hcuTransferScheduled.i

<?php
/**
 * @package hcuTransferScheduled.i
 * @author  MGHandy
 * @uses    this is a global script used for creating/
 * updating and deleting scheduled transactions. this script
 * facilitates in retrieving the available scheduled transfer
 * interval list as well as calculating the next triggerdate for
 * a scheduled transfer.
 *
 * Original creation date: 3/17/17
 *
 * NOTES:
 * 1. This file also has the core code to enter the transfer into the database and to update the database to signify the
 *    transfer has been approved. The file should be renamed to hcuTransfer.i because it isn't particular to scheduled
 *    transfers.
 */

/**
 * TxnSchedRead:
 * @uses read all repeating scheduled transfers
 *
 * @param $pEnv     - HB_ENV values
 * @param $pDbh     - database object
 * @param $pMC      - dictionary object
 *
 * @return $retStatusAry - status of request and list
 * of current scheduled transfers
 */
function TxnSchedRead($pEnv, $pDbh, $pMC) {
  $retStatusAry = Array(
    'status' => Array('code'=>'000', 'errors' => Array()),
    'data' => ''
  );

  /**
   * select all data from cu_scheduledtxn
   */
  $sqlColumns = "
    id AS tx_id,
    cu AS tx_cu,
    feature_code AS tx_feature,
    start_date AS tx_start,
    end_date AS tx_end,
    next_trigger_date AS tx_next,
    interval_count AS tx_interval_count,
    status AS tx_status,
    approved_status AS tx_approved,
    repeating_parameters AS tx_parameters,
    txn_data AS tx_data";
  $sqlSelect = "
    SELECT $sqlColumns
    FROM cu_scheduledtxn
    WHERE cu = '{$pEnv['Cu']}'
      AND user_id = '{$pEnv['Uid']}'";
  $sqlSelectRs = db_query($sqlSelect, $pDbh);
  if (!$sqlSelectRs) {
    $retStatusAry['status']['code'] = "999";
    $retStatusAry['status']['severity'] = "ERROR";
    $retStatusAry['status']['errors'][] = $pMC->msg("Transfers Unavailable", HCU_DISPLAY_AS_RAW);
  } else {
    // ** must iterate scheduled transactions to get the account names
    // ** create new object array to include from/to account descriptions
    $sqlResult = db_fetch_all($sqlSelectRs);
    $sqlData = array();
    if ($sqlResult != null) {
      foreach ($sqlResult as $key => $value) {

        // decode json txn_data to get to/from/record types
        $txData = HCU_JsonDecode($value['tx_data']);
        $txParam = HCU_JsonDecode($value['tx_parameters']);
        $txAmount = $txData['txn']['amount'];
        $txMemo = $txData['txn']['memo'];
        $txFromDesc = "";
        $txToDesc = "";
        $txFromSuffix = "";
        $txToSuffix = "";

        // if external/m2m/ach then need to get partner information
        if ( trim($value['tx_feature']) == "ACHCOL" ) {
          $txFromDesc = _GetACHPartnerDisplay( $pEnv, $txData['txn']['from'] );
          $txFromSuffix = "AC|{$pEnv['Uid']}|{$txData['txn']['from']}";

          $acctParts = explode( "|", $txData['txn']['to'] );
          $certNumber = isset( $acctParts[3] ) && $acctParts[3] > 0 ? $acctParts[3] : 0;
          $txToDesc = FindAccountDisplay( $pEnv, $pDbh, $pEnv['Cu'], $acctParts[1], $acctParts[2], $acctParts[0], $certNumber);
          $txToSuffix = $txData['txn']['to'];

       } else if ( trim($value['tx_feature']) == "ACHPMT" ) {
          $txToDesc = _GetACHPartnerDisplay( $pEnv, $txData['txn']['to'] );
          $txToSuffix = "AP|{$pEnv['Uid']}|{$txData['txn']['to']}";

          $acctParts = explode( "|", $txData['txn']['from'] );
          $certNumber = isset( $acctParts[3] ) && $acctParts[3] > 0 ? $acctParts[3] : 0;
          $txFromDesc = FindAccountDisplay( $pEnv, $pDbh, $pEnv['Cu'], $acctParts[1], $acctParts[2], $acctParts[0], $certNumber);
          $txFromSuffix = $txData['txn']['from'];
        } else if ( trim($value['tx_feature']) == "TRNEXT" ) {
          // need to figure out direction
          if ( $txData["txn"]["type"] == "X2L" ) {
            // external to local
            $txFromDesc = _GetExtPartnerDisplay( $pEnv, $txData['txn']['from'] );
            $txFromSuffix = "X|{$pEnv['Uid']}|{$txData['txn']['from']}";

            $acctParts = explode( "|", $txData['txn']['to'] );
            $certNumber = isset( $acctParts[3] ) && $acctParts[3] > 0 ? $acctParts[3] : 0;
            $txToDesc = FindAccountDisplay( $pEnv, $pDbh, $pEnv['Cu'], $txData['txn']['tomember'], $txData['txn']['tosuffix'], $txData['txn']['totype'], $certNumber);
            $txToSuffix = $txData['txn']['to'];
          } else {
            $txToDesc = _GetExtPartnerDisplay( $pEnv, $txData['txn']['to'] );
            $txToSuffix = "X|{$pEnv['Uid']}|{$txData['txn']['to']}";

            $acctParts = explode( "|", $txData['txn']['from'] );
            $certNumber = isset( $acctParts[3] ) && $acctParts[3] > 0 ? $acctParts[3] : 0;
            $txFromDesc = FindAccountDisplay( $pEnv, $pDbh, $pEnv['Cu'], $txData['txn']['frommember'], $txData['txn']['fromsuffix'], $txData['txn']['fromtype'], $certNumber);
            $txFromSuffix = $txData['txn']['from'];
          }
        } else if ( trim($value['tx_feature']) == "TRNM2M" ) {
          // only local to member allowed
            $txToDesc = _GetExtPartnerDisplay( $pEnv, $txData['txn']['to'] );
            $txToSuffix = "M|{$pEnv['Uid']}|{$txData['txn']['to']}";

            $acctParts = explode( "|", $txData['txn']['from'] );
            $certNumber = isset( $acctParts[3] ) && $acctParts[3] > 0 ? $acctParts[3] : 0;
            $txFromDesc = FindAccountDisplay( $pEnv, $pDbh, $pEnv['Cu'], $txData['txn']['frommember'], $txData['txn']['fromsuffix'], $txData['txn']['fromtype'], $certNumber);
            $txFromSuffix = $txData['txn']['from'];
        } else {
          // internal to internal

          $acctPartsTo = explode( "|", $txData['txn']['to'] );
          $acctPartsFrom = explode( "|", $txData['txn']['from'] );

          $txFromDesc = FindAccountDisplay($pEnv, $pDbh, $pEnv['Cu'], $txData['txn']['frommember'], $txData['txn']['fromsuffix'], $txData['txn']['fromtype'], isset($acctPartsFrom[3]) ? $acctPartsFrom[3] : 0);
          $txFromSuffix = $txData['txn']['from'];

          /*
           * Cross Account Lookup
           * if the transactioncode is in {XA, XP} then supply differnet values for looking up the Account Description
           */
          if (in_array($txData['txn']['transactioncode'], array("XA", "XP"))) {
            /*
             * Trying this logic.  I would like to skip needing to make a call to TX_List for the account information
             * Cross Account Transfers need to be made within the same "Member" account.  They can not cross over to a
             * different member number source.
             * Therefore, I should be able to use the 'frommember' as the member number to lookup in the useraccounts table.
             */
            $localToMbr = $txData['txn']['frommember'];
            $localToSfx = $txData['txn']['tosuffix'] . '#' . $txData['txn']['tomember'];
            $localToCert = 0;   // ** Always 0 for Cross Account Lookup
          } else {
            $localToMbr = $txData['txn']['tomember'];
            $localToSfx = $txData['txn']['tosuffix'];
            $localToCert = (isset($acctPartsTo[3]) ? $acctPartsTo[3] : 0);
          }

          $txToDesc = FindAccountDisplay($pEnv, $pDbh, $pEnv['Cu'], $localToMbr, $localToSfx, $txData['txn']['totype'], $localToCert);
          $txToSuffix = $txData['txn']['to'];
        }

        // determine the feature name
        $feature = trim( $value['tx_feature'] );
        if ( $feature == "TRN" ) {
          $featureName = $pMC->msg("Transfer", HCU_DISPLAY_AS_RAW);
        } else  if ( $feature == "TRNEXT" ) {
          $featureName = $pMC->msg("External Transfer", HCU_DISPLAY_AS_RAW);
        } else  if ( $feature == "TRNM2M" ) {
          $featureName = $pMC->msg("M2M Transfer", HCU_DISPLAY_AS_RAW);
        } else  if ( $feature == "ACHPMT" ) {
          $featureName = $pMC->msg("ACH Payment", HCU_DISPLAY_AS_RAW);
        } else  if ( $feature == "ACHCOL" ) {
          $featureName = $pMC->msg("ACH Collection", HCU_DISPLAY_AS_RAW);
        } else {
          $featureName = $pMC->msg("Unknown", HCU_DISPLAY_AS_RAW);
        }

        // return the data
        $sqlData[] = array(
          "txId" => intval($value['tx_id']),
          "txFeature" => $featureName,
          "txFromDesc" => trim($txFromDesc),
          "txFromSuffix" => trim($txFromSuffix),
          "txToDesc" => trim($txToDesc),
          "txToSuffix" => trim($txToSuffix),
          "txAmount" => floatval($txAmount),
          "txMemo" => trim($txMemo),
          "txStatus" => trim($value['tx_status']),
          "txApproved" => intval($value['tx_approved']),
          "txFrequency" => trim($txParam['interval']),
          "txFrequencyCount" => intval($value['tx_interval_count']),
          "txDateStart" => trim($value['tx_start']),
          "txDateEnd" => trim($value['tx_end']),
          "txDateNext" => trim($value['tx_next']),
          "txContinue" => ($value['tx_end'] == null || ($value['tx_end'] == $value['tx_start'])) ?
            "continuous" :
            "continueuntil"
        );
      }
    }

    $retStatusAry['status']['code'] = "000";
    $retStatusAry['status']['severity'] = "SUCCESS";
    $retStatusAry['data'] = $sqlData;
  }

  return $retStatusAry;
}

