Extension / Hack wanted - Happy To Pay

Quote

Post by steveharman » Sun May 12, 2013 11:38 pm

Hello,

There doesn't seem to be an Extension that does what I need (it's a bit unusual...) so I wondered if there was a developer interested in coding this for me?

Basically in Admin I need to be able to associate products (link them together) so that if one item sells, the stock quantity is automatically decremented on the others. That's it. One store, multiple products in Catalog with some of them 'joined together' effectively sharing the same stock / quantity level - as soon as a sale takes place the level of stock is lowered on all the linked products by the number of items ordered.

Please get in touch if your're interested in quoting to do the work or know of something that I can install which does this.

Thanks,

Steve
steveharman
Active Member
Posts
108
Joined
Mon Mar 12, 2012 6:36 pm
Top

Re: Extension / Hack wanted - Happy To Pay

Quote

Post by midgette » Fri May 17, 2013 12:05 pm

Hi Steve,
I can help you with this.
If you are using the Excel Import/Export extension, just let me know because we will need to make some additional changes.

If you would prefer an installation script to the following, just let me know.
Otherwise: First, backup your system.
Then, using something like MySQL Workbench, run the following four queries on your database (this assumes your database prefix is "oc_"):
ALTER TABLE oc_product ADD COLUMN `inv_group` INT(11) NULL AFTER `product_id` ;

UPDATE oc_product SET inv_group = product_id;

ALTER TABLE oc_product CHANGE COLUMN `inv_group` `inv_group` INT(11) NOT NULL ;

ALTER TABLE oc_order_product ADD COLUMN `inv_group` INT(11) NULL DEFAULT 0 AFTER `product_id` ;

Then you will want to make the following changes:

1. file admin/controller/catalog/product.php:
insert after line 874:

Code: Select all

		if (isset($this->request->post['inv_group'])) {
			$this->data['inv_group'] = $this->request->post['inv_group'];
		} elseif (!empty($product_info)) {
			$this->data['inv_group'] = $product_info['inv_group'];
	    	} else {
			$this->data['inv_group'] = 0;
		}
insert after line 555:

Code: Select all

		$this->data['entry_inv_group'] = $this->language->get('entry_inv_group');
2.file admin/controller/sale/order.php
insert after line 1124:

Code: Select all

				'inv_group'        => $order_product['inv_group'],
3. file admin/language/english/catalog/product.php
insert after line 46:

Code: Select all

$_['entry_inv_group']        = 'Stock Group ID:<br/>';
4. file admin/model/catalog/product.php
change line 121 from:

Code: Select all

		$this->db->query("UPDATE " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', upc = '" . $this->db->escape($data['upc']) . "', ean = '" . $this->db->escape($data['ean']) . "', jan = '" . $this->db->escape($data['jan']) . "', isbn = '" . $this->db->escape($data['isbn']) . "', mpn = '" . $this->db->escape($data['mpn']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . $this->db->escape($data['tax_class_id']) . "', sort_order = '" . (int)$data['sort_order'] . "', date_modified = NOW() WHERE product_id = '" . (int)$product_id . "'");
to:

Code: Select all

		$this->db->query("UPDATE " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', upc = '" . $this->db->escape($data['upc']) . "', ean = '" . $this->db->escape($data['ean']) . "', jan = '" . $this->db->escape($data['jan']) . "', isbn = '" . $this->db->escape($data['isbn']) . "', mpn = '" . $this->db->escape($data['mpn']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', inv_group = '" . (int)$data['inv_group'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . $this->db->escape($data['tax_class_id']) . "', sort_order = '" . (int)$data['sort_order'] . "', date_modified = NOW() WHERE product_id = '" . (int)$product_id . "'");
change line 4 from:

Code: Select all

		$this->db->query("INSERT INTO " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', upc = '" . $this->db->escape($data['upc']) . "', ean = '" . $this->db->escape($data['ean']) . "', jan = '" . $this->db->escape($data['jan']) . "', isbn = '" . $this->db->escape($data['isbn']) . "', mpn = '" . $this->db->escape($data['mpn']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . $this->db->escape($data['tax_class_id']) . "', sort_order = '" . (int)$data['sort_order'] . "', date_added = NOW()");
to:

Code: Select all

                                $query = $this->db->query("SELECT max(product_id) + 1 AS nextpid from " . DB_PREFIX . "product");
      $product_id = $query->row['nextpid'];
                                $this->db->query("INSERT INTO " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', upc = '" . $this->db->escape($data['upc']) . "', ean = '" . $this->db->escape($data['ean']) . "', jan = '" . $this->db->escape($data['jan']) . "', isbn = '" . $this->db->escape($data['isbn']) . "', mpn = '" . $this->db->escape($data['mpn']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', inv_group = '" . (int)($product_id) . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . $this->db->escape($data['tax_class_id']) . "', sort_order = '" . (int)$data['sort_order'] . "', date_added = NOW()");
5. file admin/model/sale/order.php
change line 86 from:

Code: Select all

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

Code: Select all

      			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', inv_group = '" . (int)$order_product['inv_group'] . "', 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'] . "'");
change line 90 from:

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

Code: Select all

				$this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE inv_group = '" . (int)$order_product['inv_group'] . "' AND subtract = '1'");
change line 194 from:

Code: Select all

                                                                $this->db->query("UPDATE `" . DB_PREFIX . "product` SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_id = '" . (int)$product['product_id'] . "' AND subtract = '1'");
to:

Code: Select all

                                                                $this->db->query("UPDATE `" . DB_PREFIX . "product` SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE inv_group = '" . (int)$product['inv_group'] . "' AND subtract = '1'");
change line 212 from:

Code: Select all

      			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_product_id = '" . (int)$order_product['order_product_id'] . "', 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'] . "'");
to:

Code: Select all

      			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_product_id = '" . (int)$order_product['order_product_id'] . "', order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', inv_group = '" . (int)$order_product['inv_group'] . "', 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'] . "'");
