Dashboard

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

Template : dealer_disburse_block

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

Query :

with q0 as
		  (	
	/*
		Get all records grouped by txn_set_id whose success_ref column is 'NULL'  and head is 'dealer'  and (credit-debit) greater then 0
	*/
	SELECT 
		sublan_id, field1, nbfc 
	FROM
	loan_entries
	WHERE 
		head = 'Dealer'
		AND (success_ref IS NULL or success_ref='') 
		AND txn_ref IS NOT NULL 
	GROUP BY
		sublan_id,
		txn_set_id
	HAVING
	SUM(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 grouped by txn_set_id whose success_ref column is ‘NULL’ and head is ‘dealer’ 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