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 #======================================================================
53 $main::lxdebug->enter_sub();
55 my ( $self, $myconfig, $form ) = @_;
57 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
59 my $dbh = $form->dbconnect($myconfig);
61 qq|SELECT ct.*, b.id AS business, cp.* | .
63 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
64 qq|LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) | .
65 qq|WHERE (ct.id = ?) | .
66 qq|ORDER BY cp.cp_id LIMIT 1|;
67 my $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
69 my $ref = $sth->fetchrow_hashref("NAME_lc");
71 map { $form->{$_} = $ref->{$_} } keys %$ref;
74 #get name of currency instead of id:
75 $query = qq|SELECT name AS curr FROM currencies WHERE id=?|;
76 ($form->{curr}) = selectrow_query($form, $dbh, $query, conv_i($form->{currency_id}));
78 if ( $form->{salesman_id} ) {
80 qq|SELECT ct.name AS salesman | .
84 selectrow_query($form, $dbh, $query, $form->{salesman_id});
87 my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $form->{login});
89 qq|SELECT n.*, n.itime::DATE AS created_on,
90 e.name AS created_by_name, e.login AS created_by_login
92 LEFT JOIN employee e ON (n.created_by = e.id)
93 WHERE (n.trans_id = ?) AND (n.trans_module = 'ct')|;
94 $form->{NOTES} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
97 qq|SELECT fu.follow_up_date, fu.done AS follow_up_done, e.name AS created_for_name, e.name AS created_for_login
99 LEFT JOIN employee e ON (fu.created_for_user = e.id)
100 WHERE (fu.note_id = ?)
101 AND NOT COALESCE(fu.done, FALSE)
102 AND ( (fu.created_by = ?)
103 OR (fu.created_by IN (SELECT DISTINCT what FROM follow_up_access WHERE who = ?)))|;
104 $sth = prepare_query($form, $dbh, $query);
106 foreach my $note (@{ $form->{NOTES} }) {
107 do_statement($form, $sth, $query, conv_i($note->{id}), conv_i($note->{created_by}), conv_i($employee_id));
108 $ref = $sth->fetchrow_hashref();
110 map { $note->{$_} = $ref->{$_} } keys %{ $ref } if ($ref);
115 if ($form->{edit_note_id}) {
117 qq|SELECT n.id AS NOTE_id, n.subject AS NOTE_subject, n.body AS NOTE_body,
118 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
120 LEFT JOIN follow_ups fu ON ((n.id = fu.note_id) AND NOT COALESCE(fu.done, FALSE))
122 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{edit_note_id}));
125 foreach my $key (keys %{ $ref }) {
127 $new_key =~ s/^([^_]+)/\U$1\E/;
128 $form->{$new_key} = $ref->{$key};
133 # check if it is orphaned
134 my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
137 if ($form->{db} eq 'vendor') {
138 $makemodel = qq| UNION SELECT 1 FROM makemodel mm WHERE mm.make = ?|;
145 qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
146 qq|WHERE ct.id = ? | .
150 qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
153 my ($dummy) = selectrow_query($form, $dbh, $query, (conv_i($form->{id})) x $num_args);
155 $form->{status} = "orphaned" unless ($dummy);
159 $main::lxdebug->leave_sub();
162 sub populate_drop_down_boxes {
163 $main::lxdebug->enter_sub();
165 my ($self, $myconfig, $form, $provided_dbh) = @_;
168 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig);
171 $query = qq|SELECT id, description FROM business ORDER BY id|;
172 $form->{all_business} = selectall_hashref_query($form, $dbh, $query);
176 qq|SELECT shipto_id, shiptoname, shiptodepartment_1, shiptostreet, shiptocity
178 WHERE (trans_id = ?) AND (module = 'CT')|;
179 $form->{SHIPTO} = selectall_hashref_query($form, $dbh, $query, $form->{id});
182 $query = qq|SELECT cp_id, cp_name, cp_givenname FROM contacts WHERE cp_cv_id = ? ORDER BY cp_name|;
183 $form->{CONTACTS} = selectall_hashref_query($form, $dbh, $query, $form->{id});
186 $query = qq|SELECT id, description FROM language ORDER BY id|;
187 $form->{languages} = selectall_hashref_query($form, $dbh, $query);
190 $query = qq|SELECT id, description FROM payment_terms ORDER BY sortkey|;
191 $form->{payment_terms} = selectall_hashref_query($form, $dbh, $query);
193 $dbh->disconnect() unless ($provided_dbh);
195 $main::lxdebug->leave_sub();
198 sub query_titles_and_greetings {
199 $main::lxdebug->enter_sub();
201 my ( $self, $myconfig, $form ) = @_;
202 my ( %tmp, $ref, $query );
204 my $dbh = $form->dbconnect($myconfig);
207 qq|SELECT DISTINCT(greeting) | .
209 qq|WHERE greeting ~ '[a-zA-Z]' | .
211 qq|SELECT DISTINCT(greeting) | .
213 qq|WHERE greeting ~ '[a-zA-Z]' | .
214 qq|ORDER BY greeting|;
216 map({ $tmp{$_} = 1; } selectall_array_query($form, $dbh, $query));
217 $form->{COMPANY_GREETINGS} = [ sort(keys(%tmp)) ];
220 qq|SELECT DISTINCT(cp_title) | .
222 qq|WHERE cp_title ~ '[a-zA-Z]'|;
223 $form->{TITLES} = [ selectall_array_query($form, $dbh, $query) ];
226 qq|SELECT DISTINCT(cp_abteilung) | .
228 qq|WHERE cp_abteilung ~ '[a-zA-Z]'|;
229 $form->{DEPARTMENT} = [ selectall_array_query($form, $dbh, $query) ];
232 $main::lxdebug->leave_sub();
236 $main::lxdebug->enter_sub();
238 my ( $self, $myconfig, $form ) = @_;
240 # set pricegroup to default
241 $form->{klass} = 0 unless ($form->{klass});
243 # connect to database
244 my $dbh = $form->get_standard_dbh;
247 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
248 if ( $form->{"selected_cp_${_}"} );
249 } qw(title greeting abteilung) );
250 $form->{"greeting"} = $form->{"selected_company_greeting"}
251 if ( $form->{"selected_company_greeting"} );
253 # assign value discount, terms, creditlimit
254 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
255 $form->{discount} /= 100;
256 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
258 my ( $query, $sth, $f_id );
261 $query = qq|SELECT id FROM customer WHERE customernumber = ?|;
262 ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber});
264 if (($f_id ne $form->{id}) && ($f_id ne "")) {
265 $main::lxdebug->leave_sub();
270 my $customernumber = SL::TransNumber->new(type => 'customer',
272 number => $form->{customernumber},
273 business_id => $form->{business},
275 $form->{customernumber} = $customernumber->create_unique unless $customernumber->is_unique;
277 $query = qq|SELECT nextval('id')|;
278 ($form->{id}) = selectrow_query($form, $dbh, $query);
280 $query = qq|INSERT INTO customer (id, name, currency_id) VALUES (?, '', (SELECT currency_id FROM defaults))|;
281 do_query($form, $dbh, $query, $form->{id});
284 $query = qq|UPDATE customer SET | .
285 qq|customernumber = ?, | .
288 qq|department_1 = ?, | .
289 qq|department_2 = ?, | .
303 qq|creditlimit = ?, | .
305 qq|business_id = ?, | .
306 qq|taxnumber = ?, | .
308 qq|account_number = ?, | .
309 qq|bank_code = ?, | .
314 qq|direct_debit = ?, | .
317 qq|salesman_id = ?, | .
318 qq|language_id = ?, | .
319 qq|payment_id = ?, | .
320 qq|taxzone_id = ?, | .
321 qq|user_password = ?, | .
322 qq|c_vendor_id = ?, | .
324 qq|currency_id = (SELECT id FROM currencies WHERE name = ?), | .
325 qq|taxincluded_checked = ? | .
328 $form->{customernumber},
331 $form->{department_1},
332 $form->{department_2},
346 $form->{creditlimit},
347 conv_i($form->{terms}),
348 conv_i($form->{business}),
351 $form->{account_number},
356 $form->{obsolete} ? 't' : 'f',
357 $form->{direct_debit} ? 't' : 'f',
360 conv_i($form->{salesman_id}),
361 conv_i($form->{language_id}),
362 conv_i($form->{payment_id}),
363 conv_i($form->{taxzone_id}, 0),
364 $form->{user_password},
365 $form->{c_vendor_id},
366 conv_i($form->{klass}),
368 $form->{taxincluded_checked} ne '' ? $form->{taxincluded_checked} : undef,
371 do_query( $form, $dbh, $query, @values );
373 $form->{cp_id} = $self->_save_contact($form, $dbh);
376 $form->add_shipto( $dbh, $form->{id}, "CT" );
378 $self->_save_note('dbh' => $dbh);
379 $self->_delete_selected_notes('dbh' => $dbh);
381 CVar->save_custom_variables('dbh' => $dbh,
383 'trans_id' => $form->{id},
384 'variables' => $form,
385 'always_valid' => 1);
386 if ($form->{cp_id}) {
387 CVar->save_custom_variables('dbh' => $dbh,
388 'module' => 'Contacts',
389 'trans_id' => $form->{cp_id},
390 'variables' => $form,
391 'name_prefix' => 'cp',
392 'always_valid' => 1);
395 my $rc = $dbh->commit();
397 $main::lxdebug->leave_sub();
402 $main::lxdebug->enter_sub();
404 my ( $self, $myconfig, $form ) = @_;
406 $form->{taxzone_id} *= 1;
407 # connect to database
408 my $dbh = $form->get_standard_dbh;
411 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
412 if ( $form->{"selected_cp_${_}"} );
413 } qw(title greeting abteilung) );
414 $form->{"greeting"} = $form->{"selected_company_greeting"}
415 if ( $form->{"selected_company_greeting"} );
417 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
418 $form->{discount} /= 100;
419 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
424 $query = qq|SELECT nextval('id')|;
425 ($form->{id}) = selectrow_query($form, $dbh, $query);
427 $query = qq|INSERT INTO vendor (id, name, currency_id) VALUES (?, '', (SELECT currency_id FROM defaults))|;
428 do_query($form, $dbh, $query, $form->{id});
430 my $vendornumber = SL::TransNumber->new(type => 'vendor',
432 number => $form->{vendornumber},
434 $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique;
438 qq|UPDATE vendor SET | .
439 qq| vendornumber = ?, | .
441 qq| greeting = ?, | .
442 qq| department_1 = ?, | .
443 qq| department_2 = ?, | .
448 qq| homepage = ?, | .
457 qq| discount = ?, | .
458 qq| creditlimit = ?, | .
459 qq| business_id = ?, | .
460 qq| taxnumber = ?, | .
461 qq| language = ?, | .
462 qq| account_number = ?, | .
463 qq| bank_code = ?, | .
467 qq| obsolete = ?, | .
468 qq| direct_debit = ?, | .
470 qq| payment_id = ?, | .
471 qq| taxzone_id = ?, | .
472 qq| language_id = ?, | .
473 qq| username = ?, | .
474 qq| user_password = ?, | .
475 qq| v_customer_id = ?, | .
476 qq| currency_id = (SELECT id FROM currencies WHERE name = ?) | .
479 $form->{vendornumber},
482 $form->{department_1},
483 $form->{department_2},
496 conv_i($form->{terms}),
498 $form->{creditlimit},
499 conv_i($form->{business}),
502 $form->{account_number},
507 $form->{obsolete} ? 't' : 'f',
508 $form->{direct_debit} ? 't' : 'f',
510 conv_i($form->{payment_id}),
511 conv_i($form->{taxzone_id}, 0),
512 conv_i( $form->{language_id}),
514 $form->{user_password},
515 $form->{v_customer_id},
519 do_query($form, $dbh, $query, @values);
521 $form->{cp_id} = $self->_save_contact($form, $dbh);
524 $form->add_shipto( $dbh, $form->{id}, "CT" );
526 $self->_save_note('dbh' => $dbh);
527 $self->_delete_selected_notes('dbh' => $dbh);
529 CVar->save_custom_variables('dbh' => $dbh,
531 'trans_id' => $form->{id},
532 'variables' => $form,
533 'always_valid' => 1);
534 if ($form->{cp_id}) {
535 CVar->save_custom_variables('dbh' => $dbh,
536 'module' => 'Contacts',
537 'trans_id' => $form->{cp_id},
538 'variables' => $form,
539 'name_prefix' => 'cp',
540 'always_valid' => 1);
543 my $rc = $dbh->commit();
545 $main::lxdebug->leave_sub();
550 my ($self, $form, $dbh) = @_;
552 return undef unless $form->{cp_id} || $form->{cp_name} || $form->{cp_givenname};
554 my @columns = qw(cp_title cp_givenname cp_name cp_email cp_phone1 cp_phone2 cp_abteilung cp_fax
555 cp_mobile1 cp_mobile2 cp_satphone cp_satfax cp_project cp_privatphone cp_privatemail cp_birthday cp_gender
556 cp_street cp_zipcode cp_city cp_position);
559 if ( $_ eq 'cp_gender' ) {
560 $form->{$_} eq 'f' ? 'f' : 'm';
561 } elsif ( $_ eq 'cp_birthday' && $form->{cp_birthday} eq '' ) {
571 if ($form->{cp_id}) {
572 $query = qq|UPDATE contacts SET | . join(', ', map { "${_} = ?" } @columns) . qq| WHERE cp_id = ?|;
573 push @values, $form->{cp_id};
574 $cp_id = $form->{cp_id};
577 ($cp_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
579 $query = qq|INSERT INTO contacts (| . join(', ', @columns, 'cp_cv_id', 'cp_id') . qq|) VALUES (| . join(', ', ('?') x (2 + scalar @columns)) . qq|)|;
580 push @values, $form->{id}, $cp_id;
583 do_query($form, $dbh, $query, @values);
589 $main::lxdebug->enter_sub();
591 my ( $self, $myconfig, $form ) = @_;
592 # connect to database
593 my $dbh = $form->dbconnect($myconfig);
596 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
597 my $query = qq|DELETE FROM $cv WHERE id = ?|;
598 do_query($form, $dbh, $query, $form->{id});
602 $main::lxdebug->leave_sub();
606 $main::lxdebug->enter_sub();
608 my ( $self, $myconfig, $form ) = @_;
610 # connect to database
611 my $dbh = $form->dbconnect($myconfig);
613 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
614 my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
619 my %allowed_sort_columns = (
621 "customernumber" => "ct.customernumber",
622 "vendornumber" => "ct.vendornumber",
624 "contact" => "ct.contact",
625 "phone" => "ct.phone",
627 "email" => "ct.email",
628 "street" => "ct.street",
629 "taxnumber" => "ct.taxnumber",
630 "business" => "ct.business",
631 "invnumber" => "ct.invnumber",
632 "ordnumber" => "ct.ordnumber",
633 "quonumber" => "ct.quonumber",
634 "zipcode" => "ct.zipcode",
636 "country" => "ct.country",
637 "discount" => "ct.discount",
638 "salesman" => "e.name"
641 $form->{sort} ||= "name";
643 if ( $join_records ) {
644 # in UNION case order by hash key, e.g. salesman
645 # the UNION created an implicit select around the result
646 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
648 # in not UNION case order by hash value, e.g. e.name
649 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
651 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
653 if ($sortorder !~ /(business|id|discount)/ && !$join_records) {
654 $sortorder = "lower($sortorder) ${sortdir}";
656 $sortorder .= " ${sortdir}";
659 if ($form->{"${cv}number"}) {
660 $where .= " AND ct.${cv}number ILIKE ?";
661 push(@values, '%' . $form->{"${cv}number"} . '%');
664 foreach my $key (qw(name contact email)) {
666 $where .= " AND ct.$key ILIKE ?";
667 push(@values, '%' . $form->{$key} . '%');
671 if ($form->{cp_name}) {
672 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
673 push @values, '%' . $form->{cp_name} . '%';
676 if ($form->{addr_city}) {
677 $where .= " AND ((lower(ct.city) LIKE lower(?))
682 WHERE (sc.module = 'CT')
683 AND (lower(sc.shiptocity) LIKE lower(?))
686 push @values, ('%' . $form->{addr_city} . '%') x 2;
689 if ($form->{addr_country}) {
690 $where .= " AND ((lower(ct.country) LIKE lower(?))
695 WHERE (so.module = 'CT')
696 AND (lower(so.shiptocountry) LIKE lower(?))
699 push @values, ('%' . $form->{addr_country} . '%') x 2;
702 if ( $form->{status} eq 'orphaned' ) {
704 qq| AND ct.id NOT IN | .
705 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
706 if ($cv eq 'customer') {
708 qq| AND ct.id NOT IN | .
709 qq| (SELECT a.customer_id FROM ar a, customer cv | .
710 qq| WHERE cv.id = a.customer_id)|;
712 if ($cv eq 'vendor') {
714 qq| AND ct.id NOT IN | .
715 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
716 qq| WHERE cv.id = a.vendor_id)|;
718 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
721 if ($form->{obsolete} eq "Y") {
722 $where .= qq| AND ct.obsolete|;
723 } elsif ($form->{obsolete} eq "N") {
724 $where .= qq| AND NOT ct.obsolete|;
727 if ($form->{business_id}) {
728 $where .= qq| AND (ct.business_id = ?)|;
729 push(@values, conv_i($form->{business_id}));
732 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
733 # Gilt nicht für Lieferanten
734 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
735 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
736 push(@values, $form->{login});
739 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
740 'trans_id_field' => 'ct.id',
744 $where .= qq| AND ($cvar_where)|;
745 push @values, @cvar_values;
748 if ($form->{addr_street}) {
749 $where .= qq| AND (ct.street ILIKE ?)|;
750 push @values, '%' . $form->{addr_street} . '%';
753 if ($form->{addr_zipcode}) {
754 $where .= qq| AND (ct.zipcode ILIKE ?)|;
755 push @values, $form->{addr_zipcode} . '%';
759 qq|SELECT ct.*, b.description AS business, e.name as salesman | .
760 (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) .
762 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
763 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
766 my @saved_values = @values;
767 # redo for invoices, orders and quotations
771 if ($form->{l_invnumber}) {
772 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
773 my $module = $ar eq 'ar' ? 'is' : 'ir';
774 push(@values, @saved_values);
777 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
778 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
779 qq| '$module' AS module, 'invoice' AS formtype, | .
780 qq| (a.amount = a.paid) AS closed | .
782 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
783 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
784 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
785 qq|WHERE $where AND (a.invoice = '1')|;
788 if ( $form->{l_ordnumber} ) {
789 push(@values, @saved_values);
792 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
793 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
794 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
796 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
797 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
798 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
799 qq|WHERE $where AND (o.quotation = '0')|;
802 if ( $form->{l_quonumber} ) {
803 push(@values, @saved_values);
806 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
807 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
808 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
810 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
811 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
812 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
813 qq|WHERE $where AND (o.quotation = '1')|;
817 $query .= qq| ORDER BY $sortorder|;
819 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
821 $main::lxdebug->leave_sub();
825 $main::lxdebug->enter_sub();
827 my ( $self, $myconfig, $form ) = @_;
829 die 'Missing argument: cp_id' unless $::form->{cp_id};
831 my $dbh = $form->dbconnect($myconfig);
833 qq|SELECT * FROM contacts c | .
834 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
835 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
836 my $ref = $sth->fetchrow_hashref("NAME_lc");
838 map { $form->{$_} = $ref->{$_} } keys %$ref;
840 $query = qq|SELECT COUNT(cp_id) AS used FROM (
841 SELECT cp_id FROM oe UNION
842 SELECT cp_id FROM ar UNION
843 SELECT cp_id FROM ap UNION
844 SELECT cp_id FROM delivery_orders
845 ) AS cpid WHERE cp_id = ? OR ? = 0|;
846 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
851 $main::lxdebug->leave_sub();
855 $main::lxdebug->enter_sub();
857 my ( $self, $myconfig, $form ) = @_;
858 my $dbh = $form->dbconnect($myconfig);
859 my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
860 my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id});
862 my $ref = $sth->fetchrow_hashref("NAME_lc");
864 map { $form->{$_} = $ref->{$_} } keys %$ref;
866 $query = qq|SELECT COUNT(shipto_id) AS used FROM (
867 SELECT shipto_id FROM oe UNION
868 SELECT shipto_id FROM ar UNION
869 SELECT shipto_id FROM delivery_orders
870 ) AS stid WHERE shipto_id = ? OR ? = 0|;
871 ($form->{shiptoused}) = selectfirst_array_query($form, $dbh, $query, ($form->{shipto_id})x2);
876 $main::lxdebug->leave_sub();
880 $main::lxdebug->enter_sub();
882 my ( $self, $myconfig, $form ) = @_;
883 my $dbh = $form->dbconnect($myconfig);
885 my $arap = $form->{db} eq "vendor" ? "ap" : "ar";
886 my $db = $form->{db} eq "customer" ? "customer" : "vendor";
887 my $qty_sign = $form->{db} eq 'vendor' ? ' * -1 AS qty' : '';
889 my $where = " WHERE 1=1 ";
892 if ($form->{shipto_id} && ($arap eq "ar")) {
893 $where .= "AND ${arap}.shipto_id = ?";
894 push(@values, $form->{shipto_id});
896 $where .= "AND ${arap}.${db}_id = ?";
897 push(@values, $form->{id});
901 $where .= "AND ${arap}.transdate >= ?";
902 push(@values, conv_date($form->{from}));
905 $where .= "AND ${arap}.transdate <= ?";
906 push(@values, conv_date($form->{to}));
909 qq|SELECT s.shiptoname, i.qty $qty_sign, | .
910 qq| ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | .
911 qq| i.description, i.unit, i.sellprice, | .
912 qq| oe.id AS oe_id, invoice | .
914 qq|LEFT JOIN shipto s ON | .
916 ? qq|(ar.shipto_id = s.shipto_id) |
917 : qq|(ap.id = s.trans_id) |) .
918 qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | .
919 qq|LEFT join parts p ON (p.id = i.parts_id) | .
920 qq|LEFT JOIN oe ON (oe.ordnumber = ${arap}.ordnumber AND NOT ${arap}.ordnumber = '') | .
922 qq|ORDER BY ${arap}.transdate DESC LIMIT 15|;
924 $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values);
928 $main::lxdebug->leave_sub();
932 $main::lxdebug->enter_sub();
937 my $form = $main::form;
939 Common::check_params(\%params, 'dbh');
941 if (!$form->{NOTE_subject}) {
942 $main::lxdebug->leave_sub();
946 my $dbh = $params{dbh};
950 'id' => $form->{NOTE_id},
951 'subject' => $form->{NOTE_subject},
952 'body' => $form->{NOTE_body},
953 'trans_id' => $form->{id},
954 'trans_module' => 'ct',
957 $note{id} = Notes->save(%note);
959 if ($form->{FU_date}) {
961 'id' => $form->{FU_id},
962 'note_id' => $note{id},
963 'follow_up_date' => $form->{FU_date},
964 'created_for_user' => $form->{FU_created_for_user},
965 'done' => $form->{FU_done} ? 1 : 0,
966 'subject' => $form->{NOTE_subject},
967 'body' => $form->{NOTE_body},
970 'trans_id' => $form->{id},
971 'trans_type' => $form->{db} eq 'customer' ? 'customer' : 'vendor',
972 'trans_info' => $form->{name},
977 $follow_up{id} = FU->save(%follow_up);
979 } elsif ($form->{FU_id}) {
980 do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($form->{FU_id}));
981 do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, conv_i($form->{FU_id}));
984 delete @{$form}{grep { /^NOTE_|^FU_/ } keys %{ $form }};
986 $main::lxdebug->leave_sub();
989 sub _delete_selected_notes {
990 $main::lxdebug->enter_sub();
995 Common::check_params(\%params, 'dbh');
997 my $form = $main::form;
998 my $dbh = $params{dbh};
1000 foreach my $i (1 .. $form->{NOTES_rowcount}) {
1001 next unless ($form->{"NOTE_delete_$i"} && $form->{"NOTE_id_$i"});
1003 Notes->delete('dbh' => $params{dbh},
1004 'id' => $form->{"NOTE_id_$i"});
1007 $main::lxdebug->leave_sub();
1010 # TODO: remove in 2.7.0 stable
1012 $main::lxdebug->enter_sub();
1015 my $shipto_id = shift;
1017 my $form = $main::form;
1018 my %myconfig = %main::myconfig;
1019 my $dbh = $form->get_standard_dbh(\%myconfig);
1021 do_query($form, $dbh, qq|UPDATE shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id);
1025 $main::lxdebug->leave_sub();
1028 # TODO: remove in 2.7.0 stable
1029 sub delete_contact {
1030 $main::lxdebug->enter_sub();
1035 my $form = $main::form;
1036 my %myconfig = %main::myconfig;
1037 my $dbh = $form->get_standard_dbh(\%myconfig);
1039 do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $cp_id);
1043 $main::lxdebug->leave_sub();
1047 $main::lxdebug->enter_sub();
1052 Common::check_params(\%params, qw(vc id));
1054 my $myconfig = \%main::myconfig;
1055 my $form = $main::form;
1057 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1059 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
1060 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
1061 my $placeholders = join ", ", ('?') x scalar @ids;
1062 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic
1064 WHERE id IN (${placeholders})|;
1066 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
1068 if (ref $params{id} eq 'ARRAY') {
1069 $result = { map { $_->{id} => $_ } @{ $result } };
1071 $result = $result->[0] || { 'id' => $params{id} };
1074 $main::lxdebug->leave_sub();
1079 sub parse_excel_file {
1080 $main::lxdebug->enter_sub();
1082 my ($self, $myconfig, $form) = @_;
1083 my $locale = $main::locale;
1085 my $defaults = SL::DB::Default->get;
1086 $form->error($::locale->text('No print templates have been created for this client yet. Please do so in the client configuration.')) if !$defaults->templates;
1087 $form->{templates} = $defaults->templates;
1089 $form->{formname} = 'sales_quotation';
1090 $form->{type} = 'sales_quotation';
1091 $form->{format} = 'excel';
1092 $form->{media} = 'screen';
1093 $form->{quonumber} = 1;
1096 # $form->{"notes"} will be overridden by the customer's/vendor's "notes" field. So save it here.
1097 $form->{ $form->{"formname"} . "notes" } = $form->{"notes"};
1101 $form->{"${inv}date"} = $form->{transdate};
1102 $form->{label} = $locale->text('Quotation');
1103 my $numberfld = "sqnumber";
1107 $form->{what_done} = $form->{formname};
1109 map({ delete($form->{$_}); } grep(/^cp_/, keys(%{ $form })));
1111 my $output_dateformat = $myconfig->{"dateformat"};
1112 my $output_numberformat = $myconfig->{"numberformat"};
1113 my $output_longdates = 1;
1115 # map login user variables
1116 map { $form->{"login_$_"} = $myconfig->{$_} } ("name", "email", "fax", "tel", "company");
1119 for my $field (qw(transdate_oe deliverydate_oe)) {
1121 $form->{$field}[$_] = $locale->date($myconfig, $form->{$field}[$_], 1);
1122 } 0 .. $#{ $form->{$field} };
1125 if ($form->{shipto_id}) {
1126 $form->get_shipto($myconfig);
1129 $form->{notes} =~ s/^\s+//g;
1131 delete $form->{printer_command};
1133 $form->get_employee_info($myconfig);
1135 my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_');
1137 if (scalar @{ $cvar_date_fields }) {
1138 format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields });
1141 while (my ($precision, $field_list) = each %{ $cvar_number_fields }) {
1142 reformat_numbers($output_numberformat, $precision, @{ $field_list });
1146 my $extension = 'xls';
1148 $form->{IN} = "$form->{formname}.${extension}";
1150 delete $form->{OUT};
1152 $form->parse_template($myconfig);
1154 $main::lxdebug->leave_sub();
1157 sub search_contacts {
1158 $::lxdebug->enter_sub;
1163 my $dbh = $params{dbh} || $::form->get_standard_dbh;
1166 'cp_name' => 'cp_name, cp_givenname',
1167 'vcname' => 'vcname, cp_name, cp_givenname',
1168 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
1171 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);
1173 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
1174 $::form->{sort} = $order_by;
1175 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
1177 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
1178 $order_by =~ s/,/ ${sortdir},/g;
1179 $order_by .= " $sortdir";
1181 my @where_tokens = ();
1184 if ($params{search_term}) {
1187 'cp.cp_name ILIKE ?',
1188 'cp.cp_givenname ILIKE ?',
1189 'cp.cp_email ILIKE ?';
1190 push @values, ('%' . $params{search_term} . '%') x 3;
1192 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
1193 my $number = $params{search_term};
1194 $number =~ s/[^\d]//g;
1195 $number = join '[ /\(\)+\-]*', split(m//, $number);
1197 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
1200 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
1203 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
1204 'trans_id_field' => 'cp.cp_id',
1205 'filter' => $params{filter});
1208 push @where_tokens, $cvar_where;
1209 push @values, @cvar_values;
1212 if (my $filter = $params{filter}) {
1213 for (qw(name title givenname email project abteilung)) {
1214 next unless $filter->{"cp_$_"};
1215 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
1218 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
1219 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
1222 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
1224 my $query = qq|SELECT cp.*,
1225 COALESCE(c.id, v.id) AS vcid,
1226 COALESCE(c.name, v.name) AS vcname,
1227 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
1228 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
1230 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
1231 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
1233 ORDER BY $order_by|;
1235 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
1237 $::lxdebug->leave_sub;
1239 return @{ $contacts };