cuReporting.prg

<?php
/**
 * File: cuReporting.prg
 * Authors SPB and Gus.
 *
 * This file was formerly cuReports.prg, cuReports, and cu_reports.  Most of the reports were migrated from the old script of cu_reports.  They were probably collected over 20 years or so.
 * Then it was renamed cuReports and it was modernized to use the kendo framework for the engine.  Most of the code is from that revision.
 * It was then added to Odyssey where mostly the reports were updated to use new schemas.  That stint was under Gus.
 * Now, it is slightly being updated again.  #199 requires grouping so that was added and it is generally updated for the mistakes that I realize.  One of those mistakes was lack of comments.
 *
 * The main heavy work is in the include reporting.i which previous names were reports.i and reports.  Reporting.i is common to this file, info_reports.prg, loggingReport.prg,
 * and adm_reports.prg.  The rest of the scripts need to be moved from reports.i to reporting.i before reports.i is removed.
 *
 * This file is for cu-centric reports.  For reports across ALL cu's see info_reports.prg.  See adm_reports.prg for non-CU reports.
 */

require_once("$sharedLibrary/reporting.i");
require_once("$admLibrary/userSearch.i");
require_once("$admLibrary/aGroupSupport.i");
require_once("$admLibrary/aMemberSupport.i");

// Need to translate to translate list.
require_once("$sharedLibrary/hcuTranslate.i");
require_once("$bankingLibrary/hcuTransferScheduled.i");

$parameters = array("a" => array("operation" => ""));
$string = array("filter" => FILTER_SANITIZE_STRING);
$array = array("filter" => FILTER_DEFAULT); // For inputs that require {[]}
HCU_ImportVars($parameters, "a", array("operation" => $string, "csv" => $string, "report" => $string, "sort" => $array, "filter" => $array));

$operation = HCU_array_key_exists("operation", $parameters["a"]) ? trim($parameters["a"]["operation"]) : "";
$csv = HCU_array_key_exists("csv", $parameters["a"]) ? trim($parameters["a"]["csv"]) : "";
$report = HCU_array_key_exists("report", $parameters["a"]) ? trim($parameters["a"]["report"]) : "";
$filter = HCU_array_key_exists("filter", $parameters["a"]) ? trim($parameters["a"]["filter"]) : "";
$sort = HCU_array_key_exists("sort", $parameters["a"]) ? trim($parameters["a"]["sort"]) : "";

$self = "$menu_link?ft=$ft";

$sql = "select flagset, pname from cuadmin where cu = '$Cu'";
$sth = db_query($sql,$dbh);
list($cuflags, $prodname) = db_fetch_array($sth,0);
$prodname = (trim("$prodname") == "" ? "$Cu" : trim("$prodname"));

$reportList = GetReportList($Cu, $prodname);
$MC = new hcu_talk_base("en_US");

/**
 * If there is an operation parameter set, then go do that data call.  If there is a report then get the report.  Otherwise, give a list of reports.
 */
if ($operation != "") {
  switch ($operation) {
    case "readReport":
      ReadReport($Cu, $dbh, $reportList, GetDefaultDateFormat());
      break;
    case "generateCSV":
      GenerateCSV($Cu, $dbh, $reportList, GetDefaultDateFormat());
      break;
    default: // Won't get here
      header('Content-type: application/json');
      $returnArray = array("error" => array("Operation not specified: '$operation'"), "record" => "", "operation" => "");
      print HCU_JsonEncode($returnArray);
  }
} else { // first loading of script, present report summary list
    $reportNotFound = false;
    $report = trim($report);

    if (isset($reportList[$report])) {
      $reportRecord = $reportList[$report];
      $reportRecord["reportTitle"] = isset($reportRecord["reportTitle"]) ? $reportRecord["reportTitle"] : trim($reportRecord["title"]);
      $title = isset($reportRecord["reportTitle"]) ? trim($reportRecord["reportTitle"]) : (isset($reportRecord["title"]) ? trim($prodname) . " " . trim($reportRecord["title"]) : trim($report));
    } else if ($report != "") {
      $reportNotFound = true;
    }

    $printerFriendly = trim($csv) == "print";

    $report == "" || $reportNotFound ? PrintReportListPage($reportList, $self, $report, $reportNotFound, $live)
      : ($printerFriendly ? PrintReportPagePrintOnly($reportList, $report, $prodname, $title, $self, $sort, $filter) : PrintReportPage($reportList, $report, $prodname, $title, $self));
}

// ***********************************************
// Configuration
// ***********************************************

/**
 * function GetDefaultDateFormat()
 * @return The default date format for the reports.  This is used directly for PHP date formatting.  It is translated for kendo date formatting.
 */
function GetDefaultDateFormat() {
  return "MM/dd/yyyy";
}

/**
 * function GetReportList($Cu, $prodname)
 * This is the bread and butter of the report script.  Here, we have all the scripts in this particular format:
 *
 * array($reportKey => array("title" => $title, "icon" => $icon, "sql" => $sql, "dateFormat" => $dateFormat, "dateEmpty" => $dateEmpty, "cols" => array($colTitle => $colType, ...),
 *    "groupBy" => $groupBy, "groupDescription" => $groupDescription, "ddl" => array($ddlColumn => array($value => $text, ...), ...),
 *    "href" => $href, "colHeader" => $colHeader, "schemaFunc" => $schemaFunc), ...)
 *
 * $reportKey -- The key that is necessary for the report page.  It needs to be unique.  This will show up on the URL for the report and is referenced in the menu.
 * $title -- The title of the report.  This will show up as the name of the CSV file on download, the title at the top of the page for the report, and the link on the report menu.
 *    If $title is missing, then it uses the key.
 * $sql -- This is required.  It is the base SQL of the report.  Aliases are ignored.  These come from the $col array.  Any sorts are also ignored.  Any sorts and filters are added to this
 *    SQL.  If there is no sort, then the first column is sorted.  Also, the columns are renamed as per the $col array.  This is so any sorts and filters can refer to the column name.
 * $cols -- This is required.  This should be in order of the columns mentioned in the SQL.  It consists of $colTitle => $colValue pairs.
 * $colTitle -- This is the title of the column that will appear in the kendo grid.  The kendo column name also comes from the camel case version of this string.
 *    If the title starts with a number, the kendo column name will have a "n" prepended.  This is because kendo doesn't take kindly to columns starting with a number.
 *    Also, if two columns have the same title, they will still work.  If there is a "." in the title, the actual title is everything before the period and the name takes everything.
 * $colValue -- This is the column type.  Column can be one of the following: "number", "string", "date", "boolean", "odata", "currency", "ignore", "list", "hidden"
 *    "number" -- refers to kendo's "number" format.  Text aligns right.
 *    "decimal" -- like number but also restrict the number of digits but no need for a $.
 *    "currency" -- like number except also formats the number to $0.00.
 *    "string" -- refers to kendo's "string" format.  Filter is case sensitive and trims first.
 *    "list" -- like string but also adds the "values" attribute to the kendo column.  Option needs the $ddl option for the values.
 *    "date" -- refers to kendo's "date" format.  This will be formatted according to $defaultDateFormat.
 *    "odata" -- refers to kendo's "odata" format.  Sort and filter are not well defined for this format.
 *    "boolean" -- refers to kendo's "string" format.  (The boolean format doesn't seem to work.)  It has a "values" attribute according to what is true in the dataset and what is false.
 *        Assumed trues are true, "true", "Y", "yes", etc.  Assumed falses are false, "false", "N", "no", etc.  If both the false and true aren't found, then it gets the opposite.
 *        Regardless of what is true and what is false, it will show as "Y" and "N".
 *    "ignore" -- The column shows up in the dataItem of the grid but doesn't show up in the column array.  (So then you can still do stuff with it.)
 *    "hidden" -- The columns shows up in the dataItem and the column array but it has the "hidden" attribute.  (I added it for grouping on a column.)
 *    "description" -- Like a "string" type but there is no text wrap and there are ellipses.
 *    "unencoded" -- Sets the kendo's column attribute encoded to false.  There is no additional formatting at all.
 * $groupBy -- This is an optional parameter.  It refers to the $colTitle of the column to group.  If this is set, then the attributes "serverSideGrouping" and "group" are added to dataSource.
 *    The data for the calls are returned in a format grouped by the column.  The CSV becomes grouped as well.  The attribute "group" is added to the schema to parse the data correctly.
 *    Sorts sort on the groupBy column first before the actual columns being sorted.
 * $groupDescription -- This is an optional parameter.  It doesn't do anything if $groupBy is not set.  It refers to the $colTitle of the description column.
 *    It adds the aggregate max to the description column underneath the group attribute.  This allows that column's value to be displayed in the group header instead of the $groupBy column.
 * $groupForceSortBy -- Forces the group sort to be by the $groupBy when both $groupBy and $groupDescription are defined.
 * $ddl -- This is an optional parameter.  This contains all the DDLs of the "list" columns.
 * $ddlColumn -- This refers to the $colTitle of the group with a DDL.  It needs $value => $text pairs in the DDL.
 * $value -- Refers to the "value" attribute in the "values" attribute for a column.
 * $text -- Refers to the "text" attribute in the "values" attribute for a column.
 * $dateFormat -- This is what any dates should be formatted as for the report.  If this is not set, then it gets the default date format for all the reports.
 * $dateEmpty -- This is what the text will be if the date is null.  The default is an empty string.
 * $href -- Optional.  If set, this the URL is different (versus the default $self&report=$report).
 * $colHeader -- Optional.  If set, then columns will have another line for the column header.
 * $schemaFunc -- Optional.  If set, then it refers to another function defined in this script.  Function looks like addProfileTemplates($colTitle, $colType, $kendoCol, &$columnRecord).
 *    The function is called once per column in the readReportSchema function.
 * $dataFunc -- Optional.  If set, then it referes to another function defined in this script.  Function looks like addLinksToOrphanedRecordsReport($dbh, &$dataRecord).
 *    The function is called once per data row in the getData function.
 * $dataFuncVariables -- Optional array to send to custom function in this script defined by "dataFunc".
 * $showOnCSV -- Optional.  If not set, then "ignore" and "hidden" columns are not shown on the CSV.  Other columns are shown.  If set, for the columns specified, true means the column
 * shows up in the CSV.
 * nonSqlDataFunc -- Either this option or the SQL option needs to be defined.  (If both are defined, SQL takes precedence.)  This is set to function name.  Function needs to be defined here.
 * Function has one parameter: $dbh which is the database connection.  It must return an array with "data" -- array of what should go into the report, "status" -- "000" if successful,
 * "error" -- "" if successful; otherwise it is the text of the error message, "sql" -- optional array of SQL used (if the query is too complex for the report engine or if it needs functions.)
 * defaultSort -- If set, then use this instead of the default sort which is the first visible column ascending.
 *
 * @param $Cu -- the credit union to get the report list for.
 * @param $prodname -- the prodname of the credit union
 * @return $reportList with all the attributes defined above.
 */
