reporting.i

<?php
/**
 * @package reports.i -- common functions for creating a report based on an SQL and column array.
 * @author SPB
 *
 * Configuration is required.  The file that calls this needs to have the global variables:
 * @param String $defaultDateFormat -- for any date columns, this is the default format.  If this is empty and it is empty at the lower level of configuration,
 *    then no format will be applied to dates.
 *
 * @param Associative Array $reportList -- Each key is a particular report.  This value is not used except internally to identify the report.
 *  $reportKey -- The key that is necessary for the report page.  It needs to be unique.  This will show up on the URL for the report and is referenced in the menu.
 * $title -- The title of the report.  This will show up as the name of the CSV file on download, the title at the top of the page for the report, and the link on the report menu.
 *    If $title is missing, then it uses the key.
 * $sql -- This is required.  It is the base SQL of the report.  Aliases are ignored.  These come from the $col array.  Any sorts are also ignored.  Any sorts and filters are added to this
 *    SQL.  If there is no sort, then the first column is sorted.  Also, the columns are renamed as per the $col array.  This is so any sorts and filters can refer to the column name.
 * $cols -- This is required.  This should be in order of the columns mentioned in the SQL.  It consists of $colTitle => $colValue pairs.
 * $colTitle -- This is the title of the column that will appear in the kendo grid.  The kendo column name also comes from the camel case version of this string.
 *    If the title starts with a number, the kendo column name will have a "n" prepended.  This is because kendo doesn't take kindly to columns starting with a number.
 *    Also, if two columns have the same title, they will still work.  If there is a "." in the title, the actual title is everything before the period and the name takes everything.
 * $colValue -- This is the column type.  Column can be one of the following: "number", "decimal", "string", "date", "boolean", "odata", "currency", "ignore", "list", "hidden", "datetime"
 *    "number" -- refers to kendo's "number" format.  Text aligns right.
 *    "decimal" -- like number but also restrict the number of digits but no need for a $.
 *    "currency" -- like number except also formats the number to $0.00.
 *    "string" -- refers to kendo's "string" format.  Filter is case sensitive and trims first.
 *    "list" -- like string but also adds the "values" attribute to the kendo column.  Option needs the $ddl option for the values.
 *    "date" -- refers to kendo's "date" format.  This will be formatted according to $defaultDateFormat.
 *    "datetime" -- same as "date" but translate to CU time.  This is for the "date with timezone" and other date formats in the database.  ("date" works just fine.)
 *    "odata" -- refers to kendo's "odata" format.  Sort and filter are not well defined for this format.
 *    "boolean" -- refers to kendo's "string" format.  (The boolean format doesn't seem to work.)  It has a "values" attribute according to what is true in the dataset and what is false.
 *        Assumed trues are true, "true", "Y", "yes", etc.  Assumed falses are false, "false", "N", "no", etc.  If both the false and true aren't found, then it gets the opposite.
 *        Regardless of what is true and what is false, it will show as "Y" and "N".
 *    "ignore" -- The column shows up in the dataItem of the grid but doesn't show up in the column array.  (So then you can still do stuff with it.)
 *    "hidden" -- The columns shows up in the dataItem and the column array but it has the "hidden" attribute.  (I added it for grouping on a column.)
 *    "description" -- Like a "string" type but there is no text wrap and there are ellipses.
 *    "unencoded" -- Sets the kendo's column attribute encoded to false.  There is no additional formatting at all.
 * $groupBy -- This is an optional parameter.  It refers to the $colTitle of the column to group.  If this is set, then the attributes "serverSideGrouping" and "group" are added to dataSource.
 *    The data for the calls are returned in a format grouped by the column.  The CSV becomes grouped as well.  The attribute "group" is added to the schema to parse the data correctly.
 *    Sorts sort on the groupBy column first before the actual columns being sorted.
 * $groupDescription -- This is an optional parameter.  It doesn't do anything if $groupBy is not set.  It refers to the $colTitle of the description column.
 *    It adds the aggregate max to the description column underneath the group attribute.  This allows that column's value to be displayed in the group header instead of the $groupBy column.
 * $groupForceSortBy -- Forces the group sort to be by the $groupBy when both $groupBy and $groupDescription are defined.
 * $ddl -- This is an optional parameter.  This contains all the DDLs of the "list" columns.
 * $ddlColumn -- This refers to the $colTitle of the group with a DDL.  It needs $value => $text pairs in the DDL.
 * $value -- Refers to the "value" attribute in the "values" attribute for a column.
 * $text -- Refers to the "text" attribute in the "values" attribute for a column.
 * $dateFormat -- This is what any dates should be formatted as for the report.  If this is not set, then it gets the default date format for all the reports.
 * $dateEmpty -- This is what the text will be if the date is null.  The default is an empty string.
 * $href -- Optional.  If set, this the URL is different (versus the default $self&report=$report).
 * $colHeader -- Optional.  If set, then columns will have another line for the column header.
 * $schemaFunc -- Optional.  If set, then it refers to another function defined in this script.  Function looks like addProfileTemplates($colTitle, $colType, $kendoCol, &$columnRecord).
 *    The function is called once per column in the readReportSchema function.
 * $dataFunc -- Optional.  If set, then it referes to another function defined in this script.  Function looks like addLinksToOrphanedRecordsReport($dbh, &$dataRecord).
 *    The function is called once per data row in the getData function.
 * $dataFuncVariables -- Optional array to send to custom function in this script defined by "dataFunc".
 * $showOnCSV -- Optional.  If not set, then "ignore" and "hidden" columns are not shown on the CSV.  Other columns are shown.  If set, for the columns specified, true means the column
 * shows up in the CSV.
 * nonSqlDataFunc -- Either this option or the SQL option needs to be defined.  (If both are defined, SQL takes precedence.)  This is set to function name.  Function needs to be defined here.
 * Function has one parameter: $dbh which is the database connection.  It must return an array with "data" -- array of what should go into the report, "status" -- "000" if successful,
 * "error" -- "" if successful; otherwise it is the text of the error message, "sql" -- optional array of SQL used (if the query is too complex for the report engine or if it needs functions.)
 * defaultSort -- If set, then use this instead of the default sort which is the first visible column ascending.
 * autoBind -- If set, then set the autoBind kendo property to this value.
 *
 * @param Associate Array $reportMenu -- Each key is section of the report menu.  Each value is a (Numeric Array) of report keys in that section of the report (corresponds to $reportList).
 * @param String $self -- refers to the file with the data calls, now $_SERVER["PHP_SELF"] set at the main level.
 */

/**
 * class PivotCTE
 * NOTE: Odyssey only.  This is in Mammoth to keep this file in sync but it can only be used in Odyssey.
 * Mammoth uses a prior version of Postgres that doesn't have support for Common Table Expressions (CTEs).
 *
 * This class represents the CTE for months.  It replaces the culogtrack_pivot which has the current month plus the past 12 months.
 * There is a column per month and quarter which is zero or one.
 */
class PivotCTE {
  /**
   * @var array $pivotArray -- the data in the culogtrack_pivot table.
   * @var string $cte -- the WITH clause of the SQL.  This doesn't change so after it is calculated, it is stored here.
   * @var array $columnSQLArray -- this maps the column SQL.  If the SQL is already created, use it.
   */
  private $pivotArray;
  private $cte;
  private $columnSQLArray;

  /**
   * function __construct()
   *
   * This function gets the current timestamp, strips out everything but the year and the month, and then fills in the pivotArray values based on that.
   * The pivotArray will have 13 records: 12 months ago to the current month.
   * Each record has the date as varchar Ym, title as "Jan", "Feb", etc., months as an array of zeroes and ones-- the ones will be the month that that date corresponds to,
   * quarters as an array of zeroes and ones -- the ones will be the quarter that the date corresponds to.
   */
  function __construct() {
    $pivotArray = array();
    try {
      $date = DateTime::createFromFormat("Y-m-d H:i:s", date("Y-m")."-1 00:00:00");
      // NOTE: doing createFromFormat("Ym") is STILL going to create a date with day, hours, minutes, etc.

      for($i = 0; $i <= 12; $i++) {
        $row = array("date" => "'" . $date->format("Ym") . "'::varchar",
                     "title" => $date->format("M"),
                     "months" => array_fill(0,13, 0),
                     "quarters" => array_fill(0,4,0));
        $row["months"][$i] = 1;
        $row["quarters"][floor($i/4)] = 1;
        $pivotArray[] = $row;
        $date->modify("-1 month");
      }
    } catch(exception $e) {
      throw new exception ("Pivot CTE not created correctly: " . $e->getMessage());
    }

    $this->pivotArray = $pivotArray;
    $this->columnSQLArray = array();
  }

  /**
   * public function GetCTE()
   * This function gets the WITH clause to the SQL.  It uses the "values" syntax and gets all the values from the pivotArray.  If the CTE is already calculated, then it just returns that.
   */
  public function GetCTE() {
    if (isset($this->cte)) {
      return $this->cte;
    }

    $cteArray = array();
    foreach($this->pivotArray as $pivotRow) {
      $cteArray[] = "(" . $pivotRow["date"] . "," . implode(",", $pivotRow["months"]) . "," . implode(",", $pivotRow["quarters"]) . ")";
    }

    $this->cte = "with monthPivotCTE(yearmo,m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,q0,q1,q2,q3) as (values " . implode(",", $cteArray) . ")";
    return $this->cte;
  }

  /**
   * public function GetColumnSQL($toBeSummed)
   * This function gets the SQL for the columns.  Each column looks like "sum($toBeSummed * monthColumn)".
   *
   * @param string $toBeSummed -- This is what needs to be summed to be summed by month.
   * @return string -- Add this to the column section of the SQL.
   */
  public function GetColumnSQL($toBeSummed) {
    if (isset($this->columnSQLArray[$toBeSummed])) {
      return $this->columnSQLArray[$toBeSummed];
    }

    $columnArray = array();
    for($i = 0; $i <= 12; $i++) {
      $columnArray[] = "sum($toBeSummed * m$i)";
    }

    $this->columnSQLArray[$toBeSummed] = implode(",", $columnArray);
    return $this->columnSQLArray[$toBeSummed];
  }

  /**
   * public function GetColumns($columnArray)
   * This function gets the column list.
   *
   * @param array $columnArray -- Set the column list as usual here minus the columns for the pivot.
   * @return array $columnArray -- The starting columns plus "MTD" => "number", "Jan" => "number", "Dec" => "number" etc. (That would be if the current date was in February.)
   */
  public function GetColumns($columnArray) {
    $first = true;
    foreach($this->pivotArray as $pivotRow) {
      $col = $first ? "MTD" : $pivotRow["title"];
      $columnArray[$col] = "number";
      $first = false;
    }
    return $columnArray;
  }
}

/**
 * function GetIsFilterChanged()
 * This function determines if the filter is changed.  If it is, then we will also need the count.
 *
 * Javascript parameters:
 * @param Array oldFilter -- the previous filter
 * @param Array newFilter -- the new filter
 */
function GetIsFilterChanged() { ?>
function IsFilterChanged(oldFilter, newFilter) {
  <?php // Take care of nulls ?>
  if (oldFilter == null) {
    if (newFilter == null) {
      return false;
    } else {
      return true;
    }
  } else if (newFilter == null) {
    return true;
  }

  var oldConstructor = oldFilter.constructor;
  var newConstructor = newFilter.constructor;
  var oldLogic, oldFilters, newLogic, newFilters;
  if (oldConstructor === Array) {
    oldLogic = "and";
    oldFilters = oldFilter;
  } else if (oldConstructor === Object) {
    oldLogic = oldFilter.logic == null ? "and" : oldFilter.logic.trim();
    oldFilters = oldFilter.filters;
  } else {
    return false; <?php // Invalid so in theory the filter won't change. ?>
  }

  if (newConstructor === Array) {
    newLogic = "and";
    newFilters = newFilter;
  } else if (newConstructor === Object) {
    newLogic = newFilter.logic == null ? "and" : newFilter.logic.trim();
    newFilters = newFilter.filters;
  } else {
    return false; <?php // Invalid so in theory the filter won't change. ?>
  }

  if (oldLogic != newLogic) {
    return true;
  }
  var oldFiltersEmpty = oldFilters == null || oldFilters.constructor !== Array || oldFilters.length == 0;
  var newFiltersEmpty = newFilters == null || newFilters.constructor !== Array || newFilters.length == 0;
  if (oldFiltersEmpty != newFiltersEmpty) {
    return true;
  }
  if (oldFilters.length != newFilters.length) {
    return true;
  }

  var map = {};
  for(var i = 0; i != oldFilters.length; i++) {
    var record = oldFilters[i];
    if (record.field == null || record.operator == null || record.value == null) {
      return false; <?php // Invalid so in theory the filter won't change ?>
    }
    map["" + record.field + "||" + record.operator + "||" + record.value] = true;
  }
  for(var i = 0; i != newFilters.length; i++) {
    var record = newFilters[i];
    if (record.field == null || record.operator == null || record.value == null) {
      return false; <?php // Invalid so in theory the filter won't change ?>
    }
    if (map["" + record.field + "||" + record.operator + "||" + record.value] == null) {
      return true;
    }
  }
  return false;
}
<?php }

