· 8 years ago · Mar 06, 2018, 10:06 PM
1public virtual int DeleteProjectData(Nullable<int> projectId, string deleteType, string username)
2 {
3 var projectIdParameter = projectId.HasValue ?
4 new ObjectParameter("projectId", projectId) :
5 new ObjectParameter("projectId", typeof(int));
6
7 var deleteTypeParameter = deleteType != null ?
8 new ObjectParameter("deleteType", deleteType) :
9 new ObjectParameter("deleteType", typeof(string));
10
11 var usernameParameter = username != null ?
12 new ObjectParameter("username", username) :
13 new ObjectParameter("username", typeof(string));
14
15 int result = ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("DeleteProjectData",
16
17projectIdParameter, deleteTypeParameter, usernameParameter);
18 return result; // result is -1
19
20
21
22
23}
24
25ALTER PROCEDURE [dbo].[DeleteProjectData]
26 @projectId INT,
27 @deleteType VARCHAR(10),
28 @username NVARCHAR(255)
29AS
30
31 SET NOCOUNT ON
32
33 BEGIN TRY
34
35 DECLARE @realProjectId INT = NULL,
36 @isTemplate BIT,
37 @ErrorMessage NVARCHAR(4000),
38 @deleteTypeAll VARCHAR(10),
39 @deleteTypeNodes VARCHAR(10)
40
41 DECLARE @usernameBinary VARBINARY(255)
42 SET @usernameBinary = CAST(@username AS VARBINARY(255))
43 SET CONTEXT_INFO @usernameBinary
44
45 SELECT @deleteTypeAll = 'All',
46 @deleteTypeNodes = 'Nodes'
47
48 -- ensure the input project exists and is not a template
49 SELECT @realProjectId = ProjectId
50 FROM Project
51 WHERE ProjectId = @projectId
52
53 IF @deleteType NOT IN (@deleteTypeAll, @deleteTypeNodes)
54 BEGIN
55 -- create a the error message for a project that does not exist
56 SELECT @ErrorMessage = N'The input Delete Type is not valid. Valid values are: ''' + @deleteTypeAll
57 + ''' and ''' + @deleteTypeNodes + ''''
58
59 -- raise the error
60 RAISERROR (@ErrorMessage, 16, 1)
61
62 END
63
64 IF @realProjectId IS NULL
65 BEGIN
66 -- create a the error message for a project that does not exist
67 SELECT @ErrorMessage = N'The input ProjectId does not have a corresponding Project record. No calculations will
68
69be performed. ProjectId: ' + CAST(@projectId AS VARCHAR)
70
71 -- raise the error
72 RAISERROR (@ErrorMessage, 16, 1)
73
74 END
75
76 BEGIN TRANSACTION
77
78 -- If deleting all, delete all reports associated with project first:
79 IF @deleteType = @deleteTypeAll
80 BEGIN
81
82 DELETE [dbo].[ReportComment]
83 WHERE ReportId in (SELECT ReportId
84 FROM [dbo].[Report]
85 WHERE ProjectId = @realProjectId)
86
87 DELETE [dbo].[ReportMetric]
88 WHERE ReportId in (SELECT ReportId
89 FROM [dbo].[Report]
90 WHERE ProjectId = @realProjectId)
91
92 DELETE [dbo].[ReportTopN]
93 WHERE ReportId in (SELECT ReportId
94 FROM [dbo].[Report]
95 WHERE ProjectId = @realProjectId)
96
97 DELETE [dbo].[Report]
98 WHERE ReportId in (SELECT ReportId
99 FROM [dbo].[Report]
100 WHERE ProjectId = @realProjectId)
101
102 END
103 ELSE
104 BEGIN
105
106 DELETE [dbo].[ReportTopN]
107 WHERE ReportId in (SELECT ReportId
108 FROM [dbo].[Report]
109 WHERE ProjectId = @realProjectId)
110
111 END
112
113 IF @deleteType IN (@deleteTypeAll, @deleteTypeNodes)
114 BEGIN
115 DECLARE @Keys TABLE (
116 ProjectId INT,
117 NodeId INT,
118 DeviationId INT,
119 CauseId INT,
120 ConsequenceId INT,
121 SafeguardId INT,
122 RecommendationId INT,
123 RemarkId INT,
124 DrawingId INT,
125 RiskDataId INT,
126 BowtieLoopId INT,
127 BowtieId INT)
128
129 -- build a list of keys associated with the project (simplifies delete queries)
130 INSERT INTO @Keys (ProjectId,
131 NodeId,
132 DeviationId,
133 CauseId,
134 ConsequenceId,
135 SafeguardId,
136 RecommendationId,
137 RemarkId,
138 DrawingId,
139 RiskDataId,
140 BowtieLoopId,
141 BowtieId)
142 SELECT p.ProjectId,
143 n.NodeId,
144 d.DeviationId,
145 ca.CauseId,
146 co.ConsequenceId,
147 s.SafeguardId,
148 r.RecommendationId,
149 re.RemarkId,
150 dr.DrawingId,
151 rd.RiskDataId,
152 bl.BowtieLoopId,
153 b.BowtieId
154 FROM Project p
155 LEFT OUTER JOIN Node n
156 ON p.ProjectId = n.ProjectId
157 LEFT OUTER JOIN Deviation d
158 ON n.NodeId = d.NodeId
159 LEFT OUTER JOIN Cause ca
160 ON d.DeviationId = ca.DeviationId
161 LEFT OUTER JOIN Consequence co
162 ON ca.CauseId = co.CauseId
163 LEFT OUTER JOIN Safeguard s
164 ON co.ConsequenceId = s.ConsequenceId
165 LEFT OUTER JOIN Recommendation r
166 ON co.ConsequenceId = r.ConsequenceId
167 LEFT OUTER JOIN Remark re
168 ON co.ConsequenceId = re.ConsequenceId
169 LEFT OUTER JOIN Drawing dr
170 ON r.RecommendationId = dr.RecommendationId
171 LEFT OUTER JOIN Bowtie b
172 ON b.ProjectId = p.ProjectId
173 LEFT OUTER JOIN BowtieLoop bl
174 ON bl.BowtieId = b.BowtieId
175 LEFT Outer JOIN RiskData rd
176 ON rd.BowtieLoopId = bl.BowtieLoopId
177
178 WHERE p.ProjectId = @realProjectId
179
180 -- delete the data that was imported
181
182 DELETE FROM Drawing
183 WHERE DrawingId IN (SELECT DISTINCT DrawingId
184 FROM @Keys)
185
186 DELETE FROM Recommendation
187 WHERE RecommendationId IN (SELECT DISTINCT RecommendationId
188 FROM @Keys)
189
190 DELETE FROM Safeguard
191 WHERE SafeguardId IN (SELECT DISTINCT SafeguardId
192 FROM @Keys)
193
194 DELETE FROM Remark
195 WHERE RemarkId IN (SELECT DISTINCT RemarkId
196 FROM @Keys)
197
198 DELETE FROM Consequence
199 WHERE ConsequenceId IN (SELECT DISTINCT ConsequenceId
200 FROM @Keys)
201
202 DELETE FROM CauseToBowtieLoopDetails
203 Where CauseId IN (Select Distinct CauseId
204 FROM @Keys)
205
206 DELETE FROM Cause
207 WHERE CauseId IN (SELECT DISTINCT CauseId
208 FROM @Keys)
209
210 DELETE FROM Deviation
211 WHERE DeviationId IN (SELECT DISTINCT DeviationId
212 FROM @Keys)
213
214 DELETE FROM Node
215 WHERE NodeId IN (SELECT DISTINCT NodeId
216 FROM @Keys)
217
218 DELETE FROM RiskData
219 WHERE RiskDataId IN (SELECT DISTINCT RiskDataId
220 FROM @Keys)
221
222 DELETE FROM BowtieLoop
223 WHERE BowtieLoopId IN (SELECT DISTINCT BowtieLoopId
224 FROM @Keys)
225
226 DELETE FROM BowtieToEquipmentLookup
227 WHERE BowtieId IN (SELECT DISTINCT BowtieId
228 FROM @Keys)
229
230 DELETE FROM Bowtie
231 WHERE BowtieId IN (SELECT DISTINCT BowtieId
232 FROM @Keys)
233
234 DELETE FROM ProjectDeviation
235 WHERE ProjectId = @realProjectId
236
237 DELETE FROM ProjectSafeguard
238 WHERE ProjectId = @realProjectId
239
240 DELETE FROM ProjectRecommendation
241 WHERE ProjectId = @realProjectId
242
243 -- also delete from the metrics tables
244
245 DELETE FROM ProjectMetrics
246 WHERE ProjectId = @realProjectId
247
248 DELETE FROM CauseMetrics
249 WHERE ProjectId = @realProjectId
250
251 DELETE FROM ConsequenceMetrics
252 WHERE ProjectId = @realProjectId
253
254 DELETE FROM RecommendationMetrics
255 WHERE ProjectId = @realProjectId
256
257 DELETE FROM SafeguardMetrics
258 WHERE ProjectId = @realProjectId
259
260 END
261
262 IF @deleteType = @deleteTypeAll
263 BEGIN
264
265 -- delete the project specific data (i.e., data not imported)
266
267 DELETE FROM RiskMatrixAxis
268 WHERE ProjectId = @realProjectId
269
270 DELETE FROM SafeRecCategory
271 WHERE ProjectId = @realProjectId
272
273 DELETE FROM Participant
274 WHERE ProjectId = @realProjectId
275
276 DELETE FROM RiskRanking
277 WHERE ProjectId = @realProjectId
278
279 DELETE FROM Category
280 WHERE ProjectId = @realProjectId
281
282 DELETE FROM ImportFile
283 WHERE ProjectId = @realProjectId
284
285 DELETE FROM Project
286 WHERE ProjectId = @realProjectId
287
288 END
289
290 COMMIT TRANSACTION
291
292 END TRY
293
294 BEGIN CATCH
295
296 IF(@@TRANCOUNT > 0)
297 BEGIN
298 -- rollback all changes if any error occurred
299 ROLLBACK TRANSACTION
300 END
301
302 -- raise the original error
303 EXEC RethrowError;
304 END CATCH