CustomVariables-Helper registrieren von neu erstellen CVars
[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 Data::Dumper;
41
42 use SL::Common;
43 use SL::CVar;
44 use SL::DBUtils;
45 use SL::DB::Default;
46 use SL::FU;
47 use SL::Notes;
48 use SL::TransNumber;
49
50 use strict;
51
52 sub get_tuple {
53   $main::lxdebug->enter_sub();
54
55   my ( $self, $myconfig, $form ) = @_;
56
57   my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
58
59   my $dbh   = $form->dbconnect($myconfig);
60   my $query =
61     qq|SELECT ct.*, b.id AS business, cp.* | .
62     qq|FROM $cv ct | .
63     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
64     qq|LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) | .
65     qq|WHERE (ct.id = ?) | .
66     qq|ORDER BY cp.cp_id LIMIT 1|;
67   my $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
68
69   my $ref = $sth->fetchrow_hashref("NAME_lc");
70
71   map { $form->{$_} = $ref->{$_} } keys %$ref;
72   $sth->finish;
73
74   #get name of currency instead of id:
75   $query = qq|SELECT name AS curr FROM currencies WHERE id=?|;
76   ($form->{curr}) = selectrow_query($form, $dbh, $query, conv_i($form->{currency_id}));
77
78   if ( $form->{salesman_id} ) {
79     my $query =
80       qq|SELECT ct.name AS salesman | .
81       qq|FROM $cv ct | .
82       qq|WHERE ct.id = ?|;
83     ($form->{salesman}) =
84       selectrow_query($form, $dbh, $query, $form->{salesman_id});
85   }
86
87   my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $form->{login});
88   $query =
89     qq|SELECT n.*, n.itime::DATE AS created_on,
90          e.name AS created_by_name, e.login AS created_by_login
91        FROM notes n
92        LEFT JOIN employee e ON (n.created_by = e.id)
93        WHERE (n.trans_id = ?) AND (n.trans_module = 'ct')|;
94   $form->{NOTES} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
95
96   $query =
97     qq|SELECT fu.follow_up_date, fu.done AS follow_up_done, e.name AS created_for_name, e.name AS created_for_login
98        FROM follow_ups fu
99        LEFT JOIN employee e ON (fu.created_for_user = e.id)
100        WHERE (fu.note_id = ?)
101          AND NOT COALESCE(fu.done, FALSE)
102          AND (   (fu.created_by = ?)
103               OR (fu.created_by IN (SELECT DISTINCT what FROM follow_up_access WHERE who = ?)))|;
104   $sth = prepare_query($form, $dbh, $query);
105
106   foreach my $note (@{ $form->{NOTES} }) {
107     do_statement($form, $sth, $query, conv_i($note->{id}), conv_i($note->{created_by}), conv_i($employee_id));
108     $ref = $sth->fetchrow_hashref();
109
110     map { $note->{$_} = $ref->{$_} } keys %{ $ref } if ($ref);
111   }
112
113   $sth->finish();
114
115   if ($form->{edit_note_id}) {
116     $query =
117       qq|SELECT n.id AS NOTE_id, n.subject AS NOTE_subject, n.body AS NOTE_body,
118            fu.id AS FU_id, fu.follow_up_date AS FU_date, fu.done AS FU_done, fu.created_for_user AS FU_created_for_user
119          FROM notes n
120          LEFT JOIN follow_ups fu ON ((n.id = fu.note_id) AND NOT COALESCE(fu.done, FALSE))
121          WHERE n.id = ?|;
122     $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{edit_note_id}));
123
124     if ($ref) {
125       foreach my $key (keys %{ $ref }) {
126         my $new_key       =  $key;
127         $new_key          =~ s/^([^_]+)/\U$1\E/;
128         $form->{$new_key} =  $ref->{$key};
129       }
130     }
131   }
132
133   # check if it is orphaned
134   my $arap      = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
135   my $num_args  = 2;
136   my $makemodel = '';
137   if ($form->{db} eq 'vendor') {
138     $makemodel = qq| UNION SELECT 1 FROM makemodel mm WHERE mm.make = ?|;
139     $num_args++;
140   }
141
142   $query =
143     qq|SELECT a.id | .
144     qq|FROM $arap a | .
145     qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
146     qq|WHERE ct.id = ? | .
147     qq|UNION | .
148     qq|SELECT a.id | .
149     qq|FROM oe a | .
150     qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
151     qq|WHERE ct.id = ?|
152     . $makemodel;
153   my ($dummy) = selectrow_query($form, $dbh, $query, (conv_i($form->{id})) x $num_args);
154
155   $form->{status} = "orphaned" unless ($dummy);
156
157   $dbh->disconnect;
158
159   $main::lxdebug->leave_sub();
160 }
161
162 sub populate_drop_down_boxes {
163   $main::lxdebug->enter_sub();
164
165   my ($self, $myconfig, $form, $provided_dbh) = @_;
166   my $query;
167
168   my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig);
169
170   # get business types
171   $query = qq|SELECT id, description FROM business ORDER BY id|;
172   $form->{all_business} = selectall_hashref_query($form, $dbh, $query);
173
174   # get shipto address
175   $query =
176     qq|SELECT shipto_id, shiptoname, shiptodepartment_1, shiptostreet, shiptocity
177        FROM shipto
178        WHERE (trans_id = ?) AND (module = 'CT')|;
179   $form->{SHIPTO} = selectall_hashref_query($form, $dbh, $query, $form->{id});
180
181   # get contacts
182   $query  = qq|SELECT cp_id, cp_name, cp_givenname FROM contacts WHERE cp_cv_id = ? ORDER BY cp_name|;
183   $form->{CONTACTS} = selectall_hashref_query($form, $dbh, $query, $form->{id});
184
185   # get languages
186   $query = qq|SELECT id, description FROM language ORDER BY id|;
187   $form->{languages} = selectall_hashref_query($form, $dbh, $query);
188
189   # get payment terms
190   $query = qq|SELECT id, description FROM payment_terms ORDER BY sortkey|;
191   $form->{payment_terms} = selectall_hashref_query($form, $dbh, $query);
192
193   $dbh->disconnect() unless ($provided_dbh);
194
195   $main::lxdebug->leave_sub();
196 }
197
198 sub query_titles_and_greetings {
199   $main::lxdebug->enter_sub();
200
201   my ( $self, $myconfig, $form ) = @_;
202   my ( %tmp,  $ref, $query );
203
204   my $dbh = $form->dbconnect($myconfig);
205
206   $query =
207     qq|SELECT DISTINCT(greeting) | .
208     qq|FROM customer | .
209     qq|WHERE greeting ~ '[a-zA-Z]' | .
210     qq|UNION | .
211     qq|SELECT DISTINCT(greeting) | .
212     qq|FROM vendor | .
213     qq|WHERE greeting ~ '[a-zA-Z]' | .
214     qq|ORDER BY greeting|;
215
216   map({ $tmp{$_} = 1; } selectall_array_query($form, $dbh, $query));
217   $form->{COMPANY_GREETINGS} = [ sort(keys(%tmp)) ];
218
219   $query =
220     qq|SELECT DISTINCT(cp_title) | .
221     qq|FROM contacts | .
222     qq|WHERE cp_title ~ '[a-zA-Z]'|;
223   $form->{TITLES} = [ selectall_array_query($form, $dbh, $query) ];
224
225   $query =
226     qq|SELECT DISTINCT(cp_abteilung) | .
227     qq|FROM contacts | .
228     qq|WHERE cp_abteilung ~ '[a-zA-Z]'|;
229   $form->{DEPARTMENT} = [ selectall_array_query($form, $dbh, $query) ];
230
231   $dbh->disconnect();
232   $main::lxdebug->leave_sub();
233 }
234
235 sub save_customer {
236   $main::lxdebug->enter_sub();
237
238   my ( $self, $myconfig, $form ) = @_;
239
240   # set pricegroup to default
241   $form->{klass} = 0 unless ($form->{klass});
242
243   # connect to database
244   my $dbh = $form->get_standard_dbh;
245
246   map( {
247     $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
248     if ( $form->{"selected_cp_${_}"} );
249        } qw(title greeting abteilung) );
250   $form->{"greeting"} = $form->{"selected_company_greeting"}
251   if ( $form->{"selected_company_greeting"} );
252
253   # assign value discount, terms, creditlimit
254   $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
255   $form->{discount} /= 100;
256   $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
257
258   my ( $query, $sth, $f_id );
259
260   if ( $form->{id} ) {
261     $query = qq|SELECT id FROM customer WHERE customernumber = ?|;
262     ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber});
263
264     if (($f_id ne $form->{id}) && ($f_id ne "")) {
265       $main::lxdebug->leave_sub();
266       return 3;
267     }
268
269   } else {
270     my $customernumber      = SL::TransNumber->new(type        => 'customer',
271                                                    dbh         => $dbh,
272                                                    number      => $form->{customernumber},
273                                                    business_id => $form->{business},
274                                                    save        => 1);
275     $form->{customernumber} = $customernumber->create_unique unless $customernumber->is_unique;
276
277     $query = qq|SELECT nextval('id')|;
278     ($form->{id}) = selectrow_query($form, $dbh, $query);
279
280     $query = qq|INSERT INTO customer (id, name, currency_id) VALUES (?, '', (SELECT currency_id FROM defaults))|;
281     do_query($form, $dbh, $query, $form->{id});
282   }
283
284   $query = qq|UPDATE customer SET | .
285     qq|customernumber = ?, | .
286     qq|name = ?, | .
287     qq|greeting = ?, | .
288     qq|department_1 = ?, | .
289     qq|department_2 = ?, | .
290     qq|street = ?, | .
291     qq|zipcode = ?, | .
292     qq|city = ?, | .
293     qq|country = ?, | .
294     qq|homepage = ?, | .
295     qq|contact = ?, | .
296     qq|phone = ?, | .
297     qq|fax = ?, | .
298     qq|email = ?, | .
299     qq|cc = ?, | .
300     qq|bcc = ?, | .
301     qq|notes = ?, | .
302     qq|discount = ?, | .
303     qq|creditlimit = ?, | .
304     qq|terms = ?, | .
305     qq|business_id = ?, | .
306     qq|taxnumber = ?, | .
307     qq|language = ?, | .
308     qq|account_number = ?, | .
309     qq|bank_code = ?, | .
310     qq|bank = ?, | .
311     qq|iban = ?, | .
312     qq|bic = ?, | .
313     qq|obsolete = ?, | .
314     qq|direct_debit = ?, | .
315     qq|ustid = ?, | .
316     qq|username = ?, | .
317     qq|salesman_id = ?, | .
318     qq|language_id = ?, | .
319     qq|payment_id = ?, | .
320     qq|taxzone_id = ?, | .
321     qq|user_password = ?, | .
322     qq|c_vendor_id = ?, | .
323     qq|klass = ?, | .
324     qq|currency_id = (SELECT id FROM currencies WHERE name = ?), | .
325     qq|taxincluded_checked = ? | .
326     qq|WHERE id = ?|;
327   my @values = (
328     $form->{customernumber},
329     $form->{name},
330     $form->{greeting},
331     $form->{department_1},
332     $form->{department_2},
333     $form->{street},
334     $form->{zipcode},
335     $form->{city},
336     $form->{country},
337     $form->{homepage},
338     $form->{contact},
339     $form->{phone},
340     $form->{fax},
341     $form->{email},
342     $form->{cc},
343     $form->{bcc},
344     $form->{notes},
345     $form->{discount},
346     $form->{creditlimit},
347     conv_i($form->{terms}),
348     conv_i($form->{business}),
349     $form->{taxnumber},
350     $form->{language},
351     $form->{account_number},
352     $form->{bank_code},
353     $form->{bank},
354     $form->{iban},
355     $form->{bic},
356     $form->{obsolete} ? 't' : 'f',
357     $form->{direct_debit} ? 't' : 'f',
358     $form->{ustid},
359     $form->{username},
360     conv_i($form->{salesman_id}),
361     conv_i($form->{language_id}),
362     conv_i($form->{payment_id}),
363     conv_i($form->{taxzone_id}, 0),
364     $form->{user_password},
365     $form->{c_vendor_id},
366     conv_i($form->{klass}),
367     $form->{currency},
368     $form->{taxincluded_checked} ne '' ? $form->{taxincluded_checked} : undef,
369     $form->{id}
370     );
371   do_query( $form, $dbh, $query, @values );
372
373   $form->{cp_id} = $self->_save_contact($form, $dbh);
374
375   # add shipto
376   $form->add_shipto( $dbh, $form->{id}, "CT" );
377
378   $self->_save_note('dbh' => $dbh);
379   $self->_delete_selected_notes('dbh' => $dbh);
380
381   CVar->save_custom_variables('dbh'       => $dbh,
382                               'module'    => 'CT',
383                               'trans_id'  => $form->{id},
384                               'variables' => $form,
385                               'always_valid' => 1);
386   if ($form->{cp_id}) {
387     CVar->save_custom_variables('dbh'       => $dbh,
388                                 'module'    => 'Contacts',
389                                 'trans_id'  => $form->{cp_id},
390                                 'variables' => $form,
391                                 'name_prefix'  => 'cp',
392                                 'always_valid' => 1);
393   }
394
395   my $rc = $dbh->commit();
396
397   $main::lxdebug->leave_sub();
398   return $rc;
399 }
400
401 sub save_vendor {
402   $main::lxdebug->enter_sub();
403
404   my ( $self, $myconfig, $form ) = @_;
405
406   $form->{taxzone_id} *= 1;
407   # connect to database
408   my $dbh = $form->get_standard_dbh;
409
410   map( {
411     $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
412     if ( $form->{"selected_cp_${_}"} );
413        } qw(title greeting abteilung) );
414   $form->{"greeting"} = $form->{"selected_company_greeting"}
415   if ( $form->{"selected_company_greeting"} );
416
417   $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
418   $form->{discount} /= 100;
419   $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
420
421   my $query;
422
423   if (!$form->{id}) {
424     $query = qq|SELECT nextval('id')|;
425     ($form->{id}) = selectrow_query($form, $dbh, $query);
426
427     $query = qq|INSERT INTO vendor (id, name, currency_id) VALUES (?, '', (SELECT currency_id FROM defaults))|;
428     do_query($form, $dbh, $query, $form->{id});
429
430     my $vendornumber      = SL::TransNumber->new(type   => 'vendor',
431                                                  dbh    => $dbh,
432                                                  number => $form->{vendornumber},
433                                                  save   => 1);
434     $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique;
435   }
436
437   $query =
438     qq|UPDATE vendor SET | .
439     qq|  vendornumber = ?, | .
440     qq|  name = ?, | .
441     qq|  greeting = ?, | .
442     qq|  department_1 = ?, | .
443     qq|  department_2 = ?, | .
444     qq|  street = ?, | .
445     qq|  zipcode = ?, | .
446     qq|  city = ?, | .
447     qq|  country = ?, | .
448     qq|  homepage = ?, | .
449     qq|  contact = ?, | .
450     qq|  phone = ?, | .
451     qq|  fax = ?, | .
452     qq|  email = ?, | .
453     qq|  cc = ?, | .
454     qq|  bcc = ?, | .
455     qq|  notes = ?, | .
456     qq|  terms = ?, | .
457     qq|  discount = ?, | .
458     qq|  creditlimit = ?, | .
459     qq|  business_id = ?, | .
460     qq|  taxnumber = ?, | .
461     qq|  language = ?, | .
462     qq|  account_number = ?, | .
463     qq|  bank_code = ?, | .
464     qq|  bank = ?, | .
465     qq|  iban = ?, | .
466     qq|  bic = ?, | .
467     qq|  obsolete = ?, | .
468     qq|  direct_debit = ?, | .
469     qq|  ustid = ?, | .
470     qq|  payment_id = ?, | .
471     qq|  taxzone_id = ?, | .
472     qq|  language_id = ?, | .
473     qq|  username = ?, | .
474     qq|  user_password = ?, | .
475     qq|  v_customer_id = ?, | .
476     qq|  currency_id = (SELECT id FROM currencies WHERE name = ?) | .
477     qq|WHERE id = ?|;
478   my @values = (
479     $form->{vendornumber},
480     $form->{name},
481     $form->{greeting},
482     $form->{department_1},
483     $form->{department_2},
484     $form->{street},
485     $form->{zipcode},
486     $form->{city},
487     $form->{country},
488     $form->{homepage},
489     $form->{contact},
490     $form->{phone},
491     $form->{fax},
492     $form->{email},
493     $form->{cc},
494     $form->{bcc},
495     $form->{notes},
496     conv_i($form->{terms}),
497     $form->{discount},
498     $form->{creditlimit},
499     conv_i($form->{business}),
500     $form->{taxnumber},
501     $form->{language},
502     $form->{account_number},
503     $form->{bank_code},
504     $form->{bank},
505     $form->{iban},
506     $form->{bic},
507     $form->{obsolete} ? 't' : 'f',
508     $form->{direct_debit} ? 't' : 'f',
509     $form->{ustid},
510     conv_i($form->{payment_id}),
511     conv_i($form->{taxzone_id}, 0),
512     conv_i( $form->{language_id}),
513     $form->{username},
514     $form->{user_password},
515     $form->{v_customer_id},
516     $form->{currency},
517     $form->{id}
518     );
519   do_query($form, $dbh, $query, @values);
520
521   $form->{cp_id} = $self->_save_contact($form, $dbh);
522
523   # add shipto
524   $form->add_shipto( $dbh, $form->{id}, "CT" );
525
526   $self->_save_note('dbh' => $dbh);
527   $self->_delete_selected_notes('dbh' => $dbh);
528
529   CVar->save_custom_variables('dbh'       => $dbh,
530                               'module'    => 'CT',
531                               'trans_id'  => $form->{id},
532                               'variables' => $form,
533                               'always_valid' => 1);
534   if ($form->{cp_id}) {
535     CVar->save_custom_variables('dbh'       => $dbh,
536                                 'module'    => 'Contacts',
537                                 'trans_id'  => $form->{cp_id},
538                                 'variables' => $form,
539                                 'name_prefix'  => 'cp',
540                                 'always_valid' => 1);
541   }
542
543   my $rc = $dbh->commit();
544
545   $main::lxdebug->leave_sub();
546   return $rc;
547 }
548
549 sub _save_contact {
550   my ($self, $form, $dbh) = @_;
551
552   return undef unless $form->{cp_id} || $form->{cp_name} || $form->{cp_givenname};
553
554   my @columns = qw(cp_title cp_givenname cp_name cp_email cp_phone1 cp_phone2 cp_abteilung cp_fax
555                    cp_mobile1 cp_mobile2 cp_satphone cp_satfax cp_project cp_privatphone cp_privatemail cp_birthday cp_gender
556                    cp_street cp_zipcode cp_city cp_position);
557   my @values  = map(
558     {
559       if ( $_ eq 'cp_gender' ) {
560         $form->{$_} eq 'f' ? 'f' : 'm';
561       } elsif ( $_ eq 'cp_birthday' && $form->{cp_birthday} eq '' ) {
562         undef;
563       } else {
564         $form->{$_};
565       }
566     }
567     @columns
568   );
569
570   my ($query, $cp_id);
571   if ($form->{cp_id}) {
572     $query = qq|UPDATE contacts SET | . join(', ', map { "${_} = ?" } @columns) . qq| WHERE cp_id = ?|;
573     push @values, $form->{cp_id};
574     $cp_id = $form->{cp_id};
575
576   } else {
577     ($cp_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
578
579     $query = qq|INSERT INTO contacts (| . join(', ', @columns, 'cp_cv_id', 'cp_id') . qq|) VALUES (| . join(', ', ('?') x (2 + scalar @columns)) . qq|)|;
580     push @values, $form->{id}, $cp_id;
581   }
582
583   do_query($form, $dbh, $query, @values);
584
585   return $cp_id;
586 }
587
588 sub delete {
589   $main::lxdebug->enter_sub();
590
591   my ( $self, $myconfig, $form ) = @_;
592   # connect to database
593   my $dbh = $form->dbconnect($myconfig);
594
595   # delete vendor
596   my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
597   my $query = qq|DELETE FROM $cv WHERE id = ?|;
598   do_query($form, $dbh, $query, $form->{id});
599
600   $dbh->disconnect;
601
602   $main::lxdebug->leave_sub();
603 }
604
605 sub search {
606   $main::lxdebug->enter_sub();
607
608   my ( $self, $myconfig, $form ) = @_;
609
610   # connect to database
611   my $dbh = $form->dbconnect($myconfig);
612
613   my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
614   my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
615
616   my $where = "1 = 1";
617   my @values;
618
619   my %allowed_sort_columns = (
620       "id"                 => "ct.id",
621       "customernumber"     => "ct.customernumber",
622       "vendornumber"       => "ct.vendornumber",
623       "name"               => "ct.name",
624       "contact"            => "ct.contact",
625       "phone"              => "ct.phone",
626       "fax"                => "ct.fax",
627       "email"              => "ct.email",
628       "street"             => "ct.street",
629       "taxnumber"          => "ct.taxnumber",
630       "business"           => "ct.business",
631       "invnumber"          => "ct.invnumber",
632       "ordnumber"          => "ct.ordnumber",
633       "quonumber"          => "ct.quonumber",
634       "zipcode"            => "ct.zipcode",
635       "city"               => "ct.city",
636       "country"            => "ct.country",
637       "salesman"           => "e.name"
638     );
639
640   $form->{sort} ||= "name";
641   my $sortorder;
642   if ( $join_records ) {
643     # in UNION case order by hash key, e.g. salesman
644     # the UNION created an implicit select around the result
645     $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
646   } else {
647     # in not UNION case order by hash value, e.g. e.name
648     $sortorder = $allowed_sort_columns{$form->{sort}} ?  $allowed_sort_columns{$form->{sort}} : "ct.name";
649   }
650   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
651
652   if ($sortorder !~ /(business|id)/ && !$join_records) {
653     $sortorder  = "lower($sortorder) ${sortdir}";
654   } else {
655     $sortorder .= " ${sortdir}";
656   }
657
658   if ($form->{"${cv}number"}) {
659     $where .= " AND ct.${cv}number ILIKE ?";
660     push(@values, '%' . $form->{"${cv}number"} . '%');
661   }
662
663   foreach my $key (qw(name contact email)) {
664     if ($form->{$key}) {
665       $where .= " AND ct.$key ILIKE ?";
666       push(@values, '%' . $form->{$key} . '%');
667     }
668   }
669
670   if ($form->{cp_name}) {
671     $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
672     push @values, '%' . $form->{cp_name} . '%';
673   }
674
675   if ($form->{addr_city}) {
676     $where .= " AND ((lower(ct.city) LIKE lower(?))
677                      OR
678                      (ct.id IN (
679                         SELECT sc.trans_id
680                         FROM shipto sc
681                         WHERE (sc.module = 'CT')
682                           AND (lower(sc.shiptocity) LIKE lower(?))
683                       ))
684                      )";
685     push @values, ('%' . $form->{addr_city} . '%') x 2;
686   }
687
688   if ($form->{addr_country}) {
689     $where .= " AND ((lower(ct.country) LIKE lower(?))
690                      OR
691                      (ct.id IN (
692                         SELECT so.trans_id
693                         FROM shipto so
694                         WHERE (so.module = 'CT')
695                           AND (lower(so.shiptocountry) LIKE lower(?))
696                       ))
697                      )";
698     push @values, ('%' . $form->{addr_country} . '%') x 2;
699   }
700
701   if ( $form->{status} eq 'orphaned' ) {
702     $where .=
703       qq| AND ct.id NOT IN | .
704       qq|   (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
705     if ($cv eq 'customer') {
706       $where .=
707         qq| AND ct.id NOT IN | .
708         qq| (SELECT a.customer_id FROM ar a, customer cv | .
709         qq|  WHERE cv.id = a.customer_id)|;
710     }
711     if ($cv eq 'vendor') {
712       $where .=
713         qq| AND ct.id NOT IN | .
714         qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
715         qq|  WHERE cv.id = a.vendor_id)|;
716     }
717     $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
718   }
719
720   if ($form->{obsolete} eq "Y") {
721     $where .= qq| AND ct.obsolete|;
722   } elsif ($form->{obsolete} eq "N") {
723     $where .= qq| AND NOT ct.obsolete|;
724   }
725
726   if ($form->{business_id}) {
727     $where .= qq| AND (ct.business_id = ?)|;
728     push(@values, conv_i($form->{business_id}));
729   }
730
731   # Nur Kunden finden, bei denen ich selber der Verkäufer bin
732   # Gilt nicht für Lieferanten
733   if ($cv eq 'customer' &&   !$main::auth->assert('customer_vendor_all_edit', 1)) {
734     $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
735     push(@values, $form->{login});
736   }
737
738   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'CT',
739                                                             'trans_id_field' => 'ct.id',
740                                                             'filter'         => $form);
741
742   if ($cvar_where) {
743     $where .= qq| AND ($cvar_where)|;
744     push @values, @cvar_values;
745   }
746
747   if ($form->{addr_street}) {
748     $where .= qq| AND (ct.street ILIKE ?)|;
749     push @values, '%' . $form->{addr_street} . '%';
750   }
751
752   if ($form->{addr_zipcode}) {
753     $where .= qq| AND (ct.zipcode ILIKE ?)|;
754     push @values, $form->{addr_zipcode} . '%';
755   }
756
757   my $query =
758     qq|SELECT ct.*, b.description AS business, e.name as salesman | .
759     (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) .
760     qq|FROM $cv ct | .
761     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
762     qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
763     qq|WHERE $where|;
764
765   my @saved_values = @values;
766   # redo for invoices, orders and quotations
767   if ($join_records) {
768     my $union = "UNION";
769
770     if ($form->{l_invnumber}) {
771       my $ar = $cv eq 'customer' ? 'ar' : 'ap';
772       my $module = $ar eq 'ar' ? 'is' : 'ir';
773       push(@values, @saved_values);
774       $query .=
775         qq| UNION | .
776         qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
777         qq|  a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
778         qq|  '$module' AS module, 'invoice' AS formtype, | .
779         qq|  (a.amount = a.paid) AS closed | .
780         qq|FROM $cv ct | .
781         qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
782         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
783         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
784         qq|WHERE $where AND (a.invoice = '1')|;
785     }
786
787     if ( $form->{l_ordnumber} ) {
788       push(@values, @saved_values);
789       $query .=
790         qq| UNION | .
791         qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
792         qq|  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
793         qq|  'oe' AS module, 'order' AS formtype, o.closed | .
794         qq|FROM $cv ct | .
795         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
796         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
797         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
798         qq|WHERE $where AND (o.quotation = '0')|;
799     }
800
801     if ( $form->{l_quonumber} ) {
802       push(@values, @saved_values);
803       $query .=
804         qq| UNION | .
805         qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
806         qq|  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
807         qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
808         qq|FROM $cv ct | .
809         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
810         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
811         qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
812         qq|WHERE $where AND (o.quotation = '1')|;
813     }
814   }
815
816   $query .= qq| ORDER BY $sortorder|;
817
818   $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
819
820   $main::lxdebug->leave_sub();
821 }
822
823 sub get_contact {
824   $main::lxdebug->enter_sub();
825
826   my ( $self, $myconfig, $form ) = @_;
827
828   die 'Missing argument: cp_id' unless $::form->{cp_id};
829
830   my $dbh   = $form->dbconnect($myconfig);
831   my $query =
832     qq|SELECT * FROM contacts c | .
833     qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
834   my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
835   my $ref = $sth->fetchrow_hashref("NAME_lc");
836
837   map { $form->{$_} = $ref->{$_} } keys %$ref;
838
839   $query = qq|SELECT COUNT(cp_id) AS used FROM (
840     SELECT cp_id FROM oe UNION
841     SELECT cp_id FROM ar UNION
842     SELECT cp_id FROM ap UNION
843     SELECT cp_id FROM delivery_orders
844   ) AS cpid WHERE cp_id = ? OR ? = 0|;
845   ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
846
847   $sth->finish;
848   $dbh->disconnect;
849
850   $main::lxdebug->leave_sub();
851 }
852
853 sub get_shipto {
854   $main::lxdebug->enter_sub();
855
856   my ( $self, $myconfig, $form ) = @_;
857   my $dbh   = $form->dbconnect($myconfig);
858   my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
859   my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id});
860
861   my $ref = $sth->fetchrow_hashref("NAME_lc");
862
863   map { $form->{$_} = $ref->{$_} } keys %$ref;
864
865   $query = qq|SELECT COUNT(shipto_id) AS used FROM (
866     SELECT shipto_id FROM oe UNION
867     SELECT shipto_id FROM ar UNION
868     SELECT shipto_id FROM delivery_orders
869   ) AS stid WHERE shipto_id = ? OR ? = 0|;
870   ($form->{shiptoused}) = selectfirst_array_query($form, $dbh, $query, ($form->{shipto_id})x2);
871
872   $sth->finish;
873   $dbh->disconnect;
874
875   $main::lxdebug->leave_sub();
876 }
877
878 sub get_delivery {
879   $main::lxdebug->enter_sub();
880
881   my ( $self, $myconfig, $form ) = @_;
882   my $dbh = $form->dbconnect($myconfig);
883
884   my $arap = $form->{db} eq "vendor" ? "ap" : "ar";
885   my $db = $form->{db} eq "customer" ? "customer" : "vendor";
886   my $qty_sign = $form->{db} eq 'vendor' ? ' * -1 AS qty' : '';
887
888   my $where = " WHERE 1=1 ";
889   my @values;
890
891   if ($form->{shipto_id} && ($arap eq "ar")) {
892     $where .= "AND ${arap}.shipto_id = ?";
893     push(@values, $form->{shipto_id});
894   } else {
895     $where .= "AND ${arap}.${db}_id = ?";
896     push(@values, $form->{id});
897   }
898
899   if ($form->{from}) {
900     $where .= "AND ${arap}.transdate >= ?";
901     push(@values, conv_date($form->{from}));
902   }
903   if ($form->{to}) {
904     $where .= "AND ${arap}.transdate <= ?";
905     push(@values, conv_date($form->{to}));
906   }
907   my $query =
908     qq|SELECT s.shiptoname, i.qty $qty_sign, | .
909     qq|  ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | .
910     qq|  i.description, i.unit, i.sellprice, | .
911     qq|  oe.id AS oe_id, invoice | .
912     qq|FROM $arap | .
913     qq|LEFT JOIN shipto s ON | .
914     ($arap eq "ar"
915      ? qq|(ar.shipto_id = s.shipto_id) |
916      : qq|(ap.id = s.trans_id) |) .
917     qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | .
918     qq|LEFT join parts p ON (p.id = i.parts_id) | .
919     qq|LEFT JOIN oe ON (oe.ordnumber = ${arap}.ordnumber AND NOT ${arap}.ordnumber = '') | .
920     $where .
921     qq|ORDER BY ${arap}.transdate DESC LIMIT 15|;
922
923   $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values);
924
925   $dbh->disconnect;
926
927   $main::lxdebug->leave_sub();
928 }
929
930 sub _save_note {
931   $main::lxdebug->enter_sub();
932
933   my $self   = shift;
934   my %params = @_;
935
936   my $form   = $main::form;
937
938   Common::check_params(\%params, 'dbh');
939
940   if (!$form->{NOTE_subject}) {
941     $main::lxdebug->leave_sub();
942     return;
943   }
944
945   my $dbh = $params{dbh};
946
947   my %follow_up;
948   my %note = (
949     'id'           => $form->{NOTE_id},
950     'subject'      => $form->{NOTE_subject},
951     'body'         => $form->{NOTE_body},
952     'trans_id'     => $form->{id},
953     'trans_module' => 'ct',
954   );
955
956   $note{id} = Notes->save(%note);
957
958   if ($form->{FU_date}) {
959     %follow_up = (
960       'id'               => $form->{FU_id},
961       'note_id'          => $note{id},
962       'follow_up_date'   => $form->{FU_date},
963       'created_for_user' => $form->{FU_created_for_user},
964       'done'             => $form->{FU_done} ? 1 : 0,
965       'subject'          => $form->{NOTE_subject},
966       'body'             => $form->{NOTE_body},
967       'LINKS'            => [
968         {
969           'trans_id'     => $form->{id},
970           'trans_type'   => $form->{db} eq 'customer' ? 'customer' : 'vendor',
971           'trans_info'   => $form->{name},
972         },
973       ],
974     );
975
976     $follow_up{id} = FU->save(%follow_up);
977
978   } elsif ($form->{FU_id}) {
979     do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($form->{FU_id}));
980     do_query($form, $dbh, qq|DELETE FROM follow_ups      WHERE id = ?|,           conv_i($form->{FU_id}));
981   }
982
983   delete @{$form}{grep { /^NOTE_|^FU_/ } keys %{ $form }};
984
985   $main::lxdebug->leave_sub();
986 }
987
988 sub _delete_selected_notes {
989   $main::lxdebug->enter_sub();
990
991   my $self   = shift;
992   my %params = @_;
993
994   Common::check_params(\%params, 'dbh');
995
996   my $form = $main::form;
997   my $dbh  = $params{dbh};
998
999   foreach my $i (1 .. $form->{NOTES_rowcount}) {
1000     next unless ($form->{"NOTE_delete_$i"} && $form->{"NOTE_id_$i"});
1001
1002     Notes->delete('dbh' => $params{dbh},
1003                   'id'  => $form->{"NOTE_id_$i"});
1004   }
1005
1006   $main::lxdebug->leave_sub();
1007 }
1008
1009 # TODO: remove in 2.7.0 stable
1010 sub delete_shipto {
1011   $main::lxdebug->enter_sub();
1012
1013   my $self      = shift;
1014   my $shipto_id = shift;
1015
1016   my $form      = $main::form;
1017   my %myconfig  = %main::myconfig;
1018   my $dbh       = $form->get_standard_dbh(\%myconfig);
1019
1020   do_query($form, $dbh, qq|UPDATE shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id);
1021
1022   $dbh->commit();
1023
1024   $main::lxdebug->leave_sub();
1025 }
1026
1027 # TODO: remove in 2.7.0 stable
1028 sub delete_contact {
1029   $main::lxdebug->enter_sub();
1030
1031   my $self      = shift;
1032   my $cp_id     = shift;
1033
1034   my $form      = $main::form;
1035   my %myconfig  = %main::myconfig;
1036   my $dbh       = $form->get_standard_dbh(\%myconfig);
1037
1038   do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $cp_id);
1039
1040   $dbh->commit();
1041
1042   $main::lxdebug->leave_sub();
1043 }
1044
1045 sub get_bank_info {
1046   $main::lxdebug->enter_sub();
1047
1048   my $self     = shift;
1049   my %params   = @_;
1050
1051   Common::check_params(\%params, qw(vc id));
1052
1053   my $myconfig = \%main::myconfig;
1054   my $form     = $main::form;
1055
1056   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
1057
1058   my $table        = $params{vc} eq 'customer' ? 'customer' : 'vendor';
1059   my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
1060   my $placeholders = join ", ", ('?') x scalar @ids;
1061   my $query        = qq|SELECT id, name, account_number, bank, bank_code, iban, bic
1062                         FROM ${table}
1063                         WHERE id IN (${placeholders})|;
1064
1065   my $result       = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
1066
1067   if (ref $params{id} eq 'ARRAY') {
1068     $result = { map { $_->{id} => $_ } @{ $result } };
1069   } else {
1070     $result = $result->[0] || { 'id' => $params{id} };
1071   }
1072
1073   $main::lxdebug->leave_sub();
1074
1075   return $result;
1076 }
1077
1078 sub parse_excel_file {
1079   $main::lxdebug->enter_sub();
1080
1081   my ($self, $myconfig, $form) = @_;
1082   my $locale = $main::locale;
1083
1084   my $defaults = SL::DB::Default->get;
1085   $form->error($::locale->text('No print templates have been created for this client yet. Please do so in the client configuration.')) if !$defaults->templates;
1086   $form->{templates} = $defaults->templates;
1087
1088   $form->{formname}   = 'sales_quotation';
1089   $form->{type}   = 'sales_quotation';
1090   $form->{format} = 'excel';
1091   $form->{media}  = 'screen';
1092   $form->{quonumber} = 1;
1093
1094
1095   # $form->{"notes"} will be overridden by the customer's/vendor's "notes" field. So save it here.
1096   $form->{ $form->{"formname"} . "notes" } = $form->{"notes"};
1097
1098   my $inv                  = "quo";
1099   my $due                  = "req";
1100   $form->{"${inv}date"} = $form->{transdate};
1101   $form->{label}        = $locale->text('Quotation');
1102   my $numberfld            = "sqnumber";
1103   my $order                = 1;
1104
1105   # assign number
1106   $form->{what_done} = $form->{formname};
1107
1108   map({ delete($form->{$_}); } grep(/^cp_/, keys(%{ $form })));
1109
1110   my $output_dateformat = $myconfig->{"dateformat"};
1111   my $output_numberformat = $myconfig->{"numberformat"};
1112   my $output_longdates = 1;
1113
1114   # map login user variables
1115   map { $form->{"login_$_"} = $myconfig->{$_} } ("name", "email", "fax", "tel", "company");
1116
1117   # format item dates
1118   for my $field (qw(transdate_oe deliverydate_oe)) {
1119     map {
1120       $form->{$field}[$_] = $locale->date($myconfig, $form->{$field}[$_], 1);
1121     } 0 .. $#{ $form->{$field} };
1122   }
1123
1124   if ($form->{shipto_id}) {
1125     $form->get_shipto($myconfig);
1126   }
1127
1128   $form->{notes} =~ s/^\s+//g;
1129
1130   delete $form->{printer_command};
1131
1132   $form->get_employee_info($myconfig);
1133
1134   my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_');
1135
1136   if (scalar @{ $cvar_date_fields }) {
1137     format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields });
1138   }
1139
1140   while (my ($precision, $field_list) = each %{ $cvar_number_fields }) {
1141     reformat_numbers($output_numberformat, $precision, @{ $field_list });
1142   }
1143
1144   $form->{excel} = 1;
1145   my $extension            = 'xls';
1146
1147   $form->{IN}         = "$form->{formname}.${extension}";
1148
1149   delete $form->{OUT};
1150
1151   $form->parse_template($myconfig);
1152
1153   $main::lxdebug->leave_sub();
1154 }
1155
1156 sub search_contacts {
1157   $::lxdebug->enter_sub;
1158
1159   my $self      = shift;
1160   my %params    = @_;
1161
1162   my $dbh       = $params{dbh} || $::form->get_standard_dbh;
1163
1164   my %sortspecs = (
1165     'cp_name'   => 'cp_name, cp_givenname',
1166     'vcname'    => 'vcname, cp_name, cp_givenname',
1167     'vcnumber'  => 'vcnumber, cp_name, cp_givenname',
1168     );
1169
1170   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);
1171
1172   my $order_by  = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
1173   $::form->{sort} = $order_by;
1174   $order_by     = $sortspecs{$order_by} if ($sortspecs{$order_by});
1175
1176   my $sortdir   = $::form->{sortdir} ? 'ASC' : 'DESC';
1177   $order_by     =~ s/,/ ${sortdir},/g;
1178   $order_by    .= " $sortdir";
1179
1180   my @where_tokens = ();
1181   my @values;
1182
1183   if ($params{search_term}) {
1184     my @tokens;
1185     push @tokens,
1186       'cp.cp_name      ILIKE ?',
1187       'cp.cp_givenname ILIKE ?',
1188       'cp.cp_email     ILIKE ?';
1189     push @values, ('%' . $params{search_term} . '%') x 3;
1190
1191     if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
1192       my $number =  $params{search_term};
1193       $number    =~ s/[^\d]//g;
1194       $number    =  join '[ /\(\)+\-]*', split(m//, $number);
1195
1196       push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
1197     }
1198
1199     push @where_tokens, map { "($_)" } join ' OR ', @tokens;
1200   }
1201
1202   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'Contacts',
1203                                                             'trans_id_field' => 'cp.cp_id',
1204                                                             'filter'         => $params{filter});
1205
1206   if ($cvar_where) {
1207     push @where_tokens, $cvar_where;
1208     push @values, @cvar_values;
1209   }
1210
1211   if (my $filter = $params{filter}) {
1212     for (qw(name title givenname email project abteilung)) {
1213       next unless $filter->{"cp_$_"};
1214       add_token(\@where_tokens, \@values, col =>  "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
1215     }
1216
1217     push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
1218     push @where_tokens, 'cp.cp_cv_id IS NULL'     if $filter->{status} eq 'orphaned';
1219   }
1220
1221   my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
1222
1223   my $query     = qq|SELECT cp.*,
1224                        COALESCE(c.id,             v.id)           AS vcid,
1225                        COALESCE(c.name,           v.name)         AS vcname,
1226                        COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
1227                        CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
1228                      FROM contacts cp
1229                      LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
1230                      LEFT JOIN vendor v   ON (cp.cp_cv_id = v.id)
1231                      $where
1232                      ORDER BY $order_by|;
1233
1234   my $contacts  = selectall_hashref_query($::form, $dbh, $query, @values);
1235
1236   $::lxdebug->leave_sub;
1237
1238   return @{ $contacts };
1239 }
1240
1241
1242 1;