<?php
/*
    dbadmin.php
    OpenDRS Online Discrepancy Reporting System
    Copyright (C) 2020  Rod Wright

    SPDX-License-Identifier: GPL-2.0
*/

include("common.php");

// redirect to index.php on cancel button press
if ($_REQUEST['cancel']) {
    header("Cache-Control:no-cache, must-revalidate");
    header("Pragma:no-cache");
    header("Location:index.php");
    exit();
}

// import session variables
if (isset($_SESSION['userid'])) $userid=$_SESSION['userid'];
$role=dblookup($drs_db,"users","id","role",$userid);

// redirect to index.php if not an admin
if ($role != ADMIN) {
    header("Cache-Control:no-cache, must-revalidate");
    header("Pragma:no-cache");
    header("Location:index.php");
    exit();
}

// import incoming arrays
$print=$_REQUEST['print'];
if ($print) {
    // if printer friendly was clicked, values will be passed in serialized
    // form as "all_parameters" so we need to load those into $incoming[]
    $incoming=unserialize($_REQUEST['all_parameters']);
} else {
    // copy $_REQUEST[] to $incoming[] 
    $incoming=arrayCopy($_REQUEST);
}

// load variables from incoming array
$edit_user=$incoming['edit_user'];
$add_user=$incoming['add_user'];
$update_user=$incoming['update_user'];
$delete_user=$incoming['delete_user'];
$user_id=$incoming['user_id'];
$user_fname=$incoming['user_fname'];
$user_lname=$incoming['user_lname'];
$user_login=$incoming['user_login'];
$user_pass=$incoming['user_pass'];
$user_passconf=$incoming['user_passconf'];
$user_role=$incoming['user_role'];
$user_active=$incoming['user_active'];

$edit_device=$incoming['edit_device'];
$add_device=$incoming['add_device'];
$update_device=$incoming['update_device'];
$delete_device=$incoming['delete_device'];
$device_id=$incoming['device_id'];
$device_name=$incoming['device_name'];
$device_active=$incoming['device_active'];

$edit_subsystem=$incoming['edit_subsystem'];
$add_subsystem=$incoming['add_subsystem'];
$update_subsystem=$incoming['update_subsystem'];
$delete_subsystem=$incoming['delete_subsystem'];
$subsystem_id=$incoming['subsystem_id'];
$subsystem_name=$incoming['subsystem_name'];
$subsystem_desc=$incoming['subsystem_desc'];
$subsystem_active=$incoming['subsystem_active'];

$edit_reason=$incoming['edit_reason'];
$add_reason=$incoming['add_reason'];
$update_reason=$incoming['update_reason'];
$delete_reason=$incoming['delete_reason'];
$reason_id=$incoming['reason_id'];
$reason_text=$incoming['reason_text'];
$reason_active=$incoming['reason_active'];

$edit_period=$incoming['edit_period'];
$add_period=$incoming['add_period'];
$update_period=$incoming['update_period'];
$delete_period=$incoming['delete_period'];
$period_id=$incoming['period_id'];
$period_times=$incoming['period_times'];
$period_active=$incoming['period_active'];

// define local functions


// assign variables from constants
$appname=APP_NAME;

if ($print=="Printer Friendly") {
    $mbgcolor=P_MENUBG_COLOR;
} else {
    $mbgcolor=MENUBG_COLOR;
}

framework("begin","$appname","Database Administration",$print);

//var_dump($_REQUEST);
//var_dump($incoming);

// ******** begin database manipulation ********

// users-------------------------------------
if ($add_user) {
    // do error checking
    // remove html tags from user name
    $user_fname=strip_tags($user_fname);
    $user_lname=strip_tags($user_lname);
    $user_login=strip_tags($user_login);
    // test for first name supplied
    if (strlen(trim($user_fname))) {
        $fnamesuppld=true;
    } else {
        $fnamesuppld=false;
        $fnamesuppld_err=true;
    }
    // test for last name supplied
    if (strlen(trim($user_lname))) {
        $lnamesuppld=true;
    } else {
        $lnamesuppld=false;
        $lnamesuppld_err=true;
    }
    // test for name unique
    if (mysqli_num_rows(mysqli_query($drs_db,"select id from users where firstname=\"$user_fname\" and lastname=\"$user_lname\""))) {
        $usernameunique=false;
        $usernameunique_err=true;
    } else {
        $usernameunique=true;
    }
    // test for login supplied
    if (strlen(trim($user_login))) {
        $loginsuppld=true;
    } else {
        $loginsuppld=false;
        $loginsuppld_err=true;
    }
    // test for login unique
    if (mysqli_num_rows(mysqli_query($drs_db,"select id from users where login=\"$user_login\""))) {
        $loginunique=false;
        $loginunique_err=true;
    } else {
        $loginunique=true;
    }
    // test passwords
    $passresults=validate_password($user_pass,$user_passconf);
    if ($passresults['match']) {
        $passmatch=true;
    } else {
        $passmatch=false;
        $passmatch_err=true;
    }
    if ($passresults['complex']) {
        $passcomplex=true;
    } else {
        $passcomplex=false;
        $passcomplex_err=true;
    }
    
    // set role
    if (!$user_role) $user_role=USER;
    
    // set active status
    if (!$user_active) $user_active=0;
        
    if ($fnamesuppld && $lnamesuppld && $usernameunique && $loginsuppld && $loginunique && $passcomplex && $passmatch) {
        // sanitize first name, last name, login
        $clean_fname=mysqli_real_escape_string($drs_db,$user_fname);
        $clean_lname=mysqli_real_escape_string($drs_db,$user_lname);
        $clean_login=mysqli_real_escape_string($drs_db,$user_login);
        // generate the password hash
        $passhash=password_hash($user_pass,PASSWORD_DEFAULT);
        // modify the table
        mysqli_query($drs_db,"insert into users(firstname,lastname,login,password_hash,role,active) values(\"$clean_fname\",\"$clean_lname\",\"$clean_login\",\"$passhash\",\"$user_role\",\"$user_active\")");
    } else {
        $user_add_fail=true;
    }
}

