1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
| create or replace function func_DureeSecJO (
p_date_deb date,
p_date_fin date,
p_heure_debut varchar2 default '07:00',
p_heure_fin varchar2 default '19:00')
return number as
l_date_deb date;
l_date_fin date;
l_date_deb_ouvre date;
l_date_fin_ouvre date;
l_nb_jour_ouvre_entre number;
l_delai number;
begin
if p_date_fin < p_date_deb then
return 0;
end if;
-- Si l'heure de la date de début est supérieure à l'heure de fin de travail (19:00)
-- ==> La date de début est le lendemain à 07h
if (p_date_deb >= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
l_date_deb := to_date(to_char(p_date_deb + 1,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
-- Si l'heure de la date de début est inférieure à l'heure de début de travail (07:00)
-- ==> La date de début est la date de début à 07h
elsif (p_date_deb <= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
l_date_deb := to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
else
-- Sinon on garde la date de début telle quelle
l_date_deb := p_date_deb;
end if;
-- Si l'heure de la date de fin est supérieure à l'heure de fin de travail (19:00)
-- ==> La date de fin est la date de fin à 19h
if (p_date_fin >= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
l_date_fin := to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
-- Si l'heure de la date de fin est inférieure à l'heure de début de travail (07:00)
-- ==> La date de fin est la veille à 19h
elsif (p_date_fin <= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
l_date_fin := to_date(to_char(p_date_fin - 1,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
else
-- Sinon on garde la date de fin telle quelle
l_date_fin := p_date_fin;
end if;
if l_date_fin < l_date_deb then
return 0;
end if;
-- Gestion des bornes, on prend la première date ouvrée > l_date_deb et la dernière date ouvrée < l_date_fin
-- Si aucune ligne sélectionnée => to_date('31/12/9999','dd/mm/yyyy') pour obtenir 0
-- Calcul du nombre de jours strictment entre les date min et date max
with jours_ouvres as (
select cal_jour
from v_calendrier
where cal_jour between trunc(l_date_deb) and trunc(l_date_fin)
and cal_position_semaine NOT IN ('6', '7')
and clf_chome = 0
),
min_max_jours as (
select min(to_date(to_char(cal_jour,'YYYYMMDD')||to_char(l_date_deb,'hh24:mi:ss'),'YYYYMMDDhh24:mi:ss')) as min_jour,
max(to_date(to_char(cal_jour,'YYYYMMDD')||to_char(l_date_fin,'hh24:mi:ss'),'YYYYMMDDhh24:mi:ss')) as max_jour
from jours_ouvres
)
select coalesce(max(min_jour),to_date('31/12/9999','dd/mm/yyyy')),
coalesce(max(max_jour),to_date('31/12/9999','dd/mm/yyyy')),
coalesce(sum(case when cal_jour > trunc(min_jour) and cal_jour < trunc(max_jour) then 1 else 0 end),0) as nb_jours
into l_date_deb_ouvre, l_date_fin_ouvre, l_nb_jour_ouvre_entre
from jours_ouvres
cross join min_max_jours;
-- Si la date ouvrée de début est supérieure à la date de début
-- ==> La date de début est la date ouvrée à 07h
if l_date_deb_ouvre > l_date_deb then
l_date_deb := to_date(to_char(l_date_deb_ouvre,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
end if;
-- Si la date ouvrée de fin est inférieure à la date de fin
-- ==> La date de fin est la date ouvrée à 19h
if l_date_fin_ouvre < l_date_fin then
l_date_fin := to_date(to_char(l_date_fin_ouvre,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
end if;
--dbms_output.put_line(l_date_deb ||' --> '|| l_date_fin ||'-->'|| l_nb_jour_ouvre_entre);
if l_date_fin < l_date_deb then
return 0;
elsif (trunc(l_date_deb) = trunc(l_date_fin)) then
return (l_date_fin - l_date_deb)*24*60*60;
else
/* Le délai est calculé comme :
1/ "délai écoulé le dernier jour" + "délai écoulé le 1er jour" + "délai écoulé pendant les jours entre".
2/ date1 - date2 donne un résultat en nombre de jour (*24*60*60) pour avoir un résultat en seconde
3/ to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi')-to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi')
==> ici 0.5 car les journées ne font pas 24h mais seulement 12h
*/
return (l_date_fin - to_date(to_char(l_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60
+ (to_date(to_char(l_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi') - l_date_deb)*24*60*60
+ l_nb_jour_ouvre_entre * ( to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi')
- to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60;
end if;
end func_DureeSecJO;
/ |
Partager