reports.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.
 *    Properties of report -- 
 *        String "title" -- This value displays on the report list for that report and in the title of the report page, 
 *        SQL "sql" -- valid SQL for getting the report's data.  Filter, sort, and paging is added to the initial query,
 *        Associate Array "cols": This is a map.  Title of the column => Type of column.  Types acceptable: "string", "number", "boolean", "date", "odata", "currency", "list" and "ignore".
 *     cols correspond to the select part of the SQL positionally.  The first title, type is applied to the first column in the SQL select statement.
 *        Associate Array "ddl" : Title of the column => array(value => text)
 * @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 $dataFile -- 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 because 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= new DateTime();
      $dateText= $date->format("Ym");
      $date= DateTime::createFromFormat("Ym", $dateText); // Clears out the rest of the date.  Just concerned with the year and the month.

      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)
{ 
  // 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; // Invalid so in theory the filter wont 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; // Invalid so in theory the filter wont 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; // 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; // Invalid so in theory the filter won't change
    if (map[""+record.field+"||"+record.operator+"||"+record.value] == null)
      return true;   
  }
  return false;
}
<?php } 

/**
 * 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 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 parameterMapFunc -- if defined, then this function will be called before the read operation.  It can be used to add a parameter.
 * @param Function parseFunc -- if defined, then this function will be called after the read operation.
 * @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, $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.
?>
var filterChanged= true;
function initReport(afterInitFunc, parameterMapFunc, parseFunc, dataBoundFunc, preInitFunc)
{
  var initial= true;
  var pageSize= <?php echo $pageSize; ?>;
  var count= 0;
  var currentFilter= null;
  showWaitWindow();
  $.post("<?php echo $dataUrl; ?>", {report: "<?php echo $report;?>", limit: pageSize, includeSchema: "Y", excludeData: "Y"}, 
  function(topData) {
    hideWaitWindow();
    showSQL(topData);
    if (!errorsAreShown(topData, "formValidateMainDiv"))
    {
      if (typeof(preInitFunc) == "function")
        (preInitFunc)(topData);
      count= topData.reportData.total;
      var sort= <?php echo trim($sort) == "" ? '{field: topData.columnData[0].field, dir: "asc"};' : "$sort;"; ?>
      var filter= <?php echo trim($filter) == "" ? "[];" : "$filter;"; ?>
      var grid= $("#reportGrid").kendoGrid({
        dataSource: {
          transport: {
            read: {
              url: "<?php echo $dataUrl; ?>",
              dataType: "json",
              type: "POST"
            },
            parameterMap: function(data, type) {
              <?php if ($printerFriendly) { ?>
              var parameters= {report: "<?php echo $report; ?>", limit: -1, offset: 0};
              <?php } else { ?>
              var parameters= {report: "<?php echo $report; ?>", limit: pageSize, offset: data.skip};
              <?php } ?>
              if (data.sort != null && data.sort.length > 0)
              {
                var sortCols= [], sortDirs= [];
                for(var i=0, length=data.sort.length; i!= length; i++)
                {
                  var record= data.sort[i];
                  sortCols.push(record.field);
                  sortDirs.push(record.dir);
                }
                parameters.sortCols= kendo.stringify(sortCols);
                parameters.sortDirs= kendo.stringify(sortDirs);
              }
              if (data.filter != null)
              {
                var filterCols= [], filterOps= [], filterValues= [];
                var filters= data.filter.filters;
                for(var i=0, length=filters.length; i!= length; i++)
                {
                  var record= filters[i];
                  filterCols.push(record.field);
                  filterOps.push(record.operator);

                  if (record.value.constructor == Date)
                    filterValues.push(kendo.toString(record.value, 'yyyy-MM-dd'));
                  else
                    filterValues.push(record.value);
                }
                parameters.filterCols= kendo.stringify(filterCols);
                parameters.filterOps= kendo.stringify(filterOps);
                parameters.filterValues= kendo.stringify(filterValues);
              }
              filterChanged= initial || isFilterChanged(currentFilter, data.filter);
              currentFilter= data.filter;

              if (typeof(parameterMapFunc) == "function")
                (parameterMapFunc)(data, parameters, filterChanged);

              if (filterChanged)
              {
                parameters.includeCount= "Y";
                filterChanged= false;
              }   
              initial= false;           
              return parameters;
            }
          },
          schema: {
            model: topData.modelData,
            data: "data",
            total: "total",
            parse: function(data) {
              showSQL(data);
              if (!errorsAreShown(data, "formValidateMainDiv"))
              {
                var countChanged= false;
                if (data.reportData.total == -1)
                {
                  data.reportData.total= count;
                } 
                else
                {
                  count= data.reportData.total; 
                  countChanged= true;
                }
                   
                if (typeof(parseFunc) == "function")
                  (parseFunc)(data, countChanged);
              }
              return data.reportData;
            }
          },
          serverSorting: true,
          serverPaging: true,
          serverFiltering: true,
          pageSize: pageSize,
          sort: sort,
          filter: filter
        },
        columns: topData.columnData,
        <?php if (!$printerFriendly) { ?>
        pageable: <?php echo $pageable; ?>,
        sortable: true,
        filterable: {
          extra: false
        },
        dataBound: function(e) {
          if (typeof(dataBoundFunc) == "function")
            (dataBoundFunc) (this, e);
        },
        <?php } ?>
        scrollable: false 
      }).data("kendoGrid");

      <?php if (!$printerFriendly) { ?>
      $("#downloadCSVBtn").click(function () {
        $("#downloadCSVForm .extraCSVForm").remove();
        var sort= grid.dataSource.sort();
        if (sort != null)
        {
          var sortCols= [], sortDirs= [];
          for(var i=0, length=sort.length; i!= length; i++)
          {
            var record= sort[i];
            sortCols.push(record.field);
            sortDirs.push(record.dir);
          }
          $("#downloadCSVForm").append("<input class='extraCSVForm' name='sortCols' value='" + kendo.stringify(sortCols) + "'>");
          $("#downloadCSVForm").append("<input class='extraCSVForm' name='sortDirs' value='" + kendo.stringify(sortDirs) + "'>");
        }
        var filter= grid.dataSource.filter();
        if (filter != null)
        {
          var filterCols= [], filterOps= [], filterValues= [];
          var filters= filter.filters;
          for(var i=0, length=filters.length; i!= length; i++)
          {
            var record= filters[i];
            filterCols.push(record.field);
            filterOps.push(record.operator);
            filterValues.push(record.value);
          }
          $("#downloadCSVForm").append("<input class='extraCSVForm' name='filterCols' value='" + kendo.stringify(filterCols) + "'>");
          $("#downloadCSVForm").append("<input class='extraCSVForm' name='filterOps' value='" + kendo.stringify(filterOps) + "'>");
          $("#downloadCSVForm").append("<input class='extraCSVForm' name='filterValues' value='" + kendo.stringify(filterValues) + "'>");
        }
        $("#downloadCSVForm").submit();
      });

      if (typeof(afterInitFunc) == "function")
        afterInitFunc();
      <?php } ?>
    }
  });
}
<?php }

/**
 * 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 json_encode($parameters); ?>, 
  function(topData) {
    hideWaitWindow();
    showSQL(topData);
    if (!errorsAreShown(topData, "formValidateMainDiv"))
    {
      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
/**
 * 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) // IE 10+
    navigator.msSaveBlob(blob, filename);
  else 
  {
      var link = document.createElement("a");
      if (link.download != null) 
      { // feature detection
          // 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()
{
  global $reportList;
  $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)
{
  $camelCase= lcfirst(str_replace(" ", "", ucwords(strtolower(trim(preg_replace("/[^a-z0-9 ]+/i", " ", $string)))))); // the column is camelcase with only letters and numbers.
  return is_numeric($camelCase[0]) ? "n$camelCase" : $camelCase; // Kendo seems to complain if the column starts with a number. 
}

/**
 * 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= "<table class='reportMenu'>";
  foreach($conf as $sectionTitle => $reports)
  {
    $menu.= "<tr class='sectionHeader'><td>$sectionTitle</td></tr>";
    $count=1;
    foreach($reports as $report)
    {
      $class= $count % 2 == 1 ? "odd" : "even";
      $reportTitle= isset($reportList[$report]["title"]) ? $reportList[$report]["title"] : $report;
      $href= isset($reportList[$report]["url"]) ? $reportList[$report]["url"] : $reportFrontEndFile . (false !== strpos($reportFrontEndFile, "?") ? "&" : "?") . "report=$report"; 
      // Make sure that there no two ? in the query string
      $menu.= "<tr class='$class'><td>" . ($useRadioButtons ? "<input type='radio' class='reportRadioButton' name='reportRadioButton' value='$href'> $reportTitle" : "<a href=$href>$reportTitle</a>");
      $menu.= "</td></tr>";
      $count++;
    }
  }    
  $menu.= "</table>";
  return $menu;
}

/**
 * function getSchemaUsingBaseSQL($dbh, &$baseSQL, $dateFormat, $dateIfNull, $colArray, $replaceString, $replaceCode, $values, &$sqls, &$errors, &$modelData, &$columnData, $includeSchema, 
 * $report, $defaultDateFormat, $cookieName="")
 * This gets the schema using the base sql.
 *
 * @param integer $dbh -- the database connection
 * @param string $baseSQL -- the SQL from the report configuration.  This will be expanded with the sort and filtering clauses.
 * @param string $dateFormat -- the format for the date.
 * @param string $dateIfNull -- text to display if a date is null.
 * @param array $colArray -- array of the columns in the report.
 * @param string $replaceString -- string to replace code with.
 * @param string $replaceCode -- string to replace.
 * @param array $values -- list of DDLs that a column might have.
 * @param array $sqls -- list of all SQLs.
 * @param array $errors -- list of all errors.
 * @param array $modelData -- will push to this array for the model attribute in the kendo grid.
 * @param array $columnData -- will push to this array for the column attribute in the kendo grid.
 * @param boolean $includeSchema -- if true, something doesn't happen.
 * @param string $report -- the name of the report.
 * @param string $defaultDateFormat -- the default date format.
 * @param string $cookieName -- what the cookie name is.  This is only relevant if the report has replaceString and replaceCode set.  If this is set, then whatever function to get replaceString 
 * is called one time and put in a cookie.
 */
