X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=b52c8794acafa55317b3c4bb674b4eab08d223c9;hb=f8fff60be1abb9986486cc7367747ee16d829116;hp=308a36e46daf27de62cf4dac4a8b5ef9c5732569;hpb=89b6350423e0d8f990790cd0f40df2789e9a3cd0;p=kivitendo-erp.git

diff --git a/SL/CT.pm b/SL/CT.pm
index 308a36e46..b52c8794a 100644
--- a/SL/CT.pm
+++ b/SL/CT.pm
@@ -25,7 +25,8 @@
 # GNU General Public License for more details.
 # You should have received a copy of the GNU General Public License
 # along with this program; if not, write to the Free Software
-# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
 #======================================================================
 #
 # backend code for customers and vendors
@@ -37,605 +38,486 @@
 
 package CT;
 
-sub get_tuple {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
+use SL::Common;
+use SL::CVar;
+use SL::DBUtils;
+use SL::DB;
+use SL::Util qw(trim);
+use Text::ParseWords;
 
-  my $dbh   = $form->dbconnect($myconfig);
-  my $query = qq|SELECT ct.*, b.id AS business, s.*, cp.*
-                 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 contacts cp on ct.id = cp.cp_cv_id
-		 WHERE ct.id = $form->{id}  order by cp.cp_id limit 1|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+use strict;
 
-  my $ref = $sth->fetchrow_hashref(NAME_lc);
-
-  map { $form->{$_} = $ref->{$_} } keys %$ref;
+sub search {
+  $main::lxdebug->enter_sub();
 
-  $sth->finish;
-  if ($form->{salesman_id}) {
-    my $query = qq|SELECT ct.name AS salesman
-                  FROM $form->{db} ct
-                  WHERE ct.id = $form->{salesman_id}|;
-    my $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
+  my ( $self, $myconfig, $form ) = @_;
 
-    my ($ref) = $sth->fetchrow_array();
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
 
-    $form->{salesman} = $ref;
+  my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
+  my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
 
-    $sth->finish;
+  my $where = "1 = 1";
+  my @values;
+
+  my %allowed_sort_columns = (
+      "id"                 => "ct.id",
+      "customernumber"     => "ct.customernumber",
+      "vendornumber"       => "ct.vendornumber",
+      "name"               => "ct.name",
+      "contact"            => "ct.contact",
+      "phone"              => "ct.phone",
+      "fax"                => "ct.fax",
+      "email"              => "ct.email",
+      "street"             => "ct.street",
+      "taxnumber"          => "ct.taxnumber",
+      "business"           => "b.description",
+      "invnumber"          => "ct.invnumber",
+      "ordnumber"          => "ct.ordnumber",
+      "quonumber"          => "ct.quonumber",
+      "zipcode"            => "ct.zipcode",
+      "city"               => "ct.city",
+      "country"            => "ct.country",
+      "gln"                => "ct.gln",
+      "discount"           => "ct.discount",
+      "insertdate"         => "ct.itime",
+      "salesman"           => "e.name",
+      "payment"            => "pt.description",
+      "pricegroup"         => "pg.pricegroup",
+      "ustid"              => "ct.ustid",
+      "creditlimit"        => "ct.creditlimit",
+      "commercial_court"   => "ct.commercial_court",
+    );
+
+  $form->{sort} ||= "name";
+  my $sortorder;
+  if ( $join_records ) {
+    # in UNION case order by hash key, e.g. salesman
+    # the UNION created an implicit select around the result
+    $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
+  } else {
+    # in not UNION case order by hash value, e.g. e.name
+    $sortorder = $allowed_sort_columns{$form->{sort}} ?  $allowed_sort_columns{$form->{sort}} : "ct.name";
   }
+  my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
 
-  # 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";
+  if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) {
+    $sortorder  = "lower($sortorder) ${sortdir}";
+  } else {
+    $sortorder .= " ${sortdir}";
   }
-  $sth->finish;
 
-  # get tax labels
-  $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);
-
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    $form->{taxaccounts} .= "$ref->{accno} ";
-    $form->{tax}{ $ref->{accno} }{description} = $ref->{description};
+  if ($form->{"${cv}number"}) {
+    $where .= " AND ct.${cv}number ILIKE ?";
+    push(@values, like($form->{"${cv}number"}));
   }
-  $sth->finish;
-  chop $form->{taxaccounts};
-
-  # 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);
-
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    $form->{tax}{ $ref->{accno} }{taxable} = 1;
-  }
-  $sth->finish;
 
