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 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
248 # Gilt nicht für Lieferanten
249 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
250 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
251 push(@values, $::myconfig{login});
254 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
255 'trans_id_field' => 'ct.id',
259 $where .= qq| AND ($cvar_where)|;
260 push @values, @cvar_values;
263 my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
264 my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
266 my $main_cp_select = '';
267 if ($form->{l_main_contact_person}) {
268 $main_cp_select = qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
269 FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
270 AS main_contact_person /;
273 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
274 qq| pt.description as payment | .
277 (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) .
279 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
280 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
281 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
285 my @saved_values = @values;
286 # redo for invoices, orders and quotations
290 if ($form->{l_invnumber}) {
291 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
292 my $module = $ar eq 'ar' ? 'is' : 'ir';
293 push(@values, @saved_values);
296 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
297 qq| pt.description as payment | .
299 qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
300 qq| '$module' AS module, 'invoice' AS formtype, | .
301 qq| (a.amount = a.paid) AS closed | .
303 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
304 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
305 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
306 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
308 qq|WHERE $where AND (a.invoice = '1')|;
311 if ( $form->{l_ordnumber} ) {
312 push(@values, @saved_values);
315 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
316 qq| pt.description as payment | .
318 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
319 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
321 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
322 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
323 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
324 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
326 qq|WHERE $where AND (o.quotation = '0')|;
329 if ( $form->{l_quonumber} ) {
330 push(@values, @saved_values);
333 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
334 qq| pt.description as payment | .
336 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
337 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
339 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
340 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
341 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
342 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
344 qq|WHERE $where AND (o.quotation = '1')|;
348 $query .= qq| ORDER BY $sortorder|;
350 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
352 $main::lxdebug->leave_sub();
356 $main::lxdebug->enter_sub();
358 my ( $self, $myconfig, $form ) = @_;
360 die 'Missing argument: cp_id' unless $::form->{cp_id};
362 my $dbh = SL::DB->client->dbh;
364 qq|SELECT * FROM contacts c | .
365 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
366 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
367 my $ref = $sth->fetchrow_hashref("NAME_lc");
369 map { $form->{$_} = $ref->{$_} } keys %$ref;
371 $query = qq|SELECT COUNT(cp_id) AS used FROM (
372 SELECT cp_id FROM oe UNION
373 SELECT cp_id FROM ar UNION
374 SELECT cp_id FROM ap UNION
375 SELECT cp_id FROM delivery_orders
376 ) AS cpid WHERE cp_id = ? OR ? = 0|;
377 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
381 $main::lxdebug->leave_sub();
385 $main::lxdebug->enter_sub();
390 Common::check_params(\%params, qw(vc id));
392 my $myconfig = \%main::myconfig;
393 my $form = $main::form;
395 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
397 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
398 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
399 my $placeholders = join ", ", ('?') x scalar @ids;
400 my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
401 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
403 WHERE id IN (${placeholders})|;
405 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
407 if (ref $params{id} eq 'ARRAY') {
408 $result = { map { $_->{id} => $_ } @{ $result } };
410 $result = $result->[0] || { 'id' => $params{id} };
413 $main::lxdebug->leave_sub();
418 sub search_contacts {
419 $::lxdebug->enter_sub;
424 my $dbh = $params{dbh} || $::form->get_standard_dbh;
427 'cp_name' => 'cp_name, cp_givenname',
428 'vcname' => 'vcname, cp_name, cp_givenname',
429 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
432 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);
434 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
435 $::form->{sort} = $order_by;
436 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
438 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
439 $order_by =~ s/,/ ${sortdir},/g;
440 $order_by .= " $sortdir";
442 my @where_tokens = ();
445 if ($params{search_term}) {
448 'cp.cp_name ILIKE ?',
449 'cp.cp_givenname ILIKE ?',
450 'cp.cp_email ILIKE ?';
451 push @values, (like($params{search_term})) x 3;
453 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
454 my $number = $params{search_term};
455 $number =~ s/[^\d]//g;
456 $number = join '[ /\(\)+\-]*', split(m//, $number);
458 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
461 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
464 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
465 'trans_id_field' => 'cp.cp_id',
466 'filter' => $params{filter});
469 push @where_tokens, $cvar_where;
470 push @values, @cvar_values;
473 if (my $filter = $params{filter}) {
474 for (qw(name title givenname email project abteilung)) {
475 next unless $filter->{"cp_$_"};
476 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
479 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
480 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
483 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
485 my $query = qq|SELECT cp.*,
486 COALESCE(c.id, v.id) AS vcid,
487 COALESCE(c.name, v.name) AS vcname,
488 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
489 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
491 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
492 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
496 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
498 $::lxdebug->leave_sub;
500 return @{ $contacts };