Jump to content

[[SOLVED]]Automatic update product quantities

mozack
 Share

Recommended Posts

mozack Newbie

mozack

Posted
Posted

Hi again,

I'm newbie with prestashop, i've created new products with variation (sizes), each size of products have different EAN and different quantities. How can i update that quantities from one external php file protected by password?

I want something like cronjob to do the task and keep my available quantities updated.

The external php file (from other store) have on first collum the EAN of related size, on second collum (dont need to connect) have the name of product and 3rd collum have the available quantites...

Is that possible to do? Maybe one script like other shopping carts uses to update currencies... I dont know...

Can someone help me?

Thanls thanks thanks...

Link to comment
Share on other sites
VIXUS Newbie

VIXUS

Posted
Posted

Welcome to the club.

Theres a lot of people needing some kind of module that can do update of products prices, quantities and availability but so far nothing can do that.

If you figure out how to do that please share with us.

Link to comment
Share on other sites
mozack Newbie

mozack

Posted
  • Author
Posted
Welcome to the club.

Theres a lot of people needing some kind of module that can do update of products prices, quantities and availability but so far nothing can do that.

If you figure out how to do that please share with us.


Hi, the problem of quantities update is solved...

I can share it but i'm new here...

I dont know how to share it....

Is a basic script but maybe can help someone...
Link to comment
Share on other sites
mozack Newbie

mozack

Posted
  • Author
Posted

remember that you can use all variables rou want in the some formar and order of table you are updating. Ex.:

reference - supplier_reference - location - ean13 - wholesale_price - price (...)

your csv must have this fields or not, but with the some order.

You can only update one table. In this case is the table of product_attribute.

You have to change as well the line 44 to the symbol that separate each collum of csv, set it in php file like: , or ; (..)

In my csv i just have 2 fileds ean13 and quantity (in this order)

Hope can help you, best regards.

Link to comment
Share on other sites
VIXUS Newbie

VIXUS

Posted
Posted

Managed to add script to cron job and its perfect.

Im posting this peace of code (that was killin me for hours untill iw get it right) to save others some else a time and headache.

/usr/bin/php /home/username/folder/script.php

cheers!

Link to comment
Share on other sites
  • 3 weeks later...
  • 1 month later...
VIXUS Newbie

VIXUS

Posted
Posted

file update.php

<?php 
// --------------------------------------------------
// quantity_update.php
// Change MySql database quantity and prices and other data from csv file
// Standalone script - run it by web browser
// Last Updated: May 27 2009
// Author: Mozack
//
// Instructions
//
// Backup your database.
// Define the veriables below.
// Upload this file to your server.
// update.php
//
// To run open in your browser.
// Example: http://www.mysite.com/secure_folder/update.php
//
// That's it!!!  Check that everything is changed properly and make neccessary corrections.
// 
// WHEN DONE REMOVE THIS FILE FROM YOUR SERVER OR KEEP WITH THIS FILE ONE INDEX.PHP REDIRECTING !!!!!
// ---------------------------------------------------
//Configuration variables
//---------------------------------------------------

// Connect to MySQL change with your data
mysql_connect("LOCALHOST", "here goes your login", "here goes your password") or die(mysql_error()); 
mysql_select_db("heregoes your database name") or die(mysql_error()); 

// If first row of csv file is headings set $row to 1. 
$row = 0; 
// TABLE OF PRODUCTS AND VARIATIONS - IN PRESTASHOP IS PREFIX_product_attribute 
$update_table = "ps_product"; 

// Get the csv file - INSERT THE ADDRESS OF CSV FILE OR UPLOAD IT TO THE SOME FOLDER OF THIS SCRIPT
$handle = fopen("update.csv", "r"); 

