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