<?php
class Database {
    protected $dbName = '';
    protected $dbConnection = null;
    protected $databaseName = '';
    protected $queryDbName = '';
    protected $mainUserName = '';
    protected $mainPW = '';
    
    protected $loggedInUserRow = '';
    protected $secondaryDB = '';
    
    public function setupTmsDB(){
        // Close the connection if there is one
//        $this->closeConnection();
        
        // Hold onto the main UN and PW
        $this->databaseName = 'localhost';

        // Active Sample below        
//        $this->mainUserName = 'tmsadm_samp_usr';
//        $this->mainPW = 'EES_SuperPass1#@!';
//        $this->queryDbName = 'tmsadm_tms_sample';
        // Rev2 Below
//        $this->mainUserName = 'tmsadm_rev2_usr';
//        $this->mainPW = 'EES_SuperPass1!@#';
//        $this->queryDbName = 'tmsadm_tms_rev2';
        // Real TMS Below
        $this->mainUserName = 'tmsadm_tms_adm';
        $this->mainPW = '!1114S68sZ88z!';
        $this->queryDbName = 'tmsadm_tms';
        
        // Assign the output string a successful connection message
        $output = ''; 

        try {
            // Attempt to connect to the database
            $this->dbConnection = mysqli_connect($this->databaseName, $this->mainUserName, $this->mainPW);

            // check to see if connection happened
            if (!$this->dbConnection) 
            { 
                // Assign the output string as to the fact that we could not connect to the DB
                $output = 'Unable to connect to the database server.'; 
            }
            else{
                // Try and check the changing of the character encoding
                if (!mysqli_set_charset($this->dbConnection, 'utf8')) 
                { 
                    // Assign the output string as to unable to set the encoding
                    $output = 'Unable to set database connection encoding.'; 
                }
                else {
                    // Attempt to select the database passed in as the one to work in.
                    if (!mysqli_select_db($this->dbConnection, $this->queryDbName)) 
                    { 
                        // Assign output string as to unable to select the DB
                        $output = 'Unable to locate the database "' . $this->queryDbName . '"'; 
                    }            
                }
            }

        } catch (Exception $ex) {
            // Assign the output string as to what happened
            $output = 'Unable to connect to the database server.'; 
        }

        // Let the user know of any problems
        echo $output;

        // Return the database link
        return $this->dbConnection;
    }
    
    function loginUser($inUsername, $inPassword, $inSessionId) {
        $row=null;

        try {
            // check to see if we have a link to a database
            if ($this->dbConnection) { 
                // Create the query to run
                $sql='SELECT * FROM users WHERE UserName = "' . $inUsername . 
                     '" AND Password = "' . $inPassword . '";';
                
                // Query the database for the user 
                $row = mysqli_query ($this->dbConnection, $sql);
                
                // If there is only one result for a username/password combo, then they are good to go.
                if ($row == null){
                    return $row;
                }
                elseif (mysqli_num_rows($row)==1){
                    // Remove any other users that are logged into by this session
                    $this->logoutSession($inSessionId);
                    
                    // Create the update query to run
                    $sql='UPDATE users SET LoggedIn=1, LoggedInSessionId="' . $inSessionId . 
                         '", LoggedInDate=now() WHERE UserName="' . $inUsername . '";';
                            
                    // Since they have a row, time to update it with the most recent login information
                    mysqli_query ($this->dbConnection, $sql);
                }
            }
        } catch (Exception $ex) {
            echo (':In the exception:');
            // Assign the output string as to what happened
            return $row;
        }

        // Assign the logged in user as this one
        $this->loggedInUserRow = $row;
        
        // Return the row, if there is one
        return $row;
    }

    function logoutSession($inSessionId) {
        $success=false;

        try {
            // check to see if we have a link to a database
            if ($this->dbConnection) { 
                // Create the update query to run
                $sql='UPDATE users SET LoggedIn=0, LoggedInSessionId=null, ' . 
                     'LoggedInDate=now() WHERE LoggedInSessionId="' . $inSessionId . '";';

                // Since they have a row, time to update it with the most recent login information
                if(mysqli_query ($this->dbConnection, $sql)){
                    // Set success to true
                    $success=true;
                }
            }
        } catch (Exception $ex) {
            echo (':In the exception:');
            // Assign the output string as to what happened
            return $success;
        }

        // Return the row, if there is one
        return $success;
    }
    
