Published on : June 05, 2020

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

Frontend URL : https://loantap.in/banking/manage-ready-to-disburse-v2/loantap_credit


Main Query :

/* get all active loans who have account 'Ready to Disburse' in loan entries 

select 
    active_loans.* 
from 
  active_loans 
join 
  loan_entries 
on 
  active_loans.sublan_id=loan_entries.sublan_id 
where 
  loan_entries.account='Ready to Disburse'

Filter 1 :

/*remove loan records those are disbursed*/

  Select
	   [module.previous_query_no /].*,loan_entries.account 
  from 
	  [module.previous_query_no /]
  left join 
	  loan_entries 
  on 
	  [module.previous_query_no /].sublan_id=loan_entries.sublan_id 
  and 
	  loan_entries.account='Loan Disbursed'  
  where 
	  account is null


Filter 2 :

 /*Join sublan table to get lapp_id on sublan_id*/

	select 
		pquery.*,meta_value as lapp_id,object_id as data_id 
	from 
		[module.previous_query_no /] as pquery 
	join 
		sublan 
	on 
		pquery.sublan_id=sublan.object_id 
	where 
meta_key='lapp_id'

Filter 3 :

/*When scheme ID filter used */
 [filter new meta_table=’sublan’ coll_id=’sublan’ id_field=’object_id’ meta_key=’scheme_id’ meta_value=”{form_data.req_scheme_id}” ref_id=’sublan_id’ not_empty=”{form_data.req_scheme_id}”/]

Filter 4 :

/*When Product filter used */
[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}”/]

Filter 5 :

/*When City filter used */
[filter new meta_table=’lapp_meta’ coll_id=’lapp’ id_field=’object_id’ meta_key=’loan_city’ meta_value=”{form_data.loan_city}” ref_id=’lapp_id’ not_empty=”{form_data.loan_city}”/]

Filter 6 :

/*When URL contains nbfc */
select * from [module.previous_query_no /] where nbfc='[env.search.nbfc/]’

Columns Used In UI:

From Lapp :

loan_city_label
fixed_income
mobile_number
personal_email
channel_code,
crif_score,
cibil_score
full_name
lapp_id
lapp_status
paytm_custid

From Sublan:
loan_tenure
tenure
scheme_id
product_id
sanction_amount
sublan_id
lan_id

External action:
Sublan Id have action link: open transaction page in new window 

User Interaction based Filters (UI):
Scheme DropDown – Taken from loan_scheme table
Product DropDown – Build from sublan table
Loan City DropDown – Used City taxonomy

Note: user can select scheme or product only one filter at a time, This is managed through JS to restrict user to choose both