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   if ($form->{salesman_id}) {
 175     $where .= qq| AND (ct.salesman_id = ?)|;
 176     push(@values, conv_i($form->{salesman_id}));
 177   }
 178
 179   # Nur Kunden finden, bei denen ich selber der Verkäufer bin
 180   # Gilt nicht für Lieferanten
 181   if ($cv eq 'customer' &&   !$main::auth->assert('customer_vendor_all_edit', 1)) {
 182     $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
 183     push(@values, $::myconfig{login});
 184   }
 185
 186   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'CT',
 187                                                             'trans_id_field' => 'ct.id',
 188                                                             'filter'         => $form);
 189
 190   if ($cvar_where) {
 191     $where .= qq| AND ($cvar_where)|;
 192     push @values, @cvar_values;
 193   }
 194
 195   if ($form->{addr_street}) {
 196     $where .= qq| AND (ct.street ILIKE ?)|;
 197     push @values, '%' . $form->{addr_street} . '%';
 198   }
 199
 200   if ($form->{addr_zipcode}) {
 201     $where .= qq| AND (ct.zipcode ILIKE ?)|;
 202     push @values, $form->{addr_zipcode} . '%';
 203   }
 204
 205   my $query =
 206     qq|SELECT ct.*, b.description AS business, e.name as salesman, |.
 207     qq|  pt.description as payment | .
 208     (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) .
 209     qq|FROM $cv ct | .
 210     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 211     qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 212     qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 213     qq|WHERE $where|;
 214
 215   my @saved_values = @values;
 216   # redo for invoices, orders and quotations
 217   if ($join_records) {
 218     my $union = "UNION";
 219
 220     if ($form->{l_invnumber}) {
 221       my $ar = $cv eq 'customer' ? 'ar' : 'ap';
 222       my $module = $ar eq 'ar' ? 'is' : 'ir';
 223       push(@values, @saved_values);
 224       $query .=
 225         qq| UNION | .
 226         qq|SELECT ct.*, b.description AS business, e.name as salesman, |.
 227         qq|  pt.description as payment, | .
 228         qq|  a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
 229         qq|  '$module' AS module, 'invoice' AS formtype, | .
 230         qq|  (a.amount = a.paid) AS closed | .
 231         qq|FROM $cv ct | .
 232         qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
 233         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 234         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 235         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 236         qq|WHERE $where AND (a.invoice = '1')|;
 237     }
 238
 239     if ( $form->{l_ordnumber} ) {
 240       push(@values, @saved_values);
 241       $query .=
 242         qq| UNION | .
 243         qq|SELECT ct.*, b.description AS business, e.name as salesman, |.
 244         qq|  pt.description as payment, | .
 245         qq|  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 246         qq|  'oe' AS module, 'order' AS formtype, o.closed | .
 247         qq|FROM $cv ct | .
 248         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 249         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 250         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 251         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 252         qq|WHERE $where AND (o.quotation = '0')|;
 253     }
 254
 255     if ( $form->{l_quonumber} ) {
 256       push(@values, @saved_values);
 257       $query .=
 258         qq| UNION | .
 259         qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
 260         qq|  pt.description as payment, | .
 261         qq|  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
 262         qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
 263         qq|FROM $cv ct | .
 264         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
 265         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
 266         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
 267         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
 268         qq|WHERE $where AND (o.quotation = '1')|;
 269     }
 270   }
 271
 272   $query .= qq| ORDER BY $sortorder|;
 273
 274   $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
 275
 276   $main::lxdebug->leave_sub();
 277 }
 278
 279 sub get_contact {
 280   $main::lxdebug->enter_sub();
 281
 282   my ( $self, $myconfig, $form ) = @_;
 283
 284   die 'Missing argument: cp_id' unless $::form->{cp_id};
 285
 286   my $dbh   = $form->dbconnect($myconfig);
 287   my $query =
 288     qq|SELECT * FROM contacts c | .
 289     qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
 290   my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
 291   my $ref = $sth->fetchrow_hashref("NAME_lc");
 292
 293   map { $form->{$_} = $ref->{$_} } keys %$ref;
 294
 295   $query = qq|SELECT COUNT(cp_id) AS used FROM (
 296     SELECT cp_id FROM oe UNION
 297     SELECT cp_id FROM ar UNION
 298     SELECT cp_id FROM ap UNION
 299     SELECT cp_id FROM delivery_orders
 300   ) AS cpid WHERE cp_id = ? OR ? = 0|;
 301   ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
 302
 303   $sth->finish;
 304   $dbh->disconnect;
 305
 306   $main::lxdebug->leave_sub();
 307 }
 308
 309 sub get_bank_info {
 310   $main::lxdebug->enter_sub();
 311
 312   my $self     = shift;
 313   my %params   = @_;
 314
 315   Common::check_params(\%params, qw(vc id));
 316
 317   my $myconfig = \%main::myconfig;
 318   my $form     = $main::form;
 319
 320   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 321
 322   my $table        = $params{vc} eq 'customer' ? 'customer' : 'vendor';
 323   my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
 324   my $placeholders = join ", ", ('?') x scalar @ids;
 325   my $c_mandate    = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
 326   my $query        = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
 327                         FROM ${table}
 328                         WHERE id IN (${placeholders})|;
 329
 330   my $result       = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
 331
 332   if (ref $params{id} eq 'ARRAY') {
 333     $result = { map { $_->{id} => $_ } @{ $result } };
 334   } else {
 335     $result = $result->[0] || { 'id' => $params{id} };
 336   }
 337
 338   $main::lxdebug->leave_sub();
 339
 340   return $result;
 341 }
 342
 343 sub search_contacts {
 344   $::lxdebug->enter_sub;
 345
 346   my $self      = shift;
 347   my %params    = @_;
 348
 349   my $dbh       = $params{dbh} || $::form->get_standard_dbh;
 350
 351   my %sortspecs = (
 352     'cp_name'   => 'cp_name, cp_givenname',
 353     'vcname'    => 'vcname, cp_name, cp_givenname',
 354     'vcnumber'  => 'vcnumber, cp_name, cp_givenname',
 355     );
 356
 357   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);
 358
 359   my $order_by  = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
 360   $::form->{sort} = $order_by;
 361   $order_by     = $sortspecs{$order_by} if ($sortspecs{$order_by});
 362
 363   my $sortdir   = $::form->{sortdir} ? 'ASC' : 'DESC';
 364   $order_by     =~ s/,/ ${sortdir},/g;
 365   $order_by    .= " $sortdir";
 366
 367   my @where_tokens = ();
 368   my @values;
 369
 370   if ($params{search_term}) {
 371     my @tokens;
 372     push @tokens,
 373       'cp.cp_name      ILIKE ?',
 374       'cp.cp_givenname ILIKE ?',
 375       'cp.cp_email     ILIKE ?';
 376     push @values, ('%' . $params{search_term} . '%') x 3;
 377
 378     if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
 379       my $number =  $params{search_term};
 380       $number    =~ s/[^\d]//g;
 381       $number    =  join '[ /\(\)+\-]*', split(m//, $number);
 382
 383       push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
 384     }
 385
 386     push @where_tokens, map { "($_)" } join ' OR ', @tokens;
 387   }
 388
 389   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'Contacts',
 390                                                             'trans_id_field' => 'cp.cp_id',
 391                                                             'filter'         => $params{filter});
 392
 393   if ($cvar_where) {
 394     push @where_tokens, $cvar_where;
 395     push @values, @cvar_values;
 396   }
 397
 398   if (my $filter = $params{filter}) {
 399     for (qw(name title givenname email project abteilung)) {
 400       next unless $filter->{"cp_$_"};
 401       add_token(\@where_tokens, \@values, col =>  "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
 402     }
 403
 404     push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
 405     push @where_tokens, 'cp.cp_cv_id IS NULL'     if $filter->{status} eq 'orphaned';
 406   }
 407
 408   my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
 409
 410   my $query     = qq|SELECT cp.*,
 411                        COALESCE(c.id,             v.id)           AS vcid,
 412                        COALESCE(c.name,           v.name)         AS vcname,
 413                        COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
 414                        CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
 415                      FROM contacts cp
 416                      LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
 417                      LEFT JOIN vendor v   ON (cp.cp_cv_id = v.id)
 418                      $where
 419                      ORDER BY $order_by|;
 420
 421   my $contacts  = selectall_hashref_query($::form, $dbh, $query, @values);
 422
 423   $::lxdebug->leave_sub;
 424
 425   return @{ $contacts };
 426 }
 427
 428
 429 1;