VAR F,G : STRING
n123.Close;
n123.Params.ParamByName('order').AsString := edit1.Text;
n123.Open;
N123.First;
G := '訂單編號'+ #13#10 ;
WHILE NOT N123.Eof DO
BEGIN
F := F +N123ORDER+#13#10;
N123.Next;
END; If idsmtp1.Connected then
idsmtp1.Disconnect
else
begin
idsmtp1.Host := '123.YAHOO.COM';
idsmtp1.Port:=25;
idsmtp1.Connect;
end;
idmessage1.From.Address:='[email protected]';
idmessage1.Recipients.EMailAddresses:='[email protected]';
idmessage1.From.Name:='廠商';
idmessage1.Subject:='預約單號寄送';
idmessage1.Body.Text:=G+F;
idsmtp1.Send(idmessage1); MessageDlg('寄送完成。',mtInformation,[mbok],0);
程式碼如上,第一次點選時
可以順利收到MAIL,可以當我再點選第二次的時候
會出現"NOT CONNECTED"是為什麼呢???
出現這個錯誤訊息MAIL不會收到東西...
n123.Close;
n123.Params.ParamByName('order').AsString := edit1.Text;
n123.Open;
N123.First;
G := '訂單編號'+ #13#10 ;
WHILE NOT N123.Eof DO
BEGIN
F := F +N123ORDER+#13#10;
N123.Next;
END; If idsmtp1.Connected then
idsmtp1.Disconnect
else
begin
idsmtp1.Host := '123.YAHOO.COM';
idsmtp1.Port:=25;
idsmtp1.Connect;
end;
idmessage1.From.Address:='[email protected]';
idmessage1.Recipients.EMailAddresses:='[email protected]';
idmessage1.From.Name:='廠商';
idmessage1.Subject:='預約單號寄送';
idmessage1.Body.Text:=G+F;
idsmtp1.Send(idmessage1); MessageDlg('寄送完成。',mtInformation,[mbok],0);
程式碼如上,第一次點選時
可以順利收到MAIL,可以當我再點選第二次的時候
會出現"NOT CONNECTED"是為什麼呢???
出現這個錯誤訊息MAIL不會收到東西...
If idsmtp1.Connected then
idsmtp1.Disconnect
else
begin
idsmtp1.Host := '123.YAHOO.COM';
idsmtp1.Port:=25;
idsmtp1.Connect;
end;
If idsmtp1.Connected then
idsmtp1.Disconnect
else
begin
idsmtp1.Host := '123.YAHOO.COM';
idsmtp1.Port:=25;
idsmtp1.Connect;
end;
idmessage1.From.Address:='[email protected]';
idmessage1.Recipients.EMailAddresses:='[email protected]';
idmessage1.From.Name:='廠商';
idmessage1.Subject:='預約單號寄送';
idmessage1.Body.Text:=G+F;
idsmtp1.Send(idmessage1); MessageDlg('寄送完成。',mtInformation,[mbok],0);
finally
begin
idsmtp1.disconnect;
end;
end;
USE [eHR]
GO
/****** Object: StoredProcedure [dbo].[SP_KYE_JCREPORT] Script Date: 12/15/2010 17:23:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
/*
功能:產生昆盈公司大飯堂每日消費報表
*/
-- =============================================
ALTER PROCEDURE [dbo].[SP_KYE_JCREPORT]
AS
BEGIN
declare @mcount varchar(4000),@zcount int,@wcount int,@ncount int,@xcount int
--就餐人數
--select @mcount=count(*) from ( select deptname,empno from xf_record where xfdate=convert(nvarchar(10),getdate()-1,111) group by deptname,empno ) j
--沖值人數
select @mcount=count(*) from ( select depname,empno from dlc_record where rdate=convert(nvarchar(10),getdate()-1,111) group by depname,empno) jselect @zcount= sum(CONVERT(int,rs)) from ( select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='早餐'
group by deptname ) b
select @wcount= sum(CONVERT(int,rs)) from ( select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='午餐'
group by deptname ) b
select @ncount= sum(CONVERT(int,rs)) from ( select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='晚餐'
group by deptname ) b
select @xcount= sum(CONVERT(int,rs)) from ( select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='夜宵'
group by deptname ) b
if @xcount is null
begin
set @xcount=0
end
print @mcount
print @zcount
print @ncount
print @xcountdeclare @isday int,@nowD nvarchar(20)
select @isday=datepart(dw,getdate())
select @nowD=(select (case @isday when 1 then '星期天' when 2 then '星期一' when 3 then '星期二' when 4 then '星期三' when 5 then '星期四' when 6 then '星期五' when 7 then '星期六' end))
--print @isday
--print @nowD
declare @msg varchar(max)
select @msg='昆盈eHr系統溫馨提示:</br>'+'今天是'+@nowD+','+convert(nvarchar(10),getdate()-1,111)+'共有'+convert(nvarchar(10),@mcount)+'位員工在公司(大)飯堂充值!!!,其各部門(早、午、晚、夜宵)就餐明細如下:<br><br>'
select @msg=@msg+ N'<table width="500px" border="1" cellpadding="2" cellspacing="0" bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;>'+
N'<tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;><td width="2%" bgcolor="red">部門課別</td>'+
CAST ((
--早
select deptname from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='早餐'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
+'<td>合計</td></tr><tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF; ><td width="2%" bgcolor="red">早上就餐人數</td>'+
CAST ((
select rs from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='早餐'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
set @msg=@msg+'<td>'+CONVERT(varchar(10),@zcount)+'</td></tr></table><br>'
--print @msg早
select @msg=@msg+ N'<table width="500px" border="1" cellpadding="2" cellspacing="0" bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;>'+
N'<tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;><td width="2%" bgcolor="green">部門課別</td>'+
CAST ((
--午
select deptname from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='午餐'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
+'<td>合計</td></tr><tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF; ><td width="2%" bgcolor="green">中午就餐人數</td>'+
CAST ((
select rs from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='午餐'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
set @msg=@msg+'<td>'+CONVERT(varchar(10),@wcount)+'</td></tr></table><br>'
--print @msg午
select @msg=@msg+ N'<table width="500px" border="1" cellpadding="2" cellspacing="0" bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;>'+
N'<tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;><td width="2%" bgcolor="yellow">部門課別</td>'+
CAST ((
--晚
select deptname from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='晚餐'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
+'<td>合計</td></tr><tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF; ><td width="2%" bgcolor="yellow">晚上就餐人數</td>'+
CAST ((
select rs from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='晚餐'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
set @msg=@msg+'<td>'+CONVERT(varchar(10),@ncount)+'</td></tr></table><br>'
--print @msg晚
if @xcount=0
begin
--如果沒有人吃夜宵為0
select @msg=@msg+ N'<table width="500px" border="1" cellpadding="2" cellspacing="0" bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;>'+
N'<tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;><td width="2%" bgcolor="Olive">部門課別</td>'+
CAST ((
--夜宵
select deptname from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='晚餐'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
+'<td>合計</td></tr><tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF; ><td width="2%" bgcolor="Olive">夜宵就餐人數</td>'+
CAST ((
select isnull(0,CONVERT(int,rs)) as rs from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='晚餐'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
set @msg=@msg+'<td>'+CONVERT(varchar(10),@xcount)+'</td></tr></table>'
--print @msg夜宵
--如果沒有人吃夜宵為0
end
else
begin
--如果有人吃夜宵計算
select @msg=@msg+ N'<table width="500px" border="1" cellpadding="2" cellspacing="0" bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;>'+
N'<tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF;><td width="2%" bgcolor="Olive">部門課別</td>'+
CAST ((
--夜宵
select deptname from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='夜宵'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
+'<td>合計</td></tr><tr bordercolorlight=#99AAFF; bordercolorlight=#99AAFF; ><td width="2%" bgcolor="Olive">夜宵就餐人數</td>'+
CAST ((
select rs from (
select deptname,convert(varchar(10),count(empno)) as rs from xf_record
where xfdate=convert(nvarchar(10),getdate()-1,111) and [dbo].[Get_Times_Days](xftime)='夜宵'
group by deptname ) f order by rs desc
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX))
set @msg=@msg+'<td>'+CONVERT(varchar(10),@xcount)+'</td></tr></table>'
--print @msg夜宵
--如果有人吃夜宵計算
end
if @mcount>0
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DataBaseMail',
@recipients='[email protected];[email protected];[email protected];[email protected]',
--@recipients='[email protected]',
@subject='KYE大飯堂就餐日報',
@body=@msg,
@body_format='HTML';
end
else
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DataBaseMail',
@recipients='[email protected]',
@subject='KYE大飯堂就餐日報',
@body='居飛你好:</br>昆盈eHR系統溫馨提示:昨天公司大飯堂沒有就餐人員記錄!',
@body_format='HTML';
end
END--調用exec SP_KYE_JCREPORT