aSendEmail.data

<?php
require_once(dirname(__FILE__) . "/cu_notify.data"); // Need the call to read from Amazon.

/**
 * function GetSelectArray()
 * @return the valid keys for things that can be selected.
 */
function GetSelectArray() {
    // #3044: billPay option is no longer available
    // If necessary, we will need to use perms on profile
    // --------------------
    return [
        "all",
        "withE",
        //"withBP",
        "withoutE",
        "withText"
    ];
}

/**
 * function GetTagList()
 * @return list of HTML tags that TinyMCE allows.  All HTML tags are stripped out of the email body that don't relate to this.
 */
function GetTagList() {
    return "<blockquote><p><a><strong><em><span><img><ul><li><ol>";
}

/**
 * function RemoveTemplate($dbh, $Cu, $Cn, $templateId)
 * Removes the email template
 *
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union
 * @param string $Cn -- the logged in user
 * @param number $templateId -- the id of the template to remove
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- empty string if successful, nonempty otherwise
 * @return "data"."templateDDL" -- a list of data for the template dropdownlist
 * @return "info" -- message to the user when successful
 * @return "operation" -- the operation to distinguish afterwards
 */
function RemoveTemplate($dbh, $Cu, $Cn, $templateId) {
    try {
        if ($templateId == 0) {
            throw new exception("Template Id is required.", 1);
        }
        $updateTable = array("_action" => "delete", "tmpl_id" => $templateId);
        $updateTable = array("cuadmemailtmpl" => array($updateTable));

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

        if (DataAdminTableUpdate($dbh, array("cu" => $Cu), $updateTable, $Cn, "RM_ETEMPL", "broadcastEmails.prg", "A", "Email Template Deleted", $Cn, $email,
            trim($_SERVER["REMOTE_ADDR"])) === false) {
            throw new exception("Update failed.", 3);
        }

        $results = ReadTemplate($dbh, $Cu);
        if ($results ["status"] !== "000") {
            throw new exception ("Read Template failed.", 4);
        }
        $templateDDL = $results["data"]["templateDDL"];

        $returnArray = array("status" => "000", "error" => "", "data" => array("templateDDL" => $templateDDL), "info" => "Template was deleted successfully.", "operation" => "removeTemplate");
    } catch(exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "data" => array("templateDDL" => array()), "operation" => "removeTemplate");
    }
    return $returnArray;
}

/**
 * function SaveTemplate($dbh, $Cu, $Cn, $testAddress, $body, $fromEmail, $fromName, $subject, $optin, $select, $templateName, $templateId)
 * Saves the email template (or creates one if the template is set to "(none)")
 *
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union
 * @param string $Cn -- the logged in user
 * @param string $testAddress -- the test address to send preview to
 * @param string $body -- the body of the email
 * @param string $fromEmail -- the email to send email from
 * @param string $fromName -- the name of the from email
 * @param string $subject -- the subject of the email
 * @param string $optin -- does the user have to opt in to see the email?
 * @param string $select -- the value of the select option (all members, members with E-statements, etc.)
 * @param string $templateName -- the name of the template
 * @param number $templateId -- the id of the template.  If it is zero, then create a template.
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- empty string if successful, nonempty otherwise
 * @return "data"."templateDDL" -- a list of data for the template dropdownlist
 * @return "data"."templateId" -- set the template dropdownlist to this template
 * @return "info" -- message to the user when successful
 * @return "operation" -- the operation to distinguish afterwards
 */
