<?php
	session_start();
	include("ini.php");
	if(!isset($_SESSION["Db"]))
	{
		echo "<html><meta http-equiv='refresh' content='1; URL=Index.php'><head><title>Automatische Weiterleitung erfolgt</title></head><body>keine Datenbank ausgewählt!</body></html>";
		return;
	}
	$db = $_SESSION["Db"];

	$query = "";
	if(isset($_GET["query"]))
	{
		$query = $_GET["query"];
		$_SESSION["query"]=$query;
	}
	elseif(isset($_SESSION["query"]))
		$query = $_SESSION["query"];

	if(isset($_GET["DelQuery"]) && $query != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "DELETE FROM x_queries WHERE Query='".$query."'";
			mysqli_query ($dbcon, $sql);
			$query = "";
		}
		mysqli_close($dbcon);
	}

	if(isset($_GET["CreateQuery"]))
	{	
		$query = $_GET["CreateQuery"];
		$_SESSION["query"]=$query;
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0 && $db != "")
		{
			$sql = "INSERT INTO x_queries (Query) VALUES ('".$query."')";
			mysqli_query ($dbcon, $sql);
		}
		mysqli_close($dbcon);
	}

	if(isset($_GET["AddField"]) && $query != "")
	{
		$fullfield = $_GET["AddField"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SELECT MAX(Pos) AS cPos FROM x_queryfields";
			$maxx = mysqli_query ($dbcon, $sql);
			if($max = mysqli_fetch_array($maxx))
				$pos = $max["cPos"]+1;
			else
				$pos = 1;
			$table=substr($fullfield,0,strpos($fullfield,'.'));
			$field=substr($fullfield,strpos($fullfield,'.')+1);
			$sql = "INSERT INTO x_queryfields (Query, QTable, QField, Pos, Cond, AggFunc, Alias) VALUES ('".$query."', '".$table."', '".$field."', '".$pos."','','','')";
			mysqli_query ($dbcon, $sql);
		}
		mysqli_close($dbcon);
	}

	if(isset($_GET["DelField"]) && $query != "")
	{
		$fullfield = $_GET["DelField"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$table=substr($fullfield,0,strpos($fullfield,'.'));
			$field=substr($fullfield,strpos($fullfield,'.')+1);
			$sql = "DELETE FROM x_queryfields WHERE Query='".$query."' AND QTable='".$table."' AND QField='".$field."'";
			mysqli_query ($dbcon, $sql);
		}
		mysqli_close($dbcon);
	}

	if(isset($_GET["DelTable"]) && $query != "")
	{
		$table = $_GET["DelTable"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "DELETE FROM x_querytables WHERE Query='".$query."' AND QTable='".$table."'";
			mysqli_query ($dbcon, $sql);
			$sql = "DELETE FROM x_queryfields WHERE Query='".$query."' AND QTable='".$table."'";
			mysqli_query ($dbcon, $sql);
			$sql = "DELETE FROM x_queryjoins WHERE Query='".$query."' AND QTable='".$table."'";
			mysqli_query ($dbcon, $sql);
		}
		mysqli_close($dbcon);
	}

	if(isset($_GET["SaveField"]))
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "UPDATE x_queryfields SET Cond='".$_POST["Cond"]."', AggFunc='".$_POST["AggFunc"]."', Alias='".$_POST["Alias"]."' WHERE (Query='".$query."' AND QTable='".$_GET["SaveFieldTable"]."' AND QField='".$_GET["SaveField"]."')";
			mysqli_query ($dbcon, $sql);
		}
		mysqli_close($dbcon);
	}
	
	function WrapSelWhat($t,$f,$agg,$alias)
	{
		$as = "";
		if($alias != "") $as = " AS ".$alias;
		if(strncmp ($agg, "a", 1 ) > 0 || $agg == "GROUP BY" || $agg == "") 
			return $t.".".$f.$as;
		return $agg."(".$t.".".$f.")".$as;
	}
?>
<!DOCTYPE html>
<html lang="de">
	<head>
		<link rel="stylesheet" href="Standard.css">
		<title>Queries</title>
	</head>
	<body>
		<header>
			<h1>Abfragen erstellen in <font color='red'><?php echo $db;?></font></h1>
			<nav>
				Gespeicherte Abfragen: 