function getSchemaUsingBaseSQL($dbh, &$baseSQL, $dateFormat, $dateIfNull, $colArray, $replaceString, $replaceCode, $values, &$sqls, &$errors, &$modelData, &$columnData, $includeSchema, $report, 
  $defaultDateFormat, $cookieName="")
{
  $cols= array();

  foreach($colArray as $colTitle => $colType)
  {
    $kendoCol= getCamelCase($colTitle);
    $modelRecord= array("type" => $colType == "ignore" ? "string" : $colType);
    $newTitle= explode(".", $colTitle);
    $newTitle= $newTitle[0];
    $columnRecord= array("field" => $kendoCol, "title" => $newTitle, "type" => $colType);
    if ($colType == "date")
    {
      $ifNull= isset($dateIfNull) ? trim($dateIfNull) : "";
      $format= isset($dateFormat) ? trim($dateFormat) : (isset($defaultDateFormat) ? trim($defaultDateFormat) : "");
      if ($format != "")
        $columnRecord["template"]= "# if ($kendoCol == null || $kendoCol == '') { # $ifNull # } else { # #: kendo.toString(new Date($kendoCol), '$format') # # } #";      
    }
    else if ($colType == "number")
    {
      $columnRecord["attributes"]= array("style" => "text-align: right;");
      $columnRecord["headerAttributes"]= array("style" => "text-align: right;");
    }
    else if ($colType == "currency")
    {
      $columnRecord["type"]= "number";
      $modelRecord["type"]= "number";
      $columnRecord["attributes"]= array("style" => "text-align: right;");
      $columnRecord["headerAttributes"]= array("style" => "text-align: right;");
      $columnRecord["format"]= "{0:c2}";      
    }
    else if ($colType == "list")
    {
      $columnRecord["type"]= "string";
      $modelRecord["type"]= "string";

      $ddl= array();
      foreach($values[$colTitle] as $value => $text)
      {
        $ddl[]= array("text" => $text, "value" => $value);
      }
      $columnRecord["values"]= $ddl;
    }
      
    $modelData[$kendoCol]= $modelRecord;

    if ($colType != "ignore")
      $columnData[]= $columnRecord;
    $cols[]= $kendoCol;
  }

  if (isset($replaceString) && isset($replaceCode))
  { 
    $cookie= array();  
    $replaceCodeString= ""; 
    $cookieExists= false;  
    if (isset($_COOKIE[$cookieName]))
    {
      parse_str($_COOKIE[$cookieName], $cookie);
      $cookieExists= true;
    }
      
    if (!$includeSchema && $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);
  }
  
  updateSelectSQL($baseSQL, $cols, $errors);
}

