SELECT late_sec = env_late_sec, early_sec = env_early_sec, option_1 = env_option_1, option_2 = env_option_2
from rit_work.environment
order by env_idx desc
limit 1;
FOR rec IN
select a.wus_day, a.wus_user_id, b.user_name, b.dc_code
, case when length(b.dc_code) = 0 then
''
else
c.dc_name
end
, a.wus_wc_code
, case when length(a.wus_wc_code) = 0 then
''
else
d.wc_name
end
, case when length(a.wus_wc_code) = 0 then
''
else
d.wc_type
end
, case when length(a.wus_wc_start) = 0 or length(a.wus_attend) = 0 then
'--:--'
when a.wus_wc_start <= a.wus_attend then
substring(
( (substring(a.wus_attend, 1, 4) || '-' || substring(a.wus_attend, 5, 2)
|| '-' || substring(a.wus_attend, 7, 2) || ' ' || substring(a.wus_attend, 9, 2)
|| ':' || substring(a.wus_attend, 11, 2) || ':' || substring(a.wus_attend, 13, 2))::timestamp with time zone
-
(substring(a.wus_wc_start, 1, 4) || '-' || substring(a.wus_wc_start, 5, 2)
|| '-' || substring(a.wus_wc_start, 7, 2) || ' ' || substring(a.wus_wc_start, 9, 2)
|| ':' || substring(a.wus_wc_start, 11, 2) || ':' || substring(a.wus_wc_start, 13, 2))::timestamp with time zone )
, 1, 2)
|| ':' ||
substring(
( (substring(a.wus_wc_start, 1, 4) || '-' || substring(a.wus_wc_start, 5, 2)
|| '-' || substring(a.wus_wc_start, 7, 2) || ' ' || substring(a.wus_wc_start, 9, 2)
|| ':' || substring(a.wus_wc_start, 11, 2) || ':' || substring(a.wus_wc_start, 13, 2))::timestamp with time zone
-
(substring(a.wus_attend, 1, 4) || '-' || substring(a.wus_attend, 5, 2)
|| '-' || substring(a.wus_attend, 7, 2) || ' ' || substring(a.wus_attend, 9, 2)
|| ':' || substring(a.wus_attend, 11, 2) || ':' || substring(a.wus_attend, 13, 2))::timestamp with time zone )
, 4, 2)
else
'--:--'
end as t1
, case when length(a.wus_wc_end) = 0 or length(a.wus_leave) = 0 then '--:--'
when a.wus_wc_end > a.wus_leave then
substring(
( (substring(a.wus_wc_end, 1, 4) || '-' || substring(a.wus_wc_end, 5, 2)
|| '-' || substring(a.wus_wc_end, 7, 2) || ' ' || substring(a.wus_wc_end, 9, 2)
|| ':' || substring(a.wus_wc_end, 11, 2) || ':' || substring(a.wus_wc_end, 13, 2))::timestamp with time zone
-
(substring(a.wus_leave, 1, 4) || '-' || substring(a.wus_leave, 5, 2)
|| '-' || substring(a.wus_leave, 7, 2) || ' ' || substring(a.wus_leave, 9, 2)
|| ':' || substring(a.wus_leave, 11, 2) || ':' || substring(a.wus_leave, 13, 2))::timestamp with time zone )
, 1, 2)
|| ':' ||
substring(
( (substring(a.wus_wc_end, 1, 4) || '-' || substring(a.wus_wc_end, 5, 2)
|| '-' || substring(a.wus_wc_end, 7, 2) || ' ' || substring(a.wus_wc_end, 9, 2)
|| ':' || substring(a.wus_wc_end, 11, 2) || ':' || substring(a.wus_wc_end, 13, 2))::timestamp with time zone
-
(substring(a.wus_leave, 1, 4) || '-' || substring(a.wus_leave, 5, 2)
|| '-' || substring(a.wus_leave, 7, 2) || ' ' || substring(a.wus_leave, 9, 2)
|| ':' || substring(a.wus_leave, 11, 2) || ':' || substring(a.wus_leave, 13, 2))::timestamp with time zone )
, 4, 2)
else
'--:--'
end as t2
, case when length(a.wus_wc_start) = 0 or length(a.wus_attend) = 0 then '--:--'
when a.wus_wc_start > a.wus_attend then
substring(
( (substring(a.wus_wc_start, 1, 4) || '-' || substring(a.wus_wc_start, 5, 2)
|| '-' || substring(a.wus_wc_start, 7, 2) || ' ' || substring(a.wus_wc_start, 9, 2)
|| ':' || substring(a.wus_wc_start, 11, 2) || ':' || substring(a.wus_wc_start, 13, 2))::timestamp with time zone
-
(substring(a.wus_attend, 1, 4) || '-' || substring(a.wus_attend, 5, 2)
|| '-' || substring(a.wus_attend, 7, 2) || ' ' || substring(a.wus_attend, 9, 2)
|| ':' || substring(a.wus_attend, 11, 2) || ':' || substring(a.wus_attend, 13, 2))::timestamp with time zone )
, 1, 2)
|| ':' ||
substring(
( (substring(a.wus_wc_start, 1, 4) || '-' || substring(a.wus_wc_start, 5, 2)
|| '-' || substring(a.wus_wc_start, 7, 2) || ' ' || substring(a.wus_wc_start, 9, 2)
|| ':' || substring(a.wus_wc_start, 11, 2) || ':' || substring(a.wus_wc_start, 13, 2))::timestamp with time zone
-
(substring(a.wus_attend, 1, 4) || '-' || substring(a.wus_attend, 5, 2)
|| '-' || substring(a.wus_attend, 7, 2) || ' ' || substring(a.wus_attend, 9, 2)
|| ':' || substring(a.wus_attend, 11, 2) || ':' || substring(a.wus_attend, 13, 2))::timestamp with time zone )
, 4, 2)
else
'--:--'
end as t3
, case when length(a.wus_wc_end) = 0 or length(a.wus_leave) = 0 then '--:--'
when a.wus_wc_end <= a.wus_leave then
substring(
( (substring(a.wus_leave, 1, 4) || '-' || substring(a.wus_leave, 5, 2)
|| '-' || substring(a.wus_leave, 7, 2) || ' ' || substring(a.wus_leave, 9, 2)
|| ':' || substring(a.wus_leave, 11, 2) || ':' || substring(a.wus_leave, 13, 2))::timestamp with time zone
-
(substring(a.wus_wc_end, 1, 4) || '-' || substring(a.wus_wc_end, 5, 2)
|| '-' || substring(a.wus_wc_end, 7, 2) || ' ' || substring(a.wus_wc_end, 9, 2)
|| ':' || substring(a.wus_wc_end, 11, 2) || ':' || substring(a.wus_wc_end, 13, 2))::timestamp with time zone )
, 1, 2)
|| ':' ||
substring(
( (substring(a.wus_leave, 1, 4) || '-' || substring(a.wus_leave, 5, 2)
|| '-' || substring(a.wus_leave, 7, 2) || ' ' || substring(a.wus_leave, 9, 2)
|| ':' || substring(a.wus_leave, 11, 2) || ':' || substring(a.wus_leave, 13, 2))::timestamp with time zone
-
(substring(a.wus_wc_end, 1, 4) || '-' || substring(a.wus_wc_end, 5, 2)
|| '-' || substring(a.wus_wc_end, 7, 2) || ' ' || substring(a.wus_wc_end, 9, 2)
|| ':' || substring(a.wus_wc_end, 11, 2) || ':' || substring(a.wus_wc_end, 13, 2))::timestamp with time zone )
, 4, 2)
else
'--:--'
end as t4
,
case
when length(a.wus_attend) = 0 then
'--:--'
when length(a.wus_attend) = 14 then
substring(a.wus_attend, 9, 2) || ':' || substring(a.wus_attend, 11, 2)
end as attend_time
,
case
when length(a.wus_leave) = 0 then
'--:--'
when length(a.wus_leave) = 14 then
substring(a.wus_leave, 9, 2) || ':' || substring(a.wus_leave, 11, 2)
end as leave_time
, a.wus_attend, a.wus_leave
, a.wus_wc_start, a.wus_wc_end
, a.wus_wc_ss, a.wus_wc_se, a.wus_wc_es, a.wus_wc_ee
from rit_work.work_user_schedule a left join rit_work.user b on a.wus_user_id = b.user_id
left join rit_work.depart_code c on b.dc_code = c.dc_code
left join rit_work.work_code d on a.wus_wc_code = d.wc_code
where a.wus_day between $1 and $2
order by a.wus_day, b.user_name asc
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION rit_work.usp_s_work_result_day(start_day character varying, end_day character varying) OWNER TO postgres;
COMMENT ON FUNCTION rit_work.usp_s_work_result_day(start_day character varying, end_day character varying) IS 'comment';