/**
 * function GetReportDefinition($schemaData, $pageSize, $pageable, $sort, $filter, $report, $dataUrl, $printerFriendly)
 * This gets the entire definition for the kendo grid.  This is done in PHP rather than javascript because now I have reference javascript functions.
 *
 * @param $schemaData -- the data from readReportSchema.  This is the kendo's definitions for the model, columns, and group arrays.
 * @param $pageSize -- the pageSize from a constant at the top of the page.
 * @param $pageable -- true if the grid should be pageable.
 * @param $sort -- the sort of the grid.
 * @param $filter -- the filter of the grid.
 * @param $report -- the $reportKey in the $reportList.
 * @param $dataUrl -- the URL of the data calls.
 * @param $printerFriendly -- {true/false} If true, then the grid is not filterable, sort, etc.
 *
 * @return the definition for the report grid.
 */
function GetReportDefinition($schemaData, $pageSize, $pageable, $sort, $filter, $report, $dataUrl, $printerFriendly, $autoBind) {
  // Ensures that the sort and filter are actually arrays because they can be put in directly through the call to the print page.
  $sort = isset($sort) ? (is_string($sort) ? HCU_JsonDecode($sort) : $sort) : array();
  $filter = isset($filter) ? (is_string($filter) ? HCU_JsonDecode($filter) : $filter) : array();

  $dataSource = array(
    "serverSorting" => true, "serverFiltering" => true, "serverSorting" => true, "sort" => $sort,
    "filter" => $filter,
    "transport" => array(
      "read" => "*** read ***"
    ), // End of transport
    "schema" => array(
      "model" => $schemaData["modelData"],
      "data" => "data",
      "total" => "total"
    ),
    "sort" => $sort
  );

  if (isset($schemaData["groupData"])) {
    $dataSource["serverGrouping"] = true;
    $dataSource["group"] = $schemaData["groupData"];
    $dataSource["schema"]["groups"] = "data";
  }

  $gridDefinition = array(
    "dataSource" => $dataSource,
    "columns" => $schemaData["columnData"],
    "scrollable" => false);

  if (!$printerFriendly) {
    $gridDefinition["dataSource"]["serverPaging"] = true;
    $gridDefinition["dataSource"]["pageSize"] = $pageSize;
    $gridDefinition["pageable"] = $pageable;
    $gridDefinition["sortable"] = true;
    $gridDefinition["filterable"] = array(
      "extra" => false,
      "operators" => array(
        "string" => array(
          "eq" => "Is equal to",
          "neq" => "Is not equal to",
          "startswith" => "Starts with",
          "endswith" => "Ends with",
          "contains" => "Contains",
          "doesnotcontain" => "Does not contain"
        ),
        "number" => array(
          "eq" => "Is equal to",
          "neq" => "Is not equal to",
          "gt" => "Greater than",
          "gte" => "Greater than or equal to",
          "lt" => "Less than",
          "lte" => "Less than or equal to"
        ),
        "date" => array(
          "eq" => "Is equal to",
          "neq" => "Is not equal to",
          "gt" => "Is after",
          "gte" => "Is after or equal to",
          "lt" => "Is before",
          "lte" => "Is before or equal to"
        ),
        "list" => array(
          "eq" => "Is equal to",
          "neq" => "Is not equal to",
        )
      )
    );
    $gridDefinition["dataBound"] = "*** reportDataBound ***";
    $gridDefinition["pdf"] = array("allPages" => true, "avoidLinks" => true, "paperSize" => "A4", "margin" => array("top" => "2cm", "left" => "1cm", "right" => "1cm", "bottom" => "1cm"),
      "landscape" => true, "repeatHeaders" => true, "template" => "*** \$(\"#pageTemplate\").html() ***", "fileName" => $report, "scale" => 0.59);
    $gridDefinition["noRecords"] = array("template" => "<tr><td>No Records Found</td></tr>");
    $gridDefinition["autoBind"] = $autoBind;
  }

  // JSON encode but then remove characters that are meant to be actual javascript code.
  $gridDefinition = str_replace('"***', "", str_replace('***"', "", HCU_JsonEncode($gridDefinition)));
  $gridDefinition = str_replace("\\\\", "\\", str_replace("\\/", "/", str_replace('\\"', '"', $gridDefinition))); // Escapes are not necessary anymore.
  return $gridDefinition;
}

/**
 * function InitReport($reportPage, $report, $pageable="true", $pageSize=30, $printerFriendly=false, $sort="", $filter="")
 * Prints out the init function to create the report grid with the appropriate events.  This is server-side.  Please refer to the next function for client-side.
 *
 * PHP parameters:
 * @param string $reportPage -- the url of the report page for calling the database functions readReport and readCSV.
 * @param string $report -- the report id in the report global object for getting the SQL and columns.
 * @param array $reportList -- the reportList with all the definitions.
 * @param string $pageable -- "true" or "false."  Refers to the kendo attribute pageable.
 * @param integer $pageSize -- what the size of the page is.
 * @param boolean $printerFriendly -- if true, then all the controls do not show up on the page.
 * @param string $sort -- a json_encoded array of the initial sort.
 * @param string $filter -- a json_encoded array of the initial filter.
 *
 * Javascript parameters:
 * @param Function afterInitFunc -- if defined, then this function will be called after the report grid is initialized.
 * @param Function dataBoundFunc -- if defined, then this function will be called on the dataBound.
 * @param Function preInitFunc -- if defined, then this function will be called after model and column data received but before initialization
 */
function InitReport($reportPage, $report, $reportList, $defaultDateFormat, $pageable = true, $pageSize = 30, $printerFriendly = false, $sort = "", $filter = "") {

  $dataUrl = $reportPage . (false !== strpos($reportPage, "?") ? "&" : "?") . "operation=readReport"; // Ensure that if there is already a query string to append to it.
  $schemaData = readReportSchema($reportList[$report], $defaultDateFormat);

  $sort = isset($sort) ? trim($sort) : "";
  $filter = isset($filter) ? trim($filter) : "";
  $sort = $sort == "" ? $schemaData["gridConf"]["sort"] : $sort;
  $filter = $filter == "" ? array() : $filter;
  $autoBind = HCU_array_key_exists("autoBind", $reportList[$report]) ? $reportList[$report]["autoBind"] === true : true;
  $gridDefinition = GetReportDefinition($schemaData["gridConf"], $pageSize, $pageable, $sort, $filter, $report, $dataUrl, $printerFriendly, $autoBind);
?>
var filterChanged = true;
var initial = true;
var inInitial = false;
var count = -1;
var currentFilter = null;
var currentSort = null;
var funcs = null;
function InitReport(funcs) {
  window.funcs = funcs;
  var gridDefinition = <?php echo $gridDefinition; ?>;
  if (funcs != null && typeof(funcs.preInitFunc) == "function") {
    (funcs.preInitFunc)(gridDefinition);
  }

  var grid = $("#reportGrid").kendoGrid(gridDefinition).data("kendoGrid");
  <?php if (!$printerFriendly) { ?>
  $(grid.pager.element).hide(); <?php // Hide temporary until databound. ?>
  <?php } ?>

  var toolTipProps = homecuTooltip.defaults;
  toolTipProps.filter = ".showEllipsis:overflown";
  toolTipProps.content = function(e) {
    return $(e.target).text().trim();
  };

  $("#groupRightsGrid").kendoTooltip(toolTipProps);

  <?php if (!$printerFriendly) { ?>

  var warningWindow = $("<div id='warningWindow'></div>").appendTo("body").kendoWindow({
    modal: true,
    visible: false,
    title: "Too Many Records"
  }).data("kendoWindow");

  var resultSizeLimit = 85000; <?php // If it is much higher than this, then CSV and print options aren't likely to work. ?>
  var warningTemplate = kendo.template("<p>You are trying to #: action # with more than #: limit # rows.  Please filter more.</p>"
    + "<a href='\\#' class='k-button k-primary' id='warningClose'>Close</a>");

  $("#warningWindow").on("click", "#warningClose", function() {
    warningWindow.close();
  });

  $("#printBtn").click(function() {
    var resultSize = grid.dataSource.total();
    if (resultSize >= resultSizeLimit) {
      warningWindow.content(warningTemplate({limit: resultSizeLimit, action: "print"})).open().center();
      return; <?php // Don't do anything more. ?>
    }

    $("#printForm .extraPrintForm").remove();
    var sort = grid.dataSource.sort();
    var filter = grid.dataSource.filter();
    $("#printForm").append("<input class='extraPrintForm' name='sort' value='" + kendo.stringify(sort) + "'>");
    $("#printForm").append("<input class='extraPrintForm' name='filter' value='" + kendo.stringify(filter) + "'>");
    $("#printForm").submit();
  });

  $("#downloadCSVBtn").click(function () {
      var resultSize = grid.dataSource.total();
      if (resultSize >= resultSizeLimit) {
        warningWindow.content(warningTemplate({limit: resultSizeLimit, action: "print"})).open().center();
        return; <?php // Don't do anything more. ?>
      }

      $("#downloadCSVForm .extraCSVForm").remove();
      if (currentSort != null) {
        $("#downloadCSVForm").append("<input class='extraCSVForm' name='sort' value='" + kendo.stringify(currentSort) + "'>");
      }
      if (currentFilter != null) {
        $("#downloadCSVForm").append("<input class='extraCSVForm' name='filter' value='" + kendo.stringify(currentFilter) + "'>");
      }
      $("#downloadCSVForm").submit();
  });

  $("#downloadPDFBtn").click(function() {
    var toHide = $(grid.wrapper).find(".k-icon[class^='k-i-sort'], .k-grid-filter");
    <?php // Otherwise the headers will not look right aligned.  Filter and sort are hidden by default but they still take up space in the PDF. ?>
    $(toHide).hide();
    grid.saveAsPDF();
    $(toHide).show();
  });

  if (funcs != null && typeof(funcs.afterInitFunc) == "function") {
    funcs.afterInitFunc();
  }
  <?php } // End not printerFriendly. ?>
} <?php // End InitReport. ?>

<?php
/**
 * var read= function(options)
 * This function is referenced in the GetReportDefinition function.  It is the kendo dataSource > transport > read function.  This is a function so that it can redefine the grid based on
 * the boolean fields.  The first data call will see what is the true value and what is the false value of the boolean fields and then create a DDL.  Since the kendo grid will do another
 * read after recreating the grid, this function will prevent that and instead get the data from the first data call.  All other data calls are normal.
 *
 * @param options -- the options as defined in the kendo datasource api for the read function.
 */
?>
var read = function(options) {
  if (inInitial) {
    options.success(window.reportInitialData);

    inInitial = false;
    if (funcs != null && typeof(funcs.afterInitFunc) == "function") { <?php // Because grid will have been recreated at this point. ?>
      (funcs.afterInitFunc)(window.reportInitialData);
    }
    delete window.reportInitialData;

    return;
  }

  var filterChanged = IsFilterChanged(currentFilter, options.data.filter);
  var parameters = {limit: options.data.pageSize, offset: options.data.skip, report: "<?php echo $report; ?>", initial: initial,
    printerFriendly: <?php echo $printerFriendly ? "true" : "false"; ?>};
  parameters.newFilter = initial || filterChanged;
  if (parameters.newFilter) {
    currentFilter = options.data.filter;
  }
  if (options.data.filter != null) {
    parameters.filter = kendo.stringify(options.data.filter);
  }
  if (options.data.sort != null) {
    parameters.sort = kendo.stringify(options.data.sort);
  }
  currentSort = options.data.sort;

  if (funcs != null && typeof(funcs.parameterMapFunc) == "function") {
    (funcs.parameterMapFunc) (options.data, parameters, filterChanged);
  }

  showWaitWindow();
  $.post("<?php echo $dataUrl; ?>", parameters, function(data) {
    hideWaitWindow();
    if (data.error.length > 0) {
      $.homecuValidator.displayMessage(data.error, $.homecuValidator.settings.statusError );
    } else {
      if (initial) {
        initial = false;
        if (data.booleanRedefinition.length > 0) {
          window.reportInitialData = data.reportData;
          inInitial = true;
          var grid = $("#reportGrid").data("kendoGrid");
          var colMap = {};
          for(var i = 0, iLength = grid.columns.length; i != iLength; i++) {
            var iCol = grid.columns[i];

            if (iCol.columns != null) { <?php // Column headers. ?>
              for(var j = 0, jLength = iCol.columns.length; j != jLength; j++) {
                var jCol = iCol.columns[j];
                colMap[jCol.field] = {i: i, j: j};
              }
            } else {
              colMap[iCol.field] = {i: i, j: -1};
            }
          }

          <?php // Will need to take in account if there is a column header in the boolean redefinition. ?>
          var length = data.booleanRedefinition.length;
          for(var i = 0; i != length; i++) {
            var record = data.booleanRedefinition[i];

            var mappy = colMap[record.col];
            if (mappy.j == -1) {
              grid.columns[mappy.i].values = record.values;
            } else {
              grid.columns[mappy.i].columns[mappy.j].values = record.values;
            }
          }
          if (length > 0) {
            grid.setOptions({columns: grid.columns});
          }
        }
      }

      parameters.newFilter ? count = data.reportData.total : data.reportData.total = count;
      <?php // If there is a new filter, then the count will be retrieved.  Otherwise use the current count. ?>

    }

    options.success(data.reportData);
  });
};

<?php
/**
 * var reportDataBound= function(e)
 * This function accomplishes nothing except provide a hook for a custom function for any script that uses this reporting.i script.  It is the kendo dataBound function.
 *
 * @param e -- the parameter as defined in kendo grid api for the dataBound function.
 */
?>
var reportDataBound = function(e) {

  <?php // Only show pager when there is more than one page. ?>
  var pager = this.pager.element;
  var pages = this.dataSource.totalPages();
  pages <= 1 ? $(pager).hide() : $(pager).show();

  if (funcs != null && typeof(funcs.dataBoundFunc) == "function") {
     (funcs.dataBoundFunc) (this, e);
  }
};
<?php } // End the PHP function to print out init report functions.

