1<?php
2/* Copyright (C) 2003      Rodolphe Quiedeville <rodolphe@quiedeville.org>
3 * Copyright (c) 2005-2013 Laurent Destailleur  <eldy@users.sourceforge.net>
4 * Copyright (C) 2005-2012 Regis Houssin        <regis.houssin@inodbox.com>
5 * Copyright (C) 2012      Marcos García        <marcosgdf@gmail.com>
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
21/**
22 *       \file       htdocs/fichinter/class/fichinterstats.class.php
23 *       \ingroup    fichinter
24 *       \brief      File of class to manage intervention statistics
25 */
26include_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
27include_once DOL_DOCUMENT_ROOT.'/fichinter/class/fichinter.class.php';
28include_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
29
30
31/**
32 *    Class to manage intervention statistics
33 */
34class FichinterStats extends Stats
35{
36	/**
37	 * @var string Name of table without prefix where object is stored
38	 */
39	public $table_element;
40
41	public $socid;
42	public $userid;
43
44	public $from;
45	public $field;
46	public $where;
47
48
49	/**
50	 * Constructor
51	 *
52	 * @param 	DoliDB	$db		   Database handler
53	 * @param 	int		$socid	   Id third party for filter. This value must be forced during the new to external user company if user is an external user.
54	 * @param 	string	$mode	   Option ('customer', 'supplier')
55	 * @param   int		$userid    Id user for filter (creation user)
56	 */
57	public function __construct($db, $socid, $mode, $userid = 0)
58	{
59		global $user, $conf;
60
61		$this->db = $db;
62
63		$this->socid = ($socid > 0 ? $socid : 0);
64		$this->userid = $userid;
65		$this->cachefilesuffix = $mode;
66
67		if ($mode == 'customer') {
68			$object = new Fichinter($this->db);
69			$this->from = MAIN_DB_PREFIX.$object->table_element." as c";
70			$this->from_line = MAIN_DB_PREFIX.$object->table_element_line." as tl";
71			$this->field = '0';
72			$this->field_line = '0';
73			//$this->where.= " AND c.fk_statut > 0";    // Not draft and not cancelled
74		}
75		if (!$user->rights->societe->client->voir && !$this->socid) {
76			$this->where .= " AND c.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
77		}
78		$this->where .= ($this->where ? ' AND ' : '')."c.entity IN (".getEntity('fichinter').')';
79
80		if ($this->socid) {
81			$this->where .= " AND c.fk_soc = ".((int) $this->socid);
82		}
83		if ($this->userid > 0) {
84			$this->where .= ' AND c.fk_user_author = '.((int) $this->userid);
85		}
86	}
87
88	/**
89	 * Return intervention number by month for a year
90	 *
91	 * @param	int		$year		Year to scan
92	 *	@param	int		$format		0=Label of abscissa is a translated text, 1=Label of abscissa is month number, 2=Label of abscissa is first letter of month
93	 * @return	array				Array with number by month
94	 */
95	public function getNbByMonth($year, $format = 0)
96	{
97		global $user;
98
99		$sql = "SELECT date_format(c.date_valid,'%m') as dm, COUNT(*) as nb";
100		$sql .= " FROM ".$this->from;
101		if (!$user->rights->societe->client->voir && !$this->socid) {
102			$sql .= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
103		}
104		$sql .= " WHERE c.date_valid BETWEEN '".$this->db->idate(dol_get_first_day($year))."' AND '".$this->db->idate(dol_get_last_day($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		return $res;
111	}
112
113	/**
114	 * Return interventions number per year
115	 *
116	 * @return	array	Array with number by year
117	 *
118	 */
119	public function getNbByYear()
120	{
121		global $user;
122
123		$sql = "SELECT date_format(c.date_valid,'%Y') as dm, COUNT(*) as nb, 0";
124		$sql .= " FROM ".$this->from;
125		if (!$user->rights->societe->client->voir && !$this->socid) {
126			$sql .= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
127		}
128		$sql .= " WHERE ".$this->where;
129		$sql .= " GROUP BY dm";
130		$sql .= $this->db->order('dm', 'DESC');
131
132		return $this->_getNbByYear($sql);
133	}
134
135	/**
136	 * Return the intervention amount by month for a year
137	 *
138	 * @param	int		$year		Year to scan
139	 *	@param	int		$format		0=Label of abscissa is a translated text, 1=Label of abscissa is month number, 2=Label of abscissa is first letter of month
140	 * @return	array				Array with amount by month
141	 */
142	public function getAmountByMonth($year, $format = 0)
143	{
144		global $user;
145
146		$sql = "SELECT date_format(c.date_valid,'%m') as dm, 0";
147		$sql .= " FROM ".$this->from;
148		if (!$user->rights->societe->client->voir && !$this->socid) {
149			$sql .= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
150		}
151		$sql .= " WHERE c.date_valid BETWEEN '".$this->db->idate(dol_get_first_day($year))."' AND '".$this->db->idate(dol_get_last_day($year))."'";
152		$sql .= " AND ".$this->where;
153		$sql .= " GROUP BY dm";
154		$sql .= $this->db->order('dm', 'DESC');
155
156		$res = $this->_getAmountByMonth($year, $sql, $format);
157		return $res;
158	}
159
160	/**
161	 * Return the intervention amount average by month for a year
162	 *
163	 * @param	int		$year	year for stats
164	 * @return	array			array with number by month
165	 */
166	public function getAverageByMonth($year)
167	{
168		global $user;
169
170		$sql = "SELECT date_format(c.date_valid,'%m') as dm, 0";
171		$sql .= " FROM ".$this->from;
172		if (!$user->rights->societe->client->voir && !$this->socid) {
173			$sql .= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
174		}
175		$sql .= " WHERE c.date_valid BETWEEN '".$this->db->idate(dol_get_first_day($year))."' AND '".$this->db->idate(dol_get_last_day($year))."'";
176		$sql .= " AND ".$this->where;
177		$sql .= " GROUP BY dm";
178		$sql .= $this->db->order('dm', 'DESC');
179
180		return $this->_getAverageByMonth($year, $sql);
181	}
182
183	/**
184	 *	Return nb, total and average
185	 *
186	 *	@return	array	Array of values
187	 */
188	public function getAllByYear()
189	{
190		global $user;
191
192		$sql = "SELECT date_format(c.date_valid,'%Y') as year, COUNT(*) as nb, 0 as total, 0 as avg";
193		$sql .= " FROM ".$this->from;
194		if (!$user->rights->societe->client->voir && !$this->socid) {
195			$sql .= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
196		}
197		$sql .= " WHERE ".$this->where;
198		$sql .= " GROUP BY year";
199		$sql .= $this->db->order('year', 'DESC');
200
201		return $this->_getAllByYear($sql);
202	}
203
204	/**
205	 *  Return nb, amount of predefined product for year
206	 *
207	 *  @param	int		$year			Year to scan
208	 *  @param  int     $limit      	Limit
209	 *  @return	array					Array of values
210	 */
211	public function getAllByProduct($year, $limit = 0)
212	{
213		global $user;
214
215		$sql = "SELECT product.ref, COUNT(product.ref) as nb, 0 as total, 0 as avg";
216		$sql .= " FROM ".$this->from.", ".$this->from_line.", ".MAIN_DB_PREFIX."product as product";
217		//if (!$user->rights->societe->client->voir && !$user->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
218		$sql .= " WHERE ".$this->where;
219		$sql .= " AND c.rowid = tl.fk_fichinter AND tl.fk_product = product.rowid";
220		$sql .= " AND c.date_valid BETWEEN '".$this->db->idate(dol_get_first_day($year, 1, false))."' AND '".$this->db->idate(dol_get_last_day($year, 12, false))."'";
221		$sql .= " GROUP BY product.ref";
222		$sql .= $this->db->order('nb', 'DESC');
223		//$sql.= $this->db->plimit(20);
224
225		return $this->_getAllByProduct($sql, $limit);
226	}
227}
228