<?php
    require_once ('setup_sheet_template.php');
//    require_once ('../php_only_scripts/global_after_session.php');
//    require_once ('../php_only_scripts/database_pdf.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){
            $result=null;

            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===FALSE &&
                        $_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>');
                        }
                    }
                }
            }
            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
                $result = null;
            }
            finally {
                // Return the row, if there is one
                return $result;
            }
        }

        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) {

            }
        }
    }

    // Create a function to start creation of the setup form
    function CreateSetupForm ($db, $userRow, $inTaskID){
        // Determine if there is a task ID to work on
        if ($inTaskID>0){
            // Create the query to get the the task and the user that created the task
            $sql = 'SELECT t.*, ' .
                          't2.Title AS ParentTitle, ' .
                          't3.Title AS FixtureTitle, ' .
                          'c.CompanyName, c.CompanyRowColor, ' . 
                          'ctl.CopiedFromTaskID, ' .
                          'u.FirstName, u.LastName, ' . 
                          'up.FirstName AS ProgrammerFirstName, up.LastName AS ProgrammerLastName, ' . 
                          'upcb.FirstName AS ProgramCheckedByFirstName, upcb.LastName AS ProgramCheckedByLastName, ' . 
                          'm.MachineID, m.Name as MachineName, ss.Name AS SSName, ' . 
                          'ss.Description as SSDescription, ss.RowColor AS SSRowColor ' .
                   'FROM tasks t ' . 
                   'LEFT JOIN tasks t2 ON t.ParentID = t2.TaskID ' .
                   'LEFT JOIN tasks t3 ON t.FixtureToUseID = t3.TaskID ' .
                   'LEFT JOIN secondarystatuses ss ON t.SecondaryStatusID = ss.SecondaryStatusID ' .
                   'LEFT JOIN tasktomachinelinker tm ON t.TaskID = tm.TaskID ' .
                   'LEFT JOIN copiedtaskslinker ctl ON t.TaskID = ctl.CopiedToTaskID ' .
                   'LEFT JOIN machines m ON tm.MachineID = m.MachineID ' .
                   'LEFT JOIN users u ON t.CreatedByID = u.UserID ' .
                   'LEFT JOIN users up ON t.ProgrammerID = up.UserID ' .
                   'LEFT JOIN users upcb ON t.ProgramCheckedByID = upcb.UserID ' .
                   'LEFT JOIN companies c ON t.CompanyID = c.CompanyID ' .
                   'WHERE t.TaskID=' . $inTaskID . ';';

            // Attempt to change all of the rows that need to be changed
            $taskRow = $db->queryDB($sql, $userRow, "setup_sheet Form occurance 1");
            
            // Determine if we got a row
            if ($taskRow!==FALSE && mysqli_num_rows($taskRow)>0){
                // Add a try catch to catch any errors attempting to make the setup sheet
                try {
                    // Attempt to get the user row
                    $assocTask = $taskRow->fetch_assoc();

                    // Set the assoc as null for now
                    $assocChildrenLowerThanMachining=null;

                    // Determine if this is a parent
                    if ($assocTask["ParentID"]!=0){
                        // Create a query to count the every child task in a lower state than machining for this as the parent discluding this one
                        $sql = 'SELECT COUNT(*) AS ChildrenUnderMachining ' .
                               'FROM tasks t ' .
                               'LEFT JOIN status s ON t.StatusID = s.StatusID ' .
                               'WHERE t.ParentID=' . $assocTask["ParentID"] . ' ' .
                               'AND s.ProcessOrder < (SELECT ProcessOrder FROM status WHERE Name="Machining") ' .
                               'AND t.TaskID<>' . $inTaskID . ';';

                        // Attempt to get all of the notes for this category type and ID
                        $childrenLowerThanMachining = $db->queryDB($sql, $userRow, "setup_sheet Form occurance 3");

                        // Attempt to get the amount of children
                        $assocChildrenLowerThanMachining = $childrenLowerThanMachining->fetch_assoc();
                    }

                    // Get the setup sheet started
                    $setupSheetTemplate = new setup_sheet_template();
                    $setupSheetTemplate->DocSetup();
                    $setupSheetTemplate->AliasNbPages();
                    $setupSheetTemplate->AddPage('', '', 0, $assocTask["SSRowColor"]);

                    // Instanciation of inherited class
                    $setupSheetTemplate->GeneralTaskInfo($db, $userRow, $assocTask, $assocChildrenLowerThanMachining);
                    $setupSheetTemplate->MachiningSetup($db, $userRow, $assocTask);
                    $setupSheetTemplate->PostMachining($assocTask);
                    $setupSheetTemplate->AddPinkNotes($db, $userRow, $assocTask["CopiedFromTaskID"]);

                    // Separate from the main page(s)
                    $setupSheetTemplate->AddPage();

                    // Start adding all of the setups for this task
                    $setupSheetTemplate->IndividualSetups($db, $userRow, $assocTask);

                    // Add all of the individual drawings that they have specified
                    $setupSheetTemplate->FullPageNoteImages($db, $userRow, $assocTask["TaskID"], "TASK");

                    // send the output to the screen
                    $setupSheetTemplate->Output();
//                    $setupSheetTemplate->Output('F','Setup_Sheet.pdf');
                
                    // Return a true so they can do something with it
                    return true;
                } 
                catch (Exception $ex) {
                    // Let them know the query failed
                    echo '<script>alert("There was an error creating the setup sheet.\n\nThe error was:\n\n' . $ex->getMessage() . '\n\nAborting."); window.close();</script>';
                    
                    // return a false result because of exception
                    return false;
                }
            }
            else {
                // Let them know the query failed
                echo '<script>alert("The query to get data for the setup sheet failed.\n\nAborting."); window.close();</script>';
                
                // Return a false so they can do something with it
                return false;
            }
        }
        else {
            // Let the user know that this is happening
            echo '<script>alert("There was no Task ID passed to make run sheet.\n\nAborting."); window.close();</script>';
            
            // Return a false so they can do something with it
            return false;
        }
    }
    
    $db = new Database();
    $db->setupTmsDB();
    
    // Set defaults for the passed in variables
    $taskID=-1;
    $sessionID=-1;

    // check the passed in session ID
    if (isset($_GET['sessionID']) ){
        // get the passed in session ID
        $sessionID = $_GET['sessionID'];
    }

    // Attempt to log in with this user
    $currentUser = $db->getLoggedInSessionUser($sessionID);
    
    // Check for the task ID
    if (isset($_GET['tid']) ){
        // Hold on to the task ID
        $taskID = $_GET['tid'];
    }

    // Go and get the machines and all of their tasks
    CreateSetupForm ($db, $currentUser, $taskID);
?>