cutrustdetail.data

<?php
  /*
   * File: cutrustvendor.data.v2
   *
   * Purpose:  This script will handle the data / business logic for cu trusted vendor
   *              maintenance
   * NOTE: properties are stored as key, value pairs in the database.  This has not changed from the original.
   */

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


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

$showSQL= false;

dms_import_v2($DATA_PARAMETERS, "TOP_LEVEL", array("operation" => "string"));
$operation= $DATA_PARAMETERS["TOP_LEVEL"]["operation"];

switch ($operation)
{
    case "readTrustedVendors":
        $returnArray= readTrustedVendorsDetails($dbh);
        break;
    case "createTrustedId":
        $returnArray= createTrustedIdDetails($dbh);
        break;
    case "removeTrustedId":
        $returnArray= removeTrustedIdDetails($dbh);
        break;
    case "addTrustedDetail":
        $returnArray= changeTrustedDetailDetails($dbh, "add");
        break;
    case "updateTrustedDetail":
        $returnArray= changeTrustedDetailDetails($dbh, "update");
        break;
    case "removeTrustedDetail":
        $returnArray= changeTrustedDetailDetails($dbh, "delete");
        break;
    case "addServerSetting":
        $returnArray= changeServiceProperty($dbh, "add");
        break;
    case "updateServerSetting":
        $returnArray= changeServiceProperty($dbh, "update");
        break;
    case "removeServerSetting":
        $returnArray= changeServiceProperty($dbh, "delete");
        break;
    case "readServerSettings":
        $returnArray= readServerSettings($dbh);
        break;
    case "serviceUpdate":
        $returnArray= serviceUpdate($dbh);
        break;
    case "pullDownOptions":
        $returnArray= pullDownOptions($dbh);
        break;
    case "reorderProperties":
        $returnArray= reorderProperties($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 json_encode($returnArray);

/**
 * function getServerOptions()
 * Gets the three states of the server
 * @return the DDL
 */
function getServerOptions()
{
    return array("N" => "Active", "U" => "Offline (Auto Up)", "Y" => "Offline (Stay Down)");
}

/**
 * function getServiceUpdateOptions()
 * Gets the DDL menu for updating all services
 * @return the DDL menu
 */
function getServiceUpdateOptions()
{
    return array(array("text" => "Turn On", "value" => "Y"), array("text" => "Turn Off", "value" => "N"));
}

/**
 * function extractDetails($records, $cu)
 * Extracts the details using the particular masterKey.
 *
 * @param array $records -- the trusted details
 * @param string $cu -- the credit union
 * @return array -- the records decrypted and decoded.
 */
function extractDetails($records, $cu)
{
    $masterKey = sha1("${cu}:3pk4osso");
    $extractedRecords= array();

    foreach($records as $record)
    {
        $details= array();
        $detailIndex= 0;
        $detailsDecoded= array();

        if ($record["detailsEncoded"] != "")
        {
            $detailsDecrypted= parmdecrypt($record["detailsEncoded"], $masterKey);
            $detailsDecoded= json_decode($detailsDecrypted, true);
        }

        $record["detailsDecoded"]= $detailsDecoded;
        unset($record["detailsEncoded"]);
        $extractedRecords[]= $record;
    }
    return $extractedRecords;
}

/**
 * function getDetailsForGrid($almostExpandedRecords, $trustedIdDetailDDL, $masterDetailLength= array(), &$warnings)
 * Forms the extracted details into a format for the grid.
 *
 * @param array $almostExpandedRecords -- the records retrieved from the database and decrypted and decoded.
 * @param array $trustedIdDetailDDL -- the details from the master by trusted id.  Needed to add field type and messages to the detail records.
 * @param array $masterDetailLength -- contains the lengths of the details by trusted id.
 * @param array $warnings -- if there are any warnings, append them to this array.
 * @return array --
 *      $topRecords -- the records of the trusted ids.
 *      $detailRecords -- the records of the trusted ids' properties.
 */
function getDetailsForGrid($almostExpandedRecords, $trustedIdDetailDDL, $masterDetailLength= array(), &$warnings)
{
    $expandedRecords= array();
    $details= array();
    $detailIndex= 0;

    // print var_dump($almostExpandedRecords);

    foreach ($almostExpandedRecords as $record)
    {
        // print "\$record: ".var_dump($record)."\n";

        $detailDDL= $trustedIdDetailDDL[$record["trustedId"]];
        $detailsDecoded= $record["detailsDecoded"];
        $record["hasMasterRecord"]= isset($detailDDL);
        if (is_array($detailsDecoded))
        {
            $displayOrder= 0;
            foreach($detailsDecoded as $key => $value)
            {
                $fieldType= isset($detailDDL[$key]["fieldType"]) ? $detailDDL[$key]["fieldType"] : "string";
                $message= isset($detailDDL[$key]["message"]) ? $detailDDL[$key]["message"] : "";
                $value= stripslashes($value);
                $message= stripslashes($message);

                $details[]= array("property" => $key, "value" => $value, "fieldType" => $fieldType, "hasMasterRecord" => $record["hasMasterRecord"], "message" => $message,
                    "trustedId" => $record["trustedId"], "displayOrder" => $displayOrder, "pid" => $displayOrder + 1);
                $displayOrder++;
            }
        }
        else if ($detailsDecoded != "")
        {
            $trustedId= $record["trustedId"];
            $warnings[]= "TrustedId's details are not encoded correctly: $trustedId";
        }

        unset($record["detailsDecoded"]);
        $record["masterDetailLength"]= isset($masterDetailLength[$record["trustedId"]]) ? $masterDetailLength[$record["trustedId"]] : 0;
        $expandedRecords[]= $record;
    }
    return array("topRecords" => $expandedRecords, "detailRecords" => $details);
}

/**
 * function encryptDetails($detailRecords, $cu)
 * Encrypts the details.
 *
 * @param array $detailRecords -- the detail records to encrypt
 * @param string $cu -- the credit union
 * @return string -- the encrypted string to place in database.
 */
function encryptDetails($detailRecords, $cu)
{
    $masterKey = sha1("${cu}:3pk4osso");

    if (is_array($detailRecords) && count($detailRecords) > 0)
        return parmencrypt(json_encode($detailRecords), $masterKey);
    else
        return "";
}

/**
 * function readTrustedVendorsDetails($dbh)
 * Reads the grid (top-level and bottom-level)
 *
 * @param integer $dbh -- the database connection
 */
function readTrustedVendorsDetails($dbh)
{
    $parameters= array();
    dms_import_v2($parameters, "BOTTOM_LEVEL", array("cu" => "string"));

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

    $sql= "select td.trustedid, td.parms from cutrusteddetail td where upper(td.cu) = '$cu' order by td.trustedid";
    $queryResults= runSelectStatement($sql, $dbh, array("trustedid" => "trustedId", "parms" => "detailsEncoded"));
    $errors= array_merge($errors, $queryResults["error"]);
    $warnings= array();

    $almostExpandedRecords= extractDetails($queryResults["record"], $cu);

    $masterSQL= "select tm.trustedid, tm.trustedfields from cutrustedmaster tm order by tm.trustedid, tm.trustedvendor";
    $queryResults= runSelectStatement($masterSQL, $dbh, array("trustedid" => "trustedId", "trustedfields" => "masterFields"));
    
    $errors= array_merge($errors, $queryResults["error"]);

    $trustedIdDetailDDL= array();
    $masterDetailLength= array();
    foreach($queryResults["record"] as $record)
    {
        $details= json_decode($record["masterFields"], true);
        $detailDDL= array();
        $trustedId= $record["trustedId"];

        if (is_array($details))
        {
            foreach($details as $key => $detailRow)
            {
                $detail= array("fieldType" => "string", "defaultValue" => "");
                if (is_array($detailRow))
                {
                    foreach($detailRow as $lowerKey => $value) // type and default
                    {
                        if ($lowerKey == "Type")
                            $detail["fieldType"]= $value;
                        else if ($lowerKey == "Default")
                            $detail["defaultValue"]= stripslashes($value);
                        else if ($lowerKey == "Message")
                            $detail["message"]= stripslashes($value);
                    }
                }
                $detailDDL[stripslashes($key)]= $detail;
            }
            $masterDetailLength[$trustedId]= count($details);
        }
        else if ($record["masterFields"] != "")
        {
            $trustedId= $record["trustedId"];
            $warnings[]= "TrustedId master's details are not encoded correctly: $trustedId";
            $details= array();
            $masterDetailLength[$trustedId]= 0;
        }

        $trustedIdDetailDDL[$trustedId]= $detailDDL;
    }

    $expandedRecords= getDetailsForGrid($almostExpandedRecords, $trustedIdDetailDDL, $masterDetailLength, $warnings);

    return array("sql" => array($sql, $masterSQL), "error" => $errors, "topRecords" => $expandedRecords["topRecords"], "detailRecords" => $expandedRecords["detailRecords"],
        "trustedIdDetailDDL" => $trustedIdDetailDDL, "operation" => "read", "warning" => $warnings);
}

/**
 * function readServerSettings($dbh)
 * Reads the grid (bottom-level for the server property "HOMECUSERVICE")
 *
 * @param integer $dbh -- the database connection
 * @return array --
 *      $serviceUpdateOptions -- the DDL at the top of this script: "N", "U", "Y" are the valid options
 *      $serverOptions -- the DDL at the top of this script: "N" and "Y" for on and off
 *      $hasMasterRecord -- this will always be true
 *      $record -- the list of properties with the options and messages.
 *      $defaultsDDL -- the data from the master table.  It is used when a property is created.
 */
function readServerSettings($dbh)
{
    $parameters= array();
    dms_import_v2($parameters, "BOTTOM_LEVEL", array("initializeDDLs" => "string"));

    $sqls= array();
    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("initializeDDLs" => array("type" => "boolean", "required" => false)));
    $initializeDDLs= $parameters["BOTTOM_LEVEL"]["initializeDDLs"] == "Y";

    $sql= "select td.trustedid, td.parms, tm.trustedfields, tm.trustedid as master_id from cutrusteddetail td left join cutrustedmaster tm on td.trustedid= tm.trustedid " .
          "where upper(td.cu) = 'HOMECU' and td.trustedid= 'HOMECUSERVICE' and upper(tm.trustedvendor) = 'HOMECU' order by td.trustedid";
    $queryResults= runSelectStatement($sql, $dbh, array("trustedid" => "trustedId", "parms" => "detailsEncoded", "trustedfields" => "masterDetailsEncoded", "master_id" => "masterId"));
    $errors= $queryResults["error"];
    $warnings= array();

    $almostExpandedRecords= extractDetails($queryResults["record"], "HOMECU");

    $returnArray= array("error" => $errors, "sql" => array($sql), "record" => array(), "defaultsDDL" => array(), "hasMasterRecord" => false, "operation" => "read");
    $serverOptions= getServerOptions();

    if ($initializeDDLs)
    {

        $serverOptionsDDL= array();
        $serviceUpdateOptions= getServiceUpdateOptions();
        foreach($serverOptions as $key => $value)
        {
            $serverOptionsDDL[]= array("value" => $key, "text" => $value);
        }
        $returnArray["serviceUpdateOptions"]= $serviceUpdateOptions;
        $returnArray["serverOptions"]= $serverOptionsDDL;
    }
    foreach($queryResults["record"] as $record)
    {
        $details= array();
        $messages= array();
        $masterDetails= array();
        $masterMessages= array();
        $detailsDecoded= $almostExpandedRecords[0]["detailsDecoded"];
        if (is_array($detailsDecoded))
        {
            foreach($detailsDecoded as $key => $value)
            {
                $pos= stripos($key, "_MSG");
                $len= strlen($key);

                if ($pos !== false && $len - $pos == 4) // _MSG at end of the string
                    $messages[stripslashes(substr($key, 0, $pos))]= stripslashes($value);
                else
                    $details[]= array("property" => $key, "value" => stripslashes($value));
            }
        }

        $returnArray["hasMasterRecord"]= $record["masterId"] != "";

        for($i=0, $count=count($details); $i != $count; $i++)
        {
            $key= $details[$i]["property"];
            $value= $details[$i]["value"];
            $details[$i]["message"]= strval($messages[$key]);
            $details[$i]["text"]= isset($serverOptions[$value]) ? $serverOptions[$value] : $value;
        }
        $returnArray["record"]= $details;

        if ($initializeDDLs)
        {
            $masterDetailsDecoded= json_decode($record["masterDetailsEncoded"], true);
            if (is_array($masterDetailsDecoded))
            {
                foreach($masterDetailsDecoded as $key => $value)
                {
                    $pos= stripos($key, "_MSG");
                    if ($pos !== false && strlen($key) - $pos == 4) // _MSG at end of the string
                        $masterMessages[stripslashes(substr($key, 0, $pos))]= stripslashes($value["Default"]);
                    else
                        $masterDetails[]= array("property" => stripslashes($key), "defaultValue" => stripslashes($value["Default"]));
                }
            }
            for($i=0, $count=count($masterDetails); $i != $count; $i++)
            {
                $key= $masterDetails[$i]["property"];
                $masterDetails[$i]["defaultMessage"]= strval($masterMessages[$key]);
            }

            $returnArray["defaultsDDL"]= $masterDetails;
        }
    }
    return $returnArray;
}

/**
 * function createTrustedIdDetails($dbh)
 * Creates a top-level record
 *
 * @param integer $dbh -- the database connection
 * @return array --
 *      $record -- the top level record for the trusted id.
 *      $error -- one or zero errors encountered.
 *      $sqls -- the SQLs used
 *      $operation -- set to "add" for informational purposes
 *      $trustedId -- the trusted id to create
 *      $bottomRecords -- the property records to create.
 */
function createTrustedIdDetails($dbh)
{
    $parameters= array();
    $sqls= array();
    dms_import_v2($parameters, "BOTTOM_LEVEL", array("trustedId" => "string", "cu" => "string", "masterDetails" => "string"));
    $trustedId= trim($parameters["BOTTOM_LEVEL"]["trustedId"]);
    $cu= trim($parameters["BOTTOM_LEVEL"]["cu"]);
    $masterDetails= trim($parameters["BOTTOM_LEVEL"]["masterDetails"]);
    $record= array();
    $newBottomRecords= array();
    try
    {
        if ($trustedId == "")
            throw new exception("TrustedId is required!", 1);
        if (strlen($trustedId) > 20)
            throw new exception("TrustedId is too long!", 2);
        if ($cu == "")
            throw new exception("Cu is required!", 3);
        if (strlen($cu) > 12)
            throw new exception("Cu is too long!", 4);
        if ($masterDetails == "")
            throw new exception("Master details are required!", 5);
        $masterDetails= json_decode($masterDetails, true);
        if (!is_array($masterDetails))
            throw new exception("Master details are malformed!", 6);

        foreach($masterDetails as $key => $record)
        {
            $property= stripslashes($key);
            $value= stripslashes(strval($record["defaultValue"]));
            $message= stripslashes(strval($record["message"]));
            $expandedDetails[$property]= $value;
            $fieldType= isset($record["fieldType"]) ? $record["fieldType"] : "string";
            $newBottomRecords[]= array("property" => $property, "fieldType" => $fieldType, "value" => $value, "message" => $message, "hasMasterRecord" => true, "trustedId" => $trustedId);
        }
        $encryptedDetails= encryptDetails($expandedDetails, $cu);

        $sql= "insert into cutrusteddetail (cu, trustedid, parms) values ('" . prep_save($cu, 12) . "', '" . prep_save($trustedId, 20) . "', '" . prep_save($encryptedDetails) . "')";
        $sqls[]= $sql;
        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("Insert query failed!", 7);
        $record= array("trustedId" => $trustedId, "hasMasterRecord" => true, "masterDetailLength" => count($newBottomRecords));
    }
    catch(exception $e)
    {
        return array("record" => array(), "error" => array($e->getMessage()), "sql" => $sqls, "operation" => "add", "trustedId" => $trustedId, "bottomRecords" => array());
    }
    return array("record" => $record, "error" => array(), "sql" => $sqls, "operation" => "add", "trustedId" => $trustedId, "bottomRecords" => $newBottomRecords);
}

/**
 * function removeTrustedIdDetails($dbh)
 * Removes a top-level record
 *
 * @param integer $dbh -- the database connection
 * @return array --
 *      $errors -- any errors encountered
 *      $sqls -- the SQLs used
 *      $operation -- set to "delete" for informational purposes
 */
function removeTrustedIdDetails($dbh)
{
    $parameters= array();
    dms_import_v2($parameters, "BOTTOM_LEVEL", array("trustedId" => "string", "cu" => "string"));

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

    $sql= "delete from cutrusteddetail where cu= '$cu' and trustedid= '$trustedId'";
    $queryResults= runExecStatement($sql, $dbh);
    $errors= array_merge($errors, $queryResults["error"]);

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

/**
 * function changeTrustedDetailDetails($dbh, $mode)
 * Creates, updates, or remove a bottom-level record
 *
 * @param integer $dbh -- the database connection
 * @param string $mode -- the operation: "delete", "update", "create"
 * @return array --
 *      $sql -- the SQLs used
 *      $error -- any errors encountered
 *      $operation -- the value of the $mode parameter
 *      $record -- the modified record
 */
function changeTrustedDetailDetails($dbh, $mode)
{
    $parameters= array();

    $valid= array("trustedId" => "string", "property" => "string", "cu" => "string");
    if ($mode != "delete")
    {
        $valid["value"]= "string";
        $valid["fieldType"]= "string";
        $valid["message"]= "string";
    }
    dms_import_v2($parameters, "BOTTOM_LEVEL", $valid);

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
        array("trustedId" => array("type" => "string", "required" => true, "maxlength" => 20),
              "cu" => array("type" => "string", "required" => true, "maxlength" => 12)));
    $trustedId= $parameters["BOTTOM_LEVEL"]["trustedId"];
    $property= trim($parameters["BOTTOM_LEVEL"]["property"]);
    $value= trim($parameters["BOTTOM_LEVEL"]["value"]);
    $cu= $parameters["BOTTOM_LEVEL"]["cu"];
    $fieldType= trim($parameters["BOTTOM_LEVEL"]["fieldType"]);
    $message= trim($parameters["BOTTOM_LEVEL"]["message"]);

    if ($property == "")
        $errors[]= "Property is required";

    $retrievalSQL= "select parms from cutrusteddetail where trustedid= '$trustedId' and cu= '$cu'";
    $queryResults= runSelectStatement($retrievalSQL, $dbh, array("parms" => "detailsEncoded"));
    $errors= array_merge($errors, $queryResults["error"]);
    $sqls= array($retrievalSQL);

    $detailArray= array();
    $records= array();
    if (count($errors) == 0)
    {
        $expandedRecords= extractDetails($queryResults["record"], $cu, $errors);
        if ($expandedRecords == "" || count($expandedRecords) == 0)
            $expandedDetails= array();
        else
            $expandedDetails= $expandedRecords[0]["detailsDecoded"];

        if ($mode == "delete")
            unset($expandedDetails[$property]);
        else
            $expandedDetails[$property]= $value;

        $encrypedDetails= encryptDetails($expandedDetails, $cu);
        $returnRecord= array(array("property" => $property, "value" => $value, "fieldType" => $fieldType, "message" => $message, "hasMasterRecord" => true, "trustedId" => $trustedId));

        $updateSQL= "update cutrusteddetail set parms= '$encrypedDetails' where trustedid= '$trustedId' and cu= '$cu'";
        $queryResults= runExecStatement($updateSQL, $dbh);
        $errors= array_merge($errors, $queryResults["error"]);
        $sqls[]= $updateSQL;
    }
    return array("sql" => $sqls, "error" => $errors, "operation" => $mode, "record" => $returnRecord);
}

/**
 * function reorderProperties($dbh)
 * Reorders the properties
 *
 * @param integer $dbh -- the database connection
 * @return array --
 *      $sql -- the SQLs used
 *      $error -- any errors encountered
 *      $operation -- apparently null
 *      $record -- the properties in the revised order
 */
function reorderProperties($dbh)
{
    $parameters= array();

    dms_import_v2($parameters, "BOTTOM_LEVEL", array("trustedId" => "string", "property" => "string", "cu" => "string", "position" => "string"));

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
        array("trustedId" => array("type" => "string", "required" => true, "maxlength" => 20),
              "cu" => array("type" => "string", "required" => true, "maxlength" => 12),
              "position" => array("type" => "int", "required" => true)));
    $trustedId= $parameters["BOTTOM_LEVEL"]["trustedId"];
    $property= trim($parameters["BOTTOM_LEVEL"]["property"]);
    $cu= $parameters["BOTTOM_LEVEL"]["cu"];
    $position= $parameters["BOTTOM_LEVEL"]["position"];

    if ($property == "")
        $errors[]= "Property is required";

    $retrievalSQL= "select parms from cutrusteddetail where trustedid= '$trustedId' and cu= '$cu'";
    $queryResults= runSelectStatement($retrievalSQL, $dbh, array("parms" => "detailsEncoded"));
    $errors= array_merge($errors, $queryResults["error"]);
    $sqls= array($retrievalSQL);

    $detailArray= array();
    $records= array();
    if (count($errors) == 0)
    {
        $expandedRecords= extractDetails($queryResults["record"], $cu, $errors);
        if ($expandedRecords == "" || count($expandedRecords) == 0)
            $expandedDetails= array();
        else
            $expandedDetails= $expandedRecords[0]["detailsDecoded"];

        $newExpandedDetails= array();
        $index= 0;
        foreach($expandedDetails as $key => $value)
        {
            if ($index == $position)
                $newExpandedDetails[$property]= $expandedDetails[$property];
            if ($key != $property)
                $newExpandedDetails[$key]= $value;
            $index++;
        }

        if ($index >= count($expandedDetails))
            $newExpandedDetails[$property]= $expandedDetails[$property];

        $encrypedDetails= encryptDetails($newExpandedDetails, $cu);

    $masterSQL= "select tm.trustedid, tm.trustedfields from cutrustedmaster tm where tm.trustedid = '$trustedId' order by tm.trustedid, tm.trustedvendor";
    $queryResults= runSelectStatement($masterSQL, $dbh, array("trustedid" => "trustedId", "trustedfields" => "masterFields"));
    $errors= array_merge($errors, $queryResults["error"]);

    $trustedIdDetailDDL= array();
    $masterDetailLength= array();
    foreach($queryResults["record"] as $record)
    {
        $details= json_decode($record["masterFields"], true);
        $detailDDL= array();
        $trustedId= $record["trustedId"];

        if (is_array($details))
        {
            foreach($details as $key => $detailRow)
            {
                $detail= array("fieldType" => "string", "defaultValue" => "");
                if (is_array($detailRow))
                {
                    foreach($detailRow as $lowerKey => $value) // type and default
                    {
                        if ($lowerKey == "Type")
                            $detail["fieldType"]= $value;
                        else if ($lowerKey == "Default")
                            $detail["defaultValue"]= stripslashes($value);
                        else if ($lowerKey == "Message")
                            $detail["message"]= stripslashes($value);
                    }
                }
                $detailDDL[stripslashes($key)]= $detail;
            }
            $masterDetailLength[$trustedId]= count($details);
        }
        else if ($record["masterFields"] != "")
        {
            $trustedId= $record["trustedId"];
            $warnings[]= "TrustedId master's details are not encoded correctly: $trustedId";
            $details= array();
            $masterDetailLength[$trustedId]= 0;
        }

        $trustedIdDetailDDL[$trustedId]= $detailDDL;
    }

    $warnings= array();
    $expandedRecords= getDetailsForGrid(array(array("trustedId" => $trustedId, "detailsDecoded" => $newExpandedDetails)), $trustedIdDetailDDL, $masterDetailLength, $warnings);

        $returnRecord= $expandedRecords["detailRecords"];

        $updateSQL= "update cutrusteddetail set parms= '$encrypedDetails' where trustedid= '$trustedId' and cu= '$cu'";
        $queryResults= runExecStatement($updateSQL, $dbh);
        $errors= array_merge($errors, $queryResults["error"]);
        $sqls[]= $updateSQL;
    }
    return array("sql" => $sqls, "error" => $errors, "operation" => $mode, "record" => $returnRecord);
}

