SugarCRM — Prevent currencies getting overridden

Posted on Thu 16 April 2015 in Tech

SugarCRM has some neat features involving currency rates, but one of the more annoying ones is that Sugar will automatically update the base rate every time you save a record with a currency field attached. This can be fairly annoying default behaviour if you wish to maintain the correct record value at the time of sale.

The problem

Say for example you have a custom module Books and your systems base currency is USD. You sell a book for 10 Euros at 11 dollars(the currenct change rate). If say a few weeks later you wanted to change the status of the Book to say, note down that the invoice was received and the exchange rate in that time has changed drastically. When you hit save Sugar would re-calculate the Euro value again(and despite the item being sold for 10 Euros originally the exchange rate has now changed and Sugar shows say 12 Euros, which is completely inconsistent with what you've invoiced.

The solution

There is thankfully a way around this using by overriding Sugar's default behaviour using logic hooks. So taking our book example this is what custom/modules/Books/logic_hooks.php might look like.

<?php
  // Do not store anything in this file that is not part of the array or the hook version.  This file will
  // be automatically rebuilt in the future.
  $hook_version = 1;
  $hook_array = Array();
  // position, file, function
  $hook_array['before_save'] = Array();
  $hook_array['before_save'][] = Array(1, 'workflow', 'include/workflow/WorkFlowHandler.php','WorkFlowHandler', 'WorkFlowHandler');
  $hook_array['before_save'][] = Array(2, 'Custom Book Logic Hooks', 'custom/modules/Books/logic_hooks_class.php','books_logic_hooks', 'before_save_method');
  $hook_array['after_save'][] = Array(1, 'Custom Book Logic Hooks', 'custom/modules/Books/logic_hooks_class.php','books_logic_hooks', 'after_save_method');
?>

And the actual code to do the overriding would be in custom/modules/Books/logic_hooks_class.php

<?php
if (!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

class books_logic_hooks
{
  protected static $fetchedRow;

function before_save_method(&$bean, $event, $arguments)
  {
    if (!empty($bean->id)) {
      self::$fetchedRow[$bean->id] = $bean->fetched_row;
    }
  }

/**
  * Called as process_record logic hook on the Books module
  */

/**
  * [@param](http://twitter.com/param "Twitter profile for @param") $bean
  * [@param](http://twitter.com/param "Twitter profile for @param") $event
  * [@param](http://twitter.com/param "Twitter profile for @param") $arguments
  */
  function after_save_method(&$bean, $event, $arguments)
  {
    $custom_bean = new Books();
    $custom_bean->retrieve($bean->id);
    $resave_bean = false;

// Need to ignore base rate updates when the currency stays the same
    if (isset(self::$fetchedRow[$custom_bean->id]['currency_id']) && self::$fetchedRow[$custom_bean->id]['currency_id'] == $bean->fetched_row['currency_id']
    ) {
      if (isset(self::$fetchedRow[$custom_bean->id]['base_rate']) &&
      self::$fetchedRow[$custom_bean->id]['base_rate'] !=     $bean->fetched_row['base_rate']) {
        // Run a SQL update to reset the base price to the original, as it shouldn't be over-ridden unless it's new
        $sql = sprintf('UPDATE books_cstm SET base_rate="%s" WHERE id_c="%s";', self::$fetchedRow[$custom_bean->id]['base_rate'], $custom_bean->id);
        $bean->db->query($sql);
      }
    }
  }
}

Using the db over-ride hack should allow you to retain the same base rate on each record and Sugar will display the original 10 Euros despite the exchange rate changing.

Some words of caution

If you decide to override Sugar's default behaviour, you could run into some issues down the line with reporting. Basically the reports modules seems to ignore the base rate stored on each individual record and just uses whatever the system default is.

So you'll have totals in your report that will be inconsistent with the currency value that's stored on the record. One way around this is to only create reports using your base currency, another is to create a secondary field storing the correct amount of the currency in a decimal field rather than a currency field and creating reports based on that amount.