/**
 * @todo: This probably doesn't work now with changes to the internal functions.  This function is used for adm_reports currently.
 * function InitReportClientSide($reportPage, $report, $title, $additionalParameters= array(), $scrollable=false, $beforeInitFunc="", $afterInitFunc="")
 * Prints out the init function to create the report grid with the appropriate events.  This is client-side.  Please refer to the previous function for server-side.
 *
 * PHP parameters:
 * @param string $reportPage -- the url of the report page for calling the database functions readReport and readCSV.
 * @param string $report -- the report id in the report global object for getting the SQL and columns.
 * @param string $title -- the name of the CSV download.
 * @param array $additionalParameters -- parameters to be added to the default parameters to get all the data.
 * @param boolean $scrollable -- refers to the kendo scrollable property
 * @param string $beforeInitFunc -- Javascript function call before the report grid is initialized.
 * @param string $afterInitFunc -- Javascript function call after the report grid is initialized.
 */
function InitReportClientSide($reportPage, $report, $title, $additionalParameters = array(), $scrollable = false, $beforeInitFunc = "", $afterInitFunc = "") {
  $dataUrl = $reportPage . (false !== strpos($reportPage, "?") ? "&" : "?") . "operation=readReport"; // Ensure that if there is already a query string to append to it.
  $parameters = array("report" => $report, "limit" => -1, "offset" => 0, "includeSchema" => "Y");
  foreach($additionalParameters as $key => $value) {
    $parameters[$key] = $value;
  }
?>
function InitReport() {
  var initial = true;
  var pageSize = 30;
  var count = 0;
  var currentFilter = null;
  showWaitWindow();
  $.post("<?php echo $dataUrl; ?>", <?php echo HCU_JsonEncode($parameters); ?>,
  function(topData) {
    hideWaitWindow();
    if (topData.error.length > 0) {
      $.homecuValidator.displayMessage(topData.error, $.homecuValidator.settings.statusError );
    } else {
      var sort = {field: topData.columnData[0].field, dir: "asc"};
      var gridDefinition = {
        dataSource: {
          transport: {
            read: function (options) {
              options.success(topData.reportData.data);
            }
          },
          schema: {
            model: topData.modelData
          },
          pageSize: pageSize,
          sort: sort
        },
        columns: topData.columnData,
        pageable: true,
        sortable: true,
        filterable: {
          extra: false
        },
        scrollable: <?php echo ($scrollable ? "true" : "false"); ?>
      };

      <?php echo ($beforeInitFunc != "" ? "($beforeInitFunc) (gridDefinition);" : ""); ?>

      var grid = $("#reportGrid").kendoGrid(gridDefinition).data("kendoGrid");

      $("#downloadCSVBtn").click(function () {
        downloadCSVClientSide();
      });

      <?php echo ($afterInitFunc != "" ? "($afterInitFunc) (grid);" : ""); ?>
    }
  });
}

<?php
/**
 * @todo: This probably doesn't work now with changes to the internal functions.  This function is used for adm_reports currently.
 * function DownloadCSVClientSide()
 *
 * Creates the CSV from what is in the grid.  Filter and sort are preserved.  Includes all the pages and then when done, sets the page back to the current page.
 * {@link http://stackoverflow.com/questions/14964035/how-to-export-javascript-array-info-to-csv-on-client-side}
 */
?>
function DownloadCSVClientSide() {
  var grid = $("#reportGrid").data("kendoGrid");
  var csvData = [];
  var csvRecord = [];
  var columns = [];
  for(var i = 0, length = grid.columns.length; i!= length; i++) {
    var record = grid.columns[i];

    if (record.hidden == null || !record.hidden) {
      columns.push(record.field);
      csvRecord.push(csvEscape(record.title));
    }
  }
  csvData.push(csvRecord.join());

  var data = grid.dataSource.data();
  var filter = grid.dataSource.filter();
  var sort = grid.dataSource.sort();
  var query = new kendo.data.Query(data);
  data = query.filter(filter).sort(sort).data;

  for(var i = 0; i != data.length; i++) {
    var record = data[i];
    csvRecord = [];

    for(var j = 0; j != grid.columns.length; j++) {
      if (record.hidden == null || !record.hidden) {
        var column = grid.columns[j];
        var kendoCol = column.field;
        var value = column.template != null ? kendo.template(record[kendoCol]) : (column.format != null ? kendo.format(column.format, record[kendoCol]) : record[kendoCol]);
        csvRecord.push(csvEscape(value));
      }
    }

    csvData.push(csvRecord.join());
  }

  var blob = new Blob([csvData.join("\r\n")], { type: 'text/csv;charset=utf-8;' });
  var filename = "<?php echo $title; ?>.csv";
  if (navigator.msSaveBlob) { <?php // IE 10+ ?>
    navigator.msSaveBlob(blob, filename);
  } else {
      var link = document.createElement("a");
      if (link.download != null) { <?php // feature detection ?>
          <?php // Browsers that support HTML5 download attribute ?>
          var url = URL.createObjectURL(blob);
          link.setAttribute("href", url);
          link.setAttribute("download", filename);
          link.style.visibility = 'hidden';
          document.body.appendChild(link);
          link.click();
          document.body.removeChild(link);
      }
  }
}

<?php
/**
 * function CsvEscape(string)
 * Escapes the string for CSV.  That means that if there is a newline or a double quote, it needs to be escaped.
 *
 * @param string $string -- the string to convert.
 * @return string -- the converted string.
 */
?>
function CsvEscape(string) {
  if (string == null || typeof(string) != "string") {
    return string;
  }
  string = string.indexOf('"') != -1 ? string.replace('"', '""') : string;
  string = string.indexOf(',') != -1 || string.indexOf('\n') != -1 ? '"' + string + '"' : string;
  return string;
}
<?php } // end InitReportClientSide

/**
 * function GetReportTitles()
 *
 * Get the titles of the reports.
 * @return array -- titles of the reports.
 */
function GetReportTitles() {
  $reportList = GetReportList();
  $reportTitles = array();
  foreach($reportList as $key => $record) {
    $reportTitles[$key] = isset($record["title"]) ? $record["title"] : $key;
  }
  return $reportTitles;
}

/**
 * function GetCamelCase($string)
 * Removes the whitespaces and capitalizes the first letter in each word for keys.
 *
 * @param string $string -- the string to convert.
 * @return string -- the converted string.
 */
function GetCamelCase($string) {
  $returnString = $string;
  if (!isset($string) || trim($string) == "") {
    $returnString = "";
  } else {
    $camelCase = lcfirst(str_replace(" ", "", ucwords(strtolower(trim(preg_replace("/[^a-z0-9 ]+/i", " ", $string)))))); // the column is camelcase with only letters and numbers.
    $returnString = is_numeric($camelCase[0]) ? "n$camelCase" : $camelCase; // Kendo seems to complain if the column starts with a number.
  }
  return $returnString;
}

/**
 * function CompileMenu($conf, $reportList, $reportFrontEndFile, $useRadioButtons=false)
 * Creates the menu for all the reports.
 *
 * @param array $conf -- the page layout configuration.
 * @param array $reportList -- the report configuration.
 * @param string $reportFrontEndFile -- the URL.
 * @param boolean $useRadioButtons -- If true, then to select, you would click on a radio button.  Otherwise, the list is shown with links.
 *
 */
function CompileMenu($conf, $reportList, $reportFrontEndFile, $useRadioButtons = false) {
  $menu = "";
  foreach($conf as $sectionTitle => $reports) {
    $menu .= "<div>\n<h2 class='primary'>$sectionTitle</h2>\n<div class='row'>\n";

    foreach ($reports as $report) {
      $reportRecord = HCU_array_key_exists($report, $reportList) ? $reportList[$report] : array();
      $reportTitle = HCU_array_key_exists("title", $reportRecord) ? $reportRecord["title"] : $report;
      $href = HCU_array_key_exists("url", $reportRecord) ? $reportRecord["url"] : $reportFrontEndFile . (false !== strpos($reportFrontEndFile, "?") ? "&" : "?") . "report=$report";

      $menu .= "<div class='col-xs-6'>\n<a href=$href><span class='reportTitle'>$reportTitle</span></a></div>\n";
    }
    $menu .= "</div></div>\n";
  }
  return $menu;
}

/**
 * function UpdateSelectSQL($baseSQL, $colDefs)
 * updates the select SQL with the keys based on the camelcase of the titles of the columns.
 *
 * @param string $baseSQL -- SQL to update.
 * @param array $colDefs -- the columns in the global report array.
 */
