352 lines
10 KiB
PHP
352 lines
10 KiB
PHP
|
<?php
|
||
|
|
declare(strict_types=1);
|
||
|
|
|
||
|
|
class TaskSubmissionRepository
|
||
|
|
{
|
||
|
|
public function create(
|
||
|
|
int $assignmentId,
|
||
|
|
int $methodId,
|
||
|
|
int $contactId,
|
||
|
|
string $status,
|
||
|
|
array $payload,
|
||
|
|
?int $score = null,
|
||
|
|
?int $maxScore = null
|
||
|
|
): int {
|
||
|
|
return TaskCertDb::insert(
|
||
|
|
'INSERT INTO task_submission (
|
||
|
|
assignment_id, method_id, main_contact_id, status, payload_json, score, max_score
|
||
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?)',
|
||
|
|
'iiissii',
|
||
|
|
[
|
||
|
|
$assignmentId,
|
||
|
|
$methodId,
|
||
|
|
$contactId,
|
||
|
|
$status,
|
||
|
|
json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
|
||
|
|
$score,
|
||
|
|
$maxScore,
|
||
|
|
]
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function findById(int $submissionId): ?array
|
||
|
|
{
|
||
|
|
$row = TaskCertDb::fetchOne(
|
||
|
|
'SELECT * FROM task_submission WHERE id = ?',
|
||
|
|
'i',
|
||
|
|
[$submissionId]
|
||
|
|
);
|
||
|
|
|
||
|
|
if ($row === null) {
|
||
|
|
return null;
|
||
|
|
}
|
||
|
|
|
||
|
|
$row['payload'] = $this->decodePayload($row['payload_json'] ?? null);
|
||
|
|
unset($row['payload_json']);
|
||
|
|
|
||
|
|
return $row;
|
||
|
|
}
|
||
|
|
|
||
|
|
public function findLatestByAssignmentAndMethod(int $assignmentId, int $methodId): ?array
|
||
|
|
{
|
||
|
|
$row = TaskCertDb::fetchOne(
|
||
|
|
'SELECT * FROM task_submission
|
||
|
|
WHERE assignment_id = ? AND method_id = ?
|
||
|
|
ORDER BY id DESC
|
||
|
|
LIMIT 1',
|
||
|
|
'ii',
|
||
|
|
[$assignmentId, $methodId]
|
||
|
|
);
|
||
|
|
|
||
|
|
if ($row === null) {
|
||
|
|
return null;
|
||
|
|
}
|
||
|
|
|
||
|
|
$row['payload'] = $this->decodePayload($row['payload_json'] ?? null);
|
||
|
|
unset($row['payload_json']);
|
||
|
|
|
||
|
|
return $row;
|
||
|
|
}
|
||
|
|
|
||
|
|
public function findLatestByAssignmentAndMethodAndStatuses(int $assignmentId, int $methodId, array $statuses): ?array
|
||
|
|
{
|
||
|
|
$statuses = array_values(array_filter(array_map('strval', $statuses), static function (string $status): bool {
|
||
|
|
return trim($status) !== '';
|
||
|
|
}));
|
||
|
|
|
||
|
|
if ($statuses === []) {
|
||
|
|
return null;
|
||
|
|
}
|
||
|
|
|
||
|
|
$placeholders = implode(',', array_fill(0, count($statuses), '?'));
|
||
|
|
$types = 'ii' . str_repeat('s', count($statuses));
|
||
|
|
$params = array_merge([$assignmentId, $methodId], $statuses);
|
||
|
|
|
||
|
|
$row = TaskCertDb::fetchOne(
|
||
|
|
'SELECT * FROM task_submission
|
||
|
|
WHERE assignment_id = ?
|
||
|
|
AND method_id = ?
|
||
|
|
AND status IN (' . $placeholders . ')
|
||
|
|
ORDER BY id DESC
|
||
|
|
LIMIT 1',
|
||
|
|
$types,
|
||
|
|
$params
|
||
|
|
);
|
||
|
|
|
||
|
|
if ($row === null) {
|
||
|
|
return null;
|
||
|
|
}
|
||
|
|
|
||
|
|
$row['payload'] = $this->decodePayload($row['payload_json'] ?? null);
|
||
|
|
unset($row['payload_json']);
|
||
|
|
|
||
|
|
return $row;
|
||
|
|
}
|
||
|
|
|
||
|
|
public function listByAssignment(int $assignmentId): array
|
||
|
|
{
|
||
|
|
$rows = TaskCertDb::fetchAll(
|
||
|
|
'SELECT s.*, m.method_type
|
||
|
|
FROM task_submission s
|
||
|
|
INNER JOIN task_method m ON m.id = s.method_id
|
||
|
|
WHERE s.assignment_id = ?
|
||
|
|
ORDER BY s.id DESC',
|
||
|
|
'i',
|
||
|
|
[$assignmentId]
|
||
|
|
);
|
||
|
|
|
||
|
|
foreach ($rows as &$row) {
|
||
|
|
$row['payload'] = $this->decodePayload($row['payload_json'] ?? null);
|
||
|
|
unset($row['payload_json']);
|
||
|
|
}
|
||
|
|
|
||
|
|
return $rows;
|
||
|
|
}
|
||
|
|
|
||
|
|
public function listAssignmentIdsWithSubmissions(array $assignmentIds): array
|
||
|
|
{
|
||
|
|
$assignmentIds = array_values(array_unique(array_map('intval', $assignmentIds)));
|
||
|
|
$assignmentIds = array_values(array_filter($assignmentIds, static function (int $assignmentId): bool {
|
||
|
|
return $assignmentId > 0;
|
||
|
|
}));
|
||
|
|
|
||
|
|
if ($assignmentIds === []) {
|
||
|
|
return [];
|
||
|
|
}
|
||
|
|
|
||
|
|
$placeholders = implode(',', array_fill(0, count($assignmentIds), '?'));
|
||
|
|
$rows = TaskCertDb::fetchAll(
|
||
|
|
'SELECT DISTINCT assignment_id
|
||
|
|
FROM task_submission
|
||
|
|
WHERE assignment_id IN (' . $placeholders . ')',
|
||
|
|
str_repeat('i', count($assignmentIds)),
|
||
|
|
$assignmentIds
|
||
|
|
);
|
||
|
|
|
||
|
|
$result = [];
|
||
|
|
foreach ($rows as $row) {
|
||
|
|
$assignmentId = (int) ($row['assignment_id'] ?? 0);
|
||
|
|
if ($assignmentId <= 0) {
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
|
||
|
|
$result[] = $assignmentId;
|
||
|
|
}
|
||
|
|
|
||
|
|
$result = array_values(array_unique($result));
|
||
|
|
sort($result);
|
||
|
|
|
||
|
|
return $result;
|
||
|
|
}
|
||
|
|
|
||
|
|
public function hasApprovedOrAutoPassedForMethod(int $assignmentId, int $methodId): bool
|
||
|
|
{
|
||
|
|
$count = TaskCertDb::scalar(
|
||
|
|
'SELECT COUNT(*)
|
||
|
|
FROM task_submission
|
||
|
|
WHERE assignment_id = ?
|
||
|
|
AND method_id = ?
|
||
|
|
AND status IN ("auto_passed", "approved")',
|
||
|
|
'ii',
|
||
|
|
[$assignmentId, $methodId]
|
||
|
|
);
|
||
|
|
|
||
|
|
return (int) $count > 0;
|
||
|
|
}
|
||
|
|
|
||
|
|
public function listPendingApprovals(int $taskId = 0, string $searchQuery = ''): array
|
||
|
|
{
|
||
|
|
$searchQuery = trim($searchQuery);
|
||
|
|
|
||
|
|
$sql = 'SELECT
|
||
|
|
s.*,
|
||
|
|
m.method_type,
|
||
|
|
a.task_id,
|
||
|
|
a.cycle_key,
|
||
|
|
d.title AS task_title,
|
||
|
|
c.name AS contact_name,
|
||
|
|
c.email AS contact_email
|
||
|
|
FROM task_submission s
|
||
|
|
INNER JOIN task_method m ON m.id = s.method_id
|
||
|
|
INNER JOIN task_assignment a ON a.id = s.assignment_id
|
||
|
|
INNER JOIN task_definition d ON d.id = a.task_id
|
||
|
|
LEFT JOIN main_contact c ON c.id = s.main_contact_id
|
||
|
|
WHERE s.status = "awaiting_approval"';
|
||
|
|
|
||
|
|
$types = '';
|
||
|
|
$params = [];
|
||
|
|
if ($taskId > 0) {
|
||
|
|
$sql .= ' AND a.task_id = ?';
|
||
|
|
$types .= 'i';
|
||
|
|
$params[] = $taskId;
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($searchQuery !== '') {
|
||
|
|
$searchLike = '%' . $searchQuery . '%';
|
||
|
|
$sql .= ' AND (
|
||
|
|
CAST(s.id AS CHAR) LIKE ?
|
||
|
|
OR d.title LIKE ?
|
||
|
|
OR COALESCE(c.name, "") LIKE ?
|
||
|
|
OR COALESCE(c.email, "") LIKE ?
|
||
|
|
OR m.method_type LIKE ?
|
||
|
|
)';
|
||
|
|
$types .= 'sssss';
|
||
|
|
$params[] = $searchLike;
|
||
|
|
$params[] = $searchLike;
|
||
|
|
$params[] = $searchLike;
|
||
|
|
$params[] = $searchLike;
|
||
|
|
$params[] = $searchLike;
|
||
|
|
}
|
||
|
|
|
||
|
|
$sql .= ' ORDER BY s.submitted_at ASC, s.id ASC';
|
||
|
|
|
||
|
|
$rows = TaskCertDb::fetchAll($sql, $types, $params);
|
||
|
|
foreach ($rows as &$row) {
|
||
|
|
$row['payload'] = $this->decodePayload($row['payload_json'] ?? null);
|
||
|
|
unset($row['payload_json']);
|
||
|
|
}
|
||
|
|
|
||
|
|
return $rows;
|
||
|
|
}
|
||
|
|
|
||
|
|
public function review(int $submissionId, string $newStatus, int $adminId, string $note): void
|
||
|
|
{
|
||
|
|
TaskCertDb::execute(
|
||
|
|
'UPDATE task_submission
|
||
|
|
SET status = ?, reviewed_by = ?, reviewed_at = NOW(), review_note = ?
|
||
|
|
WHERE id = ?',
|
||
|
|
'sisi',
|
||
|
|
[$newStatus, $adminId, $note, $submissionId]
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Sammelt alle in den Payloads referenzierten Datei-Pfade (form_values mit
|
||
|
|
* file_path) für die gegebenen Assignments — damit der Aufrufer die Dateien
|
||
|
|
* vor dem Löschen der Einreichungen von der Platte entfernen kann.
|
||
|
|
*
|
||
|
|
* @return string[] eindeutige relative Pfade
|
||
|
|
*/
|
||
|
|
public function collectFilePathsByAssignmentIds(array $assignmentIds): array
|
||
|
|
{
|
||
|
|
$assignmentIds = array_values(array_unique(array_map('intval', $assignmentIds)));
|
||
|
|
$assignmentIds = array_values(array_filter($assignmentIds, static function (int $assignmentId): bool {
|
||
|
|
return $assignmentId > 0;
|
||
|
|
}));
|
||
|
|
|
||
|
|
if ($assignmentIds === []) {
|
||
|
|
return [];
|
||
|
|
}
|
||
|
|
|
||
|
|
$placeholders = implode(',', array_fill(0, count($assignmentIds), '?'));
|
||
|
|
$rows = TaskCertDb::fetchAll(
|
||
|
|
'SELECT payload_json FROM task_submission WHERE assignment_id IN (' . $placeholders . ')',
|
||
|
|
str_repeat('i', count($assignmentIds)),
|
||
|
|
$assignmentIds
|
||
|
|
);
|
||
|
|
|
||
|
|
return $this->extractFilePathsFromRows($rows);
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Wie collectFilePathsByAssignmentIds, aber für alle Einreichungen einer
|
||
|
|
* Aufgabe (für das harte Löschen, das per FK-CASCADE die Zeilen entfernt).
|
||
|
|
*
|
||
|
|
* @return string[] eindeutige relative Pfade
|
||
|
|
*/
|
||
|
|
public function collectFilePathsByTaskId(int $taskId): array
|
||
|
|
{
|
||
|
|
if ($taskId <= 0) {
|
||
|
|
return [];
|
||
|
|
}
|
||
|
|
|
||
|
|
$rows = TaskCertDb::fetchAll(
|
||
|
|
'SELECT s.payload_json
|
||
|
|
FROM task_submission s
|
||
|
|
INNER JOIN task_assignment a ON a.id = s.assignment_id
|
||
|
|
WHERE a.task_id = ?',
|
||
|
|
'i',
|
||
|
|
[$taskId]
|
||
|
|
);
|
||
|
|
|
||
|
|
return $this->extractFilePathsFromRows($rows);
|
||
|
|
}
|
||
|
|
|
||
|
|
private function extractFilePathsFromRows(array $rows): array
|
||
|
|
{
|
||
|
|
$paths = [];
|
||
|
|
foreach ($rows as $row) {
|
||
|
|
$payload = $this->decodePayload($row['payload_json'] ?? null);
|
||
|
|
$formValues = is_array($payload['form_values'] ?? null) ? $payload['form_values'] : [];
|
||
|
|
|
||
|
|
foreach ($formValues as $value) {
|
||
|
|
if (!is_array($value)) {
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
|
||
|
|
foreach ($value as $fileMeta) {
|
||
|
|
if (is_array($fileMeta) && isset($fileMeta['file_path'])) {
|
||
|
|
$path = trim((string) $fileMeta['file_path']);
|
||
|
|
if ($path !== '') {
|
||
|
|
$paths[$path] = true;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
return array_keys($paths);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function deleteByAssignmentIds(array $assignmentIds): int
|
||
|
|
{
|
||
|
|
$assignmentIds = array_values(array_unique(array_map('intval', $assignmentIds)));
|
||
|
|
$assignmentIds = array_values(array_filter($assignmentIds, static function (int $assignmentId): bool {
|
||
|
|
return $assignmentId > 0;
|
||
|
|
}));
|
||
|
|
|
||
|
|
if ($assignmentIds === []) {
|
||
|
|
return 0;
|
||
|
|
}
|
||
|
|
|
||
|
|
$placeholders = implode(',', array_fill(0, count($assignmentIds), '?'));
|
||
|
|
|
||
|
|
return TaskCertDb::execute(
|
||
|
|
'DELETE FROM task_submission
|
||
|
|
WHERE assignment_id IN (' . $placeholders . ')',
|
||
|
|
str_repeat('i', count($assignmentIds)),
|
||
|
|
$assignmentIds
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
private function decodePayload(?string $json): array
|
||
|
|
{
|
||
|
|
if ($json === null || trim($json) === '') {
|
||
|
|
return [];
|
||
|
|
}
|
||
|
|
|
||
|
|
$decoded = json_decode($json, true);
|
||
|
|
return is_array($decoded) ? $decoded : [];
|
||
|
|
}
|
||
|
|
}
|