· 7 years ago · Nov 22, 2018, 11:54 AM
1UPDATE [amb].[Version] SET [VersionId] = 225
2UPDATE [amb].[Version] SET [Version] = '4.0.0.5'
3
4-- Importy:
5-- zalozit view AmbicaBI_sources.dbo.VI__AL_INDICATORS (+ pro vazby, + pro fakta)
6-- upravit popisky v AmbicaBI_sources.ambica._VI_EMPTY
7-- upravit view AmbicaBI_sources.ambica._VDM_EMPTY
8
9-- Generator viewÄek:
10-- presunout do _sources
11-- dodelat podporu free polÃ
12
13--scrollBar v TreeListu má divné chovánà - IE
14
15--wfl.WorkflowStepIndicators
16----sloupec IndicatorId pÅ™Ãjde odstranit
17
18------------------------------------------------------
19--Importy memberů přibalit
20--ESSOX OLAP kostka s novou hierarchii - vyzkouset zda funguje, prý je nový model na APV
21--SUAS processing OLAPu a jeho trvánà (attribut na Modelu, zda generovat vÄ. distinct countů) - kontrola
22--SUAS plán zhora, pÅ™eÄÃslovat D_M_INDICATORS
23--SUAS filtr přes reporty
24--SUAS
25--BCM - rozesláno cca 140mailu a informace je jen u cca 15
26--BCM - Äas nesedÃ
27
28--DADA pozastaveno
29------------------------------------------------------
30
31--Ät a pá: update TRADIXu
32--pÅ™ÃÅ¡tà týden SETOSu
33
34--pro distinct county measure gourpy prepnout datatype na int (nynà Wchar)
35
36IF COL_LENGTH('p4s.Models', 'DistinctCountIncluding') IS NULL BEGIN
37 ALTER TABLE p4s.Models
38 ADD DistinctCountIncluding BIT NOT NULL DEFAULT 0
39END
40
41GO
42
43IF COL_LENGTH('wfl.WorkflowStepIndicators', 'TreeListIndicatorCode') IS NULL BEGIN
44 ALTER TABLE wfl.WorkflowStepIndicators
45 ADD TreeListIndicatorCode NVARCHAR(MAX) NULL
46END
47
48GO
49
50IF COL_LENGTH('wfl.WorkflowStepDetails', 'TreeListRootIndicatorCode') IS NOT NULL BEGIN
51 ALTER TABLE wfl.WorkflowStepDetails
52 DROP COLUMN TreeListRootIndicatorCode
53END
54
55GO
56
57IF EXISTS ( SELECT *
58 FROM sysobjects
59 WHERE id = object_id(N'[import].[SourceTestVDM]')
60 and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
61BEGIN
62 DROP PROCEDURE [import].[SourceTestVDM]
63END
64GO
65
66/****** Object: StoredProcedure [ambica].[SourceTestVDM_newFreeFields] Script Date: 20.11.2018 10:43:35 ******/
67SET ANSI_NULLS ON
68GO
69SET QUOTED_IDENTIFIER ON
70GO
71
72CREATE PROCEDURE [import].[SourceTestVDM]
73 @DimCode varchar(20) = 'XXX'
74 ,@Preview bit = 1 -- pro prvni verzi pocitam spise s rucnim spoustenim pro kazdy zdroj zvlast
75AS
76BEGIN
77
78DECLARE
79 @pocet int = 0
80 ,@source_name varchar(255)
81 ,@time datetime = GetDate()
82 ,@query nvarchar(max),
83 @sources_db nvarchar(255) = (SELECT ParameterValue FROM amb.Parameters WHERE ParameterName = 'db_source_name')
84
85DECLARE @tbl_vzor TABLE
86( COLUMN_NAME varchar(20)
87 ,ORDINAL_POSITION int
88 ,IS_NULLABLE varchar(20)
89 ,DATA_TYPE varchar(20)
90 ,CHARACTER_MAXIMUM_LENGTH int
91)
92DECLARE @tbl_source TABLE
93( COLUMN_NAME varchar(20)
94 ,ORDINAL_POSITION int
95 ,IS_NULLABLE varchar(20)
96 ,DATA_TYPE varchar(20)
97 ,CHARACTER_MAXIMUM_LENGTH int
98)
99DECLARE @tbl_test_result TABLE
100( SourceName varchar(255)
101 ,TestResult varchar(255)
102)
103DECLARE @TypeConvertTable TABLE (AppType NVARCHAR(50), DbType NVARCHAR(50))
104 INSERT INTO @TypeConvertTable VALUES
105 ('Int32','int'),
106 ('String','nvarchar'),
107 ('DateTime','datetime'),
108 ('Double','float'),
109 ('Boolean','bit')
110SET @source_name = 'VDM_' + @DimCode
111
112SET @Query = 'SELECT COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
113FROM '+@sources_db+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''_VDM_EMPTY'' AND TABLE_SCHEMA = ''ambica'''
114--print @Query
115INSERT INTO @tbl_vzor
116EXEC(@Query)
117
118INSERT INTO @tbl_vzor
119SELECT ff.Name, (SELECT MAX(ORDINAL_POSITION) FROM @tbl_vzor)+1, 'NO', tct.DbType, CASE WHEN tct.DbType = 'NVARCHAR' THEN 255 ELSE NULL END
120FROM p4s.FreeFields ff
121JOIN p4s.MemberListFreeFields mlff
122ON mlff.FreeFieldId = ff.Id
123JOIN p4s.MemberLists ml
124ON ml.Id = mlff.MemberListId
125JOIN @TypeConvertTable tct
126ON tct.AppType = ff.Datatype
127WHERE ml.Code = @DimCode
128
129--SELECT * from @tbl_vzor
130
131SET @Query = 'SELECT COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
132FROM '+@sources_db+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@source_name+''''
133INSERT INTO @tbl_source
134EXEC(@Query)
135
136-- TEST existence zdroje
137IF (SELECT COUNT(*) FROM @tbl_source) = 0
138BEGIN
139 INSERT @tbl_test_result VALUES(@source_name, 'Zdrojové view neexistuje.' )
140END
141
142IF (SELECT COUNT(*) FROM @tbl_source) <> 0
143BEGIN
144 -- TEST shody poctu poli - asi zbytecne, kdyz pak piseme presne rozdily, ale nechme to
145 SET @pocet = (SELECT COUNT(*) FROM @tbl_vzor) - (SELECT COUNT(*) FROM @tbl_source)
146
147 IF @pocet > 0
148 BEGIN
149 INSERT @tbl_test_result VALUES(@source_name, 'NeodpovÃdá poÄet polà - poÄet chybÄ›jÃcÃch sloupců: ' + RTRIM(CAST(@pocet AS varchar)) + '.')
150 END
151
152 IF @pocet < 0
153 BEGIN
154 INSERT @tbl_test_result VALUES(@source_name, 'NeodpovÃdá poÄet polà - poÄet nadbyteÄných slpouců: ' + RTRIM(CAST(@pocet AS varchar)) + '.')
155 END
156
157 -- TEST shody nazvu - spousti se i pri shode i neshode poctu
158 INSERT INTO @tbl_test_result (SourceName, TestResult)
159 SELECT @source_name,
160 'Ve zdroji chybà sloupec Ä.' + RTRIM(CAST(v.ORDINAL_POSITION AS varchar)) + ' - ' + v.COLUMN_NAME + '.'
161 FROM @tbl_vzor v
162 LEFT JOIN @tbl_source s
163 ON s.COLUMN_NAME = v.COLUMN_NAME
164 WHERE s.COLUMN_NAME IS NULL
165 ORDER BY v.ORDINAL_POSITION
166
167 INSERT INTO @tbl_test_result (SourceName, TestResult)
168 SELECT @source_name,
169 'Ve zdroji je navÃc sloupec Ä.' + RTRIM(CAST(s.ORDINAL_POSITION AS varchar)) + ' - ' + s.COLUMN_NAME + '.'
170 FROM @tbl_source s
171 LEFT JOIN @tbl_vzor v
172 ON s.COLUMN_NAME = v.COLUMN_NAME
173 WHERE v.COLUMN_NAME IS NULL
174 ORDER BY s.ORDINAL_POSITION
175
176 ---- TEST poradi sloupcu
177 -- INSERT INTO @tbl_test_result (SourceName, TestResult)
178 -- SELECT @source_name,
179 -- 'Sloupec ' + s.COLUMN_NAME + ' nemá požadovanou pozici: ' + RTRIM(CAST(v.ORDINAL_POSITION AS varchar))
180 -- + '. Aktuálnà pozice je: ' + + RTRIM(CAST(s.ORDINAL_POSITION AS varchar))
181 -- + ' .'
182 -- FROM @tbl_vzor v
183 -- JOIN @tbl_source s
184 -- ON s.COLUMN_NAME = v.COLUMN_NAME
185 -- WHERE s.ORDINAL_POSITION <> v.ORDINAL_POSITION
186 -- ORDER BY v.ORDINAL_POSITION
187
188 -- TEST datoveho typu
189 INSERT INTO @tbl_test_result (SourceName, TestResult)
190 SELECT @source_name,
191 'Sloupec ' + v.COLUMN_NAME + ' nemá požadovaný datový typ: '
192 + v.DATA_TYPE + CASE v.DATA_TYPE
193 WHEN 'varchar' THEN '(' + RTRIM(CAST(v.CHARACTER_MAXIMUM_LENGTH AS varchar)) + ')'
194 ELSE ''
195 END
196 + ' . Aktuálnà datový typ je: '
197 + s.DATA_TYPE + CASE s.DATA_TYPE
198 WHEN 'varchar' THEN '(' + RTRIM(CAST(s.CHARACTER_MAXIMUM_LENGTH AS varchar)) + ')'
199 ELSE ''
200 END
201 + ' .'
202 FROM @tbl_vzor v
203 JOIN @tbl_source s
204 ON s.COLUMN_NAME = v.COLUMN_NAME
205 WHERE v.DATA_TYPE <> s.DATA_TYPE
206 OR ( v.DATA_TYPE = s.DATA_TYPE
207 AND v.CHARACTER_MAXIMUM_LENGTH <> s.CHARACTER_MAXIMUM_LENGTH )
208 ORDER BY v.ORDINAL_POSITION
209END
210
211IF @Preview = 1
212BEGIN
213 IF (SELECT COUNT(*) FROM @tbl_test_result) = 0
214 INSERT INTO @tbl_test_result (SourceName, TestResult)
215 SELECT @source_name, 'OK'
216 SELECT * FROM @tbl_test_result
217END
218
219IF @Preview = 0
220BEGIN
221 IF (SELECT COUNT(*) FROM @tbl_test_result) = 0
222 BEGIN
223 SET @query = '
224 -- zapis celkoveho vysledku testu zdroje
225 INSERT INTO '+@sources_db+'.ambica.SourceTestResults (SourceName, TestStatus, TestResultComment, LastTestDatetime)
226 VALUES ('''+@source_name+''', 1, ''OK'', '''+CAST(@time AS VARCHAR)+''')
227 -- zapis OK vysledku do detailni tbl - aby se promazala
228 DELETE FROM '+@sources_db+'.ambica.SourceTestDetails WHERE SourceName = '''+@source_name+'''
229 INSERT INTO '+@sources_db+'.ambica.SourceTestDetails (SourceName, TestResult, TestTime)
230 SELECT '''+@source_name+''', ''OK'', '''+CAST(@time AS VARCHAR)+''''
231 EXEC(@query)
232
233 END
234 IF (SELECT COUNT(*) FROM @tbl_test_result) <> 0
235 BEGIN
236 SET @query = '
237 -- zapis celkoveho vysledku testu zdroje
238 INSERT INTO '+@sources_db+'.ambica.SourceTestResults (SourceName, TestStatus, TestResultComment, LastTestDatetime)
239 VALUES ('''+@source_name+''', 0, ''ERROR'', '''+CAST(@time AS VARCHAR)+''')
240 -- zapis jednotlivych zjistenych nedostatku
241 DELETE FROM '+@sources_db+'.ambica.SourceTestDetails WHERE SourceName = '''+@source_name+'''
242 INSERT INTO '+@sources_db+'.ambica.SourceTestDetails (SourceName, TestResult, TestTime)
243 SELECT '''+@source_name+''', ''ERROR'', '''+CAST(@time AS VARCHAR)+''''
244 EXEC(@query)
245 END
246END
247
248END
249
250
251GO
252
253
254/****** Object: StoredProcedure [import].[MembersFromSource_newFreeFields] Script Date: 20.11.2018 10:35:13 ******/
255SET ANSI_NULLS ON
256GO
257SET QUOTED_IDENTIFIER ON
258GO
259
260-- =================================================
261-- Author: JPR
262-- Create date: 10.10.2018
263-- Description: Procedura importuje prvky jednoho memberlistu, vÄetnÄ› hodnost freepolÃ
264-- Changelog: 2018-10-10 JPR Zalozeno kopii procedury od PJE, upraveno pro nove FreeFields
265--
266-- =================================================
267ALTER PROCEDURE [import].[MembersFromSource]
268 @MemberListId int = 0
269 ,@BlockMissing bit = 0
270 ,@RunSourceTest bit = 1
271
272AS
273BEGIN
274
275DECLARE
276 @MemberListCode nvarchar(20)
277 ,@tblSource import.SourceMembers
278 ,@query nvarchar(max)
279 ,@cols nvarchar(max)
280 ,@cols_pivot nvarchar(max)
281 ,@db_source_name nvarchar(255)
282DECLARE @TypeConvertTable TABLE (AppType NVARCHAR(50), DbType NVARCHAR(50))
283INSERT INTO @TypeConvertTable VALUES
284 ('Int32','int'),
285 ('String','nvarchar'),
286 ('DateTime','datetime'),
287 ('Double','float'),
288 ('Boolean','bit')
289-- pro testy
290 --,@MemberListId int = 2
291 --,@BlockMissing bit = 0
292 --,@RunSourceTest bit = 1
293
294DECLARE @TestResult TABLE (SourceTestResult bit)
295DECLARE @source_test TABLE (name varchar(255) null)
296DECLARE @changes TABLE (MemberCode varchar(20),
297 Change varchar(20));
298
299SET @MemberListCode = (SELECT Code FROM p4s.MemberLists WHERE Id = @MemberListId)
300SET @db_source_name = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'db_source_name')
301
302-- kontrola vstupniho rozhrani
303IF @RunSourceTest = 1
304BEGIN
305 SET @query = 'import.SourceTestVDM @DimCode = ''' + @MemberListCode + ''', @Preview = 0'
306 EXEC (@query)
307
308 SET @query = 'SELECT TOP 1 TestStatus FROM ' + @db_source_name + '.ambica.SourceTestResults WHERE SourceName = ''VDM_' + @MemberListCode + ''' ORDER BY LastTestDatetime DESC'
309
310 DELETE FROM @TestResult
311 INSERT INTO @TestResult EXEC (@query)
312
313 IF (SELECT SourceTestResult FROM @TestResult) = 0
314 BEGIN
315 -- zapis do logu
316 INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
317 VALUES
318 ( 'Members', @MemberListCode, 'ERROR',0, 0, 0
319 ,'Chyba vstupnÃho rozhranÃ'
320 ,GetDate()
321 )
322 SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
323 --SELECT - @@IDENTITY --18.8.2016 JPE přidána návratová hodnota (musi byt pres SELECT, nestaci RETURN 1 :-( )
324 RETURN
325 END
326END
327
328BEGIN TRY
329
330 SET @cols = CAST((SELECT ff.Name + ', ' AS [text()]
331 FROM p4s.FreeFields ff
332 JOIN p4s.MemberListFreeFields mlff
333 ON mlff.FreeFieldId = ff.Id
334 JOIN p4s.MemberLists ml
335 ON ml.Id = mlff.MemberListId
336 WHERE ml.Code = @MemberListCode
337 ORDER BY ff.Name
338 FOR XML PATH ('')) AS VARCHAR(MAX))
339
340 SET @cols_pivot = CAST((SELECT '['+ff.Name+'] = CONVERT(VARCHAR(320), ['+ff.Name+']),' AS [text()]
341 FROM p4s.FreeFields ff
342 JOIN p4s.MemberListFreeFields mlff
343 ON mlff.FreeFieldId = ff.Id
344 JOIN p4s.MemberLists ml
345 ON ml.Id = mlff.MemberListId
346 WHERE ml.Code = @MemberListCode
347 ORDER BY ff.Name
348 FOR XML PATH ('')) AS VARCHAR(MAX))
349
350
351 DECLARE @tableSource TABLE
352 (
353 Code VARCHAR(20),
354 Name NVARCHAR(255),
355 ShortName NVARCHAR(50),
356 SortOrder INT
357 )
358
359 -- naplneni tabulky ze zdroje
360 SET @query = 'SELECT Code, Name, ShortName, SortOrder FROM ' + @db_source_name + '.dbo.VDM_' + @MemberListCode
361 INSERT INTO @tableSource (Code, Name, ShortName, SortOrder)
362 EXEC(@query)
363
364
365 --select * from ##tablesource
366
367 ;WITH cte_members
368 AS ( SELECT *
369 FROM p4s.Members
370 WHERE MemberListId = @MemberListId
371 )
372
373 MERGE cte_members AS t
374 USING @tableSource AS s
375 ON t.Code = s.Code
376 -- stavajici prvky na zdroji - updatujeme pouze pokud
377 -- a) jedna se o importovane prvky
378 -- b) na zdroji neni NULL
379 -- c) zdroj nese nejakou zmenu
380 WHEN MATCHED AND t.Imported = 1
381 AND ( t.Name <> s.Name
382 OR t.ShortName <> s.ShortName
383 OR t.SortOrder <> s.SortOrder
384 )
385 THEN UPDATE
386 SET
387 Name = s.Name
388 ,ShortName = ISNULL(s.ShortName, t.ShortName)
389 ,SortOrder = s.SortOrder
390
391 -- nove prvky na zdroji
392 WHEN NOT MATCHED BY TARGET THEN
393 INSERT (MemberListId, Code, Name, ShortName, SortOrder, Imported, Blocked)
394 VALUES (@MemberListId, s.Code, s.Name, s.ShortName, s.SortOrder, 1, 0)
395
396 -- stavajici prvky chybi na zdroji a jsou oznaceny jako importovane --> oznacime jako Blocked
397 WHEN NOT MATCHED BY SOURCE AND @BlockMissing = 1 AND t.Imported = 1 AND t.Blocked = 0
398 THEN UPDATE SET Blocked = 1
399
400 OUTPUT Inserted.Code, $action INTO @changes;
401
402 --import of freeFields, i'm unpivoting freefield data to same structure as p4s.FreeFieldsValues and then via merge UPDATE or INSERT
403 SET @query = '
404 MERGE p4s.FreeFieldValues t
405 USING (
406 SELECT d.MemberId, ff.Id AS FreeFieldId, d.Value FROM (
407 SELECT DISTINCT MemberId, Property, Value
408 FROM
409 (
410 SELECT m.Id AS MemberId, s.Code,
411 [Name] = CONVERT(VARCHAR(320), s.[Name]),
412 [ShortName] = CONVERT(VARCHAR(320), s.[ShortName]),
413 [SortOrder] = CONVERT(VARCHAR(320), s.[SortOrder]),' +
414 LEFT(@cols_pivot, LEN(@cols_pivot)-1)
415 + '
416 FROM ' + @db_source_name + '.dbo.VDM_' + @MemberListCode + ' s
417 JOIN p4s.Members m
418 ON m.Code = s.Code
419 AND m.MemberListId = ' + CAST(@MemberListId AS VARCHAR) + '
420 ) AS t
421 UNPIVOT
422 (
423 Value FOR Property IN (
424 [Name],
425 [ShortName],
426 [SortOrder],' +
427 LEFT(@cols, LEN(@cols)-1) + ')
428 ) AS up
429 where up.Property not in (''Name'', ''ShortName'', ''SortOrder'')
430 ) d
431 JOIN p4s.FreeFields ff
432 ON ff.Name = d.Property
433 RIGHT JOIN p4s.MemberListFreeFields mlff
434 ON mlff.MemberListId = ' + CAST(@MemberListId AS VARCHAR) + '
435 AND mlff.FreeFieldId = ff.Id
436 WHERE MemberId IS NOT NULL
437 --ORDER BY MemberId
438 ) AS s
439 ON t.MemberId = s.MemberID
440 AND t.FreeFieldId = s.FreeFieldId
441 -- stavajici prvky na zdroji - updatujeme pouze pokud
442 -- a) jedna se o importovane prvky
443 -- b) na zdroji neni NULL
444 -- c) zdroj nese nejakou zmenu
445 WHEN MATCHED
446 AND t.Value <> s.Value
447 THEN UPDATE
448 SET Value = s.Value
449 -- nove prvky na zdroji
450 WHEN NOT MATCHED BY TARGET THEN
451 INSERT (MemberId, FreeFieldId, Value)
452 VALUES (s.MemberId, s.FreeFieldId, s.Value);'
453
454 --print @query
455 EXEC(@query)
456
457
458 -- zapis do logu
459 INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
460 VALUES
461 ( 'Members', @MemberListCode, 'OK'
462 ,(SELECT COUNT(*) FROM @changes WHERE Change = 'UPDATE')
463 ,(SELECT COUNT(*) FROM @changes WHERE Change = 'INSERT')
464 ,0 -- TODO - je nutno si do tabulky @changes dat pole Blocked a upravit podminky pro pocty zde
465 ,'Import proběhl v pořádku'
466 ,GetDate()
467 )
468 SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
469 --SELECT @@IDENTITY --18.8.2016 JPE přidán RETURN s návratovou hodnotou
470 RETURN
471
472END TRY
473BEGIN CATCH
474 -- zapis do logu
475 INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
476 VALUES
477 ( 'Members', @MemberListCode, 'ERROR',0, 0, 0
478 ,'Nespecifikovaná chyba importu'
479 ,GetDate()
480 )
481 --SELECT - @@IDENTITY
482 SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
483 RETURN --18.8.2016 JPE přidána návratová hodnota
484END CATCH
485
486
487END
488
489
490-- GO
491-- DECLARE @Query NVARCHAR(MAX),
492-- @sources_db nvarchar(255) = (SELECT ParameterValue FROM amb.Parameters WHERE ParameterName = 'db_source_name')
493
494-- SET @Query = '
495-- ALTER VIEW ['+@sources_db+'].[ambica].[_VDM_EMPTY]
496-- AS
497-- SELECT
498-- -- povinne pole nesouci znakovy klic prvku daneho seznamu prvku (napr. kod strediska, kod produktu,...)
499-- Code AS Code --[varchar](20) NOT NULL,
500
501-- -- povinne pole pro nazev
502-- ,Name AS Name --[nvarchar](255) NOT NULL,
503-- ,ShortName AS ShortName
504-- -- povinne pole pro urceni poradi prvku pri trideni --[int] NOT NULL,
505-- -- jsou zde uvedeny vzory nekolika zpusobu, je potreba pouzit pouze JEDEN
506-- -- vzor kodu, kdy kod prvku je puvodne ciselny a ma se podle nej tridit
507-- -- neda se tomu 100% verit, proto je zabudovan test
508-- -- ty, co nejsou ciselne, maji poradi 0, budou na zacatku a uzivatele to donuti probelm resit
509-- ,CASE WHEN ISNUMERIC(Code) = 1 THEN CAST(Code AS int) ELSE 0 END AS SortOrder
510-- -- vzor kodu pro nagenerovani trideni dle kodu (znakove)
511-- --,CAST(ROW_NUMBER() OVER( ORDER BY Code) AS int) AS SortOrder
512-- -- vzor kodu pro nagenerovani trideni dle kodu (ciselne)
513-- --,CAST(ROW_NUMBER() OVER( ORDER BY CASE WHEN ISNUMERIC(Code) = 1 THEN CAST(Code AS int) ELSE 0 END) AS int) AS SortOrder
514-- -- vzor kodu pro nagenerovani trideni dle nazvu
515-- --,CAST(ROW_NUMBER() OVER( ORDER BY Name) AS int) AS SortOrder
516-- -- zde doplnit sloupce pro volná pole. Nazev a dat. typ sloupce podle Názvu a Typu volného pole
517-- -- zde doplnit skutecnou zdrojovou tabulku
518-- FROM dbo._DM_EMPTY'
519-- EXEC(@Query)
520
521GO
522
523IF EXISTS ( SELECT *
524 FROM sysobjects
525 WHERE id = object_id(N'[p4s].[DeleteMemberSet]')
526 and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
527BEGIN
528 DROP PROCEDURE [p4s].[DeleteMemberSet]
529END
530/****** Object: StoredProcedure [p4s].[DeleteMemberSet] Script Date: 21.11.2018 11:03:10 ******/
531SET ANSI_NULLS ON
532GO
533SET QUOTED_IDENTIFIER ON
534GO
535-- =================================================
536-- Author: PJE
537-- Create date: podzim 2018
538-- Description: Procedura maže všechna data pro zadanou množinu MemberSets.Id
539-- Changelog: 2018-11-20 PJE Upraveno na novou podobu metadat
540
541CREATE PROCEDURE [p4s].[DeleteMemberSet]
542 @MemberSets p4s.TIds READONLY
543AS
544BEGIN
545 -- TODO - mohli bychom aspon smazat i data pro prvky vznikle spustenim pravidla na tomto mazanem prvku !!!
546
547 DELETE f FROM p4s.Facts f JOIN @MemberSets ms ON f.DimMemberId = ms.Id
548
549 DELETE msd FROM p4s.MemberSetDetails msd JOIN @MemberSets ms ON msd.MemberSetId = ms.Id
550
551 DELETE ffv
552 FROM p4s.FreeFieldValues2 ffv
553 JOIN p4s.MemberSetFacts msf ON msf.Id = ffv.MemberSetFactId
554 JOIN @MemberSets ms ON ms.Id = msf.MemberSetId
555
556 DELETE mf FROM p4s.MemberSetFacts mf JOIN @MemberSets ms ON mf.MemberSetId = ms.Id
557
558 DELETE ms FROM p4s.MemberSets ms JOIN @MemberSets msp ON msp.Id = ms.Id
559
560END
561
562IF OBJECT_ID (N'[p4s].[MemberSets]', N'U') IS NULL BEGIN
563 CREATE TABLE [p4s].[MemberSets] (
564 [Id] [bigint] IDENTITY(1,1) NOT NULL,
565 [ModelId] [int] NOT NULL
566 CONSTRAINT [PK_MemberSets] PRIMARY KEY CLUSTERED
567 (
568 [Id] ASC
569 ) ON [PRIMARY]
570 ) ON [PRIMARY]
571 ALTER TABLE [p4s].[MemberSets] WITH NOCHECK
572 ADD CONSTRAINT [FK_MemberSets_Models] FOREIGN KEY([ModelId])
573 REFERENCES [p4s].[Models] ([Id])
574 ALTER TABLE [p4s].[MemberSets] NOCHECK CONSTRAINT [FK_MemberSets_Models]
575END
576
577GO
578
579IF (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
580WHERE TABLE_NAME = 'MemberSetDetails'
581AND COLUMN_NAME = 'MemberSetId'
582AND TABLE_SCHEMA = 'p4s') = 'int'
583BEGIN
584 ALTER TABLE [p4s].[MemberSetDetails] DROP CONSTRAINT [FK_MemberSetDetails_MemberSets]
585 ALTER TABLE [p4s].[MemberSetDetails]
586 ALTER COLUMN MemberSetId BIGINT NOT NULL
587 ALTER TABLE [p4s].[MemberSetDetails] WITH NOCHECK
588 ADD CONSTRAINT [FK_MemberSetDetails_MemberSets] FOREIGN KEY([MemberSetId])
589 REFERENCES [p4s].[MemberSets] ([Id])
590 ALTER TABLE [p4s].[MemberSetDetails] CHECK CONSTRAINT [FK_MemberSetDetails_MemberSets]
591END
592
593GO
594
595IF EXISTS (SELECT TOP 1 1
596FROM sys.indexes
597WHERE name='CI_MemberSetDetails' AND object_id = OBJECT_ID('p4s.MemberSetDetails'))
598BEGIN
599 DROP INDEX [CI_MemberSetDetails] ON [p4s].[MemberSetDetails] WITH ( ONLINE = OFF )
600 CREATE CLUSTERED INDEX [CI_MemberSetDetails] ON [p4s].[MemberSetDetails]
601 (
602 [MemberSetId] ASC,
603 [MemberListId] ASC,
604 [MemberId] ASC
605 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
606END
607
608GO
609
610IF OBJECT_ID (N'[p4s].[MemberSetFacts]', N'U') IS NULL
611BEGIN
612 ALTER TABLE [p4s].[MemberFacts] DROP CONSTRAINT [FK_MemberFacts_Members]
613 ALTER TABLE [p4s].[MemberFacts] DROP CONSTRAINT [PK_MemberFacts]
614 ALTER TABLE [p4s].[MemberFacts] DROP CONSTRAINT [FK_MemberFacts_Models]
615 ALTER TABLE [p4s].[MemberFacts] DROP CONSTRAINT [FK_MemberFacts_Rules]
616 ALTER TABLE [p4s].[MemberFacts] DROP CONSTRAINT [FK_MemberFacts_Rules1]
617 ALTER TABLE [p4s].[MemberFacts] DROP CONSTRAINT [FK_MemberFacts_Rules2]
618 EXEC sp_rename 'p4s.MemberFacts.MemberId', 'MemberSetId', 'COLUMN';
619 EXEC sp_rename 'p4s.MemberFacts', 'MemberSetFacts';
620 ALTER TABLE [p4s].[MemberSetFacts]
621 ALTER COLUMN MemberSetId BIGINT NOT NULL
622 ALTER TABLE [p4s].[MemberSetFacts] WITH NOCHECK ADD CONSTRAINT [FK_MemberSetFacts_MemberSets] FOREIGN KEY([MemberSetId])
623 REFERENCES [p4s].[MemberSets] ([Id])
624 ALTER TABLE [p4s].[MemberSetFacts] CHECK CONSTRAINT [FK_MemberSetFacts_MemberSets]
625 ALTER TABLE [p4s].[MemberSetFacts] ADD CONSTRAINT [PK_MemberSetFacts] PRIMARY KEY CLUSTERED
626 (
627 [Id] ASC
628 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
629 ALTER TABLE [p4s].[MemberSetFacts] WITH CHECK ADD CONSTRAINT [FK_MemberSetFacts_Models] FOREIGN KEY([ModelId])
630 REFERENCES [p4s].[Models] ([Id])
631 ALTER TABLE [p4s].[MemberSetFacts] CHECK CONSTRAINT [FK_MemberSetFacts_Models]
632 ALTER TABLE [p4s].[MemberSetFacts] WITH CHECK ADD CONSTRAINT [FK_MemberSetFacts_Rules] FOREIGN KEY([DimRuleId])
633 REFERENCES [p4s].[Rules] ([Id])
634 ALTER TABLE [p4s].[MemberSetFacts] CHECK CONSTRAINT [FK_MemberSetFacts_Rules]
635 ALTER TABLE [p4s].[MemberSetFacts] WITH CHECK ADD CONSTRAINT [FK_MemberSetFacts_Rules1] FOREIGN KEY([TimeRuleId])
636 REFERENCES [p4s].[Rules] ([Id])
637 ALTER TABLE [p4s].[MemberSetFacts] CHECK CONSTRAINT [FK_MemberSetFacts_Rules1]
638 ALTER TABLE [p4s].[MemberSetFacts] WITH CHECK ADD CONSTRAINT [FK_MemberSetFacts_Rules2] FOREIGN KEY([IndRuleId])
639 REFERENCES [p4s].[Rules] ([Id])
640 ALTER TABLE [p4s].[MemberSetFacts] CHECK CONSTRAINT [FK_MemberSetFacts_Rules2]
641END
642
643GO
644
645IF OBJECT_ID (N'[p4s].[FreeFieldValues2]', N'U') IS NULL BEGIN
646 CREATE TABLE [p4s].[FreeFieldValues2](
647 [Id] [bigint] IDENTITY(1,1) NOT NULL,
648 [MemberSetFactId] [bigint] NOT NULL,
649 [FreeFieldId] [int] NOT NULL,
650 [FFValueTypeId] [int] NOT NULL,
651 [Value] [nvarchar](100) NULL,
652 [MemberId] [int] NULL,
653 [MemberFreeFieldId] [int] NULL,
654 CONSTRAINT [PK_FreeFieldValues2] PRIMARY KEY CLUSTERED
655 (
656 [Id] ASC
657 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
658 ) ON [PRIMARY]
659 CREATE TABLE [p4s].[FreeFieldValueTypes](
660 [Id] [int] NOT NULL,
661 [Name] [nvarchar](255) NOT NULL,
662 [Description] [nvarchar](max) NULL,
663 CONSTRAINT [PK_FreeFieldValueTypes] PRIMARY KEY CLUSTERED
664 (
665 [Id] ASC
666 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
667 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
668 INSERT INTO [p4s].[FreeFieldValueTypes] (Id, Name, Description) VALUES
669 (1,N'Zadaná hodnota',N'Do freepole se pÅ™Ãmo zadá hodnota požadovaného datového typu.')
670 ,(2,N'PÅ™evzÃt hodnotu',N'Hodnota bude pÅ™evzata ze zadaného freepole zadaného prvku kombinaÄnà dimenze')
671 ALTER TABLE [p4s].[FreeFieldValues2] WITH CHECK ADD CONSTRAINT [FK_FreeFieldValues2_FreeFields] FOREIGN KEY([FreeFieldId])
672 REFERENCES [p4s].[FreeFields] ([Id])
673 ALTER TABLE [p4s].[FreeFieldValues2] CHECK CONSTRAINT [FK_FreeFieldValues2_FreeFields]
674 ALTER TABLE [p4s].[FreeFieldValues2] WITH CHECK ADD CONSTRAINT [FK_FreeFieldValues2_MemberFacts] FOREIGN KEY([MemberSetFactId])
675 REFERENCES [p4s].[MemberSetFacts] ([Id])
676 ALTER TABLE [p4s].[FreeFieldValues2] CHECK CONSTRAINT [FK_FreeFieldValues2_MemberFacts]
677END
678GO
679
680/****** Object: StoredProcedure [p4s].[RuleTimeRun] Script Date: 21.11.2018 13:23:41 ******/
681SET ANSI_NULLS ON
682GO
683SET QUOTED_IDENTIFIER ON
684GO
685
686-- =================================================
687-- Author: PJE
688-- Create date: 30.08.2018
689-- Description: -- Procedura na realizaci Pravidla pro Äas pro položkové Å¡ablony
690-- -- @OutputType:
691-- 0 - default - spusti proceduru ModifyFacts, nepoda nic
692-- 1 - vypise obsah @Data
693-- Changelog:
694-- 07.09.2018 PJE - oprava chovani u odpisu, posledni rok neni na vsechny mesice
695-- - pridan parametr (a nove free pole) pro vyjadreni "vyznamu" castky
696-- 14.09.2018 PJE - opravy
697-- 19.11.2018 PJE - uprava na novou podobu metadat
698-- =================================================
699ALTER PROCEDURE [p4s].[RuleTimeRun]
700 @MemberSetFactId bigint
701 ,@OutputType int = 0
702AS
703BEGIN
704
705-- pro testy
706--DECLARE
707-- @MemberSetFactId bigint = 37
708-- ,@OutputType int = 0
709
710DECLARE
711 @Data p4s.TFacts
712 ,@TimeMembers p4s.TIDs
713 ,@DateFrom datetime
714 ,@DateTo datetime
715 ,@Count int
716 ,@RealCount int
717 ,@Frequency int
718 ,@ValueSize int
719 ,@OdpisovaSkupina varchar(10)
720 ,@RuleId int
721 ,@MemberSetId bigint
722 ,@PlanId int
723 ,@TimeId int
724 ,@IndicatorId int
725 ,@TimeHierarchyId int
726 ,@TimeDetailLevelId int
727 ,@ModelId int
728 ,@TimeDetailMemberListId int
729 ,@FromTimeMemberId int
730 ,@ToTimeMemberId int
731 ,@Value float
732 ,@ValueToSave float
733 ,@FirtsYearId int
734 ,@FirstDBDate datetime
735 ,@LastDBDate datetime
736
737-- naplneni promennych
738SET @RuleId = (SELECT TimeRuleId FROM p4s.MemberSetFacts WHERE Id = @MemberSetFactId)
739SET @MemberSetId = (SELECT MemberSetId FROM p4s.MemberSetFacts WHERE Id = @MemberSetFactId)
740SET @IndicatorId = (SELECT IndicatorId FROM p4s.MemberSetFacts WHERE Id = @MemberSetFactId)
741SET @PlanId = (SELECT PlanId FROM p4s.MemberSetFacts WHERE Id = @MemberSetFactId)
742SET @TimeId = (SELECT TimeId FROM p4s.MemberSetFacts WHERE Id = @MemberSetFactId)
743SET @ModelId = (SELECT ModelId FROM p4s.MemberSetFacts WHERE Id = @MemberSetFactId)
744SET @Value = (SELECT Value FROM p4s.MemberSetFacts WHERE Id = @MemberSetFactId)
745
746PRINT '@RuleId = ' + ISNULL(CAST(@RuleId AS varchar),'NULL')
747PRINT '@MemberSetId = ' + ISNULL(CAST(@MemberSetId AS varchar),'NULL')
748PRINT '@IndicatorId = ' + ISNULL(CAST(@IndicatorId AS varchar),'NULL')
749PRINT '@PlanId = ' + ISNULL(CAST(@PlanId AS varchar),'NULL')
750PRINT '@TimeId = ' + ISNULL(CAST(@TimeId AS varchar),'NULL')
751PRINT '@ModelId = ' + ISNULL(CAST(@ModelId AS varchar),'NULL')
752PRINT '@Value = ' + ISNULL(CAST(@Value AS varchar),'NULL')
753
754SET @TimeHierarchyId = (SELECT TimeHierarchyId FROM p4s.ModelDimensionality WHERE ModelId = @ModelId)
755SET @TimeDetailLevelId = (SELECT TimeDetailLevelId FROM p4s.ModelDimensionality WHERE ModelId = @ModelId)
756PRINT '@TimeHierarchyId = ' + ISNULL(CAST(@TimeHierarchyId AS varchar),'NULL')
757PRINT '@TimeDetailLevelId = ' + ISNULL(CAST(@TimeDetailLevelId AS varchar),'NULL')
758
759SET @TimeDetailMemberListId = (SELECT MemberListId FROM p4s.TimeHierarchyLevels WHERE Id = @TimeDetailLevelId)
760
761-- naplneni promennych z freepoli
762SET @DateFrom = (SELECT CAST(Value AS datetime) FROM p4s.FreeFieldValues2 WHERE MemberSetFactId = @MemberSetFactId AND FreeFieldId = -1)
763SET @DateTo = (SELECT CAST(Value AS datetime) FROM p4s.FreeFieldValues2 WHERE MemberSetFactId = @MemberSetFactId AND FreeFieldId = -2)
764SET @Count = (SELECT CAST(Value AS int) FROM p4s.FreeFieldValues2 WHERE MemberSetFactId = @MemberSetFactId AND FreeFieldId = -3)
765SET @Frequency = CASE ISNULL((SELECT Value FROM p4s.FreeFieldValues2 WHERE MemberSetFactId = @MemberSetFactId AND FreeFieldId = -4),'')
766 WHEN 'MÄ›sÃÄnÃ' THEN 1
767 WHEN 'ÄŒtvrtletnÃ' THEN 3
768 WHEN 'PololetnÃ' THEN 6
769 WHEN 'RoÄnÃ' THEN 12
770 ELSE 1
771 END
772SET @ValueSize = CASE ISNULL((SELECT Value FROM p4s.FreeFieldValues2 WHERE MemberSetFactId = @MemberSetFactId AND FreeFieldId = -6),'')
773 WHEN '1' THEN 1
774 WHEN '3' THEN 3
775 WHEN '6' THEN 6
776 WHEN '12' THEN 12
777 ELSE 1
778 END
779SET @OdpisovaSkupina = (SELECT Value FROM p4s.FreeFieldValues2 WHERE MemberSetFactId = @MemberSetFactId AND FreeFieldId = -5)
780
781-- stanoveni casove mnoziny
782PRINT '@TimeDetailMemberListId = ' + ISNULL(CAST(@TimeDetailMemberListId AS varchar),'NULL')
783PRINT '@TimeDetailLevelId = ' + ISNULL(CAST(@TimeDetailLevelId AS varchar),'NULL')
784
785SET @FirstDBDate = (SELECT MIN(FirstDate) FROM p4s.TimeMembers WHERE MemberListId = @TimeDetailMemberListId)
786SET @LastDBDate = (SELECT MAX(LastDate) FROM p4s.TimeMembers WHERE MemberListId = @TimeDetailMemberListId)
787
788PRINT '@FirstDBDate = ' + ISNULL(CAST(@FirstDBDate AS varchar),'NULL')
789PRINT '@LastDBDate = ' + ISNULL(CAST(@LastDBDate AS varchar),'NULL')
790PRINT '@DateFrom = ' + ISNULL(CAST(@DateFrom AS varchar),'NULL')
791PRINT '@DateTo = ' + ISNULL(CAST(@DateTo AS varchar),'NULL')
792
793IF @DateFrom < @FirstDBDate SET @DateFrom = @FirstDBDate
794IF @DateTo > @LastDBDate SET @DateTo = @LastDBDate
795
796SET @FromTimeMemberId = (SELECT Id FROM p4s.TimeMembers t WHERE t.MemberListId = @TimeDetailMemberListId AND @DateFrom BETWEEN t.FirstDate AND t.LastDate)
797SET @ToTimeMemberId = (SELECT Id FROM p4s.TimeMembers t WHERE t.MemberListId = @TimeDetailMemberListId AND @DateTo BETWEEN t.FirstDate AND t.LastDate)
798
799PRINT '@DateFrom = ' + ISNULL(CAST(@DateFrom AS varchar),'NULL')
800PRINT '@DateTo = ' + ISNULL(CAST(@DateTo AS varchar),'NULL')
801PRINT '@Count = ' + ISNULL(CAST(@Count AS varchar),'NULL')
802PRINT '@Frequency = ' + ISNULL(CAST(@Frequency AS varchar),'NULL')
803PRINT '@ValueSize = ' + ISNULL(CAST(@ValueSize AS varchar),'NULL')
804PRINT '@FromTimeMemberId = ' + ISNULL(CAST(@FromTimeMemberId AS varchar),'NULL')
805PRINT '@ToTimeMemberId = ' + ISNULL(CAST(@ToTimeMemberId AS varchar),'NULL')
806
807IF @RuleId IN (1,3) -- od - do
808BEGIN
809 ;WITH t AS (
810 SELECT
811 ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber
812 ,Id
813 FROM p4s.TimeMembers
814 WHERE MemberListId = @TimeDetailMemberListId
815 AND Id BETWEEN @FromTimeMemberId AND @ToTimeMemberId
816 )
817 INSERT INTO @TimeMembers (Id)
818 SELECT Id FROM t WHERE (RowNumber - 1) % @Frequency = 0
819
820 SET @RealCount = (SELECT COUNT(*) FROM @TimeMembers)
821END
822
823IF @RuleId IN (2,4) -- od - pocet
824BEGIN
825 ;WITH t AS (
826 SELECT
827 ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber
828 ,Id
829 FROM p4s.TimeMembers
830 WHERE MemberListId = @TimeDetailMemberListId
831 AND Id >= @FromTimeMemberId
832 )
833 ,tf AS (
834 SELECT
835 ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber
836 ,Id
837 FROM t
838 WHERE (RowNumber - 1) % @Frequency = 0
839 )
840
841 INSERT INTO @TimeMembers (Id)
842 SELECT Id FROM tf
843 WHERE RowNumber <= @Count
844
845 SET @RealCount = (SELECT COUNT(*) FROM @TimeMembers)
846END
847
848IF @RuleId IN (1,2,3,4)
849BEGIN
850 SET @ValueToSave = CASE
851 WHEN @RealCount IS NULL THEN 0
852 WHEN @RuleId IN (1,2) THEN @Value / @ValueSize * @Frequency
853 WHEN @RuleId IN (3,4) THEN @Value / (@RealCount / @ValueSize) * @Frequency
854 ELSE NULL
855 END
856
857 INSERT INTO @Data (ModelId, IndicatorId, TimeId, PlanId, DimMemberId, SubIndicatorId, ValueTypeId, Value)
858 SELECT
859 @ModelId
860 ,@IndicatorId
861 ,t.Id
862 ,@PlanId
863 ,@MemberSetId
864 ,111
865 ,LEFT(CAST(t.Id AS varchar),4) + '0' + CAST(@PlanId AS char(2)) -- ValueTypeId se musime casem zbavit
866 ,@ValueToSave
867 FROM @TimeMembers t
868END
869
870IF @RuleId = 5 -- odpisy
871BEGIN
872 DECLARE @YearValues TABLE (PeriodOrder int NOT NULL, ValuePct float NOT NULL, YearId int NULL, YearValue float NULL)
873 INSERT INTO @YearValues (PeriodOrder, ValuePct, YearValue)
874 SELECT PeriodOrder, Value, @Value * Value/100
875 FROM p4s.RuleTimeRows
876 WHERE RuleId = @RuleId
877 AND SubRuleId = @OdpisovaSkupina
878
879 -- naplneni vazebni tabulky ROK-MESIC
880 DECLARE @X p4s.TIDs, @VT p4s.TIds
881 DECLARE @MonthValues TABLE (MonthId int, YearId int, MonthValue float)
882 INSERT INTO @MonthValues (MonthId, YearId)
883 EXEC p4s.GetTimeHierarchyMembers
884 @HierarchyId = @TimeHierarchyId
885 ,@OutputType = 4
886 ,@DetailLevel = @TimeDetailLevelId
887 ,@FilterType = 0
888 ,@FilterSet = @X
889 ,@ValueTypes = @VT
890 ,@SelectToOutput = 1
891 ,@AggLevel = 1
892 ,@PeriodCount = 0
893
894 ;WITH yy AS (
895 SELECT
896 DISTINCT YearId
897 FROM @MonthValues
898 WHERE MonthId >= @FromTimeMemberId
899 )
900 ,xx AS (
901 SELECT
902 ROW_NUMBER() OVER(ORDER BY YearId) AS poradi
903 ,YearId
904 FROM yy
905 )
906 UPDATE y SET YearId = xx.YearId
907 FROM @YearValues y
908 JOIN xx ON xx.poradi = y.PeriodOrder
909
910 -- promazani mesicni tabulky - smazeme mesice pro roky, ktere nejsou v rocni tabulce
911 DELETE mv
912 FROM @MonthValues mv
913 LEFT JOIN @YearValues yv ON yv.YearId = mv.YearId
914 WHERE yv.YearId IS NULL OR MonthId < @FromTimeMemberId
915
916 -- vypocet mesicnich odpisu
917 ;WITH y AS (
918 SELECT
919 m.YearId
920 ,COUNT(*) AS MonthCount
921 ,MIN(y.YearValue) AS YearValue
922 FROM @MonthValues m
923 JOIN @YearValues y ON y.YearId = m.YearId
924 GROUP BY m.YearId
925 )
926 UPDATE m SET
927 MonthValue = y.YearValue / y.MonthCount
928 FROM @MonthValues m
929 JOIN y ON y.YearId = m.YearId
930
931 INSERT INTO @Data (ModelId, IndicatorId, TimeId, PlanId, DimMemberId, SubIndicatorId, ValueTypeId, Value)
932 SELECT
933 @ModelId
934 ,@IndicatorId
935 ,m.MonthId
936 ,@PlanId
937 ,@MemberSetId
938 ,111
939 ,LEFT(CAST(m.YearId AS varchar),4) + '0' + CAST(@PlanId AS char(2)) -- ValueTypeId se musime casem zbavit
940 ,m.MonthValue
941 FROM @MonthValues m
942
943END
944
945-- ulozeni do faktove tabulky
946IF @OutputType = 0
947BEGIN
948 EXEC p4s.ModifyFacts
949 @Data = @Data
950 ,@UserId = 0 -- default 0
951 ,@ExecFromTypeId = 0 -- default 0
952 ,@ExecFromId = 0 -- default 0
953 ,@PlacementTypeId = 1 -- default 1
954
955 RETURN
956END
957-- zobrazeni vysledku
958IF @OutputType = 1 SELECT * FROM @Data
959
960END
961
962GO
963
964IF EXISTS ( SELECT *
965 FROM sysobjects
966 WHERE id = object_id(N'[p4s].[ModifyMemberSet]')
967 and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
968BEGIN
969 DROP PROCEDURE [p4s].[ModifyMemberSet]
970END
971GO
972
973/****** Object: StoredProcedure [p4s].[ModifyMemberSet] Script Date: 21.11.2018 12:29:58 ******/
974SET ANSI_NULLS ON
975GO
976SET QUOTED_IDENTIFIER ON
977GO
978CREATE PROCEDURE [p4s].[ModifyMemberSet]
979 @ModelId int
980 ,@MemberSetId bigint = 0
981 ,@DimMembers p4s.TTwoIds READONLY
982 ,@FreeFields p4s.TIdValue READONLY
983 ,@Name nvarchar(50)
984 ,@Description nvarchar(max)
985 ,@IndicatorId int
986 ,@PlanId int
987 ,@TimeId int = 0
988 ,@IndRuleId int = 0
989 ,@DimRuleId int = 0
990 ,@TimeRuleId int = 0
991 ,@Value float
992 ,@CodePrefix varchar(10) = ''
993 ,@CodeNumberCount tinyint = 0
994 ,@RunMode int = 0
995 ,@ParentMemberSetId bigint = 0
996
997AS
998
999-- =================================================
1000-- Author: PJE
1001-- Create date: 28.08.2018
1002-- Description: -- Procedura se kompletně postará o uloženà dat z položkové šablony. Konkrétně udělá toto:
1003-- -- pokud jeÅ¡tÄ› neexistuje Id detailu pro danou kombinaci dimenzÃ, založÃ:
1004-- -- novou větu v Members
1005-- -- potÅ™ebný poÄet nových vÄ›t v kombinaÄnà tabulce MemberSetDetails
1006-- -- potÅ™ebný poÄet nových vÄ›t v tabulce FreeFieldValues
1007-- -- novou větu v tabulce MemberFacts
1008-- -- dále spustà nastavená pravidla (v paměti) a vyrobà tak věty ve Facts (pomocà ModifyFacts)
1009-- -- pokud již Id existuje, provádà se update/insert jen tam kde to má smysl
1010-- -- prvnà verze poÄÃtá s oÅ™ezaným návrhem Å™eÅ¡enÃ, kdy každý řádek dostává svůj ID
1011-- Changelog:
1012-- 20.09.2018 - PJE - osetreno plneni noveho pole DimRuleParentMemberId
1013-- - pridan parametr @RunMode (0 - normalni, 1 - z dimenzniho pravidla)
1014-- - pridan parametr @ParentMemberId - v pripade spusteni z pravidla je zde Id parenta
1015-- 14.11.2018 - PJE - předěláno na novou podobu metadat !!
1016
1017-- =================================================
1018
1019BEGIN
1020
1021-- pro testy START
1022/*
1023DECLARE
1024 @ModelId int = 29
1025 ,@MemberSetId bigint = 0
1026 ,@DimMembers p4s.TTwoIds
1027 ,@FreeFields p4s.TIdValue
1028 ,@Name nvarchar(50) =N'Pokus 002 PJE'
1029 ,@Description nvarchar(max) = N''
1030 ,@IndicatorId int = 15
1031 ,@PlanId int = 40
1032 ,@TimeId int --= 2018000000
1033 ,@IndRuleId int --= 0
1034 ,@DimRuleId int = 1002
1035 ,@TimeRuleId int = 1
1036 ,@Value float = 25000000
1037 ,@CodePrefix varchar(10) = 'XXX'
1038 ,@CodeNumberCount tinyint = 7
1039 ,@RunMode int = 0
1040 ,@ParentMemberSetId int
1041
1042--INSERT INTO @DimMembers (Id1, Id2) VALUES
1043--(4,47),(5,7),(6,219)
1044declare @p5 p4s.TTwoIds
1045insert into @p5 values(42,273)
1046insert into @p5 values(43,275)
1047insert into @p5 values(44,279)
1048
1049declare @p6 p4s.TIdValue
1050insert into @p6 values(-6,N'3')
1051insert into @p6 values(-4,N'MÄ›sÃÄnÃ')
1052insert into @p6 values(-2,N'20170629')
1053insert into @p6 values(-1,N'20170301')
1054
1055INSERT INTO @DimMembers SELECT * FROM @p5
1056INSERT INTO @FreeFields SELECT * FROM @p6
1057
1058SET @ModelId=33
1059SET @MemberSetId=37
1060
1061SET @Name=N'Polozka33'
1062SET @Description=N''
1063SET @IndicatorId=8
1064SET @PlanId=10
1065SET @TimeId=NULL
1066SET @IndRuleId=NULL
1067SET @DimRuleId=NULL
1068SET @TimeRuleId=1
1069SET @Value=9999
1070SET @CodePrefix=N'Code'
1071SET @CodeNumberCount=4
1072
1073-- pro testy END
1074*/
1075
1076-- deklarace
1077DECLARE
1078 @HierarchyId int
1079 ,@ActualMemberSetId bigint
1080 ,@NewMemberSetId bigint
1081 ,@ActualMemberSetFactId bigint
1082 ,@NewMemberSetFactId bigint
1083 ,@Data p4s.TFacts
1084 ,@DimRuleParentMemberId int
1085
1086-- naplneni promennych
1087SET @HierarchyId = (SELECT DimHierarchyId FROM p4s.ModelDimensionality WHERE ModelId = @ModelId)
1088
1089-- konec
1090PRINT '@HierarchyId = ' + CAST(@HierarchyId AS varchar)
1091
1092SET @ActualMemberSetId = @MemberSetId
1093
1094IF @RunMode = 1 SET @DimRuleParentMemberId = @ParentMemberSetId
1095
1096PRINT '@ParentMemberSetId = ' + ISNULL(CAST(@ParentMemberSetId AS varchar),'NULL')
1097
1098IF @ActualMemberSetId = 0
1099--TODO
1100-- zalozeni nove vety v Members/Update popisu
1101BEGIN
1102 PRINT 'Zakládám nový detailnà prvek'
1103
1104 INSERT INTO p4s.MemberSets (ModelId) VALUES (@ModelId)
1105
1106 SET @NewMemberSetId = @@IDENTITY
1107
1108 PRINT '@NewMemberSetId = ' + ISNULL(CAST(@NewMemberSetId AS varchar),'NULL')
1109 -- zalozeni vet v MemberSetDetails
1110 INSERT INTO p4s.MemberSetDetails (MemberSetId, MemberListId, MemberId)
1111 SELECT @NewMemberSetId, Id1, Id2 FROM @DimMembers WHERE Id1 <> 0
1112
1113 -- ulozeni do MemberFacts
1114 INSERT INTO p4s.MemberSetFacts (ModelId, MemberSetId, TimeId, IndicatorId, PlanId, DimRuleId, TimeRuleId, IndRuleId, Value, DimRuleParentMemberId)
1115 VALUES (@ModelId, @NewMemberSetId, @TimeId, @IndicatorId, @PlanId, @DimRuleId, @TimeRuleId, @IndRuleId, @Value, @DimRuleParentMemberId)
1116
1117 SET @NewMemberSetFactId = @@IDENTITY
1118
1119 PRINT '@NewMemberSetFactId = ' + ISNULL(CAST(@NewMemberSetFactId AS varchar),'NULL')
1120
1121 SET @ActualMemberSetFactId = @NewMemberSetFactId -- ulozeni hodnot free poli
1122
1123 -- zatim umime ulozit jen primo vlozenou hodnotu
1124 -- TODO - naucit se ukladat odkaz na FF z memberlistu
1125 INSERT INTO p4s.FreeFieldValues2 (MemberSetFactId, FreeFieldId, FFValueTypeId, Value)
1126 SELECT @NewMemberSetFactId, Id, 1, Value FROM @FreeFields
1127
1128
1129
1130END ELSE
1131BEGIN
1132 PRINT 'Update'
1133 PRINT '@ActualMemberSetId = ' + ISNULL(CAST(@ActualMemberSetId AS varchar),'NULL')
1134
1135 -- TODO: zatim stale plati omezeni 1 memberset = 1 membersetFacts
1136 SET @ActualMemberSetFactId = (SELECT Id FROM p4s.MemberSetFacts WHERE MemberSetId = @ActualMemberSetId)
1137
1138 PRINT '@ActualMemberSetFactId = ' + ISNULL(CAST(@ActualMemberSetFactId AS varchar),'NULL')
1139
1140 ---- update vet v MemberSetDetails -- muzeme predpokladat, ze u existujici transakce je doplnen dimenzionalni prostor
1141 ;WITH msd_actual AS (SELECT Id, MemberSetId, MemberListId, MemberId FROM p4s.MemberSetDetails WHERE MemberSetId = @ActualMemberSetId)
1142
1143 MERGE msd_actual AS t
1144 USING @DimMembers AS s ON t.MemberSetId = @ActualMemberSetId AND t.MemberListId = s.Id1
1145
1146 WHEN MATCHED THEN UPDATE SET MemberId = s.Id2
1147
1148 WHEN NOT MATCHED BY TARGET THEN INSERT (MemberSetId, MemberListId, MemberId) VALUES (@ActualMemberSetId, s.Id1, s.Id2)
1149
1150 WHEN NOT MATCHED BY SOURCE THEN DELETE
1151 ;
1152 --select * from @FreeFields
1153 ---- update hodnot free poli-- muzeme predpokladat, ze u existujici transakce je doplnen dimenzionalni prostor
1154 ---- zatim umime ulozit jen primo vlozenou hodnotu
1155 ---- TODO - naucit se ukladat odkaz na FF z memberlistu
1156 ;WITH ff_actual AS (SELECT Id, MemberSetFactId, FreeFieldId, FFValueTypeId, Value FROM p4s.FreeFieldValues2 WHERE MemberSetFactId = @ActualMemberSetFactId)
1157
1158 MERGE ff_actual AS t
1159 USING @FreeFields AS s ON t.MemberSetFactId = @ActualMemberSetFactId AND t.FreeFieldId = s.Id
1160
1161 WHEN MATCHED THEN UPDATE SET Value = s.Value
1162
1163 WHEN NOT MATCHED BY TARGET THEN INSERT (MemberSetFactId, FreeFieldId, FFValueTypeId, Value) VALUES (@ActualMemberSetFactId, s.Id, 1, s.Value)
1164 ;
1165
1166 -- -- update v MemberFacts -- zatim vychazime z toho, ze jedna transakce ma tady JEDEN radek (chova se to jako prodlouzeni radku v XLS]
1167 -- -- TODO - az jednou prejdeme na viceradku k jednomu idu (kvuli ukazatelum), bude treba stanovit virtualni PK
1168 UPDATE mf SET
1169 TimeId = @TimeId
1170 ,IndicatorId = @IndicatorId
1171 ,PlanId = @PlanId
1172 ,DimRuleId = @DimRuleId
1173 ,TimeRuleId = @TimeRuleId
1174 ,IndRuleId = @IndRuleId
1175 ,Value = @Value
1176 FROM p4s.MemberSetFacts mf
1177 WHERE Id = @ActualMemberSetFactId
1178
1179 END
1180
1181-- spusteni pravidel
1182-- nejprve se musi pustit pravidlo na dimenze, protoze to povede ke vzniku novych prvku v detailni dimenzi ?? -- ne nutne
1183
1184
1185-- pravidlo pro cas
1186IF @TimeRuleId = 0 -- primy zapis hodnoty
1187BEGIN
1188 IF ISNULL(@TimeId,0) = 0
1189 BEGIN
1190 PRINT 'Neni vyplnen cas'
1191 END ELSE
1192 BEGIN
1193
1194 PRINT 'Ukladam hodnotu'
1195
1196 INSERT INTO @Data (ModelId, IndicatorId, TimeId, PlanId, DimMemberId, SubIndicatorId, ValueTypeId, Value)
1197 SELECT
1198 @ModelId
1199 ,@IndicatorId
1200 ,@TimeId
1201 ,@PlanId
1202 ,@ActualMemberSetId
1203 ,111
1204 ,LEFT(CAST(@TimeId AS varchar),4) + '0' + CAST(@PlanId AS char(2)) -- ValueTypeId se musime casem zbavit
1205 ,@Value
1206
1207 EXEC p4s.ModifyFacts
1208 @Data = @Data
1209 ,@UserId = 0 -- default 0
1210 ,@ExecFromTypeId = 0 -- default 0
1211 ,@ExecFromId = 0 -- default 0
1212 ,@PlacementTypeId = 1 -- default 1
1213
1214 PRINT 'zapis hodnoty'
1215 END
1216END ELSE
1217BEGIN
1218 PRINT 'Spoustim pravidlo pro Time'
1219 EXEC p4s.RuleTimeRun
1220 @MemberSetFactId = @ActualMemberSetFactId
1221
1222END
1223
1224-- pravidlo pro dimenzi
1225/*
1226IF @DimRuleId <> 0
1227BEGIN
1228 EXEC p4s.RuleDimRun
1229 @MemberFactId = @MemberFactId
1230 ,@CodePrefix = @CodePrefix
1231 ,@CodeNumberCount = @CodeNumberCount
1232
1233END
1234*/
1235/*
1236-- pravidlo pro ukazatel
1237IF @IndRuleId <> 0
1238BEGIN
1239 EXEC p4s.RuleIndRun
1240 @ModelId = @ModelId
1241 ,@RuleId = @IndRuleId
1242 ,@MemberId = @DetailMemberId
1243END
1244*/
1245-- ulozeni do faktu
1246
1247END