Hello all,
I am creating this new topic for helping Opencart fix a very old issue. As i have seen from our forum, many others asked about this issue and i hope that now we all can find the solution.
What's this about? Well let's see.
Opencart is an ONLINE-SHOP, and online-shops means: administration of products that are for sale. And in this "administration thing" that most important is the STOCK. A right stock means: a good bussines, happy customers and no problems when it comes to law and accountancy.
As we know Opencart is the best online-shop-system ever and many of us are trying to get it even better, but there is one thing that no one has fixed it till now:
>>> STOCK UPDATE when working over orders from the admin panel:
(1) Creating a new order from admin panel: When we, the admins, create a new order from the admin panel (sales>customers>customers>insert) the quantitys from the products we add to that new order DOES NOT subtract from the stocks. And it should.
(2) When editing an existing order from admin panel: When we, the admin, edit an existing order from the admin panel (sales>customers>customers>edit order) AND delete a product from the existing order, the stock for that product DOES NOT INCREARSE OR if we add a new product to the existing order, the stock for the new product DOES NOT SUBTRACT OR if we increase the quantity of an existing product in the order(by adding that product again with the difference of quantity we need), the stock for that product DOES NOT SUBTRACT either. And all this it should.
I dont think that this issue can be considered as a bug, but more as a design problem.
So let us find a fix, together and i also ask for our developers to help us in this.
Thank you all!
So as a begin i start with my own modification:
If you create a new order from sales>customers>customers>insert in the admin, for the moment:
> if Product X has stock = 3 and you add in the new order Product X with quantity = 2, if you return to Catalog>Products>Product X, ou will see that it still has Stock = 3 and NOT 1. So the stocks does not subtract.
So as a fix, go to: admin>model>sale>order.php, in the public function addOrder($data) find foreach ($data['order_product'] as $order_product) and arround line: 88 find this:
and add this after it:
This only works for the main Quantity of the product, NOT for the Options Quantitys also... (even if u select an option, the stock for that option do not subtract, only the main stock from Product>Data)
Maybe someone can help to make it for the Options Quantitys also please post.
Thank you!
If you create a new order from sales>customers>customers>insert in the admin, for the moment:
> if Product X has stock = 3 and you add in the new order Product X with quantity = 2, if you return to Catalog>Products>Product X, ou will see that it still has Stock = 3 and NOT 1. So the stocks does not subtract.
So as a fix, go to: admin>model>sale>order.php, in the public function addOrder($data) find foreach ($data['order_product'] as $order_product) and arround line: 88 find this:
Code: Select all
foreach ($data['order_product'] as $order_product) {
$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', name = '" . $this->db->escape($order_product['name']) . "', model = '" . $this->db->escape($order_product['model']) . "', quantity = '" . (int)$order_product['quantity'] . "', price = '" . (float)$order_product['price'] . "', total = '" . (float)$order_product['total'] . "', tax = '" . (float)$order_product['tax'] . "', reward = '" . (int)$order_product['reward'] . "'");
Code: Select all
// Subtract QTY when creating a new order -START-
$this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_id = '" . (int)$order_product['product_id'] . "' AND subtract = '1'");
$order_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product['order_product_id'] . "'");
// Subtract QTY when creating a new order -END-
Maybe someone can help to make it for the Options Quantitys also please post.
Thank you!
Last edited by i2Paq on Sun Sep 23, 2012 2:21 am, edited 1 time in total.
Reason: code-tags added
Reason: code-tags added
For options, what about, after this line:
Add this:
And remove the folowig from your changes:
Note, I have not tested this.
Code: Select all
$this->db->query("INSERT INTO " . DB_PREFIX . "order_option SET order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', product_option_id = '" . (int)$order_option['product_option_id'] . "', product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "', name = '" . $this->db->escape($order_option['name']) . "', `value` = '" . $this->db->escape($order_option['value']) . "', `type` = '" . $this->db->escape($order_option['type']) . "'");
Code: Select all
$this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "' AND subtract = '1'");
Code: Select all
$order_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product['order_product_id'] . "'");WOW! it is working! Thank you very much!
So the ADD NEW ORDER is DONE!!!
Not the only thing left is the EDIT EXISTING ORDER (increase stock when delete an existing product/subtract stock when adding a new product or increasing qty to an existing product)...
I have figured a way to make the stock subtract when increasing qty of a product BUT:
Lets say we already have:
1. product X in existing order with QTY=3 / product X has 20 QTY left.
2. to increase the QTY of the product X to 7 we need to add product X with QTY = 4 AND the stock should subtract to 16...
my code calculates and subtracts the sum... so it substracts 3+4=7 means the stock will be 13...
But no idea how to make a stock INCREASE if we delete a product... from the ( - ) button to the left... not all the order...
So the ADD NEW ORDER is DONE!!!
Not the only thing left is the EDIT EXISTING ORDER (increase stock when delete an existing product/subtract stock when adding a new product or increasing qty to an existing product)...
I have figured a way to make the stock subtract when increasing qty of a product BUT:
Lets say we already have:
1. product X in existing order with QTY=3 / product X has 20 QTY left.
2. to increase the QTY of the product X to 7 we need to add product X with QTY = 4 AND the stock should subtract to 16...
my code calculates and subtracts the sum... so it substracts 3+4=7 means the stock will be 13...
But no idea how to make a stock INCREASE if we delete a product... from the ( - ) button to the left... not all the order...
if you read the editorder function, you will see that opencart deletes "all" the product rows from order_product and option rows from order_option (irrespective of whether they have changed or not). it then re-inserts them all.
so b4 the delete code, you would need to select all the product and option records and update all the stock for "the whole order".
then do the delete of all those product and options
and then update the stock again using your above code
it's a delete/insert as opposed to an update
i really don't like the way opencart deletes and reinserts instead of update but that's life
so b4 the delete code, you would need to select all the product and option records and update all the stock for "the whole order".
then do the delete of all those product and options
and then update the stock again using your above code
it's a delete/insert as opposed to an update
i really don't like the way opencart deletes and reinserts instead of update but that's life
Ye i have seen that thing... thats why becoz it is not made by me from 0 it is hard to figure out a way to make this work as it should... becos when u have an online shop, some customers call u by phone and ask u to make their order, so u need to create an order or modify an existing order... there for the system needs to update stocks all the time ...
ADD Creative wrote:For options, what about, after this line:Add this:Code: Select all
$this->db->query("INSERT INTO " . DB_PREFIX . "order_option SET order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', product_option_id = '" . (int)$order_option['product_option_id'] . "', product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "', name = '" . $this->db->escape($order_option['name']) . "', `value` = '" . $this->db->escape($order_option['value']) . "', `type` = '" . $this->db->escape($order_option['type']) . "'");And remove the folowig from your changes:Code: Select all
$this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "' AND subtract = '1'");Note, I have not tested this.Code: Select all
$order_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product['order_product_id'] . "'");
Creative there is a small problem... with this modifications... lets say u add a product with option, when u save the order and after u return to the order to view or edit it, the product does not have the option...
The option is not saved in the order db...
You need to move
after
Code: Select all
$this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_id = '" . (int)$order_product['product_id'] . "' AND subtract = '1'");
Code: Select all
$order_product_id = $this->db->getLastId();Yessss! working! Thank YOU!!!
Still working at the order edit, did u found any other ideas? With this maybe u can implement this futures in the opencart, it would have a huge impact! I will also post my modifications.
Still working at the order edit, did u found any other ideas? With this maybe u can implement this futures in the opencart, it would have a huge impact! I will also post my modifications.
Not had chance to look at it yet. What jty posted is the correct way to do the changes.
I understand but i am not so good at php yet... thats the problem... i try my best... i am in learning process of php...
I have figured out a way to subtract the stock when adding a new product to an existing order.ADD Creative wrote:Not had chance to look at it yet. What jty posted is the correct way to do the changes.
The problem is this: lets say the order has:
product X - QTY 3 , the stock for product X is 10
and i wanna add product Y - QTY 2 , the stock from product Y before adding it was 20, now when i save the order it will be 18 BUT the stock for product X will also subtract by 3 and will become 7
if again i open the order edit and i donno modify anything, just push SAVE, bouth product stocks subtract again: product X stock will become 4 and product Y stock will become 16. and so on.
2nd scenario:
if the order is this way: product X > QTY 3
product Y > QTY 1
when i delete product Y and hit SAVE, the stock for product Y does not increase and the stock for product X subtract by 3 again.
The code is this:
in public function editOrder section, after
Code: Select all
$this->db->query("UPDATE `" . DB_PREFIX . "order` SET firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '" . $this->db->escape($data['fax']) . "', payment_firstname = '" . $this->db->escape($data['payment_firstname']) . "', payment_lastname = '" . $this->db->escape($data['payment_lastname']) . "', payment_company = '" . $this->db->escape($data['payment_company']) . "', payment_company_id = '" . $this->db->escape($data['payment_company_id']) . "', payment_tax_id = '" . $this->db->escape($data['payment_tax_id']) . "', payment_address_1 = '" . $this->db->escape($data['payment_address_1']) . "', payment_address_2 = '" . $this->db->escape($data['payment_address_2']) . "', payment_city = '" . $this->db->escape($data['payment_city']) . "', payment_postcode = '" . $this->db->escape($data['payment_postcode']) . "', payment_country = '" . $this->db->escape($payment_country) . "', payment_country_id = '" . (int)$data['payment_country_id'] . "', payment_zone = '" . $this->db->escape($payment_zone) . "', payment_zone_id = '" . (int)$data['payment_zone_id'] . "', payment_address_format = '" . $this->db->escape($payment_address_format) . "', payment_method = '" . $this->db->escape($data['payment_method']) . "', payment_code = '" . $this->db->escape($data['payment_code']) . "', shipping_firstname = '" . $this->db->escape($data['shipping_firstname']) . "', shipping_lastname = '" . $this->db->escape($data['shipping_lastname']) . "', shipping_company = '" . $this->db->escape($data['shipping_company']) . "', shipping_address_1 = '" . $this->db->escape($data['shipping_address_1']) . "', shipping_address_2 = '" . $this->db->escape($data['shipping_address_2']) . "', shipping_city = '" . $this->db->escape($data['shipping_city']) . "', shipping_postcode = '" . $this->db->escape($data['shipping_postcode']) . "', shipping_country = '" . $this->db->escape($shipping_country) . "', shipping_country_id = '" . (int)$data['shipping_country_id'] . "', shipping_zone = '" . $this->db->escape($shipping_zone) . "', shipping_zone_id = '" . (int)$data['shipping_zone_id'] . "', shipping_address_format = '" . $this->db->escape($shipping_address_format) . "', shipping_method = '" . $this->db->escape($data['shipping_method']) . "', shipping_code = '" . $this->db->escape($data['shipping_code']) . "', comment = '" . $this->db->escape($data['comment']) . "', order_status_id = '" . (int)$data['order_status_id'] . "', affiliate_id = '" . (int)$data['affiliate_id'] . "', date_modified = NOW() WHERE order_id = '" . (int)$order_id . "'");Code: Select all
foreach ($data['order_product'] as $order_product) {
$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', name = '" . $this->db->escape($order_product['name']) . "', model = '" . $this->db->escape($order_product['model']) . "', quantity = '" . (int)$order_product['quantity'] . "', price = '" . (float)$order_product['price'] . "', total = '" . (float)$order_product['total'] . "', tax = '" . (float)$order_product['tax'] . "', reward = '" . (int)$order_product['reward'] . "'");
$order_product_id = $this->db->getLastId();
// Subtract QTY when creating a new order -START-
$this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_id = '" . (int)$order_product['product_id'] . "' AND subtract = '1'");
$order_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product['order_product_id'] . "'");
// Subtract QTY when creating a new order -END-
if (isset($order_product['order_option'])) {
foreach ($order_product['order_option'] as $order_option) {
$this->db->query("INSERT INTO " . DB_PREFIX . "order_option SET order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', product_option_id = '" . (int)$order_option['product_option_id'] . "', product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "', name = '" . $this->db->escape($order_option['name']) . "', `value` = '" . $this->db->escape($order_option['value']) . "', `type` = '" . $this->db->escape($order_option['type']) . "'");
// Subtract QTY when creating a new order with option -START-
$this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "' AND subtract = '1'");
// Subtract QTY when creating a new order with option -END-
}
}
if (isset($order_product['order_download'])) {
foreach ($order_product['order_download'] as $order_download) {
$this->db->query("INSERT INTO " . DB_PREFIX . "order_download SET order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', name = '" . $this->db->escape($order_download['name']) . "', filename = '" . $this->db->escape($order_download['filename']) . "', mask = '" . $this->db->escape($order_download['mask']) . "', remaining = '" . (int)$order_download['remaining'] . "'");
}
}
}And yes, ofcourse the stock does not increase becoz in my ADD, there is " quantity - ", can anyone tell me how to update the stock so this works? and not subtract every time a SAVE even there are no modifications? and also increase stock work? Thanks!
Before the line below you will need to get the old order details and add to the product (and options) stock quantities. This line deletes the old order details from the database, so you need the old order details before you delete it.
Then you need to subtract from the product (and options) stock quantities using the new order details. The changes for this should be the same as the changes you made to the addOrder function.
So after
add
And after
add
Code: Select all
$this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
So after
Code: Select all
$order_product_id = $this->db->getLastId();Code: Select all
$this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_id = '" . (int)$order_product['product_id'] . "' AND subtract = '1'");Code: Select all
$this->db->query("INSERT INTO " . DB_PREFIX . "order_option SET order_option_id = '" . (int)$order_option['order_option_id'] . "', order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', product_option_id = '" . (int)$order_option['product_option_id'] . "', product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "', name = '" . $this->db->escape($order_option['name']) . "', `value` = '" . $this->db->escape($order_option['value']) . "', `type` = '" . $this->db->escape($order_option['type']) . "'");
Code: Select all
$this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "' AND subtract = '1'");This i dont get how to do: i mean the exact code...ADD Creative wrote:Before the line below you will need to get the old order details and add to the product (and options) stock quantities. This line deletes the old order details from the database, so you need the old order details before you delete it.Then you need to subtract from the product (and options) stock quantities using the new order details. The changes for this should be the same as the changes you made to the addOrder function.Code: Select all
$this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
So afteraddCode: Select all
$order_product_id = $this->db->getLastId();And afterCode: Select all
$this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_id = '" . (int)$order_product['product_id'] . "' AND subtract = '1'");addCode: Select all
$this->db->query("INSERT INTO " . DB_PREFIX . "order_option SET order_option_id = '" . (int)$order_option['order_option_id'] . "', order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', product_option_id = '" . (int)$order_option['product_option_id'] . "', product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "', name = '" . $this->db->escape($order_option['name']) . "', `value` = '" . $this->db->escape($order_option['value']) . "', `type` = '" . $this->db->escape($order_option['type']) . "'");Code: Select all
$this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "' AND subtract = '1'");
"Before the line below you will need to get the old order details and add to the product (and options) stock quantities."
The rest yes, i have done before, and it substracts the stock when i add a product.
But that with the "before the line bel..." and the thing with: how do i INCREASE the stock if a product is deleted from the order?
Thx, a lot!
Every code i try, when i edit a product and do not modify anything and push SAVE, the stocks from the products in order subtract. No metter if i change the QTYs or not.
Even if i only edit 1 product, the rest of products still subtract...
Even if i only edit 1 product, the rest of products still subtract...
Try something like.
Above
add
Then add the changes I posted before to the editOrder function.
Note I have not tested any of this, so there may be more need doing to it.
Above
Code: Select all
$this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'"); Code: Select all
$order_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0' AND order_id = '" . (int)$order_id . "'");
if ($order_query->num_rows) {
$product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
foreach($product_query->rows as $product) {
$this->db->query("UPDATE `" . DB_PREFIX . "product` SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_id = '" . (int)$product['product_id'] . "' AND subtract = '1'");
$option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$product['order_product_id'] . "'");
foreach ($option_query->rows as $option) {
$this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_option_value_id = '" . (int)$option['product_option_value_id'] . "' AND subtract = '1'");
}
}
}
Note I have not tested any of this, so there may be more need doing to it.
I have added this code and now yes, the stocks are perfect!!! Now i see where i was mistaking... hmmm, stupid me. But well... now i have learned something new.
Now everything it is working!!! Do u want me to send u the final order.php? tested and all working? So that u can use it in the future?
Now everything it is working!!! Do u want me to send u the final order.php? tested and all working? So that u can use it in the future?
Good to see you got it working. Please send the file, it might save me some time.
I will probably submit this change to be including in the next version or OpenCart. Although there might be a reason it was not implemented in the first place.
I will probably submit this change to be including in the next version or OpenCart. Although there might be a reason it was not implemented in the first place.
Who is online
Users browsing this forum: No registered users and 7 guests
