runBilling.i

<?php
/**
 * File: runBilling.i
 * Purpose: This file contains the whole process for running billing.
 *
 * The process takes what is in the contracts (either locally or from my.homecu.net)
 * uses the calculations for each product type and creates preinvoice records.
 * Afterwards, the preinvoice records are imported into QuickBooks using the web connector.
 */

/**
 * function GetGoogleCookie($devmode)
 * Gets the google cookie.  If DEV it is hardcoded.
 *
 * @param $devmode -- 1 if devmode is true

 * @return string
 */
function GetGoogleCookie($devmode) {
    if ($devmode == 1) {
      $googleCookie = "c2FtdWVsQGhvbWVjdS5jb218cVpoMVBFeGVZYlkrTlo5anlUMUwyNWhsYVYrM01XZlUxcW9iOEVXdVpURXdnUi9FLzcvRWprR0FIYWlDSU9rTnI3Zlk3L0tyM3EvcmxPck9nYTFnc1lhMkU5S3Y2d2h6SDJIOHZzT0VNYit0MEo5SEFhYmxKT25xSHdmNkJDZHRETzVENVhVVDB5aWdPam4wQTNWVTNHWWRsd0xLb3F4Vlg4L1FQRmxQRVorREZoS2VvSjRNL053OUdPcVFKcy9VWGc9PXwxNTY3MDk4ODkzfDdMQU56WVl1b0FFQXA4NTZWbGhwMjk2THRPTktqVFcrY3BzaWVkdURzZnZ1Z0ZjOFZmc1N4VzQrbE81R2w4dThUTk9GUWJYZEkxaXRvN3QzTzFkMHN0aDFHOXYrRjczQ2k4NGh3dCt4RnZQbDV3PT0=|1567095293|FEDshz4VY7clGFw5M5z4IT3Afwc=";
    } else {
      $googleCookie = "{$_COOKIE['homecu_dev_oauth2_proxy']}";
    }
    return $googleCookie;
}

/**
 * function RunCurlOdyssey($url, $devmode)
 * Runs the CURL options for Odyssey.  Odyssey requires Google authentication.
 *
 * @param $url -- the URL to run for the CURL command.
 *
 * @return (see RunCurl)
 */
function RunCurlOdyssey($url, $devmode) {
  $googleCookie = GetGoogleCookie($devmode);
  $curlCookies = "HCUTicket=" . urlencode($_COOKIE['HCUTicket']) . ";homecu_dev_oauth2_proxy=$googleCookie";

  $rawresp = RunCurl($url, $curlCookies);

  return $rawresp;
}

/**
 * function RunCurlMammoth($url)
 * Runs the CURL options for Mammoth.  Mammoth doesn't require Google authentication.
 *
 * @param $url -- the URL to run for the CURL command.
 *
 * @return (see RunCurl)
 */
function RunCurlMammoth($url) {
  $curlCookies = "HCUTicket=" . urlencode($_COOKIE['HCUTicket']);

  $rawresp = RunCurl($url, $curlCookies);

  return $rawresp;
}

/**
 * function RunCurl($url, $cookies)
 * Runs the CURL options.
 * There are two additions to the "default" list of CURL options:
 * 1) Handling a connection that times out.
 * 2) Handling a connection with max allocated memory.
 *
 * @param $url -- the URL to run for the CURL command.
 * @param $cookies -- the cookies to use for the CURL command.
 *
 * @return $httpCode -- the http code of the CURL command: 504, 200, or 0.
 * @return $rawresp -- the response from the curl command.
 */
function RunCurl($url, $cookies) {
  $ch = curl_init();
  curl_setopt($ch, CURLOPT_URL, $url);
  curl_setopt($ch, CURLOPT_COOKIE, $cookies);
  curl_setopt($ch, CURLOPT_USERPWD, "nobody:no1home");
  curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30); // In seconds
  curl_setopt($ch, CURLOPT_TIMEOUT, 30); // In seconds
  curl_setopt($ch, CURLOPT_HEADER, 0);
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

  $rawresp = curl_exec($ch);

  $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);

  $returnArray = array("httpCode" => $httpCode, "rawresp" => $rawresp);
  curl_close($ch);

  return $returnArray;
}

/**
 * function GetLimitInBytes()
 * Get the max allowed bytes in PHP.
 * @see https://stackoverflow.com/questions/10208698/checking-memory-limit-in-php
 *
 * @return the number of bytes for the memory limit.
 */
function GetLimitInBytes() {
  $val = trim(ini_get("memory_limit"));
  $last = strtolower($val[strlen($val) - 1]);
  switch($last) {
      // The 'G' modifier is available since PHP 5.1.0
      case 'g':
          $val *= 1024;
      case 'm':
          $val *= 1024;
      case 'k':
          $val *= 1024;
  }
  return $val;
}

/**
 * function GatherBillingInfoChildren($dbh, $sysenv, $staffId)
 * This is the starting part of the process.  This function runs each count.
 * Each count will be in a different call.
 *
 * @param $dbh -- the database connection
 * @param $sysenv -- the environment variables (from cu_top)
 * @param $staffId -- the HomeCU login user
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 * @return $type -- "child" where we are at in the process.
 */
