Need your advice on data feeds

[kunal247]

Hi,

We have a simple webstore been running for a few yrs now using oscommerce v2.2

#We now have a situation where our suppliers provide us a data feed as csv or xml and we want to be able to download there data files and import it into our website on a daily basis so that stock and prices are updated daily.

 

Also looking to revamp the site and give it a new look but dont want to lose out on the exsisting data base that we have.

 

Can any one shed some light on how to setup the data feeds? Any templates we can use to set up a new store and move our data over too?

 

Your help is appreciated!!

 

Regards,

Kunal

[♥mdtaylorlrim]

Hi,

We have a simple webstore been running for a few yrs now using oscommerce v2.2

#We now have a situation where our suppliers provide us a data feed as csv or xml and we want to be able to download there data files and import it into our website on a daily basis so that stock and prices are updated daily.

 

Also looking to revamp the site and give it a new look but dont want to lose out on the exsisting data base that we have.

 

Can any one shed some light on how to setup the data feeds? Any templates we can use to set up a new store and move our data over too?

 

Your help is appreciated!!

 

Regards,

Kunal

If I understand you correctly, you will go to a URL which downloads a file which you then will process and use the data to alter product pricing and quantities on hand, right? There is no feed to that, in my mind. But maybe I can give you some sample code.

 

This uses curl to get an xml file (although it is a dirty file that has to be sanitized first) and then parses the xml and updates price and quantity on hand.. It also gets the markup per category and alters the price obtained from the supplier to my own retail prices. You may be able to edit this to suit your needs.

 

Keep in mind that this has little to no error trapping, and is customized for some extra columns that I need, namely, vendors_products_id because I needed a common field between the vendors item number and my products, and markup which is a column in the categories table which indicates a % markup of the price from the vendor to my retail price for that category of products.

 

 

<?php
/* put this file in your admin folder. Name it whatever you like, but the same as below.
  Create a cron job that fires as often as you want that contains the following two lines

         cd /absolute/path/to/catalog/admin/
         php name_of_this_file.php

  This is completely automatic. Pricing and quantities will happen as often as the cron job runs.
*/



/* This is the file. A cron job every 30 minutes creates this file
  This script should probably not be run manually except for diagnosing
*/
$source = '/path/to/inventory.xml';

/* Following uses curl as a system call (not presently in use)
$result =  ` curl -o 'path/to/inventory.xml' 'http://url_to_xml_file.here'`;
*/

// following uses curl as php code
$ch = curl_init("url_to_xml_file.here");
$fp = fopen($source, "w");

curl_setopt($ch, CURLOPT_FILE, $fp);
curl_setopt($ch, CURLOPT_HEADER, 0);

curl_exec($ch);
curl_close($ch);
fclose($fp);
// end of php-curl code



// grab content os file into array
$lines = file_get_contents($source);

// sanitize garbage delivered by vendor into valid XML file
   $lines = str_replace('<','<',$lines);
   $lines = str_replace('>','>',$lines);
   $lines = str_replace('anything line you need to delete here','',$lines);
   $lines = str_replace('</string>','',$lines);

$fb = fopen($source,"w");
     fwrite($fb,$lines);

fclose($fb);
// complete repairing xml file

// let's get the database open now
// have to include the configure file
 require('includes/configure.php');

// include the database functions
 require('includes/functions/database.php');

// make a connection to the database... now
 tep_db_connect() or die('Unable to connect to database server!');


// load as string
$xmlstr = file_get_contents($source);
$inventory = new SimpleXMLElement($xmlstr);

// load as file
$inventory = new SimpleXMLElement($source,null,true);

// I, Q, & C are the xml containers in the file holding the item number, quantity on hand, and cost, respectively
foreach($inventory->Table as $inv_line) {
   $itemno = $inv_line->I;
   $qty = $inv_line->Q;
   $cost = $inv_line->C;

// get MAP from db
$sql = "select products_map from products where vendors_products_id = '" . $itemno . "'";
$result = mysql_query($sql);
$map = mysql_fetch_row($result);

// see if MAP applies, if not calculate markup from table
if (is_null($map[0])) {
// get and set markup
$sql = "select categories.markup from categories,products_to_categories,products where products.vendors_products_id = '" . $itemno . "' and products.products_id = products_to_categories.products_id and products_to_categories.categories_id = categories.categories_id";
$result = mysql_query($sql);
$markup = mysql_fetch_row($result);
// diagnostics   if (is_null($markup[0])) echo $itemno . " - " . $markup[0] . "<BR>\n";
$price = (int)($cost * (1+$markup[0]))+1.99;
$cost = $price;
  } else {
  $cost = $map[0];
  }

$sql = "update products set products_quantity = '" . $qty . "', products_price = '" . $cost . "' where vendors_products_id = '" . $itemno . "'";
$result = mysql_query($sql);  

}

