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