    function getLoggedInSessionUser($inSessionId) {
        $loggedInUserRow=null;

        try {
            // check to see if we have a link to a database
            if ($this->dbConnection) { 
                // Create the query
                $sql = 'SELECT * FROM users ' .
                       'WHERE LoggedInSessionId="' . $inSessionId . '" ' .
                       'AND LoggedIn=1 ' .
                       'AND AccountStatus="ACTIVE";';
                
                // Query the database for the user 
                $loggedInUserRow = mysqli_query ($this->dbConnection, $sql);
            }
        } catch (Exception $ex) {
            echo (':In the exception:');
            // Assign the output string as to what happened
            return $loggedInUserRow;
        }

        // Return the row, if there is one
        return $loggedInUserRow;
    }
    
    function getTypeAsArray($table, $column, $addAllOption){
        try {
            // Initialize the return
            $result = null;

            // Create the array of type values by querying the database
            $sql = "SHOW COLUMNS FROM " . $table . " LIKE '" . $column . "';";
            $sqlResult = mysqli_query ($this->dbConnection, $sql);  
            $sqlResultAssocRow = $sqlResult->fetch_assoc();
            $typeEnumAsString = $sqlResultAssocRow['Type'];
            preg_match('/enum\((.*)\)$/', $typeEnumAsString, $matches);
            
            // assign the enums to an array by explode
            $typesEnumArray = explode(',', $matches[1]);

            // Determine if the ALL option is added
            if ($addAllOption) {
                // Add ALL to the beginning array
                $typesEnumArray[] = "'ALL TYPES'";
            }
            
            // Return the array that was hopefully created
            return $typesEnumArray;
        } catch (Exception $ex) {
            // Assign the output string as to what happened
            return $result;
        }

        // Return whatever we got
        return $result;
    }

    function getTypeAsSelectOptions($table, $column, $selectName, $selected, $onChange, $style, $addAllOption, $fieldsDisabled){
        try {
            // Initialize the return
            $result = '';

            // Get the types array from the table
            $typesEnumArray = $this->getTypeAsArray($table, $column, $addAllOption);
            
            // Determine if we have an onchange event
            $onChangeHTML = '';
            if ($onChange!=null){
                // Set the onChange of the select to be what was passed in
                $onChangeHTML = $onChange;
            }
            
            // Loop through values, clean them up and create the combo box
            foreach($typesEnumArray as $key => $loopType) {
                // Trim it down
                $loopType=trim($loopType, "'");

                // Determine what the user's level is that we are editing
                if ($loopType==$selected){
                    // Set this as the selected one
                    $selected = "selected";
                }
                
                // Add this option
                $result = '<option value="' . $loopType . '" ' . $selected . '>' . $loopType . '</option>' . $result;
            }

            // Start and finish the select statement
            $result='<select id="' . $selectName . '" name="' . $selectName . '" style="' . $style . '" onchange="' . $onChangeHTML . '" ' . $fieldsDisabled . '>' . $result . '</select>';
        } catch (Exception $ex) {
            // Assign the output string as to what happened
            return $result;
        }

        // Return whatever we got
        return $result;
    }

