2328 lines
95 KiB
PHP
2328 lines
95 KiB
PHP
<?php
|
|
|
|
namespace Leantime\Domain\Tickets\Repositories;
|
|
|
|
use Carbon\CarbonImmutable;
|
|
use Illuminate\Contracts\Container\BindingResolutionException;
|
|
use Illuminate\Database\ConnectionInterface;
|
|
use Illuminate\Support\Facades\Cache;
|
|
use Leantime\Core\Db\DatabaseHelper;
|
|
use Leantime\Core\Db\Db as DbCore;
|
|
use Leantime\Core\Events\DispatchesEvents as EventhelperCore;
|
|
use Leantime\Core\Language as LanguageCore;
|
|
use Leantime\Core\Support\EntityRelationshipEnum;
|
|
use Leantime\Domain\Tickets\Events\TicketStatusUpdated;
|
|
use Leantime\Domain\Users\Services\Users;
|
|
|
|
class Tickets
|
|
{
|
|
use EventhelperCore;
|
|
|
|
public ?object $result = null;
|
|
|
|
public ?object $tickets = null;
|
|
|
|
private DbCore $db;
|
|
|
|
private ConnectionInterface $connection;
|
|
|
|
private DatabaseHelper $dbHelper;
|
|
|
|
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', 'milestone' => 'fa-flag'];
|
|
|
|
/**
|
|
* @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, DatabaseHelper $dbHelper)
|
|
{
|
|
$this->db = $db;
|
|
$this->connection = $db->getConnection();
|
|
$this->language = $language;
|
|
$this->dbHelper = $dbHelper;
|
|
}
|
|
|
|
/**
|
|
* @api
|
|
* Get Ticket Status List
|
|
*/
|
|
public function getStateLabels($projectId = null): array
|
|
{
|
|
if (Cache::has('projectsettings.'.$projectId.'.ticketlabels')) {
|
|
return Cache::get('projectsettings.'.$projectId.'.ticketlabels');
|
|
}
|
|
|
|
if ($projectId == null) {
|
|
$projectId = session('currentProject');
|
|
}
|
|
|
|
$result = $this->connection->table('zp_settings')
|
|
->select('value')
|
|
->where('key', 'projectsettings.'.$projectId.'.ticketlabels')
|
|
->first();
|
|
|
|
$labels = [];
|
|
|
|
$statusList = $this->statusListSeed;
|
|
|
|
// Override the state values that are in the db
|
|
if ($result !== null) {
|
|
|
|
$statusList = [];
|
|
|
|
// Archive is required and protected.
|
|
// Adding the original version back in case folks removed it
|
|
$statusList[-1] = $this->statusListSeed[-1];
|
|
|
|
foreach (safe_unserialize($result->value, []) as $key => $status) {
|
|
if (is_int($key)) {
|
|
// Backwards Compatibility with existing labels in db
|
|
// Prior to 2.1.9 labels were stored as <<statuskey>>:<<labelString>>
|
|
// Afterwards labelString was replaced with an array to include all different status attributes needed for custom status types
|
|
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 {
|
|
// If the values are not coming from the db, we need to translate the label strings
|
|
foreach ($statusList as &$status) {
|
|
$status['name'] = $this->language->__($status['name']);
|
|
}
|
|
}
|
|
|
|
// Sort by order number
|
|
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
|
|
{
|
|
|
|
// Ignoring status type NONE by design
|
|
$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;
|
|
}
|
|
|
|
/**
|
|
* Resolve the set of project ids a search applies to. A multi-project `projects`
|
|
* criterion (program cross-project views) wins over the single `currentProject`.
|
|
*
|
|
* @return int[]
|
|
*/
|
|
private function resolveScopedProjectIds(array $searchCriteria): array
|
|
{
|
|
if (isset($searchCriteria['projects']) && $searchCriteria['projects'] != '') {
|
|
return array_values(array_filter(
|
|
array_map('intval', explode(',', (string) $searchCriteria['projects'])),
|
|
static fn ($id) => $id > 0
|
|
));
|
|
}
|
|
|
|
if (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
|
|
return [(int) $searchCriteria['currentProject']];
|
|
}
|
|
|
|
return [];
|
|
}
|
|
|
|
/**
|
|
* Union the status keys matching a semantic type ("done" / "not_done") across every
|
|
* project in scope. Keys are deduped so a cross-project board can filter correctly
|
|
* even when projects define different custom status label sets.
|
|
*
|
|
* @param int[] $projectIds
|
|
* @return int[]
|
|
*/
|
|
private function collectStatusKeysByType(array $projectIds, string $filter): array
|
|
{
|
|
$statusKeys = [];
|
|
|
|
foreach ($projectIds as $projectId) {
|
|
foreach ($this->getStateLabels($projectId) as $key => $status) {
|
|
$isDone = ($status['statusType'] ?? '') === 'DONE';
|
|
if (($filter === 'done' && $isDone) || ($filter === 'not_done' && ! $isDone)) {
|
|
$statusKeys[$key] = true;
|
|
}
|
|
}
|
|
}
|
|
|
|
return array_keys($statusKeys);
|
|
}
|
|
|
|
/**
|
|
* 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);
|
|
|
|
$query = $this->connection->table('zp_tickets as ticket')
|
|
->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',
|
|
't1.id as authorId',
|
|
't1.firstname as authorFirstname',
|
|
't1.lastname as authorLastname',
|
|
't2.id as editorId',
|
|
't2.firstname as editorFirstname',
|
|
't2.lastname as editorLastname',
|
|
])
|
|
->leftJoin('zp_projects as project', 'ticket.projectId', '=', 'project.id')
|
|
->leftJoin('zp_clients as client', 'project.clientId', '=', 'client.id')
|
|
->leftJoin('zp_user as t1', 'ticket.userId', '=', 't1.id')
|
|
->leftJoin('zp_user as t2', function ($join) {
|
|
$join->on('ticket.editorId', '=', $this->connection->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('t2.id'), 'text')));
|
|
})
|
|
->where(function ($q) use ($id, $user) {
|
|
$q->whereIn('ticket.projectId', function ($subquery) use ($id) {
|
|
$subquery->select('projectId')
|
|
->from('zp_relationuserproject')
|
|
->where('zp_relationuserproject.userId', $id);
|
|
})
|
|
->orWhere('project.psettings', 'all')
|
|
->orWhere(function ($q2) use ($user) {
|
|
$q2->where('project.psettings', 'clients')
|
|
->where('project.clientId', $user['clientId'] ?? '');
|
|
});
|
|
})
|
|
->where('ticket.type', '<>', 'milestone')
|
|
->orderByDesc('ticket.id');
|
|
|
|
if ($limit > -1) {
|
|
$query->limit($limit);
|
|
}
|
|
|
|
$results = $query->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
/**
|
|
* getAllBySearchCriteria - get Tickets by search criteria array
|
|
*
|
|
* @param null $limit
|
|
*/
|
|
/**
|
|
* getAllBySearchCriteria - get Tickets by search criteria array
|
|
*
|
|
* @param null $limit
|
|
*/
|
|
public function getAllBySearchCriteria(array $searchCriteria, string $sort = 'standard', ?int $limit = null, $includeCounts = true, ?int $offset = null): bool|array
|
|
{
|
|
$requestorId = session()->exists('userdata') ? session('userdata.id') : -1;
|
|
$userId = $searchCriteria['currentUser'] ?? session('userdata.id') ?? '-1';
|
|
$clientId = $searchCriteria['currentClient'] ?? session('userdata.clientId') ?? '-1';
|
|
|
|
$query = $this->connection->table('zp_tickets')
|
|
->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',
|
|
'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',
|
|
'zp_sprints.name as sprintName',
|
|
'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',
|
|
'parent.headline as parentHeadline',
|
|
'zp_tickets.modified',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->selectRaw('CASE WHEN ('.$this->dbHelper->wrapColumn('milestone.tags').' IS NULL OR '.$this->dbHelper->wrapColumn('milestone.tags')." = '') THEN 'var(--grey)' ELSE ".$this->dbHelper->wrapColumn('milestone.tags').' END AS '.$this->dbHelper->wrapColumn('milestoneColor'))
|
|
->selectRaw('COALESCE(timesheet_agg.total_hours, 0) AS '.$this->dbHelper->wrapColumn('bookedHours'));
|
|
|
|
if ($includeCounts) {
|
|
$query->selectRaw('COALESCE(comment_agg.comment_count, 0) AS '.$this->dbHelper->wrapColumn('commentCount'))
|
|
->selectRaw('COALESCE(file_agg.file_count, 0) AS '.$this->dbHelper->wrapColumn('fileCount'))
|
|
->selectRaw('COALESCE(subtask_agg.subtask_count, 0) AS '.$this->dbHelper->wrapColumn('subtaskCount'));
|
|
} else {
|
|
$query->selectRaw('0 AS '.$this->dbHelper->wrapColumn('commentCount'))
|
|
->selectRaw('0 AS '.$this->dbHelper->wrapColumn('fileCount'))
|
|
->selectRaw('0 AS '.$this->dbHelper->wrapColumn('subtaskCount'));
|
|
}
|
|
|
|
$query->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->leftJoin('zp_clients', 'zp_projects.clientId', '=', 'zp_clients.id')
|
|
->leftJoin('zp_user as t1', 'zp_tickets.userId', '=', 't1.id')
|
|
->leftJoin('zp_user as t2', function ($join) {
|
|
$join->on('zp_tickets.editorId', '=', $this->connection->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('t2.id'), 'text')));
|
|
})
|
|
->leftJoin('zp_user as requestor', function ($join) use ($requestorId) {
|
|
$join->on('requestor.id', '=', $this->connection->raw((int) $requestorId));
|
|
})
|
|
->leftJoin('zp_sprints', 'zp_tickets.sprint', '=', 'zp_sprints.id')
|
|
->leftJoin('zp_tickets as milestone', function ($join) {
|
|
$join->on('zp_tickets.milestoneid', '=', 'milestone.id')
|
|
->where('zp_tickets.milestoneid', '>', 0)
|
|
->where('milestone.type', '=', 'milestone');
|
|
})
|
|
->leftJoin('zp_tickets as parent', 'zp_tickets.dependingTicketId', '=', 'parent.id')
|
|
->leftJoinSub(
|
|
$this->connection->table('zp_timesheets')
|
|
->select('ticketId')
|
|
->selectRaw('CAST(SUM(hours) AS DECIMAL(10,2)) as total_hours')
|
|
->groupBy('ticketId'),
|
|
'timesheet_agg',
|
|
'zp_tickets.id',
|
|
'=',
|
|
'timesheet_agg.ticketId'
|
|
);
|
|
|
|
if ($includeCounts) {
|
|
$query->leftJoinSub(
|
|
$this->connection->table('zp_comment')
|
|
->select('moduleId')
|
|
->selectRaw('COUNT(*) as comment_count')
|
|
->where('module', 'ticket')
|
|
->groupBy('moduleId'),
|
|
'comment_agg',
|
|
'zp_tickets.id',
|
|
'=',
|
|
'comment_agg.moduleId'
|
|
)
|
|
->leftJoinSub(
|
|
$this->connection->table('zp_file')
|
|
->select('moduleId')
|
|
->selectRaw('COUNT(*) as file_count')
|
|
->where('module', 'ticket')
|
|
->groupBy('moduleId'),
|
|
'file_agg',
|
|
'zp_tickets.id',
|
|
'=',
|
|
'file_agg.moduleId'
|
|
)
|
|
->leftJoinSub(
|
|
$this->connection->table('zp_tickets')
|
|
->select('dependingTicketId')
|
|
->selectRaw('COUNT(*) as subtask_count')
|
|
->where('dependingTicketId', '>', 0)
|
|
->groupBy('dependingTicketId'),
|
|
'subtask_agg',
|
|
'zp_tickets.id',
|
|
'=',
|
|
'subtask_agg.dependingTicketId'
|
|
);
|
|
}
|
|
|
|
$query->leftJoin('zp_relationuserproject as rup', function ($join) use ($userId) {
|
|
$join->on('zp_tickets.projectId', '=', 'rup.projectId')
|
|
->where('rup.userId', '=', $userId);
|
|
})
|
|
->where(function ($q) use ($clientId) {
|
|
$q->whereNotNull('rup.projectId')
|
|
->orWhere('zp_projects.psettings', 'all')
|
|
->orWhere(function ($q2) use ($clientId) {
|
|
$q2->where('zp_projects.psettings', 'clients')
|
|
->where('zp_projects.clientId', $clientId);
|
|
})
|
|
->orWhere('requestor.role', '>=', 40);
|
|
});
|
|
|
|
// Apply search criteria filters
|
|
if (isset($searchCriteria['dateFrom']) && $searchCriteria['dateFrom'] != '') {
|
|
$query->where('zp_tickets.date', '>', $searchCriteria['dateFrom']);
|
|
}
|
|
|
|
if (isset($searchCriteria['dateTo']) && $searchCriteria['dateTo'] != '') {
|
|
$query->where('zp_tickets.date', '<', $searchCriteria['dateTo']);
|
|
}
|
|
|
|
if (isset($searchCriteria['excludeType']) && $searchCriteria['excludeType'] != '') {
|
|
$query->where('zp_tickets.type', '<>', $searchCriteria['excludeType']);
|
|
}
|
|
|
|
// A multi-project filter (program cross-project views) takes precedence over the
|
|
// single currentProject filter. In program context currentProject is the program
|
|
// id, which owns no tickets, so we must not AND the two together.
|
|
$scopedProjectIds = $this->resolveScopedProjectIds($searchCriteria);
|
|
if (isset($searchCriteria['projects']) && $searchCriteria['projects'] != '') {
|
|
if ($scopedProjectIds !== []) {
|
|
$query->whereIn('zp_tickets.projectId', $scopedProjectIds);
|
|
} else {
|
|
// A projects filter was requested but resolved to no valid ids — match nothing
|
|
// rather than silently dropping the project scope (which would leak every
|
|
// accessible ticket across all projects).
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
} elseif (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
|
|
$query->where('zp_tickets.projectId', $searchCriteria['currentProject']);
|
|
} else {
|
|
// No explicit project scope (My Work / cross-project aggregation): hide tickets from
|
|
// closed projects to reduce clutter. When the user has explicitly scoped to a project
|
|
// or program above, that scope wins and its tickets show regardless of closed state, so
|
|
// a closed project stays fully browsable when opened directly (#3626).
|
|
$query->where(function ($q) {
|
|
$q->where('zp_projects.state', '<>', -1)
|
|
->orWhereNull('zp_projects.state');
|
|
});
|
|
}
|
|
|
|
if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
|
|
$userIds = explode(',', $searchCriteria['users']);
|
|
$query->where(function ($q) use ($userIds) {
|
|
$q->whereIn('zp_tickets.editorId', $userIds)
|
|
->orWhereExists(function ($subquery) use ($userIds) {
|
|
$subquery->selectRaw('1')
|
|
->from('zp_entity_relationship')
|
|
->whereColumn('zp_entity_relationship.entityA', 'zp_tickets.id')
|
|
->where('zp_entity_relationship.entityAType', 'Ticket')
|
|
->where('zp_entity_relationship.entityBType', 'User')
|
|
->where('zp_entity_relationship.relationship', EntityRelationshipEnum::Collaborator->value)
|
|
->whereIn('zp_entity_relationship.entityB', $userIds);
|
|
});
|
|
});
|
|
}
|
|
|
|
if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
|
|
$milestoneIds = explode(',', $searchCriteria['milestone']);
|
|
|
|
// A milestone id of "0" is the "Not assigned to a milestone" filter
|
|
// option (#3252). Unassigned tickets store milestoneid as NULL or 0,
|
|
// so match both, while still honoring any real milestone ids selected
|
|
// alongside it.
|
|
$includeUnassigned = in_array('0', $milestoneIds, true);
|
|
$realMilestoneIds = array_values(array_filter($milestoneIds, fn ($id) => $id !== '0' && $id !== ''));
|
|
|
|
$query->where(function ($q) use ($realMilestoneIds, $includeUnassigned) {
|
|
if (! empty($realMilestoneIds)) {
|
|
$q->whereIn('zp_tickets.milestoneid', $realMilestoneIds);
|
|
}
|
|
|
|
if ($includeUnassigned) {
|
|
$q->orWhereNull('zp_tickets.milestoneid')
|
|
->orWhere('zp_tickets.milestoneid', 0);
|
|
}
|
|
});
|
|
}
|
|
|
|
if (isset($searchCriteria['status']) && $searchCriteria['status'] == 'all') {
|
|
// No filter
|
|
} elseif (isset($searchCriteria['status']) && $searchCriteria['status'] != '') {
|
|
$statusArray = explode(',', $searchCriteria['status']);
|
|
|
|
if (array_search('not_done', $statusArray) !== false) {
|
|
$statusList = $this->collectStatusKeysByType($scopedProjectIds, 'not_done');
|
|
if (! empty($statusList)) {
|
|
$query->whereIn('zp_tickets.status', $statusList);
|
|
}
|
|
} elseif (array_search('done', $statusArray) !== false) {
|
|
$statusList = $this->collectStatusKeysByType($scopedProjectIds, 'done');
|
|
if (! empty($statusList)) {
|
|
$query->whereIn('zp_tickets.status', $statusList);
|
|
}
|
|
} else {
|
|
$statuses = array_map('intval', explode(',', $searchCriteria['status']));
|
|
$query->whereIn('zp_tickets.status', $statuses);
|
|
}
|
|
} else {
|
|
$query->where('zp_tickets.status', '<>', -1);
|
|
}
|
|
|
|
if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
|
|
$types = array_map('strtolower', explode(',', $searchCriteria['type']));
|
|
$query->whereIn($this->connection->raw('LOWER(zp_tickets.type)'), $types);
|
|
}
|
|
|
|
if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
|
|
$priorities = array_map('strtolower', explode(',', $searchCriteria['priority']));
|
|
$query->whereIn($this->connection->raw('LOWER(zp_tickets.priority)'), $priorities);
|
|
}
|
|
|
|
if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
|
|
$term = $searchCriteria['term'];
|
|
$termWild = '%'.$term.'%';
|
|
$findInSetSql = $this->dbHelper->findInSet('?', 'zp_tickets.tags');
|
|
$query->where(function ($q) use ($term, $termWild, $findInSetSql) {
|
|
$q->whereRaw($findInSetSql, [$term])
|
|
->orWhere('zp_tickets.headline', 'LIKE', $termWild)
|
|
->orWhere('zp_tickets.description', 'LIKE', $termWild)
|
|
->orWhere('zp_tickets.id', 'LIKE', $termWild);
|
|
});
|
|
}
|
|
|
|
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] !== '' && $searchCriteria['sprint'] !== 'backlog') {
|
|
$sprintIds = array_values(array_filter(
|
|
array_map('trim', explode(',', (string) $searchCriteria['sprint'])),
|
|
static fn ($token) => ctype_digit($token) && (int) $token > 0
|
|
));
|
|
|
|
if ($sprintIds !== []) {
|
|
$query->whereIn('zp_tickets.sprint', array_map('intval', $sprintIds));
|
|
}
|
|
}
|
|
|
|
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] === 'backlog') {
|
|
$query->where(function ($q) {
|
|
$q->whereNull('zp_tickets.sprint')
|
|
->orWhere('zp_tickets.sprint', 0)
|
|
->orWhere('zp_tickets.sprint', -1);
|
|
});
|
|
}
|
|
|
|
$groupByColumns = [
|
|
'zp_tickets.id',
|
|
'zp_sprints.name',
|
|
'zp_projects.name',
|
|
'zp_clients.name',
|
|
'zp_clients.id',
|
|
't1.id',
|
|
't1.lastname',
|
|
't1.firstname',
|
|
't1.profileId',
|
|
't2.firstname',
|
|
't2.lastname',
|
|
't2.profileId',
|
|
'milestone.headline',
|
|
'milestone.tags',
|
|
'parent.headline',
|
|
'zp_tickets.type',
|
|
'timesheet_agg.total_hours',
|
|
];
|
|
|
|
if ($includeCounts) {
|
|
$groupByColumns[] = 'comment_agg.comment_count';
|
|
$groupByColumns[] = 'file_agg.file_count';
|
|
$groupByColumns[] = 'subtask_agg.subtask_count';
|
|
}
|
|
|
|
$query->groupBy($groupByColumns);
|
|
|
|
// Apply sorting
|
|
if ($sort == 'standard') {
|
|
$query->orderBy('zp_tickets.sortindex', 'ASC')
|
|
->orderByDesc('zp_tickets.id');
|
|
} elseif ($sort == 'kanbansort') {
|
|
$query->orderBy('zp_tickets.kanbanSortIndex', 'ASC')
|
|
->orderByDesc('zp_tickets.id');
|
|
} elseif ($sort == 'duedate') {
|
|
$query->orderByRaw('('.$this->dbHelper->wrapColumn('zp_tickets.dateToFinish').' IS NULL)')
|
|
->orderBy('zp_tickets.dateToFinish', 'ASC')
|
|
->orderBy('zp_tickets.sortindex', 'ASC')
|
|
->orderByDesc('zp_tickets.id');
|
|
} elseif ($sort == 'priority') {
|
|
$query->orderBy('zp_tickets.priority', 'ASC')
|
|
->orderBy('zp_tickets.dateToFinish', 'ASC')
|
|
->orderBy('zp_tickets.sortindex', 'ASC')
|
|
->orderByDesc('zp_tickets.id');
|
|
} elseif ($sort == 'date') {
|
|
$query->orderByDesc('zp_tickets.date')
|
|
->orderBy('zp_tickets.sortindex', 'ASC')
|
|
->orderByDesc('zp_tickets.id');
|
|
}
|
|
|
|
if ($limit !== null && $limit > 0) {
|
|
$query->limit($limit);
|
|
if ($offset !== null && $offset > 0) {
|
|
$query->offset($offset);
|
|
}
|
|
}
|
|
|
|
$results = $query->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
public function simpleTicketQuery(?int $userId, ?int $projectId, array $types = [], bool $excludeClosedProjects = false): array|false
|
|
{
|
|
$requestorId = session()->exists('userdata') ? session('userdata.id') : -1;
|
|
$clientId = session('userdata.clientId') ?? '-1';
|
|
|
|
$query = $this->connection->table('zp_tickets')
|
|
->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',
|
|
'zp_tickets.status',
|
|
'zp_tickets.tags',
|
|
'zp_tickets.userId',
|
|
'zp_tickets.editorId',
|
|
'zp_tickets.dependingTicketId',
|
|
'zp_tickets.milestoneid',
|
|
'zp_tickets.planHours',
|
|
'zp_tickets.editFrom',
|
|
'zp_tickets.editTo',
|
|
'zp_tickets.hourRemaining',
|
|
'milestones.headline as milestoneHeadline',
|
|
'zp_projects.name as projectName',
|
|
'zp_projects.details as projectDescription',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->leftJoin('zp_user as requestor', function ($join) use ($requestorId) {
|
|
$join->on('requestor.id', '=', $this->connection->raw((int) $requestorId));
|
|
})
|
|
->leftJoin('zp_tickets as milestones', 'zp_tickets.milestoneid', '=', 'milestones.id')
|
|
->where(function ($q) use ($requestorId, $clientId) {
|
|
$q->whereIn('zp_tickets.projectId', function ($subquery) use ($requestorId) {
|
|
$subquery->select('projectId')
|
|
->from('zp_relationuserproject')
|
|
->where('zp_relationuserproject.userId', $requestorId);
|
|
})
|
|
->orWhere('zp_projects.psettings', 'all')
|
|
->orWhere(function ($q2) use ($clientId) {
|
|
$q2->where('zp_projects.psettings', 'clients')
|
|
->where('zp_projects.clientId', $clientId);
|
|
})
|
|
->orWhere('requestor.role', '>=', 40);
|
|
});
|
|
|
|
if (isset($projectId) && $projectId > 0) {
|
|
$query->where('zp_tickets.projectId', $projectId);
|
|
}
|
|
|
|
if (isset($userId) && $userId > 0) {
|
|
$query->where(function ($q) use ($userId) {
|
|
$q->where('zp_tickets.editorId', (string) $userId)
|
|
->orWhereExists(function ($subquery) use ($userId) {
|
|
$subquery->selectRaw('1')
|
|
->from('zp_entity_relationship')
|
|
->whereColumn('zp_entity_relationship.entityA', 'zp_tickets.id')
|
|
->where('zp_entity_relationship.entityAType', 'Ticket')
|
|
->where('zp_entity_relationship.entityBType', 'User')
|
|
->where('zp_entity_relationship.relationship', EntityRelationshipEnum::Collaborator->value)
|
|
->where('zp_entity_relationship.entityB', $userId);
|
|
});
|
|
});
|
|
}
|
|
|
|
if (count($types) > 0) {
|
|
$query->whereIn('zp_tickets.type', $types);
|
|
}
|
|
|
|
// Closed projects (state === -1) are inactive. Callers wanting a "my
|
|
// active work" view drop their tickets at the SQL level so closed-
|
|
// project rows are never fetched or returned — matches the existing
|
|
// `state <> -1 OR state IS NULL` pattern used elsewhere in this repo.
|
|
if ($excludeClosedProjects) {
|
|
$query->where(function ($q) {
|
|
$q->where('zp_projects.state', '<>', -1)
|
|
->orWhereNull('zp_projects.state');
|
|
});
|
|
}
|
|
|
|
$results = $query->orderByDesc('zp_tickets.dateToFinish')
|
|
->orderBy('zp_tickets.sortindex', 'ASC')
|
|
->orderByDesc('zp_tickets.id')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
public function getScheduledTasks(CarbonImmutable $dateFrom, CarbonImmutable $dateTo, ?int $userId = null)
|
|
{
|
|
$requestorId = session()->exists('userdata') ? session('userdata.id') : -1;
|
|
$clientId = session('userdata.clientId') ?? '-1';
|
|
$activeUserId = $userId ?? (session('userdata.id') ?? '-1');
|
|
|
|
$query = $this->connection->table('zp_tickets')
|
|
->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',
|
|
'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',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->leftJoin('zp_user as requestor', function ($join) use ($requestorId) {
|
|
$join->on('requestor.id', '=', $this->connection->raw((int) $requestorId));
|
|
})
|
|
->where(function ($q) use ($activeUserId, $clientId) {
|
|
$q->whereIn('zp_tickets.projectId', function ($subquery) use ($activeUserId) {
|
|
$subquery->select('projectId')
|
|
->from('zp_relationuserproject')
|
|
->where('zp_relationuserproject.userId', $activeUserId);
|
|
})
|
|
->orWhere('zp_projects.psettings', 'all')
|
|
->orWhere(function ($q2) use ($clientId) {
|
|
$q2->where('zp_projects.psettings', 'clients')
|
|
->where('zp_projects.clientId', $clientId);
|
|
})
|
|
->orWhere('requestor.role', '>=', 40);
|
|
})
|
|
->where('zp_tickets.type', '<>', 'milestone');
|
|
|
|
if (isset($userId)) {
|
|
$query->where(function ($q) use ($userId) {
|
|
$q->where('zp_tickets.editorId', (string) $userId)
|
|
->orWhereExists(function ($subquery) use ($userId) {
|
|
$subquery->selectRaw('1')
|
|
->from('zp_entity_relationship')
|
|
->whereColumn('zp_entity_relationship.entityA', 'zp_tickets.id')
|
|
->where('zp_entity_relationship.entityAType', 'Ticket')
|
|
->where('zp_entity_relationship.entityBType', 'User')
|
|
->where('zp_entity_relationship.relationship', EntityRelationshipEnum::Collaborator->value)
|
|
->where('zp_entity_relationship.entityB', $userId);
|
|
});
|
|
});
|
|
}
|
|
|
|
$query->where(function ($q) use ($dateFrom, $dateTo) {
|
|
$q->whereBetween('zp_tickets.editFrom', [$dateFrom->formatDateTimeForDb(), $dateTo->formatDateTimeForDb()])
|
|
->orWhereBetween('zp_tickets.editTo', [$dateFrom->formatDateTimeForDb(), $dateTo->formatDateTimeForDb()]);
|
|
});
|
|
|
|
$results = $query->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
public function getAllByProjectId($projectId): false|array
|
|
{
|
|
$results = $this->connection->table('zp_tickets')
|
|
->select([
|
|
'zp_tickets.id',
|
|
'zp_tickets.headline',
|
|
'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.outcomeImpact',
|
|
'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',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->leftJoin('zp_clients', 'zp_projects.clientId', '=', 'zp_clients.id')
|
|
->leftJoin('zp_user', 'zp_tickets.userId', '=', 'zp_user.id')
|
|
->leftJoin('zp_user as t3', function ($join) {
|
|
$join->on('zp_tickets.editorId', '=', $this->connection->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('t3.id'), 'text')));
|
|
})
|
|
->where('zp_tickets.projectId', $projectId)
|
|
->get();
|
|
|
|
// Convert stdClass objects to Tickets model instances
|
|
$tickets = [];
|
|
foreach ($results as $row) {
|
|
$ticket = new \Leantime\Domain\Tickets\Models\Tickets;
|
|
foreach ((array) $row as $key => $value) {
|
|
if (property_exists($ticket, $key)) {
|
|
$ticket->$key = $value;
|
|
}
|
|
}
|
|
$tickets[] = $ticket;
|
|
}
|
|
|
|
return $tickets;
|
|
}
|
|
|
|
/**
|
|
* Batched sibling of getAllByProjectId(): fetch every ticket for a SET of
|
|
* projects in a single query, grouped by project id. Callers that would
|
|
* otherwise loop getAllByProjectId() once per project (e.g. the capacity
|
|
* analyzer building a plan report) use this to collapse N round-trips into
|
|
* one. Returns the same hydrated Tickets models, keyed by projectId; every
|
|
* requested project is present (empty array when it has no tickets) so the
|
|
* caller can index without existence checks.
|
|
*
|
|
* @param int[] $projectIds
|
|
* @return array<int, array<int, \Leantime\Domain\Tickets\Models\Tickets>> projectId => Tickets[]
|
|
*/
|
|
public function getAllByProjectIds(array $projectIds): array
|
|
{
|
|
$projectIds = array_values(array_unique(array_map('intval', $projectIds)));
|
|
if ($projectIds === []) {
|
|
return [];
|
|
}
|
|
|
|
$results = $this->connection->table('zp_tickets')
|
|
->select([
|
|
'zp_tickets.id',
|
|
'zp_tickets.headline',
|
|
'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.outcomeImpact',
|
|
'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',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->leftJoin('zp_clients', 'zp_projects.clientId', '=', 'zp_clients.id')
|
|
->leftJoin('zp_user', 'zp_tickets.userId', '=', 'zp_user.id')
|
|
->leftJoin('zp_user as t3', function ($join) {
|
|
$join->on('zp_tickets.editorId', '=', $this->connection->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('t3.id'), 'text')));
|
|
})
|
|
->whereIn('zp_tickets.projectId', $projectIds)
|
|
->get();
|
|
|
|
// Pre-seed every requested project so a project with no tickets maps to
|
|
// [] rather than a missing key.
|
|
$grouped = array_fill_keys($projectIds, []);
|
|
foreach ($results as $row) {
|
|
$ticket = new \Leantime\Domain\Tickets\Models\Tickets;
|
|
foreach ((array) $row as $key => $value) {
|
|
if (property_exists($ticket, $key)) {
|
|
$ticket->$key = $value;
|
|
}
|
|
}
|
|
$grouped[(int) $row->projectId][] = $ticket;
|
|
}
|
|
|
|
return $grouped;
|
|
}
|
|
|
|
public function getTags($projectId): false|array
|
|
{
|
|
$results = $this->connection->table('zp_tickets')
|
|
->select('zp_tickets.tags')
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->where('zp_tickets.projectId', $projectId)
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
/**
|
|
* getTicket - get a specific Ticket depending on the role
|
|
*/
|
|
public function getTicket($id): \Leantime\Domain\Tickets\Models\Tickets|bool
|
|
{
|
|
$result = $this->connection->table('zp_tickets')
|
|
->select([
|
|
'zp_tickets.id',
|
|
'zp_tickets.headline',
|
|
'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.outcomeImpact',
|
|
'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',
|
|
'zp_tickets.modified',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->leftJoin('zp_clients', 'zp_projects.clientId', '=', 'zp_clients.id')
|
|
->leftJoin('zp_user', 'zp_tickets.userId', '=', 'zp_user.id')
|
|
->leftJoin('zp_user as t3', function ($join) {
|
|
$join->on('zp_tickets.editorId', '=', $this->connection->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('t3.id'), 'text')));
|
|
})
|
|
->leftJoin('zp_tickets as parent', 'zp_tickets.dependingTicketId', '=', 'parent.id')
|
|
->leftJoin('zp_tickets as milestones', 'zp_tickets.milestoneid', '=', 'milestones.id')
|
|
->where('zp_tickets.id', $id)
|
|
->limit(1)
|
|
->first();
|
|
|
|
if (! $result) {
|
|
return false;
|
|
}
|
|
|
|
$values = new \Leantime\Domain\Tickets\Models\Tickets;
|
|
foreach ((array) $result as $key => $value) {
|
|
$values->$key = $value;
|
|
}
|
|
|
|
$values->collaborators = $this->getCollaborators($id);
|
|
|
|
return $values;
|
|
}
|
|
|
|
public function getAllSubtasks($id): false|array
|
|
{
|
|
$dateFormatSql = match ($this->dbHelper->getDriverName()) {
|
|
'mysql' => "DATE_FORMAT(zp_tickets.date, '%Y,%m,%e')",
|
|
'pgsql' => "TO_CHAR(zp_tickets.date, 'YYYY,MM,DD')",
|
|
default => "DATE_FORMAT(zp_tickets.date, '%Y,%m,%e')",
|
|
};
|
|
|
|
$wrappedDateToFinish = $this->dbHelper->wrapColumn('zp_tickets.dateToFinish');
|
|
$dateToFinishFormatSql = match ($this->dbHelper->getDriverName()) {
|
|
'mysql' => "DATE_FORMAT({$wrappedDateToFinish}, '%Y,%m,%e')",
|
|
'pgsql' => "TO_CHAR({$wrappedDateToFinish}, 'YYYY,MM,DD')",
|
|
default => "DATE_FORMAT({$wrappedDateToFinish}, '%Y,%m,%e')",
|
|
};
|
|
|
|
$results = $this->connection->table('zp_tickets')
|
|
->select([
|
|
'zp_tickets.id',
|
|
'zp_tickets.headline',
|
|
'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.acceptanceCriteria',
|
|
'zp_tickets.outcomeImpact',
|
|
'zp_tickets.userId',
|
|
'zp_tickets.editorId',
|
|
'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',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->selectRaw("{$dateFormatSql} AS ".$this->dbHelper->wrapColumn('timelineDate'))
|
|
->selectRaw("{$dateToFinishFormatSql} AS ".$this->dbHelper->wrapColumn('timelineDateToFinish'))
|
|
->selectRaw('COALESCE('.$this->dbHelper->wrapColumn('zp_tickets.hourRemaining').', 0) AS '.$this->dbHelper->wrapColumn('hourRemaining'))
|
|
->selectRaw('COALESCE('.$this->dbHelper->wrapColumn('zp_tickets.planHours').', 0) AS '.$this->dbHelper->wrapColumn('planHours'))
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->leftJoin('zp_clients', 'zp_projects.clientId', '=', 'zp_clients.id')
|
|
->leftJoin('zp_user', 'zp_tickets.userId', '=', 'zp_user.id')
|
|
->leftJoin('zp_user as t3', function ($join) {
|
|
$join->on('zp_tickets.editorId', '=', $this->connection->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('t3.id'), 'text')));
|
|
})
|
|
->where('zp_tickets.dependingTicketId', $id)
|
|
->orderByDesc('zp_tickets.date')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
public function getAllPossibleParents(\Leantime\Domain\Tickets\Models\Tickets $ticket, $projectId): false|array
|
|
{
|
|
$query = $this->connection->table('zp_tickets')
|
|
->select([
|
|
'zp_tickets.id',
|
|
'zp_tickets.headline',
|
|
'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.acceptanceCriteria',
|
|
'zp_tickets.outcomeImpact',
|
|
'zp_tickets.userId',
|
|
'zp_tickets.editorId',
|
|
'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',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->selectRaw($this->dbHelper->formatDate('zp_tickets.date', '%Y,%m,%e').' AS '.$this->dbHelper->wrapColumn('timelineDate'))
|
|
->selectRaw($this->dbHelper->formatDate($this->dbHelper->wrapColumn('zp_tickets.dateToFinish'), '%Y,%m,%e').' AS '.$this->dbHelper->wrapColumn('timelineDateToFinish'))
|
|
->selectRaw('COALESCE('.$this->dbHelper->wrapColumn('zp_tickets.hourRemaining').', 0) AS '.$this->dbHelper->wrapColumn('hourRemaining'))
|
|
->selectRaw('COALESCE('.$this->dbHelper->wrapColumn('zp_tickets.planHours').', 0) AS '.$this->dbHelper->wrapColumn('planHours'))
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->leftJoin('zp_clients', 'zp_projects.clientId', '=', 'zp_clients.id')
|
|
->leftJoin('zp_user', 'zp_tickets.userId', '=', 'zp_user.id')
|
|
->leftJoin('zp_user as t3', function ($join) {
|
|
$join->on('zp_tickets.editorId', '=', $this->connection->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('t3.id'), 'text')));
|
|
})
|
|
->where('zp_tickets.id', '<>', $ticket->id ?? 0)
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
->where(function ($q) use ($ticket) {
|
|
$q->where('zp_tickets.dependingTicketId', '<>', $ticket->id ?? 0)
|
|
->orWhereNull('zp_tickets.dependingTicketId');
|
|
});
|
|
|
|
if ($projectId !== 0) {
|
|
$query->where('zp_tickets.projectId', $projectId);
|
|
}
|
|
|
|
$results = $query->orderByDesc('zp_tickets.date')->get();
|
|
|
|
// Convert stdClass objects to Tickets model instances
|
|
$tickets = [];
|
|
foreach ($results as $row) {
|
|
$ticket = new \Leantime\Domain\Tickets\Models\Tickets;
|
|
foreach ((array) $row as $key => $value) {
|
|
if (property_exists($ticket, $key)) {
|
|
$ticket->$key = $value;
|
|
}
|
|
}
|
|
$tickets[] = $ticket;
|
|
}
|
|
|
|
return $tickets;
|
|
}
|
|
|
|
/**
|
|
* 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'));
|
|
|
|
$requestorId = session('userdata.id') ?? '-1';
|
|
$userId = $searchCriteria['currentUser'] ?? session('userdata.id') ?? '-1';
|
|
$clientId = $searchCriteria['currentClient'] ?? session('userdata.clientId') ?? '-1';
|
|
|
|
$query = $this->connection->table('zp_tickets')
|
|
->select([
|
|
'zp_tickets.id',
|
|
'zp_tickets.headline',
|
|
'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.outcomeImpact',
|
|
'zp_tickets.userId',
|
|
'zp_tickets.editorId',
|
|
'zp_tickets.planHours',
|
|
'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',
|
|
'depMilestone.headline as milestoneHeadline',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->selectRaw($this->dbHelper->formatDate('zp_tickets.date', '%Y,%m,%e').' AS '.$this->dbHelper->wrapColumn('timelineDate'))
|
|
->selectRaw($this->dbHelper->formatDate($this->dbHelper->wrapColumn('zp_tickets.dateToFinish'), '%Y,%m,%e').' AS '.$this->dbHelper->wrapColumn('timelineDateToFinish'))
|
|
->selectRaw('CASE WHEN ('.$this->dbHelper->wrapColumn('depMilestone.tags').' IS NULL OR '.$this->dbHelper->wrapColumn('depMilestone.tags')." = '') THEN 'var(--grey)' ELSE ".$this->dbHelper->wrapColumn('depMilestone.tags').' END AS '.$this->dbHelper->wrapColumn('milestoneColor'))
|
|
->selectRaw("CASE WHEN (zp_tickets.tags IS NULL OR zp_tickets.tags = '') THEN 'var(--grey)' ELSE zp_tickets.tags END AS tags")
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->leftJoin('zp_tickets as depMilestone', 'zp_tickets.milestoneid', '=', 'depMilestone.id')
|
|
->leftJoin('zp_clients', 'zp_projects.clientId', '=', 'zp_clients.id')
|
|
->leftJoin('zp_user', 'zp_tickets.userId', '=', 'zp_user.id')
|
|
->leftJoin('zp_user as t3', function ($join) {
|
|
$join->on('zp_tickets.editorId', '=', $this->connection->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('t3.id'), 'text')));
|
|
})
|
|
->leftJoin('zp_user as requestor', function ($join) use ($requestorId) {
|
|
$join->on('requestor.id', '=', $this->connection->raw((int) $requestorId));
|
|
})
|
|
->where(function ($q) use ($userId, $clientId) {
|
|
$q->whereIn('zp_tickets.projectId', function ($subquery) use ($userId) {
|
|
$subquery->select('projectId')
|
|
->from('zp_relationuserproject')
|
|
->where('zp_relationuserproject.userId', $userId);
|
|
})
|
|
->orWhere('zp_projects.psettings', 'all')
|
|
->orWhere(function ($q2) use ($clientId) {
|
|
$q2->where('zp_projects.psettings', 'clients')
|
|
->where('zp_projects.clientId', $clientId);
|
|
})
|
|
->orWhere('requestor.role', '>=', 40);
|
|
});
|
|
|
|
// Restrict to milestone-type rows only when the caller didn't specify a type at all (e.g. the
|
|
// mobile milestone picker), so it doesn't get tasks/subtasks listed as milestones. Any caller
|
|
// that passes an explicit type — a single type, a comma-separated list, or '' from the Roadmap
|
|
// "Show Tasks" toggle (via normalizeRoadmapParams) — is handled by the whereIn type filter
|
|
// further below, so a milestone's child tasks come back and nest under it when requested (#3625).
|
|
if (! isset($searchCriteria['type'])) {
|
|
$query->where('zp_tickets.type', '=', 'milestone');
|
|
}
|
|
|
|
// Apply search criteria filters. A multi-project filter takes precedence over the
|
|
// single currentProject filter (see getAllBySearchCriteria for rationale).
|
|
$scopedProjectIds = $this->resolveScopedProjectIds($searchCriteria);
|
|
if (isset($searchCriteria['projects']) && $searchCriteria['projects'] != '') {
|
|
if ($scopedProjectIds !== []) {
|
|
$query->whereIn('zp_tickets.projectId', $scopedProjectIds);
|
|
} else {
|
|
// A projects filter was requested but resolved to no valid ids — match nothing.
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
} elseif (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
|
|
$query->where('zp_tickets.projectId', $searchCriteria['currentProject']);
|
|
} else {
|
|
// No explicit project scope: hide milestones from closed projects to reduce clutter in
|
|
// cross-project views. An explicitly-opened project/program (above) shows its milestones
|
|
// regardless of closed state, matching getAllBySearchCriteria (#3626).
|
|
$query->where(function ($q) {
|
|
$q->where('zp_projects.state', '<>', -1)
|
|
->orWhereNull('zp_projects.state');
|
|
});
|
|
}
|
|
|
|
if (isset($searchCriteria['clients']) && $searchCriteria['clients'] != 0 && $searchCriteria['clients'] != '') {
|
|
$clientIds = explode(',', $searchCriteria['clients']);
|
|
$query->whereIn('zp_projects.clientId', $clientIds);
|
|
}
|
|
|
|
if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
|
|
$userIds = explode(',', $searchCriteria['users']);
|
|
$query->where(function ($q) use ($userIds) {
|
|
$q->whereIn('zp_tickets.editorId', $userIds)
|
|
->orWhereExists(function ($subquery) use ($userIds) {
|
|
$subquery->selectRaw('1')
|
|
->from('zp_entity_relationship')
|
|
->whereColumn('zp_entity_relationship.entityA', 'zp_tickets.id')
|
|
->where('zp_entity_relationship.entityAType', 'Ticket')
|
|
->where('zp_entity_relationship.entityBType', 'User')
|
|
->where('zp_entity_relationship.relationship', EntityRelationshipEnum::Collaborator->value)
|
|
->whereIn('zp_entity_relationship.entityB', $userIds);
|
|
});
|
|
});
|
|
}
|
|
|
|
if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
|
|
$milestoneIds = explode(',', $searchCriteria['milestone']);
|
|
|
|
// A milestone id of "0" is the "Not assigned to a milestone" filter
|
|
// option (#3252). Unassigned tickets store milestoneid as NULL or 0,
|
|
// so match both, while still honoring any real milestone ids selected
|
|
// alongside it.
|
|
$includeUnassigned = in_array('0', $milestoneIds, true);
|
|
$realMilestoneIds = array_values(array_filter($milestoneIds, fn ($id) => $id !== '0' && $id !== ''));
|
|
|
|
$query->where(function ($q) use ($realMilestoneIds, $includeUnassigned) {
|
|
if (! empty($realMilestoneIds)) {
|
|
$q->whereIn('zp_tickets.milestoneid', $realMilestoneIds);
|
|
}
|
|
|
|
if ($includeUnassigned) {
|
|
$q->orWhereNull('zp_tickets.milestoneid')
|
|
->orWhere('zp_tickets.milestoneid', 0);
|
|
}
|
|
});
|
|
}
|
|
|
|
if (isset($searchCriteria['status']) && $searchCriteria['status'] == 'all') {
|
|
// No filter
|
|
} elseif (isset($searchCriteria['status']) && $searchCriteria['status'] != '') {
|
|
$statusArray = explode(',', $searchCriteria['status']);
|
|
|
|
if (array_search('not_done', $statusArray) !== false) {
|
|
$statusList = $this->collectStatusKeysByType($scopedProjectIds, 'not_done');
|
|
if (! empty($statusList)) {
|
|
$query->whereIn('zp_tickets.status', $statusList);
|
|
}
|
|
} elseif (array_search('done', $statusArray) !== false) {
|
|
$statusList = $this->collectStatusKeysByType($scopedProjectIds, 'done');
|
|
if (! empty($statusList)) {
|
|
$query->whereIn('zp_tickets.status', $statusList);
|
|
}
|
|
} else {
|
|
$statuses = array_map('intval', explode(',', $searchCriteria['status']));
|
|
$query->whereIn('zp_tickets.status', $statuses);
|
|
}
|
|
} else {
|
|
$query->where('zp_tickets.status', '<>', -1);
|
|
}
|
|
|
|
if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
|
|
$types = array_map('strtolower', explode(',', $searchCriteria['type']));
|
|
$query->whereIn($this->connection->raw('LOWER(zp_tickets.type)'), $types);
|
|
}
|
|
|
|
if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
|
|
$priorities = array_map('strtolower', explode(',', $searchCriteria['priority']));
|
|
$query->whereIn($this->connection->raw('LOWER(zp_tickets.priority)'), $priorities);
|
|
}
|
|
|
|
if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
|
|
$term = $searchCriteria['term'];
|
|
$termWild = '%'.$term.'%';
|
|
$findInSetSql = $this->dbHelper->findInSet('?', 'zp_tickets.tags');
|
|
$query->where(function ($q) use ($term, $termWild, $findInSetSql) {
|
|
$q->whereRaw($findInSetSql, [$term])
|
|
->orWhere('zp_tickets.headline', 'LIKE', $termWild)
|
|
->orWhere('zp_tickets.description', 'LIKE', $termWild)
|
|
->orWhere('zp_tickets.id', 'LIKE', $termWild);
|
|
});
|
|
}
|
|
|
|
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] !== '' && $searchCriteria['sprint'] !== 'backlog') {
|
|
$sprintIds = array_values(array_filter(
|
|
array_map('trim', explode(',', (string) $searchCriteria['sprint'])),
|
|
static fn ($token) => ctype_digit($token) && (int) $token > 0
|
|
));
|
|
|
|
if ($sprintIds !== []) {
|
|
$intSprintIds = array_map('intval', $sprintIds);
|
|
$query->where(function ($q) use ($intSprintIds) {
|
|
$q->whereIn('zp_tickets.sprint', $intSprintIds)
|
|
->orWhere('zp_tickets.type', 'milestone');
|
|
});
|
|
}
|
|
}
|
|
|
|
if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] === 'backlog') {
|
|
$query->where(function ($q) {
|
|
$q->whereNull('zp_tickets.sprint')
|
|
->orWhere('zp_tickets.sprint', 0)
|
|
->orWhere('zp_tickets.sprint', -1)
|
|
->orWhere('zp_tickets.type', 'milestone');
|
|
});
|
|
}
|
|
|
|
$query->groupBy([
|
|
'zp_tickets.id',
|
|
'zp_projects.name',
|
|
'zp_clients.name',
|
|
'zp_user.firstname',
|
|
'zp_user.lastname',
|
|
't3.firstname',
|
|
't3.lastname',
|
|
't3.profileId',
|
|
'depMilestone.headline',
|
|
'depMilestone.tags',
|
|
]);
|
|
|
|
// Apply sorting
|
|
if ($sort == 'standard') {
|
|
$query->orderBy('zp_tickets.sortindex', 'ASC')
|
|
->orderBy('zp_tickets.editFrom', 'ASC')
|
|
->orderByDesc('zp_tickets.id');
|
|
} elseif ($sort == 'kanbansort') {
|
|
$query->orderBy('zp_tickets.kanbanSortIndex', 'ASC')
|
|
->orderByDesc('zp_tickets.id');
|
|
} elseif ($sort == 'duedate') {
|
|
$query->orderByRaw('('.$this->dbHelper->wrapColumn('zp_tickets.dateToFinish').' IS NULL)')
|
|
->orderBy('zp_tickets.dateToFinish', 'ASC')
|
|
->orderBy('zp_tickets.sortindex', 'ASC')
|
|
->orderByDesc('zp_tickets.id');
|
|
} elseif ($sort == 'date') {
|
|
$query->orderByDesc('zp_tickets.date')
|
|
->orderBy('zp_tickets.sortindex', 'ASC')
|
|
->orderByDesc('zp_tickets.id');
|
|
}
|
|
|
|
$results = $query->get();
|
|
|
|
// Convert stdClass objects to Tickets model instances
|
|
$tickets = [];
|
|
foreach ($results as $row) {
|
|
$ticket = new \Leantime\Domain\Tickets\Models\Tickets;
|
|
foreach ((array) $row as $key => $value) {
|
|
if (property_exists($ticket, $key)) {
|
|
$ticket->$key = $value;
|
|
}
|
|
}
|
|
$tickets[] = $ticket;
|
|
}
|
|
|
|
return $tickets;
|
|
}
|
|
|
|
/**
|
|
* 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
|
|
{
|
|
$result = $this->connection->table('zp_tickets')
|
|
->select([
|
|
'zp_tickets.id',
|
|
'zp_tickets.headline',
|
|
'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.outcomeImpact',
|
|
'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',
|
|
])
|
|
->selectRaw("CASE WHEN zp_tickets.type <> '' THEN zp_tickets.type ELSE 'task' END AS type")
|
|
->selectRaw($this->dbHelper->formatDate('zp_tickets.date', '%Y,%m,%e').' AS '.$this->dbHelper->wrapColumn('timelineDate'))
|
|
->selectRaw($this->dbHelper->formatDate($this->dbHelper->wrapColumn('zp_tickets.dateToFinish'), '%Y,%m,%e').' AS '.$this->dbHelper->wrapColumn('timelineDateToFinish'))
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
->where('zp_tickets.projectId', $projectId)
|
|
->orderBy('zp_tickets.date', 'ASC')
|
|
->limit(1)
|
|
->first();
|
|
|
|
if (! $result) {
|
|
return false;
|
|
}
|
|
|
|
$ticket = new \Leantime\Domain\Tickets\Models\Tickets;
|
|
foreach ((array) $result as $key => $value) {
|
|
if (property_exists($ticket, $key)) {
|
|
$ticket->$key = $value;
|
|
}
|
|
}
|
|
|
|
return $ticket;
|
|
}
|
|
|
|
public function getNumberOfAllTickets($projectId = null): mixed
|
|
{
|
|
$query = $this->connection->table('zp_tickets')
|
|
->where('zp_tickets.type', '<>', 'milestone');
|
|
|
|
if (! is_null($projectId)) {
|
|
$query->where('zp_tickets.projectId', $projectId);
|
|
}
|
|
|
|
return $query->count();
|
|
}
|
|
|
|
public function getNumberOfMilestones($projectId = null): mixed
|
|
{
|
|
$query = $this->connection->table('zp_tickets')
|
|
->where('zp_tickets.type', 'milestone');
|
|
|
|
if (! is_null($projectId)) {
|
|
$query->where('zp_tickets.projectId', $projectId);
|
|
}
|
|
|
|
return $query->count();
|
|
}
|
|
|
|
public function getNumberOfClosedTickets($projectId): mixed
|
|
{
|
|
$statusGroupsSQL = $this->getStatusListGroupedByType($projectId);
|
|
$statusGroups = $this->dbHelper->parseStatusGroups($statusGroupsSQL);
|
|
|
|
$query = $this->connection->table('zp_tickets')
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
->where('zp_tickets.projectId', $projectId);
|
|
|
|
if (! empty($statusGroups['DONE'])) {
|
|
$query->whereIn('zp_tickets.status', $statusGroups['DONE']);
|
|
} else {
|
|
$query->whereRaw('1=0'); // Empty status group = no matches
|
|
}
|
|
|
|
return $query->count();
|
|
}
|
|
|
|
public function getEffortOfClosedTickets($projectId, $averageStorySize): mixed
|
|
{
|
|
$statusGroupsSQL = $this->getStatusListGroupedByType($projectId);
|
|
$statusGroups = $this->dbHelper->parseStatusGroups($statusGroupsSQL);
|
|
|
|
$query = $this->connection->table('zp_tickets')
|
|
->selectRaw('SUM(CASE WHEN zp_tickets.storypoints IS NOT NULL AND zp_tickets.storypoints <> 0 THEN zp_tickets.storypoints ELSE ? END) AS '.$this->dbHelper->wrapColumn('allEffort'), [$averageStorySize])
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
->where('zp_tickets.projectId', $projectId);
|
|
|
|
if (! empty($statusGroups['DONE'])) {
|
|
$query->whereIn('zp_tickets.status', $statusGroups['DONE']);
|
|
} else {
|
|
$query->whereRaw('1=0'); // Empty status group = no matches
|
|
}
|
|
|
|
$result = $query->first();
|
|
|
|
return $result->allEffort ?? 0;
|
|
}
|
|
|
|
public function getEffortOfAllTickets($projectId, $averageStorySize): mixed
|
|
{
|
|
$result = $this->connection->table('zp_tickets')
|
|
->selectRaw('SUM(CASE WHEN zp_tickets.storypoints IS NOT NULL AND zp_tickets.storypoints <> 0 THEN zp_tickets.storypoints ELSE ? END) AS '.$this->dbHelper->wrapColumn('allEffort'), [$averageStorySize])
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
->where('zp_tickets.projectId', $projectId)
|
|
->first();
|
|
|
|
return $result->allEffort ?? 0;
|
|
}
|
|
|
|
public function getAverageTodoSize($projectId): mixed
|
|
{
|
|
$result = $this->connection->table('zp_tickets')
|
|
->selectRaw('AVG(zp_tickets.storypoints) as '.$this->dbHelper->wrapColumn('avgSize'))
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
->where('zp_tickets.storypoints', '<>', 0)
|
|
->whereNotNull('zp_tickets.storypoints')
|
|
->where('zp_tickets.projectId', $projectId)
|
|
->first();
|
|
|
|
return $result->avgSize ?? null;
|
|
}
|
|
|
|
/**
|
|
* Single-query equivalent of getNumberOfAllTickets + getNumberOfClosedTickets +
|
|
* getEffortOfAllTickets + getEffortOfClosedTickets for one project. Used by the
|
|
* project-progress widget to avoid four separate table scans per project.
|
|
*
|
|
* The effort expression mirrors the standalone effort methods exactly: a ticket's
|
|
* storypoints, or the project's average story size as a fallback when unset/zero.
|
|
*
|
|
* @param int|string $projectId
|
|
* @param mixed $averageStorySize Fallback effort for tickets without storypoints.
|
|
* @return array{allCount:int, closedCount:int, allEffort:float, closedEffort:float}
|
|
*/
|
|
public function getProjectProgressAggregates($projectId, $averageStorySize): array
|
|
{
|
|
$statusGroupsSQL = $this->getStatusListGroupedByType($projectId);
|
|
$statusGroups = $this->dbHelper->parseStatusGroups($statusGroupsSQL);
|
|
$doneStatuses = $statusGroups['DONE'] ?? [];
|
|
|
|
$effortExpr = 'CASE WHEN zp_tickets.storypoints IS NOT NULL AND zp_tickets.storypoints <> 0 THEN zp_tickets.storypoints ELSE ? END';
|
|
|
|
$select = 'COUNT(*) AS '.$this->dbHelper->wrapColumn('allCount')
|
|
.', SUM('.$effortExpr.') AS '.$this->dbHelper->wrapColumn('allEffort');
|
|
$bindings = [$averageStorySize];
|
|
|
|
if (! empty($doneStatuses)) {
|
|
$placeholders = implode(',', array_fill(0, count($doneStatuses), '?'));
|
|
|
|
$select .= ', SUM(CASE WHEN zp_tickets.status IN ('.$placeholders.') THEN 1 ELSE 0 END) AS '.$this->dbHelper->wrapColumn('closedCount')
|
|
.', SUM(CASE WHEN zp_tickets.status IN ('.$placeholders.') THEN ('.$effortExpr.') ELSE 0 END) AS '.$this->dbHelper->wrapColumn('closedEffort');
|
|
|
|
// Binding order must match the placeholders left-to-right:
|
|
// allEffort fallback, closedCount IN-list, closedEffort IN-list, closedEffort fallback.
|
|
$bindings = array_merge([$averageStorySize], $doneStatuses, $doneStatuses, [$averageStorySize]);
|
|
}
|
|
|
|
$result = $this->connection->table('zp_tickets')
|
|
->selectRaw($select, $bindings)
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
->where('zp_tickets.projectId', $projectId)
|
|
->first();
|
|
|
|
return [
|
|
'allCount' => (int) ($result->allCount ?? 0),
|
|
'closedCount' => (int) ($result->closedCount ?? 0),
|
|
'allEffort' => (float) ($result->allEffort ?? 0),
|
|
'closedEffort' => (float) ($result->closedEffort ?? 0),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* addTicket - add a Ticket with postback test
|
|
*/
|
|
public function addTicket(array $values): bool|int
|
|
{
|
|
$ticketId = $this->connection->table('zp_tickets')->insertGetId([
|
|
'headline' => $values['headline'],
|
|
'type' => $values['type'],
|
|
'description' => $values['description'],
|
|
'date' => $values['date'],
|
|
'dateToFinish' => $values['dateToFinish'],
|
|
'projectId' => $values['projectId'],
|
|
'status' => $values['status'],
|
|
'userId' => $values['userId'],
|
|
'tags' => $values['tags'],
|
|
'sprint' => $values['sprint'],
|
|
'storypoints' => $values['storypoints'],
|
|
'priority' => $values['priority'],
|
|
'hourRemaining' => $values['hourRemaining'],
|
|
'planHours' => $values['planHours'],
|
|
'acceptanceCriteria' => $values['acceptanceCriteria'],
|
|
'outcomeImpact' => $values['outcomeImpact'] ?? null,
|
|
'editFrom' => $values['editFrom'],
|
|
'editTo' => $values['editTo'],
|
|
'editorId' => $values['editorId'],
|
|
'dependingTicketId' => $values['dependingTicketId'] ?? null,
|
|
'milestoneid' => $values['milestoneid'] ?? null,
|
|
'sortindex' => $values['sortIndex'] ?? null,
|
|
'kanbanSortIndex' => 0,
|
|
'modified' => dtHelper()->userNow()->formatDateTimeForDb(),
|
|
]);
|
|
|
|
if ($ticketId !== false) {
|
|
$ticketId = intval($ticketId);
|
|
$collaborators = $this->normalizeCollaborators($values['collaborators'] ?? [], $values['editorId'] ?? null);
|
|
if (! empty($collaborators)) {
|
|
$this->addCollaborators($ticketId, $collaborators, $values['userId']);
|
|
}
|
|
|
|
return $ticketId;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Patchable columns on the zp_tickets table.
|
|
* Only these fields will be included in patch UPDATE queries.
|
|
*
|
|
* @var array<string, true>
|
|
*/
|
|
private const PATCHABLE_COLUMNS = [
|
|
'headline' => true,
|
|
'type' => true,
|
|
'description' => true,
|
|
'projectId' => true,
|
|
'status' => true,
|
|
'date' => true,
|
|
'dateToFinish' => true,
|
|
'sprint' => true,
|
|
'storypoints' => true,
|
|
'priority' => true,
|
|
'hourRemaining' => true,
|
|
'planHours' => true,
|
|
'tags' => true,
|
|
'editorId' => true,
|
|
'userId' => true,
|
|
'editFrom' => true,
|
|
'editTo' => true,
|
|
'acceptanceCriteria' => true,
|
|
'outcomeImpact' => true,
|
|
'dependingTicketId' => true,
|
|
'milestoneid' => true,
|
|
'sortIndex' => true,
|
|
'kanbanSortIndex' => true,
|
|
];
|
|
|
|
/**
|
|
* Patch specific fields on a ticket.
|
|
*
|
|
* Only fields present in PATCHABLE_COLUMNS are included in the update.
|
|
* Non-column fields (e.g. request_parts, saveTicket) are silently ignored.
|
|
*
|
|
* @param int|string $id The ticket ID.
|
|
* @param array $params The fields to update.
|
|
* @return bool Whether any rows were affected.
|
|
*/
|
|
public function patchTicket($id, array $params): bool
|
|
{
|
|
$this->addTicketChange(session('userdata.id'), $id, $params);
|
|
|
|
// Match field names case-insensitively, then write the CANONICAL column name.
|
|
// PATCHABLE_COLUMNS is mostly camelCase but 'milestoneid' matches the real column, so a
|
|
// caller sending the documented 'milestoneId' fell through the case-sensitive isset()
|
|
// and was silently dropped while the call still reported success (#3692). Resolving to
|
|
// the canonical name (rather than aliasing) also keeps the UPDATE correct on
|
|
// PostgreSQL, where a quoted "milestoneId" would not match the milestoneid column.
|
|
$canonicalColumns = [];
|
|
foreach (array_keys(self::PATCHABLE_COLUMNS) as $column) {
|
|
$canonicalColumns[strtolower($column)] = $column;
|
|
}
|
|
|
|
$updates = [];
|
|
foreach ($params as $key => $value) {
|
|
$sanitizedKey = strtolower(DbCore::sanitizeToColumnString($key));
|
|
|
|
if (! isset($canonicalColumns[$sanitizedKey])) {
|
|
continue;
|
|
}
|
|
|
|
$updates[$canonicalColumns[$sanitizedKey]] = $value;
|
|
|
|
if ($sanitizedKey === 'status') {
|
|
TicketStatusUpdated::dispatch(ticketId: (int) $id, status: $value, legacyHook: __FUNCTION__);
|
|
}
|
|
}
|
|
|
|
if (empty($updates)) {
|
|
return false;
|
|
}
|
|
|
|
$updates['modified'] = dtHelper()->userNow()->formatDateTimeForDb();
|
|
|
|
return (bool) $this->connection->table('zp_tickets')
|
|
->where('id', $id)
|
|
->update($updates);
|
|
}
|
|
|
|
/**
|
|
* updateTicket - Update Ticketinformation
|
|
*/
|
|
public function updateTicket(array $values, $id): bool
|
|
{
|
|
$this->addTicketChange(session('userdata.id'), $id, $values);
|
|
|
|
$updates = [
|
|
'headline' => $values['headline'],
|
|
'type' => $values['type'],
|
|
'description' => $values['description'],
|
|
'projectId' => $values['projectId'],
|
|
'status' => $values['status'],
|
|
'date' => $values['date'],
|
|
'dateToFinish' => $values['dateToFinish'],
|
|
'sprint' => $values['sprint'],
|
|
'storypoints' => $values['storypoints'],
|
|
'priority' => $values['priority'],
|
|
'hourRemaining' => $values['hourRemaining'],
|
|
'planHours' => $values['planHours'],
|
|
'tags' => $values['tags'],
|
|
'editorId' => $values['editorId'],
|
|
'editFrom' => $values['editFrom'],
|
|
'editTo' => $values['editTo'],
|
|
'acceptanceCriteria' => $values['acceptanceCriteria'],
|
|
'dependingTicketId' => $values['dependingTicketId'],
|
|
'milestoneid' => $values['milestoneid'],
|
|
'modified' => dtHelper()->userNow()->formatDateTimeForDb(),
|
|
];
|
|
|
|
// Only touch the outcome narrative when the caller sends it — callers that rebuild the
|
|
// full value array (e.g. quickUpdateMilestone) must not wipe a saved outcome.
|
|
if (array_key_exists('outcomeImpact', $values)) {
|
|
$updates['outcomeImpact'] = $values['outcomeImpact'];
|
|
}
|
|
|
|
$result = $this->connection->table('zp_tickets')
|
|
->where('id', $id)
|
|
->update($updates);
|
|
|
|
$this->removeCollaborators($id);
|
|
|
|
// Add new collaborators
|
|
$this->addCollaborators(
|
|
$id,
|
|
$this->normalizeCollaborators($values['collaborators'] ?? [], $values['editorId'] ?? null),
|
|
session('userdata.id')
|
|
);
|
|
|
|
return $result !== false;
|
|
}
|
|
|
|
public function updateTicketStatus($ticketId, $status, int $ticketSorting = -1, $handler = null): bool
|
|
{
|
|
$this->addTicketChange(session('userdata.id'), $ticketId, ['status' => $status]);
|
|
|
|
$updates = [
|
|
'status' => $status,
|
|
'modified' => dtHelper()->userNow()->formatDateTimeForDb(),
|
|
];
|
|
|
|
if ($ticketSorting > -1) {
|
|
$updates['kanbanSortIndex'] = $ticketSorting;
|
|
}
|
|
|
|
TicketStatusUpdated::dispatch(ticketId: (int) $ticketId, status: $status, handler: $handler, legacyHook: __FUNCTION__);
|
|
|
|
return $this->connection->table('zp_tickets')
|
|
->where('id', $ticketId)
|
|
->update($updates) > 0;
|
|
}
|
|
|
|
/**
|
|
* Records ticket field changes in the history table.
|
|
* Uses a single batch insert instead of individual inserts per changed field.
|
|
*
|
|
* @param int $userId The user making the change.
|
|
* @param int $ticketId The ticket being changed.
|
|
* @param array $values The new values being applied.
|
|
*/
|
|
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',
|
|
];
|
|
|
|
// Only select the columns we actually need to compare, not the entire row
|
|
$trackedColumns = array_unique(array_values($fields));
|
|
$oldValues = $this->connection->table('zp_tickets')
|
|
->select($trackedColumns)
|
|
->where('id', $ticketId)
|
|
->first();
|
|
|
|
if (! $oldValues) {
|
|
return;
|
|
}
|
|
|
|
$oldValues = (array) $oldValues;
|
|
$now = date('Y-m-d H:i:s');
|
|
$historyRows = [];
|
|
|
|
// Compare tracked fields
|
|
foreach ($fields as $enum => $dbTable) {
|
|
if (
|
|
isset($values[$dbTable]) === true &&
|
|
isset($oldValues[$dbTable]) === true &&
|
|
($oldValues[$dbTable] != $values[$dbTable]) &&
|
|
// Skip genuine "cleared to empty" writes, but STRICTLY — a loose
|
|
// `!= ''` also drops valid falsy values, most importantly
|
|
// status 0 (Done). That silently kept ticket-closures out of
|
|
// zp_tickethistory, so burndown/throughput and the mobile
|
|
// Progress "closed on date" reflection never saw them.
|
|
($values[$dbTable] !== '' && $values[$dbTable] !== null)
|
|
) {
|
|
$historyRows[] = [
|
|
'userId' => $userId,
|
|
'ticketId' => $ticketId,
|
|
'changeType' => $enum,
|
|
'changeValue' => $values[$dbTable],
|
|
'dateModified' => $now,
|
|
];
|
|
}
|
|
}
|
|
|
|
// Single batch insert instead of N individual inserts
|
|
if (! empty($historyRows)) {
|
|
$this->connection->table('zp_tickethistory')->insert($historyRows);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Status-change history events for a set of tickets within a date range.
|
|
*
|
|
* A general reporting primitive. Every time a ticket's status changes,
|
|
* addTicketChange() writes a zp_tickethistory row (changeType 'status',
|
|
* changeValue = the new status id, dateModified = timestamp). This returns
|
|
* those rows for the given tickets over [fromDate, toDate], newest first.
|
|
*
|
|
* Deliberately status-config-agnostic: callers resolve changeValue against
|
|
* their project's status labels to decide which changes count as "to DONE",
|
|
* "to in progress", etc. That keeps this one query reusable across mobile's
|
|
* "done today" reflection, throughput/burndown reporting, and strategy-level
|
|
* progress rollups.
|
|
*
|
|
* @param int[] $ticketIds
|
|
* @param string $fromDate inclusive, 'Y-m-d'
|
|
* @param string $toDate inclusive, 'Y-m-d'
|
|
* @return array<int, array{ticketId:int, changeValue:string, dateModified:string}>
|
|
*/
|
|
public function getStatusChangeEvents(array $ticketIds, string $fromDate, string $toDate): array
|
|
{
|
|
if (empty($ticketIds)) {
|
|
return [];
|
|
}
|
|
|
|
$rows = $this->connection->table('zp_tickethistory')
|
|
->select('ticketId', 'changeValue', 'dateModified')
|
|
->where('changeType', 'status')
|
|
->whereIn('ticketId', $ticketIds)
|
|
->whereBetween('dateModified', [$fromDate.' 00:00:00', $toDate.' 23:59:59'])
|
|
->orderBy('dateModified', 'desc')
|
|
->get();
|
|
|
|
$events = [];
|
|
foreach ($rows as $row) {
|
|
$events[] = (array) $row;
|
|
}
|
|
|
|
return $events;
|
|
}
|
|
|
|
/**
|
|
* Distinct ticket ids the given user COMMENTED on within [from, to]
|
|
* (inclusive, 'Y-m-d'). Access-agnostic on its own — callers must constrain
|
|
* the result to an access-scoped set (e.g. the user's accessible projects)
|
|
* before returning anything, so this never widens visibility. Used by
|
|
* getMyCommentedTicketsForRange for Progress "Supported".
|
|
*/
|
|
public function getTicketIdsCommentedByUser(int $userId, string $fromDate, string $toDate): array
|
|
{
|
|
return $this->connection->table('zp_comment')
|
|
->where('module', 'ticket')
|
|
->where('userId', $userId)
|
|
->whereBetween('date', [$fromDate.' 00:00:00', $toDate.' 23:59:59'])
|
|
// moduleId is nullable in the schema; skip NULLs at the query level so
|
|
// (int) NULL doesn't inject a bogus id 0 into the downstream whereIn().
|
|
->whereNotNull('moduleId')
|
|
->distinct()
|
|
->pluck('moduleId')
|
|
->map(fn ($id) => (int) $id)
|
|
->filter() // belt-and-suspenders: drop any 0 (e.g. an empty-string id)
|
|
->values()
|
|
->all();
|
|
}
|
|
|
|
/**
|
|
* Ticket rows for the given ids, constrained to the given projects (the
|
|
* caller's access boundary). One row per ticket with the fields mobile
|
|
* user-ticket consumers expect (id, headline, projectId, projectName,
|
|
* editorId, userId, status, dateToFinish). Returns raw rows — no resolved
|
|
* statusLabel/statusClass. Used by getMyCommentedTicketsForRange.
|
|
*/
|
|
public function getTicketsByIdsWithinProjects(array $ticketIds, array $projectIds): array
|
|
{
|
|
if (empty($ticketIds) || empty($projectIds)) {
|
|
return [];
|
|
}
|
|
|
|
$rows = $this->connection->table('zp_tickets')
|
|
->leftJoin('zp_projects', 'zp_tickets.projectId', '=', 'zp_projects.id')
|
|
->select(
|
|
'zp_tickets.id',
|
|
'zp_tickets.headline',
|
|
'zp_tickets.projectId',
|
|
'zp_tickets.editorId',
|
|
'zp_tickets.userId',
|
|
'zp_tickets.status',
|
|
'zp_tickets.dateToFinish',
|
|
'zp_projects.name as projectName',
|
|
)
|
|
->whereIn('zp_tickets.id', $ticketIds)
|
|
->whereIn('zp_tickets.projectId', $projectIds)
|
|
// "Supported" is a task surface — exclude milestones the same way the
|
|
// other mobile user-ticket queries do, so a comment on a milestone
|
|
// doesn't surface a milestone row here.
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
// Stable, deterministic order (DB default order is unspecified).
|
|
->orderBy('zp_tickets.dateToFinish')
|
|
->orderBy('zp_tickets.id')
|
|
->get();
|
|
|
|
return array_map(fn ($row) => (array) $row, $rows->all());
|
|
}
|
|
|
|
/**
|
|
* Get all tasks (and optionally subtasks) that belong to a milestone
|
|
/**
|
|
* Get all tasks (and optionally subtasks) that belong to a milestone
|
|
*
|
|
* @param int $milestoneId The milestone ID
|
|
* @param int|null $projectId The project ID (defaults to current project)
|
|
* @return array Array of tickets
|
|
*/
|
|
public function getTasksByMilestone(int $milestoneId, ?int $projectId = null): array
|
|
{
|
|
if ($projectId === null) {
|
|
$projectId = session('currentProject');
|
|
}
|
|
|
|
$results = $this->connection->table('zp_tickets')
|
|
->where('milestoneid', $milestoneId)
|
|
->where('projectId', $projectId)
|
|
->orderBy('sortindex', 'ASC')
|
|
->orderBy('id', 'DESC')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
/**
|
|
* Get all subtasks that have a parent task
|
|
*
|
|
* @param int $parentTicketId The parent ticket ID
|
|
* @return array Array of subtasks
|
|
*/
|
|
public function getSubtasksByParent(int $parentTicketId): array
|
|
{
|
|
$results = $this->connection->table('zp_tickets')
|
|
->where('dependingTicketId', $parentTicketId)
|
|
->orderBy('sortindex', 'ASC')
|
|
->orderBy('id', 'DESC')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
/**
|
|
* delTicket - delete a Ticket and all dependencies
|
|
*/
|
|
public function delticket($id): bool
|
|
{
|
|
$this->removeCollaborators((int) $id);
|
|
|
|
$this->connection->table('zp_tickets')
|
|
->where('id', $id)
|
|
->delete();
|
|
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* @return true
|
|
*/
|
|
public function delMilestone($id): bool
|
|
{
|
|
// Clear milestoneid from tickets
|
|
$this->connection->table('zp_tickets')
|
|
->where('milestoneid', $id)
|
|
->update([
|
|
'milestoneid' => '',
|
|
'modified' => dtHelper()->userNow()->formatDateTimeForDb(),
|
|
]);
|
|
|
|
// Clear milestoneid from canvas items
|
|
$this->connection->table('zp_canvas_items')
|
|
->where('milestoneid', $id)
|
|
->update(['milestoneid' => '']);
|
|
|
|
// Delete the milestone
|
|
$this->connection->table('zp_tickets')
|
|
->where('id', $id)
|
|
->delete();
|
|
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* Adds collaborators to a ticket.
|
|
*
|
|
* @param int $ticketId The ID of the ticket.
|
|
* @param array $collaborators An array of user IDs to add as collaborators.
|
|
* @param int $createdBy The ID of the user adding the collaborators.
|
|
* @return bool Returns true if the operation is successful.
|
|
*/
|
|
/**
|
|
* Adds collaborators to a ticket using a single batch insert.
|
|
*
|
|
* @param int $ticketId The ID of the ticket.
|
|
* @param array $collaborators An array of user IDs to add as collaborators.
|
|
* @param int $createdBy The ID of the user adding the collaborators.
|
|
* @return bool Returns true if the operation is successful.
|
|
*/
|
|
public function addCollaborators(int $ticketId, array $collaborators, int $createdBy): bool
|
|
{
|
|
$collaborators = $this->normalizeCollaborators($collaborators);
|
|
|
|
if (empty($collaborators)) {
|
|
return true;
|
|
}
|
|
|
|
$now = now();
|
|
$rows = array_map(fn ($userId) => [
|
|
'entityA' => $ticketId,
|
|
'entityAType' => 'Ticket',
|
|
'entityB' => $userId,
|
|
'entityBType' => 'User',
|
|
'relationship' => EntityRelationshipEnum::Collaborator->value,
|
|
'createdOn' => $now,
|
|
'createdBy' => $createdBy,
|
|
], $collaborators);
|
|
|
|
// Single batch insert instead of N individual inserts
|
|
$this->connection->table('zp_entity_relationship')->insert($rows);
|
|
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* Retrieves all collaborators for a ticket.
|
|
*
|
|
* @param int $ticketId The ID of the ticket.
|
|
* @return array An array of user IDs who are collaborators.
|
|
*/
|
|
public function getCollaborators(int $ticketId): array
|
|
{
|
|
return $this->connection->table('zp_entity_relationship')
|
|
->select('entityB AS userId')
|
|
->where('entityA', $ticketId)
|
|
->where('entityAType', 'Ticket')
|
|
->where('entityBType', 'User')
|
|
->where('relationship', EntityRelationshipEnum::Collaborator->value)
|
|
->pluck('userId')
|
|
->map(fn ($userId) => (int) $userId)
|
|
->unique()
|
|
->values()
|
|
->toArray();
|
|
}
|
|
|
|
/**
|
|
* Retrieves collaborators for multiple tickets in a single query.
|
|
*
|
|
* @param array<int, int|string> $ticketIds
|
|
* @return array<int, array<int, int>>
|
|
*/
|
|
public function getCollaboratorsByTicketIds(array $ticketIds): array
|
|
{
|
|
$ticketIds = array_values(array_filter(array_map('intval', $ticketIds)));
|
|
|
|
if (empty($ticketIds)) {
|
|
return [];
|
|
}
|
|
|
|
$rows = $this->connection->table('zp_entity_relationship')
|
|
->select(['entityA', 'entityB'])
|
|
->whereIn('entityA', $ticketIds)
|
|
->where('entityAType', 'Ticket')
|
|
->where('entityBType', 'User')
|
|
->where('relationship', EntityRelationshipEnum::Collaborator->value)
|
|
->orderBy('entityA')
|
|
->orderBy('entityB')
|
|
->get();
|
|
|
|
$collaboratorsByTicket = [];
|
|
|
|
foreach ($rows as $row) {
|
|
$ticketId = (int) $row->entityA;
|
|
$userId = (int) $row->entityB;
|
|
$collaboratorsByTicket[$ticketId] ??= [];
|
|
if (! in_array($userId, $collaboratorsByTicket[$ticketId], true)) {
|
|
$collaboratorsByTicket[$ticketId][] = $userId;
|
|
}
|
|
}
|
|
|
|
return $collaboratorsByTicket;
|
|
}
|
|
|
|
/**
|
|
* Removes all collaborators from a ticket.
|
|
*
|
|
* @param int $ticketId The ID of the ticket.
|
|
* @return bool Returns true if the operation is successful.
|
|
*/
|
|
public function removeCollaborators(int $ticketId): bool
|
|
{
|
|
return $this->connection->table('zp_entity_relationship')
|
|
->where('entityA', $ticketId)
|
|
->where('entityAType', 'Ticket')
|
|
->where('entityBType', 'User')
|
|
->where('relationship', EntityRelationshipEnum::Collaborator->value)
|
|
->delete() > 0;
|
|
}
|
|
|
|
/**
|
|
* Normalize collaborator user IDs before relationship reads/writes.
|
|
*
|
|
* @param array<int, mixed> $collaborators
|
|
* @return array<int, int>
|
|
*/
|
|
private function normalizeCollaborators(array $collaborators, mixed $editorId = null): array
|
|
{
|
|
$editorId = (int) $editorId;
|
|
$normalized = [];
|
|
|
|
foreach ($collaborators as $userId) {
|
|
$userId = (int) $userId;
|
|
|
|
if ($userId <= 0 || ($editorId > 0 && $userId === $editorId)) {
|
|
continue;
|
|
}
|
|
|
|
$normalized[$userId] = $userId;
|
|
}
|
|
|
|
return array_values($normalized);
|
|
}
|
|
|
|
/**
|
|
* Bulk update sortindex values for multiple tickets in a single transaction.
|
|
*
|
|
* @param array<int, int> $updates Associative array of ticketId => sortIndex
|
|
* @return bool True on success, false on failure
|
|
*/
|
|
public function bulkUpdateSortIndex(array $updates): bool
|
|
{
|
|
if (empty($updates)) {
|
|
return true;
|
|
}
|
|
|
|
try {
|
|
$this->connection->beginTransaction();
|
|
|
|
foreach ($updates as $ticketId => $sortIndex) {
|
|
$this->connection->table('zp_tickets')
|
|
->where('id', (int) $ticketId)
|
|
->update([
|
|
'sortindex' => (int) $sortIndex,
|
|
'modified' => dtHelper()->userNow()->formatDateTimeForDb(),
|
|
]);
|
|
}
|
|
|
|
$this->connection->commit();
|
|
|
|
return true;
|
|
} catch (\Exception $e) {
|
|
$this->connection->rollBack();
|
|
\Illuminate\Support\Facades\Log::error($e);
|
|
|
|
return false;
|
|
}
|
|
}
|
|
}
|