/****** Object:  StoredProcedure [bsc].[NewGetScore]    Script Date: 10.08.2018 11:10:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [bsc].[NewGetScore]
    @ModelId            INT = 1035,
    @Id                 INT = 1242,
    @IdType             VARCHAR(5) = 'R',   --'R' - Report
                                            --'K' - KPI (ReportRow)
                                            --'I' - Iniciativa (Akce)
                                            --'T' - Task
    @Date               DATE  = NULL,
    @MemberIds          p4s.TIds READONLY,
    @TimeAggregation    BIT = 1,
    @WholeYear          BIT = 1,
    @CheckValidity      TINYINT = 0, -- 0 - datumy validity objektu a nekontroluji
                                     -- 1 - kontrola na dny
                                     -- 2 - kontrola na měsíce
                                     -- 3 - kontrola na roky
    @IncludeTasks       BIT = 1,
    @IncludeInactiveActions BIT = 0,
    @TreeOnly           BIT = 0,-- 1 - Procedura vrátí jen strom objektu, score se nepocita
    @Direction          BIT = 0,-- 0 - Od vstupniho objektu sesttavuju strom dolu
                                -- 1 - Nahoru
    @OutputType         INT = 1 -- 1 - Stromový výstup
                                -- 2 - Score a hodnoty v čase.
AS
BEGIN
-- =============================================
-- Author:      jpr
-- Create date: 15.01.2018
-- Description: Procedura vraci strom BSC objektu od parenta co prijde na vstupu.
--              Bud vrati ciste jen parent-child strom a prislusne membery (@TreeOnly=1),
--              nebo vrati strom a vypoctene skore k aktualnimu datu (@OutputType = 1),
--              nebo vrati skore v case (i s hodnotama) pro konretni vstupni objekt.
--              Vypocet skore fuguje tak, že se prvně sestavi strom child objektu, ocisluji
--              se jeho lvly a pak cursor jde od nejspodnejsiho levelu. Podle typu objektu se
--              vkladaji hodnoty do tabulky #output, kde se i vypocita skore. Pri prechodi na vyssi
--              level, se child objekty groupuji a jejich skore se normalizuje podle vah. Objekty se
--              skore NULL, nebo s weight 0 do výpočtu nevstupují.
-- Changelog:   30.01.2018 - JPR - Do ted probihaly zmeny velmi casto, changelog zacinam logovat od dneska.
--              30.01.2018 - JPR - Pri vypoctu RedThresholdu se jiz neotaci znamenko podle biggerBetter.
--                               - Uzivatel musi hodnotu zadat "chytre"
--              16.04.2018 - JPR - Doplnení funkcionality pro vypoctene ukazatele. Pouziti IndicatorCalculationResolve3
--                               - Zaroven zavedeno ruzne chovani pro ruzne typy agregace
--              18.04.2018 - JPR - Tasky a Inic. nyni vzdy vstupuji do vypoctu skore. Do stromu se nevraci prouze pro @treeOnly=1
--              02.05.2018 - JPR - Tasky a Inic. se nyni dostavaji do stromu v zavislosti na parametru @includeTasks
--              03.05.2018 - JPR - Pokud @CheckValidity = 3 Tak při sestavování stromu
--                               - kontroluji datumy pouze na rok.
--              25.05.2018 - JPR - Pro @IncludeTasks=0 a @OutputType=1 se Tasky do stromu zarazuji, ale mazou se pred vystupem
--                               - Stejne pro @TreeOnly=1
--                               - Pro Tasky i Akce se nyni vraci oboje casove agregace.
--              30.05.2018 - JPR - Opravy pro @CheckValidity=2, nyni se overuje mesice a rok
--              04.06.2018 - JPR - Pro Tasky a @WholeYear=1 se vrací celej Task a ne celej rok.
--              06.06.2018 - JPR - Pro @CheckValidity=2 se nyni overuje zaokrouhlenej datum na mesic. (kontrola zvlast roku a mesice byla spatne)
--                               - Pridano promazani stromu reportu o reporty, ktery nemaji ve stromu rodice (prosly, ale prarodice ne)
-- =============================================
    DECLARE @ModelTimeHierarchyId   INT = (SELECT TimeHierarchyId FROM p4s.ModelReports WHERE ModelId = @ModelId)
    DECLARE @ModelTimeDetailLevel   INT = (SELECT TimeDetailLevel FROM p4s.ModelReports WHERE ModelId = @ModelId)
    DECLARE @TimeMemberListId       INT = (SELECT MemberListId FROM p4s.TimeHierarchyLevels WHERE HierarchyId = @ModelTimeHierarchyId AND LevelOrdinal = @ModelTimeDetailLevel)
    DECLARE @c_childId              VARCHAR(25)
    DECLARE @c_childIdInt           INT
    DECLARE @c_timeId               INT
    DECLARE @c_time                 DATETIME
    DECLARE @memberId               INT
    DECLARE @Query                  NVARCHAR(MAX)
    DECLARE @ReportIndicators       TABLE (IndicatorId INT)
    DECLARE @DateReal               DATETIME
    CREATE TABLE #Output (object VARCHAR(20),
                            timeId  INT,
                            time    DATETIME,
                            score   DECIMAL(15,10),
                            redThresholdValue   FLOAT,
                            redThresholdStatus  FLOAT,
                            redThresholdValueNumber     FLOAT,
                            redThresholdStatusNumber    FLOAT,
                            BiggerBetter    BIT,
                            BiggerBetterStatus  BIT,
                            RedIfZero       FLOAT,
                            timeAggregation INT,
                            BaseValue       FLOAT,
                            GoalValue       FLOAT,
                            BaseStatus      FLOAT,
                            GoalStatus      FLOAT,
                            ValueScore      DECIMAL(15,10),
                            StatusScore     DECIMAL(15,10),
                            WeightStatus    FLOAT,
                            WeightValue     FLOAT,
                            Weight          FLOAT,
                            Trend           DECIMAL(15,10),
                            ObjectiveType   INT
                            )

    CREATE TABLE #OutputFinal (object VARCHAR(20),
                            timeId  INT,
                            time    DATETIME,
                            score   DECIMAL(15,10),
                            redThresholdValue   FLOAT,
                            redThresholdStatus  FLOAT,
                            redThresholdValueNumber     FLOAT,
                            redThresholdStatusNumber    FLOAT,
                            BiggerBetter    BIT,
                            BiggerBetterStatus  BIT,
                            RedIfZero       FLOAT,
                            timeAggregation INT,
                            BaseValue       FLOAT,
                            GoalValue       FLOAT,
                            BaseStatus      FLOAT,
                            GoalStatus      FLOAT,
                            ValueScore      DECIMAL(15,10),
                            StatusScore     DECIMAL(15,10),
                            WeightStatus    FLOAT,
                            WeightValue     FLOAT,
                            Weight          FLOAT,
                            Trend           DECIMAL(15,10),
                            ObjectiveType   INT
                            )
    DECLARE @SortOrder FLOAT = CASE WHEN @IdType = 'R' THEN (SELECT SortOrder FROM p4s.Reports WHERE Id = @Id)
                                    WHEN @IdType = 'K' THEN (SELECT SortOrder FROM p4s.ReportRows WHERE Id = @Id)
                                    ELSE 0
                                END

    IF @OutputType = 1 BEGIN
        SET @WholeYear = 1
        SET @DateReal = @Date
        SET @Date = DATEADD(DAY, -DATEPART(DAY, @date) + 1, @date)  --Zaokrouhelni na mesic
        --SELECT @Date
    END

    SET @DateReal = @Date
    SET @Date = DATEADD(DAY, -DATEPART(DAY, @date) + 1, @date)  --Zaokrouhelni na mesic

    CREATE TABLE #table (Id VARCHAR(50), ParentId VARCHAR(50), Score DECIMAL(15,10), MemberId INT, Level INT, SortOrder FLOAT);
    DECLARE @InputParent NVARCHAR(20)


    -------------------------------------------------------------------------------------------------------------------------
    --------------------    SESTAVOVANÍ STROMU -----------------------------------------------------------------------------
    --------------------    puvodni bsc.GetReportTree   ---------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------------------
    --Docasne reseni membera--
    SET @memberId = (SELECT TOP 1 Id FROM @MemberIds)
    --------------------------
    --select @Date

    IF @IdType = 'R' BEGIN
        ;WITH Reports AS
        (
            SELECT ParentReportId
            FROM p4s.ReportRelations rr
            WHERE ParentReportId = @Id
        UNION ALL
            SELECT rr.ChildReportId
            FROM  p4s.ReportRelations rr
            INNER JOIN Reports r
            ON r.ParentReportId = rr.ParentReportId
        )
        INSERT INTO #table (Id, ParentID, MemberId, SortOrder)
            SELECT DISTINCT 'R'+CAST(h.ChildReportId AS VARCHAR) AS Id,
                            'R'+CAST(h.ParentReportId AS VARCHAR) AS ParentId,
                            NULL AS MemberId,
                            rep.SortOrder AS SortOrder
            FROM p4s.ReportRelations h
            INNER JOIN Reports d
            ON h.ParentReportId = d.ParentReportId
            JOIN p4s.Reports rep    --rep: Child Reporty
            ON rep.Id = h.ChildReportId
            JOIN p4s.Reports rep2   --rep2: Parent Reporty
            ON rep2.Id = h.ParentReportId
            WHERE   --Podminky validace musim kontrolovat jak pro child, tak pro parent. Jinak do stromu lezly reporty,
                    --kterym se do stromu nedostal rodic.
                    (@CheckValidity = 0
                    OR
                    (@CheckValidity = 1 AND (rep.ValidFrom <= @DateReal AND (rep.ValidTo >= @DateReal OR rep.ValidTo IS NULL)))
                    OR
                    (@CheckValidity = 2 AND (CAST(DATEADD(DAY,-DATEPART(DAY, rep.ValidFrom)+1,rep.ValidFrom) AS DATE) <= @Date AND (CAST(DATEADD(DAY,-DATEPART(DAY, rep.ValidTo)+1,rep.ValidTo) AS DATE) >= @Date OR rep.ValidTo IS NULL)))
                    OR
                    (@CheckValidity = 3 AND (YEAR(rep.ValidFrom) <= YEAR(@DateReal) AND (YEAR(rep.ValidTo) >= YEAR(@DateReal) OR rep.ValidTo IS NULL)))
                    )
                    AND
                    (
                        @CheckValidity = 0
                        OR
                        (@CheckValidity = 1 AND (rep2.ValidFrom <= @DateReal AND (rep2.ValidTo >= @DateReal OR rep2.ValidTo IS NULL)))
                        OR
                        (@CheckValidity = 2 AND (CAST(DATEADD(DAY,-DATEPART(DAY, rep2.ValidFrom)+1,rep2.ValidFrom) AS DATE) <= @Date AND (CAST(DATEADD(DAY,-DATEPART(DAY, rep2.ValidTo)+1,rep2.ValidTo) AS DATE) >= @Date OR rep2.ValidTo IS NULL)))
                        OR
                        (@CheckValidity = 3 AND (YEAR(rep2.ValidFrom) <= YEAR(@DateReal) AND (YEAR(rep2.ValidTo) >= YEAR(@DateReal) OR rep2.ValidTo IS NULL)))
                        OR rep2.Id = @Id
                    )


        UNION ALL
            SELECT  'R'+CAST(re.Id AS VARCHAR) AS Id,
                    'R'+CAST(rr.ParentReportId AS VARCHAR) AS ParentId,
                    NULL AS MemberId,
                    re.SortOrder AS SortOrder
            FROM p4s.Reports re
            LEFT JOIN p4s.ReportRelations rr
            ON rr.ChildReportId = re.Id
            WHERE re.Id = @Id

    END

    --select * from #table

    --smaze ze stromu reporty, ktery prosli podminkama rekurze, ale neprosli jejich rodice (prarodice) + vstupni objekt vzdy nechame
    delete from #table where ParentId not in (select Id from #table) and Id <> @IdType+CAST(@Id AS varchar)

    ---test
    --update t set t.MemberId = t2.MemberId from #table t join #table t2 on t.ParentId = t2.Id WHERE t.MemberId is null
    --WHILE EXISTS (SELECT TOP 1 1 FROM #table WHERE MemberId IS NULL)
    --  update t set t.MemberId = t2.MemberId from #table t join #table t2 on t.ParentId = t2.Id WHERE t.MemberId is null

    IF(@IdType = 'K') BEGIN
        SET @InputParent = 'R'+CAST((SELECT ReportId FROM p4s.ReportRows WHERE Id=@id) AS VARCHAR)
        INSERT INTO #table (Id, ParentId, Score, MemberId, SortOrder)
        VALUES ('K'+CAST(@Id AS VARCHAR),
                @InputParent,
                NULL,
                (SELECT MemberId FROM p4s.ReportRows WHERE Id = @Id),
                (SELECT SortOrder FROM p4s.ReportRows WHERE Id = @Id))
    END

    IF(@IdType = 'I') BEGIN
        SET @InputParent = (SELECT ParentType FROM bsc.Actions WHERE Id=@Id)

        IF @InputParent = '0'
            SET @InputParent = 'R'+CAST((SELECT ParentObjectId FROM bsc.Actions WHERE Id=@Id) AS VARCHAR)

        IF @InputParent = '1'
            SET @InputParent = 'K'+CAST((SELECT ParentObjectId FROM bsc.Actions WHERE Id=@Id) AS VARCHAR)

        INSERT INTO #table (Id, ParentId, Score, MemberId, SortOrder)
        VALUES ('I'+CAST(@Id AS VARCHAR), @InputParent, NULL, @MemberId, 0)
    END



    --Vložení KPIs
    INSERT INTO #table(Id, ParentID, MemberId, SortOrder)
        SELECT 'K'+CAST(rr.Id AS VARCHAR) AS Id, CAST(t.Id AS VARCHAR) AS ParentID, rr.MemberId AS MemberId, rr.SortOrder AS SortOrder
        FROM #table t
        JOIN p4s.ReportRows rr
        ON 'R'+CAST(rr.ReportId AS VARCHAR) = t.Id
        WHERE
            @CheckValidity = 0
            OR
            (@CheckValidity = 1 AND (rr.ValidFrom <= @DateReal AND (rr.ValidTo >= @DateReal OR rr.ValidTo IS NULL)))
            OR
            (@CheckValidity = 2 AND (CAST(DATEADD(DAY,-DATEPART(DAY, rr.ValidFrom)+1,rr.ValidFrom) AS DATE) <= @Date AND (CAST(DATEADD(DAY,-DATEPART(DAY, rr.ValidTo)+1,rr.ValidTo) AS DATE) >= @Date OR rr.ValidTo IS NULL)))
            OR
            (@CheckValidity = 3 AND (YEAR(rr.ValidFrom) <= YEAR(@DateReal) AND (YEAR(rr.ValidTo) >= YEAR(@DateReal) OR rr.ValidTo IS NULL)))

    UPDATE t
    SET MemberId = (SELECT MemberId FROM p4s.ReportRows WHERE Id = RIGHT(t.Id, LEN(t.Id)-1))
    FROM #table t
    WHERE Id LIKE 'K%' AND MemberId IS NULL


    --WHILE EXISTS (SELECT TOP 1 1 FROM #table WHERE MemberId is NULL) BEGIN

    --  UPDATE t
    --  SET t.memberId = 1--ISNULL(t.MemberId, t2.MemberId)
    --  FROM #table t
    --  JOIN #table t2
    --  ON t.ParentId = t2.Id
    --  where t.MemberId is null

    --  --print 'x'
    --  --select memberId from #table
    --END

    IF (@IncludeTasks = 1 OR @OutputType = 1) BEGIN
        --Pro @OutputType=1 je potreba at Tasky vstupuji do vypoctu skore, ale nezobrazují se ve stromu.
        --Na konci pred vypisem se z #output tabulky smazou.

        --Pro vložení opatreni musí splnovat: -Musí být k aktuálnímu datu validní (ValidFrom - ValidTo)
        --                                      -Musí mít pod sebou aspoň jeden validní Task (overovani)
        --                                      -Nebo parametr @CheckValidity nastaven na 0 - pak se datumy nekontrolují
        --Vložení opatreni zarazenych pod reportama
        INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
            SELECT 'I'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
            FROM #table t
            JOIN bsc.Actions i
            ON 'R'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 0
            WHERE
                @CheckValidity = 0
                OR
                (@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal
                    AND (EXISTS (SELECT TOP 1 1 FROM bsc.Tasks WHERE ParentType = 2 AND ParentObjectId = i.Id AND ValidFrom <= @DateReal AND ValidTo >= @DateReal) OR @IncludeInactiveActions=1))
                )
                OR
                (@CheckValidity = 2 AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidFrom)+1,i.ValidFrom) AS date) <= @Date AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidTo)+1,i.ValidTo) AS date) >= @Date OR i.ValidTo IS NULL))
                    AND (EXISTS (SELECT TOP 1 1 FROM bsc.Tasks WHERE ParentType = 2 AND ParentObjectId = i.Id AND ValidFrom <= @DateReal AND ValidTo >= @DateReal) OR @IncludeInactiveActions=1)
                )
                OR
                (@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL)))
                    AND (EXISTS (SELECT TOP 1 1 FROM bsc.Tasks WHERE ParentType = 2 AND ParentObjectId = i.Id AND ValidFrom <= @DateReal AND ValidTo >= @DateReal) OR @IncludeInactiveActions=1)
                )

        --Vložení opatreni zarazenych pod reportRows
        INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
            SELECT 'I'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
            FROM #table t
            JOIN bsc.Actions i
            ON 'K'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 1
            WHERE
                @CheckValidity = 0
                OR
                (@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal
                    AND (EXISTS (SELECT TOP 1 1 FROM bsc.Tasks WHERE ParentType = 2 AND ParentObjectId = i.Id AND ValidFrom <= @DateReal AND ValidTo >= @DateReal) OR @IncludeInactiveActions=1))
                )
                OR
                (@CheckValidity = 2 AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidFrom)+1,i.ValidFrom) AS date) <= @Date AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidTo)+1,i.ValidTo) AS date) >= @Date OR i.ValidTo IS NULL))
                    AND (EXISTS (SELECT TOP 1 1 FROM bsc.Tasks WHERE ParentType = 2 AND ParentObjectId = i.Id AND ValidFrom <= @DateReal AND ValidTo >= @DateReal) OR @IncludeInactiveActions=1)
                )
                OR
                (@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL)))
                    AND (EXISTS (SELECT TOP 1 1 FROM bsc.Tasks WHERE ParentType = 2 AND ParentObjectId = i.Id AND ValidFrom <= @DateReal AND ValidTo >= @DateReal) OR @IncludeInactiveActions=1)
                )

        --Vložení tásků zarazenych pod reportama
        INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
            SELECT 'T'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
            FROM #table t
            JOIN bsc.Tasks i
            ON 'R'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 0
            WHERE
                @CheckValidity = 0
                OR
                (@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal))
                OR
                (@CheckValidity = 2 AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidFrom)+1,i.ValidFrom) AS date) <= @Date AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidTo)+1,i.ValidTo) AS date) >= @Date OR i.ValidTo IS NULL)))
                OR
                (@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL))))

        --Vložení tásků zarazenych pod reportRows
        INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
            SELECT 'T'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
            FROM #table t
            JOIN bsc.Tasks i
            ON 'K'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 1
            WHERE
                @CheckValidity = 0
                OR
                (@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal))
                OR
                (@CheckValidity = 2 AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidFrom)+1,i.ValidFrom) AS date) <= @Date AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidTo)+1,i.ValidTo) AS date) >= @Date OR i.ValidTo IS NULL)))
                OR
                (@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL))))

        --Vložení tásků zarazenych pod Iniciativama
        INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
            SELECT 'T'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
            FROM #table t
            JOIN bsc.Tasks i
            ON 'I'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 2
            WHERE
                @CheckValidity = 0
                OR
                (@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal))
                OR
                (@CheckValidity = 2 AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidFrom)+1,i.ValidFrom) AS date) <= @Date AND (CAST(DATEADD(DAY,-DATEPART(DAY, i.ValidTo)+1,i.ValidTo) AS date) >= @Date OR i.ValidTo IS NULL)))
                OR
                (@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL))))
    END

    --Pokud se ptame primo na nejaky list (KPI nebo Task), vlozime ho samotný
    --Zatím verze pro tasky
    IF @IdType = 'T' BEGIN
        INSERT INTO #table (Id, ParentId, Score, MemberId, SortOrder)
        VALUES ('T'+CAST(@Id AS VARCHAR), NULL, NULL, @ModelId, 0)
    END

    UPDATE #table SET Level = 1 WHERE Id = @IdType+CAST(@Id AS varchar)
    ;WITH pc (Id, ParentId, Level)
     AS
    (   -- Anchor member definition
        SELECT t.Id, t.ParentId, t.Level
        FROM #table t
        WHERE t.Level = 1
        UNION ALL
        -- Recursive member definition
        SELECT t.Id, t.ParentId, pc.Level+1
        FROM #table t
        INNER JOIN pc
        ON pc.Id = t.ParentId
    )
    -- Statement that executes the CTE
    UPDATE t
    SET Level = pc.Level
    FROM #table t
    JOIN pc
    ON t.Id = pc.Id





    --Pokud je parametr @TreeOnly - tady se končí a score se nebude pocitat
    IF @TreeOnly = 1 BEGIN
        IF (@IncludeTasks = 0) BEGIN
            DELETE FROM #table
            WHERE LEFT(Id,1) = 'T'
            OR LEFT(Id,1) = 'I'
        END
        SELECT Id, ParentId, Score, SortOrder, MemberId FROM #table
        RETURN
    END


    -----------------------------------------------------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------------------------------------------------
    --  nova cast na vypocet score  ---------------------------------------------------------------------------------------------------------

    --  do tabulky #leafs vlozim vsechny listy stromu. resp. Ids ktere nejsou ve sloupci parentId (nemaji deti)
    CREATE TABLE #leafs (Id VARCHAR(50), ParentId VARCHAR(50), Score DECIMAL(15,10), MemberId INT)
    INSERT INTO #leafs
    SELECT Id, ParentId, Score, MemberId
    FROM #table WHERE Id NOT IN (SELECT DISTINCT ParentId FROM #table WHERE ParentId IS NOT NULL)
--select * from #table
--select * from #leafs
--SELECT Id, ParentId, MemberId, Level FROM #table ORDER BY Level DESC

    -- kurzorem projedu pres vsechny listy, podle jeho typu zvolim vypocet score --
    CREATE TABLE #Children (Id NVARCHAR(50))
    DECLARE @c_Id       VARCHAR(50),
            @c_MemberId INT,
            @c_ObjectType   VARCHAR(1),
            @c_ObjectId     INT,
            @c_ParentId     VARCHAR(50),
            @c_Level        INT,
            @c_PrevLevel    INT = (SELECT MAX(Level) FROM #table)
    DECLARE c_leafs CURSOR FAST_FORWARD LOCAL
    --FOR   SELECT Id, MemberId FROM #leafs
    FOR SELECT Id, ParentId, MemberId, Level FROM #table ORDER BY Level DESC
    OPEN c_leafs
    FETCH NEXT FROM c_leafs INTO @c_Id, @c_ParentID, @c_MemberId, @c_level

    WHILE @@FETCH_STATUS=0 BEGIN
        SET @c_ObjectType = LEFT(@c_Id,1)
        SET @c_ObjectId   = CAST(RIGHT(@c_Id,LEN(@c_Id)-1) AS INT)
        DELETE FROM #Children
        INSERT INTO #Children
        SELECT Id FROM #table WHERE ParentId = @c_Id

        --SELECT * FROM #Children

        IF (SELECT COUNT (Id) FROM #Children) > 0 BEGIN
--SELECT @c_Id, * FROM #Output

            DECLARE c_output CURSOR FAST_FORWARD LOCAL
            FOR
            SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
            UNION ALL
            SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
            AND DATEPART(MONTH, FirstDate) = 12
            ORDER BY FirstDate DESC
            OPEN c_output
            FETCH NEXT FROM c_output INTO @c_timeID, @c_time
            WHILE @@FETCH_STATUS=0 BEGIN
                    IF @WholeYear = 1 BEGIN
                        INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                        VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
                    END
                    ELSE IF (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
                        INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                        VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
                    END
                    IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
                        INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                        VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
                    END
                    ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
                        INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                        VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
                    END


                    FETCH NEXT FROM c_output INTO @c_timeID, @c_time
            END
            CLOSE c_output
            DEALLOCATE c_output

            IF @IdType = 'R'
                UPDATE o
                SET Weight = (SELECT Weight FROM p4s.Reports WHERE Id = @Id)
                FROM #Output o
                WHERE o.object = @c_Id


            --SELECT * FROM #OutputFinal
            UPDATE o
            SET score = d.Score,
                Weight = d.Weight
            FROM #Output o
            JOIN
            (
                SELECT d.TimeId,
                        SUM( (t.Score/CAST(ISNULL(d.w_sum,1) AS float))*t.weight ) AS Score,
                        MAX(t.Weight) AS Weight
                FROM #Output t
                JOIN
                (
                    SELECT TimeId, SUM(Weight) AS w_sum
                    FROM #Output
                    WHERE object IN (SELECT Id FROM #Children)
                    AND score IS NOT NULL AND Weight <> 0 AND timeAggregation=0
                    GROUP BY TimeId
                ) d
                ON d.TimeId = t.TimeId
                WHERE object IN (SELECT Id FROM #Children)
                AND score IS NOT NULL AND Weight <> 0 AND timeAggregation=0
                GROUP BY d.TimeId
            ) d
            ON d.TimeId = o.timeId
            WHERE o.object = @c_Id  AND timeAggregation=0
--select * from #Output where timeId = 2018030001

            UPDATE o
            SET score = d.Score,
                Weight = d.Weight
            FROM #Output o
            JOIN
            (
                SELECT d.TimeId,
                        SUM( (t.Score/CAST(ISNULL(d.w_sum,1) AS float))*t.weight ) AS Score,
                        MAX(t.Weight) AS Weight
                FROM #Output t
                JOIN
                (
                    SELECT TimeId, SUM(Weight) AS w_sum
                    FROM #Output
                    WHERE object IN (SELECT Id FROM #Children)
                    AND score IS NOT NULL AND Weight <> 0 AND timeAggregation=1
                    GROUP BY TimeId
                ) d
                ON d.TimeId = t.TimeId
                WHERE object IN (SELECT Id FROM #Children)
                AND score IS NOT NULL AND Weight <> 0 AND timeAggregation=1
                GROUP BY d.TimeId
            ) d
            ON d.TimeId = o.timeId
            WHERE o.object = @c_Id  AND timeAggregation=1

            DELETE FROM #Output WHERE object IN (SELECT Id FROM #Children)
        END


        IF @c_ObjectType = 'R' BEGIN
            --Kdyz je list report, neni z ceho vypocitat score - nechavam NULL
            IF (SELECT Count(*) FROM #Output) = 0 BEGIN
                DECLARE c_output CURSOR FAST_FORWARD LOCAL
                FOR
                SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
                UNION ALL
                SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
                AND DATEPART(MONTH, FirstDate) = 12
                ORDER BY FirstDate DESC
                OPEN c_output
                FETCH NEXT FROM c_output INTO @c_timeID, @c_time
                WHILE @@FETCH_STATUS=0 BEGIN
                            --IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE time = @c_time AND  object = @c_Id)  --Pokud se z KPI nenajdou data - pridam prázdná řádek
                                INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                                VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)

                            IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
                                --IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE time = @c_time AND timeAggregation=1 AND object = @c_Id) --Pokud jiz existuje radek nizsiho level (od tasku)
                                    INSERT INTO #Output(object,timeId, time, score, timeAggregation, BiggerBetter, Weight, RedIfZero, redThresholdValue)
                                    SELECT object, timeId, time, NULL, 1, biggerBetter, Weight, redifzero, redThresholdValue FROM #Output where time=@c_time AND object = @c_id
                            END
                            ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
                                --IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE time = @c_time AND timeAggregation=1 AND object = @c_Id)
                                    INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                                    VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
                            END

                            IF @WholeYear = 0
                                DELETE FROM #Output WHERE ((month(time) <> month(@Date) AND month(time) <> month(@Date)-1) OR year(time) <> year(@date))

                            FETCH NEXT FROM c_output INTO @c_timeID, @c_time
                END

                --select * from #Output

                CLOSE c_output
                DEALLOCATE c_output

                UPDATE o
                SET Weight = (SELECT Weight FROM p4s.Reports WHERE Id = @Id),
                    ObjectiveType = (SELECT ObjectiveType FROM p4s.Reports WHERE Id = @Id)
                FROM #Output o
                WHERE o.object = @c_Id
            END
            PRINT 'Report(R) jako list'
            --SELECT * FROM #Output
            --RETURN
        END
        ELSE IF @c_ObjectType = 'I' BEGIN
            --Kdyz je list akce(iniciativa), neni z ceho vypocitat score - nechavam NULL
            IF (SELECT Count(*) FROM #Output) = 0 BEGIN
                DECLARE c_output CURSOR FAST_FORWARD LOCAL
                FOR
                SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
UNION ALL
SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
AND DATEPART(MONTH, FirstDate) = 12

                ORDER BY FirstDate DESC
                OPEN c_output
                FETCH NEXT FROM c_output INTO @c_timeID, @c_time
                WHILE @@FETCH_STATUS=0 BEGIN
                            IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE time = @c_time AND timeAggregation=1 AND object = @c_Id) BEGIN --Pokud se z KPI nenajdou data - pridam prázdná řádek
                                INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                                VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
                                INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                                VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
                            END
                            ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
                                IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE time = @c_time AND timeAggregation=1 AND object = @c_Id) BEGIN
                                    INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                                    VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
                                    INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                                    VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
                                END
                            END

                            IF @WholeYear = 0
                                DELETE FROM #Output WHERE ((month(time) <> month(@Date) AND month(time) <> month(@Date)-1) OR year(time) <> year(@date))

                            FETCH NEXT FROM c_output INTO @c_timeID, @c_time
                END
                CLOSE c_output
                DEALLOCATE c_output
            END
            PRINT 'Akce(I) jako list'
            --select * from #Output
        END
        ELSE IF @c_ObjectType = 'T' BEGIN
            DECLARE @TargetValue FLOAT = (SELECT CASE WHEN PlanValue=0 THEN 1 ELSE ISNULL(PlanValue,1) END FROM bsc.TaskValues tv JOIN bsc.Tasks t ON t.Id = tv.TaskId WHERE t.Id = @c_ObjectId AND tv.Name = 'Task end')
            DECLARE @TargetStatus FLOAT = (SELECT CASE WHEN PlanStatus=0 THEN 1 ELSE ISNULL(PlanStatus,1) END FROM bsc.TaskValues tv JOIN bsc.Tasks t ON t.Id = tv.TaskId WHERE t.Id = @c_ObjectId AND tv.Name = 'Task end')
            DECLARE @ActiveFrom  DATE = (SELECT ActiveFrom   FROM bsc.Tasks WHERE Id = @c_ObjectId) --Pouzito i pozdeji pro filtraci neaktivnich tasku
            DECLARE @ActiveFromRounded DATE = DATEADD(DAY, -DATEPART(DAY, @ActiveFrom) + 1, @ActiveFrom)
            DECLARE @ActiveTo    DATE = (SELECT ActiveTo     FROM bsc.Tasks WHERE Id = @c_ObjectId)
            DECLARE @ActiveTORounded DATE = DATEADD(DAY, -DATEPART(DAY, @ActiveTo) + 1, @ActiveTo)
            DECLARE @ActiveFromSTRING   NVARCHAR(20) = CAST(@ActiveFrom AS VARCHAR)
            DECLARE @ActiveToSTRING     NVARCHAR(20) = CAST(@ActiveTo AS VARCHAR)
            DECLARE @ActiveFromSTRING2  NVARCHAR(20) = CAST(DATEADD(MONTH,-1,@ActiveFrom) AS VARCHAR)
            SET @Query = '
            DECLARE c_time CURSOR FAST_FORWARD
            FOR
            SELECT DISTINCT * FROM
            (
            SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = '+CAST(@TimeMemberListId AS VARCHAR)+'
                                AND (FirstDate >= '''+@ActiveFromSTRING+''' OR (MONTH(FirstDate)=MONTH('''+@ActiveFromSTRING+''') AND YEAR(FirstDate)=YEAR('''+@ActiveFromSTRING+''')))
                                AND (FirstDate <= '''+@ActiveToSTRING+''' OR (MONTH(FirstDate)=MONTH('''+@ActiveToSTRING+''') AND YEAR(FirstDate)=YEAR('''+@ActiveToSTRING+''')))
            UNION ALL
            SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = '+CAST(@TimeMemberListId AS VARCHAR)+' AND MONTH(FirstDate)=MONTH('''+@ActiveFromSTRING2+''') AND YEAR(FirstDate)=YEAR('''+@ActiveFromSTRING2+''')
            UNION ALL
            SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = '+CAST(@TimeMemberListId AS VARCHAR)+' AND MONTH(FirstDate)=MONTH('''+CAST(@Date AS varchar)+''') AND YEAR(FirstDate)=YEAR('''+CAST(@Date AS varchar)+''')
            ) d
            ORDER BY FirstDate DESC'
            --print @Query
            EXEC(@Query)
            OPEN c_time
            FETCH NEXT FROM c_time INTO
            @c_timeId, @c_time

            --SELECT Id FROM @MemberIds UNION SELECT @memberId

            WHILE @@FETCH_STATUS=0 BEGIN
                    --print CAST(@c_time AS VARCHAR) + ' -- ' + @c_ObjectType + CAST(@c_ObjectId AS VARCHAR)

                    IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
                        INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                        VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
                        INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                        VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
                    END
                    ELSE IF @WholeYear = 0 AND @TimeAggregation=1 BEGIN --AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time))
                        INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                        VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
                        INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                        VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
                    END


                FETCH NEXT FROM c_time INTO
                @c_timeId, @c_time
            END
            --select @Date
            --select * from #Output
            CLOSE c_time
            DEALLOCATE c_time

            UPDATE o
                SET redThresholdStatus = (SELECT StatusRedThreshold FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
                    redThresholdValue  = (SELECT ValueRedThreshold FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
                    BiggerBetter = (SELECT ValueBiggerBetter FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
                    BiggerBetterStatus = (SELECT StatusBiggerBetter FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
                    WeightStatus = (SELECT StatusWeight FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
                    WeightValue = (SELECT ValueWeight FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
                    Weight = (SELECT Weight FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT))
            FROM #Output o
            WHERE o.object = @c_Id
        --select * from #Output
            UPDATE o
            SET BaseValue = f.ActualValue,
                GoalValue = f.PlanValue,
                BaseStatus = f.ActualStatus,
                GoalStatus = f.PlanStatus
            FROM #Output o
            CROSS APPLY bsc.f_GetLastTaskValues2(o.time, CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)) f
            WHERE o.object = @c_Id

            UPDATE o
            SET
                redThresholdValueNumber =
                CASE WHEN CAST(o.BiggerBetter AS INT) = 1 THEN
                    CASE WHEN o.GoalValue > 0 THEN
                        (((1-redThresholdValue)*(-o.GoalValue))+o.GoalValue)
                    ELSE
                        CASE WHEN o.GoalValue < 0 THEN
                            ((1-redThresholdValue)*(o.GoalValue)+o.GoalValue)
                        ELSE (SELECT ParameterValue FROM amb.Plan4SParameters where ParameterName = 'bsc_RedFlagValueTaskValue')*@TargetValue
                        END
                    END
                ELSE
                    CASE WHEN o.GoalValue > 0 THEN
                        ((1-redThresholdValue)*(o.GoalValue)+o.GoalValue)
                    ELSE
                        CASE WHEN o.GoalValue < 0 THEN
                            ((1-redThresholdValue)*(-o.GoalValue)+o.GoalValue)
                        ELSE (SELECT ParameterValue FROM amb.Plan4SParameters where ParameterName = 'bsc_RedFlagValueTaskValue')*@TargetValue
                        END
                    END
                END,
                redThresholdStatusNumber =
                CASE WHEN CAST(o.BiggerBetterStatus AS INT) = 1 THEN
                    CASE WHEN o.GoalStatus > 0 THEN
                        (((1-redThresholdStatus)*(-o.GoalStatus))+o.GoalStatus)
                    ELSE
                        CASE WHEN o.GoalStatus < 0 THEN
                            ((1-redThresholdStatus)*(o.GoalStatus)+o.GoalStatus)
                        ELSE (SELECT ParameterValue FROM amb.Plan4SParameters where ParameterName = 'bsc_RedFlagValueTaskStatus')*@TargetStatus
                        END
                    END
                ELSE
                    CASE WHEN o.GoalStatus > 0 THEN
                        ((1-redThresholdStatus)*(o.GoalStatus)+o.GoalStatus)
                    ELSE
                        CASE WHEN o.GoalStatus < 0 THEN
                            ((1-redThresholdStatus)*(-o.GoalStatus)+o.GoalStatus)
                        ELSE (SELECT ParameterValue FROM amb.Plan4SParameters where ParameterName = 'bsc_RedFlagValueTaskStatus')*@TargetStatus
                        END
                    END
                END
            FROM #Output o
            CROSS APPLY bsc.f_GetLastTaskValues2(o.time, CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)) f
            WHERE o.object = @c_Id


            UPDATE o
            SET ValueScore = (BaseValue-(2*redThresholdValueNumber) + GoalValue) / CAST(((3*GoalValue)-(3*redThresholdValueNumber)) AS FLOAT) * 15,
                StatusScore = (BaseStatus-(2*redThresholdStatusNumber) + GoalStatus) / CAST(((3*GoalStatus)-(3*redThresholdStatusNumber)) AS FLOAT) * 15
            FROM #Output o
            WHERE o.object = @c_Id


            --select * from #Output
            UPDATE o
            SET score = ((ISNULL(ValueScore,0)/(WeightStatus+WeightValue))*WeightValue) + ((ISNULL(StatusScore,0)/(WeightStatus+WeightValue))*WeightStatus)
            FROM #Output o
            WHERE o.object = @c_Id

            --Když je ValueScore i StatusScore NULL, vysledne je taky NULL (z vypoctu nad je 0.0000)
            UPDATE o
            SET score = NULL
            FROM #Output o
            WHERE o.object = @c_Id
            AND ValueScore IS NULL AND StatusScore IS NULL

            --Vymazání hodnot pokud je ukol mimo rozsah activeFrom - activeTo
--ActiveFrom a ActiveTo zaokrouhlit na měsic (prvni den mesice) - pak az overovat. Obdobne k @checkValidity = 2
            UPDATE o
            SET score = null, ValueScore = null, StatusScore = null, BaseValue = null, GoalValue = null, BaseStatus = null, GoalStatus = null, redThresholdStatusNumber = null, redThresholdValueNumber = null
            FROM #Output o
            WHERE (time < @ActiveFromRounded
                    OR
                   time > @ActiveTORounded)
            AND object = @c_Id

            --select * from #Output

        END
        ELSE IF @c_ObjectType = 'K' BEGIN
IF EXISTS (SELECT TOP 1 1 FROM #Output WHERE object = @c_Id)
    DELETE FROM #Output WHERE object = @c_Id
                DELETE FROM @ReportIndicators
                INSERT INTO @ReportIndicators SELECT IndicatorId FROM p4s.ReportRows WHERE Id = @c_ObjectId
                DECLARE c_output CURSOR FAST_FORWARD LOCAL
                FOR
                SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
                UNION ALL
SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
AND DATEPART(MONTH, FirstDate) = 12

                ORDER BY FirstDate aSC
                OPEN c_output
                FETCH NEXT FROM c_output INTO @c_timeID, @c_time
                WHILE @@FETCH_STATUS=0 BEGIN
                        INSERT INTO #Output(Object,Time,TimeId,RedIfZero,timeAggregation,redThresholdValue,BiggerBetter,Weight,redThresholdValueNumber,Score,BaseValue,GoalValue)
                        SELECT  @c_ObjectType+CAST(@c_ObjectId AS VARCHAR) Object,
                                @c_time AS Time,
                                TimeId,
                                RedIfZero,
                                0 AS timeAggregation,
                                redThresholdValue,
                                BiggerBetter,
                                Weight,
                                CASE WHEN CAST(BiggerBetter AS INT) = 1 THEN
                                        CASE WHEN piv.[20] > 0 THEN
                                            (((1-redThresholdValue)*(-piv.[20]))+piv.[20])
                                        ELSE
                                            CASE WHEN piv.[20] < 0 THEN
                                                ((1-redThresholdValue)*(piv.[20])+piv.[20])
                                            ELSE RedIfZero
                                            END
                                        END
                                    ELSE
                                        CASE WHEN piv.[20] > 0 THEN
                                            ((1-redThresholdValue)*(piv.[20])+piv.[20])
                                        ELSE
                                            CASE WHEN piv.[20] < 0 THEN
                                                ((1-redThresholdValue)*(-piv.[20])+piv.[20])
                                            ELSE RedIfZero
                                            END
                                        END
                                END AS redThresholdValueNumber,
                                NULL AS Score,
                                piv.[10] AS BaseValue,
                                piv.[20] AS GoalValue
                        FROM
                        (
                            SELECT
                                TimeId AS TimeID,
                                f.IndicatorId,
                                Value AS Value,
                                f.PlanId,
                                rr.RedIfZero AS RedIfZero,
                                rr.redThreshold AS redThresholdValue,
                                CAST(rr.BiggerBetter AS INT) AS BiggerBetter,
                                rr.Weight AS Weight,
                                NULL AS redThresholdValueNumber,
                                NULL AS Score
                            FROM p4s.Facts f
                            JOIN p4s.ReportRows rr
                            ON f.IndicatorId = rr.IndicatorId
                            WHERE rr.Id = @c_ObjectId
                            AND f.ModelId = @ModelId
                            AND f.IndicatorId IN (SELECT IndicatorId FROM @ReportIndicators)
                            AND TimeId = @c_timeId
                            AND Value IS NOT NULL
                            AND f.DimMemberId =@c_MemberId--IN (SELECT Id FROM @MemberIds UNION SELECT @memberId)
                            --AND rr.Weight <> 0
                        ) src
                        pivot
                        (
                          sum(Value)
                          for PlanId in ([10], [20])
                        ) piv;


                        IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE time = @c_time AND timeAggregation=0 AND object = @c_Id)   --Pokud se z KPI nenajdou data - pridam prázdná řádek
                            INSERT INTO #Output(object,timeId, time, score, timeAggregation)
                            VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)

                        IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
                            --IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE time = @c_time AND timeAggregation=1 AND object = @c_Id) --Pokud jiz existuje radek nizsiho level (od tasku)
                                INSERT INTO #Output(object,timeId, time, score, timeAggregation, BiggerBetter, Weight, RedIfZero, redThresholdValue)
                                SELECT object, timeId, time, NULL, 1, biggerBetter, Weight, redifzero, redThresholdValue FROM #Output where time=@c_time AND object = @c_id
                        END
                        ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
                            INSERT INTO #Output(object,timeId, time, score, timeAggregation, BiggerBetter, Weight, RedIfZero, redThresholdValue)
                                SELECT object, timeId, time, NULL, 1, biggerBetter, Weight, redifzero, redThresholdValue FROM #Output where time=@c_time AND object = @c_id
                        END
                        IF @WholeYear = 0
                            DELETE FROM #Output WHERE ((month(time) <> month(@Date) AND month(time) <> month(@Date)-1) OR year(time) <> year(@date))


                        --Vycisleni hodnot pocitaneho ukazatele. Probehne 4x. Pro agregaci a pro zvolené období.
                        --A pro skutečnost a plán.
                        --Je potreba tady vymyslet nejakou optimalizaci, at to netrva tak dlouho
                        IF (SELECT TOP 1 TypeId FROM p4s.Indicators WHERE Id IN (SELECT IndicatorId FROM @ReportIndicators)) = 2 BEGIN
                            DECLARE @IndicatorID INT = (SELECT IndicatorId FROM @ReportIndicators)
                            DECLARE @ValueTypeId10 INT = (SELECT CAST(LEFT(@c_timeId,4)+'010' AS INT))
                            DECLARE @ValueTypeId20 INT = (SELECT CAST(LEFT(@c_timeId,4)+'020' AS INT))
                            DECLARE @Time p4s.TIds
                            DELETE FROM @Time
                            INSERT INTO @Time VALUES (@c_timeId)
                            DECLARE @Dim p4s.TIds
                            DELETE FROM @Dim
                            INSERT INTO @Dim VALUES (@c_MemberId)
                            CREATE TABLE #vysledek (ModelId INT, IndicatorId INT, TimeId INT, ValueTypeID INT, PlanId INT, DimeMemberId INT, SubIndicatorId INT, Value FLOAT)

                            INSERT INTO #vysledek
                            exec p4s.IndicatorCalculationResolve3 @ModelId=@ModelId, @IndicatorId=@IndicatorID, @TimeFilterSet = @Time, @DimFilterSet = @Dim, @DimLevel = 1, @TimeLevel = 1, @ValueTypeId = @ValueTypeId10

                            UPDATE o
                            SET BaseValue = v.Value
                            FROM #Output o
                            JOIN #vysledek v
                            ON o.timeId = v.TimeId
                            AND o.timeAggregation = 0

                            DROP TABLE #vysledek


                            CREATE TABLE #vysledek2 (ModelId INT, IndicatorId INT, TimeId INT, ValueTypeID INT, PlanId INT, DimeMemberId INT, SubIndicatorId INT, Value FLOAT)

                            INSERT INTO #vysledek2
                            exec p4s.IndicatorCalculationResolve3 @ModelId=@ModelId, @IndicatorId=@IndicatorID, @TimeFilterSet = @Time, @DimFilterSet = @Dim, @DimLevel = 1, @TimeLevel = 1, @ValueTypeId = @ValueTypeId20

                            UPDATE o
                            SET GoalValue = v.Value
                            FROM #Output o
                            JOIN #vysledek2 v
                            ON o.timeId = v.TimeId
                            AND o.timeAggregation = 0

                            DROP TABLE #vysledek2


                            ------AGG------
                            DECLARE @TimeAgg p4s.TIds
                            INSERT INTO @TimeAgg VALUES (@c_timeId)

                            CREATE TABLE #vysledekAGG (Value FLOAT)
                            INSERT INTO #vysledekAGG
                            exec p4s.IndicatorCalculationResolve3 @ModelId=@ModelId, @IndicatorId=@IndicatorID, @TimeFilterSet = @TimeAgg, @DimFilterSet = @Dim, @DimLevel = 1, @TimeLevel = 1, @ValueTypeId = @ValueTypeId10, @SolveType=2


                            UPDATE o
                            SET BaseValue = v.Value
                            FROM #Output o
                            JOIN #vysledekAGG v
                            ON o.timeId = @c_timeId
                            WHERE o.timeAggregation = 1


                            CREATE TABLE #vysledekAGG2 (Value FLOAT)
                            INSERT INTO #vysledekAGG2
                            exec p4s.IndicatorCalculationResolve3 @ModelId=@ModelId, @IndicatorId=@IndicatorID, @TimeFilterSet = @TimeAgg, @DimFilterSet = @Dim, @DimLevel = 1, @TimeLevel = 1, @ValueTypeId = @ValueTypeId20, @SolveType=2

                            UPDATE o
                            SET GoalValue = v.Value
                            FROM #Output o
                            JOIN #vysledekAGG2 v
                            ON o.timeId = @c_timeId
                            WHERE o.timeAggregation = 1

                            DROP TABLE #vysledekAGG
                            DROP TABLE #vysledekAGG2

                        END


                        FETCH NEXT FROM c_output INTO @c_timeID, @c_time
                END
                CLOSE c_output
                DEALLOCATE c_output

                UPDATE o
                SET
                    RedIfZero = (SELECT RedIfZero FROM p4s.ReportRows WHERE Id = @c_ObjectId),
                    BiggerBetter = (SELECT BiggerBetter FROM p4s.ReportRows WHERE Id = @c_ObjectId),
                    Weight = (SELECT Weight FROM p4s.ReportRows WHERE Id = @c_ObjectId),
                    redThresholdValue = (SELECT RedThreshold FROM p4s.ReportRows WHERE Id = @c_ObjectId)
                FROM #Output o
                WHERE
                o.object = @c_Id

                --select * from #Output

                --Pro aditivni ukazatele vyscitam hodnoty za predchozi obdobi. Pro semiaditivni nechavam hodnoty stejne
                --Pro agregaci typu vypocet se hodnoty plní výše, procedurou IndicatorCalculationResolve3.
                IF ((SELECT AggregationTypeId FROM p4s.Indicators WHERE Id = (SELECT IndicatorId FROM @ReportIndicators)) = 1
                    AND
                    (SELECT TypeId FROM p4s.Indicators WHERE Id = (SELECT IndicatorId FROM @ReportIndicators)) <> 2) BEGIN

                    UPDATE t
                    SET BaseValue = (SELECT SUM(k.BaseValue) FROM #Output k WHERE k.Time <= t.Time AND k.object = @c_Id AND timeAggregation = 0)
                    FROM #Output t
                    WHERE timeAggregation = 1
                    AND t.object = @c_Id
                    AND EXISTS (SELECT TOP 1 1 FROM #Output WHERE time=t.time AND timeAggregation=0 AND BaseValue IS NOT NULL AND object=@c_Id)

                    UPDATE t
                    SET GoalValue = (SELECT SUM(k.GoalValue) FROM #Output k WHERE k.Time <= t.Time AND k.object = @c_Id AND timeAggregation = 0)
                    FROM #Output t
                    WHERE timeAggregation = 1
                    AND t.object = @c_Id
                    AND EXISTS (SELECT TOP 1 1 FROM #Output WHERE time=t.time AND timeAggregation=0 AND GoalValue IS NOT NULL AND object=@c_Id)
                END
                ELSE IF ((SELECT AggregationTypeId FROM p4s.Indicators WHERE Id = (SELECT IndicatorId FROM @ReportIndicators)) IN (2)
                        AND
                        (SELECT TypeId FROM p4s.Indicators WHERE Id = (SELECT IndicatorId FROM @ReportIndicators)) <> 2)
                    UPDATE t
                    SET BaseValue = (SELECT BaseValue FROM #Output k WHERE k.object = t.object AND timeAggregation = 0 AND k.time = t.time),
                        GoalValue = (SELECT GoalValue FROM #Output k WHERE k.object = t.object AND timeAggregation = 0 AND k.time = t.time)
                    FROM #Output t
                    WHERE timeAggregation = 1
                    AND t.object = @c_Id

                UPDATE t
                SET redThresholdValueNumber = CASE WHEN CAST(BiggerBetter AS INT) = 1 THEN
                                            CASE WHEN t.GoalValue > 0 THEN
                                                (((1-redThresholdValue)*(-t.GoalValue))+t.GoalValue)
                                            ELSE
                                                CASE WHEN t.GoalValue < 0 THEN
                                                    ((1-redThresholdValue)*(t.GoalValue)+t.GoalValue)
                                                ELSE RedIfZero
                                                END
                                            END
                                        ELSE
                                            CASE WHEN t.GoalValue > 0 THEN
                                                ((1-redThresholdValue)*(t.GoalValue)+t.GoalValue)
                                            ELSE
                                                CASE WHEN GoalValue < 0 THEN
                                                    ((1-redThresholdValue)*(-GoalValue)+t.GoalValue)
                                                ELSE RedIfZero
                                                END
                                            END
                                    END
                FROM #Output t
                --WHERE timeAggregation = 1
                WHERE t.object = @c_Id

                UPDATE t
                SET t.Weight = (SELECT Weight FROM p4s.ReportRows WHERE Id = @c_ObjectId)
                FROM #Output t
                WHERE t.object = @c_Id

                UPDATE t
                SET Score = (BaseValue-(2*redThresholdValueNumber) + GoalValue) / CAST(((3*GoalValue)-(3*redThresholdValueNumber)) AS float) * 15
                FROM #Output t
                WHERE t.object = @c_id

--SELECT * FROM #Output
        END


    --select * from #Output


        IF @OutputType = 1  BEGIN--Tady musi byt vypocet agregovaneho score! a ne score za daný měsíc.

            UPDATE t
            SET t.score = o.score
            FROM #table t
            JOIN #Output o
            ON o.object = t.Id
            WHERE o.time = @Date
            AND o.timeAggregation = 1

            --SELECT * from #Output
        END
    --select @c_Level, @c_Id, * from #Output

        SET @c_PrevLevel = @c_Level
        FETCH NEXT FROM c_leafs INTO @c_Id, @c_ParentId ,@c_MemberId, @c_level
    END


    IF @IdType = 'I' BEGIN
        UPDATE o
        SET Weight = (SELECT Weight FROM bsc.Actions WHERE Id = @Id)
        FROM #Output o
        --DELETE FROM #Output WHERE timeAggregation = 0 -- Po domluvě, že Tasky i Akce budou vždy vracet obe casove agregace
    END

    IF @IdType = 'R' BEGIN
        UPDATE o
        SET ObjectiveType = (SELECT ObjectiveType FROM p4s.Reports WHERE Id = @Id)
        FROM #Output o
    END

    IF @OutputType = 1 BEGIN
        IF (@IncludeTasks = 0) BEGIN
            DELETE FROM #table
            WHERE LEFT(Id,1) = 'T'
            OR LEFT(Id,1) = 'I'
        END
        SELECT Id, ParentId, Score, SortOrder FROM #table
    END

    --SELECT * from #Output

    IF @OutputType = 2 BEGIN
        --Pokud mame vic dat - normalizujeme score v case, jinak muzem vypisovat------------
        ------------------------------------------------------------------------------------
        IF (SELECT COUNT(DISTINCT Object) FROM #Output) > 1  OR ((SELECT COUNT(*) FROM #Output) > 26 AND @IdType <> 'T')
         BEGIN
            --SELECT * from #Output
            DECLARE c_output CURSOR FAST_FORWARD LOCAL
            FOR
            SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
            UNION ALL
            SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
            AND DATEPART(MONTH, FirstDate) = 12
            ORDER BY FirstDate DESC
            OPEN c_output
            FETCH NEXT FROM c_output INTO @c_timeID, @c_time
            WHILE @@FETCH_STATUS=0 BEGIN
                    IF @WholeYear = 1 BEGIN
                        INSERT INTO #OutputFinal(object,timeId, time, score, timeAggregation)
                        VALUES (@IdType+CAST(@Id AS VARCHAR),@c_timeId, @c_time, NULL, 0)
                    END
                    ELSE IF MONTH(@Date) = MONTH(@c_time) BEGIN
                        INSERT INTO #OutputFinal(object,timeId, time, score, timeAggregation)
                        VALUES (@IdType+CAST(@Id AS VARCHAR),@c_timeId, @c_time, NULL, 0)
                    END
                    IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
                        INSERT INTO #OutputFinal(object,timeId, time, score, timeAggregation)
                        VALUES (@IdType+CAST(@Id AS VARCHAR),@c_timeId, @c_time, NULL, 1)
                    END
                    ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND MONTH(@Date) = MONTH(@c_time) BEGIN
                        INSERT INTO #OutputFinal(object,timeId, time, score, timeAggregation)
                        VALUES (@IdType+CAST(@Id AS VARCHAR),@c_timeId, @c_time, NULL, 1)
                    END


                    FETCH NEXT FROM c_output INTO @c_timeID, @c_time
            END
            CLOSE c_output
            DEALLOCATE c_output

            IF @IdType = 'R'
                UPDATE o
                SET Weight = (SELECT Weight FROM p4s.Reports WHERE Id = @Id),
                    ObjectiveType = (SELECT ObjectiveType FROM p4s.Reports WHERE Id = @Id)
                FROM #OutputFinal o


            --SELECT * FROM #Output

            --Vypocet score se zde dela pro dve ruzne casove agregace zvlášť
            --pro NEagregovana data
            UPDATE o
            SET score = d.Score,
                Weight = d.Weight
            FROM #OutputFinal o
            JOIN
            (
                SELECT d.TimeId,
                        SUM( (t.Score/CAST(ISNULL(d.w_sum,1) AS float))*t.weight ) AS Score,
                        MAX(t.Weight) AS Weight
                FROM #Output t
                JOIN
                (
                    SELECT TimeId, SUM(Weight) AS w_sum
                    FROM #Output
                    WHERE score IS NOT NULL AND Weight <> 0 AND timeAggregation=0
                    GROUP BY TimeId
                ) d
                ON d.TimeId = t.TimeId
                WHERE score IS NOT NULL AND Weight <> 0 AND timeAggregation=0
                GROUP BY d.TimeId
            ) d
            ON d.TimeId = o.timeId
            WHERE timeAggregation=0

            --pro agregovana data
            UPDATE o
            SET score = d.Score,
                Weight = d.Weight
            FROM #OutputFinal o
            JOIN
            (
                SELECT d.TimeId,
                        SUM( (t.Score/CAST(ISNULL(d.w_sum,1) AS float))*t.weight ) AS Score,
                        MAX(t.Weight) AS Weight
                FROM #Output t
                JOIN
                (
                    SELECT TimeId, SUM(Weight) AS w_sum
                    FROM #Output
                    WHERE score IS NOT NULL AND Weight <> 0 AND timeAggregation=1
                    GROUP BY TimeId
                ) d
                ON d.TimeId = t.TimeId
                WHERE score IS NOT NULL AND Weight <> 0 AND timeAggregation=1
                GROUP BY d.TimeId
            ) d
            ON d.TimeId = o.timeId
            WHERE timeAggregation=1

            --select * from #OutputFinal

            --update TRENDU
            UPDATE o
            SET o.Trend = o.score - oo.score
            FROM #OutputFinal o
            JOIN #OutputFinal oo
            ON DATEADD(month, 1, oo.time) = o.time
            WHERE o.timeAggregation = 0
            AND oo.timeAggregation = 0
            --
            --update TRENDU agg
            UPDATE o
            SET o.Trend = o.score - oo.score
            FROM #OutputFinal o
            JOIN #OutputFinal oo
            ON DATEADD(month, 1, oo.time) = o.time
            WHERE o.timeAggregation = 1
            AND oo.timeAggregation = 1
            --

            --SELECT * from #Output
            --select 1

            SELECT timeId,time,score,redThresholdValueNumber AS redThresholdValue,redThresholdStatusNumber AS redThresholdStatus,RedIfZero,timeAggregation,BaseValue,GoalValue,BaseStatus,GoalStatus,ValueScore,StatusScore,WeightStatus,WeightValue,Weight, @SortOrder AS SortOrder, Trend, ObjectiveType
            FROM #OutputFinal
            WHERE (@WholeYear=1 AND YEAR(time) = YEAR(@Date)) OR
                  --(@WholeYear=1 AND @IdType = 'T') OR
                  (@WholeYear=0 AND MONTH(@Date) = MONTH(time))
            ORDER BY time ASC
        END
        ------------------------------------------------------------------------------------
        ELSE BEGIN

            --update TRENDU
            UPDATE o
            SET o.Trend = o.score - oo.score
            FROM #Output o
            JOIN #Output oo
            ON DATEADD(month, 1, oo.time) = o.time
            WHERE o.timeAggregation = 0
            AND oo.timeAggregation = 0
            --
            --update TRENDU agg
            UPDATE o
            SET o.Trend = o.score - oo.score
            FROM #Output o
            JOIN #Output oo
            ON DATEADD(month, 1, oo.time) = o.time
            WHERE o.timeAggregation = 1
            AND oo.timeAggregation = 1
            --


            --Puvodni verze mazala radky kde je time < @activeFrom. Zmeneno na porovnani pouze mesice. Pozadavek od Adama.
            --VF podstatě vyhodí TRENDovej mesic
            --OPRAVA: pro Tasky a @WholeYear=1 se vrací celej Task, a ne celej strom.
            --DECLARE @ActiveFromRounded DATE
            IF @IdType='T' AND @WholeYear = 1 BEGIN
                DELETE FROM #Output WHERE time < @ActiveFromRounded
            END

            --IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE MONTH(time) = MONTH(@Date))
                --SELECT 1
                --INSERT INTO #Output(TimeId,time,timeAggregation)
                --VALUES (1,@Date,0), (1,@Date,1)
            --select * from #Output

            SELECT timeId,time,score,redThresholdValueNumber AS redThresholdValue,redThresholdStatusNumber AS redThresholdStatus,RedIfZero,timeAggregation,BaseValue,GoalValue,BaseStatus,GoalStatus,ValueScore,StatusScore,WeightStatus,WeightValue,Weight, @SortOrder AS SortOrder, Trend, ObjectiveType
            FROM #Output
            WHERE (@WholeYear=1 AND YEAR(time) = YEAR(@Date) AND @IdType <> 'T') OR
                  (@WholeYear=1 AND @IdType = 'T') OR
                  (@WholeYear=0 AND MONTH(@Date) = MONTH(time) AND YEAR(time) = YEAR(@Date))
            ORDER BY time ASC
        END
    END


    END