function fiche(&$query) { global $langs, $conf, $user; llxHeader('', 'Query', '', '', 0, 0, array('/query/js/query.js'), array('/query/css/query.css')); dol_fiche_head($query->title); if ($query->expert == 1) { if (!empty($query->sql_fields)) { $TField = explode_brackets($query->sql_fields); $query->TField = $TField; } } ?> <script type="text/javascript"> var MODQUERY_INTERFACE = "<?php echo dol_buildpath('/query/script/interface.php', 1); ?> "; var MODQUERY_QUERYID = <?php echo $query->getId(); ?> ; var MODQUERY_EXPERT = <?php echo (int) $query->expert; ?> ; var MODQUERY_PREFIX = "<?php echo MAIN_DB_PREFIX; ?> "; var select_equal = '<select sql-act="operator"> ' + '<option value=""> </option>' + '<option value="LIKE">LIKE</option>' /*+ '<option value="=">=</option>'*/ + '<option value="!=">!=</option>' + '<option value="<"><</option>' + '<option value="<="><=</option>' + '<option value=">">></option>' + '<option value=">=">>=</option>' + '<option value="IN">IN</option>' + '</select>'; var select_mode = '<select sql-act="mode"> ' + '<option value="value">valeur</option>' + '<option value="var">variable</option>' + '<option value="function">fonction</option>' + '</select> <input type="text" value="" sql-act="value" />'; var select_null = '<select sql-act="null"> ' + '<option value=""></option>' + '<option value="1"><?php echo $langs->transnoentities('YesAllowTestOnNull'); ?> </option>' + '</select>'; var select_order = '<select sql-act="order"> ' + '<option value=""> </option>' + '<option value="ASC">Ascendant</option>' + '<option value="DESC">Descendant</option>' + '</select>'; var select_filter = '<select sql-act="filter"> ' + '<option value="">Libre</option>' + '<option value="calendar">Date</option>' + '<option value="calendars">Dates</option>' + '</select>'; var select_hide = '<select sql-act="hide"> ' + '<option value=""> </option>' + '<option value="1"><?php echo $langs->trans('Hidden'); ?> </option>' + '</select>'; var select_group = '<select sql-act="group"> ' + '<option value=""> </option>' + '<option value="1">Groupé</option>' + '</select>'; var select_total = '<select sql-act="total"> ' + '<option value=""> </option>' + '<option value="sum"><?php echo $langs->trans('Total'); ?> </option>' + '<option value="groupsum"><?php echo $langs->trans('TotalGroup'); ?> </option>' + '<option value="average"><?php echo $langs->trans('Average'); ?> </option>' + '<option value="count"><?php echo $langs->trans('CountOf'); ?> </option>' + '</select>'; var select_total_group_field = '<select sql-act="field-total-group">' <?php foreach ($query->TField as $field) { echo ' + \'<option value="' . _getFieldName($field) . '">' . _getFieldName($field) . '</option>\' '; } ?> +'</select>'; var select_type = '<select sql-act="type"> ' + '<option value=""> </option>' + '<option value="number">Nombre</option>' + '<option value="integer">Entier</option>' + '<option value="datetime">Date/Heure</option>' + '<option value="date">Date</option>' + '<option value="hour">Heure</option>' + '</select>'; var select_function = '<input type="text" size="10" sql-act="function" value="" placeholder="<?php echo $langs->trans('FunctionToApply'); ?> " /><select sql-act="function-select"> ' + '<option value=""> </option>' + '<option value="SUM(@field@)"><?php echo $langs->trans('Sum'); ?> </option>' + '<option value="ROUND(@field@,2)"><?php echo $langs->trans('Round2dec'); ?> </option>' + '<option value="COUNT(@field@)"><?php echo $langs->trans('CountOf'); ?> </option>' + '<option value="MIN(@field@)"><?php echo $langs->trans('Minimum'); ?> </option>' + '<option value="MAX(@field@)"><?php echo $langs->trans('Maximum'); ?> </option>' + '<option value="MONTH(@field@)"><?php echo $langs->trans('Month'); ?> </option>' + '<option value="YEAR"><?php echo $langs->trans('Year'); ?> </option>' + '<option value="DATE_FORMAT(@field@, \'%m/%Y\')"><?php echo $langs->trans('YearMonth'); ?> </option>' //+ '<option value="FROM_UNIXTIME(@field@,\'%H:%i\')">Timestamp</option>' + '<option value="SEC_TO_TIME(@field@)"><?php echo $langs->trans('Timestamp'); ?> </option>' //+ '<option value="(@field@ / 3600)">/ 3600</option>' + '</select>'; var select_class = '<input type="text" size="10" sql-act="class" value="" placeholder="<?php echo $langs->trans('Classname'); ?> " /><select sql-act="class-select"> ' + '<option value=""> </option>' <?php foreach ($query->TClassName as $class => $label) { echo ' +\'<option value="' . $class . '">' . $label . '</option>\''; } ?> + '</select>'; var select_method = '<input type="text" size="10" sql-act="class-method" value="" placeholder="<?php echo $langs->trans('Method'); ?> " /><select sql-act="class-method-select"> ' + '<option value=""> </option>' <?php if (!empty($query->TMethodName)) { foreach ($query->TMethodName as $method => $label) { echo ' +\'<option value="' . $method . '">' . $label . '</option>\''; } } ?> + '</select>'; function _init_query() { <?php if ($query->getId() > 0) { if ($query->expert == 2) { echo 'showQueryPreview(' . $query->getId() . ');'; init_js($query); } else { if ($query->expert == 1) { echo 'showQueryPreview(' . $query->getId() . ');'; if (!empty($query->TField)) { foreach ($query->TField as $field) { list($f, $t) = _getFieldAndTableName($field); if (!empty($t)) { $field = $t . '.' . $f; } else { $field = $f; } echo ' refresh_field_param("' . $field . '","' . $t . '"); '; } } init_js($query); } else { foreach ($query->TTable as $table) { echo 'addTable("' . $table . '"); '; } if (empty($query->TField) && !empty($query->sql_fields)) { $query->TField = explode(',', $query->sql_fields); } //$TField = if (!empty($query->TField)) { foreach ($query->TField as $field) { echo ' checkField("' . $field . '"); '; } echo 'showQueryPreview(' . $query->getId() . ');'; } init_js($query); if (!empty($query->TJoin)) { foreach ($query->TJoin as $t => $join) { ?> $("td[rel=from] select[jointure='<?php echo $t; ?> ']").val("<?php echo $join[0]; ?> "); $("td[rel=to] select[jointure-to='<?php echo $t; ?> ']").val("<?php echo $join[1]; ?> "); TJoin['<?php echo $t; ?> '] = ["<?php echo $join[0]; ?> ", "<?php echo $join[1]; ?> "]; <?php } } ?> refresh_sql(); <?php } } } ?> } </script> <form name="formQuery" id="formQuery"> <input type="hidden" name="id" value="<?php echo $query->getId(); ?> " /> <div> <?php if ($query->getId() > 0 && !empty($user->rights->query->all->expert)) { ?> <div style="float:right;z-index:999; position:relative; top:40px;"><?php if ($query->expert == 0) { ?> <a class="butAction" href="?action=set-expert&id=<?php echo $query->getId(); ?> "><?php echo $langs->trans('setExpertMode'); ?> </a><?php } else { if ($query->expert == 2) { ?> <a class="butAction" href="?action=set-expert&id=<?php echo $query->getId(); ?> "><?php echo $langs->trans('setExpertMode'); ?> </a><?php ?> <br /><br /><a class="butAction" href="?action=unset-expert&id=<?php echo $query->getId(); ?> "><?php echo $langs->trans('unsetExpertMode'); ?> </a><?php } else { ?> <a class="butAction" href="?action=set-free&id=<?php echo $query->getId(); ?> "><?php echo $langs->trans('setExpertFreeMode'); ?> </a><?php ?> <br /><br /><a class="butAction" href="?action=unset-expert&id=<?php echo $query->getId(); ?> "><?php echo $langs->trans('unsetExpertMode'); ?> </a><?php } } ?> <br /><br /><a class="butAction" href="?action=clone&id=<?php echo $query->getId(); ?> "><?php echo $langs->trans('cloneQuery'); ?> </a><?php ?> <br /><br /><a class="butAction" href="?action=export&id=<?php echo $query->getId(); ?> "><?php echo $langs->trans('ExportQuery'); ?> </a><?php ?> </div><?php } ?> <div id="query_header" style="padding: 0 20px 20px 0; background-color: #fff;z-index:1;"> <?php echo $langs->trans('Title'); ?> : <input type="text" name="title" size="80" value="<?php echo $query->title; ?> " /> <?php $form = new TFormCore(); if (!empty($user->rights->query->bdd->use_other_db)) { dol_include_once('/query/class/bddconnector.class.php'); $PDOdb = new TPDOdb(); $formCoreBDD = new TFormCore(); if ($query->getId() > 0) { $formCoreBDD->Set_typeaff('view'); } echo $formCoreBDD->combo(' - ' . $langs->trans('BDD'), 'fk_bdd', TBDDConnector::getCombo($PDOdb, true), $query->fk_bdd); } echo $form->combo('- ' . $langs->trans('Type') . ' : ', 'type', $query->TGraphiqueType, $query->type); echo '- ' . $langs->trans('XAxis') . ' : <select name="xaxis" initValue="' . $query->xaxis . '"></select>'; ?> <input class="button" type="button" id="save_query" value="<?php echo $langs->trans('SaveQuery'); ?> " /> </div> <?php if ($query->getId() > 0 && !$query->expert) { ?> <div> <?php echo $langs->trans('AddOneOfThisTables'); ?> : <select id="tables"></select> <input class="button" type="button" id="add_this_table" value="<?php echo $langs->trans('AddThisTable'); ?> " /> </div> <div id="selected_tables"> </div> <?php } ?> </div> <?php if ($query->getId() > 0 && $query->expert != 2) { ?> <div class="selected_fields"> <div class="border" id="fields"><div class="liste_titre"><?php echo $langs->trans('FieldsOrder'); ?> </div></div> </div> <?php } if ($query->getId() > 0) { ?> <div id="results" style="display:<?php echo $query->expert > 0 ? 'block' : 'none'; ?> ;"> <div> <?php echo $langs->trans('Fields'); ?> <br /> <textarea id="sql_query_fields" name="sql_fields" <?php echo $query->expert == 2 ? ' style="width:700px;height:100px;" ' : ''; ?> ><?php echo htmlentities($query->sql_fields, 0, ini_get("default_charset")); ?> </textarea> </div> <div> <?php echo $langs->trans('From'); ?> <br /> <textarea id="sql_query_from" name="sql_from" <?php echo $query->expert == 2 ? 'style="width:700px;height:100px;"' : ''; ?> ><?php echo htmlentities($query->sql_from, 0, ini_get("default_charset")); ?> </textarea> </div> <div> <?php echo $langs->trans('Where'); ?> <br /> <textarea id="sql_query_where" name="sql_where" <?php echo $query->expert == 2 ? 'style="width:700px;height:200px;"' : ''; ?> ><?php echo htmlentities($query->sql_where, 0, ini_get("default_charset")); ?> </textarea> <?php if ($query->expert > 0) { echo $langs->trans('AfterWhere'); ?> <br /><textarea id="sql_query_afterwhere" name="sql_afterwhere" <?php echo $query->expert == 2 ? 'style="width:700px;height:100px;"' : ''; ?> ><?php echo htmlentities($query->sql_afterwhere, 0, ini_get("default_charset")); ?> </textarea><?php } else { ?> <input type="hidden" id="sql_query_afterwhere" name="sql_afterwhere" value="" /><?php } ?> </div> </div> <div style="clear:both; border-top:1px solid #000;"></div> <?php if ($query->getId() > 0 && $query->expert != 2) { ?> <div class="selected_fields_view"> <div class="border" id="fieldsview"><div class="liste_titre"><?php echo $langs->trans('FieldsView'); ?> </div></div> </div> <?php } ?> <div id="previewRequete" style="display: none;"> <iframe src="#" width="100%" bgcolor="#fff" frameborder="0" onload="this.height = this.contentWindow.document.body.scrollHeight + 'px'"></iframe> </div> <?php } ?> </form> <?php dol_fiche_end(); llxFooter(); }
private function extractAliasFromSQL() { dol_include_once('/query/lib/query.lib.php'); $this->TAlias = array(); $TField = explode_brackets($this->sql_fields); foreach ($TField as $field) { $pos = strripos($field, ' as '); if ($pos !== false) { $alias = trim(substr($field, $pos + 4)); $field = trim(substr($field, 0, $pos)); list($field, $table) = _getFieldAndTableName($field); $this->TAlias[$alias] = array($field, $table); } } //$this->TField = $TField; }
function _getFieldName($field) { list($f, $t) = _getFieldAndTableName($field); return $f; }