1164 lines
41 KiB
PHP
1164 lines
41 KiB
PHP
<?php
|
|
|
|
namespace Leantime\Domain\Projects\Repositories;
|
|
|
|
use DateInterval;
|
|
use DatePeriod;
|
|
use Illuminate\Contracts\Container\BindingResolutionException;
|
|
use Illuminate\Database\ConnectionInterface;
|
|
use Leantime\Core\Configuration\Environment;
|
|
use Leantime\Core\Db\DatabaseHelper;
|
|
use Leantime\Core\Db\Db as DbCore;
|
|
use Leantime\Core\Events\DispatchesEvents as EventhelperCore;
|
|
use Leantime\Core\Support\Avatarcreator;
|
|
use Leantime\Domain\Auth\Models\Roles;
|
|
use Leantime\Domain\Users\Repositories\Users as UserRepository;
|
|
|
|
class Projects
|
|
{
|
|
use EventhelperCore;
|
|
|
|
public string $name = '';
|
|
|
|
public int $id = 0; // WAS: '';
|
|
|
|
public int $clientId = 0;
|
|
|
|
public object $result; // WAS: = '';
|
|
|
|
/**
|
|
* @var array state for projects
|
|
*/
|
|
public array $state = [0 => 'OPEN', 1 => 'CLOSED', null => 'OPEN'];
|
|
|
|
private ConnectionInterface $connection;
|
|
|
|
public function __construct(
|
|
protected Environment $config,
|
|
protected DbCore $db,
|
|
protected Avatarcreator $avatarcreator,
|
|
protected DatabaseHelper $dbHelper
|
|
) {
|
|
$this->config = $config;
|
|
$this->db = $db;
|
|
$this->connection = $db->getConnection();
|
|
}
|
|
|
|
/**
|
|
* getAll - get all projects open and closed
|
|
*/
|
|
public function getAll(bool $showClosedProjects = false): array
|
|
{
|
|
$query = $this->connection->table('zp_projects as project')
|
|
->select([
|
|
'project.id',
|
|
'project.name',
|
|
'project.clientId',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.state',
|
|
'project.menuType',
|
|
'project.type',
|
|
'project.modified',
|
|
'client.name as clientName',
|
|
'client.id as clientId',
|
|
'project.start',
|
|
'project.end',
|
|
])
|
|
->leftJoin('zp_clients as client', 'project.clientId', '=', 'client.id');
|
|
|
|
if ($showClosedProjects === false) {
|
|
$query->where(function ($q) {
|
|
$q->whereNull('project.state')
|
|
->orWhere('project.state', '<>', -1);
|
|
});
|
|
}
|
|
|
|
$query->groupBy([
|
|
'project.id',
|
|
'project.name',
|
|
'project.clientId',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.state',
|
|
'project.menuType',
|
|
'project.type',
|
|
'project.modified',
|
|
'client.name',
|
|
'client.id',
|
|
'project.start',
|
|
'project.end',
|
|
])
|
|
->orderBy('clientName')
|
|
->orderBy('project.name');
|
|
|
|
$results = $query->get();
|
|
|
|
// Get project IDs for fetching latest comment statuses
|
|
$projectIds = $results->pluck('id')->toArray();
|
|
|
|
// Fetch latest comment status per project using a separate query
|
|
$latestComments = [];
|
|
if (! empty($projectIds)) {
|
|
$comments = $this->connection->table('zp_comment')
|
|
->select('moduleId', 'status', 'date')
|
|
->where('module', 'project')
|
|
->whereIn('moduleId', $projectIds)
|
|
->orderByDesc('date')
|
|
->get();
|
|
|
|
// Group by moduleId and take only the first (latest) per project
|
|
foreach ($comments as $comment) {
|
|
if (! isset($latestComments[$comment->moduleId])) {
|
|
$latestComments[$comment->moduleId] = $comment->status;
|
|
}
|
|
}
|
|
}
|
|
|
|
// Merge comment status into results
|
|
return $results->map(function ($item) use ($latestComments) {
|
|
$arr = (array) $item;
|
|
$arr['status'] = $latestComments[$arr['id']] ?? null;
|
|
|
|
return $arr;
|
|
})->toArray();
|
|
}
|
|
|
|
/**
|
|
* Get projects by type
|
|
*
|
|
* @param string $type The project type (project, strategy, program)
|
|
* @return array Projects of the specified type
|
|
*/
|
|
public function getProjectsByType(string $type): array
|
|
{
|
|
$query = $this->connection->table('zp_projects as project')
|
|
->select([
|
|
'project.id',
|
|
'project.name',
|
|
'project.clientId',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.state',
|
|
'project.menuType',
|
|
'project.type',
|
|
'project.modified',
|
|
'client.name as clientName',
|
|
'client.id as clientId',
|
|
'project.start',
|
|
'project.end',
|
|
])
|
|
->leftJoin('zp_clients as client', 'project.clientId', '=', 'client.id')
|
|
->where('project.type', $type)
|
|
->groupBy([
|
|
'project.id',
|
|
'project.name',
|
|
'project.clientId',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.state',
|
|
'project.menuType',
|
|
'project.type',
|
|
'project.modified',
|
|
'client.name',
|
|
'client.id',
|
|
'project.start',
|
|
'project.end',
|
|
])
|
|
->orderBy('clientName')
|
|
->orderBy('project.name');
|
|
|
|
$results = $query->get();
|
|
|
|
// Get project IDs for fetching latest comment statuses
|
|
$projectIds = $results->pluck('id')->toArray();
|
|
|
|
// Fetch latest comment status per project using a separate query
|
|
$latestComments = [];
|
|
if (! empty($projectIds)) {
|
|
$comments = $this->connection->table('zp_comment')
|
|
->select('moduleId', 'status', 'date')
|
|
->where('module', 'project')
|
|
->whereIn('moduleId', $projectIds)
|
|
->orderByDesc('date')
|
|
->get();
|
|
|
|
foreach ($comments as $comment) {
|
|
if (! isset($latestComments[$comment->moduleId])) {
|
|
$latestComments[$comment->moduleId] = $comment->status;
|
|
}
|
|
}
|
|
}
|
|
|
|
// Merge comment status into results
|
|
return $results->map(function ($item) use ($latestComments) {
|
|
$arr = (array) $item;
|
|
$arr['status'] = $latestComments[$arr['id']] ?? null;
|
|
|
|
return $arr;
|
|
})->toArray();
|
|
}
|
|
|
|
/**
|
|
* Gets all users that have access to a project.
|
|
* For direct access only set the teamOnly flag to true
|
|
*/
|
|
public function getUsersAssignedToProject($id, $includeApiUsers = false): array|bool
|
|
{
|
|
$query = $this->connection->table('zp_relationuserproject')
|
|
->select([
|
|
'zp_user.id',
|
|
'zp_user.firstname',
|
|
'zp_user.lastname',
|
|
'zp_user.username',
|
|
'zp_user.notifications',
|
|
'zp_user.profileId',
|
|
'zp_user.jobTitle',
|
|
'zp_user.source',
|
|
'zp_user.status',
|
|
'zp_user.modified',
|
|
'zp_user.role',
|
|
'zp_relationuserproject.projectRole',
|
|
])
|
|
->distinct()
|
|
->leftJoin('zp_user', 'zp_relationuserproject.userId', '=', 'zp_user.id')
|
|
->leftJoin('zp_projects', 'zp_relationuserproject.projectId', '=', 'zp_projects.id')
|
|
->where('zp_relationuserproject.projectId', $id)
|
|
->whereNotNull('zp_user.id');
|
|
|
|
if ($includeApiUsers === false) {
|
|
$query->where(function ($q) {
|
|
$q->whereNull('zp_user.source')
|
|
->orWhere('zp_user.source', '<>', 'api');
|
|
});
|
|
}
|
|
|
|
$query->orderBy('zp_user.lastname');
|
|
|
|
$results = $query->get();
|
|
|
|
// Post-process to use firstname if available, otherwise username
|
|
return $results->map(function ($item) {
|
|
$arr = (array) $item;
|
|
$arr['firstname'] = $arr['firstname'] ?? $arr['username'];
|
|
|
|
return $arr;
|
|
})->toArray();
|
|
}
|
|
|
|
/**
|
|
* Gets all users that can access a project, honoring the project's access level.
|
|
*
|
|
* Directly assigned users are always included. On top of that the project's
|
|
* psettings widen the set: 'all' adds every active user, 'clients' adds the
|
|
* active users of the project's client. This mirrors isUserAssignedToProject(),
|
|
* which grants those users access — they must therefore also be offered in
|
|
* assignee dropdowns (#3331).
|
|
*
|
|
* @param int $id The ID of the project.
|
|
* @param bool $includeApiUsers Whether to include API service-account users.
|
|
* @return array The users with access to the project.
|
|
*/
|
|
public function getUsersWithAccessToProject($id, $includeApiUsers = false): array
|
|
{
|
|
$project = $this->getProject($id);
|
|
if ($project === false) {
|
|
return [];
|
|
}
|
|
|
|
$query = $this->connection->table('zp_user')
|
|
->select([
|
|
'zp_user.id',
|
|
'zp_user.firstname',
|
|
'zp_user.lastname',
|
|
'zp_user.username',
|
|
'zp_user.notifications',
|
|
'zp_user.profileId',
|
|
'zp_user.jobTitle',
|
|
'zp_user.source',
|
|
'zp_user.status',
|
|
'zp_user.modified',
|
|
'zp_user.role',
|
|
'zp_relationuserproject.projectRole',
|
|
])
|
|
->distinct()
|
|
->leftJoin('zp_relationuserproject', function ($join) use ($id) {
|
|
$join->on('zp_user.id', '=', 'zp_relationuserproject.userId')
|
|
->where('zp_relationuserproject.projectId', '=', $id);
|
|
})
|
|
->where(function ($q) use ($project) {
|
|
$q->whereNotNull('zp_relationuserproject.userId');
|
|
|
|
if (($project['psettings'] ?? '') == 'all') {
|
|
$q->orWhere('zp_user.status', 'a');
|
|
} elseif (($project['psettings'] ?? '') == 'clients') {
|
|
$q->orWhere(function ($q2) use ($project) {
|
|
$q2->where('zp_user.status', 'a')
|
|
->where('zp_user.clientId', $project['clientId']);
|
|
});
|
|
}
|
|
});
|
|
|
|
if ($includeApiUsers === false) {
|
|
$query->where(function ($q) {
|
|
$q->whereNull('zp_user.source')
|
|
->orWhere('zp_user.source', '<>', 'api');
|
|
});
|
|
}
|
|
|
|
$results = $query->orderBy('zp_user.lastname')->get();
|
|
|
|
return $results->map(function ($item) {
|
|
$arr = (array) $item;
|
|
$arr['firstname'] = $arr['firstname'] ?? $arr['username'];
|
|
|
|
return $arr;
|
|
})->toArray();
|
|
}
|
|
|
|
/**
|
|
* Retrieves the relationship of users assigned to a specific project.
|
|
*
|
|
* @param int $id The ID of the project.
|
|
* @param bool $includeApiUsers Flag to determine whether to include API users. Default is false.
|
|
* @return array|bool Returns an array of users assigned to the project or false on failure.
|
|
*
|
|
* @Deprecated
|
|
*/
|
|
public function getProjectUserRelation($id, $includeApiUsers = false): array|bool
|
|
{
|
|
return $this->getUsersAssignedToProject($id, $includeApiUsers);
|
|
}
|
|
|
|
/**
|
|
* The shared "user can reach this project" predicate used by both
|
|
* getUserProjects('all') and getProjectsUserHasAccessTo(): the user is a
|
|
* member, the project is public (psettings='all'), or the user is an
|
|
* admin/owner (role >= 40, resolved via the requestingUser join each caller
|
|
* already applies). The client-shared case differs between callers
|
|
* (own-client column vs a passed client id), so each passes its own
|
|
* $clientClause. Centralising this stops the two access paths from drifting
|
|
* — the drift that previously left the sidebar empty while the switcher worked.
|
|
*
|
|
* @param \Illuminate\Database\Query\Builder $q The where-group builder.
|
|
* @param int $userId The user whose access is being evaluated.
|
|
* @param callable $clientClause Applies the caller-specific psettings='clients' match.
|
|
*/
|
|
private function accessibleProjectPredicate($q, int $userId, callable $clientClause): void
|
|
{
|
|
$q->where('relation.userId', $userId)
|
|
->orWhere('project.psettings', 'all')
|
|
->orWhere($clientClause)
|
|
->orWhere('requestingUser.role', '>=', 40);
|
|
}
|
|
|
|
public function getUserProjects(int $userId, string $projectStatus = 'all', ?int $clientId = null, string $accessStatus = 'assigned', string $projectTypes = 'all'): false|array
|
|
{
|
|
$query = $this->connection->table('zp_projects as project')
|
|
->select([
|
|
'project.id',
|
|
'project.name',
|
|
'project.details',
|
|
'project.clientId',
|
|
'project.state',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.menuType',
|
|
'project.type',
|
|
'project.parent',
|
|
'project.modified',
|
|
'project.start',
|
|
'project.end',
|
|
'client.name as clientName',
|
|
'client.id as clientId',
|
|
'parent.id as parentId',
|
|
'parent.name as parentName',
|
|
])
|
|
->selectRaw('CASE WHEN favorite.id IS NULL THEN false ELSE true END as '.$this->dbHelper->wrapColumn('isFavorite'))
|
|
->leftJoin('zp_relationuserproject as relation', 'project.id', '=', 'relation.projectId')
|
|
->leftJoin('zp_projects as parent', 'parent.id', '=', 'project.parent')
|
|
->leftJoin('zp_clients as client', 'project.clientId', '=', 'client.id')
|
|
->leftJoin('zp_user as user', 'relation.userId', '=', 'user.id')
|
|
->leftJoin('zp_reactions as favorite', function ($join) use ($userId) {
|
|
$join->on('project.id', '=', 'favorite.moduleId')
|
|
->where('favorite.module', 'project')
|
|
->where('favorite.reaction', 'favorite')
|
|
->where('favorite.userId', $userId);
|
|
})
|
|
->leftJoin('zp_user as requestingUser', function ($join) use ($userId) {
|
|
$join->on('requestingUser.id', '=', $this->connection->raw((int) $userId));
|
|
})
|
|
->where(function ($q) {
|
|
$q->where('project.active', '>', -1)
|
|
->orWhereNull('project.active');
|
|
});
|
|
|
|
// All Projects this user has access to
|
|
if ($accessStatus == 'all') {
|
|
$query->where(function ($q) use ($userId) {
|
|
// Client-shared case here matches the requesting user's own client.
|
|
$this->accessibleProjectPredicate($q, $userId, function ($q2) {
|
|
$q2->where('project.psettings', 'clients')
|
|
->whereColumn('project.clientId', 'requestingUser.clientId');
|
|
});
|
|
});
|
|
} elseif ($accessStatus == 'clients') {
|
|
$query->where(function ($q) use ($userId) {
|
|
$q->where('relation.userId', $userId)
|
|
->orWhere(function ($q2) {
|
|
$q2->where('project.psettings', 'clients')
|
|
->whereColumn('project.clientId', 'requestingUser.clientId');
|
|
});
|
|
});
|
|
} else {
|
|
$query->where('relation.userId', $userId);
|
|
}
|
|
|
|
if ($projectStatus == 'open') {
|
|
$query->where(function ($q) {
|
|
$q->where('project.state', '<>', -1)
|
|
->orWhereNull('project.state');
|
|
});
|
|
} elseif ($projectStatus == 'closed') {
|
|
$query->where('project.state', -1);
|
|
}
|
|
|
|
if ($clientId != '' && $clientId != null && $clientId > 0) {
|
|
$query->where('project.clientId', $clientId);
|
|
}
|
|
|
|
if ($projectTypes != 'all' && $projectTypes != 'project') {
|
|
$types = explode(',', $projectTypes);
|
|
$query->whereIn('project.type', $types);
|
|
}
|
|
|
|
if ($projectTypes == 'project') {
|
|
$query->where(function ($q) {
|
|
$q->where('project.type', 'project')
|
|
->orWhereNull('project.type');
|
|
});
|
|
}
|
|
|
|
$query->groupBy([
|
|
'project.id',
|
|
'project.name',
|
|
'project.details',
|
|
'project.clientId',
|
|
'project.state',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.menuType',
|
|
'project.type',
|
|
'project.parent',
|
|
'project.modified',
|
|
'project.start',
|
|
'project.end',
|
|
'client.name',
|
|
'client.id',
|
|
'parent.id',
|
|
'parent.name',
|
|
'favorite.id',
|
|
])
|
|
->orderBy('clientName')
|
|
->orderBy('project.name');
|
|
|
|
$results = $query->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
// This populates the projects show all tab and shows users all the projects that they could access
|
|
public function getProjectsUserHasAccessTo($userId, string $status = 'all', int $clientId = 0): false|array
|
|
{
|
|
$query = $this->connection->table('zp_projects as project')
|
|
->select([
|
|
'project.id',
|
|
'project.name',
|
|
'project.clientId',
|
|
'project.state',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.menuType',
|
|
'project.type',
|
|
'project.parent',
|
|
'project.modified',
|
|
'client.name as clientName',
|
|
'client.id as clientId',
|
|
])
|
|
->selectRaw('CASE WHEN favorite.id IS NULL THEN false ELSE true END as '.$this->dbHelper->wrapColumn('isFavorite'))
|
|
->leftJoin('zp_relationuserproject as relation', 'project.id', '=', 'relation.projectId')
|
|
->leftJoin('zp_clients as client', 'project.clientId', '=', 'client.id')
|
|
->leftJoin('zp_reactions as favorite', function ($join) use ($userId) {
|
|
$join->on('project.id', '=', 'favorite.moduleId')
|
|
->where('favorite.module', 'project')
|
|
->where('favorite.reaction', 'favorite')
|
|
->where('favorite.userId', $userId);
|
|
})
|
|
// Join the requesting user so their role can grant blanket access
|
|
// (admins/owners see every project), mirroring getUserProjects().
|
|
->leftJoin('zp_user as requestingUser', function ($join) use ($userId) {
|
|
$join->on('requestingUser.id', '=', $this->connection->raw((int) $userId));
|
|
})
|
|
->where(function ($q) use ($userId, $clientId) {
|
|
// Client-shared case here matches a passed client id.
|
|
$this->accessibleProjectPredicate($q, $userId, function ($q2) use ($clientId) {
|
|
$q2->where('project.psettings', 'clients')
|
|
->where('project.clientId', $clientId);
|
|
});
|
|
})
|
|
->where(function ($q) {
|
|
$q->where('project.active', '>', -1)
|
|
->orWhereNull('project.active');
|
|
});
|
|
|
|
if ($status == 'open') {
|
|
$query->where(function ($q) {
|
|
$q->where('project.state', '<>', -1)
|
|
->orWhereNull('project.state');
|
|
});
|
|
} elseif ($status == 'closed') {
|
|
$query->where('project.state', -1);
|
|
}
|
|
|
|
$query->groupBy([
|
|
'project.id',
|
|
'project.name',
|
|
'project.clientId',
|
|
'project.state',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.menuType',
|
|
'project.type',
|
|
'project.parent',
|
|
'project.modified',
|
|
'client.name',
|
|
'client.id',
|
|
'favorite.id',
|
|
])
|
|
->orderBy('clientName')
|
|
->orderBy('project.name');
|
|
|
|
$results = $query->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
/**
|
|
* @return int|mixed
|
|
*/
|
|
public function getNumberOfProjects($clientId = null, $type = null): mixed
|
|
{
|
|
$query = $this->connection->table('zp_projects')
|
|
->where('id', '>', 0);
|
|
|
|
if ($clientId != null && is_numeric($clientId)) {
|
|
$query->where('clientId', $clientId);
|
|
}
|
|
|
|
if ($type != null) {
|
|
$query->where('type', $type);
|
|
}
|
|
|
|
return $query->count();
|
|
}
|
|
|
|
// Get all open user projects /param: open, closed, all
|
|
|
|
public function getClientProjects($clientId): false|array
|
|
{
|
|
$results = $this->connection->table('zp_projects as project')
|
|
->select([
|
|
'project.id',
|
|
'project.name',
|
|
'project.clientId',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.state',
|
|
'project.menuType',
|
|
'project.modified',
|
|
'project.type',
|
|
'client.name as clientName',
|
|
'client.id as clientId',
|
|
])
|
|
->leftJoin('zp_clients as client', 'project.clientId', '=', 'client.id')
|
|
->where(function ($q) {
|
|
$q->where('project.active', '>', -1)
|
|
->orWhereNull('project.active');
|
|
})
|
|
->where('clientId', $clientId)
|
|
->groupBy([
|
|
'project.id',
|
|
'project.name',
|
|
'project.clientId',
|
|
'project.hourBudget',
|
|
'project.dollarBudget',
|
|
'project.state',
|
|
'project.menuType',
|
|
'project.modified',
|
|
'project.type',
|
|
'client.name',
|
|
'client.id',
|
|
])
|
|
->orderBy('clientName')
|
|
->orderBy('project.name')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
public function getProjectTickets($projectId): false|array
|
|
{
|
|
$results = $this->connection->table('zp_tickets')
|
|
->select([
|
|
'zp_tickets.id',
|
|
'zp_tickets.headline',
|
|
'zp_tickets.editFrom',
|
|
'zp_tickets.editTo',
|
|
'zp_user.firstname',
|
|
'zp_user.lastname',
|
|
])
|
|
->leftJoin('zp_user', function ($join) {
|
|
$join->on('zp_tickets.editorId', '=', $this->connection->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('zp_user.id'), 'text')));
|
|
})
|
|
->where('projectId', $projectId)
|
|
->orderBy('zp_tickets.editFrom')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
/**
|
|
* getProject - get one project
|
|
*/
|
|
public function getProject($id): array|bool
|
|
{
|
|
$userId = session('userdata.id');
|
|
|
|
$result = $this->connection->table('zp_projects')
|
|
->select([
|
|
'zp_projects.id',
|
|
'zp_projects.name',
|
|
'zp_projects.clientId',
|
|
'zp_projects.details',
|
|
'zp_projects.state',
|
|
'zp_projects.hourBudget',
|
|
'zp_projects.dollarBudget',
|
|
'zp_projects.psettings',
|
|
'zp_projects.menuType',
|
|
'zp_projects.avatar',
|
|
'zp_projects.cover',
|
|
'zp_projects.type',
|
|
'zp_projects.parent',
|
|
'zp_projects.modified',
|
|
'zp_clients.name as clientName',
|
|
'zp_projects.start',
|
|
'zp_projects.end',
|
|
])
|
|
->selectRaw('CASE WHEN favorite.id IS NULL THEN false ELSE true END as '.$this->dbHelper->wrapColumn('isFavorite'))
|
|
->leftJoin('zp_clients', 'zp_projects.clientId', '=', 'zp_clients.id')
|
|
->leftJoin('zp_reactions as favorite', function ($join) use ($userId) {
|
|
$join->on('zp_projects.id', '=', 'favorite.moduleId')
|
|
->where('favorite.module', 'project')
|
|
->where('favorite.reaction', 'favorite')
|
|
->where('favorite.userId', $userId);
|
|
})
|
|
->leftJoin('zp_user as requestingUser', function ($join) use ($userId) {
|
|
$join->on('requestingUser.id', '=', $this->connection->raw((int) $userId));
|
|
})
|
|
->where('zp_projects.id', $id)
|
|
->groupBy([
|
|
'zp_projects.id',
|
|
'zp_projects.name',
|
|
'zp_projects.clientId',
|
|
'zp_projects.details',
|
|
'zp_projects.state',
|
|
'zp_projects.hourBudget',
|
|
'zp_projects.dollarBudget',
|
|
'zp_projects.psettings',
|
|
'zp_projects.menuType',
|
|
'zp_projects.avatar',
|
|
'zp_projects.cover',
|
|
'zp_projects.type',
|
|
'zp_projects.parent',
|
|
'zp_projects.modified',
|
|
'zp_clients.name',
|
|
'zp_projects.start',
|
|
'zp_projects.end',
|
|
'favorite.id',
|
|
])
|
|
->limit(1)
|
|
->first();
|
|
|
|
return $result ? (array) $result : false;
|
|
}
|
|
|
|
public function getProjectBookedHours($id): array|bool
|
|
{
|
|
$result = $this->connection->table('zp_tickets')
|
|
->selectRaw($this->dbHelper->wrapColumn('zp_tickets.projectId').', SUM(zp_timesheets.hours) AS '.$this->dbHelper->wrapColumn('totalHours'))
|
|
->join('zp_timesheets', 'zp_timesheets.ticketId', '=', 'zp_tickets.id')
|
|
->where('projectId', $id)
|
|
->first();
|
|
|
|
return $result ? (array) $result : false;
|
|
}
|
|
|
|
public function recursive_array_search($needle, $haystack): false|int|string
|
|
{
|
|
foreach ($haystack as $key => $value) {
|
|
$current_key = $key;
|
|
if ($needle === $value or (is_array($value) && $this->recursive_array_search($needle, $value) !== false)) {
|
|
return $current_key;
|
|
}
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
public function getProjectBookedHoursArray($id): array|bool
|
|
{
|
|
$wrappedWorkDate = $this->dbHelper->wrapColumn('workDate');
|
|
$dateFormatSql = match ($this->dbHelper->getDriverName()) {
|
|
'mysql' => "DATE_FORMAT(zp_timesheets.{$wrappedWorkDate}, '%Y-%m-%d')",
|
|
'pgsql' => "TO_CHAR(zp_timesheets.{$wrappedWorkDate}, 'YYYY-MM-DD')",
|
|
default => "DATE_FORMAT(zp_timesheets.{$wrappedWorkDate}, '%Y-%m-%d')",
|
|
};
|
|
|
|
$results = $this->connection->table('zp_tickets')
|
|
->select([
|
|
'zp_tickets.projectId',
|
|
])
|
|
->selectRaw('SUM(zp_timesheets.hours) AS '.$this->dbHelper->wrapColumn('totalHours'))
|
|
->selectRaw("{$dateFormatSql} AS ".$this->dbHelper->wrapColumn('workDate'))
|
|
->join('zp_timesheets', 'zp_timesheets.ticketId', '=', 'zp_tickets.id')
|
|
->where('projectId', $id)
|
|
->groupByRaw($dateFormatSql)
|
|
->orderBy('workDate')
|
|
->get();
|
|
|
|
$results = array_map(fn ($item) => (array) $item, $results->toArray());
|
|
|
|
$chartArr = [];
|
|
|
|
if (count($results) > 0) {
|
|
$begin = date_create($results[0]['workDate']);
|
|
$begin->sub(new DateInterval('P1D'));
|
|
|
|
$end = date_create($results[(count($results) - 1)]['workDate']);
|
|
$end->add(new DateInterval('P1D'));
|
|
|
|
$i = new DateInterval('P1D');
|
|
|
|
$period = new DatePeriod($begin, $i, $end);
|
|
|
|
$total = 0;
|
|
|
|
foreach ($period as $d) {
|
|
$day = $d->format('Y-m-d');
|
|
$dayKey = $d->getTimestamp();
|
|
|
|
$key = $this->recursive_array_search($day, $results);
|
|
|
|
if ($key === false) {
|
|
$value = 0;
|
|
} else {
|
|
$value = $results[$key]['totalHours'];
|
|
}
|
|
|
|
$total = $total + $value;
|
|
$chartArr[$dayKey] = $total;
|
|
}
|
|
}
|
|
|
|
return $chartArr;
|
|
}
|
|
|
|
public function getProjectBookedDollars($id): mixed
|
|
{
|
|
$result = $this->connection->table('zp_tickets')
|
|
->selectRaw($this->dbHelper->wrapColumn('zp_tickets.projectId').', SUM(zp_timesheets.hours * zp_timesheets.rate) AS '.$this->dbHelper->wrapColumn('totalDollars'))
|
|
->join('zp_timesheets', 'zp_timesheets.ticketId', '=', 'zp_tickets.id')
|
|
->where('projectId', $id)
|
|
->first();
|
|
|
|
return $result ? (array) $result : false;
|
|
}
|
|
|
|
/**
|
|
* addProject - add a project to a client
|
|
*
|
|
* @return int|bool returns new project id on success, false on failure.
|
|
*/
|
|
public function addProject(bool|array $values): int|bool
|
|
{
|
|
$startDate = null;
|
|
if (isset($values['start']) && $values['start'] !== false && $values['start'] != '') {
|
|
$startDate = $values['start'];
|
|
}
|
|
|
|
$endDate = null;
|
|
if (isset($values['end']) && $values['end'] !== false && $values['end'] != '') {
|
|
$endDate = $values['end'];
|
|
}
|
|
|
|
$projectId = $this->connection->table('zp_projects')->insertGetId([
|
|
'name' => $values['name'],
|
|
'details' => $values['details'],
|
|
'clientId' => $values['clientId'],
|
|
'hourBudget' => $values['hourBudget'],
|
|
'dollarBudget' => $values['dollarBudget'],
|
|
'psettings' => $values['psettings'],
|
|
'menuType' => $values['menuType'] ?? '',
|
|
'type' => $values['type'] ?? 'project',
|
|
'parent' => $values['parent'] ?? null,
|
|
'start' => $startDate,
|
|
'end' => $endDate,
|
|
'created' => date('Y-m-d H:i:s'),
|
|
'modified' => date('Y-m-d H:i:s'),
|
|
]);
|
|
|
|
// Add author to project
|
|
if (session()->exists('userdata.id')) {
|
|
$this->addProjectRelation(session('userdata.id'), $projectId, '');
|
|
}
|
|
|
|
// Add users to relation
|
|
if (is_array($values['assignedUsers']) === true && count($values['assignedUsers']) > 0) {
|
|
foreach ($values['assignedUsers'] as $user) {
|
|
if (is_array($user) && isset($user['id']) && isset($user['projectRole'])) {
|
|
$this->addProjectRelation($user['id'], $projectId, $user['projectRole']);
|
|
}
|
|
}
|
|
}
|
|
|
|
return (int) $projectId;
|
|
}
|
|
|
|
/**
|
|
* editProject - edit a project
|
|
*/
|
|
public function editProject(array $values, $id): void
|
|
{
|
|
$oldProject = $this->getProject($id);
|
|
|
|
$startDate = null;
|
|
if (isset($values['start']) && $values['start'] !== false && $values['start'] != '') {
|
|
$startDate = $values['start'];
|
|
}
|
|
|
|
$endDate = null;
|
|
if (isset($values['end']) && $values['end'] !== false && $values['end'] != '') {
|
|
$endDate = $values['end'];
|
|
}
|
|
|
|
$this->connection->table('zp_projects')
|
|
->where('id', $id)
|
|
->update([
|
|
'name' => $values['name'],
|
|
'details' => $values['details'] ?? '',
|
|
'clientId' => $values['clientId'] ?? '',
|
|
'state' => $values['state'] ?? '',
|
|
'hourBudget' => $values['hourBudget'] ?? '',
|
|
'dollarBudget' => $values['dollarBudget'] ?? '',
|
|
'psettings' => $values['psettings'] ?? '',
|
|
'menuType' => $values['menuType'] ?? 'default',
|
|
'type' => $values['type'] ?? 'project',
|
|
'parent' => $values['parent'] ?? null,
|
|
'start' => $startDate,
|
|
'end' => $endDate,
|
|
'modified' => date('Y-m-d H:i:s'),
|
|
]);
|
|
|
|
static::dispatch_event('editProject', ['values' => $values, 'oldProject' => $oldProject]);
|
|
}
|
|
|
|
/**
|
|
* editProject - edit a project
|
|
*/
|
|
public function editProjectRelations(array $values, $projectId): void
|
|
{
|
|
|
|
$this->deleteAllUserRelations($projectId);
|
|
|
|
// Add users to relation
|
|
if (is_array($values['assignedUsers']) === true && count($values['assignedUsers']) > 0) {
|
|
// Roles that may be assigned as a per-project role: every known role key except
|
|
// admin/owner (they're global-only and never scoped to a single project).
|
|
$roles = Roles::getRoles();
|
|
$assignableRoles = array_diff_key($roles, [
|
|
array_search(Roles::$admin, $roles, true) => null,
|
|
array_search(Roles::$owner, $roles, true) => null,
|
|
]);
|
|
|
|
foreach ($values['assignedUsers'] as $userId) {
|
|
$roleValue = (string) ($values['projectRoles']['userProjectRole-'.$userId] ?? '');
|
|
|
|
// Only persist a whitelisted, digit-only role key. "inherit", an empty selection,
|
|
// or any value that isn't a known assignable role leaves the role null, so the user
|
|
// falls back to their global role instead of being stored with an invalid key
|
|
// (which would later resolve to "no role" and lock them out of the project).
|
|
$projectRole = null;
|
|
if (ctype_digit($roleValue) && isset($assignableRoles[(int) $roleValue])) {
|
|
$projectRole = (int) $roleValue;
|
|
}
|
|
|
|
$this->addProjectRelation($userId, $projectId, $projectRole);
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* deleteProject - delete a project
|
|
*/
|
|
public function deleteProject($id): void
|
|
{
|
|
$this->connection->table('zp_projects')
|
|
->where('id', $id)
|
|
->delete();
|
|
|
|
$this->connection->table('zp_tickets')
|
|
->where('projectId', $id)
|
|
->delete();
|
|
}
|
|
|
|
/**
|
|
* hasTickets - check if there are Tickets related to a project
|
|
*/
|
|
public function hasTickets($id): bool
|
|
{
|
|
return $this->connection->table('zp_tickets')
|
|
->where('projectId', $id)
|
|
->where('zp_tickets.type', '<>', 'subtask')
|
|
->where('zp_tickets.type', '<>', 'milestone')
|
|
->exists();
|
|
}
|
|
|
|
/**
|
|
* getUserProjectRelation - get all projects related to a user
|
|
*
|
|
* @param int|null $projectId
|
|
*/
|
|
public function getUserProjectRelation($id, $projectId = null): array
|
|
{
|
|
$query = $this->connection->table('zp_relationuserproject')
|
|
->select([
|
|
'zp_relationuserproject.userId',
|
|
'zp_relationuserproject.projectId',
|
|
'zp_projects.name',
|
|
'zp_projects.modified',
|
|
'zp_relationuserproject.projectRole',
|
|
])
|
|
->join('zp_projects', 'zp_relationuserproject.projectId', '=', 'zp_projects.id')
|
|
->where('userId', $id);
|
|
|
|
if ($projectId != null) {
|
|
$query->where('zp_projects.id', $projectId);
|
|
}
|
|
|
|
$results = $query->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
/**
|
|
* @throws BindingResolutionException
|
|
*/
|
|
/**
|
|
* @throws BindingResolutionException
|
|
*/
|
|
public function isUserAssignedToProject($userId, $projectId): bool
|
|
{
|
|
|
|
$userRepo = app()->make(UserRepository::class);
|
|
$user = $userRepo->getUser($userId);
|
|
|
|
if ($user === false) {
|
|
return false;
|
|
}
|
|
|
|
// admins owners and managers can access everything
|
|
if (in_array(Roles::getRoleString($user['role']), [Roles::$admin, Roles::$owner])) {
|
|
return true;
|
|
}
|
|
|
|
$project = $this->getProject($projectId);
|
|
|
|
if ($project === false) {
|
|
return false;
|
|
}
|
|
|
|
// Everyone in org is allowed to see the project
|
|
if ($project['psettings'] == 'all') {
|
|
return true;
|
|
}
|
|
|
|
// Everyone in client is allowed to see project
|
|
if ($project['psettings'] === 'clients') {
|
|
if ($user['clientId'] == $project['clientId']) {
|
|
return true;
|
|
}
|
|
}
|
|
|
|
// Select users are allowed to see project
|
|
return $this->connection->table('zp_relationuserproject')
|
|
->join('zp_projects', 'zp_relationuserproject.projectId', '=', 'zp_projects.id')
|
|
->where('userId', $userId)
|
|
->where('zp_relationuserproject.projectId', $projectId)
|
|
->exists();
|
|
}
|
|
|
|
/**
|
|
* @throws BindingResolutionException
|
|
*/
|
|
/**
|
|
* @throws BindingResolutionException
|
|
*/
|
|
public function isUserMemberOfProject($userId, $projectId): bool
|
|
{
|
|
$userRepo = app()->make(UserRepository::class);
|
|
$user = $userRepo->getUser($userId);
|
|
|
|
if ($user === false) {
|
|
return false;
|
|
}
|
|
|
|
// admins owners and managers can access everything
|
|
|
|
$project = $this->getProject($projectId);
|
|
|
|
if ($project === false) {
|
|
return false;
|
|
}
|
|
|
|
// Select users are allowed to see project
|
|
return $this->connection->table('zp_relationuserproject')
|
|
->join('zp_projects', 'zp_relationuserproject.projectId', '=', 'zp_projects.id')
|
|
->where('userId', $userId)
|
|
->where('zp_relationuserproject.projectId', $projectId)
|
|
->exists();
|
|
}
|
|
|
|
/**
|
|
* getUserProjectRelation - get all projects related to a user
|
|
*/
|
|
public function editUserProjectRelations($id, $projects): bool
|
|
{
|
|
$results = $this->connection->table('zp_relationuserproject')
|
|
->select(['id', 'userId', 'projectId', 'projectRole'])
|
|
->where('userId', $id)
|
|
->get();
|
|
|
|
$values = array_map(fn ($item) => (array) $item, $results->toArray());
|
|
|
|
// Add relations that don't exist
|
|
foreach ($projects as $project) {
|
|
$exists = false;
|
|
if (count($values)) {
|
|
foreach ($values as $value) {
|
|
if ($project == $value['projectId']) {
|
|
$exists = true;
|
|
}
|
|
}
|
|
}
|
|
if (! $exists) {
|
|
$this->addProjectRelation($id, $project, '');
|
|
}
|
|
}
|
|
|
|
// Delete relations that were removed in select
|
|
if (count($values)) {
|
|
foreach ($values as $value) {
|
|
if (in_array($value['projectId'], $projects) !== true) {
|
|
$this->deleteProjectRelation($id, $value['projectId']);
|
|
}
|
|
}
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
public function deleteProjectRelation($userId, $projectId): void
|
|
{
|
|
$this->connection->table('zp_relationuserproject')
|
|
->where('projectId', $projectId)
|
|
->where('userId', $userId)
|
|
->delete();
|
|
}
|
|
|
|
public function deleteAllProjectRelations($userId): void
|
|
{
|
|
$this->connection->table('zp_relationuserproject')
|
|
->where('userId', $userId)
|
|
->delete();
|
|
}
|
|
|
|
public function deleteAllUserRelations($projectId): void
|
|
{
|
|
$this->connection->table('zp_relationuserproject')
|
|
->where('projectId', $projectId)
|
|
->delete();
|
|
}
|
|
|
|
public function addProjectRelation($userId, $projectId, $projectRole): void
|
|
{
|
|
$oldProject = $this->getProject($projectId);
|
|
|
|
$this->connection->table('zp_relationuserproject')->insert([
|
|
'userId' => $userId,
|
|
'projectId' => $projectId,
|
|
'projectRole' => $projectRole,
|
|
]);
|
|
|
|
static::dispatch_event('userAddedToProject', ['userId' => $userId, 'projectId' => $projectId, 'projectRole' => $projectRole, 'oldProject' => $oldProject]);
|
|
}
|
|
|
|
public function patch($id, $params): bool
|
|
{
|
|
unset($params['act']);
|
|
|
|
$updateData = [];
|
|
foreach ($params as $key => $value) {
|
|
$updateData[DbCore::sanitizeToColumnString($key)] = $value;
|
|
}
|
|
|
|
$updateData['modified'] = date('Y-m-d H:i:s');
|
|
|
|
return $this->connection->table('zp_projects')
|
|
->where('id', $id)
|
|
->update($updateData) >= 0;
|
|
}
|
|
|
|
/**
|
|
* setPicture - set the profile picture for an individual
|
|
*
|
|
* @throws BindingResolutionException
|
|
*/
|
|
public function setPicture($fileId, $id): bool
|
|
{
|
|
return $this->connection->table('zp_projects')
|
|
->where('id', $id)
|
|
->update([
|
|
'avatar' => $fileId,
|
|
'modified' => date('Y-m-d H:i:s'),
|
|
]) >= 0;
|
|
}
|
|
|
|
/**
|
|
* @throws BindingResolutionException
|
|
*/
|
|
public function getProjectAvatar($id): array|false
|
|
{
|
|
if ($id === false) {
|
|
return false;
|
|
}
|
|
|
|
$result = $this->connection->table('zp_projects')
|
|
->select(['avatar', 'name'])
|
|
->where('id', $id)
|
|
->limit(1)
|
|
->first();
|
|
|
|
return $result ? (array) $result : false;
|
|
}
|
|
}
|