- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
- p.listprice,
- c1.accno AS inventory_accno,
- c2.accno AS income_accno,
- c3.accno AS expense_accno,
- p.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes,
- pg.partsgroup
- 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};
+ 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 = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
+
+ my $query =
+ qq|SELECT
+ p.id, p.partnumber, p.description, p.sellprice,
+ p.listprice, p.part_type, p.lastcost,
+ p.ean, p.notes,
+ p.classification_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,
+
+ p.unit, p.part_type, p.onhand,
+ p.notes AS partnotes, p.notes AS longdescription,
+ p.not_discountable, p.formel, p.payment_id AS part_payment_id,
+ p.price_factor_id, p.weight,
+
+ pfac.factor AS price_factor,
+ pt.used_for_sale AS used_for_sale,
+ 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.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c2.id)
+ LEFT JOIN chart c3 ON
+ ((SELECT tc.expense_accno_id
+ FROM taxzone_charts tc
+ WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_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;
+
+ 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_sale} ) {
+ $has_wrong_pclass = PCLASS_NOTFORSALE ;
+ 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});
+
+ 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->{"${type}_accno"},
+ $ref->{"${type}_new_chart"},
+ $ref->{"${type}_valid"})
+ = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
+ }
+ }
+
+ if ($form->{payment_id} eq "") {
+ $form->{payment_id} = $form->{part_payment_id};
+ }
projects / mfinanz.git / blobdiff