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 #======================================================================
47 $main::lxdebug->enter_sub();
49 my ( $self, $myconfig, $form ) = @_;
52 my $dbh = $form->dbconnect($myconfig);
54 my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
55 my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
60 my %allowed_sort_columns = (
62 "customernumber" => "ct.customernumber",
63 "vendornumber" => "ct.vendornumber",
65 "contact" => "ct.contact",
66 "phone" => "ct.phone",
68 "email" => "ct.email",
69 "street" => "ct.street",
70 "taxnumber" => "ct.taxnumber",
71 "business" => "b.description",
72 "invnumber" => "ct.invnumber",
73 "ordnumber" => "ct.ordnumber",
74 "quonumber" => "ct.quonumber",
75 "zipcode" => "ct.zipcode",
77 "country" => "ct.country",
79 "discount" => "ct.discount",
80 "insertdate" => "ct.itime",
81 "salesman" => "e.name",
82 "payment" => "pt.description",
83 "pricegroup" => "pg.pricegroup",
86 $form->{sort} ||= "name";
88 if ( $join_records ) {
89 # in UNION case order by hash key, e.g. salesman
90 # the UNION created an implicit select around the result
91 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
93 # in not UNION case order by hash value, e.g. e.name
94 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
96 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
98 if ($sortorder !~ /(business|id|discount|itime)/ && !$join_records) {
99 $sortorder = "lower($sortorder) ${sortdir}";
101 $sortorder .= " ${sortdir}";
104 if ($form->{"${cv}number"}) {
105 $where .= " AND ct.${cv}number ILIKE ?";
106 push(@values, '%' . $form->{"${cv}number"} . '%');
109 foreach my $key (qw(name contact email)) {
111 $where .= " AND ct.$key ILIKE ?";
112 push(@values, '%' . $form->{$key} . '%');
116 if ($form->{cp_name}) {
117 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
118 push @values, '%' . $form->{cp_name} . '%';
121 if ($form->{addr_street}) {
122 $where .= qq| AND ((ct.street ILIKE ?) | .
125 qq| SELECT sc.trans_id FROM shipto sc | .
126 qq| WHERE (sc.module = 'CT') | .
127 qq| AND (sc.shiptostreet ILIKE ?) | .
129 push @values, ('%' . $form->{addr_street} . '%') x 2;
132 if ($form->{addr_zipcode}) {
133 $where .= qq| AND ((ct.zipcode ILIKE ?) | .
136 qq| SELECT sc.trans_id FROM shipto sc | .
137 qq| WHERE (sc.module = 'CT') | .
138 qq| AND (sc.shiptozipcode ILIKE ?) | .
140 push @values, ('%' . $form->{addr_zipcode} . '%') x 2;
143 if ($form->{addr_city}) {
144 $where .= " AND ((lower(ct.city) LIKE lower(?))
149 WHERE (sc.module = 'CT')
150 AND (lower(sc.shiptocity) LIKE lower(?))
153 push @values, ('%' . $form->{addr_city} . '%') x 2;
156 if ($form->{addr_country}) {
157 $where .= " AND ((lower(ct.country) LIKE lower(?))
162 WHERE (so.module = 'CT')
163 AND (lower(so.shiptocountry) LIKE lower(?))
166 push @values, ('%' . $form->{addr_country} . '%') x 2;
169 if ($form->{addr_gln}) {
170 $where .= " AND ((lower(ct.gln) LIKE lower(?))
175 WHERE (so.module = 'CT')
176 AND (lower(so.shiptogln) LIKE lower(?))
179 push @values, ('%' . $form->{addr_gln} . '%') x 2;
182 if ( $form->{status} eq 'orphaned' ) {
184 qq| AND ct.id NOT IN | .
185 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
186 if ($cv eq 'customer') {
188 qq| AND ct.id NOT IN | .
189 qq| (SELECT a.customer_id FROM ar a, customer cv | .
190 qq| WHERE cv.id = a.customer_id)|;
192 if ($cv eq 'vendor') {
194 qq| AND ct.id NOT IN | .
195 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
196 qq| WHERE cv.id = a.vendor_id)|;
198 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
201 if ($form->{obsolete} eq "Y") {
202 $where .= qq| AND ct.obsolete|;
203 } elsif ($form->{obsolete} eq "N") {
204 $where .= qq| AND NOT ct.obsolete|;
207 if ($form->{business_id}) {
208 $where .= qq| AND (ct.business_id = ?)|;
209 push(@values, conv_i($form->{business_id}));
212 if ($form->{salesman_id}) {
213 $where .= qq| AND (ct.salesman_id = ?)|;
214 push(@values, conv_i($form->{salesman_id}));
217 if($form->{insertdatefrom}) {
218 $where .= qq| AND (ct.itime::DATE >= ?)|;
219 push@values, conv_date($form->{insertdatefrom});
222 if($form->{insertdateto}) {
223 $where .= qq| AND (ct.itime::DATE <= ?)|;
224 push @values, conv_date($form->{insertdateto});
227 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
228 # Gilt nicht für Lieferanten
229 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
230 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
231 push(@values, $::myconfig{login});
234 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
235 'trans_id_field' => 'ct.id',
239 $where .= qq| AND ($cvar_where)|;
240 push @values, @cvar_values;
243 my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
244 my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.klass = pg.id) | : '';
246 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
247 qq| pt.description as payment | .
249 (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) .
251 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
252 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
253 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
257 my @saved_values = @values;
258 # redo for invoices, orders and quotations
262 if ($form->{l_invnumber}) {
263 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
264 my $module = $ar eq 'ar' ? 'is' : 'ir';
265 push(@values, @saved_values);
268 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
269 qq| pt.description as payment | .
271 qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
272 qq| '$module' AS module, 'invoice' AS formtype, | .
273 qq| (a.amount = a.paid) AS closed | .
275 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
276 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
277 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
278 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
280 qq|WHERE $where AND (a.invoice = '1')|;
283 if ( $form->{l_ordnumber} ) {
284 push(@values, @saved_values);
287 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
288 qq| pt.description as payment | .
290 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
291 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
293 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
294 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
295 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
296 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
298 qq|WHERE $where AND (o.quotation = '0')|;
301 if ( $form->{l_quonumber} ) {
302 push(@values, @saved_values);
305 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
306 qq| pt.description as payment | .
308 qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
309 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
311 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
312 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
313 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
314 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
316 qq|WHERE $where AND (o.quotation = '1')|;
320 $query .= qq| ORDER BY $sortorder|;
322 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
324 $main::lxdebug->leave_sub();
328 $main::lxdebug->enter_sub();
330 my ( $self, $myconfig, $form ) = @_;
332 die 'Missing argument: cp_id' unless $::form->{cp_id};
334 my $dbh = $form->dbconnect($myconfig);
336 qq|SELECT * FROM contacts c | .
337 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
338 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
339 my $ref = $sth->fetchrow_hashref("NAME_lc");
341 map { $form->{$_} = $ref->{$_} } keys %$ref;
343 $query = qq|SELECT COUNT(cp_id) AS used FROM (
344 SELECT cp_id FROM oe UNION
345 SELECT cp_id FROM ar UNION
346 SELECT cp_id FROM ap UNION
347 SELECT cp_id FROM delivery_orders
348 ) AS cpid WHERE cp_id = ? OR ? = 0|;
349 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
354 $main::lxdebug->leave_sub();
358 $main::lxdebug->enter_sub();
363 Common::check_params(\%params, qw(vc id));
365 my $myconfig = \%main::myconfig;
366 my $form = $main::form;
368 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
370 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
371 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
372 my $placeholders = join ", ", ('?') x scalar @ids;
373 my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
374 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
376 WHERE id IN (${placeholders})|;
378 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
380 if (ref $params{id} eq 'ARRAY') {
381 $result = { map { $_->{id} => $_ } @{ $result } };
383 $result = $result->[0] || { 'id' => $params{id} };
386 $main::lxdebug->leave_sub();
391 sub search_contacts {
392 $::lxdebug->enter_sub;
397 my $dbh = $params{dbh} || $::form->get_standard_dbh;
400 'cp_name' => 'cp_name, cp_givenname',
401 'vcname' => 'vcname, cp_name, cp_givenname',
402 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
405 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);
407 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
408 $::form->{sort} = $order_by;
409 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
411 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
412 $order_by =~ s/,/ ${sortdir},/g;
413 $order_by .= " $sortdir";
415 my @where_tokens = ();
418 if ($params{search_term}) {
421 'cp.cp_name ILIKE ?',
422 'cp.cp_givenname ILIKE ?',
423 'cp.cp_email ILIKE ?';
424 push @values, ('%' . $params{search_term} . '%') x 3;
426 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
427 my $number = $params{search_term};
428 $number =~ s/[^\d]//g;
429 $number = join '[ /\(\)+\-]*', split(m//, $number);
431 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
434 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
437 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
438 'trans_id_field' => 'cp.cp_id',
439 'filter' => $params{filter});
442 push @where_tokens, $cvar_where;
443 push @values, @cvar_values;
446 if (my $filter = $params{filter}) {
447 for (qw(name title givenname email project abteilung)) {
448 next unless $filter->{"cp_$_"};
449 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
452 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
453 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
456 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
458 my $query = qq|SELECT cp.*,
459 COALESCE(c.id, v.id) AS vcid,
460 COALESCE(c.name, v.name) AS vcname,
461 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
462 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
464 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
465 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
469 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
471 $::lxdebug->leave_sub;
473 return @{ $contacts };