<?php
namespace Leantime\Domain\Tickets\Repositories;
use Carbon\CarbonImmutable;
use Illuminate\Contracts\Container\BindingResolutionException;
use Illuminate\Support\Facades\Cache;
use Leantime\Core\Db\Db as DbCore;
use Leantime\Core\Events\DispatchesEvents as EventhelperCore;
use Leantime\Core\Language as LanguageCore;
use Leantime\Domain\Users\Services\Users;
use PDO;
class Tickets
{
use EventhelperCore;
public ?object $result = null;
public ?object $tickets = null;
private DbCore $db;
public array $statusClasses = ['3' => 'label-info', '1' => 'label-important', '4' => 'label-warning', '2' => 'label-warning', '0' => 'label-success', '-1' => 'label-default'];
public array $statusListSeed = [
3 => [
'name' => 'status.new',
'class' => 'label-info',
'statusType' => 'NEW',
'kanbanCol' => true,
'sortKey' => 1,
],
1 => [
'name' => 'status.blocked',
'class' => 'label-important',
'statusType' => 'INPROGRESS',
'kanbanCol' => true,
'sortKey' => 2,
],
4 => [
'name' => 'status.in_progress',
'class' => 'label-warning',
'statusType' => 'INPROGRESS',
'kanbanCol' => true,
'sortKey' => 3,
],
2 => [
'name' => 'status.waiting_for_approval',
'class' => 'label-warning',
'statusType' => 'INPROGRESS',
'kanbanCol' => true,
'sortKey' => 4,
],
0 => [
'name' => 'status.done',
'class' => 'label-success',
'statusType' => 'DONE',
'kanbanCol' => true,
'sortKey' => 5,
],
-1 => [
'name' => 'status.archived',
'class' => 'label-default',
'statusType' => 'DONE',
'kanbanCol' => false,
'sortKey' => 6,
],
];
public array $priority = ['1' => 'Critical', '2' => 'High', '3' => 'Medium', '4' => 'Low', '5' => 'Lowest'];
public array $efforts = ['0.5' => '< 2min', '1' => 'XS', '2' => 'S', '3' => 'M', '5' => 'L', '8' => 'XL', '13' => 'XXL'];
public array $type = ['task', 'subtask', 'story', 'bug'];
public array $typeIcons = ['story' => 'fa-book', 'task' => 'fa-check-square', 'subtask' => 'fa-diagram-successor', 'bug' => 'fa-bug'];
* @var bool
*/
private int|bool $page = 0;
* @var bool
*/
public int|bool $rowsPerPage = 10;
private string $limitSelect = '';
public string $numPages = '';
public string $sortBy = 'date';
private LanguageCore $language;
* __construct - get db connection
*
* @return void
*/
public function __construct(DbCore $db, LanguageCore $language)
{
$this->db = $db;
$this->language = $language;
}
* @return array|array[]
*/
public function getStateLabels($projectId = null): array
{
if (Cache::has('projectsettings.'.$projectId.'.ticketlabels')) {
return Cache::get('projectsettings.'.$projectId.'.ticketlabels');
}
if ($projectId == null) {
$projectId = session('currentProject');
}
$sql = 'SELECT
value
FROM zp_settings WHERE `key` = :key
LIMIT 1';
$stmn = $this->db->database->prepare($sql);
$stmn->bindvalue(':key', 'projectsettings.'.$projectId.'.ticketlabels', PDO::PARAM_STR);
$stmn->execute();
$values = $stmn->fetch();
$stmn->closeCursor();
$labels = [];
$statusList = $this->statusListSeed;
if ($values !== false) {
$statusList = [];
$statusList[-1] = $this->statusListSeed[-1];
foreach (unserialize($values['value']) as $key => $status) {
if (is_int($key)) {
if (! is_array($status)) {
$statusList[$key] = $this->statusListSeed[$key];
if (is_array($statusList[$key]) && isset($statusList[$key]['name']) && $key !== -1) {
$statusList[$key]['name'] = $status;
}
} else {
$statusList[$key] = $status;
}
}
}
} else {
foreach ($statusList as &$status) {
$status['name'] = $this->language->__($status['name']);
}
}
uasort($statusList, function ($a, $b) {
return $a['sortKey'] <=> $b['sortKey'];
});
Cache::put('projectsettings.'.$projectId.'.ticketlabels', $statusList, 3600);
return $statusList;
}
public function getStatusList(): mixed
{
return $this->statusListSeed;
}
* @return string[]
*/
public function getStatusListGroupedByType($projectId): array
{
$statusByType = [
'DONE' => [],
'INPROGRESS' => [],
'NEW' => [],
];
$states = $this->getStateLabels($projectId);
foreach ($states as $key => $value) {
$statusByType[$value['statusType']][] = $key;
}
$doneQuery = 'IN('.implode(',', $statusByType['DONE']).')';
$inProgressQuery = 'IN('.implode(',', $statusByType['INPROGRESS']).')';
$newQuery = 'IN('.implode(',', $statusByType['NEW']).')';
$openTodos = 'IN('.implode(',', array_merge($statusByType['NEW'], $statusByType['INPROGRESS'])).')';
if ($doneQuery == 'IN()') {
$doneQuery = 'IN(FALSE)';
}
if ($inProgressQuery == 'IN()') {
$inProgressQuery = 'IN(FALSE)';
}
if ($newQuery == 'IN()') {
$newQuery = 'IN(FALSE)';
}
if ($openTodos == 'IN()') {
$openTodos = 'IN(FALSE)';
}
$statusByTypeQuery = [
'DONE' => $doneQuery,
'INPROGRESS' => $inProgressQuery,
'NEW' => $newQuery,
'ALLOPEN' => $openTodos,
];
return $statusByTypeQuery;
}
public function getStatusIdByName($statusLabel, $projectId): int|false
{
$statusList = $this->getStateLabels($projectId);
foreach ($statusList as $key => $status) {
if ($status['name'] == $statusLabel) {
return $key;
}
}
return false;
}
* getAll - get all Tickets, depending on userrole
*
* @throws BindingResolutionException
*/
public function getAll(int $limit = 9999): false|array
{
$id = session('userdata.id');
$values = $this->getUsersTickets($id, $limit);
return $values;
}
* @throws BindingResolutionException
*/
public function getUsersTickets($id, $limit): false|array
{
$users = app()->make(Users::class);
$user = $users->getUser($id);
$sql = "SELECT
ticket.id,
ticket.headline,
ticket.type,
ticket.description,
ticket.date,
ticket.dateToFinish,
ticket.projectId,
ticket.priority,
ticket.status,
project.name as projectName,
client.name as clientName,
client.name as clientName,
t1.id AS authorId,
t1.firstname AS authorFirstname,
t1.lastname AS authorLastname,
t2.id AS editorId,
t2.firstname AS editorFirstname,
t2.lastname AS editorLastname
FROM
zp_tickets AS ticket
LEFT JOIN zp_projects as project ON ticket.projectId = project.id
LEFT JOIN zp_clients as client ON project.clientId = client.id
LEFT JOIN zp_user AS t1 ON ticket.userId = t1.id
LEFT JOIN zp_user AS t2 ON ticket.editorId = t2.id
WHERE (
ticket.projectId IN (SELECT projectId FROM zp_relationuserproject WHERE zp_relationuserproject.userId = :id)
OR project.psettings = 'all'
OR (project.psettings = 'clients' AND project.clientId = :clientId)
)
AND ticket.type <> 'milestone'
ORDER BY ticket.id DESC";
if ($limit > -1) {
$sql .= ' LIMIT :limit';
}
$stmn = $this->db->database->prepare($sql);
$stmn->bindValue(':id', $id, PDO::PARAM_STR);
$stmn->bindValue(':clientId', $user['clientId'] ?? '', PDO::PARAM_STR);
if ($limit > -1) {
$stmn->bindValue(':limit', $limit, PDO::PARAM_INT);
}
$stmn->execute();
$values = $stmn->fetchAll();
$stmn->closeCursor();
return $values;
}
* getAllBySearchCriteria - get Tickets by search criteria array
*
* @param null $limit
*/
public function getAllBySearchCriteria(array $searchCriteria, string $sort = 'standard', $limit = null, $includeCounts = true, $offset = null): bool|array
{
$query = "
SELECT
zp_tickets.id,
zp_tickets.headline,
zp_tickets.description,
zp_tickets.date,
zp_tickets.sprint,
zp_sprints.name as sprintName,
zp_tickets.storypoints,
zp_tickets.sortindex,
zp_tickets.dateToFinish,
zp_tickets.projectId,
zp_tickets.priority,
IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
zp_tickets.status,
zp_tickets.tags,
zp_tickets.editorId,
zp_tickets.dependingTicketId,
zp_tickets.milestoneid,
zp_tickets.planHours,
zp_tickets.editFrom,
zp_tickets.editTo,
zp_tickets.hourRemaining,
COALESCE(ROUND(timesheet_agg.total_hours, 2), 0) AS bookedHours,
zp_projects.name AS projectName,
zp_clients.name AS clientName,
zp_clients.id AS clientId,
t1.id AS authorId,
t1.lastname AS authorLastname,
t1.firstname AS authorFirstname,
t1.profileId AS authorProfileId,
t2.firstname AS editorFirstname,
t2.lastname AS editorLastname,
t2.profileId AS editorProfileId,
milestone.headline AS milestoneHeadline,
IF((milestone.tags IS NULL OR milestone.tags = ''), 'var(--grey)', milestone.tags) AS milestoneColor,";
if ($includeCounts) {
$query .= '
COALESCE(comment_agg.comment_count, 0) AS commentCount,
COALESCE(file_agg.file_count, 0) AS fileCount,
COALESCE(subtask_agg.subtask_count, 0) AS subtaskCount,
';
} else {
$query .= '
0 AS commentCount,
0 AS fileCount,
0 AS subtaskCount,
';
}
$query .= "
parent.headline AS parentHeadline
FROM
zp_tickets
LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
LEFT JOIN zp_user AS t1 ON zp_tickets.userId = t1.id
LEFT JOIN zp_user AS t2 ON zp_tickets.editorId = t2.id
LEFT JOIN zp_user AS requestor ON requestor.id = :requestorId
LEFT JOIN zp_sprints ON zp_tickets.sprint = zp_sprints.id
LEFT JOIN zp_tickets AS milestone ON zp_tickets.milestoneid = milestone.id AND zp_tickets.milestoneid > 0 AND milestone.type = 'milestone'
LEFT JOIN zp_tickets AS parent ON zp_tickets.dependingTicketId = parent.id";
$query .= '
LEFT JOIN (
SELECT ticketId, SUM(hours) as total_hours
FROM zp_timesheets
GROUP BY ticketId
) AS timesheet_agg ON zp_tickets.id = timesheet_agg.ticketId';
if ($includeCounts) {
$query .= "
LEFT JOIN (
SELECT moduleId, COUNT(*) as comment_count
FROM zp_comment
WHERE module = 'ticket'
GROUP BY moduleId
) AS comment_agg ON zp_tickets.id = comment_agg.moduleId
LEFT JOIN (
SELECT moduleId, COUNT(*) as file_count
FROM zp_file
WHERE module = 'ticket'
GROUP BY moduleId
) AS file_agg ON zp_tickets.id = file_agg.moduleId
LEFT JOIN (
SELECT dependingTicketId, COUNT(*) as subtask_count
FROM zp_tickets
WHERE dependingTicketId > 0
GROUP BY dependingTicketId
) AS subtask_agg ON zp_tickets.id = subtask_agg.dependingTicketId";
}
$query .= "
LEFT JOIN zp_relationuserproject AS rup ON zp_tickets.projectId = rup.projectId AND rup.userId = :userId
WHERE (
rup.projectId IS NOT NULL
OR zp_projects.psettings = 'all'
OR (zp_projects.psettings = 'clients' AND zp_projects.clientId = :clientId)
OR (requestor.role >= 40)
)
";
if (isset($searchCriteria['dateFrom']) && $searchCriteria['dateFrom'] != '') {
$query .= ' AND zp_tickets.date > :dateFrom';
}
if (isset($searchCriteria['dateTo']) && $searchCriteria['dateTo'] != '') {
$query .= ' AND zp_tickets.date < :dateTo';
}
if (isset($searchCriteria['excludeType']) && $searchCriteria['excludeType'] != '') {
$query .= ' AND zp_tickets.type <> :excludeType';
}
if (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
$query .= ' AND zp_tickets.projectId = :projectId';
}
if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
$editorIdIn = DbCore::arrayToPdoBindingString('users', count(explode(',', $searchCriteria['users'])));
$query .= ' AND zp_tickets.editorId IN('.$editorIdIn.')';
}
if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
$milestoneIn = DbCore::arrayToPdoBindingString('milestone', count(explode(',', $searchCriteria['milestone'])));
$query .= ' AND zp_tickets.milestoneid IN('.$milestoneIn.')';
}
if (isset($searchCriteria['status']) && $searchCriteria['status'] == 'all') {
$query .= ' ';
} elseif (isset($searchCriteria['status']) && $searchCriteria['status'] != '') {
$statusArray = explode(',', $searchCriteria['status']);
if (array_search('not_done', $statusArray) !== false) {
if ($searchCriteria['currentProject'] != '') {
$statusLabels = $this->getStateLabels($searchCriteria['currentProject']);
$statusList = [];
foreach ($statusLabels as $key => $status) {
if ($status['statusType'] !== 'DONE') {
$statusList[] = $key;
}
}
$query .= ' AND zp_tickets.status IN('.implode(',', $statusList).')';
}
} else {
$statusIn = DbCore::arrayToPdoBindingString(
'status',
count(explode(',', $searchCriteria['status']))
);
$query .= ' AND zp_tickets.status IN('.$statusIn.')';
}
} else {
$query .= ' AND zp_tickets.status <> -1';
}
if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
$typeIn = DbCore::arrayToPdoBindingString('type', count(explode(',', strtolower($searchCriteria['type']))));
$query .= ' AND LOWER(zp_tickets.type) IN('.$typeIn.')';
}
if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
$priorityIn = DbCore::arrayToPdoBindingString('priority', count(explode(',', strtolower($searchCriteria['priority']))));
$query .= ' AND LOWER(zp_tickets.priority) IN('.$priorityIn.')';
}
if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
$query .= ' AND (FIND_IN_SET(:termStandard, zp_tickets.tags) OR zp_tickets.headline LIKE :termWild OR zp_tickets.description LIKE :termWild OR zp_tickets.id LIKE :termWild)';
}
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] > 0 && $searchCriteria['sprint'] != 'all') {
$sprintIn = DbCore::arrayToPdoBindingString('sprint', count(explode(',', $searchCriteria['sprint'])));
$query .= ' AND zp_tickets.sprint IN('.$sprintIn.')';
}
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] == 'backlog') {
$query .= " AND (zp_tickets.sprint IS NULL OR zp_tickets.sprint = '' OR zp_tickets.sprint = -1)";
}
$query .= ' GROUP BY zp_tickets.id ';
if ($sort == 'standard') {
$query .= ' ORDER BY zp_tickets.sortindex ASC, zp_tickets.id DESC';
} elseif ($sort == 'kanbansort') {
$query .= ' ORDER BY zp_tickets.kanbanSortIndex ASC, zp_tickets.id DESC';
} elseif ($sort == 'duedate') {
$query .= " ORDER BY (zp_tickets.dateToFinish = '0000-00-00 00:00:00'), zp_tickets.dateToFinish ASC, zp_tickets.sortindex ASC, zp_tickets.id DESC";
} elseif ($sort == 'priority') {
$query .= ' ORDER BY zp_tickets.priority ASC, zp_tickets.dateToFinish ASC, zp_tickets.sortindex ASC, zp_tickets.id DESC';
} elseif ($sort == 'date') {
$query .= ' ORDER BY zp_tickets.date DESC, zp_tickets.sortindex ASC, zp_tickets.id DESC';
}
if ($limit !== null && $limit > 0) {
if ($offset !== null && $offset > 0) {
$query .= ' LIMIT :offset, :limit';
} else {
$query .= ' LIMIT :limit';
}
}
$stmn = $this->db->database->prepare($query);
if (isset($searchCriteria['dateFrom']) && $searchCriteria['dateFrom'] != '') {
$stmn->bindValue(':dateFrom', $searchCriteria['dateFrom'], PDO::PARAM_STR);
}
if (isset($searchCriteria['dateTo']) && $searchCriteria['dateTo'] != '') {
$stmn->bindValue(':dateTo', $searchCriteria['dateTo'], PDO::PARAM_STR);
}
if (isset($searchCriteria['excludeType']) && $searchCriteria['excludeType'] != '') {
$stmn->bindValue(':excludeType', $searchCriteria['excludeType'], PDO::PARAM_STR);
}
if (isset($searchCriteria['currentUser'])) {
$stmn->bindValue(':userId', $searchCriteria['currentUser'], PDO::PARAM_INT);
} else {
$stmn->bindValue(':userId', session('userdata.id') ?? '-1', PDO::PARAM_INT);
}
if (isset($searchCriteria['currentClient'])) {
$stmn->bindValue(':clientId', $searchCriteria['currentClient'], PDO::PARAM_INT);
} else {
$stmn->bindValue(':clientId', session('userdata.clientId') ?? '-1', PDO::PARAM_INT);
}
if (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
$stmn->bindValue(':projectId', $searchCriteria['currentProject'], PDO::PARAM_INT);
}
if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
foreach (explode(',', $searchCriteria['milestone']) as $key => $milestone) {
$stmn->bindValue(':milestone'.$key, $milestone, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
foreach (explode(',', $searchCriteria['type']) as $key => $type) {
$stmn->bindValue(':type'.$key, $type, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
foreach (explode(',', $searchCriteria['priority']) as $key => $priority) {
$stmn->bindValue(':priority'.$key, $priority, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
foreach (explode(',', $searchCriteria['users']) as $key => $user) {
$stmn->bindValue(':users'.$key, $user, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['status']) && $searchCriteria['status'] != 'all') {
$statusArray = explode(',', $searchCriteria['status']);
if ($searchCriteria['status'] != '' && array_search('not_done', $statusArray) === false) {
foreach (explode(',', $searchCriteria['status']) as $key => $status) {
$stmn->bindValue(':status'.$key, $status, PDO::PARAM_STR);
}
}
}
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] > 0 && $searchCriteria['sprint'] != 'all') {
foreach (explode(',', $searchCriteria['sprint']) as $key => $sprint) {
$stmn->bindValue(':sprint'.$key, $sprint, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
$termWild = '%'.$searchCriteria['term'].'%';
$stmn->bindValue(':termWild', $termWild, PDO::PARAM_STR);
$stmn->bindValue(':termStandard', $searchCriteria['term'], PDO::PARAM_STR);
}
if ($limit !== null && $limit > 0) {
$stmn->bindValue(':limit', $limit, PDO::PARAM_INT);
if ($offset !== null && $offset > 0) {
$stmn->bindValue(':offset', $offset, PDO::PARAM_INT);
}
}
if (session()->exists('userdata')) {
$stmn->bindValue(':requestorId', session('userdata.id'), PDO::PARAM_INT);
} else {
$stmn->bindValue(':requestorId', -1, PDO::PARAM_INT);
}
$stmn->execute();
$values = $stmn->fetchAll(PDO::FETCH_ASSOC);
$stmn->closeCursor();
return $values;
}
public function simpleTicketQuery(?int $userId, ?int $projectId, array $types = []): array|false
{
$query = <<<'SQL'
SELECT
zp_tickets.id,
zp_tickets.headline,
zp_tickets.description,
zp_tickets.date,
zp_tickets.sprint,
zp_tickets.storypoints,
zp_tickets.sortindex,
zp_tickets.dateToFinish,
zp_tickets.projectId,
zp_tickets.priority,
IF(zp_tickets.type <> "", zp_tickets.type, "task") AS type,
zp_tickets.status,
zp_tickets.tags,
zp_tickets.userId,
zp_tickets.editorId,
zp_tickets.dependingTicketId,
zp_tickets.milestoneid,
milestones.headline AS milestoneHeadline,
zp_tickets.planHours,
zp_tickets.editFrom,
zp_tickets.editTo,
zp_tickets.hourRemaining,
zp_projects.name AS projectName,
zp_projects.details AS projectDescription
FROM
zp_tickets
LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
LEFT JOIN zp_user AS requestor ON requestor.id = :requestorId
LEFT JOIN zp_tickets AS milestones ON zp_tickets.milestoneid = milestones.id
WHERE (
zp_tickets.projectId IN (SELECT projectId FROM zp_relationuserproject WHERE zp_relationuserproject.userId = :requestorId)
OR zp_projects.psettings = 'all'
OR (zp_projects.psettings = 'clients' AND zp_projects.clientId = :clientId)
OR (requestor.role >= 40)
)
SQL;
if (isset($projectId) && $projectId > 0) {
$query .= ' AND zp_tickets.projectId = :projectId';
}
if (isset($userId) && $userId > 0) {
$query .= ' AND zp_tickets.editorId = :userId';
}
if (count($types) > 0) {
$typeIn = DbCore::arrayToPdoBindingString('types', count($types));
$query .= ' AND zp_tickets.type IN('.$typeIn.')';
}
$query .= ' ORDER BY zp_tickets.dateToFinish DESC, zp_tickets.sortindex ASC, zp_tickets.id DESC';
$stmn = $this->db->database->prepare($query);
if (isset($projectId) && $projectId > 0) {
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
}
if (isset($userId) && $userId > 0) {
$stmn->bindValue(':userId', $userId, PDO::PARAM_INT);
}
if (count($types) > 0) {
foreach ($types as $key => $type) {
$stmn->bindValue(':types'.$key, $type, PDO::PARAM_STR);
}
}
$stmn->bindValue(':clientId', session('userdata.clientId') ?? '-1', PDO::PARAM_INT);
if (session()->exists('userdata')) {
$stmn->bindValue(':requestorId', session('userdata.id'), PDO::PARAM_INT);
} else {
$stmn->bindValue(':requestorId', -1, PDO::PARAM_INT);
}
$stmn->execute();
$values = $stmn->fetchAll();
$stmn->closeCursor();
return $values;
}
public function getScheduledTasks(CarbonImmutable $dateFrom, CarbonImmutable $dateTo, ?int $userId = null)
{
$query = <<<'SQL'
SELECT
zp_tickets.id,
zp_tickets.headline,
zp_tickets.description,
zp_tickets.date,
zp_tickets.sprint,
zp_tickets.storypoints,
zp_tickets.sortindex,
zp_tickets.dateToFinish,
zp_tickets.projectId,
zp_tickets.priority,
IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
zp_tickets.status,
zp_tickets.tags,
zp_tickets.editorId,
zp_tickets.dependingTicketId,
zp_tickets.milestoneid,
zp_tickets.planHours,
zp_tickets.editFrom,
zp_tickets.editTo,
zp_tickets.hourRemaining
FROM
zp_tickets
LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
LEFT JOIN zp_user AS requestor ON requestor.id = :requestorId
WHERE
(
zp_tickets.projectId IN (SELECT projectId FROM zp_relationuserproject WHERE zp_relationuserproject.userId = :userId)
OR zp_projects.psettings = 'all'
OR (zp_projects.psettings = 'clients' AND zp_projects.clientId = :clientId)
OR (requestor.role >= 40)
)
AND zp_tickets.type <> 'milestone'
SQL;
if (isset($userId)) {
$query .= ' AND zp_tickets.editorId = :userId';
}
$query .= ' AND ((zp_tickets.editFrom BETWEEN :dateFrom AND :dateTo) OR (zp_tickets.editTo BETWEEN :dateFrom AND :dateTo))';
$stmn = $this->db->database->prepare($query);
if (isset($userId)) {
$stmn->bindValue(':userId', $userId, PDO::PARAM_INT);
} else {
$stmn->bindValue(':userId', session('userdata.id') ?? '-1', PDO::PARAM_INT);
}
$stmn->bindValue(':dateFrom', $dateFrom->formatDateTimeForDb(), PDO::PARAM_STR);
$stmn->bindValue(':dateTo', $dateTo->formatDateTimeForDb(), PDO::PARAM_STR);
if (session()->exists('userdata')) {
$stmn->bindValue(':requestorId', session('userdata.id'), PDO::PARAM_INT);
} else {
$stmn->bindValue(':requestorId', -1, PDO::PARAM_INT);
}
$stmn->bindValue(':clientId', session('userdata.clientId') ?? '-1', PDO::PARAM_INT);
$stmn->execute();
$values = $stmn->fetchAll();
$stmn->closeCursor();
return $values;
}
public function getAllByProjectId($projectId): false|array
{
$query = "SELECT
zp_tickets.id,
zp_tickets.headline,
IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
zp_tickets.description,
zp_tickets.date,
zp_tickets.dateToFinish,
zp_tickets.projectId,
zp_tickets.priority,
zp_tickets.status,
zp_tickets.sprint,
zp_tickets.storypoints,
zp_tickets.hourRemaining,
zp_tickets.acceptanceCriteria,
zp_tickets.userId,
zp_tickets.editorId,
zp_tickets.planHours,
zp_tickets.tags,
zp_tickets.url,
zp_tickets.editFrom,
zp_tickets.editTo,
zp_tickets.dependingTicketId,
zp_tickets.milestoneid,
zp_projects.name AS projectName,
zp_clients.name AS clientName,
zp_user.firstname AS userFirstname,
zp_user.lastname AS userLastname,
t3.firstname AS editorFirstname,
t3.lastname AS editorLastname
FROM
zp_tickets
LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
WHERE
zp_tickets.projectId = :projectId";
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
$stmn->execute();
$values = $stmn->fetchAll(PDO::FETCH_CLASS, '\Leantime\Domain\Tickets\Models\Tickets');
$stmn->closeCursor();
return $values;
}
public function getTags($projectId): false|array
{
$query = "SELECT
zp_tickets.tags
FROM
zp_tickets LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
WHERE
zp_tickets.projectId = :projectId AND zp_tickets.type <> 'milestone'";
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
$stmn->execute();
$values = $stmn->fetchAll();
$stmn->closeCursor();
return $values;
}
* getTicket - get a specific Ticket depending on the role
*/
public function getTicket($id): \Leantime\Domain\Tickets\Models\Tickets|bool
{
$query = "SELECT
zp_tickets.id,
zp_tickets.headline,
IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
zp_tickets.description,
zp_tickets.date,
zp_tickets.dateToFinish,
zp_tickets.projectId,
zp_tickets.priority,
zp_tickets.status,
zp_tickets.sprint,
zp_tickets.storypoints,
zp_tickets.hourRemaining,
zp_tickets.acceptanceCriteria,
zp_tickets.userId,
zp_tickets.editorId,
zp_tickets.planHours,
zp_tickets.tags,
zp_tickets.url,
zp_tickets.editFrom,
zp_tickets.editTo,
zp_tickets.dependingTicketId,
zp_tickets.milestoneid,
milestones.headline AS milestoneHeadline,
zp_projects.name AS projectName,
zp_projects.details AS projectDescription,
zp_clients.name AS clientName,
zp_user.firstname AS userFirstname,
zp_user.lastname AS userLastname,
t3.firstname AS editorFirstname,
t3.lastname AS editorLastname,
parent.headline AS parentHeadline
FROM
zp_tickets
LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
LEFT JOIN zp_tickets AS parent on zp_tickets.dependingTicketId = parent.id
LEFT JOIN zp_tickets AS milestones on zp_tickets.milestoneid = milestones.id
WHERE
zp_tickets.id = :ticketId
LIMIT 1";
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':ticketId', $id, PDO::PARAM_INT);
$stmn->execute();
$values = $stmn->fetchObject('\Leantime\Domain\Tickets\Models\Tickets');
$stmn->closeCursor();
return $values;
}
public function getAllSubtasks($id): false|array
{
$query = "SELECT
zp_tickets.id,
zp_tickets.headline,
IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
zp_tickets.description,
zp_tickets.date,
DATE_FORMAT(zp_tickets.date, '%Y,%m,%e') AS timelineDate,
DATE_FORMAT(zp_tickets.dateToFinish, '%Y,%m,%e') AS timelineDateToFinish,
zp_tickets.dateToFinish,
zp_tickets.projectId,
zp_tickets.priority,
zp_tickets.status,
zp_tickets.sprint,
zp_tickets.storypoints,
IFNULL(zp_tickets.hourRemaining, 0) AS hourRemaining,
zp_tickets.acceptanceCriteria,
zp_tickets.userId,
zp_tickets.editorId,
IFNULL(zp_tickets.planHours, 0) AS planHours,
zp_tickets.tags,
zp_tickets.url,
zp_tickets.editFrom,
zp_tickets.editTo,
zp_tickets.dependingTicketId,
zp_tickets.milestoneid,
zp_projects.name AS projectName,
zp_clients.name AS clientName,
zp_user.firstname AS userFirstname,
zp_user.lastname AS userLastname,
t3.firstname AS editorFirstname,
t3.lastname AS editorLastname
FROM
zp_tickets
LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
WHERE
zp_tickets.dependingTicketId = :ticketId
ORDER BY zp_tickets.date DESC";
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':ticketId', $id, PDO::PARAM_INT);
$stmn->execute();
$values = $stmn->fetchAll();
$stmn->closeCursor();
return $values;
}
public function getAllPossibleParents(\Leantime\Domain\Tickets\Models\Tickets $ticket, $projectId): false|array
{
$query = "SELECT
zp_tickets.id,
zp_tickets.headline,
IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
zp_tickets.description,
zp_tickets.date,
DATE_FORMAT(zp_tickets.date, '%Y,%m,%e') AS timelineDate,
DATE_FORMAT(zp_tickets.dateToFinish, '%Y,%m,%e') AS timelineDateToFinish,
zp_tickets.dateToFinish,
zp_tickets.projectId,
zp_tickets.priority,
zp_tickets.status,
zp_tickets.sprint,
zp_tickets.storypoints,
IFNULL(zp_tickets.hourRemaining, 0) AS hourRemaining,
zp_tickets.acceptanceCriteria,
zp_tickets.userId,
zp_tickets.editorId,
IFNULL(zp_tickets.planHours, 0) AS planHours,
zp_tickets.tags,
zp_tickets.url,
zp_tickets.editFrom,
zp_tickets.editTo,
zp_tickets.dependingTicketId,
zp_tickets.milestoneid,
zp_projects.name AS projectName,
zp_clients.name AS clientName,
zp_user.firstname AS userFirstname,
zp_user.lastname AS userLastname,
t3.firstname AS editorFirstname,
t3.lastname AS editorLastname
FROM
zp_tickets
LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
WHERE
zp_tickets.id <> :ticketId
AND zp_tickets.type <> 'milestone'
AND (zp_tickets.dependingTicketId <> :ticketId OR zp_tickets.dependingTicketId IS NULL)
";
if ($projectId !== 0) {
$query .= ' AND zp_tickets.projectId = :projectId';
}
$query .= ' ORDER BY zp_tickets.date DESC';
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':ticketId', $ticket->id ?? 0, PDO::PARAM_INT);
$stmn->bindValue(':dependingId', $ticket->dependingTicketId ?? null, PDO::PARAM_INT);
if ($projectId !== 0) {
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
}
$stmn->execute();
$values = $stmn->fetchAll(PDO::FETCH_CLASS, 'Leantime\Domain\Tickets\Models\Tickets');
$stmn->closeCursor();
return $values;
}
* Gets all tasks grouped around milestones for timeline views
*/
public function getAllMilestones(array $searchCriteria, string $sort = 'standard'): false|array
{
$statusGroups = $this->getStatusListGroupedByType($searchCriteria['currentProject'] ?? session('currentProject'));
$query = "SELECT
zp_tickets.id,
zp_tickets.headline,
IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
zp_tickets.description,
zp_tickets.date,
DATE_FORMAT(zp_tickets.date, '%Y,%m,%e') AS timelineDate,
DATE_FORMAT(zp_tickets.dateToFinish, '%Y,%m,%e') AS timelineDateToFinish,
zp_tickets.dateToFinish,
zp_tickets.projectId,
zp_tickets.priority,
zp_tickets.status,
zp_tickets.sprint,
zp_tickets.storypoints,
zp_tickets.hourRemaining,
zp_tickets.acceptanceCriteria,
depMilestone.headline AS milestoneHeadline,
IF((depMilestone.tags IS NULL OR depMilestone.tags = ''), 'var(--grey)', depMilestone.tags) AS milestoneColor,
zp_tickets.userId,
zp_tickets.editorId,
zp_tickets.planHours,
IF((zp_tickets.tags IS NULL OR zp_tickets.tags = ''), 'var(--grey)', zp_tickets.tags) AS tags,
zp_tickets.url,
zp_tickets.editFrom,
zp_tickets.editTo,
zp_tickets.sortIndex,
zp_tickets.dependingTicketId,
zp_tickets.milestoneid,
zp_projects.name AS projectName,
zp_clients.name AS clientName,
zp_user.firstname AS userFirstname,
zp_user.lastname AS userLastname,
t3.firstname AS editorFirstname,
t3.lastname AS editorLastname,
t3.profileId AS editorProfileId
FROM
zp_tickets
LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
LEFT JOIN zp_tickets AS depMilestone ON zp_tickets.milestoneid = depMilestone.id
LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
LEFT JOIN zp_user AS requestor ON requestor.id = :requestorId
WHERE (zp_projects.state <> -1 OR zp_projects.state IS NULL)
AND (
zp_tickets.projectId IN (SELECT projectId FROM zp_relationuserproject WHERE zp_relationuserproject.userId = :userId)
OR zp_projects.psettings = 'all'
OR (zp_projects.psettings = 'clients' AND zp_projects.clientId = :clientId)
OR (requestor.role >= 40)
)
";
if (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
$query .= ' AND zp_tickets.projectId = :projectId';
}
if (isset($searchCriteria['clients']) && $searchCriteria['clients'] != 0 && $searchCriteria['clients'] != '' && $searchCriteria['clients'] != '') {
$clientIdIn = DbCore::arrayToPdoBindingString('clients', count(explode(',', $searchCriteria['clients'])));
$query .= ' AND zp_projects.clientId IN('.$clientIdIn.')';
}
if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
$editorIdIn = DbCore::arrayToPdoBindingString('users', count(explode(',', $searchCriteria['users'])));
$query .= ' AND zp_tickets.editorId IN('.$editorIdIn.')';
}
if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
$milestoneIn = DbCore::arrayToPdoBindingString('milestone', count(explode(',', $searchCriteria['milestone'])));
$query .= ' AND zp_tickets.milestoneid IN('.$milestoneIn.')';
}
if (isset($searchCriteria['status']) && $searchCriteria['status'] == 'all') {
$query .= ' ';
} elseif (isset($searchCriteria['status']) && $searchCriteria['status'] != '') {
$statusArray = explode(',', $searchCriteria['status']);
if (array_search('not_done', $statusArray) !== false) {
if ($searchCriteria['currentProject'] != '') {
$statusLabels = $this->getStateLabels($searchCriteria['currentProject']);
$statusList = [];
foreach ($statusLabels as $key => $status) {
if ($status['statusType'] !== 'DONE') {
$statusList[] = $key;
}
}
$query .= ' AND zp_tickets.status IN('.implode(',', $statusList).')';
}
} else {
$statusIn = DbCore::arrayToPdoBindingString(
'status',
count(explode(',', $searchCriteria['status']))
);
$query .= ' AND zp_tickets.status IN('.$statusIn.')';
}
} else {
$query .= ' AND zp_tickets.status <> -1';
}
if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
$typeIn = DbCore::arrayToPdoBindingString('type', count(explode(',', strtolower($searchCriteria['type']))));
$query .= ' AND LOWER(zp_tickets.type) IN('.$typeIn.')';
}
if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
$priorityIn = DbCore::arrayToPdoBindingString('priority', count(explode(',', strtolower($searchCriteria['priority']))));
$query .= ' AND LOWER(zp_tickets.priority) IN('.$priorityIn.')';
}
if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
$query .= ' AND (FIND_IN_SET(:termStandard, zp_tickets.tags) OR zp_tickets.headline LIKE :termWild OR zp_tickets.description LIKE :termWild OR zp_tickets.id LIKE :termWild)';
}
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] > 0 && $searchCriteria['sprint'] != 'all') {
$sprintIn = DbCore::arrayToPdoBindingString('sprint', count(explode(',', $searchCriteria['sprint'])));
$query .= ' AND (zp_tickets.sprint IN('.$sprintIn.") OR zp_tickets.type = 'milestone')";
}
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] == 'backlog') {
$query .= " AND (zp_tickets.sprint IS NULL OR zp_tickets.sprint = '' OR zp_tickets.sprint = -1 OR zp_tickets.type = 'milestone')";
}
$query .= ' GROUP BY
zp_tickets.id';
if ($sort == 'standard') {
$query .= ' ORDER BY zp_tickets.sortindex ASC, zp_tickets.editFrom ASC, zp_tickets.id DESC';
} elseif ($sort == 'kanbansort') {
$query .= ' ORDER BY zp_tickets.kanbanSortIndex ASC, zp_tickets.id DESC';
} elseif ($sort == 'duedate') {
$query .= " ORDER BY (zp_tickets.dateToFinish = '0000-00-00 00:00:00'), zp_tickets.dateToFinish ASC, zp_tickets.sortindex ASC, zp_tickets.id DESC";
} elseif ($sort == 'date') {
$query .= ' ORDER BY zp_tickets.date DESC, zp_tickets.sortindex ASC, zp_tickets.id DESC';
}
$stmn = $this->db->database->prepare($query);
if (isset($searchCriteria['currentUser'])) {
$stmn->bindValue(':userId', $searchCriteria['currentUser'], PDO::PARAM_INT);
} else {
$stmn->bindValue(':userId', session('userdata.id') ?? '-1', PDO::PARAM_INT);
}
$stmn->bindValue(':requestorId', session('userdata.id') ?? '-1', PDO::PARAM_INT);
if (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
$stmn->bindValue(':projectId', $searchCriteria['currentProject'], PDO::PARAM_INT);
}
if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
foreach (explode(',', $searchCriteria['users']) as $key => $user) {
$stmn->bindValue(':users'.$key, $user, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['clients']) && $searchCriteria['clients'] != 0 && $searchCriteria['clients'] != '' && $searchCriteria['clients'] != '') {
foreach (explode(',', $searchCriteria['clients']) as $key => $client) {
$stmn->bindValue(':clients'.$key, $client, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['currentClient'])) {
$stmn->bindValue(':clientId', $searchCriteria['currentClient'], PDO::PARAM_INT);
} else {
$stmn->bindValue(':clientId', session('userdata.clientId') ?? '-1', PDO::PARAM_INT);
}
if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
foreach (explode(',', $searchCriteria['milestone']) as $key => $milestone) {
$stmn->bindValue(':milestone'.$key, $milestone, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['status']) && $searchCriteria['status'] != 'all') {
$statusArray = explode(',', $searchCriteria['status']);
if ($searchCriteria['status'] != '' && array_search('not_done', $statusArray) === false) {
foreach (explode(',', $searchCriteria['status']) as $key => $status) {
$stmn->bindValue(':status'.$key, $status, PDO::PARAM_STR);
}
}
}
if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
foreach (explode(',', $searchCriteria['type']) as $key => $type) {
$stmn->bindValue(':type'.$key, $type, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
foreach (explode(',', $searchCriteria['priority']) as $key => $priority) {
$stmn->bindValue(':priority'.$key, $priority, PDO::PARAM_STR);
}
}
if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
$termWild = '%'.$searchCriteria['term'].'%';
$stmn->bindValue(':termWild', $termWild, PDO::PARAM_STR);
$stmn->bindValue(':termStandard', $searchCriteria['term'], PDO::PARAM_STR);
}
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] > 0 && $searchCriteria['sprint'] != 'all') {
foreach (explode(',', $searchCriteria['sprint']) as $key => $sprint) {
$stmn->bindValue(':sprint'.$key, $sprint, PDO::PARAM_STR);
}
}
$stmn->execute();
$values = $stmn->fetchAll(PDO::FETCH_CLASS, 'Leantime\Domain\Tickets\Models\Tickets');
$stmn->closeCursor();
return $values;
}
* getType - get the Type from the type array
*/
public function getType(): array
{
return $this->type;
}
* getPriority - get the priority from the priority array
*/
public function getPriority($priority): string
{
if ($priority !== null && $priority !== '') {
return $this->priority[$priority];
} else {
return $this->priority[1];
}
}
public function getFirstTicket($projectId): mixed
{
$query = "SELECT
zp_tickets.id,
zp_tickets.headline,
IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
zp_tickets.description,
zp_tickets.date,
DATE_FORMAT(zp_tickets.date, '%Y,%m,%e') AS timelineDate,
DATE_FORMAT(zp_tickets.dateToFinish, '%Y,%m,%e') AS timelineDateToFinish,
zp_tickets.dateToFinish,
zp_tickets.projectId,
zp_tickets.priority,
zp_tickets.status,
zp_tickets.sprint,
zp_tickets.storypoints,
zp_tickets.hourRemaining,
zp_tickets.acceptanceCriteria,
zp_tickets.userId,
zp_tickets.editorId,
zp_tickets.planHours,
zp_tickets.tags,
zp_tickets.url,
zp_tickets.editFrom,
zp_tickets.editTo,
zp_tickets.dependingTicketId,
zp_tickets.milestoneid
FROM
zp_tickets
WHERE
zp_tickets.type <> 'milestone' AND zp_tickets.projectId = :projectId
ORDER BY
zp_tickets.date ASC
LIMIT 1";
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
$stmn->execute();
$stmn->setFetchMode(PDO::FETCH_CLASS, 'Leantime\Domain\Tickets\Models\Tickets');
$values = $stmn->fetch();
$stmn->closeCursor();
return $values;
}
public function getNumberOfAllTickets($projectId = null): mixed
{
$query = "SELECT
COUNT(zp_tickets.id) AS allTickets
FROM
zp_tickets
WHERE
zp_tickets.type <> 'milestone'";
if (! is_null($projectId)) {
$query .= 'AND zp_tickets.projectId = :projectId ';
}
$stmn = $this->db->database->prepare($query);
if (! is_null($projectId)) {
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
}
$stmn->execute();
$values = $stmn->fetch();
$stmn->closeCursor();
return $values['allTickets'];
}
public function getNumberOfMilestones($projectId = null): mixed
{
$query = "SELECT
COUNT(zp_tickets.id) AS allTickets
FROM
zp_tickets
WHERE
zp_tickets.type = 'milestone' ";
if (! is_null($projectId)) {
$query .= 'AND zp_tickets.projectId = :projectId ';
}
$stmn = $this->db->database->prepare($query);
if (! is_null($projectId)) {
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
}
$stmn->execute();
$values = $stmn->fetch();
$stmn->closeCursor();
return $values['allTickets'];
}
public function getNumberOfClosedTickets($projectId): mixed
{
$statusGroups = $this->getStatusListGroupedByType($projectId);
$query = "SELECT
COUNT(zp_tickets.id) AS allTickets
FROM
zp_tickets
WHERE
zp_tickets.type <> 'milestone' AND zp_tickets.projectId = :projectId
AND zp_tickets.status ".$statusGroups['DONE'].'
ORDER BY
zp_tickets.date ASC
LIMIT 1';
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
$stmn->execute();
$values = $stmn->fetch();
$stmn->closeCursor();
return $values['allTickets'];
}
public function getEffortOfClosedTickets($projectId, $averageStorySize): mixed
{
$statusGroups = $this->getStatusListGroupedByType($projectId);
$query = "SELECT
SUM(CASE when zp_tickets.storypoints <> '' then zp_tickets.storypoints else :avgStorySize end) AS allEffort
FROM
zp_tickets
WHERE
zp_tickets.type <> 'milestone' AND zp_tickets.projectId = :projectId
AND zp_tickets.status ".$statusGroups['DONE'].'
ORDER BY
zp_tickets.date ASC
LIMIT 1';
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
$stmn->bindValue(':avgStorySize', $averageStorySize, PDO::PARAM_INT);
$stmn->execute();
$values = $stmn->fetch();
$stmn->closeCursor();
return $values['allEffort'];
}
public function getEffortOfAllTickets($projectId, $averageStorySize): mixed
{
$query = "SELECT
SUM(CASE when zp_tickets.storypoints <> '' then zp_tickets.storypoints else :avgStorySize end) AS allEffort
FROM
zp_tickets
WHERE
zp_tickets.type <> 'milestone' AND zp_tickets.projectId = :projectId
ORDER BY
zp_tickets.date ASC
LIMIT 1";
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
$stmn->bindValue(':avgStorySize', $averageStorySize, PDO::PARAM_INT);
$stmn->execute();
$values = $stmn->fetch();
$stmn->closeCursor();
return $values['allEffort'];
}
public function getAverageTodoSize($projectId): mixed
{
$query = "SELECT
AVG(zp_tickets.storypoints) as avgSize
FROM
zp_tickets
WHERE
zp_tickets.type <> 'milestone' AND
(zp_tickets.storypoints <> '' AND zp_tickets.storypoints IS NOT NULL) AND zp_tickets.projectId = :projectId
ORDER BY
zp_tickets.date ASC
LIMIT 1";
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
$stmn->execute();
$values = $stmn->fetch();
$stmn->closeCursor();
return $values['avgSize'];
}
* addTicket - add a Ticket with postback test
*/
public function addTicket(array $values): bool|int
{
$query = 'INSERT INTO zp_tickets (
headline,
type,
description,
date,
dateToFinish,
projectId,
status,
userId,
tags,
sprint,
storypoints,
priority,
hourRemaining,
planHours,
acceptanceCriteria,
editFrom,
editTo,
editorId,
dependingTicketId,
milestoneid,
sortindex,
kanbanSortindex,
modified
) VALUES (
:headline,
:type,
:description,
:date,
:dateToFinish,
:projectId,
:status,
:userId,
:tags,
:sprint,
:storypoints,
:priority,
:hourRemaining,
:planHours,
:acceptanceCriteria,
:editFrom,
:editTo,
:editorId,
:dependingTicketId,
:milestoneid,
:sortIndex,
0,
:modified
)';
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':headline', $values['headline'], PDO::PARAM_STR);
$stmn->bindValue(':type', $values['type'], PDO::PARAM_STR);
$stmn->bindValue(':description', $values['description'], PDO::PARAM_STR);
$stmn->bindValue(':date', $values['date'], PDO::PARAM_STR);
$stmn->bindValue(':dateToFinish', $values['dateToFinish'], PDO::PARAM_STR);
$stmn->bindValue(':projectId', $values['projectId'], PDO::PARAM_STR);
$stmn->bindValue(':status', $values['status'], PDO::PARAM_STR);
$stmn->bindValue(':userId', $values['userId'], PDO::PARAM_STR);
$stmn->bindValue(':tags', $values['tags'], PDO::PARAM_STR);
$stmn->bindValue(':sprint', $values['sprint'], PDO::PARAM_STR);
$stmn->bindValue(':storypoints', $values['storypoints'], PDO::PARAM_STR);
$stmn->bindValue(':priority', $values['priority'], PDO::PARAM_STR);
$stmn->bindValue(':hourRemaining', $values['hourRemaining'], PDO::PARAM_STR);
$stmn->bindValue(':planHours', $values['planHours'], PDO::PARAM_STR);
$stmn->bindValue(':acceptanceCriteria', $values['acceptanceCriteria'], PDO::PARAM_STR);
$stmn->bindValue(':editFrom', $values['editFrom'], PDO::PARAM_STR);
$stmn->bindValue(':editTo', $values['editTo'], PDO::PARAM_STR);
$stmn->bindValue(':sortIndex', $values['sortIndex'] ?? '', PDO::PARAM_STR);
$stmn->bindValue(':editorId', $values['editorId'], PDO::PARAM_STR);
$stmn->bindValue(':modified', dtHelper()->userNow()->formatDateTimeForDb(), PDO::PARAM_STR);
$depending = $values['dependingTicketId'] ?? '';
$stmn->bindValue(':dependingTicketId', $depending, PDO::PARAM_STR);
$milestoneId = $values['milestoneid'] ?? '';
$stmn->bindValue(':milestoneid', $milestoneId, PDO::PARAM_STR);
$stmn->execute();
$stmn->closeCursor();
if ($this->db->database->lastInsertId() !== false) {
return intval($this->db->database->lastInsertId());
}
return false;
}
public function patchTicket($id, array $params): bool
{
$this->addTicketChange(session('userdata.id'), $id, $params);
$sql = 'UPDATE zp_tickets SET ';
foreach ($params as $key => $value) {
$sql .= ''.DbCore::sanitizeToColumnString($key).'=:'.DbCore::sanitizeToColumnString($key).', ';
if ($key == 'status') {
static::dispatch_event('ticketStatusUpdate', ['ticketId' => $id, 'status' => $value, 'action' => 'ticketStatusUpdate']);
}
}
$sql .= 'id=:id, modified=:modified WHERE id=:id LIMIT 1';
$stmn = $this->db->database->prepare($sql);
$stmn->bindValue(':id', $id, PDO::PARAM_STR);
$stmn->bindValue(':modified', dtHelper()->userNow()->formatDateTimeForDb(), PDO::PARAM_STR);
foreach ($params as $key => $value) {
$stmn->bindValue(':'.DbCore::sanitizeToColumnString($key), $value, PDO::PARAM_STR);
}
$return = $stmn->execute();
$stmn->closeCursor();
return $return;
}
* updateTicket - Update Ticketinformation
*/
public function updateTicket(array $values, $id): bool
{
$this->addTicketChange(session('userdata.id'), $id, $values);
$query = 'UPDATE zp_tickets
SET
headline = :headline,
type = :type,
description=:description,
projectId=:projectId,
status = :status,
date = :date,
dateToFinish = :dateToFinish,
sprint = :sprint,
storypoints = :storypoints,
priority = :priority,
hourRemaining = :hourRemaining,
planHours = :planHours,
tags = :tags,
editorId = :editorId,
editFrom = :editFrom,
editTo = :editTo,
acceptanceCriteria = :acceptanceCriteria,
dependingTicketId = :dependingTicketId,
milestoneid = :milestoneid,
modified = :modified
WHERE id = :id LIMIT 1';
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':headline', $values['headline'], PDO::PARAM_STR);
$stmn->bindValue(':type', $values['type'], PDO::PARAM_STR);
$stmn->bindValue(':description', $values['description'], PDO::PARAM_STR);
$stmn->bindValue(':projectId', $values['projectId'], PDO::PARAM_STR);
$stmn->bindValue(':status', $values['status'], PDO::PARAM_STR);
$stmn->bindValue(':date', $values['date'], PDO::PARAM_STR);
$stmn->bindValue(':dateToFinish', $values['dateToFinish'], PDO::PARAM_STR);
$stmn->bindValue(':sprint', $values['sprint'], PDO::PARAM_STR);
$stmn->bindValue(':storypoints', $values['storypoints'], PDO::PARAM_STR);
$stmn->bindValue(':priority', $values['priority'], PDO::PARAM_STR);
$stmn->bindValue(':hourRemaining', $values['hourRemaining'], PDO::PARAM_STR);
$stmn->bindValue(':acceptanceCriteria', $values['acceptanceCriteria'], PDO::PARAM_STR);
$stmn->bindValue(':planHours', $values['planHours'], PDO::PARAM_STR);
$stmn->bindValue(':tags', $values['tags'], PDO::PARAM_STR);
$stmn->bindValue(':editorId', $values['editorId'], PDO::PARAM_STR);
$stmn->bindValue(':editFrom', $values['editFrom'], PDO::PARAM_STR);
$stmn->bindValue(':editTo', $values['editTo'], PDO::PARAM_STR);
$stmn->bindValue(':id', $id, PDO::PARAM_STR);
$stmn->bindValue(':dependingTicketId', $values['dependingTicketId'], PDO::PARAM_STR);
$stmn->bindValue(':milestoneid', $values['milestoneid'], PDO::PARAM_STR);
$stmn->bindValue(':modified', dtHelper()->userNow()->formatDateTimeForDb(), PDO::PARAM_STR);
$result = $stmn->execute();
$stmn->closeCursor();
return $result;
}
public function updateTicketStatus($ticketId, $status, int $ticketSorting = -1, $handler = null): bool
{
$this->addTicketChange(session('userdata.id'), $ticketId, ['status' => $status]);
if ($ticketSorting > -1) {
$query = 'UPDATE zp_tickets
SET
kanbanSortIndex = :sortIndex,
status = :status,
modified = :modified
WHERE id = :ticketId
LIMIT 1';
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':status', $status, PDO::PARAM_INT);
$stmn->bindValue(':sortIndex', $ticketSorting, PDO::PARAM_INT);
$stmn->bindValue(':ticketId', $ticketId, PDO::PARAM_INT);
$stmn->bindValue(':modified', dtHelper()->userNow()->formatDateTimeForDb(), PDO::PARAM_STR);
} else {
$query = 'UPDATE zp_tickets
SET
status = :status,
modified = :modified
WHERE id = :ticketId
LIMIT 1';
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':status', $status, PDO::PARAM_INT);
$stmn->bindValue(':ticketId', $ticketId, PDO::PARAM_INT);
$stmn->bindValue(':modified', dtHelper()->userNow()->formatDateTimeForDb(), PDO::PARAM_STR);
}
static::dispatch_event('ticketStatusUpdate', ['ticketId' => $ticketId, 'status' => $status, 'action' => 'ticketStatusUpdate', 'handler' => $handler]);
$result = $stmn->execute();
$stmn->closeCursor();
return $result;
}
public function addTicketChange($userId, $ticketId, $values): void
{
if (empty($ticketId)) {
return;
}
$fields = [
'headline' => 'headline',
'type' => 'type',
'description' => 'description',
'project' => 'projectId',
'priority' => 'priority',
'deadline' => 'dateToFinish',
'editors' => 'editorId',
'fromDate' => 'editFrom',
'toDate' => 'editTo',
'staging' => 'staging',
'production' => 'production',
'planHours' => 'planHours',
'status' => 'status',
];
$changedFields = [];
$sql = 'SELECT * FROM zp_tickets WHERE id=:ticketId LIMIT 1';
$stmn = $this->db->database->prepare($sql);
$stmn->bindValue(':ticketId', $ticketId, PDO::PARAM_INT);
$stmn->execute();
$oldValues = $stmn->fetch();
$stmn->closeCursor();
foreach ($fields as $enum => $dbTable) {
if (
isset($values[$dbTable]) === true &&
isset($oldValues[$dbTable]) === true &&
($oldValues[$dbTable] != $values[$dbTable]) &&
($values[$dbTable] != '')
) {
$changedFields[$enum] = $values[$dbTable];
}
}
$sql = 'INSERT INTO zp_tickethistory (
userId, ticketId, changeType, changeValue, dateModified
) VALUES (
:userId, :ticketId, :changeType, :changeValue, :date
)';
$stmn = $this->db->database->prepare($sql);
foreach ($changedFields as $field => $value) {
$stmn->bindValue(':userId', $userId, PDO::PARAM_INT);
$stmn->bindValue(':ticketId', $ticketId, PDO::PARAM_INT);
$stmn->bindValue(':changeType', $field, PDO::PARAM_STR);
$stmn->bindValue(':changeValue', $value, PDO::PARAM_STR);
$stmn->bindValue(':date', date('Y-m-d H:i:s'), PDO::PARAM_STR);
$stmn->execute();
}
$stmn->closeCursor();
}
* delTicket - delete a Ticket and all dependencies
*/
public function delticket($id): bool
{
$query = 'DELETE FROM zp_tickets WHERE id = :id';
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':id', $id, PDO::PARAM_STR);
$result = $stmn->execute();
$stmn->closeCursor();
return $result;
}
* @return true
*/
* @return true
*/
* @return true
*/
public function delMilestone($id): bool
{
$query = "UPDATE zp_tickets
SET
milestoneid = '',
modified = :modified
WHERE milestoneid = :id";
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':id', $id, PDO::PARAM_STR);
$stmn->bindValue(':modified', dtHelper()->userNow()->formatDateTimeForDb(), PDO::PARAM_STR);
$stmn->execute();
$query = "UPDATE zp_canvas_items
SET
milestoneid = ''
WHERE milestoneid = :id";
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':id', $id, PDO::PARAM_STR);
$stmn->execute();
$query = 'DELETE FROM zp_tickets WHERE id = :id';
$stmn = $this->db->database->prepare($query);
$stmn->bindValue(':id', $id, PDO::PARAM_STR);
$stmn->execute();
return true;
}
}