lnappLookup.data

<?php
  /*
   * File: lnappLookup.data.v2
   *
   * Purpose:  This script will handle the data / business logic for online
   *            loan application lookup maintenance
   * SPB-- 10/6/2015 version two update to kendo
   */

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

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

HCU_ImportVars($DATA_PARAMETERS, "TOP_LEVEL", array('operation' => 'string'));
$operation= $DATA_PARAMETERS["TOP_LEVEL"]['operation'];
$showSQL= false;

switch ($operation)
{
    case "read":
        $returnArray= read($dbh);
        break;
    case "createTop":
        $returnArray= createTop($dbh);
        break;
    case "updateTop":
        $returnArray= updateTop($dbh);
        break;
    case "createBottom":
        $returnArray= createBottom($dbh);
        break;
    case "updateBottom":
        $returnArray= updateBottom($dbh);
        break;
    case "deleteTop":
        $returnArray= deleteTop($dbh);
        break;
    case "deleteBottom":
        $returnArray= deleteBottom($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);

/* Gathers data for the table (both top-level and bottom level.) */
function read($dbh)
{
    $sql= "select h.ansid, h.anstitle, d.ansdetailid, d.ansvalue, d.ansdisplay from lnappanslookupheader h left join lnappanslookupdetail d on h.ansid= d.ansid
    order by h.anstitle, d.ansdisplay, ansvalue";

    $results= runSelectStatement($sql, $dbh, array("ansid" => "topId", "anstitle" => "topTitle", "ansdetailid" => "bottomId", "ansvalue" => "bottomValue", "ansdisplay" => "bottomText"));
    $errors= $results["error"];
    $gridData= array();

    $currentTopId= -1;
    $tempArray= array();
    foreach($results["record"] as $record)
    {
        if ($currentTopId != $record["topId"])
        {
            $currentTopId= $record["topId"];
            $gridData[]= array("topId" => $currentTopId, "topTitle" => $record["topTitle"]);
            $tempArray[$currentTopId]= array();
        }

        if ($record["bottomId"] != "")
        {
            $tempArray[$currentTopId][]= array("bottomId" => $record["bottomId"], "bottomValue" => $record["bottomValue"], "bottomText" => $record["bottomText"], "topId" => $record["topId"]);
        }
    }

    for($i=0, $count= count($gridData); $i != $count; $i++)
    {
        $topId= $gridData[$i]["topId"];
        if (isset($tempArray[$topId]))
            $gridData[$i]["details"]= $tempArray[$topId];
        else
            $gridData[$i]["details"]= array();
    }

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

/* Adds a record for the top level. */
function createTop($dbh)
{
    $parameters= array();
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", array("topTitle" => "string"));

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

    $sql= "insert into lnappanslookupheader (anstitle) values ('$topTitle'); select currval('lnappanslookupheader_ansid_seq'::regclass) as next; ";
    $results= runSelectStatement($sql, $dbh, array("next" => "next"));
    $topId= intval($results["record"][0]["next"]);
    $errors= array_merge($errors, $results["error"]);

    Remote_Field_Add("lnappanslookupheader", "ansid", $topId);  // ** Lookup Answer ID
    Remote_Field_Add("lnappanslookupheader", "anstitle", $topTitle); // ** Lookup Answer Title

    $Remote_Results = Remote_Update_All("U", "lnappanslookupheader");

    return array("error" => $errors, "sql" => array($sql), "operation" => "create", "record" => array(array("topTitle" => $topTitle, "topId" => $topId, "details" => array())));
}

/* Updates a record at the top level. */
function updateTop($dbh)
{
    $parameters= array();
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", array("topTitle" => "string", "topId" => "string"));

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("topTitle" => array("type" => "string", "required" => true, "maxlength" => 50),
          "topId" => array("type" => "int", "required" => true)));
    $topTitle= $parameters["BOTTOM_LEVEL"]["topTitle"];
    $topId= $parameters["BOTTOM_LEVEL"]["topId"];

    $sql= "update lnappanslookupheader set anstitle = '$topTitle' where ansid = $topId";
    $results= runExecStatement($sql, $dbh);
    $errors= array_merge($errors, $results["error"]);

    Remote_Field_Add("lnappanslookupheader", "ansid", $topId);  // ** Lookup Answer ID
    Remote_Field_Add("lnappanslookupheader", "anstitle", $topTitle); // ** Lookup Answer Title

    $Remote_Results = Remote_Update_All("U", "lnappanslookupheader");

    return array("error" => $errors, "sql" => array($sql), "operation" => "update", "record" => array(array("topTitle" => $topTitle, "topId" => $topId)));
}

/* Creates a record at the bottom level. */
function createBottom($dbh)
{
    $parameters= array();
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", array("bottomValue" => "string", "bottomText" => "string", "topId" => "string"));

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("bottomValue" => array("type" => "string", "required" => true, "maxlength" => 50),
          "bottomText" => array("type" => "string", "required" => true, "maxlength" => 50),
          "topId" => array("type" => "int", "required" => true)));
    $bottomValue= $parameters["BOTTOM_LEVEL"]["bottomValue"];
    $bottomText= $parameters["BOTTOM_LEVEL"]["bottomText"];
    $topId= $parameters["BOTTOM_LEVEL"]["topId"];

    $sql= "insert into lnappanslookupdetail (ansvalue, ansdisplay, ansid) values ('$bottomValue', '$bottomText', $topId); select currval('lnappanswerlookupdetail_ansdetailid_seq'::regclass) as next;";
    $results= runSelectStatement($sql, $dbh, array("next" => "next"));
    $bottomId= intval($results["record"][0]["next"]);
    $errors= array_merge($errors, $results["error"]);

    $record= array("bottomId" => $bottomId, "bottomValue" => $bottomValue, "bottomText" => $bottomText, "topId" => $topId);

    Remote_Field_Add("lnappanslookupdetail", "ansid", $topId);
    Remote_Field_Add("lnappanslookupdetail", "ansdetailid", $bottomId);  // ** Lookup Answer ID
    Remote_Field_Add("lnappanslookupdetail", "ansvalue", $bottomValue); // ** Lookup Answer Value
    Remote_Field_Add("lnappanslookupdetail", "ansdisplay", $bottomText); // ** Lookup Answer Display

    Remote_Update_All("U", "lnappanslookupdetail");

    return array("error" => $errors, "sql" => array($sql), "operation" => "create", "record" => array($record));
}

