taskstats.class.php 6.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219
  1. <?php
  2. /* Copyright (C) 2014-2015 Florian HENRY <florian.henry@open-concept.pro>
  3. *
  4. * This program is free software; you can redistribute it and/or modify
  5. * it under the terms of the GNU General Public License as published by
  6. * the Free Software Foundation; either version 3 of the License, or
  7. * (at your option) any later version.
  8. *
  9. * This program is distributed in the hope that it will be useful,
  10. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  12. * GNU General Public License for more details.
  13. *
  14. * You should have received a copy of the GNU General Public License
  15. * along with this program. If not, see <https://www.gnu.org/licenses/>.
  16. */
  17. include_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
  18. include_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
  19. /**
  20. * Class to manage statistics on project tasks
  21. */
  22. class TaskStats extends Stats
  23. {
  24. private $project;
  25. public $userid;
  26. public $socid;
  27. /**
  28. * @var int priority
  29. */
  30. public $priority;
  31. /**
  32. * Constructor of the class
  33. *
  34. * @param DoliDb $db Database handler
  35. */
  36. public function __construct($db)
  37. {
  38. $this->db = $db;
  39. }
  40. /**
  41. * Return all tasks grouped by status.
  42. *
  43. * @param int $limit Limit results
  44. * @return array|int Array with value or -1 if error
  45. * @throws Exception
  46. */
  47. public function getAllTaskByStatus($limit = 5)
  48. {
  49. global $user, $langs;
  50. $sql = "SELECT";
  51. $sql .= " COUNT(t.rowid), t.priority";
  52. $sql .= " FROM ".MAIN_DB_PREFIX."projet_task as t INNER JOIN ".MAIN_DB_PREFIX."projet as p ON p.rowid = t.fk_projet";
  53. if (!$user->hasRight('societe', 'client', 'voir') && !$user->socid) {
  54. $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as sc ON sc.fk_soc=p.fk_soc AND sc.fk_user=".((int) $user->id);
  55. }
  56. $sql .= $this->buildWhere();
  57. //$sql .= " AND t.fk_statut <> 0"; // We want historic also, so all task not draft
  58. $sql .= " GROUP BY t.priority";
  59. $result = array();
  60. dol_syslog(get_class($this).'::'.__METHOD__, LOG_DEBUG);
  61. $resql = $this->db->query($sql);
  62. if ($resql) {
  63. $num = $this->db->num_rows($resql);
  64. $i = 0;
  65. $other = 0;
  66. while ($i < $num) {
  67. $row = $this->db->fetch_row($resql);
  68. if ($i < $limit || $num == $limit) {
  69. $result[$i] = array(
  70. $row[1],
  71. $row[0]
  72. );
  73. } else {
  74. $other += $row[1];
  75. }
  76. $i++;
  77. }
  78. if ($num > $limit) {
  79. $result[$i] = array(
  80. $langs->transnoentitiesnoconv("Other"),
  81. $other
  82. );
  83. }
  84. $this->db->free($resql);
  85. } else {
  86. $this->error = "Error ".$this->db->lasterror();
  87. dol_syslog(get_class($this).'::'.__METHOD__.' '.$this->error, LOG_ERR);
  88. return -1;
  89. }
  90. return $result;
  91. }
  92. /**
  93. * Return count, and sum of products
  94. *
  95. * @return array of values
  96. */
  97. public function getAllByYear()
  98. {
  99. global $user;
  100. $sql = "SELECT date_format(t.datec,'%Y') as year, COUNT(t.rowid) as nb";
  101. $sql .= " FROM ".MAIN_DB_PREFIX."projet_task as t INNER JOIN ".MAIN_DB_PREFIX."projet as p ON p.rowid = t.fk_projet";
  102. if (!$user->hasRight('societe', 'client', 'voir') && !$user->socid) {
  103. $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as sc ON sc.fk_soc=p.fk_soc AND sc.fk_user=".((int) $user->id);
  104. }
  105. $sql .= $this->buildWhere();
  106. $sql .= " GROUP BY year";
  107. $sql .= $this->db->order('year', 'DESC');
  108. return $this->_getAllByYear($sql);
  109. }
  110. /**
  111. * Build the where part
  112. *
  113. * @return string
  114. */
  115. public function buildWhere()
  116. {
  117. $sqlwhere_str = '';
  118. $sqlwhere = array();
  119. $sqlwhere[] = ' t.entity IN ('.getEntity('project').')';
  120. if (!empty($this->userid)) {
  121. $sqlwhere[] = ' t.fk_user_resp = '.((int) $this->userid);
  122. }
  123. // Forced filter on socid is similar to forced filter on project. TODO Use project assignement to allow to not use filter on project
  124. if (!empty($this->socid)) {
  125. $sqlwhere[] = ' p.fk_soc = '.((int) $this->socid); // Link on thirdparty is on project, not on task
  126. }
  127. if (!empty($this->year) && empty($this->month)) {
  128. $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))."'";
  129. }
  130. if (!empty($this->year) && !empty($this->month)) {
  131. $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))."'";
  132. }
  133. if (!empty($this->priority)) {
  134. $sqlwhere[] = " t.priority IN (".$this->db->sanitize($this->priority, 1).")";
  135. }
  136. if (count($sqlwhere) > 0) {
  137. $sqlwhere_str = ' WHERE '.implode(' AND ', $sqlwhere);
  138. }
  139. return $sqlwhere_str;
  140. }
  141. /**
  142. * Return Task number by month for a year
  143. *
  144. * @param int $year Year to scan
  145. * @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
  146. * @return array Array of values
  147. */
  148. public function getNbByMonth($year, $format = 0)
  149. {
  150. global $user;
  151. $this->year = $year;
  152. $sql = "SELECT date_format(t.datec,'%m') as dm, COUNT(t.rowid) as nb";
  153. $sql .= " FROM ".MAIN_DB_PREFIX."projet_task as t INNER JOIN ".MAIN_DB_PREFIX."projet as p ON p.rowid = t.fk_projet";
  154. if (!$user->hasRight('societe', 'client', 'voir') && !$user->socid) {
  155. $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as sc ON sc.fk_soc=p.fk_soc AND sc.fk_user=".((int) $user->id);
  156. }
  157. $sql .= $this->buildWhere();
  158. $sql .= " GROUP BY dm";
  159. $sql .= $this->db->order('dm', 'DESC');
  160. $res = $this->_getNbByMonth($year, $sql, $format);
  161. // var_dump($res);print '<br>';
  162. return $res;
  163. }
  164. /**
  165. * Return the Task amount by month for a year
  166. *
  167. * @param int $year Year to scan
  168. * @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
  169. * @return array Array with amount by month
  170. */
  171. public function getAmountByMonth($year, $format = 0)
  172. {
  173. // Return an empty array at the moment because task has no amount
  174. return array();
  175. }
  176. /**
  177. * Return average of entity by month
  178. * @param int $year year number
  179. * @return array array of values
  180. */
  181. protected function getAverageByMonth($year)
  182. {
  183. $sql = "SELECT date_format(datef,'%m') as dm, AVG(f.".$this->field.")";
  184. $sql .= " FROM ".$this->from;
  185. $sql .= " WHERE f.datef BETWEEN '".$this->db->idate(dol_get_first_day($year))."' AND '".$this->db->idate(dol_get_last_day($year))."'";
  186. $sql .= " AND ".$this->where;
  187. $sql .= " GROUP BY dm";
  188. $sql .= $this->db->order('dm', 'DESC');
  189. return $this->_getAverageByMonth($year, $sql);
  190. }
  191. }