/**
 * function changeServiceProperty($dbh, $mode)
 * Creates, updates, or remove a bottom-level record for the HOMECUSERVICE thing.
 *
 * @param integer $dbh -- the database connection
 * @param string $mode -- the mode: "delete", "update", and "create"
 * @return array --
 *      $sql -- the SQLs used
 *      $error -- any errors encountered
 *      $operation -- the value of the $mode parameter
 *      $record -- the modified record
 */
function changeServiceProperty($dbh, $mode)
{
    $parameters= array();

    $valid= array("property" => "string");
    if ($mode != "delete")
    {
        $valid["value"]= "string";
        $valid["message"]= "string";
    }
    dms_import_v2($parameters, "BOTTOM_LEVEL", $valid);

    $property= trim($parameters["BOTTOM_LEVEL"]["property"]);
    $value= trim($parameters["BOTTOM_LEVEL"]["value"]);
    $message= trim($parameters["BOTTOM_LEVEL"]["message"]);

    $errors= array();
    if ($property == "")
        $errors[]= "Property is required";

    $serverOptions= getServerOptions();

    $cu= "HOMECU";
    $trustedId= "HOMECUSERVICE";

    $retrievalSQL= "select trustedid, parms from cutrusteddetail where trustedid= '$trustedId' and cu= '$cu'";
    $queryResults= runSelectStatement($retrievalSQL, $dbh, array("trustedid" => "validId", "parms" => "detailsEncoded"));
    $errors= array_merge($errors, $queryResults["error"]);
    $sqls= array($retrievalSQL);

    $detailArray= array();
    $records= array();
    if (count($errors) == 0)
    {
        $expandedRecords= extractDetails($queryResults["record"], $cu, $errors);
        if ($expandedRecords == "" || count($expandedRecords) == 0)
            $expandedDetails= array();
        else
            $expandedDetails= $expandedRecords[0]["detailsDecoded"];

        if ($mode == "delete")
        {
            unset($expandedDetails[$property]);
            unset($expandedDetails["${property}_MSG"]);
        }
        else
        {
            $expandedDetails[$property]= $value;

            if ($message != "")
                $expandedDetails[$property . "_MSG"]= $message;
            else
                unset($expandedDetails[$property . "_MSG"]);
        }

        $encrypedDetails= encryptDetails($expandedDetails, $cu);

        $returnRecord= array(array("property" => $property, "value" => $value, "message" => $message, "text" => $serverOptions[$value], "hasMasterRecord" => true, "trustedId" => $trustedId));

        $updateSQL= "update cutrusteddetail set parms= '$encrypedDetails' where trustedid= '$trustedId' and cu= '$cu'";
        $queryResults= runExecStatement($updateSQL, $dbh);
        $errors= array_merge($errors, $queryResults["error"]);
        $sqls[]= $updateSQL;
    }
    return array("sql" => $sqls, "error" => $errors, "operation" => $mode, "record" => $returnRecord);
}

