diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/modules/Customer.sql | 75 | ||||
| -rw-r--r-- | sql/modules/Location.sql | 31 | ||||
| -rw-r--r-- | sql/modules/Settings.sql | 13 | ||||
| -rw-r--r-- | sql/modules/Vendor.sql | 8 |
4 files changed, 72 insertions, 55 deletions
diff --git a/sql/modules/Customer.sql b/sql/modules/Customer.sql index 89867ded..6071bc4a 100644 --- a/sql/modules/Customer.sql +++ b/sql/modules/Customer.sql @@ -1,5 +1,17 @@ BEGIN; +CREATE OR REPLACE FUNCTION entity_list_contact_class() +RETURNS SETOF contact_class AS +$$ +DECLARE out_row RECORD; +BEGIN + FOR out_row IN + SELECT * FROM contact_class ORDER BY id + LOOP + RETURN NEXT out_row; + END LOOP; +END; +$$ language plpgsql; CREATE TYPE customer_search_return AS ( legal_name text, @@ -28,25 +40,23 @@ CREATE TYPE customer_search_return AS ( -- $$ This structure will change greatly in 1.4. -- If you want to reply on it heavily, be prepared for breakage later. $$; -CREATE OR REPLACE FUNCTION customer_save ( - in_id int, +CREATE OR REPLACE FUNCTION entity_credit_save ( + in_id int, in_entity_class int, in_discount numeric, in_taxincluded bool, in_creditlimit numeric, in_discount_terms int, - 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_terms int, in_meta_number varchar(32), 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, in_threshold NUMERIC ) returns INT as $$ -- does not require entity_class, as entity_class is a known given to be 1 + + -- Maybe we should make this generic and pass through? -- CT DECLARE t_entity_class int; @@ -55,20 +65,19 @@ CREATE OR REPLACE FUNCTION customer_save ( 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'); + select nextval('entity_id_seq') into new_entity_id; insert into entity (id, name, entity_class) - VALUES (new_entity_id, in_name, t_entity_class); + VALUES (new_entity_id, in_name, in_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 company ( entity_id, legal_name, tax_id ) + VALUES ( new_entity_id, in_name, in_tax_id ); INSERT INTO entity_credit_account ( entity_id, @@ -78,27 +87,23 @@ CREATE OR REPLACE FUNCTION customer_save ( creditlimit, terms, meta_number, - cc, - bcc, business_id, language_code, pricegroup_id, curr, startdate, enddate, - discountterms, + discount_terms, threshold ) VALUES ( new_entity_id, - t_entity_class, + in_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, @@ -106,22 +111,13 @@ CREATE OR REPLACE FUNCTION customer_save ( in_startdate, in_enddate, in_discount_terms, - in_threashold + in_threshold ); - 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, ''); - + return new_entity_id; + ELSIF FOUND THEN update company set tax_id = in_tax_id where id = in_id; @@ -131,8 +127,6 @@ CREATE OR REPLACE FUNCTION customer_save ( 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, @@ -143,17 +137,13 @@ CREATE OR REPLACE FUNCTION customer_save ( discount_terms = in_discount_terms 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; + return in_id; END IF; - return in_id; END; $$ language 'plpgsql'; @@ -161,15 +151,14 @@ $$ 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 + in_line_three text, + in_city TEXT, in_state text, in_mail_code text, in_country_code int ) 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); + in_location_class, in_line_one, in_line_two, in_line_three, + in_city, in_state, in_mail_code, in_country_code); END; $$ language 'plpgsql'; diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql index d4f8f9f5..51fb1d02 100644 --- a/sql/modules/Location.sql +++ b/sql/modules/Location.sql @@ -1,4 +1,31 @@ -- VERSION 1.3.0 + +CREATE OR REPLACE FUNCTION location_list_class() +RETURNS SETOF location_class AS +$$ +DECLARE out_row RECORD; +BEGIN + FOR out_row IN + SELECT * FROM location_class ORDER BY id + LOOP + RETURN NEXT out_row; + END LOOP; +END; +$$ language plpgsql; + +CREATE OR REPLACE FUNCTION location_list_country() +RETURNS SETOF country AS +$$ +DECLARE out_row RECORD; +BEGIN + FOR out_row IN + SELECT * FROM country ORDER BY id + LOOP + RETURN NEXT out_row; + END LOOP; +END; +$$ language plpgsql; + CREATE OR REPLACE FUNCTION location_save (in_address1 text, in_address2 text, in_address3 text, in_city text, in_state text, in_zipcode text, in_country text) @@ -16,13 +43,13 @@ BEGIN city = in_city AND state = in_state AND zipcode = in_zipcode AND - country = in_country + country_id = in_country LIMIT 1; IF FOUND THEN return location_row.id; END IF; INSERT INTO location - (companyname, address1, address2, city, state, zipcode, country) + (companyname, address1, address2, city, state, zipcode, country_id) VALUES (in_companyname, in_address1, in_address2, in_city, in_state, in_zipcode, in_country); diff --git a/sql/modules/Settings.sql b/sql/modules/Settings.sql index e86450c3..5b8d2b28 100644 --- a/sql/modules/Settings.sql +++ b/sql/modules/Settings.sql @@ -7,6 +7,7 @@ BEGIN UPDATE defaults SET value = in_value WHERE setting_key = in_key; RETURN; END; +$$ language plpgsql; CREATE OR REPLACE FUNCTION setting_get (in_key varchar) RETURNS varchar AS $$ @@ -23,7 +24,7 @@ RETURNS SETOF defaults AS $$ DECLARE account defaults%ROWTYPE; -BEGIN; +BEGIN FOR account IN SELECT * FROM defaults WHERE setting_key like '%accno_id' @@ -33,13 +34,13 @@ BEGIN; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION setting_incriment (in_key varchar) returns varchar +CREATE OR REPLACE FUNCTION setting_increment (in_key varchar) returns varchar AS $$ DECLARE base_value VARCHAR; raw_value VARCHAR; - incriment INTEGER; + increment INTEGER; inc_length INTEGER; new_value VARCHAR; BEGIN @@ -51,10 +52,10 @@ BEGIN INTO base_value; IF base_value like '0%' THEN - incriment := base_value::integer + 1; - SELECT char_length(incriment::text) INTO inc_length; + increment := base_value::integer + 1; + SELECT char_length(increment::text) INTO inc_length; - SELECT overlay(base_value placing incriment::varchar + SELECT overlay(base_value placing increment::varchar from (select char_length(base_value) - inc_length + 1) for inc_length) INTO new_value; diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql index e18f88a5..d2f6c3c3 100644 --- a/sql/modules/Vendor.sql +++ b/sql/modules/Vendor.sql @@ -171,9 +171,9 @@ $$ language 'plpgsql'; create or replace function _entity_location_save( 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 + in_location_class int, in_line_one text, in_line_two text, + in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text, + in_country_code int ) returns int AS $$ DECLARE @@ -185,7 +185,7 @@ create or replace function _entity_location_save( AND location_class = in_location_class AND location_id = in_location_id; - SELECT location_save(in_line_one, in_line_two, NULL, in_city, + SELECT location_save(in_line_one, in_line_two, in_line_three, in_city, in_state, in_mail_code, in_mail_code, in_country_code) INTO l_id; |