function GetReportList($Cu, $prodname) {
  $monthPivot = new PivotCTE(); // Definition is in reports.i

  // Apparently the menu items are the titles and the titles of the reports prepend $prodname minus a couple (10-13) thus I am adding a reportTitle attribute only for cu_reports
  // and these exceptions.
  $reportList = array(
    // 1
  "loginHistory" => array("title" => "Monthly User Login History",
      "sql" => $monthPivot->GetCTE() . ", " . GetLoginCTE($Cu) . " " .
          "select user_id, user_name, case when hcucode = 'CMB' then 'OldMBL' when hcucode = 'ADA' then 'Android' when hcucode = 'APP'
          then 'iPhone' when hcucode = 'MBL' then 'MBLweb' when hcucode = 'DSK' then 'Desktop' else 'Classic' end, " . $monthPivot->GetColumnSQL("logins") . " from loginsCTE a,
          monthPivotCTE p where a.yearmo = p.yearmo group by user_id, user_name, hcucode",
      "cols" => $monthPivot->GetColumns(array("User Id" => "ignore", "User Name" => "unencoded", "Product" => "string")),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
  // 2
  "loginSummary" => array("title" => "Monthly Login Summary",
      "sql" => "with " . GetLoginCTE($Cu) . " " .
          "select substr(yearmo,1,4) || '-' || substr(yearmo,5,2), case when hcucode = 'CMB' then 'OldMBL' when hcucode = 'ADA' then 'Android' when hcucode = 'APP' then 'iPhone'
          when hcucode = 'MBL' then 'MBLweb' when hcucode = 'DSK' then 'Desktop' else 'Classic' end,
          sum(logins), count(*), to_char(avg(logins),'999.9') from loginsCTE group by 1,2 ",
      "cols" => array("Month" => "string", "Product" => "string", "Logins" => "number", "Users" => "number", "Average / Active User" => "number")),

      // 2b
      # selecting remote_ip as inet breaks sorting and filtering
      # casting as varchar adds '/32' subnet notation
      # so cast as varchar to fix sorting / filtering, use regexp_replace to drop the subnet notation MH
    "loginTracking" => array("title" => "User Login Tracking", "dateFormat" => "yyyy/MM/dd HH:mm:ss",
        "sql" => "select user_name, case when hcucode = 'CMB' then 'OldMBL' when hcucode = 'ADA' then 'Android' when hcucode = 'APP' then 'iPhone' when hcucode = 'MBL' then 'MBLweb'
            when hcucode = 'DSK' then 'Desktop' else 'Classic' end, login_dt,
            case when status = 0  then 'LOGIN' when coalesce(status, 0) & 4::int4 = 4  then 'F_Email'
        when coalesce(status, 0) & 8::int4 = 8  then 'F_Challenge' when coalesce(status, 0) & 16::int4 = 16  then 'F_Password' when coalesce(status, 0) & 64::int4 = 64  then 'F_Username'
        when coalesce(status, 0) & 128::int4 = 128 then 'F_Access' when coalesce(status, 0) & 256::int4 = 256 then 'F_MFA' when coalesce(status, 0) & "
        . GetUserFlagsValue('MEM_LOGIN_FAILED_BNDL_TIMEOUT') . "::int4 <> 0 then 'F_Timeout'
        else '' end, regexp_replace(remote_ip::varchar ,'/.*$',''), regexp_replace(metadata::varchar, '\"UA\":', '') from ${Cu}userlogins where user_id is not null",
        "cols" => array("User Name" => "string", "Product" => "string", "Date" => "datetime", "Status" => "string", "RemoteIP" => "string", "UserAgent" => "string")
      ),
    // 3
    "transactionMonth" => array("title" => "Transaction Report by Month", "dateFormat" => "yyyy-MM",
        "sql" => "select date_trunc('month', effective_date), count(*) from ${Cu}transhdr where accountnumber is not null and feature_code = 'TRN' group by 1",
        "cols" => array("Month" => "date", "Total" => "number")),
    // 4
    "transactionDay" => array("title" => "Transaction Report by Date", "dateFormat" => "yyyy-MM-dd",
        "sql" => "select date_trunc('day', effective_date), count(*) from ${Cu}transhdr where accountnumber is not null and feature_code = 'TRN' group by 1",
        "cols" => array("Date" => "date", "Total" => "number")),
    // 5
    "emailStatement" => array("title" => "User Email and Statement Settings",
        "sql" => "select u.user_id, u.user_name, u.email, case when mama.estmnt > 0 then 'Y' else 'N' end, case when u.egenl_flag = 'Y' then 'Y' else 'N' end,
          case when cscs.accountnumber is not null then 'Y' else 'N' end, case when coalesce(u.userflags,0) & 32::int4 > 0 then 'Y' else 'N' end, array_to_string(mama.billpay, ', ')
          from ${Cu}user u
          left join (select ua.user_id, count(case when estmnt_flag = 'Y' then 1 else 0 end) as estmnt, array_agg(distinct billpayid::text) as billpay
            from ${Cu}memberacct ma
            inner join ${Cu}useraccounts ua on ma.accountnumber = ua.accountnumber
            group by ua.user_id) mama on u.user_id = mama.user_id
          left join (select distinct ua.user_id, cs.accountnumber
            from cusms cs
            inner join ${Cu}useraccounts ua on cs.accountnumber = ua.accountnumber and cs.cu = '$Cu') cscs on u.user_id = cscs.user_id",
        "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Address" => "string", "eStatement" => "boolean", "Opt-in List" => "boolean", "TXT Banking" => "boolean",
                        "Bill Pay" => "boolean", "Alt Bill Pay ID" => "string"),
        // 6/11/2018: Bill Pay changed from number to string because with multiple values, it is a comma separated.  Kendo doesn't display it.
        // When it is a string, the sort is alphanumeric.  (So 11 comes before 9.)  Mark felt that display was better than the sort.
        "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 6
    "memberLogin" => array("title" => "User Login and Locking Status", "dateFormat" => "yyyy/MM/dd",
        "sql" => "select u.user_id, u.user_name, lastlogin, failedlogin, coalesce(failedremain, a.retrylimit), case when failedremain < 0 then 'By CU' when failedremain = 0
        then 'Failed Logins' when (forcechange = 'Y' and forceremain < 1) then 'Expired Pwd' when (userflags & 2::int = 2) and forceremain <= 0 then 'Expired Security' else '' end,
        case when forcechange = 'Y' or (userflags & 2::int = 2) then coalesce(forceremain, a.gracelimit)::char(2) else '' end, pwchange from {$Cu}user u, cuadmin a where a.cu = '$Cu' ",
        "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Last Login" => "date", "Last Failed" => "date", "Retries Remain" => "number",
          "Locked" => "string", "Force Chg in" => "string", "Last Pwd Change" => "date"),
        "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
  // 6.5
  "lockedRpt" => array("title" => "Banking Locked Out Users",
      "sql" => "select user_id, trim(user_name), case when forcechange = 'Y' and forceremain <= 0 then 'Expired password' when (userflags & 2::int=2) and forceremain <= 0 then 'Expired security'
        when failedremain = 0 then 'Too many failed logins' when failedremain < 0 then 'Locked by Credit Union' end, lastlogin, failedlogin from ${Cu}user
        where (failedremain <= 0 or (forcechange = 'Y' and forceremain <= 0) or ((userflags & 2::int = 2) and forceremain <= 0)) ",
      "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Reason" => "string", "Last Login" => "string", "Last Failed" => "string"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 7
    "crossMonth" => array("title" => "Cross Transfer Transaction Report by Month", "dateFormat" => "yyyy-MM",
        "sql" => "with temp1 as (select hdr.id, date_trunc('month', hdr.effective_date) as month, dtl.transdata::json->>'acct_source' as source, dtl.transdata::json->>'acct_dest' as dest
          from ${Cu}transhdr hdr inner join ${Cu}transdtl dtl on hdr.id = dtl.transhdr_id where hdr.accountnumber is not null and hdr.feature_code = 'TRN'),
          temp2 as (select t1.id, t1.month, sum(case when t1.source <> '' and t1.dest <> '' and split_part(t1.source, '|', 2) = split_part(t1.dest, '|', 2) then 1 else 0 end) as same,
          sum(case when t1.source <> '' and t1.dest <> '' and split_part(t1.source, '|', 2) = split_part(t1.dest, '|', 2) then 0 else 1 end) as diff from temp1 t1 group by 1,2)
          select t2.month, count(*), sum(case when t2.diff > 0 then 1 else 0 end), sum(case when t2.same > 0 then 1 else 0 end) from temp2 t2 group by 1",
        "cols" => array("Month" => "date", "Total" => "number", "Cross Transfer" => "number", "Same Account" => "number")),
    // 8
    "crossDay" => array("title" => "Cross Transfer Transaction Report by Day", "dateFormat" => "yyyy-MM-dd",
        "sql" => "with temp1 as (select hdr.id, date_trunc('day', hdr.effective_date) as month, dtl.transdata::json->>'acct_source' as source, dtl.transdata::json->>'acct_dest' as dest
          from ${Cu}transhdr hdr inner join ${Cu}transdtl dtl on hdr.id = dtl.transhdr_id where hdr.accountnumber is not null and hdr.feature_code = 'TRN'),
          temp2 as (select t1.id, t1.month, sum(case when t1.source <> '' and t1.dest <> '' and split_part(t1.source, '|', 2) = split_part(t1.dest, '|', 2) then 1 else 0 end) as same,
          sum(case when t1.source <> '' and t1.dest <> '' and split_part(t1.source, '|', 2) = split_part(t1.dest, '|', 2) then 0 else 1 end) as diff from temp1 t1 group by 1,2)
          select t2.month, count(*), sum(case when t2.diff > 0 then 1 else 0 end), sum(case when t2.same > 0 then 1 else 0 end) from temp2 t2 group by 1",
        "cols" => array("Day" => "date", "Total" => "number", "Cross Transfer" => "number", "Same Account" => "number")),
    // 9
    "memberSummary" => array("title" => "User Summary",
        "sql" => "select count(*), sum( case when (failedremain <= 0 or (forcechange = 'Y' and forceremain <= 0)) then 1 else 0 end), sum( case when egenl_flag = 'Y' then 1 else 0 end)
        from ${Cu}user where passwd <> 'NULL PASSWORD'",
        "cols" => array("Total Users" => "number", "Locked Out" => "number", "Opt-In" => "number")),
    // 10
    "billpaySettings" => array("title" => "User Bill Pay ID Settings", "reportTitle" => "User Bill Pay ID Settings",
        "sql" => "select ma.accountnumber, ma.estmnt_flag, ma.billpayid, u.email from ${Cu}memberacct ma inner join ${Cu}user u on ma.primary_user = u.user_id",
        "cols" => array("Account" => "unencoded", "Bill Pay" => "boolean", "Alt Bill Pay ID" => "number", "Primary Account Email" => "string"),
        "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "Account", "type" => "account"))),
    // 11
    "avgTransDay" => array("title" => "Average Transactions per Day", "reportTitle" => "Average Transactions per Day", "dateFormat" => "yyyy-MM-dd",
        "sql" => "with temp1 as (select min(effective_date)::timestamp, now()::timestamp, age(min(effective_date)), count(*) as count
          from ${Cu}transhdr where effective_date is not null),
        temp2 as (select date_part('day', t1.now - t1.min) as num, t1.min, t1.age, t1.count from temp1 t1)
        select coalesce(nullif(t2.num, 0), 1), t2.min, t2.count, case when t2.num = 0 then t2.count else t2.count / t2.num end from temp2 t2",
        "cols" => array("Days" => "number", "Since" => "date", "Transactions" => "number", "Daily Average" => "decimal")),
    // 12
    "avgTransMonth" => array("title" => "Average Transactions per Month", "reportTitle" => "Average Transactions per Month", "dateFormat" => "yyyy-MM-dd",
        "sql" => "with temp1 as (select min(effective_date) as min, age(min(effective_date)) as age, count(*) as count from ${Cu}transhdr where effective_date is not null),
        temp2 as (select date_part('year', t1.age) * 12 + date_part('month', t1.age) + (case when date_part('day', t1.age) > 15 then 1 else 0 end) as num, t1.min, t1.age, t1.count from temp1 t1)
        select coalesce(nullif(t2.num, 0), 1), t2.min, t2.count, case when t2.num = 0 then t2.count else t2.count::float / t2.num end from temp2 t2",
        "cols" => array("Months" => "number", "Since" => "date", "Transactions" => "number", "Monthly Average" => "decimal")),
    // 13
    "repeatingSummary" => array("title" => "Repeating Transaction Summary", "reportTitle" => "Repeating Transaction Summary",
        "sql" => "with trans as (select user_id, feature_code, txn_data::json->'txn'->>'transactioncode' as transactioncode, txn_data from cu_scheduledtxn where cu = '$Cu'
          and feature_code in ('TRN', 'ACHCOL', 'ACHPMT', 'TRNM2M', 'TRNEXT')),
        expanded as (select user_id, feature_code, transactioncode, txn_data::json->'txn'->>'from' as accountnumber,
          txn_data::json->'txn'->>'to' as tomember from trans where feature_code = 'TRN'
        union all select user_id, feature_code, transactioncode, split_part(txn_data::json->'txn'->>'from', '|', 2), txn_data::json->'txn'->>'to'
          from trans where feature_code in ('TRNM2M', 'TRNEXT', 'ACHCOL', 'ACHPMT') and txn_data::json->'txn'->>'type' in ('L2M', 'L2X', 'L2R')
        union all select user_id, feature_code, transactioncode, txn_data::json->'txn'->>'from', split_part(txn_data::json->'txn'->>'to', '|', 2)
          from trans where feature_code in ('TRNM2M', 'TRNEXT', 'ACHCOL', 'ACHPMT') and txn_data::json->'txn'->>'type' in ('X2M', 'X2L', 'R2L'))
      select count(distinct user_id), count(distinct accountnumber), count(*), coalesce(sum(case when transactioncode = 'AT' then 1 else 0 end), 0),
      coalesce(sum(case when transactioncode = 'LP' then 1 else 0 end), 0), coalesce(sum(case when accountnumber = tomember then 1 else 0 end), 0),
      coalesce(sum(case when accountnumber <> tomember then 1 else 0 end), 0) from expanded",
        "cols" => array("Users" => "number", "Accounts" => "number", "Transactions" => "number", "Transfer" => "number", "Payment" => "number", "Same Account" => "number",
          "Cross Account" => "number")),
    // 14
    "loginFailure" => array("title" => "User Login Failure Status",
        "sql" => "select u.user_id, trim(u.user_name), lastlogin, failedlogin, case when coalesce(userflags, 0) & 4::int4 = 4  then 'Email'
        when coalesce(userflags, 0) & 8::int4 = 8  then 'Challenge' when coalesce(userflags, 0) & 16::int4 = 16  then 'Password' when coalesce(userflags, 0) & 64::int4 = 64  then 'Username'
        else '' end from {$Cu}user u, cuadmin a where a.cu = '$Cu' and coalesce(userflags,0) & 92::int4 > 0",
        "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Last Login" => "date", "Last Failed" => "date", "Reason" => "string"),
        "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 15
    "memAlerts" => array("title" => "User Alerts Detail",
        "sql" => "select u.user_id, trim(u.user_name), coalesce(sq_bal_join.bal_count, 0), coalesce(sq_chk_join.chk_count, 0), coalesce(sq_loan_join.ln_count, 0),
          coalesce(sq_trnx_join.trnx_count, 0) from ${Cu}user u
          left join (select accountnumber, count(*) as bal_count FROM cu_alerts WHERE cu = '$Cu' AND (alerttype = 'B') group by accountnumber) as sq_bal_join
            on u.user_name = sq_bal_join.accountnumber
          left join (select accountnumber, count(*) as chk_count FROM cu_alerts WHERE cu = '$Cu' AND (alerttype = 'C') group by accountnumber) as sq_chk_join
            on u.user_name = sq_chk_join.accountnumber
          left join (select accountnumber, count(*) as ln_count FROM cu_alerts WHERE cu = '$Cu' AND (alerttype = 'L') group by accountnumber) as sq_loan_join
            on u.user_name = sq_loan_join.accountnumber
          left join (select accountnumber, count(*) as trnx_count FROM cu_alerts WHERE cu = '$Cu' AND (alerttype = 'T') group by accountnumber) as sq_trnx_join
            on u.user_name = sq_trnx_join.accountnumber
          where (coalesce(sq_bal_join.bal_count, 0) > 0 or coalesce(sq_chk_join.chk_count, 0) > 0 or coalesce(sq_loan_join.ln_count, 0) > 0 or coalesce(sq_trnx_join.trnx_count, 0) > 0) ",
        "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Balance Alerts" => "number", "Check Alerts" => "number", "Missed Payment Alerts" => "number", "Transaction Alerts" => "number"),
        "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 18
    "memLastLoginBatch" => array("title" => "User Last Login", "dateFormat" => "yyyy/MM/dd HH:mm:ss",
      "sql" => "select user_id, trim(user_name), lastlogin, case when lastlogin is null or trim(lastlogin) = '' then null else ((extract(epoch from current_timestamp) -
        extract(epoch from substring(lastlogin, 1, 19)::timestamp)) / 86400)::integer end, substring(yearmo from 1 for 4) || '/' || substring(yearmo from 5 for 2), email from ${Cu}user u
        left join (select accountnumber, max(yearmo) as yearmo from cusmstrack where cu = '$Cu' group by accountnumber) as s on s.accountnumber = u.user_name where passwd <> 'NULL PASSWORD'",
      "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Last Login" => "date", "Elapsed Days" => "number", "Last TXT" => "string", "Email" => "string"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 19
    "lastLogin2" => array("title" => "User Last Login Summary",
      "sql" => "select count(*), sum(case when trim(lastlogin) > '' and trim(lastlogin)::timestamp > now() - '30 days'::interval then 1 else 0 end),
        sum(case when trim(lastlogin) > '' and trim(lastlogin)::timestamp > now() - '60 days'::interval and trim(lastlogin)::timestamp < now() - '30 days'::interval then 1 else 0 end),
        sum(case when trim(lastlogin) > '' and trim(lastlogin)::timestamp > now() - '90 days'::interval and trim(lastlogin)::timestamp < now() - '60 days'::interval then 1 else 0 end),
        sum(case when trim(lastlogin) > '' and trim(lastlogin)::timestamp > now() - '120 days'::interval and trim(lastlogin)::timestamp < now() - '90 days'::interval then 1 else 0 end),
        sum(case when trim(lastlogin) > '' and trim(lastlogin)::timestamp < now() - '120 days'::interval then 1 else 0 end),
        sum(case when lastlogin is null or trim(lastlogin) = '' then 1 else 0 end) from ${Cu}user where passwd <> 'NULL PASSWORD'",
      "cols" => array("Users" => "number", "Current" => "number", "30+ Days" => "number", "60+ Days" => "number", "90+ Days" => "number", "120+ Days" => "number", "Never" => "number")),
    // 20
    "totalMemAlerts" => array("title" => "Total User Alerts Summary", "reportTitle" => "$prodname Total User Alerts Summary",
      "sql" => "select sum(b), sum(c), sum(l), sum(t) from (select count(*) as b, 0 as t, 0 as c, 0 as l from cu_alerts where cu = '$Cu' and (alerttype = 'B')
        union all select 0, count(*), 0, 0 from cu_alerts where cu = '$Cu' and (alerttype = 'T') union all select 0, 0, count(*), 0 from cu_alerts where cu = '$Cu' and (alerttype = 'C')
        union all select 0, 0, 0, count(*) from cu_alerts where cu = '$Cu' AND (alerttype = 'L')) as summary ",
      "cols" => array("Balance" => "string", "Check.0" => "number", "Missed Payment" => "number", "Transaction" => "number")),
    // 23

    // 1st account number, 2nd email member account primaryuser to userid; estmnt boolean;
    "estatementMems" => array("title" => "eStatement Users",
      "sql" => "select m.accountnumber, u.user_id, u.user_name, coalesce(trim(u.email), ''), m.estmnt_flag from ${Cu}memberacct m
        left join ${Cu}user u on m.primary_user = u.user_id",
      "cols" => array("Account" => "unencoded", "User Id" => "ignore", "User Name" => "unencoded", "Address" => "string", "eStatement" => "string"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"),
        array("name" => "Account", "type" => "account"))),
    // 26
    "repeatingTrans" => array("title" => "Repeating Transactions", "dateFormat" => "MM/dd/yyyy", "nonSqlDataFunc" => "GetRepeatingTransReport",
      "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "From.User" => "string", "To.User" => "string", "Type" => "string", "Amount" => "currency", "Next Date" => "date",
                      "Frequency" => "list", "Start Date" => "date", "Stop Date" => "date"),
      "ddl" => array(
        "Frequency" => array("OneTime" => "One Time", "FirstDayOfMonth" => "First Day of the Month", "LastDayOfMonth" => "Last Day of the Month",
          "FirstFifteenth" => "First and Fifteenth Day of the Month", "FifteenthLast" => "Fifteenth and Last Day of the Month", "Weekly" => "Weekly", "BiWeekly" => "Bi-weekly",
          "WeekDayOfMonth" => "Weekday of the Month - (e.g. 3rd Thursday)", "Monthly" => "Monthly", "Every2Months" => "Every 2 Months", "Quarterly" => "Quarterly",
          "SemiAnnually" => "Semi-Annually", "Annually" => "Annually"))
      ),
    // 27
    "memAlertsSuffix" => array("title" => "User Alerts Suffix Detail", "dateFormat" => "yyyy/MM/dd",
      "sql" => "select distinct trim(accountnumber)::bigint, accounttype, 'Balance'::char(18), lastalert, ('Less than '::varchar || to_char(notifyamt, '999999999D99'))::varchar, notifymsg
      from cu_alerts where cu = '$Cu' and (alerttype = 'B')  union all select distinct trim(accountnumber)::bigint, accounttype, case when notifytranstype = 'D' then 'Transaction (Dep)'
      when notifytranstype = 'W' then 'Transaction (With)' else 'Transaction (All)' end, lastalert, case when notifyrange = '1'
      then ('Contains ' || notifydesc || ' and $ between ' || trim(to_char(notifyamtmin, '999999D99')) || ' - ' || trim(to_char(notifyamtmax, '999999D99')))::varchar
      else ('Contains ' || notifydesc)::varchar end, notifymsg from cu_alerts where cu = '$Cu' and (alerttype = 'T')
        union all select distinct trim(accountnumber)::bigint, accounttype, 'Check'::char(18), lastalert, 'Check ' || notifychknum, notifymsg
      from cu_alerts where cu = '$Cu' and (alerttype = 'C') union all select distinct trim(accountnumber)::bigint, accounttype, 'Missed Pmt'::char(18),
      lastalert, ('Days prior ' || to_char(notifyloandaysprior,'99'))::varchar, notifymsg from cu_alerts where cu = '$Cu' AND (alerttype = 'L')  ",
    "cols" => array("Account" => "unencoded", "Suffix" => "string", "Alert Type" => "string", "Last Alert" => "datetime", "When.0" => "string", "Message" => "string"),
    "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "Account", "type" => "account"))),
    // 30
    "sincePasswordChange" => array("title" => "Days Since User Password Change Summary",
      "sql" => "select count(*), sum(case when pwchange is not null and pwchange::timestamp > now() - '30 days'::interval then 1 else 0 end),
        sum(case when pwchange is not null and pwchange::timestamp > now() - '60 days'::interval and pwchange::timestamp < now() - '30 days'::interval then 1 else 0 end),
        sum(case when pwchange is not null and pwchange::timestamp > now() - '90 days'::interval and pwchange::timestamp < now() - '60 days'::interval then 1 else 0 end),
        sum(case when pwchange is not null and pwchange::timestamp > now() - '120 days'::interval and pwchange::timestamp < now() - '90 days'::interval then 1 else 0 end),
        sum(case when pwchange is not null and pwchange::timestamp > now() - '180 days'::interval and pwchange::timestamp < now() - '120 days'::interval then 1 else 0 end),
        sum(case when pwchange is not null and pwchange::timestamp > now() - '1 year'::interval and pwchange::timestamp < now() - '180 days'::interval then 1 else 0 end),
        sum(case when pwchange is not null and pwchange::timestamp > now() - '2 years'::interval and pwchange::timestamp < now() - '1 year'::interval then 1 else 0 end),
        sum(case when pwchange is not null and pwchange::timestamp < now() - '2 years'::interval then 1 else 0 end) from ${Cu}user where passwd <> 'NULL PASSWORD' ",
      "cols" => array("Users" => "number", "<30" => "number", "30-60" => "number", "60-90" => "number", "90-120" => "number", "120-180" => "number", "120-180" => "number",
        "180-1 Yr" => "number","1-2 Yrs" => "number", "2+ Yrs" => "number")),
    // 35
    "memEmail" => array("title" => "User Email / Password Changes", "dateFormat" => "yyyy-MM-dd HH:mm:ss",
      "sql" => "select u.user_id, u.user_name, a.auditdate, a.prevemail, a.afteremail, case when a.prevpass <> a.afterpass and a.prevpass <> '' and a.afterpass <> '' then 'Changed' else '' end,
        a.name from (select trim(auditsrcuser_name) || (case when auditsrctype = 'A' then ' (admin user)' else '' end) as name,
        auditdate, user_id, coalesce(auditrecbefore::json->'user'->0->>'passwd', '') as prevpass,
        coalesce(auditrecafter::json->'user'->0->>'passwd', '') as afterpass, coalesce(auditrecbefore::json->'user'->0->>'email', '') as prevemail,
        coalesce(auditrecafter::json->'user'->0->>'email', '') as afteremail
        from ${Cu}audituser where trim(auditrecbefore) like '{\"user\":[{\"%' and trim(auditrecafter) like '{\"user\":[{\"%') a
        inner join ${Cu}user u on a.user_id = u.user_id
        where (a.prevpass <> a.afterpass and a.prevpass <> '' and a.afterpass <> '') or (a.prevemail <> a.afteremail and a.prevemail <> '' and a.afteremail <> '')",
      "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Date" => "datetime", "Old Email" => "string", "New Email" => "string", "Password" => "string", "Changed By" => "string"),
      "dataFunc" => "addLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
      // 36
      // thought it'd be cool to include whether or not the member used the service but then what do you do with folks who used it in the past and now have
      // a zero limit?  (if turning this back on, change max orderby to 3)
    "onlineDepositSettings" => array("title" => "Remote Deposit Settings", "reportTitle" => "$prodname Online Deposit Settings",
      "sql" => "select accountnumber, rdcsetting from ${Cu}memberacct where rdcsetting > 0",
      "cols" => array("Member" => "unencoded", "Deposit Limit" => "number"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "Member", "type" => "account"))),
    // 37
    // TODO: might have to put the Last Packet back in.  There is currently no equivalent column in Odyssey.
    "memLastLoginLive" => array("title" => "User Last Login", "dateFormat" => "yyyy/MM/dd",
      "sql" => "select user_id, trim(user_name), lastlogin, case when lastlogin is null or trim(lastlogin) = '' then null else ((extract(epoch from current_timestamp) -
        extract(epoch from substring(lastlogin,1,19)::timestamp)) / 86400)::integer end, substring(yearmo from 1 for 4) || '/' || substring(yearmo from 5 for 2), email from ${Cu}user u
        left join (select accountnumber, max(yearmo) as yearmo from cusmstrack where cu = '$Cu' group by accountnumber) as s on s.accountnumber = u.user_name where passwd <> 'NULL PASSWORD'",
      "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Last Login" => "date", "Elapsed Days" => "number", "Last TXT" => "string", "Email" => "string"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 38
    "emailInitialized" => array("title" => "User Email Initialized Date", "dateFormat" => "yyyy-MM-dd HH:mm:ss",
      "sql" => "select u.user_id, trim(u.user_name), a.auditdate, a.email
      from (select row_number() over (partition by user_id order by auditdate) as rown, auditdate, auditrecafter::json->'user'->0->>'email' as email, user_id
        from ${Cu}audituser where auditaction = 'U_ADD') as a
      inner join ${Cu}user u on a.user_id = u.user_id and a.rown = 1",
      "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Date" => "datetime", "New Email" => "string"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 39
    "estatementStatus" => array("title" => "eStatement Status Changes", "dateFormat" => "yyyy-MM-dd HH:mm:ss",
      "sql" => "select accountnumber, auditdate, auditrecbefore::json->'memberacct'->0->>'estmnt_flag', auditrecafter::json->'memberacct'->0->>'estmnt_flag',
        trim(auditsrcuser_name) || (case when auditsrctype = 'A' then ' (admin user)' else '' end) from ${Cu}audituser
        where trim(auditrecbefore) like '{\"memberacct\":[{\"%' and trim(auditrecafter) like '{\"memberacct\":[{\"%' and auditrecbefore::json->'memberacct'->0->>'estmnt_flag' is not null
        and auditrecafter::json->'memberacct'->0->>'estmnt_flag' is not null and auditrecbefore::json->'memberacct'->0->>'estmnt_flag' <> auditrecafter::json->'memberacct'->0->>'estmnt_flag'",
      "cols" => array("Account" => "number", "Date" => "datetime", "Old Stmt" => "string", "New Stmt" => "string", "Changed By" => "string"),
      "defaultSort" => array(array("field" => "date", "dir" => "desc"))),
    // 40
    "cuDismissible" => array("title" => "CU-Dismissible Promo Exclusions",
      "sql" => "select user_id, trim(user_name), 'Y' from ${Cu}user where coalesce(msg_tx, 0) & 8192::int4 > 0  ",
      "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Excluded" => "string"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 41
    "deepTarget" => array("title" => "Deep Target Data",
      "sql" => "select ma.accountnumber, case when coalesce(u.userflags, 0) & 32::int4 > 0 then 'Y' else 'N' end, ma.estmnt_flag,
      case when exists(select * from cusms where accountnumber = user_name and cu = '$Cu') then 'Y' else 'N' end from ${Cu}memberacct ma inner join ${Cu}user u on ma.primary_user = u.user_id
      left join cusms s on u.user_id = s.user_id",
      "cols" => array("Account" => "unencoded", "Bill Pay" => "boolean", "eStatement" => "boolean", "TXT Banking" => "boolean"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "Account", "type" => "account"))),
    // 43
    "overrideMcir" => array("title" => "Override MICR",
      "sql" => "select trim(om.accountnumber), trim( ab.description) || '-' || trim(om.accounttype), nullif(trim(ab.micraccount), ''), nullif(trim(om.rt), ''),
      nullif(trim(om.micraccount), ''), om.startcheck
        from cuovermicr om join {$Cu}accountbalance ab on (om.accountnumber = ab.accountnumber and om.accounttype = ab.accounttype) where cu = '$Cu' ",
      "cols" => array("Account" => "unencoded", "Description" => "string", "Uploaded" => "number", "R/T" => "number", "Override" => "number", "Start Check" => "number"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "Account", "type" => "account"))),
    // 44
    "termsAccepted" => array("title" => "Terms Accepted", "dateFormat" => "yyyy-MM-dd",
      "sql" => "select trim(r.accountnumber), r.user_id, u.user_name, d.docsdesc, r.responseon
                from cucmsdocs d
                inner join cucmsresponse r on d.docsid = r.docsid and d.docsresponsetype = 'T' and r.cu = '$Cu'
                left join ${Cu}user u on r.user_id = u.user_id",
      "cols" => array("Account" => "unencoded", "User Id" => "ignore", "User Name" => "unencoded", "Document" => "string", "Date" => "date"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "Account", "type" => "account"), array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 45
    "emailChangedDate" => array("title" => "Email Changed Date", "dateFormat" => "yyyy-MM-dd HH:mm:ss",
      "sql" => "select u.user_id, u.user_name, a.auditdate, a.afteremail, a.auditsrcuser_name
        from (select auditsrcuser_name, auditdate, user_id, coalesce(auditrecbefore::json->'user'->0->>'email', '') as prevemail,
        coalesce(auditrecafter::json->'user'->0->>'email', '') as afteremail
        from ${Cu}audituser where trim(auditrecbefore) like '{\"user\":[{\"%' and trim(auditrecafter) like '{\"user\":[{\"%') a inner join ${Cu}user u on a.user_id = u.user_id
        where a.prevemail <> a.afteremail and a.prevemail <> '' and a.afteremail <> ''",
      "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Date" => "datetime", "New Email" => "string", "Changed By" => "string"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 53
    "androidHistory" => array("title" => "Android Monthly User Login History",
      "sql" => $monthPivot->GetCTE() . ", " . GetLoginCTE($Cu) . " select user_id, user_name, " . $monthPivot->GetColumnSQL("logins") . " from loginsCTE a, monthPivotCTE p
        where a.yearmo = p.yearmo and hcucode = 'ADA' group by user_id, user_name",
      "cols" => $monthPivot->GetColumns(array("User Id" => "ignore", "User Name" => "unencoded")),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 54
    "androidSummary" => array("title" => "Android Monthly Login Summary", "dateFormat" => "yyyy-MM",
      "sql" => "with " . GetLoginCTE($Cu) . " select substr(yearmo, 1, 4) || '-' || substr(yearmo, 5, 2) || '-01', sum(logins), count(*), to_char(avg(logins), '999.9')::decimal from loginsCTE
        where hcucode = 'ADA' group by 1 ",
      "cols" => array("Month" => "date", "Logins" => "number", "Members" => "number", "Average / Active User" => "number")),
    // 55
    "iphoneHistory" => array("title" => "iPhone User Login History",
      "sql" => $monthPivot->GetCTE() . ", " . GetLoginCTE($Cu) . " select user_id, user_name, " . $monthPivot->GetColumnSQL("logins") . " from loginsCTE a, monthPivotCTE p
        where a.yearmo = p.yearmo and hcucode = 'APP' group by user_id, user_name",
      "cols" => $monthPivot->GetColumns(array("User Id" => "ignore", "User Name" => "unencoded")),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    // 56
    "iphoneSummary" => array("title" => "iPhone Monthly Login Summary", "dateFormat" => "yyyy-MM",
      "sql" => "with " . GetLoginCTE($Cu) . " select substr(yearmo,1,4) || '-' || substr(yearmo,5,2) || '-01', sum(logins), count(*), to_char(avg(logins), '999.9')::decimal
        from loginsCTE where hcucode = 'APP' group by 1 ",
      "cols" => array("Month" => "date", "Logins" => "number", "Members" => "number", "Average / Active User" => "number")),
    "eStatementHistory" => array("title" => "eStatement Processing History", "nonSqlDataFunc" => "GetEstatementHistory", "dateFormat" => "MM/dd/yyyy HH:mm:ss",
        "cols" => array("Count" => "number", "Load Date" => "date", "Period" => "string", "Past Cutoff" => "boolean")),
    "orphanedRecords" => array("title" => "Orphaned Users, Accounts, and Groups",
      "sql" => "select type_id, type, id, name, details from (
        select 0 as type_id, 'Users Without Member Accounts' as type, u.user_id::varchar as id, u.user_name as name, u.name as details
          from ${Cu}user u
          left join ${Cu}useraccounts ua on u.user_id = ua.user_id
          left join ${Cu}memberacctrights mar on mar.user_id=u.user_id and mar.whichright='ACCESS'
          where ua.user_id is null and (mar.allowed is null or mar.allowed is false)
        union all
        select 1, 'Users Without Groups', u.user_id::varchar, u.user_name, u.name from ${Cu}user u
          left join ${Cu}group g on u.group_id = g.group_id where g.group_id is null
        union all
        select 2, 'Member Accounts Without Users', a.accountnumber, a.accountnumber, null
          from ${Cu}memberacct a
          left join ${Cu}useraccounts ua on a.accountnumber = ua.accountnumber
          left join ${Cu}memberacctrights mar on mar.accountnumber=a.accountnumber and mar.whichright='ACCESS'
          where ua.accountnumber is null and (mar.allowed is null or mar.allowed is false)
        union all
        select 3, 'Groups Without Users', g.group_id::varchar, g.group_name, p.description from ${Cu}group g left join ${Cu}user u on g.group_id = u.group_id
          left join cu_profile p on g.profile_id = p.profile_id where u.user_id is null) temp",
      "cols" => array("type id" => "hidden", "Type" => "ignore", "id" => "ignore", "Name" => "unencoded", "&nbsp;" => "string"),
      "showOnCSV" => array("type id" => false, "Type" => true),
      "groupBy" => "type id", "groupDescription" => "Type", "groupForceSortBy" => true, "dataFunc" => "addLinksToOrphanedRecordsReport"),
    "profile" => array("title" => "Banking Profiles",  "groupBy" => "Profile Code", "groupDescription" => "Profile Description",
      "sql" => "select p.profile_code, p.description, f.description, f.limit_type, "
      . GetProfileCol("pr.amount_per_transaction") . ", ". GetProfileCol("pr.amount_per_day") . ", "
      . GetProfileCol("pr.amount_per_month") . ", ". GetProfileCol("pr.amount_per_account_per_day") . ", ". GetProfileCol("pr.count_per_day", false) . ", "
      . GetProfileCol("pr.count_per_month", false) . ", ". GetProfileCol("pr.count_per_account_per_day", false) . ", pr.confirm_required from cu_profile p
      left join cu_profilerights pr on p.profile_id = pr.profile_id
      left join cu_feature f on pr.feature_code = f.feature_code
      where p.cu = '$Cu' and f.enabled",
      "cols" => array("Profile Code" => "hidden", "Profile Description" => "ignore", "Feature" => "description", "Type" => "list", "/ Trans" => "currency",
        "/ Day.1" => "currency", "/ Month.1" => "currency", "/ Account / Day.1" => "currency",
        "/ Day.2" => "number", "/ Month.2" => "number", "/ Account / Day.2" => "number", "Confirm" => "boolean"),
      "colHeader" => array(".1" => "Type", "Amount" => "/ Account / Day.1", "Count" => "/ Account / Day.2", ".2" => "Confirm"),
      "ddl" => array("Type" => array("A" => "Access", "B" => "Dollar / Qty", "Q" => "Qty", "D" => "Dollar"))),
    "group" => array("title" => "User Groups", "groupBy" => "Group Id", "groupDescription" => "Group Title",
        "sql" => "
          select concat(g.group_name, ' - ', p.description), g.group_id, p.description, u.user_id, coalesce(u.user_name, '(No Users)'), u.is_group_primary
          from ${Cu}group g
          left join ${Cu}user u
            on u.group_id = g.group_id
          left join cu_profile p
            on p.profile_id = g.profile_id",
        "cols" => array("Group Title" => "hidden", "Group Id" => "hidden", "Profile" => "hidden", "User Id" => "ignore",
                        "User Name" => "unencoded", "Primary Owner" => "boolean"),
        "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),
    "lockedMembers" => array("title" => "Restricted Member Accounts",
      "sql" => "select m.accountnumber, t.text from ${Cu}memberacct m
        inner join (values ('Locked', 'L'), ('Read Only', 'R')) as t (text, value) on m.restrictions = t.value",
      "cols" => array("Account" => "unencoded", "Status" => "string"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "Account", "type" => "account"))),
    "pendingMicroDeposits" => array("title" => "Pending and Locked Micro Deposits", "groupBy" => "Type",
      "sql" => "with expanded as (select remote_info::json->'rdfi'->>'routing' as routing, remote_info::json->'rdfi'->>'account' as account,
          remote_info::json->'rdfi'->>'type' as type, remote_info::json->'verify'->>'pending_date' as pending_date, remote_info::json->'verify'->>'tries' as tries,
          remote_info::json->'verify'->>'verified' as verified, display_name, status, id, user_id from ${Cu}extaccount)
        select t.user_id, u.user_name, t.type, t.display_name, t.account_info, to_timestamp(t.pending_date::numeric)
          from (select e.user_id::int, 'Locked' as type, e.display_name, e.routing || ' / ' || e.account as account_info, e.pending_date from expanded e
          where e.status = 'l' and e.tries is not null and e.tries::int = 0 and e.verified is null
        union all select f.user_id::int, 'Pending', f.display_name, f.routing || ' / ' || f.account, f.pending_date
          from expanded f where f.status = 'p' and f.tries is not null and f.tries::int > 0 and f.verified is null) t inner join ${Cu}user u on t.user_id = u.user_id",
      "cols" => array("User Id" => "ignore", "User Name" => "unencoded", "Type" => "hidden", "Name" => "string", "Account Info" => "string", "Pending Date" => "datetime"),
      "dataFunc" => "AddLinks", "dataFuncVariables" => array(array("name" => "User Name", "id" => "User Id", "type" => "user"))),

      "extAcctReport" => array(
       "title" => "External Account Status",
       "sql" => "select userName, Status, displayName, account, Routing, pendingDate from (
           with expanded as (
             select user_id, display_name,
               case
                 when status = '0' then 'Pending (0)'
                 when status = 'p' then 'Pending'
                 when status = 'a' then 'Available'
                 when status = 'l' then 'Locked'
                 when status = 'i' then 'Inactive'
                 else 'Unknown'
               end
               as status,
               remote_info::json->'rdfi'->>'routing' as routing,
               remote_info::json->'rdfi'->>'account' as account,
               remote_info::json->'verify'->>'pending_date' as pending_date
           from {$Cu}extaccount
           where type='EXT'
         )
         select
           u.user_name as userName,
           t.status as Status,
           t.display_name as displayName,
           t.account as account,
           t.routing as Routing,
           to_timestamp(t.pending_date::numeric) as pendingDate
         from (select * from expanded e) t
         inner join {$Cu}user u on t.user_id = u.user_id
       ) t
      limit 30 offset 0",
       "cols" => array (
         "User Name" => "string",
         "Status" => "string",
         "Display Name" => "string",
         "Account" => "string",
         "Routing Number" => "string",
         "Pending Date" => "datetime"
      )
    )
  );

  return $reportList;
}


