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