Published on : June 08, 2020

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

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

Query : 

SELECT 
	txn_set_id as data_id,
	txn_set_id,
	account as dealer_code 
FROM 
	loan_entries
WHERE 
	head = 'Dealer'
	AND success_ref IS NULL 
	AND txn_ref IS NOT NULL
	AND loan_entries.sublan_id!='LAN'                       
	AND (loan_entries.field1 IS NULL  OR loan_entries.field1 ='')
	[if.not_empty env.search.nbfc]
	AND nbfc='[env.search.nbfc/]'
	[/if.not_empty]
GROUP BY
	txn_set_id    
HAVING
	SUM(credit-debit) > 0 

Description :

1) Get all records whose
head is ‘Dealer’
AND success_ref IS NULL
AND txn_ref IS NOT NULL
AND sublan_id NOT ‘LAN’
AND field1 is NULL or empty
2) Group by records on txn_set_id
3) Filter data if NBFC provided in URL

Filter :

[filter new]
   [sql raw] 
	SELECT 
		[module.previous_query_no /].*,
		sum(credit-debit) as debit,
		lan_id,
		entry_date,
		txn_ref,
		nbfc
	FROM 
		[module.previous_query_no /] 
	JOIN 
		loan_entries 
	ON 
		[module.previous_query_no /].txn_set_id=loan_entries.txn_set_id
	WHERE
	  loan_entries.head = 'Dealer' 
	   AND (loan_entries.success_ref IS NULL OR loan_entries.success_ref='') 
	   AND loan_entries.txn_ref IS NOT NULL
	   AND loan_entries.sublan_id!='LAN' 
	   AND (loan_entries.field1 IS NULL  OR loan_entries.field1 ='')
	[if.not_empty env.search.nbfc]
	AND loan_entries.nbfc='[env.search.nbfc/]'
	[/if.not_empty]
	GROUP BY
          [module.previous_query_no /].sublan_id, 
          [module.previous_query_no /].txn_set_id      
   [/sql]
[/filter]

Description :

1) Join loan_entries table on txn_set_id and Keep those records whose
head is ‘Dealer’
AND success_ref IS NULL
AND txn_ref IS NOT NULL
AND sublan_id NOT ‘LAN’
AND field1 is NULL or empty
2) Group by records on txn_set_id
3) And get columns
sum(credit-debit) as debit,
lan_id,
sublan_id,
entry_date,
txn_ref,
Nbfc
4) Then 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 /].dealer_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 dealer_code and get dealer_object_id to load dealer details

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
txn_set_id
Debit
dealer_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 dealer disbursal

Dealer Disburse Download Queries:

//** Collect selected IDs **//
[template.set txn_set_ids=”{request2.selected_data}”/]

Main Query :

SELECT 
	sublan_id,txn_set_id as data_id,
	txn_set_id,
	account as dealer_code 
 FROM 
	loan_entries
WHERE 
	head = 'Dealer'
	AND (success_ref IS NULL OR success_ref='')  
	AND txn_ref IS NOT NULL 
	AND sublan_id!='LAN'
	AND (field1 IS NULL  OR field1 ='')
	AND txn_set_id IN ([template.txn_set_ids.comma.quote_comma/])
 ROUP BY 
	sublan_id,txn_set_id  
HAVING 
	SUM(credit-debit) > 0 

Filter :

SELECT 
	[module.previous_query_no /].*,
	sum(credit-debit) as debit,
	lan_id,                            
	entry_date,
	txn_ref,
	nbfc
FROM 
	[module.previous_query_no /] 
JOIN 
	loan_entries on [module.previous_query_no /].txn_set_id=loan_entries.txn_set_id
WHERE
	loan_entries.head = 'Dealer' 
	AND (loan_entries.success_ref IS NULL OR loan_entries.success_ref='')   
	AND loan_entries.txn_ref IS NOT NULL                           	
	AND loan_entries.sublan_id!='LAN'
	AND (loan_entries.field1 IS NULL  OR loan_entries.field1 ='')
GROUP BY 
	[module.previous_query_no /].sublan_id,
	[module.previous_query_no /].txn_set_id

Description :

1) Join loan_entries table on txn_set_id and Keep those records whose
head is ‘Dealer’
AND success_ref IS NULL
AND txn_ref IS NOT NULL
AND sublan_id NOT ‘LAN’
AND field1 IS NULL or empty
2) Group by records on sublan_id, txn_set_id
3) And get columns
sum(credit-debit) as debit,
lan_id,
sublan_id,
entry_date,
txn_ref,
Nbfc

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 /].dealer_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 dealer_code and get dealer_object_id to load dealer details

Update rows which downloaded : 

UPDATE 
  loan_entries 
SET 
  field1='dealer-downloaded' 
WHERE 
  txn_set_id IN ([template.txn_set_ids.comma.quote_comma/]) 
  AND head!='Insurance Dealer'  
  AND txn_ref IS NOT NULL 
  AND (success_ref IS NULL OR success_ref='')  
  AND sublan_id!='LAN'