VirtueMart Forum

VirtueMart 1.1.x [ Old version - no longer supported ] => Products, Prices, Tax and Categories VM 1.1 => Topic started by: willowtree on July 31, 2008, 18:17:02 pm

Title: HOW is Stock Managed?
Post by: willowtree on July 31, 2008, 18:17:02 pm
Is it possible for someone to explain when stock is removed/added?

I had an issue where if we refunded and cancelled an order, I would set the order to refunded, noting the refund details in the ordr notes, and then set it to cancelled. This would however add the items back into stock twice, so a product that was sold out (which is why we had to cancel the order) would suddenly have 2 available again. (This was using vm 1.0) It took me months to work out why stock was appearing out of nowhere.

I've recently upgraded to VM 1.1 and I seem to be having stock issues again.

I really need to understand when stock is added or removed. I couldn't find this info in the manual, but it probably make a good addition.

Is there a process I SHOULD be using when processing orders? What hapens when I partially refund an order using Paypal, as paypalsends back a refund and changes the order back to pending, has stock been added back to stock? What should i change the order to after this?

I also have a custom order status called 'Complete' That I use to mark orders that are collected etc rather than use shipped, but I don't want to leave them as just Confirmed as it's confusing. Is this messing up my stock levels?

Title: Re: HOW is Stock Managed?
Post by: akerman on August 01, 2008, 23:06:09 pm
I agree with 'willowtree'.

I also think that the VM manual would be more valuable with description of processes, and stock is a good example.

I'm too looking to understand the different steps/stages of the stock change process.
When does it change/update, by what parameters and when not?