    public function queryDB($query, $userRow, $comment){
        try {
            // Determine if we have a query to run
            if ($query != ''){
                // Query the database for the user's companies tasks
                $result = mysqli_query ($this->dbConnection, $query);
                
                // Run the appropriate query and get a response
                if ($result &&
                    $_SESSION['DebugLevel']=='ON'){ // NONE, INSERT, UPDATE, DELETE, ALL
                    
                    // Let's create the insert query off of what we have
                    $sql='INSERT INTO notes (';
                    $sql = $sql . 'Category';
                    if ($userRow!=null && $userRow["UserID"]!=null) {$sql = $sql . ', EnteredByID';}
                    if ($comment!=null) {$sql = $sql . ', Note';}
                    $sql=$sql .') VALUES (';
                    $sql = $sql . '\'DEBUG_DB\'';
                    if ($userRow!=null && $userRow["UserID"]!=null) {$sql = $sql . ', ' . $userRow["UserID"];}
                    if ($comment!=null) {$sql = $sql . ', \'Comment: ' . $comment . ' --- SQL: ' . $query . '\'';}
                    $sql=$sql .');';

                    // Attempt to run the query and evaluate the response
                    $debugResults = null;
                    $debugResults = mysqli_query($this->dbConnection, $sql);
                    if (!$debugResults){
                        // Popup that debug wasn't successful
                        echo ('<script>alert(\'DB Debug not was not entered.  Note = UserID: ' . 
                              $userRow["UserID"] . ' --- Comment: ' . $comment . ' --- SQL: ' . 
                              $query . '\');</script>');
                    }
                }

                // Return the row, if there is one
                return $result;
            }
        } catch (Exception $ex) {
            // Run the appropriate query and get a response
            if ($_SESSION['DebugLevel']=='ON'){ // NONE, INSERT, UPDATE, DELETE, ALL

                // Let's create the insert query off of what we have
                $sql='INSERT INTO notes (';
                $sql = $sql . 'Category';
                if ($userRow!=null && $userRow["UserID"]!=null) {$sql = $sql . ', EnteredByID';}
                if ($comment!=null) {$sql = $sql . ', Note';}
                $sql=$sql .') VALUES (';
                $sql = $sql . '\'DEBUG_DB\'';
                if ($userRow!=null && $userRow["UserID"]!=null) {$sql = $sql . ', ' . $userRow["UserID"];}
                if ($comment!=null) {$sql = $sql . ', \'Comment: ' . $comment . ' --- SQL: ' . $query . '\'';}
                $sql=$sql .');';

                // Attempt to run the query and evaluate the response
                $debugResults = null;
                $debugResults = mysqli_query($this->dbConnection, $sql);
                if (!$debugResults){
                    // Popup that debug wasn't successful
                    echo ('<script>alert(\'DB Debug not was not entered.  Note = UserID: ' . 
                          $userRow["UserID"] . ' --- Comment: ' . $comment . ' --- SQL: ' . 
                          $query . '\');</script>');
                }
            }

            // Assign the output string as to what happened
            return null;
        }
    }
    
    public function getLoggedInUser(){
        // Return any user that has logged in for this session
        return $this->loggedInUserRow;
    }
    
    public function getDBConnection(){
        // Return the DB Connection
        return $this->dbConnection;
    }
    
    public function closeConnection(){
        // Just wrap it in a try in case there is no connection
        try{
            // Determine if we have an object
            if ($this->dbConnection){
                // Close the DB Connection
                mysql_close($this->dbConnection);
            }
        } catch (Exception $ex) {

        }
    }
    
    public function retrieveTasks($user) {
        try {
            // Attempt to get the user row
            $row = $user->fetch_assoc ();

            // Setup a variable to hold the query string
            $query = '';

            // check to see if we have a link to a database
            switch ($row["UserLevel"]){
                case 'SU':
                    // Set the query to be performed as all in the company for an admin
                    $query = 'SELECT * FROM Tasks;';

                    // Break out of this case statement
                    break;
                case 'EXECUTIVE':
                    // Set the query to be performed as all in the company for an admin
                    $query = 'SELECT * FROM Tasks WHERE UserID in (SELECT UserID FROM Users ' .
                             'where CompanyID=' . $user("CompanyID") . ');';

                    // Break out of this case statement
                    break;
                case 'MANAGEMENT':
                    // Set the query to be performed as all in the company for an admin
                    $query = 'SELECT TaskID, StatusID, UserID, CreatedByID, TypeID, ' .
                             'Priority, Invoiced, Completed, Closed, Title, Entry, DueDate FROM ' .
                             'Tasks WHERE UserID in (SELECT UserID FROM Users where CompanyID=' . 
                             $user("CompanyID") . ');';

                    // Break out of this case statement
                    break;
                case 'TYPICAL' :
                    // Set the query to be performed as all in the company for an admin
                    $query = 'SELECT TaskID, StatusID, UserID, CreatedByID, TypeID, ' .
                             'Priority, Invoiced, Completed, Closed, Title, Entry, DueDate FROM ' .
                             'Tasks WHERE UserID in (SELECT UserID FROM Users where CompanyID=' . 
                             $user("CompanyID") . ');';

                    // Break out of this case statement
                    break;
                case 'MINIMAL' :
                    // Set the query to be performed as all in the company for an admin
                    $query = 'SELECT TaskID, StatusID, UserID, CreatedByID, TypeID, ' .
                             'Priority, Invoiced, Completed, Closed, Title, Entry, DueDate FROM ' .
                             'Tasks WHERE UserID in (SELECT UserID FROM Users where CompanyID=' . 
                             $user("CompanyID") . ');';

                    // Break out of this case statement
                    break;
            }

            // Determine if we have a query to run
            if ($query != ''){
                // Query the database for the user's companies tasks
                $result = mysqli_query ($this->dbConnection, $query);

                // Return the row, if there is one
                return $result;
            }
        } catch (Exception $ex) {
            // Assign the output string as to what happened
            return null;
        }
    }
}

//    
?>       
