dolibarr 21.0.0-alpha
expensereportstats.class.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2003 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3 * Copyright (c) 2005-2008 Laurent Destailleur <eldy@users.sourceforge.net>
4 * Copyright (C) 2005-2009 Regis Houssin <regis.houssin@inodbox.com>
5 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
6 *
7 * This program is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program. If not, see <https://www.gnu.org/licenses/>.
19 */
20
26require_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
27require_once DOL_DOCUMENT_ROOT.'/expensereport/class/expensereport.class.php';
28
33{
37 public $table_element;
38
42 public $socid;
43
47 public $userid;
48
52 public $from;
53
57 public $field;
58
62 public $where;
63
67 private $datetouse = 'date_valid';
68
69
78 public function __construct($db, $socid = 0, $userid = 0)
79 {
80 global $conf, $user;
81
82 $this->db = $db;
83 $this->socid = $socid;
84 $this->userid = $userid;
85
86 $object = new ExpenseReport($this->db);
87 $this->from = MAIN_DB_PREFIX.$object->table_element." as e";
88 $this->field = 'total_ht';
89
90 //$this->where = " e.fk_statut > 0";
91 //$this->where.= " AND e.date_valid > '2000-01-01'"; // To filter only correct "valid date". If date is invalid, the group by on it will fails. Launch a repair.php if you have.
92 $this->where .= ' e.entity IN ('.getEntity('expensereport').')';
93
94 //$this->where.= " AND entity = ".$conf->entity;
95 if ($this->socid) {
96 $this->where .= " AND e.fk_soc = ".((int) $this->socid);
97 }
98
99 // Only me and subordinates
100 if (!$user->hasRight('expensereport', 'readall') && !$user->hasRight('expensereport', 'lire_tous')) {
101 $childids = $user->getAllChildIds();
102 $childids[] = $user->id;
103 $this->where .= " AND e.fk_user_author IN (".$this->db->sanitize(implode(',', $childids)).")";
104 }
105
106 if ($this->userid > 0) {
107 $this->where .= ' AND e.fk_user_author = '.((int) $this->userid);
108 }
109 }
110
111
117 public function getNbByYear()
118 {
119 $sql = "SELECT YEAR(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).") as dm, count(*)";
120 $sql .= " FROM ".$this->from;
121 $sql .= " GROUP BY dm DESC";
122 $sql .= " WHERE ".$this->where;
123
124 return $this->_getNbByYear($sql);
125 }
126
127
135 public function getNbByMonth($year, $format = 0)
136 {
137 $sql = "SELECT MONTH(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).") as dm, count(*)";
138 $sql .= " FROM ".$this->from;
139 $sql .= " WHERE YEAR(e.".$this->datetouse.") = ".((int) $year);
140 $sql .= " AND ".$this->where;
141 $sql .= " GROUP BY dm";
142 $sql .= $this->db->order('dm', 'DESC');
143
144 $res = $this->_getNbByMonth($year, $sql, $format);
145
146 return $res;
147 }
148
149
157 public function getAmountByMonth($year, $format = 0)
158 {
159 $sql = "SELECT date_format(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).",'%m') as dm, sum(".$this->field.")";
160 $sql .= " FROM ".$this->from;
161 $sql .= " WHERE date_format(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).",'%Y') = '".$this->db->escape($year)."'";
162 $sql .= " AND ".$this->where;
163 $sql .= " GROUP BY dm";
164 $sql .= $this->db->order('dm', 'DESC');
165
166 $res = $this->_getAmountByMonth($year, $sql, $format);
167
168 return $res;
169 }
170
177 public function getAverageByMonth($year)
178 {
179 $sql = "SELECT date_format(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).",'%m') as dm, avg(".$this->field.")";
180 $sql .= " FROM ".$this->from;
181 $sql .= " WHERE date_format(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).",'%Y') = '".$this->db->escape($year)."'";
182 $sql .= " AND ".$this->where;
183 $sql .= " GROUP BY dm";
184 $sql .= $this->db->order('dm', 'DESC');
185
186 return $this->_getAverageByMonth($year, $sql);
187 }
188
194 public function getAllByYear()
195 {
196 $sql = "SELECT date_format(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).",'%Y') as year, count(*) as nb, sum(".$this->field.") as total, avg(".$this->field.") as avg";
197 $sql .= " FROM ".$this->from;
198 $sql .= " WHERE ".$this->where;
199 $sql .= " GROUP BY year";
200 $sql .= $this->db->order('year', 'DESC');
201
202 return $this->_getAllByYear($sql);
203 }
204}
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition card.php:58
Class to manage Trips and Expenses.
Class to manage the statistics of the expensereports and expense notes.
getNbByMonth($year, $format=0)
Return the quantity of invoices per month for a given year.
getAverageByMonth($year)
Return average amount.
__construct($db, $socid=0, $userid=0)
Constructor.
getAmountByMonth($year, $format=0)
Renvoie le montant de facture par mois pour une annee donnee.
getNbByYear()
Return nb of expense report per year.
getAllByYear()
Return nb, total and average.
Parent class of statistics class.
_getAverageByMonth($year, $sql, $format=0)
Return the amount average par month for a given year.
_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...