projekt kivitendo

#=====================================================================

# LX-Office ERP

# Copyright (C) 2004

# Based on SQL-Ledger Version 2.1.9

# Web http://www.lx-office.org

#

#=====================================================================

# SQL-Ledger Accounting

# Copyright (C) 2001

#

# Author: Dieter Simader

# Email: dsimader@sql-ledger.org

# Web: http://www.sql-ledger.org

#

# Contributors:

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.

#======================================================================

#

# backend code for customers and vendors

#

# CHANGE LOG:

# DS. 2000-07-04 Created

#

#======================================================================

package CT;

$main::lxdebug->enter_sub();

# connect to database

my $dbh = $form->dbconnect($myconfig);

"customernumber" => "ct.customernumber",

"vendornumber" => "ct.vendornumber",

"name" => "ct.name",

"contact" => "ct.contact",

"phone" => "ct.phone",

"fax" => "ct.fax",

"email" => "ct.email",

"street" => "ct.street",

"taxnumber" => "ct.taxnumber",

"ordnumber" => "ct.ordnumber",

"quonumber" => "ct.quonumber",

"zipcode" => "ct.zipcode",

"city" => "ct.city",

"country" => "ct.country",

"pricegroup" => "pg.pricegroup",

if ( $join_records ) {

# in UNION case order by hash key, e.g. salesman

# in not UNION case order by hash value, e.g. e.name

$sortorder .= " ${sortdir}";

if ($form->{"${cv}number"}) {

$where .= " AND ct.${cv}number ILIKE ?";

push(@values, '%' . $form->{"${cv}number"} . '%');

foreach my $key (qw(name contact email)) {

if ($form->{$key}) {

$where .= " AND ct.$key ILIKE ?";

push(@values, '%' . $form->{$key} . '%');

}

$where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";

push @values, '%' . $form->{cp_name} . '%';

}

if ($form->{addr_city}) {

$where .= " AND ((lower(ct.city) LIKE lower(?))

OR

(ct.id IN (

FROM shipto sc

WHERE (sc.module = 'CT')

AND (lower(sc.shiptocity) LIKE lower(?))

)";

push @values, ('%' . $form->{addr_city} . '%') x 2;

}

$where .= " AND ((lower(ct.country) LIKE lower(?))

OR

(ct.id IN (

FROM shipto so

WHERE (so.module = 'CT')

AND (lower(so.shiptocountry) LIKE lower(?))

)";

push @values, ('%' . $form->{addr_country} . '%') x 2;

}

$where .= " AND ((lower(ct.gln) LIKE lower(?))

OR

(ct.id IN (

SELECT so.trans_id

FROM shipto so

WHERE (so.module = 'CT')

AND (lower(so.shiptogln) LIKE lower(?))

))

)";

push @values, ('%' . $form->{addr_gln} . '%') x 2;

}

$where .=

qq| AND ct.id NOT IN | .

qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;

if ($cv eq 'customer') {

$where .=

qq| AND ct.id NOT IN | .

qq| (SELECT a.customer_id FROM ar a, customer cv | .

qq| WHERE cv.id = a.customer_id)|;

$where .=

qq| AND ct.id NOT IN | .

qq| (SELECT a.vendor_id FROM ap a, vendor cv | .

qq| WHERE cv.id = a.vendor_id)|;

$form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";

}

$where .= qq| AND (ct.salesman_id = ?)|;

push(@values, conv_i($form->{salesman_id}));

}

$where .= qq| AND (ct.itime::DATE >= ?)|;

push@values, conv_date($form->{insertdatefrom});

}

if($form->{insertdateto}) {

$where .= qq| AND (ct.itime::DATE <= ?)|;

push @values, conv_date($form->{insertdateto});

}

# Gilt nicht für Lieferanten

if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {

'trans_id_field' => 'ct.id',

'filter' => $form);

if ($cvar_where) {

$where .= qq| AND ($cvar_where)|;

push @values, @cvar_values;

}

if ($form->{addr_street}) {

}

