26require_once DOL_DOCUMENT_ROOT.
'/core/class/stats.class.php';
27require_once DOL_DOCUMENT_ROOT.
'/expensereport/class/expensereport.class.php';
37 public $table_element;
67 private $datetouse =
'date_valid';
83 $this->socid = $socid;
84 $this->userid = $userid;
87 $this->from = MAIN_DB_PREFIX.$object->table_element.
" as e";
88 $this->field =
'total_ht';
92 $this->where .=
' e.entity IN ('.getEntity(
'expensereport').
')';
96 $this->where .=
" AND e.fk_soc = ".((int) $this->socid);
100 if (!$user->hasRight(
'expensereport',
'readall') && !$user->hasRight(
'expensereport',
'lire_tous')) {
101 $childids = $user->getAllChildIds();
102 $childids[] = $user->id;
103 $this->where .=
" AND e.fk_user_author IN (".$this->db->sanitize(implode(
',', $childids)).
")";
106 if ($this->userid > 0) {
107 $this->where .=
' AND e.fk_user_author = '.((int) $this->userid);
119 $sql =
"SELECT YEAR(".$this->db->ifsql(
"e.".$this->datetouse.
" IS NULL",
"e.date_create",
"e.".$this->datetouse).
") as dm, count(*)";
120 $sql .=
" FROM ".$this->from;
121 $sql .=
" GROUP BY dm DESC";
122 $sql .=
" WHERE ".$this->where;
137 $sql =
"SELECT MONTH(".$this->db->ifsql(
"e.".$this->datetouse.
" IS NULL",
"e.date_create",
"e.".$this->datetouse).
") as dm, count(*)";
138 $sql .=
" FROM ".$this->from;
139 $sql .=
" WHERE YEAR(e.".$this->datetouse.
") = ".((int) $year);
140 $sql .=
" AND ".$this->where;
141 $sql .=
" GROUP BY dm";
142 $sql .= $this->db->order(
'dm',
'DESC');
159 $sql =
"SELECT date_format(".$this->db->ifsql(
"e.".$this->datetouse.
" IS NULL",
"e.date_create",
"e.".$this->datetouse).
",'%m') as dm, sum(".$this->field.
")";
160 $sql .=
" FROM ".$this->from;
161 $sql .=
" WHERE date_format(".$this->db->ifsql(
"e.".$this->datetouse.
" IS NULL",
"e.date_create",
"e.".$this->datetouse).
",'%Y') = '".$this->db->escape($year).
"'";
162 $sql .=
" AND ".$this->where;
163 $sql .=
" GROUP BY dm";
164 $sql .= $this->db->order(
'dm',
'DESC');
179 $sql =
"SELECT date_format(".$this->db->ifsql(
"e.".$this->datetouse.
" IS NULL",
"e.date_create",
"e.".$this->datetouse).
",'%m') as dm, avg(".$this->field.
")";
180 $sql .=
" FROM ".$this->from;
181 $sql .=
" WHERE date_format(".$this->db->ifsql(
"e.".$this->datetouse.
" IS NULL",
"e.date_create",
"e.".$this->datetouse).
",'%Y') = '".$this->db->escape($year).
"'";
182 $sql .=
" AND ".$this->where;
183 $sql .=
" GROUP BY dm";
184 $sql .= $this->db->order(
'dm',
'DESC');
196 $sql =
"SELECT date_format(".$this->db->ifsql(
"e.".$this->datetouse.
" IS NULL",
"e.date_create",
"e.".$this->datetouse).
",'%Y') as year, count(*) as nb, sum(".$this->field.
") as total, avg(".$this->field.
") as avg";
197 $sql .=
" FROM ".$this->from;
198 $sql .=
" WHERE ".$this->where;
199 $sql .=
" GROUP BY year";
200 $sql .= $this->db->order(
'year',
'DESC');
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Class to manage Trips and Expenses.
Class to manage the statistics of the expensereports and expense notes.
getNbByMonth($year, $format=0)
Return the quantity of invoices per month for a given year.
getAverageByMonth($year)
Return average amount.
__construct($db, $socid=0, $userid=0)
Constructor.
getAmountByMonth($year, $format=0)
Renvoie le montant de facture par mois pour une annee donnee.
getNbByYear()
Return nb of expense report per year.
getAllByYear()
Return nb, total and average.
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.
_getNbByMonth($year, $sql, $format=0)
Renvoie le nombre de documents par mois pour une annee donnee Return number of documents per month fo...