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