// Go through the csv file and print each row with fields to the screen. 
// And import them into the database updating only the price and quantity 
// YOU MUST SET THE VARIABLES $reference, $price AND $quantity IF YOU ARE NOT UPDATING PRESTASHOP TABLES
// IF YOU WANT TO UPDATE OTHER DATA IN THE SOME TABLE LIKE SUPPLIER_REFERENCE, LOCATION, ECOTAX, ETC YOU MAST HAVE
// ALL THE DATA IN THE SOME CSV FILE AND THE COLLUMS IN THE SOME ORDER OF THIS CODE. YOU MUST ADD MORE IF STATEMENTS TO ADD
// ADITIONAL DATA TO IMPORT
while (($data = fgetcsv($handle, 100000, ";")) !== FALSE) { 
   $num = count($data); 
   echo "\n";
   echo "( $row )\n"; 
   $row++; 
   for ($c=0; $c < $num; $c++) { 
       if ($c = 1) { 
       $reference = $data[($c - 1)]; 
       echo $reference . " - Reference Assigned\n"; 
       } 
       if ($c = 2) { 
       $price = $data[($c - 1)]; 
       mysql_query("UPDATE $update_table SET price='$price' WHERE reference='$reference'")  
       or die(mysql_error());   
       echo $price . " - Price updated\n"; 
       } 
       if ($c = 3) { 
       $quantity = $data[($c - 1)]; 
       mysql_query("UPDATE $update_table SET quantity='$quantity' WHERE reference='$reference'")  
       or die(mysql_error());  
       echo $quantity . " - Quantity updated\n"; 
       } 
       if ($c = 4) { 
       $active = $data[($c - 1)]; 
       mysql_query("UPDATE $update_table SET active='$active' WHERE reference='$reference'")  
       or die(mysql_error());  
       echo $active . " - Activity updated\n"; 
   echo "_____________________________________________________\n";
       } 

       // ADD ADITIONAL IF STATEMENTS FOR EACH FIELD YOU WANT UPDATE IN THE SOME ORDER OF YOUR CSV FILE 
   } 
} 

fclose($handle); 
echo " \n"; 
echo "  -  -  -  SUCESSFULY COMPLETED  -  -  -  ";
?>




NOTICE, code does not report any mistakes (eg. not found reference) so if anyone knows how to improve this code please share here...
file update.csv

(columns in excel) reference, price, quantity, activity

PCDE-OP360MTE2200                    8.853,000000       100    1
PCDE-OP360MTE2500                    8.853,000000       100    1
PCDE-OP360MTE5300                      132,000000       600    1
PCDE-OP360MTE7400                    4.755,000000       600    1
PCDE-OP360MTE2240                    8.853,000000       605    1

Link to comment
Share on other sites
  • 3 weeks later...
babysoros Newbie

babysoros

Posted
Posted

hello i am new in here can u explain how to use this file? i am already use csv dan they said
1 fields to update in line 0:
1;"1";"1";"1";"1";"1" EAN13 Assigned
Undefined offset: 1 in C:\wamp\www\prestashop\Protected_folder\quantity_update.php on line 54
Table 'bla bla bla ' doesn't exist

how can i know what is my table?

Link to comment
Share on other sites
  • 2 weeks later...
VIXUS Newbie

VIXUS

Posted
Posted

OMG, i see that you are updating with mine "update.csv" file...

mine file is just a sample so that you know how to set your own csv.

reference;price;quantity;activity0/1


where put file update.php?


well iw put it in folder i called update and i acess it through web browser and i protected it with password.

you can there set secure acess to or even put it somewhere in root, your choice...
Link to comment
Share on other sites
VIXUS Newbie

VIXUS

Posted
Posted

well i dont know... it works for me, it works for other people...
I asume that you are doing something wrong... and i cant help you there...

try to go through PHP and see if all of needed data related to your database is properly inserted.

Link to comment
Share on other sites
wallacept Newbie

wallacept

Posted
Posted

what problem? see:

update.csv

OP360MTE2200;8;100;1
OP360MTE2500;833;100;1
OP360MTE5300;132;600;1
OP360MTE7400;43;600;1
OP360MTE2240;844;605;1



update.php

