CRM/Batch/BAO/Batch.php

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