if ($edit_user) {
    if ($delete_user) {
        // check for actions by this user
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from writeups where action_by=\"$user_id\""))) {
            $userhaswriteups=true;
            $userhaswriteups_err=true;
        } else {
            $userhaswriteups=false;
        }
        // if none, then remove from db
        if (!$userhaswriteups) mysqli_query($drs_db,"delete from users where id=\"$user_id\"");
    }
    if ($update_user) {
        // do error checking
        // remove html tags from user name
        $user_fname=strip_tags($user_fname);
        $user_lname=strip_tags($user_lname);
        $user_login=strip_tags($user_login);
        // test for first name supplied
        if (strlen(trim($user_fname))) {
            $fnamesuppld=true;
        } else {
            $fnamesuppld=false;
            $fnamesuppld_err=true;
        }
        // test for last name supplied
        if (strlen(trim($user_lname))) {
            $lnamesuppld=true;
        } else {
            $lnamesuppld=false;
            $lnamesuppld_err=true;
        }
        // test for name unique
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from users where firstname=\"$user_fname\" and lastname=\"$user_lname\" and id!=\"$user_id\""))) {
            if (mysqli_num_rows(mysqli_query($drs_db,"select id from users where firstname=\"$user_fname\" and lastname=\"$user_lname\""))) {
            $nameunique=false;
            $nameunique_err=true;
            } else {
                $nameunique=true;
            }
        } else {
            $nameunique=true;
        }
        // test for login supplied
        if (strlen(trim($user_login))) {
            $loginsuppld=true;
        } else {
            $loginsuppld=false;
            $loginsuppld_err=true;
        }
        // test for login unique
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from users where login=\"$user_login\" and id!=\"$user_id\""))) {
            if (mysqli_num_rows(mysqli_query($drs_db,"select id from users where login=\"$user_login\""))) {
                $loginunique=false;
                $loginunique_err=true;
            } else {
                $loginunique=true;
            }
        } else {
            $loginunique=true;
        }
        // sanitize first name, last name, login
        $clean_fname=mysqli_real_escape_string($drs_db,$user_fname);
        $clean_lname=mysqli_real_escape_string($drs_db,$user_lname);
        $clean_login=mysqli_real_escape_string($drs_db,$user_login);
        
        // set role
        if (!$user_role) $user_role=USER;
        
        // set active status
        if (!$user_active) $user_active=0;
        
        if ($user_pass!="password_is_unchanged") {
            // test passwords
            $passresults=validate_password($user_pass,$user_passconf);
            if ($passresults['match']) {
                $passmatch=true;
            } else {
                $passmatch=false;
                $passmatch_err=true;
            }
            if ($passresults['complex']) {
                $passcomplex=true;
            } else {
                $passcomplex=false;
                $passcomplex_err=true;
            }
            if ($passmatch && $passcomplex) {
                $passhash=password_hash($user_pass,PASSWORD_DEFAULT);
                $updqry="update users set firstname=\"$clean_fname\",lastname=\"$clean_lname\",login=\"$clean_login\",password_hash=\"$passhash\",role=\"$user_role\",active=\"$user_active\" where id=\"$user_id\"";
            }
        } else {
            $passmatch=true;
            $passcomplex=true;
            $updqry="update users set firstname=\"$clean_fname\",lastname=\"$clean_lname\",login=\"$clean_login\",role=\"$user_role\",active=\"$user_active\" where id=\"$user_id\"";
        }
        if ($fnamesuppld && $lnamesuppld && $nameunique && $loginsuppld && $loginunique && $passcomplex && $passmatch) {
            // modify the table
            mysqli_query($drs_db,$updqry);
        }
    }
}