/**
 * function GetEstatementHistory($Cu, $dbh)
 * This gets the estatement history from the summary.log file.
 *
 * @param $Cu -- the credit union
 * @param $dbh -- the database connection.  This is not used in this callback but it is part of the parameter signature for #1315's benefit.
 * @return array of sample data to function exactly like if data came from a SQL.
 */
function GetEstatementHistory($Cu, $dbh) {
  $returnArray = array();

  try {
      $SMRYLOG = "/home/" . strtolower($Cu) . "/stmnt/summary.log";
      $date = (strftime ("%Y:%m:%d", time()));
      list($year, $month, $day) = explode(":",$date);
      $timestamp = mktime (0,0,0,$month-25,$day,$year);
      $CUTOFF = (strftime ("%Y%m%d", $timestamp));
      $returnData = array();

      if (is_readable("$SMRYLOG")) {
        $lines = file ($SMRYLOG);

        // Date is entered into summary file in Mountain Timezone.  Change this to the date of the credit union.
        $thisTz = "US/Mountain";
        $cuTz = GetCreditUnionTimezone($dbh, $Cu);
        $changeTz = false;

        if ($thisTz !== $cuTz) { // Don't need to do anything if it is already in mountain.
          $changeTz = true;
          $thisTz = new DateTimeZone($thisTz);
          $cuTz = new DateTimeZone($cuTz);
        }

        foreach ($lines as $line_num => $line) {
          $f = explode("\t",$line);
          $lddate = str_replace("-","",substr($f[0],0,10));
          $ldcount = (intval($f[5]) - intval($f[6]));
          $ldstat = $f[4];
          $ldper = $f[8];

          if ( $ldcount != 0 && $ldstat != 'Abort') {

            $strDate = $f[0];
            if ($changeTz) {
              $date = DateTime::createFromFormat("Y-m-d H:i:s|", $strDate, $thisTz);

              if ($date === false) {
                // Should never get here but if it does, do not break the page.  Report the error.
                global $logger;
                $logger->error("Estatement report: date isn't valid: $strDate");
                continue;
              } else {
                $date->setTimezone($cuTz);
                $strDate = $date->format("Y-m-d H:i:s");
              }
            }

            $returnData [] = array($f[2], $f[3], $ldcount, $strDate, $ldper, $lddate >= $CUTOFF ? "Y" : "N");
          }
        }
      }

      // Sort by the third column (period in YYYYMM) descending and then by the fourth column (period type indicator.)
      usort($returnData, function($a, $b) {
        $cmp = strcasecmp($b [0], $a [0]); // Third column descending
        $cmp = $cmp != 0 ? $cmp : strcasecmp($b [1], $a [1]); // Fourth column descending

        return $cmp;
      });

      // Remove the columns used to sort.
      for($i = 0, $count = count($returnData); $i != $count; $i++) {
        $row = $returnData[$i];
        array_shift($row);
        array_shift($row);
        $returnData[$i] = $row;
      }

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


  return $returnArray;
}

/**
 * function getLoginCTE($Cu)
 * Get the SQL for getting the loginCTE that replaces the culogtrack table.
 *
 * @return SQL snippet for getting the login CTE.
 */
function GetLoginCTE($Cu) {
  return "loginsCTE as (select user_id, to_char(login_dt,'YYYYMM') as yearmo, hcucode, max(user_name) as user_name, count(*) as logins from {$Cu}userlogins where status = 0 group by 1,2,3)";
}

/**
 * function GetRepeatingTransReport($Cu, $dbh)
 * Gets the repeating transaction report.  Modified slightly from userSupportScheduledTransactions.prg.
 *
 * @param $Cu -- the credit union
 * @param integer $dbh -- the database connection.
 * @return array -- Array of the columns in the report.  Instead of also calling "addLinks", add that to this function.
 */
function GetRepeatingTransReport($Cu, $dbh) {
  global $MC;
  try {
    $intervalList = TxIntervalList($MC);
    $intervalLookup = array();
    foreach($intervalList as $intervalRecord) {
      $intervalLookup[$intervalRecord["value"]] = $intervalRecord["text"];
    }
    unset($intervalList);

    // Then once the list of the accounts is retrieved from the primary user, then the rights for the subaccounts are retrieved from the actual user.
    $subqueryTXN = "select ss.*, case when strpos(ss.fromkey, '|') = 0 then cast(ss.fromkey as text)
      when coalesce(strpos(ss.fromsuffix, '@'), '0') <> 0 then trim(split_part(fromkey, '|', 1)) || trim(ss.frommember)
        || trim(ss.fromsuffix) || coalesce(nullif(split_part(fromkey, '|', 4), ''), '0')
      else trim(split_part(fromkey, '|', 1)) || trim(split_part(fromkey, '|', 2)) || trim(split_part(fromkey, '|', 3)) || coalesce(nullif(split_part(fromkey, '|', 4), ''), '0') end
        as fromkeya,
      case when strpos(ss.tokey, '|') = 0 then cast(ss.tokey as text)
      when coalesce(strpos(ss.tosuffix, '@'), 0) <> 0 then trim(split_part(tokey, '|', 1)) || trim(ss.tomember) || trim(ss.tosuffix) || coalesce(nullif(split_part(tokey, '|', 4), ''), '0')
      when ss.code not in ('XA', 'XP') then trim(split_part(tokey, '|', 1)) || trim(split_part(tokey, '|', 2)) || trim(split_part(tokey, '|', 3)) ||
      coalesce(nullif(split_part(tokey, '|', 4), ''), '0')
      else case when trim(split_part(tokey, '|', 1)) = 'D' then 'T' else 'P' end || frommember || trim(split_part(tokey, '|', 3)) || '#' || trim(split_part(tokey, '|', 2))
        || coalesce(nullif(split_part(tokey, '|', 4), ''), '0') end as tokeya
      from (
        select s.id, u.user_name, s.feature_code, f.description as feature, s.create_date, s.start_date, s.end_date, s.next_trigger_date, s.status, s.repeating_parameters, s.txn_data,
        s.failure_count,
        replace(s.txn_data::json->'txn'->>'from', 'C|', 'L|') as fromkey,
        replace(s.txn_data::json->'txn'->>'to', 'C|', 'L|') as tokey,
        s.txn_data::json->'txn'->>'transactioncode' as code,
        s.txn_data::json->'txn'->>'frommember' as frommember, s.txn_data::json->'txn'->>'fromsuffix' as fromsuffix, s.txn_data::json->'txn'->>'tomember' as tomember,
        s.txn_data::json->'txn'->>'tosuffix' as tosuffix, s.user_id, s.txn_data::json->'txn'->>'amount' as amount
        from cu_scheduledtxn s
        inner join ${Cu}user u on s.user_id = u.user_id
        inner join cu_feature f on s.feature_code = f.feature_code and s.cu = '$Cu' and f.enabled and approved_status = 10
      ) ss";

    $subqueryALXB = "select 'D' || trim(accountnumber) || trim(accounttype) || certnumber as key, may_deposit, may_withdraw,
      accountnumber, accounttype, certnumber, description
          from ${Cu}accountbalance
          union all
          select 'T' || trim(accountnumber) || trim(accounttype) || '#' || trim(tomember) || '0' as key, true, false, tomember, accounttype, 0, description
          from ${Cu}crossaccounts where deposittype not in ('L', 'C')
          union all
          select 'L' || trim(accountnumber) || trim(loannumber) || '0' as key, may_payment, may_addon, accountnumber, loannumber, 0, description
         from ${Cu}loanbalance
         union all
         select 'P' || trim(accountnumber) || trim(accounttype) || '#' || trim(tomember) || '0' as key, false, false, tomember, accounttype, 0, description
         from ${Cu}crossaccounts where deposittype in ('L', 'C')";

    $sql = "select txn.*, alxbfrom.description as from_desc, alxbfrom.accountnumber as from_acct, alxbfrom.accounttype as from_accounttype,
          alxbfrom.certnumber as from_certnumber, alxbto.description as to_desc, alxbto.accountnumber as to_acct,
          alxbto.accounttype as to_accounttype, alxbto.certnumber as to_certnumber from ($subqueryTXN) txn
          left join ($subqueryALXB) as alxbfrom on txn.fromkeya::text = alxbfrom.key::text
          left join ($subqueryALXB) as alxbto on txn.tokeya::text = alxbto.key::text
          order by txn.next_trigger_date";

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

    $achcols = array();
    $achpmts = array();
    $exts = array();

    for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
          switch($row["feature_code"]) {
            case "ACHCOL":
              $achcols[] = $row["id"];
              break;
            case "ACHPMT":
              $achpmts[] = $row["id"];
              break;
            case "TRNEXT":
            case "TRNM2M":
              $exts[] = $row["id"];
              break;
          }

      $repeatingTransfers[] = $row;
    }

        $sql = array();
        $achMap = array();
        if (count($achcols) > 0) {
          $sql[] = "select s.id as data_id, ap.ach_name, ap.display_name, ap.address, ap.dfi_data from cu_scheduledtxn s
               inner join ${Cu}achpartner ap on s.feature_code = 'ACHCOL' and cast(s.txn_data::json->'txn'->>'from' as int) = ap.id where s.id in (" . implode(", ", $achcols) . ")";
        }
        if (count($achpmts) > 0) {
          $sql[] = "select s.id as data_id, ap.ach_name, ap.display_name, ap.address, ap.dfi_data from cu_scheduledtxn s
               inner join ${Cu}achpartner ap on s.feature_code = 'ACHPMT' and cast(s.txn_data::json->'txn'->>'to' as int) = ap.id where s.id in (" . implode(", ", $achpmts) . ")";
        }

        if (count($sql) > 0) {
          $sql = implode(" union all ", $sql);
          $sth = db_query($sql, $dbh);
          if (!$sth) {
            throw new exception("ach map query failed.", 307);
          }
          for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
            $row["dfi_data"] = HCU_JsonDecode($row["dfi_data"]);
            $row["address"] = HCU_JsonDecode($row["address"]);

            $achMap[$row["data_id"]] = $row;
          }
        }

        $sql = array();
        $extMap = array();
        if (count($exts) > 0) {
          $sql[] = "select s.id as data_id, 'from' as dir, ea.display_name, ea.remote_info from cu_scheduledtxn s
              inner join ${Cu}extaccount ea on s.feature_code in ('TRNEXT') and s.txn_data::json->'txn'->>'type' in ('X2L') and cast(s.txn_data::json->'txn'->>'from' as int) = ea.id
              and ea.type= 'EXT' where s.id in (" . implode(", ", $exts) . ")"; // Member-to-member can only come from the local account.
          $sql[] = "select s.id as data_id, 'to', ea.display_name, ea.remote_info from cu_scheduledtxn s
              inner join ${Cu}extaccount ea on s.feature_code in ('TRNEXT', 'TRNM2M') and s.txn_data::json->'txn'->>'type' in ('L2X', 'L2M') and replace(s.feature_code, 'TRN', '') = ea.type
              and cast(s.txn_data::json->'txn'->>'to' as int) = ea.id where s.id in (" . implode(", ", $exts) . ")";
        }

        if (count($sql) > 0) {
          $sql = implode(" union all ", $sql);
          $sth = db_query($sql, $dbh);
          if (!$sth) {
            throw new exception("ext map query failed.", 308);
          }
          for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
            $row["remote_info"] = HCU_JsonDecode($row["remote_info"]);

            $extMap[$row["data_id"]] = $row;
          }
        }

    $mask = GetMask($dbh, $Cu, false)["data"];
    for($i = 0, $iCount = count($repeatingTransfers); $i != $iCount; $i++) {
      $row = $repeatingTransfers[$i];

      $repeating = isset($row["repeating_parameters"]) ? trim($row["repeating_parameters"]) : "";
      if ($repeating != "") {
        $repeating = HCU_JsonDecode($repeating);
        if (!is_array($repeating)) {
          throw new exception("Repeating is malformed!", 102);
        }
        if (!HCU_array_key_exists("interval", $repeating)) {
          throw new exception("Repeating is malformed!", 103);
        }
        $row["interval"] = $repeating["interval"];
        $row["intervalText"] = HCU_array_key_exists($row["interval"], $intervalLookup) ? $intervalLookup[$row["interval"]] : "";
      }
      unset($row["repeating_parameters"]);

      $txnData = isset($row["txn_data"]) ? trim($row["txn_data"]) : "";
      $flagset3 = GetFlagsetValue('CU3_PREPEND_MBR_DESC');
      if ($txnData != "") {
        $txnData = HCU_JsonDecode($txnData);
        if (!is_array($txnData)) {
          throw new exception("TxnData is malformed.", 104);
        }

        // Truncated version of online banking activity templates.  Normally, kendo takes care of the entities but I am turning encoded off on the column to allow for <br>.
        switch($row["feature_code"]) {
          case "TRN":
            $from = getAccountDescription($dbh, $Cu, $row["from_acct"], $row["from_desc"], $row["from_accounttype"], "", $flagset3, $row["from_certnumber"]);
            $to = getAccountDescription($dbh, $Cu, $row["to_acct"], $row["to_desc"], $row["to_accounttype"], "", $flagset3, $row["to_certnumber"]);
            break;
          case "ACHCOL":
            $partnerRow = $achMap[$row["id"]];
            $routing = trim($partnerRow["dfi_data"]["dfi_routing"]);
            $account = trim($partnerRow["dfi_data"]["dfi_account"]);
            $from = $routing . " / " . ApplyMask($account, $mask);
            $to = getAccountDescription($dbh, $Cu, $row["to_acct"], $row["to_desc"], $row["to_accounttype"], "", $flagset3, $row["to_certnumber"]);
            break;
          case "ACHPMT":
            $partnerRow = $achMap[$row["id"]];
            $routing = trim($partnerRow["dfi_data"]["dfi_routing"]);
            $account = trim($partnerRow["dfi_data"]["dfi_account"]);
            $to = $routing . " / " . ApplyMask($account, $mask);
            $from = getAccountDescription($dbh, $Cu, $row["from_acct"], $row["from_desc"], $row["from_accounttype"], "", $flagset3, $row["from_certnumber"]);
            break;
          case "TRNM2M":
            $extAccount = $extMap[$row["id"]];
            $account = trim($extAccount["remote_info"]["rdfi"]["account"]);
            $type = intval($extAccount["remote_info"]["rdfi"]["type"]);
            $type = $type == 10 ? "Checking" : "Savings";
            $to = ApplyMask($account, $mask) . " / $type";
            $from = getAccountDescription($dbh, $Cu, $row["from_acct"], $row["from_desc"], $row["from_accounttype"], "", $flagset3, $row["from_certnumber"]);
            break;
          case "TRNEXT":
            $extAccount = $extMap[$row["id"]];
            $fromLabel = $extAccount["dir"] == "from" ? "ACH From Account" : "From";
            $toLabel = $extAccount["dir"] == "to" ? "ACH To Account" : "To";

            if ($extAccount["dir"] == "from") {
                $routing = trim($extAccount["remote_info"]["rdfi"]["routing"]);
                $account = trim($extAccount["remote_info"]["rdfi"]["account"]);
                $from = $routing . " / " . ApplyMask($account, $mask);
                $to = getAccountDescription($dbh, $Cu, $row["to_acct"], $row["to_desc"], $row["to_accounttype"], "", $flagset3, $row["to_certnumber"]);
            } else {
                $from = getAccountDescription($dbh, $Cu, $row["from_acct"], $row["from_desc"], $row["from_accounttype"], "", $flagset3, $row["from_certnumber"]);
                $routing = trim($extAccount["remote_info"]["rdfi"]["routing"]);
                $account = trim($extAccount["remote_info"]["rdfi"]["account"]);
                $to = $routing . " / " . ApplyMask($account, $mask);
            }
            break;

          default:
        }
      }
      unset($row["txn_data"]);

      $payload = readUserSearch($dbh, $Cu, array("a" => array("id" => $row["user_id"])));
      $userLink = "<a href='main.prg?ft=22&payload=" . urlencode($payload["encryption"]) . "'>" . htmlentities($row["user_name"]) . "</a>";
      $newRow = array($row["user_id"], $userLink, $from, $to, $row["feature"], $row["amount"], $row["next_trigger_date"], $row["interval"], $row["start_date"], $row["end_date"]);
      $repeatingTransfers[$i] = $newRow;
    }
    $returnArray = array("data" => $repeatingTransfers, "status" => "000", "error" => "");
  }
  catch(exception $e) {
    $returnArray = array("data" => array(), "status" => $e->getCode(), "error" => $e->getMessage());
  }
  return $returnArray;
}

