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",
89 $form->{sort} ||= "name";
91 if ( $join_records ) {
92 # in UNION case order by hash key, e.g. salesman
93 # the UNION created an implicit select around the result
94 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
96 # in not UNION case order by hash value, e.g. e.name
97 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
99 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
101 if ($sortorder !~ /(business|id|discount|itime)/ && !$join_records) {
102 $sortorder = "lower($sortorder) ${sortdir}";
104 $sortorder .= " ${sortdir}";
107 if ($form->{"${cv}number"}) {
108 $where .= " AND ct.${cv}number ILIKE ?";
109 push(@values, like($form->{"${cv}number"}));
112 foreach my $key (qw(name contact email)) {
114 $where .= " AND ct.$key ILIKE ?";
115 push(@values, like($form->{$key}));
119 if ($form->{cp_name}) {
120 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
121 push @values, like($form->{cp_name});
124 if ($form->{addr_street}) {
125 $where .= qq| AND ((ct.street ILIKE ?) | .
128 qq| SELECT sc.trans_id FROM shipto sc | .
129 qq| WHERE (sc.module = 'CT') | .
130 qq| AND (sc.shiptostreet ILIKE ?) | .
132 push @values, (like($form->{addr_street})) x 2;
135 if ($form->{addr_zipcode}) {
136 $where .= qq| AND ((ct.zipcode ILIKE ?) | .
139 qq| SELECT sc.trans_id FROM shipto sc | .
140 qq| WHERE (sc.module = 'CT') | .
141 qq| AND (sc.shiptozipcode ILIKE ?) | .
143 push @values, (like($form->{addr_zipcode})) x 2;
146 if ($form->{addr_city}) {
147 $where .= " AND ((lower(ct.city) LIKE lower(?))
152 WHERE (sc.module = 'CT')
153 AND (lower(sc.shiptocity) LIKE lower(?))
156 push @values, (like($form->{addr_city})) x 2;
159 if ($form->{addr_country}) {
160 $where .= " AND ((lower(ct.country) LIKE lower(?))
165 WHERE (so.module = 'CT')
166 AND (lower(so.shiptocountry) LIKE lower(?))
169 push @values, (like($form->{addr_country})) x 2;
172 if ($form->{addr_gln}) {
173 $where .= " AND ((lower(ct.gln) LIKE lower(?))
178 WHERE (so.module = 'CT')
179 AND (lower(so.shiptogln) LIKE lower(?))
182 push @values, (like($form->{addr_gln})) x 2;
185 if ( $form->{status} eq 'orphaned' ) {
187 qq| AND ct.id NOT IN | .
188 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
189 if ($cv eq 'customer') {
191 qq| AND ct.id NOT IN | .
192 qq| (SELECT a.customer_id FROM ar a, customer cv | .
193 qq| WHERE cv.id = a.customer_id)|;
195 if ($cv eq 'vendor') {
197 qq| AND ct.id NOT IN | .
198 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
199 qq| WHERE cv.id = a.vendor_id)|;
201 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
204 if ($form->{obsolete} eq "Y") {
205 $where .= qq| AND ct.obsolete|;
206 } elsif ($form->{obsolete} eq "N") {
207 $where .= qq| AND NOT ct.obsolete|;
210 if ($form->{business_id}) {
211 $where .= qq| AND (ct.business_id = ?)|;
212 push(@values, conv_i($form->{business_id}));
215 if ($form->{salesman_id}) {
216 $where .= qq| AND (ct.salesman_id = ?)|;
217 push(@values, conv_i($form->{salesman_id}));
220 if($form->{insertdatefrom}) {
221 $where .= qq| AND (ct.itime::DATE >= ?)|;
222 push@values, conv_date($form->{insertdatefrom});
225 if($form->{insertdateto}) {
226 $where .= qq| AND (ct.itime::DATE <= ?)|;
227 push @values, conv_date($form->{insertdateto});
231 my @tokens = parse_line('\s+', 0, $form->{all});
233 ct.${cv}number ILIKE ? OR
236 push @values, ("%$_%")x2 for @tokens;
239 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
240 # Gilt nicht für Lieferanten
241 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
242 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
243 push(@values, $::myconfig{login});
246 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
247 'trans_id_field' => 'ct.id',
251 $where .= qq| AND ($cvar_where)|;
252 push @values, @cvar_values;
255 my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
256 my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
258 my $main_cp_select = '';
259 if ($form->{l_main_contact_person}) {
260 $main_cp_select = qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
261 FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
262 AS main_contact_person /;
265 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
266 qq| pt.description as payment | .
269 (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) .
271 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
272 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
273 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
277 my @saved_values = @values;
278 # redo for invoices, orders and quotations
282 if ($form->{l_invnumber}) {
283 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
284 my $module = $ar eq 'ar' ? 'is' : 'ir';
285 push(@values, @saved_values);
288 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
289 qq| pt.description as payment | .
291 qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
292 qq| '$module' AS module, 'invoice' AS formtype, | .
293 qq| (a.amount = a.paid) AS closed | .
295 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
296 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
297 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
298 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
300 qq|WHERE $where AND (a.invoice = '1')|;
303 if ( $form->{l_ordnumber} ) {
304 push(@values, @saved_values);
307 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
308 qq| pt.description as payment | .
310 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
311 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
313 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
314 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
315 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
316 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
318 qq|WHERE $where AND (o.quotation = '0')|;
321 if ( $form->{l_quonumber} ) {
322 push(@values, @saved_values);
325 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
326 qq| pt.description as payment | .
328 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
329 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
331 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
332 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
333 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
334 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
336 qq|WHERE $where AND (o.quotation = '1')|;
340 $query .= qq| ORDER BY $sortorder|;
342 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
344 $main::lxdebug->leave_sub();
348 $main::lxdebug->enter_sub();
350 my ( $self, $myconfig, $form ) = @_;
352 die 'Missing argument: cp_id' unless $::form->{cp_id};
354 my $dbh = SL::DB->client->dbh;
356 qq|SELECT * FROM contacts c | .
357 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
358 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
359 my $ref = $sth->fetchrow_hashref("NAME_lc");
361 map { $form->{$_} = $ref->{$_} } keys %$ref;
363 $query = qq|SELECT COUNT(cp_id) AS used FROM (
364 SELECT cp_id FROM oe UNION
365 SELECT cp_id FROM ar UNION
366 SELECT cp_id FROM ap UNION
367 SELECT cp_id FROM delivery_orders
368 ) AS cpid WHERE cp_id = ? OR ? = 0|;
369 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
373 $main::lxdebug->leave_sub();
377 $main::lxdebug->enter_sub();
382 Common::check_params(\%params, qw(vc id));
384 my $myconfig = \%main::myconfig;
385 my $form = $main::form;
387 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
389 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
390 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
391 my $placeholders = join ", ", ('?') x scalar @ids;
392 my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
393 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
395 WHERE id IN (${placeholders})|;
397 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
399 if (ref $params{id} eq 'ARRAY') {
400 $result = { map { $_->{id} => $_ } @{ $result } };
402 $result = $result->[0] || { 'id' => $params{id} };
405 $main::lxdebug->leave_sub();
410 sub search_contacts {
411 $::lxdebug->enter_sub;
416 my $dbh = $params{dbh} || $::form->get_standard_dbh;
419 'cp_name' => 'cp_name, cp_givenname',
420 'vcname' => 'vcname, cp_name, cp_givenname',
421 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
424 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);
426 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
427 $::form->{sort} = $order_by;
428 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
430 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
431 $order_by =~ s/,/ ${sortdir},/g;
432 $order_by .= " $sortdir";
434 my @where_tokens = ();
437 if ($params{search_term}) {
440 'cp.cp_name ILIKE ?',
441 'cp.cp_givenname ILIKE ?',
442 'cp.cp_email ILIKE ?';
443 push @values, (like($params{search_term})) x 3;
445 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
446 my $number = $params{search_term};
447 $number =~ s/[^\d]//g;
448 $number = join '[ /\(\)+\-]*', split(m//, $number);
450 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
453 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
456 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
457 'trans_id_field' => 'cp.cp_id',
458 'filter' => $params{filter});
461 push @where_tokens, $cvar_where;
462 push @values, @cvar_values;
465 if (my $filter = $params{filter}) {
466 for (qw(name title givenname email project abteilung)) {
467 next unless $filter->{"cp_$_"};
468 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
471 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
472 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
475 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
477 my $query = qq|SELECT cp.*,
478 COALESCE(c.id, v.id) AS vcid,
479 COALESCE(c.name, v.name) AS vcname,
480 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
481 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
483 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
484 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
488 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
490 $::lxdebug->leave_sub;
492 return @{ $contacts };