-  # get business types
-  $query = qq|SELECT id, description
-              FROM business
-	      ORDER BY 1|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    push @{ $form->{all_business} }, $ref;
+  foreach my $key (qw(name contact email)) {
+    if ($form->{$key}) {
+      $where .= " AND ct.$key ILIKE ?";
+      push(@values, like($form->{$key}));
+    }
   }
-  $sth->finish;
-
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-}
-
-## LINET
-sub query_titles_and_greetings {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-  my (%tmp,  $ref);
-
-  my $dbh = $form->dbconnect($myconfig);
 
-  $query =
-    "SELECT DISTINCT(c.cp_greeting) FROM contacts c WHERE c.cp_greeting LIKE '%'";
-  $sth = $dbh->prepare($query);
-  $sth->execute() || $form->dberror($query);
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    next unless ($ref->{cp_greeting} =~ /[a-zA-Z]/);
-    $tmp{ $ref->{cp_greeting} } = 1;
+  if ($form->{cp_name}) {
+    $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
+    push @values, like($form->{cp_name});
   }
-  $sth->finish();
 
-  @{ $form->{GREETINGS} } = sort(keys(%tmp));
-
-  %tmp = ();
-
-  $query =
-    "SELECT DISTINCT(c.cp_title) FROM contacts c WHERE c.cp_title LIKE '%'";
-  $sth = $dbh->prepare($query);
-  $sth->execute() || $form->dberror($query);
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    next unless ($ref->{cp_title} =~ /[a-zA-Z]/);
-    $tmp{ $ref->{cp_title} } = 1;
+  if ($form->{addr_street}) {
+    $where .= qq| AND ((ct.street ILIKE ?) | .
+              qq|      OR | .
+              qq|      (ct.id IN ( | .
+              qq|         SELECT sc.trans_id FROM shipto sc | .
+              qq|         WHERE (sc.module = 'CT') | .
+              qq|           AND (sc.shiptostreet ILIKE ?) | .
+              qq|      ))) |;
+    push @values, (like($form->{addr_street})) x 2;
   }
-  $sth->finish();
 
-  @{ $form->{TITLES} } = sort(keys(%tmp));
-
-  $dbh->disconnect();
-  $main::lxdebug->leave_sub();
-}
-## /LINET
+  if ($form->{addr_zipcode}) {
+    $where .= qq| AND ((ct.zipcode ILIKE ?) | .
+              qq|      OR | .
+              qq|      (ct.id IN ( | .
+              qq|         SELECT sc.trans_id FROM shipto sc | .
+              qq|         WHERE (sc.module = 'CT') | .
+              qq|           AND (sc.shiptozipcode ILIKE ?) | .
+              qq|      ))) |;
+    push @values, (like($form->{addr_zipcode})) x 2;
+  }
 
-sub taxaccounts {
-  $main::lxdebug->enter_sub();
+  if ($form->{addr_city}) {
+    $where .= " AND ((lower(ct.city) LIKE lower(?))
+                     OR
+                     (ct.id IN (
+                        SELECT sc.trans_id
+                        FROM shipto sc
+                        WHERE (sc.module = 'CT')
+                          AND (lower(sc.shiptocity) LIKE lower(?))
+                      ))
+                     )";
+    push @values, (like($form->{addr_city})) x 2;
+  }
 
