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     my $search_term = trim($form->{all_phonenumbers});
 250     $search_term    =~ s{\p{WSpace}+}{}g;
 251     $search_term    = join ' *', split(//, $search_term);
 252
 253     $where .= qq| AND (ct.phone ~* ? OR
 254                        ct.fax   ~* ? OR
 255                        ct.id    IN
 256                          (SELECT cp_cv_id FROM contacts
 257                           WHERE cp_phone1      ~* ? OR
 258                                 cp_phone2      ~* ? OR
 259                                 cp_fax         ~* ? OR
 260                                 cp_mobile1     ~* ? OR
 261                                 cp_mobile2     ~* ? OR
 262                                 cp_satphone    ~* ? OR
 263                                 cp_satfax      ~* ? OR
 264                                 cp_privatphone ~* ?
 265                          )
 266     )|;
 267     push @values, ($search_term)x10;
 268   }
 269
 270   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'CT',
 271                                                             'trans_id_field' => 'ct.id',
 272                                                             'filter'         => $form);
 273
 274   if ($cvar_where) {
 275     $where .= qq| AND ($cvar_where)|;
 276     push @values, @cvar_values;
 277   }
 278
 279   my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
 280   my $pg_join   = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
 281
 282   my $main_cp_select = '';
 283   if ($form->{l_main_contact_person}) {
 284     $main_cp_select =  qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
 285                               FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
 286                               AS main_contact_person /;
 287   }
 288   my $query =
 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|, NULL AS invnumber, NULL AS ordnumber, NULL AS quonumber, NULL AS invid, NULL AS module, NULL AS formtype, NULL AS closed | x!! $join_records) .
 294     qq|FROM $cv ct | .
 295     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 296     qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 297     qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 298     $pg_join .
 299     qq|WHERE $where|;
 300
 301   my @saved_values = @values;
 302   # redo for invoices, orders and quotations
 303   if ($join_records) {
 304     my $union = "UNION";
 305
 306     if ($form->{l_invnumber}) {
 307       my $ar = $cv eq 'customer' ? 'ar' : 'ap';
 308       my $module = $ar eq 'ar' ? 'is' : 'ir';
 309       push(@values, @saved_values);
 310       $query .=
 311         qq| UNION | .
 312         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 313         qq|  pt.description as payment | .
 314         $pg_select .
 315         $main_cp_select .
 316         qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
 317         qq|  '$module' AS module, 'invoice' AS formtype, | .
 318         qq|  (a.amount = a.paid) AS closed | .
 319         qq|FROM $cv ct | .
 320         qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
 321         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 322         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 323         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 324         $pg_join .
 325         qq|WHERE $where AND (a.invoice = '1')|;
 326     }
 327
 328     if ( $form->{l_ordnumber} ) {
 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 | .
 334         $pg_select .
 335         $main_cp_select .
 336         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 337         qq|  'oe' AS module, 'order' AS formtype, o.closed | .
 338         qq|FROM $cv ct | .
 339         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 340         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 341         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 342         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 343         $pg_join .
 344         qq|WHERE $where AND (o.quotation = '0')|;
 345     }
 346
 347     if ( $form->{l_quonumber} ) {
 348       push(@values, @saved_values);
 349       $query .=
 350         qq| UNION | .
 351         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 352         qq|  pt.description as payment | .
 353         $pg_select .
 354         $main_cp_select .
 355         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 356         qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
 357         qq|FROM $cv ct | .
 358         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 359         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 360         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 361         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 362         $pg_join .
 363         qq|WHERE $where AND (o.quotation = '1')|;
 364     }
 365   }
 366
 367   $query .= qq| ORDER BY $sortorder|;
 368
 369   $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
 370
 371   $main::lxdebug->leave_sub();
 372 }
 373
 374 sub get_contact {
 375   $main::lxdebug->enter_sub();
 376
 377   my ( $self, $myconfig, $form ) = @_;
 378
 379   die 'Missing argument: cp_id' unless $::form->{cp_id};
 380
 381   my $dbh   = SL::DB->client->dbh;
 382   my $query =
 383     qq|SELECT * FROM contacts c | .
 384     qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
 385   my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
 386   my $ref = $sth->fetchrow_hashref("NAME_lc");
 387
 388   map { $form->{$_} = $ref->{$_} } keys %$ref;
 389
 390   $query = qq|SELECT COUNT(cp_id) AS used FROM (
 391     SELECT cp_id FROM oe UNION
 392     SELECT cp_id FROM ar UNION
 393     SELECT cp_id FROM ap UNION
 394     SELECT cp_id FROM delivery_orders
 395   ) AS cpid WHERE cp_id = ? OR ? = 0|;
 396   ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
 397
 398   $sth->finish;
 399
 400   $main::lxdebug->leave_sub();
 401 }
 402
 403 sub get_bank_info {
 404   $main::lxdebug->enter_sub();
 405
 406   my $self     = shift;
 407   my %params   = @_;
 408
 409   Common::check_params(\%params, qw(vc id));
 410
 411   my $myconfig = \%main::myconfig;
 412   my $form     = $main::form;
 413
 414   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 415
 416   my $table        = $params{vc} eq 'customer' ? 'customer' : 'vendor';
 417   my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
 418   my $placeholders = join ", ", ('?') x scalar @ids;
 419   my $c_mandate    = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
 420   my $query        = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
 421                         FROM ${table}
 422                         WHERE id IN (${placeholders})|;
 423
 424   my $result       = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
 425
 426   if (ref $params{id} eq 'ARRAY') {
 427     $result = { map { $_->{id} => $_ } @{ $result } };
 428   } else {
 429     $result = $result->[0] || { 'id' => $params{id} };
 430   }
 431
 432   $main::lxdebug->leave_sub();
 433
 434   return $result;
 435 }
 436
 437 sub search_contacts {
 438   $::lxdebug->enter_sub;
 439
 440   my $self      = shift;
 441   my %params    = @_;
 442
 443   my $dbh       = $params{dbh} || $::form->get_standard_dbh;
 444
 445   my %sortspecs = (
 446     'cp_name'   => 'cp_name, cp_givenname',
 447     'vcname'    => 'vcname, cp_name, cp_givenname',
 448     'vcnumber'  => 'vcnumber, cp_name, cp_givenname',
 449     );
 450
 451   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);
 452
 453   my $order_by  = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
 454   $::form->{sort} = $order_by;
 455   $order_by     = $sortspecs{$order_by} if ($sortspecs{$order_by});
 456
 457   my $sortdir   = $::form->{sortdir} ? 'ASC' : 'DESC';
 458   $order_by     =~ s/,/ ${sortdir},/g;
 459   $order_by    .= " $sortdir";
 460
 461   my @where_tokens = ();
 462   my @values;
 463
 464   if ($params{search_term}) {
 465     my @tokens;
 466     push @tokens,
 467       'cp.cp_name      ILIKE ?',
 468       'cp.cp_givenname ILIKE ?',
 469       'cp.cp_email     ILIKE ?';
 470     push @values, (like($params{search_term})) x 3;
 471
 472     if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
 473       my $number =  $params{search_term};
 474       $number    =~ s/[^\d]//g;
 475       $number    =  join '[ /\(\)+\-]*', split(m//, $number);
 476
 477       push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
 478     }
 479
 480     push @where_tokens, map { "($_)" } join ' OR ', @tokens;
 481   }
 482
 483   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'Contacts',
 484                                                             'trans_id_field' => 'cp.cp_id',
 485                                                             'filter'         => $params{filter});
 486
 487   if ($cvar_where) {
 488     push @where_tokens, $cvar_where;
 489     push @values, @cvar_values;
 490   }
 491
 492   if (my $filter = $params{filter}) {
 493     for (qw(name title givenname email project abteilung)) {
 494       next unless $filter->{"cp_$_"};
 495       add_token(\@where_tokens, \@values, col =>  "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
 496     }
 497
 498     push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
 499     push @where_tokens, 'cp.cp_cv_id IS NULL'     if $filter->{status} eq 'orphaned';
 500   }
 501
 502   my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
 503
 504   my $query     = qq|SELECT cp.*,
 505                        COALESCE(c.id,             v.id)           AS vcid,
 506                        COALESCE(c.name,           v.name)         AS vcname,
 507                        COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
 508                        CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
 509                      FROM contacts cp
 510                      LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
 511                      LEFT JOIN vendor v   ON (cp.cp_cv_id = v.id)
 512                      $where
 513                      ORDER BY $order_by|;
 514
 515   my $contacts  = selectall_hashref_query($::form, $dbh, $query, @values);
 516
 517   $::lxdebug->leave_sub;
 518
 519   return @{ $contacts };
 520 }
 521
 522
 523 1;