453 lines
16 KiB
PHP
453 lines
16 KiB
PHP
<?php
|
|
|
|
namespace Leantime\Domain\Ideas\Repositories;
|
|
|
|
use Illuminate\Database\ConnectionInterface;
|
|
use Leantime\Core\Db\DatabaseHelper;
|
|
use Leantime\Core\Db\Db as DbCore;
|
|
use Leantime\Core\Language as LanguageCore;
|
|
|
|
class Ideas
|
|
{
|
|
public ?object $result = null;
|
|
|
|
public ?object $tickets = null;
|
|
|
|
private ConnectionInterface $db;
|
|
|
|
public array $canvasTypes = [
|
|
'idea' => 'status.ideation',
|
|
'research' => 'status.discovery',
|
|
'prototype' => 'status.delivering',
|
|
'validation' => 'status.inreview',
|
|
'implemented' => 'status.accepted',
|
|
'deferred' => 'status.deferred',
|
|
];
|
|
|
|
public array $statusClasses = ['idea' => 'label-info', 'validation' => 'label-warning', 'prototype' => 'label-warning', 'research' => 'label-warning', 'implemented' => 'label-success', 'deferred' => 'label-default'];
|
|
|
|
private LanguageCore $language;
|
|
|
|
private DatabaseHelper $dbHelper;
|
|
|
|
/**
|
|
* __construct - get db connection
|
|
*
|
|
* @return void
|
|
*/
|
|
public function __construct(DbCore $db, LanguageCore $language, DatabaseHelper $dbHelper)
|
|
{
|
|
$this->db = $db->getConnection();
|
|
$this->language = $language;
|
|
$this->dbHelper = $dbHelper;
|
|
}
|
|
|
|
public function getSingleCanvas(int $canvasId): false|array
|
|
{
|
|
$results = $this->db->table('zp_canvas')
|
|
->select(
|
|
'zp_canvas.id',
|
|
'zp_canvas.title',
|
|
'zp_canvas.author',
|
|
'zp_canvas.created',
|
|
'zp_canvas.projectId',
|
|
't1.firstname AS authorFirstname',
|
|
't1.lastname AS authorLastname'
|
|
)
|
|
->leftJoin('zp_user AS t1', 'zp_canvas.author', '=', 't1.id')
|
|
->where('type', 'idea')
|
|
->where('zp_canvas.id', $canvasId)
|
|
->orderBy('zp_canvas.title')
|
|
->orderBy('zp_canvas.created')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
/**
|
|
* @return array|mixed
|
|
*/
|
|
public function getCanvasLabels(): mixed
|
|
{
|
|
if (session()->exists('projectsettings.idealabels')) {
|
|
return session('projectsettings.idealabels');
|
|
} else {
|
|
$result = $this->db->table('zp_settings')
|
|
->select('value')
|
|
->where('key', 'projectsettings.'.session('currentProject').'.idealabels')
|
|
->limit(1)
|
|
->first();
|
|
|
|
$labels = [];
|
|
|
|
// preseed state labels with default values
|
|
foreach ($this->canvasTypes as $key => $label) {
|
|
$labels[$key] = [
|
|
'name' => $this->language->__($label),
|
|
'class' => $this->statusClasses[$key],
|
|
];
|
|
}
|
|
|
|
if ($result !== null) {
|
|
foreach (safe_unserialize($result->value, []) as $key => $label) {
|
|
// Ignore keys we have no status class for. Idea labels are keyed by the
|
|
// canvasTypes strings, but a bug in the rename dialog used to persist an
|
|
// integer 0 key, and reading it back threw "Undefined array key 0" on every
|
|
// render — permanently 500ing the board with no way to undo it from the UI
|
|
// (#3685). Skipping unknown keys lets an already-broken board heal itself.
|
|
if (! isset($this->statusClasses[$key])) {
|
|
continue;
|
|
}
|
|
|
|
$labels[$key] = [
|
|
'name' => $label,
|
|
'class' => $this->statusClasses[$key],
|
|
];
|
|
}
|
|
}
|
|
|
|
session(['projectsettings.idealabels' => $labels]);
|
|
|
|
return $labels;
|
|
}
|
|
}
|
|
|
|
public function getAllCanvas(int $projectId): false|array
|
|
{
|
|
$results = $this->db->table('zp_canvas')
|
|
->select(
|
|
'zp_canvas.id',
|
|
'zp_canvas.title',
|
|
'zp_canvas.author',
|
|
'zp_canvas.created',
|
|
't1.firstname AS authorFirstname',
|
|
't1.lastname AS authorLastname'
|
|
)
|
|
->leftJoin('zp_user AS t1', 'zp_canvas.author', '=', 't1.id')
|
|
->where('type', 'idea')
|
|
->where('projectId', $projectId)
|
|
->orderBy('zp_canvas.title')
|
|
->orderBy('zp_canvas.created')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
public function deleteCanvas(int $id): void
|
|
{
|
|
// Shared zp_canvas/zp_canvas_items: early-return unless this id is an idea board, so the
|
|
// items-delete (by canvasId) can't drop another canvas family's items before the
|
|
// type-guarded board delete runs.
|
|
$isIdea = $this->db->table('zp_canvas')
|
|
->where('id', $id)
|
|
->where('type', 'idea')
|
|
->exists();
|
|
|
|
if (! $isIdea) {
|
|
return;
|
|
}
|
|
|
|
$this->db->table('zp_canvas_items')
|
|
->where('canvasId', $id)
|
|
->delete();
|
|
|
|
$this->db->table('zp_canvas')
|
|
->where('id', $id)
|
|
->where('type', 'idea')
|
|
->delete();
|
|
}
|
|
|
|
public function addCanvas(array $values): false|string
|
|
{
|
|
$id = $this->db->table('zp_canvas')->insertGetId([
|
|
'title' => $values['title'],
|
|
'author' => $values['author'],
|
|
'created' => now(),
|
|
'type' => 'idea',
|
|
'projectId' => $values['projectId'],
|
|
]);
|
|
|
|
return (string) $id;
|
|
}
|
|
|
|
public function updateCanvas(array $values): mixed
|
|
{
|
|
// type guard (shared zp_canvas across all canvas families): only ever rename an idea board.
|
|
return $this->db->table('zp_canvas')
|
|
->where('id', $values['id'])
|
|
->where('type', 'idea')
|
|
->update(['title' => $values['title']]);
|
|
}
|
|
|
|
public function editCanvasItem(array $values): void
|
|
{
|
|
$this->db->table('zp_canvas_items')
|
|
->where('id', $values['itemId'])
|
|
->update([
|
|
'description' => $values['description'],
|
|
'assumptions' => $values['assumptions'],
|
|
'data' => $values['data'],
|
|
'conclusion' => $values['conclusion'],
|
|
'modified' => now(),
|
|
'status' => $values['status'],
|
|
'milestoneId' => $values['milestoneId'],
|
|
'tags' => $values['tags'],
|
|
]);
|
|
}
|
|
|
|
public function patchCanvasItem(int $id, array $params): bool
|
|
{
|
|
if (isset($params['act'])) {
|
|
unset($params['act']);
|
|
}
|
|
|
|
$updateData = [];
|
|
foreach ($params as $key => $value) {
|
|
$sanitizedKey = DbCore::sanitizeToColumnString($key);
|
|
$updateData[$sanitizedKey] = $value;
|
|
}
|
|
|
|
return $this->db->table('zp_canvas_items')
|
|
->where('id', $id)
|
|
->update($updateData) >= 0;
|
|
}
|
|
|
|
public function updateIdeaSorting(array $sortingArray): bool
|
|
{
|
|
foreach ($sortingArray as $idea) {
|
|
$this->db->table('zp_canvas_items')
|
|
->updateOrInsert(
|
|
['id' => (int) $idea['id']],
|
|
['sortindex' => (int) $idea['sortIndex']]
|
|
);
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
public function getCanvasItemsById(int $id): false|array
|
|
{
|
|
$results = $this->db->table('zp_canvas_items')
|
|
->select(
|
|
'zp_canvas_items.id',
|
|
'zp_canvas_items.description',
|
|
'zp_canvas_items.assumptions',
|
|
'zp_canvas_items.data',
|
|
'zp_canvas_items.conclusion',
|
|
'zp_canvas_items.box',
|
|
'zp_canvas_items.author',
|
|
'zp_canvas_items.created',
|
|
'zp_canvas_items.modified',
|
|
'zp_canvas_items.canvasId',
|
|
'zp_canvas_items.sortindex',
|
|
'zp_canvas_items.milestoneId',
|
|
't1.firstname AS authorFirstname',
|
|
't1.lastname AS authorLastname',
|
|
't1.profileId AS authorProfileId',
|
|
'milestone.headline as milestoneHeadline',
|
|
'milestone.editTo as milestoneEditTo'
|
|
)
|
|
->selectRaw("CASE WHEN zp_canvas_items.status IS NULL THEN 'idea' ELSE zp_canvas_items.status END as status")
|
|
->selectRaw('COUNT(DISTINCT zp_comment.id) AS '.$this->dbHelper->wrapColumn('commentCount'))
|
|
->leftJoin('zp_user AS t1', 'zp_canvas_items.author', '=', 't1.id')
|
|
->leftJoin('zp_tickets AS milestone', function ($join) {
|
|
$join->on('zp_canvas_items.milestoneId', '=', $this->db->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('milestone.id'), 'text')));
|
|
})
|
|
->leftJoin('zp_comment', function ($join) {
|
|
$join->on('zp_canvas_items.id', '=', 'zp_comment.moduleId')
|
|
->where('zp_comment.module', '=', 'idea');
|
|
})
|
|
->where('zp_canvas_items.canvasId', $id)
|
|
->groupBy(
|
|
'zp_canvas_items.id',
|
|
't1.firstname',
|
|
't1.lastname',
|
|
't1.profileId',
|
|
'milestone.headline',
|
|
'milestone.editTo',
|
|
'zp_canvas_items.status'
|
|
)
|
|
->orderBy('zp_canvas_items.sortindex')
|
|
->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
|
|
public function getSingleCanvasItem(int $id): mixed
|
|
{
|
|
$result = $this->db->table('zp_canvas_items')
|
|
->select(
|
|
'zp_canvas_items.id',
|
|
'zp_canvas_items.description',
|
|
'zp_canvas_items.assumptions',
|
|
'zp_canvas_items.data',
|
|
'zp_canvas_items.conclusion',
|
|
'zp_canvas_items.box',
|
|
'zp_canvas_items.author',
|
|
'zp_canvas_items.created',
|
|
'zp_canvas_items.modified',
|
|
'zp_canvas_items.canvasId',
|
|
'zp_canvas_items.sortindex',
|
|
'zp_canvas_items.status',
|
|
'zp_canvas_items.tags',
|
|
'zp_canvas_items.milestoneId',
|
|
't1.firstname AS authorFirstname',
|
|
't1.lastname AS authorLastname',
|
|
'milestone.headline as milestoneHeadline',
|
|
'milestone.editTo as milestoneEditTo'
|
|
)
|
|
->leftJoin('zp_tickets AS milestone', function ($join) {
|
|
$join->on('zp_canvas_items.milestoneId', '=', $this->db->raw($this->dbHelper->castAs($this->dbHelper->wrapColumn('milestone.id'), 'text')));
|
|
})
|
|
->leftJoin('zp_user AS t1', 'zp_canvas_items.author', '=', 't1.id')
|
|
->where('zp_canvas_items.id', $id)
|
|
->first();
|
|
|
|
return $result ? (array) $result : false;
|
|
}
|
|
|
|
public function addCanvasItem(array $values): false|string
|
|
{
|
|
$id = $this->db->table('zp_canvas_items')->insertGetId([
|
|
'description' => $values['description'],
|
|
'assumptions' => $values['assumptions'] ?? '',
|
|
'data' => $values['data'] ?? '',
|
|
'conclusion' => $values['conclusion'] ?? '',
|
|
'box' => $values['box'] ?? 'idea',
|
|
'author' => $values['author'] ?? session('userdata.id'),
|
|
'created' => now(),
|
|
'modified' => now(),
|
|
'canvasId' => $values['canvasId'],
|
|
'status' => $values['status'] ?? '',
|
|
'milestoneId' => $values['milestoneId'] ?? '',
|
|
]);
|
|
|
|
return (string) $id;
|
|
}
|
|
|
|
public function delCanvasItem(int $id): void
|
|
{
|
|
$this->db->table('zp_canvas_items')
|
|
->where('id', $id)
|
|
->delete();
|
|
}
|
|
|
|
public function updateIdeaStatus(int $ideaId, string $status): bool
|
|
{
|
|
return $this->db->table('zp_canvas_items')
|
|
->where('id', $ideaId)
|
|
->update(['box' => $status]) >= 0;
|
|
}
|
|
|
|
/**
|
|
* @return int|mixed
|
|
*/
|
|
public function getNumberOfIdeas(?int $projectId = null): mixed
|
|
{
|
|
$query = $this->db->table('zp_canvas_items')
|
|
->leftJoin('zp_canvas AS canvasBoard', 'zp_canvas_items.canvasId', '=', 'canvasBoard.id')
|
|
->where('canvasBoard.type', 'idea');
|
|
|
|
if ($projectId !== null) {
|
|
$query->where('canvasBoard.projectId', $projectId);
|
|
}
|
|
|
|
return $query->count('zp_canvas_items.id');
|
|
}
|
|
|
|
/**
|
|
* @return int|mixed
|
|
*/
|
|
public function getNumberOfBoards(?int $projectId = null): mixed
|
|
{
|
|
$query = $this->db->table('zp_canvas')
|
|
->where('type', 'idea');
|
|
|
|
if ($projectId !== null) {
|
|
$query->where('projectId', $projectId);
|
|
}
|
|
|
|
return $query->count();
|
|
}
|
|
|
|
public function bulkUpdateIdeaStatus(array $params): bool
|
|
{
|
|
// Jquery sortable serializes the array for kanban in format
|
|
// statusKey: item[]=X&item[]=X2...,
|
|
// statusKey2: item[]=X&item[]=X2...,
|
|
// This represents status & kanban sorting
|
|
foreach ($params as $status => $ideaList) {
|
|
$ideas = explode('&', $ideaList);
|
|
|
|
foreach ($ideas as $key => $ideaString) {
|
|
if (strlen($ideaString) > 0) {
|
|
$id = substr($ideaString, 7);
|
|
|
|
if ($this->updateIdeaStatus((int) $id, $status) === false) {
|
|
return false;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
public function getAllIdeas(?int $projectId, ?int $boardId): array|false
|
|
{
|
|
$userId = session('userdata.id') ?? -1;
|
|
$clientId = session('userdata.clientId') ?? -1;
|
|
$requesterRole = session()->exists('userdata') ? session('userdata.role') : -1;
|
|
|
|
$query = $this->db->table('zp_canvas_items')
|
|
->select(
|
|
'zp_canvas_items.id',
|
|
'zp_canvas_items.description',
|
|
'zp_canvas_items.assumptions',
|
|
'zp_canvas_items.data',
|
|
'zp_canvas_items.conclusion',
|
|
'zp_canvas_items.box',
|
|
'zp_canvas_items.author',
|
|
'zp_canvas_items.created',
|
|
'zp_canvas_items.modified',
|
|
'zp_canvas_items.canvasId',
|
|
'zp_canvas_items.sortindex',
|
|
'zp_canvas_items.status',
|
|
'zp_canvas_items.tags',
|
|
'zp_canvas_items.milestoneId',
|
|
'zp_canvas.projectId'
|
|
)
|
|
->leftJoin('zp_canvas', 'zp_canvas_items.canvasId', '=', 'zp_canvas.id')
|
|
->leftJoin('zp_projects', 'zp_canvas.projectId', '=', 'zp_projects.id')
|
|
->where('zp_canvas_items.box', 'idea')
|
|
->where(function ($q) use ($userId, $clientId, $requesterRole) {
|
|
$q->whereIn('zp_canvas.projectId', function ($subquery) use ($userId) {
|
|
$subquery->select('projectId')
|
|
->from('zp_relationuserproject')
|
|
->where('userId', $userId);
|
|
})
|
|
->orWhere('zp_projects.psettings', 'all')
|
|
->orWhere(function ($q2) use ($clientId) {
|
|
$q2->where('zp_projects.psettings', 'clients')
|
|
->where('zp_projects.clientId', $clientId);
|
|
});
|
|
// Admin and manager roles have access to all projects
|
|
if (in_array($requesterRole, ['admin', 'manager'])) {
|
|
$q->orWhereRaw('1=1');
|
|
}
|
|
});
|
|
|
|
if (isset($projectId) && $projectId > 0) {
|
|
$query->where('zp_canvas.projectId', $projectId);
|
|
}
|
|
|
|
if (isset($boardId) && $boardId > 0) {
|
|
$query->where('zp_canvas.id', $boardId);
|
|
}
|
|
|
|
$results = $query->get();
|
|
|
|
return array_map(fn ($item) => (array) $item, $results->toArray());
|
|
}
|
|
}
|