Published on : June 09, 2020

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

Frontend URL : https://loantap.in/banking/workflow-get-instalments/

Main Query :

/* Get all active sublans
 whose instalments are not over,
 lan_id,
 sublan_id,
 nbfc,
 columns selected in query 
*/

SELECT
	 sublan_id as data_id,
	 sublan_id,
	 lan_id,
	 '[env.form_data.nbfc/]' as nbfc
FROM
	active_loans 
WHERE
	nbfc='[env.form_data.nbfc/]'
	
EXCEPT

	SELECT
		 sublan_id as data_id,
		 sublan_id,
		 lan_id, 
		 '[env.form_data.nbfc/]' as nbfc
	FROM
		loan_entries
	WHERE
		account='Instalments Over'
		AND loan_entries.sublan_id!='LAN'

Filter :

/* join and 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
	pquery.*,
	max(due_date) as next_instalment_date
FROM
	[esc.table module.previous_query_no /] AS pquery 
JOIN
	loan_entries 
ON
	pquery.sublan_id=loan_entries.sublan_id 
WHERE
	account='Next Instalment' 
	AND due_date is not null 
GROUP BY 
	loan_entries.sublan_id 
HAVING 
	max(due_date) <= date('[env.form_data.next_instalment_till/]')

Filter :

/* Join sublan table on sublan_id
 and get Get lapp_id
 to load lapp data 
 */

SELECT pquery.*,
	meta_value as lapp_id
FROM
	[esc.table module.previous_query_no /] AS pquery
JOIN 
	sublan 
ON
	 pquery.sublan_id=sublan.object_id 
WHERE 
	coll_id='sublan' 
	AND meta_key="lapp_id"

Filter :


/* Join lapp_meta table on lapp_id
 to filter records if scheme_id filter not empty */

[filter new
	meta_table='lapp_meta'
	coll_id='lapp'
	id_field='object_id'
	meta_key='req_scheme_id'
	meta_value="{form_data.req_scheme_id}"
	ref_id='lapp_id'
	not_empty="{form_data.req_scheme_id}"
/]

Filter : 

/* Join sublan table on sublan_id
 to filter records if product_id filter not empty */

[filter new 
	meta_table='sublan'
	coll_id='sublan'
	id_field='object_id'
	meta_key='product_id'
	meta_value="{form_data.product_id}"
	ref_id='sublan_id'
	not_empty="{form_data.product_id}"
/] 

Columns Used in UI :

From Sublan.get_health:

installment_method

[env.sublan_obj.product.instalment.instalment_method/]

[env.sublan_obj.common.instalment_method/]

lapp_status_label

[env.sublan_obj.lapp.lapp_status_label/]

scheme_id OR product_id

[env.sublan_obj.sublan.scheme_id/]

[env.sublan_obj.sublan.core.product_id/]

previous_instalment

[env.sublan_obj.history.previous_instalment/]

previous_instalment_posted_date

[env.sublan_obj.history.previous_instalment_posted_date date_format="d M Y"/]

next_instalment_amount

[env.sublan_obj.current_meta.next_instalment_amount/]

instalment_amount

[env.sublan_obj.common.tentative_adb_instalment.instalment.instalment_amount/]

instalments_total

[env.sublan_obj.current_meta.instalments_total/]

past_instalments

[env.sublan_obj.current_meta.past_instalments/]

instalments_left

[env.sublan_obj.current_meta.instalments_left/]

next_instalment_date_label

[env.sublan_obj.current_meta.next_instalment_date_label/]

next_instalment_due_date_label

[env.sublan_obj.current_meta.next_instalment_due_date_label/]

end_date_label

[env.sublan_obj.current_meta.end_date_label/]

Direct :

Data_id

Sublan_id

sublan_id

sublan_id

lan_id

lan_id

lapp_id

lapp_id

Nbfc

Nbfc

Description :

1) Get all active sublans whose instalments are not over, lan_id, sublan_id, nbfc columns selected in query
2) Join and 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 sublan table on sublan_id and get Get lapp_id to load lapp data
4) Join lapp_meta table on lapp_id to filter records if scheme_id filter not empty
5) Join sublan table on sublan_id to filter records if product_id filter not empty