/**
 * function GetProfileCol($col, $isAmount = true)
 * This returns the SQL per amount or count column in the SQL.
 *
 * @param $col -- the column to encapulate in the SQL.
 * @param $isAmount -- if true, then use the limit for the amount.  If false , use the limit for the count.
 * @return nullif clause for the column.  If the value in there is the limit then set it to null.  Essentially don't show the value.
 */
function GetProfileCol($col, $isAmount = true) {
  return "nullif($col, " . ($isAmount ? FEATURE_LIMIT_MAX_AMOUNT : FEATURE_LIMIT_MAX_COUNT) . ")";
}

/**
 * function AddLinksToOrphanedRecordsReport($Cu, $dbh, &$dataRecord, $unused)
 * This is a callback function specific to the "orphaned records" report.  The name of this function is referenced in the "dataFunc" attribute.
 *
 * @param $Cu -- the credit union
 * @param $dbh -- the database connection
 * @param $dataRecord -- the record returned from the SQL specified above.
 */
function AddLinksToOrphanedRecordsReport($Cu, $dbh, &$dataRecord, $unused) {
  switch($dataRecord["typeId"]) {
    case 0:
    case 1:
      $payload = readUserSearch($dbh, $Cu, array("a" => array("id" => $dataRecord["id"])));
      $dataRecord["name"] = "<a href='main.prg?ft=22&payload=" . urlencode($payload["encryption"]) . "'>" . htmlentities($dataRecord["name"]) . "</a>";
      break;
    case 2:
      $payload = MemberSelect(null, $dbh, array("cu_table" => $Cu, "cu_code" => $Cu, "m_account" => $dataRecord["name"]));
      $payload = MemberEncrypt(null, $Cu, $payload);
      $dataRecord["name"] = "<a href='main.prg?ft=103101&payload=" . urlencode($payload) . "'>" . htmlentities($dataRecord["name"]) . "</a>";
      break;
    // With the current changes, g_id is now needed.
    case 3:
      $payload = GroupSelect(null, $dbh, array("cu_table" => $Cu, "cu_code" => $Cu, "g_name" => $dataRecord["name"], "g_id" => $dataRecord["id"]));
      $payload = GroupEncrypt(null, $Cu, $payload);
      $dataRecord["name"] = "<a href='main.prg?ft=102101&payload=" . urlencode($payload) . "'>" . htmlentities($dataRecord["name"]) . "</a>";
      break;
  }
}

