· 8 years ago · Mar 09, 2018, 10:40 AM
1USE [limeprod]
2GO
3/****** Object: StoredProcedure [dbo].[csp_afterupdate] Script Date: 2016-03-17 09:59:43 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- This procedure is run after each add/update/delete of data in LIME.
10-- IMPORTANT: Always avoid cursors if possible!
11
12ALTER PROCEDURE [dbo].[csp_afterupdate]
13 @@sessionidentifier NVARCHAR(64) = N''
14 , @@iduser INT = NULL
15 , @@transactionid UNIQUEIDENTIFIER
16AS
17BEGIN
18
19 -- Variables needed
20 DECLARE @relevantrecordscount INT
21 DECLARE @new BIT
22 DECLARE @updated BIT
23 DECLARE @deleted BIT
24 DECLARE @relation BIT
25 DECLARE @idrecord INT
26
27 -- Table to hold data from the updatelog table
28 DECLARE @temptable TABLE
29 (
30 [table] NVARCHAR(64),
31 [idrecord] INT,
32 [new] INT,
33 [updated] INT,
34 [deleted] INT,
35 [relation] INT
36 )
37
38 -- Find data to work with
39 INSERT INTO @temptable
40 (
41 [table]
42 , [idrecord]
43 , [new]
44 , [updated]
45 , [deleted]
46 , [relation]
47 )
48 SELECT [table]
49 , [idrecord]
50 , [new]
51 , [updated]
52 , [deleted]
53 , [relation]
54 FROM [updatelog]
55 WHERE [transactionid] = @@transactionid
56
57 -- Start doing stuff if we should
58 SELECT @relevantrecordscount = COUNT(*)
59 FROM @temptable
60 WHERE [table] IN (N'helpdesktime', N'history', N'product') -- Add all tables that there is code for in this procedure
61
62 IF @relevantrecordscount > 0
63 BEGIN
64
65
66 --Treat history records
67 -------------------------------------------------------
68 IF EXISTS
69 (
70 SELECT 0
71 FROM @temptable t
72 WHERE (t.updated = 1 OR t.new = 1)
73 AND t.[table] = N'history'
74 )
75 BEGIN
76
77 --- Mail gateway undreadhistory
78 DECLARE @idstringunread INT
79 DECLARE @idstringempty INT
80 SELECT @idstringunread = [dbo].[cfn_getidstringbykey](N'helpdesk', N'unreadhistory', N'unread')
81 SELECT @idstringempty = [dbo].[cfn_getidstringbykey](N'helpdesk', N'unreadhistory', N'empty')
82
83 -- Set the unreadhistory field on related helpdesk cases
84 UPDATE helpdesk
85 SET unreadhistory = CASE
86 WHEN EXISTS
87 (
88 SELECT idhistory
89 FROM history hi
90 WHERE hi.helpdesk = idhelpdesk
91 AND hi.[status] = 0
92 AND hi.[read] = 0
93 )
94 THEN @idstringunread
95 ELSE @idstringempty
96 END
97 , updateduser = @@iduser
98 , [timestamp] = GETDATE()
99 WHERE idhelpdesk IN
100 (
101 SELECT h.helpdesk
102 FROM history h
103 INNER JOIN @temptable t
104 ON t.idrecord = h.idhistory
105 AND (t.updated = 1 OR t.new = 1)
106 AND t.[table] = N'history'
107 WHERE h.helpdesk IS NOT NULL
108 )
109
110 END
111
112
113 IF OBJECT_ID('curProduct') IS NOT NULL
114 DEALLOCATE curProduct
115
116 --Make sure that SQL on update in executed on SOS when a activity is created
117 DECLARE curProduct CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
118
119 SELECT DISTINCT [business]
120 FROM [product]
121 WHERE [idproduct] IN
122 (
123 SELECT [idrecord] FROM @temptable
124 WHERE [table] = N'product'
125 AND ([new] = 1 OR [deleted] = 1 OR [updated] = 1)
126 )
127 AND [business] IS NOT NULL
128
129 OPEN curProduct
130
131 FETCH NEXT FROM curProduct INTO @idrecord
132 WHILE @@FETCH_STATUS = 0
133 BEGIN
134 EXECUTE lsp_addupdatelog @@table = N'product',
135 @@idrecord = @idrecord,
136 @@transactionid = @@transactionid,
137 @@iduser = @@iduser,
138 @@sessionidentifier = @@sessionidentifier,
139 @@updated = 0
140 FETCH NEXT FROM curProduct INTO @idrecord
141 END
142
143 CLOSE curProduct
144 DEALLOCATE curProduct
145
146
147
148
149
150
151 IF EXISTS
152 (
153 SELECT 0
154 FROM @temptable t
155 WHERE t.[table] = N'product'
156 )
157 BEGIN
158
159 IF OBJECT_ID('curProduct') IS NOT NULL
160 DEALLOCATE curProduct
161
162 --Make sure that SQL on update in executed on SOS when a activity is created
163 DECLARE curProduct CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
164 SELECT DISTINCT [business]
165 FROM [product]
166 WHERE [idproduct] IN
167 (
168 SELECT [idrecord] FROM @temptable
169 WHERE [table] = N'product'
170 AND ([new] = 1 OR [deleted] = 1 OR [updated] = 1)
171 )
172 AND [business] IS NOT NULL
173
174 OPEN curProduct
175
176 FETCH NEXT FROM curProduct INTO @idrecord
177 WHILE @@FETCH_STATUS = 0
178 BEGIN
179 EXECUTE lsp_addupdatelog @@table = N'business',
180 @@idrecord = @idrecord,
181 @@transactionid = @@transactionid,
182 @@iduser = @@iduser,
183 @@sessionidentifier = @@sessionidentifier,
184 @@updated = 0
185 FETCH NEXT FROM curProduct INTO @idrecord
186 END
187
188 CLOSE curProduct
189 DEALLOCATE curProduct
190
191
192 ----CREATE TABLE #Id (idbusiness int, valuesvr int, valuebus int, businessvalue int, valuebusinsurance int, valuebusps int)
193 ----INSERT INTO #Id (idbusiness, valuesvr, valuebus, businessvalue, valuebusinsurance, valuebusps)
194 ----SELECT distinct business, 0, 0, 0, 0, 0 from product where idproduct in (select distinct idrecord from @temptable where [table] = N'product')
195
196 ----UPDATE #Id set valuesvr =
197 ----(SELECT sum(isnull(p.[price],0))
198 ----FROM product p inner join business b on b.idbusiness = p.business
199 ----inner join #Id i on p.business = i.idbusiness
200 ----WHERE p.[productstatus] <> 351501
201 ----and p.productlist = 1001
202 ----GROUP BY b.idbusiness)
203
204 ----UPDATE #Id set valuebus =
205 ----(SELECT sum(isnull(p.[price],0))
206 ----FROM product p inner join business b on b.idbusiness = p.business
207 ----inner join #Id i on p.business = i.idbusiness
208 ----WHERE p.[productstatus] <> 351501
209 ----and p.productlist = 1301
210 ----and p.typeofservice not in (227001, 350701)
211 ----GROUP BY b.idbusiness)
212
213 ----UPDATE #Id set valuebusinsurance =
214 ----(SELECT sum(isnull(p.[price],0))
215 ----FROM product p inner join business b on b.idbusiness = p.business
216 ----inner join #Id i on p.business = i.idbusiness
217 ----WHERE p.[productstatus] <> 351501
218 ----and p.productlist = 1301
219 ----and p.typeofservice = 350701
220 ----GROUP BY b.idbusiness)
221
222 ----UPDATE #Id set valuebusps =
223 ----(SELECT sum(isnull(p.[price],0))
224 ----FROM product p inner join business b on b.idbusiness = p.business
225 ----inner join #Id i on p.business = i.idbusiness
226 ----WHERE p.[productstatus] <> 351501
227 ----and p.productlist = 1301
228 ----and p.typeofservice = 227001
229 ----GROUP BY b.idbusiness)
230
231 ----UPDATE #Id set businessvalue =
232 ----(SELECT sum(isnull(p.[price],0))
233 ----FROM product p inner join business b on b.idbusiness = p.business
234 ----inner join #Id i on p.business = i.idbusiness
235 ----WHERE p.[productstatus] <> 351501
236 ----GROUP BY b.idbusiness)
237
238 ----UPDATE business set
239 ----valuesvr = isnull(temp.valuesvr, 0),
240 ----valuebus = isnull(temp.valuebus, 0),
241 ----businessvalue2 = isnull(temp.businessvalue, 0),
242 ----valuebusinsurance = isnull(temp.valuebusinsurance, 0),
243 ----valuebusps = isnull(temp.valuebusps, 0)
244 ----FROM business b inner join #Id temp on b.idbusiness = temp.idbusiness
245
246 ----DROP TABLE #Id
247
248 END
249 -- -------------------------------------------------------
250
251 END
252END