dolibarr  19.0.0-dev
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 }
TicketStats\getNbByYear
getNbByYear()
Renvoie le nombre de tickets par annee.
Definition: ticketstats.class.php:81
Stats
Parent class of statistics class.
Definition: stats.class.php:30
Stats\_getNbByYear
_getNbByYear($sql)
Return nb of elements by year.
Definition: stats.class.php:348
Stats\_getAmountByMonth
_getAmountByMonth($year, $sql, $format=0)
Return the amount per month for a given year.
Definition: stats.class.php:491
TicketStats\getAllByYear
getAllByYear()
Return nb, total and average.
Definition: ticketstats.class.php:156
Stats\_getAllByYear
_getAllByYear($sql)
Return nb of elements, total amount and avg amount each year.
Definition: stats.class.php:377
TicketStats\__construct
__construct($db, $socid=0, $userid=0)
Constructor.
Definition: ticketstats.class.php:52
$sql
if(isModEnabled('facture') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $sql
Social contributions to pay.
Definition: index.php:746
TicketStats\getNbByMonth
getNbByMonth($year, $format=0)
Return the number of tickets per month for a given year.
Definition: ticketstats.class.php:98
TicketStats\getAmountByMonth
getAmountByMonth($year, $format=0)
Return th eamount of tickets for a month and a given year.
Definition: ticketstats.class.php:119
TicketStats\getAverageByMonth
getAverageByMonth($year)
Return average amount.
Definition: ticketstats.class.php:139
Stats\_getNbByMonth
_getNbByMonth($year, $sql, $format=0)
Renvoie le nombre de documents par mois pour une annee donnee Return number of documents per month fo...
Definition: stats.class.php:434
Ticket
Class to generate the form for creating a new ticket.
Definition: html.formticket.class.php:32
TicketStats
Classe permettant la gestion des stats des deplacements et notes de frais.
Definition: ticketstats.class.php:30
Stats\_getAverageByMonth
_getAverageByMonth($year, $sql, $format=0)
Renvoie le montant moyen par mois pour une annee donnee Return the amount average par month for a giv...
Definition: stats.class.php:549