/**
 * function addLinks($Cu, $dbh, &$dataRecord, $linkAssoc)
 * This is a callback function for a lot of reports to add links to the user, account, and group hubs when needed.  The name of this function is referenced in the "dataFunc" attribute.
 *
 * @param $Cu -- the credit union
 * @param $dbh -- the database connection
 * @param $dataRecord -- the data record to modify.  (This function is called once per record.)
 * @param $linkAssoc -- parameters for this function.  Defined as array("id" => $id, "name" => $name, "type" => $type).  Type is either "user", "account", or "group."
 */
function AddLinks($Cu, $dbh, &$dataRecord, $linkAssoc) {
  foreach($linkAssoc as $colArray) {
    $name = GetCamelCase($colArray["name"]);
    $id = HCU_array_key_exists("id", $colArray) ? GetCamelCase($colArray["id"]) : 0;
    switch($colArray["type"]) {
      case "user":
        if ($dataRecord[$id] != "") {
          $payload = readUserSearch($dbh, $Cu, array("a" => array("id" => $dataRecord[$id])));
          $dataRecord[$name] = "<a href='main.prg?ft=22&payload=" . urlencode($payload["encryption"]) . "'>" . htmlentities($dataRecord[$name]) . "</a>";
        }
        break;
      case "account":
        if ($dataRecord[$name] != "") {
          $payload = MemberSelect(null, $dbh, array("cu_table" => $Cu, "cu_code" => $Cu, "m_account" => $dataRecord[$name]));
          $payload = MemberEncrypt(null, $Cu, $payload);
          $dataRecord[$name] = "<a href='main.prg?ft=103101&payload=" . urlencode($payload) . "'>" . htmlentities($dataRecord[$name]) . "</a>";
        }
        break;
      // Currently, this path is not used but was probably added for completeness.
      // With the current changes, g_id is now needed.
      case "group":
        if ($dataRecord[$name] != "") {
          $payload = GroupSelect(null, $dbh, array("cu_table" => $Cu, "cu_code" => $Cu, "g_name" => $dataRecord[$name], "g_id" => $dataRecord[$id]));
          $payload = GroupEncrypt(null, $Cu, $payload);
          $dataRecord[$name] = "<a href='main.prg?ft=102101&payload=" . urlencode($payload) . "'>" . htmlentities($dataRecord[$name]) . "</a>";
        }
        break;
    }
  }
}

