import sql-ledger 2.4.4

[freeside.git] / sql-ledger / SL / CT.pm

diff --git a/sql-ledger/SL/CT.pm b/sql-ledger/SL/CT.pm
index 7c42cb8..bfcc219 100644 (file)
--- a/sql-ledger/SL/CT.pm
+++ b/sql-ledger/SL/CT.pm
@@ -1,6 +1,6 @@
 #=====================================================================
 # SQL-Ledger Accounting
 #=====================================================================
 # SQL-Ledger Accounting

-# Copyright (C) 2001
+# Copyright (C) 2000

 #
 #  Author: Dieter Simader
 #   Email: dsimader@sql-ledger.org
 #
 #  Author: Dieter Simader
 #   Email: dsimader@sql-ledger.org


@@ -24,51 +24,87 @@
 #
 # backend code for customers and vendors
 #
 #
 # backend code for customers and vendors
 #

-# CHANGE LOG:
-#   DS. 2000-07-04  Created
-#

 #======================================================================
 
 package CT;
 
 
 #======================================================================
 
 package CT;
 
 

-sub get_tuple {
+sub create_links {

   my ($self, $myconfig, $form) = @_;
 
   my $dbh = $form->dbconnect($myconfig);
   my ($self, $myconfig, $form) = @_;
 
   my $dbh = $form->dbconnect($myconfig);

-  my $query = qq|SELECT *
-                 FROM $form->{db}
-                 WHERE id = $form->{id}|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  my $query;
+  my $sth;
+  my $ref;
+
+  if ($form->{id}) {
+    $query = qq|SELECT ct.*, b.description AS business, s.*,
+                e.name AS employee, g.pricegroup AS pricegroup,
+               l.description AS language, ct.curr
+                FROM $form->{db} ct
+               LEFT JOIN business b ON (ct.business_id = b.id)
+               LEFT JOIN shipto s ON (ct.id = s.trans_id)
+               LEFT JOIN employee e ON (ct.employee_id = e.id)
+               LEFT JOIN pricegroup g ON (g.id = ct.pricegroup_id)
+               LEFT JOIN language l ON (l.code = ct.language_code)
+                WHERE ct.id = $form->{id}|;
+    $sth = $dbh->prepare($query);
+    $sth->execute || $form->dberror($query);

   
   

-  my $ref = $sth->fetchrow_hashref(NAME_lc);
+    $ref = $sth->fetchrow_hashref(NAME_lc);

   
   

-  map { $form->{$_} = $ref->{$_} } keys %$ref;
+    map { $form->{$_} = $ref->{$_} } keys %$ref;

 
 

-  $sth->finish;
+    $sth->finish;

 
 

+    # check if it is orphaned
+    my $arap = ($form->{db} eq 'customer') ? "ar" : "ap";
+    $query = qq|SELECT a.id
+              FROM $arap a
+             JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
+             WHERE ct.id = $form->{id}
+           UNION
+             SELECT a.id
+             FROM oe a
+             JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
+             WHERE ct.id = $form->{id}|;
+    $sth = $dbh->prepare($query);
+    $sth->execute || $form->dberror($query);
+  
+    unless ($sth->fetchrow_array) {
+      $form->{status} = "orphaned";
+    }
+    $sth->finish;

 
 

-  # get ship to
-  $query = qq|SELECT *
-              FROM shipto
-             WHERE trans_id = $form->{id}|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);

 
 

-  $ref = $sth->fetchrow_hashref(NAME_lc);
+    # get taxes for customer/vendor
+    $query = qq|SELECT c.accno
+               FROM chart c
+               JOIN $form->{db}tax t ON (t.chart_id = c.id)
+               WHERE t.$form->{db}_id = $form->{id}|;
+    $sth = $dbh->prepare($query);
+    $sth->execute || $form->dberror($query);

 
 

-  map { $form->{$_} = $ref->{$_} } keys %$ref;
+    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+      $form->{tax}{$ref->{accno}}{taxable} = 1;
+    }
+    $sth->finish;

 
 

-  $sth->finish;
+  } else {
+
+    ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);

 
 

+    $query = qq|SELECT current_date FROM defaults|;
+    ($form->{startdate}) = $dbh->selectrow_array($query);
+
+  }

 
   # get tax labels
 
   # get tax labels

-  $query = qq|SELECT accno, description
-              FROM chart, tax
-             WHERE link LIKE '%CT_tax%'
-             AND chart.id = tax.chart_id
-             ORDER BY accno|;
+  $query = qq|SELECT c.accno, c.description
+              FROM chart c
+             JOIN tax t ON (t.chart_id = c.id)
+             WHERE c.link LIKE '%CT_tax%'
+             ORDER BY c.accno|;

   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 


@@ -79,94 +115,67 @@ sub get_tuple {
   $sth->finish;
   chop $form->{taxaccounts};
 
   $sth->finish;
   chop $form->{taxaccounts};
 

-  # get taxes for customer/vendor
-  $query = qq|SELECT chart_id, accno
-              FROM $form->{db}tax, chart
-              WHERE chart_id = chart.id
-              AND $form->{db}_id = $form->{id}|;
+    
+  # get business types
+  $query = qq|SELECT *
+              FROM business
+             ORDER BY 2|;

   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);

-
+  

   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