// devices-------------------------------
if ($add_device) {
    // do error checking
    // remove html tags from device name
    $device_name=strip_tags($device_name);
    // test for device name supplied
    if (strlen(trim($device_name))) {
        $devicenamesuppld=true;
    } else {
        $devicenamesuppld=false;
        $devicenamesuppld_err=true;
    }
    // test for name unique
    if (mysqli_num_rows(mysqli_query($drs_db,"select id from devices where name=\"$device_name\""))) {
        $devicenameunique=false;
        $devicenameunique_err=true;
    } else {
        $devicenameunique=true;
    }
    
    // set active status
    if (!$device_active) $device_active=0;
    
    if ($devicenamesuppld && $devicenameunique) {
        // sanitize device name
        $clean_devicename=mysqli_real_escape_string($drs_db,$device_name);
        // modify the table
        mysqli_query($drs_db,"insert into devices(name,active) values(\"$clean_devicename\",\"$device_active\")");
    } else {
        $device_add_fail=true;
    }
}
if ($edit_device) {
    if ($delete_device) {
        // check for writeups on this device
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from writeups where device=\"$device_id\""))) {
            $devicehaswriteups=true;
            $devicehaswriteups_err=true;
        } else {
            $devicehaswriteups=false;
        }
        // if none, then remove from db
        if (!$devicehaswriteups) mysqli_query($drs_db,"delete from devices where id=\"$device_id\"");
    }
    if ($update_device) {
        // do error checking
        // remove html tags from device name
        $device_name=strip_tags($device_name);
        // test for device name supplied
        if (strlen(trim($device_name))) {
            $devicenamesuppld=true;
        } else {
            $devicenamesuppld=false;
            $devicenamesuppld_err=true;
        }
        // test for device name unique
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from devices where name=\"$device_name\" and id!=\"$device_id\""))) {
            if (mysqli_num_rows(mysqli_query($drs_db,"select id from devices where name=\"$device_name\""))) {
            $devicenameunique=false;
            $devicenameunique_err=true;
            } else {
                $devicenameunique=true;
            }
        } else {
            $devicenameunique=true;
        }
        
        // set active status
        if (!$user_active) $user_active=0;
        
        // sanitize device name
        $clean_devicename=mysqli_real_escape_string($drs_db,$device_name);
        $updqry="update devices set name=\"$clean_devicename\",active=\"$device_active\" where id=\"$device_id\"";
        if ($devicenamesuppld && $devicenameunique) {
            // modify the table
            mysqli_query($drs_db,$updqry);
        }
    }
}

// subsystems--------------------------------
if ($add_subsystem) {
    // do error checking
    // remove html tags from subsystem name and description
    $subsystem_name=strip_tags($subsystem_name);
    $subsystem_desc=strip_tags($subsystem_desc);
    // test for subsystem name supplied
    if (strlen(trim($subsystem_name))) {
        $ssnamesuppld=true;
    } else {
        $ssnamesuppld=false;
        $ssnamesuppld_err=true;
    }
    // test for name unique
    if (mysqli_num_rows(mysqli_query($drs_db,"select id from subsystems where name=\"$subsystem_name\""))) {
        $ssnameunique=false;
        $ssnameunique_err=true;
    } else {
        $ssnameunique=true;
    }
    
    // set active status
    if (!$subsystem_active) $subsystem_active=0;
    
    if ($ssnamesuppld && $ssnameunique) {
        // sanitize subsystem name and description
        $clean_subsystemname=mysqli_real_escape_string($drs_db,$subsystem_name);
        $clean_subsystemdesc=mysqli_real_escape_string($drs_db,$subsystem_desc);
        // modify the table
        mysqli_query($drs_db,"insert into subsystems(name,description,active) values(\"$clean_subsystemname\",\"$clean_subsystemdesc\",\"$subsystem_active\")");
    } else {
        $subsystem_add_fail=true;
    }
}
if ($edit_subsystem) {
    if ($delete_subsystem) {
        // check for writeups on this subsystem
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from writeups where subsystem=\"$subsystem_id\""))) {
            $sshaswriteups=true;
            $sshaswriteups_err=true;
        } else {
            $sshaswriteups=false;
        }
        // if none, then remove from db
        if (!$sshaswriteups) mysqli_query($drs_db,"delete from subsystems where id=\"$subsystem_id\"");
    }
    if ($update_subsystem) {
        // do error checking
        // remove html tags from subsystem name and description
        $subsystem_name=strip_tags($subsystem_name);
        $subsystem_desc=strip_tags($subsystem_desc);
        // test for subsystem name supplied
        if (strlen(trim($subsystem_name))) {
            $ssnamesuppld=true;
        } else {
            $ssnamesuppld=false;
            $ssnamesuppld_err=true;
        }
        // test for subsystem name unique
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from subsystems where name=\"$subsystem_name\" and id!=\"$subsystem_id\""))) {
            if (mysqli_num_rows(mysqli_query($drs_db,"select id from subsystems where name=\"$subsystem_name\""))) {
            $ssnameunique=false;
            $ssnameunique_err=true;
            } else {
                $ssnameunique=true;
            }
        } else {
            $ssnameunique=true;
        }
        // sanitize subsystem name and description
        $clean_ssname=mysqli_real_escape_string($drs_db,$subsystem_name);
        $clean_ssdesc=mysqli_real_escape_string($drs_db,$subsystem_desc);
        $updqry="update subsystems set name=\"$clean_ssname\",description=\"$clean_ssdesc\",active=\"$subsystem_active\" where id=\"$subsystem_id\"";
        if ($ssnamesuppld && $ssnameunique) {
            // modify the table
            mysqli_query($drs_db,$updqry);
        }
    }
}

