311 lines
7.5 KiB
PHP
311 lines
7.5 KiB
PHP
|
<!DOCTYPE html>
|
||
|
|
<html lang="en">
|
||
|
|
<head>
|
||
|
|
<meta charset="UTF-8">
|
||
|
|
<title>Create System Tables</title>
|
||
|
|
|
||
|
|
<style>
|
||
|
|
body {
|
||
|
|
background-color: #333;
|
||
|
|
color: white;
|
||
|
|
font-family: sans-serif;
|
||
|
|
padding: 1em;
|
||
|
|
}
|
||
|
|
|
||
|
|
.log {
|
||
|
|
margin-bottom: 1em;
|
||
|
|
}
|
||
|
|
|
||
|
|
.success {
|
||
|
|
color: #8fd19e;
|
||
|
|
}
|
||
|
|
|
||
|
|
.warning {
|
||
|
|
color: #ffd966;
|
||
|
|
}
|
||
|
|
|
||
|
|
.error {
|
||
|
|
color: #ff9999;
|
||
|
|
}
|
||
|
|
</style>
|
||
|
|
</head>
|
||
|
|
<body>
|
||
|
|
|
||
|
|
<?php
|
||
|
|
|
||
|
|
define("INITIALIZATION_FILE_NAME", "../xml/settings.xml");
|
||
|
|
define("INIT_SCRIPT_USER", "Init Script");
|
||
|
|
|
||
|
|
// Safety switch.
|
||
|
|
// Change this to true when you intentionally want to rebuild system tables.
|
||
|
|
$allow_rebuild = true;
|
||
|
|
|
||
|
|
if (!$allow_rebuild) {
|
||
|
|
echoLog("Rebuild is disabled. Set \$allow_rebuild to true to continue.", "warning");
|
||
|
|
exit();
|
||
|
|
}
|
||
|
|
|
||
|
|
// --------------------------
|
||
|
|
// Load Application Settings
|
||
|
|
// --------------------------
|
||
|
|
|
||
|
|
$applicationSettings = simplexml_load_file(INITIALIZATION_FILE_NAME);
|
||
|
|
|
||
|
|
if (empty($applicationSettings)) {
|
||
|
|
echoLog("Unable to load Application Settings.", "error");
|
||
|
|
exit();
|
||
|
|
}
|
||
|
|
|
||
|
|
// ---------------------
|
||
|
|
// Database Connection
|
||
|
|
// ---------------------
|
||
|
|
|
||
|
|
$host = (string) $applicationSettings->DatabaseServer;
|
||
|
|
$database = (string) $applicationSettings->DatabaseName;
|
||
|
|
$user = (string) $applicationSettings->DatabaseUser;
|
||
|
|
$password = (string) $applicationSettings->DatabasePassword;
|
||
|
|
|
||
|
|
try {
|
||
|
|
$connection = new PDO(
|
||
|
|
"mysql:host={$host};dbname={$database};charset=utf8mb4",
|
||
|
|
$user,
|
||
|
|
$password,
|
||
|
|
[
|
||
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
||
|
|
PDO::ATTR_EMULATE_PREPARES => false,
|
||
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
||
|
|
]
|
||
|
|
);
|
||
|
|
|
||
|
|
$connection->exec("SET FOREIGN_KEY_CHECKS = 0");
|
||
|
|
|
||
|
|
} catch (PDOException | Exception $e) {
|
||
|
|
echoLog("Unable to connect to the database.", "error");
|
||
|
|
exit();
|
||
|
|
}
|
||
|
|
|
||
|
|
// ----------------------------
|
||
|
|
// Get Table Definition Files
|
||
|
|
// ----------------------------
|
||
|
|
|
||
|
|
$tableFiles = glob("*.php");
|
||
|
|
|
||
|
|
$excludedFiles = [
|
||
|
|
"_createSystemTable.php",
|
||
|
|
"_createTable.php",
|
||
|
|
basename(__FILE__),
|
||
|
|
];
|
||
|
|
|
||
|
|
$tableFiles = array_values(array_filter($tableFiles, function ($file) use ($excludedFiles) {
|
||
|
|
return !in_array($file, $excludedFiles, true);
|
||
|
|
}));
|
||
|
|
|
||
|
|
sort($tableFiles);
|
||
|
|
|
||
|
|
// ----------------------------
|
||
|
|
// Process Table Files
|
||
|
|
// ----------------------------
|
||
|
|
|
||
|
|
foreach ($tableFiles as $tableFile) {
|
||
|
|
|
||
|
|
$tableName = basename($tableFile, ".php");
|
||
|
|
|
||
|
|
echoLog("Processing {$tableName}...", "warning");
|
||
|
|
|
||
|
|
try {
|
||
|
|
$connection->exec("DROP VIEW IF EXISTS `view_{$tableName}`");
|
||
|
|
echoLog("Dropped view_{$tableName}, if it existed.", "success");
|
||
|
|
} catch (PDOException $e) {
|
||
|
|
echoLog("Could not drop view_{$tableName}.", "error");
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
|
||
|
|
try {
|
||
|
|
$connection->exec("DROP TABLE IF EXISTS `{$tableName}`");
|
||
|
|
echoLog("Dropped {$tableName}, if it existed.", "success");
|
||
|
|
} catch (PDOException $e) {
|
||
|
|
echoLog("Could not drop table {$tableName}.", "error");
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
|
||
|
|
$SQL = null;
|
||
|
|
|
||
|
|
if (!include $tableFile) {
|
||
|
|
echoLog("Failed to include {$tableFile}. Skipping.", "error");
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
|
||
|
|
if (empty($SQL)) {
|
||
|
|
echoLog("No SQL found in {$tableFile}. Skipping.", "error");
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
|
||
|
|
runSQL($SQL, $connection, "create {$tableName}");
|
||
|
|
|
||
|
|
switch ($tableFile) {
|
||
|
|
|
||
|
|
case "popovers.php":
|
||
|
|
seedPopovers($connection);
|
||
|
|
break;
|
||
|
|
|
||
|
|
case "users.php":
|
||
|
|
seedInitialAdminUser($connection);
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
try {
|
||
|
|
$connection->exec("SET FOREIGN_KEY_CHECKS = 1");
|
||
|
|
} catch (PDOException $e) {
|
||
|
|
echoLog("Warning: Could not re-enable foreign key checks.", "warning");
|
||
|
|
}
|
||
|
|
|
||
|
|
echoLog("System tables created successfully.", "success");
|
||
|
|
|
||
|
|
// -------------------------
|
||
|
|
// Helper Functions
|
||
|
|
// -------------------------
|
||
|
|
|
||
|
|
function runSQL($SQL, PDO $connection, $context = "query") {
|
||
|
|
try {
|
||
|
|
$connection->exec($SQL);
|
||
|
|
echoLog("Completed {$context}.", "success");
|
||
|
|
} catch (PDOException $e) {
|
||
|
|
echoLog("Error running {$context}.", "error");
|
||
|
|
exit();
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
function seedPopovers(PDO $connection) {
|
||
|
|
|
||
|
|
$popovers = [
|
||
|
|
[
|
||
|
|
"find_user",
|
||
|
|
"Find User",
|
||
|
|
"Find User...",
|
||
|
|
"<p>Find by Name, Email, or Role</p>"
|
||
|
|
],
|
||
|
|
[
|
||
|
|
"find_report",
|
||
|
|
"Find Report",
|
||
|
|
"Find Report...",
|
||
|
|
"<p>Find by Name or Description</p>"
|
||
|
|
],
|
||
|
|
[
|
||
|
|
"select_report",
|
||
|
|
"Select Report",
|
||
|
|
"Find Report...",
|
||
|
|
"<p>Find by Name, Email, or Role</p>"
|
||
|
|
],
|
||
|
|
[
|
||
|
|
"find_popover",
|
||
|
|
"Find Popover",
|
||
|
|
"Find Popover...",
|
||
|
|
"<p>Find by Name, Title or Placeholder</p>"
|
||
|
|
],
|
||
|
|
[
|
||
|
|
"find_journal",
|
||
|
|
"Find Journal",
|
||
|
|
"Find Journal...",
|
||
|
|
"<p>Find by Name, Email, or Role</p>"
|
||
|
|
],
|
||
|
|
[
|
||
|
|
"find_status",
|
||
|
|
"Find Status",
|
||
|
|
"Find Status...",
|
||
|
|
"<p>Find by Name</p>"
|
||
|
|
],
|
||
|
|
[
|
||
|
|
"find_dropdowntype",
|
||
|
|
"Find Dropdown Types",
|
||
|
|
"Find Dropdown Types...",
|
||
|
|
"<p>Search for dropdown types</p>"
|
||
|
|
],
|
||
|
|
];
|
||
|
|
|
||
|
|
$SQL = "
|
||
|
|
INSERT INTO popovers (
|
||
|
|
popover_name,
|
||
|
|
popover_title,
|
||
|
|
popover_placeholder,
|
||
|
|
popover_text,
|
||
|
|
popover_created,
|
||
|
|
popover_creator
|
||
|
|
) VALUES (
|
||
|
|
:popover_name,
|
||
|
|
:popover_title,
|
||
|
|
:popover_placeholder,
|
||
|
|
:popover_text,
|
||
|
|
CURDATE(),
|
||
|
|
:popover_creator
|
||
|
|
)
|
||
|
|
";
|
||
|
|
|
||
|
|
$statement = $connection->prepare($SQL);
|
||
|
|
|
||
|
|
foreach ($popovers as $popover) {
|
||
|
|
$statement->execute([
|
||
|
|
":popover_name" => $popover[0],
|
||
|
|
":popover_title" => $popover[1],
|
||
|
|
":popover_placeholder" => $popover[2],
|
||
|
|
":popover_text" => $popover[3],
|
||
|
|
":popover_creator" => INIT_SCRIPT_USER,
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
|
||
|
|
echoLog("Seeded popovers.", "success");
|
||
|
|
}
|
||
|
|
|
||
|
|
function seedInitialAdminUser(PDO $connection) {
|
||
|
|
|
||
|
|
$password = password_hash("349Jamot@", PASSWORD_DEFAULT);
|
||
|
|
|
||
|
|
$SQL = "
|
||
|
|
INSERT INTO users (
|
||
|
|
user_id,
|
||
|
|
user_name,
|
||
|
|
user_email,
|
||
|
|
user_password,
|
||
|
|
user_role,
|
||
|
|
user_change_password,
|
||
|
|
user_active,
|
||
|
|
user_creator,
|
||
|
|
user_created
|
||
|
|
) VALUES (
|
||
|
|
:user_id,
|
||
|
|
:user_name,
|
||
|
|
:user_email,
|
||
|
|
:user_password,
|
||
|
|
:user_role,
|
||
|
|
:user_change_password,
|
||
|
|
:user_active,
|
||
|
|
:user_creator,
|
||
|
|
CURDATE()
|
||
|
|
)
|
||
|
|
";
|
||
|
|
|
||
|
|
$statement = $connection->prepare($SQL);
|
||
|
|
|
||
|
|
$statement->execute([
|
||
|
|
":user_id" => "DSYSADMIN",
|
||
|
|
":user_name" => "James Richie",
|
||
|
|
":user_email" => "james.richie@onesourceits.com",
|
||
|
|
":user_password" => $password,
|
||
|
|
":user_role" => "Administrator",
|
||
|
|
":user_change_password" => 1,
|
||
|
|
":user_active" => 1,
|
||
|
|
":user_creator" => INIT_SCRIPT_USER,
|
||
|
|
]);
|
||
|
|
|
||
|
|
echoLog("Seeded initial administrator user.", "success");
|
||
|
|
}
|
||
|
|
|
||
|
|
function echoLog($message, $type = "") {
|
||
|
|
$class = trim("log {$type}");
|
||
|
|
echo "<div class='{$class}'>" . htmlspecialchars($message, ENT_QUOTES, "UTF-8") . "</div>";
|
||
|
|
}
|
||
|
|
|
||
|
|
?>
|
||
|
|
|
||
|
|
</body>
|
||
|
|
</html>
|