package IC;
use Data::Dumper;
+use SL::DBUtils;
sub get_part {
$main::lxdebug->enter_sub();
LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE p.id = $form->{id}|;
+ WHERE p.id = ? |;
+ my @vars = ($form->{id});
my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
my $ref = $sth->fetchrow_hashref(NAME_lc);
# copy to $form variables
FROM parts p
JOIN assembly a ON (a.parts_id = p.id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE a.id = $form->{id}
- ORDER BY $oid{$myconfig->{dbdriver}}|;
-
+ WHERE a.id = ?
+ ORDER BY ?|;
+ @vars = ($form->{id}, $oid{$myconfig->{dbdriver}});
$sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
+ $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
+
$form->{assembly_rows} = 0;
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
$form->{assembly_rows}++;
# get prices
$query =
qq|SELECT p.parts_id, p.pricegroup_id, p.price, (SELECT pg.pricegroup FROM pricegroup pg WHERE pg.id=p.pricegroup_id) AS pricegroup FROM prices p
- WHERE parts_id = $form->{id}
+ WHERE parts_id = ?
ORDER by pricegroup|;
+ @vars = ($form->{id});
$sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
@pricegroups = ();
@pricegroups_not_used = ();
# get makes
if ($form->{makemodel}) {
$query = qq|SELECT m.make, m.model FROM makemodel m
- WHERE m.parts_id = $form->{id}|;
-
+ WHERE m.parts_id = ?|;
+ @vars = ($form->{id});
$sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
my $i = 1;
while (($form->{"make_$i"}, $form->{"model_$i"}) = $sth->fetchrow_array)
# get translations
$form->{language_values} = "";
- $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = $form->{id}|;
+ $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = ?|;
+ @vars = ($form->{id});
$trq = $dbh->prepare($query);
- $trq->execute || $form->dberror($query);
+ $trq->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
while ($tr = $trq->fetchrow_hashref(NAME_lc)) {
$form->{language_values} .= "---+++---".$tr->{language_id}."--++--".$tr->{translation};
}
# get buchungsgruppen
$query = qq|SELECT id, description
- FROM buchungsgruppen|;
+ FROM buchungsgruppen
+ ORDER BY sortkey|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
$main::lxdebug->enter_sub();
my ($self, $myconfig, $form) = @_;
- $form->{IC_expense} = "1000";
- $form->{IC_income} = "2000";
-
- if ($form->{item} ne 'service') {
- $form->{IC} = $form->{IC_expense};
- }
-
- ($form->{inventory_accno}) = split(/--/, $form->{IC});
- ($form->{expense_accno}) = split(/--/, $form->{IC_expense});
- ($form->{income_accno}) = split(/--/, $form->{IC_income});
# connect to database, turn off AutoCommit
my $dbh = $form->dbconnect_noauto($myconfig);
$form->{orphaned} = 1;
$form->{onhand} = $form->{stock} if $form->{item} eq 'assembly';
- if ($form->{partnumber} eq "" && $form->{inventory_accno} eq "") {
+ if ($form->{partnumber} eq "" && $form->{"item"} eq "service") {
$form->{partnumber} = $form->update_defaults($myconfig, "servicenumber");
}
- if ($form->{partnumber} eq "" && $form->{inventory_accno} ne "") {
+ if ($form->{partnumber} eq "" && $form->{"item"} ne "service") {
$form->{partnumber} = $form->update_defaults($myconfig, "articlenumber");
}
($partsgroup, $partsgroup_id) = split /--/, $form->{partsgroup};
}
+ my ($subq_inventory, $subq_expense, $subq_income);
+ if ($form->{"item"} eq "part") {
+ $subq_inventory =
+ qq|(SELECT bg.inventory_accno_id | .
+ qq| FROM buchungsgruppen bg | .
+ qq| WHERE bg.id = | . $dbh->quote($form->{"buchungsgruppen_id"}) . qq|)|;
+ } else {
+ $subq_inventory = "NULL";
+ }
+
+ if ($form->{"item"} ne "assembly") {
+ $subq_expense =
+ qq|(SELECT bg.expense_accno_id_0 | .
+ qq| FROM buchungsgruppen bg | .
+ qq| WHERE bg.id = | . $dbh->quote($form->{"buchungsgruppen_id"}) . qq|)|;
+ } else {
+ $subq_expense = "NULL";
+ }
+
+ $subq_income =
+ qq|(SELECT bg.income_accno_id_0 | .
+ qq| FROM buchungsgruppen bg | .
+ qq| WHERE bg.id = | . $dbh->quote($form->{"buchungsgruppen_id"}) . qq|)|;
+
$query = qq|UPDATE parts SET
partnumber = '$form->{partnumber}',
description = '$form->{description}',
bin = '$form->{bin}',
buchungsgruppen_id = '$form->{buchungsgruppen_id}',
payment_id = '$form->{payment_id}',
- inventory_accno_id = (SELECT c.id FROM chart c
- WHERE c.accno = '$form->{inventory_accno}'),
- income_accno_id = (SELECT c.id FROM chart c
- WHERE c.accno = '$form->{income_accno}'),
- expense_accno_id = (SELECT c.id FROM chart c
- WHERE c.accno = '$form->{expense_accno}'),
+ inventory_accno_id = $subq_inventory,
+ income_accno_id = $subq_income,
+ expense_accno_id = $subq_expense,
obsolete = '$form->{obsolete}',
image = '$form->{image}',
drawing = '$form->{drawing}',
shop = '$form->{shop}',
ve = '$form->{ve}',
gv = '$form->{gv}',
+ ean = '$form->{ean}',
not_discountable = '$form->{not_discountable}',
microfiche = '$form->{microfiche}',
partsgroup_id = $partsgroup_id
}
}
+ if ($form->{ean}) {
+ $var = $form->like(lc $form->{ean});
+ $where .= " AND lower(ean) LIKE '$var'";
+ }
+
if ($form->{searchitems} eq 'part') {
$where .= " AND p.inventory_accno_id > 0";
}
p.priceupdate, p.image, p.drawing, p.microfiche,
pg.partsgroup,
'' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
- ct.name|;
+ ct.name, NULL AS deliverydate|;
if ($form->{ordered}) {
$query .= qq|$union
p.priceupdate, p.image, p.drawing, p.microfiche,
pg.partsgroup,
'' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
- ct.name|;
+ ct.name, NULL AS deliverydate|;
$query .= qq|$union
SELECT $flds, 'oe' AS module, 'purchase_order' AS type,
p.priceupdate, p.image, p.drawing, p.microfiche,
pg.partsgroup,
'' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
- ct.name|;
+ ct.name, NULL AS deliverydate|;
if ($form->{quoted}) {
$query .= qq|$union
p.priceupdate, p.image, p.drawing, p.microfiche,
pg.partsgroup,
'' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
- ct.name|;
+ ct.name, NULL AS deliverydate|;
$query .= qq|$union
SELECT $flds, 'oe' AS module, 'request_quotation' AS type,
# get payment terms
$query = qq|SELECT id, description
FROM payment_terms
- ORDER BY 1|;
+ ORDER BY sortkey|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
}
sub follow_account_chain {
- $main::lxdebug->enter_sub();
+ $main::lxdebug->enter_sub(2);
my ($self, $form, $dbh, $transdate, $accno_id, $accno) = @_;
push(@visited_accno_ids, $accno_id);
}
- $main::lxdebug->leave_sub();
+ $main::lxdebug->leave_sub(2);
return ($accno_id, $accno);
}
sub retrieve_accounts {
- $main::lxdebug->enter_sub();
+ $main::lxdebug->enter_sub(2);
my ($self, $myconfig, $form, $parts_id, $index, $copy_accnos) = @_;
my ($query, $sth, $dbh);
- return $main::lxdebug->leave_sub() if (!defined($form->{"taxzone_id"}));
+ $form->{"taxzone_id"} *= 1;
$dbh = $form->dbconnect($myconfig);
if (!$ref) {
$dbh->disconnect();
- return $main::lxdebug->leave_sub();
+ return $main::lxdebug->leave_sub(2);
}
$ref->{"inventory_accno_id"} = undef unless ($ref->{"is_part"});
"WHERE t.id IN " .
" (SELECT tk.tax_id " .
" FROM taxkeys tk " .
- " WHERE tk.chart_id = $accno_id AND startdate <= $transdate " .
+ " WHERE tk.chart_id = ? AND startdate <= " . quote_db_date($transdate) .
" ORDER BY startdate DESC LIMIT 1) ";
+ @vars = ($accno_id);
$sth = $dbh->prepare($query);
- $sth->execute() || $form->dberror($query);
+ $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
$ref = $sth->fetchrow_hashref();
$sth->finish();
$dbh->disconnect();
unless ($ref) {
- $main::lxdebug->leave_sub();
+ $main::lxdebug->leave_sub(2);
return;
}
# " || taxaccounts_$index " . $form->{"taxaccounts_$index"} .
# " || taxaccounts " . $form->{"taxaccounts"});
- $main::lxdebug->leave_sub();
+ $main::lxdebug->leave_sub(2);
}
+
1;