cu_notify.data

<?php
/**
 * FILE: cu_notify.data
 * PURPOSE: pull the data functions out of cu_notify.prg in order to use data test cases against them.
 */

/**
 * function GetTemporaryEmail()
 * Get the email to use when a CU email has not been set up yet.
 *
 * @return string
 */
function GetTemporaryEmail() {
    return "unattended@homecu.com";
}

/**
 * function UpdateVerifiedEmailList($dbh, $Cu, $emailList)
 * This is called with the "update" button on the popup for "Manage Email List."  It is going to:
 * 1) Remove emails from AWS SES that were removed from the kendoGrid.
 * 2) Send verification emails to new emails added to the grid.
 * 3) Save the entire list into the database under the "SES" role.
 * If there are any errors, the previous emailList will show up with the error message.
 *
 * @param $dbh -- the database connection.
 * @param $Cu -- the credit union.
 * @param $emailList -- the email list from the kendoGrid. (dataSource.data())  "Deleted" emails are in the kendoGrid's data but are filtered out in the display.
 *
 * @return "status" -- "000" if successful, nonzero otherwise.
 * @return "error" -- The error message returned with the status code.
 * @return "verifiedEmailList" -- If the function is successful, this will be the email list constructed from the changes (adds and removals.)
 *          Otherwise, this will be the starting $emailList.
 */
function UpdateVerifiedEmailList($dbh, $Cu, $emailList) {

    try {

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

        // 2) Check to see if any of the emails are being used by the manage script.  Set the "used" flag to true.
        $results = CheckIfEmailIsUsed($dbh, $Cu, $allEmails);
        if ($results["status"] !== "000") {
            throw new exception ($results["error"], 9);
        }

        $results = _ModifyVerifiedEmailListWithUsedEmails ($results["usedEmailList"], $verifiedEmailList, $deletedEmails);
        if ($results ["status"] !== "000") {
            throw new exception ($results["error"], 14);
        }
        extract($results["data"]);

        if (count($unverifiedEmails) > 0) {
            // 3) Send emails with status of empty to Amazon.  Set them to "Pending" for the grid and verified = false in the database.
            $pyFile = dirname(__FILE__) . "/../../shared/scripts/verifySESEmail.py";
            $pyAction = "verifyEmails";
            $cmd = "python3 $pyFile -a $pyAction -e " . '"'    . implode(" ", $unverifiedEmails) . '"';
            $response = HCU_JsonDecode(exec($cmd));
            if ($response["status"] !== "000") {
                throw new exception ($response["error"], 8);
            }
        }

        if (count($deletedEmails) > 0) {
            // 4) Send deleted emails to Amazon so that Amazon can remove the identities.
            $pyFile = dirname(__FILE__) . "/../../shared/scripts/verifySESEmail.py";
            $pyAction = "removeEmails";
            $cmd = "python3 $pyFile -a $pyAction -e " . '"'    . implode(" ", $deletedEmails) . '"';
            $response = HCU_JsonDecode(exec($cmd));
            if ($response["status"] !== "000") {
                throw new exception ($response["error"], 10);
            }
        }

        // 5) Check if SES role exists for CU.
        $sql = "select email from cuadmnotify where cu = '" . prep_save($Cu, 10) . "' and role = 'SES'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception("Email query failed.", 5);
        }

        // 6) If role exists
        if (db_num_rows($sth) > 0) {

            // 6a) Update role with new values from the grid.
            $sql = "update cuadmnotify set email = '" . prep_save(HCU_JsonEncode(array_values($databaseEmailList))) . "'
                    where cu = '" . prep_save($Cu, 10) . "' and role = 'SES'";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("Update query failed.", 6);
            }

        // 7) If role doesn't exist
        } else {

            // 7a) Create role with new values from the grid.
            $sql = "insert into cuadmnotify (cu, role, email) values ('" . prep_save($Cu, 10) . "', 'SES', '" .
                    prep_save(HCU_JsonEncode(array_values($databaseEmailList))) . "')";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("Insert query failed.", 7);
            }
        }

        // 8) Sort
        ksort($verifiedEmailList);

        $results = _GetInfoForEmailList($deletedEmails, $unverifiedEmails);
        if ($results ["status"] !== "000") {
            throw new exception ($results ["error"], 13);
        }
        extract($results ["data"]);

        $returnArray = array("status" => "000", "error" => "", "verifiedEmailList" => array_values($verifiedEmailList), "info" => $info, "operation" => "update");
    } catch (exception $e) {

        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "operation" => "update");
        $results = ReadVerifiedEmailList($dbh, $Cu);
        $returnArray["verifiedEmailList"] = $results["verifiedEmailList"];
    }

    return $returnArray;
}

/**
 * function _ModifyVerifiedEmailListWithUsedEmails ($usedEmailList, $verifiedEmailList, $deletedEmails)
 * Add "used" flags to the verifiedEmailList from the usedEmailList.
 *
 * @param $usedEmailList -- a map of emails that are used in the CU email list main grid or in the email template.
 * @param $verifiedEmailList -- a list of emails for the kendoGrid.
 * @param $deletedEmails -- a list of emails to be deleted from the database and from AWS.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."verifiedEmailList" -- the verified email list with the "used" column updated.
 */