function SaveTemplate($dbh, $Cu, $Cn, $testAddress, $body, $fromEmail, $fromName, $subject, $optin, $select, $templateName, $templateId) {
    try {
        $selectArray = GetSelectArray();
        $tagList = GetTagList();
        $results = _ValidateTemplate ($testAddress, $fromEmail, $subject, $select, $templateName, $selectArray, $optin);
        if ($results ["status"] !== "000") {
            throw new exception ($results ["error"], 18);
        }

        $fromEmail = strtolower(trim($fromEmail));

        $body = strip_tags($body, $tagList); // Remove tags that aren't "approved."

        // Saved as a small int so save the index of the array.
        $intSelect = array_flip($selectArray);
        $intSelect = $intSelect[$select];
        $updateTable = array("cu" => $Cu,
            "tmpl_email" => $fromEmail,
            "tmpl_email_name" => $fromName,
            "tmpl_subject" => $subject,
            "tmpl_message" => $body,
            "tmpl_opt_mailto" => $optin == "Y" ? 1 : 0,
            "tmpl_opt_mbr" => $intSelect,
            "tmpl_lastmodified" => DBTIMESTAMP_USENOW,
            "tmpl_name" => $templateName);

        if ($templateId == 0) {
            $sql = "select nextval(pg_get_serial_sequence('cuadmemailtmpl', 'tmpl_id'))";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("nextval query failed.", 16);
            }
            $row = db_fetch_row($sth, 0);
            $templateId = intval($row[0]);
            $updateTable["tmpl_id"] = $templateId;
            $updateTable["_action"] = "insert";
            $shortAction = "ADD_ETEMPL";
            $longAction = "Add Email Template";
        } else {
            $updateTable["_action"] = "update";
            $shortAction = "UPD_ETEMPL";
            $updateTable["tmpl_id"] = $templateId;
            $longAction = "Update Email Template";
        }

        $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.", 14);
        }

        $email = trim(db_fetch_row($sth, 0)[0]);

        $updateTable = array("cuadmemailtmpl" => array($updateTable));
        if (DataAdminTableUpdate($dbh, array("cu" => $Cu), $updateTable, $Cn, $shortAction, "broadcastEmails.prg", "A", $longAction, $Cn, $email,
            trim($_SERVER["REMOTE_ADDR"])) === false) {
            throw new exception("Update failed.", 1);
        }

        $results = ReadTemplate($dbh, $Cu);
        if ($results ["status"] !== "000") {
            throw new exception ("Read Template failed.", 17);
        }
        $templateDDL = $results ["data"] ["templateDDL"];

        $returnArray = array("status" => "000", "error" => "", "data" => array("templateDDL" => $templateDDL, "templateId" => $templateId), "info" => "Template was saved successfully.",
                             "operation" => "saveTemplate");
    } catch(exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "data" => array("templateDDL" => array(), "templateId" => 0), "operation" => "saveTemplate");
    }
    return $returnArray;
}

/**
 * function _ValidateTemplate ($testAddress, $fromEmail, $subject, $select, $templateName, $selectArray, $optin)
 * Ensures that the input is valid.
 *
 * @param $testAddress -- the address to send to when previewing.
 * @param $fromEmail -- the address to show that the email was sent from.
 * @param $subject -- the subject of the email.
 * @param $select -- the value of the select dropdownlist.  It has to be in a predefined list.
 * @param $templateName -- the name to save the template as.  (For send preview, this is not validated.)
 * @param $selectArray -- the array to ensure that the select option fits.
 * @param $optin -- {0 or 1} If one, the user must be opted in to get the email.
 *
 * @return "status" -- "000" if successful, nonzero otherwise.
 * @return "error" -- empty string if successful, nonempty otherwise.
 */
