CREATE OR REPLACE FUNCTION “F_TEST6”
(
acceptTime in varchar2,
promiseDay in varchar2
)
return number
is
–返回值
return_num number;

totleTime number;
totleYGMDays number;
totleFjDays number;
totleJBDays number;
currentDay char(1);
calcDate date;

begin
return_num :=0;
totleTime :=0;
totleYGMDays :=0;
totleFjDays :=0;
totleJBDays :=0;

–*** 函数说明：传入的受理日期acceptTime，没有考虑节假日里生成的受理日期 ***–

–用于计算的日期变量
select to_date(acceptTime,‘yyyy-mm-dd’) into calcDate from dual ;

–1、计算已走过自然日总天数=当前时间-受理时间+1
select to_number((to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’) - to_date(acceptTime,‘yyyy-mm-dd’) + 1)) into totleTime from dual;

–2、计算所有已走过的周末天数总数(不包括放假里的周末时间)
loop
–dbms_output.put_line('calcDate: ’ || calcDate);
exit when calcDate > to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’);
select case when count(1)>0 then ‘8’ else to_char(calcDate,‘D’) end into currentDay from TB_JIAQU_DATE w where w.status = ‘1’ and w.type = ‘1’
and calcDate >= to_date(w.start_time,‘yyyy-mm-dd’) and calcDate <= to_date(w.end_time,‘yyyy-mm-dd’);
if (currentDay in(‘1’,‘7’)) then
totleYGMDays := totleYGMDays +1;
end if;
calcDate := calcDate + 1;
end loop;

–3、计算所有放假时间：受理时间到当前日期的所有放假时间 (情况1 当前时间在start_time之后且end_time之前，情况2 当前时间在end_time之后)
select sum(calcFJDays) into totleFjDays from (
select
(to_number(
case
when to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’) <= to_date(w.end_time,‘yyyy-mm-dd’)
then to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’)
else to_date(w.end_time,‘yyyy-MM-dd’)
end
- to_date(w.start_time,‘yyyy-MM-dd’)) + 1) as calcFJDays
from TB_JIAQU_DATE w
where w.status = ‘1’ and type = ‘1’
and to_date(acceptTime,‘yyyy-mm-dd’)<= to_date(w.start_time,‘yyyy-mm-dd’)
and (to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’)>= to_date(w.end_time,‘yyyy-mm-dd’)
or (to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’)>= to_date(w.start_time,‘yyyy-mm-dd’) and to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’)<= to_date(w.end_time,‘yyyy-mm-dd’)))
);

–4、计算所有加班时间：受理时间到当前日期的所有加班时间 (情况1 当前时间在start_time之后且end_time之前，情况2 当前时间在end_time之后)
select sum(calcJBDays) into totleJBDays from (
select
(to_number(
case
when to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’) <= to_date(w.end_time,‘yyyy-mm-dd’)
then to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’)
else to_date(w.end_time,‘yyyy-MM-dd’)
end
- to_date(w.start_time,‘yyyy-MM-dd’)) + 1) as calcJBDays
from TB_JIAQU_DATE w
where w.status = ‘1’ and type = ‘2’
and to_date(acceptTime,‘yyyy-mm-dd’)<= to_date(w.start_time,‘yyyy-mm-dd’)
and (to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’)>= to_date(w.end_time,‘yyyy-mm-dd’)
or (to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’)>= to_date(w.start_time,‘yyyy-mm-dd’) and to_date(to_char(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’)<= to_date(w.end_time,‘yyyy-mm-dd’)))
);

–5、计算剩余工作日 = 承诺时间 - (所有自然日 - 放假总天数 - 周末总天数 + 加班总天数)
return_num := nvl(to_number(promiseDay),0) - (nvl(totleTime,0) - nvl(totleFjDays,0) - nvl(totleYGMDays,0) + nvl(totleJBDays,0));

–dbms_output.put_line(‘系统日期：’ || sysdate||’ ，受理日期：’||acceptTime || ’ ，所有自然日：’ || nvl(totleTime,0) || ’ ,周末总天数：’ || nvl(totleYGMDays,0) || ’ ,放假总天数：’ || nvl(totleFjDays,0) || ’ ,加班总天数：’ || nvl(totleJBDays,0) || ’ ,承诺天数：’ || nvl(to_number(promiseDay),0) || ’ ,剩余工作日：’ || nvl(return_num,0));

return return_num;
end;