CRM/Batch/BAO/Batch.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2017                                |
   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-2017
  32  */
  33
  34 /**
  35  * Batch BAO class.
  36  */
  37 class CRM_Batch_BAO_Batch extends CRM_Batch_DAO_Batch {
  38
  39   /**
  40    * Cache for the current batch object.
  41    */
  42   static $_batch = NULL;
  43
  44   /**
  45    * Not sure this is the best way to do this. Depends on how exportFinancialBatch() below gets called.
  46    * Maybe a parameter to that function is better.
  47    */
  48   static $_exportFormat = NULL;
  49
  50   /**
  51    * Create a new batch.
  52    *
  53    * @param array $params
  54    *
  55    * @return object
  56    *   $batch batch object
  57    */
  58   public static function create(&$params) {
  59     $op = 'edit';
  60     $batchId = CRM_Utils_Array::value('id', $params);
  61     if (!$batchId) {
  62       $op = 'create';
  63       $params['name'] = CRM_Utils_String::titleToVar($params['title']);
  64     }
  65     CRM_Utils_Hook::pre($op, 'Batch', $batchId, $params);
  66     $batch = new CRM_Batch_DAO_Batch();
  67     $batch->copyValues($params);
  68     $batch->save();
  69
  70     CRM_Utils_Hook::post($op, 'Batch', $batch->id, $batch);
  71
  72     return $batch;
  73   }
  74
  75   /**
  76    * Retrieve the information about the batch.
  77    *
  78    * @param array $params
  79    *   (reference ) an assoc array of name/value pairs.
  80    * @param array $defaults
  81    *   (reference ) an assoc array to hold the flattened values.
  82    *
  83    * @return array
  84    *   CRM_Batch_BAO_Batch object on success, null otherwise
  85    */
  86   public static function retrieve(&$params, &$defaults) {
  87     $batch = new CRM_Batch_DAO_Batch();
  88     $batch->copyValues($params);
  89     if ($batch->find(TRUE)) {
  90       CRM_Core_DAO::storeValues($batch, $defaults);
  91       return $batch;
  92     }
  93     return NULL;
  94   }
  95
  96   /**
  97    * Get profile id associated with the batch type.
  98    *
  99    * @param int $batchTypeId
 100    *   Batch type id.
 101    *
 102    * @return int
 103    *   $profileId   profile id
 104    */
 105   public static function getProfileId($batchTypeId) {
 106     //retrieve the profile specific to batch type
 107     switch ($batchTypeId) {
 108       case 1:
 109       case 3:
 110         //batch profile used for pledges
 111         $profileName = "contribution_batch_entry";
 112         break;
 113
 114       case 2:
 115         //batch profile used for memberships
 116         $profileName = "membership_batch_entry";
 117         break;
 118     }
 119
 120     // get and return the profile id
 121     return CRM_Core_DAO::getFieldValue('CRM_Core_BAO_UFGroup', $profileName, 'id', 'name');
 122   }
 123
 124   /**
 125    * Generate batch name.
 126    *
 127    * @return string
 128    *   batch name
 129    */
 130   public static function generateBatchName() {
 131     $sql = "SELECT max(id) FROM civicrm_batch";
 132     $batchNo = CRM_Core_DAO::singleValueQuery($sql) + 1;
 133     return ts('Batch %1', array(1 => $batchNo)) . ': ' . date('Y-m-d');
 134   }
 135
 136   /**
 137    * Delete batch entry.
 138    *
 139    * @param int $batchId
 140    *   Batch id.
 141    *
 142    * @return bool
 143    */
 144   public static function deleteBatch($batchId) {
 145     // delete entry from batch table
 146     CRM_Utils_Hook::pre('delete', 'Batch', $batchId, CRM_Core_DAO::$_nullArray);
 147     $batch = new CRM_Batch_DAO_Batch();
 148     $batch->id = $batchId;
 149     $batch->delete();
 150     CRM_Utils_Hook::post('delete', 'Batch', $batch->id, $batch);
 151     return TRUE;
 152   }
 153
 154   /**
 155    * wrapper for ajax batch selector.
 156    *
 157    * @param array $params
 158    *   Associated array for params record id.
 159    *
 160    * @return array
 161    *   associated array of batch list
 162    */
 163   public static function getBatchListSelector(&$params) {
 164     // format the params
 165     $params['offset'] = ($params['page'] - 1) * $params['rp'];
 166     $params['rowCount'] = $params['rp'];
 167     $params['sort'] = CRM_Utils_Array::value('sortBy', $params);
 168
 169     // get batches
 170     $batches = self::getBatchList($params);
 171
 172     // get batch totals for open batches
 173     $fetchTotals = array();
 174     $batchStatus = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
 175     $batchStatus = array(
 176       array_search('Open', $batchStatus),
 177       array_search('Reopened', $batchStatus),
 178     );
 179     if ($params['context'] == 'financialBatch') {
 180       foreach ($batches as $id => $batch) {
 181         if (in_array($batch['status_id'], $batchStatus)) {
 182           $fetchTotals[] = $id;
 183         }
 184       }
 185     }
 186     $totals = self::batchTotals($fetchTotals);
 187
 188     // add count
 189     $params['total'] = self::getBatchCount($params);
 190
 191     // format params and add links
 192     $batchList = array();
 193
 194     foreach ($batches as $id => $value) {
 195       $batch = array();
 196       if ($params['context'] == 'financialBatch') {
 197         $batch['check'] = $value['check'];
 198       }
 199       $batch['batch_name'] = $value['title'];
 200       $batch['total'] = '';
 201       $batch['payment_instrument'] = $value['payment_instrument'];
 202       $batch['item_count'] = CRM_Utils_Array::value('item_count', $value);
 203       $batch['type'] = CRM_Utils_Array::value('batch_type', $value);
 204       if (!empty($value['total'])) {
 205         // CRM-21205
 206         $batch['total'] = CRM_Utils_Money::format($value['total'], $value['currency']);
 207       }
 208
 209       // Compare totals with actuals
 210       if (isset($totals[$id])) {
 211         $batch['item_count'] = self::displayTotals($totals[$id]['item_count'], $batch['item_count']);
 212         $batch['total'] = self::displayTotals(CRM_Utils_Money::format($totals[$id]['total']), $batch['total']);
 213       }
 214       $batch['status'] = $value['batch_status'];
 215       $batch['created_by'] = $value['created_by'];
 216       $batch['links'] = $value['action'];
 217       $batchList[$id] = $batch;
 218     }
 219     return $batchList;
 220   }
 221
 222   /**
 223    * Get list of batches.
 224    *
 225    * @param array $params
 226    *   Associated array for params.
 227    *
 228    * @return array
 229    */
 230   public static function getBatchList(&$params) {
 231     $apiParams = self::whereClause($params);
 232
 233     if (!empty($params['rowCount']) && is_numeric($params['rowCount'])
 234       && is_numeric($params['offset']) && $params['rowCount'] > 0
 235     ) {
 236       $apiParams['options'] = array('offset' => $params['offset'], 'limit' => $params['rowCount']);
 237     }
 238     $apiParams['options']['sort'] = 'id DESC';
 239     if (!empty($params['sort'])) {
 240       $apiParams['options']['sort'] = CRM_Utils_Type::escape($params['sort'], 'String');
 241     }
 242
 243     $return = array(
 244       "id",
 245       "name",
 246       "title",
 247       "description",
 248       "created_date",
 249       "status_id",
 250       "modified_id",
 251       "modified_date",
 252       "type_id",
 253       "mode_id",
 254       "total",
 255       "item_count",
 256       "exported_date",
 257       "payment_instrument_id",
 258       "created_id.sort_name",
 259       "created_id",
 260     );
 261     $apiParams['return'] = $return;
 262     $batches = civicrm_api3('Batch', 'get', $apiParams);
 263     $obj = new CRM_Batch_BAO_Batch();
 264     if (!empty($params['context'])) {
 265       $links = $obj->links($params['context']);
 266     }
 267     else {
 268       $links = $obj->links();
 269     }
 270
 271     $batchTypes = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'type_id');
 272     $batchStatus = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id');
 273     $batchStatusByName = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
 274     $paymentInstrument = CRM_Contribute_PseudoConstant::paymentInstrument();
 275
 276     $results = array();
 277     foreach ($batches['values'] as $values) {
 278       $newLinks = $links;
 279       $action = array_sum(array_keys($newLinks));
 280
 281       if ($values['status_id'] == array_search('Closed', $batchStatusByName) && $params['context'] != 'financialBatch') {
 282         $newLinks = array();
 283       }
 284       elseif ($params['context'] == 'financialBatch') {
 285         $values['check'] = "<input type='checkbox' id='check_" .
 286           $values['id'] .
 287           "' name='check_" .
 288           $values['id'] .
 289           "' value='1'  data-status_id='" .
 290           $values['status_id'] . "' class='select-row'></input>";
 291
 292         switch ($batchStatusByName[$values['status_id']]) {
 293           case 'Open':
 294           case 'Reopened':
 295             CRM_Utils_Array::remove($newLinks, 'reopen', 'download');
 296             break;
 297
 298           case 'Closed':
 299             CRM_Utils_Array::remove($newLinks, 'close', 'edit', 'download');
 300             break;
 301
 302           case 'Exported':
 303             CRM_Utils_Array::remove($newLinks, 'close', 'edit', 'reopen', 'export');
 304         }
 305         if (!CRM_Batch_BAO_Batch::checkBatchPermission('edit', $values['created_id'])) {
 306           CRM_Utils_Array::remove($newLinks, 'edit');
 307         }
 308         if (!CRM_Batch_BAO_Batch::checkBatchPermission('close', $values['created_id'])) {
 309           CRM_Utils_Array::remove($newLinks, 'close', 'export');
 310         }
 311         if (!CRM_Batch_BAO_Batch::checkBatchPermission('reopen', $values['created_id'])) {
 312           CRM_Utils_Array::remove($newLinks, 'reopen');
 313         }
 314         if (!CRM_Batch_BAO_Batch::checkBatchPermission('export', $values['created_id'])) {
 315           CRM_Utils_Array::remove($newLinks, 'export', 'download');
 316         }
 317         if (!CRM_Batch_BAO_Batch::checkBatchPermission('delete', $values['created_id'])) {
 318           CRM_Utils_Array::remove($newLinks, 'delete');
 319         }
 320       }
 321       if (!empty($values['type_id'])) {
 322         $values['batch_type'] = $batchTypes[$values['type_id']];
 323       }
 324       $values['batch_status'] = $batchStatus[$values['status_id']];
 325       $values['created_by'] = $values['created_id.sort_name'];
 326       $values['payment_instrument'] = '';
 327       if (!empty($values['payment_instrument_id'])) {
 328         $values['payment_instrument'] = $paymentInstrument[$values['payment_instrument_id']];
 329       }
 330       $tokens = array('id' => $values['id'], 'status' => $values['status_id']);
 331       if ($values['status_id'] == array_search('Exported', $batchStatusByName)) {
 332         $aid = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Export Accounting Batch');
 333         $activityParams = array('source_record_id' => $values['id'], 'activity_type_id' => $aid);
 334         $exportActivity = CRM_Activity_BAO_Activity::retrieve($activityParams, $val);
 335         $fid = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_EntityFile', $exportActivity->id, 'file_id', 'entity_id');
 336         $tokens = array_merge(array('eid' => $exportActivity->id, 'fid' => $fid), $tokens);
 337       }
 338       $values['action'] = CRM_Core_Action::formLink(
 339         $newLinks,
 340         $action,
 341         $tokens,
 342         ts('more'),
 343         FALSE,
 344         'batch.selector.row',
 345         'Batch',
 346         $values['id']
 347       );
 348       // CRM-21205
 349       $values['currency'] = CRM_Core_DAO::singleValueQuery("
 350         SELECT GROUP_CONCAT(DISTINCT ft.currency)
 351         FROM  civicrm_batch batch
 352         JOIN civicrm_entity_batch eb
 353           ON batch.id = eb.batch_id
 354         JOIN civicrm_financial_trxn ft
 355           ON eb.entity_id = ft.id
 356         WHERE batch.id = %1
 357         GROUP BY batch.id
 358       ", array(1 => array($values['id'], 'Positive')));
 359       $results[$values['id']] = $values;
 360     }
 361
 362     return $results;
 363   }
 364
 365   /**
 366    * Get count of batches.
 367    *
 368    * @param array $params
 369    *   Associated array for params.
 370    *
 371    * @return null|string
 372    */
 373   public static function getBatchCount(&$params) {
 374     $apiParams = self::whereClause($params);
 375     return civicrm_api3('Batch', 'getCount', $apiParams);
 376   }
 377
 378   /**
 379    * Format where clause for getting lists of batches.
 380    *
 381    * @param array $params
 382    *   Associated array for params.
 383    *
 384    * @return string
 385    */
 386   public static function whereClause($params) {
 387     $clauses = array();
 388     // Exclude data-entry batches
 389     $batchStatus = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
 390     if (empty($params['status_id'])) {
 391       $clauses['status_id'] = array('NOT IN' => array("Data Entry"));
 392     }
 393
 394     $return = array(
 395       "id",
 396       "name",
 397       "title",
 398       "description",
 399       "created_date",
 400       "status_id",
 401       "modified_id",
 402       "modified_date",
 403       "type_id",
 404       "mode_id",
 405       "total",
 406       "item_count",
 407       "exported_date",
 408       "payment_instrument_id",
 409       "created_id.sort_name",
 410       "created_id",
 411     );
 412     if (!CRM_Core_Permission::check("view all manual batches")) {
 413       if (CRM_Core_Permission::check("view own manual batches")) {
 414         $loggedInContactId = CRM_Core_Session::singleton()->get('userID');
 415         $params['created_id'] = $loggedInContactId;
 416       }
 417       else {
 418         $params['created_id'] = 0;
 419       }
 420     }
 421     foreach ($return as $field) {
 422       if (!isset($params[$field])) {
 423         continue;
 424       }
 425       $value = CRM_Utils_Type::escape($params[$field], 'String', FALSE);
 426       if (in_array($field, array('name', 'title', 'description', 'created_id.sort_name'))) {
 427         $clauses[$field] = array('LIKE' => "%{$value}%");
 428       }
 429       elseif ($field == 'status_id' && $value == array_search('Open', $batchStatus)) {
 430         $clauses['status_id'] = array('IN' => array("Open", 'Reopened'));
 431       }
 432       else {
 433         $clauses[$field] = $value;
 434       }
 435     }
 436     return $clauses;
 437   }
 438
 439   /**
 440    * Define action links.
 441    *
 442    * @param null $context
 443    *
 444    * @return array
 445    *   array of action links
 446    */
 447   public function links($context = NULL) {
 448     if ($context == 'financialBatch') {
 449       $links = array(
 450         'transaction' => array(
 451           'name' => ts('Transactions'),
 452           'url' => 'civicrm/batchtransaction',
 453           'qs' => 'reset=1&bid=%%id%%',
 454           'title' => ts('View/Add Transactions to Batch'),
 455         ),
 456         'edit' => array(
 457           'name' => ts('Edit'),
 458           'url' => 'civicrm/financial/batch',
 459           'qs' => 'reset=1&action=update&id=%%id%%&context=1',
 460           'title' => ts('Edit Batch'),
 461         ),
 462         'close' => array(
 463           'name' => ts('Close'),
 464           'title' => ts('Close Batch'),
 465           'url' => '#',
 466           'extra' => 'rel="close"',
 467         ),
 468         'export' => array(
 469           'name' => ts('Export'),
 470           'title' => ts('Export Batch'),
 471           'url' => '#',
 472           'extra' => 'rel="export"',
 473         ),
 474         'reopen' => array(
 475           'name' => ts('Re-open'),
 476           'title' => ts('Re-open Batch'),
 477           'url' => '#',
 478           'extra' => 'rel="reopen"',
 479         ),
 480         'delete' => array(
 481           'name' => ts('Delete'),
 482           'title' => ts('Delete Batch'),
 483           'url' => '#',
 484           'extra' => 'rel="delete"',
 485         ),
 486         'download' => array(
 487           'name' => ts('Download'),
 488           'url' => 'civicrm/file',
 489           'qs' => 'reset=1&id=%%fid%%&eid=%%eid%%',
 490           'title' => ts('Download Batch'),
 491         ),
 492       );
 493     }
 494     else {
 495       $links = array(
 496         CRM_Core_Action::COPY => array(
 497           'name' => ts('Enter records'),
 498           'url' => 'civicrm/batch/entry',
 499           'qs' => 'id=%%id%%&reset=1',
 500           'title' => ts('Batch Data Entry'),
 501         ),
 502         CRM_Core_Action::UPDATE => array(
 503           'name' => ts('Edit'),
 504           'url' => 'civicrm/batch',
 505           'qs' => 'action=update&id=%%id%%&reset=1',
 506           'title' => ts('Edit Batch'),
 507         ),
 508         CRM_Core_Action::DELETE => array(
 509           'name' => ts('Delete'),
 510           'url' => 'civicrm/batch',
 511           'qs' => 'action=delete&id=%%id%%',
 512           'title' => ts('Delete Batch'),
 513         ),
 514       );
 515     }
 516     return $links;
 517   }
 518
 519   /**
 520    * Get batch list.
 521    *
 522    * @return array
 523    *   all batches excluding batches with data entry in progress
 524    */
 525   public static function getBatches() {
 526     $dataEntryStatusId = CRM_Core_PseudoConstant::getKey('CRM_Batch_BAO_Batch', 'status_id', 'Data Entry');
 527     $query = "SELECT id, title
 528       FROM civicrm_batch
 529       WHERE item_count >= 1
 530       AND status_id != {$dataEntryStatusId}
 531       ORDER BY title";
 532
 533     $batches = array();
 534     $dao = CRM_Core_DAO::executeQuery($query);
 535     while ($dao->fetch()) {
 536       $batches[$dao->id] = $dao->title;
 537     }
 538     return $batches;
 539   }
 540
 541
 542   /**
 543    * Calculate sum of all entries in a batch.
 544    * Used to validate and update item_count and total when closing an accounting batch
 545    *
 546    * @param array $batchIds
 547    * @return array
 548    */
 549   public static function batchTotals($batchIds) {
 550     $totals = array_fill_keys($batchIds, array('item_count' => 0, 'total' => 0));
 551     if ($batchIds) {
 552       $sql = "SELECT eb.batch_id, COUNT(tx.id) AS item_count, SUM(tx.total_amount) AS total
 553       FROM civicrm_entity_batch eb
 554       INNER JOIN civicrm_financial_trxn tx ON tx.id = eb.entity_id AND eb.entity_table = 'civicrm_financial_trxn'
 555       WHERE eb.batch_id IN (" . implode(',', $batchIds) . ")
 556       GROUP BY eb.batch_id";
 557       $dao = CRM_Core_DAO::executeQuery($sql);
 558       while ($dao->fetch()) {
 559         $totals[$dao->batch_id] = (array) $dao;
 560       }
 561       $dao->free();
 562     }
 563     return $totals;
 564   }
 565
 566   /**
 567    * Format markup for comparing two totals.
 568    *
 569    * @param $actual
 570    *   calculated total
 571    * @param $expected
 572    *   user-entered total
 573    * @return array
 574    */
 575   public static function displayTotals($actual, $expected) {
 576     $class = 'actual-value';
 577     if ($expected && $expected != $actual) {
 578       $class .= ' crm-error';
 579     }
 580     $actualTitle = ts('Current Total');
 581     $output = "<span class='$class' title='$actualTitle'>$actual</span>";
 582     if ($expected) {
 583       $expectedTitle = ts('Expected Total');
 584       $output .= " / <span class='expected-value' title='$expectedTitle'>$expected</span>";
 585     }
 586     return $output;
 587   }
 588
 589   /**
 590    * Function for exporting financial accounts, currently we support CSV and IIF format
 591    * @see http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
 592    *
 593    * @param array $batchIds
 594    *   Associated array of batch ids.
 595    * @param string $exportFormat
 596    *   Export format.
 597    */
 598   public static function exportFinancialBatch($batchIds, $exportFormat) {
 599     if (empty($batchIds)) {
 600       CRM_Core_Error::fatal(ts('No batches were selected.'));
 601       return;
 602     }
 603     if (empty($exportFormat)) {
 604       CRM_Core_Error::fatal(ts('No export format selected.'));
 605       return;
 606     }
 607     self::$_exportFormat = $exportFormat;
 608
 609     // Instantiate appropriate exporter based on user-selected format.
 610     $exporterClass = "CRM_Financial_BAO_ExportFormat_" . self::$_exportFormat;
 611     if (class_exists($exporterClass)) {
 612       $exporter = new $exporterClass();
 613     }
 614     else {
 615       CRM_Core_Error::fatal("Could not locate exporter: $exporterClass");
 616     }
 617     $export = array();
 618     foreach ($batchIds as $batchId) {
 619       // export only batches whose status is set to Exported.
 620       $result = civicrm_api3('Batch', 'getcount', array(
 621         'id' => $batchId,
 622         'status_id' => "Exported",
 623       ));
 624       if (!$result) {
 625         continue;
 626       }
 627       $export[$batchId] = $exporter->generateExportQuery($batchId);
 628     }
 629     if ($export) {
 630       $exporter->makeExport($export);
 631     }
 632   }
 633
 634   /**
 635    * @param array $batchIds
 636    * @param $status
 637    */
 638   public static function closeReOpen($batchIds = array(), $status) {
 639     $batchStatus = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id');
 640     $params['status_id'] = CRM_Utils_Array::key($status, $batchStatus);
 641     $session = CRM_Core_Session::singleton();
 642     $params['modified_date'] = date('YmdHis');
 643     $params['modified_id'] = $session->get('userID');
 644     foreach ($batchIds as $key => $value) {
 645       $params['id'] = $ids['batchID'] = $value;
 646       self::create($params, $ids);
 647     }
 648     $url = CRM_Utils_System::url('civicrm/financial/financialbatches', "reset=1&batchStatus={$params['status_id']}");
 649     CRM_Utils_System::redirect($url);
 650   }
 651
 652   /**
 653    * Retrieve financial items assigned for a batch.
 654    *
 655    * @param int $entityID
 656    * @param array $returnValues
 657    * @param bool $notPresent
 658    * @param array $params
 659    * @param bool $getCount
 660    *
 661    * @return CRM_Core_DAO
 662    */
 663   public static function getBatchFinancialItems($entityID, $returnValues, $notPresent = NULL, $params = NULL, $getCount = FALSE) {
 664     if (!$getCount) {
 665       if (!empty($params['rowCount']) &&
 666         $params['rowCount'] > 0
 667       ) {
 668         $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
 669       }
 670     }
 671     // action is taken depending upon the mode
 672     $select = 'civicrm_financial_trxn.id ';
 673     if (!empty($returnValues)) {
 674       $select .= " , " . implode(' , ', $returnValues);
 675     }
 676
 677     $orderBy = " ORDER BY civicrm_financial_trxn.id";
 678     if (!empty($params['sort'])) {
 679       $orderBy = ' ORDER BY ' . CRM_Utils_Type::escape($params['sort'], 'String');
 680     }
 681
 682     $from = "civicrm_financial_trxn
 683 INNER JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id
 684 INNER JOIN civicrm_contribution ON (civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id
 685   AND civicrm_entity_financial_trxn.entity_table='civicrm_contribution')
 686 LEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
 687 AND civicrm_entity_batch.entity_id = civicrm_financial_trxn.id
 688 LEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id
 689 LEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id
 690 LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id
 691 ";
 692
 693     $searchFields = array(
 694       'sort_name',
 695       'financial_type_id',
 696       'contribution_page_id',
 697       'payment_instrument_id',
 698       'contribution_trxn_id',
 699       'contribution_source',
 700       'contribution_currency_type',
 701       'contribution_pay_later',
 702       'contribution_recurring',
 703       'contribution_test',
 704       'contribution_thankyou_date_is_not_null',
 705       'contribution_receipt_date_is_not_null',
 706       'contribution_pcp_made_through_id',
 707       'contribution_pcp_display_in_roll',
 708       'contribution_date_relative',
 709       'contribution_amount_low',
 710       'contribution_amount_high',
 711       'contribution_in_honor_of',
 712       'contact_tags',
 713       'group',
 714       'contribution_date_relative',
 715       'contribution_date_high',
 716       'contribution_date_low',
 717       'contribution_check_number',
 718       'contribution_status_id',
 719     );
 720     $values = array();
 721     foreach ($searchFields as $field) {
 722       if (isset($params[$field])) {
 723         $values[$field] = $params[$field];
 724         if ($field == 'sort_name') {
 725           $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id
 726           LEFT JOIN civicrm_email ON contact_b.id = civicrm_email.contact_id";
 727         }
 728         if ($field == 'contribution_in_honor_of') {
 729           $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id";
 730         }
 731         if ($field == 'contact_tags') {
 732           $from .= " LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-{$params[$field]}` ON `civicrm_entity_tag-{$params[$field]}`.entity_id = contact_a.id";
 733         }
 734         if ($field == 'group') {
 735           $from .= " LEFT JOIN civicrm_group_contact `civicrm_group_contact-{$params[$field]}` ON contact_a.id = `civicrm_group_contact-{$params[$field]}`.contact_id ";
 736         }
 737         if ($field == 'contribution_date_relative') {
 738           $relativeDate = explode('.', $params[$field]);
 739           $date = CRM_Utils_Date::relativeToAbsolute($relativeDate[0], $relativeDate[1]);
 740           $values['contribution_date_low'] = $date['from'];
 741           $values['contribution_date_high'] = $date['to'];
 742         }
 743         $searchParams = CRM_Contact_BAO_Query::convertFormValues($values);
 744         // @todo the use of defaultReturnProperties means the search will be inefficient
 745         // as slow-unneeded properties are included.
 746         $query = new CRM_Contact_BAO_Query($searchParams,
 747           CRM_Contribute_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_CONTRIBUTE,
 748             FALSE
 749           ), NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_CONTRIBUTE
 750         );
 751         if ($field == 'contribution_date_high' || $field == 'contribution_date_low') {
 752           $query->dateQueryBuilder($params[$field], 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date');
 753         }
 754       }
 755     }
 756     if (!empty($query->_where[0])) {
 757       $where = implode(' AND ', $query->_where[0]) .
 758         " AND civicrm_entity_batch.batch_id IS NULL ";
 759       $where = str_replace('civicrm_contribution.payment_instrument_id', 'civicrm_financial_trxn.payment_instrument_id', $where);
 760     }
 761     else {
 762       if (!$notPresent) {
 763         $where = " civicrm_entity_batch.batch_id = {$entityID} ";
 764       }
 765       else {
 766         $where = " civicrm_entity_batch.batch_id IS NULL ";
 767       }
 768     }
 769
 770     $sql = "
 771 SELECT {$select}
 772 FROM   {$from}
 773 WHERE  {$where}
 774        {$orderBy}
 775 ";
 776
 777     if (isset($limit)) {
 778       $sql .= "{$limit}";
 779     }
 780
 781     $result = CRM_Core_DAO::executeQuery($sql);
 782     return $result;
 783   }
 784
 785   /**
 786    * Get batch names.
 787    * @param string $batchIds
 788    *
 789    * @return array
 790    *   array of batches
 791    */
 792   public static function getBatchNames($batchIds) {
 793     $query = 'SELECT id, title
 794       FROM civicrm_batch
 795       WHERE id IN (' . $batchIds . ')';
 796
 797     $batches = array();
 798     $dao = CRM_Core_DAO::executeQuery($query);
 799     while ($dao->fetch()) {
 800       $batches[$dao->id] = $dao->title;
 801     }
 802     return $batches;
 803   }
 804
 805   /**
 806    * Function get batch statuses.
 807    *
 808    * @param string $batchIds
 809    *
 810    * @return array
 811    *   array of batches
 812    */
 813   public static function getBatchStatuses($batchIds) {
 814     $query = 'SELECT id, status_id
 815       FROM civicrm_batch
 816       WHERE id IN (' . $batchIds . ')';
 817
 818     $batches = array();
 819     $dao = CRM_Core_DAO::executeQuery($query);
 820     while ($dao->fetch()) {
 821       $batches[$dao->id] = $dao->status_id;
 822     }
 823     return $batches;
 824   }
 825
 826   /**
 827    * Function to check permission for batch.
 828    *
 829    * @param string $action
 830    * @param int $batchCreatedId
 831    *   batch created by contact id
 832    *
 833    * @return bool
 834    */
 835   public static function checkBatchPermission($action, $batchCreatedId = NULL) {
 836     if (CRM_Core_Permission::check("{$action} all manual batches")) {
 837       return TRUE;
 838     }
 839     if (CRM_Core_Permission::check("{$action} own manual batches")) {
 840       $loggedInContactId = CRM_Core_Session::singleton()->get('userID');
 841       if ($batchCreatedId == $loggedInContactId) {
 842         return TRUE;
 843       }
 844       elseif (CRM_Utils_System::isNull($batchCreatedId)) {
 845         return TRUE;
 846       }
 847     }
 848     return FALSE;
 849   }
 850
 851 }