CRM/Batch/BAO/Batch.php

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