function _ValidateTemplate ($testAddress, $fromEmail, $subject, $select, $templateName, $selectArray, $optin) {
    try {
        if ($fromEmail == "") {
            throw new exception ("From email is required.", 12);
        }

        if (!validateEmail($fromEmail)) {
            throw new exception("From address is not valid.", 7);
        }
        if ($subject == "") {
            throw new exception ("Subject is required.", 3);
        }
        if ($templateName == "") {
            throw new exception ("Name is required.", 15);
        }

        if (!isset($selectArray) || !is_array($selectArray)) {
            throw new exception ("Select array is not valid.", 2);
        }

        if (!in_array($select, $selectArray)) {
            throw new exception ("Select is invalid.", 5);
        }

        if ($testAddress != "" && !validateEmail($testAddress)) {
            throw new exception("Test address is not valid.", 1);
        }

        if (!in_array($optin, array("N","Y"))) {
            throw new exception ("Optin is not valid.", 4);
        }

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

/**
 * function SendEmail($dbh, $Cu, $logger, $encoded)
 * Sends emails to a predetermined list.  Updates the lastused column.
 *
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union
 * @param class $logger -- used to log which email addresses are sent to
 * @param string $encoded -- the encoded version of the SQL to run (from preparing the email.)
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- empty string if successful, nonempty otherwise
 * @return "info" -- message to the user when successful
 * @return "operation" -- the operation to distinguish afterwards
 */
function SendEmail($dbh, $Cu, $logger, $encoded) {
    $tagList = GetTagList();
    try {
        if ($encoded == "") {
            throw new exception("Encoded is required.", 2);
        }
        $encoded = HCU_PayloadDecode($Cu, $encoded);

        if (!is_array($encoded)) {
            throw new exception("Encoded is not encoded correctly.", 3);
        }

        if (!HCU_array_key_exists("realSQL", $encoded) || !HCU_array_key_exists("body", $encoded) || !HCU_array_key_exists("fromName", $encoded) ||
            !HCU_array_key_exists("templateId", $encoded) || !HCU_array_key_exists("templateId", $encoded) || !HCU_array_key_exists("subject", $encoded) ||
            !HCU_array_key_exists("fromEmail", $encoded)) {
            throw new exception("Encoded is not encoded correctly.", 5);
        }
        extract($encoded);

        $body = strip_tags($body, $tagList); // Remove tags that aren't "approved."  This is done on the prepare but do it again just to be sure.

        if ($templateId != 0) {
            $sql = "update cuadmemailtmpl set tmpl_lastused = now() where tmpl_id = $templateId and cu = '$Cu'";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception("Update query failed.", 1);
            }
        }

        $sth = db_query($realSQL, $dbh);
        if (!$sth) {
            throw new exception("Email read query failed.", 4);
        }

        $notify = new ErrorMail;
        $notify->mailfrom = $fromEmail;
        $notify->mailfromname = $fromName;
        $notify->subject = $subject;
        $notify->htmlMsgbody = $body;
        $notify->bulk = true;
        $notify->executeQuietly = true;
        $notify->callingfunction = __FUNCTION__;
        $notify->file = __FILE__;
        $notify->cu = $Cu;

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

        foreach($results as $result) {
            $emails[] = $result["email"];
        }

        $notify->mailto = $emails;
        $notify->hideto = true;
        $notify->SendMail();

        $returnArray = array("status" => "000", "error" => "", "info" => "Emails were sent successfully.", "operation" => "sendEmail");
    } catch(exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "operation" => "sendEmail");
    }
    return $returnArray;
}

/**
 * function ReadSetup($dbh, $Cu)
 * reads in all the templates for the particular CU
 *
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- empty string if successful, nonempty otherwise
 * @return "data"."templateDDL" -- a list of data for the template dropdownlist
 * @return "data"."emailList" -- a list of data for the fromEmail dropdownlist
 * @return "data"."hasUnverified" -- if true, then we need to get the statuses from Amazon
 * @return "operation" -- the operation to distinguish afterwards
 */
function ReadSetup($dbh, $Cu) {
    $templateDDL = array();
    $emailList = array();
    try {
        $results = ReadTemplate($dbh, $Cu);
        if ($results ["status"] !== "000") {
            throw new exception ("Read Template failed.", 1);
        }
        $templateDDL = $results["data"]["templateDDL"];

        $sql = "select lower(trim(r.email)) as email, r.verified 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) order by 1";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Verified query failed.", 2);
        }
        $emailList = db_fetch_all($sth);
        $emailList = $emailList === false ? array() : $emailList;

        $hasUnverified = false;

        $verifiedList = array();
        foreach($emailList as $emailRow) {
            if ($emailRow["verified"] === "t") {
                $verifiedList[] = array("email" => $emailRow["email"]);
            } else if (!$hasUnverified) {
                $hasUnverified = true;
            }
        }

        $returnArray = array("status" => "000", "error" => "", "data" => array("templateDDL" => $templateDDL, "emailList" => $verifiedList, "hasUnverified" =>
                                                                               $hasUnverified), "operation" => "readSetup");
    } catch(exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "data" => array("templateDDL" => array(), "emailList" => array(),
                                                                                                     "hasUnverified" => false), "operation" => "readSetup");
    }
    return $returnArray;
}

