'> : MetaTables(); foreach ($metaTables as $k => $v) { if (ereg('^qb_', $v) || ereg('_crc$', $v) || ereg('_flag$', $v)) continue; echo "".$v."\n"; } ?> ' onClick='addtable()'>

---

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 ""; } echo ""; $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 ""; $i++; } echo ""; ?>

"; echo "". ""; echo ""; echo " ".$tableId." ". " "; echo ""; echo "*"; foreach ($metaColumns as $k => $v) { echo "".$v->name.""; } echo ""; echo " "; echo "

---

'> $v) { if (!isset($countTable[$v[0]])) $countTable[$v[0]] = 1; } $countSelectedTable = count($countTable); //$countSelectedTable = count($_SESSION['selectedColumn']); if ($countSelectedTable > 1) { ?>

$v2) { $metaColumns = $adodb->MetaColumns($v2[0]); foreach ($metaColumns as $k3 => $v3) { $optionText = $v2[0].'.'.$v3->name; echo "".$optionText."\n"; } } ?>

INNER JOIN LEFT JOIN RIGHT JOIN CROSS JOIN

$v2) { $metaColumns = $adodb->MetaColumns($v2[0]); foreach ($metaColumns as $k3 => $v3) { $optionText = $v2[0].'.'.$v3->name; echo "".$optionText."\n"; } } ?>

onClick=addrelation(this.form)>

---

foreach ($_SESSION['selectedRelation'] as $k => $v) { echo ""; echo ""; echo ""; echo ""; $buttonStyle = "style='background-color:#E64C20;color:white'"; $buttonJs = "onClick='deleterelation(\"".$k."\")'"; echo ""; echo ""; } ?>

". "". "".$v[0]."". "". "

". "". "".$v[1]."". "". "

". "". "".$v[2]."". "". "

". "". "

'> ?>

---

'(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 ""; echo ""; $i = 0; foreach ($_SESSION['selectedColumn'] as $k2 => $v2) { if ($v === 'Field') { #$selectStyle = "style='width:100%'"; echo ""; echo ""; } else if ($v === 'Table') { #$selectStyle = "style='width:100%'"; echo ""; } else if ($v === 'Show') { if (!isset($_SESSION['selectedColumn'][$k2]['show'])) { $_SESSION['selectedColumn'][$k2]['show'] = '1'; } $checked = ''; if ($_SESSION['selectedColumn'][$k2]['show']) $checked = 'checked'; echo ""; } else if ($v === '') { $buttonStyle = "style='background-color:#E64C20;color:white'"; $buttonJs = "onClick='deletecolumn(\"".$k2."\")'"; echo ""; } else if ($v === 'Sort') { if (!isset($_SESSION['selectedColumn'][$k2]['sort'])) { $_SESSION['selectedColumn'][$k2]['sort'] = ''; } #$selectStyle = "style='width:100%'"; echo ""; } else if ($v === 'Total') { if (!isset($_SESSION['selectedColumn'][$k2]['total'])) { $_SESSION['selectedColumn'][$k2]['total'] = ''; } #$selectStyle = "style='width:100%'"; echo ""; } else if ($v === 'Criteria') { $textValue = "value='".$_SESSION['selectedColumn'][$k2]['criteria']."'"; $textStyle = "style='width:100%'"; echo ""; } else if ($v === 'or') { $textValue = "value='".$_SESSION['selectedColumn'][$k2]['or']."'"; $textStyle = "style='width:100%'"; echo ""; } $i++; } echo ""; } $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 ""; ?>

".$tdText."

". ""; 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 "". "".$optionText.""; } echo "". "

". "". "".$v2[0]."". "". "

". "". "

". "". "

". ""; foreach ($arrSort as $k3 => $v3) { if (ereg('[0-9]+', $k3)) $k3 = $v3; $selected = ''; if ($_SESSION['selectedColumn'][$k2]['sort'] === $k3) { $selected = 'selected'; } echo "".$v3.""; } echo "". "

". ""; foreach ($arrTotal as $k3 => $v3) { if (ereg('[0-9]+', $k3)) $k3 = $v3; $selected = ''; if ($_SESSION['selectedColumn'][$k2]['total'] === $k3) { $selected = 'selected'; } echo "".$v3.""; } echo "". "

". "

". "

' onClick=savedesign(this.form)> ' onClick=generatesql(this.form)>