Post by Cue4cheap » Sun Feb 03, 2019 10:51 pm

Hello!
It has been a while since I have tried to tackle this and yes I am still using 1.5.6.4.
So let's get directly to it.

I am now doing a lot more drop shipping versus keeping a huge amount of stock... therefore it isn't my stock levels but my wholesalers stock levels that I need to keep up with.
My wholesaler provides a feed that has a csv file with:

redbrick39 18,6
redbrick39 19,10
redbrick39 20,8
redbrick39 21,10

Now I can take the redbrick39 and get the product_id without a problem.
But now to take the option, which in this case is the 18 or 19, etc. then the last column is the quantity and update is where I am 99% lost.
The problem I see is associating the product option to the product.... shoot looking in the DB option_value it has 10 thousand entries. And the product_option_value has 8 thousand.

I guess they have that many entries in the database because each option can have it's own image and therefore each option is in essence a separate product but damn this gets confusing on how I can update the option quantity using that feed...

Anyone have any guidance? or suggestions?
Or even banter to tell me I have something set up wrong, and how to fix it.

Thanks!

Mike

cue4cheap not cheap quality


Expert Member

Posts

Joined
Fri Sep 20, 2013 4:45 am

Post by soundzgood-nz » Tue Feb 05, 2019 1:09 pm

Hi Mike

All the action happens in one table - product_option_value as you identified. In this table you have the oc product_id, the option_value_id and the quantity field you want to update. So if you have the product_id sorted, then your next step is to 'map' the dropshipper's option to the specific option_value_id and from there it's simple to update the quantity. The key question then is - "can the dropshipper's option number be mapped to a specific option_value_id in your store?"

Example (using redbrick39 18,6) can you simply create

Code: Select all

update oc_product_option_value set quantity = 6 where option_value_id = 18;
?

If ok, then it becomes a job of parsing the .csv through a 'mapping script' that'll hook up the fields you need to update. It may work best having an additional table in the database that the query can look up to see what dropshipper option = oc option_value_id, cos then you can add/update this and hv as reference. That'd make it more of an 'update script' I guess.

Simon

User avatar
Active Member

Posts

Joined
Sat Oct 25, 2014 5:15 pm
Location - New Zealand

Post by Cue4cheap » Wed Feb 06, 2019 12:45 pm

Thank you very much Simon for the reply.
Getting the correlation is where I get lost.

I know I can search for the product_id and option_id and I'll get the option_value_ids... (BTW Option_id 18 equals weight in how I have the options configured and unfortunately 18 is also a possibility of the weight so this could confuse people reading this)