/**
 * function ReadTemplate($dbh, $Cu)
 * Reads the templates from the template table.
 *
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- empty string if successful, nonempty otherwise
 * @return "data"."templateDDL" -- a list of data for the template dropdownlist
 * @return "operation" -- the operation to distinguish afterwards
 */
function ReadTemplate($dbh, $Cu) {
    try {
        $selectArray = GetSelectArray();
        $sql = "select tmpl_id as value, tmpl_name as text, lower(trim(tmpl_email)) as fromemail, tmpl_email_name as fromname, tmpl_subject as subject,
        tmpl_message as body,
        case when tmpl_opt_mailto = 1 then 'Y' else 'N' end as optin, tmpl_opt_mbr as select from cuadmemailtmpl where cu = '$Cu' order by tmpl_name";

        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception("Select query failed.", 1);
        }
        $templateDDL = db_fetch_all($sth);
        $templateDDL = $templateDDL === false ? array() : $templateDDL;

        foreach($templateDDL as $i => $row) {
            $templateDDL[$i]["select"] = HCU_array_key_value($row["select"], $selectArray);
        }

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

/**
 * function PrepareEmail($dbh, $Cu, $testAddress, $body, $fromEmail, $fromName, $subject, $optin, $select, $templateId)
 * Prepares the email for sending.  It creates the full with the email contents and list.  If there is a test email, then it sends to that email.
 *
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union
 * @param string $testAddress -- the test address to send preview to
 * @param string $body -- the body of the email
 * @param string $fromEmail -- the email to send email from
 * @param string $fromName -- the name of the from email
 * @param string $subject -- the subject of the email
 * @param string $optin -- does the user have to opt in to see the email?
 * @param string $select -- the value of the select option (all members, members with E-statements, etc.)
 * @param number $templateId -- the id of the template.  If it is zero, then create a template.
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- empty string if successful, nonempty otherwise
 * @return "data"."numEmails" -- returns the value from the count version of the query
 * @return "data"."testSent" -- returns a true/false value on if the test email was sent
 * @return "data"."encoded" -- returns a encoded version of the read SQL.  This will be extracted and sent later.
 * @return "operation" -- the operation to distinguish afterwards
 *
 * @todo What do I do about the plain email option?
 */
function PrepareEmail($dbh, $Cu, $testAddress, $body, $fromEmail, $fromName, $subject, $optin, $select, $templateId) {
    $numEmails = 0;
    $testSent = false;
    $selectArray = GetSelectArray();
    $tagList = GetTagList();

    try {
        $results = _ValidateTemplate ($testAddress, $fromEmail, $subject, $select, "unused", $selectArray, $optin);
        if ($results ["status"] !== "000") {
            throw new exception ($results ["error"], 10);
        }

        $fromEmail = strtolower(trim($fromEmail));

        $emailFlag = GetMsgTxValue('MSGTX_FORCE_EM');
        $askBillPay = GetUserFlagsValue('MEM_ASKBPAY');
        $results = _ConstructSQL($Cu, $optin, $select, $emailFlag, $askBillPay);
        if ($results ["status"] !== "000") {
            throw new exception ("SQL failed.", 9);
        }
        extract($results["data"]);

        $countSQL = "select count(distinct lower(trim(u.email))) $baseSQL";
        $realSQL = "select distinct lower(trim(u.email)) as email $baseSQL";

        $body = strip_tags($body, $tagList); // Remove tags that aren't "approved"

        $sth = db_query($countSQL, $dbh);
        if (!$sth) {
            throw new exception("Query failed.", 6);
        }
        $numEmails = intval(db_fetch_row($sth, 0)[0]);

        if ($testAddress != "") {
            // Send email
            $notify = new ErrorMail;
            $notify->mailto = $testAddress;
            $notify->mailfrom = $fromEmail;
            $notify->mailfromname = $fromName;
            $notify->subject = $subject;
            $notify->htmlMsgbody = $body;
            $notify->cu = $Cu;
            $notify->file = __FILE__;
            $notify->callingfunction = __FUNCTION__;
            $notify->SendMail();

            $testSent = true;
        }

        $encoded = HCU_PayloadEncode($Cu, array("realSQL" => $realSQL, "body" => $body, "fromEmail" => $fromEmail, "templateId" => $templateId,
                                                "subject" => $subject, "fromName" => $fromName));

        $returnArray = array("status" => "000", "error" => "", "data" => array("numEmails" => $numEmails, "testSent" => $testSent, "encoded" => $encoded),
                             "operation" => "prepareEmail");
    } catch(exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => array($e->getMessage()), "data" => array("numEmails" => 0, "testSent" => false, "encoded" => ""),
                             "operation" => "prepareEmail");
    }
    return $returnArray;
}

