GlobalCube
/
qbuilder


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566
							<?php require_once 'auth.php'; ?>
<html>
<head>
<title><?=__('Design View')?></title>
<link rel="stylesheet" href="phpME.css" type="text/css"></link>
</head>
<body>
<?=__('Parameter')?>: [userid]<br/><br/>
<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($_SESSION);
	#$_REQUEST['idView'] = $_REQUEST['rec'];
	if (isset($_GET['idView'])) {
		$rs = $adodb->Execute("SELECT * FROM qb_view ".
		" WHERE id = ? ", array($_GET['idView']));
		if (!$rs->EOF) {
			$_SESSION['selectedTable'] = array();
			$_SESSION['selectedColumn'] = array();
			$_SESSION['selectedRelation'] = array();
			$unSerialize = unserialize($rs->fields['serialize']);
			if (is_array($unSerialize['selectedTable'])) {
				$_SESSION['selectedTable'] = $unSerialize['selectedTable'];
				$_SESSION['selectedColumn'] = $unSerialize['selectedColumn'];
				$_SESSION['selectedRelation'] = $unSerialize['selectedRelation'];
			}
			#$tableId = $rs->fields['tableid'];
			$viewName = htmlspecialchars($rs->fields['name']);
		}
		$_SESSION['selectedView'] = $_GET['idView'];
	}

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

	if ($_REQUEST['tableid']) $tableId = $_REQUEST['tableid'];
	if ($tableId) {
		$_SESSION['selectedTable'] = array($tableId);

		$saveKey = array();
		$metaColumns = $adodb->MetaColumns($tableId);
		$i = 0;
		foreach ($metaColumns as $k => $v) {
			foreach ($_SESSION['selectedColumn'] as $k2 => $v2) {
				if ( $v2[0] === $tableId && $v2[1] === $v->name) {
					$saveKey[$i] = $v2;
				}
			}
			if (!isset($saveKey[$i])) $saveKey[$i] = array($tableId, $v->name);
			$i++;
		}
		$_SESSION['selectedColumn'] = $saveKey;
	}

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

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

		$_SESSION['selectedColumn'] = array();
		$metaColumns = $adodb->MetaColumns($tableId);
		if (!is_array($metaColumns)) $metaColumns = array();
		foreach ($metaColumns as $k => $v) {
			$_SESSION['selectedColumn'][] = array($tableId, $v->name);
		}
	}
