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.
317 lines
12 KiB
317 lines
12 KiB
ALTER PROCEDURE [dbo].[Mobile_proc_changeinformation] @flag VARCHAR(20) = NULL,
|
|
@userId VARCHAR(70) =NULL, -- Request customerId 값
|
|
@password VARCHAR(100) =NULL,
|
|
@newValue VARCHAR(100) = NULL,
|
|
@target VARCHAR(10) = NULL,
|
|
@tempValue VARCHAR(20) = NULL, -- 사용자가 변경을 하고자하는 번호(임시사용) 모든 인증과정 후 최종 변경됨
|
|
@check_OTP VARCHAR(4) = NULL,
|
|
@targetInfo CHAR(1) = NULL
|
|
AS
|
|
BEGIN
|
|
DECLARE @DBpassword VARCHAR(200),
|
|
@customerId VARCHAR(200),-- 체크값용 customerId 변수
|
|
@SMS_MESSAGE VARCHAR(200),
|
|
@return_OTP VARCHAR(4),
|
|
@chkValue VARCHAR(100), -- 기존값과의 비교를 위한 변수
|
|
@RANDOM_NUMBER VARCHAR(4)
|
|
|
|
-- 입력받은 pwd와 DB pwd값 비교 / CheckValidation custeomr Password
|
|
IF @flag = 'pwd_validation'
|
|
BEGIN
|
|
DECLARE @RANDOM_OTP VARCHAR(4) = RIGHT(Checksum(Newid()), 4)
|
|
|
|
SELECT @DBpassword = dbo.Fnadecryptstring(customerpassword)
|
|
FROM customermaster(nolock)
|
|
WHERE customerid = @userId
|
|
|
|
-- pwd 체 크 먼 저
|
|
IF ( @password <> @DBpassword OR @password IS NULL )
|
|
BEGIN
|
|
EXEC Proc_errorhandler
|
|
1,
|
|
'Invaild Password Check and Try again!',
|
|
@userId
|
|
RETURN
|
|
END
|
|
IF @targetInfo = 'm'
|
|
AND @tempValue IS NOT NULL
|
|
BEGIN
|
|
SELECT @chkValue = mobile
|
|
FROM customermaster
|
|
WHERE customerid = @userId
|
|
|
|
-- 변경 값이 기존과 동일한 경우
|
|
IF ( @chkValue = @tempValue )
|
|
BEGIN
|
|
EXEC Proc_errorhandler
|
|
1,
|
|
'This is the same number as the existing number.',
|
|
@userId
|
|
|
|
RETURN
|
|
END
|
|
|
|
SET @SMS_MESSAGE = 'Your GME OTP = '
|
|
+ Cast(@RANDOM_OTP AS VARCHAR)
|
|
--SET @return_OTP = Cast(@RANDOM_OTP AS VARCHAR)
|
|
|
|
IF EXISTS(SELECT 'A'
|
|
FROM tbl_otp_for_auth (nolock)
|
|
WHERE customerid = @userId)
|
|
BEGIN
|
|
UPDATE tbl_otp_for_auth
|
|
SET otp_number = dbo.Fnaencryptstring(@RANDOM_OTP),
|
|
assigned_date = Getdate()
|
|
WHERE customerid = @userId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO tbl_otp_for_auth
|
|
(customerid,
|
|
otp_number,
|
|
assigned_date)
|
|
SELECT @userId,
|
|
dbo.Fnaencryptstring(@RANDOM_OTP),
|
|
Getdate()
|
|
END
|
|
|
|
EXEC Proc_calltosendsms
|
|
@FLAG = 'I',
|
|
@SMSBody = @SMS_MESSAGE,
|
|
@MobileNo = @tempValue
|
|
|
|
EXEC Proc_errorhandler
|
|
0,
|
|
'Check Successfully!',
|
|
@RANDOM_OTP
|
|
|
|
RETURN
|
|
END
|
|
ELSE IF @targetInfo = 'e'
|
|
AND @tempValue IS NOT NULL
|
|
BEGIN
|
|
SELECT @chkValue = customeremail
|
|
FROM customermaster
|
|
WHERE customerid = @userId
|
|
|
|
-- 변경 값이 기존과 동일한 경우
|
|
IF ( @chkValue = @tempValue )
|
|
BEGIN
|
|
EXEC Proc_errorhandler
|
|
1,
|
|
'This is the same email as the existing email.',
|
|
@userId
|
|
|
|
RETURN
|
|
END
|
|
|
|
DECLARE @Mobile VARCHAR(11) = NULL
|
|
|
|
--@email_OTP VARCHAR(4) = RIGHT(CHECKSUM(NEWID()),4)
|
|
SELECT @Mobile = mobile
|
|
FROM customermaster(nolock)
|
|
WHERE customerid = @userId
|
|
|
|
SET @SMS_MESSAGE = 'Your GME OTP = '
|
|
+ Cast(@RANDOM_OTP AS VARCHAR)
|
|
|
|
IF EXISTS(SELECT 'A'
|
|
FROM tbl_otp_for_auth (nolock)
|
|
WHERE customerid = @userId)
|
|
BEGIN
|
|
UPDATE tbl_otp_for_auth
|
|
SET otp_number = dbo.Fnaencryptstring(@RANDOM_OTP),
|
|
assigned_date = Getdate()
|
|
WHERE customerid = @userId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO tbl_otp_for_auth
|
|
(customerid,
|
|
otp_number,
|
|
assigned_date)
|
|
SELECT @userId,
|
|
dbo.Fnaencryptstring(@RANDOM_OTP),
|
|
Getdate()
|
|
END
|
|
|
|
EXEC Proc_calltosendsms
|
|
@FLAG = 'I',
|
|
@SMSBody = @SMS_MESSAGE,
|
|
@MobileNo = @Mobile
|
|
|
|
EXEC Proc_errorhandler
|
|
0,
|
|
'Check Successfully!',
|
|
@RANDOM_OTP
|
|
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
EXEC Proc_errorhandler
|
|
1,
|
|
'TargetInfo or Value Missing..',
|
|
@userId
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
ELSE IF @flag = 'change_information'
|
|
BEGIN
|
|
IF EXISTS(SELECT 'A'
|
|
FROM customermaster(nolock)
|
|
WHERE customerid = @userId
|
|
AND isactive = 'Y')
|
|
BEGIN
|
|
-- email 변경 로직 --
|
|
IF ( @target = 'e' )
|
|
BEGIN
|
|
UPDATE customermaster
|
|
SET customeremail = @newValue
|
|
WHERE customerid = @userId
|
|
|
|
-- email name customerEmail
|
|
-- SELECT customerEmail, email FROM customerMaster WHERE customerEmail IS NULL
|
|
EXEC Proc_errorhandler
|
|
0,
|
|
'Change Your Email Completed',
|
|
@userId
|
|
|
|
RETURN
|
|
END
|
|
-- Mobile No 변경 로직 --
|
|
ELSE IF( @target = 'm' )
|
|
BEGIN
|
|
UPDATE customermaster
|
|
SET mobile = @newValue
|
|
WHERE customerid = @userId
|
|
|
|
EXEC Proc_errorhandler
|
|
0,
|
|
'Change Your Mobile Number Completed',
|
|
@userId
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
|
|
IF EXISTS(SELECT 'A'
|
|
FROM customermastertemp (nolock)
|
|
WHERE customerid = @userId
|
|
AND isactive = 'Y')
|
|
BEGIN
|
|
-- email 변경 로직 --
|
|
IF ( @target = 'e' )
|
|
BEGIN
|
|
UPDATE customermastertemp
|
|
SET email = @newValue
|
|
|
|
EXEC Proc_errorhandler
|
|
0,
|
|
'Change Your Email Completed',
|
|
@userId
|
|
|
|
RETURN
|
|
END
|
|
|
|
-- Mobile No 변경 로직 --
|
|
IF( @target = 'm' )
|
|
BEGIN
|
|
UPDATE customermastertemp
|
|
SET mobile = @newValue
|
|
|
|
EXEC Proc_errorhandler
|
|
0,
|
|
'Change Your Mobile Number Completed',
|
|
@userId
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
END
|
|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
ELSE IF @flag = 're_send'
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 'A'
|
|
FROM customermaster(nolock)
|
|
WHERE customerid = @userId)
|
|
BEGIN
|
|
EXEC Proc_errorhandler
|
|
'1',
|
|
'Invalid Request! customer not found..',
|
|
@userId
|
|
|
|
RETURN
|
|
END
|
|
|
|
SET @RANDOM_NUMBER = RIGHT(Checksum(Newid()), 4)
|
|
--SELECT @MOBILE_NUMBER = mobile FROM customerMaster(NOLOCK) WHERE customerId = @CUSTOMERID
|
|
SET @SMS_MESSAGE = 'Your GME OTP = '
|
|
+ Cast(@RANDOM_NUMBER AS VARCHAR)
|
|
SET @return_OTP = Cast(@RANDOM_NUMBER AS VARCHAR)
|
|
|
|
IF EXISTS(SELECT 'A'
|
|
FROM tbl_otp_for_auth (nolock)
|
|
WHERE customerid = @userId)
|
|
BEGIN
|
|
UPDATE tbl_otp_for_auth
|
|
SET otp_number = dbo.Fnaencryptstring(@RANDOM_NUMBER),
|
|
assigned_date = Getdate()
|
|
WHERE customerid = @userId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO tbl_otp_for_auth
|
|
(customerid,
|
|
otp_number,
|
|
assigned_date)
|
|
SELECT @userId,
|
|
dbo.Fnaencryptstring(@RANDOM_NUMBER),
|
|
Getdate()
|
|
END
|
|
|
|
EXEC Proc_calltosendsms
|
|
@FLAG = 'I',
|
|
@SMSBody = @SMS_MESSAGE,
|
|
@MobileNo = @tempValue
|
|
|
|
EXEC Proc_errorhandler
|
|
'0',
|
|
'Send Successfully!',
|
|
@return_OTP
|
|
|
|
RETURN
|
|
--SELECT 'A', 'Success'
|
|
--RETURN
|
|
END
|
|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
ELSE IF @flag = 'validation_OTP'
|
|
BEGIN
|
|
IF EXISTS(SELECT 'A'
|
|
FROM customermaster
|
|
WHERE customerid = @userId)
|
|
BEGIN
|
|
EXEC Proc_validationotp
|
|
@check_OTP,
|
|
@userId
|
|
END
|
|
ELSE IF EXISTS(SELECT 'A'
|
|
FROM customermastertemp
|
|
WHERE customerid = @userId)
|
|
BEGIN
|
|
EXEC Proc_validationotp @check_OTP,
|
|
@userId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
EXEC Proc_errorhandler
|
|
'1',
|
|
'User Not Found..! Please Check Your ID',
|
|
@userId
|
|
END
|
|
|
|
RETURN
|
|
END
|
|
END
|