// reasons------------------------------------
if ($add_reason) {
    // do error checking
    // remove html tags from reason text
    $reason_text=strip_tags($reason_text);
    // test for reason text supplied
    if (strlen(trim($reason_text))) {
        $reastextsuppld=true;
    } else {
        $reastextsuppld=false;
        $reastextsuppld_err=true;
    }
    // test for text unique
    if (mysqli_num_rows(mysqli_query($drs_db,"select id from reasons where text=\"$reason_text\""))) {
        $reastextunique=false;
        $reastextunique_err=true;
    } else {
        $reastextunique=true;
    }
    
    // set active status
    if (!$reason_active) $reason_active=0;
    
    if ($reastextsuppld && $reastextunique) {
        // sanitize reason text
        $clean_reastext=mysqli_real_escape_string($drs_db,$reason_text);
        // modify the table
        mysqli_query($drs_db,"insert into reasons(text,active) values(\"$clean_reastext\",\"$reason_active\")");
    } else {
        $reason_add_fail=true;
    }
}
if ($edit_reason) {
    if ($delete_reason) {
        // check for writeups with this reason
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from writeups where reason=\"$reason_id\""))) {
            $reashaswriteups=true;
            $reashaswriteups_err=true;
        } else {
            $reashaswriteups=false;
        }
        // if none, then remove from db
        if (!$reashaswriteups) mysqli_query($drs_db,"delete from reasons where id=\"$reason_id\"");
    }
    if ($update_reason) {
        // do error checking
        // remove html tags from reason text
        $reason_text=strip_tags($reason_text);
        // test for reason text supplied
        if (strlen(trim($reason_text))) {
            $reastextsuppld=true;
        } else {
            $reastextsuppld=false;
            $reastextsuppld_err=true;
        }
        // test for reason text unique
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from reasons where text=\"$reason_text\" and id!=\"$reason_id\""))) {
            if (mysqli_num_rows(mysqli_query($drs_db,"select id from reasons where text=\"$reason_text\""))) {
            $reastextunique=false;
            $reastextunique_err=true;
            } else {
                $reastextunique=true;
            }
        } else {
            $reastextunique=true;
        }
        
        // set active status
        if (!$reason_active) $reason_active=0;
        
        // sanitize reason text
        $clean_reastext=mysqli_real_escape_string($drs_db,$reason_text);
        $updqry="update reasons set text=\"$clean_reastext\",active=\"$reason_active\" where id=\"$reason_id\"";
        if ($reastextsuppld && $reastextunique) {
            // modify the table
            mysqli_query($drs_db,$updqry);
        }
    }
}

// periods-----------------------------------
if ($add_period) {
    // do error checking
    // remove html tags from id and times
    $period_id=strip_tags($period_id);
    $period_times=strip_tags($period_times);
    // test for id supplied
    if (strlen(trim($period_id))) {
        $peridsuppld=true;
    } else {
        $peridsuppld=false;
        $peridsuppld_err=true;
    }
    // test for id is integer
    if (preg_match('/^[0-9]+$/',$period_id)) {
        $peridisint=true;
    } else {
        $peridisint=false;
        $peridisint_err=true;
    }
    // test for id unique
    if (mysqli_num_rows(mysqli_query($drs_db,"select id from periods where id=\"$period_id\""))) {
        $peridunique=false;
        $peridunique_err=true;
    } else {
        $peridunique=true;
    }
    // test for times supplied
    if (strlen(trim($period_times))) {
        $pertimessuppld=true;
    } else {
        $pertimessuppld=false;
        $pertimessuppld_err=true;
    }
    // test for times unique
    if (mysqli_num_rows(mysqli_query($drs_db,"select id from periods where times=\"$period_times\""))) {
        $pertimesunique=false;
        $pertimesunique_err=true;
    } else {
        $pertimesunique=true;
    }
    
    // set active status
    if (!$period_active) $period_active=0;
    
    if ($pertimessuppld && $pertimesunique && $peridsuppld && $peridunique && $peridisint) {
        // sanitize times
        $clean_pertimes=mysqli_real_escape_string($drs_db,$period_times);
        // modify the table
        mysqli_query($drs_db,"insert into periods(id,times,active) values(\"$period_id\",\"$clean_pertimes\",\"$period_active\")");
    } else {
        $period_add_fail=true;
    }
}
if ($edit_period) {
    if ($delete_period) {
        // check for writeups with this period
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from writeups where period=\"$period_id\""))) {
            $perhaswriteups=true;
            $perhaswriteups_err=true;
        } else {
            $perhaswriteups=false;
        }
        // if none, then remove from db
        if (!$perhaswriteups) mysqli_query($drs_db,"delete from periods where id=\"$period_id\"");
    }
    if ($update_period) {
        // do error checking
        // remove html tags from times
        $period_times=strip_tags($period_times);
        // test for times supplied
        if (strlen(trim($period_times))) {
            $pertimessuppld=true;
        } else {
            $pertimessuppld=false;
            $pertimessuppld_err=true;
        }
        // test for period times unique
        if (mysqli_num_rows(mysqli_query($drs_db,"select id from periods where times=\"$period_times\" and id!=\"$period_id\""))) {
            if (mysqli_num_rows(mysqli_query($drs_db,"select id from periods where times=\"$period_times\""))) {
            $pertimesunique=false;
            $pertimesunique_err=true;
            } else {
                $pertimesunique=true;
            }
        } else {
            $pertimesunique=true;
        }
        
        // set active status
        if (!$period_active) $period_active=0;
        
        // sanitize times
        $clean_pertimes=mysqli_real_escape_string($drs_db,$period_times);
        $updqry="update periods set times=\"$clean_pertimes\",active=\"$period_active\" where id=\"$period_id\"";
        if ($pertimessuppld && $pertimesunique) {
            // modify the table
            mysqli_query($drs_db,$updqry);
        }
    }
}