/**
 * TxnSchedCreate:
 * @uses create a repeating scheduled transfer
 *
 * @param $pEnv     - HB_ENV values
 * @param $pDbh     - database object
 * @param $pMC      - dictionary object
 * @param $pValues  - transaction values
 * @param $pFeature - feature code to test for permissions
 *
 * @return $retStatusAry - status of request
 */
function TxnSchedCreate($pEnv, $pDbh, $pMC, $pValues) {
  $retStatusAry = Array(
    'status' => Array('code'=>'000', 'errors' => Array()),
    'data' => ''
  );
  /**
   * must retrieve permission for this feature
   * when creating a scheduled record, the approval cannot
   * be set if the user needs confirmation from another
   * source.
   */
  $confirmInputs = array("feature" => $pValues['txFeature']);
  $confirmRequired = Perm_CheckConfirmReq($pDbh, $pEnv, $confirmInputs);
  /**
   * REQUIRED / NOT NULLABLE
   *
   * cu, feature_code, user_id
   */
  $featureCode = $pValues['txFeature'];
  $cu = prep_save($pEnv['Cu'], 10);
  $userID = intval($pEnv['Uid']);
  $name = "NULL";
  /**
   * NULLABLE FIELDS
   *
   * create_date, next_trigger_date, start_date, end_date
   * approved_date, approved_by
   * last_edit_date, last_edit_by
   * interval_count, failure_count
   * status,
   * repeating_parameters, txn_data
   */

  $created = "CURRENT_TIMESTAMP";

  $trigger = "NULL";
  $trigger = TxNextInterval(
    $pValues['txDateStart'],
    $pValues['txFrequency'],
    $pValues['txFrequencyCount']);
  $trigger = $trigger ? "'" . $trigger . "'" : "NULL";

  // ** the initial trigger date when the schedule is created.
  // * we set the start date to the first initial trigger date
  // * not the value from txDateStart.

  if ( $pValues['txFrequency'] == "OneTime" ) {
    $end = "'{$pValues['txDateStart']}'";
  } else {
    $end = $pValues['txDateEnd'] == "" ? "NULL" : "'{$pValues['txDateEnd']}'";
  }

  // ** check permissions, set null if confirmation is needed
  $approvedDate = $confirmRequired ? "NULL" : "CURRENT_TIMESTAMP" ;
  $approvedBy = $confirmRequired ? "NULL" : $userID ;
  $approvedStatus = $confirmRequired ? 0 : 10;

  // ** creation is the first edit, set to this user
  $editedDate = $created;
  $editedBy = $userID;

  // ** number of times the intervals have been attempted
  $intervalCount = 0;
  $intervalFailure = 0;

  // ** active or inactive scheduled transfer,
  // ** initially active on create.
  $status = "'A'";

  // ** repeating_parameters used to create transhdr entry
  $parameters = "'{$pValues['txParameters']}'";
  // ** txn_data used to create transdtl entry
  $data = "'{$pValues['txData']}'";

  // ** build sql insert query
  $sqlColumns = "
    cu, name,
    feature_code,
    user_id,
    create_date,
    approved_by,
    approved_date,
    last_edit_by,
    last_edit_date,
    start_date,
    end_date,
    next_trigger_date,
    interval_count,
    failure_count,
    status,
    repeating_parameters,
    txn_data,
    approved_status";
  $sqlValues = "
    '$cu', $name,
    '$featureCode',
    $userID,
    $created,
    $approvedBy,
    $approvedDate,
    $editedBy,
    $editedDate,
    $trigger,
    $end,
    $trigger,
    $intervalCount,
    $intervalFailure,
    $status,
    $parameters,
    $data,
    $approvedStatus";
  $sqlCreate = "INSERT INTO cu_scheduledtxn
    ($sqlColumns)
    VALUES ($sqlValues)";
  $sqlCreateRs = db_query($sqlCreate, $pDbh);
  if (!$sqlCreateRs) {
    $retStatusAry['status']['code'] = "999";
    $retStatusAry['status']['severity'] = "ERROR";
    $retStatusAry['status']['errors'][] = $pMC->msg("Transfer Save Error", HCU_DISPLAY_AS_RAW);
  }

  return $retStatusAry;
}

/**
 * TxnSchedUpdate:
 * @uses update a repeating scheduled transfer
 *
 * @param $pEnv     - HB_ENV values
 * @param $pDbh     - database object
 * @param $pMC      - dictionary object
 * @param $pValues  - transaction object to update
 *
 * @return $retStatusAry - status of request
 */
