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",
78 "discount" => "ct.discount",
79 "salesman" => "e.name",
80 "payment" => "pt.description"
83 $form->{sort} ||= "name";
85 if ( $join_records ) {
86 # in UNION case order by hash key, e.g. salesman
87 # the UNION created an implicit select around the result
88 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
90 # in not UNION case order by hash value, e.g. e.name
91 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
93 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
95 if ($sortorder !~ /(business|id|discount)/ && !$join_records) {
96 $sortorder = "lower($sortorder) ${sortdir}";
98 $sortorder .= " ${sortdir}";
101 if ($form->{"${cv}number"}) {
102 $where .= " AND ct.${cv}number ILIKE ?";
103 push(@values, '%' . $form->{"${cv}number"} . '%');
106 foreach my $key (qw(name contact email)) {
108 $where .= " AND ct.$key ILIKE ?";
109 push(@values, '%' . $form->{$key} . '%');
113 if ($form->{cp_name}) {
114 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
115 push @values, '%' . $form->{cp_name} . '%';
118 if ($form->{addr_city}) {
119 $where .= " AND ((lower(ct.city) LIKE lower(?))
124 WHERE (sc.module = 'CT')
125 AND (lower(sc.shiptocity) LIKE lower(?))
128 push @values, ('%' . $form->{addr_city} . '%') x 2;
131 if ($form->{addr_country}) {
132 $where .= " AND ((lower(ct.country) LIKE lower(?))
137 WHERE (so.module = 'CT')
138 AND (lower(so.shiptocountry) LIKE lower(?))
141 push @values, ('%' . $form->{addr_country} . '%') x 2;
144 if ( $form->{status} eq 'orphaned' ) {
146 qq| AND ct.id NOT IN | .
147 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
148 if ($cv eq 'customer') {
150 qq| AND ct.id NOT IN | .
151 qq| (SELECT a.customer_id FROM ar a, customer cv | .
152 qq| WHERE cv.id = a.customer_id)|;
154 if ($cv eq 'vendor') {
156 qq| AND ct.id NOT IN | .
157 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
158 qq| WHERE cv.id = a.vendor_id)|;
160 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
163 if ($form->{obsolete} eq "Y") {
164 $where .= qq| AND ct.obsolete|;
165 } elsif ($form->{obsolete} eq "N") {
166 $where .= qq| AND NOT ct.obsolete|;
169 if ($form->{business_id}) {
170 $where .= qq| AND (ct.business_id = ?)|;
171 push(@values, conv_i($form->{business_id}));
174 if ($form->{salesman_id}) {
175 $where .= qq| AND (ct.salesman_id = ?)|;
176 push(@values, conv_i($form->{salesman_id}));
179 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
180 # Gilt nicht für Lieferanten
181 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
182 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
183 push(@values, $form->{login});
186 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
187 'trans_id_field' => 'ct.id',
191 $where .= qq| AND ($cvar_where)|;
192 push @values, @cvar_values;
195 if ($form->{addr_street}) {
196 $where .= qq| AND (ct.street ILIKE ?)|;
197 push @values, '%' . $form->{addr_street} . '%';
200 if ($form->{addr_zipcode}) {
201 $where .= qq| AND (ct.zipcode ILIKE ?)|;
202 push @values, $form->{addr_zipcode} . '%';
206 qq|SELECT ct.*, b.description AS business, e.name as salesman, |.
207 qq| pt.description as payment | .
208 (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) .
210 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
211 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
212 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
215 my @saved_values = @values;
216 # redo for invoices, orders and quotations
220 if ($form->{l_invnumber}) {
221 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
222 my $module = $ar eq 'ar' ? 'is' : 'ir';
223 push(@values, @saved_values);
226 qq|SELECT ct.*, b.description AS business, e.name as salesman, |.
227 qq| pt.description as payment, | .
228 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
229 qq| '$module' AS module, 'invoice' AS formtype, | .
230 qq| (a.amount = a.paid) AS closed | .
232 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
233 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
234 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
235 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
236 qq|WHERE $where AND (a.invoice = '1')|;
239 if ( $form->{l_ordnumber} ) {
240 push(@values, @saved_values);
243 qq|SELECT ct.*, b.description AS business, e.name as salesman, |.
244 qq| pt.description as payment, | .
245 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
246 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
248 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
249 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
250 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
251 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
252 qq|WHERE $where AND (o.quotation = '0')|;
255 if ( $form->{l_quonumber} ) {
256 push(@values, @saved_values);
259 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
260 qq| pt.description as payment, | .
261 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
262 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
264 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
265 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
266 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
267 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
268 qq|WHERE $where AND (o.quotation = '1')|;
272 $query .= qq| ORDER BY $sortorder|;
274 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
276 $main::lxdebug->leave_sub();
280 $main::lxdebug->enter_sub();
282 my ( $self, $myconfig, $form ) = @_;
284 die 'Missing argument: cp_id' unless $::form->{cp_id};
286 my $dbh = $form->dbconnect($myconfig);
288 qq|SELECT * FROM contacts c | .
289 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
290 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
291 my $ref = $sth->fetchrow_hashref("NAME_lc");
293 map { $form->{$_} = $ref->{$_} } keys %$ref;
295 $query = qq|SELECT COUNT(cp_id) AS used FROM (
296 SELECT cp_id FROM oe UNION
297 SELECT cp_id FROM ar UNION
298 SELECT cp_id FROM ap UNION
299 SELECT cp_id FROM delivery_orders
300 ) AS cpid WHERE cp_id = ? OR ? = 0|;
301 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
306 $main::lxdebug->leave_sub();
310 $main::lxdebug->enter_sub();
315 Common::check_params(\%params, qw(vc id));
317 my $myconfig = \%main::myconfig;
318 my $form = $main::form;
320 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
322 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
323 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
324 my $placeholders = join ", ", ('?') x scalar @ids;
325 my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
326 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
328 WHERE id IN (${placeholders})|;
330 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
332 if (ref $params{id} eq 'ARRAY') {
333 $result = { map { $_->{id} => $_ } @{ $result } };
335 $result = $result->[0] || { 'id' => $params{id} };
338 $main::lxdebug->leave_sub();
343 sub search_contacts {
344 $::lxdebug->enter_sub;
349 my $dbh = $params{dbh} || $::form->get_standard_dbh;
352 'cp_name' => 'cp_name, cp_givenname',
353 'vcname' => 'vcname, cp_name, cp_givenname',
354 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
357 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);
359 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
360 $::form->{sort} = $order_by;
361 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
363 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
364 $order_by =~ s/,/ ${sortdir},/g;
365 $order_by .= " $sortdir";
367 my @where_tokens = ();
370 if ($params{search_term}) {
373 'cp.cp_name ILIKE ?',
374 'cp.cp_givenname ILIKE ?',
375 'cp.cp_email ILIKE ?';
376 push @values, ('%' . $params{search_term} . '%') x 3;
378 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
379 my $number = $params{search_term};
380 $number =~ s/[^\d]//g;
381 $number = join '[ /\(\)+\-]*', split(m//, $number);
383 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
386 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
389 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
390 'trans_id_field' => 'cp.cp_id',
391 'filter' => $params{filter});
394 push @where_tokens, $cvar_where;
395 push @values, @cvar_values;
398 if (my $filter = $params{filter}) {
399 for (qw(name title givenname email project abteilung)) {
400 next unless $filter->{"cp_$_"};
401 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
404 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
405 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
408 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
410 my $query = qq|SELECT cp.*,
411 COALESCE(c.id, v.id) AS vcid,
412 COALESCE(c.name, v.name) AS vcname,
413 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
414 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
416 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
417 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
421 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
423 $::lxdebug->leave_sub;
425 return @{ $contacts };