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 $c_mandate    = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
 312   my $query        = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
 313                         FROM ${table}
 314                         WHERE id IN (${placeholders})|;
 315
 316   my $result       = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
 317
 318   if (ref $params{id} eq 'ARRAY') {
 319     $result = { map { $_->{id} => $_ } @{ $result } };
 320   } else {
 321     $result = $result->[0] || { 'id' => $params{id} };
 322   }
 323
 324   $main::lxdebug->leave_sub();
 325
 326   return $result;
 327 }
 328
 329 sub search_contacts {
 330   $::lxdebug->enter_sub;
 331
 332   my $self      = shift;
 333   my %params    = @_;
 334
 335   my $dbh       = $params{dbh} || $::form->get_standard_dbh;
 336
 337   my %sortspecs = (
 338     'cp_name'   => 'cp_name, cp_givenname',
 339     'vcname'    => 'vcname, cp_name, cp_givenname',
 340     'vcnumber'  => 'vcnumber, cp_name, cp_givenname',
 341     );
 342
 343   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);
 344
 345   my $order_by  = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
 346   $::form->{sort} = $order_by;
 347   $order_by     = $sortspecs{$order_by} if ($sortspecs{$order_by});
 348
 349   my $sortdir   = $::form->{sortdir} ? 'ASC' : 'DESC';
 350   $order_by     =~ s/,/ ${sortdir},/g;
 351   $order_by    .= " $sortdir";
 352
 353   my @where_tokens = ();
 354   my @values;
 355
 356   if ($params{search_term}) {
 357     my @tokens;
 358     push @tokens,
 359       'cp.cp_name      ILIKE ?',
 360       'cp.cp_givenname ILIKE ?',
 361       'cp.cp_email     ILIKE ?';
 362     push @values, ('%' . $params{search_term} . '%') x 3;
 363
 364     if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
 365       my $number =  $params{search_term};
 366       $number    =~ s/[^\d]//g;
 367       $number    =  join '[ /\(\)+\-]*', split(m//, $number);
 368
 369       push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
 370     }
 371
 372     push @where_tokens, map { "($_)" } join ' OR ', @tokens;
 373   }
 374
 375   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'Contacts',
 376                                                             'trans_id_field' => 'cp.cp_id',
 377                                                             'filter'         => $params{filter});
 378
 379   if ($cvar_where) {
 380     push @where_tokens, $cvar_where;
 381     push @values, @cvar_values;
 382   }
 383
 384   if (my $filter = $params{filter}) {
 385     for (qw(name title givenname email project abteilung)) {
 386       next unless $filter->{"cp_$_"};
 387       add_token(\@where_tokens, \@values, col =>  "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
 388     }
 389
 390     push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
 391     push @where_tokens, 'cp.cp_cv_id IS NULL'     if $filter->{status} eq 'orphaned';
 392   }
 393
 394   my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
 395
 396   my $query     = qq|SELECT cp.*,
 397                        COALESCE(c.id,             v.id)           AS vcid,
 398                        COALESCE(c.name,           v.name)         AS vcname,
 399                        COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
 400                        CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
 401                      FROM contacts cp
 402                      LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
 403                      LEFT JOIN vendor v   ON (cp.cp_cv_id = v.id)
 404                      $where
 405                      ORDER BY $order_by|;
 406
 407   my $contacts  = selectall_hashref_query($::form, $dbh, $query, @values);
 408
 409   $::lxdebug->leave_sub;
 410
 411   return @{ $contacts };
 412 }
 413
 414
 415 1;