<?PHP 
	$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
	if (mysqli_connect_errno()==0)
	{
		$sql = "SELECT Query FROM x_queries";
		$queries = mysqli_query ($dbcon, $sql);
		while($dquery = mysqli_fetch_array($queries))
		{
			if($dquery[0] == $query) 
				echo "<a href='SelectData.php?query=".$dquery[0]."'><font color = 'red'>".$dquery[0] ."</font></a> ";
			else
				echo "<a href='SelectData.php?query=".$dquery[0]."'>".$dquery[0] ."</a> ";
		}
	}
	mysqli_close($dbcon);
?>
				<form action="SelectData.php"><input type="submit" value="Abfrage erstellen: "><input type="text" name="CreateQuery" size="30"></form>
				<form action="SelectData.php?DelQuery=True" method="POST"><input type="submit" value="Aktuelle Abfrage l&ouml;schen"></form>
				<form action="Index.php"><input type="submit" value="Zur&uuml;ck zur Hauptseite"></form>
			</nav>
		</header>
		<aside>
			<h2>Tabellen</h2>
			<p>
<?PHP 
	if($query != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SELECT QTable,QJoin FROM x_querytables WHERE Query='".$query."' ORDER BY Pos";
			$tables = mysqli_query ($dbcon, $sql);
			while($table = mysqli_fetch_array($tables))
				echo "<a href='SelectData.php?DelTable=".$table["QTable"]."'><img src='Delete.png'></a> ".$table["QJoin"]." <b>".$table["QTable"]."</b><br>";
		}
		mysqli_close($dbcon);
	}
?>			
			</p>
			<nav>
				<form action="EditJoin.php"><input type="submit" value="Tabelle hinzuf&uuml;gen:">
					<select name="Join">
						<option>&nbsp;</option>
						<option>,</option>
						<option>INNER JOIN</option>
						<option>LEFT JOIN</option>
						<option>RIGHT JOIN</option>
					</select>

					<select name="AddTable">
<?PHP
	if($query != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SHOW TABLES";
			$tables = mysqli_query ($dbcon, $sql);
			while($table = mysqli_fetch_array($tables))
				echo "<option>".$table[0]."</option>";
		}
		mysqli_close($dbcon);
	}
?>						
					</select>
				</form>
				<br>
			</nav>
		</aside>
		<article>
			<h2>Daten</h2>
			<section>
