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