function GatherBillingInfoChildren($dbh, $sysenv, $staffId) {
  try {

    extract ($sysenv["BILL"]);

    if (!isset($sid) || trim($sid) == "") {
      throw new exception ("Sid is required.", 1);
    }

    if (!isset($childType) || trim($childType) == "") {
      throw new exception ("Child type is required.", 2);
    }

    $monthFormat = "Y-m-d|";
    $timeFormat = "H:i:s|";

    if (!isset($reportDate) || trim($reportDate) == "") {
      throw new exception ("Month is required.", 3);
    }
    if (!isset($cutoffDate) || trim($cutoffDate) == "") {
      throw new exception ("Cutoff Date is required.", 4);
    }
    if (!isset($cutoffTime) || trim($cutoffTime) == "") {
      throw new exception ("Cutoff time is required.", 5);
    }

    if (!DateTime::createFromFormat($monthFormat, $reportDate)) {
      throw new exception ("Month is invalid.", 6);
    }
    if (!DateTime::createFromFormat($monthFormat, $cutoffDate)) {
      throw new exception ("Cutoff date is invalid.", 7);
    }
    if (!DateTime::createFromFormat($timeFormat, $cutoffTime)) {
      throw new exception ("Cutoff time is invalid.", 8);
    }

    $startFile = GetStartFile();
    $contractFile = GetContractFile();
    $estatementFile = GetEstatementCountFile();
    $ivrFile = GetIVRCountFile();
    $ipayFile = GetIPayCountFile();
    $timetrackFile = GetTimeTrackCountFile();

    $dateTime = new DateTime($reportDate);
    $monthBit = 1 << intval($dateTime->format("n")) - 1;
    $sysenv["BILL"]["monthBit"] = $monthBit;

    $cutoffDate = str_replace("-", "", $cutoffDate);
    $date = DateTime::createFromFormat("Y-m-d", $reportDate);
    $date->modify("-1 month");
    $regularReportDate = $date->format("Y-m") . "-01";
    $ivrStartDate = $date->format("m/01/Y");
    $ivrEndDate = $date->format("m/t/Y");
    $iPayReportDate = $date->format("Y_m");

    switch($childType) {
      case "contracts":

        if ($sysenv["devmode"] == 1) {

          if (isset($callMy) && $callMy == "Y") {
            $queryResults = GetMonitorBillingList($dbh, $sysenv);
          } else {
            $queryResults = GetPotentiallyFromDifferentServer($dbh, $sysenv);
          }
        } else {
          $queryResults = GetPotentiallyFromDifferentServer($dbh, $sysenv);
        }

        $fileArray = array("sid" => $sid, "reportDate" => $reportDate, "monthBit" => $monthBit,
                           "contracts" => $queryResults, "ready" => true);
        file_put_contents($contractFile, HCU_JsonEncode($fileArray));
        break;

      case "estatements":

        $countResults = GetEstatementCounts($cutoffDate, $cutoffTime, $sysenv["devmode"]);

        $countResults["sid"] = $sid;
        $countResults["cutoffDate"] = $cutoffDate;
        $countResults["cutoffTime"] = $cutoffTime;
        $countResults["ready"] = true;

        file_put_contents($estatementFile, HCU_JsonEncode($countResults));
        break;

      case "ivr":

        $countResults = GetIVRCounts($ivrStartDate, $ivrEndDate);

        $countResults["sid"] = $sid;
        $countResults["ivrStartDate"] = $ivrStartDate;
        $countResults["ivrEndDate"] = $ivrEndDate;
        $countResults["ready"] = true;

        file_put_contents($ivrFile, HCU_JsonEncode($countResults));
        break;

      case "ipay":

        $countResults = GetIPayCounts ($iPayReportDate, $sysenv["devmode"]);

        $countResults["sid"] = $sid;
        $countResults["iPayReportDate"] = $iPayReportDate;
        $countResults["ready"] = true;

        file_put_contents($ipayFile, HCU_JsonEncode($countResults));
        break;

      case "timetrack":

        $countResults = GetTimetrackCounts($regularReportDate, ""); // Counts are always gathered

        $countResults["sid"] = $sid;
        $countResults["regularReportDate"] = $regularReportDate;
        $countResults["ready"] = true;

        file_put_contents($timetrackFile, HCU_JsonEncode($countResults));
        break;

      default:
        throw new exception ("Child process not found.", 9);
        break;

    }

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

/**
 * function GatherBillingInfo($dbh, $sysenv, $staffId, $mode)
 * This is the starting part of the process.  It creates the start file.
 * On return of this data call, the children function is called per count type.
 *
 * @param $dbh -- the database connection
 * @param $sysenv -- the environment variables (from cu_top)
 * @param $staffId -- the HomeCU login user
 * @param $mode --
 *    "odyssey" if using localhost and needed to get contract info from my.homecu.net.
 *    "regular" to get contract info from same database.
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 * @return $type -- "gather" where we are at in the process.
 * @return $sid -- the number "U" in UTC when the process was started.
 * @return $callMy -- "Y" if using localhost and needed to get contract info from my.homecu.net.
 * @return $reportDate -- the report date requested.  Value is passed through to the next step.
 * @return $cutoffDate -- the cutoff date requested.  Value is passed through to the next step.
 * @return $cutoffTime -- the cutoff time requested.  Value is passed through to the next step.
 */
function GatherBillingInfo($dbh, $sysenv, $staffId, $mode) {
  try {

    extract ($sysenv["BILL"]);

    $monthFormat = "Y-m-d|";
    $timeFormat = "H:i:s|";

    if (!isset($reportDate) || trim($reportDate) == "") {
      throw new exception ("Month is required.", 4);
    }
    if (!isset($cutoffDate) || trim($cutoffDate) == "") {
      throw new exception ("Cutoff Date is required.", 5);
    }
    if (!isset($cutoffTime) || trim($cutoffTime) == "") {
      throw new exception ("Cutoff time is required.", 6);
    }

    if (!DateTime::createFromFormat($monthFormat, $reportDate)) {
      throw new exception ("Month is invalid.", 1);
    }
    if (!DateTime::createFromFormat($monthFormat, $cutoffDate)) {
      throw new exception ("Cutoff date is invalid.", 2);
    }
    if (!DateTime::createFromFormat($timeFormat, $cutoffTime)) {
      throw new exception ("Cutoff time is invalid.", 3);
    }

    // Remove ALL temp files.
    $startFile = GetStartFile();
    $contractFile = GetContractFile();
    $estatementFile = GetEstatementCountFile();
    $ivrFile = GetIVRCountFile();
    $ipayFile = GetIPayCountFile();
    $timetrackFile = GetTimeTrackCountFile();

    if (file_exists($startFile)) {
      throw new exception ("Another run billing process is running.", 9);
    }

    $sid = new DateTimeZone("UTC");
    $sid = new DateTime("now", $sid);
    $sid = $sid->format("U");

    // Instead of removing files and checking for existence, use ready variable.
    $defaultString = HCU_JsonEncode(array("sid" => $sid, "ready" => false));

    file_put_contents($contractFile, $defaultString);
    file_put_contents($estatementFile, $defaultString);
    file_put_contents($ivrFile, $defaultString);
    file_put_contents($ipayFile, $defaultString);
    file_put_contents($timetrackFile, $defaultString);

    $fileArray = array("sid" => $sid, "reportDate" => $reportDate, "cutoffDate" => $cutoffDate, "cutoffTime" => $cutoffTime);
    file_put_contents($startFile, HCU_JsonEncode($fileArray));

    $returnArray = array("status" => "000", "error" => "", "sid" => $sid, "type" => "gather", "callMy" => $mode === "odyssey" ? "Y" : "N",
                         "reportDate" => $reportDate, "cutoffDate" => $cutoffDate, "cutoffTime" => $cutoffTime);
  } catch (exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "type" => "gather");
  }
  return $returnArray;
}

/**
 * function CheckBillingInfo($dbh, $sysenv, $staffId)
 * Checks to see that all the count files have been created.
 *
 * @param $dbh -- the database connection
 * @param $sysenv -- the environment variables (from cu_top)
 * @param $staffId -- the HomeCU login user
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 * @return $type -- "check" where we are at in the process.
 * @return $sid -- the number "U" in UTC when the process was started.
 * @return $ready -- true if all the required files have been created.  False otherwise.
 */
function CheckBillingInfo($dbh, $sysenv, $staffId) {
  try {

    $sid = $sysenv["BILL"]["sid"];
    if (!isset($sid) || trim($sid) == "") {
      throw new exception ("Sid is required.", 1);
    }

    $results = VerifyFiles($sysenv);
    $ready = $results["status"] === "000";

    $returnArray = array("status" => "000", "error" => "", "sid" => $sid, "ready" => $ready, "type" => "check");

  } catch (exception $e) {
    $sysenv["logger"]->error("Run billing failed: " . $e->getMessage());
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "type" => "check");
  }
  return $returnArray;
}

/**
 * function RunBillingCleanup($dbh, $sysenv, $staffId)
 * This removes the start file after completion.
 * If it isn't devmode, it also removes the count files.
 *
 * @param $dbh -- the database connection
 * @param $sysenv -- the environment variables (from cu_top)
 * @param $staffId -- the HomeCU login user
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 * @return $type -- "cleanup" where we are at in the process.
 */
