API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 19th, 2017
82
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 55.17 KB | None | 0 0
raw download clone embed print report
  1. --Title: AmbicaBI - Update script
  2. --Version from: 2.5.5
  3. --Version to: 2.5.6
  4. --Comment: jpr - kumulace hotfixů
  5. --EasyProject tasks:
  6. --Date: 13.06.2017
  7. --Generated by: jpr
  8. ---------------------------------------------------------------------------------------------------
  9. --Title: AmbicaBI - Update script
  10. --Version to: 2.5.5
  11. --Hotfix no.: 1
  12. --Comment: upravy pro olap - custom dim fields
  13. --EasyProject tasks:
  14. --Date: 21.05.2017
  15. --Generated by: jpr
  16. GO
  17. USE [AmbicaBI]
  18. GO
  19. UPDATE [amb].[VersionNew] SET  HotfixDb = 1
  20.  
  21.  
  22. GO
  23. IF NOT EXISTS (SELECT TOP 1 1 FROM amb.Plan4SParameters WHERE ParameterName = 'db_source_name') BEGIN
  24.     INSERT INTO amb.Plan4SParameters VALUES ('db_source_name', (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'olap_source_db_name')+'_sources')
  25. END
  26.  
  27. GO
  28.  
  29. /****** Object:  StoredProcedure [olap].[DimViewGenerator]    Script Date: 30. 5. 2017 14:38:03 ******/
  30. SET ANSI_NULLS ON
  31. GO
  32. SET QUOTED_IDENTIFIER ON
  33. GO
  34. ALTER PROCEDURE [olap].[DimViewGenerator]
  35. AS
  36. BEGIN
  37.  
  38. DECLARE
  39.      @query             varchar(max) = ''
  40.     ,@code              varchar(20)
  41.     ,@name              varchar(20)
  42.     ,@MemberListId      int
  43.  
  44. DECLARE cml CURSOR FOR
  45. SELECT Id, Code, LOWER(Code) FROM p4s.MemberLists ORDER BY Id
  46.  
  47. OPEN cml
  48. FETCH NEXT FROM cml INTO @MemberListId, @Code, @Name
  49. WHILE @@FETCH_STATUS = 0
  50. BEGIN
  51.  
  52.     SET @query = '
  53.  
  54.     IF EXISTS (SELECT TOP 1 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''VD_' + @Code + ''' AND TABLE_SCHEMA = ''olap'' AND TABLE_TYPE = ''VIEW'')
  55.     DROP VIEW olap.VD_' + @Code
  56.     EXEC (@query)
  57.  
  58.     SET @query = '
  59.     CREATE VIEW olap.VD_' + @Code + '
  60.     AS
  61.     SELECT
  62.          Id                                         AS ' + @Name + '_id
  63.         ,Code                                       AS ' + @Name + '_code
  64.         ,Name                                       AS ' + @Name + '_name
  65.         ,RTRIM(Name) + '' ('' + RTRIM(Code) +  '')''    AS ' + @Name + '_olapname
  66.         ,Fchar01                                    AS ' + @name + '_fchar01
  67.         ,Fchar02                                    AS ' + @name + '_fchar02
  68.         ,Fchar03                                    AS ' + @name + '_fchar03
  69.         ,Fchar04                                    AS ' + @name + '_fchar04
  70.         ,Fchar05                                    AS ' + @name + '_fchar05
  71.         ,Fchar06                                    AS ' + @name + '_fchar06
  72.         ,Fchar07                                    AS ' + @name + '_fchar07
  73.         ,Fchar08                                    AS ' + @name + '_fchar08
  74.         ,Fchar09                                    AS ' + @name + '_fchar09
  75.         ,Fchar10                                    AS ' + @name + '_fchar10
  76.         ,Fint01                                     AS ' + @name + '_fint01
  77.         ,Fint02                                     AS ' + @name + '_fint02
  78.         ,Fint03                                     AS ' + @name + '_fint03
  79.         ,Fint04                                     AS ' + @name + '_fint04
  80.         ,Fint05                                     AS ' + @name + '_fint05
  81.         ,Fdate01                                    AS ' + @name + '_fdate01
  82.         ,Fdate02                                    AS ' + @name + '_fdate02
  83.         ,Fdate03                                    AS ' + @name + '_fdate03
  84.         ,Fdate04                                    AS ' + @name + '_fdate04
  85.         ,Fdate05                                    AS ' + @name + '_fdate05
  86.         ,Ffloat01                                   AS ' + @name + '_ffloat01
  87.         ,Ffloat02                                   AS ' + @name + '_ffloat02
  88.         ,Ffloat03                                   AS ' + @name + '_ffloat03
  89.         ,Ffloat04                                   AS ' + @name + '_ffloat04
  90.         ,Ffloat05                                   AS ' + @name + '_ffloat05
  91.         ,SortOrder
  92.     FROM p4s.Members
  93.     WHERE MemberListId = ' + CAST(@MemberListId AS varchar) + '
  94.     UNION ALL
  95.     SELECT 0,''n/a'',''Neuvedeno'', ''Neuvedeno'','''','''','''','''','''','''','''','''','''','''',0,0,0,0,0,1900-1-1,1900-1-1,1900-1-1,1900-1-1,1900-1-1,0,0,0,0,0,-1'
  96.  
  97.     --SET @query = '
  98.     --CREATE VIEW olap.VD_' + @Code + '
  99.     --AS
  100.     --SELECT
  101.     --   Id                                         AS ' + @Name + '_id
  102.     --  ,Code                                       AS ' + @Name + '_code
  103.     --  ,Name                                       AS ' + @Name + '_name
  104.     --  ,RTRIM(Name) + '' ('' + RTRIM(Code) +  '')''    AS ' + @Name + '_olapname
  105.     --  ,SortOrder
  106.     --FROM p4s.Members
  107.     --WHERE MemberListId = ' + CAST(@MemberListId AS varchar) + '
  108.     --UNION ALL
  109.     --SELECT 0,''n/a'',''Neuvedeno'', ''Neuvedeno'', -1'
  110.  
  111.  
  112.     EXEC (@query)
  113.  
  114.     FETCH NEXT FROM cml INTO @MemberListId, @Code, @Name
  115.  
  116. END
  117.  
  118. CLOSE cml
  119. DEALLOCATE cml
  120.  
  121. END
  122.  
  123. GO
  124.  
  125. IF EXISTS ( SELECT *
  126.             FROM   sysobjects
  127.             WHERE  id = object_id(N'[olap].[GetCustomDimFields]')
  128.                    and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
  129. BEGIN
  130.     DROP PROCEDURE [olap].[GetCustomDimFields]
  131. END
  132.  
  133. GO
  134. /****** Object:  StoredProcedure [olap].[CreateCube]    Script Date: 31. 5. 2017 13:18:58 ******/
  135. SET ANSI_NULLS ON
  136. GO
  137. SET QUOTED_IDENTIFIER ON
  138. GO
  139. CREATE PROCEDURE [olap].[GetCustomDimFields]
  140.     @DimCode            nvarchar(124)
  141. AS
  142. -- =================================================
  143. -- Author:      JPR
  144. -- Create date: 31.05.2017
  145. -- Description:
  146. --             
  147. --             
  148. --             
  149. --             
  150. -- Changelog:  
  151. --  ================================================           
  152. DECLARE
  153.      @ProcedureName         varchar(50) = 'GetCustomDimFields' 
  154.  
  155. BEGIN
  156. TRY
  157.     select  mli.Code,
  158.             FieldName,
  159.             mla.Name,
  160.             Visible,
  161.             case when FieldName like '%fchar%' then 'char'
  162.                  when FieldName like '%fint%' then 'int'
  163.                  when FieldName like '%fdate%' then 'datetime'
  164.                  when FieldName like '%ffloat%' then 'float'
  165.             end as FieldType
  166.     from p4s.MemberLabels mla
  167.     join p4s.MemberLists mli
  168.     on mli.Id = mla.MemberListId
  169.     and Visible = 1
  170.     and Code = @DimCode
  171.  
  172.  
  173.     -- zapis do logu
  174.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  175.     VALUES  (@ProcedureName, 'Podařilo se získat volitelná pole k dimenzi', GetDate())
  176.     RETURN CAST(@@IDENTITY AS INT)
  177. END
  178. TRY
  179. BEGIN CATCH
  180.        
  181.     -- zapis do logu
  182.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  183.     VALUES  (@ProcedureName, 'Nepodařilo se získat volitelná pole k dimenzi', GetDate())
  184.     RETURN CAST(-@@IDENTITY AS INT)
  185. END CATCH
  186.  
  187. --Title: AmbicaBI - Update script
  188. --Version to: 2.5.5
  189. --Hotfix no.: 1
  190. --Comment: upravy pro olap - custom dim fields
  191. --EasyProject tasks:
  192. --Date: 21.05.2017
  193. --Generated by: jpr
  194. GO
  195. USE [AmbicaBI]
  196. GO
  197. UPDATE [amb].[VersionNew] SET  HotfixDb = 2
  198.  
  199. GO
  200.  
  201.  
  202. IF EXISTS ( SELECT *
  203.             FROM   sysobjects
  204.             WHERE  id = object_id(N'[olap].[GetCustomDimFields]')
  205.                    and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
  206. BEGIN
  207.     DROP PROCEDURE [olap].[GetCustomDimFields]
  208. END
  209.  
  210. GO
  211. /****** Object:  StoredProcedure [olap].[CreateCube]    Script Date: 31. 5. 2017 13:18:58 ******/
  212. SET ANSI_NULLS ON
  213. GO
  214. SET QUOTED_IDENTIFIER ON
  215. GO
  216. CREATE PROCEDURE [olap].[GetCustomDimFields]
  217.     @DimCode            nvarchar(124)
  218. AS
  219. -- =================================================
  220. -- Author:      JPR
  221. -- Create date: 31.05.2017
  222. -- Description:
  223. --             
  224. --             
  225. --             
  226. --             
  227. -- Changelog:  
  228. --  ================================================           
  229. DECLARE
  230.      @ProcedureName         varchar(50) = 'GetCustomDimFields' 
  231.  
  232. BEGIN
  233. TRY
  234.     select  mli.Code,
  235.             FieldName,
  236.             mla.Name,
  237.             Visible,
  238.             case when FieldName like '%fchar%' then 'char'
  239.                  when FieldName like '%fint%' then 'int'
  240.                  when FieldName like '%fdate%' then 'datetime'
  241.                  when FieldName like '%ffloat%' then 'float'
  242.             end as FieldType
  243.     from p4s.MemberLabels mla
  244.     join p4s.MemberLists mli
  245.     on mli.Id = mla.MemberListId
  246.     and Visible = 1
  247.     and Code = @DimCode
  248.  
  249.  
  250.     -- zapis do logu
  251.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  252.     VALUES  (@ProcedureName, 'Podařilo se získat volitelná pole k dimenzi', GetDate())
  253.     RETURN CAST(@@IDENTITY AS INT)
  254. END
  255. TRY
  256. BEGIN CATCH
  257.        
  258.     -- zapis do logu
  259.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  260.     VALUES  (@ProcedureName, 'Nepodařilo se získat volitelná pole k dimenzi', GetDate())
  261.     RETURN CAST(-@@IDENTITY AS INT)
  262. END CATCH
  263.  
  264. --Title: AmbicaBI - Update script
  265. --Version to: 2.5.5
  266. --Hotfix no.: 3
  267. --Comment: upravy pro olap - custom dim fields
  268. --EasyProject tasks:
  269. --Date: 21.05.2017
  270. --Generated by: jpr
  271. GO
  272. USE [AmbicaBI]
  273. GO
  274. UPDATE [amb].[VersionNew] SET  HotfixDb = 3
  275.  
  276.  
  277. GO
  278. IF EXISTS ( SELECT *
  279.             FROM   sysobjects
  280.             WHERE  id = object_id(N'[olap].[GetCustomDimFields]')
  281.                    and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
  282. BEGIN
  283.     DROP PROCEDURE [olap].[GetCustomDimFields]
  284. END
  285. GO
  286. /****** Object:  StoredProcedure [olap].[CreateCube]    Script Date: 31. 5. 2017 13:18:58 ******/
  287. SET ANSI_NULLS ON
  288. GO
  289. SET QUOTED_IDENTIFIER ON
  290. GO
  291. CREATE PROCEDURE [olap].[GetCustomDimFields]
  292.     @DimCode            nvarchar(124)
  293. AS
  294. -- =================================================
  295. -- Author:      JPR
  296. -- Create date: 31.05.2017
  297. -- Description:
  298. --             
  299. --             
  300. --             
  301. --             
  302. -- Changelog:  
  303. --  ================================================           
  304. DECLARE
  305.      @ProcedureName         varchar(50) = 'GetCustomDimFields' 
  306.  
  307. BEGIN
  308. TRY
  309.     select  mli.Code,
  310.             FieldName,
  311.             mla.Name,
  312.             Visible,
  313.             case when FieldName like '%fchar%' then 'char'
  314.                  when FieldName like '%fint%' then 'int'
  315.                  when FieldName like '%fdate%' then 'datetime'
  316.                  when FieldName like '%ffloat%' then 'float'
  317.             end as FieldType
  318.     from p4s.MemberLabels mla
  319.     join p4s.MemberLists mli
  320.     on mli.Id = mla.MemberListId
  321.     and Visible = 1
  322.     and Code = @DimCode
  323.  
  324.  
  325.     -- zapis do logu
  326.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  327.     VALUES  (@ProcedureName, 'Podařilo se získat volitelná pole k dimenzi', GetDate())
  328.     RETURN CAST(@@IDENTITY AS INT)
  329. END
  330. TRY
  331. BEGIN CATCH
  332.        
  333.     -- zapis do logu
  334.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  335.     VALUES  (@ProcedureName, 'Nepodařilo se získat volitelná pole k dimenzi', GetDate())
  336.     RETURN CAST(-@@IDENTITY AS INT)
  337. END CATCH
  338.  
  339. --Title: AmbicaBI - Update script
  340. --Version to: 2.5.5
  341. --Hotfix no.: 1
  342. --Comment: upravy pro olap - custom dim fields
  343. --EasyProject tasks:
  344. --Date: 21.05.2017
  345. --Generated by: jpr
  346. GO
  347. USE [AmbicaBI]
  348. GO
  349. UPDATE [amb].[VersionNew] SET  HotfixDb = 4
  350.  
  351. GO
  352.  
  353.  
  354. /****** Object:  StoredProcedure [olap].[DimViewGenerator]    Script Date: 30. 5. 2017 14:38:03 ******/
  355. SET ANSI_NULLS ON
  356. GO
  357. SET QUOTED_IDENTIFIER ON
  358. GO
  359. ALTER PROCEDURE [olap].[DimViewGenerator]
  360. AS
  361. BEGIN
  362.  
  363. DECLARE
  364.      @query             varchar(max) = ''
  365.     ,@code              varchar(20)
  366.     ,@name              varchar(20)
  367.     ,@MemberListId      int
  368.  
  369. DECLARE cml CURSOR FOR
  370. SELECT Id, Code, LOWER(Code) FROM p4s.MemberLists ORDER BY Id
  371.  
  372. OPEN cml
  373. FETCH NEXT FROM cml INTO @MemberListId, @Code, @Name
  374. WHILE @@FETCH_STATUS = 0
  375. BEGIN
  376.  
  377.     SET @query = '
  378.  
  379.     IF EXISTS (SELECT TOP 1 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''VD_' + @Code + ''' AND TABLE_SCHEMA = ''olap'' AND TABLE_TYPE = ''VIEW'')
  380.     DROP VIEW olap.VD_' + @Code
  381.     EXEC (@query)
  382.  
  383.     SET @query = '
  384.     CREATE VIEW olap.VD_' + @Code + '
  385.     AS
  386.     SELECT
  387.          Id                                         AS ' + @Name + '_id
  388.         ,Code                                       AS ' + @Name + '_code
  389.         ,Name                                       AS ' + @Name + '_name
  390.         ,RTRIM(Name) + '' ('' + RTRIM(Code) +  '')''    AS ' + @Name + '_olapname
  391.         ,Fchar01                                    AS ' + @name + '_fchar01
  392.         ,Fchar02                                    AS ' + @name + '_fchar02
  393.         ,Fchar03                                    AS ' + @name + '_fchar03
  394.         ,Fchar04                                    AS ' + @name + '_fchar04
  395.         ,Fchar05                                    AS ' + @name + '_fchar05
  396.         ,Fchar06                                    AS ' + @name + '_fchar06
  397.         ,Fchar07                                    AS ' + @name + '_fchar07
  398.         ,Fchar08                                    AS ' + @name + '_fchar08
  399.         ,Fchar09                                    AS ' + @name + '_fchar09
  400.         ,Fchar10                                    AS ' + @name + '_fchar10
  401.         ,Fint01                                     AS ' + @name + '_fint01
  402.         ,Fint02                                     AS ' + @name + '_fint02
  403.         ,Fint03                                     AS ' + @name + '_fint03
  404.         ,Fint04                                     AS ' + @name + '_fint04
  405.         ,Fint05                                     AS ' + @name + '_fint05
  406.         ,Fdate01                                    AS ' + @name + '_fdate01
  407.         ,Fdate02                                    AS ' + @name + '_fdate02
  408.         ,Fdate03                                    AS ' + @name + '_fdate03
  409.         ,Fdate04                                    AS ' + @name + '_fdate04
  410.         ,Fdate05                                    AS ' + @name + '_fdate05
  411.         ,Ffloat01                                   AS ' + @name + '_ffloat01
  412.         ,Ffloat02                                   AS ' + @name + '_ffloat02
  413.         ,Ffloat03                                   AS ' + @name + '_ffloat03
  414.         ,Ffloat04                                   AS ' + @name + '_ffloat04
  415.         ,Ffloat05                                   AS ' + @name + '_ffloat05
  416.         ,SortOrder
  417.     FROM p4s.Members
  418.     WHERE MemberListId = ' + CAST(@MemberListId AS varchar) + '
  419.     UNION ALL
  420.     SELECT 0,''n/a'',''Neuvedeno'', ''Neuvedeno'','''','''','''','''','''','''','''','''','''','''',0,0,0,0,0,1900-1-1,1900-1-1,1900-1-1,1900-1-1,1900-1-1,0,0,0,0,0,-1'
  421.  
  422.     --SET @query = '
  423.     --CREATE VIEW olap.VD_' + @Code + '
  424.     --AS
  425.     --SELECT
  426.     --   Id                                         AS ' + @Name + '_id
  427.     --  ,Code                                       AS ' + @Name + '_code
  428.     --  ,Name                                       AS ' + @Name + '_name
  429.     --  ,RTRIM(Name) + '' ('' + RTRIM(Code) +  '')''    AS ' + @Name + '_olapname
  430.     --  ,SortOrder
  431.     --FROM p4s.Members
  432.     --WHERE MemberListId = ' + CAST(@MemberListId AS varchar) + '
  433.     --UNION ALL
  434.     --SELECT 0,''n/a'',''Neuvedeno'', ''Neuvedeno'', -1'
  435.  
  436.  
  437.     EXEC (@query)
  438.  
  439.     FETCH NEXT FROM cml INTO @MemberListId, @Code, @Name
  440.  
  441. END
  442.  
  443. CLOSE cml
  444. DEALLOCATE cml
  445.  
  446. END
  447.  
  448.  
  449. GO
  450. IF EXISTS ( SELECT *
  451.             FROM   sysobjects
  452.             WHERE  id = object_id(N'[olap].[GetCustomDimFields]')
  453.                    and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
  454. BEGIN
  455.     DROP PROCEDURE [olap].[GetCustomDimFields]
  456. END
  457. GO
  458. /****** Object:  StoredProcedure [olap].[CreateCube]    Script Date: 31. 5. 2017 13:18:58 ******/
  459. SET ANSI_NULLS ON
  460. GO
  461. SET QUOTED_IDENTIFIER ON
  462. GO
  463. CREATE PROCEDURE [olap].[GetCustomDimFields]
  464.     @DimCode            nvarchar(124)
  465. AS
  466. -- =================================================
  467. -- Author:      JPR
  468. -- Create date: 31.05.2017
  469. -- Description:
  470. --             
  471. --             
  472. --             
  473. --             
  474. -- Changelog:  
  475. --  ================================================           
  476. DECLARE
  477.      @ProcedureName         varchar(50) = 'GetCustomDimFields' 
  478.  
  479. BEGIN
  480. TRY
  481.     select  mli.Code,
  482.             FieldName,
  483.             mla.Name,
  484.             Visible,
  485.             case when FieldName like '%fchar%' then 'char'
  486.                  when FieldName like '%fint%' then 'int'
  487.                  when FieldName like '%fdate%' then 'datetime'
  488.                  when FieldName like '%ffloat%' then 'float'
  489.             end as FieldType
  490.     from p4s.MemberLabels mla
  491.     join p4s.MemberLists mli
  492.     on mli.Id = mla.MemberListId
  493.     and Visible = 1
  494.     and Code = @DimCode
  495.  
  496.  
  497.     -- zapis do logu
  498.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  499.     VALUES  (@ProcedureName, 'Podařilo se získat volitelná pole k dimenzi', GetDate())
  500.     RETURN CAST(@@IDENTITY AS INT)
  501. END
  502. TRY
  503. BEGIN CATCH
  504.        
  505.     -- zapis do logu
  506.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  507.     VALUES  (@ProcedureName, 'Nepodařilo se získat volitelná pole k dimenzi', GetDate())
  508.     RETURN CAST(-@@IDENTITY AS INT)
  509. END CATCH
  510.  
  511. --Title: AmbicaBI - Update script
  512. --Version to: 2.5.5
  513. --Hotfix no.: 1
  514. --Comment: upravy pro olap - custom dim fields
  515. --EasyProject tasks:
  516. --Date: 21.05.2017
  517. --Generated by: jpr
  518. GO
  519. USE [AmbicaBI]
  520. GO
  521. UPDATE [amb].[VersionNew] SET  HotfixDb = 5
  522.  
  523. GO
  524. /****** Object:  StoredProcedure [olap].[GetCustomDimFields]    Script Date: 31. 5. 2017 15:24:54 ******/
  525. SET ANSI_NULLS ON
  526. GO
  527. SET QUOTED_IDENTIFIER ON
  528. GO
  529. ALTER PROCEDURE [olap].[GetCustomDimFields]
  530.     @DimCode            nvarchar(124)
  531. AS
  532. BEGIN      
  533.     DECLARE
  534.          @ProcedureName         varchar(50) = 'GetCustomDimFields' 
  535.  
  536.     BEGIN
  537.     TRY
  538.         select  mli.Code,
  539.                 lower(mli.Code)+'_'+FieldName as FieldName,
  540.                 mla.Name,
  541.                 Visible,
  542.                 case when FieldName like '%fchar%' then 'char'
  543.                      when FieldName like '%fint%' then 'int'
  544.                      when FieldName like '%fdate%' then 'datetime'
  545.                      when FieldName like '%ffloat%' then 'float'
  546.                 end as FieldType
  547.         from p4s.MemberLabels mla
  548.         join p4s.MemberLists mli
  549.         on mli.Id = mla.MemberListId
  550.         and Visible = 1
  551.         and Code = @DimCode
  552.  
  553.  
  554.         -- zapis do logu
  555.         INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  556.         VALUES  (@ProcedureName, 'Podařilo se získat volitelná pole k dimenzi', GetDate())
  557.         RETURN CAST(@@IDENTITY AS INT)
  558.     END
  559.     TRY
  560.     BEGIN CATCH
  561.        
  562.         -- zapis do logu
  563.         INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  564.         VALUES  (@ProcedureName, 'Nepodařilo se získat volitelná pole k dimenzi', GetDate())
  565.         RETURN CAST(-@@IDENTITY AS INT)
  566.     END CATCH
  567. END
  568. --Title: AmbicaBI - Update script
  569. --Version to: 2.5.5
  570. --Hotfix no.: 6
  571. --Comment: rozsireni importnich procedur
  572. --EasyProject tasks: #3021
  573. --Date: 21.05.2017
  574. --Generated by: jpr
  575. GO
  576. USE [AmbicaBI]
  577. GO
  578. UPDATE [amb].[VersionNew] SET  HotfixDb = 6
  579.  
  580. GO
  581.  
  582. /****** Object:  UserDefinedTableType [import].[SourceMembers]    Script Date: 1. 6. 2017 10:23:40 ******/
  583. DROP TYPE [import].[SourceMembers]
  584. CREATE TYPE [import].[SourceMembers] AS TABLE(
  585.     [Code] [varchar](20) NOT NULL,
  586.     [Name] [nvarchar](255) NOT NULL,
  587.     [SortOrder] [int] NULL,
  588.     [Fchar01] [varchar](20) NULL,
  589.     [Fchar02] [varchar](20) NULL,
  590.     [Fchar03] [varchar](20) NULL,
  591.     [Fchar04] [varchar](20) NULL,
  592.     [Fchar05] [varchar](20) NULL,
  593.     [Fchar06] [varchar](50) NULL,
  594.     [Fchar07] [varchar](50) NULL,
  595.     [Fchar08] [varchar](50) NULL,
  596.     [Fchar09] [varchar](50) NULL,
  597.     [Fchar10] [varchar](50) NULL,
  598.     [Fint01] [int] NULL,
  599.     [Fint02] [int] NULL,
  600.     [Fint03] [int] NULL,
  601.     [Fint04] [int] NULL,
  602.     [Fint05] [int] NULL,
  603.     [Fdate01] [datetime] NULL,
  604.     [Fdate02] [datetime] NULL,
  605.     [Fdate03] [datetime] NULL,
  606.     [Fdate04] [datetime] NULL,
  607.     [Fdate05] [datetime] NULL,
  608.     [Ffloat01] [float] NULL,
  609.     [Ffloat02] [float] NULL,
  610.     [Ffloat03] [float] NULL,
  611.     [Ffloat04] [float] NULL,
  612.     [Ffloat05] [float] NULL
  613. )
  614. GO
  615.  
  616. SET ANSI_NULLS ON
  617. GO
  618. SET QUOTED_IDENTIFIER ON
  619. GO
  620. ---- =================================================
  621. ---- Author:        PJE
  622. ---- Create date: podzim 2016
  623. ---- Description:   Procedura importuje prvky jednoho memberlistu
  624. ---- Changelog: 2017-02-13 PJE  Oprava
  625.                
  626. ---- =================================================
  627. ALTER PROCEDURE [import].[MembersFromSource]
  628.      @MemberListId int = 0
  629.     ,@BlockMissing bit = 0
  630.     ,@RunSourceTest bit = 1
  631.  
  632. AS
  633. BEGIN
  634.  
  635. DECLARE
  636.      @MemberListCode varchar(20)
  637.     ,@tblSource import.SourceMembers
  638.     ,@query varchar(max)
  639.     ,@db_source_name varchar(255)
  640.  
  641. -- pro testy
  642.     --,@MemberListId int = 2
  643.     --,@BlockMissing bit = 0
  644.     --,@RunSourceTest bit = 1
  645.  
  646. DECLARE @TestResult TABLE (SourceTestResult bit)
  647. DECLARE @source_test TABLE (name varchar(255) null)
  648.  
  649. DECLARE @changes TABLE(MemberCode varchar(20), Change varchar(20));  
  650.  
  651. SET @MemberListCode = (SELECT Code FROM p4s.MemberLists WHERE Id = @MemberListId)
  652. SET @db_source_name = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'db_source_name')
  653.  
  654. -- kontrola vstupniho rozhrani
  655. IF @RunSourceTest = 1
  656. BEGIN
  657.     SET @query = @db_source_name +  '.ambica.SourceTestVDM @DimCode = ''' + @MemberListCode + ''', @Preview = 0'
  658.     EXEC (@query)
  659.  
  660.     SET @query = 'SELECT TOP 1 TestStatus FROM ' + @db_source_name + '.ambica.SourceTestResults WHERE SourceName = ''VDM_' + @MemberListCode + ''' ORDER BY LastTestDatetime DESC'
  661.    
  662.     DELETE FROM @TestResult
  663.     INSERT INTO @TestResult EXEC (@query)
  664.  
  665.     IF (SELECT SourceTestResult FROM @TestResult) = 0
  666.     BEGIN
  667.         -- zapis do logu
  668.         INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
  669.         VALUES
  670.         (   'Members', @MemberListCode, 'ERROR',0, 0, 0
  671.             ,'Chyba vstupního rozhraní'
  672.             ,GetDate()
  673.         )
  674.         SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
  675.         --SELECT - @@IDENTITY   --18.8.2016 JPE přidána návratová hodnota (musi byt pres SELECT, nestaci RETURN 1 :-( )
  676.         RETURN
  677.     END
  678. END
  679.  
  680. BEGIN TRY
  681.     -- naplneni tabulky ze zdroje
  682.     SET @query = 'SELECT Code, Name, SortOrder, Fchar01, Fchar02, Fchar03, Fchar04, Fchar05, Fchar06, Fchar07, Fchar08, Fchar09, Fchar10, Fint01, Fint02, Fint03, Fint04, Fint05, Fdate01, Fdate02, Fdate03, Fdate04, Fdate05, Ffloat01, Ffloat02, Ffloat03, Ffloat04, Ffloat05
  683.     FROM ' + @db_source_name + '.dbo.VDM_' + @MemberListCode
  684.  
  685.     INSERT INTO @tblSource (Code, Name, SortOrder, Fchar01, Fchar02, Fchar03, Fchar04, Fchar05, Fchar06, Fchar07, Fchar08, Fchar09, Fchar10, Fint01, Fint02, Fint03, Fint04, Fint05, Fdate01, Fdate02, Fdate03, Fdate04, Fdate05, Ffloat01, Ffloat02, Ffloat03, Ffloat04, Ffloat05)
  686.     EXEC(@query)
  687.  
  688.     ;WITH cte_members
  689.     AS (    SELECT *
  690.             FROM p4s.Members
  691.             WHERE MemberListId = @MemberListId
  692.     )
  693.  
  694.     MERGE cte_members AS t
  695.     USING @tblSource AS s
  696.     ON t.Code = s.Code
  697.     -- stavajici prvky na zdroji - updatujeme pouze pokud
  698.         -- a) jedna se o importovane prvky
  699.         -- b) na zdroji neni NULL
  700.         -- c) zdroj nese nejakou zmenu
  701.     WHEN MATCHED AND t.Imported = 1
  702.     AND (   t.Name      <> s.Name
  703.          OR t.SortOrder <> s.SortOrder
  704.          OR ISNULL(t.Fchar01,'')    <> ISNULL(s.Fchar01 , t.Fchar01 )
  705.          OR ISNULL(t.Fchar02,'')    <> ISNULL(s.Fchar02 , t.Fchar02 )
  706.          OR ISNULL(t.Fchar03,'')    <> ISNULL(s.Fchar03 , t.Fchar03 )
  707.          OR ISNULL(t.Fchar04,'')    <> ISNULL(s.Fchar04 , t.Fchar04 )
  708.          OR ISNULL(t.Fchar05,'')    <> ISNULL(s.Fchar05 , t.Fchar05 )
  709.          OR ISNULL(t.Fchar06,'')    <> ISNULL(s.Fchar06 , t.Fchar06 )
  710.          OR ISNULL(t.Fchar07,'')    <> ISNULL(s.Fchar07 , t.Fchar07 )
  711.          OR ISNULL(t.Fchar08,'')    <> ISNULL(s.Fchar08 , t.Fchar08 )
  712.          OR ISNULL(t.Fchar09,'')    <> ISNULL(s.Fchar09 , t.Fchar09 )
  713.          OR ISNULL(t.Fchar10,'')    <> ISNULL(s.Fchar10 , t.Fchar10 )
  714.          OR ISNULL(t.Fint01,0)      <> ISNULL(s.Fint01  , t.Fint01  )
  715.          OR ISNULL(t.Fint02,0)      <> ISNULL(s.Fint02  , t.Fint02  )
  716.          OR ISNULL(t.Fint03,0)      <> ISNULL(s.Fint03  , t.Fint03  )
  717.          OR ISNULL(t.Fint04,0)      <> ISNULL(s.Fint04  , t.Fint04  )
  718.          OR ISNULL(t.Fint05,0)      <> ISNULL(s.Fint05  , t.Fint05  )
  719.          OR ISNULL(t.Fdate01,1900-01-01) <> ISNULL(s.Fdate01    , t.Fdate01  )
  720.          OR ISNULL(t.Fdate02,1900-01-01) <> ISNULL(s.Fdate02    , t.Fdate02  )
  721.          OR ISNULL(t.Fdate03,1900-01-01) <> ISNULL(s.Fdate03    , t.Fdate03  )
  722.          OR ISNULL(t.Fdate04,1900-01-01) <> ISNULL(s.Fdate04    , t.Fdate04  )
  723.          OR ISNULL(t.Fdate05,1900-01-01) <> ISNULL(s.Fdate05    , t.Fdate05  )
  724.          OR ISNULL(t.Ffloat01,0)        <> ISNULL(s.Ffloat01    , t.Ffloat01  )
  725.          OR ISNULL(t.Ffloat02,0)        <> ISNULL(s.Ffloat02    , t.Ffloat02  )
  726.          OR ISNULL(t.Ffloat03,0)        <> ISNULL(s.Ffloat03    , t.Ffloat03  )
  727.          OR ISNULL(t.Ffloat04,0)        <> ISNULL(s.Ffloat04    , t.Ffloat04  )
  728.          OR ISNULL(t.Ffloat05,0)        <> ISNULL(s.Ffloat05    , t.Ffloat05  )
  729.         )
  730.     THEN UPDATE
  731.     SET
  732.          Name = s.Name
  733.         ,ShortName = CASE WHEN t.ShortName = LEFT(t.Name,50) THEN LEFT(s.Name, 50) ELSE LEFT(t.Name,50) END
  734.         ,SortOrder = s.SortOrder
  735.         ,Fchar01 = ISNULL(s.Fchar01 , t.Fchar01 )
  736.         ,Fchar02 = ISNULL(s.Fchar02 , t.Fchar02 )
  737.         ,Fchar03 = ISNULL(s.Fchar03 , t.Fchar03 )
  738.         ,Fchar04 = ISNULL(s.Fchar04 , t.Fchar04 )
  739.         ,Fchar05 = ISNULL(s.Fchar05 , t.Fchar05 )
  740.         ,Fchar06 = ISNULL(s.Fchar06 , t.Fchar06 )
  741.         ,Fchar07 = ISNULL(s.Fchar07 , t.Fchar07 )
  742.         ,Fchar08 = ISNULL(s.Fchar08 , t.Fchar08 )
  743.         ,Fchar09 = ISNULL(s.Fchar09 , t.Fchar09 )
  744.         ,Fchar10 = ISNULL(s.Fchar10 , t.Fchar10 )
  745.         ,Fint01  = ISNULL(s.Fint01  , t.Fint01  )
  746.         ,Fint02  = ISNULL(s.Fint02  , t.Fint02  )
  747.         ,Fint03  = ISNULL(s.Fint03  , t.Fint03  )
  748.         ,Fint04  = ISNULL(s.Fint04  , t.Fint04  )
  749.         ,Fint05  = ISNULL(s.Fint05  , t.Fint05  )
  750.         ,Fdate01  = ISNULL(s.Fdate01    , t.Fdate01  )
  751.         ,Fdate02  = ISNULL(s.Fdate02    , t.Fdate02  )
  752.         ,Fdate03  = ISNULL(s.Fdate03    , t.Fdate03  )
  753.         ,Fdate04  = ISNULL(s.Fdate04    , t.Fdate04  )
  754.         ,Fdate05  = ISNULL(s.Fdate05    , t.Fdate05  )
  755.         ,Ffloat01  = ISNULL(s.Ffloat01  , t.Ffloat01  )
  756.         ,Ffloat02  = ISNULL(s.Ffloat02  , t.Ffloat02  )
  757.         ,Ffloat03  = ISNULL(s.Ffloat03  , t.Ffloat03  )
  758.         ,Ffloat04  = ISNULL(s.Ffloat04  , t.Ffloat04  )
  759.         ,Ffloat05  = ISNULL(s.Ffloat05  , t.Ffloat05  )
  760.  
  761.     -- nove prvky na zdroji
  762.     WHEN NOT MATCHED BY TARGET THEN
  763.     INSERT (MemberListId, Code, Name, ShortName, SortOrder, Imported, Blocked, Fchar01, Fchar02, Fchar03, Fchar04, Fchar05, Fchar06, Fchar07, Fchar08, Fchar09, Fchar10, Fint01, Fint02, Fint03, Fint04, Fint05, Fdate01, Fdate02, Fdate03, Fdate04, Fdate05, Ffloat01, Ffloat02, Ffloat03, Ffloat04, Ffloat05)
  764.     VALUES (@MemberListId, s.Code, s.Name, LEFT(s.Name, 50), s.SortOrder, 1, 0, s.Fchar01, s.Fchar02, s.Fchar03, s.Fchar04, s.Fchar05, s.Fchar06, s.Fchar07, s.Fchar08, s.Fchar09, s.Fchar10, s.Fint01, s.Fint02, s.Fint03, s.Fint04, s.Fint05, s.Fdate01, s.Fdate02, s.Fdate03, s.Fdate04, s.Fdate05, s.Ffloat01, s.Ffloat02, s.Ffloat03, s.Ffloat04, s.Ffloat05)
  765.  
  766.     -- stavajici prvky chybi na zdroji a jsou oznaceny jako importovane --> oznacime jako Blocked
  767.     WHEN NOT MATCHED BY SOURCE AND @BlockMissing = 1 AND t.Imported = 1 AND t.Blocked = 0
  768.     THEN UPDATE SET Blocked = 1
  769.  
  770.     OUTPUT Inserted.Code, $action INTO @changes;  
  771.   ;
  772.  
  773.     -- zapis do logu
  774.     INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
  775.     VALUES
  776.     (   'Members', @MemberListCode, 'OK'   
  777.         ,(SELECT COUNT(*) FROM @changes WHERE Change = 'UPDATE')  
  778.         ,(SELECT COUNT(*) FROM @changes WHERE Change = 'INSERT')  
  779.         ,0                                                              -- TODO - je nutno si do tabulky @changes dat pole Blocked a upravit podminky pro pocty zde
  780.         ,'Import proběhl v pořádku'
  781.         ,GetDate()
  782.     )
  783.     SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
  784.     --SELECT @@IDENTITY  --18.8.2016 JPE přidán RETURN s návratovou hodnotou
  785.     RETURN
  786.  
  787. END TRY
  788. BEGIN CATCH
  789.     -- zapis do logu
  790.     INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
  791.     VALUES
  792.     (   'Members', @MemberListCode, 'ERROR',0, 0, 0
  793.         ,'Nespecifikovaná chyba importu'
  794.         ,GetDate()
  795.     )
  796.     --SELECT - @@IDENTITY  
  797.     SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
  798.     RETURN  --18.8.2016 JPE přidána návratová hodnota
  799. END CATCH
  800.  
  801.  
  802. END
  803.  
  804. GO
  805.  
  806. USE [AmbicaBI_sources]
  807.  
  808. GO
  809.  
  810. /****** Object:  View [ambica].[_VDM_EMPTY]    Script Date: 1. 6. 2017 10:10:24 ******/
  811. SET ANSI_NULLS ON
  812. GO
  813.  
  814. SET QUOTED_IDENTIFIER ON
  815. GO
  816.  
  817.  
  818. ALTER VIEW [ambica].[_VDM_EMPTY]
  819. AS
  820. SELECT
  821.      -- povinne pole nesouci znakovy klic prvku daneho seznamu prvku (napr. kod strediska, kod produktu,...)
  822.      Code AS Code       --[varchar](20) NOT NULL,
  823.  
  824.     -- povinne pole pro nazev
  825.     ,Name AS Name       --[nvarchar](255) NOT NULL,
  826.  
  827.     -- povinne pole pro urceni poradi prvku pri trideni --[int] NOT NULL,
  828.     -- jsou zde uvedeny vzory nekolika zpusobu, je potreba pouzit pouze JEDEN
  829.     -- vzor kodu, kdy kod prvku je puvodne ciselny a ma se podle nej tridit
  830.         -- neda se tomu 100% verit, proto je zabudovan test
  831.         -- ty, co nejsou ciselne, maji poradi 0, budou na zacatku a uzivatele to donuti probelm resit
  832.     ,CASE WHEN ISNUMERIC(Code) = 1 THEN CAST(Code AS int) ELSE 0 END AS SortOrder
  833.         -- vzor kodu pro nagenerovani trideni dle kodu (znakove)
  834.         --,CAST(ROW_NUMBER() OVER( ORDER BY Code) AS int) AS SortOrder
  835.         -- vzor kodu pro nagenerovani trideni dle kodu (ciselne)
  836.         --,CAST(ROW_NUMBER() OVER( ORDER BY CASE WHEN ISNUMERIC(Code) = 1 THEN CAST(Code AS int) ELSE 0 END) AS int) AS SortOrder
  837.         -- vzor kodu pro nagenerovani trideni dle nazvu
  838.         --,CAST(ROW_NUMBER() OVER( ORDER BY Name) AS int) AS SortOrder
  839.  
  840.     -- volna pole pro ulozeni znakove hodnoty do delky 20 - zbytek bude oriznut zprava
  841.     ,CAST(NULL AS varchar(20)) AS Fchar01   --[varchar](20) NULL,
  842.     ,CAST(NULL AS varchar(20)) AS Fchar02   --[varchar](20) NULL,
  843.     ,CAST(NULL AS varchar(20)) AS Fchar03   --[varchar](20) NULL,
  844.     ,CAST(NULL AS varchar(20)) AS Fchar04   --[varchar](20) NULL,
  845.     ,CAST(NULL AS varchar(20)) AS Fchar05   --[varchar](20) NULL,
  846.  
  847.     -- volna pole pro ulozeni znakove hodnoty do delky 50 - zbytek bude oriznut zprava
  848.     ,CAST(NULL AS varchar(50)) AS Fchar06   --[varchar](50) NULL,
  849.     ,CAST(NULL AS varchar(50)) AS Fchar07   --[varchar](50) NULL,
  850.     ,CAST(NULL AS varchar(50)) AS Fchar08   --[varchar](50) NULL,
  851.     ,CAST(NULL AS varchar(50)) AS Fchar09   --[varchar](50) NULL,
  852.     ,CAST(NULL AS varchar(50)) AS Fchar10   --[varchar](50) NULL,
  853.  
  854.     -- volna pole pro ulozeni ciselne hodnoty typu integer
  855.     ,CAST(NULL AS int) AS Fint01    --[int] NULL,
  856.     ,CAST(NULL AS int) AS Fint02    --[int] NULL,
  857.     ,CAST(NULL AS int) AS Fint03    --[int] NULL,
  858.     ,CAST(NULL AS int) AS Fint04    --[int] NULL,
  859.     ,CAST(NULL AS int) AS Fint05    --[int] NULL
  860.  
  861.     -- volna pole pro ulozeni ciselne hodnoty typu integer
  862.     ,CAST(NULL AS datetime) AS Fdate01  --[int] NULL,
  863.     ,CAST(NULL AS datetime) AS Fdate02  --[int] NULL,
  864.     ,CAST(NULL AS datetime) AS Fdate03  --[int] NULL,
  865.     ,CAST(NULL AS datetime) AS Fdate04  --[int] NULL,
  866.     ,CAST(NULL AS datetime) AS Fdate05  --[int] NULL
  867.  
  868.     -- volna pole pro ulozeni ciselne hodnoty typu integer
  869.     ,CAST(NULL AS float) AS Ffloat01    --[int] NULL,
  870.     ,CAST(NULL AS float) AS Ffloat02    --[int] NULL,
  871.     ,CAST(NULL AS float) AS Ffloat03    --[int] NULL,
  872.     ,CAST(NULL AS float) AS Ffloat04    --[int] NULL,
  873.     ,CAST(NULL AS float) AS Ffloat05    --[int] NULL
  874.  
  875. -- zde doplnit skutecnou zdrojovou tabulku
  876. FROM dbo._DM_EMPTY
  877.  
  878.  
  879.  
  880.  
  881.  
  882.  
  883. GO
  884.  
  885.  
  886.  
  887.  
  888.  
  889.  
  890.  
  891.  
  892.  
  893. --Title: AmbicaBI - Update script
  894. --Version to: 2.5.5
  895. --Hotfix no.: 7
  896. --Comment: rozsireni importnich procedur
  897. --EasyProject tasks: #3021
  898. --Date: 21.05.2017
  899. --Generated by: jpr
  900. GO
  901. USE [AmbicaBI]
  902. GO
  903. UPDATE [amb].[VersionNew] SET  HotfixDb = 7
  904.  
  905. GO
  906.  
  907. /****** Object:  UserDefinedTableType [import].[SourceMembers]    Script Date: 1. 6. 2017 10:23:40 ******/
  908. DROP TYPE [import].[SourceMembers]
  909. CREATE TYPE [import].[SourceMembers] AS TABLE(
  910.     [Code] [varchar](20) NOT NULL,
  911.     [Name] [nvarchar](255) NOT NULL,
  912.     [ShortName] [nvarchar](50) NOT NULL,
  913.     [SortOrder] [int] NULL,
  914.     [Fchar01] [varchar](20) NULL,
  915.     [Fchar02] [varchar](20) NULL,
  916.     [Fchar03] [varchar](20) NULL,
  917.     [Fchar04] [varchar](20) NULL,
  918.     [Fchar05] [varchar](20) NULL,
  919.     [Fchar06] [varchar](50) NULL,
  920.     [Fchar07] [varchar](50) NULL,
  921.     [Fchar08] [varchar](50) NULL,
  922.     [Fchar09] [varchar](50) NULL,
  923.     [Fchar10] [varchar](50) NULL,
  924.     [Fint01] [int] NULL,
  925.     [Fint02] [int] NULL,
  926.     [Fint03] [int] NULL,
  927.     [Fint04] [int] NULL,
  928.     [Fint05] [int] NULL,
  929.     [Fdate01] [datetime] NULL,
  930.     [Fdate02] [datetime] NULL,
  931.     [Fdate03] [datetime] NULL,
  932.     [Fdate04] [datetime] NULL,
  933.     [Fdate05] [datetime] NULL,
  934.     [Ffloat01] [float] NULL,
  935.     [Ffloat02] [float] NULL,
  936.     [Ffloat03] [float] NULL,
  937.     [Ffloat04] [float] NULL,
  938.     [Ffloat05] [float] NULL
  939. )
  940. GO
  941.  
  942. SET ANSI_NULLS ON
  943. GO
  944. SET QUOTED_IDENTIFIER ON
  945. GO
  946. ---- =================================================
  947. ---- Author:        PJE
  948. ---- Create date: podzim 2016
  949. ---- Description:   Procedura importuje prvky jednoho memberlistu
  950. ---- Changelog: 2017-02-13 PJE  Oprava
  951.                
  952. ---- =================================================
  953. ALTER PROCEDURE [import].[MembersFromSource]
  954.      @MemberListId int = 0
  955.     ,@BlockMissing bit = 0
  956.     ,@RunSourceTest bit = 1
  957.  
  958. AS
  959. BEGIN
  960.  
  961. DECLARE
  962.      @MemberListCode varchar(20)
  963.     ,@tblSource import.SourceMembers
  964.     ,@query varchar(max)
  965.     ,@db_source_name varchar(255)
  966.  
  967. -- pro testy
  968.     --,@MemberListId int = 2
  969.     --,@BlockMissing bit = 0
  970.     --,@RunSourceTest bit = 1
  971.  
  972. DECLARE @TestResult TABLE (SourceTestResult bit)
  973. DECLARE @source_test TABLE (name varchar(255) null)
  974.  
  975. DECLARE @changes TABLE(MemberCode varchar(20), Change varchar(20));  
  976.  
  977. SET @MemberListCode = (SELECT Code FROM p4s.MemberLists WHERE Id = @MemberListId)
  978. SET @db_source_name = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'db_source_name')
  979.  
  980. -- kontrola vstupniho rozhrani
  981. IF @RunSourceTest = 1
  982. BEGIN
  983.     SET @query = @db_source_name +  '.ambica.SourceTestVDM @DimCode = ''' + @MemberListCode + ''', @Preview = 0'
  984.     EXEC (@query)
  985.  
  986.     SET @query = 'SELECT TOP 1 TestStatus FROM ' + @db_source_name + '.ambica.SourceTestResults WHERE SourceName = ''VDM_' + @MemberListCode + ''' ORDER BY LastTestDatetime DESC'
  987.    
  988.     DELETE FROM @TestResult
  989.     INSERT INTO @TestResult EXEC (@query)
  990.  
  991.     IF (SELECT SourceTestResult FROM @TestResult) = 0
  992.     BEGIN
  993.         -- zapis do logu
  994.         INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
  995.         VALUES
  996.         (   'Members', @MemberListCode, 'ERROR',0, 0, 0
  997.             ,'Chyba vstupního rozhraní'
  998.             ,GetDate()
  999.         )
  1000.         SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
  1001.         --SELECT - @@IDENTITY   --18.8.2016 JPE přidána návratová hodnota (musi byt pres SELECT, nestaci RETURN 1 :-( )
  1002.         RETURN
  1003.     END
  1004. END
  1005.  
  1006. BEGIN TRY
  1007.     -- naplneni tabulky ze zdroje
  1008.     SET @query = 'SELECT Code, Name, ShortName, SortOrder, Fchar01, Fchar02, Fchar03, Fchar04, Fchar05, Fchar06, Fchar07, Fchar08, Fchar09, Fchar10, Fint01, Fint02, Fint03, Fint04, Fint05, Fdate01, Fdate02, Fdate03, Fdate04, Fdate05, Ffloat01, Ffloat02, Ffloat03, Ffloat04, Ffloat05
  1009.     FROM ' + @db_source_name + '.dbo.VDM_' + @MemberListCode
  1010.  
  1011.     INSERT INTO @tblSource (Code, Name, ShortName, SortOrder, Fchar01, Fchar02, Fchar03, Fchar04, Fchar05, Fchar06, Fchar07, Fchar08, Fchar09, Fchar10, Fint01, Fint02, Fint03, Fint04, Fint05, Fdate01, Fdate02, Fdate03, Fdate04, Fdate05, Ffloat01, Ffloat02, Ffloat03, Ffloat04, Ffloat05)
  1012.     EXEC(@query)
  1013.  
  1014.     ;WITH cte_members
  1015.     AS (    SELECT *
  1016.             FROM p4s.Members
  1017.             WHERE MemberListId = @MemberListId
  1018.     )
  1019.  
  1020.     MERGE cte_members AS t
  1021.     USING @tblSource AS s
  1022.     ON t.Code = s.Code
  1023.     -- stavajici prvky na zdroji - updatujeme pouze pokud
  1024.         -- a) jedna se o importovane prvky
  1025.         -- b) na zdroji neni NULL
  1026.         -- c) zdroj nese nejakou zmenu
  1027.     WHEN MATCHED AND t.Imported = 1
  1028.     AND (   t.Name      <> s.Name
  1029.          OR t.ShortName <> s.ShortName
  1030.          OR t.SortOrder <> s.SortOrder
  1031.          OR ISNULL(t.Fchar01,'')    <> ISNULL(s.Fchar01 , t.Fchar01 )
  1032.          OR ISNULL(t.Fchar02,'')    <> ISNULL(s.Fchar02 , t.Fchar02 )
  1033.          OR ISNULL(t.Fchar03,'')    <> ISNULL(s.Fchar03 , t.Fchar03 )
  1034.          OR ISNULL(t.Fchar04,'')    <> ISNULL(s.Fchar04 , t.Fchar04 )
  1035.          OR ISNULL(t.Fchar05,'')    <> ISNULL(s.Fchar05 , t.Fchar05 )
  1036.          OR ISNULL(t.Fchar06,'')    <> ISNULL(s.Fchar06 , t.Fchar06 )
  1037.          OR ISNULL(t.Fchar07,'')    <> ISNULL(s.Fchar07 , t.Fchar07 )
  1038.          OR ISNULL(t.Fchar08,'')    <> ISNULL(s.Fchar08 , t.Fchar08 )
  1039.          OR ISNULL(t.Fchar09,'')    <> ISNULL(s.Fchar09 , t.Fchar09 )
  1040.          OR ISNULL(t.Fchar10,'')    <> ISNULL(s.Fchar10 , t.Fchar10 )
  1041.          OR ISNULL(t.Fint01,0)      <> ISNULL(s.Fint01  , t.Fint01  )
  1042.          OR ISNULL(t.Fint02,0)      <> ISNULL(s.Fint02  , t.Fint02  )
  1043.          OR ISNULL(t.Fint03,0)      <> ISNULL(s.Fint03  , t.Fint03  )
  1044.          OR ISNULL(t.Fint04,0)      <> ISNULL(s.Fint04  , t.Fint04  )
  1045.          OR ISNULL(t.Fint05,0)      <> ISNULL(s.Fint05  , t.Fint05  )
  1046.          OR ISNULL(t.Fdate01,1900-01-01) <> ISNULL(s.Fdate01    , t.Fdate01  )
  1047.          OR ISNULL(t.Fdate02,1900-01-01) <> ISNULL(s.Fdate02    , t.Fdate02  )
  1048.          OR ISNULL(t.Fdate03,1900-01-01) <> ISNULL(s.Fdate03    , t.Fdate03  )
  1049.          OR ISNULL(t.Fdate04,1900-01-01) <> ISNULL(s.Fdate04    , t.Fdate04  )
  1050.          OR ISNULL(t.Fdate05,1900-01-01) <> ISNULL(s.Fdate05    , t.Fdate05  )
  1051.          OR ISNULL(t.Ffloat01,0)        <> ISNULL(s.Ffloat01    , t.Ffloat01  )
  1052.          OR ISNULL(t.Ffloat02,0)        <> ISNULL(s.Ffloat02    , t.Ffloat02  )
  1053.          OR ISNULL(t.Ffloat03,0)        <> ISNULL(s.Ffloat03    , t.Ffloat03  )
  1054.          OR ISNULL(t.Ffloat04,0)        <> ISNULL(s.Ffloat04    , t.Ffloat04  )
  1055.          OR ISNULL(t.Ffloat05,0)        <> ISNULL(s.Ffloat05    , t.Ffloat05  )
  1056.         )
  1057.     THEN UPDATE
  1058.     SET
  1059.          Name = s.Name
  1060.         ,ShortName = CASE WHEN t.ShortName = LEFT(t.Name,50) THEN LEFT(s.Name, 50) ELSE LEFT(t.Name,50) END
  1061.         ,SortOrder = s.SortOrder
  1062.         ,Fchar01 = ISNULL(s.Fchar01 , t.Fchar01 )
  1063.         ,Fchar02 = ISNULL(s.Fchar02 , t.Fchar02 )
  1064.         ,Fchar03 = ISNULL(s.Fchar03 , t.Fchar03 )
  1065.         ,Fchar04 = ISNULL(s.Fchar04 , t.Fchar04 )
  1066.         ,Fchar05 = ISNULL(s.Fchar05 , t.Fchar05 )
  1067.         ,Fchar06 = ISNULL(s.Fchar06 , t.Fchar06 )
  1068.         ,Fchar07 = ISNULL(s.Fchar07 , t.Fchar07 )
  1069.         ,Fchar08 = ISNULL(s.Fchar08 , t.Fchar08 )
  1070.         ,Fchar09 = ISNULL(s.Fchar09 , t.Fchar09 )
  1071.         ,Fchar10 = ISNULL(s.Fchar10 , t.Fchar10 )
  1072.         ,Fint01  = ISNULL(s.Fint01  , t.Fint01  )
  1073.         ,Fint02  = ISNULL(s.Fint02  , t.Fint02  )
  1074.         ,Fint03  = ISNULL(s.Fint03  , t.Fint03  )
  1075.         ,Fint04  = ISNULL(s.Fint04  , t.Fint04  )
  1076.         ,Fint05  = ISNULL(s.Fint05  , t.Fint05  )
  1077.         ,Fdate01  = ISNULL(s.Fdate01    , t.Fdate01  )
  1078.         ,Fdate02  = ISNULL(s.Fdate02    , t.Fdate02  )
  1079.         ,Fdate03  = ISNULL(s.Fdate03    , t.Fdate03  )
  1080.         ,Fdate04  = ISNULL(s.Fdate04    , t.Fdate04  )
  1081.         ,Fdate05  = ISNULL(s.Fdate05    , t.Fdate05  )
  1082.         ,Ffloat01  = ISNULL(s.Ffloat01  , t.Ffloat01  )
  1083.         ,Ffloat02  = ISNULL(s.Ffloat02  , t.Ffloat02  )
  1084.         ,Ffloat03  = ISNULL(s.Ffloat03  , t.Ffloat03  )
  1085.         ,Ffloat04  = ISNULL(s.Ffloat04  , t.Ffloat04  )
  1086.         ,Ffloat05  = ISNULL(s.Ffloat05  , t.Ffloat05  )
  1087.  
  1088.     -- nove prvky na zdroji
  1089.     WHEN NOT MATCHED BY TARGET THEN
  1090.     INSERT (MemberListId, Code, Name, ShortName, SortOrder, Imported, Blocked, Fchar01, Fchar02, Fchar03, Fchar04, Fchar05, Fchar06, Fchar07, Fchar08, Fchar09, Fchar10, Fint01, Fint02, Fint03, Fint04, Fint05, Fdate01, Fdate02, Fdate03, Fdate04, Fdate05, Ffloat01, Ffloat02, Ffloat03, Ffloat04, Ffloat05)
  1091.     VALUES (@MemberListId, s.Code, s.Name, s.ShortName, s.SortOrder, 1, 0, s.Fchar01, s.Fchar02, s.Fchar03, s.Fchar04, s.Fchar05, s.Fchar06, s.Fchar07, s.Fchar08, s.Fchar09, s.Fchar10, s.Fint01, s.Fint02, s.Fint03, s.Fint04, s.Fint05, s.Fdate01, s.Fdate02, s.Fdate03, s.Fdate04, s.Fdate05, s.Ffloat01, s.Ffloat02, s.Ffloat03, s.Ffloat04, s.Ffloat05)
  1092.  
  1093.     -- stavajici prvky chybi na zdroji a jsou oznaceny jako importovane --> oznacime jako Blocked
  1094.     WHEN NOT MATCHED BY SOURCE AND @BlockMissing = 1 AND t.Imported = 1 AND t.Blocked = 0
  1095.     THEN UPDATE SET Blocked = 1
  1096.  
  1097.     OUTPUT Inserted.Code, $action INTO @changes;  
  1098.   ;
  1099.  
  1100.     -- zapis do logu
  1101.     INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
  1102.     VALUES
  1103.     (   'Members', @MemberListCode, 'OK'   
  1104.         ,(SELECT COUNT(*) FROM @changes WHERE Change = 'UPDATE')  
  1105.         ,(SELECT COUNT(*) FROM @changes WHERE Change = 'INSERT')  
  1106.         ,0                                                              -- TODO - je nutno si do tabulky @changes dat pole Blocked a upravit podminky pro pocty zde
  1107.         ,'Import proběhl v pořádku'
  1108.         ,GetDate()
  1109.     )
  1110.     SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
  1111.     --SELECT @@IDENTITY  --18.8.2016 JPE přidán RETURN s návratovou hodnotou
  1112.     RETURN
  1113.  
  1114. END TRY
  1115. BEGIN CATCH
  1116.     -- zapis do logu
  1117.     INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
  1118.     VALUES
  1119.     (   'Members', @MemberListCode, 'ERROR',0, 0, 0
  1120.         ,'Nespecifikovaná chyba importu'
  1121.         ,GetDate()
  1122.     )
  1123.     --SELECT - @@IDENTITY  
  1124.     SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
  1125.     RETURN  --18.8.2016 JPE přidána návratová hodnota
  1126. END CATCH
  1127.  
  1128.  
  1129. END
  1130.  
  1131. GO
  1132.  
  1133. USE [AmbicaBI_sources]
  1134.  
  1135. GO
  1136. DROP TABLE [dbo].[_DM_EMPTY]
  1137. GO
  1138. /****** Object:  Table [dbo].[_DM_EMPTY]    Script Date: 1. 6. 2017 11:08:36 ******/
  1139. SET ANSI_NULLS ON
  1140. GO
  1141.  
  1142. SET QUOTED_IDENTIFIER ON
  1143. GO
  1144.  
  1145. CREATE TABLE [dbo].[_DM_EMPTY](
  1146.     [Code] [varchar](20) NOT NULL,
  1147.     [Name] [nvarchar](255) NOT NULL,
  1148.     [ShortName] [nvarchar](50) NOT NULL,
  1149.     [SortOrder] [int] NULL,
  1150.     [Fchar01] [varchar](20) NULL,
  1151.     [Fchar02] [varchar](20) NULL,
  1152.     [Fchar03] [varchar](20) NULL,
  1153.     [Fchar04] [varchar](20) NULL,
  1154.     [Fchar05] [varchar](20) NULL,
  1155.     [Fchar06] [varchar](50) NULL,
  1156.     [Fchar07] [varchar](50) NULL,
  1157.     [Fchar08] [varchar](50) NULL,
  1158.     [Fchar09] [varchar](50) NULL,
  1159.     [Fchar10] [varchar](50) NULL,
  1160.     [Fint01] [int] NULL,
  1161.     [Fint02] [int] NULL,
  1162.     [Fint03] [int] NULL,
  1163.     [Fint04] [int] NULL,
  1164.     [Fint05] [int] NULL,
  1165.     [Fdate01] [datetime] NULL,
  1166.     [Fdate02] [datetime] NULL,
  1167.     [Fdate03] [datetime] NULL,
  1168.     [Fdate04] [datetime] NULL,
  1169.     [Fdate05] [datetime] NULL,
  1170.     [Ffloat01] [float] NULL,
  1171.     [Ffloat02] [float] NULL,
  1172.     [Ffloat03] [float] NULL,
  1173.     [Ffloat04] [float] NULL,
  1174.     [Ffloat05] [float] NULL
  1175. ) ON [PRIMARY]
  1176. GO
  1177.  
  1178.  
  1179.  
  1180. /****** Object:  View [ambica].[_VDM_EMPTY]    Script Date: 1. 6. 2017 10:10:24 ******/
  1181. SET ANSI_NULLS ON
  1182. GO
  1183.  
  1184. SET QUOTED_IDENTIFIER ON
  1185. GO
  1186.  
  1187.  
  1188. ALTER VIEW [ambica].[_VDM_EMPTY]
  1189. AS
  1190. SELECT
  1191.      -- povinne pole nesouci znakovy klic prvku daneho seznamu prvku (napr. kod strediska, kod produktu,...)
  1192.      Code AS Code       --[varchar](20) NOT NULL,
  1193.  
  1194.     -- povinne pole pro nazev
  1195.     ,Name AS Name       --[nvarchar](255) NOT NULL,
  1196.     ,ShortName AS ShortName
  1197.     -- povinne pole pro urceni poradi prvku pri trideni --[int] NOT NULL,
  1198.     -- jsou zde uvedeny vzory nekolika zpusobu, je potreba pouzit pouze JEDEN
  1199.     -- vzor kodu, kdy kod prvku je puvodne ciselny a ma se podle nej tridit
  1200.         -- neda se tomu 100% verit, proto je zabudovan test
  1201.         -- ty, co nejsou ciselne, maji poradi 0, budou na zacatku a uzivatele to donuti probelm resit
  1202.     ,CASE WHEN ISNUMERIC(Code) = 1 THEN CAST(Code AS int) ELSE 0 END AS SortOrder
  1203.         -- vzor kodu pro nagenerovani trideni dle kodu (znakove)
  1204.         --,CAST(ROW_NUMBER() OVER( ORDER BY Code) AS int) AS SortOrder
  1205.         -- vzor kodu pro nagenerovani trideni dle kodu (ciselne)
  1206.         --,CAST(ROW_NUMBER() OVER( ORDER BY CASE WHEN ISNUMERIC(Code) = 1 THEN CAST(Code AS int) ELSE 0 END) AS int) AS SortOrder
  1207.         -- vzor kodu pro nagenerovani trideni dle nazvu
  1208.         --,CAST(ROW_NUMBER() OVER( ORDER BY Name) AS int) AS SortOrder
  1209.  
  1210.     -- volna pole pro ulozeni znakove hodnoty do delky 20 - zbytek bude oriznut zprava
  1211.     ,CAST(NULL AS varchar(20)) AS Fchar01   --[varchar](20) NULL,
  1212.     ,CAST(NULL AS varchar(20)) AS Fchar02   --[varchar](20) NULL,
  1213.     ,CAST(NULL AS varchar(20)) AS Fchar03   --[varchar](20) NULL,
  1214.     ,CAST(NULL AS varchar(20)) AS Fchar04   --[varchar](20) NULL,
  1215.     ,CAST(NULL AS varchar(20)) AS Fchar05   --[varchar](20) NULL,
  1216.  
  1217.     -- volna pole pro ulozeni znakove hodnoty do delky 50 - zbytek bude oriznut zprava
  1218.     ,CAST(NULL AS varchar(50)) AS Fchar06   --[varchar](50) NULL,
  1219.     ,CAST(NULL AS varchar(50)) AS Fchar07   --[varchar](50) NULL,
  1220.     ,CAST(NULL AS varchar(50)) AS Fchar08   --[varchar](50) NULL,
  1221.     ,CAST(NULL AS varchar(50)) AS Fchar09   --[varchar](50) NULL,
  1222.     ,CAST(NULL AS varchar(50)) AS Fchar10   --[varchar](50) NULL,
  1223.  
  1224.     -- volna pole pro ulozeni ciselne hodnoty typu integer
  1225.     ,CAST(NULL AS int) AS Fint01    --[int] NULL,
  1226.     ,CAST(NULL AS int) AS Fint02    --[int] NULL,
  1227.     ,CAST(NULL AS int) AS Fint03    --[int] NULL,
  1228.     ,CAST(NULL AS int) AS Fint04    --[int] NULL,
  1229.     ,CAST(NULL AS int) AS Fint05    --[int] NULL
  1230.  
  1231.     -- volna pole pro ulozeni ciselne hodnoty typu integer
  1232.     ,CAST(NULL AS datetime) AS Fdate01  --[datetime] NULL,
  1233.     ,CAST(NULL AS datetime) AS Fdate02  --[datetime] NULL,
  1234.     ,CAST(NULL AS datetime) AS Fdate03  --[datetime] NULL,
  1235.     ,CAST(NULL AS datetime) AS Fdate04  --[datetime] NULL,
  1236.     ,CAST(NULL AS datetime) AS Fdate05  --[datetime] NULL
  1237.  
  1238.     -- volna pole pro ulozeni ciselne hodnoty typu integer
  1239.     ,CAST(NULL AS float) AS Ffloat01    --[float] NULL,
  1240.     ,CAST(NULL AS float) AS Ffloat02    --[float] NULL,
  1241.     ,CAST(NULL AS float) AS Ffloat03    --[float] NULL,
  1242.     ,CAST(NULL AS float) AS Ffloat04    --[float] NULL,
  1243.     ,CAST(NULL AS float) AS Ffloat05    --[float] NULL
  1244.  
  1245. -- zde doplnit skutecnou zdrojovou tabulku
  1246. FROM dbo._DM_EMPTY
  1247.  
  1248.  
  1249.  
  1250.  
  1251.  
  1252.  
  1253. GO
  1254.  
  1255.  
  1256.  
  1257.  
  1258.  
  1259.  
  1260.  
  1261.  
  1262.  
  1263.  
  1264. --Title: AmbicaBI - Update script
  1265. --Version to: 2.5.5
  1266. --Hotfix no.: 8
  1267. --Comment: rozsireni importnich procedur
  1268. --EasyProject tasks: #3021
  1269. --Date: 21.05.2017
  1270. --Generated by: jpr
  1271. GO
  1272. USE [AmbicaBI]
  1273. GO
  1274. UPDATE [amb].[VersionNew] SET  HotfixDb = 8
  1275.  
  1276. GO
  1277.  
  1278. /****** Object:  StoredProcedure [import].[MembersFromSource]    Script Date: 1. 6. 2017 10:20:05 ******/
  1279. SET ANSI_NULLS ON
  1280. GO
  1281. SET QUOTED_IDENTIFIER ON
  1282. GO
  1283. ---- =================================================
  1284. ---- Author:        PJE
  1285. ---- Create date: podzim 2016
  1286. ---- Description:   Procedura importuje prvky jednoho memberlistu
  1287. ---- Changelog: 2017-02-13 PJE  Oprava
  1288.                
  1289. ---- =================================================
  1290. ALTER PROCEDURE [import].[MembersFromSource]
  1291.      @MemberListId int = 0
  1292.     ,@BlockMissing bit = 0
  1293.     ,@RunSourceTest bit = 1
  1294.  
  1295. AS
  1296. BEGIN
  1297.  
  1298. DECLARE
  1299.      @MemberListCode varchar(20)
  1300.     ,@tblSource import.SourceMembers
  1301.     ,@query varchar(max)
  1302.     ,@db_source_name varchar(255)
  1303.  
  1304. -- pro testy
  1305.     --,@MemberListId int = 2
  1306.     --,@BlockMissing bit = 0
  1307.     --,@RunSourceTest bit = 1
  1308.  
  1309. DECLARE @TestResult TABLE (SourceTestResult bit)
  1310. DECLARE @source_test TABLE (name varchar(255) null)
  1311.  
  1312. DECLARE @changes TABLE(MemberCode varchar(20), Change varchar(20));  
  1313.  
  1314. SET @MemberListCode = (SELECT Code FROM p4s.MemberLists WHERE Id = @MemberListId)
  1315. SET @db_source_name = (SELECT ParameterValue FROM amb.Plan4SParameters WHERE ParameterName = 'db_source_name')
  1316.  
  1317. -- kontrola vstupniho rozhrani
  1318. IF @RunSourceTest = 1
  1319. BEGIN
  1320.     SET @query = @db_source_name +  '.ambica.SourceTestVDM @DimCode = ''' + @MemberListCode + ''', @Preview = 0'
  1321.     EXEC (@query)
  1322.  
  1323.     SET @query = 'SELECT TOP 1 TestStatus FROM ' + @db_source_name + '.ambica.SourceTestResults WHERE SourceName = ''VDM_' + @MemberListCode + ''' ORDER BY LastTestDatetime DESC'
  1324.    
  1325.     DELETE FROM @TestResult
  1326.     INSERT INTO @TestResult EXEC (@query)
  1327.  
  1328.     IF (SELECT SourceTestResult FROM @TestResult) = 0
  1329.     BEGIN
  1330.         -- zapis do logu
  1331.         INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
  1332.         VALUES
  1333.         (   'Members', @MemberListCode, 'ERROR',0, 0, 0
  1334.             ,'Chyba vstupního rozhraní'
  1335.             ,GetDate()
  1336.         )
  1337.         SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
  1338.         --SELECT - @@IDENTITY   --18.8.2016 JPE přidána návratová hodnota (musi byt pres SELECT, nestaci RETURN 1 :-( )
  1339.         RETURN
  1340.     END
  1341. END
  1342.  
  1343. BEGIN TRY
  1344.     -- naplneni tabulky ze zdroje
  1345.     SET @query = 'SELECT Code, Name, ShortName, SortOrder, Fchar01, Fchar02, Fchar03, Fchar04, Fchar05, Fchar06, Fchar07, Fchar08, Fchar09, Fchar10, Fint01, Fint02, Fint03, Fint04, Fint05, Fdate01, Fdate02, Fdate03, Fdate04, Fdate05, Ffloat01, Ffloat02, Ffloat03, Ffloat04, Ffloat05
  1346.     FROM ' + @db_source_name + '.dbo.VDM_' + @MemberListCode
  1347.  
  1348.     INSERT INTO @tblSource (Code, Name, ShortName, SortOrder, Fchar01, Fchar02, Fchar03, Fchar04, Fchar05, Fchar06, Fchar07, Fchar08, Fchar09, Fchar10, Fint01, Fint02, Fint03, Fint04, Fint05, Fdate01, Fdate02, Fdate03, Fdate04, Fdate05, Ffloat01, Ffloat02, Ffloat03, Ffloat04, Ffloat05)
  1349.     EXEC(@query)
  1350.  
  1351.     ;WITH cte_members
  1352.     AS (    SELECT *
  1353.             FROM p4s.Members
  1354.             WHERE MemberListId = @MemberListId
  1355.     )
  1356.  
  1357.     MERGE cte_members AS t
  1358.     USING @tblSource AS s
  1359.     ON t.Code = s.Code
  1360.     -- stavajici prvky na zdroji - updatujeme pouze pokud
  1361.         -- a) jedna se o importovane prvky
  1362.         -- b) na zdroji neni NULL
  1363.         -- c) zdroj nese nejakou zmenu
  1364.     WHEN MATCHED AND t.Imported = 1
  1365.     AND (   t.Name      <> s.Name
  1366.          OR t.ShortName <> s.ShortName
  1367.          OR t.SortOrder <> s.SortOrder
  1368.          OR ISNULL(t.Fchar01,'')    <> ISNULL(s.Fchar01 , t.Fchar01 )
  1369.          OR ISNULL(t.Fchar02,'')    <> ISNULL(s.Fchar02 , t.Fchar02 )
  1370.          OR ISNULL(t.Fchar03,'')    <> ISNULL(s.Fchar03 , t.Fchar03 )
  1371.          OR ISNULL(t.Fchar04,'')    <> ISNULL(s.Fchar04 , t.Fchar04 )
  1372.          OR ISNULL(t.Fchar05,'')    <> ISNULL(s.Fchar05 , t.Fchar05 )
  1373.          OR ISNULL(t.Fchar06,'')    <> ISNULL(s.Fchar06 , t.Fchar06 )
  1374.          OR ISNULL(t.Fchar07,'')    <> ISNULL(s.Fchar07 , t.Fchar07 )
  1375.          OR ISNULL(t.Fchar08,'')    <> ISNULL(s.Fchar08 , t.Fchar08 )
  1376.          OR ISNULL(t.Fchar09,'')    <> ISNULL(s.Fchar09 , t.Fchar09 )
  1377.          OR ISNULL(t.Fchar10,'')    <> ISNULL(s.Fchar10 , t.Fchar10 )
  1378.          OR ISNULL(t.Fint01,0)      <> ISNULL(s.Fint01  , t.Fint01  )
  1379.          OR ISNULL(t.Fint02,0)      <> ISNULL(s.Fint02  , t.Fint02  )
  1380.          OR ISNULL(t.Fint03,0)      <> ISNULL(s.Fint03  , t.Fint03  )
  1381.          OR ISNULL(t.Fint04,0)      <> ISNULL(s.Fint04  , t.Fint04  )
  1382.          OR ISNULL(t.Fint05,0)      <> ISNULL(s.Fint05  , t.Fint05  )
  1383.          OR ISNULL(t.Fdate01,1900-01-01) <> ISNULL(s.Fdate01    , t.Fdate01  )
  1384.          OR ISNULL(t.Fdate02,1900-01-01) <> ISNULL(s.Fdate02    , t.Fdate02  )
  1385.          OR ISNULL(t.Fdate03,1900-01-01) <> ISNULL(s.Fdate03    , t.Fdate03  )
  1386.          OR ISNULL(t.Fdate04,1900-01-01) <> ISNULL(s.Fdate04    , t.Fdate04  )
  1387.          OR ISNULL(t.Fdate05,1900-01-01) <> ISNULL(s.Fdate05    , t.Fdate05  )
  1388.          OR ISNULL(t.Ffloat01,0)        <> ISNULL(s.Ffloat01    , t.Ffloat01  )
  1389.          OR ISNULL(t.Ffloat02,0)        <> ISNULL(s.Ffloat02    , t.Ffloat02  )
  1390.          OR ISNULL(t.Ffloat03,0)        <> ISNULL(s.Ffloat03    , t.Ffloat03  )
  1391.          OR ISNULL(t.Ffloat04,0)        <> ISNULL(s.Ffloat04    , t.Ffloat04  )
  1392.          OR ISNULL(t.Ffloat05,0)        <> ISNULL(s.Ffloat05    , t.Ffloat05  )
  1393.         )
  1394.     THEN UPDATE
  1395.     SET
  1396.          Name = s.Name
  1397.         --,ShortName = CASE WHEN t.ShortName = LEFT(t.Name,50) THEN LEFT(s.Name, 50) ELSE LEFT(t.Name,50) END
  1398.         ,ShortName = ISNULL(s.ShortName, t.ShortName)
  1399.         ,SortOrder = s.SortOrder
  1400.         ,Fchar01 = ISNULL(s.Fchar01 , t.Fchar01 )
  1401.         ,Fchar02 = ISNULL(s.Fchar02 , t.Fchar02 )
  1402.         ,Fchar03 = ISNULL(s.Fchar03 , t.Fchar03 )
  1403.         ,Fchar04 = ISNULL(s.Fchar04 , t.Fchar04 )
  1404.         ,Fchar05 = ISNULL(s.Fchar05 , t.Fchar05 )
  1405.         ,Fchar06 = ISNULL(s.Fchar06 , t.Fchar06 )
  1406.         ,Fchar07 = ISNULL(s.Fchar07 , t.Fchar07 )
  1407.         ,Fchar08 = ISNULL(s.Fchar08 , t.Fchar08 )
  1408.         ,Fchar09 = ISNULL(s.Fchar09 , t.Fchar09 )
  1409.         ,Fchar10 = ISNULL(s.Fchar10 , t.Fchar10 )
  1410.         ,Fint01  = ISNULL(s.Fint01  , t.Fint01  )
  1411.         ,Fint02  = ISNULL(s.Fint02  , t.Fint02  )
  1412.         ,Fint03  = ISNULL(s.Fint03  , t.Fint03  )
  1413.         ,Fint04  = ISNULL(s.Fint04  , t.Fint04  )
  1414.         ,Fint05  = ISNULL(s.Fint05  , t.Fint05  )
  1415.         ,Fdate01  = ISNULL(s.Fdate01    , t.Fdate01  )
  1416.         ,Fdate02  = ISNULL(s.Fdate02    , t.Fdate02  )
  1417.         ,Fdate03  = ISNULL(s.Fdate03    , t.Fdate03  )
  1418.         ,Fdate04  = ISNULL(s.Fdate04    , t.Fdate04  )
  1419.         ,Fdate05  = ISNULL(s.Fdate05    , t.Fdate05  )
  1420.         ,Ffloat01  = ISNULL(s.Ffloat01  , t.Ffloat01  )
  1421.         ,Ffloat02  = ISNULL(s.Ffloat02  , t.Ffloat02  )
  1422.         ,Ffloat03  = ISNULL(s.Ffloat03  , t.Ffloat03  )
  1423.         ,Ffloat04  = ISNULL(s.Ffloat04  , t.Ffloat04  )
  1424.         ,Ffloat05  = ISNULL(s.Ffloat05  , t.Ffloat05  )
  1425.  
  1426.     -- nove prvky na zdroji
  1427.     WHEN NOT MATCHED BY TARGET THEN
  1428.     INSERT (MemberListId, Code, Name, ShortName, SortOrder, Imported, Blocked, Fchar01, Fchar02, Fchar03, Fchar04, Fchar05, Fchar06, Fchar07, Fchar08, Fchar09, Fchar10, Fint01, Fint02, Fint03, Fint04, Fint05, Fdate01, Fdate02, Fdate03, Fdate04, Fdate05, Ffloat01, Ffloat02, Ffloat03, Ffloat04, Ffloat05)
  1429.     VALUES (@MemberListId, s.Code, s.Name, s.ShortName, s.SortOrder, 1, 0, s.Fchar01, s.Fchar02, s.Fchar03, s.Fchar04, s.Fchar05, s.Fchar06, s.Fchar07, s.Fchar08, s.Fchar09, s.Fchar10, s.Fint01, s.Fint02, s.Fint03, s.Fint04, s.Fint05, s.Fdate01, s.Fdate02, s.Fdate03, s.Fdate04, s.Fdate05, s.Ffloat01, s.Ffloat02, s.Ffloat03, s.Ffloat04, s.Ffloat05)
  1430.  
  1431.     -- stavajici prvky chybi na zdroji a jsou oznaceny jako importovane --> oznacime jako Blocked
  1432.     WHEN NOT MATCHED BY SOURCE AND @BlockMissing = 1 AND t.Imported = 1 AND t.Blocked = 0
  1433.     THEN UPDATE SET Blocked = 1
  1434.  
  1435.     OUTPUT Inserted.Code, $action INTO @changes;  
  1436.   ;
  1437.  
  1438.     -- zapis do logu
  1439.     INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
  1440.     VALUES
  1441.     (   'Members', @MemberListCode, 'OK'   
  1442.         ,(SELECT COUNT(*) FROM @changes WHERE Change = 'UPDATE')  
  1443.         ,(SELECT COUNT(*) FROM @changes WHERE Change = 'INSERT')  
  1444.         ,0                                                              -- TODO - je nutno si do tabulky @changes dat pole Blocked a upravit podminky pro pocty zde
  1445.         ,'Import proběhl v pořádku'
  1446.         ,GetDate()
  1447.     )
  1448.     SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
  1449.     --SELECT @@IDENTITY  --18.8.2016 JPE přidán RETURN s návratovou hodnotou
  1450.     RETURN
  1451.  
  1452. END TRY
  1453. BEGIN CATCH
  1454.     -- zapis do logu
  1455.     INSERT INTO import.ImportLog (SourceType, SourceCode, ImportStatus, UpdatedCount, InsertedCount, BlockedCount, Comment, LogTime)
  1456.     VALUES
  1457.     (   'Members', @MemberListCode, 'ERROR',0, 0, 0
  1458.         ,'Nespecifikovaná chyba importu'
  1459.         ,GetDate()
  1460.     )
  1461.     --SELECT - @@IDENTITY  
  1462.     SELECT CAST(@@IDENTITY AS int) -- 7.2.2017 JPE prevod navratove hodnoty na int, vracet vzdy id logu (ne zapornou hodnotu)
  1463.     RETURN  --18.8.2016 JPE přidána návratová hodnota
  1464. END CATCH
  1465.  
  1466.  
  1467. END
  1468.  
  1469.  
  1470.  
  1471.  
  1472.  
  1473. --Title: AmbicaBI - Update script
  1474. --Version to: 2.5.5
  1475. --Hotfix no.: 9
  1476. --Comment: uprava MDX kodu
  1477. --EasyProject tasks: #3031
  1478. --Date: 21.05.2017
  1479. --Generated by: jpr
  1480. GO
  1481. USE [AmbicaBI]
  1482. GO
  1483. UPDATE [amb].[VersionNew] SET  HotfixDb = 9
  1484.  
  1485. GO
  1486.  
  1487. UPDATE olap.MCubeCalculationsProp SET MDXcode = REPLACE(MDXcode, '[Typ hodnoty ukazatele - základní].[Typ hodnoty]', '[Typ hodnoty - základní].[Typ hodnoty - základní]')
  1488.  
  1489. --Title: AmbicaBI - Update script
  1490. --Version to: 2.5.5
  1491. --Hotfix no.: 10
  1492. --Comment: pje - oprava procedury pro hierarchie
  1493. --         jpr - pridani chybejicich indexu
  1494. --EasyProject tasks: #3031
  1495. --Date: 21.05.2017
  1496. --Generated by: jpr
  1497. GO
  1498. USE [AmbicaBI]
  1499. GO
  1500. UPDATE [amb].[VersionNew] SET  HotfixDb = 10
  1501.  
  1502. GO
  1503. /****** Object:  StoredProcedure [p4s].[ActHierarchy]    Script Date: 6. 6. 2017 15:16:21 ******/
  1504. SET ANSI_NULLS ON
  1505. GO
  1506. SET QUOTED_IDENTIFIER ON
  1507. GO
  1508. -- =================================================
  1509. -- Author:      PJE
  1510. -- Create date: 16.02.2017
  1511. -- Description: Procedura na aktualizaci NEimportované hierarchie
  1512. -- Changelog:
  1513. -- 06.06.2017 PJE   - zdrojova vazba muze byt i typu 1 (ne jen 2)
  1514. -- =================================================
  1515.  
  1516. ALTER PROCEDURE [p4s].[ActHierarchy]
  1517.     @HierarchyId int = 0
  1518.  
  1519. AS
  1520. BEGIN
  1521.  
  1522. -- pro testy
  1523. --DECLARE @HierarchyId int = 11
  1524.  
  1525. DECLARE
  1526.      @ProcedureName     varchar(50) = 'ActHierarchy'
  1527.     ,@Level             int
  1528.     ,@ParentList        int
  1529.     ,@ChildList         int
  1530.     ,@SourceRelationId  int
  1531.     ,@RelationId        int
  1532.     ,@ParentRelationId  int
  1533.     ,@Comment           varchar(255)
  1534.  
  1535. -- kontrola na atribut Imported
  1536. IF (SELECT Imported FROM p4s.Hierarchies WHERE Id = @HierarchyId) = 1
  1537. BEGIN
  1538.     -- zapis do logu
  1539.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  1540.     VALUES  (@ProcedureName, 'Hierarchie je importovaná, není možno ji aktulizovat jinak.',GetDate() )
  1541.     --SELECT - @@IDENTITY  
  1542.     RETURN  
  1543. END
  1544.  
  1545.  
  1546. DECLARE cLevel CURSOR FAST_FORWARD FOR
  1547. SELECT hl.LevelOrdinal, mlr.ParentListId, mlr.ChildListId, mlrs.Id, mlr.Id, mlr.ParentMemberListRelationId
  1548. FROM p4s.HierarchyLevels hl
  1549. JOIN p4s.MemberListRelations mlr ON mlr.Id = hl.UpMemberListRelationId
  1550. JOIN p4s.MemberListRelations mlrs ON mlrs.ParentListId = mlr.ParentListId AND mlrs.ChildListId = mlr.ChildListId AND mlrs.RelationTypeId IN (1, 2)
  1551. WHERE HierarchyId = @HierarchyId
  1552. AND mlr.RelationTypeId = 3
  1553. ORDER BY hl.LevelOrdinal
  1554.  
  1555. OPEN cLevel
  1556. FETCH NEXT FROM cLevel INTO @Level, @ParentList, @ChildList, @SourceRelationId, @RelationId, @ParentRelationId
  1557.  
  1558. WHILE @@FETCH_STATUS = 0
  1559. BEGIN
  1560.     --SELECT @Level, @ParentList, @ChildList, @SourceRelationId, @RelationId, @ParentRelationId
  1561.     INSERT INTO p4s.MemberRelations (MemberListRelationId, ParentMemberId, ChildMemberId, SortOrder, Imported, MtMParentMemberId)
  1562.     SELECT
  1563.          @RelationId
  1564.         ,mr.ParentMemberId
  1565.         ,mr.ChildMemberId
  1566.         ,mr.SortOrder
  1567.         ,0
  1568.         ,mrp.Id
  1569.     FROM p4s.MemberRelations mr                                 -- toto je zdrojova vazba
  1570.     JOIN p4s.MemberRelations mrp                                -- toto je parent relace (v prvnim kole typ 2, pak uz 3)
  1571.     ON mr.ParentMemberId = mrp.ChildMemberId
  1572.     LEFT JOIN p4s.MemberRelations mra                           -- existujici vazby
  1573.     ON  mra.MemberListRelationId = @RelationId
  1574.     AND mra.ParentMemberId = mr.ParentMemberId
  1575.     AND mra.ChildMemberId = mr.ChildMemberId
  1576.     AND mra.MtMParentMemberId = mrp.Id
  1577.     WHERE mr.MemberListRelationId = @SourceRelationId
  1578.     AND mrp.MemberListRelationId = @ParentRelationId
  1579.     AND mra.Id IS NULL
  1580.  
  1581.     SET @Comment = 'V levelu ' + CAST(@Level AS varchar) + ' hierarchie ' + CAST(@HierarchyId AS varchar) + ' přidáno relací: ' + CAST(ISNULL(@@ROWCOUNT,0) AS varchar)
  1582.     -- zapis do logu
  1583.     INSERT INTO p4s.RunLog (ProcedureName, Comment, LogTime)
  1584.     VALUES  (@ProcedureName, @Comment, GetDate() )
  1585.  
  1586.     FETCH NEXT FROM cLevel INTO @Level, @ParentList, @ChildList, @SourceRelationId, @RelationId, @ParentRelationId
  1587. END
  1588. CLOSE cLevel
  1589. DEALLOCATE cLevel
  1590.  
  1591. END
  1592.  
  1593. GO
  1594.  
  1595.  
  1596. IF NOT EXISTS (SELECT *  FROM sys.indexes  WHERE name='NCI_MEMBERRELATIONS_1'
  1597. AND object_id = OBJECT_ID('[p4s].[MemberRelations]'))
  1598. BEGIN
  1599.     CREATE NONCLUSTERED INDEX [NCI_MEMBERRELATIONS_1]
  1600.     ON [p4s].[MemberRelations]([MemberListRelationId] ASC)
  1601.     INCLUDE([Id], [ParentMemberId], [ChildMemberId], [MtMParentMemberId]);
  1602. END
  1603.  
  1604. IF NOT EXISTS (SELECT *  FROM sys.indexes  WHERE name='NCI_MEMBERRELATIONS_2'
  1605. AND object_id = OBJECT_ID('[p4s].[MemberRelations]'))
  1606. BEGIN
  1607.     CREATE NONCLUSTERED INDEX [NCI_MEMBERRELATIONS_2]
  1608.     ON [p4s].[MemberRelations]([MemberListRelationId] ASC, [ParentMemberId] ASC, [ChildMemberId] ASC)
  1609.     INCLUDE([Id]);
  1610. END
  1611.  
  1612. IF NOT EXISTS (SELECT *  FROM sys.indexes  WHERE name='NCI_MEMBERRELATIONS_3'
  1613. AND object_id = OBJECT_ID('[p4s].[MemberRelations]'))
  1614. BEGIN
  1615.     CREATE NONCLUSTERED INDEX [NCI_MEMBERRELATIONS_3]
  1616.     ON [p4s].[MemberRelations]([MemberListRelationId] ASC, [ChildMemberId] ASC, [MtMParentMemberId] ASC)
  1617.     INCLUDE([Id], [ParentMemberId]);
  1618. END
  1619.  
  1620. -------------------------------------------------------------------------------------------------------
  1621.  
  1622. IF NOT EXISTS (SELECT *  FROM sys.indexes  WHERE name='NCI_MEMBERS_1'
  1623. AND object_id = OBJECT_ID('[p4s].[Members]'))
  1624. BEGIN
  1625.     CREATE NONCLUSTERED INDEX [NCI_MEMBERS_1]
  1626.     ON [p4s].[Members]([MemberListId] ASC)
  1627.     INCLUDE([Id], [Code], [Name], [SortOrder], [Blocked]);
  1628. END
  1629.  
  1630.  
  1631. GO
  1632.  
  1633. UPDATE amb.VersionNew SET VersionDb = 256, HotfixDb = 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!