Admin error? Deleting Pending orders.....

[mb000000]

As per this topic (http://forum.virtuemart.net/index.php?topic=100888.msg334050#msg334050) I see with VM2.0.20b that I'm having the same problem. In summary, the problem is that deleting orders does not seem to update the ordered quantites of products included in the orders.  This has implications for in/out of stock logic which will calculate that products may be out of stock when, in fact, that are in stock.

I accept that perhaps this is an Admin error (i.e. mine) and that perhaps I should be setting Pending orders to Cancelled first before deletion, but the fact remains that the system allows it (deletion of Pending orders) and now the product "on order" levels are wrong. 

I suggest that either the system should not allow deleting orders other than those with a status of Cancelled or in the tools section there is a "fix" option to set the order numbers (which used in the "in stock" logic), which would probably need to use some SQL such as the following (which I had to use to "fix" the ordered quantities):

Code Select Expand

UPDATE `#__virtuemart_products` AS p
LEFT JOIN
( SELECT `order_item_sku`,sum(`product_quantity`) AS pqs
FROM `#__virtuemart_order_items`
GROUP BY `order_item_sku`)
AS oi ON p.`product_sku`=oi.`order_item_sku`
SET `product_ordered`=oi.pqs
WHERE oi.`order_item_sku` IS NOT NULL

UPDATE `#__virtuemart_products` AS p
LEFT JOIN
( SELECT `order_item_sku`,sum(`product_quantity`) AS pqs
FROM `#__virtuemart_order_items`
GROUP BY `order_item_sku`)
AS oi ON p.`product_sku`=oi.`order_item_sku`
SET `product_ordered`=0
WHERE oi.`order_item_sku` IS NULL
The first query updates with the new quantities from the order items table and the second one zeroes ordered quantities where there are no longer any orders relating to those products. By the way, I use SKUs to link the tables as that's what we use in our accounting system - I assume that a vm product id could be used instead (but haven't checked).

Mark

[mb000000]

Naivety and ignorance are an interesting combination when it comes to SQL - or at least for me.....

I have realised that my SQL UPDATES shown as part of the original post are too crude and need to be fine tuned.  I believe these following ones are better as they now take into account the actual order status (sorry everyone!):

Code Select Expand

UPDATE `#__virtuemart_products` AS p
LEFT JOIN
( SELECT `order_item_sku`,sum(`product_quantity`) AS pqs
FROM `#__virtuemart_order_items` AS oi1
INNER JOIN `#__virtuemart_orders` as o ON
oi1.`virtuemart_order_id`=o.`virtuemart_order_id`
WHERE o.`order_status` IN ('P')
GROUP BY `order_item_sku`)
AS oi ON p.`product_sku`=oi.`order_item_sku`
SET `product_ordered`=pqs
WHERE oi.`order_item_sku` IS NOT NULL

UPDATE `#__virtuemart_products` AS p
LEFT JOIN
( SELECT `order_item_sku`,sum(`product_quantity`) AS pqs
FROM `#__virtuemart_order_items` AS oi1
INNER JOIN `#__virtuemart_orders` as o ON
oi1.`virtuemart_order_id`=o.`virtuemart_order_id`
WHERE o.`order_status` IN ('P')
GROUP BY `order_item_sku`)
AS oi ON p.`product_sku`=oi.`order_item_sku`
SET `product_ordered`=0
WHERE oi.`order_item_sku` IS NULL


In these UPDATES, I only include the order item counts where the order status is PENDING - if other VM order states should be included, theu can be included in the IN (.... clauses.

Mark