Jump to content
gottaloveit

Automatically send data feed to Froogle

Recommended Posts

Unfortunately I, and probably a few other here, am not a web-master or coder. I needed a website so have struggled to learn what is needed to make a site work through necessity. My goal is to fulfill Google's requirements by somehow submitting the existing Manufacturer and Products Model data as 'brand' and UPI. I already have a unique Products Model for each item stocked. I can pre-fill manufacturer with the store name or abbreviation (The Store in Devon = TSID). I am just trying to work out how to achieve this goal.

You're doing it correctly. Just fill in the fields you want to use, if they are not already filled in, and then set those options to use those fields. For example, make sure the model number for all products are filled in and set Brand to model. That will work in the majority of shops, based on my experience, but may not for some. All you can do is try.

Share this post


Link to post
Share on other sites

hello guys help me , here i don't know how to add new drop down list in osc, and i already create my own version of html and php drop down list but i dont know how to integrate with osc .

Share this post


Link to post
Share on other sites

Hello

 

Hope somebody can help me. This issue may have come up before and if somebody can point me in the right direction I would be delighted.

 

I have installed the contrib which is working fine. I also have SEO urls installed.

 

When the data feed is created the product urls have the hyphens missing

 

http://www.outdoorworld.co.uk/raised-beds-and-planter-kits-box-urban-corner-1500x1500x550-p-713.html

 

becomes

 

http://www.outdoorworld.co.uk/raised-beds-and-planter-kits-box-urbancorner1500x1500x550-p-713.html

 

How can i get the hyphens to remain in place?

 

Any help would be super!

 

Many thanks

 

Henry

Share this post


Link to post
Share on other sites

faiza, I am far from an expert but did recently encounter a cron issue that was caused by insufficient file access rights. Check that the file(s) permissions are 755.

Share this post


Link to post
Share on other sites

I have installed the contrib which is working fine. I also have SEO urls installed.

 

When the data feed is created the product urls have the hyphens missing

 

http://www.outdoorworld.co.uk/raised-beds-and-planter-kits-box-urban-corner-1500x1500x550-p-713.html

 

becomes

 

http://www.outdoorworld.co.uk/raised-beds-and-planter-kits-box-urbancorner1500x1500x550-p-713.html

Try turning off the "Remove all non-alphanumeric characters?" setting to see if that makes a difference.

Share this post


Link to post
Share on other sites

Hi

Sorry if this seems such a basic question but how do you change the language? I have tried messing around with this line

 

define('DEFAULT_LANGUAGE', 1); //Change this to the id of your language. BY default 1 is english

 

Thanks for a fantastic contribution

Share this post


Link to post
Share on other sites

Hi

Sorry if this seems such a basic question but how do you change the language? I have tried messing around with this line

 

define('DEFAULT_LANGUAGE', 1); //Change this to the id of your language. BY default 1 is english

 

Thanks for a fantastic contribution

Also set OPTIONS_ENABLED_FEED_LANGUAGE and OPTIONS_FEED_LANGUAGE.

Share this post


Link to post
Share on other sites

Sorted the language problem sorry, now I am trying to get the currency to match up correctly, it seems to display the wrong price in the googlebase text.

Share this post


Link to post
Share on other sites

Sorted the language problem sorry, now I am trying to get the currency to match up correctly, it seems to display the wrong price in the googlebase text.

How about searching the options for currency?

Share this post


Link to post
Share on other sites

Hi Jack,

 

I know I have asked this question some time ago, but wondered if you had any more thoughts on the matter.....

 

Approx 25% of the items in my stores are not subject to Tax (Vat). In the Google Feeder file I have turned the tax function to "on" which adds 20% tax to All prices.

 

For the items that are not subject to Vat this makes me appear 20% more expensive than my compeditors on Google Shopping - so obviously for these items Im not getting many sales from Google.

 

Is there a way (other than manually altering the text file prior to submitting) for googlefeeder to pass through the correct price of items not subject to Tax? or a workaround?

 

I cant be the only one with this problem can I ??

 

Many Thanks


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

Hi Jack,

 

I know I have asked this question some time ago, but wondered if you had any more thoughts on the matter.....

 

Approx 25% of the items in my stores are not subject to Tax (Vat). In the Google Feeder file I have turned the tax function to "on" which adds 20% tax to All prices.

 

For the items that are not subject to Vat this makes me appear 20% more expensive than my compeditors on Google Shopping - so obviously for these items Im not getting many sales from Google.

 

Is there a way (other than manually altering the text file prior to submitting) for googlefeeder to pass through the correct price of items not subject to Tax? or a workaround?

 

I cant be the only one with this problem can I ??

 

Many Thanks

It's not a simple problem. I don't know how it is in the UK but in the States, the tax usually only applies to certain States. So a product might be taxable for resisdents of California but not for those of Texas and there's no way to do that in the feed since there's no such things as customers at that point. That's the reason for the new tax option in the code, though it can be difficult to setup. You can try the following, which has not been tested at all. In the googlefeeder.php file, replace this

FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * " . OPTIONS_TAX_CALC . ",2) AS price,

with this:

