|
前台调用:
//执行更新数据
justep.Request.sendBizRequest2({
action:"updateSignAction",
parameters:actionParam,
dataType:"json",
callback:function(result){
if(result.state){
if(result.response==""){
alert('补签成功');
}else{
alert(result.response);
}
}else{
alert("补签失败");
}
}
});
后台调用:
//执行存储过程,重新计算分数
try {
Connection conn = ModelUtils.getConnection("/OA/workTime/data");
CallableStatement cstmt = conn.prepareCall("{ call updateScore()}");
cstmt.executeQuery();
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return str;
存储过程:
create or replace procedure updateScore is
score number(4,2); --记录分数的变量
latertimes number(4,2); --记录迟到次数
absentimes_a number(4,2); -- 记录旷工天数
leave_all_day number(4,2) := 1.0 ; -- 旷工一天的扣分
leave_half_day number(4,2) := 0.5; -- 旷工半天的扣分
later_one number(4,2) := 0.1; -- 迟到一次的扣分
overtime_one number(4,2) := 0.1; --加班一次的加分
cursor c_sigin_info is --查询符合时间段内签到记录
select t.fid, t.fsigndate, t.famsignintime, t.famsignintruetime, t.fpmsignintime, t.fpmsignintruetime, t.fsigndate as sigin_date
from oa_wm_signin t
where (to_date(to_char(sysdate, 'yyyy-MM-dd'), 'yyyy-MM-dd') - to_date(t.fsigndate,'yyyy-MM-dd'))<=3;
begin
-- 读取配置表中的扣分赋值到变量中
select t.fabsenteeismallscore, t.fabsenteeismhalfscore, t.flatescore, t.foverworkscore
into leave_all_day, leave_half_day, later_one, overtime_one
from oa_wm_signtimeconfig t where t.fstatus = 'live';
for v_sigin in c_sigin_info loop
score := 0;
latertimes := 0;
absentimes_a := 0.0;
-- 判断上午签到
if(v_sigin.famsignintruetime is not null and v_sigin.famsignintruetime > v_sigin.famsignintime and v_sigin.famsignintruetime<>'补签' and v_sigin.famsignintruetime <>'请假' ) then
score := score + later_one; --上午迟到扣0.1分
latertimes := latertimes+1.0; --迟到次数增加一次
--dbms_output.put_line('--> 上午迟到');
elsif (v_sigin.famsignintruetime is null) then
score := score + leave_half_day; -- 上午旷工扣0.5分
absentimes_a := absentimes_a+0.5;
--dbms_output.put_line('--> 上午旷工');
end if;
-- 判断下午签到
if(v_sigin.fpmsignintruetime is not null and v_sigin.fpmsignintruetime > v_sigin.fpmsignintime and v_sigin.fpmsignintruetime<>'补签' and v_sigin.fpmsignintruetime <>'请假') then
score := score + later_one; --下午迟到扣0.1分
latertimes := latertimes+1.0; -- 迟到次数
--dbms_output.put_line('--> 下午迟到');
elsif (v_sigin.fpmsignintruetime is null) then
score := score + leave_half_day; -- 下午旷工扣0.5分
absentimes_a := absentimes_a+0.5;
--dbms_output.put_line('--> 下午旷工');
end if;
-- 判断是否是旷工一天
if(absentimes_a = 1) then
score := leave_all_day;
end if;
-- 测试打印已经扣除的分数
--dbms_output.put_line('-->已经扣的分数:' || to_char(score) );
-- 更新签到表的分数记录
--dbms_output.put_line('-->>>>>>>' || to_char(absentimes) );
update oa_wm_signin tt set tt.fscore = score, tt.flatertimes = latertimes, tt.absentimes = absentimes_a where tt.fid = v_sigin.fid and tt.fsigndate = v_sigin.sigin_date;
commit;
--dbms_output.put_line('-->分数更新成功 ...' || score || '>>>>' || to_char(latertimes));
end loop;
end updateScore;
|
评分
-
查看全部评分
|