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