FQYZjiaozhigong考勤排班(利用外部文件导入生成)
--FQYZjiaozhigong考勤排班(利用外部文件导入生成)
declare @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 * from bc order by id
open bc
fetch next from bc into @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
select @s='declare exam cursor for SELECT custid,'+@col+ ' FROM Exam'
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)
select @custid ,d,0 as planid,p1,p2,@dept 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 @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
------------------(以下为bc表的触发器，主要作用是根据bc表的name字段值自动增减改Exam表相应的字段)
CREATE TRIGGER [Tr] ON [dbo].[bc]
FOR INSERT, UPDATE, DELETE
AS
declare @n varchar(100),@o varchar(100)
if @@rowcount>0
begin
if exists(select i.name from inserted i where i.id<>all(select d.id from deleted d))
begin
select @n=i.name from inserted i where i.id<>all(select d.id from deleted d) --新增列
exec('alter table Exam add '+@n+ ' varchar(255) null')
end
if exists(select d.name from deleted d where d.id<>all(select i.id from inserted i))
begin
select @n=d.name from deleted d where d.id<>all(select i.id from inserted i) --减少列
exec('ALTER TABLE Exam DROP COLUMN '+@n)
end
if exists(select i.name from deleted d ,inserted i where d.id=i.id and i.name<>d.name)
begin
select @n=i.name,@o=d.name from deleted d ,inserted i where d.id=i.id and i.name<>d.name --更新列
select @o='Exam.'+@o
exec sp_rename @o,@n
end
end
--------------------------------(以下为处理函数生成脚本，利用在以上处理代码中)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_F]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_F]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_h]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_h]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION f_F(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION f_h(
@s varchar(8000), --待分行的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col int)
AS
BEGIN
insert @re
select * from f_f(@s,@split)
update @re set col=8 where col=7
update @re set col=col+1 where col<>8
update @re set col=1 where col=8
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------(以下为需要增加的表说明)
| 班次表,表名bc,利用name字段值生成Exam表的字段。 | |||
| id | name | Start | End |
| 1 | 早读 | 07:15:00 | 07:35:00 |
| 2 | 升旗 | 07:40:00 | 08:00:00 |
| 3 | 晚督修 | 18:10:00 | 22:20:00 |
|
custid
|
outid
|
教师
|
早读
|
升旗
|
晚督修
|
|
7
|
00010006
|
吕
|
|
1
|
3
|
|
11
|
00010010
|
杨
|
|
1
|
5
|
|
30
|
00010203
|
林
|
1,3,5
|
1
|
1
|
|
55
|
00010301
|
伍
|
|
1
|
2
|
|
71
|
00020104
|
何
|
|
1
|
3
|
|
75
|
00020108
|
吴
|
|
1
|
1,5
|
|
80
|
00020205
|
周
|
1,3,5
|
1
|
1,5
|
|
84
|
00020209
|
郭
|
1,3,5
|
1
|
1,5
|
|
90
|
00020215
|
李
|
1,3,5
|
1
|
4,7
|
|
94
|
00020219
|
杨
|
1,3,5
|
1
|
5,7
|
|
103
|
00020303
|
吴
|
|
1
|
1,3
|
|
112
|
00020312
|
余
|
|
1
|
4,7
|
|
113
|
00020313
|
叶
|
|
1
|
4
|
|
114
|
00020314
|
何
|
|
1
|
5,7
|
|
115
|
00020315
|
郑
|
|
1
|
5,7
|
|
126
|
00020402
|
杨
|
2,4,6
|
1
|
1,4
|
|
129
|
00020405
|
陈
|
2,4,6
|
1
|
2,4
|
|
134
|
00020410
|
雷
|
2,4,6
|
1
|
2,3
|
|
138
|
00020414
|
程
|
2,4,6
|
1
|
3,7
|
|
139
|
00020415
|
黄
|
2,4,6
|
1
|
3,7
|
|
149
|
00020501
|
童
|
|
1
|
1,5
|
|
151
|
00020503
|
陈
|
|
1
|
2
|
|
154
|
00020506
|
詹
|
|
1
|
1,5
|
|
157
|
00020509
|
施
|
|
1
|
5,7
|
|
166
|
00020603
|
吴
|
|
1
|
3,5
|
|
181
|
00020705
|
林
|
|
1
|
4,7
|
|
184
|
00020708
|
朱
|
|
1
|
2,7
|
|
187
|
00020803
|
郑
|
|
1
|
1
|
|
190
|
00020806
|
钟
|
|
1
|
2,3
|
|
196
|
00020906
|
严
|
|
1
|
3
|
|
4011
|
00020109
|
倪
|
|
1
|
3,7
|
|
4012
|
00020325
|
林
|
|
1
|
2,4
|
|
4013
|
00020425
|
秦
|
2,4,6
|
1
|
4
|
|
4014
|
00020910
|
吴
|
|
1
|
2
|