-    $form->{tax}{$ref->{accno}}{taxable} = 1;
+    push @{ $form->{all_business} }, $ref;

   }
   $sth->finish;
   }
   $sth->finish;

-

   
   

-  $dbh->disconnect;
-
-}
-
-
-sub taxaccounts {
-  my ($self, $myconfig, $form) = @_;
-
-  my $dbh = $form->dbconnect($myconfig);
-
-  # get tax labels
-  my $query = qq|SELECT accno, description
-                 FROM chart, tax
-                WHERE link LIKE '%CT_tax%'
-                AND chart.id = tax.chart_id
-                ORDER BY accno|;
+  # this is for the salesperson
+  $query = qq|SELECT id, name
+              FROM employee
+             WHERE sales = '1'
+             ORDER BY 2|;

   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);

-
-  while (my $taxref = $sth->fetchrow_hashref(NAME_lc)) {
-    $form->{taxaccounts} .= "$taxref->{accno} ";
-    $form->{tax}{$taxref->{accno}}{description} = $taxref->{description};
+  
+  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    push @{ $form->{all_employee} }, $ref;

   }
   $sth->finish;
   }
   $sth->finish;

-  chop $form->{taxaccounts};
-
-  $dbh->disconnect;
-
-}
-
-
-sub delete_customer {
-  my ($self, $myconfig, $form) = @_;
-
-  # connect to database, turn AutoCommit off
-  my $dbh = $form->dbconnect_noauto($myconfig);
-
-  my $query = qq|SELECT id FROM ar
-                 WHERE customer_id = $form->{id}
-                UNION
-                SELECT id FROM oe
-                WHERE customer_id = $form->{id}|;
-  my $sth = $dbh->prepare($query) || $form->dberror($query);
-  $sth->execute;

 
 

-  my ($rc) = $sth->fetchrow_array;
+  # get language
+  $query = qq|SELECT *
+              FROM language
+             ORDER BY 2|;
+  $sth = $dbh->prepare($query);
+  $sth->execute || $form->dberror($query);
+  
+  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    push @{ $form->{all_language} }, $ref;
+  }

   $sth->finish;
   $sth->finish;

-
-  if ($rc) {
-    $dbh->disconnect;
-    $rc = -1;
-  } else {
-    
-    # delete customer
-    $query = qq|DELETE FROM customer
-                WHERE id = $form->{id}|;
-    $dbh->do($query) || $form->dberror($query);
-
-    $query = qq|DELETE FROM shipto
-                WHERE trans_id = $form->{id}|;
-    $dbh->do($query) || $form->dberror($query);
-    
-    $query = qq|DELETE FROM customertax
-                WHERE customer_id = $form->{id}|;
-    $dbh->do($query) || $form->dberror($query);
-
-    # commit and redirect
-    $rc = $dbh->commit;
-    $dbh->disconnect;
-    
+ 
+  # get pricegroups
+  $query = qq|SELECT *
+              FROM pricegroup
+             ORDER BY 2|;
+  $sth = $dbh->prepare($query);
+  $sth->execute || $form->dberror($query);
+  
+  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    push @{ $form->{all_pricegroup} }, $ref;

   }
   }

-
-  $rc;
+  $sth->finish;
+  
+  # get currencies
+  $query = qq|SELECT curr AS currencies
+              FROM defaults|;
+  $sth = $dbh->prepare($query);
+  $sth->execute || $form->dberror($query);

   
   

+  ($form->{currencies}) = $sth->fetchrow_array;
+  $sth->finish;
+
+  $dbh->disconnect;
+

 }
 
 
 }
 
 


