DB_Script/StoredProc/proc_customerRegistrationStatus.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[proc_customerRegistrationStatus]    Script Date: 12/12/2023 10:11:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[proc_customerRegistrationStatus] (
	@FLAG VARCHAR(30) = NULL
	,@searchvalue VARCHAR(300) = NULL
	,@USER VARCHAR(50) = NULL
	,@fromDate VARCHAR(20) = NULL
	,@toDate VARCHAR(20) = NULL
	,@rptFor NVARCHAR(10) = NULL
	,@searchCriteria NVARCHAR(20) = NULL
	,@customerSource VARCHAR(6) = NULL
	,@CUSTOMERID INT = NULL
	,@kycType VARCHAR(20) = NULL
	)
AS
-- EXEC [proc_customerRegistrationStatus] @searchvalue= '59253', @user='admin'
-- #11863 - add field for EKYC data in customer registration report
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN
	IF @flag = 'get-registrationStatus'
BEGIN
	DECLARE @username NVARCHAR(300)
		,@isemailVerified BIT
		,@hasDeclare BIT
		,@agreeYn BIT
		,@mobileverifiedDate VARCHAR(100)
		,@cutomerId BIGINT
		,@mobileverifiedBy VARCHAR(300)
		,@mobileapprovedBy VARCHAR(300)
		,@mobileapprovedDate VARCHAR(100)
		,@createdDate VARCHAR(20)
		,@isForcePassChange BIT
		,@isForcePinChange BIT
		,@emailVerifiedDate VARCHAR(20)
		,@ForcePassChangeDate VARCHAR(20)
		,@ForcePinChangedate VARCHAR(20)
		,@registrationType VARCHAR(10)

	IF @customerSource = 'temp'
	BEGIN
		SELECT @username = ISNULL(username, '')
			,@isemailVerified = isEmailVerified
			,@hasDeclare = ISNULL(HasDeclare, '0')
			,@agreeYn = ISNULL(agreeYn, '0')
			,@mobileverifiedDate = CONVERT(VARCHAR(20), mobileVerifiedDate, 120)
			,@mobileapprovedDate = CONVERT(VARCHAR(20), mobileApprovedDate, 120)
			,@mobileverifiedBy = mobileverifiedby
			,@mobileapprovedBy = mobileapprovedBy
		--,@createdDate =  createdDate --CONVERT(VARCHAR,createdDate,103) 
		FROM CUSTOMERMASTERTEMP(NOLOCK)
		WHERE customerId = @searchvalue

		SELECT @createdDate = convert(VARCHAR(20), createdDate, 120)
			,@isForcePassChange = isForcePassChange
			,@isForcePinChange = isForcePinChange
			,@ForcePinChangedate = convert(VARCHAR(20), forcePinChangeDate, 120)
			,@ForcePassChangeDate = convert(VARCHAR(20), ForcePassChangeDate, 120)
		FROM mobile_userRegistration(NOLOCK)
		WHERE customerId = @searchvalue

		SELECT @emailVerifiedDate = CONVERT(VARCHAR, VERIFIED_DATE, 120)
		FROM TBL_MOBILE_OTP_REQUEST(NOLOCK)
		WHERE REQUEST_FOR = 'REGISTER'
			AND USER_ID = @username
			AND IS_SUCCESS = 1
	END
	ELSE
	BEGIN
		SELECT @username = ISNULL(username, '')
			,@isemailVerified = isEmailVerified
			,@hasDeclare = ISNULL(HasDeclare, '0')
			,@agreeYn = ISNULL(agreeYn, '0')
			,@mobileverifiedDate = CONVERT(VARCHAR(20), mobileVerifiedDate, 120)
			,@mobileapprovedDate = CONVERT(VARCHAR(20), mobileApprovedDate, 120)
			,@mobileverifiedBy = mobileverifiedby
			,@mobileapprovedBy = mobileapprovedBy
			,@registrationType = RegistrationType
		FROM customerMaster(NOLOCK)
		WHERE customerId = @searchvalue

		SELECT @createdDate = convert(VARCHAR(20), createdDate, 120)
			,@isForcePassChange = isForcePassChange
			,@isForcePinChange = isForcePinChange
			,@ForcePinChangedate = convert(VARCHAR(20), forcePinChangeDate, 120)
			,@ForcePassChangeDate = convert(VARCHAR(20), ForcePassChangeDate, 120)
		FROM mobile_userRegistration(NOLOCK)
		WHERE customerId = @searchvalue

		SELECT @emailVerifiedDate = CONVERT(VARCHAR, VERIFIED_DATE, 120)
		FROM TBL_MOBILE_OTP_REQUEST(NOLOCK)
		WHERE REQUEST_FOR = 'REGISTER'
			AND USER_ID = @username
			AND IS_SUCCESS = 1
	END

	SELECT 'Username Created' AS PARTICULARS
		,CASE 
			WHEN (
					@username IS NULL
					OR @username = ''
					)
				THEN 'N'
			ELSE 'Y'
			END AS [COMPLETE]
		,CASE 
			WHEN @username IS NULL
				THEN NULL
			ELSE @createdDate
			END AS DT
	
	UNION ALL
	
	SELECT '2 Factor Authentication-Verified' AS PARTICULARS
		,CASE 
			WHEN @isemailVerified = '1'
				THEN 'Y'
			ELSE 'N'
			END AS [COMPLETE]
		,DT = @emailVerifiedDate
	
	UNION ALL
	
	SELECT 'Term & Conditions Agreed' AS PARTICULARS
		,CASE 
			WHEN @agreeYn = 1
				THEN 'Y'
			ELSE 'N'
			END
		,DT = ''
	
	UNION ALL
	
	SELECT 'Verified' AS PARTICULARS
		,CASE 
			WHEN @mobileverifiedDate IS NULL
				THEN 'N'
			ELSE 'Y'
			END
		,DT = @mobileverifiedDate + ' ( ' + @mobileverifiedBy + ' )'
	
	UNION ALL
	
	SELECT 'Approved' AS PARTICULARS
		,CASE 
			WHEN @mobileapprovedDate IS NULL
				THEN 'N'
			ELSE 'Y'
			END
		,DT = @mobileapprovedDate + ' ( ' + @mobileapprovedBy + ' )'
	
	UNION ALL
	
	SELECT 'Mandatory Password Changed' AS PARTICULARS
		,CASE 
			WHEN @isForcePassChange = 0
				THEN 'Y'
			ELSE 'N'
			END
		,DT = @ForcePassChangeDate
	
	UNION ALL
	
	SELECT 'Mandatory Pin Changed' AS PARTICULARS
		,CASE 
			WHEN @isForcePinChange = 0
				THEN 'Y'
			ELSE 'N'
			END
		,DT = @ForcePinChangedate

	IF @customerSource = 'temp'
	BEGIN
		SELECT isnull(email, '') AS email
			,isnull(sdv.detailTitle, '') AS detailTitle
			,isnull(idNumber, '') AS idNumber
			,isnull(membershipId, '') AS membershipId
				,registrationType = NULL
				,cm.approvedBy
				,@customerSource customersource
		FROM customerMasterTemp(NOLOCK) cm
		LEFT JOIN staticDataValue(NOLOCK) sdv ON sdv.valueId = cm.idtype
		WHERE cm.customerId = @searchvalue
	END
	ELSE
	BEGIN
		SELECT ISNULL(email, '') AS email
			,ISNULL(sdv.detailTitle, '') AS detailTitle
			,ISNULL(idNumber, '') AS idNumber
			,ISNULL(membershipId, '') AS membershipId
				,ISNULL(registrationType, 'MKYC') AS registrationType
				,cm.approvedBy
				,@customerSource customersource
			,mobileApprovedDate
			,createdFrom
		FROM customerMaster(NOLOCK) cm
		LEFT JOIN staticDataValue(NOLOCK) sdv ON sdv.valueId = cm.idtype
		WHERE cm.customerId = @searchvalue
	END

		IF EXISTS (
				SELECT TOP 1 1
				FROM trustDocCustomer
				WHERE customerId = @searchvalue
				)
	BEGIN
		SELECT cm.TrustDocId
			,verificationType
			,td.[state]
			,CAST(CONVERT(DATETIME, SWITCHOFFSET(td.acceptedDate, DATEPART(TZOFFSET, td.acceptedDate AT TIME ZONE 'Tokyo Standard Time'))) AS VARCHAR) accepteddate
			,CAST(CONVERT(DATETIME, SWITCHOFFSET(td.planSelectedDate, DATEPART(TZOFFSET, td.planSelectedDate AT TIME ZONE 'Tokyo Standard Time'))) AS VARCHAR) planSelectedDate
			,CAST(CONVERT(DATETIME, SWITCHOFFSET(td.documentSubmittedDate, DATEPART(TZOFFSET, td.documentSubmittedDate AT TIME ZONE 'Tokyo Standard Time'))) AS VARCHAR) documentSubmittedDate
			,cm.customerId
			,CAST(mu.ekycSubmittedDate AS VARCHAR) ekycSubmittedDate
		FROM customerMaster(NOLOCK) cm
		INNER JOIN mobile_userRegistration mu ON mu.customerId = cm.customerId
		INNER JOIN trustDocCustomer(NOLOCK) td ON td.id = cm.TrustDocId
		--	LEFT JOIN TBLCUSTOMERMODIFYLOGS (NOLOCK) tcm on tcm.customerId = cm.customerid
		WHERE cm.customerId = @searchvalue
		ORDER BY td.rowId
	END
	ELSE
		SELECT '' TrustDocId
			,'' verificationType
			,'' STATE
			,'' accepteddate
			,'' planSelectedDate
			,'' documentSubmittedDate
			,'' customerId
			,'' ekycSubmittedDate
	END
	IF @FLAG = 'update-kycType'
	BEGIN
		DECLARE @changedSecurityInfO VARCHAR(50)


		 IF @customerSource = 'temp'
		 BEGIN

		 IF EXISTS (
				SELECT TOP 1 1
				FROM CUSTOMERMASTERtemp (NOLOCK)
				WHERE CUSTOMERID = @CUSTOMERID
				)
		BEGIN
			EXEC PROC_CUSTOMERMODIFYLOG_NEW @flag = 'KYC-TYPE'
				,@user = @user
				,@customerId = @customerId
				,@registrationType = @kycType
				,@changedSecurityInfo = @changedSecurityInfo
			UPDATE CUSTOMERMASTERtemp
			SET RegistrationType = @kycType
			WHERE customerid = @customerId
			SELECT 0 ErrorCode
				,'Registration Type updated Successfully.' Msg
				,@CUSTOMERID ID
		END
		 END
		 ELSE
		 BEGIN
		IF EXISTS (
				SELECT TOP 1 1
				FROM CUSTOMERMASTER(NOLOCK)
				WHERE CUSTOMERID = @CUSTOMERID
				)
		BEGIN
			EXEC PROC_CUSTOMERMODIFYLOG_NEW @flag = 'KYC-TYPE'
				,@user = @user
				,@customerId = @customerId
				,@registrationType = @kycType
				,@changedSecurityInfo = @changedSecurityInfo
			UPDATE CUSTOMERMASTER
			SET RegistrationType = @kycType
			WHERE customerid = @customerId
			SELECT 0 ErrorCode
				,'Registration Type updated Successfully.' Msg
				,@CUSTOMERID ID
		END
		END
	END
END
	--  select * from trustDocCustomer where id = '73B05C00-5D61-4741-8F90-86ED59F80751'