AppAdm.data

<?php

require_once(__DIR__ . '/../library/DateConvert.i');

/**************************
LITERALS
***************************/

/**
 * function GetSearchInData()
 * Gets the list for the search in dropdownlist.
 * @return array
 */
function GetSearchInData() {
    return array(
                array("value" => "", "text" => "Select..."),
                array("value" => "email", "text" => "Email Address"),
                array("value" => "dob", "text" => "Date Of Birth"),
                array("value" => "ssn", "text" => "Social Security #"),
                array("value" => "name", "text" => "Last Name"),
                array("value" => "account", "text" => "Account #"));
}

/**
 * function GetApplicantSearchInData()
 * Gets the options for the search in dropdownlist in the applicant tab.
 * @return array
 */
function GetApplicantSearchInData() {
    return array(
        array("value" => "email", "text" => "Email"),
        array("value" => "account", "text" => "Account #"),
        array("value" => "username", "text" => "Username")
    );
}

/**
 * function GetWithStatusData()
 * Gets the list for the status dropdownlist.
 * @return array
 */
function GetWithStatusData() {
    return array(
                array("value" => "", "text" => "All"),
                array("value" => "NoStat", "text" => "Submitted"),
                array("value" => "SubErr", "text" => "Submitted w/Errors"),
                array("value" => "Approved", "text" => "Approved"),
                array("value" => "Rejected", "text" => "Rejected"),
                array("value" => "Pending", "text" => "Pending"),
                array("value" => "ReqReview", "text" => "Requires Review"));
}

/**
 * function GetRangeTypeData()
 * Gets the list for the range type dropdownlist.
 * @return array
 */
function GetRangeTypeData() {
    return array(
                array("value" => "", "text" => "ALL Applications"),
                array("value" => "submit", "text" => "Applications Submitted between"),
                array("value" => "start", "text" => "Applications Started between"),
                array("value" => "modified", "text" => "Applications Last Modified between"));
}

/**
 * function GetSortByData()
 * Gets the list for the sort by dropdownlist.
 * @return array
 */
function GetSortByData() {
    return array(
                array("value" => "startAD", "text" => "Most recently started first"),
                array("value" => "startBC", "text" => "Most recently started last"),
                array("value" => "statusA", "text" => "Status ordered A-Z"),
                array("value" => "statusZ", "text" => "Status ordered Z-A"),
                array("value" => "typeA", "text" => "Type ordered A-Z"),
                array("value" => "typeZ", "text" => "Type ordered Z-A"),
                array("value" => "nameA", "text" => "Name ordered A-Z"),
                array("value" => "nameZ", "text" => "Name ordered Z-A"));
}

/**
 * function GetActionData()
 * Gets the list for the action dropdownlist.
 * @return array
 */
function GetActionData() {
    return array(
                array("value" => "", "text" => "Action"),
                array("value" => "delete", "text" => "Delete"),
                array("value" => "view", "text" => "View Application"));
}

/**
 * function GetFailedLoginAttempts()
 * Gets how many failed login attempts are allowed.
 * @return integer
 */
function GetFailedLoginAttempts() {
    return 5;
}

/**************************
DATA FUNCTIONS
**************************/

/**
 * function DeleteLoanApps($dbh, $Cu, $parameters)
 * Deletes the lnappuserresponse record (and then return the grid without the records.)
 *
 * @param $dbh -- the database connection
 * @param $Cu -- the credit union
 * @param $parameters -- an array of parameters from the request string.
 *
 * @return $status -- "000" if successful; nonzero otherwise.
 * @return $error -- "" if successful; nonempty otherwise.
 * @return $info -- If successful: "Loans were deleted successfully."
 * @return $gridData.total -- how many grid records there are.
 * @return $gridData.data -- the grid records.
 */