// ******** end database manipulation ********

pagetable("begin");
if (!$print) {
    pageblock("left","begin");
    sidemenu();
    pageblock("left","end");
}
pageblock("right","begin");
banner($print);
echo "<br>";
echo "
<div id=\"dbadmintabs\">
<ul>
<li><a href=\"#users\">User Management</a></li>
<li><a href=\"#devices\">Device Management</a></li>
<li><a href=\"#subsystems\">Subsystem Management</a></li>
<li><a href=\"#reasons\">Action Reasons Management</a></li>
<li><a href=\"#periods\">Period Management</a></li>
</ul>

<div id=\"users\">
";
if ($usernameunique_err) format_message(1,"User name already exists.");
if ($fnamesuppld_err) format_message(1,"First name cannot be blank.");
if ($lnamesuppld_err) format_message(1,"Last name cannot be blank.");
if ($loginsuppld_err) format_message(1,"Login cannot be blank.");
if ($loginunique_err) format_message(1,"Login already exists.");
if ($passcomplex_err) format_message(1,"Password doesn't meet complexity requirements.");
if ($passmatch_err) format_message(1,"The supplied passwords don't match.");
if ($userhaswriteups_err) format_message(1,"User has actions in the database and cannot be deleted.");

if ($edit_user && !$delete_user) {
    // set form options for editing
    $sectiontitle="<b><font size=\"+1\">Modify User</font></b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"dbadmin.php#users\">Click here to add a new user</a><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php?edit_user=1#users\">";
    $buttontags="
    <input type=\"hidden\" name=\"user_id\" value=\"$user_id\">
    <input type=\"submit\" name=\"update_user\" value=\"Update User\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"delete_user\" value=\"Delete User\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"reset\" value=\"Reset\"><br>
    ";
    // pull current values from database
    $currentvalues=mysqli_fetch_assoc(mysqli_query($drs_db,"select * from users where id=$user_id"));
    $user_fname=$currentvalues["firstname"];
    $user_lname=$currentvalues["lastname"];
    $user_login=$currentvalues["login"];
    $user_pass=$user_passconf="password_is_unchanged";
    if ($currentvalues["role"]==ADMIN) {
        $roletag="<input type=\"checkbox\" name=\"user_role\" value=\"2\" checked>";
    } else {
        $roletag="<input type=\"checkbox\" name=\"user_role\" value=\"2\">";
    }
    if ($currentvalues["active"]==1) {
        $activetag="<input type=\"checkbox\" name=\"user_active\" value=\"1\" checked>";
    } else {
        $activetag="<input type=\"checkbox\" name=\"user_active\" value=\"1\">";
    }
} else {
    // set form options for add
    if (!$user_add_fail) unset($user_id,$user_fname,$user_lname,$user_login,$user_pass,$user_passconf,$user_role,$user_active);
    $sectiontitle="<b><font size=\"+1\">Add New User</font></b><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php#users\">";
    $buttontags="
    <input type=\"submit\" name=\"add_user\" value=\"Add User\">
    ";
    // set default values
    $roletag="<input type=\"checkbox\" name=\"user_role\" value=\"2\">";
    $activetag="<input type=\"checkbox\" name=\"user_active\" value=\"1\" checked>";
}
$password_help="Password must be at least 8 characters in length and contain at least one upper case letter, one lower case letter, one number, and one special character.";
echo "
$sectiontitle
$formtag
<table border=\"0\">
<tr><td>
First Name: &nbsp;<input type=\"text\" name=\"user_fname\" size=\"40\" value=\"$user_fname\"><br><br>
Last Name: &nbsp;<input type=\"text\" name=\"user_lname\" size=\"40\" value=\"$user_lname\"><br><br>
Login: &nbsp;<input type=\"text\" name=\"user_login\" size=\"40\" value=\"$user_login\"><br><br>
Password: &nbsp;<input type=\"password\" name=\"user_pass\" size=\"40\" id=\"pass\" title=\"$password_help\" value=\"$user_pass\"><br><br>
Repeat Password: &nbsp;<input type=\"password\" name=\"user_passconf\" size=\"40\" id=\"passconf\" title=\"$password_help\" value=\"$user_passconf\"><br><br>
<font size=\"-1\">Need a random password? &nbsp;</font>
";
$rpwok=false;
while (!$rpwok) {
    $rpw=substr(str_shuffle(password_hash(microtime(),PASSWORD_DEFAULT)), 10, 12);
    $rpwtst=validate_password($rpw,$rpw);
    if ($rpwtst['complex']) $rpwok=true;
}
echo "&nbsp; $rpw<br><br>
<br><br>
Admin: &nbsp;$roletag&nbsp;&nbsp;&nbsp;&nbsp;
Active: &nbsp; $activetag <br><br>
</td></tr>
</table>
$buttontags
</form>
<br><hr>
";

