aHJRBrrS

1/****** Object:  StoredProcedure [bsc].[NewGetScore]    Script Date: 10.08.2018 11:10:18 ******/
2SET ANSI_NULLS ON
3GO
4SET QUOTED_IDENTIFIER ON
5GO
6
7
8
9ALTER PROCEDURE [bsc].[NewGetScore]
10	@ModelId			INT = 1035,
11	@Id					INT	= 1242,
12	@IdType				VARCHAR(5) = 'R',	--'R' - Report
13											--'K' - KPI (ReportRow)
14											--'I' - Iniciativa (Akce)
15											--'T' - Task
16	@Date				DATE  = NULL,
17	@MemberIds			p4s.TIds READONLY,
18	@TimeAggregation	BIT = 1,
19	@WholeYear			BIT = 1,
20	@CheckValidity		TINYINT = 0, -- 0 - datumy validity objektu a nekontroluji
21									 -- 1 - kontrola na dny
22									 -- 2 - kontrola na měsíce
23									 -- 3 - kontrola na roky
24	@IncludeTasks		BIT = 1,
25	@IncludeInactiveActions BIT = 0,
26	@TreeOnly			BIT = 0,-- 1 - Procedura vrátí jen strom objektu, score se nepocita
27	@Direction			BIT = 0,-- 0 - Od vstupniho objektu sesttavuju strom dolu
28								-- 1 - Nahoru
29	@OutputType			INT = 1 -- 1 - Stromový výstup
30								-- 2 - Score a hodnoty v čase.
31AS
32BEGIN
33-- =============================================
34-- Author:		jpr
35-- Create date: 15.01.2018
36-- Description:	Procedura vraci strom BSC objektu od parenta co prijde na vstupu.
37--				Bud vrati ciste jen parent-child strom a prislusne membery (@TreeOnly=1),
38--				nebo vrati strom a vypoctene skore k aktualnimu datu (@OutputType = 1),
39--				nebo vrati skore v case (i s hodnotama) pro konretni vstupni objekt.
40--				Vypocet skore fuguje tak, že se prvně sestavi strom child objektu, ocisluji
41--				se jeho lvly a pak cursor jde od nejspodnejsiho levelu. Podle typu objektu se
42--				vkladaji hodnoty do tabulky #output, kde se i vypocita skore. Pri prechodi na vyssi
43--				level, se child objekty groupuji a jejich skore se normalizuje podle vah. Objekty se
44--				skore NULL, nebo s weight 0 do výpočtu nevstupují.
45-- Changelog:	30.01.2018 - JPR - Do ted probihaly zmeny velmi casto, changelog zacinam logovat od dneska.
46--				30.01.2018 - JPR - Pri vypoctu RedThresholdu se jiz neotaci znamenko podle biggerBetter.
47--								 - Uzivatel musi hodnotu zadat "chytre"
48--				16.04.2018 - JPR - Doplnení funkcionality pro vypoctene ukazatele. Pouziti IndicatorCalculationResolve3
49--								 - Zaroven zavedeno ruzne chovani pro ruzne typy agregace
50--				18.04.2018 - JPR - Tasky a Inic. nyni vzdy vstupuji do vypoctu skore. Do stromu se nevraci prouze pro @treeOnly=1
51--				02.05.2018 - JPR - Tasky a Inic. se nyni dostavaji do stromu v zavislosti na parametru @includeTasks
52--				03.05.2018 - JPR - Pokud @CheckValidity = 3 Tak při sestavování stromu
53--								 - kontroluji datumy pouze na rok.
54--				25.05.2018 - JPR - Pro @IncludeTasks=0 a @OutputType=1 se Tasky do stromu zarazuji, ale mazou se pred vystupem
55--								 - Stejne pro @TreeOnly=1
56--								 - Pro Tasky i Akce se nyni vraci oboje casove agregace.
57--				30.05.2018 - JPR - Opravy pro @CheckValidity=2, nyni se overuje mesice a rok
58--				04.06.2018 - JPR - Pro Tasky a @WholeYear=1 se vrací celej Task a ne celej rok.
59--				06.06.2018 - JPR - Pro @CheckValidity=2 se nyni overuje zaokrouhlenej datum na mesic. (kontrola zvlast roku a mesice byla spatne)
60--								 - Pridano promazani stromu reportu o reporty, ktery nemaji ve stromu rodice (prosly, ale prarodice ne)
61-- =============================================
62	DECLARE @ModelTimeHierarchyId	INT = (SELECT TimeHierarchyId FROM p4s.ModelReports WHERE ModelId = @ModelId)
63	DECLARE @ModelTimeDetailLevel	INT = (SELECT TimeDetailLevel FROM p4s.ModelReports WHERE ModelId = @ModelId)
64	DECLARE @TimeMemberListId		INT = (SELECT MemberListId FROM p4s.TimeHierarchyLevels WHERE HierarchyId = @ModelTimeHierarchyId AND LevelOrdinal = @ModelTimeDetailLevel)
65	DECLARE @c_childId				VARCHAR(25)
66	DECLARE @c_childIdInt			INT
67	DECLARE @c_timeId				INT
68	DECLARE @c_time					DATETIME
69	DECLARE @memberId				INT
70	DECLARE @Query					NVARCHAR(MAX)
71	DECLARE @ReportIndicators		TABLE (IndicatorId INT)
72	DECLARE @DateReal				DATETIME
73	CREATE TABLE #Output (object VARCHAR(20),
74							timeId	INT,
75							time	DATETIME,
76							score	DECIMAL(15,10),
77							redThresholdValue	FLOAT,
78							redThresholdStatus	FLOAT,
79							redThresholdValueNumber		FLOAT,
80							redThresholdStatusNumber	FLOAT,
81							BiggerBetter	BIT,
82							BiggerBetterStatus	BIT,
83							RedIfZero		FLOAT,
84							timeAggregation INT,
85							BaseValue		FLOAT,
86							GoalValue		FLOAT,
87							BaseStatus		FLOAT,
88							GoalStatus		FLOAT,
89							ValueScore		DECIMAL(15,10),
90							StatusScore		DECIMAL(15,10),
91							WeightStatus	FLOAT,
92							WeightValue		FLOAT,
93							Weight			FLOAT,
94							Trend			DECIMAL(15,10),
95							ObjectiveType   INT
96							)
97
98	CREATE TABLE #OutputFinal (object VARCHAR(20),
99							timeId	INT,
100							time	DATETIME,
101							score	DECIMAL(15,10),
102							redThresholdValue	FLOAT,
103							redThresholdStatus	FLOAT,
104							redThresholdValueNumber		FLOAT,
105							redThresholdStatusNumber	FLOAT,
106							BiggerBetter	BIT,
107							BiggerBetterStatus	BIT,
108							RedIfZero		FLOAT,
109							timeAggregation INT,
110							BaseValue		FLOAT,
111							GoalValue		FLOAT,
112							BaseStatus		FLOAT,
113							GoalStatus		FLOAT,
114							ValueScore		DECIMAL(15,10),
115							StatusScore		DECIMAL(15,10),
116							WeightStatus	FLOAT,
117							WeightValue		FLOAT,
118							Weight			FLOAT,
119							Trend			DECIMAL(15,10),
120							ObjectiveType   INT
121							)
122	DECLARE @SortOrder FLOAT = CASE WHEN @IdType = 'R' THEN (SELECT SortOrder FROM p4s.Reports WHERE Id = @Id) 
123									WHEN @IdType = 'K' THEN (SELECT SortOrder FROM p4s.ReportRows WHERE Id = @Id)
124									ELSE 0
125								END
126
127	IF @OutputType = 1 BEGIN
128		SET @WholeYear = 1
129		SET @DateReal = @Date
130		SET @Date = DATEADD(DAY, -DATEPART(DAY, @date) + 1, @date)	--Zaokrouhelni na mesic
131		--SELECT @Date
132	END
133
134	SET @DateReal = @Date
135	SET @Date = DATEADD(DAY, -DATEPART(DAY, @date) + 1, @date)	--Zaokrouhelni na mesic
136
137	CREATE TABLE #table (Id VARCHAR(50), ParentId VARCHAR(50), Score DECIMAL(15,10), MemberId INT, Level INT, SortOrder FLOAT);
138	DECLARE @InputParent NVARCHAR(20)
139
140
141	-------------------------------------------------------------------------------------------------------------------------
142	--------------------	SESTAVOVANÍ STROMU	-----------------------------------------------------------------------------
143	--------------------	puvodni bsc.GetReportTree	---------------------------------------------------------------------
144	-------------------------------------------------------------------------------------------------------------------------
145	--Docasne reseni membera--
146	SET @memberId = (SELECT TOP 1 Id FROM @MemberIds)
147	--------------------------
148	--select @Date
149
150	IF @IdType = 'R' BEGIN
151		;WITH Reports AS
152		(
153			SELECT ParentReportId
154			FROM p4s.ReportRelations rr
155			WHERE ParentReportId = @Id	
156		UNION ALL
157			SELECT rr.ChildReportId
158			FROM  p4s.ReportRelations rr
159			INNER JOIN Reports r
160			ON r.ParentReportId = rr.ParentReportId
161		)
162		INSERT INTO #table (Id, ParentID, MemberId, SortOrder)
163			SELECT DISTINCT 'R'+CAST(h.ChildReportId AS VARCHAR) AS Id,
164							'R'+CAST(h.ParentReportId AS VARCHAR) AS ParentId,
165							NULL AS MemberId,
166							rep.SortOrder AS SortOrder
167			FROM p4s.ReportRelations h 
168			INNER JOIN Reports d 
169			ON h.ParentReportId = d.ParentReportId
170			JOIN p4s.Reports rep	--rep: Child Reporty
171			ON rep.Id = h.ChildReportId
172			JOIN p4s.Reports rep2	--rep2: Parent Reporty
173			ON rep2.Id = h.ParentReportId
174			WHERE	--Podminky validace musim kontrolovat jak pro child, tak pro parent. Jinak do stromu lezly reporty,
175					--kterym se do stromu nedostal rodic.
176					(@CheckValidity = 0
177					OR 
178					(@CheckValidity = 1 AND (rep.ValidFrom <= @DateReal AND (rep.ValidTo >= @DateReal OR rep.ValidTo IS NULL)))
179					OR
180					(@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)))
181					OR
182					(@CheckValidity = 3 AND (YEAR(rep.ValidFrom) <= YEAR(@DateReal) AND (YEAR(rep.ValidTo) >= YEAR(@DateReal) OR rep.ValidTo IS NULL)))
183					)
184					AND
185					(
186						@CheckValidity = 0
187						OR 
188						(@CheckValidity = 1 AND (rep2.ValidFrom <= @DateReal AND (rep2.ValidTo >= @DateReal OR rep2.ValidTo IS NULL)))
189						OR
190						(@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)))
191						OR
192						(@CheckValidity = 3 AND (YEAR(rep2.ValidFrom) <= YEAR(@DateReal) AND (YEAR(rep2.ValidTo) >= YEAR(@DateReal) OR rep2.ValidTo IS NULL)))
193						OR rep2.Id = @Id						
194					)
195				
196
197		UNION ALL
198			SELECT  'R'+CAST(re.Id AS VARCHAR) AS Id,
199					'R'+CAST(rr.ParentReportId AS VARCHAR) AS ParentId,
200					NULL AS MemberId,
201					re.SortOrder AS SortOrder
202			FROM p4s.Reports re
203			LEFT JOIN p4s.ReportRelations rr
204			ON rr.ChildReportId = re.Id
205			WHERE re.Id = @Id
206
207	END
208
209	--select * from #table
210
211	--smaze ze stromu reporty, ktery prosli podminkama rekurze, ale neprosli jejich rodice (prarodice) + vstupni objekt vzdy nechame
212	delete from #table where ParentId not in (select Id from #table) and Id <> @IdType+CAST(@Id AS varchar)
213
214	---test
215	--update t set t.MemberId = t2.MemberId from #table t join #table t2 on t.ParentId = t2.Id WHERE t.MemberId is null
216	--WHILE EXISTS (SELECT TOP 1 1 FROM #table WHERE MemberId IS NULL)
217	--	update t set t.MemberId = t2.MemberId from #table t join #table t2 on t.ParentId = t2.Id WHERE t.MemberId is null
218
219	IF(@IdType = 'K') BEGIN
220		SET @InputParent = 'R'+CAST((SELECT ReportId FROM p4s.ReportRows WHERE Id=@id) AS VARCHAR)
221		INSERT INTO #table (Id, ParentId, Score, MemberId, SortOrder)
222		VALUES ('K'+CAST(@Id AS VARCHAR),
223				@InputParent,
224				NULL,
225				(SELECT MemberId FROM p4s.ReportRows WHERE Id = @Id),
226				(SELECT SortOrder FROM p4s.ReportRows WHERE Id = @Id))
227	END
228
229	IF(@IdType = 'I') BEGIN
230		SET @InputParent = (SELECT ParentType FROM bsc.Actions WHERE Id=@Id)
231
232		IF @InputParent = '0'
233			SET @InputParent = 'R'+CAST((SELECT ParentObjectId FROM bsc.Actions WHERE Id=@Id) AS VARCHAR)
234
235		IF @InputParent = '1'
236			SET @InputParent = 'K'+CAST((SELECT ParentObjectId FROM bsc.Actions WHERE Id=@Id) AS VARCHAR)
237
238		INSERT INTO #table (Id, ParentId, Score, MemberId, SortOrder)
239		VALUES ('I'+CAST(@Id AS VARCHAR), @InputParent, NULL, @MemberId, 0)		
240	END
241
242	
243		
244	--Vložení KPIs	
245	INSERT INTO #table(Id, ParentID, MemberId, SortOrder)
246		SELECT 'K'+CAST(rr.Id AS VARCHAR) AS Id, CAST(t.Id AS VARCHAR) AS ParentID, rr.MemberId AS MemberId, rr.SortOrder AS SortOrder
247		FROM #table t
248		JOIN p4s.ReportRows rr
249		ON 'R'+CAST(rr.ReportId AS VARCHAR) = t.Id
250		WHERE
251			@CheckValidity = 0
252			OR
253			(@CheckValidity = 1 AND (rr.ValidFrom <= @DateReal AND (rr.ValidTo >= @DateReal OR rr.ValidTo IS NULL)))
254			OR
255			(@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)))
256			OR
257			(@CheckValidity = 3 AND (YEAR(rr.ValidFrom) <= YEAR(@DateReal) AND (YEAR(rr.ValidTo) >= YEAR(@DateReal) OR rr.ValidTo IS NULL)))
258	
259	UPDATE t
260	SET MemberId = (SELECT MemberId FROM p4s.ReportRows WHERE Id = RIGHT(t.Id, LEN(t.Id)-1))
261	FROM #table t
262	WHERE Id LIKE 'K%' AND MemberId IS NULL
263
264
265	--WHILE EXISTS (SELECT TOP 1 1 FROM #table WHERE MemberId is NULL) BEGIN
266		
267	--	UPDATE t
268	--	SET t.memberId = 1--ISNULL(t.MemberId, t2.MemberId) 
269	--	FROM #table t
270	--	JOIN #table t2
271	--	ON t.ParentId = t2.Id
272	--	where t.MemberId is null
273		
274	--	--print 'x'
275	--	--select memberId from #table
276	--END
277
278	IF (@IncludeTasks = 1 OR @OutputType = 1) BEGIN
279		--Pro @OutputType=1 je potreba at Tasky vstupuji do vypoctu skore, ale nezobrazují se ve stromu.
280		--Na konci pred vypisem se z #output tabulky smazou.
281
282		--Pro vložení opatreni musí splnovat: -Musí být k aktuálnímu datu validní (ValidFrom - ValidTo)
283		--										-Musí mít pod sebou aspoň jeden validní Task (overovani)
284		--										-Nebo parametr @CheckValidity nastaven na 0 - pak se datumy nekontrolují
285		--Vložení opatreni zarazenych pod reportama
286		INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
287			SELECT 'I'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
288			FROM #table t
289			JOIN bsc.Actions i
290			ON 'R'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 0
291			WHERE 
292				@CheckValidity = 0
293				OR
294				(@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal
295					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))
296				)
297				OR
298				(@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))
299					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)
300				)
301				OR
302				(@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL)))
303					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)
304				)
305
306		--Vložení opatreni zarazenych pod reportRows
307		INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
308			SELECT 'I'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
309			FROM #table t
310			JOIN bsc.Actions i
311			ON 'K'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 1
312			WHERE 
313				@CheckValidity = 0
314				OR
315				(@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal
316					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))
317				)
318				OR
319				(@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))
320					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)
321				)
322				OR
323				(@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL)))
324					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)
325				)
326
327		--Vložení tásků zarazenych pod reportama
328		INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
329			SELECT 'T'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
330			FROM #table t
331			JOIN bsc.Tasks i
332			ON 'R'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 0
333			WHERE 				
334				@CheckValidity = 0
335				OR 
336				(@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal))
337				OR
338				(@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)))
339				OR
340				(@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL))))
341
342		--Vložení tásků zarazenych pod reportRows
343		INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
344			SELECT 'T'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
345			FROM #table t
346			JOIN bsc.Tasks i
347			ON 'K'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 1
348			WHERE
349				@CheckValidity = 0
350				OR 
351				(@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal))
352				OR
353				(@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)))
354				OR
355				(@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL))))
356
357		--Vložení tásků zarazenych pod Iniciativama
358		INSERT INTO #table(Id, ParentId, MemberId, SortOrder)
359			SELECT 'T'+CAST(i.Id AS VARCHAR), CAST(t.Id AS varchar) AS ParentId, t.MemberId AS MemberId, 0 AS SortOrder
360			FROM #table t
361			JOIN bsc.Tasks i
362			ON 'I'+CAST(i.ParentObjectId AS VARCHAR) = t.Id AND i.ParentType = 2
363			WHERE
364				@CheckValidity = 0
365				OR 
366				(@CheckValidity = 1 AND (i.ValidFrom <= @DateReal AND i.ValidTo >= @DateReal))
367				OR
368				(@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)))
369				OR
370				(@CheckValidity = 3 AND ((YEAR(i.ValidFrom) <= YEAR(@DateReal) AND (YEAR(i.ValidTo) >= YEAR(@DateReal) OR i.ValidTo IS NULL))))
371	END
372
373	--Pokud se ptame primo na nejaky list (KPI nebo Task), vlozime ho samotný 
374	--Zatím verze pro tasky
375	IF @IdType = 'T' BEGIN
376		INSERT INTO #table (Id, ParentId, Score, MemberId, SortOrder)
377		VALUES ('T'+CAST(@Id AS VARCHAR), NULL, NULL, @ModelId, 0)
378	END
379
380	UPDATE #table SET Level = 1 WHERE Id = @IdType+CAST(@Id AS varchar)	
381	;WITH pc (Id, ParentId, Level)
382	 AS
383	(	-- Anchor member definition
384		SELECT t.Id, t.ParentId, t.Level			
385		FROM #table t
386		WHERE t.Level = 1
387		UNION ALL
388		-- Recursive member definition
389		SELECT t.Id, t.ParentId, pc.Level+1
390		FROM #table t
391		INNER JOIN pc 
392		ON pc.Id = t.ParentId
393	)
394	-- Statement that executes the CTE
395	UPDATE t
396	SET Level = pc.Level
397	FROM #table t
398	JOIN pc
399	ON t.Id = pc.Id
400
401
402
403
404	
405	--Pokud je parametr @TreeOnly - tady se končí a score se nebude pocitat
406	IF @TreeOnly = 1 BEGIN
407		IF (@IncludeTasks = 0) BEGIN
408			DELETE FROM #table
409			WHERE LEFT(Id,1) = 'T'
410			OR LEFT(Id,1) = 'I'
411		END
412		SELECT Id, ParentId, Score, SortOrder, MemberId FROM #table	
413		RETURN
414	END
415
416
417	-----------------------------------------------------------------------------------------------------------------------------------------
418	-----------------------------------------------------------------------------------------------------------------------------------------
419	-----------------------------------------------------------------------------------------------------------------------------------------
420	--	nova cast na vypocet score  ---------------------------------------------------------------------------------------------------------
421
422	--	do tabulky #leafs vlozim vsechny listy stromu. resp. Ids ktere nejsou ve sloupci parentId (nemaji deti)
423	CREATE TABLE #leafs (Id VARCHAR(50), ParentId VARCHAR(50), Score DECIMAL(15,10), MemberId INT)
424	INSERT INTO #leafs
425	SELECT Id, ParentId, Score, MemberId
426	FROM #table WHERE Id NOT IN (SELECT DISTINCT ParentId FROM #table WHERE ParentId IS NOT NULL)
427--select * from #table
428--select * from #leafs
429--SELECT Id, ParentId, MemberId, Level FROM #table ORDER BY Level DESC
430
431	-- kurzorem projedu pres vsechny listy, podle jeho typu zvolim vypocet score --
432	CREATE TABLE #Children (Id NVARCHAR(50))
433	DECLARE @c_Id		VARCHAR(50),
434			@c_MemberId	INT,
435			@c_ObjectType	VARCHAR(1),
436			@c_ObjectId		INT,
437			@c_ParentId		VARCHAR(50),
438			@c_Level		INT,
439			@c_PrevLevel	INT = (SELECT MAX(Level) FROM #table)
440	DECLARE c_leafs CURSOR FAST_FORWARD LOCAL
441	--FOR	SELECT Id, MemberId FROM #leafs
442	FOR SELECT Id, ParentId, MemberId, Level FROM #table ORDER BY Level DESC
443	OPEN c_leafs
444	FETCH NEXT FROM c_leafs INTO @c_Id, @c_ParentID, @c_MemberId, @c_level
445
446	WHILE @@FETCH_STATUS=0 BEGIN
447		SET @c_ObjectType = LEFT(@c_Id,1)
448		SET @c_ObjectId	  = CAST(RIGHT(@c_Id,LEN(@c_Id)-1) AS INT)
449		DELETE FROM #Children
450		INSERT INTO #Children
451		SELECT Id FROM #table WHERE ParentId = @c_Id
452
453		--SELECT * FROM #Children
454
455		IF (SELECT COUNT (Id) FROM #Children) > 0 BEGIN
456--SELECT @c_Id, * FROM #Output
457
458			DECLARE c_output CURSOR FAST_FORWARD LOCAL
459			FOR
460			SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
461			UNION ALL
462			SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
463			AND DATEPART(MONTH, FirstDate) = 12
464			ORDER BY FirstDate DESC
465			OPEN c_output
466			FETCH NEXT FROM c_output INTO @c_timeID, @c_time
467			WHILE @@FETCH_STATUS=0 BEGIN
468					IF @WholeYear = 1 BEGIN
469						INSERT INTO #Output(object,timeId, time, score, timeAggregation)
470						VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
471					END
472					ELSE IF (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
473						INSERT INTO #Output(object,timeId, time, score, timeAggregation)
474						VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
475					END
476					IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
477						INSERT INTO #Output(object,timeId, time, score, timeAggregation)
478						VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
479					END
480					ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
481						INSERT INTO #Output(object,timeId, time, score, timeAggregation)
482						VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
483					END
484
485
486					FETCH NEXT FROM c_output INTO @c_timeID, @c_time
487			END
488			CLOSE c_output
489			DEALLOCATE c_output
490
491			IF @IdType = 'R'
492				UPDATE o
493				SET Weight = (SELECT Weight FROM p4s.Reports WHERE Id = @Id)
494				FROM #Output o
495				WHERE o.object = @c_Id
496
497			
498			--SELECT * FROM #OutputFinal			
499			UPDATE o
500			SET score = d.Score,
501				Weight = d.Weight
502			FROM #Output o
503			JOIN
504			(
505				SELECT d.TimeId,
506						SUM( (t.Score/CAST(ISNULL(d.w_sum,1) AS float))*t.weight ) AS Score,
507						MAX(t.Weight) AS Weight
508				FROM #Output t
509				JOIN 
510				(
511					SELECT TimeId, SUM(Weight) AS w_sum
512					FROM #Output
513					WHERE object IN (SELECT Id FROM #Children)
514					AND score IS NOT NULL AND Weight <> 0 AND timeAggregation=0
515					GROUP BY TimeId
516				) d
517				ON d.TimeId = t.TimeId
518				WHERE object IN (SELECT Id FROM #Children)
519				AND score IS NOT NULL AND Weight <> 0 AND timeAggregation=0
520				GROUP BY d.TimeId
521			) d
522			ON d.TimeId = o.timeId
523			WHERE o.object = @c_Id  AND timeAggregation=0
524--select * from #Output where timeId = 2018030001
525
526			UPDATE o
527			SET score = d.Score,
528				Weight = d.Weight
529			FROM #Output o
530			JOIN
531			(
532				SELECT d.TimeId,
533						SUM( (t.Score/CAST(ISNULL(d.w_sum,1) AS float))*t.weight ) AS Score,
534						MAX(t.Weight) AS Weight
535				FROM #Output t
536				JOIN 
537				(
538					SELECT TimeId, SUM(Weight) AS w_sum
539					FROM #Output
540					WHERE object IN (SELECT Id FROM #Children)
541					AND score IS NOT NULL AND Weight <> 0 AND timeAggregation=1
542					GROUP BY TimeId
543				) d
544				ON d.TimeId = t.TimeId
545				WHERE object IN (SELECT Id FROM #Children)
546				AND score IS NOT NULL AND Weight <> 0 AND timeAggregation=1
547				GROUP BY d.TimeId
548			) d
549			ON d.TimeId = o.timeId
550			WHERE o.object = @c_Id  AND timeAggregation=1
551
552			DELETE FROM #Output WHERE object IN (SELECT Id FROM #Children)
553		END
554
555
556		IF @c_ObjectType = 'R' BEGIN
557			--Kdyz je list report, neni z ceho vypocitat score - nechavam NULL
558			IF (SELECT Count(*) FROM #Output) = 0 BEGIN
559				DECLARE c_output CURSOR FAST_FORWARD LOCAL
560				FOR
561				SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
562				UNION ALL
563				SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
564				AND DATEPART(MONTH, FirstDate) = 12
565				ORDER BY FirstDate DESC
566				OPEN c_output
567				FETCH NEXT FROM c_output INTO @c_timeID, @c_time
568				WHILE @@FETCH_STATUS=0 BEGIN
569							--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
570								INSERT INTO #Output(object,timeId, time, score, timeAggregation)
571								VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
572
573							IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
574								--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)
575									INSERT INTO #Output(object,timeId, time, score, timeAggregation, BiggerBetter, Weight, RedIfZero, redThresholdValue)
576									SELECT object, timeId, time, NULL, 1, biggerBetter, Weight, redifzero, redThresholdValue FROM #Output where time=@c_time AND object = @c_id
577							END
578							ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
579								--IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE time = @c_time AND timeAggregation=1 AND object = @c_Id)
580									INSERT INTO #Output(object,timeId, time, score, timeAggregation)
581									VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
582							END
583
584							IF @WholeYear = 0
585								DELETE FROM #Output WHERE ((month(time) <> month(@Date) AND month(time) <> month(@Date)-1) OR year(time) <> year(@date))
586
587							FETCH NEXT FROM c_output INTO @c_timeID, @c_time
588				END
589
590				--select * from #Output
591
592				CLOSE c_output
593				DEALLOCATE c_output
594
595				UPDATE o
596				SET Weight = (SELECT Weight FROM p4s.Reports WHERE Id = @Id),
597					ObjectiveType = (SELECT ObjectiveType FROM p4s.Reports WHERE Id = @Id)
598				FROM #Output o
599				WHERE o.object = @c_Id
600			END
601			PRINT 'Report(R) jako list'
602			--SELECT * FROM #Output
603			--RETURN
604		END
605		ELSE IF @c_ObjectType = 'I' BEGIN
606			--Kdyz je list akce(iniciativa), neni z ceho vypocitat score - nechavam NULL
607			IF (SELECT Count(*) FROM #Output) = 0 BEGIN
608				DECLARE c_output CURSOR FAST_FORWARD LOCAL
609				FOR
610				SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
611UNION ALL
612SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
613AND DATEPART(MONTH, FirstDate) = 12
614
615				ORDER BY FirstDate DESC
616				OPEN c_output
617				FETCH NEXT FROM c_output INTO @c_timeID, @c_time
618				WHILE @@FETCH_STATUS=0 BEGIN
619							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
620								INSERT INTO #Output(object,timeId, time, score, timeAggregation)
621								VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
622								INSERT INTO #Output(object,timeId, time, score, timeAggregation)
623								VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
624							END
625							ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
626								IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE time = @c_time AND timeAggregation=1 AND object = @c_Id) BEGIN
627									INSERT INTO #Output(object,timeId, time, score, timeAggregation)
628									VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
629									INSERT INTO #Output(object,timeId, time, score, timeAggregation)
630									VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
631								END
632							END
633
634							IF @WholeYear = 0
635								DELETE FROM #Output WHERE ((month(time) <> month(@Date) AND month(time) <> month(@Date)-1) OR year(time) <> year(@date))
636
637							FETCH NEXT FROM c_output INTO @c_timeID, @c_time
638				END
639				CLOSE c_output
640				DEALLOCATE c_output
641			END
642			PRINT 'Akce(I) jako list'
643			--select * from #Output
644		END
645		ELSE IF @c_ObjectType = 'T' BEGIN
646			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')
647			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')
648			DECLARE @ActiveFrom	 DATE = (SELECT ActiveFrom	 FROM bsc.Tasks WHERE Id = @c_ObjectId)	--Pouzito i pozdeji pro filtraci neaktivnich tasku
649			DECLARE @ActiveFromRounded DATE = DATEADD(DAY, -DATEPART(DAY, @ActiveFrom) + 1, @ActiveFrom)	
650			DECLARE @ActiveTo	 DATE = (SELECT ActiveTo	 FROM bsc.Tasks WHERE Id = @c_ObjectId)
651			DECLARE @ActiveTORounded DATE = DATEADD(DAY, -DATEPART(DAY, @ActiveTo) + 1, @ActiveTo)
652			DECLARE @ActiveFromSTRING	NVARCHAR(20) = CAST(@ActiveFrom AS VARCHAR)
653			DECLARE @ActiveToSTRING		NVARCHAR(20) = CAST(@ActiveTo AS VARCHAR)
654			DECLARE @ActiveFromSTRING2	NVARCHAR(20) = CAST(DATEADD(MONTH,-1,@ActiveFrom) AS VARCHAR)
655			SET @Query = '
656			DECLARE c_time CURSOR FAST_FORWARD
657			FOR
658			SELECT DISTINCT * FROM 
659			(
660			SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = '+CAST(@TimeMemberListId AS VARCHAR)+'
661								AND (FirstDate >= '''+@ActiveFromSTRING+''' OR (MONTH(FirstDate)=MONTH('''+@ActiveFromSTRING+''') AND YEAR(FirstDate)=YEAR('''+@ActiveFromSTRING+''')))
662								AND (FirstDate <= '''+@ActiveToSTRING+''' OR (MONTH(FirstDate)=MONTH('''+@ActiveToSTRING+''') AND YEAR(FirstDate)=YEAR('''+@ActiveToSTRING+''')))
663			UNION ALL
664			SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = '+CAST(@TimeMemberListId AS VARCHAR)+' AND MONTH(FirstDate)=MONTH('''+@ActiveFromSTRING2+''') AND YEAR(FirstDate)=YEAR('''+@ActiveFromSTRING2+''')
665			UNION ALL
666			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)+''')
667			) d
668			ORDER BY FirstDate DESC'
669			--print @Query
670			EXEC(@Query)
671			OPEN c_time
672			FETCH NEXT FROM c_time INTO
673			@c_timeId, @c_time
674
675			--SELECT Id FROM @MemberIds UNION SELECT @memberId
676
677			WHILE @@FETCH_STATUS=0 BEGIN
678					--print CAST(@c_time AS VARCHAR) + ' -- ' + @c_ObjectType + CAST(@c_ObjectId AS VARCHAR)
679					
680					IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
681						INSERT INTO #Output(object,timeId, time, score, timeAggregation)
682						VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
683						INSERT INTO #Output(object,timeId, time, score, timeAggregation)
684						VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
685					END
686					ELSE IF @WholeYear = 0 AND @TimeAggregation=1 BEGIN --AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time))
687						INSERT INTO #Output(object,timeId, time, score, timeAggregation)
688						VALUES (@c_Id,@c_timeId, @c_time, NULL, 1)
689						INSERT INTO #Output(object,timeId, time, score, timeAggregation)
690						VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
691					END
692					
693
694				FETCH NEXT FROM c_time INTO
695				@c_timeId, @c_time
696			END
697			--select @Date
698			--select * from #Output
699			CLOSE c_time
700			DEALLOCATE c_time
701
702			UPDATE o
703				SET redThresholdStatus = (SELECT StatusRedThreshold FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
704					redThresholdValue  = (SELECT ValueRedThreshold FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
705					BiggerBetter = (SELECT ValueBiggerBetter FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
706					BiggerBetterStatus = (SELECT StatusBiggerBetter FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
707					WeightStatus = (SELECT StatusWeight FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
708					WeightValue = (SELECT ValueWeight FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)),
709					Weight = (SELECT Weight FROM bsc.Tasks WHERE Id = CAST(RIGHT(o.object,LEN(o.object)-1) AS INT))
710			FROM #Output o
711			WHERE o.object = @c_Id
712		--select * from #Output
713			UPDATE o
714			SET BaseValue = f.ActualValue,
715				GoalValue = f.PlanValue,
716				BaseStatus = f.ActualStatus,
717				GoalStatus = f.PlanStatus
718			FROM #Output o
719			CROSS APPLY bsc.f_GetLastTaskValues2(o.time, CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)) f
720			WHERE o.object = @c_Id
721
722			UPDATE o
723			SET 
724				redThresholdValueNumber = 
725				CASE WHEN CAST(o.BiggerBetter AS INT) = 1 THEN 
726					CASE WHEN o.GoalValue > 0 THEN 
727						(((1-redThresholdValue)*(-o.GoalValue))+o.GoalValue)
728					ELSE
729						CASE WHEN o.GoalValue < 0 THEN
730							((1-redThresholdValue)*(o.GoalValue)+o.GoalValue)	
731						ELSE (SELECT ParameterValue FROM amb.Plan4SParameters where ParameterName = 'bsc_RedFlagValueTaskValue')*@TargetValue
732						END
733					END
734				ELSE 
735					CASE WHEN o.GoalValue > 0 THEN 
736						((1-redThresholdValue)*(o.GoalValue)+o.GoalValue)
737					ELSE
738						CASE WHEN o.GoalValue < 0 THEN
739							((1-redThresholdValue)*(-o.GoalValue)+o.GoalValue)	
740						ELSE (SELECT ParameterValue FROM amb.Plan4SParameters where ParameterName = 'bsc_RedFlagValueTaskValue')*@TargetValue
741						END
742					END
743				END,
744				redThresholdStatusNumber = 
745				CASE WHEN CAST(o.BiggerBetterStatus AS INT) = 1 THEN 
746					CASE WHEN o.GoalStatus > 0 THEN 
747						(((1-redThresholdStatus)*(-o.GoalStatus))+o.GoalStatus)
748					ELSE
749						CASE WHEN o.GoalStatus < 0 THEN
750							((1-redThresholdStatus)*(o.GoalStatus)+o.GoalStatus)	
751						ELSE (SELECT ParameterValue FROM amb.Plan4SParameters where ParameterName = 'bsc_RedFlagValueTaskStatus')*@TargetStatus
752						END
753					END
754				ELSE 
755					CASE WHEN o.GoalStatus > 0 THEN 
756						((1-redThresholdStatus)*(o.GoalStatus)+o.GoalStatus)
757					ELSE
758						CASE WHEN o.GoalStatus < 0 THEN
759							((1-redThresholdStatus)*(-o.GoalStatus)+o.GoalStatus)	
760						ELSE (SELECT ParameterValue FROM amb.Plan4SParameters where ParameterName = 'bsc_RedFlagValueTaskStatus')*@TargetStatus
761						END
762					END
763				END
764			FROM #Output o
765			CROSS APPLY bsc.f_GetLastTaskValues2(o.time, CAST(RIGHT(o.object,LEN(o.object)-1) AS INT)) f
766			WHERE o.object = @c_Id
767
768
769			UPDATE o
770			SET ValueScore = (BaseValue-(2*redThresholdValueNumber) + GoalValue) / CAST(((3*GoalValue)-(3*redThresholdValueNumber)) AS FLOAT) * 15, 
771				StatusScore = (BaseStatus-(2*redThresholdStatusNumber) + GoalStatus) / CAST(((3*GoalStatus)-(3*redThresholdStatusNumber)) AS FLOAT) * 15
772			FROM #Output o
773			WHERE o.object = @c_Id
774
775
776			--select * from #Output
777			UPDATE o
778			SET score = ((ISNULL(ValueScore,0)/(WeightStatus+WeightValue))*WeightValue) + ((ISNULL(StatusScore,0)/(WeightStatus+WeightValue))*WeightStatus)
779			FROM #Output o
780			WHERE o.object = @c_Id
781
782			--Když je ValueScore i StatusScore NULL, vysledne je taky NULL (z vypoctu nad je 0.0000)
783			UPDATE o 
784			SET score = NULL
785			FROM #Output o
786			WHERE o.object = @c_Id
787			AND ValueScore IS NULL AND StatusScore IS NULL
788
789			--Vymazání hodnot pokud je ukol mimo rozsah activeFrom - activeTo
790--ActiveFrom a ActiveTo zaokrouhlit na měsic (prvni den mesice) - pak az overovat. Obdobne k @checkValidity = 2
791			UPDATE o
792			SET score = null, ValueScore = null, StatusScore = null, BaseValue = null, GoalValue = null, BaseStatus = null, GoalStatus = null, redThresholdStatusNumber = null, redThresholdValueNumber = null
793			FROM #Output o 
794			WHERE (time < @ActiveFromRounded
795					OR 
796				   time > @ActiveTORounded)
797			AND object = @c_Id
798
799			--select * from #Output
800																	 
801		END															 
802		ELSE IF @c_ObjectType = 'K' BEGIN
803IF EXISTS (SELECT TOP 1 1 FROM #Output WHERE object = @c_Id)
804	DELETE FROM #Output WHERE object = @c_Id
805				DELETE FROM @ReportIndicators
806				INSERT INTO @ReportIndicators SELECT IndicatorId FROM p4s.ReportRows WHERE Id = @c_ObjectId
807				DECLARE c_output CURSOR FAST_FORWARD LOCAL
808				FOR
809				SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
810				UNION ALL
811SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
812AND DATEPART(MONTH, FirstDate) = 12
813
814				ORDER BY FirstDate aSC
815				OPEN c_output
816				FETCH NEXT FROM c_output INTO @c_timeID, @c_time
817				WHILE @@FETCH_STATUS=0 BEGIN
818						INSERT INTO #Output(Object,Time,TimeId,RedIfZero,timeAggregation,redThresholdValue,BiggerBetter,Weight,redThresholdValueNumber,Score,BaseValue,GoalValue)
819						SELECT  @c_ObjectType+CAST(@c_ObjectId AS VARCHAR) Object,
820								@c_time AS Time,
821								TimeId,						
822								RedIfZero,
823								0 AS timeAggregation,
824								redThresholdValue,
825								BiggerBetter,
826								Weight,
827								CASE WHEN CAST(BiggerBetter AS INT) = 1 THEN 
828										CASE WHEN piv.[20] > 0 THEN 
829											(((1-redThresholdValue)*(-piv.[20]))+piv.[20])
830										ELSE
831											CASE WHEN piv.[20] < 0 THEN
832												((1-redThresholdValue)*(piv.[20])+piv.[20])	
833											ELSE RedIfZero
834											END
835										END
836									ELSE 
837										CASE WHEN piv.[20] > 0 THEN 
838											((1-redThresholdValue)*(piv.[20])+piv.[20])
839										ELSE
840											CASE WHEN piv.[20] < 0 THEN
841												((1-redThresholdValue)*(-piv.[20])+piv.[20])	
842											ELSE RedIfZero
843											END
844										END
845								END AS redThresholdValueNumber,
846								NULL AS Score,
847								piv.[10] AS BaseValue,
848								piv.[20] AS GoalValue
849						FROM 
850						(
851							SELECT
852								TimeId AS TimeID,
853								f.IndicatorId,
854								Value AS Value,
855								f.PlanId,
856								rr.RedIfZero AS RedIfZero,
857								rr.redThreshold AS redThresholdValue,
858								CAST(rr.BiggerBetter AS INT) AS BiggerBetter,
859								rr.Weight AS Weight,
860								NULL AS redThresholdValueNumber,
861								NULL AS Score
862							FROM p4s.Facts f
863							JOIN p4s.ReportRows rr
864							ON f.IndicatorId = rr.IndicatorId
865							WHERE rr.Id = @c_ObjectId
866							AND f.ModelId = @ModelId
867							AND f.IndicatorId IN (SELECT IndicatorId FROM @ReportIndicators)
868							AND TimeId = @c_timeId
869							AND Value IS NOT NULL
870							AND f.DimMemberId =@c_MemberId--IN (SELECT Id FROM @MemberIds UNION SELECT @memberId)
871							--AND rr.Weight <> 0
872						) src
873						pivot
874						(
875						  sum(Value)
876						  for PlanId in ([10], [20])
877						) piv;
878
879
880						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
881							INSERT INTO #Output(object,timeId, time, score, timeAggregation)
882							VALUES (@c_Id,@c_timeId, @c_time, NULL, 0)
883
884						IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
885							--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)
886								INSERT INTO #Output(object,timeId, time, score, timeAggregation, BiggerBetter, Weight, RedIfZero, redThresholdValue)
887								SELECT object, timeId, time, NULL, 1, biggerBetter, Weight, redifzero, redThresholdValue FROM #Output where time=@c_time AND object = @c_id
888						END
889						ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND (MONTH(@Date) = MONTH(@c_time) OR MONTH(@Date)-1 = MONTH(@c_time)) BEGIN
890							INSERT INTO #Output(object,timeId, time, score, timeAggregation, BiggerBetter, Weight, RedIfZero, redThresholdValue)
891								SELECT object, timeId, time, NULL, 1, biggerBetter, Weight, redifzero, redThresholdValue FROM #Output where time=@c_time AND object = @c_id
892						END
893						IF @WholeYear = 0
894							DELETE FROM #Output WHERE ((month(time) <> month(@Date) AND month(time) <> month(@Date)-1) OR year(time) <> year(@date))
895						
896						
897						--Vycisleni hodnot pocitaneho ukazatele. Probehne 4x. Pro agregaci a pro zvolené období.
898						--A pro skutečnost a plán.
899						--Je potreba tady vymyslet nejakou optimalizaci, at to netrva tak dlouho
900						IF (SELECT TOP 1 TypeId FROM p4s.Indicators WHERE Id IN (SELECT IndicatorId FROM @ReportIndicators)) = 2 BEGIN
901							DECLARE @IndicatorID INT = (SELECT IndicatorId FROM @ReportIndicators)
902							DECLARE @ValueTypeId10 INT = (SELECT CAST(LEFT(@c_timeId,4)+'010' AS INT))
903							DECLARE @ValueTypeId20 INT = (SELECT CAST(LEFT(@c_timeId,4)+'020' AS INT))
904							DECLARE @Time p4s.TIds
905							DELETE FROM @Time
906							INSERT INTO @Time VALUES (@c_timeId)
907							DECLARE @Dim p4s.TIds
908							DELETE FROM @Dim
909							INSERT INTO @Dim VALUES (@c_MemberId)
910							CREATE TABLE #vysledek (ModelId INT, IndicatorId INT, TimeId INT, ValueTypeID INT, PlanId INT, DimeMemberId INT, SubIndicatorId INT, Value FLOAT)
911
912							INSERT INTO #vysledek
913							exec p4s.IndicatorCalculationResolve3 @ModelId=@ModelId, @IndicatorId=@IndicatorID, @TimeFilterSet = @Time, @DimFilterSet = @Dim, @DimLevel = 1, @TimeLevel = 1, @ValueTypeId = @ValueTypeId10
914
915							UPDATE o
916							SET BaseValue = v.Value
917							FROM #Output o
918							JOIN #vysledek v
919							ON o.timeId = v.TimeId
920							AND o.timeAggregation = 0
921
922							DROP TABLE #vysledek
923
924
925							CREATE TABLE #vysledek2 (ModelId INT, IndicatorId INT, TimeId INT, ValueTypeID INT, PlanId INT, DimeMemberId INT, SubIndicatorId INT, Value FLOAT)
926
927							INSERT INTO #vysledek2
928							exec p4s.IndicatorCalculationResolve3 @ModelId=@ModelId, @IndicatorId=@IndicatorID, @TimeFilterSet = @Time, @DimFilterSet = @Dim, @DimLevel = 1, @TimeLevel = 1, @ValueTypeId = @ValueTypeId20
929
930							UPDATE o
931							SET GoalValue = v.Value
932							FROM #Output o
933							JOIN #vysledek2 v
934							ON o.timeId = v.TimeId
935							AND o.timeAggregation = 0
936
937							DROP TABLE #vysledek2
938
939
940							------AGG------
941							DECLARE @TimeAgg p4s.TIds
942							INSERT INTO @TimeAgg VALUES (@c_timeId)
943
944							CREATE TABLE #vysledekAGG (Value FLOAT)
945							INSERT INTO #vysledekAGG
946							exec p4s.IndicatorCalculationResolve3 @ModelId=@ModelId, @IndicatorId=@IndicatorID, @TimeFilterSet = @TimeAgg, @DimFilterSet = @Dim, @DimLevel = 1, @TimeLevel = 1, @ValueTypeId = @ValueTypeId10, @SolveType=2
947							
948
949							UPDATE o
950							SET BaseValue = v.Value
951							FROM #Output o
952							JOIN #vysledekAGG v
953							ON o.timeId = @c_timeId
954							WHERE o.timeAggregation = 1
955
956
957							CREATE TABLE #vysledekAGG2 (Value FLOAT)
958							INSERT INTO #vysledekAGG2
959							exec p4s.IndicatorCalculationResolve3 @ModelId=@ModelId, @IndicatorId=@IndicatorID, @TimeFilterSet = @TimeAgg, @DimFilterSet = @Dim, @DimLevel = 1, @TimeLevel = 1, @ValueTypeId = @ValueTypeId20, @SolveType=2
960							
961							UPDATE o
962							SET GoalValue = v.Value
963							FROM #Output o
964							JOIN #vysledekAGG2 v
965							ON o.timeId = @c_timeId
966							WHERE o.timeAggregation = 1
967
968							DROP TABLE #vysledekAGG
969							DROP TABLE #vysledekAGG2
970
971						END
972
973
974						FETCH NEXT FROM c_output INTO @c_timeID, @c_time
975				END
976				CLOSE c_output
977				DEALLOCATE c_output
978				
979				UPDATE o
980				SET 
981					RedIfZero = (SELECT RedIfZero FROM p4s.ReportRows WHERE Id = @c_ObjectId),
982					BiggerBetter = (SELECT BiggerBetter FROM p4s.ReportRows WHERE Id = @c_ObjectId),
983					Weight = (SELECT Weight FROM p4s.ReportRows WHERE Id = @c_ObjectId),
984					redThresholdValue = (SELECT RedThreshold FROM p4s.ReportRows WHERE Id = @c_ObjectId)
985				FROM #Output o
986				WHERE
987				o.object = @c_Id
988
989				--select * from #Output
990
991				--Pro aditivni ukazatele vyscitam hodnoty za predchozi obdobi. Pro semiaditivni nechavam hodnoty stejne
992				--Pro agregaci typu vypocet se hodnoty plní výše, procedurou IndicatorCalculationResolve3.
993				IF ((SELECT AggregationTypeId FROM p4s.Indicators WHERE Id = (SELECT IndicatorId FROM @ReportIndicators)) = 1 
994					AND
995					(SELECT TypeId FROM p4s.Indicators WHERE Id = (SELECT IndicatorId FROM @ReportIndicators)) <> 2) BEGIN
996
997					UPDATE t
998					SET BaseValue = (SELECT SUM(k.BaseValue) FROM #Output k WHERE k.Time <= t.Time AND k.object = @c_Id AND timeAggregation = 0)						
999					FROM #Output t
1000					WHERE timeAggregation = 1
1001					AND t.object = @c_Id
1002					AND EXISTS (SELECT TOP 1 1 FROM #Output WHERE time=t.time AND timeAggregation=0 AND BaseValue IS NOT NULL AND object=@c_Id)					
1003					
1004					UPDATE t
1005					SET GoalValue = (SELECT SUM(k.GoalValue) FROM #Output k WHERE k.Time <= t.Time AND k.object = @c_Id AND timeAggregation = 0)
1006					FROM #Output t
1007					WHERE timeAggregation = 1
1008					AND t.object = @c_Id
1009					AND EXISTS (SELECT TOP 1 1 FROM #Output WHERE time=t.time AND timeAggregation=0 AND GoalValue IS NOT NULL AND object=@c_Id)
1010				END
1011				ELSE IF ((SELECT AggregationTypeId FROM p4s.Indicators WHERE Id = (SELECT IndicatorId FROM @ReportIndicators)) IN (2)
1012						AND
1013						(SELECT TypeId FROM p4s.Indicators WHERE Id = (SELECT IndicatorId FROM @ReportIndicators)) <> 2)
1014					UPDATE t
1015					SET BaseValue = (SELECT BaseValue FROM #Output k WHERE k.object = t.object AND timeAggregation = 0 AND k.time = t.time),
1016						GoalValue = (SELECT GoalValue FROM #Output k WHERE k.object = t.object AND timeAggregation = 0 AND k.time = t.time)
1017					FROM #Output t
1018					WHERE timeAggregation = 1
1019					AND t.object = @c_Id
1020
1021				UPDATE t
1022				SET redThresholdValueNumber = CASE WHEN CAST(BiggerBetter AS INT) = 1 THEN 
1023											CASE WHEN t.GoalValue > 0 THEN 
1024												(((1-redThresholdValue)*(-t.GoalValue))+t.GoalValue)
1025											ELSE
1026												CASE WHEN t.GoalValue < 0 THEN
1027													((1-redThresholdValue)*(t.GoalValue)+t.GoalValue)	
1028												ELSE RedIfZero
1029												END
1030											END
1031										ELSE 
1032											CASE WHEN t.GoalValue > 0 THEN 
1033												((1-redThresholdValue)*(t.GoalValue)+t.GoalValue)
1034											ELSE
1035												CASE WHEN GoalValue < 0 THEN
1036													((1-redThresholdValue)*(-GoalValue)+t.GoalValue)	
1037												ELSE RedIfZero
1038												END
1039											END
1040									END
1041				FROM #Output t
1042				--WHERE timeAggregation = 1
1043				WHERE t.object = @c_Id
1044
1045				UPDATE t
1046				SET t.Weight = (SELECT Weight FROM p4s.ReportRows WHERE Id = @c_ObjectId)
1047				FROM #Output t
1048				WHERE t.object = @c_Id
1049
1050				UPDATE t
1051				SET Score = (BaseValue-(2*redThresholdValueNumber) + GoalValue) / CAST(((3*GoalValue)-(3*redThresholdValueNumber)) AS float) * 15
1052				FROM #Output t
1053				WHERE t.object = @c_id
1054				
1055--SELECT * FROM #Output
1056		END
1057		
1058		
1059	--select * from #Output	
1060		
1061
1062		IF @OutputType = 1	BEGIN--Tady musi byt vypocet agregovaneho score! a ne score za daný měsíc.
1063			
1064			UPDATE t
1065			SET t.score = o.score
1066			FROM #table t
1067			JOIN #Output o
1068			ON o.object = t.Id
1069			WHERE o.time = @Date
1070			AND o.timeAggregation = 1
1071			
1072			--SELECT * from #Output
1073		END
1074	--select @c_Level, @c_Id, * from #Output
1075
1076		SET @c_PrevLevel = @c_Level
1077		FETCH NEXT FROM c_leafs INTO @c_Id, @c_ParentId ,@c_MemberId, @c_level
1078	END
1079	
1080
1081	IF @IdType = 'I' BEGIN
1082		UPDATE o
1083		SET Weight = (SELECT Weight FROM bsc.Actions WHERE Id = @Id)
1084		FROM #Output o
1085		--DELETE FROM #Output WHERE timeAggregation = 0	-- Po domluvě, že Tasky i Akce budou vždy vracet obe casove agregace
1086	END
1087
1088	IF @IdType = 'R' BEGIN
1089		UPDATE o
1090		SET ObjectiveType = (SELECT ObjectiveType FROM p4s.Reports WHERE Id = @Id)
1091		FROM #Output o 
1092	END
1093
1094	IF @OutputType = 1 BEGIN
1095		IF (@IncludeTasks = 0) BEGIN
1096			DELETE FROM #table
1097			WHERE LEFT(Id,1) = 'T'
1098			OR LEFT(Id,1) = 'I'
1099		END
1100		SELECT Id, ParentId, Score, SortOrder FROM #table
1101	END
1102
1103	--SELECT * from #Output
1104
1105	IF @OutputType = 2 BEGIN
1106		--Pokud mame vic dat - normalizujeme score v case, jinak muzem vypisovat------------
1107		------------------------------------------------------------------------------------
1108		IF (SELECT COUNT(DISTINCT Object) FROM #Output) > 1  OR ((SELECT COUNT(*) FROM #Output) > 26 AND @IdType <> 'T')
1109		 BEGIN
1110			--SELECT * from #Output
1111			DECLARE c_output CURSOR FAST_FORWARD LOCAL
1112			FOR
1113			SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(@date,GETDATE()))
1114			UNION ALL
1115			SELECT Id, FirstDate FROM p4s.TimeMembers WHERE MemberListId = @TimeMemberListId AND YEAR(FirstDate)=YEAR(ISNULL(DATEADD(year, -1, @date),GETDATE()))
1116			AND DATEPART(MONTH, FirstDate) = 12
1117			ORDER BY FirstDate DESC
1118			OPEN c_output
1119			FETCH NEXT FROM c_output INTO @c_timeID, @c_time
1120			WHILE @@FETCH_STATUS=0 BEGIN
1121					IF @WholeYear = 1 BEGIN
1122						INSERT INTO #OutputFinal(object,timeId, time, score, timeAggregation)
1123						VALUES (@IdType+CAST(@Id AS VARCHAR),@c_timeId, @c_time, NULL, 0)
1124					END
1125					ELSE IF MONTH(@Date) = MONTH(@c_time) BEGIN
1126						INSERT INTO #OutputFinal(object,timeId, time, score, timeAggregation)
1127						VALUES (@IdType+CAST(@Id AS VARCHAR),@c_timeId, @c_time, NULL, 0)
1128					END
1129					IF @WholeYear = 1 AND @TimeAggregation=1 BEGIN
1130						INSERT INTO #OutputFinal(object,timeId, time, score, timeAggregation)
1131						VALUES (@IdType+CAST(@Id AS VARCHAR),@c_timeId, @c_time, NULL, 1)
1132					END
1133					ELSE IF @WholeYear = 0 AND @TimeAggregation=1 AND MONTH(@Date) = MONTH(@c_time) BEGIN
1134						INSERT INTO #OutputFinal(object,timeId, time, score, timeAggregation)
1135						VALUES (@IdType+CAST(@Id AS VARCHAR),@c_timeId, @c_time, NULL, 1)
1136					END
1137
1138
1139					FETCH NEXT FROM c_output INTO @c_timeID, @c_time
1140			END
1141			CLOSE c_output
1142			DEALLOCATE c_output
1143
1144			IF @IdType = 'R'
1145				UPDATE o
1146				SET Weight = (SELECT Weight FROM p4s.Reports WHERE Id = @Id),
1147					ObjectiveType = (SELECT ObjectiveType FROM p4s.Reports WHERE Id = @Id)
1148				FROM #OutputFinal o
1149
1150			
1151			--SELECT * FROM #Output
1152			
1153			--Vypocet score se zde dela pro dve ruzne casove agregace zvlášť
1154			--pro NEagregovana data
1155			UPDATE o
1156			SET score = d.Score,
1157				Weight = d.Weight
1158			FROM #OutputFinal o
1159			JOIN
1160			(
1161				SELECT d.TimeId,
1162						SUM( (t.Score/CAST(ISNULL(d.w_sum,1) AS float))*t.weight ) AS Score,
1163						MAX(t.Weight) AS Weight
1164				FROM #Output t
1165				JOIN 
1166				(
1167					SELECT TimeId, SUM(Weight) AS w_sum
1168					FROM #Output
1169					WHERE score IS NOT NULL AND Weight <> 0 AND timeAggregation=0
1170					GROUP BY TimeId
1171				) d
1172				ON d.TimeId = t.TimeId
1173				WHERE score IS NOT NULL AND Weight <> 0 AND timeAggregation=0
1174				GROUP BY d.TimeId
1175			) d
1176			ON d.TimeId = o.timeId
1177			WHERE timeAggregation=0
1178
1179			--pro agregovana data
1180			UPDATE o
1181			SET score = d.Score,
1182				Weight = d.Weight
1183			FROM #OutputFinal o
1184			JOIN
1185			(
1186				SELECT d.TimeId,
1187						SUM( (t.Score/CAST(ISNULL(d.w_sum,1) AS float))*t.weight ) AS Score,
1188						MAX(t.Weight) AS Weight
1189				FROM #Output t
1190				JOIN 
1191				(
1192					SELECT TimeId, SUM(Weight) AS w_sum
1193					FROM #Output
1194					WHERE score IS NOT NULL AND Weight <> 0 AND timeAggregation=1
1195					GROUP BY TimeId
1196				) d
1197				ON d.TimeId = t.TimeId
1198				WHERE score IS NOT NULL AND Weight <> 0 AND timeAggregation=1
1199				GROUP BY d.TimeId
1200			) d
1201			ON d.TimeId = o.timeId
1202			WHERE timeAggregation=1
1203
1204			--select * from #OutputFinal
1205			
1206			--update TRENDU
1207			UPDATE o
1208			SET o.Trend = o.score - oo.score
1209			FROM #OutputFinal o
1210			JOIN #OutputFinal oo
1211			ON DATEADD(month, 1, oo.time) = o.time
1212			WHERE o.timeAggregation = 0		
1213			AND oo.timeAggregation = 0				
1214			--
1215			--update TRENDU agg
1216			UPDATE o
1217			SET o.Trend = o.score - oo.score
1218			FROM #OutputFinal o
1219			JOIN #OutputFinal oo
1220			ON DATEADD(month, 1, oo.time) = o.time
1221			WHERE o.timeAggregation = 1		
1222			AND oo.timeAggregation = 1				
1223			--
1224
1225			--SELECT * from #Output
1226			--select 1
1227			
1228			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
1229			FROM #OutputFinal 
1230			WHERE (@WholeYear=1 AND YEAR(time) = YEAR(@Date)) OR
1231				  --(@WholeYear=1 AND @IdType = 'T') OR 
1232				  (@WholeYear=0 AND MONTH(@Date) = MONTH(time))
1233			ORDER BY time ASC
1234		END	
1235		------------------------------------------------------------------------------------
1236		ELSE BEGIN
1237			
1238			--update TRENDU
1239			UPDATE o
1240			SET o.Trend = o.score - oo.score
1241			FROM #Output o
1242			JOIN #Output oo
1243			ON DATEADD(month, 1, oo.time) = o.time
1244			WHERE o.timeAggregation = 0		
1245			AND oo.timeAggregation = 0	
1246			--
1247			--update TRENDU agg
1248			UPDATE o
1249			SET o.Trend = o.score - oo.score
1250			FROM #Output o
1251			JOIN #Output oo
1252			ON DATEADD(month, 1, oo.time) = o.time
1253			WHERE o.timeAggregation = 1		
1254			AND oo.timeAggregation = 1	
1255			--
1256			
1257
1258			--Puvodni verze mazala radky kde je time < @activeFrom. Zmeneno na porovnani pouze mesice. Pozadavek od Adama.
1259			--VF podstatě vyhodí TRENDovej mesic
1260			--OPRAVA: pro Tasky a @WholeYear=1 se vrací celej Task, a ne celej strom.
1261			--DECLARE @ActiveFromRounded DATE
1262			IF @IdType='T' AND @WholeYear = 1 BEGIN			
1263				DELETE FROM #Output WHERE time < @ActiveFromRounded
1264			END
1265
1266			--IF NOT EXISTS (SELECT TOP 1 1 FROM #Output WHERE MONTH(time) = MONTH(@Date))
1267				--SELECT 1
1268				--INSERT INTO #Output(TimeId,time,timeAggregation)
1269				--VALUES (1,@Date,0), (1,@Date,1)
1270			--select * from #Output
1271
1272			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
1273			FROM #Output
1274			WHERE (@WholeYear=1 AND YEAR(time) = YEAR(@Date) AND @IdType <> 'T') OR 
1275				  (@WholeYear=1 AND @IdType = 'T') OR 
1276				  (@WholeYear=0 AND MONTH(@Date) = MONTH(time) AND YEAR(time) = YEAR(@Date))				  				  
1277			ORDER BY time ASC
1278		END
1279	END
1280
1281
1282	END