Go to the documentation of this file.
28 include_once DOL_DOCUMENT_ROOT.
'/core/class/stats.class.php';
29 include_once DOL_DOCUMENT_ROOT.
'/comm/propal/class/propal.class.php';
30 include_once DOL_DOCUMENT_ROOT.
'/supplier_proposal/class/supplier_proposal.class.php';
31 include_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)
68 $this->socid = ($socid > 0 ? $socid : 0);
69 $this->userid = $userid;
72 if ($mode ==
'customer') {
75 $this->from = MAIN_DB_PREFIX.$object->table_element.
" as p";
76 $this->from_line = MAIN_DB_PREFIX.$object->table_element_line.
" as tl";
77 $this->field_date =
'p.datep';
78 $this->field =
'total_ht';
79 $this->field_line =
'total_ht';
83 if ($mode ==
'supplier') {
86 $this->from = MAIN_DB_PREFIX.$object->table_element.
" as p";
87 $this->from_line = MAIN_DB_PREFIX.$object->table_element_line.
" as tl";
88 $this->field_date =
'p.date_valid';
89 $this->field =
'total_ht';
90 $this->field_line =
'total_ht';
95 $this->where .= ($this->where ?
' AND ' :
'').
"p.entity IN (".
getEntity(
'propal').
")";
97 $this->where .=
" AND p.fk_soc = ".((int) $this->socid);
99 if ($this->userid > 0) {
100 $this->where .=
' AND fk_user_author = '.((int) $this->userid);
104 $this->join .=
' LEFT JOIN '.MAIN_DB_PREFIX.
'societe as s ON s.rowid = p.fk_soc';
105 $this->where .=
' AND s.fk_typent = '.((int) $typentid);
109 $this->join .=
' LEFT JOIN '.MAIN_DB_PREFIX.
'categorie_societe as cs ON cs.fk_soc = p.fk_soc';
110 $this->join .=
' LEFT JOIN '.MAIN_DB_PREFIX.
'categorie as c ON c.rowid = cs.fk_categorie';
111 $this->where .=
' AND c.rowid = '.((int) $categid);
127 $sql =
"SELECT date_format(".$this->field_date.
",'%m') as dm, COUNT(*) as nb";
128 $sql .=
" FROM ".$this->from;
129 if (empty($user->rights->societe->client->voir) && !$user->socid) {
130 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
134 $sql .=
" AND ".$this->where;
135 $sql .=
" GROUP BY dm";
136 $sql .= $this->
db->order(
'dm',
'DESC');
152 $sql =
"SELECT date_format(".$this->field_date.
",'%Y') as dm, COUNT(*) as nb, SUM(c.".$this->field.
")";
153 $sql .=
" FROM ".$this->from;
154 if (empty($user->rights->societe->client->voir) && !$this->socid) {
155 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
158 $sql .=
" WHERE ".$this->where;
159 $sql .=
" GROUP BY dm";
160 $sql .= $this->
db->order(
'dm',
'DESC');
176 $sql =
"SELECT date_format(".$this->field_date.
",'%m') as dm, SUM(p.".$this->field.
")";
177 $sql .=
" FROM ".$this->from;
178 if (empty($user->rights->societe->client->voir) && !$this->socid) {
179 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
183 $sql .=
" AND ".$this->where;
184 $sql .=
" GROUP BY dm";
185 $sql .= $this->
db->order(
'dm',
'DESC');
201 $sql =
"SELECT date_format(".$this->field_date.
",'%m') as dm, AVG(p.".$this->field.
")";
202 $sql .=
" FROM ".$this->from;
203 if (empty($user->rights->societe->client->voir) && !$this->socid) {
204 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
208 $sql .=
" AND ".$this->where;
209 $sql .=
" GROUP BY dm";
210 $sql .= $this->
db->order(
'dm',
'DESC');
224 $sql =
"SELECT date_format(".$this->field_date.
",'%Y') as year, COUNT(*) as nb, SUM(".$this->field.
") as total, AVG(".$this->field.
") as avg";
225 $sql .=
" FROM ".$this->from;
226 if (empty($user->rights->societe->client->voir) && !$this->socid) {
227 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
230 $sql .=
" WHERE ".$this->where;
231 $sql .=
" GROUP BY year";
232 $sql .= $this->
db->order(
'year',
'DESC');
250 $sql =
"SELECT product.ref, COUNT(product.ref) as nb, SUM(tl.".$this->field_line.
") as total, AVG(tl.".$this->field_line.
") as avg";
251 $sql .=
" FROM ".$this->from;
252 $sql .=
" INNER JOIN ".$this->from_line.
" ON p.rowid = tl.fk_propal";
253 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"product as product ON tl.fk_product = product.rowid";
254 if (empty($user->rights->societe->client->voir) && !$user->socid) {
255 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe_commerciaux as sc ON p.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
258 $sql .=
" WHERE ".$this->where;
260 $sql .=
" GROUP BY product.ref";
261 $sql .= $this->
db->order(
'nb',
'DESC');
$conf db
API class for accounts.
_getAllByProduct($sql, $limit=10)
Return number or total of product refs.
getAmountByMonth($year, $format=0)
Return the propals amount by month for a year.
getAllByProduct($year, $limit=10)
Return nb, amount of predefined product for year.
Parent class of statistics class.
_getNbByYear($sql)
Return nb of elements by year.
_getAmountByMonth($year, $sql, $format=0)
Return the amount per month for a given year.
Class to manage proposals statistics.
__construct($db, $socid=0, $userid=0, $mode='customer', $typentid=0, $categid=0)
Constructor.
getAllByYear()
Return nb, total and average.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
_getAllByYear($sql)
Return nb of elements, total amount and avg amount each year.
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.
getNbByMonth($year, $format=0)
Return propals number by month for a year.
_getNbByMonth($year, $sql, $format=0)
Renvoie le nombre de documents par mois pour une annee donnee Return number of documents per month fo...
getNbByYear()
Return propals number per year.
Class to manage proposals.
Class to manage price ask supplier.
getAverageByMonth($year)
Return the propals amount average by month for a year.
_getAverageByMonth($year, $sql, $format=0)
Renvoie le montant moyen par mois pour une annee donnee Return the amount average par month for a giv...