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