<?php
	session_start();
	include("ini.php");
	$db = "";	
	$table = "";

	function AssertQueriesThere($db)
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SHOW TABLES";
			$tables = mysqli_query ($dbcon, $sql);
			$queriesThere = false;
			while($ttable = mysqli_fetch_array($tables))
				$queriesThere |= ($ttable[0] == "x_queries"); 
			if(!$queriesThere)
			{
				$sql = "CREATE TABLE x_queries (Query varchar(20) NOT NULL, PRIMARY KEY (Query)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
				mysqli_query ($dbcon, $sql);
				$sql = "CREATE TABLE x_querytables ( Query varchar(20) NOT NULL, QTable varchar(30) NOT NULL, QJoin varchar(10) NOT NULL, Pos int(11) NOT NULL, PRIMARY KEY (Query,QTable)) ENGINE=InnoDB  DEFAULT CHARSET=utf8";
				mysqli_query ($dbcon, $sql);
				$sql = "CREATE TABLE x_queryfields (Query varchar(20) NOT NULL, QTable varchar(30) NOT NULL, QField varchar(30) NOT NULL, Pos int(11) NOT NULL, Cond varchar(50) NOT NULL, AggFunc varchar(10) NOT NULL, Alias varchar(30) NOT NULL, PRIMARY KEY (Query,QTable,QField)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
				mysqli_query ($dbcon, $sql);
				$sql = "CREATE TABLE x_queryjoins (Query varchar(20) NOT NULL,  QTable varchar(30) NOT NULL, QField varchar(30) NOT NULL, LTable varchar(30) NOT NULL, LField varchar(30) NOT NULL, PRIMARY KEY (Query,QTable,QField)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
				mysqli_query ($dbcon, $sql);
				$sql = "ALTER TABLE x_queryfields ADD CONSTRAINT FOREIGN KEY (Query,QTable) REFERENCES x_querytables (Query,QTable) ON DELETE CASCADE ON UPDATE CASCADE";
				mysqli_query ($dbcon, $sql);
				$sql = "ALTER TABLE x_queryjoins ADD CONSTRAINT FOREIGN KEY (Query,QTable) REFERENCES x_querytables (Query,QTable) ON DELETE CASCADE ON UPDATE CASCADE";
				mysqli_query ($dbcon, $sql);
				$sql = "ALTER TABLE x_querytables ADD CONSTRAINT FOREIGN KEY (Query) REFERENCES x_queries (Query) ON DELETE CASCADE ON UPDATE CASCADE";
				mysqli_query ($dbcon, $sql);
				$sql = "CREATE TABLE x_sql (QueryName varchar(40) NOT NULL, QuerySql text NOT NULL, PRIMARY KEY (QueryName)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
				mysqli_query ($dbcon, $sql);
			}
			
			$queryFields = mysqli_query ($dbcon," SELECT * FROM  x_queryfields");
			$field_cnt = mysqli_num_fields($queryFields);
			if($field_cnt<7)
				mysqli_query($dbcon, 'ALTER TABLE x_queryfields ADD Alias varchar(30) NOT NULL');
		}
		mysqli_close($dbcon);
	}
	
	if(isset($_GET["Db"]))
	{
		$db = $_GET["Db"];
		$_SESSION["Db"]=$db;
		AssertQueriesThere($db);
	}
	elseif(isset($_SESSION["Db"]))
	{
		$db = $_SESSION["Db"];
		$table = (isset($_GET["Table"])) ? $_GET["Table"]: "";
	}


	if(isset($_GET["DelDb"]) && $db != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD);
		if (mysqli_connect_errno()==0)
		{
			$sql = "DROP DATABASE IF EXISTS ".$db;
			mysqli_query ($dbcon, $sql);
			$db = "";
		}
		mysqli_close($dbcon);
	}

	if(isset($_GET["CreateDb"]))
	{	
		$db = $_GET["CreateDb"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD);
		if (mysqli_connect_errno()==0 && $db != "")
		{
			$sql = "CREATE DATABASE IF NOT EXISTS ".$db." DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
			mysqli_query ($dbcon, $sql);
			AssertQueriesThere($db);
		}
		mysqli_close($dbcon);
	}

	function AddLine($dbcon, $table)
	{
		$sql = "SHOW COLUMNS FROM ".$table;
		$cols = mysqli_query ($dbcon, $sql);
		$nrCols=0;
		$sql = "INSERT INTO ".$table." (";
		while($col = mysqli_fetch_array($cols))
		{
			$sql = $sql.$col[0].", ";
			$nrCols++;
		}
		$sql = substr($sql, 0, -2).") VALUES (";
		for($c=0; $c<$nrCols; $c++)
			$sql = $sql."'".$_POST["C".$c]."', ";
		$sql = substr($sql, 0, -2).")";
		$sql = str_replace("\\","\\\\",$sql);  // notwendig, damit TEX-Kommandos erhalten bleiben
		mysqli_query ($dbcon, $sql);
	}
	
	if(isset($_GET["AddLine"]))
	{	
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0) AddLine($dbcon, $table);
		mysqli_close($dbcon);
	}

	function DeleteLine($dbcon, $table, $delLine)
	{
		$sql = "SELECT * FROM ".$table;
		$rows = mysqli_query ($dbcon, $sql);
		$rowNr = 0;
		while($row = mysqli_fetch_array($rows))
		{
			if($rowNr == $delLine) break;
			$rowNr++;
		}

		$sql = "SHOW COLUMNS FROM ".$table;
		$cols = mysqli_query ($dbcon, $sql);
		$nrCols=0;
		$sql = "DELETE FROM ".$table." WHERE (";
		while($col = mysqli_fetch_array($cols))
		{
			$sql = $sql.$col[0]."='".$row[$nrCols]."' AND ";
			$nrCols++;
		}

		$sql = substr($sql, 0, -4).")";

		mysqli_query ($dbcon, $sql);
	}

	if(isset($_GET["DelLine"]))
	{	
		$delLine = $_GET["DelLine"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
			DeleteLine($dbcon, $table, $delLine);
		mysqli_close($dbcon);
	}

	function SaveLine($dbcon, $table, $saveLine)
	{
		$sql = "SELECT * FROM ".$table;
		$rows = mysqli_query ($dbcon, $sql);
		$rowNr = 0;
		while($row = mysqli_fetch_array($rows))
		{
			if($rowNr == $saveLine) break;
			$rowNr++;
		}

		$sql = "SHOW COLUMNS FROM ".$table;
		$cols = mysqli_query ($dbcon, $sql);
		$nrCols=0;
		$wheresql = " WHERE (";
		$setsql = " SET ";
		while($col = mysqli_fetch_array($cols))
		{
			$wheresql = $wheresql.$col[0]."='".$row[$nrCols]."' AND ";
			$setsql = $setsql.$col[0]."='".$_POST["C".$nrCols]."', ";
			$nrCols++;
		}
		$wheresql = substr($wheresql, 0, -4).")";
		$setsql = substr($setsql, 0, -2);

		$sql = "UPDATE ".$table.$setsql.$wheresql;
		$sql = str_replace("\\","\\\\",$sql);  // notwendig, damit TEX-Kommandos erhalten bleiben
		mysqli_query ($dbcon, $sql);
	}

	if(isset($_GET["SaveLine"]))
	{	
		$saveLine = $_GET["SaveLine"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
			SaveLine($dbcon, $table, $saveLine);
	}

	if(isset($_GET["DelTable"]) && $db != "")
	{
		$delTable = $_GET["DelTable"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "DROP TABLE ".$delTable;
			if(!mysqli_query ($dbcon, $sql))
				echo $delTable." konnte nicht gelöscht werden. Lösche zuerst abhängige Tabellen.<br>";
		}
		mysqli_close($dbcon);
	}

?>
<!DOCTYPE html>
<html lang="de">
	<head>
		<link rel="stylesheet" href="Standard.css">
		<title>Datenbankverwaltung</title>
	</head>
	<body>
		<header>
			<h1>Datenbankverwaltung</h1>
			<nav>
				MySql-Datenbanken: 
<?PHP 
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, "information_schema");
		if (mysqli_connect_errno()==0)
		{
			$sql = "SELECT schema_name FROM schemata";
			$dbs = mysqli_query ($dbcon, $sql);
			while($ddb = mysqli_fetch_array($dbs))
			{
				if($ddb[0] == "information_schema" || $ddb[0] == "mysql" || $ddb[0] == "phpmyadmin" || $ddb[0] == "performance_schema" ) continue;
				if($ddb[0] == $db) 
					echo "<a href='Index.php?Db=".$ddb[0]."'><font color = 'red'>".$ddb[0] ."</font></a> ";
				else
					echo "<a href='Index.php?Db=".$ddb[0]."'>".$ddb[0] ."</a> ";
			}
		}
		mysqli_close($dbcon);
?>
				<form action="index.php"><input type="submit" value="Datenbank erstellen: "><input type="text" name="CreateDb" size="30"></form>
				<form action="index.php?DelDb=True" method="POST"><input type="submit" value="Aktuelle Datenbank l&ouml;schen"></form>
			</nav>
		</header>
		<aside>
			<h2>Tabellen</h2>
			<p>
<?PHP 
	if($db != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SHOW TABLES";
			$tables = mysqli_query ($dbcon, $sql);
			while($ttable = mysqli_fetch_array($tables))
			{
				echo "<a href='Index.php?DelTable=".$ttable[0]."'><img src='Delete.png'></a>&nbsp;";
				if($ttable[0] == $table) 
					echo "<a href='Index.php?Table=".$ttable[0]."'><font color = 'red'>".$ttable[0] ."</font></a><br>";
				else
					echo "<a href='Index.php?Table=".$ttable[0]."'>".$ttable[0] ."</a><br>";
			}
		}
		mysqli_close($dbcon);
	}
