- $ref = $sth->fetchrow_hashref(NAME_lc);
- map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
-
- # retrieve individual items
- $query = qq|SELECT c1.accno AS inventory_accno,
- c2.accno AS income_accno,
- c3.accno AS expense_accno,
- p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice,
- 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 (p.inventory_accno_id = c1.id)
- LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
- LEFT JOIN chart c3 ON (p.expense_accno_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)) {
-
- #set expense_accno=inventory_accno if they are different => bilanz
- $vendor_accno =
- ($ref->{expense_accno} != $ref->{inventory_accno})
- ? $ref->{inventory_accno}
- : $ref->{expense_accno};
- $vendor_accno =
- ($ref->{inventory_accno})
- ? $ref->{inventory_accno}
- : $ref->{expense_accno};
-
- # get tax rates and description
- $accno_id =
- ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
- $query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
- FROM chart c, tax t
- WHERE c.id=t.chart_id AND t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
- ORDER BY accno|;
- $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
- $ref->{taxaccounts} = "";
- while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
-
- # if ($customertax{$ref->{accno}}) {
- $ref->{taxaccounts} .= "$ptr->{accno} ";
- if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
- $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
- $form->{"$ptr->{accno}_description"} = $ptr->{description};
- $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
- $form->{taxaccounts} .= "$ptr->{accno} ";
- }
+ my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
+ my $taxzone_id = $form->{taxzone_id} * 1;
+
+ $taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id));
+
+ # 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,
+
+ i.description, i.qty, i.fxsellprice AS sellprice,
+ i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber,
+
+ p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, p.bin,
+ pr.projectnumber,
+ 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_${taxzone_id}
+ FROM buchungsgruppen
+ WHERE id = p.buchungsgruppen_id) = c2.id)
+ LEFT JOIN chart c3 ON
+ ((SELECT expense_accno_id_${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 = ?
+
+ ORDER BY i.id|;
+ $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));