CRM/Core/BAO/FinancialTrxn.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.3                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  32  * $Id$
  33  *
  34  */
  35
  36 class CRM_Core_BAO_FinancialTrxn extends CRM_Financial_DAO_FinancialTrxn {
  37   function __construct() {
  38     parent::__construct();
  39   }
  40
  41   /**
  42    * takes an associative array and creates a financial transaction object
  43    *
  44    * @param array  $params (reference ) an assoc array of name/value pairs
  45    *
  46    * @param string $trxnEntityTable entity_table
  47    *
  48    * @return object CRM_Core_BAO_FinancialTrxn object
  49    * @access public
  50    * @static
  51    */
  52   static function create(&$params, $trxnEntityTable = null ) {
  53     $trxn = new CRM_Financial_DAO_FinancialTrxn();
  54     $trxn->copyValues($params);
  55     $fids = array();
  56     if (!CRM_Utils_Rule::currencyCode($trxn->currency)) {
  57       $config = CRM_Core_Config::singleton();
  58       $trxn->currency = $config->defaultCurrency;
  59     }
  60
  61     $trxn->save();
  62
  63     // save to entity_financial_trxn table
  64     $entityFinancialTrxnParams =
  65       array(
  66         'entity_table' => "civicrm_contribution",
  67         'financial_trxn_id' => $trxn->id,
  68         'amount' => $params['total_amount'],
  69         'currency' => $trxn->currency,
  70       );
  71
  72     if (!empty($trxnEntityTable)) {
  73       $entityFinancialTrxnParams['entity_table'] = $trxnEntityTable['entity_table'];
  74       $entityFinancialTrxnParams['entity_id']    = $trxnEntityTable['entity_id'];
  75     }
  76     else {
  77       $entityFinancialTrxnParams['entity_id'] =  $params['contribution_id'];
  78     }
  79
  80     $entityTrxn = new CRM_Financial_DAO_EntityFinancialTrxn();
  81     $entityTrxn->copyValues($entityFinancialTrxnParams);
  82     $entityTrxn->save();
  83     return $trxn;
  84   }
  85
  86   /**
  87    * Takes a bunch of params that are needed to match certain criteria and
  88    * retrieves the relevant objects. Typically the valid params are only
  89    * contact_id. We'll tweak this function to be more full featured over a period
  90    * of time. This is the inverse function of create. It also stores all the retrieved
  91    * values in the default array
  92    *
  93    * @param array $params   (reference ) an assoc array of name/value pairs
  94    * @param array $defaults (reference ) an assoc array to hold the flattened values
  95    *
  96    * @return object CRM_Contribute_BAO_ContributionType object
  97    * @access public
  98    * @static
  99    */
 100   static function retrieve( &$params, &$defaults ) {
 101     $financialItem = new CRM_Financial_DAO_FinancialTrxn( );
 102     $financialItem->copyValues($params);
 103     if ($financialItem->find(true)) {
 104       CRM_Core_DAO::storeValues( $financialItem, $defaults );
 105       return $financialItem;
 106     }
 107     return null;
 108   }
 109
 110   /**
 111    *
 112    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 113    * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 114    *
 115    * @param string $entityTable name of the entity table usually 'civicrm_contact'
 116    * @param int $entityID id of the entity usually the contactID.
 117    * @param string  $orderBy to get single trxn id for a entity table i.e last or first.
 118    *
 119    * @return array( ) reference $tag array of catagory id's the contact belongs to.
 120    *
 121    * @access public
 122    * @static
 123    */
 124   static function getFinancialTrxnId($entity_id, $orderBy = 'ASC', $newTrxn = FALSE) {
 125     $ids = array('entityFinancialTrxnId' => NULL, 'financialTrxnId' => NULL);
 126
 127     $condition = "";
 128     if (!$newTrxn) {
 129       $condition = " AND ((ceft1.entity_table IS NOT NULL) OR (cft.payment_instrument_id IS NOT NULL AND ceft1.entity_table IS NULL)) ";
 130     }
 131     $query = "SELECT ceft.id, ceft.financial_trxn_id FROM `civicrm_financial_trxn` cft
 132 LEFT JOIN civicrm_entity_financial_trxn ceft
 133 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
 134 LEFT JOIN civicrm_entity_financial_trxn ceft1
 135 ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
 136 LEFT JOIN civicrm_financial_item cfi ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
 137 WHERE ceft.entity_id = %1 AND (cfi.entity_table <> 'civicrm_financial_trxn' or cfi.entity_table is NULL)
 138 {$condition}
 139 ORDER BY cft.id {$orderBy}
 140 LIMIT 1;";
 141
 142     $params = array(1 => array($entity_id, 'Integer'));
 143     $dao = CRM_Core_DAO::executeQuery($query, $params);
 144     if ($dao->fetch()) {
 145       $ids['entityFinancialTrxnId'] = $dao->id;
 146       $ids['financialTrxnId'] = $dao->financial_trxn_id;
 147     }
 148     return $ids;
 149   }
 150
 151   /**
 152    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 153    * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 154    *
 155    * @param string $entityTable name of the entity table usually 'civicrm_contact'
 156    * @param int $entityID id of the entity usually the contactID.
 157    *
 158    * @return array( ) reference $tag array of catagory id's the contact belongs to.
 159    *
 160    * @access public
 161    * @static
 162    */
 163   static function getFinancialTrxnTotal($entity_id) {
 164     $query = "
 165       SELECT (ft.amount+SUM(ceft.amount)) AS total FROM civicrm_entity_financial_trxn AS ft
 166 LEFT JOIN civicrm_entity_financial_trxn AS ceft ON ft.financial_trxn_id = ceft.entity_id
 167 WHERE ft.entity_table = 'civicrm_contribution' AND ft.entity_id = %1
 168         ";
 169
 170     $sqlParams = array(1 => array($entity_id, 'Integer'));
 171     return  CRM_Core_DAO::singleValueQuery($query, $sqlParams);
 172
 173   }
 174   /**
 175    * Given an financial_trxn_id  check for previous entity_financial_trxn.
 176    *
 177    * @param int $financialTrxn_id id of the latest payment.
 178    *
 179    * @return array( ) $payment array of previous payments
 180    *
 181    * @access public
 182    * @static
 183    */
 184   static function getPayments($financial_trxn_id) {
 185     $query = "
 186 SELECT ef1.financial_trxn_id, sum(ef1.amount) amount
 187 FROM civicrm_entity_financial_trxn ef1
 188 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.financial_trxn_id = ef2.entity_id
 189 WHERE ef2.financial_trxn_id =%1
 190   AND ef2.entity_table = 'civicrm_financial_trxn'
 191   AND ef1.entity_table = 'civicrm_financial_item'
 192 GROUP BY ef1.financial_trxn_id
 193 UNION
 194 SELECT ef1.financial_trxn_id, ef1.amount
 195 FROM civicrm_entity_financial_trxn ef1
 196 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.entity_id = ef2.entity_id
 197 WHERE  ef2.financial_trxn_id =%1
 198   AND ef2.entity_table = 'civicrm_financial_trxn'
 199   AND ef1.entity_table = 'civicrm_financial_trxn'";
 200
 201     $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
 202     $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 203     $i = 0;
 204     $result = array();
 205     while ($dao->fetch()) {
 206       $result[$i]['financial_trxn_id'] = $dao->financial_trxn_id;
 207       $result[$i]['amount'] = $dao->amount;
 208       $i++;
 209     }
 210
 211     if (empty($result)) {
 212       $query = "SELECT sum( amount ) amount FROM civicrm_entity_financial_trxn WHERE financial_trxn_id =%1 AND entity_table = 'civicrm_financial_item'";
 213       $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
 214       $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 215
 216       if ($dao->fetch()) {
 217         $result[0]['financial_trxn_id'] = $financial_trxn_id;
 218         $result[0]['amount'] = $dao->amount;
 219       }
 220     }
 221     return $result;
 222   }
 223
 224   /**
 225    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 226    * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 227    *
 228    * @param string $entityTable name of the entity table usually 'civicrm_contact'
 229    * @param int $entityID id of the entity usually the contactID.
 230    *
 231    * @return array(  ) reference $tag array of catagory id's the contact belongs to.
 232    *
 233    * @access public
 234    * @static
 235    */
 236   static function getFinancialTrxnLineTotal($entity_id, $entity_table = 'civicrm_contribution') {
 237     $query = "SELECT lt.price_field_value_id AS id, ft.financial_trxn_id,ft.amount AS amount FROM civicrm_entity_financial_trxn AS ft
 238 LEFT JOIN civicrm_financial_item AS fi ON fi.id = ft.entity_id AND fi.entity_table = 'civicrm_line_item' AND ft.entity_table = 'civicrm_financial_item'
 239 LEFT JOIN civicrm_line_item AS lt ON lt.id = fi.entity_id AND lt.entity_table = %2
 240 WHERE lt.entity_id = %1 ";
 241
 242     $sqlParams = array(1 => array($entity_id, 'Integer'), 2 => array($entity_table, 'String'));
 243     $dao =  CRM_Core_DAO::executeQuery($query, $sqlParams);
 244     while($dao->fetch()){
 245       $result[$dao->financial_trxn_id][$dao->id] = $dao->amount;
 246     }
 247     if (!empty($result)) {
 248       return $result;
 249     }
 250     else {
 251       return null;
 252     }
 253   }
 254
 255   /**
 256    * Delete financial transaction
 257    *
 258    * @return true on success, false otherwise
 259    * @access public
 260    * @static
 261    */
 262   static function deleteFinancialTrxn($entity_id) {
 263     $query = "DELETE ceft1, cfi, ceft, cft FROM `civicrm_financial_trxn` cft
 264 LEFT JOIN civicrm_entity_financial_trxn ceft
 265   ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
 266 LEFT JOIN civicrm_entity_financial_trxn ceft1
 267   ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
 268 LEFT JOIN civicrm_financial_item cfi
 269   ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
 270 WHERE ceft.entity_id = %1";
 271     CRM_Core_DAO::executeQuery($query, array(1 => array($entity_id, 'Integer')));
 272     return TRUE;
 273   }
 274
 275   /**
 276    * create financial transaction for premium
 277    *
 278    * @access public
 279    * @static
 280    */
 281   static function createPremiumTrxn($params) {
 282     if ((!CRM_Utils_Array::value('financial_type_id', $params) || !CRM_Utils_Array::value('contributionId', $params)) && !CRM_Utils_Array::value('oldPremium', $params)) {
 283       return;
 284     }
 285
 286     if (CRM_Utils_Array::value('cost', $params)) {
 287       $contributionStatuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 288       $financialAccountType = CRM_Contribute_PseudoConstant::financialAccountType($params['financial_type_id']);
 289       $accountRelationship = CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND label IN ('Premiums Inventory Account is', 'Cost of Sales Account is')");
 290       $toFinancialAccount = CRM_Utils_Array::value('isDeleted', $params) ? 'Premiums Inventory Account is' : 'Cost of Sales Account is';
 291       $fromFinancialAccount = CRM_Utils_Array::value('isDeleted', $params) ? 'Cost of Sales Account is': 'Premiums Inventory Account is';
 292       $accountRelationship = array_flip($accountRelationship);
 293       $financialtrxn = array(
 294         'to_financial_account_id' => $financialAccountType[$accountRelationship[$toFinancialAccount]],
 295         'from_financial_account_id' => $financialAccountType[$accountRelationship[$fromFinancialAccount]],
 296         'trxn_date' => date('YmdHis'),
 297         'total_amount' => CRM_Utils_Array::value('cost', $params) ? $params['cost'] : 0,
 298         'currency' => CRM_Utils_Array::value('currency', $params),
 299         'status_id' => array_search('Completed', $contributionStatuses)
 300       );
 301       $trxnEntityTable['entity_table'] = 'civicrm_contribution';
 302       $trxnEntityTable['entity_id'] = $params['contributionId'];
 303       CRM_Core_BAO_FinancialTrxn::create($financialtrxn, $trxnEntityTable);
 304     }
 305
 306     if (CRM_Utils_Array::value('oldPremium', $params)) {
 307       $premiumParams = array(
 308         'id' => $params['oldPremium']['product_id']
 309       );
 310       $productDetails = array();
 311       CRM_Contribute_BAO_ManagePremiums::retrieve($premiumParams, $productDetails);
 312       $params = array(
 313         'cost' => CRM_Utils_Array::value('cost', $productDetails),
 314         'currency' => CRM_Utils_Array::value('currency', $productDetails),
 315         'financial_type_id' => CRM_Utils_Array::value('financial_type_id', $productDetails),
 316         'contributionId' => $params['oldPremium']['contribution_id'],
 317         'isDeleted' => TRUE
 318       );
 319       CRM_Core_BAO_FinancialTrxn::createPremiumTrxn($params);
 320     }
 321   }
 322   /**
 323    * create financial trxn and items when fee is charged
 324    *
 325    * @params params to create trxn entries
 326    *
 327    * @access public
 328    * @static
 329    */
 330
 331   static function recordFees($params) {
 332     $expenseTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Expense Account is' "));
 333     $domainId = CRM_Core_Config::domainID();
 334     $amount = 0;
 335     if (CRM_Utils_Array::value('prevContribution', $params)) {
 336       $amount = $params['prevContribution']->fee_amount;
 337     }
 338     $amount = $params['fee_amount'] - $amount;
 339     $financialAccount = CRM_Contribute_PseudoConstant::financialAccountType($params['financial_type_id'], $expenseTypeId);
 340     $params['trxnParams']['from_financial_account_id'] = $params['to_financial_account_id'];
 341     $params['trxnParams']['to_financial_account_id'] = $financialAccount;
 342     $params['trxnParams']['total_amount'] = $amount;
 343     $params['trxnParams']['fee_amount'] =
 344       $params['trxnParams']['net_amount'] = 0;
 345     $params['trxnParams']['status_id'] = CRM_Core_OptionGroup::getValue('contribution_status','Completed','name');
 346     $params['trxnParams']['contribution_id'] = isset($params['contribution']->id) ? $params['contribution']->id : $params['contribution_id'];
 347     $trxn = self::create($params['trxnParams']);
 348     if (!CRM_Utils_Array::value('entity_id', $params)) {
 349       $financialTrxnID = CRM_Core_BAO_FinancialTrxn::getFinancialTrxnId($params['trxnParams']['contribution_id'], 'DESC');
 350       $params['entity_id'] = $financialTrxnID['financialTrxnId'];
 351     }
 352     $fItemParams =
 353       array(
 354         'financial_account_id' => $financialAccount,
 355         'contact_id' => CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id'),
 356         'created_date' => date('YmdHis'),
 357         'transaction_date' => date('YmdHis'),
 358         'amount' => $amount,
 359         'description' => 'Fee',
 360         'status_id' => CRM_Core_OptionGroup::getValue('financial_item_status','Paid','name'),
 361         'entity_table' => 'civicrm_financial_trxn',
 362         'entity_id' => $params['entity_id'],
 363         'currency' => $params['trxnParams']['currency'],
 364       );
 365     $trxnIDS['id'] = $trxn->id;
 366     $financialItem = CRM_Financial_BAO_FinancialItem::create($fItemParams, NULL, $trxnIDS);
 367   }
 368 }
 369