26include_once DOL_DOCUMENT_ROOT.
'/core/class/stats.class.php';
27include_once DOL_DOCUMENT_ROOT.
'/compta/facture/class/facture.class.php';
28include_once DOL_DOCUMENT_ROOT.
'/fourn/class/fournisseur.facture.class.php';
29include_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
42 public $table_element;
60 public function __construct(
DoliDB $db, $socid, $mode, $userid = 0, $typentid = 0, $categid = 0)
65 $this->socid = ($socid > 0 ? $socid : 0);
66 $this->userid = $userid;
67 $this->cachefilesuffix = $mode;
70 if ($mode ==
'customer') {
72 $this->from = MAIN_DB_PREFIX.$object->table_element.
" as f";
73 $this->from_line = MAIN_DB_PREFIX.$object->table_element_line.
" as tl";
74 $this->field =
'total_ht';
75 $this->field_line =
'total_ht';
77 if ($mode ==
'supplier') {
79 $this->from = MAIN_DB_PREFIX.$object->table_element.
" as f";
80 $this->from_line = MAIN_DB_PREFIX.$object->table_element_line.
" as tl";
81 $this->field =
'total_ht';
82 $this->field_line =
'total_ht';
85 $this->where .= ($this->where ?
' AND ' :
'').
" f.fk_statut >= 0";
86 $this->where .=
" AND f.entity IN (".getEntity(
'invoice').
")";
87 if (!$user->hasRight(
'societe',
'client',
'voir')) {
88 $this->where .=
" AND f.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id);
90 if ($mode ==
'customer') {
91 $this->where .=
" AND (f.fk_statut <> 3 OR f.close_code <> 'replaced')";
94 $this->where .=
" AND f.fk_soc = ".((int) $this->socid);
96 if ($this->userid > 0) {
97 $this->where .=
' AND f.fk_user_author = '.((int) $this->userid);
99 if ($mode ==
'customer') {
101 $this->where .=
" AND f.type IN (0,1,2,5)";
103 $this->where .=
" AND f.type IN (0,1,2,3,5)";
106 if ($mode ==
'supplier') {
108 $this->where .=
" AND f.type IN (0,1,2,5)";
110 $this->where .=
" AND f.type IN (0,1,2,3,5)";
115 $this->join .=
' LEFT JOIN '.MAIN_DB_PREFIX.
'societe as s ON s.rowid = f.fk_soc';
116 $this->where .=
' AND s.fk_typent = '.((int) $typentid);
120 $this->where .=
' AND EXISTS (SELECT rowid FROM '.MAIN_DB_PREFIX.
'categorie_societe as cats WHERE cats.fk_soc = f.fk_soc AND cats.fk_categorie = '.((int) $categid).
')';
136 $sql =
"SELECT date_format(f.datef,'%m') as dm, COUNT(*) as nb";
137 $sql .=
" FROM ".$this->from;
138 if (!$user->hasRight(
'societe',
'client',
'voir')) {
139 $sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
143 $sql .=
" AND ".$this->where;
144 $sql .=
" GROUP BY dm";
145 $sql .= $this->db->order(
'dm',
'DESC');
162 $sql =
"SELECT date_format(f.datef,'%Y') as dm, COUNT(*), SUM(c.".$this->field.
")";
163 $sql .=
" FROM ".$this->from;
164 if (!$user->hasRight(
'societe',
'client',
'voir')) {
165 $sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
168 $sql .=
" WHERE ".$this->where;
169 $sql .=
" GROUP BY dm";
170 $sql .= $this->db->order(
'dm',
'DESC');
187 $sql =
"SELECT date_format(datef,'%m') as dm, SUM(f.".$this->field.
")";
188 $sql .=
" FROM ".$this->from;
189 if (!$user->hasRight(
'societe',
'client',
'voir')) {
190 $sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
194 $sql .=
" AND ".$this->where;
195 $sql .=
" GROUP BY dm";
196 $sql .= $this->db->order(
'dm',
'DESC');
213 $sql =
"SELECT date_format(datef,'%m') as dm, AVG(f.".$this->field.
")";
214 $sql .=
" FROM ".$this->from;
215 if (!$user->hasRight(
'societe',
'client',
'voir')) {
216 $sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
220 $sql .=
" AND ".$this->where;
221 $sql .=
" GROUP BY dm";
222 $sql .= $this->db->order(
'dm',
'DESC');
236 $sql =
"SELECT date_format(datef,'%Y') as year, COUNT(*) as nb, SUM(f.".$this->field.
") as total, AVG(f.".$this->field.
") as avg";
237 $sql .=
" FROM ".$this->from;
238 if (!$user->hasRight(
'societe',
'client',
'voir')) {
239 $sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
242 $sql .=
" WHERE ".$this->where;
243 $sql .=
" GROUP BY year";
244 $sql .= $this->db->order(
'year',
'DESC');
260 $sql =
"SELECT product.ref, COUNT(product.ref) as nb, SUM(tl.".$this->field_line.
") as total, AVG(tl.".$this->field_line.
") as avg";
261 $sql .=
" FROM ".$this->from.
", ".$this->from_line.
", ".MAIN_DB_PREFIX.
"product as product";
262 if (!$user->hasRight(
'societe',
'client',
'voir')) {
263 $sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
266 $sql .=
" WHERE ".$this->where;
267 $sql .=
" AND f.rowid = tl.fk_facture AND tl.fk_product = product.rowid";
269 $sql .=
" GROUP BY product.ref";
270 $sql .= $this->db->order(
'nb',
'DESC');
286 $endYear = date(
'Y');
287 $startYear = $endYear - $numberYears;
288 $sql =
"SELECT date_format(datef,'%Y') as dm, SUM(f.".$this->field.
")";
289 $sql .=
" FROM ".$this->from;
290 if (!$user->hasRight(
'societe',
'client',
'voir')) {
291 $sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
295 $sql .=
" AND ".$this->where;
296 $sql .=
" GROUP BY dm";
297 $sql .= $this->db->order(
'dm',
'ASC');
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Class to manage Dolibarr database access.
Class to manage suppliers invoices.
Class to manage invoices.
Class to manage stats for invoices (customer and supplier)
getAverageByMonth($year)
Return average amount.
getNbByMonth($year, $format=0)
Return orders number by month for a year.
getAmountByMonth($year, $format=0)
Return the invoices amount by month for a year.
__construct(DoliDB $db, $socid, $mode, $userid=0, $typentid=0, $categid=0)
Constructor.
getAllByProduct($year, $limit=10)
Return nb, amount of predefined product for year.
getNbByYear()
Return invoices number per year.
getAmountByYear($numberYears, $format=0)
Return the invoices amount by year for a number of past years.
getAllByYear()
Return nb, total and average.
Parent class of statistics class.
_getAmountByYear($sql)
Returns the summed amounts per year for a given number of past years ending now.
_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...
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.
getDolGlobalString($key, $default='')
Return dolibarr global constant string value.