MOBILE_API/DB/SPs/proc_referralReport.sql

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

--EXEC proc_referralReport @flag = 'transaction-rpt',@startDate = '2018-03-01',@endDate = '2018-03-31',@user = 'admin',@referralCode = '9424010704321',@country = 'Cambodia'

ALTER PROC proc_referralReport
(
	@flag			VARCHAR(50)
	,@startDate		VARCHAR(10)
	,@endDate		VARCHAR(30)
	,@user			VARCHAR(30) = NULL
	,@referralCode	VARCHAR(30) = NULL
	,@country		VARCHAR(50) = NULL
	
)
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @sqlRegister varchar(MAX)

BEGIN
	IF @flag = 'register-rpt'
	BEGIN
	
		set @sqlRegister='SELECT 
								[Referral Name] = cm2.firstName 
								,[Referral Code] = cm.referelCode
								,[Native Country] = cr.countryName 
								,[No Of Registered] = ''<a href="#" onclick="OpenInNewWindow(''''/RemittanceSystem/RemittanceReports/Reports.aspx?reportName=customerreportdrilldowntotalreferrerdetail&startDate=' + @startDate + '&endDate='+ @endDate + 
									'&referralCode=''+cm.referelCode+''&flag=detail-customer-drilldown-report'''')">'' + CAST(count(1) AS VARCHAR) + ''</a>''
							FROM customermaster (NOLOCK) cm
							INNER JOIN dbo.customerMaster (NOLOCK) cm2 ON cm2.WalletAccountNo=cm.referelCode
							JOIN countrymaster (NOLOCK) cr ON cr.countryId=cm.nativeCountry 
							WHERE 1 = 1  AND cm.referelCode IS NOT NULL
							and cm.ApprovedDate BETWEEN ''' + @startDate + ''' AND ''' + @endDate + ' 23:59:59'''
		IF ISNULL(@referralCode, '') <> ''
			SET @sqlRegister = @sqlRegister + ' AND cm.referelCode = ''' + @referralCode + ''''
		
		if @country is not null
			SET @sqlRegister = @sqlRegister + ' AND cr.countryName  = ''' + @country + ''''

		SET @sqlRegister = @sqlRegister+' GROUP BY cm.referelCode, cr.countryName,cm2.firstName  order by 1 '

		EXEC(@sqlRegister)
				
		EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
		
		SELECT  'From Date' head, @startDate value union all
		SELECT  'To Date' head, @endDate value  UNION ALL	
		SELECT 'Referral Code' head, @referralCode
		
		SELECT 'Register Report' title
	END
	ELSE IF @flag = 'transaction-rpt'
	BEGIN
		SET @sqlRegister='
		SELECT 
			[Referral Name] = dbo.FunGetWalletName(cm.referelCode) 
			,[Referral Code] = cm.referelCode
			,[Native Country] = cr.countryName 
			,[No Of Txn] = ''<a href="#" onclick="OpenInNewWindow(''''/RemittanceSystem/RemittanceReports/Reports.aspx?reportName=txnreportdrilldowntotalreferrerdetail&startDate=' + @startDate + '&endDate='+ @endDate + 
								'&referralCode=''+cm.referelCode+''&flag=detail-txn-drilldown-report'''')">'' + CAST(COUNT(1) AS VARCHAR) + ''</a>''
		FROM customermaster (NOLOCK) cm
		JOIN tranSenders (NOLOCK) ts on ts.customerid = cm.customerid
		JOIN remitTran (NOLOCK) rt on rt.id = ts.tranId
		JOIN countrymaster (NOLOCK) cr ON cr.countryId = cm.nativeCountry 
		WHERE 1 = 1 and
		 cm.ApprovedDate BETWEEN ''' + @startDate + ''' AND ''' + @endDate + ' 23:59:59'''
		
		IF ISNULL(@referralCode, '') <> ''
			SET @sqlRegister=@sqlRegister+' AND cm.referelCode = ''' + @referralCode + ''''
		
		if @country is not null
			SET @sqlRegister = @sqlRegister + ' AND cr.countryName  = ''' + @country + ''''

		SET @sqlRegister=@sqlRegister+' GROUP BY cm.referelCode, cr.countryName  order by 1  '
		
		EXEC(@sqlRegister)
		print @sqlRegister
		EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
		
		SELECT  'From Date' head, @startDate value union all
		SELECT  'To Date' head, @endDate value  UNION ALL	
		SELECT 'Referral Code' head, @referralCode
		
		SELECT 'Transaction Report' title		
	END
	ELSE IF @flag = 'detail-customer-report'
	BEGIN
		set @sqlRegister='
						SELECT 
							[Referral Code]	 = cm.referelCode
							,[Referral Name] = dbo.FunGetWalletName(cm.referelCode) 
							,[Customer Name] = cm.fullName
							,[Id Number]	 = cm.idNumber
							,[Mobile No]	 = cm.Mobile
							,[Register Date] = cm.approvedDate
							,[Register By]	 = cm.approvedBy
						FROM customermaster (NOLOCK) cm
						WHERE 1 = 1 and 
						 cm.Approveddate BETWEEN '''+@startDate+''' AND '''+@endDate+''''
		IF ISNULL(@referralCode, '') <> ''
			SET @sqlRegister=@sqlRegister+' AND cm.referelCode='''+@referralCode+''''

		EXEC(@sqlRegister)
				
		EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
		
		SELECT  'From Date' head, @startDate value union all
		SELECT  'To Date' head, @endDate value  UNION ALL	
		SELECT 'Referral Code' head, CASE WHEN @referralCode IS NULL THEN 'N/A' ELSE @referralCode END
		
		SELECT 'Customer Report Drill Down Detail' title
	END
	ELSE IF @flag = 'detail-txn-report'
	BEGIN
		--LEN(cm.referelCode) = 13 AND LEFT(cm.referelCode,5) = ''94240''
		set @sqlRegister='
						SELECT 
							[Referral Code]  = cm.referelCode
							,[Referral Name] = dbo.FunGetWalletName(cm.referelCode) 
							,[Customer Name] = cm.fullName 
							,[Id Number]	 = cm.idNumber
							,[Mobile No]	 = cm.Mobile
							,[Control No]    = dbo.fnadecryptstring(rt.controlNo)
							,[Tran Date]     = rt.approvedDate
						FROM customermaster (NOLOCK) cm
						JOIN tranSenders (NOLOCK) ts on ts.customerid=cm.customerid
						JOIN remitTran (NOLOCK) rt on rt.id=ts.tranId
						WHERE 1 = 1
						'

		IF @startDate IS NOT NULL AND @endDate IS NOT NULL
			SET @sqlRegister = @sqlRegister+' and rt.approvedDate BETWEEN ''' + @startDate + ''' AND ''' + @endDate + ''''
		IF ISNULL(@referralCode, '') <> ''
			SET @sqlRegister = @sqlRegister+' AND cm.referelCode = '''+@referralCode+''''

		EXEC(@sqlRegister)
				
		EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
		
		SELECT  'From Date' head, @startDate value union all
		SELECT  'To Date' head, @endDate value  UNION ALL	
		SELECT 'Referral Code' head, @referralCode
		
		SELECT 'Txn Report Drill Down Detail' title
	END
	ELSE IF @flag = 'detail-customer-drilldown-report'
	BEGIN
		set @sqlRegister='
						SELECT 
							[Referral Name]   = dbo.FunGetWalletName(cm.referelCode) 
							,[Referral Code]  = cm.referelCode
							,[Customer Name]  = cm.fullName
							,[Native Country] = cr.countryName 
							,[Id Number]	  = cm.idNumber
							,[Mobile No]	  = cm.Mobile
							,[Register Date]  = cm.approvedDate
							,[Register By]	  = cm.approvedBy
						FROM customermaster (NOLOCK) cm
						JOIN countrymaster (NOLOCK) cr ON cr.countryId = cm.nativeCountry 
						WHERE 1 = 1'
		
		IF @startDate IS NOT NULL AND @endDate IS NOT NULL
			SET @sqlRegister=@sqlRegister+' and cm.ApprovedDate BETWEEN '''+@startDate+''' AND '''+@endDate+''''
		IF ISNULL(@referralCode, '') <> ''
			SET @sqlRegister=@sqlRegister+' AND cm.referelCode='''+@referralCode+''''
		
		EXEC(@sqlRegister)
				
		EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
		
		SELECT  'From Date' head, @startDate value union all
		SELECT  'To Date' head, @endDate value  UNION ALL	
		SELECT 'Referral Code' head, @referralCode
		
		SELECT 'Detail Report' title
	END
	ELSE IF @flag = 'detail-txn-drilldown-report'
	BEGIN
		set @sqlRegister='
						SELECT 
							[Referral Name]   = dbo.FunGetWalletName(cm.referelCode)
							,[Referral Code]  = cm.referelCode
							,[Customer Name]  = cm.fullName
							,[Native Country] = cr.countryName 
							,[Id Number]	  = cm.idNumber
							,[Mobile No]	  = cm.Mobile
							,[Control No]     = dbo.fnadecryptstring(rt.controlNo)
							,[Sending Amount] = rt.tAmt
						FROM customermaster (NOLOCK) cm
						join tranSenders (nolock) ts on ts.customerid=cm.customerid
						join remitTran (nolock) rt on rt.id=ts.tranId
						JOIN countrymaster (NOLOCK) cr ON cr.countryId=cm.nativeCountry 
						WHERE 1 = 1'

		IF @startDate IS NOT NULL AND @endDate IS NOT NULL
			SET @sqlRegister=@sqlRegister+' and rt.ApprovedDate BETWEEN '''+@startDate+''' AND '''+@endDate+''''
		IF ISNULL(@referralCode, '') <> ''
			SET @sqlRegister=@sqlRegister+' AND cm.referelCode='''+@referralCode+''''
		
		EXEC(@sqlRegister)
				
		EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
		
		SELECT  'From Date' head, @startDate value union all
		SELECT  'To Date' head, @endDate value  UNION ALL	
		SELECT 'Referral Code' head, @referralCode
		
		SELECT 'Detail Report' title
	END
END

GO