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       "department_1"       => "ct.department_1",
  70       "department_2"       => "ct.department_2",
  71       "contact"            => "ct.contact",
  72       "phone"              => "ct.phone",
  73       "fax"                => "ct.fax",
  74       "email"              => "ct.email",
  75       "street"             => "ct.street",
  76       "taxnumber"          => "ct.taxnumber",
  77       "business"           => "b.description",
  78       "invnumber"          => "ct.invnumber",
  79       "ordnumber"          => "ct.ordnumber",
  80       "quonumber"          => "ct.quonumber",
  81       "zipcode"            => "ct.zipcode",
  82       "city"               => "ct.city",
  83       "country"            => "ct.country",
  84       "gln"                => "ct.gln",
  85       "discount"           => "ct.discount",
  86       "insertdate"         => "ct.itime",
  87       "salesman"           => "e.name",
  88       "payment"            => "pt.description",
  89       "taxzone"            => "tz.description",
  90       "pricegroup"         => "pg.pricegroup",
  91       "ustid"              => "ct.ustid",
  92       "creditlimit"        => "ct.creditlimit",
  93       "commercial_court"   => "ct.commercial_court",
  94       "dunning_lock"       => "ct.dunning_lock",
  95     );
  96
  97   $form->{sort} ||= "name";
  98   my $sortorder;
  99   if ( $join_records ) {
 100     # in UNION case order by hash key, e.g. salesman
 101     # the UNION created an implicit select around the result
 102     $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
 103   } else {
 104     # in not UNION case order by hash value, e.g. e.name
 105     $sortorder = $allowed_sort_columns{$form->{sort}} ?  $allowed_sort_columns{$form->{sort}} : "ct.name";
 106   }
 107   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
 108
 109   if ($sortorder !~ /(business|creditlimit|id|discount|itime|dunning_lock)/ && !$join_records) {
 110     $sortorder  = "lower($sortorder) ${sortdir}";
 111   } else {
 112     $sortorder .= " ${sortdir}";
 113   }
 114
 115   if ($form->{"${cv}number"}) {
 116     $where .= " AND ct.${cv}number ILIKE ?";
 117     push(@values, like($form->{"${cv}number"}));
 118   }
 119
 120   foreach my $key (qw(name department_1 department_2 contact email)) {
 121     if ($form->{$key} ne '') {
 122       $where .= " AND ct.$key ILIKE ?";
 123       push(@values, like($form->{$key}));
 124     }
 125   }
 126
 127   if ($form->{cp_name}) {
 128     $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
 129     push @values, like($form->{cp_name});
 130   }
 131
 132   if ($form->{addr_street}) {
 133     $where .= qq| AND ((ct.street ILIKE ?) | .
 134               qq|      OR | .
 135               qq|      (ct.id IN ( | .
 136               qq|         SELECT sc.trans_id FROM shipto sc | .
 137               qq|         WHERE (sc.module = 'CT') | .
 138               qq|           AND (sc.shiptostreet ILIKE ?) | .
 139               qq|      ))) |;
 140     push @values, (like($form->{addr_street})) x 2;
 141   }
 142
 143   if ($form->{addr_zipcode}) {
 144     $where .= qq| AND ((ct.zipcode ILIKE ?) | .
 145               qq|      OR | .
 146               qq|      (ct.id IN ( | .
 147               qq|         SELECT sc.trans_id FROM shipto sc | .
 148               qq|         WHERE (sc.module = 'CT') | .
 149               qq|           AND (sc.shiptozipcode ILIKE ?) | .
 150               qq|      ))) |;
 151     push @values, (like($form->{addr_zipcode})) x 2;
 152   }
 153
 154   if ($form->{addr_city}) {
 155     $where .= " AND ((lower(ct.city) LIKE lower(?))
 156                      OR
 157                      (ct.id IN (
 158                         SELECT sc.trans_id
 159                         FROM shipto sc
 160                         WHERE (sc.module = 'CT')
 161                           AND (lower(sc.shiptocity) LIKE lower(?))
 162                       ))
 163                      )";
 164     push @values, (like($form->{addr_city})) x 2;
 165   }
 166
 167   if ($form->{addr_country}) {
 168     $where .= " AND ((lower(ct.country) LIKE lower(?))
 169                      OR
 170                      (ct.id IN (
 171                         SELECT so.trans_id
 172                         FROM shipto so
 173                         WHERE (so.module = 'CT')
 174                           AND (lower(so.shiptocountry) LIKE lower(?))
 175                       ))
 176                      )";
 177     push @values, (like($form->{addr_country})) x 2;
 178   }
 179
 180   if ($form->{addr_gln}) {
 181     $where .= " AND ((lower(ct.gln) LIKE lower(?))
 182                      OR
 183                      (ct.id IN (
 184                         SELECT so.trans_id
 185                         FROM shipto so
 186                         WHERE (so.module = 'CT')
 187                           AND (lower(so.shiptogln) LIKE lower(?))
 188                       ))
 189                      )";
 190     push @values, (like($form->{addr_gln})) x 2;
 191   }
 192
 193   if ( $form->{status} eq 'orphaned' ) {
 194     $where .=
 195       qq| AND ct.id NOT IN | .
 196       qq|   (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
 197     if ($cv eq 'customer') {
 198       $where .=
 199         qq| AND ct.id NOT IN | .
 200         qq| (SELECT a.customer_id FROM ar a, customer cv | .
 201         qq|  WHERE cv.id = a.customer_id)|;
 202     }
 203     if ($cv eq 'vendor') {
 204       $where .=
 205         qq| AND ct.id NOT IN | .
 206         qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
 207         qq|  WHERE cv.id = a.vendor_id)|;
 208     }
 209     $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
 210   }
 211
 212   if ($form->{obsolete} eq "Y") {
 213     $where .= qq| AND ct.obsolete|;
 214   } elsif ($form->{obsolete} eq "N") {
 215     $where .= qq| AND NOT ct.obsolete|;
 216   }
 217
 218   if ($form->{business_id}) {
 219     $where .= qq| AND (ct.business_id = ?)|;
 220     push(@values, conv_i($form->{business_id}));
 221   }
 222
 223   if ($form->{salesman_id}) {
 224     $where .= qq| AND (ct.salesman_id = ?)|;
 225     push(@values, conv_i($form->{salesman_id}));
 226   }
 227
 228   if ($form->{payment_id}) {
 229     $where .= qq| AND (ct.payment_id = ?)|;
 230     push(@values, $form->{payment_id});
 231   }
 232
 233   if ($form->{taxzone_id}) {
 234     $where .= qq| AND (ct.taxzone_id = ?)|;
 235     push(@values, $form->{taxzone_id});
 236   }
 237
 238   if($form->{insertdatefrom}) {
 239     $where .= qq| AND (ct.itime::DATE >= ?)|;
 240     push@values, conv_date($form->{insertdatefrom});
 241   }
 242
 243   if($form->{insertdateto}) {
 244     $where .= qq| AND (ct.itime::DATE <= ?)|;
 245     push @values, conv_date($form->{insertdateto});
 246   }
 247
 248   if($form->{dunning_lock} ne '') {
 249     $where .= qq| AND ct.dunning_lock = ?|;
 250     push @values, $form->{dunning_lock};
 251   }
 252
 253   if ($form->{all}) {
 254     my @tokens = parse_line('\s+', 0, $form->{all});
 255       $where .= qq| AND (
 256           ct.${cv}number ILIKE ? OR
 257           ct.name        ILIKE ?
 258           )| for @tokens;
 259     push @values, ("%$_%")x2 for @tokens;
 260   }
 261
 262   if (($form->{create_zugferd_invoices} // '') ne '') {
 263     $where .= qq| AND (ct.create_zugferd_invoices = ?)|;
 264     push @values, $form->{create_zugferd_invoices};
 265   }
 266
 267   if ($form->{all_phonenumbers}) {
 268     my $search_term = trim($form->{all_phonenumbers});
 269     $search_term    =~ s{\p{WSpace}+}{}g;
 270     $search_term    = join ' *', split(//, $search_term);
 271
 272     $where .= qq| AND (ct.phone ~* ? OR
 273                        ct.fax   ~* ? OR
 274                        ct.id    IN
 275                          (SELECT cp_cv_id FROM contacts
 276                           WHERE cp_phone1      ~* ? OR
 277                                 cp_phone2      ~* ? OR
 278                                 cp_fax         ~* ? OR
 279                                 cp_mobile1     ~* ? OR
 280                                 cp_mobile2     ~* ? OR
 281                                 cp_satphone    ~* ? OR
 282                                 cp_satfax      ~* ? OR
 283                                 cp_privatphone ~* ?
 284                          )
 285     )|;
 286     push @values, ($search_term)x10;
 287   }
 288
 289   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'CT',
 290                                                             'trans_id_field' => 'ct.id',
 291                                                             'filter'         => $form);
 292
 293   if ($cvar_where) {
 294     $where .= qq| AND ($cvar_where)|;
 295     push @values, @cvar_values;
 296   }
 297
 298   my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
 299   my $pg_join   = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
 300
 301   my $main_cp_select = '';
 302   if ($form->{l_main_contact_person}) {
 303     $main_cp_select =  qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
 304                               FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
 305                               AS main_contact_person /;
 306   }
 307   my $query =
 308     qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 309     qq|  pt.description as payment, tz.description as taxzone | .
 310     $pg_select .
 311     $main_cp_select .
 312     (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) .
 313     qq|FROM $cv ct | .
 314     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 315     qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 316     qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 317     qq|LEFT JOIN tax_zones tz ON (ct.taxzone_id = tz.id) | .
 318     $pg_join .
 319     qq|WHERE $where|;
 320
 321   my @saved_values = @values;
 322   # redo for invoices, orders and quotations
 323   if ($join_records) {
 324     my $union = "UNION";
 325
 326     if ($form->{l_invnumber}) {
 327       my $ar = $cv eq 'customer' ? 'ar' : 'ap';
 328       my $module = $ar eq 'ar' ? 'is' : 'ir';
 329       push(@values, @saved_values);
 330       $query .=
 331         qq| UNION | .
 332         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 333         qq|  pt.description as payment, tz.description as taxzone | .
 334         $pg_select .
 335         $main_cp_select .
 336         qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
 337         qq|  '$module' AS module, 'invoice' AS formtype, | .
 338         qq|  (a.amount = a.paid) AS closed | .
 339         qq|FROM $cv ct | .
 340         qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
 341         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 342         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 343         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 344         qq|LEFT JOIN tax_zones tz ON (ct.taxzone_id = tz.id) | .
 345         $pg_join .
 346         qq|WHERE $where AND (a.invoice = '1')|;
 347     }
 348
 349     if ( $form->{l_ordnumber} ) {
 350       push(@values, @saved_values);
 351       $query .=
 352         qq| UNION | .
 353         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 354         qq|  pt.description as payment, tz.description as taxzone | .
 355         $pg_select .
 356         $main_cp_select .
 357         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 358         qq|  'oe' AS module, 'order' AS formtype, o.closed | .
 359         qq|FROM $cv ct | .
 360         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 361         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 362         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 363         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 364         qq|LEFT JOIN tax_zones tz ON (ct.taxzone_id = tz.id) | .
 365         $pg_join .
 366         qq|WHERE $where AND ((o.record_type = 'sales_order') OR (o.record_type = 'purchase_order'))|;
 367     }
 368
 369     if ( $form->{l_quonumber} ) {
 370       push(@values, @saved_values);
 371       $query .=
 372         qq| UNION | .
 373         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 374         qq|  pt.description as payment, tz.description as taxzone | .
 375         $pg_select .
 376         $main_cp_select .
 377         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 378         qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
 379         qq|FROM $cv ct | .
 380         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 381         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 382         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 383         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 384         qq|LEFT JOIN tax_zones tz ON (ct.taxzone_id = tz.id) | .
 385         $pg_join .
 386         qq|WHERE $where AND ((o.record_type = 'sales_quotation') OR (o.record_type = 'request_quotation'))|;
 387     }
 388   }
 389
 390   $query .= qq| ORDER BY $sortorder|;
 391
 392   $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
 393
 394   $main::lxdebug->leave_sub();
 395 }
 396
 397 sub get_contact {
 398   $main::lxdebug->enter_sub();
 399
 400   my ( $self, $myconfig, $form ) = @_;
 401
 402   die 'Missing argument: cp_id' unless $::form->{cp_id};
 403
 404   my $dbh   = SL::DB->client->dbh;
 405   my $query =
 406     qq|SELECT * FROM contacts c | .
 407     qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
 408   my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
 409   my $ref = $sth->fetchrow_hashref("NAME_lc");
 410
 411   map { $form->{$_} = $ref->{$_} } keys %$ref;
 412
 413   $query = qq|SELECT COUNT(cp_id) AS used FROM (
 414     SELECT cp_id FROM oe UNION
 415     SELECT cp_id FROM ar UNION
 416     SELECT cp_id FROM ap UNION
 417     SELECT cp_id FROM delivery_orders
 418   ) AS cpid WHERE cp_id = ? OR ? = 0|;
 419   ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
 420
 421   $sth->finish;
 422
 423   $main::lxdebug->leave_sub();
 424 }
 425
 426 sub get_bank_info {
 427   $main::lxdebug->enter_sub();
 428
 429   my $self     = shift;
 430   my %params   = @_;
 431
 432   Common::check_params(\%params, qw(vc id));
 433
 434   my $myconfig = \%main::myconfig;
 435   my $form     = $main::form;
 436
 437   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 438
 439   my $table        = $params{vc} eq 'customer' ? 'customer' : 'vendor';
 440   my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
 441   my $placeholders = join ", ", ('?') x scalar @ids;
 442   my $c_mandate    = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
 443   my $query        = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
 444                         FROM ${table}
 445                         WHERE id IN (${placeholders})|;
 446
 447   my $result       = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
 448
 449   if (ref $params{id} eq 'ARRAY') {
 450     $result = { map { $_->{id} => $_ } @{ $result } };
 451   } else {
 452     $result = $result->[0] || { 'id' => $params{id} };
 453   }
 454
 455   $main::lxdebug->leave_sub();
 456
 457   return $result;
 458 }
 459
 460 sub search_contacts {
 461   $::lxdebug->enter_sub;
 462
 463   my $self      = shift;
 464   my %params    = @_;
 465
 466   my $dbh       = $params{dbh} || $::form->get_standard_dbh;
 467
 468   my %sortspecs = (
 469     'cp_name'   => 'cp_name, cp_givenname',
 470     'vcname'    => 'vcname, cp_name, cp_givenname',
 471     'vcnumber'  => 'vcnumber, cp_name, cp_givenname',
 472     );
 473
 474   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);
 475
 476   my $order_by  = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
 477   $::form->{sort} = $order_by;
 478   $order_by     = $sortspecs{$order_by} if ($sortspecs{$order_by});
 479
 480   my $sortdir   = $::form->{sortdir} ? 'ASC' : 'DESC';
 481   $order_by     =~ s/,/ ${sortdir},/g;
 482   $order_by    .= " $sortdir";
 483
 484   my @where_tokens = ();
 485   my @values;
 486
 487   if ($params{search_term}) {
 488     my @tokens;
 489     push @tokens,
 490       'cp.cp_name      ILIKE ?',
 491       'cp.cp_givenname ILIKE ?',
 492       'cp.cp_email     ILIKE ?';
 493     push @values, (like($params{search_term})) x 3;
 494
 495     if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
 496       my $number =  $params{search_term};
 497       $number    =~ s/[^\d]//g;
 498       $number    =  join '[ /\(\)+\-]*', split(m//, $number);
 499
 500       push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
 501     }
 502
 503     push @where_tokens, map { "($_)" } join ' OR ', @tokens;
 504   }
 505
 506   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'Contacts',
 507                                                             'trans_id_field' => 'cp.cp_id',
 508                                                             'filter'         => $params{filter});
 509
 510   if ($cvar_where) {
 511     push @where_tokens, $cvar_where;
 512     push @values, @cvar_values;
 513   }
 514
 515   if (my $filter = $params{filter}) {
 516     for (qw(name title givenname email project abteilung)) {
 517       next unless $filter->{"cp_$_"};
 518       add_token(\@where_tokens, \@values, col =>  "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
 519     }
 520
 521     push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
 522     push @where_tokens, 'cp.cp_cv_id IS NULL'     if $filter->{status} eq 'orphaned';
 523   }
 524
 525   my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
 526
 527   my $query     = qq|SELECT cp.*,
 528                        COALESCE(c.id,             v.id)           AS vcid,
 529                        COALESCE(c.name,           v.name)         AS vcname,
 530                        COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
 531                        CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
 532                      FROM contacts cp
 533                      LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
 534                      LEFT JOIN vendor v   ON (cp.cp_cv_id = v.id)
 535                      $where
 536                      ORDER BY $order_by|;
 537
 538   my $contacts  = selectall_hashref_query($::form, $dbh, $query, @values);
 539
 540   $::lxdebug->leave_sub;
 541
 542   return @{ $contacts };
 543 }
 544
 545
 546 1;