USE [ChiefmesNEW]
GO
/****** Object: StoredProcedure [dbo].[st_Alarm_SendEmail] Script Date: 12/11/2015 09:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************
Create By: zhuss
Create Date: 2013-03-13
Module: Alarm
Remark: 发送电邮
********************/
ALTER procEDURE [dbo].[st_Alarm_SendEmail]
as
begin
--取半小时内产生的报警短信
if OBJECT_ID('temp..#t_EmailDetail') is not null drop table #t_EmailDetail
select s.ID,s.SMSContent,s.Email,m.AlarmName,s.AlarmBillNO,s.eFlag,s.AlarmLevel,s.EmpID,s.MachineNO,s.AlarmCode,s.CreateTime
into #t_EmailDetail from MES_AlarmSMSDetail s
join MES_AlarmTypeSet m on s.AlarmCode=m.AlarmCode
where s.eFlag=0 and Convert(varchar(19),s.CreateTime,120)>CONVERT(varchar(19),dateadd(N,-30,getdate()),120)
and s.Email<>''
--按照工号、报警项、报警层级、机台分组
declare @tableHTML nvarchar(max)
declare @i int
set @i=1
declare @count int,@EmpName varchar(10)
declare @SMSContent varchar(200),@CreateTime varchar(19)
declare @EmpID varchar(50),@AlarmCode varchar(20),@AlarmLevel varchar(10),@MachineNo varchar(50)
declare @vEmpID varchar(50),@str_subject varchar(100),@Email varchar(50)
if @@rowCount = 0 return
create table #t_EmailCollect
(
seq int not null identity(1,1),
EmpID varchar(50) not null,
AlarmCode varchar(20) not null,
AlarmLevel varchar(50) not null,
MachineNo varchar(50) not null,
SMSContent varchar(200) null,
AlarmName varchar(50) null,
CreateTime varchar(19) null,
UnitCount int
)
insert into #t_EmailCollect(EmpID,AlarmCode,AlarmLevel,UnitCount,MachineNo)
select EmpID,AlarmCode,AlarmLevel,UnitCount=COUNT(SMSContent),MachineNo from #t_EmailDetail
group by EmpID,AlarmCode,AlarmLevel,MachineNo
update c set c.AlarmName=t.AlarmName from #t_EmailCollect c
join MES_AlarmTypeSet t on t.AlarmCode=c.AlarmCode
select @count=COUNT(*) from #t_EmailCollect
while(@i<=@count)
begin
select @EmpID=EmpID,@AlarmCode=AlarmCode,@AlarmLevel=AlarmLevel,@MachineNo=MachineNo
from #t_EmailCollect where seq=@i
select top 1 @SMSContent=SMSContent,@CreateTime=Convert(varchar(19),CreateTime,121)
from #t_EmailDetail
where EmpID=@EmpID and AlarmCode=@AlarmCode and AlarmLevel=@AlarmLevel and MachineNo=@MachineNo
update #t_EmailCollect set SMSContent=@SMSContent,CreateTime=@CreateTime
where seq=@i
set @i=@i+1
end
--declare @vAlarmCode varchar(50),@vAlarmLevel varchar(10),@vMachineNo varchar(20)
if OBJECT_ID('temp..#t_EmpList') is not null drop table #t_EmpList
select distinct seq=identity(int),EmpID into #t_EmpList from #t_EmailCollect
declare @EmpCount int,@m int set @m=1
select @EmpCount=COUNT(*)from #t_EmpList
while(@m<=@EmpCount)
begin
select @vEmpID=EmpID from #t_EmpList where seq=@m
select @EmpName=EmpNameCN from MES_Employee where EmpID=@vEmpID
select @Email=Email from MES_AlarmEmpConfig where EmpID=@vEmpID
set @str_subject='报警信息'
SET @tableHTML = N'<br>尊敬的'+@EmpName+':'+
N'<br> 您好!'+
N'<br> 以下表格中的数据是在10分钟内您所管理机台的报警信息,请查看,谢谢!<br><br><table border="1">' +
N'<tr><th>报警项</th><th>报警层级</th><th>机台</th><th>报警信息</th><th>产生时间</th><th>产生次数</th></tr>' +
CAST ((select AlarmName as 'td','',AlarmLevel as 'td','',MachineNo as 'td','',SMSContent as 'td','',CreateTime as 'td','',UnitCount as 'td'
from #t_EmailCollect where EmpID=@vEmpID order by CreateTime
FOR XML PATH('tr'), ELEMENTS-- TYPE
) as nvarchar(max))+N'</table>'
--exec msdb..sp_send_dbmail @profile_name='ETLErrorMailLog',@recipients=@Email,@subject=@str_subject,@body=@tableHTML,@body_format='html'
--exec msdb..sp_send_dbmail @profile_name='JobSendEmai1',@recipients=@Email,@subject=@str_subject,@body=@tableHTML,@body_format='html'
exec msdb..sp_send_dbmail 'JobSendEmai1',@Email,@str_subject,@tableHTML,'html'
update e set e.eFlag=1 from #t_EmailDetail e where e.EmpID=@vEmpID
update s set s.eFlag=m.eFlag from MES_AlarmSMSDetail s
--join #t_EmailDetail m on m.AlarmBillNO=s.AlarmBillNO
join #t_EmailDetail m on m.ID=s.ID--update by zhuss 2014-10-22
set @m=@m+1
end
drop table #t_EmailDetail
drop table #t_EmailCollect
drop table #t_EmpList
end
-------------------------------------------------------------------
--select Email,* from MES_AlarmEmpConfig where EmpID='11007585'
--select EmpNameCN,* from MES_Employee
--exec msdb..sp_send_dbmail 'JobSendEmai1','307130041@qq.com','test','test','html'