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
 132     if ($orderBy) {
 133       $orderBy = CRM_Utils_Type::escape($orderBy, 'String');
 134     }
 135
 136     $query = "SELECT ceft.id, ceft.financial_trxn_id FROM `civicrm_financial_trxn` cft
 137 LEFT JOIN civicrm_entity_financial_trxn ceft
 138 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
 139 LEFT JOIN civicrm_entity_financial_trxn ceft1
 140 ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
 141 LEFT JOIN civicrm_financial_item cfi ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
 142 WHERE ceft.entity_id = %1 AND (cfi.entity_table <> 'civicrm_financial_trxn' or cfi.entity_table is NULL)
 143 {$condition}
 144 ORDER BY cft.id {$orderBy}
 145 LIMIT 1;";
 146
 147     $params = array(1 => array($entity_id, 'Integer'));
 148     $dao = CRM_Core_DAO::executeQuery($query, $params);
 149     if ($dao->fetch()) {
 150       $ids['entityFinancialTrxnId'] = $dao->id;
 151       $ids['financialTrxnId'] = $dao->financial_trxn_id;
 152     }
 153     return $ids;
 154   }
 155
 156   /**
 157    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 158    * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 159    *
 160    * @param string $entityTable name of the entity table usually 'civicrm_contact'
 161    * @param int $entityID id of the entity usually the contactID.
 162    *
 163    * @return array( ) reference $tag array of catagory id's the contact belongs to.
 164    *
 165    * @access public
 166    * @static
 167    */
 168   static function getFinancialTrxnTotal($entity_id) {
 169     $query = "
 170       SELECT (ft.amount+SUM(ceft.amount)) AS total FROM civicrm_entity_financial_trxn AS ft
 171 LEFT JOIN civicrm_entity_financial_trxn AS ceft ON ft.financial_trxn_id = ceft.entity_id
 172 WHERE ft.entity_table = 'civicrm_contribution' AND ft.entity_id = %1
 173         ";
 174
 175     $sqlParams = array(1 => array($entity_id, 'Integer'));
 176     return  CRM_Core_DAO::singleValueQuery($query, $sqlParams);
 177
 178   }
 179   /**
 180    * Given an financial_trxn_id  check for previous entity_financial_trxn.
 181    *
 182    * @param int $financialTrxn_id id of the latest payment.
 183    *
 184    * @return array( ) $payment array of previous payments
 185    *
 186    * @access public
 187    * @static
 188    */
 189   static function getPayments($financial_trxn_id) {
 190     $query = "
 191 SELECT ef1.financial_trxn_id, sum(ef1.amount) amount
 192 FROM civicrm_entity_financial_trxn ef1
 193 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.financial_trxn_id = ef2.entity_id
 194 WHERE ef2.financial_trxn_id =%1
 195   AND ef2.entity_table = 'civicrm_financial_trxn'
 196   AND ef1.entity_table = 'civicrm_financial_item'
 197 GROUP BY ef1.financial_trxn_id
 198 UNION
 199 SELECT ef1.financial_trxn_id, ef1.amount
 200 FROM civicrm_entity_financial_trxn ef1
 201 LEFT JOIN civicrm_entity_financial_trxn ef2 ON ef1.entity_id = ef2.entity_id
 202 WHERE  ef2.financial_trxn_id =%1
 203   AND ef2.entity_table = 'civicrm_financial_trxn'
 204   AND ef1.entity_table = 'civicrm_financial_trxn'";
 205
 206     $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
 207     $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 208     $i = 0;
 209     $result = array();
 210     while ($dao->fetch()) {
 211       $result[$i]['financial_trxn_id'] = $dao->financial_trxn_id;
 212       $result[$i]['amount'] = $dao->amount;
 213       $i++;
 214     }
 215
 216     if (empty($result)) {
 217       $query = "SELECT sum( amount ) amount FROM civicrm_entity_financial_trxn WHERE financial_trxn_id =%1 AND entity_table = 'civicrm_financial_item'";
 218       $sqlParams = array(1 => array($financial_trxn_id, 'Integer'));
 219       $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 220
 221       if ($dao->fetch()) {
 222         $result[0]['financial_trxn_id'] = $financial_trxn_id;
 223         $result[0]['amount'] = $dao->amount;
 224       }
 225     }
 226     return $result;
 227   }
 228
 229   /**
 230    * Given an entity_id and entity_table, check for corresponding entity_financial_trxn and financial_trxn record.
 231    * NOTE: This should be moved to separate BAO for EntityFinancialTrxn when we start adding more code for that object.
 232    *
 233    * @param string $entityTable name of the entity table usually 'civicrm_contact'
 234    * @param int $entityID id of the entity usually the contactID.
 235    *
 236    * @return array(  ) reference $tag array of catagory id's the contact belongs to.
 237    *
 238    * @access public
 239    * @static
 240    */
 241   static function getFinancialTrxnLineTotal($entity_id, $entity_table = 'civicrm_contribution') {
 242     $query = "SELECT lt.price_field_value_id AS id, ft.financial_trxn_id,ft.amount AS amount FROM civicrm_entity_financial_trxn AS ft
 243 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'
 244 LEFT JOIN civicrm_line_item AS lt ON lt.id = fi.entity_id AND lt.entity_table = %2
 245 WHERE lt.entity_id = %1 ";
 246
 247     $sqlParams = array(1 => array($entity_id, 'Integer'), 2 => array($entity_table, 'String'));
 248     $dao =  CRM_Core_DAO::executeQuery($query, $sqlParams);
 249     while($dao->fetch()){
 250       $result[$dao->financial_trxn_id][$dao->id] = $dao->amount;
 251     }
 252     if (!empty($result)) {
 253       return $result;
 254     }
 255     else {
 256       return null;
 257     }
 258   }
 259
 260   /**
 261    * Delete financial transaction
 262    *
 263    * @return true on success, false otherwise
 264    * @access public
 265    * @static
 266    */
 267   static function deleteFinancialTrxn($entity_id) {
 268     $query = "DELETE ceft1, cfi, ceft, cft FROM `civicrm_financial_trxn` cft
 269 LEFT JOIN civicrm_entity_financial_trxn ceft
 270   ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
 271 LEFT JOIN civicrm_entity_financial_trxn ceft1
 272   ON ceft1.financial_trxn_id = cft.id AND ceft1.entity_table = 'civicrm_financial_item'
 273 LEFT JOIN civicrm_financial_item cfi
 274   ON ceft1.entity_table = 'civicrm_financial_item' and cfi.id = ceft1.entity_id
 275 WHERE ceft.entity_id = %1";
 276     CRM_Core_DAO::executeQuery($query, array(1 => array($entity_id, 'Integer')));
 277     return TRUE;
 278   }
 279
 280   /**
 281    * create financial transaction for premium
 282    *
 283    * @access public
 284    * @static
 285    */
 286   static function createPremiumTrxn($params) {
 287     if ((!CRM_Utils_Array::value('financial_type_id', $params) || !CRM_Utils_Array::value('contributionId', $params)) && !CRM_Utils_Array::value('oldPremium', $params)) {
 288       return;
 289     }
 290
 291     if (CRM_Utils_Array::value('cost', $params)) {
 292       $contributionStatuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 293       $financialAccountType = CRM_Contribute_PseudoConstant::financialAccountType($params['financial_type_id']);
 294       $accountRelationship = CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND label IN ('Premiums Inventory Account is', 'Cost of Sales Account is')");
 295       $toFinancialAccount = CRM_Utils_Array::value('isDeleted', $params) ? 'Premiums Inventory Account is' : 'Cost of Sales Account is';
 296       $fromFinancialAccount = CRM_Utils_Array::value('isDeleted', $params) ? 'Cost of Sales Account is': 'Premiums Inventory Account is';
 297       $accountRelationship = array_flip($accountRelationship);
 298       $financialtrxn = array(
 299         'to_financial_account_id' => $financialAccountType[$accountRelationship[$toFinancialAccount]],
 300         'from_financial_account_id' => $financialAccountType[$accountRelationship[$fromFinancialAccount]],
 301         'trxn_date' => date('YmdHis'),
 302         'total_amount' => CRM_Utils_Array::value('cost', $params) ? $params['cost'] : 0,
 303         'currency' => CRM_Utils_Array::value('currency', $params),
 304         'status_id' => array_search('Completed', $contributionStatuses)
 305       );
 306       $trxnEntityTable['entity_table'] = 'civicrm_contribution';
 307       $trxnEntityTable['entity_id'] = $params['contributionId'];
 308       CRM_Core_BAO_FinancialTrxn::create($financialtrxn, $trxnEntityTable);
 309     }
 310
 311     if (CRM_Utils_Array::value('oldPremium', $params)) {
 312       $premiumParams = array(
 313         'id' => $params['oldPremium']['product_id']
 314       );
 315       $productDetails = array();
 316       CRM_Contribute_BAO_ManagePremiums::retrieve($premiumParams, $productDetails);
 317       $params = array(
 318         'cost' => CRM_Utils_Array::value('cost', $productDetails),
 319         'currency' => CRM_Utils_Array::value('currency', $productDetails),
 320         'financial_type_id' => CRM_Utils_Array::value('financial_type_id', $productDetails),
 321         'contributionId' => $params['oldPremium']['contribution_id'],
 322         'isDeleted' => TRUE
 323       );
 324       CRM_Core_BAO_FinancialTrxn::createPremiumTrxn($params);
 325     }
 326   }
 327   /**
 328    * create financial trxn and items when fee is charged
 329    *
 330    * @params params to create trxn entries
 331    *
 332    * @access public
 333    * @static
 334    */
 335
 336   static function recordFees($params) {
 337     $expenseTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Expense Account is' "));
 338     $domainId = CRM_Core_Config::domainID();
 339     $amount = 0;
 340     if (CRM_Utils_Array::value('prevContribution', $params)) {
 341       $amount = $params['prevContribution']->fee_amount;
 342     }
 343     $amount = $params['fee_amount'] - $amount;
 344     if (!$amount) {
 345       return FALSE;
 346     }
 347     $financialAccount = CRM_Contribute_PseudoConstant::financialAccountType($params['financial_type_id'], $expenseTypeId);
 348     $params['trxnParams']['from_financial_account_id'] = $params['to_financial_account_id'];
 349     $params['trxnParams']['to_financial_account_id'] = $financialAccount;
 350     $params['trxnParams']['total_amount'] = $amount;
 351     $params['trxnParams']['fee_amount'] =
 352       $params['trxnParams']['net_amount'] = 0;
 353     $params['trxnParams']['status_id'] = CRM_Core_OptionGroup::getValue('contribution_status','Completed','name');
 354     $params['trxnParams']['contribution_id'] = isset($params['contribution']->id) ? $params['contribution']->id : $params['contribution_id'];
 355     $trxn = self::create($params['trxnParams']);
 356     if (!CRM_Utils_Array::value('entity_id', $params)) {
 357       $financialTrxnID = CRM_Core_BAO_FinancialTrxn::getFinancialTrxnId($params['trxnParams']['contribution_id'], 'DESC');
 358       $params['entity_id'] = $financialTrxnID['financialTrxnId'];
 359     }
 360     $fItemParams =
 361       array(
 362         'financial_account_id' => $financialAccount,
 363         'contact_id' => CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id'),
 364         'created_date' => date('YmdHis'),
 365         'transaction_date' => date('YmdHis'),
 366         'amount' => $amount,
 367         'description' => 'Fee',
 368         'status_id' => CRM_Core_OptionGroup::getValue('financial_item_status','Paid','name'),
 369         'entity_table' => 'civicrm_financial_trxn',
 370         'entity_id' => $params['entity_id'],
 371         'currency' => $params['trxnParams']['currency'],
 372       );
 373     $trxnIDS['id'] = $trxn->id;
 374     $financialItem = CRM_Financial_BAO_FinancialItem::create($fItemParams, NULL, $trxnIDS);
 375   }
 376 }
 377