- $query = qq|SELECT (SELECT c.accno FROM chart c
- WHERE p.inventory_accno_id = c.id)
- AS inventory_accno,
- (SELECT c.accno FROM chart c
- WHERE p.income_accno_id = c.id)
- AS income_accno,
- (SELECT c.accno FROM chart c
- WHERE p.expense_accno_id = c.id)
- AS expense_accno,
- i.description, i.qty, i.fxsellprice AS sellprice,
- i.discount, i.parts_id AS id, i.unit, i.deliverydate,
- i.project_id, pr.projectnumber, i.serialnumber,
- p.partnumber, p.assembly, p.bin, p.notes AS partnotes, i.id AS invoice_pos,
- pg.partsgroup
- FROM invoice i
- JOIN parts p ON (i.parts_id = p.id)
- LEFT JOIN project pr ON (i.project_id = pr.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE i.trans_id = $form->{id}
- AND NOT i.assemblyitem = '1'
- ORDER BY i.id|;
- $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};
-
-
- # get tax rates and description
- $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
- $query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
- FROM chart c, tax t
- WHERE c.id=t.chart_id AND 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} = "";
- while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
- # if ($customertax{$ref->{accno}}) {
- $ref->{taxaccounts} .= "$ptr->{accno} ";
- if (!($form->{taxaccounts}=~/$ptr->{accno}/)) {
- $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
- $form->{"$ptr->{accno}_description"} = $ptr->{description};
- $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
- $form->{taxaccounts} .= "$ptr->{accno} ";
- }
-
- }
-
- if ($form->{lizenzen}) {
- $query = qq|SELECT l.licensenumber, l.id AS licenseid
- FROM license l, licenseinvoice li
- WHERE l.id = li.license_id AND li.trans_id = $ref->{invoice_pos}|;
- $stg = $dbh->prepare($query);
- $stg->execute || $form->dberror($query);
- ($licensenumber, $licenseid) = $stg->fetchrow_array();
- $ref->{lizenzen} ="<option value=\"$licenseid\">$licensenumber</option>";
- $stg->finish();
- }
-
- chop $ref->{taxaccounts};
- push @{ $form->{invoice_details} }, $ref;
- $stw->finish;
+ $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.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
+ i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
+ i.price_factor_id, i.price_factor, i.marge_price_factor,
+ p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
+ pr.projectnumber, pg.partsgroup, prg.pricegroup
+
+ FROM invoice i
+ LEFT JOIN parts p ON (i.parts_id = p.id)
+ LEFT JOIN project pr ON (i.project_id = pr.id)
+ LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+ LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.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)
+
+ WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
+
+ $sth = prepare_execute_query($form, $dbh, $query, $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 <= date($transdate)
+ ORDER BY startdate DESC LIMIT 1)
+ ORDER BY c.accno|;
+ my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
+ $ref->{taxaccounts} = "";
+ my $i=0;
+ while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
+
+ if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
+ $i++;
+ $ptr->{accno} = $i;
+ }
+ $ref->{taxaccounts} .= "$ptr->{accno} ";
+
+ if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
+ $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
+ $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
+ $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
+ $form->{taxaccounts} .= "$ptr->{accno} ";
+ }
+
+ }
+
+ if ($form->{lizenzen}) {
+ $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
+ my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
+ $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
+ }
+
+ $ref->{qty} *= -1 if $form->{type} eq "credit_note";
+
+ chop $ref->{taxaccounts};
+ push @{ $form->{invoice_details} }, $ref;
+ $stw->finish;
projects / mfinanz.git / blobdiff