FORMAT(IFNULL(specials.specials_new_products_price, products.products_price) * IFNULL(NULLIF(products.products_tax_class_id, 0)," . OPTIONS_TAX_CALC . "), 2) AS price,

Share this post


Link to post
Share on other sites

Hi Jack I will try that later and let you know.

 

In the UK 20% vat is applied to goods regardless of where they are sent. However, some items - Food, Food Plants, Childrens Clothing, Some medicinal products, books etc) are zero Vat rated - so Vat does not get added to these items.

 

Many Thanks


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

Hi Jack I will try that later and let you know.

 

In the UK 20% vat is applied to goods regardless of where they are sent. However, some items - Food, Food Plants, Childrens Clothing, Some medicinal products, books etc) are zero Vat rated - so Vat does not get added to these items.

 

Many Thanks

I don't see anyway around this other than to have a special field. Even if the tax was straightforward as that, how would the code determine which category to apply the tax to? You can place the ID's in the file but that becomes a maintenance issue if a category is added or changed.

Share this post


Link to post
Share on other sites

Hi again,

 

Sorry to ask such difficult questions.

 

The store shows the prices correctly, because the individual items have their Tax class set to either no tak or vat @ 20% depending if they are taxable items or not.

 

So I will try the code you suggested above and see what happens.

 

Thanks


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

Hi Jack,

 

Using the code you posted above almost worked, however, in the output file it stripped the vat from the items that should have vat applied and left vat on the ones that shouldnt have vat applied.

 

Many Thanks


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

Hello:

 

is there a way to split the feed file automatically?

 

Google is only allowing 50,000 items per feed but will allow 10 feeds per account. At this point anything over the 50,000 items are refused if they are in a single feed.

 

any ideas?

 

Thanks

Pete

Share this post


Link to post
Share on other sites

Hello:

 

is there a way to split the feed file automatically?

 

Google is only allowing 50,000 items per feed but will allow 10 feeds per account. At this point anything over the 50,000 items are refused if they are in a single feed.

 

any ideas?

No, it's not possible with the current code. A change could be made to track the number of items and then close the current feed and open a new one when that is done. But specialize coding is something covered in a support thread so you'll need to figure out a solution or pay someone to code it.

Share this post


Link to post
Share on other sites

Hi Again,

 

If I change the code you suggested to:

FORMAT(IFNULL(specials.specials_new_products_price, products.products_price) * IFNULL(NULLIF(products.products_tax_class_id, 1)," . OPTIONS_TAX_CALC . "), 2) AS price,

 

It leaves out the items that are not subject to Vat

 

There must be a way to do this with some form of else if statement ?

 

Thanks


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

Hi Again,

 

If I change the code you suggested to:

FORMAT(IFNULL(specials.specials_new_products_price, products.products_price) * IFNULL(NULLIF(products.products_tax_class_id, 1)," . OPTIONS_TAX_CALC . "), 2) AS price,

 

It leaves out the items that are not subject to Vat

 

There must be a way to do this with some form of else if statement ?

 

Thanks

The NULLIF is mysql's if/then statement. That's why I added it in that example. But, as mentioned, it was untested so there may be some logic mistake in it.

Share this post


Link to post
Share on other sites

Thanks Jack,

 

SQL baffles me - so playing with that is a non starter for me.

 

To simplify this would it be possible to run two instances of Googlefeeder.php ie googlefeeder1 & googlefeeder2

 

The first one would pick up and submit products with a tax class of 1 and add vat to them as per normal as datafeed1.txt

 

The second one to pick up and submit products with no tax class and not add vat to them as datafeed2.txt

 

Im clutching at straws here but I need to find a way to solve this.

 

Thanks


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

Thanks Jack,

 

SQL baffles me - so playing with that is a non starter for me.

 

To simplify this would it be possible to run two instances of Googlefeeder.php ie googlefeeder1 & googlefeeder2

 

The first one would pick up and submit products with a tax class of 1 and add vat to them as per normal as datafeed1.txt

 

The second one to pick up and submit products with no tax class and not add vat to them as datafeed2.txt

 

Im clutching at straws here but I need to find a way to solve this.

 

Thanks

You can rename the file and change the options in each as many times as you like. The feed name would have to be different but google will allow you have multiple feeds so that is not a problem. But you will still have to have a way for the code to decide if tax should be applied or not.

Share this post


Link to post
Share on other sites

Could this be done on the Tax class by writing something int the file that would:

 

1) Only Pick up a product if its tax class = 1

2) Only Pick up a product if its tax class did not = 1

 

am I getting warmer ?

 

Thanks


Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Share this post


Link to post
Share on other sites

Could this be done on the Tax class by writing something int the file that would:

 

1) Only Pick up a product if its tax class = 1

2) Only Pick up a product if its tax class did not = 1

 

am I getting warmer ?

 

Thanks

That setting can be any number so I wouldn't use it for such a test but in your case it should work. But I think that was what I did in the code I posted.

Share this post


Link to post
Share on other sites

Hi Jack,

 

Of course it is often ask, I searched the forum several times, but couldn't find it

