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 "insertdate" => "ct.itime",
80 "salesman" => "e.name",
81 "payment" => "pt.description"
84 $form->{sort} ||= "name";
86 if ( $join_records ) {
87 # in UNION case order by hash key, e.g. salesman
88 # the UNION created an implicit select around the result
89 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
91 # in not UNION case order by hash value, e.g. e.name
92 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
94 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
96 if ($sortorder !~ /(business|id|discount|itime)/ && !$join_records) {
97 $sortorder = "lower($sortorder) ${sortdir}";
99 $sortorder .= " ${sortdir}";
102 if ($form->{"${cv}number"}) {
103 $where .= " AND ct.${cv}number ILIKE ?";
104 push(@values, '%' . $form->{"${cv}number"} . '%');
107 foreach my $key (qw(name contact email)) {
109 $where .= " AND ct.$key ILIKE ?";
110 push(@values, '%' . $form->{$key} . '%');
114 if ($form->{cp_name}) {
115 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
116 push @values, '%' . $form->{cp_name} . '%';
119 if ($form->{addr_city}) {
120 $where .= " AND ((lower(ct.city) LIKE lower(?))
125 WHERE (sc.module = 'CT')
126 AND (lower(sc.shiptocity) LIKE lower(?))
129 push @values, ('%' . $form->{addr_city} . '%') x 2;
132 if ($form->{addr_country}) {
133 $where .= " AND ((lower(ct.country) LIKE lower(?))
138 WHERE (so.module = 'CT')
139 AND (lower(so.shiptocountry) LIKE lower(?))
142 push @values, ('%' . $form->{addr_country} . '%') x 2;
145 if ( $form->{status} eq 'orphaned' ) {
147 qq| AND ct.id NOT IN | .
148 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
149 if ($cv eq 'customer') {
151 qq| AND ct.id NOT IN | .
152 qq| (SELECT a.customer_id FROM ar a, customer cv | .
153 qq| WHERE cv.id = a.customer_id)|;
155 if ($cv eq 'vendor') {
157 qq| AND ct.id NOT IN | .
158 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
159 qq| WHERE cv.id = a.vendor_id)|;
161 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
164 if ($form->{obsolete} eq "Y") {
165 $where .= qq| AND ct.obsolete|;
166 } elsif ($form->{obsolete} eq "N") {
167 $where .= qq| AND NOT ct.obsolete|;
170 if ($form->{business_id}) {
171 $where .= qq| AND (ct.business_id = ?)|;
172 push(@values, conv_i($form->{business_id}));
175 if ($form->{salesman_id}) {
176 $where .= qq| AND (ct.salesman_id = ?)|;
177 push(@values, conv_i($form->{salesman_id}));
180 if($form->{insertdatefrom}) {
181 $where .= qq| AND (ct.itime::DATE >= ?)|;
182 push@values, conv_date($form->{insertdatefrom});
185 if($form->{insertdateto}) {
186 $where .= qq| AND (ct.itime::DATE <= ?)|;
187 push @values, conv_date($form->{insertdateto});
190 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
191 # Gilt nicht für Lieferanten
192 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
193 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
194 push(@values, $::myconfig{login});
197 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
198 'trans_id_field' => 'ct.id',
202 $where .= qq| AND ($cvar_where)|;
203 push @values, @cvar_values;
206 if ($form->{addr_street}) {
207 $where .= qq| AND (ct.street ILIKE ?)|;
208 push @values, '%' . $form->{addr_street} . '%';
211 if ($form->{addr_zipcode}) {
212 $where .= qq| AND (ct.zipcode ILIKE ?)|;
213 push @values, $form->{addr_zipcode} . '%';
217 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
218 qq| pt.description as payment | .
219 (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) .
221 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
222 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
223 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
226 my @saved_values = @values;
227 # redo for invoices, orders and quotations
231 if ($form->{l_invnumber}) {
232 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
233 my $module = $ar eq 'ar' ? 'is' : 'ir';
234 push(@values, @saved_values);
237 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
238 qq| pt.description as payment, | .
239 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
240 qq| '$module' AS module, 'invoice' AS formtype, | .
241 qq| (a.amount = a.paid) AS closed | .
243 qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
244 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
245 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
246 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
247 qq|WHERE $where AND (a.invoice = '1')|;
250 if ( $form->{l_ordnumber} ) {
251 push(@values, @saved_values);
254 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
255 qq| pt.description as payment, | .
256 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
257 qq| 'oe' AS module, 'order' AS formtype, o.closed | .
259 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
260 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
261 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
262 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
263 qq|WHERE $where AND (o.quotation = '0')|;
266 if ( $form->{l_quonumber} ) {
267 push(@values, @saved_values);
270 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
271 qq| pt.description as payment, | .
272 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
273 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
275 qq|JOIN oe o ON (o.${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) | .
279 qq|WHERE $where AND (o.quotation = '1')|;
283 $query .= qq| ORDER BY $sortorder|;
285 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
287 $main::lxdebug->leave_sub();
291 $main::lxdebug->enter_sub();
293 my ( $self, $myconfig, $form ) = @_;
295 die 'Missing argument: cp_id' unless $::form->{cp_id};
297 my $dbh = $form->dbconnect($myconfig);
299 qq|SELECT * FROM contacts c | .
300 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
301 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
302 my $ref = $sth->fetchrow_hashref("NAME_lc");
304 map { $form->{$_} = $ref->{$_} } keys %$ref;
306 $query = qq|SELECT COUNT(cp_id) AS used FROM (
307 SELECT cp_id FROM oe UNION
308 SELECT cp_id FROM ar UNION
309 SELECT cp_id FROM ap UNION
310 SELECT cp_id FROM delivery_orders
311 ) AS cpid WHERE cp_id = ? OR ? = 0|;
312 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
317 $main::lxdebug->leave_sub();
321 $main::lxdebug->enter_sub();
326 Common::check_params(\%params, qw(vc id));
328 my $myconfig = \%main::myconfig;
329 my $form = $main::form;
331 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
333 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
334 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
335 my $placeholders = join ", ", ('?') x scalar @ids;
336 my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
337 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
339 WHERE id IN (${placeholders})|;
341 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
343 if (ref $params{id} eq 'ARRAY') {
344 $result = { map { $_->{id} => $_ } @{ $result } };
346 $result = $result->[0] || { 'id' => $params{id} };
349 $main::lxdebug->leave_sub();
354 sub search_contacts {
355 $::lxdebug->enter_sub;
360 my $dbh = $params{dbh} || $::form->get_standard_dbh;
363 'cp_name' => 'cp_name, cp_givenname',
364 'vcname' => 'vcname, cp_name, cp_givenname',
365 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
368 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);
370 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
371 $::form->{sort} = $order_by;
372 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
374 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
375 $order_by =~ s/,/ ${sortdir},/g;
376 $order_by .= " $sortdir";
378 my @where_tokens = ();
381 if ($params{search_term}) {
384 'cp.cp_name ILIKE ?',
385 'cp.cp_givenname ILIKE ?',
386 'cp.cp_email ILIKE ?';
387 push @values, ('%' . $params{search_term} . '%') x 3;
389 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
390 my $number = $params{search_term};
391 $number =~ s/[^\d]//g;
392 $number = join '[ /\(\)+\-]*', split(m//, $number);
394 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
397 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
400 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
401 'trans_id_field' => 'cp.cp_id',
402 'filter' => $params{filter});
405 push @where_tokens, $cvar_where;
406 push @values, @cvar_values;
409 if (my $filter = $params{filter}) {
410 for (qw(name title givenname email project abteilung)) {
411 next unless $filter->{"cp_$_"};
412 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
415 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
416 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
419 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
421 my $query = qq|SELECT cp.*,
422 COALESCE(c.id, v.id) AS vcid,
423 COALESCE(c.name, v.name) AS vcname,
424 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
425 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
427 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
428 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
432 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
434 $::lxdebug->leave_sub;
436 return @{ $contacts };