Jump to content



Photo
- - - - -

Auto Update Currencies


  • Please log in to reply
87 replies to this topic

#81   Jack_mcs

Jack_mcs
  • Members
  • 26,695 posts
  • Real Name:Jack York
  • Gender:Male
  • Location:Michigan

Posted 13 November 2014 - 14:58

The page shouldn't be blank when you run it. That indicates a problem of some kind. If your site generates an error_log file in the admin directory, look at it to see if it shows any errors for this problem. You might also want to go over the installation to be sure you didn't miss something. And try going into admin->Localization->Currencies and clicking on the update currencies button. If that fails to work, then something on your server is blocking the connection.



#82   Psytanium

Psytanium
  • Members
  • 197 posts
  • Real Name:Psytanium
  • Gender:Male

Posted 13 November 2014 - 16:33

the update currency button is working fine, currencies updated.

 

thats the code

<?
/*
  Auto Update Currencies v 1.0 by Jack_mcs - oscommerce-solution.com
  osCommerce, Open Source E-Commerce Solutions
  http://www.oscommerce.com

  Copyright (c) 2003 osCommerce

  Released under the GNU General Public License
*/
  ///////CONFIGURABLE VARIABLES///////////
  define('DEFAULT_CURRENCY', 'EUR');
  define('HOURS_BETWEEN_UPDATES' , '24'); //if more than one day, multiply days by hours so the setting for two days would be 2 * 24 = 48
  define('REPORT_NO_UPDATE', true); //if the currencies are not updated since they have already been updated, don't report it. Set to false to always receive this message
  
  define('NO_REPORT',    0);
  define('SCREEN',       1);
  define('EMAIL',        2);
  define('SCREEN_EMAIL', 3);
  
  $output_report = EMAIL;
  ///////////////END//////////////////////

  define('CURRENCY_SERVER_PRIMARY', 'xe');
  define('CURRENCY_SERVER_BACKUP', 'oanda');

  require_once('includes/configure.php');
  require_once(DIR_WS_FUNCTIONS . 'localization.php');

  function date_diff_new($str_start, $str_end)
  {
    $str_start = strtotime($str_start); // The start date becomes a timestamp
    $str_end = strtotime($str_end); // The end date becomes a timestamp

    $nseconds = $str_end - $str_start; // Number of seconds between the two dates
    $ndays = round($nseconds / 86400); // One day has 86400 seconds
    $nseconds = $nseconds % 86400; // The remainder from the operation
    $nhours = round($nseconds / 3600); // One hour has 3600 seconds
    $nseconds = $nseconds % 3600;
    $nminutes = round($nseconds / 60); // One minute has 60 seconds, duh!
    $nseconds = $nseconds % 60;

    return $nhours;
  }


  $link = mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD) or die("Unable to connect to database server!");
  mysql_select_db(DB_DATABASE);

  $report = '';
  $updated = array();
  $rate = '';
  $today = date("Y-m-d H:i:s"); // The exact time
  $currency_query = mysql_query("select currencies_id, code, title, last_updated from currencies" ) or die(mysql_error());

  while ($currency = mysql_fetch_array($currency_query, MYSQL_ASSOC))
  {
    if (($ndays = date_diff_new($currency['last_updated'], $today)) > HOURS_BETWEEN_UPDATES)
    {
      $quote_function = 'quote_' . CURRENCY_SERVER_PRIMARY . '_currency';
      $rate = $quote_function($currency['code']);

      if (empty($rate)) {
        $quote_function = 'quote_' . CURRENCY_SERVER_BACKUP . '_currency';
        $rate = $quote_function($currency['code']);
      }

      if (!empty($rate)) {
        $updated[] = 'The exchange rate for ' . $currency['title'] . ' <b>WAS</b> successfully updated.' . "\n";
        mysql_query("update currencies set value = '" . $rate . "', last_updated = now() where currencies_id = '" . (int)$currency['currencies_id'] . "'");
      }
      else
        $updated[] = 'The exchange rate for ' . $currency['title'] . ' <b>WAS NOT</b> successfully updated. It was last updated on ' . $currency['last_updated'] . "\n";
    }
    else if (REPORT_NO_UPDATE)
      $updated[] = $currency['title'] . ' was not updated since it has been updated in the last ' . HOURS_BETWEEN_UPDATES . ((HOURS_BETWEEN_UPDATES > 1) ? ' hours.' : ' hour.') . "\n";
  }

  if ($output_report > NO_REPORT) {
    $report = 'Currencies Update Report' . "\n\n";

    $configuration_query = mysql_query("select configuration_value as store_name from configuration where configuration_key = 'STORE_NAME' limit 1") or die(mysql_error());
    $configuration = mysql_fetch_array($configuration_query, MYSQL_ASSOC);
    $report .= 'Currencies for ' . $configuration['store_name'] . ' updated on ' . date("D M j G:i:s Y") . "\n\n";
 
    foreach ($updated as $changed)
      $report .= $changed;

    if ($output_report != EMAIL) {
      echo str_replace("\n", '<br>', $report);
    }

    if ($output_report != SCREEN) {
      $config_query = mysql_query("select configuration_value as email_address from configuration where configuration_key = 'STORE_OWNER_EMAIL_ADDRESS' limit 1") or die(mysql_error());
      $config = mysql_fetch_array($config_query, MYSQL_ASSOC);
      mail($config['email_address'], 'Currency Rates updated for ' . $configuration['store_name'], stripslashes($report), $configuration['store_name']);
    }
  }
  mysql_close($link);
