Jump to content
gottaloveit

Automatically send data feed to Froogle

Recommended Posts

@mr_absinthe I assumed you meant when you were running the script in admin. Is that the case or are you talking about the cron job? The method mentioned by Bob may work but I haven't seen that required for this addon. If the failure is happening when the script is ran in admin, then it would seem like there is a problem with your configure file since it is just including things like any file in admin is. But if it dialing via the cron job, you may need to alter the cron job to work from the admin directory. Something like

cd /home/USER NAME/public_html/ADMIN Name/; /usr/local/bin/php -c /home/USER NAME/public_html/php.ini -q /home/USER NAME/public_html/ADMIN NAME/googlefeeder.php

If you don't use a php.ini file, you can leave that part out and the path may be different on your server.

Share this post


Link to post
Share on other sites

The script runs fine when running in admin, it is failing when I use cron job. I've tested three different cron jobs:

php5 -q /home/user/public_html/ADMIN/googlefeeder.php

Email received with errors previously mentioned, no file created

cd /home/USER/public_html/ADMIN/; /usr/local/bin/php -q /home/USER/public_html/ADMIN/googlefeeder.php

No email, no file

cd /home/USER/public_html/ADMIN/; /usr/local/bin/php -c /home/USER/public_html/php.ini -q /home/USER/public_html/ADMIN/googlefeeder.php

No email, no file. I use identical cron job for your sitemonitor and it works just fine.

 

All of the above with the following code in the googlefeeder.php file:

include_once 'includes/application_top.php';
    error_reporting(E_ALL);  
    ini_set('display_errors','1'); 
    chdir('../');

and

$myfetch_mysql = mysqli_fetch_object;
Edited by mr_absinthe

Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

@mr_absinthe I'm sorry but I don't have any other suggestions other than to try what Bob suggested, if you have not already. Otherwise, it is something that would need troubleshooting.

Share this post


Link to post
Share on other sites

@@Dan Cole I didn't start the thread. Please feel free to ask for it to be moved if you like.

Edited by Jack_mcs

Share this post


Link to post
Share on other sites

@@Jan Zonjee This thread should probably be in the Add-ons section.

 

I didn't start the thread. Please feel free to ask for it to be moved if you like.

 

Dan

Share this post


Link to post
Share on other sites

Including this

set_include_path('/home/USER/public_html/ADMIN/includes/');

