/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/fetch_duplicate_assignments.php (4902B)
0) {
$augustFirst = new DateTime("$semesterYear-08-01");
$dayOfWeek = (int)$augustFirst->format('N');
$daysUntilMonday = ($dayOfWeek === 1) ? 0 : (8 - $dayOfWeek) % 7;
$semesterStart = clone $augustFirst;
$semesterStart->modify("+$daysUntilMonday days");
$semesterEnd = new DateTime(($semesterYear + 1) . '-06-30');
$semStartStr = $semesterStart->format('Y-m-d');
$semEndStr = $semesterEnd->format('Y-m-d');
}
$query = "SELECT
tca.Course_ID,
tca.Group_ID,
tca.Program_ID,
COUNT(*) AS total_assignments,
COUNT(DISTINCT tca.Teacher_ID) AS teacher_count,
GROUP_CONCAT(DISTINCT CONCAT(tp.First_Name, ' ', tp.Last_Name, ' (', tcount.cnt, 'x)') SEPARATOR '
') AS teacher_list,
GROUP_CONCAT(DISTINCT tca.Teacher_ID) AS teacher_ids,
MIN(tca.Assignment_ID) AS first_id,
MAX(tca.Assignment_ID) AS last_id,
c.Course_Name,
g.Group_Name,
p.Program_Name,
GROUP_CONCAT(DISTINCT tca.Time_Slot ORDER BY tca.Time_Slot SEPARATOR ', ') AS Time_Slots,
MIN(tca.Start_Date) AS Start_Date,
MAX(tca.End_Date) AS End_Date,
MIN(YEAR(tca.Start_Date)) AS year_start
FROM teacher_course_assignments tca
JOIN teacher_profile tp ON tca.Teacher_ID = tp.Teacher_ID
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN manager_group_name g ON tca.Group_ID = g.Group_ID
JOIN Programs p ON tca.Program_ID = p.Program_ID
LEFT JOIN (
SELECT Teacher_ID, Course_ID, Group_ID, Program_ID, COUNT(*) AS cnt
FROM teacher_course_assignments
GROUP BY Teacher_ID, Course_ID, Group_ID, Program_ID
) tcount ON tcount.Teacher_ID = tca.Teacher_ID
AND tcount.Course_ID = tca.Course_ID
AND tcount.Group_ID = tca.Group_ID
AND tcount.Program_ID = tca.Program_ID
WHERE tca.Program_ID = ?";
$params = [$Program_ID];
$types = "i";
if ($semesterYear > 0) {
$query .= " AND tca.Start_Date >= ? AND tca.End_Date <= ?";
$params[] = $semStartStr;
$params[] = $semEndStr;
$types .= "ss";
}
$query .= " GROUP BY tca.Course_ID, tca.Group_ID, tca.Program_ID
HAVING (COUNT(*) > 1 OR COUNT(DISTINCT tca.Teacher_ID) > 1)
ORDER BY c.Course_Name, g.Group_Name";
$stmt = $mysqli->prepare($query);
if (!$stmt) {
echo "
| Error preparing query: " . $mysqli->error . " |
";
$mysqli->close();
exit;
}
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows === 0) {
echo "
| No duplicate assignments found for this program. |
";
} else {
while ($row = $result->fetch_assoc()) {
$courseName = htmlspecialchars($row['Course_Name']);
$groupName = htmlspecialchars($row['Group_Name']);
$programName = htmlspecialchars($row['Program_Name']);
$teacherList = $row['teacher_list'];
$teacherIDs = htmlspecialchars($row['teacher_ids']);
$timeSlots = htmlspecialchars($row['Time_Slots']);
$startDate = htmlspecialchars($row['Start_Date']);
$endDate = htmlspecialchars($row['End_Date']);
$totalCount = (int)$row['total_assignments'];
$teacherCount = (int)$row['teacher_count'];
$badgeClass = $teacherCount > 1 ? 'label-warning' : 'label-danger';
$badgeText = $teacherCount > 1 ? $teacherCount . ' teachers / ' . $totalCount . ' total' : $totalCount . 'x';
echo "
| " . $teacherList . " |
" . $courseName . " |
" . $groupName . " |
" . $programName . " |
" . $timeSlots . " |
" . $startDate . " |
" . $endDate . " |
" . $badgeText . " |
";
}
}
$stmt->close();
$mysqli->close();
}
?>