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