diff options
| -rw-r--r-- | LedgerSMB/CT.pm | 72 | ||||
| -rw-r--r-- | LedgerSMB/Form.pm | 13 | ||||
| -rw-r--r-- | sql/Pg-database.sql | 110 |
3 files changed, 127 insertions, 68 deletions
diff --git a/LedgerSMB/CT.pm b/LedgerSMB/CT.pm index 6aa07459..768f2196 100644 --- a/LedgerSMB/CT.pm +++ b/LedgerSMB/CT.pm @@ -209,7 +209,8 @@ sub _save_vc { $form->{vc} = 'vendor'; $form->{entity_class} = 1; } - + + # this should really all be replaced by an upsert. if ( $form->{id} ) { $query = qq| DELETE FROM $form->{vc}tax @@ -231,7 +232,7 @@ sub _save_vc { if ( $sth->fetchrow_array ) { $sth->finish; $query = qq| - UPDATE $form->{vc} + UPDATE enti SET discount = ? taxincluded = ? creditlimit = ? @@ -265,7 +266,7 @@ sub _save_vc { if (!$updated){ # Creating Entity ($form->{entity_id}) = $dbh->selectrow_array("SELECT nextval('entity_id_seq')"); - $query = qq|INSERT INTO entity (id, name, entity_class) VALUES (?, ?)|; + $query = qq|INSERT INTO entity (id, name, entity_class) VALUES (?, ?,?)|; $sth = $dbh->prepare($query); $sth->execute($form->{entity_id}, $form->{name}, $form->{entity_class}); $sth->finish; @@ -276,9 +277,9 @@ sub _save_vc { $query = qq| INSERT INTO location (id, line_one, line_two, city_province, mail_code, - country_id, location_class,created) + country_id, location_class, created) VALUES - (?, ?, ?, ?, + (?, ?, ?, ?, ?, (SELECT id FROM country WHERE short_name = ? OR name = ?), 1, current_date) @@ -291,43 +292,60 @@ sub _save_vc { #Creating company - # Removed entity_class_id ~Aurynn - # removed primary_location_id ~Aurynn + # Removed entity_class_id, + # removed primary_location_id, + # added sic_code ~Aurynn $query = qq| INSERT INTO company - (entity_id, legal_name, tax_id) + (entity_id, legal_name, tax_id, sic_code) VALUES - (?, ?, ?) + (?, ?, ?, ?) |; $sth = $dbh->prepare($query) || $form->dberror($query); $sth->execute($form->{entity_id}, # $form->{entity_class}, # removed entity_class_id ~Aurynn $form->{name}, # $form->{location_id}, # removed by ~aurynn - $form->{taxnumber}); - #Creating customer record + $form->{taxnumber}, + $form->{sic_code}); + # Creating entity_metadata record, replacing customer and vendor. + $query = qq| - INSERT INTO customer - (entity_id, discount, taxincluded, creditlimit, terms, - customernumber, cc, bcc, business_id, sic_code, - language_code, pricegroup_id, curr, startdate, - enddate, invoice_notes, bic, iban) - - VALUES (?, ?, ?, ?, ?, - ?, ?, ?, ?, ?, - ?, ?, ?, ?, - ?, ?, ?, ?)|; - + INSERT INTO entity_credit_account + (entity_id, entity_class, discount, taxincluded, creditlimit, + terms, meta_number, cc, bcc, business_id, + language_code, pricegroup_id, curr, startdate, + enddate) + + VALUES (?, ?, ?, ?, ?, + ?, ?, ?, ?, + ?, ?, ?, ?, + ?, ?)|; + $sth = $dbh->prepare($query); $sth->execute( - $form->{entity_id}, $form->{discount}, $form->{taxincluded}, - $form->{creditlimit}, + $form->{entity_id}, $form->{entity_class}, $form->{discount}, + $form->{taxincluded}, $form->{creditlimit}, $form->{terms}, $form->{"$form->{vc}number"}, $form->{cc}, - $form->{bcc}, $form->{business_id}, $form->{sic_code}, + $form->{bcc}, $form->{business_id}, $form->{language_code}, $form->{pricegroup_id}, $form->{curr}, $form->{startdate} || undef, $form->{enddate} || undef, - $form->{invoice_notes}, - $form->{bic}, $form->{iban} ) || $form->dberror($query); + + $query = qq| + INSERT INTO entity_bank_account (entity_id, bic, iban) + VALUES (?,?,?) + |; + $sth = $dbh->prepare($query); + $sth->execute($form->{entity_id}, $form->{bic}, $form->{iban}) || + $form->dberror($query); + $query = qq| + insert into entity_invoice_notes (entity_id, note) + values (?, ?) + |; + $sth = $dbh->prepare($query); + $sth->execute($form->{entity_id}, $form->{notes}) || + $form->dberror($query); + } } diff --git a/LedgerSMB/Form.pm b/LedgerSMB/Form.pm index 7d263fdd..0633ced5 100644 --- a/LedgerSMB/Form.pm +++ b/LedgerSMB/Form.pm @@ -1197,9 +1197,11 @@ sub get_name { @queryargs = ( $transdate, $transdate ); } - + + # Company name is stored in $self->{vendor} or $self->{customer} my $name = $self->like( lc $self->{$table} ); - + + # Vendor and Customer are now views into entity_credit_account. my $query = qq| SELECT * FROM $table t JOIN entity e ON t.entity_id = e.id @@ -1207,23 +1209,20 @@ sub get_name { $where ORDER BY e.name|; - unshift( @queryargs, $name, $name ); + unshift( @queryargs, $name, $name, $table ); my $sth = $self->{dbh}->prepare($query); $sth->execute(@queryargs) || $self->dberror($query); my $i = 0; @{ $self->{name_list} } = (); - while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { push( @{ $self->{name_list} }, $ref ); $i++; } - $sth->finish; - $i; - + return $i; } sub all_vc { diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 9cb6c87f..a5cb9e31 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -324,21 +324,18 @@ ALTER TABLE invoice_note ADD FOREIGN KEY (ref_key) REFERENCES invoice(id); -- -- -CREATE TABLE customer ( - id serial PRIMARY KEY, - entity_id int references entity(id), + +CREATE TABLE entity_credit_account ( + entity_id int not null references entity(id), + entity_class int not null references entity_class(id) check ( entity_class in (1,2) ), discount numeric, taxincluded bool default 'f', creditlimit NUMERIC default 0, terms int2 default 0, - customernumber varchar(32), + meta_number varchar(32), cc text, bcc text, business_id int, - invoice_notes text, - sic_code varchar(6), - iban varchar(34), - bic varchar(11), language_code varchar(6), pricegroup_id int, curr char(3), @@ -346,9 +343,78 @@ CREATE TABLE customer ( enddate date ); -COMMENT ON TABLE customer IS $$ This is now a metadata table that holds information specific to customers. Source info is not part of the entity management $$; -COMMENT ON COLUMN customer.entity_id IS $$ This is the relationship between entities and customers $$; +CREATE TABLE entity_bank_account ( + id serial not null, + entity_id int references entity(id) not null, + bic varchar, + iban varchar, + UNIQUE (id), + PRIMARY KEY (bic, iban) +); + +CREATE TABLE entity_invoice_notes ( + id serial not null, + entity_id int not null references entity(id), + note text, + unique (id) +); +CREATE VIEW customer AS + SELECT emd.entity_id, + emd.entity_class, + emd.discount, + emd.taxincluded, + emd.creditlimit, + emd.terms, + emd.meta_number as customernumber, + emd.cc, + emd.bcc, + emd.business_id, + emd.language_code, + emd.pricegroup_id, + emd.curr, + emd.startdate, + emd.enddate, + eba.bic, + eba.iban, + ein.note as + invoice_notes + FROM entity_credit_account emd + join entity_bank_account eba on emd.entity_id = eba.entity_id + join entity_invoice_notes ein on ein.entity_id = emd.entity_id + where emd.entity_class = 2; + +CREATE VIEW vendor AS + SELECT emd.entity_id, + emd.entity_class, + emd.discount, + emd.taxincluded, + emd.creditlimit, + emd.terms, + emd.meta_number as vendornumber, + emd.cc, + emd.bcc, + emd.business_id, + emd.language_code, + emd.pricegroup_id, + emd.curr, + emd.startdate, + emd.enddate, + eba.bic, + eba.iban, + ein.note as + invoice_notes + FROM entity_credit_account emd + join entity_bank_account eba on emd.entity_id = eba.entity_id + join entity_invoice_notes ein on ein.entity_id = emd.entity_id + where emd.entity_class = 1; + +COMMENT ON TABLE entity_metadata IS $$ This is a metadata table for ALL entities in LSMB; it deprecates the use of customer and vendor specific tables (which were nearly identical and largely redundant), and replaces it with a single point of metadata. $$; + +COMMENT ON COLUMN entity_metadata.entity_id IS $$ This is the relationship between entities and their metadata. $$; +COMMENT ON COLUMN entity_metadata.entity_class IS $$ A reference to entity_class, requiring that entity_metadata only apply to vendors and customers, using the entity_class table as the Point Of Truth. $$; + +ALTER TABLE company ADD COLUMN sic_code varchar; -- -- @@ -634,30 +700,6 @@ create table shipto ( -- SHIPTO really needs to be pushed into entities too -- -CREATE TABLE vendor ( - entity_id int references entity(id) not null PRIMARY KEY, - entity_class_id int references entity_class(id) not null check (entity_class_id = 1), - terms int2 default 0, - taxincluded bool default 'f', - vendornumber varchar(32), - cc text, - bcc text, - gifi_accno varchar(30), - business_id int, - taxnumber varchar(32), - sic_code varchar(6), - discount numeric, - creditlimit numeric default 0, - iban varchar(34), - bic varchar(11), - language_code varchar(6), - pricegroup_id int, - curr char(3), - startdate date, - enddate date -); - -COMMENT ON TABLE vendor IS $$ Now a meta data table $$; -- CREATE TABLE project ( |