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