Jump to content

Automated Store Update

siadmin

By siadmin
in Core developers

 Share

Recommended Posts

siadmin Newbie

siadmin

Posted
Posted

Hi All,

After many mind numbing hours staring at code, today I have completed my store update scripts. I'm posting here to say that if anyone would like a copy of the code, and an explaination of how it works then I'd be happy to provide it.

The structure is unique to my store and webhost (dropship, single supplier, bluehost) but not difficult to alter to suit your needs. PHP/MySQL knowledge is essential for implementation as it requires database modification and SQL queries run outside of prestashop. I can guarantee you will have to write your own queries, and alter the php code to get it to work. No plug and play here.

This is what my script achieves.. it is 100% automatic.
__________________________

Log in to supplier via FTP
Download suppliers wholesale data, this is a list of items that appear as combinations in my store, aprox 6000 items
Import suppliers data to a table in my database

Update prestashop combination table (prices and quantities).
Update prestashop products to active/inactive based on stock availability on combination table

Runs a query on the suppliers data to group items (combinations) into their product parents
Selects only product parents that do not exist in my store
Exports the new products to a .csv in appropriate format for prestashop import
Imports the product.csv using the prestashop admin import tool

Runs a query on the suppliers data to select combinations that dont exist in my store
Exports the new combinations to .csv for import to prestashop, correctly identifying their parent product
Imports the combination.csv using the prestashop admin import tool

Runs the prestashop searchcron.php file to update the the prestashop search index, so that customers can find new products on the front end.

Sends an email report to a specified address detailing how many items were added to the store, and any errors that were encountered.
__________________________

I've been running 90% of this script on a daily basis for nearly 2 months and i'm yet to have an error.

If anyone is a genius developer and would like to help me turn it into a proper prestashop module let me know.

I am aware that aspects of this have been discussed and sucessfully completed before (I am using some of their ideas), i dont believe there is a completely automated start to finish products and combinations script though.

Link to comment
Share on other sites
siadmin Newbie

siadmin

Posted
  • Author
Posted

Hi Carlo,
I'm glad to hear someone is interested..

As I've only just finished it, I dont have a user friendly version of the scripts with instructions yet.. I also need to go through and remove any sensitive data for my store. I will work on that when i get a chance in the next few days and post the scripts as soon as I can..

To get you started though, if you are downloading a csv from your supplier, you will need to get that into the your database on its own table. I havent incorporated a CREATE TABLE from .csv query in my script so you need to do it manually in phpMyAdmin.. once you have your table you can use phpMyAdmin to do a once off import of your suppliers data, and work on SQL queries for producing the presta-format csvs for import. They will be unique to your own stores configuration and you will have to insert them into my scripts to replace my own.

if you dont understand anything just ask..

regards,
Andrew

Link to comment
Share on other sites
siadmin Newbie

siadmin

Posted
  • Author
Posted

Hi guys,
Apologies for not posting sooner, i've had a busy week. Also have been doing fairly massive modifications to the script that required alot of learning on my part.

Additional feature (once its completed) is that for my site it now correctly categorises my products during the export of the product.csv, and if an appropriate category does not exist it creates that category automatically.

In this post i'm attaching parts of the process.

Firstly, my ftp script for getting my suppliers data.

Secondly, my modified version of the file adminimport.php ..this is where alot of the work was done, and it was not me that did it. So full credit to those responsible.. you can see the full development of this part in the thread i've linked to below, with instructions for getting the file to work via cron (edits of admintab.php). Note that I have multiple versions of this file for each entity that you wish to import. Currently there is just AdminImportProducts.php and AdminImportCombinations.php . I was considering an AdminImportCategories.php but I have left that alone for now.

http://www.prestashop.com/forums/viewthread/39920/configuring___using_prestashop/adminimport_dot_php

Get your import file sorted, then get the adminImport.php mod sorted and make sure its working when you run its tab in the back office.. I will cover getting the cron to run it soon..

during testing for these processes I encountered a lot of errors and was forced to wipe all products/combinations repeatedly... so.. PLEASE BACK UP YOUR DATABASE BEFORE TESTING.

