lnapp.data

<?php
// File: lnapp.data.v2
// SPB: 10/6/2015 version 2 with kendo

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

HCU_ImportVars($DATA_PARAMETERS, "TOP_LEVEL", array('operation' => 'string', "action" => "string"));
$operation= "";
if (HCU_array_key_exists("operation", $DATA_PARAMETERS['TOP_LEVEL'])) {
  $operation = $DATA_PARAMETERS['TOP_LEVEL']['operation'];
} else {
  $operation = $DATA_PARAMETERS['TOP_LEVEL']['action'];
}

$showSQL= false;

  if (!CheckPerm($link, $Hu, 'loanAppConfig', $_SERVER['REMOTE_ADDR'])) {
    // ** Permissions failed
    // ** redirect to new page
    header("Location: /hcuadm/hcu_noperm.prg");
    exit;
  }

switch ($operation)
{
    case "readOptions":
        $returnArray= readOrModifyOptions($dbh, "read");
        break;
    case "createOption":
        $returnArray= readOrModifyOptions($dbh, "add");
        break;
    case "updateOption":
        $returnArray= readOrModifyOptions($dbh, "update");
        break;
    case "deleteOption":
        $returnArray= readOrModifyOptions($dbh, "delete");
        break;
    case "readLoanApplications":
        $returnArray= readLoanApplications($dbh);
        break;
    case "createLoanApplication":
        $returnArray= createOrUpdateLoanApplication($dbh, true);
        break;
    case "updateLoanApplication":
        $returnArray= createOrUpdateLoanApplication($dbh, false);
        break;
    case "deleteLoanApplication":
        $returnArray= deleteLoanApplication($dbh);
        break;
    case "app":
        $returnArray=  editSave($dbh);
        break;
    default: $returnArray= array("sql" => array(), "error" => array("Operation not specified: '$operation'"), "record" => "", "operation" => "");
}

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

function getConfigOptions($isLive)
{
    return array("configHomeLogin" => array("text" => "Allow Home Banking Login", "type" => "boolean"),
                 "configDisableEmail" => array("text" => "Disable Email Login", "type" => "boolean"),
                 "configSSOOnly" => array("text" => "SSO Only", "type" => "boolean"),
               "configLoanSubmit" => array("text" => "Post Loan Applications to Core", "type" => "boolean", "disabled" => !$isLive, "defaultValue" => true),
               "configLoanSubmitMember" => array("text" => "Post Primary Member With Loan App", "type" => "boolean"),
               "configLoanFormat" => array("text" => "Post Loan Applications Format", "type" => "list"),
               "configLoanMIR" => array("text" => "Allow Member Info to Populate From Core", "type" => "boolean"),
               "configLoanCSS" => array("text" => "Loan App Stylesheet", "type" => "url"),
               "configLoanIntroMbr" => array("text" => "Loan Login Message for Members", "type" => "string"),
               "configLoanIntroNonHBMbr" => array("text" => "Loan Login Message for Non-Home Banking Members", "type" => "string"),
               "configLoanIntroNon" => array("text" => "Loan Login Message for non-Members", "type" => "string"),
               "configLoanDataOnly" => array("text" => "Submit Fields With Data Only", "type" => "boolean"),
               "configPassword" => array("text" => "Password Requirements", "type" => "string")
                );
}

function getConfigLists()
{
    return array("configLoanFormat" => array("XML", "JSON", "SERIAL"));
}

