Dashboard

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

Template : insurance_disburse_block

Frontend URL : https://loantap.in/banking

Query :

with q0 as
(	
/*
Get all records whose head is 'Insurance Dealer' success_ref column is 'NULL'  and txn_ref column is not 'NULL' and (credit-debit) greater then 0
*/
	SELECT 
		sublan_id,field1,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-debit) > 0 
					   
),              
q1 as (
	/*
		Add two new columns to count  ready and suspense 
			Ready: If Field1 empty column value is 1 else 0 
			Suspense: If Field1 not empty column value is 1 else 0 
	*/
	SELECT 
		nbfc, 
		IF(field1 IS NULL or field1 = '', 1, 0) AS ready, 
		IF(field1 IS NOT NULL or field1 != '', 1, 0) AS suspense
	FROM 
		q0 
),
q2 as (
	/* 
	Group by NBFC to get counts
		Count of all records as total
		Sum of ready columns as ready
		Sum of suspense columns as suspense
	*/
	SELECT 
		nbfc,count(1) AS total,
		sum(ready) AS ready,
		sum(suspense) AS suspense 
	FROM 
		q1 
	GROUP BY 
		nbfc
)
/* join with common_meta to get nbfc Name */
SELECT 
	q2.*,meta_value AS nbfc_label 
FROM 
	q2 
JOIN 
	common_meta AS a 
ON 
	q2.nbfc=a.object_id 
	AND a.coll_id='nbfc' 
AND a.meta_key='nbfc_name'

Description :

1) Get all records whose head is ‘Insurance Dealer’
success_ref column is ‘NULL’
and txn_ref column is not ‘NULL’
and (credit-debit) greater then 0
2) Add two new columns to count ready and suspense
Ready: If Field1 empty column value is 1 else 0
Suspense: If Field1 not empty column value is 1 else 0
3) Group by NBFC to get counts
Count of all records as total
Sum of ready columns as ready
Sum of suspense columns as suspense
4) Join with common_meta on nbfc column to get nbfc Name