针对《FQYZjiaozhigong考勤,20070216数据库全新升级脚本》的补充(20070420)
-- 针对《FQYZjiaozhigong考勤,20070216数据库全新升级脚本》的补充(20070420)ALTER TABLE bc ADD qd_qt int not null default 1
--某一班次下，是只签到(1)还是只签退(2),还是即要签到又要签退(3)的设置字段.
ALTER TABLE bc ADD tq int not null default 10
--某一班次下，签到允许提前刷卡分钟数的设置字段
ALTER TABLE bc ADD cd int not null default 10
--某一班次下，签到允许迟到刷卡分钟数的设置字段
ALTER TABLE bc ADD zt int not null default 0
--某一班次下，签退允许早退刷卡分钟数的设置字段
ALTER TABLE bc ADD zc int not null default 10
--某一班次下，签退允许最迟刷卡分钟数的设置字段
ALTER TABLE ProcRecords ADD grp_id int
--排班结果表，增加班组号字段(grp_id)
ALTER TABLE ProcRecords ADD id int
--排班结果表，增加班次号字段(id)
ALTER TABLE ProcRecords ADD bc_name varchar(30)
--排班结果表，增加班次名称字段(bc_name)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE PB
(@st datetime,
@et datetime
) AS
--FQYZjiaozhigong考勤排班(利用外部文件导入生成)
declare @grp_id int,@id int,@name varchar(30),@Start char(8),@End char(8),@col sysname,@s varchar(8000),@custid int,@s_col nvarchar(255)--,@h_col varchar(8000)
declare /*@st datetime, /*排班起始日期*/@et datetime /*排班终止日期*/, */ @dept varchar(10) ,@planid int,@i int --作为planworkid值这用
declare @t datetime,@procdt datetime
--set @st='2007-1-1'
--set @et='2007-1-7'
CREATE TABLE #dt (
[dt] [datetime] NOT NULL
)
while (select datediff(day,@st,@et))>=0
begin
insert into #dt values(@st)
select @st=dateadd(day,1,@st)
end
select DATEADD(day, DATEDIFF(day,0,#dt.dt), 0) as d,datepart(weekday,#dt.dt) as weekid into #d_w from #dt
/*select @h_col=''
select @h_col=@h_col+name+',' from bc
select @h_col=substring(ltrim(rtrim(@h_col)),1,len(ltrim(rtrim(@h_col)))-1) */
declare bc cursor for select grp_id, id, name, Start, [End] from bc order by id
open bc
fetch next from bc into @grp_id,@id,@name,@Start,@End
while (@@fetch_status=0)
begin
/*
declare col cursor for SELECT syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.name = 'exam')
and syscolumns.name=@name
open col
fetch next from col into @col
while (@@fetch_status=0)
begin
*/
set @col=@name
select @s='declare exam cursor for SELECT custid,'+@col+ ' FROM Exam'+rtrim(cast(@grp_id as varchar))
exec(@s)
open exam
fetch next from exam into @custid,@s_col
while (@@fetch_status=0)
begin
/*处理开始：*/
if (@s_col is null or @s_col='')
begin
select null
end
else
begin
SELECT @dept=DeptCode FROM Customers WHERE (CustID =@custid )
select d,rtrim(cast(year(d) as char))+'-'+rtrim(cast(month(d) as char))+'-'+rtrim(cast(day(d) as char))+' '+@Start as p1,
rtrim(cast(year(d) as char))+'-'+rtrim(cast(month(d) as char))+'-'+rtrim(cast(day(d) as char))+' '+ @End as p2 into #d_t
from #d_w where weekid in (select * from f_h(@s_col,',') as a )
insert into ProcRecords(CustID, ProcDT, PlanWorkID, PlanStart, PlanEnd, DeptCode,grp_id,id,bc_name)
select @custid ,d,0 as planid,p1,p2,@dept,@grp_id,@id,@name from #d_t
drop table #d_t
end
/*处理结束*/
fetch next from exam into @custid,@s_col
end
close exam
deallocate exam
/*
fetch next from col into @col
end
close col
deallocate col */
fetch next from bc into @grp_id,@id,@name,@Start,@End
end
close bc
deallocate bc
/*planworkid值处理开始:*/
/*
declare custid cursor for select distinct custid,procdt from ProcRecords where ProcDT between (select min(d) from #d_w) and (select max(d) from #d_w)
open custid
fetch next from custid into @custid,@procdt
while (@@fetch_status=0)
begin
select @i=1
declare planid cursor for select planworkid,PlanStart from ProcRecords where custid=@custid and procdt=@procdt and ProcDT between (select min(d) from #d_w) and (select max(d) from #d_w) order by PlanStart
open planid
fetch next from planid into @planid,@t
while (@@fetch_status=0)
begin
update ProcRecords set planworkid=@i where custid=@custid and PlanStart=@t
select @i=@i+1
fetch next from planid into @planid,@t
end
close planid
deallocate planid
fetch next from custid into @custid,@procdt
end
close custid
deallocate custid
*/
/*planworkid值处理结束*/
drop table #d_w
drop table #dt
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [CL]
(
@st datetime, --起始日期
@et datetime --终止日期
)
AS
SELECT orgrec.*, ProcRecords.PlanWorkID, ProcRecords.PlanStart, ProcRecords.RealStart,
ProcRecords.StartStatus, ProcRecords.PlanEnd, ProcRecords.RealEnd,
ProcRecords.EndStatus, ProcRecords.grp_id, ProcRecords.id, bc.Start, bc.[End],
bc.qd_qt, bc.tq, bc.cd, bc.zt, bc.zc
into #rec
FROM (SELECT *, DATEADD([day], DATEDIFF([day], 0, OpDT), 0) AS [Day]
FROM (SELECT CustID, OpDT, CollDT, TermID
FROM AS_Record
UNION ALL
SELECT CustID, OpDT, CollDT, TermID
FROM orgRecords) rec) orgrec INNER JOIN
ProcRecords ON orgrec.CustID = ProcRecords.CustID AND
orgrec.[Day] = ProcRecords.ProcDT INNER JOIN
bc ON ProcRecords.grp_id = bc.grp_id AND ProcRecords.id = bc.id where (ProcRecords.ProcDT between @st and @et)
and bc.qd_qt=1 --只签到处理
--rec视图,OrgRecords与ProcRecords表及bc表的联合查询
--正常签到
update procrecords set realstart=min_dt,StartStatus=0,realend=min_dt,EndStatus=0
from (
SELECT CustID, [Day], grp_id, id,min(opdt) as min_dt
FROM #rec
where opdt between DATEADD(mi, tq, PlanStart) and DATEADD(mi, cd, PlanStart)
GROUP BY CustID, [Day], grp_id, id
) m where m.custid=procrecords.custid and procrecords.procdt=m.[day]
and procrecords.grp_id=m.grp_id and procrecords.id=m.id
--迟到处理
update procrecords set realstart=min_dt,StartStatus=1,realend=min_dt,EndStatus=1
from (
SELECT CustID, [Day], grp_id, id,min(opdt) as min_dt
FROM #rec
where opdt between DATEADD(mi, cd, PlanStart) and DATEADD(mi, zt, PlanEnd) and not exists
( select *
from (
SELECT CustID, [Day], grp_id, id
FROM #rec
where opdt between DATEADD(mi, tq, PlanStart) and DATEADD(mi, cd, PlanStart)
GROUP BY CustID, [Day], grp_id, id
) n where n.custid=#rec.custid and #rec.[day]=n.[day]
and #rec.grp_id=n.grp_id and #rec.id=n.id
)
GROUP BY CustID, [Day], grp_id, id
) m where m.custid=procrecords.custid and procrecords.procdt=m.[day]
and procrecords.grp_id=m.grp_id and procrecords.id=m.id
update procrecords set StartStatus=3,EndStatus=3 where (procdt between @st and @et) and StartStatus='' and EndStatus=''
--未刷处理
drop table #rec
Go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CL3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CL3]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [CL3]
(
@st datetime, --起始日期
@et datetime --终止日期
)
AS
SELECT orgrec.*, ProcRecords.PlanWorkID, ProcRecords.PlanStart, ProcRecords.RealStart,
ProcRecords.StartStatus, ProcRecords.PlanEnd, ProcRecords.RealEnd,
ProcRecords.EndStatus, ProcRecords.grp_id, ProcRecords.id, bc.Start, bc.[End],
bc.qd_qt, bc.tq, bc.cd, bc.zt, bc.zc
into #rec
FROM (SELECT *, DATEADD([day], DATEDIFF([day], 0, OpDT), 0) AS [Day]
FROM (SELECT CustID, OpDT, CollDT, TermID