- $sth->finish;
-
- if ($form->{id}) {
-
- # retrieve invoice
- $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
- a.orddate, a.quodate,
- a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate,
- a.intnotes, a.curr AS currency
- FROM ap a
- WHERE a.id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
- map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
-
- $form->{exchangerate} =
- $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
- "sell");
-
- # get shipto
- $query = qq|SELECT s.* FROM shipto s
- WHERE s.trans_id = $form->{id} AND s.module = 'AP'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
- delete($ref->{id});
- map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
-
- my $transdate =
- $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
-
- if(!$form->{taxzone_id}) {
- $form->{taxzone_id} = 0;
- }
- # 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;
- }
-
- while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
- my $query = qq| SELECT accno AS expense_accno, new_chart_id AS expense_new_chart, date($transdate) - valid_from AS expense_valid FROM chart WHERE id = $ref->{expense_new_chart}|;
- my $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
- ($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
- $stw->finish;
+
+ $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell");
+
+ # get shipto
+ $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
+ delete $ref->{id};
+ map { $form->{$_} = $ref->{$_} } keys %$ref;
+
+ 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.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,
+ 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"});