You are not logged in.
Pages: 1
Hello GLPI Team,
GLPI 0.85.4
I want to get the average of my projects automatically (In the main page of a project, the field percent done), from the tasks added to my project, for that I have the sql query, and I think, I have to put it in the project.class.php file, but I dont know how to do it, my knowledge in php are very poor.
Could somebody help me, telling me how show this consult in the main page?
Here the code I want to introduce
"SELECT SUM( glpi_projecttasks.percent_done /100 * glpi_projecttasks.planned_duration ) / SUM( glpi_projecttasks.planned_duration ) *100
FROM glpi_projecttasks, glpi_projects
WHERE glpi_projects.id = glpi_projecttasks.projects_id
AND `glpi_projecttasks`.`projects_id` = '$ID' ";
Thanks a lot,
Claudia
Offline
Hi Claudia,
I'm pretty amateur with PHP myself, but I may be able to help you.
Try this:
1) Make a copy of your project.class.php file.
2) Edit project.class.php and at line 797, insert this:
function funcAutoPercentDone($ID) {
global $CFG_GLPI, $DB;
$vSQL="SELECT SUM( glpi_projecttasks.percent_done /100 * glpi_projecttasks.planned_duration ) / SUM( glpi_projecttasks.planned_duration ) *100 AS 'PERCENTDONE'
FROM glpi_projecttasks, glpi_projects
WHERE glpi_projects.id = glpi_projecttasks.projects_id
AND `glpi_projecttasks`.`projects_id` = ".$ID;
$vResult = $DB->query($vSQL);
$vPercentDone = $DB->fetch_assoc($vResult);
$vQueryResults = $vPercentDone['PERCENTDONE'];
return round($vQueryResults, 1)."%";
}
3) Next go down to line to 868 and comment out (or remove) the following:
Dropdown::showNumber("percent_done", array('value' => $this->fields['percent_done'],
'min' => 0,
'max' => 100,
'step' => 5,
'unit' => '%'));
4) Put the following it its place:
echo funcAutoPercentDone($ID);
This works on my v0.85.4 install, but the result of your SQL doesn't seem to return what I would expect. It looks like you know what you're doing in SQL though, so I'll leave that with you
If you want a copy of my project.class.php with these changes, just reply to this thread.
Hope that helps.
Elliot
1500+ Computers / 1100+ users
Offline
Thank you very much elliot!
I design a trigger and only change the presentation in the php, I dont know what would be most usefull and better for the performance of the glpi, this is my trigger, I hope any of two ways can be used for improve the future versions of glpi.
This is my trigger:
delimiter $$
CREATE TRIGGER before_glpi_projecttasks_update
AFTER UPDATE ON glpi_projecttasks
FOR EACH ROW BEGIN
declare cc int;
select (sum(ifnull(percent_done,0)/100 * ifnull(planned_duration,0))/sum(ifnull(planned_duration,0)))*100
into cc
from glpi_projecttasks
where projects_id=OLD.projects_id;
update glpi_projects
set percent_done= cc
where id=OLD.projects_id;
END$$
delimiter ;
With this two options I close this topic
[resolved]
Best regards,
Claudia
Offline
For anybody following this thread, I decided to alter Claudia's SQL to suit our own needs.
I am using the following to provide a project done % based on the quantity of project tasks and their respective %done:
$vSQL="SELECT SUM( glpi_projecttasks.percent_done ) / ( SELECT COUNT(*) FROM glpi_projecttasks WHERE projects_id = ".$ID." ) AS 'PERCENTDONE'
FROM glpi_projecttasks
WHERE glpi_projecttasks.projects_id = ".$ID;
Last edited by elliot01 (2015-08-09 22:34:45)
1500+ Computers / 1100+ users
Offline
Funtion should be changed to the following, to handle new projects, and projects without any sub-tasks:
function funcAutoPercentDone($ID) {
global $CFG_GLPI, $DB;
$vSQL="SELECT SUM( glpi_projecttasks.percent_done ) / ( SELECT COUNT(*) FROM glpi_projecttasks WHERE projects_id = ".$ID." ) AS 'PERCENTDONE'
FROM glpi_projecttasks
WHERE glpi_projecttasks.projects_id = ".$ID;
if ($vResult = $DB->query($vSQL) ) {
$vPercentDone = $DB->fetch_assoc($vResult);
$vQueryResults = $vPercentDone['PERCENTDONE'];
}
else { $vQueryResults = 0; }
return round($vQueryResults, 1)."%";
}
Last edited by elliot01 (2015-08-12 13:50:35)
1500+ Computers / 1100+ users
Offline
Pages: 1