项目那点事

想说几点工作碰到的：

1.首先就是JSP文件中传回的值：""并不是为null。而至于怎么传回null值，还有待实验

2.就是mybatis中insert到数据库中null列值是必须注明jdbcType，比如：一个表的外键值有可能为空（即改表此时没有所关联的表的值）

方法：

#{branch,jdbcType=INTEGER}等等，注意大小写。

3.工数黑名单检查SQL代码：

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `checkerror2`(startDate date,endDate date)
begin
    declare done int default false;
    declare staffNum int;
    declare firstStaff int;
    
    
    declare curTemp cursor for
    select staffID from staff where position !='所长' and position !='董事长' and position !='本部长' and position !='经理' and position!='担当经理'and state=1 order by staffID;
    declare continue handler for not found set done =true;
    open curTemp;
    
    set firstStaff=(select staffID from staff where position !='所长' and position !='董事长' and position !='本部长' and position !='经理' and position!='担当经理' and state=1
    order by staffID limit 1);
    
    truncate table temp;
    read_loop:loop
    fetch curTemp into staffNum;
    if done then
        leave read_loop;
    end if;
    if staffNum >= firstStaff then
        set staffNum=(select checkbystaffid(staffNum,startDate,endDate));
    end if;
    end loop;
close curTemp;
    insert into temp (staffID,date,totalTimes) (select m.staffID,m.date,sum(m.times) as totalTimes 
    from manhour m,work_calendar wc,staff s
    where m.date between startDate and endDate and m.date = wc.date and wc.able=1
    and s.staffID = m.staffID and s.state = 1
    group by m.staffID ,m.date
    having sum(m.times) < 8 or sum(m.times)>24);
end

CREATE DEFINER=`root`@`%` FUNCTION `checkbystaffid`(staffNum int,sdate date,edate date) RETURNS int(1)
begin

declare done int default false;
declare indexdate DATE;

declare cur1 cursor for  
select date from work_calendar where date not in (select m.date as mDate 
from manhour m
where m.date between sdate and edate and m.staffID= staffNum
group by m.date) and able =1 and date between sdate and edate;

declare continue handler for not found set done =true;
open cur1;


set indexdate = sdate;
read_loop:loop
    fetch cur1 into indexDate;
    if done then
        leave read_loop;
    end if;
    if indexDate >= sdate then
        insert into temp (staffID,date) values(staffNum,indexDate);
    end if;
end loop;
close cur1;
return(staffNum);
end