ok, Guys new 1.5 issue need help!

[TheCamoHut]

OK, B4 I updated to ps 1.5 I built an update script to update my inventory using a csv file for example I would export/build my csv file daily which only takes about two clicks thanks to my POS system and would send it to the PS database. via the URL I created (ex: google.com/inventory) Now with this new update the inventory is no longer in ths PS_products table in the sql database it for some reason is in its own table PS_stock_available and this is fine expect I update my inventory using the EAN filed the scripts searched for ean and connects the stock,my issue is

 

1. in the new PS_stock_available table is doesn't have the EAN column

 

2. the PS_products no longer has the inventory

 

Now I should be able to make this work in one of two ways if i can do...

 

1. find a way to add the ean field into the PS_stock_available

or

2. find a way to move the stock back to the PS_products table

 

I have attmpted to alter the tables a bit with no luck, any help would be apperciated. If I am able to get this solved I would release my script for the world to use I know how many people need something like this and how much expence other have had to pay for cluncky PS managment systems for this single reason.

 

Thanks for all your help

Matthew

I have owned a prestashop store for close to 10 years and the 1.5 update has been the most headach of them all, but I will NEVER leave PS.

[TheCamoHut]

still no replies? am I seriously the only one with this issue?

[PascalVG]

Hi Matthew,

Let me check if I understand you well: You have at the moment some script or function that puts the daily inventory in the ps_product file, using the field EAN, right? So I assume you add the inventory (quantity) to this EAN field and then it somehow copies it to the quantity field. Still correct?

 

If so, what you can do is either:

1) modifying the function/script where the ean field is copied into to ps_product. Instead make it copy it into the right field in ps_stock_available. We need to see your current function/script that handles the import for that, so we know what we are talking about exactly. If you want to take this road, please attach a zipped file with the script/function-file concerned.

 

2) Another option is  in MySQL to use a 'trigger' on the EAN field, (and remove the old part where it copies the value to the ps_product quantity field). The trigger checks automatically if there's any change to the EAN fields and if so, can run a script. This script then can take care of the copying to the right field in ps_stock_available. If you need help to remove the current copying to the ps_product field, then again add the zip file here. If yo need help with the trigger (simple sql script should do the trick) let us know. Please describe the process in a little more detail then (What you expect exactly with the EAN field to happen)

 

My 2 cents,

pascal

[TheCamoHut]

Not exactly. First of all currently I am using PS 1.4.4.1 I need this to work in ps 1.5.4.1 and higher.

 

I have attached a copy of my spread sheet I currently upload. Note column A is the UPC and B is the current inventory. to make it simple I just have a few rows normally I have over 1,000 + items to update.

 

Now the script I wrote currently works as follows.

1. I log into the script www.skiandstuff.com/import
2. I click browse and find the updated inventory file on my desktop (see attached)
3. I click send and off it goes.

What it is doing is connecting with the PS 1.4.4.1 sql database in the ps_products table using the EAN field (which can be modified easily to look for the reference, or upc  field currently ean works best for me) than once it locates the ean it than updates the quantity field with the days current stock.

 

My problem is that the new PS 1.5+ has changed how the sql database is built. The EAN, UPC, and/or reference files are not in the same table as the quantity as they are in ps 1.4. The quantity is now in a new table Ps_stock_available

And sadly this new table ps_stock_available does not have the EAN, UPC or Reference column in it. So I need to know how to relocate the EAN field which is currently in one of 2 tables ps_product or ps_product_attribute (depending on whether or not the item has attributes) and put it in the ps_stock_available sql table.

 

Thanks for the help

Matt

Edited September 5, 2013 by TheCamoHut (see edit history)

[Dh42]

Ok try this out. Run this query once on your database.

ALTER TABLE ps_stock_available ADD ean13 varchar(13)

Then in your script, before you call your current sql statement you are using, run this statement.

UPDATE ps_stock_available AS a INNER JOIN ps_product AS b ON a.id_product = b.id_product SET a.ean13 = b.ean13

Back your database up before you do this, I am not that great at sql. Now in the current statement in your file instead of using ps_products change that to ps_stock_available 

[TheCamoHut]

IT WORKED!

 

I hope you know how much this has helped me. As thanks for this I will be releasing the script I built to update the inventory for FREE!

 

again Thanks SO MUCH!

[Whispar1]

This is exactly what I need. I have issues even spelling SQL and have been trying to find a way to update inventory quantities other than manually. Would love to see how this is done. Having accurate inventory levels is a MUST for e-commerce and it seems to me there should be a much easier way to achieve this within Prestashop... note for 1.5.6

[Whispar1]

Hello Matt,

 

Any progress with the script for updating inventory? This is still a major headache to manually update inventory levels. Thanks for any heads up on this.

[TheCamoHut]

I am still working on this and keep running into issues at every turn. So far the inventory scrips only works if a site is set up one way or the other meaning you have simple items with no combinations OR every item has a combination. I will have something figured out SOON!!! 

 

Thanks for waiting, I am in the same boat here

 

Matthew

www.TheCamoHut.com

[Whispar1]

Did you have any success on this? I am still fighting this issue for inventory updates. Ideally, I need a query that joins any tables required to create a matching data set to my CSV file which is all based on reference ID and then update the inventory amount. (ps_product and ps_stock_available or ps_product_attribute ???) 

[Dh42]

You know that you can use csv import to update products, correct?

[Whispar1]

Hey DH. Yeah but the problem I am having (and maybe I am so deep in this that I am overlooking the obvious) is that most of my items have combinations. I use an excel spreadsheet with vlookup functions to  updates quantities from a daily inventory spreadsheet from my supplier. For me, the reference number is unique for each Item and its attribute. I understand the ps_stock_available is where the quantities are updated but that database does not have a reference field whereas ps_product_attribute (which also has available_date which is also important to update for me.)

Edited May 20, 2014 by Whispar1 (see edit history)

[Dh42]

By any chance have you looked into store manager?

[Whispar1]

Yes, I have tried it but to be honest, it requires additional addons to acomplish this and it was just overkill for what I need that a fairly simple sql query should be able to accomplish... assuming I knew sql well enough to do it

[Whispar1]

Perhaps something similar to this thread?

http://www.prestashop.com/forums/topic/265318-query-sql-update/

[Dh42]

Are you trying to update each item one by one, or have it run as an update and update them all? I cannot see any easy way to get a whole file based around vlookups imported correctly. 

[Whispar1]

Yes, I need to be able to update the entire ps_stock_available and ps_product_attribute (for the availability date) tables.

I would of course build a separate file and convert to csv to update these two tables. That's what I am thinking anyway.