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 #======================================================================
50 $main::lxdebug->enter_sub();
52 my ( $self, $myconfig, $form ) = @_;
55 my $dbh = $form->dbconnect($myconfig);
57 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
58 my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
63 my %allowed_sort_columns = (
65 "customernumber" => "ct.customernumber",
66 "vendornumber" => "ct.vendornumber",
68 "contact" => "ct.contact",
69 "phone" => "ct.phone",
71 "email" => "ct.email",
72 "street" => "ct.street",
73 "taxnumber" => "ct.taxnumber",
74 "business" => "b.description",
75 "invnumber" => "ct.invnumber",
76 "ordnumber" => "ct.ordnumber",
77 "quonumber" => "ct.quonumber",
78 "zipcode" => "ct.zipcode",
80 "country" => "ct.country",
82 "discount" => "ct.discount",
83 "insertdate" => "ct.itime",
84 "salesman" => "e.name",
85 "payment" => "pt.description",
86 "pricegroup" => "pg.pricegroup",
87 "ustid" => "ct.ustid",
88 "creditlimit" => "ct.creditlimit",
89 "commercial_court" => "ct.commercial_court",
92 $form->{sort} ||= "name";
94 if ( $join_records ) {
95 # in UNION case order by hash key, e.g. salesman
96 # the UNION created an implicit select around the result
97 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
99 # in not UNION case order by hash value, e.g. e.name
100 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
102 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
104 if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) {
105 $sortorder = "lower($sortorder) ${sortdir}";
107 $sortorder .= " ${sortdir}";
110 if ($form->{"${cv}number"}) {
111 $where .= " AND ct.${cv}number ILIKE ?";
112 push(@values, like($form->{"${cv}number"}));
115 foreach my $key (qw(name contact email)) {
117 $where .= " AND ct.$key ILIKE ?";
118 push(@values, like($form->{$key}));
122 if ($form->{cp_name}) {
123 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
124 push @values, like($form->{cp_name});
127 if ($form->{addr_street}) {
128 $where .= qq| AND ((ct.street ILIKE ?) | .
131 qq| SELECT sc.trans_id FROM shipto sc | .
132 qq| WHERE (sc.module = 'CT') | .
133 qq| AND (sc.shiptostreet ILIKE ?) | .
135 push @values, (like($form->{addr_street})) x 2;
138 if ($form->{addr_zipcode}) {
139 $where .= qq| AND ((ct.zipcode ILIKE ?) | .
142 qq| SELECT sc.trans_id FROM shipto sc | .
143 qq| WHERE (sc.module = 'CT') | .
144 qq| AND (sc.shiptozipcode ILIKE ?) | .
146 push @values, (like($form->{addr_zipcode})) x 2;
149 if ($form->{addr_city}) {
150 $where .= " AND ((lower(ct.city) LIKE lower(?))
155 WHERE (sc.module = 'CT')
156 AND (lower(sc.shiptocity) LIKE lower(?))
159 push @values, (like($form->{addr_city})) x 2;
162 if ($form->{addr_country}) {
163 $where .= " AND ((lower(ct.country) LIKE lower(?))
168 WHERE (so.module = 'CT')
169 AND (lower(so.shiptocountry) LIKE lower(?))
172 push @values, (like($form->{addr_country})) x 2;
175 if ($form->{addr_gln}) {
176 $where .= " AND ((lower(ct.gln) LIKE lower(?))
181 WHERE (so.module = 'CT')
182 AND (lower(so.shiptogln) LIKE lower(?))
185 push @values, (like($form->{addr_gln})) x 2;
188 if ( $form->{status} eq 'orphaned' ) {
190 qq| AND ct.id NOT IN | .
191 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
192 if ($cv eq 'customer') {
194 qq| AND ct.id NOT IN | .
195 qq| (SELECT a.customer_id FROM ar a, customer cv | .
196 qq| WHERE cv.id = a.customer_id)|;
198 if ($cv eq 'vendor') {
200 qq| AND ct.id NOT IN | .
201 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
202 qq| WHERE cv.id = a.vendor_id)|;
204 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
207 if ($form->{obsolete} eq "Y") {
208 $where .= qq| AND ct.obsolete|;
209 } elsif ($form->{obsolete} eq "N") {
210 $where .= qq| AND NOT ct.obsolete|;
213 if ($form->{business_id}) {
214 $where .= qq| AND (ct.business_id = ?)|;
215 push(@values, conv_i($form->{business_id}));
218 if ($form->{salesman_id}) {
219 $where .= qq| AND (ct.salesman_id = ?)|;
220 push(@values, conv_i($form->{salesman_id}));
223 if($form->{insertdatefrom}) {
224 $where .= qq| AND (ct.itime::DATE >= ?)|;
225 push@values, conv_date($form->{insertdatefrom});
228 if($form->{insertdateto}) {
229 $where .= qq| AND (ct.itime::DATE <= ?)|;
230 push @values, conv_date($form->{insertdateto});
234 my @tokens = parse_line('\s+', 0, $form->{all});
236 ct.${cv}number ILIKE ? OR
239 push @values, ("%$_%")x2 for @tokens;
242 if (($form->{create_zugferd_invoices} // '') ne '') {
243 $where .= qq| AND (ct.create_zugferd_invoices = ?)|;
244 push @values, $form->{create_zugferd_invoices};
247 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
248 'trans_id_field' => 'ct.id',
252 $where .= qq| AND ($cvar_where)|;
253 push @values, @cvar_values;
256 my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
257 my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
259 my $main_cp_select = '';
260 if ($form->{l_main_contact_person}) {
261 $main_cp_select = qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
262 FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
263 AS main_contact_person /;
266 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
267 qq| pt.description as payment | .
270 (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) .
272 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
273 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
274 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
278 my @saved_values = @values;
279 # redo for invoices, orders and quotations
283 if ($form->{l_invnumber}) {
284 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
285 my $module = $ar eq 'ar' ? 'is' : 'ir';
286 push(@values, @saved_values);
289 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
290 qq| pt.description as payment | .
293 qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
294 qq| '$module' AS module, 'invoice' AS formtype, | .
295 qq| (a.amount = a.paid) AS closed | .
297 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
298 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
299 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
300 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
302 qq|WHERE $where AND (a.invoice = '1')|;
305 if ( $form->{l_ordnumber} ) {
306 push(@values, @saved_values);
309 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
310 qq| pt.description as payment | .
313 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
314 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
316 qq|JOIN oe o ON (o.${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 (o.quotation = '0')|;
324 if ( $form->{l_quonumber} ) {
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, 'quotation' 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 = '1')|;
344 $query .= qq| ORDER BY $sortorder|;
346 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
348 $main::lxdebug->leave_sub();
352 $main::lxdebug->enter_sub();
354 my ( $self, $myconfig, $form ) = @_;
356 die 'Missing argument: cp_id' unless $::form->{cp_id};
358 my $dbh = SL::DB->client->dbh;
360 qq|SELECT * FROM contacts c | .
361 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
362 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
363 my $ref = $sth->fetchrow_hashref("NAME_lc");
365 map { $form->{$_} = $ref->{$_} } keys %$ref;
367 $query = qq|SELECT COUNT(cp_id) AS used FROM (
368 SELECT cp_id FROM oe UNION
369 SELECT cp_id FROM ar UNION
370 SELECT cp_id FROM ap UNION
371 SELECT cp_id FROM delivery_orders
372 ) AS cpid WHERE cp_id = ? OR ? = 0|;
373 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
377 $main::lxdebug->leave_sub();
381 $main::lxdebug->enter_sub();
386 Common::check_params(\%params, qw(vc id));
388 my $myconfig = \%main::myconfig;
389 my $form = $main::form;
391 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
393 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
394 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
395 my $placeholders = join ", ", ('?') x scalar @ids;
396 my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
397 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
399 WHERE id IN (${placeholders})|;
401 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
403 if (ref $params{id} eq 'ARRAY') {
404 $result = { map { $_->{id} => $_ } @{ $result } };
406 $result = $result->[0] || { 'id' => $params{id} };
409 $main::lxdebug->leave_sub();
414 sub search_contacts {
415 $::lxdebug->enter_sub;
420 my $dbh = $params{dbh} || $::form->get_standard_dbh;
423 'cp_name' => 'cp_name, cp_givenname',
424 'vcname' => 'vcname, cp_name, cp_givenname',
425 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
428 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);
430 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
431 $::form->{sort} = $order_by;
432 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
434 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
435 $order_by =~ s/,/ ${sortdir},/g;
436 $order_by .= " $sortdir";
438 my @where_tokens = ();
441 if ($params{search_term}) {
444 'cp.cp_name ILIKE ?',
445 'cp.cp_givenname ILIKE ?',
446 'cp.cp_email ILIKE ?';
447 push @values, (like($params{search_term})) x 3;
449 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
450 my $number = $params{search_term};
451 $number =~ s/[^\d]//g;
452 $number = join '[ /\(\)+\-]*', split(m//, $number);
454 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
457 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
460 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
461 'trans_id_field' => 'cp.cp_id',
462 'filter' => $params{filter});
465 push @where_tokens, $cvar_where;
466 push @values, @cvar_values;
469 if (my $filter = $params{filter}) {
470 for (qw(name title givenname email project abteilung)) {
471 next unless $filter->{"cp_$_"};
472 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
475 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
476 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
479 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
481 my $query = qq|SELECT cp.*,
482 COALESCE(c.id, v.id) AS vcid,
483 COALESCE(c.name, v.name) AS vcname,
484 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
485 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
487 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
488 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
492 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
494 $::lxdebug->leave_sub;
496 return @{ $contacts };