MOBILE_API/DB/SPs/Mobile_proc_changeinformation.sql

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