?>			
			</p>
		</aside>
		<article>
			<h2><?php echo $table;?></h2>
			<section>
<?PHP 
	if($db != "" && $table != "" )
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SHOW COLUMNS FROM ".$table;
			$cols = mysqli_query ($dbcon, $sql);
			$nrCols=0;
			echo "<table><tr>";
			while($col = mysqli_fetch_array($cols))
			{
				$fname = ($col[3]=="PRI")? "<font color = 'red'>".$col[0] ."</font>": $col[0];
				echo "<th>".$fname."</th>";
				$nrCols++;
			}
			echo "</tr><tr>";
			echo "<form action='Index.php?Table=".$table."' method='POST'>";

			for($c=0; $c<$nrCols; $c++)
			{
				$filter[] = "";
				$filtername = "Cond".$c;
				if(isset($_SESSION["$filtername"])) $filter[$c] = $_SESSION["$filtername"];
				if(isset($_POST["$filtername"]))
				{
					$filter[$c] = $_POST["$filtername"];
					$_SESSION["$filtername"] = $filter[$c];
				}					
				echo "<td><input class='filter' type='text' name='Cond".$c."' size='8' value='".$filter[$c]."'></td>";
			}
			echo "<td><input type='submit' value='Apply'></td></form><td>";
		
			echo "</tr><tr>";
				
			$MAXLINES = 100;
			$nrlines = 0;
			$sql = "SELECT * FROM ".$table;
			$rows = mysqli_query ($dbcon, $sql);
			$rowNr = 0;
			while($row = mysqli_fetch_array($rows))
			{
				$showLine=true;
				for($c=0; $c<$nrCols; $c++)
				{
					if($filter[$c] != "" && $filter[$c] != $row[$c]) $showLine=false;
				}
				if($showLine && $nrlines<$MAXLINES)
				{
					echo "<form action='Index.php?Table=".$table."&SaveLine=".$rowNr."' method='POST'>";
					for($c=0; $c<$nrCols; $c++)
						echo "<td><input type='text' name='C".$c."' size='8' value='".$row[$c]."'></td>";
					echo "<td><input type='submit' value='Save'></td></form><td><a href='Index.php?Table=".$table."&DelLine=".$rowNr."'><img src='Delete.png'></a></td>";
					echo "</tr>";
					$nrlines++;
				}
				$rowNr++;
			}
			echo "<tr><form action='Index.php?Table=".$table."&AddLine=true' method='POST'>";
			for($c=0; $c<$nrCols; $c++)
				echo "<td><input type='text' name='C".$c."' size='8'></td>";
			echo 	"<td><input type='submit' value='Add'></td>";
			echo "</form></tr>";
			echo "</table>";
		}
		mysqli_close($dbcon);
	}
?>			
			</section>
		</article>
		<aside>
			<h2>Aktionen</h2>
			<p>
				<a href='TableDesign.php<?php if($table != "") echo "?Table=".$table;?>'>Tabellen-Design</a><br>
				<a href="SelectData.php">Select-Abfragen</a><br>
				<a href="ExecuteSql.php">Sql-Statements</a><br>
			</p>
		</aside>
		<footer>
			<p>&copy; 2020 by L&uuml;der Reimers</p>
		</footer>  
	</body>
</html>