<?PHP 
	if($query != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{	
			echo "<table><tr>";
			$sql = "SELECT * FROM x_queryfields WHERE Query='".$query."' ORDER BY Pos";
			$fields = mysqli_query ($dbcon, $sql);
			$selwhat = "SELECT ";
			$selwhere = "WHERE (";
			$groupby = "GROUP BY ";
			$selhaving = "HAVING (";
			$nrCols = 0;
			while($qfields = mysqli_fetch_array($fields))
			{
				$selwhat = $selwhat.WrapSelWhat($qfields["QTable"],$qfields["QField"],$qfields["AggFunc"],$qfields["Alias"]).", ";
				if($qfields["AggFunc"]=="GROUP BY") 
					$groupby = $groupby.$qfields["QTable"].".".$qfields["QField"].", ";
				if($qfields["Cond"]!="")
				{
					if($qfields["Alias"]!="")
						$selhaving = $selhaving.$qfields["Alias"]." ".$qfields["Cond"]." AND ";
					else
						$selwhere = $selwhere.$qfields["QTable"].".".$qfields["QField"]." ".$qfields["Cond"]." AND ";
				}
				if($qfields["Alias"]!="")
					echo "<th>".$qfields["Alias"]."</th>";
				else
					echo "<th>".$qfields["QField"]."</th>";
				$nrCols++;
			}
			$selwhat = substr($selwhat, 0, -2)." FROM ";
			if($groupby == "GROUP BY ")
				$groupby = "";
			else
				$groupby = substr($groupby, 0, -2)." ";
			if($selwhere == "WHERE (")
				$selwhere = "";
			else
				$selwhere = substr($selwhere, 0, -4).") ";
			if($selhaving == "HAVING (")
				$selhaving = "";
			else
				$selhaving = substr($selhaving, 0, -4).") ";
			echo "</tr><tr>";
			
			$sql = "SELECT QTable, QJoin, Pos FROM x_querytables WHERE Query='".$query."' ORDER BY Pos";
			$tables = mysqli_query ($dbcon, $sql);
			$first = true;
			$selfrom = "";
			while($table = mysqli_fetch_array($tables))
			{
				if($first)
				{
					$selfrom = $selfrom.$table["QTable"]." ";
					$first = false;
				}
				else
				{
					$selfrom = $selfrom.$table["QJoin"]." ".$table["QTable"]." ";
					if($table["QJoin"] != ",")
					{
						$selfrom = "(".$selfrom."ON ";
						$sql = "SELECT QField, LTable, LField FROM x_queryjoins WHERE Query='".$query."' AND QTable='".$table["QTable"]."'";
						$joins = mysqli_query ($dbcon, $sql);
						while($join = mysqli_fetch_array($joins))
							$selfrom = $selfrom.$join["LTable"].".".$join["LField"]." = ".$table["QTable"].".".$join["QField"]." AND ";
						$selfrom = substr($selfrom, 0, -4).") ";
					}
				}
			}
			if($nrCols>0 && $selfrom !="")
			{
				$sql = $selwhat.$selfrom.$selwhere.$groupby.$selhaving;
				$rows = mysqli_query ($dbcon, $sql);
				while($row = mysqli_fetch_array($rows))
				{
					for($c=0; $c<$nrCols; $c++)
						echo "<td>".$row[$c]."</td>";
					echo "</tr>";
				}
				echo "</tr></table>";
				echo "<p>".$sql."</p>";
			}
			else
				echo "</tr></table>";
		}
		mysqli_close($dbcon);
	}
?>			
			</section>
		</article>
		<aside>
			<h2>Felder</h2>
			<?PHP
	if($query != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SELECT * FROM x_queryfields WHERE Query='".$query."' ORDER BY Pos";
			$fields = mysqli_query ($dbcon, $sql);
			while($field = mysqli_fetch_array($fields))
			{
				echo "<div class='topic'><b>".$field["QTable"].".".$field["QField"]."</b><a href='SelectData.php?DelField=".$field["QTable"].".".$field["QField"]."'><img src='Delete.png'></a><br>";
				echo "<form action='SelectData.php?SaveField=".$field["QField"]."&SaveFieldTable=".$field["QTable"]."' method='POST'>";
				echo "Bed.:<input type='text' name='Cond' value='".$field["Cond"]."' size='15'><br>";
				echo "Agg.:<select name='AggFunc'>";
				$opts = array("&nbsp;","GROUP BY","COUNT","SUM","AVG","STDDEV","MAX","MIN");
				for($i=0;$i<8;$i++)
				{
					if($field["AggFunc"]==$opts[$i])
						echo "<option selected>".$opts[$i]."</option>";
					else
						echo "<option>".$opts[$i]."</option>";
				}
				echo "</select><br>";
				echo "Alias:<input type='text' name='Alias' value='".$field["Alias"]."' size='15'><br>";
				echo "<input type='submit' value='Save'></form></div>";
			}
		}
		mysqli_close($dbcon);
	}
?>									
			<nav>
				<form action="SelectData.php"><input type="submit" value="Feld hinzuf&uuml;gen:">
					<select name="AddField">
<?PHP
	if($query != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SELECT QTable FROM x_querytables WHERE Query='".$query."'";
			$tables = mysqli_query ($dbcon, $sql);
			while($table = mysqli_fetch_array($tables))
			{
				$sql = "SHOW COLUMNS FROM ".$table[0];
				$cols = mysqli_query ($dbcon, $sql);
				while($col = mysqli_fetch_array($cols))
					echo "<option>".$table[0].".".$col[0]."</option>";
			}
		}
		mysqli_close($dbcon);
	}
?>						
					</select>
				</form>
			</nav>
		</aside>
		<footer>
			<p>&copy; 2020 by L&uuml;der Reimers</p>
		</footer>  
	</body>
</html>