diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/Pg-database.sql | 32 | ||||
| -rw-r--r-- | sql/modules/Customer.sql | 212 | ||||
| -rw-r--r-- | sql/modules/Employee.sql | 218 | ||||
| -rw-r--r-- | sql/modules/Vendor.sql | 280 |
4 files changed, 667 insertions, 75 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index b0d63331..8011a9b8 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -188,7 +188,7 @@ CREATE TABLE location ( created date not null, inactive_date timestamp default null, active boolean not null default TRUE - ); +); CREATE INDEX location_unique_class_idx ON location (id,location_class); CREATE TABLE company ( @@ -313,7 +313,7 @@ CREATE TABLE note (id serial primary key, note_class integer not null references ref_key integer not null); CREATE TABLE entity_note() INHERITS (note); -ALTER TABLE entity_note ADD CHECK (id = 1); +ALTER TABLE entity_note ADD CHECK (note_class = 1); ALTER TABLE entity_note ADD FOREIGN KEY (ref_key) REFERENCES entity(id) ON DELETE CASCADE; CREATE INDEX entity_note_id_idx ON entity_note(id); CREATE UNIQUE INDEX entity_note_class_idx ON note_class(lower(class)); @@ -448,7 +448,7 @@ CREATE TABLE entity_credit_account ( bcc text, business_id int, language_code varchar(6), - pricegroup_id int, + pricegroup_id int references pricegroup(id), curr char(3), startdate date DEFAULT CURRENT_DATE, enddate date, @@ -462,18 +462,13 @@ CREATE TABLE entity_bank_account ( bic varchar, iban varchar, UNIQUE (id), - PRIMARY KEY (bic, iban) -); - -CREATE TABLE entity_invoice_notes ( - id serial not null, - entity_id int not null references entity(id), - note text, - unique (id) + PRIMARY KEY (entity_id, bic, iban) ); CREATE VIEW customer AS - SELECT emd.entity_id, + SELECT + c.id, + emd.entity_id, emd.entity_class, emd.discount, emd.taxincluded, @@ -490,15 +485,17 @@ CREATE VIEW customer AS emd.enddate, eba.bic, eba.iban, - ein.note as - invoice_notes + ein.note as invoice_notes FROM entity_credit_account emd join entity_bank_account eba on emd.entity_id = eba.entity_id - join entity_invoice_notes ein on ein.entity_id = emd.entity_id + join entity_note ein on ein.ref_key = emd.entity_id + join company c on c.entity_id = emd.entity_id where emd.entity_class = 2; CREATE VIEW vendor AS - SELECT emd.entity_id, + SELECT + c.id, + emd.entity_id, emd.entity_class, emd.discount, emd.taxincluded, @@ -519,7 +516,8 @@ CREATE VIEW vendor AS invoice_notes FROM entity_credit_account emd join entity_bank_account eba on emd.entity_id = eba.entity_id - join entity_invoice_notes ein on ein.entity_id = emd.entity_id + join entity_note ein on ein.ref_key = emd.entity_id + join company c on c.entity_id = emd.entity_id where emd.entity_class = 1; COMMENT ON TABLE entity_credit_account IS $$ This is a metadata table for ALL entities in LSMB; it deprecates the use of customer and vendor specific tables (which were nearly identical and largely redundant), and replaces it with a single point of metadata. $$; 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 |