/**
 * function updateSelectSQL(&$baseSQL, $cols, &$errors)
 * 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 $cols -- the columns in the global report array.
 * @param array $errors -- the errors.
 */
function updateSelectSQL(&$baseSQL, $cols, &$errors)
{
  $stack=array();
  $word= "";
  $nonAlphaFound= false;
  $selectFound= false;
  $asFound= false;
  $inExpression= true;
  $fromIndex= -1;
  $selectArray= "";
  $selectCalc= "";
  $selectName= "";
  $selectIndex= 0;
  $whereFound= false;
  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;
            break;
          case "as":
            if ($selectFound && $stackEmpty)
              $asFound= true;
            break;
          case "from":
            if ($selectFound && $stackEmpty)
            {
              $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 != "")
          $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 ",":
          if ($selectFound && $stackEmpty)
          {
            $selectArray.= "$selectCalc as " . $cols[$selectIndex++];
            $selectCalc= "";
            $selectName= "";
            $asFound= false;
            $inExpression= true;
          }
          break;
        case "|":
        case "+":
        case "/":
        case "%":
        case "-":
        case "<":
        case ">":
        case ".":
        case "*":
        case "&":
        case "^":
        case "~":
        case "`":
        case ":":
        case "=":
          if ($stackEmpty)
            $inExpression= true;
         break;   
      }
      $word= "";    
      if ($inExpression)
          $selectCalc.= $char;
      $inExpression= !$willNotBeInExpression;
    }      

  }

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

