Jump to content
Sign in to follow this  

Get Qty/Price Feed From Supplier Database Problem

Recommended Posts

I have installed Get Qty Price Feed From Supplier module and I'm having some trouble. I only want to update price and quantity and use a 30% markup. I do not include all of the products on my distributor's data feed on my website. I only want to update the price/qty of products I use.


When in test mode, I am receiving no errors. The cron job is running successfully. However, when in live mode, there is no update to my database. I have added the markup column to my database. I would like to be able to do an across the board 30% markup but this is not mandatory.


I know very little about php programming other than following simple directions to install oscommerce modules. I feel sure it is a simple problem with my php probably in the "Common Field" section because I was confused about that.


The link to my get_feed.php:



The link to my distributor's feed is:



The link to my products.csv file is: http://www.tacklethisshootthat.com/ttst/store/catalog/products.csv


Please let me know if you need more information.


My get_feed.php file:



1.01 Repaired a missing ;

1.02 Replaced some single quotes with double quotes.

1.03 Updated to make editing easier

1.04 Updated to include an OFF mode, added more instructions

1.05 3/4/2010 Geoffrey Walton

Removed references old price & qty in test output as these were not available.

Allowed rounding to any no of pence/cents and made it optional.

Made test output a table.

Kept the cost price so can be used to update the product if required.

Prevented selling price falling below cost price.

Works with admin/local/configure.php file.

CRON Job Command

You can use the following cron job to execute this script.

php /absolute/path/to/shop/admin/get_feed.php

Edit the absolute path in this command to reference your admin directory.

If you rename this file, change the above command to reflect the new name.


// Set Options

// Set mode of operation // use to set mode to test or live.

$test_mode = 1; // 1 = test mode, 0 = live mode

$working_dir = '/home/jaandki/public_html/ttst/store/catalog/admin'; // Change to a valid working directory for your shop, otherwise your server may not let you execute the file

$local_file = '/home/jaandki/public_html/ttst/store/catalog/products.csv'; // Change to absolute path to a local file name where you will store the file on your server

// For xampp users on windows

//$working_dir = '/htdocs/mt6admin/'; // Have you renamed your admin folder, you have a security hole if you have not.

//$local_file = 'C:\Program Files\xampp\htdocs\mt6admin\products.txt';

// this is the method you will use to get your data feed

$get_method = 'http'; // valid values are 'ftp' or 'http' or 'off' NOTE that http method uses curl and so will not work under xampp

// useful for testing or when a supplier 'puts' a file on your server instead of you having to go out and get it


// These are required if you are collecting the files using ftp

//$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

//$remote_file = 'products.csv'; // name of products file to get from vendor

// this is required is you use the http get method. It should be a valid URL that you get get with a browser

$url_to_file = 'http://www.farrisbrothers.com/pub/invc.csv'; // If http is Get_method this must be a valid URL to the file

// These are general options

$use_map = 0; // set to 1 to enable map, must have map in products table

$use_markup = 1; //set to 1 to enable markup calculations, must have markup in categories table.

$round=true; // true/false : Selling price to always end in xx pence/cents

$round_to = 0.99; // xx p to use, but will never be set to bel.

// Analysis of file

// Visually look at your file and determine if it is a comma separated file or a tab delimited file

// A comma separated file will have lines that look like this: 1234,127.55,2,4567

// A tab delimited file will have lines that look like this: 1234 127.55 2 4567

$type_sep = ","; // use "," for a comma separated file and "\t" for a tab delimited file

// Set data position

// Visually look at the data and enter the position of the type of data in the file.

$item_pos = 1; // set the position on the line where the item number is.

$qty_pos = 13; // set the position on the line where the quantity is.

$price_pos = 12;

/* Set your common field

For this to work your data feed must have a unique field (like products_id or products_model) that mataches one for one with a field in

your database.in order to work properly. Some shops have

installed custom fields. This is where you will define that field. */


define('QTY ON HAND', 'products_quantity');

define('COST', 'products_price');


// //////////////// There are no additional edits unless you encounter errors /////////////////////////////

// Let's shift the position so starts with 0

$item_pos -= 1;

$qty_pos -= 1;

$price_pos -= 1;

// Let's change to the working directory

//chdir($working_dir); // this does not work under xampp so just comment it out

// Start the download

switch ($get_method) {

case "off":


case "ftp":

// 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, $remote_dir);


// 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

} else {

echo "There was a problem while downloading $remote_file to $local_file\n"; // may have to comment this out after testing


// close the connection and the file handle



// end of php-ftp code


case "http":


// following uses curl as php code

$ch = curl_init($url_to_file);

$fp = fopen($local_file, "w");

curl_setopt($ch, CURLOPT_FILE, $fp);

curl_setopt($ch, CURLOPT_HEADER, 0);




// end of php-curl code


// let's get the database open now

// have to include the configure file

// Set the local configuration parameters - mainly for developers

if (file_exists('includes/local/configure.php')) include('includes/local/configure.php');



// include the database functions


// make a connection to the database... now

tep_db_connect() or die('Unable to connect to database server!');

$lines = file($local_file);

if ($test_mode == true) {

echo "<table><tr><td>Item </td><td>New Qty</td><td>Cost</td><td>New Price</td><tr>\n";


// loop through lines in file

foreach ($lines as $line) {

$items = explode ($type_sep, $line);

$itemno = $items[$item_pos];

$qty = $items[$qty_pos];

$cost = $items[$price_pos];

// $another_field = $items[3]; // expand if you need to but you will have to add to the sql statement below to get the data into your database.


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);

if ($round == true) {

$price = (int)($cost * (1+$markup[0]))+$round_to; // add markup and change pence/cents

} else {

$price = ($cost * (1+$markup[0])); // add markup


} else { // supplier is sending over price you are going to sell at.

if ($round == true) {

$price = (int)($cost)+$round_to; // but you want to change the pence/cents

} else {

$price = $cost; // Use supplier's 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]))) {

$price = $map[0];



if ($price < $cost) { // Make sure we are not selling below cost

$price = $cost;


// OK, let's build and execute the sql statement for this item

$sql = "update products set products_quantity = '" . $qty . "', products_price = '" . $price . "' where " . PRODUCTS_ID . "= '" . $itemno . "'";

//$sql = "update products set products_quantity = '" . $qty . "', products_price = '" . $price . "', products_cost = '" . $cost . "' where " . PRODUCTS_ID . "= '" . $itemno . "'";

if ($test_mode == true) {

echo "<td>" . $itemno . "</td><td>" . $qty . "</td><td>" . $cost . "</td><td>" . $price . "</td></tr>\n";

} else {

$result = mysql_query($sql);



} // back up to process next item

if ($test_mode == true) {

echo "</table>\n";


tep_db_close(); // and finally, close the db and we are done.


Thanks! Any help would be greatly appreciated. I have spent weeks trying to figure this out on my own and have read every single thing I could find, all to no avail.

Share this post

Link to post
Share on other sites

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
Sign in to follow this