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