CRM/Batch/BAO/Batch.php

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