if ($form->{addr_zipcode}) {

}

my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.klass = pg.id) | : '';

qq|LEFT JOIN business b ON (ct.business_id = b.id) | .

my $union = "UNION";

if ($form->{l_invnumber}) {

my $module = $ar eq 'ar' ? 'is' : 'ir';

$query .=

qq| UNION | .

$pg_select .

qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .

qq| (a.amount = a.paid) AS closed | .

qq|FROM $cv ct | .

qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .

qq|LEFT JOIN business b ON (ct.business_id = b.id) | .

$pg_select .

qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .

qq|FROM $cv ct | .

qq|JOIN oe o ON (o.${cv}_id = ct.id) | .

qq|LEFT JOIN business b ON (ct.business_id = b.id) | .

$pg_select .

qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .

qq|FROM $cv ct | .

qq|JOIN oe o ON (o.${cv}_id = ct.id) | .

qq|LEFT JOIN business b ON (ct.business_id = b.id) | .

}

$main::lxdebug->leave_sub();

}

$main::lxdebug->enter_sub();

die 'Missing argument: cp_id' unless $::form->{cp_id};

qq|SELECT * FROM contacts c | .

qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;

my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});

map { $form->{$_} = $ref->{$_} } keys %$ref;

SELECT cp_id FROM oe UNION

SELECT cp_id FROM ar UNION

SELECT cp_id FROM ap UNION

SELECT cp_id FROM delivery_orders

) AS cpid WHERE cp_id = ? OR ? = 0|;

($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);

$dbh->disconnect;

$main::lxdebug->leave_sub();

}

$main::lxdebug->enter_sub();

my $self = shift;

my %params = @_;

Common::check_params(\%params, qw(vc id));

my $myconfig = \%main::myconfig;

my $form = $main::form;

my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);

my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';

my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});

my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}

WHERE id IN (${placeholders})|;

my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);

if (ref $params{id} eq 'ARRAY') {

$result = { map { $_->{id} => $_ } @{ $result } };

} else {

$result = $result->[0] || { 'id' => $params{id} };

}

$main::lxdebug->leave_sub();

return $result;

}

$::lxdebug->enter_sub;

my $self = shift;

my %params = @_;

my $dbh = $params{dbh} || $::form->get_standard_dbh;

my %sortspecs = (

'cp_name' => 'cp_name, cp_givenname',

'vcname' => 'vcname, cp_name, cp_givenname',

'vcnumber' => 'vcnumber, cp_name, cp_givenname',

);

my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';

$::form->{sort} = $order_by;

$order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});

my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';

$order_by =~ s/,/ ${sortdir},/g;

$order_by .= " $sortdir";

my @where_tokens = ();

my @values;

if ($params{search_term}) {

my @tokens;

push @tokens,

'cp.cp_name ILIKE ?',

'cp.cp_givenname ILIKE ?',

'cp.cp_email ILIKE ?';

push @values, ('%' . $params{search_term} . '%') x 3;

if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {

my $number = $params{search_term};

$number =~ s/[^\d]//g;

$number = join '[ /\(\)+\-]*', split(m//, $number);

push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);

}

push @where_tokens, map { "($_)" } join ' OR ', @tokens;

}

'trans_id_field' => 'cp.cp_id',

'filter' => $params{filter});

if ($cvar_where) {

push @where_tokens, $cvar_where;

push @values, @cvar_values;

}

for (qw(name title givenname email project abteilung)) {

next unless $filter->{"cp_$_"};

add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');

}

push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';

push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';

}

my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';

my $query = qq|SELECT cp.*,

COALESCE(c.id, v.id) AS vcid,

COALESCE(c.name, v.name) AS vcname,

COALESCE(c.customernumber, v.vendornumber) AS vcnumber,

CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db

FROM contacts cp

LEFT JOIN customer c ON (cp.cp_cv_id = c.id)

LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)

$where

ORDER BY $order_by|;

my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);

$::lxdebug->leave_sub;

return @{ $contacts };

}