Fixes Add Vendor, to correctly work with the new entity_credit_account table, in place of the customer/vendor tables.

Removes the Vendor and Customer tables, replacing them with entity_credit_account (previously entity_metadata) and Vendor and Customer as a view, mimicking the original Vendor and Customer tables.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1373 4979c152-3d1c-0410-bac9-87ea11338e46

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 (