/**
 * function getData($dbh, $fullSQL, &$reportData, &$modelData, &$columnData, &$sqls, &$errors, $dontIncludeId=false, $excludeData= false)
 * Retrieves the data for the report from the database.
 *
 * @param integer $dbh -- the database connection
 * @param string $fullSQL -- the full SQL (with the clauses for sorting and filtering)
 * @param array $reportData -- the array that contains the data to send to kendo.
 * @param array $modelData -- the array that contains the model data to send to kendo.
 * @param array $columnData -- the array that contains the column data to send to kendo.
 * @param array $sqls -- the SQLs.
 * @param array $errors -- the errors.
 * @param boolean $dontIncludeId -- for CSV.  If it is not CSV, then there is a special id for kendo.
 * @param boolean $excludeData -- if true, then it will do anything else except for actually retrieving the data.
 */
function getData($dbh, $fullSQL, &$reportData, &$modelData, &$columnData, &$sqls, &$errors, $dontIncludeId=false, $excludeData= false)
{  
  $colMap= array();
  foreach($modelData as $key => $unused)
  {
    $colMap[strtolower($key)]= $key;
  }

  // Add id so Kendo knows what to do.
  if (!$dontIncludeId)
  {
    $idName= "reportId";
    $modelData[$idName]= array("type" => "number");
    $modelData= array("id" => $idName, "fields" => $modelData);
  }
  else
  {
    $modelData= array("fields" => $modelData);
  }

  if (!$excludeData)
  {
    $sqls[]= $fullSQL;
    $sth = db_query($fullSQL,$dbh);
    $newError= trim(db_last_error());
    if ($newError != "")
    {
      $errors[]= $newError;
      db_free_result($sth);
    } 

    $booleanFields= array();
    $index= 0;
    foreach($modelData["fields"] as $kendoCol => $kendoColArray)
    {
      if ($kendoColArray["type"] == "boolean")
        $booleanFields[$kendoCol]= array("trueFound" => false, "falseFound" => false, "trueValue" => "", "falseValue" => "", "index" => $index);
      $index++;
    } 

    $i= 0;
    while($dRecord = db_fetch_assoc($sth, $i++)) // 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);

        if (isset($booleanFields[$kendoCol]))
        {
          $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;
              }
          }
        }
        else
        {
          $kendoRecord[$kendoCol]= trim($value);
        } 
      }
      $reportData[]= $kendoRecord;
    } 
    db_free_result($sth);  

    foreach($booleanFields as $kendoCol => $record)
    {
      $columnData[$record["index"]]["values"]= array(array("text" => $record["trueValue"], "value" => true), array("text" => $record["falseValue"], "value" => false));
    }   
  }
}

/**
 * function addSort(&$sql, &$errors, $sortCols, $sortDirs, $modelData)
 * Add the sort clause to the SQL.
 *
 * @param array $sql -- the SQLs.
 * @param array $errors -- the errors.
 * @param string $sortCols -- the columns to sort.
 * @param string $sortDirs -- the directions of the columns to sort.
 * @param array $modelData -- the model data.  This is included because if there isn't any sort, then the first column in the model is sorted.
 */
