Published on : June 10, 2020
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 :