Since there are several users complaining in the forum about stock calculation, maybe some effort could be done to collect those question and answer them with a stock description.
(In my line of business we usually call this 'decision-trees'. 

It is also my conviction that some schematics of VM functionality and relationships between PHP files for example, would significantly reduce the number of question floating around in the forum, repeating themselves...


Regards
Akerman
Title: Re: HOW is Stock Managed?
Post by: aravot on August 13, 2008, 02:35:05 am
Honestly I have not looked at how stock is managed, because I have not had a return.

To fix this issue or add description to user manual, we need to agree how stock should be managed, in my opinion item should be added back to stock only when order status is set to 'canceled'.

Please add your suggestions.
Title: Re: HOW is Stock Managed?
Post by: willowtree on August 13, 2008, 12:47:06 pm
It's not just managing returns, but knowing what effect actions have, such as setting order statuses. If you have the order status set as refunded or cancelled, and add a customer note, i believe it will keep adding those items back into stock, each time you save a status of refunded or cancelled.
Title: Re: HOW is Stock Managed?
Post by: aravot on August 13, 2008, 18:26:08 pm
Before we start debugging, as to on what actions item is put back in stock, lets agree on what.

I think item should be put back in stock only when order status is changed to cancel, do we agree on this.
Title: Re: HOW is Stock Managed?
Post by: willowtree on August 13, 2008, 19:04:07 pm
sounds OK, however it should only be put back in stock once, not each time the order history is saved with the cancel status.

I'd LOVE to have a popup when it is going to add back into stock, with check boxes for each item so I can select which ones to add and which not, for example if a product was damaged I don't want it added back into stock (and possibly running the notification script).

Perhaps there needs to be a 'flag' set somewhere to make sure the return to stock routine is only processed once for each order?
Title: Re: HOW is Stock Managed?
Post by: akerman on August 13, 2008, 23:14:35 pm
Sounds all like good ideas and it's nice to see some brainstorming around this.  :)

However, there are quite a few instances that has to be considered when and how articles are affecting the warehouse.

An example:
A. When a customer order is set to be paid via invoice, the normal routine is to send the
   merchandise and the invoice to the customer. Either separate or in the same package.
   Today it seems VM is not able to handle this correctly. The article is still in stock,
   since no payment has yet been made.
   The administrator of VM has alternatively to reduce the stock manually or to hide the
   article manually, if it was the last item. Otherwise another customer can place an order
   that can't be managed. Actually multiple orders can be placed, if they too are made with
   invoice as payment method.


Suggested solution:
   When a customer confirms the order and checks out correctly, the stock should be reduced.
   Or at least some kind of status should be introduced that reduce the number of available
   items in the front end. Making it impossible to buy already sold/delivered products.

   Upon returned merchandise, the stock should be able to become increased with the number
   of articles returned. Or the status flag removed/reset.     

Some studies of commercial stock/warehouse/accounting software behaviour maybe could be of interest? I know there are differences between countries in how this is handled, but some kind of best practise ought to be possible to implement.


Regards
Akerman 
Title: Re: HOW is Stock Managed?
Post by: betasoft on October 01, 2008, 03:20:20 am
Just a little note to the developers :

I know about the stock problem and understand what you are talking about, but one problem that I as the owner do NOT control is, when the buyer goes in to my shop and picks out an item to buy, then goes to the creditcard (epay) payment, but then cancels the order, then i have a problem, because it adds +1 to my stock, without having taken one from stock, this is a big problem, i just saw epay testing my site and he (Thomas, Epay) cancelled the order i i ended up with one more item in stock.

Please fix this stock problem ! :-[

Regards Bjarne
JM v1.0.15 and VM v1.0.15
Title: Re: HOW is Stock Managed?
Post by: willowtree on November 14, 2008, 18:49:23 pm
another related post is here:
http://forum.virtuemart.net/index.php?topic=38028.0
Title: Re: HOW is Stock Managed?
Post by: willowtree on November 14, 2008, 18:54:30 pm
I've been trying out a different system, and it has quite a nice system.

Each order has a refund button, which changes the page to offera selection of Full/Partail refund, a value or Percentage, and an option of whether to restock the product or not, and whether to advise the customr of the refund.

I like this much better than the VM method (There isn't really one :) )

[attachment cleanup by admin]
Title: Re: HOW is Stock Managed?
Post by: johk on November 23, 2008, 11:16:30 am
I have the same problem.  For example if I have an order of 3 items which get Refunded those 3 items are added back to the total stock. But if the same order is cancelled (after it has been set to Refunded) an additional 3 items are added back to the stock.
If you have a larger site with more than one administrator that handle the orders this is a big issue as one order could easily be changed by another admin user.
I know it is important to set (or decided) the work flow before coding but this is something that need to be looked at.
I don’t want to sound harsh because I do love the work that has been done on VM.

I use joomla 1.5.7 and VM 1.1.2

Thanks

jonas
Title: Re: HOW is Stock Managed?
Post by: MikeUK on November 23, 2008, 16:24:44 pm
Well, I will offer my 2 cents. I have a different opinion.

I've been working on a system where the client needs to be aware of stock, but not so many products. From a retail point of view, any automatic putting back of stock is a risky thing, and I think just shouldn't be done at all within a system such as Virtuemart (where so many people have so many different uses). I will explain....

1) what if an order is cancelled because the product is damaged?

2) What if it's cancelled because the store admin made a mistake with stock inventory?

I appears to me that re-stocking a product is better done as a manual task, because there could be various situations (like the above) where human input is needed.

I think the idea of completely automatic stock control is fine when dealing with a commericial e-commerce product that has a defined area, but I wouldn't say Virtuemart is like that. But, this is also affected by my overall view that the Virtuemart core system is about as complicated as I believe it should be.
Title: Re: HOW is Stock Managed?
Post by: akerman on November 24, 2008, 10:11:57 am
I agree with Mike in regards that the complications that will arise when/if trying to automate the stock control are hard to overlook, to say the least.

I have been working with major retail companies over the years and even if a lot of the stock handling is automated, it is always surrounded by a plethora of manual functions and checking. Just for the very reasons that Mike mentions. (There are of course more reasons...)

The problem with VM (my problem), is that the stock control that do exist today is inadequate. (See my note above).

It need to be fixed so that a 'manual' put back into stock is possible and that the stock is actually reduced when check-out is finished and the order is placed. The purchase is then to be considered 'reserved' but not delivered. Still no other customer should actually see the item in the shop. (Think about the shelf in the supermarket and it's stock in back. What do you see as a customer and what do you actually have access to?)

Without going into detail I can just state that the stock process does not work as it should or is expected to work. Even at the level that VM currently is / want's to be.

Regards
Akerman

   
Title: Re: HOW is Stock Managed?
Post by: johk on November 24, 2008, 11:23:55 am
I do agree that a manual stock control is safer than an automatic.
But as I noted above that every time a status of an order is changed it updates the stock. That is the concern I have at the moment.
Jonas
Title: Re: HOW is Stock Managed?
Post by: willowtree on November 24, 2008, 11:49:48 am
Quote
Well, I will offer my 2 cents. I have a different opinion.

tbh I'm not sure it's different!

I would like to see a semi-automatic method. By that I mean I don't want to have to go into each products details to put an order back in stock, but I don't really want the system to blindly do it either.

I think perhaps we need to think about a slightly different order process.

Perhaps in the order list there should be a refund or cancel (or something) button, and clicking that triggers another page, or a popup, that allows you to manage the other processes involved in refunding or cancelling an order.

The pop up would allow you to select which products to put back into stock, for example you could just add 2 of 4 and possibly involves the payment process too. This would allow the stock to be manually controlled, but in a more efficient manner than just removing stock changes on cancel/refund altogether. I

I think there are also issues with Paypal. If we have an order that is paid via paypal, and we refund a part of it, the paypal repsonse to VM causes the order status to be reset to pending, which is not correct. For example, a customer order 4 items at £5.00 each, we only have 3 so we refund £5.00, vm now says that the order is pending, when in fact it is paid. I haven't checked but I fear that it is also returning all the items to stock.

At the start of this thread I was just looking for clarification to how it works so I could try to create a workflow instore to fit, but it seems like this is a much larger problem and impacts on a lot of users.
Title: Re: HOW is Stock Managed?
Post by: MikeUK on November 24, 2008, 12:03:00 pm
It need to be fixed so that a 'manual' put back into stock is possible and that the stock is actually reduced when check-out is finished and the order is placed. The purchase is then to be considered 'reserved' but not delivered. Still no other customer should actually see the item in the shop. (Think about the shelf in the supermarket and it's stock in back. What do you see as a customer and what do you actually have access to?)   

Yes, this makes sense to me. Good to get someone with major retail experience commenting, by the way.

From my point of view, a cancelled or declined order is only related to the payment process. It doesn't mean the customer doesn't want the goods, or should immediately lose the option of purchasing (if the stock automatically goes back and some else purchases). The payment processor, not the customer for example, may be at fault.  I'm not 100% sure if this is also part of your point akerman, but to me it makes a lot more sense that in such a situation manual intervention is required. That the order is still in a state of being 'reserved' until admin decides to make enquires or manually delete it and add back the stock.


Perhaps in the order list there should be a refund or cancel (or something) button, and clicking that triggers another page, or a popup, that allows you to manage the other processes involved in refunding or cancelling an order.

The pop up would allow you to select which products to put back into stock, for example you could just add 2 of 4 and possibly involves the payment process too. This would allow the stock to be manually controlled, but in a more efficient manner than just removing stock changes on cancel/refund altogether.

I think this is a nice idea, when focusing on just the issue in this thread. I suppose my view here is affected by my overall concern with VM, which is that, if every situation is solved by attempting the ideal programmed route (which I would say your idea is), we could end up with a system that is incredibly large and complex, and much harder to debug and, inevitably trust. Still, if popular opinion was for such a button, I would vote for it being a one-way button, where it was only possible to click once, which if it returned the items to stock, also archived (or deleted) the order so there was not chance of automatically OR manually, doing it again. Possibly that's what you meant anyway?
Title: Re: HOW is Stock Managed?
Post by: willowtree on November 24, 2008, 12:15:56 pm
possibly, I think at the moment anyone should put forward any ideas, and hopefully we can come up with a solution that suits.

After reading your post, it occured to me that perhaps if there was such a button, and pop up. If items were put back to stock, the date they were put back could be added so if it was clicked again you couldn't put the same items back,

Personally i don't like deleting orders as I like to be able to look at a customers history, and it is useful to be able to go back and see an order, and customers quite  like to be able to see them too, even if just to check that it has been refunded
Title: Re: HOW is Stock Managed?
Post by: akerman on November 27, 2008, 00:19:49 am
I think Mike just touched one of the 'make or brake' issues here:

What is VM and where does it want to go? To be only a cart? More than a cart? A fully fledged eCommerce solution for all types of tangible and non-tangible goods?

The answer to that question is the answer on how complex VM will/wants be/to become...
...and also the probable answer to how a decent/good stock management implementation could look like.

I'm sorry, but I think that trying to present any solutions/ideas in this area is going to just become water under the bridges, until the goal for VM is answered above.


In the meantime my suggestion is to just look at a small commercial system (or possibly the competition), and just simply copy that 'procedure'/approach. A kind of semi-automatic solution if you will (as Willowtree said). There's really no idea to develop something unique for VM. There are already good, simple solutions implemented out there. It's just that 'someone' needs to take the time and evaluate some of them.
(...Not me though... ;) )

And Willowtree is correct, an order system never deletes the order. It just flags status differently or in some instances it has suborders created that are active, while the parent order is already delivered/paid in full. The reason for keeping them in the system are of course mainly for bookkeeping and for purposes of backtracking. 


Regards
Akerman
Title: Re: HOW is Stock Managed?
Post by: MikeUK on November 27, 2008, 09:19:05 am
I think Mike just touched one of the 'make or brake' issues here:

What is VM and where does it want to go? To be only a cart? More than a cart? A fully fledged eCommerce solution for all types of tangible and non-tangible goods?

I've always been good at adding more questions and not giving any answers. :)

Obviously this thread is not about VM as a whole, so I'll be brief. It is already much more than a cart. But I think OsCommerce showed us something. It showed that an ecommerce system can grow and grow, become very popular, and then begin to lose popularity because it is too big / too complicated. Zen cart comes along and gets praise for being a simpler, more straight-forwards system, with easier styling. I wonder what the OsCommerce devs thought about that after spending hours ans hours adding new features?

How about something like this then:

1) orders are placed, stock is adjusted for those products
2) one order is cancelled, admin goes to that order, clicks 'cancel' button'
    (cancelled is removed from drop down list and becomes separate button, for clarity, maybe put in red box to warn admin to nor click by   mistake)
