From 9e42bb3c1477d4622172b9ac49f67270db14ee7b Mon Sep 17 00:00:00 2001 From: einhverfr Date: Wed, 6 Aug 2008 23:33:47 +0000 Subject: Adding entity_credit_account note types git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2237 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/modules/Company.sql | 98 +++++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 82 insertions(+), 16 deletions(-) (limited to 'sql') diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql index 45fd89dd..45eebee7 100644 --- a/sql/modules/Company.sql +++ b/sql/modules/Company.sql @@ -102,6 +102,20 @@ BEGIN END; $$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION eca__save_notes(in_credit_id int, in_note text) +RETURNS INT AS +$$ +DECLARE out_id int; +BEGIN + -- TODO, change this to create vector too + INSERT INTO eca_note (ref_key, note_class, note, vector) + VALUES (in_credit_id, 3, in_note, ''); + + SELECT currval('note_id_seq') INTO out_id; + RETURN out_id; +END; +$$ LANGUAGE PLPGSQL; + CREATE OR REPLACE FUNCTION entity_credit_get_id_by_meta_number (in_meta_number text, in_account_class int) returns int AS @@ -170,7 +184,9 @@ CREATE TYPE entity_credit_retrieve AS ( enddate date, ar_ap_account_id int, cash_account_id int, - threshold numeric + threshold numeric, + control_code text, + credit_id int ); COMMENT ON TYPE entity_credit_search_return IS @@ -203,7 +219,7 @@ BEGIN ec.meta_number, ec.business_id, ec.language_code, ec.pricegroup_id, ec.curr, ec.startdate, ec.enddate, ec.ar_ap_account_id, ec.cash_account_id, - c.tax_id, ec.threshold + ec.threshold, e.control_code, ec.id FROM company c JOIN entity e ON (c.entity_id = e.id) JOIN entity_credit_account ec ON (c.entity_id = ec.entity_id) @@ -251,34 +267,39 @@ BEGIN a.city, a.state, a.mail_code, cc.name into out_var FROM company c - JOIN company_to_location cl ON (c.id = cl.company_id) + JOIN company eca ON (eca.entity_id = c.entity_id) + JOIN company_to_location cl ON (eca.id = cl.credit_id) JOIN location a ON (a.id = cl.location_id) JOIN country cc ON (cc.id = a.country_id) - WHERE c.entity_id = (select entity_id - from entity_credit_account where id = in_id) - AND a.id = (SELECT min(location_id) from company_to_location - where company_id = c.id and location_class = 1); + WHERE eca.id = in_id AND location_class = 1; + RETURN out_var; END; $$ language plpgsql; CREATE OR REPLACE FUNCTION company_save ( - in_id int, in_entity_class int, + in_id int, in_control_code text, in_entity_class int, in_name text, in_tax_id TEXT, in_entity_id int, in_sic_code text ) RETURNS INT AS $$ DECLARE t_entity_id INT; t_company_id INT; + t_control_code TEXT; BEGIN t_company_id := in_id; + IF in_control_code IS NULL THEN + t_control_code := setting_increment('company_control'); + ELSE + t_control_code := in_control_code; + END IF; + IF in_entity_id IS NULL THEN IF in_id IS NULL THEN RAISE NOTICE 'in_id is null'; SELECT id INTO t_company_id FROM company - WHERE legal_name = in_name AND - (tax_id = in_tax_id OR - (tax_id IS NULL AND in_tax_id IS NULL)); + WHERE entity_id = (SELECT id FROM entity WHERE + control_code = t_control_code); END IF; IF t_company_id IS NOT NULL THEN SELECT entity_id INTO t_entity_id FROM company @@ -289,8 +310,8 @@ BEGIN t_entity_id := in_entity_id; END IF; IF t_entity_id IS NULL THEN - INSERT INTO entity (name, entity_class) - VALUES (in_name, in_entity_class); + INSERT INTO entity (name, entity_class, control_code) + VALUES (in_name, in_entity_class, t_control_code); t_entity_id := currval('entity_id_seq'); END IF; @@ -404,10 +425,9 @@ BEGIN l.state, l.mail_code, 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 + WHERE ctl.company_id = (select id from company where entity_id = in_entity_id) ORDER BY lc.id, l.id, c.name LOOP RETURN NEXT out_row; @@ -437,7 +457,6 @@ BEGIN END LOOP; END; $$ language plpgsql; - CREATE OR REPLACE FUNCTION company__list_bank_account(in_entity_id int) RETURNS SETOF entity_bank_account AS @@ -452,6 +471,26 @@ BEGIN END; $$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION entity__save_bank_account +(in_entity_id int, in_credit_id int, in_bic text, in_iban text) +RETURNS int AS +$$ +DECLARE out_id int; +BEGIN + INSERT INTO entity_bank_account(entity_id, bic, iban) + VALUES(in_entity_id, in_bic, in_iban); + + SELECT CURRVAL('entity_bank_account_id_seq') INTO out_id ; + + IF in_credit_id IS NOT NULL THEN + UPDATE entity_credit_account SET bank_account = out_id + WHERE id = in_credit_id; + END IF; + + RETURN out_id; +END; +$$ LANGUAGE PLPGSQL; + CREATE OR REPLACE FUNCTION entity__save_bank_account (in_entity_id int, in_bic text, in_iban text) RETURNS int AS @@ -484,6 +523,7 @@ $$ LANGUAGE PLPGSQL; CREATE TYPE entity_note_list AS ( id int, + note_class int, note text ); @@ -496,12 +536,37 @@ BEGIN SELECT * FROM entity_note WHERE ref_key = in_entity_id + ORDER BY created LOOP RETURN NEXT out_row; END LOOP; END; $$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION eca__list_notes(in_credit_id int) +RETURNS SETOF note AS +$$ +DECLARE out_row record; + t_entity_id int; +BEGIN + SELECT entity_id INTO t_entity_id + FROM entity_credit_account + WHERE id = in_credit_id; + + FOR out_row IN + SELECT * + FROM note + WHERE (note_class = 3 and ref_key = in_credit_id) or + (note_class = 1 and ref_key = t_entity_id) + ORDER BY created + LOOP + RETURN NEXT out_row; + END LOOP; +END; +$$ LANGUAGE PLPGSQL SECURITY DEFINER; + +REVOKE EXECUTE ON FUNCTION eca__list_notes(INT) FROM public; + CREATE OR REPLACE FUNCTION company__next_id() returns bigint as $$ select nextval('company_id_seq'); @@ -556,4 +621,5 @@ create or replace function _entity_location_save( $$ language 'plpgsql'; + -- COMMIT; -- cgit v1.2.3