mUserList.data

<?php
/**
 * File: mUserList.data
 * This file works is for the data operations of the file mUserList.prg.  Currently, the only operation is "read" but that could change.  As its .prg counterpart, it is only for Odyssey.
 */

// ********************************************************************************************************************************************************************************
// Top portion: includes and extracting parameters from commandline and/or post.
// ********************************************************************************************************************************************************************************

$monLibrary = dirname(__FILE__) . "/../library";
$sharedLibrary = dirname(__FILE__) . "../../shared/library";
require_once("$monLibrary/cu_top.i");

$string = array("filter" => FILTER_SANITIZE_STRING);
$params = array("operation" => $string, "memberNumber" => $string, "numRows" => $string, "userName" => $string, "email" => $string, "cu" => $string);
$vars = array("operation" => "", "memberNumber" => "", "numRows" => "", "userName" => "", "email" => "", "cu" => "");
HCU_ImportVars($vars, "", $params);

$operation = HCU_array_key_value("operation", $vars);
$operation = $operation === false ? "" : trim($operation);
$showSQL = $SYSENV["devmode"];

switch($operation) {
    case "read":
    case "":
        $returnArray = ReadUserList($dbh, $SYSENV, $vars);
        break;
    default:
        $returnArray = array("error" => "Operation is not known.", "data" => array());
        break;
}

header('Content-type: application/json');
if (!$showSQL) {
    unset($returnArray["sql"]);
}
print HCU_JsonEncode($returnArray);

// ********************************************************************************************************************************************************************************
// Common to .prg and .data (if there is enough lines of code, put it in an include file.)
// ********************************************************************************************************************************************************************************

/**
 * function GetMaxRowsList()
 * This function returns the specified options of the max rows kendoDropDownList.  It is used in the prg to set the kendo definition and in the data to verify that it is a correct option.
 */
function GetMaxRowsList() {
      return array("50", "100", "ALL");
}

// ********************************************************************************************************************************************************************************
// Start of functions
// ********************************************************************************************************************************************************************************

/**
 * function ReadUserList($dbh, $SYSENV, $params)
 * This function takes in the parameters from the search and returns a list of user/account combinations (Cartesian product)
 *
 * @param $dbh -- the database connection
 * @param $SYSENV -- the system environment variables
 * @param $params -- the parameters from the POST.  These come from the search controls in the search DIV.
 *
 * @return $returnArray
 *      If successful: $returnArray contains the data for the grid and an empty error array.
 *      If not successful: $returnArray contains an error array with one error and an empty data array.
 */
function ReadUserList($dbh, $SYSENV, $params) {
    $logger = $SYSENV["logger"];
    unset($SYSENV);
    $sqls = array();

    try {
        // Get parameters and see if there are any errors in retrieval.
        $cu = HCU_array_key_value("cu", $params);
        $cu = $cu === false ? "" : trim($cu);

        $memberNumber = HCU_array_key_value("memberNumber", $params);
        $memberNumber = $memberNumber === false ? "" : trim($memberNumber);

        $numRows = HCU_array_key_value("numRows", $params);
        $numRows = $numRows === false ? "" : trim($numRows);

        $userName = HCU_array_key_value("userName", $params);
        $userName = $userName === false ? "" : trim($userName);

        $email = HCU_array_key_value("email", $params);
        $email = $email === false ? "" : trim($email);

        $where = array();

        if ($cu == "") {
            throw new exception("Cu must be defined.", 2);
        }

        if ($memberNumber != "") {
            if (preg_match('/\D/', trim($memberNumber)) !== 0) { // If there is a non-digit character then throw error message.  1: there is a non-digit + false: reg expression errors out.
                throw new exception("Member must be a number.", 3);
            }
            $where[] = "maa.accountnumber = '" . prep_save(intval($memberNumber), 12) . "'"; // Even though this is a number, it is a character in the database.
        }

        if (!in_array($numRows, GetMaxRowsList())) {
            throw new exception("Max rows must be either 50, 100, or ALL.", 4);
        }
        $limit = $numRows == "ALL" ? "" : "limit $numRows";

        if ($userName != "") {
            $where[] = "u.user_name = '" . prep_save($userName, 50) . "'";
        }

        if ($email != "") {
            if (!validateEmail($email)) {
                throw new exception("Email is not valid.", 5);
            }
            $where[] = "u.email = '" . prep_save($email, 255) . "'";
        }

        $where = count($where) > 0 ? "where " . implode(" and ", $where) : "";

        $preppedCu = prep_save($cu, 12);
        $sql = "select case when u.lastlogin = '' then 1 else 0 end as sort, u.lastlogin, u.user_id as login, u.user_name as user, u.email, maa.accountnumber as member, maa.estmnt_flag
                from (select user_id, user_name, email, coalesce(trim(lastlogin), '') as lastlogin from ${preppedCu}user) u
                inner join (select ma.accountnumber, ma.estmnt_flag, ua.user_id from ${preppedCu}memberacct ma
                inner join (select row_number() over(partition by user_id, accountnumber) as rown, accountnumber, user_id from ${preppedCu}useraccounts) ua
                on ma.accountnumber = ua.accountnumber and ua.rown = 1) maa on u.user_id = maa.user_id
                $where order by 1, 2 desc $limit";
                // Using row_number is less expensive than using the "distinct" keyword.
        $sqls[] = $sql;

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

        $list = array();
        for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
            $row["lastLogin"] = isset($row["lastlogin"]) ? $row["lastlogin"] : "";
            $row["hasEstmnt"] = isset($row["estmnt_flag"]) ? trim($row["estmnt_flag"]) == "Y" : false;
            $row["member"] = isset($row["member"]) ? trim($row["member"]) : "";
            unset($row["estmnt_flag"]);
            unset($row["lastlogin"]);
            $list[] = $row;
        }

        $returnArray = array("error" => "", "data" => $list, "sql" => $sqls);
    } catch (exception $e) {
        $logger->error("Error: ". $e->getMessage());
        $code = "HCU-" . str_pad(strval($e->getCode()), 4, STR_PAD_LEFT, "0"); // E.g. 0003.
        $returnArray = array("error" => "A problem occurred. $code", "data" => array(), "sql" => $sqls);
    }
    return $returnArray;
}