FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.income_accno_id = c.id)
-- use transdate from subwhere
WHERE (c.category = 'I')
$subwhere
FROM invoice ac
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.expense_accno_id = c.id)
WHERE (c.category = 'E')
$subwhere
$dpt_where
FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.income_accno_id = c.id)
-- use transdate from subwhere
WHERE (c.category = 'I')
$subwhere
FROM invoice ac
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.expense_accno_id = c.id)
WHERE (c.category = 'E')
$subwhere
$dpt_where
FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.income_accno_id = c.id)
WHERE (c.category = 'I') $prwhere $dpt_where
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
$project
FROM invoice ac
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.expense_accno_id = c.id)
WHERE (c.category = 'E') $prwhere $dpt_where
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
$project
FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.income_accno_id = c.id)
WHERE (c.category = 'I')
$prwhere
$dpt_where
FROM invoice ac
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.expense_accno_id = c.id)
WHERE (c.category = 'E')
$prwhere
$dpt_where
if ($form->{fromdate} || $form->{todate}) {
if ($form->{fromdate}) {
$fromdate = conv_dateq($form->{fromdate});
+ my $fiscal_year_startdate = conv_dateq($self->get_balance_starting_date($form->{fromdate}));
+ # my $date_trunc = "(select date_trunc('year', date $fromdate))";
$tofrom .= " AND (ac.transdate >= $fromdate)";
$subwhere .= " AND (ac.transdate >= $fromdate)";
- $sumsubwhere .= " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
- $saldosubwhere .= " AND (ac,transdate>=(select date_trunc('year', date $fromdate))) ";
- $invwhere .= " AND (a.transdate >= $fromdate)";
- $glsaldowhere .= " AND ac.transdate>=(select date_trunc('year', date $fromdate)) ";
+ $sumsubwhere .= " AND (ac.transdate >= $fiscal_year_startdate) ";
+ $saldosubwhere .= " AND (ac.transdate >= $fiscal_year_startdate) ";
+ $invwhere .= " AND (a.transdate >= $fromdate)";
+ $glsaldowhere .= " AND (ac.transdate >= $fiscal_year_startdate) ";
$glwhere = " AND (ac.transdate >= $fromdate)";
- $glsumwhere = " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
+ $glsumwhere = " AND (ac.transdate >= $fiscal_year_startdate) ";
}
if ($form->{todate}) {
$todate = conv_dateq($form->{todate});
FROM invoice ac
JOIN ar a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c ON (t.income_accno_id = c.id)
WHERE $invwhere
$dpt_where
$customer_where
FROM invoice ac
JOIN ap a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c ON (t.expense_accno_id = c.id)
WHERE $invwhere
$dpt_where
$customer_no_union
FROM invoice ac
JOIN parts p ON (ac.parts_id = p.id)
JOIN ap a ON (ac.trans_id = a.id)
- JOIN chart c ON (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c ON (t.expense_accno_id = c.id)
WHERE $invwhere
$dpt_where
$customer_no_union
FROM invoice ac
JOIN parts p ON (ac.parts_id = p.id)
JOIN ar a ON (ac.trans_id = a.id)
- JOIN chart c ON (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c ON (t.income_accno_id = c.id)
WHERE $invwhere
$dpt_where
$customer_where
}
- my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo,$soll_kummuliert, $haben_kummuliert, $last_transaction);
+ my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo, $soll_kumuliert, $haben_kumuliert, $last_transaction);
foreach my $accno (sort keys %trb) {
$ref = {};
$main::lxdebug->leave_sub();
}
-sub get_storno {
- $main::lxdebug->enter_sub();
- my ($self, $dbh, $form) = @_;
- my $arap = $form->{arap} eq "ar" ? "ar" : "ap";
- my $query = qq|SELECT invnumber FROM $arap WHERE invnumber LIKE "Storno zu "|;
- my $sth = $dbh->prepare($query);
- while(my $ref = $sth->fetchrow_hashref()) {
- $ref->{invnumer} =~ s/Storno zu //g;
- $form->{storno}{$ref->{invnumber}} = 1;
- }
- $main::lxdebug->leave_sub();
-}
-
sub aging {
$main::lxdebug->enter_sub();
SELECT ${ct}.id AS ctid, ${ct}.name,
street, zipcode, city, country, contact, email,
phone as customerphone, fax as customerfax, ${ct}number,
- "invnumber", "transdate",
+ "invnumber", "transdate", "type",
(amount - COALESCE((SELECT sum(amount)*$ml FROM acc_trans WHERE chart_link ilike '%paid%' AND acc_trans.trans_id=${arap}.id AND acc_trans.transdate <= (date $todate)),0)) as "open", "amount",
- "duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays,
+ "duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays, datepaid, (amount - paid) as current_open,
(SELECT $buysell
FROM exchangerate
WHERE (${arap}.currency_id = exchangerate.currency_id)