123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219 |
- <?php
- /* Copyright (C) 2014-2015 Florian HENRY <florian.henry@open-concept.pro>
- *
- * This program is free software; you can redistribute it and/or modify
- * it under the terms of the GNU General Public License as published by
- * the Free Software Foundation; either version 3 of the License, or
- * (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful,
- * but WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
- * GNU General Public License for more details.
- *
- * You should have received a copy of the GNU General Public License
- * along with this program. If not, see <https://www.gnu.org/licenses/>.
- */
- include_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
- include_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
- /**
- * Class to manage statistics on project tasks
- */
- class TaskStats extends Stats
- {
- private $project;
- public $userid;
- public $socid;
- /**
- * @var int priority
- */
- public $priority;
- /**
- * Constructor of the class
- *
- * @param DoliDb $db Database handler
- */
- public function __construct($db)
- {
- $this->db = $db;
- }
- /**
- * Return all tasks grouped by status.
- *
- * @param int $limit Limit results
- * @return array|int Array with value or -1 if error
- * @throws Exception
- */
- public function getAllTaskByStatus($limit = 5)
- {
- global $user, $langs;
- $sql = "SELECT";
- $sql .= " COUNT(t.rowid), t.priority";
- $sql .= " FROM ".MAIN_DB_PREFIX."projet_task as t INNER JOIN ".MAIN_DB_PREFIX."projet as p ON p.rowid = t.fk_projet";
- if (!$user->hasRight('societe', 'client', 'voir') && !$user->socid) {
- $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as sc ON sc.fk_soc=p.fk_soc AND sc.fk_user=".((int) $user->id);
- }
- $sql .= $this->buildWhere();
- //$sql .= " AND t.fk_statut <> 0"; // We want historic also, so all task not draft
- $sql .= " GROUP BY t.priority";
- $result = array();
- dol_syslog(get_class($this).'::'.__METHOD__, LOG_DEBUG);
- $resql = $this->db->query($sql);
- if ($resql) {
- $num = $this->db->num_rows($resql);
- $i = 0;
- $other = 0;
- while ($i < $num) {
- $row = $this->db->fetch_row($resql);
- if ($i < $limit || $num == $limit) {
- $result[$i] = array(
- $row[1],
- $row[0]
- );
- } else {
- $other += $row[1];
- }
- $i++;
- }
- if ($num > $limit) {
- $result[$i] = array(
- $langs->transnoentitiesnoconv("Other"),
- $other
- );
- }
- $this->db->free($resql);
- } else {
- $this->error = "Error ".$this->db->lasterror();
- dol_syslog(get_class($this).'::'.__METHOD__.' '.$this->error, LOG_ERR);
- return -1;
- }
- return $result;
- }
- /**
- * Return count, and sum of products
- *
- * @return array of values
- */
- public function getAllByYear()
- {
- global $user;
- $sql = "SELECT date_format(t.datec,'%Y') as year, COUNT(t.rowid) as nb";
- $sql .= " FROM ".MAIN_DB_PREFIX."projet_task as t INNER JOIN ".MAIN_DB_PREFIX."projet as p ON p.rowid = t.fk_projet";
- if (!$user->hasRight('societe', 'client', 'voir') && !$user->socid) {
- $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as sc ON sc.fk_soc=p.fk_soc AND sc.fk_user=".((int) $user->id);
- }
- $sql .= $this->buildWhere();
- $sql .= " GROUP BY year";
- $sql .= $this->db->order('year', 'DESC');
- return $this->_getAllByYear($sql);
- }
- /**
- * Build the where part
- *
- * @return string
- */
- public function buildWhere()
- {
- $sqlwhere_str = '';
- $sqlwhere = array();
- $sqlwhere[] = ' t.entity IN ('.getEntity('project').')';
- if (!empty($this->userid)) {
- $sqlwhere[] = ' t.fk_user_resp = '.((int) $this->userid);
- }
- // Forced filter on socid is similar to forced filter on project. TODO Use project assignement to allow to not use filter on project
- if (!empty($this->socid)) {
- $sqlwhere[] = ' p.fk_soc = '.((int) $this->socid); // Link on thirdparty is on project, not on task
- }
- if (!empty($this->year) && empty($this->month)) {
- $sqlwhere[] = " t.datec BETWEEN '".$this->db->idate(dol_get_first_day($this->year, 1))."' AND '".$this->db->idate(dol_get_last_day($this->year, 12))."'";
- }
- if (!empty($this->year) && !empty($this->month)) {
- $sqlwhere[] = " t.datec BETWEEN '".$this->db->idate(dol_get_first_day($this->year, $this->month))."' AND '".$this->db->idate(dol_get_last_day($this->year, $this->month))."'";
- }
- if (!empty($this->priority)) {
- $sqlwhere[] = " t.priority IN (".$this->db->sanitize($this->priority, 1).")";
- }
- if (count($sqlwhere) > 0) {
- $sqlwhere_str = ' WHERE '.implode(' AND ', $sqlwhere);
- }
- return $sqlwhere_str;
- }
- /**
- * Return Task number by month for a year
- *
- * @param int $year Year to scan
- * @param int $format 0=Label of abscissa is a translated text, 1=Label of abscissa is month number, 2=Label of abscissa is first letter of month
- * @return array Array of values
- */
- public function getNbByMonth($year, $format = 0)
- {
- global $user;
- $this->year = $year;
- $sql = "SELECT date_format(t.datec,'%m') as dm, COUNT(t.rowid) as nb";
- $sql .= " FROM ".MAIN_DB_PREFIX."projet_task as t INNER JOIN ".MAIN_DB_PREFIX."projet as p ON p.rowid = t.fk_projet";
- if (!$user->hasRight('societe', 'client', 'voir') && !$user->socid) {
- $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as sc ON sc.fk_soc=p.fk_soc AND sc.fk_user=".((int) $user->id);
- }
- $sql .= $this->buildWhere();
- $sql .= " GROUP BY dm";
- $sql .= $this->db->order('dm', 'DESC');
- $res = $this->_getNbByMonth($year, $sql, $format);
- // var_dump($res);print '<br>';
- return $res;
- }
- /**
- * Return the Task amount by month for a year
- *
- * @param int $year Year to scan
- * @param int $format 0=Label of abscissa is a translated text, 1=Label of abscissa is month number, 2=Label of abscissa is first letter of month
- * @return array Array with amount by month
- */
- public function getAmountByMonth($year, $format = 0)
- {
- // Return an empty array at the moment because task has no amount
- return array();
- }
- /**
- * Return average of entity by month
- * @param int $year year number
- * @return array array of values
- */
- protected function getAverageByMonth($year)
- {
- $sql = "SELECT date_format(datef,'%m') as dm, AVG(f.".$this->field.")";
- $sql .= " FROM ".$this->from;
- $sql .= " WHERE f.datef BETWEEN '".$this->db->idate(dol_get_first_day($year))."' AND '".$this->db->idate(dol_get_last_day($year))."'";
- $sql .= " AND ".$this->where;
- $sql .= " GROUP BY dm";
- $sql .= $this->db->order('dm', 'DESC');
- return $this->_getAverageByMonth($year, $sql);
- }
- }
|