Addition of Vendor and Customer-specific save, get, and search functions in Pgsql stored procedures.

Cleanup and modification of Employee.sql, to fit the new Entity framework, as well as the modifications to support trigram searching.
Addition of customer.pl and vendor.pl scripts, to support new perl framework code, as well as .pm modules for the same.

Some cleanup of Pg-database.sql.
addition of note_class = 1 check on entity_note, removal of check id = 1.

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

4 files changed, 120 insertions, 13 deletions

diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm
index b2df1567..dfcb0cc4 100644
--- a/LedgerSMB/AA.pm
+++ b/LedgerSMB/AA.pm
@@ -33,9 +33,31 @@
 #
 #======================================================================
 
+
+
 package AA;
 use LedgerSMB::Sysconfig;
 
+=pod
+
+=head1 post_transaction()
+Post transaction uses the following variables in the $form variable:
+ * dbh - the database connection handle
+ * currency - The current users' currency
+ * defaultcurrency - The "normal" currency
+ * department - Unknown
+ * department_id - ID for the department
+ * exchangerate - Conversion between currency and defaultcurrency
+ * invnumber - invoice number
+ * reverse - ?
+ * rowcount - Number of rows in the invoice
+ * taxaccounts - Apply taxes?
+ * taxincluded - ?
+ * transdate - Date of the transaction
+ * vc - Vendor or customer - determines transaction type
+
+=cut
+
 sub post_transaction {
 
     my ( $self, $myconfig, $form ) = @_;
@@ -278,18 +300,31 @@ sub post_transaction {
 
         my $uid = localtime;
         $uid .= "$$";
-
+        
+        # The query is done like this as the login name maps to the users table
+        # which maps to the user conf table, which links to an entity, to which 
+        # a person is also attached. This is done in this fashion because we 
+        # are using the current username as the "person" inserting the new 
+        # AR/AP Transaction.
+        # ~A
         $query = qq|
-			INSERT INTO $table (invnumber)
-			     VALUES ('$uid')|;
-
-        $dbh->do($query) || $form->dberror($query);
+			INSERT INTO $table (invnumber, person_id)
+			     VALUES (?, (select p.id from person p, entity e, users u
+			                 where u.username = ?
+			                 AND e.id = u.entity_id
+			                 AND p.entity_id = e.id ))|;
+
+        # the second param is undef, as the DBI api expects a hashref of
+        # attributes to pass to $dbh->prepare. This is not used here.
+        # ~A
+        
+        $dbh->do($query,undef,$uid,$form->{login}) || $form->dberror($query);
 
         $query = qq|
 			SELECT id FROM $table
-			 WHERE invnumber = '$uid'|;
+			 WHERE invnumber = ?|;
 
-        ( $form->{id} ) = $dbh->selectrow_array($query);
+        ( $form->{id} ) = $dbh->selectrow_array($query,undef,$uid);
     }
 
     # record last payment date in ar/ap table
@@ -312,7 +347,6 @@ sub post_transaction {
 			curr = ?,
 			notes = ?,
 			department_id = ?,
-			person_id = ?,
 			ponumber = ?
 		WHERE id = ?
 	|;
@@ -324,7 +358,7 @@ sub post_transaction {
         $form->{duedate},       $paid,
         $datepaid,              $invnetamout,
         $form->{currency},      $form->{notes},
-        $form->{department_id}, $form->{employee_id},
+        $form->{department_id},
         $form->{ponumber},      $form->{id}
     );
 
@@ -752,9 +786,9 @@ sub transactions {
 		     FROM $table a
 		     JOIN $form->{vc} vc USING (entity_id)
 		LEFT JOIN employee e ON (a.person_id = e.entity_id)
-		LEFT JOIN employee m ON (e.managerid = m.id)
+		LEFT JOIN employee m ON (e.managerid = m.entity_id)
 		     JOIN entity ee ON (e.entity_id = ee.id)
-                     JOIN entity me ON (m.entity_id = me.id)
+             JOIN entity me ON (m.entity_id = me.id)
 		     JOIN entity vce ON (vc.entity_id = vce.id)
 		LEFT JOIN exchangerate ex ON (ex.curr = a.curr
 		          AND ex.transdate = a.transdate)
@@ -789,6 +823,7 @@ sub transactions {
 
     my $where = "1 = 1";
     if ( $form->{"$form->{vc}_id"} ) {
+        $form->{entity_id} = $form->{$form->{vc}."_id"};
         $where .= qq| AND a.entity_id = $form->{entity_id}|;
     }
     else {
diff --git a/LedgerSMB/Customer.pm b/LedgerSMB/Customer.pm
new file mode 100644
index 00000000..ef25c4a1
--- /dev/null
+++ b/LedgerSMB/Customer.pm
@@ -0,0 +1,33 @@
+package LedgerSMB::DBObject::Customer;
+
+use base qw(LedgerSMB);
+use LedgerSMB::DBObject;
+
+sub save_to_db {
+    
+    my $self = shift @_;
+    
+    my $id;
+    if ($self->{id} >= 1) {
+        $id = $self->{id};
+    }
+    else {
+        $id = $self->next_customer_id();
+    }
+    $id = $self->save($id, $self->{discount}, $self->{tax_included}, 
+        $self->{creditlimit}, $self->{terms}, $self->{customernumber}, 
+        $self->{cc}, $self->{bcc}, $self->{business_id}, $self->{language},
+        $self->{pricegroup}, $self->{currency}, $self->{startdate}, 
+        $self->{enddate}
+    );
+    
+    # Undef in the created field causes the system to use now() as the current
+    # creation date.
+    $self->location_save(
+        $id, 1, $self->{line_one}, $self->{line_two}, $self->{line_three},
+        $self->{city_province}, $self->{mailing_code}, $self->{country}, undef
+        
+    );
+    return $id;
+}
+1;
\ No newline at end of file
diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm
index d3db12e8..c8d39f5f 100644
--- a/LedgerSMB/IC.pm
+++ b/LedgerSMB/IC.pm
@@ -1829,7 +1829,10 @@ sub create_links {
         my ($count) = $dbh->selectrow_array($query);
 
         if ( $count < $myconfig->{vclimit} ) {
-            $query = qq|SELECT id, name FROM vendor ORDER BY name|;
+            $query = qq|SELECT v.id, e.name 
+                FROM vendor v 
+                join entity e on e.id = v.entity_id 
+                ORDER BY e.name|;
             $sth   = $dbh->prepare($query);
             $sth->execute || $form->dberror($query);
 
@@ -1845,7 +1848,10 @@ sub create_links {
     ($count) = $dbh->selectrow_array($query);
 
     if ( $count < $myconfig->{vclimit} ) {
-        $query = qq|SELECT id, name FROM customer ORDER BY name|;
+        $query = qq|SELECT c.id, e.name 
+            FROM customer c 
+            join entity e on e.id = c.entity_id 
+            ORDER BY e.name|;
         $sth   = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
 
diff --git a/LedgerSMB/Vendor.pm b/LedgerSMB/Vendor.pm
new file mode 100644
index 00000000..52aa4944
--- /dev/null
+++ b/LedgerSMB/Vendor.pm
@@ -0,0 +1,33 @@
+package LedgerSMB::DBObject::Vendor;
+
+use base qw(LedgerSMB);
+use LedgerSMB::DBObject;
+
+sub save_to_db {
+    
+    my $self = shift @_;
+    
+    my $id;
+    if ($self->{id} >= 1) {
+        $id = $self->{id};
+    }
+    else {
+        $id = $self->next_vendor_id();
+    }
+    $id = $self->save($id, $self->{discount}, $self->{tax_included}, 
+        $self->{creditlimit}, $self->{terms}, $self->{vendornumber}, 
+        $self->{cc}, $self->{bcc}, $self->{business_id}, $self->{language},
+        $self->{pricegroup}, $self->{currency}, $self->{startdate}, 
+        $self->{enddate}
+    );
+    
+    # Undef in the created field causes the system to use now() as the current
+    # creation date.
+    $self->location_save(
+        $id, 1, $self->{line_one}, $self->{line_two}, $self->{line_three},
+        $self->{city_province}, $self->{mailing_code}, $self->{country}, undef
+        
+    );
+    return $id;
+}
+1;
\ No newline at end of file