<?php 
// --------------------------------------------------
// quantity_update.php
// Change MySql database quantity and prices and other data from csv file
// Standalone script - run it by web browser
// Last Updated: May 27 2009
// Author: Mozack
//
// Instructions
//
// Backup your database.
// Define the veriables below.
// Upload this file to your server.
// update.php
//
// To run open in your browser.
// Example: http://www.mysite.com/secure_folder/update.php
//
// That's it!!!  Check that everything is changed properly and make neccessary corrections.
// 
// WHEN DONE REMOVE THIS FILE FROM YOUR SERVER OR KEEP WITH THIS FILE ONE INDEX.PHP REDIRECTING !!!!!
// ---------------------------------------------------
//Configuration variables
//---------------------------------------------------

// Connect to MySQL change with your data
mysql_connect("LOCALHOST", "root", "") or die(mysql_error()); 
mysql_select_db("presta100") or die(mysql_error()); 

// If first row of csv file is headings set $row to 1. 
$row = 0; 
// TABLE OF PRODUCTS AND VARIATIONS - IN PRESTASHOP IS PREFIX_product_attribute 
$update_table = "ps_product"; 

// Get the csv file - INSERT THE ADDRESS OF CSV FILE OR UPLOAD IT TO THE SOME FOLDER OF THIS SCRIPT
$handle = fopen("update.csv", "r+"); 

// Go through the csv file and print each row with fields to the screen. 
// And import them into the database updating only the price and quantity 
// YOU MUST SET THE VARIABLES $reference, $price AND $quantity IF YOU ARE NOT UPDATING PRESTASHOP TABLES
// IF YOU WANT TO UPDATE OTHER DATA IN THE SOME TABLE LIKE SUPPLIER_REFERENCE, LOCATION, ECOTAX, ETC YOU MAST HAVE
// ALL THE DATA IN THE SOME CSV FILE AND THE COLLUMS IN THE SOME ORDER OF THIS CODE. YOU MUST ADD MORE IF STATEMENTS TO ADD
// ADITIONAL DATA TO IMPORT
while (($data = fgetcsv($handle, 100000, ";")) !== FALSE) { 
   $num = count($data); 
   echo "\n";
   echo "( $row )\n"; 
   $row++; 
   for ($c=0; $c < $num; $c++) { 
       if ($c = 1) { 
       $reference = $data[($c - 1)]; 
       echo $reference . " - Reference Assigned\n"; 
       } 
       if ($c = 2) { 
       $price = $data[($c - 1)]; 
       mysql_query("UPDATE $update_table SET price='$price' WHERE reference='$reference'")  
       or die(mysql_error());   
       echo $price . " - Price updated\n"; 
       } 
       if ($c = 3) { 
       $quantity = $data[($c - 1)]; 
       mysql_query("UPDATE $update_table SET quantity='$quantity' WHERE reference='$reference'")  
       or die(mysql_error());  
       echo $quantity . " - Quantity updated\n"; 
       } 
       if ($c = 4) { 
       $active = $data[($c - 1)]; 
       mysql_query("UPDATE $update_table SET active='$active' WHERE reference='$reference'")  
       or die(mysql_error());  
       echo $active . " - Activity updated\n"; 
   echo "_____________________________________________________\n";
       } 

       // ADD ADITIONAL IF STATEMENTS FOR EACH FIELD YOU WANT UPDATE IN THE SOME ORDER OF YOUR CSV FILE 
   } 
} 

fclose($handle); 
echo " \n"; 
echo "  -  -  -  SUCESSFULY COMPLETED  -  -  -  ";
?>

Link to comment
Share on other sites
  • 2 weeks later...
  • 4 weeks later...
Bogdans Newbie

Bogdans

Posted
Posted

Hi all
I put the update.php file to C: \ wamp \ www \ PrestaShop \ secure_folder
I typed in the browser address C: \ wamp \ www \ PrestaShop \ secure_folder \ update.php
and receive message from someone rezol poza.ma can help this problem?
Thanks much for any information
> inversaţi

12122_ho9WP10F8iDHIPmmlPEb_t

Link to comment
Share on other sites
  • 4 weeks later...
  • 1 month later...
mozack Newbie

mozack

Posted
  • Author
Posted

I dont understand what you want or what happen with the file, can you send me pscreen or one link to see the problem, if any?

If you want to import products description, i never try it before, but my be it possible...

Let me know clearly what you want to do...

Best Regards.

