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