fleetfolio/aws_cost_report.sql

              SELECT 'dynamic' AS component, sqlpage.run_sql('shell/shell.sql') AS properties;
              -- not including breadcrumbs from sqlpage_aide_navigation
              -- 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 = 'fleetfolio/aws_cost_report.sql/index.sql') as contents;
    ;
    --- Display breadcrumb
 SELECT
    'breadcrumb' AS component;
  SELECT
    'Home' AS title,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/'AS link;
  SELECT
    'Fleetfolio' AS title,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/fleetfolio/index.sql' AS link;  
  SELECT
    'Boundary' AS title,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/fleetfolio/boundary.sql' AS link; 

  SELECT
    'AWS Trust Boundary' AS title,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/fleetfolio/aws_trust_boundary_list.sql' AS link; 

  SELECT
    'AWS Cost Summary' AS title,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/fleetfolio/aws_cost_detail_list.sql' AS link; 

  SELECT
    $service AS title,
    sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/fleetfolio/aws_cost_report.sql?service=' || $service  AS link; 

  --- Dsply Page Title
  SELECT
      'title'   as component,
      $service contents;

     select
      'text'              as component,
      'View a consolidated summary of your AWS spending, broken down by account and month. Monitor trends, compare costs, and gain insights to optimize your cloud expenses.' as contents;

  SELECT 'tab' AS component, TRUE AS center;
  SELECT 'Daily Cost' AS title, '?tab=daily_cost&service=' || $service AS link, ($tab = 'daily_cost' OR $tab IS NULL) AS active;
  select 'Monthly Cost' as title, '?tab=monthly_coste&service=' || $service AS link, $tab = 'monthly_coste' as active;


SELECT 'table' AS component,
        TRUE as sort,
        TRUE as search;
-- AWS daily service cost list
SET total_rows = (SELECT COUNT(*) FROM list_aws_daily_service_cost WHERE service=$service);
SET limit = COALESCE($limit, 50);
SET offset = COALESCE($offset, 0);
SET total_pages = ($total_rows + $limit - 1) / $limit;
SET current_page = ($offset / $limit) + 1; 
 
    SELECT 
    datetime(substr(period_start, 1, 19)) as "period start",
    datetime(substr(period_end, 1, 19)) AS "period end",
    service,
    region,
    amortized_cost_amount AS "amortized cost amount", 
    usage_quantity_amount AS "usage quantity amount"
    FROM list_aws_daily_service_cost WHERE service=$service AND ($tab = 'daily_cost' OR $tab IS NULL) ORDER BY period_start DESC;
     SELECT 'text' AS component,
    (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) ||  '&tab=' || replace($tab, ' ', '%20') ||
'&service=' || replace($service, ' ', '%20') ||   ')' ELSE '' END) || ' ' ||
    '(Page ' || $current_page || ' of ' || $total_pages || ") " ||
    (SELECT CASE WHEN $current_page < $total_pages THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) ||   '&tab=' || replace($tab, ' ', '%20') ||
'&service=' || replace($service, ' ', '%20') ||  ')' ELSE '' END)
    AS contents_md 
 WHERE $tab='daily_cost';;

-- AWS monthly service cost list    
SET total_rows = (SELECT COUNT(*) FROM list_aws_monthly_service_cost WHERE service=$service);
SET limit = COALESCE($limit, 50);
SET offset = COALESCE($offset, 0);
SET total_pages = ($total_rows + $limit - 1) / $limit;
SET current_page = ($offset / $limit) + 1; 
 
    SELECT 
    datetime(substr(period_start, 1, 19)) as "period start",
    datetime(substr(period_end, 1, 19)) AS "period end",
    service,
    region,
    amortized_cost_amount AS "amortized cost amount", 
    usage_quantity_amount AS "usage quantity amount"
    FROM list_aws_monthly_service_cost WHERE service=$service AND $tab = 'monthly_coste' ORDER BY period_start DESC;
     SELECT 'text' AS component,
    (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) ||  '&tab=' || replace($tab, ' ', '%20') ||
'&service=' || replace($service, ' ', '%20') ||   ')' ELSE '' END) || ' ' ||
    '(Page ' || $current_page || ' of ' || $total_pages || ") " ||
    (SELECT CASE WHEN $current_page < $total_pages THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) ||   '&tab=' || replace($tab, ' ', '%20') ||
'&service=' || replace($service, ' ', '%20') ||  ')' ELSE '' END)
    AS contents_md 
 WHERE $tab='monthly_coste';