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