CRM/Batch/BAO/Batch.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   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 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 (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   (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 entry from batch table
 169     $batch = new CRM_Batch_DAO_Batch();
 170     $batch->id = $batchId;
 171     $batch->delete();
 172     return true;
 173   }
 174
 175   /**
 176    * This function is a wrapper for ajax batch selector
 177    *
 178    * @param  array   $params associated array for params record id.
 179    *
 180    * @return array   $batchList associated array of batch list
 181    * @access public
 182    */
 183   public function getBatchListSelector(&$params) {
 184     // format the params
 185     $params['offset'] = ($params['page'] - 1) * $params['rp'];
 186     $params['rowCount'] = $params['rp'];
 187     $params['sort'] = CRM_Utils_Array::value('sortBy', $params);
 188
 189     // get batches
 190     $batches = self::getBatchList($params);
 191
 192     // get batch totals for open batches
 193     $fetchTotals = array();
 194     $batchStatus =  CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
 195     $batchStatus = array(
 196       array_search('Open', $batchStatus),
 197       array_search('Reopened', $batchStatus),
 198     );
 199     if ($params['context'] == 'financialBatch') {
 200       foreach ($batches as $id => $batch) {
 201         if (in_array($batch['status_id'], $batchStatus)) {
 202           $fetchTotals[] = $id;
 203         }
 204       }
 205     }
 206     $totals = self::batchTotals($fetchTotals);
 207
 208     // add count
 209     $params['total'] = self::getBatchCount($params);
 210
 211     // format params and add links
 212     $batchList = array();
 213
 214     foreach ($batches as $id => $value) {
 215       $batch = array();
 216       if ($params['context'] == 'financialBatch') {
 217         $batch['check'] = $value['check'];
 218       }
 219       $batch['batch_name'] = $value['title'];
 220       $batch['total'] = $batch['item_count'] = '';
 221       $batch['payment_instrument'] = $value['payment_instrument'];
 222       $batch['item_count'] = CRM_Utils_Array::value('item_count', $value);
 223       if (!empty($value['total'])) {
 224         $batch['total'] = CRM_Utils_Money::format($value['total']);
 225       }
 226
 227       // Compare totals with actuals
 228       if (isset($totals[$id])) {
 229         $batch['item_count'] = self::displayTotals($totals[$id]['item_count'], $batch['item_count']);
 230         $batch['total'] = self::displayTotals(CRM_Utils_Money::format($totals[$id]['total']), $batch['total']);
 231       }
 232       $batch['status'] = $value['batch_status'];
 233       $batch['created_by'] = $value['created_by'];
 234       $batch['links'] = $value['action'];
 235       $batchList[$id] = $batch;
 236     }
 237     return $batchList;
 238   }
 239
 240   /**
 241    * Get list of batches
 242    *
 243    * @param  array   $params associated array for params
 244    * @access public
 245    */
 246   static function getBatchList(&$params) {
 247     $whereClause = self::whereClause($params);
 248
 249     if (!empty($params['rowCount']) && is_numeric($params['rowCount'])
 250       && is_numeric($params['offset']) && $params['rowCount'] > 0
 251     ) {
 252       $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
 253     }
 254
 255     $orderBy = ' ORDER BY batch.id desc';
 256     if (!empty($params['sort'])) {
 257       $orderBy = ' ORDER BY ' . CRM_Utils_Type::escape($params['sort'], 'String');
 258     }
 259
 260     $query = "
 261       SELECT batch.*, c.sort_name created_by
 262       FROM  civicrm_batch batch
 263       INNER JOIN civicrm_contact c ON batch.created_id = c.id
 264     WHERE {$whereClause}
 265     {$orderBy}
 266     {$limit}";
 267
 268     $object = CRM_Core_DAO::executeQuery($query, $params, TRUE, 'CRM_Batch_DAO_Batch');
 269     if (!empty($params['context'])) {
 270       $links = self::links($params['context']);
 271     }
 272     else {
 273       $links = self::links();
 274     }
 275
 276     $batchTypes = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'type_id');
 277     $batchStatus = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id');
 278     $batchStatusByName = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
 279     $paymentInstrument = CRM_Contribute_PseudoConstant::paymentInstrument();
 280
 281     $results = array();
 282     while ($object->fetch()) {
 283       $values = array();
 284       $newLinks = $links;
 285       CRM_Core_DAO::storeValues($object, $values);
 286       $action = array_sum(array_keys($newLinks));
 287
 288       if ($values['status_id'] == array_search('Closed', $batchStatusByName) && $params['context'] != 'financialBatch') {
 289         $newLinks = array();
 290       }
 291       elseif ($params['context'] == 'financialBatch') {
 292         $values['check'] =
 293           "<input type='checkbox' id='check_" .
 294           $object->id .
 295           "' name='check_" .
 296           $object->id .
 297           "' value='1'  data-status_id='" .
 298           $values['status_id']."' class='select-row'></input>";
 299
 300         switch ($batchStatusByName[$values['status_id']]) {
 301           case 'Open':
 302             CRM_Utils_Array::remove($newLinks, 'reopen', 'download');
 303             break;
 304           case 'Closed':
 305             CRM_Utils_Array::remove($newLinks, 'close', 'edit', 'download');
 306             break;
 307           case 'Exported':
 308             CRM_Utils_Array::remove($newLinks, 'close', 'edit', 'reopen', 'export');
 309         }
 310       }
 311       if (!empty($values['type_id'])) {
 312         $values['batch_type'] = $batchTypes[$values['type_id']];
 313       }
 314       $values['batch_status'] = $batchStatus[$values['status_id']];
 315       $values['created_by'] = $object->created_by;
 316       $values['payment_instrument'] = '';
 317       if (!empty($object->payment_instrument_id)) {
 318         $values['payment_instrument'] = $paymentInstrument[$object->payment_instrument_id];
 319       }
 320       $tokens = array('id' => $object->id, 'status' => $values['status_id']);
 321       if ($values['status_id'] == array_search('Exported', $batchStatusByName)) {
 322         $aid = CRM_Core_OptionGroup::getValue('activity_type','Export Accounting Batch');
 323         $activityParams = array('source_record_id' => $object->id, 'activity_type_id' => $aid);
 324         $exportActivity = CRM_Activity_BAO_Activity::retrieve($activityParams, $val);
 325         $fid = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_EntityFile', $exportActivity->id, 'file_id', 'entity_id');
 326         $tokens = array_merge(array('eid' => $exportActivity->id, 'fid' => $fid), $tokens);
 327       }
 328       $values['action'] = CRM_Core_Action::formLink(
 329         $newLinks,
 330         $action,
 331         $tokens,
 332         ts('more'),
 333         FALSE,
 334         'batch.selector.row',
 335         'Batch',
 336         $object->id
 337       );
 338       $results[$object->id] = $values;
 339     }
 340
 341     return $results;
 342   }
 343
 344   /**
 345    * Get count of batches
 346    *
 347    * @param  array   $params associated array for params
 348    * @access public
 349    */
 350   public static function getBatchCount(&$params) {
 351     $args = array();
 352     $whereClause = self::whereClause($params, $args);
 353     $query = " SELECT COUNT(*) FROM civicrm_batch batch
 354       INNER JOIN civicrm_contact c ON batch.created_id = c.id
 355       WHERE {$whereClause}";
 356     return CRM_Core_DAO::singleValueQuery($query);
 357   }
 358
 359   /**
 360    * Format where clause for getting lists of batches
 361    *
 362    * @param  array   $params associated array for params
 363    * @access public
 364    */
 365   public static function whereClause($params) {
 366     $clauses = array();
 367     // Exclude data-entry batches
 368     $batchStatus = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
 369     if (empty($params['status_id'])) {
 370       $clauses[] = 'batch.status_id <> ' . array_search('Data Entry', $batchStatus);
 371     }
 372
 373     $fields = array(
 374       'title' => 'String',
 375       'sort_name' => 'String',
 376       'status_id' => 'Integer',
 377       'payment_instrument_id' => 'Integer',
 378       'item_count' => 'Integer',
 379       'total' => 'Float',
 380     );
 381
 382     foreach ($fields as $field => $type) {
 383       $table = $field == 'sort_name' ? 'c' : 'batch';
 384       if (isset($params[$field])) {
 385         $value = CRM_Utils_Type::escape($params[$field], $type, FALSE);
 386         if ($value && $type == 'String') {
 387           $clauses[] = "$table.$field LIKE '%$value%'";
 388         }
 389         elseif ($value && $type == 'Float') {
 390           $clauses[] = "$table.$field = '$value'";
 391         }
 392         elseif ($value) {
 393           if ($field == 'status_id' && $value == array_search('Open', $batchStatus)) {
 394             $clauses[] = "$table.$field IN ($value," . array_search('Reopened', $batchStatus) . ')';
 395           }
 396           else {
 397             $clauses[] = "$table.$field = $value";
 398           }
 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 all batches
 487    * excluding batches with data entry in progress
 488    */
 489   static function getBatches() {
 490     $dataEntryStatusId = CRM_Core_OptionGroup::getValue('batch_status','Data Entry', 'name');
 491     $query = "SELECT id, title
 492       FROM civicrm_batch
 493       WHERE item_count >= 1
 494       AND status_id != {$dataEntryStatusId}
 495       ORDER BY id DESC";
 496
 497     $batches = array();
 498     $dao = CRM_Core_DAO::executeQuery($query);
 499     while ( $dao->fetch( ) ) {
 500       $batches[$dao->id] = $dao->title;
 501     }
 502     return $batches;
 503   }
 504
 505
 506
 507   /**
 508    * Calculate sum of all entries in a batch
 509    * Used to validate and update item_count and total when closing an accounting batch
 510    *
 511    * @param array $batchIds
 512    * @return array
 513    */
 514   static function batchTotals($batchIds) {
 515     $totals = array_fill_keys($batchIds, array('item_count' => 0, 'total' => 0));
 516     if ($batchIds) {
 517       $sql = "SELECT eb.batch_id, COUNT(tx.id) AS item_count, SUM(tx.total_amount) AS total
 518       FROM civicrm_entity_batch eb
 519       INNER JOIN civicrm_financial_trxn tx ON tx.id = eb.entity_id AND eb.entity_table = 'civicrm_financial_trxn'
 520       WHERE eb.batch_id IN (" . implode(',', $batchIds) . ")
 521       GROUP BY eb.batch_id";
 522       $dao = CRM_Core_DAO::executeQuery($sql);
 523       while ($dao->fetch()) {
 524         $totals[$dao->batch_id] = (array) $dao;
 525       }
 526       $dao->free();
 527     }
 528     return $totals;
 529   }
 530
 531   /**
 532    * Format markup for comparing two totals
 533    *
 534    * @param $actual: calculated total
 535    * @param $expected: user-entered total
 536    * @return array
 537    */
 538   static function displayTotals($actual, $expected) {
 539     $class = 'actual-value';
 540     if ($expected && $expected != $actual) {
 541       $class .= ' crm-error';
 542     }
 543     $actualTitle = ts('Current Total');
 544     $output = "<span class='$class' title='$actualTitle'>$actual</span>";
 545     if ($expected) {
 546       $expectedTitle = ts('Expected Total');
 547       $output .= " / <span class='expected-value' title='$expectedTitle'>$expected</span>";
 548     }
 549     return $output;
 550   }
 551
 552   /**
 553    * Function for exporting financial accounts, currently we support CSV and IIF format
 554    * @see http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
 555    *
 556    * @param array  $batchIds associated array of batch ids
 557    * @param string $exportFormat export format
 558    *
 559    * @return void
 560    *
 561    * @static
 562    * @access public
 563    */
 564   static function exportFinancialBatch($batchIds, $exportFormat) {
 565     if (empty($batchIds)) {
 566       CRM_Core_Error::fatal(ts('No batches were selected.'));
 567       return;
 568     }
 569     if (empty($exportFormat)) {
 570       CRM_Core_Error::fatal(ts('No export format selected.'));
 571       return;
 572     }
 573     self::$_exportFormat = $exportFormat;
 574
 575     // Instantiate appropriate exporter based on user-selected format.
 576     $exporterClass = "CRM_Financial_BAO_ExportFormat_" . self::$_exportFormat;
 577     if ( class_exists( $exporterClass ) ) {
 578       $exporter = new $exporterClass();
 579     }
 580     else {
 581       CRM_Core_Error::fatal("Could not locate exporter: $exporterClass");
 582     }
 583     switch (self::$_exportFormat) {
 584       case 'CSV':
 585         foreach ($batchIds as $batchId) {
 586           $export[$batchId] = $exporter->generateExportQuery($batchId);
 587         }
 588         $exporter->makeCSV($export);
 589         break;
 590
 591       case 'IIF':
 592         foreach ($batchIds as $batchId) {
 593           $export[$batchId] = $exporter->generateExportQuery($batchId);
 594         }
 595         $exporter->makeIIF($export);
 596         break;
 597     }
 598   }
 599
 600   static function closeReOpen($batchIds = array(), $status) {
 601     $batchStatus = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id');
 602     $params['status_id'] = CRM_Utils_Array::key( $status, $batchStatus );
 603     $session = CRM_Core_Session::singleton( );
 604     $params['modified_date'] = date('YmdHis');
 605     $params['modified_id'] = $session->get( 'userID' );
 606     foreach ($batchIds as $key => $value) {
 607       $params['id'] = $ids['batchID'] = $value;
 608       self::create($params, $ids);
 609     }
 610     $url = CRM_Utils_System::url('civicrm/financial/financialbatches',"reset=1&batchStatus={$params['status_id']}");
 611     CRM_Utils_System::redirect($url);
 612   }
 613
 614   /**
 615    * Function to retrieve financial items assigned for a batch
 616    *
 617    * @param int $entityID
 618    * @param array $returnValues
 619    * @param null $notPresent
 620    * @param null $params
 621    * @return Object
 622    */
 623   static function getBatchFinancialItems($entityID, $returnValues, $notPresent = NULL, $params = NULL, $getCount = FALSE) {
 624     if (!$getCount) {
 625       if (!empty($params['rowCount']) &&
 626         $params['rowCount'] > 0
 627       ) {
 628         $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
 629       }
 630     }
 631     // action is taken depending upon the mode
 632     $select = 'civicrm_financial_trxn.id ';
 633     if (!empty( $returnValues)) {
 634       $select .= " , ".implode(' , ', $returnValues);
 635     }
 636
 637     $orderBy = " ORDER BY civicrm_financial_trxn.id";
 638     if (!empty($params['sort'])) {
 639       $orderBy = ' ORDER BY ' . CRM_Utils_Type::escape($params['sort'], 'String');
 640     }
 641
 642     $from = "civicrm_financial_trxn
 643 LEFT JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id
 644 LEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_id = civicrm_financial_trxn.id
 645 LEFT JOIN civicrm_contribution ON civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id
 646 LEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id
 647 LEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id
 648 LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id
 649 ";
 650
 651     $searchFields =
 652       array(
 653         'sort_name',
 654         'financial_type_id',
 655         'contribution_page_id',
 656         'contribution_payment_instrument_id',
 657         'contribution_transaction_id',
 658         'contribution_source',
 659         'contribution_currency_type',
 660         'contribution_pay_later',
 661         'contribution_recurring',
 662         'contribution_test',
 663         'contribution_thankyou_date_is_not_null',
 664         'contribution_receipt_date_is_not_null',
 665         'contribution_pcp_made_through_id',
 666         'contribution_pcp_display_in_roll',
 667         'contribution_date_relative',
 668         'contribution_amount_low',
 669         'contribution_amount_high',
 670         'contribution_in_honor_of',
 671         'contact_tags',
 672         'group',
 673         'contribution_date_relative',
 674         'contribution_date_high',
 675         'contribution_date_low',
 676         'contribution_check_number',
 677         'contribution_status_id',
 678       );
 679     $values = array();
 680     foreach ($searchFields as $field) {
 681       if (isset($params[$field])) {
 682         $values[$field] = $params[$field];
 683         if ($field == 'sort_name') {
 684           $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id
 685           LEFT JOIN civicrm_email ON contact_b.id = civicrm_email.contact_id";
 686         }
 687         if ($field == 'contribution_in_honor_of') {
 688           $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id";
 689         }
 690         if ($field == 'contact_tags') {
 691           $from .= " LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-{$params[$field]}` ON `civicrm_entity_tag-{$params[$field]}`.entity_id = contact_a.id";
 692         }
 693         if ($field == 'group') {
 694           $from .= " LEFT JOIN civicrm_group_contact `civicrm_group_contact-{$params[$field]}` ON contact_a.id = `civicrm_group_contact-{$params[$field]}`.contact_id ";
 695         }
 696         if ($field == 'contribution_date_relative') {
 697           $relativeDate = explode('.', $params[$field]);
 698           $date = CRM_Utils_Date::relativeToAbsolute($relativeDate[0], $relativeDate[1]);
 699           $values['contribution_date_low'] = $date['from'];
 700           $values['contribution_date_high'] = $date['to'];
 701         }
 702         $searchParams = CRM_Contact_BAO_Query::convertFormValues($values);
 703         $query = new CRM_Contact_BAO_Query($searchParams,
 704           CRM_Contribute_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_CONTRIBUTE,
 705             FALSE
 706           ),NULL, FALSE, FALSE,CRM_Contact_BAO_Query::MODE_CONTRIBUTE
 707         );
 708         if ($field == 'contribution_date_high' || $field == 'contribution_date_low') {
 709           $query->dateQueryBuilder($params[$field], 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date');
 710         }
 711       }
 712     }
 713     if (!empty($query->_where[0])) {
 714       $where = implode(' AND ', $query->_where[0]) .
 715         " AND civicrm_entity_batch.batch_id IS NULL
 716          AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'";
 717       $searchValue = TRUE;
 718     }
 719     else {
 720       $searchValue = FALSE;
 721     }
 722
 723     if (!$searchValue) {
 724       if (!$notPresent) {
 725         $where =  " ( civicrm_entity_batch.batch_id = {$entityID}
 726         AND civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
 727         AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution') ";
 728       }
 729       else {
 730         $where = " ( civicrm_entity_batch.batch_id IS NULL
 731         AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')";
 732       }
 733     }
 734
 735     $sql = "
 736 SELECT {$select}
 737 FROM   {$from}
 738 WHERE  {$where}
 739        {$orderBy}
 740 ";
 741
 742     if (isset($limit)) {
 743       $sql .= "{$limit}";
 744     }
 745
 746     $result = CRM_Core_DAO::executeQuery($sql);
 747     return $result;
 748   }
 749
 750   /**
 751    * function to get batch names
 752    * @param string $batchIds
 753    *
 754    * @return array array of batches
 755    */
 756   static function getBatchNames($batchIds) {
 757     $query = 'SELECT id, title
 758       FROM civicrm_batch
 759       WHERE id IN ('. $batchIds . ')';
 760
 761     $batches = array();
 762     $dao = CRM_Core_DAO::executeQuery($query);
 763     while ( $dao->fetch( ) ) {
 764       $batches[$dao->id] = $dao->title;
 765     }
 766     return $batches;
 767   }
 768
 769   /**
 770    * Function get batch statuses
 771    *
 772    * @param string $batchIds
 773    *
 774    * @return array array of batches
 775    */
 776   static function getBatchStatuses($batchIds) {
 777     $query = 'SELECT id, status_id
 778       FROM civicrm_batch
 779       WHERE id IN ('.$batchIds.')';
 780
 781     $batches = array();
 782     $dao = CRM_Core_DAO::executeQuery($query);
 783     while ( $dao->fetch( ) ) {
 784       $batches[$dao->id] = $dao->status_id;
 785     }
 786     return $batches;
 787   }
 788 }