I have installed your great conribution, but can't get it working.

It is used for the netherlands, but do I have to set it otherwise?

 

Here are my settings.

 

 

<?php

// Title: Google Base Data Feeder

// Last Update: 12/25/09 by Jack_mcs at oscommerce-solution.com

// google feed requirements - http://www.google.com/support/merchants/bin/answer.py?hl=en&answer=188494

 

chdir('../');

include_once 'includes/application_top.php';

 

/*************** BEGIN MASTER SETTINGS ******************/

 

define('SEO_ENABLED','true'); //Change to 'false' to disable if Ultimate SEO URLs is not installed

define('FEEDNAME', 'googlefeed.txt'); //from your googlebase account

define('DOMAIN_NAME', 'www.cyber-seller.nl'); //your correct domain name (don't include www unless it is used)

define('FTP_USERNAME', 'xxxxx'); //created from within your googlebase account

define('FTP_PASSWORD', 'xxxx'); //created from within your googlebase account

define('FTP_ENABLED', (isset($_GET['1']) ? '0' : '1')); //set to 0 to disable

define('CONVERT_CURRENCY', '0'); //set to 0 to disable - only needed if a feed in a difference currecny is required

define('CURRENCY_TYPE', 'EUR'); //(eg. USD, EUR, GBP)

define('DEFAULT_LANGUAGE_ID', 2); //Change this to the id of your language. BY default 1 is english

define('QUOTES_CATEGORY_NAME',''); //if the Quotes contribution is installed, enter the name of the quotes category here

 

/*************** OPTIONS - IF ENABLED, ALSO SET THE SIMILAR OPTION FARTHER DOWN ******************/

define('OPTIONS_ENABLED', 1);

define('OPTIONS_ENABLED_AGE_RANGE', 0);

define('OPTIONS_ENABLED_ATTRIBUTES', 0);

define('OPTIONS_ENABLED_BRAND', 1); //if set, see options for this setting below

define('OPTIONS_ENABLED_CONDITION', 1);

define('OPTIONS_ENABLED_CURRENCY', 0);

define('OPTIONS_ENABLED_EXPIRATION', 1);

define('OPTIONS_ENABLED_FEED_LANGUAGE', 0);

define('OPTIONS_ENABLED_GTIN', 0); //if set, a database field named products_gtin must exist

define('OPTIONS_ENABLED_GOOGLE_PRODUCT_CATEGORY', 1); //http://www.google.com/support/merchants/bin/answer.py?answer=160081

define('OPTIONS_ENABLED_GOOGLE_UTM', 0);

define('OPTIONS_ENABLED_ISBN', 0); //if set, a database field named products_isbn must exist

define('OPTIONS_ENABLED_MADE_IN', 0);

define('OPTIONS_ENABLED_MPN', 1); //if set, see options for this setting below

define('OPTIONS_ENABLED_PRODUCT_MODEL', 0); //displays the product model

define('OPTIONS_ENABLED_PRODUCT_TYPE', 1);

define('OPTIONS_ENABLED_SHIPPING', 0);

define('OPTIONS_ENABLED_INCLUDE_TAX', 2); //0 = no tax, 1 = uses google method, 2 = UK Vat

define('OPTIONS_ENABLED_UPC', 0); //if set, a database field named products_upc must exist

define('OPTIONS_ENABLED_WEIGHT', 0);

 

//some of the following only work if the matching option is enabled above.

define('OPTIONS_AGE_RANGE', '20-90 years');

 

define('OPTIONS_AVAILABILITY', 'in stock'); //in stock - Include this value if you are certain that it will ship (or be in-transit to the customer) in 3 business days or less.

//available for order - Include this value if it will take 4 or more business days to ship it to the customer.

//out of stock - You’re currently not accepting orders for this product.

//preorder - You are taking orders for this product, but it’s not yet been released.

//if empty (no entry), the data will be loaded from the database. A field in the products description table named products_availability is required

//if "quantity," the field will be popuplated via the quantity: 0 or less = out of stock, greater than 0 = in stock

//if "status," the field will be popuplated via the status field. in or out of stock

 

define('OPTIONS_BRAND', 'manu'); //leave blank to load from the database field named products_brand, set to "name" to substitute the products name, manu to substitute the manufactueres name or model to substitute the products model

define('OPTIONS_CONDITION', 'new'); //possible entries are New, Refurbished, Used or blank, which loads from the database field named products_condition

define('OPTIONS_CURRENCY', 'USD');

define('OPTIONS_CURRENCY_THOUSANDS_POINT', ','); //this is the thousands point as in $1,000.

define('OPTIONS_DATE_FORMAT', 'Y-m-d'); //change how the date is formatted

define('OPTIONS_FEED_LANGUAGE', 'dutch');

define('OPTIONS_GOOGLE_UTM', '?utm_source=GoogleBase1&utm_medium=BaseFeed1&utm_campaign=products'); //see http://www.google.com/support/googleanalytics/bin/answer.py?hl=en&answer=55578

define('OPTIONS_GOOGLE_PRODUCT_CATEGORY', 'Electronics'); //enter db to load from a database field named google_product_category enter or enter a specific google category - see taxomy - http://www.google.com/support/merchants/bin/answer.py?answer=160081