i.e. SELECT * FROM `product_option_value` WHERE `product_id` = 1458 AND `option_id` = 18
result (BTW I tried to put | between the cells to show a little separation since the post doesn't show right):
product_option_value_id  product_option_id product_id option_id option_value_id quantity
6495 | 1944 | 1458 | 18 | 18396 | 10
6496 | 1944 | 1458 | 18 | 18397 | 10
6497 | 1944 | 1458 | 18 | 18398 | 10
6498 | 1944 | 1458 | 18 | 18399 | 10

And if I do:
SELECT name FROM `option_value_description` WHERE `option_value_id` = 18396
I get:
name
18 lbs

OR
SELECT name FROM `option_value_description` WHERE `option_value_id` = 18398
I get:
name
20 lbs

And I know I can filter out and get just the number and do a simple compare and end up with
Product_id of 1458 has option_value_id of 18398 and option_value_id has 20 in the name
therefore that should be the option that I should update the quantity to 8...

BUT that logic is clunky. So I am sure there is some 'advanced' sql query (like leftjoin, blah blah blah) that could be done to query the product_option_value and the option_value_description to make this a one query but those types of queries I never have grasped well and usually they take me days and days to figure out.

And of course I'd need to loop through each one but that is as far as I got in my thinking this evening. It has been a long work day, long day working on the house and time to hit the bed.
Any thoughts are welcome....

Thank you again,
Mike

cue4cheap not cheap quality


Expert Member

Posts

Joined
Fri Sep 20, 2013 4:45 am

User avatar
Expert Member

Posts

Joined
Tue Jul 17, 2012 10:35 pm
Location - România

Post by soundzgood-nz » Wed Feb 06, 2019 6:46 pm

xxvirusxx wrote:
Wed Feb 06, 2019 4:53 pm
You have tried this extension?

https://www.opencart.com/index.php?rout ... sion_id=17
Yes if you're planning on manually updating your product's options then this is a good (and free) tool (I think it imports/exports xls files)
Simon

User avatar
Active Member

Posts

Joined
Sat Oct 25, 2014 5:15 pm
Location - New Zealand

Post by soundzgood-nz » Wed Feb 06, 2019 6:57 pm

Cue4cheap wrote:
Wed Feb 06, 2019 12:45 pm
Thank you very much Simon for the reply.
Getting the correlation is where I get lost.

I know I can search for the product_id and option_id and I'll get the option_value_ids... (BTW Option_id 18 equals weight in how I have the options configured and unfortunately 18 is also a possibility of the weight so this could confuse people reading this)
Weight is an interesting one as each option can have its own weight set in relation to the parent product's weight - eg product a is 10kg, product a in red is 11kg so '+' and '1' is entered in the weight_prefix and weight fields.

The key bit of this is how data translates between dropshipper and oc tables - eg it's probably unlikely the dropshipper ids match your opencart ids - so you need a reference table to 'map' these.

Simon

User avatar
Active Member

Posts

Joined
Sat Oct 25, 2014 5:15 pm
Location - New Zealand

Post by Cue4cheap » Thu Feb 07, 2019 7:19 am

xxvirusxx wrote:
Wed Feb 06, 2019 4:53 pm
You have tried this extension?

https://www.opencart.com/index.php?rout ... sion_id=17
Yes and we have been using it since we started to us opencart but it doesn't work as needed for updating product quantity and options from the feed I posted. I need something that I can adapt for auto updates... i.e. not manually converting the feed daily to work with JNeuhoff's importexport tool.
I have just enough knowledge to be dangerous but I can do a cron to FTP the file down and I am working on figuring out the cron call for the script to do it's work but the logic to make it do what I need is where I am noodling through it with this topic.
Thanks, Mike

cue4cheap not cheap quality


Expert Member

Posts

Joined
Fri Sep 20, 2013 4:45 am

Post by Cue4cheap » Thu Feb 07, 2019 7:46 am

soundzgood-nz wrote:
Wed Feb 06, 2019 6:57 pm
Cue4cheap wrote:
Wed Feb 06, 2019 12:45 pm
Thank you very much Simon for the reply.
Getting the correlation is where I get lost.

I know I can search for the product_id and option_id and I'll get the option_value_ids... (BTW Option_id 18 equals weight in how I have the options configured and unfortunately 18 is also a possibility of the weight so this could confuse people reading this)
Weight is an interesting one as each option can have its own weight set in relation to the parent product's weight - eg product a is 10kg, product a in red is 11kg so '+' and '1' is entered in the weight_prefix and weight fields.

The key bit of this is how data translates between dropshipper and oc tables - eg it's probably unlikely the dropshipper ids match your opencart ids - so you need a reference table to 'map' these.

Simon
Ah well in this case the weight is in a range of mostly 10 to 21 and adding weight isn't a concern.

Could you give me an idea on what you mean about the reference table?
95% of the products will be 18, 19, 20, or 21 so that is what is in my database (the name field in the option_value_description table) and the drop shippers feed.
So my thinking is still sitting with the logic I posted about in my post with the time hack of Tue Feb 05, 2019 10:45 pm.

I think this weekend I may have to build a little script, load in the fields from my drop shippers file, run the queries I posted and compare what is in the name field of option_value_description table to the option in the drop shipper's file (in the example the #s after the space)
redbrick39 18,6
redbrick39 19,10
redbrick39 20,8
redbrick39 21,10

And then update using the option_value_id....

As always, thanks for the input and thoughts...
Mike

cue4cheap not cheap quality


Expert Member

Posts

Joined
Fri Sep 20, 2013 4:45 am

Post by Cue4cheap » Tue Feb 12, 2019 6:18 am

Well the logic works and I am able to do the updates using my wholesaler's feed. I just needed to update a few options as their feed has some options truncated like "blk" where I have them spelled out as "Black" but that is ok, I can adapt for those simple differences.

Now I need to work on having the file do it's updates without user intervention.
I have a lead in to how to adapt the script BUT it looks as though the average suggestion is to disable the login and permissions for the admin to allow the script to run via cron... obviously this isn't my preferred method but I'm early in the search for a workable solution on how to run the script automatically.

Thanks,
Mike

cue4cheap not cheap quality


Expert Member

Posts

Joined
Fri Sep 20, 2013 4:45 am
Who is online

Users browsing this forum: No registered users and 62 guests