drh/study-participant-dashboard/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='drh/study-participant-dashboard/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 'title' AS component, (SELECT COALESCE(title, caption)
    FROM sqlpage_aide_navigation
   WHERE namespace = 'prime' AND path = 'drh/study-participant-dashboard/index.sql/index.sql') as contents;
    ;


SELECT
'datagrid' AS component; 

SELECT
    'Study Name' AS title,
    '' || study_name || '' AS description
FROM
    drh_study_vanity_metrics_details;

SELECT
    'Start Date' AS title,
    '' || start_date || '' AS description
FROM
    drh_study_vanity_metrics_details;

SELECT
    'End Date' AS title,
    '' || end_date || '' AS description
FROM
    drh_study_vanity_metrics_details;

SELECT
    'NCT Number' AS title,
    '' || nct_number || '' AS description
FROM
    drh_study_vanity_metrics_details;




SELECT
   'card'     as component,
   '' as title,
    4         as columns;

SELECT
   'Total Number Of Participants' AS title,
   '' || total_number_of_participants || '' AS description
FROM
    drh_study_vanity_metrics_details;

SELECT

    'Total CGM Files' AS title,
   '' || number_of_cgm_raw_files || '' AS description
FROM
  drh_number_cgm_count;



SELECT
   '% Female' AS title,
   '' || percentage_of_females || '' AS description
FROM
    drh_study_vanity_metrics_details;


SELECT
   'Average Age' AS title,
   '' || average_age || '' AS description
FROM
    drh_study_vanity_metrics_details;




SELECT
'datagrid' AS component;


SELECT
    'Study Description' AS title,
    '' || study_description || '' AS description
FROM
    drh_study_vanity_metrics_details;

    SELECT
    'Study Team' AS title,
    '' || investigators || '' AS description
FROM
    drh_study_vanity_metrics_details;


    SELECT
   'card'     as component,
   '' as title,
    1         as columns;

    SELECT
    'Device Wise Raw CGM File Count' AS title,
    GROUP_CONCAT(' ' || devicename || ': ' || number_of_files || '') AS description
    FROM
        drh_device_file_count_view;

        SELECT
'text' as component,
'# Participant Dashboard' as contents_md;

    SET total_rows = (SELECT COUNT(*) FROM participant_dashboard_cached );
SET limit = COALESCE($limit, 50);
SET offset = COALESCE($offset, 0);
SET total_pages = ($total_rows + $limit - 1) / $limit;
SET current_page = ($offset / $limit) + 1;

  -- Display uniform_resource table with pagination
  SELECT 'table' AS component,
        'participant_id' as markdown,
        TRUE AS sort,
        TRUE AS search;        
  SELECT tenant_id,format('[%s]('||sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/drh/participant-info/index.sql?participant_id='||'%s)',participant_id, participant_id) as participant_id,gender,age,study_arm,baseline_hba1c,cgm_devices,cgm_files,tir,tar_vh,tar_h,tbr_l,tbr_vl,tar,tbr,gmi,percent_gv,gri,days_of_wear,data_start_date,data_end_date FROM participant_dashboard_cached
  LIMIT $limit
  OFFSET $offset;

  SELECT 'text' AS component,
    (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) ||     ')' ELSE '' END) || ' ' ||
    '(Page ' || $current_page || ' of ' || $total_pages || ") " ||
    (SELECT CASE WHEN $current_page < $total_pages THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) ||     ')' ELSE '' END)
    AS contents_md;