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