store_procedure

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