hcuExternalAccts.i

<?php
/*
 * File: hcuExternalAccts.i
 *
 * Purpose: Contains helper functions that are used with both External Accounts and M2M Accounts features.  Can be called
 *          from the .prg or the .data files.
 *
 * Standard format: Each function should return a FALSE if there is an error or non-successful
 *          operation or a structure with the desired return information.
 * Alternative format: Return a structure with a code = "000" for success, and a list of
 *          errors in an array.
 *
 * The "type" field is "EXT" or "M2M" for external accounts and member-to-member accounts, respectively.
 *
 * Status values for <cu>extaccount.status (character):
 *  0 - pending (waiting for user approval)
 *  p - pending (waiting for micro-deposit confirmation)
 *  a - active (available for use)
 *  l - (lower case L) locked due to validation retry limit
 *  i - inactive (still considered confirmed but not available for use)
 *  (remove account with a delete action)
 * NOTE: initially, there is not going to be a "pending approval" step.
 *
 */

// this should be configurable
define( "M2M_SIGNIFICANT_LEN", 5 );

/**
 * Process an External Accounts action. This handles both External Accounts and M2M Accounts
 * because they share the update functionaltiy.
 *
 * @param array $pHBEnv           -- Current environment
 * @param array $pInputVars       -- Input variables specific to the call.
 *
 * @return array                  -- An array with success code or error information
 */