/**
 * function _ConstructSQL($Cu, $optin, $select, $emailFlag, $askBillPay)
 * Creates the SQL according to the parameters.
 *
 * @param $Cu -- the credit union
 * @param $optin -- {0,1} should a user have to opted in to receive the email?
 * @param $select -- the value of the select option (all members, members with E-statements, etc.)
 * @param $emailFlag -- the value of the email flag.  (Defined in cu_flagconst.i)
 * @param $askBillPay -- the value of the bill pay flag.  (Defined in cu_flagconst.i)
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- empty string if successful, nonempty otherwise
 * @return "data"."baseSQL" -- the results of the SQL
 */
function _ConstructSQL($Cu, $optin, $select, $emailFlag, $askBillPay) {
    try {
        $sql = "from ${Cu}user u ";
        $where = array("coalesce(trim(u.email), '') <> ''", "(coalesce(u.msg_tx, 0) & $emailFlag) = 0"); // has email and is not flagged as a bad email.

        switch($select) {
            case "withE":
                $sql .= " inner join (select user_id, accountnumber, row_number() over (partition by user_id, accountnumber) as rown
                          from ${Cu}useraccounts) uc on u.user_id = uc.user_id and uc.rown = 1";
                $where[] = "exists (select 'FOUND' from ${Cu}memberacct ma where uc.accountnumber = ma.accountnumber and estmnt_flag = 'Y')";
                break;
            case "withoutE":
                $sql .= " inner join (select user_id, accountnumber, row_number() over (partition by user_id, accountnumber) as rown
                          from ${Cu}useraccounts) uc on u.user_id = uc.user_id and uc.rown = 1";
                $where[] = "not exists (select 'FOUND' from ${Cu}memberacct ma where uc.accountnumber = ma.accountnumber and estmnt_flag = 'Y')";
                break;
            // #3044: BillPay flag is only used here
            // If necessary, we will need to use perms on profile
            // --------------------
            // case "withBP":
            //     $where[] = "(u.userflags & $askBillPay) <> 0";
            //     break;
            case "withText":
                $sql .= " inner join (select user_id, accountnumber, row_number() over (partition by user_id, accountnumber) as rown
                          from ${Cu}useraccounts) uc on u.user_id = uc.user_id and uc.rown = 1";
                $where[] = "exists (select 'FOUND' from cusms s where u.user_id = s.user_id and uc.accountnumber = s.accountnumber
                            and coalesce(trim(s.cellnumber::text), '') <> '' and s.cu = '${Cu}')";
                break;
            case "all":
                break;
            default:
                throw new exception("Option is not valid.", 6);
        }

        if ($optin == 1) {
            $where[] = "u.egenl_flag = 'Y'";
        }

        if (count($where) > 0) {
            $sql .= " where " . implode(" and ", $where);
        }

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

/**
 * function UpdateEmailList($dbh, $Cu)
 * Iff there are unverified emails in the CU's SES role, then do a separate call to potientally update list from Amazon.
 *
 * @param $dbh -- the database connection
 * @param $Cu -- the credit union
 *
 * @return "status" -- "000" if successful, nonzero otherwise
 * @return "error" -- empty string if successful, nonempty otherwise
 * @return "data"."emailList" -- a list of verified emails after calling Amazon
 * @return "operation" -- the operation to distinguish afterwards
 */
function UpdateEmailList($dbh, $Cu) {
    try {
        $results = ReadVerifiedEmailList($dbh, $Cu);
        if ($results ["status"] !== "000") {
            throw new exception ("ReadVerifiedEmailList failed.", 1);
        }

        $emailList = array();
        foreach($results ["verifiedEmailList"] as $emailRow) {
            if ($emailRow["status"] === "Success") {
                $emailList[] = array("email" => strtolower(trim($emailRow["email"])));
            }
        }

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