function DeleteLoanApps($dbh, $Cu, $parameters) {
    try {
        $isDelete = true;
        $results = _ValidateReadLoanAppsParameters($parameters, $isDelete);
        if ($results["status"] !== "000") {
            throw new exception ($results["error"], 1);
        }
        $parameters = $results["data"];

        $sql = "delete from lnappuserresponse where respid in
                    (select r.respid from lnappuserresponse r
                    inner join lnappuser u on r.userid = u.userid
                    where u.cu = '$Cu' and r.respid in " . $parameters["deleteIn"] . ")";
                    // I like guarantees that no data outside the CU will be deleted.

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

        $returnArray = ReadLoanApps($dbh, $Cu, $parameters, $isDelete);
        $returnArray["info"] = "Loans were deleted successfully."; // Give a success message.
    } catch (exception $e) {

        // Requery the grid regardless.  Read should already have worked as this point.
        $returnArray = ReadLoanApps($dbh, $Cu, $parameters, $isDelete);
        $returnArray["status"] = $e->getCode();
        $returnArray["error"] = $e->getMessage();
    }
    return $returnArray;
}

/**
 * function ReadLoanApps($dbh, $Cu, $parameters, $isDelete)
 * Reads the grid with the current sort and filter settings.
 *
 * @param $dbh -- the database connection
 * @param $Cu -- the credit union
 * @param $parameters -- the request parameters
 * @param $isDelete -- If the call is actually deleting records, bypass validation (already done)
 *
 * @return $status -- "000" if successful; nonzero otherwise.
 * @return $error -- "" if successful; nonempty otherwise.
 * @return $gridData.total -- how many grid records there are.
 * @return $gridData.data -- the grid records.
 */
