根据指纹打开记录，计算员工加班时间

首先，原始记录比较简单，就是excel 表

就是这种形式记录

目前需求是这样

1.公司目前加班常态化，周一至周五8:30-17:30算正式上班，17:30-20:00算加班，加够2小时算钱，否则不算。

  加班时长按8:30开始计算，早来不算。

2.周六上班的时间都算加班时间。

3.周一至周五如果请假缺勤，算付数。

根据这个需求

写了如下存储过程

BEGIN


  DECLARE a VARCHAR(32);
  DECLARE i int DEFAULT 1;
  DECLARE d VARCHAR(50);
DECLARE cur1 CURSOR for SELECT DISTINCT 姓名 from 上班;
  set @staday1 = '2015-9-12';
  set @staday2 = '2015-9-19';


  set @rest1 = '2015-9-13';
  set @rest2 = '2015-9-20';
  
  OPEN cur1;
  
   FETCH cur1 INTO a;
   
   WHILE (a is not NULL) do 
       set  i =1;
    #每月最大天数不一致，十月将此值调整为32
       WHILE i<31 DO
            
            set d = CONCAT('2015-09-',CAST(i AS char(10)));
            set @daytype = (select type from dateType where dd = d);
            
            set @q = (select 
`get0.5`(
if ( (A.`日期`=@staday1 or A.`日期`=@staday2)  , (TIME_TO_SEC(B.`时间`) - TIME_TO_SEC(A.`时间`))/3600  ,
if (A.`时间`>'8:30',  (TIME_TO_SEC(B.`时间`) - TIME_TO_SEC(A.`时间`)-32400)/3600,
(TIME_TO_SEC(B.`时间`) - TIME_TO_SEC('8:30')-32400)/3600)) 


) 
 from 上班 as A  join `下班` as B on A.`姓名` = B.`姓名`  and A.`日期` = B.`日期`  where A.姓名 = a  and A.日期= d);
        
             set @v =
             case 
               when  @daytype='staday' then @q
               when @daytype='restday' then @q
               when @daytype is null then if(@q is null,-8,@q)
             else
               @q
              end;           


            INSERT into test1 values (a,d,@v,@daytype);
         set i = i+1;
        end WHILE ;




     
    FETCH cur1 into a;
   end while;
   CLOSE cur1;
  
END

----------------------------------------------------------------------------------------------------------

其中daytype 表

-------------------------------------------------------------------------------------------------------------

其中 get0.5函数

BEGIN


    DECLARE A FLOAT;
set A =
case 


when `num` <0 and  `num`  > -2 then 0
when `num` <-2   then  FLOOR(`num`)
when `num` >= 0 and `num` <2 then 0
when `num` >= 2 and `num` <2.25 then 2
when `num` >= 2.25 and `num` <2.75 then 2.5
when `num` >= 2.75 and `num` <3.25 then 3
when `num` >= 3.25 and `num` < 3.75 then 3.5
when `num` >= 3.75 and `num` < 4.25 then 4
when `num` >= 4.25 and `num` < 4.75 then 4.5
when `num` >= 4.75 and `num` < 5.25 then 5
when `num` >= 5.25 and `num` < 5.75 then 5.5
when `num` >= 5.75 and `num` < 6.25 then 6
when `num` >= 6.25 and `num` < 6.75 then 6.5
when `num` >= 6.75 and `num` < 7.25 then 7
when `num` >= 7.25 and `num` < 7.75 then 7.5
when `num` >= 7.75 and `num` < 8.25 then 8


when `num` >= 8.25 and `num` < 8.75 then 8.5
when `num` >= 8.75 and `num` < 9.25 then 9
when `num` >= 9.25 and `num` < 9.75 then 9.5
when `num` >= 9.75 and `num` < 10.25 then 10
when `num` >= 10.25 and `num` < 10.75 then 10.5
when `num` >= 10.75 and `num` < 11.25 then 11
when `num` >= 11.25 and `num` < 11.75 then 11.5
when `num` >= 11.75 and `num` < 12.25 then 12


when `num` >= 12.25 and `num` < 12.75 then 12.5
when `num` >= 12.75 and `num` < 13.25 then 13
when `num` >= 13.25 and `num` < 13.75 then 13.5
when `num` >= 13.75 and `num` < 14.25 then 14
when `num` >= 14.25 and `num` < 14.75 then 14.5
when `num` >= 14.75 and `num` < 15.25 then 15
when `num` >= 15.25 and `num` < 15.75 then 15.5
when `num` >= 15.75 and `num` < 16.25 then 16
else
0
end;
RETURN A;
END

结果如下