SL/CT.pm

   1 #=====================================================================
   2 # LX-Office ERP
   3 # Copyright (C) 2004
   4 # Based on SQL-Ledger Version 2.1.9
   5 # Web http://www.lx-office.org
   6 #
   7 #=====================================================================
   8 # SQL-Ledger Accounting
   9 # Copyright (C) 2001
  10 #
  11 #  Author: Dieter Simader
  12 #   Email: dsimader@sql-ledger.org
  13 #     Web: http://www.sql-ledger.org
  14 #
  15 #  Contributors:
  16 #
  17 # This program is free software; you can redistribute it and/or modify
  18 # it under the terms of the GNU General Public License as published by
  19 # the Free Software Foundation; either version 2 of the License, or
  20 # (at your option) any later version.
  21 #
  22 # This program is distributed in the hope that it will be useful,
  23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  25 # GNU General Public License for more details.
  26 # You should have received a copy of the GNU General Public License
  27 # along with this program; if not, write to the Free Software
  28 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
  29 # MA 02110-1335, USA.
  30 #======================================================================
  31 #
  32 # backend code for customers and vendors
  33 #
  34 # CHANGE LOG:
  35 #   DS. 2000-07-04  Created
  36 #
  37 #======================================================================
  38
  39 package CT;
  40
  41 use SL::Common;
  42 use SL::CVar;
  43 use SL::DBUtils;
  44 use SL::DB;
  45 use SL::Util qw(trim);
  46 use Text::ParseWords;
  47
  48 use strict;
  49
  50 sub search {
  51   $main::lxdebug->enter_sub();
  52
  53   my ( $self, $myconfig, $form ) = @_;
  54
  55   # connect to database
  56   my $dbh = $form->dbconnect($myconfig);
  57
  58   my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
  59   my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
  60
  61   my $where = "1 = 1";
  62   my @values;
  63
  64   my %allowed_sort_columns = (
  65       "id"                 => "ct.id",
  66       "customernumber"     => "ct.customernumber",
  67       "vendornumber"       => "ct.vendornumber",
  68       "name"               => "ct.name",
  69       "contact"            => "ct.contact",
  70       "phone"              => "ct.phone",
  71       "fax"                => "ct.fax",
  72       "email"              => "ct.email",
  73       "street"             => "ct.street",
  74       "taxnumber"          => "ct.taxnumber",
  75       "business"           => "b.description",
  76       "invnumber"          => "ct.invnumber",
  77       "ordnumber"          => "ct.ordnumber",
  78       "quonumber"          => "ct.quonumber",
  79       "zipcode"            => "ct.zipcode",
  80       "city"               => "ct.city",
  81       "country"            => "ct.country",
  82       "gln"                => "ct.gln",
  83       "discount"           => "ct.discount",
  84       "insertdate"         => "ct.itime",
  85       "salesman"           => "e.name",
  86       "payment"            => "pt.description",
  87       "pricegroup"         => "pg.pricegroup",
  88       "ustid"              => "ct.ustid",
  89       "creditlimit"        => "ct.creditlimit",
  90       "commercial_court"   => "ct.commercial_court",
  91     );
  92
  93   $form->{sort} ||= "name";
  94   my $sortorder;
  95   if ( $join_records ) {
  96     # in UNION case order by hash key, e.g. salesman
  97     # the UNION created an implicit select around the result
  98     $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
  99   } else {
 100     # in not UNION case order by hash value, e.g. e.name
 101     $sortorder = $allowed_sort_columns{$form->{sort}} ?  $allowed_sort_columns{$form->{sort}} : "ct.name";
 102   }
 103   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
 104
 105   if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) {
 106     $sortorder  = "lower($sortorder) ${sortdir}";
 107   } else {
 108     $sortorder .= " ${sortdir}";
 109   }
 110
 111   if ($form->{"${cv}number"}) {
 112     $where .= " AND ct.${cv}number ILIKE ?";
 113     push(@values, like($form->{"${cv}number"}));
 114   }
 115
 116   foreach my $key (qw(name contact email)) {
 117     if ($form->{$key}) {
 118       $where .= " AND ct.$key ILIKE ?";
 119       push(@values, like($form->{$key}));
 120     }
 121   }
 122
 123   if ($form->{cp_name}) {
 124     $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
 125     push @values, like($form->{cp_name});
 126   }
 127
 128   if ($form->{addr_street}) {
 129     $where .= qq| AND ((ct.street ILIKE ?) | .
 130               qq|      OR | .
 131               qq|      (ct.id IN ( | .
 132               qq|         SELECT sc.trans_id FROM shipto sc | .
 133               qq|         WHERE (sc.module = 'CT') | .
 134               qq|           AND (sc.shiptostreet ILIKE ?) | .
 135               qq|      ))) |;
 136     push @values, (like($form->{addr_street})) x 2;
 137   }
 138
 139   if ($form->{addr_zipcode}) {
 140     $where .= qq| AND ((ct.zipcode ILIKE ?) | .
 141               qq|      OR | .
 142               qq|      (ct.id IN ( | .
 143               qq|         SELECT sc.trans_id FROM shipto sc | .
 144               qq|         WHERE (sc.module = 'CT') | .
 145               qq|           AND (sc.shiptozipcode ILIKE ?) | .
 146               qq|      ))) |;
 147     push @values, (like($form->{addr_zipcode})) x 2;
 148   }
 149
 150   if ($form->{addr_city}) {
 151     $where .= " AND ((lower(ct.city) LIKE lower(?))
 152                      OR
 153                      (ct.id IN (
 154                         SELECT sc.trans_id
 155                         FROM shipto sc
 156                         WHERE (sc.module = 'CT')
 157                           AND (lower(sc.shiptocity) LIKE lower(?))
 158                       ))
 159                      )";
 160     push @values, (like($form->{addr_city})) x 2;
 161   }
 162
 163   if ($form->{addr_country}) {
 164     $where .= " AND ((lower(ct.country) LIKE lower(?))
 165                      OR
 166                      (ct.id IN (
 167                         SELECT so.trans_id
 168                         FROM shipto so
 169                         WHERE (so.module = 'CT')
 170                           AND (lower(so.shiptocountry) LIKE lower(?))
 171                       ))
 172                      )";
 173     push @values, (like($form->{addr_country})) x 2;
 174   }
 175
 176   if ($form->{addr_gln}) {
 177     $where .= " AND ((lower(ct.gln) LIKE lower(?))
 178                      OR
 179                      (ct.id IN (
 180                         SELECT so.trans_id
 181                         FROM shipto so
 182                         WHERE (so.module = 'CT')
 183                           AND (lower(so.shiptogln) LIKE lower(?))
 184                       ))
 185                      )";
 186     push @values, (like($form->{addr_gln})) x 2;
 187   }
 188
 189   if ( $form->{status} eq 'orphaned' ) {
 190     $where .=
 191       qq| AND ct.id NOT IN | .
 192       qq|   (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
 193     if ($cv eq 'customer') {
 194       $where .=
 195         qq| AND ct.id NOT IN | .
 196         qq| (SELECT a.customer_id FROM ar a, customer cv | .
 197         qq|  WHERE cv.id = a.customer_id)|;
 198     }
 199     if ($cv eq 'vendor') {
 200       $where .=
 201         qq| AND ct.id NOT IN | .
 202         qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
 203         qq|  WHERE cv.id = a.vendor_id)|;
 204     }
 205     $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
 206   }
 207
 208   if ($form->{obsolete} eq "Y") {
 209     $where .= qq| AND ct.obsolete|;
 210   } elsif ($form->{obsolete} eq "N") {
 211     $where .= qq| AND NOT ct.obsolete|;
 212   }
 213
 214   if ($form->{business_id}) {
 215     $where .= qq| AND (ct.business_id = ?)|;
 216     push(@values, conv_i($form->{business_id}));
 217   }
 218
 219   if ($form->{salesman_id}) {
 220     $where .= qq| AND (ct.salesman_id = ?)|;
 221     push(@values, conv_i($form->{salesman_id}));
 222   }
 223
 224   if($form->{insertdatefrom}) {
 225     $where .= qq| AND (ct.itime::DATE >= ?)|;
 226     push@values, conv_date($form->{insertdatefrom});
 227   }
 228
 229   if($form->{insertdateto}) {
 230     $where .= qq| AND (ct.itime::DATE <= ?)|;
 231     push @values, conv_date($form->{insertdateto});
 232   }
 233
 234   if ($form->{all}) {
 235     my @tokens = parse_line('\s+', 0, $form->{all});
 236       $where .= qq| AND (
 237           ct.${cv}number ILIKE ? OR
 238           ct.name        ILIKE ?
 239           )| for @tokens;
 240     push @values, ("%$_%")x2 for @tokens;
 241   }
 242
 243   if (($form->{create_zugferd_invoices} // '') ne '') {
 244     $where .= qq| AND (ct.create_zugferd_invoices = ?)|;
 245     push @values, $form->{create_zugferd_invoices};
 246   }
 247
 248   if ($form->{all_phonenumbers}) {
 249     $where .= qq| AND (ct.phone ILIKE ? OR
 250                        ct.fax   ILIKE ? OR
 251                        ct.id    IN
 252                          (SELECT cp_cv_id FROM contacts
 253                           WHERE cp_phone1      ILIKE ? OR
 254                                 cp_phone2      ILIKE ? OR
 255                                 cp_fax         ILIKE ? OR
 256                                 cp_mobile1     ILIKE ? OR
 257                                 cp_mobile2     ILIKE ? OR
 258                                 cp_satphone    ILIKE ? OR
 259                                 cp_satfax      ILIKE ? OR
 260                                 cp_privatphone ILIKE ?
 261                          )
 262     )|;
 263     push @values, (like(trim($form->{all_phonenumbers})))x10;
 264   }
 265
 266   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'CT',
 267                                                             'trans_id_field' => 'ct.id',
 268                                                             'filter'         => $form);
 269
 270   if ($cvar_where) {
 271     $where .= qq| AND ($cvar_where)|;
 272     push @values, @cvar_values;
 273   }
 274
 275   my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
 276   my $pg_join   = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
 277
 278   my $main_cp_select = '';
 279   if ($form->{l_main_contact_person}) {
 280     $main_cp_select =  qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
 281                               FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
 282                               AS main_contact_person /;
 283   }
 284   my $query =
 285     qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 286     qq|  pt.description as payment | .
 287     $pg_select .
 288     $main_cp_select .
 289     (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) .
 290     qq|FROM $cv ct | .
 291     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 292     qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 293     qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 294     $pg_join .
 295     qq|WHERE $where|;
 296
 297   my @saved_values = @values;
 298   # redo for invoices, orders and quotations
 299   if ($join_records) {
 300     my $union = "UNION";
 301
 302     if ($form->{l_invnumber}) {
 303       my $ar = $cv eq 'customer' ? 'ar' : 'ap';
 304       my $module = $ar eq 'ar' ? 'is' : 'ir';
 305       push(@values, @saved_values);
 306       $query .=
 307         qq| UNION | .
 308         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 309         qq|  pt.description as payment | .
 310         $pg_select .
 311         $main_cp_select .
 312         qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
 313         qq|  '$module' AS module, 'invoice' AS formtype, | .
 314         qq|  (a.amount = a.paid) AS closed | .
 315         qq|FROM $cv ct | .
 316         qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
 317         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 318         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 319         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 320         $pg_join .
 321         qq|WHERE $where AND (a.invoice = '1')|;
 322     }
 323
 324     if ( $form->{l_ordnumber} ) {
 325       push(@values, @saved_values);
 326       $query .=
 327         qq| UNION | .
 328         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 329         qq|  pt.description as payment | .
 330         $pg_select .
 331         $main_cp_select .
 332         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 333         qq|  'oe' AS module, 'order' AS formtype, o.closed | .
 334         qq|FROM $cv ct | .
 335         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 336         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 337         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 338         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 339         $pg_join .
 340         qq|WHERE $where AND (o.quotation = '0')|;
 341     }
 342
 343     if ( $form->{l_quonumber} ) {
 344       push(@values, @saved_values);
 345       $query .=
 346         qq| UNION | .
 347         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 348         qq|  pt.description as payment | .
 349         $pg_select .
 350         $main_cp_select .
 351         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 352         qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
 353         qq|FROM $cv ct | .
 354         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 355         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 356         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 357         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 358         $pg_join .
 359         qq|WHERE $where AND (o.quotation = '1')|;
 360     }
 361   }
 362
 363   $query .= qq| ORDER BY $sortorder|;
 364
 365   $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
 366
 367   $main::lxdebug->leave_sub();
 368 }
 369
 370 sub get_contact {
 371   $main::lxdebug->enter_sub();
 372
 373   my ( $self, $myconfig, $form ) = @_;
 374
 375   die 'Missing argument: cp_id' unless $::form->{cp_id};
 376
 377   my $dbh   = SL::DB->client->dbh;
 378   my $query =
 379     qq|SELECT * FROM contacts c | .
 380     qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
 381   my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
 382   my $ref = $sth->fetchrow_hashref("NAME_lc");
 383
 384   map { $form->{$_} = $ref->{$_} } keys %$ref;
 385
 386   $query = qq|SELECT COUNT(cp_id) AS used FROM (
 387     SELECT cp_id FROM oe UNION
 388     SELECT cp_id FROM ar UNION
 389     SELECT cp_id FROM ap UNION
 390     SELECT cp_id FROM delivery_orders
 391   ) AS cpid WHERE cp_id = ? OR ? = 0|;
 392   ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
 393
 394   $sth->finish;
 395
 396   $main::lxdebug->leave_sub();
 397 }
 398
 399 sub get_bank_info {
 400   $main::lxdebug->enter_sub();
 401
 402   my $self     = shift;
 403   my %params   = @_;
 404
 405   Common::check_params(\%params, qw(vc id));
 406
 407   my $myconfig = \%main::myconfig;
 408   my $form     = $main::form;
 409
 410   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 411
 412   my $table        = $params{vc} eq 'customer' ? 'customer' : 'vendor';
 413   my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
 414   my $placeholders = join ", ", ('?') x scalar @ids;
 415   my $c_mandate    = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
 416   my $query        = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
 417                         FROM ${table}
 418                         WHERE id IN (${placeholders})|;
 419
 420   my $result       = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
 421
 422   if (ref $params{id} eq 'ARRAY') {
 423     $result = { map { $_->{id} => $_ } @{ $result } };
 424   } else {
 425     $result = $result->[0] || { 'id' => $params{id} };
 426   }
 427
 428   $main::lxdebug->leave_sub();
 429
 430   return $result;
 431 }
 432
 433 sub search_contacts {
 434   $::lxdebug->enter_sub;
 435
 436   my $self      = shift;
 437   my %params    = @_;
 438
 439   my $dbh       = $params{dbh} || $::form->get_standard_dbh;
 440
 441   my %sortspecs = (
 442     'cp_name'   => 'cp_name, cp_givenname',
 443     'vcname'    => 'vcname, cp_name, cp_givenname',
 444     'vcnumber'  => 'vcnumber, cp_name, cp_givenname',
 445     );
 446
 447   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);
 448
 449   my $order_by  = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
 450   $::form->{sort} = $order_by;
 451   $order_by     = $sortspecs{$order_by} if ($sortspecs{$order_by});
 452
 453   my $sortdir   = $::form->{sortdir} ? 'ASC' : 'DESC';
 454   $order_by     =~ s/,/ ${sortdir},/g;
 455   $order_by    .= " $sortdir";
 456
 457   my @where_tokens = ();
 458   my @values;
 459
 460   if ($params{search_term}) {
 461     my @tokens;
 462     push @tokens,
 463       'cp.cp_name      ILIKE ?',
 464       'cp.cp_givenname ILIKE ?',
 465       'cp.cp_email     ILIKE ?';
 466     push @values, (like($params{search_term})) x 3;
 467
 468     if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
 469       my $number =  $params{search_term};
 470       $number    =~ s/[^\d]//g;
 471       $number    =  join '[ /\(\)+\-]*', split(m//, $number);
 472
 473       push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
 474     }
 475
 476     push @where_tokens, map { "($_)" } join ' OR ', @tokens;
 477   }
 478
 479   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'Contacts',
 480                                                             'trans_id_field' => 'cp.cp_id',
 481                                                             'filter'         => $params{filter});
 482
 483   if ($cvar_where) {
 484     push @where_tokens, $cvar_where;
 485     push @values, @cvar_values;
 486   }
 487
 488   if (my $filter = $params{filter}) {
 489     for (qw(name title givenname email project abteilung)) {
 490       next unless $filter->{"cp_$_"};
 491       add_token(\@where_tokens, \@values, col =>  "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
 492     }
 493
 494     push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
 495     push @where_tokens, 'cp.cp_cv_id IS NULL'     if $filter->{status} eq 'orphaned';
 496   }
 497
 498   my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
 499
 500   my $query     = qq|SELECT cp.*,
 501                        COALESCE(c.id,             v.id)           AS vcid,
 502                        COALESCE(c.name,           v.name)         AS vcname,
 503                        COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
 504                        CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
 505                      FROM contacts cp
 506                      LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
 507                      LEFT JOIN vendor v   ON (cp.cp_cv_id = v.id)
 508                      $where
 509                      ORDER BY $order_by|;
 510
 511   my $contacts  = selectall_hashref_query($::form, $dbh, $query, @values);
 512
 513   $::lxdebug->leave_sub;
 514
 515   return @{ $contacts };
 516 }
 517
 518
 519 1;