<?php



/* Connect to the database */
require_once("$rootDir/classes/SqlConnect.php");


/* Check if the caller id number is in the database, if yes fetch Entry ID */
if ($result = $dbSupport->query("
SELECT * FROM `ost_form_entry_values`
WHERE value = '$callerID'"
)) {

    if ($result->num_rows) {
        while($row = $result->fetch_assoc()) {
            $isKnownUser = 1;
            $entryID=$row['entry_id'];
            $rowCount = $result->num_rows;
            $message = "Number $callerID is in the database. User Enrty ID is $entryID";
            outputVerbose(3,$message);
            $message = "Query successfull, returned $rowCount row(s)";
            outputVerbose(4,$message);
        }

    } else {
        $isKnownUser = 0;
        $message = "Number $callerID is not in the database.";
        outputVerbose(3,$message);
        $message = "Query successfull, returned 0 row(s)";
        outputVerbose(4,$message);
    }
    $result->free();
}





/* FROM HERE I NEED TO SEE IF THIS CAN BE DONE MORE EFFIECNTLY, I WILL ALSO CHNAGE IT TO OBJECT ORENTED */

if ($EntryID!==null){

// Get User's ID
    $sql1 = ("SELECT * FROM ost_form_entry WHERE id = '".$EntryID."'");
    $result = $conn->query($sql1);
    while($row = $result->fetch_assoc()) {
        $userId=$row['object_id'];
    }

            // Get User's Full Name
            $sql2 = ("SELECT * FROM `ost_user` WHERE id = '".$userId."'");
            $result = $conn->query($sql2);
                while($row = $result->fetch_assoc()) {
                        $UserFullName=$row['name'];
                        $UserOrgID=$row['org_id'];
                        }

            // Get User's Email
            $sql3 = ("SELECT * FROM ost_user_email where user_id = '".$userId."'");
            $result = $conn->query($sql3);
                while($row = $result->fetch_assoc()) {
                        $UserEmail=$row['address'];
                        }

            // Get Organization Name
            $sql4 = ("SELECT * FROM ost_organization where id = '".$UserOrgID."'");
            $result = $conn->query($sql4);
                while($row = $result->fetch_assoc()) {
                        $UserOrganization=$row['name'];
                        }


            // Check if User has any open requests
            $sql5 = ("SELECT * FROM `ost_ticket` WHERE user_id = '".$userId."' AND status_id = 1");
            $result = $conn->query($sql5);
            if ($result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {
                        $hasOpenRequests=Yes;
                }
            }
            else {
                $hasOpenRequests=No;
            }
            $agi->Set_Variable("hasOpenRequests","${hasOpenRequests}");

            $conn->close();
    }


// If Number is not in database
    else {
            $agi->Verbose("** Number IS NOT in Database, Setting Variables and Routing **");
            $agi->Set_Variable("isKnownUser","0");
            $agi->Set_Variable("userId","1");
            $agi->Set_Variable("UserEmail","robots@sict.com.mt");
            $agi->Set_Variable("UserName","Unkown User");

            }
?>