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