define('OPTIONS_GTIN', '');

define('OPTIONS_ISBN', '');

define('OPTIONS_MADE_IN', 'US');

define('OPTIONS_MPN', 'manu'); //leave blank to load from the database field named products_mpn, set to "name" to substitute the products name, manu to substitute the manufactueres name or model to substitute the products model

define('OPTIONS_PRODUCT_TYPE', 'full'); //full means the full category path (i.e., hardware,printers), anything else, or blank, means just the products category (i.e., printers)

 

//the following is for the shipping override option - enter multiple values separated by a comma

//Format entries follow. A colon must be present for each field, whether it is entered or not.

// COUNTRY - OPTIONAL - If country isn't included, we'll assume the shipping price applies to the target country of the item. If region isn't included, the shipping price will apply across the entire country.

// REGION - OPTIONAL - blank for entire country, otherwise, us two-letter State (CA), full zip code (90210) or wildcard zip code (902*)

// SERVICE - OPTIONAL - The service class or delivery speed, i.e. ground

// PRICE - REQUIRED - Fixed shipping price (assumes the same currency as the price attribute)

define('OPTIONS_SHIPPING_STRING', 'US:FL:Ground:7.00'); //says charge shipping to US for residents of Florida at 5% and don't apply tax to shipping

 

define('OPTIONS_TAX_RATE' , '19.0'); //default = 0 (e.g. for 20.0% tax use "$taxRate = 20.0;") //only used in the next line

define('OPTIONS_TAX_CALC', (OPTIONS_ENABLED_INCLUDE_TAX == 2 ? (OPTIONS_TAX_RATE/100) + 1 : '1')); //UK. US tax rate - US is ignorded since it is 1

//the following is for the tax override option - enter multiple values separated by a comma

//Format entries follow. A colon must be present for each field, whether it is entered or not.

// COUNTRY - OPTIONAL - country the tax applies to - only US for now

// REGION - OPTIONAL - blank for entire country, otherwise, us two-letter State (CA), full zip code (90210) or wildcard zip code (902*)

// TAX - REQUIRED - default = 19 (e.g. for 5.76% tax use 5.76)

// SHIPPING - OPTIONAL - do you charge tax on shipping - choices are y or n

define('OPTIONS_TAX_STRING', '19.00'); //says charge tax to US for residents of Florida at 5% and don't apply tax to shipping

 

define('OPTIONS_UPC', '');

define('OPTIONS_WEIGHT_ACCEPTED_METHODS', 'kg'); //Valid units include lb, pound, oz, ounce, g, gram, kg, kilogram.

 

//the following allow skipping certain items

define('OPTIONS_IGNORE_PRODUCT_PRICE', 0); //0 = include products with price of 0 in output, 1 = ignore products with price of 0

define('OPTIONS_IGNORE_PRODUCT_ZERO', 0); //0 = include products with qty of 0 in output, 1 = ignore products with qty of 0

 

/*************** END MASTER SETTINGS ******************/

 

 

/*************** NO EDITS NEEDED BELOW THIS LINE *****************/

 

//********************

// Start TIMER

// -----------

$stimer = explode( ' ', microtime() );

$stimer = $stimer[1] + $stimer[0];

// -----------

 

 

$OutFile = "feeds/" . FEEDNAME;

$destination_file = FEEDNAME;

$source_file = $OutFile;

$imageURL = 'http://' . DOMAIN_NAME . '/images/';

if(SEO_ENABLED=='true'){

$productURL = 'product_info.php'; // ***** Revised for SEO

$productParam = "products_id="; // ***** Added for SEO

}else{

$productURL = 'http://' . DOMAIN_NAME . '/product_info.php?products_id=';

}

 

$already_sent = array();

 

if(CONVERT_CURRENCY)

{

if(SEO_ENABLED=='true'){

$productParam="currency=" . CURRENCY_TYPE . "&products_id=";

}else{

$productURL = "http://" . DOMAIN_NAME . "/product_info.php?currency=" . CURRENCY_TYPE . "&products_id="; //where CURRENCY_TYPE is your currency type (eg. USD, EUR, GBP)

}

}

 

$feed_exp_date = @date(OPTIONS_DATE_FORMAT, time() + 2419200 );

 

if (!($link=mysql_connect(DB_SERVER,DB_SERVER_USERNAME,DB_SERVER_PASSWORD))) {

echo "Error when connecting itself to the data base";

exit();

}

if (!mysql_select_db( DB_DATABASE , $link )) {

echo "Error the data base does not exist";

exit();

}

 

$quotes = '';

if (QUOTES_CATEGORY_NAME !== '') {

$quotes = " and products.customers_email_address = '' and products.quotes_email_address = ''";

}

 

$extraFields = '';

if (OPTIONS_AVAILABILITY == '') {

$extraFields .= ' products_description.products_availability as availability, ';

}

