DB_Script/StoredProc/proc_low_txn_report.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[proc_low_txn_report]    Script Date: 4/9/2024 3:54:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_low_txn_report] @FLAG VARCHAR(20)
	,@user VARCHAR(30)
	,@toDate VARCHAR(100) = NULL
	,@postCode VARCHAR(100) = NULL
	,@txnCountType VARCHAR(20) = NULL
	,@txnCount INT = NULL
AS
SET NOCOUNT ON;

IF @FLAG = 'txn-report'
BEGIN
	SET @txnCountType = CASE WHEN @txnCountType = 'gte' THEN '>='
							WHEN @txnCountType = 'lte' THEN '<='
							WHEN @txnCountType = 'eq' THEN '='
							WHEN @txnCountType = 'lt' THEN '<'
							WHEN @txnCountType = 'gt' THEN '>'
						ELSE NULL
						END;

	--WITH CustomerDetails
	--AS (
	--	SELECT customerId
	--		,membershipId
	--		,(ISNULL(firstName, '') + ' ' + ISNULL(middleName, '') + ' ' + ISNULL(lastName1, '')) AS fullName
	--		,email
	--		,mobile
	--		,zipCode
	--		,address + ', ' + ISNULL(city, '') AS Address1
	--		,createdDate
	--	FROM customerMaster
	--	WHERE createdDate BETWEEN '2023-11-01'
	--			AND GETDATE()
	--	)
	--	,TransactionCounts
	--AS (
	--	SELECT cm.customerId
	--		,ISNULL(COUNT(ts.tranId), 0) AS txnCount
	--		,MAX(rt.createdDate) AS lastTxnDate
	--	FROM CustomerDetails cm
	--	LEFT JOIN vwTranSenders ts ON cm.customerId = ts.customerId
	--	LEFT JOIN vwRemitTran rt ON ISNULL(rt.id, rt.holdTranId) = ts.tranId
	--	GROUP BY cm.customerId
	--	)
	--	,TranDetails
	--AS (
	--	SELECT cd.customerId AS customerId
	--		,cd.membershipId
	--		,cd.Address1
	--		,cd.fullName
	--		,cd.email
	--		,cd.mobile
	--		,cd.zipCode
	--		,cd.createdDate
	--		,ISNULL(tc.txnCount, 0) AS txnCount
	--		,tc.lastTxnDate
	--	FROM CustomerDetails cd
	--	LEFT JOIN TransactionCounts tc ON cd.customerId = tc.customerId
	--	)
	--SELECT ROW_NUMBER() OVER (
	--		ORDER BY txnCount DESC
	--		) AS SN
	--	--,customerId
	--	,membershipId
	--	,fullName
	--	,email
	--	,mobile
	--	,zipCode
	--	,Address1
	--	,createdDate
	--	,txnCount
	--	,lastTxnDate
	--FROM TranDetails
	--WHERE lastTxnDate <= CONVERT(DATE, @toDate)
	--	OR lastTxnDate IS NULL
	--ORDER BY txnCount DESC

	DECLARE @sql NVARCHAR(MAX);
	SET @sql = 'WITH CustomerDetails AS (
                    SELECT customerId,
                            membershipId,
                            (ISNULL(firstName, '''') + '' '' + ISNULL(middleName, '''') + '' '' + ISNULL(lastName1, '''')) AS fullName,
                            email,
                            mobile,
                            zipCode,
                            address + '', '' + ISNULL(city, '''') AS Address1,
                            createdDate
                    FROM customerMaster
                    
                ),
                TransactionCounts AS (
                    SELECT cm.customerId,
                            ISNULL(COUNT(ts.tranId), 0) AS txnCount,
                            MAX(rt.createdDate) AS lastTxnDate
                    FROM CustomerDetails cm
                    LEFT JOIN vwTranSenders ts ON cm.customerId = ts.customerId
                    LEFT JOIN vwRemitTran rt ON ISNULL(rt.id, rt.holdTranId) = ts.tranId
                    GROUP BY cm.customerId
                ),
                TranDetails AS (
                    SELECT cd.customerId AS customerId,
                            cd.membershipId,
                            cd.Address1,
                            cd.fullName,
                            cd.email,
                            cd.mobile,
                            cd.zipCode,
                            cd.createdDate,
                            ISNULL(tc.txnCount, 0) AS txnCount,
                            tc.lastTxnDate
                    FROM CustomerDetails cd
                    LEFT JOIN TransactionCounts tc ON cd.customerId = tc.customerId
                )
                SELECT ROW_NUMBER() OVER (ORDER BY txnCount DESC) AS SN,
                        membershipId,
                        fullName,
                        email,
                        mobile,
                        zipCode PostCode,
                        Address1,
                        createdDate,
                        txnCount,
                        lastTxnDate
                FROM TranDetails
                WHERE (lastTxnDate <= CONVERT(DATE, '''+ @toDate +''') OR lastTxnDate IS NULL) ORDER BY lastTxnDate DESC'

	IF ISNULL(@postCode,'') <> ''
	BEGIN
	    SET @postCode = REPLACE(@postCode, ' ', '')
	    SET @sql = @sql + ' AND (LEFT(zipCode, 3) = LEFT(''' + @postCode + ''', 3) OR zipCode = ''' + @postCode + ''')'
	END


	IF ISNULL(@txnCountType,'') <> ''
	BEGIN
		SET @sql = @sql + ' AND txnCount ' + @txnCountType + ' ' + CAST(@txnCount AS VARCHAR(10))
	END

	PRINT(@sql);
	--EXEC(@sql);

	EXEC sp_executesql @sql, 
                           N'@toDate VARCHAR(10), @postCode VARCHAR(100), @txnCount INT', 
                           @toDate, 
                           @postCode, 
                           @txnCount;

	EXEC proc_errorHandler '0'
		,'Report has been prepared successfully.'
		,NULL

	SELECT 'To Date' head
		,@toDate VALUE

	SELECT 'Low Txn report' title
END