246 lines
7.9 KiB
PHP
246 lines
7.9 KiB
PHP
|
<?php
|
||
|
|
declare(strict_types=1);
|
||
|
|
|
||
|
|
class TaskDefinitionRepository
|
||
|
|
{
|
||
|
|
public function create(array $data): int
|
||
|
|
{
|
||
|
|
$sql = 'INSERT INTO task_definition (
|
||
|
|
title, description, task_category_id, frequency_type, recurrence_basis,
|
||
|
|
recurrence_unit, recurrence_interval, start_date, deadline_rule_type,
|
||
|
|
deadline_offset_days, deadline_weekday, deadline_monthday,
|
||
|
|
fulfillment_mode, certificate_enabled, certificate_title, certificate_validity_mode, certificate_validity_days,
|
||
|
|
show_cycle_period, active, created_by, modified_by
|
||
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
|
||
|
|
|
||
|
|
return TaskCertDb::insert($sql, 'ssisssissiiisissiiiii', [
|
||
|
|
$data['title'],
|
||
|
|
$data['description'],
|
||
|
|
$data['task_category_id'],
|
||
|
|
$data['frequency_type'],
|
||
|
|
$data['recurrence_basis'],
|
||
|
|
$data['recurrence_unit'],
|
||
|
|
$data['recurrence_interval'],
|
||
|
|
$data['start_date'],
|
||
|
|
$data['deadline_rule_type'],
|
||
|
|
$data['deadline_offset_days'],
|
||
|
|
$data['deadline_weekday'],
|
||
|
|
$data['deadline_monthday'],
|
||
|
|
$data['fulfillment_mode'],
|
||
|
|
$data['certificate_enabled'],
|
||
|
|
$data['certificate_title'],
|
||
|
|
$data['certificate_validity_mode'],
|
||
|
|
$data['certificate_validity_days'],
|
||
|
|
$data['show_cycle_period'],
|
||
|
|
$data['active'],
|
||
|
|
$data['created_by'],
|
||
|
|
$data['modified_by'],
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function update(int $taskId, array $data): void
|
||
|
|
{
|
||
|
|
$sql = 'UPDATE task_definition SET
|
||
|
|
title = ?,
|
||
|
|
description = ?,
|
||
|
|
task_category_id = ?,
|
||
|
|
frequency_type = ?,
|
||
|
|
recurrence_basis = ?,
|
||
|
|
recurrence_unit = ?,
|
||
|
|
recurrence_interval = ?,
|
||
|
|
start_date = ?,
|
||
|
|
deadline_rule_type = ?,
|
||
|
|
deadline_offset_days = ?,
|
||
|
|
deadline_weekday = ?,
|
||
|
|
deadline_monthday = ?,
|
||
|
|
fulfillment_mode = ?,
|
||
|
|
certificate_enabled = ?,
|
||
|
|
certificate_title = ?,
|
||
|
|
certificate_validity_mode = ?,
|
||
|
|
certificate_validity_days = ?,
|
||
|
|
show_cycle_period = ?,
|
||
|
|
active = ?,
|
||
|
|
modified_by = ?
|
||
|
|
WHERE id = ?';
|
||
|
|
|
||
|
|
TaskCertDb::execute($sql, 'ssisssissiiisissiiiii', [
|
||
|
|
$data['title'],
|
||
|
|
$data['description'],
|
||
|
|
$data['task_category_id'],
|
||
|
|
$data['frequency_type'],
|
||
|
|
$data['recurrence_basis'],
|
||
|
|
$data['recurrence_unit'],
|
||
|
|
$data['recurrence_interval'],
|
||
|
|
$data['start_date'],
|
||
|
|
$data['deadline_rule_type'],
|
||
|
|
$data['deadline_offset_days'],
|
||
|
|
$data['deadline_weekday'],
|
||
|
|
$data['deadline_monthday'],
|
||
|
|
$data['fulfillment_mode'],
|
||
|
|
$data['certificate_enabled'],
|
||
|
|
$data['certificate_title'],
|
||
|
|
$data['certificate_validity_mode'],
|
||
|
|
$data['certificate_validity_days'],
|
||
|
|
$data['show_cycle_period'],
|
||
|
|
$data['active'],
|
||
|
|
$data['modified_by'],
|
||
|
|
$taskId,
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function softDelete(int $taskId, int $modifiedBy): void
|
||
|
|
{
|
||
|
|
TaskCertDb::execute(
|
||
|
|
'UPDATE task_definition SET active = 0, modified_by = ? WHERE id = ?',
|
||
|
|
'ii',
|
||
|
|
[$modifiedBy, $taskId]
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function hardDelete(int $taskId): int
|
||
|
|
{
|
||
|
|
return TaskCertDb::execute(
|
||
|
|
'DELETE FROM task_definition WHERE id = ?',
|
||
|
|
'i',
|
||
|
|
[$taskId]
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function getHardDeleteImpact(int $taskId): array
|
||
|
|
{
|
||
|
|
$assignmentCount = (int) TaskCertDb::scalar(
|
||
|
|
'SELECT COUNT(*)
|
||
|
|
FROM task_assignment
|
||
|
|
WHERE task_id = ?',
|
||
|
|
'i',
|
||
|
|
[$taskId]
|
||
|
|
);
|
||
|
|
|
||
|
|
$submissionCount = (int) TaskCertDb::scalar(
|
||
|
|
'SELECT COUNT(*)
|
||
|
|
FROM task_submission s
|
||
|
|
INNER JOIN task_assignment a ON a.id = s.assignment_id
|
||
|
|
WHERE a.task_id = ?',
|
||
|
|
'i',
|
||
|
|
[$taskId]
|
||
|
|
);
|
||
|
|
|
||
|
|
$certificateCount = (int) TaskCertDb::scalar(
|
||
|
|
'SELECT COUNT(*)
|
||
|
|
FROM task_certificate
|
||
|
|
WHERE task_id = ?',
|
||
|
|
'i',
|
||
|
|
[$taskId]
|
||
|
|
);
|
||
|
|
|
||
|
|
$methodCount = (int) TaskCertDb::scalar(
|
||
|
|
'SELECT COUNT(*)
|
||
|
|
FROM task_method
|
||
|
|
WHERE task_id = ?',
|
||
|
|
'i',
|
||
|
|
[$taskId]
|
||
|
|
);
|
||
|
|
|
||
|
|
$escalationEventCount = (int) TaskCertDb::scalar(
|
||
|
|
'SELECT COUNT(*)
|
||
|
|
FROM task_escalation_event e
|
||
|
|
INNER JOIN task_assignment a ON a.id = e.assignment_id
|
||
|
|
WHERE a.task_id = ?',
|
||
|
|
'i',
|
||
|
|
[$taskId]
|
||
|
|
);
|
||
|
|
|
||
|
|
return [
|
||
|
|
'assignments' => $assignmentCount,
|
||
|
|
'submissions' => $submissionCount,
|
||
|
|
'certificates' => $certificateCount,
|
||
|
|
'methods' => $methodCount,
|
||
|
|
'escalation_events' => $escalationEventCount,
|
||
|
|
];
|
||
|
|
}
|
||
|
|
|
||
|
|
public function findById(int $taskId): ?array
|
||
|
|
{
|
||
|
|
return TaskCertDb::fetchOne('SELECT * FROM task_definition WHERE id = ?', 'i', [$taskId]);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function listForAdmin(string $searchQuery = '', int $categoryIdFilter = 0): array
|
||
|
|
{
|
||
|
|
$searchQuery = trim($searchQuery);
|
||
|
|
$categoryIdFilter = max(0, $categoryIdFilter);
|
||
|
|
|
||
|
|
$sql = 'SELECT
|
||
|
|
d.*,
|
||
|
|
MAX(tc.name) AS task_category_name,
|
||
|
|
COUNT(a.id) AS assignment_count,
|
||
|
|
SUM(CASE WHEN a.status IN ("open", "in_progress", "approval_pending", "overdue") THEN 1 ELSE 0 END) AS open_count,
|
||
|
|
SUM(CASE WHEN a.status = "completed" THEN 1 ELSE 0 END) AS completed_count
|
||
|
|
FROM task_definition d
|
||
|
|
LEFT JOIN task_category tc ON tc.id = d.task_category_id
|
||
|
|
LEFT JOIN task_assignment a ON a.task_id = d.id';
|
||
|
|
|
||
|
|
$conditions = [];
|
||
|
|
$types = '';
|
||
|
|
$params = [];
|
||
|
|
|
||
|
|
if ($categoryIdFilter > 0) {
|
||
|
|
$conditions[] = 'd.task_category_id = ?';
|
||
|
|
$types .= 'i';
|
||
|
|
$params[] = $categoryIdFilter;
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($searchQuery !== '') {
|
||
|
|
$searchLike = '%' . $searchQuery . '%';
|
||
|
|
$conditions[] = '(
|
||
|
|
d.title LIKE ?
|
||
|
|
OR d.description LIKE ?
|
||
|
|
OR COALESCE(tc.name, "") LIKE ?
|
||
|
|
OR CAST(d.id AS CHAR) LIKE ?
|
||
|
|
)';
|
||
|
|
$types .= 'ssss';
|
||
|
|
$params[] = $searchLike;
|
||
|
|
$params[] = $searchLike;
|
||
|
|
$params[] = $searchLike;
|
||
|
|
$params[] = $searchLike;
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($conditions !== []) {
|
||
|
|
$sql .= ' WHERE ' . implode(' AND ', $conditions);
|
||
|
|
}
|
||
|
|
|
||
|
|
$sql .= ' GROUP BY d.id
|
||
|
|
ORDER BY d.active DESC, d.created_at DESC';
|
||
|
|
|
||
|
|
return TaskCertDb::fetchAll($sql, $types, $params);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function listForResponsible(int $contactId): array
|
||
|
|
{
|
||
|
|
if ($contactId <= 0) {
|
||
|
|
return [];
|
||
|
|
}
|
||
|
|
|
||
|
|
$sql = 'SELECT
|
||
|
|
d.*,
|
||
|
|
MAX(tc.name) AS task_category_name,
|
||
|
|
COUNT(a.id) AS assignment_count,
|
||
|
|
SUM(CASE WHEN a.status IN ("open", "in_progress", "approval_pending", "overdue") THEN 1 ELSE 0 END) AS open_count,
|
||
|
|
SUM(CASE WHEN a.status = "completed" THEN 1 ELSE 0 END) AS completed_count
|
||
|
|
FROM task_definition d
|
||
|
|
INNER JOIN task_responsible tr ON tr.task_id = d.id AND tr.main_contact_id = ?
|
||
|
|
LEFT JOIN task_category tc ON tc.id = d.task_category_id
|
||
|
|
LEFT JOIN task_assignment a ON a.task_id = d.id
|
||
|
|
GROUP BY d.id
|
||
|
|
ORDER BY d.active DESC, d.created_at DESC';
|
||
|
|
|
||
|
|
return TaskCertDb::fetchAll($sql, 'i', [$contactId]);
|
||
|
|
}
|
||
|
|
|
||
|
|
public function listActive(): array
|
||
|
|
{
|
||
|
|
return TaskCertDb::fetchAll(
|
||
|
|
'SELECT * FROM task_definition WHERE active = 1 ORDER BY id DESC'
|
||
|
|
);
|
||
|
|
}
|
||
|
|
}
|