schedliveproc.php

#!/usr/local/bin/php
<?php

#use LWP::Simple qw(get $ua);
//use LWP::UserAgent;

//$ua = LWP::UserAgent->new(
//  ssl_opts => { SSL_verify_mode => 'SSL_VERIFY_NONE'},
//);

# pgliverecur.php
#
# Fetches packets for live homebanking members with recurring transactions
#
# If provided, -p packetstamp is used as the fetch 'if_mod_since' parameter
# Otherwise, if_mod_since is set to current timestamp - 24 hours
# Fetcher cutoff is set to 'if_mod_since' - 1 day
#
# Loads alert tables (alertus, alertab, alertah, alertlb, alertlh) with data
#   alertus contains user info for members with active repeating transactions
#   alertab, alertah, alertlb, alertlh have homebanking data
#
# Processes recurring transactions and notifies members
#
// ** SET HOMECU FLAGS
    $serviceMinimal = true;
    $serviceShowInfo = false;
    $serviceLoadMenu = false;
    $serviceShowMenu = false;
    $serviceLoadCuInfo = false;

  // ** INCLUDE MAIN GLOBAL SCRIPT -- Handles security / global variable values
  // hcuService will be returning a status object: e.g. ["homecuErrors":{[{"message":"message1"}...{"message":"messageN"}}]
  require_once('/var/www/html/banking/library/hcuService.i');

  require_once('/var/www/html/shared/library/sAPIAppl.i');
  require_once('/var/www/html/shared/library/sAPIAppl.std.i');
  require_once('/var/www/html/banking/library/hcuTransferScheduled.i');
  require_once('/var/www/html/banking/library/hcuTransfer.i');

  // INCLUDE the cu_sms.pi script which will have the function for sending out text messages
  require_once('/var/www/html/shared/library/cu_sms.i');
  require_once("/var/www/html/shared/library/cu_data.i"); // For mask functions.

  // set up the logger
  $logger = $HB_ENV["SYSENV"]["logger"];

# # # # # # # # # # # # # # # #
# Import Server Variables
# # # # # # # # # # # # # # # #
//require "/usr/local/bin/dbserver.cfg" if -f "/usr/local/bin/dbserver.cfg" and -r "/usr/local/bin/dbserver.cfg";   # Import: $ownuname, $grpuname, $wwwhost, $dbhost, $dbport, $dbname, $dbuser, $dbpasswd, $dbplat
//if ( file_exists( "/usr/local/bin/dbserver.cfg" ) && is_readable( "/usr/local/bin/dbserver.cfg" ) ) {
//  require "/usr/local/bin/dbserver.cfg";
//} else {
//  die( "Needed file is missing: /usr/local/bin/dbserver.cfg\n" );
//}

  $ownuname = 'www0';
  $grpuname = 'www0';

  $wwwhost = 'www0.homecu.net';

  $dbhost = getenv('DATABASE_HOST');
  $dbport = getenv('DATABASE_PORT');
  $dbname = getenv('DATABASE_NAME');
  $dbuser = getenv('DATABASE_USER');
  $dbpasswd = getenv('DATABASE_PASSWORD');
  $dbplat = 'Postgres';
  $pReplyEmail = "nobody@homecu.net";    // used for the From when mailing CU

  # ################
  # SET LONG CODE AVAILABLE NUMBERS (for alerts messaging)
  $gRoundRobinCounter = 0;
  $LONGCODE_ROUNDROBIN = array('+12672458136', '+12082982149', '+16017142198', '+12082547302', '+16017142199', '+13306492200');


//use vars qw($opt_c $opt_l $opt_n $opt_m $opt_p $opt_s $opt_f $opt_d $FAILRPTAFTER $WARNFAILRPT);
# a = action - either "alerts" or "scheduled"
# c = limit fetching and processing to this particular cu code
# x = exclude this cu code when fetching and processing
# l = limit number of packets retrieved
# n = nap interval between requests
# m = fetch and process for this member only
# p = 'if_mod_since' pktstamp to pass to fetcher
# s = skip loading tables
# f = fetch only - no messages sent
# t = limit fetching and processing to this particular time zone
# d = database host to use - fully-qualified host.homecu.net name
# r = runslot override - used to override the calculated runslot

//use Getopt::Std;
//unless (getopts('c:h:l:n:m:p:t:x:d:sf')) {
//  die "Usage: $ARGV[0] [-d dbhost] [-c cucode] [-x cucode] [-t timezone] [-l limit] [-n nap] [-m member] [-p pktstamp] [-s] [-f]\n";
//}
  // characters followed by a colon (':') are required values, followed by two colons ("::") are optional values
  $showUsage = false;
  $allowedOptions = "c::l::n::m::p::t::x::d::s::f::r::";
  $longOptions = array( "help::", "dryrun::" );
  $commandOptions = getopt( $allowedOptions, $longOptions );
  if ( $commandOptions === FALSE || isset( $commandOptions["help"] ) ) {
      $showUsage = true;
  }

  // get the action (last parameter)
  $action = trim( $argv[$argc-1] );

  // set some option variables based on what was passed in
  $opt_c = isset( $commandOptions["c"] ) ? $commandOptions["c"] : "";
  $opt_l = isset( $commandOptions["l"] ) ? $commandOptions["l"] : "";
  $opt_n = isset( $commandOptions["n"] ) ? $commandOptions["n"] : "";
  $opt_m = isset( $commandOptions["m"] ) ? $commandOptions["m"] : "";
  $opt_p = isset( $commandOptions["p"] ) ? $commandOptions["p"] : "";
  $opt_s = isset( $commandOptions["s"] ) ? true : false;
  $opt_f = isset( $commandOptions["f"] ) ? true : false;
  $opt_d = isset( $commandOptions["d"] ) ? $commandOptions["d"] : "";
  $opt_x = isset( $commandOptions["x"] ) ? $commandOptions["x"] : "";
  $opt_t = isset( $commandOptions["t"] ) ? $commandOptions["t"] : "";
  $opt_r = isset( $commandOptions["r"] ) ? trim( $commandOptions["r"] ) : "";

  $dryRun = isset( $commandOptions["dryrun"] );

  $dryRunPrefix = $dryRun ? "Dry run: " : "";

  // enforce some command line rules
  if ( $action == "" || !($action == "alerts" || $action  == "scheduled") ) {
    print "Missing action. Supported actions: alerts OR scheduled\n";
    $showUsage = true;
  }
  if ( "$opt_d" == "" && "$dbhost" == "" ) {
    print "Missing database host.\n";
    $showUsage = true;
  }
  if ( "$opt_r" != "" && !$opt_s ) {
    print "[-r runslot (DAYHHMM)] should be used with the [-s] (skip load) option.\n";
    $showUsage = true;
  }

  if ( $showUsage ) {
      // show usage and exit
      die( "Usage: {$argv[0]} [-dDbhost] [-cCucode] [-xCucode] [-tTimezone] [-lLimit] [-nNap] [-mMember] [-pPktstamp] [-rRunslot] [-s] [-f] [--dryrun] <action>\n[runslot] is expected to be used with -s option\n<action> can be 'alerts' or 'scheduled'\n" );
  }

  if ( "$opt_d" != "" ) { $dbhost = "$opt_d"; }

  // this is the directory we have rights to and monitor will eventually be able to see
  $WORKINGDIRECTORY = "/home/homecu/html";
  # Change Directory to where this script has permissions
  chdir( $WORKINGDIRECTORY );

  date_default_timezone_set ( "America/Denver" );

  $DMSMAIL = 'support@homecu.net';
  $ALERTMAIL = 'support@homecu.net';

  $STARTDATE = date( "D M d, Y G:i" );
  $TODAY = date ( "Y-m-d" );                      // date formatted like sql date field
  $STDOUT = fopen('php://stdout', 'w');
  fwrite( $STDOUT, "$STARTDATE $TODAY\n" );

  /*
   * run-slot logic
   */
  list( $s, $m, $h, $mday, $mon, $year, $wday, $yday, $isdst) = localtime();

  # CREATE THE RUNSLOT value -- IT will be the key used in tables
  $weekdays = array("SUN", "MON", "TUE", "WED", "THR", "FRI", "SAT");
  # Minute segments -- sections of quarter hour
  $minsegs = array("00", "15", "30", "45");


  # Create a string for the hour/min segment
  $runslotPrefix = $action == "alerts" ? "A" : "S";
  // * Set the platform identifier. This is used in the query to the core.
  $HB_ENV['platform'] = ($action == "alerts" ? "ALERT" : "SCHED");

  if ( strlen( $opt_r ) > 0 ) {
      $runslotseg = substr( $opt_r, -4 );
      $runslot = $runslotPrefix . $opt_r;
  } else {
      $runslotseg = sprintf("%02d", $h) . sprintf("%02d", $minsegs[intval($m / 15)]);
      $runslot = $runslotPrefix . $weekdays[$wday] . $runslotseg;
  }

  $PIDFILE = $action == "alerts" ? $WORKINGDIRECTORY . "/runalerts{$runslot}.pid" : $WORKINGDIRECTORY . "/runrecur{$runslot}.pid";
  if ( file_exists( "$PIDFILE" ) ) {
      $errorMessage = "Prior PID file $PIDFILE still exists -- giving up";
      if ( $dryRun ) {
          print "Dry Run: Would send error email to HomeCU: $errorMessage\n";
      } else {
          dmserror( $DMSMAIL, 'WARN', "$errorMessage\n" );
      }
    exit (1);
  }

  $PID = fopen( $PIDFILE, "w+" );
  fwrite( $PID, getmypid() . "\n" );
  fclose( $PID );

  list ($s, $m, $h, $mday, $mon, $year, $wday, $yday, $isdst) = localtime();
  $T = sprintf( "%4d%02d%02d%02d%02d%02d", 1900+$year,$mon+1,$mday,$h,$m,$s );

  if ( $dryRun ) {
    $STAT = fopen('php://stdout', 'w');
    fprintf( $STAT, "Dry Run: outputting to standard output\n" );
  } else {
    if ( $action == "alerts") {
      $filename = "alerts$T.txt";
      $STAT = fopen( $filename, "w+" );
    } else if ( $action == "scheduled" ) {
      $filename = "recurltx$T.txt";
      $STAT = fopen( $filename, "w+" );
    } else {
      dmserror( $DMSMAIL, 'FATAL', "Unknown action: $action\n" );
    }

    fwrite( $STDOUT, "Results will be in {$WORKINGDIRECTORY}/$filename\n" );
  }

  fflush( $STAT );

  //`find /home/httpd/hcupriv -name "recur*" -mtime +90 -exec rm -f {} \\;`;
  foreach( glob( "recur*") as $filename ) {
    // make sure file is at least 90 days old
    if ( filemtime( $filename ) < (time() - 90 * 24 * 3600) ) {
      if ( $dryRun ) {
        fprintf( $STAT, "Dry Run: removing old recurring file $filename\n" );
      } else {
        unlink( $filename );
      }
    }
  }
  foreach( glob( "alert*") as $filename ) {
    // make sure file is at least 90 days old
    if ( filemtime( $filename ) < (time() - 90 * 24 * 3600) ) {
      if ( $dryRun ) {
        fprintf( $STAT, "Dry Run: removing old alerts file $filename\n" );
      } else {
        unlink( $filename );
      }
    }
  }

  $cucode = ($opt_c ? "'" . strtoupper($opt_c) . "'" : "");
  $xcucode = ($opt_x ? "'" . strtoupper($opt_x) . "'": "");
  $opt_t = str_replace( " ", "", $opt_t );
  $opt_t = ucfirst(strtolower($opt_t));
  $limit =  ($opt_l > 0 ? $opt_l : "");
  $nap = ( $opt_n < 0 ? 0 : intval( $opt_n ) );
  $member = ($opt_m ? $opt_m : "");
  $skipload = ($opt_s ? 1 : "");
  $fetchonly = ($opt_f ? true : false);
  $pktstamp = (intval($opt_p) > 0 ? $opt_p : time() - 86400);
  //$alertdate = `date +'%a %b %d %Y %X %Z'`;
  $alertdate = date( "D M d Y H:i:s T");

  $opt_m = str_replace( " ", "", $opt_m );
  // "s" is alias for cu_scheduledtxn
  $memberSQL = ($opt_m ? " and s.accountnumber = '$opt_m' " : "");
  // "a" is alias for cuadmin
  if ( $opt_t == "Eastern" ) {
      $timezoneSQL = " and a.tz in ('Eastern','East-Indiana','Indiana-Starke','Michigan','America/Port_of_Spain','America/St_Thomas','America/Barbados') ";
  } elseif ( $opt_t == "Pacific" ) {
      $timezoneSQL = " and a.tz in ('Pacific','Alaska','Arizona') ";
  } else {
      $timezoneSQL = ($opt_t ?  " and a.tz = '$opt_t' " : "");
  }

  // 09/19: This clarifies the changing state of $xcucode. We want it to be an array member,
  // not a select string part. See design doc
  // https://docs.google.com/document/d/1wqTezsy-9l2q01sInvbxSuxCx-zq0wRV_kA0VtrWsj8
  $xcu_arr = [];
  $sql_xcucodes = $xcucode;
  $xcucode_arr = (! empty($xcucode))? [str_replace("'", '', $xcucode)] : [];
  // if timezone is set
  //   AND a single Credit union is NOT listed
  // then I want to load the list of credit unions to exclude from the blacklist file
  if (($opt_t != '') && $xcucode == '') {
    // ReturnBlackList
    // This function will load the list of credit unions to be excluded from the alerts.blacklist.conf file
    //  and return in a string formatted as 'CUCODE1', 'CUCODE2', 'CUCODE3'
    // 09-19: Now returns array, see design document at above link as to the "why"
    $xcu_arr = ReturnBlackList();
    $sql_xcucodes = "'" . implode("', '", $xcu_arr) . "'";
    $returnString = "\nExcluded Blacklisted: $sql_xcucodes\n\n";

  } else if ( ($opt_t == "") && $cucode == "" ) {
    # If timezone is BLANK
      # AND cucode is currently blank --
      # THEN build cucode from the ReturnRunSlotList Function
      # Pass the runslot segment to the function 'HH:MM'
    $cucode = ReturnRunSlotList( $runslotseg );
  }

  $ignore_cu_arr = ReturnIgnoreList($dbh);
  if (count($ignore_cu_arr) > 0) {
    // if CU is empty OR CU is specified AND in ignore list - add to blacklist and report
    if ( $cucode == "" || ($cucode != "" && in_array($cucode, $ignore_cu_arr))) {

      $sql_xcucodes  = "'" . implode("', '", array_unique(array_merge($xcucode_arr, $xcu_arr, $ignore_cu_arr))) . "'";
      $returnString = "\nExcluded Blacklisted: "
      . "'" . implode("', '", $xcu_arr) . "'" . "\n";
      if (count($xcucode_arr) > 0) {
        $returnString .= "Command line X option exclusions: "
        . "'" . implode("', '", $xcucode_arr) . "'" . "\n";
      }
      $returnString .= "In Migration CUs Ignored: "
      . "'" . implode("', '", $ignore_cu_arr) . "'\n\n";

      // this is for the person running it to see on the console
      if ( !$dryRun ) {
        fprintf( $STDOUT, $returnString );
      }
      fprintf( $STAT, $returnString );
    }
  }

  ############ print out processing parameters ########
  fprintf( $STAT, "Command options: \n" );
  fprintf( $STAT, "\tCU Code: $cucode\n" );
  fprintf( $STAT, "\tlimit: $opt_l\n" );
  fprintf( $STAT, "\tnap: $opt_n\n" );
  fprintf( $STAT, "\tmember: $opt_m\n" );
  fprintf( $STAT, "\tpktstamp: $opt_p\n" );
  fprintf( $STAT, "\tskip load: $skipload\n" );
  fprintf( $STAT, "\tfetch only: $fetchonly\n" );
  fprintf( $STAT, "\tdbhost: $opt_d\n" );
  fprintf( $STAT, "\texclude CU: $sql_xcucodes\n" );
  fprintf( $STAT, "\ttimezone: $opt_t\n" );
  fprintf( $STAT, "\trunslot: $opt_r\n" );
  fprintf( $STAT, "\tdry run: " . ($dryRun ? "true" : "false") . "\n" );
  fprintf( $STAT, "\nUsing run slot: $runslot\n\n" );

  // these are only applicable if loading the dataset to process
  if ( !$skipload ) {
        if ( $opt_t ) {
          $string = "Processing Timezone $opt_t\n";
          fprintf( $STAT,  $string );
          if ( !$dryRun ) {
              fprintf( $STDOUT, $string );
          }
        }
        if ( $opt_c ) {
          $string = "Processing CU $opt_c\n";
          fprintf( $STAT,  $string );
            if ( !$dryRun ) {
                fprintf( $STDOUT, $string );
            }
        }
        if ( $opt_x ) {
          $string = "Excluding CU $opt_x\n";
          fprintf( $STAT,  $string );
            if ( !$dryRun ) {
                fprintf( $STDOUT, $string );
            }
        }
        if ( $opt_m ) {
          $string = "Processing Member $opt_m\n";
          fprintf( $STAT,  $string );
            if ( !$dryRun ) {
                fprintf( $STDOUT, $string );
            }
        }
        if ( $opt_l > 0 ) {
          $string = "Limit=$limit\n";
          fprintf( $STAT,  $string );
            if ( !$dryRun ) {
                fprintf( $STDOUT, $string );
            }
        }
        if ( $opt_n ) {
          $string = "Nap=$nap\n";
          fprintf( $STAT,  $string );
          if ( !$dryRun ) {
              fprintf( $STDOUT, $string );
          }
        }
        if ( $opt_p ) {
          $string = "Using pktstamp $pktstamp\n";
          fprintf( $STAT,  $string );
          if ( !$dryRun ) {
              fprintf( $STDOUT, $string );
          }
        }
  }

  if ( $action == "alerts" ) {
      // for alerts look back 7 days
      $cutoff = date( "Ymd", time() - 7 * 24 * 60 * 60 );
  } else {
      // for scheduled txns cutoff is in the future to avoid getting history (+5 days)
      $cutoff = date( "Ymd", time() + 5 * 24 * 60 * 60 );
  }

  ############# configuration variables ###########
  $config = array();
  $config["skipload"] = $skipload;
  $config["runslot"] = $runslot;
  $config["cucode"] = $cucode;
  $config["xcucode"] = $sql_xcucodes;
  $config["member"] = $member;
  $config["limit"] = $limit;
  $config["nap"] = $nap;
  $config["fetchonly"] = $fetchonly;
  $config["pktstamp"] = $pktstamp;
  $config["cutoff"] = $cutoff;

  ####################### Globals
  $gatheredStats = array();
  $fetchStats = array();

  ####################### Open Database

  // database was opened via hcuService.i but verify we have a connection
  if ( !$dbh ) {
    dmserror( $DMSMAIL, 'FATAL', "Database connection failed" );
  }

  fprintf( $STAT, "Connected to database.\n" );

  $starttime = time();

  $numProcessed = 0;
  ############# Process Stuff
  if ( $action == "alerts" ) {
    if ($opt_t == "" && $opt_c == "" && $cucode == "") {
    fprintf($STAT, "No valid Credit Unions found with current parameters.\n");
    } else {
    $numProcessed = ProcessAlerts($config);
    }
  } else if ( $action == "scheduled" ) {
    $numProcessed = ProcessScheduledTransfers( $config );
  }

  $now = date( "D M d H:i:s Y" );
  fprintf( $STAT, "{$dryRunPrefix}Finished Processing $now\n" );

  $endtime = time();
  $time = $endtime - $starttime;
  $processedString = "\n{$dryRunPrefix}Processed $numProcessed accounts in $time seconds\n\n";
  fprintf( $STAT, $processedString );

  // this is for the person running it to see on the console
  if ( !$dryRun ) {
      fprintf( $STDOUT, $processedString );
  }

  if ( count( $fetchStats ) > 0 ) {
    fprintf( $STAT, "Fetch statistics:\n");
    foreach( $fetchStats as $statCode => $count ) {
        fprintf( $STAT, "\t$statCode: $count\n");
    }
  } else {
    fprintf( $STAT, "No Fetch stats gathered.\n");
  }
  fprintf( $STAT, "\n");

  if ( $action == "alerts" ) {
    PrintGatheredAlertStats( $STAT, $gatheredStats );
  } else if ( $action == "scheduled" ) {
    PrintGatheredTransferStats( $STAT, $gatheredStats );
  }

