· 10 years ago · Apr 14, 2016, 11:30 AM
1sp_configure 'show advanced options',1
2GO
3RECONFIGURE WITH OVERRIDE
4GO
5sp_configure 'Database Mail XPs',1
6GO
7RECONFIGURE
8GO
9
10
11EXECUTE msdb.dbo.sysmail_add_account_sp
12@account_name = 'EwiMail',
13@enable_ssl = 1,
14@description = 'Sent Mail using MSDB',
15@email_address = 'eworkin.test@gmail.com',
16@display_name = 'EWI',
17@username='eworkin.test@gmail.com',
18@password='eworkin.1',
19@port=587,
20@mailserver_name = 'smtp.gmail.com'
21
22
23EXECUTE msdb.dbo.sysmail_add_profile_sp
24@profile_name = 'EwiMail',
25@description = 'Profile used to send mail'
26
27EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
28@profile_name = 'EwiMail',
29@account_name = 'EwiMail',
30@sequence_number = 1
31
32
33EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
34@profile_name = 'EwiMail',
35@principal_name = 'public',
36@is_default = 1 ;
37
38
39
40/****** Object: StoredProcedure [dbo].[rRaiseTaskPriority] Script Date: 2016-04-13 14:17:28 ******/
41DROP PROCEDURE [dbo].[rRaiseTaskPriority]
42GO
43
44/****** Object: StoredProcedure [dbo].[rRaiseTaskPriority] Script Date: 2016-04-13 14:17:28 ******/
45SET ANSI_NULLS ON
46GO
47
48SET QUOTED_IDENTIFIER ON
49GO
50
51CREATE PROCEDURE [dbo].[rRaiseTaskPriority]
52 @threshold INT
53AS
54BEGIN
55 declare @history table (from_status varchar,
56 to_status varchar,
57 wip_operation_id INT,
58 comment varchar,
59 transaction_code varchar,
60 user_id INT,
61 date datetime2,
62 row_version INT,
63 deleted INT);
64
65 UPDATE dbo.wip_operations
66 SET wip_priority = wip_priority + 1,
67 entity_version = entity_version +1,
68 updated_by = 'admin',
69 updated_on = SYSDATETIME()
70 OUTPUT INSERTED.STATUS,
71 INSERTED.STATUS,
72 INSERTED.wip_operation_id,
73 NULL,
74 NULL,
75 1,
76 INSERTED.updated_on,
77 INSERTED.entity_version,
78 0 INTO @history
79 WHERE DATEDIFF(ss,ISNULL(valid_from,created_on),SYSDATETIME()) > @threshold
80 AND STATUS = 1
81
82 INSERT INTO dbo.wip_operation_history
83 SELECT * FROM @history;
84END;
85
86
87
88GO
89
90/****** Object: StoredProcedure [dbo].[rSendEmail] Script Date: 2016-04-13 14:17:38 ******/
91DROP PROCEDURE [dbo].[rSendEmail]
92GO
93
94/****** Object: StoredProcedure [dbo].[rSendEmail] Script Date: 2016-04-13 14:17:38 ******/
95SET ANSI_NULLS ON
96GO
97
98SET QUOTED_IDENTIFIER ON
99GO
100
101CREATE PROCEDURE [dbo].[rSendEmail]
102@queryq NVARCHAR(4000),
103@template NVARCHAR(MAX)
104AS
105BEGIN
106
107DECLARE @sql2 NVARCHAR(150);
108DECLARE @columnName NVARCHAR(50);
109DECLARE @columnValue NVARCHAR(50);
110DECLARE @tempReplace NVARCHAR(4000);
111declare @recipients table (Id int NOT NULL identity(1,1), email NVARCHAR(100));
112declare @topic NVARCHAR(255) = 'EWI_NO_TOPIC';
113
114declare @sql NVARCHAR(4000) = 'SELECT * INTO ##emailTempTable
115 FROM ('+@queryq+') subquery';
116select @sql;
117
118declare @html NVARCHAR(MAX) = @template;
119
120IF OBJECT_ID('tempdb..##emailTempTable') IS NOT NULL
121 DROP TABLE ##emailTempTable
122
123EXECUTE sp_executesql @sql;
124
125declare @start INT = CHARINDEX('<ewi-repeat>', @html);
126declare @end INT = CHARINDEX('</ewi-repeat>', @html) + LEN('</ewi-repeat>') ;
127declare @repeat NVARCHAR(4000) = SUBSTRING(@html,@start,@end-@start);
128
129SET @repeat = REPLACE(@repeat,'<ewi-repeat>','');
130SET @repeat = REPLACE(@repeat,'</ewi-repeat>','');
131SET @html = STUFF(@html,@start,@end-@start,'<ewi-repeat>');
132
133
134DECLARE @NumberRecords INT = (SELECT COUNT(1) FROM ##emailTempTable)
135DECLARE @RowCount INT = 1
136
137WHILE @RowCount <= @NumberRecords
138BEGIN
139
140 IF OBJECT_ID('tempdb..#emailTempColumns') IS NOT NULL
141 DROP TABLE #emailTempColumns
142
143 select *
144 INTO #emailTempColumns
145 from tempdb.sys.columns
146 where object_id = object_id('tempdb..##emailTempTable');
147
148 DECLARE @NumberRecords2 INT = (SELECT COUNT(1) FROM #emailTempColumns)
149 DECLARE @RowCount2 INT = 1
150 SET @tempReplace = @repeat;
151
152 WHILE @RowCount2 <= @NumberRecords2
153 BEGIN
154
155 SELECT TOP 1 @columnName=name
156 from #emailTempColumns
157 ORDER BY name;
158
159 SET @sql2 = 'SELECT @columnValue ='+@columnName+'
160 FROM (SELECT TOP 1 * FROM ##emailTempTable ORDER BY 1) s';
161
162 EXECUTE sp_executesql @sql2, N'@columnValue NVARCHAR(50) OUT', @columnValue OUT;
163 IF EXISTS (select * from wip_operation_attachment)
164 if (@columnName='sendTo')
165 BEGIN
166 if NOT EXISTS (select * from @recipients where email=@columnValue)
167 INSERT INTO @recipients (email) VALUES (@columnValue);
168 END
169 ELSE IF (@columnName='topic')
170 SET @topic = @columnValue;
171 ELSE
172 BEGIN
173 SET @html= REPLACE(@html,'@'+@columnName,ISNULL(@columnValue,''));
174 SET @tempReplace = REPLACE(@tempReplace,'@'+@columnName,ISNULL(@columnValue,''));
175 END;
176
177 WITH CTE AS
178 (
179 SELECT TOP 1 name
180 FROM #emailTempColumns
181 ORDER BY name
182 )
183 DELETE FROM CTE
184 SET @RowCount2 = @RowCount2+1;
185 END;
186
187 SET @html= STUFF(@html,CHARINDEX('<ewi-repeat>', @html),0,@tempReplace);
188
189 WITH CTE AS
190 (
191 SELECT TOP 1 *
192 FROM ##emailTempTable
193 ORDER BY 1
194 )
195 DELETE FROM CTE
196 SET @RowCount = @RowCount+1;
197
198
199
200END
201
202IF (@NumberRecords>0)
203BEGIN
204 DECLARE @RowCount3 INT = 1
205 DECLARE @maxEmail INT = (SELECT MAX(ID) FROM @recipients);
206 declare @email NVARCHAR(100);
207 SELECT * FROM @recipients;
208
209 WHILE @RowCount3 <= @maxEmail
210 BEGIN
211
212 SET @email = (SELECT email FROM @recipients where ID=@RowCount3);
213
214 exec msdb.dbo.sp_send_dbmail
215 @profile_name = 'EwiMail',
216 @recipients = @email,
217 @subject = @topic,
218 @body = @html,
219 @body_format = 'html';
220
221 SET @RowCount3 = @RowCount3+1;
222 END
223END;
224END;
225GO
226DROP TABLE [dbo].[job_sql_param];
227
228CREATE TABLE [dbo].[job_sql_param](
229 [job_sql_param_id] [int] IDENTITY(1,1) NOT NULL,
230 [key] [nvarchar](80) NULL,
231 [value] [nvarchar](MAX) NULL,
232 [job_id] [int] NOT NULL,
233PRIMARY KEY CLUSTERED
234(
235 [job_sql_param_id] ASC
236)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
237) ON [PRIMARY]
238
239GO
240
241ALTER TABLE [dbo].[job_sql_param] WITH CHECK ADD CONSTRAINT [fk_job_sql_param_job1] FOREIGN KEY([job_id])
242REFERENCES [dbo].[job] ([job_id])
243GO
244
245ALTER TABLE [dbo].[job_sql_param] CHECK CONSTRAINT [fk_job_sql_param_job1]
246GO
247
248delete from job_sql where job_id in (SELECT job_id FROM job where name='send email for overdued new tasks')
249delete from job_sql where job_id in (SELECT job_id FROM job where name='raise priority for tasks')
250delete from job_sql_param where job_id in (SELECT job_id FROM job where name='send email for overdued new tasks')
251delete from job_sql_param where job_id in (SELECT job_id FROM job where name='raise priority for tasks')
252delete from job where name= 'send email for overdued new tasks'
253delete from job where name= 'raise priority for tasks'
254
255-- JOB PART
256-- send email for overdued new tasks
257
258insert into job(name,
259 start_date,
260 end_date,
261 last_started_date,
262 last_finished_date,
263 active,
264 sinterval,
265 next_start_date,
266 created_by)
267VALUES (
268'Send email for overdued new tasks',
269SYSDATETIME(),
270'2016-12-31 23:59:59',
271NULL,
272NULL,
2731,
2743600,
275null,
276'master data')
277
278DECLARE @job_id VARCHAR(50) = (select job_id from job where name='send email for overdued new tasks')
279
280INSERT INTO job_sql (raw_sql,job_id) VALUES
281('declare @query NVARCHAR(4000) = (SELECT VALUE FROM job_sql_param WHERE "key"=''query'' AND job_id ='+@job_id+')
282 declare @html NVARCHAR(MAX) = (SELECT VALUE FROM job_sql_param WHERE "key"=''html'' AND job_id ='+@job_id+');
283 exec rSendEmail @query,@html;'
284 ,@job_id
285)
286
287INSERT INTO job_sql_param VALUES ('query','SELECT wip_operation_id,
288 wo.code code,
289 ''bsuchorowski@andea.com'' sendTo,
290 ''Overdued new tasks '' + CONVERT (VARCHAR(10), SYSDATETIME(),120) topic,
291 (SELECT TOP 1 swoh.comment
292 FROM wip_operation_history swoh
293 WHERE swoh.wip_operation_id=wo.wip_operation_id
294 AND comment IS NOT NULL
295 ORDER BY wip_operations_history_id ASC) comment,
296 (SELECT TOP 1 e.name
297 FROM wip_operation_equipments swoe, equipment e
298 WHERE swoe.wip_operation_id=wo.wip_operation_id
299 AND e.equipment_id=swoe.equipment_id
300 AND e.name IS NOT NULL
301 ORDER BY wip_operation_equipment_id ASC) equipment,
302 wo.wip_priority,
303 (SELECT status_description from wip_statuses ws WHERE ws.status_id = wo.status) status,
304 wo.created_on,
305 wo.wip_priority priority
306FROM wip_operations wo
307WHERE status=1
308 AND DATEDIFF(ss,ISNULL(valid_from,created_on),SYSDATETIME()) > 3600',@job_id);
309
310
311INSERT INTO job_sql_param VALUES ('html',
312'<div>LIST OF OVERDUED TASKS</div>
313 <br>
314 <table style="border-collapse:collapse; border: solid 1px black; width:100%">
315 <thead style="background-color: #1e4d79; color: white">
316 <th>ID</th>
317 <th>EQUIPMENT</th>
318 <th>COMMENT</th>
319 <th>CREATED ON</th>
320 <th>PRIORITY</th>
321 <thead>
322 <tbody style="color: black">
323 <ewi-repeat>
324 <tr style="border-bottom: 1px solid #000; text-align: center">
325 <td style="border-bottom: 1px solid #000">@wip_operation_id</td>
326 <td style="border-bottom: 1px solid #000">@equipment</td>
327 <td style="border-bottom: 1px solid #000">@comment</td>
328 <td style="border-bottom: 1px solid #000">@created_on</td>
329 <td style="border-bottom: 1px solid #000">@priority</td>
330 </tr>
331 </ewi-repeat>
332</tbody>
333</table>',@job_id);
334
335-- JOB PART
336-- raise priority for tasks
337
338insert into job(name,
339 start_date,
340 end_date,
341 last_started_date,
342 last_finished_date,
343 active,
344 sinterval,
345 next_start_date,
346 created_by)
347VALUES (
348'Raise priority for tasks',
349SYSDATETIME(),
350'2016-12-31 23:59:59',
351NULL,
352NULL,
3531,
3543600,
355null,
356'master data')
357
358SET @job_id = (select job_id from job where name='raise priority for tasks')
359
360INSERT INTO job_sql (raw_sql,job_id) VALUES
361('declare @threshold NVARCHAR(50) = (SELECT VALUE FROM job_sql_param WHERE "key"=''threshold'' and job_id='+@job_id+');
362 exec rRaiseTaskPriority @threshold', @job_id
363)
364
365INSERT INTO job_sql_param VALUES ('threshold','3600',@job_id)
366
367
368select * from job