<?php    
    include_once dirname(__FILE__) . '/../global_after_session.php';
    include_once dirname(__FILE__) . '/../database.php';
    include_once dirname(__FILE__) . '/../global_functions.php';

    function addStickyNote($db, $userRow, $inAssociatedCategoryID, $inStickyNoteCategory, $inStickyNoteTitle, $inStickyNoteContent){
        $result = "";
        
        // Set up what the user posted at first, to be changed with logic.
        $associatedCategoryID = $inAssociatedCategoryID;
        $stickyNoteCategory=$inStickyNoteCategory;
        $stickyNoteContent=$inStickyNoteContent;
        $stickyNoteTitle=$inStickyNoteTitle;
        
        try {
            // Ensure that we have something to work on, a title and content to post
            if ($associatedCategoryID>0 && 
                $stickyNoteContent!=null && $stickyNoteContent!=''  && 
                $stickyNoteTitle!=null && $stickyNoteTitle!=''){
                // create a query to determine if we have a task to add the sticky note to as default
                $sql = 'SELECT * FROM tasks ' .
                       'WHERE TaskID = ' . $associatedCategoryID . ';';
                
                // Determine what the category is and change the query to match
                switch ($stickyNoteCategory) {
                    case 'COMPANY':
                        // create a query to determine if we have a company to add the sticky note to
                        $sql = 'SELECT * FROM companies ' .
                               'WHERE CompanyID = ' . $associatedCategoryID . ';';
                        
                        // Breakout of the loop
                        break;

                    case 'COMPANY':
                        // create a query to determine if we have a user to add the sticky note to
                        $sql = 'SELECT * FROM users ' .
                               'WHERE UserID = ' . $associatedCategoryID . ';';
                        
                        // Breakout of the loop
                        break;

                    case 'MATERIAL':
                        // create a query to determine if we have a material to add the sticky note to
                        $sql = 'SELECT * FROM material ' .
                               'WHERE MaterialID = ' . $associatedCategoryID . ';';
                        
                        // Breakout of the loop
                        break;

                    case 'TOOL':
                        // create a query to determine if we have a tool to add the sticky note to
                        $sql = 'SELECT * FROM tools ' .
                               'WHERE ToolID = ' . $associatedCategoryID . ';';
                        
                        // Breakout of the loop
                        break;

                    default:
                        break;
                }

                // Attempt to run the query
                $categoryRow = $db->queryDB($sql, $userRow, "add_sticky_note Form occurance 1");
                
                // Count the rows to ensure we have one
                if (mysqli_num_rows($categoryRow)==1){
                    // Let's create the insert query off of what we have
                    $sql='INSERT INTO notes (IsSticky, AssociatedCategoryID, Category ';
                    if ($stickyNoteTitle!=null) {$sql = $sql . ', Title';}
                    if ($stickyNoteContent!=null) {$sql = $sql . ', Note';}
                    if ($userRow["UserID"]!=null) {$sql = $sql . ', EnteredByID';}
                    $sql=$sql .') VALUES (1, ' . $associatedCategoryID . ', "' . $stickyNoteCategory . '" ';
                    if ($stickyNoteTitle!=null) {$sql = $sql . ', "' . $stickyNoteTitle . '"';}
                    if ($stickyNoteContent!=null) {$sql = $sql . ', "' . $stickyNoteContent . '"';}
                    if ($userRow["UserID"]!=null) {$sql = $sql . ', ' . $userRow["UserID"];}
                    $sql=$sql .');';

                    // Attempt to run the query and get the response
                    if ($db->queryDB($sql, $userRow, "note_presentation Form occurance 3.")){
                        // Get the most recent ID (note)
                        return mysqli_insert_id($db->getDBConnection());
                    }
                    else {
                        // Send a message back of the failure
                        return 'ABORT STICKY NOTE (add_sticky_note)::There was an error ' . 
                               'trying to add the sticky note to the database.  \n\n SQL:' .
                               $sql;
                    }
                }
                else {
                    // Send and error message back
                    return 'ABORT STICKY NOTE (add_sticky_note)::There is no ' . 
                            $stickyNoteCategory . ' to add the note to. Aborted.';
                }
            }
            else {
                // Determine which was the problem
                if ($associatedCategoryID<1){
                    // Send and error message back
                    return 'ABORT STICKY NOTE (add_sticky_note)::There is no ' . 
                            $stickyNoteCategory . ' to add a sticky note to. Aborted.';
                }
                else if ($stickyNoteContent==null||$stickyNoteContent==''){
                    // Send and error message back
                    return 'ABORT STICKY NOTE (add_sticky_note)::There is no content ' . 
                              'to add for the sticky note. Aborted.';
                }
                else if ($stickyNoteTitle==null||$stickyNoteTitle==''){
                    // Send and error message back
                    return 'ABORT STICKY NOTE (add_sticky_note)::There is no title ' . 
                              'to add for the sticky note. Aborted.';
                }
            }
        } catch (Exception $ex) {
            // Set up an error message for the exception
            return 'ABORT STICKY NOTE (add_sticky_note)::There was an error ' . 
                      'trying to add the note to the ' . $stickyNoteCategory . 
                      '.\n\n The error is: \n' . $ex->getMessage();
        }

        // Return whatever we got
        return $result;
    }
    
    function addRecipients($db, $userRow, $inNoteID, $inRecipients){
        $result = $inNoteID;
        
        // Set up what the user posted at first, to be changed with logic.
        $noteID = $inNoteID;
        $recipients=$inRecipients;
        
        try {
            // Ensure that we have something to work on, a not ID and the CSV string of user IDs
            if ($noteID>0 && 
                $recipients!=null && $recipients!=''){
                // create a query to determine if we have a note to add recipients to
                $sql = 'SELECT * FROM notes ' .
                       'WHERE NoteID = ' . $noteID . ';';
                
                // Attempt to run the query
                $noteRow = $db->queryDB($sql, $userRow, "add_sticky_note Form occurance 4");
                
                // Count the rows to ensure we have one
                if (mysqli_num_rows($noteRow)==1){
                    // Determine if everybody is selected
                    if (strpos($recipients,'-1')!==false){
                        // Create a query to insert everyone in the SU company for this note
                        $sql='INSERT INTO stickynoteusers (NoteID, SendToID) ' .
                             'SELECT ' . $noteID . ', UserID FROM users WHERE SUEmployee=1 AND AccountStatus="ACTIVE";';

                        // Attempt to run the query and get the response
                        if ($db->queryDB($sql, $userRow, "note_presentation Form occurance 5.")){
                        }
                        else {
                            // Send a message back of the failure
                            $result = 'ABORT STICKY NOTE (add_sticky_note)::There was an error ' . 
                                      'trying to add to the DB some recipients of the sticky note.';
                        }
                    }
                    else {
                        // Create an array of recipient user IDs
                        $users = explode (",", $recipients); 

                        // Attempt to loop through the users
                        foreach ($users as &$user) {
                            // Let's create the insert query off of what we have
                            $sql='INSERT INTO stickynoteusers (NoteID, SendToID) ' .
                                 'VALUES (' . $noteID . ', ' . $user . ');';

                            // Attempt to run the query and get the response
                            if ($db->queryDB($sql, $userRow, "note_presentation Form occurance 5.")){
                            }
                            else {
                                // Send a message back of the failure
                                $result = 'ABORT STICKY NOTE (add_sticky_note)::There was an error ' . 
                                          'trying to add to the DB some recipients of the sticky note.';
                            }
                        }
                    }
                }
                else {
                    // Send and error message back
                    return 'ABORT STICKY NOTE (add_sticky_note)::There is no sticky ' . 
                            'note to add recipients to. Recipients Aborted.';
                }
            }
            else {
                // Send and error message back
                return 'ABORT STICKY NOTE (add_sticky_note)::There is no sticky ' . 
                        'note to add recipients to. Recipients Aborted.';
            }
        } catch (Exception $ex) {
            // Set up an error message for the exception
            return 'ABORT STICKY NOTE (add_sticky_note)::There was an error ' . 
                      'trying to add the recipients to the sticky  note. ' .  
                      '.\n\n The error is: \n' . $ex->getMessage();
        }

        // Return whatever we got
        return $result;
    }
    
    $db = new Database();
    $db->setupTmsDB();

    // Set defaults for the passed in variables
    $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);
    if (mysqli_num_rows($currentUser)!=1){
        echo 'ABORT STICKY NOTE (add_sticky_note)::Your session has expired.  ' . 
             'Refresh the page before adding a sticky note.';
    }
    else {
        // Set defaults for the passed in variables
        $categoryID=-1;
        $stickyNoteCategory='TASK';
        $stickyNoteTitle='';
        $stickyNoteContent='';

        // Check for the category ID
        if (isset($_GET['categoryID']) ){
            // Hold on to the category ID
            $categoryID = $_GET['categoryID'];
        }

        // Check for the category type
        if (isset($_GET['stickyNoteCategory']) ){
            // Hold on to the category type
            $stickyNoteCategory = $_GET['stickyNoteCategory'];
        }

        // Check for the sticky note title
        if (isset($_GET['stickyNoteTitle']) ){
            // Hold on to the sticky note title
            $stickyNoteTitle = htmlspecialchars($_GET['stickyNoteTitle']);
        }

        // Check for the sticky note content
        if (isset($_GET['stickyNoteContent']) ){
            // Hold on to the sticky note content
            $stickyNoteContent = htmlspecialchars($_GET['stickyNoteContent']);
        }

        // Attempt to get the user row
        $userRow = $currentUser->fetch_assoc();

        // Go and get the machines and all of their tasks
        $stickyNoteInfo = addStickyNote($db, $userRow, $categoryID, $stickyNoteCategory, $stickyNoteTitle, $stickyNoteContent);
        
        // Determine if there is a sticky note
        if ((int)trim($stickyNoteInfo)>0){
            // Check for the user to assign this sticky note to
            $stickyNoteTo=-1;
            if (isset($_GET['stickyNoteTo']) ){
                // Hold on to the user to assign this sticky note to
                $stickyNoteTo = $_GET['stickyNoteTo'];
                
                // Attempt to add the recipients
                $stickyNoteInfo = addRecipients($db, $userRow, $stickyNoteInfo, $stickyNoteTo);            
            }
        }
        
        // Return the note ID
        echo $stickyNoteInfo;
    }
?>   

