diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/Pg-database.sql | 3 | ||||
| -rw-r--r-- | sql/modules/Location.sql | 8 | ||||
| -rw-r--r-- | sql/modules/Roles.sql | 1 | ||||
| -rw-r--r-- | sql/modules/Vendor.sql | 60 |
4 files changed, 24 insertions, 48 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 67dc0a5a..9737b474 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -130,7 +130,6 @@ SELECT SETVAL('location_class_id_seq',4); CREATE TABLE location ( id serial PRIMARY KEY, - location_class integer not null references location_class(id), line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL, line_two text, line_three text, @@ -156,6 +155,7 @@ COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$; CREATE TABLE company_to_location ( location_id integer references location(id) not null, + location_class integer not null references location_class(id), company_id integer not null references company(id) ON DELETE CASCADE, PRIMARY KEY(location_id,company_id)); @@ -201,6 +201,7 @@ create table entity_employee ( CREATE TABLE person_to_location ( location_id integer not null references location(id), + location_class integer not null references location_class(id), person_id integer not null references person(id) ON DELETE CASCADE, PRIMARY KEY (location_id,person_id)); diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql index fb93cf82..d4f8f9f5 100644 --- a/sql/modules/Location.sql +++ b/sql/modules/Location.sql @@ -1,6 +1,6 @@ -- VERSION 1.3.0 CREATE OR REPLACE FUNCTION location_save -(in_companyname text, in_address1 text, in_address2 text, +(in_address1 text, in_address2 text, in_address3 text, in_city text, in_state text, in_zipcode text, in_country text) returns integer AS $$ @@ -10,9 +10,9 @@ DECLARE BEGIN SELECT * INTO location_row FROM location - WHERE companyname = in_companyname AND - address1 = in_address1 AND - address2 = in_address2 AND + WHERE line_one = in_address1 AND + line_two = in_address2 AND + line_three = in_address3 AND city = in_city AND state = in_state AND zipcode = in_zipcode AND diff --git a/sql/modules/Roles.sql b/sql/modules/Roles.sql index a44ebcf4..864fb4e7 100644 --- a/sql/modules/Roles.sql +++ b/sql/modules/Roles.sql @@ -1376,3 +1376,4 @@ GRANT SELECT ON user_preference TO public; GRANT SELECT ON custom_table_catalog TO PUBLIC; GRANT SELECT ON custom_field_catalog TO PUBLIC; grant select on menu_node, menu_attribute, menu_acl to public; +GRANT select on chart, gifi to public; diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql index d075b62e..e18f88a5 100644 --- a/sql/modules/Vendor.sql +++ b/sql/modules/Vendor.sql @@ -170,9 +170,9 @@ $$ 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_company_id int, in_location_id int, + in_location_class int, in_line_one text, in_line_two text, + in_city TEXT, in_state TEXT, in_mail_code text, in_country_code int, in_created date ) returns int AS $$ @@ -180,46 +180,20 @@ create or replace function _entity_location_save( 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; + DELETE FROM company_to_location + WHERE company_id = in_company_id + AND location_class = in_location_class + AND location_id = in_location_id; + + SELECT location_save(in_line_one, in_line_two, NULL, in_city, + in_state, in_mail_code, in_mail_code, in_country_code) + INTO l_id; + + INSERT INTO company_to_location + (company_id, location_class, location_id) + VALUES (in_company_id, in_location_class, l_id); + + RETURN l_id; END; $$ language 'plpgsql'; |