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,
    'The dashboard provides a centralized view of your testing efforts, displaying key metrics such as test progress, results, and team productivity. It offers visual insights with charts and reports, enabling efficient tracking of test runs, milestones, and issue trends, ensuring streamlined collaboration and enhanced test management throughout the project lifecycle.' 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(bug_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 
    jira_issues t ;
    select
    '## Open Defects' as description_md,

    'white' as background_color,
    '## '||count(bug_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=To Do' as link
    FROM 
    jira_issues t  where status='To Do';


    select
    '## Closed Defects' as description_md,

    'white' as background_color,
    '## '||count(bug_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=Completed' as link
    FROM 
    jira_issues t where status='Completed';


    select
    '## Rejected Defects' as description_md,

    'white' as background_color,
    '## '||count(bug_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 
    jira_issues t where 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, 
     'Test Suite List' as contents; 
        SELECT 'text' as component;

        
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,
'TAP Test Results' as contents;

SELECT 'text' as component,
'Test Anything Protocol (TAP) results from automated test runs. Click on any TAP file name to view detailed test case information and individual test results.' 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,
'HTML Test Execution Results' as contents;

SELECT 'text' as component,
'Summary statistics for HTML test execution results from automated test runs. View detailed results in the HTML Test Results section.' 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;