X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=b52c8794acafa55317b3c4bb674b4eab08d223c9;hb=f8fff60be1abb9986486cc7367747ee16d829116;hp=3057391e31e858d3d22a1520377dbd1e117bb77c;hpb=af85349024c0d1fc6db899cd84708c7fb6dfb7f4;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index 3057391e3..b52c8794a 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -25,7 +25,8 @@ # 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. +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, +# MA 02110-1335, USA. #====================================================================== # # backend code for customers and vendors @@ -36,857 +37,487 @@ #====================================================================== package CT; -use Data::Dumper; -use SL::DBUtils; - -sub get_tuple { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT ct.*, b.id AS business, cp.* - FROM $form->{db} ct - LEFT JOIN business b on ct.business_id = b.id - LEFT JOIN contacts cp on ct.id = cp.cp_cv_id - WHERE ct.id = $form->{id} order by cp.cp_id limit 1|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $ref = $sth->fetchrow_hashref(NAME_lc); - map { $form->{$_} = $ref->{$_} } keys %$ref; - - $sth->finish; - if ($form->{salesman_id}) { - my $query = qq|SELECT ct.name AS salesman - FROM $form->{db} ct - WHERE ct.id = $form->{salesman_id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my ($ref) = $sth->fetchrow_array(); +use SL::Common; +use SL::CVar; +use SL::DBUtils; +use SL::DB; +use SL::Util qw(trim); +use Text::ParseWords; - $form->{salesman} = $ref; +use strict; - $sth->finish; - } +sub search { + $main::lxdebug->enter_sub(); - # check if it is orphaned - my $arap = ($form->{db} eq 'customer') ? "ar" : "ap"; - $query = qq|SELECT a.id - FROM $arap a - JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id) - WHERE ct.id = $form->{id} - UNION - SELECT a.id - FROM oe a - JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id) - WHERE ct.id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - unless ($sth->fetchrow_array) { - $form->{status} = "orphaned"; - } - $sth->finish; + my ( $self, $myconfig, $form ) = @_; - # get tax labels - $query = qq|SELECT c.accno, c.description - FROM chart c - JOIN tax t ON (t.chart_id = c.id) - WHERE c.link LIKE '%CT_tax%' - ORDER BY c.accno|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $form->{taxaccounts} .= "$ref->{accno} "; - $form->{tax}{ $ref->{accno} }{description} = $ref->{description}; - } - $sth->finish; - chop $form->{taxaccounts}; - - # get taxes for customer/vendor - $query = qq|SELECT c.accno - FROM chart c - JOIN $form->{db}tax t ON (t.chart_id = c.id) - WHERE t.$form->{db}_id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $form->{tax}{ $ref->{accno} }{taxable} = 1; - } - $sth->finish; + # connect to database + my $dbh = $form->dbconnect($myconfig); - # get business types - $query = qq|SELECT id, description - FROM business - ORDER BY 1|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $cv = $form->{db} eq "customer" ? "customer" : "vendor"; + my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_business} }, $ref; + my $where = "1 = 1"; + my @values; + + my %allowed_sort_columns = ( + "id" => "ct.id", + "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", + "business" => "b.description", + "invnumber" => "ct.invnumber", + "ordnumber" => "ct.ordnumber", + "quonumber" => "ct.quonumber", + "zipcode" => "ct.zipcode", + "city" => "ct.city", + "country" => "ct.country", + "gln" => "ct.gln", + "discount" => "ct.discount", + "insertdate" => "ct.itime", + "salesman" => "e.name", + "payment" => "pt.description", + "pricegroup" => "pg.pricegroup", + "ustid" => "ct.ustid", + "creditlimit" => "ct.creditlimit", + "commercial_court" => "ct.commercial_court", + ); + + $form->{sort} ||= "name"; + my $sortorder; + if ( $join_records ) { + # in UNION case order by hash key, e.g. salesman + # the UNION created an implicit select around the result + $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name"; + } else { + # in not UNION case order by hash value, e.g. e.name + $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name"; } - $sth->finish; + my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; - # get tax zones - $query = qq|SELECT id, description - FROM tax_zones|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{TAXZONE} }, $ref; + if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) { + $sortorder = "lower($sortorder) ${sortdir}"; + } else { + $sortorder .= " ${sortdir}"; } - $sth->finish; - - - # get shipto address - $query = qq|SELECT shipto_id, shiptoname, shiptodepartment_1 - FROM shipto WHERE trans_id=$form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{SHIPTO} }, $ref; + if ($form->{"${cv}number"}) { + $where .= " AND ct.${cv}number ILIKE ?"; + push(@values, like($form->{"${cv}number"})); } - $sth->finish; - - # get contacts - $query = qq|SELECT cp_id, cp_name - FROM contacts WHERE cp_cv_id=$form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{CONTACTS} }, $ref; + foreach my $key (qw(name contact email)) { + if ($form->{$key}) { + $where .= " AND ct.$key ILIKE ?"; + push(@values, like($form->{$key})); + } } - $sth->finish; - - # get languages - $query = qq|SELECT id, description - FROM language - ORDER BY 1|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{languages} }, $ref; + if ($form->{cp_name}) { + $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))"; + push @values, like($form->{cp_name}); + } + + if ($form->{addr_street}) { + $where .= qq| AND ((ct.street ILIKE ?) | . + qq| OR | . + qq| (ct.id IN ( | . + qq| SELECT sc.trans_id FROM shipto sc | . + qq| WHERE (sc.module = 'CT') | . + qq| AND (sc.shiptostreet ILIKE ?) | . + qq| ))) |; + push @values, (like($form->{addr_street})) x 2; + } + + if ($form->{addr_zipcode}) { + $where .= qq| AND ((ct.zipcode ILIKE ?) | . + qq| OR | . + qq| (ct.id IN ( | . + qq| SELECT sc.trans_id FROM shipto sc | . + qq| WHERE (sc.module = 'CT') | . + qq| AND (sc.shiptozipcode ILIKE ?) | . + qq| ))) |; + push @values, (like($form->{addr_zipcode})) x 2; + } + + if ($form->{addr_city}) { + $where .= " AND ((lower(ct.city) LIKE lower(?)) + OR + (ct.id IN ( + SELECT sc.trans_id + FROM shipto sc + WHERE (sc.module = 'CT') + AND (lower(sc.shiptocity) LIKE lower(?)) + )) + )"; + push @values, (like($form->{addr_city})) x 2; + } + + if ($form->{addr_country}) { + $where .= " AND ((lower(ct.country) LIKE lower(?)) + OR + (ct.id IN ( + SELECT so.trans_id + FROM shipto so + WHERE (so.module = 'CT') + AND (lower(so.shiptocountry) LIKE lower(?)) + )) + )"; + push @values, (like($form->{addr_country})) x 2; + } + + if ($form->{addr_gln}) { + $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, (like($form->{addr_gln})) x 2; + } + + if ( $form->{status} eq 'orphaned' ) { + $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)|; + } + if ($cv eq 'vendor') { + $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} = ""; } - $sth->finish; - # get languages - $query = qq|SELECT id, description - FROM payment_terms - ORDER BY 1|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{payment_terms} }, $ref; + if ($form->{obsolete} eq "Y") { + $where .= qq| AND ct.obsolete|; + } elsif ($form->{obsolete} eq "N") { + $where .= qq| AND NOT ct.obsolete|; } - $sth->finish; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -## LINET -sub query_titles_and_greetings { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - my (%tmp, $ref); - - my $dbh = $form->dbconnect($myconfig); - - $query = - "SELECT DISTINCT(c.cp_greeting) FROM contacts c WHERE c.cp_greeting LIKE '%'"; - $sth = $dbh->prepare($query); - $sth->execute() || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - next unless ($ref->{cp_greeting} =~ /[a-zA-Z]/); - $tmp{ $ref->{cp_greeting} } = 1; + if ($form->{business_id}) { + $where .= qq| AND (ct.business_id = ?)|; + push(@values, conv_i($form->{business_id})); } - $sth->finish(); - @{ $form->{GREETINGS} } = sort(keys(%tmp)); - - %tmp = (); - - $query = - "SELECT greeting FROM customer UNION select greeting FROM vendor"; - $sth = $dbh->prepare($query); - $sth->execute() || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - next unless ($ref->{greeting} =~ /[a-zA-Z]/); - $tmp{ $ref->{greeting} } = 1; - } - $sth->finish(); + if ($form->{salesman_id}) { + $where .= qq| AND (ct.salesman_id = ?)|; + push(@values, conv_i($form->{salesman_id})); + } + + if($form->{insertdatefrom}) { + $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}); + } + + if ($form->{all}) { + my @tokens = parse_line('\s+', 0, $form->{all}); + $where .= qq| AND ( + ct.${cv}number ILIKE ? OR + ct.name ILIKE ? + )| for @tokens; + push @values, ("%$_%")x2 for @tokens; + } + + if (($form->{create_zugferd_invoices} // '') ne '') { + $where .= qq| AND (ct.create_zugferd_invoices = ?)|; + push @values, $form->{create_zugferd_invoices}; + } + + if ($form->{all_phonenumbers}) { + my $search_term = trim($form->{all_phonenumbers}); + $search_term =~ s{\p{WSpace}+}{}g; + $search_term = join ' *', split(//, $search_term); + + $where .= qq| AND (ct.phone ~* ? OR + ct.fax ~* ? OR + ct.id IN + (SELECT cp_cv_id FROM contacts + WHERE cp_phone1 ~* ? OR + cp_phone2 ~* ? OR + cp_fax ~* ? OR + cp_mobile1 ~* ? OR + cp_mobile2 ~* ? OR + cp_satphone ~* ? OR + cp_satfax ~* ? OR + cp_privatphone ~* ? + ) + )|; + push @values, ($search_term)x10; + } + + my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT', + 'trans_id_field' => 'ct.id', + 'filter' => $form); + + if ($cvar_where) { + $where .= qq| AND ($cvar_where)|; + push @values, @cvar_values; + } + + my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : ''; + my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : ''; + + my $main_cp_select = ''; + if ($form->{l_main_contact_person}) { + $main_cp_select = qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1) + FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1) + AS main_contact_person /; + } + my $query = + qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . + qq| pt.description as payment | . + $pg_select . + $main_cp_select . + (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) . + qq|FROM $cv ct | . + qq|LEFT JOIN business b ON (ct.business_id = b.id) | . + qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . + qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | . + $pg_join . + qq|WHERE $where|; + + my @saved_values = @values; + # redo for invoices, orders and quotations + if ($join_records) { + my $union = "UNION"; - @{ $form->{COMPANY_GREETINGS} } = sort(keys(%tmp)); + if ($form->{l_invnumber}) { + my $ar = $cv eq 'customer' ? 'ar' : 'ap'; + my $module = $ar eq 'ar' ? 'is' : 'ir'; + push(@values, @saved_values); + $query .= + qq| UNION | . + qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . + qq| pt.description as payment | . + $pg_select . + $main_cp_select . + qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | . + qq| '$module' AS module, 'invoice' AS formtype, | . + 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) | . + qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . + qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | . + $pg_join . + qq|WHERE $where AND (a.invoice = '1')|; + } - %tmp = (); + if ( $form->{l_ordnumber} ) { + push(@values, @saved_values); + $query .= + qq| UNION | . + qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . + qq| pt.description as payment | . + $pg_select . + $main_cp_select . + qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . + qq| 'oe' AS module, 'order' AS formtype, o.closed | . + 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) | . + qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . + qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | . + $pg_join . + qq|WHERE $where AND (o.quotation = '0')|; + } - $query = - "SELECT DISTINCT(c.cp_title) FROM contacts c WHERE c.cp_title LIKE '%'"; - $sth = $dbh->prepare($query); - $sth->execute() || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - next unless ($ref->{cp_title} =~ /[a-zA-Z]/); - $tmp{ $ref->{cp_title} } = 1; + if ( $form->{l_quonumber} ) { + push(@values, @saved_values); + $query .= + qq| UNION | . + qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . + qq| pt.description as payment | . + $pg_select . + $main_cp_select . + qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . + qq| 'oe' AS module, 'quotation' AS formtype, o.closed | . + 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) | . + qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . + qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | . + $pg_join . + qq|WHERE $where AND (o.quotation = '1')|; + } } - $sth->finish(); - @{ $form->{TITLES} } = sort(keys(%tmp)); + $query .= qq| ORDER BY $sortorder|; - %tmp = (); + $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values); - $query = - "SELECT DISTINCT(c.cp_abteilung) FROM contacts c WHERE c.cp_abteilung LIKE '%'"; - $sth = $dbh->prepare($query); - $sth->execute() || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $tmp{ $ref->{cp_abteilung} } = 1; - } - $sth->finish(); - - @{ $form->{DEPARTMENT} } = sort(keys(%tmp)); - - $dbh->disconnect(); $main::lxdebug->leave_sub(); } -## /LINET -sub taxaccounts { +sub get_contact { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; - - my $dbh = $form->dbconnect($myconfig); - - # get tax labels - my $query = qq|SELECT accno, description - FROM chart c, tax t - WHERE c.link LIKE '%CT_tax%' - AND c.id = t.chart_id - ORDER BY accno|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $ref = (); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $form->{taxaccounts} .= "$ref->{accno} "; - $form->{tax}{ $ref->{accno} }{description} = $ref->{description}; - } - $sth->finish; - chop $form->{taxaccounts}; - - # this is just for the selection for type of business - $query = qq|SELECT id, description - FROM business|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_business} }, $ref; - } - $sth->finish; - # get languages - $query = qq|SELECT id, description - FROM language - ORDER BY 1|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{languages} }, $ref; - } - $sth->finish; + my ( $self, $myconfig, $form ) = @_; - # get payment terms - $query = qq|SELECT id, description - FROM payment_terms - ORDER BY 1|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + die 'Missing argument: cp_id' unless $::form->{cp_id}; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{payment_terms} }, $ref; - } - $sth->finish; + my $dbh = SL::DB->client->dbh; + my $query = + 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}); + my $ref = $sth->fetchrow_hashref("NAME_lc"); - # get taxkeys and description - $query = qq|SELECT id, description - FROM tax_zones|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + map { $form->{$_} = $ref->{$_} } keys %$ref; + $query = qq|SELECT COUNT(cp_id) AS used FROM ( + 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); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{TAXZONE} }, $ref; - } $sth->finish; - - $dbh->disconnect; - $main::lxdebug->leave_sub(); } -sub save_customer { +sub get_bank_info { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; - - # set pricegroup to default - if ($form->{klass}) { } - else { $form->{klass} = 0; } - - # connect to database - my $dbh = $form->dbconnect($myconfig); -##LINET - map({ - $form->{"cp_${_}"} = $form->{"selected_cp_${_}"} - if ($form->{"selected_cp_${_}"}); - } qw(title greeting abteilung)); - $form->{"greeting"} = $form->{"selected_company_greeting"} - if ($form->{"selected_company_greeting"}); - # - # escape ' - map { $form->{$_} =~ s/\'/\'\'/g } - qw(customernumber name street zipcode city country homepage contact notes cp_title cp_greeting language pricegroup); -##/LINET - # assign value discount, terms, creditlimit - $form->{discount} = $form->parse_amount($myconfig, $form->{discount}); - $form->{discount} /= 100; - $form->{terms} *= 1; - $form->{taxincluded} *= 1; - $form->{obsolete} *= 1; - $form->{business} *= 1; - $form->{salesman_id} *= 1; - $form->{payment_id} *= 1; - $form->{taxzone_id} *= 1; - $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit}); - - my ($query, $sth, $f_id); - - if ($form->{id}) { - - $query = qq|SELECT id FROM customer - WHERE customernumber = '$form->{customernumber}'|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - (${f_id}) = $sth->fetchrow_array; - $sth->finish; - if ((${f_id} ne $form->{id}) and (${f_id} ne "")) { - - $main::lxdebug->leave_sub(); - return 3; - } - $query = qq|DELETE FROM customertax - WHERE customer_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - -# $query = qq|DELETE FROM shipto -# WHERE trans_id = $form->{id} AND module = 'CT'|; -# $dbh->do($query) || $form->dberror($query); - } else { - - my $uid = rand() . time; - - $uid .= $form->{login}; - - $uid = substr($uid, 2, 75); - if (!$form->{customernumber} && $form->{business}) { - $form->{customernumber} = - $form->update_business($myconfig, $form->{business}); - } - if (!$form->{customernumber}) { - $form->{customernumber} = - $form->update_defaults($myconfig, "customernumber"); - } + my $self = shift; + my %params = @_; - $query = qq|SELECT c.id FROM customer c - WHERE c.customernumber = '$form->{customernumber}'|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - (${f_id}) = $sth->fetchrow_array; - $sth->finish; - if (${f_id} ne "") { - $main::lxdebug->leave_sub(); - return 3; - } + Common::check_params(\%params, qw(vc id)); - $query = qq|INSERT INTO customer (name) - VALUES ('$uid')|; - $dbh->do($query) || $form->dberror($query); + my $myconfig = \%main::myconfig; + my $form = $main::form; - $query = qq|SELECT c.id FROM customer c - WHERE c.name = '$uid'|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - ($form->{id}) = $sth->fetchrow_array; - $sth->finish; - } - $query = qq|UPDATE customer SET - customernumber = '$form->{customernumber}', - name = '$form->{name}', - greeting = '$form->{greeting}', - department_1 = '$form->{department_1}', - department_2 = '$form->{department_2}', - street = '$form->{street}', - zipcode = '$form->{zipcode}', - city = '$form->{city}', - country = '$form->{country}', - homepage = '$form->{homepage}', - contact = '$form->{contact}', - phone = '$form->{phone}', - fax = '$form->{fax}', - email = '$form->{email}', - cc = '$form->{cc}', - bcc = '$form->{bcc}', - notes = '$form->{notes}', - discount = $form->{discount}, - creditlimit = $form->{creditlimit}, - terms = $form->{terms}, - taxincluded = '$form->{taxincluded}', - business_id = $form->{business}, - taxnumber = '$form->{taxnumber}', - sic_code = '$form->{sic}', - language = '$form->{language}', - account_number = '$form->{account_number}', - bank_code = '$form->{bank_code}', - bank = '$form->{bank}', - obsolete = '$form->{obsolete}', - ustid = '$form->{ustid}', - username = '$form->{username}', - salesman_id = '$form->{salesman_id}', - language_id = | . conv_i($form->{language_id}, "NULL") . qq|, - payment_id = '$form->{payment_id}', - taxzone_id = '$form->{taxzone_id}', - user_password = | . $dbh->quote($form->{user_password}) . qq|, - c_vendor_id = '$form->{c_vendor_id}', - klass = '$form->{klass}' - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - if ($form->{cp_id}) { - $query = qq|UPDATE contacts SET - cp_greeting = '$form->{cp_greeting}', - cp_title = '$form->{cp_title}', - cp_givenname = '$form->{cp_givenname}', - cp_name = '$form->{cp_name}', - cp_email = '$form->{cp_email}', - cp_phone1 = '$form->{cp_phone1}', - cp_phone2 = '$form->{cp_phone2}', - cp_abteilung = | . $dbh->quote($form->{cp_abteilung}) . qq|, - cp_fax = | . $dbh->quote($form->{cp_fax}) . qq|, - cp_mobile1 = | . $dbh->quote($form->{cp_mobile1}) . qq|, - cp_mobile2 = | . $dbh->quote($form->{cp_mobile2}) . qq|, - cp_satphone = | . $dbh->quote($form->{cp_satphone}) . qq|, - cp_satfax = | . $dbh->quote($form->{cp_satfax}) . qq|, - cp_project = | . $dbh->quote($form->{cp_project}) . qq|, - cp_privatphone = | . $dbh->quote($form->{cp_privatphone}) . qq|, - cp_privatemail = | . $dbh->quote($form->{cp_privatemail}) . qq|, - cp_birthday = | . $dbh->quote($form->{cp_birthday}) . qq| - WHERE cp_id = $form->{cp_id}|; - } elsif ($form->{cp_name} || $form->{cp_givenname}) { - $query = - qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, cp_birthday) - VALUES ($form->{id}, '$form->{cp_greeting}','$form->{cp_title}','$form->{cp_givenname}','$form->{cp_name}','$form->{cp_email}','$form->{cp_phone1}','$form->{cp_phone2}', '$form->{cp_abteilung}', | . $dbh->quote($form->{cp_fax}) . qq|,| . $dbh->quote($form->{cp_mobile1}) . qq|,| . $dbh->quote($form->{cp_mobile2}) . qq|,| . $dbh->quote($form->{cp_satphone}) . qq|,| . $dbh->quote($form->{cp_satfax}) . qq|,| . $dbh->quote($form->{cp_project}) . qq|,| . $dbh->quote($form->{cp_privatphone}) . qq|,| . $dbh->quote($form->{cp_privatemail}) . qq|,| . $dbh->quote($form->{cp_birthday}) . qq|)|; - } - $dbh->do($query) || $form->dberror($query); - - # save taxes - foreach $item (split / /, $form->{taxaccounts}) { - if ($form->{"tax_$item"}) { - $query = qq|INSERT INTO customertax (customer_id, chart_id) - VALUES ($form->{id}, (SELECT c.id - FROM chart c - WHERE c.accno = '$item'))|; - $dbh->do($query) || $form->dberror($query); - } - } - # add shipto - $form->add_shipto($dbh, $form->{id}, "CT"); + my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor'; + my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id}); + my $placeholders = join ", ", ('?') x scalar @ids; + my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : ''; + my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate} + FROM ${table} + WHERE id IN (${placeholders})|; - $rc = $dbh->disconnect; + my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids); - $main::lxdebug->leave_sub(); - return $rc; -} - -sub save_vendor { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); -##LINET - map({ - $form->{"cp_${_}"} = $form->{"selected_cp_${_}"} - if ($form->{"selected_cp_${_}"}); - } qw(title greeting abteilung)); - $form->{"greeting"} = $form->{"selected_company_greeting"} - if ($form->{"selected_company_greeting"}); - # escape ' - map { $form->{$_} =~ s/\'/\'\'/g } - qw(vendornumber name street zipcode city country homepage contact notes cp_title cp_greeting language); -##/LINET - $form->{discount} = $form->parse_amount($myconfig, $form->{discount}); - $form->{discount} /= 100; - $form->{terms} *= 1; - $form->{taxincluded} *= 1; - $form->{obsolete} *= 1; - $form->{business} *= 1; - $form->{payment_id} *= 1; - $form->{taxzone_id} *= 1; - $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit}); - - my $query; - - if ($form->{id}) { - $query = qq|DELETE FROM vendortax - WHERE vendor_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|DELETE FROM shipto - WHERE trans_id = $form->{id} AND module = 'CT'|; - $dbh->do($query) || $form->dberror($query); + if (ref $params{id} eq 'ARRAY') { + $result = { map { $_->{id} => $_ } @{ $result } }; } else { - my $uid = time; - $uid .= $form->{login}; - my $uid = rand() . time; - $uid .= $form->{login}; - $uid = substr($uid, 2, 75); - $query = qq|INSERT INTO vendor (name) - VALUES ('$uid')|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|SELECT v.id FROM vendor v - WHERE v.name = '$uid'|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - ($form->{id}) = $sth->fetchrow_array; - $sth->finish; - if (!$form->{vendornumber}) { - $form->{vendornumber} = - $form->update_defaults($myconfig, "vendornumber"); - } - - } - -##LINET - $query = qq|UPDATE vendor SET - vendornumber = '$form->{vendornumber}', - name = '$form->{name}', - greeting = '$form->{greeting}', - department_1 = '$form->{department_1}', - department_2 = '$form->{department_2}', - street = '$form->{street}', - zipcode = '$form->{zipcode}', - city = '$form->{city}', - country = '$form->{country}', - homepage = '$form->{homepage}', - contact = '$form->{contact}', - phone = '$form->{phone}', - fax = '$form->{fax}', - email = '$form->{email}', - cc = '$form->{cc}', - bcc = '$form->{bcc}', - notes = '$form->{notes}', - terms = $form->{terms}, - discount = $form->{discount}, - creditlimit = $form->{creditlimit}, - taxincluded = '$form->{taxincluded}', - gifi_accno = '$form->{gifi_accno}', - business_id = $form->{business}, - taxnumber = '$form->{taxnumber}', - sic_code = '$form->{sic}', - language = '$form->{language}', - account_number = '$form->{account_number}', - bank_code = '$form->{bank_code}', - bank = '$form->{bank}', - obsolete = '$form->{obsolete}', - ustid = '$form->{ustid}', - payment_id = '$form->{payment_id}', - taxzone_id = '$form->{taxzone_id}', - language_id = | . conv_i($form->{language_id}, "NULL") . qq|, - username = '$form->{username}', - user_password = '$form->{user_password}', - v_customer_id = '$form->{v_customer_id}' - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - if ($form->{cp_id}) { - $query = qq|UPDATE contacts SET - cp_greeting = '$form->{cp_greeting}', - cp_title = '$form->{cp_title}', - cp_givenname = '$form->{cp_givenname}', - cp_name = '$form->{cp_name}', - cp_email = '$form->{cp_email}', - cp_phone1 = '$form->{cp_phone1}', - cp_phone2 = '$form->{cp_phone2}' - WHERE cp_id = $form->{cp_id}|; - } elsif ($form->{cp_name} || $form->{cp_givenname}) { - $query = - qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, cp_name, cp_email, cp_phone1, cp_phone2) - VALUES ($form->{id}, '$form->{cp_greeting}','$form->{cp_title}','$form->{cp_givenname}','$form->{cp_name}','$form->{cp_email}','$form->{cp_phone1}','$form->{cp_phone2}')|; - } - $dbh->do($query) || $form->dberror($query); - - # save taxes - foreach $item (split / /, $form->{taxaccounts}) { - if ($form->{"tax_$item"}) { - $query = qq|INSERT INTO vendortax (vendor_id, chart_id) - VALUES ($form->{id}, (SELECT c.id - FROM chart c - WHERE c.accno = '$item'))|; - $dbh->do($query) || $form->dberror($query); - } + $result = $result->[0] || { 'id' => $params{id} }; } - # add shipto - $form->add_shipto($dbh, $form->{id}, "CT"); - - $rc = $dbh->disconnect; - $main::lxdebug->leave_sub(); - return $rc; -} - -sub delete { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - # delete vendor - my $query = qq|DELETE FROM $form->{db} - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - $dbh->disconnect; - $main::lxdebug->leave_sub(); + return $result; } -sub search { - $main::lxdebug->enter_sub(); +sub search_contacts { + $::lxdebug->enter_sub; - my ($self, $myconfig, $form) = @_; + my $self = shift; + my %params = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $params{dbh} || $::form->get_standard_dbh; - my $where = "1 = 1"; - $form->{sort} = "name" unless ($form->{sort}); - - if ($form->{"$form->{db}number"}) { - my $companynumber = $form->like(lc $form->{"$form->{db}number"}); - $where .= " AND lower(ct.$form->{db}number) LIKE '$companynumber'"; - } - if ($form->{name}) { - my $name = $form->like(lc $form->{name}); - $where .= " AND lower(ct.name) LIKE '$name'"; - } - if ($form->{contact}) { - my $contact = $form->like(lc $form->{contact}); - $where .= " AND lower(ct.contact) LIKE '$contact'"; - } - if ($form->{email}) { - my $email = $form->like(lc $form->{email}); - $where .= " AND lower(ct.email) LIKE '$email'"; - } + my %sortspecs = ( + 'cp_name' => 'cp_name, cp_givenname', + 'vcname' => 'vcname, cp_name, cp_givenname', + 'vcnumber' => 'vcnumber, cp_name, cp_givenname', + ); - if ($form->{status} eq 'orphaned') { - $where .= qq| AND ct.id NOT IN (SELECT o.$form->{db}_id - FROM oe o, $form->{db} cv - WHERE cv.id = o.$form->{db}_id)|; - if ($form->{db} eq 'customer') { - $where .= qq| AND ct.id NOT IN (SELECT a.customer_id - FROM ar a, customer cv - WHERE cv.id = a.customer_id)|; - } - if ($form->{db} eq 'vendor') { - $where .= qq| AND ct.id NOT IN (SELECT a.vendor_id - FROM ap a, vendor cv - WHERE cv.id = a.vendor_id)|; - } - $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = ""; - } + 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); - my $query = qq|SELECT ct.*, b.description AS business - FROM $form->{db} ct - LEFT JOIN business b ON (ct.business_id = b.id) - WHERE $where|; + my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name'; + $::form->{sort} = $order_by; + $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by}); - # redo for invoices, orders and quotations - if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) { + my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC'; + $order_by =~ s/,/ ${sortdir},/g; + $order_by .= " $sortdir"; - my ($ar, $union, $module); - $query = ""; + my @where_tokens = (); + my @values; - if ($form->{l_invnumber}) { - $ar = ($form->{db} eq 'customer') ? 'ar' : 'ap'; - $module = ($ar eq 'ar') ? 'is' : 'ir'; - - $query = qq|SELECT ct.*, b.description AS business, - a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, - '$module' AS module, 'invoice' AS formtype, - (a.amount = a.paid) AS closed - FROM $form->{db} ct - JOIN $ar a ON (a.$form->{db}_id = ct.id) - LEFT JOIN business b ON (ct.business_id = b.id) - WHERE $where - AND a.invoice = '1'|; - - $union = qq| - UNION|; + if ($params{search_term}) { + my @tokens; + push @tokens, + 'cp.cp_name ILIKE ?', + 'cp.cp_givenname ILIKE ?', + 'cp.cp_email ILIKE ?'; + push @values, (like($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); - if ($form->{l_ordnumber}) { - $query .= qq|$union - SELECT ct.*, b.description AS business, - ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, - 'oe' AS module, 'order' AS formtype, - o.closed - FROM $form->{db} ct - JOIN oe o ON (o.$form->{db}_id = ct.id) - LEFT JOIN business b ON (ct.business_id = b.id) - WHERE $where - AND o.quotation = '0'|; - - $union = qq| - UNION|; + push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2); } - if ($form->{l_quonumber}) { - $query .= qq|$union - SELECT ct.*, b.description AS business, - ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, - 'oe' AS module, 'quotation' AS formtype, - o.closed - FROM $form->{db} ct - JOIN oe o ON (o.$form->{db}_id = ct.id) - LEFT JOIN business b ON (ct.business_id = b.id) - WHERE $where - AND o.quotation = '1'|; - - } + push @where_tokens, map { "($_)" } join ' OR ', @tokens; } - $query .= qq| - ORDER BY $form->{sort}|; + my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts', + 'trans_id_field' => 'cp.cp_id', + 'filter' => $params{filter}); - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); -##LINET - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - $ref->{address} = ""; - map { $ref->{address} .= "$ref->{$_} "; } qw(street zipcode city country); - push @{ $form->{CT} }, $ref; + if ($cvar_where) { + push @where_tokens, $cvar_where; + push @values, @cvar_values; } -##/LINET - $sth->finish; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub get_contact { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT c.* - FROM contacts c - WHERE c.cp_id = $form->{cp_id} order by c.cp_id limit 1|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $ref = $sth->fetchrow_hashref(NAME_lc); - - map { $form->{$_} = $ref->{$_} } keys %$ref; - - $sth->finish; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} + if (my $filter = $params{filter}) { + 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'); + } -sub get_shipto { - $main::lxdebug->enter_sub(); + 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 ($self, $myconfig, $form) = @_; - my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT s.* - FROM shipto s - WHERE s.shipto_id = $form->{shipto_id}|; - #WHERE s.shipto_id = $form->{shipto_id} order by s.shipto_id limit 1|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : ''; - my $ref = $sth->fetchrow_hashref(NAME_lc); + 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|; - map { $form->{$_} = $ref->{$_} } keys %$ref; + my $contacts = selectall_hashref_query($::form, $dbh, $query, @values); - $sth->finish; - $dbh->disconnect; + $::lxdebug->leave_sub; - $main::lxdebug->leave_sub(); + return @{ $contacts }; } -sub get_delivery { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - my $dbh = $form->dbconnect($myconfig); - $tabelle = ($form->{db} eq "vendor") ? "ap" : "ar"; - - $where = " WHERE 1=1 "; - if ($form->{shipto_id} && $tabelle eq "ar") { - $where .= "AND $tabelle.shipto_id=$form->{shipto_id} "; - } else { - $where .="AND $tabelle.$form->{db}_id=$form->{id} "; - } - if ($form->{from}) { - $where .= "AND $tabelle.transdate >= '$form->{from}' "; - } - if ($form->{to}) { - $where .= "AND $tabelle.transdate <= '$form->{to}' "; - } - my $query = qq|select shiptoname, $tabelle.transdate, $tabelle.invnumber, $tabelle.ordnumber, invoice.description, qty, invoice.unit FROM $tabelle LEFT JOIN shipto ON |; - $query .= ($tabelle eq "ar") ? qq|($tabelle.shipto_id=shipto.shipto_id) |:qq|($tabelle.id=shipto.trans_id) |; - $query .=qq|LEFT join invoice on ($tabelle.id=invoice.trans_id) LEFT join parts ON (parts.id=invoice.parts_id) $where ORDER BY $tabelle.transdate DESC LIMIT 15|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{DELIVERY} }, $ref; - } - $sth->finish; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} 1; -