qualityfolio/test-cases.sql

              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-cases.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-cases.sql/index.sql') as contents;
    ;

-- Page description based on context
SELECT 'text' AS component,
  CASE
    WHEN $id IS NOT NULL THEN
      'This page displays test cases for the selected group: ' || (SELECT group_name FROM test_cases WHERE group_id = $id LIMIT 1) || '. Use the search and filter functionality to find specific test cases.'
    ELSE
      'This page displays a comprehensive list of all test cases across all projects and test suites. Use the search and filter functionality to find specific test cases by name, status, group, or priority.'
  END AS contents;

-- Status overview based on context
SELECT 'alert' AS component,
       'info' AS color,
       CASE
         WHEN $id IS NOT NULL THEN 'Group Test Case Overview'
         ELSE 'Test Case Overview'
       END AS title,
       'Total test cases: ' ||
       CASE
         WHEN $id IS NOT NULL THEN (SELECT COUNT(*) FROM test_cases WHERE group_id = $id)
         ELSE (SELECT COUNT(*) FROM test_cases)
       END ||
       ' | Passed: ' ||
       CASE
         WHEN $id IS NOT NULL THEN (SELECT COUNT(*) FROM test_cases WHERE group_id = $id AND test_status = 'passed')
         ELSE (SELECT COUNT(*) FROM test_cases WHERE test_status = 'passed')
       END ||
       ' | Failed: ' ||
       CASE
         WHEN $id IS NOT NULL THEN (SELECT COUNT(*) FROM test_cases WHERE group_id = $id AND test_status = 'failed')
         ELSE (SELECT COUNT(*) FROM test_cases WHERE test_status = 'failed')
       END ||
       ' | Pending: ' ||
       CASE
         WHEN $id IS NOT NULL THEN (SELECT COUNT(*) FROM test_cases WHERE group_id = $id AND (test_status IS NULL OR test_status = 'TODO'))
         ELSE (SELECT COUNT(*) FROM test_cases WHERE test_status IS NULL OR test_status = 'TODO')
       END 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 $id IS NOT NULL
UNION ALL
-- Show "Export Group Test Cases" button when filtering by group
SELECT 'Export Group Test Cases' as title,
       'download-test-case.sql?group_id=' || $id as link,
       'download' as icon
WHERE $id 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 $id IS NULL;

SET total_rows = (SELECT COUNT(*) FROM test_cases WHERE ($id IS NULL OR group_id = $id));
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 ($id IS NULL OR tc.group_id = $id)
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('&id=' || replace($id, ' ', '%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('&id=' || replace($id, ' ', '%20'), '') || ')' ELSE '' END)
    AS contents_md
;
        ;