function readOrModifyOptions($dbh, $mode)
{
    $parameters= array();
  $allowed= array("cu" => "string");
  if ($mode != "read")
  {
      $allowed["configId"]= "string";
      $allowed["value"]= "string";
  }
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", $allowed);

  // Since it is in a json array, encode it and THEN prep for the database.
  if ($mode != "read") {
    $configId= $parameters["BOTTOM_LEVEL"]["configId"];
  }
  if ($mode != "read" && $mode != "delete") {
    $value= $parameters["BOTTOM_LEVEL"]["value"];
  }

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
  array("cu" => array("type" => "string", "required" => true, "maxlength" => 12),
        "configId" => array("type" => "string", "required" => true),
        "value" => array("type" => "string", "required" => false)));

  $cu= strtoupper($parameters["BOTTOM_LEVEL"]["cu"]);

  $findAppConfigSQL= "select appconfig, cu from lnappconfig where upper(cu) = '$cu'";
  $findLiveBatchSQL= "select livebatch from cuadmin where upper(user_name) = '$cu'";
  $sqls= array($findAppConfigSQL, $findLiveBatchSQL);

  $appResults= runSelectStatement($findAppConfigSQL, $dbh, array("appconfig" => "appconfig", "cu" => "cu"));
  $liveBatchResults= runSelectStatement($findLiveBatchSQL, $dbh, array("livebatch" => "livebatch"));
  $errors= array_merge($errors, $appResults["error"], $liveBatchResults["error"]);

  $appConfig = "";
  $isLive = false;
  $hasAppConfigRecord = false;
  if (HCU_array_key_exists(0, $appResults["record"])) {
    $appConfig= strval($appResults["record"][0]["appconfig"]);
    $isLive= trim($liveBatchResults["record"][0]["livebatch"]) == "L";
    $hasAppConfigRecord= strval($appResults["record"][0]["cu"]) != "";
  }

    $configOptions= getConfigOptions($isLive);

    $options= $appConfig == "" ? array() : HCU_JsonDecode($appConfig, true);
    $gridData= array();
    if (is_array($options))
    {
    if ($mode != "read")
    {
      switch($mode)
      {
          case "delete":
              unset($options[$configId]);
              break;
          case "add":
          case "update":
              $options[$configId]= $value;
              break;
          default:
              $errors[]= "$mode is not a valid mode.";
      }
      $appConfig= prepSave(HCU_JsonEncode($options));
      $updateSQL= $hasAppConfigRecord ? "update lnappconfig set appconfig='$appConfig' where upper(cu) = '$cu'"
                                      : "insert into lnappconfig (cu, appconfig) values ('$cu', '$appConfig')";
      $sqls[]= $updateSQL;
      if (count($errors) == 0)
      {
          $results= runExecStatement($updateSQL, $dbh);
          $errors= array_merge($errors, $results["error"]);
      }
    }

    if ($mode == "read")
    {
        foreach($options as $key => $value)
        {
            $config= $configOptions[$key];
            $configText= isset($config["text"]) ? $config["text"] : $key;
            $type= isset($config["type"]) ? $config["type"] : "string";
            $disabled= isset($config["disabled"]) ? $config["disabled"] : false;
            $gridData[]= array("configId" => $key, "configText" =>  $configText, "value" => $value, "type" => $type, "disabled" => $disabled);
        }
    }
    else if ($mode != "delete")
    {
        $config= $configOptions[$configId];
        $configText= isset($config["text"]) ? $config["text"] : $configId;
        $type= isset($config["type"]) ? $config["type"] : "string";
        $disabled= isset($config["disabled"]) ? $config["disabled"] : false;
        $gridData[]= array("configId" => $configId, "configText" =>  $configText, "value" => $value, "type" => $type, "disabled" => $disabled);
    }
    }
    else
        $errors[]= "Options not coded correctly for $cu.";

    $returnArray= array("error" => $errors, "sql" => $sqls, "operation" => $mode);

    if ($mode != "delete")
        $returnArray["record"]= $gridData;

    if ($mode == "read")
    {
        $configDDL= array();
        $configLists= getConfigLists();
        foreach($configOptions as $key => $record)
        {
            $record["value"]= $key;
            $configDDL[]= $record;
        }
        $returnArray["configDDL"]= $configDDL;
        $returnArray["configLists"]= $configLists;
    }

    return $returnArray;
}

