Low Stock Level Email

[sw0857]

The default contribution (http://www.oscommerce.com/community/contributions,1891) is able to send out email alert whenever hit preset DB value STOCK_REORDER_LEVEL. What if I'd like to have variable quantity such as last 30 days purchase? Any help will be welcome thanks in advance!

 

/catalog/checkout_process.php

 

$warning_stock = STOCK_REORDER_LEVEL;

$current_stock = $stock_left;

 

$low_stock_email = '<b>Low stock warning:</b> ' . $order->products[$i]['name'] . "\n" . '<b>Model No.:</b> ' . $order->products[$i]['model'] . "\n" . '<b>Quantity:</b> ' . $stock_left . "\n" . '<b>Product URL:</b>' . HTTP_SERVER . DIR_WS_CATALOG . 'product_info.php?products_id='. $order->products[$i]['id'] . "\n\n" . '<b>Current Low order limit is ' . $warning_stock . ' units</b>';

$low_stock_subject = 'Low Stock Warning: ' . $order->products[$i]['name'] . ' ' . 'Model No :' . $order->products[$i]['model'];

 

if ($current_stock <= $warning_stock) {

tep_mail(STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS, $low_stock_subject, $low_stock_email, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS); }

[sw0857]

The default contribution (http://www.oscommerce.com/community/contributions,1891) is able to send out email alert whenever hit preset DB value STOCK_REORDER_LEVEL. What if I'd like to have variable quantity such as last 30 days purchase? Any help will be welcome thanks in advance!

 

/catalog/checkout_process.php

 

$warning_stock = STOCK_REORDER_LEVEL;

$current_stock = $stock_left;

 

$low_stock_email = '<b>Low stock warning:</b> ' . $order->products[$i]['name'] . "\n" . '<b>Model No.:</b> ' . $order->products[$i]['model'] . "\n" . '<b>Quantity:</b> ' . $stock_left . "\n" . '<b>Product URL:</b>' . HTTP_SERVER . DIR_WS_CATALOG . 'product_info.php?products_id='. $order->products[$i]['id'] . "\n\n" . '<b>Current Low order limit is ' . $warning_stock . ' units</b>';

$low_stock_subject = 'Low Stock Warning: ' . $order->products[$i]['name'] . ' ' . 'Model No :' . $order->products[$i]['model'];

 

if ($current_stock <= $warning_stock) {

tep_mail(STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS, $low_stock_subject, $low_stock_email, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS); }

 

I replaced STOCK_REORDER_LEVEL with DB query but can't get it working still. Query generates right number in phpmyadmin... Anyone's able to share some thought?

 

$warning_stock = "SELECT SUM(op.products_quantity) FROM " . TABLE_ORDERS . " o INNER JOIN " . TABLE_ORDERS_PRODUCTS . " op ON o.orders_id = op.orders_id INNER JOIN " . TABLE_ORDERS_STATUS_HISTORY . " osh ON o.orders_id = osh.orders_id WHERE osh.orders_status_id = 1 AND o.date_purchased > DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)";

 

Another strange I found is my email notification get Resource id #65 on $warning_stock