3) cancel button archives order  (perhaps looking at it from the inside, placed into a new table?), returns to stock.
4) cancelled orders can not be 'uncancelled'. A new order must be created if customer returns.

Title: Re: HOW is Stock Managed?
Post by: willowtree on November 27, 2008, 09:25:19 am
imho, to be any kind of functional cart for tangiable goods, it needs to have a better stock management system than is already in place. Too many errors are introduced  into the system in its current form. Errors are also multiplied, in that the system will automatically make changes, and possibly notify users that there is stock, when there isn't. Combined with payment processes not updating the status (such as Worldpay in vm 1.1)there are too many issues controlling stock levels.

I do agree that we shouldn't have to reinvent the wheel, other systems must have processes in place, so lets see how they do it.

For what its worth, in a post above I was looking at Freeway who have a semi-auto system.

Has anyone used/trialled any other systems with solutions they liked? Lets post them here in the spirit of improving VM
Title: Re: HOW is Stock Managed?
Post by: willowtree on November 27, 2008, 09:45:03 am
Mike posted while I was typing, so I'll respond to his post here;

again, this is just imho, but this is a must have for VM, rather than an additional feature. I can see your concerns about VM becoming too big and cumbersome, but that I feel is a slightly different issue, such as how to keep the core minimal, but allow people to change vm to suit them, by adding new features etc. Currently by hacking vm to bits, which is really the only way, but that puts people off upgrading, which causes more problems as people are running old systems....but I digress :)
Quote
How about something like this then:

