这个需求是公司有个文控中心,如果有用增删改了文件信息希望可以发邮件通知到有权限的人。当然方式很多。
这里是用数据库作业来完成
JOB+Com,这里用的com组件是Jmail
当然你需要把com组件放到system32下面
下面是实现方式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
CREATE PROC dbo.dcc_Send_Email
AS BEGIN SET
NOCOUNT ON
DECLARE
@lasttime DATETIME --获取最后一次发送的时间
IF Not
EXISTS( SELECT
lastsendtime FROM
dcc_lastsend)
begin
set
@lasttime=GETDATE()
END
ELSE
BEGIN
SELECT
@lasttime=lastsendtime FROM
dcc_lastsend
END
--SELECT @lasttime
--SELECT * FROM dcc_changereaon WHERE createdate>=@lasttime
DECLARE
@sendto VARCHAR (500) --发送邮件给谁
DECLARE
@aliasname VARCHAR (500) --档案名称
DECLARE
@filename VARCHAR (500) --名称
DECLARE
@userid INT
--用户ID
DECLARE
@dccid INT
--文件夹ID
DECLARE
@dccfileid INT
--文件ID
DECLARE
@ action VARCHAR (500) --操作类型 1 新增 2 修改 3删除
DECLARE
@changeid int
DECLARE
@reasons VARCHAR (8000) --原因
DECLARE
@html VARCHAR (8000) ---html格式内容
DECLARE
@ table VARCHAR (8000) ---html格式内容
DECLARE
@senduiserid VARCHAR (8000) --通知的用户
DECLARE
@sendfileid VARCHAR (8000) --更改的文件用户
DECLARE
@ temp TABLE (id INT , ACTION varchar (50), aliasname varchar (50),FILENAME varchar (50))
DECLARE
@rcount INT
SET
@html= ‘‘ --
SET
@ table = ‘‘
DECLARE
@ index INT --奇数还是偶数行
SET
@ index =0
DECLARE
CusCursor CURSOR
FOR --外层游标查询用户
SELECT
id,DccMail FROM
view_dcc_users WHERE
DccMail<> ‘‘
AND IsLock=0
OPEN
CusCursor
FETCH
NEXT FROM CusCursor INTO
@userid,@sendto
WHILE (@@FETCH_STATUS = 0)
BEGIN SET
@ table = ‘‘
SET
@ index =0
SET
@html = ‘‘ SET
@rcount=0
DELETE
@ temp
INSERT
INTO @ temp (id, action ,aliasname,filename)
SELECT
a.id,[ action ],a.aliasname,a.filename FROM
dcc_changereaon a
WHERE
a.createdate>=@lasttime
AND
dccid IN ( SELECT folderid FROM
dbo.dcc_rights WHERE
userid=@userid AND
enabled=1)
SET
@rcount = @@ROWCOUNT
IF @rcount>0
BEGIN
DECLARE
cur CURSOR
FOR SELECT * FROM @ temp --内层游标查询用户有那些权限
OPEN
cur
SET
@html= ‘<html><head><style type="text/css">table{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}td,th{font-size:1em;border:1px solid #98bf21;padding:3px 7px 2px 7px;}th {font-size:1.1em;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#ffffff;}tr.alt td {color:#000000;background-color:#EAF2D3;}</style></head><body>各位:<br>兹通知下述文件有变动,贵部可随时登录文控网页(www.gardenchinagroup.com:82) 查阅:<br><br><br><table><tr><th>档案名称</th><th>名称</th><th>备注(标注新增/更新/取消)</th></tr>‘
FETCH
NEXT FROM cur INTO @changeid,@ action ,@aliasname,@filename
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT
INTO dbo.dcc_logs
( changeid, userid, createtime )
VALUES
( @changeid, -- changeid - int
@userid, -- userid - int
GETDATE() -- createtime - smalldatetime
)
IF @ index
%2<>0
SET
@ table =@ table + ‘<tr class=‘ ‘alt‘ ‘><td>‘ +@filename+ ‘</td><td>‘ +@aliasname+ ‘</td><td>‘ +@ action + ‘</td></tr>‘
ELSE
SET
@ table =@ table + ‘<tr><td>‘ +@filename+ ‘</td><td>‘ +@aliasname+ ‘</td><td>‘ +@ action + ‘</td></tr>‘
SET
@ index =@ index +1
fetch
next from cur into @changeid,@ action ,@aliasname,@filename
END
CLOSE
cur
DEALLOCATE
cur
SET
@html=@html+@ table + ‘</table></body></html>‘
--PRINT @sendto
EXEC
myCommData.dbo.my_SendMail ‘文件新增/更新/取消通知‘ ,@html, ‘DCC‘ ,@sendto, ‘‘ , ‘fengmin.dg@gardenchinagroup.com,foxbuilder.dg@gardenchinagroup.com‘ , ‘joey.dg@gardenchinagroup.com‘ ,1
END
--SELECT @html
FETCH
NEXT FROM CusCursor INTO
@userid,@sendto
END
CLOSE
CusCursor
DEALLOCATE
CusCursor
IF Not
EXISTS( SELECT
lastsendtime FROM
dcc_lastsend) --更新最后一次发送时间
begin
INSERT
INTO dbo.dcc_lastsend( lastsendtime ) VALUES (GETDATE())
END
ELSE
BEGIN
UPDATE
dcc_lastsend SET
lastsendtime=GETDATE()
END
END GO |