setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdotest = $pdo->query('SHOW TABLES');
if ($pdotest->rowCount() == 0) {
$pdo->exec("CREATE TABLE IF NOT EXISTS grievances (
id INT AUTO_INCREMENT PRIMARY KEY,
grievant_name VARCHAR(255) NOT NULL,
discipline VARCHAR(255) NOT NULL,
subject VARCHAR(255) NOT NULL,
status VARCHAR(100) NOT NULL,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
union_representative VARCHAR(255) NOT NULL,
category VARCHAR(255) NOT NULL DEFAULT 'Local Grievances'
);");
}
// Check if 'category' column exists and add it if it doesn't
$stmt = $pdo->query("SHOW COLUMNS FROM grievances LIKE 'category'");
if ($stmt->rowCount() == 0) {
$pdo->exec("ALTER TABLE grievances ADD COLUMN category VARCHAR(255) NOT NULL DEFAULT 'Local Grievances'");
}
// Check if 'case_number' column exists and add it if it doesn't
$stmt = $pdo->query("SHOW COLUMNS FROM grievances LIKE 'case_number'");
if ($stmt->rowCount() == 0) {
$pdo->exec("ALTER TABLE grievances ADD COLUMN case_number VARCHAR(255) NULL");
}
$pdo->exec("CREATE TABLE IF NOT EXISTS grievance_updates (
id INT AUTO_INCREMENT PRIMARY KEY,
grievance_id INT NOT NULL,
update_text TEXT NOT NULL,
representative_name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (grievance_id) REFERENCES grievances(id) ON DELETE CASCADE
);");
} catch (PDOException $e) {
$message = 'Database error: ' . $e->getMessage() . '
';
}
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['add_grievance'])) {
$grievant_name = trim($_POST['grievant_name']);
$discipline = trim($_POST['discipline']);
$subject = trim($_POST['subject']);
$status = trim($_POST['status']);
$union_representative = trim($_POST['union_representative']);
$category = trim($_POST['category']);
if (!empty($grievant_name) && !empty($discipline) && !empty($subject) && !empty($status) && !empty($union_representative) && !empty($category)) {
try {
$stmt = $pdo->prepare("INSERT INTO grievances (grievant_name, discipline, subject, status, union_representative, category) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->execute([$grievant_name, $discipline, $subject, $status, $union_representative, $category]);
$message = 'Grievance added successfully!
';
} catch (PDOException $e) {
$message = 'Error adding grievance: ' . $e->getMessage() . '
';
}
} else {
$message = 'Please fill out all fields.
';
}
} elseif (isset($_POST['move_grievance'])) {
$grievance_id = $_POST['grievance_id'];
$new_category = $_POST['new_category'];
if (!empty($grievance_id) && !empty($new_category) && in_array($new_category, $categories)) {
try {
$stmt = $pdo->prepare("UPDATE grievances SET category = ? WHERE id = ?");
$stmt->execute([$new_category, $grievance_id]);
$message = 'Grievance moved successfully!
';
} catch (PDOException $e) {
$message = 'Error moving grievance: ' . $e->getMessage() . '
';
}
} else {
$message = 'Invalid move operation.
';
}
} elseif (isset($_POST['edit_grievance'])) {
$grievance_id = $_POST['grievance_id'];
$grievant_name = trim($_POST['grievant_name']);
$discipline = trim($_POST['discipline']);
$subject = trim($_POST['subject']);
$status = trim($_POST['status']);
$union_representative = trim($_POST['union_representative']);
$case_number = isset($_POST['case_number']) ? trim($_POST['case_number']) : null;
if (!empty($grievance_id) && !empty($grievant_name) && !empty($discipline) && !empty($subject) && !empty($status) && !empty($union_representative)) {
try {
// Check category to decide if case_number should be updated
$stmt_cat = $pdo->prepare("SELECT category FROM grievances WHERE id = ?");
$stmt_cat->execute([$grievance_id]);
$grievance_category = $stmt_cat->fetchColumn();
$sql = "UPDATE grievances SET grievant_name = ?, discipline = ?, subject = ?, status = ?, union_representative = ?";
$params = [$grievant_name, $discipline, $subject, $status, $union_representative];
if (($grievance_category === 'FWD to Office' || $grievance_category === 'Terminations')) {
$sql .= ", case_number = ?";
$params[] = $case_number;
}
$sql .= " WHERE id = ?";
$params[] = $grievance_id;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$message = 'Grievance updated successfully!
';
} catch (PDOException $e) {
$message = 'Error updating grievance: ' . $e->getMessage() . '
';
}
} else {
$message = 'Please fill out all fields.
';
}
} elseif (isset($_POST['add_grievance_update'])) {
$grievance_id = $_POST['grievance_id'];
$update_text = trim($_POST['update_text']);
$representative_name = trim($_POST['representative_name']);
if (!empty($grievance_id) && !empty($update_text) && !empty($representative_name)) {
try {
$stmt = $pdo->prepare("INSERT INTO grievance_updates (grievance_id, update_text, representative_name) VALUES (?, ?, ?)");
$stmt->execute([$grievance_id, $update_text, $representative_name]);
$message = 'Update added successfully!
';
} catch (PDOException $e) {
$message = 'Error adding update: ' . $e->getMessage() . '
';
}
} else {
$message = 'Please fill out all fields for the update.
';
}
}
}
$sort_column = $_GET['sort'] ?? 'last_updated';
$sort_order = $_GET['order'] ?? 'desc';
// The 'timeframes' column in the UI is a date input, not a database field.
// Sorting by 'timeframes' will sort by 'last_updated' as a sensible default.
$valid_columns = ['last_updated', 'status', 'timeframes'];
if (!in_array($sort_column, $valid_columns)) {
$sort_column = 'last_updated';
}
$order_by_column = ($sort_column === 'timeframes') ? 'last_updated' : $sort_column;
$direction = ($sort_order === 'asc' ? 'ASC' : 'DESC');
if ($order_by_column === 'status') {
$order_clause = "ORDER BY CASE status
WHEN 'Pending Submission' THEN 1
WHEN '1st Step' THEN 2
WHEN '1st Step Pending' THEN 3
WHEN '2nd Step' THEN 4
WHEN '2nd Step Pending' THEN 5
WHEN 'Settled' THEN 6
WHEN 'Withdrawn' THEN 7
ELSE 8
END " . $direction;
} else {
$order_clause = "ORDER BY " . $order_by_column . " " . $direction;
}
try {
$stmt = $pdo->prepare("SELECT * FROM grievances WHERE category = ? $order_clause");
$stmt->execute([$page]);
$grievances = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$grievances = [];
$message .= 'Error fetching grievances: ' . $e->getMessage() . '
';
}
?>