Get NACH Data

Backend URL : https://loantap.in/wp-admin/post.php?post=1080303&action=edit

Frontend URL : https://loantap.in/banking/workflow-nach-for-bank/

VAR Query :

/* Create TEMPORARY TABLEs dispositions and nachs 
	dispositions: Get the last Disposition record for all sublans
	nachs:  Get the last NACH record for all sublans
*/

 /* Find the last Disposition for all accounts */
	CREATE TEMPORARY TABLE dispositions SELECT * FROM (                   
		SELECT 
			* ,ROW_NUMBER() OVER (PARTITION BY sublan_id ORDER BY ID DESC) AS row_num 
		FROM 
			loan_entries 
		WHERE 
			loan_entries.head='NACH Disposition'
	) AS all_dispositions 
	WHERE row_num=1;

	/* Find the last NACHS for all accounts */
	CREATE TEMPORARY TABLE nachs SELECT * FROM (                    
		SELECT 
			* ,ROW_NUMBER() OVER (PARTITION BY sublan_id ORDER BY ID DESC) AS row_num 
		FROM 
			loan_entries 
		WHERE 
			loan_entries.account='Next NACH'
	) AS all_nachs 
	WHERE row_num=1;

Main Query :

/* Get all active  sublan_id records whose NACH is not OVER */ 
SELECT sublan_id FROM active_loans
EXCEPT
SELECT sublan_id FROM loan_entries WHERE account='NACH Over'  

Filter :

/* Join all active loans
with  nach table
on sublan_id 
whose due date is less than settlement_date */
SELECT 
  nachs.* 
FROM 
  [esc.table module.previous_query_no /] as pq 
JOIN 
  nachs 
ON 
  pq.sublan_id=nachs.sublan_id 
WHERE 
  date(due_date) <= date('[env.form_data.settlement_date /]')

Filter :

/* Filter records on exact_date
if user choose than due date
should equal to user exact_date */
SELECT 
	* 
FROM
	[esc.table module.previous_query_no /] 
WHERE 
	date(due_date) = date('[env.form_data.exact_date /]')

Filter :

/*Filter records on nbfc if user choose than nbfc should equal to user nbfc  */
SELECT 
	* 
FROM 
	[esc.table module.previous_query_no /] 
WHERE 
	nbfc='[env.form_data.nbfc/]' 

Filter :

/* Join dispositions table and add new columns  
	account as disposition, 
	field2 as bank_name,
	field1 as cheque_no
*/

SELECT pq.*,
	dispositions.account as disposition,
	dispositions.field2 as bank_name,
	dispositions.field1 as cheque_no
FROM 
	[esc.table module.previous_query_no /] as pq
LEFT JOIN 
	dispositions
ON 
	pq.sublan_id=dispositions.sublan_id
	AND pq.ID < dispositions.ID

Filter :