GlobalCube
/
qbuilder


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540
							<?php require_once 'auth.php'; ?>
<html>
<head>
<title><?=__('Design View')?></title>
<style type='text/css'>
body, th, td, select, input { font-size: 8pt; }
</style>
</head>
<body>

<script language=javascript>
	function addtable() {
		tableElement = document.forms[0].table;
		id = tableElement.options[tableElement.selectedIndex].value;
		f = document.fView;
		f.addtable.value = 1;
		f.idtable.value = id
		f.submit();
	}

	function deletetable(id) {
		f = document.fView;
		f.deltable.value = 1;
		f.idtable.value = id
		f.submit();
	}

	function addcolumn(table, columnElement) {
		id = columnElement.options[columnElement.selectedIndex].value;
		f = document.fView;
		f.addcolumn.value = 1;
		f.idcolumn.value = id;
		f.tbcolumn.value = table;
		f.submit();
	}

	function deletecolumn(id) {
		f = document.fView;
		f.idcolumn.value = id;
		f.delcolumn.value = 1;
		f.submit();
	}

	function savedesign(f) {
		f.submit();
	}

	function addrelation(f) {
		f.addrelation.value = 1;
		f.submit();
	}

	function deleterelation(id) {
		f.delrelation.value = 1;
		f.idrelation.value = id;
		f.submit();
	}

	function generatesql(f) {
		f.generate.value = 1;
		f.submit();
	}

</script>
<?php

	//print_r($_POST);

?>
<table>
<tr>
<td>
<form method=post action='<?=$_SERVER['PHP_SELF']?>'>
<?=__('Tables')?>: <select name=table>
<?php
	$metaTables  = $adodb->MetaTables();
	foreach ($metaTables as $k => $v) {
		if (ereg('^qb_', $v) || ereg('_crc$', $v) || ereg('_flag$', $v)) continue;
		echo "<option value='".$v."'>".$v."</option>\n";
	}
?>
</select>
<input type=button style='width:80px' value='<?=__('Add')?>' onClick='addtable()'><br/>
</form>
</td>
</tr>
</table>

<hr/>

