Post by Alexisander » Sun Sep 23, 2012 12:22 am

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!
Last edited by Alexisander on Sun Sep 23, 2012 12:44 am, edited 1 time in total.

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by Alexisander » Sun Sep 23, 2012 12:37 am

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:

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'] . "'");
and add this after it:

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-
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!
Last edited by i2Paq on Sun Sep 23, 2012 2:21 am, edited 1 time in total.
Reason: code-tags added

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by ADD Creative » Mon Sep 24, 2012 5:10 pm

For options, what about, after this line:

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']) . "'");
Add this:

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'");
And remove the folowig from your changes:

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'] . "'");
Note, I have not tested this.

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by Alexisander » Mon Sep 24, 2012 8:33 pm

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...

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by jty » Mon Sep 24, 2012 10:38 pm

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

jty
Active Member

Posts

Joined
Sat Aug 30, 2008 8:19 am

Post by Alexisander » Mon Sep 24, 2012 11:46 pm

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 ...

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by Alexisander » Wed Sep 26, 2012 3:35 am

ADD Creative wrote:For options, what about, after this line:

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']) . "'");
Add this:

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'");
And remove the folowig from your changes:

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'] . "'");
Note, I have not tested this.

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...

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by Alexisander » Wed Sep 26, 2012 3:37 am

by modifications i mean mine and yours... that new added code...

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by ADD Creative » Wed Sep 26, 2012 5:12 pm

You need to move

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'");
after

Code: Select all

				$order_product_id = $this->db->getLastId();

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by Alexisander » Wed Sep 26, 2012 7:31 pm

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.

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by ADD Creative » Wed Sep 26, 2012 7:49 pm

Not had chance to look at it yet. What jty posted is the correct way to do the changes.

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by Alexisander » Wed Sep 26, 2012 8:21 pm

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...

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by Alexisander » Thu Sep 27, 2012 3:24 pm

ADD Creative wrote:Not had chance to look at it yet. What jty posted is the correct way to do the changes.
I have figured out a way to subtract the stock when adding a new product to an existing order.
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 . "'");
add 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'] . "'");

				$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'] . "'");
					}
				}
	  	}

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by Alexisander » Thu Sep 27, 2012 3:28 pm

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!

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by ADD Creative » Thu Sep 27, 2012 4:28 pm

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.

Code: Select all

		$this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'"); 
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

Code: Select all

				$order_product_id = $this->db->getLastId();
add

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'");
And after

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']) . "'");
add

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'");

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by Alexisander » Thu Sep 27, 2012 5:22 pm

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.

Code: Select all

		$this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'"); 
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

Code: Select all

				$order_product_id = $this->db->getLastId();
add

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'");
And after

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']) . "'");
add

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...
"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!

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by Alexisander » Thu Sep 27, 2012 5:31 pm

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...

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by ADD Creative » Thu Sep 27, 2012 7:47 pm

Try something like.

Above

Code: Select all

      $this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'"); 
add

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'");
				}
			}
		}
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.

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by Alexisander » Thu Sep 27, 2012 8:51 pm

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?

Active Member

Posts

Joined
Mon Jul 18, 2011 10:11 pm

Post by ADD Creative » Thu Sep 27, 2012 9:20 pm

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.

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom
Who is online

Users browsing this forum: No registered users and 14 guests