training-report.html

<!-- Check out the documentation at https://github.com/KaiVandivier/ascend-mda-report -->

<!-- Bootstrap stylesheet and scripts -->
<link
  rel="stylesheet"
  href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"
  integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z"
  crossorigin="anonymous"
/>
<script
  src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"
  integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN"
  crossorigin="anonymous"
></script>
<script
  src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"
  integrity="sha384-B4gt1jrGC7Jh4AgTPSdUtOBvfO8shuf57BaghqFfPlYxofvL8/KUEfYiJOMMV+rV"
  crossorigin="anonymous"
></script>

<!-- Font awesome icons -->
<script
  src="https://kit.fontawesome.com/8483276227.js"
  crossorigin="anonymous"
></script>

<style type="text/css" media="screen">
  /* Put your CSS styling here */

  /* Vars */
  :root {
    --colShade: rgba(0, 0, 0, 0.02);
    --pink: hsl(348, 100%, 97.5%);
    --yellow: hsl(60, 100%, 97.5%);
    --green: hsl(132, 100%, 97.5%);
    --blue: hsl(204, 100%, 97.5%);
    --lilac: hsl(276, 100%, 97.5%);
  }

  /* Button styles */
  .btn.disabled,
  .btn:disabled {
    pointer-events: none;
  }

  /* Table styles */
  table {
    border-collapse: separate;
    border-spacing: 0;
  }

  /* Constrain column size */
  thead th:first-child {
    width: 60%;
  }

  /* Table header color & stripe */
  thead th {
    background: white;
  }
  thead th:nth-child(even) {
    background: #f7f7f7;
  }

  /* Table header styles */
  #header-row > th {
    position: sticky;
    top: 0;
  }

  th.container {
    width: auto;
  }

  .bottom-row {
    font-weight: normal;
    font-size: 0.9rem;
    height: calc(0.9rem * 1.5);
  }

  /**
   * Table body color & stripe:
   * THIS IS HARD-CODED AND NEEDS CHANGING IF COLUMNS CHANGE
   */
  tbody tr *:nth-child(even) {
    background-color: var(--colShade);
  }

  /* Row headers that are not category labels: normal font-weight */
  th[scope="row"]:not([colspan]) {
    font-weight: normal;
  }

  /* Colors (used for rows) */
  .pink {
    background: var(--pink);
  }
  .yellow {
    background: var(--yellow);
  }
  .green {
    background: var(--green);
  }
  .blue {
    background: var(--blue);
  }
  .lilac {
    background: var(--lilac);
  }
</style>