function readLoanApplications($dbh)
{
    $parameters= array();
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", array("cu" => "string"));

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("cu" => array("type" => "string", "required" => true, "maxlength" => 12)));

    $cu= strtolower($parameters["BOTTOM_LEVEL"]["cu"]);

    // ** Order the results with the template firt followed in CU alphanumeric order
    $sql= "select asm.loanid, asm.cu, asm.loantitle, asm.loandisclosure_fragment, asm.lastmodified from lnappschemamaster asm order by case when cu = 'DMSTEMPL' then 0 else 1 end, cu, loanid";
    $sqls= array($sql);

    $records= array();
    $templateDDL= array(array("value" => 0, "text" => "(None)"));
    if (count($errors) == 0)
    {
        $results= runSelectStatement($sql, $dbh, array("loanid" => "loanId", "cu" => "cu", "loantitle" => "title", "loandisclosure_fragment" => "fragment", "lastmodified" => "lastModified"));
        $errors= array_merge($errors, $results["error"]);

        foreach($results["record"] as $record)
        {
            $templateDDL[]= array("value" => $record["loanId"], "text" => $record['cu'] . " - " . $record["title"]);
            if (((strtoupper($cu) == 'DMSTEMPL') && $record['cu'] == 'DMSTEMPL') || $record["cu"] == strtoupper($cu))
            {
                unset($record["cu"]);
                $records[]= $record;
            }
        }

        $templateDDL[]= array("value" => "", "text" => "Blank");
    }

    return array("sql" => $sqls, "error" => $errors, "record" => $records, "operation" => "read", "templateDDL" => $templateDDL);
}

function createOrUpdateLoanApplication($dbh, $isCreate)
{
    $parameters= array();
    $allowed= array("title" => "string", "fragment" => "string");
    if ($isCreate)
    {
        $allowed["cu"]= "string";
        $allowed["templateId"]= "string";
    }
    else
    {
        $allowed["loanId"]= "string";
    }
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", $allowed);


    $title= $parameters["BOTTOM_LEVEL"]["title"];
    $fragment= $parameters["BOTTOM_LEVEL"]["fragment"];

    $sqls= array();
    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("cu" => array("type" => "string", "required" => true, "maxlength" => 12),
          "title" => array("type" => "string", "required" => true, "maxlength" => 50),
          "fragment" => array("type" => "string", "required" => false, "maxlength" => 50),
          "templateId" => array("type" => "int", "required" => false),
          "loanId" => array("type" => "int", "required" => true)));

    $titleClean= $parameters["BOTTOM_LEVEL"]["title"];
    $fragmentClean= $parameters["BOTTOM_LEVEL"]["fragment"];

    if (count($errors) == 0)
    {
      if ($isCreate)
      {
        $cu= $parameters["BOTTOM_LEVEL"]["cu"];
        $cuClean= $parameters["BOTTOM_LEVEL"]["cu"];
        $templateId= intval($parameters["BOTTOM_LEVEL"]["templateId"]);

        $sql= "insert into lnappschemamaster (cu, loantitle, loandisclosure_fragment, lastmodified) values ('$cuClean', '$titleClean', '$fragmentClean', now());
        select currval('lnappschemamaster_loanid_seq'::regclass) as next";
        $sqls[]= $sql;
        $results= runSelectStatement($sql, $dbh, array("next" => "next"));
        $errors= array_merge($errors, $results["error"]);
        $loanId= intval($results["record"][0]["next"]);

        if (count($errors) == 0 && $templateId != 0)
        {
            $sql= "insert into lnappschemadetail(loanid, pageid, groupid, lineid, fieldtype, fieldvalue, fieldattr) select $loanId, pageid, groupid, lineid, fieldtype, fieldvalue, fieldattr
                   from lnappschemadetail where loanid = $templateId";
            $sqls[]= $sql;
            $results= runExecStatement($sql, $dbh);
            $errors= array_merge($errors, $results["error"]);
        }
      }
      else
      {
        $loanId= $parameters["BOTTOM_LEVEL"]["loanId"];

        $sql= "update lnappschemamaster set loantitle= '$titleClean', loandisclosure_fragment= '$fragmentClean', lastmodified= now() where loanid= $loanId";
        $sqls[]= $sql;
        $results= runExecStatement($sql, $dbh);
        $errors= array_merge($errors, $results["error"]);
      }
    }
    return array("error" => $errors, "sql" => $sqls, "operation" => $isCreate ? "create" : "update", "record" => array(array("loanId" => $loanId, "title" => $title, "fragment" => $fragment)));
}

