Repository navigation
Expand file tree
/
Copy pathexport_csv.php
More file actions
105 lines (85 loc) · 2.69 KB
/
Copy pathexport_csv.php
File metadata and controls
105 lines (85 loc) · 2.69 KB
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
<?php
require_once 'includes/database.php';
if (!isset($_SESSION)) {
session_start();
}
// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
header("Location: login.php");
exit();
}
// Validate and sanitize filter parameters
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : null;
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : null;
$category = isset($_GET['category']) ? $_GET['category'] : null;
// Validate date formats
if ($start_date && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $start_date)) {
die("Invalid start date format");
}
if ($end_date && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $end_date)) {
die("Invalid end date format");
}
// Validate category against allowed values
$allowed_categories = ['Training', 'Conference', 'Reading', 'Online Course', 'Other', 'all'];
if ($category && !in_array($category, $allowed_categories)) {
die("Invalid category");
}
// Build query with filters using prepared statements
$query = "SELECT * FROM cpd_entries WHERE user_id = ?";
$params = [$_SESSION['user_id']];
if ($start_date) {
$query .= " AND date_completed >= ?";
$params[] = $start_date;
}
if ($end_date) {
$query .= " AND date_completed <= ?";
$params[] = $end_date;
}
if ($category && $category !== 'all') {
$query .= " AND category = ?";
$params[] = $category;
}
$query .= " ORDER BY date_completed DESC";
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$entries = $stmt->fetchAll();
// Calculate total hours
$total_hours = 0;
foreach ($entries as $entry) {
$total_hours += $entry['hours'];
}
// Set headers for CSV download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=cpd_export_' . date('Y-m-d') . '.csv');
// Create output stream
$output = fopen('php://output', 'w');
// Add BOM for UTF-8 compatibility in Excel
fputs($output, "\xEF\xBB\xBF");
// Write headers
fputcsv($output, [
'Date Completed',
'Title',
'Description',
'Category',
'Hours',
'Supporting Document',
'Date Logged'
]);
// Write data rows
foreach ($entries as $entry) {
fputcsv($output, [
$entry['date_completed'],
htmlspecialchars($entry['title']),
htmlspecialchars($entry['description']),
htmlspecialchars($entry['category']),
$entry['hours'],
$entry['supporting_docs'] ? htmlspecialchars(basename($entry['supporting_docs'])) : 'None',
$entry['created_at']
]);
}
// Add summary row
fputcsv($output, []); // Empty row
fputcsv($output, ['', '', '', 'TOTAL HOURS:', $total_hours, '', '']);
fclose($output);
exit();
?>