Link to comment
Share on other sites
mozack Newbie

mozack

Posted
  • Author
Posted

Yes, is possible,

But first you need to check if all data is in the some table (ps_???), after that you need all collums with data or bypass some data: EX.: If you have the meta-description and you dont have any data, dont use this collum.

Do this to all your data in csv file, dont use if you haven't data to insert. In php you choose the collums you want to use like collum1, 2 ,4 etc

With html, this can be used once mysql table have this data as well.

Check carefully the both, sql table and csv and check what you have to use or not.

Hope it help you

Best regards

Link to comment
Share on other sites
  • 2 months later...
2Nine Newbie

2Nine

Posted
Posted

have anyone around been messin' with the inserting of items not found in database?

my problem is this: every week my supplier sends me a CSV with actual state of the warehouse, i need update of existing items (prices, quantities), but if the product is not there, i need the script to insert it

i've seen some similar requests earlier in this thread, but with no answers, so i'm asking again only to know if anything changed and someone may has the solution...

Link to comment
Share on other sites
  • 2 months later...
  • 3 months later...
guest* Newbie

guest*

Posted
Posted
It works perfect for me too. But I cannot update products with attributes directly using the reference number. Anyway to do that?

Thank you!

You have to ammend in line 34 this: $update_table = 'ps_product_attribute'; and it only updates with ean. If you want to update by reference you have to ammend the block to assign it, or change the $ean13 into $reference by all variables.
Link to comment
Share on other sites
guest* Newbie

guest*

Posted
Posted

It works also for me in Presta 1.3.1.1 but I have one fatal problem. Quantities are updated only once. If I try to update again so the one piece is not updating into 2 pcs, i.e. if I have stock 0 before "importing" and add with the import 1 pc, so there is 1 pcs. written, if I "import" again then this 1 pc do not change into2 pcs.

Also I have the problem that my file which ist generated by a barcode-scanner contains the EAN more than 1 time, beacause I receive f.ex. into stock 3 pcs. The csv is written then in this way

EAN
1234567891011;1
1234567891011;1
1234567891011;1
2222222222222;1
2222222222222;1

and so on. When I "import" this file with no end statement of absolute quantities, so it ammend only 1 pc. and the same ean are ignored.

Is there any way to ammend the code to manage this ?

Link to comment
Share on other sites
fitgura Rookie

fitgura

Posted
Posted

Somebody justt tell me how it works, because nothings change in my DB, but the script run correctly..

I use PS 1.3.1.1

The main problem is that all of my products was imported from csv, and are these in ps_product table and not ps_product attribute

ok I set the correct table name in the file

but after the script ran down I checked the table in phpmyadmin and nothing was changed!!!

Why is it???

is that works with ps 1.3.1.1????????

Link to comment
Share on other sites
guest* Newbie

guest*

Posted
Posted

Sorry, yes you are right, it changed for me only one time the stock and also you can use only one of the tables, not both. Sorry I must pass, because I'm not a coder... Perhaps somebody else fit in php can take a look on that ?

I changed also the login querries from " into ', and than it works for only one time... to update more than one time is not possible...

Link to comment
Share on other sites
  • 2 weeks later...
shake Newbie

shake

Posted
Posted

this works perfectly for items in a single table, but how about when i need to update fields requiring 2 tables


i/e - my csv file has ean, price, quantity, name
i update table ps_product (which has ean, price, quantity columns)
but i also need to update the name column which is in a different table (ps_product_lang)






is there any way to do this?



Thanks

Link to comment
Share on other sites
chineseboy Newbie

chineseboy

Posted
Posted

I also need to update products in multiple tables, I need to update the meta title etc. Any ideas?

EDIT: I've changed the code so that I can update the date that I want but none of the new data is updating in the database. I'm trying to change the meta title, keywords and description. Any one got any more info?

Link to comment
Share on other sites
shake Newbie

shake

Posted
Posted

i believe i've sorted it :)

change:

// TABLE OF PRODUCTS AND VARIATIONS - IN PRESTASHOP IS PREFIX_product_attribute 
$update_table = "ps_product";



to:

