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 SL::DB;
  44 use Text::ParseWords;
  45
  46 use strict;
  47
  48 sub search {
  49   $main::lxdebug->enter_sub();
  50
  51   my ( $self, $myconfig, $form ) = @_;
  52
  53   # connect to database
  54   my $dbh = $form->dbconnect($myconfig);
  55
  56   my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
  57   my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
  58
  59   my $where = "1 = 1";
  60   my @values;
  61
  62   my %allowed_sort_columns = (
  63       "id"                 => "ct.id",
  64       "customernumber"     => "ct.customernumber",
  65       "vendornumber"       => "ct.vendornumber",
  66       "name"               => "ct.name",
  67       "contact"            => "ct.contact",
  68       "phone"              => "ct.phone",
  69       "fax"                => "ct.fax",
  70       "email"              => "ct.email",
  71       "street"             => "ct.street",
  72       "taxnumber"          => "ct.taxnumber",
  73       "business"           => "b.description",
  74       "invnumber"          => "ct.invnumber",
  75       "ordnumber"          => "ct.ordnumber",
  76       "quonumber"          => "ct.quonumber",
  77       "zipcode"            => "ct.zipcode",
  78       "city"               => "ct.city",
  79       "country"            => "ct.country",
  80       "gln"                => "ct.gln",
  81       "discount"           => "ct.discount",
  82       "insertdate"         => "ct.itime",
  83       "salesman"           => "e.name",
  84       "payment"            => "pt.description",
  85       "pricegroup"         => "pg.pricegroup",
  86     );
  87
  88   $form->{sort} ||= "name";
  89   my $sortorder;
  90   if ( $join_records ) {
  91     # in UNION case order by hash key, e.g. salesman
  92     # the UNION created an implicit select around the result
  93     $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
  94   } else {
  95     # in not UNION case order by hash value, e.g. e.name
  96     $sortorder = $allowed_sort_columns{$form->{sort}} ?  $allowed_sort_columns{$form->{sort}} : "ct.name";
  97   }
  98   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
  99
 100   if ($sortorder !~ /(business|id|discount|itime)/ && !$join_records) {
 101     $sortorder  = "lower($sortorder) ${sortdir}";
 102   } else {
 103     $sortorder .= " ${sortdir}";
 104   }
 105
 106   if ($form->{"${cv}number"}) {
 107     $where .= " AND ct.${cv}number ILIKE ?";
 108     push(@values, like($form->{"${cv}number"}));
 109   }
 110
 111   foreach my $key (qw(name contact email)) {
 112     if ($form->{$key}) {
 113       $where .= " AND ct.$key ILIKE ?";
 114       push(@values, like($form->{$key}));
 115     }
 116   }
 117
 118   if ($form->{cp_name}) {
 119     $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
 120     push @values, like($form->{cp_name});
 121   }
 122
 123   if ($form->{addr_street}) {
 124     $where .= qq| AND ((ct.street ILIKE ?) | .
 125               qq|      OR | .
 126               qq|      (ct.id IN ( | .
 127               qq|         SELECT sc.trans_id FROM shipto sc | .
 128               qq|         WHERE (sc.module = 'CT') | .
 129               qq|           AND (sc.shiptostreet ILIKE ?) | .
 130               qq|      ))) |;
 131     push @values, (like($form->{addr_street})) x 2;
 132   }
 133
 134   if ($form->{addr_zipcode}) {
 135     $where .= qq| AND ((ct.zipcode ILIKE ?) | .
 136               qq|      OR | .
 137               qq|      (ct.id IN ( | .
 138               qq|         SELECT sc.trans_id FROM shipto sc | .
 139               qq|         WHERE (sc.module = 'CT') | .
 140               qq|           AND (sc.shiptozipcode ILIKE ?) | .
 141               qq|      ))) |;
 142     push @values, (like($form->{addr_zipcode})) x 2;
 143   }
 144
 145   if ($form->{addr_city}) {
 146     $where .= " AND ((lower(ct.city) LIKE lower(?))
 147                      OR
 148                      (ct.id IN (
 149                         SELECT sc.trans_id
 150                         FROM shipto sc
 151                         WHERE (sc.module = 'CT')
 152                           AND (lower(sc.shiptocity) LIKE lower(?))
 153                       ))
 154                      )";
 155     push @values, (like($form->{addr_city})) x 2;
 156   }
 157
 158   if ($form->{addr_country}) {
 159     $where .= " AND ((lower(ct.country) LIKE lower(?))
 160                      OR
 161                      (ct.id IN (
 162                         SELECT so.trans_id
 163                         FROM shipto so
 164                         WHERE (so.module = 'CT')
 165                           AND (lower(so.shiptocountry) LIKE lower(?))
 166                       ))
 167                      )";
 168     push @values, (like($form->{addr_country})) x 2;
 169   }
 170
 171   if ($form->{addr_gln}) {
 172     $where .= " AND ((lower(ct.gln) LIKE lower(?))
 173                      OR
 174                      (ct.id IN (
 175                         SELECT so.trans_id
 176                         FROM shipto so
 177                         WHERE (so.module = 'CT')
 178                           AND (lower(so.shiptogln) LIKE lower(?))
 179                       ))
 180                      )";
 181     push @values, (like($form->{addr_gln})) x 2;
 182   }
 183
 184   if ( $form->{status} eq 'orphaned' ) {
 185     $where .=
 186       qq| AND ct.id NOT IN | .
 187       qq|   (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
 188     if ($cv eq 'customer') {
 189       $where .=
 190         qq| AND ct.id NOT IN | .
 191         qq| (SELECT a.customer_id FROM ar a, customer cv | .
 192         qq|  WHERE cv.id = a.customer_id)|;
 193     }
 194     if ($cv eq 'vendor') {
 195       $where .=
 196         qq| AND ct.id NOT IN | .
 197         qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
 198         qq|  WHERE cv.id = a.vendor_id)|;
 199     }
 200     $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
 201   }
 202
 203   if ($form->{obsolete} eq "Y") {
 204     $where .= qq| AND ct.obsolete|;
 205   } elsif ($form->{obsolete} eq "N") {
 206     $where .= qq| AND NOT ct.obsolete|;
 207   }
 208
 209   if ($form->{business_id}) {
 210     $where .= qq| AND (ct.business_id = ?)|;
 211     push(@values, conv_i($form->{business_id}));
 212   }
 213
 214   if ($form->{salesman_id}) {
 215     $where .= qq| AND (ct.salesman_id = ?)|;
 216     push(@values, conv_i($form->{salesman_id}));
 217   }
 218
 219   if($form->{insertdatefrom}) {
 220     $where .= qq| AND (ct.itime::DATE >= ?)|;
 221     push@values, conv_date($form->{insertdatefrom});
 222   }
 223
 224   if($form->{insertdateto}) {
 225     $where .= qq| AND (ct.itime::DATE <= ?)|;
 226     push @values, conv_date($form->{insertdateto});
 227   }
 228
 229   if ($form->{all}) {
 230     my @tokens = parse_line('\s+', 0, $form->{all});
 231       $where .= qq| AND (
 232           ct.${cv}number ILIKE ? OR
 233           ct.name        ILIKE ?
 234           )| for @tokens;
 235     push @values, ("%$_%")x2 for @tokens;
 236   }
 237
 238   # Nur Kunden finden, bei denen ich selber der Verkäufer bin
 239   # Gilt nicht für Lieferanten
 240   if ($cv eq 'customer' &&   !$main::auth->assert('customer_vendor_all_edit', 1)) {
 241     $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
 242     push(@values, $::myconfig{login});
 243   }
 244
 245   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'CT',
 246                                                             'trans_id_field' => 'ct.id',
 247                                                             'filter'         => $form);
 248
 249   if ($cvar_where) {
 250     $where .= qq| AND ($cvar_where)|;
 251     push @values, @cvar_values;
 252   }
 253
 254   my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
 255   my $pg_join   = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
 256   my $query =
 257     qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 258     qq|  pt.description as payment | .
 259     $pg_select .
 260     (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) .
 261     qq|FROM $cv ct | .
 262     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 263     qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 264     qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 265     $pg_join .
 266     qq|WHERE $where|;
 267
 268   my @saved_values = @values;
 269   # redo for invoices, orders and quotations
 270   if ($join_records) {
 271     my $union = "UNION";
 272
 273     if ($form->{l_invnumber}) {
 274       my $ar = $cv eq 'customer' ? 'ar' : 'ap';
 275       my $module = $ar eq 'ar' ? 'is' : 'ir';
 276       push(@values, @saved_values);
 277       $query .=
 278         qq| UNION | .
 279         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 280         qq|  pt.description as payment | .
 281         $pg_select .
 282         qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
 283         qq|  '$module' AS module, 'invoice' AS formtype, | .
 284         qq|  (a.amount = a.paid) AS closed | .
 285         qq|FROM $cv ct | .
 286         qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
 287         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 288         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 289         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 290         $pg_join .
 291         qq|WHERE $where AND (a.invoice = '1')|;
 292     }
 293
 294     if ( $form->{l_ordnumber} ) {
 295       push(@values, @saved_values);
 296       $query .=
 297         qq| UNION | .
 298         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 299         qq|  pt.description as payment | .
 300         $pg_select .
 301         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 302         qq|  'oe' AS module, 'order' AS formtype, o.closed | .
 303         qq|FROM $cv ct | .
 304         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 305         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 306         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 307         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 308         $pg_join .
 309         qq|WHERE $where AND (o.quotation = '0')|;
 310     }
 311
 312     if ( $form->{l_quonumber} ) {
 313       push(@values, @saved_values);
 314       $query .=
 315         qq| UNION | .
 316         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
 317         qq|  pt.description as payment | .
 318         $pg_select .
 319         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 320         qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
 321         qq|FROM $cv ct | .
 322         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 323         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 324         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 325         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 326         $pg_join .
 327         qq|WHERE $where AND (o.quotation = '1')|;
 328     }
 329   }
 330
 331   $query .= qq| ORDER BY $sortorder|;
 332
 333   $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
 334
 335   $main::lxdebug->leave_sub();
 336 }
 337
 338 sub get_contact {
 339   $main::lxdebug->enter_sub();
 340
 341   my ( $self, $myconfig, $form ) = @_;
 342
 343   die 'Missing argument: cp_id' unless $::form->{cp_id};
 344
 345   my $dbh   = SL::DB->client->dbh;
 346   my $query =
 347     qq|SELECT * FROM contacts c | .
 348     qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
 349   my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
 350   my $ref = $sth->fetchrow_hashref("NAME_lc");
 351
 352   map { $form->{$_} = $ref->{$_} } keys %$ref;
 353
 354   $query = qq|SELECT COUNT(cp_id) AS used FROM (
 355     SELECT cp_id FROM oe UNION
 356     SELECT cp_id FROM ar UNION
 357     SELECT cp_id FROM ap UNION
 358     SELECT cp_id FROM delivery_orders
 359   ) AS cpid WHERE cp_id = ? OR ? = 0|;
 360   ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
 361
 362   $sth->finish;
 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, (like($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;