projectstats.class.php 19 KB

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