Published on : June 08, 2020

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

Frontend URL : https://loantap.in/banking/manage-insurance-disburse/loantap_credit

Query :

SELECT 
	ID as data_id,
	txn_set_id,
	account as insurance_code,
	credit,
	reason,
	lan_id,
	sublan_id,
	entry_date,
	txn_ref,
	nbfc
FROM 
	loan_entries 
WHERE 
	head = 'Insurance Dealer'
	AND (success_ref IS NULL or success_ref='')
	AND txn_ref IS NOT NULL 
	AND sublan_id!='LAN'                 
	AND credit > 0
	[if.not_empty env.search.nbfc]
		AND nbfc='[env.search.nbfc/]'
	[/if.not_empty]
AND (field1 IS NULL  OR field1 ='')

Description :

1) Get all records whose
head is ‘Insurance Dealer’
AND success_ref IS NULL or success_ref is empty
AND txn_ref IS NOT NULL
AND sublan_idis not ‘LAN’
AND credit is greater than 0
AND field1 IS NULL OR field1 is empty
2) Filter data if NBFC provided in URL

Add Field :

SELECT
	[module.previous_query_no /].*, object_id as dealer_object_id
FROM
	[module.previous_query_no /] 
LEFT JOIN 
	 common_meta 
          ON 
	[module.previous_query_no /].insurance_code=common_meta.meta_value 
WHERE 
	common_meta.coll_id='dealer' 
	AND common_meta.meta_key='dealer_code' 

Description :
Joined to common_meta table on insurance_code and get dealer_object_id to load dealer data

Columns Used in UI: 

Common Meta:
dealer_bank_name
dealer_name
dealer_bank_account_no
dealer_bank_ifsc_code
dealer_city
dealer_pincode

Direct:
Sublan_id
Lan_id
txn_ref
credit
insurance_code
dealer_object_id
Nbfc
entry_date

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

User Interaction based (UI) :
Generate Login Disbursement Excel Sheet Button – To generate the excel sheet of insurance disbursal

Insurance Disburse Download Queries :
//** Collect selected IDs **//
[template.set ids=”{request.selected_data}”/]

Main Query :

SELECT 
	ID as data_id,
	txn_set_id,
	account as insurance_code,
	credit,
	reason,
	lan_id,
	sublan_id,
	entry_date,
	txn_ref,
	nbfc
FROM 
	loan_entries 
WHERE  
	head = 'Insurance Dealer'
	AND (success_ref IS NULL or success_ref='')  
	AND txn_ref IS NOT NULL 
	AND sublan_id!='LAN'              	 
	AND credit > 0
	AND (field1 IS NULL  OR field1 ='')
	[if.not_empty env.search.nbfc]
		AND nbfc='[env.search.nbfc/]'
	[/if.not_empty]
	AND ID IN ([template.ids.comma.quote_comma/])

Description :

Get all records whose
head is ‘Insurance Dealer’
AND success_ref IS NULL
AND txn_ref IS NOT NULL
AND sublan_id!=’LAN’
AND credit is greater than 0
AND field1 IS NULL OR field1 is empty
AND id are selected id’s

Add Field :

SELECT
	[module.previous_query_no /].*,  object_id as dealer_object_id
FROM
	[module.previous_query_no /] 
LEFT JOIN 
	common_meta 
ON
	[module.previous_query_no /].insurance_code=common_meta.meta_value 
WHERE
	common_meta.coll_id='dealer' 
	AND common_meta.meta_key='dealer_code' 

Description :
Joined to common_meta table on insurance_code and get dealer_object_id to load dealer data

Update rows which downloaded : 

UPDATE
	loan_entries 
SET
	field1='insurance-downloaded' 
WHERE
	head = 'Insurance Dealer' 
	AND (success_ref IS NULL or success_ref='')  
	AND txn_ref IS NOT NULL       	
	AND credit > 0
	AND sublan_id!='LAN'
	AND ID IN ([template.ids.comma.quote_comma/])

Description :
Update rows which downloaded  ‘insurance-downloaded’