1<?php 2/* Lead 3 * Copyright (C) 2014-2015 Florian HENRY <florian.henry@open-concept.pro> 4 * 5 * This program is free software; you can redistribute it and/or modify 6 * it under the terms of the GNU General Public License as published by 7 * the Free Software Foundation; either version 3 of the License, or 8 * (at your option) any later version. 9 * 10 * This program is distributed in the hope that it will be useful, 11 * but WITHOUT ANY WARRANTY; without even the implied warranty of 12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13 * GNU General Public License for more details. 14 * 15 * You should have received a copy of the GNU General Public License 16 * along with this program. If not, see <https://www.gnu.org/licenses/>. 17 */ 18include_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php'; 19include_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php'; 20 21 22/** 23 * Class to manage statistics on projects 24 */ 25class ProjectStats extends Stats 26{ 27 private $project; 28 public $userid; 29 public $socid; 30 public $year; 31 32 /** 33 * Constructor 34 * 35 * @param DoliDB $db Database handler 36 */ 37 public function __construct($db) 38 { 39 global $conf, $user; 40 41 $this->db = $db; 42 43 require_once 'project.class.php'; 44 $this->project = new Project($this->db); 45 } 46 47 48 /** 49 * Return all leads grouped by opportunity status. 50 * Warning: There is no filter on WON/LOST because we want this for statistics. 51 * 52 * @param int $limit Limit results 53 * @return array|int Array with value or -1 if error 54 * @throws Exception 55 */ 56 public function getAllProjectByStatus($limit = 5) 57 { 58 global $conf, $user, $langs; 59 60 $datay = array(); 61 62 $sql = "SELECT"; 63 $sql .= " SUM(t.opp_amount), t.fk_opp_status, cls.code, cls.label"; 64 $sql .= " FROM ".MAIN_DB_PREFIX."projet as t"; 65 // No check is done on company permission because readability is managed by public status of project and assignement. 66 //if (! $user->rights->societe->client->voir && ! $user->socid) 67 // $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as sc ON sc.fk_soc=t.fk_soc AND sc.fk_user = ".((int) $user->id); 68 $sql .= ", ".MAIN_DB_PREFIX."c_lead_status as cls"; 69 $sql .= $this->buildWhere(); 70 // For external user, no check is done on company permission because readability is managed by public status of project and assignement. 71 //if ($socid > 0) $sql.= " AND t.fk_soc = ".((int) $socid); 72 // No check is done on company permission because readability is managed by public status of project and assignement. 73 //if (! $user->rights->societe->client->voir && ! $socid) $sql.= " AND ((s.rowid = sc.fk_soc AND sc.fk_user = ".((int) $user->id).") OR (s.rowid IS NULL))"; 74 $sql .= " AND t.fk_opp_status = cls.rowid"; 75 $sql .= " AND t.fk_statut <> 0"; // We want historic also, so all projects not draft 76 $sql .= " GROUP BY t.fk_opp_status, cls.code, cls.label"; 77 78 $result = array(); 79 80 dol_syslog(get_class($this).'::'.__METHOD__."", LOG_DEBUG); 81 $resql = $this->db->query($sql); 82 if ($resql) { 83 $num = $this->db->num_rows($resql); 84 $i = 0; 85 $other = 0; 86 while ($i < $num) { 87 $row = $this->db->fetch_row($resql); 88 if ($i < $limit || $num == $limit) { 89 $label = (($langs->trans("OppStatus".$row[2]) != "OppStatus".$row[2]) ? $langs->trans("OppStatus".$row[2]) : $row[2]); 90 $result[$i] = array( 91 $label.' ('.price(price2num($row[0], 'MT'), 1, $langs, 1, -1, -1, $conf->currency).')', 92 $row[0] 93 ); 94 } else { 95 $other += $row[1]; 96 } 97 $i++; 98 } 99 if ($num > $limit) { 100 $result[$i] = array( 101 $langs->transnoentitiesnoconv("Other"), 102 $other 103 ); 104 } 105 $this->db->free($resql); 106 } else { 107 $this->error = "Error ".$this->db->lasterror(); 108 dol_syslog(get_class($this).'::'.__METHOD__.' '.$this->error, LOG_ERR); 109 return -1; 110 } 111 112 return $result; 113 } 114 115 /** 116 * Return count, and sum of products 117 * 118 * @return array of values 119 */ 120 public function getAllByYear() 121 { 122 global $conf, $user, $langs; 123 124 $datay = array(); 125 126 $wonlostfilter = 0; // No filter on status WON/LOST 127 128 $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,"; 129 $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"; 130 $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"; 131 // No check is done on company permission because readability is managed by public status of project and assignement. 132 //if (! $user->rights->societe->client->voir && ! $user->soc_id) 133 // $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as sc ON sc.fk_soc=t.fk_soc AND sc.fk_user = ".((int) $user->id); 134 $sql .= $this->buildWhere(); 135 // For external user, no check is done on company permission because readability is managed by public status of project and assignement. 136 //if ($socid > 0) $sql.= " AND t.fk_soc = ".((int) $socid); 137 // No check is done on company permission because readability is managed by public status of project and assignement. 138 //if (! $user->rights->societe->client->voir && ! $socid) $sql.= " AND ((s.rowid = sc.fk_soc AND sc.fk_user = ".((int) $user->id).") OR (s.rowid IS NULL))"; 139 $sql .= " GROUP BY year"; 140 $sql .= $this->db->order('year', 'DESC'); 141 142 return $this->_getAllByYear($sql); 143 } 144 145 146 /** 147 * Build the where part 148 * 149 * @return string 150 */ 151 public function buildWhere() 152 { 153 global $user; 154 155 $sqlwhere_str = ''; 156 $sqlwhere = array(); 157 158 // Get list of project id allowed to user (in a string list separated by coma) 159 $object = new Project($this->db); 160 $projectsListId = ''; 161 if (!$user->rights->projet->all->lire) { 162 $projectsListId = $object->getProjectsAuthorizedForUser($user, 0, 1, $user->socid); 163 } 164 165 $sqlwhere[] = ' t.entity IN ('.getEntity('project').')'; 166 167 if (!empty($this->userid)) { 168 $sqlwhere[] = ' t.fk_user_resp='.$this->userid; 169 } 170 171 // Forced filter on socid is similar to forced filter on project. TODO Use project assignement to allow to not use filter on project 172 if (!empty($this->socid)) { 173 $sqlwhere[] = ' t.fk_soc='.$this->socid; 174 } 175 if (!empty($this->year) && empty($this->yearmonth)) { 176 $sqlwhere[] = " date_format(t.datec,'%Y')='".$this->db->escape($this->year)."'"; 177 } 178 if (!empty($this->yearmonth)) { 179 $sqlwhere[] = " t.datec BETWEEN '".$this->db->idate(dol_get_first_day($this->yearmonth))."' AND '".$this->db->idate(dol_get_last_day($this->yearmonth))."'"; 180 } 181 182 if (!empty($this->status)) { 183 $sqlwhere[] = " t.fk_opp_status IN (".$this->db->sanitize($this->status).")"; 184 } 185 186 if (!$user->rights->projet->all->lire) { 187 $sqlwhere[] = " t.rowid IN (".$this->db->sanitize($projectsListId).")"; // public and assigned to, or restricted to company for external users 188 } 189 190 if (count($sqlwhere) > 0) { 191 $sqlwhere_str = ' WHERE '.implode(' AND ', $sqlwhere); 192 } 193 194 return $sqlwhere_str; 195 } 196 197 /** 198 * Return Project number by month for a year 199 * 200 * @param int $year Year to scan 201 * @param int $format 0=Label of abscissa is a translated text, 1=Label of abscissa is month number, 2=Label of abscissa is first letter of month 202 * @return array Array of values 203 */ 204 public function getNbByMonth($year, $format = 0) 205 { 206 global $user; 207 208 $this->yearmonth = $year; 209 210 $sql = "SELECT date_format(t.datec,'%m') as dm, COUNT(*) as nb"; 211 $sql .= " FROM ".MAIN_DB_PREFIX."projet as t"; 212 // No check is done on company permission because readability is managed by public status of project and assignement. 213 //if (! $user->rights->societe->client->voir && ! $user->soc_id) 214 // $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as sc ON sc.fk_soc=t.fk_soc AND sc.fk_user = ".((int) $user->id); 215 $sql .= $this->buildWhere(); 216 $sql .= " GROUP BY dm"; 217 $sql .= $this->db->order('dm', 'DESC'); 218 219 $this->yearmonth = 0; 220 221 $res = $this->_getNbByMonth($year, $sql, $format); 222 // var_dump($res);print '<br>'; 223 return $res; 224 } 225 226 /** 227 * Return the Project amount by month for a year 228 * 229 * @param int $year Year to scan 230 * @param int $format 0=Label of abscissa is a translated text, 1=Label of abscissa is month number, 2=Label of abscissa is first letter of month 231 * @return array Array with amount by month 232 */ 233 public function getAmountByMonth($year, $format = 0) 234 { 235 global $user; 236 237 $this->yearmonth = $year; 238 239 $sql = "SELECT date_format(t.datec,'%m') as dm, SUM(t.opp_amount)"; 240 $sql .= " FROM ".MAIN_DB_PREFIX."projet as t"; 241 // No check is done on company permission because readability is managed by public status of project and assignement. 242 //if (! $user->rights->societe->client->voir && ! $user->soc_id) 243 // $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as sc ON sc.fk_soc=t.fk_soc AND sc.fk_user = ".((int) $user->id); 244 $sql .= $this->buildWhere(); 245 $sql .= " GROUP BY dm"; 246 $sql .= $this->db->order('dm', 'DESC'); 247 $this->yearmonth = 0; 248 249 $res = $this->_getAmountByMonth($year, $sql, $format); 250 // var_dump($res);print '<br>'; 251 return $res; 252 } 253 254 255 /** 256 * Return amount of elements by month for several years 257 * 258 * @param int $endyear Start year 259 * @param int $startyear End year 260 * @param int $cachedelay Delay we accept for cache file (0=No read, no save of cache, -1=No read but save) 261 * @param int $wonlostfilter Add a filter on status won/lost 262 * @return array Array of values 263 */ 264 public function getWeightedAmountByMonthWithPrevYear($endyear, $startyear, $cachedelay = 0, $wonlostfilter = 1) 265 { 266 global $conf, $user, $langs; 267 268 if ($startyear > $endyear) { 269 return -1; 270 } 271 272 $datay = array(); 273 274 // Search into cache 275 if (!empty($cachedelay)) { 276 include_once DOL_DOCUMENT_ROOT.'/core/lib/files.lib.php'; 277 include_once DOL_DOCUMENT_ROOT.'/core/lib/json.lib.php'; 278 } 279 280 $newpathofdestfile = $conf->user->dir_temp.'/'.get_class($this).'_'.__FUNCTION__.'_'.(empty($this->cachefilesuffix) ? '' : $this->cachefilesuffix.'_').$langs->defaultlang.'_user'.$user->id.'.cache'; 281 $newmask = '0644'; 282 283 $nowgmt = dol_now(); 284 285 $foundintocache = 0; 286 if ($cachedelay > 0) { 287 $filedate = dol_filemtime($newpathofdestfile); 288 if ($filedate >= ($nowgmt - $cachedelay)) { 289 $foundintocache = 1; 290 291 $this->lastfetchdate[get_class($this).'_'.__FUNCTION__] = $filedate; 292 } else { 293 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."); 294 } 295 } 296 297 // Load file into $data 298 if ($foundintocache) { // Cache file found and is not too old 299 dol_syslog(get_class($this).'::'.__FUNCTION__." read data from cache file ".$newpathofdestfile." ".$filedate."."); 300 $data = json_decode(file_get_contents($newpathofdestfile), true); 301 } else { 302 $year = $startyear; 303 while ($year <= $endyear) { 304 $datay[$year] = $this->getWeightedAmountByMonth($year, $wonlostfilter); 305 $year++; 306 } 307 308 $data = array(); 309 // $data = array('xval'=>array(0=>xlabel,1=>yval1,2=>yval2...),...) 310 for ($i = 0; $i < 12; $i++) { 311 $data[$i][] = $datay[$endyear][$i][0]; // set label 312 $year = $startyear; 313 while ($year <= $endyear) { 314 $data[$i][] = $datay[$year][$i][1]; // set yval for x=i 315 $year++; 316 } 317 } 318 } 319 320 // Save cache file 321 if (empty($foundintocache) && ($cachedelay > 0 || $cachedelay == -1)) { 322 dol_syslog(get_class($this).'::'.__FUNCTION__." save cache file ".$newpathofdestfile." onto disk."); 323 if (!dol_is_dir($conf->user->dir_temp)) { 324 dol_mkdir($conf->user->dir_temp); 325 } 326 $fp = fopen($newpathofdestfile, 'w'); 327 if ($fp) { 328 fwrite($fp, json_encode($data)); 329 fclose($fp); 330 if (!empty($conf->global->MAIN_UMASK)) { 331 $newmask = $conf->global->MAIN_UMASK; 332 } 333 @chmod($newpathofdestfile, octdec($newmask)); 334 } else { 335 dol_syslog("Failed to write cache file", LOG_ERR); 336 } 337 $this->lastfetchdate[get_class($this).'_'.__FUNCTION__] = $nowgmt; 338 } 339 340 return $data; 341 } 342 343 344 /** 345 * Return the Project weighted opp amount by month for a year. 346 * 347 * @param int $year Year to scan 348 * @param int $wonlostfilter Add a filter on status won/lost 349 * @return array Array with amount by month 350 */ 351 public function getWeightedAmountByMonth($year, $wonlostfilter = 1) 352 { 353 global $user; 354 355 $this->yearmonth = $year; 356 357 $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)"; 358 $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'; 359 // No check is done on company permission because readability is managed by public status of project and assignement. 360 //if (! $user->rights->societe->client->voir && ! $user->soc_id) 361 // $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as sc ON sc.fk_soc=t.fk_soc AND sc.fk_user = ".((int) $user->id); 362 $sql .= $this->buildWhere(); 363 $sql .= " GROUP BY dm"; 364 $sql .= $this->db->order('dm', 'DESC'); 365 $this->yearmonth = 0; 366 367 $res = $this->_getAmountByMonth($year, $sql); 368 // var_dump($res);print '<br>'; 369 return $res; 370 } 371 372 /** 373 * Return amount of elements by month for several years 374 * 375 * @param int $endyear End year 376 * @param int $startyear Start year 377 * @param int $cachedelay accept for cache file (0=No read, no save of cache, -1=No read but save) 378 * @return array of values 379 */ 380 public function getTransformRateByMonthWithPrevYear($endyear, $startyear, $cachedelay = 0) 381 { 382 global $conf, $user, $langs; 383 384 if ($startyear > $endyear) { 385 return -1; 386 } 387 388 $datay = array(); 389 390 // Search into cache 391 if (!empty($cachedelay)) { 392 include_once DOL_DOCUMENT_ROOT.'/core/lib/files.lib.php'; 393 include_once DOL_DOCUMENT_ROOT.'/core/lib/json.lib.php'; 394 } 395 396 $newpathofdestfile = $conf->user->dir_temp.'/'.get_class($this).'_'.__FUNCTION__.'_'.(empty($this->cachefilesuffix) ? '' : $this->cachefilesuffix.'_').$langs->defaultlang.'_user'.$user->id.'.cache'; 397 $newmask = '0644'; 398 399 $nowgmt = dol_now(); 400 401 $foundintocache = 0; 402 if ($cachedelay > 0) { 403 $filedate = dol_filemtime($newpathofdestfile); 404 if ($filedate >= ($nowgmt - $cachedelay)) { 405 $foundintocache = 1; 406 407 $this->lastfetchdate[get_class($this).'_'.__FUNCTION__] = $filedate; 408 } else { 409 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."); 410 } 411 } 412 413 // Load file into $data 414 if ($foundintocache) { // Cache file found and is not too old 415 dol_syslog(get_class($this).'::'.__FUNCTION__." read data from cache file ".$newpathofdestfile." ".$filedate."."); 416 $data = json_decode(file_get_contents($newpathofdestfile), true); 417 } else { 418 $year = $startyear; 419 while ($year <= $endyear) { 420 $datay[$year] = $this->getTransformRateByMonth($year); 421 $year++; 422 } 423 424 $data = array(); 425 // $data = array('xval'=>array(0=>xlabel,1=>yval1,2=>yval2...),...) 426 for ($i = 0; $i < 12; $i++) { 427 $data[$i][] = $datay[$endyear][$i][0]; // set label 428 $year = $startyear; 429 while ($year <= $endyear) { 430 $data[$i][] = $datay[$year][$i][1]; // set yval for x=i 431 $year++; 432 } 433 } 434 } 435 436 // Save cache file 437 if (empty($foundintocache) && ($cachedelay > 0 || $cachedelay == - 1)) { 438 dol_syslog(get_class($this).'::'.__FUNCTION__." save cache file ".$newpathofdestfile." onto disk."); 439 if (!dol_is_dir($conf->user->dir_temp)) { 440 dol_mkdir($conf->user->dir_temp); 441 } 442 $fp = fopen($newpathofdestfile, 'w'); 443 fwrite($fp, json_encode($data)); 444 fclose($fp); 445 if (!empty($conf->global->MAIN_UMASK)) { 446 $newmask = $conf->global->MAIN_UMASK; 447 } 448 @chmod($newpathofdestfile, octdec($newmask)); 449 450 $this->lastfetchdate[get_class($this).'_'.__FUNCTION__] = $nowgmt; 451 } 452 453 return $data; 454 } 455 456 /** 457 * Return the Project transformation rate by month for a year 458 * 459 * @param int $year Year to scan 460 * @param int $format 0=Label of abscissa is a translated text, 1=Label of abscissa is month number, 2=Label of abscissa is first letter of month 461 * @return array Array with amount by month 462 */ 463 public function getTransformRateByMonth($year, $format = 0) 464 { 465 global $user; 466 467 $this->yearmonth = $year; 468 469 $sql = "SELECT date_format(t.datec,'%m') as dm, count(t.opp_amount)"; 470 $sql .= " FROM ".MAIN_DB_PREFIX."projet as t"; 471 // No check is done on company permission because readability is managed by public status of project and assignement. 472 //if (! $user->rights->societe->client->voir && ! $user->soc_id) 473 // $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as sc ON sc.fk_soc=t.fk_soc AND sc.fk_user = ".((int) $user->id); 474 $sql .= $this->buildWhere(); 475 $sql .= " GROUP BY dm"; 476 $sql .= $this->db->order('dm', 'DESC'); 477 478 $res_total = $this->_getNbByMonth($year, $sql, $format); 479 480 $this->status = 6; 481 482 $sql = "SELECT date_format(t.datec,'%m') as dm, count(t.opp_amount)"; 483 $sql .= " FROM ".MAIN_DB_PREFIX."projet as t"; 484 // No check is done on company permission because readability is managed by public status of project and assignement. 485 //if (! $user->rights->societe->client->voir && ! $user->soc_id) 486 // $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as sc ON sc.fk_soc=t.fk_soc AND sc.fk_user = ".((int) $user->id); 487 $sql .= $this->buildWhere(); 488 $sql .= " GROUP BY dm"; 489 $sql .= $this->db->order('dm', 'DESC'); 490 491 $this->status = 0; 492 $this->yearmonth = 0; 493 494 $res_only_wined = $this->_getNbByMonth($year, $sql, $format); 495 496 $res = array(); 497 498 foreach ($res_total as $key => $total_row) { 499 //var_dump($total_row); 500 if (!empty($total_row[1])) { 501 $res[$key] = array($total_row[0], (100 * $res_only_wined[$key][1]) / $total_row[1]); 502 } else { 503 $res[$key] = array($total_row[0], 0); 504 } 505 } 506 // var_dump($res);print '<br>'; 507 return $res; 508 } 509} 510