# 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
#======================================================================
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 payment terms
- $query = qq|SELECT id, description
- FROM payment_terms
- ORDER BY sortkey|;
- $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 sortkey|;
- $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;
-