Backend URL : https://loantap.in/wp-admin/post.php?post=758518&action=edit
Frontend URL :https://loantap.in/banking
Template : get_instalment_count
Parameter :
till_date: <date given>
account: “Next NACH”
For NACH update block : till_date is <todays_date>
For NACH to be presented block : till_date is <+4 day>
Query :
with q0 as (
/* Get all active sublans whose
instalments are not over,
columns selected in query sublan_id, nbfc */
SELECT sublan_id, nbfc FROM active_loans
EXCEPT
SELECT sublan_id, nbfc FROM loan_entries
WHERE account='NACH Over'
AND loan_entries.sublan_id!='LAN'
),
max_nach as (
/* Get last record of all sublan's
whose account name is 'Next NACH'
and due date not null
and grouped by sublan_id */
SELECT
max(due_date) as due_date, sublan_id
FROM
loan_entries
WHERE
account='[template.account/]'
AND due_date is not null
GROUP BY
sublan_id
),
q2 as (
/* Joined active sublans table with last records table
on sublan_id and get all columns from active sublan table and due date from last records table */
SELECT
pquery.*, a.due_date
FROM
q0 as pquery
JOIN
max_nach as a
ON
pquery.sublan_id=a.sublan_id
),
q3 as (
/*
Add two new columns to count posted and not_posted
posted : If due_date greater than column value is 1 else 0
not_posted: If due_date less than equal to column value is 1 else 0
*/
SELECT
nbfc, due_date,
CASE
WHEN due_date > date('[template.till_date/]') THEN 1
ELSE 0
END as posted,
CASE
WHEN due_date <= date('[template.till_date/]') THEN 1
ELSE 0
END as not_posted
FROM
q2
),
q4 as (
/*
Group by NBFC to get counts
Sum of posted columns as posted_total
Sum of not_posted columns as not_posted_total
*/
SELECT
nbfc,
sum(posted) as posted_total,
sum(not_posted) as not_posted_total
FROM
q3
GROUP BY
nbfc
)
/*join with common_meta to get nbfc Name*/
SELECT
q4.*, common_meta.meta_value as nbfc_label
FROM
q4
LEFT JOIN
common_meta
ON
q4.nbfc=common_meta.object_id
AND common_meta.coll_id='nbfc'
AND common_meta.meta_key='nbfc_name'
ORDER BY
nbfc_label
Description :
1) Get all active sublans whose instalments are not over, columns selected in query sublan_id, nbfc
2) Get last record of all sublan's whose account name is 'Next NACH' and due date not null and grouped by sublan_id
3) Joined active sublans table with last records table on sublan_id and get all columns from active sublan table and due date from last records table
4) Add two new columns to count posted and not_posted
posted : If due_date greater than column value is 1 else 0
not_posted: If due_date less than equal to column value is 1 else 0
5) Group by NBFC to get counts
Sum of posted columns as posted_total
Sum of not_posted columns as not_posted_total
6) join with common_meta on nbfc column to get nbfc Name
