dolibarr 21.0.0-beta
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 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
7 *
8 * This program is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 3 of the License, or
11 * (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with this program. If not, see <https://www.gnu.org/licenses/>.
20 */
21
27require_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
28require_once DOL_DOCUMENT_ROOT.'/expensereport/class/expensereport.class.php';
29
34{
38 public $table_element;
39
43 public $socid;
44
48 public $userid;
49
53 public $from;
54
58 public $field;
59
63 public $where;
64
68 private $datetouse = 'date_valid';
69
70
79 public function __construct($db, $socid = 0, $userid = 0)
80 {
81 global $conf, $user;
82
83 $this->db = $db;
84 $this->socid = $socid;
85 $this->userid = $userid;
86
87 $object = new ExpenseReport($this->db);
88 $this->from = MAIN_DB_PREFIX.$object->table_element." as e";
89 $this->field = 'total_ht';
90
91 //$this->where = " e.fk_statut > 0";
92 //$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.
93 $this->where .= ' e.entity IN ('.getEntity('expensereport').')';
94
95 //$this->where.= " AND entity = ".$conf->entity;
96 if ($this->socid) {
97 $this->where .= " AND e.fk_soc = ".((int) $this->socid);
98 }
99
100 // Only me and subordinates
101 if (!$user->hasRight('expensereport', 'readall') && !$user->hasRight('expensereport', 'lire_tous')) {
102 $childids = $user->getAllChildIds();
103 $childids[] = $user->id;
104 $this->where .= " AND e.fk_user_author IN (".$this->db->sanitize(implode(',', $childids)).")";
105 }
106
107 if ($this->userid > 0) {
108 $this->where .= ' AND e.fk_user_author = '.((int) $this->userid);
109 }
110 }
111
112
118 public function getNbByYear()
119 {
120 $sql = "SELECT YEAR(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).") as dm, count(*)";
121 $sql .= " FROM ".$this->from;
122 $sql .= " GROUP BY dm DESC";
123 $sql .= " WHERE ".$this->where;
124
125 return $this->_getNbByYear($sql);
126 }
127
128
136 public function getNbByMonth($year, $format = 0)
137 {
138 $sql = "SELECT MONTH(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).") as dm, count(*)";
139 $sql .= " FROM ".$this->from;
140 $sql .= " WHERE YEAR(e.".$this->datetouse.") = ".((int) $year);
141 $sql .= " AND ".$this->where;
142 $sql .= " GROUP BY dm";
143 $sql .= $this->db->order('dm', 'DESC');
144
145 $res = $this->_getNbByMonth($year, $sql, $format);
146
147 return $res;
148 }
149
150
158 public function getAmountByMonth($year, $format = 0)
159 {
160 $sql = "SELECT date_format(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).",'%m') as dm, sum(".$this->field.")";
161 $sql .= " FROM ".$this->from;
162 $sql .= " WHERE date_format(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).",'%Y') = '".$this->db->escape($year)."'";
163 $sql .= " AND ".$this->where;
164 $sql .= " GROUP BY dm";
165 $sql .= $this->db->order('dm', 'DESC');
166
167 $res = $this->_getAmountByMonth($year, $sql, $format);
168
169 return $res;
170 }
171
178 public function getAverageByMonth($year)
179 {
180 $sql = "SELECT date_format(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).",'%m') as dm, avg(".$this->field.")";
181 $sql .= " FROM ".$this->from;
182 $sql .= " WHERE date_format(".$this->db->ifsql("e.".$this->datetouse." IS NULL", "e.date_create", "e.".$this->datetouse).",'%Y') = '".$this->db->escape($year)."'";
183 $sql .= " AND ".$this->where;
184 $sql .= " GROUP BY dm";
185 $sql .= $this->db->order('dm', 'DESC');
186
187 return $this->_getAverageByMonth($year, $sql);
188 }
189
195 public function getAllByYear()
196 {
197 $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";
198 $sql .= " FROM ".$this->from;
199 $sql .= " WHERE ".$this->where;
200 $sql .= " GROUP BY year";
201 $sql .= $this->db->order('year', 'DESC');
202
203 return $this->_getAllByYear($sql);
204 }
205}
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition card.php:66
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...
global $conf
The following vars must be defined: $type2label $form $conf, $lang, The following vars may also be de...
Definition member.php:79