also noted that the prestashop import function doesnt seem to like importing lots of products at once, I limited it to 5 during testing, once it was working I used 200, plus a script that made it repeat until all products were imported, this is done using a WHERE NOT EXISTS clause in my SQL during export of the product.csv

best of luck

ftp_getsupplierdata.php

AdminImportProducts.php

Link to comment
Share on other sites
siadmin Newbie

siadmin

Posted
  • Author
Posted

EXPORTING YOUR CSV FILE FROM A SUPPLIER TABLE IN YOUR DB

this is a stripped down version of my script, mine includes many other queries to my database to return category ids, and create new categories if required..

<?php
error_reporting(E_ALL);
//This script exports supplier data to csv.

//Connect to the database.
       $DB_host = '_________'; // MYSQL database host address.
       $DB_name = '_________'; // MYSQL database name.
       $DB_user = '_________'; // Mysql datbase user.
       $DB_pass = '_________'; // Mysql datbase password.

       $link = mysql_connect($DB_host, $DB_user, $DB_pass);
       mysql_select_db($DB_name);

           $query = "SELECT * FROM supplier LIMIT 0,5";      //this query limits to the first 5 records, my query limits 100 and also contains WHERE NOT EXISTS, so that I only export products that havent already been added to my database.
           $result = mysql_query($query) or die(mysql_error());
           $row = mysql_fetch_array($result) or die(mysql_error());

$fp = fopen('file.csv', 'w');

while($row = mysql_fetch_array($result)){

   $arr = array();

   // You can modify how the supplier data appears in your csv here using php string functions. I have included an example on the 3rd field
   $arr[0] = $row['supplier_field1'];
   $arr[1] = $row['supplier_field2'];
   $arr[2] = substr($row['supplier_field3'],0,5); //this would return the first 5 characters of supplier_field3
   //etc. etc.    
   fputcsv($fp, $arr,';','"');
}
fclose($fp);

?>



Note: You may also be able to achieve this simply by including INTO OUTFILE in your sql query and avoid all the extra php. I do it this way because i need the power of php to correctly produce my csv, SQL alone just can't do it.. atleast not without murdering my server and timing out..

Link to comment
Share on other sites
siadmin Newbie

siadmin

Posted
  • Author
Posted

This is an example of my more complex csv export script..

<?php
error_reporting(E_ALL);
//This script exports product parents to csv.

//Connect to the database.
       $DB_host = '______'; // MYSQL database host address.
       $DB_name = '______'; // MYSQL database name.
       $DB_user = '______'; // Mysql datbase user.
       $DB_pass = '______'; // Mysql datbase password.


       $link = mysql_connect($DB_host, $DB_user, $DB_pass);
       mysql_select_db($DB_name);

           $query = "SELECT * FROM ps_feed WHERE NOT EXISTS(SELECT * FROM ps_product WHERE ps_product.supplier_reference=ps_feed.reference";      
           $result = mysql_query($query) or die(mysql_error());
           $row = mysql_fetch_array($result) or die(mysql_error());

$fp = fopen('file.csv', 'w');

