multicurrency.class.php

Go to the documentation of this file.

<?php
/* Copyright (C) 2007-2020  Laurent Destailleur <eldy@users.sourceforge.net>
 * Copyright (C) 2014       Juanjo Menent       <jmenent@2byte.es>
 * Copyright (C) 2015       Florian Henry       <florian.henry@open-concept.pro>
 * Copyright (C) 2015       Raphaël Doursenaud  <rdoursenaud@gpcsolutions.fr>
 * Copyright (C) 2016       Pierre-Henry Favre  <phf@atm-consulting.fr>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 */
 
// Put here all includes required by your class file
require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php';
require_once DOL_DOCUMENT_ROOT.'/core/class/commonobjectline.class.php';
 
 
class MultiCurrency extends CommonObject
{
  public $element = 'multicurrency';
 
  public $table_element = 'multicurrency';
 
  public $table_element_line = "multicurrency_rate";
 
  public $rates = array();
 
  public $id;
 
  public $code;
 
  public $name;
 
  public $entity;
 
  public $date_create;
 
  public $fk_user;
 
  public $rate;
 
 
  public function __construct(DoliDB $db)
  {
    $this->db = $db;
 
    return 1;
  }
 
  public function create(User $user, $trigger = true)
  {
    global $conf, $langs;
 
    dol_syslog('MultiCurrency::create', LOG_DEBUG);
 
    $error = 0;
 
    if (self::checkCodeAlreadyExists($this->code)) {
      $error++;
      $this->errors[] = $langs->trans('multicurrency_code_already_added');
      return -1;
    }
 
    if (empty($this->entity) || $this->entity <= 0) {
      $this->entity = $conf->entity;
    }
    $now = dol_now();
 
    // Insert request
    $sql = "INSERT INTO ".MAIN_DB_PREFIX.$this->table_element."(";
    $sql .= ' code,';
    $sql .= ' name,';
    $sql .= ' entity,';
    $sql .= ' date_create,';
    $sql .= ' fk_user';
    $sql .= ') VALUES (';
    $sql .= " '".$this->db->escape($this->code)."',";
    $sql .= " '".$this->db->escape($this->name)."',";
    $sql .= " ".((int) $this->entity).",";
    $sql .= " '".$this->db->idate($now)."',";
    $sql .= " ".((int) $user->id);
    $sql .= ')';
 
    $this->db->begin();
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $this->db->query($sql);
    if (!$resql) {
      $error++;
      $this->errors[] = 'Error '.$this->db->lasterror();
      dol_syslog('MultiCurrency::create '.join(',', $this->errors), LOG_ERR);
    }
 
    if (!$error) {
      $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX.$this->table_element);
      $this->date_create = $now;
      $this->fk_user = $user->id;
 
      if ($trigger) {
        $result = $this->call_trigger('CURRENCY_CREATE', $user);
        if ($result < 0) {
          $error++;
        }
      }
    }
 
