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