Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...