<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Entier Studio</title>
<script src="../scripts/common.{USERBROWSER}.js" type="text/javascript" language="javascript"></script>
<script src="scripts/OnlineDataQueryManager-tables.js" language="javascript"></script>
<link rel="stylesheet" type="text/css" href="../styles/pageview.{USERBROWSER}.css">
<link rel="stylesheet" type="text/css" href="../styles/menuview.{USERBROWSER}.css">
<link rel="stylesheet" type="text/css" href="../styles/formview.{USERBROWSER}.css">
<link rel="stylesheet" type="text/css" href="../styles/gridview.{USERBROWSER}.css">
</head>
<body>
<div id="p-head" style="LEFT: 0px; POSITION: absolute; TOP: 0px; WIDTH: 100%">
<table class="textBar" border="0" cellspacing="0" cellpadding="3">
<tr>
<td width="50%"><img src="../images/mb16-3.1.gif" alt="Icon" class="formIcon"> Data source query</td>
</tr>
</table>
</div>
<div id="p-menu" class="menuBar" style=" WIDTH: 95%">
<span class="menuBarGrip"></span>
<span class="menuBarGrip" style="LEFT: 5px"></span>
<div class="menuBarItem" id="mb_item_1" style="LEFT: 15px">
<a class="menuBarLink" href="mainpage.php?__ctrl=OnlineDataQueryManager&__mode=view&__view=pane&__user={DIRPUSRPKID}&pkid={OBJRESOPKID}" target=pane>
<img class="menuBarIcon" src="../images/mb16-6.1.gif" title="New query"></a>
</div>
<span class="menuBarLine" style="LEFT: 40px"> </span>
<div class="menuBarItem" id="mb_item_3" style="LEFT: 50px">
<a class="menuBarLink" href="mainpage.php?__ctrl=OnlineDataQueryManager&__mode=view&__view=fields&__user={DIRPUSRPKID}&pkid={OBJRESOPKID}" target=grid>
<img class="menuBarIcon" src="../images/mb16-6.4.gif" alt="Delete button" title="Delete filters"></a>
</div>
<div class="menuBarItem" id="mb_item_4" style="LEFT:75px">
<a class="menuBarLink" href="javascript:AnalyzeQuery()">
<img class="menuBarIcon" src="../images/mb16-6.5.gif" title="Check Query"></a>
</div>
<span class="menuBarLine" style="LEFT: 100px"> </span>
<div class="menuBarItem" id="mb_item_5" style="LEFT:110px">
<a class="menuBarLink" href="javascript:executeQuery()">
<img class="menuBarIcon" src="../images/mb16-6.3.gif" title="Run Query"></a>
</div>
<div class="menuBarItem" id="mb_item_6" style="LEFT:95%">
<a href="helppage.php?__user={DIRPUSRPKID}&__lang={DIRUSERLANG}&__page=quickstart.objectory.Resource-query" class="menuBarLink">
<img class="menuBarIcon" src="../images/mb16-10.gif" alt="Help button" title="Help"></a>
</div>
</div>
<div id="p-body" class="formPlace" style="WIDTH: 95%">
<form name="form" method="post" action="mainpage.php">
<input type="hidden" name="__ctrl" value="data">
<input type="hidden" name="__view" value="list">
<input type="hidden" name="__verb" value="2">
<input type="hidden" name="__user" value="{DIRPUSRPKID}">
<input type="hidden" name="pkid" value="{OBJRESOPKID}">
<input type="hidden" name="fkid" value="{DIRPROJPKID}">
<input type="hidden" name="kind" value="{RCatPkid}">
<!-- tabView : start -->
<div id="p-form" class="formPanel" style="WIDTH: 100%">
<table class="tabView" cellspacing="0" cellpadding="3" onclick="onclick_tabview(event);">
<thead>
<tr>
<td class="tabHead" colspan="3">
<table class="tabHeader">
<tr>
<td width="80%" align="left" class="tabTitle"><img src="images/data_item-{DIRRESTPKID}.gif" alt="Icon" class="formIcon"> {OBJRESONAME} Query Manager</td>
<td width="20%" align="right" >
<table border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr><td class="square1"></td><td class="square2"></td><td class="square3"></td></tr>
<tr><td class="square2"></td><td class="square3"></td><td class="square4"></td></tr>
</tbody>
</table>
</td>
</tr>
</table>
</td>
</tr>
</thead>
<tbody>
<tr>
<td id="t1" class="selItem" width="10%">Query</td>
<td id="t2" class="tabItem" width="10%">Extraction</td>
<td id="t3" class="tabFill" width="80%"> </td>
</tr>
<tr>
<td id="tabPane" class="tabBody" colspan="3">
<!-- Pane 1 : debut -->
<table id="t1page" class="tabPage" cellspacing="0" cellpadding="3">
<tbody>
<tr><td width="100%" colspan="2"><div class="tabViewLightLine"></div></td></tr>
<tr valign="middle">
<td align="left" width="25%"><div class="formLabel">Table prefix</div></td>
<td align="left" width="75%"><input class="formInput" id="pref" name="pref" value=""></td>
</tr>
<tr><td width="100%" colspan="2"><hr class="tabViewHRLine"></td></tr>
<tr>
<td width="100%" colspan="2" align="left" valign=top>
<table id="grid" name="grid" class="gridView" style="width:100%; position:relative" cellspacing="0" cellpadding="1" border="1" bordercolor=activeborder>
<thead>
<tr valign="middle" class="gridHead" >
<td class="gridHeadCell" width="35%"> Table</td>
<td class="gridHeadCell" width="10%"> As</td>
<td class="gridHeadCell" width="10%"> Join</td>
<td class="gridHeadCell" width="45%"> Join Condition</td>
</tr>
</thead>
<tbody>
<tr valign="top" class="gridBody">
<td class="gridBodyCell" width="35%"><span ondrop="fViewOnDropExternal(this, 'table' )" ondragenter="fViewOnDragOver()" ondragover="fViewOnDragOver()" ondragstart="fViewOnDragStart(this,window.event)" style="width:100%"> </span></td>
<td class="gridBodyCell" width="10%"> </td>
<td class="gridBodyCell" width="10%"> </td>
<td class="gridBodyCell" width="45%"> </td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<!-- Pane 2 : debut -->
<table id="t2page" class="tabPage" cellspacing="0" cellpadding="3" style="display: none">
<tbody>
<tr><td width="100%" colspan="2"><hr class="tabViewHRLine"></td></tr>
<tr valign="middle">
<td align="left" width="25%"><div class="formLabel">Format d'extraction</div></td>
<td align="left" width="75%">
<SELECT name="mode" class="formValue">
<OPTION value="xml">XML</OPTION>
<OPTION value="html" selected>HTML</OPTION>
<OPTION value="txt">Texte</OPTION>
</SELECT>
</td>
</tr>
<tr valign="middle">
<td align="left" width="25%"><div class="formLabel">Type d'affichage</div></td>
<td align="left" width="75%">
<SELECT name="mime" class="formValue">
<OPTION value="text/xml">XML</OPTION>
<OPTION value="text/html" selected>HTML</OPTION>
<OPTION value="text/plain" >Texte</OPTION>
<OPTION value="application/vnd.ms-excel">Excel</OPTION>
</SELECT>
</td>
</tr>
<tr><td width="100%" colspan="2"><hr class="tabViewHRLine"></td></tr>
<tr valign="middle">
<td align="left" width="25%"><div class="formLabel">Requête</div></td>
<td align="left" width="75%"><textarea class="formInput" cols="36" name="qury" rows="1"0 ondragenter="fViewOnDragOver()" ondragover="fViewOnDragOver()">{ItemName}</textarea></td>
</tr>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<!-- tabView : end -->
</div>
</form>
</div>
<script>
// QL constant
var cField = 0;
var cTable = 0;
var cAlias = 1;
var cOrder = 2;
var cGroup = 3;
var cFunct = 4;
var cShow = 5;
var cQuery = 6;
// Global structures
var gTablesDrag = new Array();
var gTablesName = new Array();
//
function executeQuery()
{
//
var qury = ConstructQuery();
if( qury == "" )
return;
//
var theMode = document.all["mode"];
var theMime = document.all["mime"];
var theQury = document.all["qury"];
theQury.value = qury;
qury = encodeURI(qury);
//
var page = "mainpage.php?__ctrl=OnlineDataQueryManager&__mode=list&__view=result&__user={DIRPUSRPKID}&pkid={OBJRESOPKID}&type=query&text=" + qury + "&mode=" + theMode.value + "&mime=" + theMime.value;
//
parent.window.location = page;
}
//
function AnalyzeQuery()
{
//
var qury = ConstructQuery();
if( qury == "" )
return;
//
var theQury = document.all["qury"];
theQury.value = qury;
qury = encodeURI(qury);
}
//
function ConstructQuery()
{
// QueryDef
var queryDef = "";
var pivotSet = "";
var fieldSet = "";
var tableSet = "";
var querySet = "";
//
tableSet = BuildTableSet();
if( tableSet == "" )
return( "" );
//
fieldSet = BuildFieldSet();
if( fieldSet == "" )
return( "" );
//
pivotSet = BuildPivotSet();
//
querySet = BuildQuerySet();
//
queryDef += pivotSet;
queryDef += fieldSet;
queryDef += tableSet;
queryDef += querySet;
//
return( queryDef );
}
// BuildTableSet
function BuildTableSet()
{
//
var theGrid = document.all["grid"];
var theBody = theGrid.tBodies(0);
// TableSet [FROM syntax]
var tables = "";
//
var i;
var j;
var n = theBody.rows.length
for( i=0; i < n; i++)
{
//
var prefix = "";
var pref= document.all["pref"];
if( pref.value != "" )
prefix = pref.value + "."
//
var table = theBody.rows(i).cells(0).innerText;
//
if( table && (table != "") && (table != " ") )
{
//
table = prefix + table;
//
if( i > 0 )
{
var union = theBody.rows(i).cells(2).children(0);
if( union )
tables += ( " " + union.value + " " );
}
//
tables += table;
//
var alias = theBody.rows(i).cells(1).children(0);
if( alias && (alias.value != "") ) {
tables += ( " as " + alias.value );
//gTablesName[i] = theRegs[1];
}
//
if( i > 0 ) {
var using = theBody.rows(i).cells(3).children(0).tBodies(0);
if( using ) {
//
for( j=0; j < using.rows.length; j++ )
{
//
if( using.rows(j).cells(0).children(0).children.length > 0 ) {
//
var v1 = using.rows(j).cells(0).children(0).children(0);
if( v1.value == "")
return( "" );
//
if( using.rows(j).cells(1).children.length > 0 ) {
//
var v2 = using.rows(j).cells(1).children(0);
//
if( using.rows(j).cells(2).children(0).children.length == 0 )
return( "" );
//
var v3 = using.rows(j).cells(2).children(0).children(0);
if( v3.value == "" )
return( "" );
//
if( v1 && v2 && v3 ) {
//
if( j == 0 )
tables += ( " ON " + v1.value + v2.value + v3.value);
else
tables += ( " AND " + v1.value + v2.value + v3.value);
}
}
}
}
}
}
}
}
//
if( tables == "" )
return( "" );
else
return( "FROM " + tables + " " );
}
// BuildFieldSet
function BuildFieldSet()
{
// fieldSet [SELECT syntax]
var theView = parent.frames["grid"];
var theGrid = theView.document.all["grid"];
var theHead = theGrid.tHead.children[0];
var theBody = theGrid.tBodies(0);
//
var fields = "";
//
var i;
var j;
var n = theHead.cells.length;
for( i=2; i < n; i++)
{
// Field Name
var field;
if( theHead.cells(i).children(0).children.length == 0 )
field = theHead.cells(i).innerText;
else
field = theHead.cells(i).children(0).children(0).value;
//
if( field && (field != "") && (field != " ") ) {
//
if( theBody.rows(cShow ).cells(i).children(0).value == "on" ) {
//
var table = theBody.rows(cTable).cells(i).innerText;
var alias = theBody.rows(cAlias).cells(i).children(0).value;
var funct = theBody.rows(cFunct).cells(i).children(0).value;
//
if( i > 2 )
fields += ", ";
//
if( funct && (funct != "0") )
fields += ( funct + "(" );
if( table && (table != "") && (table != " ") && ( field != "*") )
fields += ( table + "." );
//
fields += field;
//
if( funct && (funct != "0") )
fields += ")";
//
if( alias && (alias != "") && (alias != " ") )
fields += ( " as " + alias );
}
}
}
//
if( fields == "" )
return( "SELECT * " );
else
return( "SELECT " + fields + " " );
}
// BuildPivotSet
function BuildPivotSet()
{
// PivotSet
var theView = parent.frames["grid"];
var theGrid = theView.document.all["grid"];
var theHead = theGrid.tHead.children[0];
var theBody = theGrid.tBodies(0);
//
var i;
var j;
var n = theHead.cells.length;
// [CROSSTAB syntax]
var pivots = "";
for( i=2; i < n; i++)
{
// Field Name
var field;
if( theHead.cells(i).children(0).children.length == 0 )
field = theHead.cells(i).innerText;
else
field = theHead.cells(i).children(0).children(0).value;
//
if( field && (field != "") && (field != " ") && ( field != "*") ) {
//
var table = theBody.rows(cTable).cells(i).innerText;
var pivot = theBody.rows(cGroup).cells(i).children(0).value;
//
if( pivot && (pivot == "2") ) {
//
if( pivots != "" )
pivots += ", ";
//
if( table && (table != "") && (table != " ") )
pivots += ( table + "." );
pivots += field;
}
}
}
//
if( pivots != "" )
return( "CROSSTAB " + pivots + " " );
else
return( "" );
}
// BuildQuerySet
function BuildQuerySet()
{
// QuerySet
var theView = parent.frames["grid"];
var theGrid = theView.document.all["grid"];
var theHead = theGrid.tHead.children[0];
var theBody = theGrid.tBodies(0);
//
var i;
var j;
var n = theHead.cells.length;
// [WHERE syntax]
var clause = "";
for( i=2; i < n; i++)
{
// Field Name
var field;
if( theHead.cells(i).children(0).children.length == 0 )
field = theHead.cells(i).innerText;
else
field = theHead.cells(i).children(0).children(0).value;
//
if( field && (field != "") && (field != " ") ) {
//
var table = theBody.rows(cTable).cells(i).innerText;
var query = theBody.rows(cQuery).cells(i).children(0).value;
//
if( query && (query != "") && (query != " ") ) {
//
if( clause != "" )
clause += ", ";
//
if( table && (table != "") && (table != " ") && ( field != "*") )
clause += ( table + "." );
//
clause += field;
clause += query;
}
}
}
//
if( clause != "" )
clause = "WHERE " + clause + " ";
// [GROUP syntax]
var groups = "";
for( i=2; i < n; i++)
{
// Field Name
var field;
if( theHead.cells(i).children(0).children.length == 0 )
field = theHead.cells(i).innerText;
else
field = theHead.cells(i).children(0).children(0).value;
//
if( field && (field != "") && (field != " ") && ( field != "*") ) {
//
var table = theBody.rows(cTable).cells(i).innerText;
var group = theBody.rows(cGroup).cells(i).children(0).value;
//
if( group && (group != "0") ) {
//
if( groups != "" )
groups += ", ";
//
if( table && (table != "") && (table != " ") )
groups += ( table + "." );
groups += field;
}
}
}
//
if( groups != "" )
groups = "GROUP BY " + groups + " ";
// [ORDER syntax]
var orders = "";
for( i=2; i < n; i++)
{
// Field Name
var field;
if( theHead.cells(i).children(0).children.length == 0 )
field = theHead.cells(i).innerText;
else
field = theHead.cells(i).children(0).children(0).value;
//
if( field && (field != "") && (field != " ") ) {
//
var order = theBody.rows(cOrder).cells(i).children(0).value;
if( order && (order != "0") ) {
//
if( orders != "" )
orders += ", ";
//
var alias = theBody.rows(cAlias).cells(i).children(0).value;
if( alias && (alias != "") && (alias != " ") )
orders += alias;
else {
var funct = theBody.rows(cFunct).cells(i).children(0).value;
if( funct && (funct != "0") )
orders += ( funct + "(" );
if( table && (table != "") && (table != " ") && ( field != "*") )
orders += ( table + "." );
orders += field;
if( funct && (funct != "0") )
orders += ")";
}
orders += ( " " + order );
}
}
}
//
if( orders != "" )
orders = "ORDER BY " + orders + " ";
//
return( clause + groups + orders );
}
//
function lookupTables( name )
{
//
for( var i=0; i < gTablesName.length; i++ )
{
if( gTablesName[i] == name )
return( true );
}
//
return( false );
}
</script>
</body>
</html>