/*
We are trying to combine cross account logic with regular account logic, so hopefully don't need this part

  $memsql = "SELECT trim(cu), trim(from_account) as from_account, trim(to_account) as to_account
                      FROM cutronus
                      WHERE runslot = '$runslot'
                    ORDER BY shuffle ";
  $memsql .= ( $limit != "" ? " limit $limit " : "" );

  #print STAT "Using sql " . $memsql . "\n";

  $listRS = db_query( $memsql, $dbh );
  if ( $listRS ) {
    dmserror($DMSMAIL,'FATAL',"Failed Selecting Cross Accounts to Process\n" . db_last_error( $listRS ) );
  }

  $now = date( "D M d H:i:s Y" );
  fprintf( $STAT, "Fetching Cross Account Packets Begin $now\n";

  $stats = array();
  $row = 0;
  while ( $dataRow = db_fetch_row( $listRS, $row++ ) ) {
      if ( $nap > 0 ) { sleep( $nap ); }

      $cu = $dataRow[0];
      $fromAccount = $dataRow[1];
      $toAccount = $dataRow[2];

    ($status, $asofdate, $reason) = batch_XS();

    if ($reason > '' ) { $showcount .= $reason; }

    $stats{"Status_${status}"}++;

    if ($status == '200' || $status == '201' || $status == '202') {
            if ( $cucode != "") sleep( $nap + 300 );
    }

    if ($status eq '001' || $status eq '999') {
        print STAT "$row\tCU $Cu Member $Cn\tFailed XA! $status - $reason\n";
    }

    if ($status eq '001') {
        # -- I want to send an email to the Credit Union
            # They have a user w/repeating transactions that is NO longer setup
            # in the Credit Union but are still setup in HomeBanking
        my $email_msg = "RECURRING TRANSACTION FAILED\n\nMember $Cn does not exist or is not setup on your core system.\n\n\n\n($Cu:$Cn)";
        my $ERR_CUMAIL = '';
        # FETCH THE CU EMAIL ACCOUNT
        my $sql = "SELECT rtrim(email)
                             FROM cuadmnotify
                             WHERE cu = '$Cu'
                                AND role = 'transfernotify'";
        my $admalertresult = $conn->exec($sql);
        unless (PGRES_TUPLES_OK eq $admalertresult->resultStatus) {
            dmserror($DMSMAIL,'WARN',$conn->errorMessage);
        } else {
            ($ERR_CUMAIL) = $admalertresult->fetchrow;
        }
        $ERR_CUMAIL = ($ERR_CUMAIL gt '' ? $ERR_CUMAIL : $DMSMAIL);

        SmsMailTransfer( $ERR_CUMAIL, $ERR_CUMAIL, $email_msg, '' );
    }
}
print STAT "Fetching Cross Account Packets Complete " . scalar localtime() . "\n";
$endtime = time;
$time = $endtime - $starttime;
print STAT "\nProcessed $row accounts in $time seconds\n\n";
while(($stat,$count) = each %stats) {
    print STAT "$stat:  $count\n";
}
*/


  fclose( $STAT );

  unlink($PIDFILE);
exit;

/**
 * This function will perform all the ScheduledTransfers processing. All the configuration variables are passed in.
 * There are global variables used, as well.
 */
