CRM/Financial/BAO/FinancialAccount.php

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