/**
 * This function connects to the MySQL server
 * and uses the global array $config. The 
 * function also selects the right database-schema
 *
 */
function databaseConnect()
{
    global $config;
    # report exceptions, but don't throw them, and we are not interested in
    # wether we use indexes.
    mysqli_report(MYSQLI_REPORT_ALL ^ MYSQLI_REPORT_STRICT ^ MYSQLI_REPORT_INDEX);
    $db_conn = mysqli_connect($config['mysql']['hostname'], $config['mysql']['username'], $config['mysql']['password'], $config['mysql']['database']);
    // check connection
    if (mysqli_connect_errno()) {
        $errormessage = "Connect failed " . mysqli_connect_error();
        add_to_message_stack($errormessage, true);
        exit;
    }
    return $db_conn;
}
/**
 * check for given variable if value is empty
 * and appends message to the array if this is the case
 * 
 */
function ifEmptyAddMessage(&$variable, &$errorList, $message, $addToStack)
{
    if (empty($variable)) {
        $errorList[] = $message;
        if ($addToStack) {
            add_to_message_stack($message, FALSE);
        }
    }
}
/**
 * This is a template rendering function. As such it 
 * serves illustrative purposes and in general needs to
 * be configured for specific needs.
 * 
 * This function makes a report of all the records in the 
 * Jobs table. It outputs HTML and can sort records in the
 * table based on a predefined field and sortation order.
 */
function displayAllJobs()
{
    global $db_conn;
    #references connection to the database
    $field = 'JobID';
    #default sort column
    $sort = 'ASC';
    #default sorting order
    // Set the sortation order requested via the URL
    if (isset($_GET['sorting'])) {
        if ($_GET['sorting'] == 'ASC') {
            $sort = 'ASC';
        } else {
            $sort = 'DESC';
        }
    }
    // Set the sortation field requested by URL
    // on which soration should be applied
    if (isset($_GET['field'])) {
        switch ($_GET['field']) {
            case "JobTitle":
                $field = 'JobTitle';
                break;
            case "MinSalary":
                $field = 'MinSalary';
                break;
            case "MaxSalary":
                $field = 'MaxSalary';
                break;
            default:
                $field = 'JobID';
        }
    }
    // initializes a statement and return an object for use
    // with mysqli_stmt_prepare
    $stmt = mysqli_stmt_init($db_conn);
    // the query that should be parsed
    $sql = "SELECT * FROM `jobs` ORDER BY {$field} {$sort}";
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        // if things go wrong we sent a message to the screen
        add_to_message_stack("Query could not be parsed. This message is logged", true);
        // and a message to the system log. Note we do not give information
        // about SQL to the user.
        log_message("Query could not be parsed: " . $sql);
    } else {
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        mysqli_stmt_close($stmt);
        ?>
			<h1>Jobs</h1>
			<br/>
			<input type="button" onclick="document.location.href='?action=addJob';" value="Add Job" />
			<br/>
			<br/>
			<table>
				<tr>
					<th>Job ID <br/> <?php 
        echo parseSorting("jobs", "JobID", $field, $sort);
        ?>
</th>
					<th>Title <br/> <?php 
        echo parseSorting("jobs", "JobTitle", $field, $sort);
        ?>
</th>
					<th>Minimum Salary <br/> <?php 
        echo parseSorting("jobs", "MinSalary", $field, $sort);
        ?>
</th>
					<th>Maximum Salary <br/> <?php 
        echo parseSorting("jobs", "MaxSalary", $field, $sort);
        ?>
</th>
					<th>Action</th>
				</tr>

				<?php 
        $count = 1;
        while ($row = mysqli_fetch_assoc($result)) {
            $count++;
            ?>
					<tr <?php 
            if ($count % 2 == 0) {
                echo "class=\"highlight\" ";
            }
            ?>
>
						<td><?php 
            echo $row['JobID'];
            ?>
</td>
						<td><?php 
            echo $row['JobTitle'];
            ?>
</td>
						<td><?php 
            echo $row['MinSalary'];
            ?>
</td>
						<td><?php 
            echo $row['MaxSalary'];
            ?>
</td>
						<td>
							<a href="index.php?action=editJob&amp;id=<?php 
            echo $row['JobID'];
            ?>
">Edit</a>|<a class="delete" href="javascript:confirmAction('Are you sure?', 'index.php?action=deleteJob&amp;id=<?php 
            echo $row['JobID'];
            ?>
');">Remove</a>
						</td>
					</tr>
					<?php 
        }
        ?>
				</table>
				<?php 
    }
}
/**
 * This is a template rendering function. As such it 
 * serves illustrative purposes and in general needs to
 * be configured for specific needs.
 * 
 * This function makes a report of all the records in the 
 * Jobs table. It outputs HTML and can sort records in the
 * table based on a predefined field and sortation order.
 */