// TABLE OF PRODUCTS AND VARIATIONS - IN PRESTASHOP IS PREFIX_product_attribute 
$update_table = "ps_product inner join ps_product_lang on (ps_product.id_product = ps_product_lang.id_product)";



now you can update both, linked by the product id

i/e when $ean = ean, (or whichever) in your csv file matches, the associated can also be updated from the product_lang table, so you can update price, quantity, ean from ps_product, but then also name, description, meta_title from ps_product_lang :)

Link to comment
Share on other sites
chineseboy Newbie

chineseboy

Posted
Posted

Thanks for that shake. Tried it but the problem is that the data in my database isn't actually updating. The file is telling me that it is updating, but it isn't. The fields for meta information are all empty. It was doing the same thing before I'd implemented your changes. Any other things you can think of that could fix it?

Link to comment
Share on other sites
mozack Newbie

mozack

Posted
  • Author
Posted

Hi to all,

When i made this, i has in mind update just quantities from product or variation ean. It was made just because i have more than 1 store. 1 main store that have the quantities and the others goes to the first and check the quantities variations and update it to have the same quantities in all my stores.

Now, you are trying to use that to other functions, by this way, i cannot still help you to make this changes. You can change the work method to your needs...

Thanks

Mozack

Link to comment
Share on other sites
guest* Newbie

guest*

Posted
Posted

Hi Mozack,

I also tried only to update quantities by ean. Code does not work, i.e. you can update only once, a sencond time is not possible and also is not possible to update attributes. Sorry I tried..

Sem chance, o script nao funciona.

Abracos de uma brasileira vivente na Áustria.

Conny

Link to comment
Share on other sites
  • 2 weeks later...
guest* Newbie

guest*

Posted
Posted

OK, it works, unfortunately it is not what I need. I need a script which INCREASES the stock by the importing value. This Script only updates the stock by value given in csv.

Do somebody knows how I can adapt the code for checking what is written in quantity and ADD the one given in csv ?

Link to comment
Share on other sites
Jaktis Newbie

Jaktis

Posted
Posted

Thank you for this script, I don't know why I didn't find it before, but it has been a long time avaited for =)

Here's my addition to the use of this script, a shell script to login and download a csv for the updater to use. Simply run this script via cron before the quantity_update.php .


Explanation:
row 2 login and download suppliers csv from ftp
row 3 As my product file was tab separated text file, this row changes the separation from tabs to semicolons and saves the file as new.csv.
row 4 Row 3 also added some blank rows between all products, after this gone they are and saved in a temporary file...
row 5Overwrites the values from the temporary csv to the new.csv
row 6Removes the temporary csv

#!/bin/bash
wget -N -r -nH --cut-dirs=2 -t 180 -P /home/xyz/update/ ftp://username:[email protected]/dir_to_csv/csv.txt
sed -e 's_\(\t\)\{1,\}_;_g' /home/xyz/update/beholdning.txt > /home/xyz/update/ny.csv
sed '/^$/d' /home/xyz/update/new.csv > /home/xyz/update/temp.csv
cut -d ';' -f 1-2 /home/xyz/update/temp.csv  > /home/xyz/update/new.csv
rm /home/xyz/update/temp.csv

  • Like 1
Link to comment
Share on other sites
mozack Newbie

mozack

Posted
  • Author
Posted

Hello,

Well every time i come here, someone has changed the script to their needs. You're welcome.

I'll try to add a new feature on it. Check the data of csv file, verify and update the current products as it do now. But the new feature is:

- If sku is not in database, it send an e-mail with the list of missing sku's to admin. The point is have all products of csv file in our website.

I'll try that asap

Regards

Mozack

Link to comment
Share on other sites
MrBaseball34 Newbie

MrBaseball34

Posted
Posted

Guys, I wrote a class that downloads an XML feed and changes the qtys.
It downloads the XML file if it is >1 day old and stores it locally.
Then it looks up the product based on information in the XML file.
It kind of does this real-time, if a product is viewed and the qty is
not == the value in the XML, it changes it.

It works from category.php and product.php.

If you'd like it to modify to do with csv, let me know.
It could also be modified to ftp the file, kind of like how I do it with one of our vendor update files.