function RunBillingCleanup($dbh, $sysenv, $staffId) {
  try {
    extract ($sysenv["BILL"]);

    if (!isset($sid) || trim($sid) == "") {
      throw new exception ("Sid is required.", 1);
    }

    $startFile = GetStartFile();
    $contractFile = GetContractFile();
    $estatementFile = GetEstatementCountFile();
    $ivrFile = GetIVRCountFile();
    $ipayFile = GetIPayCountFile();
    $timetrackFile = GetTimeTrackCountFile();

    $startOnly = $sysenv["devmode"] !== 1; // Want to see what is going on on devmode.

    if (file_exists($startFile)) {
      unlink($startFile);
    }

    if (!$startOnly) {
      if (file_exists($contractFile)) {
        unlink($contractFile);
      }

      if (file_exists($estatementFile)) {
        unlink($estatementFile);
      }

      if (file_exists($ivrFile)) {
        unlink($ivrFile);
      }

      if (file_exists($ipayFile)) {
        unlink($ipayFile);
      }

      if (file_exists($timetrackFile)) {
        unlink($timetrackFile);
      }
    }

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

/**
 * function VerifyFiles
 * This function ensures that:
 * the files exist, are ready, & have counts
 *
 * @param $sysenv -- the environment variables (from cu_top)
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 * @return $data -- all the counts from the files.
 */
function VerifyFiles($sysenv) {
  try {
    $startFile = GetStartFile();
    $contractFile = GetContractFile();
    $estatementFile = GetEstatementCountFile();
    $ivrFile = GetIVRCountFile();
    $ipayFile = GetIPayCountFile();
    $timetrackFile = GetTimeTrackCountFile();
    $data = array();

    extract ($sysenv["BILL"]);

    if (!isset($sid) || trim($sid) == "") {
      throw new exception ("Sid is required.", 1);
    }

    if (!file_exists($startFile)) {
      throw new exception ("Start file does not exist.", 2);
    }

    $contents = file_get_contents($startFile);
    if ($contents === false) {
      throw new exception ("Contents do not exist in start file.", 3);
    }
    $contents = HCU_JsonDecode($contents);

    $testSid = HCU_array_key_value("sid", $contents);

    if ($testSid !== $sid) {
      throw new exception ("Sid doesn't match in start file.", 4);
    }

    $reportDate = HCU_array_key_value("reportDate", $contents);
    if ($reportDate === false) {
      throw new exception ("ReportDate is not found.", 20);
    }

    $data["reportDate"] = $reportDate;

    if (!file_exists($contractFile)) {
      throw new exception ("Contract file doesn't exist.", 5);
    }

    $contents = file_get_contents($contractFile);
    if ($contents === false) {
      throw new exception ("Contract file doesn't exist.", 6);
    }
    $contents = HCU_JsonDecode($contents);

    $testSid = HCU_array_key_value("sid", $contents);

    if ($testSid !== $sid) {
      throw new exception ("Sid doesn't match in contract file.", 7);
    }

    $contractResults = HCU_array_key_value("contracts", $contents);
    $contractResults = $contractResults === false ? array() : $contractResults;
    $membershipCounts = HCU_array_key_value("membershipCounts", $contractResults);
    $membershipCounts = $membershipCounts === false ? array() : $membershipCounts;
    $contractResults = HCU_array_key_value("record", $contractResults);
    $contractResults = $contractResults === false ? array() : $contractResults;

    $data["contractResults"] = $contractResults;
    $data["membershipCounts"] = $membershipCounts;

    if (!file_exists($estatementFile)) {
      throw new exception ("eStatement file doesn't exist.", 8);
    }

    $contents = file_get_contents($estatementFile);
    if ($contents === false) {
      throw new exception ("eStatement file doesn't exist.", 9);
    }
    $contents = HCU_JsonDecode($contents);

    $testSid = HCU_array_key_value("sid", $contents);

    if ($testSid !== $sid) {
      throw new exception ("Sid doesn't match in eStatement file.", 10);
    }

    $ready = HCU_array_key_value("ready", $contents);
    if (!$ready) {
      throw new exception ("eStatements are not ready.", 22);
    }

    $eStatementCounts = HCU_array_key_value("counts", $contents);
    $eStatementCounts = $eStatementCounts === false ? array() : $eStatementCounts;

    $data["eStatementCounts"] = $eStatementCounts;

    if (!file_exists($ivrFile)) {
      throw new exception ("IVR file doesn't exist.", 11);
    }

    $contents = file_get_contents($ivrFile);
    if ($contents === false) {
      throw new exception ("IVR file doesn't exist.", 12);
    }
    $contents = HCU_JsonDecode($contents);

    $testSid = HCU_array_key_value("sid", $contents);

    if ($testSid !== $sid) {
      throw new exception ("Sid doesn't match in IVR file.", 13);
    }

    $ready = HCU_array_key_value("ready", $contents);
    if (!$ready) {
      throw new exception ("IVRs are not ready.", 23);
    }

    $ivrCounts = HCU_array_key_value("counts", $contents);
    $ivrCounts = $ivrCounts === false ? array() : $ivrCounts;

    $data["ivrCounts"] = $ivrCounts;

    if (!file_exists($ipayFile)) {
      throw new exception ("iPay file doesn't exist.", 14);
    }

    $contents = file_get_contents($ipayFile);
    if ($contents === false) {
      throw new exception ("iPay file doesn't exist.", 15);
    }
    $contents = HCU_JsonDecode($contents);

    $testSid = HCU_array_key_value("sid", $contents);

    if ($testSid !== $sid) {
      throw new exception ("Sid doesn't match in iPay file.", 16);
    }

    $ready = HCU_array_key_value("ready", $contents);
    if (!$ready) {
      throw new exception ("iPays are not ready.", 24);
    }

    $iPayCounts = HCU_array_key_value("counts", $contents);
    $iPayCounts = $iPayCounts === false ? array() : $iPayCounts;

    $data["iPayCounts"] = $iPayCounts;

    if (!file_exists($timetrackFile)) {
      throw new exception ("Timetrack file doesn't exist.", 17);
    }

    $contents = file_get_contents($timetrackFile);
    if ($contents === false) {
      throw new exception ("Timetrack file doesn't exist.", 18);
    }
    $contents = HCU_JsonDecode($contents);

    $testSid = HCU_array_key_value("sid", $contents);

    if ($testSid !== $sid) {
      throw new exception ("Sid doesn't match in timetrack file.", 19);
    }

    $ready = HCU_array_key_value("ready", $contents);
    if (!$ready) {
      throw new exception ("Timetracks are not ready.", 25);
    }

    $timetrackCounts = HCU_array_key_value("counts", $contents);
    $timetrackCounts = $timetrackCounts === false ? array() : $timetrackCounts;

    $data["timetrackCounts"] = $timetrackCounts;


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

/**
 * function ExtractContractsAndCounts($sid)
 * This extracts the contracts and counts for processing.
 * After counts are extracted, they are put in an array by Credit Union.
 * All files are validated by checking the $sid saved in each file.  (It should be the same.)
 *
 * @param $sid -- the files need to match this $sid from the previous step of this process.
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 * @return $contractResults -- this is a list of records to loop through to run billing.
 * @return $counts -- a single array to check when processing different features of billing.
 * @return $reportDate -- the requested report date (comes from the initial call.)
 */
function ExtractContractsAndCounts($sysenv) {
  try {

    $results = VerifyFiles($sysenv);
    if ($results["status"] !== "000") {
      throw new exception ($results["error"], $results["status"]);
    }
    extract($results["data"]);

    // Combine counts like previous function GetCounts.
    $countByCU = array();
    foreach($contractResults as $record) {
        $cu = strtoupper(trim($record["cu"]));
        if (!isset($countByCU[$cu])) {
          $countByCU[$cu] = array("cuinfo" => array(), "eStatements" => array(), "ivr" => array(), "timetrack" => array(), "iPay" => array());
        }
    }

    foreach($membershipCounts as $record) {
      $cu = strtoupper(trim($record["cu"]));
      if (isset($countByCU[$cu])) {
        $countByCU[$cu]["cuinfo"] = $record;
      }
    }

    foreach($eStatementCounts as $key => $value) {
        $cu = strtoupper(trim($key));
        if (isset($countByCU[$cu])) {
          $countByCU[$cu]["eStatements"] = $value;
        }
    }

    foreach($ivrCounts as $record) {
        $cu = strtoupper(trim($record["cu"]));
        if (isset($countByCU[$cu])) {

          // Now, there could be multiple IVR records for the CU divided by the DNID.
          if (!array_key_exists("ivr", $countByCU[$cu])) {
            $countByCU[$cu]["ivr"] = array("undivided" => $record);
            unset($countByCU[$cu]["ivr"]["DNID"]);
          // Add up the values in the "undivided" record so that it functions the same as if the "ByDNID" parameter didn't exist in the report.
          } else {
            $undivided = HCU_array_key_value("undivided", $countByCU[$cu]["ivr"]);
            $undivided = $undivided === false ? array("calls" => 0, "minutes" => 0) : $undivided;
            $undivided["calls"] += $record["calls"];
            $undivided["minutes"] += $record["minutes"];
            $countByCU[$cu]["ivr"]["undivided"] = $undivided;
          }

          $aDNID = $record["DNID"];
          unset($record["DNID"]);
          $countByCU[$cu]["ivr"][$aDNID] = $record;
        }
    }

    foreach($iPayCounts as $cu => $record) {
      if (HCU_array_key_exists($cu, $cuIpayMinimumMap)) {
        $cuIpayRec = $cuIpayMinimumMap[$cu];
        $record["Monthly Minimum"] = HCU_array_key_exists("minimum", $cuIpayRec) ? intval($cuIpayRec["minimum"]) : 0;
        $record["Per Transaction"] = HCU_array_key_exists("transfee", $cuIpayRec) ? floatval($cuIpayRec["transfee"]) : 0;
      }
      $countByCU[$cu]["iPay"] = $record;
    }

    $countByCU["timetrack"] = $timetrackCounts;

    $returnArray = array("status" => "000", "error" => "", "contractResults" => $contractResults, "counts" => $countByCU, "reportDate" => $reportDate);
  } catch (exception $e) {
    $sysenv["logger"]->error("Run Billing Failed: " . $e->getMessage());
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }
  return $returnArray;
}

/**
 * function RunBillingMonth($dbh, $sysenv, $staffId)
 * This is the actual function that creates records
 * in cubillmonth and cubillpreinvoice tables.
 *
 * @param $dbh -- the database connection
 * @param $sysenv -- the environment variables (from cu_top)
 * @param $staffId -- the HomeCU login user
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 * @return $type -- "run" where we are at in the process.
 * @return $sid -- the number "U" in UTC when the process was started.
 */
function RunBillingMonth($dbh, $sysenv, $staffId) {
  try {

    $sid = HCU_array_key_value("sid", $sysenv["BILL"]);

    $results = ExtractContractsAndCounts($sysenv);
    if ($results["status"] !== "000") {
      throw new exception ("Run billing failed to extract values for contracts or counts.", 2);
    }

    $reportDate = $results["reportDate"];
    $counts = $results["counts"];
    $contractResults = $results["contractResults"];

    $results = StartProcess($dbh, $reportDate);
    if ($results["status"] !== "000") {
      throw new exception ("Process was not started successfully.", 7);
    }

    foreach($contractResults as $record) {

      $results = ProcessFeature($dbh, $reportDate, $record, $counts, $staffId);
      if ($results["status"] != "000") {
        throw new exception ($results["error"], 9);
      }
    }

    $results = ProcessTimetrack($dbh, $counts["timetrack"], $reportDate);
    if ($results["status"] !== "000") {
      throw new exception ("Processing timetrack failed.", 12);
    }

    // Update month if there is no lasterror in it.
    $sql = "update cubillmonth set processing_flag = 100, last_step_date = now()
            where billing_date = '" . prep_save($reportDate) . "'
            and (last_error_code is null or last_error_code = '') and (last_error_msg is null or last_error_msg = '')";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Update query failed.", 10);
    }

    // Update hooked issues to "Billed"
    $updateSQL = "update cutrack set billed = 'Y' where track_id in (select t.track_id from cutrack t
                inner join cubillpreinvoice pi on t.billing_feature_id = pi.feature_detail_id
                and pi.billing_date = '" . prep_save($reportDate) . "' and billing_part in (0,2))";
                // billing_part 1 means bill half before starting workflow so ignore this.
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Update query failed.", 11);
    }

    $returnArray = array("status" => "000", "error" => "", "sid" => $sid, "type" => "run");

  } catch (exception $e) {
    if ($e->getCode() != 2) {
      $sysenv["logger"]->error("Run Billing Failed: " . $e->getMessage());
    }
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "sid" => $sid, "type" => "run");
  }
  return $returnArray;
}

