- # retrieve individual items
- $query = qq|SELECT c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
- c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
- c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
- p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice, p.inventory_accno_id AS part_inventory_accno_id,
- i.parts_id AS id, i.unit, p.bin, i.deliverydate,
- pr.projectnumber,
- i.project_id, i.serialnumber,
- pg.partsgroup
- FROM invoice i
- JOIN parts p ON (i.parts_id = p.id)
- LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
- LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
- LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
- LEFT JOIN project pr ON (i.project_id = pr.id)
- LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
- WHERE i.trans_id = $form->{id}
- ORDER BY i.id|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- if (!$ref->{"part_inventory_accno_id"}) {
- map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
- }
- delete($ref->{"part_inventory_accno_id"});
-
- while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
- my $query = qq| SELECT accno AS inventory_accno, new_chart_id AS inventory_new_chart, date($transdate) - valid_from AS inventory_valid FROM chart WHERE id = $ref->{inventory_new_chart}|;
- my $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
- ($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
- $stw->finish;
- }
-
- while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
- my $query = qq| SELECT accno AS income_accno, new_chart_id AS income_new_chart, date($transdate) - valid_from AS income_valid FROM chart WHERE id = $ref->{income_new_chart}|;
- my $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
- ($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
- $stw->finish;
+ delete($ref->{"part_inventory_accno_id"});
+
+ foreach my $type (qw(inventory income expense)) {
+ while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
+ my $query =
+ qq|SELECT accno, new_chart_id, date($transdate) - valid_from
+ FROM chart
+ WHERE id = ?|;
+ ($ref->{"${type}_accno"},
+ $ref->{"${type}_new_chart"},
+ $ref->{"${type}_valid"})
+ = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});