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 = ?, | .
323 qq|taxincluded_checked = ? | .
326 $form->{customernumber},
329 $form->{department_1},
330 $form->{department_2},
344 $form->{creditlimit},
345 conv_i($form->{terms}),
346 conv_i($form->{business}),
349 $form->{account_number},
354 $form->{obsolete} ? 't' : 'f',
355 $form->{direct_debit} ? 't' : 'f',
358 conv_i($form->{salesman_id}),
359 conv_i($form->{language_id}),
360 conv_i($form->{payment_id}),
361 conv_i($form->{taxzone_id}, 0),
362 $form->{user_password},
363 $form->{c_vendor_id},
364 conv_i($form->{klass}),
365 substr($form->{currency}, 0, 3),
366 $form->{taxincluded_checked} ne '' ? $form->{taxincluded_checked} : undef,
369 do_query( $form, $dbh, $query, @values );
372 my @columns = qw(cp_title cp_givenname cp_name cp_email cp_phone1 cp_phone2 cp_abteilung cp_fax
373 cp_mobile1 cp_mobile2 cp_satphone cp_satfax cp_project cp_privatphone cp_privatemail cp_birthday cp_gender
374 cp_street cp_zipcode cp_city);
375 @values = map { $_ eq 'cp_gender' ? ($form->{$_} eq 'f' ? 'f' : 'm') : $form->{$_} } @columns;
377 if ( $form->{cp_id} ) {
378 $query = qq|UPDATE contacts SET | . join(', ', map { "${_} = ?" } @columns) . qq| WHERE cp_id = ?|;
379 push @values, $form->{cp_id};
381 } elsif ( $form->{cp_name} || $form->{cp_givenname} ) {
382 $query = qq|INSERT INTO contacts (| . join(', ', 'cp_cv_id', @columns) . qq|) VALUES (?, | . join(', ', ('?') x scalar(@columns)) . qq|)|;
383 unshift @values, $form->{id};
385 do_query( $form, $dbh, $query, @values ) if ($query);
388 $form->add_shipto( $dbh, $form->{id}, "CT" );
390 $self->_save_note('dbh' => $dbh);
391 $self->_delete_selected_notes('dbh' => $dbh);
393 CVar->save_custom_variables('dbh' => $dbh,
395 'trans_id' => $form->{id},
396 'variables' => $form,
397 'always_valid' => 1);
398 if ($form->{cp_id}) {
399 CVar->save_custom_variables('dbh' => $dbh,
400 'module' => 'Contacts',
401 'trans_id' => $form->{cp_id},
402 'variables' => $form,
403 'name_prefix' => 'cp',
404 'always_valid' => 1);
407 my $rc = $dbh->commit();
409 $main::lxdebug->leave_sub();
414 $main::lxdebug->enter_sub();
416 my ( $self, $myconfig, $form ) = @_;
418 $form->{taxzone_id} *= 1;
419 # connect to database
420 my $dbh = $form->get_standard_dbh;
423 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
424 if ( $form->{"selected_cp_${_}"} );
425 } qw(title greeting abteilung) );
426 $form->{"greeting"} = $form->{"selected_company_greeting"}
427 if ( $form->{"selected_company_greeting"} );
429 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
430 $form->{discount} /= 100;
431 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
436 $query = qq|SELECT nextval('id')|;
437 ($form->{id}) = selectrow_query($form, $dbh, $query);
439 $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|;
440 do_query($form, $dbh, $query, $form->{id});
442 my $vendornumber = SL::TransNumber->new(type => 'vendor',
444 number => $form->{vendornumber},
446 $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique;
450 qq|UPDATE vendor SET | .
451 qq| vendornumber = ?, | .
453 qq| greeting = ?, | .
454 qq| department_1 = ?, | .
455 qq| department_2 = ?, | .
460 qq| homepage = ?, | .
469 qq| discount = ?, | .
470 qq| creditlimit = ?, | .
471 qq| business_id = ?, | .
472 qq| taxnumber = ?, | .
473 qq| language = ?, | .
474 qq| account_number = ?, | .
475 qq| bank_code = ?, | .
479 qq| obsolete = ?, | .
480 qq| direct_debit = ?, | .
482 qq| payment_id = ?, | .
483 qq| taxzone_id = ?, | .
484 qq| language_id = ?, | .
485 qq| username = ?, | .
486 qq| user_password = ?, | .
487 qq| v_customer_id = ?, | .
489 qq| taxincluded_checked = ? | .
492 $form->{vendornumber},
495 $form->{department_1},
496 $form->{department_2},
509 conv_i($form->{terms}),
511 $form->{creditlimit},
512 conv_i($form->{business}),
515 $form->{account_number},
520 $form->{obsolete} ? 't' : 'f',
521 $form->{direct_debit} ? 't' : 'f',
523 conv_i($form->{payment_id}),
524 conv_i($form->{taxzone_id}, 0),
525 conv_i( $form->{language_id}),
527 $form->{user_password},
528 $form->{v_customer_id},
529 substr($form->{currency}, 0, 3),
530 $form->{taxincluded_checked},
533 do_query($form, $dbh, $query, @values);
536 if ( $form->{cp_id} ) {
537 $query = qq|UPDATE contacts SET | .
539 qq|cp_givenname = ?, | .
542 qq|cp_phone1 = ?, | .
543 qq|cp_phone2 = ?, | .
544 qq|cp_abteilung = ?, | .
546 qq|cp_mobile1 = ?, | .
547 qq|cp_mobile2 = ?, | .
548 qq|cp_satphone = ?, | .
549 qq|cp_satfax = ?, | .
550 qq|cp_project = ?, | .
551 qq|cp_privatphone = ?, | .
552 qq|cp_privatemail = ?, | .
553 qq|cp_birthday = ?, | .
558 $form->{cp_givenname},
563 $form->{cp_abteilung},
567 $form->{cp_satphone},
570 $form->{cp_privatphone},
571 $form->{cp_privatemail},
572 $form->{cp_birthday},
573 $form->{cp_gender} eq 'f' ? 'f' : 'm',
576 } elsif ( $form->{cp_name} || $form->{cp_givenname} ) {
578 qq|INSERT INTO contacts ( cp_cv_id, cp_title, cp_givenname, | .
579 qq| cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, | .
580 qq| cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, | .
581 qq| cp_birthday, cp_gender) | .
582 qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
586 $form->{cp_givenname},
591 $form->{cp_abteilung},
595 $form->{cp_satphone},
598 $form->{cp_privatphone},
599 $form->{cp_privatemail},
600 $form->{cp_birthday},
604 do_query($form, $dbh, $query, @values) if ($query);
607 $form->add_shipto( $dbh, $form->{id}, "CT" );
609 $self->_save_note('dbh' => $dbh);
610 $self->_delete_selected_notes('dbh' => $dbh);
612 CVar->save_custom_variables('dbh' => $dbh,
614 'trans_id' => $form->{id},
615 'variables' => $form,
616 'always_valid' => 1);
617 if ($form->{cp_id}) {
618 CVar->save_custom_variables('dbh' => $dbh,
619 'module' => 'Contacts',
620 'trans_id' => $form->{cp_id},
621 'variables' => $form,
622 'name_prefix' => 'cp',
623 'always_valid' => 1);
626 my $rc = $dbh->commit();
628 $main::lxdebug->leave_sub();
633 $main::lxdebug->enter_sub();
635 my ( $self, $myconfig, $form ) = @_;
636 # connect to database
637 my $dbh = $form->dbconnect($myconfig);
640 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
641 my $query = qq|DELETE FROM $cv WHERE id = ?|;
642 do_query($form, $dbh, $query, $form->{id});
646 $main::lxdebug->leave_sub();
650 $main::lxdebug->enter_sub();
652 my ( $self, $myconfig, $form ) = @_;
654 # connect to database
655 my $dbh = $form->dbconnect($myconfig);
657 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
662 my %allowed_sort_columns =
664 id customernumber vendornumber name contact phone fax email street
665 taxnumber business invnumber ordnumber quonumber zipcode city
667 my $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
668 $form->{sort} = $sortorder;
669 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
671 if ($sortorder !~ /(business|id)/ && 1 >= scalar grep { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber )) {
672 $sortorder = "lower($sortorder) ${sortdir}";
674 $sortorder .= " ${sortdir}";
677 if ($form->{"${cv}number"}) {
678 $where .= " AND ct.${cv}number ILIKE ?";
679 push(@values, '%' . $form->{"${cv}number"} . '%');
682 foreach my $key (qw(name contact email)) {
684 $where .= " AND ct.$key ILIKE ?";
685 push(@values, '%' . $form->{$key} . '%');
689 if ($form->{cp_name}) {
690 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
691 push @values, '%' . $form->{cp_name} . '%';
694 if ($form->{addr_city}) {
695 $where .= " AND ((lower(ct.city) LIKE lower(?))
700 WHERE (module = 'CT')
701 AND (lower(shiptocity) LIKE lower(?))
704 push @values, ('%' . $form->{addr_city} . '%') x 2;
707 if ( $form->{status} eq 'orphaned' ) {
709 qq| AND ct.id NOT IN | .
710 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
711 if ($cv eq 'customer') {
713 qq| AND ct.id NOT IN | .
714 qq| (SELECT a.customer_id FROM ar a, customer cv | .
715 qq| WHERE cv.id = a.customer_id)|;
717 if ($cv eq 'vendor') {
719 qq| AND ct.id NOT IN | .
720 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
721 qq| WHERE cv.id = a.vendor_id)|;
723 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
726 if ($form->{obsolete} eq "Y") {
727 $where .= qq| AND obsolete|;
728 } elsif ($form->{obsolete} eq "N") {
729 $where .= qq| AND NOT obsolete|;
732 if ($form->{business_id}) {
733 $where .= qq| AND (business_id = ?)|;
734 push(@values, conv_i($form->{business_id}));
737 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
738 # Gilt nicht für Lieferanten
739 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
740 $where .= qq| AND ct.salesman_id = (select id from employee where login= ?)|;
741 push(@values, $form->{login});
744 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
745 'trans_id_field' => 'ct.id',
749 $where .= qq| AND ($cvar_where)|;
750 push @values, @cvar_values;
753 if ($form->{addr_street}) {
754 $where .= qq| AND (street ILIKE ?)|;
755 push @values, '%' . $form->{addr_street} . '%';
758 if ($form->{addr_zipcode}) {
759 $where .= qq| AND (zipcode ILIKE ?)|;
760 push @values, $form->{addr_zipcode} . '%';
764 qq|SELECT ct.*, b.description AS business | .
766 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
769 my @saved_values = @values;
770 # redo for invoices, orders and quotations
771 if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
772 my ($ar, $union, $module);
775 if ($form->{l_invnumber}) {
776 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
777 my $module = $ar eq 'ar' ? 'is' : 'ir';
780 qq|SELECT ct.*, b.description AS business, | .
781 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
782 qq| '$module' AS module, 'invoice' AS formtype, | .
783 qq| (a.amount = a.paid) AS closed | .
785 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
786 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
787 qq|WHERE $where AND (a.invoice = '1')|;
792 if ( $form->{l_ordnumber} ) {
793 if ($union eq "UNION") {
794 push(@values, @saved_values);
798 qq|SELECT ct.*, b.description AS business,| .
799 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
800 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
802 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
803 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
804 qq|WHERE $where AND (o.quotation = '0')|;
809 if ( $form->{l_quonumber} ) {
810 if ($union eq "UNION") {
811 push(@values, @saved_values);
815 qq|SELECT ct.*, b.description AS business, | .
816 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
817 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
819 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
820 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
821 qq|WHERE $where AND (o.quotation = '1')|;
825 $query .= qq| ORDER BY $sortorder|;
827 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
829 $main::lxdebug->leave_sub();
833 $main::lxdebug->enter_sub();
835 my ( $self, $myconfig, $form ) = @_;
837 die 'Missing argument: cp_id' unless $::form->{cp_id};
839 my $dbh = $form->dbconnect($myconfig);
841 qq|SELECT * FROM contacts c | .
842 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
843 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
844 my $ref = $sth->fetchrow_hashref("NAME_lc");
846 map { $form->{$_} = $ref->{$_} } keys %$ref;
848 $query = qq|SELECT COUNT(cp_id) AS used FROM (
849 SELECT cp_id FROM oe UNION
850 SELECT cp_id FROM ar UNION
851 SELECT cp_id FROM ap UNION
852 SELECT cp_id FROM delivery_orders
853 ) AS cpid WHERE cp_id = ? OR ? = 0|;
854 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
859 $main::lxdebug->leave_sub();
863 $main::lxdebug->enter_sub();
865 my ( $self, $myconfig, $form ) = @_;
866 my $dbh = $form->dbconnect($myconfig);
867 my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
868 my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id});
870 my $ref = $sth->fetchrow_hashref("NAME_lc");
872 map { $form->{$_} = $ref->{$_} } keys %$ref;
874 $query = qq|SELECT COUNT(shipto_id) AS used FROM (
875 SELECT shipto_id FROM oe UNION
876 SELECT shipto_id FROM ar UNION
877 SELECT shipto_id FROM delivery_orders
878 ) AS stid WHERE shipto_id = ? OR ? = 0|;
879 ($form->{shiptoused}) = selectfirst_array_query($form, $dbh, $query, ($form->{shipto_id})x2);
884 $main::lxdebug->leave_sub();
888 $main::lxdebug->enter_sub();
890 my ( $self, $myconfig, $form ) = @_;
891 my $dbh = $form->dbconnect($myconfig);
893 my $arap = $form->{db} eq "vendor" ? "ap" : "ar";
894 my $db = $form->{db} eq "customer" ? "customer" : "vendor";
895 my $qty_sign = $form->{db} eq 'vendor' ? ' * -1 AS qty' : '';
897 my $where = " WHERE 1=1 ";
900 if ($form->{shipto_id} && ($arap eq "ar")) {
901 $where .= "AND ${arap}.shipto_id = ?";
902 push(@values, $form->{shipto_id});
904 $where .= "AND ${arap}.${db}_id = ?";
905 push(@values, $form->{id});
909 $where .= "AND ${arap}.transdate >= ?";
910 push(@values, conv_date($form->{from}));
913 $where .= "AND ${arap}.transdate <= ?";
914 push(@values, conv_date($form->{to}));
917 qq|SELECT s.shiptoname, i.qty $qty_sign, | .
918 qq| ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | .
919 qq| i.description, i.unit, i.sellprice, | .
920 qq| oe.id AS oe_id, invoice | .
922 qq|LEFT JOIN shipto s ON | .
924 ? qq|(ar.shipto_id = s.shipto_id) |
925 : qq|(ap.id = s.trans_id) |) .
926 qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | .
927 qq|LEFT join parts p ON (p.id = i.parts_id) | .
928 qq|LEFT JOIN oe ON (oe.ordnumber = ${arap}.ordnumber AND NOT ${arap}.ordnumber = '') | .
930 qq|ORDER BY ${arap}.transdate DESC LIMIT 15|;
932 $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values);
936 $main::lxdebug->leave_sub();
940 $main::lxdebug->enter_sub();
945 my $form = $main::form;
947 Common::check_params(\%params, 'dbh');
949 if (!$form->{NOTE_subject}) {
950 $main::lxdebug->leave_sub();
954 my $dbh = $params{dbh};
958 'id' => $form->{NOTE_id},
959 'subject' => $form->{NOTE_subject},
960 'body' => $form->{NOTE_body},
961 'trans_id' => $form->{id},
962 'trans_module' => 'ct',
965 $note{id} = Notes->save(%note);
967 if ($form->{FU_date}) {
969 'id' => $form->{FU_id},
970 'note_id' => $note{id},
971 'follow_up_date' => $form->{FU_date},
972 'created_for_user' => $form->{FU_created_for_user},
973 'done' => $form->{FU_done} ? 1 : 0,
974 'subject' => $form->{NOTE_subject},
975 'body' => $form->{NOTE_body},
978 'trans_id' => $form->{id},
979 'trans_type' => $form->{db} eq 'customer' ? 'customer' : 'vendor',
980 'trans_info' => $form->{name},
985 $follow_up{id} = FU->save(%follow_up);
987 } elsif ($form->{FU_id}) {
988 do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($form->{FU_id}));
989 do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, conv_i($form->{FU_id}));
992 delete @{$form}{grep { /^NOTE_|^FU_/ } keys %{ $form }};
994 $main::lxdebug->leave_sub();
997 sub _delete_selected_notes {
998 $main::lxdebug->enter_sub();
1003 Common::check_params(\%params, 'dbh');
1005 my $form = $main::form;
1006 my $dbh = $params{dbh};
1008 foreach my $i (1 .. $form->{NOTES_rowcount}) {
1009 next unless ($form->{"NOTE_delete_$i"} && $form->{"NOTE_id_$i"});
1011 Notes->delete('dbh' => $params{dbh},
1012 'id' => $form->{"NOTE_id_$i"});
1015 $main::lxdebug->leave_sub();
1018 # TODO: remove in 2.7.0 stable
1020 $main::lxdebug->enter_sub();
1023 my $shipto_id = shift;
1025 my $form = $main::form;
1026 my %myconfig = %main::myconfig;
1027 my $dbh = $form->get_standard_dbh(\%myconfig);
1029 do_query($form, $dbh, qq|UPDATE shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id);
1033 $main::lxdebug->leave_sub();
1036 # TODO: remove in 2.7.0 stable
1037 sub delete_contact {
1038 $main::lxdebug->enter_sub();
1043 my $form = $main::form;
1044 my %myconfig = %main::myconfig;
1045 my $dbh = $form->get_standard_dbh(\%myconfig);
1047 do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $cp_id);
1051 $main::lxdebug->leave_sub();
1055 $main::lxdebug->enter_sub();
1060 Common::check_params(\%params, qw(vc id));
1062 my $myconfig = \%main::myconfig;
1063 my $form = $main::form;
1065 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1067 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
1068 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
1069 my $placeholders = join ", ", ('?') x scalar @ids;
1070 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic
1072 WHERE id IN (${placeholders})|;
1074 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
1076 if (ref $params{id} eq 'ARRAY') {
1077 $result = { map { $_->{id} => $_ } @{ $result } };
1079 $result = $result->[0] || { 'id' => $params{id} };
1082 $main::lxdebug->leave_sub();
1087 sub parse_excel_file {
1088 $main::lxdebug->enter_sub();
1090 my ($self, $myconfig, $form) = @_;
1091 my $locale = $main::locale;
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 $form->{templates} = $myconfig->{templates};
1137 delete $form->{printer_command};
1139 $form->get_employee_info($myconfig);
1141 my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_');
1143 if (scalar @{ $cvar_date_fields }) {
1144 format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields });
1147 while (my ($precision, $field_list) = each %{ $cvar_number_fields }) {
1148 reformat_numbers($output_numberformat, $precision, @{ $field_list });
1152 my $extension = 'xls';
1154 $form->{IN} = "$form->{formname}.${extension}";
1156 delete $form->{OUT};
1158 $form->parse_template($myconfig);
1160 $main::lxdebug->leave_sub();
1163 sub search_contacts {
1164 $::lxdebug->enter_sub;
1169 my $dbh = $params{dbh} || $::form->get_standard_dbh;
1170 my $vc = $params{db} eq 'customer' ? 'customer' : 'vendor';
1173 'cp_name' => 'cp_name, cp_givenname',
1174 'vcname' => 'vcname, cp_name, cp_givenname',
1175 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
1178 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);
1180 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
1181 $::form->{sort} = $order_by;
1182 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
1184 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
1185 $order_by =~ s/,/ ${sortdir},/g;
1186 $order_by .= " $sortdir";
1188 my @where_tokens = ();
1191 if ($params{search_term}) {
1194 'cp.cp_name ILIKE ?',
1195 'cp.cp_givenname ILIKE ?',
1196 'cp.cp_email ILIKE ?';
1197 push @values, ('%' . $params{search_term} . '%') x 3;
1199 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
1200 my $number = $params{search_term};
1201 $number =~ s/[^\d]//g;
1202 $number = join '[ /\(\)+\-]*', split(m//, $number);
1204 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
1207 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
1210 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
1211 'trans_id_field' => 'cp.cp_id',
1212 'filter' => $params{filter});
1215 push @where_tokens, $cvar_where;
1216 push @values, @cvar_values;
1219 if (my $filter = $params{filter}) {
1220 for (qw(name title givenname email project abteilung)) {
1221 next unless $filter->{"cp_$_"};
1222 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
1225 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
1226 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
1229 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
1231 my $query = qq|SELECT cp.*,
1232 COALESCE(c.id, v.id) AS vcid,
1233 COALESCE(c.name, v.name) AS vcname,
1234 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
1235 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
1237 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
1238 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
1240 ORDER BY $order_by|;
1242 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
1244 $::lxdebug->leave_sub;
1246 return @{ $contacts };