at the beginning of the file makes no difference... :(  I have about 20 different cron jobs running fine, this is the only one failing

Edited by mr_absinthe

Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

Ah...you try this with your cron? There are two exe commands here...

 

cd /home/user/public_html/admin; /usr/bin/php -q /home/user/public_html/admin/googlefeeder.php

 

 

However I still might have a problem with a cron. After running it, I receive a message full of warnings:

PHP Warning:  require(includes/configure.php): failed to open stream: No such file or directory in /home/user/public_html/admin/includes/application_top.php on line 43
PHP Warning:  require(includes/configure.php): failed to open stream: No such file or directory in /home/user/public_html/admin/includes/application_top.php on line 43
PHP Fatal error:  require(): Failed opening required 'includes/configure.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/user/public_html/admin/includes/application_top.php on line 43

Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/user/public_html/admin/includes/application_top.php on line 43

Warning: require(includes/configure.php): failed to open stream: No such file or directory in /home/user/public_html/admin/includes/application_top.php on line 43

Fatal error: require(): Failed opening required 'includes/configure.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/user/public_html/admin/includes/application_top.php on line 43

The cron is php5 -q /home/user/public_html/admin/googlefeeder.php

Share this post


Link to post
Share on other sites

Hi chaps

 

Cron is a total PITA from server to server - what works on one fails on another (every dang time)

 

If you are not troubled by the feeder file getting relocated to the /catalog/ folder then you could try running it under cron using wget (so long as wget id in the PHP install) - I've resorted to that a few times when a Cron syntax reduces me to tears. It does mean the cron file is exposed to the web in the /catalog/ folder but you could install some basic security check if you were troubled

 

0 * * * * wget -O /dev/null http://www.example.com/googlefeeder.php?user=MyUserName&pass=MyPassWord

Share this post


Link to post
Share on other sites

Thank you very much for your replies, I have tested both examples... these are the results and they do not work I'm afraid:

cd /home/user/public_html/admin; /usr/bin/php -q /home/user/public_html/admin/googlefeeder.php

creates no file, no error message, nothing

 

and this one:

 wget -O /dev/null http://www.example.com/googlefeeder.php?user=MyUserName&pass=MyPassWord

appears that nothing is created ( no file found in /feeds folder or root) and also nothing is uploaded top google base, however this message was received:

--2015-06-07 06:05:01--  http://www.mysite.com/googlefeeder_uk.php?user=username
Resolving www.mysite.com... 64.xx.xxx.30
Connecting to www.mysite.com|64.xx.xxx.30|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: “/dev/null”

     0K .                                                       171M=0s

2015-06-07 06:05:02 (171 MB/s) - “/dev/null” saved [1446]


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

@mr_absinthe, did you use the commands verbatim? That was just an example. You need to make sure that you have the correct path to your admin folder and to the googlefeeder.php file. Also, you seem to be using php5 and it was working. So do a

 

whereis php5

 

to find the path to the php5 file and use that full path to run your googlefeeder.php. You said it was working when you exec the command inside the admin folder. That make sense as in your application_top.php it uses relative path. This happened to me before and that's how it was solved. Well, I hope this help...

 

 

 

Thank you very much for your replies, I have tested both examples... these are the results and they do not work I'm afraid:

cd /home/user/public_html/admin; /usr/bin/php -q /home/user/public_html/admin/googlefeeder.php

Share this post


Link to post
Share on other sites

I'm not familiar with commands verbatim I'm afraid. Not sure if this is what I need, however:

Configuration File (php.ini) Path: /usr/lib

Loaded Configuration File: /usr/selector.etc/php.ini

 

I have therefore tried:

cd /home/myusername/public_html/Admin; /usr/selector.etc/php -q /home/myusername/public_html/Admin/googlefeeder.php - received message "No such file or directory"

cd /home/myusername/public_html/Admin; /usr/lib/php -q /home/myusername/public_html/Admin/googlefeeder.php - message /usr/local/cpanel/bin/jailshell: /usr/lib/php: is a directory

cd /home/myusername/public_html/Admin; /usr/lib -q /home/myusername/public_html/Admin/googlefeeder.php - message: /usr/local/cpanel/bin/jailshell: /usr/lib: is a directory

I have also tried this, but that doesn't work either, no message, no file:

cd /home/myusername/public_html/Admin/; /usr/local/bin/php -c /home/myusername/public_html/php.ini -q /home/myusername/public_html/Admin/googlefeeder.php

Not sure if I'm doing it right...


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

@mr_absinthe. Ok, you are definitely lost...

 

If you can run the script in admin and it's ran fine. Then in your cron job you just have to make sure that you cd into the admin dir before exec the script. Now, the later OSC you can't run cron jobs using the admin folder as it requires a login, but you should be able to run it from the catalog folder...

 

Also, you more than likely can't interchange php and php5 as they could be different executable. You can find out by doing a php -v and a php5 -v and check their version. Basically, you need to use the same php version that worked. Do a "wehreis" on the php that worked and use full path in your cron job. Don't use just php in cron as I am sure that you are not sure of your environment. No surprises this way...good luck!

 

 

 

 

The script runs fine when running in admin, it is failing when I use cron job. I've tested three different cron jobs:

php5 -q /home/user/public_html/ADMIN/googlefeeder.php

Email received with errors previously mentioned, no file created

cd /home/USER/public_html/ADMIN/; /usr/local/bin/php -q /home/USER/public_html/ADMIN/googlefeeder.php

No email, no file

cd /home/USER/public_html/ADMIN/; /usr/local/bin/php -c /home/USER/public_html/php.ini -q /home/USER/public_html/ADMIN/googlefeeder.php

No email, no file. I use identical cron job for your sitemonitor and it works just fine.

 

All of the above with the following code in the googlefeeder.php file:

include_once 'includes/application_top.php';
    error_reporting(E_ALL);  
    ini_set('display_errors','1'); 
    chdir('../');

and

$myfetch_mysql = mysqli_fetch_object;

Share this post


Link to post
Share on other sites

@@clustersolutions yes, you're correct I'm completely lost now... and forever with this script I guess... This is now beyond my coding skills I'm afraid :(  I have several cron commands running fine, but this one is still failing and I have already tried almost every possible combination. I have even spoke to my host to confirm the path to the php5 file. Their reply was:

The correct path of php is "/usr/bin/php", so all the cronjobs you have set are using php5. You will need to update those crons "/usr/bin/php" instead of using php5. Refer below example for more details.

/usr/bin/php -q /home/myusername/public_html/Admin/googlefeeder.php

As I'm still using OsC 2.2, so I guess I can use cron from admin folder and I have several that are in admin folder running fine. If I use the above example command:

/usr/bin/php -q /home/user/public_html/admin/googlefeeder.php
or
cd /home/user/public_html/admin; /usr/bin/php -q /home/user/public_html/admin/googlefeeder.php
changing /usr/bin/php to /usr/local/bin/php makes no difference, result is the same - nothing created, no message

it does nothing, no message, no file created. I don't quite understand why this is failing if these similar commands I have are running just fine:

/usr/local/bin/php /home/user/public_html/myfolder/admin/index.php -p processqueue
or this one
cd /home/user/public_html/Admin/; /usr/local/bin/php -c /home/user/public_html/php.ini -q /home/user/public_html/Admin/sitemonitor.php

I have no idea what I'm doing wrong... It looks like I'll have to run this manually from admin forever trying to remember to do it at least once a month... :x


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

@mr_absinthe..

 

ok...so you running crontab via command line or GUI?

 

If you can ssh in...do this right after you have login...pls make sure that the syntax is correct as I don't know your username and etc...

 

cd /home/myusername/public_html/Admin; /usr/bin/php -googlefeeder.php

 

File created, and FTP works? I believe you said this command works...

Share this post


Link to post
Share on other sites

@@Bob Terveuren thank you, it will be most likely my only option, if it is going to work obviously

 

@@clustersolutions to set up and run a cron job, I'm using cPanel GUI, tested your command above (replaced myusername obviously) and it does nothing, no file, no message, no ftp, nothing

 

I have to say, I have never experienced so much hassle with a cron job and I have about 20 of them running just fine. Why is this script failing...? No idea. Thank you for trying to help, much appreciated.


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

Had this add-on working 100% for years. Past few months had 0 records loading into google shopping so I have just installed googlefeeder 3.4. It is loading and working but unfortunately with no records again loading into google shopping. Just checked merchant center and am now getting some more details re. my errors.

Shopping: 0 / 1,056 successful

Missing tax information: 1056 errors

Missing shipping information: 1056 errors

Item warnings: 1056 errors Missing recommended attribute: google product category: 1056 warnings

Insufficient product identifiers: Missing 2 out of 3 attributes (gtin, brand, mpn) 1056 errors

 

I presume the "fixes" lie with googlefeeder.php - current code

<?php
//  Title: Google Base Data Feeder
//  Version: 3.3 by Jack York (aka Jack_mcs) - www.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';
$use_mysqli = false; 
if (function_exists('tep_get_version')) { 
   $ver = tep_get_version();
   if (isset($ver[4]) && $ver[4] > 1) { //only versions after 2.3.1 use mysqli
      $use_mysqli = true;
   }
} 
$myfetch_mysql = ($use_mysqli ? mysqli_fetch_object : mysql_fetch_object);
 
/*************** BEGIN MASTER SETTINGS ******************/

define('SEO_ENABLED','true');    //Change to 'false' to disable if Ultimate SEO URLs is not installed
define('FEEDNAME', 'nontando.txt');       //from your googlebase account
define('DOMAIN_NAME', 'www.nontando.com'); //your correct domain name (don't include www unless it is used but do include the shops directory)
define('FTP_USERNAME', 'nontando'); //created from within your googlebase account
define('FTP_PASSWORD', 'b8WEBWw9nbinbACvf'); //created from within your googlebase account
define('CONVERT_CURRENCY', '0'); //set to 0 to disable - only needed if a feed in a difference currecny is required
define('CURRENCY_TYPE', 'USD');  //(eg. USD, EUR, GBP)
define('DEFAULT_LANGUAGE_ID', $languages_id);   //Change this to the id of your language if different than what is set as the default language in admin
define('QUOTES_CATEGORY_NAME',''); //if the Quotes contribution is installed, enter the name of the quotes category here
define('SKIP_CATEGORY_ID', ''); //don't list any categories (or their products) listed here - separate each id by a comma
define('SKIP_PRODUCT_ID', ''); //don't list any products listed here - separate each id by a comma

/*************** 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', 0); //http://www.google.com/support/merchants/bin/answer.py?answer=160081
define('OPTIONS_ENABLED_GOOGLE_UTM', 0);
define('OPTIONS_ENABLED_IDENTIFIER_EXISTS', 1); //set to 0 if required - https://support.google.com/merchants/answer/188494?hl=en
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', 0);       //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', 'quantity');     //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', 'name');                //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', 'en');
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', '');  //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', 'model');                       //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 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' , '20.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 = 0 (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', 'US:FL:5.00:n'); //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', 'lb'); //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];
//  -----------

define('FTP_ENABLED', (isset($_GET['noftp']) ? '0' : '1'));   //DO NOT CHANGE THIS LINE
$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 = @[member=date](OPTIONS_DATE_FORMAT, time() + 2419200 );
 
$quotes = '';
if (QUOTES_CATEGORY_NAME !== '') {
   $quotes = " and products.customers_email_address = '' and products.quotes_email_address = ''";
}

$identfierCtr = 0; //check if the identifier exists field applies
$extraFields = '';
if (OPTIONS_AVAILABILITY == '') {
   $extraFields .= ' products_description.products_availability as availability, ';
}
if (OPTIONS_ENABLED_BRAND == 1) {
   $identfierCtr++;
   
   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) {
   $identfierCtr++;
   $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 == 1) {
   $identfierCtr++;

   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, ';
}

if (tep_not_null(SKIP_CATEGORY_ID)) {
   $ids = explode(',', SKIP_CATEGORY_ID);
   for ($i = 0; $i < count($ids); ++$i) {
       $cStr .=  $ids[$i] . ",";       
   }
   $cStr = substr($cStr, 0, -1);
   $skipCatIDS = " and categories.parent_id NOT IN (" . $cStr . ") ";
}

if (tep_not_null(SKIP_PRODUCT_ID)) {
   $ids = explode(',', SKIP_PRODUCT_ID);
   $skipIDS = ' and ( ';
   for ($i = 0; $i < count($ids); ++$i) {
       $skipIDS .= ' products.products_id != ' . $ids[$i] . ' and ';
   }
   $skipIDS = substr($skipIDS, 0, -4) . ' ) ';
}

$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 . $skipCatIDS . $skipIDS . "
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 = tep_db_query( $catInfo )or die( $FunctionName . ": SQL error " . tep_db_error() . "| catInfo = " . htmlentities($catInfo) );

while ( $catRow = $myfetch_mysql( $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 == 1)   	$output .= "\tlanguage";
   if(OPTIONS_ENABLED_GOOGLE_PRODUCT_CATEGORY == 1) $output .= "\tgoogle product category";
   if(OPTIONS_ENABLED_GTIN == 1)            	$output .= "\tgtin";
   if(OPTIONS_ENABLED_IDENTIFIER_EXISTS == 1 && $identfierCtr > 1)  $output .= "\tidentifier exists";
   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 .= "\tshipping_weight";

   if (OPTIONS_ENABLED_ATTRIBUTES == 1)  {
       $products_options_name_query = tep_db_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(tep_db_error());
       while ($products_options_name = $myfetch_mysql($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=tep_db_query( $sql )or die( $FunctionName . ": SQL error " . tep_db_error() . "| sql = " . htmlentities($sql) );

//Currency Information
if(CONVERT_CURRENCY) {
   $sql3 = "
   SELECT
   currencies.value AS curUSD
   FROM
   currencies
   WHERE currencies.code = '" . CURRENCY_TYPE . "'";

   $result3=tep_db_query( $sql3 )or die( $FunctionName . ": SQL error " . tep_db_error() . "| sql3 = " . htmlentities($sql3) );
   $row3 = $myfetch_mysql( $result3 );
}

$loop_counter = 0;
$statsArray = array();      //record messages
$statsArrayPrice = array(); //record prices of 0
$statsArrayQty = array();   //record quantities of 0
$showPriceZero = false;
$showQtyZero = false;


while( $row = $myfetch_mysql( $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 && $row->prodStatus == 1) {$statsArray['price']++; $showPriceZero = true; }//record for warning
   if (OPTIONS_IGNORE_PRODUCT_ZERO < 1 && $row->quantity < 1 && $row->prodStatus == 1) {$statsArray['qty']++; $showQtyZero = true; } //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 : '');
      $pURL = $row->product_url;

      if(SEO_ENABLED=='true'){
          $output .= tep_href_link($productURL,$productParam . $row->id, 'NONSSL', false) . $google_utm . "\t";
          $pURL = tep_href_link($productURL,$productParam . $row->id, 'NONSSL', false);
      } else {
          $output .= $row->product_url . $google_utm . "\t";
      }

      if ($showPriceZero) {
          $showPriceZero = false;
          $statsArrayPrice[] = $pURL;
      }
      if ($showQtyZero) {
          $showQtyZero = false;
          $statsArrayQty[] = $pURL;
      }

      $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_IDENTIFIER_EXISTS == 1 && $identfierCtr > 1) {
            $icnt = 0;
            if (OPTIONS_ENABLED_BRAND && empty($row->brand)) $icnt++;
            if (OPTIONS_ENABLED_GTIN && empty($row->gtin)) $icnt++;   
            if (OPTIONS_ENABLED_MPN && empty($row->mpn)) $icnt++;  
            if ($icnt > 1) { //at least two required fields are empty
              $output .= "\tFALSE";
            } else {
              $output .= "\tTRUE";
            }
         }   
            
         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 = tep_db_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 = $myfetch_mysql($products_attributes_query);
            if ($products_attributes->total > 0) {
              $products_options_name_query = tep_db_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(tep_db_error());

              $trackTabs = '';

              while ($products_options_name = $myfetch_mysql($products_options_name_query)) {
                $products_options_array = array();
                $products_options_query = tep_db_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 = $myfetch_mysql($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;  padding:10px; 0px">';
  printf( "***Warning:*** There are %d products with a price of $0.<br>", $statsArray['price'] );

  for ($i = 0; $i < count($statsArrayPrice); ++$i) {
      echo  '   ' . $i . ' - ' . $statsArrayPrice[$i] .'<br>';
  }

  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.<br>", $statsArray['qty'] );

  for ($i = 0; $i < count($statsArrayQty); ++$i) {
      echo  '   ' . $i . ' - ' . $statsArrayQty[$i] .'<br>';
  }

  echo '</p>';
}

if (tep_not_null(SKIP_PRODUCT_ID)) {
  echo '<p style="margin:auto; text-align:left; padding-bottom:10px; ">';
  printf( "The following product ID's were skipped: %s.", SKIP_PRODUCT_ID );
  echo '</p>';
}

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

echo '<p style="margin:auto; text-align:left">';
echo "$completed <a href=\"../" . $OutFile . "\" target=\"_blank\">" . $destination_file . "</a><br>\n\n";
echo '</p>';


$csvFileDest = str_replace('.txt', '.csv', $destination_file);
$csvFileLocn = str_replace('.txt', '.csv', $OutFile);
$csvStr = str_replace("\t", '", "', '"' . $output);
$csvStr = str_replace("\n", "\"\n\"", $csvStr);
$csvStr = substr($csvStr,0,-1);
$csvStr = str_replace("\t", '", "', '"' . $output . '"');

$fp = fopen( $csvFileLocn , "a" );
$fout = fwrite( $fp , $csvStr );
fclose( $fp );

echo '<p style="margin:auto; text-align:left; padding-top:10px;">';
echo 'Use the following for easier viewing from this page. It is still in development and not meant for anything other than viewing.' . "<br>\n\n";
echo "$completed <a href=\"../" . $csvFileLocn . "\" target=\"_blank\">" . $csvFileDest . "</a><br>\n";
echo '</p>';


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




//  ---------

?>

Any assistance will be much appreciated

Share this post


Link to post
Share on other sites

@@bksbeat Most, if not all, of the errors have to do with how the options are set. For example, one of the errors is that you don't have a google product category set and if you look at the settings, you will see that is turned off.

 

Please don't common code like that. It makes following the thread very difficult and doesn't serve any purpose.

Share this post


Link to post
Share on other sites

Thanks Jack. Will play with the option settings and try and remove those errors. 

Share this post


Link to post
Share on other sites

Please try this change. Find

   $skipCatIDS = " and categories.parent_id NOT IN (" . $cStr . ") ";

and change it to

   $skipCatIDS = " and ( categories.parent_id NOT IN (" . $cStr . ") && categories.categories_id NOT IN (" . $cStr . ")) ";

Share this post


Link to post
Share on other sites

@@bksbeat Most, if not all, of the errors have to do with how the options are set. For example, one of the errors is that you don't have a google product category set and if you look at the settings, you will see that is turned off.

 

Please don't common code like that. It makes following the thread very difficult and doesn't serve any purpose.

 

Hi Jack,

 

can you point me to the latest file/instructions for this?

i've never setup this type of thing before on live site,

 

im using the BS edge version.

thanks

Share this post


Link to post
Share on other sites

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

×