<table border=0><tr>
<?php

	if (isset($_REQUEST['idView'])) {
		$rs = $adodb->Execute("SELECT serialize FROM qb_view ".
		" WHERE id = ? ", $_REQUEST['idView']);
		$_SESSION['selectedTable'] = array();
		$_SESSION['selectedColumn'] = array();
		$_SESSION['selectedRelation'] = array();
		if (!$rs->EOF) {
			$unSerialize = unserialize($rs->fields['serialize']);
			if (is_array($unSerialize['selectedTable'])) {
				$_SESSION['selectedTable'] = $unSerialize['selectedTable'];
				$_SESSION['selectedColumn'] = $unSerialize['selectedColumn'];
				$_SESSION['selectedRelation'] = $unSerialize['selectedRelation'];
			}
		}
		$_SESSION['selectedView'] = $_REQUEST['idView'];
	}

	if (!isset($_SESSION['selectedTable'])) $_SESSION['selectedTable'] = array();
	if (!isset($_SESSION['selectedColumn'])) $_SESSION['selectedColumn'] = array();
	if (!isset($_SESSION['selectedRelation'])) $_SESSION['selectedRelation'] = array();


	if ($_POST['deltable']) {
		unset($_SESSION['selectedTable'][$_POST['idtable']]);
	}

	if ($_POST['addtable']) {
		$tableId = $_POST['idtable'];
		$_SESSION['selectedTable'][] = $tableId;
	}

	if ($_POST['delcolumn']) {
		unset($_SESSION['selectedColumn'][$_POST['idcolumn']]);
	}

	if ($_POST['addcolumn']) {
		$tableId = $_POST['tbcolumn'];
		$_SESSION['selectedColumn'][] = array($tableId, $_POST['idcolumn']);
	}

	if ($_POST['action'] === 'savedesign') {
		if (!is_array($_POST['id'])) $_POST['id'] = array();
		foreach ($_POST['id'] as $k => $v) {
			if ($_POST['delcolumn'] && $v === $_POST['idcolumn']) continue;
			$_SESSION['selectedColumn'][$v]['total'] = $_POST['total'][$k];
			$_SESSION['selectedColumn'][$v]['sort'] = $_POST['sort'][$k];
			
			$showVal = $_POST['show'][$k];
			if ($showVal === 'on') $_SESSION['selectedColumn'][$v]['show'] = 1;
			else $_SESSION['selectedColumn'][$v]['show'] = 0;

			$fieldVal = $_POST['field'][$k];
			list($table, $field) = explode(".", $fieldVal);
			$_SESSION['selectedColumn'][$v][0] = $table;
			$_SESSION['selectedColumn'][$v][1] = $field;

			$_SESSION['selectedColumn'][$v]['criteria'] = $_POST['criteria'][$k];
			$_SESSION['selectedColumn'][$v]['or'] = $_POST['or'][$k];
		}
	}

	if ($_POST['addrelation']) {
		$a = $_POST['rLeft'];
		$b = $_POST['rJoin'];
		$c = $_POST['rRight'];
		$_SESSION['selectedRelation'][] = array($a, $b, $c);
	}

	if ($_POST['delrelation']) {
		unset($_SESSION['selectedRelation'][$_POST['idrelation']]);
	}

	if ($_POST['generate']) {
		$column = $where = $orderby = $table = $group = '';
		foreach ($_SESSION['selectedColumn'] as $k => $v) {
			if (!isset($arrTable[$v[0]])) $arrTable[$v[0]] = 1;
			$tableField = $v[0].".".$v[1];
			if ($v['criteria']) {
				if ($where) $where .= " AND ";
				$where .= $tableField." = '".$v['criteria']."'";
			}
			if ($v['show'] && (!$v['total'] || $v['total'] === 'Group By')) {
				if ($column) $column .= ",";
				$column .= $tableField;
			}
			if ($v['sort'] && $v[1] !== '*') {
				$order = "ASC";
				if ($v['sort'] === 'Descending') $order = "DESC";
				
				if ($orderby) $orderby .= ",";
				$orderby .= $tableField." ".$order;
			}
			if ($v['total']) {
				if ($v['total'] === 'Group By') {
					if ($group) $group .= ",";
					$group .= $tableField;
				} else if ($v['show']) {
					if ($column) $column .= ",";
					$column .= strtoupper($v['total'])."(".$tableField.")";
				}
			}
		}
		$countTable = count($arrTable);
		if ($countTable > 1) {
			$arrRelation = array();
			foreach ($_SESSION['selectedRelation'] as $k => $v) {
				$left = $v[0];
				$join = $v[1];
				$right = $v[2];
				list($leftTable, $leftField) = explode(".", $left);
				list($rightTable, $rightField) = explode(".", $right);
				$arrRelation[$leftTable][$join][$rightTable][] = array($v[0],$v[2]);
			}
			foreach ($arrRelation as $leftTable => $v) {
			foreach ($v as $join => $v2) {
			foreach ($v2 as $rightTable => $v3) {
				if ($table) $table .= $join." ".$rightTable;
				else $table .= $leftTable." ".$join." ".$rightTable;
				$table .= " ON (";
				$using = '';
				foreach ($v3 as $k4 => $v4) {
					if ($using) $using .= " AND ";
					$using .= $v4[0]." = ".$v4[1];
				}
				$table .= $using.")";
			}
			}
			}
		}

		if ($table === '') {
			foreach ($arrTable as $k => $v) {
				if ($table) $table .= ", ";
				$table .= $k;
			}
			
		}

		$sql = "SELECT ".$column."\n".
		"FROM ".$table."\n";
		if ($where) $sql .= "WHERE ".$where."\n";
		if ($group) $sql .= "GROUP BY ".$group."\n";
		if ($orderby) $sql .= "ORDER BY ".$orderby;
		$sql = addslashes($sql);
		echo nl2br($sql);
		$sql = str_replace("\n", "\\n", $sql);
		echo "<script language=javascript>
		form = opener.document.forms[0];
		sqlElement = null;
		if (form) sqlElement = form.sql;
		if (sqlElement) {
			opener.document.forms[0].sql.value = '".$sql."';
		}
		</script>";
	}


	echo "<form method=post action='".$_SERVER['PHP_SELF']."'>";
	$i = 0;
	foreach ($_SESSION['selectedTable'] as $k => $v) {
		$tableId = $v;
		$metaColumns = $adodb->MetaColumns($tableId);
		$sizeColumns = count($metaColumns)+1;
		if ($sizeColumns>10) $sizeColumns = 10;
		$thStyle = "style='white-space: nowrap; background-color:#0055f6;color:white'";
		#$selectStyle = "style='width:100%'";
		$selectJs = "ondblclick='addcolumn(\"".$tableId."\", this)'";
		$selectSize = "size=".$sizeColumns;
		$buttonStyle = "style='background-color:#E64C20;color:white'";
		$buttonJs = "onClick='deletetable(\"".$k."\")'";
		echo "<td valign=top>";
		echo "<table style='border:1px #0055f6 solid;'>".
		"<tr><th ".$thStyle.">".$tableId." ".
		" <input ".$buttonStyle." type=button value=X ".$buttonJs."></th></tr>";
		echo "<tr><td>";
		echo "<select ".$selectStyle." name=column".$i." ".$selectSize." ".$selectJs.">";
		echo "<option value='*'>*</option>";
		foreach ($metaColumns as $k => $v) {
			echo "<option value='".$v->name."'>".$v->name."</option>";
		}
		echo "</select>";
		echo "</td></tr>";
		echo "</table>";
		echo "</td>";
		$i++;
	}
	echo "</form>";
