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