summaryrefslogtreecommitdiff
path: root/sql/modules
diff options
context:
space:
mode:
Diffstat (limited to 'sql/modules')
-rw-r--r--sql/modules/Customer.sql212
-rw-r--r--sql/modules/Employee.sql218
-rw-r--r--sql/modules/Vendor.sql280
3 files changed, 652 insertions, 58 deletions
diff --git a/sql/modules/Customer.sql b/sql/modules/Customer.sql
new file mode 100644
index 00000000..f59c1ae1
--- /dev/null
+++ b/sql/modules/Customer.sql
@@ -0,0 +1,212 @@
+BEGIN;
+
+CREATE TYPE customer_search_return AS (
+ legal_name text,
+ id int,
+ entity_id int,
+ entity_class int,
+ discount numeric,
+ taxincluded bool,
+ creditlimit numeric,
+ terms int2,
+ customernumber int,
+ cc text,
+ bcc text,
+ business_id int,
+ language_code text,
+ pricegroup_id int,
+ curr char,
+ startdate date,
+ enddate date,
+ bic varchar,
+ iban varchar,
+ note text
+);
+
+CREATE OR REPLACE FUNCTION customer_save (
+ in_id int,
+
+ in_discount numeric, in_taxincluded bool, in_creditlimit numeric,
+ in_terms int, in_meta_number varchar(32), in_cc text, in_bcc text,
+ in_business_id int, in_language varchar(6), in_pricegroup_id int,
+ in_curr char, in_startdate date, in_enddate date,
+
+ in_bic text, in_iban text,
+
+ in_notes text,
+
+ in_name text, in_tax_id TEXT
+
+) returns INT as $$
+
+ -- does not require entity_class, as entity_class is a known given to be 1
+
+ DECLARE
+ t_entity_class int;
+ new_entity_id int;
+ v_row company;
+ l_id int;
+ BEGIN
+
+ t_entity_class := 1;
+
+ SELECT INTO v_row * FROM company WHERE id = in_id;
+
+ IF NOT FOUND THEN
+ -- do some inserts
+
+ new_entity_id := nextval('entity_id_seq');
+
+ insert into entity (id, name, entity_class)
+ VALUES (new_entity_id, in_name, t_entity_class);
+
+ INSERT INTO company ( id, entity_id, legal_name, tax_id )
+ VALUES ( in_id, new_entity_id, in_name, in_tax_id );
+
+ INSERT INTO entity_credit_account (
+ entity_id,
+ entity_class,
+ discount,
+ taxincluded,
+ creditlimit,
+ terms,
+ meta_number,
+ cc,
+ bcc,
+ business_id,
+ language_code,
+ pricegroup_id,
+ curr,
+ startdate,
+ enddate
+ )
+ VALUES (
+ new_entity_id,
+ t_entity_class,
+ in_discount,
+ in_taxincluded,
+ in_creditlimit,
+ in_terms,
+ in_meta_number,
+ in_cc,
+ in_bcc,
+ in_business_id,
+ in_language,
+ in_pricegroup_id,
+ in_curr,
+ in_startdate,
+ in_enddate
+ );
+ INSERT INTO entity_bank_account (
+ entity_id,
+ bic,
+ iban
+ )
+ VALUES (
+ new_entity_id,
+ in_bic,
+ in_iban
+ );
+ -- entity note class
+ insert into entity_note (note_class, note, ref_key, vector) VALUES (
+ 1, in_notes, new_entity_id, '');
+
+ ELSIF FOUND THEN
+
+ update company set tax_id = in_tax_id where id = in_id;
+ update entity_credit_account SET
+ discount = in_discount,
+ taxincluded = in_taxincluded,
+ creditlimit = in_creditlimit,
+ terms = in_terms,
+ meta_number = in_meta_number,
+ cc = in_cc,
+ bcc = in_bcc,
+ business_id = in_business_id,
+ language_code = in_language,
+ pricegroup_id = in_pricegroup_id,
+ curr = in_curr,
+ startdate = in_startdate,
+ enddate = in_enddate
+ where entity_id = v_row.entity_id;
+
+ UPDATE entity_bank_account SET
+ bic = in_bic,
+ iban = in_iban
+ WHERE entity_id = v_row.entity_id;
+
+ UPDATE entity_note SET
+ note = in_note
+ WHERE ref_key = v_row.entity_id;
+
+ END IF;
+ return in_id;
+ END;
+
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION customer_location_save (
+ in_company_id int,
+ in_location_class int, in_line_one text, in_line_two text,
+ in_city_province TEXT, in_mail_code text, in_country_code int,
+ in_created date
+) returns int AS $$
+ BEGIN
+ return _entity_location_save(
+ in_company_id,
+ in_location_class, in_line_one, in_line_two,
+ in_city_province , in_mail_code, in_country_code,
+ in_created);
+ END;
+
+$$ language 'plpgsql';
+
+
+CREATE OR REPLACE FUNCTION customer_search(in_pattern TEXT) returns setof customer_search_return as $$
+
+ -- searches customer name, account number, street address, city, state,
+ -- other location-based stuff
+
+ declare
+ v_row customer_search_return;
+ query text;
+ begin
+
+ for v_row in select c.legal_name, v.* from customer v
+ join company c on c.entity_id = v.entity_id
+ join entity e on e.id = v.entity_id
+ join company_to_location ctl on c.id = ctl.company_id
+ join location l on l.id = ctl.location_id
+ where l.line_one % in_pattern
+ OR l.line_two % in_pattern
+ OR l.line_three % in_pattern
+ OR l.city_province % in_pattern
+ OR c.legal_name % in_pattern
+ OR e.name % in_pattern
+ LOOP
+
+ RETURN NEXT v_row;
+
+ END LOOP;
+
+ RETURN;
+
+ end;
+
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION customer_retrieve(INT) returns setof customer as $$
+
+
+ select v.* from customer v
+ join company c on c.entity_id = v.entity_id
+ where v.id = $1;
+
+$$ language 'sql';
+COMMIT;
+
+CREATE OR REPLACE FUNCTION customer_next_customer_id() returns int as $$
+
+ select nextval('company_id_seq');
+
+$$ language 'sql';ƒ \ No newline at end of file
diff --git a/sql/modules/Employee.sql b/sql/modules/Employee.sql
index c407e244..a934a3e5 100644
--- a/sql/modules/Employee.sql
+++ b/sql/modules/Employee.sql
@@ -1,66 +1,157 @@
-- VERSION 1.3.0
+BEGIN;
CREATE OR REPLACE FUNCTION employee_save
-(in_id integer, in_location_id integer, in_employeenumber varchar(32),
- in_name varchar(64), in_address1 varchar(32), in_address2 varchar(32),
+(in_id integer, in_employeenumber varchar(32),
+ in_salutation int, in_first_name varchar(64), in_last_name varchar(64),
+ in_address1 varchar(32), in_address2 varchar(32),
in_city varchar(32), in_state varchar(32), in_zipcode varchar(10),
- in_country varchar(32), in_workphone varchar(20),
+ in_country int, in_workphone varchar(20),
in_homephone varchar(20), in_startdate date, in_enddate date,
in_notes text, in_role varchar(20), in_sales boolean, in_email text,
in_ssn varchar(20), in_dob date, in_iban varchar(34),
- in_bic varchar(11), in_managerid integer) returns int
-AS
-$$
+ in_bic varchar(11), in_managerid integer)
+returns int AS $$
+DECLARE
+ e_id int;
+ e entity;
+ loc location;
+ l_id int;
+ per person;
+ p_id int;
BEGIN
- UPDATE employees
- SET location_id = in_location_id,
- employeenumber = in_employeenumber,
- name = in_name,
- address1 = in_address1,
- address2 = in_address2,
- city = in_city,
- state = in_state,
- zipcode = in_zipcode,
- country = in_country,
- workphone = in_workphone,
- homephone = in_homephone,
- startdate = in_startdate,
- enddate = in_enddate,
- notes = in_notes,
- role = in_role,
- sales = in_sales,
- email = in_email,
- ssn = in_ssn,
- dob=in_dob,
- iban = in_iban,
- bic = in_bic,
- manager_id = in_managerid
- WHERE id = in_id;
- IF FOUND THEN
- return in_id;
+ select * into e from entity where id = in_id and entity_class = 3;
+
+ if found then
+
+ select l.* into loc from location l
+ left join person_to_location ptl on ptl.location_id = l.id
+ left join person p on p.id = ptl.person_id
+ where p.entity_id = in_id;
+
+ select * into per from person p where p.entity_id = in_id;
+
+ update location
+ set
+ line_one = in_address1,
+ line_two = in_address2,
+ city_province = in_city,
+ mail_code = in_zipcode,
+ country_id = in_country
+ where id = loc.id;
+
+ UPDATE employee
+ SET
+ employeenumber = in_employeenumber,
+ startdate = in_startdate,
+ enddate = in_enddate,
+ role = in_role,
+ sales = in_sales,
+ ssn = in_ssn,
+ dob = in_dob,
+ managerid = in_managerid
+ WHERE entity_id = in_id;
+
+ update entity_note
+ set
+ note = in_note
+ where entity_id = in_id;
+
+ UPDATE entity_bank_account
+ SET
+ bic = in_bic,
+ iban = in_iban
+ WHERE entity_id = in_id;
+
+ UPDATE person
+ SET
+ salutation_id = in_salutation,
+ first_name = in_first_name,
+ last_name = in_last_name
+ WHERE entity_id = in_id;
+
+ UPDATE person_to_contact
+ set
+ contact = in_homephone
+ WHERE person_id = per.id
+ AND contact_class_id = 11;
+
+ UPDATE person_to_contact
+ set
+ contact = in_workphone
+ WHERE person_id = per.id
+ AND contact_class_id = 1;
+
+ UPDATE person_to_contact
+ set
+ contact = in_email
+ WHERE person_id = per.id
+ AND contact_class_id = 12;
+
+ return in_id;
+
+ ELSIF NOT FOUND THEN
+ -- first, create a new entity
+ -- Then, create an employee.
+
+ e_id := in_id; -- expect nextval entity_id to have been called.
+ INSERT INTO entity (id, entity_class, name) VALUES (e_id, 3, in_first_name||' '||in_last_name);
+
+ INSERT INTO entity_bank_account (entity_id, iban, bic)
+ VALUES (e_id, in_iban, in_bic);
+
+ p_id := nextval('person_id_seq');
+ insert into person (id, salutation_id, first_name, last_name, entity_id)
+ VALUES
+ (p_id, in_salutation, in_first_name, in_last_name, e_id);
+
+ if in_notes is not null then
+ insert into entity_note (note_class, note, ref_key, vector)
+ values (1, in_notes, e_id, '');
+ END IF;
+
+ insert into person_to_contact (person_id, contact_class_id, contact)
+ VALUES (p_id, 1, in_workphone); -- work phone #
+ insert into person_to_contact (person_id, contact_class_id, contact)
+ VALUES (p_id, 11, in_homephone); -- Home phone #
+ insert into person_to_contact (person_id, contact_class_id, contact)
+ VALUES (p_id, 12, in_email); -- email address.
+
+ INSERT INTO employee
+ (employeenumber, startdate, enddate,
+ role, sales, ssn,
+ dob, managerid, entity_id, entity_class_id)
+ VALUES
+ (in_employeenumber, in_startdate, in_enddate,
+ in_role, in_sales, in_ssn,
+ in_dob, in_managerid, e_id, 3);
+
+ l_id := nextval('location_id_seq');
+ insert into location (id, location_class, line_one, line_two, city_province, country_id, mail_code)
+ VALUES (
+ l_id,
+ 1,
+ in_address1,
+ in_address2,
+ in_city,
+ in_country,
+ in_zipcode
+ );
+ insert into person_to_location (person_id, location_id)
+ VALUES (p_id, l_id);
+
+ return e_id;
END IF;
- INSERT INTO employees
- (location_id, employeenumber, name, address1, address2,
- city, state, zipcode, country, workphone, homephone,
- startdate, enddate, notes, role, sales, email, ssn,
- dob, iban, bic, managerid)
- VALUES
- (in_location_id, in_employeenumber, in_name, in_address1,
- in_address2, in_city, in_state, in_zipcode, in_country,
- in_workphone, in_homephone, in_startdate, in_enddate,
- in_notes, in_role, in_sales, in_email, in_ssn, in_dob,
- in_iban, in_bic, in_managerid);
- SELECT currval('employee_id_seq') INTO employee_id;
- return employee_id;
END;
-$$ LANGUAGE PLPGSQL;
+$$ LANGUAGE 'plpgsql';
+-- why is this like this?
CREATE OR REPLACE FUNCTION employee_get
(in_id integer)
-returns employees as
+returns employee as
$$
DECLARE
- emp employees%ROWTYPE;
+ emp employee%ROWTYPE;
BEGIN
SELECT * INTO emp FROM employees WHERE id = in_id;
RETURN emp;
@@ -69,15 +160,15 @@ $$ language plpgsql;
CREATE OR REPLACE FUNCTION employee_list_managers
(in_id integer)
-RETURNS SETOF employees as
+RETURNS SETOF employee as
$$
DECLARE
- emp employees%ROWTYPE;
+ emp employee%ROWTYPE;
BEGIN
FOR emp IN
- SELECT * FROM employees
+ SELECT * FROM employee
WHERE sales = '1' AND role='manager'
- AND id <> coalesce(in_id, -1)
+ AND entity_id <> coalesce(in_id, -1)
ORDER BY name
LOOP
RETURN NEXT emp;
@@ -89,15 +180,25 @@ CREATE OR REPLACE FUNCTION employee_delete
(in_id integer) returns void as
$$
BEGIN
- DELETE FROM employees WHERE id = in_id;
+ DELETE FROM employee WHERE entity_id = in_id;
RETURN;
END;
$$ language plpgsql;
-- as long as we need the datatype, might as well get some other use out of it!
+--
+-- % type is pg_trgm comparison.
+
+CREATE INDEX name_idx ON employee USING gist(name gist_trgm_ops);
+CREATE INDEX notes_idx ON entity_note USING gist(note gist_trgm_ops);
+
CREATE OR REPLACE VIEW employee_search AS
-SELECT e.*, m.name AS manager
-FROM employees e LEFT JOIN employees m ON (e.managerid = m.id);
+SELECT e.*, em.name AS manager, emn.note, en.name as name
+FROM employee e
+LEFT JOIN entity en on (e.entity_id = en.id)
+LEFT JOIN employee m ON (e.managerid = m.entity_id)
+LEFT JOIN entity em on (em.id = m.entity_id)
+LEFT JOIN entity_note emn on (emn.ref_key = em.id);
CREATE OR REPLACE FUNCTION employee_search
(in_startdatefrom date, in_startdateto date, in_name varchar, in_notes text,
@@ -118,12 +219,13 @@ BEGIN
coalesce(in_enddateto, 'infinity'::timestamp)
AND coalesce(enddate, 'infinity'::timestamp) >=
coalesce(in_enddatefrom, '-infinity'::timestamp)
- AND lower(name) LIKE '%' || lower(in_name) || '%'
- AND lower(notes) LIKE '%' || lower(in_notes) || '%'
+ AND (name % in_name
+ OR note % in_notes)
AND (sales = 't' OR coalesce(in_sales, 'f') = 'f')
LOOP
RETURN NEXT emp;
END LOOP;
+ return;
END;
$$ language plpgsql;
-
+COMMIT; \ No newline at end of file
diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql
new file mode 100644
index 00000000..2026fffa
--- /dev/null
+++ b/sql/modules/Vendor.sql
@@ -0,0 +1,280 @@
+BEGIN;
+
+CREATE TYPE vendor_search_return AS (
+ legal_name text,
+ id int,
+ entity_id int,
+ entity_class int,
+ discount numeric,
+ taxincluded bool,
+ creditlimit numeric,
+ terms int2,
+ vendornumber int,
+ cc text,
+ bcc text,
+ business_id int,
+ language_code text,
+ pricegroup_id int,
+ curr char,
+ startdate date,
+ enddate date,
+ bic varchar,
+ iban varchar,
+ note text
+);
+
+CREATE OR REPLACE FUNCTION vendor_save (
+ in_id int,
+
+ in_discount numeric, in_taxincluded bool, in_creditlimit numeric,
+ in_terms int, in_vendornumber varchar(32), in_cc text, in_bcc text,
+ in_business_id int, in_language varchar(6), in_pricegroup_id int,
+ in_curr char, in_startdate date, in_enddate date,
+
+ in_bic text, in_iban text,
+
+ in_notes text,
+
+ in_name text, in_tax_id TEXT
+
+) returns INT as $$
+
+ -- does not require entity_class, as entity_class is a known given to be 1
+
+ DECLARE
+ t_entity_class int;
+ new_entity_id int;
+ v_row company;
+ l_id int;
+ BEGIN
+
+ t_entity_class := 1;
+
+ SELECT INTO v_row * FROM company WHERE id = in_id;
+
+ IF NOT FOUND THEN
+ -- do some inserts
+
+ new_entity_id := nextval('entity_id_seq');
+
+ insert into entity (id, name, entity_class)
+ VALUES (new_entity_id, in_name, t_entity_class);
+
+ INSERT INTO company ( id, entity_id, legal_name, tax_id )
+ VALUES ( in_id, new_entity_id, in_name, in_tax_id );
+
+ INSERT INTO entity_credit_account (
+ entity_id,
+ entity_class,
+ discount,
+ taxincluded,
+ creditlimit,
+ terms,
+ cc,
+ bcc,
+ business_id,
+ language_code,
+ pricegroup_id,
+ curr,
+ startdate,
+ enddate,
+ meta_number
+ )
+ VALUES (
+ new_entity_id,
+ t_entity_class,
+ in_discount,
+ in_taxincluded,
+ in_creditlimit,
+ in_terms,
+ in_cc,
+ in_bcc,
+ in_business_id,
+ in_language,
+ in_pricegroup_id,
+ in_curr,
+ in_startdate,
+ in_enddate,
+ in_vendornumber
+ );
+ INSERT INTO entity_bank_account (
+ entity_id,
+ bic,
+ iban
+ )
+ VALUES (
+ new_entity_id,
+ in_bic,
+ in_iban
+ );
+ -- entity note class
+ insert into entity_note (note_class, note, ref_key, vector) VALUES (
+ 1, in_notes, new_entity_id, '');
+
+ ELSIF FOUND THEN
+
+ update company set tax_id = in_tax_id where id = in_id;
+ update entity_credit_account SET
+ discount = in_discount,
+ taxincluded = in_taxincluded,
+ creditlimit = in_creditlimit,
+ terms = in_terms,
+ cc = in_cc,
+ bcc = in_bcc,
+ business_id = in_business_id,
+ language_code = in_language,
+ pricegroup_id = in_pricegroup_id,
+ curr = in_curr,
+ startdate = in_startdate,
+ enddate = in_enddate,
+ meta_number = in_vendornumber
+ where entity_id = v_row.entity_id;
+
+ UPDATE entity_bank_account SET
+ bic = in_bic,
+ iban = in_iban
+ WHERE entity_id = v_row.entity_id;
+
+ UPDATE entity_note SET
+ note = in_note
+ WHERE ref_key = v_row.entity_id;
+
+ END IF;
+ return in_id;
+ END;
+
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION vendor_location_save (
+ in_company_id int,
+ in_location_class int, in_line_one text, in_line_two text,
+ in_city_province TEXT, in_mail_code text, in_country_code int,
+ in_created date
+) returns int AS $$
+ BEGIN
+ return _entity_location_save(
+ in_company_id,
+ in_location_class, in_line_one, in_line_two,
+ in_city_province , in_mail_code, in_country_code,
+ in_created);
+ END;
+
+$$ language 'plpgsql';
+
+
+create or replace function _entity_location_save(
+ in_company_id int,
+ in_location_class int, in_line_one text, in_line_two text,
+ in_city_province TEXT, in_mail_code text, in_country_code int,
+ in_created date
+) returns int AS $$
+
+ DECLARE
+ l_row location;
+ l_id INT;
+ BEGIN
+
+ SELECT l.* INTO l_row FROM location l
+ JOIN company_to_location ctl ON ctl.location_id = l.id
+ JOIN company c on ctl.company_id = c.id
+ where c.id = in_company_id;
+
+ IF NOT FOUND THEN
+
+ l_id := nextval('location_id_seq');
+
+ INSERT INTO location (id, location_class, line_one, line_two,
+ city_province, country_id, mail_code, created)
+ VALUES (
+ l_id,
+ in_location_class,
+ in_line_one,
+ in_line_two,
+ in_city_province,
+ in_country_code,
+ in_mail_code,
+ in_created
+ );
+
+ INSERT INTO company_to_location (location_id, company_id)
+ VALUES (l_id, in_company_id);
+
+ ELSIF FOUND THEN
+
+ l_id := l.id;
+ update location SET
+ location_class = in_location_class,
+ line_one = in_line_one,
+ line_two = in_line_two,
+ city_province = in_city_province,
+ country_id = in_country_code,
+ mail_code = in_mail_code
+ WHERE id = l_id;
+
+ END IF;
+ return l_id;
+ END;
+
+$$ language 'plpgsql';
+
+CREATE INDEX company_name_gist__idx ON company USING gist(legal_name gist_trgm_ops);
+CREATE INDEX location_address_one_gist__idx ON location USING gist(line_one gist_trgm_ops);
+CREATE INDEX location_address_two_gist__idx ON location USING gist(line_two gist_trgm_ops);
+CREATE INDEX location_address_three_gist__idx ON location USING gist(line_three gist_trgm_ops);
+
+CREATE INDEX location_city_prov_gist_idx ON location USING gist(city_province gist_trgm_ops);
+CREATE INDEX entity_name_gist_idx ON entity USING gist(name gist_trgm_ops);
+
+CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT,
+ in_city_prov TEXT)
+ RETURNS SETOF vendor_search_return AS $$
+
+ -- searches vendor name, account number, street address, city, state,
+ -- other location-based stuff
+
+ declare
+ v_row vendor_search_return;
+ query text;
+ begin
+
+ for v_row in select c.legal_name, v.* from vendor v
+ join company c on c.entity_id = v.entity_id
+ join entity e on e.id = v.entity_id
+ join company_to_location ctl on c.id = ctl.company_id
+ join location l on l.id = ctl.location_id
+ where (
+ l.line_one % in_address
+ OR l.line_two % in_address
+ OR l.line_three % in_address
+ )
+ OR l.city_province % in_city_prov
+ OR (
+ c.legal_name % in_name
+ OR e.name % in_name
+ )
+ LOOP
+
+ RETURN NEXT v_row;
+
+ END LOOP;
+
+ RETURN;
+
+ end;
+
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION vendor_retrieve(INT) returns setof vendor as $$
+
+ select v.* from vendor v
+ join company c on c.entity_id = v.entity_id
+ where v.id = $1;
+
+$$ language 'sql';
+COMMIT;
+
+CREATE OR REPLACE FUNCTION vendor_next_vendor_id() returns int as $$
+
+ select nextval('company_id_seq');
+
+$$ language 'sql'; \ No newline at end of file
generated by cgit v1.2.3 (git 2.46.0) at 2025-01-11 18:33:45 +0000