19include_once DOL_DOCUMENT_ROOT.
'/core/class/stats.class.php';
20include_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
51 require_once
'project.class.php';
52 $this->project =
new Project($this->db);
54 $this->from = MAIN_DB_PREFIX.$this->project->table_element;
55 $this->field =
'opp_amount';
56 $this->where =
" entity = ".$conf->entity;
57 if ($this->socid > 0) {
58 $this->where .=
" AND fk_soc = ".((int) $this->socid);
60 if (is_array($this->userid) && count($this->userid) > 0) {
61 $this->where .=
' AND fk_user IN ('.$this->db->sanitize(implode(
',', $this->userid)).
')';
62 } elseif ($this->userid > 0) {
63 $this->where .=
" AND fk_user = ".((int) $this->userid);
78 global $conf, $user, $langs;
83 $sql .=
" SUM(t.opp_amount), t.fk_opp_status, cls.code, cls.label";
84 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
88 $sql .=
", ".MAIN_DB_PREFIX.
"c_lead_status as cls";
94 $sql .=
" AND t.fk_opp_status = cls.rowid";
95 $sql .=
" AND t.fk_statut <> 0";
96 $sql .=
" GROUP BY t.fk_opp_status, cls.code, cls.label";
100 dol_syslog(get_class($this).
'::'.__METHOD__, LOG_DEBUG);
101 $resql = $this->db->query($sql);
103 $num = $this->db->num_rows($resql);
107 $row = $this->db->fetch_row($resql);
108 if ($i < $limit || $num == $limit) {
109 $label = (($langs->trans(
"OppStatus".$row[2]) !=
"OppStatus".$row[2]) ? $langs->trans(
"OppStatus".$row[2]) : $row[2]);
111 $label.
' ('.
price(
price2num($row[0],
'MT'), 1, $langs, 1, -1, -1, $conf->currency).
')',
121 $langs->transnoentitiesnoconv(
"Other"),
125 $this->db->free($resql);
127 $this->error =
"Error ".$this->db->lasterror();
128 dol_syslog(get_class($this).
'::'.__METHOD__.
' '.$this->error, LOG_ERR);
142 global $conf, $user, $langs;
148 $sql =
"SELECT date_format(t.datec,'%Y') as year, COUNT(t.rowid) as nb, SUM(t.opp_amount) as total, AVG(t.opp_amount) as avg,";
149 $sql .=
" SUM(t.opp_amount * ".$this->db->ifsql(
"t.opp_percent IS NULL".($wonlostfilter ?
" OR cls.code IN ('WON','LOST')" :
""),
'0',
't.opp_percent').
" / 100) as weighted";
150 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t LEFT JOIN ".MAIN_DB_PREFIX.
"c_lead_status as cls ON cls.rowid = t.fk_opp_status";
159 $sql .=
" GROUP BY year";
160 $sql .= $this->db->order(
'year',
'DESC');
180 $projectsListId =
'';
181 if (!$user->hasRight(
'projet',
'all',
'lire')) {
182 $projectsListId =
$object->getProjectsAuthorizedForUser($user, 0, 1, $user->socid);
185 $sqlwhere[] =
' t.entity IN ('.getEntity(
'project').
')';
187 if (!empty($this->userid)) {
188 $sqlwhere[] =
' t.fk_user_resp = '.((int) $this->userid);
192 if (!empty($this->socid)) {
193 $sqlwhere[] =
' t.fk_soc = '.((int) $this->socid);
195 if (!empty($this->year) && empty($this->month)) {
198 if (!empty($this->year) && !empty($this->month)) {
199 $sqlwhere[] =
" t.datec BETWEEN '".$this->db->idate(
dol_get_first_day($this->year, $this->month)).
"' AND '".$this->db->idate(
dol_get_last_day($this->year, $this->month)).
"'";
202 if (!empty($this->
status)) {
203 $sqlwhere[] =
" t.fk_statut IN (".$this->db->sanitize($this->
status).
")";
206 if (!empty($this->opp_status)) {
207 if (is_numeric($this->opp_status) && $this->opp_status > 0) {
208 $sqlwhere[] =
" t.fk_opp_status = ".((int) $this->opp_status);
210 if ($this->opp_status ==
'all') {
211 $sqlwhere[] =
" (t.fk_opp_status IS NOT NULL AND t.fk_opp_status <> -1)";
213 if ($this->opp_status ==
'openedopp') {
214 $sqlwhere[] =
" (t.fk_opp_status IS NOT NULL AND t.fk_opp_status <> -1 AND t.fk_opp_status NOT IN (SELECT rowid FROM ".MAIN_DB_PREFIX.
"c_lead_status WHERE code IN ('WON','LOST')))";
216 if ($this->opp_status ==
'notopenedopp') {
217 $sqlwhere[] =
" (t.fk_opp_status IS NULL OR t.fk_opp_status = -1 OR t.fk_opp_status IN (SELECT rowid FROM ".MAIN_DB_PREFIX.
"c_lead_status WHERE code = 'WON'))";
219 if ($this->opp_status ==
'none') {
220 $sqlwhere[] =
" (t.fk_opp_status IS NULL OR t.fk_opp_status = -1)";
224 if (!$user->hasRight(
'projet',
'all',
'lire')) {
225 $sqlwhere[] =
" t.rowid IN (".$this->db->sanitize($projectsListId).
")";
228 if (count($sqlwhere) > 0) {
229 $sqlwhere_str =
' WHERE '.implode(
' AND ', $sqlwhere);
232 return $sqlwhere_str;
246 $sql =
"SELECT date_format(t.datec,'%m') as dm, COUNT(*) as nb";
247 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
252 $sql .=
" GROUP BY dm";
253 $sql .= $this->db->order(
'dm',
'DESC');
271 $sql =
"SELECT date_format(t.datec,'%m') as dm, SUM(t.opp_amount)";
272 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
277 $sql .=
" GROUP BY dm";
278 $sql .= $this->db->order(
'dm',
'DESC');
297 global $conf, $user, $langs;
299 if ($startyear > $endyear) {
306 if (!empty($cachedelay)) {
307 include_once DOL_DOCUMENT_ROOT.
'/core/lib/files.lib.php';
308 include_once DOL_DOCUMENT_ROOT.
'/core/lib/json.lib.php';
311 $newpathofdestfile = $conf->user->dir_temp.
'/'.get_class($this).
'_'.__FUNCTION__.
'_'.(empty($this->cachefilesuffix) ?
'' : $this->cachefilesuffix.
'_').$langs->defaultlang.
'_user'.$user->id.
'.cache';
317 if ($cachedelay > 0) {
319 if ($filedate >= ($nowgmt - $cachedelay)) {
322 $this->lastfetchdate[get_class($this).
'_'.__FUNCTION__] = $filedate;
324 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" cache file ".$newpathofdestfile.
" is not found or older than now - cachedelay (".$nowgmt.
" - ".$cachedelay.
") so we can't use it.");
329 if ($foundintocache) {
330 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" read data from cache file ".$newpathofdestfile.
" ".$filedate.
".");
331 $data = json_decode(file_get_contents($newpathofdestfile),
true);
332 '@phan-var-force array $data';
335 while ($year <= $endyear) {
342 for ($i = 0; $i < 12; $i++) {
343 $data[$i][] = $datay[$endyear][$i][0];
345 while ($year <= $endyear) {
346 $data[$i][] = $datay[$year][$i][1];
353 if (empty($foundintocache) && ($cachedelay > 0 || $cachedelay == -1)) {
354 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" save cache file ".$newpathofdestfile.
" onto disk.");
358 $fp = fopen($newpathofdestfile,
'w');
360 fwrite($fp, json_encode($data));
364 dol_syslog(
"Failed to write cache file", LOG_ERR);
366 $this->lastfetchdate[get_class($this).
'_'.__FUNCTION__] = $nowgmt;
384 $sql =
"SELECT date_format(t.datec,'%m') as dm, SUM(t.opp_amount * ".$this->db->ifsql(
"t.opp_percent IS NULL".($wonlostfilter ?
" OR cls.code IN ('WON','LOST')" :
""),
'0',
't.opp_percent').
" / 100)";
385 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t LEFT JOIN ".MAIN_DB_PREFIX.
'c_lead_status as cls ON t.fk_opp_status = cls.rowid';
390 $sql .=
" GROUP BY dm";
391 $sql .= $this->db->order(
'dm',
'DESC');
408 global $conf, $user, $langs;
410 if ($startyear > $endyear) {
417 if (!empty($cachedelay)) {
418 include_once DOL_DOCUMENT_ROOT.
'/core/lib/files.lib.php';
419 include_once DOL_DOCUMENT_ROOT.
'/core/lib/json.lib.php';
422 $newpathofdestfile = $conf->user->dir_temp.
'/'.get_class($this).
'_'.__FUNCTION__.
'_'.(empty($this->cachefilesuffix) ?
'' : $this->cachefilesuffix.
'_').$langs->defaultlang.
'_user'.$user->id.
'.cache';
428 if ($cachedelay > 0) {
430 if ($filedate >= ($nowgmt - $cachedelay)) {
433 $this->lastfetchdate[get_class($this).
'_'.__FUNCTION__] = $filedate;
435 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" cache file ".$newpathofdestfile.
" is not found or older than now - cachedelay (".$nowgmt.
" - ".$cachedelay.
") so we can't use it.");
440 if ($foundintocache) {
441 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" read data from cache file ".$newpathofdestfile.
" ".$filedate.
".");
442 $data = json_decode(file_get_contents($newpathofdestfile),
true);
443 '@phan-var-force array $data';
446 while ($year <= $endyear) {
453 for ($i = 0; $i < 12; $i++) {
454 $data[$i][] = $datay[$endyear][$i][0];
456 while ($year <= $endyear) {
457 $data[$i][] = $datay[$year][$i][1];
464 if (empty($foundintocache) && ($cachedelay > 0 || $cachedelay == - 1)) {
465 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" save cache file ".$newpathofdestfile.
" onto disk.");
469 $fp = fopen($newpathofdestfile,
'w');
471 fwrite($fp, json_encode($data));
476 $this->lastfetchdate[get_class($this).
'_'.__FUNCTION__] = $nowgmt;
493 $sql =
"SELECT date_format(t.datec,'%m') as dm, count(t.opp_amount)";
494 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
499 $sql .=
" GROUP BY dm";
500 $sql .= $this->db->order(
'dm',
'DESC');
506 $sql =
"SELECT date_format(t.datec,'%m') as dm, count(t.opp_amount)";
507 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
512 $sql .=
" GROUP BY dm";
513 $sql .= $this->db->order(
'dm',
'DESC');
517 $res_only_wined = $this->
_getNbByMonth($year, $sql, $format);
521 foreach ($res_total as $key => $total_row) {
523 if (!empty($total_row[1])) {
524 $res[$key] = array($total_row[0], (100 * $res_only_wined[$key][1]) / $total_row[1]);
526 $res[$key] = array($total_row[0], 0);
540 $sql =
"SELECT date_format(datef,'%m') as dm, AVG(f.".$this->field.
")";
541 $sql .=
" FROM ".$this->from;
543 $sql .=
" AND ".$this->where;
544 $sql .=
" GROUP BY dm";
545 $sql .= $this->db->order(
'dm',
'DESC');
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
print $langs trans("AuditedSecurityEvents").'</strong >< span class="opacitymedium"></span >< br > status
Or an array listing all the potential status of the object: array: int of the status => translated la...
Class to manage projects.
Class to manage statistics on projects.
getAmountByMonth($year, $format=0)
Return the Project amount by month for a year.
__construct($db)
Constructor.
buildWhere()
Build the where part.
getAllByYear()
Return count, and sum of products.
getTransformRateByMonth($year, $format=0)
Return the Project transformation rate by month for a year.
getAllProjectByStatus($limit=5)
Return all leads grouped by opportunity status.
getNbByMonth($year, $format=0)
Return Project number by month for a year.
getWeightedAmountByMonth($year, $wonlostfilter=1)
Return the Project weighted opp amount by month for a year.
getAverageByMonth($year)
Return average of entity by month.
getTransformRateByMonthWithPrevYear($endyear, $startyear, $cachedelay=0)
Return amount of elements by month for several years.
getWeightedAmountByMonthWithPrevYear($endyear, $startyear, $cachedelay=0, $wonlostfilter=1)
Return amount of elements by month for several years.
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.
_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...
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.
dol_filemtime($pathoffile)
Return time of a file.
dol_is_dir($folder)
Test if filename is a directory.
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages.
dolChmod($filepath, $newmask='')
Change mod of a file.
dol_now($mode='auto')
Return date for now.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
dol_mkdir($dir, $dataroot='', $newmask='')
Creation of a directory (this can create recursive subdir)