CREATE procedure p_att_onduty_days(@i_empoguid char(38),@i_startdate datetime,@i_enddate datetime)
as
declare
@V_onduty_days decimal(9,2),
@V_real_days decimal(9,2),
@V_nonormal_days decimal(9,2),
@V_makegid char(38),
@V_cale decimal(9,2),
@V_hotcale decimal(9,2),
@V_isWeekEnd decimal(9,2),
@V_total_days decimal(9,2),
@i datetimebegin delete from t_att_onduty_days
where emp_OGuid=@i_empOGuid and start_date between @i_startDate and @i_endDate set @V_makegid='{'+CONVERT(varchar(38), newid())+'}'
set @i=@i_startdate
set @V_isWeekEnd=0 while @i<=@i_enddate
begin select @V_cale=count(*)
from t_vac_calendar_sub sub,
(select att_OGuid
from t_vac_emp_calendar
where emp_OGuid=@i_empOguid) empcale
where sub.CALE_OGUID=empcale.att_oguid
and convert(char(10),sub.vac_date,120)=convert(char(10),@i,120)
select @V_hotcale=count(*)
from t_vac_hotcalendar_sub sub,
(select att_OGuid from t_vac_emp_workcale where emp_OGuid=@i_empOguid) empcale
where sub.CALE_OGUID=empcale.att_oguid
and convert(char(10),sub.vac_date,120)=convert(char(10),@i,120)
if (datepart(dw,@i)=1 or datepart(dw,@i)=7 or (@V_cale<>0)) and (@V_hotCale=0)
set @V_isWeekEnd=@V_isWeekEnd+1
set @i=@i+1
end set @V_total_days=datediff(day,@i_startdate,@i_enddate)+1
print @i_startdate+1
print @i_enddate
print @V_total_days
set @V_onduty_days=@V_total_days-@V_isWeekEnd select @V_noNormal_days=sum(diff_time)
from t_att_result
where emp_OGuid=@i_empOGuid
and code_Oguid not in ('{E3CF59EC-9BD3-4AEF-B468-4D8A5B9690ED}','{0ECA2FDC-893A-48C4-8352-5E02868566FC}','{1AC48201-58F2-49B6-BA25-5B14643C5797}',
'{B73E6089-CC00-4B55-9463-733168D7FD62}','{D5843F3B-D7C4-4FDB-889F-7553C9173451}')
and (att_date between @i_startdate and @i_enddate) set @V_real_days=datediff(day,@V_noNormal_Days,@V_onduty_days) --@V_onduty_days-@V_noNormal_Days insert into t_att_onduty_days(OGUID,
EMP_OGUID,
TOTAL_DAYS,
ONDUTY_DAYS,
REAL_ONDUTY_DAYS,
NONORMAL_DAYS,
START_DATE,END_DATE)
values( @V_makegid,@i_empOGuid,@V_total_days,@V_onduty_days,@V_real_days,@V_nonormal_days,@i_startDate,@i_endDate)
endGO
CREATE procedure p_att_onduty_days(i_empoguid VARCHAR2,i_startdate DATE,i_enddate DATE)
AS
DECLARE
V_onduty_days NUMBER(9,2);
V_real_days NUMBER(9,2);
V_nonormal_days NUMBER(9,2);
V_makegid VARCHAR2(38);
V_cale NUMBER(9,2);
V_hotcale NUMBER(9,2),
V_isWeekEnd NUMBER(9,2);
V_total_days NUMBER(9,2);
i DATE;BEGIN delete from t_att_onduty_days
where emp_OGuid=i_empOGuid and start_date between i_startDate and i_endDate; --V_makegid :='{'||CONVERT(varchar(38), newid())||'}'; --这里需要用序列号,oracle没有guid函数
SELECT '{' || yourseq.nextval ||')' into V_makegid from dual;
i := i_startdate;
V_isWeekEnd := 0; WHILE i <= i_enddate SELECT count(*) INTO V_cale
FROM t_vac_calendar_sub sub,
(SELECT att_OGuid
FROM t_vac_emp_calendar
WHERE emp_OGuid=i_empOguid) empcale
WHERE sub.CALE_OGUID=empcale.att_oguid
AND sub.vac_date = i;
SELECT count(*) INTO V_hotcale
FROM t_vac_hotcalendar_sub sub,
(SELECT att_OGuid FROM t_vac_emp_workcale
WHERE emp_OGuid=@i_empOguid) empcale
WHERE sub.CALE_OGUID=empcale.att_oguid
and sub.vac_date = i;
IF (TO_CHAR(i,'D')=1 or TO_CHAR(i,'D')=7 or (V_cale<>0)) and (V_hotCale=0) THEN
V_isWeekEnd := V_isWeekEnd + 1;
END IF; i := i+1;
LOOP V_total_days := ABS(i_startdate - i_enddate) + 1;
--print @i_startdate+1
DBMS_OUTPUT.printline(i_startdate+1);
--print @i_enddate
DBMS_OUTPUT.printline(i_enddate+1);
--print @V_total_days
DBMS_OUTPUT.printline(V_total_days+1);
V_onduty_days := V_total_days - V_isWeekEnd; SELECT sum(diff_time) INTO V_noNormal_days
FROM t_att_result
WHERE emp_OGuid=i_empOGuid
and code_Oguid not in ('{E3CF59EC-9BD3-4AEF-B468-4D8A5B9690ED}','{0ECA2FDC-893A-48C4-8352-5E02868566FC}','{1AC48201-58F2-49B6-BA25-5B14643C5797}',
'{B73E6089-CC00-4B55-9463-733168D7FD62}','{D5843F3B-D7C4-4FDB-889F-7553C9173451}')
and (att_date between i_startdate and i_enddate) V_real_days := ABS(V_noNormal_Days - V_onduty_days); --@V_onduty_days-@V_noNormal_Days insert into t_att_onduty_days(OGUID,
EMP_OGUID,
TOTAL_DAYS,
ONDUTY_DAYS,
REAL_ONDUTY_DAYS,
NONORMAL_DAYS,
START_DATE,END_DATE)
values( V_makegid,i_empOGuid,V_total_days,V_onduty_days,V_real_days,V_nonormal_days,i_startDate,i_endDate)
END;--GO