?>
</tr></table>
<hr/>

<form name=fView method=post action='<?=$_SERVER['PHP_SELF']?>'>
<?php

	$countTable = array();
	foreach ($_SESSION['selectedColumn'] as $k => $v) {
		if (!isset($countTable[$v[0]])) $countTable[$v[0]] = 1;
	}

	$countSelectedTable = count($countTable);
	//$countSelectedTable = count($_SESSION['selectedColumn']);
	if ($countSelectedTable > 1) {
?>

<table>
<tr>

<td valign=top>
<select name=rLeft>
<?php
		foreach ($_SESSION['selectedColumn'] as $k2 => $v2) {
			$metaColumns = $adodb->MetaColumns($v2[0]);
			foreach ($metaColumns as $k3 => $v3) {
				$optionText = $v2[0].'.'.$v3->name;
				echo "<option value='".$optionText."'>".$optionText."</option>\n";
			}
		}
?>
</select>
</td>

<td valign=top>
<select name=rJoin>
<option value='INNER JOIN'>INNER JOIN</option>
<option value='LEFT JOIN'>LEFT JOIN</option>
<option value='RIGHT JOIN'>RIGHT JOIN</option>
<option value='CROSS JOIN'>CROSS JOIN</option>
</select>
</td>

<td valign=top>
<select name=rRight>
<?php
		foreach ($_SESSION['selectedColumn'] as $k2 => $v2) {
			$metaColumns = $adodb->MetaColumns($v2[0]);
			foreach ($metaColumns as $k3 => $v3) {
				$optionText = $v2[0].'.'.$v3->name;
				echo "<option value='".$optionText."'>".$optionText."</option>\n";
			}
		}
?>
</select>
</td>


<td>
<input type=hidden name=addrelation>
<input type=hidden name=delrelation>
<input type=hidden name=idrelation>
<input type=button value=<?=__('Add')?> onClick=addrelation(this.form)>
</td>

</tr>
</table>


<hr/>

<table>

<?php

	//</form>

	
		foreach ($_SESSION['selectedRelation'] as $k => $v) {
			echo "<tr>";
			echo "<td>".
			"<select name=left[]>".
			"<option value='".$v[0]."'>".$v[0]."</option>".
			"</select>".
			"</td>";
			echo "<td>".
			"<select name=join[]>".
			"<option value='".$v[1]."'>".$v[1]."</option>".
			"</select>".
			"</td>";
			echo "<td>".
			"<select name=right[]>".
			"<option value='".$v[2]."'>".$v[2]."</option>".
			"</select>".
			"</td>";
	
			$buttonStyle = "style='background-color:#E64C20;color:white'";
			$buttonJs = "onClick='deleterelation(\"".$k."\")'";
			echo "<td>".
			"<input ".$buttonStyle." type=button value=X ".$buttonJs.">".
			"</td>";
	
			echo "</tr>";
		}
?>

</table>
<?php
	}


//<form method=post action='<?=$_SERVER['PHP_SELF']? >'>

?>

<hr/>


