Bonjour,
Comme très justement signalé par MoYo ou JMB voici les 2 patchs et le
dump en question.
/!\ les deux patchs s'appliquent sur la version 0.60.2 /!\
commondbtm.class.php.patch : modification de la requête pour prendre en
compte la fonction de calcul de dates.
/!\ la fonction sec_to_time à une limitation à 838:59:59 présente à
partir de la version 5.0.22 (?) de mysql et nécessiterais un recalcul
du temps retourné en secondes vers hh:mm:ss
tracking.function.php.patch : remplacement du champs realtime par celui
retourné précédemment. Par flemmardise aiguë je laisse la modification
concernant l'ajout de l'utilisateur sous sa machine.
Le dump contient les deux routines ainsi que les tables :
*
ben_hd_dates :
rempli avec les jours fériés en vigueur (français + alsaciens +
ponts nous concernant)
*
ben_hd_mod :
7 lignes renfermant les plages horaires
day_num : 0=lundi,1=mardi etc
mstarttime : morning start time
mstoptime : morning stop time
astarttime : afternoon start time
astoptime : afternoon stop time
workday : prévisionnel .... non utilisé
next_day : prévisionnel .... non utilisé
L'import du dump fait sauter les commentaires des routines .... Voilà
qui est signalé et bon à savoir!
Bien cordialement
--
Benjamin PREISS
*** /var/www/glpi/inc/commondbtm.class.php 2008-05-21 17:49:46.402289795
+0200
--- glpi/inc/commondbtm.class.php 2008-05-20 17:48:10.753926549 +0200
***************
*** 36,42 ****
var $type=-1;
var $dohistory=false;
-
function CommonDBTM () {
}
--- 36,41 ----
***************
*** 47,61 ****
// Make new database object and fill variables
global $db;
if (empty($ID)) return false;
! // Start mod benp ------------------------------------
! $date_calc="";
! if (strcmp($this->table,"glpi_tracking")==0) {
!
$date_calc=",sec_to_time(ben_hd_realcalc(date,closedate)) as \"realdate_ng\"";
! }
! //$query = "SELECT * FROM ".$this->table." WHERE (ID = '$ID')";
! $query = "SELECT *".$date_calc." FROM ".$this->table." WHERE
(ID = '$ID')";
! //echo $query." ";
! // Stop mod benp -------------------------------------
if ($result = $db->query($query)) {
if ($db->numrows($result)==1){
--- 46,53 ----
// Make new database object and fill variables
global $db;
if (empty($ID)) return false;
!
! $query = "SELECT * FROM ".$this->table." WHERE (ID = '$ID')";
if ($result = $db->query($query)) {
if ($db->numrows($result)==1){
-- MySQL dump 10.11
--
-- Host: localhost Database: glpi
-- ------------------------------------------------------
-- Server version 5.0.51a-3ubuntu5.1
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `ben_hd_dates`
--
DROP TABLE IF EXISTS `ben_hd_dates`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ben_hd_dates` (
`id` int(10) unsigned NOT NULL auto_increment,
`date` date NOT NULL,
`comment` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `ben_hd_dates`
--
LOCK TABLES `ben_hd_dates` WRITE;
/*!40000 ALTER TABLE `ben_hd_dates` DISABLE KEYS */;
INSERT INTO `ben_hd_dates` VALUES (5,'2008-01-01','jour de
l\'an'),(6,'2008-03-23','Pâques'),(7,'2008-03-24','lundi de
Pâques'),(8,'2008-05-01','Fête du
travail'),(9,'2008-05-08','1945'),(10,'2008-05-09','RTT2'),(11,'2008-05-12','Pentecôte'),(12,'2008-07-14','Fête
Nationnale'),(13,'2008-08-15','Assomption'),(14,'2008-11-01','Toussasint'),(15,'2008-11-11','1918'),(16,'2008-12-25','Noël'),(17,'2008-12-26','Noël_bis');
/*!40000 ALTER TABLE `ben_hd_dates` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ben_hd_mod`
--
DROP TABLE IF EXISTS `ben_hd_mod`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ben_hd_mod` (
`ID` int(10) unsigned NOT NULL auto_increment,
`day_num` tinyint(3) unsigned NOT NULL,
`mstarttime` time default NULL,
`mstoptime` time default NULL,
`astarttime` time default NULL,
`astoptime` time default NULL,
`workday` bit(1) NOT NULL,
`next_day` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `ben_hd_mod`
--
LOCK TABLES `ben_hd_mod` WRITE;
/*!40000 ALTER TABLE `ben_hd_mod` DISABLE KEYS */;
INSERT INTO `ben_hd_mod` VALUES
(1,0,'08:00:00','12:30:00','13:30:00','18:00:00','',3),(2,1,'08:00:00','12:30:00','13:30:00','18:00:00','',4),(3,2,'08:00:00','12:30:00','13:30:00','18:00:00','',5),(4,3,'08:00:00','12:30:00','13:30:00','18:00:00','',6),(5,4,'08:00:00','12:30:00','13:30:00','16:00:00','',7),(6,5,'00:00:00','00:00:00','00:00:00','00:00:00','\0',1),(7,6,'00:00:00','00:00:00','00:00:00','00:00:00','\0',2);
/*!40000 ALTER TABLE `ben_hd_mod` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping routines for database 'glpi'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `Ben_hd_DateCalc` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 [EMAIL PROTECTED]/ /*!50003 FUNCTION
`Ben_hd_DateCalc`(odate datetime, fdate datetime) RETURNS int(10) unsigned
BEGIN
#Déclaration des variables
DECLARE o_time TIME;
DECLARE f_time TIME;
DECLARE o_date DATE;
DECLARE f_date DATE;
#Déclaration des variables, résultat + iterators
DECLARE result INT unsigned;
DECLARE jours_entiers INTEGER unsigned;
DECLARE i TINYINT;
DECLARE j TINYINT;
#Si date+0 = 0 le ticket est ouvert -> calcul jusqu'Ã maintenant
if (fdate+0=0) then
Set fdate = current_timestamp;
end if;
#refuse une horaire d'ouverture > fermeture (evite les résultats négatifs)
if fdate < odate then
return null;
end if;
#Séparation ouverture/fermeture en heure-date/ouverture-fermeture
Set o_date = date(odate);
Set o_time = time(odate);
Set f_date = date(fdate);
Set f_time = time(fdate);
#Initialisation du résultat
Set result = 0;
#Si hors plage, recalage dans la plage pour l'ouverture ...
if o_time < (SELECT b.`mstarttime` FROM ben_hd_mod b where
b.`day_num`=weekday(o_date)) then
Set o_time = (SELECT b.`mstarttime` FROM ben_hd_mod b where
b.`day_num`=weekday(o_date));
elseif (o_time > (SELECT b.`mstoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(o_date))) and
(o_time < (SELECT b.`astarttime` FROM ben_hd_mod b where
b.`day_num`=weekday(o_date))) then
Set o_time = (SELECT b.`mstoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(o_date));
elseif o_time > (SELECT b.`astoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(o_date)) then
Set o_time = (SELECT b.`astoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(o_date));
end if;
#...Et la fermeture
if f_time < (SELECT b.`mstarttime` FROM ben_hd_mod b where
b.`day_num`=weekday(f_date)) then
Set f_time = (SELECT b.`mstarttime` FROM ben_hd_mod b where
b.`day_num`=weekday(f_date));
elseif (f_time > (SELECT b.`mstoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(f_date))) and
(f_time < (SELECT b.`astarttime` FROM ben_hd_mod b where
b.`day_num`=weekday(f_date))) then
Set f_time = (SELECT b.`mstoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(f_date));
elseif f_time > (SELECT b.`astoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(f_date)) then
Set f_time = (SELECT b.`astoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(f_date));
end if;
#Le calcul sur jours entiers : pas nécessaire si le nombre de jours entre
ouverture fermeture/ouverture <=2
# clacul des j : j1_o-{j-j-j-j-j-j-j-j-j-j}-jn_f
If datediff(f_date,o_date) >= 2 then
Set jours_entiers = datediff(f_date,o_date)-1;
#Si une semaine entière peut être calculée:
if truncate(jours_entiers/7,0)>=1 then
Set i = 0;
while i <= 6 do
Set result = result +
time_to_sec(timediff((SELECT b.`mstoptime` FROM ben_hd_mod
b where b.`day_num`=i),
(SELECT b.`mstarttime` FROM
ben_hd_mod b where b.`day_num`=i))) +
time_to_sec(timediff((SELECT b.`astoptime` FROM ben_hd_mod
b where b.`day_num`=i),
(SELECT b.`astarttime` FROM
ben_hd_mod b where b.`day_num`=i)));
Set i = i+1;
end while;
end if;
#Si plusieurs semaines peuvent en être déduites: (Rq si semaine <1: *0,
si semaine =1: *1, si semaine =n: *n)
Set result = result * truncate(jours_entiers/7,0);
#Calcul des jours restants:
if (jours_entiers%7) > 0 then
Set j=0;
Set i = weekday(o_date)+1;
while j <= (jours_entiers%7)-1 do
Set result = result +
time_to_sec(timediff((SELECT b.`mstoptime` FROM
ben_hd_mod b where b.`day_num`=i%7),
(SELECT b.`mstarttime` FROM
ben_hd_mod b where b.`day_num`=i%7))) +
time_to_sec(timediff((SELECT b.`astoptime` FROM
ben_hd_mod b where b.`day_num`=i%7),
(SELECT b.`astarttime` FROM
ben_hd_mod b where b.`day_num`=i%7)));
Set i = i+1;
Set j = j+1;
end while;
end if;
end if;
#Calcul des jours {j1_o}-j-j-j-j-j-j-j-j-j-j-{jn_f}
#Si ouverture/fermeture sur deux jours différents
If datediff(f_date,o_date) >0 then
Set result = result +
time_to_sec(timediff((SELECT b.`astoptime` FROM ben_hd_mod b
where b.`day_num`=weekday(o_date)),
o_time));
#Si ouveture le matin: retrancher la pause:
If o_time < (SELECT b.`astarttime` FROM ben_hd_mod b where
b.`day_num`=weekday(o_date)) then
Set result = result -
time_to_sec(timediff((SELECT b.`astarttime` FROM ben_hd_mod
b where b.`day_num`=weekday(o_date)),
(SELECT b.`mstoptime` FROM ben_hd_mod b
where b.`day_num`=weekday(o_date))));
end if;
Set result = result +
time_to_sec(timediff(f_time,
(SELECT b.`mstarttime` FROM ben_hd_mod b
where b.`day_num`=weekday(f_date))));
#Si fermeture le soir : retrancher la pause:
If f_time > (SELECT b.`mstoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(f_date)) then
Set result = result -
time_to_sec(timediff((SELECT b.`astarttime` FROM ben_hd_mod
b where b.`day_num`=weekday(f_date)),
(SELECT b.`mstoptime` FROM ben_hd_mod b
where b.`day_num`=weekday(f_date))));
end if;
else
#Ouverture fermeture dans la journée:
Set result = time_to_sec(timediff(f_time,o_time));
#Si ouvert le matin et fermé le soir, retrancher la pause:
if o_time <= (SELECT b.`mstoptime` FROM ben_hd_mod b where
b.`day_num`=weekday(o_date)) and
f_time >= (SELECT b.`astarttime` FROM ben_hd_mod b where
b.`day_num`=weekday(f_date)) then
Set result = result -
time_to_sec(timediff((SELECT b.`astarttime` FROM ben_hd_mod
b where b.`day_num`=weekday(f_date)),
(SELECT b.`mstoptime` FROM ben_hd_mod b
where b.`day_num`=weekday(o_date))));
end if;
end if;
#Si ouverture - fermeture hors plage, mais dans la même plage :
If result = 0 then
#Dans mon cas ON NE TRAVAIL PAS!
return 0;
#sinon, un timediff(ftime,otime) retournera le résultat éxact ...
else
#retourne le résultat en seconde dans la limite du INT UNSIGNED
return result;
end if;
END */;;
/*!50003 SET SESSION [EMAIL PROTECTED]/;;
/*!50003 DROP FUNCTION IF EXISTS `ben_hd_realcalc` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 [EMAIL PROTECTED]/ /*!50003 FUNCTION
`ben_hd_realcalc`(odate datetime, fdate datetime) RETURNS int(10) unsigned
BEGIN
#Déclaration des variables
DECLARE result int unsigned;
DECLARE ferie int unsigned;
#Initialisation
Set result=0;
Set ferie=0;
#Si date+0 = 0 le ticket est ouvert -> calcul jusqu'Ã maintenant
if fdate+0=0 then
Set fdate = current_timestamp;
end if;
#refuse une horaire d'ouverture > fermeture (evite les résultats négatifs)
if fdate <= odate then
return null;
end if;
#Calcul de la plage entière
Set result = (select ben_hd_datecalc(odate,fdate));
#Calcul de la somme des palges des jours fériés
Set ferie = (select sum(ben_hd_datecalc(concat(b.date," 00:00:00"),
concat(b.date," 23:59:59")))
from ben_hd_dates b
where b.date between odate and fdate);
#Si pas de jours fériés dans la plage
if isnull(ferie) then
Set ferie=0;
end if;
#Jours totaux - fériés
#retourne le résultat en seconde dans la limite du INT UNSIGNED
Set result = result - ferie;
return result;
END */;;
/*!50003 SET SESSION [EMAIL PROTECTED]/;;
DELIMITER ;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
-- Dump completed on 2008-05-22 11:27:49
*** /var/www/glpi/inc/tracking.function.php 2008-05-21 12:47:56.270288617
+0200
--- glpi/inc/tracking.function.php 2008-05-20 17:48:08.953721378 +0200
***************
*** 449,471 ****
echo
displaySearchItem($output_type,$first_col,$item_num,$row_num,0,$align);
-
- // Start Ben_Mod_CalcDate
---------------------------------------------------------
// Second column
$second_col="";
if (!ereg("old_",$job->fields["status"]))
{
$second_col.="<small>".$lang["joblist"][11].":";
if ($output_type==HTML_OUTPUT) $second_col.="<br>";
! $second_col.=
" ".convDateTime($job->fields["date"]);
!
! // Mod
benp------------------------------------------------
! $second_col.="<br>";
! $second_col.="<font Color=\"#009966\"> Ouvert depuis :
";
! if ($output_type==HTML_OUTPUT) $second_col.="<br>";
!
$second_col.=" <b>".$job->fields["realdate_ng"]."</b></font>";
! // mod
benp------------------------------------------------
! $second_col.="</small>";
}
else
{
--- 449,461 ----
echo
displaySearchItem($output_type,$first_col,$item_num,$row_num,0,$align);
// Second column
$second_col="";
if (!ereg("old_",$job->fields["status"]))
{
$second_col.="<small>".$lang["joblist"][11].":";
if ($output_type==HTML_OUTPUT) $second_col.="<br>";
! $second_col.=
" ".convDateTime($job->fields["date"])."</small>";
}
else
{
***************
*** 477,492 ****
if ($output_type==HTML_OUTPUT) $second_col.="<br>";
$second_col.=" ".convDateTime($job->fields["closedate"])."</i>";
$second_col.="<br>";
! //if ($job->fields["realtime"]>0)
$second_col.=$lang["job"][20].": ";
! $second_col.="<font Color=\"#FF0000\"> Temps de
résolution :";
if ($output_type==HTML_OUTPUT) $second_col.="<br>";
! // Mod
benp------------------------------------------------
!
$second_col.=" <b>".$job->fields["realdate_ng"]."</b></font>";
!
//$second_col.=" ".getRealtime($job->fields["realtime"]);
! // Mod
benp------------------------------------------------
$second_col.="</small>";
}
! // End Ben_Mod_CalcDate
---------------------------------------------------------
echo
displaySearchItem($output_type,$second_col,$item_num,$row_num,0,$align."
width=130");
// Third Column
--- 467,478 ----
if ($output_type==HTML_OUTPUT) $second_col.="<br>";
$second_col.=" ".convDateTime($job->fields["closedate"])."</i>";
$second_col.="<br>";
! if ($job->fields["realtime"]>0)
$second_col.=$lang["job"][20].": ";
if ($output_type==HTML_OUTPUT) $second_col.="<br>";
!
$second_col.=" ".getRealtime($job->fields["realtime"]);
$second_col.="</small>";
}
!
echo
displaySearchItem($output_type,$second_col,$item_num,$row_num,0,$align."
width=130");
// Third Column
***************
*** 553,561 ****
$sixth_col.="
(".$job->fields["computer"].")";
$sixth_col.="</strong>";
}
- // Mod Ben ---------------------------------------------
- $sixth_col.="<br><small>".$m->obj->fields["contact"]."</small>";
- // Mod Ben ---------------------------------------------
echo
displaySearchItem($output_type,$sixth_col,$item_num,$row_num,$deleted,$align);
// Seventh column
--- 539,544 ----
***************
*** 1333,1338 ****
--- 1316,1322 ----
commonTrackingListHeader($output_type,$target,$parameters2,$sort,$order);
+
while ($i < $numrows && $i<$end_display){
$ID = $db->result($result, $i, "ID");
showJobShort($ID,
$showfollowups,$output_type,$i-$start+1);
_______________________________________________
Glpi-dev mailing list
Glpi-dev@gna.org
https://mail.gna.org/listinfo/glpi-dev
