customreports.php 32 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799
  1. <?php
  2. /* Copyright (C) 2020 Laurent Destailleur <eldy@users.sourceforge.net>
  3. *
  4. * This program is free software; you can redistribute it and/or modify
  5. * it under the terms of the GNU General Public License as published by
  6. * the Free Software Foundation; either version 3 of the License, or
  7. * (at your option) any later version.
  8. *
  9. * This program is distributed in the hope that it will be useful,
  10. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  12. * GNU General Public License for more details.
  13. *
  14. * You should have received a copy of the GNU General Public License
  15. * along with this program. If not, see <http://www.gnu.org/licenses/>.
  16. *
  17. * Note: This tool can be included into a list page with :
  18. * define('USE_CUSTOM_REPORT_AS_INCLUDE', 1);
  19. * include DOL_DOCUMENT_ROOT.'/core/customreports.php';
  20. */
  21. /**
  22. * \file htdocs/core/customreports.php
  23. * \ingroup core
  24. * \brief Page to make custom reports
  25. */
  26. if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
  27. require '../main.inc.php';
  28. // Get parameters
  29. $action = GETPOST('action', 'aZ09') ?GETPOST('action', 'aZ09') : 'view'; // The action 'add', 'create', 'edit', 'update', 'view', ...
  30. $massaction = GETPOST('massaction', 'alpha'); // The bulk action (combo box choice into lists)
  31. $mode = GETPOST('mode', 'alpha') ? GETPOST('mode', 'alpha') : 'graph';
  32. $objecttype = GETPOST('objecttype', 'aZ09');
  33. $tabfamily = GETPOST('tabfamily', 'aZ09');
  34. if (empty($objecttype)) {
  35. $objecttype = 'thirdparty';
  36. }
  37. $search_filters = GETPOST('search_filters', 'array');
  38. $search_measures = GETPOST('search_measures', 'array');
  39. //$search_xaxis = GETPOST('search_xaxis', 'array');
  40. if (GETPOST('search_xaxis', 'alpha') && GETPOST('search_xaxis', 'alpha') != '-1') {
  41. $search_xaxis = array(GETPOST('search_xaxis', 'alpha'));
  42. } else {
  43. $search_xaxis = array();
  44. }
  45. //$search_groupby = GETPOST('search_groupby', 'array');
  46. if (GETPOST('search_groupby', 'alpha') && GETPOST('search_groupby', 'alpha') != '-1') {
  47. $search_groupby = array(GETPOST('search_groupby', 'alpha'));
  48. } else {
  49. $search_groupby = array();
  50. }
  51. $search_yaxis = GETPOST('search_yaxis', 'array');
  52. $search_graph = GETPOST('search_graph', 'none');
  53. // Load variable for pagination
  54. $limit = GETPOST('limit', 'int') ? GETPOST('limit', 'int') : $conf->liste_limit;
  55. $sortfield = GETPOST('sortfield', 'aZ09comma');
  56. $sortorder = GETPOST('sortorder', 'aZ09comma');
  57. $page = GETPOSTISSET('pageplusone') ? (GETPOST('pageplusone') - 1) : GETPOST("page", 'int');
  58. if (empty($page) || $page == -1 || GETPOST('button_search', 'alpha') || GETPOST('button_removefilter', 'alpha') || (empty($toselect) && $massaction === '0')) {
  59. $page = 0;
  60. } // If $page is not defined, or '' or -1 or if we click on clear filters or if we select empty mass action
  61. $offset = $limit * $page;
  62. $pageprev = $page - 1;
  63. $pagenext = $page + 1;
  64. $diroutputmassaction = $conf->user->dir_temp.'/'.$user->id.'/customreport';
  65. }
  66. require_once DOL_DOCUMENT_ROOT."/core/lib/admin.lib.php";
  67. require_once DOL_DOCUMENT_ROOT."/core/lib/company.lib.php";
  68. require_once DOL_DOCUMENT_ROOT."/core/class/dolgraph.class.php";
  69. require_once DOL_DOCUMENT_ROOT."/core/class/doleditor.class.php";
  70. require_once DOL_DOCUMENT_ROOT."/core/class/html.formother.class.php";
  71. // Load traductions files requiredby by page
  72. $langs->loadLangs(array("companies", "other", "exports", "sendings"));
  73. $extrafields = new ExtraFields($db);
  74. $hookmanager->initHooks(array('customreport')); // Note that conf->hooks_modules contains array
  75. $title = '';
  76. $picto = '';
  77. $head = array();
  78. $object = null;
  79. $ObjectClassName = '';
  80. // Objects available by default
  81. $arrayoftype = array(
  82. 'thirdparty' => array('label' => 'ThirdParties', 'ObjectClassName' => 'Societe', 'enabled' => $conf->societe->enabled, 'ClassPath' => "/societe/class/societe.class.php"),
  83. 'contact' => array('label' => 'Contacts', 'ObjectClassName' => 'Contact', 'enabled' => $conf->societe->enabled, 'ClassPath' => "/contact/class/contact.class.php"),
  84. 'proposal' => array('label' => 'Proposals', 'ObjectClassName' => 'Propal', 'enabled' => $conf->propal->enabled, 'ClassPath' => "/comm/propal/class/propal.class.php"),
  85. 'order' => array('label' => 'Orders', 'ObjectClassName' => 'Commande', 'enabled' => $conf->commande->enabled, 'ClassPath' => "/commande/class/commande.class.php"),
  86. 'invoice' => array('label' => 'Invoices', 'ObjectClassName' => 'Facture', 'enabled' => $conf->facture->enabled, 'ClassPath' => "/compta/facture/class/facture.class.php"),
  87. 'invoice_template'=>array('label' => 'PredefinedInvoices', 'ObjectClassName' => 'FactureRec', 'enabled' => $conf->facture->enabled, 'ClassPath' => "/compta/class/facturerec.class.php", 'langs'=>'bills'),
  88. 'contract' => array('label' => 'Contracts', 'ObjectClassName' => 'Contrat', 'enabled' => $conf->contrat->enabled, 'ClassPath' => "/contrat/class/contrat.class.php", 'langs'=>'contract'),
  89. 'bom' => array('label' => 'BOM', 'ObjectClassName' => 'Bom', 'enabled' => $conf->bom->enabled),
  90. 'mo' => array('label' => 'MO', 'ObjectClassName' => 'Mo', 'enabled' => $conf->mrp->enabled, 'ClassPath' => "/mrp/class/mo.class.php"),
  91. 'ticket' => array('label' => 'Ticket', 'ObjectClassName' => 'Ticket', 'enabled' => $conf->ticket->enabled),
  92. 'member' => array('label' => 'Adherent', 'ObjectClassName' => 'Adherent', 'enabled' => $conf->adherent->enabled, 'ClassPath' => "/adherents/class/adherent.class.php", 'langs'=>'members'),
  93. 'cotisation' => array('label' => 'Subscriptions', 'ObjectClassName' => 'Subscription', 'enabled' => $conf->adherent->enabled, 'ClassPath' => "/adherents/class/subscription.class.php", 'langs'=>'members'),
  94. );
  95. // Complete $arrayoftype by external modules
  96. $parameters = array('objecttype'=>$objecttype, 'tabfamily'=>$tabfamily);
  97. $reshook = $hookmanager->executeHooks('loadDataForCustomReports', $parameters, $object, $action); // Note that $action and $object may have been modified by some hooks
  98. if ($reshook < 0) {
  99. setEventMessages($hookmanager->error, $hookmanager->errors, 'errors');
  100. } elseif (is_array($hookmanager->resArray)) {
  101. if (!empty($hookmanager->resArray['title'])) { // Add entries for tabs
  102. $title = $hookmanager->resArray['title'];
  103. }
  104. if (!empty($hookmanager->resArray['picto'])) { // Add entries for tabs
  105. $picto = $hookmanager->resArray['picto'];
  106. }
  107. if (!empty($hookmanager->resArray['head'])) { // Add entries for tabs
  108. $head = array_merge($head, $hookmanager->resArray['head']);
  109. }
  110. if (!empty($hookmanager->resArray['arrayoftype'])) { // Add entries from hook
  111. foreach ($hookmanager->resArray['arrayoftype'] as $key => $val) {
  112. $arrayoftype[$key] = $val;
  113. }
  114. }
  115. }
  116. if ($objecttype) {
  117. try {
  118. if (!empty($arrayoftype[$objecttype]['ClassPath'])) {
  119. dol_include_once($arrayoftype[$objecttype]['ClassPath']);
  120. } else {
  121. dol_include_once("/".$objecttype."/class/".$objecttype.".class.php");
  122. }
  123. $ObjectClassName = $arrayoftype[$objecttype]['ObjectClassName'];
  124. $object = new $ObjectClassName($db);
  125. } catch (Exception $e) {
  126. print 'Failed to load class for type '.$objecttype;
  127. }
  128. }
  129. // Security check
  130. $socid = 0;
  131. if ($user->socid > 0) { // Protection if external user
  132. //$socid = $user->socid;
  133. accessforbidden();
  134. }
  135. // Fetch optionals attributes and labels
  136. $extrafields->fetch_name_optionals_label($object->table_element);
  137. //$extrafields->fetch_name_optionals_label($object->table_element_line);
  138. $search_array_options = $extrafields->getOptionalsFromPost($object->table_element, '', 'search_');
  139. $search_component_params = array('');
  140. $MAXUNIQUEVALFORGROUP = 20;
  141. $MAXMEASURESINBARGRAPH = 20;
  142. $YYYY = substr($langs->trans("Year"), 0, 1).substr($langs->trans("Year"), 0, 1).substr($langs->trans("Year"), 0, 1).substr($langs->trans("Year"), 0, 1);
  143. $MM = substr($langs->trans("Month"), 0, 1).substr($langs->trans("Month"), 0, 1);
  144. $DD = substr($langs->trans("Day"), 0, 1).substr($langs->trans("Day"), 0, 1);
  145. $HH = substr($langs->trans("Hour"), 0, 1).substr($langs->trans("Hour"), 0, 1);
  146. $MI = substr($langs->trans("Minute"), 0, 1).substr($langs->trans("Minute"), 0, 1);
  147. $SS = substr($langs->trans("Second"), 0, 1).substr($langs->trans("Second"), 0, 1);
  148. $arrayofmesures = array('t.count'=>'Count');
  149. $arrayofxaxis = array();
  150. $arrayofgroupby = array();
  151. $arrayofyaxis = array();
  152. $arrayofvaluesforgroupby = array();
  153. $result = restrictedArea($user, $object->element, 0, '');
  154. /*
  155. * Actions
  156. */
  157. /*
  158. * View
  159. */
  160. $form = new Form($db);
  161. $formother = new FormOther($db);
  162. if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
  163. llxHeader('', $langs->transnoentitiesnoconv('CustomReports'), '');
  164. print dol_get_fiche_head($head, 'customreports', $title, -1, $picto);
  165. }
  166. // Check parameters
  167. if ($action == 'viewgraph') {
  168. if (!count($search_measures)) {
  169. setEventMessages($langs->trans("AtLeastOneMeasureIsRequired"), null, 'warnings');
  170. } elseif ($mode == 'graph' && count($search_xaxis) > 1) {
  171. setEventMessages($langs->trans("OnlyOneFieldForXAxisIsPossible"), null, 'warnings');
  172. $search_xaxis = array(0 => $search_xaxis[0]);
  173. }
  174. if (count($search_groupby) >= 2) {
  175. setEventMessages($langs->trans("ErrorOnlyOneFieldForGroupByIsPossible"), null, 'warnings');
  176. $search_groupby = array(0 => $search_groupby[0]);
  177. }
  178. if (!count($search_xaxis)) {
  179. setEventMessages($langs->trans("AtLeastOneXAxisIsRequired"), null, 'warnings');
  180. } elseif ($mode == 'graph' && $search_graph == 'bars' && count($search_measures) > $MAXMEASURESINBARGRAPH) {
  181. $langs->load("errors");
  182. setEventMessages($langs->trans("GraphInBarsAreLimitedToNMeasures", $MAXMEASURESINBARGRAPH), null, 'warnings');
  183. $search_graph = 'lines';
  184. }
  185. }
  186. // Get all possible values of fields when a 'group by' is set, and save this into $arrayofvaluesforgroupby
  187. // $arrayofvaluesforgroupby will be used to forge lael of each grouped series
  188. if (is_array($search_groupby) && count($search_groupby)) {
  189. foreach ($search_groupby as $gkey => $gval) {
  190. $gvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $gval);
  191. if (preg_match('/\-year$/', $search_groupby[$gkey])) {
  192. $tmpval = preg_replace('/\-year$/', '', $search_groupby[$gkey]);
  193. $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y')";
  194. } elseif (preg_match('/\-month$/', $search_groupby[$gkey])) {
  195. $tmpval = preg_replace('/\-month$/', '', $search_groupby[$gkey]);
  196. $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y-%m')";
  197. } elseif (preg_match('/\-day$/', $search_groupby[$gkey])) {
  198. $tmpval = preg_replace('/\-day$/', '', $search_groupby[$gkey]);
  199. $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d')";
  200. } else {
  201. $fieldtocount = $search_groupby[$gkey];
  202. }
  203. $sql = 'SELECT DISTINCT '.$fieldtocount.' as val';
  204. if (strpos($fieldtocount, 'te.') === 0) {
  205. $sql .= ' FROM '.MAIN_DB_PREFIX.$object->table_element.'_extrafields as te';
  206. } else {
  207. $sql .= ' FROM '.MAIN_DB_PREFIX.$object->table_element.' as t';
  208. }
  209. // TODO Add the where here
  210. $sql .= ' LIMIT '.($MAXUNIQUEVALFORGROUP + 1);
  211. //print $sql;
  212. $resql = $db->query($sql);
  213. if (!$resql) {
  214. dol_print_error($db);
  215. }
  216. while ($obj = $db->fetch_object($resql)) {
  217. if (is_null($obj->val)) {
  218. $keytouse = '__NULL__';
  219. $valuetranslated = $langs->transnoentitiesnoconv("NotDefined");
  220. } elseif ($obj->val === '') {
  221. $keytouse = '';
  222. $valuetranslated = $langs->transnoentitiesnoconv("Empty");
  223. } else {
  224. $keytouse = (string) $obj->val;
  225. $valuetranslated = $obj->val;
  226. }
  227. $regs = array();
  228. if (!empty($object->fields[$gvalwithoutprefix]['arrayofkeyval'])) {
  229. $valuetranslated = $object->fields[$gvalwithoutprefix]['arrayofkeyval'][$obj->val];
  230. if (is_null($valuetranslated)) {
  231. $valuetranslated = $langs->transnoentitiesnoconv("UndefinedKey");
  232. }
  233. $valuetranslated = $langs->trans($valuetranslated);
  234. } elseif (preg_match('/integer:([^:]+):([^:]+)$/', $object->fields[$gvalwithoutprefix]['type'], $regs)) {
  235. $classname = $regs[1];
  236. $classpath = $regs[2];
  237. dol_include_once($classpath);
  238. if (class_exists($classname)) {
  239. $tmpobject = new $classname($db);
  240. $tmpobject->fetch($obj->val);
  241. foreach ($tmpobject->fields as $fieldkey => $field) {
  242. if ($field['showoncombobox']) {
  243. $valuetranslated = $tmpobject->$fieldkey;
  244. //if ($valuetranslated == '-') $valuetranslated = $langs->transnoentitiesnoconv("Unknown")
  245. break;
  246. }
  247. }
  248. //$valuetranslated = $tmpobject->ref.'eee';
  249. }
  250. }
  251. $arrayofvaluesforgroupby['g_'.$gkey][$keytouse] = $valuetranslated;
  252. }
  253. asort($arrayofvaluesforgroupby['g_'.$gkey]);
  254. if (count($arrayofvaluesforgroupby['g_'.$gkey]) > $MAXUNIQUEVALFORGROUP) {
  255. $langs->load("errors");
  256. if (strpos($fieldtocount, 'te.') === 0) {
  257. //if (!empty($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix])) {
  258. // $langs->load($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix]);
  259. //}
  260. $keyforlabeloffield = $extrafields->attributes[$object->table_element]['label'][$gvalwithoutprefix];
  261. } else {
  262. $keyforlabeloffield = $object->fields[$gvalwithoutprefix]['label'];
  263. }
  264. //var_dump($gkey.' '.$gval.' '.$gvalwithoutprefix);
  265. $gvalwithoutprefix = preg_replace('/\-(year|month|day)/', '', $gvalwithoutprefix);
  266. $labeloffield = $langs->transnoentitiesnoconv($keyforlabeloffield);
  267. setEventMessages($langs->trans("ErrorTooManyDifferentValueForSelectedGroupBy", $MAXUNIQUEVALFORGROUP, $labeloffield), null, 'warnings');
  268. $search_groupby = array();
  269. }
  270. $db->free($resql);
  271. }
  272. }
  273. //var_dump($arrayofvaluesforgroupby);exit;
  274. $tmparray = dol_getdate(dol_now());
  275. $endyear = $tmparray['year'];
  276. $endmonth = $tmparray['mon'];
  277. $datelastday = dol_get_last_day($endyear, $endmonth, 1);
  278. $startyear = $endyear - 2;
  279. $param = '';
  280. print '<form method="post" action="'.$_SERVER['PHP_SELF'].'">';
  281. print '<input type="hidden" name="token" value="'.newToken().'">';
  282. print '<input type="hidden" name="action" value="viewgraph">';
  283. print '<input type="hidden" name="tabfamily" value="'.$tabfamily.'">';
  284. print '<div class="liste_titre liste_titre_bydiv centpercent">';
  285. // Select object
  286. print '<div class="divadvancedsearchfield center floatnone">';
  287. print '<div class="inline-block"><span class="opacitymedium">'.$langs->trans("StatisticsOn").'</span></div> ';
  288. $newarrayoftype = array();
  289. foreach ($arrayoftype as $key => $val) {
  290. if (dol_eval($val['enabled'], 1)) {
  291. $newarrayoftype[$key] = $arrayoftype[$key];
  292. }
  293. if ($val['langs']) {
  294. $langs->load($val['langs']);
  295. }
  296. }
  297. print $form->selectarray('objecttype', $newarrayoftype, $objecttype, 0, 0, 0, '', 1, 0, 0, '', 'minwidth200', 1);
  298. if (empty($conf->use_javascript_ajax)) {
  299. print '<input type="submit" class="button buttongen" name="changeobjecttype" value="'.$langs->trans("Refresh").'">';
  300. } else {
  301. print '<script type="text/javascript" language="javascript">
  302. jQuery(document).ready(function() {
  303. jQuery("#objecttype").change(function() {
  304. console.log("Reload for "+jQuery("#objecttype").val());
  305. location.href = "'.$_SERVER["PHP_SELF"].'?objecttype="+jQuery("#objecttype").val()+"'.($tabfamily ? '&tabfamily='.$tabfamily : '').'";
  306. });
  307. });
  308. </script>';
  309. }
  310. print '</div><div class="clearboth"></div>';
  311. // Add Filter
  312. print '<div class="divadvancedsearchfield quatrevingtpercent">';
  313. print $form->searchComponent(array($object->element => $object->fields), $search_component_params);
  314. print '</div>';
  315. // Add measures into array
  316. print '<div class="divadvancedsearchfield clearboth">';
  317. foreach ($object->fields as $key => $val) {
  318. if (!empty($val['isameasure']) && (!isset($val['enabled']) || dol_eval($val['enabled'], 1))) {
  319. $arrayofmesures['t.'.$key.'-sum'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Sum").')</span>';
  320. $arrayofmesures['t.'.$key.'-average'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Average").')</span>';
  321. $arrayofmesures['t.'.$key.'-min'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Minimum").')</span>';
  322. $arrayofmesures['t.'.$key.'-max'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Maximum").')</span>';
  323. }
  324. }
  325. // Add extrafields to Measures
  326. if ($object->isextrafieldmanaged) {
  327. foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) {
  328. if (!empty($extrafields->attributes[$object->table_element]['totalizable'][$key]) && (!isset($extrafields->attributes[$object->table_element]['enabled'][$key]) || dol_eval($extrafields->attributes[$object->table_element]['enabled'][$key], 1))) {
  329. $arrayofmesures['te.'.$key.'-sum'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Sum").')</span>';
  330. $arrayofmesures['te.'.$key.'-average'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Average").')</span>';
  331. $arrayofmesures['te.'.$key.'-min'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Minimum").')</span>';
  332. $arrayofmesures['te.'.$key.'-max'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Maximum").')</span>';
  333. }
  334. }
  335. }
  336. print '<div class="inline-block"><span class="fas fa-chart-line paddingright" title="'.$langs->trans("Measures").'"></span>'.$langs->trans("Measures").'</div> ';
  337. print $form->multiselectarray('search_measures', $arrayofmesures, $search_measures, 0, 0, 'minwidth400', 1);
  338. print '</div>';
  339. // Group by
  340. print '<div class="divadvancedsearchfield">';
  341. print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-horizontal paddingright" title="'.$langs->trans("GroupBy").'"></span>'.$langs->trans("GroupBy").'</div> ';
  342. print $formother->selectGroupByField($object, $search_groupby, $arrayofgroupby);
  343. print '</div>';
  344. // XAxis
  345. print '<div class="divadvancedsearchfield">';
  346. print '<div class="inline-block"><span class="fas fa-ruler-horizontal paddingright" title="'.$langs->trans("XAxis").'"></span>'.$langs->trans("XAxis").'</div> ';
  347. print $formother->selectXAxisField($object, $search_xaxis, $arrayofxaxis);
  348. print '</div>';
  349. if ($mode == 'grid') {
  350. // YAxis
  351. print '<div class="divadvancedsearchfield">';
  352. foreach ($object->fields as $key => $val) {
  353. if (empty($val['measure']) && (!isset($val['enabled']) || dol_eval($val['enabled'], 1))) {
  354. if (in_array($key, array('id', 'rowid', 'entity', 'last_main_doc', 'extraparams'))) {
  355. continue;
  356. }
  357. if (preg_match('/^fk_/', $key)) {
  358. continue;
  359. }
  360. if (in_array($val['type'], array('html', 'text'))) {
  361. continue;
  362. }
  363. if (in_array($val['type'], array('timestamp', 'date', 'datetime'))) {
  364. $arrayofyaxis['t.'.$key.'-year'] = array('label' => $langs->trans($val['label']).' ('.$YYYY.')', 'position' => $val['position']);
  365. $arrayofyaxis['t.'.$key.'-month'] = array('label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.')', 'position' => $val['position']);
  366. $arrayofyaxis['t.'.$key.'-day'] = array('label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.'-'.$DD.')', 'position' => $val['position']);
  367. } else {
  368. $arrayofyaxis['t.'.$key] = array('label' => $val['label'], 'position' => (int) $val['position']);
  369. }
  370. }
  371. // Add measure from extrafields
  372. if ($object->isextrafieldmanaged) {
  373. foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) {
  374. if (!empty($extrafields->attributes[$object->table_element]['totalizable'][$key]) && (!isset($extrafields->attributes[$object->table_element]['enabled'][$key]) || dol_eval($extrafields->attributes[$object->table_element]['enabled'][$key], 1))) {
  375. $arrayofyaxis['te.'.$key] = array('label' => $extrafields->attributes[$object->table_element]['label'][$key], 'position' => (int) $extrafields->attributes[$object->table_element]['pos'][$key]);
  376. }
  377. }
  378. }
  379. }
  380. $arrayofyaxis = dol_sort_array($arrayofyaxis, 'position');
  381. $arrayofyaxislabel = array();
  382. foreach ($arrayofyaxis as $key => $val) {
  383. $arrayofyaxislabel[$key] = $val['label'];
  384. }
  385. print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-vertical paddingright" title="'.$langs->trans("YAxis").'"></span>'.$langs->trans("YAxis").'</div> ';
  386. print $form->multiselectarray('search_yaxis', $arrayofyaxislabel, $search_yaxis, 0, 0, 'minwidth100', 1);
  387. print '</div>';
  388. }
  389. if ($mode == 'graph') {
  390. print '<div class="divadvancedsearchfield">';
  391. $arrayofgraphs = array('bars' => 'Bars', 'lines' => 'Lines'); // also 'pies'
  392. print '<div class="inline-block opacitymedium"><span class="fas fa-chart-area paddingright" title="'.$langs->trans("Graph").'"></span>'.$langs->trans("Graph").'</div> ';
  393. print $form->selectarray('search_graph', $arrayofgraphs, $search_graph, 0, 0, 0, 'minwidth100', 1);
  394. print '</div>';
  395. }
  396. print '<div class="divadvancedsearchfield">';
  397. print '<input type="submit" class="button buttongen" value="'.$langs->trans("Refresh").'">';
  398. print '</div>';
  399. print '</div>';
  400. print '</form>';
  401. // Generate the SQL request
  402. $sql = '';
  403. if (!empty($search_measures) && !empty($search_xaxis)) {
  404. $fieldid = 'rowid';
  405. $sql = 'SELECT ';
  406. foreach ($search_xaxis as $key => $val) {
  407. if (preg_match('/\-year$/', $val)) {
  408. $tmpval = preg_replace('/\-year$/', '', $val);
  409. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y') as x_".$key.', ';
  410. } elseif (preg_match('/\-month$/', $val)) {
  411. $tmpval = preg_replace('/\-month$/', '', $val);
  412. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m') as x_".$key.', ';
  413. } elseif (preg_match('/\-day$/', $val)) {
  414. $tmpval = preg_replace('/\-day$/', '', $val);
  415. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d') as x_".$key.', ';
  416. } else {
  417. $sql .= $val.' as x_'.$key.', ';
  418. }
  419. }
  420. foreach ($search_groupby as $key => $val) {
  421. if (preg_match('/\-year$/', $val)) {
  422. $tmpval = preg_replace('/\-year$/', '', $val);
  423. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y') as g_".$key.', ';
  424. } elseif (preg_match('/\-month$/', $val)) {
  425. $tmpval = preg_replace('/\-month$/', '', $val);
  426. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m') as g_".$key.', ';
  427. } elseif (preg_match('/\-day$/', $val)) {
  428. $tmpval = preg_replace('/\-day$/', '', $val);
  429. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d') as g_".$key.', ';
  430. } else {
  431. $sql .= $val.' as g_'.$key.', ';
  432. }
  433. }
  434. foreach ($search_measures as $key => $val) {
  435. if ($val == 't.count') {
  436. $sql .= 'COUNT(t.'.$fieldid.') as y_'.$key.', ';
  437. } elseif (preg_match('/\-sum$/', $val)) {
  438. $tmpval = preg_replace('/\-sum$/', '', $val);
  439. $sql .= 'SUM('.$db->ifsql($tmpval.' IS NULL', '0', $tmpval).') as y_'.$key.', ';
  440. } elseif (preg_match('/\-average$/', $val)) {
  441. $tmpval = preg_replace('/\-average$/', '', $val);
  442. $sql .= 'AVG('.$db->ifsql($tmpval.' IS NULL', '0', $tmpval).') as y_'.$key.', ';
  443. } elseif (preg_match('/\-min$/', $val)) {
  444. $tmpval = preg_replace('/\-min$/', '', $val);
  445. $sql .= 'MIN('.$db->ifsql($tmpval.' IS NULL', '0', $tmpval).') as y_'.$key.', ';
  446. } elseif (preg_match('/\-max$/', $val)) {
  447. $tmpval = preg_replace('/\-max$/', '', $val);
  448. $sql .= 'MAX('.$db->ifsql($tmpval.' IS NULL', '0', $tmpval).') as y_'.$key.', ';
  449. }
  450. }
  451. $sql = preg_replace('/,\s*$/', '', $sql);
  452. $sql .= ' FROM '.MAIN_DB_PREFIX.$object->table_element.' as t';
  453. // Add measure from extrafields
  454. if ($object->isextrafieldmanaged) {
  455. $sql .= ' LEFT JOIN '.MAIN_DB_PREFIX.$object->table_element.'_extrafields as te ON te.fk_object = t.'.$fieldid;
  456. }
  457. if ($object->ismultientitymanaged) {
  458. if ($object->ismultientitymanaged == 1) {
  459. // Nothing here
  460. } else {
  461. $tmparray = explode('@', $object->ismultientitymanaged);
  462. $sql .= ' INNER JOIN '.MAIN_DB_PREFIX.$tmparray[1].' as parenttable ON t.'.$tmparray[0].' = parenttable.rowid';
  463. $sql .= ' AND parenttable.entity IN ('.getEntity($tmparray[1]).')';
  464. }
  465. }
  466. $sql .= ' WHERE 1 = 1';
  467. if ($object->ismultientitymanaged == 1) {
  468. $sql .= ' AND entity IN ('.getEntity($object->element).')';
  469. }
  470. foreach ($search_filters as $key => $val) {
  471. // TODO Add the where here
  472. }
  473. $sql .= ' GROUP BY ';
  474. foreach ($search_xaxis as $key => $val) {
  475. if (preg_match('/\-year$/', $val)) {
  476. $tmpval = preg_replace('/\-year$/', '', $val);
  477. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y'), ";
  478. } elseif (preg_match('/\-month$/', $val)) {
  479. $tmpval = preg_replace('/\-month$/', '', $val);
  480. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m'), ";
  481. } elseif (preg_match('/\-day$/', $val)) {
  482. $tmpval = preg_replace('/\-day$/', '', $val);
  483. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d'), ";
  484. } else {
  485. $sql .= $val.', ';
  486. }
  487. }
  488. foreach ($search_groupby as $key => $val) {
  489. if (preg_match('/\-year$/', $val)) {
  490. $tmpval = preg_replace('/\-year$/', '', $val);
  491. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y'), ";
  492. } elseif (preg_match('/\-month$/', $val)) {
  493. $tmpval = preg_replace('/\-month$/', '', $val);
  494. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m'), ";
  495. } elseif (preg_match('/\-day$/', $val)) {
  496. $tmpval = preg_replace('/\-day$/', '', $val);
  497. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d'), ";
  498. } else {
  499. $sql .= $val.', ';
  500. }
  501. }
  502. $sql = preg_replace('/,\s*$/', '', $sql);
  503. $sql .= ' ORDER BY ';
  504. foreach ($search_xaxis as $key => $val) {
  505. if (preg_match('/\-year$/', $val)) {
  506. $tmpval = preg_replace('/\-year$/', '', $val);
  507. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y'), ";
  508. } elseif (preg_match('/\-month$/', $val)) {
  509. $tmpval = preg_replace('/\-month$/', '', $val);
  510. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m'), ";
  511. } elseif (preg_match('/\-day$/', $val)) {
  512. $tmpval = preg_replace('/\-day$/', '', $val);
  513. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d'), ";
  514. } else {
  515. $sql .= $val.', ';
  516. }
  517. }
  518. foreach ($search_groupby as $key => $val) {
  519. if (preg_match('/\-year$/', $val)) {
  520. $tmpval = preg_replace('/\-year$/', '', $val);
  521. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y'), ";
  522. } elseif (preg_match('/\-month$/', $val)) {
  523. $tmpval = preg_replace('/\-month$/', '', $val);
  524. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m'), ";
  525. } elseif (preg_match('/\-day$/', $val)) {
  526. $tmpval = preg_replace('/\-day$/', '', $val);
  527. $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d'), ";
  528. } else {
  529. $sql .= $val.', ';
  530. }
  531. }
  532. $sql = preg_replace('/,\s*$/', '', $sql);
  533. }
  534. //print $sql;
  535. $legend = array();
  536. foreach ($search_measures as $key => $val) {
  537. $legend[] = $langs->trans($arrayofmesures[$val]);
  538. }
  539. $useagroupby = (is_array($search_groupby) && count($search_groupby));
  540. //var_dump($useagroupby);
  541. //var_dump($arrayofvaluesforgroupby);
  542. // Execute the SQL request
  543. $totalnbofrecord = 0;
  544. $data = array();
  545. if ($sql) {
  546. $resql = $db->query($sql);
  547. if (!$resql) {
  548. dol_print_error($db);
  549. }
  550. $ifetch = 0;
  551. $xi = 0;
  552. $oldlabeltouse = '';
  553. while ($obj = $db->fetch_object($resql)) {
  554. $ifetch++;
  555. if ($useagroupby) {
  556. $xval = $search_xaxis[0];
  557. $fieldforxkey = 'x_0';
  558. $xlabel = $obj->$fieldforxkey;
  559. $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval);
  560. // Define $xlabel
  561. if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) {
  562. $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey];
  563. }
  564. $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->trans("Empty") : $langs->trans("NotDefined")));
  565. if ($oldlabeltouse && ($labeltouse != $oldlabeltouse)) {
  566. $xi++; // Increase $xi
  567. }
  568. //var_dump($labeltouse.' '.$oldlabeltouse.' '.$xi);
  569. $oldlabeltouse = $labeltouse;
  570. /* Example of value for $arrayofvaluesforgroupby
  571. * array (size=1)
  572. * 'g_0' =>
  573. * array (size=6)
  574. * 0 => string '0' (length=1)
  575. * '' => string 'Empty' (length=5)
  576. * '__NULL__' => string 'Not defined' (length=11)
  577. * 'done' => string 'done' (length=4)
  578. * 'processing' => string 'processing' (length=10)
  579. * 'undeployed' => string 'undeployed' (length=10)
  580. */
  581. foreach ($search_measures as $key => $val) {
  582. $gi = 0;
  583. foreach ($search_groupby as $gkey) {
  584. //var_dump('*** Fetch #'.$ifetch.' for labeltouse='.$labeltouse.' measure number '.$key.' and group g_'.$gi);
  585. //var_dump($arrayofvaluesforgroupby);
  586. foreach ($arrayofvaluesforgroupby['g_'.$gi] as $gvaluepossiblekey => $gvaluepossiblelabel) {
  587. $ykeysuffix = $gvaluepossiblelabel;
  588. $gvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $gval);
  589. $fieldfory = 'y_'.$key;
  590. $fieldforg = 'g_'.$gi;
  591. $fieldforybis = 'y_'.$key.'_'.$ykeysuffix;
  592. //var_dump('gvaluepossiblekey='.$gvaluepossiblekey.' gvaluepossiblelabel='.$gvaluepossiblelabel.' ykeysuffix='.$ykeysuffix.' gval='.$gval.' gvalwithoutsuffix='.$gvalwithoutprefix);
  593. //var_dump('fieldforg='.$fieldforg.' obj->$fieldforg='.$obj->$fieldforg.' fieldfory='.$fieldfory.' obj->$fieldfory='.$obj->$fieldfory.' fieldforybis='.$fieldforybis);
  594. if (!is_array($data[$xi])) {
  595. $data[$xi] = array();
  596. }
  597. if (!array_key_exists('label', $data[$xi])) {
  598. $data[$xi] = array();
  599. $data[$xi]['label'] = $labeltouse;
  600. }
  601. $objfieldforg = $obj->$fieldforg;
  602. if (is_null($objfieldforg)) {
  603. $objfieldforg = '__NULL__';
  604. }
  605. if ($gvaluepossiblekey == '0') { // $gvaluepossiblekey can have type int or string. So we create a special if, used when value is '0'
  606. //var_dump($objfieldforg.' == \'0\' -> '.($objfieldforg == '0'));
  607. if ($objfieldforg == '0') {
  608. // The record we fetch is for this group
  609. $data[$xi][$fieldforybis] = $obj->$fieldfory;
  610. } elseif (!isset($data[$xi][$fieldforybis])) {
  611. // The record we fetch is not for this group
  612. $data[$xi][$fieldforybis] = '0';
  613. }
  614. } else {
  615. //var_dump((string) $objfieldforg.' === '.(string) $gvaluepossiblekey.' -> '.((string) $objfieldforg === (string) $gvaluepossiblekey));
  616. if ((string) $objfieldforg === (string) $gvaluepossiblekey) {
  617. // The record we fetch is for this group
  618. $data[$xi][$fieldforybis] = $obj->$fieldfory;
  619. } elseif (!isset($data[$xi][$fieldforybis])) {
  620. // The record we fetch is not for this group
  621. $data[$xi][$fieldforybis] = '0';
  622. }
  623. }
  624. }
  625. //var_dump($data[$xi]);
  626. $gi++;
  627. }
  628. }
  629. } else { // No group by
  630. $xval = $search_xaxis[0];
  631. $fieldforxkey = 'x_0';
  632. $xlabel = $obj->$fieldforxkey;
  633. $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval);
  634. // Define $xlabel
  635. if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) {
  636. $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey];
  637. }
  638. $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->trans("Empty") : $langs->trans("NotDefined")));
  639. $xarrayforallseries = array('label' => $labeltouse);
  640. foreach ($search_measures as $key => $val) {
  641. $fieldfory = 'y_'.$key;
  642. $xarrayforallseries[$fieldfory] = $obj->$fieldfory;
  643. }
  644. $data[$xi] = $xarrayforallseries;
  645. $xi++;
  646. }
  647. }
  648. $totalnbofrecord = count($data);
  649. }
  650. //var_dump($data);
  651. print '<div class="customreportsoutput'.($totalnbofrecord ? '' : ' customreportsoutputnotdata').'">';
  652. if ($mode == 'grid') {
  653. // TODO
  654. }
  655. if ($mode == 'graph') {
  656. $WIDTH = '80%';
  657. $HEIGHT = 200;
  658. // Show graph
  659. $px1 = new DolGraph();
  660. $mesg = $px1->isGraphKo();
  661. if (!$mesg) {
  662. /*var_dump($legend);
  663. var_dump($data);*/
  664. $px1->SetData($data);
  665. unset($data);
  666. $arrayoftypes = array();
  667. foreach ($search_measures as $key => $val) {
  668. $arrayoftypes[] = $search_graph;
  669. }
  670. $px1->SetLegend($legend);
  671. $px1->SetMinValue($px1->GetFloorMinValue());
  672. $px1->SetMaxValue($px1->GetCeilMaxValue());
  673. $px1->SetWidth($WIDTH);
  674. $px1->SetHeight($HEIGHT);
  675. $px1->SetYLabel($langs->trans("Y"));
  676. $px1->SetShading(3);
  677. $px1->SetHorizTickIncrement(1);
  678. $px1->SetCssPrefix("cssboxes");
  679. $px1->SetType($arrayoftypes);
  680. $px1->mode = 'depth';
  681. $px1->SetTitle('');
  682. $dir = $conf->user->dir_temp;
  683. dol_mkdir($dir);
  684. $filenamenb = $dir.'/customreport_'.$object->element.'.png';
  685. $fileurlnb = DOL_URL_ROOT.'/viewimage.php?modulepart=user&file=customreport_'.$object->element.'.png';
  686. $px1->draw($filenamenb, $fileurlnb);
  687. $texttoshow = $langs->trans("NoRecordFound");
  688. if (!GETPOSTISSET('search_measures') || !GETPOSTISSET('search_xaxis')) {
  689. $texttoshow = $langs->trans("SelectYourGraphOptionsFirst");
  690. }
  691. print $px1->show($totalnbofrecord ? 0 : $texttoshow);
  692. }
  693. }
  694. if ($sql) {
  695. // Show admin info
  696. print '<br>'.info_admin($langs->trans("SQLUsedForExport").':<br> '.$sql, 0, 0, 1, '', 'TechnicalInformation');
  697. }
  698. print '<div>';
  699. if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
  700. print dol_get_fiche_end();
  701. }
  702. // End of page
  703. llxFooter();
  704. $db->close();