Jump to content
Sign in to follow this  
faithendures

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:

http://www.tacklethisshootthat.com/ttst/store/catalog/admin/get_feed.php

 

The link to my distributor's feed is:

http://www.farrisbrothers.com/pub/invc.csv

 

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:

<?php

/*

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('SKU','products_model');

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":

break;

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

ftp_close($conn_id);

fclose($handle);

// end of php-ftp code

break;

case "http":

default:

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

curl_exec($ch);

curl_close($ch);

fclose($fp);

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

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

$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  

×