Dashboard

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

Template : bank_disburse_block

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

Query :

with q0 as
	  (	
/* 
        Get all records  whose head is 'bank'
        and success_ref column is 'NULL'
        and  txn_ref is not null
        and sublan_id is not 'LAN'
        and (credit-debit) greater then 0
*/


		SELECT 
			sublan_id,field1,nbfc 
		FROM
			loan_entries
		WHERE 
			head = 'Bank'
			AND (success_ref IS NULL or success_ref='')  
			AND txn_ref IS NOT NULL 
			AND sublan_id!='LAN'	
			AND credit > 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 ‘bank’ and credit is greater then
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