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;
}