function UpdateSelectSQL($baseSQL, $colDefs) {
  try {
    if (!isset($baseSQL) || trim($baseSQL) == "") {
      throw new exception("BaseSQL needs to be defined.", 1);
    }
    if (!isset($colDefs) || !is_array($colDefs)) {
      throw new exception("ColDefs needs to be defined.", 2);
    }
    $cols = array();
    foreach($colDefs as $colTitle => $colType) { // Gets only the kendo column names.
      $cols[] = getCamelCase($colTitle);
    }

    $stack = array();
    $word = "";
    $nonAlphaFound = false;
    $selectFound = false;
    $asFound = false;
    $inExpression = true;
    $fromIndex = -1;
    $selectArray = "";
    $selectCalc = "";
    $selectName = "";
    $selectIndex = 0;
    $whereFound = false;
    $baseSQL = trim($baseSQL);
    for($i = 0, $count = strlen($baseSQL); $i != $count; $i++) {
      $char = $baseSQL[$i];

      if(ctype_alnum ($char) || $char == "_") {
        $word .= $char;
        $nonAlphaFound = false;
      } else {
          $willNotBeInExpression = false;
          $numInStack = count($stack);
          $stackEmpty = $numInStack == 0;
          $lastInStack = $stackEmpty ? "" : $stack[$numInStack - 1];

          if ($selectFound && $word != "" && !$nonAlphaFound && $stackEmpty) {
            $inExpression = false;
          }

          switch(strtolower($word)) {
            case "select":
              if (!$selectFound && $stackEmpty) {
                $selectFound = true;
                if ($i > strlen($word) - 1) {
                  $word = " select";
                }
              }
              break;
            case "as":
              if ($selectFound && $stackEmpty) {
                $asFound = true;
              }
              break;
            case "from":
              if ($selectFound && $stackEmpty) {
                $selectCalc = trim($selectCalc);
                if (in_array($selectCalc, array("select *", "*"))) {
                  throw new exception("Asterisk is not supported at this time.", 4);
                }
                $doesContain = preg_match('/(^| |([a-zA-Z0-9_]+\.))\*$/', $selectCalc); // Either it is ONLY a *, OR there is a * preceded by a space "select *" or ", *", OR {table}.*.
                if ($doesContain === false) {
                  throw new exception ("Preg_match failed.", 5);
                }
                if ($doesContain === 1) {
                  throw new exception("Asterisk is not supported at this time.", 6);
                }
                if ($selectCalc[0] != ",") { // Add a space if the first character is not a comma.  Otherwise it looks more natural to not have a space.
                  $selectArray .= " ";
                }
                $selectArray .= "$selectCalc as " . $cols[$selectIndex++];
                $fromIndex = $i;
              }
              break;
            case "case":
              if (!$nonAlphaFound) {
                array_push($stack, $word);
                $inExpression = true;
              }
              break;
            case "end":
              if ($lastInStack == "case" && !$nonAlphaFound) {
                array_pop($stack);
                if (count($stack) == 0) {
                  $willNotBeInExpression = true;
                  $stackEmpty = true;
                } else { // Need to update the lastInStack variable for the case of "end)"
                  $lastInStack = $stack[$numInStack - 2];
                }
              }
              break;
            case "distinct":
              $inExpression = true;
              break;
          }

          $nonAlphaFound = true;
          if ($fromIndex != -1) {
            break;
          } else if ($word != "") {
            if (!$asFound) {
              $selectCalc .= $word;
            }
          }

        switch ($char) {
          case "(":
          case "[":
          case "{":
            array_push($stack, $char);
            $inExpression = true;
            break;
          case "'":
          case '"':
            if ($lastInStack == $char) {
              array_pop($stack);
              if (count($stack) == 0) {
                $willNotBeInExpression = true;
              }
            } else {
              array_push($stack, $char);
            }

            break;
          case "}":
            if ($lastInStack == "{") {
              array_pop($stack);
              if (count($stack) == 0) {
                $willNotBeInExpression = true;
              }
            }
            break;
          case "]":
            if ($lastInStack == "[") {
              array_pop($stack);
              if (count($stack) == 0) {
                $willNotBeInExpression = true;
              }
            }
            break;
          case ")":
            if ($lastInStack == "(") {
              array_pop($stack);
              if (count($stack) == 0) {
                $willNotBeInExpression = true;
              }
            }

            break;
          case ",":
            $inExpression = true;
            if ($selectFound && $stackEmpty) {
              if (!HCU_array_key_exists($selectIndex, $cols)) {
                throw new exception("Column mismatch found.", 3);
              }
              $selectCalc = trim($selectCalc);
              $doesContain = preg_match('/(^| |([a-zA-Z0-9_]+\.))\*$/', $selectCalc); // Either it is ONLY a *, OR there is a * preceded by a space "select *" or ", *", OR {table}.*.
              if ($doesContain === false) {
                throw new exception ("Preg_match failed.", 5);
              }
              if ($doesContain === 1) {
                throw new exception("Asterisk is not supported at this time.", 6);
              }
              if ($selectCalc[0] != ",") { // Add a space if the first character is not a comma.  Otherwise it looks more natural to not have a space.
                $selectArray .= " ";
              }
              $selectArray .= "$selectCalc as " . $cols[$selectIndex++];
              $selectCalc = "";
              $selectName = "";
              $asFound = false;
            }
            break;
          case "|":
          case "+":
          case "/":
          case "%":
          case "-":
          case "<":
          case ">":
          case ".":
          case "*":
          case "&":
          case "^":
          case "~":
          case "`":
          case ":":
          case "=":
            if ($stackEmpty) {
              $inExpression = true;
            }
           break;
        }
        $word = "";

        if (!$asFound) {
          $selectCalc .= $char;
        }
        $inExpression = !$willNotBeInExpression;
      }

    }

    $restOfSQL = substr($baseSQL, $fromIndex);
    $baseSQL = trim($selectArray) . " from " . trim($restOfSQL);

    $returnArray = array("status" => "000", "error" => "", "modifiedSQL" => $baseSQL);
  } catch (exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }

  return $returnArray;
}

/**
 * function FormatReportData($Cu, $dbh, $unprocessedDataArray, $cols, $groupBy, $groupDescription, $dontIncludeId, $initial, &$booleanRedefinition, $dataFunc, $dataFuncVariables)
 * Retrieves the data for the report from the database.
 *
 * @param string $Cu -- the credit union
 * @param integer $dbh -- the database connection
 * @param array $unprocessedDataArray -- Associative array from either sql or report-specific function.  Sort and filter have already taken place at this point.
 * @param array $cols -- the column definition from the $reportRecord.
 * @param string $groupBy -- the column to group by.
 * @param string $groupDescription -- the column to display on the group header row.
 * @param boolean $dontIncludeId -- for CSV.  If it is not CSV, then there is a special id for kendo.
 * @param boolean $initial -- if it is the first time, then also get the DDLs for the boolean columns.
 * @param array $booleanRedefinition -- contains all the DDLs for the boolean fields if this is the first time.
 * @param string $dataFunc -- if this is defined, then it references the name of the function for the report.  The function gets the $dbh and database record.
 *
 * @return the data for the current sort and filter in the grid.
 */
function FormatReportData($Cu, $dbh, $unprocessedDataArray, $cols, $groupBy, $groupDescription, $dontIncludeId, $initial, &$booleanRedefinition, $dataFunc, $dataFuncVariables) {
    $reportData = array();
    $booleanFields = array();
    $index = 0;
    $idName = "reportId";
    $datetimeFields = array();
    $tz = null;
    foreach($cols as $colTitle => $colType) {
      $value = getCamelCase($colTitle);
      $key = strtolower($value);
      $colMap[$key] = $value;

      if ($colType == "boolean") {
        $booleanFields[$value] = array("trueFound" => false, "falseFound" => false, "trueValue" => "", "falseValue" => "");
      } else if ($colType == "datetime") {

        $datetimeFields[$key] = true;
        $tz = GetCreditUnionTimezone($dbh, $Cu);
      }
    }

    $first = true;
    $currentGroup = "";
    foreach($unprocessedDataArray as $i => $dRecord) { // This will keep the camelcase.  The database puts everything as lowercase.
      $kendoRecord = array();
      if (!$dontIncludeId) {
        $kendoRecord[$idName] = $i;
      }

      foreach($dRecord as $key => $value) {
        $kendoCol = $colMap[strtolower($key)];
        $value = trim($value);
        $value = isset($booleanFields[$kendoCol]) ? ($value == "t" ? true : ($value == "" || $value == "f" ? false : $value)) : $value;
        $kendoRecord[$kendoCol] = $value;

        CaptureBooleanValues($booleanFields, $kendoCol, $value); // Will find what is the true value and what is the false value.

        if (HCU_array_key_exists($key, $datetimeFields) && $value != "") {
          // Apply Credit Union timezone.
          $dateTime = new DateTime($value);
          $dateTime->setTimezone(new DateTimeZone($tz));
          $kendoRecord[$kendoCol] = $dateTime->format("Y-m-d H:i:s.u") . "Z";
        }

      }

      if ($dataFunc != "") {
        $dataFunc($Cu, $dbh, $kendoRecord, $dataFuncVariables);
      }

      if ($groupBy != "") {
        $groupCol = GetCamelCase($groupBy);
        $groupValue = $kendoRecord[$groupCol];

        if ($first || $groupValue != $currentGroup) {
          $currentGroup = $groupValue;
          $reportData[$groupValue] = array("field" => $groupCol, "value" => $groupValue, "items" => array(), "hasSubgroups" => false, "aggregates" => array());

          if ($groupDescription != "") {
            $descCol = GetCamelCase($groupDescription);
            $reportData[$groupValue]["aggregates"][$descCol]["max"] = $kendoRecord[$descCol];
          }
          $first = false;
        }
        $reportData[$groupValue]["items"][] = $kendoRecord;
      } else {
        $reportData[] = $kendoRecord;
      }


    }

    if ($initial && count($booleanFields) > 0) { // Need to get the true and false and get the ddl for filtering on booleans.
      foreach($booleanFields as $boolCol => $booleanItem) {
        $booleanRedefinition[$boolCol] = array("col" => $boolCol,
          "values" => array(array("text" => "Y", "value" => $booleanItem["trueValue"]), array("text" => "N", "value" => $booleanItem["falseValue"])));
      }
      $booleanRedefinition = array_values($booleanRedefinition);
    }
    return $groupBy != "" ? array_values($reportData) : $reportData;
}

/**
 * function CaptureBooleanValues(&$booleanFields, $kendoCol, $value)
 * This function will check the value of boolean field.  If the true or false value is not known, then it will assign the current value as true or false for the DDL.
 *
 * @param $booleanFields -- the array to capture the boolean value.
 * @param $kendoCol -- the column currently looked at.
 * @param $value -- the value of the column currently looked at.
 */
function CaptureBooleanValues(&$booleanFields, $kendoCol, $value) {
    if (HCU_array_key_exists($kendoCol, $booleanFields)) {
      $falseFound = $booleanFields[$kendoCol]["falseFound"];
      $trueFound = $booleanFields[$kendoCol]["trueFound"];
      $updateTrue = false;
      $updateFalse = false;
      if (!$trueFound || !$falseFound) {
          switch($value) {
            case "Yes":
              if (!$falseFound) {
                $booleanFields[$kendoCol]["falseValue"] = "No";
              }
              $updateTrue = true;
              break;
            case "yes":
              if (!$falseFound) {
                $booleanFields[$kendoCol]["falseValue"] = "no";
              }
              $updateTrue = true;
              break;
            case "Y":
              if (!$falseFound) {
                 $booleanFields[$kendoCol]["falseValue"] = "N";
               }
               $updateTrue = true;
               break;
            case "y":
              if (!$falseFound) {
                 $booleanFields[$kendoCol]["falseValue"] = "n";
               }
               $updateTrue= true;
               break;
            case "1":
              if (!$falseFound) {
                 $booleanFields[$kendoCol]["falseValue"] = "0";
               }
               $updateTrue = true;
               break;
            case "true":
              if (!$falseFound) {
                 $booleanFields[$kendoCol]["falseValue"] = "false";
               }
               $updateTrue = true;
               break;
            case "True":
              if (!$falseFound) {
                 $booleanFields[$kendoCol]["falseValue"] = "False";
               }
               $updateTrue = true;
               break;
            case "No":
              if (!$trueFound) {
                $booleanFields[$kendoCol]["trueValue"] = "Yes";
              }
              $updateFalse = true;
              break;
            case "no":
              if (!$trueFound) {
                $booleanFields[$kendoCol]["trueValue"] = "yes";
              }
              $updateFalse = true;
              break;
            case "n":
              if (!$trueFound) {
                $booleanFields[$kendoCol]["trueValue"] = "y";
              }
              $updateFalse = true;
              break;
            case "N":
              if (!$trueFound) {
                $booleanFields[$kendoCol]["trueValue"] = "Y";
              }
              $updateFalse = true;
              break;
            case "0":
              if (!$trueFound) {
                $booleanFields[$kendoCol]["trueValue"] = "1";
              }
              $updateFalse = true;
              break;
            case "false":
              if (!$trueFound) {
                $booleanFields[$kendoCol]["trueValue"] = "true";
              }
              $updateFalse = true;
              break;
            case "False":
              if (!$trueFound) {
                $booleanFields[$kendoCol]["trueValue"] = "True";
              }
              $updateFalse = true;
              break;
            default:
              $updateFalse = true;
          }

          if ($updateTrue) {
            $booleanFields[$kendoCol]["trueValue"] = $value;
            $booleanFields[$kendoCol]["trueFound"] = true;
            $kendoRecord[$kendoCol] = true;
          } else if ($updateFalse) {
            $booleanFields[$kendoCol]["falseValue"] = $value;
            $booleanFields[$kendoCol]["falseFound"] = true;
            $kendoRecord[$kendoCol] = false;
          }
      }
    }
}

