3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
21 class CRM_Batch_BAO_Batch
extends CRM_Batch_DAO_Batch
{
24 * Cache for the current batch object.
27 public static $_batch = NULL;
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.
34 public static $_exportFormat = NULL;
39 * @param array $params
45 public static function create(&$params) {
46 if (empty($params['id']) && empty($params['name'])) {
47 $params['name'] = CRM_Utils_String
::titleToVar($params['title'] ??
'batch_ref_' . random_int(0, 100000));
49 return self
::writeRecord($params);
53 * Retrieve DB object and copy to defaults array.
55 * @param array $params
56 * Array of criteria values.
57 * @param array|null $defaults
58 * Array to be populated with found values.
61 * The DAO object, if found.
65 public static function retrieve(array $params, ?
array &$defaults = NULL) {
66 $defaults = $defaults ??
[];
67 return self
::commonRetrieve(self
::class, $params, $defaults);
71 * Get profile id associated with the batch type.
73 * @param int $batchTypeId
77 * $profileId profile id
79 public static function getProfileId($batchTypeId) {
80 //retrieve the profile specific to batch type
81 switch ($batchTypeId) {
84 //batch profile used for pledges
85 $profileName = "contribution_batch_entry";
89 //batch profile used for memberships
90 $profileName = "membership_batch_entry";
94 // get and return the profile id
95 return CRM_Core_DAO
::getFieldValue('CRM_Core_BAO_UFGroup', $profileName, 'id', 'name');
99 * Generate batch name.
104 public static function generateBatchName() {
105 $sql = "SELECT max(id) FROM civicrm_batch";
106 $batchNo = CRM_Core_DAO
::singleValueQuery($sql) +
1;
107 return ts('Batch %1', [1 => $batchNo]) . ': ' . date('Y-m-d');
111 * Delete batch entry.
113 * @param int $batchId
118 public static function deleteBatch($batchId) {
119 // delete entry from batch table
120 CRM_Utils_Hook
::pre('delete', 'Batch', $batchId);
121 $batch = new CRM_Batch_DAO_Batch();
122 $batch->id
= $batchId;
124 CRM_Utils_Hook
::post('delete', 'Batch', $batch->id
, $batch);
129 * wrapper for ajax batch selector.
131 * @param array $params
132 * Associated array for params record id.
135 * associated array of batch list
137 public static function getBatchListSelector(&$params) {
139 $params['offset'] = ($params['page'] - 1) * $params['rp'];
140 $params['rowCount'] = $params['rp'];
141 $params['sort'] = $params['sortBy'] ??
NULL;
144 $batches = self
::getBatchList($params);
146 // get batch totals for open batches
148 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id', ['labelColumn' => 'name']);
150 array_search('Open', $batchStatus),
151 array_search('Reopened', $batchStatus),
153 if ($params['context'] == 'financialBatch') {
154 foreach ($batches as $id => $batch) {
155 if (in_array($batch['status_id'], $batchStatus)) {
156 $fetchTotals[] = $id;
160 $totals = self
::batchTotals($fetchTotals);
163 $params['total'] = self
::getBatchCount($params);
165 // format params and add links
168 foreach ($batches as $id => $value) {
170 if ($params['context'] == 'financialBatch') {
171 $batch['check'] = $value['check'];
173 $batch['batch_name'] = $value['title'];
174 $batch['total'] = '';
175 $batch['payment_instrument'] = $value['payment_instrument'];
176 $batch['item_count'] = $value['item_count'] ??
NULL;
177 $batch['type'] = $value['batch_type'] ??
NULL;
178 if (!empty($value['total'])) {
180 $batch['total'] = CRM_Utils_Money
::format($value['total'], $value['currency']);
183 // Compare totals with actuals
184 if (isset($totals[$id])) {
185 $batch['item_count'] = self
::displayTotals($totals[$id]['item_count'], $batch['item_count']);
186 $batch['total'] = self
::displayTotals(CRM_Utils_Money
::format($totals[$id]['total']), $batch['total']);
188 $batch['status'] = $value['batch_status'];
189 $batch['created_by'] = $value['created_by'];
190 $batch['links'] = $value['action'];
191 $batchList[$id] = $batch;
197 * Get list of batches.
199 * @param array $params
200 * Associated array for params.
204 public static function getBatchList(&$params) {
205 $apiParams = self
::whereClause($params);
207 if (!empty($params['rowCount']) && is_numeric($params['rowCount'])
208 && is_numeric($params['offset']) && $params['rowCount'] > 0
210 $apiParams['options'] = ['offset' => $params['offset'], 'limit' => $params['rowCount']];
212 $apiParams['options']['sort'] = 'id DESC';
213 if (!empty($params['sort'])) {
214 $apiParams['options']['sort'] = CRM_Utils_Type
::escape($params['sort'], 'String');
231 "payment_instrument_id",
232 "created_id.sort_name",
235 $apiParams['return'] = $return;
236 $batches = civicrm_api3('Batch', 'get', $apiParams);
237 $obj = new CRM_Batch_BAO_Batch();
238 if (!empty($params['context'])) {
239 $links = $obj->links($params['context']);
242 $links = $obj->links();
245 $batchTypes = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'type_id');
246 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id');
247 $batchStatusByName = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id', ['labelColumn' => 'name']);
248 $paymentInstrument = CRM_Contribute_PseudoConstant
::paymentInstrument();
251 foreach ($batches['values'] as $values) {
253 $action = array_sum(array_keys($newLinks));
255 if ($values['status_id'] == array_search('Closed', $batchStatusByName) && $params['context'] != 'financialBatch') {
258 elseif ($params['context'] == 'financialBatch') {
259 $values['check'] = "<input type='checkbox' id='check_" .
263 "' value='1' data-status_id='" .
264 $values['status_id'] . "' class='select-row'></input>";
266 switch ($batchStatusByName[$values['status_id']]) {
269 CRM_Utils_Array
::remove($newLinks, 'reopen', 'download');
273 CRM_Utils_Array
::remove($newLinks, 'close', 'edit', 'download');
277 CRM_Utils_Array
::remove($newLinks, 'close', 'edit', 'reopen', 'export');
279 if (!CRM_Batch_BAO_Batch
::checkBatchPermission('edit', $values['created_id'])) {
280 CRM_Utils_Array
::remove($newLinks, 'edit');
282 if (!CRM_Batch_BAO_Batch
::checkBatchPermission('close', $values['created_id'])) {
283 CRM_Utils_Array
::remove($newLinks, 'close', 'export');
285 if (!CRM_Batch_BAO_Batch
::checkBatchPermission('reopen', $values['created_id'])) {
286 CRM_Utils_Array
::remove($newLinks, 'reopen');
288 if (!CRM_Batch_BAO_Batch
::checkBatchPermission('export', $values['created_id'])) {
289 CRM_Utils_Array
::remove($newLinks, 'export', 'download');
291 if (!CRM_Batch_BAO_Batch
::checkBatchPermission('delete', $values['created_id'])) {
292 CRM_Utils_Array
::remove($newLinks, 'delete');
295 if (!empty($values['type_id'])) {
296 $values['batch_type'] = $batchTypes[$values['type_id']];
298 $values['batch_status'] = $batchStatus[$values['status_id']];
299 $values['created_by'] = $values['created_id.sort_name'];
300 $values['payment_instrument'] = '';
301 if (!empty($values['payment_instrument_id'])) {
302 $values['payment_instrument'] = $paymentInstrument[$values['payment_instrument_id']];
304 $tokens = ['id' => $values['id'], 'status' => $values['status_id']];
305 if ($values['status_id'] == array_search('Exported', $batchStatusByName)) {
306 $aid = CRM_Core_PseudoConstant
::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Export Accounting Batch');
307 $activityParams = ['source_record_id' => $values['id'], 'activity_type_id' => $aid];
308 $exportActivity = CRM_Activity_BAO_Activity
::retrieve($activityParams, $val);
309 if ($exportActivity) {
310 $fid = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_EntityFile', $exportActivity->id
, 'file_id', 'entity_id');
311 $fileHash = CRM_Core_BAO_File
::generateFileHash($exportActivity->id
, $fid);
312 $tokens = array_merge(['eid' => $exportActivity->id
, 'fid' => $fid, 'fcs' => $fileHash], $tokens);
315 CRM_Utils_Array
::remove($newLinks, 'export', 'download');
318 $values['action'] = CRM_Core_Action
::formLink(
324 'batch.selector.row',
329 $values['currency'] = CRM_Batch_BAO_EntityBatch
::getBatchCurrency($values['id']);
330 $results[$values['id']] = $values;
337 * Get count of batches.
339 * @param array $params
340 * Associated array for params.
342 * @return null|string
344 public static function getBatchCount(&$params) {
345 $apiParams = self
::whereClause($params);
346 return civicrm_api3('Batch', 'getCount', $apiParams);
350 * Format where clause for getting lists of batches.
352 * @param array $params
353 * Associated array for params.
357 public static function whereClause($params) {
359 // Exclude data-entry batches
360 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id', ['labelColumn' => 'name']);
361 if (empty($params['status_id'])) {
362 $clauses['status_id'] = ['NOT IN' => ["Data Entry"]];
379 "payment_instrument_id",
380 "created_id.sort_name",
383 if (!CRM_Core_Permission
::check("view all manual batches")) {
384 if (CRM_Core_Permission
::check("view own manual batches")) {
385 $loggedInContactId = CRM_Core_Session
::singleton()->get('userID');
386 $params['created_id'] = $loggedInContactId;
389 $params['created_id'] = 0;
392 foreach ($return as $field) {
393 if (!isset($params[$field])) {
396 $value = CRM_Utils_Type
::escape($params[$field], 'String', FALSE);
397 if (in_array($field, ['name', 'title', 'description', 'created_id.sort_name'])) {
398 $clauses[$field] = ['LIKE' => "%{$value}%"];
400 elseif ($field == 'status_id' && $value == array_search('Open', $batchStatus)) {
401 $clauses['status_id'] = ['IN' => ["Open", 'Reopened']];
404 $clauses[$field] = $value;
411 * Define action links.
413 * @param null $context
416 * array of action links
418 public function links($context = NULL) {
419 if ($context == 'financialBatch') {
422 'name' => ts('Transactions'),
423 'url' => 'civicrm/batchtransaction',
424 'qs' => 'reset=1&bid=%%id%%',
425 'title' => ts('View/Add Transactions to Batch'),
428 'name' => ts('Edit'),
429 'url' => 'civicrm/financial/batch',
430 'qs' => 'reset=1&action=update&id=%%id%%&context=1',
431 'title' => ts('Edit Batch'),
434 'name' => ts('Close'),
435 'title' => ts('Close Batch'),
437 'extra' => 'rel="close"',
440 'name' => ts('Export'),
441 'title' => ts('Export Batch'),
442 'url' => 'civicrm/financial/batch/export',
443 'qs' => 'reset=1&id=%%id%%&status=1',
446 'name' => ts('Re-open'),
447 'title' => ts('Re-open Batch'),
449 'extra' => 'rel="reopen"',
452 'name' => ts('Delete'),
453 'title' => ts('Delete Batch'),
455 'extra' => 'rel="delete"',
458 'name' => ts('Download'),
459 'url' => 'civicrm/file',
460 'qs' => 'reset=1&id=%%fid%%&eid=%%eid%%&fcs=%%fcs%%',
461 'title' => ts('Download Batch'),
467 CRM_Core_Action
::COPY
=> [
468 'name' => ts('Enter records'),
469 'url' => 'civicrm/batch/entry',
470 'qs' => 'id=%%id%%&reset=1',
471 'title' => ts('Batch Data Entry'),
473 CRM_Core_Action
::UPDATE
=> [
474 'name' => ts('Edit'),
475 'url' => 'civicrm/batch',
476 'qs' => 'action=update&id=%%id%%&reset=1',
477 'title' => ts('Edit Batch'),
479 CRM_Core_Action
::DELETE
=> [
480 'name' => ts('Delete'),
481 'url' => 'civicrm/batch',
482 'qs' => 'action=delete&id=%%id%%',
483 'title' => ts('Delete Batch'),
494 * all batches excluding batches with data entry in progress
496 public static function getBatches() {
497 $dataEntryStatusId = CRM_Core_PseudoConstant
::getKey('CRM_Batch_BAO_Batch', 'status_id', 'Data Entry');
498 $query = "SELECT id, title
500 WHERE item_count >= 1
501 AND status_id != {$dataEntryStatusId}
505 $dao = CRM_Core_DAO
::executeQuery($query);
506 while ($dao->fetch()) {
507 $batches[$dao->id
] = $dao->title
;
513 * Calculate sum of all entries in a batch.
514 * Used to validate and update item_count and total when closing an accounting batch
516 * @param array $batchIds
519 public static function batchTotals($batchIds) {
520 $totals = array_fill_keys($batchIds, ['item_count' => 0, 'total' => 0]);
522 $sql = "SELECT eb.batch_id, COUNT(tx.id) AS item_count, SUM(tx.total_amount) AS total
523 FROM civicrm_entity_batch eb
524 INNER JOIN civicrm_financial_trxn tx ON tx.id = eb.entity_id AND eb.entity_table = 'civicrm_financial_trxn'
525 WHERE eb.batch_id IN (" . implode(',', $batchIds) . ")
526 GROUP BY eb.batch_id";
527 $dao = CRM_Core_DAO
::executeQuery($sql);
528 while ($dao->fetch()) {
529 $totals[$dao->batch_id
] = (array) $dao;
536 * Format markup for comparing two totals.
544 public static function displayTotals($actual, $expected) {
545 $class = 'actual-value';
546 if ($expected && $expected != $actual) {
547 $class .= ' crm-error';
549 $actualTitle = ts('Current Total');
550 $output = "<span class='$class' title='$actualTitle'>$actual</span>";
552 $expectedTitle = ts('Expected Total');
553 $output .= " / <span class='expected-value' title='$expectedTitle'>$expected</span>";
559 * Function for exporting financial accounts, currently we support CSV and IIF format
560 * @see http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
562 * @param array $batchIds
563 * Associated array of batch ids.
564 * @param string $exportFormat
566 * @param bool $downloadFile
567 * Download export file?.
569 public static function exportFinancialBatch($batchIds, $exportFormat, $downloadFile) {
570 if (empty($batchIds)) {
571 throw new CRM_Core_Exception(ts('No batches were selected.'));
573 if (empty($exportFormat)) {
574 throw new CRM_Core_Exception(ts('No export format selected.'));
576 self
::$_exportFormat = $exportFormat;
578 // Instantiate appropriate exporter based on user-selected format.
579 $exporterClass = "CRM_Financial_BAO_ExportFormat_" . self
::$_exportFormat;
580 if (class_exists($exporterClass)) {
581 $exporter = new $exporterClass();
584 throw new CRM_Core_Exception("Could not locate exporter: $exporterClass");
587 $exporter->_isDownloadFile
= $downloadFile;
588 foreach ($batchIds as $batchId) {
589 // export only batches whose status is set to Exported.
590 $result = civicrm_api3('Batch', 'getcount', [
592 'status_id' => "Exported",
597 $export[$batchId] = $exporter->generateExportQuery($batchId);
600 $exporter->makeExport($export);
605 * @param array $batchIds
608 public static function closeReOpen($batchIds, $status) {
609 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id');
610 $params['status_id'] = CRM_Utils_Array
::key($status, $batchStatus);
611 $session = CRM_Core_Session
::singleton();
612 $params['modified_date'] = date('YmdHis');
613 $params['modified_id'] = $session->get('userID');
614 foreach ($batchIds as $key => $value) {
615 $params['id'] = $ids['batchID'] = $value;
616 self
::create($params, $ids);
618 $url = CRM_Utils_System
::url('civicrm/financial/financialbatches', "reset=1&batchStatus={$params['status_id']}");
619 CRM_Utils_System
::redirect($url);
623 * Retrieve financial items assigned for a batch.
625 * @param int $entityID
626 * @param array $returnValues
627 * @param bool $notPresent
628 * @param array $params
629 * @param bool $getCount
631 * @return CRM_Core_DAO
633 public static function getBatchFinancialItems($entityID, $returnValues, $notPresent = NULL, $params = NULL, $getCount = FALSE) {
635 if (!empty($params['rowCount']) &&
636 $params['rowCount'] > 0
638 $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
641 // action is taken depending upon the mode
642 $select = 'civicrm_financial_trxn.id ';
643 if (!empty($returnValues)) {
644 $select .= " , " . implode(' , ', $returnValues);
647 $orderBy = " ORDER BY civicrm_financial_trxn.id";
648 if (!empty($params['sort'])) {
649 $orderBy = ' ORDER BY ' . CRM_Utils_Type
::escape($params['sort'], 'String');
652 $from = "civicrm_financial_trxn
653 INNER JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id
654 INNER JOIN civicrm_contribution ON (civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id
655 AND civicrm_entity_financial_trxn.entity_table='civicrm_contribution')
656 LEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
657 AND civicrm_entity_batch.entity_id = civicrm_financial_trxn.id
658 LEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id
659 LEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id
660 LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id
666 'contribution_page_id',
667 'contribution_payment_instrument_id',
668 'contribution_trxn_id',
669 'contribution_source',
670 'contribution_currency_type',
671 'contribution_pay_later',
672 'contribution_recurring',
674 'contribution_thankyou_date_is_not_null',
675 'contribution_receipt_date_is_not_null',
676 'contribution_pcp_made_through_id',
677 'contribution_pcp_display_in_roll',
678 'contribution_amount_low',
679 'contribution_amount_high',
680 'contribution_in_honor_of',
683 'receive_date_relative',
686 'contribution_check_number',
687 'contribution_status_id',
688 'financial_trxn_card_type_id',
689 'financial_trxn_pan_truncation',
691 $values = $customJoins = [];
693 // If a custom field was passed as a param,
694 // we'll take it into account.
695 $customSearchFields = [];
696 if (!empty($params)) {
697 foreach ($params as $name => $param) {
698 if (substr($name, 0, 6) == 'custom') {
699 $searchFields[] = $name;
704 foreach ($searchFields as $field) {
705 if (isset($params[$field])) {
706 $values[$field] = $params[$field];
707 if ($field == 'sort_name') {
708 $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id
709 LEFT JOIN civicrm_email ON contact_b.id = civicrm_email.contact_id";
711 if ($field == 'contribution_in_honor_of') {
712 $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id";
714 if ($field == 'contact_tags') {
715 $from .= " LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-{$params[$field]}` ON `civicrm_entity_tag-{$params[$field]}`.entity_id = contact_a.id";
717 if ($field == 'group') {
718 $from .= " LEFT JOIN civicrm_group_contact `civicrm_group_contact-{$params[$field]}` ON contact_a.id = `civicrm_group_contact-{$params[$field]}`.contact_id ";
720 if ($field == 'receive_date_relative') {
721 $relativeDate = explode('.', $params[$field]);
722 $date = CRM_Utils_Date
::relativeToAbsolute($relativeDate[0], $relativeDate[1]);
723 $values['receive_date_low'] = $date['from'];
724 $values['receive_date_high'] = $date['to'];
727 // Add left joins as they're needed to consider
728 // conditions over custom fields.
729 if (substr($field, 0, 6) == 'custom') {
730 $customFieldParams = ['id' => explode('_', $field)[1]];
731 $customFieldDefaults = [];
732 $customField = CRM_Core_BAO_CustomField
::retrieve($customFieldParams, $customFieldDefaults);
734 $customGroupParams = ['id' => $customField->custom_group_id
];
735 $customGroupDefaults = [];
736 $customGroup = CRM_Core_BAO_CustomGroup
::retrieve($customGroupParams, $customGroupDefaults);
738 $columnName = $customField->column_name
;
739 $tableName = $customGroup->table_name
;
741 if (!array_key_exists($tableName, $customJoins)) {
742 $customJoins[$tableName] = "LEFT JOIN $tableName ON $tableName.entity_id = civicrm_contribution.id";
748 $searchParams = CRM_Contact_BAO_Query
::convertFormValues(
755 'contribution_soft_credit_type_id',
756 'contribution_status_id',
757 'contribution_page_id',
758 'financial_trxn_card_type_id',
759 'contribution_payment_instrument_id',
762 // @todo the use of defaultReturnProperties means the search will be inefficient
763 // as slow-unneeded properties are included.
764 $query = new CRM_Contact_BAO_Query($searchParams,
765 CRM_Contribute_BAO_Query
::defaultReturnProperties(CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
,
767 ), NULL, FALSE, FALSE, CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
770 if (count($customJoins) > 0) {
771 $from .= " " . implode(" ", $customJoins);
774 if (!empty($query->_where
[0])) {
775 $where = implode(' AND ', $query->_where
[0]) .
776 " AND civicrm_entity_batch.batch_id IS NULL ";
777 $where = str_replace('civicrm_contribution.payment_instrument_id', 'civicrm_financial_trxn.payment_instrument_id', $where);
781 $where = " civicrm_entity_batch.batch_id = {$entityID} ";
784 $where = " civicrm_entity_batch.batch_id IS NULL ";
799 $result = CRM_Core_DAO
::executeQuery($sql);
805 * @param string $batchIds
810 public static function getBatchNames($batchIds) {
811 $query = 'SELECT id, title
813 WHERE id IN (' . $batchIds . ')';
816 $dao = CRM_Core_DAO
::executeQuery($query);
817 while ($dao->fetch()) {
818 $batches[$dao->id
] = $dao->title
;
824 * Function get batch statuses.
826 * @param string $batchIds
831 public static function getBatchStatuses($batchIds) {
832 $query = 'SELECT id, status_id
834 WHERE id IN (' . $batchIds . ')';
837 $dao = CRM_Core_DAO
::executeQuery($query);
838 while ($dao->fetch()) {
839 $batches[$dao->id
] = $dao->status_id
;
845 * Function to check permission for batch.
847 * @param string $action
848 * @param int $batchCreatedId
849 * batch created by contact id
853 public static function checkBatchPermission($action, $batchCreatedId = NULL) {
854 if (CRM_Core_Permission
::check("{$action} all manual batches")) {
857 if (CRM_Core_Permission
::check("{$action} own manual batches")) {
858 $loggedInContactId = CRM_Core_Session
::singleton()->get('userID');
859 if ($batchCreatedId == $loggedInContactId) {
862 elseif (CRM_Utils_System
::isNull($batchCreatedId)) {