package CT;
use Data::Dumper;
-
+use SL::DBUtils;
sub get_tuple {
$main::lxdebug->enter_sub();
# get shipto address
- $query = qq|SELECT id, shiptoname
+ $query = qq|SELECT shipto_id, shiptoname, shiptodepartment_1
FROM shipto WHERE trans_id=$form->{id}|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
$form->{obsolete} *= 1;
$form->{business} *= 1;
$form->{salesman_id} *= 1;
- $form->{language_id} *= 1;
$form->{payment_id} *= 1;
$form->{taxzone_id} *= 1;
$form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
ustid = '$form->{ustid}',
username = '$form->{username}',
salesman_id = '$form->{salesman_id}',
- language_id = '$form->{language_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|,
$dbh->do($query) || $form->dberror($query);
}
}
- print(STDERR "SHIPTO_ID $form->{shipto_id}\n");
# add shipto
$form->add_shipto($dbh, $form->{id}, "CT");
$form->{obsolete} *= 1;
$form->{business} *= 1;
$form->{payment_id} *= 1;
- $form->{language_id} *= 1;
$form->{taxzone_id} *= 1;
$form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
ustid = '$form->{ustid}',
payment_id = '$form->{payment_id}',
taxzone_id = '$form->{taxzone_id}',
- language_id = '$form->{language_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}'
}
# add shipto
- $form->add_shipto($dbh, $form->{id});
+ $form->add_shipto($dbh, $form->{id}, "CT");
$rc = $dbh->disconnect;
my $dbh = $form->dbconnect($myconfig);
my $query = qq|SELECT s.*
FROM shipto s
- WHERE s.id = $form->{shipto_id} order by s.id limit 1|;
+ 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 ($self, $myconfig, $form) = @_;
my $dbh = $form->dbconnect($myconfig);
+ $tabelle = ($form->{db} eq "vendor") ? "ap" : "ar";
+
$where = " WHERE 1=1 ";
- if ($form->{shipto_id}) {
- $where .= "AND ar.shipto_id=$form->{shipto_id} ";
+ 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 ar.transdate >= '$form->{from}' ";
+ $where .= "AND $tabelle.transdate >= '$form->{from}' ";
}
if ($form->{to}) {
- $where .= "AND ar.transdate <= '$form->{to}' ";
+ $where .= "AND $tabelle.transdate <= '$form->{to}' ";
}
-
- my $query = qq|select shiptoname, adr_code, ar.transdate, ar.invnumber, ar.ordnumber, invoice.description, qty, invoice.unit FROM ar LEFT join shipto ON (ar.shipto_id=shipto.id) LEFT join invoice on (ar.id=invoice.trans_id) LEFT join parts ON (parts.id=invoice.parts_id) LEFT join adr ON (parts.adr_id=adr.id) $where ORDER BY ar.transdate DESC LIMIT 15|;
+ 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);
$main::lxdebug->leave_sub();
}
-sub adr {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
- my $dbh = $form->dbconnect($myconfig);
- $where = " WHERE 1=1 ";
- if ($form->{from}) {
- $where .= "AND ar.transdate >= '$form->{from}' ";
- }
- if ($form->{to}) {
- $where .= "AND ar.transdate <= '$form->{to}' ";
- }
- if ($form->{year}) {
- $where = " WHERE ar.transdate >= '$form->{year}-01-01' AND ar.transdate <= '$form->{year}-12-31' ";
- }
-
- my $query = qq|select adr_code, adr_description, sum(base_qty), parts.unit from ar LEFT join invoice on (ar.id=invoice.trans_id) LEFT join parts ON (invoice.parts_id=parts.id) LEFT join adr ON (adr.id=parts.adr_id) $where GROUP BY adr_code,adr_description,parts.unit|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{ADR} }, $ref;
- }
- $sth->finish;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
1;