-  my ($self, $myconfig, $form) = @_;
+  if ($form->{addr_country}) {
+    $where .= " AND ((lower(ct.country) LIKE lower(?))
+                     OR
+                     (ct.id IN (
+                        SELECT so.trans_id
+                        FROM shipto so
+                        WHERE (so.module = 'CT')
+                          AND (lower(so.shiptocountry) LIKE lower(?))
+                      ))
+                     )";
+    push @values, (like($form->{addr_country})) x 2;
+  }
 
-  my $dbh = $form->dbconnect($myconfig);
+  if ($form->{addr_gln}) {
+    $where .= " AND ((lower(ct.gln) LIKE lower(?))
+                     OR
+                     (ct.id IN (
+                        SELECT so.trans_id
+                        FROM shipto so
+                        WHERE (so.module = 'CT')
+                          AND (lower(so.shiptogln) LIKE lower(?))
+                      ))
+                     )";
+    push @values, (like($form->{addr_gln})) x 2;
+  }
 
-  # get tax labels
-  my $query = qq|SELECT accno, description
-                 FROM chart c, tax t
-		 WHERE c.link LIKE '%CT_tax%'
-	         AND c.id = t.chart_id
-		 ORDER BY accno|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  my $ref = ();
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    $form->{taxaccounts} .= "$ref->{accno} ";
-    $form->{tax}{ $ref->{accno} }{description} = $ref->{description};
+  if ( $form->{status} eq 'orphaned' ) {
+    $where .=
+      qq| AND ct.id NOT IN | .
+      qq|   (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
+    if ($cv eq 'customer') {
+      $where .=
+        qq| AND ct.id NOT IN | .
+        qq| (SELECT a.customer_id FROM ar a, customer cv | .
+        qq|  WHERE cv.id = a.customer_id)|;
+    }
+    if ($cv eq 'vendor') {
+      $where .=
+        qq| AND ct.id NOT IN | .
+        qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
+        qq|  WHERE cv.id = a.vendor_id)|;
+    }
+    $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
   }
-  $sth->finish;
-  chop $form->{taxaccounts};
 
-  # this is just for the selection for type of business
-  $query = qq|SELECT id, description
-              FROM business|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  if ($form->{obsolete} eq "Y") {
+    $where .= qq| AND ct.obsolete|;
+  } elsif ($form->{obsolete} eq "N") {
+    $where .= qq| AND NOT ct.obsolete|;
+  }
 
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    push @{ $form->{all_business} }, $ref;
+  if ($form->{business_id}) {
+    $where .= qq| AND (ct.business_id = ?)|;
+    push(@values, conv_i($form->{business_id}));
   }
-  $sth->finish;
 
-  $dbh->disconnect;
+  if ($form->{salesman_id}) {
+    $where .= qq| AND (ct.salesman_id = ?)|;
+    push(@values, conv_i($form->{salesman_id}));
+  }
 
-  $main::lxdebug->leave_sub();
-}
+  if($form->{insertdatefrom}) {
+    $where .= qq| AND (ct.itime::DATE >= ?)|;
+    push@values, conv_date($form->{insertdatefrom});
+  }
 
