3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
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-2014
39 class CRM_Batch_BAO_Batch
extends CRM_Batch_DAO_Batch
{
42 * Cache for the current batch object
44 static $_batch = NULL;
47 * Not sure this is the best way to do this. Depends on how exportFinancialBatch() below gets called.
48 * Maybe a parameter to that function is better.
50 static $_exportFormat = NULL;
55 * @param array $params
57 * Associated array of ids.
58 * @param string $context
64 public static function create(&$params, $ids = NULL, $context = NULL) {
65 if (empty($params['id'])) {
66 $params['name'] = CRM_Utils_String
::titleToVar($params['title']);
69 $batch = new CRM_Batch_DAO_Batch();
70 $batch->copyValues($params);
71 if ($context == 'financialBatch' && !empty($ids['batchID'])) {
72 $batch->id
= $ids['batchID'];
80 * Retrieve the information about the batch
82 * @param array $params
83 * (reference ) an assoc array of name/value pairs.
84 * @param array $defaults
85 * (reference ) an assoc array to hold the flattened values.
88 * CRM_Batch_BAO_Batch object on success, null otherwise
90 public static function retrieve(&$params, &$defaults) {
91 $batch = new CRM_Batch_DAO_Batch();
92 $batch->copyValues($params);
93 if ($batch->find(TRUE)) {
94 CRM_Core_DAO
::storeValues($batch, $defaults);
101 * Get profile id associated with the batch type
103 * @param int $batchTypeId
107 * $profileId profile id
109 public static function getProfileId($batchTypeId) {
110 //retrieve the profile specific to batch type
111 switch ($batchTypeId) {
114 //batch profile used for pledges
115 $profileName = "contribution_batch_entry";
119 //batch profile used for memberships
120 $profileName = "membership_batch_entry";
124 // get and return the profile id
125 return CRM_Core_DAO
::getFieldValue('CRM_Core_BAO_UFGroup', $profileName, 'id', 'name');
129 * Generate batch name
134 public static function generateBatchName() {
135 $sql = "SELECT max(id) FROM civicrm_batch";
136 $batchNo = CRM_Core_DAO
::singleValueQuery($sql) +
1;
137 return ts('Batch %1', array(1 => $batchNo)) . ': ' . date('Y-m-d');
141 * Create entity batch entry
143 * @param array $params
146 public static function addBatchEntity(&$params) {
147 $entityBatch = new CRM_Batch_DAO_EntityBatch();
148 $entityBatch->copyValues($params);
149 $entityBatch->save();
154 * Remove entries from entity batch
155 * @param array $params
156 * @return CRM_Batch_DAO_EntityBatch
158 public static function removeBatchEntity($params) {
159 $entityBatch = new CRM_Batch_DAO_EntityBatch();
160 $entityBatch->copyValues($params);
161 $entityBatch->delete();
168 * @param int $batchId
173 public static function deleteBatch($batchId) {
174 // delete entry from batch table
175 $batch = new CRM_Batch_DAO_Batch();
176 $batch->id
= $batchId;
182 * wrapper for ajax batch selector
184 * @param array $params
185 * Associated array for params record id.
188 * associated array of batch list
190 public function getBatchListSelector(&$params) {
192 $params['offset'] = ($params['page'] - 1) * $params['rp'];
193 $params['rowCount'] = $params['rp'];
194 $params['sort'] = CRM_Utils_Array
::value('sortBy', $params);
197 $batches = self
::getBatchList($params);
199 // get batch totals for open batches
200 $fetchTotals = array();
201 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
202 $batchStatus = array(
203 array_search('Open', $batchStatus),
204 array_search('Reopened', $batchStatus),
206 if ($params['context'] == 'financialBatch') {
207 foreach ($batches as $id => $batch) {
208 if (in_array($batch['status_id'], $batchStatus)) {
209 $fetchTotals[] = $id;
213 $totals = self
::batchTotals($fetchTotals);
216 $params['total'] = self
::getBatchCount($params);
218 // format params and add links
219 $batchList = array();
221 foreach ($batches as $id => $value) {
223 if ($params['context'] == 'financialBatch') {
224 $batch['check'] = $value['check'];
226 $batch['batch_name'] = $value['title'];
227 $batch['total'] = '';
228 $batch['payment_instrument'] = $value['payment_instrument'];
229 $batch['item_count'] = CRM_Utils_Array
::value('item_count', $value);
230 $batch['type'] = $value['batch_type'];
231 if (!empty($value['total'])) {
232 $batch['total'] = CRM_Utils_Money
::format($value['total']);
235 // Compare totals with actuals
236 if (isset($totals[$id])) {
237 $batch['item_count'] = self
::displayTotals($totals[$id]['item_count'], $batch['item_count']);
238 $batch['total'] = self
::displayTotals(CRM_Utils_Money
::format($totals[$id]['total']), $batch['total']);
240 $batch['status'] = $value['batch_status'];
241 $batch['created_by'] = $value['created_by'];
242 $batch['links'] = $value['action'];
243 $batchList[$id] = $batch;
249 * Get list of batches
251 * @param array $params
252 * Associated array for params.
256 public static function getBatchList(&$params) {
257 $whereClause = self
::whereClause($params);
259 if (!empty($params['rowCount']) && is_numeric($params['rowCount'])
260 && is_numeric($params['offset']) && $params['rowCount'] > 0
262 $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
265 $orderBy = ' ORDER BY batch.id desc';
266 if (!empty($params['sort'])) {
267 $orderBy = ' ORDER BY ' . CRM_Utils_Type
::escape($params['sort'], 'String');
271 SELECT batch.*, c.sort_name created_by
272 FROM civicrm_batch batch
273 INNER JOIN civicrm_contact c ON batch.created_id = c.id
278 $object = CRM_Core_DAO
::executeQuery($query, $params, TRUE, 'CRM_Batch_DAO_Batch');
279 if (!empty($params['context'])) {
280 $links = self
::links($params['context']);
283 $links = self
::links();
286 $batchTypes = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'type_id');
287 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id');
288 $batchStatusByName = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
289 $paymentInstrument = CRM_Contribute_PseudoConstant
::paymentInstrument();
292 while ($object->fetch()) {
295 CRM_Core_DAO
::storeValues($object, $values);
296 $action = array_sum(array_keys($newLinks));
298 if ($values['status_id'] == array_search('Closed', $batchStatusByName) && $params['context'] != 'financialBatch') {
301 elseif ($params['context'] == 'financialBatch') {
302 $values['check'] = "<input type='checkbox' id='check_" .
306 "' value='1' data-status_id='" .
307 $values['status_id'] . "' class='select-row'></input>";
309 switch ($batchStatusByName[$values['status_id']]) {
311 CRM_Utils_Array
::remove($newLinks, 'reopen', 'download');
315 CRM_Utils_Array
::remove($newLinks, 'close', 'edit', 'download');
319 CRM_Utils_Array
::remove($newLinks, 'close', 'edit', 'reopen', 'export');
322 if (!empty($values['type_id'])) {
323 $values['batch_type'] = $batchTypes[$values['type_id']];
325 $values['batch_status'] = $batchStatus[$values['status_id']];
326 $values['created_by'] = $object->created_by
;
327 $values['payment_instrument'] = '';
328 if (!empty($object->payment_instrument_id
)) {
329 $values['payment_instrument'] = $paymentInstrument[$object->payment_instrument_id
];
331 $tokens = array('id' => $object->id
, 'status' => $values['status_id']);
332 if ($values['status_id'] == array_search('Exported', $batchStatusByName)) {
333 $aid = CRM_Core_OptionGroup
::getValue('activity_type', 'Export Accounting Batch');
334 $activityParams = array('source_record_id' => $object->id
, 'activity_type_id' => $aid);
335 $exportActivity = CRM_Activity_BAO_Activity
::retrieve($activityParams, $val);
336 $fid = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_EntityFile', $exportActivity->id
, 'file_id', 'entity_id');
337 $tokens = array_merge(array('eid' => $exportActivity->id
, 'fid' => $fid), $tokens);
339 $values['action'] = CRM_Core_Action
::formLink(
345 'batch.selector.row',
349 $results[$object->id
] = $values;
356 * Get count of batches
358 * @param array $params
359 * Associated array for params.
361 * @return null|string
363 public static function getBatchCount(&$params) {
365 $whereClause = self
::whereClause($params, $args);
366 $query = " SELECT COUNT(*) FROM civicrm_batch batch
367 INNER JOIN civicrm_contact c ON batch.created_id = c.id
368 WHERE {$whereClause}";
369 return CRM_Core_DAO
::singleValueQuery($query);
373 * Format where clause for getting lists of batches
375 * @param array $params
376 * Associated array for params.
380 public static function whereClause($params) {
382 // Exclude data-entry batches
383 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id', array('labelColumn' => 'name'));
384 if (empty($params['status_id'])) {
385 $clauses[] = 'batch.status_id <> ' . array_search('Data Entry', $batchStatus);
390 'sort_name' => 'String',
391 'status_id' => 'Integer',
392 'payment_instrument_id' => 'Integer',
393 'item_count' => 'Integer',
397 foreach ($fields as $field => $type) {
398 $table = $field == 'sort_name' ?
'c' : 'batch';
399 if (isset($params[$field])) {
400 $value = CRM_Utils_Type
::escape($params[$field], $type, FALSE);
401 if ($value && $type == 'String') {
402 $clauses[] = "$table.$field LIKE '%$value%'";
404 elseif ($value && $type == 'Float') {
405 $clauses[] = "$table.$field = '$value'";
408 if ($field == 'status_id' && $value == array_search('Open', $batchStatus)) {
409 $clauses[] = "$table.$field IN ($value," . array_search('Reopened', $batchStatus) . ')';
412 $clauses[] = "$table.$field = $value";
417 return $clauses ?
implode(' AND ', $clauses) : '1';
421 * Define action links
423 * @param null $context
426 * array of action links
428 public function links($context = NULL) {
429 if ($context == 'financialBatch') {
431 'transaction' => array(
432 'name' => ts('Transactions'),
433 'url' => 'civicrm/batchtransaction',
434 'qs' => 'reset=1&bid=%%id%%',
435 'title' => ts('View/Add Transactions to Batch'),
438 'name' => ts('Edit'),
439 'url' => 'civicrm/financial/batch',
440 'qs' => 'reset=1&action=update&id=%%id%%&context=1',
441 'title' => ts('Edit Batch'),
444 'name' => ts('Close'),
445 'title' => ts('Close Batch'),
447 'extra' => 'rel="close"',
450 'name' => ts('Export'),
451 'title' => ts('Export Batch'),
453 'extra' => 'rel="export"',
456 'name' => ts('Re-open'),
457 'title' => ts('Re-open Batch'),
459 'extra' => 'rel="reopen"',
462 'name' => ts('Delete'),
463 'title' => ts('Delete Batch'),
465 'extra' => 'rel="delete"',
468 'name' => ts('Download'),
469 'url' => 'civicrm/file',
470 'qs' => 'reset=1&id=%%fid%%&eid=%%eid%%',
471 'title' => ts('Download Batch'),
477 CRM_Core_Action
::COPY
=> array(
478 'name' => ts('Enter records'),
479 'url' => 'civicrm/batch/entry',
480 'qs' => 'id=%%id%%&reset=1',
481 'title' => ts('Batch Data Entry'),
483 CRM_Core_Action
::UPDATE
=> array(
484 'name' => ts('Edit'),
485 'url' => 'civicrm/batch',
486 'qs' => 'action=update&id=%%id%%&reset=1',
487 'title' => ts('Edit Batch'),
489 CRM_Core_Action
::DELETE
=> array(
490 'name' => ts('Delete'),
491 'url' => 'civicrm/batch',
492 'qs' => 'action=delete&id=%%id%%',
493 'title' => ts('Delete Batch'),
504 * all batches excluding batches with data entry in progress
506 public static function getBatches() {
507 $dataEntryStatusId = CRM_Core_OptionGroup
::getValue('batch_status', 'Data Entry', 'name');
508 $query = "SELECT id, title
510 WHERE item_count >= 1
511 AND status_id != {$dataEntryStatusId}
515 $dao = CRM_Core_DAO
::executeQuery($query);
516 while ($dao->fetch()) {
517 $batches[$dao->id
] = $dao->title
;
524 * Calculate sum of all entries in a batch
525 * Used to validate and update item_count and total when closing an accounting batch
527 * @param array $batchIds
530 public static function batchTotals($batchIds) {
531 $totals = array_fill_keys($batchIds, array('item_count' => 0, 'total' => 0));
533 $sql = "SELECT eb.batch_id, COUNT(tx.id) AS item_count, SUM(tx.total_amount) AS total
534 FROM civicrm_entity_batch eb
535 INNER JOIN civicrm_financial_trxn tx ON tx.id = eb.entity_id AND eb.entity_table = 'civicrm_financial_trxn'
536 WHERE eb.batch_id IN (" . implode(',', $batchIds) . ")
537 GROUP BY eb.batch_id";
538 $dao = CRM_Core_DAO
::executeQuery($sql);
539 while ($dao->fetch()) {
540 $totals[$dao->batch_id
] = (array) $dao;
548 * Format markup for comparing two totals
556 public static function displayTotals($actual, $expected) {
557 $class = 'actual-value';
558 if ($expected && $expected != $actual) {
559 $class .= ' crm-error';
561 $actualTitle = ts('Current Total');
562 $output = "<span class='$class' title='$actualTitle'>$actual</span>";
564 $expectedTitle = ts('Expected Total');
565 $output .= " / <span class='expected-value' title='$expectedTitle'>$expected</span>";
571 * Function for exporting financial accounts, currently we support CSV and IIF format
572 * @see http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
574 * @param array $batchIds
575 * Associated array of batch ids.
576 * @param string $exportFormat
582 public static function exportFinancialBatch($batchIds, $exportFormat) {
583 if (empty($batchIds)) {
584 CRM_Core_Error
::fatal(ts('No batches were selected.'));
587 if (empty($exportFormat)) {
588 CRM_Core_Error
::fatal(ts('No export format selected.'));
591 self
::$_exportFormat = $exportFormat;
593 // Instantiate appropriate exporter based on user-selected format.
594 $exporterClass = "CRM_Financial_BAO_ExportFormat_" . self
::$_exportFormat;
595 if (class_exists($exporterClass)) {
596 $exporter = new $exporterClass();
599 CRM_Core_Error
::fatal("Could not locate exporter: $exporterClass");
601 switch (self
::$_exportFormat) {
603 foreach ($batchIds as $batchId) {
604 $export[$batchId] = $exporter->generateExportQuery($batchId);
606 $exporter->makeCSV($export);
610 foreach ($batchIds as $batchId) {
611 $export[$batchId] = $exporter->generateExportQuery($batchId);
613 $exporter->makeIIF($export);
619 * @param array $batchIds
622 public static function closeReOpen($batchIds = array(), $status) {
623 $batchStatus = CRM_Core_PseudoConstant
::get('CRM_Batch_DAO_Batch', 'status_id');
624 $params['status_id'] = CRM_Utils_Array
::key($status, $batchStatus);
625 $session = CRM_Core_Session
::singleton();
626 $params['modified_date'] = date('YmdHis');
627 $params['modified_id'] = $session->get('userID');
628 foreach ($batchIds as $key => $value) {
629 $params['id'] = $ids['batchID'] = $value;
630 self
::create($params, $ids);
632 $url = CRM_Utils_System
::url('civicrm/financial/financialbatches', "reset=1&batchStatus={$params['status_id']}");
633 CRM_Utils_System
::redirect($url);
637 * Retrieve financial items assigned for a batch
639 * @param int $entityID
640 * @param array $returnValues
641 * @param bool $notPresent
642 * @param array $params
643 * @param bool $getCount
645 * @return CRM_Core_DAO
647 public static function getBatchFinancialItems($entityID, $returnValues, $notPresent = NULL, $params = NULL, $getCount = FALSE) {
649 if (!empty($params['rowCount']) &&
650 $params['rowCount'] > 0
652 $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
655 // action is taken depending upon the mode
656 $select = 'civicrm_financial_trxn.id ';
657 if (!empty($returnValues)) {
658 $select .= " , " . implode(' , ', $returnValues);
661 $orderBy = " ORDER BY civicrm_financial_trxn.id";
662 if (!empty($params['sort'])) {
663 $orderBy = ' ORDER BY ' . CRM_Utils_Type
::escape($params['sort'], 'String');
666 $from = "civicrm_financial_trxn
667 LEFT JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id
668 LEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_id = civicrm_financial_trxn.id
669 LEFT JOIN civicrm_contribution ON civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id
670 LEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id
671 LEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id
672 LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id
675 $searchFields = array(
678 'contribution_page_id',
679 'contribution_payment_instrument_id',
680 'contribution_transaction_id',
681 'contribution_source',
682 'contribution_currency_type',
683 'contribution_pay_later',
684 'contribution_recurring',
686 'contribution_thankyou_date_is_not_null',
687 'contribution_receipt_date_is_not_null',
688 'contribution_pcp_made_through_id',
689 'contribution_pcp_display_in_roll',
690 'contribution_date_relative',
691 'contribution_amount_low',
692 'contribution_amount_high',
693 'contribution_in_honor_of',
696 'contribution_date_relative',
697 'contribution_date_high',
698 'contribution_date_low',
699 'contribution_check_number',
700 'contribution_status_id',
703 foreach ($searchFields as $field) {
704 if (isset($params[$field])) {
705 $values[$field] = $params[$field];
706 if ($field == 'sort_name') {
707 $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id
708 LEFT JOIN civicrm_email ON contact_b.id = civicrm_email.contact_id";
710 if ($field == 'contribution_in_honor_of') {
711 $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id";
713 if ($field == 'contact_tags') {
714 $from .= " LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-{$params[$field]}` ON `civicrm_entity_tag-{$params[$field]}`.entity_id = contact_a.id";
716 if ($field == 'group') {
717 $from .= " LEFT JOIN civicrm_group_contact `civicrm_group_contact-{$params[$field]}` ON contact_a.id = `civicrm_group_contact-{$params[$field]}`.contact_id ";
719 if ($field == 'contribution_date_relative') {
720 $relativeDate = explode('.', $params[$field]);
721 $date = CRM_Utils_Date
::relativeToAbsolute($relativeDate[0], $relativeDate[1]);
722 $values['contribution_date_low'] = $date['from'];
723 $values['contribution_date_high'] = $date['to'];
725 $searchParams = CRM_Contact_BAO_Query
::convertFormValues($values);
726 $query = new CRM_Contact_BAO_Query($searchParams,
727 CRM_Contribute_BAO_Query
::defaultReturnProperties(CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
,
729 ), NULL, FALSE, FALSE, CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
731 if ($field == 'contribution_date_high' ||
$field == 'contribution_date_low') {
732 $query->dateQueryBuilder($params[$field], 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date');
736 if (!empty($query->_where
[0])) {
737 $where = implode(' AND ', $query->_where
[0]) .
738 " AND civicrm_entity_batch.batch_id IS NULL
739 AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'";
743 $searchValue = FALSE;
748 $where = " ( civicrm_entity_batch.batch_id = {$entityID}
749 AND civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
750 AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution') ";
753 $where = " ( civicrm_entity_batch.batch_id IS NULL
754 AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')";
769 $result = CRM_Core_DAO
::executeQuery($sql);
775 * @param string $batchIds
780 public static function getBatchNames($batchIds) {
781 $query = 'SELECT id, title
783 WHERE id IN (' . $batchIds . ')';
786 $dao = CRM_Core_DAO
::executeQuery($query);
787 while ($dao->fetch()) {
788 $batches[$dao->id
] = $dao->title
;
794 * Function get batch statuses
796 * @param string $batchIds
801 public static function getBatchStatuses($batchIds) {
802 $query = 'SELECT id, status_id
804 WHERE id IN (' . $batchIds . ')';
807 $dao = CRM_Core_DAO
::executeQuery($query);
808 while ($dao->fetch()) {
809 $batches[$dao->id
] = $dao->status_id
;