// show the table of entities
$existingdata=mysqli_query($drs_db,"select id,firstname,lastname,login,role,active from users order by id asc");
echo "
<b>Existing Users</b>&nbsp;&nbsp;&nbsp;<font size=\"-1\">Click on the User ID to edit.</font><br><br>
<table border=\"1\">
<tr><th>User ID</th><th>First Name</th><th>Last Name</th><th>Login</th><th>Role</th><th>Active</th></tr>
";
while ($tablerow=mysqli_fetch_assoc($existingdata)) {
    if ($tablerow["role"]==ADMIN) {
        $roledef="Admin";
    } elseif ($tablerow["role"]==USER) {
        $roledef="User";
    }
    if ($tablerow["active"]) {
        $activedef="<img src=\"icons/tick.png\" title=\"active\" alt=\"active\">";
    } else {
        $activedef="<img src=\"icons/cross.png\" title=\"inactive\" alt=\"inactive\">";
    }
    printf("
    <tr>
    <td><a href=\"dbadmin.php?edit_user=1&user_id=%s#users\">%s</a></td>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>
    <td align=\"center\">%s</td>
    <td align=\"center\">%s</td>
    </tr>",
    $tablerow["id"],$tablerow["id"],
    $tablerow["firstname"],
    $tablerow["lastname"],
    $tablerow["login"],
    $roledef,
    $activedef);
}
echo "</table>";

echo "
</div>

<div id=\"devices\">
";
if ($devicenameunique_err) format_message(1,"Device name already exists.");
if ($devicenamesuppld_err) format_message(1,"Device name cannot be blank.");
if ($devicehaswriteups_err) format_message(1,"Device has writeups in the database and cannot be deleted.");

if ($edit_device && !$delete_device) {
    // set form options for editing
    $sectiontitle="<b><font size=\"+1\">Modify Device</font></b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"dbadmin.php#devices\">Click here to add a new device</a><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php?edit_device=1#devices\">";
    $buttontags="
    <input type=\"hidden\" name=\"device_id\" value=\"$device_id\">
    <input type=\"submit\" name=\"update_device\" value=\"Update Device\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"delete_device\" value=\"Delete Device\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"reset\" value=\"Reset\"><br>
    ";
    // pull current values from database
    $currentvalues=mysqli_fetch_assoc(mysqli_query($drs_db,"select * from devices where id=$device_id"));
    $device_name=$currentvalues["name"];
    if ($currentvalues["active"]==1) {
        $activetag="<input type=\"checkbox\" name=\"device_active\" value=\"1\" checked>";
    } else {
        $activetag="<input type=\"checkbox\" name=\"device_active\" value=\"1\">";
    }
} else {
    // set form options for add
    if (!$device_add_fail) unset($device_id,$device_name,$device_active);
    $sectiontitle="<b><font size=\"+1\">Add New Device</font></b><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php#devices\">";
    $buttontags="
    <input type=\"submit\" name=\"add_device\" value=\"Add Device\">
    ";
    // set default values
    $activetag="<input type=\"checkbox\" name=\"device_active\" value=\"1\" checked>";
}
echo "
$sectiontitle
$formtag
<table border=\"0\">
<tr><td>
Device Name: &nbsp;<input type=\"text\" name=\"device_name\" size=\"40\" value=\"$device_name\"><br><br>
Active: &nbsp; $activetag <br><br>
</td></tr>
</table>
$buttontags
</form>
<br><hr>
";

// show the table of entities
$existingdata=mysqli_query($drs_db,"select * from devices order by id asc");
echo "
<b>Existing Devices</b>&nbsp;&nbsp;&nbsp;<font size=\"-1\">Click on the Device ID to edit.</font><br><br>
<table border=\"1\">
<tr><th>Device ID</th><th>Device Name</th><th>Active</th></tr>
";
while ($tablerow=mysqli_fetch_assoc($existingdata)) {
    if ($tablerow["active"]) {
        $activedef="<img src=\"icons/tick.png\" title=\"active\" alt=\"active\">";
    } else {
        $activedef="<img src=\"icons/cross.png\" title=\"inactive\" alt=\"inactive\">";
    }
    printf("
    <tr>
    <td><a href=\"dbadmin.php?edit_device=1&device_id=%s#devices\">%s</a></td>
    <td>%s</td>
    <td align=\"center\">%s</td>
    </tr>",
    $tablerow["id"],$tablerow["id"],
    $tablerow["name"],
    $activedef);
}
echo "</table>";
echo "
</div>

<div id=\"subsystems\">
";
if ($ssnameunique_err) format_message(1,"Subsystem name already exists.");
if ($ssnamesuppld_err) format_message(1,"Subsystem name cannot be blank.");
if ($sshaswriteups_err) format_message(1,"Subsystem has writeups in the database and cannot be deleted.");

