3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2015
37 class CRM_Batch_BAO_Batch
extends CRM_Batch_DAO_Batch
{
40 * Cache for the current batch object.
42 static $_batch = NULL;
45 * Not sure this is the best way to do this. Depends on how exportFinancialBatch() below gets called.
46 * Maybe a parameter to that function is better.
48 static $_exportFormat = NULL;
53 * @param array $params
55 * Associated array of ids.
56 * @param string $context
62 public static function create(&$params, $ids = NULL, $context = NULL) {
63 if (empty($params['id'])) {
64 $params['name'] = CRM_Utils_String
::titleToVar($params['title']);
67 $batch = new CRM_Batch_DAO_Batch();
68 $batch->copyValues($params);
69 if ($context == 'financialBatch' && !empty($ids['batchID'])) {
70 $batch->id
= $ids['batchID'];
78 * Retrieve the information about the batch.
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.
86 * CRM_Batch_BAO_Batch object on success, null otherwise
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);
99 * Get profile id associated with the batch type.
101 * @param int $batchTypeId
105 * $profileId profile id
107 public static function getProfileId($batchTypeId) {
108 //retrieve the profile specific to batch type
109 switch ($batchTypeId) {
112 //batch profile used for pledges
113 $profileName = "contribution_batch_entry";
117 //batch profile used for memberships
118 $profileName = "membership_batch_entry";
122 // get and return the profile id
123 return CRM_Core_DAO
::getFieldValue('CRM_Core_BAO_UFGroup', $profileName, 'id', 'name');
127 * Generate batch name.
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', array(1 => $batchNo)) . ': ' . date('Y-m-d');
139 * Create entity batch entry.
141 * @param array $params
144 public static function addBatchEntity(&$params) {
145 $entityBatch = new CRM_Batch_DAO_EntityBatch();
146 $entityBatch->copyValues($params);
147 $entityBatch->save();
152 * Remove entries from entity batch.
153 * @param array $params
154 * @return CRM_Batch_DAO_EntityBatch
156 public static function removeBatchEntity($params) {
157 $entityBatch = new CRM_Batch_DAO_EntityBatch();
158 $entityBatch->copyValues($params);
159 $entityBatch->delete();
164 * Delete batch entry.
166 * @param int $batchId
171 public static function deleteBatch($batchId) {
172 // delete entry from batch table
173 $batch = new CRM_Batch_DAO_Batch();
174 $batch->id
= $batchId;
180 * wrapper for ajax batch selector.
182 * @param array $params
183 * Associated array for params record id.
186 * associated array of batch list
188 public function getBatchListSelector(&$params) {
190 $params['offset'] = ($params['page'] - 1) * $params['rp'];
191 $params['rowCount'] = $params['rp'];
192 $params['sort'] = CRM_Utils_Array
::value('sortBy', $params);
195 $batches = self
::getBatchList($params);
197 // get batch totals for open batches
198 $fetchTotals = array();
199 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
200 $batchStatus = array(
201 array_search('Open', $batchStatus),
202 array_search('Reopened', $batchStatus),
204 if ($params['context'] == 'financialBatch') {
205 foreach ($batches as $id => $batch) {
206 if (in_array($batch['status_id'], $batchStatus)) {
207 $fetchTotals[] = $id;
211 $totals = self
::batchTotals($fetchTotals);
214 $params['total'] = self
::getBatchCount($params);
216 // format params and add links
217 $batchList = array();
219 foreach ($batches as $id => $value) {
221 if ($params['context'] == 'financialBatch') {
222 $batch['check'] = $value['check'];
224 $batch['batch_name'] = $value['title'];
225 $batch['total'] = '';
226 $batch['payment_instrument'] = $value['payment_instrument'];
227 $batch['item_count'] = CRM_Utils_Array
::value('item_count', $value);
228 $batch['type'] = $value['batch_type'];
229 if (!empty($value['total'])) {
230 $batch['total'] = CRM_Utils_Money
::format($value['total']);
233 // Compare totals with actuals
234 if (isset($totals[$id])) {
235 $batch['item_count'] = self
::displayTotals($totals[$id]['item_count'], $batch['item_count']);
236 $batch['total'] = self
::displayTotals(CRM_Utils_Money
::format($totals[$id]['total']), $batch['total']);
238 $batch['status'] = $value['batch_status'];
239 $batch['created_by'] = $value['created_by'];
240 $batch['links'] = $value['action'];
241 $batchList[$id] = $batch;
247 * Get list of batches.
249 * @param array $params
250 * Associated array for params.
254 public static function getBatchList(&$params) {
255 $whereClause = self
::whereClause($params);
257 if (!empty($params['rowCount']) && is_numeric($params['rowCount'])
258 && is_numeric($params['offset']) && $params['rowCount'] > 0
260 $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
263 $orderBy = ' ORDER BY batch.id desc';
264 if (!empty($params['sort'])) {
265 $orderBy = ' ORDER BY ' . CRM_Utils_Type
::escape($params['sort'], 'String');
269 SELECT batch.*, c.sort_name created_by
270 FROM civicrm_batch batch
271 INNER JOIN civicrm_contact c ON batch.created_id = c.id
276 $object = CRM_Core_DAO
::executeQuery($query, $params, TRUE, 'CRM_Batch_DAO_Batch');
277 if (!empty($params['context'])) {
278 $links = self
::links($params['context']);
281 $links = self
::links();
284 $batchTypes = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'type_id');
285 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id');
286 $batchStatusByName = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
287 $paymentInstrument = CRM_Contribute_PseudoConstant
::paymentInstrument();
290 while ($object->fetch()) {
293 CRM_Core_DAO
::storeValues($object, $values);
294 $action = array_sum(array_keys($newLinks));
296 if ($values['status_id'] == array_search('Closed', $batchStatusByName) && $params['context'] != 'financialBatch') {
299 elseif ($params['context'] == 'financialBatch') {
300 $values['check'] = "<input type='checkbox' id='check_" .
304 "' value='1' data-status_id='" .
305 $values['status_id'] . "' class='select-row'></input>";
307 switch ($batchStatusByName[$values['status_id']]) {
309 CRM_Utils_Array
::remove($newLinks, 'reopen', 'download');
313 CRM_Utils_Array
::remove($newLinks, 'close', 'edit', 'download');
317 CRM_Utils_Array
::remove($newLinks, 'close', 'edit', 'reopen', 'export');
320 if (!empty($values['type_id'])) {
321 $values['batch_type'] = $batchTypes[$values['type_id']];
323 $values['batch_status'] = $batchStatus[$values['status_id']];
324 $values['created_by'] = $object->created_by
;
325 $values['payment_instrument'] = '';
326 if (!empty($object->payment_instrument_id
)) {
327 $values['payment_instrument'] = $paymentInstrument[$object->payment_instrument_id
];
329 $tokens = array('id' => $object->id
, 'status' => $values['status_id']);
330 if ($values['status_id'] == array_search('Exported', $batchStatusByName)) {
331 $aid = CRM_Core_OptionGroup
::getValue('activity_type', 'Export Accounting Batch');
332 $activityParams = array('source_record_id' => $object->id
, 'activity_type_id' => $aid);
333 $exportActivity = CRM_Activity_BAO_Activity
::retrieve($activityParams, $val);
334 $fid = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_EntityFile', $exportActivity->id
, 'file_id', 'entity_id');
335 $tokens = array_merge(array('eid' => $exportActivity->id
, 'fid' => $fid), $tokens);
337 $values['action'] = CRM_Core_Action
::formLink(
343 'batch.selector.row',
347 $results[$object->id
] = $values;
354 * Get count of batches.
356 * @param array $params
357 * Associated array for params.
359 * @return null|string
361 public static function getBatchCount(&$params) {
363 $whereClause = self
::whereClause($params, $args);
364 $query = " SELECT COUNT(*) FROM civicrm_batch batch
365 INNER JOIN civicrm_contact c ON batch.created_id = c.id
366 WHERE {$whereClause}";
367 return CRM_Core_DAO
::singleValueQuery($query);
371 * Format where clause for getting lists of batches.
373 * @param array $params
374 * Associated array for params.
378 public static function whereClause($params) {
380 // Exclude data-entry batches
381 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
382 if (empty($params['status_id'])) {
383 $clauses[] = 'batch.status_id <> ' . array_search('Data Entry', $batchStatus);
388 'sort_name' => 'String',
389 'status_id' => 'Integer',
390 'payment_instrument_id' => 'Integer',
391 'item_count' => 'Integer',
395 foreach ($fields as $field => $type) {
396 $table = $field == 'sort_name' ?
'c' : 'batch';
397 if (isset($params[$field])) {
398 $value = CRM_Utils_Type
::escape($params[$field], $type, FALSE);
399 if ($value && $type == 'String') {
400 $clauses[] = "$table.$field LIKE '%$value%'";
402 elseif ($value && $type == 'Float') {
403 $clauses[] = "$table.$field = '$value'";
406 if ($field == 'status_id' && $value == array_search('Open', $batchStatus)) {
407 $clauses[] = "$table.$field IN ($value," . array_search('Reopened', $batchStatus) . ')';
410 $clauses[] = "$table.$field = $value";
415 return $clauses ?
implode(' AND ', $clauses) : '1';
419 * Define action links.
421 * @param null $context
424 * array of action links
426 public function links($context = NULL) {
427 if ($context == 'financialBatch') {
429 'transaction' => array(
430 'name' => ts('Transactions'),
431 'url' => 'civicrm/batchtransaction',
432 'qs' => 'reset=1&bid=%%id%%',
433 'title' => ts('View/Add Transactions to Batch'),
436 'name' => ts('Edit'),
437 'url' => 'civicrm/financial/batch',
438 'qs' => 'reset=1&action=update&id=%%id%%&context=1',
439 'title' => ts('Edit Batch'),
442 'name' => ts('Close'),
443 'title' => ts('Close Batch'),
445 'extra' => 'rel="close"',
448 'name' => ts('Export'),
449 'title' => ts('Export Batch'),
451 'extra' => 'rel="export"',
454 'name' => ts('Re-open'),
455 'title' => ts('Re-open Batch'),
457 'extra' => 'rel="reopen"',
460 'name' => ts('Delete'),
461 'title' => ts('Delete Batch'),
463 'extra' => 'rel="delete"',
466 'name' => ts('Download'),
467 'url' => 'civicrm/file',
468 'qs' => 'reset=1&id=%%fid%%&eid=%%eid%%',
469 'title' => ts('Download Batch'),
475 CRM_Core_Action
::COPY
=> array(
476 'name' => ts('Enter records'),
477 'url' => 'civicrm/batch/entry',
478 'qs' => 'id=%%id%%&reset=1',
479 'title' => ts('Batch Data Entry'),
481 CRM_Core_Action
::UPDATE
=> array(
482 'name' => ts('Edit'),
483 'url' => 'civicrm/batch',
484 'qs' => 'action=update&id=%%id%%&reset=1',
485 'title' => ts('Edit Batch'),
487 CRM_Core_Action
::DELETE
=> array(
488 'name' => ts('Delete'),
489 'url' => 'civicrm/batch',
490 'qs' => 'action=delete&id=%%id%%',
491 'title' => ts('Delete Batch'),
502 * all batches excluding batches with data entry in progress
504 public static function getBatches() {
505 $dataEntryStatusId = CRM_Core_OptionGroup
::getValue('batch_status', 'Data Entry', 'name');
506 $query = "SELECT id, title
508 WHERE item_count >= 1
509 AND status_id != {$dataEntryStatusId}
513 $dao = CRM_Core_DAO
::executeQuery($query);
514 while ($dao->fetch()) {
515 $batches[$dao->id
] = $dao->title
;
522 * Calculate sum of all entries in a batch.
523 * Used to validate and update item_count and total when closing an accounting batch
525 * @param array $batchIds
528 public static function batchTotals($batchIds) {
529 $totals = array_fill_keys($batchIds, array('item_count' => 0, 'total' => 0));
531 $sql = "SELECT eb.batch_id, COUNT(tx.id) AS item_count, SUM(tx.total_amount) AS total
532 FROM civicrm_entity_batch eb
533 INNER JOIN civicrm_financial_trxn tx ON tx.id = eb.entity_id AND eb.entity_table = 'civicrm_financial_trxn'
534 WHERE eb.batch_id IN (" . implode(',', $batchIds) . ")
535 GROUP BY eb.batch_id";
536 $dao = CRM_Core_DAO
::executeQuery($sql);
537 while ($dao->fetch()) {
538 $totals[$dao->batch_id
] = (array) $dao;
546 * Format markup for comparing two totals.
554 public static function displayTotals($actual, $expected) {
555 $class = 'actual-value';
556 if ($expected && $expected != $actual) {
557 $class .= ' crm-error';
559 $actualTitle = ts('Current Total');
560 $output = "<span class='$class' title='$actualTitle'>$actual</span>";
562 $expectedTitle = ts('Expected Total');
563 $output .= " / <span class='expected-value' title='$expectedTitle'>$expected</span>";
569 * Function for exporting financial accounts, currently we support CSV and IIF format
570 * @see http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
572 * @param array $batchIds
573 * Associated array of batch ids.
574 * @param string $exportFormat
577 public static function exportFinancialBatch($batchIds, $exportFormat) {
578 if (empty($batchIds)) {
579 CRM_Core_Error
::fatal(ts('No batches were selected.'));
582 if (empty($exportFormat)) {
583 CRM_Core_Error
::fatal(ts('No export format selected.'));
586 self
::$_exportFormat = $exportFormat;
588 // Instantiate appropriate exporter based on user-selected format.
589 $exporterClass = "CRM_Financial_BAO_ExportFormat_" . self
::$_exportFormat;
590 if (class_exists($exporterClass)) {
591 $exporter = new $exporterClass();
594 CRM_Core_Error
::fatal("Could not locate exporter: $exporterClass");
596 switch (self
::$_exportFormat) {
598 foreach ($batchIds as $batchId) {
599 $export[$batchId] = $exporter->generateExportQuery($batchId);
601 $exporter->makeCSV($export);
605 foreach ($batchIds as $batchId) {
606 $export[$batchId] = $exporter->generateExportQuery($batchId);
608 $exporter->makeIIF($export);
614 * @param array $batchIds
617 public static function closeReOpen($batchIds = array(), $status) {
618 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id');
619 $params['status_id'] = CRM_Utils_Array
::key($status, $batchStatus);
620 $session = CRM_Core_Session
::singleton();
621 $params['modified_date'] = date('YmdHis');
622 $params['modified_id'] = $session->get('userID');
623 foreach ($batchIds as $key => $value) {
624 $params['id'] = $ids['batchID'] = $value;
625 self
::create($params, $ids);
627 $url = CRM_Utils_System
::url('civicrm/financial/financialbatches', "reset=1&batchStatus={$params['status_id']}");
628 CRM_Utils_System
::redirect($url);
632 * Retrieve financial items assigned for a batch.
634 * @param int $entityID
635 * @param array $returnValues
636 * @param bool $notPresent
637 * @param array $params
638 * @param bool $getCount
640 * @return CRM_Core_DAO
642 public static function getBatchFinancialItems($entityID, $returnValues, $notPresent = NULL, $params = NULL, $getCount = FALSE) {
644 if (!empty($params['rowCount']) &&
645 $params['rowCount'] > 0
647 $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
650 // action is taken depending upon the mode
651 $select = 'civicrm_financial_trxn.id ';
652 if (!empty($returnValues)) {
653 $select .= " , " . implode(' , ', $returnValues);
656 $orderBy = " ORDER BY civicrm_financial_trxn.id";
657 if (!empty($params['sort'])) {
658 $orderBy = ' ORDER BY ' . CRM_Utils_Type
::escape($params['sort'], 'String');
661 $from = "civicrm_financial_trxn
662 LEFT JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id
663 LEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
664 AND civicrm_entity_batch.entity_id = civicrm_financial_trxn.id
665 LEFT JOIN civicrm_contribution ON civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id
666 LEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id
667 LEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id
668 LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id
671 $searchFields = array(
674 'contribution_page_id',
675 'payment_instrument_id',
676 'contribution_trxn_id',
677 'contribution_source',
678 'contribution_currency_type',
679 'contribution_pay_later',
680 'contribution_recurring',
682 'contribution_thankyou_date_is_not_null',
683 'contribution_receipt_date_is_not_null',
684 'contribution_pcp_made_through_id',
685 'contribution_pcp_display_in_roll',
686 'contribution_date_relative',
687 'contribution_amount_low',
688 'contribution_amount_high',
689 'contribution_in_honor_of',
692 'contribution_date_relative',
693 'contribution_date_high',
694 'contribution_date_low',
695 'contribution_check_number',
696 'contribution_status_id',
699 foreach ($searchFields as $field) {
700 if (isset($params[$field])) {
701 $values[$field] = $params[$field];
702 if ($field == 'sort_name') {
703 $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id
704 LEFT JOIN civicrm_email ON contact_b.id = civicrm_email.contact_id";
706 if ($field == 'contribution_in_honor_of') {
707 $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id";
709 if ($field == 'contact_tags') {
710 $from .= " LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-{$params[$field]}` ON `civicrm_entity_tag-{$params[$field]}`.entity_id = contact_a.id";
712 if ($field == 'group') {
713 $from .= " LEFT JOIN civicrm_group_contact `civicrm_group_contact-{$params[$field]}` ON contact_a.id = `civicrm_group_contact-{$params[$field]}`.contact_id ";
715 if ($field == 'contribution_date_relative') {
716 $relativeDate = explode('.', $params[$field]);
717 $date = CRM_Utils_Date
::relativeToAbsolute($relativeDate[0], $relativeDate[1]);
718 $values['contribution_date_low'] = $date['from'];
719 $values['contribution_date_high'] = $date['to'];
721 $searchParams = CRM_Contact_BAO_Query
::convertFormValues($values);
722 $query = new CRM_Contact_BAO_Query($searchParams,
723 CRM_Contribute_BAO_Query
::defaultReturnProperties(CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
,
725 ), NULL, FALSE, FALSE, CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
727 if ($field == 'contribution_date_high' ||
$field == 'contribution_date_low') {
728 $query->dateQueryBuilder($params[$field], 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date');
732 if (!empty($query->_where
[0])) {
733 $where = implode(' AND ', $query->_where
[0]) .
734 " AND civicrm_entity_batch.batch_id IS NULL
735 AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'";
736 $where = str_replace('civicrm_contribution.payment_instrument_id', 'civicrm_financial_trxn.payment_instrument_id', $where);
740 $searchValue = FALSE;
745 $where = " ( civicrm_entity_batch.batch_id = {$entityID}
746 AND civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
747 AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution') ";
750 $where = " ( civicrm_entity_batch.batch_id IS NULL
751 AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')";
766 $result = CRM_Core_DAO
::executeQuery($sql);
772 * @param string $batchIds
777 public static function getBatchNames($batchIds) {
778 $query = 'SELECT id, title
780 WHERE id IN (' . $batchIds . ')';
783 $dao = CRM_Core_DAO
::executeQuery($query);
784 while ($dao->fetch()) {
785 $batches[$dao->id
] = $dao->title
;
791 * Function get batch statuses.
793 * @param string $batchIds
798 public static function getBatchStatuses($batchIds) {
799 $query = 'SELECT id, status_id
801 WHERE id IN (' . $batchIds . ')';
804 $dao = CRM_Core_DAO
::executeQuery($query);
805 while ($dao->fetch()) {
806 $batches[$dao->id
] = $dao->status_id
;