?>

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


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

	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;
			if (!isset($_SESSION['selectedColumn'][$v])) 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 = '';
		$notShow = array();
		foreach ($_SESSION['selectedColumn'] as $k => $v) {
			if (!isset($arrTable[$v[0]])) $arrTable[$v[0]] = 1;
			$tableField = $v[0].".".$v[1];
			$xtableField = $v[1];
			if ($v['criteria']) {
				if ($where) $where .= " AND ";
				$where .= $tableField." = '".$v['criteria']."'";
			}
			if ($v['or']) {
				if ($where) $where .= " AND ";
				$where .= $tableField." ".$v['or']."";
			}
			if ($v['show'] && (!$v['total'] || $v['total'] === 'Group By')) {
				if ($column) $column .= ", ";
				$column .= $xtableField;
			}
			if (!$v['show']) {
				$notShow[$xtableField] = 1;
			}
			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;
		$generateSQL = nl2br($sql);
		$sql = addslashes($sql);
		$sql = str_replace("\n", "\\n", $sql);

		$metaPK = $adodb->MetaPrimaryKeys($table);
		$metaColumns = $adodb->MetaColumns($table);
		$arrCols = $arrCols2 = $arrCols3 = array();
		foreach ($metaColumns as $k => $v) {
			if ($v->primary_key === true) {
				$arrCols3[] = $v->name." = [".$v->name."]";
			} else {
				if ($notShow[$v->name]) continue;
				$arrCols2[] = $v->name." = [".$v->name."]";
			}
			$arrCols[] = $v->name;
		}

		$onInsert = "INSERT INTO ".$table." (".implode(", ", $arrCols).") ".
		" VALUES ( [".implode("], [", $arrCols)."] ); ";
		$onInsert = addslashes($onInsert);
		$onInsert = str_replace("\n", "\\n", $onInsert);

		$onUpdate = "UPDATE ".$table." SET ".implode(", ", $arrCols2).
		" WHERE ".implode(" AND ", $arrCols3).";";
		$onUpdate = addslashes($onUpdate);
		$onUpdate = str_replace("\n", "\\n", $onUpdate);

		$onDelete = "DELETE FROM ".$table." ".
		" WHERE ".implode(" AND ", $arrCols3).";";
		$onDelete = addslashes($onDelete);
		$onDelete = str_replace("\n", "\\n", $onDelete);

		$isDelete = "SELECT * FROM ".$table."_crc".
		" WHERE ".implode(" AND ", $arrCols3).";";
		$isDelete = addslashes($isDelete);
		$isDelete = str_replace("\n", "\\n", $isDelete);

		$readonly = '';
		$rs = $adodb->Execute("SELECT qb_config_odbc.serialize FROM qb_config ".
		" LEFT JOIN qb_config_odbc USING (id) ".
		" WHERE qb_config.tableid = ? ", array($table));
		if (!$rs->EOF) {
			$odbcCol = unserialize($rs->fields['serialize']);
			if (!is_array($odbcCol)) $odbcCol = array();
			#$readonly = implode(", ", $odbcCol);
			foreach ($odbcCol as $k => $v) {
				if ($notShow[$v]) continue;
				if ($readonly) $readonly .= ", ";
				$readonly .= $v;
			}
		}

		echo "<script language=javascript>

		function generateSQL() {

			if (! opener) return false;

			if (! opener.document) return false;

			if (! opener.document.forms[0]) return false;

			f = opener.document.forms[0];

			if (f.sql) f.sql.value = \"".$sql."\";

			if (f.oninsert) f.oninsert.value = \"".$onInsert."\";

			if (f.onupdate) f.onupdate.value = \"".$onUpdate."\";

			if (f.ondelete) f.ondelete.value = \"".$onDelete."\";

			if (f.isdelete) f.isdelete.value = \"".$isDelete."\";

			if (f.readonly) f.readonly.value = \"".$readonly."\";

			return true;

		}

		generateSQL();

		</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 align=center>";

		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>

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

=__("name", "Name","qb_view")?>: <input class="pme-input-1" type="text" name="name" size="60" maxlength="64" value="<?=$viewName?>">

*/
?>
<?php

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

	$countSelectedTable = count($countTable);

?>

<table width=100%>
<?php
$arrTd = array('', 'Field', 'Table', 'Show', /*'Criteria',*/ 'or');
$arrSort = array ('Ascending', 'Descending', '' => '(not sorted)');
$arrTotal = array ('', 'Group By', 'Sum', 'Avg', 'Min', 'Max', 'Count'); 
//, 'StDev', 'Var', 'First', 'Last', 'Expression', 'Where');

if (count($_SESSION['selectedColumn'])) {
	echo "<tr>";
	foreach ($arrTd as $k => $v) {
		$tdText = '';
		if ($v === 'or') $v = 'Criteria';
		if ($v) $tdText = __($v)."";
		if ($v === 'Table' || $v === '') {
		} else {
			echo "<th>".$tdText." </td>";
		}
	}
	echo "</tr>";
}

$i = 0;
foreach ($_SESSION['selectedColumn'] as $k2 => $v2) {

	if ($v === 'Table' || $v === '') {
	} else {
		echo "<tr>";
	}
	
	foreach ($arrTd as $k => $v) {
		if ($v === 'Field') {
			echo "<td align=center>

			<input type=hidden name=id[] value='$k2'>

			<input type=hidden name=field[] ";
			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) {
				$optionVal = $v2[0].".".$v3->name;
				$optionText = $v3->name;	
				$selected = '';
				if ($v2[1] === $v3->name) {
					$selected = 'selected';
					echo " value='".$optionVal."'>".$optionText;
				}
			}
			echo "

			</td>";
		} else if ($v === 'Table') {
			echo "<input type=hidden name=table[] value='".$v2[0]."'>";
		} 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>\n";

			}

			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>\n";
			}
			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=\"".htmlspecialchars($v2['or'])."\"";
			$textStyle = "style='width:99%'";
			echo "<td>

			<input ".$textStyle." type=text name=or[] ".$textValue.">

			</td>";
		}

	}
	if ($v === 'Table' || $v === '') {
	} else {
		echo "</tr>";
	}

	$i++;
}

	$record = array();
	$record['serialize'] = serialize(array(
		'selectedTable' => $_SESSION['selectedTable'],
		'selectedColumn' => $_SESSION['selectedColumn'],
		'selectedRelation' => $_SESSION['selectedRelation']
	));

	$rs = $adodb->Execute("SELECT * FROM qb_view ".
	" WHERE id = ? ", array($_SESSION['selectedView']));
	$updateSQL = $adodb->GetUpdateSQL($rs, $record);
	if ($updateSQL) $adodb->Execute($updateSQL);
	
	$record['serialize'] = str_replace("\n", "\\n", $record['serialize']);
	echo "<script language=javascript>

	form = opener.document.forms[0];

	serializeElement = null;

	if (form) serializeElement = form.serialize;

	if (serializeElement) {

		serializeElement.value = \"".addslashes($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=generatesql(this.form)>
<input type=button value='<?=__('Cancel')?>' onClick="self.close()">
</form>
<?php 
	#<input type=button value='<?=__('Save')? >' onClick=savedesign(this.form)>
	#if ($generateSQL) echo $generateSQL; 
?>
</body>
</html>