//
function ManageExternalAccount( $pHBEnv, $pInputVars ) {
  $retStatusAry = Array(
    'status' => Array('code'=>'000', 'errors' => Array()),
    'data' => '',
    'info' => array()     // informational messages
  );

  try {
    $aryErrors = array();

    $MC = $pHBEnv["MC"];
    $dbh = $pHBEnv["dbh"];

    // do the requested operation
    $action = $pInputVars["action"];
    switch ( $action ) {
      case "add_account":
        // validate the inputs
        $aryValidate = EXT_ValidateInputs( $pHBEnv, $dbh, $pInputVars, $MC );

        if ( !$aryValidate || ($aryValidate["code"] !== "000") ) {
          // an error occurred
          if ( is_array( $aryValidate["errors"] ) ) {
            for ( $e = 0; $e < count( $aryValidate["errors"] ); $e++ ) {
              $aryErrors[] = $aryValidate["errors"][$e];
            }
          } else {
            $aryErrors[] = $aryValidate["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // clean up any user-supplied data - convert any UTF-8 characters to encoded html entities
        // NOTE: data was sanitized on input
        $pInputVars["display_name"] = ConvertFromUTF8( $pInputVars["display_name"] );

        // add the account
        $return = EXT_AddAccount( $dbh, $pHBEnv,  $pInputVars, $MC );
        if ( $return["code"] !== "000" ) {
          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // if we got here we were successful with the initial external account creation
        $retStatusAry["info"][] = $MC->msg('EXT Account Added', HCU_DISPLAY_AS_RAW);

        // NOTE: this is a separate entry-point in case later need to have approval of external account setup

        // start the validation
        $pInputVars["ext_acct_id"] = $return["data"]["id"];
        $return = EXT_StartAccountVerify( $dbh, $pHBEnv,  $pInputVars, $MC );
        if ( $return["code"] !== "000" ) {
          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // if we got here we were successful with the initial external account creation
        $retStatusAry["info"][] = $MC->msg('EXT Account verify started', HCU_DISPLAY_AS_RAW);

        // if we got here, return the updated account list
        $return = EXT_GetAccounts( $dbh, $pHBEnv );
        if ( $return["code"] !== "000" ) {
          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // return the account lists
        $retStatusAry["data"] = $return["data"];

        break;
      case "add_m2m_account":
        // validate the inputs
        $aryValidate = EXT_ValidateM2MInputs( $pHBEnv, $dbh, $pInputVars, $MC );

        if ( !$aryValidate || ($aryValidate["code"] !== "000") ) {
          // an error occurred
          if ( is_array( $aryValidate["errors"] ) ) {
            for ( $e = 0; $e < count( $aryValidate["errors"] ); $e++ ) {
              $aryErrors[] = $aryValidate["errors"][$e];
            }
          } else {
            $aryErrors[] = $aryValidate["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // clean up any user-supplied data - convert any UTF-8 characters to encoded html entities
        // NOTE: data was sanitized on input
        $pInputVars["display_name"] = ConvertFromUTF8( $pInputVars["display_name"] );

        // add the account
        $return = EXT_AddM2MAccount( $dbh, $pHBEnv,  $pInputVars, $MC );
        if ( $return["code"] !== "000" ) {
          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // if we got here we were successful with the initial external account creation
        $retStatusAry["info"][] = $MC->msg('Member Account Added', HCU_DISPLAY_AS_RAW);

        // NOTE: This account validates right away (i.e. no error -> validated)

        $return = EXT_GetM2MAccounts( $dbh, $pHBEnv );
        if ( $return["code"] !== "000" ) {
          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // return the account lists
        $retStatusAry["data"] = $return["data"];

        break;
      case "update_account":
        // the names were expected to be sanitized before calling this
        // clean up any user-supplied data - convert any UTF-8 characters to encoded html entities
        $pInputVars["display_name"] = ConvertFromUTF8( $pInputVars["display_name"] );

        // update the account
        $return = EXT_UpdateAccount( $dbh, $pHBEnv,  $pInputVars, $MC );
        if ( $return["code"] !== "000" ) {
          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // if we got here we were successful with the initial external account creation
        $retStatusAry["info"][] = $MC->msg('EXT Account Updated', HCU_DISPLAY_AS_RAW);

        // if we got here, return the updated account info
        $retStatusAry["data"] = $return["data"];

        break;
      case "validate_account":
        // validate the micro deposit amounts are correct

        // update the account
        $return = EXT_ValidateAccount( $dbh, $pHBEnv,  $pInputVars, $MC );
        if ( $return["code"] !== "000" ) {
          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // this can return an error or success as part of the reply so only say validated if the status is active
        if ( isset( $return["status"] ) && $return["status"] == "a" ) {
          $retStatusAry["info"][] = $MC->msg('EXT Account Validated', HCU_DISPLAY_AS_RAW);
        }

        // return the updated account info
        $retStatusAry["data"] = $return["data"];

        break;
      case "delete_account":
        // delete the account
        $return = EXT_DeleteAccount( $dbh, $pHBEnv,  $pInputVars, $MC );
        if ( $return["code"] !== "000" ) {
          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // if we got here we were successful with the initial external account creation
        $retStatusAry["info"][] = $MC->msg('EXT Account Deleted', HCU_DISPLAY_AS_RAW);

        // if we got here, return the id that was updated

        // return the account id just deleted
        $retStatusAry["data"] = $pInputVars["id"];

        break;
      case "get_accounts":
        // get the external account for the current user
        $return = EXT_GetAccounts( $dbh, $pHBEnv );
        if ( $return["code"] !== "000" ) {
          $aryErrors[] = $MC->msg('EXT Error getting accounts', HCU_DISPLAY_AS_RAW);

          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // return the requested list
        $retStatusAry["data"] = $return["data"];
        break;
      case "get_m2m_accounts":
        // get the M2M accounts for the current user
        $return = EXT_GetM2MAccounts( $dbh, $pHBEnv );
        if ( $return["code"] !== "000" ) {
          $aryErrors[] = $MC->msg('EXT Error getting accounts', HCU_DISPLAY_AS_RAW);

          if ( is_array( $return["errors"] ) ) {
            for ( $e = 0; $e < count( $return["errors"] ); $e++ ) {
              $aryErrors[] = $return["errors"][$e];
            }
          } else {
            $aryErrors[] = $return["errors"];
          }

          throw new Exception (HCU_JsonEncode($aryErrors));
        }

        // return the requested list
        $retStatusAry["data"] = $return["data"];
        break;
      default:
        $aryErrors[] = "Unexpected action: {$pInputVars["action"]}";
        throw new Exception (HCU_JsonEncode($aryErrors));
    }

    // from here, success will be returned
  } catch( Exception $ex ) {
    //Return error message(s)
    $retStatusAry["status"]["errors"] = HCU_JsonDecode( $ex->getMessage() );
    $retStatusAry["status"]["code"] = "999";
  }

  // safety check
  if ( $retStatusAry["status"]["errors"] != array() ) {
    $retStatusAry["status"]["code"] = "999";
  }

  return $retStatusAry;
} // end ManageExternalAccount

// Check the menuing features to verify this feature is available to this credit union.
function Check_ExternalTransfersEnabled( $pDbh, $pHBEnv ) {
  try {
  } catch (Exception $ex) {
    $logInfo = array( "message" => $ex->getMessage(), "code" => $ex->getCode() );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );
  }

  return true;
} // end Check_ExternalTransfersEnabled

// Check the menuing features to verify this feature is available to this credit union.
function Check_M2MTransfersEnabled( $pDbh, $pHBEnv ) {
  try {
  } catch (Exception $ex) {
    $logInfo = array( "message" => $ex->getMessage(), "code" => $ex->getCode() );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );
  }

  return true;
} // end Check_ExternalTransfersEnabled

// Return the status lookup
function Get_ExternalTransferStatusLookup( $pMC ) {
  return array ( "0" => $pMC->msg( "EXT Pending confirmation" ),
                 "p" => $pMC->msg( "EXT Pending verification" ),
                 "a" => $pMC->msg( "Active" ),
                 "l" => $pMC->msg( "EXT Locked" ),
                 "i" => $pMC->msg( "Inactive" ) );
} // end Get_ExternalTransferStatusLookup

/**
 * Get the external accounts that have been configured for this user.
 *
 * @param integer $pDbh           -- Current database handle
 * @param array $pHBEnv           -- Current HB_ENV values
 *
 * @return array                  -- An array with the correct accounts
 */
function EXT_GetAccounts( $pDbh, $pHBEnv ) {
  // initialize the return array
  $returnData = array( "code" => "000", "errors" => array(), "data" => array() );
  $pMC = $pHBEnv['MC'];

  try {
    // get the deposit accounts can deposit into and loans that can have payments made into
    $SQL = "SELECT *
            FROM {$pHBEnv["Cu"]}extaccount ea
            WHERE ea.user_id = {$pHBEnv["Uid"]}
              AND type = 'EXT'
            ORDER BY lower( display_name ), status, id
            ";
    $rs = db_query( $SQL, $pDbh );
    if ( !$rs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1133" );
    }

    // cycle through the rows
    $row = 0;
    $return = array();

    $mask= GetMask($pDbh, $pHBEnv["Cu"]);
    $mask= $mask["code"] != 0 ? array("start" => -2) : $mask["data"]; // Just return the default mask in this case.

    while ( $extRow = db_fetch_array( $rs, $row++ ) ) {
      $displayName = trim( $extRow["display_name"] );

      $remoteInfo = HCU_JsonDecode( $extRow["remote_info"]);

      $nameOnAccount = $remoteInfo["rdfi"]["name"];

      // dont return the micro deposit amounts
      $status = trim( $extRow["status"] );
      if ( $status == 'p' ) {
        $keep = array( "rdfi" => $remoteInfo["rdfi"] );

        // need to convert to local time
        $myDateTime = new DateTime( date( "m/d/y g:ia", $remoteInfo["verify"]["pending_date"] ) );
        $myDateTime->setTimezone( new DateTimeZone( $pHBEnv["tz"] ) );

        $keep["verify"] = array( "pending_date" => $myDateTime->format( "m/d/y g:ia" ),
                                              "tries" => $remoteInfo["verify"]["tries"] );
        $returnRemoteInfo = HCU_JsonEncode( $keep );

      } else {
        $returnRemoteInfo = $extRow["remote_info"];
      }



      $saveRow = array( "id" => $extRow["id"],
                        "user_id" => $extRow["user_id"],
                        "display_name" => $displayName,
                        "name_on_account" => $nameOnAccount,
                        "status" => $status,
                        "remote_info" => $returnRemoteInfo,
                        "remoteAccount" => $remoteInfo["rdfi"]["routing"] . " / " . ApplyMask($remoteInfo["rdfi"]["account"], $mask));

      $return[] = $saveRow;
    }

    $returnData["data"] = $return;
  } catch (Exception $ex) {
    $logInfo = array( "message" => $ex->getMessage(), "code" => $ex->getCode() );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );

    $returnData["code"] = "999";
  }

  return $returnData;
} // end EXT_GetAccounts


/**
 * Get the M2M accounts that have been configured for this user.
 *
 * @param integer $pDbh           -- Current database handle
 * @param array $pHBEnv         -- Current HB_ENV values
 *
 * @return array                  -- An array with the correct accounts
 */
function EXT_GetM2MAccounts( $pDbh, $pHBEnv ) {
  // initialize the return array
  $returnData = array( "code" => "000", "errors" => array(), "data" => array() );
  $pMC = $pHBEnv['MC'];

  try {
    // get the deposit accounts can deposit into and loans that can have payments made into
    $SQL = "SELECT *
            FROM {$pHBEnv["Cu"]}extaccount ea
            WHERE ea.user_id = {$pHBEnv["Uid"]}
              AND type = 'M2M'
            ORDER BY lower( display_name ), status, id
            ";
    $rs = db_query( $SQL, $pDbh );
    if ( !$rs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1134" );
    }

    // cycle through the rows
    $row = 0;
    $return = array();

    $mask= GetMask($pDbh, $pHBEnv["Cu"]);
    $mask= $mask["code"] != 0 ? array("start" => -2) : $mask["data"]; // Just return the default mask in this case.

    while ( $extRow = db_fetch_array( $rs, $row++ ) ) {
      $displayName = trim( $extRow["display_name"] );

      $remoteInfo = HCU_JsonDecode( $extRow["remote_info"]);

      $status = trim( $extRow["status"] );
      $returnRemoteInfo = $extRow["remote_info"];

      $saveRow = array( "id" => $extRow["id"],
                        "user_id" => $extRow["user_id"],
                        "display_name" => $displayName,
                        "status" => $status,
                        "remote_info" => $returnRemoteInfo,
                        "remoteAccount" => ApplyMask($remoteInfo["rdfi"]["account"], $mask));

      $return[] = $saveRow;
    }

    $returnData["data"] = $return;
  } catch (Exception $ex) {
    $logInfo = array( "message" => $ex->getMessage(), "code" => $ex->getCode() );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );

    $returnData["code"] = "999";
  }

  return $returnData;
} // end EXT_GetM2MAccounts


/**
 * Check the inputs for valid data. Note that some inputs are optional and not checked
 * because the inputs were sanitized.
 *
 * @param array $pInputVars       -- Current database handle
 *
 * @return array                  -- An array with success code or error information
 */
function EXT_ValidateInputs( $pHBEnv, $pDbh, $pInputVars, $pMC ) {
  // initialize the return array
  $aryReturnErrors = array();
  $retACHValidate = array( "code" => "000", "errors" => array(), "data" => array() );

  try {
    // can only check if name exists because we don't know if being saved or not
    if ( strlen( trim( $pInputVars["display_name"] ) ) == 0 ) {
      $aryReturnErrors = $pMC->msg("ACH Validation Name", HCU_DISPLAY_AS_HTML) . " 1119";
    }

    // make sure display name is unique (across all types of entries)
    $testDisplay = prep_save( html_entity_decode( $pInputVars["display_name"], ENT_QUOTES ), 20 );
    $sql = "SELECT count(*)
                FROM {$pHBEnv["Cu"]}extaccount
                WHERE user_id = {$pHBEnv["Uid"]}
                  AND lower( display_name ) = lower( '{$testDisplay}' )
                ";

    $rs = db_query( $sql, $pDbh );
    if ( !$rs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1135" );
    }

    $countRow = db_fetch_array( $rs, 0 );
    if ( $countRow[0] > 0 ) {
      $aryReturnErrors = $pMC->msg("ACH Unique display name required", HCU_DISPLAY_AS_HTML) . " 1125";
    }

    if ( trim( $pInputVars["dfi_routing"] ) == "" ||
         !ctype_digit( trim( $pInputVars["dfi_routing"] ) ) ||
         strlen( trim( $pInputVars["dfi_routing"] ) ) != 9 ) {
      $aryReturnErrors[] = $pMC->msg("ACH Validation DFI Routing") . " 1122";
    }

    if ( trim( $pInputVars["dfi_account"] ) == "" ) {
      $aryReturnErrors[] = $pMC->msg("ACH Validation DFI Account") . " 1121";
    }

    if ( !(trim( $pInputVars["dfi_account_type"] ) == ACCOUNT_TYPE_CHECKING ||
           trim( $pInputVars["dfi_account_type"] ) == ACCOUNT_TYPE_SAVINGS )) {
      $aryReturnErrors[] = $pMC->msg("ACH Validation DFI Type") . " 1120";
    }
  } catch (Exception $ex) {
    $message = $ex->getMessage();

    $aryReturnErrors[] = $message;
  }

  if ( count( $aryReturnErrors ) > 0 ) {
    // * validation errors occurred
    $retACHValidate['code'] = '999';
    $retACHValidate["errors"] = $aryReturnErrors;
  }

  return $retACHValidate;
} // end EXT_ValidateInputs


/**
 * Check the inputs for valid data. This is for M2M feature.
 *
 * @param array $pInputVars       -- Current database handle
 *
 * @return array                  -- An array with success code or error information
 */
function EXT_ValidateM2MInputs( $pHBEnv, $pDbh, $pInputVars, $pMC ) {
  // initialize the return array
  $aryReturnErrors = array();
  $retACHValidate = array( "code" => "000", "errors" => array(), "data" => array() );

  try {
    // can only check if name exists because we don't know if being saved or not
    if ( strlen( trim( $pInputVars["display_name"] ) ) == 0 ) {
      $aryReturnErrors = $pMC->msg("ACH Validation Name", HCU_DISPLAY_AS_HTML) . " 1127";
    }

    // make sure display name is unique (across all types of entries)
    $testDisplay = prep_save( html_entity_decode( $pInputVars["display_name"], ENT_QUOTES ), 20 );
    $sql = "SELECT count(*)
                FROM {$pHBEnv["Cu"]}extaccount
                WHERE user_id = {$pHBEnv["Uid"]}
                  AND lower( display_name ) = lower( '{$testDisplay}' )
                ";

    $rs = db_query( $sql, $pDbh );
    if ( !$rs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1136" );
    }

    $countRow = db_fetch_array( $rs, 0 );
    if ( $countRow[0] > 0 ) {
      $aryReturnErrors = $pMC->msg("ACH Unique display name required", HCU_DISPLAY_AS_HTML) . " 1128";
    }

    if ( trim( $pInputVars["dfi_account"] ) == "" ) {
      $aryReturnErrors[] = $pMC->msg("ACH Validation DFI Account") . " 1129";
    }

    if ( !(trim( $pInputVars["dfi_account_type"] ) == ACCOUNT_TYPE_CHECKING ||
           trim( $pInputVars["dfi_account_type"] ) == ACCOUNT_TYPE_SAVINGS )) {
      $aryReturnErrors[] = $pMC->msg("ACH Validation DFI Type") . " 1131";
    }
  } catch (Exception $ex) {
    $message = $ex->getMessage();

    $aryReturnErrors[] = $message;
  }

  if ( count( $aryReturnErrors ) > 0 ) {
    // * validation errors occurred
    $retACHValidate['code'] = '999';
    $retACHValidate["errors"] = $aryReturnErrors;
  }

  return $retACHValidate;
} // end EXT_ValidateM2MInputs


/**
 * Add an account with the initial status.
 *
 * @param array $pDbh             -- Current database handle
 * @param array $pHBEnv           -- Current server environment
 * @param array $pInputVars       -- Validated data from the client
 *
 * @return array                  -- An array with success code and extaccount.id or error information
 */
function EXT_AddAccount( $pDbh, $pHBEnv, $pInputVars, $pMC ) {
  // initialize the return array
  $returnData = array( "code" => "000", "errors" => array(), "data" => array() );

  try {
    // gather any information we need

    // add the record to the <cu>extaccount table
    $displayName = prep_save( html_entity_decode( $pInputVars["display_name"], ENT_QUOTES ), 20 );
    $nameOnAccount = prep_save( html_entity_decode( $pInputVars["name_on_account"], ENT_QUOTES ) );

    // set up the account info
    $remoteInfo = array( "rdfi" => array( "routing" => $pInputVars["dfi_routing"],
                                                               "account" => $pInputVars["dfi_account"],
                                                               "type" => $pInputVars["dfi_account_type"],
                                                               "name" => $nameOnAccount ) );

    $remoteInfoStr = HCU_JsonEncode( $remoteInfo );

    $sql = "INSERT INTO {$pHBEnv["Cu"]}extaccount (user_id, type, display_name, status, remote_info)
                VALUES ({$pHBEnv["Uid"]}, 'EXT', '$displayName', '0', '$remoteInfoStr')
                RETURNING id";

    $extRs = db_query( $sql, $pDbh );

    if ( !$extRs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1101" );
    }

    // get the id that was added
    list($extId) = db_fetch_array($extRs, 0);

    if ( !$extId ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1102" );
    }

    // return the id to the caller
    $returnData["data"]["id"] = $extId;

  } catch (Exception $ex) {
    // capture the exception
    $message = $ex->getMessage();
    $code = $ex->getCode();

    $logInfo = array( "message" => $message, "code" => $code );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );

    $returnData["code"] = "999";
    $returnData["errors"][] = $message;
  }

  return $returnData;
} // end EXT_AddAccount

/**
 * Add an M2M account with the initial status. This will validate the account using a MIR packet, so an entry in the
 *  table is an active account.
 *
 * @param array $pDbh             -- Current database handle
 * @param array $pHBEnv           -- Current server environment
 * @param array $pInputVars       -- Validated data from the client
 *
 * @return array                  -- An array with success code and extaccount.id or error information
 */
function EXT_AddM2MAccount( $pDbh, $pHBEnv, $pInputVars, $pMC ) {
  // initialize the return array
  $returnData = array( "code" => "000", "errors" => array(), "data" => array() );

  try {
    // call the core to see if this member is valid (throw exception if not)
    if ( _ConfirmM2MAccount( $pHBEnv, $pInputVars ) == false ) {
      throw new Exception( $pMC->msg("M2M Account Not Found", HCU_DISPLAY_AS_HTML) . " 1130" );
    }

    // gather any information we need

    // add the record to the <cu>extaccount table
    $displayName = prep_save( html_entity_decode( $pInputVars["display_name"], ENT_QUOTES ), 20 );
    $nameOnAccount = prep_save( html_entity_decode( $pInputVars["name_on_account"], ENT_QUOTES ) );

    // set up the account info
    $remoteInfo = array( "rdfi" => array( "account" => $pInputVars["dfi_account"],
                                                               "type" => $pInputVars["dfi_account_type"],
                                                               "name" => $nameOnAccount ),
                                      "verify" => array( "verified" => time() ) );

    $remoteInfoStr = HCU_JsonEncode( $remoteInfo );

    $sql = "INSERT INTO {$pHBEnv["Cu"]}extaccount (user_id, type, display_name, status, remote_info)
                VALUES ({$pHBEnv["Uid"]}, 'M2M', '$displayName', 'a', '$remoteInfoStr')
                RETURNING id";

    $extRs = db_query( $sql, $pDbh );

    if ( !$extRs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1101" );
    }

    // get the id that was added
    list($extId) = db_fetch_array($extRs, 0);

    if ( !$extId ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1102" );
    }

    // return the id to the caller
    $returnData["data"]["id"] = $extId;

  } catch (Exception $ex) {
    // capture the exception
    $message = $ex->getMessage();
    $code = $ex->getCode();

    $logInfo = array( "message" => $message, "code" => $code );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );

    $returnData["code"] = "999";
    $returnData["errors"][] = $message;
  }

  return $returnData;
} // end EXT_AddM2MAccount

/**
 * Call the core to get a MIR packet to validate if the given name is correct.
 *
 * @param array $pEnv                -- Current server environment
 * @param array $pInputVars       -- Validated data from the client
 *                                dfi_account - member number of the MIR packet we want to request
 *                                name_on_account  - name on the account
 *
 * @return array                  -- An array with success code and extaccount.id or error information
 */
function _ConfirmM2MAccount( $pEnv, $pInputVars ) {

  $isValid = false;
  $pMC = $pEnv['MC'];

  if ($pEnv['live'] == 0) {
    throw new Exception( $pMC->msg("Option not set", HCU_DISPLAY_AS_HTML) . " 1144" );
  }

  $mbrRequest = array( "type" => PACKET_REQUEST_MIR,
                                  "member" => $pInputVars["dfi_account"] );
  $memberInfo = GetMemberInfo( $pEnv, $mbrRequest );

  if ( $memberInfo["code"] === "000" ) {
    if ( isset( $memberInfo["data"]["lastname"] ) ) {
      // figure out what we are comparing in case it is shorter than the expected length
      // NOTE, if it is shorter, then need to match exactly, including length
      $testCompare = substr( $pInputVars["name_on_account"], 0, M2M_SIGNIFICANT_LEN );
      $compareLen = strlen( $testCompare );
      $compareExact = $compareLen < M2M_SIGNIFICANT_LEN;

      $actualName = substr( trim( $memberInfo["data"]["lastname"] ), 0,  M2M_SIGNIFICANT_LEN );

      if ( $compareExact ) {
        // do the length compare on what we got from the core
        $isValid = strcasecmp( $actualName, $testCompare ) == 0 && strlen( trim( $memberInfo["data"]["lastname"]) ) == $compareLen;
      } else {
        $isValid = strcasecmp( $actualName, $testCompare ) == 0;
      }
    }
  }

  return $isValid;


} // end _ConfirmM2MAccount

/**
 * Update just the names on the account. Return the name that was updated so the client-side has
 *  for sure what is in the database. Remember the encoding!
 *
 * @param array $pDbh             -- Current database handle
 * @param array $pHBEnv           -- Current server environment
 * @param array $pInputVars       -- Validated data from the client
 *
 * @return array                  -- An array with success code and extaccount.id or error information
 */
function EXT_UpdateAccount( $pDbh, $pHBEnv, $pInputVars, $pMC ) {
  // initialize the return array
  $returnData = array( "code" => "000", "errors" => array(), "data" => array() );

  try {
    // make sure display name is unique (across all types of entries)
    $testDisplay = prep_save( html_entity_decode( $pInputVars["display_name"], ENT_QUOTES ), 20 );
    $sql = "SELECT count(*)
                FROM {$pHBEnv["Cu"]}extaccount
                WHERE user_id = {$pHBEnv["Uid"]}
                  AND lower( display_name ) = lower( '{$testDisplay}' )
                  AND id <> {$pInputVars["id"]}
                ";

    $rs = db_query( $sql, $pDbh );
    if ( !$rs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1137" );
    }

    $countRow = db_fetch_array( $rs, 0 );
    if ( $countRow[0] > 0 ) {
      throw new Exception( $pMC->msg("ACH Unique display name required", HCU_DISPLAY_AS_HTML) . " 1126" );
    }

    // gather any information we need
    $sql = "SELECT ea.*
                FROM {$pHBEnv["Cu"]}extaccount ea
                WHERE ea.id = {$pInputVars["id"]}
                ";

    $rs = db_query( $sql, $pDbh );

    $extRow = db_fetch_array( $rs, 0 );

    if ( $extRow["id"] == 0 ) {
      throw new Exception( $pMC->msg("EXT Acct Status Error", HCU_DISPLAY_AS_HTML) . " 1103" );
    }

    // create the update statement
    $needUpdate = false;
    $sqlUpdate = "";

    // handle the types differently
    if ( $extRow["type"] == "EXT" ) {
      $remoteInfo = HCU_JsonDecode( $extRow["remote_info"]);

      if ( $remoteInfo["rdfi"]["name"] != $pInputVars["name_on_account"] ) {
        $returnNameOnAccount = html_entity_decode( $pInputVars["name_on_account"], ENT_QUOTES );
        $remoteInfo["rdfi"]["name"] = prep_save( $returnNameOnAccount );
        $updateRemoteInfo = HCU_JsonEncode( $remoteInfo );

        $needUpdate = true;

        if ( strlen( $sqlUpdate ) > 0 ) {
          $sqlUpdate .= ", ";
        }

        $sqlUpdate .= "remote_info = '$updateRemoteInfo' ";
      } else {
        $returnNameOnAccount = $remoteInfo["rdfi"]["name"];
      }
    }

    if ( $extRow["display_name"] != $pInputVars["display_name"] ) {
      $returnDisplayName = html_entity_decode( $pInputVars["display_name"], ENT_QUOTES );
      $displayName = prep_save( $returnDisplayName, 20 );
      $needUpdate = true;

      if ( strlen( $sqlUpdate ) > 0 ) {
        $sqlUpdate .= ", ";
      }

      $sqlUpdate .= "display_name = '$displayName' ";
    } else {
      // so returning a consistent value
      $returnDisplayName = $extRow["display_name"];
    }

    if ( $needUpdate ) {
      $sql = "UPDATE {$pHBEnv["Cu"]}extaccount SET $sqlUpdate WHERE id = {$pInputVars["id"]}";

      $extRs = db_query( $sql, $pDbh );

      if ( !$extRs ) {
        throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1104" );
      }
    }

    if ( $extRow["type"] == "EXT" ) {
      // return the updated record info (new status)
      $returnData["data"] = array( "id" => $pInputVars["id"],
                                                    "name_on_account" => $returnNameOnAccount,
                                                    "display_name" => $returnDisplayName
                                                  );
    } else {
      $returnData["data"] = array( "id" => $pInputVars["id"],
                                                    "display_name" => $returnDisplayName
                                                  );
    }
  } catch (Exception $ex) {
    // capture the exception
    $message = $ex->getMessage();
    $code = $ex->getCode();

    $logInfo = array( "message" => $message, "code" => $code );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );

    $returnData["code"] = "999";
    $returnData["errors"][] = $message;
  }

  return $returnData;
} // end EXT_UpdateAccount


/**
 * Validate the account by comparing the micro deposit amounts. Update status if successful.
 *
 * @param array $pDbh             -- Current database handle
 * @param array $pHBEnv           -- Current server environment
 * @param array $pInputVars       -- Validated data from the client
 *
 * @return array                  -- An array with success code and updated account info if successful.
 */
function EXT_ValidateAccount( $pDbh, $pHBEnv, $pInputVars, $pMC ) {
  // initialize the return array
  $returnData = array( "code" => "000", "errors" => array(), "data" => array() );

  try {
    if ( $pInputVars["id"] == 0 ) {
      throw new Exception( $pMC->msg("EXT Acct Status Error", HCU_DISPLAY_AS_HTML) . " 1105" );
    }

    $sql = "SELECT * FROM {$pHBEnv["Cu"]}extaccount WHERE id = {$pInputVars["id"]}";

    $extRs = db_query( $sql, $pDbh );

    $extRow = db_fetch_array( $extRs, 0 );

    if ( $extRow["id"] == 0 || ($extRow["status"] != "p" && $extRow["status"] != "l") ) {
      throw new Exception( $pMC->msg("EXT Acct Status Error", HCU_DISPLAY_AS_HTML) . " 1106" );
    }

    // get the saved micro amounts and retry count
    $remoteInfo = HCU_JsonDecode( $extRow["remote_info"]);

    if ( !isset( $remoteInfo["verify"] ) || ($remoteInfo["verify"]["micro1"] <= 0) || ($remoteInfo["verify"]["micro2"] <= 0) ) {
      throw new Exception( $pMC->msg("EXT Acct Status Error", HCU_DISPLAY_AS_HTML) . " 1107" );
    }

    // test the tries
    if ( $remoteInfo["verify"]["tries"] <= 0 || $extRow["status"] == "l" ) {
      throw new Exception( $pMC->msg("EXT Retry Attempt", HCU_DISPLAY_AS_HTML) . " 1108" );
    }

    // the saved smaller one in the first slot
    $savedMicro1 = $remoteInfo["verify"]["micro1"];
    $savedMicro2 = $remoteInfo["verify"]["micro2"];

    // don't know which order the micro amounts are coming, so compare smallest first
    $failedValidation = true;
    if ( $pInputVars["micro1"] < $pInputVars["micro2"] ) {
      if ( ( $savedMicro1 == $pInputVars["micro1"] ) &&
           ( $savedMicro2 == $pInputVars["micro2"] ) ) {
        $failedValidation = false;
      }
    } else {
      if ( ( $savedMicro1 == $pInputVars["micro2"] ) &&
           ( $savedMicro2 == $pInputVars["micro1"] ) ) {
        $failedValidation = false;
      }
    }

    if ( $failedValidation ) {
      // count down that they tried
      $remoteInfo["verify"]["tries"] -= 1;
      $remoteInfoStr = prep_save( HCU_JsonEncode( $remoteInfo ) );

      $returnStatus = $remoteInfo["verify"]["tries"] <= 0 ? "l" : $extRow["status"];

      // return as data (not error) so can show updated retry count on client
      if ( $returnStatus == "l" ) {
        $returnMessage = $pMC->msg("EXT Retry Attempt", HCU_DISPLAY_AS_HTML) . " 1123";
      } else {
        $returnMessage = $pMC->msg("EXT Acct validation fail", HCU_DISPLAY_AS_HTML) . " 1124";
      }
    } else {
      // update the record
      $returnStatus = "a";

      date_default_timezone_set("UTC");
      $remoteInfo["verify"] = array( "verified" => time() );
      $remoteInfoStr = prep_save( HCU_JsonEncode( $remoteInfo ) );


      $returnMessage = $pMC->msg("EXT Acct validation success", HCU_DISPLAY_AS_HTML);
    }

    $sql = "UPDATE {$pHBEnv["Cu"]}extaccount SET remote_info = '$remoteInfoStr', status = '$returnStatus'
                WHERE id = {$extRow["id"]}; ";

    $rs = db_query( $sql, $pDbh );

    // make sure the update worked
    if ( !$rs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1109" );
    }

    // return the updated record info (new status)
    $returnData["data"] = array( "id" => $pInputVars["id"],
                                                  "status" => $returnStatus,
                                                  "tries" => HCU_array_key_value( "tries", $remoteInfo["verify"] ),
                                                  "message" => $returnMessage
                                                );
  } catch (Exception $ex) {
    // capture the exception
    $message = $ex->getMessage();
    $code = $ex->getCode();

    $logInfo = array( "message" => $message, "code" => $code );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );

    $returnData["code"] = "999";
    $returnData["errors"][] = $message;
  }

  return $returnData;
} // end EXT_ValidateAccount


/**
 * Remove the account information from the database.
 *
 * @param array $pDbh             -- Current database handle
 * @param array $pHBEnv           -- Current server environment
 * @param array $pInputVars       -- Validated data from the client
 *
 * @return array                  -- An array with success code and extaccount.id or error information
 */
function EXT_DeleteAccount( $pDbh, $pHBEnv, $pInputVars, $pMC ) {
  // initialize the return array
  $returnData = array( "code" => "000", "errors" => array(), "data" => array() );

  try {
    if ( $pInputVars["id"] == 0 ) {
      throw new Exception( $pMC->msg("EXT Acct Status Error", HCU_DISPLAY_AS_HTML) . " 1110" );
    }

    // check for scheduled transfers that use this accounts
    // if any scheduled transfers use this accounts, do not allow delete.
    $sql = "
      SELECT * FROM cu_scheduledtxn WHERE cu = '{$pHBEnv['Cu']}' AND txn_data::json->'txn'->>'from'='{$pInputVars["id"]}'
      UNION
      SELECT * FROM cu_scheduledtxn WHERE cu = '{$pHBEnv['Cu']}' AND txn_data::json->'txn'->>'to'='{$pInputVars["id"]}'";
    $txnRs = db_query( $sql, $pDbh );
    if ( !$txnRs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1140" );
    }
    $txnData = db_fetch_all($txnRs);

    // if no scheduled transactions are found, continue with delete.
    // else throw error to user.
    if (!empty($txnData) && count($txnData) > 0) {
      throw new Exception($pMC->msg("EXT Error Delete Accounts Scheduled", HCU_DISPLAY_AS_HTML) . "1141");
    }

    // check for any non-scheduled transactions that have no been processed or cancelled
    // if any are found using this account, do not allow delete of external account.
    // the below conditions include micro deposit transactions.

    /**
     * in the hdr table, tere are two columns we need to look at to allow deletion of
     * an external account.
     *
     * approved status:
     *   approved = 10, declined = 90, cancelled = 99, pending = NULL
     * processed status:
     *   approved = 10, cancelled = 99, pending = NULL
     *
     * if approved status is NULL: user may not delete
     * if approved status is declined: user may delete
     * if approved status is cancelled: user may delete
     * if approved status is approved: look at processed status
     *
     * if processed status is NULL: user may not delete
     * if processed status is approved: user may delete
     * if processed status is cancelled: user may delete
     */
    $sql = "
      SELECT * FROM {$pHBEnv['Cu']}transhdr h
      LEFT JOIN {$pHBEnv['Cu']}transdtl d ON h.id = d.transhdr_id
      WHERE h.feature_code='TRNEXT'
        AND h.approved_status is NULL
        OR (h.approved_status=10 AND h.processed_status is NULL)
        AND
          CASE WHEN h.transactioncode='1P' OR h.transactioncode='1W'
          THEN d.transdata::json->'acct_dest'->'remote_entity'->>'entry_id'='{$pInputVars['id']}'
          ELSE d.transdata::json->'acct_source'->'remote_entity'->>'entry_id'='{$pInputVars['id']}'
          END;";
    $txnRs = db_query( $sql, $pDbh );
    if ( !$txnRs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1142" );
    }
    $txnData = db_fetch_all($txnRs);

    // if no transactions are found, continue with delete.
    // else throw error to user.
    if (!empty($txnData) && count($txnData) > 0) {
      throw new Exception($pMC->msg("EXT Error Delete Accounts", HCU_DISPLAY_AS_HTML) . " 1143");
    }

    $sql = "DELETE FROM {$pHBEnv["Cu"]}extaccount WHERE id = {$pInputVars["id"]}";
    $extRs = db_query( $sql, $pDbh );
    if ( !$extRs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1111" );
    }
  } catch (Exception $ex) {
    // capture the exception
    $message = $ex->getMessage();
    $code = $ex->getCode();

    $logInfo = array( "message" => $message, "code" => $code );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );

    $returnData["code"] = "999";
    $returnData["errors"][] = $message;
  }

  return $returnData;
} // end EXT_DeleteAccount


/**
 * Determine the micro deposit amounts and create the ACH transaction.
 *
 * @param array $pDbh             -- Current database handle
 * @param array $pHBEnv           -- Current server environment
 * @param array $pInputVars       -- Validated data from the client
 *
 * @return array                  -- An array with success code or error information
 */
function EXT_StartAccountVerify( $pDbh, $pHBEnv, $pInputVars, $pMC ) {
  // initialize the return array
  $returnData = array( "code" => "000", "errors" => array(), "data" => array() );

  try {
    // read the admin settings for ach cutoff and micro deposit offset
    $sql = "SELECT settings::json->>'offsetting' AS offsetting,
                   settings::json->>'cutoff' AS cutoff
            FROM cuadmin a
            WHERE cu = '{$pHBEnv["Cu"]}'";
    $sqlRs = db_query( $sql, $pDbh );
    if ( !$sqlRs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1131" );
    }

    $adminData = db_fetch_assoc( $sqlRs );

    $offsetMicroFlag = $adminData['offsetting'] === "null" ? 0 : $adminData['offsetting'];
    $achCutoffTime = $adminData['cutoff'] === "null" ? 0 : $adminData['cutoff'];

    // read the extaccount entry
    $sql = "SELECT ea.*
                FROM {$pHBEnv["Cu"]}extaccount ea
                WHERE ea.id = {$pInputVars["ext_acct_id"]}
                ";

    $rs = db_query( $sql, $pDbh );
    if ( !$rs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1132" );
    }

    $extRow = db_fetch_array( $rs, 0 );

    if ( $extRow["id"] == 0 || $extRow["status"] != "0" ) {
      throw new Exception( $pMC->msg("EXT Acct Status Error", HCU_DISPLAY_AS_HTML) . " 1112" );
    }

    $newStatus = "p";
    $remoteInfo = HCU_JsonDecode( $extRow["remote_info"]);

    // add the micro deposits (use whole numbers but treat as decimals)
    $microOne = rand( 10, 70 );
    $microTwo = rand( 10, 70 );

    $microOneSubmit = round( $microOne / 100, 2 );
    $microTwoSubmit = round( $microTwo / 100, 2 );
    $microSumSubmit = round( ($microOneSubmit + $microTwoSubmit), 2 );

    // put the smaller value in the 1st slot
    if ( $microOne < $microTwo ) {
      $microSave1 = $microOne;
      $microSave2 = $microTwo;
    } else {
      $microSave1 = $microTwo;
      $microSave2 = $microOne;
    }

    date_default_timezone_set("UTC");
    $remoteInfo["verify"] = array( "micro1" => $microSave1,
                                   "micro2" => $microSave2,
                                   "tries" => 5,
                                   "pending_date" => time() );

    $remoteInfoStr = prep_save( HCU_JsonEncode( $remoteInfo ) );

    $sql = "BEGIN TRANSACTION";
    $rs = db_query( $sql, $pDbh );

    $sql = "UPDATE {$pHBEnv["Cu"]}extaccount SET remote_info = '$remoteInfoStr', status = 'p'
                WHERE id = {$extRow["id"]}; ";

    $rs = db_query( $sql, $pDbh );
    if ( !$rs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1138" );
    }

    // set up the query for the transaction header - do deposits and withdrawal separately
    // get the effective date
// unresolved - need to handle the cutoff time
    // the account for the micro deposit is determined on the Admin side when ach file is built
    $effDate = date( "Y-m-d" );
    $featureCode = FEATURE_EXTERNAL_TRANSFERS;
    $transCode = "1P";    // credit, PPD
    $sql = "INSERT INTO {$pHBEnv["Cu"]}transhdr (feature_code, effective_date,
                 posted_by, posted_date, approved_by, approved_date, approved_status,
                 transactioncode, memo)
               VALUES ('{$featureCode}', '{$effDate}',
                 {$pHBEnv["Uid"]}, now(), {$pHBEnv["Uid"]}, now(), 10,
                 '$transCode', 'External Account Activate' )
               RETURNING id";

    $hdrRs = db_query( $sql, $pDbh );

    if ( !$hdrRs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1113" );
    }

    // use the insert id from the header in the detail
    list($headerId) = db_fetch_array($hdrRs, 0);

    if ( !$headerId ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1114" );
    }

    // "rdfi" is the remote dfi - this is the same for each transaction detail
    $achData = array( "rdfi" => array( "rdfi_routing" => $remoteInfo["rdfi"]["routing"],
                                      "rdfi_account" => $remoteInfo["rdfi"]["account"],
                                      "rdfi_account_type" => $remoteInfo["rdfi"]["type"],
                                      "rdfi_txn_type" => "CR",
                                      "addenda" => "" ),
                      "remote_entity" => array( "name" => $remoteInfo["rdfi"]["name"],
                                                "entry_id" => $extRow["id"],
                                                "display_name" => $extRow["display_name"] ) );

    // set up the transfer data (matching how TRNEXT saves info)
    $transData = array( "acct_source" => "micro", "acct_dest" => $achData );

    $jsonTransData = prep_save( HCU_JsonEncode( $transData ) );

    // use the partner id for now
    $referenceId = $pHBEnv["Uid"];

    // add the transaction detail
    // reference_id is a quasi-foreigh key to either the ach partner, external accounts, or wire partner table
    $sql = "INSERT INTO {$pHBEnv["Cu"]}transdtl (transhdr_id, reference_id, amount, email_notify, transdata)
               VALUES ($headerId, $referenceId, $microOneSubmit, 0, '$jsonTransData');";
    $sql .= "INSERT INTO {$pHBEnv["Cu"]}transdtl (transhdr_id, reference_id, amount, email_notify, transdata)
               VALUES ($headerId, $referenceId, $microTwoSubmit, 0, '$jsonTransData')";

    $dtlRs = db_query( $sql, $pDbh );

    if ( !$dtlRs ) {
      throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1115" );
    }

    // see if configured for offsetting debit
    if ( $offsetMicroFlag ) {
  // unresolved - should this be the next business day after the deposits? Or when the deposit is confirmed?
      $transCode = "2P";          // debit, PPD
      // keep the memo under 30
      $sql = "INSERT INTO {$pHBEnv["Cu"]}transhdr (feature_code, effective_date,
                   posted_by, posted_date, approved_by, approved_date, approved_status,
                   transactioncode, memo)
                 VALUES ('{$featureCode}', '{$effDate}',
                   {$pHBEnv["Uid"]}, now(), {$pHBEnv["Uid"]}, now(), 10,
                   '$transCode', 'Ext Acct Activate Offset' )
                 RETURNING id";

      $hdrRs = db_query( $sql, $pDbh );

      if ( !$hdrRs ) {
        throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1116" );
      }

      // use the insert id from the header in the detail
      list($headerId) = db_fetch_array($hdrRs, 0);

      if ( !$headerId ) {
        throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1117" );
      }

      // set up the transfer data to debit from the remote account (matching how TRNEXT saves info)
      $achData["rdfi"]["rdfi_txn_type"] = "DB";
      $transData = array( "acct_source" => $achData, "acct_dest" => "micro" );

      $jsonTransData = prep_save( HCU_JsonEncode( $transData ) );

      // add the transaction detail (all the same values but the amount)
      $sql = "INSERT INTO {$pHBEnv["Cu"]}transdtl (transhdr_id, reference_id, amount, email_notify, transdata)
                 VALUES ($headerId, $referenceId, $microSumSubmit, 0, '$jsonTransData')";

      $dtlRs = db_query( $sql, $pDbh );

      if ( !$dtlRs ) {
        throw new Exception( $pMC->msg("ACH Query Error", HCU_DISPLAY_AS_HTML) . " 1118" );
      }
    }

    // commit the transaction
    $sql = "COMMIT TRANSACTION";
    $achRs = db_query( $sql, $pDbh );

    // return success

  } catch (Exception $ex) {
    // capture the exception
    $message = $ex->getMessage();
    $code = $ex->getCode();

    // roll back the transaction
    $sql = "ROLLBACK TRANSACTION";
    db_query( $sql, $pDbh );

    $logInfo = array( "message" => $message, "code" => $code );
    $pHBEnv["SYSENV"]["logger"]->error( HCU_JsonEncode( $logInfo ) );

    $returnData["code"] = "999";
    $returnData["errors"][] = $message;
  }

  return $returnData;

} // end EXT_StartAccountVerify