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.
499 lines
28 KiB
499 lines
28 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_change_customer_email] Script Date: 11/21/2023 11:03:35 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER PROCEDURE [dbo].[proc_change_customer_email] @flag VARCHAR(50) = NULL
|
|
,@newEmail VARCHAR(50) = NULL
|
|
,@oldEmail VARCHAR(50) = NULL
|
|
,@user VARCHAR(30) = NULL
|
|
,@customerId VARCHAR(30) = NULL
|
|
,@searchCriteria VARCHAR(30) = NULL
|
|
,@searchValue VARCHAR(50) = NULL
|
|
,@fromDate DATETIME = NULL
|
|
,@toDate DATETIME = NULL
|
|
,@agentId BIGINT = NULL
|
|
,@obpId VARCHAR(50) = NULL
|
|
--grid parameters
|
|
,@pageSize VARCHAR(50) = NULL
|
|
,@pageNumber VARCHAR(50) = NULL
|
|
,@sortBy VARCHAR(50) = NULL
|
|
,@sortOrder VARCHAR(50) = NULL
|
|
,@modifiedBy VARCHAR(50) = NULL
|
|
,@modifiedDate DATETIME = NULL
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @table VARCHAR(MAX)
|
|
,@select_field_list VARCHAR(MAX)
|
|
,@extra_field_list VARCHAR(MAX)
|
|
,@sql_filter VARCHAR(MAX)
|
|
DECLARE @email VARCHAR(200)
|
|
|
|
SET @toDate = @toDate + ' 23:59:59'
|
|
|
|
IF @flag = 'searchByEmail'
|
|
BEGIN
|
|
SELECT '' value
|
|
,'Select' [text]
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'email'
|
|
,'Email ID'
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'IdNumber'
|
|
,'ID - Number'
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'mobile'
|
|
,'Mobile No'
|
|
|
|
RETURN
|
|
END
|
|
|
|
--IF @flag = 'updateEmailold' --update new email address
|
|
--BEGIN
|
|
-- DECLARE @userName VARCHAR(50)
|
|
-- SELECT @userName = username
|
|
-- FROM CUSTOMERMASTER
|
|
-- WHERE customerId = @customerId --email=@oldEmail
|
|
-- --to check when username is null and new email != old email
|
|
-- IF @userName IS NULL
|
|
-- OR @userName <> @oldEmail
|
|
-- BEGIN
|
|
-- IF EXISTS (
|
|
-- SELECT 'X'
|
|
-- FROM dbo.customerMaster cm(NOLOCK)
|
|
-- left join mobile_userRegistration mr ON cm.email = mr.username
|
|
-- WHERE (cm.email = @newEmail or mr.username = @newEmail) AND cm.customerId <> @customerId
|
|
-- --AND approvedBy IS NOT NULL
|
|
-- )
|
|
-- BEGIN
|
|
-- SELECT '1' ErrorCode
|
|
-- ,'Customer with same email already exists.' Msg
|
|
-- ,NULL
|
|
-- RETURN
|
|
-- END
|
|
-- ELSE
|
|
-- BEGIN
|
|
-- EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i-update'
|
|
-- ,@email = @newEmail
|
|
-- ,@customerId = @customerId
|
|
-- ,@user = @user
|
|
-- ,@username = @newEmail
|
|
-- UPDATE customerMaster
|
|
-- SET username = @newEmail
|
|
-- ,email = @newEmail
|
|
-- WHERE customerId = @customerId
|
|
-- SELECT '0' ErrorCode
|
|
-- ,'Customer username updated.' Msg
|
|
-- ,@email id
|
|
-- ,'Y' extra
|
|
-- RETURN
|
|
-- END
|
|
-- END
|
|
-- ELSE IF EXISTS (
|
|
-- SELECT 'X'
|
|
-- FROM dbo.mobile_userRegistration(NOLOCK)
|
|
-- WHERE username = @newEmail
|
|
-- )
|
|
-- BEGIN
|
|
-- SELECT '1' ErrorCode
|
|
-- ,'Customer with same username already exists.' Msg
|
|
-- ,NULL
|
|
-- RETURN
|
|
-- END
|
|
-- ELSE
|
|
-- BEGIN
|
|
-- EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i-update'
|
|
-- ,@email = @newEmail
|
|
-- ,@customerId = @customerId
|
|
-- ,@user = @user
|
|
-- ,@username = @newEmail
|
|
-- UPDATE dbo.mobile_userRegistration
|
|
-- SET
|
|
-- username = @newEmail
|
|
-- ,modifiedBy = @user
|
|
-- ,modifiedDate = GETDATE()
|
|
-- WHERE customerId = @customerId
|
|
-- SELECT '0' ErrorCode
|
|
-- ,'Username updated successfully.' Msg
|
|
-- ,@email id
|
|
-- ,'Y' extra
|
|
-- --,'verified' EXTRA2
|
|
-- RETURN
|
|
-- END
|
|
--END
|
|
-- ELSE IF EXISTS (
|
|
-- SELECT 'X'
|
|
-- FROM dbo.customerMaster(NOLOCK) cm
|
|
-- INNER JOIN dbo.mobile_userRegistration(NOLOCK) mr ON cm.email = mr.username
|
|
-- WHERE cm.email <> @newEmail
|
|
-- )
|
|
-- BEGIN
|
|
-- EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i-update'
|
|
-- ,@email = @newEmail
|
|
-- ,@customerId = @customerId
|
|
-- ,@user = @user
|
|
-- ,@userName = @newEmail
|
|
-- UPDATE customerMaster
|
|
-- SET email = @newEmail
|
|
-- ,username = @newEmail
|
|
-- ,modifiedBy = @user
|
|
-- ,modifiedDate = GETDATE()
|
|
-- ,approvedDate = NULL
|
|
-- ,approvedBy = NULL
|
|
-- ,customerEmail = @email
|
|
-- WHERE customerId = @customerId
|
|
-- UPDATE mobile_userRegistration
|
|
-- SET username = @newEmail
|
|
-- ,modifiedBy = @user
|
|
-- ,modifiedDate = GETDATE()
|
|
-- WHERE customerId = @customerId
|
|
-- UPDATE TBL_MOBILE_OTP_REQUEST
|
|
-- SET is_expired = '1'
|
|
-- WHERE USER_ID = @oldEmail
|
|
-- AND REQUEST_FOR = 'REGISTER'
|
|
-- SELECT '0' ErrorCode
|
|
-- ,'Username updated successfully.' Msg
|
|
-- ,@email id
|
|
-- ,'Y' extra
|
|
-- RETURN
|
|
-- END
|
|
--END
|
|
IF @flag = 'updateEmail'
|
|
BEGIN
|
|
DECLARE @userName VARCHAR(50)
|
|
|
|
SELECT @userName = username
|
|
FROM CUSTOMERMASTER
|
|
WHERE customerId = @customerId
|
|
|
|
--IF @userName IS NOT NULL
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM dbo.customerMaster cm(NOLOCK)
|
|
LEFT JOIN mobile_userRegistration mr ON cm.email = mr.username
|
|
--WHERE (email = @newEmail OR mr.username = @newEmail) AND cm.customerId <> @customerId
|
|
WHERE email = @newEmail
|
|
AND cm.customerId <> @customerId
|
|
)
|
|
BEGIN
|
|
SELECT '1' ErrorCode
|
|
,'Customer with same username already exists.' Msg
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
ELSE IF EXISTS (
|
|
SELECT 'X'
|
|
FROM dbo.customerMaster cm(NOLOCK)
|
|
LEFT JOIN mobile_userRegistration mr ON cm.email = mr.username
|
|
--WHERE (email = @newEmail OR mr.username = @newEmail) AND cm.customerId = @customerId
|
|
WHERE email = @newEmail
|
|
AND cm.customerId = @customerId
|
|
)
|
|
BEGIN
|
|
EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i-update'
|
|
,@email = @newEmail
|
|
,@customerId = @customerId
|
|
,@user = @user
|
|
,@username = @newEmail
|
|
|
|
UPDATE customerMaster
|
|
SET username = @newEmail
|
|
,email = @newEmail
|
|
,isEmailVerified = 0
|
|
,modifiedBy = @user
|
|
,modifiedDate = GETDATE()
|
|
WHERE customerId = @customerId
|
|
|
|
UPDATE dbo.mobile_userRegistration
|
|
SET username = @newEmail
|
|
,modifiedBy = @user
|
|
,modifiedDate = GETDATE()
|
|
WHERE customerId = @customerId
|
|
|
|
UPDATE TBL_MOBILE_OTP_REQUEST
|
|
SET is_expired = '1'
|
|
WHERE USER_ID = @oldEmail
|
|
AND REQUEST_FOR = 'REGISTER'
|
|
|
|
SELECT '0' ErrorCode
|
|
,'Customer username updated.' Msg
|
|
,@email id
|
|
,'Y' extra
|
|
|
|
RETURN
|
|
END
|
|
ELSE IF EXISTS (
|
|
SELECT 'X'
|
|
FROM dbo.customerMaster cm(NOLOCK)
|
|
LEFT JOIN mobile_userRegistration mr ON cm.email = mr.username
|
|
WHERE email <> @newEmail --AND cm.customerId = @customerId
|
|
)
|
|
BEGIN
|
|
EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i-update'
|
|
,@email = @newEmail
|
|
,@customerId = @customerId
|
|
,@user = @user
|
|
,@username = @newEmail
|
|
|
|
UPDATE customerMaster
|
|
SET username = @newEmail
|
|
,email = @newEmail
|
|
,isEmailVerified = 0
|
|
,modifiedBy = @user
|
|
,modifiedDate = GETDATE()
|
|
WHERE customerId = @customerId
|
|
|
|
UPDATE dbo.mobile_userRegistration
|
|
SET username = @newEmail
|
|
,modifiedBy = @user
|
|
,modifiedDate = GETDATE()
|
|
WHERE customerId = @customerId
|
|
|
|
UPDATE TBL_MOBILE_OTP_REQUEST
|
|
SET is_expired = '1'
|
|
WHERE USER_ID = @oldEmail
|
|
AND REQUEST_FOR = 'REGISTER'
|
|
|
|
SELECT '0' ErrorCode
|
|
,'Customer username updated.' Msg
|
|
,@email id
|
|
,'Y' extra
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
END
|
|
|
|
--ELSE IF @userName IS NULL
|
|
--BEGIN
|
|
-- IF EXISTS (
|
|
-- SELECT 'X'
|
|
-- FROM dbo.customerMaster cm(NOLOCK)
|
|
-- left join mobile_userRegistration mr ON cm.email = mr.username
|
|
-- WHERE (email = @newEmail OR mr.username = @newEmail) AND cm.customerId <> @customerId
|
|
-- )
|
|
-- BEGIN
|
|
-- SELECT '1' ErrorCode
|
|
-- ,'Customer with same username already exists.' Msg
|
|
-- ,NULL
|
|
-- RETURN
|
|
-- END
|
|
-- ELSE IF EXISTS (
|
|
-- SELECT 'X'
|
|
-- FROM dbo.customerMaster cm(NOLOCK)
|
|
-- left join mobile_userRegistration mr ON cm.email = mr.username
|
|
-- WHERE (email = @newEmail OR mr.username = @newEmail) AND cm.customerId = @customerId
|
|
-- )
|
|
-- BEGIN
|
|
-- EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i-update'
|
|
-- ,@email = @newEmail
|
|
-- ,@customerId = @customerId
|
|
-- ,@user = @user
|
|
-- ,@username = @newEmail
|
|
-- UPDATE customerMaster
|
|
-- SET username = @newEmail
|
|
-- ,email = @newEmail
|
|
-- ,isEmailVerified ='0'
|
|
-- WHERE customerId = @customerId
|
|
-- UPDATE dbo.mobile_userRegistration
|
|
-- SET
|
|
-- username = @newEmail
|
|
-- ,modifiedBy = @user
|
|
-- ,modifiedDate = GETDATE()
|
|
-- WHERE customerId = @customerId
|
|
-- UPDATE TBL_MOBILE_OTP_REQUEST
|
|
-- SET is_expired = '1'
|
|
-- WHERE USER_ID = @oldEmail
|
|
-- AND REQUEST_FOR = 'REGISTER'
|
|
-- SELECT '0' ErrorCode
|
|
-- ,'Customer username updated.' Msg
|
|
-- ,@email id
|
|
-- ,'Y' extra
|
|
-- RETURN
|
|
-- END
|
|
--END
|
|
/*old*/
|
|
--BEGIN
|
|
-- IF EXISTS (
|
|
-- SELECT 'X'
|
|
-- FROM dbo.customerMaster cm(NOLOCK)
|
|
-- left join mobile_userRegistration mr ON cm.email = mr.username
|
|
-- WHERE email = @newEmail OR mr.username = @newEmail) AND cm.customerId = @customerId
|
|
-- --AND approvedBy IS NOT NULL
|
|
-- )
|
|
-- BEGIN
|
|
-- EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i-update'
|
|
-- ,@email = @newEmail
|
|
-- ,@customerId = @customerId
|
|
-- ,@user = @user
|
|
-- ,@username = @newEmail
|
|
-- UPDATE customerMaster
|
|
-- SET username = @newEmail
|
|
-- ,email = @newEmail
|
|
-- ,isEmailVerified ='0'
|
|
-- WHERE customerId = @customerId
|
|
-- UPDATE dbo.mobile_userRegistration
|
|
-- SET
|
|
-- username = @newEmail
|
|
-- ,modifiedBy = @user
|
|
-- ,modifiedDate = GETDATE()
|
|
-- WHERE customerId = @customerId
|
|
-- UPDATE TBL_MOBILE_OTP_REQUEST
|
|
-- SET is_expired = '1'
|
|
-- WHERE USER_ID = @oldEmail
|
|
-- AND REQUEST_FOR = 'REGISTER'
|
|
-- SELECT '0' ErrorCode
|
|
-- ,'Customer username updated.' Msg
|
|
-- ,@email id
|
|
-- ,'Y' extra
|
|
-- END
|
|
--END
|
|
-- ELSE IF EXISTS (
|
|
-- SELECT 'X'
|
|
-- FROM dbo.customerMaster cm(NOLOCK)
|
|
-- left join mobile_userRegistration mr ON cm.email = mr.username
|
|
-- WHERE (cm.email = @newEmail or mr.username = @newEmail) AND cm.customerId <> @customerId
|
|
-- --AND approvedBy IS NOT NULL
|
|
-- )
|
|
-- BEGIN
|
|
-- SELECT '1' ErrorCode
|
|
-- ,'Customer with same email already exists.' Msg
|
|
-- ,NULL
|
|
-- RETURN
|
|
-- END
|
|
-- ELSE
|
|
-- BEGIN
|
|
-- EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i-update'
|
|
-- ,@email = @newEmail
|
|
-- ,@customerId = @customerId
|
|
-- ,@user = @user
|
|
-- ,@username = @newEmail
|
|
-- UPDATE customerMaster
|
|
-- SET username = @newEmail
|
|
-- ,email = @newEmail
|
|
-- ,isEmailVerified ='0'
|
|
-- --, approvedBy = null
|
|
-- --, approvedDate = null
|
|
-- --, mobileApprovedBy = null
|
|
-- --, mobileApprovedDate = null
|
|
-- --, mobileverifiedby = null
|
|
-- --, mobileVerificationtype = null
|
|
-- --,modifiedBy = @user
|
|
-- -- ,modifiedDate = GETDATE()
|
|
-- WHERE customerId = @customerId
|
|
-- IF EXISTS (
|
|
-- SELECT 'X'
|
|
-- FROM dbo.mobile_userRegistration
|
|
-- WHERE customerId = @customerId --AND username = @oldemail
|
|
-- )
|
|
-- BEGIN
|
|
-- UPDATE dbo.mobile_userRegistration
|
|
-- SET
|
|
-- username = @newEmail
|
|
-- ,modifiedBy = @user
|
|
-- ,modifiedDate = GETDATE()
|
|
-- WHERE customerId = @customerId
|
|
-- UPDATE TBL_MOBILE_OTP_REQUEST
|
|
-- SET is_expired = '1'
|
|
-- WHERE USER_ID = @oldEmail
|
|
-- AND REQUEST_FOR = 'REGISTER'
|
|
-- END
|
|
-- SELECT '0' ErrorCode
|
|
-- ,'Customer username updated.' Msg
|
|
-- ,@email id
|
|
-- ,'Y' extra
|
|
-- RETURN
|
|
-- END
|
|
IF @flag = 'pending-cust-from-mobile'
|
|
BEGIN
|
|
DECLARE @searchEmail VARCHAR(50)
|
|
|
|
SELECT @searchEmail = email
|
|
FROM dbo.customerMaster(NOLOCK)
|
|
WHERE customerId = @customerId
|
|
|
|
SET @sortBy = 'createdDate'
|
|
SET @sortOrder = 'desc'
|
|
SET @table =
|
|
'(
|
|
SELECT customerId=cm.customerId
|
|
,CM.membershipid
|
|
,cm.userName
|
|
,email=cm.email
|
|
,fullName= REPLACE(ISNULL(cm.firstName, '''') + ISNULL('' '' + cm.middleName, '''') + ISNULL('' '' + cm.lastName1, ''''), '' '', '' '')
|
|
,dob=CONVERT(VARCHAR,cm.dob,101)
|
|
,address=cm.[address]
|
|
,nativeCountry=com.countryName
|
|
,idtype= ''''--case when cm.idtype=''11402'' then ISNULL(sdv.detailTitle, '''') + (''-'') + ISNULL('' '' + cm.otherIdNumber, '''') else sdv.detailTitle end
|
|
,idNumber=cm.idNumber
|
|
,createdDate=CAST(cm.createdDate AS DATE)
|
|
,createdBy=cm.createdBy
|
|
,verifiedBy=cm.verifiedBy
|
|
,verifiedDate=CAST(cm.verifiedDate AS DATE)
|
|
,mobile=cm.mobile
|
|
,cm.agentId
|
|
,case when isnull(cm.isExistingCustomer,0) = 1 then ''Yes'' ELSE ''No'' End isExistingCustomer
|
|
,createdUserFrom = ''mobile''
|
|
,mobileApprovedBy = cm.mobileApprovedBy
|
|
,mobileApprovedDate = cm.mobileApprovedDate
|
|
,mobileverifiedby = cm.mobileverifiedby
|
|
,mobileverifieddate = cm.mobileverifieddate
|
|
,OTP_Verified=case when isnull(x.IS_SUCCESS,0) = 1 then ''Yes'' ELSE ''No'' End
|
|
FROM customerMaster cm(NOLOCK)
|
|
LEFT JOIN countryMaster com(NOLOCK) ON cm.nativeCountry=com.countryId
|
|
-- INNER JOIN staticDataValue sdv (NOLOCK) ON sdv.valueId=cm.idType AND sdv.typeID=1300
|
|
--LEFT JOIN TBL_MOBILE_OTP_REQUEST otp(NOLOCK) ON cm.email=otp.USER_ID
|
|
LEFT JOIN (select top 1 USER_ID , IS_SUCCESS from TBL_MOBILE_OTP_REQUEST where REQUEST_FOR=''REGISTER'' and USER_ID='''
|
|
+ @searchEmail + ''')x on x.user_id = cm.email
|
|
--AND CM.ISACTIVE = ''Y''
|
|
WHERE customerId = ''' + @customerId + '''
|
|
--AND USERNAME IS NOT NULL
|
|
and cm.membershipId is not null
|
|
)X'
|
|
--IF ISNULL(@fromDate, '') <> ''
|
|
-- AND ISNULL(@toDate, '') <> ''
|
|
-- SET @table = @table + ' AND createdDate BETWEEN ''' + CAST(@fromDate AS VARCHAR) + ''' AND ''' + CAST(@toDate AS VARCHAR) + ''''
|
|
--SET @table = @table + ')x'
|
|
--SET @sql_filter = ''
|
|
--IF ISNULL(@searchCriteria, '') <> ''
|
|
-- AND ISNULL(@searchValue, '') <> ''
|
|
--BEGIN
|
|
-- IF @searchCriteria = 'emailId'
|
|
-- BEGIN
|
|
-- SET @sql_Filter = @sql_Filter + ' AND email like ''' + @searchValue + '%'''
|
|
-- END
|
|
--END
|
|
--PRINT @table;
|
|
SET @select_field_list = '
|
|
customerId,userName,isExistingCustomer,membershipid,email,fullName,dob,address,nativeCountry,idtype,idNumber
|
|
,createdDate,createdBy,verifiedBy,verifiedDate,createdUserFrom,mobileApprovedBy,mobileApprovedDate,mobileverifiedby,mobileverifieddate,OTP_Verified
|
|
'
|
|
|
|
EXEC dbo.proc_paging @table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber
|
|
|
|
--,@action
|
|
RETURN
|
|
END
|
|
END
|