qltyfolio/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='qltyfolio/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') || '/qltyfolio/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') || '/qltyfolio/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') || '/qltyfolio/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 
    test_case_run_results 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 
    test_case_run_results r
    ON
    t.test_case_id = r.test_case_id;


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') || '/qltyfolio/chart1.sql?_sqlpage_embed' as embed;
select 
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qltyfolio/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('qltyfolio/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;