IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PV_IRWebGetOnSiteManagerEquipmentForJob]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].PV_IRWebGetOnSiteManagerEquipmentForJob
GO
PRINT 'Creating procedure PV_IRWebGetOnSiteManagerEquipmentForJob';
GO
CREATE PROCEDURE  [dbo].PV_IRWebGetOnSiteManagerEquipmentForJob
    @ClientID BIGINT,
    @JobID BIGINT,
    @SortField VARCHAR(100) = 'ManufacturerModel',
    @SortDirection VARCHAR(5) = 'ASC',
    @PageSize INT = 10,
    @PageIndex BIGINT = 1,
    @RecordCount BIGINT OUTPUT
AS
BEGIN
    CREATE TABLE #Results
    (
          ID BIGINT
        , EquipmentId BIGINT
        , ActualEquipmentId BIGINT
        , ManufacturerModel NVARCHAR(400)
        , Manufacturer NVARCHAR(200)
        , Model NVARCHAR(200)
        , Barcode NVARCHAR(200)
        , Serial NVARCHAR(200)
        , ModelId BIGINT
        , Requested INT
        , Needed INT
        , Assigned INT
        , Unhandled INT
        , OnStock INT
        , [Status] TINYINT
        , IsDeleted BIT
        , IsAsset BIT
        , RowId INT
    )

    IF @SortField = ''
    BEGIN
        SET @SortField = 'ManufacturerModel'
    END
    IF @SortDirection = ''
    BEGIN
        SET @SortDirection = 'ASC'
    END

    -- get main rows (model)
    INSERT INTO #Results
    SELECT ID, EquipmentId, NULL as ActualEquipmentId, ManufacturerModel, Manufacturer, Model, Barcode, Serial, ModelId, Requested, Needed, Assigned , Unhandled, OnStock, [Status], IsDeleted, IsAsset,
            (ROW_NUMBER() OVER (ORDER BY
                CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'ASC' THEN ManufacturerModel END ASC,
                CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'DESC' THEN ManufacturerModel END DESC
            )) as RowId
    FROM
    (
        SELECT jte.ID
            , jte.IREquipmentD as EquipmentId
            , m.Manufacturer + N' ' + m.Model as ManufacturerModel
            , m.Manufacturer
            , m.Model
            , '' as Barcode
            , '' as Serial
            , jte.IRModelID as ModelId
            , jte.InProcessCount as Requested
            , jte.NeededCount as Needed
            , 0 as Assigned
            , jte.UnhandledCount as Unhandled
            , jte.OnStock as OnStock
            , jte.[Status] as [Status]
            , jte.IsDeleted
            , 0 as IsAsset
        FROM IRJobTechnician as jt INNER JOIN
            IRJob as j ON jt.IRJobID = j.ID INNER JOIN
            IRJobTechnicianEquipment as jte ON jte.IRJobTechnicianID = jt.ID
            OUTER APPLY dbo.fn_GetModelsAndModelClient(jte.IRModelID, NULL, @ClientID, 0, NULL, NULL) as m
        WHERE jt.IsOnSiteManager = 1
            AND jt.IRJobID = @JobID
            AND jt.IsDeleted = 0
            AND jte.IsDeleted = 0
    ) r

    DECLARE @TechnicianEquipments TABLE
    (
        EquipmentID BIGINT
    )
    --INSERT INTO @TechnicianEquipments
    --SELECT e.ID
    --FROM IREquipment as e
    --  INNER JOIN IRJobTechnician as jt ON e.IRUserIDTechnician = jt.IRUserID
    --WHERE (e.IRJobID IS NULL
    --      OR e.IRJobID <> @JobID)
    --  AND jt.IRJobID = @JobID
    --  AND jt.IsDeleted = 0
    --  AND jt.IsOnSiteManager = 0
    --  AND e.IsOnStock = 1
    --  AND e.IsDropped = 0
    --  AND e.[Status] = 1 /* Normal */
    --  --AND dbo.fn_GetEquipmentStatus(e.ID) <> 2

    INSERT INTO @TechnicianEquipments
    SELECT e.ID
    FROM IREquipment as e
        INNER JOIN IRJobTechnician as jt ON e.IRUserIDTechnician = jt.IRUserID
    WHERE (e.IRJobID IS NULL
            OR e.IRJobID <> @JobID)
        AND jt.IRJobID = @JobID
        AND jt.IsDeleted = 0
        AND jt.IsOnSiteManager = 0
        AND e.IsOnStock = 1
        AND e.IsDropped = 0
        AND e.[Status] = 1 /* Normal */
         --jest przypisany do innego joba, gdzie project leadem jest jeden z techników na tym jobie
         -- takich equipmentów nie wyświetlamy.
         --Muszą być bezpośrednio przypisane albo do technika albo do tego konkretnego joba
         -- to jak rozumiem i zwalcza ten przypadek:
         -- equipment jest przypisany do innego joba, użtykownik nie jest przypisany do joba jako technik
        AND jt.IRUserID NOT IN (SELECT jt1.IRUserID /*not add equipment have project lead*/
                                FROM IRJobTechnician as jt1
                                WHERE jt1.IRJobID = @JobID
                                    AND jt1.IsDeleted = 0
                                    AND jt1.IsOnSiteManager = 1)
        --AND dbo.fn_GetEquipmentStatus(e.ID) <> 2

    -- get subrows (equipment)
    INSERT INTO #Results
    SELECT ID, EquipmentId, EquipmentId as ActualEquipmentId, ManufacturerModel, Manufacturer, Model, Barcode, Serial, ModelId, Requested, Needed, Assigned , Unhandled, OnStock, [Status], IsDeleted, IsAsset,
            (ROW_NUMBER() OVER (ORDER BY
                CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'ASC' THEN ManufacturerModel END ASC,
                CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'DESC' THEN ManufacturerModel END DESC
            )) as RowId
    FROM
    (
        SELECT rr.ID as ID
            , e.ID as EquipmentId
            , m.Manufacturer + N' ' + m.Model + N' ' + e.BarCode + N' ' + e.Serial as ManufacturerModel
            , m.Manufacturer as Manufacturer
            , m.Model as Model
            , e.BarCode as Barcode
            , e.Serial as Serial
            , e.IRModelID as ModelId
            , 0 as Requested
            , 0 as Needed
            , 0 as Assigned
            , 0 as Unhandled
            , 0 as OnStock
            , 0 as [Status]
            , 0 as IsDeleted
            , 1 as IsAsset
            , dbo.fn_GetEquipmentStatus(e.ID) as EquipmentStatus
        FROM IREquipment as e
            LEFT JOIN #Results as rr ON rr.ModelId = e.IRModelID
            OUTER APPLY dbo.fn_GetModelsAndModelClient(e.IRModelID, NULL, @ClientID, 0, NULL, NULL) as m
        WHERE e.IRJobID = @JobID
            AND (rr.ID IS NULL OR rr.IsDeleted = 0)

        UNION ALL

        SELECT rr.ID as ID
            , e.ID as EquipmentId
            , m.Manufacturer + N' ' + m.Model + N' ' + e.BarCode + N' ' + e.Serial as ManufacturerModel
            , m.Manufacturer as Manufacturer
            , m.Model as Model
            , e.BarCode as Barcode
            , e.Serial as Serial
            , e.IRModelID as ModelId
            , 0 as Requested
            , 0 as Needed
            , 0 as Assigned
            , 0 as Unhandled
            , 0 as OnStock
            , 0 as [Status]
            , 0 as IsDeleted
            , 1 as IsAsset
            , dbo.fn_GetEquipmentStatus(e.ID) as EquipmentStatus
        FROM IREquipment as e
            INNER JOIN @TechnicianEquipments as te ON e.ID = te.EquipmentID
            LEFT JOIN #Results as rr ON rr.ModelId = e.IRModelID
            OUTER APPLY dbo.fn_GetModelsAndModelClient(e.IRModelID, NULL, @ClientID, 0, NULL, NULL) as m
        WHERE (e.IRJobID IS NULL
                OR e.IRJobID <> @JobID)
            AND (rr.ID IS NULL OR rr.IsDeleted = 0)
    ) r
    WHERE r.EquipmentStatus NOT IN (4 /* Rental Return */, 6 /* Sold */, 7 /* Disposal */, 3 /* Service */)

    -- insert main rows (model) for existing equipment but not existing JobTechnicianEquipment
    INSERT INTO #Results
    SELECT ID, EquipmentId, NULL as ActualEquipmentId, ManufacturerModel, Manufacturer, Model, Barcode, Serial, ModelId, Requested, Needed, Assigned , Unhandled, OnStock, [Status], IsDeleted, IsAsset,
            (ROW_NUMBER() OVER (ORDER BY
                CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'ASC' THEN ManufacturerModel END ASC,
                CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'DESC' THEN ManufacturerModel END DESC
            )) as RowId
    FROM
    (
        SELECT rr.EquipmentId as ID
            , rr.EquipmentId as EquipmentId
            , rr.Manufacturer + N' ' + rr.Model as ManufacturerModel
            , rr.Manufacturer as Manufacturer
            , rr.Model as Model
            , '' as Barcode
            , '' as Serial
            , rr.ModelId
            , 0 as Requested
            , 0 as Needed
            , 1 as Assigned
            , 0 as Unhandled
            , 0 as OnStock
            , 0 as [Status]
            , 0 as IsDeleted
            , 0 as IsAsset
        FROM #Results as rr
        WHERE rr.IsAsset = 1
            AND rr.ID IS NULL
    ) r
    -- group all subrows (equipment) to one main row (model)
    DECLARE @ManyEquipments TABLE
    (
        ModelId BIGINT,
        [Count] INT,
        EquipmentId BIGINT
    )
    INSERT INTO @ManyEquipments
    SELECT e.ModelId, COUNT(e.EquipmentId), (SELECT MIN(r.EquipmentId) FROM #Results as r WHERE r.ModelId = e.ModelId AND ID IS NOT NULL) as EquipmentId
    FROM (SELECT *
        FROM #Results
        WHERE EquipmentId > 0
            AND ID IS NULL
    ) e
    GROUP BY e.ModelId
    HAVING COUNT(e.EquipmentId) > 1

    -- update subrow.EquipmentId to mainrow.EquipmentId
    UPDATE r
    SET r.EquipmentId = e.EquipmentId
    FROM #Results as r
        INNER JOIN @ManyEquipments as e ON r.ModelId = e.ModelId
    WHERE r.EquipmentId > 0
        AND r.ID IS NULL

    -- update mainrow.Assigned
    UPDATE r
    SET r.Assigned = e.[Count]
    FROM #Results as r
        INNER JOIN @ManyEquipments as e ON r.ModelId = e.ModelId
    WHERE r.EquipmentId = e.EquipmentId
        AND r.ID = e.EquipmentId

    -- remove mainrows with no subrow
    DELETE r
    FROM #Results as r
    WHERE r.ID IS NOT NULL
        AND r.EquipmentId IS NOT NULL
        AND r.IsAsset = 0
        AND NOT EXISTS (SELECT 1 FROM #Results as rr WHERE rr.EquipmentId > 0 AND rr.ID IS NULL AND rr.EquipmentId = r.ID)


    -- update Assigned count
    UPDATE rr
    SET rr.Assigned = (SELECT ISNULL(COUNT(rr2.ID), 0) FROM #Results as rr2 WHERE rr2.IsAsset = 1 AND rr2.ID = rr.ID)
    FROM #Results as rr
    WHERE rr.IsAsset = 0
        AND rr.EquipmentId IS NULL

     --remove equipment transfered, status = 1 - pending
     --equipment jest przypisany do joba, ale jest też w transferze.
     --takich equipmentów nie pokazujemy w jobie
    DELETE r
    FROM #Results as r
    WHERE r.EquipmentId IN (SELECT rr.EquipmentId
                                FROM #Results as rr INNER JOIN
                                    IRDocumentLine as dl ON dl.IREquipmentID = rr.ID
                                                            OR dl.IREquipmentID = rr.EquipmentId
                                                            OR dl.IREquipmentID = rr.ActualEquipmentId
                                                            AND dl.IsDeleted = 0 INNER JOIN
                                    IRDocument as d ON d.ID = dl.IRDocumentID
                                                                AND d.IsDeleted = 0
                                WHERE dl.Status = 1 /*1 - pending*/
                                        AND d.[Type] = 12)


    SELECT @RecordCount = COUNT(1)
    FROM #Results

    IF ((((@PageIndex - 1) * @PageSize) + 1) > @RecordCount)
    BEGIN
        SET @PageIndex = 1
    END

    SELECT ID, ActualEquipmentId as EquipmentId, ManufacturerModel, Manufacturer, Model, Barcode, Serial, ModelId, Requested, Needed, Assigned, Unhandled, OnStock, [Status], IsDeleted, IsAsset, RowId
    FROM #Results
    --WHERE RowId BETWEEN (((@PageIndex - 1) * @PageSize) + 1) AND (@PageIndex * @PageSize)
    ORDER BY CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'ASC' THEN Manufacturer END ASC,
             CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'ASC' THEN Model END ASC,
             CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'DESC' THEN Manufacturer END DESC,
             CASE WHEN @SortField = 'ManufacturerModel' AND @SortDirection = 'DESC' THEN Model END DESC
        , IsAsset ASC

    DROP TABLE #Results

    IF @@ERROR<>0 GOTO ERROR_PROC;

    --------------------------------------------------------------------------
    -- exit point of the store proc
    RETURN(0);

    ERROR_PROC:
    RETURN(99);
END;
GO
-- end PV_IRWebGetOnSiteManagerEquipmentForJob