    if ($error) {
      $this->db->rollback();
 
      return -1 * $error;
    } else {
      $this->db->commit();
 
      return $this->id;
    }
  }
 
  public function fetch($id, $code = null)
  {
    dol_syslog('MultiCurrency::fetch', LOG_DEBUG);
 
    global $conf;
 
    $sql = "SELECT";
    $sql .= ' c.rowid, c.name, c.code, c.entity, c.date_create, c.fk_user';
    $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' AS c';
    if (!empty($code)) {
      $sql .= ' WHERE c.code = \''.$this->db->escape($code).'\' AND c.entity = '.$conf->entity;
    } else {
      $sql .= ' WHERE c.rowid = '.((int) $id);
    }
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $this->db->query($sql);
 
    if ($resql) {
      $numrows = $this->db->num_rows($resql);
      if ($numrows) {
        $obj = $this->db->fetch_object($resql);
 
        $this->id = $obj->rowid;
        $this->name = $obj->name;
        $this->code = $obj->code;
        $this->entity = $obj->entity;
        $this->date_create = $obj->date_create;
        $this->fk_user = $obj->fk_user;
 
        $this->fetchAllCurrencyRate();
        $this->getRate();
      }
      $this->db->free($resql);
 
      if ($numrows) {
        return 1;
      } else {
        return 0;
      }
    } else {
      $this->errors[] = 'Error '.$this->db->lasterror();
      dol_syslog('MultiCurrency::fetch '.join(',', $this->errors), LOG_ERR);
 
      return -1;
    }
  }
 
  public function fetchAllCurrencyRate()
  {
    $sql = "SELECT cr.rowid";
    $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element_line.' as cr';
    $sql .= ' WHERE cr.fk_multicurrency = '.((int) $this->id);
    $sql .= ' ORDER BY cr.date_sync DESC';
 
    $this->rates = array();
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $this->db->query($sql);
    if ($resql) {
      $num = $this->db->num_rows($resql);
 
      while ($obj = $this->db->fetch_object($resql)) {
        $rate = new CurrencyRate($this->db);
        $rate->fetch($obj->rowid);
 
        $this->rates[] = $rate;
      }
      $this->db->free($resql);
 
      return $num;
    } else {
      $this->errors[] = 'Error '.$this->db->lasterror();
      dol_syslog('MultiCurrency::fetchAllCurrencyRate '.join(',', $this->errors), LOG_ERR);
 
      return -1;
    }
  }
 
  public function update(User $user, $trigger = true)
  {
    $error = 0;
 
    dol_syslog('MultiCurrency::update', LOG_DEBUG);
 
    // Clean parameters
    $this->name = trim($this->name);
    $this->code = trim($this->code);
 
    // Check parameters
    if (empty($this->code)) {
      $error++;
      dol_syslog('MultiCurrency::update $this->code can not be empty', LOG_ERR);
 
      return -1;
    }
 
    // Update request
    $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element." SET";
    $sql .= " name = '".$this->db->escape($this->name)."',";
    $sql .= " code = '".$this->db->escape($this->code)."'";
    $sql .= " WHERE rowid = ".((int) $this->id);
 
    $this->db->begin();
 
    $resql = $this->db->query($sql);
    if (!$resql) {
      $error++;
      $this->errors[] = 'Error '.$this->db->lasterror();
      dol_syslog('MultiCurrency::update '.join(',', $this->errors), LOG_ERR);
    }
 
    if (!$error && $trigger) {
      $result = $this->call_trigger('CURRENCY_MODIFY', $user);
      if ($result < 0) {
        $error++;
      }
    }
 
    // Commit or rollback
    if ($error) {
      $this->db->rollback();
 
      return -1 * $error;
    } else {
      $this->db->commit();
 
      return 1;
    }
  }
 
  public function delete($trigger = true)
  {
    global $user;
 
    dol_syslog('MultiCurrency::delete', LOG_DEBUG);
 
    $error = 0;
 
    $this->db->begin();
 
    if ($trigger) {
      $result = $this->call_trigger('CURRENCY_DELETE', $user);
      if ($result < 0) {
        $error++;
      }
    }
 
    if (!$error) {
      // Delete all rates before
      if (!$this->deleteRates()) {
        $error++;
        $this->errors[] = 'Error '.$this->db->lasterror();
        dol_syslog('Currency::delete  '.join(',', $this->errors), LOG_ERR);
      }
 
      $sql = "DELETE FROM ".MAIN_DB_PREFIX.$this->table_element;
      $sql .= " WHERE rowid = ".((int) $this->id);
 
      dol_syslog(__METHOD__, LOG_DEBUG);
      $resql = $this->db->query($sql);
      if (!$resql) {
        $error++;
        $this->errors[] = 'Error '.$this->db->lasterror();
        dol_syslog('MultiCurrency::delete '.join(',', $this->errors), LOG_ERR);
      }
    }
 
    // Commit or rollback
    if ($error) {
      $this->db->rollback();
 
      return -1 * $error;
    } else {
      $this->db->commit();
 
      return 1;
    }
  }
 
  public function deleteRates()
  {
    foreach ($this->rates as &$rate) {
      if ($rate->delete() <= 0) {
        return false;
      }
    }
 
    return true;
  }
 
  public function addRate($rate)
  {
    $currencyRate = new CurrencyRate($this->db);
    $currencyRate->rate = price2num($rate);
 
    if ($currencyRate->create($this->id) > 0) {
      $this->rate = $currencyRate;
      return 1;
    } else {
      $this->rate = null;
      $this->errors = $currencyRate->errors;
      return -1;
    }
  }
 
  public function addRateFromDolibarr($code, $rate)
  {
    global $user;
 
    $currency = new MultiCurrency($this->db);
    $currency->code = $code;
    $currency->name = $code;
 
    $sql = "SELECT label FROM ".MAIN_DB_PREFIX."c_currencies WHERE code_iso = '".$this->db->escape($code)."'";
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $this->db->query($sql);
    if ($resql && ($line = $this->db->fetch_object($resql))) {
      $currency->name = $line->label;
    }
 
    if ($currency->create($user) > 0) {
      $currency->addRate($rate);
 
      if (!empty($line)) {
        return 2;
      } else {
        return 1;
      }
    }
 
    return -1;
  }
 
  public function updateRate($rate)
  {
    return $this->addRate($rate);
  }
 
  public function getRate()
  {
    $sql = "SELECT cr.rowid";
    $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element_line." as cr";
    $sql .= " WHERE cr.fk_multicurrency = ".((int) $this->id);
    $sql .= " AND cr.date_sync = (SELECT MAX(cr2.date_sync) FROM ".MAIN_DB_PREFIX.$this->table_element_line." AS cr2 WHERE cr2.fk_multicurrency = ".((int) $this->id).")";
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $this->db->query($sql);
    if ($resql && ($obj = $this->db->fetch_object($resql))) {
      $this->rate = new CurrencyRate($this->db);
      return $this->rate->fetch($obj->rowid);
    }
 
    return -1;
  }
 
  public static function getIdFromCode($dbs, $code)
  {
    global $conf;
 
    $sql = "SELECT rowid FROM ".MAIN_DB_PREFIX."multicurrency WHERE code = '".$dbs->escape($code)."' AND entity = ".((int) $conf->entity);
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $dbs->query($sql);
    if ($resql && $obj = $dbs->fetch_object($resql)) {
      return $obj->rowid;
    } else {
      return 0;
    }
  }
 
  public static function getIdAndTxFromCode($dbs, $code, $date_document = '')
  {
    global $conf;
 
    $sql1 = "SELECT m.rowid, mc.rate FROM ".MAIN_DB_PREFIX."multicurrency m";
 
    $sql1 .= ' LEFT JOIN '.MAIN_DB_PREFIX.'multicurrency_rate mc ON (m.rowid = mc.fk_multicurrency)';
    $sql1 .= " WHERE m.code = '".$dbs->escape($code)."'";
    $sql1 .= " AND m.entity IN (".getEntity('multicurrency').")";
    $sql2 = '';
    if (!empty($conf->global->MULTICURRENCY_USE_RATE_ON_DOCUMENT_DATE) && !empty($date_document)) { // Use last known rate compared to document date
      $tmparray = dol_getdate($date_document);
      $sql2 .= " AND mc.date_sync <= '".$dbs->idate(dol_mktime(23, 59, 59, $tmparray['mon'], $tmparray['mday'], $tmparray['year'], true))."'";
    }
    $sql3 = " ORDER BY mc.date_sync DESC LIMIT 1";
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $dbs->query($sql1.$sql2.$sql3);
 
    if ($resql && $obj = $dbs->fetch_object($resql)) {
      return array($obj->rowid, $obj->rate);
    } else {
      if (!empty($conf->global->MULTICURRENCY_USE_RATE_ON_DOCUMENT_DATE)) {
        $resql = $dbs->query($sql1.$sql3);
        if ($resql && $obj = $dbs->fetch_object($resql)) {
          return array($obj->rowid, $obj->rate);
        }
      }
 
      return array(0, 1);
    }
  }
 
  public static function getAmountConversionFromInvoiceRate($fk_facture, $amount, $way = 'dolibarr', $table = 'facture')
  {
    $multicurrency_tx = self::getInvoiceRate($fk_facture, $table);
 
    if ($multicurrency_tx) {
      if ($way == 'dolibarr') {
        return price2num($amount * $multicurrency_tx, 'MU');
      } else {
        return price2num($amount / $multicurrency_tx, 'MU');
      }
    } else {
      return false;
    }
  }
 
  public static function getInvoiceRate($fk_facture, $table = 'facture')
  {
    global $db;
 
    $sql = "SELECT multicurrency_tx FROM ".MAIN_DB_PREFIX.$table." WHERE rowid = ".((int) $fk_facture);
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $db->query($sql);
    if ($resql && ($line = $db->fetch_object($resql))) {
      return $line->multicurrency_tx;
    }
 
    return false;
  }
 
  public function recalculRates(&$TRate)
  {
    global $conf;
 
    if ($conf->currency != getDolGlobalString('MULTICURRENCY_APP_SOURCE')) {
      $alternate_source = 'USD'.$conf->currency;
      if (!empty($TRate->$alternate_source)) {
        $coef = $TRate->USDUSD / $TRate->$alternate_source;
        foreach ($TRate as $attr => &$rate) {
          $rate *= $coef;
        }
 
        return 1;
      }
 
      return -1; // Alternate souce not found
    }
 
    return 0; // Nothing to do
  }
 
  public function syncRates($key, $addifnotfound = 0)
  {
    global $conf, $db, $langs;
 
    include_once DOL_DOCUMENT_ROOT.'/core/lib/geturl.lib.php';
 
    $urlendpoint = 'http://api.currencylayer.com/live?access_key='.$key;
    $urlendpoint .= '&source=' . (empty($conf->global->MULTICURRENCY_APP_SOURCE) ? 'USD' : $conf->global->MULTICURRENCY_APP_SOURCE);
 
    dol_syslog("Call url endpoint ".$urlendpoint);
 
    $resget = getURLContent($urlendpoint);
 
    if ($resget['content']) {
      $response = $resget['content'];
      $response = json_decode($response);
 
      if ($response->success) {
        $TRate = $response->quotes;
        //$timestamp = $response->timestamp;
 
        if ($this->recalculRates($TRate) >= 0) {
          foreach ($TRate as $currency_code => $rate) {
            $code = substr($currency_code, 3, 3);
            $obj = new MultiCurrency($db);
            if ($obj->fetch(0, $code) > 0) {
              $obj->updateRate($rate);
            } elseif ($addifnotfound) {
              $this->addRateFromDolibarr($code, $rate);
            }
          }
        }
 
        return 1;
      } else {
        dol_syslog("Failed to call endpoint ".$response->error->info, LOG_WARNING);
        setEventMessages($langs->trans('multicurrency_syncronize_error', $response->error->info), null, 'errors');
 
        return -1;
      }
    } else {
      return -1;
    }
  }
 
  public function checkCodeAlreadyExists($code)
  {
    $currencytmp = new MultiCurrency($this->db);
    if ($currencytmp->fetch('', $code) > 0) {
      return true;
    } else {
      return false;
    }
  }
}
 
 
class CurrencyRate extends CommonObjectLine
{
  public $element = 'multicurrency_rate';
 
