12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286 |
- <?php
- /* Copyright (C) 2020 Laurent Destailleur <eldy@users.sourceforge.net>
- *
- * This program is free software; you can redistribute it and/or modify
- * it under the terms of the GNU General Public License as published by
- * the Free Software Foundation; either version 3 of the License, or
- * (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful,
- * but WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
- * GNU General Public License for more details.
- *
- * You should have received a copy of the GNU General Public License
- * along with this program. If not, see <http://www.gnu.org/licenses/>.
- *
- * Note: This tool can be included into a list page with :
- * define('USE_CUSTOM_REPORT_AS_INCLUDE', 1);
- * include DOL_DOCUMENT_ROOT.'/core/customreports.php';
- */
- /**
- * \file htdocs/core/customreports.php
- * \ingroup core
- * \brief Page to make custom reports
- */
- if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
- require '../main.inc.php';
- // Get parameters
- $action = GETPOST('action', 'aZ09') ?GETPOST('action', 'aZ09') : 'view'; // The action 'add', 'create', 'edit', 'update', 'view', ...
- $massaction = GETPOST('massaction', 'alpha'); // The bulk action (combo box choice into lists)
- $mode = GETPOST('mode', 'alpha') ? GETPOST('mode', 'alpha') : 'graph';
- $objecttype = GETPOST('objecttype', 'aZ09');
- $tabfamily = GETPOST('tabfamily', 'aZ09');
- if (empty($objecttype)) {
- $objecttype = 'thirdparty';
- }
- $search_measures = GETPOST('search_measures', 'array');
- //$search_xaxis = GETPOST('search_xaxis', 'array');
- if (GETPOST('search_xaxis', 'alpha') && GETPOST('search_xaxis', 'alpha') != '-1') {
- $search_xaxis = array(GETPOST('search_xaxis', 'alpha'));
- } else {
- $search_xaxis = array();
- }
- //$search_groupby = GETPOST('search_groupby', 'array');
- if (GETPOST('search_groupby', 'alpha') && GETPOST('search_groupby', 'alpha') != '-1') {
- $search_groupby = array(GETPOST('search_groupby', 'alpha'));
- } else {
- $search_groupby = array();
- }
- $search_yaxis = GETPOST('search_yaxis', 'array');
- $search_graph = GETPOST('search_graph', 'restricthtml');
- // Load variable for pagination
- $limit = GETPOST('limit', 'int') ? GETPOST('limit', 'int') : $conf->liste_limit;
- $sortfield = GETPOST('sortfield', 'aZ09comma');
- $sortorder = GETPOST('sortorder', 'aZ09comma');
- $page = GETPOSTISSET('pageplusone') ? (GETPOST('pageplusone') - 1) : GETPOST("page", 'int');
- 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
- $offset = $limit * $page;
- $pageprev = $page - 1;
- $pagenext = $page + 1;
- $diroutputmassaction = $conf->user->dir_temp.'/'.$user->id.'/customreport';
- }
- require_once DOL_DOCUMENT_ROOT."/core/lib/admin.lib.php";
- require_once DOL_DOCUMENT_ROOT."/core/lib/company.lib.php";
- require_once DOL_DOCUMENT_ROOT."/core/class/dolgraph.class.php";
- require_once DOL_DOCUMENT_ROOT."/core/class/doleditor.class.php";
- require_once DOL_DOCUMENT_ROOT."/core/class/html.formother.class.php";
- // Load traductions files requiredby by page
- $langs->loadLangs(array("companies", "other", "exports", "sendings"));
- $extrafields = new ExtraFields($db);
- $hookmanager->initHooks(array('customreport')); // Note that conf->hooks_modules contains array
- $title = '';
- $picto = '';
- $head = array();
- $object = null;
- $ObjectClassName = '';
- // Objects available by default
- $arrayoftype = array(
- 'thirdparty' => array('label' => 'ThirdParties', 'ObjectClassName' => 'Societe', 'enabled' => isModEnabled('societe'), 'ClassPath' => "/societe/class/societe.class.php"),
- 'contact' => array('label' => 'Contacts', 'ObjectClassName' => 'Contact', 'enabled' => isModEnabled('societ'), 'ClassPath' => "/contact/class/contact.class.php"),
- 'proposal' => array('label' => 'Proposals', 'ObjectClassName' => 'Propal', 'enabled' => isModEnabled('propal'), 'ClassPath' => "/comm/propal/class/propal.class.php"),
- 'order' => array('label' => 'Orders', 'ObjectClassName' => 'Commande', 'enabled' => isModEnabled('commande'), 'ClassPath' => "/commande/class/commande.class.php"),
- 'invoice' => array('label' => 'Invoices', 'ObjectClassName' => 'Facture', 'enabled' => isModEnabled('facture'), 'ClassPath' => "/compta/facture/class/facture.class.php"),
- 'invoice_template'=>array('label' => 'PredefinedInvoices', 'ObjectClassName' => 'FactureRec', 'enabled' => isModEnabled('facture'), 'ClassPath' => "/compta/class/facturerec.class.php", 'langs'=>'bills'),
- 'contract' => array('label' => 'Contracts', 'ObjectClassName' => 'Contrat', 'enabled' => isModEnabled('contrat'), 'ClassPath' => "/contrat/class/contrat.class.php", 'langs'=>'contracts'),
- 'contractdet' => array('label' => 'ContractLines', 'ObjectClassName' => 'ContratLigne', 'enabled' => isModEnabled('contrat'), 'ClassPath' => "/contrat/class/contrat.class.php", 'langs'=>'contracts'),
- 'bom' => array('label' => 'BOM', 'ObjectClassName' => 'Bom', 'enabled' => isModEnabled('bom')),
- 'mo' => array('label' => 'MO', 'ObjectClassName' => 'Mo', 'enabled' => isModEnabled('mrp'), 'ClassPath' => "/mrp/class/mo.class.php"),
- 'ticket' => array('label' => 'Ticket', 'ObjectClassName' => 'Ticket', 'enabled' => isModEnabled('ticket')),
- 'member' => array('label' => 'Adherent', 'ObjectClassName' => 'Adherent', 'enabled' => isModEnabled('adherent'), 'ClassPath' => "/adherents/class/adherent.class.php", 'langs'=>'members'),
- 'cotisation' => array('label' => 'Subscriptions', 'ObjectClassName' => 'Subscription', 'enabled' => isModEnabled('adherent'), 'ClassPath' => "/adherents/class/subscription.class.php", 'langs'=>'members'),
- );
- // Complete $arrayoftype by external modules
- $parameters = array('objecttype'=>$objecttype, 'tabfamily'=>$tabfamily);
- $reshook = $hookmanager->executeHooks('loadDataForCustomReports', $parameters, $object, $action); // Note that $action and $object may have been modified by some hooks
- if ($reshook < 0) {
- setEventMessages($hookmanager->error, $hookmanager->errors, 'errors');
- } elseif (is_array($hookmanager->resArray)) {
- if (!empty($hookmanager->resArray['title'])) { // Add entries for tabs
- $title = $hookmanager->resArray['title'];
- }
- if (!empty($hookmanager->resArray['picto'])) { // Add entries for tabs
- $picto = $hookmanager->resArray['picto'];
- }
- if (!empty($hookmanager->resArray['head'])) { // Add entries for tabs
- $head = array_merge($head, $hookmanager->resArray['head']);
- }
- if (!empty($hookmanager->resArray['arrayoftype'])) { // Add entries from hook
- foreach ($hookmanager->resArray['arrayoftype'] as $key => $val) {
- $arrayoftype[$key] = $val;
- }
- }
- }
- if ($objecttype) {
- try {
- if (!empty($arrayoftype[$objecttype]['ClassPath'])) {
- dol_include_once($arrayoftype[$objecttype]['ClassPath']);
- } else {
- dol_include_once("/".$objecttype."/class/".$objecttype.".class.php");
- }
- $ObjectClassName = $arrayoftype[$objecttype]['ObjectClassName'];
- $object = new $ObjectClassName($db);
- } catch (Exception $e) {
- print 'Failed to load class for type '.$objecttype;
- }
- }
- // Security check
- $socid = 0;
- if ($user->socid > 0) { // Protection if external user
- //$socid = $user->socid;
- accessforbidden();
- }
- // Fetch optionals attributes and labels
- $extrafields->fetch_name_optionals_label($object->table_element);
- //$extrafields->fetch_name_optionals_label($object->table_element_line);
- $search_array_options = $extrafields->getOptionalsFromPost($object->table_element, '', 'search_');
- $search_component_params = array('');
- $search_component_params_hidden = GETPOST('search_component_params_hidden', 'alphanohtml');
- // For the case we enter a criteria manually, the search_component_params_input will be defined and must be used in priority
- if (GETPOST('search_component_params_input', 'alphanohtml')) {
- $search_component_params_hidden = GETPOST('search_component_params_input', 'alphanohtml');
- }
- $MAXUNIQUEVALFORGROUP = 20;
- $MAXMEASURESINBARGRAPH = 20;
- $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);
- $MM = substr($langs->trans("Month"), 0, 1).substr($langs->trans("Month"), 0, 1);
- $DD = substr($langs->trans("Day"), 0, 1).substr($langs->trans("Day"), 0, 1);
- $HH = substr($langs->trans("Hour"), 0, 1).substr($langs->trans("Hour"), 0, 1);
- $MI = substr($langs->trans("Minute"), 0, 1).substr($langs->trans("Minute"), 0, 1);
- $SS = substr($langs->trans("Second"), 0, 1).substr($langs->trans("Second"), 0, 1);
- $arrayofmesures = array();
- $arrayofxaxis = array();
- $arrayofgroupby = array();
- $arrayofyaxis = array();
- $arrayofvaluesforgroupby = array();
- $features = $object->element;
- if (!empty($object->element_for_permission)) {
- $features = $object->element_for_permission;
- }
- restrictedArea($user, $features, 0, '');
- $error = 0;
- /*
- * Actions
- */
- // None
- /*
- * View
- */
- $form = new Form($db);
- $formother = new FormOther($db);
- if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
- llxHeader('', $langs->transnoentitiesnoconv('CustomReports'), '');
- print dol_get_fiche_head($head, 'customreports', $title, -1, $picto);
- }
- $newarrayoftype = array();
- foreach ($arrayoftype as $key => $val) {
- if (dol_eval($val['enabled'], 1, 1, '1')) {
- $newarrayoftype[$key] = $arrayoftype[$key];
- }
- if (!empty($val['langs'])) {
- $langs->load($val['langs']);
- }
- }
- $count = 0;
- $arrayofmesures = fillArrayOfMeasures($object, 't', $langs->trans($newarrayoftype[$objecttype]['label']), $arrayofmesures, 0, $count);
- $arrayofmesures = dol_sort_array($arrayofmesures, 'position', 'asc', 0, 0, 1);
- $count = 0;
- $arrayofxaxis = fillArrayOfXAxis($object, 't', $langs->trans($newarrayoftype[$objecttype]['label']), $arrayofxaxis, 0, $count);
- $arrayofxaxis = dol_sort_array($arrayofxaxis, 'position', 'asc', 0, 0, 1);
- $count = 0;
- $arrayofgroupby = fillArrayOfGroupBy($object, 't', $langs->trans($newarrayoftype[$objecttype]['label']), $arrayofgroupby, 0, $count);
- $arrayofgroupby = dol_sort_array($arrayofgroupby, 'position', 'asc', 0, 0, 1);
- // Check parameters
- if ($action == 'viewgraph') {
- if (!count($search_measures)) {
- setEventMessages($langs->trans("AtLeastOneMeasureIsRequired"), null, 'warnings');
- } elseif ($mode == 'graph' && count($search_xaxis) > 1) {
- setEventMessages($langs->trans("OnlyOneFieldForXAxisIsPossible"), null, 'warnings');
- $search_xaxis = array(0 => $search_xaxis[0]);
- }
- if (count($search_groupby) >= 2) {
- setEventMessages($langs->trans("ErrorOnlyOneFieldForGroupByIsPossible"), null, 'warnings');
- $search_groupby = array(0 => $search_groupby[0]);
- }
- if (!count($search_xaxis)) {
- setEventMessages($langs->trans("AtLeastOneXAxisIsRequired"), null, 'warnings');
- } elseif ($mode == 'graph' && $search_graph == 'bars' && count($search_measures) > $MAXMEASURESINBARGRAPH) {
- $langs->load("errors");
- setEventMessages($langs->trans("GraphInBarsAreLimitedToNMeasures", $MAXMEASURESINBARGRAPH), null, 'warnings');
- $search_graph = 'lines';
- }
- }
- // Get all possible values of fields when a 'group by' is set, and save this into $arrayofvaluesforgroupby
- // $arrayofvaluesforgroupby will be used to forge lael of each grouped series
- if (is_array($search_groupby) && count($search_groupby)) {
- foreach ($search_groupby as $gkey => $gval) {
- $gvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $gval);
- if (preg_match('/\-year$/', $search_groupby[$gkey])) {
- $tmpval = preg_replace('/\-year$/', '', $search_groupby[$gkey]);
- $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y')";
- } elseif (preg_match('/\-month$/', $search_groupby[$gkey])) {
- $tmpval = preg_replace('/\-month$/', '', $search_groupby[$gkey]);
- $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y-%m')";
- } elseif (preg_match('/\-day$/', $search_groupby[$gkey])) {
- $tmpval = preg_replace('/\-day$/', '', $search_groupby[$gkey]);
- $fieldtocount .= 'DATE_FORMAT('.$tmpval.", '%Y-%m-%d')";
- } else {
- $fieldtocount = $search_groupby[$gkey];
- }
- $sql = "SELECT DISTINCT ".$fieldtocount." as val";
- if (strpos($fieldtocount, 'te.') === 0) {
- $sql .= " FROM ".MAIN_DB_PREFIX.$object->table_element."_extrafields as te";
- } else {
- $tabletouse = $object->table_element;
- $tablealiastouse = 't';
- if (!empty($arrayofgroupby[$gval])) {
- $tmpval = explode('.', $gval);
- $tabletouse = $arrayofgroupby[$gval]['table'];
- $tablealiastouse = $tmpval[0];
- }
- $sql .= " FROM ".MAIN_DB_PREFIX.$tabletouse." as ".$tablealiastouse;
- }
- // Add the where here
- /*
- $sqlfilters = GETPOST('search_component_params_hidden', 'alphanohtml');
- if ($sqlfilters) {
- $errormessage = '';
- if (dolCheckFilters($sqlfilters, $errormessage)) {
- $regexstring = '\(([^:\'\(\)]+:[^:\'\(\)]+:[^\(\)]+)\)';
- $sql .= " WHERE (".preg_replace_callback('/'.$regexstring.'/', 'dolForgeCriteriaCallback', $sqlfilters).")";
- } else {
- print $errormessage;
- }
- }*/
- $sql .= " LIMIT ".((int) ($MAXUNIQUEVALFORGROUP + 1));
- //print $sql;
- $resql = $db->query($sql);
- if (!$resql) {
- dol_print_error($db);
- }
- while ($obj = $db->fetch_object($resql)) {
- if (is_null($obj->val)) {
- $keytouse = '__NULL__';
- $valuetranslated = $langs->transnoentitiesnoconv("NotDefined");
- } elseif ($obj->val === '') {
- $keytouse = '';
- $valuetranslated = $langs->transnoentitiesnoconv("Empty");
- } else {
- $keytouse = (string) $obj->val;
- $valuetranslated = $obj->val;
- }
- $regs = array();
- if (!empty($object->fields[$gvalwithoutprefix]['arrayofkeyval'])) {
- $valuetranslated = $object->fields[$gvalwithoutprefix]['arrayofkeyval'][$obj->val];
- if (is_null($valuetranslated)) {
- $valuetranslated = $langs->transnoentitiesnoconv("UndefinedKey");
- }
- $valuetranslated = $langs->trans($valuetranslated);
- } elseif (preg_match('/integer:([^:]+):([^:]+)$/', $object->fields[$gvalwithoutprefix]['type'], $regs)) {
- $classname = $regs[1];
- $classpath = $regs[2];
- dol_include_once($classpath);
- if (class_exists($classname)) {
- $tmpobject = new $classname($db);
- $tmpobject->fetch($obj->val);
- foreach ($tmpobject->fields as $fieldkey => $field) {
- if ($field['showoncombobox']) {
- $valuetranslated = $tmpobject->$fieldkey;
- //if ($valuetranslated == '-') $valuetranslated = $langs->transnoentitiesnoconv("Unknown")
- break;
- }
- }
- //$valuetranslated = $tmpobject->ref.'eee';
- }
- }
- $arrayofvaluesforgroupby['g_'.$gkey][$keytouse] = $valuetranslated;
- }
- // Add also the possible NULL value if field is a parent field that is not a strict join
- $tmpfield = explode('.', $gval);
- if ($tmpfield[0] != 't' || (is_array($object->fields[$tmpfield[1]]) && empty($object->fields[$tmpfield[1]]['notnull']))) {
- 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");
- //var_dump($gval); var_dump($object->fields);
- $arrayofvaluesforgroupby['g_'.$gkey]['__NULL__'] = $langs->transnoentitiesnoconv("NotDefined");
- }
- asort($arrayofvaluesforgroupby['g_'.$gkey]);
- // Add a protection/error to refuse the request if number of differentr values for the group by is higher than $MAXUNIQUEVALFORGROUP
- if (count($arrayofvaluesforgroupby['g_'.$gkey]) > $MAXUNIQUEVALFORGROUP) {
- $langs->load("errors");
- if (strpos($fieldtocount, 'te.') === 0) {
- //if (!empty($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix])) {
- // $langs->load($extrafields->attributes[$object->table_element]['langfile'][$gvalwithoutprefix]);
- //}
- $keyforlabeloffield = $extrafields->attributes[$object->table_element]['label'][$gvalwithoutprefix];
- } else {
- $keyforlabeloffield = $object->fields[$gvalwithoutprefix]['label'];
- }
- //var_dump($gkey.' '.$gval.' '.$gvalwithoutprefix);
- $gvalwithoutprefix = preg_replace('/\-(year|month|day)/', '', $gvalwithoutprefix);
- $labeloffield = $langs->transnoentitiesnoconv($keyforlabeloffield);
- setEventMessages($langs->trans("ErrorTooManyDifferentValueForSelectedGroupBy", $MAXUNIQUEVALFORGROUP, $labeloffield), null, 'warnings');
- $search_groupby = array();
- }
- $db->free($resql);
- }
- }
- //var_dump($arrayofvaluesforgroupby);exit;
- $tmparray = dol_getdate(dol_now());
- $endyear = $tmparray['year'];
- $endmonth = $tmparray['mon'];
- $datelastday = dol_get_last_day($endyear, $endmonth, 1);
- $startyear = $endyear - 2;
- $param = '';
- print '<form method="post" action="'.$_SERVER['PHP_SELF'].'">';
- print '<input type="hidden" name="token" value="'.newToken().'">';
- print '<input type="hidden" name="action" value="viewgraph">';
- print '<input type="hidden" name="tabfamily" value="'.$tabfamily.'">';
- $viewmode = '';
- $viewmode .= '<div class="divadvancedsearchfield">';
- $arrayofgraphs = array('bars' => 'Bars', 'lines' => 'Lines'); // also 'pies'
- $viewmode .= '<div class="inline-block opacitymedium"><span class="fas fa-chart-area paddingright" title="'.$langs->trans("Graph").'"></span>'.$langs->trans("Graph").'</div> ';
- $viewmode .= $form->selectarray('search_graph', $arrayofgraphs, $search_graph, 0, 0, 0, 'minwidth100', 1);
- $viewmode .= '</div>';
- $num = 0;
- $massactionbutton = '';
- $nav = '';
- $newcardbutton = '';
- $limit = 0;
- 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);
- print '<div class="liste_titre liste_titre_bydiv centpercent">';
- // Select object
- print '<div class="divadvancedsearchfield center floatnone">';
- print '<div class="inline-block"><span class="opacitymedium">'.$langs->trans("StatisticsOn").'</span></div> ';
- print $form->selectarray('objecttype', $newarrayoftype, $objecttype, 0, 0, 0, '', 1, 0, 0, '', 'minwidth200', 1);
- if (empty($conf->use_javascript_ajax)) {
- print '<input type="submit" class="button buttongen button-save nomargintop" name="changeobjecttype" value="'.$langs->trans("Refresh").'">';
- } else {
- print '<!-- js code to reload page with good object type -->
- <script type="text/javascript">
- jQuery(document).ready(function() {
- jQuery("#objecttype").change(function() {
- console.log("Reload for "+jQuery("#objecttype").val());
- 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')) : '').'";
- });
- });
- </script>';
- }
- print '</div><div class="clearboth"></div>';
- // Add Filter (you can use param &show_search_component_params_hidden=1 for debug)
- print '<div class="divadvancedsearchfield quatrevingtpercent">';
- print $form->searchComponent(array($object->element => $object->fields), $search_component_params, array(), $search_component_params_hidden);
- print '</div>';
- // Add measures into array
- $count = 0;
- //var_dump($arrayofmesures);
- print '<div class="divadvancedsearchfield clearboth">';
- 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>';
- $simplearrayofmesures = array();
- foreach ($arrayofmesures as $key => $val) {
- $simplearrayofmesures[$key] = $arrayofmesures[$key]['label'];
- }
- print $form->multiselectarray('search_measures', $simplearrayofmesures, $search_measures, 0, 0, 'minwidth400', 1, 0, '', '', $langs->trans("Measures")); // Fill the array $arrayofmeasures with possible fields
- print '</div>';
- // XAxis
- $count = 0;
- print '<div class="divadvancedsearchfield">';
- 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>';
- //var_dump($arrayofxaxis);
- print $formother->selectXAxisField($object, $search_xaxis, $arrayofxaxis, $langs->trans("XAxis")); // Fill the array $arrayofxaxis with possible fields
- print '</div>';
- // Group by
- $count = 0;
- print '<div class="divadvancedsearchfield">';
- print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-horizontal paddingright pictofixedwidth" title="'.dol_escape_htmltag($langs->trans("GroupBy")).'"></span></div>';
- print $formother->selectGroupByField($object, $search_groupby, $arrayofgroupby, 'minwidth250 maxwidth300', $langs->trans("GroupBy")); // Fill the array $arrayofgroupby with possible fields
- print '</div>';
- if ($mode == 'grid') {
- // YAxis
- print '<div class="divadvancedsearchfield">';
- foreach ($object->fields as $key => $val) {
- if (empty($val['measure']) && (!isset($val['enabled']) || dol_eval($val['enabled'], 1, 1, '1'))) {
- if (in_array($key, array('id', 'rowid', 'entity', 'last_main_doc', 'extraparams'))) {
- continue;
- }
- if (preg_match('/^fk_/', $key)) {
- continue;
- }
- if (in_array($val['type'], array('html', 'text'))) {
- continue;
- }
- if (in_array($val['type'], array('timestamp', 'date', 'datetime'))) {
- $arrayofyaxis['t.'.$key.'-year'] = array(
- 'label' => $langs->trans($val['label']).' ('.$YYYY.')',
- 'position' => $val['position'],
- 'table' => $object->table_element
- );
- $arrayofyaxis['t.'.$key.'-month'] = array(
- 'label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.')',
- 'position' => $val['position'],
- 'table' => $object->table_element
- );
- $arrayofyaxis['t.'.$key.'-day'] = array(
- 'label' => $langs->trans($val['label']).' ('.$YYYY.'-'.$MM.'-'.$DD.')',
- 'position' => $val['position'],
- 'table' => $object->table_element
- );
- } else {
- $arrayofyaxis['t.'.$key] = array(
- 'label' => $val['label'],
- 'position' => (int) $val['position'],
- 'table' => $object->table_element
- );
- }
- }
- }
- // Add measure from extrafields
- if ($object->isextrafieldmanaged) {
- foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) {
- 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'))) {
- $arrayofyaxis['te.'.$key] = array(
- 'label' => $extrafields->attributes[$object->table_element]['label'][$key],
- 'position' => (int) $extrafields->attributes[$object->table_element]['pos'][$key],
- 'table' => $object->table_element
- );
- }
- }
- }
- $arrayofyaxis = dol_sort_array($arrayofyaxis, 'position');
- $arrayofyaxislabel = array();
- foreach ($arrayofyaxis as $key => $val) {
- $arrayofyaxislabel[$key] = $val['label'];
- }
- print '<div class="inline-block opacitymedium"><span class="fas fa-ruler-vertical paddingright" title="'.$langs->trans("YAxis").'"></span>'.$langs->trans("YAxis").'</div> ';
- print $form->multiselectarray('search_yaxis', $arrayofyaxislabel, $search_yaxis, 0, 0, 'minwidth100', 1);
- print '</div>';
- }
- if ($mode == 'graph') {
- //
- }
- print '<div class="divadvancedsearchfield">';
- print '<input type="submit" class="button buttongen button-save nomargintop" value="'.$langs->trans("Refresh").'">';
- print '</div>';
- print '</div>';
- print '</form>';
- // Generate the SQL request
- $sql = '';
- if (!empty($search_measures) && !empty($search_xaxis)) {
- $fieldid = 'rowid';
- $sql = "SELECT ";
- foreach ($search_xaxis as $key => $val) {
- if (preg_match('/\-year$/', $val)) {
- $tmpval = preg_replace('/\-year$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y') as x_".$key.', ';
- } elseif (preg_match('/\-month$/', $val)) {
- $tmpval = preg_replace('/\-month$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m') as x_".$key.', ';
- } elseif (preg_match('/\-day$/', $val)) {
- $tmpval = preg_replace('/\-day$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d') as x_".$key.', ';
- } else {
- $sql .= $val." as x_".$key.", ";
- }
- }
- foreach ($search_groupby as $key => $val) {
- if (preg_match('/\-year$/', $val)) {
- $tmpval = preg_replace('/\-year$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y') as g_".$key.', ';
- } elseif (preg_match('/\-month$/', $val)) {
- $tmpval = preg_replace('/\-month$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m') as g_".$key.', ';
- } elseif (preg_match('/\-day$/', $val)) {
- $tmpval = preg_replace('/\-day$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d') as g_".$key.', ';
- } else {
- $sql .= $val." as g_".$key.", ";
- }
- }
- foreach ($search_measures as $key => $val) {
- if ($val == 't.count') {
- $sql .= "COUNT(t.".$fieldid.") as y_".$key.', ';
- } elseif (preg_match('/\-sum$/', $val)) {
- $tmpval = preg_replace('/\-sum$/', '', $val);
- $sql .= "SUM(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
- } elseif (preg_match('/\-average$/', $val)) {
- $tmpval = preg_replace('/\-average$/', '', $val);
- $sql .= "AVG(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
- } elseif (preg_match('/\-min$/', $val)) {
- $tmpval = preg_replace('/\-min$/', '', $val);
- $sql .= "MIN(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
- } elseif (preg_match('/\-max$/', $val)) {
- $tmpval = preg_replace('/\-max$/', '', $val);
- $sql .= "MAX(".$db->ifsql($tmpval.' IS NULL', '0', $tmpval).") as y_".$key.", ";
- }
- }
- $sql = preg_replace('/,\s*$/', '', $sql);
- $sql .= " FROM ".MAIN_DB_PREFIX.$object->table_element." as t";
- // Add measure from extrafields
- if ($object->isextrafieldmanaged) {
- $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$object->table_element."_extrafields as te ON te.fk_object = t.".$fieldid;
- }
- // Add table for link for multientity
- if ($object->ismultientitymanaged) { // 0=No test on entity, 1=Test with field entity, 'field@table'=Test with link by field@table
- if ($object->ismultientitymanaged == 1) {
- // No table to add here
- } else {
- $tmparray = explode('@', $object->ismultientitymanaged);
- $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$tmparray[1]." as parenttableforentity ON t.".$tmparray[0]." = parenttableforentity.rowid";
- $sql .= " AND parenttableforentity.entity IN (".getEntity($tmparray[1]).")";
- }
- }
- $listoftablesalreadyadded = array($object->table_element => $object->table_element);
- // Add LEFT JOIN for all parent tables mentionned into the Xaxis
- //var_dump($arrayofxaxis); var_dump($search_xaxis);
- foreach ($search_xaxis as $key => $val) {
- if (!empty($arrayofxaxis[$val])) {
- $tmpval = explode('.', $val);
- //var_dump($arrayofxaxis[$val]['table']);
- if (! in_array($arrayofxaxis[$val]['table'], $listoftablesalreadyadded)) { // We do not add join for main table already added
- $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";
- $listoftablesalreadyadded[$arrayofxaxis[$val]['table']] = $arrayofxaxis[$val]['table'];
- }
- } else {
- dol_print_error($db, 'Found a key into search_xaxis not found into arrayofxaxis');
- }
- }
- // Add LEFT JOIN for all parent tables mentionned into the Group by
- //var_dump($arrayofgroupby); var_dump($search_groupby);
- foreach ($search_groupby as $key => $val) {
- if (!empty($arrayofgroupby[$val])) {
- $tmpval = explode('.', $val);
- //var_dump($arrayofxaxis[$val]['table']);
- if (! in_array($arrayofgroupby[$val]['table'], $listoftablesalreadyadded)) { // We do not add join for main table already added
- $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$arrayofgroupby[$val]['table']." as ".$tmpval[0]." ON t.".str_replace('t__', '', $tmpval[0])." = ".$tmpval[0].".rowid";
- $listoftablesalreadyadded[$arrayofgroupby[$val]['table']] = $arrayofgroupby[$val]['table'];
- }
- } else {
- dol_print_error($db, 'Found a key into search_groupby not found into arrayofgroupby');
- }
- }
- // Add LEFT JOIN for all parent tables mentionned into the Yaxis
- //var_dump($arrayofgroupby); var_dump($search_groupby);
- foreach ($search_measures as $key => $val) {
- if (!empty($arrayofmesures[$val])) {
- $tmpval = explode('.', $val);
- //var_dump($arrayofxaxis[$val]['table']);
- if (! in_array($arrayofmesures[$val]['table'], $listoftablesalreadyadded)) { // We do not add join for main table already added
- $sql .= " LEFT JOIN ".MAIN_DB_PREFIX.$arrayofmesures[$val]['table']." as ".$tmpval[0]." ON t.".str_replace('t__', '', $tmpval[0])." = ".$tmpval[0].".rowid";
- $listoftablesalreadyadded[$arrayofmesures[$val]['table']] = $arrayofmesures[$val]['table'];
- }
- } else {
- dol_print_error($db, 'Found a key into search_measures not found into arrayofmesures');
- }
- }
- $sql .= " WHERE 1 = 1";
- if ($object->ismultientitymanaged == 1) { // 0=No test on entity, 1=Test with field entity, 'field@table'=Test with link by field@table
- $sql .= " AND t.entity IN (".getEntity($object->element).")";
- }
- // Add the where here
- $sqlfilters = $search_component_params_hidden;
- if ($sqlfilters) {
- $errormessage = '';
- if (dolCheckFilters($sqlfilters, $errormessage)) {
- $regexstring = '\(([^:\'\(\)]+:[^:\'\(\)]+:[^\(\)]+)\)';
- $sql .= " AND (".preg_replace_callback('/'.$regexstring.'/', 'dolForgeCriteriaCallback', $sqlfilters).")";
- } else {
- print $errormessage;
- }
- }
- $sql .= " GROUP BY ";
- foreach ($search_xaxis as $key => $val) {
- if (preg_match('/\-year$/', $val)) {
- $tmpval = preg_replace('/\-year$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
- } elseif (preg_match('/\-month$/', $val)) {
- $tmpval = preg_replace('/\-month$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
- } elseif (preg_match('/\-day$/', $val)) {
- $tmpval = preg_replace('/\-day$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
- } else {
- $sql .= $val.", ";
- }
- }
- foreach ($search_groupby as $key => $val) {
- if (preg_match('/\-year$/', $val)) {
- $tmpval = preg_replace('/\-year$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
- } elseif (preg_match('/\-month$/', $val)) {
- $tmpval = preg_replace('/\-month$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
- } elseif (preg_match('/\-day$/', $val)) {
- $tmpval = preg_replace('/\-day$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
- } else {
- $sql .= $val.', ';
- }
- }
- $sql = preg_replace('/,\s*$/', '', $sql);
- $sql .= ' ORDER BY ';
- foreach ($search_xaxis as $key => $val) {
- if (preg_match('/\-year$/', $val)) {
- $tmpval = preg_replace('/\-year$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
- } elseif (preg_match('/\-month$/', $val)) {
- $tmpval = preg_replace('/\-month$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
- } elseif (preg_match('/\-day$/', $val)) {
- $tmpval = preg_replace('/\-day$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
- } else {
- $sql .= $val.', ';
- }
- }
- foreach ($search_groupby as $key => $val) {
- if (preg_match('/\-year$/', $val)) {
- $tmpval = preg_replace('/\-year$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y'), ";
- } elseif (preg_match('/\-month$/', $val)) {
- $tmpval = preg_replace('/\-month$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m'), ";
- } elseif (preg_match('/\-day$/', $val)) {
- $tmpval = preg_replace('/\-day$/', '', $val);
- $sql .= "DATE_FORMAT(".$tmpval.", '%Y-%m-%d'), ";
- } else {
- $sql .= $val.', ';
- }
- }
- $sql = preg_replace('/,\s*$/', '', $sql);
- }
- //print $sql;
- $legend = array();
- foreach ($search_measures as $key => $val) {
- $legend[] = $langs->trans($arrayofmesures[$val]['label']);
- }
- $useagroupby = (is_array($search_groupby) && count($search_groupby));
- //var_dump($useagroupby);
- //var_dump($arrayofvaluesforgroupby);
- // Execute the SQL request
- $totalnbofrecord = 0;
- $data = array();
- if ($sql) {
- $resql = $db->query($sql);
- if (!$resql) {
- dol_print_error($db);
- }
- $ifetch = 0;
- $xi = 0;
- $oldlabeltouse = '';
- while ($obj = $db->fetch_object($resql)) {
- $ifetch++;
- if ($useagroupby) {
- $xval = $search_xaxis[0];
- $fieldforxkey = 'x_0';
- $xlabel = $obj->$fieldforxkey;
- $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval);
- // Define $xlabel
- if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) {
- $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey];
- }
- $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->transnoentitiesnoconv("Empty") : $langs->transnoentitiesnoconv("NotDefined")));
- if ($oldlabeltouse && ($labeltouse != $oldlabeltouse)) {
- $xi++; // Increase $xi
- }
- //var_dump($labeltouse.' '.$oldlabeltouse.' '.$xi);
- $oldlabeltouse = $labeltouse;
- /* Example of value for $arrayofvaluesforgroupby
- * array (size=1)
- * 'g_0' =>
- * array (size=6)
- * 0 => string '0' (length=1)
- * '' => string 'Empty' (length=5)
- * '__NULL__' => string 'Not defined' (length=11)
- * 'done' => string 'done' (length=4)
- * 'processing' => string 'processing' (length=10)
- * 'undeployed' => string 'undeployed' (length=10)
- */
- foreach ($search_measures as $key => $val) {
- $gi = 0;
- foreach ($search_groupby as $gkey) {
- //var_dump('*** Fetch #'.$ifetch.' for labeltouse='.$labeltouse.' measure number '.$key.' and group g_'.$gi);
- //var_dump($arrayofvaluesforgroupby);
- foreach ($arrayofvaluesforgroupby['g_'.$gi] as $gvaluepossiblekey => $gvaluepossiblelabel) {
- $ykeysuffix = $gvaluepossiblelabel;
- $gvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $gval);
- $fieldfory = 'y_'.$key;
- $fieldforg = 'g_'.$gi;
- $fieldforybis = 'y_'.$key.'_'.$ykeysuffix;
- //var_dump('gvaluepossiblekey='.$gvaluepossiblekey.' gvaluepossiblelabel='.$gvaluepossiblelabel.' ykeysuffix='.$ykeysuffix.' gval='.$gval.' gvalwithoutsuffix='.$gvalwithoutprefix);
- //var_dump('fieldforg='.$fieldforg.' obj->$fieldforg='.$obj->$fieldforg.' fieldfory='.$fieldfory.' obj->$fieldfory='.$obj->$fieldfory.' fieldforybis='.$fieldforybis);
- if (!is_array($data[$xi])) {
- $data[$xi] = array();
- }
- if (!array_key_exists('label', $data[$xi])) {
- $data[$xi] = array();
- $data[$xi]['label'] = $labeltouse;
- }
- $objfieldforg = $obj->$fieldforg;
- if (is_null($objfieldforg)) {
- $objfieldforg = '__NULL__';
- }
- if ($gvaluepossiblekey == '0') { // $gvaluepossiblekey can have type int or string. So we create a special if, used when value is '0'
- //var_dump($objfieldforg.' == \'0\' -> '.($objfieldforg == '0'));
- if ($objfieldforg == '0') {
- // The record we fetch is for this group
- $data[$xi][$fieldforybis] = $obj->$fieldfory;
- } elseif (!isset($data[$xi][$fieldforybis])) {
- // The record we fetch is not for this group
- $data[$xi][$fieldforybis] = '0';
- }
- } else {
- //var_dump((string) $objfieldforg.' === '.(string) $gvaluepossiblekey.' -> '.((string) $objfieldforg === (string) $gvaluepossiblekey));
- if ((string) $objfieldforg === (string) $gvaluepossiblekey) {
- // The record we fetch is for this group
- $data[$xi][$fieldforybis] = $obj->$fieldfory;
- } elseif (!isset($data[$xi][$fieldforybis])) {
- // The record we fetch is not for this group
- $data[$xi][$fieldforybis] = '0';
- }
- }
- }
- //var_dump($data[$xi]);
- $gi++;
- }
- }
- } else { // No group by
- $xval = $search_xaxis[0];
- $fieldforxkey = 'x_0';
- $xlabel = $obj->$fieldforxkey;
- $xvalwithoutprefix = preg_replace('/^[a-z]+\./', '', $xval);
- // Define $xlabel
- if (!empty($object->fields[$xvalwithoutprefix]['arrayofkeyval'])) {
- $xlabel = $object->fields[$xvalwithoutprefix]['arrayofkeyval'][$obj->$fieldforxkey];
- }
- $labeltouse = (($xlabel || $xlabel == '0') ? dol_trunc($xlabel, 20, 'middle') : ($xlabel === '' ? $langs->trans("Empty") : $langs->trans("NotDefined")));
- $xarrayforallseries = array('label' => $labeltouse);
- foreach ($search_measures as $key => $val) {
- $fieldfory = 'y_'.$key;
- $xarrayforallseries[$fieldfory] = $obj->$fieldfory;
- }
- $data[$xi] = $xarrayforallseries;
- $xi++;
- }
- }
- $totalnbofrecord = count($data);
- }
- //var_dump($data);
- print '<div class="customreportsoutput'.($totalnbofrecord ? '' : ' customreportsoutputnotdata').'">';
- if ($mode == 'grid') {
- // TODO
- }
- if ($mode == 'graph') {
- $WIDTH = '80%';
- $HEIGHT = 200;
- // Show graph
- $px1 = new DolGraph();
- $mesg = $px1->isGraphKo();
- if (!$mesg) {
- //var_dump($legend);
- //var_dump($data);
- $px1->SetData($data);
- unset($data);
- $arrayoftypes = array();
- foreach ($search_measures as $key => $val) {
- $arrayoftypes[] = $search_graph;
- }
- $px1->SetLegend($legend);
- $px1->SetMinValue($px1->GetFloorMinValue());
- $px1->SetMaxValue($px1->GetCeilMaxValue());
- $px1->SetWidth($WIDTH);
- $px1->SetHeight($HEIGHT);
- $px1->SetYLabel($langs->trans("Y"));
- $px1->SetShading(3);
- $px1->SetHorizTickIncrement(1);
- $px1->SetCssPrefix("cssboxes");
- $px1->SetType($arrayoftypes);
- $px1->mode = 'depth';
- $px1->SetTitle('');
- $dir = $conf->user->dir_temp;
- dol_mkdir($dir);
- $filenamenb = $dir.'/customreport_'.$object->element.'.png';
- $fileurlnb = DOL_URL_ROOT.'/viewimage.php?modulepart=user&file=customreport_'.$object->element.'.png';
- $px1->draw($filenamenb, $fileurlnb);
- $texttoshow = $langs->trans("NoRecordFound");
- if (!GETPOSTISSET('search_measures') || !GETPOSTISSET('search_xaxis')) {
- $texttoshow = $langs->trans("SelectYourGraphOptionsFirst");
- }
- print $px1->show($totalnbofrecord ? 0 : $texttoshow);
- }
- }
- if ($sql) {
- // Show admin info
- print '<br>'.info_admin($langs->trans("SQLUsedForExport").':<br> '.$sql, 0, 0, 1, '', 'TechnicalInformation');
- }
- print '<div>';
- if (!defined('USE_CUSTOM_REPORT_AS_INCLUDE')) {
- print dol_get_fiche_end();
- }
- // End of page
- llxFooter();
- $db->close();
- /**
- * Fill arrayofmesures for an object
- *
- * @param mixed $object Any object
- * @param string $tablealias Alias of table
- * @param string $labelofobject Label of object
- * @param array $arrayofmesures Array of mesures already filled
- * @param int $level Level
- * @param int $count Count
- * @return array Array of mesures
- */
- function fillArrayOfMeasures($object, $tablealias, $labelofobject, &$arrayofmesures, $level = 0, &$count = 0)
- {
- global $langs, $extrafields, $db;
- if ($level > 10) { // Protection against infinite loop
- return $arrayofmesures;
- }
- if ($level == 0) {
- // Add the count of record only for the main/first level object. Parents are necessarly unique for each record.
- $arrayofmesures[$tablealias.'.count'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': Count',
- 'position' => 0,
- 'table' => $object->table_element
- );
- }
- // Add main fields of object
- foreach ($object->fields as $key => $val) {
- if (!empty($val['isameasure']) && (!isset($val['enabled']) || dol_eval($val['enabled'], 1, 1, '1'))) {
- $position = (empty($val['position']) ? 0 : intVal($val['position']));
- $arrayofmesures[$tablealias.'.'.$key.'-sum'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Sum").')</span>',
- 'position' => ($position + ($count * 100000)).'.1',
- 'table' => $object->table_element
- );
- $arrayofmesures[$tablealias.'.'.$key.'-average'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Average").')</span>',
- 'position' => ($position + ($count * 100000)).'.2',
- 'table' => $object->table_element
- );
- $arrayofmesures[$tablealias.'.'.$key.'-min'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Minimum").')</span>',
- 'position' => ($position + ($count * 100000)).'.3',
- 'table' => $object->table_element
- );
- $arrayofmesures[$tablealias.'.'.$key.'-max'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$langs->trans("Maximum").')</span>',
- 'position' => ($position + ($count * 100000)).'.4',
- 'table' => $object->table_element
- );
- }
- }
- // Add extrafields to Measures
- if (!empty($object->isextrafieldmanaged)) {
- foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) {
- 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'))) {
- $position = (!empty($val['position']) ? $val['position'] : 0);
- $arrayofmesures[$tablealias.'e.'.$key.'-sum'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Sum").')</span>',
- 'position' => ($position+($count * 100000)).'.1',
- 'table' => $object->table_element
- );
- $arrayofmesures[$tablealias.'e.'.$key.'-average'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Average").')</span>',
- 'position' => ($position+($count * 100000)).'.2',
- 'table' => $object->table_element
- );
- $arrayofmesures[$tablealias.'e.'.$key.'-min'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Minimum").')</span>',
- 'position' => ($position+($count * 100000)).'.3',
- 'table' => $object->table_element
- );
- $arrayofmesures[$tablealias.'e.'.$key.'-max'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($extrafields->attributes[$object->table_element]['label'][$key]).' <span class="opacitymedium">('.$langs->trans("Maximum").')</span>',
- 'position' => ($position+($count * 100000)).'.4',
- 'table' => $object->table_element
- );
- }
- }
- }
- // Add fields for parent objects
- foreach ($object->fields as $key => $val) {
- if (preg_match('/^[^:]+:[^:]+:/', $val['type'])) {
- $tmptype = explode(':', $val['type'], 4);
- if ($tmptype[0] == 'integer' && $tmptype[1] && $tmptype[2]) {
- $newobject = $tmptype[1];
- dol_include_once($tmptype[2]);
- if (class_exists($newobject)) {
- $tmpobject = new $newobject($db);
- //var_dump($key); var_dump($tmpobject->element); var_dump($val['label']); var_dump($tmptype); var_dump('t-'.$key);
- $count++;
- $arrayofmesures = fillArrayOfMeasures($tmpobject, $tablealias.'__'.$key, $langs->trans($val['label']), $arrayofmesures, $level + 1, $count);
- } else {
- print 'For property '.$object->element.'->'.$key.', type="'.$val['type'].'": Failed to find class '.$newobject." in file ".$tmptype[2]."<br>\n";
- }
- }
- }
- }
- return $arrayofmesures;
- }
- /**
- * Fill arrayofmesures for an object
- *
- * @param mixed $object Any object
- * @param string $tablealias Alias of table ('t' for example)
- * @param string $labelofobject Label of object
- * @param array $arrayofxaxis Array of xaxis already filled
- * @param int $level Level
- * @param int $count Count
- * @return array Array of xaxis
- */
- function fillArrayOfXAxis($object, $tablealias, $labelofobject, &$arrayofxaxis, $level = 0, &$count = 0)
- {
- global $langs, $extrafields, $db;
- if ($level > 10) { // Protection against infinite loop
- return $arrayofxaxis;
- }
- if ($level >= 2) {
- return $arrayofxaxis;
- }
- $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);
- $MM = substr($langs->trans("Month"), 0, 1).substr($langs->trans("Month"), 0, 1);
- $DD = substr($langs->trans("Day"), 0, 1).substr($langs->trans("Day"), 0, 1);
- $HH = substr($langs->trans("Hour"), 0, 1).substr($langs->trans("Hour"), 0, 1);
- $MI = substr($langs->trans("Minute"), 0, 1).substr($langs->trans("Minute"), 0, 1);
- $SS = substr($langs->trans("Second"), 0, 1).substr($langs->trans("Second"), 0, 1);
- // Add main fields of object
- foreach ($object->fields as $key => $val) {
- if (empty($val['measure'])) {
- if (in_array($key, array(
- 'id', 'ref_int', 'ref_ext', 'rowid', 'entity', 'last_main_doc', 'logo', 'logo_squarred', 'extraparams',
- 'parent', 'photo', 'socialnetworks', 'webservices_url', 'webservices_key'))) {
- continue;
- }
- if (isset($val['enabled']) && !dol_eval($val['enabled'], 1, 1, '1')) {
- continue;
- }
- if (isset($val['visible']) && !dol_eval($val['visible'], 1, 1, '1')) {
- continue;
- }
- if (preg_match('/^fk_/', $key) && !preg_match('/^fk_statu/', $key)) {
- continue;
- }
- if (preg_match('/^pass/', $key)) {
- continue;
- }
- if (in_array($val['type'], array('html', 'text'))) {
- continue;
- }
- if (in_array($val['type'], array('timestamp', 'date', 'datetime'))) {
- $position = (empty($val['position']) ? 0 : intVal($val['position']));
- $arrayofxaxis[$tablealias.'.'.$key.'-year'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$YYYY.')</span>',
- 'position' => ($position + ($count * 100000)).'.1',
- 'table' => $object->table_element
- );
- $arrayofxaxis[$tablealias.'.'.$key.'-month'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$YYYY.'-'.$MM.')</span>',
- 'position' => ($position + ($count * 100000)).'.2',
- 'table' => $object->table_element
- );
- $arrayofxaxis[$tablealias.'.'.$key.'-day'] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$YYYY.'-'.$MM.'-'.$DD.')</span>',
- 'position' => ($position + ($count * 100000)).'.3',
- 'table' => $object->table_element
- );
- } else {
- $position = (empty($val['position']) ? 0 : intVal($val['position']));
- $arrayofxaxis[$tablealias.'.'.$key] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']),
- 'position' => ($position + ($count * 100000)),
- 'table' => $object->table_element
- );
- }
- }
- }
- // Add extrafields to X-Axis
- if (!empty($object->isextrafieldmanaged)) {
- foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) {
- if ($extrafields->attributes[$object->table_element]['type'][$key] == 'separate') {
- continue;
- }
- if (!empty($extrafields->attributes[$object->table_element]['totalizable'][$key])) {
- continue;
- }
- $arrayofxaxis[$tablealias.'e.'.$key] = array(
- 'label' => img_picto('', $object->picto, 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($extrafields->attributes[$object->table_element]['label'][$key]),
- 'position' => 1000 + (int) $extrafields->attributes[$object->table_element]['pos'][$key] + ($count * 100000),
- 'table' => $object->table_element
- );
- }
- }
- // Add fields for parent objects
- foreach ($object->fields as $key => $val) {
- if (preg_match('/^[^:]+:[^:]+:/', $val['type'])) {
- $tmptype = explode(':', $val['type'], 4);
- if ($tmptype[0] == 'integer' && $tmptype[1] && $tmptype[2]) {
- $newobject = $tmptype[1];
- dol_include_once($tmptype[2]);
- if (class_exists($newobject)) {
- $tmpobject = new $newobject($db);
- //var_dump($key); var_dump($tmpobject->element); var_dump($val['label']); var_dump($tmptype); var_dump('t-'.$key);
- $count++;
- $arrayofxaxis = fillArrayOfXAxis($tmpobject, $tablealias.'__'.$key, $langs->trans($val['label']), $arrayofxaxis, $level + 1, $count);
- } else {
- print 'For property '.$object->element.'->'.$key.', type="'.$val['type'].'": Failed to find class '.$newobject." in file ".$tmptype[2]."<br>\n";
- }
- }
- }
- }
- return $arrayofxaxis;
- }
- /**
- * Fill arrayofgrupby for an object
- *
- * @param mixed $object Any object
- * @param string $tablealias Alias of table
- * @param string $labelofobject Label of object
- * @param array $arrayofgroupby Array of groupby already filled
- * @param int $level Level
- * @param int $count Count
- * @return array Array of groupby
- */
- function fillArrayOfGroupBy($object, $tablealias, $labelofobject, &$arrayofgroupby, $level = 0, &$count = 0)
- {
- global $langs, $extrafields, $db;
- if ($level > 10) { // Protection against infinite loop
- return $arrayofgroupby;
- }
- if ($level >= 2) {
- return $arrayofgroupby;
- }
- $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);
- $MM = substr($langs->trans("Month"), 0, 1).substr($langs->trans("Month"), 0, 1);
- $DD = substr($langs->trans("Day"), 0, 1).substr($langs->trans("Day"), 0, 1);
- $HH = substr($langs->trans("Hour"), 0, 1).substr($langs->trans("Hour"), 0, 1);
- $MI = substr($langs->trans("Minute"), 0, 1).substr($langs->trans("Minute"), 0, 1);
- $SS = substr($langs->trans("Second"), 0, 1).substr($langs->trans("Second"), 0, 1);
- // Add main fields of object
- foreach ($object->fields as $key => $val) {
- if (empty($val['isameasure'])) {
- if (in_array($key, array(
- 'id', 'ref_int', 'ref_ext', 'rowid', 'entity', 'last_main_doc', 'logo', 'logo_squarred', 'extraparams',
- 'parent', 'photo', 'socialnetworks', 'webservices_url', 'webservices_key'))) {
- continue;
- }
- if (isset($val['enabled']) && !dol_eval($val['enabled'], 1, 1, '1')) {
- continue;
- }
- if (isset($val['visible']) && !dol_eval($val['visible'], 1, 1, '1')) {
- continue;
- }
- if (preg_match('/^fk_/', $key) && !preg_match('/^fk_statu/', $key)) {
- continue;
- }
- if (preg_match('/^pass/', $key)) {
- continue;
- }
- if (in_array($val['type'], array('html', 'text'))) {
- continue;
- }
- if (in_array($val['type'], array('timestamp', 'date', 'datetime'))) {
- $position = (empty($val['position']) ? 0 : intVal($val['position']));
- $arrayofgroupby[$tablealias.'.'.$key.'-year'] = array(
- 'label' => img_picto('', $object->picto,
- 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$YYYY.')</span>', 'position' => ($position + ($count * 100000)).'.1',
- 'table' => $object->table_element
- );
- $arrayofgroupby[$tablealias.'.'.$key.'-month'] = array(
- 'label' => img_picto('', $object->picto,
- 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$YYYY.'-'.$MM.')</span>', 'position' => ($position + ($count * 100000)).'.2',
- 'table' => $object->table_element
- );
- $arrayofgroupby[$tablealias.'.'.$key.'-day'] = array(
- 'label' => img_picto('', $object->picto,
- 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']).' <span class="opacitymedium">('.$YYYY.'-'.$MM.'-'.$DD.')</span>', 'position' => ($position + ($count * 100000)).'.3',
- 'table' => $object->table_element
- );
- } else {
- $position = (empty($val['position']) ? 0 : intVal($val['position']));
- $arrayofgroupby[$tablealias.'.'.$key] = array(
- 'label' => img_picto('', $object->picto,
- 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($val['label']), 'position' => ($position + ($count * 100000)),
- 'table' => $object->table_element
- );
- }
- }
- }
- // Add extrafields to Group by
- if (! empty($object->isextrafieldmanaged)) {
- foreach ($extrafields->attributes[$object->table_element]['label'] as $key => $val) {
- if ($extrafields->attributes[$object->table_element]['type'][$key] == 'separate') {
- continue;
- }
- if (!empty($extrafields->attributes[$object->table_element]['totalizable'][$key])) {
- continue;
- }
- $arrayofgroupby[$tablealias.'e.'.$key] = array(
- 'label' => img_picto('', $object->picto,
- 'class="pictofixedwidth"').' '.$labelofobject.': '.$langs->trans($extrafields->attributes[$object->table_element]['label'][$key]), 'position' => 1000 + (int) $extrafields->attributes[$object->table_element]['pos'][$key] + ($count * 100000),
- 'table' => $object->table_element
- );
- }
- }
- // Add fields for parent objects
- foreach ($object->fields as $key => $val) {
- if (preg_match('/^[^:]+:[^:]+:/', $val['type'])) {
- $tmptype = explode(':', $val['type'], 4);
- if ($tmptype[0] == 'integer' && $tmptype[1] && $tmptype[2]) {
- $newobject = $tmptype[1];
- dol_include_once($tmptype[2]);
- if (class_exists($newobject)) {
- $tmpobject = new $newobject($db);
- //var_dump($key); var_dump($tmpobject->element); var_dump($val['label']); var_dump($tmptype); var_dump('t-'.$key);
- $count++;
- $arrayofgroupby = fillArrayOfGroupBy($tmpobject, $tablealias.'__'.$key, $langs->trans($val['label']), $arrayofgroupby, $level + 1, $count);
- } else {
- print 'For property '.$object->element.'->'.$key.', type="'.$val['type'].'": Failed to find class '.$newobject." in file ".$tmptype[2]."<br>\n";
- }
- }
- }
- }
- return $arrayofgroupby;
- }
|