dolibarr  17.0.3
salariesstats.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2018 Alexandre Spangaro <aspangaro@open-dsi.fr>
3  * Copyright (c) 2018 Fidesio <contact@fidesio.com>
4  * Copyright (C) 2021 Gauthier VERDOL <gauthier.verdol@atm-consulting.fr>
5  *
6  * This program is free software; you can redistribute it and/or modify
7  * it under the terms of the GNU General Public License as published by
8  * the Free Software Foundation; either version 3 of the License, or
9  * (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program. If not, see <https://www.gnu.org/licenses/>.
18  */
19 
25 include_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
26 include_once DOL_DOCUMENT_ROOT.'/salaries/class/salary.class.php';
27 
31 class SalariesStats extends Stats
32 {
36  public $table_element;
37 
38  public $socid;
39  public $userid;
40 
41  public $from;
42  public $field;
43  public $where;
44 
53  public function __construct($db, $socid = 0, $userid = 0)
54  {
55  global $conf;
56 
57  $this->db = $db;
58  $this->socid = $socid;
59  $this->userid = $userid;
60 
61  $object = new Salary($this->db);
62  $this->from = MAIN_DB_PREFIX.$object->table_element;
63  $this->field = 'amount';
64 
65  $this->where = " entity = ".$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 IN ('.$this->db->sanitize(join(',', $this->userid)).')';
71  } elseif ($this->userid > 0) {
72  $this->where .= " AND fk_user = ".((int) $this->userid);
73  }
74  }
75 
76 
82  public function getNbByYear()
83  {
84  $sql = "SELECT YEAR(dateep) as dm, count(*)";
85  $sql .= " FROM ".$this->from;
86  $sql .= " WHERE ".$this->where;
87  $sql .= " GROUP BY dm DESC";
88 
89  return $this->_getNbByYear($sql);
90  }
91 
92 
100  public function getNbByMonth($year, $format = 0)
101  {
102  $sql = "SELECT MONTH(dateep) as dm, count(*)";
103  $sql .= " FROM ".$this->from;
104  $sql .= " WHERE YEAR(dateep) = ".((int) $year);
105  $sql .= " AND ".$this->where;
106  $sql .= " GROUP BY dm";
107  $sql .= $this->db->order('dm', 'DESC');
108 
109  $res = $this->_getNbByMonth($year, $sql, $format);
110 
111  return $res;
112  }
113 
114 
122  public function getAmountByMonth($year, $format = 0)
123  {
124  $sql = "SELECT date_format(dateep,'%m') as dm, sum(".$this->field.")";
125  $sql .= " FROM ".$this->from;
126  $sql .= " WHERE date_format(dateep,'%Y') = '".$this->db->escape($year)."'";
127  $sql .= " AND ".$this->where;
128  $sql .= " GROUP BY dm";
129  $sql .= $this->db->order('dm', 'DESC');
130 
131  $res = $this->_getAmountByMonth($year, $sql, $format);
132 
133  return $res;
134  }
135 
142  public function getAverageByMonth($year)
143  {
144  $sql = "SELECT date_format(dateep,'%m') as dm, avg(".$this->field.")";
145  $sql .= " FROM ".$this->from;
146  $sql .= " WHERE date_format(dateep,'%Y') = '".$this->db->escape($year)."'";
147  $sql .= " AND ".$this->where;
148  $sql .= " GROUP BY dm";
149  $sql .= $this->db->order('dm', 'DESC');
150 
151  return $this->_getAverageByMonth($year, $sql);
152  }
153 
159  public function getAllByYear()
160  {
161  $sql = "SELECT date_format(dateep,'%Y') as year, count(*) as nb, sum(".$this->field.") as total, avg(".$this->field.") as avg";
162  $sql .= " FROM ".$this->from;
163  $sql .= " WHERE ".$this->where;
164  $sql .= " GROUP BY year";
165  $sql .= $this->db->order('year', 'DESC');
166 
167  return $this->_getAllByYear($sql);
168  }
169 }
db
$conf db
API class for accounts.
Definition: inc.php:41
SalariesStats\getNbByYear
getNbByYear()
Return the number of salary by year.
Definition: salariesstats.class.php:82
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:357
Stats\_getAmountByMonth
_getAmountByMonth($year, $sql, $format=0)
Return the amount per month for a given year.
Definition: stats.class.php:494
SalariesStats
Classe permettant la gestion des stats des salaires.
Definition: salariesstats.class.php:31
Stats\_getAllByYear
_getAllByYear($sql)
Return nb of elements, total amount and avg amount each year.
Definition: stats.class.php:386
SalariesStats\__construct
__construct($db, $socid=0, $userid=0)
Constructor.
Definition: salariesstats.class.php:53
SalariesStats\getAmountByMonth
getAmountByMonth($year, $format=0)
Return amount of salaries by month for a given year.
Definition: salariesstats.class.php:122
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:437
Salary
Class to manage salary payments.
Definition: salary.class.php:33
SalariesStats\getAverageByMonth
getAverageByMonth($year)
Return average amount.
Definition: salariesstats.class.php:142
SalariesStats\getNbByMonth
getNbByMonth($year, $format=0)
Return the number of salary by month, for a given year.
Definition: salariesstats.class.php:100
SalariesStats\getAllByYear
getAllByYear()
Return nb, total and average.
Definition: salariesstats.class.php:159
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:552