<?php # moth.php
// This script retrieves all the records from the users table.
require ('includes/config.inc.php');
$page_title = 'View the Month\'s Stats';
include ('includes/header.html');
require (MYSQL);
// If no first_name session variable exists, redirect the user:
if (!isset($_SESSION['person_id'])) {
$url = BASE_URL . 'index.php'; // Define the URL.
ob_end_clean(); // Delete the buffer.
header("Location: $url");
exit(); // Quit the script.
}
// Header
echo '<h1><center>Stats For The Last Month</center></h1>';
// Number of records to show per page:
$display = 10;
// Determine how many pages there are...
if (isset($_GET['p']) && is_numeric($_GET['p'])) { // Already been determined.
$pages = $_GET['p'];
} else { // Need to determine.
// Count the number of records:
$q = "SELECT COUNT(worker_id) FROM worker";
$r = @mysqli_query ($dbc, $q);
$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
$records = $row[0];
// Calculate the number of pages...
if ($records > $display) { // More than 1 page.
$pages = ceil($records/$display);
} else {
$pages = 1;
}
} // End of p IF.
// Determine where in the database to start returning results...
if (isset($_GET['s']) && is_numeric($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}
// Determine the sort...
// Default is by registration date.
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'box';
$ob = (isset($_GET['ob'])) ? $_GET['ob'] : 'ASC';
$ob = ($ob=='DESC' ? 'ASC' : 'DESC');
// Determine the sorting order:
switch ($sort) {
case 'box':
$order_by = 'Boxes '.$ob;
break;
case 'pull':
$order_by = 'Pull '.$ob;
break;
case 'nick':
$order_by = 'Nickname '.$ob;
break;
case 'fn':
$order_by = 'first_name '.$ob;
break;
case 'ln':
$order_by = 'last_name '.$ob;
break;
case 'city':
$order_by = 'city '.$ob;
break;
case 'st':
$order_by = 'st '.$ob;
break;
case 'start':
$order_by = 'Started '.$ob;
break;
case 'rd':
$order_by = 'registred '.$ob;
break;
default:
$order_by = 'Boxes '.$ob;
$sort = 'box';
break;
}
// Define the query:
$q = "
SELECT r.user_id, w.person_id, w.worker_id, w.office_id, a.address_id, t.transaction_id,
SUM(oi.quantaty) AS Boxes, FLOOR(SUM(oi.negotiated_price)) AS Pull,
r.screen_name AS Nickname, p.first_name, p.last_name, a.city, a.st,
DATE_FORMAT(w.start_date, '%M, %Y') AS Started,
DATE_FORMAT(r.registration_date, '%m/%d/%Y') AS registred
FROM worker AS w
INNER JOIN person AS p
ON w.person_id = p.person_id
INNER JOIN registered_user AS r
ON r.person_id = p.person_id
INNER JOIN address AS a
ON a.office_id = w.office_id
INNER JOIN the_transaction AS t
ON t.worker_id = w.worker_id
AND t.paid_dateTime >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
INNER JOIN order_item AS oi
ON oi.transaction_id = t.transaction_id
GROUP BY (worker_id)
ORDER BY $order_by
LIMIT $start, $display
";
$r = @mysqli_query ($dbc, $q); // Run the query.
// Table header:
echo '<table align="center" cellspacing="0" cellpadding="5" width="75%">
<tr>
<td align="left"><b>Veiw</b></td>
<td align="left"><b><a href="month.php?sort=nick&ob='.$ob.'">Nickname</a></b></td>
<td align="left"><b><a href="month.php?sort=box&ob='.$ob.'">Box Count</a></b></td>
<td align="left"><b><a href="month.php?sort=pull&ob='.$ob.'">How Much</a></b></td>
<td align="left"><b><a href="month.php?sort=city&ob='.$ob.'">City</a></b></td>
<td align="left"><b><a href="month.php?sort=st&ob='.$ob.'">State</a></b></td>
</tr>
';
// Fetch and print all the records....
$bg = '#eeeeee';
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
echo '<tr bgcolor="' . $bg . '">
<td align="left"><a href="view_user.php?id=' . $row['worker_id'] . '">view</a></td>
<td align="left">' . $row['Nickname'] . '</td>
<td align="left">' . $row['Boxes'] . '</td>
<td align="left">' . $row['Pull'] . '</td>
<td align="left">' . $row['city'] . '</td>
<td align="left">' . $row['st'] . '</td>
</tr>
';
} // End of WHILE loop.
echo '</table>';
mysqli_free_result ($r);
mysqli_close($dbc);
// Make the links to other pages, if necessary.
if ($pages > 1) {
echo '<br /><p>';
$current_page = ($start/$display) + 1;
// If it's not the first page, make a Previous button:
if ($current_page != 1) {
echo '<a href="week.php?s=' . ($start - $display) . '&p=' . $pages . '&sort=' . $sort . '">Previous</a> ';
}
// Make all the numbered pages:
for ($i = 1; $i <= $pages; $i++) {
if ($i != $current_page) {
echo '<a href="week.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&sort=' . $sort . '">' . $i . '</a> ';
} else {
echo $i . ' ';
}
} // End of FOR loop.
// If it's not the last page, make a Next button:
if ($current_page != $pages) {
echo '<a href="week.php?s=' . ($start + $display) . '&p=' . $pages . '&sort=' . $sort . '">Next</a>';
}
echo '</p>'; // Close the paragraph.
} // End of links section.
include ('includes/footer.html');
?>