dolibarr  20.0.0-alpha
customreports.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2020-2024 Laurent Destailleur <eldy@users.sourceforge.net>
3  * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
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 
34 if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
35  require '../main.inc.php';
36 
37  // Get parameters
38  $action = GETPOST('action', 'aZ09') ? GETPOST('action', 'aZ09') : 'view'; // The action 'add', 'create', 'edit', 'update', 'view', ...
39  $massaction = GETPOST('massaction', 'alpha'); // The bulk action (combo box choice into lists)
40 
41  $mode = GETPOST('mode', 'alpha');
42  $objecttype = GETPOST('objecttype', 'aZ09arobase');
43  $tabfamily = GETPOST('tabfamily', 'aZ09');
44 
45  $search_measures = GETPOST('search_measures', 'array');
46 
47  //$search_xaxis = GETPOST('search_xaxis', 'array');
48  if (GETPOST('search_xaxis', 'alpha') && GETPOST('search_xaxis', 'alpha') != '-1') {
49  $search_xaxis = array(GETPOST('search_xaxis', 'alpha'));
50  } else {
51  $search_xaxis = array();
52  }
53  //$search_groupby = GETPOST('search_groupby', 'array');
54  if (GETPOST('search_groupby', 'alpha') && GETPOST('search_groupby', 'alpha') != '-1') {
55  $search_groupby = array(GETPOST('search_groupby', 'alpha'));
56  } else {
57  $search_groupby = array();
58  }
59 
60  $search_yaxis = GETPOST('search_yaxis', 'array');
61  $search_graph = GETPOST('search_graph', 'restricthtml');
62 
63  // Load variable for pagination
64  $limit = GETPOSTINT('limit') ? GETPOSTINT('limit') : $conf->liste_limit;
65  $sortfield = GETPOST('sortfield', 'aZ09comma');
66  $sortorder = GETPOST('sortorder', 'aZ09comma');
67  $page = GETPOSTISSET('pageplusone') ? (GETPOSTINT('pageplusone') - 1) : GETPOSTINT("page");
68  if (empty($page) || $page == -1 || GETPOST('button_search', 'alpha') || GETPOST('button_removefilter', 'alpha') || (empty($toselect) && $massaction === '0')) {
69  $page = 0;
70  } // If $page is not defined, or '' or -1 or if we click on clear filters or if we select empty mass action
71  $offset = $limit * $page;
72  $pageprev = $page - 1;
73  $pagenext = $page + 1;
74 
75  $diroutputmassaction = $conf->user->dir_temp.'/'.$user->id.'/customreport';
76 
77  $object = null;
78 } else {
79  $langs->load("main");
80  // $search_measures, $search_xaxis or $search_yaxis may have been defined by the parent.
81 
82  if (empty($user) || empty($user->id)) {
83  print 'Page is called as an include but $user and its permission loaded with getrights() are not defined. We stop here.';
84  exit(-1);
85  }
86  if (empty($object)) {
87  print 'Page is called as an include but $object is not defined. We stop here.';
88  exit(-1);
89  }
90 }
91 
92 if (empty($mode)) {
93  $mode = 'graph';
94 }
95 if (!isset($search_measures)) {
96  $search_measures = array(0 => 't.count');
97 }
98 if (!empty($object)) {
99  $objecttype = $object->element.($object->module ? '@'.$object->module : '');
100 }
101 if (empty($objecttype)) {
102  $objecttype = 'thirdparty';
103 }
104 
105 require_once DOL_DOCUMENT_ROOT."/core/class/extrafields.class.php";
106 require_once DOL_DOCUMENT_ROOT."/core/class/html.form.class.php";
107 require_once DOL_DOCUMENT_ROOT."/core/lib/admin.lib.php";
108 require_once DOL_DOCUMENT_ROOT."/core/lib/company.lib.php";
109 require_once DOL_DOCUMENT_ROOT."/core/lib/date.lib.php";
110 require_once DOL_DOCUMENT_ROOT."/core/lib/customreports.lib.php";
111 require_once DOL_DOCUMENT_ROOT."/core/class/dolgraph.class.php";
112 require_once DOL_DOCUMENT_ROOT."/core/class/doleditor.class.php";
113 require_once DOL_DOCUMENT_ROOT."/core/class/html.formother.class.php";
114 
115 // Load traductions files requiredby by page
116 $langs->loadLangs(array("companies", "other", "exports", "sendings"));
117 
118 $extrafields = new ExtraFields($db);
119 
120 $hookmanager->initHooks(array('customreport')); // Note that conf->hooks_modules contains array
121 
122 $title = '';
123 $picto = '';
124 $head = array();
125 $ObjectClassName = '';
126 // Objects available by default
127 $arrayoftype = array(
128  'thirdparty' => array('langs' => 'companies', 'label' => 'ThirdParties', 'picto' => 'company', 'ObjectClassName' => 'Societe', 'enabled' => isModEnabled('societe'), 'ClassPath' => "/societe/class/societe.class.php"),
129  'contact' => array('label' => 'Contacts', 'picto' => 'contact', 'ObjectClassName' => 'Contact', 'enabled' => isModEnabled('societe'), 'ClassPath' => "/contact/class/contact.class.php"),
130  'proposal' => array('label' => 'Proposals', 'picto' => 'proposal', 'ObjectClassName' => 'Propal', 'enabled' => isModEnabled('propal'), 'ClassPath' => "/comm/propal/class/propal.class.php"),
131  'order' => array('label' => 'Orders', 'picto' => 'order', 'ObjectClassName' => 'Commande', 'enabled' => isModEnabled('order'), 'ClassPath' => "/commande/class/commande.class.php"),
132  'invoice' => array('langs' => 'facture', 'label' => 'Invoices', 'picto' => 'bill', 'ObjectClassName' => 'Facture', 'enabled' => isModEnabled('invoice'), 'ClassPath' => "/compta/facture/class/facture.class.php"),
133  'invoice_template' => array('label' => 'PredefinedInvoices', 'picto' => 'bill', 'ObjectClassName' => 'FactureRec', 'enabled' => isModEnabled('invoice'), 'ClassPath' => "/compta/class/facturerec.class.php", 'langs' => 'bills'),
134  'contract' => array('label' => 'Contracts', 'picto' => 'contract', 'ObjectClassName' => 'Contrat', 'enabled' => isModEnabled('contract'), 'ClassPath' => "/contrat/class/contrat.class.php", 'langs' => 'contracts'),
135  'contractdet' => array('label' => 'ContractLines', 'picto' => 'contract', 'ObjectClassName' => 'ContratLigne', 'enabled' => isModEnabled('contract'), 'ClassPath' => "/contrat/class/contrat.class.php", 'langs' => 'contracts'),
136  'bom' => array('label' => 'BOM', 'picto' => 'bom', 'ObjectClassName' => 'Bom', 'enabled' => isModEnabled('bom')),
137  'mrp' => array('label' => 'MO', 'picto' => 'mrp', 'ObjectClassName' => 'Mo', 'enabled' => isModEnabled('mrp'), 'ClassPath' => "/mrp/class/mo.class.php"),
138  'ticket' => array('label' => 'Ticket', 'picto' => 'ticket', 'ObjectClassName' => 'Ticket', 'enabled' => isModEnabled('ticket')),
139  'member' => array('label' => 'Adherent', 'picto' => 'member', 'ObjectClassName' => 'Adherent', 'enabled' => isModEnabled('member'), 'ClassPath' => "/adherents/class/adherent.class.php", 'langs' => 'members'),
140  'cotisation' => array('label' => 'Subscriptions', 'picto' => 'member', 'ObjectClassName' => 'Subscription', 'enabled' => isModEnabled('member'), 'ClassPath' => "/adherents/class/subscription.class.php", 'langs' => 'members'),
141 );
142 
143 
144 // Complete $arrayoftype by external modules
145 $parameters = array('objecttype' => $objecttype, 'tabfamily' => $tabfamily);
146 $reshook = $hookmanager->executeHooks('loadDataForCustomReports', $parameters, $object, $action); // Note that $action and $object may have been modified by some hooks
147 if ($reshook < 0) {
148  setEventMessages($hookmanager->error, $hookmanager->errors, 'errors');
149 } elseif (is_array($hookmanager->resArray)) {
150  if (!empty($hookmanager->resArray['title'])) { // Add entries for tabs
151  $title = $hookmanager->resArray['title'];
152  }
153  if (!empty($hookmanager->resArray['picto'])) { // Add entries for tabs
154  $picto = $hookmanager->resArray['picto'];
155  }
156  if (!empty($hookmanager->resArray['head'])) { // Add entries for tabs
157  $head = array_merge($head, $hookmanager->resArray['head']);
158  }
159  if (!empty($hookmanager->resArray['arrayoftype'])) { // Add entries from hook
160  foreach ($hookmanager->resArray['arrayoftype'] as $key => $val) {
161  $arrayoftype[$key] = $val;
162  }
163  }
164 }
165 
166 // Load the main $object for statistics
167 if ($objecttype) {
168  try {
169  if (!empty($arrayoftype[$objecttype]['ClassPath'])) {
170  $fileforclass = $arrayoftype[$objecttype]['ClassPath'];
171  } else {
172  $fileforclass = "/".$objecttype."/class/".$objecttype.".class.php";
173  }
174  dol_include_once($fileforclass);
175 
176  $ObjectClassName = $arrayoftype[$objecttype]['ObjectClassName'];
177  if (!empty($ObjectClassName)) {
178  if (class_exists($ObjectClassName)) {
179  $object = new $ObjectClassName($db);
180  } else {
181  print 'Failed to load class for type '.$objecttype.'. Class file found but Class object named '.$ObjectClassName.' not found.';
182  }
183  } else {
184  print 'Failed to load class for type '.$objecttype.'. Class file name is unknown.';
185  }
186  } catch (Exception $e) {
187  print 'Failed to load class for type '.$objecttype.'. Class path not found.';
188  }
189 }
190 
191 '@phan-var-force CommonObject $object';
192 
193 // Security check
194 $socid = 0;
195 if ($user->socid > 0) { // Protection if external user
196  //$socid = $user->socid;
197  accessforbidden('Access forbidden to external users');
198 }
199 
200 // Fetch optionals attributes and labels
201 $extrafields->fetch_name_optionals_label('all'); // We load all extrafields definitions for all objects
202 //$extrafields->fetch_name_optionals_label($object->table_element_line);
203 
204 $search_array_options = $extrafields->getOptionalsFromPost($object->table_element, '', 'search_');
205 
206 $search_component_params = array('');
207 $search_component_params_hidden = trim(GETPOST('search_component_params_hidden', 'alphanohtml'));
208 $search_component_params_input = trim(GETPOST('search_component_params_input', 'alphanohtml'));
209 //var_dump($search_component_params_hidden);
210 //var_dump($search_component_params_input);
211 
212 // If string is not an universal filter string, we try to convert it into universal filter syntax string
213 $errorstr = '';
214 forgeSQLFromUniversalSearchCriteria($search_component_params_input, $errorstr); // Try conversion UFS->SQL
215 //var_dump($errorstr);
216 if ($errorstr) {
217  $value = $search_component_params_input;
218 
219  $value = preg_replace('/([a-z\.]+)\s*([!<>=]+|in|notin|like|notlike)\s*/', '\1:\2:', $value); // Clean string 'x < 10' into 'x:<:10' so we can then explode on space to get all AND tests to do
220  $value = preg_replace('/\s*\|\s*/', '|', $value);
221  //var_dump($value);
222 
223  $crits = explode(' ', trim($value)); // the string after the name of the field. Explode on each AND
224  $res = '';
225 
226  $i1 = 0; // count the nb of and criteria added (all fields / criteria)
227  foreach ($crits as $crit) { // Loop on each AND criteria
228  $crit = trim($crit);
229 
230  $i2 = 0; // count the nb of valid criteria added for this first criteria
231  $newres = '';
232  $tmpcrits = explode('|', $crit);
233  $i3 = 0; // count the nb of valid criteria added for this current field
234  foreach ($tmpcrits as $tmpcrit) {
235  if ($tmpcrit !== '0' && empty($tmpcrit)) {
236  continue;
237  }
238  $tmpcrit = trim($tmpcrit);
239  //var_dump($tmpcrit);
240 
241  $errorstr = '';
242  $parenthesislevel = 0;
243  $rescheckfilter = dolCheckFilters($tmpcrit, $errorstr, $parenthesislevel);
244  if ($rescheckfilter) {
245  while ($parenthesislevel > 0) {
246  $tmpcrit = preg_replace('/^\‍(/', '', preg_replace('/\‍)$/', '', $tmpcrit));
247  $parenthesislevel--;
248  }
249  }
250 
251  $field = preg_replace('/(:[!<>=\s]+:|:in:|:notin:|:like:|:notlike:).*$/', '', $tmpcrit); // the name of the field
252  $tmpcrit = preg_replace('/^.*(:[!<>=\s]+:|:in:|:notin:|:like:|:notlike:)/', '\1', $tmpcrit); // the condition after the name of the field
253  //var_dump($field); var_dump($tmpcrit); var_dump($i3);
254 
255  $newres .= (($i2 > 0 || $i3 > 0) ? ' OR ' : '');
256 
257  $operator = '=';
258  $newcrit = preg_replace('/(:[!<>=\s]+:|:in:|:notin:|:like:|:notlike:)/', '', $tmpcrit);
259  //var_dump($newcrit);
260 
261  $reg = array();
262  preg_match('/:([!<>=\s]+|in|notin|like|notlike):/', $tmpcrit, $reg);
263  if (!empty($reg[1])) {
264  $operator = $reg[1];
265  }
266  if ($newcrit != '') {
267  if (!preg_match('/^\'[^\']*\'$/', $newcrit)) {
268  $numnewcrit = price2num($newcrit);
269  $newres .= '('.$field.':'.$operator.':'.((float) $numnewcrit).')';
270  } else {
271  $newres .= '('.$field.':'.$operator.":".((string) $newcrit).')';
272  }
273  $i3++; // a criteria was added to string
274  }
275  }
276  $i2++; // a criteria for 1 more field was added to string
277 
278  if ($newres) {
279  $res = $res.($res ? ' AND ' : '').($i2 > 1 ? '(' : '').$newres.($i2 > 1 ? ')' : '');
280  }
281  $i1++;
282  }
283  $res = "(".$res.")";
284 
285  //var_dump($res);exit;
286  $search_component_params_input = $res;
287 }
288 
289 $arrayofandtagshidden = dolForgeExplodeAnd($search_component_params_hidden);
290 $arrayofandtagsinput = dolForgeExplodeAnd($search_component_params_input);
291 
292 $search_component_params_hidden = implode(' AND ', array_merge($arrayofandtagshidden, $arrayofandtagsinput));
293 //var_dump($search_component_params_hidden);
294 
295 $MAXUNIQUEVALFORGROUP = 20;
296 $MAXMEASURESINBARGRAPH = 20;
297 $SHOWLEGEND = (isset($SHOWLEGEND) ? $SHOWLEGEND : 1);
298 
299 $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);
300 $MM = substr($langs->trans("Month"), 0, 1).substr($langs->trans("Month"), 0, 1);
301 $DD = substr($langs->trans("Day"), 0, 1).substr($langs->trans("Day"), 0, 1);
302 $HH = substr($langs->trans("Hour"), 0, 1).substr($langs->trans("Hour"), 0, 1);
303 $MI = substr($langs->trans("Minute"), 0, 1).substr($langs->trans("Minute"), 0, 1);
304 $SS = substr($langs->trans("Second"), 0, 1).substr($langs->trans("Second"), 0, 1);
305 
306 $arrayofmesures = array();
307 $arrayofxaxis = array();
308 $arrayofgroupby = array();
309 $arrayofyaxis = array();
310 $arrayofvaluesforgroupby = array();
311 
312 $features = $object->element;
313 if (!empty($object->element_for_permission)) {
314  $features = $object->element_for_permission;
315 } else {
316  $features = $features.(empty($object->module) ? '' : '@'.$object->module);
317 }
318 
319 restrictedArea($user, $features, 0, '');
320 
321 $error = 0;
322 
323 
324 /*
325  * Actions
326  */
327 
328 // None
329 
330 
331 
332 /*
333  * View
334  */
335 
336 $form = new Form($db);
337 $formother = new FormOther($db);
338 
339 if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
340  llxHeader('', $langs->transnoentitiesnoconv('CustomReports'), '');
341 
342  if (empty($head)) {
343  print dol_get_fiche_head($head, 'customreports', $title, -2, $picto);
344  } else {
345  print dol_get_fiche_head($head, 'customreports', $title, -1, $picto);
346  }
347 }
348 
349 $newarrayoftype = array();
350 foreach ($arrayoftype as $key => $val) {
351  if (dol_eval($val['enabled'], 1, 1, '1')) {
352  $newarrayoftype[$key] = $arrayoftype[$key];
353  }
354  if (!empty($val['langs'])) {
355  $langs->load($val['langs']);
356  }
357 }
358 
359 $count = 0;
360 $arrayofmesures = fillArrayOfMeasures($object, 't', $langs->trans($newarrayoftype[$objecttype]['label']), $arrayofmesures, 0, $count);
361 $arrayofmesures = dol_sort_array($arrayofmesures, 'position', 'asc', 0, 0, 1);
362 
363 $count = 0;
364 $arrayofxaxis = fillArrayOfXAxis($object, 't', $langs->trans($newarrayoftype[$objecttype]['label']), $arrayofxaxis, 0, $count);
365 $arrayofxaxis = dol_sort_array($arrayofxaxis, 'position', 'asc', 0, 0, 1);
366 
367 $count = 0;
368 $arrayofgroupby = fillArrayOfGroupBy($object, 't', $langs->trans($newarrayoftype[$objecttype]['label']), $arrayofgroupby, 0, $count);
369 $arrayofgroupby = dol_sort_array($arrayofgroupby, 'position', 'asc', 0, 0, 1);
370 
371 
372 // Check parameters
373 if ($action == 'viewgraph') {
374  if (!count($search_measures)) {
375  setEventMessages($langs->trans("AtLeastOneMeasureIsRequired"), null, 'warnings');
376  } elseif ($mode == 'graph' && count($search_xaxis) > 1) {
377  setEventMessages($langs->trans("OnlyOneFieldForXAxisIsPossible"), null, 'warnings');
378  $search_xaxis = array(0 => $search_xaxis[0]);
379  }
380  if (count($search_groupby) >= 2) {
381  setEventMessages($langs->trans("ErrorOnlyOneFieldForGroupByIsPossible"), null, 'warnings');
382  $search_groupby = array(0 => $search_groupby[0]);
383  }
384  if (!count($search_xaxis)) {
385  setEventMessages($langs->trans("AtLeastOneXAxisIsRequired"), null, 'warnings');
386  } elseif ($mode == 'graph' && $search_graph == 'bars' && count($search_measures) > $MAXMEASURESINBARGRAPH) {
387  $langs->load("errors");
388  setEventMessages($langs->trans("GraphInBarsAreLimitedToNMeasures", $MAXMEASURESINBARGRAPH), null, 'warnings');
389  $search_graph = 'lines';
390  }
391 }
392 
393 // Get all possible values of fields when a 'group by' is set, and save this into $arrayofvaluesforgroupby
394 // $arrayofvaluesforgroupby will be used to forge lael of each grouped series
395 if (is_array($search_groupby) && count($search_groupby)) {
396  $fieldtocount = '';
397  foreach ($search_groupby as $gkey => $gval) {
398  $gvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $gval);
399 
400  if (preg_match('/\-year$/', $search_groupby[$gkey])) {
401  $tmpval = preg_replace('/\-year$/', '', $search_groupby[$gkey]);
402  $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y')";
403  } elseif (preg_match('/\-month$/', $search_groupby[$gkey])) {
404  $tmpval = preg_replace('/\-month$/', '', $search_groupby[$gkey]);
405  $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y-%m')";
406  } elseif (preg_match('/\-day$/', $search_groupby[$gkey])) {
407  $tmpval = preg_replace('/\-day$/', '', $search_groupby[$gkey]);
408  $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d')";
409  } else {
410  $fieldtocount = $search_groupby[$gkey];
411  }
412 
413  $sql = "SELECT DISTINCT ".$fieldtocount." as val";
414 
415  if (strpos($fieldtocount, 'te') === 0) {
416  $tabletouse = $object->table_element;
417  $tablealiastouse = 'te';
418  if (!empty($arrayofgroupby[$gval])) {
419  $tmpval = explode('.', $gval);
420  $tabletouse = $arrayofgroupby[$gval]['table'];
421  $tablealiastouse = $tmpval[0];
422  }
423  //var_dump($tablealiastouse);exit;
424 
425  //$sql .= " FROM ".MAIN_DB_PREFIX.$object->table_element."_extrafields as te";
426  $sql .= " FROM ".MAIN_DB_PREFIX.$tabletouse."_extrafields as ".$tablealiastouse;
427  } else {
428  $tabletouse = $object->table_element;
429  $tablealiastouse = 't';
430  if (!empty($arrayofgroupby[$gval])) {
431  $tmpval = explode('.', $gval);
432  $tabletouse = $arrayofgroupby[$gval]['table'];
433  $tablealiastouse = $tmpval[0];
434  }
435  $sql .= " FROM ".MAIN_DB_PREFIX.$tabletouse." as ".$tablealiastouse;
436  }
437 
438  // Add a where here keeping only the criteria on $tabletouse
439  /* TODO
440  if ($search_component_params_hidden) {
441  $errormessage = '';
442  $sql .= forgeSQLFromUniversalSearchCriteria($search_component_params_hidden, $errormessage);
443  }
444  */
445 
446  $sql .= " LIMIT ".((int) ($MAXUNIQUEVALFORGROUP + 1));
447 
448  //print $sql;
449  $resql = $db->query($sql);
450  if (!$resql) {
451  dol_print_error($db);
452  }
453 
454  while ($obj = $db->fetch_object($resql)) {
455  if (is_null($obj->val)) {
456  $keytouse = '__NULL__';
457  $valuetranslated = $langs->transnoentitiesnoconv("NotDefined");
458  } elseif ($obj->val === '') {
459  $keytouse = '';
460  $valuetranslated = $langs->transnoentitiesnoconv("Empty");
461  } else {
462  $keytouse = (string) $obj->val;
463  $valuetranslated = $obj->val;
464  }
465 
466  $regs = array();
467  if (!empty($object->fields[$gvalwithoutprefix]['arrayofkeyval'])) {
468  $valuetranslated = $object->fields[$gvalwithoutprefix]['arrayofkeyval'][$obj->val];
469  if (is_null($valuetranslated)) {
470  $valuetranslated = $langs->transnoentitiesnoconv("UndefinedKey");
471  }
472  $valuetranslated = $langs->trans($valuetranslated);
473  } elseif (preg_match('/integer:([^:]+):([^:]+)$/', $object->fields[$gvalwithoutprefix]['type'], $regs)) {
474  $classname = $regs[1];
475  $classpath = $regs[2];
476  dol_include_once($classpath);
477  if (class_exists($classname)) {
478  $tmpobject = new $classname($db);
479  $tmpobject->fetch($obj->val);
480  foreach ($tmpobject->fields as $fieldkey => $field) {
481  if ($field['showoncombobox']) {
482  $valuetranslated = $tmpobject->$fieldkey;
483  //if ($valuetranslated == '-') $valuetranslated = $langs->transnoentitiesnoconv("Unknown")
484  break;
485  }
486  }
487  //$valuetranslated = $tmpobject->ref.'eee';
488  }
489  }
490 
491  $arrayofvaluesforgroupby['g_'.$gkey][$keytouse] = $valuetranslated;
492  }
493  // Add also the possible NULL value if field is a parent field that is not a strict join
494  $tmpfield = explode('.', $gval);
495  if ($tmpfield[0] != 't' || (is_array($object->fields[$tmpfield[1]]) && empty($object->fields[$tmpfield[1]]['notnull']))) {
496  dol_syslog("The group by field ".$gval." may be null (because field is null or it is a left join), so we add __NULL__ entry in list of possible values");
497  //var_dump($gval); var_dump($object->fields);
498  $arrayofvaluesforgroupby['g_'.$gkey]['__NULL__'] = $langs->transnoentitiesnoconv("NotDefined");
499  }
500 
501  if (is_array($arrayofvaluesforgroupby['g_'.$gkey])) {
502  asort($arrayofvaluesforgroupby['g_'.$gkey]);
503  }
504 
505  // Add a protection/error to refuse the request if number of differentr values for the group by is higher than $MAXUNIQUEVALFORGROUP
506  if (is_array($arrayofvaluesforgroupby['g_'.$gkey]) && count($arrayofvaluesforgroupby['g_'.$gkey]) > $MAXUNIQUEVALFORGROUP) {
507  $langs->load("errors");
508 
509  if (strpos($fieldtocount, 'te') === 0) { // This is a field of an extrafield
510  //if (!empty($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix])) {
511  // $langs->load($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix]);
512  //}
513  $keyforlabeloffield = $extrafields->attributes[$object->table_element]['label'][$gvalwithoutprefix];
514  $labeloffield = $langs->transnoentitiesnoconv($keyforlabeloffield);
515  } elseif (strpos($fieldtocount, 't__') === 0) { // This is a field of a foreign key
516  $reg = array();
517  if (preg_match('/^(.*)\.(.*)/', $gvalwithoutprefix, $reg)) {
518  /*
519  $gvalwithoutprefix = preg_replace('/\..*$/', '', $gvalwithoutprefix);
520  $gvalwithoutprefix = preg_replace('/^t__/', '', $gvalwithoutprefix);
521  $keyforlabeloffield = $object->fields[$gvalwithoutprefix]['label'];
522  $labeloffield = $langs->transnoentitiesnoconv($keyforlabeloffield).'-'.$reg[2];
523  */
524  $labeloffield = $arrayofgroupby[$fieldtocount]['labelnohtml'];
525  } else {
526  $labeloffield = $langs->transnoentitiesnoconv($keyforlabeloffield);
527  }
528  } else { // This is a common field
529  $reg = array();
530  if (preg_match('/^(.*)\-(year|month|day)/', $gvalwithoutprefix, $reg)) {
531  $gvalwithoutprefix = preg_replace('/\-(year|month|day)/', '', $gvalwithoutprefix);
532  $keyforlabeloffield = $object->fields[$gvalwithoutprefix]['label'];
533  $labeloffield = $langs->transnoentitiesnoconv($keyforlabeloffield).'-'.$reg[2];
534  } else {
535  $keyforlabeloffield = $object->fields[$gvalwithoutprefix]['label'];
536  $labeloffield = $langs->transnoentitiesnoconv($keyforlabeloffield);
537  }
538  }
539  //var_dump($object->fields);
540  setEventMessages($langs->trans("ErrorTooManyDifferentValueForSelectedGroupBy", $MAXUNIQUEVALFORGROUP, $labeloffield), null, 'warnings');
541  $search_groupby = array();
542  }
543 
544  $db->free($resql);
545  }
546 }
547 //var_dump($arrayofvaluesforgroupby);exit;
548 
549 
550 $tmparray = dol_getdate(dol_now());
551 $endyear = $tmparray['year'];
552 $endmonth = $tmparray['mon'];
553 $datelastday = dol_get_last_day($endyear, $endmonth, 1);
554 $startyear = $endyear - 2;
555 
556 $param = '';
557 
558 
559 if (!defined('MAIN_CUSTOM_REPORT_KEEP_GRAPH_ONLY')) {
560  print '<form method="post" action="'.$_SERVER['PHP_SELF'].'" autocomplete="off">';
561  print '<input type="hidden" name="token" value="'.newToken().'">';
562  print '<input type="hidden" name="action" value="viewgraph">';
563  print '<input type="hidden" name="tabfamily" value="'.$tabfamily.'">';
564 
565  $viewmode = '';
566 
567  $viewmode .= '<div class="divadvancedsearchfield">';
568  $arrayofgraphs = array('bars' => 'Bars', 'lines' => 'Lines'); // also 'pies'
569  $viewmode .= '<div class="inline-block opacitymedium"><span class="fas fa-chart-area paddingright" title="'.$langs->trans("Graph").'"></span>'.$langs->trans("Graph").'</div> ';
570  $viewmode .= $form->selectarray('search_graph', $arrayofgraphs, $search_graph, 0, 0, 0, '', 1, 0, 0, '', 'graphtype width100');
571  $viewmode .= '</div>';
572 
573  $num = 0;
574  $massactionbutton = '';
575  $nav = '';
576  $newcardbutton = '';
577  $limit = 0;
578 
579  print_barre_liste('', $page, $_SERVER["PHP_SELF"], $param, $sortfield, $sortorder, $massactionbutton, $num, -1, 'object_action', 0, $nav.'<span class="marginleftonly"></span>'.$newcardbutton, '', $limit, 1, 0, 1, $viewmode);
580 
581 
582  foreach ($newarrayoftype as $tmpkey => $tmpval) {
583  $newarrayoftype[$tmpkey]['label'] = img_picto('', $tmpval['picto'], 'class="pictofixedwidth"').$langs->trans($tmpval['label']);
584  }
585 
586  print '<div class="liste_titre liste_titre_bydiv liste_titre_bydiv_inlineblock centpercent">';
587 
588  // Select object
589  print '<div class="divadvancedsearchfield center floatnone">';
590  print '<div class="inline-block"><span class="opacitymedium">'.$langs->trans("StatisticsOn").'</span></div> ';
591  print $form->selectarray('objecttype', $newarrayoftype, $objecttype, 0, 0, 0, '', 1, 0, 0, '', 'minwidth200', 1, '', 0, 1);
592  if (empty($conf->use_javascript_ajax)) {
593  print '<input type="submit" class="button buttongen button-save nomargintop" name="changeobjecttype" value="'.$langs->trans("Refresh").'">';
594  } else {
595  print '<!-- js code to reload page with good object type -->
596  <script nonce="'.getNonce().'" type="text/javascript">
597  jQuery(document).ready(function() {
598  jQuery("#objecttype").change(function() {
599  console.log("Reload for "+jQuery("#objecttype").val());
600  location.href = "'.$_SERVER["PHP_SELF"].'?objecttype="+jQuery("#objecttype").val()+"'.($tabfamily ? '&tabfamily='.urlencode($tabfamily) : '').(GETPOSTINT('show_search_component_params_hidden') ? '&show_search_component_params_hidden='.((int) GETPOSTINT('show_search_component_params_hidden')) : '').'";
601  });
602  });
603  </script>';
604  }
605  print '</div><div class="clearboth"></div>';
606 
607  // Filter (you can use param &show_search_component_params_hidden=1 for debug)
608  if (!empty($object)) {
609  print '<div class="divadvancedsearchfield">';
610  print $form->searchComponent(array($object->element => $object->fields), $search_component_params, array(), $search_component_params_hidden);
611  print '</div>';
612  }
613 
614  // YAxis (add measures into array)
615  $count = 0;
616  //var_dump($arrayofmesures);
617  print '<div class="divadvancedsearchfield clearboth">';
618  print '<div class="inline-block"><span class="fas fa-ruler-combined paddingright pictofixedwidth" title="'.dol_escape_htmltag($langs->trans("Measures")).'"></span><span class="fas fa-caret-left caretleftaxis" title="'.dol_escape_htmltag($langs->trans("Measures")).'"></span></div>';
619  $simplearrayofmesures = array();
620  foreach ($arrayofmesures as $key => $val) {
621  $simplearrayofmesures[$key] = $arrayofmesures[$key]['label'];
622  }
623  print $form->multiselectarray('search_measures', $simplearrayofmesures, $search_measures, 0, 0, 'minwidth300', 1, 0, '', '', $langs->trans("Measures")); // Fill the array $arrayofmeasures with possible fields
624  print '</div>';
625 
626  // XAxis
627  $count = 0;
628  print '<div class="divadvancedsearchfield">';
629  print '<div class="inline-block"><span class="fas fa-ruler-combined paddingright pictofixedwidth" title="'.dol_escape_htmltag($langs->trans("XAxis")).'"></span><span class="fas fa-caret-down caretdownaxis" title="'.dol_escape_htmltag($langs->trans("XAxis")).'"></span></div>';
630  //var_dump($arrayofxaxis);
631  print $formother->selectXAxisField($object, $search_xaxis, $arrayofxaxis, $langs->trans("XAxis"), 'minwidth300 maxwidth400'); // Fill the array $arrayofxaxis with possible fields
632  print '</div>';
633 
634  // Group by
635  $count = 0;
636  print '<div class="divadvancedsearchfield">';
637  print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-horizontal paddingright pictofixedwidth" title="'.dol_escape_htmltag($langs->trans("GroupBy")).'"></span></div>';
638  print $formother->selectGroupByField($object, $search_groupby, $arrayofgroupby, 'minwidth250 maxwidth300', $langs->trans("GroupBy")); // Fill the array $arrayofgroupby with possible fields
639  print '</div>';
640 
641 
642  if ($mode == 'grid') {
643  // YAxis
644  print '<div class="divadvancedsearchfield">';
645  foreach ($object->fields as $key => $val) {
646  if (empty($val['measure']) && (!isset($val['enabled']) || dol_eval($val['enabled'], 1, 1, '1'))) {
647  if (in_array($key, array('id', 'rowid', 'entity', 'last_main_doc', 'extraparams'))) {
648  continue;
649  }
650  if (preg_match('/^fk_/', $key)) {
651  continue;
652  }
653  if (in_array($val['type'], array('html', 'text'))) {
654  continue;
655  }
656  if (in_array($val['type'], array('timestamp', 'date', 'datetime'))) {
657  $arrayofyaxis['t.'.$key.'-year'] = array(
658  'label' => $langs->trans($val['label']).' ('.$YYYY.')',
659  'position' => $val['position'],
660  'table' => $object->table_element
661  );
662  $arrayofyaxis['t.'.$key.'-month'] = array(
663  'label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.')',
664  'position' => $val['position'],
665  'table' => $object->table_element
666  );
667  $arrayofyaxis['t.'.$key.'-day'] = array(
668  'label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.'-'.$DD.')',
669  'position' => $val['position'],
670  'table' => $object->table_element
671  );
672  } else {
673  $arrayofyaxis['t.'.$key] = array(
674  'label' => $val['label'],
675  'position' => (int) $val['position'],
676  'table' => $object->table_element
677  );
678  }
679  }
680  }
681  // Add measure from extrafields
682  if ($object->isextrafieldmanaged) {
683  foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) {
684  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, 1, '1'))) {
685  $arrayofyaxis['te.'.$key] = array(
686  'label' => $extrafields->attributes[$object->table_element]['label'][$key],
687  'position' => (int) $extrafields->attributes[$object->table_element]['pos'][$key],
688  'table' => $object->table_element
689  );
690  }
691  }
692  }
693  $arrayofyaxis = dol_sort_array($arrayofyaxis, 'position');
694  $arrayofyaxislabel = array();
695  foreach ($arrayofyaxis as $key => $val) {
696  $arrayofyaxislabel[$key] = $val['label'];
697  }
698  print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-vertical paddingright" title="'.$langs->trans("YAxis").'"></span>'.$langs->trans("YAxis").'</div> ';
699  print $form->multiselectarray('search_yaxis', $arrayofyaxislabel, $search_yaxis, 0, 0, 'minwidth100', 1);
700  print '</div>';
701  }
702 
703  if ($mode == 'graph') {
704  //
705  }
706 
707  print '<div class="divadvancedsearchfield">';
708  print '<input type="submit" class="button buttongen button-save nomargintop" value="'.$langs->trans("Refresh").'">';
709  print '</div>';
710  print '</div>';
711  print '</form>';
712 }
713 
714 // Generate the SQL request
715 $sql = '';
716 if (!empty($search_measures) && !empty($search_xaxis)) {
717  $errormessage = '';
718 
719  $fieldid = 'rowid';
720 
721  $sql = "SELECT ";
722  foreach ($search_xaxis as $key => $val) {
723  if (preg_match('/\-year$/', $val)) {
724  $tmpval = preg_replace('/\-year$/', '', $val);
725  $sql .= "DATE_FORMAT(".$tmpval.", '%Y') as x_".$key.', ';
726  } elseif (preg_match('/\-month$/', $val)) {
727  $tmpval = preg_replace('/\-month$/', '', $val);
728  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m') as x_".$key.', ';
729  } elseif (preg_match('/\-day$/', $val)) {
730  $tmpval = preg_replace('/\-day$/', '', $val);
731  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d') as x_".$key.', ';
732  } else {
733  $sql .= $val." as x_".$key.", ";
734  }
735  }
736  if (!empty($search_groupby)) {
737  foreach ($search_groupby as $key => $val) {
738  if (preg_match('/\-year$/', $val)) {
739  $tmpval = preg_replace('/\-year$/', '', $val);
740  $sql .= "DATE_FORMAT(".$tmpval.", '%Y') as g_".$key.', ';
741  } elseif (preg_match('/\-month$/', $val)) {
742  $tmpval = preg_replace('/\-month$/', '', $val);
743  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m') as g_".$key.', ';
744  } elseif (preg_match('/\-day$/', $val)) {
745  $tmpval = preg_replace('/\-day$/', '', $val);
746  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d') as g_".$key.', ';
747  } else {
748  $sql .= $val." as g_".$key.", ";
749  }
750  }
751  }
752  foreach ($search_measures as $key => $val) {
753  if ($val == 't.count') {
754  $sql .= "COUNT(t.".$fieldid.") as y_".$key.', ';
755  } elseif (preg_match('/\-sum$/', $val)) {
756  $tmpval = preg_replace('/\-sum$/', '', $val);
757  $sql .= "SUM(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
758  } elseif (preg_match('/\-average$/', $val)) {
759  $tmpval = preg_replace('/\-average$/', '', $val);
760  $sql .= "AVG(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
761  } elseif (preg_match('/\-min$/', $val)) {
762  $tmpval = preg_replace('/\-min$/', '', $val);
763  $sql .= "MIN(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
764  } elseif (preg_match('/\-max$/', $val)) {
765  $tmpval = preg_replace('/\-max$/', '', $val);
766  $sql .= "MAX(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
767  }
768  }
769  $sql = preg_replace('/,\s*$/', '', $sql);
770  $sql .= " FROM ".MAIN_DB_PREFIX.$object->table_element." as t";
771  // Add measure from extrafields
772  if ($object->isextrafieldmanaged) {
773  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$object->table_element."_extrafields as te ON te.fk_object = t.".$fieldid;
774  }
775  // Add table for link on multientity
776  if ($object->ismultientitymanaged) { // 0=No test on entity, 1=Test with field entity, 'field@table'=Test with link by field@table
777  if ($object->ismultientitymanaged == 1) {
778  // No table to add here
779  } else {
780  $tmparray = explode('@', $object->ismultientitymanaged);
781  $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$tmparray[1]." as parenttableforentity ON t.".$tmparray[0]." = parenttableforentity.rowid";
782  $sql .= " AND parenttableforentity.entity IN (".getEntity($tmparray[1]).")";
783  }
784  }
785 
786  // Init the list of tables added. We include by default always the main table.
787  $listoftablesalreadyadded = array($object->table_element => $object->table_element);
788 
789  // Add LEFT JOIN for all parent tables mentioned into the Xaxis
790  //var_dump($arrayofxaxis); var_dump($search_xaxis);
791  foreach ($search_xaxis as $key => $val) {
792  if (!empty($arrayofxaxis[$val])) {
793  $tmpval = explode('.', $val);
794  //var_dump($arrayofgroupby);
795  $tmpforloop = dolExplodeIntoArray($arrayofxaxis[$val]['tablefromt'], ',');
796  foreach ($tmpforloop as $tmptable => $tmptablealias) {
797  if (! in_array($tmptable, $listoftablesalreadyadded)) { // We do not add join for main table and tables already added
798  $tmpforexplode = explode('__', $tmptablealias);
799  $endpart = end($tmpforexplode);
800  $parenttableandfield = preg_replace('/__'.$endpart.'$/', '', $tmptablealias).'.'.$endpart;
801 
802  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$tmptable." as ".$db->sanitize($tmptablealias)." ON ".$db->sanitize($parenttableandfield)." = ".$db->sanitize($tmptablealias).".rowid";
803  $listoftablesalreadyadded[$tmptable] = $tmptable;
804 
805  if (preg_match('/^te/', $tmpval[0]) && preg_replace('/^t_/', 'te_', $tmptablealias) == $tmpval[0]) {
806  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$tmptable."_extrafields as ".$db->sanitize($tmpval[0])." ON ".$db->sanitize($tmpval[0]).".fk_object = ".$db->sanitize($tmptablealias).".rowid";
807  $listoftablesalreadyadded[$tmptable] = $tmptable;
808  }
809  }
810  }
811  } else {
812  $errormessage = 'Found a key into search_xaxis not found into arrayofxaxis';
813  }
814  }
815 
816  // Add LEFT JOIN for all parent tables mentioned into the Group by
817  //var_dump($arrayofgroupby); var_dump($search_groupby);
818  foreach ($search_groupby as $key => $val) {
819  if (!empty($arrayofgroupby[$val])) {
820  $tmpval = explode('.', $val);
821  //var_dump($arrayofgroupby[$val]); var_dump($tmpval);
822  $tmpforloop = dolExplodeIntoArray($arrayofgroupby[$val]['tablefromt'], ',');
823  foreach ($tmpforloop as $tmptable => $tmptablealias) {
824  if (! in_array($tmptable, $listoftablesalreadyadded)) { // We do not add join for main table and tables already added
825  $tmpforexplode = explode('__', $tmptablealias);
826  $endpart = end($tmpforexplode);
827  $parenttableandfield = preg_replace('/__'.$endpart.'$/', '', $tmptablealias).'.'.$endpart;
828 
829  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$tmptable." as ".$db->sanitize($tmptablealias)." ON ".$db->sanitize($parenttableandfield)." = ".$db->sanitize($tmptablealias).".rowid";
830  $listoftablesalreadyadded[$tmptable] = $tmptable;
831 
832  if (preg_match('/^te/', $tmpval[0]) && preg_replace('/^t_/', 'te_', $tmptablealias) == $tmpval[0]) {
833  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$tmptable."_extrafields as ".$db->sanitize($tmpval[0])." ON ".$db->sanitize($tmpval[0]).".fk_object = ".$db->sanitize($tmptablealias).".rowid";
834  $listoftablesalreadyadded[$tmptable] = $tmptable;
835  }
836  }
837  }
838  } else {
839  $errormessage = 'Found a key into search_groupby not found into arrayofgroupby';
840  }
841  }
842 
843  // Add LEFT JOIN for all parent tables mentioned into the Yaxis
844  //var_dump($arrayofgroupby); var_dump($search_groupby);
845  foreach ($search_measures as $key => $val) {
846  if (!empty($arrayofmesures[$val])) {
847  $tmpval = explode('.', $val);
848  //var_dump($arrayofgroupby);
849  $tmpforloop = dolExplodeIntoArray($arrayofmesures[$val]['tablefromt'], ',');
850  foreach ($tmpforloop as $tmptable => $tmptablealias) {
851  if (! in_array($tmptable, $listoftablesalreadyadded)) { // We do not add join for main table and tables already added
852  $tmpforexplode = explode('__', $tmptablealias);
853  $endpart = end($tmpforexplode);
854  $parenttableandfield = preg_replace('/__'.$endpart.'$/', '', $tmptablealias).'.'.$endpart;
855 
856  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$tmptable." as ".$db->sanitize($tmptablealias)." ON ".$db->sanitize($parenttableandfield)." = ".$db->sanitize($tmptablealias).".rowid";
857  $listoftablesalreadyadded[$tmptable] = $tmptable;
858 
859  if (preg_match('/^te/', $tmpval[0]) && preg_replace('/^t_/', 'te_', $tmptablealias) == $tmpval[0]) {
860  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$tmptable."_extrafields as ".$db->sanitize($tmpval[0])." ON ".$db->sanitize($tmpval[0]).".fk_object = ".$db->sanitize($tmptablealias).".rowid";
861  $listoftablesalreadyadded[$tmptable] = $tmptable;
862  }
863  }
864  }
865  } else {
866  $errormessage = 'Found a key into search_measures not found into arrayofmesures';
867  }
868  }
869 
870  $sql .= " WHERE 1 = 1";
871  if ($object->ismultientitymanaged == 1) { // 0=No test on entity, 1=Test with field entity, 'field@table'=Test with link by field@table
872  $sql .= " AND t.entity IN (".getEntity($object->element).")";
873  }
874  // Add the where here
875  $sqlfilters = $search_component_params_hidden;
876  if ($sqlfilters) {
877  $sql .= forgeSQLFromUniversalSearchCriteria($sqlfilters, $errormessage, 0, 0, 1);
878  }
879  $sql .= " GROUP BY ";
880  foreach ($search_xaxis as $key => $val) {
881  if (preg_match('/\-year$/', $val)) {
882  $tmpval = preg_replace('/\-year$/', '', $val);
883  $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
884  } elseif (preg_match('/\-month$/', $val)) {
885  $tmpval = preg_replace('/\-month$/', '', $val);
886  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
887  } elseif (preg_match('/\-day$/', $val)) {
888  $tmpval = preg_replace('/\-day$/', '', $val);
889  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
890  } else {
891  $sql .= $val.", ";
892  }
893  }
894  if (!empty($search_groupby)) {
895  foreach ($search_groupby as $key => $val) {
896  if (preg_match('/\-year$/', $val)) {
897  $tmpval = preg_replace('/\-year$/', '', $val);
898  $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
899  } elseif (preg_match('/\-month$/', $val)) {
900  $tmpval = preg_replace('/\-month$/', '', $val);
901  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
902  } elseif (preg_match('/\-day$/', $val)) {
903  $tmpval = preg_replace('/\-day$/', '', $val);
904  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
905  } else {
906  $sql .= $val.', ';
907  }
908  }
909  }
910  $sql = preg_replace('/,\s*$/', '', $sql);
911  $sql .= ' ORDER BY ';
912  foreach ($search_xaxis as $key => $val) {
913  if (preg_match('/\-year$/', $val)) {
914  $tmpval = preg_replace('/\-year$/', '', $val);
915  $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
916  } elseif (preg_match('/\-month$/', $val)) {
917  $tmpval = preg_replace('/\-month$/', '', $val);
918  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
919  } elseif (preg_match('/\-day$/', $val)) {
920  $tmpval = preg_replace('/\-day$/', '', $val);
921  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
922  } else {
923  $sql .= $val.', ';
924  }
925  }
926  if (!empty($search_groupby)) {
927  foreach ($search_groupby as $key => $val) {
928  if (preg_match('/\-year$/', $val)) {
929  $tmpval = preg_replace('/\-year$/', '', $val);
930  $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
931  } elseif (preg_match('/\-month$/', $val)) {
932  $tmpval = preg_replace('/\-month$/', '', $val);
933  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
934  } elseif (preg_match('/\-day$/', $val)) {
935  $tmpval = preg_replace('/\-day$/', '', $val);
936  $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
937  } else {
938  $sql .= $val.', ';
939  }
940  }
941  }
942  $sql = preg_replace('/,\s*$/', '', $sql);
943 
944  // Can overwrite the SQL with a custom SQL string (when used as an include)
945  if (!empty($customsql)) {
946  $sql = $customsql;
947  }
948 }
949 //print $sql;
950 
951 if ($errormessage) {
952  print '<div class="warning">';
953  print dol_escape_htmltag($errormessage);
954  //print '<br>'.dol_escape_htmltag('SQL is '.$sql);
955  print '</div>';
956  $sql = '';
957 }
958 
959 $legend = array();
960 foreach ($search_measures as $key => $val) {
961  $legend[] = $langs->trans($arrayofmesures[$val]['label']);
962 }
963 
964 $useagroupby = (is_array($search_groupby) && count($search_groupby));
965 //var_dump($useagroupby);
966 //var_dump($arrayofvaluesforgroupby);
967 
968 // Execute the SQL request
969 $totalnbofrecord = 0;
970 $data = array();
971 if ($sql) {
972  $resql = $db->query($sql);
973  if (!$resql) {
974  print '<div class="warning">';
975  print dol_escape_htmltag($db->lasterror());
976  //print '<br>'.dol_escape_htmltag('SQL is '.$sql);
977  print '</div>';
978  } else {
979  $ifetch = 0;
980  $xi = 0;
981  $oldlabeltouse = '';
982  while ($obj = $db->fetch_object($resql)) {
983  $ifetch++;
984  if ($useagroupby) {
985  $xval = $search_xaxis[0];
986  $fieldforxkey = 'x_0';
987  $xlabel = $obj->$fieldforxkey;
988  $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval);
989 
990  // Define $xlabel
991  if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) {
992  $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey];
993  }
994  $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->transnoentitiesnoconv("Empty") : $langs->transnoentitiesnoconv("NotDefined")));
995 
996  if ($oldlabeltouse !== '' && ($labeltouse != $oldlabeltouse)) {
997  $xi++; // Increase $xi
998  }
999  //var_dump($labeltouse.' '.$oldlabeltouse.' '.$xi);
1000  $oldlabeltouse = $labeltouse;
1001 
1002  /* Example of value for $arrayofvaluesforgroupby
1003  * array (size=1)
1004  * 'g_0' =>
1005  * array (size=6)
1006  * 0 => string '0' (length=1)
1007  * '' => string 'Empty' (length=5)
1008  * '__NULL__' => string 'Not defined' (length=11)
1009  * 'done' => string 'done' (length=4)
1010  * 'processing' => string 'processing' (length=10)
1011  * 'undeployed' => string 'undeployed' (length=10)
1012  */
1013  foreach ($search_measures as $key => $val) {
1014  $gi = 0;
1015  foreach ($search_groupby as $gkey) {
1016  //var_dump('*** Fetch #'.$ifetch.' for labeltouse='.$labeltouse.' measure number '.$key.' and group g_'.$gi);
1017  //var_dump($arrayofvaluesforgroupby);
1018  foreach ($arrayofvaluesforgroupby['g_'.$gi] as $gvaluepossiblekey => $gvaluepossiblelabel) {
1019  $ykeysuffix = $gvaluepossiblelabel;
1020  $gvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $gval);
1021 
1022  $fieldfory = 'y_'.$key;
1023  $fieldforg = 'g_'.$gi;
1024  $fieldforybis = 'y_'.$key.'_'.$ykeysuffix;
1025  //var_dump('gvaluepossiblekey='.$gvaluepossiblekey.' gvaluepossiblelabel='.$gvaluepossiblelabel.' ykeysuffix='.$ykeysuffix.' gval='.$gval.' gvalwithoutsuffix='.$gvalwithoutprefix);
1026  //var_dump('fieldforg='.$fieldforg.' obj->$fieldforg='.$obj->$fieldforg.' fieldfory='.$fieldfory.' obj->$fieldfory='.$obj->$fieldfory.' fieldforybis='.$fieldforybis);
1027 
1028  if (!is_array($data[$xi])) {
1029  $data[$xi] = array();
1030  }
1031 
1032  if (!array_key_exists('label', $data[$xi])) {
1033  $data[$xi] = array();
1034  $data[$xi]['label'] = $labeltouse;
1035  }
1036 
1037  $objfieldforg = $obj->$fieldforg;
1038  if (is_null($objfieldforg)) {
1039  $objfieldforg = '__NULL__';
1040  }
1041 
1042  if ($gvaluepossiblekey == '0') { // $gvaluepossiblekey can have type int or string. So we create a special if, used when value is '0'
1043  //var_dump($objfieldforg.' == \'0\' -> '.($objfieldforg == '0'));
1044  if ($objfieldforg == '0') {
1045  // The record we fetch is for this group
1046  $data[$xi][$fieldforybis] = $obj->$fieldfory;
1047  } elseif (!isset($data[$xi][$fieldforybis])) {
1048  // The record we fetch is not for this group
1049  $data[$xi][$fieldforybis] = '0';
1050  }
1051  } else {
1052  //var_dump((string) $objfieldforg.' === '.(string) $gvaluepossiblekey.' -> '.((string) $objfieldforg === (string) $gvaluepossiblekey));
1053  if ((string) $objfieldforg === (string) $gvaluepossiblekey) {
1054  // The record we fetch is for this group
1055  $data[$xi][$fieldforybis] = $obj->$fieldfory;
1056  } elseif (!isset($data[$xi][$fieldforybis])) {
1057  // The record we fetch is not for this group
1058  $data[$xi][$fieldforybis] = '0';
1059  }
1060  }
1061  }
1062  //var_dump($data[$xi]);
1063  $gi++;
1064  }
1065  }
1066  } else { // No group by
1067  $xval = $search_xaxis[0];
1068  $fieldforxkey = 'x_0';
1069  $xlabel = $obj->$fieldforxkey;
1070  $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval);
1071 
1072  // Define $xlabel
1073  if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) {
1074  $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey];
1075  }
1076 
1077  $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->transnoentitiesnoconv("Empty") : $langs->transnoentitiesnoconv("NotDefined")));
1078  $xarrayforallseries = array('label' => $labeltouse);
1079  foreach ($search_measures as $key => $val) {
1080  $fieldfory = 'y_'.$key;
1081  $xarrayforallseries[$fieldfory] = $obj->$fieldfory;
1082  }
1083  $data[$xi] = $xarrayforallseries;
1084  $xi++;
1085  }
1086  }
1087 
1088  $totalnbofrecord = count($data);
1089  }
1090 }
1091 //var_dump($data);
1092 
1093 print '<!-- Section to show the result -->'."\n";
1094 print '<div class="customreportsoutput'.($totalnbofrecord ? '' : ' customreportsoutputnotdata').'">';
1095 
1096 
1097 if ($mode == 'grid') {
1098  // TODO
1099 }
1100 
1101 if ($mode == 'graph') {
1102  $WIDTH = '80%';
1103  $HEIGHT = (empty($_SESSION['dol_screenheight']) ? 400 : $_SESSION['dol_screenheight'] - 500);
1104 
1105  // Show graph
1106  $px1 = new DolGraph();
1107  $mesg = $px1->isGraphKo();
1108  if (!$mesg) {
1109  //var_dump($legend);
1110  //var_dump($data);
1111  $px1->SetData($data);
1112  unset($data);
1113 
1114  $arrayoftypes = array();
1115  foreach ($search_measures as $key => $val) {
1116  $arrayoftypes[] = $search_graph;
1117  }
1118 
1119  $px1->SetLegend($legend);
1120  $px1->setShowLegend($SHOWLEGEND);
1121  $px1->SetMinValue($px1->GetFloorMinValue());
1122  $px1->SetMaxValue($px1->GetCeilMaxValue());
1123  $px1->SetWidth($WIDTH);
1124  $px1->SetHeight($HEIGHT);
1125  $px1->SetYLabel($langs->trans("Y"));
1126  $px1->SetShading(3);
1127  $px1->SetHorizTickIncrement(1);
1128  $px1->SetCssPrefix("cssboxes");
1129  $px1->SetType($arrayoftypes);
1130  $px1->mode = 'depth';
1131  $px1->SetTitle('');
1132 
1133  $dir = $conf->user->dir_temp;
1134  dol_mkdir($dir);
1135  // $customreportkey may be defined when using customreports.php as an include
1136  $filenamekey = $dir.'/customreport_'.$object->element.(empty($customreportkey) ? '' : $customreportkey).'.png';
1137  $fileurlkey = DOL_URL_ROOT.'/viewimage.php?modulepart=user&file=customreport_'.$object->element.(empty($customreportkey) ? '' : $customreportkey).'.png';
1138 
1139  $px1->draw($filenamekey, $fileurlkey);
1140 
1141  $texttoshow = $langs->trans("NoRecordFound");
1142  if (!GETPOSTISSET('search_measures') || !GETPOSTISSET('search_xaxis')) {
1143  $texttoshow = $langs->trans("SelectYourGraphOptionsFirst");
1144  }
1145 
1146  print $px1->show($totalnbofrecord ? 0 : $texttoshow);
1147  }
1148 }
1149 
1150 if ($sql && !defined('MAIN_CUSTOM_REPORT_KEEP_GRAPH_ONLY')) {
1151  // Show admin info
1152  print '<br>'.info_admin($langs->trans("SQLUsedForExport").':<br> '.$sql, 0, 0, 1, '', 'TechnicalInformation');
1153 }
1154 
1155 print '<div>';
1156 
1157 if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
1158  print dol_get_fiche_end();
1159 
1160  llxFooter();
1161  // End of page
1162 
1163  $db->close();
1164 }
if($user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition: card.php:58
if(!defined('NOREQUIRESOC')) if(!defined('NOREQUIRETRAN')) if(!defined('NOTOKENRENEWAL')) if(!defined('NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined('NOREQUIREAJAX')) llxHeader()
Empty header.
Definition: wrapper.php:56
llxFooter()
Empty footer.
Definition: wrapper.php:70
Class to build graphs.
Class to manage standard extra fields.
Class to manage generation of HTML components Only common components must be here.
Class permettant la generation de composants html autre Only common components are here.
if(isModEnabled('invoice') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&!getDolGlobalString('MAIN_USE_NEW_SUPPLIERMOD') && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') && $user->hasRight('tax', 'charges', 'lire')) if(isModEnabled('invoice') &&isModEnabled('order') && $user->hasRight("commande", "lire") &&!getDolGlobalString('WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER')) $sql
Social contributions to pay.
Definition: index.php:744
if($cancel &&! $id) if($action=='add' &&! $cancel) if($action=='delete') if($id) $form
Actions.
Definition: card.php:143
fillArrayOfGroupBy($object, $tablealias, $labelofobject, &$arrayofgroupby, $level=0, &$count=0, &$tablepath='')
Fill arrayofgrupby for an object.
fillArrayOfMeasures($object, $tablealias, $labelofobject, &$arrayofmesures, $level=0, &$count=0, &$tablepath='')
Fill arrayofmesures for an object.
fillArrayOfXAxis($object, $tablealias, $labelofobject, &$arrayofxaxis, $level=0, &$count=0, &$tablepath='')
Fill arrayofmesures for an object.
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition: date.lib.php:613
dolCheckFilters($sqlfilters, &$error='', &$parenthesislevel=0)
Return if a $sqlfilters parameter has a valid balance of parenthesis.
dolForgeExplodeAnd($sqlfilters)
Explode an universal search string with AND parts.
dolExplodeIntoArray($string, $delimiter=';', $kv='=')
Split a string with 2 keys into key array.
img_picto($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0, $alt='', $morecss='', $marginleftonlyshort=2)
Show picto whatever it's its name (generic function)
GETPOSTINT($paramname, $method=0)
Return the value of a $_GET or $_POST supervariable, converted into integer.
dol_get_fiche_head($links=array(), $active='', $title='', $notab=0, $picto='', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limittoshow=0, $moretabssuffix='', $dragdropfile=0)
Show tabs of a record.
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
dol_get_fiche_end($notab=0)
Return tab footer of a card.
dol_eval($s, $returnvalue=1, $hideerrors=1, $onlysimplestring='1')
Replace eval function to add more security.
forgeSQLFromUniversalSearchCriteria($filter, &$errorstr='', $noand=0, $nopar=0, $noerror=0)
forgeSQLFromUniversalSearchCriteria
if(!function_exists('dol_getprefix')) dol_include_once($relpath, $classname='')
Make an include_once using default root and alternate root if it fails.
dol_now($mode='auto')
Return date for now.
dol_sort_array(&$array, $index, $order='asc', $natsort=0, $case_sensitive=0, $keepindex=0)
Advanced sort array by the value of a given key, which produces ascending (default) or descending out...
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
setEventMessages($mesg, $mesgs, $style='mesgs', $messagekey='', $noduplicate=0)
Set event messages in dol_events session object.
print_barre_liste($titre, $page, $file, $options='', $sortfield='', $sortorder='', $morehtmlcenter='', $num=-1, $totalnboflines='', $picto='generic', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limit=-1, $hideselectlimit=0, $hidenavigation=0, $pagenavastextinput=0, $morehtmlrightbeforearrow='')
Print a title with navigation controls for pagination.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
dol_trunc($string, $size=40, $trunc='right', $stringencoding='UTF-8', $nodot=0, $display=0)
Truncate a string to a particular length adding '…' if string larger than length.
GETPOSTISSET($paramname)
Return true if we are in a context of submitting the parameter $paramname from a POST of a form.
isModEnabled($module)
Is Dolibarr module enabled.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
dol_getdate($timestamp, $fast=false, $forcetimezone='')
Return an array with locale date info.
dol_mkdir($dir, $dataroot='', $newmask='')
Creation of a directory (this can create recursive subdir)
dol_escape_htmltag($stringtoescape, $keepb=0, $keepn=0, $noescapetags='', $escapeonlyhtmltags=0, $cleanalsojavascript=0)
Returns text escaped for inclusion in HTML alt or title or value tags, or into values of HTML input f...
div float
Buy price without taxes.
Definition: style.css.php:959
restrictedArea(User $user, $features, $object=0, $tableandshare='', $feature2='', $dbt_keyfield='fk_soc', $dbt_select='rowid', $isdraft=0, $mode=0)
Check permissions of a user to show a page and an object.
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0, $params=null)
Show a message to say access is forbidden and stop program.