SELECT 'dynamic' AS component, sqlpage.run_sql('shell/shell.sql') AS properties;
SELECT 'breadcrumb' as component;
WITH RECURSIVE breadcrumbs AS (
SELECT
COALESCE(abbreviated_caption, caption) AS title,
COALESCE(url, path) AS link,
parent_path, 0 AS level,
namespace
FROM sqlpage_aide_navigation
WHERE namespace = 'prime' AND path='qualityfolio/test_cycle_case.sql'
UNION ALL
SELECT
COALESCE(nav.abbreviated_caption, nav.caption) AS title,
COALESCE(nav.url, nav.path) AS link,
nav.parent_path, b.level + 1, nav.namespace
FROM sqlpage_aide_navigation nav
INNER JOIN breadcrumbs b ON nav.namespace = b.namespace AND nav.path = b.parent_path
)
SELECT title ,
sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/'||link as link
FROM breadcrumbs ORDER BY level DESC;
-- not including page title from sqlpage_aide_navigation
SELECT 'title' AS component, (SELECT COALESCE(title, caption)
FROM sqlpage_aide_navigation
WHERE namespace = 'prime' AND path = 'qualityfolio/test_cycle_case.sql/index.sql') as contents;
;
-- Page description based on context
SELECT 'title'AS component,
(SELECT page_title FROM page_guide WHERE page_identifier = 'qualityfolio/test_cycle_case.sql' AND page_order = 1) as contents;
SELECT 'text' as component,
(SELECT page_content FROM page_guide WHERE page_identifier = 'qualityfolio/test_cycle_case.sql' AND page_order = 1) as contents;
-- Status overview based on context
SELECT
'alert' AS component,
'info' AS color,
'Test Case Overview' AS title,
'Total test cases: ' || (
SELECT COUNT(*)
FROM test_cases
WHERE $test_cycle IS NULL
OR EXISTS (
SELECT 1
FROM json_each(test_cycles)
WHERE value = $test_cycle
)
) ||
' | Passed: ' || (
SELECT COUNT(*)
FROM test_cases
WHERE test_status = 'passed'
AND ($test_cycle IS NULL
OR EXISTS (
SELECT 1
FROM json_each(test_cycles)
WHERE value = $test_cycle
))
) ||
' | Failed: ' || (
SELECT COUNT(*)
FROM test_cases
WHERE test_status = 'failed'
AND ($test_cycle IS NULL
OR EXISTS (
SELECT 1
FROM json_each(test_cycles)
WHERE value = $test_cycle
))
) ||
' | Pending: ' || (
SELECT COUNT(*)
FROM test_cases
WHERE (test_status IS NULL OR test_status = 'TODO')
AND ($test_cycle IS NULL
OR EXISTS (
SELECT 1
FROM json_each(test_cycles)
WHERE value = $test_cycle
))
) AS description;
SELECT 'html' as component,
'<style>
tr td.test_status {
color: blue !important;
}
tr.rowClass-passed td.test_status {
color: green !important;
}
tr.rowClass-failed td.test_status {
color: red !important;
}
tr.rowClass-TODO td.test_status {
color: orange !important;
}
.btn-list {
display: flex;
justify-content: flex-end;
}
</style>' as html;
SELECT 'button' as component;
-- Show "View All Test Cases" button when filtering by group
SELECT 'View All Test Cases' as title,
'test-cases.sql' as link,
'list' as icon
WHERE $test_cycle IS NOT NULL
UNION ALL
-- Show "Export Test Cycle Test Cases" button when filtering by group
SELECT 'Export Test Cycle Test Cases' as title,
-- Include status query param only when $status is provided
'download-test-cycle-test-case.sql?test_cycle=' || REPLACE(REPLACE($test_cycle, ' ', '%20'), '&', '%26') ||
CASE WHEN $status IS NOT NULL THEN '&status=' || REPLACE(REPLACE($status, ' ', '%20'), '&', '%26') ELSE '' END as link,
'download' as icon
WHERE $test_cycle IS NOT NULL
UNION ALL
-- Show "Export All Test Cases" button when showing all test cases
SELECT 'Export All Test Cases' as title,
'download-full_list.sql' as link,
'download' as icon
WHERE $test_cycle IS NULL;
SET total_rows = (SELECT COUNT(*) FROM test_cases WHERE (
$test_cycle IS NULL
OR EXISTS (
SELECT 1
FROM json_each(test_cycles)
WHERE value = $test_cycle
)
)
AND (
$status IS NULL
OR LOWER($status) = 'passed' AND LOWER(COALESCE(test_status, '')) = 'passed'
OR LOWER($status) = 'failed' AND LOWER(COALESCE(test_status, '')) = 'failed'
));
SET limit = COALESCE($limit, 50);
SET offset = COALESCE($offset, 0);
SET total_pages = ($total_rows + $limit - 1) / $limit;
SET current_page = ($offset / $limit) + 1;
SELECT 'table' as component,
TRUE AS sort,
TRUE AS search,
'Test Case ID' as markdown,
'Title' as markdown,
'Group' as markdown,
'Suite' as markdown,
'Status' as markdown;
SELECT
'[' || tc.test_case_id || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test-detail.sql?tab=actual-result&id='|| tc.test_case_id || ')' as "Test Case ID",
tc.test_case_title AS "Title",
'[' || tc.group_name || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/group-detail.sql?id='|| tc.group_id || ')' AS "Group",
'[' || ts.name || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/suite-data.sql?id='|| ts.id || ')' AS "Suite",
CASE
WHEN tc.test_status IS NOT NULL THEN tc.test_status
ELSE 'TODO'
END AS "Status",
'rowClass-' || COALESCE(tc.test_status, 'TODO') as _sqlpage_css_class,
tc.test_type AS "Type",
tc.priority AS "Priority",
tc.created_by AS "Created By",
tc.formatted_test_case_created_at AS "Created On"
FROM test_cases tc
LEFT JOIN test_suites ts ON ts.id = tc.suite_id
WHERE (
$test_cycle IS NULL
OR EXISTS (
SELECT 1
FROM json_each(tc.test_cycles)
WHERE value = $test_cycle
)
)
AND (
$status IS NULL
OR LOWER($status) = 'passed' AND LOWER(COALESCE(tc.test_status, '')) = 'passed'
OR LOWER($status) = 'failed' AND LOWER(COALESCE(tc.test_status, '')) = 'failed'
)
ORDER BY tc.test_case_id
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || COALESCE('&test_cycle=' || replace($test_cycle, ' ', '%20'), '') || COALESCE('&status=' || replace($status, ' ', '%20'), '') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || COALESCE('&test_cycle=' || replace($test_cycle, ' ', '%20'), '') || COALESCE('&status=' || replace($status, ' ', '%20'), '') || ')' ELSE '' END)
AS contents_md
;
;