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