DBConnect Caching: rollback nur bei Handles ohne AutoCommit
[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       "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;