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 if ( $form->{cp_id} ) {
371 $query = qq|UPDATE contacts SET | .
373 qq|cp_givenname = ?, | .
376 qq|cp_phone1 = ?, | .
377 qq|cp_phone2 = ?, | .
378 qq|cp_abteilung = ?, | .
380 qq|cp_mobile1 = ?, | .
381 qq|cp_mobile2 = ?, | .
382 qq|cp_satphone = ?, | .
383 qq|cp_satfax = ?, | .
384 qq|cp_project = ?, | .
385 qq|cp_privatphone = ?, | .
386 qq|cp_privatemail = ?, | .
387 qq|cp_birthday = ?, | .
392 $form->{cp_givenname},
397 $form->{cp_abteilung},
401 $form->{cp_satphone},
404 $form->{cp_privatphone},
405 $form->{cp_privatemail},
406 $form->{cp_birthday},
407 $form->{cp_gender} eq 'f' ? 'f' : 'm',
410 } elsif ( $form->{cp_name} || $form->{cp_givenname} ) {
412 qq|INSERT INTO contacts ( cp_cv_id, cp_title, cp_givenname, | .
413 qq| cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, | .
414 qq| cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, | .
415 qq| cp_birthday, cp_gender) | .
416 qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
420 $form->{cp_givenname},
425 $form->{cp_abteilung},
429 $form->{cp_satphone},
432 $form->{cp_privatphone},
433 $form->{cp_privatemail},
434 $form->{cp_birthday},
435 $form->{cp_gender} eq 'f' ? 'f' : 'm',
438 do_query( $form, $dbh, $query, @values ) if ($query);
441 $form->add_shipto( $dbh, $form->{id}, "CT" );
443 $self->_save_note('dbh' => $dbh);
444 $self->_delete_selected_notes('dbh' => $dbh);
446 CVar->save_custom_variables('dbh' => $dbh,
448 'trans_id' => $form->{id},
449 'variables' => $form,
450 'always_valid' => 1);
451 CVar->save_custom_variables('dbh' => $dbh,
452 'module' => 'Contacts',
453 'trans_id' => $form->{cp_id},
454 'variables' => $form,
455 'name_prefix' => 'cp',
456 'always_valid' => 1);
458 my $rc = $dbh->commit();
460 $main::lxdebug->leave_sub();
465 $main::lxdebug->enter_sub();
467 my ( $self, $myconfig, $form ) = @_;
469 $form->{taxzone_id} *= 1;
470 # connect to database
471 my $dbh = $form->get_standard_dbh;
474 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
475 if ( $form->{"selected_cp_${_}"} );
476 } qw(title greeting abteilung) );
477 $form->{"greeting"} = $form->{"selected_company_greeting"}
478 if ( $form->{"selected_company_greeting"} );
480 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
481 $form->{discount} /= 100;
482 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
487 $query = qq|SELECT nextval('id')|;
488 ($form->{id}) = selectrow_query($form, $dbh, $query);
490 $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|;
491 do_query($form, $dbh, $query, $form->{id});
493 my $vendornumber = SL::TransNumber->new(type => 'vendor',
495 number => $form->{vendornumber},
497 $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique;
501 qq|UPDATE vendor SET | .
502 qq| vendornumber = ?, | .
504 qq| greeting = ?, | .
505 qq| department_1 = ?, | .
506 qq| department_2 = ?, | .
511 qq| homepage = ?, | .
520 qq| discount = ?, | .
521 qq| creditlimit = ?, | .
522 qq| business_id = ?, | .
523 qq| taxnumber = ?, | .
524 qq| language = ?, | .
525 qq| account_number = ?, | .
526 qq| bank_code = ?, | .
530 qq| obsolete = ?, | .
531 qq| direct_debit = ?, | .
533 qq| payment_id = ?, | .
534 qq| taxzone_id = ?, | .
535 qq| language_id = ?, | .
536 qq| username = ?, | .
537 qq| user_password = ?, | .
538 qq| v_customer_id = ?, | .
542 $form->{vendornumber},
545 $form->{department_1},
546 $form->{department_2},
559 conv_i($form->{terms}),
561 $form->{creditlimit},
562 conv_i($form->{business}),
565 $form->{account_number},
570 $form->{obsolete} ? 't' : 'f',
571 $form->{direct_debit} ? 't' : 'f',
573 conv_i($form->{payment_id}),
574 conv_i($form->{taxzone_id}, 0),
575 conv_i( $form->{language_id}),
577 $form->{user_password},
578 $form->{v_customer_id},
579 substr($form->{currency}, 0, 3),
582 do_query($form, $dbh, $query, @values);
585 if ( $form->{cp_id} ) {
586 $query = qq|UPDATE contacts SET | .
588 qq|cp_givenname = ?, | .
591 qq|cp_phone1 = ?, | .
592 qq|cp_phone2 = ?, | .
593 qq|cp_abteilung = ?, | .
595 qq|cp_mobile1 = ?, | .
596 qq|cp_mobile2 = ?, | .
597 qq|cp_satphone = ?, | .
598 qq|cp_satfax = ?, | .
599 qq|cp_project = ?, | .
600 qq|cp_privatphone = ?, | .
601 qq|cp_privatemail = ?, | .
602 qq|cp_birthday = ?, | .
607 $form->{cp_givenname},
612 $form->{cp_abteilung},
616 $form->{cp_satphone},
619 $form->{cp_privatphone},
620 $form->{cp_privatemail},
621 $form->{cp_birthday},
622 $form->{cp_gender} eq 'f' ? 'f' : 'm',
625 } elsif ( $form->{cp_name} || $form->{cp_givenname} ) {
627 qq|INSERT INTO contacts ( cp_cv_id, cp_title, cp_givenname, | .
628 qq| cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, | .
629 qq| cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, | .
630 qq| cp_birthday, cp_gender) | .
631 qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
635 $form->{cp_givenname},
640 $form->{cp_abteilung},
644 $form->{cp_satphone},
647 $form->{cp_privatphone},
648 $form->{cp_privatemail},
649 $form->{cp_birthday},
653 do_query($form, $dbh, $query, @values) if ($query);
656 $form->add_shipto( $dbh, $form->{id}, "CT" );
658 $self->_save_note('dbh' => $dbh);
659 $self->_delete_selected_notes('dbh' => $dbh);
661 CVar->save_custom_variables('dbh' => $dbh,
663 'trans_id' => $form->{id},
664 'variables' => $form,
665 'always_valid' => 1);
666 CVar->save_custom_variables('dbh' => $dbh,
667 'module' => 'Contacts',
668 'trans_id' => $form->{cp_id},
669 'variables' => $form,
670 'name_prefix' => 'cp',
671 'always_valid' => 1);
673 my $rc = $dbh->commit();
675 $main::lxdebug->leave_sub();
680 $main::lxdebug->enter_sub();
682 my ( $self, $myconfig, $form ) = @_;
683 # connect to database
684 my $dbh = $form->dbconnect($myconfig);
687 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
688 my $query = qq|DELETE FROM $cv WHERE id = ?|;
689 do_query($form, $dbh, $query, $form->{id});
693 $main::lxdebug->leave_sub();
697 $main::lxdebug->enter_sub();
699 my ( $self, $myconfig, $form ) = @_;
701 # connect to database
702 my $dbh = $form->dbconnect($myconfig);
704 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
709 my %allowed_sort_columns =
711 id customernumber vendornumber name contact phone fax email street
712 taxnumber business invnumber ordnumber quonumber zipcode city
714 my $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
715 $form->{sort} = $sortorder;
716 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
718 if ($sortorder !~ /(business|id)/ && 1 >= scalar grep { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber )) {
719 $sortorder = "lower($sortorder) ${sortdir}";
721 $sortorder .= " ${sortdir}";
724 if ($form->{"${cv}number"}) {
725 $where .= " AND ct.${cv}number ILIKE ?";
726 push(@values, '%' . $form->{"${cv}number"} . '%');
729 foreach my $key (qw(name contact email)) {
731 $where .= " AND ct.$key ILIKE ?";
732 push(@values, '%' . $form->{$key} . '%');
736 if ($form->{cp_name}) {
737 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
738 push @values, '%' . $form->{cp_name} . '%';
741 if ($form->{addr_city}) {
742 $where .= " AND ((lower(ct.city) LIKE lower(?))
747 WHERE (module = 'CT')
748 AND (lower(shiptocity) LIKE lower(?))
751 push @values, ('%' . $form->{addr_city} . '%') x 2;
754 if ( $form->{status} eq 'orphaned' ) {
756 qq| AND ct.id NOT IN | .
757 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
758 if ($cv eq 'customer') {
760 qq| AND ct.id NOT IN | .
761 qq| (SELECT a.customer_id FROM ar a, customer cv | .
762 qq| WHERE cv.id = a.customer_id)|;
764 if ($cv eq 'vendor') {
766 qq| AND ct.id NOT IN | .
767 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
768 qq| WHERE cv.id = a.vendor_id)|;
770 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
773 if ($form->{obsolete} eq "Y") {
774 $where .= qq| AND obsolete|;
775 } elsif ($form->{obsolete} eq "N") {
776 $where .= qq| AND NOT obsolete|;
779 if ($form->{business_id}) {
780 $where .= qq| AND (business_id = ?)|;
781 push(@values, conv_i($form->{business_id}));
784 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
785 # Gilt nicht für Lieferanten
786 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
787 $where .= qq| AND ct.salesman_id = (select id from employee where login= ?)|;
788 push(@values, $form->{login});
791 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
792 'trans_id_field' => 'ct.id',
796 $where .= qq| AND ($cvar_where)|;
797 push @values, @cvar_values;
800 if ($form->{addr_street}) {
801 $where .= qq| AND (street ILIKE ?)|;
802 push @values, '%' . $form->{addr_street} . '%';
805 if ($form->{addr_zipcode}) {
806 $where .= qq| AND (zipcode ILIKE ?)|;
807 push @values, $form->{addr_zipcode} . '%';
811 qq|SELECT ct.*, b.description AS business | .
813 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
816 my @saved_values = @values;
817 # redo for invoices, orders and quotations
818 if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
819 my ($ar, $union, $module);
822 if ($form->{l_invnumber}) {
823 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
824 my $module = $ar eq 'ar' ? 'is' : 'ir';
827 qq|SELECT ct.*, b.description AS business, | .
828 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
829 qq| '$module' AS module, 'invoice' AS formtype, | .
830 qq| (a.amount = a.paid) AS closed | .
832 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
833 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
834 qq|WHERE $where AND (a.invoice = '1')|;
839 if ( $form->{l_ordnumber} ) {
840 if ($union eq "UNION") {
841 push(@values, @saved_values);
845 qq|SELECT ct.*, b.description AS business,| .
846 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
847 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
849 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
850 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
851 qq|WHERE $where AND (o.quotation = '0')|;
856 if ( $form->{l_quonumber} ) {
857 if ($union eq "UNION") {
858 push(@values, @saved_values);
862 qq|SELECT ct.*, b.description AS business, | .
863 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
864 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
866 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
867 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
868 qq|WHERE $where AND (o.quotation = '1')|;
872 $query .= qq| ORDER BY $sortorder|;
874 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
876 $main::lxdebug->leave_sub();
880 $main::lxdebug->enter_sub();
882 my ( $self, $myconfig, $form ) = @_;
884 die 'Missing argument: cp_id' unless $::form->{cp_id};
886 my $dbh = $form->dbconnect($myconfig);
888 qq|SELECT * FROM contacts c | .
889 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
890 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
891 my $ref = $sth->fetchrow_hashref("NAME_lc");
893 map { $form->{$_} = $ref->{$_} } keys %$ref;
895 $query = qq|SELECT COUNT(cp_id) AS used FROM (
896 SELECT cp_id FROM oe UNION
897 SELECT cp_id FROM ar UNION
898 SELECT cp_id FROM ap UNION
899 SELECT cp_id FROM delivery_orders
900 ) AS cpid WHERE cp_id = ? OR ? = 0|;
901 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
906 $main::lxdebug->leave_sub();
910 $main::lxdebug->enter_sub();
912 my ( $self, $myconfig, $form ) = @_;
913 my $dbh = $form->dbconnect($myconfig);
914 my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
915 my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id});
917 my $ref = $sth->fetchrow_hashref("NAME_lc");
919 map { $form->{$_} = $ref->{$_} } keys %$ref;
921 $query = qq|SELECT COUNT(shipto_id) AS used FROM (
922 SELECT shipto_id FROM oe UNION
923 SELECT shipto_id FROM ar UNION
924 SELECT shipto_id FROM delivery_orders
925 ) AS stid WHERE shipto_id = ? OR ? = 0|;
926 ($form->{shiptoused}) = selectfirst_array_query($form, $dbh, $query, ($form->{shipto_id})x2);
931 $main::lxdebug->leave_sub();
935 $main::lxdebug->enter_sub();
937 my ( $self, $myconfig, $form ) = @_;
938 my $dbh = $form->dbconnect($myconfig);
940 my $arap = $form->{db} eq "vendor" ? "ap" : "ar";
941 my $db = $form->{db} eq "customer" ? "customer" : "vendor";
942 my $qty_sign = $form->{db} eq 'vendor' ? ' * -1 AS qty' : '';
944 my $where = " WHERE 1=1 ";
947 if ($form->{shipto_id} && ($arap eq "ar")) {
948 $where .= "AND ${arap}.shipto_id = ?";
949 push(@values, $form->{shipto_id});
951 $where .= "AND ${arap}.${db}_id = ?";
952 push(@values, $form->{id});
956 $where .= "AND ${arap}.transdate >= ?";
957 push(@values, conv_date($form->{from}));
960 $where .= "AND ${arap}.transdate <= ?";
961 push(@values, conv_date($form->{to}));
964 qq|SELECT s.shiptoname, i.qty $qty_sign, | .
965 qq| ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | .
966 qq| i.description, i.unit, i.sellprice, | .
967 qq| oe.id AS oe_id, invoice | .
969 qq|LEFT JOIN shipto s ON | .
971 ? qq|(ar.shipto_id = s.shipto_id) |
972 : qq|(ap.id = s.trans_id) |) .
973 qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | .
974 qq|LEFT join parts p ON (p.id = i.parts_id) | .
975 qq|LEFT JOIN oe ON (oe.ordnumber = ${arap}.ordnumber AND NOT ${arap}.ordnumber = '') | .
977 qq|ORDER BY ${arap}.transdate DESC LIMIT 15|;
979 $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values);
983 $main::lxdebug->leave_sub();
987 $main::lxdebug->enter_sub();
992 my $form = $main::form;
994 Common::check_params(\%params, 'dbh');
996 if (!$form->{NOTE_subject}) {
997 $main::lxdebug->leave_sub();
1001 my $dbh = $params{dbh};
1005 'id' => $form->{NOTE_id},
1006 'subject' => $form->{NOTE_subject},
1007 'body' => $form->{NOTE_body},
1008 'trans_id' => $form->{id},
1009 'trans_module' => 'ct',
1012 $note{id} = Notes->save(%note);
1014 if ($form->{FU_date}) {
1016 'id' => $form->{FU_id},
1017 'note_id' => $note{id},
1018 'follow_up_date' => $form->{FU_date},
1019 'created_for_user' => $form->{FU_created_for_user},
1020 'done' => $form->{FU_done} ? 1 : 0,
1021 'subject' => $form->{NOTE_subject},
1022 'body' => $form->{NOTE_body},
1025 'trans_id' => $form->{id},
1026 'trans_type' => $form->{db} eq 'customer' ? 'customer' : 'vendor',
1027 'trans_info' => $form->{name},
1032 $follow_up{id} = FU->save(%follow_up);
1034 } elsif ($form->{FU_id}) {
1035 do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($form->{FU_id}));
1036 do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, conv_i($form->{FU_id}));
1039 delete @{$form}{grep { /^NOTE_|^FU_/ } keys %{ $form }};
1041 $main::lxdebug->leave_sub();
1044 sub _delete_selected_notes {
1045 $main::lxdebug->enter_sub();
1050 Common::check_params(\%params, 'dbh');
1052 my $form = $main::form;
1053 my $dbh = $params{dbh};
1055 foreach my $i (1 .. $form->{NOTES_rowcount}) {
1056 next unless ($form->{"NOTE_delete_$i"} && $form->{"NOTE_id_$i"});
1058 Notes->delete('dbh' => $params{dbh},
1059 'id' => $form->{"NOTE_id_$i"});
1062 $main::lxdebug->leave_sub();
1065 # TODO: remove in 2.7.0 stable
1067 $main::lxdebug->enter_sub();
1070 my $shipto_id = shift;
1072 my $form = $main::form;
1073 my %myconfig = %main::myconfig;
1074 my $dbh = $form->get_standard_dbh(\%myconfig);
1076 do_query($form, $dbh, qq|UPDATE shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id);
1080 $main::lxdebug->leave_sub();
1083 # TODO: remove in 2.7.0 stable
1084 sub delete_contact {
1085 $main::lxdebug->enter_sub();
1090 my $form = $main::form;
1091 my %myconfig = %main::myconfig;
1092 my $dbh = $form->get_standard_dbh(\%myconfig);
1094 do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $cp_id);
1098 $main::lxdebug->leave_sub();
1102 $main::lxdebug->enter_sub();
1107 Common::check_params(\%params, qw(vc id));
1109 my $myconfig = \%main::myconfig;
1110 my $form = $main::form;
1112 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1114 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
1115 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
1116 my $placeholders = join ", ", ('?') x scalar @ids;
1117 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic
1119 WHERE id IN (${placeholders})|;
1121 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
1123 if (ref $params{id} eq 'ARRAY') {
1124 $result = { map { $_->{id} => $_ } @{ $result } };
1126 $result = $result->[0] || { 'id' => $params{id} };
1129 $main::lxdebug->leave_sub();
1134 sub parse_excel_file {
1135 $main::lxdebug->enter_sub();
1137 my ($self, $myconfig, $form) = @_;
1138 my $locale = $main::locale;
1140 $form->{formname} = 'sales_quotation';
1141 $form->{type} = 'sales_quotation';
1142 $form->{format} = 'excel';
1143 $form->{media} = 'screen';
1144 $form->{quonumber} = 1;
1147 # $form->{"notes"} will be overridden by the customer's/vendor's "notes" field. So save it here.
1148 $form->{ $form->{"formname"} . "notes" } = $form->{"notes"};
1152 $form->{"${inv}date"} = $form->{transdate};
1153 $form->{label} = $locale->text('Quotation');
1154 my $numberfld = "sqnumber";
1158 $form->{what_done} = $form->{formname};
1160 map({ delete($form->{$_}); } grep(/^cp_/, keys(%{ $form })));
1162 my $output_dateformat = $myconfig->{"dateformat"};
1163 my $output_numberformat = $myconfig->{"numberformat"};
1164 my $output_longdates = 1;
1166 # map login user variables
1167 map { $form->{"login_$_"} = $myconfig->{$_} } ("name", "email", "fax", "tel", "company");
1170 for my $field (qw(transdate_oe deliverydate_oe)) {
1172 $form->{$field}[$_] = $locale->date($myconfig, $form->{$field}[$_], 1);
1173 } 0 .. $#{ $form->{$field} };
1176 if ($form->{shipto_id}) {
1177 $form->get_shipto($myconfig);
1180 $form->{notes} =~ s/^\s+//g;
1182 $form->{templates} = $myconfig->{templates};
1184 delete $form->{printer_command};
1186 $form->get_employee_info($myconfig);
1188 my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_');
1190 if (scalar @{ $cvar_date_fields }) {
1191 format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields });
1194 while (my ($precision, $field_list) = each %{ $cvar_number_fields }) {
1195 reformat_numbers($output_numberformat, $precision, @{ $field_list });
1199 my $extension = 'xls';
1201 $form->{IN} = "$form->{formname}.${extension}";
1203 delete $form->{OUT};
1205 $form->parse_template($myconfig);
1207 $main::lxdebug->leave_sub();
1210 sub search_contacts {
1211 $::lxdebug->enter_sub;
1216 my $dbh = $params{dbh} || $::form->get_standard_dbh;
1217 my $vc = $params{db} eq 'customer' ? 'customer' : 'vendor';
1220 'cp_name' => 'cp_name, cp_givenname',
1221 'vcname' => 'vcname, cp_name, cp_givenname',
1222 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
1225 my %sortcols = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email vcname vcnumber);
1227 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
1228 $::form->{sort} = $order_by;
1229 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
1231 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
1232 $order_by =~ s/,/ ${sortdir},/g;
1233 $order_by .= " $sortdir";
1235 my @where_tokens = ();
1238 if ($params{search_term}) {
1241 'cp.cp_name ILIKE ?',
1242 'cp.cp_givenname ILIKE ?',
1243 'cp.cp_email ILIKE ?';
1244 push @values, ('%' . $params{search_term} . '%') x 3;
1246 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
1247 my $number = $params{search_term};
1248 $number =~ s/[^\d]//g;
1249 $number = join '[ /\(\)+\-]*', split(m//, $number);
1251 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
1254 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
1257 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
1258 'trans_id_field' => 'cp.cp_id',
1259 'filter' => $params{filter});
1262 push @where_tokens, $cvar_where;
1263 push @values, @cvar_values;
1266 if (my $filter = $params{filter}) {
1267 for (qw(name title givenname email project abteilung)) {
1268 next unless $filter->{"cp_$_"};
1269 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
1272 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
1273 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
1276 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
1278 my $query = qq|SELECT cp.*,
1279 COALESCE(c.id, v.id) AS vcid,
1280 COALESCE(c.name, v.name) AS vcname,
1281 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
1282 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
1284 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
1285 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
1287 ORDER BY $order_by|;
1289 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
1291 $::lxdebug->leave_sub;
1293 return @{ $contacts };