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
 117     return $financialAccount;
 118   }
 119
 120   /**
 121    * Delete financial Types.
 122    *
 123    * @param int $financialAccountId
 124    */
 125   public static function del($financialAccountId) {
 126     // checking if financial type is present
 127     $check = FALSE;
 128
 129     //check dependencies
 130     $dependency = [
 131       ['Core', 'FinancialTrxn', 'to_financial_account_id'],
 132       ['Financial', 'FinancialTypeAccount', 'financial_account_id'],
 133       ['Financial', 'FinancialItem', 'financial_account_id'],
 134     ];
 135     foreach ($dependency as $name) {
 136       require_once str_replace('_', DIRECTORY_SEPARATOR, "CRM_" . $name[0] . "_BAO_" . $name[1]) . ".php";
 137       $className = "CRM_{$name[0]}_BAO_{$name[1]}";
 138       $bao = new $className();
 139       $bao->{$name[2]} = $financialAccountId;
 140       if ($bao->find(TRUE)) {
 141         $check = TRUE;
 142       }
 143     }
 144
 145     if ($check) {
 146       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.'));
 147       return FALSE;
 148     }
 149
 150     // delete from financial Type table
 151     $financialAccount = new CRM_Financial_DAO_FinancialAccount();
 152     $financialAccount->id = $financialAccountId;
 153     $financialAccount->delete();
 154     return TRUE;
 155   }
 156
 157   /**
 158    * Get accounting code for a financial type with account relation Income Account is.
 159    *
 160    * @param int $financialTypeId
 161    *
 162    * @return int
 163    *   accounting code
 164    */
 165   public static function getAccountingCode($financialTypeId) {
 166     $relationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' "));
 167     $query = "SELECT cfa.accounting_code
 168 FROM civicrm_financial_type cft
 169 LEFT JOIN civicrm_entity_financial_account cefa ON cefa.entity_id = cft.id AND cefa.entity_table = 'civicrm_financial_type'
 170 LEFT JOIN  civicrm_financial_account cfa ON cefa.financial_account_id = cfa.id
 171 WHERE cft.id = %1
 172   AND account_relationship = %2";
 173     $params = [
 174       1 => [$financialTypeId, 'Integer'],
 175       2 => [$relationTypeId, 'Integer'],
 176     ];
 177     return CRM_Core_DAO::singleValueQuery($query, $params);
 178   }
 179
 180   /**
 181    * Get AR account.
 182    *
 183    * @param $financialAccountId
 184    *   Financial account id.
 185    *
 186    * @param $financialAccountTypeId
 187    *   Financial account type id.
 188    *
 189    * @param string $accountTypeCode
 190    *   account type code
 191    *
 192    * @return int
 193    *   count
 194    */
 195   public static function getARAccounts($financialAccountId, $financialAccountTypeId = NULL, $accountTypeCode = 'ar') {
 196     if (!$financialAccountTypeId) {
 197       $financialAccountTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('financial_account_type', NULL, " AND v.name LIKE 'Asset' "));
 198     }
 199     $query = "SELECT count(id) FROM civicrm_financial_account WHERE financial_account_type_id = %1 AND LCASE(account_type_code) = %2
 200       AND id != %3 AND is_active = 1;";
 201     $params = [
 202       1 => [$financialAccountTypeId, 'Integer'],
 203       2 => [strtolower($accountTypeCode), 'String'],
 204       3 => [$financialAccountId, 'Integer'],
 205     ];
 206     return CRM_Core_DAO::singleValueQuery($query, $params);
 207   }
 208
 209   /**
 210    * Get the Financial Account for a Financial Type Relationship Combo.
 211    *
 212    * Note that some relationships are optionally configured - so far
 213    * Chargeback and Credit / Contra. Since these are the only 2 currently Income
 214    * is an appropriate fallback. In future it might make sense to extend the logic.
 215    *
 216    * Note that we avoid the CRM_Core_PseudoConstant function as it stores one
 217    * account per financial type and is unreliable.
 218    *
 219    * @param int $financialTypeID
 220    *
 221    * @param string $relationshipType
 222    *
 223    * @return int
 224    */
 225   public static function getFinancialAccountForFinancialTypeByRelationship($financialTypeID, $relationshipType) {
 226     $relationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE '{$relationshipType}' "));
 227
 228     if (!isset(Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$relationTypeId])) {
 229       $accounts = civicrm_api3('EntityFinancialAccount', 'get', [
 230         'entity_id' => $financialTypeID,
 231         'entity_table' => 'civicrm_financial_type',
 232       ]);
 233
 234       foreach ($accounts['values'] as $account) {
 235         Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$account['account_relationship']] = $account['financial_account_id'];
 236       }
 237
 238       $accountRelationships = CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL);
 239
 240       $incomeAccountRelationshipID = array_search('Income Account is', $accountRelationships);
 241       $incomeAccountFinancialAccountID = Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$incomeAccountRelationshipID];
 242
 243       foreach (['Chargeback Account is', 'Credit/Contra Revenue Account is'] as $optionalAccountRelationship) {
 244
 245         $accountRelationshipID = array_search($optionalAccountRelationship, $accountRelationships);
 246         if (empty(Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$accountRelationshipID])) {
 247           Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$accountRelationshipID] = $incomeAccountFinancialAccountID;
 248         }
 249       }
 250       if (!isset(Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$relationTypeId])) {
 251         Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$relationTypeId] = NULL;
 252       }
 253     }
 254     return Civi::$statics[__CLASS__]['entity_financial_account'][$financialTypeID][$relationTypeId];
 255   }
 256
 257   /**
 258    * Get the sales tax financial account id for the financial type id.
 259    *
 260    * This is a helper wrapper to make the function name more readable.
 261    *
 262    * @param int $financialAccountID
 263    *
 264    * @return int
 265    */
 266   public static function getSalesTaxFinancialAccount($financialAccountID) {
 267     return self::getFinancialAccountForFinancialTypeByRelationship($financialAccountID, 'Sales Tax Account is');
 268   }
 269
 270   /**
 271    * Get Financial Account type relations.
 272    *
 273    * @param $flip bool
 274    *
 275    * @return array
 276    *
 277    */
 278   public static function getfinancialAccountRelations($flip = FALSE) {
 279     $params = ['labelColumn' => 'name'];
 280     $financialAccountType = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialAccount', 'financial_account_type_id', $params);
 281     $accountRelationships = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_EntityFinancialAccount', 'account_relationship', $params);
 282     $Links = [
 283       'Expense Account is' => 'Expenses',
 284       'Accounts Receivable Account is' => 'Asset',
 285       'Income Account is' => 'Revenue',
 286       'Asset Account is' => 'Asset',
 287       'Cost of Sales Account is' => 'Cost of Sales',
 288       'Premiums Inventory Account is' => 'Asset',
 289       'Discounts Account is' => 'Revenue',
 290       'Sales Tax Account is' => 'Liability',
 291       'Deferred Revenue Account is' => 'Liability',
 292     ];
 293     if (!$flip) {
 294       foreach ($Links as $accountRelation => $accountType) {
 295         $financialAccountLinks[array_search($accountRelation, $accountRelationships)] = array_search($accountType, $financialAccountType);
 296       }
 297     }
 298     else {
 299       foreach ($Links as $accountRelation => $accountType) {
 300         $financialAccountLinks[array_search($accountType, $financialAccountType)][] = array_search($accountRelation, $accountRelationships);
 301       }
 302     }
 303     return $financialAccountLinks;
 304   }
 305
 306   /**
 307    * Get Deferred Financial type.
 308    *
 309    * @return array
 310    *
 311    */
 312   public static function getDeferredFinancialType() {
 313     $deferredFinancialType = [];
 314     $query = "SELECT ce.entity_id, cft.name FROM civicrm_entity_financial_account ce
 315 INNER JOIN civicrm_financial_type cft ON ce.entity_id = cft.id
 316 WHERE ce.entity_table = 'civicrm_financial_type' AND ce.account_relationship = %1 AND cft.is_active = 1";
 317     $deferredAccountRel = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' "));
 318     $queryParams = [1 => [$deferredAccountRel, 'Integer']];
 319     $dao = CRM_Core_DAO::executeQuery($query, $queryParams);
 320     while ($dao->fetch()) {
 321       $deferredFinancialType[$dao->entity_id] = $dao->name;
 322     }
 323     return $deferredFinancialType;
 324   }
 325
 326   /**
 327    * Check if financial account is referenced by financial item.
 328    *
 329    * @param int $financialAccountId
 330    *
 331    * @param int $financialAccountTypeID
 332    *
 333    * @return bool
 334    *
 335    */
 336   public static function validateFinancialAccount($financialAccountId, $financialAccountTypeID = NULL) {
 337     $sql = "SELECT f.financial_account_type_id FROM civicrm_financial_account f
 338 INNER JOIN civicrm_financial_item fi ON fi.financial_account_id = f.id
 339 WHERE f.id = %1 AND f.financial_account_type_id IN (%2)
 340 LIMIT 1";
 341     $params = ['labelColumn' => 'name'];
 342     $financialAccountType = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialAccount', 'financial_account_type_id', $params);
 343     $params = [
 344       1 => [$financialAccountId, 'Integer'],
 345       2 => [
 346         implode(',',
 347           [
 348             array_search('Revenue', $financialAccountType),
 349             array_search('Liability', $financialAccountType),
 350           ]
 351         ),
 352         'Text',
 353       ],
 354     ];
 355     $result = CRM_Core_DAO::singleValueQuery($sql, $params);
 356     if ($result && $result != $financialAccountTypeID) {
 357       return TRUE;
 358     }
 359     return FALSE;
 360   }
 361
 362   /**
 363    * Validate Financial Type has Deferred Revenue account relationship
 364    * with Financial Account.
 365    *
 366    * @param array $params
 367    *   Holds submitted formvalues and params from api for updating/adding contribution.
 368    *
 369    * @param int $contributionID
 370    *   Contribution ID
 371    *
 372    * @param array $priceSetFields
 373    *   Array of price fields of a price set.
 374    *
 375    * @return bool
 376    *
 377    */
 378   public static function checkFinancialTypeHasDeferred($params, $contributionID = NULL, $priceSetFields = NULL) {
 379     if (!Civi::settings()->get('deferred_revenue_enabled')) {
 380       return FALSE;
 381     }
 382     $recognitionDate = $params['revenue_recognition_date'] ?? NULL;
 383     if (!(!CRM_Utils_System::isNull($recognitionDate)
 384       || ($contributionID && isset($params['prevContribution'])
 385         && !CRM_Utils_System::isNull($params['prevContribution']->revenue_recognition_date)))
 386     ) {
 387       return FALSE;
 388     }
 389
 390     $lineItems = $params['line_item'] ?? NULL;
 391     $financialTypeID = $params['financial_type_id'] ?? NULL;
 392     if (!$financialTypeID) {
 393       $financialTypeID = $params['prevContribution']->financial_type_id;
 394     }
 395     if (($contributionID || !empty($params['price_set_id'])) && empty($lineItems)) {
 396       if (!$contributionID) {
 397         CRM_Price_BAO_PriceSet::processAmount($priceSetFields,
 398           $params, $items);
 399       }
 400       else {
 401         $items = CRM_Price_BAO_LineItem::getLineItems($contributionID, 'contribution', TRUE, TRUE, TRUE);
 402       }
 403       if (!empty($items)) {
 404         $lineItems[] = $items;
 405       }
 406     }
 407     $deferredFinancialType = self::getDeferredFinancialType();
 408     $isError = FALSE;
 409     if (!empty($lineItems)) {
 410       foreach ($lineItems as $lineItem) {
 411         foreach ($lineItem as $items) {
 412           if (!array_key_exists($items['financial_type_id'], $deferredFinancialType)) {
 413             $isError = TRUE;
 414           }
 415         }
 416       }
 417     }
 418     elseif (!array_key_exists($financialTypeID, $deferredFinancialType)) {
 419       $isError = TRUE;
 420     }
 421
 422     if ($isError) {
 423       $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.');
 424       throw new CRM_Core_Exception($error);
 425     }
 426     return $isError;
 427   }
 428
 429   /**
 430    * Retrieve all Deferred Financial Accounts.
 431    *
 432    *
 433    * @return array of Deferred Financial Account
 434    *
 435    */
 436   public static function getAllDeferredFinancialAccount() {
 437     $financialAccount = [];
 438     $result = civicrm_api3('EntityFinancialAccount', 'get', [
 439       'sequential' => 1,
 440       'return' => ["financial_account_id.id", "financial_account_id.name", "financial_account_id.accounting_code"],
 441       'entity_table' => "civicrm_financial_type",
 442       'account_relationship' => "Deferred Revenue Account is",
 443     ]);
 444     if ($result['count'] > 0) {
 445       foreach ($result['values'] as $key => $value) {
 446         $financialAccount[$value['financial_account_id.id']] = $value['financial_account_id.name'] . ' (' . $value['financial_account_id.accounting_code'] . ')';
 447       }
 448     }
 449     return $financialAccount;
 450   }
 451
 452   /**
 453    * Get Organization Name associated with Financial Account.
 454    *
 455    * @param bool $checkPermissions
 456    *
 457    * @return array
 458    *
 459    */
 460   public static function getOrganizationNames($checkPermissions = TRUE) {
 461     $result = civicrm_api3('FinancialAccount', 'get', [
 462       'return' => ["contact_id.organization_name", "contact_id"],
 463       'contact_id.is_deleted' => 0,
 464       'options' => ['limit' => 0],
 465       'check_permissions' => $checkPermissions,
 466     ]);
 467     $organizationNames = [];
 468     foreach ($result['values'] as $values) {
 469       $organizationNames[$values['contact_id']] = $values['contact_id.organization_name'];
 470     }
 471     return $organizationNames;
 472   }
 473
 474 }