<?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 = '0iFKJd=*Eu5i5YYD-w';
            $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) {

            }
        }
    }

    // Create a function to start creation of the setup form
    function CreateSetupForm ($db, $userRow, $inTaskID){
        try {
            if ($inTaskID){
                // 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_with_downloads Form occurance 1");

                // Determine if we got a row
                if ($taskRow){
                    // 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_with_downloads Form occurance 2");

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

                    // Create a query to get the tools used for this job on this machine and exposed length status
                    $sql = 'SELECT tttl.*, asst.MachineID, ' . 
                           '       IF(asst.ExposedLength = tttl.ExposedLength, 1,0) AS ExposedLengthEqual, ' . 
                           '       IF(asst.CurrentToolID = tttl.ToolID, 1,0) AS ToolsMatch ' .
                           'FROM tooltotasklinker tttl ' .
                           'LEFT JOIN assignedtools asst ON tttl.ToolPosition = asst.Position ' .
                           'WHERE tttl.TaskID = ' . $inTaskID . ' ' .
                           'AND (asst.MachineID = ' . $assocTask["MachineID"] . ' OR asst.MachineID IS NULL) ' . 
                           'ORDER BY tttl.ToolPosition;';

    //echo '<script>alert("' . addslashes($sql) . '");</script>';
                    // Attempt to get all of the notes for this category type and ID
                    $toolInformationQuery = $db->queryDB($sql, $userRow, "setup_sheet_with_downloads Form occurance 3");

                    // 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->Blanks($db, $userRow, $assocTask["TaskID"]);
                    $setupSheetTemplate->PostMachining($assocTask);
                    $setupSheetTemplate->TaskNotes($db, $userRow, $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");

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

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

                    // Determine if the directory has already been made 
                    if (file_exists('./temp/MachiningInfo_'. $inTaskID)!=true){
                        // Make the directory for the files
                        mkdir('./temp/MachiningInfo_' . $inTaskID);
                    }
                    
                    // send the output to the screen
                    $setupSheetTemplate->Output('F','./temp/MachiningInfo_'. $inTaskID . '/Setup_Sheet.pdf');

                    // Return a true so they can do something with it
                    return true;
                }
                else {
                    // Let them know the query failed
                    echo '<script>alert("The query to get data for the setup sheet failed.\n\nShowing empty document.");</script>';

                    // Return a false so they can do something with it
                    return false;
                }
            }
            else {
                // Return a false so they can do something with it
                return false;
            }
        } catch (Exception $ex) {
            // Give a message about the exception
            echo '<script>alert("There was an exception thrown.  The exception message is:\n\n' . $ex->getMessage() . '");</script>';
            
            // Return a false so they can do something with it
            return false;
        }
    }
    
    function getDownloads($db, $userRow, $id){
        // Determine if the directory of GCodes has been created or not
        if (!file_exists(dirname(__FILE__) . './temp/MachiningInfo_'. $id)){
            // Make the directory
            mkdir(dirname(__FILE__) . './temp/MachiningInfo_' . $id);
        }
        
        // Attempt to create the ZIP file
        $zipName="./temp/MachiningInfo_" . $id . "/MachiningInfo_" . $id . ".zip";
        $zip = new ZipArchive;

        // Attempt to open the file
        if ($zip->open($zipName, ZipArchive::CREATE)===TRUE){
            // Create the query to get the GCode files from the database
            $sqlSetupFiles = "SELECT ToolStart, ToolStartFileName, GCodeFileName " .
                             "FROM machiningsetups WHERE TaskID = $id ;";

            // Attempt to get all of the setups that have GCodes
            $setupFiles = $db->queryDB($sqlSetupFiles, $userRow, "setup_sheet_with_downloads Form occurance 4");

            // Determine if there were rows for the setups
            if ($setupFiles != null){
                // Loop through the returned results to create the files and add them
                while ($setupFile=mysqli_fetch_array($setupFiles)){
                    // Determine if there is a tool start file
                    if ($setupFile["ToolStartFileName"]!=null && 
                        $setupFile["ToolStart"]!=null){
                        // Download the start file into this temp directory
                        file_put_contents(dirname(__FILE__) . '/temp/MachiningInfo_'. $id . '/' .$setupFile["ToolStartFileName"], $setupFile["ToolStart"]);

                        // Add this tool start image to the zip file
                        $zip->addFile(dirname(__FILE__) . '/temp/MachiningInfo_'. $id . '/' .$setupFile["ToolStartFileName"], $setupFile["ToolStartFileName"]);
                    }
                    
                    // Determine if the gcode file exists
                    if($setupFile["GCodeFileName"]==null || $setupFile["GCodeFileName"]=="" || 
                       !file_exists(dirname(__FILE__) . '/temp/MachiningInfo_'. $id . '/' . $setupFile["GCodeFileName"])){
// TODO: Determine if I care about letting the user know or just want to let it go with no file.
//                            // Let the user know
//                            echo '<script>alert("The GCode file "' . $setupFile["GCodeFileName"] . '" did not get created properly.");</script>';
                    }
                    else {
                        // Add the already created GCode zip file
                        $zip->addFile(dirname(__FILE__) . '/temp/MachiningInfo_'. $id . '/' .$setupFile["GCodeFileName"], $setupFile["GCodeFileName"]);
                    }
                }
            }

            // Create a query to get the images to be shown at the machine 
            $sqlM00Images='SELECT n.* FROM tasks t ' .
                          'LEFT JOIN machiningsetups ms on t.TaskID = ms.TaskID ' .
                          'LEFT JOIN notes n on ms.MachiningSetupID = n.AssociatedCategoryID ' .
                          'WHERE t.TaskID = ' . $id . ' ' .
                          'AND Category="SETUP_M00" ' .
                          'AND FileName LIKE "M00_%" ' .
                          'AND IsSticky=0 ' .
                          'ORDER BY NoteOrder;';

            // Run the query
            $m00s = $db->queryDB($sqlM00Images, $userRow, "setup_sheet_with_downloads Form occurance 5");

            // Determine if there are any rows before attempting to loop through them
            if ($m00s != null && 
                mysqli_num_rows($m00s)>0){
                // Loop through the returned results to create the files and add them
                while ($m00=mysqli_fetch_array($m00s)){
                    // Download the file into this temp directory
                    file_put_contents('./temp/MachiningInfo_'. $id . '/' . $m00["FileName"], $m00["Datum"]);

                    // Add this GCode to the zip file
                    $zip->addFile('./temp/MachiningInfo_'. $id . '/' . $m00["FileName"], $m00["FileName"]);
                }
            }
                
            // Determine if the setup file was successfully made.
            if (file_exists('./temp/MachiningInfo_'. $id . '/Setup_Sheet.pdf')){
                // Add it to the zip file
                $zip->addFile('./temp/MachiningInfo_'. $id . '/Setup_Sheet.pdf', 'Setup_Sheet.pdf');
            }
                
            // Close the zip file
            $zip->close();

            // Prep the file to be sent
            ob_clean();
            ob_end_flush();
            header('Content-Description: File Transfer');
            header("Content-Type: application/zip");
            header('Content-disposition: attachment; filename=MachiningInfo_' . $id . '.zip');
            header('Content-Length: ' . filesize($zipName));

            // Send the file
            readfile($zipName);

            // Variable to store directory that contains the GCodes
            $folder = dirname(__FILE__) . '/temp/MachiningInfo_'. $id . '/';

            // Get the list of all of file names in the folder.
            $files = glob($folder . '/*');

            // Loop through the file list
            foreach($files as $file) {
                // Check for file
                if(is_file($file)) {
                    // Use unlink function to delete the file.
                    unlink($file);
                }
            }
                
            // Use unlink function to delete the folder.
            rmdir($folder);
        }
    }
    
    $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'];
    }

    // try to make the setup sheet and check the result\
    if (CreateSetupForm ($db, $currentUser, $taskID)){
        // Now make the GCode downloads and wrap it all up in a bow
        getDownloads($db, $userRow, $taskID);
    }
?>