28include_once DOL_DOCUMENT_ROOT.
'/core/class/stats.class.php';
29include_once DOL_DOCUMENT_ROOT.
'/comm/propal/class/propal.class.php';
30include_once DOL_DOCUMENT_ROOT.
'/supplier_proposal/class/supplier_proposal.class.php';
31include_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
42 public $table_element;
63 public function __construct($db, $socid = 0, $userid = 0, $mode =
'customer', $typentid = 0, $categid = 0)
66 $this->socid = ($socid > 0 ? $socid : 0);
67 $this->userid = $userid;
70 if ($mode ==
'customer') {
73 $this->from = MAIN_DB_PREFIX.$object->table_element.
" as p";
74 $this->from_line = MAIN_DB_PREFIX.$object->table_element_line.
" as tl";
75 $this->field_date =
'p.datep';
76 $this->field =
'total_ht';
77 $this->field_line =
'total_ht';
81 if ($mode ==
'supplier') {
84 $this->from = MAIN_DB_PREFIX.$object->table_element.
" as p";
85 $this->from_line = MAIN_DB_PREFIX.$object->table_element_line.
" as tl";
86 $this->field_date =
'p.date_valid';
87 $this->field =
'total_ht';
88 $this->field_line =
'total_ht';
93 $this->where .= ($this->where ?
' AND ' :
'').
"p.entity IN (".
getEntity(
'propal').
")";
95 $this->where .=
" AND p.fk_soc = ".((int) $this->socid);
97 if ($this->userid > 0) {
98 $this->where .=
' AND fk_user_author = '.((int) $this->userid);
102 $this->join .=
' LEFT JOIN '.MAIN_DB_PREFIX.
'societe as s ON s.rowid = p.fk_soc';
103 $this->where .=
' AND s.fk_typent = '.((int) $typentid);
107 $this->where .=
' AND EXISTS (SELECT rowid FROM '.MAIN_DB_PREFIX.
'categorie_societe as cats WHERE cats.fk_soc = p.fk_soc AND cats.fk_categorie = '.((int) $categid).
')';
123 $sql =
"SELECT date_format(".$this->field_date.
",'%m') as dm, COUNT(*) as nb";
124 $sql .=
" FROM ".$this->from;
125 if (!$user->hasRight(
'societe',
'client',
'voir')) {
126 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
130 $sql .=
" AND ".$this->where;
131 $sql .=
" GROUP BY dm";
132 $sql .= $this->db->order(
'dm',
'DESC');
148 $sql =
"SELECT date_format(".$this->field_date.
",'%Y') as dm, COUNT(*) as nb, SUM(c.".$this->field.
")";
149 $sql .=
" FROM ".$this->from;
150 if (!$user->hasRight(
'societe',
'client',
'voir')) {
151 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
154 $sql .=
" WHERE ".$this->where;
155 $sql .=
" GROUP BY dm";
156 $sql .= $this->db->order(
'dm',
'DESC');
172 $sql =
"SELECT date_format(".$this->field_date.
",'%m') as dm, SUM(p.".$this->field.
")";
173 $sql .=
" FROM ".$this->from;
174 if (!$user->hasRight(
'societe',
'client',
'voir')) {
175 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
179 $sql .=
" AND ".$this->where;
180 $sql .=
" GROUP BY dm";
181 $sql .= $this->db->order(
'dm',
'DESC');
197 $sql =
"SELECT date_format(".$this->field_date.
",'%m') as dm, AVG(p.".$this->field.
")";
198 $sql .=
" FROM ".$this->from;
199 if (!$user->hasRight(
'societe',
'client',
'voir')) {
200 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
204 $sql .=
" AND ".$this->where;
205 $sql .=
" GROUP BY dm";
206 $sql .= $this->db->order(
'dm',
'DESC');
220 $sql =
"SELECT date_format(".$this->field_date.
",'%Y') as year, COUNT(*) as nb, SUM(".$this->field.
") as total, AVG(".$this->field.
") as avg";
221 $sql .=
" FROM ".$this->from;
222 if (!$user->hasRight(
'societe',
'client',
'voir')) {
223 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
226 $sql .=
" WHERE ".$this->where;
227 $sql .=
" GROUP BY year";
228 $sql .= $this->db->order(
'year',
'DESC');
246 $sql =
"SELECT product.ref, COUNT(product.ref) as nb, SUM(tl.".$this->field_line.
") as total, AVG(tl.".$this->field_line.
") as avg";
247 $sql .=
" FROM ".$this->from;
248 $sql .=
" INNER JOIN ".$this->from_line.
" ON p.rowid = tl.fk_propal";
249 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"product as product ON tl.fk_product = product.rowid";
250 if (!$user->hasRight(
'societe',
'client',
'voir')) {
251 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
254 $sql .=
" WHERE ".$this->where;
255 $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)).
"'";
256 $sql .=
" GROUP BY product.ref";
257 $sql .= $this->db->order(
'nb',
'DESC');
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Class to manage proposals.
Class to manage proposals statistics.
getAllByYear()
Return nb, total and average.
getAverageByMonth($year)
Return the propals amount average by month for a year.
getAllByProduct($year, $limit=10)
Return nb, amount of predefined product for year.
getAmountByMonth($year, $format=0)
Return the propals amount by month for a year.
getNbByMonth($year, $format=0)
Return propals number by month for a year.
__construct($db, $socid=0, $userid=0, $mode='customer', $typentid=0, $categid=0)
Constructor.
getNbByYear()
Return propals number per year.
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.
_getAllByProduct($sql, $limit=10)
Return number or total of product refs.
_getNbByMonth($year, $sql, $format=0)
Renvoie le nombre de documents par mois pour une annee donnee Return number of documents per month fo...
Class to manage price ask supplier.
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.