/**
 * function AddSort($sort, $cols, $groupBy, $groupDescription, $groupForceSortBy, $defaultSort)
 * Add the sort clause to the SQL.
 *
 * @param string $sort -- a JSON encoded string of the current sort in the grid.
 * @param array $cols -- the column definition in the $reportRecord.
 * @param string $groupBy -- the column to group by (will need to sort by this column first before the actual sort.)
 * @param string $groupDescription -- if this exists, then the first sort is this column.  Otherwise, it will be the $groupBy column.  (If both don't exist, then the sort is normal.)
 * @param boolean $groupForceSortBy -- if both $groupBy and $groupDescription exist and this is true, then it sorts by the $groupBy.
 * @param array $defaultSort -- if set, then use this sort when there is no actual sort.
 *
 * @return array("appendToSQL" => the order bys to append to the SQL, "status" => "000" if successful, nonzero number otherwise, "error" => the error encountered)
 */
function AddSort($sort, $cols, $groupBy, $groupDescription, $groupForceSortBy, $defaultSort) {
  $colTypeMap = array();
  $sortSQL = array();
  $appendToSQL = "";

  try {
      foreach($cols as $colTitle => $colType) {
          $colTypeMap[getCamelCase($colTitle)] = $colType;
      }

      if ($groupBy != "") { // Need to check if there is a group by first.
        $groupCol = getCamelCase($groupDescription != "" && !$groupForceSortBy ? $groupDescription : $groupBy);
        $sortSQL[] = "$groupCol asc";
      }

      if ($sort != "") {
          $sort = HCU_JsonDecode($sort);
          if (!is_array($sort)) {
              throw new exception("Sort is not a valid array.", 401);
          }

          foreach($sort as $sortItem) {
              if (!HCU_array_key_exists("field", $sortItem) || !HCU_array_key_exists("dir", $sortItem)) {
                  throw new exception("Sort is not a valid array.", 402);
              }
              if (!in_array($sortItem["dir"], array("asc", "desc"))) {
                  throw new exception("Sort is not a valid array.", 403);
              }
              $sortSQL[] = getSortCol($sortItem["field"], $colTypeMap[$sortItem["field"]]) . " " . $sortItem["dir"];
          }
      } else if (isset($defaultSort)) {
          if (!is_array($defaultSort)) {
              throw new exception("Sort is not a valid array.", 404);
          }

          foreach($defaultSort as $sortItem) {
              if (!HCU_array_key_exists("field", $sortItem) || !HCU_array_key_exists("dir", $sortItem)) {
                  throw new exception("Sort is not a valid array.", 402);
              }
              if (!in_array($sortItem["dir"], array("asc", "desc"))) {
                  throw new exception("Sort is not a valid array.", 403);
              }
              $sortSQL[] = getSortCol($sortItem["field"], $colTypeMap[$sortItem["field"]]) . " " . $sortItem["dir"];
          }
      } else { // There is always a sort.  If there isn't one, then it is the first column asc
          foreach($cols as $colTitle => $colType) {
              if ($colType != "ignore" && $colType != "hidden") {
                  $sortCol = getSortCol(getCamelCase($colTitle), $colType);
                  $sortSQL[] = "$sortCol asc";
                  break;
              }
          }
      }

      // Need to take it out of if/else.  Even though there has to be a sort by and if there isn't then there is a default for the first column.  Now, groups as well.
      if (isset($sortSQL) && count($sortSQL) != 0) {
          $appendToSQL .= " order by " . implode(", ", $sortSQL);
      }

      $returnArray = array("appendToSQL" => $appendToSQL, "status" => "000", "error" => "");
    } catch (exception $e) {
      $returnArray = array("appendToSQL" => "", "status" => $e->getCode(), "error" => $e->getMessage());
    }

    return $returnArray;
}

/**
 * function AddNonSqlSort(&$unprocessedDataArray, $sort, $cols, $groupBy, $groupDescription, $groupForceSortBy)
 * Takes an array and sorts it according to the sorts from the KendoGrid.
 *
 * @param $unprocessedDataArray -- the inputted array.
 * @param $sort -- the JSON encoded string representation of the sort object within the KendoGrid.
 * @param $cols -- the columns array from the report definition array.
 * @param $groupBy -- the groupBy attribute in the report definition array.
 * @param $groupDescription -- the groupDescription attribute in the report definition array.
 * @param $groupForceSortBy -- the groupForceSortBy attribute in the report definition array.
 *
 * @return array("data" => data sorted, "status" => "000" if successful, non-zero if not successful, "error" => empty string if successful, otherwise the error message.)
 */
function AddNonSqlSort($unprocessedDataArray, $sort, $cols, $groupBy, $groupDescription, $groupForceSortBy) {
  $colTypeMap = array();
  $compArray = array();
  $returnArray = array("data" => $unprocessedDataArray, "status" => "000", "error" => "");
  try {
      foreach($cols as $colTitle => $colType) {
          $colTypeMap [GetCamelCase($colTitle)] = $colType;
      }

      if ($groupBy != "") { // Need to check if there is a group by first.
        $groupCol = GetCamelCase($groupDescription != "" && !$groupForceSortBy ? $groupDescription : $groupBy);
        $compArray [] = array("field" => $groupCol, "type" => "string", "dir" => "asc"); // Possibly the "string" needs to not be hardcoded.
      }

      if ($sort != "") {
        $sort = HCU_JsonDecode($sort);
        if (!is_array($sort)) {
          throw new exception("Sort is not a valid array.", 401);
        }

        foreach($sort as $sortItem) {

          if (!HCU_array_key_exists("field", $sortItem) || !HCU_array_key_exists("dir", $sortItem)) {
            throw new exception("Sort is not a valid array.", 402);
          }

          if (!in_array($sortItem["dir"], array("asc", "desc"))) {
            throw new exception("Sort is not a valid array.", 403);
          }

          $compArray [] = array("field" => $sortItem["field"], "type" => $colTypeMap[$sortItem["field"]], "dir" => $sortItem["dir"]);
        }

        // Natural sort is whatever order the function returns data in.
        if (count($sort) > 0) {
          usort($unprocessedDataArray, function($a, $b) use ($compArray) {
            return CmpSort($a, $b, $compArray);
          });
        }

        $returnArray = array("data" => $unprocessedDataArray, "status" => "000", "error" => "");
     }
  } catch (exception $e) {
     $returnArray = array("data" => array(), "status" => $e->getCode(), "error" => $e->getMessage());
  }

  return $returnArray;
}

/**
 * function GetSortCol($kendoCol, $type)
 * Gets the particular sort of the column based on the column type.  So far, there is just the string compare and the number compare.
 *
 * @param $kendoCol -- the kendo column name.
 * @param $type -- the type of the kendo column.
 * @param $isFilter -- if this function is used for filtering dates are cast to the date.  For sorting dates are cast to timestamp.  The "equal" operator seems to be more for the date.
 * @return the string of the comparison SQL clause.
 */
function GetSortCol($sortCol, $type, $isFilter = false) {
    $sortCol = '"' . strtolower($sortCol) . '"';
    switch($type) {
      case "string":
      case "list":
      case "boolean":
      case "description":
        return "lower(trim(coalesce($sortCol::varchar, '')))";
      case "number":
      case "currency":
      case "decimal":
        return "$sortCol::decimal"; // Ensure that it sorts as a number not as ascii.
      case "date":
      case "datetime":
        return "(case when trim(coalesce($sortCol::varchar, '')) = '' then null else $sortCol end)::" . ($isFilter ? "date" : "timestamp");
      default:
        return $sortCol;
    }
}

/**
 * function CmpSort($a, $b, $sortArray)
 * This is a custom sort function for if the sort needed to happen after it was retrieved from the database.  In the case of getting data from www3, www5, www6, conbining and then sorting.
 *
 * @param string $a -- the first object to check
 * @param string $b -- the second object to check
 * @param array $sortArray -- equivalent to the sort array in kendo.
 */
function CmpSort($a, $b, $sortArray) {
  $cmp = 0; // $a > $b is positive; $a < $b is negative; 0 means that $a is equal to $b.
  foreach($sortArray as $sortRow) {
    switch($sortRow["type"]) {
      case "string":
      case "list":
      case "odata":
      case "hidden":
      case "ignore":
      case "description":
      case "unencoded":
      case "unencoded":
        $aCmp = trim($a[$sortRow["field"]]);
        $bCmp = trim($b[$sortRow["field"]]);
        $cmp = $sortRow["dir"] == "asc" ? strcmp($aCmp, $bCmp) : strcmp($bCmp, $aCmp);
        break;
      case "boolean":
        $aCmp = $a[$sortRow["field"]];
        $bCmp = $b[$sortRow["field"]];
        $aCmp = $aCmp === true ? 1 : ($aCmp === false ? 0 : (in_array(strtolower(trim(strval($aCmp))), array("true", "t", "y", "yes", "1")) ? 1 : 0));
        $bCmp = $bCmp === true ? 1 : ($bCmp === false ? 0 : (in_array(strtolower(trim(strval($bCmp))), array("true", "t", "y", "yes", "1")) ? 1 : 0));
        $cmp = $sortRow["dir"] == "asc" ? $aCmp - $bCmp : $bCmp - $aCmp;
        break;
      case "number":
      case "decimal":
      case "currency":
        $aCmp = floatval($a[$sortRow["field"]]);
        $bCmp = floatval($b[$sortRow["field"]]);
        $cmp = $sortRow["dir"] == "asc" ? $aCmp - $bCmp : $bCmp - $aCmp;
        break;
      case "date":
      case "datetime":
        $aCmp = strtotime($a[$sortRow["field"]]);
        $bCmp = strtotime($b[$sortRow["field"]]);
        $cmp = $sortRow["dir"] == "asc" ? $aCmp - $bCmp : $bCmp - $aCmp;
        break;
    }

    if ($cmp != 0) {
      break;
    }
  }

  return $cmp;
}

/**
 * function AddFilter($filter, $cols)
 * This will add the filter to current report SQL.
 *
 * @param array $filter -- the current filter of the grid.
 * @param array $cols -- the column definition from the $reportRecord.
 * @return array("appendToSQL" => the filter where clause to add to SQL, "status" => "000" if successful, nonzero number otherwise, "error" => the error encountered)
 */
