· 7 years ago · Dec 13, 2018, 07:34 AM
1USE [GI_RYCHLY_START]
2GO
3/****** Object: StoredProcedure [gist].[gsp_io_dim_odberatel] Script Date: 13.12.2018 8:30:33 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- =============================================
10-- Author: DR
11-- Create date: 13.9.2018
12-- =============================================
13ALTER PROCEDURE [gist].[gsp_io_dim_odberatel]
14 @id_pump_out int, -- ID pumpy OUT
15 @io_table varchar(255),/*VM tato tabulka musà existovcat v DB CO a může se plnit postupnÄ› z vÃce zdrojů (napÅ™. vÃce DB HEG)
16 -- nelze to použÃt jako odakz na tabulku nebo VIEW ve zdrojové DB. Název zdrojového VIEW nutno použÃt (jako string) v samotném SELECT nÞe*/
17 @tmp_yes smallint, -- 1/0 = ANO/NE pro použità TMP tabulek
18 @parent_process varchar(50)
19
20AS
21
22 DECLARE @iddim int, @dim_name varchar(50), @txt_sql varchar(8000), @process varchar(50),
23 @text varchar(255),@error varchar(8000),@errmsg varchar(8000),@errno int,
24 @idhier int,@sp_name varchar(255),@type_target varchar(10),@id_target int,@id_attr int
25
26
27 /*VM Obecná sekce pro informace o dimenzi podle @id_pump_out*/
28 SELECT @sp_name=sp_name,@type_target=type_target ,@id_target=id_target,@id_attr=id_attr
29 FROM s4v_pumps_out
30 WHERE id_pump_out=@id_pump_out
31
32 SET @iddim=@id_target
33 SET @process =@sp_name
34
35 --nacteme nazev dimeze
36 SELECT @dim_name= ISNULL(name,LTRIM(RTRIM(STR(@id_target))))
37 FROM sys_dimension
38 WHERE iddim = @id_target
39 /*VM Konec - Obecná sekce pro informace o dimenzi podle @id_pump_out*/
40
41 --zapis zacatku zpracovani do logu
42 SET @text ='Start datového přenosu z tabulky "' + @io_table + '" do dimeze '+ @dim_name
43 EXEC gcsp_write_log 'Information','Pumpa pro plněnà dimenze',@text, 0, @parent_process,@sp_name,'Datove_prenosy_detail'
44
45 --kontroly
46 IF @type_target<>'DIM' BEGIN
47 SET @error ='Procedura nemůže zpracovat data pro typ cÃle type_target="' + @type_target+ '",
48 protože je urÄena jen pro type_target="DIM" . Transakce se neprovede!!!'
49 GOTO error
50 END
51
52 IF @id_attr <>0 BEGIN
53 SET @error ='Procedura nemůže zpracovat data pro typ atribut dimenze id_attr="' + ltrim(str(@id_attr)) + '",
54 protože je urÄena jen pro prvky dimenzÃ" . Transakce se neprovede!!!'
55 GOTO error
56 END
57
58 IF not exists (SELECT * FROM sys_dimension WHERE iddim=@id_target) BEGIN
59 SET @error ='Procedura nemůže zpracovat data pro dimenzi IDdim="' + ltrim(str(@id_target)) + '",
60 protože v modulu CO nenà taková dimenze známá" . Transakce se neprovede!!!'
61 GOTO error
62 END
63
64 --zpracovávaná dimenze
65 SET @iddim=@id_target
66--/---------------------------------------------------------------------ZACIATOK UPRAV PROCKY
67 -- drop table #tmp_ta_dim1
68 CREATE TABLE #tmp_ta_dim1 (
69 [parent_ext] [varchar](30) COLLATE database_default NOT NULL,
70 [code_ext] [varchar](30) COLLATE database_default NOT NULL,
71 [code_name] [varchar](150) COLLATE database_default NOT NULL,
72 [idhier] [int] NOT NULL,
73 [list] [int] NULL,
74 [ord] [int] NULL)
75
76
77
78
79
80
81 /*dle OZ - 53103 */
82-- uzly
83INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
84SELECT distinct '@nechat@kde@je@2',id_obch_zast,obch_zast,53103,0,0
85FROM gist.cgist_temp2
86
87-- listy
88INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
89SELECT distinct id_obch_zast,cislo_odber,cislo_odber+' '+nazev_odber,53103,1,0
90FROM gist.cgist_temp2
91
92 /*dle Zemà - 53104 */
93INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
94SELECT distinct '@nechat@kde@je@2',id_zeme,zeme,53104,0,0
95FROM gist.cgist_temp2
96
97INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
98SELECT distinct id_zeme,cislo_odber,cislo_odber+' '+nazev_odber,53104,1,0
99FROM gist.cgist_temp2
100
101
102 /*dle Skupin - 53105 */
103INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
104SELECT distinct '@nechat@kde@je@2',id_skupina_odb,skupina_odb,53105,0,0
105FROM gist.cgist_temp2
106
107INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
108SELECT distinct id_skupina_odb,cislo_odber,cislo_odber+' '+nazev_odber,53105,1,0
109FROM gist.cgist_temp2
110
111
112 select * from gist.cgist_temp2
113
114
115---------/-------------------------------------------------------------------------KONIEC Uziv. casti
116 --vlozeni do ta_dim
117 INSERT INTO ta_dim (dim_name,hier_name,parent_ext,code_ext,code_name,ord,list,iddim,idhier,attr0)
118 SELECT 'x' as dim_name,'x' as hier_name, parent_ext,code_ext,code_name,ord,list,@iddim,idhier,0
119 FROM #tmp_ta_dim1
120
121 IF @@error <> 0 BEGIN
122 SET @error = 'INSERT INTO ta_dim.....'
123 GOTO error
124 END
125
126 --doplneni atributu nazev aktivity-----------------------------------------------------------------
127
128update e
129set e.attribute_16 = t.cislo_odber,
130e.attribute_17 = t.nazev_odber,
131e.attribute_18 = t.skupina_odb,
132e.attribute_19 = t.obch_zast,
133e.attribute_20 = t.zeme,
134e.attribute_21 = t.region
135from gist.cgist_temp2 t
136join gist.dim_element_533 e on (e.attribute_13 = t.cislo_odber)
137
138-----drop tmp dim1-------
139IF OBJECT_ID('temmpdb..#tmp_ta_dim1') IS NOT NULL
140 DROP TABLE #tmp_ta_dim1
141
142
143 --zapis do logu
144 SET @text ='Konec datového přenosu z tabulky "' + @io_table + '" do dimeze '+ @dim_name
145 EXEC gcsp_write_log 'Information','Pumpa pro plněnà dimenze',@text, 0, @parent_process,@sp_name,'Datove_prenosy_detail'
146
147RETURN 0
148
149ERROR:
150
151 SELECT @errno = 13000, @errmsg = 'Uložená procedura "' + @process + '" skonÄila chybou - ' + @error
152 RAISERROR (@errmsg, 16, 1)
153 EXEC gcsp_write_log 'Error','Pumpa pro plněnà dimenze',@text, 0, @parent_process,@sp_name,'Datove_prenosy_detail'
154
155 RETURN -1