代码作用:--检查COST SHEET 1ST COST 与 ITEM MASTER 1ST COST不一致
SELECT U.ITEM_CODE,(C.TOTAL_COST/7.7*1.20) AS CostSheet_FirstCost,(U.STD_COST/7.7*1.20) AS Item_firstCost,
U.DES,C.INDATE,U.CRT_USER,U.LST_USER
FROM UN_ITEM1 U, CS_HEAD C
WHERE SUBSTRING(ITEM_CODE,9,1)='1'
AND ITEM_CODE LIKE '%-0%'
AND U.ITEM_CODE = C.PRODUCT_CODE
AND U.STD_COST <> C.TOTAL_COST
-代码作用: 检查ITEM MASTER中的LAST PRICE与COST SHEET 中的UNIT PRICE不一致的ITEM LISTSELECT C.PRODUCT_CODE, C.ITEM_CODE, C.UPRICE, I.LAST_PRICE ,I.LST_USER,I.CRT_DATE
FROM CS_METAL C JOIN UN_ITEM1 I ON C.ITEM_CODE=I.ITEM_CODE
WHERE C.UPRICE<>I.LAST_PRICE
AND SUBSTRING(C.PRODUCT_CODE,9,1)='1'
AND ((C.UPRICE+0.0005)<I.LAST_PRICE or (C.UPRICE>I.LAST_PRICE+0.0005))
ORDER BY C.PRODUCT_CODE
有没有办法实现:这样触发器,能自动检测这两段代码 if count(*)>= 1 就形成两张EXCEL输出。。。。那就完美了。。。。
这两段代码 共涉及到4 张表, 但有一个公共的表UN_ITEM1 I,(我都不知道要怎么写这个触发器,难道将两段代码都产生一个新视图,能在视图上写触发器吗?要写UPDATE INSERT DELETE三种吗?) 谢谢!
19 个解决方案
#1
在视图上不能写触发器,必须要针对实际的某个表
#2
或者设置一个定时的作业去操作
#3
定时可以到天,或小时或分钟,根据你的需要
#4
针对表写触发器。。。。
#5
我之前就做過和你類似的需求
1.寫的一個程序,功能是執行程序后從SQL裏面取出符合條件的區間段數據,寫入Excel,存放在SQL服務器所在機器上,然後利用xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
2.在SQL所在服務器上做一個計劃定時執行該程序。
1.寫的一個程序,功能是執行程序后從SQL裏面取出符合條件的區間段數據,寫入Excel,存放在SQL服務器所在機器上,然後利用xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
2.在SQL所在服務器上做一個計劃定時執行該程序。
#6
能说一下怎么写的代码吗???
做一个定时计划,要怎么写才能利用到xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
還需要配置SQLServer??怎么配置》你们有成功过的吗?我没有一次成功过
做一个定时计划,要怎么写才能利用到xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
還需要配置SQLServer??怎么配置》你们有成功过的吗?我没有一次成功过
#7
能说一下怎么写的代码吗???
做一个定时计划,要怎么写才能利用到xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
還需要配置SQLServer??怎么配置》你们有成功过的吗?我没有一次成功过
做一个定时计划,要怎么写才能利用到xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
還需要配置SQLServer??怎么配置》你们有成功过的吗?我没有一次成功过
#8
呵呵。触发器是针对表的。
#9
确是一好方法,呵呵
#10
没有结果????
#11
期待啊.
#12
我也期待啊.可是没有高手愿意出手
#13
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [SendP052]
ON [dbo].[P052] FOR INSERT, UPDATE
AS
BEGIN TRAN
DECLARE @temp TABLE( [P05201] varchar(10), [S21002] varchar(40))
INSERT INTO @temp ( P05201, S21002 )
SELECT P05201,S21002='p05' FROM INSERTED
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
COMMIT TRAN
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [SendP052]
ON [dbo].[P052] FOR INSERT, UPDATE
AS
BEGIN TRAN
DECLARE @temp TABLE( [P05201] varchar(10), [S21002] varchar(40))
INSERT INTO @temp ( P05201, S21002 )
SELECT P05201,S21002='p05' FROM INSERTED
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
COMMIT TRAN
#14
能否解释一下就好了,这段代码是不是在P05201,S21002两个表中进行的插入和更新的触发,
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
这些句子都不懂,基础太差,能否有人帮忙解释一下.....
#15
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [SendP052]
ON [dbo].[P052] FOR INSERT, UPDATE
AS
BEGIN TRAN
DECLARE @temp TABLE( [P05201] varchar(10), [S21002] varchar(40))
INSERT INTO @temp ( P05201, S21002 )
SELECT P05201,S21002='p05' FROM INSERTED
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
COMMIT TRAN
13楼的,你那触发器代码能否公开下
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [SendP052]
ON [dbo].[P052] FOR INSERT, UPDATE
AS
BEGIN TRAN
DECLARE @temp TABLE( [P05201] varchar(10), [S21002] varchar(40))
INSERT INTO @temp ( P05201, S21002 )
SELECT P05201,S21002='p05' FROM INSERTED
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
COMMIT TRAN
13楼的,你那触发器代码能否公开下
#16
还是很想知道 13楼的代码。来论坛毕竟是为了交流对吗论坛还是有比你更高的高手存在的。。?
分享了知识,也提高了自己。
分享了知识,也提高了自己。
#17
====================================================================================
procedure TfmMain.FormCreate(Sender: TObject);
const
xlWorksheet=-4167;
var
i:Integer;
sFilePath:String;
xlsApp:Variant;
begin
try
try
//sFilePath := '\\Datacenter\Public\PaUser\'+FormatDateTime('yyyy',Date)+'年第'+FloatToStr(WeekOfTheYear(Date))+'周電話記錄.xls';
xlsApp:=CreateOLEObject('Excel.Application');
xlsApp.Workbooks.Add(xlWorksheet);
xlsApp.ActiveWorkbook.ActiveSheet.Name:=FormatDateTime('yyyy',Date)+'年第'+FloatToStr(WeekOfTheYear(Date))+'周電話記錄';
//標題行
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,1].Value:='分機';
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,2].Value:='通話日期';
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,3].Value:='時長';
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,4].Value:='電話號碼';
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,5].Value:='話務類別';
//標題行對齊方式
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,1].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,2].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,3].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,4].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,5].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.Rows[1].RowHeight:=0.8/0.035; //第一行行高
xlsApp.ActiveWorkbook.ActiveSheet.Range['A1:E1'].Borders[4].Weight:=2;//第一行下劃線
xlsApp.ActiveWorkbook.ActiveSheet.Rows[1].Font.Color:=clRed; //第一行字體顏色
xlsApp.ActiveWorkbook.ActiveSheet.Rows[1].Font.Bold :=True; //
with ADS_GetTELRec do
begin
Active:=False;
CommandText:=' Select ExtentionNo,[DateTime],Long,a.TelNo,'+
' TelKind=Case When IsNull(b.TelKind,'''')<>'''' Then TelKind Else ''私人電話'' end'+
' From Hr..Call_list a Left Outer Join Call_TelNumber b On a.TelNo=b.TelNo '+
' where Year([DateTime])=Year(GetDate()) and DATEPART(Week,[DateTime])=DATEPART(Week,GetDate()) '+
' and ExtentionNo not in (''20'',''15'',''16'',''17'',''18'',''21'',''22'',''28'') '+
' and Convert(Int,SubString(Long,1,CHARINDEX(''-'',Long)-1))>=3 '+
' Order By ExtentionNo ';
Active:=True;
First;
i:=1;
while not eof do
begin
i:=i+1;
xlsApp.ActiveWorkbook.ActiveSheet.rows[i].select;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].Value:=FieldByName('ExtentionNo').AsString;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].NumberFormatLocal:='G/通用格式';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].Value;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].Value:=FieldByName('DateTime').AsDateTime;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].NumberFormatLocal:='dd.mm.yyyy hh:mm:ss';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].Value;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].Value:=''''+FieldByName('Long').AsString;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].NumberFormatLocal:='@';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].Value;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].Value:=''''+FieldByName('TelNo').AsString;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].NumberFormatLocal:='@';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].Value;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].Value:=FieldByName('TelKind').AsString;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].NumberFormatLocal:='@';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].HorizontalAlignment:=4;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].Value;
Next;
end;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[1].ColumnWidth := 5;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[2].ColumnWidth := 18;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[3].ColumnWidth := 8;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[4].ColumnWidth := 15;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[5].ColumnWidth := 15;
sFilePath := '\\Datacenter\Public\PaUser\'+FormatDateTime('yyyy',Date)+'年第'+FloatToStr(WeekOfTheYear(Date))+'周電話記錄.xls';
xlsApp.Visible := False;
xlsApp.DisplayAlerts:=False;
xlsApp.ActiveWorkbook.SaveAs(sFilePath);
xlsApp.ActiveWorkBook.Saved := True;
xlsApp.WorkBooks.Close;
end;
//以下處理發送郵件
with SP_SendMail do
begin
close;
Parameters.ParamByName('@Year').value:= FormatDateTime('yyyy',Date);
Parameters.ParamByName('@Wek').value := FloatToStr(WeekOfTheYear(Date));
ExecProc;
end;
if FileExists(sFilePath) then DeleteFile(sFilePath);
except
ShowMessage(Exception(ExceptObject).Message);
end;
finally
xlsApp.Quit;
Application.Terminate;
end;
end;
#18
以下是SP_SendMail加載的存儲過程
--Created by ljj 2011.03.08 發送每週電話記錄給歐經理
CREATE PROCEDURE dbo.P_GetTELRecForWeek(
@Year VarChar(4),
@Wek VarChar(2))
AS
Begin
Set Nocount On
Declare @sSubject VarChar(100)
Declare @sFileAddr VarChar(100)
Set @sSubject = @Year + '年第' + Cast(Cast(@Wek As Int)As VarChar(2))+'周電話記錄'
Set @sFileAddr = '\\Datacenter\public\Pauser\'+@Year+'年第'+Rtrim(Ltrim(Cast(Cast(@Wek As Int) As VarChar(2))))+'周電話記錄.xls'
--EXEC master.dbo.xp_sendmail @recipients='ljj', @subject = @sSubject, @attachments = @sFileAddr
print @sFileAddr
End
#19
不好意思,上面的是測試用,屏蔽了發送過程
CREATE PROCEDURE dbo.P_GetTELRecForWeek(
@Year VarChar(4),
@Wek VarChar(2))
AS
Begin
Set Nocount On
Declare @sSubject VarChar(100)
Declare @sFileAddr VarChar(100)
Set @sSubject = @Year + '年第' + Cast(Cast(@Wek As Int)As VarChar(2))+'周電話記錄'
Set @sFileAddr = '\\Datacenter\public\Pauser\'+@Year+'年第'+Rtrim(Ltrim(Cast(Cast(@Wek As Int) As VarChar(2))))+'周電話記錄.xls'
EXEC ERP2.master.dbo.xp_sendmail @recipients='pam', @subject = @sSubject, @attachments = @sFileAddr
End
#20
#1
在视图上不能写触发器,必须要针对实际的某个表
#2
或者设置一个定时的作业去操作
#3
定时可以到天,或小时或分钟,根据你的需要
#4
针对表写触发器。。。。
#5
我之前就做過和你類似的需求
1.寫的一個程序,功能是執行程序后從SQL裏面取出符合條件的區間段數據,寫入Excel,存放在SQL服務器所在機器上,然後利用xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
2.在SQL所在服務器上做一個計劃定時執行該程序。
1.寫的一個程序,功能是執行程序后從SQL裏面取出符合條件的區間段數據,寫入Excel,存放在SQL服務器所在機器上,然後利用xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
2.在SQL所在服務器上做一個計劃定時執行該程序。
#6
能说一下怎么写的代码吗???
做一个定时计划,要怎么写才能利用到xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
還需要配置SQLServer??怎么配置》你们有成功过的吗?我没有一次成功过
做一个定时计划,要怎么写才能利用到xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
還需要配置SQLServer??怎么配置》你们有成功过的吗?我没有一次成功过
#7
能说一下怎么写的代码吗???
做一个定时计划,要怎么写才能利用到xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
還需要配置SQLServer??怎么配置》你们有成功过的吗?我没有一次成功过
做一个定时计划,要怎么写才能利用到xp_sendmail存儲過程發送給所需要發送的人(當然要使用xp_sendmail發送郵件,還需要配置SQLServer),然後程序自動關閉。
還需要配置SQLServer??怎么配置》你们有成功过的吗?我没有一次成功过
#8
呵呵。触发器是针对表的。
#9
确是一好方法,呵呵
#10
没有结果????
#11
期待啊.
#12
我也期待啊.可是没有高手愿意出手
#13
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [SendP052]
ON [dbo].[P052] FOR INSERT, UPDATE
AS
BEGIN TRAN
DECLARE @temp TABLE( [P05201] varchar(10), [S21002] varchar(40))
INSERT INTO @temp ( P05201, S21002 )
SELECT P05201,S21002='p05' FROM INSERTED
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
COMMIT TRAN
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [SendP052]
ON [dbo].[P052] FOR INSERT, UPDATE
AS
BEGIN TRAN
DECLARE @temp TABLE( [P05201] varchar(10), [S21002] varchar(40))
INSERT INTO @temp ( P05201, S21002 )
SELECT P05201,S21002='p05' FROM INSERTED
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
COMMIT TRAN
#14
能否解释一下就好了,这段代码是不是在P05201,S21002两个表中进行的插入和更新的触发,
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
这些句子都不懂,基础太差,能否有人帮忙解释一下.....
#15
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [SendP052]
ON [dbo].[P052] FOR INSERT, UPDATE
AS
BEGIN TRAN
DECLARE @temp TABLE( [P05201] varchar(10), [S21002] varchar(40))
INSERT INTO @temp ( P05201, S21002 )
SELECT P05201,S21002='p05' FROM INSERTED
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
COMMIT TRAN
13楼的,你那触发器代码能否公开下
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [SendP052]
ON [dbo].[P052] FOR INSERT, UPDATE
AS
BEGIN TRAN
DECLARE @temp TABLE( [P05201] varchar(10), [S21002] varchar(40))
INSERT INTO @temp ( P05201, S21002 )
SELECT P05201,S21002='p05' FROM INSERTED
insert into vwc910(s21002,c91003)
select S21002,P05201 FROM @temp
COMMIT TRAN
13楼的,你那触发器代码能否公开下
#16
还是很想知道 13楼的代码。来论坛毕竟是为了交流对吗论坛还是有比你更高的高手存在的。。?
分享了知识,也提高了自己。
分享了知识,也提高了自己。
#17
====================================================================================
procedure TfmMain.FormCreate(Sender: TObject);
const
xlWorksheet=-4167;
var
i:Integer;
sFilePath:String;
xlsApp:Variant;
begin
try
try
//sFilePath := '\\Datacenter\Public\PaUser\'+FormatDateTime('yyyy',Date)+'年第'+FloatToStr(WeekOfTheYear(Date))+'周電話記錄.xls';
xlsApp:=CreateOLEObject('Excel.Application');
xlsApp.Workbooks.Add(xlWorksheet);
xlsApp.ActiveWorkbook.ActiveSheet.Name:=FormatDateTime('yyyy',Date)+'年第'+FloatToStr(WeekOfTheYear(Date))+'周電話記錄';
//標題行
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,1].Value:='分機';
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,2].Value:='通話日期';
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,3].Value:='時長';
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,4].Value:='電話號碼';
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,5].Value:='話務類別';
//標題行對齊方式
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,1].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,2].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,3].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,4].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[1,5].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.Rows[1].RowHeight:=0.8/0.035; //第一行行高
xlsApp.ActiveWorkbook.ActiveSheet.Range['A1:E1'].Borders[4].Weight:=2;//第一行下劃線
xlsApp.ActiveWorkbook.ActiveSheet.Rows[1].Font.Color:=clRed; //第一行字體顏色
xlsApp.ActiveWorkbook.ActiveSheet.Rows[1].Font.Bold :=True; //
with ADS_GetTELRec do
begin
Active:=False;
CommandText:=' Select ExtentionNo,[DateTime],Long,a.TelNo,'+
' TelKind=Case When IsNull(b.TelKind,'''')<>'''' Then TelKind Else ''私人電話'' end'+
' From Hr..Call_list a Left Outer Join Call_TelNumber b On a.TelNo=b.TelNo '+
' where Year([DateTime])=Year(GetDate()) and DATEPART(Week,[DateTime])=DATEPART(Week,GetDate()) '+
' and ExtentionNo not in (''20'',''15'',''16'',''17'',''18'',''21'',''22'',''28'') '+
' and Convert(Int,SubString(Long,1,CHARINDEX(''-'',Long)-1))>=3 '+
' Order By ExtentionNo ';
Active:=True;
First;
i:=1;
while not eof do
begin
i:=i+1;
xlsApp.ActiveWorkbook.ActiveSheet.rows[i].select;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].Value:=FieldByName('ExtentionNo').AsString;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].NumberFormatLocal:='G/通用格式';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].Value;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].Value:=FieldByName('DateTime').AsDateTime;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].NumberFormatLocal:='dd.mm.yyyy hh:mm:ss';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].Value;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].Value:=''''+FieldByName('Long').AsString;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].NumberFormatLocal:='@';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].HorizontalAlignment:=3;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].Value;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].Value:=''''+FieldByName('TelNo').AsString;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].NumberFormatLocal:='@';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].Value;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].Value:=FieldByName('TelKind').AsString;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].NumberFormatLocal:='@';
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].HorizontalAlignment:=4;
xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].Value;
Next;
end;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[1].ColumnWidth := 5;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[2].ColumnWidth := 18;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[3].ColumnWidth := 8;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[4].ColumnWidth := 15;
xlsApp.ActiveWorkbook.ActiveSheet.Columns[5].ColumnWidth := 15;
sFilePath := '\\Datacenter\Public\PaUser\'+FormatDateTime('yyyy',Date)+'年第'+FloatToStr(WeekOfTheYear(Date))+'周電話記錄.xls';
xlsApp.Visible := False;
xlsApp.DisplayAlerts:=False;
xlsApp.ActiveWorkbook.SaveAs(sFilePath);
xlsApp.ActiveWorkBook.Saved := True;
xlsApp.WorkBooks.Close;
end;
//以下處理發送郵件
with SP_SendMail do
begin
close;
Parameters.ParamByName('@Year').value:= FormatDateTime('yyyy',Date);
Parameters.ParamByName('@Wek').value := FloatToStr(WeekOfTheYear(Date));
ExecProc;
end;
if FileExists(sFilePath) then DeleteFile(sFilePath);
except
ShowMessage(Exception(ExceptObject).Message);
end;
finally
xlsApp.Quit;
Application.Terminate;
end;
end;
#18
以下是SP_SendMail加載的存儲過程
--Created by ljj 2011.03.08 發送每週電話記錄給歐經理
CREATE PROCEDURE dbo.P_GetTELRecForWeek(
@Year VarChar(4),
@Wek VarChar(2))
AS
Begin
Set Nocount On
Declare @sSubject VarChar(100)
Declare @sFileAddr VarChar(100)
Set @sSubject = @Year + '年第' + Cast(Cast(@Wek As Int)As VarChar(2))+'周電話記錄'
Set @sFileAddr = '\\Datacenter\public\Pauser\'+@Year+'年第'+Rtrim(Ltrim(Cast(Cast(@Wek As Int) As VarChar(2))))+'周電話記錄.xls'
--EXEC master.dbo.xp_sendmail @recipients='ljj', @subject = @sSubject, @attachments = @sFileAddr
print @sFileAddr
End
#19
不好意思,上面的是測試用,屏蔽了發送過程
CREATE PROCEDURE dbo.P_GetTELRecForWeek(
@Year VarChar(4),
@Wek VarChar(2))
AS
Begin
Set Nocount On
Declare @sSubject VarChar(100)
Declare @sFileAddr VarChar(100)
Set @sSubject = @Year + '年第' + Cast(Cast(@Wek As Int)As VarChar(2))+'周電話記錄'
Set @sFileAddr = '\\Datacenter\public\Pauser\'+@Year+'年第'+Rtrim(Ltrim(Cast(Cast(@Wek As Int) As VarChar(2))))+'周電話記錄.xls'
EXEC ERP2.master.dbo.xp_sendmail @recipients='pam', @subject = @sSubject, @attachments = @sFileAddr
End