dolibarr  9.0.0
propalestats.class.php
Go to the documentation of this file.
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-2009 Regis Houssin <regis.houssin@inodbox.com>
5  * Copyright (c) 2011 Juanjo Menent <jmenent@2byte.es>
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 <http://www.gnu.org/licenses/>.
19  */
20 
27 include_once DOL_DOCUMENT_ROOT . '/core/class/stats.class.php';
28 include_once DOL_DOCUMENT_ROOT . '/comm/propal/class/propal.class.php';
29 include_once DOL_DOCUMENT_ROOT . '/supplier_proposal/class/supplier_proposal.class.php';
30 include_once DOL_DOCUMENT_ROOT . '/core/lib/date.lib.php';
31 
32 
36 class PropaleStats extends Stats
37 {
41  public $table_element;
42 
43  var $socid;
44  var $userid;
45 
46  var $from;
47  var $field;
48  var $where;
49 
50 
59  function __construct($db, $socid=0, $userid=0, $mode='customer')
60  {
61  global $user, $conf;
62 
63  $this->db = $db;
64  $this->socid = ($socid > 0 ? $socid : 0);
65  $this->userid = $userid;
66 
67  if ($mode == 'customer')
68  {
69  $object=new Propal($this->db);
70 
71  $this->from = MAIN_DB_PREFIX.$object->table_element." as p";
72  $this->from_line = MAIN_DB_PREFIX.$object->table_element_line." as tl";
73  $this->field_date='p.datep';
74  $this->field='total_ht';
75  $this->field_line='total_ht';
76 
77  $this->where.= " p.fk_statut > 0";
78  }
79  if ($mode == 'supplier')
80  {
81  $object=new SupplierProposal($this->db);
82 
83  $this->from = MAIN_DB_PREFIX.$object->table_element." as p";
84  $this->from_line = MAIN_DB_PREFIX.$object->table_element_line." as tl";
85  $this->field_date='p.date_valid';
86  $this->field='total_ht';
87  $this->field_line='total_ht';
88 
89  $this->where.= " p.fk_statut > 0"; // Validated, accepted, refused and closed
90  }
91  //$this->where.= " AND p.fk_soc = s.rowid AND p.entity = ".$conf->entity;
92  $this->where.= " AND p.entity IN (".getEntity('propal').")";
93  if (!$user->rights->societe->client->voir && !$this->socid) $this->where .= " AND p.fk_soc = sc.fk_soc AND sc.fk_user = " .$user->id;
94  if($this->socid)
95  {
96  $this->where.=" AND p.fk_soc = ".$this->socid;
97  }
98  if ($this->userid > 0) $this->where.=' AND fk_user_author = '.$this->userid;
99  }
100 
101 
109  function getNbByMonth($year, $format=0)
110  {
111  global $user;
112 
113  $sql = "SELECT date_format(".$this->field_date.",'%m') as dm, COUNT(*) as nb";
114  $sql.= " FROM ".$this->from;
115  if (!$user->rights->societe->client->voir && !$user->societe_id) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
116  $sql.= " WHERE ".$this->field_date." BETWEEN '".$this->db->idate(dol_get_first_day($year))."' AND '".$this->db->idate(dol_get_last_day($year))."'";
117  $sql.= " AND ".$this->where;
118  $sql.= " GROUP BY dm";
119  $sql.= $this->db->order('dm','DESC');
120 
121  $res=$this->_getNbByMonth($year, $sql, $format);
122  return $res;
123  }
124 
131  function getNbByYear()
132  {
133  global $user;
134 
135  $sql = "SELECT date_format(".$this->field_date.",'%Y') as dm, COUNT(*) as nb, SUM(c.".$this->field.")";
136  $sql.= " FROM ".$this->from;
137  if (!$user->rights->societe->client->voir && !$this->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
138  $sql.= " WHERE ".$this->where;
139  $sql.= " GROUP BY dm";
140  $sql.= $this->db->order('dm','DESC');
141 
142  return $this->_getNbByYear($sql);
143  }
144 
152  function getAmountByMonth($year, $format)
153  {
154  global $user;
155 
156  $sql = "SELECT date_format(".$this->field_date.",'%m') as dm, SUM(p.".$this->field.")";
157  $sql.= " FROM ".$this->from;
158  if (!$user->rights->societe->client->voir && !$this->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
159  $sql.= " WHERE ".$this->field_date." BETWEEN '".$this->db->idate(dol_get_first_day($year))."' AND '".$this->db->idate(dol_get_last_day($year))."'";
160  $sql.= " AND ".$this->where;
161  $sql.= " GROUP BY dm";
162  $sql.= $this->db->order('dm','DESC');
163 
164  $res=$this->_getAmountByMonth($year, $sql, $format);
165  return $res;
166  }
167 
174  function getAverageByMonth($year)
175  {
176  global $user;
177 
178  $sql = "SELECT date_format(".$this->field_date.",'%m') as dm, AVG(p.".$this->field.")";
179  $sql.= " FROM ".$this->from;
180  if (!$user->rights->societe->client->voir && !$this->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
181  $sql.= " WHERE ".$this->field_date." BETWEEN '".$this->db->idate(dol_get_first_day($year))."' AND '".$this->db->idate(dol_get_last_day($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  function getAllByYear()
195  {
196  global $user;
197 
198  $sql = "SELECT date_format(".$this->field_date.",'%Y') as year, COUNT(*) as nb, SUM(".$this->field.") as total, AVG(".$this->field.") as avg";
199  $sql.= " FROM ".$this->from;
200  if (!$user->rights->societe->client->voir && !$this->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
201  $sql.= " WHERE ".$this->where;
202  $sql.= " GROUP BY year";
203  $sql.= $this->db->order('year','DESC');
204 
205  return $this->_getAllByYear($sql);
206  }
207 
208 
209 
216  function getAllByProduct($year)
217  {
218  global $user;
219 
220  $sql = "SELECT product.ref, COUNT(product.ref) as nb, SUM(tl.".$this->field_line.") as total, AVG(tl.".$this->field_line.") as avg";
221  $sql.= " FROM ".$this->from.", ".$this->from_line.", ".MAIN_DB_PREFIX."product as product";
222  if (!$user->rights->societe->client->voir && !$user->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
223  $sql.= " WHERE ".$this->where;
224  $sql.= " AND p.rowid = tl.fk_propal AND tl.fk_product = product.rowid";
225  $sql.= " AND ".$this->field_date." BETWEEN '".$this->db->idate(dol_get_first_day($year,1,false))."' AND '".$this->db->idate(dol_get_last_day($year,12,false))."'";
226  $sql.= " GROUP BY product.ref";
227  $sql.= $this->db->order('nb','DESC');
228  //$sql.= $this->db->plimit(20);
229 
230  return $this->_getAllByProduct($sql);
231  }
232 }
Parent class of statistics class.
Definition: stats.class.php:30
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition: date.lib.php:453
_getNbByMonth($year, $sql, $format=0)
Renvoie le nombre de proposition par mois pour une annee donnee.
getAmountByMonth($year, $format)
Return the propals amount by month for a year.
getAllByYear()
Return nb, total and average.
_getAllByProduct($sql, $limit=10)
Return number or total of product refs.
_getAllByYear($sql)
Return nb of elements, total amount and avg amount each year.
__construct($db, $socid=0, $userid=0, $mode='customer')
Constructor.
getAllByProduct($year)
Return nb, amount of predefined product for year.
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition: date.lib.php:467
getAverageByMonth($year)
Return the propals amount average by month for a year.
Class to manage price ask supplier.
_getAmountByMonth($year, $sql, $format=0)
Renvoie le nombre d&#39;element par mois pour une annee donnee.
getNbByMonth($year, $format=0)
Return propals number by month for a year.
_getNbByYear($sql)
Return nb of elements by year.
getNbByYear()
Return propals number per year.
_getAverageByMonth($year, $sql, $format=0)
Renvoie le montant moyen par mois pour une annee donnee.
Class to manage proposals statistics.
Class to manage proposals.