function deleteLoanApplication($dbh)
{
    $parameters= array();
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", array("loanId" => "string", "cu" => "string"));

    $sqls= array();
    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("loanId" => array("type" => "int", "required" => true),
          "cu" => array("type" => "string", "required" => true, "maxlength" => 12)));

    $loanId= $parameters["BOTTOM_LEVEL"]["loanId"];
    $cu= strtolower($parameters["BOTTOM_LEVEL"]["cu"]);

    $sql= "delete from lnappschemadetail where loanid = $loanId and exists (select 'FOUND' from lnappschemamaster where loanid = $loanId and lower(cu) = '$cu');
           delete from lnappschemamaster where loanid = $loanId and lower(cu) = '$cu'";
    $sqls= array($sql);
    if (count($errors) == 0)
    {
        $results= runExecStatement($sql, $dbh);
        $errors= array_merge($errors, $results["error"]);
    }

    return array("sql" => $sqls, "error" => $errors, "operation" => "delete");
}

function editSave($dbh)
{
    $parameters= array();
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", array("loanid" => "string"));
    $loanid= $parameters["BOTTOM_LEVEL"]["loanid"];

            // ** I messed up on my naming, I mixed the - sign in the field names
          // ** and by the time I noticed I had written almost all the form for
          // ** editing. So at this time, I will instead use the _POST variable
          // ** For accessing data here
        // ** LOOP THROUGH THE lnapp-item values
        $sql = "";

        // ** FIRST -- DELETE ALL ROWS
          // ** Deleting rows ensures removal of any deletes
        $sql = "DELETE FROM lnappschemadetail
                WHERE loanid = " . intval($loanid) . "; ";

        foreach ($_POST['lnapp-item'] as $item_value) {
          // ** We have either an insert or an update
          // ** ALL RECORDS ARE INSERTS -- SOME ARE KNOWN detailid records -
            // ** in which case we use the value from the POST
          if ($item_value < 0) {
            // ** NEW RECORD -- CREATE INSERT
            $sql .= "INSERT INTO lnappschemadetail (loanid, pageid, groupid, lineid, fieldtype, fieldvalue, fieldattr)
                      VALUES
                     ( " . intval($loanid) . ",
                       " . intval($_POST['lnapp-item-page-' . $item_value]) . ",
                       " . intval($_POST['lnapp-item-group-' . $item_value]) . ",
                       " . intval($_POST['lnapp-item-line-' . $item_value]) . ",
                       '" . prep_save($_POST['lnapp-item-fieldtype-' . $item_value], 10) . "',
                       '" . prep_save($_POST['lnapp-item-fieldvalue-' . $item_value], 2000) . "',
                       '" . prep_save($_POST['lnapp-item-fieldattr-' . $item_value], 3000) . "'
                     ); ";

          } else {
            $sql .= "INSERT INTO lnappschemadetail (detailid, loanid, pageid, groupid, lineid, fieldtype, fieldvalue, fieldattr)
                      VALUES
                     ( " . intval($item_value) . ",
                       " . intval($loanid) . ",
                       " . intval($_POST['lnapp-item-page-' . $item_value]) . ",
                       " . intval($_POST['lnapp-item-group-' . $item_value]) . ",
                       " . intval($_POST['lnapp-item-line-' . $item_value]) . ",
                       '" . prep_save($_POST['lnapp-item-fieldtype-' . $item_value], 10) . "',
                       '" . prep_save($_POST['lnapp-item-fieldvalue-' . $item_value], 2000) . "',
                       '" . prep_save($_POST['lnapp-item-fieldattr-' . $item_value], 3000) . "'
                     ); ";
          }
        }
        if ($sql != '') {
          // ** ALSO Update the modified date on the lnappschemamaster record
          $sql .= "UPDATE lnappschemamaster
                   SET lastmodified = current_timestamp
                   WHERE loanid = " . intval($loanid) . " ";
          $sqls= array($sql);
          $results= runExecStatement($sql, $dbh);
          if (count($results["error"]) > 0)
            return array("Result" => "ERROR", "sql" => array($sql), "Message" => $results["error"][0]);
          else
            return array("Result" => "OK", "sql" => array($sql));
        }

}