function addSort(&$sql, &$errors, $sortCols, $sortDirs, $modelData)
{
  $sortCols= trim($sortCols);
  $sortDirs= trim($sortDirs);

  if ($sortCols != "" && $sortDirs != "")
  {
    $colsExpanded= json_decode($sortCols, true);
    $dirsExpanded= json_decode($sortDirs, true);
    if (!is_array($colsExpanded))
      $errors[]= "Sort columns is not a valid array.";
    if (!is_array($dirsExpanded))
      $errors[]= "Sort directions is not a valid array.";
    if (count($colsExpanded) != count($dirsExpanded))
      $errors[]= "Sort arrays need to be the same size.";
    $sortSQL= array();
    $index= 0;
    $allValidDirs= true;
    foreach($colsExpanded as $col)
    {
      $type= trim($modelData[$col]["type"]);
      $dir= strtolower(strval($dirsExpanded[$index]));
      $databaseCol= strtolower($type) == "string" ? "nullif(trim($col), '')" : $col;
      if (in_array($dir, array("asc", "desc")) === false)
        $allValidDirs= false;
      $sortSQL[]= "$databaseCol $dir";
      if (!$allValidDirs)
        break;
      $index++;
    }
    if (!$allValidDirs)
      $errors[]= "Sort directions must be either asc or desc.";

    if (count($sortSQL) != 0)
      $sql.= " order by " . implode(", ", $sortSQL);
  }
  else if (($sortCols != "" && $sortDirs == "") || ($sortCols == "" && $sortDirs != ""))
    $errors[]= "Both sort column and sort direction arrays must be defined for a sort.";
  else // There is always a sort.  If there isn't one, then it is the first column asc
  {
    $first= true;
    foreach($modelData as $kendoCol => $kendoColArray)
    {
      if ($first)
      {
        $sql.= " order by " . (strtolower($kendoColArray["type"]) == "string" ? "trim(coalesce($kendoCol, ''))" : $kendoCol) . " asc";
        $first= false;
      }
    }
  }
}

/**
 * function cmpSort($a, $b, $compArray)
 * 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 $compArray -- equivalent to the sort array in kendo.
 */
function cmpSort($a, $b, $compArray)
{
  foreach($compArray as $comp)
  {
    switch($comp["type"])
    {
      case "string":
        $aComp= trim($a[$comp["field"]]);
        $bComp= trim($b[$comp["field"]]);
        $comp= $comp["dir"] == "asc" ? strcmp($aComp, $bComp) : strcmp($bComp, $aComp);
        break;
      case "number":
        $aComp= floatval($a[$comp["field"]]);
        $bComp= floatval($b[$comp["field"]]);
        $comp= $comp["dir"] == "asc" ? $aComp - $bComp : $bComp - $aComp;
        break;
      case "date":
        $aComp= strtotime($a[$comp["field"]]);
        $bComp= strtotime($b[$comp["field"]]);
        $comp= $comp["dir"] == "asc" ? $aComp - $bComp : $bComp - $aComp;
        break;
      // TODO: boolean, odata?
    }

    if ($comp != 0)
      return $comp;
  }
}

/**
 * function addSortFromData(&$data, &$sqls, &$errors, $sortCols, $sortDirs, $modelData)
 * This will sort after the database call.  In the case of getting data from www3, www5, www6, conbining and then sorting.
 *
 * @param array $data -- the combined data
 * @param array $sqls -- the SQLs
 * @param array $errors -- the errors
 * @param array $sortCols -- the columns to sort.
 * @param array $sortDirs -- the directions of the columns to sort.
 * @param array $modelData -- the model data.  This is included because if there isn't any sort, then the first column in the model is sorted.
 */
function addSortFromData(&$data, &$sqls, &$errors, $sortCols, $sortDirs, $modelData)
{
  $sortCols= trim($sortCols);
  $sortDirs= trim($sortDirs);

  if ($sortCols != "" && $sortDirs != "")
  {
    $colsExpanded= json_decode($sortCols, true);
    $dirsExpanded= json_decode($sortDirs, true);
    if (!is_array($colsExpanded))
      $errors[]= "Sort columns is not a valid array.";
    if (!is_array($dirsExpanded))
      $errors[]= "Sort directions is not a valid array.";
    if (count($colsExpanded) != count($dirsExpanded))
      $errors[]= "Sort arrays need to be the same size.";
    $sortSQL= array();
    $index= 0;
    $allValidDirs= true;
    $compArray= array();
    foreach($colsExpanded as $col)
    {
      $type= trim($modelData["fields"][$col]["type"]);
      $dir= strtolower(strval($dirsExpanded[$index]));
      if (in_array($dir, array("asc", "desc")) === false)
        $allValidDirs= false;
      $compArray[]= array("field" => $col, "dir" => $dir, "type" => $type);
      if (!$allValidDirs)
        break;
      $index++;
    }
    if (!$allValidDirs)
      $errors[]= "Sort directions must be either asc or desc.";

    if (count($sortSQL) != 0)
      $sql.= " order by " . implode(", ", $sortSQL);
  }
  else if (($sortCols != "" && $sortDirs == "") || ($sortCols == "" && $sortDirs != ""))
    $errors[]= "Both sort column and sort direction arrays must be defined for a sort.";
  else // There is always a sort.  If there isn't one, then it is the first column asc
  {
    foreach($modelData as $kendoCol => $kendoColArray)
    {
        $compArray[]= array("field" => $kendoCol, "dir" => "asc", "type" => isset($kendoColArray["type"]) ? $kendoColArray["type"] : "string");
        break;
    }
  } 

  usort($data, function($a, $b) use ($compArray) {
    return cmpSort($a, $b, $compArray);
  }); 
}

