create procedure myNewPro
as
declare @max int
declare @min int
declare @in1 datetime
declare @in2 datetime
declare @in3 datetime
declare @in4 datetime
declare @out1 datetime
declare @out2 datetime
declare @out3 datetime
declare @out4 datetime
declare @Jholiday float
declare @Jholiday1 float
declare @Festival float
declare @Festival1 float
declare @Overtime varchar(120)
declare @division_id varchar(10)
declare @dspName varchar(10)
declare @emp_id varchar(10)
declare @c1 varchar(8) ,@c2 varchar(8) ,@c3 varchar(8),@c4 varchar(8)
declare @a1 float ,@a2 float,@a3 float,@a4 float
declare @commonTime float,@oneDotFiveTime float,@twoTime float, @threeTime float,@quanNum float
declare @type1 varchar(8),@type2 varchar(8),@type3 varchar(8),@type4 varchar(8)
declare @temp1 datetime,@temp2 datetime,@overtimeType int,@salaryType int
declare @Jholidaysee varchar(80),@Festivalsee varchar(80),@waichusee varchar(80),@chucaisee varchar(80),@Overtimesee varchar(80)
declare @tempType varchar(8),@tempTypeInt int,@month varchar(8)
set @max =(select max(id) from T045F120)
set @min=(select min(id) from T045F120)
set @temp1=''
set @temp2=''
while @min<@max
begin
if exists(select field42 from T045F120 where id=@min and field104='1')
begin
set @emp_id=(select field42 from T045F120 where id=@min)
--獲取進廠記錄
set @in1=(select top 1 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9))
if ( select count(*) from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9))>1
set @in2=( select top 1 a.field9 from (select top 2 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
else
set @in2=null
if ( select count(*) from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9))>2
set @in3=( select top 1 a.field9 from (select top 3 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
else
set @in3=null
if ( select count(*) from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9))>3
set @in4=( select top 1 a.field9 from (select top 3 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
else
set @in4=null
--獲取出廠記錄
set @out1=(select top 1 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9))
if ( select count(*) from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9))>1
set @out2=( select top 1 a.field9 from (select top 2 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
else
set @out2=null
if ( select count(*) from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9))>2
set @out3=( select top 1 a.field9 from (select top 3 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
else
set @out3=null
if ( select count(*) from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9))>3
set @out4=( select top 1 a.field9 from (select top 3 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
else
set @out4=null
-- print(@emp_id+@in1+@out1+@in2+@out2+' ' +@in3+' '+@out3+' '+@in4+' '+@out4)
--寫入臨時表變量
--如果進廠時間遲于出廠時間,表明跨天上班
if @in1>@out1
--- set @in1=(select top 1 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-3)=day(field9) order by field9 desc)
if(select top 1 field3 from T093F040 where field21=@emp_id and day(getdate()-3)=day(field9) order by field9 desc)=2
begin
-- set @in1=(select top 1 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-3)=day(field9) order by field9 desc)
--昨天的不處理
set @in1=''
set @out1=''
end
if @out1 is null
-- set @out1=(select top 1 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-1)=day(field9) order by field9 desc)
if(select top 1 field3 from T093F040 where field21=@emp_id and day(getdate()-1)=day(field9) order by field9 asc)=7
begin
set @out1=(select top 1 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-1)=day(field9) order by field9 asc)
end
--獲取部門id號,姓名
select @division_id= field106,@dspName=field41,@salaryType=field63 from T045F120 where field42=@emp_id
--?取?假??
if exists (select id from T055F120 where getdate()-2 between field17 and field18 and field51=@emp_id)
select @Jholiday=field34,@Jholiday1=field35 from T055F120 where getdate()-2 between field17 and field18 and field51=@emp_id
else
begin
set @Jholiday=0.0
set @Jholiday1=0.0
end
--?取公休??
if exists ( select id from T041F120 where getdate()-2 between field19 and field20 and field51=@emp_id)
select @Festival=field30,@Festival1=field31 from T041F120 where getdate()-2 between field19 and field20 and field51=@emp_id
else
begin
set @Festival=0.0
set @Festival1=0.0
end
-- ?取加加班??
if exists ( select id from T056F120 where convert(varchar(10),getdate()-2 ,23)=convert(varchar(10),field17,23) and field46=@emp_id)
begin
select @Overtime=field25,@temp1=field17,@temp2=field18,@overtimeType=field9 from T056F120 where convert(varchar(10),getdate()-2 ,23)=convert(varchar(10),field17,23) and field46=@emp_id
--判斷間段是否為加班段
if @in1 is not null and @out1 is not null and @temp1!='' and @temp2!=''
begin
if datediff(mi,@temp1,@in1)>-60 and datediff(mi,@temp2,@out1)<60
begin
if @overtimeType=1
begin
set @type1=1.5
end
else set @type1=@overtimeType
print('--------------------------------- datediff(mi,@temp1,@in1)------------------------------'+cast(@in1 as varchar(20))+' '+cast(@out1 as varchar(20))+' '+cast( @temp1 as varchar(20))+' '+cast(@temp2 as varchar(20))+' '+cast( datediff(mi,@temp1,@in1) as varchar(8)))
end
end
else
set @type1=1
if @in2 is not null and @out2 is not null
begin
if datediff(mi,@temp1,@in2)>-60 and datediff(mi,@temp2,@out2)<60
begin
if @overtimeType=1
set @type2=1.5
else set @type2=@overtimeType
end
end
else
set @type2=1
if @in3 is not null and @out3 is not null
begin
if datediff(mi,@temp1,@in3)>-60 and datediff(mi,@temp2,@out3)<60
begin
if @overtimeType=1
set @type3=1.5
else set @type3=@overtimeType
end
end
else
set @type3=1
if @in4 is not null and @out4 is not null
begin
if datediff(mi,@temp1,@in4)>-60 and datediff(mi,@temp2,@out4)<60
begin
if @overtimeType=1
set @type4=1.5
else set @type4=@overtimeType
end
end
else
set @type4=1
end
else
begin
set @Overtime=0.0
set @type1=1 set @type2=1 set @type3=1 set @type4=1
end
--null change to ''
if @division_id is null
set @division_id=''
if @dspName is null
set @dspName=''
if @emp_id is null
set @emp_id=''
if @in1 is null
set @in1=''
if @out1 is null
set @out1=''
if @in2 is null
set @in2=''
if @out2 is null
set @out2=''
if @in3 is null
set @in3=''
if @out3 is null
set @out3=''
if @in4 is null
set @in4=''
if @out4 is null
set @out4=''
if @Festival1 is null
set @Festival1=''
if @Festival is null
set @Festival=''
if @Jholiday is null
set @Jholiday=''
if @Jholiday1 is null
set @Jholiday1=''
if @Overtime is null
set @Overtime=''
--把時間小段寫入表中
if @in1!='' and @out1!=''
begin
set @c1=datediff(mi,@in1,@out1)
set @c1=cast(@c1/60 as varchar(20))+substring(cast(@c1%60/60.0 as varchar(20)),2,3)
print(@c1+' '+@emp_id)
--可更改字段設置默認值
set @a1=round(cast(@c1 as float),0,1)
if @a1>8 set @a1=8
end
else
begin
set @c1=0.0
set @a1=0.0
end
if @in2!='' and @out2!=''
begin
set @c2=datediff(mi,@in2,@out2)
set @c2=cast(@c2/60 as varchar(8))+substring(cast(@c2%60/60.0 as varchar(8)),2,3)
print(@c2+' '+@emp_id)
--可更改字段設置默認值,@a2是@c2取整數
set @a2=round(cast(@c2 as float),0,1)
if @a2>8 set @a2=8
print('------------------@a2:@c2---------------'+cast(@a2 as varchar(10))+' '+@c2 );
end
else
begin
set @c2=0.0
set @a2=0.0
end
if @in4!='' and @out4!=''
begin
set @c4=datediff(mi,@in4,@out4)
set @c4=cast(@c4/60 as varchar(8))+substring(cast(@c4%60/60.0 as varchar(8)),2,3)
print(@c4+' '+@emp_id)
--可更改字段設置默認值
set @a4=round(cast(@c4 as float),0,1)
if @a4>8 set @a4=8
end
else
begin
set @c4=0.0
set @a4=0.0
end
if @in3!='' and @out3!=''
begin
set @c3=datediff(mi,@in3,@out3)
set @c3=cast(@c3/60 as varchar(8))+substring(cast(@c3%60/60.0 as varchar(8)),2,3)
print(@c3+' '+@emp_id)
--可更改字段設置默認值
set @a3=round(cast(@c3 as float),0,1)
if @a3>8 set @a3=8
end
else
begin
set @c3=0.0
set @a3=0.0
end
--請假參照field60,公休參照field61,加班參照field63,出差參照field62,外出關照field64
-- @Jholidaysee varchar(50),@Festivalsee varchar(50),waichusee varchar(50),chucaisee varchar(50),Overtimesee varchar(50)
--1.請假參照處理
if exists ( select * from T055F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(20), field17,111) and convert(varchar(20), field18,111) and field51=@emp_id)
begin
select @tempType=field41,@temp1=field17,@temp2=field18 from T055F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111) and field51=@emp_id
--解碼
if @tempType is not null
begin
select @Jholidaysee=field6 from T064F010 where field1=@tempType
set @Jholidaysee=@Jholidaysee+'<br>'+convert(varchar(20),@temp1,120)+'<br>'+convert(varchar(20),@temp2,120)
print('---------------@tempType:@Jholidaysee-----------------'+@tempType+' '+@Jholidaysee);
end
end
else
set @Jholidaysee='----'
--2.公休參照處理
if exists ( select * from T041F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111) and field51=@emp_id)
begin
select @temp1=field17,@temp2=field18 from T055F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111) and field51=@emp_id
set @Festivalsee=convert(varchar(20),@temp1,120)+'<br>'+convert(varchar(20),@temp2,120);
print('---------------公休-----------------'+@Festivalsee);
end
else
set @Festivalsee='----'
--1.加班參照處理
if exists ( select * from T056F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111) and field46=@emp_id)
begin
select @tempTypeInt=field9,@temp1=field17,@temp2=field18 from T056F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111) and field46=@emp_id
--解碼
if @tempTypeInt is not null
begin
if @tempTypeInt=1
set @Overtimesee='1.5倍加班'
if @tempTypeInt=2
set @Overtimesee='2倍加班'
if @tempTypeInt=3
set @Overtimesee='4倍加班'
set @Overtimesee=@Overtimesee+'<br>'+convert(varchar(20),@temp1,120) +'<br>'+convert(varchar(20),@temp2,120)
print('---------------@Overtimesee-----------------'+@Overtimesee);
end
end
else
set @Overtimesee='----'
--1出差參照處理
if exists ( select * from T098F080 where convert(varchar(20),getdate()-2,111)between convert(varchar(10), field14,111) and convert(varchar(10), field15,111) and field32 like '%'+@emp_id+'%')
begin
select @temp1=field14,@temp2=field15 from T098F080 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field14,111) and convert(varchar(10), field15,111) and field32 like '%'+@emp_id+'%'
set @chucaisee='出差'+'<br>'+convert(varchar(20),@temp1,120)+'<br>'+convert(varchar(20),@temp2,120)
print('---------------@chucaisee-----------------'+@chucaisee);
end
else
set @chucaisee='----'
--計算正常上班總時間,1.5倍加班總時間,2.0,3.0倍總時間
if @type1='1' set @commonTime=@a1;
else set @commonTime=0.0;
if @type1='1.5' set @oneDotFiveTime=@a1;
else set @oneDotFiveTime=0.0;
if @type1='2' set @twoTime=@a1;
else set @twoTime=0.0;
if @type1=' 3' set @threeTime=@a1;
else set @threeTime=0.0;
if @type2='1' set @commonTime=@commonTime+@a2;
if @type2='1.5' set @oneDotFiveTime= @oneDotFiveTime+@a2;
if @type2='2' set @twoTime=@twoTime+@a2;
if @type2=' 3' set @threeTime=@threeTime+@a2;
if @type3='1' set @commonTime=@commonTime+@a3;
if @type3='1.5' set @oneDotFiveTime= @oneDotFiveTime+@a3;
if @type3='2' set @twoTime=@twoTime+@a3;
if @type3=' 3' set @threeTime=@threeTime+@a3;
if @type4='1' set @commonTime=@commonTime+@a4;
if @type4='1.5' set @oneDotFiveTime= @oneDotFiveTime+@a4;
if @type4='2' set @twoTime=@twoTime+@a4;
if @type4= '3' set @threeTime=@threeTime+@a4;
set @quanNum= @oneDotFiveTime*1.5+@twoTime*2+@threeTime*3;
print('-----------------------@type1,@type2,@type3,@type4----------------------------------------------------'+@type1+':'+@type2+':'+@type3);
print('--@emp_id,@in1,@out1,@in2,@out2,@in3,@out3---------'+@emp_id+':'+convert(varchar(20),@in1,120)+':'+convert(varchar(20),@out1,120));
print('---------------@oneDotFiveTime:@twoTime:@threeTime------------------------------------'+cast(@commonTime as varchar(20))+':'+cast(@oneDotFiveTime as varchar(20))+':'+cast(@twoTime as varchar(20))+':'+cast(@threeTime as varchar(20))+':'+cast(@quanNum as varchar(20)));
--年月
set @month=cast(month(getdate()-2) as varchar(8));
if cast(@month as int)<10
set @month='0'+cast(month(getdate()-2) as varchar(8));
-- 插入考勤審核檔T089F120
insert into T089F120(df2_id,field51,field1,field42,field44,field43,field17,field18,field19,field20,field21,field22,field23,field24,field25,field26,field27,field28,field30,
field31,field32,field33,field34,field35,field36,field37,field38,field39,field116,field52,field53,field54,field55,field45,field46,field47,field48,field49,field60,field61,field62,field63,field64) values
(719,convert(varchar(20),getdate()-2,111),cast(year(getdate()-2) as varchar(8))+@month,@division_id,@dspName,@emp_id,@in1,@out1,@in2,@out2,@in3,@out3,@in4,@out4,@a1,@a2,@a3,@a4,@Festival1,
@commonTime ,@Overtime,@Jholiday,@Jholiday1,@Festival,@oneDotFiveTime,@twoTime,@threeTime,@quanNum,'',@c1,@c2,@c3,@c4,@type1,@type2,@type3,@type4,@salaryType,@Jholidaysee,@Festivalsee,@chucaisee,@Overtimesee,@waichusee)
end
set @min=@min+1
end
--select * from @temp