function TxnSchedUpdate($pEnv, $pDbh, $pMC, $pValues) {
  $retStatusAry = Array(
    'status' => Array('code'=>'000', 'errors' => Array()),
    'data' => ''
  );
  /**
   * must retrieve permission for this feature
   * when creating a scheduled record, the approval cannot
   * be set if the user needs confirmation from another
   * source.
   */
  $confirmInputs = array("feature" => $pValues['txFeature']);
  $confirmRequired = Perm_CheckConfirmReq($pDbh, $pEnv, $confirmInputs);
  /**
   * UPDATABLE FIELDS
   *
   * approved_by, approved_date
   * last_edit_by, last_edit_date
   * start_date, end_date, next_trigger_date
   * status,
   * repeating_parameters, txn_data
   */
  $id = $pValues['txId'];
  $cu = prep_save($pEnv['Cu'], 10);

  // ** altered start/end/trigger dates
  $start = $pValues['txDateStart'] == "" ? "NULL" : "'{$pValues['txDateStart']}'";
  if ( $pValues['txFrequency'] == "OneTime" ) {
    $end = "'{$pValues['txDateStart']}'";
  } else {
    $end = $pValues['txDateEnd'] == "" ? "NULL" : "'{$pValues['txDateEnd']}'";
  }

  $trigger = "NULL";
  $trigger = TxNextInterval(
    $pValues['txDateStart'],
    $pValues['txFrequency'],
    $pValues['txFrequencyCount']);
  $trigger = $trigger ? "'" . $trigger . "'" : "NULL";

  // ** setup interval count
  $intervalCount = intval($pValues['txFrequencyCount']);

  // ** check permissions, set null if confirmation is needed
  $approvedDate = $confirmRequired ? "NULL" : "CURRENT_TIMESTAMP" ;
  $approvedBy = $confirmRequired ? "NULL" : intval($pEnv['Uid']) ;

  // ** creation is the first edit, set to this user
  $editedBy = intval($pEnv['Uid']);
  $editedDate = "CURRENT_TIMESTAMP";

  // ** active or inactive scheduled transfer
  $status = "'{$pValues['txStatus']}'";

  // ** repeating_parameters used to create transhdr entry
  $parameters = "'{$pValues['txParameters']}'";
  // ** txn_data used to create transdtl entry
  $data = "'{$pValues['txData']}'";

  // ** build sql insert query
  $sqlColumns = "
    approved_by,
    approved_date,
    last_edit_by,
    last_edit_date,
    start_date,
    end_date,
    next_trigger_date,
    interval_count,
    status,
    repeating_parameters,
    txn_data";
  $sqlValues = "
    $approvedBy,
    $approvedDate,
    $editedBy,
    $editedDate,
    $start,
    $end,
    $trigger,
    $intervalCount,
    $status,
    $parameters,
    $data";
  $sqlUpdate = "
    UPDATE cu_scheduledtxn
    SET ($sqlColumns) = ($sqlValues)
    WHERE cu = '$cu'
      AND id = $id";
  $sqlUpdateRs = db_query($sqlUpdate, $pDbh);
  if (!$sqlUpdateRs) {
    $retStatusAry['status']['code'] = "999";
    $retStatusAry['status']['severity'] = "ERROR";
    $retStatusAry['status']['errors'][] = $pMC->msg("Transfer Save Error", HCU_DISPLAY_AS_RAW);
  }

  return $retStatusAry;
}

/**
 * TxnSchedDelete:
 * @uses delete a repeating scheduled transfer
 *
 * @param $pEnv     - HB_ENV values
 * @param $pDbh     - database object
 * @param $pMC      - dictionary object
 * @param $pValues  - transaction object to remove
 *
 * @return $retStatusAry - status of request
 */
function TxnSchedDelete($pEnv, $pDbh, $pMC, $pValues) {
  $retStatusAry = Array(
    'status' => Array('code'=>'000', 'errors' => Array()),
    'data' => ''
  );

  /*
   * DELETE by CU and ID
   */
  $id = $pValues['txId'];
  $cu = prep_save($pEnv['Cu'], 10);

  // ** build sql statement
  $sqlDelete = "
    DELETE FROM cu_scheduledtxn
    WHERE cu = '$cu'
      AND id = $id";
  $sqlDeleteRs = db_query($sqlDelete, $pDbh);
  if (!$sqlDeleteRs) {
    $retStatusAry['status']['code'] = "999";
    $retStatusAry['status']['severity'] = "ERROR";
    $retStatusAry['status']['errors'][] = $pMC->msg("Transfer Delete Fail", HCU_DISPLAY_AS_RAW);
  }

  return $retStatusAry;
}

/**
 * TxnSchedParameters:
 * @uses generate a json list of paraeters which will be stored in the
 * cu_scheduledtxn table and used to generate the next trigger date.
 *
 * @param $pValues - transaction data, containing the transaction information
 *
 * @return $paramSTRING - json string of parameters
 */
function TxnSchedParameters($pValues) {
  $paramJSON = array(
    "interval" => $pValues['txFrequency']
  );
  $paramSTRING = HCU_JsonEncode($paramJSON);
  return $paramSTRING;
}

/**
 * TxnSchedData:
 * @uses generate a json list of data which will be stored in the cu_scheduledtxn
 * table and used to generate entries for the (cu)transhrd/dtl tables for
 * internal transactions.
 *
 * @param $pValues - transaction data, containing the transaction information
 *
 * @return $dataSTRING - json string of data
 */
function TxnSchedData( $pValues ) {
  // see what type of operation is occuring
  if ( $pValues["txFromType"] == 'X' || $pValues["txToType"] == 'X' ||
       $pValues["txToType"] == 'M' ) {
    // external or M2M, now figure out direction
    if ( $pValues["txFromType"] == 'X' ) {
      // from external to local
      $fromParts = explode( "|", $pValues["txFromAcctId"] );
      $from = $fromParts[2];
      $frommember = "";
      $fromsuffix = "";
      $fromtype = $pValues["txMemType"];
      $to = $pValues['txToAcctId'];
      $tomember = $pValues['txToMember'];
      $tosuffix = $pValues['txToSuffix'];
      $type = "X2L";
    } else {
      // from local to external/m2m
      $from = $pValues['txFromAcctId'];
      $frommember = $pValues['txFromMember'];
      $fromsuffix = $pValues['txFromSuffix'];
      $fromtype = $pValues['txFromType'];
      $toParts = explode( "|", $pValues["txToAcctId"] );
      $to = $toParts[2];    // this is the extacct table id
      $tomember = "";
      $tosuffix = "";
      $type = $pValues["txToType"] == 'M' ? "L2M" : "L2X";
    }

    $dataJSON = array(
    "txn" => array(
      "from" => $from,
      "frommember" => $frommember,
      "fromsuffix" => $fromsuffix,
      "fromtype" => $fromtype,
      "to" => $to,
      "tomember" => $tomember,
      "tosuffix" => $tosuffix,
      "totype" => $pValues['txToType'],
      "amount" => $pValues['txAmount'],
      "transactioncode" => $pValues['txCode'],
      "include_memo" => "",                                     // future need - include memo in ach addenda
      "memo" => prep_save($pValues['txMemo']),
      "type" => $type                                                 // this is really just to make it easier to understand when looking at database table and because TRNEXT txns can go either direction
    )
  );
} else {

    // Slight difference with cross accounts.
    $tomember = $pValues['txToMember'];
    if ($pValues["txTrust"] == "transfer") {
      $toParts = explode( "|", $pValues["txToAcctId"] );
      $tomember = $toParts[1];
    }

    // traditional internal transfer
    $dataJSON = array(
      "txn" => array(
        "from" => $pValues['txFromAcctId'],
        "frommember" => $pValues['txFromMember'],
        "fromsuffix" => $pValues['txFromSuffix'],
        "fromtype" => $pValues['txFromType'],
        "to" => $pValues['txToAcctId'],
        "tomember" => $tomember,
        "tosuffix" => $pValues['txToSuffix'],
        "totype" => $pValues['txToType'],
        "amount" => $pValues['txAmount'],
        "transactioncode" => $pValues['txCode'],
        "deposittype" => $pValues['txFromType'],
        "memo" => prep_save($pValues['txMemo'])
      )
    );
  }


  $dataSTRING = HCU_JsonEncode($dataJSON);
  return $dataSTRING;
} // end TxnSchedData

