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" => "ct.business",
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"
82 $form->{sort} ||= "name";
84 if ( $join_records ) {
85 # in UNION case order by hash key, e.g. salesman
86 # the UNION created an implicit select around the result
87 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
89 # in not UNION case order by hash value, e.g. e.name
90 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
92 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
94 if ($sortorder !~ /(business|id|discount)/ && !$join_records) {
95 $sortorder = "lower($sortorder) ${sortdir}";
97 $sortorder .= " ${sortdir}";
100 if ($form->{"${cv}number"}) {
101 $where .= " AND ct.${cv}number ILIKE ?";
102 push(@values, '%' . $form->{"${cv}number"} . '%');
105 foreach my $key (qw(name contact email)) {
107 $where .= " AND ct.$key ILIKE ?";
108 push(@values, '%' . $form->{$key} . '%');
112 if ($form->{cp_name}) {
113 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
114 push @values, '%' . $form->{cp_name} . '%';
117 if ($form->{addr_city}) {
118 $where .= " AND ((lower(ct.city) LIKE lower(?))
123 WHERE (sc.module = 'CT')
124 AND (lower(sc.shiptocity) LIKE lower(?))
127 push @values, ('%' . $form->{addr_city} . '%') x 2;
130 if ($form->{addr_country}) {
131 $where .= " AND ((lower(ct.country) LIKE lower(?))
136 WHERE (so.module = 'CT')
137 AND (lower(so.shiptocountry) LIKE lower(?))
140 push @values, ('%' . $form->{addr_country} . '%') x 2;
143 if ( $form->{status} eq 'orphaned' ) {
145 qq| AND ct.id NOT IN | .
146 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
147 if ($cv eq 'customer') {
149 qq| AND ct.id NOT IN | .
150 qq| (SELECT a.customer_id FROM ar a, customer cv | .
151 qq| WHERE cv.id = a.customer_id)|;
153 if ($cv eq 'vendor') {
155 qq| AND ct.id NOT IN | .
156 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
157 qq| WHERE cv.id = a.vendor_id)|;
159 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
162 if ($form->{obsolete} eq "Y") {
163 $where .= qq| AND ct.obsolete|;
164 } elsif ($form->{obsolete} eq "N") {
165 $where .= qq| AND NOT ct.obsolete|;
168 if ($form->{business_id}) {
169 $where .= qq| AND (ct.business_id = ?)|;
170 push(@values, conv_i($form->{business_id}));
173 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
174 # Gilt nicht für Lieferanten
175 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
176 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
177 push(@values, $form->{login});
180 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
181 'trans_id_field' => 'ct.id',
185 $where .= qq| AND ($cvar_where)|;
186 push @values, @cvar_values;
189 if ($form->{addr_street}) {
190 $where .= qq| AND (ct.street ILIKE ?)|;
191 push @values, '%' . $form->{addr_street} . '%';
194 if ($form->{addr_zipcode}) {
195 $where .= qq| AND (ct.zipcode ILIKE ?)|;
196 push @values, $form->{addr_zipcode} . '%';
200 qq|SELECT ct.*, b.description AS business, e.name as salesman | .
201 (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) .
203 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
204 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
207 my @saved_values = @values;
208 # redo for invoices, orders and quotations
212 if ($form->{l_invnumber}) {
213 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
214 my $module = $ar eq 'ar' ? 'is' : 'ir';
215 push(@values, @saved_values);
218 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
219 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
220 qq| '$module' AS module, 'invoice' AS formtype, | .
221 qq| (a.amount = a.paid) AS closed | .
223 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
224 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
225 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
226 qq|WHERE $where AND (a.invoice = '1')|;
229 if ( $form->{l_ordnumber} ) {
230 push(@values, @saved_values);
233 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
234 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
235 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
237 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
238 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
239 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
240 qq|WHERE $where AND (o.quotation = '0')|;
243 if ( $form->{l_quonumber} ) {
244 push(@values, @saved_values);
247 qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
248 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
249 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
251 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
252 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
253 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
254 qq|WHERE $where AND (o.quotation = '1')|;
258 $query .= qq| ORDER BY $sortorder|;
260 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
262 $main::lxdebug->leave_sub();
266 $main::lxdebug->enter_sub();
268 my ( $self, $myconfig, $form ) = @_;
270 die 'Missing argument: cp_id' unless $::form->{cp_id};
272 my $dbh = $form->dbconnect($myconfig);
274 qq|SELECT * FROM contacts c | .
275 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
276 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
277 my $ref = $sth->fetchrow_hashref("NAME_lc");
279 map { $form->{$_} = $ref->{$_} } keys %$ref;
281 $query = qq|SELECT COUNT(cp_id) AS used FROM (
282 SELECT cp_id FROM oe UNION
283 SELECT cp_id FROM ar UNION
284 SELECT cp_id FROM ap UNION
285 SELECT cp_id FROM delivery_orders
286 ) AS cpid WHERE cp_id = ? OR ? = 0|;
287 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
292 $main::lxdebug->leave_sub();
296 $main::lxdebug->enter_sub();
301 Common::check_params(\%params, qw(vc id));
303 my $myconfig = \%main::myconfig;
304 my $form = $main::form;
306 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
308 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
309 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
310 my $placeholders = join ", ", ('?') x scalar @ids;
311 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic
313 WHERE id IN (${placeholders})|;
315 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
317 if (ref $params{id} eq 'ARRAY') {
318 $result = { map { $_->{id} => $_ } @{ $result } };
320 $result = $result->[0] || { 'id' => $params{id} };
323 $main::lxdebug->leave_sub();
328 sub search_contacts {
329 $::lxdebug->enter_sub;
334 my $dbh = $params{dbh} || $::form->get_standard_dbh;
337 'cp_name' => 'cp_name, cp_givenname',
338 'vcname' => 'vcname, cp_name, cp_givenname',
339 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
342 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);
344 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
345 $::form->{sort} = $order_by;
346 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
348 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
349 $order_by =~ s/,/ ${sortdir},/g;
350 $order_by .= " $sortdir";
352 my @where_tokens = ();
355 if ($params{search_term}) {
358 'cp.cp_name ILIKE ?',
359 'cp.cp_givenname ILIKE ?',
360 'cp.cp_email ILIKE ?';
361 push @values, ('%' . $params{search_term} . '%') x 3;
363 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
364 my $number = $params{search_term};
365 $number =~ s/[^\d]//g;
366 $number = join '[ /\(\)+\-]*', split(m//, $number);
368 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
371 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
374 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
375 'trans_id_field' => 'cp.cp_id',
376 'filter' => $params{filter});
379 push @where_tokens, $cvar_where;
380 push @values, @cvar_values;
383 if (my $filter = $params{filter}) {
384 for (qw(name title givenname email project abteilung)) {
385 next unless $filter->{"cp_$_"};
386 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
389 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
390 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
393 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
395 my $query = qq|SELECT cp.*,
396 COALESCE(c.id, v.id) AS vcid,
397 COALESCE(c.name, v.name) AS vcname,
398 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
399 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
401 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
402 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
406 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
408 $::lxdebug->leave_sub;
410 return @{ $contacts };