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