[Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

ebouilleur · 08-08-2012 10:11:34

Bonjour,

J'ai récupérer une fonction sql qui permet de calculer le nombre d'heure entre 2 dates. Cette fonction ne fonctionne pas sur une base de 24h, mais sur une base de 11h (soit les heures ouvrées : 8h à 19h)

Je dois passer cette fonction sur mysql, mais j'ai qq soucis

Voila la fonction sql server :

USE [IndicateursNoviaServ]

GO

/****** Object:  UserDefinedFunction [dbo].[CalculerDuree]    Script Date: 08/08/2012 08:54:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[CalculerDuree](

@start_date DATETIME ,

@end_date DATETIME )

RETURNS INT

AS

BEGIN

declare @begin_time datetime 

declare @end_time datetime

declare @bdate datetime

declare @edate datetime

declare @nbFerie int

declare @startDate datetime

declare @endDate datetime

set @begin_time = '8:00:00'

set @end_time = '19:00:01'

set @startDate = @Start_date

set @endDate = @end_date

if DATEPART(hour, @start_date) <  DATEPART(Hour, @begin_time) begin set @startDate = CAST(convert(varchar, @start_date, 111) +' '+ @begin_time as datetime)  end

if DATEPART(hour, @start_date) > DATEPART(Hour, @end_time)   begin set @startDate = CAST(convert(varchar, @start_date, 111) +' '+ @end_time as datetime) end

if DATEPART(hour, @end_date) < DATEPART(Hour, @begin_time)  begin set @endDate = CAST(convert(varchar, @end_date, 111) +' '+ @begin_time as datetime) end

if DATEPART(hour, @end_date) > DATEPART(Hour, @end_time)   begin set @endDate = CAST(convert(varchar, @end_date, 111) +' '+ @end_time as datetime) end

IF DATEPART ( WEEKDAY , @endDate + @@DATEFIRST - 1) = 6 begin set @endDate =  CAST(CONVERT(varchar, DATEADD(day,-1, @endDate),111)+' '+@end_time as datetime) end

set @bdate = dateadd(day, datediff(day, 0, @startDate), 0)

set @edate = dateadd(day, datediff(day, 0, @endDate), 0)

set @nbFerie = (SELECT COUNT(*) FROM dbo.DatesFerie WHERE DateFerie BETWEEN @startDate AND @endDate)

RETURN

( SELECT ((total_days / 7) * 5 + total_days % 7 -

CASE WHEN 6 BETWEEN start_weekday AND end_weekday

THEN 1 ELSE 0 END -

CASE WHEN 7 BETWEEN start_weekday AND end_weekday

THEN 1 ELSE 0 END ) * 39600 - DATEDIFF(second, @bdate + @begin_time, @startDate) - DATEDIFF(second, @endDate, @edate + @end_time) - (@nbFerie * 36000)

FROM ( SELECT total_days, start_weekday,

start_weekday + total_days % 7 - 1

FROM ( SELECT DATEDIFF ( day , @startDate, @endDate) + 1,

DATEPART ( WEEKDAY , @startDate + @@DATEFIRST - 1)

) AS T(total_days, start_weekday)

) AS D(total_days, start_weekday, end_weekday)

);

END

Et voila ce que j'ai pour le moment fait en mysql :

DELIMITER $$

CREATE 

FUNCTION `CalculerDuree`(

start_date DATETIME ,

end_date DATETIME )

RETURNS INT

BEGIN

declare begin_time datetime; 

declare end_time datetime ;

declare bdate datetime ;

declare edate datetime ;

declare nbFerie int ;

declare startDate datetime ;

declare endDate datetime;

set begin_time = '8:00:00' ;

set end_time = '19:00:01';

set startDate = start_date ;

set endDate = end_date ;

if DATEPART(hour, start_date) <  DATEPART(Hour, begin_time) then set startDate = CAST(convert(char, start_date, 111) +' '+ begin_time as datetime)  end if;

if DATEPART(hour, start_date) > DATEPART(Hour, end_time)   then set startDate = CAST(convert(char, start_date, 111) +' '+ end_time as datetime) end if;

if DATEPART(hour, end_date) < DATEPART(Hour, begin_time)  then set endDate = CAST(convert(char, end_date, 111) +' '+ begin_time as datetime) end  if;

if DATEPART(hour, end_date) > DATEPART(Hour, end_time)   then set endDate = CAST(convert(char, end_date, 111) +' '+ end_time as datetime) end  if;

IF DATEPART ( WEEKDAY , endDate + @@DATEFIRST - 1) = 6 then set endDate =  CAST(CONVERT(char, DATEADD(day,-1, endDate),111)+' '+end_time as datetime) end  if;

set bdate = dateadd(day, datediff(day, 0, startDate), 0) ;

set edate = dateadd(day, datediff(day, 0, endDate), 0) ;

RETURN

( SELECT ((total_days / 7) * 5 + total_days % 7 -

CASE WHEN 6 BETWEEN start_weekday AND end_weekday

THEN 1 ELSE 0 END -

CASE WHEN 7 BETWEEN start_weekday AND end_weekday

THEN 1 ELSE 0 END ) * 39600 - DATEDIFF(second, bdate + begin_time, startDate) - DATEDIFF(second, endDate, edate + end_time) 

FROM ( SELECT total_days, start_weekday,

start_weekday + total_days % 7 - 1

FROM ( SELECT DATEDIFF ( day , startDate, endDate) + 1,

DATEPART ( WEEKDAY , startDate + @@DATEFIRST - 1)

) AS T(total_days, start_weekday)

) AS D(total_days, start_weekday, end_weekday)

);

END

Là ca coince à la ligne 22 : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' start_date, 111) +' '+ begin_time as datetime) end if; if DATEPART(hour, star' at line 22

Je continu de chercher, mais si vous avez des idées, suggestion, etc je suis preneur

merci

Edit 12h:
Bon, a priori convert() en sql server et mysql ne sont pas les même choses, super ^^
Enfin, je n'ai pas besoin du convert je pense, car ma date ici sera deja au bon format AAAA-MM-JJ hh:mm:ss

Edit 16h:
J'ai du mal a comprendre ce que fait la ligne suivante, avez vous une idée?

startDate = CAST(start_date +' '+ begin_time as datetime)

 

Dernière modification par ebouilleur (08-08-2012 14:56:02)

ebouilleur · 13-08-2012 15:13:26

bonjour,

A defaut de réussir a adapter quelque chose que je ne maitrissais pas, j'ai pondu la fonction suivante en php. IL ne me reste plus qu'a en faire une procédure stockée.

    <?php

    // Fonction permettant de compter le nombre de jours ouvrés entre deux dates

    function get_nb_open_hours($date_start_full, $date_stop_full) {

    // on décompose la date en jour / heure

    $pieces1 = explode(" ", $date_start_full);

    $date_start = $pieces1[0];

    $heure_start = $pieces1[0];     

    $pieces2 = explode(" ", $date_stop_full);

    $date_stop = $pieces2[0];

    $heure_stop = $pieces2[0];

    // variable  (format date dans la bdd : 2012-04-30 11:19:42)

    $heure_open = '08:00:00';

    $heure_close = "19:00:00";

    $date_close_start = $date_start." ".$heure_close;

    $date_open_start = $date_stop." ".$heure_open;    

    $opentime = 11;

    $date_start = strtotime($date_start);

    $date_stop = strtotime($date_stop);

    if ($date_start != $date_stop){

    $nb_second_first_et_last_jour = (strtotime($date_close_start) - strtotime($date_start_full)) + (strtotime($date_stop_full) - strtotime($date_open_start));

    }else{

    $nb_second_first_et_last_jour = (strtotime($date_stop_full) - strtotime($date_start_full));

    }

    $arr_bank_holidays = array(); // Tableau des jours feriés

    // On boucle dans le cas où l'année de départ serait différente de l'année d'arrivée

    $diff_year = date('Y', $date_stop) - date('Y', $date_start);

    for ($i = 0; $i <= $diff_year; $i++) {

    $year = (int)date('Y', $date_start) + $i;

    // Liste des jours feriés

    $arr_bank_holidays[] = '1_1_'.$year; // Jour de l'an

    $arr_bank_holidays[] = '1_5_'.$year; // Fete du travail

    $arr_bank_holidays[] = '8_5_'.$year; // Victoire 1945

    $arr_bank_holidays[] = '14_7_'.$year; // Fete nationale

    $arr_bank_holidays[] = '15_8_'.$year; // Assomption

    $arr_bank_holidays[] = '1_11_'.$year; // Toussaint

    $arr_bank_holidays[] = '11_11_'.$year; // Armistice 1918

    $arr_bank_holidays[] = '25_12_'.$year; // Noel

    // Récupération de paques. Permet ensuite d'obtenir le jour de l'ascension et celui de la pentecote

    $easter = easter_date($year);

    $arr_bank_holidays[] = date('j_n_'.$year, $easter + 86400); // Paques

    $arr_bank_holidays[] = date('j_n_'.$year, $easter + (86400*39)); // Ascension

    $arr_bank_holidays[] = date('j_n_'.$year, $easter + (86400*50)); // Pentecote

    }

    $nb_days_open = 0;

    // on rajoute +1 jour a la date_start vu que le 1er jour est deja calculé et idem pour le dernier jour

    $date_start = $date_start + 86400;    

    $date_stop = $date_stop - 86400;

    while ($date_start <= $date_stop) {

    // Si le jour suivant n'est ni un dimanche (0) ou un samedi (6), ni un jour férié, on incrémente les jours ouvrés

    if (!in_array(date('w', $date_start), array(0, 6))

    && !in_array(date('j_n_'.date('Y', $date_start), $date_start), $arr_bank_holidays)) {

    $nb_days_open++;

    }

    $date_start = mktime(date('H', $date_start), date('i', $date_start), date('s', $date_start), date('m', $date_start), date('d', $date_start) + 1, date('Y', $date_start));

    }

    $nb_jours_ouvres = $nb_days_open ;

    echo "<br> jour ouvrés : ".$nb_jours_ouvres ;

    echo "<br> nb heure du 1er jour : ".$nb_heure_first_jour ;  

    echo "<br> nb_heure_last_jour : ".$nb_heure_last_jour ;      

    $nb_heures_ouvres = $nb_jours_ouvres * $opentime + ($nb_second_first_et_last_jour /3600);

    return $nb_heures_ouvres;

    }

    $date_depart = '2012-08-14 12:00:00';

    $date_fin = '2012-08-16 18:00:00';

    $nb_heures_ouvres = get_nb_open_hours($date_depart, $date_fin) ;

    echo '<br><br>Il y a '.$nb_heures_ouvres.' heures ouvr&eacute;s  entre le '.$date_depart.' et le '.$date_fin ;

    ?>

Jc · 18-08-2012 11:24:05

Bonjour,

Pour commencer pour reprendre ta fonction initiale, en mysql le plus simple, si ta plage de date au niveau des historiques de tes enregistrements à gérer corresponds à la contrainte de domaine, pour avoir le nombre d'heures totales entre deux dates, il suffit de faire un

(UNIX_TIMESTAMP(end_date)-UNIX_TIMESTAMP(start_date))/3600

Ensuite pour avoir le nombre de jour ouvrés effectif, il suffit de diviser par 11 le résultat précédent. Ceci est donc valable lorsque date_end<Now()

Par contre, pour avoir déjà développé un logiciel de généalogie, je peux dores et déjà te dire que si tu désires calculer en interne les vacances religieuses du style paques, tu es dans les choux car c'est largement plus complexe à intégrer niveau calcul. Si ça t'interesse je les ais, mais le plus simple pour le faire en MySQL c'est de faire une table hollidays et de rentrer les jours pour chaque année et non les calculer à la volée. (Le jour de pâques peut s'échelonner sur 3 mois calendaires, et les calculs ne relèvent pas du hasard).

De plus avoir une table de jours fériés n'a d'intérêt que si tu cherches à savoir sur combien de jours calendaires s'échelonnent tes heures ouvrées effectives calculées précedemment, sinon cela n'a aucun intérêt. Tu peux déjà faire un pré calcul à part très rapide sur les jours de fermeture, et les fêtes nationales, et faire un algo spécifique selon la région concernée de france (l'Alsace a des jours fériés supplémentaires que les autres régions n'ont pas par exemple). En fait ta table hollidays te servira en termes de performances principalement pour les vacances religieuses. Tout mettre dedans est aussi à envisager

++

ebouilleur · 20-08-2012 09:02:56

Salut,

Tu dis :
pour avoir le nombre d'heures totales entre deux dates, il suffit de faire un
(UNIX_TIMESTAMP(end_date)-UNIX_TIMESTAMP(start_date))/3600

Oui ca donne le nombre d'heure entre 2 dates, mais la en dehors des ouverts ouvrés "le temps s'arrête"
Par exemple, pour les heures ouvré de 8h a 19h, si j'ai un dossier qui a été ouvert à 18h et fermé le lendemain a 9h, ce dossier aura été ouvert pendant 2h.

Enfin je note que je dois faire une table hollidays pour stocker les jours fériés

merci

Maljuna Kris · 20-08-2012 09:31:23

Jc a écrit :

Pour commencer pour reprendre ta fonction initiale, en mysql le plus simple, si ta plage de date au niveau des historiques de tes enregistrements à gérer corresponds à la contrainte de domaine, pour avoir le nombre d'heures totales entre deux dates, il suffit de faire un
(UNIX_TIMESTAMP(end_date)-UNIX_TIMESTAMP(start_date))/3600
Ensuite pour avoir le nombre de jour ouvrés effectif, il suffit de diviser par 11 le résultat précédent. .

Ou la, mon Jc, ne vas-tu pas un peu vite en besogne, là ?
Si les colonnes end_date et start_date sont des TIMESTAMP, leur appliquer une formule algébrique va jouer sur des journées de 24h (3600 * 24).

Si l'on regarde l'exemple fourni par ébouilleur :
end_date="2012/08/20 09:00:00" et start_date="2012/08/19 19:00:00" ta formule retourne 14; et si l'on avance start_date d'un jour on obtient bien 38 (24h de plus).

La division par 11 ne fait guère avancer le schmilblick.

Jc · 20-08-2012 12:48:23

Bonjour,

Merci de ne pas sortir du contexte ce que j'ai dit. Ma démonstration n'a que pour but de démontrer que les calculs de base sont simples. Ensuite pourquoi diviser par 11? j'ai bien précisé que c'était pour calculer le nombre de jours ouvrés effectifs, et comme il y a 11 heures ouvrées par jour ouvré... cqfd.

Ensuite j'ai bien dit que pour les jours fériés cela n'a d'intérêt à la base que si l'on désire savoir sur combien de jours calendaires s'étalent les jours ouvrés. Maintenant, pour le cas de figure ennoncé par ebouilleur à savoir combien de temps un dossier a été ouvert réellement en terme d'heures ouvrées, une table hollidays est bien plus rapide et performante pour sortir le calcul en effet (un simple count de jours fériés dimanches compris entre deux dates suffisent).

Par contre en terme de sécurité, il vaut mieux à mon avis considérer le temps d'ouverture "réel" d'un dossier surtout si l'on pars du principe que seul un dossier ouvert est modifiable, il peut potentiellement être modifié en dehors des heures d'ouverture, et de considérer le contraire est dangereux.

++

Dernière modification par Jc (20-08-2012 12:54:34)

Maljuna Kris · 20-08-2012 17:15:00

Alors je remets les choses écrites dans leur contexte

Jc a écrit :

pour avoir le nombre d'heures totales entre deux dates, il suffit de faire un
(UNIX_TIMESTAMP(end_date)-UNIX_TIMESTAMP(start_date))/3600
Ensuite pour avoir le nombre de jour ouvrés effectif, il suffit de diviser par 11 le résultat précédent.

et je maintiens que cette division par 11 est erronée, mon bon Jc.

Jc · 20-08-2012 18:41:38

ebouilleur a écrit :

set @begin_time = '8:00:00'
set @end_time = '19:00:01'

@end-time - @begin_time représente bien le nombre d'heures ouvrées par jour ouvré (= 11).
Je ne sais pas ce que tu as compris que je voulais faire ou essayais de faire, mais ce que j'ai dit dans le contexte où je l'ai dit reste vrai.

++

Dernière modification par Jc (20-08-2012 18:42:59)

Jc · 22-08-2012 00:32:07

Bonsoir,

Donc juste un petit récapitulatif du calcul avec la table holidays (1 seule requête)

Nombre d'heures d'ouverture d'un dossier = Nombre d'heures d'ouverture du dossier le jour de l'ouverture + nombre d'heures d'ouverture du dossier le jour de fermeture + ((nbre de jour total entre la fermeture et l'ouverture - le nombre de jour fériés)*11)

Si un dossier peut être ouvert plusieurs fois, je te conseille de créer un log d'ouverture avec en colonnes: id, id_dossier, date_debut, date_fin, nbre heures
Note: dans les deux cas le nombre d'heures doit être decimal, et il peut être interessant de loguer les ids du personnel avec l'action effectuée sur le dossier durant chaque phase où le dossier est ouvert.

++

Dernière modification par Jc (22-08-2012 00:35:06)

PHP|Débutant :: Forums

Advertisement

#1 08-08-2012 10:11:34

[Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

#2 13-08-2012 15:13:26

Re : [Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

#3 18-08-2012 11:24:05

Re : [Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

#4 20-08-2012 09:02:56

Re : [Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

#5 20-08-2012 09:31:23

Re : [Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

#6 20-08-2012 12:48:23

Re : [Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

#7 20-08-2012 17:15:00

Re : [Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

#8 20-08-2012 18:41:38

Re : [Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

#9 22-08-2012 00:32:07

Re : [Procédure stocké ] Calcul d'heure sur une base de 11h (et non 24h)

Pied de page des forums