- $query = qq|SELECT o.id AS orderitems_id,
- c1.accno AS inventory_accno,
- c2.accno AS income_accno,
- c3.accno AS expense_accno,
- p.partnumber, p.assembly, o.description, o.qty,
- o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes,
- o.reqdate, o.project_id, o.serialnumber, o.ship,
- pr.projectnumber,
- pg.partsgroup
- FROM orderitems o
- JOIN parts p ON (o.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 (o.project_id = pr.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE o.trans_id = $form->{id}
- ORDER BY o.$oid{$myconfig->{dbdriver}}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($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};
+ # this query looks up all information about the items
+ # stuff different from the whole will not be overwritten, but saved with a suffix.
+ $query =
+ qq|SELECT o.id AS orderitems_id,
+ 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,
+ oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
+ p.partnumber, p.part_type, p.listprice, o.description, o.qty,
+ p.classification_id,
+ o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type,
+ o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
+ o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
+ o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
+ pr.projectnumber, p.formel,
+ pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
+ FROM orderitems o
+ JOIN parts p ON (o.parts_id = p.id)
+ JOIN oe ON (o.trans_id = oe.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 tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
+ LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
+ LEFT JOIN project pr ON (o.project_id = pr.id)
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
+ ($form->{id}
+ ? qq|WHERE o.trans_id = ?|
+ : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
+ qq|ORDER BY o.trans_id, o.position|;
+
+ @ids = $form->{id} ? ($form->{id}) : @ids;
+ $sth = prepare_execute_query($form, $dbh, $query, @values);
+
+ while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
+ # Retrieve custom variables.
+ my $cvars = CVar->get_custom_variables(dbh => $dbh,
+ module => 'IC',
+ sub_module => 'orderitems',
+ trans_id => $ref->{orderitems_id},
+ );
+ map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
+
+ # Handle accounts.
+ if (!$ref->{"part_type"} eq 'part') {
+ map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
+ }
+ # delete($ref->{"part_inventory_accno_id"});
+
+ # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
+ # unless already present there
+ # remove _oe entries afterwards
+ map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
+ qw|ordnumber transdate cusordnumber|
+ if (@ids);
+ map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
+
+
+
+ while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
+ my $query =
+ qq|SELECT accno AS inventory_accno, | .
+ qq| new_chart_id AS inventory_new_chart, | .
+ qq| date($transdate) - valid_from AS inventory_valid | .
+ qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
+ ($ref->{inventory_accno}, $ref->{inventory_new_chart},
+ $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
+ }
+
+ while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
+ my $query =
+ qq|SELECT accno AS income_accno, | .
+ qq| new_chart_id AS income_new_chart, | .
+ qq| date($transdate) - valid_from AS income_valid | .
+ qq|FROM chart WHERE id = $ref->{income_new_chart}|;
+ ($ref->{income_accno}, $ref->{income_new_chart},
+ $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
+ }
+
+ while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
+ my $query =
+ qq|SELECT accno AS expense_accno, | .
+ qq| new_chart_id AS expense_new_chart, | .
+ qq| date($transdate) - valid_from AS expense_valid | .
+ qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
+ ($ref->{expense_accno}, $ref->{expense_new_chart},
+ $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
+ }
+
+ # delete orderitems_id in collective orders, so that they get cloned no matter what
+ # is this correct? or is the following meant?
+ # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
+ $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;