Link to comment
Share on other sites
MrBaseball34 Newbie

MrBaseball34

Posted
Posted

What happened to the last post in this thread? I rcvd a notification that said this message was posted:


Great news mate, can you perhaps expand it to update price and availability?


Will the poster please repost? Are you talking about my class?
If so, then yes it could be modified to do that as well.
Link to comment
Share on other sites
  • 1 year later...
chetwyn Newbie

chetwyn

Posted
Posted (edited)

Sorry guys. But just wanted to be clear. After the script has run it does not send out email alerts to all the customer that requested to be notified once that product is back in stock. Do we need to call some hook or.. ? please help.

 

Here's is my working code.

 

<?php
// --------------------------------------------------
//
// To run open in your browser.
// Example: http://www.mysite.com/secure_folder/update.php
//
// ---------------------------------------------------
//Configuration variables
//---------------------------------------------------
// Connect to MySQL change with your data - Not required, we'll use global class
//mysql_connect("LOCALHOST", "here goes your login", "here goes your password") or die(mysql_error());
//mysql_select_db("heregoes your database name") or die(mysql_error());
// If first row of csv file is headings set $row to 1.
$row = 0;
// TABLE OF PRODUCTS AND VARIATIONS - IN PRESTASHOP IS PREFIX_product_attribute
$update_table = "ps_product_attribute";
// Get the csv file - INSERT THE ADDRESS OF CSV FILE OR UPLOAD IT TO THE SOME FOLDER OF THIS SCRIPT
// $handle = fopen("update.csv", "r");
$fileName = "update.csv";
// Load gloabl libs
$site_base_path = "../";
include($site_base_path . 'config/config.inc.php');
require_once($site_base_path . 'classes/Link.php');
require_once($site_base_path . 'classes/Product.php');
require_once($site_base_path . 'init.php');
// Go through the csv file and print each row with fields to the screen.
// And import them into the database updating only the price and quantity
if (file_exists($filename)) {
   echo "The file $filename exists";

$handle = fopen($fileName, "r");
while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
 $num = count($data);
 echo "\n";
 echo "( $row )\n";
 $row++;
 for ($c=0; $c < $num; $c++) {
  if ($c = 1) {
   $reference = $data[($c - 1)];
   echo $reference . " - Reference Assigned\n";
  }
  if ($c = 2) {
   $quantity = $data[($c - 1)];
   echo "</br> "."UPDATE ".$update_table." SET quantity=".$quantity." WHERE reference='".$reference."'"."</br> ";
   Db::getInstance()->Execute("UPDATE ".$update_table." SET quantity=".$quantity." WHERE reference='".$reference."'");
   echo $quantity . " - Quantity updated\n";
  }
/*
  if ($c = 3) {
   $price = $data[($c - 1)];
  Db::getInstance()->Execute(("UPDATE $update_table SET price='$price' WHERE reference='$reference'") 
  or die(mysql_error());  
  echo $price . " - Price updated\n";		
  }   
  if ($c = 4) {
  $active = $data[($c - 1)];
  mysql_query("UPDATE $update_table SET active='$active' WHERE reference='$reference'") 
  or die(mysql_error()); 
  echo $active . " - Activity updated\n";
 echo "_____________________________________________________\n";
  }
*/ 


 }

}
}
// Update quantity in ps_product table to equal total quantity for combinations.
$sqlRecs = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS("select id_product, sum(quantity) as qty from ps_product_attribute group by id_product");
foreach($sqlRecs as $rec){ 
Db::getInstance()->Execute("update ps_product set quantity = ".$rec['qty'].' where id_product = '.$rec['id_product']); 
echo "\n</br> Updated ID: ".$rec['id_product']."\n";
}
fclose($handle);
echo " \n";
echo "  -  -  -  SUCESSFULY COMPLETED  -  -  -  ";
?>

Edited by chetwyn (see edit history)
  • Like 1
Link to comment
Share on other sites
  • 4 weeks later...
  • 2 years later...
  • 10 months later...
 Share
Go to topic listing
×
×
  • Create New...