- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query = qq|SELECT p.id, p.partnumber, p.description,
- c1.accno AS inventory_accno,
- c2.accno AS income_accno,
- c3.accno AS expense_accno,
- pg.partsgroup,
- p.lastcost AS sellprice, p.unit, p.bin, p.onhand, p.notes AS partnotes
- FROM parts p
- 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 partsgroup pg ON (pg.id = p.partsgroup_id)
- WHERE $where|;
- my $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};
+ my $transdate = "";
+ if ($form->{type} eq "invoice") {
+ $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
+ : $form->{invdate} ? $dbh->quote($form->{invdate})
+ : "current_date";
+ } else {
+ $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
+ }
+
+ my $taxzone_id = $form->{taxzone_id} * 1;
+ $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);
+
+ my $query =
+ qq|SELECT
+ p.id, p.partnumber, p.description, p.lastcost AS sellprice, p.listprice,
+ p.unit, p.part_type, p.onhand, p.formel,
+ p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
+ p.price_factor_id,
+ p.ean,
+ p.classification_id,
+
+ pfac.factor AS price_factor,
+
+ 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,
+
+ pt.used_for_purchase AS used_for_purchase,
+ pg.partsgroup
+
+ FROM parts p
+ 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 = '$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 = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
+ LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+ LEFT JOIN part_classifications pt ON (pt.id = p.classification_id)
+ LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
+ WHERE $where|;
+ my $sth = prepare_execute_query($form, $dbh, $query, @values);
+
+ my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
+ FROM translation tr
+ WHERE tr.language_id = ? AND tr.parts_id = ?| ],
+ [ qq|SELECT tr.translation, tr.longdescription
+ FROM translation tr
+ WHERE tr.language_id IN
+ (SELECT id
+ FROM language
+ WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
+ AND tr.parts_id = ?
+ LIMIT 1| ] );
+ map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
+
+ $form->{item_list} = [];
+ my $has_wrong_pclass = PCLASS_OK;
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+
+ if ($mm_by_id{$ref->{id}}) {
+ $ref->{makemodels} = $mm_by_id{$ref->{id}};
+ push @{ $ref->{matches} ||= [] }, $::locale->text('Model') . ': ' . join ', ', map { $_->{model} } @{ $mm_by_id{$ref->{id}} };
+ }
+
+ if (($::form->{"partnumber_$i"} ne '') && ($ref->{ean} eq $::form->{"partnumber_$i"})) {
+ push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean};
+ }
+ $ref->{type_and_classific} = $::request->presenter->type_abbreviation($ref->{part_type}).
+ $::request->presenter->classification_abbreviation($ref->{classification_id});
+
+ if (! $ref->{used_for_purchase} ) {
+ $has_wrong_pclass = PCLASS_NOTFORPURCHASE;
+ next;
+ }
+ # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
+ # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
+ # Buchungskonto also aus dem Ergebnis rausgenommen werden.
+ if (!$ref->{inventory_accno_id}) {
+ map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
+ }
+ delete($ref->{inventory_accno_id});