/**
 * function serviceUpdate($dbh)
 * This should now be an array of key/value sets.
 *
 * key is the homecuService ie IPAY,ENSENTA
 *
 * value {Y, N, U}
 *
 *    N - Service is ONLINE
 *    Y - Service is OFFLINE - and stays down, if the script is set to enable services, this service will NOT change
 *    U - Service is OFFLINE - and if the script is set to enable services, the service value is set to N
 *
 * NOW -- LOOP through all the key/value sets
 *        when action is set to Off -a off, then turn all occurrences of N to U
 *        when action is set to On -a on, then turn all occurrencens of U to N
 * if -s ALL is set, then all services will be affected
 * if -s is an array of services, only those listed services should be affected
 *
 * @param integer $dbh -- the database connection
 * @return array --
 *      $sql -- the SQLs used
 *      $error -- any errors encountered
 *      $operation -- set to "bulkUpdate" for informational purposes
 */
function serviceUpdate($dbh)
{
    $parameters= array();
    dms_import_v2($parameters, "BOTTOM_LEVEL", array("isTurnOff" => "string"));
    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
        array("isTurnOff" => array("type" => "boolean", "required" => true)));

    $isTurnOff= $parameters["BOTTOM_LEVEL"]["isTurnOff"] == "Y";

    $cu= "HOMECU";
    $trustedId= "HOMECUSERVICE";

    $retrievalSQL= "select trustedid, parms from cutrusteddetail where trustedid= '$trustedId' and cu= '$cu'";
    $queryResults= runSelectStatement($retrievalSQL, $dbh, array("trustedid" => "validId", "parms" => "detailsEncoded"));
    $errors= array_merge($errors, $queryResults["error"]);
    $sqls= array($retrievalSQL);

    $detailArray= array();
    $records= array();
    if (count($errors) == 0)
    {
        $expandedRecords= extractDetails($queryResults["record"], $cu, $errors);
        if ($expandedRecords == "" || count($expandedRecords) == 0)
            $expandedDetails= array();
        else
            $expandedDetails= $expandedRecords[0]["detailsDecoded"];

        foreach($expandedDetails as $key => $value)
        {
            $pos= stripos($key, "_MSG");
            $len= strlen($key);

            if ($pos !== false && $len - $pos == 4) // _MSG at end of the string
                continue; // Do not worry about the messages.  It is possible that they could have U or N values and then they would be falsely updated.
            if ($isTurnOff)
            {
                if ($value == "U")
                    $expandedDetails[$key]= "N";
            }
            else
            {
                if ($value == "N")
                    $expandedDetails[$key]= "U";
            }
        }

        $encrypedDetails= encryptDetails($expandedDetails, $cu);

        $updateSQL= "update cutrusteddetail set parms= '$encrypedDetails' where trustedid= '$trustedId' and cu= '$cu'";
        $queryResults= runExecStatement($updateSQL, $dbh);
        $errors= array_merge($errors, $queryResults["error"]);
        $sqls[]= $updateSQL;
    }
    return array("sql" => $sqls, "error" => $errors, "operation" => "bulkUpdate");
}