function ProcessScheduledTransfers( $config ) {
    global $dbh;
    global $dryRun, $dryRunPrefix;
    global $HB_ENV;
    global $STAT;
    global $TODAY;
    global $DMSMAIL;
    global $memberSQL, $timezoneSQL;
    global $gatheredStats, $fetchStats;
    global $pReplyEmail; // Without this, MailCU does not run.

    # ####################### #
    # FAILED REPEATED TRANSFER MORATORIUM IN DAYS
    # ####################### #
    $FAILRPTAFTER = '33';

    # ####################### #
    # FAILED REPEATED TRANSFER WARNING MSG IN DAYS
    # ####################### #
    $WARNFAILRPT = '10';

    // cuEmailCache is just to gather the email so don't need to get it again
    $cuEmailCache = array();

    // NOTE: setting of scheduled transfers to inactive is not done if doing skipload because it is usually a debugging effort
    if ( !$config["skipload"] ) {
        // just delete the entries being added
      $memSQL = "DELETE FROM cutronus WHERE runslot = '{$config["runslot"]}';
                               DELETE FROM cutronab WHERE runslot = '{$config["runslot"]}';
                               DELETE FROM cutronah WHERE runslot = '{$config["runslot"]}';
                               DELETE FROM cutronlb WHERE runslot = '{$config["runslot"]}';
                               DELETE FROM cutronlh WHERE runslot = '{$config["runslot"]}'; ";
        if ( $dryRun ) {
          fprintf( $STAT, "Dry run: would be deleting from tables\n $memSQL\n" );
        } else {
          $rs = db_query( $memSQL, $dbh );
          if ( !$rs ) {
              dmserror( $DMSMAIL, 'FATAL', "Failed Deleting From Tables\n" . db_last_error( $rs ) );
          }
      }

        $now = date( "D M d H:i:s Y" );
        fprintf( $STAT, "Truncate Tables Complete $now\n" );

        $CU2_PROCRECUR = 32;        # Process Repeating Transfers flag

        # #####################
        #
        # SET UNSUCCESSFUL UPDATES TO INACTIVE
        #
        # select all active transfers that have failed for more than FAILRPTAFTER
        #      stopdate must NOT be exceeded
        #   then
        #   Gather Email / members for credit union that transfer is set to inactive
        #   Email member that transfer is set to inacive
        #   SET stopdate to current day
        #   SET status to I
        #
        #   AFTER process of all records email each CU with list of Accounts
        #
        # #####################

        $failSQL = "SELECT s.id, s.user_id, trim(s.cu), s.next_trigger_date, s.status, trim(a.pname)
                            FROM cu_scheduledtxn s
                            LEFT JOIN cuadmin a on a.cu = s.cu
                            WHERE date_part('day', current_date::timestamp - next_trigger_date::timestamp) > {$FAILRPTAFTER}
                              AND (s.end_date IS NULL OR s.end_date >= s.next_trigger_date)
                              AND a.livebatch = 'L'
                              AND s.status = 'A'
                              AND coalesce(a.offlinestat, 'N') not in ('Y','U')
                              AND (a.flagset2::bigint & {$CU2_PROCRECUR}::bigint) = $CU2_PROCRECUR

                            ORDER BY s.cu, s.id; ";

        $failRS = db_query( $failSQL, $dbh );
        $row = 0;
        $currCU = '';
        $cuList = array();

        # LOOP through any returned rows
        while ( $failRec = db_fetch_row( $failRS, $row++ ) ) {

          $inactiveMsg = "";
          $failCUMail = "";

          # Get CU Code
          $currCU = $failRec[2];

          # #############
          # Check for Credit Union
          if ( !isset( $cuList[$currCU] ) ) {
            # CU Code has not been added yet
            # FETCH THE CU EMAIL ACCOUNT
            $failCUMail = FetchCUEmail( $dbh, $currCU );

            // make sure we got an email
            $failCUMail = ($failCUMail != "" ? $failCUMail : $DMSMAIL);

            # Add Email for CU
            $cuList[$currCU]["email"] = $failCUMail;
            // start the account list
            $cuList[$currCU]["accounts"] = "";
          } else {
            # SET THE failCUMail
            $failCUMail = $cuList[$currCU]["email"];
          }

          // add the CU email to the cache in case need it again
          if ( !isset( $cuEmailCache[$currCU] ) ) {
            $cuEmailCache[$currCU]["email"] = $failCUMail;
          }

          // get user info
          $userInfo = FetchUserInfo( $dbh, $currCU, $failRec[1] );

          // add User Name to the CU Email LIst
          $cuList[$currCU]["accounts"] .= "Notice: A repeating transfer for user {$userInfo["user_name"]} was set to inactive.\n";

          if ( $dryRun ) {
            fprintf( $STAT, "Dry Run: Would send inactive email to {$userInfo["email"] } (at $currCU):\n{$userInfo["email"] }\n" );
          } else {
            // send email to user
            if ( validateEmail( $userInfo["email"] ) ) {
              # Email appears to be valid -- Send email with credit union name
              MailUserInactive( $failCUMail, $failRec[5], $userInfo["email"] );
            }
          }

          if ( !$dryRun ) {
            # Update Record in the database
            $updSQL = "UPDATE cu_scheduledtxn
                                 SET status = 'I',
                                   end_date = current_date
                                 WHERE id = '" . $failRec[0] . "'; ";

            $rs = db_query( $updSQL, $dbh );
            if ( !$rs ) {
              dmserror( $DMSMAIL, 'FATAL', "Failed Updating Deactivate Txns\n" . db_last_error() . "\n$updSQL");
            }
          }

          $updateMessage = "{$dryRunPrefix}Updating repeating transfer {$failRec[0]} to inactive, user {$userInfo["user_name"]} of {$currCU}\n";
          fprintf( $STAT, $updateMessage );
        }

        # #############
        # Send email to Credit Union

        # ##########################
        # EMAIL ANY CUs that have members drop off
        # Loop through cuList

        # Loop through each cucode mentioned
        if ( count( $cuList ) > 0 ) {
          foreach( $cuList as $thisCU => $info ) {
            if ( $dryRun ) {
              fprintf( $STAT, "Dry Run: Email CU that account(s) dropped off:  $thisCU, {$cuList[$thisCU]["accounts"]}\n" );
            } else {
                $message =  "Hi,\n\nThe following user's had recurring transfer(s) were set to inactive.\n\n";
                $message .= "{$cuList[$thisCU]["accounts"]}\n\n\n";
                $message .= "HomeCU";

                MailCU( $pReplyEmail, $cuList[$thisCU]["email"], $message );
            }
          }
        }



  #
        // Get the unique list of users and accounts; we need the user to know where to send an email if a failure; it can be unique
        //  because the core will fail if there is a balance issue if there are multiple transfers from the same account; need to get
        //  cross-account authority from the core because some cores aren't good about checking that (go figure).
        // Get both the from and to account so can tell if cross account or not
        // Lower code will get the 'liveserver' value from cuadmin, so just using cuadmin to see if online

        if ( !$dryRun ) {
          // save the run in the cutronus table (accountnumber is used for alerts)
          $insert =  "INSERT INTO cutronus (runslot, cu, user_id, sched_id, accountnumber) ";
          $select = "SELECT distinct '{$config["runslot"]}', s.cu, s.user_id, id, ''
                            FROM cu_scheduledtxn s
                            JOIN cuadmin a on a.cu = s.cu
                              AND a.livebatch = 'L'
                              AND coalesce(a.offlinestat, 'N') not in ('Y','U')
                            WHERE (a.flagset2::bigint & {$CU2_PROCRECUR}::bigint) = $CU2_PROCRECUR
                              AND s.next_trigger_date <= '{$TODAY}'
                              AND s.status = 'A'
                              AND s.approved_status = 10
                              AND (s.end_date is NULL OR s.end_date >= s.next_trigger_date)
                              AND s.feature_code IN ('TRN', 'TRNM2M', 'TRNEXT', 'ACHCOL', 'ACHPMT')
                                  $memberSQL $timezoneSQL";

          if ( $config["cucode"] != "" ) { $select .= " AND s.cu IN ({$config["cucode"]}) "; }
          if ( $config["xcucode"] != "" ) { $select .= " AND s.cu NOT IN ({$config["xcucode"]}) "; }

          $shuffle = "UPDATE cutronus SET shuffle = random() where runslot = '{$config["runslot"]}'; ";

          // note the ';' before the shuffle query
          $sql = "$insert $select; $shuffle";
          $memRS = db_query( $sql, $dbh );

          if ( !$memRS ) {
            dmserror( $DMSMAIL,'FATAL',"Failed populating cutronus table with accounts to process\n" . db_last_error() . "\n$sql\n" );
          }

          fprintf( $STAT, "   SQL to gather scheduled txns:\n\t\t\t $select\n" );
        }

        $now = date( "D M d H:i:s Y" );
        fprintf( $STAT,  "{$dryRunPrefix}Load Tables for Regular Accounts Complete $now\n" );
        // fprintf( $STAT,  "  with SQL: $select\n" );
    } // end !$config["skipload"]

    // for a dry run, use a form of the query from earlier, that would have populated the cutronus table
    if ( $dryRun ) {
      $memsql = "SELECT s.cu, s.user_id, s.feature_code, s.txn_data, s.id, a.pname, an1.email,
                                          s.approved_by, s.start_date, s.end_date, s.next_trigger_date, s.interval_count, s.repeating_parameters
                            FROM cu_scheduledtxn s
                            INNER JOIN cuadmin a on a.cu = s.cu
                              AND a.livebatch = 'L' and coalesce(a.offlinestat, 'N') not in ('Y','U')
                              AND (a.flagset2::bigint & {$CU2_PROCRECUR}::bigint) = $CU2_PROCRECUR
                            INNER JOIN cuadmnotify an1 ON an1.cu = s.cu
                              AND role = 'transfernotify'
                            WHERE s.next_trigger_date <= '{$TODAY}'
                              AND s.status = 'A'
                              AND (s.end_date is NULL OR s.end_date >= s.next_trigger_date)
                              AND s.feature_code IN ('TRN', 'TRNM2M', 'TRNEXT', 'ACHCOL', 'ACHPMT')
                                  $memberSQL $timezoneSQL";

      if ( $config["cucode"] != "" ) { $memsql .= " AND s.cu IN ({$config["cucode"]}) "; }
      if ( $config["xcucode"] != "" ) { $memsql .= " AND s.cu NOT IN ({$config["xcucode"]}) "; }
    } else {
      // We need a separate query for the user email because the credit union name is part of the table name
      // The txn_data is a json array that needs to be processed different based on feature code
      $memsql = "SELECT tron.cu, tron.user_id, s.feature_code, s.txn_data, s.id, a.pname, an1.email,
                                          s.approved_by, s.start_date, s.end_date, s.next_trigger_date, s.interval_count, s.repeating_parameters
                          FROM cutronus tron
                          INNER JOIN cu_scheduledtxn s ON s.id = tron.sched_id
                          INNER JOIN cuadmin a on a.cu = s.cu
                          INNER JOIN cuadmnotify an1 ON an1.cu = s.cu
                              AND role = 'transfernotify'
                          WHERE runslot = '{$config["runslot"]}' ";

      if ( $config["cucode"] != "" ) { $memsql .= " AND tron.cu IN ({$config["cucode"]}) "; }
      if ( $config["xcucode"] != "" ) { $memsql .= " AND tron.cu NOT IN ({$config["xcucode"]}) "; }

      // NOTE: need to handle member account filter while processing the records since it is buried in json array and dependant on transfer type
      $memsql .= " order by shuffle ";
    }

    if ( $config["limit"] != "" ) { $memsql .= " limit {$config["limit"]} "; }

    fprintf( $STAT, "   Using sql to process scheduled txns:\n\t\t\t" . $memsql . "\n" );

    $memRS = db_query( $memsql, $dbh );

    if ( !$memRS ) {
      dmserror( $DMSMAIL,'FATAL',"Failed Selecting Accounts to Process\n" . db_last_error() );
    }

    $now = date( "D M d H:i:s Y" );
    fprintf( $STAT, "{$dryRunPrefix}Fetching Account Packets Begin $now\n" );

    $showcount = "";

    /* This loop will
     *  - validate the user has access to the account
     *  - validate the transfer rights (internal/external, deposit/withdrawal) allow the transfer
     *  - validate the core allows (if flag set that core needs to check authority)
     *  - get each "from" account's balances (except for external -> local) (NOTE: really just need the misc1 field for applicable loans)
     */
    $row = 0;
    $numProcessed = 0;
    while ( $schedRow = db_fetch_assoc( $memRS, $row++ ) ) {
        // get the values we need - cu, user_id, from_account, to_account, feature_code, type
        $currCU = trim( $schedRow["cu"] );
        $userId = $schedRow["user_id"];
        $featureCode = trim( $schedRow["feature_code"] );
        $txnData = HCU_JsonDecode( $schedRow["txn_data"], false );
        $scheduleId = $schedRow["id"];
        $pName = trim( $schedRow["pname"] );
        $adminEmail = trim( $schedRow["email"] );
        $approvedBy = $schedRow["approved_by"];
        $startDate = $schedRow["start_date"];
        $endDate = $schedRow["end_date"];
        $nextTriggerDate = $schedRow["next_trigger_date"];
        $intervalCount = $schedRow["interval_count"];
        $repeatingParameters = HCU_JsonDecode( $schedRow["repeating_parameters"], false );
        $fromAccountKey = Array();
        $toAccountKey = Array();
        $lockStatus = '';

        // get the type of transfer
        $type = isset( $txnData["txn"]["type"] ) ? $txnData["txn"]["type"] : "";

        $localTransCode = trim($txnData["txn"]["transactioncode"]);

        // set the environment for this credit union
        LoadCUAdmin( $dbh, $currCU, $HB_ENV );

        /**
         * SET COMMON VALUES THAT WOULD NORMALLY BE IN COOKIE -- BUT LOAD FROM LoadCuAdmin results *
         */
        $HB_ENV['Fset'] = $HB_ENV['flagset'];
        $HB_ENV['Fset2'] = $HB_ENV['flagset2'];
        $HB_ENV['Fset3'] = $HB_ENV['flagset3'];
        /* User Flags -- Should not change functionality */

        // adding flag so we don't load cross accounts during scheduled transaction processing
        $HB_ENV["Fmsg_tx"] = GetMsgTxValue('MSGTX_TMP_XAX_LD');
        $HB_ENV['Ml'] = '';
        $HB_ENV['Fplog'] = '';

        // count doesn't need a message
        AddToGatheredTransferStats( $gatheredStats, $currCU, "count", "" );

        if ( $txnData == "" ) {
            $errorMessage = "Error extracting scheduled transaction data. CU: {$currCU}, User Id: {$usreId}";
            if ( $dryRun ) {
                fprintf( $STAT, "Dry Run: Would send error email to HomeCU: $errorMessage\n" );
            } else {
                dmserror( $DMSMAIL, 'FATAL',  $errorMessage );
            }
        }

        if ( $repeatingParameters == "" ) {
            $errorMessage = "Error with scheduled transaction repeating parameters. CU: {$currCU}, User Id: {$usreId}, id: $scheduleId";
            if ( $dryRun ) {
                fprintf( $STAT, "Dry Run: Would send error email to HomeCU: $errorMessage\n" );
            } else {
                dmserror( $DMSMAIL, 'FATAL',  $errorMessage );
            }
        }

        // if only handlng certain accounts test that now
        if ( $config["member"] != "") {
            if ( $txnData["txn"]["txFromAcct"]  != $config["member"] ) continue;
        }

        // validate the user has access to the account and rights to do the transfer
        // TRN - both accounts need to exist and have withdrawal (from) and deposit (to) rights
        // TRNEXT - one account needs to exist and have appropriate rights
        // TRNM2M/ACHPMT - from account needs to exist and have withdrawal right
        // ACHCOL - to account needs to exist and have deposit rights
        if ( $featureCode == "TRN" ) {
            // let both from and to accounts through, but need to create the account info in the form T|XXX|YYY|Z
            // we shouldn't have a transfer on certificate so we are okay with a zero for the certificate
            $fromAccountId = $txnData["txn"]["from"];
            $toAccountId = $txnData["txn"]["to"];

            $fromAccountKey = BuildAccountKey($txnData, "from");
            $toAccountKey = BuildAccountKey($txnData, "to");

        } else if ( $featureCode == "TRNEXT" ) {
          if ( $type == "X2L" ) {
            // external to local - don't check from account
            $fromAccountId = "";
            $toAccountId = $txnData["txn"]["to"];
            $toAccountKey = BuildAccountKey($txnData, "to");
          } else {
            // local to external - don't check to account
            $fromAccountId = $txnData["txn"]["from"];
            $toAccountId = "";
            $fromAccountKey = BuildAccountKey($txnData, "from");
          }
        } else if ( $featureCode == "TRNM2M" || $featureCode == "ACHPMT" ) {
            // local to external - don't check to account
            $fromAccountId = $txnData["txn"]["from"];
            $toAccountId = "";
            $fromAccountKey = BuildAccountKey($txnData, "from");
        } else if ( $featureCode == "ACHCOL" ) {
            // external to local - don't check from account
            $fromAccountId = "";
            $toAccountId = $txnData["txn"]["to"];
            $toAccountKey = BuildAccountKey($txnData, "to");
        }

        $validAccess = UserHasRightsToTransfer( $dbh, $currCU, $userId, $fromAccountKey, $toAccountKey, $localTransCode);

        if ( !$validAccess ) {
            $failMsg = "\nWarning: you no longer have rights to an account used in your repeating transfer.\n";
            $failMsg .= "If this isn't corrected within {$FAILRPTAFTER} days the transfer will be set inactive.\n";

            // get user info
            $userInfo = FetchUserInfo( $dbh, $currCU, $userId );

            // add the CU email to the cache in case need it again
            if ( isset( $cuEmailCache[$currCU]["email"] ) ) {
                $failCUMail = $cuEmailCache[$currCU]["email"];
            } else {
                $failCUMail = FetchCUEmail( $dbh, $currCU );

                // make sure we got an email
                $failCUMail = ($failCUMail != "" ? $failCUMail : $DMSMAIL);

                $cuEmailCache[$currCU]["email"] = $failCUMail;
            }

            if ( validateEmail( $userInfo["email"] ) ) {
                if ( $dryRun ) {
                    fprintf( $STAT, "Dry Run: Would send error email to user: $failMsg\n" );
                } else {
                    # Email appears to be valid -- Send email with credit union name
                    MailUserWarning( $failCUMail, $currCU, $userInfo["email"], $failMsg );
                }
            }

            $accountInfoForMessage = "FROM: $fromAccountId TO: $toAccountId";
            $message = "User {$userInfo["user_name"]} no longer has rights to account ($accountInfoForMessage)\n";
            AddToGatheredTransferStats( $gatheredStats, $currCU, "rights", $message );

            // skip to the next entry
            continue;
        }

        if (HCU_array_key_value("fromtype", $txnData['txn']) != 'X') {
          // check if the from account is locked or readonly -- IF NOT type 'X'
          //$parts = explode( "|", $fromAccountId );
          $account = HCU_array_key_value("frommember", $txnData['txn']); /// $parts[1];
          $lockStatus = CheckIfAccountLocked( $dbh, $currCU, $account );
        }
        if ( $lockStatus == "" &&
             (HCU_array_key_value("totype", $txnData['txn']) != 'X') &&
             (HCU_array_key_value("totype", $txnData['txn']) != 'M') ) {
          // from account is okay, check the to account
          //$parts = explode( "|", $toAccountId );
          //$account = $parts[1];
          $account = HCU_array_key_value("tomember", $txnData['txn']); /// $parts[1];
          $lockStatus = CheckIfAccountLocked( $dbh, $currCU, $account );
        }

        if ( $lockStatus != "" ) {
            $lockType = $lockStatus == "R" ? "Readonly" : "Locked";
            $failMsg = "\nWarning: an account used in your repeating transfer is marked $lockType.\n";
            $failMsg .= "If this isn't corrected within {$FAILRPTAFTER} days the transfer will be set inactive.\n";

            // get user info
            $userInfo = FetchUserInfo( $dbh, $currCU, $userId );

            // add the CU email to the cache in case need it again
            if ( isset( $cuEmailCache[$currCU]["email"] ) ) {
                $failCUMail = $cuEmailCache[$currCU]["email"];
            } else {
                $failCUMail = FetchCUEmail( $dbh, $currCU );

                // make sure we got an email
                $failCUMail = ($failCUMail != "" ? $failCUMail : $DMSMAIL);

                $cuEmailCache[$currCU]["email"] = $failCUMail;
            }

            if ( validateEmail( $userInfo["email"] ) ) {
                if ( $dryRun ) {
                    fprintf( $STAT, "Dry Run: Would send error email to user: $failMsg\n" );
                } else {
                    # Email appears to be valid -- Send email with credit union name
                    MailUserWarning( $failCUMail, $currCU, $userInfo["email"], $failMsg );
                }
            }

            $accountInfoForMessage = "FROM: $fromAccountId TO: $toAccountId";
            $message = "User {$userInfo["user_name"]} is using a $lockType account ($accountInfoForMessage)\n";
            AddToGatheredTransferStats( $gatheredStats, $currCU, "rights", $message );

            // skip to the next entry
            continue;
        }

        if ( in_array( $featureCode, array( "TRN", "TRNM2M" ) ) ) {
            // make sure we have a From account
            if ( $fromAccountId == "" ) {
                // we need to have had a From account, so this is an error path
                $errorMessage = "Invalid transaction - missing From account\n";
                if ( $dryRun ) {
                    fprintf( $STAT, "Dry Run: Would send error email to HomeCU: $errorMessage\n" );
                    continue;
                } else {
                    dmserror( $DMSMAIL,'FATAL',"$errorMessage\n" );
                }
            }

            // see if already gathered data on this From account
            if ( HaveAccountData( $dbh, $currCU, $fromAccountId, $config["runslot"] ) ) {
                // pretend we have good status for later logic
                $status = "000";
            } else {
                // TODO: WHEN FUNCTIONALITY EXISTS, CHECK CROSS-ACCOUNT AUTHORITY WITH CORE

                // TODO: check the appliance down list??

                // since we are about to contact the core, check the nap setting
                if ( $config["nap"] > 0 ) {
                    sleep( $config["nap"] );
                }

                // Get the account balance only for the internal from account

                // set up some environment since calling banking function
                $HB_ENV["Cu"] = $currCU;
                $HB_ENV["cu"] = $currCU;
                $HB_ENV["Uid"] = $userId;
                // NOTE: dryRun will fetch and evaluate but not process
                // set the email to "NULL" because we don't know if we have the actual member or just a user
                $memberData = array( "account" => $fromAccountKey['accountnumber'], "cutoff" => $config["cutoff"], "packetstamp" => $config["pktstamp"], "email" => "NULL" );

                $fetchResp = FetchMemberDataTRON( $HB_ENV, $memberData );

                // For the packet status use the value returned from the core
                $status = $fetchResp["status"];

                if ( !isset( $fetchStats["Status_{$status}"] ) ) {
                  $fetchStats["Status_{$status}"] = 1;
                } else {
                  $fetchStats["Status_{$status}"]++;
                }

                // for the error code use the value returned from the function
                $code = $fetchResp["code"];

                if ( $code == '200' || $code == '201' || $code == '202' ) {
                    // if just doing one credit union, give it a little quiet time
                    if ( $cucode != "" ) {  sleep( $config["nap"] + 300 ); }
                } else if ( $code != '000' ) {
                  $reason = trim( $fetchResp["data"]["requestDesc"] );
                  if ( !strlen( $reason ) ) {
                    $reason = "Unknown reason";
                  }

                  fprintf( $STAT, "$row\tCU $currCU Member Account {$fromAccountKey['accountnumber']}\tFailed! $code - $reason\n" );
                }

                if ( $code == '001' ) {
                  # -- I want to send an email to the Credit Union
                  # They have a user w/repeating transaction(s) that is/are NO longer set up
                  # in the Credit Union but is/are still set up in HomeBanking
                  $emailMsg = "RECURRING TRANSACTION FAILED\n\n";
                  $emailMsg .= "Member {$fromAccountKey['accountnumber']} does not exist or is not set up on your core system.\n\n\n\n";
                  $emailMsg .= "($currCU: {$fromAccountKey['accountnumber']})";

                  # FETCH THE CU EMAIL ACCOUNT
                  $errCUMail = FetchCUEmail( $dbh, $currCU );

                  $errCUMail = ($errCUMail > "" ? $errCUMail : $DMSMAIL);

                  if ( $dryRun ) {
                      fprintf( $STAT, "Dry Run: Would send error email to CU: $emailMsg\n" );
                  } else {
                      MailCU( $errCUMail, $errCUMail, $emailMsg );
                  }
                } else if ( $code == "000" ) {
                    $accountData = $fetchResp["data"]["requestData"];
                    // update the cutronab, cutronah, cutronlb, cutronlh tables with the data just retrieved
                    $loadResp = UpdateTRONTables( $HB_ENV, $config["runslot"], $memberData, $accountData );

                    if ( $loadResp["status"]["code"] != "000" ) {
                       $errorMessage = "Failure loading TRON tables: {$loadResp["status"]["error"]}.";

                        if ( $dryRun ) {
                            fprintf( $STAT, "Dry Run: Would send error email to HomeCU: $errorMessage\n" );
                        } else {
                            dmserror( $DMSMAIL, 'FATAL',  $errorMessage );
                        }

                        // need to skip this transaction because of the error
                        continue;
                    }
                }
            }
        }

        // All transfers features will do this transfer section

  // TODO: If looking for potential performance improvements, this is where we would have a child process handle the actual scheduling of the transaction.
        if ( !$config["fetchonly"] ) {
            // need the authority account

            /**** TEMPORARY - Set up the txn data as to how it will be after things are cleaned up
            {"txn": { "from":"D|1103|0|0", "txFrom": "1103", "txFromSuffix": "0", "txFromType": "D", "to":"D|1103|10|0", "toacct": "1103", "tosuffix": "10", "totype": "D", "amount":11.11, "transactioncode": "AT", "deposittype":"D", "memo": "" } }
              ****/
            if ( !is_array( $txnData ) ) {
                $errorMessage = "Error processing scheduled transaction data. CU: {$currCU}, User Id: {$userId} No transaction data.";
                if ( $dryRun ) {
                    fprintf( $STAT, "Dry Run: Would send error email to HomeCU: $errorMessage\n" );
                } else {
                    dmserror( $DMSMAIL, 'FATAL', "$errorMessage\n" );
                }
            }

            $pName =  ( $pName == "" ) ? "HomeCU" : $pName;

            // set up the "banking" environment
            $HB_ENV["Cu"] = $currCU;
            $HB_ENV["cu"] = $currCU;
            $HB_ENV["Uid"] = $userId;

            $txnData["txn_id"] = $scheduleId;
            $txnData["approved_by"] = $approvedBy;
            $txnData["frequency"] = $repeatingParameters['interval'];
            $txnData["interval"] = $intervalCount;

            $transferResult = HandleTransferRequest( $dbh, $HB_ENV, $featureCode, $txnData, $dryRun );

            // get user info
            $userInfo = FetchUserInfo( $dbh, $currCU, $userId );

            if ( $transferResult["status"]["code"] != "000" ) {
                // show there was an error
                $data = "From account: {$txnData["txn"]["from"]}, To account: {$txnData["txn"]["to"]}, Amount: {$txnData["txn"]["amount"]}";
                $message = "Unable to post transaction $scheduleId for CU: $currCU, user: {$userInfo["user_name"]}\n";
                $message .= "\t\t\t\tRequest=$featureCode\tData=({$data})\n";
                $message .= "\t\t\t\tResponse: {$transferResult["status"]["error"]}\n";

                // add this error to the stats
                AddToGatheredTransferStats( $gatheredStats, $currCU, "failed", $message );
            } else {
                // now, finally, update the schedule's next trigger date
                $interval = $repeatingParameters["interval"];

                // NOTE: There are logic errors in TxNextInterval and the parameters will need to change when that issue is fixed.
                $trigger = TxNextInterval( $startDate, $interval, $intervalCount + 1 );
                // if the next trigger date is after the end date then null is returned - this is handled in the function

                // create a success message
                $message = "Updating next transaction date ($trigger) txid $scheduleId for user {$userInfo["user_name"]}.";

                if ( !$dryRun ) {
                    // update the next trigger date
                    $updated = UpdateNextTriggerDate( $dbh, $scheduleId, $trigger );

                    if ( !$updated ) {
                        dmserror( $DMSMAIL, 'FATAL', "Error updating next trigger date for schedule id $scheduleId (cu: $currCU)\n" );
                    }
                }

                // add this success to the stats
                AddToGatheredTransferStats( $gatheredStats, $currCU, "success", $message );
            }

        } // end if ( !fetchonly )

        $numProcessed++;
    } // end while

    return $numProcessed;
} // end ProcessScheduledTransfers

/**
* Added: 04/16/2018
* This function retrieves the offlinestat and offlineblurb attributes from the cuadmin table
* for the current CU
* Args:
*   pDbh - database handler
*   currCU - current CU admin
* Returns:
*   offlinestat (Y/N) and offlineblurb values from the database
* Logic:
* First, if offlinestat is "", offlinestat is set to "N"
* Then,  if offlinestat != "N", offlinestat is set to "Y", denoting anything that is not "N" is "Y"
*/
function GetCUAdminOfflineStatus($pDbh, $currCU) {
    $offlineStatSql = "SELECT trim(offlinestat), trim(offlineblurb)
                            FROM cuadmin
                            WHERE cu = '$currCU'";

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

    if ( !$rs ) {
        dmserror( $DMSMAIL, "FATAL", "Unable to obtain offline status from database: {$currCU}.\n" . db_last_error() . "\n$offlineStatSql\n" );
        //in case the execution passes, we set the flags to terminate it from the calling scope
        $returnOfflineStat = array( "offlinestat" => "unknown", "offlineblurb" => "unknown" );
    } else {
        $cuAdminRow = db_fetch_row( $rs );
        $thisOfflineStat = $cuAdminRow[0];

      # initial status "" is considered as "N"
      if ( $thisOfflineStat == "") {
          $thisOfflineStat = "N";
      }
      // anything other than "N" is considered "Y" i.e. U and R => Y
      if ( $thisOfflineStat != "N") {
          $thisOfflineStat = "Y";
      }

      $returnOfflineStat = array( "offlinestat" => $thisOfflineStat, "offlineblurb" => $cuAdminRow[1] );
    }

    return $returnOfflineStat;
} // end GetCUAdminOfflineStatus

/**
 * This function will perform all the ScheduledTransfers processing. All the configuration variables are passed in.
 * There are global variables used, as well.
 */
