18 include_once DOL_DOCUMENT_ROOT.
'/core/class/stats.class.php';
19 include_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
47 require_once
'project.class.php';
62 global $conf, $user, $langs;
67 $sql .=
" SUM(t.opp_amount), t.fk_opp_status, cls.code, cls.label";
68 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
72 $sql .=
", ".MAIN_DB_PREFIX.
"c_lead_status as cls";
78 $sql .=
" AND t.fk_opp_status = cls.rowid";
79 $sql .=
" AND t.fk_statut <> 0";
80 $sql .=
" GROUP BY t.fk_opp_status, cls.code, cls.label";
84 dol_syslog(get_class($this).
'::'.__METHOD__.
"", LOG_DEBUG);
92 if ($i < $limit || $num == $limit) {
93 $label = (($langs->trans(
"OppStatus".$row[2]) !=
"OppStatus".$row[2]) ? $langs->trans(
"OppStatus".$row[2]) : $row[2]);
95 $label.
' ('.
price(
price2num($row[0],
'MT'), 1, $langs, 1, -1, -1, $conf->currency).
')',
105 $langs->transnoentitiesnoconv(
"Other"),
111 $this->error =
"Error ".$this->db->lasterror();
112 dol_syslog(get_class($this).
'::'.__METHOD__.
' '.$this->error, LOG_ERR);
126 global $conf, $user, $langs;
132 $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,";
133 $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";
134 $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";
143 $sql .=
" GROUP BY year";
144 $sql .= $this->
db->order(
'year',
'DESC');
164 $projectsListId =
'';
165 if (empty($user->rights->projet->all->lire)) {
166 $projectsListId = $object->getProjectsAuthorizedForUser($user, 0, 1, $user->socid);
169 $sqlwhere[] =
' t.entity IN ('.getEntity(
'project').
')';
171 if (!empty($this->userid)) {
172 $sqlwhere[] =
' t.fk_user_resp = '.((int) $this->userid);
176 if (!empty($this->socid)) {
177 $sqlwhere[] =
' t.fk_soc = '.((int) $this->socid);
179 if (!empty($this->year) && empty($this->yearmonth)) {
180 $sqlwhere[] =
" date_format(t.datec,'%Y') = '".$this->db->escape($this->year).
"'";
182 if (!empty($this->yearmonth)) {
186 if (!empty($this->status)) {
187 $sqlwhere[] =
" t.fk_statut IN (".$this->db->sanitize($this->status).
")";
190 if (!empty($this->opp_status)) {
191 if (is_numeric($this->opp_status) && $this->opp_status > 0) {
192 $sqlwhere[] =
" t.fk_opp_status = ".((int) $this->opp_status);
194 if ($this->opp_status ==
'all') {
195 $sqlwhere[] =
" (t.fk_opp_status IS NOT NULL AND t.fk_opp_status <> -1)";
197 if ($this->opp_status ==
'openedopp') {
198 $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')))";
200 if ($this->opp_status ==
'notopenedopp') {
201 $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'))";
203 if ($this->opp_status ==
'none') {
204 $sqlwhere[] =
" (t.fk_opp_status IS NULL OR t.fk_opp_status = -1)";
208 if (empty($user->rights->projet->all->lire)) {
209 $sqlwhere[] =
" t.rowid IN (".$this->db->sanitize($projectsListId).
")";
212 if (count($sqlwhere) > 0) {
213 $sqlwhere_str =
' WHERE '.implode(
' AND ', $sqlwhere);
216 return $sqlwhere_str;
230 $this->yearmonth = $year;
232 $sql =
"SELECT date_format(t.datec,'%m') as dm, COUNT(*) as nb";
233 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
238 $sql .=
" GROUP BY dm";
239 $sql .= $this->
db->order(
'dm',
'DESC');
241 $this->yearmonth = 0;
259 $this->yearmonth = $year;
261 $sql =
"SELECT date_format(t.datec,'%m') as dm, SUM(t.opp_amount)";
262 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
267 $sql .=
" GROUP BY dm";
268 $sql .= $this->
db->order(
'dm',
'DESC');
269 $this->yearmonth = 0;
288 global $conf, $user, $langs;
290 if ($startyear > $endyear) {
297 if (!empty($cachedelay)) {
298 include_once DOL_DOCUMENT_ROOT.
'/core/lib/files.lib.php';
299 include_once DOL_DOCUMENT_ROOT.
'/core/lib/json.lib.php';
302 $newpathofdestfile = $conf->user->dir_temp.
'/'.get_class($this).
'_'.__FUNCTION__.
'_'.(empty($this->cachefilesuffix) ?
'' : $this->cachefilesuffix.
'_').$langs->defaultlang.
'_user'.$user->id.
'.cache';
308 if ($cachedelay > 0) {
310 if ($filedate >= ($nowgmt - $cachedelay)) {
313 $this->lastfetchdate[get_class($this).
'_'.__FUNCTION__] = $filedate;
315 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.");
320 if ($foundintocache) {
321 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" read data from cache file ".$newpathofdestfile.
" ".$filedate.
".");
322 $data = json_decode(file_get_contents($newpathofdestfile),
true);
325 while ($year <= $endyear) {
332 for ($i = 0; $i < 12; $i++) {
333 $data[$i][] = $datay[$endyear][$i][0];
335 while ($year <= $endyear) {
336 $data[$i][] = $datay[$year][$i][1];
343 if (empty($foundintocache) && ($cachedelay > 0 || $cachedelay == -1)) {
344 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" save cache file ".$newpathofdestfile.
" onto disk.");
348 $fp = fopen($newpathofdestfile,
'w');
350 fwrite($fp, json_encode($data));
352 if (!empty($conf->global->MAIN_UMASK)) {
353 $newmask = $conf->global->MAIN_UMASK;
355 @chmod($newpathofdestfile, octdec($newmask));
357 dol_syslog(
"Failed to write cache file", LOG_ERR);
359 $this->lastfetchdate[get_class($this).
'_'.__FUNCTION__] = $nowgmt;
377 $this->yearmonth = $year;
379 $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)";
380 $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';
385 $sql .=
" GROUP BY dm";
386 $sql .= $this->
db->order(
'dm',
'DESC');
387 $this->yearmonth = 0;
404 global $conf, $user, $langs;
406 if ($startyear > $endyear) {
413 if (!empty($cachedelay)) {
414 include_once DOL_DOCUMENT_ROOT.
'/core/lib/files.lib.php';
415 include_once DOL_DOCUMENT_ROOT.
'/core/lib/json.lib.php';
418 $newpathofdestfile = $conf->user->dir_temp.
'/'.get_class($this).
'_'.__FUNCTION__.
'_'.(empty($this->cachefilesuffix) ?
'' : $this->cachefilesuffix.
'_').$langs->defaultlang.
'_user'.$user->id.
'.cache';
424 if ($cachedelay > 0) {
426 if ($filedate >= ($nowgmt - $cachedelay)) {
429 $this->lastfetchdate[get_class($this).
'_'.__FUNCTION__] = $filedate;
431 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.");
436 if ($foundintocache) {
437 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" read data from cache file ".$newpathofdestfile.
" ".$filedate.
".");
438 $data = json_decode(file_get_contents($newpathofdestfile),
true);
441 while ($year <= $endyear) {
448 for ($i = 0; $i < 12; $i++) {
449 $data[$i][] = $datay[$endyear][$i][0];
451 while ($year <= $endyear) {
452 $data[$i][] = $datay[$year][$i][1];
459 if (empty($foundintocache) && ($cachedelay > 0 || $cachedelay == - 1)) {
460 dol_syslog(get_class($this).
'::'.__FUNCTION__.
" save cache file ".$newpathofdestfile.
" onto disk.");
464 $fp = fopen($newpathofdestfile,
'w');
465 fwrite($fp, json_encode($data));
467 if (!empty($conf->global->MAIN_UMASK)) {
468 $newmask = $conf->global->MAIN_UMASK;
470 @chmod($newpathofdestfile, octdec($newmask));
472 $this->lastfetchdate[get_class($this).
'_'.__FUNCTION__] = $nowgmt;
489 $this->yearmonth = $year;
491 $sql =
"SELECT date_format(t.datec,'%m') as dm, count(t.opp_amount)";
492 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
497 $sql .=
" GROUP BY dm";
498 $sql .= $this->
db->order(
'dm',
'DESC');
504 $sql =
"SELECT date_format(t.datec,'%m') as dm, count(t.opp_amount)";
505 $sql .=
" FROM ".MAIN_DB_PREFIX.
"projet as t";
510 $sql .=
" GROUP BY dm";
511 $sql .= $this->
db->order(
'dm',
'DESC');
514 $this->yearmonth = 0;
516 $res_only_wined = $this->
_getNbByMonth($year, $sql, $format);
520 foreach ($res_total as $key => $total_row) {
522 if (!empty($total_row[1])) {
523 $res[$key] = array($total_row[0], (100 * $res_only_wined[$key][1]) / $total_row[1]);
525 $res[$key] = array($total_row[0], 0);
539 $sql =
"SELECT date_format(datef,'%m') as dm, AVG(f.".$this->field.
")";
540 $sql .=
" FROM ".$this->from;
543 $sql .=
" AND ".$this->where;
544 $sql .=
" GROUP BY dm";
545 $sql .= $this->
db->order(
'dm',
'DESC');