/**
 * function StartProcess($dbh, $reportDate)
 * This starts the process of running billing.
 * It creates the cubillmonth record
 * and removes previous cubillpreinvoice records for the month.
 *
 * @param $dbh -- the database connection
 * @param $reportDate -- the report date to save.
 */
function StartProcess($dbh, $reportDate) {
  try {

    if (!db_work ($dbh, HOMECU_WORK_BEGIN)) {
      throw new exception("begin query failed.", 1);
    }

    $sql = "insert into cubillmonth (billing_date, started_date, last_error_code, last_error_msg)
            select '$reportDate', now(), '', '' where not exists (select 'FOUND' from cubillmonth where billing_date = '" . prep_save($reportDate) . "')";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("cubillmonth insert failed.", 2);
    }

    // Delete the old preinvoices but only if QBWC is not running.
    $sql = "delete from cubillpreinvoice
            where billing_date = '" . prep_save($reportDate) . "'
            and not exists (select 'FOUND' from cubillmonth where billing_date = '" . prep_save($reportDate) . "' and processing_flag > 100)";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("cubillpreinvoice deletion failed.", 3);
    }

    $sql = "update cubillmonth set processing_flag = 10, last_step_date = now(), last_error_code = '', last_error_msg = ''
            where billing_date = '" . prep_save($reportDate) . "'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("cubillmonth update failed.", 4);
    }

    $sql = "update cubillfeaturedetail set processing_flag = 0 where end_date is null or end_date > '" . prep_save($reportDate) . "'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("cubillfeaturedetail update failed.", 5);
    }

    if (!db_work ($dbh, HOMECU_WORK_COMMIT)) {
      throw new exception("commit query failed.", 7);
    }

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

    db_work($dbh, HOMECU_WORK_ROLLBACK);
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }
  return $returnArray;
}

/**
 * function ProcessFeature($dbh, $reportDate, $record, $counts, $staffId)
 * This process a billing item based on the calcId of the record.
 *
 * @param $dbh -- the database connection
 * @param $reportDate -- the report date to save.
 * @param $record -- the record from the $contractResults array.
 * @param $counts -- the count array.
 * @param $staffId -- the HomeCU login user
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 * @return $warnings -- there are some features that give warnings.  This weren't shown on Mammoth.
 */
function ProcessFeature($dbh, $reportDate, $record, $counts, $staffId) {
  try {
    $cu = strtoupper(trim($record["cu"]));

    $membershipRec = HCU_array_key_value("cuinfo", $counts[$cu]);
    $membershipCount = $membershipRec === false || !HCU_array_key_exists("membership", $membershipRec) ? 0 : intval($membershipRec["membership"]);
    $assetCount = $membershipRec === false || !HCU_array_key_exists("assets", $membershipRec) ? 0 : floatval($membershipRec["assets"]);
    $eStatementCount = HCU_array_key_exists("eStatements", $counts[$cu]) ? intval($counts[$cu]["eStatements"]) : 0;
    $iPayCount = HCU_array_key_exists("iPay", $counts[$cu]) ? $counts[$cu]["iPay"] : array();
    $ivrCount = HCU_array_key_exists("ivr", $counts[$cu]) ? $counts[$cu]["ivr"] : array();

    // Expand billing template
    $template = HCU_JsonDecode($record["billingTemplate"]);

    unset($record["billingTemplate"]);

    $record["description"] = $template["descr"];
    $record["qty1"] = intval($template["qty1"]);
    $record["qty2"] = intval($template["qty2"]);
    $record["fixed"] = floatval($template["fixed"]);
    $record["variable"] = floatval($template["variable"]);
    $record["calcId"] = intval($record["calcId"]);
    $record["aDNID"] = HCU_array_key_exists("aDNID", $template) ? trim($template["aDNID"]) : "";

    $frequency = $record["frequency"];

    $alreadyAdded = false;

    $calcId = $record["calcId"];
    $salesItemId = $record["salesItemId"];

    $warnings = array();

    // Do not continue if QBWC is running on the same records.
    $results = ProcessFeatureStart($dbh, $reportDate, $record);
    if ($results["status"] !== "000") {
      if ($results["status"] < 0) {
        $warnings[] = $results["error"];
        throw new exception ($results["error"], -1);
      } else {
        throw new exception ("Process feature start failed.", 2);
      }
    }

    switch($calcId) {
        case 1:         // HB monthly
        case 51:        // Mobile web monthly
        case 61:        // Android monthly
        case 71:        // iPhone monthly
            $calcs = CalcMembership($membershipCount, $record);
            $results = ProcessFeatureEnd($dbh, $calcs["count"], $calcs["amount"], $reportDate, false, $record, $staffId, $frequency);
            if ($results["status"] !== "000") {
              throw new exception ($results["error"], 4);
            }
            break;
        case 5151:
            $results = ProcessFeatureEnd($dbh, $membershipCount, $record["variable"] * $membershipCount, $reportDate, false, $record, $staffId, $frequency);
            if ($results["status"] !== "000") {
              throw new exception ($results["error"], 5);
            }
            break;
        case 3:         // HB sunset
        case 4:         // HB sunset
            $calcs = CalcSunset($membershipCount, $record);
            if ($calcs["processIt"]) {
              $results = ProcessFeatureEnd($dbh, $calcs["count"], $calcs["amount"], $reportDate, false, $record, $staffId, $frequency);
              if ($results["status"] !== "000") {
                throw new exception ($results["error"], 6);
              }
            }
            break;
        case 52:        // Mobile web setup
        case 62:        // Android setup
        case 72:        // iPhone setup
        case 2:         // HB setup
        case 32:        // IVR setup
        case 41:        // Check image monthly
        case 42:        // Check image setup
        case 82:        // RDC setup
        case 92:        // Secure form setup
        case 102:       // Website dev setup
        case 112:       // TXT banking setup
        case 122:       // SSL Setup
        case 31:        // IVR monthly
        case 81:        // RDC monthly
        case 91:        // Secure form monthly
        case 101:       // Website monthly
        case 111:       // TXT banking monthly
        case 24: // Free eStatements
            $results = ProcessFeatureEnd($dbh, 1, $record["fixed"], $reportDate, false, $record, $staffId, $frequency);
            if ($results["status"] !== "000") {
              throw new exception ($results["error"], 7);
            }
            break;
        case 21:        // Estmnt tier 1
        case 22:        // Estmnt tier 2
        case 23:        // Estmnt tier 3
            $calcs = CalcEstatements($eStatementCount, $record);
            if ($calcs["warning"] != "") {
              $warnings[] = $calcs["warning"];
            }
            $results = ProcessFeatureEnd($dbh, $calcs["count"], $calcs["amount"], $reportDate, $calcs["dontAddRecord"], $record, $staffId, $frequency);
            if ($results["status"] !== "000") {
              throw new exception ($results["error"], 8);
            }
            break;
        case 33:        // IVR minutes
        case 34:          // IVR calls
        case 35:
        case 36:
            $calcs = CalcIVR($ivrCount, $record);
            $results = ProcessFeatureEnd($dbh, $calcs["count"], $calcs["amount"], $reportDate, false, $record, $staffId, $frequency);
            if ($results["status"] !== "000") {
              throw new exception ($results["error"], 9);
            }

            if ($record["calcId"] != 36) {
              $amount2 = $record["fixed"];
              $record["description"] .= $record["actualDescription"] . " Monthly Rate";

              $results = ProcessFeatureEnd($dbh, 1, $amount2, $reportDate, false, $record, $staffId, $frequency);
              if ($results["status"] !== "000") {
                throw new exception ($results["error"], 10);
              }
            }
            break;
        case 1001:        // email
        case 1002:        // premium email
        case 1003:        // email credit
            throw new exception ("Email is unused now.", -13);
            break;
        case 121:           // SSL Monthly
            $calcs = CalcSSL($assetCount, $record, $cu);
            if ($calcs["warning"] != "") {
              $warnings[] = $calcs["warning"];
            }
            if ($calcs["processIt"]) {
              $results = ProcessFeatureEnd($dbh, $calcs["count"], $calcs["amount"], $reportDate, false, $record, $staffId, $frequency);
              if ($results["status"] !== "000") {
                throw new exception ($results["error"], 11);
              }
            }

            break;
        case 200:
            $calcs = CalcConditionalMemb($membershipCount, $record);
            $results = ProcessFeatureEnd($dbh, $calcs["count"], $calcs["amount"], $reportDate, false, $record, $staffId, $frequency);
            if ($results["status"] !== "000") {
              throw new exception ($results["error"], 12);
            }
            break;
        case 2001:
        case 2002:
        case 2003:
        case 2004:
        case 2005:
        case 2006:
            // Prevent E_NOTICEs when CU is set up for Ipay but there are no counts.
            if (count($iPayCount) > 0) {
              $calcs = CalcIpay($iPayCount, $record);
              if ($calcs["processIt"]) {
                $results = ProcessFeatureEnd($dbh, $calcs["count"], $calcs["amount"], $reportDate, false, $record, $staffId, $frequency);
                if ($results["status"] !== "000") {
                  throw new exception ($results["error"], 6);
                }
              }
            }


            break;
        default:
            // this is an error
            throw new exception ("CalcId doesn't exist! (CALC ID: $calcId, SALES ITEM ID: $salesItemId)", 3);
            break;
    }

    $returnArray = array("status" => "000", "error" => "", "warnings" => $warnings);
  } catch (exception $e) {
    if ($e->getCode() < 0) {
      $returnArray = array("status" => "000", "error" => "", "warnings" => $warnings); // Stopped because of the warning but show it as a warning, not an error.
    } else {
        $sql = "update cubillmonth set last_step_date = now(), last_error_code = 'Error', last_error_msg = '" . prep_save($e->getMessage(), 250)
        . "' where billing_date = '" . prep_save($reportDate) . "'";
        $sth = db_query($sql, $dbh);
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "warnings" => $warnings);
    }
  }

  return $returnArray;
}

