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",
82 "pricegroup" => "pg.pricegroup",
85 $form->{sort} ||= "name";
87 if ( $join_records ) {
88 # in UNION case order by hash key, e.g. salesman
89 # the UNION created an implicit select around the result
90 $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
92 # in not UNION case order by hash value, e.g. e.name
93 $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
95 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
97 if ($sortorder !~ /(business|id|discount|itime)/ && !$join_records) {
98 $sortorder = "lower($sortorder) ${sortdir}";
100 $sortorder .= " ${sortdir}";
103 if ($form->{"${cv}number"}) {
104 $where .= " AND ct.${cv}number ILIKE ?";
105 push(@values, '%' . $form->{"${cv}number"} . '%');
108 foreach my $key (qw(name contact email)) {
110 $where .= " AND ct.$key ILIKE ?";
111 push(@values, '%' . $form->{$key} . '%');
115 if ($form->{cp_name}) {
116 $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
117 push @values, '%' . $form->{cp_name} . '%';
120 if ($form->{addr_city}) {
121 $where .= " AND ((lower(ct.city) LIKE lower(?))
126 WHERE (sc.module = 'CT')
127 AND (lower(sc.shiptocity) LIKE lower(?))
130 push @values, ('%' . $form->{addr_city} . '%') x 2;
133 if ($form->{addr_country}) {
134 $where .= " AND ((lower(ct.country) LIKE lower(?))
139 WHERE (so.module = 'CT')
140 AND (lower(so.shiptocountry) LIKE lower(?))
143 push @values, ('%' . $form->{addr_country} . '%') x 2;
146 if ( $form->{status} eq 'orphaned' ) {
148 qq| AND ct.id NOT IN | .
149 qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
150 if ($cv eq 'customer') {
152 qq| AND ct.id NOT IN | .
153 qq| (SELECT a.customer_id FROM ar a, customer cv | .
154 qq| WHERE cv.id = a.customer_id)|;
156 if ($cv eq 'vendor') {
158 qq| AND ct.id NOT IN | .
159 qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
160 qq| WHERE cv.id = a.vendor_id)|;
162 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
165 if ($form->{obsolete} eq "Y") {
166 $where .= qq| AND ct.obsolete|;
167 } elsif ($form->{obsolete} eq "N") {
168 $where .= qq| AND NOT ct.obsolete|;
171 if ($form->{business_id}) {
172 $where .= qq| AND (ct.business_id = ?)|;
173 push(@values, conv_i($form->{business_id}));
176 if ($form->{salesman_id}) {
177 $where .= qq| AND (ct.salesman_id = ?)|;
178 push(@values, conv_i($form->{salesman_id}));
181 if($form->{insertdatefrom}) {
182 $where .= qq| AND (ct.itime::DATE >= ?)|;
183 push@values, conv_date($form->{insertdatefrom});
186 if($form->{insertdateto}) {
187 $where .= qq| AND (ct.itime::DATE <= ?)|;
188 push @values, conv_date($form->{insertdateto});
191 # Nur Kunden finden, bei denen ich selber der Verkäufer bin
192 # Gilt nicht für Lieferanten
193 if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
194 $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
195 push(@values, $::myconfig{login});
198 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
199 'trans_id_field' => 'ct.id',
203 $where .= qq| AND ($cvar_where)|;
204 push @values, @cvar_values;
207 if ($form->{addr_street}) {
208 $where .= qq| AND (ct.street ILIKE ?)|;
209 push @values, '%' . $form->{addr_street} . '%';
212 if ($form->{addr_zipcode}) {
213 $where .= qq| AND (ct.zipcode ILIKE ?)|;
214 push @values, $form->{addr_zipcode} . '%';
217 my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
218 my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.klass = pg.id) | : '';
220 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
221 qq| pt.description as payment | .
223 (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) .
225 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
226 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
227 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
231 my @saved_values = @values;
232 # redo for invoices, orders and quotations
236 if ($form->{l_invnumber}) {
237 my $ar = $cv eq 'customer' ? 'ar' : 'ap';
238 my $module = $ar eq 'ar' ? 'is' : 'ir';
239 push(@values, @saved_values);
242 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
243 qq| pt.description as payment, | .
244 qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
245 qq| '$module' AS module, 'invoice' AS formtype, | .
246 qq| (a.amount = a.paid) AS closed | .
248 qq|JOIN $ar a ON (a.${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 (a.invoice = '1')|;
255 if ( $form->{l_ordnumber} ) {
256 push(@values, @saved_values);
259 qq|SELECT ct.*, ct.itime::DATE AS insertdate, 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, 'order' 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 = '0')|;
271 if ( $form->{l_quonumber} ) {
272 push(@values, @saved_values);
275 qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
276 qq| pt.description as payment, | .
277 qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
278 qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
280 qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
281 qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
282 qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
283 qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
284 qq|WHERE $where AND (o.quotation = '1')|;
288 $query .= qq| ORDER BY $sortorder|;
290 $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
292 $main::lxdebug->leave_sub();
296 $main::lxdebug->enter_sub();
298 my ( $self, $myconfig, $form ) = @_;
300 die 'Missing argument: cp_id' unless $::form->{cp_id};
302 my $dbh = $form->dbconnect($myconfig);
304 qq|SELECT * FROM contacts c | .
305 qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
306 my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
307 my $ref = $sth->fetchrow_hashref("NAME_lc");
309 map { $form->{$_} = $ref->{$_} } keys %$ref;
311 $query = qq|SELECT COUNT(cp_id) AS used FROM (
312 SELECT cp_id FROM oe UNION
313 SELECT cp_id FROM ar UNION
314 SELECT cp_id FROM ap UNION
315 SELECT cp_id FROM delivery_orders
316 ) AS cpid WHERE cp_id = ? OR ? = 0|;
317 ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
322 $main::lxdebug->leave_sub();
326 $main::lxdebug->enter_sub();
331 Common::check_params(\%params, qw(vc id));
333 my $myconfig = \%main::myconfig;
334 my $form = $main::form;
336 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
338 my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
339 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
340 my $placeholders = join ", ", ('?') x scalar @ids;
341 my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
342 my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
344 WHERE id IN (${placeholders})|;
346 my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
348 if (ref $params{id} eq 'ARRAY') {
349 $result = { map { $_->{id} => $_ } @{ $result } };
351 $result = $result->[0] || { 'id' => $params{id} };
354 $main::lxdebug->leave_sub();
359 sub search_contacts {
360 $::lxdebug->enter_sub;
365 my $dbh = $params{dbh} || $::form->get_standard_dbh;
368 'cp_name' => 'cp_name, cp_givenname',
369 'vcname' => 'vcname, cp_name, cp_givenname',
370 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
373 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);
375 my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
376 $::form->{sort} = $order_by;
377 $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
379 my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
380 $order_by =~ s/,/ ${sortdir},/g;
381 $order_by .= " $sortdir";
383 my @where_tokens = ();
386 if ($params{search_term}) {
389 'cp.cp_name ILIKE ?',
390 'cp.cp_givenname ILIKE ?',
391 'cp.cp_email ILIKE ?';
392 push @values, ('%' . $params{search_term} . '%') x 3;
394 if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
395 my $number = $params{search_term};
396 $number =~ s/[^\d]//g;
397 $number = join '[ /\(\)+\-]*', split(m//, $number);
399 push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
402 push @where_tokens, map { "($_)" } join ' OR ', @tokens;
405 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
406 'trans_id_field' => 'cp.cp_id',
407 'filter' => $params{filter});
410 push @where_tokens, $cvar_where;
411 push @values, @cvar_values;
414 if (my $filter = $params{filter}) {
415 for (qw(name title givenname email project abteilung)) {
416 next unless $filter->{"cp_$_"};
417 add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
420 push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
421 push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
424 my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
426 my $query = qq|SELECT cp.*,
427 COALESCE(c.id, v.id) AS vcid,
428 COALESCE(c.name, v.name) AS vcname,
429 COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
430 CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
432 LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
433 LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
437 my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
439 $::lxdebug->leave_sub;
441 return @{ $contacts };