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 );
371 $form->{cp_id} = $self->_save_contact($form, $dbh);
374 $form->add_shipto( $dbh, $form->{id}, "CT" );
376 $self->_save_note('dbh' => $dbh);
377 $self->_delete_selected_notes('dbh' => $dbh);
379 CVar->save_custom_variables('dbh' => $dbh,
381 'trans_id' => $form->{id},
382 'variables' => $form,
383 'always_valid' => 1);
384 if ($form->{cp_id}) {
385 CVar->save_custom_variables('dbh' => $dbh,
386 'module' => 'Contacts',
387 'trans_id' => $form->{cp_id},
388 'variables' => $form,
389 'name_prefix' => 'cp',
390 'always_valid' => 1);
393 my $rc = $dbh->commit();
395 $main::lxdebug->leave_sub();
400 $main::lxdebug->enter_sub();
402 my ( $self, $myconfig, $form ) = @_;
404 $form->{taxzone_id} *= 1;
405 # connect to database
406 my $dbh = $form->get_standard_dbh;
409 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
410 if ( $form->{"selected_cp_${_}"} );
411 } qw(title greeting abteilung) );
412 $form->{"greeting"} = $form->{"selected_company_greeting"}
413 if ( $form->{"selected_company_greeting"} );
415 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
416 $form->{discount} /= 100;
417 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
422 $query = qq|SELECT nextval('id')|;
423 ($form->{id}) = selectrow_query($form, $dbh, $query);
425 $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|;
426 do_query($form, $dbh, $query, $form->{id});
428 my $vendornumber = SL::TransNumber->new(type => 'vendor',
430 number => $form->{vendornumber},
432 $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique;
436 qq|UPDATE vendor SET | .
437 qq| vendornumber = ?, | .
439 qq| greeting = ?, | .
440 qq| department_1 = ?, | .
441 qq| department_2 = ?, | .
446 qq| homepage = ?, | .
455 qq| discount = ?, | .
456 qq| creditlimit = ?, | .
457 qq| business_id = ?, | .
458 qq| taxnumber = ?, | .
459 qq| language = ?, | .
460 qq| account_number = ?, | .
461 qq| bank_code = ?, | .
465 qq| obsolete = ?, | .
466 qq| direct_debit = ?, | .
468 qq| payment_id = ?, | .
469 qq| taxzone_id = ?, | .
470 qq| language_id = ?, | .
471 qq| username = ?, | .
472 qq| user_password = ?, | .
473 qq| v_customer_id = ?, | .
477 $form->{vendornumber},
480 $form->{department_1},
481 $form->{department_2},
494 conv_i($form->{terms}),
496 $form->{creditlimit},
497 conv_i($form->{business}),
500 $form->{account_number},
505 $form->{obsolete} ? 't' : 'f',
506 $form->{direct_debit} ? 't' : 'f',
508 conv_i($form->{payment_id}),
509 conv_i($form->{taxzone_id}, 0),
510 conv_i( $form->{language_id}),
512 $form->{user_password},
513 $form->{v_customer_id},
514 substr($form->{currency}, 0, 3),
517 do_query($form, $dbh, $query, @values);
519 $form->{cp_id} = $self->_save_contact($form, $dbh);
522 $form->add_shipto( $dbh, $form->{id}, "CT" );
524 $self->_save_note('dbh' => $dbh);
525 $self->_delete_selected_notes('dbh' => $dbh);
527 CVar->save_custom_variables('dbh' => $dbh,
529 'trans_id' => $form->{id},
530 'variables' => $form,
531 'always_valid' => 1);
532 if ($form->{cp_id}) {
533 CVar->save_custom_variables('dbh' => $dbh,
534 'module' => 'Contacts',
535 'trans_id' => $form->{cp_id},
536 'variables' => $form,
537 'name_prefix' => 'cp',
538 'always_valid' => 1);
541 my $rc = $dbh->commit();
543 $main::lxdebug->leave_sub();
548 my ($self, $form, $dbh) = @_;
550 return undef unless $form->{cp_id} || $form->{cp_name} || $form->{cp_givenname};
552 my @columns = qw(cp_title cp_givenname cp_name cp_email cp_phone1 cp_phone2 cp_abteilung cp_fax
553 cp_mobile1 cp_mobile2 cp_satphone cp_satfax cp_project cp_privatphone cp_privatemail cp_birthday cp_gender
554 cp_street cp_zipcode cp_city cp_position);
557 if ( $_ eq 'cp_gender' ) {
558 $form->{$_} eq 'f' ? 'f' : 'm';
559 } elsif ( $_ eq 'cp_birthday' && $form->{cp_birthday} eq '' ) {
569 if ($form->{cp_id}) {
570 $query = qq|UPDATE contacts SET | . join(', ', map { "${_} = ?" } @columns) . qq| WHERE cp_id = ?|;
571 push @values, $form->{cp_id};
572 $cp_id = $form->{cp_id};
575 ($cp_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
577 $query = qq|INSERT INTO contacts (| . join(', ', @columns, 'cp_cv_id', 'cp_id') . qq|) VALUES (| . join(', ', ('?') x (2 + scalar @columns)) . qq|)|;
578 push @values, $form->{id}, $cp_id;
581 do_query($form, $dbh, $query, @values);
587 $main::lxdebug->enter_sub();
589 my ( $self, $myconfig, $form ) = @_;
590 # connect to database
591 my $dbh = $form->dbconnect($myconfig);
594 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
595 my $query = qq|DELETE FROM $cv WHERE id = ?|;
596 do_query($form, $dbh, $query, $form->{id});
600 $main::lxdebug->leave_sub();
604 $main::lxdebug->enter_sub();
606 my ( $self, $myconfig, $form ) = @_;
608 # connect to database
609 my $dbh = $form->dbconnect($myconfig);
611 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
612 my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
617 my %allowed_sort_columns =
619 id customernumber vendornumber name contact phone fax email street
620 taxnumber business invnumber ordnumber quonumber zipcode city country salesman
622 my $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
623 $form->{sort} = $sortorder;
624 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
626 if ($sortorder !~ /(business|id)/ && !$join_records) {
627 $sortorder = "lower($sortorder) ${sortdir}";
629 $sortorder .= " ${sortdir}";
632 if ($form->{"${cv}number"}) {
633 $where .= " AND ct.${cv}number ILIKE ?";
634 push(@values, '%' . $form->{"${cv}number"} . '%');
637 foreach my $key (qw(name contact email)) {
639 $where .= " AND ct.$key ILIKE ?";
640 push(@values, '%' . $form->{$key} . '%');
644 if ($form->{cp_name}) {
645 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
646 push @values, '%' . $form->{cp_name} . '%';
649 if ($form->{addr_city}) {
650 $where .= " AND ((lower(ct.city) LIKE lower(?))
655 WHERE (module = 'CT')
656 AND (lower(shiptocity) LIKE lower(?))
659 push @values, ('%' . $form->{addr_city} . '%') x 2;
662 if ($form->{addr_country}) {
663 $where .= " AND ((lower(ct.country) LIKE lower(?))
668 WHERE (module = 'CT')
669 AND (lower(shiptocountry) LIKE lower(?))
672 push @values, ('%' . $form->{addr_country} . '%') x 2;
675 if ( $form->{status} eq 'orphaned' ) {
677 qq| AND ct.id NOT IN | .
678 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
679 if ($cv eq 'customer') {
681 qq| AND ct.id NOT IN | .
682 qq| (SELECT a.customer_id FROM ar a, customer cv | .
683 qq| WHERE cv.id = a.customer_id)|;
685 if ($cv eq 'vendor') {
687 qq| AND ct.id NOT IN | .
688 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
689 qq| WHERE cv.id = a.vendor_id)|;
691 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
694 if ($form->{obsolete} eq "Y") {
695 $where .= qq| AND obsolete|;
696 } elsif ($form->{obsolete} eq "N") {
697 $where .= qq| AND NOT obsolete|;
700 if ($form->{business_id}) {
701 $where .= qq| AND (business_id = ?)|;
702 push(@values, conv_i($form->{business_id}));
705 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
706 # Gilt nicht für Lieferanten
707 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
708 $where .= qq| AND ct.salesman_id = (select id from employee where login= ?)|;
709 push(@values, $form->{login});
712 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
713 'trans_id_field' => 'ct.id',
717 $where .= qq| AND ($cvar_where)|;
718 push @values, @cvar_values;
721 if ($form->{addr_street}) {
722 $where .= qq| AND (street ILIKE ?)|;
723 push @values, '%' . $form->{addr_street} . '%';
726 if ($form->{addr_zipcode}) {
727 $where .= qq| AND (zipcode ILIKE ?)|;
728 push @values, $form->{addr_zipcode} . '%';
732 qq|SELECT ct.*, b.description AS business, e.name as salesman | .
733 (qq|, NULL AS invnumber, NULL AS ordnumber, NULL AS quonumber, NULL AS invid, NULL AS module, NULL AS formtype, NULL AS closed | x!! $join_records) .
735 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
736 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
739 my @saved_values = @values;
740 # redo for invoices, orders and quotations
744 if ($form->{l_invnumber}) {
745 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
746 my $module = $ar eq 'ar' ? 'is' : 'ir';
747 push(@values, @saved_values);
750 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
751 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
752 qq| '$module' AS module, 'invoice' AS formtype, | .
753 qq| (a.amount = a.paid) AS closed | .
755 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
756 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
757 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
758 qq|WHERE $where AND (a.invoice = '1')|;
761 if ( $form->{l_ordnumber} ) {
762 push(@values, @saved_values);
765 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
766 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
767 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
769 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
770 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
771 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
772 qq|WHERE $where AND (o.quotation = '0')|;
775 if ( $form->{l_quonumber} ) {
776 push(@values, @saved_values);
779 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
780 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
781 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
783 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
784 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
785 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
786 qq|WHERE $where AND (o.quotation = '1')|;
790 $query .= qq| ORDER BY $sortorder|;
792 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
794 $main::lxdebug->leave_sub();
798 $main::lxdebug->enter_sub();
800 my ( $self, $myconfig, $form ) = @_;
802 die 'Missing argument: cp_id' unless $::form->{cp_id};
804 my $dbh = $form->dbconnect($myconfig);
806 qq|SELECT * FROM contacts c | .
807 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
808 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
809 my $ref = $sth->fetchrow_hashref("NAME_lc");
811 map { $form->{$_} = $ref->{$_} } keys %$ref;
813 $query = qq|SELECT COUNT(cp_id) AS used FROM (
814 SELECT cp_id FROM oe UNION
815 SELECT cp_id FROM ar UNION
816 SELECT cp_id FROM ap UNION
817 SELECT cp_id FROM delivery_orders
818 ) AS cpid WHERE cp_id = ? OR ? = 0|;
819 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
824 $main::lxdebug->leave_sub();
828 $main::lxdebug->enter_sub();
830 my ( $self, $myconfig, $form ) = @_;
831 my $dbh = $form->dbconnect($myconfig);
832 my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
833 my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id});
835 my $ref = $sth->fetchrow_hashref("NAME_lc");
837 map { $form->{$_} = $ref->{$_} } keys %$ref;
839 $query = qq|SELECT COUNT(shipto_id) AS used FROM (
840 SELECT shipto_id FROM oe UNION
841 SELECT shipto_id FROM ar UNION
842 SELECT shipto_id FROM delivery_orders
843 ) AS stid WHERE shipto_id = ? OR ? = 0|;
844 ($form->{shiptoused}) = selectfirst_array_query($form, $dbh, $query, ($form->{shipto_id})x2);
849 $main::lxdebug->leave_sub();
853 $main::lxdebug->enter_sub();
855 my ( $self, $myconfig, $form ) = @_;
856 my $dbh = $form->dbconnect($myconfig);
858 my $arap = $form->{db} eq "vendor" ? "ap" : "ar";
859 my $db = $form->{db} eq "customer" ? "customer" : "vendor";
860 my $qty_sign = $form->{db} eq 'vendor' ? ' * -1 AS qty' : '';
862 my $where = " WHERE 1=1 ";
865 if ($form->{shipto_id} && ($arap eq "ar")) {
866 $where .= "AND ${arap}.shipto_id = ?";
867 push(@values, $form->{shipto_id});
869 $where .= "AND ${arap}.${db}_id = ?";
870 push(@values, $form->{id});
874 $where .= "AND ${arap}.transdate >= ?";
875 push(@values, conv_date($form->{from}));
878 $where .= "AND ${arap}.transdate <= ?";
879 push(@values, conv_date($form->{to}));
882 qq|SELECT s.shiptoname, i.qty $qty_sign, | .
883 qq| ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | .
884 qq| i.description, i.unit, i.sellprice, | .
885 qq| oe.id AS oe_id, invoice | .
887 qq|LEFT JOIN shipto s ON | .
889 ? qq|(ar.shipto_id = s.shipto_id) |
890 : qq|(ap.id = s.trans_id) |) .
891 qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | .
892 qq|LEFT join parts p ON (p.id = i.parts_id) | .
893 qq|LEFT JOIN oe ON (oe.ordnumber = ${arap}.ordnumber AND NOT ${arap}.ordnumber = '') | .
895 qq|ORDER BY ${arap}.transdate DESC LIMIT 15|;
897 $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values);
901 $main::lxdebug->leave_sub();
905 $main::lxdebug->enter_sub();
910 my $form = $main::form;
912 Common::check_params(\%params, 'dbh');
914 if (!$form->{NOTE_subject}) {
915 $main::lxdebug->leave_sub();
919 my $dbh = $params{dbh};
923 'id' => $form->{NOTE_id},
924 'subject' => $form->{NOTE_subject},
925 'body' => $form->{NOTE_body},
926 'trans_id' => $form->{id},
927 'trans_module' => 'ct',
930 $note{id} = Notes->save(%note);
932 if ($form->{FU_date}) {
934 'id' => $form->{FU_id},
935 'note_id' => $note{id},
936 'follow_up_date' => $form->{FU_date},
937 'created_for_user' => $form->{FU_created_for_user},
938 'done' => $form->{FU_done} ? 1 : 0,
939 'subject' => $form->{NOTE_subject},
940 'body' => $form->{NOTE_body},
943 'trans_id' => $form->{id},
944 'trans_type' => $form->{db} eq 'customer' ? 'customer' : 'vendor',
945 'trans_info' => $form->{name},
950 $follow_up{id} = FU->save(%follow_up);
952 } elsif ($form->{FU_id}) {
953 do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($form->{FU_id}));
954 do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, conv_i($form->{FU_id}));
957 delete @{$form}{grep { /^NOTE_|^FU_/ } keys %{ $form }};
959 $main::lxdebug->leave_sub();
962 sub _delete_selected_notes {
963 $main::lxdebug->enter_sub();
968 Common::check_params(\%params, 'dbh');
970 my $form = $main::form;
971 my $dbh = $params{dbh};
973 foreach my $i (1 .. $form->{NOTES_rowcount}) {
974 next unless ($form->{"NOTE_delete_$i"} && $form->{"NOTE_id_$i"});
976 Notes->delete('dbh' => $params{dbh},
977 'id' => $form->{"NOTE_id_$i"});
980 $main::lxdebug->leave_sub();
983 # TODO: remove in 2.7.0 stable
985 $main::lxdebug->enter_sub();
988 my $shipto_id = shift;
990 my $form = $main::form;
991 my %myconfig = %main::myconfig;
992 my $dbh = $form->get_standard_dbh(\%myconfig);
994 do_query($form, $dbh, qq|UPDATE shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id);
998 $main::lxdebug->leave_sub();
1001 # TODO: remove in 2.7.0 stable
1002 sub delete_contact {
1003 $main::lxdebug->enter_sub();
1008 my $form = $main::form;
1009 my %myconfig = %main::myconfig;
1010 my $dbh = $form->get_standard_dbh(\%myconfig);
1012 do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $cp_id);
1016 $main::lxdebug->leave_sub();
1020 $main::lxdebug->enter_sub();
1025 Common::check_params(\%params, qw(vc id));
1027 my $myconfig = \%main::myconfig;
1028 my $form = $main::form;
1030 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1032 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
1033 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
1034 my $placeholders = join ", ", ('?') x scalar @ids;
1035 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic
1037 WHERE id IN (${placeholders})|;
1039 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
1041 if (ref $params{id} eq 'ARRAY') {
1042 $result = { map { $_->{id} => $_ } @{ $result } };
1044 $result = $result->[0] || { 'id' => $params{id} };
1047 $main::lxdebug->leave_sub();
1052 sub parse_excel_file {
1053 $main::lxdebug->enter_sub();
1055 my ($self, $myconfig, $form) = @_;
1056 my $locale = $main::locale;
1058 $form->{formname} = 'sales_quotation';
1059 $form->{type} = 'sales_quotation';
1060 $form->{format} = 'excel';
1061 $form->{media} = 'screen';
1062 $form->{quonumber} = 1;
1065 # $form->{"notes"} will be overridden by the customer's/vendor's "notes" field. So save it here.
1066 $form->{ $form->{"formname"} . "notes" } = $form->{"notes"};
1070 $form->{"${inv}date"} = $form->{transdate};
1071 $form->{label} = $locale->text('Quotation');
1072 my $numberfld = "sqnumber";
1076 $form->{what_done} = $form->{formname};
1078 map({ delete($form->{$_}); } grep(/^cp_/, keys(%{ $form })));
1080 my $output_dateformat = $myconfig->{"dateformat"};
1081 my $output_numberformat = $myconfig->{"numberformat"};
1082 my $output_longdates = 1;
1084 # map login user variables
1085 map { $form->{"login_$_"} = $myconfig->{$_} } ("name", "email", "fax", "tel", "company");
1088 for my $field (qw(transdate_oe deliverydate_oe)) {
1090 $form->{$field}[$_] = $locale->date($myconfig, $form->{$field}[$_], 1);
1091 } 0 .. $#{ $form->{$field} };
1094 if ($form->{shipto_id}) {
1095 $form->get_shipto($myconfig);
1098 $form->{notes} =~ s/^\s+//g;
1100 $form->{templates} = $myconfig->{templates};
1102 delete $form->{printer_command};
1104 $form->get_employee_info($myconfig);
1106 my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_');
1108 if (scalar @{ $cvar_date_fields }) {
1109 format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields });
1112 while (my ($precision, $field_list) = each %{ $cvar_number_fields }) {
1113 reformat_numbers($output_numberformat, $precision, @{ $field_list });
1117 my $extension = 'xls';
1119 $form->{IN} = "$form->{formname}.${extension}";
1121 delete $form->{OUT};
1123 $form->parse_template($myconfig);
1125 $main::lxdebug->leave_sub();
1128 sub search_contacts {
1129 $::lxdebug->enter_sub;
1134 my $dbh = $params{dbh} || $::form->get_standard_dbh;
1135 my $vc = $params{db} eq 'customer' ? 'customer' : 'vendor';
1138 'cp_name' => 'cp_name, cp_givenname',
1139 'vcname' => 'vcname, cp_name, cp_givenname',
1140 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
1143 my %sortcols = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email cp_street cp_zipcode cp_city cp_position vcname vcnumber);
1145 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
1146 $::form->{sort} = $order_by;
1147 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
1149 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
1150 $order_by =~ s/,/ ${sortdir},/g;
1151 $order_by .= " $sortdir";
1153 my @where_tokens = ();
1156 if ($params{search_term}) {
1159 'cp.cp_name ILIKE ?',
1160 'cp.cp_givenname ILIKE ?',
1161 'cp.cp_email ILIKE ?';
1162 push @values, ('%' . $params{search_term} . '%') x 3;
1164 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
1165 my $number = $params{search_term};
1166 $number =~ s/[^\d]//g;
1167 $number = join '[ /\(\)+\-]*', split(m//, $number);
1169 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
1172 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
1175 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
1176 'trans_id_field' => 'cp.cp_id',
1177 'filter' => $params{filter});
1180 push @where_tokens, $cvar_where;
1181 push @values, @cvar_values;
1184 if (my $filter = $params{filter}) {
1185 for (qw(name title givenname email project abteilung)) {
1186 next unless $filter->{"cp_$_"};
1187 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
1190 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
1191 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
1194 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
1196 my $query = qq|SELECT cp.*,
1197 COALESCE(c.id, v.id) AS vcid,
1198 COALESCE(c.name, v.name) AS vcname,
1199 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
1200 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
1202 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
1203 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
1205 ORDER BY $order_by|;
1207 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
1209 $::lxdebug->leave_sub;
1211 return @{ $contacts };