/**
 * TxnSchedDataACH:
 * @uses generate a JSON list of data which will be stored in the cu_scheduledtxn
 * table and used to generate entries for the (cu)transhdr/dtl tables for
 * ACH transactions.
 *
 * @param $pValues - transaction data, containing the transaction information
 *
 * @return $dataSTRING - JSON string of data
 */
function TxnSchedDataACH( $pValues ) {

  $dataJSON = array(
    "txn" => array(
      "amount" => $pValues['txAmount'],
      "transactioncode" => $pValues['txCode'],
      "include_memo" => "", // future need - include memo in ach addenda
      "memo" => prep_save($pValues['txMemo']),
      "addenda" => $pValues['txAddenda'],
      "type" => $pValues['txType']
    )
  );

  $acctId = explode("|", $pValues['txLocalAccount']);
  if ($pValues['txType'] == "L2R") {
    $dataJSON['txn']['from'] = $pValues['txLocalAccount'];
    $dataJSON['txn']['frommember'] = $pValues['txLocalMember'];
    $dataJSON['txn']['fromsuffix'] = $acctId[2];  // account type
    $dataJSON['txn']['fromtype'] = $acctId[0];    // record type
    $dataJSON['txn']['to'] = $pValues['txPartnerId'];
  } else {
    $dataJSON['txn']['to'] = $pValues['txLocalAccount'];
    $dataJSON['txn']['tomember'] = $pValues['txLocalMember'];
    $dataJSON['txn']['tosuffix'] = $acctId[2];  // account type
    $dataJSON['txn']['totype'] = $acctId[0];    // record type
    $dataJSON['txn']['from'] = $pValues['txPartnerId'];
  }

  $dataSTRING = HCU_JsonEncode($dataJSON);
  return $dataSTRING;
} // end TxnSchedDataACH

/**
 * TxIntervalList:
 * @uses retrieve a list of all possible scheduled transfer
 * intervals
 *
 * @param $MC -- represents the dictionary object
 * @return list of possible scheduled transfer intervals
 */
