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