change line 216 from:

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

Code: Select all

				$this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE inv_group = '" . (int)$order_product['inv_group'] . "' AND subtract = '1'");
6. file admin/view/template/catalog/product_form.tpl
insert after line 116 :

Code: Select all

            <tr>
              <td><?php echo $entry_inv_group; ?></td>
              <td><input type="text" name="inv_group" value="<?php echo $inv_group; ?>" size="2" /></td>
            </tr>
7. file admin/view/template/sale/order_form.tpl
insert after line 276:

Code: Select all

                  <input type="hidden" name="order_product[<?php echo $product_row; ?>][inv_group]" value="<?php echo $order_product['inv_group']; ?>" />
8. file catalog/controller/checkout/confirm.php
insert after line 243:

Code: Select all

					'inv_group'  => $product['inv_group'],
9. file catalog/model/checkout/order.php
change line 9 from:

Code: Select all

			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$product['product_id'] . "', name = '" . $this->db->escape($product['name']) . "', model = '" . $this->db->escape($product['model']) . "', quantity = '" . (int)$product['quantity'] . "', price = '" . (float)$product['price'] . "', total = '" . (float)$product['total'] . "', tax = '" . (float)$product['tax'] . "', reward = '" . (int)$product['reward'] . "'");
to:

Code: Select all

			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$product['product_id'] . "', inv_group = '" . (int)$product['inv_group'] . "', name = '" . $this->db->escape($product['name']) . "', model = '" . $this->db->escape($product['model']) . "', quantity = '" . (int)$product['quantity'] . "', price = '" . (float)$product['price'] . "', total = '" . (float)$product['total'] . "', tax = '" . (float)$product['tax'] . "', reward = '" . (int)$product['reward'] . "'");
change line 204 from:

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

Code: Select all

				$this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE inv_group = '" . (int)$order_product['inv_group'] . "' AND subtract = '1'");
10. file system/library/cart.php
insert line after 222 :

Code: Select all

						'inv_group'       => $product_query->row['inv_group'],
After you've made these changes, your Admin's Catalog > Products > Data tab will include a "Stock Group ID" section where you can assign groups of products. The default "Stock Group ID" is the product_id that is assigned by the product table. If, for example, if you were to change the Stock Group ID for items (product_id) 3 and 4 to 2, then whenever product_id 2, 3 or 4 sold, the inventory quantities for items 2, 3 and 4 would be decremented. There's no limitations on the number of groups you make. And there's no limitation to the number of items you include in any group.
ad.jpg

ad.jpg (165.74 KiB) Viewed 2571 times

Testing is easy. Just note the inventory quantities for all items of a group and then purchase one or two item from that group. As soon as you confirm the transaction, you will see the stock quantity levels drop for all items in the group by the total amount that you just purchased.

I sure hope you use this because I've put quite a bit of work into it : )

Cheers,
Midgette
pmidgette1@gmail.com
midgette
Newbie
Posts
19
Joined
Sun Apr 28, 2013 4:16 pm
Top

Re: Extension / Hack wanted - Happy To Pay

Quote

Post by steveharman » Tue May 21, 2013 11:30 pm

