CRM/Batch/BAO/Batch.php

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