1) orders are placed, stock is adjusted for those products
2) one order is cancelled, admin goes to that order, clicks 'cancel' button'
    (cancelled is removed from drop down list and becomes separate button, for clarity, maybe put in red box to warn admin to nor click by   mistake)
3) cancel button archives order  (perhaps looking at it from the inside, placed into a new table?), returns to stock.
4) cancelled orders can not be 'uncancelled'. A new order must be created if customer returns.

AFAIK at the moment, the only way to add notes to the order history, automatically goes through the order status change process and runs processes it finds there, which is why adding notes to the history can add products back into stock multiple times. It is this coupling of 2 processes that causes one of the problems.

I think we are talking at slight cross purposes.

A straight cancelled order is fairly straightforward, problems generally occur when things do not go to plan.

A few for examples:
1 - An order is placed, paid, and shipped. Customer then reports that product arrived damaged, and returns it for a refund.
We need to a.) process the refund b.) mark as cancelled c.) NOT put the stock back as it is damaged

2-An order is placed and paid for via paypal. We do not have 1 of the items in stock so we process the refund in PayPal.
In this case, what I beleive happens is the stock is deducted when the payment is received by PayPal. The stock is ALL then returned then the partial refund is processed by PayPal as vm beleives it is a Full refund. Currently we then have to go into each line item and correct the stock levels.(ouch)