tep_db_close();

?>

[kunal247]

Hi,

Thank you for your feedback.

 

Will this method also alow .csv files which i have to download via ftp?

Found a contribution called Multi Vendor Feed Manager

Here is a link - http://www.oscommerce.com/community/contributions,7224/page,49

 

I have installed this and seems pretty good but unable to connect via ftp. Keeps giving me an error.

It seems the person who wrote the script is not able to support it.

[♥mdtaylorlrim]

Hi,

Thank you for your feedback.

 

Will this method also alow .csv files which i have to download via ftp?

Found a contribution called Multi Vendor Feed Manager

Here is a link - http://www.oscommerce.com/community/contributions,7224/page,49

 

I have installed this and seems pretty good but unable to connect via ftp. Keeps giving me an error.

It seems the person who wrote the script is not able to support it.

Most of the feed contributions that I know feed FROM your osCommerce store to places like Google Products, Yahoo Shopping, and the like. They are not designed for RECEIVING anything.

 

If you are receiving a csv the script could be edited for that. I shouldn't be too difficult.

[♥mdtaylorlrim]

I should have mentioned, if you use the markup column the format is decimal of 100. 20% should be entered as .20

[♥toyicebear]

Hi,

Thank you for your feedback.

 

Will this method also alow .csv files which i have to download via ftp?

Found a contribution called Multi Vendor Feed Manager

Here is a link - http://www.oscommerce.com/community/contributions,7224/page,49

 

I have installed this and seems pretty good but unable to connect via ftp. Keeps giving me an error.

It seems the person who wrote the script is not able to support it.

 

For csv import/export have a look at, Easy Populate

[♥mdtaylorlrim]

Hi,

Thank you for your feedback.

 

Will this method also alow .csv files which i have to download via ftp?

Found a contribution called Multi Vendor Feed Manager

Here is a link - http://www.oscommerce.com/community/contributions,7224/page,49

 

I have installed this and seems pretty good but unable to connect via ftp. Keeps giving me an error.

It seems the person who wrote the script is not able to support it.

Give me a day and I'll give you one to use with ftp and a csv file. Send me a pm with a sample of the csv file you get and I'll customize it for you.

[♥mdtaylorlrim]

Here is a script to get a csv via ftp and update prices and qty onhand. It has no real safeguards in it and no error trapping. Try it on a test database first.

 

 

<?php

// name this file get_feed.php or whatever you want, just change it below also.
/* use the following cron job to execute this as often as you need. 
             cd /absolute/path/to/catalog/your_admin/
             php get_feed.php
*/

// set options
$use_map = 0;                                                // set to 1 to enable map
$use_markup = 0;                                             //set to 1 to enable markup calculations
$remote_file = 'products.csv'                                // name of products file to get from vendor
$local_file = '/absolute/path/to/catalog/products.csv';      // Change to absolute path to a local file name
$ftp_server = 'ftp://domain.com';                            // Change to fully qualified domain name
$ftp_user_name = 'your_user_name';                           // Change to your user name
$ftp_user_passs = 'password';                                // Change to your password
$remote_dir = '' ;                                           // Change to something other than entry directory

// open some file to write to
$handle = fopen($local_file, 'w');

// set up basic connection
$conn_id = ftp_connect($ftp_server);

// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

// change directory if need to
if (isset($remote_dir)) {
ftp_chdir($conn_id, "somedir")
}

// try to download $remote_file and save it to $handle
if (ftp_fget($conn_id, $handle, $remote_file, FTP_ASCII, 0)) {
echo "successfully written to $local_file\n";                                // may have to comment this out after testing or may get emailed to you by the cron process
} else {
echo "There was a problem while downloading $remote_file to $local_file\n";  // may have to comment this out after testing or may get emailed to you by the cron process
}

// close the connection and the file handler
ftp_close($conn_id);
fclose($handle);

// end of php-ftp code



// grab content os file into array
$lines = file_get_contents($source);

// let's get the database open now
// have to include the configure file
 require('includes/configure.php');

// include the database functions
 require('includes/functions/database.php');

// make a connection to the database... now
 tep_db_connect() or die('Unable to connect to database server!');