<script type="text/javascript">
  // Put your javascript functions here

  // Enums for table
  const colSubcats = Object.freeze({
    ROUND_1: { name: "Round 1", shortName: "R1" },
    ROUND_2: { name: "Round 2", shortName: "R2" },
    TOTAL: { name: "Total", shortName: "Tot." },
  });
  const rowTypes = Object.freeze({
    CATEGORY: "category",
    DATA: "data",
  });
  const rowColors = Object.freeze({
    ENDEMICITY: "yellow",
    TARGETS: "blue",
    COVERAGE: "pink",
    COVERAGE_EVALUATION: "green",
    LNOB: "lilac",
  });
  const tooltipTypes = Object.freeze({
    INFO: { name: "info", icon: "fa-info-circle", color: "text-secondary" },
    ERROR: {
      name: "error",
      icon: "fa-exclamation-circle",
      color: "text-danger",
    },
    WARNING: {
      name: "warning",
      icon: "fa-exclamation-triangle",
      color: "text-warning",
    },
  });

  /**
   * Row and column definitions: Edit these to define the table.
   * See the documentation at https://github.com/KaiVandivier/ascend-mda-report
   * for a guide.
   */
  const columns = [
    {
      name: "Female",
      shortName: "Female",
      subcategories: null,
    },
    {
      name: "Male",
      shortName: "Male",
      subcategories: null,
    },
    {
      name: "Total",
      shortName: "Total",
      subcategories: null,
    },
  ];

  const rows = [
    {
      name: "People trained to support morbidity mapping for LF",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - People trained to support morbidity mapping for LF_Female",
        },
        {
          dn:
            "Training - People trained to support morbidity mapping for LF_Male",
        },
        {
          dn:
            "Training - People trained to support morbidity mapping for LF_Total",
        },
      ],
    },
    {
      name: "People trained to support morbidity mapping for Trachoma",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - People trained to support morbidity mapping for Trachoma_Female",
        },
        {
          dn:
            "Training - People trained to support morbidity mapping for Trachoma_Male",
        },
        {
          dn:
            "Training - People trained to support morbidity mapping for Trachoma_Total",
        },
      ],
    },
    {
      name: "Staff trained on transmission assessment and data collection",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - Staff trained on transmission assessment and data collection_Female",
        },
        {
          dn:
            "Training - Staff trained on transmission assessment and data collection_Male",
        },
        {
          dn:
            "Training - Staff trained on transmission assessment and data collection_Total",
        },
      ],
    },
    {
      name: "People trained in budgeting",
      type: rowTypes.DATA,
      cells: [
        { dn: "Training - People trained in budgeting_Female" },
        { dn: "Training - People trained in budgeting_Male" },
        { dn: "Training - People trained in budgeting_Total" },
      ],
    },
    {
      name: "Total people trained (disaggregated to roles and geographic area)",
      type: rowTypes.DATA,
      cells: [null, null, null],
    },
    {
      name:
        "Health care providers providing morbidity management who received raining on morbidity management (LF: Lymphoedema, Hydrocele; Trachoma: Trichiasis)",
      type: rowTypes.DATA,
      cells: [null, null, null],
    },
    {
      name: "People trained on data management systems",
      type: rowTypes.DATA,
      cells: [
        { dn: "Training - People trained on data management systems_Female" },
        { dn: "Training - People trained on data management systems_Male" },
        { dn: "Training - People trained on data management systems_Total" },
      ],
    },
    {
      name: "Surveillance trainings conducted",
      type: rowTypes.DATA,
      cells: [
        null,
        null,
        { dn: "Training - Surveillance trainings conducted" },
      ],
    },
    {
      name: "People trained on supply chain management systems",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - People trained on supply chain management systems_Female",
        },
        {
          dn:
            "Training - People trained on supply chain management systems_Male",
        },
        {
          dn:
            "Training - People trained on supply chain management systems_Total",
        },
      ],
    },
    {
      name: "NTD trainings including IEC/BCC components",
      type: rowTypes.DATA,
      cells: [
        null,
        null,
        { dn: "Training - NTD trainings including IEC/BCC components_Total" },
      ],
    },
    {
      name:
        "Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: CDDs",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: CDDs_Female",
        },
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: CDDs_Male",
        },
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: CDDs_Total",
        },
      ],
    },
    {
      name:
        "Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: FLHW",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: FLHW_Female",
        },
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: FLHW_Male",
        },
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: FLHW_Total",
        },
      ],
    },
    {
      name:
        "Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: Supervisor/Trainers",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: Supervisor/Trainers_Female",
        },
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: Supervisor/Trainers_Male",
        },
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: Supervisor/Trainers_Total",
        },
      ],
    },
    {
      name:
        "Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: Teachers",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: Teachers_Female",
        },
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: Teachers_Male",
        },
        {
          dn:
            "Training - Health care providers and community drug distributors trained and working on MDA planning, social mobilization, implementation and monitoring: Teachers_Total",
        },
      ],
    },
    {
      name:
        "VL treatment facilities which are functional with adequate trained human resources, diagnostic equipment and supplies and medicines",
      type: rowTypes.DATA,
      cells: [null, null, null],
    },
    {
      name: "VL treatment facilities receiving training",
      type: rowTypes.DATA,
      cells: [
        null,
        null,
        { dn: "Training - VL treatment facilities receiving training" },
      ],
    },
    {
      name: "People trained in mmdp care: Lymphoedema management",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - People trained in mmdp care: Lymphoedema management_Female",
        },
        {
          dn:
            "Training - People trained in mmdp care: Lymphoedema management_Male",
        },
        {
          dn:
            "Training - People trained in mmdp care: Lymphoedema management_Total",
        },
      ],
    },
    {
      name: "People trained in mmdp care: Hydrocelectomy",
      type: rowTypes.DATA,
      cells: [
        { dn: "Training - People trained in mmdp care: Hydrocelectomy_Female" },
        { dn: "Training - People trained in mmdp care: Hydrocelectomy_Male" },
        { dn: "Training - People trained in mmdp care: Hydrocelectomy_Total" },
      ],
    },
    {
      name: "People trained in mmdp care: TT surgery",
      type: rowTypes.DATA,
      cells: [
        { dn: "Training - People trained in mmdp care: TT surgery_Female" },
        { dn: "Training - People trained in mmdp care: TT surgery_Male" },
        { dn: "Training - People trained in mmdp care: TT surgery_Total" },
      ],
    },
    {
      name: "People trained in mmdp care: VL treatment",
      type: rowTypes.DATA,
      cells: [
        { dn: "Training - People trained in mmdp care: VL treatment_Female" },
        { dn: "Training - People trained in mmdp care: VL treatment_Male" },
        { dn: "Training - People trained in mmdp care: VL treatment_Total" },
      ],
    },
    {
      name: "People trained in active case finding for VL",
      type: rowTypes.DATA,
      cells: [
        {
          dn: "Training - People trained in active case finding for VL_Female",
        },
        { dn: "Training - People trained in active case finding for VL_Male" },
        { dn: "Training - People trained in active case finding for VL_Total" },
      ],
    },
    {
      name: "People trained in active case finding for trachoma",
      type: rowTypes.DATA,
      cells: [
        {
          dn:
            "Training - People trained in active case finding for trachoma_Female",
        },
        {
          dn:
            "Training - People trained in active case finding for trachoma_Male",
        },
        {
          dn:
            "Training - People trained in active case finding for trachoma_Total",
        },
      ],
    },
    {
      name: "People trained in active case finding for LF",
      type: rowTypes.DATA,
      cells: [
        {
          dn: "Training - People trained in active case finding for LF_Female",
        },
        { dn: "Training - People trained in active case finding for LF_Male" },
        { dn: "Training - People trained in active case finding for LF_Total" },
      ],
    },
    {
      name: "Total people trained: all training types",
      type: rowTypes.DATA,
      cells: [
        { dn: "Training - People trained: all training types_Female" },
        { dn: "Training - People trained: all training types_Male" },
        { dn: "Training - People trained: all training types_Total" },
      ],
    },
    {
      name: "Total people trained: by training type",
      type: rowTypes.DATA,
      cells: [null, { value: "todo" }, null],
    },
  ];
  // End row & cell definitions

  /**
   *
   * App state
   *
   */

  // (Ideally, initial values would be set by the reports app parameters)
  // Initial period should get overwritten by `setUpPeriodSelect()`;
  const currentYear = new Date().getFullYear();
  const initialPeriod = currentYear;
  const initialOrgUnit = Object.keys(dhis2.report.organisationUnit).length
    ? dhis2.report.organisationUnit.id
    : "GdH306UQ8L8"; // This is the global org-unit

  // Number format to be used in cells
  const numberFormat = new Intl.NumberFormat(undefined, {
    maximumFractionDigits: 2,
  });
  // Cell-width of table
  const totalColumns =
    1 +
    columns.reduce(
      (sum, column) => sum + (column.subcategories?.length || 1),
      0
    );

  let state = {
    currentYear: currentYear,
    period: initialPeriod,
    orgUnit: initialOrgUnit,
    numberFormat,
    totalColumns,
    // Dim. IDs, found by dimension-name lookup:
    dimensionsToQuery: [],
    // Warnings and errors to be displayed in tooltips:
    alerts: [],
  };

  /**
   *
   * Custom logic helper functions
   *
   */
  function greatestOf(...args) {
    const nums = args.filter((n) => !isNaN(n));
    if (!nums.length) return 0;
    return Math.max(...nums);
  }

  function sumOf(...args) {
    const nums = args.filter((n) => !isNaN(n));
    if (!nums.length) return 0;
    return nums.reduce((sum, n) => sum + Number(n), 0);
  }

  function uniqueRespondingIUs(cells) {
    // This function counts all unique IUs with at least one value in this row
    // 1. Build dimensions list for query url
    const dimensions = cells
      .filter((cell) => cell && cell.dxId)
      .map((cell) => cell.dxId)
      .join(";");

    // 2. Query analytics:
    return $.get("../api/34/analytics", {
      // Second orgunit dimension is hardcoded to IU level
      dimension: `dx:${dimensions},ou:${state.orgUnit};LEVEL-iGO9aN3ZSSi`,
      filter: `pe:${state.period}`,
      skipMeta: true,
    })
      .then((json) => {
        // 3. Get unique org units (IUs) with response > 0
        const uniqueOrgUnits = new Map();
        json.rows.forEach(([dimensionId, orgUnitId, value]) => {
          if (!value) return;

          // If this org unit is a duplicate in the list, set value to be the greater
          const existingValue = uniqueOrgUnits.get(orgUnitId);
          if (existingValue) {
            const greaterValue = greatestOf(value, existingValue);
            uniqueOrgUnits.set(orgUnitId, greaterValue);
            return;
          }

          // Otherwise, set value
          uniqueOrgUnits.set(orgUnitId, value);
        });

        // Sum response values of unique IUs
        let sumOfValues = 0;
        uniqueOrgUnits.forEach((value) => {
          if (isNaN(value)) return;
          sumOfValues += Number(value);
        });
        const numUniqueIUs = uniqueOrgUnits.size;

        return { numUniqueIUs, sumOfValues };
      })
      .catch((err) => {
        // Format error for later handling
        return Promise.reject({ ...err, message: "Unique IUs error" });
      });
  }

  /**
   *
   * Table logic
   *
   */
  function getAllDimensions() {
    // Gets a list of all indicators' and program indicators' names and IDs
    const filter = "Training";

    // TODO: Refactor for easier manipulation
    return Promise.all([
      $.get(
        `../api/34/programIndicators.json?filter=displayName:like:${filter}&paging=false`
      ),
      $.get(
        `../api/34/indicators.json?filter=displayName:like:${filter}&paging=false`
      ),
    ]).then((values) => {
      const [{ programIndicators }, { indicators }] = values;
      const allDimensions = [...programIndicators, ...indicators];

      const dimensionMap = new Map();
      allDimensions.forEach((dimension) => {
        // Check for duplicated dimensions
        const dimensionWithSameName = dimensionMap.get(dimension.displayName);
        if (dimensionWithSameName) {
          const alert = {
            identifier: "dimension-name",
            identifierValue: dimension.displayName,
            tooltipType: tooltipTypes.WARNING,
            message: `Duplicate dimension name found: '${dimension.displayName}'. You may not get the data you're looking for if this cell is queried by name.`,
          };
          state.alerts.push(alert);

          console.warn(
            "Duplicate dimension name found: ",
            dimension.displayName
          );
        }

        // As of right now, overwrite to give last-queried group precedence (i.e., indicators)
        dimensionMap.set(dimension.displayName, dimension.id);
      });
      return dimensionMap;
    });
  }

  function populateCellDimensionIds(allDimensions) {
    // Populates dimensionId (`dxId`) on cells based on dimensionName
    // Also populates `state.dimensionsToQuery` for analytics query
    const dimensionsToQuery = [];

    // Look up dimension ids by name and add ids to cells
    rows.forEach(({ cells }) => {
      if (!cells) return;

      cells.forEach((cell) => {
        if (!cell) return;
        if (cell.dxId) {
          // cell already has `dimensionId`; skip ID lookup
          dimensionsToQuery.push(cell.dxId);
          return;
        }
        if (!cell.dn) return;
        const { dn: dimensionName } = cell;

        // Find dimension in map:
        const dimensionId = allDimensions.get(dimensionName);

        if (!dimensionId) {
          if (!cell.tooltips) cell.tooltips = [];
          cell.tooltips.push({
            type: tooltipTypes.ERROR,
            message: `Dimension not found: ${dimensionName}`,
          });
          return;
        }

        // Dimension found - add it to list to query and populate cell
        dimensionsToQuery.push(dimensionId);
        cell.dxId = dimensionId;
      });
    });

    state = { ...state, dimensionsToQuery };
    return dimensionsToQuery;
  }

  function getAnalyticsData() {
    // Query analytics API for data (with one monolithic query)
    const { dimensionsToQuery, period, orgUnit } = state;

    return $.get("../api/34/analytics", {
      dimension: `dx:${dimensionsToQuery.join(";")}`,
      filter: `ou:${orgUnit},pe:${period}`,
      skipMeta: true,
    })
      .then((json) => {
        // convert to a map for easy lookup by ID:
        const keyValuePairs = json.rows.map((row) => [row[0], row[1]]);
        const resultsMap = new Map(keyValuePairs);
        return resultsMap;
      })
      .catch((err) => {
        // There was an error with the monolothic query. Fall back to individual queries

        // Log error and notify user
        console.warn(
          "There was an error with the monolithic query. Falling back to individual queries.",
          err
        );
        const errMsgExists = $("#errMsg").length;
        if (!errMsgExists) {
          $("#loading").prepend(`
            <div id="errMsg">
              <p>Oops!  There was an error with the database query.  This report will fall back to a less efficient method to identify the error, which will take a bit longer to load than usual.</p>
              <p class="text-warning"><i class="fa fa-exclamation-triangle"></i> Warning: Please be aware that refreshing the table multiple times while the error exists can crash the server, due to a large volume of network requests.</p>
            </div>
          `);
        }

        // Query dimensions individually to sort out errors
        return getAnalyticsDataIndividually();
      });
  }

  function getAnalyticsDataIndividually() {
    // Query each cell individually to sort out errors
    // (This is much slower and should only be used if there is a db error in the monolothic query)
    const { dimensionsToQuery, period, orgUnit } = state;

    return Promise.all(
      dimensionsToQuery.map((dimensionId) => {
        // Query individual cell contents
        return $.get("../api/34/analytics", {
          dimension: `dx:${dimensionId},pe:${period}`,
          filter: `ou:${orgUnit}`,
          skipMeta: true,
        }).then(
          // If successful, return key-value pair of data
          (json) => {
            if (!json.rows.length) return;
            return [dimensionId, json.rows[0][2]];
          },
          // If unsuccessful, log error and make key-value pair of id and error message
          (err) => {
            console.error(
              `Analytics query error found for dimension ID ${dimensionId}: `,
              err
            );
            const { status, statusText, responseJSON } = err;
            const errorMessage = `Query error, status ${status}: ${
              responseJSON ? responseJSON.message : statusText
            }`;
            state.alerts.push({
              identifier: "dimension-id",
              identifierValue: dimensionId,
              tooltipType: tooltipTypes.ERROR,
              message: errorMessage,
            });
          }
        );
      })
    ).then((kvPairs) => {
      // Turn key-value pairs into a map for lookup during table population
      const filteredPairs = kvPairs.filter((pair) => pair);
      const resultsMap = new Map(filteredPairs);
      return resultsMap;
    });
  }

  function populateCellValues(analyticsResults) {
    // Use `dimensionId` or `customLogic` to populate `value`
    // Execute in parallel across rows
    return Promise.all(
      rows.map(async ({ cells }, rowIdx) => {
        if (!cells) return; // Empty row; return

        // Execute (possibly async) cell logic in _series_
        return cells
          .reduce((prevTask, cell, idx, arr) => {
            return prevTask.then(async () => {
              if (!cell) return;
              const { dxId: dimensionId, customLogic } = cell;

              // Handle cell defined by dimension ID
              if (dimensionId) {
                const data = analyticsResults.get(dimensionId);
                cell.value = data ?? "-";
                return;
              }

              // Handle cell defined by custom logic
              if (customLogic) {
                try {
                  cell.value = await customLogic(arr, idx);
                } catch (err) {
                  // If error in custom logic, create alert
                  const message = `
                      ${err.message || "Custom logic error"}: 
                      ${err.status || ""} ${err.statusText || ""}.
                      See console.
                    `;
                  state.alerts.push({
                    identifier: "custom-logic",
                    identifierValue: `row-${rowIdx}-idx-${idx}`,
                    tooltipType: tooltipTypes.ERROR,
                    message,
                  });
                  console.error(message, err);
                }
                return;
              }
            });
          }, Promise.resolve())
          .catch(console.error.bind(console, "Cell population error: "));
      })
    );
  }

  /**
   *
   * HTML Table building
   *
   */
  function populateHtmlTableHeader() {
    // Empty cell over row names (Maybe "indicator?")
    $("#header-row").append(`<th scope="col"></th>`);
    columns.forEach((col) => {
      const colspan = col.subcategories ? col.subcategories.length : 1;
      const subheadings = col.subcategories
        ? col.subcategories.map((sc) => sc.shortName)
        : null;

      const newHeader = $(`
        <th scope="col" class="container" colspan="${colspan}">
          <div class="row mb-2">
            <span class="col">${subheadings ? col.name : col.shortName}</span>
          </div>
        </th>
      `);
      $("#header-row").append(newHeader);

      const bottomRow = $(`<div class="row no-gutters bottom-row"></div>`);
      newHeader.append(bottomRow);

      if (subheadings) {
        subheadings.forEach((subheading) =>
          bottomRow.append(`<span class="col">${subheading}</span>`)
        );
        return;
      }
    });
  }

  function populateHtmlTableBodyWithValues() {
    // Clear tbody for fresh rows & remove "loading"
    const tbody = $("#tbody").empty();
    $("#loading").hide();

    // Set up table body
    rows.forEach((row, rowIdx) => {
      // Make new row element
      const newRow = $(
        `<tr
          data-name="${row.name}"
          data-type="${row.type}"
          class="${row.color || ""}"
        ></tr>`
      );
      // Add new row to DOM
      tbody.append(newRow);

      if (row.type === rowTypes.CATEGORY) {
        // Row is just a category label (like "Coverage Evaluation"): make one wide cell and return
        newRow.append(
          `<th scope="row" colspan="${state.totalColumns}">${row.name}</th>`
        );
        return;
      }

      // Add row header with name
      newRow.append(`<th scope="row">${row.name}</th>`);
      addCellsToHtmlRow(newRow, row, rowIdx);
    });
  }

  function addCellsToHtmlRow(htmlRow, row, rowIdx) {
    const { cells } = row;
    if (!cells || !cells.length) {
      htmlRow.append(
        `<td colspan="${state.totalColumns - 1}">No dimensions specified</td>`
      );
      return;
    }

    cells.forEach((cell, idx) => {
      if (!cell) return htmlRow.append("<td></td>");

      const { dn, dxId, customLogic, value } = cell;
      const newCell = $(`
        <td
          ${dn ? `data-dimension-name="${dn}"` : ""}
          ${dxId ? `data-dimension-id="${dxId}"` : ""}
          ${customLogic ? `data-custom-logic="row-${rowIdx}-idx-${idx}"` : ""}
        ></td>
      `);

      // Add value and format number
      if (typeof value !== "undefined") {
        const formattedValue = isNaN(value)
          ? value
          : state.numberFormat.format(Number(value));
        newCell.append(formattedValue);
      }

      // Add tooltips, if defined
      if (cell.tooltips)
        cell.tooltips.forEach((tooltip) => addTooltipToCell(newCell, tooltip));
      htmlRow.append(newCell);
    });
  }

  function addTooltipToCell(htmlCell, { type, message }) {
    // Check if cell is valid:
    if (!htmlCell.length)
      return console.warn("Invalid html cell when adding tooltip");

    const htmlTooltip = $(
      `<i class="fa ${type.icon} ${type.color}" data-toggle="tooltip" data-title="${message}"></i>`
    );
    htmlCell.append(" ", htmlTooltip);
    htmlTooltip.tooltip();
  }

  function addAlertTooltips() {
    state.alerts.forEach((alert) => {
      const { identifier, identifierValue, tooltipType, message } = alert;
      const targetCell = $(`[data-${identifier}="${identifierValue}"`);
      if (!targetCell.length) return;
      addTooltipToCell(targetCell, { type: tooltipType, message });
    });
  }

  /**
   *
   * Load the Table, populate it with data
   *
   */
  function loadTableData() {
    // Prepare for new table data & show loading
    $("#tbody").empty();
    $("#loading").show();
    $("#downloadCSV").attr("disabled", true);
    $("#downloadCSV").addClass("disabled");

    // Query new data and populate table
    // TODO: update period and org unit display
    return getAnalyticsData()
      .then(populateCellValues)
      .then(populateHtmlTableBodyWithValues)
      .then(addAlertTooltips)
      .then(() => {
        $("#downloadCSV").attr("disabled", false);
        $("#downloadCSV").removeClass("disabled");
      });
  }

  /**
   *
   * CSV Export
   *
   */
  function exportToCSV() {
    const csvPrefix = "data:text/csv;charset=utf-8,";

    // 0. Include Org unit and period
    const {
      period,
      allYears,
      orgUnit,
      allOrgUnits,
      orgUnitPrettyString,
      periodPrettyString,
    } = state;
    const csvOrgUnit = `Organisation Unit(s):,${
      orgUnitPrettyString || allOrgUnits.get(orgUnit)
    }\n`;
    const csvPeriod = `Period(s):,${
      period === allYears ? "All Years" : period
    }\n\n`;

    // 1. Make two header rows for column title
    const colHeadersTop = [null];
    const colHeadersBot = [null];
    const columnHeaders = columns.forEach((column) => {
      // TODO: Short names?
      // If no subcategories, enter column name on top and null on bottom.
      if (!column.subcategories) {
        colHeadersTop.push(column.name);
        colHeadersBot.push(null);
        return;
      }

      // If column has subcategories, enter col name and n-1 spaces on top,
      // and all subcategories in bottom.
      column.subcategories.forEach((subcategory, idx) => {
        colHeadersTop.push(idx === 0 ? column.name : null);
        colHeadersBot.push(subcategory.name);
      });
    });
    const csvHeaders =
      colHeadersTop.join(",") + "\n" + colHeadersBot.join(",") + "\n";

    // 2. Map each row; join with newlines
    // 3. Map each cell; join with columns
    const csvRows = rows
      .map((row) => {
        const formattedName = row.name.replace(/,/g, "");
        if (!row.cells) return formattedName;

        const csvCells = row.cells
          .map((cell) => {
            // If cell is empty or has no value, make empty cell element
            if (!cell || typeof cell.value === "undefined") return null;
            const { value } = cell;
            const formattedValue = isNaN(value)
              ? value.replace(/,/g, "")
              : Number(value)
                  .toFixed(2)
                  .replace(/\.?0*$/, "");
            return formattedValue;
          })
          .join(",");
        return `${formattedName},${csvCells}`;
      })
      .join("\n");

    // 4. Combine all strings
    const csvContent =
      csvPrefix + csvOrgUnit + csvPeriod + csvHeaders + csvRows;

    // 5. Encode URI; make invisible link then download
    const encodedUri = encodeURI(csvContent);
    const link = document.createElement("a");
    link.setAttribute("href", encodedUri);
    link.setAttribute("download", "training_report.csv");
    window.frameElement.parentNode.appendChild(link); // Bypasses chrome sandboxed iframe restrictions

    link.click(); // This will download the data file named "training_report.csv".
  }

  /**
   *
   * Period & Org Unit selection
   *
   */
  function setUpPeriodCheckboxes(startingYear) {
    // Populate period checkboxes, including "all years"
    const currentYear = state.currentYear;

    const allYears = [];
    for (let year = currentYear; year >= startingYear; year--) {
      allYears.push(year);
    }
    const allYearsString = allYears.join(";");
    state = { ...state, allYears: allYearsString };

    const checkboxes = $("#periodCheckboxes");
    allYears.forEach((year) => {
      checkboxes.append(`
        <div class="form-check">
          <input
            class="form-check-input"
            type="checkbox"
            value="${year}"
            name="period"
            id="${year}"
            ${year == currentYear ? "checked" : ""}
          />
          <label class="form-check-label" for="${year}">
            ${year}
          </label>
        </div>
      `);
    });

    checkboxes.append(`
      <div class="form-check">
        <input
          class="form-check-input"
          type="checkbox"
          value="${allYearsString}"
          name="period"
          id="${allYearsString}"
        />
        <label class="form-check-label" for="${allYearsString}">
          All Years
        </label>
      </div>
    `);
  }

  function setUpOrgUnitCheckboxes(maxLevel) {
    $.get("../api/34/organisationUnits.json", { maxLevel, paging: false })
      .then(({ organisationUnits: orgUnits }) => {
        if (!orgUnits) return;

        const orgUnitCheckboxes = $("#orgUnitCheckboxes");

        const allOrgUnits = new Map();
        orgUnits.forEach(({ id, displayName }, idx) => {
          // Set first orgUnit to state (should be global org unit)
          if (idx === 0) state = { ...state, orgUnit: id };

          allOrgUnits.set(id, displayName);
          orgUnitCheckboxes.append(`
            <div class="form-check">
              <input
                class="form-check-input"
                type="checkbox"
                value="${id}"
                name="orgUnit"
                id="${id}"
                ${idx === 0 ? "checked" : ""}
              />
              <label class="form-check-label" for="${id}">
                ${displayName}
              </label>
            </div>
          `);
        });

        state = { ...state, allOrgUnits };
      })
      .catch(console.error);
  }

  function handleFormSubmit(e) {
    e.preventDefault();

    // Parse out input values
    const eles = document.querySelector("#filterForm").elements;
    const checkboxes = [...eles].filter((ele) => ele.type === "checkbox");

    const periodCheckboxValues = checkboxes
      .filter((checkbox) => checkbox.name === "period")
      .filter((checkbox) => checkbox.checked)
      .map((checkbox) => checkbox.value);
    const orgUnitCheckboxValues = checkboxes
      .filter((checkbox) => checkbox.name === "orgUnit")
      .filter((checkbox) => checkbox.checked)
      .map((checkbox) => checkbox.value);

    // Check for valid query parameters
    $("#filterForm .alert").remove();
    if (!periodCheckboxValues.length || !orgUnitCheckboxValues.length) {
      $("#filterForm").prepend(`
        <div class="alert alert-danger alert-dismissible fade show" role="alert">
          At least one of each parameter below must be selected.
          <button type="button" class="close" data-dismiss="alert" aria-label="Close">
            <span aria-hidden="true">&times;</span>
          </button>
        </div>
      `);
      return;
    }

    // Format filter parameters
    const periodFilterString = periodCheckboxValues.join(";");
    const periodPrettyString = periodCheckboxValues
      .map((period) => (period === state.allYears ? "All Years" : period))
      .join(", ");
    const orgUnitFilterString = orgUnitCheckboxValues.join(";");
    const orgUnitPrettyString = orgUnitCheckboxValues
      .map((orgUnitId) => state.allOrgUnits.get(orgUnitId))
      .join(", ");

    // TODO: Save values as array to format later

    // Update state
    state = {
      ...state,
      period: periodFilterString,
      orgUnit: orgUnitFilterString,
      periodPrettyString,
      orgUnitPrettyString,
    };
    // Load table
    loadTableData();
    // Close dropdown
    $(".dropdown-toggle").dropdown("toggle");
  }

  function setUpCheckboxForm({ startingYear, maxOrgUnitLevel }) {
    setUpPeriodCheckboxes(startingYear);
    setUpOrgUnitCheckboxes(maxOrgUnitLevel);

    // Use button because forms cannot submit in sandboxed iframes
    $("#updateTable").on("click", handleFormSubmit);
  }

  /**
   *
   * Page-load execution
   *
   */
  jQuery(document).ready(function () {
    // Javascript to be executed after page is loaded here

    setUpCheckboxForm({ startingYear: 2010, maxOrgUnitLevel: 2 });
    populateHtmlTableHeader();
    getAllDimensions()
      .then(populateCellDimensionIds)
      .then(loadTableData)
      .catch(console.error);
  });
