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         if ($exportActivity) {
 338           $fid = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_EntityFile', $exportActivity->id, 'file_id', 'entity_id');
 339           $fileHash = CRM_Core_BAO_File::generateFileHash($exportActivity->id, $fid);
 340           $tokens = array_merge(['eid' => $exportActivity->id, 'fid' => $fid, 'fcs' => $fileHash], $tokens);
 341         }
 342         else {
 343           CRM_Utils_Array::remove($newLinks, 'export', 'download');
 344         }
 345       }
 346       $values['action'] = CRM_Core_Action::formLink(
 347         $newLinks,
 348         $action,
 349         $tokens,
 350         ts('more'),
 351         FALSE,
 352         'batch.selector.row',
 353         'Batch',
 354         $values['id']
 355       );
 356       // CRM-21205
 357       $values['currency'] = CRM_Core_DAO::singleValueQuery("
 358         SELECT GROUP_CONCAT(DISTINCT ft.currency)
 359         FROM  civicrm_batch batch
 360         JOIN civicrm_entity_batch eb
 361           ON batch.id = eb.batch_id
 362         JOIN civicrm_financial_trxn ft
 363           ON eb.entity_id = ft.id
 364         WHERE batch.id = %1
 365         GROUP BY batch.id
 366       ", [1 => [$values['id'], 'Positive']]);
 367       $results[$values['id']] = $values;
 368     }
 369
 370     return $results;
 371   }
 372
 373   /**
 374    * Get count of batches.
 375    *
 376    * @param array $params
 377    *   Associated array for params.
 378    *
 379    * @return null|string
 380    */
 381   public static function getBatchCount(&$params) {
 382     $apiParams = self::whereClause($params);
 383     return civicrm_api3('Batch', 'getCount', $apiParams);
 384   }
 385
 386   /**
 387    * Format where clause for getting lists of batches.
 388    *
 389    * @param array $params
 390    *   Associated array for params.
 391    *
 392    * @return string
 393    */
 394   public static function whereClause($params) {
 395     $clauses = [];
 396     // Exclude data-entry batches
 397     $batchStatus = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id', ['labelColumn' => 'name']);
 398     if (empty($params['status_id'])) {
 399       $clauses['status_id'] = ['NOT IN' => ["Data Entry"]];
 400     }
 401
 402     $return = [
 403       "id",
 404       "name",
 405       "title",
 406       "description",
 407       "created_date",
 408       "status_id",
 409       "modified_id",
 410       "modified_date",
 411       "type_id",
 412       "mode_id",
 413       "total",
 414       "item_count",
 415       "exported_date",
 416       "payment_instrument_id",
 417       "created_id.sort_name",
 418       "created_id",
 419     ];
 420     if (!CRM_Core_Permission::check("view all manual batches")) {
 421       if (CRM_Core_Permission::check("view own manual batches")) {
 422         $loggedInContactId = CRM_Core_Session::singleton()->get('userID');
 423         $params['created_id'] = $loggedInContactId;
 424       }
 425       else {
 426         $params['created_id'] = 0;
 427       }
 428     }
 429     foreach ($return as $field) {
 430       if (!isset($params[$field])) {
 431         continue;
 432       }
 433       $value = CRM_Utils_Type::escape($params[$field], 'String', FALSE);
 434       if (in_array($field, ['name', 'title', 'description', 'created_id.sort_name'])) {
 435         $clauses[$field] = ['LIKE' => "%{$value}%"];
 436       }
 437       elseif ($field == 'status_id' && $value == array_search('Open', $batchStatus)) {
 438         $clauses['status_id'] = ['IN' => ["Open", 'Reopened']];
 439       }
 440       else {
 441         $clauses[$field] = $value;
 442       }
 443     }
 444     return $clauses;
 445   }
 446
 447   /**
 448    * Define action links.
 449    *
 450    * @param null $context
 451    *
 452    * @return array
 453    *   array of action links
 454    */
 455   public function links($context = NULL) {
 456     if ($context == 'financialBatch') {
 457       $links = [
 458         'transaction' => [
 459           'name' => ts('Transactions'),
 460           'url' => 'civicrm/batchtransaction',
 461           'qs' => 'reset=1&bid=%%id%%',
 462           'title' => ts('View/Add Transactions to Batch'),
 463         ],
 464         'edit' => [
 465           'name' => ts('Edit'),
 466           'url' => 'civicrm/financial/batch',
 467           'qs' => 'reset=1&action=update&id=%%id%%&context=1',
 468           'title' => ts('Edit Batch'),
 469         ],
 470         'close' => [
 471           'name' => ts('Close'),
 472           'title' => ts('Close Batch'),
 473           'url' => '#',
 474           'extra' => 'rel="close"',
 475         ],
 476         'export' => [
 477           'name' => ts('Export'),
 478           'title' => ts('Export Batch'),
 479           'url' => '#',
 480           'extra' => 'rel="export"',
 481         ],
 482         'reopen' => [
 483           'name' => ts('Re-open'),
 484           'title' => ts('Re-open Batch'),
 485           'url' => '#',
 486           'extra' => 'rel="reopen"',
 487         ],
 488         'delete' => [
 489           'name' => ts('Delete'),
 490           'title' => ts('Delete Batch'),
 491           'url' => '#',
 492           'extra' => 'rel="delete"',
 493         ],
 494         'download' => [
 495           'name' => ts('Download'),
 496           'url' => 'civicrm/file',
 497           'qs' => 'reset=1&id=%%fid%%&eid=%%eid%%&fcs=%%fcs%%',
 498           'title' => ts('Download Batch'),
 499         ],
 500       ];
 501     }
 502     else {
 503       $links = [
 504         CRM_Core_Action::COPY => [
 505           'name' => ts('Enter records'),
 506           'url' => 'civicrm/batch/entry',
 507           'qs' => 'id=%%id%%&reset=1',
 508           'title' => ts('Batch Data Entry'),
 509         ],
 510         CRM_Core_Action::UPDATE => [
 511           'name' => ts('Edit'),
 512           'url' => 'civicrm/batch',
 513           'qs' => 'action=update&id=%%id%%&reset=1',
 514           'title' => ts('Edit Batch'),
 515         ],
 516         CRM_Core_Action::DELETE => [
 517           'name' => ts('Delete'),
 518           'url' => 'civicrm/batch',
 519           'qs' => 'action=delete&id=%%id%%',
 520           'title' => ts('Delete Batch'),
 521         ],
 522       ];
 523     }
 524     return $links;
 525   }
 526
 527   /**
 528    * Get batch list.
 529    *
 530    * @return array
 531    *   all batches excluding batches with data entry in progress
 532    */
 533   public static function getBatches() {
 534     $dataEntryStatusId = CRM_Core_PseudoConstant::getKey('CRM_Batch_BAO_Batch', 'status_id', 'Data Entry');
 535     $query = "SELECT id, title
 536       FROM civicrm_batch
 537       WHERE item_count >= 1
 538       AND status_id != {$dataEntryStatusId}
 539       ORDER BY title";
 540
 541     $batches = [];
 542     $dao = CRM_Core_DAO::executeQuery($query);
 543     while ($dao->fetch()) {
 544       $batches[$dao->id] = $dao->title;
 545     }
 546     return $batches;
 547   }
 548
 549   /**
 550    * Calculate sum of all entries in a batch.
 551    * Used to validate and update item_count and total when closing an accounting batch
 552    *
 553    * @param array $batchIds
 554    * @return array
 555    */
 556   public static function batchTotals($batchIds) {
 557     $totals = array_fill_keys($batchIds, ['item_count' => 0, 'total' => 0]);
 558     if ($batchIds) {
 559       $sql = "SELECT eb.batch_id, COUNT(tx.id) AS item_count, SUM(tx.total_amount) AS total
 560       FROM civicrm_entity_batch eb
 561       INNER JOIN civicrm_financial_trxn tx ON tx.id = eb.entity_id AND eb.entity_table = 'civicrm_financial_trxn'
 562       WHERE eb.batch_id IN (" . implode(',', $batchIds) . ")
 563       GROUP BY eb.batch_id";
 564       $dao = CRM_Core_DAO::executeQuery($sql);
 565       while ($dao->fetch()) {
 566         $totals[$dao->batch_id] = (array) $dao;
 567       }
 568     }
 569     return $totals;
 570   }
 571
 572   /**
 573    * Format markup for comparing two totals.
 574    *
 575    * @param $actual
 576    *   calculated total
 577    * @param $expected
 578    *   user-entered total
 579    * @return array
 580    */
 581   public static function displayTotals($actual, $expected) {
 582     $class = 'actual-value';
 583     if ($expected && $expected != $actual) {
 584       $class .= ' crm-error';
 585     }
 586     $actualTitle = ts('Current Total');
 587     $output = "<span class='$class' title='$actualTitle'>$actual</span>";
 588     if ($expected) {
 589       $expectedTitle = ts('Expected Total');
 590       $output .= " / <span class='expected-value' title='$expectedTitle'>$expected</span>";
 591     }
 592     return $output;
 593   }
 594
 595   /**
 596    * Function for exporting financial accounts, currently we support CSV and IIF format
 597    * @see http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
 598    *
 599    * @param array $batchIds
 600    *   Associated array of batch ids.
 601    * @param string $exportFormat
 602    *   Export format.
 603    * @param bool $downloadFile
 604    *   Download export file?.
 605    */
 606   public static function exportFinancialBatch($batchIds, $exportFormat, $downloadFile) {
 607     if (empty($batchIds)) {
 608       CRM_Core_Error::fatal(ts('No batches were selected.'));
 609       return;
 610     }
 611     if (empty($exportFormat)) {
 612       CRM_Core_Error::fatal(ts('No export format selected.'));
 613       return;
 614     }
 615     self::$_exportFormat = $exportFormat;
 616
 617     // Instantiate appropriate exporter based on user-selected format.
 618     $exporterClass = "CRM_Financial_BAO_ExportFormat_" . self::$_exportFormat;
 619     if (class_exists($exporterClass)) {
 620       $exporter = new $exporterClass();
 621     }
 622     else {
 623       CRM_Core_Error::fatal("Could not locate exporter: $exporterClass");
 624     }
 625     $export = [];
 626     $exporter->_isDownloadFile = $downloadFile;
 627     foreach ($batchIds as $batchId) {
 628       // export only batches whose status is set to Exported.
 629       $result = civicrm_api3('Batch', 'getcount', [
 630         'id' => $batchId,
 631         'status_id' => "Exported",
 632       ]);
 633       if (!$result) {
 634         continue;
 635       }
 636       $export[$batchId] = $exporter->generateExportQuery($batchId);
 637     }
 638     if ($export) {
 639       $exporter->makeExport($export);
 640     }
 641   }
 642
 643   /**
 644    * @param array $batchIds
 645    * @param $status
 646    */
 647   public static function closeReOpen($batchIds = [], $status) {
 648     $batchStatus = CRM_Core_PseudoConstant::get('CRM_Batch_DAO_Batch', 'status_id');
 649     $params['status_id'] = CRM_Utils_Array::key($status, $batchStatus);
 650     $session = CRM_Core_Session::singleton();
 651     $params['modified_date'] = date('YmdHis');
 652     $params['modified_id'] = $session->get('userID');
 653     foreach ($batchIds as $key => $value) {
 654       $params['id'] = $ids['batchID'] = $value;
 655       self::create($params, $ids);
 656     }
 657     $url = CRM_Utils_System::url('civicrm/financial/financialbatches', "reset=1&batchStatus={$params['status_id']}");
 658     CRM_Utils_System::redirect($url);
 659   }
 660
 661   /**
 662    * Retrieve financial items assigned for a batch.
 663    *
 664    * @param int $entityID
 665    * @param array $returnValues
 666    * @param bool $notPresent
 667    * @param array $params
 668    * @param bool $getCount
 669    *
 670    * @return CRM_Core_DAO
 671    */
 672   public static function getBatchFinancialItems($entityID, $returnValues, $notPresent = NULL, $params = NULL, $getCount = FALSE) {
 673     if (!$getCount) {
 674       if (!empty($params['rowCount']) &&
 675         $params['rowCount'] > 0
 676       ) {
 677         $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
 678       }
 679     }
 680     // action is taken depending upon the mode
 681     $select = 'civicrm_financial_trxn.id ';
 682     if (!empty($returnValues)) {
 683       $select .= " , " . implode(' , ', $returnValues);
 684     }
 685
 686     $orderBy = " ORDER BY civicrm_financial_trxn.id";
 687     if (!empty($params['sort'])) {
 688       $orderBy = ' ORDER BY ' . CRM_Utils_Type::escape($params['sort'], 'String');
 689     }
 690
 691     $from = "civicrm_financial_trxn
 692 INNER JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id
 693 INNER JOIN civicrm_contribution ON (civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id
 694   AND civicrm_entity_financial_trxn.entity_table='civicrm_contribution')
 695 LEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
 696 AND civicrm_entity_batch.entity_id = civicrm_financial_trxn.id
 697 LEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id
 698 LEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id
 699 LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id
 700 ";
 701
 702     $searchFields = [
 703       'sort_name',
 704       'financial_type_id',
 705       'contribution_page_id',
 706       'contribution_payment_instrument_id',
 707       'contribution_trxn_id',
 708       'contribution_source',
 709       'contribution_currency_type',
 710       'contribution_pay_later',
 711       'contribution_recurring',
 712       'contribution_test',
 713       'contribution_thankyou_date_is_not_null',
 714       'contribution_receipt_date_is_not_null',
 715       'contribution_pcp_made_through_id',
 716       'contribution_pcp_display_in_roll',
 717       'contribution_amount_low',
 718       'contribution_amount_high',
 719       'contribution_in_honor_of',
 720       'contact_tags',
 721       'group',
 722       'receive_date_relative',
 723       'receive_date_high',
 724       'receive_date_low',
 725       'contribution_check_number',
 726       'contribution_status_id',
 727       'financial_trxn_card_type_id',
 728       'financial_trxn_pan_truncation',
 729     ];
 730     $values = [];
 731     foreach ($searchFields as $field) {
 732       if (isset($params[$field])) {
 733         $values[$field] = $params[$field];
 734         if ($field == 'sort_name') {
 735           $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id
 736           LEFT JOIN civicrm_email ON contact_b.id = civicrm_email.contact_id";
 737         }
 738         if ($field == 'contribution_in_honor_of') {
 739           $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id";
 740         }
 741         if ($field == 'contact_tags') {
 742           $from .= " LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-{$params[$field]}` ON `civicrm_entity_tag-{$params[$field]}`.entity_id = contact_a.id";
 743         }
 744         if ($field == 'group') {
 745           $from .= " LEFT JOIN civicrm_group_contact `civicrm_group_contact-{$params[$field]}` ON contact_a.id = `civicrm_group_contact-{$params[$field]}`.contact_id ";
 746         }
 747         if ($field == 'receive_date_relative') {
 748           $relativeDate = explode('.', $params[$field]);
 749           $date = CRM_Utils_Date::relativeToAbsolute($relativeDate[0], $relativeDate[1]);
 750           $values['receive_date_low'] = $date['from'];
 751           $values['receive_date_high'] = $date['to'];
 752         }
 753       }
 754     }
 755
 756     $searchParams = CRM_Contact_BAO_Query::convertFormValues(
 757       $values,
 758       0,
 759       FALSE,
 760       NULL,
 761       [
 762         'financial_type_id',
 763         'contribution_soft_credit_type_id',
 764         'contribution_status_id',
 765         'contribution_page_id',
 766         'financial_trxn_card_type_id',
 767         'contribution_payment_instrument_id',
 768       ]
 769     );
 770     // @todo the use of defaultReturnProperties means the search will be inefficient
 771     // as slow-unneeded properties are included.
 772     $query = new CRM_Contact_BAO_Query($searchParams,
 773       CRM_Contribute_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_CONTRIBUTE,
 774         FALSE
 775       ), NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_CONTRIBUTE
 776     );
 777
 778     if (!empty($query->_where[0])) {
 779       $where = implode(' AND ', $query->_where[0]) .
 780         " AND civicrm_entity_batch.batch_id IS NULL ";
 781       $where = str_replace('civicrm_contribution.payment_instrument_id', 'civicrm_financial_trxn.payment_instrument_id', $where);
 782     }
 783     else {
 784       if (!$notPresent) {
 785         $where = " civicrm_entity_batch.batch_id = {$entityID} ";
 786       }
 787       else {
 788         $where = " civicrm_entity_batch.batch_id IS NULL ";
 789       }
 790     }
 791
 792     $sql = "
 793 SELECT {$select}
 794 FROM   {$from}
 795 WHERE  {$where}
 796        {$orderBy}
 797 ";
 798
 799     if (isset($limit)) {
 800       $sql .= "{$limit}";
 801     }
 802
 803     $result = CRM_Core_DAO::executeQuery($sql);
 804     return $result;
 805   }
 806
 807   /**
 808    * Get batch names.
 809    * @param string $batchIds
 810    *
 811    * @return array
 812    *   array of batches
 813    */
 814   public static function getBatchNames($batchIds) {
 815     $query = 'SELECT id, title
 816       FROM civicrm_batch
 817       WHERE id IN (' . $batchIds . ')';
 818
 819     $batches = [];
 820     $dao = CRM_Core_DAO::executeQuery($query);
 821     while ($dao->fetch()) {
 822       $batches[$dao->id] = $dao->title;
 823     }
 824     return $batches;
 825   }
 826
 827   /**
 828    * Function get batch statuses.
 829    *
 830    * @param string $batchIds
 831    *
 832    * @return array
 833    *   array of batches
 834    */
 835   public static function getBatchStatuses($batchIds) {
 836     $query = 'SELECT id, status_id
 837       FROM civicrm_batch
 838       WHERE id IN (' . $batchIds . ')';
 839
 840     $batches = [];
 841     $dao = CRM_Core_DAO::executeQuery($query);
 842     while ($dao->fetch()) {
 843       $batches[$dao->id] = $dao->status_id;
 844     }
 845     return $batches;
 846   }
 847
 848   /**
 849    * Function to check permission for batch.
 850    *
 851    * @param string $action
 852    * @param int $batchCreatedId
 853    *   batch created by contact id
 854    *
 855    * @return bool
 856    */
 857   public static function checkBatchPermission($action, $batchCreatedId = NULL) {
 858     if (CRM_Core_Permission::check("{$action} all manual batches")) {
 859       return TRUE;
 860     }
 861     if (CRM_Core_Permission::check("{$action} own manual batches")) {
 862       $loggedInContactId = CRM_Core_Session::singleton()->get('userID');
 863       if ($batchCreatedId == $loggedInContactId) {
 864         return TRUE;
 865       }
 866       elseif (CRM_Utils_System::isNull($batchCreatedId)) {
 867         return TRUE;
 868       }
 869     }
 870     return FALSE;
 871   }
 872
 873 }