// loop through lines in file
foreach($lines as $line) {

$items = explode(",",$line);      // change the , to whatever your field separator is. Use \t for tab delimited.
   $itemno = $items[0];          // change these numbers to match the order of data received in the file
   $qty = $items[1];             // 0 is the first element, 1 is the second element, etc.
   $cost = $items[2];
//  $another_field = $items[3];   // expand if you need to but you will have to add to the sql statement below


if ($use_markup == true) {       // requires a markup column in the categories table with markeup amount as decimal ( .20 as 20%)
// get and set markup
$sql = "select categories.markup from categories,products_to_categories,products where products.products_id = '" . $itemno . "' and products.products_id = products_to_categories.products_id and products_to_categories.categories_id = categories.categories_id";
$result = mysql_query($sql);
$markup = mysql_fetch_row($result);
$price = (int)($cost * (1+$markup[0]))+.99;    // go ahead and change the .99 to whatever you want
$cost = $price;
}

// are we going to use MAP pricing?       Requires a products_map column in the products table, with a value for map, NULL if no MAP
if ($use_map == true) {
$sql = "select products_map from products where products_id = '" . $itemno . "'";
$result = mysql_query($sql);
$map = mysql_fetch_row($result);
if (!(is_null($map[0]))) {
  $cost = $map[0];
  }
}

// let's do it    
$sql = "update products set products_quantity = '" . $qty . "', products_price = '" . $cost . "' where products_id = '" . $itemno . "'";
$result = mysql_query($sql);  

}

tep_db_close();

?>

[♥mdtaylorlrim]

This adds 'across the board' markup functionality.

 

 

<?php

// name this file get_feed.php or whatever you want, just change it below also.
/* use the following cron job to execute this as often as you need. 
             cd /absolute/path/to/catalog/your_admin/
             php get_feed.php
*/

// set options
$use_map = 0;                                                // set to 1 to enable Minimum Advertised Pricing
$use_markup = "0";                                           //set to 1 to enable markup calculations from table, 2 to enable across the board markup
$markup_amount = 0;                                          // set to percentage of markup - .20 = 20% markup
$remote_file = 'products.csv'                                // name of products file to get from vendor
$local_file = '/absolute/path/to/catalog/products.csv';      // Change to absolute path to a local file name
$ftp_server = 'ftp://domain.com';                            // Change to fully qualified domain name
$ftp_user_name = 'your_user_name';                           // Change to your user name
$ftp_user_passs = 'password';                                // Change to your password
$remote_dir = '' ;                                           // Change to something other than entry directory if necessary

// open some file to write to
$handle = fopen($local_file, 'w');

// set up basic connection
$conn_id = ftp_connect($ftp_server);

// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

// change directory if need to
if (isset($remote_dir)) {
ftp_chdir($conn_id, "somedir")
}

// try to download $remote_file and save it to $handle
if (ftp_fget($conn_id, $handle, $remote_file, FTP_ASCII, 0)) {
echo "successfully written to $local_file\n";                                // may have to comment this out after testing or may get emailed to you by the cron process
} else {
echo "There was a problem while downloading $remote_file to $local_file\n";  // may have to comment this out after testing or may get emailed to you by the cron process
}

// close the connection and the file handler
ftp_close($conn_id);
fclose($handle);

// end of php-ftp code



// grab content os file into array
$lines = file_get_contents($source);

// let's get the database open now
// have to include the configure file
 require('includes/configure.php');

// include the database functions
 require('includes/functions/database.php');

// make a connection to the database... now
 tep_db_connect() or die('Unable to connect to database server!');


// loop through lines in file
foreach($lines as $line) {

$items = explode(",",$line);      // change the , to whatever your field separator is. Use \t for tab delimited.
   $itemno = $items[0];          // change these numbers to match the order of data received in the file
   $qty = $items[1];             // 0 is the first element, 1 is the second element, etc.
   $cost = $items[2];
//  $another_field = $items[3];   // expand if you need to but you will have to add to the sql statement below


if ($use_markup == "1") {       // requires a markup column in the categories table with markeup amount as decimal ( .20 as 20%)
// get and set markup
$sql = "select categories.markup from categories,products_to_categories,products where products.products_id = '" . $itemno . "' and products.products_id = products_to_categories.products_id and products_to_categories.categories_id = categories.categories_id";
$result = mysql_query($sql);
$markup = mysql_fetch_row($result);
$price = (int)($cost * (1+$markup[0]))+.99;    // go ahead and change the .99 to whatever you want
$cost = $price;
}

// use across the board markup amount
if ($use_markup == "2") {
$price = (int)($cost * (1+$markup_amount))+.99;    // go ahead and change the .99 to whatever you want
$cost = $price;
}

// are we going to use MAP pricing?       Requires a products_map column in the products table, with a value for map, NULL if no MAP
if ($use_map == true) {
$sql = "select products_map from products where products_id = '" . $itemno . "'";
$result = mysql_query($sql);
$map = mysql_fetch_row($result);
if (!(is_null($map[0]))) {
  $cost = $map[0];
  }
}

// let's do it    
$sql = "update products set products_quantity = '" . $qty . "', products_price = '" . $cost . "' where products_id = '" . $itemno . "'";
$result = mysql_query($sql);  

}

tep_db_close();

?>