Prestashop to eBid

[irrelevant]

We use Prestashop for our inventory and such direct sales as happen to find us.

In case anybody else does the same, the following bit of code will create a csv file compatible with eBid's bulk upload specification! I'm forever tinkering with it, but this works for now!

You will need to add a custom field to ps_category called 'thirdpartyinfo' as per here. Use type TYPE_HTML and validation isAnything. When you edit your store category, you will now be able to add, e.g.

<ebid>2674</ebid>
<ebidstore>2:1</ebidstore>

Which is the category number, and your store number:category number.

The code to create the csv file is...

SELECT "irrelevant" as "Username", ExtractValue(c.thirdpartyinfo, "/ebid") as "Category ID", 
ExtractValue(c.thirdpartyinfo, "/ebidstore") as"eBid Store Category", 
IF(pa.ean13 IS NOT NULL,pa.ean13,IF(pa.upc IS NOT NULL,pa.upc, IF(p.ean13 IS NOT NULL,p.ean13,p.upc))) as "Barcode", 
IF (pal.name IS NOT NULL, CONCAT(pl.name, " (", pal.name, ")"),pl.name) as "Auction Title", 
CONCAT("http://", su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL,pai.id_image,im.id_image), "/", REPLACE(pl.name," ","_"), ".jpg") as "Image URL",
p.condition as "Item Condition", 
IF(sa.out_of_stock = 0 and (s.physical_quantity > 0 AND s.usable_quantity > 0),LEAST(s.physical_quantity,s.usable_quantity),25) as "Quantity", 
"Immediate" as "Start", "3" as "End", 
"" as "Starting Bid", "" as "Sales Tax", "" as "Reserve", 
"gallery" as "Feature",  "" as "YouTube Video ID", 
IF(sp.price IS NOT NULL, p.price - IF(sp.reduction_type = "amount", sp.reduction, p.price * sp.reduction/100), p.price) as "BuyNow price",
m.name AS "Brand", 
IF(p.weight<=0,"!NSP",IF(p.weight<=100,"!100",IF(p.weight<=250,"!250",IF(p.weight<=500,"!500",IF(p.weight<1000,"!000","!STD"))))) as "Domestic Shipping", 
"" as "International Shipping", 
"3|5|6|10|11" as "Payment Methods", 
"10" as "Auto Repost", 
CONCAT("ps_",sh.id_shop,"/",p.id_product) as "SKU", 
udf_cleanstring(CONCAT(pl.description,IF(sup.product_supplier_reference IS NOT NULL, CONCAT("  <br>Reference - ",product_supplier_reference),IF(p.supplier_reference IS NOT NULL,CONCAT("  <br>Reference - ",p.supplier_reference),"")))) as "Description", 
IF(p.active = 1 AND ( (s.physical_quantity > 0 OR s.usable_quantity > 0) OR sa.out_of_stock != 0 ),"ao","c") as "Action", "##end##"
FROM ps_product p 
LEFT JOIN ps_shop sh ON sh.active = 1
LEFT JOIN ps_category c ON p.id_category_default = c.id_category
LEFT JOIN ps_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1
LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_shop = sh.id_shop AND pl.id_lang = 1 
LEFT JOIN ps_image im ON (p.id_product = im.id_product AND im.cover = 1)
LEFT JOIN ps_product_attribute pa ON (pa.id_product = p.id_product )
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND (sa.id_shop = 0 OR sa.id_shop = sh.id_shop) AND sa.id_product_attribute = IF(pac.id_product_attribute IS NULL,0,pac.id_product_attribute))
LEFT JOIN ps_stock s ON (s.id_product = p.id_product AND s.id_product_attribute = sa.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pal.id_attribute = pac.id_attribute AND pal.id_lang = 1)
LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)
left join ps_product_supplier sup on p.id_product = sup.id_product and p.id_supplier = sup.id_supplier and sup.id_product_attribute = pa.id_product_attribute

LEFT JOIN ps_manufacturer m ON p.id_manufacturer = m.id_manufacturer  

LEFT JOIN ps_specific_price sp  ON (p.id_product = sp.id_product AND (sp.id_shop = sh.id_shop OR sp.id_shop = 0) AND sp.from < CURDATE() AND sp.to > CURDATE() )

 WHERE p.id_shop_default = sh.id_shop  AND ExtractValue(c.thirdpartyinfo,"/ebid") IS NOT NULL

You will need to change username at the top, obviously. Probably the shipping stuff too.

That's too big to add using the prestashop SQL Manager; use mysqladmin or even the cli to insert it into ps_request_sql . Once it's there, though, you can use the SQL Manager to run it and retrieve the csv file for uploading to ebid.

HTH.

Edit - updated code 20130806
Forgot to mention you need udf_cleanstring from here.
Revert: SKU back as product_id
Add: MPN/Supplier Ref appended to description.
Bug fix: multiple shop URLs caused multiple listings.

Edit - updated code 20130809
Add: Initial code to cope with shared-stock in multi-store.

