FROM record_links rl1
LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
+ UNION
+ SELECT rl1.from_id, rl3.to_id
+ FROM record_links rl1
+ JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
+ JOIN record_links rl3 ON (rl2.to_table = rl3.from_table AND rl2.to_id = rl3.from_id)
+ WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar' AND rl3.to_table = 'ar'
) rl
LEFT JOIN ar ON ar.id = rl.to_id
qq| o.transaction_description, | .
qq| o.marge_total, o.marge_percent, | .
qq| o.itime::DATE AS insertdate, | .
+ qq| department.description as department, | .
qq| ex.$rate AS exchangerate, | .
qq| pt.description AS payment_terms, | .
qq| pr.projectnumber AS globalprojectnumber, | .
qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
+ qq|LEFT JOIN department ON (o.department_id = department.id) | .
qq|$periodic_invoices_joins | .
qq|WHERE (o.quotation = ?) |;
push(@values, $quotation);
push(@values, (like($form->{"cp_name"}))x2);
}
- if (!$main::auth->assert('sales_all_edit', 1)) {
+ if ( !(($vc eq 'customer' && $main::auth->assert('sales_all_edit', 1)) || ($vc eq 'vendor' && $main::auth->assert('purchase_all_edit', 1))) ) {
$query .= " AND o.employee_id = (select id from employee where login= ?)";
push @values, $::myconfig{login};
}
"insertdate" => "o.itime",
"taxzone" => "tz.description",
"payment_terms" => "pt.description",
+ "department" => "department.description",
);
if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
$sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
$ref->{billed_amount} = $billed_amount{$ref->{id}};
$ref->{billed_netamount} = $billed_netamount{$ref->{id}};
- $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
- $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
+ if ($ref->{billed_amount} < 0) { # case: credit note(s) higher than invoices
+ $ref->{remaining_amount} = $ref->{amount} + $ref->{billed_amount};
+ $ref->{remaining_netamount} = $ref->{netamount} + $ref->{billed_netamount};
+ } else {
+ $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
+ $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
+ }
$ref->{exchangerate} = 1 unless $ref->{exchangerate};
push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
$id{ $ref->{id} } = $ref->{id};
my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
$form->{taxzone_id} = 0 unless ($form->{taxzone_id});
+ unshift @values, ($form->{taxzone_id}) x 2;
# retrieve individual items
# this query looks up all information about the items
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 chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.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 = ? 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}
# 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 | .
- qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
+ qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber | .
+ qq|FROM tax t | .
+ qq|LEFT JOIN chart c on (c.id = t.chart_id) | .
qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
- my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
+ my $tax_objs = SL::DB::Manager::Tax->get_objects_from_sql(
+ sql => 'SELECT * from tax where chart_id = (SELECT id FROM chart WHERE accno = ?)',
+ args => [ $form->{"${item}_taxnumber"} ]
+ );
+ my $tax_obj;
+ if ( $tax_objs ) {
+ $tax_obj = $tax_objs->[0];
+ }
my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
}