dolibarr  17.0.4
ticketstats.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2016 Jean-François Ferry <hello@librethic.io>
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 
23 require_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
24 require_once 'ticket.class.php';
25 
26 
30 class TicketStats extends Stats
31 {
35  public $table_element;
36 
37  public $socid;
38  public $userid;
39 
40  public $from;
41  public $field;
42  public $where;
43 
52  public function __construct($db, $socid = 0, $userid = 0)
53  {
54  global $conf;
55 
56  $this->db = $db;
57  $this->socid = $socid;
58  $this->userid = $userid;
59 
60  $object = new Ticket($this->db);
61  $this->from = MAIN_DB_PREFIX.$object->table_element;
62  $this->field = 'timing';
63 
64  $this->where = " fk_statut > 0";
65  $this->where .= " AND entity = ".((int) $conf->entity);
66  if ($this->socid > 0) {
67  $this->where .= " AND fk_soc = ".((int) $this->socid);
68  }
69  if (is_array($this->userid) && count($this->userid) > 0) {
70  $this->where .= ' AND fk_user_create IN ('.$this->db->sanitize(join(',', $this->userid)).')';
71  } elseif ($this->userid > 0) {
72  $this->where .= " AND fk_user_create = ".((int) $this->userid);
73  }
74  }
75 
81  public function getNbByYear()
82  {
83  $sql = "SELECT YEAR(datec) as dm, count(*)";
84  $sql .= " FROM ".$this->from;
85  $sql .= " GROUP BY dm DESC";
86  $sql .= " WHERE ".$this->where;
87 
88  return $this->_getNbByYear($sql);
89  }
90 
98  public function getNbByMonth($year, $format = 0)
99  {
100  $sql = "SELECT MONTH(datec) as dm, count(*)";
101  $sql .= " FROM ".$this->from;
102  $sql .= " WHERE YEAR(datec) = ".((int) $year);
103  $sql .= " AND ".$this->where;
104  $sql .= " GROUP BY dm";
105  $sql .= $this->db->order('dm', 'DESC');
106 
107  $res = $this->_getNbByMonth($year, $sql, $format);
108  //var_dump($res);print '<br>';
109  return $res;
110  }
111 
119  public function getAmountByMonth($year, $format = 0)
120  {
121  $sql = "SELECT date_format(datec,'%m') as dm, sum(".$this->field.")";
122  $sql .= " FROM ".$this->from;
123  $sql .= " WHERE date_format(datec,'%Y') = '".$this->db->escape($year)."'";
124  $sql .= " AND ".$this->where;
125  $sql .= " GROUP BY dm";
126  $sql .= $this->db->order('dm', 'DESC');
127 
128  $res = $this->_getAmountByMonth($year, $sql, $format);
129  //var_dump($res);print '<br>';
130  return $res;
131  }
132 
139  public function getAverageByMonth($year)
140  {
141  $sql = "SELECT date_format(datec,'%m') as dm, avg(".$this->field.")";
142  $sql .= " FROM ".$this->from;
143  $sql .= " WHERE date_format(datec,'%Y') = '".$this->db->escape($year)."'";
144  $sql .= " AND ".$this->where;
145  $sql .= " GROUP BY dm";
146  $sql .= $this->db->order('dm', 'DESC');
147 
148  return $this->_getAverageByMonth($year, $sql);
149  }
150 
156  public function getAllByYear()
157  {
158  $sql = "SELECT date_format(datec,'%Y') as year, count(*) as nb, sum(".$this->field.") as total, avg(".$this->field.") as avg";
159  $sql .= " FROM ".$this->from;
160  $sql .= " WHERE ".$this->where;
161  $sql .= " GROUP BY year";
162  $sql .= $this->db->order('year', 'DESC');
163 
164  return $this->_getAllByYear($sql);
165  }
166 }
Parent class of statistics class.
Definition: stats.class.php:31
_getAverageByMonth($year, $sql, $format=0)
Renvoie le montant moyen par mois pour une annee donnee Return the amount average par month for a giv...
_getAmountByMonth($year, $sql, $format=0)
Return the amount per month for a given year.
_getNbByYear($sql)
Return nb of elements by year.
_getAllByYear($sql)
Return nb of elements, total amount and avg amount each year.
_getNbByMonth($year, $sql, $format=0)
Renvoie le nombre de documents par mois pour une annee donnee Return number of documents per month fo...
Classe permettant la gestion des stats des deplacements et notes de frais.
getAllByYear()
Return nb, total and average.
getNbByMonth($year, $format=0)
Return the number of tickets per month for a given year.
getNbByYear()
Renvoie le nombre de tickets par annee.
getAverageByMonth($year)
Return average amount.
getAmountByMonth($year, $format=0)
Return th eamount of tickets for a month and a given year.
__construct($db, $socid=0, $userid=0)
Constructor.
Class to generate the form for creating a new ticket.
$conf db
API class for accounts.
Definition: inc.php:41