qualityfolio/index.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/index.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 'text' as component,
    (SELECT page_content FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 1) as contents;
    select 
    'card' as component,
    4      as columns;

    SELECT
    '## Automation Coverage' AS description_md,
    'white' AS background_color,
    '##  ' || ROUND(100.0 * SUM(CASE WHEN test_type = 'Automation' THEN 1 ELSE 0 END) / COUNT(*), 2) || '%' AS description_md,    
    'orange' AS color,
    'brand-ansible' AS icon
    FROM
    test_cases;

     SELECT
    '## Automated Test Cases' AS description_md,
    'white' AS background_color,
    '##  ' || SUM(CASE WHEN test_type = 'Automation' THEN 1 ELSE 0 END) AS description_md,    
    'green' AS color,
    'brand-ansible' AS icon
    FROM
    test_cases;

    SELECT
    '## Manual Test Cases' AS description_md,
    'white' AS background_color,
    '##  ' || SUM(CASE WHEN test_type = 'Manual' THEN 1 ELSE 0 END) AS description_md,    
    'yellow' AS color,
    'analyze' AS icon
    FROM
    test_cases;
    
    

  select
    '## Total Test Cases Count' as description_md,
 
    'white' as background_color,
    '## '||count(test_case_id) as description_md,
    '12' as width,
     'red' as color,
    'brand-speedtest'       as icon,
     sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test-cases-full-list.sql' as link
    
    FROM test_cases ;

     select
    '## Total Test Suites Count' as description_md,
    'white' as background_color,
    '## '||count(id) as description_md,
    '12' as width,
    'sum'       as icon,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test-suites.sql' as link
    FROM test_suites ; 

  select
    '## Total Test Plans Count' as description_md,
 
    'white' as background_color,
    '## '||count(id) as description_md,
    '12' as width,
     'pink' as color,
    'timeline-event'       as icon,
    'background-color: #FFFFFF' as style,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test-plan.sql' as link
    FROM test_plan ; 

    select
    '## Success Rate' as description_md,

    'white' as background_color,
    '## '||ROUND(100.0 * SUM(CASE WHEN r.status = 'passed' THEN 1 ELSE 0 END) / COUNT(t.test_case_id), 2)||'%' as description_md,
    '12' as width,
     'lime' as color,
    'circle-dashed-check'       as icon,
    'background-color: #FFFFFF' as style
    FROM 
    test_cases t
LEFT JOIN 
    (select * from test_case_run_results group by test_case_id) r
    ON
    t.test_case_id = r.test_case_id;

    


    select
    '## Failed Rate' as description_md,

    'white' as background_color,
    '## '||ROUND(100.0 * SUM(CASE WHEN r.status = 'failed' THEN 1 ELSE 0 END) / COUNT(t.test_case_id), 2)||'%' as description_md,
    '12' as width,
     'red' as color,
    'details-off'       as icon,
    'background-color: #FFFFFF' as style
    FROM 
    test_cases t
LEFT JOIN 
    (select * from test_case_run_results group by test_case_id) r
    ON
    t.test_case_id = r.test_case_id;


 select
    '## Total Defects' as description_md,

    'white' as background_color,
    '## '||count(id) as description_md,
    '12' as width,
    'red' as color,
    'details-off'       as icon,
    'background-color: #FFFFFF' as style,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/bug-list.sql' as link
    FROM 
    bug_report t ;

    select
    '## Open Defects' as description_md,

    'white' as background_color,
    '## '||count(id) as description_md,
    '12' as width,
     'orange' as color,
    'details-off'       as icon,
    'background-color: #FFFFFF' as style,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/bug-list.sql?status=open' as link
    FROM 
    bug_report t  where lower(status)='open';


    select
    '## Closed Defects' as description_md,

    'white' as background_color,
    '## '||count(id) as description_md,
    '12' as width,
     'purple' as color,
    'details-off'       as icon,
    'background-color: #FFFFFF' as style,
     sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/bug-list.sql?status=closed' as link
    FROM 
    bug_report t where lower(status)='closed';


    select
    '## Rejected Defects' as description_md,

    'white' as background_color,
    '## '||count(id) as description_md,
    '12' as width,
     'cyan' as color,
    'details-off'       as icon,
    'background-color: #FFFFFF' as style,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/bug-list.sql?status=rejected' as link
    FROM 
    bug_report t where lower(status)='rejected';