/**
 * function addPagingFromData(&$data, $limit, $offset)
 * This will set the current page after retrieving data.  In the case of getting data from www3, www5, www6, conbining and then sorting.
 *
 * @param array $data -- the combined data
 * @param integer $limit -- pageSize
 * @param integer $offset -- where to start
 */
function addPagingFromData(&$data, $limit, $offset)
{
  $total= count($data);
  if ($limit != -1)
    $data= array_slice($data, $offset, $limit);
  $data= array("total" => $total, "data" => $data);
}

/**
 * function addFilter(&$sql, &$countSQL, &$errors, $filterCols, $filterOps, $filterValues, $modelData)
 * This will add the filter to current report SQL.
 *
 * @param string $sql -- the sql to get data.
 * @param string $countSQL -- the sql to get the count.  (The filter needs to be added to both SQLs.)
 * @param array $errors -- the errors.
 * @param string $filterCols -- the columns to filter.
 * @param string $filterOps -- the operations of the columns to filter.
 * @param string $filterValues -- the values of the columns to filter.
 * @param array $modelData -- the model data.  This is needed because the query is slightly different per data type.
 */
function addFilter(&$sql, &$countSQL, &$errors, $filterCols, $filterOps, $filterValues, $modelData)
{
  $filterCols= trim($filterCols);
  $filterOps= trim($filterOps);
  $filterValues= trim($filterValues);
  $filterSQL= array();

  if ($filterCols != "" && $filterOps != "" && $filterValues != "")
  {
    $colsExpanded= json_decode($filterCols, true);
    $opsExpanded= json_decode($filterOps, true);
    $valuesExpanded= json_decode($filterValues, true);
    if (!is_array($colsExpanded))
      $errors[]= "Filter columns is not a valid array.";
    if (!is_array($opsExpanded))
      $errors[]= "Filter operators is not a valid array.";
    if (!is_array($valuesExpanded))
      $errors[]= "Values is not a valid array.";
    if (!(count($colsExpanded) == count($opsExpanded) && count($opsExpanded) == count($valuesExpanded)))
      $errors[]= "Filter arrays need to be the same size.";
    $allValidCols= true;
    $allValidOps= true;
    $index= 0;
    $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($colsExpanded as $col)
    {
      $databaseCol= strtolower($col);
      $op= strtolower(strval($opsExpanded[$index]));  
      $value= strtolower(strval($valuesExpanded[$index]));
      if (!isset($opStartMap[$op]))
        $allValidOps= false;

      switch($modelData[$col]["type"])
      {
        case "string":
          $filterSQL[]= "trim(from lower($databaseCol)) " . $opStartMap[$op] . trim($value) . $opEndMap[$op];
          break;
        case "number":
        case "date": // Handled on frontend
          $filterSQL[]= "$databaseCol " . $opStartMap[$op] . trim($value) . $opEndMap[$op];
          break;
        // TODO: boolean, odata?      
      }
      
      if (!$allValidOps)
        break;
      $index++;    
    }
  }
  else if (!($filterCols == "" && $filterOps == "" && $filterValues == ""))
    $errors[]= "All three filter arrays (Columns, operators, and values) must be defined for a filter.";

  $filterSQL= count($filterSQL) == 0 ? "" : " where (" . implode(") and (", $filterSQL) . ")";
  $sql.= $filterSQL;
  $countSQL.= $filterSQL;
}

