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