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

    SELECT
     'card'     as component,
     '' as title,
      1         as columns;
    SELECT 
     'The Participants Detail page is a comprehensive report that includes glucose statistics, such as the Ambulatory Glucose Profile (AGP), Glycemia Risk Index (GRI), Daily Glucose Profile, and all other metrics data.' as description;
  
     

    SELECT 
        'form'            as component,
        'Filter by Date Range'   as title,
        'Submit' as validate,    
        'Clear'           as reset;
    SELECT 
        'start_date' as name,
        'Start Date' as label,
         strftime('%Y-%m-%d', MIN(Date_Time))  as value, 
        'date'       as type,
        6            as width,
        'mt-1' as class
    FROM     
        combined_cgm_tracing        
    WHERE 
        participant_id = $participant_id;  
    SELECT 
        'end_date' as name,
        'End Date' as label,
         strftime('%Y-%m-%d', MAX(Date_Time))  as value, 
        'date'       as type,
         6             as width,
         'mt-1' as class
    FROM     
        combined_cgm_tracing        
    WHERE 
        participant_id = $participant_id; 



  SELECT
    'datagrid' AS component;
  SELECT
      'MRN: ' || participant_id || '' AS title,
      ' ' AS description
  FROM
      drh_participant
  WHERE participant_id = $participant_id;

  SELECT
      'Study: ' || study_arm || '' AS title,
      ' ' AS description
  FROM
      drh_participant
  WHERE participant_id = $participant_id;

  
  SELECT
      'Age: '|| age || ' Years' AS title,
      ' ' AS description
  FROM
      drh_participant
  WHERE participant_id = $participant_id;

  SELECT
      'hba1c: ' || baseline_hba1c || '' AS title,
      ' ' AS description
  FROM
      drh_participant
  WHERE participant_id = $participant_id;

  SELECT
      'BMI: '|| bmi || '' AS title,
      ' ' AS description
  FROM
      drh_participant
  WHERE participant_id = $participant_id;

  SELECT
      'Diabetes Type: '|| diabetes_type || ''  AS title,
      ' ' AS description
  FROM
      drh_participant
  WHERE participant_id = $participant_id;

  SELECT
      strftime('Generated: %Y-%m-%d %H:%M:%S', 'now') AS title,
      ' ' AS description
 
  SELECT    
    'html' as component,
      '<input type="hidden" name="participant_id" class="participant_id" value="'|| $participant_id ||'">' as html;      
      
    SELECT 
    'card' as component,    
    2      as columns;
SELECT 
    '' AS title,
    'white' As background_color,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/drh/glucose-statistics-and-targets/index.sql?_sqlpage_embed&participant_id=' || $participant_id ||
    '&start_date=' || COALESCE($start_date, participant_cgm_dates.cgm_start_date) ||
    '&end_date=' || COALESCE($end_date, participant_cgm_dates.cgm_end_date) AS embed
FROM 
    (SELECT participant_id, 
            MIN(Date_Time) AS cgm_start_date, 
            MAX(Date_Time) AS cgm_end_date
     FROM combined_cgm_tracing
     GROUP BY participant_id) AS participant_cgm_dates
WHERE 
    participant_cgm_dates.participant_id = $participant_id;  

         
SELECT 
    '' as title,
    'white' As background_color,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/drh/goals-for-type-1-and-type-2-diabetes/index.sql?_sqlpage_embed&participant_id=' || $participant_id ||
    '&start_date=' || COALESCE($start_date, participant_cgm_dates.cgm_start_date) ||
    '&end_date=' || COALESCE($end_date, participant_cgm_dates.cgm_end_date) AS embed
FROM 
    (SELECT participant_id, 
            MIN(Date_Time) AS cgm_start_date, 
            MAX(Date_Time) AS cgm_end_date
     FROM combined_cgm_tracing
     GROUP BY participant_id) AS participant_cgm_dates
WHERE 
    participant_cgm_dates.participant_id = $participant_id;  

SELECT 
    '' as title,
    'white' As background_color,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/drh/ambulatory-glucose-profile/index.sql?_sqlpage_embed&participant_id=' || $participant_id as embed;  
SELECT 
    '' as title,
    'white' As background_color,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/drh/daily-gluecose-profile/index.sql?_sqlpage_embed&participant_id=' || $participant_id as embed;  
SELECT 
    '' as title,
    'white' As background_color,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/drh/glycemic_risk_indicator/index.sql?_sqlpage_embed&participant_id=' || $participant_id as embed;  
  SELECT 
    '' as title,
    'white' As background_color,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/drh/advanced_metrics/index.sql?_sqlpage_embed&participant_id=' || $participant_id  || 
    '&start_date=' || COALESCE($start_date, participant_cgm_dates.cgm_start_date) ||
    '&end_date=' || COALESCE($end_date, participant_cgm_dates.cgm_end_date) AS embed 
    FROM 
        (SELECT participant_id, 
                MIN(Date_Time) AS cgm_start_date, 
                MAX(Date_Time) AS cgm_end_date
        FROM combined_cgm_tracing
        GROUP BY participant_id) AS participant_cgm_dates
    WHERE 
        participant_cgm_dates.participant_id = $participant_id;