<table border=1>
<?php
$arrTd = array('', 'Field', 'Table', 'Total', 'Sort', 'Show', 'Criteria', 'or');
$arrSort = array ('Ascending', 'Descending', '' => '(not sorted)');
$arrTotal = array ('', 'Group By', 'Sum', 'Avg', 'Min', 'Max', 'Count'); 
//, 'StDev', 'Var', 'First', 'Last', 'Expression', 'Where');
foreach ($arrTd as $k => $v) {
	$tdText = '';
	if ($v) $tdText = __($v).":";
	echo "<tr>";
	echo "<td>".$tdText." </td>";
	$i = 0;
	foreach ($_SESSION['selectedColumn'] as $k2 => $v2) {
		if ($v === 'Field') {
			#$selectStyle = "style='width:100%'";
			echo "<input type=hidden name=id[] value='$k2'>";
			echo "<td>".
			"<select name=field[] ".$selectStyle.">";
			if (!isset($issetTable[$v2[0]])) {
				$metaColumns = $adodb->MetaColumns($v2[0]);
				$starObj->name = '*';
				$metaStar = array('star' => $starObj);
				$issetTable[$v2[0]] = array_merge($metaStar, $metaColumns);
			}
			$metaColumns = $issetTable[$v2[0]];
			foreach ($metaColumns as $k3 => $v3) {
				$optionText = $v2[0].".".$v3->name;	
				$selected = '';
				if ($v2[1] === $v3->name) $selected = 'selected';
				echo "<option value='".$optionText."' ".$selected.">".
				"".$optionText."</option>";
			}
			echo "</select>".
			"</td>";
		} else if ($v === 'Table') {
			#$selectStyle = "style='width:100%'";
			echo "<td>".
			"<select name=table[] ".$selectStyle.">".
			"<option value='".$v2[0]."'>".$v2[0]."</option>".
			"</select>".
			"</td>";
		} else if ($v === 'Show') {
			if (!isset($_SESSION['selectedColumn'][$k2]['show'])) {
				$_SESSION['selectedColumn'][$k2]['show'] = '1';
			}
			$checked = '';
			if ($_SESSION['selectedColumn'][$k2]['show']) $checked = 'checked';
			echo "<td align=center>".
			"<input type=checkbox name=show[".$i."] ".$checked.">".
			"</td>";
		} else if ($v === '') {
			$buttonStyle = "style='background-color:#E64C20;color:white'";
			$buttonJs = "onClick='deletecolumn(\"".$k2."\")'";
			echo "<td align=center>".
			"<input ".$buttonStyle." type=button value=X ".$buttonJs.">".
			"</td>";
		} else if ($v === 'Sort') {
			if (!isset($_SESSION['selectedColumn'][$k2]['sort'])) {
				$_SESSION['selectedColumn'][$k2]['sort'] = '';
			}
			#$selectStyle = "style='width:100%'";
			echo "<td>".
			"<select name=sort[] ".$selectStyle.">";
			foreach ($arrSort as $k3 => $v3) {
				if (ereg('[0-9]+', $k3)) $k3 = $v3;
				$selected = '';
				if ($_SESSION['selectedColumn'][$k2]['sort'] === $k3) {
					$selected = 'selected';
				}
				echo "<option value='".$k3."' ".$selected.">".$v3."</option>";
			}
			echo "</select>".
			"</td>";
		} else if ($v === 'Total') {
			if (!isset($_SESSION['selectedColumn'][$k2]['total'])) {
				$_SESSION['selectedColumn'][$k2]['total'] = '';
			}
			#$selectStyle = "style='width:100%'";
			echo "<td>".
			"<select name=total[] ".$selectStyle.">";
			foreach ($arrTotal as $k3 => $v3) {
				if (ereg('[0-9]+', $k3)) $k3 = $v3;
				$selected = '';
				if ($_SESSION['selectedColumn'][$k2]['total'] === $k3) {
					$selected = 'selected';
				}
				echo "<option value='".$k3."' ".$selected.">".$v3."</option>";
			}
			echo "</select>".
			"</td>";
		} else if ($v === 'Criteria') {
			$textValue = "value='".$_SESSION['selectedColumn'][$k2]['criteria']."'";
			$textStyle = "style='width:100%'";
			echo "<td>".
			"<input ".$textStyle." type=text name=criteria[] ".$textValue."></td>";
		} else if ($v === 'or') {
			$textValue = "value='".$_SESSION['selectedColumn'][$k2]['or']."'";
			$textStyle = "style='width:100%'";
			echo "<td>".
			"<input ".$textStyle." type=text name=or[] ".$textValue."></td>";
		}
		$i++;
	}
	echo "</tr>";
}

	$record = array();
	$record['serialize'] = serialize(array(
		'selectedTable' => $_SESSION['selectedTable'],
		'selectedColumn' => $_SESSION['selectedColumn'],
		'selectedRelation' => $_SESSION['selectedRelation']
	));
	#$rs = $adodb->Execute("SELECT * FROM qb_view ".
	#" WHERE id = ? ", $_SESSION['selectedView']);
	#$adodb->Execute($adodb->GetUpdateSQL($rs, $record, 1));
	$record['serialize'] = str_replace("\n", "\\n", $record['serialize']);
	echo "<script language=javascript>
	form = opener.document.forms[0];
	sqlElement = null;
	if (form) sqlElement = form.sql;
	if (sqlElement) {
		opener.document.forms[0].serialize.value = '".$record['serialize']."';
	}
	</script>";

?>
</table>
<input type=hidden name=deltable>
<input type=hidden name=addtable>
<input type=hidden name=idtable>
<input type=hidden name=addcolumn>
<input type=hidden name=idcolumn>
<input type=hidden name=tbcolumn>
<input type=hidden name=delcolumn>
<input type=hidden name=action value=savedesign>
<input type=hidden name=generate>
<input type=button value='<?=__('Save')?>' onClick=savedesign(this.form)>
<input type=button value='<?=__('Generate')?>' onClick=generatesql(this.form)>
</form>

</body>
</html>