while($row = mysql_fetch_array($result)){
       //FETCH CATGEGORY INFORMATION
           //SET BRAND CATEGORY
               //FIRST CHECK IF BRAND EXISTS ON CATEGORY TABLE, INSERT IF REQUIRED
                   $num_check = mysql_num_rows(mysql_query("SELECT * FROM ps_category_lang WHERE name='".$row['BRAND']."'"));
               if($num_check>0)
                   {
                       //code if exists
                   }
                   else
                   {
                       //INSERT QUERIES

                               //GET BRAND PARENT ID
                                   if(ctype_digit(strtoupper(substr($row['BRAND'],0,1)))==TRUE)
                                       {
                                           $p_cat = "24";
                                       }
                                       else
                                       {                                
                                           $brand_letter = "'" . strtoupper(substr($row['BRAND'],0,1)) . "'";
                                           $cat_query_p = "SELECT * FROM ps_category_lang WHERE name=$brand_letter AND id_category<2000"; 
                                           $cat_result_p = mysql_query($cat_query_p) or die(mysql_error());
                                           $cat_row_p = mysql_fetch_array($cat_result_p) or die(mysql_error());
                                           $p_cat = $cat_row_p['id_category'];
                                       }
                               //END GET PARENT ID

                       $insert_ps_category = "
                           INSERT INTO `ps_category` 
                               (`id_category`, `id_parent`, `level_depth`, `active`, `date_add`, `date_upd`) 
                                   VALUES 
                               ('".$row['reference']."', '".$cat_row_p['id_category']."', '4', '1', NOW(), NOW())
                           ";

                       $insert_ps_category_lang = "
                           INSERT INTO `ps_category_lang` 
                               (`id_category`, `id_lang`, `name`, `description`, `link_rewrite`, `meta_title`, `meta_keywords`, `meta_description`) 
                                   VALUES 
                               ('".$row['reference']."', '1', '".$row['BRAND']."', NULL, 'brand-".$row['BRAND']."', NULL, NULL, NULL)
                           ";

                       $insert_ps_category_group = "
                           INSERT INTO `ps_category_group` 
                               (`id_category`, `id_group`) 
                                   VALUES 
                               ('".$row['reference']."', '1')
                           ";

                           mysql_query($insert_ps_category) or die(mysql_error());
                           mysql_query($insert_ps_category_lang) or die(mysql_error());
                           mysql_query($insert_ps_category_group) or die(mysql_error());
                       //END INSERT QUERIES

                   }


               //SET CATEGORY
                   $cat_query_b = "SELECT * FROM ps_category_lang WHERE name='".$row['BRAND']."'"; 
                   $cat_result_b = mysql_query($cat_query_ or die(mysql_error());
                   $cat_row_b = mysql_fetch_array($cat_result_ or die(mysql_error());
                   $b_cat = $cat_row_b['id_category'];
               //END SET CATEGORY
           //END BRAND

           //SET ALPHA
               if(ctype_digit(strtoupper(substr($row['NAME'],0,1)))==TRUE)
                   {
                       $a_cat = "25";
                   }
                   else
                   {
                       $cat_query_a = "SELECT * FROM ps_category_lang WHERE name='".strtoupper(substr($row['NAME'],0,1))."' AND id_category>2000"; 
                       $cat_result_a = mysql_query($cat_query_a) or die(mysql_error());
                       $cat_row_a = mysql_fetch_array($cat_result_a) or die(mysql_error());
                       $a_cat = $cat_row_a['id_category'];
                   }
           //END ALPHA

       //END FETCH CATEGORY


   $arr = array();
   $arr[0] = "1";
   $arr[1] = ucwords(substr($row['NAME'],0,strpos($row['NAME'],' by')));
   $arr[2] = "255,".$b_cat.",".$a_cat;
   //etc. etc.

   fputcsv($fp, $arr,';','"');
}
fclose($fp);

?>

Link to comment
Share on other sites
  • 1 month later...
Hatex Newbie

Hatex

Posted
Posted
Hi guys,
Apologies for not posting sooner, i've had a busy week. Also have been doing fairly massive modifications to the script that required alot of learning on my part.

Additional feature (once its completed) is that for my site it now correctly categorises my products during the export of the product.csv, and if an appropriate category does not exist it creates that category automatically.

In this post i'm attaching parts of the process.

Firstly, my ftp script for getting my suppliers data.

Secondly, my modified version of the file adminimport.php ..this is where alot of the work was done, and it was not me that did it. So full credit to those responsible.. you can see the full development of this part in the thread i've linked to below, with instructions for getting the file to work via cron (edits of admintab.php). Note that I have multiple versions of this file for each entity that you wish to import. Currently there is just AdminImportProducts.php and AdminImportCombinations.php . I was considering an AdminImportCategories.php but I have left that alone for now.

http://www.prestashop.com/forums/viewthread/39920/configuring___using_prestashop/adminimport_dot_php

Get your import file sorted, then get the adminImport.php mod sorted and make sure its working when you run its tab in the back office.. I will cover getting the cron to run it soon..

during testing for these processes I encountered a lot of errors and was forced to wipe all products/combinations repeatedly... so.. PLEASE BACK UP YOUR DATABASE BEFORE TESTING.

also noted that the prestashop import function doesnt seem to like importing lots of products at once, I limited it to 5 during testing, once it was working I used 200, plus a script that made it repeat until all products were imported, this is done using a WHERE NOT EXISTS clause in my SQL during export of the product.csv

best of luck


How I can integrated AdminImportProducts.php to presta ? Or I must run this code in another instance ? THX
Link to comment
Share on other sites
siadmin Newbie

siadmin

Posted
  • Author
Posted


How I can integrated AdminImportProducts.php to presta ? Or I must run this code in another instance ? THX


you have to create a new tab that runs from that script in the back office. you can find the explaination of how to do this in the link i mentioned..

http://www.prestashop.com/forums/viewthread/39920/configuring___using_prestashop/adminimport_dot_php

as far as integrating the csv export and all the other aspects of this script, I havent done that, nor do i know how to.. or intend to try.. it works just running in putty or as a cron and thats all i need.
Link to comment
Share on other sites
  • 4 weeks later...
epicureanfoods.com Newbie

epicureanfoods.com

Posted
Posted

Hello Siadmin

This is incredible what you have accomplished. Congratulations!

This is exactly what we are trying to do.

I am not a programmer so we have been trying to enlist web developers to do this for us.

We have been delayed due to unforeseen circumstances, so with the little bit of knowledge I have I've decided to try to do the Prestashop Admin Import myself (the rest, as described in your post I have no intention of attempting).

We have a number of products we need to import to our website and we have put all of the elements of these products into a spreadsheet.

My understanding of the import tool is that it's a little flaky and I am nervous to proceed. Particularly, I understand that we shouldn't use excel to create the csv since it leaves characters in that could corrupt the product database.

A while ago, I came across a set of "do's and don'ts" that someone had written with respect to this tool. Unfortunately, I did not save this Forum Post and have not been able to find it again.

What I'm requesting is any advice or templates you might have that you would be willing to supply to me.

Basically, our business is on hold until we are able to do this and we have just had a string of bad luck so far getting this done.

thank you very much
Phyllis

Link to comment
Share on other sites
rmalla Newbie

rmalla

Posted
Posted

Hello all,

I have a question related to this issue. I'm looking for suppliers that work with FTP files which I can integrate to my store; I know and am aware that Ingram Micro, Tech Data and all this suppliers have this system but I would like to find more suppliers (other than IT) which can offer this to be able to contact them an put their products on my store.

Is there any place where I can have like a list of this suppliers? if I google that what should I be searching to find this.

Thanks,.

Link to comment
Share on other sites
  • 1 year later...
miguelfcord Newbie

miguelfcord

Posted
Posted

Thank you very much for sharing your scripts.

 

It´s nice solution but you still have to deal with the problem of the stock. If you sell an item by ecommerce your supplier don´t record the sale and the stock is not update in the supplier. Unless you point sale`s email confirmations to the supplier and it update the stock manually, some realtime bi-directional solution must be supplied to deal with stock, which depend on ERP software posibilities.

 

Thank you very much again for sharing the scripts.

Link to comment
Share on other sites
  • 3 months later...
TWP Newbie

TWP

Posted
Posted

Hello to all ..

 

I downloaded this two files:

 

1) ftp_getsupplierdata.php

2) AdminImportProducts.php

 

But I do not know which folder to place them.

 

I have tried in this directory:

 

www.miosito.it / controllers / admin / AdminImportProducts.php

 

www.miosito.it / ftp_getsupplierdata.php

 

 

but nothing ...

 

I created the tab with the classes AdminImportProducts.php and does not work

 

There was an error in the web site during the recovery of May not be available for maintenance or configured incorrectly.

 

and the second

 

FTP dowload from supplier to supplier connected - Problem downloading ftp data. SCRIPT TERMINATED.

 

 

I ask for help because I need this automatic import

Link to comment
Share on other sites
  • 1 year later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...