-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathlocker_report.php
106 lines (96 loc) · 3.34 KB
/
locker_report.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
<?php
// locker_check_report_with_date_picker.php
// Set default dates to the last 7 days
$default_from_date = date('Y-m-d', strtotime('-7 days'));
$default_to_date = date('Y-m-d');
$from_date = isset($_GET['from_date']) ? $_GET['from_date'] : $default_from_date;
$to_date = isset($_GET['to_date']) ? $_GET['to_date'] : $default_to_date;
include 'db.php';
$db = get_db_connection();
// Check if session has not already been started
if (session_status() === PHP_SESSION_NONE) {
session_start();
}
// Check if the version session variable is not set
if (!isset($_SESSION['version'])) {
// Get the latest Git tag version
$version = trim(exec('git describe --tags $(git rev-list --tags --max-count=1)'));
// Set the session variable
$_SESSION['version'] = $version;
} else {
// Use the already set session variable
$version = $_SESSION['version'];
}
// Prepare and execute the query
$query = "
WITH LatestChecks AS (
SELECT
locker_id,
MAX(id) AS latest_check_id
FROM checks
WHERE DATE(check_date) BETWEEN :from_date AND :to_date
GROUP BY locker_id
)
SELECT
t.name as truck_name,
l.name as locker_name,
i.name as item_name,
ci.is_present as checked,
c.check_date,
c.checked_by,
c.id as check_id
FROM checks c
JOIN LatestChecks lc ON c.id = lc.latest_check_id
JOIN check_items ci ON c.id = ci.check_id
JOIN lockers l ON c.locker_id = l.id
JOIN trucks t ON l.truck_id = t.id
JOIN items i ON ci.item_id = i.id
ORDER BY t.name, l.name;
";
$stmt = $db->prepare($query);
$stmt->execute(['from_date' => $from_date, 'to_date' => $to_date]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Locker Check Report</title>
</head>
<body>
<h1>Locker Check Report</h1>
<form method="GET" action="">
<label for="from_date">From Date:</label>
<input type="date" id="from_date" name="from_date" value="<?php echo htmlspecialchars($from_date); ?>">
<label for="to_date">To Date:</label>
<input type="date" id="to_date" name="to_date" value="<?php echo htmlspecialchars($to_date); ?>">
<button type="submit">Filter</button>
</form>
<table border="1">
<thead>
<tr>
<th>Truck Name</th>
<th>Locker Name</th>
<th>Item Name</th>
<th>Checked</th>
<th>Check Date</th>
<th>Checked By</th>
<th>Check ID</th>
</tr>
</thead>
<tbody>
<?php foreach ($results as $row): ?>
<tr>
<td><?php echo htmlspecialchars($row['truck_name']); ?></td>
<td><?php echo htmlspecialchars($row['locker_name']); ?></td>
<td><?php echo htmlspecialchars($row['item_name']); ?></td>
<td><?php echo htmlspecialchars($row['checked']); ?></td>
<td><?php echo htmlspecialchars($row['check_date']); ?></td>
<td><?php echo htmlspecialchars($row['checked_by']); ?></td>
<td><?php echo htmlspecialchars($row['check_id']); ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</body>
</html>