function ProcessAlerts( $config ) {
    global $dbh;
    global $dryRun, $dryRunPrefix;
    global $HB_ENV;
    global $STAT;
    global $TODAY;
    global $DMSMAIL, $ALERTMAIL;
    global $memberSQL, $timezoneSQL;
    global $gatheredStats, $fetchStats;

    $CU2_ALLOW_MBR_ALERTS = 33554432;       # Process Alerts flag
    $CU2_ESCHEMA = 2097152;       # Expanded loan history schema

    // Update: 04/19/2018
    // Gotten rid of the dms_ia_head file fetching process.
    // We now directly fetch the offline status of the CUs from the database
    // rather than reading the list of offline CUs list from
    // '/home/homecu/logs/dms_ia_head/dms_ia_head.log.not_online_up' log file

    if ( !$config["skipload"] ) {
      // delete from alert temporary tables for the runslot
      // delete from temporary banking data tables for runslot
      $memSQL = "DELETE FROM cutronus WHERE runslot = '{$config["runslot"]}';
                 DELETE FROM cutronab WHERE runslot = '{$config["runslot"]}';
                 DELETE FROM cutronah WHERE runslot = '{$config["runslot"]}';
                 DELETE FROM cutronlb WHERE runslot = '{$config["runslot"]}';
                 DELETE FROM cutronlh WHERE runslot = '{$config["runslot"]}'; ";
        if ( $dryRun ) {
          fprintf( $STAT, "Dry run: would be deleting from tables\n $memSQL\n" );
        } else {
          $rs = db_query( $memSQL, $dbh );
          if ( !$rs ) {
            dmserror( $DMSMAIL, 'FATAL', "Failed Deleting From Tables\n" . db_last_error( $rs ) );
          }

          fprintf( $STAT, "Deleting from tables complete.\n" );
      }

      // query all the alert member accounts into the temporary table
      # ###
      # Sort of complex logic based on how several settings can determine the status of cucode
      # cucode can be set by either the runtime slot list OR on command line with -c
      # In theory the -t option is mutually exclusive to either -c or the runslot list
      #
      # Therefore if cucode is blank AND if timezone is NOT set
      #  Then set query for cu = '', this will prevent the query from returning ALL the CUs on the server
      # Problem was discovered when a runslot was being executed but no files existed.  The cu query was left off
      #  so ALL CUs were returned
      #
      #

      if ( !$dryRun ) {
      // unresolved - I removed the timezone check as secondary to cu list - not sure why it was there
        $cuSQL = ($config["cucode"] != "" ? " AND a.cu IN ({$config["cucode"]}) " : "");
        $xcuSQL= ($config["xcucode"] != "" ? " AND a.cu NOT IN ({$config["xcucode"]}) " : "");

        // save the run in the cutronus table (accountnumber is used for alerts)
        $insert =  "INSERT INTO cutronus (runslot, cu, user_id, sched_id, accountnumber) ";

        // lastalert is used for balance and loan but not transaction alerts; boolean flag for checknumber
        $select = "SELECT DISTINCT '{$config["runslot"]}', alert.cu, 0, 0, alert.accountnumber
                   FROM cu_alerts alert
                   JOIN cuadmin a on a.cu = alert.cu
                     AND a.livebatch = 'L'
                     AND coalesce(a.offlinestat, 'N') not in ('Y','U')
                     AND (a.flagset2::bigint & {$CU2_ALLOW_MBR_ALERTS}::bigint) = {$CU2_ALLOW_MBR_ALERTS}
                       $timezoneSQL $cuSQL $xcuSQL
                   WHERE ((alert.alerttype IN ('B','T','L') AND (lastalert < CURRENT_DATE OR alert.lastalert IS NULL)) OR
                          (alert.alerttype = 'C' AND alert.lastalert IS NULL)) ";


        $shuffle = "UPDATE cutronus SET shuffle = random() where runslot = '{$config["runslot"]}'; ";

        // note the ';' before the shuffle query
        $sql = "$insert $select; $shuffle";
        $memRS = db_query( $sql, $dbh );

        if ( !$memRS ) {
          dmserror( $DMSMAIL,'FATAL',"Failed populating cutronus table with accounts to process\n" . db_last_error() . "\n$sql\n" );
          }

        fprintf( $STAT, "   SQL to gather alerts:\n\t\t\t $select\n" );
      }

      $now = date( "D M d H:i:s Y" );
      fprintf( $STAT, "Load Tables Complete $now\n" );
    }

    // Update 04/18/2018: We get rid of use of
    // /home/homecu/logs/dms_ia_head/dms_ia_head.log.not_online_up file
    // Previously: see if internet appliance "down" file exits
    // if ( false && !file_exists( $dms_ia_file ) ) {
    //  printf( $STAT, "Internet Appliance down list $dms_ia_file not found!\n" );
    //  dmserror( $DMSMAIL, "WARN", "Internet Appliance down list $dms_ia_file not found!\n" );
    // }

    // process credit union member accounts one at a time, randomly, tracking CU stats
    // NOTE: Cannot initially get user table info because need CU name as part of it.
    $memsql = "SELECT trim(tron.cu) as cu, trim(tron.accountnumber) as accountnumber
               FROM cutronus tron
               WHERE tron.runslot = '{$config["runslot"]}'";

    if ( $config["cucode"] != "" ) { $memsql .= " AND tron.cu in ({$config["cucode"]}) "; }
    if ( $config["xcucode"] != "" ) { $memsql .= " AND tron.cu not in ({$config["xcucode"]}) "; }
    if ( $config["member"] != "" ) { $memsql .= " AND accountnumber='{$config["member"]}' "; }

    $memsql .= " ORDER BY shuffle ";

    if ( $config["limit"] != "" ) { $memsql .= " limit {$config["limit"]} "; }

    fprintf( $STAT, "Using sql: $memsql\n" );

    $memRS = db_query( $memsql, $dbh );

    $cuInfoCache = array();
    $numProcessed = 0;
    $row = 0;
    while ( $alertRow = db_fetch_assoc( $memRS, $row++ ) ) {
      // start the timer for the gathering of this CU/account
      $thisTimeStart = microtime(true);
      $thisMessages = array();

      // gather credit union specific information - email, name, etc.
      $currCU = strtoupper( $alertRow["cu"] );
      $accountNumber = $alertRow["accountnumber"];

      // retrieve offlinestat, offlineblurb for currCU
      $offlineStat = GetCUAdminOfflineStatus($dbh, $currCU);

      if ( $offlineStat["offlinestat"] == "unknown" ) {
          dmserror( $DMSMAIL, "FATAL", "Unable to obtain offlinestat from database: {$currCU}.\n". db_last_error(). "\n" );
      }

      $isCUOffline = false;

      // continue the while loop if the offlinestat of the currCU is not N
      if ( $offlineStat["offlinestat"] != "N" ) {
          fprintf( $STAT, "{$currCU} is offline (status: '".$offlineStat["offlinestat"]."') ".$offlineStat["offlineblurb"]."'\n" );
          $thisMessages[] = "$row\tCU $currCU Account $accountNumber\tSkipped! Connection is down\n";

          $thisStat = array( "time" => 0, "messages" => $thisMessages );
          AddToGatheredAlertStats( $gatheredStats, $currCU, "gathering", $thisStat );
          // set the flat to indicate that the currCU is offline, just in case we don't want to continue here and wan't to do
          // further processing conditioning on this flag
          $isCUOffline = true;
          // continue with other record in $alertRow
          continue;
      }

      // Update 04/19/2018: conditioning on the isCUOffline flag as well
      if ( !$isCUOffline && !isset( $cuInfoCache[$currCU]) ) {
        // add the info for next time the credit union is used

        $cuSQL = "SELECT trim(pname) as pname, trim(user_name) as user_name,
                         flagset, flagset2, flagset3, trim(orgname) as orgname
                  FROM cuadmin
                  WHERE cu = '$currCU' ";

        $rs = db_query( $cuSQL, $dbh );

        if ( !$rs ) {
          $errorMessage = "Failed getting admin info: {$config["runslot"]} \n" . db_last_error() . " SQL: $cuSQL\n";
          dmserror( $DMSMAIL, 'WARN', $errorMessage );

          // cannot really go on
          continue;
        }

        $adminRow = db_fetch_assoc( $rs, 0 );

        // get the notification addresses
        $sql = "SELECT trim(email) as email, trim(role) as role FROM cuadmnotify
                WHERE cu = '$currCU'
                  AND role IN ('alert', 'txtbanking') ";
        $rs = db_query( $sql, $dbh );

        $row2 = 0;
        $alertNotify = "";
        $txtBankingNotify = "";
        while ( $notifyRow = db_fetch_assoc( $rs, $row2++ ) ) {
          if ( $notifyRow["role"] == "alert" ) {
            $alertNotify = trim( $notifyRow["email"] );
          } else {
            $txtBankingNotify = trim( $notifyRow["email"] );
          }
        }

        // need at least the "alert" email
        if ( strlen( $alertNotify ) == 0 ) {
          dmserror( $DMSMAIL, 'WARN', "Failed getting ALERTMAIL {$config["runslot"]} \n" . db_last_error() . " SQL: $sql\n" );

          // use HomeCU email
          $alertNotify = $ALERTMAIL;
        }

        $cuInfoCache[$currCU] = array(
                                  "alert" => $alertNotify,
                                  "txtbanking" => $txtBankingNotify,
                                  "flagset" => $adminRow["flagset"],
                                  "flagset2" => $adminRow["flagset2"],
                                  "flagset3" => $adminRow["flagset3"],
                                  "pname" => $adminRow["pname"],
                                  "user_name" => $adminRow["user_name"],
                                  "orgname" => $adminRow["orgname"]
                                );

      }

      // set the environment for this credit union
      LoadCUAdmin( $dbh, $currCU, $HB_ENV );

      // some errors require us to skip processing but we still want to record the message
      $thisSkip = $isCUOffline;

      // see if already gathered data on this From account
      if ( HaveAccountData( $dbh, $currCU, $accountNumber, $config["runslot"] ) ) {
          // pretend we have good status for later logic
          $status = "000";
      } else {
        $lockLevel = CheckIfAccountLocked( $dbh, $currCU, $accountNumber );

        // if readonly, still can get alerted
        if ( $lockLevel == "L" ) {
          $thisMessages[] = "$row\tCU $currCU Account $accountNumber\tSkipped! Account is locked ($lockLevel)\n";
        } else {
          // TODO: WHEN FUNCTIONALITY EXISTS, CHECK CROSS-ACCOUNT AUTHORITY WITH CORE

            // Update: 04/19/2018: just an extra conditioning to check whether to skip the current alert row with currCU
            if ( !$thisSkip ) {

            // since we are about to contact the core, check the nap setting
            if ( $config["nap"] > 0 ) {
                sleep( $config["nap"] );
            }

            // set up some environment since calling banking function
            $HB_ENV["Cu"] = $currCU;
            $HB_ENV["cu"] = $currCU;

            // NOTE: dryRun will fetch and evaluate but not process
            // set the email to "NULL" because we don't know if we have the actual member or just a user
            $memberData = array( "account" => $accountNumber, "cutoff" => $config["cutoff"], "packetstamp" => $config["pktstamp"], "email" => "NULL" );

            $fetchResp = FetchMemberDataTRON( $HB_ENV, $memberData );

            // For the packet status use the value returned from the core
            $status = $fetchResp["status"];

            if ( !isset( $fetchStats["Status_{$status}"] ) ) {
              $fetchStats["Status_{$status}"] = 1;
            } else {
              $fetchStats["Status_{$status}"]++;
            }

            // log each member access
            $reason = $fetchResp["data"]["requestDesc"];
            $now = date( "D M d H:i:s Y" );
            $thisMessages[] = "$row\tCU $currCU Account $accountNumber\t $status - $reason $now\n";

            // for the error code use the value returned from the function
            $code = $fetchResp["code"];

            if ( $code == '200' || $code == '201' || $code == '202' ) {
                // if just doing one credit union, give it a little quiet time
                if ( $config["cucode"] != "" ) {  sleep( $config["nap"] + 300 ); }
            } else if ( $code != '000' ) {
              $reason = trim( $fetchResp["data"]["requestDesc"] );
              if ( !strlen( $reason ) ) {
                $reason = "Unknown reason";
              }

              $thisMessages[] = "$row\tCU $currCU Member Account {$accountNumber}\tFailed! $code - $reason\n";
              $thisSkip = true;
            }

            if ( $code == '001' ) {
              # -- I want to send an email to the Credit Union
              # They have a user with alerts on an account that is/are NO longer set up
              # in the Credit Union but is/are still set up in HomeBanking
              $emailMsg = "PROCESSING ALERTS FAILED\n\n";
              $emailMsg .= "Member Account {$accountNumber} does not exist or is not set up on your core system.\n\n\n\n";

              # FETCH THE CU EMAIL ACCOUNT
              $errCUMail = FetchCUEmail( $dbh, $currCU );

              $errCUMail = ($errCUMail > "" ? $errCUMail : $DMSMAIL);

              if ( $dryRun ) {
                  fprintf( $STAT, "Dry Run: Would send error email to CU: $emailMsg\n" );
              } else {
                  MailCU( $errCUMail, $errCUMail, $emailMsg );
              }
            } else if ( $code == "000" ) {
              $accountData = $fetchResp["data"]["requestData"];
              // update the cutronab, cutronah, cutronlb, cutronlh tables with the data just retrieved
              $loadResp = UpdateTRONTables( $HB_ENV, $config["runslot"], $memberData, $accountData );

              if ( $loadResp["status"]["code"] != "000" ) {
                 $errorMessage = "Failure loading TRON tables: {$loadResp["status"]["error"]}.";

                  if ( $dryRun ) {
                      fprintf( $STAT, "Dry Run: Would send error email to HomeCU: $errorMessage\n" );
                  } else {
                      dmserror( $DMSMAIL, 'FATAL', $errorMessage );
                  }

                  // need to skip this transaction because of the error (only for dry run)
                  continue;
              }
            }
          }
        }
      }

      $thisTimeEnd = microtime(true);
      $thisTime = $thisTimeEnd - $thisTimeStart;

      $thisStat = array( "time" => $thisTime, "messages" => $thisMessages );
      AddToGatheredAlertStats( $gatheredStats, $currCU, "gathering", $thisStat );

      // no processing if fetchonly
      if ( !$thisSkip && !$config["fetchonly"] ) {
        // process all users of this CU's member account
        // NOTE: this will process all the alerts for the account
        $results = ProcessAlertsForAccount( $HB_ENV, $cuInfoCache[$currCU], $currCU, $accountNumber, $config );

        // gather the returned stats/messages
        AddToGatheredAlertStats( $gatheredStats, $currCU, "sent_stats", $results );
        AddToGatheredAlertStats( $gatheredStats, $currCU, "sending", $results );
      }

      $numProcessed++;
    }

    return $numProcessed;
} // end ProcessAlerts


