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