- #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, t.taxdescription, t.rate, t.taxnumber
- FROM tax t LEFT join chart c ON (c.id=t.chart_id)
- WHERE 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} = "";
- my $i = 0;
- while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
-
- # if ($customertax{$ref->{accno}}) {
- if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
- $i++;
- $ptr->{accno} = $i;
- }
- $ref->{taxaccounts} .= "$ptr->{accno} ";
- if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
- $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
- $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
- $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.id AS invoice_id,
+ i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber,
+ i.price_factor_id, i.price_factor, i.marge_price_factor, i.discount,
+ 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}));
+
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ # Retrieve custom variables.
+ my $cvars = CVar->get_custom_variables(dbh => $dbh,
+ module => 'IC',
+ sub_module => 'invoice',
+ trans_id => $ref->{invoice_id},
+ );
+ map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
+ delete $ref->{invoice_id};
+
+ map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
+ 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{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
+ }
+ }
+
+ # get tax rates and description
+ my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
+ $query =
+ qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
+ LEFT JOIN chart c ON (c.id = t.chart_id)
+ WHERE t.id in
+ (SELECT tk.tax_id FROM taxkeys tk
+ WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
+ AND (startdate <= $transdate)
+ ORDER BY startdate DESC
+ LIMIT 1)
+ ORDER BY c.accno|;
+ my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
+ $ref->{taxaccounts} = "";