1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
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.
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 #======================================================================
31 # backend code for customers and vendors
34 # DS. 2000-07-04 Created
36 #======================================================================
53 $main::lxdebug->enter_sub();
55 my ( $self, $myconfig, $form ) = @_;
57 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
59 my $dbh = $form->dbconnect($myconfig);
61 qq|SELECT ct.*, b.id AS business, cp.* | .
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});
69 my $ref = $sth->fetchrow_hashref("NAME_lc");
71 map { $form->{$_} = $ref->{$_} } keys %$ref;
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}));
78 if ( $form->{salesman_id} ) {
80 qq|SELECT ct.name AS salesman | .
84 selectrow_query($form, $dbh, $query, $form->{salesman_id});
87 my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $form->{login});
89 qq|SELECT n.*, n.itime::DATE AS created_on,
90 e.name AS created_by_name, e.login AS created_by_login
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}));
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
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);
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();
110 map { $note->{$_} = $ref->{$_} } keys %{ $ref } if ($ref);
115 if ($form->{edit_note_id}) {
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
120 LEFT JOIN follow_ups fu ON ((n.id = fu.note_id) AND NOT COALESCE(fu.done, FALSE))
122 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{edit_note_id}));
125 foreach my $key (keys %{ $ref }) {
127 $new_key =~ s/^([^_]+)/\U$1\E/;
128 $form->{$new_key} = $ref->{$key};
133 # check if it is orphaned
134 my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
137 if ($form->{db} eq 'vendor') {
138 $makemodel = qq| UNION SELECT 1 FROM makemodel mm WHERE mm.make = ?|;
145 qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
146 qq|WHERE ct.id = ? | .
150 qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
153 my ($dummy) = selectrow_query($form, $dbh, $query, (conv_i($form->{id})) x $num_args);
155 $form->{status} = "orphaned" unless ($dummy);
159 $main::lxdebug->leave_sub();
162 sub populate_drop_down_boxes {
163 $main::lxdebug->enter_sub();
165 my ($self, $myconfig, $form, $provided_dbh) = @_;
168 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig);
171 $query = qq|SELECT id, description FROM business ORDER BY id|;
172 $form->{all_business} = selectall_hashref_query($form, $dbh, $query);
176 qq|SELECT shipto_id, shiptoname, shiptodepartment_1, shiptostreet, shiptocity
178 WHERE (trans_id = ?) AND (module = 'CT')|;
179 $form->{SHIPTO} = selectall_hashref_query($form, $dbh, $query, $form->{id});
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});
186 $query = qq|SELECT id, description FROM language ORDER BY id|;
187 $form->{languages} = selectall_hashref_query($form, $dbh, $query);
190 $query = qq|SELECT id, description FROM payment_terms ORDER BY sortkey|;
191 $form->{payment_terms} = selectall_hashref_query($form, $dbh, $query);
193 $dbh->disconnect() unless ($provided_dbh);
195 $main::lxdebug->leave_sub();
198 sub query_titles_and_greetings {
199 $main::lxdebug->enter_sub();
201 my ( $self, $myconfig, $form ) = @_;
202 my ( %tmp, $ref, $query );
204 my $dbh = $form->dbconnect($myconfig);
207 qq|SELECT DISTINCT(greeting) | .
209 qq|WHERE greeting ~ '[a-zA-Z]' | .
211 qq|SELECT DISTINCT(greeting) | .
213 qq|WHERE greeting ~ '[a-zA-Z]' | .
214 qq|ORDER BY greeting|;
216 map({ $tmp{$_} = 1; } selectall_array_query($form, $dbh, $query));
217 $form->{COMPANY_GREETINGS} = [ sort(keys(%tmp)) ];
220 qq|SELECT DISTINCT(cp_title) | .
222 qq|WHERE cp_title ~ '[a-zA-Z]'|;
223 $form->{TITLES} = [ selectall_array_query($form, $dbh, $query) ];
226 qq|SELECT DISTINCT(cp_abteilung) | .
228 qq|WHERE cp_abteilung ~ '[a-zA-Z]'|;
229 $form->{DEPARTMENT} = [ selectall_array_query($form, $dbh, $query) ];
232 $main::lxdebug->leave_sub();
236 $main::lxdebug->enter_sub();
238 my ( $self, $myconfig, $form ) = @_;
240 # set pricegroup to default
241 $form->{klass} = 0 unless ($form->{klass});
243 # connect to database
244 my $dbh = $form->get_standard_dbh;
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"} );
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} );
258 my ( $query, $sth, $f_id );
261 $query = qq|SELECT id FROM customer WHERE customernumber = ?|;
262 ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber});
264 if (($f_id ne $form->{id}) && ($f_id ne "")) {
265 $main::lxdebug->leave_sub();
270 my $customernumber = SL::TransNumber->new(type => 'customer',
272 number => $form->{customernumber},
273 business_id => $form->{business},
275 $form->{customernumber} = $customernumber->create_unique unless $customernumber->is_unique;
277 $query = qq|SELECT nextval('id')|;
278 ($form->{id}) = selectrow_query($form, $dbh, $query);
280 $query = qq|INSERT INTO customer (id, name, currency_id) VALUES (?, '', (SELECT currency_id FROM defaults))|;
281 do_query($form, $dbh, $query, $form->{id});
284 $query = qq|UPDATE customer SET | .
285 qq|customernumber = ?, | .
288 qq|department_1 = ?, | .
289 qq|department_2 = ?, | .
303 qq|creditlimit = ?, | .
305 qq|business_id = ?, | .
306 qq|taxnumber = ?, | .
308 qq|account_number = ?, | .
309 qq|bank_code = ?, | .
314 qq|direct_debit = ?, | .
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 = ?, | .
324 qq|currency_id = (SELECT id FROM currencies WHERE name = ?), | .
325 qq|taxincluded_checked = ?, | .
326 qq|delivery_term_id = ? | .
329 $form->{customernumber},
332 $form->{department_1},
333 $form->{department_2},
347 $form->{creditlimit},
348 conv_i($form->{terms}),
349 conv_i($form->{business}),
352 $form->{account_number},
357 $form->{obsolete} ? 't' : 'f',
358 $form->{direct_debit} ? 't' : 'f',
361 conv_i($form->{salesman_id}),
362 conv_i($form->{language_id}),
363 conv_i($form->{payment_id}),
364 conv_i($form->{taxzone_id}, 0),
365 $form->{user_password},
366 $form->{c_vendor_id},
367 conv_i($form->{klass}),
369 $form->{taxincluded_checked} ne '' ? $form->{taxincluded_checked} : undef,
370 conv_i($form->{delivery_term_id}),
373 do_query( $form, $dbh, $query, @values );
375 $form->{cp_id} = $self->_save_contact($form, $dbh);
378 $form->add_shipto( $dbh, $form->{id}, "CT" );
380 $self->_save_note('dbh' => $dbh);
381 $self->_delete_selected_notes('dbh' => $dbh);
383 CVar->save_custom_variables('dbh' => $dbh,
385 'trans_id' => $form->{id},
386 'variables' => $form,
387 'always_valid' => 1);
388 if ($form->{cp_id}) {
389 CVar->save_custom_variables('dbh' => $dbh,
390 'module' => 'Contacts',
391 'trans_id' => $form->{cp_id},
392 'variables' => $form,
393 'name_prefix' => 'cp',
394 'always_valid' => 1);
397 my $rc = $dbh->commit();
399 $main::lxdebug->leave_sub();
404 $main::lxdebug->enter_sub();
406 my ( $self, $myconfig, $form ) = @_;
408 $form->{taxzone_id} *= 1;
409 # connect to database
410 my $dbh = $form->get_standard_dbh;
413 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
414 if ( $form->{"selected_cp_${_}"} );
415 } qw(title greeting abteilung) );
416 $form->{"greeting"} = $form->{"selected_company_greeting"}
417 if ( $form->{"selected_company_greeting"} );
419 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
420 $form->{discount} /= 100;
421 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
426 $query = qq|SELECT nextval('id')|;
427 ($form->{id}) = selectrow_query($form, $dbh, $query);
429 $query = qq|INSERT INTO vendor (id, name, currency_id) VALUES (?, '', (SELECT currency_id FROM defaults))|;
430 do_query($form, $dbh, $query, $form->{id});
432 my $vendornumber = SL::TransNumber->new(type => 'vendor',
434 number => $form->{vendornumber},
436 $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique;
440 qq|UPDATE vendor SET | .
441 qq| vendornumber = ?, | .
443 qq| greeting = ?, | .
444 qq| department_1 = ?, | .
445 qq| department_2 = ?, | .
450 qq| homepage = ?, | .
459 qq| discount = ?, | .
460 qq| creditlimit = ?, | .
461 qq| business_id = ?, | .
462 qq| taxnumber = ?, | .
463 qq| language = ?, | .
464 qq| account_number = ?, | .
465 qq| bank_code = ?, | .
469 qq| obsolete = ?, | .
470 qq| direct_debit = ?, | .
472 qq| payment_id = ?, | .
473 qq| taxzone_id = ?, | .
474 qq| language_id = ?, | .
475 qq| username = ?, | .
476 qq| user_password = ?, | .
477 qq| v_customer_id = ?, | .
478 qq| currency_id = (SELECT id FROM currencies WHERE name = ?), | .
479 qq| delivery_term_id = ? | .
482 $form->{vendornumber},
485 $form->{department_1},
486 $form->{department_2},
499 conv_i($form->{terms}),
501 $form->{creditlimit},
502 conv_i($form->{business}),
505 $form->{account_number},
510 $form->{obsolete} ? 't' : 'f',
511 $form->{direct_debit} ? 't' : 'f',
513 conv_i($form->{payment_id}),
514 conv_i($form->{taxzone_id}, 0),
515 conv_i( $form->{language_id}),
517 $form->{user_password},
518 $form->{v_customer_id},
520 conv_i($form->{delivery_term_id}),
523 do_query($form, $dbh, $query, @values);
525 $form->{cp_id} = $self->_save_contact($form, $dbh);
528 $form->add_shipto( $dbh, $form->{id}, "CT" );
530 $self->_save_note('dbh' => $dbh);
531 $self->_delete_selected_notes('dbh' => $dbh);
533 CVar->save_custom_variables('dbh' => $dbh,
535 'trans_id' => $form->{id},
536 'variables' => $form,
537 'always_valid' => 1);
538 if ($form->{cp_id}) {
539 CVar->save_custom_variables('dbh' => $dbh,
540 'module' => 'Contacts',
541 'trans_id' => $form->{cp_id},
542 'variables' => $form,
543 'name_prefix' => 'cp',
544 'always_valid' => 1);
547 my $rc = $dbh->commit();
549 $main::lxdebug->leave_sub();
554 my ($self, $form, $dbh) = @_;
556 return undef unless $form->{cp_id} || $form->{cp_name} || $form->{cp_givenname};
558 my @columns = qw(cp_title cp_givenname cp_name cp_email cp_phone1 cp_phone2 cp_abteilung cp_fax
559 cp_mobile1 cp_mobile2 cp_satphone cp_satfax cp_project cp_privatphone cp_privatemail cp_birthday cp_gender
560 cp_street cp_zipcode cp_city cp_position);
563 if ( $_ eq 'cp_gender' ) {
564 $form->{$_} eq 'f' ? 'f' : 'm';
565 } elsif ( $_ eq 'cp_birthday' && $form->{cp_birthday} eq '' ) {
575 if ($form->{cp_id}) {
576 $query = qq|UPDATE contacts SET | . join(', ', map { "${_} = ?" } @columns) . qq| WHERE cp_id = ?|;
577 push @values, $form->{cp_id};
578 $cp_id = $form->{cp_id};
581 ($cp_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
583 $query = qq|INSERT INTO contacts (| . join(', ', @columns, 'cp_cv_id', 'cp_id') . qq|) VALUES (| . join(', ', ('?') x (2 + scalar @columns)) . qq|)|;
584 push @values, $form->{id}, $cp_id;
587 do_query($form, $dbh, $query, @values);
593 $main::lxdebug->enter_sub();
595 my ( $self, $myconfig, $form ) = @_;
596 # connect to database
597 my $dbh = $form->dbconnect($myconfig);
600 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
601 my $query = qq|DELETE FROM $cv WHERE id = ?|;
602 do_query($form, $dbh, $query, $form->{id});
606 $main::lxdebug->leave_sub();
610 $main::lxdebug->enter_sub();
612 my ( $self, $myconfig, $form ) = @_;
614 # connect to database
615 my $dbh = $form->dbconnect($myconfig);
617 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
618 my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
623 my %allowed_sort_columns = (
625 "customernumber" => "ct.customernumber",
626 "vendornumber" => "ct.vendornumber",
628 "contact" => "ct.contact",
629 "phone" => "ct.phone",
631 "email" => "ct.email",
632 "street" => "ct.street",
633 "taxnumber" => "ct.taxnumber",
634 "business" => "ct.business",
635 "invnumber" => "ct.invnumber",
636 "ordnumber" => "ct.ordnumber",
637 "quonumber" => "ct.quonumber",
638 "zipcode" => "ct.zipcode",
640 "country" => "ct.country",
641 "discount" => "ct.discount",
642 "salesman" => "e.name"
645 $form->{sort} ||= "name";
647 if ( $join_records ) {
648 # in UNION case order by hash key, e.g. salesman
649 # the UNION created an implicit select around the result
650 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
652 # in not UNION case order by hash value, e.g. e.name
653 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
655 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
657 if ($sortorder !~ /(business|id|discount)/ && !$join_records) {
658 $sortorder = "lower($sortorder) ${sortdir}";
660 $sortorder .= " ${sortdir}";
663 if ($form->{"${cv}number"}) {
664 $where .= " AND ct.${cv}number ILIKE ?";
665 push(@values, '%' . $form->{"${cv}number"} . '%');
668 foreach my $key (qw(name contact email)) {
670 $where .= " AND ct.$key ILIKE ?";
671 push(@values, '%' . $form->{$key} . '%');
675 if ($form->{cp_name}) {
676 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
677 push @values, '%' . $form->{cp_name} . '%';
680 if ($form->{addr_city}) {
681 $where .= " AND ((lower(ct.city) LIKE lower(?))
686 WHERE (sc.module = 'CT')
687 AND (lower(sc.shiptocity) LIKE lower(?))
690 push @values, ('%' . $form->{addr_city} . '%') x 2;
693 if ($form->{addr_country}) {
694 $where .= " AND ((lower(ct.country) LIKE lower(?))
699 WHERE (so.module = 'CT')
700 AND (lower(so.shiptocountry) LIKE lower(?))
703 push @values, ('%' . $form->{addr_country} . '%') x 2;
706 if ( $form->{status} eq 'orphaned' ) {
708 qq| AND ct.id NOT IN | .
709 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
710 if ($cv eq 'customer') {
712 qq| AND ct.id NOT IN | .
713 qq| (SELECT a.customer_id FROM ar a, customer cv | .
714 qq| WHERE cv.id = a.customer_id)|;
716 if ($cv eq 'vendor') {
718 qq| AND ct.id NOT IN | .
719 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
720 qq| WHERE cv.id = a.vendor_id)|;
722 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
725 if ($form->{obsolete} eq "Y") {
726 $where .= qq| AND ct.obsolete|;
727 } elsif ($form->{obsolete} eq "N") {
728 $where .= qq| AND NOT ct.obsolete|;
731 if ($form->{business_id}) {
732 $where .= qq| AND (ct.business_id = ?)|;
733 push(@values, conv_i($form->{business_id}));
736 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
737 # Gilt nicht für Lieferanten
738 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
739 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
740 push(@values, $form->{login});
743 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
744 'trans_id_field' => 'ct.id',
748 $where .= qq| AND ($cvar_where)|;
749 push @values, @cvar_values;
752 if ($form->{addr_street}) {
753 $where .= qq| AND (ct.street ILIKE ?)|;
754 push @values, '%' . $form->{addr_street} . '%';
757 if ($form->{addr_zipcode}) {
758 $where .= qq| AND (ct.zipcode ILIKE ?)|;
759 push @values, $form->{addr_zipcode} . '%';
763 qq|SELECT ct.*, b.description AS business, e.name as salesman | .
764 (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) .
766 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
767 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
770 my @saved_values = @values;
771 # redo for invoices, orders and quotations
775 if ($form->{l_invnumber}) {
776 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
777 my $module = $ar eq 'ar' ? 'is' : 'ir';
778 push(@values, @saved_values);
781 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
782 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
783 qq| '$module' AS module, 'invoice' AS formtype, | .
784 qq| (a.amount = a.paid) AS closed | .
786 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
787 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
788 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
789 qq|WHERE $where AND (a.invoice = '1')|;
792 if ( $form->{l_ordnumber} ) {
793 push(@values, @saved_values);
796 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
797 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
798 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
800 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
801 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
802 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
803 qq|WHERE $where AND (o.quotation = '0')|;
806 if ( $form->{l_quonumber} ) {
807 push(@values, @saved_values);
810 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
811 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
812 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
814 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
815 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
816 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
817 qq|WHERE $where AND (o.quotation = '1')|;
821 $query .= qq| ORDER BY $sortorder|;
823 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
825 $main::lxdebug->leave_sub();
829 $main::lxdebug->enter_sub();
831 my ( $self, $myconfig, $form ) = @_;
833 die 'Missing argument: cp_id' unless $::form->{cp_id};
835 my $dbh = $form->dbconnect($myconfig);
837 qq|SELECT * FROM contacts c | .
838 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
839 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
840 my $ref = $sth->fetchrow_hashref("NAME_lc");
842 map { $form->{$_} = $ref->{$_} } keys %$ref;
844 $query = qq|SELECT COUNT(cp_id) AS used FROM (
845 SELECT cp_id FROM oe UNION
846 SELECT cp_id FROM ar UNION
847 SELECT cp_id FROM ap UNION
848 SELECT cp_id FROM delivery_orders
849 ) AS cpid WHERE cp_id = ? OR ? = 0|;
850 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
855 $main::lxdebug->leave_sub();
859 $main::lxdebug->enter_sub();
861 my ( $self, $myconfig, $form ) = @_;
862 my $dbh = $form->dbconnect($myconfig);
863 my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
864 my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id});
866 my $ref = $sth->fetchrow_hashref("NAME_lc");
868 map { $form->{$_} = $ref->{$_} } keys %$ref;
870 $query = qq|SELECT COUNT(shipto_id) AS used FROM (
871 SELECT shipto_id FROM oe UNION
872 SELECT shipto_id FROM ar UNION
873 SELECT shipto_id FROM delivery_orders
874 ) AS stid WHERE shipto_id = ? OR ? = 0|;
875 ($form->{shiptoused}) = selectfirst_array_query($form, $dbh, $query, ($form->{shipto_id})x2);
880 $main::lxdebug->leave_sub();
884 $main::lxdebug->enter_sub();
886 my ( $self, $myconfig, $form ) = @_;
887 my $dbh = $form->dbconnect($myconfig);
889 my $arap = $form->{db} eq "vendor" ? "ap" : "ar";
890 my $db = $form->{db} eq "customer" ? "customer" : "vendor";
891 my $qty_sign = $form->{db} eq 'vendor' ? ' * -1 AS qty' : '';
893 my $where = " WHERE 1=1 ";
896 if ($form->{shipto_id} && ($arap eq "ar")) {
897 $where .= "AND ${arap}.shipto_id = ?";
898 push(@values, $form->{shipto_id});
900 $where .= "AND ${arap}.${db}_id = ?";
901 push(@values, $form->{id});
905 $where .= "AND ${arap}.transdate >= ?";
906 push(@values, conv_date($form->{from}));
909 $where .= "AND ${arap}.transdate <= ?";
910 push(@values, conv_date($form->{to}));
913 qq|SELECT s.shiptoname, i.qty $qty_sign, | .
914 qq| ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | .
915 qq| i.description, i.unit, i.sellprice, | .
916 qq| oe.id AS oe_id, invoice | .
918 qq|LEFT JOIN shipto s ON | .
920 ? qq|(ar.shipto_id = s.shipto_id) |
921 : qq|(ap.id = s.trans_id) |) .
922 qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | .
923 qq|LEFT join parts p ON (p.id = i.parts_id) | .
924 qq|LEFT JOIN oe ON (oe.ordnumber = ${arap}.ordnumber AND NOT ${arap}.ordnumber = '') | .
926 qq|ORDER BY ${arap}.transdate DESC LIMIT 15|;
928 $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values);
932 $main::lxdebug->leave_sub();
936 $main::lxdebug->enter_sub();
941 my $form = $main::form;
943 Common::check_params(\%params, 'dbh');
945 if (!$form->{NOTE_subject}) {
946 $main::lxdebug->leave_sub();
950 my $dbh = $params{dbh};
954 'id' => $form->{NOTE_id},
955 'subject' => $form->{NOTE_subject},
956 'body' => $form->{NOTE_body},
957 'trans_id' => $form->{id},
958 'trans_module' => 'ct',
961 $note{id} = Notes->save(%note);
963 if ($form->{FU_date}) {
965 'id' => $form->{FU_id},
966 'note_id' => $note{id},
967 'follow_up_date' => $form->{FU_date},
968 'created_for_user' => $form->{FU_created_for_user},
969 'done' => $form->{FU_done} ? 1 : 0,
970 'subject' => $form->{NOTE_subject},
971 'body' => $form->{NOTE_body},
974 'trans_id' => $form->{id},
975 'trans_type' => $form->{db} eq 'customer' ? 'customer' : 'vendor',
976 'trans_info' => $form->{name},
981 $follow_up{id} = FU->save(%follow_up);
983 } elsif ($form->{FU_id}) {
984 do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($form->{FU_id}));
985 do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, conv_i($form->{FU_id}));
988 delete @{$form}{grep { /^NOTE_|^FU_/ } keys %{ $form }};
990 $main::lxdebug->leave_sub();
993 sub _delete_selected_notes {
994 $main::lxdebug->enter_sub();
999 Common::check_params(\%params, 'dbh');
1001 my $form = $main::form;
1002 my $dbh = $params{dbh};
1004 foreach my $i (1 .. $form->{NOTES_rowcount}) {
1005 next unless ($form->{"NOTE_delete_$i"} && $form->{"NOTE_id_$i"});
1007 Notes->delete('dbh' => $params{dbh},
1008 'id' => $form->{"NOTE_id_$i"});
1011 $main::lxdebug->leave_sub();
1014 # TODO: remove in 2.7.0 stable
1016 $main::lxdebug->enter_sub();
1019 my $shipto_id = shift;
1021 my $form = $main::form;
1022 my %myconfig = %main::myconfig;
1023 my $dbh = $form->get_standard_dbh(\%myconfig);
1025 do_query($form, $dbh, qq|UPDATE shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id);
1029 $main::lxdebug->leave_sub();
1032 # TODO: remove in 2.7.0 stable
1033 sub delete_contact {
1034 $main::lxdebug->enter_sub();
1039 my $form = $main::form;
1040 my %myconfig = %main::myconfig;
1041 my $dbh = $form->get_standard_dbh(\%myconfig);
1043 do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $cp_id);
1047 $main::lxdebug->leave_sub();
1051 $main::lxdebug->enter_sub();
1056 Common::check_params(\%params, qw(vc id));
1058 my $myconfig = \%main::myconfig;
1059 my $form = $main::form;
1061 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1063 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
1064 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
1065 my $placeholders = join ", ", ('?') x scalar @ids;
1066 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic
1068 WHERE id IN (${placeholders})|;
1070 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
1072 if (ref $params{id} eq 'ARRAY') {
1073 $result = { map { $_->{id} => $_ } @{ $result } };
1075 $result = $result->[0] || { 'id' => $params{id} };
1078 $main::lxdebug->leave_sub();
1083 sub parse_excel_file {
1084 $main::lxdebug->enter_sub();
1086 my ($self, $myconfig, $form) = @_;
1087 my $locale = $main::locale;
1089 my $defaults = SL::DB::Default->get;
1090 $form->error($::locale->text('No print templates have been created for this client yet. Please do so in the client configuration.')) if !$defaults->templates;
1091 $form->{templates} = $defaults->templates;
1093 $form->{formname} = 'sales_quotation';
1094 $form->{type} = 'sales_quotation';
1095 $form->{format} = 'excel';
1096 $form->{media} = 'screen';
1097 $form->{quonumber} = 1;
1100 # $form->{"notes"} will be overridden by the customer's/vendor's "notes" field. So save it here.
1101 $form->{ $form->{"formname"} . "notes" } = $form->{"notes"};
1105 $form->{"${inv}date"} = $form->{transdate};
1106 $form->{label} = $locale->text('Quotation');
1107 my $numberfld = "sqnumber";
1111 $form->{what_done} = $form->{formname};
1113 map({ delete($form->{$_}); } grep(/^cp_/, keys(%{ $form })));
1115 my $output_dateformat = $myconfig->{"dateformat"};
1116 my $output_numberformat = $myconfig->{"numberformat"};
1117 my $output_longdates = 1;
1119 # map login user variables
1120 map { $form->{"login_$_"} = $myconfig->{$_} } ("name", "email", "fax", "tel", "company");
1123 for my $field (qw(transdate_oe deliverydate_oe)) {
1125 $form->{$field}[$_] = $locale->date($myconfig, $form->{$field}[$_], 1);
1126 } 0 .. $#{ $form->{$field} };
1129 if ($form->{shipto_id}) {
1130 $form->get_shipto($myconfig);
1133 $form->{notes} =~ s/^\s+//g;
1135 delete $form->{printer_command};
1137 $form->get_employee_info($myconfig);
1139 my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_');
1141 if (scalar @{ $cvar_date_fields }) {
1142 format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields });
1145 while (my ($precision, $field_list) = each %{ $cvar_number_fields }) {
1146 reformat_numbers($output_numberformat, $precision, @{ $field_list });
1150 my $extension = 'xls';
1152 $form->{IN} = "$form->{formname}.${extension}";
1154 delete $form->{OUT};
1156 $form->parse_template($myconfig);
1158 $main::lxdebug->leave_sub();
1161 sub search_contacts {
1162 $::lxdebug->enter_sub;
1167 my $dbh = $params{dbh} || $::form->get_standard_dbh;
1170 'cp_name' => 'cp_name, cp_givenname',
1171 'vcname' => 'vcname, cp_name, cp_givenname',
1172 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
1175 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);
1177 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
1178 $::form->{sort} = $order_by;
1179 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
1181 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
1182 $order_by =~ s/,/ ${sortdir},/g;
1183 $order_by .= " $sortdir";
1185 my @where_tokens = ();
1188 if ($params{search_term}) {
1191 'cp.cp_name ILIKE ?',
1192 'cp.cp_givenname ILIKE ?',
1193 'cp.cp_email ILIKE ?';
1194 push @values, ('%' . $params{search_term} . '%') x 3;
1196 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
1197 my $number = $params{search_term};
1198 $number =~ s/[^\d]//g;
1199 $number = join '[ /\(\)+\-]*', split(m//, $number);
1201 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
1204 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
1207 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
1208 'trans_id_field' => 'cp.cp_id',
1209 'filter' => $params{filter});
1212 push @where_tokens, $cvar_where;
1213 push @values, @cvar_values;
1216 if (my $filter = $params{filter}) {
1217 for (qw(name title givenname email project abteilung)) {
1218 next unless $filter->{"cp_$_"};
1219 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
1222 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
1223 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
1226 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
1228 my $query = qq|SELECT cp.*,
1229 COALESCE(c.id, v.id) AS vcid,
1230 COALESCE(c.name, v.name) AS vcname,
1231 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
1232 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
1234 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
1235 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
1237 ORDER BY $order_by|;
1239 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
1241 $::lxdebug->leave_sub;
1243 return @{ $contacts };