function _ModifyVerifiedEmailListWithUsedEmails ($usedEmailList, $verifiedEmailList, $deletedEmails) {
    try {
        if (!isset($usedEmailList) || !is_array($usedEmailList) || !isset($verifiedEmailList) || !is_array($verifiedEmailList)
            || !isset($deletedEmails) || !is_array($deletedEmails)) {
            throw new exception ("Inputs are not valid.", 12);
        }

        foreach($usedEmailList as $emailKey => $emailRow) {
            if (!HCU_array_key_exists("email", $emailRow) || !HCU_array_key_exists("used", $emailRow)) {
                throw new exception ("UsedEmailList is not valid.", 13);
            }
            $email = $emailRow["email"];
            $used = $emailRow["used"];
            if (!validateEmail($email)) {
                throw new exception ("One or more emails are not valid.", 14);
            }
            if (HCU_array_key_exists($emailKey, $verifiedEmailList)) {
                $verifiedEmailList[$emailKey]["used"] = $used;
            } else if ($used && HCU_array_key_exists($emailKey, $deletedEmails)) {
                throw new exception ("Cannot delete an email in use.", 11);
            }
        }

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

    return $returnArray;
}

/**
 * function _GetInfoForEmailList($deletedEmails, $unverifiedEmails)
 * Constructs the information to return to the user.  This will tell which emails where added or deleted (if any.)
 *
 * @param $deletedEmails -- a list of emails to be deleted from the database and from AWS.
 * @param $unverifiedEmails -- a list of emails to send verification emails.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."info" -- the full success message.
 */
function _GetInfoForEmailList($deletedEmails, $unverifiedEmails) {
    try {
        // 9) Info
        $info = array();
        $info[] = "Email list saved successfully.";
        if (count ($unverifiedEmails) > 0) {
            $info[] = "&nbsp;";
            $info[] = "Verification emails were sent to:";
            $info = array_merge($info, array_keys($unverifiedEmails));
        }

        if (count($deletedEmails) > 0) {
            $info[] = "&nbsp;";
            $info[] = "The following emails were removed:";
            $info = array_merge($info, array_keys($deletedEmails));
        }

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

/**
 * function _TransformKendoEmailList($emailList)
 * This transforms the input of a kendoGrid into what it looks like after the update.
 *    It also creates multiple variables to send for verification, deletion, and what to save in the database.
 *
 * @param $emailList -- the input from the kendoGrid.  This is a JSON-encoded string.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."allEmails" -- a list of all emails.  This will be sent to the CheckIfEmailIsUsed function to see if it can be deleted.
 * @return "data"."unverifiedEmails" -- a list of new emails.  This will be sent to the python script to send out verification emails.
 * @return "data"."verifiedEmailList" -- a list of emails for the kendoGrid.  This will eventually have the "used" attribute set.
 *    Any "unverifiedEmails" are set to a status of "Pending."
 * @return "data"."databaseEmailList" -- a list of emails for the database.
 *    The only other attribute is "verified" which is true if the email is verified and false otherwise.
 * @return "data"."deletedEmails" -- a list of emails to delete.  These don't show up in the databaseEmailList or the verifiedEmailList.
 *    They will be sent to the python script to delete.
 */
function _TransformKendoEmailList($emailList) {
    $verifiedEmailList = array();
    $databaseEmailList = array();
    $unverifiedEmails = array();
    $deletedEmails = array();
    $allEmails = array();
    $validStatuses = array("Success", "Pending", "Failed", "TemporaryFailure", "NotStarted", "");
    try {
        // 1) Extract and verify emailList.  Build an array for the database and for the grid.
        $emailList = HCU_JsonDecode($emailList);
        if (!is_array($emailList) || count($emailList) == 0) {
            throw new exception ("Email list is not valid.", 1);
        }

        foreach($emailList as $emailRow) {
            if (!HCU_array_key_exists("email", $emailRow) || !HCU_array_key_exists("status", $emailRow)) {
                throw new exception ("Email list is not valid.", 2);
            }
            $email = $emailRow["email"];
            $status = $emailRow["status"];
            $emailKey = strtolower($email);

            if (!in_array($status, $validStatuses)) {
                throw new exception ("Email list is not valid.", 3);
            }

            if (!validateEmail($email)) {
                throw new exception ("Email list is not valid.", 4);
            }

            // At this point, the temporary email should never gotten this far but if it is, filter it out so it doesn't save it.
            if (trim(strtolower($email)) == GetTemporaryEmail()) {
                continue;
            }

            $allEmails[] = prep_save($email);
            if (!HCU_array_key_value("deleted", $emailRow)) {
                if ($status == "") {
                    $unverifiedEmails [$emailKey] = str_replace('"', '\x22', $emailKey);
                    // escapeshellarg() does too much.  It will escape all the quotes in the command.
                    $verifiedEmailList [$emailKey] = array("email" => $emailKey, "status" => "Pending");

                    $date = new DateTime("now", new DateTimeZone("UTC"));
                    $date = $date->format("U");
                    $databaseEmailList [$emailKey] = array("email" => $emailKey, "verified" => false, "date" => intval($date));
                } else if ($status == "Success") {
                    $verifiedEmailList [$emailKey] = array("email" => $emailKey, "status" => $status);
                    $databaseEmailList [$emailKey] = array("email" => $emailKey, "verified" => true);
                } else {
                    $verifiedEmailList [$emailKey] = array("email" => $emailKey, "status" => $status);
                    $databaseEmailList [$emailKey] = array("email" => $emailKey, "verified" => false);
                }
            } else if (!HCU_array_key_value("itsNew", $emailRow)) { // It should be already filtered out on the frontend side.
                $deletedEmails [$emailKey] = str_replace('"', '\x22', $emailKey); // escapeshellarg() does too much.  It will escape all the quotes in the command.
            }
        }

        // 1.5) Now remove any deleted emails that are in the verified ones.  (User deleted and readded email.)
        foreach ($deletedEmails as $email => $value) {
            if (HCU_array_key_exists($email, $verifiedEmailList)) {
                unset($deletedEmails[$email]);
            }
        }

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

/**
 * function ReadVerifiedEmailList($dbh, $Cu)
 * This function will check the SES role for the credit union.
 * If it exists, then it will check the statuses from Amazon for any unverified emails and return a list of emails and statuses.
 * If it doesn't exist, then it will get a list of emails used for CU emails and return those.
 *    (Statuses will be empty because they normally won't be sent to AWS SES at this point.)
 *
 * @param $dbh -- the database connection
 * @param $Cu -- the credit union
 *
 * @return "status" -- "000" if successful, nonzero otherwise.
 * @return "error" -- The error message returned with the status code.
 * @return "verifiedEmailList" -- If the function is successful, this will the emails from the SES role or the emails in usage in CU emails.
 */
function ReadVerifiedEmailList($dbh, $Cu) {
    $verifiedEmailList = array();
    $databaseEmailList = array();
    $updateDatabase = false; // This is needed when an email becomes verified.
    $isNew = false;
    $approvedEmails = array();
    try {

        // 1) Check if SES role exists for CU.
        $sql = "select email from cuadmnotify where cu = '" . prep_save($Cu, 10) . "' and role = 'SES'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception("Email query failed.", 1);
        }

        // 2) If role exists
        if (db_num_rows($sth) > 0) {

            $results = _TransformDatabaseEmailList(db_fetch_assoc($sth, 0)["email"]);
            if ($results ["status"] !== "000") {
                throw new exception ($results ["error"], 13);
            }
            extract($results ["data"]);

            // 2c) Check to see if any of the emails are being used by the manage script.  Set the "used" flag to true.
            $results = CheckIfEmailIsUsed($dbh, $Cu, $allEmails);
            if ($results["status"] !== "000") {
                throw new exception ($results["error"], 9);
            }
            foreach($results["usedEmailList"] as $emailRow) {
                $key = strtolower($emailRow["email"]);
                $verified = $databaseEmailList[$key]["verified"];
                $verifiedEmailList[$key]["used"] = $emailRow["used"] && $verified;
            }

            // 2d) Call python script to get statuses of non-verified emails.
            if (count($unverifiedEmails) > 0) {
                $pyFile = dirname(__FILE__) . "/../../shared/scripts/verifySESEmail.py";
                $pyAction = "getVerification";

                $cmd = "python3 $pyFile -a $pyAction -e " . '"'    . implode(" ", $unverifiedEmails) . '"';
                $response = exec($cmd);

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

                $results = _TransformEmailStatuses($emailStatuses, $verifiedEmailList, $databaseEmailList);
                if ($results ["status"] !== "000") {
                    throw new exception ($results ["error"], 15);
                }
                extract($results ["data"]);
            }

            // 2f) Update the "SES" role if one or more emails became verified.
            if ($updateDatabase) {

                foreach($databaseEmailList as $datum) {
                    if ($datum["verified"]) {
                        $approvedEmails[] = array("email" => $datum["email"]);
                    }
                }
                $sql = "update cuadmnotify set email = '" . prep_save(HCU_JsonEncode(array_values($databaseEmailList))) .
                        "' where cu = '" . prep_save($Cu, 10) . "' and role = 'SES'";
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Update query failed.", 10);
                }
            }

        // 3) If role doesn't exist
        } else {
            $isNew = true;
            // 3a) Retrieve distinct emails from the other roles in the table.
            $results = ReadEmailNotify($dbh, $Cu);
            if ($results["status"] !== "000") {
                throw new exception($results["error"], 7);
            }
            $dataFromNotifies = $results ["data"]["gridData"];

            // 3b) Get emails out of email template for broadcast emails.
            $sql = "select distinct trim(tmpl_email) as email from cuadmemailtmpl where cu = '" . prep_save($Cu, 10) . "'";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("Email template failed.", 12);
            }
            $dataFromTemplate = db_fetch_all($sth);

            $results = _TransformEmailsNoSES($dataFromNotifies, $dataFromTemplate);
            if ($results ["status"] !== "000") {
                throw new exception ($results ["error"], 16);
            }

            extract($results["data"]);
        }

        // Add temporary email to end of list.
        $approvedEmails[] = array("email" => GetTemporaryEmail());

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

    // 4) Sort
    ksort($verifiedEmailList);
    $returnArray ["verifiedEmailList"] = array_values($verifiedEmailList);
    $returnArray ["isNew"] = $isNew;
    $returnArray ["operation"] = "read";

    return $returnArray;
}

/**
 * function _TransformEmailsNoSES($dataFromNotifies, $dataFromTemplate)
 * This function creates a list to send to the kendoGrid when there is no SES role in the database.
 * Data comes from two sources: emails of other roles that are not hidden and email templates.
 *
 * @param $dataFromNotifies -- Emails from the cuadmnotify table that are not hidden.
 * @param $dataFromTemplate -- Emails from the cuadmemailtmpl table.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."verifiedEmailList" -- the data for the kendoGrid.
 */
function _TransformEmailsNoSES($dataFromNotifies, $dataFromTemplate) {
    $verifiedEmailList = array();
    try {
        foreach($dataFromNotifies as $emailRow) {
            // Only the from emails need to be validated.
            if (!$emailRow["single"]) {
                continue;
            }

            $email = trim($emailRow["email"]);
            $emailKey = strtolower($email); // Emails are case insensitive.

            // Do not include any temporary emails.
            if ($emailKey == GetTemporaryEmail()) {
                continue;
            }

            if ($email != "" && !HCU_array_key_exists($emailKey, $verifiedEmailList)) {
                $verifiedEmailList[$emailKey] = array("email" => $emailKey, "status" => "", "used" => true, "isDirty" => true);
            }
        }

        if ($dataFromTemplate) {
            foreach($dataFromTemplate as $row) {
                $email = $row["email"];
                $emailKey = strtolower($email);

                if (!HCU_array_key_exists($emailKey, $verifiedEmailList)) {
                    $verifiedEmailList[$emailKey] = array("email" => $emailKey, "status" => "", "used" => true, "isDirty" => true);
                }
            }
        }

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

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

/**
 * function _TransformEmailStatuses($emailStatuses, $verifiedEmailList, $databaseEmailList)
 * This modifies the kendoGrid data to include the status data and the database data to be "verified": true when the status becomes "Success."
 *
 * @param $emailStatuses -- the email statuses section of AWS call.
 * @param $verifiedEmailList -- the kendoGrid data to modify.
 * @param $databaseEmailList -- the database data to modify.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."verifiedEmailList" -- the data for the kendoGrid.
 * @return "data"."databaseEmailList" -- the data for the database.
 * @return "data"."updateDatabase" -- this is a boolean.  When true, update the database.  The only case is when one of the verified attributes change.
 */
function _TransformEmailStatuses($emailStatuses, $verifiedEmailList, $databaseEmailList) {
    $updateDatabase = false;
    try {
        foreach($emailStatuses as $email => $emailRow) {
            $emailKey = strtolower($email); // Emails are case insensitive.
            $status = HCU_array_key_value("VerificationStatus", $emailRow);

            if ($status !== false) {
                $verifiedEmailList[$emailKey]["status"] = $status;
                $databaseEmailList[$emailKey]["email"] = $emailKey;

                if ($status == "Success") { // If the status is "Success", then update the verified option.  Otherwise, it wouldn't show up as an option later.
                    $databaseEmailList[$emailKey]["verified"] = true;
                    $verifiedEmailList[$emailKey]["resend"] = false; // Do not need to resend if verified.
                    unset($databaseEmailList[$emailKey]["date"]); // Date is no longer important.
                }
                $updateDatabase = true;
            }
        }

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

/**
 * function _TransformDatabaseEmailList($emailList)
 * This gets the kendoGrid data when coming from the database.  (Read.)
 *
 * @param $emailList -- the email list from the SES role in the database.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."allEmails" -- a list of all emails.  This will be sent to the CheckIfEmailIsUsed function to see if it can be deleted.
 * @return "data"."unverifiedEmails" -- a list of new emails.  This will be sent to the python script to send out verification emails.
 * @return "data"."verifiedEmailList" -- a list of emails for the kendoGrid.  This will eventually have the "used" attribute set.
 *    Any "unverifiedEmails" are set to a status of "Pending."
 * @return "data"."databaseEmailList" -- a list of emails for the database.
 *    The only other attribute is "verified" which is true if the email is verified and false otherwise.
 */
function _TransformDatabaseEmailList($emailList) {
    $unverifiedEmails = array();
    $allEmails = array();
    $verifiedEmailList = array();
    $databaseEmailList = array();
    try {
        // 2a) Retrieve emails from role with verified boolean.
        $emailList = HCU_JsonDecode($emailList);

        $utc = new DateTimeZone("UTC");
        $hourAgo = new DateTime("now", $utc);
        $hourAgo->modify('-1 hour');

        // 2b) Create array for grid with emails and statuses.  Set status to "Success" for verified emails.
        foreach($emailList as $emailRow) {
            $email = trim($emailRow["email"]);
            $emailKey = strtolower($email); // Emails are case insensitive.

            if (!HCU_array_key_exists($emailKey, $verifiedEmailList)) {
                if (!validateEmail($emailKey)) {
                    throw new exception ("Email list is not valid.", 8);
                }

                // Should not have gotten the temporary email in the database for the SES role but if that is the case, filter it out.
                if ($emailKey == GetTemporaryEmail()) {
                    continue;
                }

                // Force emails to be lowercase regardless
                $emailRow["email"] = $emailKey;

                $date = HCU_array_key_value("date", $emailRow);
                if ($date === false) {
                    $resend = true;
                } else {
                    $date = DateTime::createFromFormat("U", $date, $utc);
                    $resend = $date < $hourAgo;
                }

                $verifiedEmailList[$emailKey] = array("email" => $emailKey, "status" => "", "used" => false, "resend" => $resend);
                $allEmails[$emailKey] = prep_save($emailKey);
                if ($emailRow["verified"] === true) {
                    $verifiedEmailList[$emailKey]["status"] = "Success";
                    $verifiedEmailList[$emailKey]["resend"] = false; // Do not need to resend if verified.
                } else {
                    $unverifiedEmails[$emailKey] = str_replace('"', '\x22', $emailKey);
                    // escapeshellarg() does too much.  It will escape all the quotes in the command.
                }
                $databaseEmailList[$emailKey] = $emailRow;
            }
        }

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

    return $returnArray;
}

/**
 * function _ValidateAWSStatusReturn($response)
 * This ensures that the AWS call to get statuses comes back correctly and retrieves the email statuses from the response.
 *
 * @param $response -- the response from the python script.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."emailStatuses" -- the email statuses from the response.
 */
function _ValidateAWSStatusReturn($response) {
    try {
        // 2e) Update status for emails that come back.  (Emails that don't come back will stay in the empty not started status.)
        if ($response == "") {
            throw new exception ("Invalid response from script.", 11);
        }
        $response = HCU_JsonDecode($response);
        if (count($response) == 0) {
            throw new exception("Invalid response from script.", 3);
        }
        if (!HCU_array_key_exists("status", $response) || !HCU_array_key_exists("error", $response) || !HCU_array_key_exists("response", $response)) {
            throw new exception("Invalid response from script.", 5);
        }
        if ($response["status"] !== "000") {
            throw new exception($response["error"], 4);
        }

        $emailStatuses = HCU_array_key_value("VerificationAttributes", $response["response"]);
        if ($emailStatuses === false) {
            throw new exception("Invalid response from script.", 6);
        }

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

    return $returnArray;
}

/**
 * function CheckIfEmailIsUsed($dbh, $Cu, $emailList)
 * This function returns if the email is used in any CU emails.  This determines the "used" flag which prohibits a user from removing an email.
 *
 * @param $dbh -- the database connection
 * @param $Cu -- the credit union
 * @param $emailList -- a list of emails to check
 *
 * @return "status" -- "000" if successful, nonzero otherwise.
 * @return "error" -- The error message returned with the status code.
 * @return "usedEmailList" -- A list of emails and if they are used in the CU emails or not.
 */
function CheckIfEmailIsUsed($dbh, $Cu, $emailList) {
    $usedEmailList = array();
    try {
        if (count($emailList) > 0) {
            $results = _GetInvalidRoles(GetEmailNotifyRoles());
            if ($results ["status"] !== "000") {
                throw new exception ($results ["error"], 2);
            }
            extract($results["data"]);

            if (count($invalidRoles) > 0) {
                $sql = "select distinct a.email, b.email is not null or e.tmpl_id is not null as used
                        from (values ('" . implode("'), ('", $emailList) . "')) as a (email)
                        left join (select regexp_split_to_table ( trim ( email ), E'\\\\s*;\\\\s*' ) from cuadmnotify
                        where cu = '" . prep_save($Cu, 10) . "' and role not in ('" . implode("', '", $invalidRoles) . "')) as b (email)
                            on trim(lower(a.email)) = trim(lower(b.email))
                        left join cuadmemailtmpl e on trim(lower(a.email)) = trim(lower(e.tmpl_email)) and e.cu = '" . prep_save($Cu, 10) . "'";

                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Used query failed.", 1);
                }

                $results = _TransformUsedEmailList(db_fetch_all($sth));
                if ($results ["status"] !== "000") {
                    throw new exception ($results ["error"], 2);
                }
                extract($results["data"]);
            }
        }

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

    return $returnArray;
}

/**
 * function _TransformUsedEmailList($usedEmailData)
 * This applies the usedEmailData to the kendoGrid data.
 *
 * @param $usedEmailData -- the data from the CheckIfEmailIsUsed query.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."usedEmailList" -- A map of emails to return.
 */
function _TransformUsedEmailList($usedEmailData) {
    $usedEmailList = array();
    try {
        if ($usedEmailData) {
            foreach($usedEmailData as $row) {
                $email = trim($row["email"]);
                $emailKey = strtolower($email); // Emails are case insensitive.
                $used = trim($row["used"]) == "t";
                $usedEmailList[$emailKey] = array("email" => $emailKey, "used" => $used);
            }
        }

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

/**
 * function _GetInvalidRoles($roles)
 * This gets a list of invalidRoles from all the roles.  Invalid roles are ones with the "hidden" attribute set to true or if is isn't "single".
 *
 * @param $roles -- all the roles.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."invalidRoles" -- a list of invalid roles.
 */
function _GetInvalidRoles($roles) {
    $invalidRoles = array();
    try {
        $invalidRoles = array();
        foreach($roles as $role => $roleRow) {
            if (HCU_array_key_value("hidden", $roleRow) || !HCU_array_key_value("single", $roleRow)) {
                $invalidRoles [] = prep_save($role, 15);
            }
        }

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

/**
 * function ReadEmailNotify($dbh, $Cu)
 *
 * Gets the data for the kendo grid.  This is driven off of the emailNotifyRoles array and uses that order.
 *
 * @param integer $dbh         The database handle
 * @param string $Cu         The Credit Union
 *
 * @return array $returnArray         array("code" => numeric string, "errors" => array, "data" => array)
 * "code" is dependent on what errors there are.
 * "errors" is a list of errors (string)
 * "data" is the data needed for the Kendo Grid.  It looks like [{roleId: string, roleText: string, required: boolean, single: boolean, email: single}]
 */
function ReadEmailNotify($dbh, $Cu) {
    $gridData = array();
    $approvedEmails = array();
    try {

        $roles = GetEmailNotifyRoles();
        $sql = "select role, email from cuadmnotify where cu = '" . prep_save($Cu, 10) . "'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Role query failed.", 1);
        }
        $roleData = db_fetch_all($sth);
        $featureList = FetchMenuFeatureList( array("dbh" => $dbh), array("Cu" => $Cu) );

        $records = array();
        $results = _TransformReadEmailNotify($roles, $roleData, $featureList);
        if ($results ["status"] !== "000") {
            throw new exception ($results ["error"], 2);
        }
        extract($results["data"]);

        usort($records, "SortEmailNotify");

        $results = RetrieveApprovedEmails($dbh, $Cu);
        if ($results["status"] !== "000") {
            throw new exception ("Verified query failed.", 3);
        }
        $approvedEmails = $results["approvedEmails"];

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

/**
 * function RetrieveApprovedEmails($dbh, $Cu)
 * Retrieve the approved emails from the database.
 *
 * @param $dbh -- the database connection
 * @param $Cu -- the credit union
 *
 * @return $status -- "000" if successful, nonzero otherwise
 * @return $error -- "" if successful, nonempty otherwise
 * @return $approvedEmails -- A list of approved emails
 */
function RetrieveApprovedEmails($dbh, $Cu) {
    try {
        $sql = "select trim(r.email) as email from json_to_recordset((select a.email from cuadmnotify a where a.role = 'SES'
                and a.cu = '" . prep_save($Cu, 10) . "')::json)
                as r (email varchar, verified boolean) where r.verified = true order by 1";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Verified query failed.", 3);
        }
        $approvedEmails = db_fetch_all($sth);
        $approvedEmails = $approvedEmails === false ? array() : $approvedEmails;

        // Add temporary email to end of list.
        $approvedEmails[] = array("email" => GetTemporaryEmail());

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

/**
 * function SortEmailNotify($a, $b)
 * Helper function for usort.  Sorts the single address emails before the other ones so the CU notification grid shows before the other.
 *
 * @param $a -- the first item
 * @param $b -- the second item
 * @return 1 if the first item is greater than the second, 0 if they are equal (same order), -1 if the first item is less than the second.
 */
function SortEmailNotify($a, $b) {
    $cmp = $a["single"] ? ($b["single"] ? 0 : -1) : ($b["single"] ? 1 : 0);
    return $cmp;
}

/**
 * function _TransformReadEmailNotify($roles, $roleData, $featureList)
 * Translates the data from the query into what the kendoGrid needs.
 *
 * @param $roles -- all the roles.
 * @param $roleData -- the data from the cuadmnotify table for the CU.
 * @param $featureList -- the feature list of the CU.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."records" -- the records for the kendoGrid.
 */
function _TransformReadEmailNotify($roles, $roleData, $featureList) {
    $emailMap = array();
    $records = array();
    try {
        // the new email notifications are for commercial use only so do not show ACH Notifications role.
        // use the feature menu to determine this fact.
        $featureMenuHasCommercial = false;
        if (in_array("ACHPMT", $featureList['data']) || in_array("ACHCOL", $featureList['data'])) {
            $featureMenuHasCommercial = true;
        }

        foreach($roleData as $row) {
            $emailMap[trim($row["role"])] = trim($row["email"]);
        }

        foreach($roles as $roleKey => $roleArray) {
            // if no commercial features are found for the credit union menu, skip the entry
            if (!$featureMenuHasCommercial && $roleKey == "achnotify") {
                continue;
            }

            if (isset($roleArray["hidden"]) && $roleArray["hidden"]) {
                continue;
            }
            $email = HCU_array_key_value($roleKey, $emailMap);
            $email = $email === false ? "" : trim($email);
            $clear = HCU_array_key_value("clear", $roleArray);
            $single = HCU_array_key_value("single", $roleArray);
            $record = array("roleId" => $roleKey, "roleText" => $roleArray["desc"], "required" => !$clear && $email != "", "single" => $single, "email" => $email);
            $records[] = $record;
        }

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


/**
 * function GetEmailNotifyRoles()
 *
 * @return array $returnArray   array(role => array("desc" => string, "single" => boolean, "clear" => boolean, "hidden" => boolean))
 * "desc" will be what shows up in the description in the grid.
 * "single", "clear", and "hidden" do not have to be set.  If they aren't, then they are false.
 * "single" specifies that there can only be one email address.  If there are multiple, then an error is shown.
 * "clear" specifies that the email address can be removed.  Normally email addresses cannot be removed but they can be changed.
 * "hidden" specifies that the role doesn't appear on the page.
 */
function GetEmailNotifyRoles() {

  return [
    "upload"          => ["desc" => "File Upload Confirmations"],
    "alert"           => ["desc" => "Alerts From / Returned To",                "single" => true],
    "esload"          => ["desc" => "eStatement Upload"],
    "agree"           => ["desc" => "eStatement Signups / Stops"],
    "preset"          => ["desc" => "Password Reset CU Copy To"],
    "presetfrom"      => ["desc" => "Password Reset Mailed From",               "single" => true],
    "transfernotify"  => ["desc" => "Transfers / Repeating Txn Failures"],
    // 06-19 note that "onldepnotify" does not refer to admin email notifications for online
    // deposits. This key is only used for loan app submission notifications.
    "onldepnotify"    => ["desc" => "Loan System Notifications"],
    "securenotify"    => ["desc" => "Secure Message Notification",              "clear" => true],
    "securemsgfrom"   => ["desc" => "Secure Message Notification Mailed From",  "single"  => true],
    "securitychgfrom" => ["desc" => "Security Settings Changed Mailed From",    "single"  => true],
    "enrollnotify"    => ["desc" => "Enrollment Form Notification",             "clear"   => true, "delete" => true],
    "txtbanking"      => ["desc" => "TXT Banking Email",                        "clear"   => true, "hidden" => true],
    "twtralert"       => ["desc" => "Twitter Alert Account",                    "clear"   => true, "hidden" => true],
    "achnotify"       => ["desc" => "ACH Notifications",                        "single"  => true],
    "hcunotice"       => ["desc" => "HomeCU Special Notifications<br><font size=1>(Planned outages, special updates, etc.)</font>"],
    "ccemail"         => ["desc" => "Credit Card Payment Request",              "hidden"  => true],
     // This role is for verifying emails.
    "SES"             => ["hidden" => true]
  ];
}


/**
 * function UpdateEmailNotify($dbh, $Cu, $showSQL, $role, $email)
 *
 * Update/insert a record for the kendo grid (depending on if it exists).
 *
 * @param integer $dbh     The database handle
 * @param string $Cu     The Credit Union
 * @param boolean $showSQL   If true, then SQL is returned
 * @param string $role     The role to update/insert
 * @param string $email   The email value
 * @param boolean $specialEmailUpdate  If true, email is being updated through the welcome screen so use a different audit
 *
 * @return array $returnArray     array("code" => numeric string, "errors" => array, "data" => array)
 * "code" is dependent on what errors there are.
 * "errors" is a list of errors (string)
 * "data" is the data needed for the Kendo Grid.  It looks like [{roleId: string, roleText: string, required: boolean, single: boolean, email: single}]
 */
function UpdateEmailNotify($dbh, $Cu, $Cn, $role, $email, $specialEmailUpdate) {
  $roles = GetEmailNotifyRoles();

  try {
    $results = _TransformRoleForUpdate($role, $roles, $email);
    if ($results ["status"] !== "000") {
        throw new exception ($results ["error"], 9);
    }
    extract($results["data"]);

    $sql = "select 'FOUND' from cuadmnotify where cu = '" . prep_save($Cu, 10) . "' and role = '" . prep_save($role, 15) . "'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("notify find query failed.", 6);
    }
    $recordExists = db_num_rows($sth) > 0;

    $sql = "select email from cuadminusers where user_name = '$Cn' and cu = '$Cu'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("Email query failed.", 7);
    }
    $userEmail = db_fetch_row($sth)[0];

    $results = _CreateUpdateTableForUpdateAudit($Cu, $role, $returnRecord["email"], $recordExists, $userEmail, $specialEmailUpdate);
    if ($results ["status"] !== "000") {
        throw new exception ($results ["error"], 10);
    }
    extract($results["data"]);

    if (DataAdminTableUpdate($dbh, array("cu" => $Cu), $updateTable, $Cn, $shortLabel, "notificationMaintenance.prg", "A",
        $longLabel, $Cn, $userEmail, trim($_SERVER["REMOTE_ADDR"])) === false) {
          throw new exception("Update failed.", 8);
    }

    $returnArray = array("status" => "000", "error" => "", "record" => array($returnRecord), "info" => "Email has been updated successfully", "action" => "update");
  } catch(exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "action" => "update");
  }
  return $returnArray;
}

/**
 * function _TransformRoleForUpdate($role, $roles, $email)
 * Validates and gets a record ready for the kendoGrid.
 *
 * @param $role -- the role to update.
 * @param $roles -- the list of roles.
 * @param $email -- the email to update.
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."returnRecord" -- the updated record.
 */
function _TransformRoleForUpdate($role, $roles, $email) {
    $numEmails = 0;
    try {
        $roleRecord = HCU_array_key_value($role, $roles);
        if ($roleRecord === false) {
          throw new exception("role record not found.", 1);
        }

        if (trim($email) == "" && !(isset($roleRecord["clear"]) && $roleRecord["clear"])) {
          throw new exception("Email cannot be cleared.", 2);
        }

        $emails = array();

        foreach (preg_split('/[;, ]/',$email) as $ema) {
          $ema = trim($ema);
          if ($ema != "") {
            $numEmails++;
            if (!validateEmail($ema)) {
              throw new exception("One of the emails is invalid.", 3);
            }

            $emails[] = strtolower(trim($ema));
          }
        }

        // Want to do a little bit of cleanup.  Yes, "a@bcd.efg; ;, h@ijk.lmn;" is legal but set it to "a@bcd.efg;h@ijk.lmn".
        $email = implode(";", $emails);

        if ($numEmails == 0) {
          throw new exception("Email cannot be cleared.", 4);
        }

        if ($numEmails > 1 && isset($roleRecord["single"]) && $roleRecord["single"]) {
          throw new exception("Only one address is allowed.", 5);
        }

        $returnRecord = array("roleId" => $role, "roleText" => $roleRecord["desc"], "email" => $email,
            "required" => trim($email) != "" && !(isset($roleRecord["clear"]) && $roleRecord["clear"]), "recordExists" => "Y");

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

/**
 * function _CreateUpdateTableForUpdateAudit($Cu, $role, $email, $recordExists, $userEmail)
 * This creates the audit for updating an email.
 *
 * @param $Cu -- the credit union
 * @param $role -- the role to update
 * @param $email -- the email to update
 * @param $recordExists -- does the record exist? (Create versus update audit)
 * @param $userEmail -- the email of the logged in user
 * @param $specialEmailUpdate -- if the email was updated through the welcome screen or not
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "data"."updateTable" -- what to send to the audit function to make the change and audit it.
 */
function _CreateUpdateTableForUpdateAudit($Cu, $role, $email, $recordExists, $userEmail, $specialEmailUpdate) {
    $shortLabel = "";
    $longLabel = "";
    try {

        $updateTable = array("cu" => $Cu, "role" => $role, "email" => $email);
        if ($recordExists) {
          $updateTable["_action"] = "update";

          if ($specialEmailUpdate) {
            $shortLabel = "UPD_SNOTI";
            $longLabel = "Update Special Email Notification";
          } else {
            $shortLabel = "UPD_NOTI";
            $longLabel = "Update Notification Emails";
          }
        } else {
          $updateTable["_action"] = "insert";

          if ($specialEmailUpdate) {
            $shortLabel = "ADD_SNOTI";
            $longLabel = "Add Special Email Notification";
          } else {
            $shortLabel = "ADD_NOTI";
            $longLabel = "Add Notification Emails";
          }
        }

        $updateTable = array("cuadmnotify" => array($updateTable));

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

/**
 * function ResendEmail($dbh, $Cu, $email)
 * Resends one email that was sent more than 24 hours ago.
 *
 * @param $dbh -- the database connection
 * @param $Cu -- the credit union
 * @param $email -- the email to resend
 *
 * @return "status" -- "000" if successful, nonzero if there is an error.
 * @return "error" -- "" if successful, nonempty if there is an error.
 * @return "email" -- the email that was resent.  This is to update the manage email list grid.
 * @return "info" -- the information message to send.
 */
function ResendEmail($dbh, $Cu, $email) {
    try {

        // Step 1: Validate email.
        if (!validateEmail($email)) {
          throw new exception("Email is invalid.", 1);
        }

        $email = trim(strtolower($email));

        $encoded = trim(str_replace('"', '\x22', $email));
        // escapeshellarg() does too much.  It will escape all the quotes in the command.

        // Step 2: Call python script to resend email.
        $pyFile = dirname(__FILE__) . "/../../shared/scripts/verifySESEmail.py";
        $pyAction = "verifyEmails";
        $cmd = "python3 $pyFile -a $pyAction -e " . '"' . $encoded . '"';
        $response = HCU_JsonDecode(exec($cmd));
        if ($response["status"] !== "000") {
            throw new exception ($response["error"], 2);
        }

        // Step 3: Get SES role from database.
        $sql = "select email from cuadmnotify where cu = '" . prep_save($Cu, 10) . "' and role = 'SES'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 3);
        }

        $emailArray = db_num_rows($sth) > 0 ? db_fetch_row($sth, 0)[0] : "[]";
        $emailArray = HCU_JsonDecode($emailArray);

        // Step 4: Update SES role with "date" of now for the resent email.
        foreach($emailArray as $i => $emailRow) {
            $checkEmail = HCU_array_key_value("email", $emailRow);
            $checkEmail = $checkEmail === false ? "" : trim(strtolower($checkEmail));

            $newEmailRow = $emailRow;
            $newEmailRow["email"] = $checkEmail;
            if ($checkEmail == $email) {
                $date = new DateTime("now", new DateTimeZone("UTC"));
                $date = $date->format("U");
                $newEmailRow["date"] = intval($date);
            }

            $emailArray[$i] = $newEmailRow;
        }

        $emailArray = HCU_JsonEncode($emailArray);

        $sql = "update cuadmnotify set email = '" . prep_save($emailArray) . "'
                where cu = '" . prep_save($Cu, 10) . "' and role = 'SES'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Update query failed.", 4);
        }

        $returnArray = array("status" => "000", "error" => "", "email" => $email, "info" => "Email was resent to $email.");
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}