/* Updates a record at the bottom level. */
function updateBottom($dbh)
{
    $parameters= array();
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", array("bottomValue" => "string", "bottomText" => "string", "bottomId" => "string"));

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("bottomValue" => array("type" => "string", "required" => true, "maxlength" => 50),
          "bottomText" => array("type" => "string", "required" => true, "maxlength" => 50),
          "bottomId" => array("type" => "int", "required" => true),
          "topId" => array("type" => "int", "required" => true)));
    $bottomValue= $parameters["BOTTOM_LEVEL"]["bottomValue"];
    $bottomText= $parameters["BOTTOM_LEVEL"]["bottomText"];
    $bottomId= $parameters["BOTTOM_LEVEL"]["bottomId"];

    $sql= "update lnappanslookupdetail set ansvalue = '$bottomValue', ansdisplay = '$bottomText' where ansdetailid = $bottomId";
    $results= runExecStatement($sql, $dbh);
    $errors= array_merge($errors, $results["error"]);

    $record= array("bottomId" => $bottomId, "bottomValue" => $bottomValue, "bottomText" => $bottomText);

    Remote_Field_Add("lnappanslookupdetail", "ansdetailid", $bottomId);  // ** Lookup Answer ID
    Remote_Field_Add("lnappanslookupdetail", "ansvalue", $bottomValue); // ** Lookup Answer Value
    Remote_Field_Add("lnappanslookupdetail", "ansdisplay", $bottomText); // ** Lookup Answer Display

    Remote_Update_All("U", "lnappanslookupdetail");

    return array("error" => $errors, "sql" => array($sql), "operation" => "update", "record" => array($record));
}

/* Removes a record at the top level. */
function deleteTop($dbh)
{
    $parameters= array();
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", array("topId" => "string", "deleteDetails" => "string"));

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("topId" => array("type" => "int", "required" => true),
          "deleteDetails" => array("type" => "boolean", "required" => false)));

    $topId= $parameters["BOTTOM_LEVEL"]["topId"];
    $deleteDetails= $parameters["BOTTOM_LEVEL"]["deleteDetails"] == "Y";

    $sqls= array();
    if ($deleteDetails)
    {
        $sql1= "select ansdetailid from lnappanslookupdetail where ansid = $topId";
        $results1= runSelectStatement($sql1, $dbh, array("ansdetailid" => "id"));
        $sql2= "delete from lnappanslookupdetail where ansid = $topId";
        $results2= runExecStatement($sql2, $dbh);
        $errors= array_merge($errors, $results1["error"], $results2["error"]);
        array_push($sqls, $sql1, $sql2);

        foreach($results1["record"] as $record)
        {
            Remote_Field_Add("lnappanslookupdetail", "ansdetailid", $record["id"]);  // ** Answer Detail ID
            Remote_Update_All("D", "lnappanslookupdetail");
        }
    }

    $sql= "delete from lnappanslookupheader where ansid = $topId";
    $sqls[]= $sql;
    $results= runExecStatement($sql, $dbh);
    $errors= array_merge($errors, $results["error"]);

    Remote_Field_Add("lnappanslookupheader", "ansid", $topId);  // ** TRUSTED ID
    Remote_Update_All("D", "lnappanslookupheader");

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

/* Removes a record at the bottom level. */
function deleteBottom($dbh)
{
    $parameters= array();
    HCU_ImportVars($parameters, "BOTTOM_LEVEL", array("bottomId" => "string"));

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("bottomId" => array("type" => "int", "required" => true)));

    $bottomId= $parameters["BOTTOM_LEVEL"]["bottomId"];

    $sql= "delete from lnappanslookupdetail where ansdetailid = $bottomId";
    $results= runExecStatement($sql, $dbh);

    Remote_Field_Add("lnappanslookupdetail", "ansdetailid", $bottomId);  // ** Answer Detail ID
    Remote_Update_All("D", "lnappanslookupdetail");

    return array("error" => array_merge($errors, $results["error"]), "sql" => array($sql), "operation" => "delete");
}