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