@@ -174,18 +183,24 @@ sub save_customer {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
   my ($self, $myconfig, $form) = @_;
 
   # connect to database

-  my $dbh = $form->dbconnect($myconfig);
-
-  # escape '
-  map { $form->{$_} =~ s/'/''/g } qw(customernumber name addr1 addr2 addr3 addr4 contact notes);
-
+  my $dbh = $form->dbconnect_noauto($myconfig);
+  my $query;
+  my $sth;
+  my $null;
+  
+  # remove double spaces
+  $form->{name} =~ s/  / /g;
+  # remove double minus and minus at the end
+  $form->{name} =~ s/--+/-/g;
+  $form->{name} =~ s/-+$//;
+  

   # assign value discount, terms, creditlimit
   # assign value discount, terms, creditlimit

+  $form->{discount} = $form->parse_amount($myconfig, $form->{discount});

   $form->{discount} /= 100;
   $form->{terms} *= 1;
   $form->{taxincluded} *= 1;
   $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
   
   $form->{discount} /= 100;
   $form->{terms} *= 1;
   $form->{taxincluded} *= 1;
   $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
   

-  my ($query, $sth);

 
   if ($form->{id}) {
     $query = qq|DELETE FROM customertax
 
   if ($form->{id}) {
     $query = qq|DELETE FROM customertax


@@ -195,6 +210,15 @@ sub save_customer {
     $query = qq|DELETE FROM shipto
                 WHERE trans_id = $form->{id}|;
     $dbh->do($query) || $form->dberror($query);
     $query = qq|DELETE FROM shipto
                 WHERE trans_id = $form->{id}|;
     $dbh->do($query) || $form->dberror($query);

+
+    # retrieve enddate
+    if ($form->{type} && $form->{enddate}) {
+      my $now;
+      $query = qq|SELECT enddate, current_date AS now FROM customer|;
+      ($form->{enddate}, $now) = $dbh->selectrow_array($query);
+      $form->{enddate} = $now if $form->{enddate} lt $now;
+    }
+

   } else {
     my $uid = time;
     $uid .= $form->{login};
   } else {
     my $uid = time;
     $uid .= $form->{login};


@@ -212,25 +236,55 @@ sub save_customer {
     $sth->finish;
 
   }
     $sth->finish;
 
   }

-               
+
+  my $employee_id;
+  ($null, $employee_id) = split /--/, $form->{employee};
+  $employee_id *= 1;
+  
+  my $pricegroup_id;
+  ($null, $pricegroup_id) = split /--/, $form->{pricegroup};
+  $pricegroup_id *= 1;
+
+  my $business_id;
+  ($null, $business_id) = split /--/, $form->{business};
+  $business_id *= 1;
+
+  my $language_code;
+  ($null, $language_code) = split /--/, $form->{language};
+
+  $form->{customernumber} = $form->update_defaults($myconfig, "customernumber", $dbh) if ! $form->{customernumber};
+  

   $query = qq|UPDATE customer SET
   $query = qq|UPDATE customer SET

-              customernumber = '$form->{customernumber}',
-             name = '$form->{name}',
-             addr1 = '$form->{addr1}',
-             addr2 = '$form->{addr2}',
-             addr3 = '$form->{addr3}',
-             addr4 = '$form->{addr4}',
-             contact = '$form->{contact}',
+              customernumber = |.$dbh->quote($form->{customernumber}).qq|,
+             name = |.$dbh->quote($form->{name}).qq|,
+             address1 = |.$dbh->quote($form->{address1}).qq|,
+             address2 = |.$dbh->quote($form->{address2}).qq|,
+             city = |.$dbh->quote($form->{city}).qq|,
+             state = |.$dbh->quote($form->{state}).qq|,
+             zipcode = |.$dbh->quote($form->{zipcode}).qq|,
+             country = |.$dbh->quote($form->{country}).qq|,
+             contact = |.$dbh->quote($form->{contact}).qq|,

              phone = '$form->{phone}',
              fax = '$form->{fax}',
              email = '$form->{email}',
              cc = '$form->{cc}',
              bcc = '$form->{bcc}',
              phone = '$form->{phone}',
              fax = '$form->{fax}',
              email = '$form->{email}',
              cc = '$form->{cc}',
              bcc = '$form->{bcc}',

-             notes = '$form->{notes}',
+             notes = |.$dbh->quote($form->{notes}).qq|,

              discount = $form->{discount},
              creditlimit = $form->{creditlimit},
              terms = $form->{terms},
              discount = $form->{discount},
              creditlimit = $form->{creditlimit},
              terms = $form->{terms},

-             taxincluded = '$form->{taxincluded}'
+             taxincluded = '$form->{taxincluded}',
+             business_id = $business_id,
+             taxnumber = |.$dbh->quote($form->{taxnumber}).qq|,
+             sic_code = '$form->{sic}',
+             iban = '$form->{iban}',
+             bic = '$form->{bic}',
+             employee_id = $employee_id,
+             pricegroup_id = $pricegroup_id,
+             language_code = '$language_code',
+             curr = '$form->{curr}',
+             startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
+             enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|

              WHERE id = $form->{id}|;
   $dbh->do($query) || $form->dberror($query);
 
              WHERE id = $form->{id}|;
   $dbh->do($query) || $form->dberror($query);
 


@@ -248,6 +302,7 @@ sub save_customer {
   # add shipto
   $form->add_shipto($dbh, $form->{id});
 
   # add shipto
   $form->add_shipto($dbh, $form->{id});
 

+  $dbh->commit;

   $dbh->disconnect;
 
 }
   $dbh->disconnect;
 
 }


@@ -257,15 +312,24 @@ sub save_vendor {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
   my ($self, $myconfig, $form) = @_;
 
   # connect to database

-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = $form->dbconnect_noauto($myconfig);

 
 

-  # escape '
-  map { $form->{$_} =~ s/'/''/g } qw(vendornumber name addr1 addr2 addr3 addr4 contact notes);
+  my $query;
+  my $sth;
+  my $null;
+
+  # remove double spaces
+  $form->{name} =~ s/  / /g;
+  # remove double minus and minus at the end
+  $form->{name} =~ s/--+/-/g;
+  $form->{name} =~ s/-+$//;

 
 

+  $form->{discount} = $form->parse_amount($myconfig, $form->{discount});
+  $form->{discount} /= 100;

   $form->{terms} *= 1;
   $form->{taxincluded} *= 1;
   $form->{terms} *= 1;
   $form->{taxincluded} *= 1;

-  
-  my $query;
+  $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
+ 

   
   if ($form->{id}) {
     $query = qq|DELETE FROM vendortax
   
   if ($form->{id}) {
     $query = qq|DELETE FROM vendortax


@@ -293,23 +357,55 @@ sub save_vendor {
 
   }
    
 
   }
    

-
+  my $employee_id;
+  ($null, $employee_id) = split /--/, $form->{employee};
+  $employee_id *= 1;
+  
+  my $pricegroup_id;
+  ($null, $pricegroup_id) = split /--/, $form->{pricegroup};
+  $pricegroup_id *= 1;
+
+  my $business_id;
+  ($null, $business_id) = split /--/, $form->{business};
+  $business_id *= 1;
+
+  my $language_code;
+  ($null, $language_code) = split /--/, $form->{language};
+ 
+  $form->{vendornumber} = $form->update_defaults($myconfig, "vendornumber", $dbh) if ! $form->{vendornumber};
+  

   $query = qq|UPDATE vendor SET
   $query = qq|UPDATE vendor SET

-              vendornumber = '$form->{vendornumber}',
-             name = '$form->{name}',
-             addr1 = '$form->{addr1}',
-             addr2 = '$form->{addr2}',
-             addr3 = '$form->{addr3}',
-             addr4 = '$form->{addr4}',
-             contact = '$form->{contact}',
+              vendornumber = |.$dbh->quote($form->{vendornumber}).qq|,
+             name = |.$dbh->quote($form->{name}).qq|,
+             address1 = |.$dbh->quote($form->{address1}).qq|,
+             address2 = |.$dbh->quote($form->{address2}).qq|,
+             city = |.$dbh->quote($form->{city}).qq|,
+             state = |.$dbh->quote($form->{state}).qq|,
+             zipcode = |.$dbh->quote($form->{zipcode}).qq|,
+             country = |.$dbh->quote($form->{country}).qq|,
+             contact = |.$dbh->quote($form->{contact}).qq|,

              phone = '$form->{phone}',
              fax = '$form->{fax}',
              email = '$form->{email}',
              cc = '$form->{cc}',
              bcc = '$form->{bcc}',
              phone = '$form->{phone}',
              fax = '$form->{fax}',
              email = '$form->{email}',
              cc = '$form->{cc}',
              bcc = '$form->{bcc}',

-             notes = '$form->{notes}',
+             notes = |.$dbh->quote($form->{notes}).qq|,

              terms = $form->{terms},
              terms = $form->{terms},

-             taxincluded = '$form->{taxincluded}'
+             discount = $form->{discount},
+             creditlimit = $form->{creditlimit},
+             taxincluded = '$form->{taxincluded}',
+             gifi_accno = '$form->{gifi_accno}',
+             business_id = $business_id,
+             taxnumber = |.$dbh->quote($form->{taxnumber}).qq|,
+             sic_code = '$form->{sic}',
+             iban = '$form->{iban}',
+             bic = '$form->{bic}',
+             employee_id = $employee_id,
+             language_code = '$language_code',
+             pricegroup_id = $pricegroup_id,
+             curr = '$form->{curr}',
+             startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
+             enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|

              WHERE id = $form->{id}|;
   $dbh->do($query) || $form->dberror($query);
 
              WHERE id = $form->{id}|;
   $dbh->do($query) || $form->dberror($query);
 


@@ -327,113 +423,422 @@ sub save_vendor {
   # add shipto
   $form->add_shipto($dbh, $form->{id});
 
   # add shipto
   $form->add_shipto($dbh, $form->{id});
 

+  $dbh->commit;

   $dbh->disconnect;
 
 }
 
 
 
   $dbh->disconnect;
 
 }
 
 
 

-sub delete_vendor {
+sub delete {

   my ($self, $myconfig, $form) = @_;
 
   my ($self, $myconfig, $form) = @_;
 

-  # connect to database, turn AutoCommit off
-  my $dbh = $form->dbconnect_noauto($myconfig);
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+
+  # delete customer/vendor
+  my $query = qq|DELETE FROM $form->{db}
+                WHERE id = $form->{id}|;
+  $dbh->do($query) || $form->dberror($query);
+
+  $dbh->disconnect;

 
 

-  # check if there are any transactions on file
-  my $query = qq|SELECT id FROM ap
-                 WHERE vendor_id = $form->{id}
-                UNION
-                SELECT id FROM oe
-                WHERE vendor_id = $form->{id}|;
-  my $sth = $dbh->prepare($query) || $form->dberror($query);
-  $sth->execute;
+}
+
+
+sub search {
+  my ($self, $myconfig, $form) = @_;
+
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+
+  my $where = "1 = 1";
+  $form->{sort} = ($form->{sort}) ? $form->{sort} : "name";
+  my @a = qw(name);
+  my $sortorder = $form->sort_order(\@a);
+
+  my $var;
+  my $item;
+
+  @a = ("$form->{db}number");
+  push @a, qw(name contact city state zipcode country notes email);

   
   

-  my ($rc) = $sth->fetchrow_array;
-  $sth->finish;
+  foreach $item (@a) {
+    if ($form->{$item}) {
+      $var = $form->like(lc $form->{$item});
+      $where .= " AND lower(ct.$item) LIKE '$var'";
+    }
+  }
+  if ($form->{address}) {
+    $var = $form->like(lc $form->{address});
+    $where .= " AND (lower(ct.address1) LIKE '$var' OR lower(ct.address2) LIKE '$var')";
+  }
+
+  if ($form->{status} eq 'orphaned') {
+    $where .= qq| AND ct.id NOT IN (SELECT o.$form->{db}_id
+                                    FROM oe o, $form->{db} cv
+                                   WHERE cv.id = o.$form->{db}_id)|;
+    if ($form->{db} eq 'customer') {
+      $where .= qq| AND ct.id NOT IN (SELECT a.customer_id
+                                      FROM ar a, customer cv
+                                     WHERE cv.id = a.customer_id)|;
+    }
+    if ($form->{db} eq 'vendor') {
+      $where .= qq| AND ct.id NOT IN (SELECT a.vendor_id
+                                      FROM ap a, vendor cv
+                                     WHERE cv.id = a.vendor_id)|;
+    }
+    $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
+  }

   
   

-  if ($rc) {
-    $dbh->disconnect;
-    $rc = -1;
-  } else {
+
+  my $query = qq|SELECT ct.*, b.description AS business,
+                 e.name AS employee, g.pricegroup, l.description AS language,
+                m.name AS manager
+                 FROM $form->{db} ct
+             LEFT JOIN business b ON (ct.business_id = b.id)
+             LEFT JOIN employee e ON (ct.employee_id = e.id)
+             LEFT JOIN employee m ON (m.id = e.managerid)
+             LEFT JOIN pricegroup g ON (ct.pricegroup_id = g.id)
+             LEFT JOIN language l ON (l.code = ct.language_code)
+                 WHERE $where|;
+
+  # redo for invoices, orders and quotations
+  if ($form->{l_transnumber} || $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
+
+    my ($ar, $union, $module);
+    $query = "";
+    my $transwhere;
+    my $openarap = "";
+    my $openoe = "";

     
     

-    # delete vendor
-    $query = qq|DELETE FROM vendor
-                WHERE id = $form->{id}|;
-    $dbh->do($query) || $form->dberror($query);
+    if ($form->{open} || $form->{closed}) {
+      unless ($form->{open} && $form->{closed}) {
+       $openarap = " AND a.amount != a.paid" if $form->{open};
+       $openarap = " AND a.amount = a.paid" if $form->{closed};
+       $openoe = " AND o.closed = '0'" if $form->{open};
+       $openoe = " AND o.closed = '1'" if $form->{closed};
+      }
+    }
+      
+    if ($form->{l_transnumber}) {
+      $ar = ($form->{db} eq 'customer') ? 'ar' : 'ap';
+      $module = $ar;
+
+      $transwhere = "";
+      $transwhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
+      $transwhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
+      
+   
+      $query = qq|SELECT ct.*, b.description AS business,
+                  a.invnumber, a.ordnumber, a.quonumber, a.id AS invid,
+                 '$ar' AS module, 'invoice' AS formtype,
+                 (a.amount = a.paid) AS closed, a.amount, a.netamount
+                 FROM $form->{db} ct
+               JOIN $ar a ON (a.$form->{db}_id = ct.id)
+               LEFT JOIN business b ON (ct.business_id = b.id)
+                 WHERE $where
+                 AND a.invoice = '0'
+                 $transwhere
+                 $openarap
+                 |;
+  
+      $union = qq|
+              UNION|;
+      
+    }

 
 

-    $query = qq|DELETE FROM shipto
-                WHERE trans_id = $form->{id}|;
-    $dbh->do($query) || $form->dberror($query);
+    if ($form->{l_invnumber}) {
+      $ar = ($form->{db} eq 'customer') ? 'ar' : 'ap';
+      $module = ($ar eq 'ar') ? 'is' : 'ir';

 
 

-    $query = qq|DELETE FROM vendortax
-                WHERE vendor_id = $form->{id}|;
-    $dbh->do($query) || $form->dberror($query);
+      $transwhere = "";
+      $transwhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
+      $transwhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
+    
+      $query .= qq|$union
+                  SELECT ct.*, b.description AS business,
+                  a.invnumber, a.ordnumber, a.quonumber, a.id AS invid,
+                 '$module' AS module, 'invoice' AS formtype,
+                 (a.amount = a.paid) AS closed, a.amount, a.netamount
+                 FROM $form->{db} ct
+               JOIN $ar a ON (a.$form->{db}_id = ct.id)
+               LEFT JOIN business b ON (ct.business_id = b.id)
+                 WHERE $where
+                 AND a.invoice = '1'
+                 $transwhere
+                 $openarap
+                 |;
+  
+      $union = qq|
+              UNION|;
+      
+    }
+    
+    if ($form->{l_ordnumber}) {
+      
+      $transwhere = "";
+      $transwhere .= " AND o.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
+      $transwhere .= " AND o.transdate <= '$form->{transdateto}'" if $form->{transdateto};
+      $query .= qq|$union
+                  SELECT ct.*, b.description AS business,
+                 ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid,
+                 'oe' AS module, 'order' AS formtype,
+                 o.closed, o.amount, o.netamount
+                 FROM $form->{db} ct
+               JOIN oe o ON (o.$form->{db}_id = ct.id)
+               LEFT JOIN business b ON (ct.business_id = b.id)
+                 WHERE $where
+                 AND o.quotation = '0'
+                 $transwhere
+                 $openoe
+                 |;
+  
+      $union = qq|
+              UNION|;
+
+    }
+
+    if ($form->{l_quonumber}) {
+
+      $transwhere = "";
+      $transwhere .= " AND o.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
+      $transwhere .= " AND o.transdate <= '$form->{transdateto}'" if $form->{transdateto};
+      $query .= qq|$union
+                  SELECT ct.*, b.description AS business,
+                 ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid,
+                 'oe' AS module, 'quotation' AS formtype,
+                 o.closed, o.amount, o.netamount
+                 FROM $form->{db} ct
+               JOIN oe o ON (o.$form->{db}_id = ct.id)
+               LEFT JOIN business b ON (ct.business_id = b.id)
+                 WHERE $where
+                 AND o.quotation = '1'
+                 $transwhere
+                 $openoe
+                 |;

 
 

-    # commit and redirect
-    $rc = $dbh->commit;
-    $dbh->disconnect;
+    }

 
 

+      $sortorder .= ", invid";

   }
 
   }
 

-  $rc;
+  $query .= qq|
+                ORDER BY $sortorder|;
+                
+  my $sth = $dbh->prepare($query);
+  $sth->execute || $form->dberror($query);
+
+  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+    $ref->{address} = "";
+    map { $ref->{address} .= "$ref->{$_} "; } qw(address1 address2 city state zipcode country);
+    push @{ $form->{CT} }, $ref;
+  }
+
+  $sth->finish;
+  $dbh->disconnect;

 
 }
 
 
 
 }
 
 

-sub search {
+sub get_history {

   my ($self, $myconfig, $form) = @_;
   my ($self, $myconfig, $form) = @_;

-
+  

   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 

+  my $query;

   my $where = "1 = 1";
   my $where = "1 = 1";

-  $form->{sort} = "name" unless ($form->{sort});
+  $form->{sort} = "partnumber" unless $form->{sort};
+  my $sortorder = $form->{sort};
+  my %ordinal = ();
+  my $var;
+  my $table;
+
+  # setup ASC or DESC
+  $form->sort_order();

   
   if ($form->{"$form->{db}number"}) {
   
   if ($form->{"$form->{db}number"}) {

-    my $companynumber = $form->like(lc $form->{"$form->{db}number"});
-    $where .= " AND lower($form->{db}number) LIKE '$companynumber'";
+    $var = $form->like(lc $form->{"$form->{db}number"});
+    $where .= " AND lower(ct.$form->{db}number) LIKE '$var'";

   }
   if ($form->{name}) {
   }
   if ($form->{name}) {

-    my $name = $form->like(lc $form->{name});
-    $where .= " AND lower(name) LIKE '$name'";
+    $var = $form->like(lc $form->{name});
+    $where .= " AND lower(ct.name) LIKE '$var'";
+  }
+  if ($form->{address}) {
+    $var = $form->like(lc $form->{address});
+    $where .= " AND lower(ct.address1) LIKE '$var'";
+  }
+  if ($form->{city}) {
+    $var = $form->like(lc $form->{city});
+    $where .= " AND lower(ct.city) LIKE '$var'";
+  }
+  if ($form->{state}) {
+    $var = $form->like(lc $form->{state});
+    $where .= " AND lower(ct.state) LIKE '$var'";
+  }
+  if ($form->{zipcode}) {
+    $var = $form->like(lc $form->{zipcode});
+    $where .= " AND lower(ct.zipcode) LIKE '$var'";
+  }
+  if ($form->{country}) {
+    $var = $form->like(lc $form->{country});
+    $where .= " AND lower(ct.country) LIKE '$var'";

   }
   if ($form->{contact}) {
   }
   if ($form->{contact}) {

-    my $contact = $form->like(lc $form->{contact});
-    $where .= " AND lower(contact) LIKE '$contact'";
+    $var = $form->like(lc $form->{contact});
+    $where .= " AND lower(ct.contact) LIKE '$var'";
+  }
+  if ($form->{notes}) {
+    $var = $form->like(lc $form->{notes});
+    $where .= " AND lower(ct.notes) LIKE '$var'";

   }
   if ($form->{email}) {
   }
   if ($form->{email}) {

-    my $email = $form->like(lc $form->{email});
-    $where .= " AND lower(email) LIKE '$email'";
+    $var = $form->like(lc $form->{email});
+    $where .= " AND lower(ct.email) LIKE '$var'";

   }
 
   }
 

-  if ($form->{status} eq 'orphaned') {
-    $where .= qq| AND id NOT IN (SELECT o.$form->{db}_id
-                                 FROM oe o, $form->{db} ct
-                                WHERE ct.id = o.$form->{db}_id)|;
-    if ($form->{db} eq 'customer') {
-      $where .= qq| AND id NOT IN (SELECT a.customer_id
-                                   FROM ar a, customer ct
-                                  WHERE ct.id = a.customer_id)|;
-    }
-    if ($form->{db} eq 'vendor') {
-      $where .= qq| AND id NOT IN (SELECT a.vendor_id
-                                   FROM ap a, vendor ct
-                                  WHERE ct.id = a.vendor_id)|;
+  $where .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
+  $where .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
+
+  if ($form->{open} || $form->{closed}) {
+    unless ($form->{open} && $form->{closed}) {
+      if ($form->{type} eq 'invoice') {
+       $where .= " AND a.amount != a.paid" if $form->{open};
+       $where .= " AND a.amount = a.paid" if $form->{closed};
+      } else {
+       $where .= " AND a.closed = '0'" if $form->{open};
+       $where .= " AND a.closed = '1'" if $form->{closed};
+      }

     }
   }
   
     }
   }
   

-  my $query = qq~SELECT id, name, $form->{db}number, 
-                 addr1 || ' ' || addr2 || ' ' || addr3 || ' ' || addr4 AS address,
-                 contact, phone, fax, email, cc, terms
-                 FROM $form->{db}
-                 WHERE $where
-                ORDER BY $form->{sort}~;
+  my $invnumber = 'invnumber';
+  my $deldate = 'deliverydate';
+  my $buysell;
+  
+  if ($form->{db} eq 'customer') {
+    $buysell = "buy";
+    if ($form->{type} eq 'invoice') {
+      $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
+      $table = 'ar';
+    } else {
+      $table = 'oe';
+      if ($form->{type} eq 'order') {
+       $invnumber = 'ordnumber';
+       $where .= qq| AND a.quotation = '0'|;
+      } else {
+       $invnumber = 'quonumber';
+       $where .= qq| AND a.quotation = '1'|;
+      }
+      $deldate = 'reqdate';
+    }
+  }
+  if ($form->{db} eq 'vendor') {
+    $buysell = "sell";
+    if ($form->{type} eq 'invoice') {
+      $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
+      $table = 'ap';
+    } else {
+      $table = 'oe';
+      if ($form->{type} eq 'order') {
+       $invnumber = 'ordnumber';
+       $where .= qq| AND a.quotation = '0'|;
+      } else {
+       $invnumber = 'quonumber';
+       $where .= qq| AND a.quotation = '1'|;
+      } 
+      $deldate = 'reqdate';
+    }
+  }
+ 
+  my $invjoin = qq|
+                JOIN invoice i ON (i.trans_id = a.id)|;
+
+  if ($form->{type} eq 'order') {
+    $invjoin = qq|
+                JOIN orderitems i ON (i.trans_id = a.id)|;
+  }
+  if ($form->{type} eq 'quotation') {
+    $invjoin = qq|
+                JOIN orderitems i ON (i.trans_id = a.id)|;
+    $where .= qq| AND a.quotation = '1'|;
+  }
+
+
+  if ($form->{history} eq 'summary') {
+    $query = qq|SELECT curr FROM defaults|;
+    my ($curr) = $dbh->selectrow_array($query);
+    $curr =~ s/:.*//;
+    
+    %ordinal = ( partnumber    => 8,
+                 description   => 9
+              );
+    $sortorder = "2 $form->{direction}, 1, $ordinal{$sortorder} $form->{direction}";
+    
+    $query = qq|SELECT ct.id AS ctid, ct.name, ct.address1,
+               ct.address2, ct.city, ct.state,
+               p.id AS pid, p.partnumber, i.description, p.unit,
+               sum(i.qty) AS qty, sum(i.sellprice) AS sellprice,
+               '$curr' AS curr,
+               ct.zipcode, ct.country
+               FROM $form->{db} ct
+               JOIN $table a ON (a.$form->{db}_id = ct.id)
+               $invjoin
+               JOIN parts p ON (p.id = i.parts_id)
+               WHERE $where
+               GROUP BY ct.id, ct.name, ct.address1, ct.address2, ct.city,
+               ct.state, ct.zipcode, ct.country,
+               p.id, p.partnumber, i.description, p.unit
+               ORDER BY $sortorder|;
+  } else {
+    %ordinal = ( partnumber    => 9,
+                 description   => 12,
+                "$deldate"     => 16,
+                serialnumber   => 17,
+                projectnumber  => 18
+               );
+ 
+    $sortorder = "2 $form->{direction}, 1, 11, $ordinal{$sortorder} $form->{direction}";
+    
+    $query = qq|SELECT ct.id AS ctid, ct.name, ct.address1,
+               ct.address2, ct.city, ct.state,
+               p.id AS pid, p.partnumber, a.id AS invid,
+               a.$invnumber, a.curr, i.description,
+               i.qty, i.sellprice, i.discount,
+               i.$deldate, i.serialnumber, pr.projectnumber,
+               e.name AS employee, ct.zipcode, ct.country, i.unit|;
+    $query .= qq|, i.fxsellprice| if $form->{type} eq 'invoice';
+
+    if ($form->{type} ne 'invoice') {
+      if ($form->{l_curr}) {
+       $query .= qq|, (SELECT $buysell FROM exchangerate ex
+                       WHERE a.curr = ex.curr
+                       AND a.transdate = ex.transdate) AS exchangerate|;
+      }
+    }
+       
+    $query .= qq|
+                FROM $form->{db} ct
+               JOIN $table a ON (a.$form->{db}_id = ct.id)
+               $invjoin
+               JOIN parts p ON (p.id = i.parts_id)
+               LEFT JOIN project pr ON (pr.id = i.project_id)
+               LEFT JOIN employee e ON (e.id = a.employee_id)
+               WHERE $where
+               ORDER BY $sortorder|;
+  }
+

 
   my $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
 
   my $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 

-

   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

+    $ref->{address} = "";
+    $ref->{exchangerate} = 1 unless $ref->{exchangerate};
+    map { $ref->{address} .= "$ref->{$_} "; } qw(address1 address2 city state zipcode country);
+    $ref->{id} = $ref->{ctid};

     push @{ $form->{CT} }, $ref;
   }
 
     push @{ $form->{CT} }, $ref;
   }
 


@@ -443,5 +848,161 @@ sub search {
 }
 
 
 }
 
 

+sub pricelist {
+  my ($self, $myconfig, $form) = @_;
+  
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+
+  my $query;
+
+  if ($form->{db} eq 'customer') {
+    $query = qq|SELECT p.id, p.partnumber, p.description,
+                p.sellprice, pg.partsgroup, p.partsgroup_id,
+                m.pricebreak, m.sellprice,
+               m.validfrom, m.validto, m.curr
+                FROM partscustomer m
+               JOIN parts p ON (p.id = m.parts_id)
+               LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+               WHERE m.customer_id = $form->{id}
+               ORDER BY partnumber|;
+  }
+  if ($form->{db} eq 'vendor') {
+    $query = qq|SELECT p.id, p.partnumber AS sku, p.description,
+                pg.partsgroup, p.partsgroup_id,
+               m.partnumber, m.leadtime, m.lastcost, m.curr
+               FROM partsvendor m
+               JOIN parts p ON (p.id = m.parts_id)
+               LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+               WHERE m.vendor_id = $form->{id}
+               ORDER BY p.partnumber|;
+  }
+
+  my $sth;
+  my $ref;
+
+  if ($form->{id}) {
+    $sth = $dbh->prepare($query);
+    $sth->execute || $form->dberror($query);
+
+    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+      push @{ $form->{all_partspricelist} }, $ref;
+    }
+    $sth->finish;
+  }
+
+  $query = qq|SELECT curr FROM defaults|;
+  ($form->{currencies}) = $dbh->selectrow_array($query);
+
+  $query = qq|SELECT id, partsgroup FROM partsgroup
+              ORDER BY partsgroup|;
+
+  $sth = $dbh->prepare($query);
+  $sth->execute || $self->dberror($query);
+
+  $form->{all_partsgroup} = ();
+  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    push @{ $form->{all_partsgroup} }, $ref;
+  }
+  $sth->finish;
+  
+  $dbh->disconnect;
+
+}
+
+
+sub save_pricelist {
+  my ($self, $myconfig, $form) = @_;
+
+  my $dbh = $form->dbconnect_noauto($myconfig);
+  
+  my $query = qq|DELETE FROM parts$form->{db}
+                 WHERE $form->{db}_id = $form->{id}|;
+  $dbh->do($query) || $form->dberror($query);
+
+  
+  foreach $i (1 .. $form->{rowcount}) {
+
+    if ($form->{"id_$i"}) {
+
+      if ($form->{db} eq 'customer') {
+       map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(pricebreak sellprice);
+       
+       $query = qq|INSERT INTO parts$form->{db} (parts_id, customer_id,
+                   pricebreak, sellprice, validfrom, validto, curr)
+                   VALUES ($form->{"id_$i"}, $form->{id},
+                   $form->{"pricebreak_$i"}, $form->{"sellprice_$i"},|
+                   .$form->dbquote($form->{"validfrom_$i"}, SQL_DATE) .qq|,|
+                   .$form->dbquote($form->{"validto_$i"}, SQL_DATE) .qq|,
+                   '$form->{"curr_$i"}')|;
+      } else {
+       map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(leadtime lastcost);
+       
+       $query = qq|INSERT INTO parts$form->{db} (parts_id, vendor_id,
+                   partnumber, lastcost, leadtime, curr)
+                   VALUES ($form->{"id_$i"}, $form->{id},
+                   '$form->{"partnumber_$i"}', $form->{"lastcost_$i"},
+                   $form->{"leadtime_$i"}, '$form->{"curr_$i"}')|;
+
+      }
+      $dbh->do($query) || $form->dberror($query);
+    }
+
+  }
+
+  $_ = $dbh->commit;
+  $dbh->disconnect;
+
+}
+
+
+
+sub retrieve_item {
+  my ($self, $myconfig, $form) = @_;
+
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+
+  my $i = $form->{rowcount};
+  my $var;
+  my $null;
+
+  my $where = "WHERE p.obsolete = '0' AND p.income_accno_id > 0";
+
+  if ($form->{"partnumber_$i"}) {
+    $var = $form->like(lc $form->{"partnumber_$i"});
+    $where .= " AND lower(p.partnumber) LIKE '$var'";
+  }
+  if ($form->{"description_$i"}) {
+    $var = $form->like(lc $form->{"description_$i"});
+    $where .= " AND lower(p.description) LIKE '$var'";
+  }
+
+  if ($form->{"partsgroup_$i"}) {
+    ($null, $var) = split /--/, $form->{"partsgroup_$i"};
+    $where .= qq| AND p.partsgroup_id = $var|;
+  }
+  
+  
+  my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
+                 p.lastcost, p.unit, pg.partsgroup, p.partsgroup_id
+                FROM parts p
+                LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+                $where
+                |;
+  my $sth = $dbh->prepare($query);
+  $sth->execute || $form->dberror($query);
+
+  my $ref;
+  $form->{item_list} = ();
+  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    push @{ $form->{item_list} }, $ref;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+
+}
+
+

 1;
 
 1;