if (OPTIONS_ENABLED_BRAND == 1) {

if (strlen(OPTIONS_BRAND) == 0) {

$extraFields .= ' products.products_brand as brand, ';

} else {

switch (OPTIONS_BRAND) {

case 'name': $extraFields .= ' products_description.products_name as brand, '; break;

case 'manu': $extraFields .= ' manufacturers.manufacturers_name as brand, '; break;

case 'model': $extraFields .= ' products.products_model as brand, '; break;

default: $extraFields .= ' products_description.products_name as brand, ';

}

}

}

if (OPTIONS_ENABLED_CONDITION == 1 && strlen(OPTIONS_CONDITION) == 0) {

$extraFields .= ' products.products_condition as pcondition, ';

}

if (OPTIONS_ENABLED_GTIN == 1 && strlen(OPTIONS_GTIN) == 0) {

$extraFields .= ' products.products_gtin as gtin, ';

}

if (OPTIONS_ENABLED_ISBN == 1 && strlen(OPTIONS_ISBN) == 0) {

$extraFields .= ' products.products_isbn as isbn, ';

}

if (OPTIONS_ENABLED_MPN == 0) {

if (strlen(OPTIONS_MPN) == 0) {

$extraFields .= ' products.products_mpn as mpn, ';

} else {

switch (OPTIONS_MPN) {

case 'name': $extraFields .= ' products_description.products_name as mpn, '; break;

case 'manu': $extraFields .= ' manufacturers.manufacturers_name as mpn, '; break;

case 'model': $extraFields .= ' products.products_model as mpn, '; break;

default: $extraFields .= ' products_description.products_name as mpn, ';

}

}

}

if (OPTIONS_ENABLED_UPC == 1 && strlen(OPTIONS_UPC) == 0) {

$extraFields .= ' products.products_upc as upc, ';

}

 

if (OPTIONS_ENABLED_GOOGLE_PRODUCT_CATEGORY == 1 && OPTIONS_GOOGLE_PRODUCT_CATEGORY == 'db') {

$extraFields .= ' products_description.google_product_category as google_category, ';

}

 

 

$sql = "

SELECT concat( '" . $productURL . "' ,products.products_id) AS product_url,

products_model AS prodModel,

manufacturers.manufacturers_id,

products.products_id AS id,

products_description.products_name AS name,

products_description.products_description AS description,

products.products_quantity AS quantity,

products.products_status AS prodStatus,

products.products_weight AS prodWeight, " . $extraFields . "

FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * " . OPTIONS_TAX_CALC . ",2) AS price,

CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,

products_to_categories.categories_id AS prodCatID,

categories.parent_id AS catParentID,

categories_description.categories_name AS catName

FROM (categories,

categories_description,

products,

products_description,

products_to_categories)

 

left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )

left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date is NULL) OR (specials.expires_date = 0) ) AND ( specials.status = 1 ) ) )

 

WHERE products.products_id=products_description.products_id

AND products.products_id=products_to_categories.products_id

AND products_to_categories.categories_id=categories.categories_id

AND categories.categories_id=categories_description.categories_id " . $quotes . "

AND categories_description.language_id = " . DEFAULT_LANGUAGE_ID . "

AND products_description.language_id = " . DEFAULT_LANGUAGE_ID . "

ORDER BY

products.products_id ASC

";

 

$quotes = '';

if (QUOTES_CATEGORY_NAME !== '') {

$quotes = " and categories_description.categories_name NOT LIKE '" . QUOTES_CATEGORY_NAME . "' ";

}

 

$catInfo = "

SELECT

categories.categories_id AS curCatID,

categories.parent_id AS parentCatID,

categories_description.categories_name AS catName

FROM

categories,

categories_description

WHERE categories.categories_id = categories_description.categories_id " . $quotes . "

AND categories_description.language_id = " . DEFAULT_LANGUAGE_ID . "";

 

function findCat($curID, $catTempPar, $catTempDes, $catIndex) {

if( (isset($catTempPar[$curID])) && ($catTempPar[$curID] != 0) ) {

if(isset($catIndex[$catTempPar[$curID]])) {

$temp=$catIndex[$catTempPar[$curID]];

} else {

$catIndex = findCat($catTempPar[$curID], $catTempPar, $catTempDes, $catIndex);

$temp = $catIndex[$catTempPar[$curID]];

}

}

if( (isset($catTempPar[$curID])) && (isset($catTempDes[$curID])) && ($catTempPar[$curID] == 0) ) {

$catIndex[$curID] = $catTempDes[$curID];

} else {

$catIndex[$curID] = $temp . ", " . $catTempDes[$curID];

}

return $catIndex;

}

 

$catIndex = array();

$catTempDes = array();

$catTempPar = array();

$processCat = mysql_query( $catInfo )or die( $FunctionName . ": SQL error " . mysql_error() . "| catInfo = " . htmlentities($catInfo) );

 

while ( $catRow = mysql_fetch_object( $processCat ) ) {

$catKey = $catRow->curCatID;

$catName = $catRow->catName;

$catParID = $catRow->parentCatID;

if($catName != "") {

$catTempDes[$catKey]=$catName;

$catTempPar[$catKey]=$catParID;

}

}

 

