customreports.php 32 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800
  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. // ...
  211. $sql .= ' LIMIT '.($MAXUNIQUEVALFORGROUP + 1);
  212. //print $sql;
  213. $resql = $db->query($sql);
  214. if (!$resql) {
  215. dol_print_error($db);
  216. }
  217. while ($obj = $db->fetch_object($resql)) {
  218. if (is_null($obj->val)) {
  219. $keytouse = '__NULL__';
  220. $valuetranslated = $langs->transnoentitiesnoconv("NotDefined");
  221. } elseif ($obj->val === '') {
  222. $keytouse = '';
  223. $valuetranslated = $langs->transnoentitiesnoconv("Empty");
  224. } else {
  225. $keytouse = (string) $obj->val;
  226. $valuetranslated = $obj->val;
  227. }
  228. $regs = array();
  229. if (!empty($object->fields[$gvalwithoutprefix]['arrayofkeyval'])) {
  230. $valuetranslated = $object->fields[$gvalwithoutprefix]['arrayofkeyval'][$obj->val];
  231. if (is_null($valuetranslated)) {
  232. $valuetranslated = $langs->transnoentitiesnoconv("UndefinedKey");
  233. }
  234. $valuetranslated = $langs->trans($valuetranslated);
  235. } elseif (preg_match('/integer:([^:]+):([^:]+)$/', $object->fields[$gvalwithoutprefix]['type'], $regs)) {
  236. $classname = $regs[1];
  237. $classpath = $regs[2];
  238. dol_include_once($classpath);
  239. if (class_exists($classname)) {
  240. $tmpobject = new $classname($db);
  241. $tmpobject->fetch($obj->val);
  242. foreach ($tmpobject->fields as $fieldkey => $field) {
  243. if ($field['showoncombobox']) {
  244. $valuetranslated = $tmpobject->$fieldkey;
  245. //if ($valuetranslated == '-') $valuetranslated = $langs->transnoentitiesnoconv("Unknown")
  246. break;
  247. }
  248. }
  249. //$valuetranslated = $tmpobject->ref.'eee';
  250. }
  251. }
  252. $arrayofvaluesforgroupby['g_'.$gkey][$keytouse] = $valuetranslated;
  253. }
  254. asort($arrayofvaluesforgroupby['g_'.$gkey]);
  255. if (count($arrayofvaluesforgroupby['g_'.$gkey]) > $MAXUNIQUEVALFORGROUP) {
  256. $langs->load("errors");
  257. if (strpos($fieldtocount, 'te.') === 0) {
  258. //if (!empty($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix])) {
  259. // $langs->load($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix]);
  260. //}
  261. $keyforlabeloffield = $extrafields->attributes[$object->table_element]['label'][$gvalwithoutprefix];
  262. } else {
  263. $keyforlabeloffield = $object->fields[$gvalwithoutprefix]['label'];
  264. }
  265. //var_dump($gkey.' '.$gval.' '.$gvalwithoutprefix);
  266. $gvalwithoutprefix = preg_replace('/\-(year|month|day)/', '', $gvalwithoutprefix);
  267. $labeloffield = $langs->transnoentitiesnoconv($keyforlabeloffield);
  268. setEventMessages($langs->trans("ErrorTooManyDifferentValueForSelectedGroupBy", $MAXUNIQUEVALFORGROUP, $labeloffield), null, 'warnings');
  269. $search_groupby = array();
  270. }
  271. $db->free($resql);
  272. }
  273. }
  274. //var_dump($arrayofvaluesforgroupby);exit;
  275. $tmparray = dol_getdate(dol_now());
  276. $endyear = $tmparray['year'];
  277. $endmonth = $tmparray['mon'];
  278. $datelastday = dol_get_last_day($endyear, $endmonth, 1);
  279. $startyear = $endyear - 2;
  280. $param = '';
  281. print '<form method="post" action="'.$_SERVER['PHP_SELF'].'">';
  282. print '<input type="hidden" name="token" value="'.newToken().'">';
  283. print '<input type="hidden" name="action" value="viewgraph">';
  284. print '<input type="hidden" name="tabfamily" value="'.$tabfamily.'">';
  285. print '<div class="liste_titre liste_titre_bydiv centpercent">';
  286. // Select object
  287. print '<div class="divadvancedsearchfield center floatnone">';
  288. print '<div class="inline-block"><span class="opacitymedium">'.$langs->trans("StatisticsOn").'</span></div> ';
  289. $newarrayoftype = array();
  290. foreach ($arrayoftype as $key => $val) {
  291. if (dol_eval($val['enabled'], 1)) {
  292. $newarrayoftype[$key] = $arrayoftype[$key];
  293. }
  294. if ($val['langs']) {
  295. $langs->load($val['langs']);
  296. }
  297. }
  298. print $form->selectarray('objecttype', $newarrayoftype, $objecttype, 0, 0, 0, '', 1, 0, 0, '', 'minwidth200', 1);
  299. if (empty($conf->use_javascript_ajax)) {
  300. print '<input type="submit" class="button buttongen" name="changeobjecttype" value="'.$langs->trans("Refresh").'">';
  301. } else {
  302. print '<script type="text/javascript" language="javascript">
  303. jQuery(document).ready(function() {
  304. jQuery("#objecttype").change(function() {
  305. console.log("Reload for "+jQuery("#objecttype").val());
  306. location.href = "'.$_SERVER["PHP_SELF"].'?objecttype="+jQuery("#objecttype").val()+"'.($tabfamily ? '&tabfamily='.$tabfamily : '').'";
  307. });
  308. });
  309. </script>';
  310. }
  311. print '</div><div class="clearboth"></div>';
  312. // Add Filter
  313. print '<div class="divadvancedsearchfield quatrevingtpercent">';
  314. print $form->searchComponent(array($object->element => $object->fields), $search_component_params);
  315. print '</div>';
  316. // Add measures into array
  317. print '<div class="divadvancedsearchfield clearboth">';
  318. foreach ($object->fields as $key => $val) {
  319. if (!empty($val['isameasure']) && (!isset($val['enabled']) || dol_eval($val['enabled'], 1))) {
  320. $arrayofmesures['t.'.$key.'-sum'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Sum").')</span>';
  321. $arrayofmesures['t.'.$key.'-average'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Average").')</span>';
  322. $arrayofmesures['t.'.$key.'-min'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Minimum").')</span>';
  323. $arrayofmesures['t.'.$key.'-max'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Maximum").')</span>';
  324. }
  325. }
  326. // Add extrafields to Measures
  327. if ($object->isextrafieldmanaged) {
  328. foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) {
  329. 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))) {
  330. $arrayofmesures['te.'.$key.'-sum'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Sum").')</span>';
  331. $arrayofmesures['te.'.$key.'-average'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Average").')</span>';
  332. $arrayofmesures['te.'.$key.'-min'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Minimum").')</span>';
  333. $arrayofmesures['te.'.$key.'-max'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Maximum").')</span>';
  334. }
  335. }
  336. }
  337. print '<div class="inline-block"><span class="fas fa-chart-line paddingright" title="'.$langs->trans("Measures").'"></span>'.$langs->trans("Measures").'</div> ';
  338. print $form->multiselectarray('search_measures', $arrayofmesures, $search_measures, 0, 0, 'minwidth400', 1);
  339. print '</div>';
  340. // Group by
  341. print '<div class="divadvancedsearchfield">';
  342. print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-horizontal paddingright" title="'.$langs->trans("GroupBy").'"></span>'.$langs->trans("GroupBy").'</div> ';
  343. print $formother->selectGroupByField($object, $search_groupby, $arrayofgroupby);
  344. print '</div>';
  345. // XAxis
  346. print '<div class="divadvancedsearchfield">';
  347. print '<div class="inline-block"><span class="fas fa-ruler-horizontal paddingright" title="'.$langs->trans("XAxis").'"></span>'.$langs->trans("XAxis").'</div> ';
  348. print $formother->selectXAxisField($object, $search_xaxis, $arrayofxaxis);
  349. print '</div>';
  350. if ($mode == 'grid') {
  351. // YAxis
  352. print '<div class="divadvancedsearchfield">';
  353. foreach ($object->fields as $key => $val) {
  354. if (empty($val['measure']) && (!isset($val['enabled']) || dol_eval($val['enabled'], 1))) {
  355. if (in_array($key, array('id', 'rowid', 'entity', 'last_main_doc', 'extraparams'))) {
  356. continue;
  357. }
  358. if (preg_match('/^fk_/', $key)) {
  359. continue;
  360. }
  361. if (in_array($val['type'], array('html', 'text'))) {
  362. continue;
  363. }
  364. if (in_array($val['type'], array('timestamp', 'date', 'datetime'))) {
  365. $arrayofyaxis['t.'.$key.'-year'] = array('label' => $langs->trans($val['label']).' ('.$YYYY.')', 'position' => $val['position']);
  366. $arrayofyaxis['t.'.$key.'-month'] = array('label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.')', 'position' => $val['position']);
  367. $arrayofyaxis['t.'.$key.'-day'] = array('label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.'-'.$DD.')', 'position' => $val['position']);
  368. } else {
  369. $arrayofyaxis['t.'.$key] = array('label' => $val['label'], 'position' => (int) $val['position']);
  370. }
  371. }
  372. // Add measure from extrafields
  373. if ($object->isextrafieldmanaged) {
  374. foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) {
  375. 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))) {
  376. $arrayofyaxis['te.'.$key] = array('label' => $extrafields->attributes[$object->table_element]['label'][$key], 'position' => (int) $extrafields->attributes[$object->table_element]['pos'][$key]);
  377. }
  378. }
  379. }
  380. }
  381. $arrayofyaxis = dol_sort_array($arrayofyaxis, 'position');
  382. $arrayofyaxislabel = array();
  383. foreach ($arrayofyaxis as $key => $val) {
  384. $arrayofyaxislabel[$key] = $val['label'];
  385. }
  386. print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-vertical paddingright" title="'.$langs->trans("YAxis").'"></span>'.$langs->trans("YAxis").'</div> ';
  387. print $form->multiselectarray('search_yaxis', $arrayofyaxislabel, $search_yaxis, 0, 0, 'minwidth100', 1);
  388. print '</div>';
  389. }
  390. if ($mode == 'graph') {
  391. print '<div class="divadvancedsearchfield">';
  392. $arrayofgraphs = array('bars' => 'Bars', 'lines' => 'Lines'); // also 'pies'
  393. print '<div class="inline-block opacitymedium"><span class="fas fa-chart-area paddingright" title="'.$langs->trans("Graph").'"></span>'.$langs->trans("Graph").'</div> ';
  394. print $form->selectarray('search_graph', $arrayofgraphs, $search_graph, 0, 0, 0, 'minwidth100', 1);
  395. print '</div>';
  396. }
  397. print '<div class="divadvancedsearchfield">';
  398. print '<input type="submit" class="button buttongen" value="'.$langs->trans("Refresh").'">';
  399. print '</div>';
  400. print '</div>';
  401. print '</form>';
  402. // Generate the SQL request
  403. $sql = '';
  404. if (!empty($search_measures) && !empty($search_xaxis)) {
  405. $fieldid = 'rowid';
  406. $sql = 'SELECT ';
  407. foreach ($search_xaxis as $key => $val) {
  408. if (preg_match('/\-year$/', $val)) {
  409. $tmpval = preg_replace('/\-year$/', '', $val);
  410. $sql .= "DATE_FORMAT(".$tmpval.", '%Y') as x_".$key.', ';
  411. } elseif (preg_match('/\-month$/', $val)) {
  412. $tmpval = preg_replace('/\-month$/', '', $val);
  413. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m') as x_".$key.', ';
  414. } elseif (preg_match('/\-day$/', $val)) {
  415. $tmpval = preg_replace('/\-day$/', '', $val);
  416. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d') as x_".$key.', ';
  417. } else {
  418. $sql .= $val." as x_".$key.", ";
  419. }
  420. }
  421. foreach ($search_groupby as $key => $val) {
  422. if (preg_match('/\-year$/', $val)) {
  423. $tmpval = preg_replace('/\-year$/', '', $val);
  424. $sql .= "DATE_FORMAT(".$tmpval.", '%Y') as g_".$key.', ';
  425. } elseif (preg_match('/\-month$/', $val)) {
  426. $tmpval = preg_replace('/\-month$/', '', $val);
  427. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m') as g_".$key.', ';
  428. } elseif (preg_match('/\-day$/', $val)) {
  429. $tmpval = preg_replace('/\-day$/', '', $val);
  430. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d') as g_".$key.', ';
  431. } else {
  432. $sql .= $val." as g_".$key.", ";
  433. }
  434. }
  435. foreach ($search_measures as $key => $val) {
  436. if ($val == 't.count') {
  437. $sql .= "COUNT(t.".$fieldid.") as y_".$key.', ';
  438. } elseif (preg_match('/\-sum$/', $val)) {
  439. $tmpval = preg_replace('/\-sum$/', '', $val);
  440. $sql .= "SUM(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
  441. } elseif (preg_match('/\-average$/', $val)) {
  442. $tmpval = preg_replace('/\-average$/', '', $val);
  443. $sql .= "AVG(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
  444. } elseif (preg_match('/\-min$/', $val)) {
  445. $tmpval = preg_replace('/\-min$/', '', $val);
  446. $sql .= "MIN(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
  447. } elseif (preg_match('/\-max$/', $val)) {
  448. $tmpval = preg_replace('/\-max$/', '', $val);
  449. $sql .= "MAX(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
  450. }
  451. }
  452. $sql = preg_replace('/,\s*$/', '', $sql);
  453. $sql .= ' FROM '.MAIN_DB_PREFIX.$object->table_element.' as t';
  454. // Add measure from extrafields
  455. if ($object->isextrafieldmanaged) {
  456. $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$object->table_element."_extrafields as te ON te.fk_object = t.".$fieldid;
  457. }
  458. if ($object->ismultientitymanaged) {
  459. if ($object->ismultientitymanaged == 1) {
  460. // Nothing here
  461. } else {
  462. $tmparray = explode('@', $object->ismultientitymanaged);
  463. $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$tmparray[1]." as parenttable ON t.".$tmparray[0]." = parenttable.rowid";
  464. $sql .= ' AND parenttable.entity IN ('.getEntity($tmparray[1]).')';
  465. }
  466. }
  467. $sql .= ' WHERE 1 = 1';
  468. if ($object->ismultientitymanaged == 1) {
  469. $sql .= ' AND entity IN ('.getEntity($object->element).')';
  470. }
  471. foreach ($search_filters as $key => $val) {
  472. // TODO Add the where here
  473. }
  474. $sql .= ' GROUP BY ';
  475. foreach ($search_xaxis as $key => $val) {
  476. if (preg_match('/\-year$/', $val)) {
  477. $tmpval = preg_replace('/\-year$/', '', $val);
  478. $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
  479. } elseif (preg_match('/\-month$/', $val)) {
  480. $tmpval = preg_replace('/\-month$/', '', $val);
  481. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
  482. } elseif (preg_match('/\-day$/', $val)) {
  483. $tmpval = preg_replace('/\-day$/', '', $val);
  484. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
  485. } else {
  486. $sql .= $val.", ";
  487. }
  488. }
  489. foreach ($search_groupby as $key => $val) {
  490. if (preg_match('/\-year$/', $val)) {
  491. $tmpval = preg_replace('/\-year$/', '', $val);
  492. $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
  493. } elseif (preg_match('/\-month$/', $val)) {
  494. $tmpval = preg_replace('/\-month$/', '', $val);
  495. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
  496. } elseif (preg_match('/\-day$/', $val)) {
  497. $tmpval = preg_replace('/\-day$/', '', $val);
  498. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
  499. } else {
  500. $sql .= $val.', ';
  501. }
  502. }
  503. $sql = preg_replace('/,\s*$/', '', $sql);
  504. $sql .= ' ORDER BY ';
  505. foreach ($search_xaxis as $key => $val) {
  506. if (preg_match('/\-year$/', $val)) {
  507. $tmpval = preg_replace('/\-year$/', '', $val);
  508. $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
  509. } elseif (preg_match('/\-month$/', $val)) {
  510. $tmpval = preg_replace('/\-month$/', '', $val);
  511. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
  512. } elseif (preg_match('/\-day$/', $val)) {
  513. $tmpval = preg_replace('/\-day$/', '', $val);
  514. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
  515. } else {
  516. $sql .= $val.', ';
  517. }
  518. }
  519. foreach ($search_groupby as $key => $val) {
  520. if (preg_match('/\-year$/', $val)) {
  521. $tmpval = preg_replace('/\-year$/', '', $val);
  522. $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
  523. } elseif (preg_match('/\-month$/', $val)) {
  524. $tmpval = preg_replace('/\-month$/', '', $val);
  525. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
  526. } elseif (preg_match('/\-day$/', $val)) {
  527. $tmpval = preg_replace('/\-day$/', '', $val);
  528. $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
  529. } else {
  530. $sql .= $val.', ';
  531. }
  532. }
  533. $sql = preg_replace('/,\s*$/', '', $sql);
  534. }
  535. //print $sql;
  536. $legend = array();
  537. foreach ($search_measures as $key => $val) {
  538. $legend[] = $langs->trans($arrayofmesures[$val]);
  539. }
  540. $useagroupby = (is_array($search_groupby) && count($search_groupby));
  541. //var_dump($useagroupby);
  542. //var_dump($arrayofvaluesforgroupby);
  543. // Execute the SQL request
  544. $totalnbofrecord = 0;
  545. $data = array();
  546. if ($sql) {
  547. $resql = $db->query($sql);
  548. if (!$resql) {
  549. dol_print_error($db);
  550. }
  551. $ifetch = 0;
  552. $xi = 0;
  553. $oldlabeltouse = '';
  554. while ($obj = $db->fetch_object($resql)) {
  555. $ifetch++;
  556. if ($useagroupby) {
  557. $xval = $search_xaxis[0];
  558. $fieldforxkey = 'x_0';
  559. $xlabel = $obj->$fieldforxkey;
  560. $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval);
  561. // Define $xlabel
  562. if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) {
  563. $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey];
  564. }
  565. $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->trans("Empty") : $langs->trans("NotDefined")));
  566. if ($oldlabeltouse && ($labeltouse != $oldlabeltouse)) {
  567. $xi++; // Increase $xi
  568. }
  569. //var_dump($labeltouse.' '.$oldlabeltouse.' '.$xi);
  570. $oldlabeltouse = $labeltouse;
  571. /* Example of value for $arrayofvaluesforgroupby
  572. * array (size=1)
  573. * 'g_0' =>
  574. * array (size=6)
  575. * 0 => string '0' (length=1)
  576. * '' => string 'Empty' (length=5)
  577. * '__NULL__' => string 'Not defined' (length=11)
  578. * 'done' => string 'done' (length=4)
  579. * 'processing' => string 'processing' (length=10)
  580. * 'undeployed' => string 'undeployed' (length=10)
  581. */
  582. foreach ($search_measures as $key => $val) {
  583. $gi = 0;
  584. foreach ($search_groupby as $gkey) {
  585. //var_dump('*** Fetch #'.$ifetch.' for labeltouse='.$labeltouse.' measure number '.$key.' and group g_'.$gi);
  586. //var_dump($arrayofvaluesforgroupby);
  587. foreach ($arrayofvaluesforgroupby['g_'.$gi] as $gvaluepossiblekey => $gvaluepossiblelabel) {
  588. $ykeysuffix = $gvaluepossiblelabel;
  589. $gvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $gval);
  590. $fieldfory = 'y_'.$key;
  591. $fieldforg = 'g_'.$gi;
  592. $fieldforybis = 'y_'.$key.'_'.$ykeysuffix;
  593. //var_dump('gvaluepossiblekey='.$gvaluepossiblekey.' gvaluepossiblelabel='.$gvaluepossiblelabel.' ykeysuffix='.$ykeysuffix.' gval='.$gval.' gvalwithoutsuffix='.$gvalwithoutprefix);
  594. //var_dump('fieldforg='.$fieldforg.' obj->$fieldforg='.$obj->$fieldforg.' fieldfory='.$fieldfory.' obj->$fieldfory='.$obj->$fieldfory.' fieldforybis='.$fieldforybis);
  595. if (!is_array($data[$xi])) {
  596. $data[$xi] = array();
  597. }
  598. if (!array_key_exists('label', $data[$xi])) {
  599. $data[$xi] = array();
  600. $data[$xi]['label'] = $labeltouse;
  601. }
  602. $objfieldforg = $obj->$fieldforg;
  603. if (is_null($objfieldforg)) {
  604. $objfieldforg = '__NULL__';
  605. }
  606. if ($gvaluepossiblekey == '0') { // $gvaluepossiblekey can have type int or string. So we create a special if, used when value is '0'
  607. //var_dump($objfieldforg.' == \'0\' -> '.($objfieldforg == '0'));
  608. if ($objfieldforg == '0') {
  609. // The record we fetch is for this group
  610. $data[$xi][$fieldforybis] = $obj->$fieldfory;
  611. } elseif (!isset($data[$xi][$fieldforybis])) {
  612. // The record we fetch is not for this group
  613. $data[$xi][$fieldforybis] = '0';
  614. }
  615. } else {
  616. //var_dump((string) $objfieldforg.' === '.(string) $gvaluepossiblekey.' -> '.((string) $objfieldforg === (string) $gvaluepossiblekey));
  617. if ((string) $objfieldforg === (string) $gvaluepossiblekey) {
  618. // The record we fetch is for this group
  619. $data[$xi][$fieldforybis] = $obj->$fieldfory;
  620. } elseif (!isset($data[$xi][$fieldforybis])) {
  621. // The record we fetch is not for this group
  622. $data[$xi][$fieldforybis] = '0';
  623. }
  624. }
  625. }
  626. //var_dump($data[$xi]);
  627. $gi++;
  628. }
  629. }
  630. } else { // No group by
  631. $xval = $search_xaxis[0];
  632. $fieldforxkey = 'x_0';
  633. $xlabel = $obj->$fieldforxkey;
  634. $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval);
  635. // Define $xlabel
  636. if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) {
  637. $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey];
  638. }
  639. $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->trans("Empty") : $langs->trans("NotDefined")));
  640. $xarrayforallseries = array('label' => $labeltouse);
  641. foreach ($search_measures as $key => $val) {
  642. $fieldfory = 'y_'.$key;
  643. $xarrayforallseries[$fieldfory] = $obj->$fieldfory;
  644. }
  645. $data[$xi] = $xarrayforallseries;
  646. $xi++;
  647. }
  648. }
  649. $totalnbofrecord = count($data);
  650. }
  651. //var_dump($data);
  652. print '<div class="customreportsoutput'.($totalnbofrecord ? '' : ' customreportsoutputnotdata').'">';
  653. if ($mode == 'grid') {
  654. // TODO
  655. }
  656. if ($mode == 'graph') {
  657. $WIDTH = '80%';
  658. $HEIGHT = 200;
  659. // Show graph
  660. $px1 = new DolGraph();
  661. $mesg = $px1->isGraphKo();
  662. if (!$mesg) {
  663. /*var_dump($legend);
  664. var_dump($data);*/
  665. $px1->SetData($data);
  666. unset($data);
  667. $arrayoftypes = array();
  668. foreach ($search_measures as $key => $val) {
  669. $arrayoftypes[] = $search_graph;
  670. }
  671. $px1->SetLegend($legend);
  672. $px1->SetMinValue($px1->GetFloorMinValue());
  673. $px1->SetMaxValue($px1->GetCeilMaxValue());
  674. $px1->SetWidth($WIDTH);
  675. $px1->SetHeight($HEIGHT);
  676. $px1->SetYLabel($langs->trans("Y"));
  677. $px1->SetShading(3);
  678. $px1->SetHorizTickIncrement(1);
  679. $px1->SetCssPrefix("cssboxes");
  680. $px1->SetType($arrayoftypes);
  681. $px1->mode = 'depth';
  682. $px1->SetTitle('');
  683. $dir = $conf->user->dir_temp;
  684. dol_mkdir($dir);
  685. $filenamenb = $dir.'/customreport_'.$object->element.'.png';
  686. $fileurlnb = DOL_URL_ROOT.'/viewimage.php?modulepart=user&file=customreport_'.$object->element.'.png';
  687. $px1->draw($filenamenb, $fileurlnb);
  688. $texttoshow = $langs->trans("NoRecordFound");
  689. if (!GETPOSTISSET('search_measures') || !GETPOSTISSET('search_xaxis')) {
  690. $texttoshow = $langs->trans("SelectYourGraphOptionsFirst");
  691. }
  692. print $px1->show($totalnbofrecord ? 0 : $texttoshow);
  693. }
  694. }
  695. if ($sql) {
  696. // Show admin info
  697. print '<br>'.info_admin($langs->trans("SQLUsedForExport").':<br> '.$sql, 0, 0, 1, '', 'TechnicalInformation');
  698. }
  699. print '<div>';
  700. if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
  701. print dol_get_fiche_end();
  702. }
  703. // End of page
  704. llxFooter();
  705. $db->close();