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., 51 Franklin Street, Fifth Floor, Boston,
30 #======================================================================
32 # backend code for customers and vendors
35 # DS. 2000-07-04 Created
37 #======================================================================
45 use SL::Util qw(trim);
51 $main::lxdebug->enter_sub();
53 my ( $self, $myconfig, $form ) = @_;
56 my $dbh = $form->dbconnect($myconfig);
58 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
59 my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
64 my %allowed_sort_columns = (
66 "customernumber" => "ct.customernumber",
67 "vendornumber" => "ct.vendornumber",
69 "contact" => "ct.contact",
70 "phone" => "ct.phone",
72 "email" => "ct.email",
73 "street" => "ct.street",
74 "taxnumber" => "ct.taxnumber",
75 "business" => "b.description",
76 "invnumber" => "ct.invnumber",
77 "ordnumber" => "ct.ordnumber",
78 "quonumber" => "ct.quonumber",
79 "zipcode" => "ct.zipcode",
81 "country" => "ct.country",
83 "discount" => "ct.discount",
84 "insertdate" => "ct.itime",
85 "salesman" => "e.name",
86 "payment" => "pt.description",
87 "pricegroup" => "pg.pricegroup",
88 "ustid" => "ct.ustid",
89 "creditlimit" => "ct.creditlimit",
90 "commercial_court" => "ct.commercial_court",
93 $form->{sort} ||= "name";
95 if ( $join_records ) {
96 # in UNION case order by hash key, e.g. salesman
97 # the UNION created an implicit select around the result
98 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
100 # in not UNION case order by hash value, e.g. e.name
101 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
103 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
105 if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) {
106 $sortorder = "lower($sortorder) ${sortdir}";
108 $sortorder .= " ${sortdir}";
111 if ($form->{"${cv}number"}) {
112 $where .= " AND ct.${cv}number ILIKE ?";
113 push(@values, like($form->{"${cv}number"}));
116 foreach my $key (qw(name contact email)) {
118 $where .= " AND ct.$key ILIKE ?";
119 push(@values, like($form->{$key}));
123 if ($form->{cp_name}) {
124 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
125 push @values, like($form->{cp_name});
128 if ($form->{addr_street}) {
129 $where .= qq| AND ((ct.street ILIKE ?) | .
132 qq| SELECT sc.trans_id FROM shipto sc | .
133 qq| WHERE (sc.module = 'CT') | .
134 qq| AND (sc.shiptostreet ILIKE ?) | .
136 push @values, (like($form->{addr_street})) x 2;
139 if ($form->{addr_zipcode}) {
140 $where .= qq| AND ((ct.zipcode ILIKE ?) | .
143 qq| SELECT sc.trans_id FROM shipto sc | .
144 qq| WHERE (sc.module = 'CT') | .
145 qq| AND (sc.shiptozipcode ILIKE ?) | .
147 push @values, (like($form->{addr_zipcode})) x 2;
150 if ($form->{addr_city}) {
151 $where .= " AND ((lower(ct.city) LIKE lower(?))
156 WHERE (sc.module = 'CT')
157 AND (lower(sc.shiptocity) LIKE lower(?))
160 push @values, (like($form->{addr_city})) x 2;
163 if ($form->{addr_country}) {
164 $where .= " AND ((lower(ct.country) LIKE lower(?))
169 WHERE (so.module = 'CT')
170 AND (lower(so.shiptocountry) LIKE lower(?))
173 push @values, (like($form->{addr_country})) x 2;
176 if ($form->{addr_gln}) {
177 $where .= " AND ((lower(ct.gln) LIKE lower(?))
182 WHERE (so.module = 'CT')
183 AND (lower(so.shiptogln) LIKE lower(?))
186 push @values, (like($form->{addr_gln})) x 2;
189 if ( $form->{status} eq 'orphaned' ) {
191 qq| AND ct.id NOT IN | .
192 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
193 if ($cv eq 'customer') {
195 qq| AND ct.id NOT IN | .
196 qq| (SELECT a.customer_id FROM ar a, customer cv | .
197 qq| WHERE cv.id = a.customer_id)|;
199 if ($cv eq 'vendor') {
201 qq| AND ct.id NOT IN | .
202 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
203 qq| WHERE cv.id = a.vendor_id)|;
205 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
208 if ($form->{obsolete} eq "Y") {
209 $where .= qq| AND ct.obsolete|;
210 } elsif ($form->{obsolete} eq "N") {
211 $where .= qq| AND NOT ct.obsolete|;
214 if ($form->{business_id}) {
215 $where .= qq| AND (ct.business_id = ?)|;
216 push(@values, conv_i($form->{business_id}));
219 if ($form->{salesman_id}) {
220 $where .= qq| AND (ct.salesman_id = ?)|;
221 push(@values, conv_i($form->{salesman_id}));
224 if($form->{insertdatefrom}) {
225 $where .= qq| AND (ct.itime::DATE >= ?)|;
226 push@values, conv_date($form->{insertdatefrom});
229 if($form->{insertdateto}) {
230 $where .= qq| AND (ct.itime::DATE <= ?)|;
231 push @values, conv_date($form->{insertdateto});
235 my @tokens = parse_line('\s+', 0, $form->{all});
237 ct.${cv}number ILIKE ? OR
240 push @values, ("%$_%")x2 for @tokens;
243 if (($form->{create_zugferd_invoices} // '') ne '') {
244 $where .= qq| AND (ct.create_zugferd_invoices = ?)|;
245 push @values, $form->{create_zugferd_invoices};
248 if ($form->{all_phonenumbers}) {
249 $where .= qq| AND (ct.phone ILIKE ? OR
252 (SELECT cp_cv_id FROM contacts
253 WHERE cp_phone1 ILIKE ? OR
256 cp_mobile1 ILIKE ? OR
257 cp_mobile2 ILIKE ? OR
258 cp_satphone ILIKE ? OR
260 cp_privatphone ILIKE ?
263 push @values, (like(trim($form->{all_phonenumbers})))x10;
266 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
267 'trans_id_field' => 'ct.id',
271 $where .= qq| AND ($cvar_where)|;
272 push @values, @cvar_values;
275 my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
276 my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
278 my $main_cp_select = '';
279 if ($form->{l_main_contact_person}) {
280 $main_cp_select = qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
281 FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
282 AS main_contact_person /;
285 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
286 qq| pt.description as payment | .
289 (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) .
291 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
292 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
293 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
297 my @saved_values = @values;
298 # redo for invoices, orders and quotations
302 if ($form->{l_invnumber}) {
303 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
304 my $module = $ar eq 'ar' ? 'is' : 'ir';
305 push(@values, @saved_values);
308 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
309 qq| pt.description as payment | .
312 qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
313 qq| '$module' AS module, 'invoice' AS formtype, | .
314 qq| (a.amount = a.paid) AS closed | .
316 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
317 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
318 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
319 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
321 qq|WHERE $where AND (a.invoice = '1')|;
324 if ( $form->{l_ordnumber} ) {
325 push(@values, @saved_values);
328 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
329 qq| pt.description as payment | .
332 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
333 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
335 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
336 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
337 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
338 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
340 qq|WHERE $where AND (o.quotation = '0')|;
343 if ( $form->{l_quonumber} ) {
344 push(@values, @saved_values);
347 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
348 qq| pt.description as payment | .
351 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
352 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
354 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
355 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
356 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
357 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
359 qq|WHERE $where AND (o.quotation = '1')|;
363 $query .= qq| ORDER BY $sortorder|;
365 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
367 $main::lxdebug->leave_sub();
371 $main::lxdebug->enter_sub();
373 my ( $self, $myconfig, $form ) = @_;
375 die 'Missing argument: cp_id' unless $::form->{cp_id};
377 my $dbh = SL::DB->client->dbh;
379 qq|SELECT * FROM contacts c | .
380 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
381 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
382 my $ref = $sth->fetchrow_hashref("NAME_lc");
384 map { $form->{$_} = $ref->{$_} } keys %$ref;
386 $query = qq|SELECT COUNT(cp_id) AS used FROM (
387 SELECT cp_id FROM oe UNION
388 SELECT cp_id FROM ar UNION
389 SELECT cp_id FROM ap UNION
390 SELECT cp_id FROM delivery_orders
391 ) AS cpid WHERE cp_id = ? OR ? = 0|;
392 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
396 $main::lxdebug->leave_sub();
400 $main::lxdebug->enter_sub();
405 Common::check_params(\%params, qw(vc id));
407 my $myconfig = \%main::myconfig;
408 my $form = $main::form;
410 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
412 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
413 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
414 my $placeholders = join ", ", ('?') x scalar @ids;
415 my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
416 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
418 WHERE id IN (${placeholders})|;
420 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
422 if (ref $params{id} eq 'ARRAY') {
423 $result = { map { $_->{id} => $_ } @{ $result } };
425 $result = $result->[0] || { 'id' => $params{id} };
428 $main::lxdebug->leave_sub();
433 sub search_contacts {
434 $::lxdebug->enter_sub;
439 my $dbh = $params{dbh} || $::form->get_standard_dbh;
442 'cp_name' => 'cp_name, cp_givenname',
443 'vcname' => 'vcname, cp_name, cp_givenname',
444 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
447 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);
449 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
450 $::form->{sort} = $order_by;
451 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
453 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
454 $order_by =~ s/,/ ${sortdir},/g;
455 $order_by .= " $sortdir";
457 my @where_tokens = ();
460 if ($params{search_term}) {
463 'cp.cp_name ILIKE ?',
464 'cp.cp_givenname ILIKE ?',
465 'cp.cp_email ILIKE ?';
466 push @values, (like($params{search_term})) x 3;
468 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
469 my $number = $params{search_term};
470 $number =~ s/[^\d]//g;
471 $number = join '[ /\(\)+\-]*', split(m//, $number);
473 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
476 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
479 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
480 'trans_id_field' => 'cp.cp_id',
481 'filter' => $params{filter});
484 push @where_tokens, $cvar_where;
485 push @values, @cvar_values;
488 if (my $filter = $params{filter}) {
489 for (qw(name title givenname email project abteilung)) {
490 next unless $filter->{"cp_$_"};
491 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
494 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
495 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
498 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
500 my $query = qq|SELECT cp.*,
501 COALESCE(c.id, v.id) AS vcid,
502 COALESCE(c.name, v.name) AS vcname,
503 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
504 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
506 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
507 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
511 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
513 $::lxdebug->leave_sub;
515 return @{ $contacts };