if ($edit_subsystem && !$delete_subsystem) {
    // set form options for editing
    $sectiontitle="<b><font size=\"+1\">Modify Subsystem</font></b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"dbadmin.php#subsystems\">Click here to add a new subsystem</a><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php?edit_subsystem=1#subsystems\">";
    $buttontags="
    <input type=\"hidden\" name=\"subsystem_id\" value=\"$subsystem_id\">
    <input type=\"submit\" name=\"update_subsystem\" value=\"Update Subsystem\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"delete_subsystem\" value=\"Delete Subsystem\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"reset\" value=\"Reset\"><br>
    ";
    // pull current values from database
    $currentvalues=mysqli_fetch_assoc(mysqli_query($drs_db,"select * from subsystems where id=$subsystem_id"));
    $subsystem_name=$currentvalues["name"];
    $subsystem_desc=$currentvalues["description"];
    if ($currentvalues["active"]==1) {
        $activetag="<input type=\"checkbox\" name=\"subsystem_active\" value=\"1\" checked>";
    } else {
        $activetag="<input type=\"checkbox\" name=\"subsystem_active\" value=\"1\">";
    }
} else {
    // set form options for add
    if (!$subsystem_add_fail) unset($subsystem_id,$subsystem_name,$subsystem_desc,$subsystem_active);
    $sectiontitle="<b><font size=\"+1\">Add New Subsystem</font></b><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php#subsystems\">";
    $buttontags="
    <input type=\"submit\" name=\"add_subsystem\" value=\"Add Subsystem\">
    ";
    // set default values
    $activetag="<input type=\"checkbox\" name=\"subsystem_active\" value=\"1\" checked>";
}
echo "
$sectiontitle
$formtag
<table border=\"0\">
<tr><td>
Subsystem Name: &nbsp;<input type=\"text\" name=\"subsystem_name\" size=\"40\" value=\"$subsystem_name\"><br><br>
Description: <br><textarea rows=\"2\" cols=\"60\" name=\"subsystem_desc\" wrap=\"soft\">$subsystem_desc</textarea><br><br>
Active: &nbsp; $activetag <br><br>
</td></tr>
</table>
$buttontags
</form>
<br><hr>
";

// show the table of entities
$existingdata=mysqli_query($drs_db,"select * from subsystems order by id asc");
echo "
<b>Existing Subsystems</b>&nbsp;&nbsp;&nbsp;<font size=\"-1\">Click on the Subsystem ID to edit.</font><br><br>
<table border=\"1\">
<tr><th>Subsystem ID</th><th>Subsystem Name</th><th>Description</th><th>Active</th></tr>
";
while ($tablerow=mysqli_fetch_assoc($existingdata)) {
    if ($tablerow["active"]) {
        $activedef="<img src=\"icons/tick.png\" title=\"active\" alt=\"active\">";
    } else {
        $activedef="<img src=\"icons/cross.png\" title=\"inactive\" alt=\"inactive\">";
    }
    printf("
    <tr>
    <td><a href=\"dbadmin.php?edit_subsystem=1&subsystem_id=%s#subsystems\">%s</a></td>
    <td>%s</td>
    <td>%s</td>
    <td align=\"center\">%s</td>
    </tr>",
    $tablerow["id"],$tablerow["id"],
    $tablerow["name"],
    $tablerow["description"],
    $activedef);
}
echo "</table>";

echo "
</div>

<div id=\"reasons\">
";
if ($reastextunique_err) format_message(1,"Reason already exists.");
if ($reastextsuppld_err) format_message(1,"Reason cannot be blank.");
if ($reashaswriteups_err) format_message(1,"Reason has writeups in the database and cannot be deleted.");

if ($edit_reason && !$delete_reason) {
    // set form options for editing
    $sectiontitle="<b><font size=\"+1\">Modify Reason</font></b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"dbadmin.php#reasons\">Click here to add a new reason</a><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php?edit_reason=1#reasons\">";
    $buttontags="
    <input type=\"hidden\" name=\"reason_id\" value=\"$reason_id\">
    <input type=\"submit\" name=\"update_reason\" value=\"Update Reason\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"delete_reason\" value=\"Delete Reason\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"reset\" value=\"Reset\"><br>
    ";
    // pull current values from database
    $currentvalues=mysqli_fetch_assoc(mysqli_query($drs_db,"select * from reasons where id=$reason_id"));
    $reason_text=$currentvalues["text"];
    if ($currentvalues["active"]==1) {
        $activetag="<input type=\"checkbox\" name=\"reason_active\" value=\"1\" checked>";
    } else {
        $activetag="<input type=\"checkbox\" name=\"reason_active\" value=\"1\">";
    }
} else {
    // set form options for add
    if (!$reason_add_fail) unset($reason_id,$reason_text,$reason_active);
    $sectiontitle="<b><font size=\"+1\">Add New Reason</font></b><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php#reasons\">";
    $buttontags="
    <input type=\"submit\" name=\"add_reason\" value=\"Add Reason\">
    ";
    // set default values
    $activetag="<input type=\"checkbox\" name=\"reason_active\" value=\"1\" checked>";
}
echo "
$sectiontitle
$formtag
<table border=\"0\">
<tr><td>
Reason Text: &nbsp;<input type=\"text\" name=\"reason_text\" size=\"40\" value=\"$reason_text\"><br><br>
Active: &nbsp; $activetag <br><br>
</td></tr>
</table>
$buttontags
</form>
<br><hr>
";

