AND ac.trans_id IN
(
SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
+ FROM acc_trans a
+ JOIN chart c ON (a.chart_id = c.id)
WHERE (link LIKE '%AR_paid%')
$subwhere
)
AND ac.trans_id IN
(
SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
+ FROM acc_trans a
+ JOIN chart c ON (a.chart_id = c.id)
WHERE (link LIKE '%AP_paid%')
$subwhere
)
AND ac.trans_id IN
(
SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
+ FROM acc_trans a
+ JOIN chart c ON (a.chart_id = c.id)
WHERE (link LIKE '%AR_paid%')
$subwhere
)
AND ac.trans_id IN
(
SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
+ FROM acc_trans a
+ JOIN chart c ON (a.chart_id = c.id)
WHERE link LIKE '%AP_paid%'
$subwhere
)
JOIN ar a ON (a.id = ac.trans_id)
$dpt_join
WHERE $where $dpt_where
- AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere)
+ AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere)
$project
GROUP BY c.$category
*/
JOIN ap a ON (a.id = ac.trans_id)
$dpt_join
WHERE $where $dpt_where
- AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere)
+ AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere)
$project
GROUP BY c.$category
JOIN chart c on (p.income_accno_id = c.id)
$dpt_join
WHERE (c.category = 'I') $prwhere $dpt_where
- AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere)
+ AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere)
$project
GROUP BY c.$category
JOIN chart c on (p.expense_accno_id = c.id)
$dpt_join
WHERE (c.category = 'E') $prwhere $dpt_where
- AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere)
+ AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere)
$project
GROUP BY c.$category
|;
$dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;
$where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
}
+ my $review_of_aging_list;
+ if ($form->{review_of_aging_list}) {
+ if ($form->{review_of_aging_list} =~ m "-"){
+ my @period = split(/-/, $form->{review_of_aging_list});
+ $review_of_aging_list = " AND $period[0] < date_part('days', now() - duedate)
+ AND date_part('days', now() - duedate) < $period[1]";
+ } else {
+ $form->{review_of_aging_list} =~ s/[^0-9]//g;
+ $review_of_aging_list = " AND $form->{review_of_aging_list} < date_part('days', now() - duedate)";
+ }
+ }
my $q_details = qq|
- -- between 0-30 days
SELECT ${ct}.id AS ctid, ${ct}.name,
street, zipcode, city, country, contact, email,
AND (${arap}.${ct}_id = ${ct}.id)
AND (${ct}.id = ?)
AND (transdate <= (date $todate) $fromwhere )
-
+ $review_of_aging_list
ORDER BY ctid, transdate, invnumber |;
my $sth_details = prepare_query($form, $dbh, $q_details);
AND ac.trans_id IN
(
SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
+ FROM acc_trans a
+ JOIN chart c ON (a.chart_id = c.id)
WHERE (link LIKE '%${ARAP}_paid%')
AND (transdate <= $todate)
)