· 4 years ago · Feb 12, 2021, 11:40 AM
1SET QUOTED_IDENTIFIER ON
2GO
3SET ANSI_NULLS ON
4GO
5IF EXISTS (SELECT *
6 FROM dbo.sysobjects
7 WHERE id = OBJECT_ID(N'dbo.[RD_TIMING_VALIDATE]') AND
8 OBJECTPROPERTY(id,N'IsProcedure') = 1)
9BEGIN
10 DROP PROCEDURE dbo.[RD_TIMING_VALIDATE]
11END
12GO
13/*******************************************************************************
14
15 © Кейсистемс (Keysystems), 2019
16
17*******************************************************************************/
18CREATE PROCEDURE [dbo].[RD_TIMING_VALIDATE]
19AS
20DECLARE @tempTable TABLE ( [LINK_TIMING] INT,
21 [LINK_TIMING_FORM] INT,
22 [LINK_TIMING_FORM_TEMP] INT,
23 [CODE_FORM] VARCHAR(250),
24 [LINK_BUDGET] INT,
25 [LINK_ORGANIZATION] INT,
26 [LINK_ATTRIBUTE_FORM] INT,
27 [LINK_DICTION_ROW] INT)
28---------------------------------------------------------------------------------------------------------------------------
29-- 1. Создание отсутствующих входных таблиц для корректной работы процедуры.
30---------------------------------------------------------------------------------------------------------------------------
31IF OBJECT_ID('tempdb..#tabTiming') IS NULL
32BEGIN
33 CREATE TABLE #tabTiming( [DEL] TINYINT,
34 [LINK_TIMING] INT,
35 [CODE] VARCHAR(50),
36 [NAME] VARCHAR(1000),
37 [NOTE] VARCHAR(8000),
38 [TYPE] TINYINT,
39 [BEGIN_DATE] SMALLDATETIME,
40 [END_DATE] SMALLDATETIME,
41 [DAY_START] TINYINT,
42 [DAY_END] TINYINT,
43 [CREATE_SAVED_FORMS] BIT DEFAULT 0,
44 [ALLOW_DELETE_SAVED_FORMS] BIT DEFAULT 0,
45 [USE_END_OF_TIMING_IN_NOTIFICATION] INT DEFAULT 0)
46END
47
48IF OBJECT_ID('tempdb..#tabTimingPeriod') IS NULL
49BEGIN
50 CREATE TABLE #tabTimingPeriod( [DEL] TINYINT,
51 [LINK_TIMING] INT,
52 [PERIOD_TYPE] TINYINT,
53 [PERIOD_NUMBER] INT,
54 [YEAR] INT)
55END
56
57IF OBJECT_ID('tempdb..#tabTimingUser') IS NULL
58BEGIN
59 CREATE TABLE #tabTimingUser( [DEL] TINYINT,
60 [LINK_TIMING] INT,
61 [LINK_USER] INT)
62END
63
64IF OBJECT_ID('tempdb..#tabTimingForm') IS NULL
65BEGIN
66 CREATE TABLE #tabTimingForm( [DEL] TINYINT,
67 [LINK_TIMING] INT,
68 [LINK_FORM] INT,
69 [LINK] INT,
70 [LINK_GROUP] INT,
71 [CODE] VARCHAR(250))
72END
73
74IF OBJECT_ID('tempdb..#tabTimingFormAttributes') IS NULL
75BEGIN
76 CREATE TABLE #tabTimingFormAttributes( [DEL] TINYINT,
77 [LINK] INT,
78 [LINK_TIMING_FORM] INT,
79 [LINK_DICTION_ROW] INT,
80 [STRING] VARCHAR(8000),
81 [VALUE] NUMERIC(19,4),
82 [LINK_ATTRIBUTE_FORM] INT,
83 [LINK_ATTRIBUTE] INT)
84END
85
86IF OBJECT_ID('tempdb..#tabTimingBudgetHierarchy') IS NULL
87BEGIN
88 CREATE TABLE #tabTimingBudgetHierarchy( [DEL] TINYINT,
89 [LINK_TIMING] INT,
90 [LINK_BUDGET] INT,
91 [LINK_ORGANIZATION] INT)
92END
93---------------------------------------------------------------------------------------------------------------------------
94-- 2. Получение дублирующейся формы и задачи в которой она уже содержится.
95---------------------------------------------------------------------------------------------------------------------------
96INSERT INTO @tempTable ([LINK_TIMING], [LINK_TIMING_FORM], [LINK_TIMING_FORM_TEMP], [CODE_FORM], [LINK_BUDGET], [LINK_ORGANIZATION], [LINK_ATTRIBUTE_FORM], [LINK_DICTION_ROW])
97SELECT DISTINCT
98 A.LINK_TIMING AS LINK_TIMING,
99 A.LINK AS LINK_TIMING_FORM,
100 B.LINK AS LINK_TIMING_FORM_TEMP,
101 A.CODE_FORM AS CODE_FORM,
102 A.LINK_BUDGET AS LINK_BUDGET,
103 A.LINK_ORGANIZATION AS LINK_ORGANIZATION,
104 A.LINK_ATTRIBUTE_FORM AS LINK_ATTRIBUTE_FORM,
105 A.LINK_DICTION_ROW AS LINK_DICTION_ROW
106FROM
107(
108 SELECT
109 A.LINK_TIMING,
110 A.PERIOD_TYPE,
111 A.PERIOD_NUMBER,
112 A.[YEAR],
113 B.LINK,
114 B.CODE_FORM,
115 C.LINK_BUDGET,
116 C.LINK_ORGANIZATION,
117 D.LINK_ATTRIBUTE_FORM,
118 D.LINK_DICTION_ROW
119 FROM dbo.R_TIMING_PERIOD [A]
120 INNER JOIN dbo.R_TIMING_FORM [B]
121 ON A.LINK_TIMING = B.LINK_TIMING
122 INNER JOIN dbo.R_TIMING_BUDGET_HIERARCHY [C]
123 ON A.LINK_TIMING = C.LINK_TIMING
124 LEFT JOIN dbo.R_TIMING_FORM_ATTRIBUTES [D]
125 ON B.LINK = D.LINK_TIMING_FORM
126) [A]
127INNER JOIN
128(
129 SELECT
130 A.LINK_TIMING,
131 A.PERIOD_TYPE,
132 A.PERIOD_NUMBER,
133 A.[YEAR],
134 B.LINK,
135 B.CODE,
136 C.LINK_BUDGET,
137 C.LINK_ORGANIZATION
138 FROM #tabTimingPeriod [A]
139 INNER JOIN #tabTimingForm [B]
140 ON A.LINK_TIMING = B.LINK_TIMING
141 INNER JOIN #tabTimingBudgetHierarchy [C]
142 ON A.LINK_TIMING = C.LINK_TIMING
143) [B]
144ON
145 A.LINK_TIMING <> B.LINK_TIMING AND
146 (A.PERIOD_TYPE = B.PERIOD_TYPE OR (A.PERIOD_TYPE IS NULL AND B.PERIOD_TYPE IS NULL)) AND
147 (A.PERIOD_NUMBER = B.PERIOD_NUMBER OR (A.PERIOD_NUMBER IS NULL AND B.PERIOD_NUMBER IS NULL)) AND
148 (A.[YEAR] = B.[YEAR] OR (A.[YEAR] IS NULL AND B.[YEAR] IS NULL)) AND
149 A.CODE_FORM = B.CODE AND
150 A.LINK_BUDGET = B.LINK_BUDGET AND
151 A.LINK_ORGANIZATION = B.LINK_ORGANIZATION;
152
153-- Проверяем аттрибуты формы...
154DECLARE @iLINK_TIMING_FORM INT, @iLINK_TIMING_FORM_TEMP INT
155DECLARE CUR CURSOR FOR SELECT DISTINCT LINK_TIMING_FORM, LINK_TIMING_FORM_TEMP FROM @tempTable
156OPEN CUR
157FETCH CUR INTO @iLINK_TIMING_FORM, @iLINK_TIMING_FORM_TEMP
158WHILE @@FETCH_STATUS = 0
159 BEGIN
160 IF EXISTS(
161 SELECT *
162 FROM @tempTable [A]
163 LEFT JOIN #tabTimingFormAttributes [C]
164 ON A.LINK_TIMING_FORM_TEMP = C.LINK_TIMING_FORM
165 WHERE A.LINK_TIMING_FORM = @iLINK_TIMING_FORM AND A.LINK_TIMING_FORM_TEMP = @iLINK_TIMING_FORM_TEMP AND
166 -- Если один из LINK_ATTRIBUTE_FORM NULL, а другой не NULL, то на LINK_DICTION_ROW не смотрим...
167 (((A.LINK_ATTRIBUTE_FORM IS NOT NULL AND C.LINK_ATTRIBUTE_FORM IS NULL) OR
168 (A.LINK_ATTRIBUTE_FORM IS NULL AND C.LINK_ATTRIBUTE_FORM IS NOT NULL)) OR
169 -- Если LINK_ATTRIBUTE_FORM совпадают, ищем хоть один отличающийся LINK_DICTION_ROW...
170 (A.LINK_ATTRIBUTE_FORM = C.LINK_ATTRIBUTE_FORM AND
171 (A.LINK_DICTION_ROW <> C.LINK_DICTION_ROW OR
172 (A.LINK_DICTION_ROW IS NULL AND C.LINK_DICTION_ROW IS NOT NULL) OR
173 (A.LINK_DICTION_ROW IS NOT NULL AND C.LINK_DICTION_ROW IS NULL)))))
174 DELETE FROM @tempTable WHERE LINK_TIMING_FORM = @iLINK_TIMING_FORM AND LINK_TIMING_FORM_TEMP = @iLINK_TIMING_FORM_TEMP
175
176 FETCH NEXT FROM CUR INTO @iLINK_TIMING_FORM, @iLINK_TIMING_FORM_TEMP
177 END
178CLOSE CUR
179DEALLOCATE CUR
180---------------------------------------------------------------------------------------------------------------------------
181-- 3. Результат.
182---------------------------------------------------------------------------------------------------------------------------
183SELECT DISTINCT
184 B.CODE AS NAME_TIMING,
185 A.CODE_FORM AS CODE_FORM,
186 D.NAME AS BUDGET,
187 E.NAME AS ORGANIZATION
188FROM @tempTable [A]
189INNER JOIN dbo.R_TIMING [B]
190 ON A.LINK_TIMING = B.LINK_TIMING
191INNER JOIN dbo.R_BUDGET [D]
192 ON A.LINK_BUDGET = D.LINK_BUDGET
193INNER JOIN dbo.R_ORGANIZATION [E]
194 ON A.LINK_ORGANIZATION = E.LINK_ORGANIZATION;
195
196GO
197
198GRANT EXECUTE ON dbo.[RD_TIMING_VALIDATE] TO PUBLIC
199GO
200SET QUOTED_IDENTIFIER OFF
201GO
202SET ANSI_NULLS ON
203GO