/**
 * function readReportCommon($report, &$errors, &$sqls, $dbh, $limit, $offset, $includeSchema, $includeCount, $excludeData, $dontIncludeId, $excludeSort, $sortCols, $sortDirs, 
 * $filterCols, $filterOps, $filterValues, $reportList, $defaultDateFormat, $cookieName)
 * 
 * This reads the report and returns the actual data plus the kendo model and column attributes.  This is the main entry point of this script.
 *
 * @param string $report -- the key of the report in the report definition global variable.
 * @param array $errors -- the errors.
 * @param array $sqls -- the sqls.
 * @param integer $dbh -- the database connection.
 * @param integer $limit -- the limit parameter (page size).
 * @param integer $offset -- the offset parameter (what page?).
 * @param boolean $includeSchema -- if true, include model data.
 * @param boolean $includeCount -- if true, also run the count query.  For sort, this is unnecessary as the count stays the same.  It is necessary for if the filter changes or a force refresh.
 * @param boolean $excludeData -- if true, then the query to get the data won't actually be run.  I have the reports firing out two calls at initialization because I need the model and column data.
 * @param boolean $dontIncludeId -- if true, there won't be an additional id column for the kendo grid.  This is for CSV.
 * @param boolean $excludeSort -- if true, ignores the sort parameters if needed to combine data from www3, www5, www6 first.
 * @param string $sortCols -- list of columns to sort.
 * @param string $sortDirs -- list of directions of the columns to sort.
 * @param string $filterCols -- list of columns to filter.
 * @param string $filterOps -- list of operations of columns to filter.
 * @param string $filterValues -- list of values of columns to filter.
 * @param array $reportList -- the global report list definition.
 * @param string $defaultDateFormat -- the default date format.
 * @param string $cookieName -- the cookie name.  Only relevant if there is a report with the replaceString and replaceCode values set.
 *
 * @return array --
 *    array $sql -- the SQLs used.
 *    array $errors -- the errors.
 *    array $reportData -- the data of the report.
 *    array $modelData -- the array needed for the kendo grid's dataSource > schema > model attribute.
 *    array $columnData -- the array needed for the kendo grid's column attribute.
 */
function readReportCommon($report, &$errors, &$sqls, $dbh, $limit, $offset, $includeSchema, $includeCount, $excludeData, $dontIncludeId, $excludeSort, $sortCols, $sortDirs, 
  $filterCols, $filterOps, $filterValues, $reportList, $defaultDateFormat, $cookieName)
{
  $reportRecord= $reportList[$report];  
  validateReportRecord($reportRecord, $errors);

  if (count($errors) == 0)
  {
    $baseSQL= $reportRecord["sql"];
    $colList= array();

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

    $dateFormat = isset($reportRecord["dateFormat"]) ? $reportRecord["dateFormat"] : "";
    $dateEmpty = isset($reportRecord["dateEmpty"]) ? $reportRecord["dateEmpty"] : "";
    $replaceString = isset($reportRecord["replaceString"]) ? $reportRecord["replaceString"] : null;
    $replaceCode = isset($reportRecord["replaceCode"]) ? $reportRecord["replaceCode"] : null;
    $ddl = isset($reportRecord["ddl"]) ? $reportRecord["ddl"] : "";

    getSchemaUsingBaseSQL($dbh, $baseSQL, $dateFormat, $dateEmpty, $reportRecord["cols"], $replaceString, $replaceCode,
      $ddl, $sqls, $errors, $modelData, $columnData, $includeSchema, $report, $defaultDateFormat, $cookieName);

    $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.
    addFilter($fullSQL, $countSQL, $errors, $filterCols, $filterOps, $filterValues, $modelData);

    if (!$excludeSort) // If there are multiple servers, then this function is unnecessary.
      addSort($fullSQL, $errors, $sortCols, $sortDirs, $modelData);

    if ($limit != -1)
    {
      $fullSQL.= " limit $limit offset $offset";
    }
      
  }
  $reportData= array();

  if (count($errors) == 0)
  {
    getData($dbh, $fullSQL, $reportData, $modelData, $columnData, $sqls, $errors, $dontIncludeId, $excludeData);
  }

  $reportData= array("total" => -1, "data" => $reportData);
  if ($includeCount)
  {
    $sql= $countSQL;
    $sqls[]= $sql;
    $sth = db_query($sql,$dbh);
    $newError= trim(db_last_error());
    if ($newError != "")
    {
      $errors[]= $newError;
    }
    else
    {
      list($count) = db_fetch_array($sth,0);
      $reportData["total"]= $count;      
    }
    db_free_result($sth);
  }
  return array("sql" => $sqls, "error" => $errors, "reportData" => $reportData, "modelData" => $modelData, "columnData" => $columnData);
}

/**
 * function runReadReportCurl($server, $parameters, $showSQL, $cookieName="")
 *
 * Reads the readReport operation through a curl call.
 *
 * @param string $server -- e.g. www4, www3, www.
 * @param array $parameters -- additional parameters.
 * @param boolean $showSQL -- if true, show the SQL from the curl call.
 * @param string $cookieName -- cookieName is relevant if one report has the replaceString and replaceCode attributes set.
 * 
 * @return runCurl contents
 */
