Merge branch 'b-3.6.1' into mebil
[kivitendo-erp.git] / 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., 51 Franklin Street, Fifth Floor, Boston,
29 # MA 02110-1335, USA.
30 #======================================================================
31 #
32 # backend code for customers and vendors
33 #
34 # CHANGE LOG:
35 #   DS. 2000-07-04  Created
36 #
37 #======================================================================
38
39 package CT;
40
41 use SL::Common;
42 use SL::CVar;
43 use SL::DBUtils;
44 use SL::DB;
45 use SL::Util qw(trim);
46 use Text::ParseWords;
47
48 use strict;
49
50 sub search {
51   $main::lxdebug->enter_sub();
52
53   my ( $self, $myconfig, $form ) = @_;
54
55   # connect to database
56   my $dbh = $form->dbconnect($myconfig);
57
58   my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
59   my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
60
61   my $where = "1 = 1";
62   my @values;
63
64   my %allowed_sort_columns = (
65       "id"                 => "ct.id",
66       "customernumber"     => "ct.customernumber",
67       "vendornumber"       => "ct.vendornumber",
68       "name"               => "ct.name",
69       "contact"            => "ct.contact",
70       "phone"              => "ct.phone",
71       "fax"                => "ct.fax",
72       "email"              => "ct.email",
73       "street"             => "ct.street",
74       "taxnumber"          => "ct.taxnumber",
75       "business"           => "b.description",
76       "invnumber"          => "ct.invnumber",
77       "ordnumber"          => "ct.ordnumber",
78       "quonumber"          => "ct.quonumber",
79       "zipcode"            => "ct.zipcode",
80       "city"               => "ct.city",
81       "country"            => "ct.country",
82       "gln"                => "ct.gln",
83       "discount"           => "ct.discount",
84       "insertdate"         => "ct.itime",
85       "salesman"           => "e.name",
86       "payment"            => "pt.description",
87       "pricegroup"         => "pg.pricegroup",
88       "ustid"              => "ct.ustid",
89       "creditlimit"        => "ct.creditlimit",
90       "commercial_court"   => "ct.commercial_court",
91     );
92
93   $form->{sort} ||= "name";
94   my $sortorder;
95   if ( $join_records ) {
96     # in UNION case order by hash key, e.g. salesman
97     # the UNION created an implicit select around the result
98     $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
99   } else {
100     # in not UNION case order by hash value, e.g. e.name
101     $sortorder = $allowed_sort_columns{$form->{sort}} ?  $allowed_sort_columns{$form->{sort}} : "ct.name";
102   }
103   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
104
105   if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) {
106     $sortorder  = "lower($sortorder) ${sortdir}";
107   } else {
108     $sortorder .= " ${sortdir}";
109   }
110
111   if ($form->{"${cv}number"}) {
112     $where .= " AND ct.${cv}number ILIKE ?";
113     push(@values, like($form->{"${cv}number"}));
114   }
115
116   foreach my $key (qw(name contact email)) {
117     if ($form->{$key}) {
118       $where .= " AND ct.$key ILIKE ?";
119       push(@values, like($form->{$key}));
120     }
121   }
122
123   if ($form->{cp_name}) {
124     $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
125     push @values, like($form->{cp_name});
126   }
127
128   if ($form->{addr_street}) {
129     $where .= qq| AND ((ct.street ILIKE ?) | .
130               qq|      OR | .
131               qq|      (ct.id IN ( | .
132               qq|         SELECT sc.trans_id FROM shipto sc | .
133               qq|         WHERE (sc.module = 'CT') | .
134               qq|           AND (sc.shiptostreet ILIKE ?) | .
135               qq|      ))) |;
136     push @values, (like($form->{addr_street})) x 2;
137   }
138
139   if ($form->{addr_zipcode}) {
140     $where .= qq| AND ((ct.zipcode ILIKE ?) | .
141               qq|      OR | .
142               qq|      (ct.id IN ( | .
143               qq|         SELECT sc.trans_id FROM shipto sc | .
144               qq|         WHERE (sc.module = 'CT') | .
145               qq|           AND (sc.shiptozipcode ILIKE ?) | .
146               qq|      ))) |;
147     push @values, (like($form->{addr_zipcode})) x 2;
148   }
149
150   if ($form->{addr_city}) {
151     $where .= " AND ((lower(ct.city) LIKE lower(?))
152                      OR
153                      (ct.id IN (
154                         SELECT sc.trans_id
155                         FROM shipto sc
156                         WHERE (sc.module = 'CT')
157                           AND (lower(sc.shiptocity) LIKE lower(?))
158                       ))
159                      )";
160     push @values, (like($form->{addr_city})) x 2;
161   }
162
163   if ($form->{addr_country}) {
164     $where .= " AND ((lower(ct.country) LIKE lower(?))
165                      OR
166                      (ct.id IN (
167                         SELECT so.trans_id
168                         FROM shipto so
169                         WHERE (so.module = 'CT')
170                           AND (lower(so.shiptocountry) LIKE lower(?))
171                       ))
172                      )";
173     push @values, (like($form->{addr_country})) x 2;
174   }
175
176   if ($form->{addr_gln}) {
177     $where .= " AND ((lower(ct.gln) LIKE lower(?))
178                      OR
179                      (ct.id IN (
180                         SELECT so.trans_id
181                         FROM shipto so
182                         WHERE (so.module = 'CT')
183                           AND (lower(so.shiptogln) LIKE lower(?))
184                       ))
185                      )";
186     push @values, (like($form->{addr_gln})) x 2;
187   }
188
189   if ( $form->{status} eq 'orphaned' ) {
190     $where .=
191       qq| AND ct.id NOT IN | .
192       qq|   (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
193     if ($cv eq 'customer') {
194       $where .=
195         qq| AND ct.id NOT IN | .
196         qq| (SELECT a.customer_id FROM ar a, customer cv | .
197         qq|  WHERE cv.id = a.customer_id)|;
198     }
199     if ($cv eq 'vendor') {
200       $where .=
201         qq| AND ct.id NOT IN | .
202         qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
203         qq|  WHERE cv.id = a.vendor_id)|;
204     }
205     $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
206   }
207
208   if ($form->{obsolete} eq "Y") {
209     $where .= qq| AND ct.obsolete|;
210   } elsif ($form->{obsolete} eq "N") {
211     $where .= qq| AND NOT ct.obsolete|;
212   }
213
214   if ($form->{business_id}) {
215     $where .= qq| AND (ct.business_id = ?)|;
216     push(@values, conv_i($form->{business_id}));
217   }
218
219   if ($form->{salesman_id}) {
220     $where .= qq| AND (ct.salesman_id = ?)|;
221     push(@values, conv_i($form->{salesman_id}));
222   }
223
224   if($form->{insertdatefrom}) {
225     $where .= qq| AND (ct.itime::DATE >= ?)|;
226     push@values, conv_date($form->{insertdatefrom});
227   }
228
229   if($form->{insertdateto}) {
230     $where .= qq| AND (ct.itime::DATE <= ?)|;
231     push @values, conv_date($form->{insertdateto});
232   }
233
234   if ($form->{all}) {
235     my @tokens = parse_line('\s+', 0, $form->{all});
236       $where .= qq| AND (
237           ct.${cv}number ILIKE ? OR
238           ct.name        ILIKE ?
239           )| for @tokens;
240     push @values, ("%$_%")x2 for @tokens;
241   }
242
243   if (($form->{create_zugferd_invoices} // '') ne '') {
244     $where .= qq| AND (ct.create_zugferd_invoices = ?)|;
245     push @values, $form->{create_zugferd_invoices};
246   }
247
248   if ($form->{all_phonenumbers}) {
249     my $search_term = trim($form->{all_phonenumbers});
250     $search_term    =~ s{\p{WSpace}+}{}g;
251     $search_term    = join ' *', split(//, $search_term);
252
253     $where .= qq| AND (ct.phone ~* ? OR
254                        ct.fax   ~* ? OR
255                        ct.id    IN
256                          (SELECT cp_cv_id FROM contacts
257                           WHERE cp_phone1      ~* ? OR
258                                 cp_phone2      ~* ? OR
259                                 cp_fax         ~* ? OR
260                                 cp_mobile1     ~* ? OR
261                                 cp_mobile2     ~* ? OR
262                                 cp_satphone    ~* ? OR
263                                 cp_satfax      ~* ? OR
264                                 cp_privatphone ~* ?
265                          )
266     )|;
267     push @values, ($search_term)x10;
268   }
269
270   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'CT',
271                                                             'trans_id_field' => 'ct.id',
272                                                             'filter'         => $form);
273
274   if ($cvar_where) {
275     $where .= qq| AND ($cvar_where)|;
276     push @values, @cvar_values;
277   }
278
279   my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
280   my $pg_join   = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
281
282   my $main_cp_select = '';
283   if ($form->{l_main_contact_person}) {
284     $main_cp_select =  qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
285                               FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
286                               AS main_contact_person /;
287   }
288   my $query =
289     qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
290     qq|  pt.description as payment | .
291     $pg_select .
292     $main_cp_select .
293     (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) .
294     qq|FROM $cv ct | .
295     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
296     qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
297     qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
298     $pg_join .
299     qq|WHERE $where|;
300
301   my @saved_values = @values;
302   # redo for invoices, orders and quotations
303   if ($join_records) {
304     my $union = "UNION";
305
306     if ($form->{l_invnumber}) {
307       my $ar = $cv eq 'customer' ? 'ar' : 'ap';
308       my $module = $ar eq 'ar' ? 'is' : 'ir';
309       push(@values, @saved_values);
310       $query .=
311         qq| UNION | .
312         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
313         qq|  pt.description as payment | .
314         $pg_select .
315         $main_cp_select .
316         qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
317         qq|  '$module' AS module, 'invoice' AS formtype, | .
318         qq|  (a.amount = a.paid) AS closed | .
319         qq|FROM $cv ct | .
320         qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
321         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
322         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
323         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
324         $pg_join .
325         qq|WHERE $where AND (a.invoice = '1')|;
326     }
327
328     if ( $form->{l_ordnumber} ) {
329       push(@values, @saved_values);
330       $query .=
331         qq| UNION | .
332         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
333         qq|  pt.description as payment | .
334         $pg_select .
335         $main_cp_select .
336         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
337         qq|  'oe' AS module, 'order' AS formtype, o.closed | .
338         qq|FROM $cv ct | .
339         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
340         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
341         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
342         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
343         $pg_join .
344         qq|WHERE $where AND (o.quotation = '0')|;
345     }
346
347     if ( $form->{l_quonumber} ) {
348       push(@values, @saved_values);
349       $query .=
350         qq| UNION | .
351         qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
352         qq|  pt.description as payment | .
353         $pg_select .
354         $main_cp_select .
355         qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
356         qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
357         qq|FROM $cv ct | .
358         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
359         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
360         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
361         qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
362         $pg_join .
363         qq|WHERE $where AND (o.quotation = '1')|;
364     }
365   }
366
367   $query .= qq| ORDER BY $sortorder|;
368
369   $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
370
371   $main::lxdebug->leave_sub();
372 }
373
374 sub get_contact {
375   $main::lxdebug->enter_sub();
376
377   my ( $self, $myconfig, $form ) = @_;
378
379   die 'Missing argument: cp_id' unless $::form->{cp_id};
380
381   my $dbh   = SL::DB->client->dbh;
382   my $query =
383     qq|SELECT * FROM contacts c | .
384     qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
385   my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
386   my $ref = $sth->fetchrow_hashref("NAME_lc");
387
388   map { $form->{$_} = $ref->{$_} } keys %$ref;
389
390   $query = qq|SELECT COUNT(cp_id) AS used FROM (
391     SELECT cp_id FROM oe UNION
392     SELECT cp_id FROM ar UNION
393     SELECT cp_id FROM ap UNION
394     SELECT cp_id FROM delivery_orders
395   ) AS cpid WHERE cp_id = ? OR ? = 0|;
396   ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
397
398   $sth->finish;
399
400   $main::lxdebug->leave_sub();
401 }
402
403 sub get_bank_info {
404   $main::lxdebug->enter_sub();
405
406   my $self     = shift;
407   my %params   = @_;
408
409   Common::check_params(\%params, qw(vc id));
410
411   my $myconfig = \%main::myconfig;
412   my $form     = $main::form;
413
414   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
415
416   my $table        = $params{vc} eq 'customer' ? 'customer' : 'vendor';
417   my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
418   my $placeholders = join ", ", ('?') x scalar @ids;
419   my $c_mandate    = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
420   my $query        = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
421                         FROM ${table}
422                         WHERE id IN (${placeholders})|;
423
424   my $result       = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
425
426   if (ref $params{id} eq 'ARRAY') {
427     $result = { map { $_->{id} => $_ } @{ $result } };
428   } else {
429     $result = $result->[0] || { 'id' => $params{id} };
430   }
431
432   $main::lxdebug->leave_sub();
433
434   return $result;
435 }
436
437 sub search_contacts {
438   $::lxdebug->enter_sub;
439
440   my $self      = shift;
441   my %params    = @_;
442
443   my $dbh       = $params{dbh} || $::form->get_standard_dbh;
444
445   my %sortspecs = (
446     'cp_name'   => 'cp_name, cp_givenname',
447     'vcname'    => 'vcname, cp_name, cp_givenname',
448     'vcnumber'  => 'vcnumber, cp_name, cp_givenname',
449     );
450
451   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);
452
453   my $order_by  = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
454   $::form->{sort} = $order_by;
455   $order_by     = $sortspecs{$order_by} if ($sortspecs{$order_by});
456
457   my $sortdir   = $::form->{sortdir} ? 'ASC' : 'DESC';
458   $order_by     =~ s/,/ ${sortdir},/g;
459   $order_by    .= " $sortdir";
460
461   my @where_tokens = ();
462   my @values;
463
464   if ($params{search_term}) {
465     my @tokens;
466     push @tokens,
467       'cp.cp_name      ILIKE ?',
468       'cp.cp_givenname ILIKE ?',
469       'cp.cp_email     ILIKE ?';
470     push @values, (like($params{search_term})) x 3;
471
472     if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
473       my $number =  $params{search_term};
474       $number    =~ s/[^\d]//g;
475       $number    =  join '[ /\(\)+\-]*', split(m//, $number);
476
477       push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
478     }
479
480     push @where_tokens, map { "($_)" } join ' OR ', @tokens;
481   }
482
483   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'Contacts',
484                                                             'trans_id_field' => 'cp.cp_id',
485                                                             'filter'         => $params{filter});
486
487   if ($cvar_where) {
488     push @where_tokens, $cvar_where;
489     push @values, @cvar_values;
490   }
491
492   if (my $filter = $params{filter}) {
493     for (qw(name title givenname email project abteilung)) {
494       next unless $filter->{"cp_$_"};
495       add_token(\@where_tokens, \@values, col =>  "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
496     }
497
498     push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
499     push @where_tokens, 'cp.cp_cv_id IS NULL'     if $filter->{status} eq 'orphaned';
500   }
501
502   my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
503
504   my $query     = qq|SELECT cp.*,
505                        COALESCE(c.id,             v.id)           AS vcid,
506                        COALESCE(c.name,           v.name)         AS vcname,
507                        COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
508                        CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
509                      FROM contacts cp
510                      LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
511                      LEFT JOIN vendor v   ON (cp.cp_cv_id = v.id)
512                      $where
513                      ORDER BY $order_by|;
514
515   my $contacts  = selectall_hashref_query($::form, $dbh, $query, @values);
516
517   $::lxdebug->leave_sub;
518
519   return @{ $contacts };
520 }
521
522
523 1;