3- An order is placed and paid for by Worldpay. VM no longer updates the order status to paid so stock is not removed, then another order is placed for the same item, which is no longer in stock, and is paid for by paypal. a third customer then tries to order that product but it is now out of stock, so they are added to the notify/waiting list.
We have to a.) send the product to the first customer, updating the status as we go, b.) refund the 2nd customer, which adds to the stock, sending out a notification to the third customer, who then orders an item that is not actually in stock, so we have to refund that one....

We have a few thousand items in our store (a lot are archived too, we cannot delete them as it will mess up order histories etc) so searching for an SKU, waiing for the product list, selecting the item, switching tabs entering new stock level, saving etc takes a while for 10 items in an order.

I like the notify feature, but its achillies heel is that its based on a stock level that too easily is incorrect.

Perhaps instead of automaticalle emailing customers, there should be a send notify list option or something that lists the customers, and the products, and what it beleives is in stock, then we could double check it and only send the notifications we want? Althought, again I fear I have drifted off topic.

Stock is such a fundamental issue, it seeps into so many other VM features.

:(
Title: Re: HOW is Stock Managed?
Post by: MikeUK on November 27, 2008, 10:47:21 am
Just a brief thing, before a proper reply.

Stock is only reduced on a 'Confirmed' payment? I didn't know that. I thought a 'pending' return also did that. But I don't have my own shop right now, so I guess I haven't used stock control enough.

So what's the issue with Worldpay? I've seen a few Paypal setups where it is necessary to have both Confirmed and Pending options in the payment module config set to 'Confirmed', due to the successful Paypal response being recognised as Pending. But I think this is only an issue with the payment module config, not anything else.
Title: Re: HOW is Stock Managed?
Post by: willowtree on November 27, 2008, 10:58:02 am
Quote
Stock is only reduced on a 'Confirmed' payment? I didn't know that. I thought a 'pending' return also did that. But I don't have my own shop right now, so I guess I haven't used stock control enough.
In my experiance, only a confirmed status reduced the stock,i could be wrong though, and my shop definaltely seems a bit hincky with stock control at the moment,

Quote

So what's the issue with Worldpay? I've seen a few Paypal setups where it is necessary to have both Confirmed and Pending options in the payment module config set to 'Confirmed', due to the successful Paypal response being recognised as Pending. But I think this is only an issue with the payment module config, not anything else.

I'm at a loss over worldpay. In vm 1.0 I had it all working, as payments were processed, the order status was changed to confirmed.

I upgraded to vm 1.1 and now it doesn't update the status after a successful transaction. I didn't change anything, just installed the upgrade. I beleive I also tried to copy over the old worldpay notify file and that didn't solve it either.

It has been posted on the forums a few times but no solution so far.

Another thing that has just occured to me is if people choose to make offline payments, we don't get it very often, but sometimes people send a cheque. Whilst we haven't received payment, that stock needs be be 'reserved' or somthing until the cheque arrives, then we can update the status.
Title: Re: HOW is Stock Managed?
Post by: cinos on February 13, 2009, 17:11:51 pm
Just putting my two cents out here...

Is there a way that we could just have the stock management set, so that it only modifies the stock when you update an order status to shipped?

Nothing else, just that. I know for myself this would solve many problems. :)
Title: Re: HOW is Stock Managed?
Post by: cinos on February 13, 2009, 17:24:50 pm
Also wanted to note that currently stock is deducted when the order is pending. It is also automatically increased when the order is set to cancelled AND when it's set to refunded (two increases there) AND AGAIN if the order is deleted for some reason.

