CRM/Batch/BAO/Batch.php

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