// Process the alerts for the given credit union member account. It is assumed the
//  correct amount of the latest account data was obtained before calling this function.
function ProcessAlertsForAccount( $pHbEnv, $cuInfo, $currCU, $account, $config ) {
  global $STDOUT;
  global $STAT;
  global $DMSMAIL;

  $alertReturn = array( "time" => 0, "user_count" => 0, "alert_count" => 0, "messages" => array(), "alert_stats" => array() );

  $thisTimeStart = microtime(true);

  $dbh = $pHbEnv["dbh"];
  $orgname = $cuInfo["orgname"];

  // global configuration flag set in admin
  $CU_CALCRUNBAL = 64;

  $ALERTFROMADDR = "noreply@homecu.net";
  $ALERTFROMNAME = $orgname;

  // get any alert messages the CU has set up
  $shortAlertMsg = array();
  $longAlertMsg = array();
  $numAlertMsgs = 0;
  $sql = "SELECT message, sms_message
          FROM cualertmsgs
          WHERE cu ='$currCU'
            AND (startdate <= CURRENT_DATE AND CURRENT_DATE <= stopdate)
            AND runstat = 1 ";

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

  if ( !$rs ) {
    $errorMsg = "Failed getting AlertMsgs {$config["runslot"]} \n" . db_last_error() . " SQL: $sql\n";
    dmserror( $DMSMAIL, 'WARN', $errorMsg );

    // save the error
    $alertReturn["messages"][] = $errorMsg;
  } else {
    $row = 0;
    while ( $msgRow = db_fetch_assoc( $rs, $row++ ) ) {
       $longAlertMsg[] = $row["message"];
       $shortAlertMsg[] = $row["sms_message"];

       $numAlertMsgs++;
    }
}

  // set up all the alert select and update queries for this user
  $select = array();
  $update = array();

  // the select fields need to all be in a specific order
  $select["CurrentBalance"] =
     "SELECT alert.id, alert.user_id, alert.emailtype, trim(alert.notifyto) as notifyto,
             trim(alert.provider_id) as provider_id, trim(alert.notifymsg) as notifymsg,
             0 as incamt, 0 as amount,
             alert.incbal, acct.amount as balance, alert.alerttype
     FROM cu_alerts alert
     JOIN cutronab acct ON acct.cu = alert.cu
       AND acct.accountnumber = alert.accountnumber
       AND acct.accounttype = alert.accounttype
       AND acct.certnumber = alert.certnumber
       AND acct.runslot = '{$config["runslot"]}'
       AND acct.amount < alert.notifyamt
     WHERE alert.cu = '$currCU'
       AND alert.accountnumber='$account'
       AND (alert.lastalert IS NULL OR alert.lastalert < CURRENT_DATE)
       AND coalesce( alert.useavailbal, 0 ) = 0
       AND alert.alerttype = 'B' ";

  $update["CurrentBalance"] =
    "UPDATE cu_alerts SET lastalert = CURRENT_TIMESTAMP
     FROM cutronab acct
     WHERE acct.cu = cu_alerts.cu
      AND acct.accountnumber = cu_alerts.accountnumber
      AND acct.accounttype = cu_alerts.accounttype
      AND acct.certnumber = cu_alerts.certnumber
      AND acct.runslot = '{$config["runslot"]}'
      AND acct.amount < cu_alerts.notifyamt
      AND (cu_alerts.lastalert IS NULL OR cu_alerts.lastalert < CURRENT_DATE)
      AND cu_alerts.cu = '$currCU'
      AND cu_alerts.accountnumber = '$account'
      AND coalesce( cu_alerts.useavailbal, 0 ) = 0
      AND cu_alerts.alerttype = 'B' ";

  #########
  # Available Balance Alerts
  #########
  $select["AvailableBalance"] =
     "SELECT alert.id, alert.user_id, alert.emailtype, trim(alert.notifyto) as notifyto,
            trim(alert.provider_id) as provider_id, trim(alert.notifymsg) as notifymsg,
            0 as incamt, 0 as amount,
            alert.incbal, acct.available as balance, alert.alerttype
     FROM cu_alerts as alert
     JOIN cutronab as acct ON acct.cu = alert.cu
       AND acct.accountnumber = alert.accountnumber
       AND acct.accounttype = alert.accounttype
       AND acct.certnumber = alert.certnumber
       AND acct.runslot = '{$config["runslot"]}'
       AND acct.available < alert.notifyamt
     WHERE alert.cu = '$currCU'
       AND alert.accountnumber ='$account'
       AND (alert.lastalert IS NULL OR alert.lastalert < CURRENT_DATE)
       AND coalesce( alert.useavailbal, 0 ) > 0
       AND alert.alerttype = 'B' ";

  $update["AvailableBalance"] =
    "UPDATE cu_alerts SET lastalert = CURRENT_TIMESTAMP
     FROM cutronab acct
     WHERE acct.cu = cu_alerts.cu
      AND acct.accountnumber = cu_alerts.accountnumber
      AND acct.accounttype = cu_alerts.accounttype
      AND acct.certnumber = cu_alerts.certnumber
      AND acct.runslot = '{$config["runslot"]}'
      AND acct.available < cu_alerts.notifyamt
      AND (cu_alerts.lastalert IS NULL OR cu_alerts.lastalert < CURRENT_DATE)
      AND cu_alerts.cu = '{$currCU}'
      AND cu_alerts.accountnumber = '$account'
      AND coalesce( cu_alerts.useavailbal, 0 ) > 0
      AND cu_alerts.alerttype = 'B' ";

  #########
  # Check Alerts
  #########
  $select["Check"] =
     "SELECT alert.id, alert.user_id, alert.emailtype, trim(alert.notifyto) as notifyto,
             trim(alert.provider_id) as provider_id, trim(alert.notifymsg) as notifymsg,
             alert.incamt, acct.amount,
             0 incbal, 0 balance, alert.alerttype
      FROM cu_alerts as alert
      JOIN cutronah as acct ON acct.cu = alert.cu
       AND acct.accountnumber = alert.accountnumber
       AND acct.accounttype = alert.accounttype
       AND acct.certnumber = alert.certnumber
       AND trim(acct.checknumber) = trim(alert.notifychknum)
       AND acct.runslot = '{$config["runslot"]}'
      WHERE alert.cu = '{$currCU}'
       AND alert.accountnumber = '$account'
       AND alert.lastalert IS NULL
       AND alert.alerttype = 'C' ";
  $update["Check"] =
     "UPDATE cu_alerts SET lastalert = CURRENT_TIMESTAMP
      FROM cutronah as acct
      WHERE acct.cu = cu_alerts.cu
        AND acct.accountnumber = cu_alerts.accountnumber
        AND acct.accounttype = cu_alerts.accounttype
        AND acct.certnumber = cu_alerts.certnumber
        AND acct.runslot = '{$config["runslot"]}'
        AND acct.checknumber = cu_alerts.notifychknum
        AND cu_alerts.lastalert is null
        AND cu_alerts.cu = '{$currCU}'
        AND cu_alerts.accountnumber ='$account'
        AND cu_alerts.alerttype = 'C' ";

  $select["Payment"] =
     "SELECT alert.id, alert.user_id, alert.emailtype, trim(alert.notifyto) as notifyto,
             trim(alert.provider_id) as provider_id, trim(alert.notifymsg) as notifymsg,
             0 as incamt, 0 as amount,
             0 as incbal, 0 as balance, alert.alerttype
      FROM cu_alerts as alert
      JOIN cutronlb as loan ON loan.cu = alert.cu
       AND loan.accountnumber = alert.accountnumber
       AND loan.loannumber = alert.accounttype
       AND loan.runslot = '{$config["runslot"]}'
       AND (loan.nextduedate - (coalesce(alert.notifyloandaysprior, 0) || ' days')::interval) < CURRENT_TIMESTAMP
       AND loan.currentbalance > 0
      WHERE alert.cu = '{$currCU}'
       AND alert.accountnumber = '$account'
       AND (alert.lastalert IS NULL OR alert.lastalert < CURRENT_DATE)
       AND alert.alerttype = 'L' ";
  $update["Payment"] =
     "UPDATE cu_alerts SET lastalert = CURRENT_TIMESTAMP
      FROM cutronlb as loan
      WHERE loan.cu = cu_alerts.cu
        AND loan.accountnumber = cu_alerts.accountnumber
        AND loan.loannumber = cu_alerts.accounttype
        AND loan.runslot = '{$config["runslot"]}'
        AND (loan.nextduedate - (coalesce(cu_alerts.notifyloandaysprior, 0) || ' days')::interval) < CURRENT_TIMESTAMP
        AND (cu_alerts.lastalert IS NULL OR cu_alerts.lastalert < CURRENT_DATE)
        AND loan.currentbalance > 0
        AND cu_alerts.cu = '{$currCU}'
        AND cu_alerts.accountnumber='$account'
        AND cu_alerts.alerttype = 'L' ";

  # ##################
  # mws 2/11/2015 -
  #   removed the comparison to ~*, this is a regular expression compare and the string in alert.notifydesc could contain
  #   regular expression items ie ( ) {} [], this caused a problem when the member typed (:
  #   also, the extra processing for re is not desired.  found an equivalent with ilike
  #   ~* alert.notifydesc
  #   ilike '%' || alert.notifydesc || '%'
  # ##################
  // Just get the most recent trace number for the subaccount because all trace numbers were
  // examine as part of the query and wouldn't match the next time the query ran anyway.
  $select["Transaction"] =
    "SELECT alert.id, alert.user_id, alert.emailtype, trim(alert.notifyto) as notifyto,
            trim(alert.provider_id) as provider_id,
            case when coalesce(inctransdesc, 0) = 1
                  then trim(acct.description)
                  else trim(alert.notifymsg)
            end as notifymsg,
            alert.incamt, acct.amount,
            alert.incbal, acct.balance, alert.alerttype
     FROM cu_alerts as alert
     JOIN cutronah as acct ON acct.cu = alert.cu
      AND acct.accountnumber = alert.accountnumber
      AND acct.accounttype = alert.accounttype
      AND acct.certnumber = alert.certnumber
      AND acct.description ilike '%' || alert.notifydesc || '%'
      AND acct.runslot = '{$config["runslot"]}'
     WHERE alert.cu = '{$currCU}'
      AND alert.accountnumber = '$account'
      AND (coalesce( alert.notifyrange, 0 ) = 0
        OR (coalesce( alert.notifyrange, 0 ) > 0 AND abs(acct.amount) > alert.notifyamtmin AND abs(acct.amount) < alert.notifyamtmax))
      AND ((alert.notifytranstype = 'B')
        OR (alert.notifytranstype = 'D' AND acct.amount > 0)
        OR (alert.notifytranstype = 'W' AND acct.amount < 0))
      AND (alert.lasttrace IS NULL OR alert.lasttrace < acct.tracenumber)
      AND alert.alerttype = 'T' ";

  $update["Transaction"] =
     "UPDATE cu_alerts SET
            lastalert = CURRENT_TIMESTAMP,
            lasttrace = (SELECT max(tracenumber)
                         FROM cutronah as hist
                         WHERE  hist.cu = acct.cu
                           AND hist.accountnumber = acct.accountnumber
                           AND hist.accounttype = acct.accounttype
                           AND hist.certnumber = acct.certnumber
                           AND hist.runslot = '{$config["runslot"]}')
      FROM cutronah as acct
      WHERE acct.cu = cu_alerts.cu
        AND acct.runslot = '{$config["runslot"]}'
        AND acct.accountnumber = cu_alerts.accountnumber
        AND acct.accounttype = cu_alerts.accounttype
        AND acct.certnumber = cu_alerts.certnumber
        AND acct.description ilike '%' || cu_alerts.notifydesc || '%'
        AND (coalesce( cu_alerts.notifyrange, 0 ) = 0
          OR (abs(acct.amount) > cu_alerts.notifyamtmin AND abs(acct.amount) < cu_alerts.notifyamtmax))
        AND ((cu_alerts.notifytranstype = 'B')
          OR (cu_alerts.notifytranstype = 'D' AND acct.amount > 0)
          OR (cu_alerts.notifytranstype = 'W' AND acct.amount < 0))
        AND (cu_alerts.lasttrace IS NULL OR cu_alerts.lasttrace < acct.tracenumber)
        AND  cu_alerts.cu = '{$currCU}'
        AND cu_alerts.accountnumber='$account'
        AND cu_alerts.alerttype = 'T' ";

  // use an array to hold unique users for later counting
  $uniqueUserList = array();

  #
  # Since the logic for each alert type is so close, we churn thru
  # a hash of selects/updates to process the alerts.
  #
  #print STAT `date +'%a %b %d %T %Y %Z'`;
  foreach ( $select as $key => $query ) {
      $alertReturn["alert_stats"]["sent.$key"] = 0;
      $alertReturn["alert_stats"]["upd.$key"] = 0;

      $alertReturn["messages"][] = "Processing $currCU $account $key Alerts";

      $rs = db_query( $query, $dbh );

      if ( !$rs ) {
        $errorMsg = "Failed getting $key Alerts {$config["runslot"]} \n" . db_last_error() . " SQL: $query\n";
        dmserror( $DMSMAIL, 'WARN', $errorMsg );

        $alertReturn["messages"][] = $errorMsg;

        // keep going
        continue;
      }

      $alertIdList = array();
      $msgPick = 0;
      $row = 0;
      while ( $alertRow = db_fetch_assoc( $rs, $row++ ) ) {
        $userId = $alertRow["user_id"];
        if ( !in_array( $userId, $uniqueUserList ) ) {
          $uniqueUserList[] = $userId;
        }

        $alertReturn["alert_stats"]["sent.$key"]++;

        $alertType = $alertRow["alerttype"];

        $msg = $alertRow["notifymsg"];

        // what we show depends on alert type
        if ( $alertType != "L" ) {
          if ( $alertRow["incamt"] > 0 ) {
            $msg .= "\nAmount = {$alertRow["amount"]}";
          }

          if ( $alertType == "B" ) {
            $msg .= "\nBalance = {$alertRow["balance"]}";
          } else if ( $alertType == "T" ) {
            // only include the balance if the CU_CALCURUNBAL flag is not set
            // -- otherwise we haven't got a good balance to show
            if ( ($cuInfo["flagset"] & $CU_CALCRUNBAL) != $CU_CALCRUNBAL ) {
              if ( $alertRow["incbal"] > 0 ) {
                $msg .= "\nBalance = {$alertRow["balance"]}";
              }
            }
          }
        }

        $to = $alertRow["notifyto"];
        $alertLongCode = $cuInfo["txtbanking"];
        $alertMail = $cuInfo["alert"];

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

        if ( $alertRow["emailtype"] == 'W' ) {
          // wireless/SMS

          // customize the message based on type
          $preamble = "";
          switch( $alertType ) {
            case "B":
              $preamble .= "Low Balance";
              break;
            case "C":
              $preamble .= "Check Number";
              break;
            case "L":
              $preamble .= "Loan Payment Due";
              break;
            case "T":
              $preamble .= "Transaction Found";
              break;
          }
          $msg = "Account ending {$accountPortion} {$preamble}\n{$msg}";

          if ( isset( $shortAlertMsg[$msgPick] ) ) {
            $msg .= "\n" . $shortAlertMsg[$msgPick];
          }


          # Determine if I am sending this via Long code or mail

          // * Are we using AWS SMS or Long Code?

          if ($cuInfo['flagset3'] & GetFlagsetValue('CU3_LONGCODE_MFA')) {
            // ** Use a LONG CODE of some sort, if CU has a custom, then use it.
              // ** Otherwise use the Roundrobin list
            # check if CU has a longcode to use
            if ( preg_match( "/^[+]{0,1}[0-9]{1,12}$/", $alertLongCode ) && $alertLongCode != '' ) {
              SmsLongcode( 'SMS', $alertLongCode, $to, $msg, $account, '' );
            } else {
              # use our longcode
              SmsInternalLongcode( $cuInfo["alert"], $to, $msg, $account, $ALERTFROMNAME );
            }
          } else {
            // ** Send via AWS, but use SMSLongCode function to standardize message
            SmsLongcode( 'AWS', $alertLongCode, $to, $msg, $account, "$ALERTFROMNAME" );
          }
        } else {
          // html email

          // customize the message based on type
          $preamble = "";
          switch( $alertRow["alerttype"] ) {
            case "B":
              $preamble .= "The balance is below the specified amount.\n";
              break;
            case "C":
              $preamble .= "A check with the specified check number has been found.\n";
              break;
            case "L":
              $preamble .= "There is a loan payment due.\n";
              break;
            case "T":
              $preamble .= "A transaction has been found with the specified phrase.\n";
              break;
          }

          // add some words around the message
          $msg = "Account ending with $accountPortion has an alert.\n"
                 . "<br /><br />\n"
                 . "$preamble\n"
                 . "<br /><br />\n"
                 . "<pre>$msg</pre>\n";

          if ( isset( $longAlertMsg[$msgPick] ) ) {
            $msg .= "<hr>" . $longAlertMsg[$msgPick++];
          }

          HtmlMail( $alertMail, $to, $msg, $ALERTFROMADDR, $ALERTFROMNAME);
        }

        // count this alert
        $alertReturn["alert_count"]++;

        // rotate through the alert messages
        if ( $msgPick >= $numAlertMsgs ) {
          $msgPick = 0;
        }
      }

      if ( $rs ) {
        // update Last Alert Date for the affected alerts
#           print STAT "Updating $key Alerts. \n";

        $rs = db_query( $update[$key], $dbh );

#            print $conn->errorMessage,"\n"
        if ( !$rs ) {
          $errorMsg = "Failed updating $key Alerts {$config["runslot"]} \n" . db_last_error() . "SQL: {$update[$key]}\n";
          dmserror( $DMSMAIL, 'WARN', $errorMsg );

          $alertReturn["messages"][] = $errorMsg;

          // don't stop
        } else {
#            fprintf( $STAT, "$currCU $key Alert(s) Updated. $update[$key]\n" );
          // NOTE: the update count may not equal the sent count because the sent count is for the
          //    number of transactions found and the update count is the number of alert entries found.
          $alertReturn["alert_stats"]["upd.$key"] += db_affected_rows( $rs );
        }
      }
  } # End foreach

  // add the user count
  $alertReturn["user_count"] = count( $uniqueUserList );

  $thisTimeEnd = microtime(true);

  $alertReturn["time"] = $thisTimeEnd - $thisTimeStart;

  return $alertReturn;
} // end ProcessAlertsForAccount

/* See if we already retrieved member account data for this account/runslot.
 *
 * All accounts should have a share account which shows up in the cutronab table so only check that.
 * Accounts come in the form "D|1103|5|0" or as a simple account number. Since all subaccounts are
 * read with the balance info (cutronab is balances) we only need to check if the account number exists.
 */
function HaveAccountData( $pDbh, $pCurrCU, $pAccount, $pRunslot ) {
  $haveAccount = false;

  if ( strpos( $pAccount, "|" ) > 0 ) {
    $parts = explode( "|", $pAccount );
    $type = $parts[0];
    $account = $parts[1];
    $subAccount = $parts[2];
  } else {
    $account = $pAccount;
  }

  $sql = "SELECT count(*) as count
              FROM cutronab
              WHERE cu = '$pCurrCU'
                  AND runslot = '$pRunslot'
                  AND accountnumber = '$account'";

  $rs = db_query( $sql, $pDbh );
  if ( $rs ) {
      $accountRow = db_fetch_row( $rs, 0 );

      $haveAccount = $accountRow[0] > 0;
  }

  return $haveAccount;
} // end HaveAccountData

/* Take the raw data returned from the core and load into the TRON tables. Remove current runslot
 *  data first.
 */
function UpdateTRONTables( $pEnv, $pRunSlot, $pMemberData, $pRawData ) {
    $returnError = array(
        "status" => array( "code"=>'000', "error" => "" )
    );

    try {
      // Update tables cu_alertab, cu_alertah, cu_alertlb, cu_alertlh.
      $dbh = HCU_array_key_value('dbh', $pEnv);

      /* START TRANSACTION */
      $startRs = db_work( $dbh, HOMECU_WORK_BEGIN );

      // ** Traverse to the Inquiry Node
      $pktInquiryNode = $pRawData->Inquiry;

      /*
       * Removed cutronlh from the data insert.
       * This table has extra columns {fee, escrow} that not all credit union interfaces
       * provide.  However, this table is NOT used for the "Transaction" alerts.  Decided
       * to just not insert the tables.
       * This only has an impact if we ever decide to include loan history for the
       * "Transaction" alert searches.
       */
      $updTableList = array( "cutronab", "cutronah", "cutronlb" );
      $updTableMeta = array( "cutronab" => array( "node" => 'AccountBalance' ),
                                              "cutronah" => array( "node" => "AccountHistory"),
                                              "cutronlb" => array( "node" => "LoanBalance") );

      /* ** TABLE LOOP ** */
      foreach ( $updTableList as $updTableName ) {

        // ** First Delete existing rows
        $sql = "DELETE FROM $updTableName
                    WHERE runslot = '$pRunSlot'
                      AND cu = '{$pEnv["Cu"]}'
                      AND accountnumber = '" . prep_save( $pMemberData["account"], 12 ) . "'";
        $result = db_query( $sql, $dbh );

        // start the data copy
        $cp = "copy {$updTableName} from stdin with null as ''";
        $result = db_query( $cp, $dbh );

        // ** Search for the expected <node> in the packet
        $tblPktData = $pktInquiryNode->{ HCU_array_key_value( 'node', HCU_array_key_value( $updTableName, $updTableMeta ) ) };

        $dataRecords = explode("\n", $tblPktData);

        while (list($key, $line) = each($dataRecords)) {
            if ( trim( $line ) == "" ) {
               continue;
            }

            // ** massage some bits in the data -- sanitize special characters
            $line = preg_replace("/ ?\t ?/", "\t", $line);
            $line = preg_replace("/ $/", "", $line);
            $line = preg_replace("/\\0/", " ", $line);

            // add the runslot to the beginning
           # ADD RUNSLOT PRIOR TO CU CODE
           $line = "{$pRunSlot}\t{$pEnv["Cu"]}\t" . $line;

            pg_put_line( $dbh, $line . "\n" );
        }

        // ** Terminate the Copy with a \.
        $endCopyRs = pg_put_line( $dbh, "\\.\n" );

        if ( !$endCopyRs ) {
          // ** Error was found
          throw new Exception ("Error with the final pg_put_line");
        }

        $resultsRs = pg_end_copy( $dbh );
        if ( !$resultsRs ) {
          throw new Exception ("Error Performing the Copy");
        }
      }

      /* UPDATE TRANSACTION */
      $commitRs = db_work( $dbh, HOMECU_WORK_COMMIT );
      if ( !$commitRs ) {
        // ** FAILED COMMIT
        throw new Exception ("Failed to Commit Work");
      }
    } catch( Exception $e ) {
      $commitRs = db_work( $dbh, HOMECU_WORK_ROLLBACK );

      $returnError["status"]["code"] = "999";
      $returnError["status"]["error"] = $e->getMessage();
    }


return $returnError;
} // end UpdateTRONTables

