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 if ($form->{cp_id}) {
452 CVar->save_custom_variables('dbh' => $dbh,
453 'module' => 'Contacts',
454 'trans_id' => $form->{cp_id},
455 'variables' => $form,
456 'name_prefix' => 'cp',
457 'always_valid' => 1);
460 my $rc = $dbh->commit();
462 $main::lxdebug->leave_sub();
467 $main::lxdebug->enter_sub();
469 my ( $self, $myconfig, $form ) = @_;
471 $form->{taxzone_id} *= 1;
472 # connect to database
473 my $dbh = $form->get_standard_dbh;
476 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
477 if ( $form->{"selected_cp_${_}"} );
478 } qw(title greeting abteilung) );
479 $form->{"greeting"} = $form->{"selected_company_greeting"}
480 if ( $form->{"selected_company_greeting"} );
482 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
483 $form->{discount} /= 100;
484 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
489 $query = qq|SELECT nextval('id')|;
490 ($form->{id}) = selectrow_query($form, $dbh, $query);
492 $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|;
493 do_query($form, $dbh, $query, $form->{id});
495 my $vendornumber = SL::TransNumber->new(type => 'vendor',
497 number => $form->{vendornumber},
499 $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique;
503 qq|UPDATE vendor SET | .
504 qq| vendornumber = ?, | .
506 qq| greeting = ?, | .
507 qq| department_1 = ?, | .
508 qq| department_2 = ?, | .
513 qq| homepage = ?, | .
522 qq| discount = ?, | .
523 qq| creditlimit = ?, | .
524 qq| business_id = ?, | .
525 qq| taxnumber = ?, | .
526 qq| language = ?, | .
527 qq| account_number = ?, | .
528 qq| bank_code = ?, | .
532 qq| obsolete = ?, | .
533 qq| direct_debit = ?, | .
535 qq| payment_id = ?, | .
536 qq| taxzone_id = ?, | .
537 qq| language_id = ?, | .
538 qq| username = ?, | .
539 qq| user_password = ?, | .
540 qq| v_customer_id = ?, | .
544 $form->{vendornumber},
547 $form->{department_1},
548 $form->{department_2},
561 conv_i($form->{terms}),
563 $form->{creditlimit},
564 conv_i($form->{business}),
567 $form->{account_number},
572 $form->{obsolete} ? 't' : 'f',
573 $form->{direct_debit} ? 't' : 'f',
575 conv_i($form->{payment_id}),
576 conv_i($form->{taxzone_id}, 0),
577 conv_i( $form->{language_id}),
579 $form->{user_password},
580 $form->{v_customer_id},
581 substr($form->{currency}, 0, 3),
584 do_query($form, $dbh, $query, @values);
587 if ( $form->{cp_id} ) {
588 $query = qq|UPDATE contacts SET | .
590 qq|cp_givenname = ?, | .
593 qq|cp_phone1 = ?, | .
594 qq|cp_phone2 = ?, | .
595 qq|cp_abteilung = ?, | .
597 qq|cp_mobile1 = ?, | .
598 qq|cp_mobile2 = ?, | .
599 qq|cp_satphone = ?, | .
600 qq|cp_satfax = ?, | .
601 qq|cp_project = ?, | .
602 qq|cp_privatphone = ?, | .
603 qq|cp_privatemail = ?, | .
604 qq|cp_birthday = ?, | .
609 $form->{cp_givenname},
614 $form->{cp_abteilung},
618 $form->{cp_satphone},
621 $form->{cp_privatphone},
622 $form->{cp_privatemail},
623 $form->{cp_birthday},
624 $form->{cp_gender} eq 'f' ? 'f' : 'm',
627 } elsif ( $form->{cp_name} || $form->{cp_givenname} ) {
629 qq|INSERT INTO contacts ( cp_cv_id, cp_title, cp_givenname, | .
630 qq| cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, | .
631 qq| cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, | .
632 qq| cp_birthday, cp_gender) | .
633 qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
637 $form->{cp_givenname},
642 $form->{cp_abteilung},
646 $form->{cp_satphone},
649 $form->{cp_privatphone},
650 $form->{cp_privatemail},
651 $form->{cp_birthday},
655 do_query($form, $dbh, $query, @values) if ($query);
658 $form->add_shipto( $dbh, $form->{id}, "CT" );
660 $self->_save_note('dbh' => $dbh);
661 $self->_delete_selected_notes('dbh' => $dbh);
663 CVar->save_custom_variables('dbh' => $dbh,
665 'trans_id' => $form->{id},
666 'variables' => $form,
667 'always_valid' => 1);
668 if ($form->{cp_id}) {
669 CVar->save_custom_variables('dbh' => $dbh,
670 'module' => 'Contacts',
671 'trans_id' => $form->{cp_id},
672 'variables' => $form,
673 'name_prefix' => 'cp',
674 'always_valid' => 1);
677 my $rc = $dbh->commit();
679 $main::lxdebug->leave_sub();
684 $main::lxdebug->enter_sub();
686 my ( $self, $myconfig, $form ) = @_;
687 # connect to database
688 my $dbh = $form->dbconnect($myconfig);
691 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
692 my $query = qq|DELETE FROM $cv WHERE id = ?|;
693 do_query($form, $dbh, $query, $form->{id});
697 $main::lxdebug->leave_sub();
701 $main::lxdebug->enter_sub();
703 my ( $self, $myconfig, $form ) = @_;
705 # connect to database
706 my $dbh = $form->dbconnect($myconfig);
708 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
713 my %allowed_sort_columns =
715 id customernumber vendornumber name contact phone fax email street
716 taxnumber business invnumber ordnumber quonumber zipcode city
718 my $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
719 $form->{sort} = $sortorder;
720 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
722 if ($sortorder !~ /(business|id)/ && 1 >= scalar grep { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber )) {
723 $sortorder = "lower($sortorder) ${sortdir}";
725 $sortorder .= " ${sortdir}";
728 if ($form->{"${cv}number"}) {
729 $where .= " AND ct.${cv}number ILIKE ?";
730 push(@values, '%' . $form->{"${cv}number"} . '%');
733 foreach my $key (qw(name contact email)) {
735 $where .= " AND ct.$key ILIKE ?";
736 push(@values, '%' . $form->{$key} . '%');
740 if ($form->{cp_name}) {
741 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
742 push @values, '%' . $form->{cp_name} . '%';
745 if ($form->{addr_city}) {
746 $where .= " AND ((lower(ct.city) LIKE lower(?))
751 WHERE (module = 'CT')
752 AND (lower(shiptocity) LIKE lower(?))
755 push @values, ('%' . $form->{addr_city} . '%') x 2;
758 if ( $form->{status} eq 'orphaned' ) {
760 qq| AND ct.id NOT IN | .
761 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
762 if ($cv eq 'customer') {
764 qq| AND ct.id NOT IN | .
765 qq| (SELECT a.customer_id FROM ar a, customer cv | .
766 qq| WHERE cv.id = a.customer_id)|;
768 if ($cv eq 'vendor') {
770 qq| AND ct.id NOT IN | .
771 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
772 qq| WHERE cv.id = a.vendor_id)|;
774 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
777 if ($form->{obsolete} eq "Y") {
778 $where .= qq| AND obsolete|;
779 } elsif ($form->{obsolete} eq "N") {
780 $where .= qq| AND NOT obsolete|;
783 if ($form->{business_id}) {
784 $where .= qq| AND (business_id = ?)|;
785 push(@values, conv_i($form->{business_id}));
788 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
789 # Gilt nicht für Lieferanten
790 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
791 $where .= qq| AND ct.salesman_id = (select id from employee where login= ?)|;
792 push(@values, $form->{login});
795 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
796 'trans_id_field' => 'ct.id',
800 $where .= qq| AND ($cvar_where)|;
801 push @values, @cvar_values;
804 if ($form->{addr_street}) {
805 $where .= qq| AND (street ILIKE ?)|;
806 push @values, '%' . $form->{addr_street} . '%';
809 if ($form->{addr_zipcode}) {
810 $where .= qq| AND (zipcode ILIKE ?)|;
811 push @values, $form->{addr_zipcode} . '%';
815 qq|SELECT ct.*, b.description AS business | .
817 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
820 my @saved_values = @values;
821 # redo for invoices, orders and quotations
822 if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
823 my ($ar, $union, $module);
826 if ($form->{l_invnumber}) {
827 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
828 my $module = $ar eq 'ar' ? 'is' : 'ir';
831 qq|SELECT ct.*, b.description AS business, | .
832 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
833 qq| '$module' AS module, 'invoice' AS formtype, | .
834 qq| (a.amount = a.paid) AS closed | .
836 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
837 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
838 qq|WHERE $where AND (a.invoice = '1')|;
843 if ( $form->{l_ordnumber} ) {
844 if ($union eq "UNION") {
845 push(@values, @saved_values);
849 qq|SELECT ct.*, b.description AS business,| .
850 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
851 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
853 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
854 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
855 qq|WHERE $where AND (o.quotation = '0')|;
860 if ( $form->{l_quonumber} ) {
861 if ($union eq "UNION") {
862 push(@values, @saved_values);
866 qq|SELECT ct.*, b.description AS business, | .
867 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
868 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
870 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
871 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
872 qq|WHERE $where AND (o.quotation = '1')|;
876 $query .= qq| ORDER BY $sortorder|;
878 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
880 $main::lxdebug->leave_sub();
884 $main::lxdebug->enter_sub();
886 my ( $self, $myconfig, $form ) = @_;
888 die 'Missing argument: cp_id' unless $::form->{cp_id};
890 my $dbh = $form->dbconnect($myconfig);
892 qq|SELECT * FROM contacts c | .
893 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
894 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
895 my $ref = $sth->fetchrow_hashref("NAME_lc");
897 map { $form->{$_} = $ref->{$_} } keys %$ref;
899 $query = qq|SELECT COUNT(cp_id) AS used FROM (
900 SELECT cp_id FROM oe UNION
901 SELECT cp_id FROM ar UNION
902 SELECT cp_id FROM ap UNION
903 SELECT cp_id FROM delivery_orders
904 ) AS cpid WHERE cp_id = ? OR ? = 0|;
905 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
910 $main::lxdebug->leave_sub();
914 $main::lxdebug->enter_sub();
916 my ( $self, $myconfig, $form ) = @_;
917 my $dbh = $form->dbconnect($myconfig);
918 my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
919 my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id});
921 my $ref = $sth->fetchrow_hashref("NAME_lc");
923 map { $form->{$_} = $ref->{$_} } keys %$ref;
925 $query = qq|SELECT COUNT(shipto_id) AS used FROM (
926 SELECT shipto_id FROM oe UNION
927 SELECT shipto_id FROM ar UNION
928 SELECT shipto_id FROM delivery_orders
929 ) AS stid WHERE shipto_id = ? OR ? = 0|;
930 ($form->{shiptoused}) = selectfirst_array_query($form, $dbh, $query, ($form->{shipto_id})x2);
935 $main::lxdebug->leave_sub();
939 $main::lxdebug->enter_sub();
941 my ( $self, $myconfig, $form ) = @_;
942 my $dbh = $form->dbconnect($myconfig);
944 my $arap = $form->{db} eq "vendor" ? "ap" : "ar";
945 my $db = $form->{db} eq "customer" ? "customer" : "vendor";
946 my $qty_sign = $form->{db} eq 'vendor' ? ' * -1 AS qty' : '';
948 my $where = " WHERE 1=1 ";
951 if ($form->{shipto_id} && ($arap eq "ar")) {
952 $where .= "AND ${arap}.shipto_id = ?";
953 push(@values, $form->{shipto_id});
955 $where .= "AND ${arap}.${db}_id = ?";
956 push(@values, $form->{id});
960 $where .= "AND ${arap}.transdate >= ?";
961 push(@values, conv_date($form->{from}));
964 $where .= "AND ${arap}.transdate <= ?";
965 push(@values, conv_date($form->{to}));
968 qq|SELECT s.shiptoname, i.qty $qty_sign, | .
969 qq| ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | .
970 qq| i.description, i.unit, i.sellprice, | .
971 qq| oe.id AS oe_id, invoice | .
973 qq|LEFT JOIN shipto s ON | .
975 ? qq|(ar.shipto_id = s.shipto_id) |
976 : qq|(ap.id = s.trans_id) |) .
977 qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | .
978 qq|LEFT join parts p ON (p.id = i.parts_id) | .
979 qq|LEFT JOIN oe ON (oe.ordnumber = ${arap}.ordnumber AND NOT ${arap}.ordnumber = '') | .
981 qq|ORDER BY ${arap}.transdate DESC LIMIT 15|;
983 $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values);
987 $main::lxdebug->leave_sub();
991 $main::lxdebug->enter_sub();
996 my $form = $main::form;
998 Common::check_params(\%params, 'dbh');
1000 if (!$form->{NOTE_subject}) {
1001 $main::lxdebug->leave_sub();
1005 my $dbh = $params{dbh};
1009 'id' => $form->{NOTE_id},
1010 'subject' => $form->{NOTE_subject},
1011 'body' => $form->{NOTE_body},
1012 'trans_id' => $form->{id},
1013 'trans_module' => 'ct',
1016 $note{id} = Notes->save(%note);
1018 if ($form->{FU_date}) {
1020 'id' => $form->{FU_id},
1021 'note_id' => $note{id},
1022 'follow_up_date' => $form->{FU_date},
1023 'created_for_user' => $form->{FU_created_for_user},
1024 'done' => $form->{FU_done} ? 1 : 0,
1025 'subject' => $form->{NOTE_subject},
1026 'body' => $form->{NOTE_body},
1029 'trans_id' => $form->{id},
1030 'trans_type' => $form->{db} eq 'customer' ? 'customer' : 'vendor',
1031 'trans_info' => $form->{name},
1036 $follow_up{id} = FU->save(%follow_up);
1038 } elsif ($form->{FU_id}) {
1039 do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($form->{FU_id}));
1040 do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, conv_i($form->{FU_id}));
1043 delete @{$form}{grep { /^NOTE_|^FU_/ } keys %{ $form }};
1045 $main::lxdebug->leave_sub();
1048 sub _delete_selected_notes {
1049 $main::lxdebug->enter_sub();
1054 Common::check_params(\%params, 'dbh');
1056 my $form = $main::form;
1057 my $dbh = $params{dbh};
1059 foreach my $i (1 .. $form->{NOTES_rowcount}) {
1060 next unless ($form->{"NOTE_delete_$i"} && $form->{"NOTE_id_$i"});
1062 Notes->delete('dbh' => $params{dbh},
1063 'id' => $form->{"NOTE_id_$i"});
1066 $main::lxdebug->leave_sub();
1069 # TODO: remove in 2.7.0 stable
1071 $main::lxdebug->enter_sub();
1074 my $shipto_id = shift;
1076 my $form = $main::form;
1077 my %myconfig = %main::myconfig;
1078 my $dbh = $form->get_standard_dbh(\%myconfig);
1080 do_query($form, $dbh, qq|UPDATE shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id);
1084 $main::lxdebug->leave_sub();
1087 # TODO: remove in 2.7.0 stable
1088 sub delete_contact {
1089 $main::lxdebug->enter_sub();
1094 my $form = $main::form;
1095 my %myconfig = %main::myconfig;
1096 my $dbh = $form->get_standard_dbh(\%myconfig);
1098 do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $cp_id);
1102 $main::lxdebug->leave_sub();
1106 $main::lxdebug->enter_sub();
1111 Common::check_params(\%params, qw(vc id));
1113 my $myconfig = \%main::myconfig;
1114 my $form = $main::form;
1116 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1118 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
1119 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
1120 my $placeholders = join ", ", ('?') x scalar @ids;
1121 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic
1123 WHERE id IN (${placeholders})|;
1125 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
1127 if (ref $params{id} eq 'ARRAY') {
1128 $result = { map { $_->{id} => $_ } @{ $result } };
1130 $result = $result->[0] || { 'id' => $params{id} };
1133 $main::lxdebug->leave_sub();
1138 sub parse_excel_file {
1139 $main::lxdebug->enter_sub();
1141 my ($self, $myconfig, $form) = @_;
1142 my $locale = $main::locale;
1144 $form->{formname} = 'sales_quotation';
1145 $form->{type} = 'sales_quotation';
1146 $form->{format} = 'excel';
1147 $form->{media} = 'screen';
1148 $form->{quonumber} = 1;
1151 # $form->{"notes"} will be overridden by the customer's/vendor's "notes" field. So save it here.
1152 $form->{ $form->{"formname"} . "notes" } = $form->{"notes"};
1156 $form->{"${inv}date"} = $form->{transdate};
1157 $form->{label} = $locale->text('Quotation');
1158 my $numberfld = "sqnumber";
1162 $form->{what_done} = $form->{formname};
1164 map({ delete($form->{$_}); } grep(/^cp_/, keys(%{ $form })));
1166 my $output_dateformat = $myconfig->{"dateformat"};
1167 my $output_numberformat = $myconfig->{"numberformat"};
1168 my $output_longdates = 1;
1170 # map login user variables
1171 map { $form->{"login_$_"} = $myconfig->{$_} } ("name", "email", "fax", "tel", "company");
1174 for my $field (qw(transdate_oe deliverydate_oe)) {
1176 $form->{$field}[$_] = $locale->date($myconfig, $form->{$field}[$_], 1);
1177 } 0 .. $#{ $form->{$field} };
1180 if ($form->{shipto_id}) {
1181 $form->get_shipto($myconfig);
1184 $form->{notes} =~ s/^\s+//g;
1186 $form->{templates} = $myconfig->{templates};
1188 delete $form->{printer_command};
1190 $form->get_employee_info($myconfig);
1192 my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_');
1194 if (scalar @{ $cvar_date_fields }) {
1195 format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields });
1198 while (my ($precision, $field_list) = each %{ $cvar_number_fields }) {
1199 reformat_numbers($output_numberformat, $precision, @{ $field_list });
1203 my $extension = 'xls';
1205 $form->{IN} = "$form->{formname}.${extension}";
1207 delete $form->{OUT};
1209 $form->parse_template($myconfig);
1211 $main::lxdebug->leave_sub();
1214 sub search_contacts {
1215 $::lxdebug->enter_sub;
1220 my $dbh = $params{dbh} || $::form->get_standard_dbh;
1221 my $vc = $params{db} eq 'customer' ? 'customer' : 'vendor';
1224 'cp_name' => 'cp_name, cp_givenname',
1225 'vcname' => 'vcname, cp_name, cp_givenname',
1226 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
1229 my %sortcols = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email vcname vcnumber);
1231 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
1232 $::form->{sort} = $order_by;
1233 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
1235 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
1236 $order_by =~ s/,/ ${sortdir},/g;
1237 $order_by .= " $sortdir";
1239 my @where_tokens = ();
1242 if ($params{search_term}) {
1245 'cp.cp_name ILIKE ?',
1246 'cp.cp_givenname ILIKE ?',
1247 'cp.cp_email ILIKE ?';
1248 push @values, ('%' . $params{search_term} . '%') x 3;
1250 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
1251 my $number = $params{search_term};
1252 $number =~ s/[^\d]//g;
1253 $number = join '[ /\(\)+\-]*', split(m//, $number);
1255 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
1258 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
1261 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
1262 'trans_id_field' => 'cp.cp_id',
1263 'filter' => $params{filter});
1266 push @where_tokens, $cvar_where;
1267 push @values, @cvar_values;
1270 if (my $filter = $params{filter}) {
1271 for (qw(name title givenname email project abteilung)) {
1272 next unless $filter->{"cp_$_"};
1273 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
1276 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
1277 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
1280 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
1282 my $query = qq|SELECT cp.*,
1283 COALESCE(c.id, v.id) AS vcid,
1284 COALESCE(c.name, v.name) AS vcname,
1285 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
1286 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
1288 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
1289 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
1291 ORDER BY $order_by|;
1293 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
1295 $::lxdebug->leave_sub;
1297 return @{ $contacts };