  public $table_element = 'multicurrency_rate';
 
  public $id;
 
  public $rate;
 
  public $date_sync;
 
  public $fk_multicurrency;
 
  public $entity;
 
 
  public function __construct(DoliDB $db)
  {
    $this->db = $db;
 
    return 1;
  }
 
  public function create($fk_multicurrency, $trigger = true)
  {
    global $conf, $user;
 
    dol_syslog('CurrencyRate::create', LOG_DEBUG);
 
    $error = 0;
    $this->rate = price2num($this->rate);
    if (empty($this->entity) || $this->entity <= 0) {
      $this->entity = $conf->entity;
    }
    $now = empty($this->date_sync) ? dol_now() : $this->date_sync;
 
    // Insert request
    $sql = "INSERT INTO ".MAIN_DB_PREFIX.$this->table_element."(";
    $sql .= ' rate,';
    $sql .= ' date_sync,';
    $sql .= ' fk_multicurrency,';
    $sql .= ' entity';
    $sql .= ') VALUES (';
    $sql .= ' '.((float) $this->rate).',';
    $sql .= " '".$this->db->idate($now)."',";
    $sql .= " ".((int) $fk_multicurrency).",";
    $sql .= " ".((int) $this->entity);
    $sql .= ')';
 
    $this->db->begin();
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $this->db->query($sql);
    if (!$resql) {
      $error++;
      $this->errors[] = 'Error '.$this->db->lasterror();
      dol_syslog('CurrencyRate::create '.join(',', $this->errors), LOG_ERR);
    }
 
    if (!$error) {
      $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX.$this->table_element);
      $this->fk_multicurrency = $fk_multicurrency;
      $this->date_sync = $now;
 
      if ($trigger) {
        $result = $this->call_trigger('CURRENCYRATE_CREATE', $user);
        if ($result < 0) {
          $error++;
        }
      }
    }
 