SELECT 'html' as component,
'<style>
.apexcharts-legend-seriesd {
    color: #ffff; /* Red color */
    font-weight: bold; /* Makes the text bold */
}



</style>' 
as html;

select 
    'card' as component,
    2      as columns;
select 
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/chart1.sql?_sqlpage_embed' as embed;
select 
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/chart2.sql?_sqlpage_embed' as embed;
    
    SELECT 'title'AS component, 
     (SELECT page_title FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 2) as contents; 
   SELECT 'text' as component,
    (SELECT page_content FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 2) as contents;
    select 
    'card' as component,
    4      as columns;

        
select 'dynamic' as component, sqlpage.run_sql('qualityfolio/test-suites-common.sql') as properties;

-- select 
--     'chart'             as component,
--     'Test Suites' as title,
--     -- 'area'              as type,
--     -- 'purple'            as color,
--     0         as ymin,
--      5                   as marker,
--     'Success Test Case' as ytitle,
--     'Total Test Case' as xtitle;

-- select 
--     total_test_case as x,
--     success_count    as value,
    
--     suite_name as series
--     FROM test_suite_success_and_failed_rate;

-- TAP Test Results Section
SELECT 'title'AS component, 
     (SELECT page_title FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 3) as contents; 
   SELECT 'text' as component,
    (SELECT page_content FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 3) as contents;
SELECT 'table' as component,
       'Total Tests,Passed,Failed,Pass Rate' as align_right,
       TRUE as sort,
       TRUE as search,
       'File Name' as markdown;

SELECT
    '['||name||']('||sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/tap-details.sql'||'?file='||REPLACE(REPLACE(name, ' ', '%20'), '&', '%26')||')' as "File Name",
    COALESCE(total_planned_tests, total_test_lines, 0) as "Total Tests",
    COALESCE(passed_tests, 0) as "Passed",
    COALESCE(failed_tests, 0) as "Failed",
    CASE
        WHEN total_test_lines > 0
        THEN ROUND((passed_tests * 100.0) / total_test_lines, 1) || '%'
        ELSE '0%'
    END as "Pass Rate",
    strftime('%d-%m-%Y %H:%M', tap_file_created_at) as "Created",
    CASE
        WHEN total_test_lines > 0
        THEN 'rowClass-'||CAST((passed_tests * 100) / total_test_lines AS INTEGER)
        ELSE 'rowClass-0'
    END as _sqlpage_css_class
FROM tap_test_results
ORDER BY tap_file_created_at DESC;

-- HTML Test Execution Results Section
SELECT 'title'AS component,
     (SELECT page_title FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 4) as contents;

SELECT 'text' as component,
    (SELECT page_content FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 4) as contents;

SELECT 'table' as component,
       'Total Tests,Passed,Failed,Pass Rate' as align_right,
       'Test Type' as markdown;

WITH html_stats AS (
    SELECT
        COUNT(*) as total_tests,
        SUM(CASE WHEN execution_status LIKE '%pass%' THEN 1 ELSE 0 END) as passed_tests,
        SUM(CASE WHEN execution_status LIKE '%fail%' OR execution_status LIKE '%error%' THEN 1 ELSE 0 END) as failed_tests
    FROM html_test_execution_results
)
SELECT
    '[HTML Test Executions]('||sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/html-test-results.sql'||')' as "Test Type",
    total_tests as "Total Tests",
    passed_tests as "Passed",
    failed_tests as "Failed",
    CASE
        WHEN total_tests > 0
        THEN ROUND((passed_tests * 100.0) / total_tests, 1) || '%'
        ELSE '0%'
    END as "Pass Rate",
    CASE
        WHEN total_tests > 0
        THEN 'rowClass-'||CAST((passed_tests * 100) / total_tests AS INTEGER)
        ELSE 'rowClass-0'
    END as _sqlpage_css_class
FROM html_stats;


-- Test cycles
SELECT 'title'AS component,
     (SELECT page_title FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 5) as contents;

SELECT 'text' as component,
    (SELECT page_content FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 5) as contents;

