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 #======================================================================
48 $main::lxdebug->enter_sub();
50 my ( $self, $myconfig, $form ) = @_;
53 my $dbh = $form->dbconnect($myconfig);
55 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
56 my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
61 my %allowed_sort_columns = (
63 "customernumber" => "ct.customernumber",
64 "vendornumber" => "ct.vendornumber",
66 "contact" => "ct.contact",
67 "phone" => "ct.phone",
69 "email" => "ct.email",
70 "street" => "ct.street",
71 "taxnumber" => "ct.taxnumber",
72 "business" => "b.description",
73 "invnumber" => "ct.invnumber",
74 "ordnumber" => "ct.ordnumber",
75 "quonumber" => "ct.quonumber",
76 "zipcode" => "ct.zipcode",
78 "country" => "ct.country",
80 "discount" => "ct.discount",
81 "insertdate" => "ct.itime",
82 "salesman" => "e.name",
83 "payment" => "pt.description",
84 "pricegroup" => "pg.pricegroup",
87 $form->{sort} ||= "name";
89 if ( $join_records ) {
90 # in UNION case order by hash key, e.g. salesman
91 # the UNION created an implicit select around the result
92 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
94 # in not UNION case order by hash value, e.g. e.name
95 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
97 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
99 if ($sortorder !~ /(business|id|discount|itime)/ && !$join_records) {
100 $sortorder = "lower($sortorder) ${sortdir}";
102 $sortorder .= " ${sortdir}";
105 if ($form->{"${cv}number"}) {
106 $where .= " AND ct.${cv}number ILIKE ?";
107 push(@values, like($form->{"${cv}number"}));
110 foreach my $key (qw(name contact email)) {
112 $where .= " AND ct.$key ILIKE ?";
113 push(@values, like($form->{$key}));
117 if ($form->{cp_name}) {
118 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
119 push @values, like($form->{cp_name});
122 if ($form->{addr_street}) {
123 $where .= qq| AND ((ct.street ILIKE ?) | .
126 qq| SELECT sc.trans_id FROM shipto sc | .
127 qq| WHERE (sc.module = 'CT') | .
128 qq| AND (sc.shiptostreet ILIKE ?) | .
130 push @values, (like($form->{addr_street})) x 2;
133 if ($form->{addr_zipcode}) {
134 $where .= qq| AND ((ct.zipcode ILIKE ?) | .
137 qq| SELECT sc.trans_id FROM shipto sc | .
138 qq| WHERE (sc.module = 'CT') | .
139 qq| AND (sc.shiptozipcode ILIKE ?) | .
141 push @values, (like($form->{addr_zipcode})) x 2;
144 if ($form->{addr_city}) {
145 $where .= " AND ((lower(ct.city) LIKE lower(?))
150 WHERE (sc.module = 'CT')
151 AND (lower(sc.shiptocity) LIKE lower(?))
154 push @values, (like($form->{addr_city})) x 2;
157 if ($form->{addr_country}) {
158 $where .= " AND ((lower(ct.country) LIKE lower(?))
163 WHERE (so.module = 'CT')
164 AND (lower(so.shiptocountry) LIKE lower(?))
167 push @values, (like($form->{addr_country})) x 2;
170 if ($form->{addr_gln}) {
171 $where .= " AND ((lower(ct.gln) LIKE lower(?))
176 WHERE (so.module = 'CT')
177 AND (lower(so.shiptogln) LIKE lower(?))
180 push @values, (like($form->{addr_gln})) x 2;
183 if ( $form->{status} eq 'orphaned' ) {
185 qq| AND ct.id NOT IN | .
186 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
187 if ($cv eq 'customer') {
189 qq| AND ct.id NOT IN | .
190 qq| (SELECT a.customer_id FROM ar a, customer cv | .
191 qq| WHERE cv.id = a.customer_id)|;
193 if ($cv eq 'vendor') {
195 qq| AND ct.id NOT IN | .
196 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
197 qq| WHERE cv.id = a.vendor_id)|;
199 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
202 if ($form->{obsolete} eq "Y") {
203 $where .= qq| AND ct.obsolete|;
204 } elsif ($form->{obsolete} eq "N") {
205 $where .= qq| AND NOT ct.obsolete|;
208 if ($form->{business_id}) {
209 $where .= qq| AND (ct.business_id = ?)|;
210 push(@values, conv_i($form->{business_id}));
213 if ($form->{salesman_id}) {
214 $where .= qq| AND (ct.salesman_id = ?)|;
215 push(@values, conv_i($form->{salesman_id}));
218 if($form->{insertdatefrom}) {
219 $where .= qq| AND (ct.itime::DATE >= ?)|;
220 push@values, conv_date($form->{insertdatefrom});
223 if($form->{insertdateto}) {
224 $where .= qq| AND (ct.itime::DATE <= ?)|;
225 push @values, conv_date($form->{insertdateto});
229 my @tokens = parse_line('\s+', 0, $form->{all});
231 ct.${cv}number ILIKE ? OR
234 push @values, ("%$_%")x2 for @tokens;
237 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
238 # Gilt nicht für Lieferanten
239 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
240 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
241 push(@values, $::myconfig{login});
244 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
245 'trans_id_field' => 'ct.id',
249 $where .= qq| AND ($cvar_where)|;
250 push @values, @cvar_values;
253 my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
254 my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
256 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
257 qq| pt.description as payment | .
259 (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) .
261 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
262 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
263 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
267 my @saved_values = @values;
268 # redo for invoices, orders and quotations
272 if ($form->{l_invnumber}) {
273 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
274 my $module = $ar eq 'ar' ? 'is' : 'ir';
275 push(@values, @saved_values);
278 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
279 qq| pt.description as payment | .
281 qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
282 qq| '$module' AS module, 'invoice' AS formtype, | .
283 qq| (a.amount = a.paid) AS closed | .
285 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
286 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
287 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
288 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
290 qq|WHERE $where AND (a.invoice = '1')|;
293 if ( $form->{l_ordnumber} ) {
294 push(@values, @saved_values);
297 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
298 qq| pt.description as payment | .
300 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
301 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
303 qq|JOIN oe o ON (o.${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 (o.quotation = '0')|;
311 if ( $form->{l_quonumber} ) {
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, 'quotation' 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 = '1')|;
330 $query .= qq| ORDER BY $sortorder|;
332 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
334 $main::lxdebug->leave_sub();
338 $main::lxdebug->enter_sub();
340 my ( $self, $myconfig, $form ) = @_;
342 die 'Missing argument: cp_id' unless $::form->{cp_id};
344 my $dbh = $form->dbconnect($myconfig);
346 qq|SELECT * FROM contacts c | .
347 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
348 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
349 my $ref = $sth->fetchrow_hashref("NAME_lc");
351 map { $form->{$_} = $ref->{$_} } keys %$ref;
353 $query = qq|SELECT COUNT(cp_id) AS used FROM (
354 SELECT cp_id FROM oe UNION
355 SELECT cp_id FROM ar UNION
356 SELECT cp_id FROM ap UNION
357 SELECT cp_id FROM delivery_orders
358 ) AS cpid WHERE cp_id = ? OR ? = 0|;
359 ($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 };