// show the table of entities
$existingdata=mysqli_query($drs_db,"select * from reasons order by id asc");
echo "
<b>Existing Reasons</b>&nbsp;&nbsp;&nbsp;<font size=\"-1\">Click on the Reason ID to edit.</font><br><br>
<table border=\"1\">
<tr><th>Reason ID</th><th>Reason Text</th><th>Active</th></tr>
";
while ($tablerow=mysqli_fetch_assoc($existingdata)) {
    if ($tablerow["active"]) {
        $activedef="<img src=\"icons/tick.png\" title=\"active\" alt=\"active\">";
    } else {
        $activedef="<img src=\"icons/cross.png\" title=\"inactive\" alt=\"inactive\">";
    }
    printf("
    <tr>
    <td><a href=\"dbadmin.php?edit_reason=1&reason_id=%s#reasons\">%s</a></td>
    <td>%s</td>
    <td align=\"center\">%s</td>
    </tr>",
    $tablerow["id"],$tablerow["id"],
    $tablerow["text"],
    $activedef);
}
echo "</table>";

echo "
</div>

<div id=\"periods\">
";
if ($peridisint_err) format_message(1,"Period number must be an integer.");
if ($peridsuppld_err) format_message(1,"Period number cannot be blank.");
if ($peridunique_err) format_message(1,"Period number already exists.");
if ($pertimesunique_err) format_message(1,"Period already exists.");
if ($pertimessuppld_err) format_message(1,"Period times cannot be blank.");
if ($perhaswriteups_err) format_message(1,"Period has writeups in the database and cannot be deleted.");

if ($edit_period && !$delete_period) {
    // set form options for editing
    $sectiontitle="<b><font size=\"+1\">Modify Period</font></b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href=\"dbadmin.php#periods\">Click here to add a new period</a><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php?edit_period=1#periods\">";
    $buttontags="
    <input type=\"hidden\" name=\"period_id\" value=\"$period_id\">
    <input type=\"submit\" name=\"update_period\" value=\"Update Period\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"delete_period\" value=\"Delete Period\">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"reset\" value=\"Reset\"><br>
    ";
    // pull current values from database
    $currentvalues=mysqli_fetch_assoc(mysqli_query($drs_db,"select * from periods where id=$period_id"));
    $period_times=$currentvalues["times"];
    if ($currentvalues["active"]==1) {
        $activetag="<input type=\"checkbox\" name=\"period_active\" value=\"1\" checked>";
    } else {
        $activetag="<input type=\"checkbox\" name=\"period_active\" value=\"1\">";
    }
    $idtag="$period_id";
} else {
    // set form options for add
    if (!$period_add_fail) unset($period_id,$period_times,$period_active);
    $sectiontitle="<b><font size=\"+1\">Add New Period</font></b><br><br>";
    $formtag="<form method=\"post\" action=\"dbadmin.php#periods\">";
    $buttontags="
    <input type=\"submit\" name=\"add_period\" value=\"Add Period\">
    ";
    $idtag="<input type=\"text\" name=\"period_id\" size=\"3\" value=\"$period_id\">";
    // set default values
    $activetag="<input type=\"checkbox\" name=\"period_active\" value=\"1\" checked>";
}
echo "
$sectiontitle
$formtag
<table border=\"0\">
<tr><td>
Period Number: &nbsp;$idtag<br><br>
Period Times: &nbsp;<input type=\"text\" name=\"period_times\" size=\"40\" value=\"$period_times\"><br><br>
Active: &nbsp; $activetag <br><br>
</td></tr>
</table>
$buttontags
</form>
<br><hr>
";

// show the table of entities
$existingdata=mysqli_query($drs_db,"select * from periods order by id asc");
echo "
<b>Existing Periods</b>&nbsp;&nbsp;&nbsp;<font size=\"-1\">Click on the Period ID to edit.</font><br><br>
<table border=\"1\">
<tr><th>Period ID</th><th>Period Times</th><th>Active</th></tr>
";
while ($tablerow=mysqli_fetch_assoc($existingdata)) {
    if ($tablerow["active"]) {
        $activedef="<img src=\"icons/tick.png\" title=\"active\" alt=\"active\">";
    } else {
        $activedef="<img src=\"icons/cross.png\" title=\"inactive\" alt=\"inactive\">";
    }
    printf("
    <tr>
    <td><a href=\"dbadmin.php?edit_period=1&period_id=%s#periods\">%s</a></td>
    <td>%s</td>
    <td align=\"center\">%s</td>
    </tr>",
    $tablerow["id"],$tablerow["id"],
    $tablerow["times"],
    $activedef);
}
echo "</table>";
echo "
</div>

</div>
";

echo "<br>";
banner($print);
pageblock("right","end");
pagetable("end");

framework("end","","",$print);

?>