<?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 material stock and then notify appropriate users
    function checkMaterialStockLevels($db, $userRow){
        // Define to get stock that needs to be notified of being low
        $sql = 'SELECT ms.*, v.VendorEMail, v.VendorName FROM (
                    SELECT *, (FLOOR(DATEDIFF(CURRENT_DATE,LastAutoCheckedDate)/CheckFrequencyInDays)-
                               (DATEDIFF(CURRENT_DATE,LastAutoCheckedDate)/CheckFrequencyInDays)) as LastAutoCheckDayDifference 
                            FROM materialstock) ms
                LEFT JOIN vendors v ON ms.VendorID = v.VendorID
                WHERE ms.Amount <= ms.LowReorderLevel
                AND   (ms.LowReorderLevel + ms.HighReorderLevel) != 0
                AND   (ms.LastAutoCheckedDate IS NULL OR
                       ms.LastAutoCheckedDate <=  CURRENT_DATE - INTERVAL ms.CheckFrequencyInDays DAY)
                AND   (ms.LastAutoCheckDayDifference IS NULL OR
                       ms.LastAutoCheckDayDifference = 0.0)
                ORDER BY v.VendorName;';

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

        // 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 = '<br><br>To whom it may concern,<br><br>' .
                                'We have reached low stock levels of the following ' .
                                'items 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 ' .
                                'companies/items to be quoted.<br>';


                        // Create headers
                        $currentVendorEMailSubject = 'Material Quote From EES for "' . $row["VendorName"] . '"';
                        $notificationEMailSubject  = 'Material Quote Needed for "' . $row["VendorName"] . '"';
                    }
                    else {
                        // Finish the e-mail content
                        $currentVendorEMailContent .= ' ' .
                                '<br>For rectangular/square extrusion please call for amount clarification. ' . 
                                'We may want to order full sticks if the price break is good enough.<br><br>' .
                                '<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 = '<br><br>To whom it may concern,<br><br>' .
                                'We have reached low stock levels of the following ' .
                                'items to be quoted with "' . $row["VendorName"] . 
                                '" with e-mail of "' . $row["VendorEMail"] . '".<br><br>';

                        // Create a new header
                        $currentVendorEMailSubject = 'Material 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>';
                }

                // Add the stock information to the content
                $currentVendorEMailContent .= '++ ' . ($row["HighReorderLevel"]-$row["Amount"]) . ' ' .
                                          $row["Units"] . ' of ' . $row["Name"] . '<br>';
                $notificationEMailContent .= '++ ' . ($row["HighReorderLevel"]-$row["Amount"]) . ' ' .
                                          $row["Units"] . ' of <a href="https://www.eestms.com/' . 
                                          getTMSHome() . '/updateStockedMaterial.php?updateStockedMaterialID=' . 
                                          $row["MaterialStockID"] . '">' . $row["Name"] . '</a><br>';

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

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

            // Determine if we have anything to finish sending for material quote
            if ($currentVendorEMailContent!='' ){
                // Finish the e-mail content
                $currentVendorEMailContent .= ' ' .
                        '<br>For rectangular/square extrusion please call for amount clarification. ' . 
                        'We may want to order full sticks if the price break is good enough.<br><br>' .
                        '<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, "ReceiveMaterialLowStockNotificationUpdate");

            // Determine if we have anyone in-house that cares
            if ($notificationEMails!='' && $notificationEMailSubject!=''){
                // Finish the e-mail content
                $notificationEMailContent .= ' ' .
                        '<br>For rectangular/square extrusion please call for amount clarification. ' . 
                        'We may want to order full sticks if the price break is good enough.<br><br>' .
                        '<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 materialstock SET LastAutoCheckedDate = CURDATE()
                WHERE MaterialStockID IN 
                    (
                        SELECT MaterialStockID FROM 
                                    (SELECT *, (FLOOR(DATEDIFF(CURRENT_DATE,LastAutoCheckedDate)/CheckFrequencyInDays)-
                                               (DATEDIFF(CURRENT_DATE,LastAutoCheckedDate)/CheckFrequencyInDays)) as LastAutoCheckDayDifference 
                                            FROM materialstock) ms
                        WHERE (LastAutoCheckDayDifference IS NULL OR
                               LastAutoCheckDayDifference = 0.0)
                    );';

        // Update the rows that have just been sent a reorder notification
        $db->queryDB($sql, $userRow, "checkmateriallevels 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
            checkMaterialStockLevels ($db, $userRow);        
        }
    }

    // Exit this code
    exit;
?>       