function TxIntervalList($MC) {
  return Array(
    Array("value" => "OneTime", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED OneTime"))),
    Array("value" => "FirstDayOfMonth", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED FirstDayOfMonth"))),
    Array("value" => "LastDayOfMonth", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED LastDayOfMonth"))),
    Array("value" => "FirstFifteenth", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED FirstFifteenth"))),
    Array("value" => "FifteenthLast", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED FifteenthLast"))),
    Array("value" => "Weekly", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED Weekly"))),
    Array("value" => "BiWeekly", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED BiWeekly"))),
    Array("value" => "WeekDayOfMonth", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED WeekDayOfMonth"))),
    Array("value" => "Monthly", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED Monthly"))),
    Array("value" => "Every2Months", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED Every2Months"))),
    Array("value" => "Quarterly", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED Quarterly"))),
    Array("value" => "SemiAnnually", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED SemiAnnually"))),
    Array("value" => "Annually", "text" => str_replace("#", "\\#", $MC->msg("TRNSCHED Annually")))
  );
}

/**
 * TxContinueList:
 * @uses return a list of the "Continue Until" choices to end the repeating transfer.
 *
 * @param $MC -- represents the dictionary object
 * @return list of possible ending choices.
 */
function TxContinueList($MC) {
  return Array(
            Array("value" => "continuous", "text" => $MC->msg('Transfer Continue Until', HCU_DISPLAY_AS_RAW) . ' ' .$MC->msg('Transfer Further Notice', HCU_DISPLAY_AS_RAW)),
            Array("value" => "continueuntil", "text" => $MC->msg('Transfer Continue Until', HCU_DISPLAY_AS_RAW))
          );
}

/**
 * TxNextInterval:
 * @uses calculate the next trigger date for a particular transaction
 *
 * @param $pStart - calculate the next trigger date starting at this date
 * @param $pEnd - next trigger date cannot go past this date
 * @param $pInterval - the interval for which to calculate the next trigger date.
 *
 * @return $dateTrigger - next trigger date: will be null if calculation is past the pEnd date.
 */
function TxNextInterval($pStart, $pInterval, $pIntervalCount) {

  $dateTrigger = null;
  $dateStart = strtotime($pStart);

  // set current day/month/year
  $cDay = date("j", $dateStart);
  $cMonth = date("m", $dateStart);
  $cYear = date("Y", $dateStart);
  $cInterval = intval($pIntervalCount);

  // ** switch interval for calculation
  switch ($pInterval) {
    case "OneTime":
      // ** Increment the Next Trigger date
      // ** One time only runs once, incrementing the interval should have NO impact.
      $dateTrigger = date("m/d/Y", $dateStart + (($cInterval > 0 ? 1 : 0) * 86400));
      break;
    case "FirstDayOfMonth":
      /* if the starting day is greater than 1
       * increment the month to  next month.
       *
       * always add the interval to the month
       * to ensure the correct month based on
       * how many intervals have already occurred.
       */
      $cMonth = ($cDay > 1) ? $cMonth + 1 : $cMonth;
      $cMonth = $cMonth + $cInterval;

      $nextFirst = mktime(0,0,0, $cMonth, 1, $cYear);
      $nextFirst = date("m/d/Y", $nextFirst);
      $dateTrigger = $nextFirst;
      break;
    case "LastDayOfMonth":
      /* since we are looking for the last day of the month
       * there is no need to add one prior to adding the interval
       */
      $cMonth = $cMonth + $cInterval;

      $nextLast = mktime(0,0,0, $cMonth, 1, $cYear);
      $nextLast = date("m/t/Y", $nextLast);
      $dateTrigger = $nextLast;
      break;
    case "FirstFifteenth":
      /**
       * if the interval count is greater than 0
       * calculate the next trgger date off of the start date
       * plus the number of intervals.
       *
       * start with by finding the initial date
       * being a 1st or 15th of the month
       *
       * if the current date is 1
       *    set day to 15 for next iteration
       * if the current date is 15
       *    set day to 1 for next iteration
       *    set month + 1 for next iteration
       *
       * this should run  for the total number of intervals that
       * have actually occurred in order to get the correct next
       * trigger date.
       */

      $startDay = ($cDay > 15 || $cDay == 1) ? 1 : 15;
      $startMonth = $cMonth + ($cDay > 15);

      while ($cInterval > 0) {
        if ($startDay == 1) {
          $startDay = 15;
        } else {
          $startDay = 1;
          $startMonth ++;
          if ($startMonth > 12) {
            $startMonth = 1;
            $cYear ++;
          }
        }

        $cInterval --;
      }

      $nextDate = mktime(0,0,0, $startMonth, $startDay, $cYear);
      $dateTrigger = date("m/d/Y", $nextDate);

      break;
    case "FifteenthLast":
      /**
       * if the interval count is greater than 0
       * calculate the next trgger date off of the start date
       * plus the number of intervals.
       *
       * start with by finding the initial date
       * being a 15th or last day of the month
       *
       * if the current date is 15
       *    set day to last day for next iteration
       * if the current date is last day
       *    set day to 15 for next iteration
       *    set month + 1 for next iteration
       *
       * this should run  for the total number of intervals that
       * have actually occurred in order to get the correct next
       * trigger date.
       */
      $startDay = ($cDay <= 15) ? 15 : date("t", $dateStart);
      $startMonth = $cMonth;

      while ($cInterval > 0) {
        if ($startDay == 15) {
          $startDay = strtotime($startMonth . "/1/" . $cYear);
          $startDay = date("t", $startDay);
        } else {
          $startDay = 15;
          $startMonth ++;
          if ($startMonth > 12) {
            $startMonth = 1;
            $cYear ++;
          }
        }

        $cInterval --;
      }

      $nextDate = mktime(0,0,0, $startMonth, $startDay, $cYear);
      $dateTrigger = date("m/d/Y", $nextDate);

      break;
    case "Weekly":
      /* one week is 7 days, multiply this by each interval */
      $cDay = $cDay + (7 * $cInterval);

      $nextWeek = mktime(0,0,0, $cMonth, $cDay, $cYear);
      $nextWeek = date("m/d/Y", $nextWeek);
      $dateTrigger = $nextWeek;
      break;
    case "BiWeekly":
      /* two weeks is 14 days, multiply this by each interval */
      $cDay = $cDay + (14 * $cInterval);

      $nextBiWeek = mktime(0,0,0, $cMonth, $cDay, $cYear);
      $nextBiWeek = date("m/d/Y", $nextBiWeek);
      $dateTrigger = $nextBiWeek;
      break;
    case "WeekDayOfMonth":
      // e.g. third thursday of the month
      // e.g. fouth saturday of the month
      $weekDay = date("l", $dateStart);
      $week = ceil($cDay / 7);

      $weekNumber = "";
      switch ($week) {
        case 1:
          $weekNumber = "first";
          break;
        case 2:
          $weekNumber = "second";
          break;
        case 3:
          $weekNumber = "third";
          break;
        case 4:
          $weekNumber = "fourth";
          break;
        case 5:
          $weekNumber = "last";
          break;
      }

      $monthStart = mktime(0,0,0, $cMonth + $cInterval, 1, $cYear);
      $monthName = date("M", $monthStart);
      $monthYear = date("Y", $monthStart);

      // format of this string: first saturday of june 2017
      $nextDayOfMonth = "";
      $nextDayOfMonth .= $weekNumber . " ";
      $nextDayOfMonth .= $weekDay . " of ";
      $nextDayOfMonth .= $monthName . " ";
      $nextDayOfMonth .= $monthYear;

      $nextDay = strtotime($nextDayOfMonth);
      $dateTrigger = date("m/d/Y", $nextDay);
      break;
    case "Monthly":
      /* add interval to get correct month */
      $cMonth = $cMonth + $cInterval;

      $nextMonth = mktime(0,0,0, $cMonth, 1, $cYear);
      $nextMonthDays = date("t", $nextMonth);
      $nextDay = null;
      if ($cDay > $nextMonthDays) {
        $nextDay = $nextMonthDays;
      } else {
        $nextDay = $cDay;
      }

      $nextDayOfMonth = mktime(0,0,0, $cMonth, $nextDay, $cYear);
      $nextDayOfMonth = date("m/d/Y", $nextDayOfMonth);
      $dateTrigger = $nextDayOfMonth;
      break;
    case "Every2Months":
      $cMonth = $cMonth + (2 * $cInterval);
      while ($cMonth > 12) {
        $cMonth = $cMonth - 12;
        $cYear ++;
      }

      $nextTwoMonth = mktime(0,0,0, $cMonth, 1, $cYear);
      $nextTwoMonthDays = date("t", $nextTwoMonth);
      $nextDay = null;
      if ($cDay > $nextTwoMonthDays) {
        $nextDay = $nextTwoMonthDays;
      } else {
        $nextDay = $cDay;
      }

      $nextTwo = mktime(0,0,0, $cMonth, $nextDay, $cYear);
      $nextTwo = date("m/d/Y", $nextTwo);
      $dateTrigger = $nextTwo;
      break;
    case "Quarterly":
      $cMonth = $cMonth + (3 * $cInterval);
      while ($cMonth > 12) {
        $cMonth = $cMonth - 12;
        $cYear ++;
      }

      $nextThreeMonth = mktime(0,0,0, $cMonth, 1, $cYear);
      $nextThreeMonthDays = date("t", $nextThreeMonth);
      $nextDay = null;
      if ($cDay > $nextThreeMonthDays) {
        $nextDay = $nextThreeMonthDays;
      } else {
        $nextDay = $cDay;
      }

      $nextQuarter = mktime(0,0,0, $cMonth, $nextDay, $cYear);
      $nextQuarter = date("m/d/Y", $nextQuarter);
      $dateTrigger = $nextQuarter;
      break;
    case "SemiAnnually":
      $cMonth = $cMonth + (6 * $cInterval);
      while ($cMonth > 12) {
        $cMonth = $cMonth - 12;
        $cYear ++;
      }

      $nextSixMonth = mktime(0,0,0, $cMonth, 1, $cYear);
      $nextSixMonthDays = date("t", $nextSixMonth);
      $nextDay = null;
      if ($cDay > $nextSixMonthDays) {
        $nextDay = $nextSixMonthDays;
      } else {
        $nextDay = $cDay;
      }

      $nextSemi = mktime(0,0,0, $cMonth, $nextDay, $cYear);
      $nextSemi = date("m/d/Y", $nextSemi);
      $dateTrigger = $nextSemi;
      break;
    case "Annually":
      /* add interval to get correct year */
      $cYear = $cYear + $cInterval;

      $nextAnnual = mktime(0,0,0, $cMonth, 1, $cYear);
      $nextAnnualDays = date("t", $nextAnnual);
      $nextDay = null;
      if ($cDay > $nextAnnualDays) {
        $nextDay = $nextAnnualDays;
      } else {
        $nextDay = $cDay;
      }

      $nextAnnual = mktime(0,0,0, $cMonth, $nextDay, $cYear);
      $nextAnnual = date("m/d/Y", $nextAnnual);
      $dateTrigger = $nextAnnual;
      break;
  }


  return $dateTrigger;
}


// Get the partner info from the ach partner table.
function _GetACHPartnerDisplay( $pEnv, $pPartnerId ) {
  $Cu = $pEnv["Cu"];

  $sql = "SELECT ap.display_name
          FROM {$Cu}achpartner ap
          WHERE ap.id = $pPartnerId";

  $rs = db_query( $sql, $pEnv["dbh"] );
  $row = db_fetch_assoc( $rs );
  $name = "ACH: {$row["display_name"]}";

  return $name;
} // end _GetACHPartnerDisplay

// Get the partner info from the extaccount table.
function _GetExtPartnerDisplay( $pEnv, $pPartnerId ) {
  $Cu = $pEnv["Cu"];

  $sql = "SELECT ea.display_name, type
              FROM {$Cu}extaccount ea
              WHERE ea.id = $pPartnerId";

  $rs = db_query( $sql, $pEnv["dbh"] );
  $row = db_fetch_assoc( $rs );
  $name = "{$row["type"]}:  {$row["display_name"]}";

  return $name;
} // end _GetExtPartnerDisplay

function FindTransactionData($pDbh, $pId) {
  $sqlSelect = "
    SELECT txn_data, feature_code, start_date, repeating_parameters
    FROM cu_scheduledtxn
    WHERE id = $pId";
  $sqlSelectRs = db_query($sqlSelect, $pDbh);
  $sqlTxn = db_fetch_assoc($sqlSelectRs, 0);

  return $sqlTxn;
}

/**
 * GetCalendarData:
 * @uses this function servs to create arrays to hold all dates for
 * repeating transactions upto a specified date. the function also
 * creates an array of key/value pairs where each key is a date value
 * from the first array with values being each txId associated with it.
 *
 * @param pHBEnv - environment variable: HB_ENV values
 * @param pMC - dictionary object
 * @param pSchedules - list of scheduled repeating transfers
 * @param pStart - the list of transfers will contain dates >= to this
 * @param pEnd - the list of transfers will contain dates up to this
 *
 * @return scheduledReturn - list of dates and key/value pairs
 */
function GetCalendarData($pHBEnv, $pMC, $pSchedules, $pStart, $pEnd) {
  /*
   * Create Schedule of Dates for the repeating transfers.
   *
   * Starting tomorrow going until $pMonths months from tomorrow create a list of dates
   * each scheduled transfer is requested
   *
   * start schedule tomorrow
   * end the schedule $pMonths months from tomorrow
   */
  $sdDateStart = $pStart;
  $sdTimeStart = strtotime($pStart);
  $sdDateEnd = $pEnd;
  $sdTimeEnd = strtotime($pEnd);

  /**
   * scheduledDates:
   * - list of all dates which have a transfer occurence
   *
   * scheduledOccur:
   * - list of key/value pairs
   * - keys = scheduledDates
   * - values = txid's associated with the key dates
   */
  $scheduleDates = array();
  $scheduleOccur = array();
  $scheduleValid = array();
  $scheduleReturn = array();

  // ** iterate schedules from tx read
  foreach ($pSchedules as $key => $value) {
    $txStatus = $value['txStatus'];
    $txApproved = $value['txApproved'];
    $txInterval = $value['txFrequency'];
    $txIntervalCount = $value['txFrequencyCount'];

    // get record next trigger date and end date in date and time formats
    // start the next date on the start date to include the start date in
    // the list
    $txTimeNext = strtotime($value['txDateNext']);
    $txDateNext = date("m/d/Y", $txTimeNext);

    $txDateEnd = null;
    $txTimeEnd = null;

    // set end time for this schedule if one exists
    // set end time to user defined end date if none exists
    if ($value['txDateEnd'] != "") {
      $txTimeEnd = strtotime($value['txDateEnd']);
      $txDateEnd = date("m/d/Y", $txTimeEnd);

    } else {
      $txDateEnd = $sdDateEnd;
      $txTimeEnd = $sdTimeEnd;
    }

    // ** Check for Inactive -- ONLY calculate for ACTIVE
    if ($txStatus == "A" && ($txApproved == 10 || $txApproved == 0)) {

      /*
       * evaluate records that have a trigger date greater or equal to tomorrow ($scheduleStart)
       */
      if ($txTimeNext >= $sdTimeStart) {
        /**
         * get next trigger dates for this record up to the scheduleEnd date
         * IntervalCalculate will retun null when the date to calculate from is greater
         * than the end date.
         *
         * start date will be the next calculated trigger date
         * end date will be the $scheduleEnd date or the end date of the record
         */
        while ($txDateNext != null) {
          $scheduleDates[] = returnJsonDateValue($txDateNext);
          $scheduleOccur[$txDateNext][] = $value['txId'];
          $txDateNext = TxNextInterval($txDateNext, $txInterval, $txIntervalCount);

          // if one time, break loop
          if ($txInterval == "OneTime") {
            break;
          }

          // if next date is after cutoff date
          // break loop.
          if (strtotime($txDateNext) < $txTimeEnd) {
            break;
          }
        }
      }
    }

    $scheduleValid[] = $value;
  }

  $scheduleReturn['valid'] = $scheduleValid;
  $scheduleReturn['dates'] = $scheduleDates;
  $scheduleReturn['occur'] = $scheduleOccur;
  return $scheduleReturn;
} // end GetCalendarData

/**
 * function SkipTrans($dbh, $Cu, $userId, $changeList, $MC)
 * This skips all selected scheduled transactions forward to the next trigger date (if it exists.)
 *
 * @param $dbh -- the database connection
 * @param $Cu -- the credit union
 * @param $userId -- the banking user
 * @param $changeList -- a JSON list of ids to change
 * @param $MC -- the dictionary (since I am using a DDL from the banking side, I need this to translate into English.)
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- "" if successful, nonempty otherwise
 * @return "repeatingTransfers" -- a list of repeating transfers
 * @return "info" -- if successful, give a success message
 */
function SkipTrans($dbh, $Cu, $userId, $changeList, $MC) {
  try {
    if ($changeList == "") {
      throw new exception("Nothing to skip.", 201);
    }
    $changeList = HCU_JsonDecode($changeList);
    if (!is_array($changeList)) {
      throw new exception("Change List is malformed.", 202);
    }
    if (count($changeList) == 0) {
      throw new exception("Nothing to skip.", 207);
    }
    foreach($changeList as $changeListId) {
      if (!is_integer($changeListId)) {
        throw new exception("Change List is malformed.", 203);
      }
    }

    // SQL to check that records are valid for skipping.
    $sql = "select id, approved_status, repeating_parameters::json->>'interval' as interval, interval_count, status, next_trigger_date, end_date, start_date from cu_scheduledtxn
      where cu = '$Cu' and user_id = $userId and id in (" . implode(", ", $changeList) . ")";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("Select query failed.", 209);
    }
    $updateList = array();
    for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
      if ($row["status"] != "A") {
        throw new exception("Skips can only happen on active records.", 204);
      }
      if ($row["approved_status"] != 10) {
        throw new exception("Skips can only happen on approved records.", 205);
      }
      $id = $row["id"];

      if (!isset($row["next_trigger_date"])) {
        throw new exception("Skips cannot happen on records with a null next trigger date.", 209);
      }

      $nextInterval = TxNextInterval($row["start_date"], $row["interval"], $row["interval_count"] + 1);

      if (isset($nextInterval)) {
        $nextInterval = explode("/", $nextInterval);
        $nextInterval = "'" . $nextInterval[2] . "-" . $nextInterval[0] . "-" . $nextInterval[1] . "'"; // Get into the format needed for the database.
      } else {
        $nextInterval = "null";
      }

      if ($nextInterval != "null" && isset($row["end_date"]) && $nextInterval > "'" . $row["end_date"] . "'") {
        $nextInterval = "null";
      }
      $updateList[$id] = "($id, $nextInterval)";
    }
    if (count($updateList) != count($changeList)) { // Some ids do not exist or are attached to the wrong user/Cu.
      throw new exception("Some ids in the changeList are invalid.", 206);
    }
    unset($changeList);

    $sql = "update cu_scheduledtxn set next_trigger_date = b.next_trigger_date::date, interval_count = interval_count + 1
        from (values " . implode(", ", $updateList) . ") as b (id, next_trigger_date) where cu_scheduledtxn.id = b.id";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("Update query failed.", 208);
    }

    $readResults = ReadTrans($dbh, $Cu, $userId, $MC);

    if ($readResults["status"] != "000") {
      throw new exception($readResults["error"][0], $readResults["code"]);
    }
    $repeatingTransfers = $readResults["repeatingTransfers"];
    unset($readResults);

    $returnArray = array("status" => "000", "error" => "", "repeatingTransfers" => $repeatingTransfers,
    "info" => (count($updateList) == 1 ? "Transfer was " : "Transfers were ") . "skipped successfully.");
  } catch(exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }
  return $returnArray;
}

/**
 * function ReadTrans($dbh, $Cu, $userId, $MC)
 * This reads the scheduled transactions for the banking user that are approved.
 *
 * @param $dbh -- the database connection
 * @param $Cu -- the credit union
 * @param $userId -- the banking user
 * @param $MC -- the dictionary (since I am using a DDL from the banking side, I need this to translate into English.)
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- "" if successful, nonempty otherwise
 * @return "repeatingTransfers" -- a list of repeating transfers
 */
function ReadTrans($dbh, $Cu, $userId, $MC) {
  try {
    $intervalList = TxIntervalList($MC);
    $intervalLookup = array();
    foreach($intervalList as $intervalRecord) {
      $intervalLookup[$intervalRecord["value"]] = $intervalRecord["text"];
    }
    unset($intervalList);

    // Then once the list of the accounts is retrieved from the primary user, then the rights for the subaccounts are retrieved from the actual user.
    $subqueryTXN = "select ss.id, ss.feature_code, ss.feature, ss.create_date, ss.start_date, ss.end_date, ss.next_trigger_date, ss.status, ss.repeating_parameters,
      ss.txn_data, ss.failure_count, case when strpos(ss.fromkey, '|') = 0 then cast(ss.fromkey as text)
      when coalesce(strpos(ss.fromsuffix, '@'), '0') <> 0 then trim(split_part(fromkey, '|', 1)) || trim(ss.frommember)
        || trim(ss.fromsuffix) || coalesce(nullif(split_part(fromkey, '|', 4), ''), '0')
      else trim(split_part(fromkey, '|', 1)) || trim(split_part(fromkey, '|', 2)) || trim(split_part(fromkey, '|', 3)) || coalesce(nullif(split_part(fromkey, '|', 4), ''), '0') end
        as fromkey,
      case when strpos(ss.tokey, '|') = 0 then cast(ss.tokey as text)
      when coalesce(strpos(ss.tosuffix, '@'), 0) <> 0 then trim(split_part(tokey, '|', 1)) || trim(ss.tomember) || trim(ss.tosuffix) || coalesce(nullif(split_part(tokey, '|', 4), ''), '0')
      when ss.code not in ('XA', 'XP') then trim(split_part(tokey, '|', 1)) || trim(split_part(tokey, '|', 2)) || trim(split_part(tokey, '|', 3)) ||
      coalesce(nullif(split_part(tokey, '|', 4), ''), '0')
      else case when trim(split_part(tokey, '|', 1)) = 'D' then 'T' else 'P' end || frommember || trim(split_part(tokey, '|', 3)) || '#' || trim(split_part(tokey, '|', 2))
        || coalesce(nullif(split_part(tokey, '|', 4), ''), '0') end as tokey
      from (
        select s.id, s.feature_code, f.description as feature, s.create_date, s.start_date, s.end_date, s.next_trigger_date, s.status, s.repeating_parameters, s.txn_data,
        s.failure_count,
        replace(s.txn_data::json->'txn'->>'from', 'C|', 'L|') as fromkey,
        replace(s.txn_data::json->'txn'->>'to', 'C|', 'L|') as tokey,
        s.txn_data::json->'txn'->>'transactioncode' as code,
        s.txn_data::json->'txn'->>'frommember' as frommember, s.txn_data::json->'txn'->>'fromsuffix' as fromsuffix, s.txn_data::json->'txn'->>'tomember' as tomember,
        s.txn_data::json->'txn'->>'tosuffix' as tosuffix
        from cu_scheduledtxn s
        inner join cu_feature f on s.feature_code = f.feature_code and s.cu = '$Cu' and f.enabled and approved_status = 10
        where s.user_id = $userId
      ) ss";

    $subqueryALXB = "select 'D' || trim(accountnumber) || trim(accounttype) || certnumber as key, may_deposit, may_withdraw,
      accountnumber, accounttype, certnumber, description
        from ${Cu}accountbalance
        union all
        select 'T' || trim(accountnumber) || trim(accounttype) || '#' || trim(tomember) || '0' as key, true, false, tomember, accounttype, 0, description
        from ${Cu}crossaccounts where deposittype not in ('L', 'C')
        union all
        select 'L' || trim(accountnumber) || trim(loannumber) || '0' as key, may_payment, may_addon, accountnumber, loannumber, 0, description
        from ${Cu}loanbalance
        union all
        select 'P' || trim(accountnumber) || trim(accounttype) || '#' || trim(tomember) || '0' as key, false, false, tomember, accounttype, 0, description
        from ${Cu}crossaccounts where deposittype in ('L', 'C')";

    $sql = "select txn.*, alxbfrom.description as from_desc, alxbfrom.accountnumber as from_acct, alxbfrom.accounttype as from_accounttype,
          alxbfrom.certnumber as from_certnumber, alxbto.description as to_desc, alxbto.accountnumber as to_acct,
          alxbto.accounttype as to_accounttype, alxbto.certnumber as to_certnumber from ($subqueryTXN) txn
          left join ($subqueryALXB) as alxbfrom on txn.fromkey::text = alxbfrom.key::text
          left join ($subqueryALXB) as alxbto on txn.tokey::text = alxbto.key::text
          order by txn.next_trigger_date";

    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("Select query failed.", 101);
    }
    $repeatingTransfers = array();

    $achcols = array();
    $achpmts = array();
    $exts = array();

    for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
          switch($row["feature_code"]) {
              case "ACHCOL":
                  $achcols[] = $row["id"];
                  break;
              case "ACHPMT":
                  $achpmts[] = $row["id"];
                  break;
              case "TRNEXT":
              case "TRNM2M":
                  $exts[] = $row["id"];
                  break;
          }

      $repeatingTransfers[] = $row;
    }

      $sql = array();
        $achMap = array();
        if (count($achcols) > 0) {
          $sql[] = "select s.id as data_id, ap.ach_name, ap.display_name, ap.address, ap.dfi_data from cu_scheduledtxn s
               inner join ${Cu}achpartner ap on s.feature_code = 'ACHCOL' and cast(s.txn_data::json->'txn'->>'from' as int) = ap.id where s.id in (" . implode(", ", $achcols) . ")";
        }
        if (count($achpmts) > 0) {
          $sql[] = "select s.id as data_id, ap.ach_name, ap.display_name, ap.address, ap.dfi_data from cu_scheduledtxn s
               inner join ${Cu}achpartner ap on s.feature_code = 'ACHPMT' and cast(s.txn_data::json->'txn'->>'to' as int) = ap.id where s.id in (" . implode(", ", $achpmts) . ")";
        }

        if (count($sql) > 0) {
          $sql = implode(" union all ", $sql);
          $sth = db_query($sql, $dbh);
          if (!$sth) {
            throw new exception("ach map query failed.", 307);
          }
          for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
            $row["dfi_data"] = HCU_JsonDecode($row["dfi_data"]);
            $row["address"] = HCU_JsonDecode($row["address"]);

            $achMap[$row["data_id"]] = $row;
          }
        }

        $sql = array();
        $extMap = array();
        if (count($exts) > 0) {
          $sql[] = "select s.id as data_id, 'from' as dir, ea.display_name, ea.remote_info from cu_scheduledtxn s
              inner join ${Cu}extaccount ea on s.feature_code in ('TRNEXT') and s.txn_data::json->'txn'->>'type' in ('X2L') and cast(s.txn_data::json->'txn'->>'from' as int) = ea.id
              and ea.type = 'EXT' where s.id in (" . implode(", ", $exts) . ")"; // Member-to-member can only come from the local account.
          $sql[] = "select s.id as data_id, 'to', ea.display_name, ea.remote_info from cu_scheduledtxn s
              inner join ${Cu}extaccount ea on s.feature_code in ('TRNEXT', 'TRNM2M') and s.txn_data::json->'txn'->>'type' in ('L2X', 'L2M') and replace(s.feature_code, 'TRN', '') = ea.type
              and cast(s.txn_data::json->'txn'->>'to' as int) = ea.id where s.id in (" . implode(", ", $exts) . ")";
        }

        if (count($sql) > 0) {
          $sql = implode(" union all ", $sql);
          $sth = db_query($sql, $dbh);
          if (!$sth) {
            throw new exception("ext map query failed.", 308);
          }
          for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
            $row["remote_info"] = HCU_JsonDecode($row["remote_info"]);

            $extMap[$row["data_id"]] = $row;
          }
        }

    $mask = GetMask($dbh, $Cu)["data"];
    for($i = 0, $iCount = count($repeatingTransfers); $i != $iCount; $i++) {
      $row = $repeatingTransfers[$i];

      $repeating = isset($row["repeating_parameters"]) ? trim($row["repeating_parameters"]) : "";
      if ($repeating != "") {
        $repeating = HCU_JsonDecode($repeating);
        if (!is_array($repeating)) {
          throw new exception("Repeating is malformed.", 102);
        }
        if (!HCU_array_key_exists("interval", $repeating)) {
          throw new exception("Repeating is malformed.", 103);
        }
        $row["interval"] = $repeating["interval"];
        $row["intervalText"] = HCU_array_key_exists($row["interval"], $intervalLookup) ? $intervalLookup[$row["interval"]] : "";
      }
      unset($row["repeating_parameters"]);

      $txnData = isset($row["txn_data"]) ? trim($row["txn_data"]) : "";
      $flagset3 = GetFlagsetValue('CU3_PREPEND_MBR_DESC');
      $row["details"] = ""; // Ensure that this always exists.
      if ($txnData != "") {
        $txnData = HCU_JsonDecode($txnData);
        if (!is_array($txnData)) {
          throw new exception("TxnData is malformed.", 104);
        }

        // Truncated version of online banking activity templates.  Normally, kendo takes care of the entities but I am turning encoded off on the column to allow for <br>.
        switch($row["feature_code"]) {
          case "TRN":
            $row["details"] = "<b>From</b> " .
                getAccountDescription($dbh, $Cu, $row["from_acct"], $row["from_desc"], $row["from_accounttype"], "", $flagset3, $row["from_certnumber"])
              . "<br> <b>To</b> " .
              getAccountDescription($dbh, $Cu, $row["to_acct"], $row["to_desc"], $row["to_accounttype"], "", $flagset3, $row["to_certnumber"]);
            break;
          case "ACHCOL":
            $partnerRow = $achMap[$row["id"]];
            $routing = trim($partnerRow["dfi_data"]["dfi_routing"]);
            $account = trim($partnerRow["dfi_data"]["dfi_account"]);
            $from = $routing . " / " . ApplyMask($account, $mask);
            $row["details"] = "<b>From</b> $from "
              . "<br> <b>To</b> " . getAccountDescription($dbh, $Cu, $row["to_acct"], $row["to_desc"], $row["to_accounttype"], "", $flagset3, $row["to_certnumber"]);
            break;
          case "ACHPMT":
            $partnerRow = $achMap[$row["id"]];
            $routing = trim($partnerRow["dfi_data"]["dfi_routing"]);
            $account = trim($partnerRow["dfi_data"]["dfi_account"]);
            $to = $routing . " / " . ApplyMask($account, $mask);
            $row["details"] = "<b>From</b> " .
                getAccountDescription($dbh, $Cu, $row["from_acct"], $row["from_desc"], $row["from_accounttype"], "", $flagset3, $row["from_certnumber"])
              . "<br> <b>To</b> $to ";
            break;
          case "TRNM2M":
            $extAccount = $extMap[$row["id"]];
            $account = trim($extAccount["remote_info"]["rdfi"]["account"]);
            $type = intval($extAccount["remote_info"]["rdfi"]["type"]);
            $type = $type == 10 ? "Checking" : "Savings";
            $to = ApplyMask($account, $mask) . " / $type";

            $row["details"] = "<b>From</b> " .
                getAccountDescription($dbh, $Cu, $row["from_acct"], $row["from_desc"], $row["from_accounttype"], "", $flagset3, $row["from_certnumber"])
              . "<br> <b>To</b> $to";
            break;
          case "TRNEXT":
            $extAccount = $extMap[$row["id"]];
            $fromLabel = $extAccount["dir"] == "from" ? "ACH From Account" : "From";
            $toLabel = $extAccount["dir"] == "to" ? "ACH To Account" : "To";

              if ($extAccount["dir"] == "from") {
                  $routing = trim($extAccount["remote_info"]["rdfi"]["routing"]);
                  $account = trim($extAccount["remote_info"]["rdfi"]["account"]);
                  $from = $routing . " / " . ApplyMask($account, $mask);
                  $to = getAccountDescription($dbh, $Cu, $row["to_acct"], $row["to_desc"], $row["to_accounttype"], "", $flagset3, $row["to_certnumber"]);
              } else {
                  $from = getAccountDescription($dbh, $Cu, $row["from_acct"], $row["from_desc"], $row["from_accounttype"], "", $flagset3, $row["from_certnumber"]);
                  $routing = trim($extAccount["remote_info"]["rdfi"]["routing"]);
                  $account = trim($extAccount["remote_info"]["rdfi"]["account"]);
                  $to = $routing . " / " . ApplyMask($account, $mask);
              }
              $row["details"] = "<b>From</b> $from <br> <b>To</b> $to";
              break;
          default:
            break;
        }

        $row["details"] .= "<br> <b>Amount</b> \$" . money_format('%.2n', $txnData["txn"]["amount"]);
      }


      unset($row["txn_data"]);

      $row["statusText"] = $row["status"] == "A" ? "Active" : "Inactive";
      $repeatingTransfers[$i] = $row;
    }
    $returnArray = array("status" => "000", "error" => "", "repeatingTransfers" => $repeatingTransfers);
  } catch(exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }
  return $returnArray;
}