/* Set up a transfer request. The request will be put into the <cu>transhdr/dtl tables and approved.
 *  The core will be sent the request if it is not an ACH transfer.
 *
 *  NOTE: look at hcuACH.i for transaction code meetings for ach-related transactions
 *
 *  @param array $pHbEnv -- The current HB_ENV array
 *  @param array $pTxnData  -- Array of data from the cu_scheduledtxn.txn_data field.
 *
 *  @return  structure with error or success
 */
function HandleTransferRequest( $pDbh, $pEnv, $pFeatureCode, $pTxnData, $pDryRun ) {
  global $DMSMAIL;
  global $STAT;

  $returnError = array(
      "status" => array( "code"=>'000', "error" => "" )
  );

  try {
    $transData = array();
    $fromMember = "";
    $transactionCode = "";
    $referenceId = 0;
    $emailNotify = 0;
    $inTransaction = false;
    $pTxnData["txn"]["memo"] = trim($pTxnData["txn"]["memo"]) != "" ? $pTxnData["txn"]["memo"] : "AUTO HB XFER";

    $transactionMemo = "";          // this is a memo for the overall transaction, not an addenda on an ACH
    // build the transdata json as the regular processing would have built it
    if ( $pFeatureCode == "TRN" ) {
      $srcAcctId = explode("|", $pTxnData["txn"]["from"]);
      $srcAcctKey = BuildAccountKey($pTxnData, "from");

      $dstAcctId = explode("|", $pTxnData["txn"]["to"]);
      $dstAcctKey = BuildAccountKey($pTxnData, "to");

      // add transdata
      $transData["acct_source"] = $pTxnData["txn"]["from"];
      $transData["acct_dest"] = $pTxnData["txn"]["to"];
      $transData['source_key'] = $srcAcctKey;
      $transData['dest_key'] = $dstAcctKey;

      // add any memo
      $transactionMemo = $pTxnData["txn"]["memo"];

      // add any misc1 field
      $misc1 = isset( $pTxnData["txn"]["misc1"] ) && strlen( $pTxnData["txn"]["misc1"] ) > 0 ? $pTxnData["txn"]["misc1"] : "";
      $transData['misc'] = $misc1;

      // get the from member account
      $parts = explode( "|", $pTxnData["txn"]["from"] );
      $fromMember = $srcAcctKey['accountnumber'];

      // reference id is the user_id of who made the transfer
      $referenceId = $pEnv["Uid"];
    } else if ( $pFeatureCode == "TRNEXT" ) {
      $localAcctKey = array();

      if ( $pTxnData["txn"]["type"] == "L2X" ) {
          $localAcct = $pTxnData["txn"]["from"];
          $localAcctId = explode("|", $pTxnData["txn"]["from"]);

          $localAcctKey = BuildAccountKey($pTxnData, "from");
          $remoteAcct = BuildExtAcctData( $pDbh, $pEnv, $pTxnData["txn"]["to"], "CR" );

          // reference id is the achpartner entry
          $referenceId = $pTxnData["txn"]["to"];
      } else {
          $localAcct = $pTxnData["txn"]["to"];
          $localAcctId = explode("|", $pTxnData["txn"]["to"]);

          $localAcctKey = BuildAccountKey($pTxnData, "to");
          $remoteAcct = BuildExtAcctData( $pDbh, $pEnv, $pTxnData["txn"]["from"], "DB" );

          // reference id is the achpartner entry
          $referenceId = $pTxnData["txn"]["from"] ;
      }

      // add any memo (this is not the addenda)
      $transactionMemo = $pTxnData["txn"]["memo"];

      if ( $pTxnData["txn"]["type"] == "L2X" ) {
          $transData["acct_source"] = $localAcct;
          $transData["acct_dest"] = $remoteAcct;
          $transData['source_key'] = $localAcctKey;

          // get the "from" member even though it isn't needed for authority with local -> external
          $parts = explode( "|", $localAcct );
          $fromMember = $localAcctKey['accountnumber'];
      } else {
          $transData["acct_source"] = $remoteAcct;
          $transData["acct_dest"] = $localAcct;
          $transData['dest_key'] = $localAcctKey;

          // no "from" member on external -> local
          $fromMember = "";
      }
    } else if ( $pFeatureCode == "TRNM2M" ) {
        $localAcct = $pTxnData["txn"]["from"];
        $localAcctId = explode("|", $pTxnData["txn"]["from"]);

        $localAcctKey = BuildAccountKey($pTxnData, "from");
        $transData["acct_source"] = $pTxnData["txn"]["from"];
        $transData['source_key'] = $localAcctKey;

        // get the from member account
        $parts = explode( "|", $pTxnData["txn"]["from"] );
        $fromMember = $localAcctKey['accountnumber'];

        // need "acct_dest"
        $remoteAcct = BuildM2MAcctData( $pDbh, $pEnv, $pTxnData["txn"]["to"], "DB" );

        $destId = "M|{$remoteAcct["rdfi"]["rdfi_account"]}|{$remoteAcct["rdfi"]["rdfi_account_type"]}";
        $remoteAcctKey = BuildM2MAccountKey($remoteAcct);
        $transData["acct_dest"] = $destId;
        $transData['dest_key'] = $remoteAcctKey;

        // add any memo
        $transactionMemo = $pTxnData["txn"]["memo"];

        // name goes in the "misc" field to match an immediate M2M transfer
        $transData['misc'] = $remoteAcctKey["name"];

        // reference id is the M2M entry (if ad-hoc supported, then it would be 0)
        $referenceId = $pTxnData["txn"]["to"] ;
    } else if ( $pFeatureCode == "ACHCOL" || $pFeatureCode == "ACHPMT" ) {
        $localAcctKey = array();
        if ( $pTxnData["txn"]["type"] == "L2R" ) {
            $localAcct = $pTxnData["txn"]["from"];
            $localAcctId = explode("|", $localAcct);

            $localAcctKey['accountnumber'] = $pTxnData['txn']['frommember'];
            $localAcctKey['recordtype'] = $localAcctId[0];
            if ($localAcctId[0] == "D") {
              $localAcctKey['accounttype'] = $localAcctId[2];
              $localAcctKey['certnumber'] = $localAcctId[3];
            } else if ($localAcctId[0] == "L") {
              $localAcctKey['loannumber'] = $localAcctId[2];
            }
            $remoteAcct = BuildACHData( $pDbh, $pEnv, $pTxnData["txn"]["to"] );

            // this is a credit to the remote account
            $remoteAcct["rdfi"]["rdfi_txn_type"] = "CR";

            // reference id is the achpartner entry
            $referenceId = $pTxnData["txn"]["to"];
        } else {
            $localAcct = $pTxnData["txn"]["to"];
            $localAcctId = explode("|", $localAcct);
            $localAcctKey['accountnumber'] = $pTxnData['txn']['tomember'];
            $localAcctKey['recordtype'] = $localAcctId[0];
            if ($localAcctId[0] == "D") {
              $localAcctKey['accounttype'] = $localAcctId[2];
              $localAcctKey['certnumber'] = $localAcctId[3];
            } else if ($localAcctId[0] == "L") {
              $localAcctKey['loannumber'] = $localAcctId[2];
            }
            $remoteAcct = BuildACHData( $pDbh, $pEnv, $pTxnData["txn"]["from"] );

            // this is a debit from the remote account
            $remoteAcct["rdfi"]["rdfi_txn_type"] = "DB";

            // reference id is the achpartner entry
            $referenceId = $pTxnData["txn"]["from"] ;
        }

        // get email notify value
        $emailNotify = $remoteAcct['remote_entity']['notify'] == "t" ? 1 : 0;
        // unset, this data is unnecessary for the transaction itself
        unset($remoteAcct['remote_entity']['notify']);

        // add any memo
        $transactionMemo = $pTxnData["txn"]["memo"];

        if ( isset( $pTxnData["txn"]["addenda"] ) && $pTxnData["txn"]["addenda"] != "" ) {
            $remoteAcct["rdfi"]["addenda"] = $pTxnData["txn"]["addenda"];
        }

        if ( $pTxnData["txn"]["type"] == "L2R" ) {
            $transData["acct_source"] = $localAcct;
            $transData["acct_dest"] = $remoteAcct;
            $transData['source_key'] = $localAcctKey;
            $transData['dest_key'] = array();

            // get the "from" member even though it isn't needed for authority with local -> external
            $parts = explode( "|", $localAcct );
            $fromMember = $localAcctKey['accountnumber'];
        } else {
            $transData["acct_source"] = $remoteAcct;
            $transData["acct_dest"] = $localAcct;
            $transData['source_key'] = array();
            $transData['dest_key'] = $localAcctKey;

            // no "from" member on external -> local
            $fromMember = "";
        }
    } else {
        // error - unknown feature
        $errorMessage = "Unknown feature code: $pFeatureCode.";

        // reference id is ?? if ad-hoc
        $returnError["status"]["code"] = "999";
        $returnError["status"]["error"] = $errorMessage;


        dmserror( $DMSMAIL, 'FATAL', $errorMessage );
        return $returnError;
    }

    if ( $pDryRun ) {
        // not making changes, but let person running the script know

        // read the user information
        $userSql = "SELECT user_name
                            FROM {$pEnv["Cu"]}user
                            WHERE user_id = {$pEnv["Uid"]} ";

        $userRS = db_query( $userSql, $pDbh );
        $userRow = db_fetch_row( $userRS, 0 );

        fprintf( $STAT, "Dry Run: Recording transfer for CU: {$pEnv["Cu"]}, User: {$userRow[0]},  Feature: $pFeatureCode, Amount: {$pTxnData["txn"]['amount']}, TxnId: {$pTxnData["txn_id"]} \n" );
    } else {

        // get some allowed amounts for validation
        $permissionInputs = array( "feature" => $pFeatureCode );
        $permissionInputs["amount"] = $pTxnData["txn"]['amount'];

        switch ($pFeatureCode) {
          case "TRN":
            $permissionInputs["account"] = $pTxnData["txn"]["frommember"];
            $permissionInputs["accounttype"] = $pTxnData["txn"]["fromsuffix"];
            break;
          case "TRNEXT":
            if ($pTxnData["txn"]["type"] == "X2L") { // If coming from external account, mimic transfer screen which sets the accountnumber to user_id and the accounttype to extaccount id.
              $permissionInputs["account"] = $pEnv["Uid"];
              $permissionInputs["accounttype"] = $pTxnData["txn"]["from"];
            } else {
              $sourceParts = explode("|", $pTxnData["txn"]["from"]);
              $permissionInputs["account"] = $sourceParts[1];
              $permissionInputs["accounttype"] = $sourceParts[2];
            }
            break;
          case "TRNM2M":
            $sourceParts = explode("|", $pTxnData["txn"]["from"]);
            $permissionInputs["account"] = $sourceParts[1];
            $permissionInputs["accounttype"] = $sourceParts[2];
            break;
          // TODO: Solve at a later time.
          case "ACHCOL":
            $sourceParts = explode("|", $pTxnData["txn"]["to"]);
            $permissionInputs["account"] = $sourceParts[1];
            $permissionInputs["accounttype"] = $sourceParts[2];
            break;
          case "ACHPMT":
            $sourceParts = explode("|", $pTxnData["txn"]["from"]);
            $permissionInputs["account"] = $sourceParts[1];
            $permissionInputs["accounttype"] = $sourceParts[2];
            break;
        }

        $limitTestResult = Perm_CheckLimits( $pDbh, $pEnv, $permissionInputs );

        if ($limitTestResult["status"]["code"] != "000") {
          throw new Exception( "Scheduled Transfer Error: " . $limitTestResult["status"]["error"]);
        }

        // for ach payment transactions we must check the
        // available funds.
        if ($pFeatureCode == "ACHPMT") {
          $memberInfoParams = array(
            "member"=>$localAcctKey['accountnumber'],
            "email"=>""
          );

          // get list of member accounts
          $memberInfo = FindMemberAccounts($pEnv, $memberInfoParams);
          if ($memberInfo['code'] != "000") {
            throw new Exception($memberInfo['error']);
          }

          $memberAccts = $memberInfo['data']['accounts'];
          $acctAvailable = 0;
          // iterate member account list to find the account available
          // balance.

          $acctFound = false;
          if (HCU_array_key_exists("deposit", $memberAccts)) {
            foreach ($memberAccts['deposit'] as $key => $value) {

              $acctNum = $value['accountnumber'];
              $acctType = $value['accounttype'];
              $acctCert = $value['certnumber'];
              // match account by accountnumber, accounttype and certnumber
              // recordtype can be hard match because loan accounts are not
              // allowed for ach transactions.
              $acctFound =
                ($acctNum == $localAcctKey['accountnumber']) &&
                ($acctType == $localAcctKey['accounttype']) &&
                ($acctCert == $localAcctKey['certnumber']);

              // get available balance and break loop
              if ($acctFound) {
                $acctAvailable = doubleval($value['available']);
                break;
              }
            }
          }

          $acctAmount = doubleval($pTxnData['txn']['amount']);
          if ($acctAvailable < $acctAmount) {
            throw new Exception("Scheduled Transfer Error: Transfer exceeds available funds.");
          }
        }

        // start a transaction
        $txnRs = db_work( $pDbh, HOMECU_WORK_BEGIN);
        $inTransaction = true;

        // record the transfer in the <cu>transhdr/transdtl table

        // all transactions use this info
        $recordVariables = array();
        $recordVariables["txFeatureCode"] = $pFeatureCode;
        $recordVariables["txPostedBy"] = $pEnv["Uid"];
        // ** Set the Authority Account for the transaction
        // This will always be the "from" member, but only used with calls to the core
        $recordVariables["txAuthAccount"] = $fromMember;
        $recordVariables["txTransCode"] = trim( $pTxnData["txn"]["transactioncode"] );
        $recordVariables["txComment"] = trim( $transactionMemo );

        $recordVariables["txReferenceId"] = $referenceId;
        $recordVariables["txAmount"] = $pTxnData["txn"]['amount'];
        $recordVariables["txNotify"] = $emailNotify;

        // put the transfer data in to a JSON string
        $jsonTransData = HCU_JsonEncode( $transData );
        $recordVariables["txData"] = $jsonTransData;

        // Setup transaction metadata.
        // Single or Recurring transaction, and the current interval. In this case
        // the record can have a frequency of One Time, which is Single.
        $transMeta = array();
        $transMeta["source"] = $pTxnData["txn_id"];
        $transMeta["recurr"] = $pTxnData["frequency"] == "OneTime" ? "no" : "yes";
        $transMeta["interval"] = $pTxnData["interval"];
        $jsonTransMeta = HCU_JsonEncode($transMeta);
        $recordVariables["txMeta"] = $jsonTransMeta;

        // the pHBEnv is needed for CU and logging
        $transferResult = RecordTransfer( $pDbh, $pEnv, $recordVariables );

        if ( $transferResult["status"]["code"] == "000" ) {
            // save the header id
            $transHeaderId = $transferResult["data"]["id"];
            $approvalInfo = array( "txId" => $transHeaderId,
                                                 "txApprover" => $pTxnData["approved_by"] );

            // scheduled transactions always approve
            $transferResult = MarkTransferApproved( $pDbh, $pEnv, $approvalInfo );

            // regular and M2M transfers post to the core right away; ACH-related wait for the admin process
            if ( $transferResult["status"]["code"] == "000" &&
                 ( $pFeatureCode == "TRN" || $pFeatureCode == "TRNM2M" ) ) {
                // populate all the transfer data
                $transferRecord = array();
                // use the source/dest determined above (since M2M is a calculated value)
                $transferRecord["acct_source"] = $transData["acct_source"];
                $transferRecord["acct_dest"] = $transData["acct_dest"];
                $transferRecord["misc"] = $transData["misc"];
                $transferRecord["accountnumber"] = $fromMember;
                $transferRecord["transactioncode"] = $pTxnData["txn"]["transactioncode"];
                $transferRecord["memo"] = trim( $transactionMemo );
                $transferRecord["amount"] = $pTxnData["txn"]["amount"];

                $transferResult = PostInternalTransfer( $pEnv, $transferRecord );

                // if no error, mark it processed
                if ( $transferResult["status"]["code"] === "000" ) {
                    // set up the query to mark the transaction as processed ("*sched*" means processed by scheduler processor)
                    $sql = "UPDATE {$pEnv["Cu"]}transhdr SET
                              processed_by = '*sched*',
                              processed_date = now(),
                              processed_status = 20
                            WHERE id = $transHeaderId";

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

        if ( $transferResult["status"]["code"] !== "000" ) {
            $errorMessage = "Feature: $pFeatureCode, UserId: {$pEnv["Uid"]}; Error Message: ";
            for ( $i = 0; $i < count( $transferResult["status"]["errors"] ); $i++ ) {
              $errorMessage .= $transferResult["status"]["errors"][$i] . " ";
            }
            throw new Exception( "Scheduled Transfer Error: $errorMessage" );
        }

        // if we made it here, let's commit the transaction
        $commitRs = db_work( $pDbh, HOMECU_WORK_COMMIT );
        if ( !$commitRs ) {
          // ** FAILED COMMIT
          throw new Exception ("Failed to Commit Work");
        }
    }

  } catch( Exception $e ) {
    $errorMessage = $e->getMessage();

    $returnError["status"]["code"] = "999";
    $returnError["status"]["error"] = $errorMessage;

    if ( $inTransaction ) {
        db_work( $pDbh, HOMECU_WORK_ROLLBACK );
    }
  }

  return $returnError;
} // end HandleTransferRequest

// Build a structure of external account data that would mimic the transdtl.transdata remote account info.
function BuildExtAcctData( $pDbh, $pEnv, $pAcctId, $pTxnType ) {
    $extInfo = array();

    // get the remote info from the external accounts table
    $extSQL = "SELECT display_name, remote_info
                        FROM {$pEnv["Cu"]}extaccount
                        WHERE id = {$pAcctId}";

    $rs = db_query( $extSQL, $pDbh );
    if ( !$rs ) {
        dmserror( $DMSMAIL, "WARN", "Unable to read  {$pEnv["Cu"]}extaccount for userId {$pEnv["Uid"]}, id $pAcctId.\n" . db_last_error() );
    } else {
        $extRow = db_fetch_assoc( $rs );

        $extData = HCU_JsonDecode( $extRow["remote_info"], false );
        if ( !$extData ) {
            dmserror( $DMSMAIL, "WARN", "Unable to parse json for {$pEnv["Cu"]}extaccount (for userId {$pEnv["Uid"]}), id $pAcctId.\n" . db_last_error() );
        } else {
            /* Here is the structures we are working with:
             *
             * this is in <cu>extaccount
             * {"rdfi":{"routing":"930840239","account":"8989","type":"10","name":"Mike Streymore"},
             *
             * this is in <cu>transdtl.transdata
             * {"rdfi":{"rdfi_routing":"930840239","rdfi_account":"8989","rdfi_account_type":"10","addenda":""},"remote_entity":{"name":"Mike Streymore"}}
             */
            $extDFI = array( "rdfi_routing" => $extData["rdfi"]["routing"],
                             "rdfi_account" => $extData["rdfi"]["account"],
                             "rdfi_account_type" => $extData["rdfi"]["type"],
                             "rdfi_txn_type" => $pTxnType,
                             "addenda" => "" );          // addenda is placeholder if scheduled txn has a memo
            $remoteEntity = array ( "name" => $extData["rdfi"]["name"],
                                    "entry_id" => $pAcctId,
                                    "display_name" => $extRow["display_name"] );

            $extInfo["rdfi"] = $extDFI;
            $extInfo["remote_entity"] = $remoteEntity;
        }
    }

    return $extInfo;
} // end BuildExtAcctData

// Build a structure of M2M account data that would mimic the transdtl.transdata m2m account info.
function BuildM2MAcctData( $pDbh, $pEnv, $pAcctId, $pTxnType ) {
    $extInfo = array();

    // get the remote info from the external accounts table
    $extSQL = "SELECT display_name, remote_info
                        FROM {$pEnv["Cu"]}extaccount
                        WHERE id = {$pAcctId}";

    $rs = db_query( $extSQL, $pDbh );
    if ( !$rs ) {
        dmserror( $DMSMAIL, "WARN", "Unable to read  {$pEnv["Cu"]}extaccount for userId {$pEnv["Uid"]}, id $pAcctId.\n" . db_last_error() );
    } else {
        $extRow = db_fetch_assoc( $rs );

        $extData = HCU_JsonDecode( $extRow["remote_info"], false );
        if ( !$extData ) {
            dmserror( $DMSMAIL, "WARN", "Unable to parse json for {$pEnv["Cu"]}extaccount (for userId {$pEnv["Uid"]}), id $pAcctId.\n" . db_last_error() );
        } else {
            /* Here is the structures we are working with:
             *
             * this is in <cu>extaccount
             * {"rdfi":{"routing":"930840239","account":"8989","type":"10","name":"Mike Streymore"},
             *
             * this is in <cu>transdtl.transdata
             * {"rdfi":{"rdfi_routing":"930840239","rdfi_account":"8989","rdfi_account_type":"10","addenda":""},"remote_entity":{"name":"Mike Streymore"}}
             */
            $extDFI = array( "rdfi_account" => $extData["rdfi"]["account"],
                             "rdfi_account_type" => $extData["rdfi"]["type"],
                             "rdfi_txn_type" => $pTxnType,
                             "addenda" => "" );          // addenda is placeholder if scheduled txn has a memo
            $remoteEntity = array ( "name" => $extData["rdfi"]["name"],
                                    "entry_id" => $pAcctId,
                                    "display_name" => $extRow["display_name"] );

            $extInfo["rdfi"] = $extDFI;
            $extInfo["remote_entity"] = $remoteEntity;
        }
    }

    return $extInfo;
} // end BuildM2MAcctData

// Build a structure for ACH data that would mimic the transdtl.transdata remote account info.
function BuildACHData( $pDbh, $pEnv, $pAcctId ) {
    $achInfo = array();

    // get the remote info from the external accounts table
    $achSQL = "SELECT ach_name, address, dfi_data, email_notify
                        FROM {$pEnv["Cu"]}achpartner
                        WHERE id = {$pAcctId}";

    $rs = db_query( $achSQL, $pDbh );
    if ( !$rs ) {
        dmserror( $DMSMAIL, "WARN", "Unable to read {$pEnv["Cu"]}achpartner for userId {$pEnv["Uid"]}, id $pAcctId.\n" . db_last_error() );
    } else {
        $achRow = db_fetch_assoc( $rs );

        $achDFI = HCU_JsonDecode( $achRow["dfi_data"], false );
        $achAddress = HCU_JsonDecode( $achRow["address"], false );
        if ( !$achDFI || !$achAddress ) {
            dmserror( $DMSMAIL, "WARN", "Unable to parse json for {$pEnv["Cu"]}achpartner (for userId {$pEnv["Uid"]}), id $pAcctId.\n" . db_last_error() );
        } else {
            /* Here is the structures we are working with:
             *
             * this is in <cu>achpartner
             *   {"address1":"123 Woods St.","address2":"","city":"Boise","state":"ID","zip":"83705","country":"","email":"mike2@homecu.net"}
             *   {"dfi_routing":"456389323","dfi_account":"123383","dfi_account_type":"10"}
             *
             * this is in <cu>transdtl.transdata
             * {{"acct_source":{"rdfi":{"rdfi_routing":"456389343","rdfi_account":"123383","rdfi_account_type":"10","rdfi_txn_type":"DB","addenda":"Testing addenda"},"remote_entity":{"name":"Mike Wabash","address1":"123 Woods St.","address2":"","email":"mike2@homecu.net"}},"acct_dest":"D|1103|10|0"}
             */
            $achData = array( "rdfi_routing" => $achDFI["dfi_routing"],
                                           "rdfi_account" => $achDFI["dfi_account"],
                                           "rdfi_account_type" => $achDFI["dfi_account_type"],
                                           "rdfi_txn_type" => "",     // placeholder so calling routine can put in the txn type
                                           "addenda" => "" );          // addenda is placeholder if scheduled txn has an addenda
            $remoteEntity = array ( "name" => $achRow["ach_name"],
                                                   "address1" => $achAddress["address1"],
                                                   "address2" => $achAddress["address2"],
                                                   "email" => $achAddress["email"],
                                                   "notify" => $achRow['email_notify'] );

            $achInfo["rdfi"] = $achData;
            $achInfo["remote_entity"] = $remoteEntity;
        }
    }

    return $achInfo;
} // end BuildACHData

// Make sure the user has ability to access the given account and rights to do the transfer.
// Accounts come in the form "D|1103|5|0" or an empty string if it is an external account.

/**
 * Verify if the user still has rights to this account for transferring
 *
 * @param integer $dbh - Current database handle
 * @param string $cu - Credit Union code
 * @param integer $userId - User Id for the user in question
 * @param array $pFromAcctKey - This is the FROM Account key used to lookup record (may be empty)
 * @param array $pToAcctkey - This is the TO Account key used to lookup record (may be empty)
 * @param string $pTransCode - The transaction code for this scheduled transaction.  This can change how the
 *                             to account is formed.  Needed for XA and XP transactions.
 *
 * @return boolean
 *            {true, false}  - Does the user have rights to the appropriate accounts to make this transfer
 */
function UserHasRightsToTransfer( $dbh, $cu, $userId, $pFromAcctKey, $pToAcctKey, $pTransCode ) {
  $hasRights = false;

  try {

    if (count($pFromAcctKey) == 0 && count($pToAcctKey) == 0) {
      throw new Exception ("No Valid Accounts");
    }

    /* Cross Account Transaction Codes */
    $tranCodeXLookup = Array("XA", "XP");

    /* ** VERIFY FROM ACCOUNT ** */
    if (count($pFromAcctKey)) {
      $fromAcct = FindUserAccountExists($dbh, $cu, $userId, $pFromAcctKey['recordtype'], $pFromAcctKey['accountnumber'],
                                        ($pFromAcctKey['recordtype'] == "D" ? $pFromAcctKey['accounttype'] : $pFromAcctKey['loannumber']),
                                        ($pFromAcctKey['recordtype'] == "D" ? $pFromAcctKey['certnumber'] : '')
                                        );
      if ($fromAcct === false) {
        dmserror( $GLOBALS['DMSMAIL'], "WARN", "Unable to read  {$cu}useraccount for userId $userId, account {$pFromAcctKey['accountnumber']}, accounttype {$pFromAcctKey['accounttype']}, recordtype {$pFromAcctKey['recordtype']}. \n" . db_last_error() );
        $hasFromRights = false;
      } else {
        // ** Read the data
        // test for rights
        if (count($pToAcctKey) == 0) {
          // internal -> external, so test ext_withdraw
          $hasFromRights = HCU_array_key_value("ext_withdraw", $fromAcct['data']) === 't';
        } else {
          // internal -> internal, so test int_withdraw
          $hasFromRights = HCU_array_key_value("int_withdraw", $fromAcct['data']) === 't';
        }

      }
    } else {
      $hasFromRights = true;
    }

    /* ** VERIFY TO ACCOUNT ** */
    if (count($pToAcctKey)) {

      if (in_array($pTransCode, $tranCodeXLookup)) {
        /*
         * Cross Account Lookup
         * Build the information slightly different
         *
         * To lookup in the useraccounts table, always send the from accountnumber as the accountnumber,
         *   AND construct the useraccounts.accounttype from sub-account#accountnumber (of cross account)
         *
         */

        $localAcctNbr = $pFromAcctKey['accountnumber'];
        $localAcctSfx = ($pToAcctKey['recordtype'] == "D" ? $pToAcctKey['accounttype'] : $pToAcctKey['loannumber']) . '#' . $pToAcctKey['accountnumber'];
        $localAcctCrt = '';
        $localRecType = ($pToAcctKey['recordtype'] == "D" ? 'T' : 'P');
      } else {
        $localAcctNbr = $pToAcctKey['accountnumber'];
        $localAcctSfx = ($pToAcctKey['recordtype'] == "D" ? $pToAcctKey['accounttype'] : $pToAcctKey['loannumber']);
        $localAcctCrt = ($pToAcctKey['recordtype'] == "D" ? $pToAcctKey['certnumber'] : '');
        $localRecType = $pToAcctKey['recordtype'];

      }
      $toAcct = FindUserAccountExists($dbh, $cu, $userId, $localRecType, $localAcctNbr, $localAcctSfx, $localAcctCrt);
      if ($toAcct === false) {
        dmserror( $GLOBALS['DMSMAIL'], "WARN", "Unable to read  {$cu}useraccount for userId $userId, account {$localAcctNbr}, accounttype {$localAcctSfx}, recordtype {$localAcctCrt}. \n" . db_last_error() );
        $hasToRights = false;
      } else {
        // ** Read the data
        // test for rights
        if (count($pFromAcctKey) == 0) {
          // internal -> external, so test ext_withdraw
          $hasToRights = HCU_array_key_value("ext_deposit", $toAcct['data']) === 't';
        } else {
          // internal -> internal, so test int_withdraw
          $hasToRights = HCU_array_key_value("int_deposit", $toAcct['data']) === 't';
        }

      }
    } else {
      $hasToRights = true;
    }

    // need both From and To rights to be successful
    $hasRights = $hasFromRights && $hasToRights;
  } catch( Exception $e ) {
    $hasRights = false;
  }

  return $hasRights;
} // end UserHasRightsToTransfer


// Update the given schedule record's next trigger date.
// NOTE: Also updates interval_count
// Return a boolean true on success, false on failure.
function UpdateNextTriggerDate( $pDbh, $pScheduleId, $pTrigger ) {
    $updateResult = false;

    // need to set to NULL if no trigger date
    $quotedTriggerDate = $pTrigger ? "'$pTrigger'" : "NULL";

    $sql = "UPDATE cu_scheduledtxn
                SET next_trigger_date = $quotedTriggerDate, interval_count = interval_count + 1
                WHERE id = $pScheduleId";

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

    $updateResult = $rs != false;

    return $updateResult;
} // end UpdateNextTriggerDate

// for getting data from appliance, get just the balances, put into cu_alerts so only have one set of tables and can use the runslot logic.
// write a function in sApiAppl.i file to get just the balances (mimic current function in sApplApi.std.i).
// we will stop using the alertxx tables and use the cu_alertxx tables instead (to use runslot logic).

// Send message to the given list; The "from" is only the first in the $from list.
function SmsMailTransfer( $from, $to, $message, $acct ) {

    // need to use comma separators
    $to = str_replace( ";", ",", $to );

    $fromParts = explode( ";", $from );
    $fromMail = $fromParts[0];

    $date = date( "D M d H:i:s Y" );

    $headers = "From: $fromMail\r\n";

    $subject = "Credit Union Recurring Transaction";

    $body = "$message\r\n\r\n$date";

    $notify = new ErrorMail;
    $notify->mailto = $to;
    $notify->mailfrom = $fromMail;
    $notify->subject = $subject;
    $notify->msgbody = $body;
    $notify->callingfunction = __FUNCTION__;
    $notify->file = __FILE__;
    $notify->SendMail();

} // end SmsMailTransfer

// Return the next long code from the roundrobin list.
function GetNextLongCode() {
  global $gRoundRobinCounter;
  global $LONGCODE_ROUNDROBIN;

  $idx = $gRoundRobinCounter % count( $LONGCODE_ROUNDROBIN );
  $gRoundRobinCounter++;

  return $LONGCODE_ROUNDROBIN[$idx];
} // end GetNextLongCode

// Send the text message using our round-robin, next long code.
function SmsInternalLongcode( $from, $to, $message, $acct, $orgname ) {
  $nextLongCode = GetNextLongCode();

  SmsLongcode( 'SMS', $nextLongCode, $to, $message, $acct, "($from) " . $orgname );
} // end SmsInternalLongcode

// Send a message to a single address from a single address.
function HtmlMail( $from, $to, $message, $hdrFromAddr, $hdrFromName) {

  $date = date( "D M d H:i:s Y" );

  $subject = "Credit Union Alert";

  $body = "<html><body>$message<br /><br /><span style='font-style: italic'>$date</span></body></html>";

  $notify = new ErrorMail;
  $notify->mailto = $to;
  $notify->mailfrom = $hdrFromAddr;
  $notify->mailfromname = $hdrFromName;
  $notify->subject = $subject;
  $notify->htmlMsgbody = $body;
  $notify->callingfunction = __FUNCTION__;
  $notify->file = __FILE__;
  $notify->SendMail();
} // end HtmlMail

// Send the text message using a longcode.
function SmsLongcode( $pType, $from, $to, $message, $acct, $orgname ) {

  # SEND using LONG CODE
  $smsMsg = ($orgname != '' ? "Account Alert from $orgname\n\n" . $message : "Credit Union Alert\n" . $message);

  #
  # -- Need to escape new lines so the perl long code script will keep the string on one line
    # When broken up it causes problems on the command line call
//  $message = preg_replace( "/\n/", "\\n", $message);

  if ($pType == 'SMS') {
    // use the global values for the api key and url
    SendLongCodeSMS ($GLOBALS['HOMECU_LONGCODE_API_KEY'], $GLOBALS['HOMECU_LONGCODE_URL'], $from, $to, $smsMsg);
  } elseif($pType == 'AWS') {
    // ** -- ATTEMPT TO SEND AS AWS SMS
    $msg_response = SendAwsSMS($to, $orgname, $smsMsg);
  }

} // end SmsLongcode

// Fetch the CU's notify email account
function FetchCUEmail( $dbh, $cu ) {
  global $DMSMAIL;

  $cuEmail = "";

  $sql = "SELECT rtrim(email)
              FROM cuadmnotify
              WHERE cu = '$cu'
                 AND role = 'transfernotify'";
  $rs = db_query( $sql, $dbh );
  if ( !$rs ) {
    dmserror( $DMSMAIL, "WARN", "Unable to get CU's transfernotify email: {$cu}. \n" . db_last_error() );
  } else {
    $cuRow = db_fetch_row( $rs );
    $cuEmail = $cuRow[0];
  }

  return $cuEmail;
} // end FetchCUEmail

// Fetch information from the user record
function FetchUserInfo( $dbh, $cu, $userId ) {
  global $DMSMAIL;

  $returnInfo = array();
  $sql = "SELECT user_name, email
              FROM {$cu}user
              WHERE user_id = $userId";
  $rs = db_query( $sql, $dbh );
  if ( !$rs ) {
    dmserror( $DMSMAIL, "WARN", "Unable to get user name and email: {$cu}, user_id: {$userId}. \n" . db_last_error() );
    $returnInfo = array( "user_name" => "unknown", "email" => "unknown" );
  } else {
    $cuRow = db_fetch_row( $rs );
    $returnInfo = array( "user_name" => $cuRow[0], "email" => $cuRow[1] );
  }

  return $returnInfo;
} // end FetchUserInfo

// Mail the supplied message to the given credit union email address.
function MailCU( $pFromMail, $pCUMail, $pMessage ) {
    // can only have one from email
    $fromParts = explode( ";", $pFromMail );
    $fromMail = $fromParts[0];

    // handle multiple destination emails; need to use comma separators
    $to = str_replace( ";", ",", $pCUMail );

    $date = date( "D M d H:i:s Y" );

    $subject = "HomeCU Member Processing Issue";

    $body = "$pMessage\r\n\r\n$date";

    $notify = new ErrorMail;
    $notify->mailto = $to;
    $notify->mailfrom = $fromMail;
    $notify->subject = $subject;
    $notify->msgbody = $body;
    $notify->callingfunction = __FUNCTION__;
    $notify->file = __FILE__;
    $notify->SendMail();

} // end MailCU

// Send email to user that a recurring transaction was made inactive.
function MailUserInactive( $CUMAIL, $cuName, $userEmail ) {

    $parts = explode( ";", $CUMAIL );
    $fromMail = $parts[0];

    $date = date( "D M d H:i:s Y" );
    $subject = "$cuName recurring transfers";

    // make an informational message
    $body = "This notice is to inform you your recurring transfer was set to inactive.\n\n";
    $body .= "If you want to manually transfer these funds please call the credit union to reschedule your recurring transfer.  \n\n";
    $body .= "$cuName\n\n$date";

    $notify = new ErrorMail;
    $notify->header = "Content-Type: text/html";
    $notify->mailto = $userEmail;
    $notify->mailfrom = $fromMail;
    $notify->subject = $subject;
    $notify->msgbody = $body;
    $notify->callingfunction = __FUNCTION__;
    $notify->file = __FILE__;
    $notify->cu = $cuName;
    $notify->SendMail();
} // end MailUserInactive

// Send email to user with the given warning message. The CU and current date will be added to the message.
function MailUserWarning( $CUMAIL, $cuName, $userEmail, $message ) {

    $parts = explode( ";", $CUMAIL );
    $fromMail = $parts[0];

    $date = date( "D M d H:i:s Y" );
    $subject = "$cuName recurring transfers";

    // make an informational message
    $body = "$message\n\n$cuName\n\n$date";

    $notify = new ErrorMail;
    $notify->mailto = $userEmail;
    $notify->mailfrom = $fromMail;
    $notify->subject = $subject;
    $notify->msgbody = $body;
    $notify->callingfunction = __FUNCTION__;
    $notify->file = __FILE__;
    $notify->cu = $cuName;
    $notify->SendMail();
} // end MailUserWarning

// Report the given error
function dmserror( $email, $errorType, $errorMessage ) {
  global $PIDFILE;

  // 9/16/2019: Do not send an email when there is an error.
  // They are not useful to support.
  // handle multiple email addresses
  $email = str_replace( ";", ",", $email );

  $subject = "Subject: HomeCU Live Background Processing Error";

  // make an informational message
  $body = "An error occured in " . __FILE__ . " during background processing.\n\n";
  $body .= "Error message: $errorMessage\n\n";
  $body .= "Timestamp: " . date( "D M d, Y G:i" );

  $notify = new ErrorMail;
  $notify->mailto = $email;
  $notify->subject = $subject;
  $notify->msgbody = $body;
  $notify->callingfunction = __FUNCTION__;
  $notify->file = __FILE__;
  $notify->SendMail();

  $stderr = fopen( "php://stderr", "w" );

  if ( !$stderr ) {
    $stderr = fopen( "php://stdout", "w" );
  }

  fwrite( $stderr,  "\nERROR OCCURRED DURING BACKGROUND PROCESSING!\n\n" );
  fwrite( $stderr,  "$errorMessage\n" );

  if ( $errorType == "FATAL" ) {
    unlink($PIDFILE);
    exit(1);
  }
}

# #################
# LOAD CREDIT UNION BLACK LIST
#   data is one line per credit union
# #################
/**
 * Open blacklist alerts file and build an array.
 * 09/19 Used to crete an SQL in clause, was getting concatenated without
 * a comma to contents of SQL query. Now just returns the array.
 * https://github.com/homecu/odyssey/issues/3069
 * @return array
 */
function ReturnBlackList() {
  global $WORKINGDIRECTORY;

  // Where is the blacklist file located
  $blacklistFile= $WORKINGDIRECTORY . "/alerts.blacklist.conf";
  $returnCUList = [];

  // OPEN THE FILE / RETRIEVE CONTENTS
  if (file_exists( $blacklistFile)) {
    $cuList = file($blacklistFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    # LOOP THROUGH EACH RECORD IN BLACKLIST
    for ( $i = 0; $i < count($cuList); $i++ ) {

        // Always use Uppercase value of cucode
        $returnCUList[] = trim(strtoupper($cuList[$i]));
    }
  }

  return $returnCUList;

}

# ######################
# LOAD CREDIT UNION INCLUDE LIST
#  one line per credit union in format CUCODE ; time1; time2; ... timeN;
# ######################
# Usage $x = ReturnRunSlotList('0815');
# param1 - currRunSlot - string - this is the 4 character runslot to match in the include file
# returns list of quoted CU codes.
function ReturnRunSlotList( $pCurrRunSlot ) {
  global $WORKINGDIRECTORY;

  $runslitFile = $WORKINGDIRECTORY . "/alerts.runslotlist.conf";
  $returnCUList = "";


  # OPEN THE FILE / RETRIEVE CONTENTS
  if ( file_exists( $runslitFile ) ) {
    $slotList = file( $runslitFile );
    # LOOP through each record in the INCLUSION File
    for ( $i = 0; $i < count( $slotList ); $i++ ) {
      $line = trim( $slotList[$i] );

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

      # Match the appropriate time slot
      # each line of file should be formatted as
        # Be sure to put the credit union in the blacklist file if you do NOT want it to run with timezone
      # CUCODE ;0600;0815;
      # CUCODE ;0945;
      # CUCODE ;0430;1100;
      $cuCode = trim( $parts[0] );

      for ( $s = 1; $s < count( $parts ); $s++ ) {
        if ( trim( $parts[$s] ) == trim( $pCurrRunSlot ) ) {
          $returnCUList .= ( $returnCUList != "" ? ", " : "" ) . "'" . strtoupper( $cuCode ) . "'";
          // break out of this loop
          break;
        }
      }
    }
  }

  return $returnCUList;
} // end ReturnRunSlotList

// Add the given message to the gathered stats slot for the given credit union.
function AddToGatheredTransferStats( &$pHolderArray, $pCurrCU, $pSlot, $pMessage ) {
  if ( !isset( $pHolderArray[$pCurrCU ] ) ) {
      // set up a new slot
      $pHolderArray[$pCurrCU] = array( "count" => 0,
                                       "failed" => array( "count" => 0, "messages" => array() ),
                                       "rights" => array( "count" => 0, "messages" => array() ),
                                       "success" => array( "count" => 0, "messages" => array() ),
                                       "unknown" => 0 );
  }

  if ( $pSlot == "count" ) {
      $pHolderArray[$pCurrCU]["count"]++;
  } else if ( in_array( $pSlot, array( "failed", "rights", "success" ) ) ) {
      // this would handle failed, rights, success
      $pHolderArray[$pCurrCU][$pSlot]["count"]++;
      $pHolderArray[$pCurrCU][$pSlot]["messages"][] = $pMessage;
  } else {
      $pHolderArray[$pCurrCU]["unknown"]++;
  }

  return;
} // end AddToGatheredTransferStats

// Paired function with AddToGatheredTransferStats to print out the stats.
function PrintGatheredTransferStats( $pOutputHandle, $pHolderArray ) {
    $output = $pOutputHandle ? $pOutputHandle : fopen('php://stdout', 'w');

    // format the output a little
    $padding = str_repeat( " ", 15 );

    // print the count
    fprintf( $output,  "Credit Union transactions processed:\n" );
    fprintf( $output,  "CU\t\tCount\tMessages\n" );
    foreach( $pHolderArray as $thisCU => $cuStats ) {
        $thisCUPrint = substr( $thisCU . $padding, 0, 15 );
        fprintf( $output,  "$thisCUPrint\t{$pHolderArray[$thisCU]["count"]}\n" );

        // show any failed messages
        if ( isset( $pHolderArray[$thisCU]["failed"] ) && $pHolderArray[$thisCU]["failed"]["count"] > 0 ) {
            $failedInfo = $pHolderArray[$thisCU]["failed"];
            fprintf( $output, "\n$padding\t\tNumber of failures: {$failedInfo["count"]}\n" );

            for ( $i = 0; $i < count( $failedInfo["messages"] ); $i++ ) {
                fprintf( $output, "$padding\t\t{$failedInfo["messages"][$i]}\n" );
            }
        }

        // show any success messages
        if ( isset( $pHolderArray[$thisCU]["success"] ) && $pHolderArray[$thisCU]["success"]["count"] > 0 ) {
            $successInfo = $pHolderArray[$thisCU]["success"];
            fprintf( $output, "\n$padding\t\tNumber of successful updates: {$successInfo["count"]}\n" );

            for ( $i = 0; $i < count( $successInfo["messages"] ); $i++ ) {
                fprintf( $output, "$padding\t\t{$successInfo["messages"][$i]}\n" );
            }
        }

        // show any access rights issues
        if ( isset( $pHolderArray[$thisCU]["rights"] ) && $pHolderArray[$thisCU]["rights"]["count"] > 0 ) {
            $rightsInfo = $pHolderArray[$thisCU]["rights"];
            fprintf( $output, "\n$padding\t\tNumber of access rights issues: {$rightsInfo["count"]}\n" );

            for ( $i = 0; $i < count( $rightsInfo["messages"] ); $i++ ) {
                fprintf( $output, "$padding\t\t{$rightsInfo["messages"][$i]}\n" );
            }
        }

        // show any unknown calls
        if ( isset( $pHolderArray[$thisCU]["unkonwn"] ) && $pHolderArray[$thisCU]["unknown"] > 0 ) {
            $unknownCount = $pHolderArray[$thisCU]["unknown"];
            fprintf( $output, "\n$padding\t\tNumber of unknown statistic calls (need to fix): {$unknownCount}\n" );
        }

    }

    return;
} // end PrintGatheredTransferStats

// Add the given message to the gathered stats slot for the given credit union.
// This assumes a specific structure that is returned from the ProcessAlertsForAccount() function.
function AddToGatheredAlertStats( &$pHolderArray, $pCurrCU, $pCategory, $pResults ) {
  if ( !isset( $pHolderArray[$pCurrCU ] ) ) {
      // set up a new slot
      $pHolderArray[$pCurrCU] = array( "gathering" => array( "time" => 0, "count" => 0, "messages" => array() ),
                                       "sending" => array( "time" => 0, "user_count" => 0, "alert_count" => 0, "messages" => array() ),
                                       "sent_stats" => array(),
                                       "unknown" => 0 );
  }

  if ( $pCategory == "gathering" ) {
    // gathering is per credit union account
    $pHolderArray[$pCurrCU]["gathering"]["count"]++;

    $pHolderArray[$pCurrCU]["gathering"]["time"] += $pResults["time"];

    // add any messages
    if ( count( $pResults["messages"] ) > 0 ) {
      $pHolderArray[$pCurrCU]["gathering"]["messages"] = array_merge( $pHolderArray[$pCurrCU]["gathering"]["messages"], $pResults["messages"] );
    }
  } else if ( $pCategory == "sending" ) {
    // sending is all users of the account and all alerts sent
    $pHolderArray[$pCurrCU]["sending"]["user_count"] += $pResults["user_count"];
    $pHolderArray[$pCurrCU]["sending"]["alert_count"] += $pResults["alert_count"];

    $pHolderArray[$pCurrCU]["sending"]["time"] += $pResults["time"];

    // add any messages
    if ( count( $pResults["messages"] ) > 0 ) {
      $pHolderArray[$pCurrCU]["sending"]["messages"] = array_merge( $pHolderArray[$pCurrCU]["sending"]["messages"], $pResults["messages"] );
    }
  } else if ( $pCategory == "sent_stats" ) {
    // add any specific alert counts
    $arrayKeys = array_keys( $pResults["alert_stats"] );
    for ( $i = 0; $i < count( $arrayKeys ); $i++ ) {
      // initialize if not used yet
      if ( !isset( $pHolderArray[$pCurrCU]["sent_stats"][$arrayKeys[$i]] ) ) {
        $pHolderArray[$pCurrCU]["sent_stats"][$arrayKeys[$i]] = 0;
      }

      // add the current count to the existing
      $pHolderArray[$pCurrCU]["sent_stats"][$arrayKeys[$i]] += $pResults["alert_stats"][$arrayKeys[$i]];
    }
  } else {
    $pHolderArray[$pCurrCU]["unknown"]++;
  }

  return;
} // end AddToGatheredAlertStats

// Paired function with AddToGatheredAlertStats to print out the stats.
function PrintGatheredAlertStats( $pOutputHandle, $pHolderArray ) {
  $output = $pOutputHandle ? $pOutputHandle : fopen('php://stdout', 'w');

  // print the count
  fprintf( $output,  "Credit Union Alerts Processed:\n" );

  foreach( $pHolderArray as $thisCU => $cuStats ) {
    fprintf( $output,  "{$thisCU}\n" );

    $timeGathering = round( $pHolderArray[$thisCU]["gathering"]["time"], 1 );
    fprintf( $output,  "\tGathered Count\t{$pHolderArray[$thisCU]["gathering"]["count"]}\t{$timeGathering} seconds\n" );

    // show the gathering messages if there are any
    if ( isset( $pHolderArray[$thisCU]["gathering"]["messages"] ) &&
         count( $pHolderArray[$thisCU]["gathering"]["messages"] ) > 0 ) {
      fprintf( $output, "\tGathering Messages\n" );

      for ( $i = 0; $i < count( $pHolderArray[$thisCU]["gathering"]["messages"] ); $i++ ) {
        fprintf( $output, "\t\t{$pHolderArray[$thisCU]["gathering"]["messages"][$i]}\n" );
      }
    }

    $timeSending = round( $pHolderArray[$thisCU]["sending"]["time"], 1 );
    fprintf( $output,  "\tSent Alerts\t{$pHolderArray[$thisCU]["sending"]["alert_count"]}\tSent Users\t{$pHolderArray[$thisCU]["sending"]["user_count"]}\t{$timeSending} seconds\n" );

    // show the gathering messages if there are any
    if ( isset( $pHolderArray[$thisCU]["sending"]["messages"] ) &&
         count( $pHolderArray[$thisCU]["sending"]["messages"] ) > 0 ) {
      fprintf( $output, "\tSending Messages\n" );

      for ( $i = 0; $i < count( $pHolderArray[$thisCU]["sending"]["messages"] ); $i++ ) {
        fprintf( $output, "\t\t{$pHolderArray[$thisCU]["sending"]["messages"][$i]}\n" );
      }
    }


    $nameWidth = 25;
    $statisticTitle = substr( "Statistics" . str_repeat( " ", $nameWidth ), 0, $nameWidth );
    fprintf( $output,  "\t{$statisticTitle}\tCount\n" );

    $stats = array_keys( $pHolderArray[$thisCU]["sent_stats"] );
    for ( $i = 0; $i < count( $stats ); $i++ ) {
      // align the first column
      $statistic = substr( $stats[$i] . str_repeat( " ", $nameWidth ), 0, $nameWidth );
      fprintf( $output,  "\t{$statistic}\t{$pHolderArray[$thisCU]["sent_stats"][$stats[$i]]}\n" );
    }
  }

  return;
} // end PrintGatheredAlertStats


/**
 *  This function will return either the {dest_key} or {source_key} information from the
 *    transdtl table for this record.
 *  It will accept the txn_data and parse the correct information from the record.
 *  NOTE: The accounts passed in are expected to be local accounts.
 *
 *  @param array $pTxnData  - The txn_data for this transdtl record
 *  @param string $pKey     - The particular key to retrieve.  Options are {from, to}
 *
 *  @return array
 *      [recordtype]
 *      [accountnumber]
 *  FOR DEPOSITS
 *      [accounttype]
 *      [certnumber]
 *  FOR LOANS
 *      [loannumber]
 */
function BuildAccountKey($pTxnData, $pKey) {

  $acctKey = Array();

  if (HCU_array_key_exists("txn", $pTxnData)) {
    $lookup = Array(
                "acctid" => ($pKey == "from" ? "from" : "to"),
                "member" => ($pKey == "from" ? "frommember" : "tomember"),
                "accounttype" => ($pKey == "from" ? "fromsuffix" : "tosuffix"),
                "recordtype" => ($pKey == "from" ? "fromtype" : "totype")
              );

    $acctId = HCU_AcctIdExplode(HCU_array_key_value($lookup["acctid"], $pTxnData["txn"]));

    $acctKey['accountnumber'] = HCU_array_key_value($lookup["member"], $pTxnData['txn']);

    $acctKey['recordtype'] = HCU_array_key_value($lookup["recordtype"], $pTxnData['txn']);

    if ($acctKey['recordtype'] == "D") {
      $acctKey['accounttype'] = HCU_array_key_value($lookup["accounttype"], $pTxnData['txn']);
      $acctKey['certnumber'] = $acctId["segment4"];

    } else if ($acctKey['recordtype'] == "L" || $acctKey['recordtype'] == "C") {
      $acctKey['loannumber'] = HCU_array_key_value($lookup["accounttype"], $pTxnData['txn']);
      // ** For Credit card records the recordtype should be 'L'
      $acctKey['recordtype'] = "L";
    }

  }

  return $acctKey;
}

/**
 *  This function will return either the {dest_key} information from the
 *    transdtl table for this record.
 *  It will accept the remote account data and parse the correct information from that.
 *  NOTE: The account passed in are expected to be an M2M account.
 *
 *  @param array $pTxnData  - The txn_data for this transdtl record
 *  @param string $pKey     - The particular key to retrieve.  Options are {from, to}
 *
 *  @return array
 *      [accountnumber]
 *      [accounttype]
 *      [display_name]
 *      [name]
 *      [addenda]
 *      [ext_id]
 */
function BuildM2MAccountKey( $pAcctData ) {
  $acctKey = Array();

  $acctKey['accountnumber'] = $pAcctData["rdfi"]["rdfi_account"];
  $acctKey['accounttype'] = $pAcctData["rdfi"]["rdfi_account_type"];
  $acctKey['display_name'] = $pAcctData["remote_entity"]["display_name"];
  $acctKey['name'] = $pAcctData["remote_entity"]["name"];
  $acctKey['addenda'] = $pAcctData["rdfi"]["addenda"];
  $acctKey['ext_id'] = $pAcctData["remote_entity"]["entry_id"];


  return $acctKey;
} // end BuildM2MAccountKey

/**
 * function ReturnIgnoreList($dbh)
 * Returns a comma delimited list of CUs in migration determined
 * by migration status found witin the system_options field
 * in the cuinfo table.
 * 09/19 Getting concatenated with blacklist without comma, just return the array.
 *
 * @param $dbh - database connection
 * @throws Exception
 * @return array $returnCUList - list of CUs in migration
 */
function ReturnIgnoreList($dbh) {

  // constants - taken from cu_top.i, can't include since running this file from crontab
  $SYS_TYPE_CLOSED  = 64;
  $SYS_TYPE_UPG_TEST = 128;   // ** TESTING/SETUP
  $SYS_TYPE_UPG_BETA = 256;   // ** BETA
  $returnCUList = [];

  $sql = "select user_name from cuinfo" .
         " where (coalesce(system_options, 0) & $SYS_TYPE_CLOSED) = 0" .
         " and ((coalesce(system_options, 0) & $SYS_TYPE_UPG_TEST) <> 0" .
         " or (coalesce(system_options, 0) & $SYS_TYPE_UPG_BETA) <> 0)" .
         " order by user_name";

  $sth = db_query($sql, $dbh);
  if (!$sth) {
    throw new Exception ("Select query failed.", 2);
  }

  $results = db_fetch_all($sth);
  $results = $results === false ? array() : $results;

  if (count($results) > 0) {
    foreach ( $results as $cuName) {
      // Always use Uppercase value of cucode
      $returnCUList[] = trim(strtoupper($cuName["user_name"]));
    }
  }

  return $returnCUList;

}