Low inventory warning on lane

[otc-narendra]

• Version of CORE? 2.7
• Issue with Office, Lane, or both? Lane
• Is this [mostly] a bug report, feature request, or question? Question

We do not currently use the inventory features so please excuse the newbie questions: 1) With inventory tracking enabled, is there a warning/error displayed on the lane if the item being rung up has low or zero inventory? 2) Is there a mechanism to check remaining inventory of an item on the lane? Kind of like a "Price Check" but to display inventory versus price?

I'm asking because, as I've mentioned before, we are increasingly doing restaurant style things so having a pre-determined inventory level on kitchen food items would make it possible for cashiers to not accidentally oversell.

• Narendra

[gohanman]

There's not any kind of warning or error. With more conventional grocery items the fact that you're ringing it up means the inventory isn't zero. There also isn't an inventory check mechanism although of the two that's probably easier to add.

One potential mismatch here is the inventory isn't ingredient oriented. It could tell you how many cans of tomatoes are available for customers to buy, but it's not going to be able to determine the kitchen ran out of tomatoes and now recipes X, Y, and Z are effectively out of stock.

[otc-narendra]

An inventory check mechanism from the lane would be a huge help!

[otc-narendra]

How would a cashier access this inventory check mechanism?

[gohanman]

Entering INV

[otc-narendra]

I've got v2.8 installed. I downloaded this plugin to try it and when I enable it and enter INV, I get an error: "input unknown". I tried moving the InventoryCheck.php file to the parser\parse folder and added

namespace COREPOS\pos\parser\parse; class InventoryCheck extends Parser

This helped. I could now enter INV in the lane and if I didn't have any items in the transaction it correctly gives the error "Not an item". However, when I select an item in the transaction and then enter INV, nothing happens.

[otc-narendra]

Inventory check seems to work now although I did need to change line 7 in InventoryCheck.php to: class InventoryCheck extends Parser

Question: The inventory check gets data from the InventoryCache table - is the only way this table update from the InventoryTask cron task? If so, how often should this be run? I assume if we want running inventory, it needs to be run every few mins?

[otc-narendra]

Also noticed that the inventory check was not properly accounting for current sales from the dlog table. Lines 62-74 in InventoryCheck.php should read:

    $invP = $dbc->prepare("SELECT SUM(quantity) AS qty
        FROM dlog
        WHERE upc=?
            AND store_id=?
            AND emp_no <> 9999
            AND register_no <> 99
            AND trans_status <> 'X'
    ");
    $inv2 = $dbc->getValue($invP, array($upc, $this->session->get('store_id')));
    $inv2 = $inv2 ? $inv2 : 0;

    return DisplayLib::boxMsg(
        _('Current Inventory: ') . ($inv1 - $inv2),

[otc-narendra]

How would I set up a menu item in fannie to force run the InventoryTask.php task to update the InventoryCache table after updating inventory counts?

[gohanman]

I don't think there's a way to do that. The closest option would be to enter counts per-vendor. The Recalculate Totals button will essential run the inventory task for all that vendors' items.

[otc-narendra]

The vendor counts page did the trick - thanks! One more question: We sell three types of freshly made pizza but from an inventory standpoint, the primary limiting factor is dough (vs. toppings). I tried the trick of creating a "Dough" item that I put into the Deposit field in the item info so that it is automatically added to the transaction when either of the pizzas is rung up but when I try and select the automatically added dough item and check INV on it, it reverts to the main item inventory. I imagine this is expected behavior?

[gohanman]

No, it's not expected behavior. I'm at a bit of a loss why that would be. Nothing in the perpetual inventory system, server or lane side, has any kind of reference to deposits.

[otc-narendra]

Looking at the localtemptrans table for the current transaction the only thing I see is that trans_subtype for the deposit item is "AD" versus "NA" for regular items. I assume that the $this->session->get('LastID') on line 19 in InventoryCheck.php is somehow skipping the deposit item?

[otc-narendra]

My mistake - the InventoryCheck only picks up the last item in the transaction but the deposit item is one row above so scrolling up to it and then entering INV doesn't work! Would it be possible for the INV command to work against the currently selected item in the transaction (or if no selected item then the last item)? That would, I imagine solve this.

I changed line 19 in my local copy of InventoryCheck.php to $this->session->get('currentid') and that did the trick - now the inventory is reported for the selected item.

[gohanman]

My mistake. Should have been currentid all along.

[otc-narendra]

One more issue I'd like some advice on: InventoryCheck.php starts with the count in the InventoryCache table and then subtracts today's sales from the dlog table. If I update the count using the inventory tools on fannie vendor counts screen and hit recalculate totals, the InventoryCache number gets updated with the new count but this does not take into account sales from dlog which means that the InventoryCheck figure reported is the new updated count MINUS current sales. Ideally, once counts are manually updated, the dlog count would no longer be subtracted. Wondering if there is an easy way to do this...

Also, Andy I sent you some code edits to include a "Include suspended" switch on this plugin to count items in suspended transactions. Not sure if you want to merge those edits with the main code base or if these should stay as Our Table specific mods.

[otc-narendra]

I added some code to filter dlog to exclude transactions prior to the last manual count (InventoryCounts.countDate) and it seems to behave as expected. I will let you merge this into the main codebase if you think it's appropriate:

    //Get quantity sold today EXCLUDING sales that occurred prior to the last manual count

    // Get InventoryCounts.countDate for selected upc and storeID WHERE mostRecent = 1
    $invP = $dbc->prepare('SELECT countDate
        FROM ' . $this->session->get('InventoryOpDB') . $dbc->sep() . 'InventoryCounts
        WHERE upc=?
            AND storeID=?
            AND mostRecent = 1');
    $mostrecentcountdate = $dbc->getValue($invP, array($upc, $this->session->get('store_id')));
    if ($mostrecentcountdate === false) {
        $mostrecentcountdate = date('Y-m-d', strtotime('yesterday')); //if no countDate available then use yesterday so all of today's sales are counted
    }

    // Get quantity sold today and filter dlog to only return sales AFTER the most recent inventory count
    $invP = $dbc->prepare("SELECT SUM(quantity) AS qty
        FROM dlog
        WHERE upc=?
            AND store_id=?
            AND tdate>?
            AND emp_no <> 9999
            AND register_no <> 99
            AND trans_status <> 'X'
    ");
    $inv2 = $dbc->getValue($invP, array($upc, $this->session->get('store_id'), $mostrecentcountdate));
    $inv2 = $inv2 ? $inv2 : 0;

[gohanman]

I had the same idea, although I think it can be done without adding an extra query (I'd guess the practical performance difference is negligible if even noticeable).

[otc-narendra]

Thanks Andy! In InventoryCheck.php, the following typos need to be fixed:

Line 52:          WHERE i.upc=?
Line53:                AND i.storeID=? 
Line 75:     $moreRecent = $dbc->getValue($moreRecent, array($upc, $this->session->get('store_id'), $invData['countDate']));

With the above fixes, everything seems to work well and we will test in production this evening and report back with any issues.

[gohanman]

This is included in 2.10