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