diff options
| -rw-r--r-- | LedgerSMB/DBObject/Company.pm | 55 | ||||
| -rw-r--r-- | LedgerSMB/DBObject/Customer.pm | 50 | ||||
| -rw-r--r-- | LedgerSMB/Entity.pm | 2 | ||||
| -rw-r--r-- | sql/Pg-database.sql | 8 | ||||
| -rw-r--r-- | sql/modules/Company.sql | 323 | ||||
| -rw-r--r-- | sql/modules/Customer.sql | 291 | ||||
| -rw-r--r-- | sql/modules/Location.sql | 13 | ||||
| -rw-r--r-- | sql/modules/Vendor.sql | 223 |
8 files changed, 406 insertions, 559 deletions
diff --git a/LedgerSMB/DBObject/Company.pm b/LedgerSMB/DBObject/Company.pm new file mode 100644 index 00000000..30023c92 --- /dev/null +++ b/LedgerSMB/DBObject/Company.pm @@ -0,0 +1,55 @@ + +package LedgerSMB::DBObject::Company; + +use base qw(LedgerSMB::DBObject); +use strict; + +sub save_credit { + my $self = shift @_; + + my ($ref) = $self->exec_method(funcname => 'entity_credit_save'); + $self->{entity_id} = $ref->{entity_credit_save}; + $self->{dbh}->commit; +} + +sub get_metadata { + my $self = shift @_; + + @{$self->{location_class_list}} = + $self->exec_method(funcname => 'location_list_class'); + + @{$self->{country_list}} = + $self->exec_method(funcname => 'location_list_country'); + + @{$self->{contact_class_list}} = + $self->exec_method(funcname => 'entity_list_contact_class'); +} + +sub save_contact { + my ($self) = @_; + $self->exec_method(funcname => 'company__save_contact'); + $self->{dbh}->commit; +} + +sub save_bank_account { + my $self = shift @_; + $self->exec_method(funcname => 'entity__save_bank_account'); + $self->{dbh}->commit; +} + +sub get_company{ + my $self = shift @_; + @{$self->{locations}} = $self->exec_method( + funcname => 'company__list_locations'); + + @{$self->{contacts}} = $self->exec_method( + funcname => 'company__list_contacts'); + + @{$self->{bank_account}} = $self->exec_method( + funcname => 'company__list_bank_account'); + + @{$self->{notes}} = $self->exec_method( + funcname => 'company__list_notes'); +}; + +1; diff --git a/LedgerSMB/DBObject/Customer.pm b/LedgerSMB/DBObject/Customer.pm index 3805762f..a9bcffc7 100644 --- a/LedgerSMB/DBObject/Customer.pm +++ b/LedgerSMB/DBObject/Customer.pm @@ -1,8 +1,6 @@ package LedgerSMB::DBObject::Customer; -use base qw(LedgerSMB::DBObject); -use LedgerSMB::DBObject; -use LedgerSMB::Entity; +use base qw(LedgerSMB::DBObject::Company); use strict; my $CUSTOMER_ENTITY_CLASS = 2; @@ -10,66 +8,30 @@ my $CUSTOMER_ENTITY_CLASS = 2; sub save { my $self = shift @_; - # This saves both the entity and the credit account. -- CT $self->{entity_class} = $CUSTOMER_ENTITY_CLASS; - - my ($ref) = $self->exec_method(funcname => 'entity_credit_save'); - $self->{entity_id} = $ref->{entity_credit_save}; - $self->{dbh}->commit; + $self->save_credit(); # inherited from Company } -sub get_metadata { - my $self = shift @_; - - @{$self->{location_class_list}} = - $self->exec_method(funcname => 'location_list_class'); - - @{$self->{country_list}} = - $self->exec_method(funcname => 'location_list_country'); - - @{$self->{contact_class_list}} = - $self->exec_method(funcname => 'entity_list_contact_class'); -} sub save_location { my $self = shift @_; $self->{entity_class} = $CUSTOMER_ENTITY_CLASS; $self->{country_id} = $self->{country}; - $self->exec_method(funcname => 'customer_location_save'); + $self->exec_method(funcname => 'company__location_save'); $self->{dbh}->commit; } -sub save_contact { - my ($self) = @_; - $self->exec_method(funcname => 'company__save_contact'); - $self->{dbh}->commit; -} -sub save_bank_account { - my $self = shift @_; - $self->exec_method(funcname => 'entity__save_bank_account'); - $self->{dbh}->commit; -} sub get { my $self = shift @_; - my ($ref) = $self->exec_method(funcname => 'customer__retrieve'); + $self->{entity_class} = $CUSTOMER_ENTITY_CLASS; + my ($ref) = $self->exec_method(funcname => 'entity__retrieve_credit'); $self->merge($ref); $self->{name} = $self->{legal_name}; - - @{$self->{locations}} = $self->exec_method( - funcname => 'company__list_locations'); - - @{$self->{contacts}} = $self->exec_method( - funcname => 'company__list_contacts'); - - @{$self->{bank_account}} = $self->exec_method( - funcname => 'company__list_bank_account'); - - @{$self->{notes}} = $self->exec_method( - funcname => 'company__list_notes'); + $self->get_company(); } diff --git a/LedgerSMB/Entity.pm b/LedgerSMB/Entity.pm index 4c01b804..07b84a62 100644 --- a/LedgerSMB/Entity.pm +++ b/LedgerSMB/Entity.pm @@ -40,4 +40,4 @@ sub search { ] ) }; } -1;
\ No newline at end of file +1; diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 9737b474..5593bb94 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -2666,4 +2666,12 @@ CREATE TABLE report_corrections ( insert_time timestamptz not null default now() ); +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 gist_trgm_ops); +CREATE INDEX entity_name_gist_idx ON entity USING gist(name gist_trgm_ops); + commit; diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql new file mode 100644 index 00000000..3973ff2b --- /dev/null +++ b/sql/modules/Company.sql @@ -0,0 +1,323 @@ +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 entity_credit_search_return AS ( + legal_name text, + id int, + entity_id int, + entity_class int, + discount numeric, + taxincluded bool, + creditlimit numeric, + terms int2, + customernumber text, + business_id int, + language_code text, + pricegroup_id int, + curr char(3), + startdate date, + enddate date +); + +COMMENT ON TYPE entity_credit_search_return IS +$$ This may change in 1.4 and should not be relied upon too much $$; + +CREATE OR REPLACE FUNCTION entity__retrieve_credit +(in_entity_id int, in_entity_cass int) +RETURNS entity_credit_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 = in_entity_class; + + RETURN out_row; +END; +$$ LANGUAGE PLPGSQL; + +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_business_id int, + in_language varchar(6), in_pricegroup_id int, + in_curr char, in_startdate date, in_enddate date, + in_notes text, + in_name text, in_tax_id TEXT, + in_threshold NUMERIC + +) returns INT as $$ + + DECLARE + t_entity_class int; + new_entity_id int; + v_row company; + l_id int; + BEGIN + + -- TODO: Move every table to an upsert mode independantly. + SELECT INTO v_row * FROM company WHERE id = in_id; + + IF NOT FOUND THEN + -- do some inserts + + select nextval('entity_id_seq') into new_entity_id; + + insert into entity (id, name, entity_class) + VALUES (new_entity_id, in_name, in_entity_class); + + 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, + entity_class, + discount, + taxincluded, + creditlimit, + terms, + meta_number, + business_id, + language_code, + pricegroup_id, + curr, + startdate, + enddate, + discount_terms, + threshold + ) + VALUES ( + new_entity_id, + in_entity_class, + in_discount / 100, + in_taxincluded, + in_creditlimit, + in_terms, + in_meta_number, + in_business_id, + in_language, + in_pricegroup_id, + in_curr, + in_startdate, + in_enddate, + in_discount_terms, + in_threshold + ); + -- 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; + update entity_credit_account SET + discount = in_discount, + taxincluded = in_taxincluded, + creditlimit = in_creditlimit, + terms = in_terms, + meta_number = in_meta_number, + business_id = in_business_id, + language_code = in_language, + pricegroup_id = in_pricegroup_id, + curr = in_curr, + startdate = in_startdate, + enddate = in_enddate, + threshold = in_threshold, + discount_terms = in_discount_terms + 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; + END; + +$$ language 'plpgsql'; + +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 out_row; + END LOOP; +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION entity__save_bank_account +(in_entity_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 ; + + RETURN out_id; +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION company__save_contact +(in_entity_id int, in_contact_class int, in_contact text) +RETURNS INT AS +$$ +DECLARE out_id int; +BEGIN + INSERT INTO company_to_contact(company_id, contact_class_id, contact) + SELECT id, in_contact_class, in_contact FROM company + WHERE entity_id = in_entity_id; + + RETURN 1; +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 company__next_id() returns bigint as $$ + + select nextval('company_id_seq'); + +$$ language 'sql'; + +CREATE OR REPLACE FUNCTION company__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_entity_id int, in_location_id int, + 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 + l_row location; + l_id INT; + t_company_id int; + BEGIN + SELECT id INTO t_company_id + FROM company WHERE entity_id = in_entity_id; + + DELETE FROM company_to_location + WHERE company_id = t_company_id + AND location_class = in_location_class + AND location_id = in_location_id; + + SELECT location_save(in_line_one, in_line_two, in_line_three, in_city, + in_state, in_mail_code, in_country_code) + INTO l_id; + + INSERT INTO company_to_location + (company_id, location_class, location_id) + VALUES (t_company_id, in_location_class, l_id); + + RETURN l_id; + END; + +$$ language 'plpgsql'; + +COMMIT; diff --git a/sql/modules/Customer.sql b/sql/modules/Customer.sql index 3ce11f30..a024b9fc 100644 --- a/sql/modules/Customer.sql +++ b/sql/modules/Customer.sql @@ -1,282 +1,5 @@ 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, - id int, - entity_id int, - entity_class int, - discount numeric, - taxincluded bool, - creditlimit numeric, - terms int2, - customernumber text, - business_id int, - language_code text, - pricegroup_id int, - curr char(3), - startdate date, - 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. $$; - -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_business_id int, - in_language varchar(6), in_pricegroup_id int, - in_curr char, in_startdate date, in_enddate date, - in_notes text, - in_name text, in_tax_id TEXT, - in_threshold NUMERIC - -) returns INT as $$ - - DECLARE - t_entity_class int; - new_entity_id int; - v_row company; - l_id int; - BEGIN - - - SELECT INTO v_row * FROM company WHERE id = in_id; - - IF NOT FOUND THEN - -- do some inserts - - select nextval('entity_id_seq') into new_entity_id; - - insert into entity (id, name, entity_class) - VALUES (new_entity_id, in_name, in_entity_class); - - 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, - entity_class, - discount, - taxincluded, - creditlimit, - terms, - meta_number, - business_id, - language_code, - pricegroup_id, - curr, - startdate, - enddate, - discount_terms, - threshold - ) - VALUES ( - new_entity_id, - in_entity_class, - in_discount / 100, - in_taxincluded, - in_creditlimit, - in_terms, - in_meta_number, - in_business_id, - in_language, - in_pricegroup_id, - in_curr, - in_startdate, - in_enddate, - in_discount_terms, - in_threshold - ); - -- 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; - update entity_credit_account SET - discount = in_discount, - taxincluded = in_taxincluded, - creditlimit = in_creditlimit, - terms = in_terms, - meta_number = in_meta_number, - business_id = in_business_id, - language_code = in_language, - pricegroup_id = in_pricegroup_id, - curr = in_curr, - startdate = in_startdate, - enddate = in_enddate, - threshold = in_threshold, - discount_terms = in_discount_terms - 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; - END; - -$$ 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 out_row; - END LOOP; -END; -$$ LANGUAGE PLPGSQL; - -CREATE OR REPLACE FUNCTION entity__save_bank_account -(in_entity_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 ; - - RETURN out_id; -END; -$$ LANGUAGE PLPGSQL; - -CREATE OR REPLACE FUNCTION company__save_contact -(in_entity_id int, in_contact_class int, in_contact text) -RETURNS INT AS -$$ -DECLARE out_id int; -BEGIN - INSERT INTO company_to_contact(company_id, contact_class_id, contact) - SELECT id, in_contact_class, in_contact FROM company - WHERE entity_id = in_entity_id; - - RETURN 1; -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_entity_id int, @@ -327,18 +50,4 @@ CREATE OR REPLACE FUNCTION customer_search(in_pattern TEXT) returns setof custom $$ 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'; - -CREATE OR REPLACE FUNCTION customer_next_customer_id() returns bigint as $$ - - select nextval('company_id_seq'); - -$$ language 'sql'; COMMIT; diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql index c0d57b51..67d197b3 100644 --- a/sql/modules/Location.sql +++ b/sql/modules/Location.sql @@ -19,7 +19,7 @@ $$ DECLARE out_row RECORD; BEGIN FOR out_row IN - SELECT * FROM country ORDER BY id + SELECT * FROM country ORDER BY name LOOP RETURN NEXT out_row; END LOOP; @@ -124,3 +124,14 @@ BEGIN END; $$ 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 +); + diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql index 1e3b640b..1675d5ee 100644 --- a/sql/modules/Vendor.sql +++ b/sql/modules/Vendor.sql @@ -1,214 +1,6 @@ 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_discount_terms int, in_threshold 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, - discount_terms, - threshold - ) - 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, - in_discount_terms, - 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, ''); - - 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, - threshold = in_threshold, - 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; - - 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_entity_id int, in_location_id int, - 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 - l_row location; - l_id INT; - t_company_id int; - BEGIN - SELECT id INTO t_company_id - FROM company WHERE entity_id = in_entity_id; - - DELETE FROM company_to_location - WHERE company_id = t_company_id - AND location_class = in_location_class - AND location_id = in_location_id; - - SELECT location_save(in_line_one, in_line_two, in_line_three, in_city, - in_state, in_mail_code, in_country_code) - INTO l_id; - - INSERT INTO company_to_location - (company_id, location_class, location_id) - VALUES (t_company_id, in_location_class, l_id); - - 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 gist_trgm_ops); -CREATE INDEX entity_name_gist_idx ON entity USING gist(name gist_trgm_ops); +-- TODO: Move indexes to Pg-database CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT, in_city_prov TEXT) @@ -249,17 +41,4 @@ CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT, $$ 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'; - -CREATE OR REPLACE FUNCTION vendor_next_vendor_id() returns bigint as $$ - - select nextval('company_id_seq'); - -$$ language 'sql'; COMMIT; |