/**
 * function GetReportMenu($live)
 * This gets the report menu in a format:
 *
 * array($menuTitle => array($reportKey, ...), ...)
 *
 * $menuTitle -- The text to display at the top of the menu group.
 * $reportKey -- Refers to the $reportKey in the function getReportList().  The link will show the title in the report record and will go to that report when clicked on.
 *
 * @param $live -- Live CUs have a slightly different report list.  @todo: put the switches back in when Odyssey supports batch clients.
 * @return @array the report menu with the attributes above.
 */
function GetReportMenu($live) {

  $liveBatch = $live == "Y" ? "Live" : "Batch";

  return [
    "Android Login Reports" => [
      "androidHistory",
      "androidSummary"
    ],
    "iPhone Login Reports" => [
      "iphoneHistory",
      "iphoneSummary"
    ],
    "User Login Reports" => [
      "loginHistory",
      "lockedRpt",
      "loginSummary",
      "loginTracking",
      "memberSummary",
      "memLastLogin$liveBatch",
      "lastLogin2",
      "loginFailure",
      "sincePasswordChange"
    ],
    "User Settings Reports" => [
      "emailStatement",
      "memberLogin",
      "billpaySettings",
      "memEmail",
      "emailInitialized",
      "emailChangedDate",
      "estatementStatus",
      "overrideMcir",
      "cuDismissible",
      "deepTarget",
      "termsAccepted",
      "onlineDepositSettings"
    ],
    "Repeating Transaction Reports" => [
      "repeatingTrans",
      "repeatingSummary"
    ],
    "Online Transaction Reports" => [
      "transactionMonth",
      "transactionDay",
      "crossMonth",
      "crossDay",
      "avgTransMonth",
      "avgTransDay"
    ],
    "User Alerts" => [
      "totalMemAlerts",
      "memAlerts",
      "memAlertsSuffix"
    ],
    "eStatement Reports" => [
      "eStatementHistory",
      "estatementMems"
    ],
    "Profile, Group, and User Reports" => [
      "profile",
      "group",
      "orphanedRecords",
      "pendingMicroDeposits",
      "lockedMembers",
      "extAcctReport"
    ]
  ];

}

