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;
73 #get name of currency instead of id:
74 $query = qq|SELECT name AS curr FROM currencies WHERE id=?|;
75 ($form->{curr}) = selectrow_query($form, $dbh, $query, conv_i($form->{curr}));
77 if ( $form->{salesman_id} ) {
79 qq|SELECT ct.name AS salesman | .
83 selectrow_query($form, $dbh, $query, $form->{salesman_id});
86 my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $form->{login});
88 qq|SELECT n.*, n.itime::DATE AS created_on,
89 e.name AS created_by_name, e.login AS created_by_login
91 LEFT JOIN employee e ON (n.created_by = e.id)
92 WHERE (n.trans_id = ?) AND (n.trans_module = 'ct')|;
93 $form->{NOTES} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
96 qq|SELECT fu.follow_up_date, fu.done AS follow_up_done, e.name AS created_for_name, e.name AS created_for_login
98 LEFT JOIN employee e ON (fu.created_for_user = e.id)
99 WHERE (fu.note_id = ?)
100 AND NOT COALESCE(fu.done, FALSE)
101 AND ( (fu.created_by = ?)
102 OR (fu.created_by IN (SELECT DISTINCT what FROM follow_up_access WHERE who = ?)))|;
103 $sth = prepare_query($form, $dbh, $query);
105 foreach my $note (@{ $form->{NOTES} }) {
106 do_statement($form, $sth, $query, conv_i($note->{id}), conv_i($note->{created_by}), conv_i($employee_id));
107 $ref = $sth->fetchrow_hashref();
109 map { $note->{$_} = $ref->{$_} } keys %{ $ref } if ($ref);
114 if ($form->{edit_note_id}) {
116 qq|SELECT n.id AS NOTE_id, n.subject AS NOTE_subject, n.body AS NOTE_body,
117 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
119 LEFT JOIN follow_ups fu ON ((n.id = fu.note_id) AND NOT COALESCE(fu.done, FALSE))
121 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{edit_note_id}));
124 foreach my $key (keys %{ $ref }) {
126 $new_key =~ s/^([^_]+)/\U$1\E/;
127 $form->{$new_key} = $ref->{$key};
132 # check if it is orphaned
133 my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
136 if ($form->{db} eq 'vendor') {
137 $makemodel = qq| UNION SELECT 1 FROM makemodel mm WHERE mm.make = ?|;
144 qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
145 qq|WHERE ct.id = ? | .
149 qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
152 my ($dummy) = selectrow_query($form, $dbh, $query, (conv_i($form->{id})) x $num_args);
154 $form->{status} = "orphaned" unless ($dummy);
158 $main::lxdebug->leave_sub();
161 sub populate_drop_down_boxes {
162 $main::lxdebug->enter_sub();
164 my ($self, $myconfig, $form, $provided_dbh) = @_;
167 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig);
170 $query = qq|SELECT id, description FROM business ORDER BY id|;
171 $form->{all_business} = selectall_hashref_query($form, $dbh, $query);
175 qq|SELECT shipto_id, shiptoname, shiptodepartment_1, shiptostreet, shiptocity
177 WHERE (trans_id = ?) AND (module = 'CT')|;
178 $form->{SHIPTO} = selectall_hashref_query($form, $dbh, $query, $form->{id});
181 $query = qq|SELECT cp_id, cp_name, cp_givenname FROM contacts WHERE cp_cv_id = ? ORDER BY cp_name|;
182 $form->{CONTACTS} = selectall_hashref_query($form, $dbh, $query, $form->{id});
185 $query = qq|SELECT id, description FROM language ORDER BY id|;
186 $form->{languages} = selectall_hashref_query($form, $dbh, $query);
189 $query = qq|SELECT id, description FROM payment_terms ORDER BY sortkey|;
190 $form->{payment_terms} = selectall_hashref_query($form, $dbh, $query);
192 $dbh->disconnect() unless ($provided_dbh);
194 $main::lxdebug->leave_sub();
197 sub query_titles_and_greetings {
198 $main::lxdebug->enter_sub();
200 my ( $self, $myconfig, $form ) = @_;
201 my ( %tmp, $ref, $query );
203 my $dbh = $form->dbconnect($myconfig);
206 qq|SELECT DISTINCT(greeting) | .
208 qq|WHERE greeting ~ '[a-zA-Z]' | .
210 qq|SELECT DISTINCT(greeting) | .
212 qq|WHERE greeting ~ '[a-zA-Z]' | .
213 qq|ORDER BY greeting|;
215 map({ $tmp{$_} = 1; } selectall_array_query($form, $dbh, $query));
216 $form->{COMPANY_GREETINGS} = [ sort(keys(%tmp)) ];
219 qq|SELECT DISTINCT(cp_title) | .
221 qq|WHERE cp_title ~ '[a-zA-Z]'|;
222 $form->{TITLES} = [ selectall_array_query($form, $dbh, $query) ];
225 qq|SELECT DISTINCT(cp_abteilung) | .
227 qq|WHERE cp_abteilung ~ '[a-zA-Z]'|;
228 $form->{DEPARTMENT} = [ selectall_array_query($form, $dbh, $query) ];
231 $main::lxdebug->leave_sub();
235 $main::lxdebug->enter_sub();
237 my ( $self, $myconfig, $form ) = @_;
239 # set pricegroup to default
240 $form->{klass} = 0 unless ($form->{klass});
242 # connect to database
243 my $dbh = $form->get_standard_dbh;
246 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
247 if ( $form->{"selected_cp_${_}"} );
248 } qw(title greeting abteilung) );
249 $form->{"greeting"} = $form->{"selected_company_greeting"}
250 if ( $form->{"selected_company_greeting"} );
252 # assign value discount, terms, creditlimit
253 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
254 $form->{discount} /= 100;
255 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
257 my ( $query, $sth, $f_id );
260 $query = qq|SELECT id FROM customer WHERE customernumber = ?|;
261 ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber});
263 if (($f_id ne $form->{id}) && ($f_id ne "")) {
264 $main::lxdebug->leave_sub();
269 my $customernumber = SL::TransNumber->new(type => 'customer',
271 number => $form->{customernumber},
272 business_id => $form->{business},
274 $form->{customernumber} = $customernumber->create_unique unless $customernumber->is_unique;
276 $query = qq|SELECT nextval('id')|;
277 ($form->{id}) = selectrow_query($form, $dbh, $query);
279 $query = qq|INSERT INTO customer (id, name) VALUES (?, '')|;
280 do_query($form, $dbh, $query, $form->{id});
283 $query = qq|UPDATE customer SET | .
284 qq|customernumber = ?, | .
287 qq|department_1 = ?, | .
288 qq|department_2 = ?, | .
302 qq|creditlimit = ?, | .
304 qq|business_id = ?, | .
305 qq|taxnumber = ?, | .
307 qq|account_number = ?, | .
308 qq|bank_code = ?, | .
313 qq|direct_debit = ?, | .
316 qq|salesman_id = ?, | .
317 qq|language_id = ?, | .
318 qq|payment_id = ?, | .
319 qq|taxzone_id = ?, | .
320 qq|user_password = ?, | .
321 qq|c_vendor_id = ?, | .
323 qq|currency_id = (SELECT id FROM currencies WHERE name = ?), | .
324 qq|taxincluded_checked = ? | .
327 $form->{customernumber},
330 $form->{department_1},
331 $form->{department_2},
345 $form->{creditlimit},
346 conv_i($form->{terms}),
347 conv_i($form->{business}),
350 $form->{account_number},
355 $form->{obsolete} ? 't' : 'f',
356 $form->{direct_debit} ? 't' : 'f',
359 conv_i($form->{salesman_id}),
360 conv_i($form->{language_id}),
361 conv_i($form->{payment_id}),
362 conv_i($form->{taxzone_id}, 0),
363 $form->{user_password},
364 $form->{c_vendor_id},
365 conv_i($form->{klass}),
367 $form->{taxincluded_checked} ne '' ? $form->{taxincluded_checked} : undef,
370 do_query( $form, $dbh, $query, @values );
372 $form->{cp_id} = $self->_save_contact($form, $dbh);
375 $form->add_shipto( $dbh, $form->{id}, "CT" );
377 $self->_save_note('dbh' => $dbh);
378 $self->_delete_selected_notes('dbh' => $dbh);
380 CVar->save_custom_variables('dbh' => $dbh,
382 'trans_id' => $form->{id},
383 'variables' => $form,
384 'always_valid' => 1);
385 if ($form->{cp_id}) {
386 CVar->save_custom_variables('dbh' => $dbh,
387 'module' => 'Contacts',
388 'trans_id' => $form->{cp_id},
389 'variables' => $form,
390 'name_prefix' => 'cp',
391 'always_valid' => 1);
394 my $rc = $dbh->commit();
396 $main::lxdebug->leave_sub();
401 $main::lxdebug->enter_sub();
403 my ( $self, $myconfig, $form ) = @_;
405 $form->{taxzone_id} *= 1;
406 # connect to database
407 my $dbh = $form->get_standard_dbh;
410 $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
411 if ( $form->{"selected_cp_${_}"} );
412 } qw(title greeting abteilung) );
413 $form->{"greeting"} = $form->{"selected_company_greeting"}
414 if ( $form->{"selected_company_greeting"} );
416 $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
417 $form->{discount} /= 100;
418 $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
423 $query = qq|SELECT nextval('id')|;
424 ($form->{id}) = selectrow_query($form, $dbh, $query);
426 $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|;
427 do_query($form, $dbh, $query, $form->{id});
429 my $vendornumber = SL::TransNumber->new(type => 'vendor',
431 number => $form->{vendornumber},
433 $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique;
437 qq|UPDATE vendor SET | .
438 qq| vendornumber = ?, | .
440 qq| greeting = ?, | .
441 qq| department_1 = ?, | .
442 qq| department_2 = ?, | .
447 qq| homepage = ?, | .
456 qq| discount = ?, | .
457 qq| creditlimit = ?, | .
458 qq| business_id = ?, | .
459 qq| taxnumber = ?, | .
460 qq| language = ?, | .
461 qq| account_number = ?, | .
462 qq| bank_code = ?, | .
466 qq| obsolete = ?, | .
467 qq| direct_debit = ?, | .
469 qq| payment_id = ?, | .
470 qq| taxzone_id = ?, | .
471 qq| language_id = ?, | .
472 qq| username = ?, | .
473 qq| user_password = ?, | .
474 qq| v_customer_id = ?, | .
475 qq| currency_id = (SELECT id FROM currencies WHERE name = ?) | .
478 $form->{vendornumber},
481 $form->{department_1},
482 $form->{department_2},
495 conv_i($form->{terms}),
497 $form->{creditlimit},
498 conv_i($form->{business}),
501 $form->{account_number},
506 $form->{obsolete} ? 't' : 'f',
507 $form->{direct_debit} ? 't' : 'f',
509 conv_i($form->{payment_id}),
510 conv_i($form->{taxzone_id}, 0),
511 conv_i( $form->{language_id}),
513 $form->{user_password},
514 $form->{v_customer_id},
518 do_query($form, $dbh, $query, @values);
520 $form->{cp_id} = $self->_save_contact($form, $dbh);
523 $form->add_shipto( $dbh, $form->{id}, "CT" );
525 $self->_save_note('dbh' => $dbh);
526 $self->_delete_selected_notes('dbh' => $dbh);
528 CVar->save_custom_variables('dbh' => $dbh,
530 'trans_id' => $form->{id},
531 'variables' => $form,
532 'always_valid' => 1);
533 if ($form->{cp_id}) {
534 CVar->save_custom_variables('dbh' => $dbh,
535 'module' => 'Contacts',
536 'trans_id' => $form->{cp_id},
537 'variables' => $form,
538 'name_prefix' => 'cp',
539 'always_valid' => 1);
542 my $rc = $dbh->commit();
544 $main::lxdebug->leave_sub();
549 my ($self, $form, $dbh) = @_;
551 return undef unless $form->{cp_id} || $form->{cp_name} || $form->{cp_givenname};
553 my @columns = qw(cp_title cp_givenname cp_name cp_email cp_phone1 cp_phone2 cp_abteilung cp_fax
554 cp_mobile1 cp_mobile2 cp_satphone cp_satfax cp_project cp_privatphone cp_privatemail cp_birthday cp_gender
555 cp_street cp_zipcode cp_city cp_position);
558 if ( $_ eq 'cp_gender' ) {
559 $form->{$_} eq 'f' ? 'f' : 'm';
560 } elsif ( $_ eq 'cp_birthday' && $form->{cp_birthday} eq '' ) {
570 if ($form->{cp_id}) {
571 $query = qq|UPDATE contacts SET | . join(', ', map { "${_} = ?" } @columns) . qq| WHERE cp_id = ?|;
572 push @values, $form->{cp_id};
573 $cp_id = $form->{cp_id};
576 ($cp_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
578 $query = qq|INSERT INTO contacts (| . join(', ', @columns, 'cp_cv_id', 'cp_id') . qq|) VALUES (| . join(', ', ('?') x (2 + scalar @columns)) . qq|)|;
579 push @values, $form->{id}, $cp_id;
582 do_query($form, $dbh, $query, @values);
588 $main::lxdebug->enter_sub();
590 my ( $self, $myconfig, $form ) = @_;
591 # connect to database
592 my $dbh = $form->dbconnect($myconfig);
595 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
596 my $query = qq|DELETE FROM $cv WHERE id = ?|;
597 do_query($form, $dbh, $query, $form->{id});
601 $main::lxdebug->leave_sub();
605 $main::lxdebug->enter_sub();
607 my ( $self, $myconfig, $form ) = @_;
609 # connect to database
610 my $dbh = $form->dbconnect($myconfig);
612 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
613 my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
618 my %allowed_sort_columns = (
620 "customernumber" => "customernumber",
621 "vendornumber" => "vendornumber",
623 "contact" => "contact",
627 "street" => "street",
628 "taxnumber" => "taxnumber",
629 "business" => "business",
630 "invnumber" => "invnumber",
631 "ordnumber" => "ordnumber",
632 "quonumber" => "quonumber",
633 "zipcode" => "zipcode",
635 "country" => "country",
636 "salesman" => "e.name"
639 $form->{sort} ||= "name";
641 if ( $join_records ) {
642 # in UNION case order by hash key, e.g. salesman
643 # the UNION created an implicit select around the result
644 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
646 # in not UNION case order by hash value, e.g. e.name
647 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
649 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
651 if ($sortorder !~ /(business|id)/ && !$join_records) {
652 $sortorder = "lower($sortorder) ${sortdir}";
654 $sortorder .= " ${sortdir}";
657 if ($form->{"${cv}number"}) {
658 $where .= " AND ct.${cv}number ILIKE ?";
659 push(@values, '%' . $form->{"${cv}number"} . '%');
662 foreach my $key (qw(name contact email)) {
664 $where .= " AND ct.$key ILIKE ?";
665 push(@values, '%' . $form->{$key} . '%');
669 if ($form->{cp_name}) {
670 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
671 push @values, '%' . $form->{cp_name} . '%';
674 if ($form->{addr_city}) {
675 $where .= " AND ((lower(ct.city) LIKE lower(?))
680 WHERE (module = 'CT')
681 AND (lower(shiptocity) LIKE lower(?))
684 push @values, ('%' . $form->{addr_city} . '%') x 2;
687 if ($form->{addr_country}) {
688 $where .= " AND ((lower(ct.country) LIKE lower(?))
693 WHERE (module = 'CT')
694 AND (lower(shiptocountry) LIKE lower(?))
697 push @values, ('%' . $form->{addr_country} . '%') x 2;
700 if ( $form->{status} eq 'orphaned' ) {
702 qq| AND ct.id NOT IN | .
703 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
704 if ($cv eq 'customer') {
706 qq| AND ct.id NOT IN | .
707 qq| (SELECT a.customer_id FROM ar a, customer cv | .
708 qq| WHERE cv.id = a.customer_id)|;
710 if ($cv eq 'vendor') {
712 qq| AND ct.id NOT IN | .
713 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
714 qq| WHERE cv.id = a.vendor_id)|;
716 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
719 if ($form->{obsolete} eq "Y") {
720 $where .= qq| AND obsolete|;
721 } elsif ($form->{obsolete} eq "N") {
722 $where .= qq| AND NOT obsolete|;
725 if ($form->{business_id}) {
726 $where .= qq| AND (business_id = ?)|;
727 push(@values, conv_i($form->{business_id}));
730 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
731 # Gilt nicht für Lieferanten
732 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
733 $where .= qq| AND ct.salesman_id = (select id from employee where login= ?)|;
734 push(@values, $form->{login});
737 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
738 'trans_id_field' => 'ct.id',
742 $where .= qq| AND ($cvar_where)|;
743 push @values, @cvar_values;
746 if ($form->{addr_street}) {
747 $where .= qq| AND (street ILIKE ?)|;
748 push @values, '%' . $form->{addr_street} . '%';
751 if ($form->{addr_zipcode}) {
752 $where .= qq| AND (zipcode ILIKE ?)|;
753 push @values, $form->{addr_zipcode} . '%';
757 qq|SELECT ct.*, b.description AS business, e.name as salesman | .
758 (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) .
760 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
761 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
764 my @saved_values = @values;
765 # redo for invoices, orders and quotations
769 if ($form->{l_invnumber}) {
770 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
771 my $module = $ar eq 'ar' ? 'is' : 'ir';
772 push(@values, @saved_values);
775 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
776 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
777 qq| '$module' AS module, 'invoice' AS formtype, | .
778 qq| (a.amount = a.paid) AS closed | .
780 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
781 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
782 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
783 qq|WHERE $where AND (a.invoice = '1')|;
786 if ( $form->{l_ordnumber} ) {
787 push(@values, @saved_values);
790 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
791 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
792 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
794 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
795 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
796 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
797 qq|WHERE $where AND (o.quotation = '0')|;
800 if ( $form->{l_quonumber} ) {
801 push(@values, @saved_values);
804 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
805 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
806 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
808 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
809 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
810 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
811 qq|WHERE $where AND (o.quotation = '1')|;
815 $query .= qq| ORDER BY $sortorder|;
817 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
819 $main::lxdebug->leave_sub();
823 $main::lxdebug->enter_sub();
825 my ( $self, $myconfig, $form ) = @_;
827 die 'Missing argument: cp_id' unless $::form->{cp_id};
829 my $dbh = $form->dbconnect($myconfig);
831 qq|SELECT * FROM contacts c | .
832 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
833 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
834 my $ref = $sth->fetchrow_hashref("NAME_lc");
836 map { $form->{$_} = $ref->{$_} } keys %$ref;
838 $query = qq|SELECT COUNT(cp_id) AS used FROM (
839 SELECT cp_id FROM oe UNION
840 SELECT cp_id FROM ar UNION
841 SELECT cp_id FROM ap UNION
842 SELECT cp_id FROM delivery_orders
843 ) AS cpid WHERE cp_id = ? OR ? = 0|;
844 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
849 $main::lxdebug->leave_sub();
853 $main::lxdebug->enter_sub();
855 my ( $self, $myconfig, $form ) = @_;
856 my $dbh = $form->dbconnect($myconfig);
857 my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
858 my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id});
860 my $ref = $sth->fetchrow_hashref("NAME_lc");
862 map { $form->{$_} = $ref->{$_} } keys %$ref;
864 $query = qq|SELECT COUNT(shipto_id) AS used FROM (
865 SELECT shipto_id FROM oe UNION
866 SELECT shipto_id FROM ar UNION
867 SELECT shipto_id FROM delivery_orders
868 ) AS stid WHERE shipto_id = ? OR ? = 0|;
869 ($form->{shiptoused}) = selectfirst_array_query($form, $dbh, $query, ($form->{shipto_id})x2);
874 $main::lxdebug->leave_sub();
878 $main::lxdebug->enter_sub();
880 my ( $self, $myconfig, $form ) = @_;
881 my $dbh = $form->dbconnect($myconfig);
883 my $arap = $form->{db} eq "vendor" ? "ap" : "ar";
884 my $db = $form->{db} eq "customer" ? "customer" : "vendor";
885 my $qty_sign = $form->{db} eq 'vendor' ? ' * -1 AS qty' : '';
887 my $where = " WHERE 1=1 ";
890 if ($form->{shipto_id} && ($arap eq "ar")) {
891 $where .= "AND ${arap}.shipto_id = ?";
892 push(@values, $form->{shipto_id});
894 $where .= "AND ${arap}.${db}_id = ?";
895 push(@values, $form->{id});
899 $where .= "AND ${arap}.transdate >= ?";
900 push(@values, conv_date($form->{from}));
903 $where .= "AND ${arap}.transdate <= ?";
904 push(@values, conv_date($form->{to}));
907 qq|SELECT s.shiptoname, i.qty $qty_sign, | .
908 qq| ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | .
909 qq| i.description, i.unit, i.sellprice, | .
910 qq| oe.id AS oe_id, invoice | .
912 qq|LEFT JOIN shipto s ON | .
914 ? qq|(ar.shipto_id = s.shipto_id) |
915 : qq|(ap.id = s.trans_id) |) .
916 qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | .
917 qq|LEFT join parts p ON (p.id = i.parts_id) | .
918 qq|LEFT JOIN oe ON (oe.ordnumber = ${arap}.ordnumber AND NOT ${arap}.ordnumber = '') | .
920 qq|ORDER BY ${arap}.transdate DESC LIMIT 15|;
922 $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values);
926 $main::lxdebug->leave_sub();
930 $main::lxdebug->enter_sub();
935 my $form = $main::form;
937 Common::check_params(\%params, 'dbh');
939 if (!$form->{NOTE_subject}) {
940 $main::lxdebug->leave_sub();
944 my $dbh = $params{dbh};
948 'id' => $form->{NOTE_id},
949 'subject' => $form->{NOTE_subject},
950 'body' => $form->{NOTE_body},
951 'trans_id' => $form->{id},
952 'trans_module' => 'ct',
955 $note{id} = Notes->save(%note);
957 if ($form->{FU_date}) {
959 'id' => $form->{FU_id},
960 'note_id' => $note{id},
961 'follow_up_date' => $form->{FU_date},
962 'created_for_user' => $form->{FU_created_for_user},
963 'done' => $form->{FU_done} ? 1 : 0,
964 'subject' => $form->{NOTE_subject},
965 'body' => $form->{NOTE_body},
968 'trans_id' => $form->{id},
969 'trans_type' => $form->{db} eq 'customer' ? 'customer' : 'vendor',
970 'trans_info' => $form->{name},
975 $follow_up{id} = FU->save(%follow_up);
977 } elsif ($form->{FU_id}) {
978 do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($form->{FU_id}));
979 do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, conv_i($form->{FU_id}));
982 delete @{$form}{grep { /^NOTE_|^FU_/ } keys %{ $form }};
984 $main::lxdebug->leave_sub();
987 sub _delete_selected_notes {
988 $main::lxdebug->enter_sub();
993 Common::check_params(\%params, 'dbh');
995 my $form = $main::form;
996 my $dbh = $params{dbh};
998 foreach my $i (1 .. $form->{NOTES_rowcount}) {
999 next unless ($form->{"NOTE_delete_$i"} && $form->{"NOTE_id_$i"});
1001 Notes->delete('dbh' => $params{dbh},
1002 'id' => $form->{"NOTE_id_$i"});
1005 $main::lxdebug->leave_sub();
1008 # TODO: remove in 2.7.0 stable
1010 $main::lxdebug->enter_sub();
1013 my $shipto_id = shift;
1015 my $form = $main::form;
1016 my %myconfig = %main::myconfig;
1017 my $dbh = $form->get_standard_dbh(\%myconfig);
1019 do_query($form, $dbh, qq|UPDATE shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id);
1023 $main::lxdebug->leave_sub();
1026 # TODO: remove in 2.7.0 stable
1027 sub delete_contact {
1028 $main::lxdebug->enter_sub();
1033 my $form = $main::form;
1034 my %myconfig = %main::myconfig;
1035 my $dbh = $form->get_standard_dbh(\%myconfig);
1037 do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $cp_id);
1041 $main::lxdebug->leave_sub();
1045 $main::lxdebug->enter_sub();
1050 Common::check_params(\%params, qw(vc id));
1052 my $myconfig = \%main::myconfig;
1053 my $form = $main::form;
1055 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1057 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
1058 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
1059 my $placeholders = join ", ", ('?') x scalar @ids;
1060 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic
1062 WHERE id IN (${placeholders})|;
1064 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
1066 if (ref $params{id} eq 'ARRAY') {
1067 $result = { map { $_->{id} => $_ } @{ $result } };
1069 $result = $result->[0] || { 'id' => $params{id} };
1072 $main::lxdebug->leave_sub();
1077 sub parse_excel_file {
1078 $main::lxdebug->enter_sub();
1080 my ($self, $myconfig, $form) = @_;
1081 my $locale = $main::locale;
1083 $form->{formname} = 'sales_quotation';
1084 $form->{type} = 'sales_quotation';
1085 $form->{format} = 'excel';
1086 $form->{media} = 'screen';
1087 $form->{quonumber} = 1;
1090 # $form->{"notes"} will be overridden by the customer's/vendor's "notes" field. So save it here.
1091 $form->{ $form->{"formname"} . "notes" } = $form->{"notes"};
1095 $form->{"${inv}date"} = $form->{transdate};
1096 $form->{label} = $locale->text('Quotation');
1097 my $numberfld = "sqnumber";
1101 $form->{what_done} = $form->{formname};
1103 map({ delete($form->{$_}); } grep(/^cp_/, keys(%{ $form })));
1105 my $output_dateformat = $myconfig->{"dateformat"};
1106 my $output_numberformat = $myconfig->{"numberformat"};
1107 my $output_longdates = 1;
1109 # map login user variables
1110 map { $form->{"login_$_"} = $myconfig->{$_} } ("name", "email", "fax", "tel", "company");
1113 for my $field (qw(transdate_oe deliverydate_oe)) {
1115 $form->{$field}[$_] = $locale->date($myconfig, $form->{$field}[$_], 1);
1116 } 0 .. $#{ $form->{$field} };
1119 if ($form->{shipto_id}) {
1120 $form->get_shipto($myconfig);
1123 $form->{notes} =~ s/^\s+//g;
1125 $form->{templates} = $myconfig->{templates};
1127 delete $form->{printer_command};
1129 $form->get_employee_info($myconfig);
1131 my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_');
1133 if (scalar @{ $cvar_date_fields }) {
1134 format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields });
1137 while (my ($precision, $field_list) = each %{ $cvar_number_fields }) {
1138 reformat_numbers($output_numberformat, $precision, @{ $field_list });
1142 my $extension = 'xls';
1144 $form->{IN} = "$form->{formname}.${extension}";
1146 delete $form->{OUT};
1148 $form->parse_template($myconfig);
1150 $main::lxdebug->leave_sub();
1153 sub search_contacts {
1154 $::lxdebug->enter_sub;
1159 my $dbh = $params{dbh} || $::form->get_standard_dbh;
1160 my $vc = $params{db} eq 'customer' ? 'customer' : 'vendor';
1163 'cp_name' => 'cp_name, cp_givenname',
1164 'vcname' => 'vcname, cp_name, cp_givenname',
1165 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
1168 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);
1170 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
1171 $::form->{sort} = $order_by;
1172 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
1174 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
1175 $order_by =~ s/,/ ${sortdir},/g;
1176 $order_by .= " $sortdir";
1178 my @where_tokens = ();
1181 if ($params{search_term}) {
1184 'cp.cp_name ILIKE ?',
1185 'cp.cp_givenname ILIKE ?',
1186 'cp.cp_email ILIKE ?';
1187 push @values, ('%' . $params{search_term} . '%') x 3;
1189 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
1190 my $number = $params{search_term};
1191 $number =~ s/[^\d]//g;
1192 $number = join '[ /\(\)+\-]*', split(m//, $number);
1194 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
1197 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
1200 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
1201 'trans_id_field' => 'cp.cp_id',
1202 'filter' => $params{filter});
1205 push @where_tokens, $cvar_where;
1206 push @values, @cvar_values;
1209 if (my $filter = $params{filter}) {
1210 for (qw(name title givenname email project abteilung)) {
1211 next unless $filter->{"cp_$_"};
1212 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
1215 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
1216 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
1219 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
1221 my $query = qq|SELECT cp.*,
1222 COALESCE(c.id, v.id) AS vcid,
1223 COALESCE(c.name, v.name) AS vcname,
1224 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
1225 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
1227 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
1228 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
1230 ORDER BY $order_by|;
1232 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
1234 $::lxdebug->leave_sub;
1236 return @{ $contacts };