Surely that's a bug?
Title: Re: HOW is Stock Managed?
Post by: cinos on February 16, 2009, 14:40:41 pm
Bumped. :)
Title: Re: HOW is Stock Managed?
Post by: MikeUK on February 17, 2009, 18:50:48 pm
Just putting my two cents out here...

Is there a way that we could just have the stock management set, so that it only modifies the stock when you update an order status to shipped?

Nothing else, just that. I know for myself this would solve many problems. :)

This wouldn't work as a general mod as what if 3 customers all shopped in one day and bought more stock than was available, as the first of these orders may not have been shipped. This is something much more down the custom path.
Title: Re: HOW is Stock Managed?
Post by: cinos on February 18, 2009, 11:23:29 am
Well going with your example, my general thinking was that when the orders are placed you see that 3 people have ordered the same item.

You check your inventory and you see that you have only 2 in stock. So you order extra stock and package up and ship two of the orders.

As soon as you manually change the status of the order to shipped, the inventory is reduced and you then just wait for the additional stock to come in for the 3rd order.

I don't think I understand what you're trying to say. What do you mean by "the first of these orders may not have been shipped"?  ???
Title: Re: HOW is Stock Managed?
Post by: MikeUK on March 01, 2009, 16:06:26 pm
cinos, I wasn't suggesting it was a bad idea or wouldn't be a good solution for some, but it would not be a universal solution. It would be suitable only for certain types of online shop, and something more custom.

Many types of business may not be able to wait until 'shipped' in order to determine stock. Some my not even use standard shipping. Customers must end up purchasing something that isn't available, and maybe can't be replaced.
Title: Re: HOW is Stock Managed?
Post by: hellodave on May 28, 2009, 11:53:45 am
This is the last thing I need to sort out before launching my site.
Is everyone just adjusting stock manually until a solution is found?
On my site currently, stock is reduced when "confirm order" is clicked.
I only really want the stock reduced when payment is received.
I can cope with manually adjusting stock levels for refunds and cancellations etc if I can fix this.
Has anyone made any progress with working out how stock is managed?
Title: Re: HOW is Stock Managed?
Post by: hellodave on May 29, 2009, 11:36:07 am
Well I managed to get this working after a fashion.
I found 4 files where stock is adjusted -

ps_checkout.php
ps_order.php
ps_order_change.php
ps_order_edit.php

All are in -

\administrator\components\com_virtuemart\classes\

The last 2 are commented as - "...acts as a plugin for the order_print page"
so I have ignored them!

So in ps_checkout.php there is a function called "add" that "is the main function which stores the order information in the database."
Around line 1122 I found this -

Code: [Select]
// Update Stock Level and Product Sales, decrease - no matter if in stock or not!
$q = "UPDATE #__{vm}_product ";
$q .= "SET product_in_stock = product_in_stock - ".(int)$cart[$i]["quantity"];
$q .= " WHERE product_id = '" . $cart[$i]["product_id"]. "'";
$db->query($q);

