aAudit.i

<?php

/**
 * function _ValidateParseUserAuditRow($row, $Cu, $dbh)
 * Validates the parameters for ParseUserAuditRow.
 *
 * @param $row -- the row from the database.
 * @param $Cu -- the credit union.
 * @param $dbh -- the database connection.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $data.details -- empty array
 * @return $data.before -- the before part of the row.
 * @return $data.after -- the after part of the row.
 * @return $data.isDelete -- if delete, the before and after can be null and that is okay.
 */
function _ValidateParseUserAuditRow($row, $Cu, $dbh) {
  try {
    if (!isset($row) || !is_array($row)) {
      throw new exception ("Row is invalid.", 2);
    }
    if (!isset($Cu) || !is_string($Cu)) {
      throw new exception ("Cu is invalid.", 3);
    }
    if (!isset($dbh)) {
      throw new exception ("Dbh is invalid.", 4);
    }
    if (!HCU_array_key_exists("before", $row) || !HCU_array_key_exists("after", $row)) {
      throw new exception ("Row is not valid.", 1);
    }

    $auditaction = HCU_array_key_value("actioncode", $row);

    if (in_array($auditaction, array("U_DEL_U", "U_DEL_G", "U_DEL_A"))) {
      $isDelete = true;
      $after = array();

      $before = HCU_JsonDecode($row["before"], false);
      if (!is_array($before)) { // NULL in the database which IS NOT an error to Kibana but is the previous style for a "delete."
        $before = array();
      } else if (!HCU_array_key_exists("deldata", $before)) {
        throw new exception ("Delete audit is invalid.", 5);
      }
    } else {
      $isDelete = false;

      $before = HCU_JsonDecode($row["before"], false);
      if (!is_array($before)) {
        throw new exception("Before JSON is not valid.", 13);
      }
      $after = HCU_JsonDecode($row["after"], false);
      if (!is_array($after)) {
        throw new exception("After JSON is not valid.", 14);
      }
    }

    $details = array();

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

/**
 * function _ValidateTable ($table, $after, $tableDefinition)
 * Validates the table inside of the before field and compares it with the after.
 *
 * @param $table -- the table changed.
 * @param $after -- the after array.
 * @param $tableDefinition -- the table definition
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $data.afterTable -- the table in the after column.
 * @return $data.newTableRow -- the row to send to the kendo template to show the audit.
 * @return $data.dontDoTableForeach -- this is false.
 */
function _ValidateTable ($table, $after, $tableDefinition) {
  try {

      if (!isset($table) || !is_string($table)) {
        throw new exception ("Table is invalid.", 1);
      }
      if (!isset($after) || !is_array($after)) {
        throw new exception ("After is invalid.", 2);
      }
      if (!isset($tableDefinition) || !is_array($tableDefinition)) {
        throw new exception ("Table definition is invalid.", 3);
      }

      $afterTable = HCU_array_key_exists($table, $after) ? $after[$table] : null;

      if (!isset($afterTable) || !is_array($afterTable)) {
        throw new exception("Table mismatch.", 15);
      }

      $label = isset($tableDefinition["_label"]) ? $tableDefinition["_label"] : $table;
      $newTableRow = array("table" => $table, "label" => $label, "rows" => array());

      $dontDoTableForeach = false;

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

/**
 * function _DoGroupAudit ($table, $newTableRow, $changedRows, $afterTable)
 * Does the group audit
 *
 * @param $table -- The table that will be changing.
 * @param $newTableRow -- Add an alternate description for the group audit.
 * @param $changedRows -- Get the changed rows for the group name.
 * @param $afterTable -- The after audits.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $data.newTableRow -- the table row with an alternate description attached.
 * @return $data.dontDoTableForeach -- this will be true.
 */
function _DoGroupAudit ($table, $newTableRow, $changedRows, $afterTable) {
  try {
      if (!isset($table) || $table !== "group") {
        throw new exception ("Table isn't \"group\".", 4);
      }
      if (!isset($newTableRow) || !is_array($newTableRow)) {
        throw new exception ("New table row isn't valid.", 5);
      }
      if (!isset($changedRows) || !is_array($changedRows)) {
        throw new exception ("Changed Rows isn't valid.", 6);
      }
      if (!isset($afterTable) || !is_array($afterTable)) {
        throw new exception ("After table isn't valid.", 7);
      }
      $dontDoTableForeach = true;
      $groupName = false;
      $isAdded = false;
      $isRemoved = false;

      foreach($changedRows as $changedRow) {
        $thisGroupName = HCU_array_key_value("group_name", $changedRow);
        if ($thisGroupName) {
          $isRemoved = true;
        }
        if ($groupName === false && $thisGroupName !== false) {
          $groupName = $thisGroupName;
          break;
        }
      }

      foreach($afterTable as $changedRow) {
        $thisGroupName = HCU_array_key_value("group_name", $changedRow);
        if ($thisGroupName) {
          $isAdded = true;
        }
        if ($groupName === false && $thisGroupName !== false) {
          $groupName = $thisGroupName;
          break;
        }
      }

      if ($groupName !== false) {
        $newTableRow["altDesc"] = "<b>$groupName</b> " . ($isAdded ? ($isRemoved ? "was changed." : "was added.") : ($isRemoved ? "was removed." : "was changed."));
        $dontDoTableForeach = true;
      } else { // Invalid because we are expecting to show the group name.
        throw new exception ("Group audit is invalid.", 3);
      }

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

/**
 * function ParseUserAuditRow($row, $Cu, $dbh)
 * This is the entry point for parsing the before and after snapshots in the audit table for display.
 * This started as a shared function between the user and admin audits but it has diverged significantly now for clarity with specific audits.
 *
 * @param $row -- this is the database row from the audit table.  Parse the before and after snapshots and put that back in the row in the "details" attribute.
 * @param $Cu -- the credit union.  This is needed for getting the time specific to the CU.
 * @param $dbh -- the database connection.  This is needed for getting the time specific to the CU as well as the mask.
 * @param $changedOnly -- If true, the function will only return parts of the audit that were changed.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $data.details -- What we shall be returning to the kendo template.
 */
function ParseUserAuditRow($row, $Cu, $dbh, $changedOnly = false) {

    try {

        $results = _ValidateParseUserAuditRow($row, $Cu, $dbh);

        if ($results["status"] !== "000") {
          throw new exception ("User Audit Row is invalid.", 1);
        }
        extract($results["data"]);

        if ($isDelete) {
          $deldata = HCU_array_key_value("deldata", $before);
          $msg = "";
          if ($auditaction == "U_DEL_U") {

            if ($deldata === false) {
              $msg = "User was deleted.";
            } else if (!HCU_array_key_exists("user_name", $deldata)) {
              throw new exception ("Delete user audit is invalid.", 4);
            } else {
              $msg = "User <b>" . trim($deldata["user_name"]) . "</b> was deleted.";
            }

          } else if ($auditaction == "U_DEL_G") {

            if ($deldata === false) {
              $msg = "Group was deleted.";
            } else if (!HCU_array_key_exists("group_name", $deldata)) {
              throw new exception ("Delete group audit is invalid.", 5);
            } else {
              $msg = "Group <b>" . trim($deldata["group_name"]) . "</b> was deleted.";
            }

          } else if ($auditaction == "U_DEL_A") {

            if ($deldata === false) {
              $msg = "Account was deleted.";
            } else if (!HCU_array_key_exists("accountnumber", $deldata)) {
              throw new exception ("Delete account audit is invalid.", 6);
            } else {
              $msg = "Account <b>" . trim($deldata["accountnumber"]) . "</b> was deleted.";
            }

          }

          if ($msg == "") {
            throw new exception ("Delete audit is invalid.", 7);
          }

          $newTableRow["altDesc"] = $msg;
          $details[] = $newTableRow;
        } else {
            foreach($before as $table => $changedRows) {
              $tableDefinition = GetTableDefinition (array("cu" => $Cu), $table)[$table];

              $results = _ValidateTable ($table, $after, $tableDefinition);
              if ($results["status"] !== "000") {
                throw new exception ("Table row is invalid.", 2);
              }
              extract($results["data"]);
              switch ($table) {
                case "group": // Only do a description with a group.

                  $results = _DoGroupAudit ($table, $newTableRow, $changedRows, $afterTable);
                  if ($results["status"] !== "000") {
                    throw new exception ("Group audit is invalid.", 3);
                  }
                  extract($results["data"]);
                  break;
              }

              if (!$dontDoTableForeach) {
                $results = ParseUserAuditTable($changedRows, $afterTable, $tableDefinition, $table, $dbh, $Cu, $changedOnly);
                if ($results["status"] !== "000") {
                  throw new exception($results["error"], $results["status"]);
                } else {
                  $newTableRow["rows"] = $results["returnedRows"];
                  $newTableRow["type"] = $results["returnedType"];
                }
              }

            $details[] = $newTableRow;
          }
        }

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

    return $returnArray;
}

/**
 * function _ValidateParseUserAuditTable ($changedRows, $afterTable, $tableDefinition, $table, $dbh, $Cu)
 * Validate parameters for ParseUserAuditTable.
 *
 * @param $changedRows -- the changedRows from the before snapshot.
 * @param $afterTable -- the changedRows from the after snapshot.
 * @param $tableDefinition -- the definition of the table (defined in cuDataModel.i.)
 * @param $table -- the table.
 * @param $dbh -- the database connection.
 * @param $Cu -- the credit union.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 */
function _ValidateParseUserAuditTable ($changedRows, $afterTable, $tableDefinition, $table, $dbh, $Cu) {
  try {
      if (!isset($changedRows) || !is_array($changedRows)) {
        throw new exception ("Changed rows is not valid.", 1);
      }
      if (!isset($afterTable) || !is_array($afterTable)) {
        throw new exception ("After table is not valid.", 2);
      }
      if (!isset($tableDefinition) || !is_array($tableDefinition)) {
        throw new exception ("Table definition is not valid.", 3);
      }
      if (!isset($table) || !is_string($table)) {
        throw new exception ("Table is not valid.", 4);
      }
      if (!isset($dbh)) {
        throw new exception ("Dbh is not valid.", 5);
      }
      if (!isset($Cu) || !is_string($Cu)) {
        throw new exception ("Cu is not valid.", 6);
      }

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

/**
 * function ParseUserAuditTable($changedRows, $afterTable, $tableDefinition, $passwordMask, $table, $dbh, $Cu)
 * This function parses the audit at the table level.  It is broken out because there is an audit that just has a description for the audit instead.
 *
 * @param $changedRows -- the array of rows changed in the before snapshot.
 * @param $afterTable -- the array of rows changed in the after snapshot.
 * @param $tableDefinition -- the table definition in cuDataModel.i.  This is used for validation as well as any table or column labels.
 * @param $table -- the table that the audit is being run on.
 * @param $dbh -- the database connection.  This is needed for getting the time specific to the CU as well as the mask.
 * @param $Cu -- the credit union.  This is needed for getting the time specific to the CU.
 * @param $changedOnly -- If true, the function will only return parts of the audit that were changed.
 *
 * @return array [
 *  status -- "000" if successful, nonzero if there are errors.
 *  error -- blank if successful, nonblank otherwise.
 *  returnedRows -- Rows for the audit.
 *  returnedType -- "mixed" for different audit types, "add" for new records, "remove" for deleted records, "update" for changed records.
 * ]
 */
function ParseUserAuditTable($changedRows, $afterTable, $tableDefinition, $table, $dbh, $Cu, $changedOnly) {

  $returnedRows =[];
  // $returnedType = ""; // not needed, variable immediately overwritten
  try {

    $results = _ValidateParseUserAuditTable ($changedRows, $afterTable, $tableDefinition, $table, $dbh, $Cu);
    if ($results["status"] !== "000") {
      throw new exception ("Validate failed.", 1);
    }
    switch($table) {
      case "memberacctrights":
        $results = GetMemberAcctRightsDescrs($changedRows, $afterTable);
        if ($results["status"] !== "000") {
          throw new exception($results["error"], $results["status"]);
        }
        $returnedType = "desc";
        $returnedRows = $results["returnedRows"];
        break;

      default:

        // Throws 'current() expects parameter 1 to be array, null given' otherwise
        $tableTypeMap = [];

        foreach($changedRows as $j => $beforeRow) {
          $afterRow = $afterTable[$j];

          if (!isset($afterRow)) {
            throw new exception("Table mismatch.", 16);
          }

          $beforeEmpty = count($beforeRow) == 0;
          $afterEmpty = count($afterRow) == 0;
          if ($beforeEmpty && $afterEmpty) {
            continue;
          }

          $results = ParseAuditValueRow($beforeEmpty, $afterEmpty, $afterRow, $beforeRow, $tableDefinition, $table, $dbh, $Cu, $changedOnly);
          if ($results["status"] !== "000") {
            throw new exception($results["error"], $results["status"]);
          }
          $combinedRow = $results["combinedRow"];

          $type = $afterEmpty ? "remove" : ($beforeEmpty ? "add" : "update");
          $combinedRow = ["type" => $type, "values" => $combinedRow];
          $tableTypeMap[$type] = $type;

          $returnedRows[] = $combinedRow;
        }

        $returnedType = count($tableTypeMap) > 1 ? "mixed" : current($tableTypeMap);
        break;
      }

    $returnArray = ["status" => "000", "error" => "", "returnedRows" => $returnedRows, "returnedType" => $returnedType];
  } catch (exception $e) {
    $returnArray = ["status" => $e->getCode(), "error" => $e->getMessage()];
  }

  return $returnArray;
}

/**
 * GetMemberAcctRightsDescrs($changedRows, $afterTable)
 * This gets a series of custom descriptions for the memberacctrights table.  This replaces the normal auditing for this table.
 *
 * @param $changedRows -- the rows in the before snapshot.
 * @param $afterTable -- the rows in the after snapshot.
 *
 * @return status -- "000" if successful, nonzero if there are errors.
 * @return error -- blank if successful, nonblank otherwise.
 * @return returnedRows -- Rows for the audit.
 */
function GetMemberAcctRightsDescrs($changedRows, $afterTable) {
  $returnedRows = array();
  try {
    $rightMap = array();
    if (!isset($changedRows) || !is_array($changedRows)) {
      throw new exception ("Changed Rows is not valid.", 1);
    }
    if (!isset($afterTable) || !is_array($afterTable)) {
      throw new exception ("After table is not valid.", 2);
    }
    foreach($changedRows as $beforeRow) {
      if (count($beforeRow) == 0) {
        continue; // Do not process an empty array
      }
      $right = HCU_array_key_value("whichright", $beforeRow);
      $accountnumber = HCU_array_key_value("accountnumber", $beforeRow);
      if ($right === false) {
        throw new exception ("Before right is not found.", 5);
      }
      if ($accountnumber === false) {
        throw new exception ("Before accountnumber is not found.", 10);
      }
      $key = trim($accountnumber) . "|" . trim($right);

      $rightMap[$key] = array("before" => $beforeRow);
    }

    foreach($afterTable as $afterRow) {
      if (count($afterRow) == 0) {
        continue; // Do not process an empty array
      }
      $right = HCU_array_key_value("whichright", $afterRow);
      $accountnumber = HCU_array_key_value("accountnumber", $afterRow);
      if ($right === false) {
        throw new exception ("After right is not found.", 6);
      }
      if ($accountnumber === false) {
        throw new exception ("After accountnumber is not found.", 11);
      }
      $key = trim($accountnumber) . "|" . trim($right);
      if (!HCU_array_key_exists($key, $rightMap)) {
        $rightMap[$key] = array("before" => array(), "after" => $afterRow);
      } else {
        $rightMap[$key]["after"] = $afterRow;
      }
    }

    // I have an guarantee that rightMap is set because I set up the variable.
    if (count($rightMap) == 0) {
      throw new exception ("Member acct audit doesn't have any information.", 9);
    }
    foreach($rightMap as $key => $rightStuff) {
        $rightRightMap = array("ACCESS" => "access", "ES" => "eStatement", "RDC" => "RDC", "BP" => "BillPay");

        $beforeRow = HCU_array_key_value("before", $rightStuff);
        $afterRow = HCU_array_key_value("after", $rightStuff);
        $beforeEmpty = $beforeRow === false || count($beforeRow) == 0;
        $afterEmpty = $afterRow === false || count($afterRow) == 0;

        // There is a case where the platform is set to NULL.  In this case, it is really not restricted.
        $beforePlatform = $beforeEmpty || !HCU_array_key_exists("platform", $beforeRow) ? array() : (!isset($beforeRow["platform"]) ? array("A","D") : HCU_JsonDecode($beforeRow["platform"]));
        $afterPlatform = $afterEmpty || !HCU_array_key_exists("platform", $afterRow) ? array() : (!isset($afterRow["platform"]) ? array("A","D") : HCU_JsonDecode($afterRow["platform"]));

        foreach($beforePlatform as $platformType) {
          if (!in_array($platformType, array("A","D"))) {
            throw new exception ("Before platform is invalid.", 7);
          }
        }

        foreach($afterPlatform as $platformType) {
          if (!in_array($platformType, array("A","D"))) {
            throw new exception ("After platform is invalid.", 8);
          }
        }

        $whichright = explode("|", $key);
        $whichright = $whichright[1];

        $right = HCU_array_key_exists($whichright, $rightRightMap) ? $rightRightMap[$whichright] : false;
        $account = HCU_array_key_exists("accountnumber", $beforeRow) ? $beforeRow["accountnumber"] : HCU_array_key_value("accountnumber", $afterRow);

        if ($right === false) {
          throw new exception ("Right is not valid.", 3);
        }
        if ($account === false) {
          throw new exception ("Account is not valid.", 4);
        }

        $altDesc = "<b>" . trim($account) . "</b>: ";
        if ($whichright == "ACCESS") {
          $beforeAllowed = HCU_array_key_value("allowed", $beforeRow) == "t"; // Always true for all rights except for the ACCESS right.
          $afterAllowed = HCU_array_key_value("allowed", $afterRow) == "t";
          $added = ($beforeEmpty && !$afterEmpty) || (!$beforeAllowed && $afterAllowed);
          $removed = (!$beforeEmpty && $afterEmpty) || ($beforeAllowed && !$afterAllowed);

          if ($added) {
            $altDesc .= "Added $right rights.";
          } else if ($removed) {
            $altDesc .= "Removed $right rights.";
          } else {
            continue; // No change between ACCESS rights so don't report it.
          }

        } else if ($beforeEmpty) {
          if (!$afterEmpty) {
            $hasDesktop = in_array("D", $afterPlatform);
            $hasApps = in_array("A", $afterPlatform);
            $altDesc .= "Added $right rights" . ($hasDesktop ? ($hasApps ? "." : " for desktop.") : ($hasApps ? " for the apps." : "."));
          }
        } else {
          if ($afterEmpty) {
            $hasDesktop = in_array("D", $beforePlatform);
            $hasApps = in_array("A", $beforePlatform);
            $altDesc .= "Removed $right rights" . ($hasDesktop ? ($hasApps ? "." : " for desktop.") : ($hasApps ? " for the apps." : "."));
          } else {
            $hasBDesktop = in_array("D", $beforePlatform);
            $hasBApps = in_array("A", $beforePlatform);
            $hasADesktop = in_array("D", $afterPlatform);
            $hasAApps = in_array("A", $afterPlatform);
            $fromDesc = $hasBDesktop ? ($hasBApps ? "all" : "desktop") : ($hasBApps ? "apps" : "none");
            $toDesc = $hasADesktop ? ($hasAApps ? "all" : "desktop") : ($hasAApps ? "apps" : "none");
            $altDesc .= "Changed $right rights from $fromDesc to $toDesc.";
          }
        }

        $returnedRows[] = array("type" => "desc", "value" => array(), "altDesc" => $altDesc);
    }

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

  return $returnArray;
}

/**
 * function ParseAuditValueRow($beforeEmpty, $afterEmpty, $afterRow, $beforeRow, $passwordMask, $tableDefinition, $table, $dbh, $Cu)
 * This function parses the audit as the column level.  It is broken up from the original function because there are also custom column descriptions that can happen.
 *
 * @param $beforeEmpty -- true if the before snapshot is empty.
 * @param $afterEmpty -- true if the after snapshot is empty.
 * @param $afterRow -- This is the table row that was changed in the after snapshot.
 * @param $beforeRow -- This is the table row that was changed in the before snapshot.
 * @param $tableDefinition -- this is useful for any column labels.
 * @param $table -- the table that the audit is done on.
 * @param $dbh -- the database connection.  This is needed for getting the time specific to the CU as well as the mask.
 * @param $Cu -- the credit union.  This is needed for getting the time specific to the CU.
 * @param $changedOnly -- If true, the function will only return parts of the audit that were changed.
 *
 * @return status -- "000" if successful, nonzero if there are errors.
 * @return error -- blank if successful, nonblank otherwise.
 * @return combinedRow -- The row for the audit.
 */
function ParseAuditValueRow($beforeEmpty, $afterEmpty, $afterRow, $beforeRow, $tableDefinition, $table, $dbh, $Cu, $changedOnly) {

  $combinedRow = array();

  try {
    if ($beforeEmpty) {
      if (!$afterEmpty) {
        if (!isset($afterRow) || !is_array($afterRow)) {
          throw new exception ("After row is not valid.", 1);
        }
        foreach($afterRow as $colName => $colValue) {

          $colDef = $tableDefinition["_cols"][$colName];
          if (!isset($colDef)) {
            throw new exception("Table mismatch.", 17);
          }

          $permTable = $table === "cuusers" ? "user" : $table;
          $visibility = GetAuditColVisibility($colName, $permTable);

          if ($visibility == "none") {
            continue;
          }
          $restrictVisibility = $visibility == "hidden";

          $label = isset($colDef["label"]) ? $colDef["label"] : $colName;
          $results = GetCustomValue($colName, $permTable, null, $colValue, $restrictVisibility, $label, $dbh, $Cu, false);

          if ($results["isCustom"]) {
            if ($changedOnly) {
              foreach($results["customValueArray"] as $row) {
                if (!$row["same"]) {
                  $combinedRow[] = $row;
                }
              }
            } else {
              $combinedRow = array_merge($combinedRow, $results["customValueArray"]); // Can be multiple rows in the case of splitting a flagset.
            }

          } else {

            $afterValue = !isset($colValue) || trim($colValue) == "" ? true : $colValue;
            $same = false;

            // Empty and null are the same and are set to "--"
            $beforeValue = "--";
            $afterValue = $afterValue === true ? "--" : trim($afterValue);

            // If time or timestamp, then format the output.  If timestamp, then remember to also output it in Credit Union time.
            // NOTE: this doesn't catch the prior login / failed login / current login because that is in a STRING field for some reason.
            switch($colDef["type"]) {
              case DBTYPE_DATE:
                $beforeValue = GetAuditTime($dbh, $beforeValue, $Cu, false, $tz);
                $afterValue = GetAuditTime($dbh, $afterValue, $Cu, false, $tz);
                break;
              case DBTYPE_TIMESTAMPTZ:
              case DBTYPE_TIMESTAMP:
              case DBTIMESTAMP_USENOW:
                $beforeValue = GetAuditTime($dbh, $beforeValue, $Cu, true, $tz);
                $afterValue = GetAuditTime($dbh, $afterValue, $Cu, true, $tz);
                break;
            }

            // If changed only, only add it if it is changed.
            if (!$changedOnly || ($changedOnly && !$restrictVisibility)) {
              $combinedRow[] = array("col" => trim($colName), "before" => $beforeValue, "after" => $afterValue, "label" => trim($label), "same" => $restrictVisibility);
            }

          }


        }
      }
    } else {
      if (!isset($beforeRow) || !is_array($beforeRow)) {
        throw new exception ("Before row is not valid.", 2);
      }
      foreach($beforeRow as $colName => $colValue) {
        $colDef = $tableDefinition["_cols"][$colName];
        if (!isset($colDef)) {
          throw new exception("Table mismatch.", 18);
        }


        $permTable = $table === "cuusers" ? "user" : $table;
        $visibility = GetAuditColVisibility($colName, $permTable);
        if ($visibility == "none") {
          continue;
        }
        $restrictVisibility = $visibility == "hidden";

        $beforeValue = trim($colValue) == "" ? "--" : $colValue;
        if ($afterEmpty) {
          $afterValue = "";
          $same = true;
        } else {
          if (!HCU_array_key_exists($colName, $afterRow)) {
            $afterValue = "";
            $same = true;
          } else if (trim($afterRow[$colName]) == "") {
            $afterValue = "--";
            $same = strcmp($beforeValue, $afterValue) === 0;
          } else {
            $afterValue = trim($afterRow[$colName]);
            $same = strcmp(trim($beforeValue), trim($afterValue)) === 0;
          }
        }

        $label = isset($colDef["label"]) ? $colDef["label"] : $colName;
        $results = GetCustomValue($colName, $permTable, $colValue, $afterValue, $afterEmpty ? $restrictVisibility : false, $label, $dbh, $Cu, $same);

        if ($results["isCustom"]) {
            if ($changedOnly) {
              foreach($results["customValueArray"] as $row) {
                if (!$row["same"]) {
                  $combinedRow[] = $row;
                }
              }
            } else {
              $combinedRow = array_merge($combinedRow, $results["customValueArray"]); // Can be multiple rows in the case of splitting a flagset.
            }
        } else {

          // If time or timestamp, then format the output.  If timestamp, then remember to also output it in Credit Union time.
          // NOTE: this doesn't catch the prior login / failed login / current login because that is in a STRING field for some reason.
          $tz = null;
          switch($colDef["type"]) {
            case DBTYPE_DATE:
              $beforeValue = GetAuditTime($dbh, $beforeValue, $Cu, false, $tz);
              $afterValue = GetAuditTime($dbh, $afterValue, $Cu, false, $tz);
              break;
            case DBTYPE_TIMESTAMPTZ:
            case DBTYPE_TIMESTAMP:
            case DBTIMESTAMP_USENOW:
              $beforeValue = GetAuditTime($dbh, $beforeValue, $Cu, true, $tz);
              $afterValue = GetAuditTime($dbh, $afterValue, $Cu, true, $tz);
              break;
          }

          $show = $visibility == "always" ? true : ($afterEmpty ? !$restrictVisibility : !$same);
          if (!$changedOnly || ($changedOnly && $show)) {
            $combinedRow[] = array("col" => trim($colName), "before" => $beforeValue, "after" => $afterValue, "label" => trim($label), "same" => !$show);
          }
        }
      }
    }

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

  return $returnArray;
}

/**
 * function GetAuditColVisibility($col, $table)
 * Determines the visibility of a column.
 * Values are:
 *    "hidden" -- On an add, remove or changed audit, it doesn't show up.  (It is technically a "changed" value.)  You can view it by clicking on the "Show All" button.
 *    "none" -- Column doesn't get returned.  You cannot see it with either the normal or "show all" modes.
 *    "visible" -- Default.  Column will be visible on an add or remove audit.  For a changed audit, it will be visible when there is a change.
 *
 * @param $col -- the column to check
 * @param $table -- the table to check
 *
 * @return "hidden", "none", or "visible" depending on the visibility of the column.
 */
function GetAuditColVisibility($col, $table) {
  $key = "$table | $col";
  $visibility = "visible";
  switch($key) {
    case "user | group_id":
    case "user | contact":
    case "user | user_id":
    case "user | primary_account":
    case "user | challenge_quest_id": // Only from profile require.
    case "user | schedule_code":
    case "user | pkattempt":
    case "user | is_group_primary":
    case "useraccounts | display_order":
    case "useraccounts | user_id":
    case "useraccounts | display_qty":
    case "useraccounts | display_qty_type":
    case "useraccount | display_order": // Typo also is in use.
    case "useraccount | user_id":
    case "useraccount | display_qty":
    case "useraccount | display_qty_type":
    case "memberacct | primary_user":
    case "memberacct | balance_stamp":
    case "memberacct | balance_attempt":
    case "memberacct | allowenroll":
    case "userrights | user_id":
    case "usercontact | contact_id":
      $visibility = "none";
      break;
    case "user | user_name":
    case "user | email":
    case "useraccounts | accountnumber":
    case "useraccounts | accounttype":
    case "useraccounts | display_name":
    case "useraccounts | certnumber":
    case "useraccount | accountnumber": // Typo also is in use.
    case "useraccount | accounttype":
    case "useraccount | display_name":
    case "useraccount | certnumber":
    case "alert | accountnumber":
    case "alert | accounttype":
    case "alert | certnumber":
    case "alert | emailtype":
    case "alert | notifyto":
    case "alert | notifymsg":
    case "alert | lastalert":
    case "alert | alertstatus":
      $visibility = "visible";
      break;
    case "userrights | feature_code":
      $visibility = "always";
      break;
    case substr($key, 0, strlen("alert | ")) == "alert | ":
      $visibility = "none";
      break;
    case substr($key, 0, strlen("user | ")) == "user | ":
    case substr($key, 0, strlen("useraccounts | ")) == "useraccounts | ":
    case substr($key, 0, strlen("useraccount | ")) == "useraccount | ":
    case substr($key, 0, strlen("userrights | ")) == "userrights | ":
      $visibility = "hidden";
      break;
    default:
      $visibility = "visible"; // By default, don't restrict this value.
      break;
  }
  return $visibility;
}

/**
 * function GetCustomValue($colName, $table, $beforeValue, $afterValue, $restrictVisibility, $label, $dbh, $Cu)
 * Gets the custom row for particular columns.  This is useful for flagset fields where the meaningful change is a bit.
 *
 * @param $colName -- the column
 * @param $table -- the table
 * @param $beforeValue -- the value in the before snapshot for the column
 * @param $afterValue -- the value in the after snapshot for the column
 * @param $restrictVisibility -- if true, column will be hidden at the start.  Otherwise it will be visible at the start if there was a change in the column.
 * @param $label -- the label of the column
 * @param $dbh -- the database connection.  This is needed for getting the time specific to the CU as well as the mask.
 * @param $Cu -- the credit union.  This is needed for getting the time specific to the CU.
 *
 * @return isCustom -- if there is a custom row for the column then it is true.  False otherwise.
 * @return customValueArray -- records to append to the audit for the table.
 */
function GetCustomValue($colName, $table, $beforeValue, $afterValue, $restrictVisibility, $label, $dbh, $Cu, $same) {
  $key = "$table | $colName";
  $isCustom = true;
  $customValueArray = array();
  $beforeEmpty = !isset($beforeValue) || $beforeValue == "--" || $beforeValue == "";
  $afterEmpty = !isset($afterValue) || $afterValue == "--" || $afterValue == "";

  switch($key) {
    case "user | userflags":
      $before = $beforeEmpty ? "" : (GetUserFlagsValue("MEM_FORCE_RESET") & intval($beforeValue) == 0 ? "N" : "Y");
      $after = $afterEmpty ? "" : (GetUserFlagsValue("MEM_FORCE_RESET") & intval($afterValue) == 0 ? "N" : "Y");
      $show = $restrictVisibility ? false : !$afterEmpty && $before != $after;
      $customValueArray[] = array("label" => "Force Reset", "before" => $before, "after" => $after, "same" => !$show);
      $before = $beforeEmpty ? "" : (GetUserFlagsValue("MEM_ASKBPAY") & intval($beforeValue) == 0 ? "N" : "Y");
      $after = $afterEmpty ? "" : (GetUserFlagsValue("MEM_ASKBPAY") & intval($afterValue) == 0 ? "N" : "Y");
      $show = $restrictVisibility ? false : !$afterEmpty && $before != $after;
      $customValueArray[] = array("label" => "Ask BillPay", "before" => $before, "after" => $after, "same" => !$show);
      break;
    case "useraccount | recordtype":
    case "useraccounts | recordtype":
      $beforeValue = in_array($beforeValue, array("D", "T")) ? "Deposit" : (in_array($beforeValue, array("L", "P")) ? "Loan" : $beforeValue);
      $afterValue = in_array($afterValue, array("D", "T")) ? "Deposit" : (in_array($afterValue, array("L", "P")) ? "Loan" : $afterValue);
      $show = $restrictVisibility ? false : !$afterEmpty && $beforeValue != $afterValue;
      $customValueArray[] = array("col" => trim($colName), "before" => $beforeValue, "after" => $afterValue, "label" => trim($label), "same" => !$show);
      break;
    case "user | mfaquest":
      $before = $beforeEmpty ? array() : HCU_JsonDecode($beforeValue);
      $after = $afterEmpty ? array() : HCU_JsonDecode($afterValue);

      $beforeAnswerArray = HCU_array_key_value("answers", $before);
      $afterAnswerArray = HCU_array_key_value("answers", $after);
      $challengeChange = array();
      if ($beforeAnswerArray !== false || $afterAnswerArray !== false) {
        if ($beforeAnswerArray !== false) {
          foreach($beforeAnswerArray as $key => $value) {
            $challengeChange [$key] = array("before" => $value);
          }
        }

        if ($afterAnswerArray !== false) {
          foreach($afterAnswerArray as $key => $value) {
            if (!HCU_array_key_exists($key, $challengeChange)) {
              $challengeChange [$key] = array("after" => $value);
            } else {
              $challengeChange [$key]["after"] = $value;
            }
          }
        }

        // I know that it is set because I control the variable.
        foreach($challengeChange as $key => $row) {
          if (!HCU_array_key_exists("before", $row)) {
            $challengeChange [$key] ["before"] = "";
          } else if (!isset($row["before"]) || trim($row["before"]) == "") {
            $challengeChange [$key] ["before"] = "--";
          }

          if (!HCU_array_key_exists("after", $row)) {
            $challengeChange [$key] ["after"] = "";
          } else if (!isset($row["after"]) || trim($row["after"]) == "") {
            $challengeChange [$key] ["after"] = "--";
          }

          $challengeChange [$key] ["label"] = "Challenge Question #$key";
          $show = $restrictVisibility ? false : !$afterEmpty && $challengeChange [$key] ["before"] != $challengeChange [$key] ["after"];
          $challengeChange [$key] ["same"] = !$show;
        }
      }

      $beforeMFADate = HCU_array_key_value("mfadate", $before);
      $afterMFADate = HCU_array_key_value("mfadate", $after);
      $tz = null;
      $dateArray = null;
      if ($beforeMFADate !== false || $afterMFADate !== false) {

        if ($beforeMFADate !== false) {
          if (!isset($beforeMFADate) || trim($beforeMFADate) == "") {
            $beforeTime = "--";
          } else if (intval($beforeMFADate) == 0) {
            $beforeTime = "";
          } else {
            $beforeTime = GetAuditTime($dbh, $beforeMFADate, $Cu, true, $tz);
          }

          $dateArray = array("before" => $beforeTime);
        }

        if ($afterMFADate !== false) {
          if (!isset($afterMFADate) || trim($afterMFADate) == "") {
            $afterTime = "--";
          } else if (intval($afterMFADate) == 0) {
            $afterTime = "";
          } else {
            $afterTime = GetAuditTime($dbh, $afterMFADate, $Cu, true, $tz);
          }

          if (isset($dateArray)) {
            $dateArray["after"] = $afterTime;
          } else {
            $dateArray = array("after" => $afterTime);
          }
        }

        if (!HCU_array_key_exists("before", $dateArray)) {
          $dateArray["before"] = "";
        }

        if (!HCU_array_key_exists("after", $dateArray)) {
          $dateArray["after"] = "";
        }

        $dateArray["label"] = "MFA Date";
        $show = $restrictVisibility ? false : !$afterEmpty && $dateArray["before"] != $dateArray["after"];
        $dateArray["same"] = !$show;
      }

      if (count($challengeChange) > 0) {
        $customValueArray = array_values($challengeChange);
      }
      if (isset($dateArray)) {
        $customValueArray[] = $dateArray;
      }
      break;
      case "usercontact | phones":
        $before = $beforeEmpty ? array() : HCU_JsonDecode($beforeValue);
        $after = $afterEmpty ? array() : HCU_JsonDecode($afterValue);
        $phoneArray = array();
        if ($before !== false && HCU_array_key_exists("mobile", $before) && is_array($before["mobile"])) {
          foreach($before["mobile"] as $phone) {
            $key = preg_replace('/\D+/', '', $phone);
            if (!HCU_array_key_exists($key, $phoneArray)) {
              $phoneArray[$key] = array("before" => $phone);
            }
          }
        }
        if ($after !== false && HCU_array_key_exists("mobile", $after) && is_array($after["mobile"])) {
          foreach($after["mobile"] as $phone) {
            $key = preg_replace('/\D+/', '', $phone);
            if (!HCU_array_key_exists($key, $phoneArray)) {
              $phoneArray[$key] = array("after" => $phone);
            } else {
              $phoneArray[$key]["after"] = $phone;
            }
          }
        }

        $index = 1;
        foreach($phoneArray as $key => $row) {
          $phoneArray[$key]["label"] = "Phone #" . $index++;
          if (!isset($row["before"])) {
            $phoneArray[$key]["before"] = "--";
          }

          if (!isset($row["after"])) {
            $phoneArray[$key]["after"] = "--";
          }
          $show = $restrictVisibility ? false : !$afterEmpty && $phoneArray[$key]["before"] != $phoneArray[$key]["after"];
          $phoneArray[$key]["same"] = !$show;
        }
        $customValueArray = array_values($phoneArray);
        break;
      case "user | passwd":
        $passwordMask = "********";
        $show = $restrictVisibility ? false : !$same;
        $before = $beforeValue == "--" ? "--" : $passwordMask;
        $after = $afterValue == "--" ? "--" : $passwordMask;

        $customValueArray[] = array("col" => trim($colName), "before" => $before, "after" => $after, "label" => trim($label), "same" => !$show);
        break;
      case "user | msg_tx":
        $before = $beforeEmpty ? 0 : GetMsgTxValue("MSGTX_FORCE_EM") & intval($beforeValue);
        $after = $afterEmpty ? 0 : GetMsgTxValue("MSGTX_FORCE_EM") & intval($afterValue);
        $before = $before === 0 ? "N" : "Y";
        $after = $after === 0 ? "N" : "Y";
        $show = $restrictVisibility ? false : !$afterEmpty && $before != $after;
        $customValueArray[] = array("label" => "Force Email", "before" => $before, "after" => $after, "same" => !$show);
        break;
      case "alert | alerttype":

        switch ($beforeValue) {
          case "B":
            $before = "Balance";
            break;
          case "T":
            $before = "Transaction";
            break;
          case "L":
            $before = "Loan";
            break;
          case "C":
            $before = "Check";
            break;
          default:
            $before = $beforeValue;
            break;
        }
        switch ($afterValue) {
          case "B":
            $after = "Balance";
            break;
          case "T":
            $after = "Transaction";
            break;
          case "L":
            $after = "Loan";
            break;
          case "C":
            $after = "Check";
            break;
          default:
            $after = $afterValue;
            break;
        }
        $show = $restrictVisibility ? false : !$same;
        $customValueArray[] = array("col" => trim($colName), "before" => $before, "after" => $after, "label" => trim($label), "same" => !$show);
        break;
      case "alert | emailtype":
        switch ($beforeValue) {
          case "E":
            $before = "Email";
            break;
          case "W":
            $before = "Phone";
            break;
          default:
            $before = $beforeValue;
            break;
        }
        switch ($afterValue) {
          case "E":
            $after = "Email";
            break;
          case "W":
            $after = "Phone";
            break;
          default:
            $after = $afterValue;
            break;
        }
        $show = $restrictVisibility ? false : !$same;
        $customValueArray[] = array("col" => trim($colName), "before" => $before, "after" => $after, "label" => trim($label), "same" => !$show);
        break;
        // Booleans using 0 or 1.
      case "alert | alertstatus":
        switch ($beforeValue) {
          case "0":
            $before = "N";
            break;
          case "1":
            $before = "Y";
            break;
          default:
            $before = $beforeValue;
            break;
        }
        switch ($afterValue) {
          case "0":
            $after = "N";
            break;
          case "1":
            $after = "Y";
            break;
          default:
            $after = $afterValue;
            break;
        }
        $show = $restrictVisibility ? false : !$same;
        $customValueArray[] = array("col" => trim($colName), "before" => $before, "after" => $after, "label" => trim($label), "same" => !$show);
        break;
      // Booleans using "t" or "f"
      case "useraccount | view_balances":
      case "useraccount | view_transactions":
      case "useraccount | int_deposit":
      case "useraccount | int_withdraw":
      case "useraccount | ext_deposit":
      case "useraccount | ext_withdraw":
      case "useraccounts | view_balances":
      case "useraccounts | view_transactions":
      case "useraccounts | int_deposit":
      case "useraccounts | int_withdraw":
      case "useraccounts | ext_deposit":
      case "useraccounts | ext_withdraw":
        switch ($beforeValue) {
          case "t":
            $before = "Y";
            break;
          case "f":
            $before = "N";
            break;
          default:
            $before = $beforeValue;
            break;
        }
        switch ($afterValue) {
          case "t":
            $after = "Y";
            break;
          case "f":
            $after = "N";
            break;
          default:
            $after = $afterValue;
            break;
        }
        $show = $restrictVisibility ? false : !$same;
        $customValueArray[] = array("col" => trim($colName), "before" => $before, "after" => $after, "label" => trim($label), "same" => !$show);
        break;
      default:
        $isCustom = false;
        $customValueArray = array();
        break;
  }

  return array("isCustom" => $isCustom, "customValueArray" => $customValueArray);
}

/**
 * function GetAuditTime($dbh, $value, $Cu, $setTimezone, &$tz)
 * Gets the time for timezone fields in the audit.  If it is not set then --.
 *
 * @param $dbh -- the database connection
 * @param $value -- the raw database version of the timestamp.
 * @param $Cu -- the credit union
 * @param $setTimezone -- If true, then the data is a timestamp.  The date part is shown and it is set to the credit union's time.  Otherwise, it shows just the date.
 * @param $tz -- handle to the timezone object.
 *
 * @return string of date/time in credit union time (if timestamp); otherwise, it is a string of the date.
 */
function GetAuditTime($dbh, $value, $Cu, $setTimezone, &$tz) {
  if ($value == "" || $value == "--") {
    return $value;
  }
  if ($setTimezone && !isset($tz)) {
    $tz = GetCreditUnionTimezone($dbh, $Cu);
  }

  // Numbers are treated as UTC.
  if(is_numeric($value)) {
    $value = "@" . intval($value);
  }
  $dateTime = new DateTime($value);
  // If it is a type of "date" in the database, timezone is set to zero so display shows as the previous day.
  // If it is a type of "timestamp" in the database, then we do need to adjust.
  if ($setTimezone && isset($value)) {
    $dateTime->setTimezone(new DateTimeZone($tz));
  }

  $format = $setTimezone ? "n/j/Y g:i A" : "n/j/Y";

  return $dateTime->format($format);
}