function runReadReportCurl($server, $parameters, $showSQL, $cookieName="")
{
  global $dataFile;
  $cmd = "https://${server}.homecu.net${dataFile}?operation=readReport";
  if ($showSQL)
    $cmd.= "SQL"; // Flips on the show SQL flag for the remote call so SQL can be shown from devel but not prod and so that I don't have to decode the json and then encode it right away.
  return runCurl($server, $parameters, $showSQL, $cmd, $cookieName);  
}

/**
 * function runCurl($server, $parameters, $showSQL, $cmd, $cookieName) 
 *
 * Runs a curl call
 *
 * @param string $server -- e.g. www4, www3, www.
 * @param array $parameters -- additional parameters.
 * @param boolean $showSQL -- if true, show the SQL from the curl call.
 * @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($server, $parameters, $cookieName)
{
  global $dataFile;
  $remoteFile = substr($dataFile, 0, -4);

  $curlServers = array("www3", "www5", "www6");

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

  $cmd = "https://${server}.homecu.net${remoteFile}?operation=readReport";

  $ch=curl_init($cmd);
  curl_setopt($ch, CURLOPT_COOKIE, $curlCookies);
  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, "/tmp/reportCookie");
  curl_setopt($ch, CURLOPT_COOKIEFILE, "/tmp/reportCookie");

  $rawresp=curl_exec($ch);

  return $rawresp;
}

/**
 * function printCSVData($reportValues, $report, $reportTitle, $reportList, $defaultDateFormat, $delimiter=",")
 *
 * Downloads a CSV from the page.
 *
 * @param array $reportValues -- 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($reportValues, $report, $reportTitle, $reportList, $defaultDateFormat, $delimiter=",")
{
  $reportRecord= $reportList[$report];
  $reportName= $reportTitle != "" ? $reportTitle : (isset($reportRecord["title"]) ? $reportRecord["title"] : trim($report));
  $kendoDateFormat= isset($reportRecord["dateFormat"]) ? $reportRecord["dateFormat"] : (isset($defaultDateFormat) ? $defaultDateFormat : "MM/dd/yyyy");

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

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

  $phpDateFormat= $kendoDateFormat;
  foreach($phpDateFormatArray as $from => $to)
  {
    $phpDateFormat= str_replace($from, $to, $phpDateFormat);
  }
  
  // This pushes the date format back in 
  foreach($reportValues["modelData"]["fields"] as $kendoCol => $modelRecord)
  {
    if ($modelRecord["type"] == "date")
      $dateArray[$kendoCol]= true;
  }

  foreach($reportValues["columnData"] as $column)
  {
    $title= strval($column["title"]);
    if (strpos($title, "$delimiter") !== false)
      $title= "\"$title\"";
    $csvRow[]= $title;
  }
  $csvData[]= implode("$delimiter ", $csvRow);

  foreach($reportValues["reportData"]["data"] as $row)
  {
    $csvRow= array();
    foreach($row as $col => $value)
    {
      if (isset($dateArray[$col]))
      {
        try 
        {
          $dateTime= new DateTime($value);
          $value= $dateTime->format($phpDateFormat);
        }
        catch (exception $e)
        {
          $value= "(Invalid Date Format)";
        }        
      }
      else 
        $value= strval($value);

      if (strpos($value, "$delimiter") !== false || strpos($value, "\n") !== false)
        $value= "\"$value\"";
      $csvRow[]= $value;
    }
    $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 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.
 * @param array $errors -- the errors.
 */
function validateReportRecord($reportRecord, &$errors)
{
  try
  {
    if (!isset($reportRecord["sql"]))
      throw new exception("SQL is required!", 1);
    if (!isset($reportRecord["cols"]))
      throw new exception("Cols are required!", 2);
    foreach($reportRecord["cols"] as $colName => $colType)
    {
      if (!in_array($colType, array("number", "string", "date", "boolean", "odata", "currency", "ignore", "list")))
        throw new exception("Column type is not known.", 3);
      if ($colType == "list")
      {
        if (!isset($reportRecord["ddl"]))
          throw new exception ("DDLs are not defined.", 4);
        if (!isset($reportRecord["ddl"][$colName]))
          throw new exception ("DDL is not defined for $colName.", 5);
      }
    }
  }
  catch(exception $e)
  {
    $errors[]= $e->getMessage();
  }
}