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 #======================================================================
52 $main::lxdebug->enter_sub();
54 my ( $self, $myconfig, $form ) = @_;
56 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
58 my $dbh = $form->dbconnect($myconfig);
60 qq|SELECT ct.*, b.id AS business, cp.* | .
62 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
63 qq|LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) | .
64 qq|WHERE (ct.id = ?) | .
65 qq|ORDER BY cp.cp_id LIMIT 1|;
66 my $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
68 my $ref = $sth->fetchrow_hashref("NAME_lc");
70 map { $form->{$_} = $ref->{$_} } keys %$ref;
72 # remove any trailing whitespace
73 $form->{curr} =~ s/\s*$//;
76 if ( $form->{salesman_id} ) {
78 qq|SELECT ct.name AS salesman | .
82 selectrow_query($form, $dbh, $query, $form->{salesman_id});
85 my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $form->{login});
87 qq|SELECT n.*, n.itime::DATE AS created_on,
88 e.name AS created_by_name, e.login AS created_by_login
90 LEFT JOIN employee e ON (n.created_by = e.id)
91 WHERE (n.trans_id = ?) AND (n.trans_module = 'ct')|;
92 $form->{NOTES} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
95 qq|SELECT fu.follow_up_date, fu.done AS follow_up_done, e.name AS created_for_name, e.name AS created_for_login
97 LEFT JOIN employee e ON (fu.created_for_user = e.id)
98 WHERE (fu.note_id = ?)
99 AND NOT COALESCE(fu.done, FALSE)
100 AND ( (fu.created_by = ?)
101 OR (fu.created_by IN (SELECT DISTINCT what FROM follow_up_access WHERE who = ?)))|;
102 $sth = prepare_query($form, $dbh, $query);
104 foreach my $note (@{ $form->{NOTES} }) {
105 do_statement($form, $sth, $query, conv_i($note->{id}), conv_i($note->{created_by}), conv_i($employee_id));
106 $ref = $sth->fetchrow_hashref();
108 map { $note->{$_} = $ref->{$_} } keys %{ $ref } if ($ref);
113 if ($form->{edit_note_id}) {
115 qq|SELECT n.id AS NOTE_id, n.subject AS NOTE_subject, n.body AS NOTE_body,
116 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
118 LEFT JOIN follow_ups fu ON ((n.id = fu.note_id) AND NOT COALESCE(fu.done, FALSE))
120 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{edit_note_id}));
123 foreach my $key (keys %{ $ref }) {
125 $new_key =~ s/^([^_]+)/\U$1\E/;
126 $form->{$new_key} = $ref->{$key};
131 # check if it is orphaned
132 my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
135 if ($form->{db} eq 'vendor') {
136 $makemodel = qq| UNION SELECT 1 FROM makemodel mm WHERE mm.make = ?|;
143 qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
144 qq|WHERE ct.id = ? | .
148 qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
151 my ($dummy) = selectrow_query($form, $dbh, $query, (conv_i($form->{id})) x $num_args);
153 $form->{status} = "orphaned" unless ($dummy);
157 $main::lxdebug->leave_sub();
160 sub populate_drop_down_boxes {
161 $main::lxdebug->enter_sub();
163 my ($self, $myconfig, $form, $provided_dbh) = @_;
166 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig);
169 $query = qq|SELECT id, description FROM business ORDER BY id|;
170 $form->{all_business} = selectall_hashref_query($form, $dbh, $query);
174 qq|SELECT shipto_id, shiptoname, shiptodepartment_1, shiptostreet, shiptocity
176 WHERE (trans_id = ?) AND (module = 'CT')|;
177 $form->{SHIPTO} = selectall_hashref_query($form, $dbh, $query, $form->{id});
180 $query = qq|SELECT cp_id, cp_name, cp_givenname FROM contacts WHERE cp_cv_id = ? ORDER BY cp_name|;
181 $form->{CONTACTS} = selectall_hashref_query($form, $dbh, $query, $form->{id});
184 $query = qq|SELECT id, description FROM language ORDER BY id|;
185 $form->{languages} = selectall_hashref_query($form, $dbh, $query);
188 $query = qq|SELECT id, description FROM payment_terms ORDER BY sortkey|;
189 $form->{payment_terms} = selectall_hashref_query($form, $dbh, $query);
191 $dbh->disconnect() unless ($provided_dbh);
193 $main::lxdebug->leave_sub();
196 sub query_titles_and_greetings {
197 $main::lxdebug->enter_sub();
199 my ( $self, $myconfig, $form ) = @_;
200 my ( %tmp, $ref, $query );
202 my $dbh = $form->dbconnect($myconfig);
205 qq|SELECT DISTINCT(greeting) | .
207 qq|WHERE greeting ~ '[a-zA-Z]' | .
209 qq|SELECT DISTINCT(greeting) | .
211 qq|WHERE greeting ~ '[a-zA-Z]' | .
212 qq|ORDER BY greeting|;
214 map({ $tmp{$_} = 1; } selectall_array_query($form, $dbh, $query));
215 $form->{COMPANY_GREETINGS} = [ sort(keys(%tmp)) ];
218 qq|SELECT DISTINCT(cp_title) | .
220 qq|WHERE cp_title ~ '[a-zA-Z]'|;
221 $form->{TITLES} = [ selectall_array_query($form, $dbh, $query) ];
224 qq|SELECT DISTINCT(cp_abteilung) | .
226 qq|WHERE cp_abteilung ~ '[a-zA-Z]'|;
227 $form->{DEPARTMENT} = [ selectall_array_query($form, $dbh, $query) ];
230 $main::lxdebug->leave_sub();
234 $main::lxdebug->enter_sub();
236 my ( $self, $myconfig, $form ) = @_;
238 # set pricegroup to default
239 $form->{klass} = 0 unless ($form->{klass});
241 # connect to database
242 my $dbh = $form->get_standard_dbh;
245 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
246 if ( $form->{"selected_cp_${_}"} );
247 } qw(title greeting abteilung) );
248 $form->{"greeting"} = $form->{"selected_company_greeting"}
249 if ( $form->{"selected_company_greeting"} );
251 # assign value discount, terms, creditlimit
252 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
253 $form->{discount} /= 100;
254 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
256 my ( $query, $sth, $f_id );
259 $query = qq|SELECT id FROM customer WHERE customernumber = ?|;
260 ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber});
262 if (($f_id ne $form->{id}) && ($f_id ne "")) {
263 $main::lxdebug->leave_sub();
268 my $customernumber = SL::TransNumber->new(type => 'customer',
270 number => $form->{customernumber},
271 business_id => $form->{business},
273 $form->{customernumber} = $customernumber->create_unique unless $customernumber->is_unique;
275 $query = qq|SELECT nextval('id')|;
276 ($form->{id}) = selectrow_query($form, $dbh, $query);
278 $query = qq|INSERT INTO customer (id, name) VALUES (?, '')|;
279 do_query($form, $dbh, $query, $form->{id});
282 $query = qq|UPDATE customer SET | .
283 qq|customernumber = ?, | .
286 qq|department_1 = ?, | .
287 qq|department_2 = ?, | .
301 qq|creditlimit = ?, | .
303 qq|business_id = ?, | .
304 qq|taxnumber = ?, | .
306 qq|account_number = ?, | .
307 qq|bank_code = ?, | .
312 qq|direct_debit = ?, | .
315 qq|salesman_id = ?, | .
316 qq|language_id = ?, | .
317 qq|payment_id = ?, | .
318 qq|taxzone_id = ?, | .
319 qq|user_password = ?, | .
320 qq|c_vendor_id = ?, | .
325 $form->{customernumber},
328 $form->{department_1},
329 $form->{department_2},
343 $form->{creditlimit},
344 conv_i($form->{terms}),
345 conv_i($form->{business}),
348 $form->{account_number},
353 $form->{obsolete} ? 't' : 'f',
354 $form->{direct_debit} ? 't' : 'f',
357 conv_i($form->{salesman_id}),
358 conv_i($form->{language_id}),
359 conv_i($form->{payment_id}),
360 conv_i($form->{taxzone_id}, 0),
361 $form->{user_password},
362 $form->{c_vendor_id},
363 conv_i($form->{klass}),
364 substr($form->{currency}, 0, 3),
367 do_query( $form, $dbh, $query, @values );
370 my @columns = qw(cp_title cp_givenname cp_name cp_email cp_phone1 cp_phone2 cp_abteilung cp_fax
371 cp_mobile1 cp_mobile2 cp_satphone cp_satfax cp_project cp_privatphone cp_privatemail cp_birthday cp_gender
372 cp_street cp_zipcode cp_city);
373 @values = map { $_ eq 'cp_gender' ? ($form->{$_} eq 'f' ? 'f' : 'm') : $form->{$_} } @columns;
375 if ( $form->{cp_id} ) {
376 $query = qq|UPDATE contacts SET | . join(', ', map { "${_} = ?" } @columns) . qq| WHERE cp_id = ?|;
377 push @values, $form->{cp_id};
379 } elsif ( $form->{cp_name} || $form->{cp_givenname} ) {
380 $query = qq|INSERT INTO contacts (| . join(', ', 'cp_cv_id', @columns) . qq|) VALUES (?, | . join(', ', ('?') x scalar(@columns)) . qq|)|;
381 unshift @values, $form->{id};
383 do_query( $form, $dbh, $query, @values ) if ($query);
386 $form->add_shipto( $dbh, $form->{id}, "CT" );
388 $self->_save_note('dbh' => $dbh);
389 $self->_delete_selected_notes('dbh' => $dbh);
391 CVar->save_custom_variables('dbh' => $dbh,
393 'trans_id' => $form->{id},
394 'variables' => $form,
395 'always_valid' => 1);
396 if ($form->{cp_id}) {
397 CVar->save_custom_variables('dbh' => $dbh,
398 'module' => 'Contacts',
399 'trans_id' => $form->{cp_id},
400 'variables' => $form,
401 'name_prefix' => 'cp',
402 'always_valid' => 1);
405 my $rc = $dbh->commit();
407 $main::lxdebug->leave_sub();
412 $main::lxdebug->enter_sub();
414 my ( $self, $myconfig, $form ) = @_;
416 $form->{taxzone_id} *= 1;
417 # connect to database
418 my $dbh = $form->get_standard_dbh;
421 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
422 if ( $form->{"selected_cp_${_}"} );
423 } qw(title greeting abteilung) );
424 $form->{"greeting"} = $form->{"selected_company_greeting"}
425 if ( $form->{"selected_company_greeting"} );
427 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
428 $form->{discount} /= 100;
429 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
434 $query = qq|SELECT nextval('id')|;
435 ($form->{id}) = selectrow_query($form, $dbh, $query);
437 $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|;
438 do_query($form, $dbh, $query, $form->{id});
440 my $vendornumber = SL::TransNumber->new(type => 'vendor',
442 number => $form->{vendornumber},
444 $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique;
448 qq|UPDATE vendor SET | .
449 qq| vendornumber = ?, | .
451 qq| greeting = ?, | .
452 qq| department_1 = ?, | .
453 qq| department_2 = ?, | .
458 qq| homepage = ?, | .
467 qq| discount = ?, | .
468 qq| creditlimit = ?, | .
469 qq| business_id = ?, | .
470 qq| taxnumber = ?, | .
471 qq| language = ?, | .
472 qq| account_number = ?, | .
473 qq| bank_code = ?, | .
477 qq| obsolete = ?, | .
478 qq| direct_debit = ?, | .
480 qq| payment_id = ?, | .
481 qq| taxzone_id = ?, | .
482 qq| language_id = ?, | .
483 qq| username = ?, | .
484 qq| user_password = ?, | .
485 qq| v_customer_id = ?, | .
489 $form->{vendornumber},
492 $form->{department_1},
493 $form->{department_2},
506 conv_i($form->{terms}),
508 $form->{creditlimit},
509 conv_i($form->{business}),
512 $form->{account_number},
517 $form->{obsolete} ? 't' : 'f',
518 $form->{direct_debit} ? 't' : 'f',
520 conv_i($form->{payment_id}),
521 conv_i($form->{taxzone_id}, 0),
522 conv_i( $form->{language_id}),
524 $form->{user_password},
525 $form->{v_customer_id},
526 substr($form->{currency}, 0, 3),
529 do_query($form, $dbh, $query, @values);
532 if ( $form->{cp_id} ) {
533 $query = qq|UPDATE contacts SET | .
535 qq|cp_givenname = ?, | .
538 qq|cp_phone1 = ?, | .
539 qq|cp_phone2 = ?, | .
540 qq|cp_abteilung = ?, | .
542 qq|cp_mobile1 = ?, | .
543 qq|cp_mobile2 = ?, | .
544 qq|cp_satphone = ?, | .
545 qq|cp_satfax = ?, | .
546 qq|cp_project = ?, | .
547 qq|cp_privatphone = ?, | .
548 qq|cp_privatemail = ?, | .
549 qq|cp_birthday = ?, | .
554 $form->{cp_givenname},
559 $form->{cp_abteilung},
563 $form->{cp_satphone},
566 $form->{cp_privatphone},
567 $form->{cp_privatemail},
568 $form->{cp_birthday},
569 $form->{cp_gender} eq 'f' ? 'f' : 'm',
572 } elsif ( $form->{cp_name} || $form->{cp_givenname} ) {
574 qq|INSERT INTO contacts ( cp_cv_id, cp_title, cp_givenname, | .
575 qq| cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, | .
576 qq| cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, | .
577 qq| cp_birthday, cp_gender) | .
578 qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
582 $form->{cp_givenname},
587 $form->{cp_abteilung},
591 $form->{cp_satphone},
594 $form->{cp_privatphone},
595 $form->{cp_privatemail},
596 $form->{cp_birthday},
600 do_query($form, $dbh, $query, @values) if ($query);
603 $form->add_shipto( $dbh, $form->{id}, "CT" );
605 $self->_save_note('dbh' => $dbh);
606 $self->_delete_selected_notes('dbh' => $dbh);
608 CVar->save_custom_variables('dbh' => $dbh,
610 'trans_id' => $form->{id},
611 'variables' => $form,
612 'always_valid' => 1);
613 if ($form->{cp_id}) {
614 CVar->save_custom_variables('dbh' => $dbh,
615 'module' => 'Contacts',
616 'trans_id' => $form->{cp_id},
617 'variables' => $form,
618 'name_prefix' => 'cp',
619 'always_valid' => 1);
622 my $rc = $dbh->commit();
624 $main::lxdebug->leave_sub();
629 $main::lxdebug->enter_sub();
631 my ( $self, $myconfig, $form ) = @_;
632 # connect to database
633 my $dbh = $form->dbconnect($myconfig);
636 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
637 my $query = qq|DELETE FROM $cv WHERE id = ?|;
638 do_query($form, $dbh, $query, $form->{id});
642 $main::lxdebug->leave_sub();
646 $main::lxdebug->enter_sub();
648 my ( $self, $myconfig, $form ) = @_;
650 # connect to database
651 my $dbh = $form->dbconnect($myconfig);
653 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
658 my %allowed_sort_columns =
660 id customernumber vendornumber name contact phone fax email street
661 taxnumber business invnumber ordnumber quonumber zipcode city
663 my $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
664 $form->{sort} = $sortorder;
665 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
667 if ($sortorder !~ /(business|id)/ && 1 >= scalar grep { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber )) {
668 $sortorder = "lower($sortorder) ${sortdir}";
670 $sortorder .= " ${sortdir}";
673 if ($form->{"${cv}number"}) {
674 $where .= " AND ct.${cv}number ILIKE ?";
675 push(@values, '%' . $form->{"${cv}number"} . '%');
678 foreach my $key (qw(name contact email)) {
680 $where .= " AND ct.$key ILIKE ?";
681 push(@values, '%' . $form->{$key} . '%');
685 if ($form->{cp_name}) {
686 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
687 push @values, '%' . $form->{cp_name} . '%';
690 if ($form->{addr_city}) {
691 $where .= " AND ((lower(ct.city) LIKE lower(?))
696 WHERE (module = 'CT')
697 AND (lower(shiptocity) LIKE lower(?))
700 push @values, ('%' . $form->{addr_city} . '%') x 2;
703 if ( $form->{status} eq 'orphaned' ) {
705 qq| AND ct.id NOT IN | .
706 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
707 if ($cv eq 'customer') {
709 qq| AND ct.id NOT IN | .
710 qq| (SELECT a.customer_id FROM ar a, customer cv | .
711 qq| WHERE cv.id = a.customer_id)|;
713 if ($cv eq 'vendor') {
715 qq| AND ct.id NOT IN | .
716 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
717 qq| WHERE cv.id = a.vendor_id)|;
719 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
722 if ($form->{obsolete} eq "Y") {
723 $where .= qq| AND obsolete|;
724 } elsif ($form->{obsolete} eq "N") {
725 $where .= qq| AND NOT obsolete|;
728 if ($form->{business_id}) {
729 $where .= qq| AND (business_id = ?)|;
730 push(@values, conv_i($form->{business_id}));
733 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
734 # Gilt nicht für Lieferanten
735 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
736 $where .= qq| AND ct.salesman_id = (select id from employee where login= ?)|;
737 push(@values, $form->{login});
740 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
741 'trans_id_field' => 'ct.id',
745 $where .= qq| AND ($cvar_where)|;
746 push @values, @cvar_values;
749 if ($form->{addr_street}) {
750 $where .= qq| AND (street ILIKE ?)|;
751 push @values, '%' . $form->{addr_street} . '%';
754 if ($form->{addr_zipcode}) {
755 $where .= qq| AND (zipcode ILIKE ?)|;
756 push @values, $form->{addr_zipcode} . '%';
760 qq|SELECT ct.*, b.description AS business | .
762 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
765 my @saved_values = @values;
766 # redo for invoices, orders and quotations
767 if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
768 my ($ar, $union, $module);
771 if ($form->{l_invnumber}) {
772 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
773 my $module = $ar eq 'ar' ? 'is' : 'ir';
776 qq|SELECT ct.*, b.description AS business, | .
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 | .
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|WHERE $where AND (a.invoice = '1')|;
788 if ( $form->{l_ordnumber} ) {
789 if ($union eq "UNION") {
790 push(@values, @saved_values);
794 qq|SELECT ct.*, b.description AS business,| .
795 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
796 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
798 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
799 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
800 qq|WHERE $where AND (o.quotation = '0')|;
805 if ( $form->{l_quonumber} ) {
806 if ($union eq "UNION") {
807 push(@values, @saved_values);
811 qq|SELECT ct.*, b.description AS business, | .
812 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
813 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
815 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
816 qq|LEFT JOIN business b ON (ct.business_id = b.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 $form->{formname} = 'sales_quotation';
1090 $form->{type} = 'sales_quotation';
1091 $form->{format} = 'excel';
1092 $form->{media} = 'screen';
1093 $form->{quonumber} = 1;
1096 # $form->{"notes"} will be overridden by the customer's/vendor's "notes" field. So save it here.
1097 $form->{ $form->{"formname"} . "notes" } = $form->{"notes"};
1101 $form->{"${inv}date"} = $form->{transdate};
1102 $form->{label} = $locale->text('Quotation');
1103 my $numberfld = "sqnumber";
1107 $form->{what_done} = $form->{formname};
1109 map({ delete($form->{$_}); } grep(/^cp_/, keys(%{ $form })));
1111 my $output_dateformat = $myconfig->{"dateformat"};
1112 my $output_numberformat = $myconfig->{"numberformat"};
1113 my $output_longdates = 1;
1115 # map login user variables
1116 map { $form->{"login_$_"} = $myconfig->{$_} } ("name", "email", "fax", "tel", "company");
1119 for my $field (qw(transdate_oe deliverydate_oe)) {
1121 $form->{$field}[$_] = $locale->date($myconfig, $form->{$field}[$_], 1);
1122 } 0 .. $#{ $form->{$field} };
1125 if ($form->{shipto_id}) {
1126 $form->get_shipto($myconfig);
1129 $form->{notes} =~ s/^\s+//g;
1131 $form->{templates} = $myconfig->{templates};
1133 delete $form->{printer_command};
1135 $form->get_employee_info($myconfig);
1137 my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_');
1139 if (scalar @{ $cvar_date_fields }) {
1140 format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields });
1143 while (my ($precision, $field_list) = each %{ $cvar_number_fields }) {
1144 reformat_numbers($output_numberformat, $precision, @{ $field_list });
1148 my $extension = 'xls';
1150 $form->{IN} = "$form->{formname}.${extension}";
1152 delete $form->{OUT};
1154 $form->parse_template($myconfig);
1156 $main::lxdebug->leave_sub();
1159 sub search_contacts {
1160 $::lxdebug->enter_sub;
1165 my $dbh = $params{dbh} || $::form->get_standard_dbh;
1166 my $vc = $params{db} eq 'customer' ? 'customer' : 'vendor';
1169 'cp_name' => 'cp_name, cp_givenname',
1170 'vcname' => 'vcname, cp_name, cp_givenname',
1171 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
1174 my %sortcols = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email cp_street cp_zipcode cp_city vcname vcnumber);
1176 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
1177 $::form->{sort} = $order_by;
1178 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
1180 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
1181 $order_by =~ s/,/ ${sortdir},/g;
1182 $order_by .= " $sortdir";
1184 my @where_tokens = ();
1187 if ($params{search_term}) {
1190 'cp.cp_name ILIKE ?',
1191 'cp.cp_givenname ILIKE ?',
1192 'cp.cp_email ILIKE ?';
1193 push @values, ('%' . $params{search_term} . '%') x 3;
1195 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
1196 my $number = $params{search_term};
1197 $number =~ s/[^\d]//g;
1198 $number = join '[ /\(\)+\-]*', split(m//, $number);
1200 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
1203 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
1206 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
1207 'trans_id_field' => 'cp.cp_id',
1208 'filter' => $params{filter});
1211 push @where_tokens, $cvar_where;
1212 push @values, @cvar_values;
1215 if (my $filter = $params{filter}) {
1216 for (qw(name title givenname email project abteilung)) {
1217 next unless $filter->{"cp_$_"};
1218 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
1221 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
1222 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
1225 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
1227 my $query = qq|SELECT cp.*,
1228 COALESCE(c.id, v.id) AS vcid,
1229 COALESCE(c.name, v.name) AS vcname,
1230 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
1231 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
1233 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
1234 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
1236 ORDER BY $order_by|;
1238 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
1240 $::lxdebug->leave_sub;
1242 return @{ $contacts };