function AddFilter($filter, $cols) {

  $appendToSQL = array();

  try {
    if ($filter != "") {
      $filter = HCU_JsonDecode($filter);
      if (!is_array($filter)) {
        throw new exception("Filter is not a valid array.", 501);
      }
      if (!HCU_array_key_exists("logic", $filter) || !HCU_array_key_exists("filters", $filter)) {
        throw new exception("Filter is not a valid array.", 502);
      }
      if (!in_array($filter["logic"], array("and", "or"))) {
        throw new exception("Filter is not a valid array.", 503);
      }

      $opStartMap = array("eq" => "= '", "neq" => "<> '", "lt" => "< '", "lte" => "<= '", "gt" => "> '", "gte" => ">= '", "startswith" => "like '", "contains" => "like '%",
        "endswith" => "like '%", "doesnotcontain" => "not like '%");
      $opEndMap = array("eq" => "'", "neq" => "'", "lt" => "'", "lte" => "'", "gt" => "'", "gte" => "'", "startswith" => "%'", "contains" => "%'", "endswith" => "'", "doesnotcontain" => "%'");
      $colMap = array();

      foreach($cols as $colTitle => $colValue) {
        $key = GetCamelCase($colTitle);
        $colMap[$key] = $colTitle;
      }

      foreach($filter["filters"] as $filterItem) {
        if (HCU_array_key_exists("logic", $filterItem) || HCU_array_key_exists("filters", $filterItem)) {
          throw new exception("At this time, multiple levels of filters are not allowed.", 504);
        }
        if (!HCU_array_key_exists("field", $filterItem) || !HCU_array_key_exists("operator", $filterItem) || !HCU_array_key_exists("value", $filterItem)) {
          throw new exception("Filter is not a valid array.", 505);
        }
        $databaseCol = trim($filterItem["field"]);
        $op = strtolower(trim($filterItem["operator"]));
        $value = prep_save(strtolower(trim($filterItem["value"])));

        // Need to configure is null and is not null.
        $fullOp = $op == "isnull" ? "\"" . strtolower($databaseCol) . "\" is null" : ($op == "isnotnull" ? "\"" . strtolower($databaseCol) . "\" is not null" :
                  getSortCol($databaseCol, $cols[$colMap[$databaseCol]], true) . " " . $opStartMap[$op] . trim($value) . $opEndMap[$op]);

        $appendToSQL[] = $fullOp;
      }
    }

    $appendToSQL = count($appendToSQL) == 0 ? "" : " where (" . implode(") " . $filter["logic"] . " (", $appendToSQL) . ")";

    $returnArray = array("appendToSQL" => $appendToSQL, "status" => "000", "error" => "");
  } catch (exception $e) {
    $returnArray = array("appendToSQL" => "", "status" => $e->getCode(), "error" => $e->getMessage());
  }

  return $returnArray;
}

/**
 * function AddNonSqlFilter(&$unprocessedDataArray, $filter, $cols)
 * This will add a filter to an array based on the columns types and the filter array sent from the KendoGrid.
 *
 * @param $unprocessedDataArray -- the inputted array.
 * @param $sort -- the JSON encoded string representation of the sort object within the KendoGrid.
 * @param $cols -- the columns array from the report definition array.
 * @param $groupBy -- the groupBy attribute in the report definition array.
 * @param $groupDescription -- the groupDescription attribute in the report definition array.
 * @param $groupForceSortBy -- the groupForceSortBy attribute in the report definition array.
 *
 * @return array("data" => the filtered array, "status" => "000" if successful, nonzero if not, "error" => empty string if successful, otherwise error message)
 */
function AddNonSqlFilter($unprocessedDataArray, $filter, $cols) {
  $returnArray = array("data" => $unprocessedDataArray, "status" => "000", "error" => "");
  try {
      if ($filter != "") {
        $filter = HCU_JsonDecode($filter);
        if (!is_array($filter)) {
          throw new exception("Filter is not a valid array.", 501);
        }
        if (!HCU_array_key_exists("logic", $filter) || !HCU_array_key_exists("filters", $filter)) {
          throw new exception("Filter is not a valid array.", 502);
        }
        if (!in_array($filter["logic"], array("and", "or"))) {
          throw new exception("Filter is not a valid array.", 503);
        }

        $colMap = array();

        foreach($cols as $colTitle => $colValue) {
          $key = getCamelCase($colTitle);
          $colMap[$key] = $colTitle;
        }

        $simplifiedTypeMap = array("number" => "number", "decimal" => "number", "currency" => "number", "string" => "string", "list" => "string", "date" => "date", "odata" => "string",
                                   "boolean" => "boolean", "ignore" => "string", "hidden" => "string", "description" => "string", "unencoded" => "string", "datetime" => "date");

        $filterArray = array();
        $logic = $filter["logic"];
        foreach($filter["filters"] as $filterItem) {
          if (HCU_array_key_exists("logic", $filterItem) || HCU_array_key_exists("filters", $filterItem)) {
            throw new exception("At this time, multiple levels of filters are not allowed.", 504);
          }
          if (!HCU_array_key_exists("field", $filterItem) || !HCU_array_key_exists("operator", $filterItem) || !HCU_array_key_exists("value", $filterItem)) {
            throw new exception("Filter is not a valid array.", 505);
          }

          $field = trim($filterItem["field"]);
          $type = $cols[$colMap[$field]];
          $type = $simplifiedTypeMap[$type];

          $filterArray [] = array("field" => $field, "operator" => strtolower(trim($filterItem["operator"])), "value" => strtolower(trim($filterItem["value"])),
                                  "type" => $type);
        }

        $unprocessedDataArray = array_filter($unprocessedDataArray, function($value) use ($filterArray, $logic) {
          return CmpFilter($value, $filterArray, $logic);
        });

        /* From array_filter documentation: Because array_filter() preserves keys, you should consider the resulting array to be an associative array even if the original array had integer keys
        for there may be holes in your sequence of keys. This means that, for example, json_encode() will convert your result array into an object instead of an array.
        Call array_values() on the result array to guarantee json_encode() gives you an array. */
        $returnArray = array("data" => array_values($unprocessedDataArray), "status" => "000", "error" => "");
      }
  } catch (exception $e) {
     $returnArray = array("data" => array(), "status" => $e->getCode(), "error" => $e->getMessage());
  }

  return $returnArray;
}

/**
 * function CmpFilter($dataRow, $filterArray, $logic)
 * Callback function to see if to keep row based on the filtering.
 *
 * @param array $dataRow -- the row to check if it fits.
 * @param array $filterArray -- the filters from the KendoGrid.
 * @param string $logic -- {and/or} If "and", then results of comparisons are &&ed together.  If "or", then results of comparisons are ||ed together.
 */
function CmpFilter($dataRow, $filterArray, $logic) {

  $keepRow = true;
  foreach($filterArray as $filterRow) {
    $dataValue = $dataRow[$filterRow["field"]];
    $filterValue = $filterRow["value"];
    $filterValueLength = strlen($filterValue);
    $cmp = true;

    $dataDate = null;
    $filterDate = null;
    $dataDateFormat = "";
    $filterDateFormat = "";
    $filterBoolean = null;
    $dataBoolean = null;
    if ($filterRow["type"] == "date") {
      $dataDate = DateTime::createFromFormat("Y-m-d H:i:s+|", $dataValue);
      $dataDate = !$dataDate ? DateTime::createFromFormat("Y-m-d+|", $dataValue) : $dataDate;
      $filterDate = DateTime::createFromFormat("Y-m-d H:i:s+|", $filterValue);
      $filterDate = !$filterDate ? DateTime::createFromFormat("Y-m-d+|", $filterValue) : $filterDate;
      $dataDateFormat = $dataDate->format("Y-m-d");
      $filterDateFormat = $filterDate->format("Y-m-d");
    } else if ($filterRow["type"] == "boolean") {
      $dataBoolean = $dataValue === true ? 1 : ($dataValue === false ? 0 : (in_array(strtolower(trim(strval($dataValue))), array("true", "t", "y", "yes", "1")) ? 1 : 0));
      $filterBoolean = $filterValue === true ? 1 : ($filterValue === false ? 0 : (in_array(strtolower(trim(strval($filterValue))), array("true", "t", "y", "yes", "1")) ? 1 : 0));
    } else if ($filterRow["type"] == "string") {
      $dataValue = trim(strtolower($dataValue));
      $filterValue = trim(strtolower($filterValue));
    }

    switch ($filterRow["type"] . " | " . $filterRow["operator"]) {
      case "number | eq": $cmp = floatval($dataValue) == floatval($filterValue); break;
      case "number | neq": $cmp = floatval($dataValue) != floatval($filterValue); break;
      case "number | lt": $cmp = floatval($dataValue) < floatval($filterValue); break;
      case "number | lte": $cmp = floatval($dataValue) <= floatval($filterValue); break;
      case "number | gt": $cmp = floatval($dataValue) > floatval($filterValue); break;
      case "number | gte": $cmp = floatval($dataValue) >= floatval($filterValue); break;
      case "string | eq": $cmp = strcmp($dataValue, $filterValue) == 0; break;
      case "string | neq": $cmp = strcmp($dataValue, $filterValue) != 0; break;
      case "string | startswith": $cmp = substr($dataValue, 0, $filterValueLength) == $filterValue; break;
      case "string | endswith": $cmp = substr($dataValue, -$filterValueLength) == $filterValue; break;
      case "string | contains": $cmp = strpos($dataValue, $filterValue) !== false; break;
      case "string | doesnotcontain": $cmp = strpos($dataValue, $filterValue) === false; break;
      case "date | eq": $cmp = strcmp($dataDateFormat, $filterDateFormat) == 0; break;
      case "date | neq": $cmp = strcmp($dataDateFormat, $filterDateFormat) != 0; break;
      case "date | gt": $cmp = $dataDate > $filterDate; break;
      case "date | gte": $cmp = $dataDate >= $filterDate; break;
      case "date | lt": $cmp = $dataDate < $filterDate; break;
      case "date | lte": $cmp = $dataDate <= $filterDate; break;
      case "boolean | eq": $cmp = intval($dataBoolean) == intval($filterBoolean); break;
      case "boolean | neq": $cmp = intval($dataBoolean) != intval($filterBoolean); break;
      case "boolean | isnull": $cmp = !isset($dataValue); break;
      case "boolean | isnotnull": $cmp = isset($dataValue); break;
    }

    $keepRow = $logic == "and" ? $keepRow && $cmp : $keepRow || $cmp;

    if (($logic == "and" && !$keepRow) || ($logic == "or" && $keepRow)) { // Shortcut.  If there is one false, then AND is false.  If there is one true, then OR is true.
      break;
    }
  }

  return $keepRow;
}

/**
 * function ReadReportData($reportRecord, $dbh, $limit, $offset, $includeCount, $dontIncludeId, $initial, $excludeSort, $sort, $filter, $report, $cookieName)
 * Will get the data for the report.  This is the main function to call for the main read function and the CSV read function.
 *
 * @param $Cu -- the credit union
 * @param $reportRecord -- the record in the $reportList for the particular $report.
 * @param $dbh -- the database connection.
 * @param $limit -- how many records to show.
 * @param $offset -- where to start the record count.
 * @param $includeCount -- where to run the SQL to see how many records there are.  This happens on the initial data call and when the filter changes.  It doesn't happen when getting CSV.
 * @param $dontIncludeId -- if this is false, then a kendoId column is added for the dataSource > schema > model > id attribute in the kendo grid.
 * @param $initial -- if this is true, then get the true/false values for the boolean fields.
 * @param $excludeSort -- by default, a sort is added to the first column.  If a sort is defined, then it uses that sort.  This is for when the sort happens later.
 * @param $sort -- the current sort of the grid.
 * @param $filter -- the current filter of the grid.
 * @param $report -- the $reportKey in the $reportList.
 * @param $cookieName -- the name of the cookie.  This is only relevant if info needs to be cached or whatnot.
 *
 * @return array("error" => $errors, "status" => $status, "reportData" => $reportData, "sql" => $sqls, "booleanRedefinition" => $booleanRedefinition);
 *    $errors -- the first error encountered or an empty array
 *    $status -- "000" if no errors; nonzero otherwise
 *    $sql -- all SQLs used.  (Usually the SQL to get the data only.  If $includeCount, then also the SQL to get the count.)
 *    $booleanRedefinition -- an array of DDLs to update the grid with.  This happens on the first data call.
 */
