Published on : June 09, 2020

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

Template :multi_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 'Multi Dealer' and subgroup='Dealer Payment Account'   and (credit-debit) greater then 0
	*/
	
	  SELECT 
		sublan_id, field1 
	  FROM
		loan_entries
	  WHERE 
		  head = 'Multi Dealer'
		  AND (credit-debit) > 0 
		  AND success_ref IS NULL 
		  AND txn_ref IS NOT NULL 
		  AND subgroup='Dealer Payment Account' 
	  GROUP BY
		 txn_set_id
  ),
  q1 as
  (	
	/*Get scheme_id from sublan where meta_key in ('scheme_id','product_id')*/
		SELECT
			field1, meta_value as scheme_id 
		FROM
			q0 
		JOIN
			sublan 
		ON
			q0.sublan_id=sublan.object_id 
		WHERE
			meta_key in ('scheme_id','product_id') 
  ),
  q2 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
			scheme_id, 
			IF(field1 IS NULL or field1 = '', 1, 0) as ready, 
			IF(field1 IS NOT NULL or field1 != '', 1, 0) as suspense
		FROM
			q1 
  )
	/*
		Group by scheme_id to get counts
			Count of all records as total
			Sum of ready columns as ready
			Sum of suspense columns as suspense
	*/
	  SELECT
		scheme_id, count(1) as total, sum(ready) as ready, sum(suspense) as suspense 
	  FROM
		q2 
	  GROUP BY 
		scheme_id