2,497
社区成员
发帖
与我相关
我的任务
分享
With ADOQuery1 do
begin
Close;
Sql.Clear;
Sql.Add('if exists (select * from syscolumns where name= ''Is_Checked'' and id=object_id(''SPerson''))');
Sql.Add('Alter Table SPerson Drop column Is_Checked');
Sql.Add('Go');
Sql.Add('Alter Table SPerson add Is_Checked bit null');
Sql.Add('Go ');
Sql.Add('Update SPerson Set Is_Checked=0');
//Memo1.Clear;
//Memo1.Text:=Sql.Text;
ExecSQL;
end;
qry_BaseTemp.Close;
qry_BaseTemp.SQL.Clear;
qry_BaseTemp.SQL.Add( ' exec SP_HR_DAY_AUDIT ');//這裡加傳入參數
qry_BaseTemp.ExecSQL;
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[SP_HR_DAY_AUDIT] Script Date: 08/26/2010 18:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
/*
作者:天涯浪子一居士(有志者事竟成破釜沉舟百二秦关终属楚;苦心人天不负卧薪尝胆三千越甲可吞吴!)
时间:2010-08-26
地点:广东東莞
公司:昆盈電腦製品有限公司
功能:生成出勤快報
*/
-- =============================================
ALTER PROCEDURE [dbo].[SP_HR_DAY_AUDIT]
@rq smalldatetime,
@sWhere nvarchar(10)
AS
BEGIN
declare @ts int
select @ts=Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@rq)+1, 0)))
declare @i1 int select @i1=1 declare @xx1 nvarchar(2)
delete from HR_DAY_AUDIT
while @i1<=@ts
begin
declare @kk1 int select @kk1=0 declare @kkk1 int select @kkk1=0
if @i1<=9 select @xx1='0'+CAST(@i1 as nvarchar(1))
else select @xx1=CAST(@i1 as nvarchar(2))
select @kk1=(select datepart(weekday, CONVERT(smalldatetime,CONVERT(nvarchar(8),@rq,120) +@xx1) ) - 1 )
if exists( select * from HR_HOLIDAYS where COLRQ=CONVERT(nvarchar(8),@rq,120) +@xx1 )
select @kkk1=1
INSERT INTO HR_DAY_AUDIT(COLRQ,COLCDN) VALUES( CONVERT(nvarchar(8),@rq,120) +@xx1,'>'+@sWhere)
select @i1=@i1+1
end
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HA1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HA1] --臨時表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HA2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HA2] --臨時表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HR_AUDI_DAY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HR_AUDI_DAY] --日稽核表
SELECT A.COLAID,A.COLRQ,A.SL,B.BM INTO _HA1 FROM
(
SELECT [COLAID],[COLRQ],SUM(COLJBT) AS SL
FROM [MES].[dbo].[HR_EMPLOYEE_DAY]
WHERE COLRQ>=CONVERT(nvarchar(8),@rq,120)+'01' AND
COLRQ<=CONVERT(nvarchar(8),@rq,120)+CONVERT(NVARCHAR(2),@ts)
GROUP BY [COLAID],[COLRQ]
) A LEFT JOIN Res_Hrd_Temp B ON A.COLAID=B.Account_Id
WHERE SL>CAST(@sWhere as float)
SELECT COLRQ,BM,COUNT(*) AS SL INTO _HA2 FROM _HA1 GROUP BY COLRQ,BM
declare @sql1_1 varchar(8000)
set @sql1_1 = 'select BM,'
select @sql1_1 = @sql1_1 + 'sum(case COLRQ when '''+COLRQ+'''then SL else 0 end) as '''+'THR'+RIGHT(COLRQ,2)+''','
from (select COLRQ from HR_DAY_AUDIT ) as a
select @sql1_1 = left(@sql1_1,len(@sql1_1)-1) + ' INTO HR_AUDI_DAY from _HA2 group by BM order by BM'
exec(@sql1_1)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HA1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HA1]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HA2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HA2]
--SELECT * FROM HR_AUDI_DAY
END
try
if not Assigned(RES_LOADING_F) then
RES_LOADING_F:=TRES_LOADING_F.Create(Self);
RES_LOADING_F.Label1.Caption:='正在讀取數據......';
RES_LOADING_F.Show;
Screen.Cursor:=crSQLWait;
Application.ProcessMessages;
qry_BaseTemp.Close;
qry_BaseTemp.SQL.Clear;
qry_BaseTemp.SQL.Add( ' exec SP_HR_DAY_AUDIT ');
qry_BaseTemp.ExecSQL;
finally
Screen.Cursor:=crDefault;
FreeAndNil(RES_LOADING_F);
end;
var
I:Integer;
begin
I:=0;
With ADOQuery1 do
begin
Close;
Sql.Clear;
Sql.Add('if exists (select * from syscolumns where name= ''Is_Checked'' and id=object_id(''ST_Person''))');
Try
Open;
I:=1;
Except
I:=2;
End;
if I=1 then
begin
Close;
Sql.Clear;
Sql.Add('Alter Table ST_Person Drop column Is_Checked');
ExecSQL;
end;
Close;
Sql.Clear;
Sql.Add('Alter Table ST_Person add Is_Checked bit null');
ExecSQL;
Close;
Sql.Clear;
Sql.Add('Update ST_Person Set Is_Checked=0');
ExecSQL;
//Sql.Add('Go');
//Sql.Add();
//Sql.Add('Go ');
//Sql.Add('');
//Memo1.Clear;
//Memo1.Text:=Sql.Text;
end;
end;
With ADOQuery1 do
begin
Close;
Sql.Clear;
Sql.Add('if exists (select * from syscolumns where name= '+#13+Is_Checked+#13+' and id=object_id('+#13+SPerson+#13+'))');
Sql.Add('Alter Table SPerson Drop column Is_Checked');
Sql.Add('Go');
Sql.Add('Alter Table SPerson add Is_Checked bit null');
Sql.Add('Go ');
Sql.Add('Update SPerson Set Is_Checked=0');
//Memo1.Clear;
//Memo1.Text:=Sql.Text;
ExecSQL;
end;