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 #======================================================================
49 $main::lxdebug->enter_sub();
51 my ( $self, $myconfig, $form ) = @_;
54 my $dbh = $form->dbconnect($myconfig);
56 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
57 my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
62 my %allowed_sort_columns = (
64 "customernumber" => "ct.customernumber",
65 "vendornumber" => "ct.vendornumber",
67 "contact" => "ct.contact",
68 "phone" => "ct.phone",
70 "email" => "ct.email",
71 "street" => "ct.street",
72 "taxnumber" => "ct.taxnumber",
73 "business" => "b.description",
74 "invnumber" => "ct.invnumber",
75 "ordnumber" => "ct.ordnumber",
76 "quonumber" => "ct.quonumber",
77 "zipcode" => "ct.zipcode",
79 "country" => "ct.country",
81 "discount" => "ct.discount",
82 "insertdate" => "ct.itime",
83 "salesman" => "e.name",
84 "payment" => "pt.description",
85 "pricegroup" => "pg.pricegroup",
88 $form->{sort} ||= "name";
90 if ( $join_records ) {
91 # in UNION case order by hash key, e.g. salesman
92 # the UNION created an implicit select around the result
93 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
95 # in not UNION case order by hash value, e.g. e.name
96 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
98 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
100 if ($sortorder !~ /(business|id|discount|itime)/ && !$join_records) {
101 $sortorder = "lower($sortorder) ${sortdir}";
103 $sortorder .= " ${sortdir}";
106 if ($form->{"${cv}number"}) {
107 $where .= " AND ct.${cv}number ILIKE ?";
108 push(@values, like($form->{"${cv}number"}));
111 foreach my $key (qw(name contact email)) {
113 $where .= " AND ct.$key ILIKE ?";
114 push(@values, like($form->{$key}));
118 if ($form->{cp_name}) {
119 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
120 push @values, like($form->{cp_name});
123 if ($form->{addr_street}) {
124 $where .= qq| AND ((ct.street ILIKE ?) | .
127 qq| SELECT sc.trans_id FROM shipto sc | .
128 qq| WHERE (sc.module = 'CT') | .
129 qq| AND (sc.shiptostreet ILIKE ?) | .
131 push @values, (like($form->{addr_street})) x 2;
134 if ($form->{addr_zipcode}) {
135 $where .= qq| AND ((ct.zipcode ILIKE ?) | .
138 qq| SELECT sc.trans_id FROM shipto sc | .
139 qq| WHERE (sc.module = 'CT') | .
140 qq| AND (sc.shiptozipcode ILIKE ?) | .
142 push @values, (like($form->{addr_zipcode})) x 2;
145 if ($form->{addr_city}) {
146 $where .= " AND ((lower(ct.city) LIKE lower(?))
151 WHERE (sc.module = 'CT')
152 AND (lower(sc.shiptocity) LIKE lower(?))
155 push @values, (like($form->{addr_city})) x 2;
158 if ($form->{addr_country}) {
159 $where .= " AND ((lower(ct.country) LIKE lower(?))
164 WHERE (so.module = 'CT')
165 AND (lower(so.shiptocountry) LIKE lower(?))
168 push @values, (like($form->{addr_country})) x 2;
171 if ($form->{addr_gln}) {
172 $where .= " AND ((lower(ct.gln) LIKE lower(?))
177 WHERE (so.module = 'CT')
178 AND (lower(so.shiptogln) LIKE lower(?))
181 push @values, (like($form->{addr_gln})) x 2;
184 if ( $form->{status} eq 'orphaned' ) {
186 qq| AND ct.id NOT IN | .
187 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
188 if ($cv eq 'customer') {
190 qq| AND ct.id NOT IN | .
191 qq| (SELECT a.customer_id FROM ar a, customer cv | .
192 qq| WHERE cv.id = a.customer_id)|;
194 if ($cv eq 'vendor') {
196 qq| AND ct.id NOT IN | .
197 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
198 qq| WHERE cv.id = a.vendor_id)|;
200 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
203 if ($form->{obsolete} eq "Y") {
204 $where .= qq| AND ct.obsolete|;
205 } elsif ($form->{obsolete} eq "N") {
206 $where .= qq| AND NOT ct.obsolete|;
209 if ($form->{business_id}) {
210 $where .= qq| AND (ct.business_id = ?)|;
211 push(@values, conv_i($form->{business_id}));
214 if ($form->{salesman_id}) {
215 $where .= qq| AND (ct.salesman_id = ?)|;
216 push(@values, conv_i($form->{salesman_id}));
219 if($form->{insertdatefrom}) {
220 $where .= qq| AND (ct.itime::DATE >= ?)|;
221 push@values, conv_date($form->{insertdatefrom});
224 if($form->{insertdateto}) {
225 $where .= qq| AND (ct.itime::DATE <= ?)|;
226 push @values, conv_date($form->{insertdateto});
230 my @tokens = parse_line('\s+', 0, $form->{all});
232 ct.${cv}number ILIKE ? OR
235 push @values, ("%$_%")x2 for @tokens;
238 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
239 # Gilt nicht für Lieferanten
240 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
241 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
242 push(@values, $::myconfig{login});
245 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
246 'trans_id_field' => 'ct.id',
250 $where .= qq| AND ($cvar_where)|;
251 push @values, @cvar_values;
254 my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
255 my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
257 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
258 qq| pt.description as payment | .
260 (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) .
262 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
263 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
264 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
268 my @saved_values = @values;
269 # redo for invoices, orders and quotations
273 if ($form->{l_invnumber}) {
274 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
275 my $module = $ar eq 'ar' ? 'is' : 'ir';
276 push(@values, @saved_values);
279 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
280 qq| pt.description as payment | .
282 qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
283 qq| '$module' AS module, 'invoice' AS formtype, | .
284 qq| (a.amount = a.paid) AS closed | .
286 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
287 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
288 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
289 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
291 qq|WHERE $where AND (a.invoice = '1')|;
294 if ( $form->{l_ordnumber} ) {
295 push(@values, @saved_values);
298 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
299 qq| pt.description as payment | .
301 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
302 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
304 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
305 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
306 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
307 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
309 qq|WHERE $where AND (o.quotation = '0')|;
312 if ( $form->{l_quonumber} ) {
313 push(@values, @saved_values);
316 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
317 qq| pt.description as payment | .
319 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
320 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
322 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
323 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
324 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
325 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
327 qq|WHERE $where AND (o.quotation = '1')|;
331 $query .= qq| ORDER BY $sortorder|;
333 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
335 $main::lxdebug->leave_sub();
339 $main::lxdebug->enter_sub();
341 my ( $self, $myconfig, $form ) = @_;
343 die 'Missing argument: cp_id' unless $::form->{cp_id};
345 my $dbh = SL::DB->client->dbh;
347 qq|SELECT * FROM contacts c | .
348 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
349 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
350 my $ref = $sth->fetchrow_hashref("NAME_lc");
352 map { $form->{$_} = $ref->{$_} } keys %$ref;
354 $query = qq|SELECT COUNT(cp_id) AS used FROM (
355 SELECT cp_id FROM oe UNION
356 SELECT cp_id FROM ar UNION
357 SELECT cp_id FROM ap UNION
358 SELECT cp_id FROM delivery_orders
359 ) AS cpid WHERE cp_id = ? OR ? = 0|;
360 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
364 $main::lxdebug->leave_sub();
368 $main::lxdebug->enter_sub();
373 Common::check_params(\%params, qw(vc id));
375 my $myconfig = \%main::myconfig;
376 my $form = $main::form;
378 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
380 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
381 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
382 my $placeholders = join ", ", ('?') x scalar @ids;
383 my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
384 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
386 WHERE id IN (${placeholders})|;
388 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
390 if (ref $params{id} eq 'ARRAY') {
391 $result = { map { $_->{id} => $_ } @{ $result } };
393 $result = $result->[0] || { 'id' => $params{id} };
396 $main::lxdebug->leave_sub();
401 sub search_contacts {
402 $::lxdebug->enter_sub;
407 my $dbh = $params{dbh} || $::form->get_standard_dbh;
410 'cp_name' => 'cp_name, cp_givenname',
411 'vcname' => 'vcname, cp_name, cp_givenname',
412 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
415 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);
417 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
418 $::form->{sort} = $order_by;
419 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
421 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
422 $order_by =~ s/,/ ${sortdir},/g;
423 $order_by .= " $sortdir";
425 my @where_tokens = ();
428 if ($params{search_term}) {
431 'cp.cp_name ILIKE ?',
432 'cp.cp_givenname ILIKE ?',
433 'cp.cp_email ILIKE ?';
434 push @values, (like($params{search_term})) x 3;
436 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
437 my $number = $params{search_term};
438 $number =~ s/[^\d]//g;
439 $number = join '[ /\(\)+\-]*', split(m//, $number);
441 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
444 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
447 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
448 'trans_id_field' => 'cp.cp_id',
449 'filter' => $params{filter});
452 push @where_tokens, $cvar_where;
453 push @values, @cvar_values;
456 if (my $filter = $params{filter}) {
457 for (qw(name title givenname email project abteilung)) {
458 next unless $filter->{"cp_$_"};
459 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
462 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
463 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
466 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
468 my $query = qq|SELECT cp.*,
469 COALESCE(c.id, v.id) AS vcid,
470 COALESCE(c.name, v.name) AS vcname,
471 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
472 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
474 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
475 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
479 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
481 $::lxdebug->leave_sub;
483 return @{ $contacts };