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