CREATE OR REPLACE function moniter_work_time(p_bgdatetime1 in date, --开始时间
p_eddatetime1 in date, --结束时间
workorderid  in number
) return number is
min_time number(9);
v_holiday_count varchar2(400);
v_yongshi        number := 0; ----用时,单位小时
v_bgtime         char(8);
v_edtime         char(8);
v_bgdate         char(10);
v_eddate         char(10);
v_tmp_bgdatetime varchar2(50);
v_tmp_eddatetime varchar2(50);
v_midtime        number := 0; -----中午的1.5小时
v_dtime          number;
v_fishDate       date;
p_bgdatetime     DATE;
p_eddatetime         DATE;
begin
p_bgdatetime := p_bgdatetime1;
p_eddatetime := p_eddatetime1;
v_bgtime     := to_char(p_bgdatetime, 'hh24:mi:ss');
v_edtime     := to_char(p_eddatetime, 'hh24:mi:ss');
v_bgdate     := to_char(p_bgdatetime, 'yyyy-mm-dd');
v_eddate     := to_char(p_eddatetime, 'yyyy-mm-dd');
for r1 in (SELECT DEFINE_WEEK FROM JK_OFFICIAL_HOLIDAY WHERE IS_STATUS = 1) loop
if v_bgdate = r1.DEFINE_WEEK then
p_bgdatetime:= p_bgdatetime - 1;
v_bgtime := '18:00:00';
end if;
if v_eddate = r1.DEFINE_WEEK then
p_eddatetime:= p_eddatetime + 1;
v_edtime := '08:30:00';
end if;
end loop;
v_bgdate     := to_char(p_bgdatetime, 'yyyy-mm-dd');
v_eddate     := to_char(p_eddatetime, 'yyyy-mm-dd');
--挂起时间
begin
select nvl(max(sum(round(moniter_work_time_tmp(wp.pause_begin_date,nvl(wp.pause_end_date, v_fishDate),workorderid),2))),0)
INTO v_dtime
from work_order_pause wp
where wp.work_order_id = workorderid
group by wp.work_order_id;
EXCEPTION
WHEN OTHERS THEN
v_dtime := 0;
end;
begin
--去除节假日
SELECT
COUNT( 1 ) into v_holiday_count
FROM
JK_OFFICIAL_HOLIDAY jjk
WHERE
jjk.IS_STATUS = 1
AND to_date( jjk.DEFINE_WEEK, 'yyyy-mm-dd' ) BETWEEN p_bgdatetime AND p_eddatetime;
EXCEPTION
WHEN OTHERS THEN
v_holiday_count := 0;
end;

if v_bgtime <= '08:30:00' then v_bgtime := '08:30:00';
elsif v_bgtime >= '18:00:00' then    v_bgtime := '18:00:00';
end if;
if v_edtime <= '08:30:00' then v_edtime := '08:30:00';
elsif v_edtime >= '18:00:00' then v_edtime := '18:00:00';
end if;
if trunc(p_bgdatetime,'dd')=trunc(p_eddatetime,'dd')then
--同一天
v_tmp_bgdatetime := '2016-11-22 ' || v_bgtime;
v_tmp_eddatetime := '2016-11-22 ' || v_edtime;
v_yongshi := (to_date(v_tmp_eddatetime, 'yyyy-mm-dd hh24:mi:ss') - to_date(v_tmp_bgdatetime, 'yyyy-mm-dd hh24:mi:ss')) * 24;
ELSIF (trunc(p_bgdatetime,'dd') <> trunc(p_eddatetime,'dd')) THEN
--规范化时间
v_tmp_bgdatetime := v_bgdate || v_bgtime;
v_tmp_eddatetime := v_eddate || v_edtime;
v_yongshi := (to_date(v_tmp_eddatetime, 'yyyy-mm-dd hh24:mi:ss') - to_date(v_tmp_bgdatetime, 'yyyy-mm-dd hh24:mi:ss')) * 24 - (trunc(to_date(v_eddate,'yyyy-mm-dd'),'dd')- trunc(to_date(v_bgdate,'yyyy-mm-dd'),'dd'))*14.5;
end if;
min_time := round(v_yongshi * 60, 2) - v_holiday_count*9.5*60 - v_dtime;
return min_time;
EXCEPTION
WHEN OTHERS THEN
min_time := (p_eddatetime1 - p_bgdatetime1) * 60 * 24;
return min_time;
end moniter_work_time;

CREATE OR REPLACE function moniter_work_time(p_bgdatetime1 in date, --开始时间 p_eddatetime1 in date, --结束时间 worko... console.log(moment().format("YYYY-MM-DD HH:mm:ss")); //当前 时间 console.log(moment().format("YYYY-MM-DD HH:mm:ss").set('hour', 18).set('minute', 0).set('second', 0)); //当前特定 时间 console.log(moment().subtract(10, "days").format("YYYY-MM-DD")); /