function ReadLoanApps($dbh, $Cu, $parameters, $isDelete) {
    try {
        // Prevent double validation if deleting.
        if (!$isDelete) {
            $results = _ValidateReadLoanAppsParameters($parameters, false);
            if ($results["status"] !== "000") {
                throw new exception ($results["error"], 1);
            }
            extract($results["data"]);
        } else {
            extract($parameters);
        }


        // Timezone needs to be part of the query due to filtering based on the start, submit, or modified date.
        $tz = GetCreditUnionTimezone($dbh, $Cu);

        $select =  "set time zone '$tz';
                    select case when l.respstatus = '000' then 'Submitted'
                        when l.respstatus in ('020','021','022') then 'Submitted With Errors'
                        when l.respstatus = '027' then 'Approved'
                        when l.respstatus = '028' then 'Rejected'
                        when l.respstatus = '026' then 'Pending'
                        when l.respstatus in ('029','030') then 'Requires Review'
                        else '' end as status,
                        trim(l.resplname) || ', ' || trim(l.respfname) || ' ' || l.respmname as name, m.loantitle, l.respid, a.userid, l.loanid,
                        l.respstatus, l.respamt, l.respstarton, l.respdob, l.respssn, l.respmember, lower(a.email) as email, l.respcoreloanappid";
        $sql = "from lnappuser a
                inner join lnappuserresponse l on a.userid = l.userid
                inner join lnappschemamaster m on l.loanid = m.loanid";

        $where = array("a.cu = '" . prep_save($Cu, 10) . "'");
        if ($userId != 0) {
            $where[] = "a.userid = " . intval($userId);
        } else if ($lookFor != "") {
            switch ($searchIn) {
                case "email":
                    $where[] = "lower(a.email) = '" . strtolower(prep_save($lookFor, 50)) . "'";
                    break;
                case "dob":
                    $where[] = "l.respdob = '" . prep_save($lookFor, 10) . "'";
                    break;
                case "ssn":
                    $where[] = "l.respssn = '" . prep_save($lookFor, 11) . "'";
                    break;
                case "name":
                    $where[] = "strpos(lower(l.resplname), '" . strtolower(prep_save($lookFor, 55)) . "') > 0";
                    break;
                case "account":
                    $where[] = "l.respmember = '" . prep_save($lookFor, 15) . "'";
                    break;
                case "loan":
                    $where[] = "l.respcoreloanappid = '" . prep_save($lookFor, 15) . "'";
                    break;
            }
        }

        if ($withStatus != "") {
            switch ($withStatus) {
                case 'NoStat':
                        // ** Try to show any loan that does not have any other form of status -- started, but not finished, or a zero..
                        $where[] = "(l.respstatus = '000' or l.respstatus = '0' or l.respstatus is null)";
                        break;
                case 'SubErr':
                        $where[] = "l.respstatus in ('020','021','022')";
                        break;
                case 'Approved':
                        $where[] = "l.respstatus = '027'";
                        break;
                case 'Rejected':
                        $where[] = "l.respstatus = '028'";
                        break;
                case 'Pending':
                        $where[] = "l.respstatus = '026'";
                        break;
                case 'ReqReview':
                        $where[] = "l.respstatus in ('029','030')";
                        break;
                case 'All':
                default:
                # show all loanapps regardless of status
            }
        }

        // ** Date Range Filter
        if ($rangeType != "") {
            $dateFilterField = "";
            switch ($rangeType) {
              case "submit":
                $dateFilterField = 'l.respsubmiton';
                break;
              case "start":
                $dateFilterField = 'l.respstarton';
                break;
              case "modified":
                $dateFilterField = 'l.respmodifiedon';
                break;
            }
            if ($dateFilterField != "") {
                if ($fromDate != "") {
                    $where[] = "$dateFilterField >= '" . prep_save($fromDate) . "'";
                }
                if ($toDate != "") {
                    $where[] = "$dateFilterField <= '" . prep_save($toDate) . "'";
                }
            }
        }

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

        switch($sortBy) {
            case "startAD":
                $sortSQL = "order by l.respstarton desc";
                break;
            case "startBC":
                $sortSQL = "order by l.respstarton asc";
                break;
            case "statusA":
                $sortSQL = "order by 1 asc";
                break;
            case "statusZ":
                $sortSQL = "order by 1 desc";
                break;
            case "typeA":
                $sortSQL = "order by m.loantitle asc";
                break;
            case "typeZ":
                $sortSQL = "order by m.loantitle desc";
                break;
            case "nameA":
                $sortSQL = "order by 2 asc";
                break;
            case "nameZ":
                $sortSQL = "order by 2 desc";
                break;
            default:
                $sortSQL = "";
                break;
        }

        $limitSQL = $limit == 0 && $offset == 0 ? "" : "limit $limit offset $offset";

        $countSQL = "select count(*) $sql";
        $mainSQL = "$select $sql $sortSQL $limitSQL";

        $sth = db_query($countSQL, $dbh);
        if (!$sth) {
            throw new exception ("count query failed.", 2);
        }
        $results = db_fetch_row($sth, 0);
        $total = intval($results[0]);

        $sth = db_query($mainSQL, $dbh);
        if (!$sth) {
            throw new exception ("main query failed.", 3);
        }
        $results = db_fetch_all($sth);
        $results = $results === false ? array() : $results;

        $gridData = array();
        foreach($results as $record) {
            $gridData[] = array("loanappid" => $record["respcoreloanappid"], "start" => $record["respstarton"], "account" => $record["respmember"],
                                "status" => $record["status"], "respid" => $record["respid"], "dob" => $record["respdob"], "title" => $record["loantitle"],
                                "ssn" => $record["respssn"], "email" => $record["email"], "name" => $record["name"], "userid" => $record["userid"]);
        }

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

/**
 * function _ValidateReadLoanAppsParameters($parameters, $isDelete)
 * This function validates the parameters for read and delete operations.
 *
 * @param $parameters -- the parameters from the request line.
 * @param $isDelete -- if delete, also need to validate the "loanids" parameter.
 *
 * @return $status -- "000" if successful; nonzero otherwise.
 * @return $error -- "" if successful; nonempty otherwise.
 * @return $data -- the sanitized version of the parameters.
 */
function _ValidateReadLoanAppsParameters($parameters, $isDelete) {
    try {
        if ($isDelete) {
            $loanids = HCU_array_key_value("loanids", $parameters);
            $loanids = $loanids === false ? "" : trim($loanids);
            if ($loanids == "") {
                throw new exception ("Loan ids are required.", 14);
            }
            $loanids = HCU_JsonDecode($loanids);
            if (count($loanids) == 0) {
                throw new exception ("Loan ids are required.", 15);
            }
            foreach($loanids as $i => $loanid) {
                if (!ctype_digit(strval($loanid))) {
                    throw new exception ("Loan ids are invalid.", 16);
                }
            }
            $parameters ["deleteIn"] = "(" . implode(", ", $loanids) . ")";
        }

        // Search in validation
        if (HCU_array_key_exists("searchIn", $parameters)) {
            $searchInData = GetSearchInData();
            $found = false;
            foreach($searchInData as $searchInRow) {
                if ($searchInRow["value"] == $parameters["searchIn"]) {
                    $found = true;
                    break;
                }
            }
            if (!$found) {
                throw new exception ("Search in is invalid.", 1);
            }
        } else {
            $parameters["searchIn"] = "";
        }

        // look for validation
        if ($parameters["searchIn"] != "" && HCU_array_key_exists("lookFor", $parameters)) {
            $parameters["lookFor"] = trim($parameters["lookFor"]);
        } else {
            $parameters["lookFor"] = "";
        }

        switch($parameters["searchIn"]) {
            case "":
                break; // Not searching for anything.
            case "email":
                if (!validateEmail($parameters["lookFor"])) {
                    throw new exception ("Email is invalid.", 3);
                }
                break;
            case "dob":
                // DateConvert class, also optional last param $century could be used if
                // abberations show up (they shouldn't for a birth date search)
                $parameters["lookFor"] = FilterDateForDatabase($parameters["lookFor"], 'm/d/Y');
                if (empty($parameters["lookFor"])) {
                    throw new exception ("Date is invalid.", 4);
                }
                break;
            case "ssn":
                if (!ValidateSSN($parameters["lookFor"])) {
                    throw new exception ("SSN is invalid.", 5);
                }
                break;
            case "account":
            case "loan":
                if (!ctype_digit(strval($parameters["lookFor"]))) {
                    throw new exception ("search string needs to be a number.", 6);
                }
                break;
        }

        // Status validation
        if (HCU_array_key_exists("withStatus", $parameters)) {
            $statusData = GetWithStatusData();
            $found = false;
            foreach($statusData as $statusRow) {
                if ($statusRow["value"] == $parameters["withStatus"]) {
                    $found = true;
                    break;
                }
            }
            if (!$found) {
                throw new exception ("Status is invalid.", 2);
            }
        } else {
            $parameters["withStatus"] = "";
        }

        // Range type validation
        if (HCU_array_key_exists("rangeType", $parameters)) {
            $rangeTypeData = GetRangeTypeData();
            $found = false;
            foreach($rangeTypeData as $rangeTypeRow) {
                if ($rangeTypeRow["value"] == $parameters["rangeType"]) {
                    $found = true;
                    break;
                }
            }
            if (!$found) {
                throw new exception ("Range type is invalid.", 7);
            }
        } else {
            $parameters["rangeType"] = "";
        }

        // Date validation
        $parameters["fromDate"] = $parameters["rangeType"] == "" ? false : HCU_array_key_value("fromDate", $parameters);
        $parameters["fromDate"] = $parameters["fromDate"] === false ? "" : trim($parameters["fromDate"]);
        $parameters["toDate"] = $parameters["rangeType"] == "" ? false : HCU_array_key_value("toDate", $parameters);
        $parameters["toDate"] = $parameters["toDate"] === false ? "" : trim($parameters["toDate"]);

        if ($parameters["fromDate"] == "") {
            if ($parameters["rangeType"] != "") {
                throw new exception ("From date is required.", 8);
            }
        } else {
            if (!validateDate($parameters["fromDate"])) {
                throw new exception ("From date is invalid.", 10);
            }
            $parameters["fromDate"] = ModifyDateForDatabase($parameters["fromDate"]);
        }

        if ($parameters["toDate"] == "") {
            if ($parameters["rangeType"] != "") {
                throw new exception ("To date is required.", 8);
            }
        } else {
            if (!validateDate($parameters["toDate"])) {
                throw new exception ("To date is invalid.", 12);
            }
            $parameters["toDate"] = ModifyDateForDatabase($parameters["toDate"]);
        }

        if ($parameters["fromDate"] > $parameters["toDate"]) {
            throw new exception ("To date cannot be before from date.", 11);
        }

        // Sort validation
        if (HCU_array_key_exists("sortBy", $parameters) && $parameters["sortBy"] != "") {
            $sortByData = GetSortByData();
            $found = false;
            foreach($sortByData as $sortByRow) {
                if ($sortByRow["value"] == $parameters["sortBy"]) {
                    $found = true;
                    break;
                }
            }
            if (!$found) {
                throw new exception ("Sort by is invalid.", 13);
            }
        } else {
            $parameters["sortBy"] = "startAD";
        }

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

/**
 * Supports multiple date input formats by converting it to the format you need
 * and allows users to enter date formats that are not so strict. See the
 * referenced class object for full documentation.
 * @param string $date
 * @param string $requested_format DateTime format string (default below)
 * @param int $century, overrides default IBM 40 year algo - see docs in DateConvert
 * @return string
 * @throws Exception
 */
function FilterDateForDatabase($date = '', $requested_format = 'Y-m-d', $century = 0)
{
  $dc = new DateConvert();
  return $dc->ConvertDateFromFormat($date, $requested_format, $century);
}

/**
 * function ModifyDateForDatabase($date)
 * Changes the date format from MM/dd/yyyy to yyyy-MM-dd which is better for the database.
 * I made the change so that I can use a common date validation function.
 *
 * @param $date -- the old date in MM/dd/yyyy
 *
 * @return $date -- the new date in yyyy-MM-dd
 */
function ModifyDateForDatabase($date) {
    return substr($date, - 4) . "-" . substr($date, 0, 2) . "-" . substr($date, 3, 2);
}

/**
 * function ValidateSSN($ssn)
 * A simple function to validate the syntax of the SSN.
 * I did not see a function anywhere like validateEmail or validateDate.
 *
 * @param $ssn -- the SSN to verify
 *
 * @return $passed -- true if the SSN is valid
 */
function ValidateSSN($ssn) {
    $pattern = '/^\d{3}-\d{2}-\d{4}$/';
    $result = preg_match($pattern, $ssn);
    return $result === 1;
}

/**
 * function ReadApplicantSearch($dbh, $Cu, $parameters)
 * This search for the applicant on the applicant tab.
 *
 * @param $dbh -- the database connection.
 * @param $Cu -- the credit union.
 * @param $parameters -- the parameters.
 *
 * @return $status -- "000" if successful; nonzero otherwise.
 * @return $error -- "" if successful; nonempty otherwise.
 * @return $gridData.total -- how many grid records there are.
 * @return $gridData.data -- the grid records.
 */
function ReadApplicantSearch($dbh, $Cu, $parameters) {
    try {
        $results = _ValidateApplicantSearch($parameters);
        if ($results["status"] !== "000") {
            throw new exception ("Search is invalid.", 1);
        }
        extract($parameters);

        $select = "select ln.userid \"lnId\", ln.email \"email\", coalesce(ln.failedloginattempts, 0) \"failedAttempts\",
                case when ln.userlogintype = 'H' then 'Home Banking' when ln.userlogintype = 'N' then 'Non-Home Banking Member' else 'Loan App User' end \"loginType\",
                coalesce(trim(u.user_name), '<None>') \"username\", coalesce(trim(ln.session_account), '<None>') \"accountnumber\"";

        $sql = "from lnappuser ln
                left join ${Cu}user u on ln.banking_user_id = u.user_id
                where ln.cu = '" . prep_save($Cu, 10) . "'";

        if ($searchIn === "email") {
            $sql .= " and lower(trim(ln.email)) like '%" . strtolower(prep_save($lookFor, 50)) . "%'";
        } else if ($searchIn === "account") {
            $sql .= " and trim(ln.session_account) like '%" . prep_save($lookFor) . "%'";
        } else if ($searchIn === "username") {
            $sql .= " and lower(trim(u.user_name)) like '%" . strtolower(prep_save($lookFor)) . "%'";
        }

        $sortSQL = "order by ln.userid desc"; // Most recent first?
        $limitSQL = $limit == 0 && $offset == 0 ? "" : "limit $limit offset $offset";

        $countSQL = "select count(*) $sql";
        $mainSQL = "$select $sql $sortSQL $limitSQL";

        $sth = db_query($countSQL, $dbh);
        if (!$sth) {
            throw new exception ("count query failed.", 2);
        }
        $results = db_fetch_row($sth, 0);
        $total = intval($results[0]);

        $sth = db_query($mainSQL, $dbh);
        if (!$sth) {
            throw new exception ("main query failed.", 3);
        }
        $gridData = db_fetch_all($sth);
        $gridData = $gridData === false ? array() : $gridData;

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

/**
 * function _ValidateApplicantSearch($parameters)
 * This validates the applicant search data call.
 *
 * @param $parameters -- the parameters.
 *
 * @return $status -- "000" if successful; nonzero otherwise.
 * @return $error -- "" if successful; nonempty otherwise.
 */
function _ValidateApplicantSearch($parameters) {
    try {
        $searchIn = HCU_array_key_value("searchIn", $parameters);
        $lookFor = HCU_array_key_value("lookFor", $parameters);

        if ($searchIn === false || $lookFor === false) {
            throw new exception ("Search is missing parameters.", 1);
        }
        switch($searchIn) {
            case "email":
                if (!validateEmail($lookFor)) {
                    throw new exception ("Email is invalid.", 3);
                }
                break;
            case "account":
                if (!ctype_digit(strval($lookFor))) {
                    throw new exception ("search string needs to be a number.", 4);
                }
                break;
            case "username":
                break;
            default:
                throw new exception ("Search is invalid.", 2);
        }

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

/**
 * function ReadApplicant($dbh, $Cu, $parameters)
 * This reads the applicant (view button on the applicant tab grid.)
 *
 * @param $dbh -- the database connection.
 * @param $Cu -- the credit union.
 * @param $parameters -- the parameters.
 *
 * @return $status -- "000" if successful; nonzero otherwise.
 * @return $error -- "" if successful; nonempty otherwise.
 * @return $applicantData -- used for the template for applicant data.
 * @return $type -- "read" for purpose of determining what to do on return.
 */
function ReadApplicant($dbh, $Cu, $parameters) {
    try {
        $userId = HCU_array_key_value("userId", $parameters);
        if ($userId === false) {
            throw new exception ("Userid is required.", 1);
        }

        $sql = "select ln.userid \"lnId\", ln.email \"email\", coalesce(ln.failedloginattempts, 0) \"failedAttempts\",
                case when ln.userlogintype = 'H' then 'Home Banking' when ln.userlogintype = 'N' then 'Non-Home Banking Member' else 'Loan App User' end \"loginType\",
                coalesce(trim(u.user_name), '') \"username\", coalesce(trim(ln.session_account), '') \"accountnumber\",
                coalesce(trim(ln.confidenceword), '<None>') \"confidence\"
                from lnappuser ln
                left join ${Cu}user u on ln.banking_user_id = u.user_id
                where ln.cu = '" . prep_save($Cu, 10) . "' and ln.userid = " . intval($userId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("query failed.", 5);
        }
        if (db_num_rows($sth) <= 0) {
            throw new exception ("User not found.", 2);
        }
        $applicantData = db_fetch_assoc($sth, 0);

        $applicantData["isLocked"] = $applicantData["failedAttempts"] >= GetFailedLoginAttempts();

        // don't show password stuff for type "H" because it uses home banking credentials
        $applicantData["canChangeLogin"] = $applicantData["loginType"] !== "Home Banking";

        $sql = "select a.user_answer \"answer\", m.quest_text \"question\"
                from lnappuser_questselect a
                inner join cuquestmaster m on a.questid = m.quest_id
                where a.userid = " . intval($userId) . " and m.quest_lang = 'en_US'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("query failed.", 3);
        }
        $results = db_fetch_all($sth);
        $results = $results === false ? array() : $results;

        $applicantData["securityQuestions"] = $results;

        $sql = "select count(*) from lnappuserresponse where userid = " . intval($userId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("query failed.", 4);
        }
        if (db_num_rows($sth) <= 0) {
            throw new exception ("query failed.", 6);
        }
        list ($numLoanApps) = db_fetch_row($sth, 0);

        $applicantData["numLoanApps"] = $numLoanApps;

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

/**
 * function SaveApplicant($dbh, $Cu, $parameters)
 * This saves the applicant on the applicant tab.
 *
 * @param $dbh -- the database connection.
 * @param $Cu -- the credit union.
 * @param $parameters -- the parameters.
 *
 * @return $status -- "000" if successful; nonzero otherwise.
 * @return $error -- "" if successful; nonempty otherwise.
 * @return $type -- "read" for purpose of determining what to do on return.
 * @return $info -- the message to display to the screen on success.
 */
function SaveApplicant($dbh, $Cu, $parameters) {
    try {
        $userId = HCU_array_key_value("userId", $parameters);
        if ($userId === false) {
            throw new exception ("Userid is required.", 4);
        }

        $lockSetting = HCU_array_key_value("lockSetting", $parameters);
        if ($lockSetting === false) {
            throw new exception ("Lock setting is required.", 3);
        }

        $email = HCU_array_key_value("email", $parameters);
        if ($email === false) {
            throw new exception ("Email is required.", 5);
        }

        $password = HCU_array_key_value("password", $parameters);
        if ($password === false) {
            throw new exception ("Password is required.", 6);
        }

        $confirm = HCU_array_key_value("confirm", $parameters);
        if ($confirm === false) {
            throw new exception ("Confirm is required.", 7);
        }

        $sql = "select ln.userid \"lnId\", lower(coalesce(trim(ln.email), '')) \"email\", coalesce(ln.failedloginattempts, 0) \"failedAttempts\"
                from lnappuser ln
                where ln.userid = " . intval($userId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Query failed.", 1);
        }
        if (db_num_rows($sth) <= 0) {
            throw new exception ("User not found.", 2);
        }

        list($userId, $originalEmail, $failedAttempts) = db_fetch_row($sth, 0);
        $saveList = array();

        switch($lockSetting) {
            case "noChange":
                break;
            case "setLocked":
                $saveList[] = "failedloginattempts = " . intval(GetFailedLoginAttempts());
                break;
            case "setUnlocked":
                $saveList[] = "failedloginattempts = 0";
                break;
            default:
                throw new exception ("Lock setting is not valid.", 8);
                break;
        }

        $email = trim($email);
        if ($email == "") {
            if ($originalEmail == "") {
                throw new exception ("Please set an email.", 9);
            }
        } else if (strtolower($email) !== $originalEmail) {
            if (!validateEmail($email)) {
                throw new exception ("Email is not valid.", 11);
            }

            $sql = "select count(*) from lnappuser ln where lower(trim(ln.email)) = '" . strtolower(prep_save($email, 50)) . "'";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("Select query failed.", 15);
            }
            if (db_num_rows($sth) <= 0) {
                throw new exception ("Select query failed.", 16);
            }
            list($dupCount) = db_fetch_row($sth, 0);
            if ($dupCount > 0) {
                throw new exception ("Email needs to be unique.", 17);
            }
            $saveList[] = "email = '" . prep_save($email, 50) . "'";
        }

        if ($password !== "") {
            if ($confirm !== $password) {
                throw new exception ("Passwords do not match.", 10);
            }

            if (strlen($password) < 6 || strlen($password) > 20) {
                throw new exception ("Password must be from 6 to 20 characters long.", 12);
            }

            if ( !(preg_match("/\d/",$password) && preg_match("/\D/",$password))) {
                throw new exception ("Password must contain both number and letter characters,", 13);
            }

            if (preg_match("/['\"]/", $password)) {
                throw new exception ("There are invalid characters in password.", 14);
            }

            $saveList[] = "pwd = '" . prep_save(password_hash($password, PASSWORD_DEFAULT)) . "'";
        }

        if (count($saveList) > 0) {

            $sql = "update lnappuser
                    set " . implode(", ", $saveList) . "
                    where userid = " . intval($userId) . " and cu = '" . prep_save($Cu, 10) . "'";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("Update query failed.", 18);
            }
        }

        $returnArray = array("status" => "000", "error" => "", "info" => "Applicant was updated successfully.", "type" => "update");
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "type" => "update");
    }
    return $returnArray;
}