[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [1899] trunk
- Subject: SF.net SVN: ledger-smb: [1899] trunk
- From: ..hidden..
- Date: Sun, 25 Nov 2007 00:12:13 -0800
Revision: 1899
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1899&view=rev
Author: einhverfr
Date: 2007-11-25 00:12:12 -0800 (Sun, 25 Nov 2007)
Log Message:
-----------
Contact management postfactoring
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Customer.pm
trunk/LedgerSMB/Entity.pm
trunk/sql/Pg-database.sql
trunk/sql/modules/Customer.sql
trunk/sql/modules/Location.sql
trunk/sql/modules/Vendor.sql
Added Paths:
-----------
trunk/LedgerSMB/DBObject/Company.pm
trunk/sql/modules/Company.sql
Added: trunk/LedgerSMB/DBObject/Company.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Company.pm (rev 0)
+++ trunk/LedgerSMB/DBObject/Company.pm 2007-11-25 08:12:12 UTC (rev 1899)
@@ -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;
Modified: trunk/LedgerSMB/DBObject/Customer.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Customer.pm 2007-11-24 08:09:03 UTC (rev 1898)
+++ trunk/LedgerSMB/DBObject/Customer.pm 2007-11-25 08:12:12 UTC (rev 1899)
@@ -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 @@
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();
}
Modified: trunk/LedgerSMB/Entity.pm
===================================================================
--- trunk/LedgerSMB/Entity.pm 2007-11-24 08:09:03 UTC (rev 1898)
+++ trunk/LedgerSMB/Entity.pm 2007-11-25 08:12:12 UTC (rev 1899)
@@ -40,4 +40,4 @@
]
) };
}
-1;
\ No newline at end of file
+1;
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2007-11-24 08:09:03 UTC (rev 1898)
+++ trunk/sql/Pg-database.sql 2007-11-25 08:12:12 UTC (rev 1899)
@@ -2666,4 +2666,12 @@
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;
Added: trunk/sql/modules/Company.sql
===================================================================
--- trunk/sql/modules/Company.sql (rev 0)
+++ trunk/sql/modules/Company.sql 2007-11-25 08:12:12 UTC (rev 1899)
@@ -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;
Modified: trunk/sql/modules/Customer.sql
===================================================================
--- trunk/sql/modules/Customer.sql 2007-11-24 08:09:03 UTC (rev 1898)
+++ trunk/sql/modules/Customer.sql 2007-11-25 08:12:12 UTC (rev 1899)
@@ -1,283 +1,6 @@
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,
in_location_class int, in_line_one text, in_line_two text,
@@ -327,18 +50,4 @@
$$ 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;
Modified: trunk/sql/modules/Location.sql
===================================================================
--- trunk/sql/modules/Location.sql 2007-11-24 08:09:03 UTC (rev 1898)
+++ trunk/sql/modules/Location.sql 2007-11-25 08:12:12 UTC (rev 1899)
@@ -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 @@
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
+);
+
Modified: trunk/sql/modules/Vendor.sql
===================================================================
--- trunk/sql/modules/Vendor.sql 2007-11-24 08:09:03 UTC (rev 1898)
+++ trunk/sql/modules/Vendor.sql 2007-11-25 08:12:12 UTC (rev 1899)
@@ -1,215 +1,7 @@
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
-);
+-- TODO: Move indexes to Pg-database
-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);
-
CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT,
in_city_prov TEXT)
RETURNS SETOF vendor_search_return AS $$
@@ -249,17 +41,4 @@
$$ 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;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.