miPayReport.prg

<?php
/**
 * File: miPayReport.prg
 * The purpose of this file is to upload iPay file to a directory so that Billing can pick up the counts like the domain_email_report.
 */

 /*************************************************************
 **** Common place for file includes and globals. *************
 **************************************************************/

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

if (!CheckPerm($link, $Hu, basename($_SERVER['SCRIPT_NAME']), $_SERVER['REMOTE_ADDR'])) {
    header("Location: /hcuadm/hcu_noperm.prg");
    exit;
}

/**
 * function GetResultsDir()
 * Returns the directory to upload and view the CSV files from.
 */
function GetResultsDir () {
    return "/home/homecu/tmp/billing/";
}

/**
 * function GetXrcPrefix()
 * Returns the base file name of the CSV file.
 */
function GetXrcPrefix () {
    return "ipayrpt";
}

/**
 * function GetMaxSize ()
 * Returns the max size of the CSV file.
 */
function GetMaxSize () {
    return 10485760;
}

/**
 * function GetPageLabel ()
 * Returns the label to put on the top of the page.
 */
function GetPageLabel () {
    return "iPay Report";
}

/**
 * function GetSelf ()
 * Returns the PHP file name.
 */
function GetSelf () {
    return "miPayReport.prg";
}

/**
 * function GetUsualHeader ()
 * This is what I expect for the header.  It comes from Paul's file for March 2018.  I am using it as a check to make sure that I get the right file.
 */
function GetUsualHeader () {
    return "Institution ID,FI Name,Subscribers Plus,Subscribers Plus Active,Subscribers Plus Inactive,Enrollments Plus,Total Transactions Plus,"
         . "Total Electronic Transactions Plus,Total Check Transactions Plus,Subscribers Business,Subscribers Business Active,"
         . "Subscribers Business Inactive,Enrollments Business,Total Transactions Business,Total Electronic Transactions Business,"
         . "Total Check Transactions Business,Bus Invoice User,Bus Payroll User,Bus Bundle User,Invoice Count,Payroll Count,Billing Class,P2P Count,NSF Count,"
         . "Stop Payment Count,Check Copies Count,Compliance Package Annual Fee,MFA Annual Fee,Bill Simple Invoice Only User,Bill Simple Invoice And Pmt User,"
         . "Bill Simple Count CC,Bill Simple Count ACH,Bill Simple Count NOC RET";
}

/**
 * function GetFileErrors ()
 * Returns file errors
 */
function GetFileErrors () {
    return array(
        "1" => "1: The file you tried to upload is just too big.  The allowable limit is 10MB.",
        "2" => "2: The file you tried to upload is just too big.  The allowable limit is 10MB.",
        "3" => "3: The file was only partially uploaded",
        "4" => "4: no file was uploaded");
}

$string = array("filter" => FILTER_SANITIZE_STRING);

$parameters = array();
HCU_ImportVars( $parameters, "", array("operation" => $string, "month" => $string));
extract($parameters);

$operation = isset($operation) ? trim($operation) : "";
$month = isset($month) ? trim($month) : "";

switch($operation) {
    case "csv":
        $returnArray = DownloadCSV($month);
        break;
    case "view": // Later
        $returnArray = ViewReport();
        break;
    case "uploadFile":
        $returnArray = UploadFile($dbh, $month);
        break;
    case "":
        PrintPage(GetInitialData (), $homecuKendoVersion, $cloudfrontDomainName);
        exit;
        break;
    default:
        $returnArray = array("error" => "Operation is not recognized.");
        break;
}

header('Content-type: application/json');
print HCU_JsonEncode($returnArray);
exit; // At this point, all intentional things to do have been done.  Do not continue.


/*************************************************************
**** Code for downloading CSV. *******************************
**************************************************************/

/**
 * function DownloadCSV ($month)
 * This function downloads the CSV.
 *
 * @param $month -- the month to download the CSV for.
 *
 * @return if successful, prints out the CSV file.  If not successful, returns the code and message of the error message.
 */
