USE [temps_develop]
GO

/****** Object:  StoredProcedure [dbo].[GetPreviouslyBookedCandidates]    Script Date: 8/17/2018 03:21:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[GetPreviouslyBookedCandidates]
@OrderId int,
@DisplayLength int,
@DisplayStart int
AS
BEGIN

--DECLARE @OrderId int = 196;
--DECLARE @DisplayLength int = 1000;
--DECLARE @DisplayStart int = 0;

    DECLARE @Start int = @DisplayStart;
    DECLARE @End int = @DisplayStart + @DisplayLength;
    DECLARE @today DATETIME;
    DECLARE @totalResults bigint;

    SET @today = CONVERT(DATE, getdate(), 101);

    DECLARE @CurrentlyBookedCandidates TABLE
    (
        BookedCandidateId BIGINT,
        Fullname NVARCHAR(250),
        StartDate DATETIME,
        FinishDate DATETIME,
        CandidateId BIGINT,
        ShiftPatternId BIGINT,
        OriginalShiftPatternId BIGINT,
        LatestShiftPatternId BIGINT,
        LatestShiftPatternVersion BIGINT
    )

    DECLARE @PreviouslyBookedCandidates TABLE
    (
        BookedCandidateId BIGINT,
        Fullname NVARCHAR(250),
        Telephone NVARCHAR(250),
        StartDate DATETIME,
        FinishDate DATETIME,
        CandidateId BIGINT,
        ShiftPatternId BIGINT,
        OriginalShiftPatternId BIGINT,
        LatestShiftPatternId BIGINT,
        LatestShiftPatternVersion BIGINT,
        CanRehire BIT,
        ShouldBeDeleted BIT,
        RowNumber BIGINT
    )

    -- populate the currently booked candidates
    INSERT INTO @CurrentlyBookedCandidates
    SELECT
        obc.Id AS BookedCandidateId
    , cp.Forename + ' ' + cp.Surname AS Fullname
    , obc.StartDate
    , obc.FinishDate
    , obc.CandidateId
    , obc.ShiftPatternId
    , NULL AS OriginalShiftPattern
    , (
        SELECT CASE
                WHEN (sp.OriginalShiftPatternId IS NULL AND (sp.[Version] = 0 OR sp.[Version] = 1))
                THEN
                    CASE
                        WHEN (SELECT Count(*) FROM ShiftPatterns WHERE OriginalShiftPatternId = sp.Id) > 0
                            THEN
                            (SELECT TOP 1 Id FROM ShiftPatterns WHERE OriginalShiftPatternId = sp.Id ORDER BY CASE WHEN [Version] IS NULL THEN sp.Id ELSE sp.Id END DESC)
                        ELSE
                            obc.ShiftPatternId
                    END
                ELSE
                    (SELECT TOP 1 sp2.Id FROM ShiftPatterns sp2 WHERE sp2.OriginalShiftPatternId = sp.OriginalShiftPatternId ORDER BY CASE WHEN sp2.[Version] IS NULL THEN 1 ELSE sp2.[Version] END DESC)
            END AS IsOriginal
        FROM ShiftPatterns sp
        WHERE sp.Id = obc.ShiftPatternId
        ) AS LatestShiftPatternId
    , NULL
    FROM OrderBookedCandidates obc
    INNER JOIN CandidateProfiles cp
    ON obc.CandidateId = cp.Id
    WHERE obc.OrderId = @orderId
    AND (obc.FinishDate IS NULL OR (obc.FinishDate IS NOT NULL AND CONVERT(DATE, obc.FinishDate, 101) >= @today))

    -- populate the previously booked candidates
    INSERT INTO @PreviouslyBookedCandidates
    SELECT
        obc.Id AS BookedCandidateId
    , cp.Forename + ' ' + cp.Surname AS Fullname
    , NULL
    , obc.StartDate
    , obc.FinishDate
    , obc.CandidateId
    , obc.ShiftPatternId
    , NULL AS OriginalShiftPattern
    , (
        SELECT CASE
                WHEN (sp.OriginalShiftPatternId IS NULL AND (sp.[Version] = 0 OR sp.[Version] = 1))
                THEN
                    CASE
                        WHEN (SELECT Count(*) FROM ShiftPatterns WHERE OriginalShiftPatternId = sp.Id) > 0
                            THEN
                            (SELECT TOP 1 Id FROM ShiftPatterns WHERE OriginalShiftPatternId = sp.Id ORDER BY CASE WHEN [Version] IS NULL THEN 1 ELSE [Version] END DESC)
                        ELSE
                            obc.ShiftPatternId
                    END
                ELSE
                    (SELECT TOP 1 sp2.Id FROM ShiftPatterns sp2 WHERE sp2.OriginalShiftPatternId = sp.OriginalShiftPatternId ORDER BY CASE WHEN sp2.[Version] IS NULL THEN 1 ELSE sp2.[Version] END DESC)
            END AS IsOriginal
        FROM ShiftPatterns sp
        WHERE sp.Id = obc.ShiftPatternId
        ) AS LatestShiftPatternId
    , NULL AS LatestShiftPatternVersion
    , NULL AS CanRehire
    , NULL AS ShouldBeDeleted
    , NULL AS RowNumber
    FROM OrderBookedCandidates obc
    INNER JOIN CandidateProfiles cp
    ON obc.CandidateId = cp.Id
    WHERE obc.OrderId = @orderId
    AND (obc.FinishDate IS NOT NULL AND  CONVERT(DATE, obc.FinishDate, 101) < @today)


    --SELECT * FROM @CurrentlyBookedCandidates cbc
    --ORDER BY CandidateId ASC


    -- set the previously booked candidates latest shift pattern
    UPDATE pb
    SET pb.LatestShiftPatternVersion = (SELECT TOP 1 [Version] FROM ShiftPatterns WHERE Id = pb.LatestShiftPatternId)
    FROM @PreviouslyBookedCandidates pb

    -- set the previously booked candidates current shift pattern
    UPDATE pb
    SET pb.OriginalShiftPatternId = (SELECT TOP 1 [OriginalShiftPatternId] FROM ShiftPatterns WHERE Id = pb.LatestShiftPatternId)
    FROM @PreviouslyBookedCandidates pb

    -- set the previously booked candidates can rehire
    UPDATE pb
    SET pb.CanRehire = (
                            (
                            CASE
                                WHEN (
                                        SELECT COUNT(*)
                                        FROM @CurrentlyBookedCandidates cbc
                                        WHERE cbc.BookedCandidateId != pb.BookedCandidateId
                                        AND cbc.CandidateId = pb.CandidateId
                                        AND cbc.ShiftPatternId = pb.LatestShiftPatternId
                                        ) > 0
                                THEN 0
                                ELSE 1
                                END
                            )
    )
    FROM @PreviouslyBookedCandidates pb

    -- set the previously booked candidates can rehire
    UPDATE pb
    SET pb.ShouldBeDeleted = (
                            (
                            CASE
                                WHEN (
                                        SELECT COUNT(*)
                                        FROM @CurrentlyBookedCandidates cbc
                                        WHERE cbc.CandidateId = pb.CandidateId
                                        AND cbc.LatestShiftPatternId = pb.LatestShiftPatternId
                                        ) > 0
                                THEN 1
                                ELSE 0
                                END
                            )
    )
    FROM @PreviouslyBookedCandidates pb

    UPDATE pb
    SET pb.Telephone = (SELECT PhoneMobile FROM CandidateProfiles WHERE Id = pb.CandidateId)
    FROM @PreviouslyBookedCandidates pb

    --UPDATE pb
    --SET pb.RowNumber = ROW_NUMBER() OVER (ORDER BY BookedCandidateId )
    --FROM @PreviouslyBookedCandidates pb

    DELETE FROM @PreviouslyBookedCandidates
    WHERE ShouldBeDeleted = 1


    UPDATE T
    SET T.rownumber = TT.ROW_ID
    FROM @PreviouslyBookedCandidates AS T
    INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY BookedCandidateId) AS ROW_ID
                    , BookedCandidateId
            FROM @PreviouslyBookedCandidates) AS TT
    ON T.BookedCandidateId = TT.BookedCandidateId

    SET @totalResults = (SELECT COUNT(*) FROM @PreviouslyBookedCandidates)

    SELECT BookedCandidateId, Fullname, Telephone,  StartDate, FinishDate, CanRehire, CandidateId, (SELECT @totalResults) AS TotalResults
    FROM  @PreviouslyBookedCandidates pb
    WHERE pb.RowNumber > @Start and pb.RowNumber <= @End
    ORDER BY pb.RowNumber

END
GO