Hi Midgette,

Sorry for the delay replying, I've been away.

This looks fantastic and appears to be precisely what I need. I'm just in the process of backing things up now, ahead of following your instructions.

Once again thanks for your effort. Please let me know where a donation can be sent!

Regards,

Steve
steveharman
Active Member
Posts
108
Joined
Mon Mar 12, 2012 6:36 pm
Top

Re: Extension / Hack wanted - Happy To Pay

Quote

Post by midgette » Wed May 22, 2013 12:43 pm

Hi Steve,

Two things about testing that I should have mentioned:
1. After seeing that a purchase has correctly adjusted the inventory quantities for all items in the associated group(s), go into Admin > Sales > Orders and edit the order's status from "Pending" to "Completed". Then verify that the inventory quantities for all of the items in the order are still correct. I know that sounds quite strange...the reason is that, when an order's status is changed, opencart will increment and then immediately decrement the inventory levels for all items in the order. There's a good reason they do this but to explain would take me an hour. Anyway, no worries, this will all work perfectly if you make the above code changes carefully.
2. Also, after testing, verify that your Admin > System > "Error Log" is empty.

Please let me know if you have any problems...I don't expect that you will.

I can't accept any donation...but you may choose to 'pay it forward' : )

Take care.
Midgette
midgette
Newbie
Posts
19
Joined
Sun Apr 28, 2013 4:16 pm
Top

Re: Extension / Hack wanted - Happy To Pay

Quote

Post by steveharman » Wed May 22, 2013 7:41 pm

Hi Midgette,

I'm getting...

Code: Select all

Parse error: syntax error, unexpected T_STRING in /var/www/html/[MY_DOMAIN_NAME]/vqmod/vqcache/vq2-admin_model_sale_order.php on line 209
Just reverted to my backup for now, will try your steps again later to see if it was something I did wrong. If an installer script was indeed available that might help remove me from the equation! :-)

Regards,

Steve
steveharman
Active Member
Posts
108
Joined
Mon Mar 12, 2012 6:36 pm
Top

Re: Extension / Hack wanted - Happy To Pay

Quote

Post by midgette » Thu May 23, 2013 3:16 am

Hi Steve,
I'll make you a vQmod for this. If you can tell me all of the vQmod extensions that you are using, I can make sure that this doesn't step on any of them, or vice versa.

Regards,
Midgette
midgette
Newbie
Posts
19
Joined
Sun Apr 28, 2013 4:16 pm
Top

Re: Extension / Hack wanted - Happy To Pay

Quote

Post by steveharman » Thu May 23, 2013 3:27 am

Hi Midgette,

Wow. Thanks. The only Extension I have installed is OpenBayPro (which is the driving reason behind the work you've done) - which required VQMod I believe. That and the Shoppica2 theme, which probably doesn't count.

The backup came back fine BTW but I've left the SQL alterations you suggested in place.
Regards,

Steve
steveharman
Active Member
Posts
108
Joined
Mon Mar 12, 2012 6:36 pm
Top

Re: Extension / Hack wanted - Happy To Pay

Quote

Post by midgette » Fri May 24, 2013 1:20 am

Hi Steve,

I just realized that, you might have pending orders...If you do, then before installing this extension, you should run this query on your database (this assumes your database prefix is "oc_"):

Code: Select all

UPDATE oc_order_product 
SET inv_group = (SELECT inv_group 
FROM oc_product 
WHERE product_id = oc_order_product.product_id)
The extension will perform this update on new orders automatically.

Today I added on the product form, the system assigned Product ID, alongside the Stock Group ID:
pf.jpg

pf.jpg (166.51 KiB) Viewed 2479 times

This makes it easier to remove an item from a group by returning its Stock Group ID to its Product ID.
For example, in the image above, if we changed the Stock Group ID to 288, then it would become 'ungrouped'.

Save the attached xml file in your vQmod/xml folder and you should be good to go.

Regards,
Midgette
midgette
Newbie
Posts
19
Joined
Sun Apr 28, 2013 4:16 pm
Top

Re: Extension / Hack wanted - Happy To Pay

Quote

Post by steveharman » Fri May 24, 2013 8:41 pm

Fantastic, all installed without a hitch Midgette. We are re-stocking next Tuesday so will be trying it out from then on!

You've been SO helpful, once again many thanks.

Steve
steveharman
Active Member
Posts
108
Joined
Mon Mar 12, 2012 6:36 pm
Top
Post Reply
Return to “General Support”
Who is online

Users browsing this forum: No registered users and 19 guests