/**
 * function ProcessFeatureStart($dbh, $reportDate, $record)
 * This starts processing a feature.  It checks if QBWC is currently running on the record.
 * Then it updates the processing_flag of the feature detail record that is currently being billed.
 *
 * @param $dbh -- the database connection
 * @param $reportDate -- the report date to save.
 * @param $record -- the record from the $contractResults array.
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 */
function ProcessFeatureStart($dbh, $reportDate, $record) {
  try {
    $featureDetailId = $record["featureDetailId"];
    $cu = strtoupper(trim($record["cu"]));
    $warnings = array();

    $sql = "select pi.id from cubillpreinvoice pi
            where pi.processing_flag >= 10 and pi.processing_flag < 20 and pi.feature_detail_id = " . intval($featureDetailId) . "
              and upper(pi.cu_id) = '" . prep_save($cu, 10) . "' and pi.billing_date = '" . prep_save($reportDate) . "'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Select query failed.", 1);
    }

    if (db_num_rows($sth) > 0) {
      throw new exception ("QWBC is running for CU: $cu and feature detail: $featureDetailId", -2);
    }

    if (!db_work ($dbh, HOMECU_WORK_BEGIN)) {
      throw new exception("begin query failed.", 3);
    }

    $sql = "delete from cubillpreinvoice where feature_detail_id = " . intval($featureDetailId) . " and upper(cu_id) = '" . prep_save($cu, 10) . "'
            and billing_date = '" . prep_save($reportDate) . "'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Delete query failed.", 104);
    }

    $sql = "update cubillfeaturedetail set processing_flag = 1 where id = " . intval($featureDetailId);
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Update query failed.", 105);
    }

    if (!db_work ($dbh, HOMECU_WORK_COMMIT)) {
      throw new exception("commit query failed.", 106);
    }

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

