You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
85 lines
6.9 KiB
85 lines
6.9 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_IncompleteKYCReport] Script Date: 8/26/2024 12:50:29 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER PROC [dbo].[proc_IncompleteKYCReport]
|
|
(
|
|
@searchValue VARCHAR(300) = null
|
|
,@USER VARCHAR(50)
|
|
,@fromDate VARCHAR(20) = NULL
|
|
,@toDate VARCHAR(20) = NULL
|
|
,@rptFor NVARCHAR(10) = NULL
|
|
,@searchCriteria NVARCHAR(20) = NULL
|
|
)
|
|
AS
|
|
-------------------------------------------------------------------
|
|
-- #34182 - Changes for registration tracker
|
|
-------------------------------------------------------------------
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
BEGIN
|
|
SELECT ROW_NUMBER() OVER (
|
|
ORDER BY customerId
|
|
) SN, * FROM (
|
|
SELECT ct.customerId, idNumber AS [ID Number], membershipId as 'Membership ID', mobile AS [Mobile Number], email AS [Email ID],
|
|
[OTP Verified] = CASE WHEN VERIFIED_DATE is null then 'N' ELSE 'Y' END,
|
|
[OTP Verified Date] = CONVERT(VARCHAR(20), VERIFIED_DATE, 121),
|
|
[KYC Verified] = CASE WHEN hasDeclare = 1 THEN 'Y' ELSE 'N' END,
|
|
[KYC Verified Date] = mobileverifieddate,
|
|
[Registration Type] = '',
|
|
[Created Date] = ct.createdDate ,
|
|
'<button onclick="onclick(''' + CAST(ct.customerId AS VARCHAR) + ''')">Move</button>' AS [Action]
|
|
FROM CustomerMasterTemp(NOLOCK) ct
|
|
INNER JOIN mobile_userRegistration(NOLOCK) mr ON mr.customerId = ct.customerId
|
|
LEFT JOIN TBL_MOBILE_OTP_REQUEST(NOLOCK) otp ON otp.USER_ID = mr.username
|
|
WHERE 1=1
|
|
AND ct.customerId = isnull(@searchValue,ct.customerId)
|
|
AND cast(ct.createdDate as date) BETWEEN @fromDate AND @toDate + ' 23:59:59'
|
|
|
|
UNION ALL
|
|
|
|
|
|
SELECT cm.customerid , idNumber AS [ID Number], membershipId as 'Membership ID', mobile AS [Mobile Number], email AS [Email ID],
|
|
[OTP Verified] = CASE WHEN VERIFIED_DATE is null then 'N' ELSE 'Y' END,
|
|
[OTP Verified Date] = CONVERT(VARCHAR(20), VERIFIED_DATE, 121),
|
|
[KYC Verified] = CASE WHEN hasDeclare = 1 THEN 'Y' ELSE 'N' END,
|
|
[KYC Verified Date] = mobileverifieddate,
|
|
[Registration Type] = CASE WHEN cm.registrationtype IS NULL THEN 'MANUAL' WHEN cm.registrationtype='MKYC' THEN 'MANUAL' ELSE 'EKYC' END,
|
|
[Created Date] = cm.createdDate,
|
|
'<button onclick="onclick(''' + CAST(cm.customerid AS VARCHAR) + ''')">Move</button>' AS [Action]
|
|
FROM CustomerMaster(NOLOCK) cm
|
|
INNER JOIN mobile_userRegistration(NOLOCK) mr ON mr.customerId = cm.customerId
|
|
LEFT JOIN TBL_MOBILE_OTP_REQUEST(NOLOCK) otp ON otp.USER_ID = mr.username
|
|
WHERE ISNULL(HasDeclare,0) = 0
|
|
AND ISNULL(agreeYn,0) = 0
|
|
AND isnull(cm.customerId,'') = isnull(@searchValue,cm.customerid)
|
|
AND cast(cm.createdDate as date) BETWEEN @fromDate AND @toDate + ' 23:59:59'
|
|
|
|
UNION ALL
|
|
|
|
SELECT tm.tempCustId , '' AS [ID Number], '' as 'Membership ID', mobile AS [Mobile Number], email AS [Email ID],
|
|
tm.isOtpVerified AS [isOtpVerified],
|
|
'' [OTP Verified Date],
|
|
'' AS [KYC Verified],
|
|
'' AS [KYC Verified Date],
|
|
'' AS [Registration Type],
|
|
[Created Date] = tm.createdDate,
|
|
'' AS [Action]
|
|
FROM TempUserRegister(NOLOCK) tm
|
|
WHERE tm.isActive = 'Y' AND
|
|
cast(tm.createdDate as date) BETWEEN @fromDate AND @toDate + ' 23:59:59'
|
|
)x ORDER BY [Created Date] desc
|
|
|
|
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
|
|
SELECT 'From Date' head, @fromDate VALUE
|
|
UNION ALL
|
|
SELECT 'To Date' head, @toDate VALUE
|
|
|
|
SELECT 'Incomplete KYC Report' title
|
|
END
|