Backend URL : https://loantap.in/wp-admin/post.php?post=758518&action=edit
Frontend URL : https://loantap.in/banking
Template : get_instalments
Parameter :
till_date: <date given>
account: “Next Instalment”
For instalment dues block : till_date is <todays_date>
For upcoming instalment dues block : till_date is <+4 day>
Query :
with
q0 as (
/* Get all active sublans whose instalments are not over, lan_id, sublan_id, nbfc columns selected in query */
SELECT lan_id ,sublan_id,nbfc FROM active_loans
EXCEPT
SELECT lan_id ,sublan_id,nbfc FROM loan_entries WHERE account='Instalments Over' AND loan_entries.sublan_id!='LAN'
),
next_instalment as (
/* Get last record of all sublan's whose account name is 'Next Instalment' and due date is not null and records 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 'next_instalment' table on sublan_id column and get all columns from active sublan table and due date from 'next_instalment' table */
SELECT
pquery.*,a.due_date
FROM
q0 as pquery
JOIN
next_instalment as a
ON
pquery.sublan_id=a.sublan_id
),
q3 as (
/* Group all records on nbfc and due date whose due date is smaller than provided date and added new column for total count using count records */
SELECT
count(1) as total,nbfc,due_date
FROM
q2
WHERE
due_date <= date('[template.till_date/]')
GROUP BY
nbfc,due_date
ORDER BY
due_date,nbfc
)
/* join with common_meta to get nbfc Name on nbfc column */
SELECT
q3.*,common_meta.meta_value as nbfc_label
FROM
q3
LEFT JOIN
common_meta
ON
q3.nbfc=common_meta.object_id
AND common_meta.coll_id='nbfc'
AND common_meta.meta_key='nbfc_name'
Description :
1) Get all active sublans whose instalments are not over, lan_id, sublan_id, nbfc columns selected in query
2) Get last record of all sublan's whose account name is 'Next Instalment' and due date is not null and records grouped by sublan_id
3) Join active sublans table with 'next_instalment' table on sublan_id column and get all columns from active sublan table and due date from 'next_instalment' table
4) Group all records on NBFC and due date whose due date is smaller than provided date and added new column for total count using count records
5) Join with common_meta to get nbfc Name on nbfc column