    if ($error) {
      $this->db->rollback();
 
      return -1 * $error;
    } else {
      $this->db->commit();
 
      return $this->id;
    }
  }
 
  public function fetch($id)
  {
    dol_syslog('CurrencyRate::fetch', LOG_DEBUG);
 
    $sql = "SELECT cr.rowid, cr.rate, cr.date_sync, cr.fk_multicurrency, cr.entity";
    $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." AS cr";
    $sql .= " WHERE cr.rowid = ".((int) $id);
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $this->db->query($sql);
    if ($resql) {
      $numrows = $this->db->num_rows($resql);
      if ($numrows) {
        $obj = $this->db->fetch_object($resql);
 
        $this->id = $obj->rowid;
        $this->rate = $obj->rate;
        $this->date_sync = $this->db->jdate($obj->date_sync);
        $this->fk_multicurrency = $obj->fk_multicurrency;
        $this->entity = $obj->entity;
      }
      $this->db->free($resql);
 
      if ($numrows) {
        return 1;
      } else {
        return 0;
      }
    } else {
      $this->errors[] = 'Error '.$this->db->lasterror();
      dol_syslog('CurrencyRate::fetch '.join(',', $this->errors), LOG_ERR);
 
      return -1;
    }
  }
 
  public function update($trigger = true)
  {
    global $user;
 
    $error = 0;
 
    dol_syslog('CurrencyRate::update', LOG_DEBUG);
 
    $this->rate = price2num($this->rate);
 
    // Update request
    $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element;
    $sql .= "SET rate = ".((float) $this->rate);
    if (!empty($this->date_sync)) {
      $sql .= ", date_sync = '".$this->db->idate($this->date_sync)."'";
    }
    if (!empty($this->fk_multicurrency)) {
      $sql .= ', fk_multicurrency = '.((int) $this->fk_multicurrency);
    }
    $sql .= " WHERE rowid =".((int) $this->id);
 
    $this->db->begin();
 
    dol_syslog(__METHOD__, LOG_DEBUG);
    $resql = $this->db->query($sql);
    if (!$resql) {
      $error++;
      $this->errors[] = 'Error '.$this->db->lasterror();
      dol_syslog('CurrencyRate::update '.join(',', $this->errors), LOG_ERR);
    }
 
    if (!$error && $trigger) {
      $result = $this->call_trigger('CURRENCYRATE_MODIFY', $user);
      if ($result < 0) {
        $error++;
      }
    }
 
    // Commit or rollback
    if ($error) {
      $this->db->rollback();
 
      return -1 * $error;
    } else {
      $this->db->commit();
 
      return 1;
    }
  }
 
  public function delete($trigger = true)
  {
    global $user;
 
    dol_syslog('CurrencyRate::delete', LOG_DEBUG);
 
    $error = 0;
 
    $this->db->begin();
 
    if ($trigger) {
      $result = $this->call_trigger('CURRENCYRATE_DELETE', $user);
      if ($result < 0) {
        $error++;
      }
    }
 
    if (!$error) {
      $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element;
      $sql .= ' WHERE rowid='.((int) $this->id);
 
      dol_syslog(__METHOD__, LOG_DEBUG);
      $resql = $this->db->query($sql);
      if (!$resql) {
        $error++;
        $this->errors[] = 'Error '.$this->db->lasterror();
        dol_syslog('CurrencyRate::delete '.join(',', $this->errors), LOG_ERR);
      }
    }
 
    // Commit or rollback
    if ($error) {
      $this->db->rollback();
 
      return -1 * $error;
    } else {
      $this->db->commit();
 
      return 1;
    }
  }
}