/**
 * function pullDownOptions($dbh)
 * Pulls in properties from the master list that aren't currently in the list
 *
 * @param integer $dbh -- the database connection
 * @return array --
 *      $sql -- the SQLs used
 *      $error -- any errors encountered
 *      $record -- any additional properties added
 */
function pullDownOptions($dbh)
{
    $parameters= array();
    dms_import_v2($parameters, "BOTTOM_LEVEL", array("cu" => "string", "trustedId" => "string", "options" => "string"));

    $cu= $parameters["BOTTOM_LEVEL"]["cu"];
    $trustedId= $parameters["BOTTOM_LEVEL"]["trustedId"];

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

    $cuClean= $parameters["BOTTOM_LEVEL"]["cu"];
    $trustedIdClean= $parameters["BOTTOM_LEVEL"]["trustedId"];

    $optionsEncoded= $parameters["BOTTOM_LEVEL"]["options"];

    $retrievalSQL= "select parms from cutrusteddetail where trustedid= '$trustedIdClean' and cu= '$cuClean'";
    $queryResults= runSelectStatement($retrievalSQL, $dbh, array("parms" => "detailsEncoded"));
    $errors= array_merge($errors, $queryResults["error"]);
    $sqls= array($retrievalSQL);

    $returnRecords= array();
    $expandedRecords= array();
    $expandedDetails= array();
    if (count($errors) == 0)
    {
        $expandedRecords= extractDetails($queryResults["record"], $cu, $errors);
        $expandedDetails= $expandedRecords[0]["detailsDecoded"];
    }

    if (trim($optionsEncoded) == "")
        $errors[]= "Options are required";
    else
    {
        $options= json_decode($optionsEncoded, true);
        if (is_array($options))
        {
            foreach($options as $record)
            {
                if (isset($record["property"]))
                {
                    $property= stripslashes($record["property"]);
                    $value= stripslashes(strval($record["value"]));
                    $message= stripslashes(strval($record["message"]));
                    $expandedDetails[$property]= $value;
                    $fieldType= isset($record["fieldType"]) ? $record["fieldType"] : "string";
                    $returnRecords[]= array("property" => $property, "fieldType" => $fieldType, "value" => $value, "message" => $message, "hasMasterRecord" => true, "trustedId" => $trustedId);
                }
                else
                {
                    $errors[]= "One or more options rows is not formed correctly";
                    break;
                }
            }
        }
        else
            $errors[]= "Options are not encoded correctly";
    }

    $encryptedDetails= encryptDetails($expandedDetails, $cu);

    $updateSQL= "update cutrusteddetail set parms= '$encryptedDetails' where trustedid= '$trustedIdClean' and cu= '$cuClean'";
    $sqls[]= $updateSQL;
    if (count($errors) == 0)
    {
        $queryResults= runExecStatement($updateSQL, $dbh);
        $errors= array_merge($errors, $queryResults["error"]);
    }

    return array("sql" => $sqls, "error" => $errors, "record" => $returnRecords);
}