Location: PHPKode > projects > Entier Studio > entierstudio/studio/templates/OnlineDataQueryManager-tables.html
<!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">&nbsp;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">&nbsp;</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">&nbsp;</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">&nbsp;{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%">&nbsp;</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%">&nbsp;Table</td>
			<td class="gridHeadCell" width="10%">&nbsp;As</td>
			<td class="gridHeadCell" width="10%">&nbsp;Join</td>
		    <td class="gridHeadCell" width="45%">&nbsp;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%">&nbsp;</span></td>
			<td class="gridBodyCell" width="10%">&nbsp;</td>
			<td class="gridBodyCell" width="10%">&nbsp;</td>
		    <td class="gridBodyCell" width="45%">&nbsp;</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>
Return current item: Entier Studio