// Data layer
// **************************************

/**
 * function ReadReport($Cu, $dbh, $reportList, $defaultDateFormat)
 * Returns the data needed from the parameters for the report:
 *
 * "report" -- the report to read.  Refers to the $reportKey in the function getReportList().  The frontend sends the variable down as a javascript literal.
 * "initial" -- {true/false}  If initial, then also figure out the boolean DDLs and send those to the frontend to recreate the grid with those parameters.  (There is no re-read.)
 * "limit" -- How many records to return.
 * "offset" -- Where to start in the resultset.
 * "newFilter" -- {true/false}  If newFilter, then also find out the count of the resultset.  Also, ignore the offset.  It will get the first page.
 * "sort" -- This is a json encoded string of the current sort in the grid.
 * "filter" -- This is a json encoded string of the current filter in the grid.
 * "printerFriendly" -- printer friendly doesn't have paging.
 *
 * @param $Cu -- the credit union
 * @param $dbh -- the database connection
 * @param $reportList -- the reportList from the getReportList() above.
 * @param $defaultDateFormat -- This is what the date will be formatted as.
 */
function ReadReport($Cu, $dbh, $reportList, $defaultDateFormat) {
  $parameters = array("a" => array("report" => "", "initial" => "", "limit" => "", "offset" => "", "newFilter" => "", "sort" => "", "filter" => "", "printerFriendly" => ""));
  $string = array("filter" => HCUFILTER_INPUT_STRING);
  HCU_ImportVars($parameters, "a", array("report" => $string, "initial" => $string, "limit" => $string, "offset" => $string, "newFilter" => $string, "sort" => $string,
    "filter" => $string, "printerFriendly" => $string));

  $report = HCU_array_key_exists("report", $parameters["a"]) ? trim($parameters["a"]["report"]) : "";
  $initial = HCU_array_key_exists("initial", $parameters["a"]) ? trim($parameters["a"]["initial"]) == "true" : false;
  $limit = HCU_array_key_exists("limit", $parameters["a"]) ? intval($parameters["a"]["limit"]) : -1;
  $offset = HCU_array_key_exists("offset", $parameters["a"]) ? intval($parameters["a"]["offset"]) : 0;
  $newFilter = HCU_array_key_exists("newFilter", $parameters["a"]) ? trim($parameters["a"]["newFilter"]) == "true" : false;
  $sort = HCU_array_key_exists("sort", $parameters["a"]) ? trim($parameters["a"]["sort"]) : "";
  $filter = HCU_array_key_exists("filter", $parameters["a"]) ? trim($parameters["a"]["filter"]) : "";
  $printerFriendly = HCU_array_key_exists("printerFriendly", $parameters["a"]) ? trim($parameters["a"]["printerFriendly"]) == "true" : false;

  $includeCount = !$printerFriendly && $newFilter;
  $limit = $printerFriendly ? -1 : $limit;
  $offset = $printerFriendly || $newFilter ? 0 : $offset;

  $returnArray = ReadReportData($Cu, $reportList[$report], $dbh, $limit, $offset, $includeCount, false, $initial, false, $sort, $filter, $report, "cu_reports");

  header('Content-type: application/json');
  print HCU_JsonEncode($returnArray);
}