function ReadReportData($Cu, $reportRecord, $dbh, $limit, $offset, $includeCount, $dontIncludeId, $initial, $excludeSort, $sort, $filter, $report, $cookieName) {
  try {

    validateReportRecord($reportRecord);

    $baseSQL = HCU_array_key_exists("sql", $reportRecord) ? $reportRecord["sql"] : "";
    $nonSqlDataFunc = HCU_array_key_exists("nonSqlDataFunc", $reportRecord) ? $reportRecord["nonSqlDataFunc"] : "";

    $cols = HCU_array_key_exists("cols", $reportRecord) ? $reportRecord["cols"] : array();
    $groupBy = HCU_array_key_exists("groupBy", $reportRecord) ? trim($reportRecord["groupBy"]) : "";
    $groupDescription = HCU_array_key_exists("groupDescription", $reportRecord) ? trim($reportRecord["groupDescription"]) : "";
    $groupForceSortBy = HCU_array_key_exists("groupForceSortBy", $reportRecord) ? $reportRecord["groupForceSortBy"] : false;
    $dataFunc = HCU_array_key_exists("dataFunc", $reportRecord) ? trim($reportRecord["dataFunc"]) : "";
    $dataFuncVariables = HCU_array_key_exists("dataFuncVariables", $reportRecord) ? $reportRecord["dataFuncVariables"] : array();
    $defaultSort = HCU_array_key_exists("defaultSort", $reportRecord) ? $reportRecord["defaultSort"] : null;

    // Likely this syntax can be replaced with nonSqlDataFunc as it should be a more wholistic approach to the two reports in adm_reports.prg that use the replaceString.
    if (isset($replaceString) && isset($replaceCode)) {
      $cookie = array();
      $replaceCodeString = "";
      $cookieExists = false;
      if (isset($_COOKIE[$cookieName])) {
        parse_str($_COOKIE[$cookieName], $cookie);
        $cookieExists = true;
      }

      if ($cookieExists && isset($cookie[$report]) && trim($cookie[$report]) != "") {
        $replaceCodeString = trim($cookie[$report]);
      } else {
        $replaceCodeString = eval($replaceCode);
        $cookie[$report] = $replaceCodeString;
        $expiresInDay = time() + 86400;

        setcookie($cookieName, http_build_query($cookie), $expiresInDay, "/", $_SERVER['SERVER_NAME']);
      }
      $baseSQL = str_replace($replaceString, $replaceCodeString, $baseSQL);
    }

    // Now, we have two options: use the SQL or use the sqlReplaceFunc.  If we use the sqlReplaceFunc, then we will have to sort and filter ourselves.
    if ($baseSQL != "") {
        $results = UpdateSelectSQL($baseSQL, $cols);
        if ($results["status"] !== "000") {
          throw new exception("Update Select SQL failed.", 211);
        }
        $baseSQL = $results["modifiedSQL"];

        $fullSQL = "select * from ($baseSQL) t";
        $countSQL = "select count(*) from ($baseSQL) t";

        // If there are multiple servers, then we do still want to add the filter to the query.
        $results = AddFilter($filter, $cols);
        if ($results["status"] != "000") {
          throw new exception("SQL didn't filter correctly.", 210);
        }

        $fullSQL .= $results["appendToSQL"];
        $countSQL .= $results["appendToSQL"];

        if (!$excludeSort) { // If there are multiple servers, then this function is unnecessary.
          $results = AddSort($sort, $cols, $groupBy, $groupDescription, $groupForceSortBy, $defaultSort);
          if ($results["status"] != "000") {
            throw new exception("SQL didn't sort correctly.", 209);
          }

          $fullSQL .= $results["appendToSQL"];
        }

        if ($limit != -1)
          $fullSQL .= " limit $limit offset $offset";

        $sqls[] = $fullSQL;

        $sth = db_query($fullSQL, $dbh);

        $unprocessedDataArray = db_fetch_all($sth);

        // FALSE is returned if there are no rows in the result, or on any other error.
        $unprocessedDataArray = !$unprocessedDataArray ? array() : $unprocessedDataArray;

        $sql = $countSQL;
        $sqls[] = $countSQL;
        $sth = db_query($sql,$dbh);
        if (!$sth) {
          throw new exception("Count SQL failed.", 201);
        }

        list($count) = db_fetch_array($sth,0);

    } else if ($nonSqlDataFunc) {

      $sqls = array(); // No sqls but prevent an E_NOTICE.
      $results = $nonSqlDataFunc($Cu, $dbh);

      if (!isset($results)) {
        throw new exception("Data function is not formatted correctly.", 206);
      } else if (!HCU_array_key_exists("status", $results) || !HCU_array_key_exists("error", $results) || !HCU_array_key_exists("data", $results)) {
        throw new exception("Data function is not formatted correctly.", 207);
      } else if ($results["status"] !== "000") {
        throw new exception ($results["error"], 208);
      }

      if (HCU_array_key_exists("sql", $results)) {
        $sqls = $results["sql"];
      }

      $rawData = $results["data"];
      $unprocessedDataArray = array();

      // Add columns to rawData.
      $colCount = count($cols);
      $colnames = array();
      foreach($cols as $colTitle => $colType) { // Gets only the kendo column names.
        $colnames[] = getCamelCase($colTitle);
      }
      foreach($rawData as $dataRow) {
        if (count($dataRow) != $colCount) {
          throw new exception ("Data from function doesn't return columns necessary for the report.", 203);
        }

        $unprocessedDataArray [] = array_combine ($colnames, $dataRow);
      }

      // Filter
      $results = AddNonSqlFilter($unprocessedDataArray, $filter, $cols);

      if ($results ["status"] != "000") {
        throw new exception ("Data did not filter correctly.", 204);
      }

      $unprocessedDataArray = $results ["data"];

      $count = count($unprocessedDataArray);

      // Sort
      $results = AddNonSqlSort($unprocessedDataArray, $sort, $cols, $groupBy, $groupDescription, $groupForceSortBy);

      if ($results ["status"] != "000") {
        throw new exception ("Data did not filter correctly.", 205);
      }

      $unprocessedDataArray = $results ["data"];

      // Page
      if ($limit != -1) {
        $unprocessedDataArray = array_slice($unprocessedDataArray, $offset, $limit);
      }

    } else {
      throw new exception ("Either the 'sql' or the 'nonSqlDataFunc' need to be defined.", 202);
    }

    $booleanRedefinition = array();
    $reportData = FormatReportData($Cu, $dbh, $unprocessedDataArray, $cols, $groupBy, $groupDescription, $dontIncludeId, $initial, $booleanRedefinition, $dataFunc, $dataFuncVariables);

    $reportData = array("total" => -1, "data" => $reportData);
    if ($includeCount) {
      $reportData["total"] = $count;
    }

    $returnArray = array("error" => array(), "status" => "000", "reportData" => $reportData, "sql" => $sqls, "booleanRedefinition" => $booleanRedefinition);
  } catch(exception $e) {
    $returnArray = array("error" => array($e->getMessage()), "status" => $e->getCode(), "reportData" => array(), "sql" => $sqls);
  }
  return $returnArray;
}

/**
 * function ReadReportSchema($reportRecord, $defaultDateFormat)
 * Gets the schema for the kendo grid.  That includes the column array, the model array, and the group array.
 *
 * @param $reportRecord -- the record from the $reportList according to the $report.
 * @param $defaultDateFormat -- the default date format.  If the date format is not defined for the report, then it uses this value.
 *
 * @return array("error" => $errors, "status" => $status, "gridConf" => array("modelData" => $modelData, "columnData" => $columnData, "groupData" => $groupData, "sort" => $sort));
 *    $errors -- the first error encountered or an empty array
 *    $status -- "000" if no errors; nonzero otherwise
 *    $gridConf -- contains all the configuration for the kendoGrid.
 *    $modelData -- contains the configuration for the kendoGrid: dataSource > schema > model.
 *    $columnData -- contains the configuration for the kendoGrid: columns.
 *    $groupData -- contains the configuration for the kendoGrid: dataSource > group.
 *    $sort -- contains the default sort: first non-ignore column.
 */
function ReadReportSchema($reportRecord, $defaultDateFormat) {
  try {
    ValidateReportRecord($reportRecord);

    $modelData = array();
    $columnData = array();
    $groupData = null;

    $dateFormat = HCU_array_key_exists("dateFormat", $reportRecord) ? trim($reportRecord["dateFormat"]) : null;
    $format = isset($dateFormat) ? trim($dateFormat) : (isset($defaultDateFormat) ? trim($defaultDateFormat) : "");
    $dateIfNull = HCU_array_key_exists("dateEmpty", $reportRecord) ? trim($reportRecord["dateEmpty"]) : null;
    $replaceString = HCU_array_key_exists("replaceString", $reportRecord) ? trim($reportRecord["replaceString"]) : null;
    $replaceCode = HCU_array_key_exists("replaceCode", $reportRecord) ? trim($reportRecord["replaceCode"]) : null;
    $values = HCU_array_key_exists("ddl", $reportRecord) ? $reportRecord["ddl"] : null;
    $groupBy = HCU_array_key_exists("groupBy", $reportRecord) ? trim($reportRecord["groupBy"]) : null;
    $groupDescription = HCU_array_key_exists("groupDescription", $reportRecord) ? trim($reportRecord["groupDescription"]) : null;
    $cols = HCU_array_key_exists("cols", $reportRecord) ? $reportRecord["cols"] : array();
    $colHeader = HCU_array_key_exists("colHeader", $reportRecord) ? $reportRecord["colHeader"] : array();
    $schemaFunc = HCU_array_key_exists("schemaFunc", $reportRecord) ? $reportRecord["schemaFunc"] : "";
    $defaultSort = HCU_array_key_exists("defaultSort", $reportRecord) ? $reportRecord["defaultSort"] : null;

    $fieldData = array();

    if (count($colHeader) > 0) {
      foreach($colHeader as $header => $lastColumn) {
        $title = explode(".", $header)[0];
        $columnData[] = array("title" => $title, "columns" => array(), "lastColumn" => $lastColumn);
      }
      reset($columnData);
    }

    $sort = array("dir" => "asc");
    $first = true;
    foreach($cols as $colTitle => $colType) {
      $kendoCol = getCamelCase($colTitle);
      $fieldRecord = array();
      $newTitle = explode(".", $colTitle);
      $newTitle = $newTitle[0];
      $columnRecord = array("field" => $kendoCol, "title" => $newTitle);
      if ($colType == "ignore" || $colType == "boolean") {
        $fieldRecord["type"] = "string";
      } else if ($colType == "unencoded") {
        $fieldRecord["type"] = "string";
        $columnRecord["encoded"] = false;
      } else if ($colType == "hidden") {
        $fieldRecord["type"] = "string";
        $columnRecord["hidden"] = true;
      } else if ($colType == "date" || $colType == "datetime") {
        $fieldRecord["type"] = "date";
        if ($format != "") {
          $columnRecord["template"] = "# if ($kendoCol == null || $kendoCol == '') { # $dateIfNull # } else { # #: kendo.toString(new Date($kendoCol), '$format') # # } #";
        }
      } else if ($colType == "number") {
        $fieldRecord["type"] = "number";
        $columnRecord["attributes"] = array("class" => "textAlignRight");
        $columnRecord["headerAttributes"] = array("class" => "textAlignRight");
      } else if ($colType == "decimal") {
        $fieldRecord["type"] = "number";
        $columnRecord["attributes"] = array("class" => "textAlignRight");
        $columnRecord["headerAttributes"] = array("class" => "textAlignRight");
        $columnRecord["format"] = "{0:n}";
      } else if ($colType == "currency") {
        $fieldRecord["type"] = "number";
        $columnRecord["attributes"] = array("class" => "textAlignRight");
        $columnRecord["headerAttributes"] = array("class" => "textAlignRight");
        $columnRecord["format"] = "{0:c2}";
      } else if ($colType == "list") {
        $fieldRecord["type"] = "string";

        $ddl = array();
        foreach($values[$colTitle] as $value => $text) {
          $ddl[] = array("text" => $text, "value" => $value);
        }
        $columnRecord["values"] = $ddl;
      } else if ($colType == "description") {
        $fieldRecord["type"] = "string";
        $columnRecord["attributes"] = array("class" => "showEllipsis");
        $columnRecord["width"] = 100;
      } else {
        $fieldRecord["type"] = $colType;
      }

      if (isset($groupBy) && $groupBy == $colTitle) {
        $groupData = array("field" => $kendoCol);

        if (isset($groupDescription)) {
          $groupDescCol = getCamelCase($groupDescription);
          $groupData["aggregates"] = array(array("field" => $groupDescCol, "aggregate" => "max"));
          $columnRecord["groupHeaderTemplate"] = "#: aggregates.$groupDescCol.max #";
        } else {
          $columnRecord["groupHeaderTemplate"] = "#: value #";
        }
      }

      $fieldData[$kendoCol] = $fieldRecord;

      if ($schemaFunc != "") {
        $schemaFunc($Cu, $colTitle, $colType, $kendoCol, $columnRecord);
      }

      if ($colType != "ignore") {
        if (count($colHeader) > 0) {
          $key = key($columnData);
          $columnData[$key]["columns"][] = $columnRecord;
          if ($colTitle == $columnData[$key]["lastColumn"]) {
            unset($columnData[$key]["lastColumn"]);
            next($columnData);
          }
        } else {
          $columnData[] = $columnRecord;
        }

        if ($first && $colType != "hidden") { // Don't sort on hidden fields either
          if (isset($defaultSort)) {
            $sort = $defaultSort;
          } else {
            $sort["field"] = $kendoCol;
          }
          $first = false;
        }

      }
    }

    $idName = "reportId";
    $fieldData[$idName] = array("type" => "number");
    $modelData["id"] = $idName;
    $modelData["fields"] = $fieldData;
  } catch(exception $e) {
    return array("error" => array($e->getMessage()), "status" => $e->getCode(), "gridConf" => array());
  }
  return array("error" => array(), "status" => "000", "gridConf" => array("modelData" => $modelData, "columnData" => $columnData, "groupData" => $groupData, "sort" => $sort));
}

