<?php
    include dirname(__FILE__) . '/../php_only_scripts/global_after_session.php';
    include dirname(__FILE__) . '/../php_only_scripts/database.php';
    include dirname(__FILE__) . '/../php_only_scripts/global_functions.php';
    
    // Function to check tool stock and then notify appropriate users
    function checkToolStockLevels($db, $userRow){
        // Define to get stock that needs to be notified of being low
        $sql = 'SELECT ts.*, tt.CategoryName, v.VendorEMail, v.VendorName
                FROM (SELECT *, (FLOOR(DATEDIFF(CURRENT_DATE,LastAutoCheckedDate)/CheckFrequencyInDays)-
                                      (DATEDIFF(CURRENT_DATE,LastAutoCheckedDate)/CheckFrequencyInDays)) as LastAutoCheckDayDifference 
                      FROM tools) ts
                LEFT JOIN tooltype tt ON ts.ToolTypeID=tt.ToolTypeID   
                LEFT JOIN vendors v ON ts.VendorID = v.VendorID
                WHERE ts.Amount <= ts.LowReorderLevel
                AND   (ts.LowReorderLevel + ts.HighReorderLevel) != 0
                AND   (ts.LastAutoCheckedDate IS NULL OR
                       ts.LastAutoCheckedDate <=  CURRENT_DATE - INTERVAL ts.CheckFrequencyInDays DAY)
                AND   (ts.LastAutoCheckDayDifference IS NULL OR
                       ts.LastAutoCheckDayDifference = 0.0)
                ORDER BY v.VendorName';

        // Query the database
        $rows = $db->queryDB($sql, $userRow, "checktoollevels occurance 4");

        // Create and init loop variables
        $notificationEMails='';
        $notificationEMailSubject='';
        $notificationEMailContent='';
        $currentVendorEMail='-1';
        $currentVendorName="";
        $currentVendorEMailSubject='';
        $currentVendorEMailContent='';

        // Create the e-mail headers
        $eMailHeaders = "From: vendorsales@eestms.com\r\n" . 
                        "MIME-Version: 1.0\r\n" .
                        "Content-type: text/html; charset=iso-8859-1\r\n";

        // Determine if we have rows to parse
        if ($rows!=null && mysqli_num_rows($rows)>0){
            // Loop through the returned results
            while ($row=mysqli_fetch_array($rows)){
                // Determine if this is a new vendor 
                if ($currentVendorName != $row["VendorName"]){
                    // Now determine if this is the first set
                    if ($currentVendorName == ""){
                        // Start the new e-mail contents
                        $currentVendorEMailContent = 'To whom it may concern,<br><br>' .
                                'We have reached low stock levels of the following ' .
                                'tools to be quoted with "' . $row["VendorName"] . 
                                '" with e-mail of "' . $row["VendorEMail"] . '".<br><br>';
                        $notificationEMailContent = 'All Concerned,<br><br>' .
                                'We have reached low stock levels of the following ' .
                                'items to be quoted.<br>';

                        // Create headers
                        $currentVendorEMailSubject = 'Tool Quote From EES for "' . $row["VendorName"] . '"';
                        $notificationEMailSubject  = 'Tool Quote Needed for "' . $row["VendorName"] . '"';
                    }
                    else {
                        // Finish the e-mail content
                        $currentVendorEMailContent .= ' ' .
                                '<br>Please quote these items at your ealiest convenience.<br><br>' .
                                'Thank You,<br><br>Evolve Engineering Automated System<br>' .
                                'vendorsales@evolveeng.com<br>(303)949-6557';

                        // Attempt to mail it
                        mail($currentVendorEMail,$currentVendorEMailSubject,
                             $currentVendorEMailContent,$eMailHeaders);

                        // Start the new e-mail content
                        $currentVendorEMailContent = 'To whom it may concern,<br><br>' .
                                'We have reached low stock levels of the following ' .
                                'tools to be quoted with "' . $row["VendorName"] . 
                                '" with e-mail of "' . $row["VendorEMail"] . '".<br><br>';

                        // Create a new header
                        $currentVendorEMailSubject = 'Tool Quote From EES for "' . $row["VendorName"] . '"';
                    }

                    // Change the current e-mail and name to be the new vendor we are working on now
                    $currentVendorEMail = $row["VendorEMail"];
                    $currentVendorName = $row["VendorName"];

                    //  Add the vendor e-mail
                    $notificationEMailContent .= '<br>Quote with "' . $row["VendorName"] . '":<br>';
                }

                // Determine what type we have 
                $categoryOf=2;
                if ($row["CategoryName"]=='TOOL'){
                    // Set the caetory of a tool not miscellaneous
                    $categoryOf=1;
                }

                // Add the stock information to the content
                $currentVendorEMailContent .= '++ ' . ($row["HighReorderLevel"]-$row["Amount"]) . ' ' .
                                          'each of Brand: ' . $row["Brand"] . '  Part Number: ' . 
                                          $row["PartNumber"] . ' (' . str_replace(chr(194),"",($row["Name"])) . 
                                          ')<br>';
                $notificationEMailContent .= '++ ' . ($row["HighReorderLevel"]-$row["Amount"]) . ' ' .
                                          ' each of <a href="https://www.eestms.com/' . 
                                          getTMSHome() . '/updateTool.php?c=' . $categoryOf . '&toid=' . 
                                          $row["ToolID"] . '">' . str_replace(chr(194),"",($row["Name"])) . 
                                          '</a><br>';

                // Now create the query to update the last queried date
                $sql = 'UPDATE tools SET ReorderNotificationSentDate = CURDATE()
                        WHERE ToolID = ' . $row["ToolID"] . ';';

                // Update the rows that have just been sent a reorder notification
                $db->queryDB($sql, $userRow, "checktoollevels occurance 2");
            }

            // Determine if we have anything to finish sending for material quote
            if ($currentVendorEMailContent!='' ){
                // Finish the e-mail content
                $currentVendorEMailContent .= '<meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> ' .
                        '<br>Please quote these items at your earliest convenience.<br><br>' .
                        'Thank You,<br><br>Evolve Engineering Automated System<br>' .
                        'vendorsales@evolveeng.com<br>(303)949-6557';

                // Determine if there is an e-mail to send to
                if ($currentVendorEMail!=null && trim($currentVendorEMail)!=""){
                    // Attempt to mail it
                    mail($currentVendorEMail,$currentVendorEMailSubject,
                         $currentVendorEMailContent,$eMailHeaders);
                }
            }

            // get the notification E-Mails
            $notificationEMails .= getStockNotificationEmails ($db, $userRow, "ReceiveToolLowStockNotificationUpdate");

            // Determine if we have anyone in-house that cares
            if ($notificationEMails!='' && $notificationEMailSubject!=''){
                // Finish the e-mail content
                $notificationEMailContent .= ' ' .
                        '<br>Please quote these items at your earliest convenience.<br><br>' .
                        'Thank You,<br><br>Evolve Engineering Automated System<br>' .
                        'vendorsales@evolveeng.com<br>(303)949-6557';

                // Determine if there is an e-mail to send to
                if ($notificationEMails!=null && trim($notificationEMails)!=""){
                    // Attempt to mail it
                    mail($notificationEMails,$notificationEMailSubject,
                         $notificationEMailContent,$eMailHeaders);
                }
            }

        }        
        
        // Now create the query to update the last queried date
        $sql = 'UPDATE tools SET LastAutoCheckedDate = CURDATE() 
                WHERE ToolID IN 
                    (
                        SELECT ToolID FROM 
                                    (SELECT *, (FLOOR(DATEDIFF(CURRENT_DATE,LastAutoCheckedDate)/CheckFrequencyInDays)-
                                               (DATEDIFF(CURRENT_DATE,LastAutoCheckedDate)/CheckFrequencyInDays)) as LastAutoCheckDayDifference 
                                            FROM tools) ts
                        WHERE (LastAutoCheckDayDifference IS NULL OR
                               LastAutoCheckDayDifference = 0.0)
                    );';

        // Update the rows that have just been sent a reorder notification
        $db->queryDB($sql, $userRow, "checktoollevels occurance 3");
    }
    
    // Create and Setup the database
    $db = new Database();
    $db->setupTmsDB();

    // Attempt to get the username and password
    $userName = 'cuser';
    $password = '1Crono2User3!';

    // Attempt to log in with this user
    $currentUser = $db->loginUser($userName, $password, 7777777);

    // If there is only one result for a username/password combo, then they are good to go.
    if ($currentUser != null and mysqli_num_rows($currentUser)==1){
        // Attempt to get the user row
        $userRow = $currentUser->fetch_assoc();

        // Determine if they are the SU company
        if ($userRow["CompanyID"]==1){
            // Check for old attachments and delete thm
            checkToolStockLevels ($db, $userRow);        
        }
    }

    // Exit this code
    exit;
?>       