</script>

<div class="p-1">
  <!-- HTML Markup to make up report -->
  <h1>Training Summary</h1>

  <div class="mb-2">
    <div class="d-inline-block dropdown dropright">
      <button
        class="btn btn-primary dropdown-toggle"
        type="button"
        id="dropdownMenuButton"
        data-toggle="dropdown"
        aria-haspopup="true"
        aria-expanded="false"
      >
        Filter Period and Org. Unit(s)
      </button>
      <div class="dropdown-menu" aria-labelledby="dropdownMenuButton">
        <form id="filterForm" class="px-4 py-3 mb-0" style="min-width: 30rem">
          <div class="row">
            <div class="col">
              <fieldset class="form-group">
                <legend class="pt-0">Period:</legend>
                <div id="periodCheckboxes">
                  <!-- Populated by script -->
                </div>
              </fieldset>
            </div>

            <div class="col-7">
              <fieldset class="form-group">
                <legend class="pt-0">Organisation Unit:</legend>
                <div id="orgUnitCheckboxes">
                  <!-- Populate by script -->
                </div>
              </fieldset>
            </div>
          </div>

          <div class="dropdown-divider"></div>

          <div class="row">
            <div class="col d-flex justify-content-center">
              <button id="updateTable" class="btn btn-primary" type="submit">
                Update Table
              </button>
            </div>
          </div>
        </form>
      </div>
    </div>

    <div class="btn-group">
      <btn id="downloadCSV" class="btn btn-primary" onclick="exportToCSV()"
        >Download CSV</btn
      >
    </div>
  </div>

  <p>
    <em
      >A cell showing "-" means that no value exists in the database for that
      dimension.</em
    >
  </p>

  <table id="table1" class="table">
    <thead id="thead">
      <tr id="header-row">
        <!-- Script will populate col headers here -->
      </tr>
    </thead>
    <tbody id="tbody">
      <!-- Script will populate rows here -->
    </tbody>
  </table>

  <div id="loading" class="text-center">
    <p>Loading...</p>
    <div class="spinner-border" role="status">
      <span class="sr-only">Loading...</span>
    </div>
  </div>
</div>