/**
 * function RunCurl($server, $parameters, $cmd, $cookieName)
 * Runs a curl call
 *
 * @param array $parameters -- additional parameters.
 * @param string $cmd -- the URL to run.
 * @param string $cookieName -- cookieName is relevant if one report has the replaceString and replaceCode attributes set.
 *
 * @return string -- whatever was returned by the call.
 */
function RunCurl($parameters, $cmd, $cookieName) {
  $ch = curl_init($cmd);

  $cookieLine = "HCUTicket=" . urlencode($_COOKIE['HCUTicket']) . ($cookieName != "" ? ";$cookieName=" . urlencode($_COOKIE[$cookieName]) : "");

  curl_setopt($ch, CURLOPT_COOKIE, $cookieLine);
  curl_setopt($ch, CURLOPT_USERPWD,"nobody:no1home");
  curl_setopt($ch, CURLOPT_RETURNTRANSFER,1); # get response as string
  curl_setopt($ch, CURLOPT_POST, true);
  curl_setopt($ch, CURLOPT_POSTFIELDS, $parameters);
  curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
  curl_setopt($ch, CURLOPT_COOKIESESSION, true );
  curl_setopt($ch, CURLOPT_COOKIEJAR, "/home/homecu/tmp/reportCookie");
  curl_setopt($ch, CURLOPT_COOKIEFILE, "/home/homecu/tmp/reportCookie");

  $rawresp = curl_exec($ch);

  return $rawresp;
}

/**
 * function PrintCSVData($reportValues, $report, $reportTitle, $reportList, $defaultDateFormat, $delimiter = ",")
 * Downloads a CSV from the page.
 *
 * @param array $reportData -- the data of the report.
 * @param string $report -- the key of the report which is in the report definition array.
 * @param string $reportTitle -- what to save the CSV as.
 * @param array $reportList -- the report definition array.
 * @param string $defaultDateFormat -- the default date format.  Now, in the configuration, this is intrepreted as the PHP date format so this is translated into javascript date format.
 * @param string $delimiter -- what should separate the columns and column values?  A Comma is assumed.
 */
function PrintCSVData($reportData, $report, $reportTitle, $reportList, $defaultDateFormat, $delimiter = ",") {
  $reportRecord = $reportList[$report];
  $reportName = $reportTitle != "" ? $reportTitle : (HCU_array_key_exists("title", $reportRecord) ? $reportRecord["title"] : trim($report));
  $kendoDateFormat = HCU_array_key_exists("dateFormat", $reportRecord) ? $reportRecord["dateFormat"] : (isset($defaultDateFormat) ? $defaultDateFormat : "MM/dd/yyyy");
  $showOnCSV = HCU_array_key_exists("showOnCSV", $reportRecord) ? $reportRecord["showOnCSV"] : array();

  $groupBy = HCU_array_key_exists("groupBy", $reportRecord) ? trim($reportRecord["groupBy"]) : "";
  $groupDescription = HCU_array_key_exists("groupDescription", $reportRecord) ? trim($reportRecord["groupDescription"]) : "";

  $csvData = array();
  $csvRow = array();

  $phpDateFormatArray = array("yyyy" => "Y", "dd" => "d", "HH" => "H", "MM" => "m", "mm" => "i", "ss" => "s");
  $dateArray = array();
  $ignoreMap = array();
  $currencyMap = array();
  $listMap = array();
  $unencodedMap = array();

  $phpDateFormat = $kendoDateFormat;
  foreach($phpDateFormatArray as $from => $to) {
    $phpDateFormat = str_replace($from, $to, $phpDateFormat);
  }

  if ($groupBy != "") {
    $csvRow[] = ""; // Indent report
  }
  foreach($reportRecord["cols"] as $colTitle => $colType) {
    $kendoCol = strtolower(GetCamelCase($colTitle));

    switch($colType) {
      case "date":
      case "datetime":
        $dateArray[$kendoCol] = true;
        break;
      case "currency":
        $currencyMap[$kendoCol] = true;
        break;
      case "list":
        $listMap[$kendoCol] = $reportRecord["ddl"][$colTitle];
        break;
      case "unencoded":
        $unencodedMap[$kendoCol] = true;
        break;
    }

    // Want the group description to show by default if it exist.  Otherwise want the group id to show by default.
    $showColumnByGroup = $groupBy != "" ? ($groupDescription != "" ? $groupDescription == $colTitle : $groupBy == $colTitle) : false;

    // If the showOnCSV array sets this column to show false or the column is either ignore or hidden then don't show it on the CSV.
    $ignoreColumn = HCU_array_key_exists($colTitle, $showOnCSV) ? !$showOnCSV[$colTitle] : ($showColumnByGroup ? false : in_array($colType, array("ignore", "hidden")));
    $ignoreMap[$kendoCol] = $ignoreColumn;

    // Don't show it if it is part of the group because that will be in a different place in the CSV or if it is ignored.
    if ($colTitle == $groupBy || $colTitle == $groupDescription || $ignoreColumn) {
      continue;
    }

    $csvRow[] = getCsvValue(explode(".", $colTitle)[0], $delimiter);
  }

  $csvData[] = implode("$delimiter", $csvRow);

  if ($groupBy == "") {
    foreach($reportData as $row) {
      $csvRow = array();
      foreach($row as $col => $value) {
        $lcol = strtolower($col);
        if ($ignoreMap[$lcol]) {
          continue;
        }
        if (HCU_array_key_exists($lcol, $dateArray)) {
          try {
            if (isset($value) && trim($value) != "") {
              $dateTime = new DateTime($value);
              $value = $dateTime->format($phpDateFormat);
            }
          } catch (exception $e) {
            $value = "(Invalid Date Format)";
          }
        } else if (HCU_array_key_exists($lcol, $currencyMap)) {
          $value = intval($value) == 0 ? "" : "\$" . number_format(floatval($value), 2);
        } else if (HCU_array_key_exists($lcol, $listMap)) {
          $value = $listMap[$lcol][trim($value)];
        } else if (HCU_array_key_exists($lcol, $unencodedMap)) {
          $value = strip_tags($value);
        } else {
          $value = strval($value);
        }

        $csvRow[] = GetCsvValue($value, $delimiter);
      }
      $csvData[] = implode("$delimiter", $csvRow);
    }
  } else {
    $groupByCol = strtolower(GetCamelCase($groupBy));
    $groupDescriptionCol = strtolower(GetCamelCase($groupDescription));

    foreach($reportData as $row) {
      $csvData[] = "";
      $csvData[] = "";

      // Row telling the group by value.
      if (!$ignoreMap[$groupByCol]) {
        $csvData[] = implode("$delimiter", array(GetCsvValue($groupBy, $delimiter), GetCsvValue($row["value"], $delimiter)));
      }

      // Row telling the group description value.
      if ($groupDescription != "" && !$ignoreMap[$groupDescription]) {
        $descriptionValue = $row["aggregates"][GetCamelCase($groupDescription)]["max"];

        if (!$ignoreMap[$groupDescriptionCol]) {
          $csvData[] = implode("$delimiter", array(GetCsvValue($groupDescription, $delimiter), GetCsvValue($descriptionValue, $delimiter)));
        }
      }

      $csvData[] = "";

      foreach($row["items"] as $rowRow) {

        $csvRow = array(""); // Indent the report.
        foreach($rowRow as $col => $value) {
          $lower = strtolower($col);
          if ($lower == $groupByCol || $lower == $groupDescriptionCol || $ignoreMap[$lower]) {
            continue;
          }

          if (HCU_array_key_exists($lower, $dateArray)) {
            try {
              if (isset($value) && trim($value) != "") {
                $dateTime = new DateTime($value);
                $value = $dateTime->format($phpDateFormat);
              }

            } catch (exception $e) {
              $value = "(Invalid Date Format)";
            }
          } else if (HCU_array_key_exists($lower, $currencyMap)) {
            $value = intval($value) == 0 ? "" : "\$" . number_format(floatval($value), 2);
          } else if (HCU_array_key_exists($lower, $listMap)) {
            $value = $listMap[$lower][trim($value)];
          } else if (HCU_array_key_exists($lower, $unencodedMap)) {
            $value = strip_tags($value);
          } else {
            $value = strval($value);
          }

          $csvRow[] = GetCsvValue($value, $delimiter);
        }
        $csvData[] = implode("$delimiter", $csvRow);
      }
    }
  }

  $csvData = implode("\r\n", $csvData);

  header("Content-length: " . strlen($csvData) );
  header("Content-type: application/octetstream");
  header("Content-disposition: inline; filename=\"$reportName.csv\"");
  print ($csvData);

}

/**
 * function GetCsvValue($value, $delimiter)
 * This will get the CSV value from the regular value.  It essentially escapes the delimiter and new lines.
 *
 * @param $value -- the value
 * @param $delimiter -- the delimiter (most likely a comma)
 * @return the escaped version of the value.
 */
function GetCsvValue($value, $delimiter) {
    $value = str_replace("&nbsp;", " ", trim($value));
    if (strpos($value, "$delimiter") !== false || strpos($value, "\n") !== false) {
      $value = "\"$value\"";
    }
    return $value;
}

/**
 * function ValidateReportRecord($reportRecord, &$errors)
 * Validates the $reportRecord to spot some obvious problems before actually parsing the SQL and adding sort and filter and whatnot.
 *
 * @param array $reportRecord -- the array that has all the information to create a report.
 */
function ValidateReportRecord($reportRecord) {
    if (!HCU_array_key_exists("sql", $reportRecord) && !HCU_array_key_exists("nonSqlDataFunc", $reportRecord)) {
      throw new exception("Either SQL or another way of getting the data is required.", 101);
    }
    if (!HCU_array_key_exists("cols", $reportRecord)) {
      throw new exception("Cols are required.", 102);
    }
    foreach($reportRecord["cols"] as $colName => $colType) {
      if (!in_array($colType, array("number", "decimal", "string", "date", "boolean", "odata", "currency", "ignore", "list", "hidden", "description", "unencoded", "datetime"))) {
        throw new exception("Column type is not known.", 103);
      }
      if ($colType == "list") {
        if (!HCU_array_key_exists("ddl", $reportRecord)) {
          throw new exception ("DDLs are not defined.", 104);
        }
        if (!HCU_array_key_exists($colName, $reportRecord["ddl"])) {
          throw new exception ("DDL is not defined for $colName.", 105);
        }
      }
    }
}