/**
 * function GenerateCSV($dbh, $reportList, $defaultDateFormat)
 * Returns the CSV needed from the parameters for the report:
 *
 * "report" -- the report to read.  Refers to the $reportKey in the function getReportList().  The frontend sends the variable down as a javascript literal.
 * "sort" -- This is a json encoded string of the current sort in the grid.
 * "filter" -- This is a json encoded string of the current filter in the grid.
 * "title" -- This is an overwrite for the filename returned.  Otherwise, it is the $title attribute in the getReportList() function.
 *
 * @param $Cu -- the credit union
 * @param $dbh -- the database connection
 * @param $reportList -- the reportList from the getReportList() above.
 * @param $defaultDateFormat -- This is what the date will be formatted as.
 */
function GenerateCSV($Cu, $dbh, $reportList, $defaultDateFormat) {
  $parameters = array("a" => array("report" => "", "sort" => "", "filter" => "", "title" => ""));
  $string = array("filter" => HCUFILTER_INPUT_STRING);
  HCU_ImportVars($parameters, "a", array("report" => $string, "sort" => $string, "filter" => $string, "title" => $string));

  $report = HCU_array_key_exists("report", $parameters["a"]) ? trim($parameters["a"]["report"]) : "";
  $sort = HCU_array_key_exists("sort", $parameters["a"]) ? trim($parameters["a"]["sort"]) : "";
  $filter = HCU_array_key_exists("filter", $parameters["a"]) ? trim($parameters["a"]["filter"]) : "";
  $title = HCU_array_key_exists("title", $parameters["a"]) ? trim($parameters["a"]["title"]) : "";

  $reportValues = ReadReportData($Cu, $reportList[$report], $dbh, -1, 0, true, true, false, false, $sort, $filter, $report, "cu_reports");
  $reportData = $reportValues["reportData"]["data"];

  printCSVData($reportData, $report, $title, $reportList, $defaultDateFormat);
}

/**
 * function printReportListPage($reportList, $self, $report, $reportNotFound)
 * Prints out the page for the report list.
 *
 * @param $reportList -- the reportList in the function getReportList().
 * @param $self -- the URL of this script.
 * @param $report -- the $reportKey in the function getReportList().
 * @param $reportNotFound -- {true/false}  If the user typed in the wrong report in the URL, then there will be a info message and the report list will still show up.
 * @param $live -- Live CUs have a slightly different report list than batch CUs.  Odyssey supports only live CUs so far.
 */
function printReportListPage($reportList, $self, $report, $reportNotFound, $live) { ?>
  <div class="container-fluid">
    <?php if ($reportNotFound) { ?>
    <div class=" k-info-colored">
      Report "<?php echo $report; ?>" not found.  Please select from the list below.
    </div>
    <?php } ?>

    <div id="cuReportList">

      <?php echo compileMenu(getReportMenu($live), $reportList, $self); ?>

      <?php if (false) { // PHIL SAYS NO #996 ?>
      <div>
        <h2 class='primary'>Web Statistic Reports</h2>
        <div class="row">
          <div class="col-xs-6">
            <a href='/admbin/main.prg/WEBLOG?ft=84' target="_blank"><span class='reportTitle'>Statistics <i class="fa fa-external-link"></i></span></a>
          </div>

          <div class="col-xs-6">
            <a href='/admbin/main.prg/WEBLOG?ft=84&report=C' target="_blank"><span class='reportTitle'>Current Month MTD Statistics <i class="fa fa-external-link"></i></span></a>
          </div>

          <div class="col-xs-6">
            <a href='/admbin/main.prg/WEBLOG?ft=84&report=P' target="_blank"><span class='reportTitle'>Prior Month Statistics <i class="fa fa-external-link"></i></span></a>
          </div>
        </div>
      </div>
      <?php } ?>

    </div>
  </div>
<?php }

/**
 * function PrintReportPage($reportList, $csv, $report, $prodname, $title, $self)
 * Prints out the page for a report that is found.
 *
 * @param $reportList -- the reportList in getReportList().
 * @param $csv -- If it is print, then do a printerFriendly version.
 * @param $report -- the $reportKey in the function getReportList().
 * @param $prodname -- the prodname
 * @param $title -- the title of the report.
 * @param $self -- the URL of this script.  (&report=$report is appended for all calls.)
 */
function PrintReportPage($reportList, $report, $prodname, $title, $self) {
  $reportSelf = "$self&report=$report";
  $printURL = "$reportSelf&csv=print";
  ?>

  <style>
    /* Page Template for the exported PDF */
    .pageTemplate {
      font-family: "DejaVu Sans", "Arial", sans-serif;
      position: absolute;
      width: 100%;
      height: 100%;
      top: 0;
      left: 0;
    }
    .pageTemplate .header {
      position: absolute;
      top: 30px;
      left: 30px;
      right: 30px;
      border-bottom: 1px solid #888;
      color: #888;
    }
    .pageTemplate .footer {
      position: absolute;
      bottom: 30px;
      left: 30px;
      right: 30px;
      border-top: 1px solid #888;
      text-align: center;
      color: #888;
    }
    .pageTemplate .watermark {
      font-weight: bold;
      font-size: 400%;
      text-align: center;
      margin-top: 30%;
      color: #aaaaaa;
      opacity: 0.1;
      transform: rotate(-35deg) scale(1.7, 1.5);
    }
  </style>

  <script type="text/javascript">

  <?php
  getShowWaitFunctions();
  GetIsFilterChanged();

  $pageSize    = 30;
  $pagesToShow = 10;

  InitReport($reportSelf, $report, $reportList, getDefaultDateFormat(), "true", $pageSize, false, "", "", "cu_reports"); ?>

  var pageVariables = { <?php echo "skip: 0, page: 1, count: -1, numPages: -1, pageSize: " . $pageSize . ", pagesToShow: " . $pagesToShow ?> };

  function ShowNote() {
      var notification = $("#notification").kendoNotification({
      position: {
          pinned: true,
          top: 118,
          left: 128
      },
      show: function () {
          notificationIsShown = true;
      },
      hide: function () {
          notificationIsShown = false;
      }
      }).data("kendoNotification");

      var notifificationIsShown = false;
      var infoLine = "";
      notification.info(infoLine);
  }

  $(document).ready(function () {
    $.homecuValidator.setup({formValidate:'cuReportingDiv', formStatusField: 'formValidateMainDiv'});
    InitReport();
  });
</script>

<script type="x/kendo-template" id="pageTemplate">
  <div class="pageTemplate">
    <div class="header">
      <div style="float: right">Page #: pageNum # of #: totalPages #</div>
      <?php echo $title; ?>
    </div>
    <div class="watermark"><?php echo hcu_displayHtml($prodname); ?></div>
    <div class="footer">
      Page #: pageNum # of #: totalPages #
    </div>
  </div>
</script>

  <div class="cuReportingDiv container hcu-all-100 vsgPrimary" id="cuReportingDiv">
    <div class="row reportHeader hcuSpacer">
      <span class="hcu-breadcrumb col-xs-6"><a href="<?php echo $self; ?>"> Reports </a> / <?php echo $title ?></span>
      <div class="exports col-xs-6">
        <div class="floatRight">
          <span id="csv">
            <a href="#" id="downloadCSVBtn" alt="Download CSV" title="Download CSV">
              <i class='fa fa-download' aria-hidden='true'></i></a></span>

          <span id="pdf">
            <a href="#" id="downloadPDFBtn" alt="Download PDF" title="Download PDF">
              <i class='fa fa-file-pdf-o' aria-hidden='true'></i></a></span>

          <span id="print">
            <a href="#" id="printBtn" alt="Printer Friendly" title="Printer Friendly">
              <i class='fa fa-print' aria-hidden='true'></i></a></span>
        </div>
      </div>
    </div>

    <div id="notification"></div>

    <div class="row hcuSpacer">
      <div class="col-xs-12">
        <div id="formValidateMainDiv" class="k-block k-error-colored formValidateDiv" style="display:none;"></div>
      </div>
    </div>

    <div class="row hcuSpacer">
      <div class="col-xs-12">
        <div class="reportGridOverflow">
          <div id="reportGrid"></div>
        </div>
      </div>
    </div>

    <iframe name="hiddenFrame" class="hide"></iframe>
    <form id="downloadCSVForm" action="<?php echo $reportSelf; ?>&operation=generateCSV" method="post" target="hiddenFrame" style="display:none;">
      <input name="title" value="<?php echo $title; ?>">
    </form>
    <form id="printForm" target="_blank" action="<?php echo $reportSelf; ?>&csv=print&title=<?php echo $title; ?>" method="post" style="display:none;">
      <input name="report" value="<?php echo $report; ?>">
    </form>
  </div>

<?php }

/**
 * function PrintReportPagePrintOnly($reportList, $report, $prodname, $title, $self, $sort, $filter)
 * Prints out the page for a report that is found.
 *
 * @param $reportList -- the reportList in getReportList().
 * @param $report -- the $reportKey in the function getReportList().
 * @param $prodname -- the prodname
 * @param $title -- the title of the report.
 * @param $self -- the URL of this script.  (&report=$report is appended for all calls.)
 * @param $sort -- the sort for the page.
 * @param $filter -- the filter for the page.
 */
function PrintReportPagePrintOnly($reportList, $report, $prodname, $title, $self, $sort, $filter) {
  $reportSelf = "$self&report=$report";
  ?>

  <script type="text/javascript">

  <?php
  getShowWaitFunctions();
  GetIsFilterChanged();

  $pageSize    = 30;
  $pagesToShow = 10;

  InitReport($reportSelf, $report, $reportList, getDefaultDateFormat(), "true", $pageSize, true, $sort, $filter, "cu_reports"); ?>

  $(document).ready(function () {
    $.homecuValidator.setup({formValidate:'cuReportingDiv', formStatusField: 'formValidateMainDiv'});
    InitReport();
    $("#closeWindow").click(function () {
      window.close();
      return false;
    });
    $("#printWindow").click(function () {
      window.print();
      return false;
    });
  });
</script>
  <div class="cuReportingDiv container hcu-all-100 vsgPrimary" id="cuReportingDiv">

    <div class="row hcuSpacer">
      <h2 class="col-xs-12"><?php echo $title; ?></h2>
    </div>
    <div id="formValidateMainDiv" class="k-block k-error-colored formValidateDiv" style="display:none;"></div>

    <div class="row hcuSpacer hidden-print">
      <div class="col-xs-12">
        <a id="closeWindow" href="#" ><i class='fa fa-times' aria-hidden='true'></i>&nbsp;Close Window</a>
        &nbsp;&nbsp;
        <a id="printWindow" href="#" ><i class='fa fa-print' aria-hidden='true'></i>&nbsp;Print</a>
      </div>
    </div>

    <div class="row hcuSpacer">
      <div class="col-xs-12">
        <div id="reportGrid" class="hcu-all-100 printableGrid"></div>
      </div>
    </div>
  </div>
<?php }