function displayAllCustomers()
{
    log_message("Backtrace: displayAllCustomers() called. ");
    global $db_conn;
    #references connection to the database
    $field = 'Wcode';
    #default sort column
    $sort = 'ASC';
    #default sorting order
    // Set the sortation order requested via the URL
    if (isset($_GET['sorting'])) {
        if ($_GET['sorting'] == 'ASC') {
            $sort = 'ASC';
        } else {
            $sort = 'DESC';
        }
    }
    // Set the sortation field requested by URL
    // on which soration should be applied
    if (isset($_GET['field'])) {
        switch ($_GET['field']) {
            case "Wcode":
                $field = 'Wcode';
                break;
            case "Naam":
                $field = 'Naam';
                break;
            case "Adres":
                $field = 'Adres';
                break;
            case "Plaats":
                $field = 'Plaats';
                break;
            case "Telefoonnr":
                $field = 'Plaats';
                break;
            default:
                $field = 'JobID';
        }
    }
    // initializes a statement and return an object for use
    // with mysqli_stmt_prepare
    $stmt = mysqli_stmt_init($db_conn);
    // the query that should be parsed
    $sql = "SELECT * FROM `winkel` ORDER BY {$field} {$sort}";
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        // if things go wrong we sent a message to the screen
        add_to_message_stack("Query could not be parsed. This message is logged", true);
        // and a message to the system log. Note we do not give information
        // about SQL to the user.
        log_message("Query could not be parsed: " . $sql);
    } else {
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        mysqli_stmt_close($stmt);
        ?>
			<h1>Customers</h1>
			<br/>
			<input type="button" onclick="document.location.href='?action=displayAddCustomer';" value="Add Customer" />
			<br/>
			<br/>
			<table>
				<tr>
					<th>Wcode <br/> <?php 
        echo parseSorting("showCustomers", "Wcode", $field, $sort);
        ?>
</th>
					<th>Name <br/> <?php 
        echo parseSorting("showCustomers", "Naam", $field, $sort);
        ?>
</th>
					<th>Address <br/> <?php 
        echo parseSorting("showCustomers", "Adres", $field, $sort);
        ?>
</th>
					<th>Place <br/> <?php 
        echo parseSorting("showCustomers", "Plaats", $field, $sort);
        ?>
</th>
					<th>Phone number <br/> <?php 
        echo parseSorting("showCustomers", "Telefoonnr", $field, $sort);
        ?>
</th>
					<th>Action</th>
				</tr>

				<?php 
        $count = 1;
        while ($row = mysqli_fetch_assoc($result)) {
            $count++;
            ?>
					<tr <?php 
            if ($count % 2 == 0) {
                echo "class=\"highlight\" ";
            }
            ?>
>
						<td><?php 
            echo $row['Wcode'];
            ?>
</td>
						<td><?php 
            echo $row['Naam'];
            ?>
</td>
						<td><?php 
            echo $row['Adres'];
            ?>
</td>
						<td><?php 
            echo $row['Plaats'];
            ?>
</td>
						<td><?php 
            echo $row['Telefoonnr'];
            ?>
</td>
						<td>
							<a href="index.php?action=displayEditCustomer&amp;id=<?php 
            echo $row['Wcode'];
            ?>
">Edit</a>|<a class="delete" href="javascript:confirmAction('Are you sure?', 'index.php?action=deleteCustomer&amp;id=<?php 
            echo $row['Wcode'];
            ?>
');">Remove</a>
						</td>
					</tr>
					<?php 
        }
        ?>
				</table>
				<?php 
    }
}