-- Add small CSS to color passed/failed cells via row classes (safer than inline HTML in cells)
SELECT 'html' as component,
       '<style>tr.has-pass td:nth-child(5){color:#28a745;font-weight:600} tr.has-fail td:nth-child(6){color:#dc3545;font-weight:600}</style>' as html;

SELECT 'table' as component,
       'Total Tests,Passed,Failed,Pass Rate' as align_right,
       'test cycle' as markdown,
       'test case' as markdown,
       'passed' as markdown,
       'failed' as markdown;
  SELECT 
    '[' || test_cycle || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test_cycle_detail.sql?test_cycle=' || REPLACE(REPLACE(test_cycle, ' ', '%20'), '&', '%26') || ')' AS "test cycle",
    suite_id as "suite",
    suite_name as "suite name",
    -- Make the test case count and passed/failed counts clickable links to the cycle view filtered by status
    '[' || COALESCE(test_case_count, 0) || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test_cycle_case.sql?test_cycle=' || REPLACE(REPLACE(test_cycle, ' ', '%20'), '&', '%26') || ')' AS "test case",
     '[' || COALESCE(passed_count, 0) || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test_cycle_case.sql?test_cycle=' || REPLACE(REPLACE(test_cycle, ' ', '%20'), '&', '%26') || '&status=passed' || ')' AS "passed",
     '[' || COALESCE(failed_count, 0) || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test_cycle_case.sql?test_cycle=' || REPLACE(REPLACE(test_cycle, ' ', '%20'), '&', '%26') || '&status=failed' || ')' AS "failed",
     -- add a CSS class to the TR (SQLPage uses _sqlpage_css_class) so we can color specific TDs
     (CASE WHEN COALESCE(passed_count,0) > 0 THEN 'has-pass ' ELSE '' END || CASE WHEN COALESCE(failed_count,0) > 0 THEN 'has-fail ' ELSE '' END || 'rowClass-' || CAST(COALESCE(passed_count,0) * 100 / CASE WHEN COALESCE(test_case_count,0)=0 THEN 1 ELSE test_case_count END AS INTEGER)) AS _sqlpage_css_class
  FROM test_cycle;
    
    -- Related requirements (dashboard block)
    SELECT 'title'AS component,
     (SELECT page_title FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 6) as contents;

    SELECT 'text' as component,
    (SELECT page_content FROM page_guide WHERE page_identifier = 'qualityfolio/index.sql' AND page_order = 6) as contents;
    SELECT 'html' as component,
      '<style>tr.has-pass td:nth-child(5){color:#28a745;font-weight:600} tr.has-fail td:nth-child(6){color:#dc3545;font-weight:600}</style>' as html;

    SELECT 'table' as component,
           'Total Tests,Passed,Failed,Pass Rate' as align_right,
           'requirement' as markdown,
           'test case' as markdown,
           'passed' as markdown,
           'failed' as markdown;
    SELECT
      '[' || related_requirement || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/related_requirement_detail.sql?requirement=' || REPLACE(REPLACE(related_requirement, ' ', '%20'), '&', '%26') || ')' AS "requirement",
      suite_id as "suite",
      suite_name as "suite name",
      '[' || COALESCE(test_case_count, 0) || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test_case_related_requirements.sql?requirement=' || REPLACE(REPLACE(related_requirement, ' ', '%20'), '&', '%26') || ')' AS "test case",
      '[' || COALESCE(passed_count, 0) || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test_case_related_requirements.sql?requirement=' || REPLACE(REPLACE(related_requirement, ' ', '%20'), '&', '%26') || '&status=passed' || ')' AS "passed",
      '[' || COALESCE(failed_count, 0) || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test_case_related_requirements.sql?requirement=' || REPLACE(REPLACE(related_requirement, ' ', '%20'), '&', '%26') || '&status=failed' || ')' AS "failed",
      (CASE WHEN COALESCE(passed_count,0) > 0 THEN 'has-pass ' ELSE '' END || CASE WHEN COALESCE(failed_count,0) > 0 THEN 'has-fail ' ELSE '' END || 'rowClass-' || CAST(COALESCE(passed_count,0) * 100 / CASE WHEN COALESCE(test_case_count,0)=0 THEN 1 ELSE test_case_count END AS INTEGER)) AS _sqlpage_css_class
    FROM test_case_related_requirements;