Forum GLPI-Project

claugiral

Member

Registered: 2015-04-13

Posts: 55

Auto percent done projects

Hello GLPI Team,

GLPI 9.5.6

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, but the function added in GLPI is averaging all the percent of the task without taking care of the duration of this task what is not usefull for me.

I would like to change de code to do this, a few years ago I made this trigger and solve the problem:

This was the 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 ;

Now I see the actual code, and I dont understand it because dont use sql sintax,

Actual code:
/**
     * Update the specified project's percent_done based on the percent_done of subprojects and tasks.
     * This function indirectly updates the percent done for all parents if they are set to automatically update.
     * @since 9.5.0
     * @return boolean False if the specified project is not set to automatically update the percent done.
     */
    public static function recalculatePercentDone($ID)
    {
        global $DB;

        $project = new self();
        $project->getFromDB($ID);
        if (!$project->fields['auto_percent_done']) {
            return false;
        }

        $query1 = new \QuerySubQuery([
            'SELECT' => [
                'percent_done'
            ],
            'FROM'   => self::getTable(),
            'WHERE'  => [
                'projects_id'  => $ID,
                'is_deleted'   => 0
            ]
        ]);
        $query2 = new \QuerySubQuery([
            'SELECT' => [
                'percent_done'
            ],
            'FROM'   => ProjectTask::getTable(),
            'WHERE'  => [
                'projects_id' => $ID
            ]
        ]);
        $union = new QueryUnion([$query1, $query2], false, 'all_items');
        $iterator = $DB->request([
            'SELECT' => [
                new QueryExpression('CAST(AVG(' . $DB->quoteName('percent_done') . ') AS UNSIGNED) AS percent_done')
            ],
            'FROM'   => $union
        ]);

        if ($iterator->count()) {
            $avg = $iterator->current()['percent_done'];
            $percent_done = is_null($avg) ? 0 : $avg;
        } else {
            $percent_done = 0;
        }

        $project->update([
            'id'           => $ID,
            'percent_done' => $percent_done
        ]);
        return true;
    }

Somebody could tell me how to change it to do what I want?

Thanks a lot,

Claudia