foreach($catTempDes as $curID=>$des) { //don't need the $des

$catIndex = findCat($curID, $catTempPar, $catTempDes, $catIndex);

}

 

$_strip_search = array(

"![\t ]+$|^[\t ]+!m", // remove leading/trailing space chars

'%[\r\n]+%m'); // remove CRs and newlines

$_strip_replace = array(

'',

' ');

$_cleaner_array = array(">" => "> ", "®" => "", " " => " ", "®" => "", "™" => "", "™" => "", "\t" => "", " " => "", """ => "\"");

 

 

if ( file_exists( $OutFile ) ) {

unlink( $OutFile );

}

 

$output = "link\ttitle\tdescription\tprice\timage_link\tid\tavailability";

$attributesColumns = array();

 

//create optional section

if(OPTIONS_ENABLED == 1) {

if(OPTIONS_ENABLED_AGE_RANGE == 1) $output .= "\tage_range";

if(OPTIONS_ENABLED_BRAND == 1) $output .= "\tbrand";

if(OPTIONS_ENABLED_CONDITION == 1) $output .= "\tcondition";

if(OPTIONS_ENABLED_CURRENCY == 1) $output .= "\tcurrency";

if(OPTIONS_ENABLED_EXPIRATION == 1) $output .= "\texpiration_date";

if(OPTIONS_ENABLED_FEED_LANGUAGE == 2) $output .= "\tlanguage";

if(OPTIONS_ENABLED_GOOGLE_PRODUCT_CATEGORY == 1) $output .= "\tgoogle product_category";

if(OPTIONS_ENABLED_GTIN == 1) $output .= "\tgtin";

if(OPTIONS_ENABLED_ISBN == 1) $output .= "\tisbn";

if(OPTIONS_ENABLED_MADE_IN == 1) $output .= "\tmade_in";

if(OPTIONS_ENABLED_MPN == 1) $output .= "\tmpn";

if(OPTIONS_ENABLED_PRODUCT_MODEL == 1) $output .= "\tmodel";

if(OPTIONS_ENABLED_PRODUCT_TYPE == 1) $output .= "\tproduct_type";

if(OPTIONS_ENABLED_SHIPPING == 1) $output .= "\tshipping";

if(OPTIONS_ENABLED_INCLUDE_TAX == 1) $output .= "\ttax";

if(OPTIONS_ENABLED_UPC == 1) $output .= "\tupc";

if(OPTIONS_ENABLED_WEIGHT == 1) $output .= "\tweight";

 

if (OPTIONS_ENABLED_ATTRIBUTES == 1) {

$products_options_name_query = mysql_query("select distinct popt.products_options_id, popt.products_options_name from products_options popt, products_attributes patrib where popt.language_id = '" . (int)1 . "' order by popt.products_options_name") or die(mysql_error());

while ($products_options_name = mysql_fetch_object($products_options_name_query)) {

$attributesColumns[] = $products_options_name->products_options_name;

$name = strtolower($products_options_name->products_options_name);

$name = str_replace(" ","_", $name);

$output .= "\tc:" . $name;

}

 

/*

//If you want to only show particular attributes, comment out the above and uncomment this section.

//Then enter two lines for each one you want to show. For example, if the atttributes you want to

//show are named Color and Fabric, the entries would appear as follows:

 

$attributesColumns[] = "Color";

$attributesColumns[] = "Fabric";

 

$output .= "\tc:" . strtolower("Color");

$output .= "\tc:" . strtolower("Fabric");

*/

}

}

$output .= "\n";

 

 

$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );

 

//Currency Information

if(CONVERT_CURRENCY) {

$sql3 = "

SELECT

currencies.value AS curUSD

FROM

currencies

WHERE currencies.code = '" . CURRENCY_TYPE . "'";

 

$result3=mysql_query( $sql3 )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql3 = " . htmlentities($sql3) );

$row3 = mysql_fetch_object( $result3 );

}

 

$loop_counter = 0;

$statsArray = array();

 

while( $row = mysql_fetch_object( $result ) ) {

if (isset($already_sent[$row->id])) continue; // if we've sent this one, skip the rest of the while loop

if (OPTIONS_IGNORE_PRODUCT_PRICE > 0 && $row->price <= 0) continue; //skip products with 0 price

if (OPTIONS_IGNORE_PRODUCT_ZERO > 0 && $row->quantity < 1) continue; //skip products with 0 qty

if (OPTIONS_IGNORE_PRODUCT_PRICE < 1 && $row->price <= 0) $statsArray['price']++; //record for warning

if (OPTIONS_IGNORE_PRODUCT_ZERO < 1 && $row->quantity < 1) $statsArray['qty']++; //record for warning

 

$statsArray['total']++;

 

if ( $row->prodStatus == 1 ) {

if (CONVERT_CURRENCY) {

$row->price = preg_replace("/[^.0-9]/", "", $row->price);

$row->price = $row->price * $row3->curUSD;

$row->price = number_format($row->price, 2, '.', OPTIONS_CURRENCY_THOUSANDS_POINT);

}

 

$availability = '';

switch (OPTIONS_AVAILABILITY) {

case 'quantity': $availability = ($row->quantity > 0 ? 'in stock' : 'out of stock'); break;

case 'status': $availability = ($row->prodStatus == 1 ? 'in stock' : 'out of stock'); break;

case '': $availability = $row->availability; break;

default: $availability = OPTIONS_AVAILABILITY;

}

 

$google_utm = (OPTIONS_ENABLED_GOOGLE_UTM ? OPTIONS_GOOGLE_UTM : '');

 

if(SEO_ENABLED=='true'){

$output .= tep_href_link($productURL,$productParam . $row->id, 'NONSSL', false) . $google_utm . "\t";

} else {

$output .= $row->product_url . $google_utm . "\t";

}

 

$output .=

preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->name, $_cleaner_array) ) ) . "\t" .

preg_replace($_strip_search, $_strip_replace, strip_tags( strtr($row->description, $_cleaner_array) ) ) . "\t" .

$row->price . "\t" .

$row->image_url . "\t" .

// $catIndex[$row->prodCatID] . "\t" .

$row->id . "\t" .

$availability;

 

//optional values section

if(OPTIONS_ENABLED == 1) {

if(OPTIONS_ENABLED_AGE_RANGE == 1)

$output .= "\t" . OPTIONS_AGE_RANGE;

if(OPTIONS_ENABLED_BRAND == 1)

$output .= "\t" . (isset($row->brand) ? $row->brand : (strlen(OPTIONS_BRAND) ? $row->name : "Not Supported"));

if(OPTIONS_ENABLED_CONDITION == 1)

$output .= "\t" . (isset($row->pcondition) ? $row->pcondition : OPTIONS_CONDITION);

if(OPTIONS_ENABLED_CURRENCY == 1)

$output .= "\t" . OPTIONS_CURRENCY;

if(OPTIONS_ENABLED_EXPIRATION == 1)

$output .= "\t" . $feed_exp_date;

if(OPTIONS_ENABLED_FEED_LANGUAGE == 1)

$output .= "\t" . OPTIONS_FEED_LANGUAGE;

if(OPTIONS_ENABLED_GOOGLE_PRODUCT_CATEGORY == 1)

$output .= "\t" . (OPTIONS_GOOGLE_PRODUCT_CATEGORY == 'db' ? $row->google_category : OPTIONS_GOOGLE_PRODUCT_CATEGORY);

if(OPTIONS_ENABLED_GTIN == 1)

$output .= "\t" . (isset($row->gtin) ? $row->gtin : (strlen(OPTIONS_GTIN) ? OPTIONS_GTIN : "Not Supported"));

if(OPTIONS_ENABLED_ISBN == 1)

$output .= "\t" . (isset($row->isbn) ? $row->isbn : (strlen(OPTIONS_ISBN) ? OPTIONS_ISBN : "Not Supported"));

if(OPTIONS_ENABLED_MADE_IN == 1)

$output .= "\t" . OPTIONS_MADE_IN;

if(OPTIONS_ENABLED_MPN == 1)

$output .= "\t" . (isset($row->mpn) ? $row->mpn : (strlen(OPTIONS_MPN) ? OPTIONS_MPN : "Not Supported"));

if(OPTIONS_ENABLED_PRODUCT_MODEL == 1)

$output .= "\t" . (! empty($row->prodModel) ? $row->prodModel : $row->catName);

if(OPTIONS_ENABLED_PRODUCT_TYPE == 1)

$output .= "\t" . ((OPTIONS_PRODUCT_TYPE == strtolower('full')) ? $catIndex[$row->prodCatID] : $row->catName);

if(OPTIONS_ENABLED_SHIPPING == 1)

$output .= "\t" . OPTIONS_SHIPPING_STRING;

if(OPTIONS_ENABLED_INCLUDE_TAX == 1)

$output .= "\t" . OPTIONS_TAX_STRING;

if(OPTIONS_ENABLED_UPC == 1)

$output .= "\t" . (isset($row->upc) ? $row->upc : (strlen(OPTIONS_UPC) ? OPTIONS_UPC : "Not Supported"));

if(OPTIONS_ENABLED_WEIGHT == 1)

$output .= "\t" . $row->prodWeight . ' ' .OPTIONS_WEIGHT_ACCEPTED_METHODS;

 

/******************* BEGIN HANDLING THE ATTRIBUTES ********************/

if (OPTIONS_ENABLED_ATTRIBUTES == 1)

{

$products_attributes_query = mysql_query("select count(*) as total from products_options popt, products_attributes patrib where patrib.products_id='" . $row->id . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)1 . "'");

$products_attributes = mysql_fetch_object($products_attributes_query);

if ($products_attributes->total > 0) {

$products_options_name_query = mysql_query("select distinct popt.products_options_id, popt.products_options_name from products_options popt, products_attributes patrib where patrib.products_id='" . (int)$row->id . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)1 . "' order by popt.products_options_name") or die(mysql_error());

 

$trackTabs = '';

 

while ($products_options_name = mysql_fetch_object($products_options_name_query)) {

$products_options_array = array();

$products_options_query = mysql_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from products_attributes pa, products_options_values pov where pa.products_id = '" . (int)$row->id . "' and pa.options_id = '" . $products_options_name->products_options_id . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)1 . "'");

while ($products_options = mysql_fetch_object($products_options_query)) {

$products_options_array[] = array('id' => $products_options->products_options_values_id, 'text' => $products_options->products_options_values_name);

}

 

for ($a = 0; $a < count($attributesColumns); ++$a)

{

if ($products_options_name->products_options_name == $attributesColumns[$a])

{

if ($a == 0)

$trackTabs = "\t";

else

{

if (empty($trackTabs))

$trackTabs = str_repeat("\t", $a);

$trackTabs .= "\t";

}

 

$output .= $trackTabs;

foreach ($products_options_array as $arr)

$output .= $arr['text'] . ',';

$output = substr($output, 0, -1);

}

}

}

}

}

/******************* END HANDLING THE ATTRIBUTES ********************/

}

$output .= " \n";

}

 

$already_sent[$row->id] = 1;

$loop_counter++;

 

if ($loop_counter>750) {

$fp = fopen( $OutFile , "a" );

$fout = fwrite( $fp , $output );

fclose( $fp );

$loop_counter = 0;

$output = "";

}

}

 

$fp = fopen( $OutFile , "a" );

$fout = fwrite( $fp , $output );

fclose( $fp );

 

echo '<p style="margin:auto; text-align:left">';

printf( "Feed contains %d products.", $statsArray['total'] );

echo '</p>';

 

$warning = false;

if (count($statsArray['price']) > 0) {

$warning = true;

echo '<p style="margin:auto; text-align:left">';

printf( " ***Warning:*** There are %d products with a price of $0.", $statsArray['price'] );

echo '</p>';

}

 

if (count($statsArray['qty']) > 0) {

$warning = true;

echo '<p style="margin:auto; text-align:left; padding-bottom:10px; ">';

printf( " ***Warning:*** There are %d products with a quantity of 0.", $statsArray['qty'] );

echo '</p>';

}

 

$completed = 'File Completed' . ($warning ? ' (with warnings): ' : ':' );

 

echo "$completed <a href=\"../" . $OutFile . "\" target=\"_blank\">" . $destination_file . "</a><br>\n";

chmod($OutFile, 0777);

 

 

//Start FTP

 

function ftp_file( $ftpservername, $ftpusername, $ftppassword, $ftpsourcefile, $ftpdirectory, $ftpdestinationfile ) {

// set up basic connection

$conn_id = ftp_connect($ftpservername);

if ( $conn_id == false ) {

echo "FTP open connection failed to $ftpservername <BR>\n" ;

return false;

}

 

// login with username and password

$login_result = ftp_login($conn_id, $ftpusername, $ftppassword);

 

// check connection

if ((!$conn_id) || (!$login_result)) {

echo "FTP connection has failed!<BR>\n";

echo "Attempted to connect to " . $ftpservername . " for user " . $ftpusername . "<BR>\n";

return false;

} else {

echo "Connected to " . $ftpservername . ", for user " . $ftpusername . "<BR>\n";

}

 

if ( strlen( $ftpdirectory ) > 0 ) {

if (ftp_chdir($conn_id, $ftpdirectory )) {

echo "Current directory is now: " . ftp_pwd($conn_id) . "<BR>\n";

} else {

echo "Couldn't change directory on $ftpservername<BR>\n";

return false;

}

}

 

ftp_pasv ( $conn_id, true ) ;

// upload the file

$upload = ftp_put( $conn_id, $ftpdestinationfile, $ftpsourcefile, FTP_ASCII );

 

// check upload status

if (!$upload) {

echo "$ftpservername: FTP upload has failed!<BR>\n";

return false;

} else {

echo "Uploaded " . $ftpsourcefile . " to " . $ftpservername . " as " . $ftpdestinationfile . "<BR>\n";

}

 

// close the FTP stream

ftp_close($conn_id);

 

return true;

}

 

if (FTP_ENABLED)

ftp_file( "uploads.google.com", FTP_USERNAME, FTP_PASSWORD, $source_file, "", $destination_file);

 

//End FTP

 

 

// End TIMER

// ---------

$etimer = explode( ' ', microtime() );

$etimer = $etimer[1] + $etimer[0];

 

echo '<p style="margin:auto; text-align:center">';

printf( "Script timer: <b>%f</b> seconds.", ($etimer-$stimer) );

echo '</p>';

 

 

 

 

// ---------

 

?>

 

 

Thx in advance.

 

Grtz Mario

Share this post


Link to post
Share on other sites

Hi Jack,

 

Some extra info.

 

I get this result.

 

Bronfeed: googlefeed.txt Product Search: blank.gif Afgekeurd of ongeldig Dit artikel voldoet niet aan ons beleid. Lees ons Programmabeleid voor meer informatie. Product Ads: blank.gif Niet geconfigureerd Search API for Shopping: blank.gif Afgekeurd of ongeldig Dit artikel voldoet niet aan ons beleid. Lees ons Programmabeleid voor meer informatie.

Thx again.

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

×