CRM/Financial/BAO/FinancialAccount.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2019
  32  */
  33 class CRM_Financial_BAO_FinancialAccount extends CRM_Financial_DAO_FinancialAccount {
  34
  35   /**
  36    * Class constructor.
  37    */
  38   public function __construct() {
  39     parent::__construct();
  40   }
  41
  42   /**
  43    * Fetch object based on array of properties.
  44    *
  45    * @param array $params
  46    *   (reference ) an assoc array of name/value pairs.
  47    * @param array $defaults
  48    *   (reference ) an assoc array to hold the flattened values.
  49    *
  50    * @return CRM_Financial_BAO_FinancialAccount
  51    */
  52   public static function retrieve(&$params, &$defaults = []) {
  53     $financialAccount = new CRM_Financial_DAO_FinancialAccount();
  54     $financialAccount->copyValues($params);
  55     if ($financialAccount->find(TRUE)) {
  56       CRM_Core_DAO::storeValues($financialAccount, $defaults);
  57       return $financialAccount;
  58     }
  59     return NULL;
  60   }
  61
  62   /**
  63    * Update the is_active flag in the db.
  64    *
  65    * @param int $id
  66    *   Id of the database record.
  67    * @param bool $is_active
  68    *   Value we want to set the is_active field.
  69    *
  70    * @return bool
  71    *   true if we found and updated the object, else false
  72    */
  73   public static function setIsActive($id, $is_active) {
  74     return CRM_Core_DAO::setFieldValue('CRM_Financial_DAO_FinancialAccount', $id, 'is_active', $is_active);
  75   }
  76
  77   /**
  78    * Add the financial types.
  79    *
  80    * @param array $params
  81    *   Reference array contains the values submitted by the form.
  82    *
  83    * @return CRM_Financial_DAO_FinancialAccount
  84    */
  85   public static function add(&$params) {
  86     if (empty($params['id'])) {
  87       $params['is_active'] = CRM_Utils_Array::value('is_active', $params, FALSE);
  88       $params['is_deductible'] = CRM_Utils_Array::value('is_deductible', $params, FALSE);
  89       $params['is_tax'] = CRM_Utils_Array::value('is_tax', $params, FALSE);
  90       $params['is_header_account'] = CRM_Utils_Array::value('is_header_account', $params, FALSE);
  91       $params['is_default'] = CRM_Utils_Array::value('is_default', $params, FALSE);
  92     }
  93     if (!empty($params['id'])
  94       && !empty($params['financial_account_type_id'])
  95       && CRM_Financial_BAO_FinancialAccount::validateFinancialAccount(
  96         $params['id'],
  97         $params['financial_account_type_id']
  98       )
  99     ) {
 100       throw new CRM_Core_Exception(ts('You cannot change the account type since this financial account refers to a financial item having an account type of Revenue/Liability.'));
 101     }
 102     if (!empty($params['is_default'])) {
 103       $query = 'UPDATE civicrm_financial_account SET is_default = 0 WHERE financial_account_type_id = %1';
 104       $queryParams = [1 => [$params['financial_account_type_id'], 'Integer']];
 105       CRM_Core_DAO::executeQuery($query, $queryParams);
 106     }
 107
 108     // action is taken depending upon the mode
 109     $financialAccount = new CRM_Financial_DAO_FinancialAccount();
 110
 111     // invoke pre hook
 112     $op = 'create';
 113     if (!empty($params['id'])) {
 114       $op = 'edit';
 115     }
 116     CRM_Utils_Hook::pre($op, 'FinancialAccount', CRM_Utils_Array::value('id', $params), $params);
 117
 118     if (!empty($params['id'])) {
 119       $financialAccount->id = $params['id'];
 120       $financialAccount->find(TRUE);
 121     }
 122
 123     $financialAccount->copyValues($params);
 124     $financialAccount->save();
 125
 126     // invoke post hook
 127     $op = 'create';
 128     if (!empty($params['id'])) {
 129       $op = 'edit';
 130     }
 131     CRM_Utils_Hook::post($op, 'FinancialAccount', $financialAccount->id, $financialAccount);
 132
 133     return $financialAccount;
 134   }
 135
 136   /**
 137    * Delete financial Types.
 138    *
 139    * @param int $financialAccountId
 140    */
 141   public static function del($financialAccountId) {
 142     // checking if financial type is present
 143     $check = FALSE;
 144
 145     //check dependencies
 146     $dependency = [
 147       ['Core', 'FinancialTrxn', 'to_financial_account_id'],
 148       ['Financial', 'FinancialTypeAccount', 'financial_account_id'],
 149       ['Financial', 'FinancialItem', 'financial_account_id'],
 150     ];
 151     foreach ($dependency as $name) {
 152       require_once str_replace('_', DIRECTORY_SEPARATOR, "CRM_" . $name[0] . "_BAO_" . $name[1]) . ".php";
 153       $className = "CRM_{$name[0]}_BAO_{$name[1]}";
 154       $bao = new $className();
 155       $bao->{$name[2]} = $financialAccountId;
 156       if ($bao->find(TRUE)) {
 157         $check = TRUE;
 158       }
 159     }
 160
 161     if ($check) {
 162       CRM_Core_Session::setStatus(ts('This financial account cannot be deleted since it is being used as a header account. Please remove it from being a header account before trying to delete it again.'));
 163       return FALSE;
 164     }
 165
 166     // delete from financial Type table
 167     $financialAccount = new CRM_Financial_DAO_FinancialAccount();
 168     $financialAccount->id = $financialAccountId;
 169     $financialAccount->delete();
 170     return TRUE;
 171   }
 172
 173   /**
 174    * Get accounting code for a financial type with account relation Income Account is.
 175    *
 176    * @param int $financialTypeId
 177    *
 178    * @return int
 179    *   accounting code
 180    */
 181   public static function getAccountingCode($financialTypeId) {
 182     $relationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' "));
 183     $query = "SELECT cfa.accounting_code
 184 FROM civicrm_financial_type cft
 185 LEFT JOIN civicrm_entity_financial_account cefa ON cefa.entity_id = cft.id AND cefa.entity_table = 'civicrm_financial_type'
 186 LEFT JOIN  civicrm_financial_account cfa ON cefa.financial_account_id = cfa.id
 187 WHERE cft.id = %1
 188   AND account_relationship = %2";
 189     $params = [
 190       1 => [$financialTypeId, 'Integer'],
 191       2 => [$relationTypeId, 'Integer'],
 192     ];
 193     return CRM_Core_DAO::singleValueQuery($query, $params);
 194   }
 195
 196   /**
 197    * Get AR account.
 198    *
 199    * @param $financialAccountId
 200    *   Financial account id.
 201    *
 202    * @param $financialAccountTypeId
 203    *   Financial account type id.
 204    *
 205    * @param string $accountTypeCode
 206    *   account type code
 207    *
 208    * @return int
 209    *   count
 210    */
 211   public static function getARAccounts($financialAccountId, $financialAccountTypeId = NULL, $accountTypeCode = 'ar') {
 212     if (!$financialAccountTypeId) {
 213       $financialAccountTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('financial_account_type', NULL, " AND v.name LIKE 'Asset' "));
 214     }
 215     $query = "SELECT count(id) FROM civicrm_financial_account WHERE financial_account_type_id = %1 AND LCASE(account_type_code) = %2
 216       AND id != %3 AND is_active = 1;";
 217     $params = [
 218       1 => [$financialAccountTypeId, 'Integer'],
 219       2 => [strtolower($accountTypeCode), 'String'],
 220       3 => [$financialAccountId, 'Integer'],
 221     ];
 222     return CRM_Core_DAO::singleValueQuery($query, $params);
 223   }
 224
 225   /**
 226    * Get the Financial Account for a Financial Type Relationship Combo.
 227    *
 228    * Note that some relationships are optionally configured - so far
 229    * Chargeback and Credit / Contra. Since these are the only 2 currently Income
 230    * is an appropriate fallback. In future it might make sense to extend the logic.
 231    *
 232    * Note that we avoid the CRM_Core_PseudoConstant function as it stores one
 233    * account per financial type and is unreliable.
 234    *
 235    * @param int $financialTypeID
 236    *
 237    * @param string $relationshipType
 238    *
 239    * @return int
 240    */
 241   public static function getFinancialAccountForFinancialTypeByRelationship($financialTypeID, $relationshipType) {
 242     $relationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE '{$relationshipType}' "));
 243
 244     if (!isset(Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$relationTypeId])) {
 245       $accounts = civicrm_api3('EntityFinancialAccount', 'get', [
 246         'entity_id' => $financialTypeID,
 247         'entity_table' => 'civicrm_financial_type',
 248       ]);
 249
 250       foreach ($accounts['values'] as $account) {
 251         Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$account['account_relationship']] = $account['financial_account_id'];
 252       }
 253
 254       $accountRelationships = CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL);
 255
 256       $incomeAccountRelationshipID = array_search('Income Account is', $accountRelationships);
 257       $incomeAccountFinancialAccountID = Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$incomeAccountRelationshipID];
 258
 259       foreach (['Chargeback Account is', 'Credit/Contra Revenue Account is'] as $optionalAccountRelationship) {
 260
 261         $accountRelationshipID = array_search($optionalAccountRelationship, $accountRelationships);
 262         if (empty(Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$accountRelationshipID])) {
 263           Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$accountRelationshipID] = $incomeAccountFinancialAccountID;
 264         }
 265       }
 266       if (!isset(Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$relationTypeId])) {
 267         Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$relationTypeId] = NULL;
 268       }
 269     }
 270     return Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$relationTypeId];
 271   }
 272
 273   /**
 274    * Get the sales tax financial account id for the financial type id.
 275    *
 276    * This is a helper wrapper to make the function name more readable.
 277    *
 278    * @param int $financialAccountID
 279    *
 280    * @return int
 281    */
 282   public static function getSalesTaxFinancialAccount($financialAccountID) {
 283     return self::getFinancialAccountForFinancialTypeByRelationship($financialAccountID, 'Sales Tax Account is');
 284   }
 285
 286   /**
 287    * Get Financial Account type relations.
 288    *
 289    * @param $flip bool
 290    *
 291    * @return array
 292    *
 293    */
 294   public static function getfinancialAccountRelations($flip = FALSE) {
 295     $params = ['labelColumn' => 'name'];
 296     $financialAccountType = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialAccount', 'financial_account_type_id', $params);
 297     $accountRelationships = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_EntityFinancialAccount', 'account_relationship', $params);
 298     $Links = [
 299       'Expense Account is' => 'Expenses',
 300       'Accounts Receivable Account is' => 'Asset',
 301       'Income Account is' => 'Revenue',
 302       'Asset Account is' => 'Asset',
 303       'Cost of Sales Account is' => 'Cost of Sales',
 304       'Premiums Inventory Account is' => 'Asset',
 305       'Discounts Account is' => 'Revenue',
 306       'Sales Tax Account is' => 'Liability',
 307       'Deferred Revenue Account is' => 'Liability',
 308     ];
 309     if (!$flip) {
 310       foreach ($Links as $accountRelation => $accountType) {
 311         $financialAccountLinks[array_search($accountRelation, $accountRelationships)] = array_search($accountType, $financialAccountType);
 312       }
 313     }
 314     else {
 315       foreach ($Links as $accountRelation => $accountType) {
 316         $financialAccountLinks[array_search($accountType, $financialAccountType)][] = array_search($accountRelation, $accountRelationships);
 317       }
 318     }
 319     return $financialAccountLinks;
 320   }
 321
 322   /**
 323    * Get Deferred Financial type.
 324    *
 325    * @return array
 326    *
 327    */
 328   public static function getDeferredFinancialType() {
 329     $deferredFinancialType = [];
 330     $query = "SELECT ce.entity_id, cft.name FROM civicrm_entity_financial_account ce
 331 INNER JOIN civicrm_financial_type cft ON ce.entity_id = cft.id
 332 WHERE ce.entity_table = 'civicrm_financial_type' AND ce.account_relationship = %1 AND cft.is_active = 1";
 333     $deferredAccountRel = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' "));
 334     $queryParams = [1 => [$deferredAccountRel, 'Integer']];
 335     $dao = CRM_Core_DAO::executeQuery($query, $queryParams);
 336     while ($dao->fetch()) {
 337       $deferredFinancialType[$dao->entity_id] = $dao->name;
 338     }
 339     return $deferredFinancialType;
 340   }
 341
 342   /**
 343    * Check if financial account is referenced by financial item.
 344    *
 345    * @param int $financialAccountId
 346    *
 347    * @param int $financialAccountTypeID
 348    *
 349    * @return bool
 350    *
 351    */
 352   public static function validateFinancialAccount($financialAccountId, $financialAccountTypeID = NULL) {
 353     $sql = "SELECT f.financial_account_type_id FROM civicrm_financial_account f
 354 INNER JOIN civicrm_financial_item fi ON fi.financial_account_id = f.id
 355 WHERE f.id = %1 AND f.financial_account_type_id IN (%2)
 356 LIMIT 1";
 357     $params = ['labelColumn' => 'name'];
 358     $financialAccountType = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialAccount', 'financial_account_type_id', $params);
 359     $params = [
 360       1 => [$financialAccountId, 'Integer'],
 361       2 => [
 362         implode(',',
 363           [
 364             array_search('Revenue', $financialAccountType),
 365             array_search('Liability', $financialAccountType),
 366           ]
 367         ),
 368         'Text',
 369       ],
 370     ];
 371     $result = CRM_Core_DAO::singleValueQuery($sql, $params);
 372     if ($result && $result != $financialAccountTypeID) {
 373       return TRUE;
 374     }
 375     return FALSE;
 376   }
 377
 378   /**
 379    * Validate Financial Type has Deferred Revenue account relationship
 380    * with Financial Account.
 381    *
 382    * @param array $params
 383    *   Holds submitted formvalues and params from api for updating/adding contribution.
 384    *
 385    * @param int $contributionID
 386    *   Contribution ID
 387    *
 388    * @param array $priceSetFields
 389    *   Array of price fields of a price set.
 390    *
 391    * @return bool
 392    *
 393    */
 394   public static function checkFinancialTypeHasDeferred($params, $contributionID = NULL, $priceSetFields = NULL) {
 395     if (!Civi::settings()->get('deferred_revenue_enabled')) {
 396       return FALSE;
 397     }
 398     $recognitionDate = CRM_Utils_Array::value('revenue_recognition_date', $params);
 399     if (!(!CRM_Utils_System::isNull($recognitionDate)
 400       || ($contributionID && isset($params['prevContribution'])
 401         && !CRM_Utils_System::isNull($params['prevContribution']->revenue_recognition_date)))
 402     ) {
 403       return FALSE;
 404     }
 405
 406     $lineItems = CRM_Utils_Array::value('line_item', $params);
 407     $financialTypeID = CRM_Utils_Array::value('financial_type_id', $params);
 408     if (!$financialTypeID) {
 409       $financialTypeID = $params['prevContribution']->financial_type_id;
 410     }
 411     if (($contributionID || !empty($params['price_set_id'])) && empty($lineItems)) {
 412       if (!$contributionID) {
 413         CRM_Price_BAO_PriceSet::processAmount($priceSetFields,
 414           $params, $items);
 415       }
 416       else {
 417         $items = CRM_Price_BAO_LineItem::getLineItems($contributionID, 'contribution', TRUE, TRUE, TRUE);
 418       }
 419       if (!empty($items)) {
 420         $lineItems[] = $items;
 421       }
 422     }
 423     $deferredFinancialType = self::getDeferredFinancialType();
 424     $isError = FALSE;
 425     if (!empty($lineItems)) {
 426       foreach ($lineItems as $lineItem) {
 427         foreach ($lineItem as $items) {
 428           if (!array_key_exists($items['financial_type_id'], $deferredFinancialType)) {
 429             $isError = TRUE;
 430           }
 431         }
 432       }
 433     }
 434     elseif (!array_key_exists($financialTypeID, $deferredFinancialType)) {
 435       $isError = TRUE;
 436     }
 437
 438     if ($isError) {
 439       $error = ts('Revenue Recognition Date cannot be processed unless there is a Deferred Revenue account setup for the Financial Type. Please remove Revenue Recognition Date, select a different Financial Type with a Deferred Revenue account setup for it, or setup a Deferred Revenue account for this Financial Type.');
 440       throw new CRM_Core_Exception($error);
 441     }
 442     return $isError;
 443   }
 444
 445   /**
 446    * Retrieve all Deferred Financial Accounts.
 447    *
 448    *
 449    * @return array of Deferred Financial Account
 450    *
 451    */
 452   public static function getAllDeferredFinancialAccount() {
 453     $financialAccount = [];
 454     $result = civicrm_api3('EntityFinancialAccount', 'get', [
 455       'sequential' => 1,
 456       'return' => ["financial_account_id.id", "financial_account_id.name", "financial_account_id.accounting_code"],
 457       'entity_table' => "civicrm_financial_type",
 458       'account_relationship' => "Deferred Revenue Account is",
 459     ]);
 460     if ($result['count'] > 0) {
 461       foreach ($result['values'] as $key => $value) {
 462         $financialAccount[$value['financial_account_id.id']] = $value['financial_account_id.name'] . ' (' . $value['financial_account_id.accounting_code'] . ')';
 463       }
 464     }
 465     return $financialAccount;
 466   }
 467
 468   /**
 469    * Get Organization Name associated with Financial Account.
 470    *
 471    * @param bool $checkPermissions
 472    *
 473    * @return array
 474    *
 475    */
 476   public static function getOrganizationNames($checkPermissions = TRUE) {
 477     $result = civicrm_api3('FinancialAccount', 'get', [
 478       'return' => ["contact_id.organization_name", "contact_id"],
 479       'contact_id.is_deleted' => 0,
 480       'options' => ['limit' => 0],
 481       'check_permissions' => $checkPermissions,
 482     ]);
 483     $organizationNames = [];
 484     foreach ($result['values'] as $values) {
 485       $organizationNames[$values['contact_id']] = $values['contact_id.organization_name'];
 486     }
 487     return $organizationNames;
 488   }
 489
 490 }