首页 > 代码库 > 基于SQL调用Com组件来发送邮件

基于SQL调用Com组件来发送邮件

2024-07-07 23:18:53   227人阅读

这个需求是公司有个文控中心，如果有用增删改了文件信息希望可以发邮件通知到有权限的人。当然方式很多。

这里是用数据库作业来完成

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

　　


联系
我们