Untitled

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;
SET @t1 = GETDATE();
declare @UserId INT = 305
    declare @PageNumber INT = 1
    declare @PageSize INT = 0
    declare @SortColumn NVARCHAR(100) = 'fn'
    declare @SortOrder NVARCHAR(4) = 'a'
    declare @SearchTerm NVARCHAR(100) = ''


IF @PageSize = 0
    BEGIN
        SET @PageSize = 2147483647
    END

    DECLARE @CompanyId INT    
    SET @CompanyId = (SELECT CompanyId FROM [User] WHERE Id = @UserId)

    DECLARE @LocationIds NVARCHAR(MAX)

    SELECT @LocationIds = STRING_AGG(LocationId, ',')
    FROM dbo.udfGetUserLocations(@UserId)

    DECLARE @WorkerIds TABLE (WorkerId UNIQUEIDENTIFIER)

    INSERT INTO @WorkerIds
    SELECT CW.workerid 
    FROM CompanyWorker CW
    JOIN dbo.Split(@LocationIds, ',') S
    ON CW.LocationId = S.items

    DECLARE @MaxRScore FLOAT 
    SET @MaxRScore = (select top 1 MaxScore from Settings_WorkerScore order by LastUpdatedDate desc)

    ;with WorkerData as (
        SELECT
            w.Id, 
            cw.CompanyEmployeeId,
            w.FirstName, 
            w.MiddleName, 
            w.LastName, 
            w.Email, 
            cw.WorkerTypeId, 
            lwt.Name AS TypeText, 
            lwt.[Type],
            cw.JoiningDate,
            cw.LocationId,
            cw.IsActive,
            l.Name AS LocationName,
            w.MobilePhoneNumber AS PhoneNumber,            
            wdt.TotalCertCount AS TotalJobCount,
            wdt.ExpiredJobCount AS ExpiredJobCount,
            wdt.TotalSkillCount AS TotalSkillCount,
            wdt.ExpiredSkillCount AS ExpiredSkillCount,
            -- (
            --     select count(*) 
            --     from WorkerSkill ws 
            --     inner join skill s on ws.skillid=s.id and s.companyid=@companyid 
            --     where 
            --     ws.workerid=w.Id and ws.SkillValidityTo < CAST(dbo.udfLocationDateTime(GETUTCDATE(), l.time_zone) AS DATE)
            -- ) AS ExpiredSkillCount,
            wdt.ExpiredCertCount AS ExpiredCertCount,
            -- (
            --     select count(*) 
            --     from WorkerCert wc 
            --     inner join CompanyCert cc on wc.CompanyCertId=cc.id and cc.companyid=@companyid 
            --     where 
            --     wc.workerid=w.Id and wc.ExpiryDate < CAST(dbo.udfLocationDateTime(GETUTCDATE(), l.time_zone) AS DATE)
            -- ) AS ExpiredCertCount,
            wdt.TotalCertCount AS TotalCertCount,
            m.Id ManagerId,
            CONCAT(m.FirstName,' ',m.LastName) ManagerName,
            cw.JobTitle,
            cw.WorkCenterId,
            wc.Name AS WorkCenterName,
            cw.ShiftPatternId,
            ftsp.Name AS ShiftPatternName,
            wdt.SkillScore as SkillScore,
            COALESCE(cw.PrimaryJobId,0) AS PrimaryJobId,
            j.Name AS PrimaryJobName,
            j.HexColor AS PrimaryJobColor,
            w.TotalCompletedHours AS TotalCompletedHours,            
            w.SAR as ShiftAcceptanceRate,
            wt.Title as TierTitle,
            el.Title as ExperienceLevel,
            w.RScore as RScore,
            (w.RScore/@MaxRScore*100) as RScorePercent            
        FROM worker w
        INNER JOIN CompanyWorker cw ON w.Id = cw.workerid AND cw.CompanyId = @CompanyId
        INNER JOIN LuWorkerType lwt ON cw.WorkerTypeId = lwt.Id
        INNER JOIN Location l ON cw.LocationId = l.Id
        INNER JOIN WorkerTier wt ON w.TierSeq = wt.Id
        INNER JOIN ExpLevel el ON w.ExpLevelId = el.Id
        LEFT JOIN [User] m ON cw.ManagerUserId = m.Id
        LEFT JOIN WorkCenter wc ON cw.WorkCenterId = wc.Id
        LEFT JOIN FTShiftPatterns ftsp ON cw.ShiftPatternId = ftsp.Id
        LEFT JOIN WorkerDataTable wdt on w.Id=wdt.WorkerId and wdt.CompanyId=@CompanyId
        LEFT JOIN Job j on cw.PrimaryJobId=j.Id
        --INNER JOIN WorkerSkillScore_Vw wss on cw.WorkerId=wss.WorkerId and cw.CompanyId = wss.CompanyId
    )
        SELECT wd.*
    FROM WorkerData wd
    WHERE 
        wd.Id IN (SELECT workerid FROM @WorkerIds)
        AND wd.IsActive = 1
        AND (
            wd.FirstName LIKE '%' + @SearchTerm + '%' 
            OR wd.LastName LIKE '%' + @SearchTerm + '%'
            OR wd.CompanyEmployeeId LIKE '%' + @SearchTerm + '%'
            OR wd.TypeText LIKE '%' + @SearchTerm + '%'
            OR wd.PhoneNumber LIKE '%' + @SearchTerm + '%'            
            OR wd.ManagerName LIKE '%' + @SearchTerm + '%'
            OR wd.LocationName LIKE '%' + @SearchTerm + '%'
        )
    ORDER BY    
        CASE WHEN @SortOrder = 'a' AND @SortColumn = 'jd' THEN wd.JoiningDate END ASC,
        CASE WHEN @SortOrder = 'd' AND @SortColumn = 'jd' THEN wd.JoiningDate END DESC,
        CASE WHEN @SortOrder = 'a' AND @SortColumn = 'jc' THEN wd.TotalJobCount END ASC,
        CASE WHEN @SortOrder = 'd' AND @SortColumn = 'jc' THEN wd.TotalJobCount END DESC,
        CASE WHEN @SortOrder = 'a' AND @SortColumn = 'sc' THEN wd.TotalSkillCount END ASC,
        CASE WHEN @SortOrder = 'd' AND @SortColumn = 'sc' THEN wd.TotalSkillCount END DESC,
        CASE WHEN @SortOrder = 'a' AND @SortColumn = 'tc' THEN wd.TotalCertCount END ASC,
        CASE WHEN @SortOrder = 'd' AND @SortColumn = 'tc' THEN wd.TotalCertCount END DESC,
        CASE WHEN @SortOrder = 'a' THEN 
            CASE WHEN @SortColumn = 'fn' THEN wd.FirstName
                WHEN @SortColumn = 'ln' THEN wd.LastName
                WHEN @SortColumn = 'wi' THEN wd.CompanyEmployeeId
                WHEN @SortColumn = 'wt' THEN wd.TypeText
                WHEN @SortColumn = 'ph' THEN wd.PhoneNumber
                WHEN @SortColumn = 'mn' THEN wd.ManagerName
                WHEN @SortColumn = 'lc' THEN wd.LocationName                
            END
        END ASC,
        CASE WHEN @SortOrder = 'd' THEN 
            CASE WHEN @SortColumn = 'fn' THEN wd.FirstName
                WHEN @SortColumn = 'ln' THEN wd.LastName
                WHEN @SortColumn = 'wi' THEN wd.CompanyEmployeeId
                WHEN @SortColumn = 'wt' THEN wd.TypeText
                WHEN @SortColumn = 'ph' THEN wd.PhoneNumber
                WHEN @SortColumn = 'mn' THEN wd.ManagerName
                WHEN @SortColumn = 'lc' THEN wd.LocationName                
            END
        END DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
        
                SET @t2 = GETDATE();

        SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;