Confluence likes release notes

Hi every one

In my company we use confluence and one of the usefull feature is to generate a list of all features for a particular version. We use this features to generate a document named “delivery notes”.

So I develop a mysql request that give me the same result (generate a MD table)

I don’t known how to develop a kanboard plugin but if one of you could do this it will be interesting to use my request with the parameters. You only have to provide 4 variables.

see below the script

SELECT @project_id := id from projects where name = “test”;
SELECT @version_min := position from swimlanes where name = “Version 0”;
SELECT @version_max := position from swimlanes where name = “Version 2”;
SET @server_name := “localhost”;

SET SESSION group_concat_max_len = 18446744073709551615;

SELECT ‘|Id|Priority|Summary|Sprint|Category|links|Status|’;
SELECT ‘|–|--------|-------|------|--------|-----|------|’;

SELECT CONCAT(’|[’, task_id, ‘](’, url, ‘)|’, task_priority, ‘|’, summary, ‘|’, sprint, ‘|’, category, ‘|’, links, ‘|’, status,’|’ ) FROM (

SELECT
t.id as ‘task_id’,
CONCAT(‘http://’, @server_name, ‘/project/’, p.id, ‘/task/’, t.id) as url,
CASE t.priority
WHEN 0 THEN ‘Must have’
WHEN 1 THEN ‘Could have’
WHEN 2 THEN ‘Should have’
ELSE ‘Error’
END ‘task_priority’,
t.title as summary,
s.name as sprint,
phc.name as category,
IFNULL(GROUP_CONCAT(’[’, l.label, ’ ', thl.opposite_task_id, ‘](’, CONCAT(‘http://’, @server_name, ‘/project/’, p.id, ‘/task/’, thl.opposite_task_id, ‘)’) separator ', '), ‘’) as ‘links’,
c.title as status

FROM tasks t

       JOIN swimlanes s ON t.swimlane_id = s.id
       JOIN projects p ON t.project_id = p.id
       JOIN project_has_categories phc ON t.category_id = phc.id 
       JOIN columns c ON t.column_id = c.id 
       LEFT JOIN task_has_links thl ON thl.task_id  = t.id
       LEFT JOIN links l ON thl.link_id = l.id

WHERE
s.position BETWEEN @version_min and @version_max AND
s.is_active = TRUE AND
p.id = @project_id

GROUP BY t.id, t.priority, url, summary, sprint, category
ORDER BY sprint, t.priority

) result;

and the result is

Id Priority Summary Sprint Category links Status
1 Must have Production software architecture Sprint 1 - 31/01/2020 Epic is a parent of 2 Backlog