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_CUSTOME_REPORT_AS_INCLUDE', 1); 19 * include DOL_DOCUMENT_ROOT.'/core/customreports.php'; 20 */ 21 22/** 23 * \file htdocs/core/customreports.php 24 * \ingroup core 25 * \brief Page to make custom reports 26 */ 27 28if (!defined('USE_CUSTOME_REPORT_AS_INCLUDE')) 29{ 30 require '../main.inc.php'; 31 32 // Get parameters 33 $action = GETPOST('action', 'aZ09') ?GETPOST('action', 'aZ09') : 'view'; // The action 'add', 'create', 'edit', 'update', 'view', ... 34 $massaction = GETPOST('massaction', 'alpha'); // The bulk action (combo box choice into lists) 35 36 $mode = GETPOST('mode', 'alpha') ? GETPOST('mode', 'alpha') : 'graph'; 37 $objecttype = GETPOST('objecttype', 'aZ09'); 38 $tabfamily = GETPOST('tabfamily', 'aZ09'); 39 40 if (empty($objecttype)) $objecttype = 'thirdparty'; 41 42 $search_filters = GETPOST('search_filters', 'array'); 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') $search_xaxis = array(GETPOST('search_xaxis', 'alpha')); 47 else $search_xaxis = array(); 48 //$search_groupby = GETPOST('search_groupby', 'array'); 49 if (GETPOST('search_groupby', 'alpha') && GETPOST('search_groupby', 'alpha') != '-1') $search_groupby = array(GETPOST('search_groupby', 'alpha')); 50 else $search_groupby = array(); 51 52 $search_yaxis = GETPOST('search_yaxis', 'array'); 53 $search_graph = GETPOST('search_graph', 'none'); 54 55 // Load variable for pagination 56 $limit = GETPOST('limit', 'int') ? GETPOST('limit', 'int') : $conf->liste_limit; 57 $sortfield = GETPOST('sortfield', 'aZ09comma'); 58 $sortorder = GETPOST('sortorder', 'aZ09comma'); 59 $page = GETPOSTISSET('pageplusone') ? (GETPOST('pageplusone') - 1) : GETPOST("page", 'int'); 60 if (empty($page) || $page == -1 || GETPOST('button_search', 'alpha') || GETPOST('button_removefilter', 'alpha') || (empty($toselect) && $massaction === '0')) { $page = 0; } // If $page is not defined, or '' or -1 or if we click on clear filters or if we select empty mass action 61 $offset = $limit * $page; 62 $pageprev = $page - 1; 63 $pagenext = $page + 1; 64 65 $diroutputmassaction = $conf->user->dir_temp.'/'.$user->id.'/customreport'; 66} 67 68require_once DOL_DOCUMENT_ROOT."/core/lib/admin.lib.php"; 69require_once DOL_DOCUMENT_ROOT."/core/lib/company.lib.php"; 70require_once DOL_DOCUMENT_ROOT."/core/class/dolgraph.class.php"; 71require_once DOL_DOCUMENT_ROOT."/core/class/doleditor.class.php"; 72require_once DOL_DOCUMENT_ROOT."/core/class/html.formother.class.php"; 73 74// Load traductions files requiredby by page 75$langs->loadLangs(array("companies", "other", "exports", "sendings")); 76 77$extrafields = new ExtraFields($db); 78 79$hookmanager->initHooks(array('customreport')); // Note that conf->hooks_modules contains array 80 81$title = ''; 82$picto = ''; 83$head = array(); 84$object = null; 85$ObjectClassName = ''; 86// Objects available by default 87$arrayoftype = array( 88 'thirdparty' => array('label' => 'ThirdParties', 'ObjectClassName' => 'Societe', 'enabled' => $conf->societe->enabled, 'ClassPath' => "/societe/class/societe.class.php"), 89 'contact' => array('label' => 'Contacts', 'ObjectClassName' => 'Contact', 'enabled' => $conf->societe->enabled, 'ClassPath' => "/contact/class/contact.class.php"), 90 'proposal' => array('label' => 'Proposals', 'ObjectClassName' => 'Propal', 'enabled' => $conf->propal->enabled, 'ClassPath' => "/comm/propal/class/propal.class.php"), 91 'order' => array('label' => 'Orders', 'ObjectClassName' => 'Commande', 'enabled' => $conf->commande->enabled, 'ClassPath' => "/commande/class/commande.class.php"), 92 'invoice' => array('label' => 'Invoices', 'ObjectClassName' => 'Facture', 'enabled' => $conf->facture->enabled, 'ClassPath' => "/compta/facture/class/facture.class.php"), 93 'invoice_template'=>array('label' => 'PredefinedInvoices', 'ObjectClassName' => 'FactureRec', 'enabled' => $conf->facture->enabled, 'ClassPath' => "/compta/class/facturerec.class.php", 'langs'=>'bills'), 94 'contract' => array('label' => 'Contracts', 'ObjectClassName' => 'Contrat', 'enabled' => $conf->contrat->enabled, 'ClassPath' => "/contrat/class/contrat.class.php", 'langs'=>'contract'), 95 'bom' => array('label' => 'BOM', 'ObjectClassName' => 'Bom', 'enabled' => $conf->bom->enabled), 96 'mo' => array('label' => 'MO', 'ObjectClassName' => 'Mo', 'enabled' => $conf->mrp->enabled, 'ClassPath' => "/mrp/class/mo.class.php"), 97 'ticket' => array('label' => 'Ticket', 'ObjectClassName' => 'Ticket', 'enabled' => $conf->ticket->enabled), 98 'member' => array('label' => 'Adherent', 'ObjectClassName' => 'Adherent', 'enabled' => $conf->adherent->enabled, 'ClassPath' => "/adherents/class/adherent.class.php", 'langs'=>'members'), 99 'cotisation' => array('label' => 'Subscriptions', 'ObjectClassName' => 'Subscription', 'enabled' => $conf->adherent->enabled, 'ClassPath' => "/adherents/class/subscription.class.php", 'langs'=>'members'), 100); 101 102// Complete $arrayoftype by external modules 103$parameters = array('objecttype'=>$objecttype, 'tabfamily'=>$tabfamily); 104$reshook = $hookmanager->executeHooks('loadDataForCustomReports', $parameters, $object, $action); // Note that $action and $object may have been modified by some hooks 105if ($reshook < 0) setEventMessages($hookmanager->error, $hookmanager->errors, 'errors'); 106elseif (is_array($hookmanager->resArray)) { 107 if (!empty($hookmanager->resArray['title'])) { // Add entries for tabs 108 $title = $hookmanager->resArray['title']; 109 } 110 if (!empty($hookmanager->resArray['picto'])) { // Add entries for tabs 111 $picto = $hookmanager->resArray['picto']; 112 } 113 if (!empty($hookmanager->resArray['head'])) { // Add entries for tabs 114 $head = array_merge($head, $hookmanager->resArray['head']); 115 } 116 if (!empty($hookmanager->resArray['arrayoftype'])) { // Add entries from hook 117 foreach ($hookmanager->resArray['arrayoftype'] as $key => $val) { 118 $arrayoftype[$key] = $val; 119 } 120 } 121} 122 123if ($objecttype) { 124 try { 125 if (!empty($arrayoftype[$objecttype]['ClassPath'])) { 126 dol_include_once($arrayoftype[$objecttype]['ClassPath']); 127 } else { 128 dol_include_once("/".$objecttype."/class/".$objecttype.".class.php"); 129 } 130 $ObjectClassName = $arrayoftype[$objecttype]['ObjectClassName']; 131 $object = new $ObjectClassName($db); 132 } catch (Exception $e) { 133 print 'Failed to load class for type '.$objecttype; 134 } 135} 136 137// Security check 138$socid = 0; 139if ($user->socid > 0) // Protection if external user 140{ 141 //$socid = $user->socid; 142 accessforbidden(); 143} 144 145$result = restrictedArea($user, $object->element, 0, ''); 146 147// Fetch optionals attributes and labels 148$extrafields->fetch_name_optionals_label($object->table_element); 149//$extrafields->fetch_name_optionals_label($object->table_element_line); 150 151$search_array_options = $extrafields->getOptionalsFromPost($object->table_element, '', 'search_'); 152 153$search_component_params = array(''); 154 155$MAXUNIQUEVALFORGROUP = 20; 156$MAXMEASURESINBARGRAPH = 20; 157 158$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); 159$MM = substr($langs->trans("Month"), 0, 1).substr($langs->trans("Month"), 0, 1); 160$DD = substr($langs->trans("Day"), 0, 1).substr($langs->trans("Day"), 0, 1); 161$HH = substr($langs->trans("Hour"), 0, 1).substr($langs->trans("Hour"), 0, 1); 162$MI = substr($langs->trans("Minute"), 0, 1).substr($langs->trans("Minute"), 0, 1); 163$SS = substr($langs->trans("Second"), 0, 1).substr($langs->trans("Second"), 0, 1); 164 165$arrayofmesures = array('t.count'=>'Count'); 166$arrayofxaxis = array(); 167$arrayofgroupby = array(); 168$arrayofyaxis = array(); 169$arrayofvaluesforgroupby = array(); 170 171 172 173/* 174 * Actions 175 */ 176 177 178 179 180 181/* 182 * View 183 */ 184 185$form = new Form($db); 186$formother = new FormOther($db); 187 188if (!defined('USE_CUSTOME_REPORT_AS_INCLUDE')) { 189 llxHeader('', $langs->transnoentitiesnoconv('CustomReports'), ''); 190 191 print dol_get_fiche_head($head, 'customreports', $title, -1, $picto); 192} 193 194// Check parameters 195if ($action == 'viewgraph') { 196 if (!count($search_measures)) { 197 setEventMessages($langs->trans("AtLeastOneMeasureIsRequired"), null, 'warnings'); 198 } elseif ($mode == 'graph' && count($search_xaxis) > 1) { 199 setEventMessages($langs->trans("OnlyOneFieldForXAxisIsPossible"), null, 'warnings'); 200 $search_xaxis = array(0 => $search_xaxis[0]); 201 } 202 if (count($search_groupby) >= 2) { 203 setEventMessages($langs->trans("ErrorOnlyOneFieldForGroupByIsPossible"), null, 'warnings'); 204 $search_groupby = array(0 => $search_groupby[0]); 205 } 206 if (!count($search_xaxis)) { 207 setEventMessages($langs->trans("AtLeastOneXAxisIsRequired"), null, 'warnings'); 208 } elseif ($mode == 'graph' && $search_graph == 'bars' && count($search_measures) > $MAXMEASURESINBARGRAPH) { 209 $langs->load("errors"); 210 setEventMessages($langs->trans("GraphInBarsAreLimitedToNMeasures", $MAXMEASURESINBARGRAPH), null, 'warnings'); 211 $search_graph = 'lines'; 212 } 213} 214 215// Get all possible values of fields when a 'group by' is set, and save this into $arrayofvaluesforgroupby 216// $arrayofvaluesforgroupby will be used to forge lael of each grouped series 217if (is_array($search_groupby) && count($search_groupby)) { 218 foreach ($search_groupby as $gkey => $gval) { 219 $gvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $gval); 220 221 if (preg_match('/\-year$/', $search_groupby[$gkey])) { 222 $tmpval = preg_replace('/\-year$/', '', $search_groupby[$gkey]); 223 $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y')"; 224 } elseif (preg_match('/\-month$/', $search_groupby[$gkey])) { 225 $tmpval = preg_replace('/\-month$/', '', $search_groupby[$gkey]); 226 $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y-%m')"; 227 } elseif (preg_match('/\-day$/', $search_groupby[$gkey])) { 228 $tmpval = preg_replace('/\-day$/', '', $search_groupby[$gkey]); 229 $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d')"; 230 } else { 231 $fieldtocount = $search_groupby[$gkey]; 232 } 233 234 $sql = 'SELECT DISTINCT '.$fieldtocount.' as val'; 235 if (strpos($fieldtocount, 'te.') === 0) { 236 $sql .= ' FROM '.MAIN_DB_PREFIX.$object->table_element.'_extrafields as te'; 237 } else { 238 $sql .= ' FROM '.MAIN_DB_PREFIX.$object->table_element.' as t'; 239 } 240 // TODO Add the where here 241 242 $sql .= ' LIMIT '.($MAXUNIQUEVALFORGROUP + 1); 243 244 //print $sql; 245 $resql = $db->query($sql); 246 if (!$resql) { 247 dol_print_error($db); 248 } 249 250 while ($obj = $db->fetch_object($resql)) { 251 if (is_null($obj->val)) { 252 $keytouse = '__NULL__'; 253 $valuetranslated = $langs->transnoentitiesnoconv("NotDefined"); 254 } elseif ($obj->val === '') { 255 $keytouse = ''; 256 $valuetranslated = $langs->transnoentitiesnoconv("Empty"); 257 } else { 258 $keytouse = (string) $obj->val; 259 $valuetranslated = $obj->val; 260 } 261 262 $regs = array(); 263 if (!empty($object->fields[$gvalwithoutprefix]['arrayofkeyval'])) { 264 $valuetranslated = $object->fields[$gvalwithoutprefix]['arrayofkeyval'][$obj->val]; 265 if (is_null($valuetranslated)) $valuetranslated = $langs->transnoentitiesnoconv("UndefinedKey"); 266 $valuetranslated = $langs->trans($valuetranslated); 267 } elseif (preg_match('/integer:([^:]+):([^:]+)$/', $object->fields[$gvalwithoutprefix]['type'], $regs)) { 268 $classname = $regs[1]; 269 $classpath = $regs[2]; 270 dol_include_once($classpath); 271 if (class_exists($classname)) { 272 $tmpobject = new $classname($db); 273 $tmpobject->fetch($obj->val); 274 foreach ($tmpobject->fields as $fieldkey => $field) { 275 if ($field['showoncombobox']) { 276 $valuetranslated = $tmpobject->$fieldkey; 277 //if ($valuetranslated == '-') $valuetranslated = $langs->transnoentitiesnoconv("Unknown") 278 break; 279 } 280 } 281 //$valuetranslated = $tmpobject->ref.'eee'; 282 } 283 } 284 285 $arrayofvaluesforgroupby['g_'.$gkey][$keytouse] = $valuetranslated; 286 } 287 asort($arrayofvaluesforgroupby['g_'.$gkey]); 288 289 if (count($arrayofvaluesforgroupby['g_'.$gkey]) > $MAXUNIQUEVALFORGROUP) { 290 $langs->load("errors"); 291 if (strpos($fieldtocount, 'te.') === 0) { 292 //if (!empty($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix])) { 293 // $langs->load($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix]); 294 //} 295 $keyforlabeloffield = $extrafields->attributes[$object->table_element]['label'][$gvalwithoutprefix]; 296 } else { 297 $keyforlabeloffield = $object->fields[$gvalwithoutprefix]['label']; 298 } 299 //var_dump($gkey.' '.$gval.' '.$gvalwithoutprefix); 300 $gvalwithoutprefix = preg_replace('/\-(year|month|day)/', '', $gvalwithoutprefix); 301 $labeloffield = $langs->transnoentitiesnoconv($keyforlabeloffield); 302 setEventMessages($langs->trans("ErrorTooManyDifferentValueForSelectedGroupBy", $MAXUNIQUEVALFORGROUP, $labeloffield), null, 'warnings'); 303 $search_groupby = array(); 304 } 305 306 $db->free($resql); 307 } 308} 309//var_dump($arrayofvaluesforgroupby);exit; 310 311 312$tmparray = dol_getdate(dol_now()); 313$endyear = $tmparray['year']; 314$endmonth = $tmparray['mon']; 315$datelastday = dol_get_last_day($endyear, $endmonth, 1); 316$startyear = $endyear - 2; 317 318$param = ''; 319 320print '<form method="post" action="'.$_SERVER['PHP_SELF'].'">'; 321print '<input type="hidden" name="token" value="'.newToken().'">'; 322print '<input type="hidden" name="action" value="viewgraph">'; 323print '<input type="hidden" name="tabfamily" value="'.$tabfamily.'">'; 324 325print '<div class="liste_titre liste_titre_bydiv centpercent">'; 326 327// Select object 328print '<div class="divadvancedsearchfield center floatnone">'; 329print '<div class="inline-block"><span class="opacitymedium">'.$langs->trans("StatisticsOn").'</span></div> '; 330$newarrayoftype = array(); 331foreach ($arrayoftype as $key => $val) { 332 if (dol_eval($val['enabled'], 1)) { 333 $newarrayoftype[$key] = $arrayoftype[$key]; 334 } 335 if ($val['langs']) { 336 $langs->load($val['langs']); 337 } 338} 339print $form->selectarray('objecttype', $newarrayoftype, $objecttype, 0, 0, 0, '', 1, 0, 0, '', 'minwidth200', 1); 340if (empty($conf->use_javascript_ajax)) print '<input type="submit" class="button buttongen" name="changeobjecttype" value="'.$langs->trans("Refresh").'">'; 341else { 342 print '<script type="text/javascript" language="javascript"> 343 jQuery(document).ready(function() { 344 jQuery("#objecttype").change(function() { 345 console.log("Reload for "+jQuery("#objecttype").val()); 346 location.href = "'.$_SERVER["PHP_SELF"].'?objecttype="+jQuery("#objecttype").val()+"'.($tabfamily ? '&tabfamily='.$tabfamily : '').'"; 347 }); 348 }); 349 </script>'; 350} 351print '</div><div class="clearboth"></div>'; 352 353// Add Filter 354print '<div class="divadvancedsearchfield quatrevingtpercent">'; 355print $form->searchComponent(array($object->element => $object->fields), $search_component_params); 356print '</div>'; 357 358// Add measures into array 359print '<div class="divadvancedsearchfield clearboth">'; 360foreach ($object->fields as $key => $val) { 361 if (!empty($val['isameasure']) && (!isset($val['enabled']) || dol_eval($val['enabled'], 1))) { 362 $arrayofmesures['t.'.$key.'-sum'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Sum").')</span>'; 363 $arrayofmesures['t.'.$key.'-average'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Average").')</span>'; 364 $arrayofmesures['t.'.$key.'-min'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Minimum").')</span>'; 365 $arrayofmesures['t.'.$key.'-max'] = $langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Maximum").')</span>'; 366 } 367} 368// Add extrafields to Measures 369if ($object->isextrafieldmanaged) { 370 foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) { 371 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))) { 372 $arrayofmesures['te.'.$key.'-sum'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Sum").')</span>'; 373 $arrayofmesures['te.'.$key.'-average'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Average").')</span>'; 374 $arrayofmesures['te.'.$key.'-min'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Minimum").')</span>'; 375 $arrayofmesures['te.'.$key.'-max'] = $langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Maximum").')</span>'; 376 } 377 } 378} 379print '<div class="inline-block"><span class="fas fa-chart-line paddingright" title="'.$langs->trans("Measures").'"></span>'.$langs->trans("Measures").'</div> '; 380print $form->multiselectarray('search_measures', $arrayofmesures, $search_measures, 0, 0, 'minwidth400', 1); 381print '</div>'; 382 383 384// Group by 385print '<div class="divadvancedsearchfield">'; 386print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-horizontal paddingright" title="'.$langs->trans("GroupBy").'"></span>'.$langs->trans("GroupBy").'</div> '; 387print $formother->selectGroupByField($object, $search_groupby, $arrayofgroupby); 388print '</div>'; 389 390 391// XAxis 392print '<div class="divadvancedsearchfield">'; 393print '<div class="inline-block"><span class="fas fa-ruler-horizontal paddingright" title="'.$langs->trans("XAxis").'"></span>'.$langs->trans("XAxis").'</div> '; 394print $formother->selectXAxisField($object, $search_xaxis, $arrayofxaxis); 395print '</div>'; 396 397 398if ($mode == 'grid') { 399 // YAxis 400 print '<div class="divadvancedsearchfield">'; 401 foreach ($object->fields as $key => $val) { 402 if (empty($val['measure']) && (!isset($val['enabled']) || dol_eval($val['enabled'], 1))) { 403 if (in_array($key, array('id', 'rowid', 'entity', 'last_main_doc', 'extraparams'))) continue; 404 if (preg_match('/^fk_/', $key)) continue; 405 if (in_array($val['type'], array('html', 'text'))) continue; 406 if (in_array($val['type'], array('timestamp', 'date', 'datetime'))) { 407 $arrayofyaxis['t.'.$key.'-year'] = array('label' => $langs->trans($val['label']).' ('.$YYYY.')', 'position' => $val['position']); 408 $arrayofyaxis['t.'.$key.'-month'] = array('label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.')', 'position' => $val['position']); 409 $arrayofyaxis['t.'.$key.'-day'] = array('label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.'-'.$DD.')', 'position' => $val['position']); 410 } else { 411 $arrayofyaxis['t.'.$key] = array('label' => $val['label'], 'position' => (int) $val['position']); 412 } 413 } 414 // Add measure from extrafields 415 if ($object->isextrafieldmanaged) { 416 foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) { 417 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))) { 418 $arrayofyaxis['te.'.$key] = array('label' => $extrafields->attributes[$object->table_element]['label'][$key], 'position' => (int) $extrafields->attributes[$object->table_element]['pos'][$key]); 419 } 420 } 421 } 422 } 423 $arrayofyaxis = dol_sort_array($arrayofyaxis, 'position'); 424 $arrayofyaxislabel = array(); 425 foreach ($arrayofyaxis as $key => $val) { 426 $arrayofyaxislabel[$key] = $val['label']; 427 } 428 print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-vertical paddingright" title="'.$langs->trans("YAxis").'"></span>'.$langs->trans("YAxis").'</div> '; 429 print $form->multiselectarray('search_yaxis', $arrayofyaxislabel, $search_yaxis, 0, 0, 'minwidth100', 1); 430 print '</div>'; 431} 432 433if ($mode == 'graph') { 434 print '<div class="divadvancedsearchfield">'; 435 $arrayofgraphs = array('bars' => 'Bars', 'lines' => 'Lines'); // also 'pies' 436 print '<div class="inline-block opacitymedium"><span class="fas fa-chart-area paddingright" title="'.$langs->trans("Graph").'"></span>'.$langs->trans("Graph").'</div> '; 437 print $form->selectarray('search_graph', $arrayofgraphs, $search_graph, 0, 0, 0, 'minwidth100', 1); 438 print '</div>'; 439} 440print '<div class="divadvancedsearchfield">'; 441print '<input type="submit" class="button buttongen" value="'.$langs->trans("Refresh").'">'; 442print '</div>'; 443print '</div>'; 444print '</form>'; 445 446// Generate the SQL request 447$sql = ''; 448if (!empty($search_measures) && !empty($search_xaxis)) 449{ 450 $fieldid = 'rowid'; 451 452 $sql = 'SELECT '; 453 foreach ($search_xaxis as $key => $val) { 454 if (preg_match('/\-year$/', $val)) { 455 $tmpval = preg_replace('/\-year$/', '', $val); 456 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y') as x_".$key.', '; 457 } elseif (preg_match('/\-month$/', $val)) { 458 $tmpval = preg_replace('/\-month$/', '', $val); 459 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m') as x_".$key.', '; 460 } elseif (preg_match('/\-day$/', $val)) { 461 $tmpval = preg_replace('/\-day$/', '', $val); 462 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d') as x_".$key.', '; 463 } else $sql .= $val.' as x_'.$key.', '; 464 } 465 foreach ($search_groupby as $key => $val) { 466 if (preg_match('/\-year$/', $val)) { 467 $tmpval = preg_replace('/\-year$/', '', $val); 468 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y') as g_".$key.', '; 469 } elseif (preg_match('/\-month$/', $val)) { 470 $tmpval = preg_replace('/\-month$/', '', $val); 471 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m') as g_".$key.', '; 472 } elseif (preg_match('/\-day$/', $val)) { 473 $tmpval = preg_replace('/\-day$/', '', $val); 474 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d') as g_".$key.', '; 475 } else $sql .= $val.' as g_'.$key.', '; 476 } 477 foreach ($search_measures as $key => $val) { 478 if ($val == 't.count') $sql .= 'COUNT(t.'.$fieldid.') as y_'.$key.', '; 479 elseif (preg_match('/\-sum$/', $val)) { 480 $tmpval = preg_replace('/\-sum$/', '', $val); 481 $sql .= 'SUM('.$db->ifsql($tmpval.' IS NULL', '0', $tmpval).') as y_'.$key.', '; 482 } elseif (preg_match('/\-average$/', $val)) { 483 $tmpval = preg_replace('/\-average$/', '', $val); 484 $sql .= 'AVG('.$db->ifsql($tmpval.' IS NULL', '0', $tmpval).') as y_'.$key.', '; 485 } elseif (preg_match('/\-min$/', $val)) { 486 $tmpval = preg_replace('/\-min$/', '', $val); 487 $sql .= 'MIN('.$db->ifsql($tmpval.' IS NULL', '0', $tmpval).') as y_'.$key.', '; 488 } elseif (preg_match('/\-max$/', $val)) { 489 $tmpval = preg_replace('/\-max$/', '', $val); 490 $sql .= 'MAX('.$db->ifsql($tmpval.' IS NULL', '0', $tmpval).') as y_'.$key.', '; 491 } 492 } 493 $sql = preg_replace('/,\s*$/', '', $sql); 494 $sql .= ' FROM '.MAIN_DB_PREFIX.$object->table_element.' as t'; 495 // Add measure from extrafields 496 if ($object->isextrafieldmanaged) { 497 $sql .= ' LEFT JOIN '.MAIN_DB_PREFIX.$object->table_element.'_extrafields as te ON te.fk_object = t.'.$fieldid; 498 } 499 if ($object->ismultientitymanaged) { 500 if ($object->ismultientitymanaged == 1) { 501 // Nothing here 502 } else { 503 $tmparray = explode('@', $object->ismultientitymanaged); 504 $sql .= ' INNER JOIN '.MAIN_DB_PREFIX.$tmparray[1].' as parenttable ON t.'.$tmparray[0].' = parenttable.rowid'; 505 $sql .= ' AND parenttable.entity IN ('.getEntity($tmparray[1]).')'; 506 } 507 } 508 $sql .= ' WHERE 1 = 1'; 509 if ($object->ismultientitymanaged == 1) { 510 $sql .= ' AND entity IN ('.getEntity($object->element).')'; 511 } 512 foreach ($search_filters as $key => $val) { 513 // TODO Add the where here 514 } 515 $sql .= ' GROUP BY '; 516 foreach ($search_xaxis as $key => $val) { 517 if (preg_match('/\-year$/', $val)) { 518 $tmpval = preg_replace('/\-year$/', '', $val); 519 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y'), "; 520 } elseif (preg_match('/\-month$/', $val)) { 521 $tmpval = preg_replace('/\-month$/', '', $val); 522 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m'), "; 523 } elseif (preg_match('/\-day$/', $val)) { 524 $tmpval = preg_replace('/\-day$/', '', $val); 525 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d'), "; 526 } else $sql .= $val.', '; 527 } 528 foreach ($search_groupby as $key => $val) { 529 if (preg_match('/\-year$/', $val)) { 530 $tmpval = preg_replace('/\-year$/', '', $val); 531 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y'), "; 532 } elseif (preg_match('/\-month$/', $val)) { 533 $tmpval = preg_replace('/\-month$/', '', $val); 534 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m'), "; 535 } elseif (preg_match('/\-day$/', $val)) { 536 $tmpval = preg_replace('/\-day$/', '', $val); 537 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d'), "; 538 } else $sql .= $val.', '; 539 } 540 $sql = preg_replace('/,\s*$/', '', $sql); 541 $sql .= ' ORDER BY '; 542 foreach ($search_xaxis as $key => $val) { 543 if (preg_match('/\-year$/', $val)) { 544 $tmpval = preg_replace('/\-year$/', '', $val); 545 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y'), "; 546 } elseif (preg_match('/\-month$/', $val)) { 547 $tmpval = preg_replace('/\-month$/', '', $val); 548 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m'), "; 549 } elseif (preg_match('/\-day$/', $val)) { 550 $tmpval = preg_replace('/\-day$/', '', $val); 551 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d'), "; 552 } else $sql .= $val.', '; 553 } 554 foreach ($search_groupby as $key => $val) { 555 if (preg_match('/\-year$/', $val)) { 556 $tmpval = preg_replace('/\-year$/', '', $val); 557 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y'), "; 558 } elseif (preg_match('/\-month$/', $val)) { 559 $tmpval = preg_replace('/\-month$/', '', $val); 560 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m'), "; 561 } elseif (preg_match('/\-day$/', $val)) { 562 $tmpval = preg_replace('/\-day$/', '', $val); 563 $sql .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d'), "; 564 } else $sql .= $val.', '; 565 } 566 $sql = preg_replace('/,\s*$/', '', $sql); 567} 568//print $sql; 569 570$legend = array(); 571foreach ($search_measures as $key => $val) { 572 $legend[] = $langs->trans($arrayofmesures[$val]); 573} 574 575$useagroupby = (is_array($search_groupby) && count($search_groupby)); 576//var_dump($useagroupby); 577//var_dump($arrayofvaluesforgroupby); 578 579// Execute the SQL request 580$totalnbofrecord = 0; 581$data = array(); 582if ($sql) { 583 $resql = $db->query($sql); 584 if (!$resql) { 585 dol_print_error($db); 586 } 587 588 $ifetch = 0; $xi = 0; $oldlabeltouse = ''; 589 while ($obj = $db->fetch_object($resql)) { 590 $ifetch++; 591 if ($useagroupby) { 592 $xval = $search_xaxis[0]; 593 $fieldforxkey = 'x_0'; 594 $xlabel = $obj->$fieldforxkey; 595 $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval); 596 597 // Define $xlabel 598 if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) { 599 $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey]; 600 } 601 $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->trans("Empty") : $langs->trans("NotDefined"))); 602 603 if ($oldlabeltouse && ($labeltouse != $oldlabeltouse)) { 604 $xi++; // Increase $xi 605 } 606 //var_dump($labeltouse.' '.$oldlabeltouse.' '.$xi); 607 $oldlabeltouse = $labeltouse; 608 609 /* Example of value for $arrayofvaluesforgroupby 610 * array (size=1) 611 * 'g_0' => 612 * array (size=6) 613 * 0 => string '0' (length=1) 614 * '' => string 'Empty' (length=5) 615 * '__NULL__' => string 'Not defined' (length=11) 616 * 'done' => string 'done' (length=4) 617 * 'processing' => string 'processing' (length=10) 618 * 'undeployed' => string 'undeployed' (length=10) 619 */ 620 foreach ($search_measures as $key => $val) { 621 $gi = 0; 622 foreach ($search_groupby as $gkey) { 623 //var_dump('*** Fetch #'.$ifetch.' for labeltouse='.$labeltouse.' measure number '.$key.' and group g_'.$gi); 624 //var_dump($arrayofvaluesforgroupby); 625 foreach ($arrayofvaluesforgroupby['g_'.$gi] as $gvaluepossiblekey => $gvaluepossiblelabel) { 626 $ykeysuffix = $gvaluepossiblelabel; 627 $gvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $gval); 628 629 $fieldfory = 'y_'.$key; 630 $fieldforg = 'g_'.$gi; 631 $fieldforybis = 'y_'.$key.'_'.$ykeysuffix; 632 //var_dump('gvaluepossiblekey='.$gvaluepossiblekey.' gvaluepossiblelabel='.$gvaluepossiblelabel.' ykeysuffix='.$ykeysuffix.' gval='.$gval.' gvalwithoutsuffix='.$gvalwithoutprefix); 633 //var_dump('fieldforg='.$fieldforg.' obj->$fieldforg='.$obj->$fieldforg.' fieldfory='.$fieldfory.' obj->$fieldfory='.$obj->$fieldfory.' fieldforybis='.$fieldforybis); 634 635 if (!is_array($data[$xi])) $data[$xi] = array(); 636 637 if (!array_key_exists('label', $data[$xi])) { 638 $data[$xi] = array(); 639 $data[$xi]['label'] = $labeltouse; 640 } 641 642 $objfieldforg = $obj->$fieldforg; 643 if (is_null($objfieldforg)) $objfieldforg = '__NULL__'; 644 645 if ($gvaluepossiblekey == '0') { // $gvaluepossiblekey can have type int or string. So we create a special if, used when value is '0' 646 //var_dump($objfieldforg.' == \'0\' -> '.($objfieldforg == '0')); 647 if ($objfieldforg == '0') { 648 // The record we fetch is for this group 649 $data[$xi][$fieldforybis] = $obj->$fieldfory; 650 } 651 // The record we fetch is not for this group 652 elseif (!isset($data[$xi][$fieldforybis])) { 653 $data[$xi][$fieldforybis] = '0'; 654 } 655 } else { 656 //var_dump((string) $objfieldforg.' === '.(string) $gvaluepossiblekey.' -> '.((string) $objfieldforg === (string) $gvaluepossiblekey)); 657 if ((string) $objfieldforg === (string) $gvaluepossiblekey) { 658 // The record we fetch is for this group 659 $data[$xi][$fieldforybis] = $obj->$fieldfory; 660 } 661 // The record we fetch is not for this group 662 elseif (!isset($data[$xi][$fieldforybis])) { 663 $data[$xi][$fieldforybis] = '0'; 664 } 665 } 666 } 667 //var_dump($data[$xi]); 668 $gi++; 669 } 670 } 671 } else { // No group by 672 $xval = $search_xaxis[0]; 673 $fieldforxkey = 'x_0'; 674 $xlabel = $obj->$fieldforxkey; 675 $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval); 676 677 // Define $xlabel 678 if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) { 679 $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey]; 680 } 681 682 $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->trans("Empty") : $langs->trans("NotDefined"))); 683 $xarrayforallseries = array('label' => $labeltouse); 684 foreach ($search_measures as $key => $val) { 685 $fieldfory = 'y_'.$key; 686 $xarrayforallseries[$fieldfory] = $obj->$fieldfory; 687 } 688 $data[$xi] = $xarrayforallseries; 689 $xi++; 690 } 691 } 692 693 $totalnbofrecord = count($data); 694} 695//var_dump($data); 696 697 698print '<div class="customreportsoutput'.($totalnbofrecord ? '' : ' customreportsoutputnotdata').'">'; 699 700 701if ($mode == 'grid') { 702 // TODO 703} 704 705if ($mode == 'graph') { 706 $WIDTH = '80%'; 707 $HEIGHT = 200; 708 709 // Show graph 710 $px1 = new DolGraph(); 711 $mesg = $px1->isGraphKo(); 712 if (!$mesg) 713 { 714 /*var_dump($legend); 715 var_dump($data);*/ 716 $px1->SetData($data); 717 unset($data); 718 719 $arrayoftypes = array(); 720 foreach ($search_measures as $key => $val) { 721 $arrayoftypes[] = $search_graph; 722 } 723 724 $px1->SetLegend($legend); 725 $px1->SetMinValue($px1->GetFloorMinValue()); 726 $px1->SetMaxValue($px1->GetCeilMaxValue()); 727 $px1->SetWidth($WIDTH); 728 $px1->SetHeight($HEIGHT); 729 $px1->SetYLabel($langs->trans("Y")); 730 $px1->SetShading(3); 731 $px1->SetHorizTickIncrement(1); 732 $px1->SetCssPrefix("cssboxes"); 733 $px1->SetType($arrayoftypes); 734 $px1->mode = 'depth'; 735 $px1->SetTitle(''); 736 737 $dir = $conf->user->dir_temp; 738 dol_mkdir($dir); 739 $filenamenb = $dir.'/customreport_'.$object->element.'.png'; 740 $fileurlnb = DOL_URL_ROOT.'/viewimage.php?modulepart=user&file=customreport_'.$object->element.'.png'; 741 742 $px1->draw($filenamenb, $fileurlnb); 743 744 $texttoshow = $langs->trans("NoRecordFound"); 745 if (!GETPOSTISSET('search_measures') || !GETPOSTISSET('search_xaxis')) { 746 $texttoshow = $langs->trans("SelectYourGraphOptionsFirst"); 747 } 748 749 print $px1->show($totalnbofrecord ? 0 : $texttoshow); 750 } 751} 752 753if ($sql) { 754 // Show admin info 755 print '<br>'.info_admin($langs->trans("SQLUsedForExport").':<br> '.$sql, 0, 0, 1, '', 'TechnicalInformation'); 756} 757 758print '<div>'; 759 760if (!defined('USE_CUSTOME_REPORT_AS_INCLUDE')) { 761 print dol_get_fiche_end(); 762} 763 764// End of page 765llxFooter(); 766 767$db->close(); 768