Edit - updated code 20131018
Shipping costs now use shipping codes rather than hard coded prices.
Change so as to cancel auctions for non-active products.

[graphskill]

Nice!

We also use prestashop, but because we have variations (called attributes there) and Ebid does not yet have variations I have to do it differently

We have prestashop plugins for Ebay, Amazon, CQOUT and I jsut hope once the app / API is sorted here, we can convince someone to make a plug in for here )

[irrelevant]

That will handle attributes/variations quite happily - see e.g. http://uk.ebid.net/perl/main.cgi?tit...hamini+capsule - each colour is just a variation of the one product on prestashop.

I've made a start on a simple prestashop module to do it all a bit more neatly, but no promises; too many other coding jobs competing for my time. With no API yet (How many years have we been waiting?) It'll still have to do it by creating a CSV and FTPing it up to ebid. I have no idea how to make eBid launch it after that without screen scraping, which rather defeats the point somewhat ... I seem to recall that even Ninjalister only uploads a file into the same place and you have to go launch it manually.

ETA: Hmm. The ftp setup section implies that it might update automatically. I'll have to do some testing! If that is the case, then I could do something a bit more sophisticated - Now, one of the issues I currently have is that an "amend" via the spreadsheet actually overwrites and resets the listing end time. Since I've been poking about in the prestashop ebay module (trying to find out why it's not working for me!) I've decided that I might be able to do something similar to how that works: actually keeping track of what's been posted, so only updating as and when necessary. If the ftp does auto-update, then it could update eBid dynamically! Of course, there's no way to do it in reverse -to find out which items have been sold on eBid - without the API, but it would be a start.. I just have to find some quality time spare to devote to it, or find somebody to fund it.. (since it's not currently a priority given I'm ok with the raw sql->csv!.)

ETA2: Yep.. a file ftp'd up will get processed automatically. It takes several minutes for it to get done. So, yes, a system to keep things updated automatically would be possible.

[irrelevant]

I forgot to mention .. if you are using the SQL in the OP within PS's SQL Manager, you will have to amend AdminRequestSqlController.php -

change : fputs($csv, '"'.strip_tags($result[$name]).'";');
to: fputs($csv, '"'.$result[$name].'"'.chr(9));

This sets it to tab-separated, plus stops it removing any html from your descriptions.

[BostonRecycling]

Can you build a module for this ?

I'll buy it asap

[irrelevant]

Hmm...
It's something I am thinking about.

At the moment I've got a rough and ready collection of code fragments that works for us, that now keeps ebid updated should any sales go through elsewhere. The next issue for me is getting the orders out of ebid and back into the store. (We actually had a couple...!) That is actually something that would be easier to accomplish from code running within the prestashop framework, so it might be the trigger to get me to properly module-ise it (is that a word?)

What's your budget? :-) (PM or email me!)

[ianuk2005]

Hi guys,

great to see someone coming up with solutions for this!

Wondering if anyone can help me get this set up properly. I've got as far as executing the SQL to generate the csv and hit a couple of roadblocks. I'm not really great with SQL so I'm a bit clueless to what the code is actually doing.

1. SELECT "irrelevant" as "Username"
What would i change this to, my prestashop login? Executing it in phpmyadmin just returns gives me a single column with the username.

2. Forgot to mention you need udf_cleanstring from here.
I'm really not sure on what to do with this and worried about breaking things. Do i copy paste and execute this in phpmyadmin?

3. I'm guessing for all the LEFT JOIN commands I need to change "ps_" to whatever my tables have been renamed to in phpmyadmin e.g. "ps_shop" to "sx8e4s0wg_shop"?

Thanks in advance,
Ian

[irrelevant]

Sorry for the late response!

]1. SELECT "irrelevant" as "Username"
What would i change this to, my prestashop login? Executing it in phpmyadmin just returns gives me a single column with the username.

Just change this to your ebid username.

2. Forgot to mention you need udf_cleanstring from here.
I'm really not sure on what to do with this and worried about breaking things. Do i copy paste and execute this in phpmyadmin?

Basically, yes.

3. I'm guessing for all the LEFT JOIN commands I need to change "ps_" to whatever my tables have been renamed to in phpmyadmin e.g. "ps_shop" to "sx8e4s0wg_shop"?

Correct again.

I've done some updates since the last update to the OP, but I want to make it a bit more flexible and finish integrating it into a module.
I've finally got it successfully automatically updating individual eBid auctions when items are sold in the website, but at present those are
run by cron tasks and scripts tied closely into my configuration(it relies on a table updated by another (paid) module - but I know the procedure
works so can re-write eventually. If anybody wants to commission this then it will float higher up the TDO list!
It should be possible to import orders, too, but I've not done that as, TBH, they are few and far between..

[irrelevant]

I've been playing with the new API. Looks good, and I should be able to do a properly integrated module using it!

(This is the bit I enjoy; coding. Sales and marketing is just a necessary evil..)