USP_CLIENTUSERSCIPHER_MATCH_CIPHER

 CREATE procedure dbo.USP_CLIENTUSERSCIPHER_MATCH_CIPHER
      (
        @CLIENTUSERSID int,
        @CIPHER varchar(255)
      )       
      as 
      begin
        set nocount on;

          declare @OLD_CIPHER nvarchar(4000)
          declare @IsValid BIT

    if exists (select 1 from INSTALLATIONINFO where ISHOSTED = 1)
    begin  
        -- Check Admin Rights logic, this is same as Property HasAdminRights() of BBWebPrincipal.vb

        -- For Me.IsAdmin

        declare @ISADMIN bit = 0;
        declare @HASTASK bit;
        declare @COUNTTASK int = 0;
        declare @COUNTTYPETASK int = 0;

        select @ISADMIN = 1 From dbo.ClientUsers
        where Id = @CLIENTUSERSID
        and (InternalUser = 1 OR IsSupervisor = 1)

        if(@ISADMIN = 0)
        begin
            --For Me.User.Tasks.HasAnyTasks

            select @COUNTTASK = Count(TASKGUID) from [dbo].[UFN_CLIENTUSERS_TASKS](@CLIENTUSERSID, null)

            if(@COUNTTASK = 0)
            begin
                select @COUNTTYPETASK = Count(TASKGUID) from dbo.[UFN_CLIENTUSERS_CONTENTTYPE_TASKS](@CLIENTUSERSID, null)
            end

            if(@COUNTTASK > 0 or @COUNTTYPETASK > 0)
            begin
                set @HASTASK = 1;
            end
        end
    end

    if(@ISADMIN = 1 or @HASTASK = 1)
    begin
        set @ISVALID =0
    end
    else
    begin  
        select @OLD_CIPHER = CIPHER from dbo.CLIENTUSERSCIPHER where CLIENTUSERSID = @CLIENTUSERSID;

        --CASE 1: WHEN USER LOGIN AFTER CHANGES TO SALTEDPASSWORD THEN MATCH ACCORDING TO OLD PROCESS, IF SUCCESSFULLY VERIFIED THEN UPDATE IT'S CIPHER

        if (@OLD_CIPHER not like '$2a$%')
        begin
            declare @HASH_VAL as nvarchar(600)

            --Open key to get plan text value 

            exec dbo.USP_GET_KEY_ACCESS;

            select @HASH_VAL =coalesce(convert(varchar(255),DECRYPTBYKEY(CIPHER)), '') 
            from dbo.CLIENTUSERSCIPHER where CLIENTUSERSID = @CLIENTUSERSID;


            --Migrate password for this user

            exec dbo.USP_CLIENTUSERSCIPHER_INSERTORUPDATE_CIPHER @CLIENTUSERSID,@HASH_VAL

            --migrate history for user 

            declare @CHANGEAGENTID? uniqueidentifier;
??            exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;
            update CUCH set CUCH.CIPHER = dbo.[UFN_HASHBYTES_GET](coalesce(convert(varchar(255),DECRYPTBYKEY(CUCH.CIPHER)), '')) ,
                                          CUCH.CHANGEDBYID = @CHANGEAGENTID,???????????????????????                                    
???????????????????????                      CUCH.datechanged=GETDATE()
            from dbo.CLIENTUSERSCIPHERHISTORY CUCH where CUCH.CLIENTUSERSID =@CLIENTUSERSID and CUCH.CIPHER not like '$2a$%' ;
            exec dbo.USP_CLOSE_KEY_ACCESS; 

            if @HASH_VAL is not null and @HASH_VAL <> '' and @CIPHER = @HASH_VAL
            begin
                set @IsValid =1
            End 
            else
            begin
                set @IsValid =0
            end 
        end        
        else
            --CASE 2: WHEN USER LOGIN AFTER UPRGADE TO SALTED PASSWORD MATCH AS NEW PROCESS

        begin

            --Check if Chpher is null

            if(@CIPHER is null)
            begin
                --Cipher cannot be null

                set @IsValid =0;
            end
            else
            begin
                -- match hash value 

                exec @IsValid = UFN_CIPHER_MATCH @CIPHER, @OLD_CIPHER 
            end
        end     
    end
    if @IsValid =1 
    begin
      select 1 as IsValid
    end
    else 
    begin
    select 0 as IsValid
    end

    end