$q = "UPDATE #__{vm}_product ";
$q .= "SET product_sales= product_sales + ".(int)$cart[$i]["quantity"];
$q .= " WHERE product_id='".$cart[$i]["product_id"]."'";
$db->query($q);

I commented the whole thing out, I don't want any stock adjusted here.

In ps_order.php there is a function called "order_status_update."
Around line 160 I found this -

Code: [Select]
// Do we need to re-update the Stock Level?
if( (strtoupper($d["order_status"]) == "X" || strtoupper($d["order_status"])=="R")
// && CHECK_STOCK == '1'
&& $curr_order_status != $d["order_status"]
) {
// Get the order items and update the stock level
// to the number before the order was placed
$q = "SELECT product_id, product_quantity FROM #__{vm}_order_item WHERE order_id='".$db->getEscaped($d["order_id"])."'";
$db->query( $q );
$dbu = new ps_DB;
// Now update each ordered product
while( $db->next_record() ) {
$q = "UPDATE #__{vm}_product SET product_in_stock=product_in_stock+".$db->f("product_quantity").", product_sales=product_sales-".$db->f("product_quantity")." WHERE product_id='".$db->f("product_id")."'";
$dbu->query( $q );
}
}

So this is restocking the item if the status is changed from anything to cancelled or refunded. Not really what I want so again I commented out the entire thing.
This is where I want the stock adjusted though so immediately after that I added this -

Code: [Select]
if( $curr_order_status!="C" && strtoupper($d["order_status"])=="C") { //going from anything to confirmed
$q = "SELECT product_id, product_quantity FROM #__{vm}_order_item WHERE order_id='".$db->getEscaped($d["order_id"])."'";
$db->query( $q );
$dbu = new ps_DB;
// Now update each ordered product
while( $db->next_record() ) {
$q = "UPDATE #__{vm}_product SET product_in_stock=product_in_stock-".$db->f("product_quantity").", product_sales=product_sales+".$db->f("product_quantity")." WHERE product_id='".$db->f("product_id")."'";
$dbu->query( $q );
}
}

This is reducing stock levels if the status is changed from anything to confirmed.

What has this achieved?
Now stock is only reduced when order status is changed to confirmed.
Stock is only increased when the order is removed from the system, on the order list page, select the order, click remove, stock is added back.
In reality, I don't think we will be removing orders to restock them, I think that will be done by hand.
This might prove problematic if we get a large number of orders, but Ill cross that bridge when I get to it.
There is only 1 of most of the items in my store so this works for me.

This does feel decidedly hacky, like I have probably missed something.
So feedback is welcome  8) Can you see any problems with what I've done?
Title: Re: HOW is Stock Managed?
Post by: Lylene on January 06, 2010, 00:32:58 am
hellodave

Thanks for this trick, this is what I was looking for !!!

Has anyone tested it yet ? :)

Thanks
Lylene
Title: Re: HOW is Stock Managed?
Post by: Lylene on February 23, 2010, 18:35:08 pm
works great by the way :)
Title: Re: HOW is Stock Managed?
Post by: Sid. on April 14, 2011, 01:28:26 am
I know this is an old topic, but specifically related to what I want to know.

@hellodave -

Quote
Stock is only increased when the order is removed from the system, on the order list page, select the order, click remove, stock is added back.

I'm having an issue where my webstore client has unique 1-of-a-kind products. So each product has an inventory of '1' only, and once ordered, should not be available to other customers. When an Order is Pending, Confirmed, or Shipped - it should have inventory of '0' - and the default inventory stock reduction works well for this.  When the Order Status is Cancelled or Refunded, the item is added back into inventory by default. Again, this works for our needs.

However, when you Delete an Order - each product from that order is added back into Inventory.  My client deletes the Orders regularly, to protect customer data, and though I persuade them to Delete the Products associated with it at the same time, this isn't always performed in the right order.  