/**
 * function ProcessFeatureEnd($dbh, $count, $amount, $reportDate, $alreadyAdded, $record, $staffId, $frequency)
 * This creates the preinvoice record knowing the count and amount values.
 *
 * @param $dbh -- the database connection
 * @param $count -- the calculated count (depending on the feature)
 * @param $amount -- the price per count
 * @param $reportDate -- the report date to save
 * @param $alreadyAdded -- if true, don't add the preinvoice.  (This function also updates to processing_flag of the billing item to 20 (processed).)
 * @param $record -- the record from the $contractResults array.
 * @param $staffId -- the HomeCU login user
 * @param $frequency -- some billing features are billed once or twice a year.  In this case, use the counts for this month and times by 12 or 6.
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 */
function ProcessFeatureEnd($dbh, $count, $amount, $reportDate, $alreadyAdded, $record, $staffId, $frequency) {
  try {
    $featureDetailId = $record["featureDetailId"];
    $addendum = DateTime::createFromFormat("Y-m-d", $reportDate);
    $addendum = $addendum->format("M Y");
    $cu = trim(strtoupper($record["cu"]));
    $description = trim($record["description"]);
    $billingItem = $record["billingId"];
    $salesItemId = $record["salesItemId"];
    $billHalf = $record["billHalf"] == "Y";
    $billingStatus = intval($record["billingStatus"]);
    $partiallyBilledDate = $record["partiallyBilledDate"];

    if (!$alreadyAdded) {


      if ($billHalf) { // In the case of 2 where the workflow is completed before the end of the month, it is like billing full.
        switch($billingStatus) {
            case 0: // First part billed
            case 4: // Even if no workflow exists, still bill for half.
              // TODO: error or no error that is the question.
              if($partiallyBilledDate == "" || $partiallyBilledDate != $reportDate) {
                throw new exception ("Do not create record.", -1);
              }

              $description = $record["actualDescription"] . " (50% deposit)";

              $amount = $amount / 2.0;
              $insertSQL = "insert into cubillpreinvoice (feature_detail_id, sales_item_id, billing_system_id, cu_id, billing_date, description,
                              quantity, amount, billing_part)
                            values (" . intval($featureDetailId) . ", " . intval($salesItemId) . ", '" . prep_save($billingItem, 20)
                            . "', '" . prep_save($cu, 10) . "', '" . prep_save($reportDate) . "', '" . prep_save($description, 100)
                            . "', " . floatval($count) . ", '" . floatval($amount) . "', 1)";
              $sth = db_query($insertSQL, $dbh);
              if (!$sth) {
                throw new exception ("Insert query failed.", 2);
              }

              $updateFeatureToPendingSQL =  "update cubillfeaturedetail set billing_status = " . ($billingStatus == 0 ? "1" : "5")
              . " where id = " . intval($featureDetailId);
              $sth = db_query($updateFeatureToPendingSQL, $dbh);
              if (!$sth) {
                throw new exception ("Update query failed.", 3);
              }

              break;
            case 3: // workflow completed before billed partially
                $insertSQL = "insert into cubillpreinvoice (feature_detail_id, sales_item_id, billing_system_id, cu_id, billing_date,
                                description,  quantity, amount)
                              values (" . intval($featureDetailId) . ", " . intval($salesItemId) . ", '" . prep_save($billingItem, 20)
                              . "', '" . prep_save($cu, 10) . "', '" . prep_save($reportDate) . "', '" . prep_save($description, 100)
                              . "', " . floatval($count) . ", '" . floatval($amount) . "')";
                $sth = db_query($insertSQL, $dbh);
                if (!$sth) {
                  throw new exception ("Insert query failed.", 4);
                }
                break;
              case 2: // remainder
                $findSQL = "select amount from cubillpreinvoice where feature_detail_id = " . intval($featureDetailId) . " and billing_part = 1";
                $sth = db_query($findSQL, $dbh);
                if (!$sth) {
                  throw new exception ("Find query failed.", 5);
                }
                $findAmount = db_fetch_row($sth, 0)[0];
                $amount = $amount - floatval($findAmount);

                $description = $record["actualDescription"] . " (remainder)";
                $insertSQL = "insert into cubillpreinvoice (feature_detail_id, sales_item_id, billing_system_id, cu_id, billing_date,
                                description,  quantity, amount, billing_part)
                              values (" . intval($featureDetailId) . ", " . intval($salesItemId) . ", '" . prep_save($billingItem, 20)
                              . "', '" . prep_save($cu, 10) . "', '" . prep_save($reportDate) . "', '" . prep_save($description, 100)
                              . "', " . floatval($count) . ", '" . floatval($amount) . "', 2)";
                $sth = db_query($insertSQL, $dbh);
                if (!$sth) {
                  throw new exception ("Insert query failed.", 6);
                }
                break;
            }
      } else {
        if ($billingStatus != 4 && $billingStatus != 0) {
          switch($frequency) {
            case 1:
              $count *= 6;
              $amount *= 6;
              break;
            case 2:
              $count *= 12;
              $amount *= 12;
              break;
          }

          if ($record["productName"] == "ESP" && $description == "") {
            $description = $record["actualDescription"] . " ($addendum)";
          }
          $insertSQL = "insert into cubillpreinvoice (feature_detail_id, sales_item_id, billing_system_id, cu_id, billing_date, description,  quantity, amount)
                        values (" . intval($featureDetailId) . ", " . intval($salesItemId) . ", '" . prep_save($billingItem, 20)
                        . "', '" . prep_save($cu, 10) . "', '" . prep_save($reportDate) . "', '" . prep_save($description, 100)
                        . "', " . floatval($count) . ", '" . floatval($amount) . "')";
          $sth = db_query($insertSQL, $dbh);
          if (!$sth) {
            throw new exception ("Insert query failed.", 7);
          }
      }

      if ($partiallyBilledDate != "") {
            $dateTime = new DateTime($partiallyBilledDate);
            $dateFormat = $dateTime->format("m/d/Y");
            $description = "Credit paid $dateFormat";
            $amount = 0 - floatval($record["partiallyBilledAmount"]);

            $insertSQL = "insert into cubillpreinvoice (feature_detail_id, sales_item_id, billing_system_id, cu_id, billing_date, description,  quantity, amount)
                          values (" . intval($featureDetailId) . ", " . intval($salesItemId) . ", '" . prep_save($billingItem, 20)
                          . "', '" . prep_save($cu, 10) . "', '" . prep_save($reportDate) . "', '" . prep_save($description, 100)
                          . "', 1, '" . floatval($amount) . "')";
            $sth = db_query($insertSQL, $dbh);
            if (!$sth) {
              throw new exception ("Insert query failed.", 8);
            }

        }
      }
    }

    $setupRecurring = getSetupRecurring();
    $additionalUpdate = ($billingStatus == 4 ? ", billing_status = 5" : "");
    $processSQL = "update cubillfeaturedetail set processing_flag = 20 $additionalUpdate where id = " . intval($featureDetailId);
    $sth = db_query($processSQL, $dbh);
    if (!$sth) {
      throw new exception ("Update query failed.", 9);
    }

    $returnArray = array("status" => "000", "error" => "");

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

/**
 * function CalcMembership($membershipCount, $record)
 * Calculate using the membership calculation.
 *
 * @param $membershipCount -- number of members for the CU.
 * @param $record -- the record from the $contractResults array.
 *
 * @return $amount -- the amount to charge.
 * @return $count -- the number to charge.
 */
function CalcMembership($membershipCount, $record) {
    $calcCount = $membershipCount;
    $count = 1;

    // the count is the number of members; formula is fixed up to qty1, plus variable for groups of qty2 over qty1.
    $amount = $record["fixed"];
    if ( $record["qty1"] > 0 ) {
      $variableAmount = (int) ceil( ( $calcCount - $record["qty2"] ) / $record["qty1"] );
    } else {
      $variableAmount = 0;
    }

    if ( $variableAmount > 0 ) {
      $amount += $variableAmount * $record["variable"];
    }

    return array("amount" => $amount, "count" => $count);
}

/**
 * function CalcSunset($membershipCount, $record)
 * Calculate using the sunset calculation.
 *
 * @param $membershipCount -- number of members for the CU.
 * @param $record -- the record from the $contractResults array.
 *
 * @return $amount -- the amount to charge.
 * @return $count -- the number to charge.
 */
function CalcSunset($membershipCount, $record) {
  try {
    if ($record["calcId"] == 3) { // (lower)
        $calcCount = $membershipCount;
        $count = $calcCount;
        if ( $calcCount > 2500 ) {
          throw new exception ("Don't process it", 1);
        }

        // HB sunset, membership < cutoff
        // straight variable calculation up to an amount
        if ( $count < $record["qty1"] ) {
          $amount = $record["variable"] * $count;
        } else {
          $amount = 0;
        }
    } else { // 4 (upper)
        $calcCount = $membershipCount;
        $count = $calcCount;
        if ( $calcCount <= 2500 ) {
          throw new exception ("Don't process it", 2);
        }

        // HB sunset, membership >= cutoff
        // this is like regular home banking; qty2 is both the cutoff and the grouping
        $amount = $record["fixed"];
        if ( $count >= $record["qty1"] && $record["qty1"] != 0) {
          $variableAmount = (int) ceil( ( $count - $record["qty2"] ) / $record["qty1"] );
        } else {
          $amount = 0;
        }

        if ( $variableAmount > 0 ) {
          $amount += $variableAmount * $record["variable"];
        }
    }

    $returnArray = array("processIt" => true, "amount" => $amount, "count" => $count);
  } catch (exception $e) {
    $returnArray = array("processIt" => false);
  }

  return $returnArray;

}

/**
 * function CalcEstatements($eStatementCount, $record)
 * Calculate using the tiered estatement calculation
 *
 * @param $eStatementCount -- number of eStatements for the CU.
 * @param $record -- the record from the $contractResults array.
 *
 * @return $amount -- the amount to charge.
 * @return $count -- the number to charge.
 */
function CalcEstatements($eStatementCount, $record) {
   $count = intval($eStatementCount);
   $bottom = intval($record["qty1"]);
   $top = intval($record["qty2"]);
   $cu = $record["cu"];
   $calcID = $record["calcId"];
   $rate = floatval($record["variable"]);

   $warning = "";

   $dontAddRecord = false;
   $range = $top - $bottom;
   if ($top != -1 && $count >= $range + $bottom) {
      $count = $range;
      if ($bottom != 0) {
        $count++;
      }
   } else if ($count < $bottom) {
      $warning = "No record created at this level of eStatements because there isn't enough count. (CALC ID: $calcID, cu: $cu)";
      $dontAddRecord = true;
      $count = 0;
   } else {
      $count = $count - $bottom;
      if ($bottom != 0) {
        $count++;
      }
   }
   $amount = $count * $rate;

   return array("amount" => $amount, "count" => $count, "dontAddRecord" => $dontAddRecord, "warning" => $warning);
}

/**
 * function CalcIVR($ivrCounts, $record)
 * Calculate using the IVR calculation.
 *
 * @param $ivrCounts -- holds both the number of calls and the total call duration for the CU.
 * @param $record -- the record from the $contractResults array.
 *
 * @return $amount -- the amount to charge.
 * @return $count -- the number to charge.
 */
function CalcIVR($ivrCounts, $record) {
    // Check to see if there is a DNID in the record.  If there is, then get that count.  Otherwise, use the undivided record.

    $ivrCounts = $record["aDNID"] != "" ? (HCU_array_key_exists($record["aDNID"], $ivrCounts) ? $ivrCounts[$record["aDNID"]] : array()) :
        (HCU_array_key_exists("undivided", $ivrCounts) ? $ivrCounts["undivided"] : array());
    $ivrCountMinutes = HCU_array_key_exists("minutes", $ivrCounts) ? $ivrCounts["minutes"] : 0;
    $ivrCountCalls = HCU_array_key_exists("calls", $ivrCounts) ? $ivrCounts["calls"] : 0;

    switch($record["calcId"]) {
      case 35:
        $count = $ivrCountMinutes > $record["qty1"] ? $record["qty1"] : $ivrCountMinutes;
        $amount1 = $record["variable"] * $count;
        break;
      case 36:
        $count = $ivrCountMinutes > $record["qty1"] ? $ivrCountMinutes - $record["qty1"] : 0;
        $amount1 = $record["variable"] * $count;
        break;
      case 33:
        $count = $ivrCountMinutes;
        $amount1 = $record["variable"] * $count;
        break;
      case 34:
        $count = $ivrCountCalls;
        $amount1 = $record["variable"] * $count;
        break;
    }

    return array("count" => $count, "amount" => $amount1);
}

/**
 * function ProcessTimetrack($dbh, $timetrackCount, $reportDate)
 * Calculate using the Timetrack calculation.
 * This is different because timetrack records are created from the counts.
 * There are no contract details that specify timetrack.
 * The sales item for timetrack tells the process the regular and rush job rates.
 *
 * @param $dbh -- the database connection.
 * @param $timetrackCount -- the counts from timetrack.
 * @param $reportDate -- the report date to record.
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 */
function ProcessTimetrack($dbh, $timetrackCount, $reportDate) {
  try {

    $salesItemId = 65;
    $sql = "select billing_template from cubillsalesitem where id = " . intval($salesItemId);
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Select query failed.", 1);
    }

    $timetrackTemplate = db_num_rows($sth) > 0 ? db_fetch_row($sth, 0)[0] : "";
    if ($timetrackTemplate == "") {
      $regularRate = 85.0;
      $rushRate = 127.5;
    } else {
      $timetrackTemplate = HCU_JsonDecode($timetrackTemplate);
      $regularRate = floatval($timetrackTemplate["fixed"]);
      $rushRate = floatval($timetrackTemplate["variable"]);
    }

    foreach($timetrackCount as $cu => $timetrackRecords) {
      $cu = trim(strtoupper($cu));
      $sql = "insert into cubillpreinvoice (feature_detail_id, sales_item_id, billing_system_id, cu_id, billing_date, description,  quantity, amount) values ";
      $sqlParts = array();

      $sqlValid = false;

      $timetrackBillingItems = getTimetrackBillingItems();
      foreach($timetrackRecords as $counts) {

        $staffName = $counts["staff"];

        if ($staffName == "test") {
          continue; // Do not bill for the test user.
        }

        $billAmount = floatval($counts["billable"]) * floatval($regularRate);
        $rushAmount = floatval($counts["rushable"]) * floatval($rushRate);
        $billCount = floatval($counts["billable"]);
        $rushCount = floatval($counts["rushable"]);

        // Get the actual description for the rush description (If there isn't any entry in the configuration,
        // then it is HTML/Support but different people have different codes.)
        $staffKey = strtolower($staffName);
        $billingItemDescription = HCU_array_key_value($staffKey, $timetrackBillingItems);
        $actualDescription = $billingItemDescription !== false ? trim($billingItemDescription) : "";
        $rushDescription = "$actualDescription (Rush)";

        if($billAmount > 0) {
            $sqlParts[] = "(0, " . intval($salesItemId) . ", '" . prep_save($staffName, 20) . "', '" . prep_save($cu, 10)
            . "', '" . prep_save($reportDate) . "', '', " . floatval($billCount) . ", " . floatval($billAmount) . ")";
            $sqlValid = true;
        }

        if ($rushAmount > 0) {
            $sqlParts[] = "(0, " . intval($salesItemId) . ", '" . prep_save($staffName, 20) . "', '" . prep_save($cu, 10)
            . "', '" . prep_save($reportDate) . "', '" . prep_save($rushDescription, 100) . "', " . floatval($rushCount) . ", " . floatval($rushAmount) . ")";
            $sqlValid = true;
        }
      }

      if ($sqlValid) {
        $sql .= implode(",", $sqlParts);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
          throw new exception ("Insert query failed.", 2);
        }
      }
    }

    $returnArray = array("status" => "000", "error" => "");

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

/**
 * function CalcSSL($assetCount, $record, $cu)
 * Calculate using the SSL calculation.
 *
 * @param $assetCount -- how much assets the CU has.
 * @param $record -- the record from the $contractResults array.
 * @param $cu -- the Credit Union.  Used for the warning.
 *
 * @param $processIt -- if true, then create the cubillpreinvoice record.
 * @return $amount -- the amount to charge.
 * @return $count -- the number to charge.
 * @return $warning -- "" if there is no warning, nonempty otherwise.
 */
function CalcSSL($assetCount, $record, $cu) {
    $rangeFrom = floatval($record["qty1"]);
    $rangeTo = floatval($record["qty2"]);
    $cu = $record["cu"];
    $warning = "";
    $count = 1;
    $amount = $record["fixed"];
    $processIt = false;

    // Make sure that it is in range.  -1 is positive infinity for the second number
    if ($assetCount >= $rangeFrom && ($rangeTo == -1 || $assetCount < $rangeTo)) {
        $processIt = true;
    } else {
        if ($rangeTo != -1) {
          $warning = "SSL record not in range: $rangeFrom to $rangeTo for CU: $cu";
        } else {
          $warning = "SSL record not created because it is less than $rangeFrom for CU: $cu";
        }
    }

    return array("processIt" => $processIt, "count" => $count, "amount" => $amount, "warning" => $warning);
}

/**
 * function CalcIpay($iPayCount, $record)
 * Calculate using the iPay calculation.
 *
 * @param $iPayCount -- all the iPay counts.
 * @param $record -- the record from the $contractResults array.
 *
 * @param $processIt -- if true, then create the cubillpreinvoice record.
 * @return $amount -- the amount to charge.
 * @return $count -- the number to charge.
 */
function CalcIpay($iPayCount, $record) {
    $variableAmount = floatval($record["variable"]);
    $fixedAmount = floatval($record["fixed"]);
    $calcId = intval($record["calcId"]);

    switch($calcId) {
        case 2001: // This is the per transaction.  Only process this if it is greater than the minimum.
            $count = intval($iPayCount["Total Transactions Plus"]);
            $amount = $variableAmount == $iPayCount["Per Transaction"] ? $variableAmount * $count : 0;
            $amount = $amount < $iPayCount["Monthly Minimum"] ? 0 : $amount;
            break;
        case 2002:
            $count = intval($iPayCount["Stop Payment Count"]);
            $amount = $variableAmount * $count;
            break;
        case 2003:
            $count = 0; // Letter Mailed Express Mail doesn't show up in iPay file.  If/when it does, add it back in for the count.
            $amount = 0;
            break;
        case 2004:
            $count = intval($iPayCount["NSF Count"]);
            $amount = $variableAmount * $count;
            break;
        case 2005:
            $count = intval($iPayCount["Check Copies Count"]);
            $amount = $variableAmount * $count;
            break;
        case 2006: // This is the Monthly Minimum.  Only process this if the amount from transaction is less than the amount.
            $count = 1;
            $amount = floatval($iPayCount["Per Transaction"]) * intval($iPayCount["Total Transactions Plus"]);
            $amount = $fixedAmount == $iPayCount["Monthly Minimum"] ? ($amount < $fixedAmount ? $fixedAmount : 0) : 0;
            break;

    }

    $processIt = $amount > 0;

    return array("processIt" => $processIt, "count" => $count, "amount" => $amount);
}

/**
 * function CalcConditionalMemb($membershipCount, $record)
 * Calculate using this calculation.
 *
 * @param $membershipCount -- number of members for the CU.
 * @param $record -- the record from the $contractResults array.
 *
 * @param $processIt -- if true, then create the cubillpreinvoice record.
 * @return $amount -- the amount to charge.
 * @return $count -- the number to charge.
 */
function CalcConditionalMemb($membershipCount, $record) {
    $memberDivide = floatval($record["qty1"]);
    $ifLess = floatval($record["fixed"]);
    $ifMore = floatval($record["variable"]);
    $cu = $record["cu"];

    // Make sure that it is in range.  -1 is positive infinity for the second number
    $value = $membershipCount <= $memberDivide ? $ifLess : $ifMore;

    return array("count" => 1, "amount" => $value);
}

/**
 * function GetEstatementCounts($cutoffDate, $cutoffTime, $homecu_dev_oauth2_proxy)
 * Gets the eStatement counts from www3, www5, www6, and my.homecu.net.
 * Then it aggregates the data into one array.
 *
 * @param $cutoffDate -- the cutoff date (get everything after this date)
 * @param $cutoffTime -- the cutoff time (get everything after this time on the cutoff date)
 * @param $devmode -- the devmode
 *
 * @return array -- the eStatement counts
 */
function GetEstatementCounts($cutoffDate, $cutoffTime, $devmode) {

  try {
    // set a parameter string with the cutoff date and time
    $q = "cdate=$cutoffDate&ctime=$cutoffTime";

    // list of servers to check
    $odysseyServer = 'my';
    $servers = array('www3','www5','www6', $odysseyServer);
    $cuList = array();
    foreach ($servers as $ask) {

      $isOdyssey = $ask == $odysseyServer;
      if ($isOdyssey) {
        $cmd = "https://$ask.homecu.net/monitor/cgi-bin/stmntcount.mp?csv=raw&$q";
      } else {
        $cmd = "https://$ask.homecu.net/hcuadm/stmntcount.pl?csv=raw&$q";
      }

      $results = $isOdyssey ? RunCurlOdyssey($cmd, $devmode) : RunCurlMammoth($cmd);
      switch ($results["httpCode"]) {
        case 504:
        case 0:
          throw new exception ("Server $ask timed out.", 1);
          break;
        case 200:
          $rawresp = $results["rawresp"];
          break;
        default:
          throw new exception ("Status not found.", 2);
          break;
      }

      $first = true;
      $second = false;

      if ($rawresp) {
        $lines = explode("\r\n",$rawresp);

        foreach($lines as $line) {
          if (trim($line) != "") {
            if ($first) {
              $first = false;
              $second = true;
              continue;
            }
            if ($second) {
              $second = false;
              continue;
            }

            $explodedLine = explode("\t", $line);

            if ($explodedLine[0] != "s") {
              continue;
            }

            $cu = strtolower(trim($explodedLine[1]));
            $count = intval($explodedLine[2]);

            // In case CU has counts on several servers.
            if (isset($cuList[$cu])) {
              $cuList[$cu] += $count;
            } else {
              $cuList[$cu] = $count;
            }
          }
        }
      }
    }

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

  return $returnArray;
} // end GetEstatementCounts

/**
 * function GetIVRCounts($reportStartDate, $reportEndDate)
 * Gets the IVR counts from ivr.homecu.net.
 *
 * @param $reportStartDate -- the beginning date of the range.
 * @param $reportEndDate -- the ending date of the range.
 *
 * @return array -- the IVR counts
 */
function GetIVRCounts($reportStartDate, $reportEndDate) {

  try {
    $cmd = "http://ivr.homecu.net/hcuadm/ivr_report?rv=RAW&byDNID=Y&reportstartdate=$reportStartDate&reportenddate=$reportEndDate";


    $results = RunCurlMammoth($cmd);
    switch ($results["httpCode"]) {
      case 504:
      case 0:
        throw new exception ("IVR server timed out.", 1);
        break;
      case 200:
        $rawresp = $results["rawresp"];
        break;
      default:
        throw new exception ("Status not found.", 2);
        break;
    }

    $cuList = array();
    $first = true;
    if ($rawresp) {
      $lines = explode("\r\n",$rawresp);
      foreach($lines as $line) {
          if (trim($line) != "") {
            // first line is the column headers
            if ($first) {
              $first = false;
              continue;
            }

            $explodedLine = explode(",", $line);

            // skip any "Total" lines
            if ( $explodedLine[0] == "T" ) {
              continue;
            }

            $cuList[] = array( "cu" => trim($explodedLine[0]), "DNID" => $explodedLine[1], "calls" => $explodedLine[2], "minutes" => $explodedLine[3] );
          }
      }
    }

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

  return $returnArray;
} // end GetIVRCounts

/**
 * function GetIPayCounts($iPayReportDate, $homecu_dev_oauth2_proxy)
 * This function returns a curl call to the miPayReport.prg page to get the counts for iPay.
 *
 * @param $iPayReportDate -- date in the YYYY_mm format to get the CSV.
 * @param $devmode -- the devmode
 *
 * @return $cuList: map of CU and the record in the CSV file.
 */
function GetIPayCounts($iPayReportDate, $devmode) {

  try {
    $cmd = "https://my.homecu.net/hcuadm/miPayReport.prg?operation=csv&month=$iPayReportDate";


    $results = RunCurlOdyssey($cmd, $devmode);
    switch ($results["httpCode"]) {
      case 504:
      case 0:
        throw new exception ("Monitor server timed out.", 1);
        break;
      case 200:
        $rawresp = $results["rawresp"];
        break;
      default:
        throw new exception ("Status not found.", 2);
        break;
    }

    $cuList = array();
    if ($rawresp) {
      $lines = explode("\n",$rawresp);
      $first = true;
      $header = array();
      foreach($lines as $line) {

          if (trim($line) == "") {
            continue;
          }
          // first line is the column headers
          if ($first) {
              $first = false;
              $header = str_getcsv($line);
              continue;
          }

          $parts = str_getcsv($line);

          // skip any "Error" lines
          if ( $parts[0] == "E") {
              continue;
          }

          $parts = array_combine($header, $parts);

          $cuList [$parts["CU Code"]] = $parts;
      }
    }

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

  return $returnArray;
} // end GetEmailCounts

/**
 * function GetTimetrackCounts($reportDate, $endDate)
 * Get the counts from timetrack.
 *
 * @param $reportDate -- the beginning date of the report range.
 * @param $endDate -- the ending date of the report range.
 *
 * @return array -- the timetrack counts.
 */
function GetTimetrackCounts($reportDate, $endDate) {

  try {
    $url = "https://www.homecu.net/timetrack/main.php?mainAction=report&month=$reportDate";

    $url .= $endDate == "" ? "" : "&endmonth=$endDate";

    $results = RunCurlMammoth($url);
    switch ($results["httpCode"]) {
      case 504:
      case 0:
        throw new exception ("Monitor server timed out.", 1);
        break;
      case 200:
        $raw = $results["rawresp"];
        break;
      default:
        throw new exception ("Status not found.", 2);
        break;
    }

    $first = true;
    $counts = array();

    if ($raw != "") {
      // CU,Staff Name,Rush Hours,Bill Hours
      foreach(explode("\r\n", $raw) as $line) {
        $lineParts = explode(",", $line);
        if ($lineParts[0] == "ERROR") {
          $errors[] = $lineParts[1];
        } else if ($first) { // Skip over column header.
          $first = false;
        } else { // Here is the meat
          $cu = strtolower(trim($lineParts[0]));
          if ($cu == "") {
            continue; // There is a line with no CU and staff = Kim.  This is not going to relate to anything.
          }
          if (!isset($counts[$cu])) {
            $counts[$cu] = array();
          }

          $counts[$cu][] = array("staff" => $lineParts[1], "rushable" => $lineParts[2], "billable" => $lineParts[3]);
        }
      }
    }

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

  return $returnArray;
}

/**
 * function GetTimetrackCookie (
 * Create a cookie for timetrack using the test user.
 *
 * @return string -- the cookie for timetrack
 */
function GetTimetrackCookie () {
    $secret = "Apache";
    $id = 13; // TEST DUDE
    $month = "01";
    $day = "01";
    $year = 1990;
    $date = "1990-01-01";
    $userName = "TEST DUDE";
    $ipAddress = "";
    $ticketDomain = "www.homecu.net";
    $now = time();

    $hash = MD5($secret . MD5(join (':', array($secret, $id, $month, $year, $day, $date, $userName, $ipAddress, $now))));
    $cookie = "c_sid=$id&c_mno=$month&c_syr=$year&c_day=$day&c_date=$date&c_nme=$userName&Cip=$ipAddress&Ctime=$now&Ch=$hash";

    return $cookie;
}

/**
 * function getMonitorBillingList($dbh, $showSQL, $reportDate, $monthBit, $logFile)
 * Get billing list, membership counts from the monitor server if the server is DEV and debug option is set.
 *
 * @param integer $dbh -- the database connection
 * @param boolean $showSQL -- if true, SQL will be added to the return array
 * @param integer $monthBit -- the bit to run the billing list for.  It is going to be 1-12 obviously.
 * @param string $logFile -- points to the billing count file.  There will be a file per month.
 *
 * @return SQLs, errors, membership counts, and the billing list.
 */
function GetMonitorBillingList($dbh, $sysenv) {
  try {
    extract ($sysenv["BILL"]);

    $reportDate = isset($reportDate) ? trim($reportDate) : "";
    $monthBit = isset($monthBit) ? trim($monthBit) : "";

    $url = "https://my.homecu.net/billing/index.prg?operation=getPotentiallyFromDifferentServer&reportDate=$reportDate&monthBit=$monthBit";
    $results = RunCurlOdyssey($url, $sysenv["devmode"]);

    switch ($results["httpCode"]) {
      case 504:
      case 0:
        throw new exception ("Monitor server timed out.", 3);
        break;
      case 200:
        $raw = $results["rawresp"];
        break;
      default:
        throw new exception ("Status not found.", 4);
    }

    if (trim($raw) == "") {
      throw new exception ("Monitor has no results.", 1);
    }

    $returnArray = HCU_JsonDecode($raw);
    if (count($returnArray) == 0) {
      throw new exception ("Monitor has no results.", 2);
    }
  } catch (exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "record" => array(), "membershipCounts" => array());
  }
  return $returnArray;
}

/**
 * function GetPotentiallyFromDifferentServer($dbh, $sysenv)
 * This gets the billing features from the billing contract configuration.
 * For the most part, this will come from the same database as the current container.
 * However, there is an option to get this information from my.homecu.net on the DEV environment.
 *
 * @param $dbh -- the database connection
 * @param $sysenv -- the environment variables (from cu_top)
 *
 * @return $status -- "000" if there are no errors.  Nonzero otherwise.
 * @return $error -- "" if there are no errors.  Nonempty otherwise.
 * @return $record -- the result of the query to get all billing features for billing.
 * @return $membershipCounts -- the assets and membership of the CU.
 */
function GetPotentiallyFromDifferentServer($dbh, $sysenv) {
  try {
    global $SYS_TYPE_CLOSED;

    extract ($sysenv["BILL"]);

    // If called from localhost, don't show an E_NOTICE.
    $recordResults = array();
    $memberResults = array();

    $sql = "select fd.id \"featureDetailId\", fd.sales_order_detail_id \"detailId\", trim(so.cu_id) \"cu\", trim(sd.prod_id) \"productName\",
              trim(si.billing_system_id) \"billingId\", si.calc_id \"calcId\", trim(fd.billing_template) \"billingTemplate\", si.id \"salesItemId\",
              trim(si.display_name) \"actualDescription\", fd.bill_half_boolean \"billHalf\", fd.billing_status \"billingStatus\",
              fd.partially_billed_date \"partiallyBilledDate\", fd.partially_billed_amount \"partiallyBilledAmount\", trim(p.home_cu_desc) \"prodText\",
              trim(ci.name) \"cuName\", fd.frequency \"frequency\", fd.months_to_bill \"monthsToBill\"
            from cubillfeaturedetail fd
            left join cubillsalesorderdetail sd on fd.sales_order_detail_id = sd.id
            left join cubillsalesorder so on sd.sales_order_id = so.id
            left join cuprodlist p on sd.prod_id = p.home_cu_code
            left join cubillsalesitem si on fd.sales_item_id = si.id
            left join cuinfo ci on lower(so.cu_id) = lower(ci.user_name)
            where (
              (fd.start_date <= '" . prep_save($reportDate) . "' and (fd.end_date is null or fd.end_date >= '" . prep_save($reportDate) . "')
                and fd.billing_status in (2,3) and fd.frequency between 0 and 2 and (fd.frequency = 0 or fd.months_to_bill & " . intval($monthBit) . " <> 0))
                or (fd.partially_billed_date is not null and fd.billing_status in (0,4) and fd.partially_billed_date = '" . prep_save($reportDate) . "'))
            order by so.cu_id, si.calc_id";

    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Record query failed.", 1);
    }
    $recordResults = db_fetch_all($sth);
    $recordResults = $recordResults === false ? array() : $recordResults;

    $sql = "select trim(user_name) \"cu\", dec_31_mem \"membership\", dec_31_assets \"assets\" from cuinfo";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Member query failed.", 2);
    }
    $memberResults = db_fetch_all($sth);
    $memberResults = $memberResults === false ? array() : $memberResults;

    $returnArray = array("status" => "000", "error" => "", "record" => $recordResults, "membershipCounts" => $memberResults);

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

/**
 * function DownloadRunBillingFiles($dbh, $sysenv)
 * This function downloads the run billing files.
 * If there is a problem so that the run billing process fails halfway through,
 * I can use this URL to get the files without bothering Mike, Mark, Kenny, or Miki.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment.
 *
 * @return file | json of error
 */
function DownloadRunBillingFiles($dbh, $sysenv) {
  try {

    $cmd = "cd /home/homecu/tmp >/dev/null;
            mkdir runBilling >/dev/null;
            cp .runBilling* runBilling >/dev/null;
            tar -zcvf runBilling.tar.gz runBilling >/dev/null;";
    $results = shell_exec($cmd);
    if ($results != "") {
      throw new exception ($results, 1);
    }

    $file = "/home/homecu/tmp/runBilling.tar.gz";
    $dir = "/home/homecu/tmp/runBilling";

    if (!file_exists($file)) {
      throw new exception ("File doesn't exist.", 1);
    }

    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="' . basename($file) . '"');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file));
    flush(); // Flush system output buffer
    readfile($file);
    exit;
  } catch (exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }
  return $returnArray;
}