-sub save_customer {
-  $main::lxdebug->enter_sub();
+  if($form->{insertdateto}) {
+    $where .= qq| AND (ct.itime::DATE <= ?)|;
+    push @values, conv_date($form->{insertdateto});
+  }
 
-  my ($self, $myconfig, $form) = @_;
+  if ($form->{all}) {
+    my @tokens = parse_line('\s+', 0, $form->{all});
+      $where .= qq| AND (
+          ct.${cv}number ILIKE ? OR
+          ct.name        ILIKE ?
+          )| for @tokens;
+    push @values, ("%$_%")x2 for @tokens;
+  }
 
-  # set pricegroup to default
-  if ($form->{klass}) { }
-  else { $form->{klass} = 0; }
+  if (($form->{create_zugferd_invoices} // '') ne '') {
+    $where .= qq| AND (ct.create_zugferd_invoices = ?)|;
+    push @values, $form->{create_zugferd_invoices};
+  }
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-##LINET
-  map({
-      $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
-        if ($form->{"selected_cp_${_}"});
-  } qw(title greeting));
-#
-  # escape '
-  map { $form->{$_} =~ s/\'/\'\'/g }
-    qw(customernumber name street zipcode city country homepage contact notes cp_title cp_greeting language pricegroup);
-##/LINET
-  # assign value discount, terms, creditlimit
-  $form->{discount} = $form->parse_amount($myconfig, $form->{discount});
-  $form->{discount} /= 100;
-  $form->{terms}       *= 1;
-  $form->{taxincluded} *= 1;
-  $form->{obsolete}    *= 1;
-  $form->{business}    *= 1;
-  $form->{salesman_id} *= 1;
-  $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
-
-  my ($query, $sth);
-
-  if ($form->{id}) {
-    $query = qq|DELETE FROM customertax
-                WHERE customer_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);
-  } else {
-    my $uid = rand() . time;
+  if ($form->{all_phonenumbers}) {
+    my $search_term = trim($form->{all_phonenumbers});
+    $search_term    =~ s{\p{WSpace}+}{}g;
+    $search_term    = join ' *', split(//, $search_term);
+
+    $where .= qq| AND (ct.phone ~* ? OR
+                       ct.fax   ~* ? OR
+                       ct.id    IN
+                         (SELECT cp_cv_id FROM contacts
+                          WHERE cp_phone1      ~* ? OR
+                                cp_phone2      ~* ? OR
+                                cp_fax         ~* ? OR
+                                cp_mobile1     ~* ? OR
+                                cp_mobile2     ~* ? OR
+                                cp_satphone    ~* ? OR
+                                cp_satfax      ~* ? OR
+                                cp_privatphone ~* ?
+                         )
+    )|;
+    push @values, ($search_term)x10;
+  }
 
-    $uid .= $form->{login};
+  my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'CT',
+                                                            'trans_id_field' => 'ct.id',
+                                                            'filter'         => $form);
 
-    $uid = substr($uid, 2, 75);
+  if ($cvar_where) {
+    $where .= qq| AND ($cvar_where)|;
+    push @values, @cvar_values;
+  }
 
-    $query = qq|INSERT INTO customer (name)
-                VALUES ('$uid')|;
-    $dbh->do($query) || $form->dberror($query);
+  my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
+  my $pg_join   = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
 
-    $query = qq|SELECT c.id FROM customer c
-                WHERE c.name = '$uid'|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
+  my $main_cp_select = '';
+  if ($form->{l_main_contact_person}) {
+    $main_cp_select =  qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
+                              FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
+                              AS main_contact_person /;
+  }
+  my $query =
+    qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
+    qq|  pt.description as payment | .
+    $pg_select .
+    $main_cp_select .
+    (qq|, NULL AS invnumber, NULL AS ordnumber, NULL AS quonumber, NULL AS invid, NULL AS module, NULL AS formtype, NULL AS closed | x!! $join_records) .
+    qq|FROM $cv ct | .
+    qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+    qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
+    qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
+    $pg_join .
+    qq|WHERE $where|;
+
+  my @saved_values = @values;
+  # redo for invoices, orders and quotations
+  if ($join_records) {
+    my $union = "UNION";
 
-    ($form->{id}) = $sth->fetchrow_array;
-    $sth->finish;
-    if (!$form->{customernumber} && $form->{business}) {
-      $form->{customernumber} =
-        $form->update_business($myconfig, $form->{business});
-    }
-    if (!$form->{customernumber}) {
-      $form->{customernumber} =
-        $form->update_defaults($myconfig, "customernumber");
+    if ($form->{l_invnumber}) {
+      my $ar = $cv eq 'customer' ? 'ar' : 'ap';
+      my $module = $ar eq 'ar' ? 'is' : 'ir';
+      push(@values, @saved_values);
+      $query .=
+        qq| UNION | .
+        qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
+        qq|  pt.description as payment | .
+        $pg_select .
+        $main_cp_select .
+        qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
+        qq|  '$module' AS module, 'invoice' AS formtype, | .
+        qq|  (a.amount = a.paid) AS closed | .
+        qq|FROM $cv ct | .
+        qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
+        qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+        qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
+        qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
+        $pg_join .
+        qq|WHERE $where AND (a.invoice = '1')|;
     }
 
-  }
+    if ( $form->{l_ordnumber} ) {
+      push(@values, @saved_values);
+      $query .=
+        qq| UNION | .
+        qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
+        qq|  pt.description as payment | .
+        $pg_select .
+        $main_cp_select .
+        qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
+        qq|  'oe' AS module, 'order' AS formtype, o.closed | .
+        qq|FROM $cv ct | .
+        qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
+        qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+        qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
+        qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
+        $pg_join .
+        qq|WHERE $where AND (o.quotation = '0')|;
+    }
 
-  $query = qq|UPDATE customer SET
-              customernumber = '$form->{customernumber}',
-	      name = '$form->{name}',
-              department_1 = '$form->{department_1}',
-              department_2 = '$form->{department_2}',
-	      street = '$form->{street}',
-	      zipcode = '$form->{zipcode}',
-	      city = '$form->{city}',
-	      country = '$form->{country}',
-	      homepage = '$form->{homepage}',
-	      contact = '$form->{contact}',
-	      phone = '$form->{phone}',
-	      fax = '$form->{fax}',
-	      email = '$form->{email}',
-	      cc = '$form->{cc}',
-	      bcc = '$form->{bcc}',
-	      notes = '$form->{notes}',
-	      discount = $form->{discount},
-	      creditlimit = $form->{creditlimit},
-	      terms = $form->{terms},
-	      taxincluded = '$form->{taxincluded}',
-	      business_id = $form->{business},
-	      taxnumber = '$form->{taxnumber}',
-	      sic_code = '$form->{sic}',
-              language = '$form->{language}',
-              account_number = '$form->{account_number}',
-              bank_code = '$form->{bank_code}',
-              bank = '$form->{bank}',
-              obsolete = '$form->{obsolete}',
-              ustid = '$form->{ustid}',
-              username = '$form->{username}',
-              salesman_id = '$form->{salesman_id}',
-              user_password = | . $dbh->quote($form->{user_password}) .qq|,
-              c_vendor_id = '$form->{c_vendor_id}',
-              klass = '$form->{klass}'
-	      WHERE id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
-
-  if ($form->{cp_id}) {
-    $query = qq|UPDATE contacts SET
-		cp_greeting = '$form->{cp_greeting}',
-		cp_title = '$form->{cp_title}',
-		cp_givenname = '$form->{cp_givenname}',
-		cp_name = '$form->{cp_name}',
-		cp_email = '$form->{cp_email}',
-		cp_phone1 = '$form->{cp_phone1}',
-		cp_phone2 = '$form->{cp_phone2}'
-		WHERE cp_id = $form->{cp_id}|;
-  } elsif ($form->{cp_name} || $form->{cp_givenname}) {
-    $query =
-      qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, cp_name, cp_email, cp_phone1, cp_phone2)
-		  VALUES ($form->{id}, '$form->{cp_greeting}','$form->{cp_title}','$form->{cp_givenname}','$form->{cp_name}','$form->{cp_email}','$form->{cp_phone1}','$form->{cp_phone2}')|;
-  }
-  $dbh->do($query) || $form->dberror($query);
-
-  # save taxes
-  foreach $item (split / /, $form->{taxaccounts}) {
-    if ($form->{"tax_$item"}) {
-      $query = qq|INSERT INTO customertax (customer_id, chart_id)
-		  VALUES ($form->{id}, (SELECT c.id
-				        FROM chart c
-				        WHERE c.accno = '$item'))|;
-      $dbh->do($query) || $form->dberror($query);
+    if ( $form->{l_quonumber} ) {
+      push(@values, @saved_values);
+      $query .=
+        qq| UNION | .
+        qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
+        qq|  pt.description as payment | .
+        $pg_select .
+        $main_cp_select .
+        qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
+        qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
+        qq|FROM $cv ct | .
+        qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
+        qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+        qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
+        qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
+        $pg_join .
+        qq|WHERE $where AND (o.quotation = '1')|;
     }
   }
 
-  # add shipto
-  $form->add_shipto($dbh, $form->{id});
+  $query .= qq| ORDER BY $sortorder|;
 
-  $rc = $dbh->disconnect;
+  $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
 
   $main::lxdebug->leave_sub();
-  return $rc;
 }
 
-sub save_vendor {
+sub get_contact {
   $main::lxdebug->enter_sub();
 
-  my ($self, $myconfig, $form) = @_;
+  my ( $self, $myconfig, $form ) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-##LINET
-  map({
-      $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
-        if ($form->{"selected_cp_${_}"});
-  } qw(title greeting));
-
-  # escape '
-  map { $form->{$_} =~ s/\'/\'\'/g }
-    qw(vendornumber name street zipcode city country homepage contact notes cp_title cp_greeting language);
-##/LINET
-  $form->{discount} = $form->parse_amount($myconfig, $form->{discount});
-  $form->{discount} /= 100;
-  $form->{terms}       *= 1;
-  $form->{taxincluded} *= 1;
-  $form->{obsolete}    *= 1;
-  $form->{business}    *= 1;
-  $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
-
-  my $query;
-
-  if ($form->{id}) {
-    $query = qq|DELETE FROM vendortax
-                WHERE vendor_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);
-  } else {
-    my $uid = time;
-    $uid .= $form->{login};
-    my $uid = rand() . time;
-    $uid .= $form->{login};
-    $uid = substr($uid, 2, 75);
-    $query = qq|INSERT INTO vendor (name)
-                VALUES ('$uid')|;
-    $dbh->do($query) || $form->dberror($query);
-
-    $query = qq|SELECT v.id FROM vendor v
-                WHERE v.name = '$uid'|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    ($form->{id}) = $sth->fetchrow_array;
-    $sth->finish;
-    if (!$form->{vendornumber}) {
-      $form->{vendornumber} =
-        $form->update_defaults($myconfig, "vendornumber");
-    }
+  die 'Missing argument: cp_id' unless $::form->{cp_id};
 
-  }
+  my $dbh   = SL::DB->client->dbh;
+  my $query =
+    qq|SELECT * FROM contacts c | .
+    qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
+  my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
+  my $ref = $sth->fetchrow_hashref("NAME_lc");
 
-##LINET
-  $query = qq|UPDATE vendor SET
-              vendornumber = '$form->{vendornumber}',
-	      name = '$form->{name}',
-              department_1 = '$form->{department_1}',
-              department_2 = '$form->{department_2}',
-	      street = '$form->{street}',
-	      zipcode = '$form->{zipcode}',
-	      city = '$form->{city}',
-	      country = '$form->{country}',
-	      homepage = '$form->{homepage}',
-	      contact = '$form->{contact}',
-	      phone = '$form->{phone}',
-	      fax = '$form->{fax}',
-	      email = '$form->{email}',
-	      cc = '$form->{cc}',
-	      bcc = '$form->{bcc}',
-	      notes = '$form->{notes}',
-	      terms = $form->{terms},
-	      discount = $form->{discount},
-	      creditlimit = $form->{creditlimit},
-	      taxincluded = '$form->{taxincluded}',
-	      gifi_accno = '$form->{gifi_accno}',
-	      business_id = $form->{business},
-	      taxnumber = '$form->{taxnumber}',
-	      sic_code = '$form->{sic}',
-              language = '$form->{language}',
-              account_number = '$form->{account_number}',
-              bank_code = '$form->{bank_code}',
-              bank = '$form->{bank}',
-              obsolete = '$form->{obsolete}',
-              ustid = '$form->{ustid}',
-              username = '$form->{username}',
-              user_password = '$form->{user_password}',
-              v_customer_id = '$form->{v_customer_id}'
-	      WHERE id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
-
-  if ($form->{cp_id}) {
-    $query = qq|UPDATE contacts SET
-		cp_greeting = '$form->{cp_greeting}',
-		cp_title = '$form->{cp_title}',
-		cp_givenname = '$form->{cp_givenname}',
-		cp_name = '$form->{cp_name}',
-		cp_email = '$form->{cp_email}',
-		cp_phone1 = '$form->{cp_phone1}',
-		cp_phone2 = '$form->{cp_phone2}'
-		WHERE cp_id = $form->{cp_id}|;
-  } elsif ($form->{cp_name} || $form->{cp_givenname}) {
-    $query =
-      qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, cp_name, cp_email, cp_phone1, cp_phone2)
-		  VALUES ($form->{id}, '$form->{cp_greeting}','$form->{cp_title}','$form->{cp_givenname}','$form->{cp_name}','$form->{cp_email}','$form->{cp_phone1}','$form->{cp_phone2}')|;
-  }
-  $dbh->do($query) || $form->dberror($query);
-
-  # save taxes
-  foreach $item (split / /, $form->{taxaccounts}) {
-    if ($form->{"tax_$item"}) {
-      $query = qq|INSERT INTO vendortax (vendor_id, chart_id)
-		  VALUES ($form->{id}, (SELECT c.id
-				        FROM chart c
-				        WHERE c.accno = '$item'))|;
-      $dbh->do($query) || $form->dberror($query);
-    }
-  }
+  map { $form->{$_} = $ref->{$_} } keys %$ref;
 
-  # add shipto
-  $form->add_shipto($dbh, $form->{id});
+  $query = qq|SELECT COUNT(cp_id) AS used FROM (
+    SELECT cp_id FROM oe UNION
+    SELECT cp_id FROM ar UNION
+    SELECT cp_id FROM ap UNION
+    SELECT cp_id FROM delivery_orders
+  ) AS cpid WHERE cp_id = ? OR ? = 0|;
+  ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
 
-  $rc = $dbh->disconnect;
+  $sth->finish;
 
   $main::lxdebug->leave_sub();
-  return $rc;
 }
 
-sub delete {
+sub get_bank_info {
   $main::lxdebug->enter_sub();
 
-  my ($self, $myconfig, $form) = @_;
+  my $self     = shift;
+  my %params   = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  Common::check_params(\%params, qw(vc id));
+
+  my $myconfig = \%main::myconfig;
+  my $form     = $main::form;
+
+  my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 
-  # delete vendor
-  my $query = qq|DELETE FROM $form->{db}
-	         WHERE id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+  my $table        = $params{vc} eq 'customer' ? 'customer' : 'vendor';
+  my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
+  my $placeholders = join ", ", ('?') x scalar @ids;
+  my $c_mandate    = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
+  my $query        = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
+                        FROM ${table}
+                        WHERE id IN (${placeholders})|;
 
-  $dbh->disconnect;
+  my $result       = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
+
+  if (ref $params{id} eq 'ARRAY') {
+    $result = { map { $_->{id} => $_ } @{ $result } };
+  } else {
+    $result = $result->[0] || { 'id' => $params{id} };
+  }
 
   $main::lxdebug->leave_sub();
+
+  return $result;
 }
 
-sub search {
-  $main::lxdebug->enter_sub();
+sub search_contacts {
+  $::lxdebug->enter_sub;
 
-  my ($self, $myconfig, $form) = @_;
+  my $self      = shift;
+  my %params    = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh       = $params{dbh} || $::form->get_standard_dbh;
 
-  my $where = "1 = 1";
-  $form->{sort} = "name" unless ($form->{sort});
+  my %sortspecs = (
+    'cp_name'   => 'cp_name, cp_givenname',
+    'vcname'    => 'vcname, cp_name, cp_givenname',
+    'vcnumber'  => 'vcnumber, cp_name, cp_givenname',
+    );
 
-  if ($form->{"$form->{db}number"}) {
-    my $companynumber = $form->like(lc $form->{"$form->{db}number"});
-    $where .= " AND lower(ct.$form->{db}number) LIKE '$companynumber'";
-  }
-  if ($form->{name}) {
-    my $name = $form->like(lc $form->{name});
-    $where .= " AND lower(ct.name) LIKE '$name'";
-  }
-  if ($form->{contact}) {
-    my $contact = $form->like(lc $form->{contact});
-    $where .= " AND lower(ct.contact) LIKE '$contact'";
-  }
-  if ($form->{email}) {
-    my $email = $form->like(lc $form->{email});
-    $where .= " AND lower(ct.email) LIKE '$email'";
-  }
+  my %sortcols  = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email cp_street cp_zipcode cp_city cp_position vcname vcnumber);
 
-  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} = "";
-  }
+  my $order_by  = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
+  $::form->{sort} = $order_by;
+  $order_by     = $sortspecs{$order_by} if ($sortspecs{$order_by});
 
-  my $query = qq|SELECT ct.*, b.description AS business
-                 FROM $form->{db} ct
-	      LEFT JOIN business b ON (ct.business_id = b.id)
-                 WHERE $where|;
+  my $sortdir   = $::form->{sortdir} ? 'ASC' : 'DESC';
+  $order_by     =~ s/,/ ${sortdir},/g;
+  $order_by    .= " $sortdir";
 
-  # redo for invoices, orders and quotations
-  if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
+  my @where_tokens = ();
+  my @values;
 
-    my ($ar, $union, $module);
-    $query = "";
+  if ($params{search_term}) {
+    my @tokens;
+    push @tokens,
+      'cp.cp_name      ILIKE ?',
+      'cp.cp_givenname ILIKE ?',
+      'cp.cp_email     ILIKE ?';
+    push @values, (like($params{search_term})) x 3;
 
-    if ($form->{l_invnumber}) {
-      $ar     = ($form->{db} eq 'customer') ? 'ar' : 'ap';
-      $module = ($ar         eq 'ar')       ? 'is' : 'ir';
-
-      $query = qq|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
-		  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'|;
-
-      $union = qq|
-              UNION|;
+    if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
+      my $number =  $params{search_term};
+      $number    =~ s/[^\d]//g;
+      $number    =  join '[ /\(\)+\-]*', split(m//, $number);
 
+      push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
     }
 
-    if ($form->{l_ordnumber}) {
-      $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
-		  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'|;
-
-      $union = qq|
-              UNION|;
-    }
+    push @where_tokens, map { "($_)" } join ' OR ', @tokens;
+  }
 
-    if ($form->{l_quonumber}) {
-      $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
-		  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'|;
+  my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'Contacts',
+                                                            'trans_id_field' => 'cp.cp_id',
+                                                            'filter'         => $params{filter});
 
-    }
+  if ($cvar_where) {
+    push @where_tokens, $cvar_where;
+    push @values, @cvar_values;
   }
 
-  $query .= qq|
-		 ORDER BY $form->{sort}|;
+  if (my $filter = $params{filter}) {
+    for (qw(name title givenname email project abteilung)) {
+      next unless $filter->{"cp_$_"};
+      add_token(\@where_tokens, \@values, col =>  "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
+    }
 
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-##LINET
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-    $ref->{address} = "";
-    map { $ref->{address} .= "$ref->{$_} "; } qw(street zipcode city country);
-    push @{ $form->{CT} }, $ref;
+    push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
+    push @where_tokens, 'cp.cp_cv_id IS NULL'     if $filter->{status} eq 'orphaned';
   }
-##/LINET
-  $sth->finish;
-  $dbh->disconnect;
 
-  $main::lxdebug->leave_sub();
+  my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
+
+  my $query     = qq|SELECT cp.*,
+                       COALESCE(c.id,             v.id)           AS vcid,
+                       COALESCE(c.name,           v.name)         AS vcname,
+                       COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
+                       CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
+                     FROM contacts cp
+                     LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
+                     LEFT JOIN vendor v   ON (cp.cp_cv_id = v.id)
+                     $where
+                     ORDER BY $order_by|;
+
+  my $contacts  = selectall_hashref_query($::form, $dbh, $query, @values);
+
+  $::lxdebug->leave_sub;
+
+  return @{ $contacts };
 }
 
-1;
 
+1;