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 +--------------------------------------------------------------------+
13 * Test APIv3 civicrm_report_instance_* functions
15 * @package CiviCRM_APIv3
16 * @subpackage API_Report
19 class api_v3_ReportTemplateTest
extends CiviUnitTestCase
{
21 use Civi\Test\ACLPermissionTrait
;
22 use CRMTraits_PCP_PCPTestTrait
;
23 use CRMTraits_Custom_CustomDataTrait
;
25 protected $contactIDs = [];
28 * Our group reports use an alter so transaction cleanup won't work.
32 public function tearDown(): void
{
33 $this->quickCleanUpFinancialEntities();
34 $this->quickCleanup(['civicrm_group', 'civicrm_saved_search', 'civicrm_group_contact', 'civicrm_group_contact_cache', 'civicrm_group'], TRUE);
35 (new CRM_Logging_Schema())->dropAllLogTables();
40 * Test CRUD actions on a report template.
42 * @throws \CRM_Core_Exception
44 public function testReportTemplate() {
45 /** @noinspection SpellCheckingInspection */
46 $result = $this->callAPISuccess('ReportTemplate', 'create', [
47 'label' => 'Example Form',
48 'description' => 'Longish description of the example form',
49 'class_name' => 'CRM_Report_Form_Examplez',
50 'report_url' => 'example/path',
51 'component' => 'CiviCase',
53 $this->assertAPISuccess($result);
54 $this->assertEquals(1, $result['count']);
55 $entityId = $result['id'];
56 $this->assertIsNumeric($entityId);
57 $this->assertEquals(7, $result['values'][$entityId]['component_id']);
58 $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
59 WHERE name = "CRM_Report_Form_Examplez"
60 AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
61 $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value
62 WHERE name = "CRM_Report_Form_Examplez"');
64 // change component to null
65 $result = $this->callAPISuccess('ReportTemplate', 'create', [
69 $this->assertAPISuccess($result);
70 $this->assertEquals(1, $result['count']);
71 $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
72 WHERE name = "CRM_Report_Form_Examplez"
73 AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
74 $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
75 WHERE name = "CRM_Report_Form_Examplez"
76 AND component_id IS NULL');
79 $result = $this->callAPISuccess('ReportTemplate', 'create', [
83 $this->assertAPISuccess($result);
84 $this->assertEquals(1, $result['count']);
85 $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
86 WHERE name = "CRM_Report_Form_Examplez"
87 AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
88 $this->assertDBQuery(0, 'SELECT is_active FROM civicrm_option_value
89 WHERE name = "CRM_Report_Form_Examplez"');
92 $result = $this->callAPISuccess('ReportTemplate', 'create', [
96 $this->assertAPISuccess($result);
97 $this->assertEquals(1, $result['count']);
98 $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
99 WHERE name = "CRM_Report_Form_Examplez"
100 AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
101 $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value
102 WHERE name = "CRM_Report_Form_Examplez"');
104 $result = $this->callAPISuccess('ReportTemplate', 'delete', [
107 $this->assertAPISuccess($result);
108 $this->assertEquals(1, $result['count']);
109 $this->assertDBQuery(0, 'SELECT count(*) FROM civicrm_option_value
110 WHERE name = "CRM_Report_Form_Examplez"
115 * Test api to get rows from reports.
117 * @dataProvider getReportTemplatesSupportingSelectWhere
121 * @throws \CRM_Core_Exception
123 public function testReportTemplateSelectWhere($reportID): void
{
124 $this->hookClass
->setHook('civicrm_selectWhereClause', [$this, 'hookSelectWhere']);
125 $result = $this->callAPISuccess('report_template', 'getrows', [
126 'report_id' => $reportID,
127 'options' => ['metadata' => ['sql']],
130 foreach ($result['metadata']['sql'] as $sql) {
131 if (strpos($sql, " = 'Organization' ") !== FALSE) {
135 $this->assertTrue($found, $reportID);
139 * Get templates suitable for SelectWhere test.
142 * @throws \CiviCRM_API3_Exception
144 public function getReportTemplatesSupportingSelectWhere() {
145 $allTemplates = self
::getReportTemplates();
146 // Exclude all that do not work as of test being written. I have not dug into why not.
147 $currentlyExcluded = [
155 'contribute/bookkeeping',
157 'event/incomesummary',
162 'logging/contact/detail',
163 'logging/contact/summary',
165 foreach ($allTemplates as $index => $template) {
166 $reportID = $template[0];
167 if (in_array($reportID, $currentlyExcluded, TRUE) ||
stripos($reportID, 'has existing issues') !== FALSE) {
168 unset($allTemplates[$index]);
171 return $allTemplates;
175 * @param \CRM_Core_DAO $entity
176 * @param array $clauses
178 public function hookSelectWhere($entity, &$clauses) {
179 // Restrict access to cases by type
180 if ($entity === 'Contact') {
181 $clauses['contact_type'][] = " = 'Organization' ";
186 * Test getrows on contact summary report.
188 * @throws \CRM_Core_Exception
190 public function testReportTemplateGetRowsContactSummary() {
191 $result = $this->callAPISuccess('report_template', 'getrows', [
192 'report_id' => 'contact/summary',
193 'options' => ['metadata' => ['labels', 'title']],
195 $this->assertEquals('Contact Name', $result['metadata']['labels']['civicrm_contact_sort_name']);
197 //the second part of this test has been commented out because it relied on the db being reset to
199 //wasn't able to get that to work consistently
200 // however, when the db is in the base state the tests do pass
201 // and because the test covers 'all' contacts we can't create our own & assume the others don't exist
203 $this->assertEquals(2, $result['count']);
204 $this->assertEquals('Default Organization', $result[0]['civicrm_contact_sort_name']);
205 $this->assertEquals('Second Domain', $result[1]['civicrm_contact_sort_name']);
206 $this->assertEquals('15 Main St', $result[1]['civicrm_address_street_address']);
211 * Test getrows on Mailing Opened report.
213 public function testReportTemplateGetRowsMailingUniqueOpened() {
214 $description = 'Retrieve rows from a mailing opened report template.';
215 $this->loadXMLDataSet(__DIR__
. '/../../CRM/Mailing/BAO/queryDataset.xml');
217 // Check total rows without distinct
219 $_REQUEST['distinct'] = 0;
220 $result = $this->callAPIAndDocument('report_template', 'getrows', [
221 'report_id' => 'Mailing/opened',
222 'options' => ['metadata' => ['labels', 'title']],
223 ], __FUNCTION__
, __FILE__
, $description, 'Getrows');
224 $this->assertEquals(14, $result['count']);
226 // Check total rows with distinct
227 $_REQUEST['distinct'] = 1;
228 $result = $this->callAPIAndDocument('report_template', 'getrows', [
229 'report_id' => 'Mailing/opened',
230 'options' => ['metadata' => ['labels', 'title']],
231 ], __FUNCTION__
, __FILE__
, $description, 'Getrows');
232 $this->assertEquals(5, $result['count']);
234 // Check total rows with distinct by passing NULL value to distinct parameter
235 $_REQUEST['distinct'] = NULL;
236 $result = $this->callAPIAndDocument('report_template', 'getrows', [
237 'report_id' => 'Mailing/opened',
238 'options' => ['metadata' => ['labels', 'title']],
239 ], __FUNCTION__
, __FILE__
, $description, 'Getrows');
240 $this->assertEquals(5, $result['count']);
244 * Test api to get rows from reports.
246 * @dataProvider getReportTemplates
250 * @throws \CRM_Core_Exception
252 public function testReportTemplateGetRowsAllReports($reportID) {
253 //$reportID = 'logging/contact/summary';
254 if (stripos($reportID, 'has existing issues') !== FALSE) {
255 $this->markTestIncomplete($reportID);
257 if (strpos($reportID, 'logging') === 0) {
258 Civi
::settings()->set('logging', 1);
261 $this->callAPISuccess('report_template', 'getrows', [
262 'report_id' => $reportID,
264 if (strpos($reportID, 'logging') === 0) {
265 Civi
::settings()->set('logging', 0);
270 * Test logging report when a custom data table has a table removed by hook.
272 * Here we are checking that no fatal is triggered.
274 * @throws \CRM_Core_Exception
276 public function testLoggingReportWithHookRemovalOfCustomDataTable() {
277 Civi
::settings()->set('logging', 1);
278 $group1 = $this->customGroupCreate();
279 $group2 = $this->customGroupCreate(['name' => 'second_one', 'title' => 'second one', 'table_name' => 'civicrm_value_second_one']);
280 $this->customFieldCreate(['custom_group_id' => $group1['id'], 'label' => 'field one']);
281 $this->customFieldCreate(['custom_group_id' => $group2['id'], 'label' => 'field two']);
282 $this->hookClass
->setHook('civicrm_alterLogTables', [$this, 'alterLogTablesRemoveCustom']);
284 $this->callAPISuccess('report_template', 'getrows', [
285 'report_id' => 'logging/contact/summary',
287 Civi
::settings()->set('logging', 0);
288 $this->customGroupDelete($group1['id']);
289 $this->customGroupDelete($group2['id']);
293 * Remove one log table from the logging spec.
295 * @param array $logTableSpec
297 public function alterLogTablesRemoveCustom(&$logTableSpec) {
298 unset($logTableSpec['civicrm_value_second_one']);
302 * Test api to get rows from reports with ACLs enabled.
304 * Checking for lack of fatal error at the moment.
306 * @dataProvider getReportTemplates
310 * @throws \PHPUnit\Framework\IncompleteTestError
311 * @throws \CRM_Core_Exception
313 public function testReportTemplateGetRowsAllReportsACL($reportID) {
314 if (stripos($reportID, 'has existing issues') !== FALSE) {
315 $this->markTestIncomplete($reportID);
317 if (strpos($reportID, 'logging') === 0) {
318 Civi
::settings()->set('logging', 1);
320 $this->hookClass
->setHook('civicrm_aclWhereClause', [$this, 'aclWhereHookNoResults']);
321 $this->callAPISuccess('report_template', 'getrows', [
322 'report_id' => $reportID,
324 if (strpos($reportID, 'logging') === 0) {
325 Civi
::settings()->set('logging', 0);
330 * Test get statistics.
332 * @dataProvider getReportTemplates
336 * @throws \PHPUnit\Framework\IncompleteTestError
338 public function testReportTemplateGetStatisticsAllReports($reportID) {
339 if (stripos($reportID, 'has existing issues') !== FALSE) {
340 $this->markTestIncomplete($reportID);
342 if (in_array($reportID, ['contribute/softcredit', 'contribute/bookkeeping'])) {
343 $this->markTestIncomplete($reportID . ' has non enotices when calling statistics fn');
345 if (strpos($reportID, 'logging') === 0) {
346 Civi
::settings()->set('logging', 1);
348 $description = "Get Statistics from a report (note there isn't much data to get in the test DB).";
349 if ($reportID === 'contribute/summary') {
350 $this->hookClass
->setHook('civicrm_alterReportVar', [$this, 'alterReportVarHook']);
352 $this->callAPIAndDocument('report_template', 'getstatistics', [
353 'report_id' => $reportID,
354 ], __FUNCTION__
, __FILE__
, $description, 'Getstatistics');
355 if (strpos($reportID, 'logging') === 0) {
356 Civi
::settings()->set('logging', 0);
361 * Implements hook_civicrm_alterReportVar().
363 public function alterReportVarHook($varType, &$var, &$object) {
364 if ($varType === 'sql' && $object instanceof CRM_Report_Form_Contribute_Summary
) {
365 /* @var CRM_Report_Form $var */
366 $from = $var->getVar('_from');
367 $from .= ' LEFT JOIN civicrm_financial_type as temp ON temp.id = contribution_civireport.financial_type_id';
368 $var->setVar('_from', $from);
369 $where = $var->getVar('_where');
370 $where .= ' AND ( temp.id IS NOT NULL )';
371 $var->setVar('_where', $where);
376 * Data provider function for getting all templates.
378 * Note that the function needs to
379 * be static so cannot use $this->callAPISuccess
381 * @throws \CiviCRM_API3_Exception
383 public static function getReportTemplates() {
384 $reportTemplates = [];
386 'event/income' => 'I do no understand why but error is Call to undefined method CRM_Report_Form_Event_Income::from() in CRM/Report/Form.php on line 2120',
387 'contribute/history' => 'Declaration of CRM_Report_Form_Contribute_History::buildRows() should be compatible with CRM_Report_Form::buildRows($sql, &$rows)',
390 $reports = civicrm_api3('report_template', 'get', ['return' => 'value', 'options' => ['limit' => 500]]);
391 foreach ($reports['values'] as $report) {
392 if (empty($reportsToSkip[$report['value']])) {
393 $reportTemplates[] = [$report['value']];
396 $reportTemplates[] = [$report['value'] . ' has existing issues : ' . $reportsToSkip[$report['value']]];
400 return $reportTemplates;
404 * Get contribution templates that work with basic filter tests.
406 * These templates require minimal data config.
408 public static function getContributionReportTemplates() {
409 return [['contribute/summary'], ['contribute/detail'], ['contribute/repeat'], ['topDonor' => 'contribute/topDonor']];
413 * Get contribution templates that work with basic filter tests.
415 * These templates require minimal data config.
417 public static function getMembershipReportTemplates() {
418 return [['member/detail']];
422 * Get the membership and contribution reports to test.
426 public static function getMembershipAndContributionReportTemplatesForGroupTests() {
427 $templates = array_merge(self
::getContributionReportTemplates(), self
::getMembershipReportTemplates());
428 foreach ($templates as $key => $value) {
429 if (array_key_exists('topDonor', $value)) {
430 // Report is not standard enough to test here.
431 unset($templates[$key]);
439 * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
441 * @throws \CRM_Core_Exception
443 public function testLybuntReportWithData() {
444 $inInd = $this->individualCreate();
445 $outInd = $this->individualCreate();
446 $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-03-01']);
447 $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]);
448 $rows = $this->callAPISuccess('report_template', 'getrows', [
449 'report_id' => 'contribute/lybunt',
451 'yid_op' => 'calendar',
452 'options' => ['metadata' => ['sql']],
454 $this->assertEquals(1, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE));
458 * Test Lybunt report applies ACLs.
460 * @throws \CRM_Core_Exception
462 public function testLybuntReportWithDataAndACLFilter() {
463 CRM_Core_Config
::singleton()->userPermissionClass
->permissions
= ['administer CiviCRM'];
464 $inInd = $this->individualCreate();
465 $outInd = $this->individualCreate();
466 $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-03-01']);
467 $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]);
468 $this->hookClass
->setHook('civicrm_aclWhereClause', [$this, 'aclWhereHookNoResults']);
470 'report_id' => 'contribute/lybunt',
472 'yid_op' => 'calendar',
473 'options' => ['metadata' => ['sql']],
474 'check_permissions' => 1,
477 $rows = $this->callAPISuccess('report_template', 'getrows', $params);
478 $this->assertEquals(0, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE));
480 CRM_Utils_Hook
::singleton()->reset();
484 * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
486 * @throws \CRM_Core_Exception
488 public function testLybuntReportWithFYData() {
489 $inInd = $this->individualCreate();
490 $outInd = $this->individualCreate();
491 $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-10-01']);
492 $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]);
493 $this->callAPISuccess('Setting', 'create', ['fiscalYearStart' => ['M' => 7, 'd' => 1]]);
494 $rows = $this->callAPISuccess('report_template', 'getrows', [
495 'report_id' => 'contribute/lybunt',
497 'yid_op' => 'fiscal',
498 'options' => ['metadata' => ['sql']],
501 'column' => 'first_name',
507 $this->assertEquals(2, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE));
508 $inUseCollation = CRM_Core_BAO_SchemaHandler
::getInUseCollation();
509 $expected = preg_replace('/\s+/', ' ', 'COLLATE ' . $inUseCollation . ' AS
510 SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as cid FROM civicrm_contact contact_civireport INNER JOIN civicrm_contribution contribution_civireport USE index (received_date) ON contribution_civireport.contact_id = contact_civireport.id
511 AND contribution_civireport.is_test = 0
512 AND contribution_civireport.is_template = 0
513 AND contribution_civireport.receive_date BETWEEN \'20140701000000\' AND \'20150630235959\'
514 WHERE contact_civireport.id NOT IN (
515 SELECT cont_exclude.contact_id
516 FROM civicrm_contribution cont_exclude
517 WHERE cont_exclude.receive_date BETWEEN \'2015-7-1\' AND \'20160630235959\')
518 AND ( contribution_civireport.contribution_status_id IN (1) )
519 GROUP BY contact_civireport.id');
520 // Exclude whitespace in comparison as we don't care if it changes & this allows us to make the above readable.
521 $whitespacelessSql = preg_replace('/\s+/', ' ', $rows['metadata']['sql'][0]);
522 $this->assertStringContainsString($expected, $whitespacelessSql);
526 * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
528 * @throws \CRM_Core_Exception
530 public function testLybuntReportWithFYDataOrderByLastYearAmount() {
531 $inInd = $this->individualCreate();
532 $outInd = $this->individualCreate();
533 $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-10-01']);
534 $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]);
535 $this->callAPISuccess('Setting', 'create', ['fiscalYearStart' => ['M' => 7, 'd' => 1]]);
536 $rows = $this->callAPISuccess('report_template', 'getrows', [
537 'report_id' => 'contribute/lybunt',
539 'yid_op' => 'fiscal',
540 'options' => ['metadata' => ['sql']],
541 'fields' => ['first_name' => 1],
544 'column' => 'last_year_total_amount',
550 $this->assertEquals(2, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE));
554 * Test the group filter works on the contribution summary (with a smart
557 * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
559 * @param string $template
560 * Name of the template to test.
562 * @throws \CRM_Core_Exception
563 * @throws \CiviCRM_API3_Exception
565 public function testContributionSummaryWithSmartGroupFilter(string $template): void
{
566 $groupID = $this->setUpPopulatedSmartGroup();
567 $rows = $this->callAPISuccess('report_template', 'getrows', [
568 'report_id' => $template,
569 'gid_value' => $groupID,
571 'options' => ['metadata' => ['sql']],
573 $this->assertNumberOfContactsInResult(3, $rows, $template);
574 if ($template === 'contribute/summary') {
575 $this->assertEquals(3, $rows['values'][0]['civicrm_contribution_total_amount_count']);
580 * Test the group filter works on the contribution summary.
582 * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
584 * @param string $template
586 * @throws \CRM_Core_Exception
587 * @throws \CiviCRM_API3_Exception
589 public function testContributionSummaryWithNotINSmartGroupFilter($template): void
{
590 $groupID = $this->setUpPopulatedSmartGroup();
591 $rows = $this->callAPISuccess('report_template', 'getrows', [
592 'report_id' => 'contribute/summary',
593 'gid_value' => $groupID,
595 'options' => ['metadata' => ['sql']],
597 $this->assertEquals(2, $rows['values'][0]['civicrm_contribution_total_amount_count']);
601 * Test no fatal on order by per https://lab.civicrm.org/dev/core/issues/739
603 * @throws \CRM_Core_Exception
605 public function testCaseDetailsCaseTypeHeader() {
606 $this->callAPISuccess('report_template', 'getrows', [
607 'report_id' => 'case/detail',
608 'fields' => ['subject' => 1, 'client_sort_name' => 1],
611 'column' => 'case_type_title',
620 * Test the group filter works on the contribution summary.
622 * @throws \CRM_Core_Exception
624 public function testContributionDetailSoftCredits() {
625 $contactID = $this->individualCreate();
626 $contactID2 = $this->individualCreate();
627 $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
628 $template = 'contribute/detail';
629 $rows = $this->callAPISuccess('report_template', 'getrows', [
630 'report_id' => $template,
631 'contribution_or_soft_value' => 'contributions_only',
632 'fields' => ['soft_credits' => 1, 'contribution_or_soft' => 1, 'sort_name' => 1],
633 'options' => ['metadata' => ['sql']],
636 "<a href='/index.php?q=civicrm/contact/view&reset=1&cid=" . $contactID2 . "'>Anderson, Anthony</a> $ 5.00",
637 $rows['values'][0]['civicrm_contribution_soft_credits']
642 * Test the amount column is populated on soft credit details.
644 * @throws \CRM_Core_Exception
646 public function testContributionDetailSoftCreditsOnly() {
647 $contactID = $this->individualCreate();
648 $contactID2 = $this->individualCreate();
649 $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
650 $template = 'contribute/detail';
651 $rows = $this->callAPISuccess('report_template', 'getrows', [
652 'report_id' => $template,
653 'contribution_or_soft_value' => 'soft_credits_only',
657 'financial_type_id' => '1',
658 'receive_date' => '1',
659 'total_amount' => '1',
661 'options' => ['metadata' => ['sql', 'labels']],
663 foreach (array_keys($rows['metadata']['labels']) as $header) {
664 $this->assertNotEmpty($rows['values'][0][$header]);
669 * Test the group filter works on the various reports.
671 * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
673 * @param string $template
674 * Report template unique identifier.
676 * @throws \CRM_Core_Exception
678 public function testReportsWithNonSmartGroupFilter($template) {
679 $groupID = $this->setUpPopulatedGroup();
680 $rows = $this->callAPISuccess('report_template', 'getrows', [
681 'report_id' => $template,
682 'gid_value' => [$groupID],
684 'options' => ['metadata' => ['sql']],
686 $this->assertNumberOfContactsInResult(1, $rows, $template);
690 * Assert the included results match the expected.
692 * There may or may not be a group by in play so the assertion varies a little.
694 * @param int $numberExpected
696 * Rows returned from the report.
697 * @param string $template
699 protected function assertNumberOfContactsInResult($numberExpected, $rows, $template) {
700 if (isset($rows['values'][0]['civicrm_contribution_total_amount_count'])) {
701 $this->assertEquals($numberExpected, $rows['values'][0]['civicrm_contribution_total_amount_count'], 'wrong row count in ' . $template);
704 $this->assertCount($numberExpected, $rows['values'], 'wrong row count in ' . $template);
709 * Test the group filter works on the contribution summary when 2 groups are involved.
711 * @throws \CRM_Core_Exception
713 public function testContributionSummaryWithTwoGroups() {
714 $groupID = $this->setUpPopulatedGroup();
715 $groupID2 = $this->setUpPopulatedSmartGroup();
716 $rows = $this->callAPISuccess('report_template', 'getrows', [
717 'report_id' => 'contribute/summary',
718 'gid_value' => [$groupID, $groupID2],
720 'options' => ['metadata' => ['sql']],
722 $this->assertEquals(4, $rows['values'][0]['civicrm_contribution_total_amount_count']);
726 * Test we don't get a fatal grouping by only contribution status id.
728 * @throws \CRM_Core_Exception
730 public function testContributionSummaryGroupByContributionStatus() {
732 'report_id' => 'contribute/summary',
733 'fields' => ['total_amount' => 1, 'country_id' => 1],
734 'group_bys' => ['contribution_status_id' => 1],
735 'options' => ['metadata' => ['sql']],
737 $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
738 $this->assertStringContainsString('GROUP BY contribution_civireport.contribution_status_id WITH ROLLUP', $rowsSql[0]);
739 $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
740 $this->assertStringContainsString('GROUP BY contribution_civireport.contribution_status_id, currency', $statsSql[2]);
744 * Test we don't get a fatal grouping by only contribution status id.
746 * @throws \CRM_Core_Exception
748 public function testContributionSummaryGroupByYearFrequency() {
750 'report_id' => 'contribute/summary',
751 'fields' => ['total_amount' => 1, 'country_id' => 1],
752 'group_bys' => ['receive_date' => 1],
753 'group_bys_freq' => ['receive_date' => 'YEAR'],
754 'options' => ['metadata' => ['sql']],
756 $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
757 $this->assertStringContainsString('GROUP BY YEAR(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]);
758 $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
759 $this->assertStringContainsString('GROUP BY YEAR(contribution_civireport.receive_date), currency', $statsSql[2]);
763 * Test we don't get a fatal grouping with QUARTER frequency.
765 * @throws \CRM_Core_Exception
767 public function testContributionSummaryGroupByYearQuarterFrequency() {
769 'report_id' => 'contribute/summary',
770 'fields' => ['total_amount' => 1, 'country_id' => 1],
771 'group_bys' => ['receive_date' => 1],
772 'group_bys_freq' => ['receive_date' => 'QUARTER'],
773 'options' => ['metadata' => ['sql']],
775 $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
776 $this->assertStringContainsString('GROUP BY YEAR(contribution_civireport.receive_date), QUARTER(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]);
777 $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
778 $this->assertStringContainsString('GROUP BY YEAR(contribution_civireport.receive_date), QUARTER(contribution_civireport.receive_date), currency', $statsSql[2]);
782 * Test we don't get a fatal grouping with QUARTER frequency.
784 * @throws \CRM_Core_Exception
786 public function testContributionSummaryGroupByDateFrequency() {
788 'report_id' => 'contribute/summary',
789 'fields' => ['total_amount' => 1, 'country_id' => 1],
790 'group_bys' => ['receive_date' => 1],
791 'group_bys_freq' => ['receive_date' => 'DATE'],
792 'options' => ['metadata' => ['sql']],
794 $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
795 $this->assertStringContainsString('GROUP BY DATE(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]);
796 $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
797 $this->assertStringContainsString('GROUP BY DATE(contribution_civireport.receive_date), currency', $statsSql[2]);
801 * Test we don't get a fatal grouping with QUARTER frequency.
803 * @throws \CRM_Core_Exception
805 public function testContributionSummaryGroupByWeekFrequency() {
807 'report_id' => 'contribute/summary',
808 'fields' => ['total_amount' => 1, 'country_id' => 1],
809 'group_bys' => ['receive_date' => 1],
810 'group_bys_freq' => ['receive_date' => 'YEARWEEK'],
811 'options' => ['metadata' => ['sql']],
813 $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
814 $this->assertStringContainsString('GROUP BY YEARWEEK(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]);
815 $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
816 $this->assertStringContainsString('GROUP BY YEARWEEK(contribution_civireport.receive_date), currency', $statsSql[2]);
820 * CRM-20640: Test the group filter works on the contribution summary when a single contact in 2 groups.
822 * @throws \CRM_Core_Exception
824 public function testContributionSummaryWithSingleContactsInTwoGroups(): void
{
825 [$groupID1, $individualID] = $this->setUpPopulatedGroup(TRUE);
826 // create second group and add the individual to it.
827 $groupID2 = $this->groupCreate(['name' => 'test_group', 'title' => 'test_title']);
828 $this->callAPISuccess('GroupContact', 'create', [
829 'group_id' => $groupID2,
830 'contact_id' => $individualID,
834 $rows = $this->callAPISuccess('report_template', 'getrows', [
835 'report_id' => 'contribute/summary',
836 'gid_value' => [$groupID1, $groupID2],
838 'options' => ['metadata' => ['sql']],
840 $this->assertEquals(1, $rows['count']);
844 * Test the group filter works on the contribution summary when 2 groups are involved.
846 * @throws \CRM_Core_Exception
848 public function testContributionSummaryWithTwoGroupsWithIntersection(): void
{
849 $groups = $this->setUpIntersectingGroups();
851 $rows = $this->callAPISuccess('report_template', 'getrows', [
852 'report_id' => 'contribute/summary',
853 'gid_value' => $groups,
855 'options' => ['metadata' => ['sql']],
857 $this->assertEquals(7, $rows['values'][0]['civicrm_contribution_total_amount_count']);
861 * Test date field is correctly handled.
863 * @throws \CRM_Core_Exception
865 public function testContributionSummaryDateFields(): void
{
866 $sql = $this->callAPISuccess('report_template', 'getrows', [
867 'report_id' => 'contribute/summary',
868 'thankyou_date_relative' => '0',
869 'thankyou_date_from' => '2020-03-01 00:00:00',
870 'thankyou_date_to' => '2020-03-31 23:59:59',
871 'options' => ['metadata' => ['sql']],
872 ])['metadata']['sql'];
873 $expectedSql = 'SELECT contact_civireport.id as civicrm_contact_id, DATE_SUB(contribution_civireport.receive_date, INTERVAL (DAYOFMONTH(contribution_civireport.receive_date)-1) DAY) as civicrm_contribution_receive_date_start, MONTH(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_subtotal, MONTHNAME(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_interval, contribution_civireport.currency as civicrm_contribution_currency, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount_sum, ROUND(AVG(contribution_civireport.total_amount),2) as civicrm_contribution_total_amount_avg, address_civireport.country_id as civicrm_address_country_id FROM civicrm_contact contact_civireport
874 INNER JOIN civicrm_contribution contribution_civireport
875 ON contact_civireport.id = contribution_civireport.contact_id AND
876 contribution_civireport.is_test = 0
877 AND contribution_civireport.is_template = 0
878 LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
879 ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = contribution_civireport.id)
880 LEFT JOIN civicrm_financial_type financial_type_civireport
881 ON contribution_civireport.financial_type_id =financial_type_civireport.id
883 LEFT JOIN civicrm_address address_civireport
884 ON (contact_civireport.id =
885 address_civireport.contact_id) AND
886 address_civireport.is_primary = 1
887 WHERE ( contribution_civireport.thankyou_date >= 20200301000000) AND ( contribution_civireport.thankyou_date <= 20200331235959) AND ( contribution_civireport.contribution_status_id IN (1) ) GROUP BY EXTRACT(YEAR_MONTH FROM contribution_civireport.receive_date), contribution_civireport.contribution_status_id LIMIT 25';
888 $this->assertLike($expectedSql, $sql[0]);
892 * Set up a smart group for testing.
894 * The smart group includes all Households by filter. In addition an
895 * individual is created and hard-added and an individual is created that is
898 * One household is hard-added as well as being in the filter.
900 * This gives us a range of scenarios for testing contacts are included only
901 * once whenever they are hard-added or in the criteria.
904 * @throws \CRM_Core_Exception
905 * @throws \CiviCRM_API3_Exception
907 public function setUpPopulatedSmartGroup(): int {
908 $household1ID = $this->householdCreate();
909 $individual1ID = $this->individualCreate();
910 $householdID = $this->householdCreate();
911 $individualID = $this->individualCreate();
912 $individualIDRemoved = $this->individualCreate();
913 $groupID = $this->smartGroupCreate([], ['name' => 'smart_group', 'title' => 'smart group']);
914 $this->callAPISuccess('GroupContact', 'create', [
915 'group_id' => $groupID,
916 'contact_id' => $individualIDRemoved,
917 'status' => 'Removed',
919 $this->callAPISuccess('GroupContact', 'create', [
920 'group_id' => $groupID,
921 'contact_id' => $individualID,
924 $this->callAPISuccess('GroupContact', 'create', [
925 'group_id' => $groupID,
926 'contact_id' => $householdID,
929 foreach ([$household1ID, $individual1ID, $householdID, $individualID, $individualIDRemoved] as $contactID) {
930 $this->contributionCreate(['contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => '']);
931 $this->contactMembershipCreate(['contact_id' => $contactID]);
934 // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
935 CRM_Contact_BAO_GroupContactCache
::invalidateGroupContactCache($groupID);
940 * Set up a static group for testing.
942 * An individual is created and hard-added and an individual is created that is not added.
944 * This gives us a range of scenarios for testing contacts are included only once
945 * whenever they are hard-added or in the criteria.
947 * @param bool $returnAddedContact
950 * @throws \CRM_Core_Exception
952 public function setUpPopulatedGroup($returnAddedContact = FALSE) {
953 $individual1ID = $this->individualCreate();
954 $individualID = $this->individualCreate();
955 $individualIDRemoved = $this->individualCreate();
956 $groupID = $this->groupCreate(['name' => uniqid(), 'title' => uniqid()]);
957 $this->callAPISuccess('GroupContact', 'create', [
958 'group_id' => $groupID,
959 'contact_id' => $individualIDRemoved,
960 'status' => 'Removed',
962 $this->callAPISuccess('GroupContact', 'create', [
963 'group_id' => $groupID,
964 'contact_id' => $individualID,
968 foreach ([$individual1ID, $individualID, $individualIDRemoved] as $contactID) {
969 $this->contributionCreate(['contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => '']);
970 $this->contactMembershipCreate(['contact_id' => $contactID]);
973 // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
974 CRM_Contact_BAO_GroupContactCache
::invalidateGroupContactCache($groupID);
976 if ($returnAddedContact) {
977 return [$groupID, $individualID];
986 * @throws \CRM_Core_Exception
988 public function setUpIntersectingGroups() {
989 $groupID = $this->setUpPopulatedGroup();
990 $groupID2 = $this->setUpPopulatedSmartGroup();
991 $addedToBothIndividualID = $this->individualCreate();
992 $removedFromBothIndividualID = $this->individualCreate();
993 $addedToSmartGroupRemovedFromOtherIndividualID = $this->individualCreate();
994 $removedFromSmartGroupAddedToOtherIndividualID = $this->individualCreate();
995 $this->callAPISuccess('GroupContact', 'create', [
996 'group_id' => $groupID,
997 'contact_id' => $addedToBothIndividualID,
1000 $this->callAPISuccess('GroupContact', 'create', [
1001 'group_id' => $groupID2,
1002 'contact_id' => $addedToBothIndividualID,
1003 'status' => 'Added',
1005 $this->callAPISuccess('GroupContact', 'create', [
1006 'group_id' => $groupID,
1007 'contact_id' => $removedFromBothIndividualID,
1008 'status' => 'Removed',
1010 $this->callAPISuccess('GroupContact', 'create', [
1011 'group_id' => $groupID2,
1012 'contact_id' => $removedFromBothIndividualID,
1013 'status' => 'Removed',
1015 $this->callAPISuccess('GroupContact', 'create', [
1016 'group_id' => $groupID2,
1017 'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
1018 'status' => 'Added',
1020 $this->callAPISuccess('GroupContact', 'create', [
1021 'group_id' => $groupID,
1022 'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
1023 'status' => 'Removed',
1025 $this->callAPISuccess('GroupContact', 'create', [
1026 'group_id' => $groupID,
1027 'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
1028 'status' => 'Added',
1030 $this->callAPISuccess('GroupContact', 'create', [
1031 'group_id' => $groupID2,
1032 'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
1033 'status' => 'Removed',
1037 $addedToBothIndividualID,
1038 $removedFromBothIndividualID,
1039 $addedToSmartGroupRemovedFromOtherIndividualID,
1040 $removedFromSmartGroupAddedToOtherIndividualID,
1042 $this->contributionCreate([
1043 'contact_id' => $contactID,
1048 return [$groupID, $groupID2];
1052 * Test Deferred Revenue Report.
1054 * @throws \CRM_Core_Exception
1055 * @throws \CiviCRM_API3_Exception
1057 public function testDeferredRevenueReport(): void
{
1058 $indv1 = $this->individualCreate();
1059 $indv2 = $this->individualCreate();
1060 Civi
::settings()->set('deferred_revenue_enabled', TRUE);
1061 $this->contributionCreate(
1063 'contact_id' => $indv1,
1064 'receive_date' => '2016-10-01',
1065 'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+3 month')),
1066 'financial_type_id' => 2,
1069 $this->contributionCreate(
1071 'contact_id' => $indv1,
1072 'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+22 month')),
1073 'financial_type_id' => 4,
1075 'invoice_id' => NULL,
1078 $this->contributionCreate(
1080 'contact_id' => $indv2,
1081 'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+1 month')),
1082 'financial_type_id' => 4,
1084 'invoice_id' => NULL,
1087 $this->contributionCreate(
1089 'contact_id' => $indv2,
1090 'receive_date' => '2016-03-01',
1091 'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+4 month')),
1092 'financial_type_id' => 2,
1094 'invoice_id' => NULL,
1097 $rows = $this->callAPISuccess('report_template', 'getrows', [
1098 'report_id' => 'contribute/deferredrevenue',
1100 $this->assertEquals(2, $rows['count'], 'Report failed to get row count');
1102 foreach ($rows['values'] as $row) {
1103 $this->assertCount(array_pop($count), $row['rows'], 'Report failed to get row count');
1108 * Test the custom data order by works when not in select.
1110 * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
1112 * @param string $template
1113 * Report template unique identifier.
1115 * @throws \API_Exception
1116 * @throws \CRM_Core_Exception
1117 * @throws \Civi\API\Exception\UnauthorizedException
1119 public function testReportsCustomDataOrderBy($template) {
1120 $this->entity
= 'Contact';
1121 $this->createCustomGroupWithFieldOfType();
1122 $this->callAPISuccess('report_template', 'getrows', [
1123 'report_id' => $template,
1124 'contribution_or_soft_value' => 'contributions_only',
1125 'order_bys' => [['column' => 'custom_' . $this->ids
['CustomField']['text'], 'order' => 'ASC']],
1130 * Test the group filter works on the various reports.
1132 * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
1134 * @param string $template
1135 * Report template unique identifier.
1137 * @throws \CRM_Core_Exception
1139 public function testReportsWithNoTInSmartGroupFilter($template) {
1140 $groupID = $this->setUpPopulatedGroup();
1141 $rows = $this->callAPISuccess('report_template', 'getrows', [
1142 'report_id' => $template,
1143 'gid_value' => [$groupID],
1144 'gid_op' => 'notin',
1145 'options' => ['metadata' => ['sql']],
1147 $this->assertNumberOfContactsInResult(2, $rows, $template);
1151 * Test we don't get a fatal grouping with various frequencies.
1153 * @throws \CRM_Core_Exception
1155 public function testActivitySummaryGroupByFrequency() {
1156 $this->createContactsWithActivities();
1157 foreach (['MONTH', 'YEARWEEK', 'QUARTER', 'YEAR'] as $frequency) {
1159 'report_id' => 'activitySummary',
1161 'activity_type_id' => 1,
1163 // id is "total activities", which is required by default(?)
1166 'group_bys' => ['activity_date_time' => 1],
1167 'group_bys_freq' => ['activity_date_time' => $frequency],
1168 'options' => ['metadata' => ['sql']],
1170 $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
1171 $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
1172 switch ($frequency) {
1174 // Year only contains one grouping.
1175 // Also note the extra space.
1176 $this->assertStringContainsString('GROUP BY YEAR(activity_civireport.activity_date_time)', $rowsSql[1], "Failed for frequency $frequency");
1177 $this->assertStringContainsString('GROUP BY YEAR(activity_civireport.activity_date_time)', $statsSql[1], "Failed for frequency $frequency");
1181 $this->assertStringContainsString("GROUP BY YEAR(activity_civireport.activity_date_time), {$frequency}(activity_civireport.activity_date_time)", $rowsSql[1], "Failed for frequency $frequency");
1182 $this->assertStringContainsString("GROUP BY YEAR(activity_civireport.activity_date_time), {$frequency}(activity_civireport.activity_date_time)", $statsSql[1], "Failed for frequency $frequency");
1189 * Test activity details report - requiring all current fields to be output.
1191 * @throws \CRM_Core_Exception
1193 public function testActivityDetails() {
1194 $this->createContactsWithActivities();
1196 'contact_source' => '1',
1197 'contact_assignee' => '1',
1198 'contact_target' => '1',
1199 'contact_source_email' => '1',
1200 'contact_assignee_email' => '1',
1201 'contact_target_email' => '1',
1202 'contact_source_phone' => '1',
1203 'contact_assignee_phone' => '1',
1204 'contact_target_phone' => '1',
1205 'activity_type_id' => '1',
1206 'activity_subject' => '1',
1207 'activity_date_time' => '1',
1212 'priority_id' => '1',
1214 'engagement_level' => '1',
1215 'address_name' => '1',
1216 'street_address' => '1',
1217 'supplemental_address_1' => '1',
1218 'supplemental_address_2' => '1',
1219 'supplemental_address_3' => '1',
1220 'street_number' => '1',
1221 'street_name' => '1',
1222 'street_unit' => '1',
1224 'postal_code' => '1',
1225 'postal_code_suffix' => '1',
1226 'country_id' => '1',
1227 'state_province_id' => '1',
1231 'fields' => $fields,
1232 'current_user_op' => 'eq',
1233 'current_user_value' => '0',
1234 'include_case_activities_op' => 'eq',
1235 'include_case_activities_value' => 0,
1237 1 => ['column' => 'activity_date_time', 'order' => 'ASC'],
1238 2 => ['column' => 'activity_type_id', 'order' => 'ASC'],
1242 $params['report_id'] = 'Activity';
1244 $rows = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1246 'civicrm_contact_contact_source' => 'Łąchowski-Roberts, Anthony',
1247 'civicrm_contact_contact_assignee' => '<a title=\'View Contact Summary for this Contact\' href=\'/index.php?q=civicrm/contact/view&reset=1&cid=4\'>Łąchowski-Roberts, Anthony</a>',
1248 'civicrm_contact_contact_target' => '<a title=\'View Contact Summary for this Contact\' href=\'/index.php?q=civicrm/contact/view&reset=1&cid=3\'>Brzęczysław, Anthony</a>; <a title=\'View Contact Summary for this Contact\' href=\'/index.php?q=civicrm/contact/view&reset=1&cid=4\'>Łąchowski-Roberts, Anthony</a>',
1249 'civicrm_contact_contact_source_id' => $this->contactIDs
[2],
1250 'civicrm_contact_contact_assignee_id' => $this->contactIDs
[1],
1251 'civicrm_contact_contact_target_id' => $this->contactIDs
[0] . ';' . $this->contactIDs
[1],
1252 'civicrm_email_contact_source_email' => 'anthony_anderson@civicrm.org',
1253 'civicrm_email_contact_assignee_email' => 'anthony_anderson@civicrm.org',
1254 'civicrm_email_contact_target_email' => 'techo@spying.com;anthony_anderson@civicrm.org',
1255 'civicrm_phone_contact_source_phone' => NULL,
1256 'civicrm_phone_contact_assignee_phone' => NULL,
1257 'civicrm_phone_contact_target_phone' => NULL,
1258 'civicrm_activity_id' => '1',
1259 'civicrm_activity_source_record_id' => NULL,
1260 'civicrm_activity_activity_type_id' => 'Meeting',
1261 'civicrm_activity_activity_subject' => 'Very secret meeting',
1262 'civicrm_activity_activity_date_time' => date('Y-m-d 23:59:58'),
1263 'civicrm_activity_status_id' => 'Scheduled',
1264 'civicrm_activity_duration' => '120',
1265 'civicrm_activity_location' => 'Pennsylvania',
1266 'civicrm_activity_details' => 'a test activity',
1267 'civicrm_activity_priority_id' => 'Normal',
1268 'civicrm_address_address_name' => NULL,
1269 'civicrm_address_street_address' => NULL,
1270 'civicrm_address_supplemental_address_1' => NULL,
1271 'civicrm_address_supplemental_address_2' => NULL,
1272 'civicrm_address_supplemental_address_3' => NULL,
1273 'civicrm_address_street_number' => NULL,
1274 'civicrm_address_street_name' => NULL,
1275 'civicrm_address_street_unit' => NULL,
1276 'civicrm_address_city' => NULL,
1277 'civicrm_address_postal_code' => NULL,
1278 'civicrm_address_postal_code_suffix' => NULL,
1279 'civicrm_address_country_id' => NULL,
1280 'civicrm_address_state_province_id' => NULL,
1281 'civicrm_address_county_id' => NULL,
1282 'civicrm_contact_contact_source_link' => '/index.php?q=civicrm/contact/view&reset=1&cid=' . $this->contactIDs
[2],
1283 'civicrm_contact_contact_source_hover' => 'View Contact Summary for this Contact',
1284 'civicrm_activity_activity_type_id_hover' => 'View Activity Record',
1287 // This link is not relative - skip for now
1288 unset($row['civicrm_activity_activity_type_id_link']);
1289 if ($row['civicrm_email_contact_target_email'] === 'anthony_anderson@civicrm.org;techo@spying.com') {
1290 // order is unpredictable
1291 $expected['civicrm_email_contact_target_email'] = 'anthony_anderson@civicrm.org;techo@spying.com';
1294 $this->assertEquals($expected, $row);
1298 * Activity Details report has some whack-a-mole to fix when filtering on null/not null.
1300 * @throws \CRM_Core_Exception
1302 public function testActivityDetailsNullFilters() {
1303 $this->createContactsWithActivities();
1305 'report_id' => 'activity',
1306 'location_op' => 'nll',
1307 'location_value' => '',
1309 $rowsWithoutLocation = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1310 $this->assertEmpty($rowsWithoutLocation);
1311 $params['location_op'] = 'nnll';
1312 $rowsWithLocation = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1313 $this->assertCount(1, $rowsWithLocation);
1314 // Test for CRM-18356 - activity shouldn't appear if target contact filter is null.
1316 'report_id' => 'activity',
1317 'contact_target_op' => 'nll',
1318 'contact_target_value' => '',
1320 $rowsWithNullTarget = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1321 $this->assertEmpty($rowsWithNullTarget);
1325 * Test the source contact filter works.
1327 * @throws \CRM_Core_Exception
1329 public function testActivityDetailsContactFilter() {
1330 $this->createContactsWithActivities();
1332 'report_id' => 'activity',
1333 'contact_source_op' => 'has',
1334 'contact_source_value' => 'z',
1335 'options' => ['metadata' => ['sql']],
1337 $rows = $this->callAPISuccess('report_template', 'getrows', $params);
1338 $this->assertStringContainsString("civicrm_contact_source.sort_name LIKE '%z%'", $rows['metadata']['sql'][3]);
1342 * Set up some activity data..... use some chars that challenge our utf handling.
1344 * @throws \CRM_Core_Exception
1346 public function createContactsWithActivities() {
1347 $this->contactIDs
[] = $this->individualCreate(['last_name' => 'Brzęczysław', 'email' => 'techo@spying.com']);
1348 $this->contactIDs
[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']);
1349 $this->contactIDs
[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']);
1351 $this->activityID
= $this->callAPISuccess('Activity', 'create', [
1352 'subject' => 'Very secret meeting',
1353 'activity_date_time' => date('Y-m-d 23:59:58'),
1355 'location' => 'Pennsylvania',
1356 'details' => 'a test activity',
1358 'activity_type_id' => 'Meeting',
1359 'source_contact_id' => $this->contactIDs
[2],
1360 'target_contact_id' => [$this->contactIDs
[0], $this->contactIDs
[1]],
1361 'assignee_contact_id' => $this->contactIDs
[1],
1366 * Test the group filter works on the contribution summary.
1368 * @throws \CRM_Core_Exception
1370 public function testContributionDetailTotalHeader() {
1371 $contactID = $this->individualCreate();
1372 $contactID2 = $this->individualCreate();
1373 $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
1374 $template = 'contribute/detail';
1375 $this->callAPISuccess('report_template', 'getrows', [
1376 'report_id' => $template,
1377 'contribution_or_soft_value' => 'contributions_only',
1383 'financial_type_id' => '1',
1384 'receive_date' => '1',
1385 'total_amount' => '1',
1387 'order_bys' => [['column' => 'sort_name', 'order' => 'ASC', 'section' => '1']],
1388 'options' => ['metadata' => ['sql']],
1393 * Test contact subtype filter on grant report.
1395 * @throws \CRM_Core_Exception
1397 public function testGrantReportSeparatedFilter() {
1398 $contactID = $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1399 $contactID2 = $this->individualCreate();
1400 $this->callAPISuccess('Grant', 'create', ['contact_id' => $contactID, 'status_id' => 1, 'grant_type_id' => 1, 'amount_total' => 1]);
1401 $this->callAPISuccess('Grant', 'create', ['contact_id' => $contactID2, 'status_id' => 1, 'grant_type_id' => 1, 'amount_total' => 1]);
1402 $rows = $this->callAPISuccess('report_template', 'getrows', [
1403 'report_id' => 'grant/detail',
1404 'contact_sub_type_op' => 'in',
1405 'contact_sub_type_value' => ['Student'],
1407 $this->assertEquals(1, $rows['count']);
1411 * Test contact subtype filter on summary report.
1413 * @throws \CRM_Core_Exception
1415 public function testContactSubtypeNotNull() {
1416 $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1417 $this->individualCreate();
1419 $rows = $this->callAPISuccess('report_template', 'getrows', [
1420 'report_id' => 'contact/summary',
1421 'contact_sub_type_op' => 'nnll',
1422 'contact_sub_type_value' => [],
1423 'contact_type_op' => 'eq',
1424 'contact_type_value' => 'Individual',
1426 $this->assertEquals(1, $rows['count']);
1430 * Test contact subtype filter on summary report.
1432 * @throws \CRM_Core_Exception
1434 public function testContactSubtypeNull() {
1435 $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1436 $this->individualCreate();
1438 $rows = $this->callAPISuccess('report_template', 'getrows', [
1439 'report_id' => 'contact/summary',
1440 'contact_sub_type_op' => 'nll',
1441 'contact_sub_type_value' => [],
1442 'contact_type_op' => 'eq',
1443 'contact_type_value' => 'Individual',
1445 $this->assertEquals(1, $rows['count']);
1449 * Test contact subtype filter on summary report.
1451 * @throws \CRM_Core_Exception
1453 public function testContactSubtypeIn() {
1454 $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1455 $this->individualCreate();
1457 $rows = $this->callAPISuccess('report_template', 'getrows', [
1458 'report_id' => 'contact/summary',
1459 'contact_sub_type_op' => 'in',
1460 'contact_sub_type_value' => ['Student'],
1461 'contact_type_op' => 'in',
1462 'contact_type_value' => 'Individual',
1464 $this->assertEquals(1, $rows['count']);
1468 * Test contact subtype filter on summary report.
1470 * @throws \CRM_Core_Exception
1472 public function testContactSubtypeNotIn() {
1473 $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1474 $this->individualCreate();
1476 $rows = $this->callAPISuccess('report_template', 'getrows', [
1477 'report_id' => 'contact/summary',
1478 'contact_sub_type_op' => 'notin',
1479 'contact_sub_type_value' => ['Student'],
1480 'contact_type_op' => 'in',
1481 'contact_type_value' => 'Individual',
1483 $this->assertEquals(1, $rows['count']);
1487 * Test PCP report to ensure total donors and total committed is accurate.
1489 * @throws \CRM_Core_Exception
1491 public function testPcpReportTotals() {
1492 $donor1ContactId = $this->individualCreate();
1493 $donor2ContactId = $this->individualCreate();
1494 $donor3ContactId = $this->individualCreate();
1496 // We are going to create two PCP pages. We will create two contributions
1497 // on the first PCP page and one contribution on the second PCP page.
1499 // Then, we will ensure that the first PCP page reports a total of both
1500 // contributions (but not the contribution made on the second PCP page).
1502 // A PCP page requires three components:
1503 // 1. A contribution page
1507 // pcpBLockParams creates a contribution page and returns the parameters
1508 // necessary to create a PBP Block.
1509 $blockParams = $this->pcpBlockParams();
1510 $pcpBlock = CRM_PCP_BAO_PCPBlock
::create($blockParams);
1512 // Keep track of the contribution page id created. We will use this
1513 // contribution page id for all the PCP pages.
1514 $contribution_page_id = $pcpBlock->entity_id
;
1516 // pcpParams returns the parameters needed to create a PCP page.
1517 $pcpParams = $this->pcpParams();
1518 // Keep track of the owner of the page so we can properly apply the
1520 $pcpOwnerContact1Id = $pcpParams['contact_id'];
1521 $pcpParams['pcp_block_id'] = $pcpBlock->id
;
1522 $pcpParams['page_id'] = $contribution_page_id;
1523 $pcpParams['page_type'] = 'contribute';
1524 $pcp1 = CRM_PCP_BAO_PCP
::create($pcpParams);
1526 // Nice work. Now, let's create a second PCP page.
1527 $pcpParams = $this->pcpParams();
1528 // Keep track of the owner of the page.
1529 $pcpOwnerContact2Id = $pcpParams['contact_id'];
1530 // We're using the same pcpBlock id and contribution page that we created above.
1531 $pcpParams['pcp_block_id'] = $pcpBlock->id
;
1532 $pcpParams['page_id'] = $contribution_page_id;
1533 $pcpParams['page_type'] = 'contribute';
1534 $pcp2 = CRM_PCP_BAO_PCP
::create($pcpParams);
1536 // Get soft credit types, with the name column as the key.
1537 $soft_credit_types = CRM_Core_PseudoConstant
::get('CRM_Contribute_BAO_ContributionSoft', 'soft_credit_type_id', ['flip' => TRUE, 'labelColumn' => 'name']);
1538 $pcp_soft_credit_type_id = $soft_credit_types['pcp'];
1540 // Create two contributions assigned to this contribution page and
1541 // assign soft credits appropriately.
1543 $contribution1params = [
1544 'contact_id' => $donor1ContactId,
1545 'contribution_page_id' => $contribution_page_id,
1546 'total_amount' => '75.00',
1548 $c1 = $this->contributionCreate($contribution1params);
1549 // Now the soft contribution.
1551 'contribution_id' => $c1,
1552 'pcp_id' => $pcp1->id
,
1553 'contact_id' => $pcpOwnerContact1Id,
1555 'currency' => 'USD',
1556 'soft_credit_type_id' => $pcp_soft_credit_type_id,
1558 $this->callAPISuccess('contribution_soft', 'create', $p);
1560 $contribution2params = [
1561 'contact_id' => $donor2ContactId,
1562 'contribution_page_id' => $contribution_page_id,
1563 'total_amount' => '25.00',
1565 $c2 = $this->contributionCreate($contribution2params);
1566 // Now the soft contribution.
1568 'contribution_id' => $c2,
1569 'pcp_id' => $pcp1->id
,
1570 'contact_id' => $pcpOwnerContact1Id,
1572 'currency' => 'USD',
1573 'soft_credit_type_id' => $pcp_soft_credit_type_id,
1575 $this->callAPISuccess('contribution_soft', 'create', $p);
1577 // Create one contributions assigned to the second PCP page
1578 $contribution3params = [
1579 'contact_id' => $donor3ContactId,
1580 'contribution_page_id' => $contribution_page_id,
1581 'total_amount' => '200.00',
1583 $c3 = $this->contributionCreate($contribution3params);
1584 // Now the soft contribution.
1586 'contribution_id' => $c3,
1587 'pcp_id' => $pcp2->id
,
1588 'contact_id' => $pcpOwnerContact2Id,
1590 'currency' => 'USD',
1591 'soft_credit_type_id' => $pcp_soft_credit_type_id,
1593 $this->callAPISuccess('contribution_soft', 'create', $p);
1595 $template = 'contribute/pcp';
1596 $rows = $this->callAPISuccess('report_template', 'getrows', [
1597 'report_id' => $template,
1598 'title' => 'My PCP',
1604 $values = $rows['values'][0];
1605 $this->assertEquals(100.00, $values['civicrm_contribution_soft_amount_1_sum'], 'Total committed should be $100');
1606 $this->assertEquals(2, $values['civicrm_contribution_soft_soft_id_count'], 'Total donors should be 2');
1610 * Test a report that uses getAddressColumns();
1612 * @throws \CRM_Core_Exception
1614 public function testGetAddressColumns() {
1615 $template = 'event/participantlisting';
1616 $this->callAPISuccess('report_template', 'getrows', [
1617 'report_id' => $template,
1620 'street_address' => '1',
1626 * Convoluted test of the convoluted logging detail report.
1628 * In principle it's just make an update and get the report and see if it
1629 * matches the update.
1630 * In practice, besides some setup and trigger-wrangling, the report isn't
1631 * useful for activities, so we're checking activity_contact records, and
1632 * because of how an activity update works that's actually a delete+insert.
1634 public function testLoggingDetail() {
1635 \Civi
::settings()->set('logging', 1);
1636 $this->createContactsWithActivities();
1637 $this->doQuestionableStuffInASeparateFunctionSoNobodyNotices();
1639 // Do something that creates an update record.
1640 $this->callAPISuccess('Activity', 'create', [
1641 'id' => $this->activityID
,
1642 'assignee_contact_id' => $this->contactIDs
[0],
1643 'details' => 'Edited details',
1646 // In normal UI flow you would go to the summary report and drill down,
1647 // but here we need to go directly to the connection id, so find out what
1649 $queryParams = [1 => [$this->activityID
, 'Integer']];
1650 $log_conn_id = CRM_Core_DAO
::singleValueQuery("SELECT log_conn_id FROM log_civicrm_activity WHERE id = %1 AND log_action='UPDATE' LIMIT 1", $queryParams);
1652 // There should be only one instance of this after enabling so we can
1653 // just specify the template id as the lookup criteria.
1654 $instance_id = $this->callAPISuccess('report_instance', 'getsingle', [
1656 'report_id' => 'logging/contact/detail',
1659 $_GET = $_REQUEST = [
1661 'log_conn_id' => $log_conn_id,
1662 'q' => "civicrm/report/instance/$instance_id",
1664 $values = $this->callAPISuccess('report_template', 'getrows', [
1665 'report_id' => 'logging/contact/detail',
1668 // Note this is a delete+insert which is logically equivalent to update
1670 // here's the delete
1673 0 => 'Activity ID (id: 2)',
1674 1 => 'Contact ID (id: 2)',
1675 2 => 'Activity Contact Type (id: 2)',
1678 0 => 'Very secret meeting (id: 1)',
1679 1 => 'Mr. Anthony Łąchowski-Roberts II (id: 4)',
1680 2 => 'Activity Assignees',
1688 // this is the insert
1691 0 => 'Activity ID (id: 5)',
1692 1 => 'Contact ID (id: 5)',
1693 2 => 'Activity Contact Type (id: 5)',
1701 0 => 'Very secret meeting (id: 1)',
1702 1 => 'Mr. Anthony Brzęczysław II (id: 3)',
1703 2 => 'Activity Assignees',
1707 $this->assertEquals($expectedValues, $values);
1709 \Civi
::settings()->set('logging', 0);
1713 * The issue is that in a unit test the log_conn_id is going to
1714 * be the same throughout the entire test, which is not how it works normally
1715 * when you have separate page requests. So use the fact that the conn_id
1716 * format is controlled by a hidden variable, so we can force different
1717 * conn_id's during initialization and after.
1718 * If we don't do this, then the report thinks EVERY log record is part
1719 * of the one change detail.
1721 * On the plus side, this doesn't affect other tests since if they enable
1722 * logging then that'll just recreate the variable and triggers.
1724 private function doQuestionableStuffInASeparateFunctionSoNobodyNotices(): void
{
1725 CRM_Core_DAO
::executeQuery("DELETE FROM civicrm_setting WHERE name='logging_uniqueid_date'");
1726 // Now we have to rebuild triggers because the format formula is stored in
1728 CRM_Core_Config
::singleton(TRUE, TRUE);
1729 \Civi
::service('sql_triggers')->rebuild(NULL, TRUE);