So, when orders started coming in for long-ago sold items, Virtuemart and myself each got our share of the blame (as I had made a few hacks & mods to their installation already). The advice from @hellodave above led me to the right area, but if you dig further into the code located in ps_order.php to around line 770 (VM 1.1.6) you will see the following code:

Code: [Select]
/**
* Deletes one Record.
*/
function delete_record( $record_id, &$d ) {
global $db;
$record_id = intval( $record_id );


if ($this->validate_delete($record_id)) {
$dbu = new ps_db();
// Get the order items and update the stock level
// to the number before the order was placed
$q = "SELECT order_status, product_id, product_quantity FROM #__{vm}_order_item WHERE order_id=$record_id";
$db->query( $q );
require_once( CLASSPATH .'ps_product.php' );
// Now update each ordered product
while( $db->next_record() ) {
if( in_array( $db->f('order_status'), array('P', 'X', 'R') )) continue;

if( ENABLE_DOWNLOADS == '1' && ps_product::is_downloadable($db->f("product_id")) && VM_DOWNLOADABLE_PRODUCTS_KEEP_STOCKLEVEL == '1') {
$q = "UPDATE #__{vm}_product  
SET product_sales=product_sales-".$db->f("product_quantity")."
WHERE product_id=".$db->f("product_id");
$dbu->query( $q );
}
else {
$q = "UPDATE #__{vm}_product
SET product_in_stock=product_in_stock+".$db->f("product_quantity").",
product_sales=product_sales-".$db->f("product_quantity")."
WHERE product_id=".$db->f("product_id");
$dbu->query( $q );
}
}

$q = "DELETE from #__{vm}_orders where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_order_item where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_order_payment where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_product_download where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_order_history where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_order_user_info where order_id='$record_id'";
$db->query($q);

$q = "DELETE FROM #__{vm}_shipping_label where order_id=$record_id";
$db->query($q);

return True;
}
else {
return False;
}
}
/**

which I commented out around line 778 like so:

Code: [Select]
/**
* Deletes one Record.
*/
function delete_record( $record_id, &$d ) {
global $db;
$record_id = intval( $record_id );


if ($this->validate_delete($record_id)) {
/*** REMOVE UPDATE STOCK INVENTORY WHEN ORDER IS DELETED

$dbu = new ps_db();
// Get the order items and update the stock level
// to the number before the order was placed
$q = "SELECT order_status, product_id, product_quantity FROM #__{vm}_order_item WHERE order_id=$record_id";
$db->query( $q );
require_once( CLASSPATH .'ps_product.php' );
// Now update each ordered product
while( $db->next_record() ) {
if( in_array( $db->f('order_status'), array('P', 'X', 'R') )) continue;

if( ENABLE_DOWNLOADS == '1' && ps_product::is_downloadable($db->f("product_id")) && VM_DOWNLOADABLE_PRODUCTS_KEEP_STOCKLEVEL == '1') {
$q = "UPDATE #__{vm}_product  
SET product_sales=product_sales-".$db->f("product_quantity")."
WHERE product_id=".$db->f("product_id");
$dbu->query( $q );
}
else {
$q = "UPDATE #__{vm}_product
SET product_in_stock=product_in_stock+".$db->f("product_quantity").",
product_sales=product_sales-".$db->f("product_quantity")."
WHERE product_id=".$db->f("product_id");
$dbu->query( $q );
}
} END REMOVE UPDATE STOCK INVENTORY WHEN ORDER IS DELETED ***/

$q = "DELETE from #__{vm}_orders where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_order_item where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_order_payment where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_product_download where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_order_history where order_id='$record_id'";
$db->query($q);

$q = "DELETE from #__{vm}_order_user_info where order_id='$record_id'";
$db->query($q);

$q = "DELETE FROM #__{vm}_shipping_label where order_id=$record_id";
$db->query($q);

return True;
}
else {
return False;
}
}
/**

This keeps the default VM functions for inventory management when an Order Status is changed, but it prevents the inventory from changing when an Order is Deleted.  I hope it helps others who were drawn to this post like I was.

Joomla 1.5.22
VirtueMart 1.1.6