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