diff options
author | einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46> | 2007-11-21 05:42:22 +0000 |
---|---|---|
committer | einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46> | 2007-11-21 05:42:22 +0000 |
commit | eb7e5f65f488f890c1d9fb6d56bed8a40fe8a595 (patch) | |
tree | ca581deabbbae5cc59c7df22b9ed77c095e8fccb /sql/modules/Customer.sql | |
parent | ff6f935be468458483306b06a827ccc82564c496 (diff) |
More Customer Fixes
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1885 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'sql/modules/Customer.sql')
-rw-r--r-- | sql/modules/Customer.sql | 120 |
1 files changed, 112 insertions, 8 deletions
diff --git a/sql/modules/Customer.sql b/sql/modules/Customer.sql index 7f2a8da0..40625cbf 100644 --- a/sql/modules/Customer.sql +++ b/sql/modules/Customer.sql @@ -23,19 +23,33 @@ CREATE TYPE customer_search_return AS ( creditlimit numeric, terms int2, customernumber int, - cc text, - bcc text, business_id int, language_code text, pricegroup_id int, - curr char, + curr char(3), startdate date, - enddate date, - bic varchar, - iban varchar, - note text + enddate date ); +CREATE OR REPLACE FUNCTION customer__retrieve(in_entity_id int) RETURNS +customer_search_return AS +$$ +DECLARE out_row customer_search_return; +BEGIN + SELECT c.legal_name, c.id, e.id, ec.entity_class, ec.discount, + ec.taxincluded, ec.creditlimit, ec.terms, ec.meta_number, + ec.business_id, ec.language_code, ec.pricegroup_id, + ec.curr::char(3), ec.startdate, ec.enddate + INTO out_row + FROM company c + JOIN entity e ON (c.entity_id = e.id) + JOIN entity_credit_account ec ON (c.entity_id = ec.entity_id) + WHERE e.id = in_entity_id + AND ec.entity_class = 2; + + RETURN out_row; +END; +$$ LANGUAGE PLPGSQL; -- COMMENT ON TYPE customer_search_result IS -- $$ This structure will change greatly in 1.4. -- If you want to reply on it heavily, be prepared for breakage later. $$; @@ -145,6 +159,96 @@ CREATE OR REPLACE FUNCTION entity_credit_save ( $$ language 'plpgsql'; +CREATE TYPE location_result AS ( + id int, + line_one text, + line_two text, + line_three text, + city text, + state text, + country text, + class text +); + + +CREATE OR REPLACE FUNCTION company__list_locations(in_entity_id int) +RETURNS SETOF location_result AS +$$ +DECLARE out_row RECORD; +BEGIN + FOR out_row IN + SELECT l.id, l.line_one, l.line_two, l.line_three, l.city, + l.state, c.name, lc.class + FROM location l + JOIN company_to_location ctl ON (ctl.location_id = l.id) + JOIN company cp ON (ctl.company_id = cp.id) + JOIN location_class lc ON (ctl.location_class = lc.id) + JOIN country c ON (c.id = l.country_id) + WHERE cp.entity_id = in_entity_id + ORDER BY lc.id, l.id, c.name + LOOP + RETURN NEXT out_row; + END LOOP; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TYPE contact_list AS ( + class text, + contact text +); + +CREATE OR REPLACE FUNCTION company__list_contacts(in_entity_id int) +RETURNS SETOF contact_list AS +$$ +DECLARE out_row RECORD; +BEGIN + FOR out_row IN + SELECT cc.class, c.contact + FROM company_to_contact c + JOIN contact_class cc ON (c.contact_class_id = cc.id) + JOIN company cp ON (c.company_id = cp.id) + WHERE cp.entity_id = in_entity_id + LOOP + RETURN NEXT out_row; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION company__list_bank_account(in_entity_id int) +RETURNS SETOF entity_bank_account AS +$$ +DECLARE out_row entity_bank_account%ROWTYPE; +BEGIN + FOR out_row IN + SELECT * from entity_bank_account where entity_id = in_entity_id + LOOP + RETURN NEXT; + END LOOP; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TYPE entity_note_list AS ( + id int, + note text +); + +CREATE OR REPLACE FUNCTION company__list_notes(in_entity_id int) +RETURNS SETOF entity_note_list AS +$$ +DECLARE out_row record; +BEGIN + FOR out_row IN + SELECT id, note + FROM entity_note + WHERE ref_key = in_entity_id + LOOP + RETURN NEXT out_row; + END LOOP; +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, @@ -153,7 +257,7 @@ CREATE OR REPLACE FUNCTION customer_location_save ( ) returns int AS $$ BEGIN return _entity_location_save( - in_company_id, + in_company_id, NULL, in_location_class, in_line_one, in_line_two, in_line_three, in_city, in_state, in_mail_code, in_country_code); END; |