?>

anything wrong ?



#83   Jack_mcs

Jack_mcs
  • Members
  • 26,695 posts
  • Real Name:Jack York
  • Gender:Male
  • Location:Michigan

Posted 13 November 2014 - 17:29

If you are using around 2.3.3 (maybe before), that won't work. The database calls are different for those versions. I'll update this as soon as I can.



#84   Psytanium

Psytanium
  • Members
  • 197 posts
  • Real Name:Psytanium
  • Gender:Male

Posted 15 November 2014 - 12:10

What versions are compatible ?

is there something i can do ?



#85   Jack_mcs

Jack_mcs
  • Members
  • 26,695 posts
  • Real Name:Jack York
  • Gender:Male
  • Location:Michigan

Posted 15 November 2014 - 18:00

It will work with any of the versions that don't use mysqli, which start around 2.3.3, I think. You can try replacing all instance of mysql in that file with mysqli. I haven't looked close enough at it to say if that will work or not but those calls are the problem. The correct fix, and what should have been done initially, is to include application_top and use the tep functions. That's the change I will make once I get to it.



#86   Bob Terveuren

Bob Terveuren
  • Members
  • 343 posts
  • Real Name:Bob Terveuren
  • Gender:Male

Posted 16 November 2014 - 13:37

Hi there Jack

 

@Psytanium

 

Here's the code I use on a 2.3.4 site - has the mysqli stuff in there

<?php
/*
  Auto Update Currencies v 1.0 by Jack_mcs - oscommerce-solution.com
  osCommerce, Open Source E-Commerce Solutions
  http://www.oscommerce.com

  Copyright (c) 2003 osCommerce

  Released under the GNU General Public License
*/
  ///////CONFIGURABLE VARIABLES///////////
  define('DEFAULT_CURRENCY', 'GBP');
  define('HOURS_BETWEEN_UPDATES' , '24'); //if more than one day, multiply days by hours so the setting for two days would be 2 * 24 = 48
  define('REPORT_NO_UPDATE', true); //if the currencies are not updated since they have already been updated, don't report it. Set to false to always receive this message
  
  define('NO_REPORT',    0);
  define('SCREEN',       1);
  define('EMAIL',        2);
  define('SCREEN_EMAIL', 3);
  
  $output_report = EMAIL;
  ///////////////END//////////////////////

  define('CURRENCY_SERVER_PRIMARY', 'xe');
  define('CURRENCY_SERVER_BACKUP', 'oanda');

  require_once('includes/configure.php');
  require_once(DIR_WS_FUNCTIONS . 'localization.php');




  $link = mysqli_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD,DB_DATABASE) or die("Unable to connect to database server!");
  //mysql_select_db(DB_DATABASE);

  $report = '';
  $updated = array();
  $rate = '';
  $today = date("Y-m-d H:i:s"); // The exact time
  $currency_query = mysqli_query($link,"select currencies_id, code, title, last_updated from currencies" ) or die(mysql_error());

  while ($currency = mysqli_fetch_array($currency_query, MYSQL_ASSOC))
  {
    
    {
      $quote_function = 'quote_' . CURRENCY_SERVER_PRIMARY . '_currency';
      $rate = $quote_function($currency['code']);

      if (empty($rate)) {
        $quote_function = 'quote_' . CURRENCY_SERVER_BACKUP . '_currency';
        $rate = $quote_function($currency['code']);
      }

      if (!empty($rate)) {
        $updated[] = 'The exchange rate for ' . $currency['title'] . ' <b>WAS</b> successfully updated.' . "\n";
		//if ($rate !=1){$rate=$rate*1.02;}
        mysqli_query($link,"update currencies set value = '" . $rate . "', last_updated = now() where currencies_id = '" . (int)$currency['currencies_id'] . "'");
      }
      else
        $updated[] = 'The exchange rate for ' . $currency['title'] . ' <b>WAS NOT</b> successfully updated. It was last updated on ' . $currency['last_updated'] . "\n";
    }

  }

  if ($output_report > NO_REPORT) {
    $report = 'Currencies Update Report' . "\n\n";

    $configuration_query = mysqli_query($link,"select configuration_value as store_name from configuration where configuration_key = 'STORE_NAME' limit 1") or die(mysqli_error());
    $configuration = mysqli_fetch_array($configuration_query, MYSQL_ASSOC);
    $report .= 'Currencies for ' . $configuration['store_name'] . ' updated on ' . date("D M j G:i:s Y") . "\n\n";
 
    foreach ($updated as $changed)
      $report .= $changed;

     {
      echo str_replace("\n", '<br>', $report);
    }

    
  }
  mysqli_close($link);
?>

n.b I have an extra quick and dirty line in there (if ($rate !=1){$rate=$rate*1.02;}) that adds 2% markup on the exchange rate as my payment processor adds that in as their premium over and above the bank rates.

 

edit: commented out that line but left it in there FYI


Edited by Bob Terveuren, 16 November 2014 - 13:38.


#87   Jack_mcs

Jack_mcs
  • Members
  • 26,695 posts
  • Real Name:Jack York
  • Gender:Male
  • Location:Michigan

Posted 16 November 2014 - 14:41

Thanks for posting that. I'm sure it will help others.



#88   Psytanium

Psytanium
  • Members
  • 197 posts
  • Real Name:Psytanium
  • Gender:Male

Posted 17 November 2014 - 08:16

@Bob Terveuren thx, i really appreciate your help. also i needed the 2% markup. :thumbsup: