# 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);
}
$sth->finish;
- if ($form->{id}) {
- $query = qq|SELECT weightunit
- FROM defaults|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- ($form->{weightunit}) = $sth->fetchrow_array;
- $sth->finish;
-
- } else {
- $query = qq|SELECT weightunit, current_date
- FROM defaults|;
+ if (!$form->{id}) {
+ $query = qq|SELECT current_date FROM defaults|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- ($form->{weightunit}, $form->{priceupdate}) = $sth->fetchrow_array;
+ ($form->{priceupdate}) = $sth->fetchrow_array;
$sth->finish;
}
}
-sub retrieve_taxaccounts {
+sub follow_account_chain {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $form, $dbh, $transdate, $accno_id, $accno) = @_;
+
+ my @visited_accno_ids = ($accno_id);
+
+ my ($query, $sth);
+
+ $query =
+ "SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, " .
+ " cnew.accno " .
+ "FROM chart c " .
+ "LEFT JOIN chart cnew ON c.new_chart_id = cnew.id " .
+ "WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)";
+ $sth = $dbh->prepare($query);
+
+ while (1) {
+ $sth->execute($accno_id) || $form->dberror($query . " ($accno_id)");
+ $ref = $sth->fetchrow_hashref();
+ last unless ($ref && $ref->{"is_valid"} &&
+ !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
+ $accno_id = $ref->{"new_chart_id"};
+ $accno = $ref->{"accno"};
+ push(@visited_accno_ids, $accno_id);
+ }
+
+ $main::lxdebug->leave_sub();
+
+ return ($accno_id, $accno);
+}
+
+sub retrieve_accounts {
$main::lxdebug->enter_sub();
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);
} else {
$transdate = $form->{deliverydate};
}
+ } elsif ($form->{type} eq "credit_note") {
+ $transdate = $form->{invdate};
} else {
$transdate = $form->{transdate};
}
$transdate = $dbh->quote($transdate);
}
- my $inc_exp = $form->{vc} eq "customer" ? "income" : "expense";
-
- my $accno_str = "${inc_exp}_accno_id_$form->{taxzone_id}";
-
$query =
- "SELECT bg.$accno_str AS accno_id, c.accno " .
- "FROM buchungsgruppen bg " .
- "LEFT JOIN chart c ON bg.$accno_str = c.id " .
- "WHERE bg.id = (SELECT p.buchungsgruppen_id FROM parts p WHERE p.id = ?)";
+ "SELECT " .
+ " p.inventory_accno_id AS is_part, " .
+ " bg.inventory_accno_id, " .
+ " bg.income_accno_id_$form->{taxzone_id} AS income_accno_id, " .
+ " bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id, " .
+ " c1.accno AS inventory_accno, " .
+ " c2.accno AS income_accno, " .
+ " c3.accno AS expense_accno " .
+ "FROM parts p " .
+ "LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id " .
+ "LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id " .
+ "LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id " .
+ "LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id " .
+ "WHERE p.id = ?";
$sth = $dbh->prepare($query);
$sth->execute($parts_id) || $form->dberror($query . " ($parts_id)");
my $ref = $sth->fetchrow_hashref();
$sth->finish();
+# $main::lxdebug->message(0, "q $query");
+
if (!$ref) {
$dbh->disconnect();
- return $lxdebug->leave_sub();
+ return $main::lxdebug->leave_sub();
}
- my ($accno_id, $accno) = ($ref->{"accno_id"}, $ref->{"accno"});
- my ($old_accno_id, $old_accno) = ($ref->{"accno_id"}, $ref->{"accno"});
+ $ref->{"inventory_accno_id"} = undef unless ($ref->{"is_part"});
- my @visited_accno_ids = ($accno_id);
-
- $query =
- "SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, " .
- " cnew.accno " .
- "FROM chart c " .
- "LEFT JOIN chart cnew ON c.new_chart_id = cnew.id " .
- "WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)";
- $sth = $dbh->prepare($query);
-
- while (1) {
- $sth->execute($accno_id) || $form->dberror($query . " ($accno_id)");
- $ref = $sth->fetchrow_hashref();
- last unless ($ref && $ref->{"is_valid"} &&
- !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
- $accno_id = $ref->{"new_chart_id"};
- $accno = $ref->{"accno"};
- push(@visited_accno_ids, $accno_id);
+ my %accounts;
+ foreach my $type (qw(inventory income expense)) {
+ next unless ($ref->{"${type}_accno_id"});
+ ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
+ $self->follow_account_chain($form, $dbh, $transdate,
+ $ref->{"${type}_accno_id"},
+ $ref->{"${type}_accno"});
}
-# $main::lxdebug->message(0, "found final accno_id $accno_id accno $accno for old accno_id $old_accno_id accno $old_accno");
+ map({ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} }
+ qw(inventory income expense));
+
+ my $inc_exp = $form->{"vc"} eq "customer" ? "income" : "expense";
+ my $accno_id = $accounts{"${inc_exp}_accno_id"};
$query =
"SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber " .
$sth->finish();
$dbh->disconnect();
- return $lxdebug->leave_sub() unless ($ref);
+ unless ($ref) {
+ $main::lxdebug->leave_sub();
+ return;
+ }
$form->{"taxaccounts_$index"} = $ref->{"accno"};
if ($form->{"taxaccounts"} !~ /$ref->{accno}/) {
# " || taxaccounts_$index " . $form->{"taxaccounts_$index"} .
# " || taxaccounts " . $form->{"taxaccounts"});
- $sth->finish();
-
$main::lxdebug->leave_sub();
}
1;