function DownloadCSV ($month) {
    try {
        $uploadPath = GetResultsDir ();
        $fileName = GetXrcPrefix ();

        if ($month == "") {
            throw new exception("Please enter a month.", 8);
        }

        $matches = array();
        $pregMatch = preg_match('/(\d{4})_(\d{2})/', $month, $matches);

        // In theory, it doesn't really matter if the preg_match itself failed or it doesn't match the string.
        if (!$pregMatch) {
            throw new exception("Month is invalid.", 3);
        }

        if ($matches[2] < 1 || $matches[2] > 12) { // Ensure that the month itself is within range.
            throw new exception("Month is invalid.", 1);
        }

        $fileName .= "_$month.csv"; // At this point, we know that the month string is valid so append it on the end of the filename.

        if (!is_writable($uploadPath)) {
            throw new exception("CSV Directory is not set up correctly.  Please contact HomeCU for assistance.", 2);
        }

        $file = "${uploadPath}${fileName}";

        if (!file_exists($file)) {
            throw new exception("File doesn't exist.", 4);
        }

        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="'.basename($file).'"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($file));
        readfile($file);
        exit;

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

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

/*************************************************************
**** Code for viewing comparison report (later). *************
**************************************************************/

function ViewReport () {

}

/*************************************************************
**** Code for uploading file. ********************************
**************************************************************/


/**
 * function uploadFile($dbh, $month)
 * This function uploads a file to the ipay report directory.
 *
 * @param $dbh -- the database connection
 * @param $month -- the month to upload the file for
 *
 * @return array --
 *            $code -- zero if successful, non-zero otherwise.
 *            $error -- error message if not successful.
 */
function UploadFile ($dbh, $month) {
    try {
        $fileErrors = GetFileErrors ();
        $uploadPath = GetResultsDir ();
        $maxSize = GetMaxSize ();
        $fileName = GetXrcPrefix ();

        if ($month == "") {
            throw new exception("Please enter a month.", 8);
        }

        $matches = array();
        $pregMatch = preg_match('/(\d{4})_(\d{2})/', $month, $matches);

        // In theory, it doesn't really matter if the preg_match itself failed or it doesn't match the string.
        if (!$pregMatch) {
            throw new exception("Month is invalid.", 3);
        }

        if ($matches[2] < 1 || $matches[2] > 12) { // Ensure that the month itself is within range.
            throw new exception("Month is invalid.", 1);
        }

        $fileName .= "_$month.csv"; // At this point, we know that the month string is valid so append it on the end of the filename.

        if (!is_writable($uploadPath)) {
            throw new exception("CSV Directory is not set up correctly.  Please contact HomeCU for assistance.", 2);
        }

        $file = $_FILES['files'];

        if ($file["size"] > $maxSize) {
            throw new exception("File exceeds the max size.", 4);
        }
        if ($file["error"] != 0) {
            $errorInt = $file["error"];
            throw new exception(HCU_array_key_exists($errorInt, $fileErrors) ? $fileErrors[$errorInt] : "$errorInt: Unknown error.", 5);
        }

        $filetmp = $file['tmp_name'];
        if (!@move_uploaded_file($filetmp, "${uploadPath}${fileName}")) {
            @unlink ($filetmp);
            throw new exception("Couldn't post the file where it belongs.  Better call HomeCU.", 7);
        }

        @chmod("${uploadPath}${fileName}", 0644);

        // At this point, I need to open the CSV and add the Credit Union code for billing.
        $convertFileReturn = ConvertFile($dbh, "${uploadPath}${fileName}");
        if ($convertFileReturn["status"] !== "000") {
            throw new exception($convertFileReturn["error"], 9);
        }

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

/**
 * function ConvertFile($dbh, $fileName)
 * This will copy the file contents to a tmp file.  Then it revise the data to fit billing needs.
 *
 * @param $dbh -- the database connection
 * @param $fileName -- the fullpath of the file to convert
 *
 * @return an array with "code" and "error."  If successful, code is zero and error doesn't exist.  If not successful, code is nonzero and error does exist.
 */
function ConvertFile($dbh, $fileName) {
    try {
        if (!@copy($fileName, "$fileName.tmp")) {
            throw new exception("File could not be copied.", 1);
        }

        $readFP = fopen("$fileName.tmp", "r");

        $header = null;
        $headerCount = 0;
        $csvLines = array();
        // Get the header and check for columns.
        while (($buffer = fgets($readFP)) !== false) {
            if (!preg_match('/[^,\s]/', $buffer)) { // If there is an empty line, ignore it.
                continue;
            }

            if (strpos($buffer, "Reseller Report") !== false) {
                continue;
            }

            if (!isset($header)) {

                $header = str_getcsv($buffer);
                $headerCount = count($header);
            } else {
                $buffer = str_getcsv($buffer);

                if (count($buffer) != $headerCount) {
                    throw new exception("File is invalid.", 3);
                }

                $csvLines [trim($buffer[0])] = $buffer;
                // Institution ID: need to get the credit union code by lookup in the bp_cuid attribute of the IPAY trust detail.
            }
        }

        fclose($readFP);

        $sql = "select user_name, name from cuinfo";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception("orgname query failed.", 4);
        }
        $cuResults = db_fetch_all($sth);
        $cuResults = $cuResults === false ? array() : $cuResults;

        $results = GetCuLookup($dbh);
        if ($results["status"] !== "000") {
            throw new exception ("Cu Lookup failed.", 5);
        }
        $cuMap = $results["cuMap"];
        $institutionMap = array();

        foreach ($cuResults as $cuResult) {
            $cu = trim(strtoupper($cuResult["user_name"]));
            if (HCU_array_key_exists($cu, $cuMap) && $cuMap[$cu] != "") {
                $institutionMap[$cuMap[$cu]] = array("cu" => $cu, "orgname" => $cuResult["name"]);
            }
        }

        $header [0] = "CU Code";
        $header [1] = "CU Name";

        $writeFP = fopen($fileName, "w");
        fputcsv ($writeFP, $header);

        foreach ($csvLines as $institutionId => $line) {
            if (HCU_array_key_exists($institutionId, $institutionMap)) {
                $correction = $institutionMap[$institutionId];
                $line [0] = $correction ["cu"];
                $line [1] = $correction ["orgname"];
            } else {
                $line [0] = "E";
                $line [1] = "Error: Need Lookup for $institutionId"; // Follow same style for error in lookup for the domain_email_report.
            }

            fputcsv ($writeFP, $line);
        }

        fclose($writeFP);

        // Cleanup: remove the tmp file.
        @unlink("$fileName.tmp");

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

function GetCuLookup($dbh) {
    try {
        // Get all the iPay trusted details encrypted from the servers.
        $parameters = array("report" => "ipayLookup", "showServer" => "Y", "showDuplicates" => "Y", "limit" => "-1", "offset" => "0");

        $mammothList = array("www3", "www5", "www6");
        $dataList = array();
        foreach($mammothList as $server) {
            $cmd = "https://${server}.homecu.net/hcuadm/adm_reports?operation=readReport";
            $results = HCU_JsonDecode(RunCurl($parameters, $cmd, false));
            if (count($results) == 0) {
                throw new exception ("$server has no results.", 1);
            }
            if (count($results["error"]) > 0) {
                throw new exception ("$server threw: " . $results["error"][0], 2);
            }
            $dataList[$server] = $results["reportData"]["data"];
        }

        $sql = "select cu, trustedid, parms from cutrusteddetail where substr(lower(trim(trustedid)), 1, 4) = 'ipay'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 3);
        }
        $results = db_fetch_all($sth);
        $results = $results === false ? array() : $results;

        $dataList["my"] = $results;

        // Then go through the results and decrypt.  Then separate the values of bp_cuid by credit union and server.
        $cuMap = array();
        foreach ($dataList as $server => $serverData) {
            foreach($serverData as $record) {
               $cu = trim($record["cu"]);
               $masterKey = sha1("${cu}:3pk4osso");

                try {
                   $decrypted = parmdecrypt($record["parms"], $masterKey);
                } catch (exception $e) {
                   global $logger;
                   $logger->error("iPay Report failed encryption for $cu.");
                   continue;
                }

                $detailArray = HCU_JsonDecode(parmdecrypt($record["parms"], $masterKey));

                if (isset($detailArray) && HCU_array_key_exists("bp_cuid", $detailArray)) {
                   if (!HCU_array_key_exists($cu, $cuMap)) {
                       $cuMap[$cu] = array();
                   }
                   $cuMap[$cu][$server] = trim($detailArray["bp_cuid"]);
                }

                // This is a new key for IPAY_V3
                if (isset($detailArray) && HCU_array_key_exists("ipay_instid", $detailArray)) {
                   if (!HCU_array_key_exists($cu, $cuMap)) {
                       $cuMap[$cu] = array();
                   }
                   $cuMap[$cu][$server] = trim($detailArray["ipay_instid"]);
               }
           }
        }

        foreach ($cuMap as $cu => $cuRecord) {
            if (HCU_array_key_exists("my", $cuRecord)) {
                $cuMap[$cu] = $cuRecord["my"];
            } else if (HCU_array_key_exists("www3", $cuRecord)) {
                $cuMap[$cu] = $cuRecord["www3"];
            } else if (HCU_array_key_exists("www6", $cuRecord)) {
                $cuMap[$cu] = $cuRecord["www6"];
            } else if (HCU_array_key_exists("www5", $cuRecord)) {
                $cuMap[$cu] = $cuRecord["www5"];
            } else {
                $cuMap[$cu] = 0;
            }
        }

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

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

/**
 * function GetInitialData ()
 * This function goes through the Ipay report directory.  It returns the file size and month from the file.
 *
 * @return array(month => fileSize)
 */
function GetInitialData () {
    try {
        $uploadPath = GetResultsDir ();
        $filePrefix = GetXrcPrefix ();
        $filePrefixLength = strlen($filePrefix);

        if (!is_writable($uploadPath)) {
            if (!mkdir($uploadPath)) {
                throw new exception("CSV Directory is not set up correctly.  Please contact HomeCU for assistance.", 2);
            }
        }

        $fileList = scandir ($uploadPath);

        $monthMap = array();
        foreach ($fileList as $file) {
           if (strncmp($file, $filePrefix, $filePrefixLength) == 0) {
               $month = str_replace(".csv", "", str_replace("${filePrefix}_", "", $file));
               $fileSize = floatval(@filesize("${uploadPath}${file}"));

               if ($fileSize < 1024) {
                   $fileSize = "$fileSize B";
               } else if ($fileSize < 1024*1024) {
                   $fileSize = ($fileSize / 1024.0) . " KB";
               } else if ($fileSize < 1024*1024*1024) {
                   $fileSize = ($fileSize / (1024.0*1024)) . " MB";
               } else {
                   $fileSize = ($fileSize / (1024.0*1024*1024)) . " GB";
               }
               $monthMap [$month] = $fileSize;
           }
        }

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

/**
 * function PrintPage ($initialData, $homecuKendoVersion, $cloudfrontDomainName)
 * This function prints out the visual part of this script.
 *
 * @param $initialData -- Contains the data for what months already have a file uploaded and the filesizes of those files.
 * @param $homecuKendoVersion -- Global to get the right kendo css and javascript.
 * @param $cloudfrontDomainName -- Global to get the right cloudfront domain name.
 */
function PrintPage ($initialData, $homecuKendoVersion, $cloudfrontDomainName) {
    $pageLabel = GetPageLabel ();
    $self = GetSelf ();
    $maxSize = GetMaxSize ();

    printMonitorPageTop ($pageLabel, $homecuKendoVersion, $cloudfrontDomainName);
    printMonitorPageMiddle ($pageLabel);

    /*************************************************************
    **** Javascript code. ****************************************
    **************************************************************/ ?>

    <script type="text/javascript">
    <?php // INCLUDE COMMON JAVASCRIPT FUNCTIONS
    getShowWaitFunctions (); ?>

    <?php
    /**
     * function OpenAuthFailedDialog(response)
     * This opens a dialog if the uploader fails.
     *
     * @param response -- the response from the uploader.
     */
    ?>
    function OpenAuthFailedDialog(response) {
        var authFailedDialog = $("#authFailedDialog").data("kendoWindow");
        if (authFailedDialog != null) {
            authFailedDialog.destroy();
            $("#authFailedDialog").empty();
        }

        authFailedDialog = $("#authFailedDialog").kendoWindow({
            content: {template: response.replace(/[#]/g, "\\#")}, <?php // have to escape the # for kendo. ?>
            draggable: false,
            visible: false,
            actions: [],
            modal: true,
            title: false,
            width: 400,
            resizable: false
        }).data("kendoWindow");

        authFailedDialog.open().center();
    }

    <?php
    /**
     * function Init()
     * Initializes all the kendo controls and jQuery click events on the page.
     */
    ?>
    function Init() {

        $.homecuValidator.setup({formValidate:'uploadForm', formStatusField: 'statusDiv'});

        var initialData = <?php echo HCU_JsonEncode($initialData); ?>;
        if (initialData.status !== "000") {
            $.homecuValidator.displayMessage(initialData.error, $.homecuValidator.settings.statusError );
        }

        <?php
        /**
         * @var uploader -- this is the kendo uploader widget responsible for uploading files.
         */
        ?>
        var uploader = $("#uploader").kendoUpload({
            async: {
                saveUrl: "<?php echo $self; ?>?operation=uploadFile",
                withCredentials: true,
                autoUpload: false
            },
            multiple: false,
            showFileList: true,
            error: function(e) {
                hideWaitWindow();
                var response = e.XMLHttpRequest.responseText;

                if (response.indexOf("PDFUploadFirstErrorLi") != -1) {
                    $.homecuValidator.displayMessage(response, $.homecuValidator.settings.statusError );
                } else {
                    openAuthFailedDialog(response);
                }
                $(".k-upload-files").remove();
            },
            success: function(e) {
                hideWaitWindow();
                if (e.response.status === "000") {
                    $.homecuValidator.validate();
                    $("#fileExists").hide();
                    $("#monthDatePicker").data("kendoDatePicker").value(null);
                } else {
                    $.homecuValidator.displayMessage(e.response.error, $.homecuValidator.settings.statusError );
                    e.preventDefault();
                    return false;
                }

            },
            select: function(e) { <?php // When a file is selected.  If we can prevent at this level, then the user doesn't have to click on the upload to know that the file is invalid. ?>
                var error = [];
                var file = e.files[0];
                file.extension.toLowerCase() != ".csv" ? error.push ("File needs to be a CSV.") : null;
                file.size > <?php echo $maxSize; ?> ? error.push ("File exceeds the max size.") : null;

                if (error.length > 0) {
                    $.homecuValidator.displayMessage(error, $.homecuValidator.settings.statusError );
                    e.preventDefault();
                    return false;
                }
            },
            upload: function(e) {
                var month = $("#monthDatePicker").data("kendoDatePicker").value();
                var month = month == null ? "" : kendo.toString(month, "yyyy_MM");
                e.data = {month: month};
                showWaitWindow();
            },
            localization: {
                select: "Select File"
            }
        }).data("kendoUpload");

        var monthDatePicker = $("#monthDatePicker").kendoDatePicker({
            depth: "year",
            start: "year",
            format: "MMM yyyy",
            parseFormats: ["yyyy_MM"],
            change: function() {
                var month = this.value();
                month = kendo.toString(month, "yyyy_MM");
                if (initialData.data[month] != null) {
                    $("#fileExists .size").text(initialData.data[month]);
                    $("#fileExists").show();
                } else {
                    $("#fileExists").hide();
                }
            }
        }).data("kendoDatePicker");

        $("#uploadBtn").click(function() {

            var error = [];

            var month = $("#monthDatePicker").data("kendoDatePicker").value();
            month == null ? error.push ("Please select a month.") : null;
            $("#uploadForm .k-upload-files .k-file:visible").length == 0 ? error.push ("Please select a file.") : null;

            error.length > 0 ? $.homecuValidator.displayMessage(error, $.homecuValidator.settings.statusError ) : $("#uploadForm .k-button.k-upload-selected").click();
        });
    }

    $(document).ready(function() {
        Init();
    });

    </script>

    <?php /*******************************************************
    **** Page-specific CSS. **************************************
    **************************************************************/ ?>

    <style>
        #uploadForm {
            max-width: 600px;
        }

        .grid_12 {
            margin-top: 5px;
        }

        .hcuAll100 {
            width: 100%;
        }

        #uploadForm .k-clear-selected, #uploadForm .k-upload-selected, #uploadForm .k-upload-status {
            display: none;
        }
    </style>

    <?php /*******************************************************
    **** Kendo templates. ****************************************
    **************************************************************/ ?>

    <script id="popupTemplateDetail" type="text/x-kendo-template">

    </script>

    <?php // Mammoth doesn't have bootstrap files but use grid_360.  Will need to be updated to Bootstrap when migrated to Odyssey. ?>
    <div class="container_12">
        <div class="grid_12" id="statusDiv"></div>

    <?php /*******************************************************
     **** GUI for uploading CSV. *********************************
     *************************************************************/ ?>

     <div id="uploadForm">
         <div class="grid_12"><input type="file" name="files" id="uploader" accept=".csv" /></div>
         <div class="grid_12">
             <div class="grid_3 alpha">
                 Month to Upload:
             </div>
             <div class="grid_4">
                 <input id="monthDatePicker">
             </div>
             <div class="grid_5 omega">
                 <h6 id="fileExists" style="display:none">File exists with a size of <span class="size"></span>.</h6>
             </div>
         </div>
         <div class="grid_12">
             <a class="k-button k-primary" id="uploadBtn">Upload File</a>
         </div>
     </div>

    <?php /*******************************************************
     **** GUI for comparison report (later). *********************
     *************************************************************/ ?>

     </div>

     <?php printMonitorPageBottom(); ?>
<?php }