tests/phpunit/api/v3/ReportTemplateTest.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *  Test APIv3 civicrm_report_instance_* functions
  14  *
  15  * @package CiviCRM_APIv3
  16  * @subpackage API_Report
  17  * @group headless
  18  */
  19 class api_v3_ReportTemplateTest extends CiviUnitTestCase {
  20
  21   use Civi\Test\ACLPermissionTrait;
  22   use CRMTraits_PCP_PCPTestTrait;
  23   use CRMTraits_Custom_CustomDataTrait;
  24
  25   protected $contactIDs = [];
  26
  27   /**
  28    * Our group reports use an alter so transaction cleanup won't work.
  29    *
  30    * @throws \Exception
  31    */
  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();
  36     parent::tearDown();
  37   }
  38
  39   /**
  40    * Test CRUD actions on a report template.
  41    *
  42    * @throws \CRM_Core_Exception
  43    */
  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',
  52     ]);
  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"');
  63
  64     // change component to null
  65     $result = $this->callAPISuccess('ReportTemplate', 'create', [
  66       'id' => $entityId,
  67       'component' => '',
  68     ]);
  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');
  77
  78     // deactivate
  79     $result = $this->callAPISuccess('ReportTemplate', 'create', [
  80       'id' => $entityId,
  81       'is_active' => 0,
  82     ]);
  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"');
  90
  91     // activate
  92     $result = $this->callAPISuccess('ReportTemplate', 'create', [
  93       'id' => $entityId,
  94       'is_active' => 1,
  95     ]);
  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"');
 103
 104     $result = $this->callAPISuccess('ReportTemplate', 'delete', [
 105       'id' => $entityId,
 106     ]);
 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"
 111       ');
 112   }
 113
 114   /**
 115    * Test api to get rows from reports.
 116    *
 117    * @dataProvider getReportTemplatesSupportingSelectWhere
 118    *
 119    * @param $reportID
 120    *
 121    * @throws \CRM_Core_Exception
 122    */
 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']],
 128     ]);
 129     $found = FALSE;
 130     foreach ($result['metadata']['sql'] as $sql) {
 131       if (strpos($sql, " =  'Organization' ") !== FALSE) {
 132         $found = TRUE;
 133       }
 134     }
 135     $this->assertTrue($found, $reportID);
 136   }
 137
 138   /**
 139    * Get templates suitable for SelectWhere test.
 140    *
 141    * @return array
 142    * @throws \CiviCRM_API3_Exception
 143    */
 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 = [
 148       'contribute/repeat',
 149       'member/summary',
 150       'event/summary',
 151       'case/summary',
 152       'case/timespent',
 153       'case/demographics',
 154       'contact/log',
 155       'contribute/bookkeeping',
 156       'grant/detail',
 157       'event/incomesummary',
 158       'case/detail',
 159       'Mailing/bounce',
 160       'Mailing/summary',
 161       'grant/statistics',
 162       'logging/contact/detail',
 163       'logging/contact/summary',
 164     ];
 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]);
 169       }
 170     }
 171     return $allTemplates;
 172   }
 173
 174   /**
 175    * @param \CRM_Core_DAO $entity
 176    * @param array $clauses
 177    */
 178   public function hookSelectWhere($entity, &$clauses) {
 179     // Restrict access to cases by type
 180     if ($entity === 'Contact') {
 181       $clauses['contact_type'][] = " =  'Organization' ";
 182     }
 183   }
 184
 185   /**
 186    * Test getrows on contact summary report.
 187    *
 188    * @throws \CRM_Core_Exception
 189    */
 190   public function testReportTemplateGetRowsContactSummary() {
 191     $result = $this->callAPISuccess('report_template', 'getrows', [
 192       'report_id' => 'contact/summary',
 193       'options' => ['metadata' => ['labels', 'title']],
 194     ]);
 195     $this->assertEquals('Contact Name', $result['metadata']['labels']['civicrm_contact_sort_name']);
 196
 197     //the second part of this test has been commented out because it relied on the db being reset to
 198     // it's base state
 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
 202     /*
 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']);
 207      */
 208   }
 209
 210   /**
 211    * Test getrows on Mailing Opened report.
 212    */
 213   public function testReportTemplateGetRowsMailingUniqueOpened() {
 214     $description = 'Retrieve rows from a mailing opened report template.';
 215     $this->loadXMLDataSet(__DIR__ . '/../../CRM/Mailing/BAO/queryDataset.xml');
 216
 217     // Check total rows without distinct
 218     global $_REQUEST;
 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']);
 225
 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']);
 233
 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']);
 241   }
 242
 243   /**
 244    * Test api to get rows from reports.
 245    *
 246    * @dataProvider getReportTemplates
 247    *
 248    * @param $reportID
 249    *
 250    * @throws \CRM_Core_Exception
 251    */
 252   public function testReportTemplateGetRowsAllReports($reportID) {
 253     //$reportID = 'logging/contact/summary';
 254     if (stripos($reportID, 'has existing issues') !== FALSE) {
 255       $this->markTestIncomplete($reportID);
 256     }
 257     if (strpos($reportID, 'logging') === 0) {
 258       Civi::settings()->set('logging', 1);
 259     }
 260
 261     $this->callAPISuccess('report_template', 'getrows', [
 262       'report_id' => $reportID,
 263     ]);
 264     if (strpos($reportID, 'logging') === 0) {
 265       Civi::settings()->set('logging', 0);
 266     }
 267   }
 268
 269   /**
 270    * Test logging report when a custom data table has a table removed by hook.
 271    *
 272    * Here we are checking that no fatal is triggered.
 273    *
 274    * @throws \CRM_Core_Exception
 275    */
 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']);
 283
 284     $this->callAPISuccess('report_template', 'getrows', [
 285       'report_id' => 'logging/contact/summary',
 286     ]);
 287     Civi::settings()->set('logging', 0);
 288     $this->customGroupDelete($group1['id']);
 289     $this->customGroupDelete($group2['id']);
 290   }
 291
 292   /**
 293    * Remove one log table from the logging spec.
 294    *
 295    * @param array $logTableSpec
 296    */
 297   public function alterLogTablesRemoveCustom(&$logTableSpec) {
 298     unset($logTableSpec['civicrm_value_second_one']);
 299   }
 300
 301   /**
 302    * Test api to get rows from reports with ACLs enabled.
 303    *
 304    * Checking for lack of fatal error at the moment.
 305    *
 306    * @dataProvider getReportTemplates
 307    *
 308    * @param $reportID
 309    *
 310    * @throws \PHPUnit\Framework\IncompleteTestError
 311    * @throws \CRM_Core_Exception
 312    */
 313   public function testReportTemplateGetRowsAllReportsACL($reportID) {
 314     if (stripos($reportID, 'has existing issues') !== FALSE) {
 315       $this->markTestIncomplete($reportID);
 316     }
 317     if (strpos($reportID, 'logging') === 0) {
 318       Civi::settings()->set('logging', 1);
 319     }
 320     $this->hookClass->setHook('civicrm_aclWhereClause', [$this, 'aclWhereHookNoResults']);
 321     $this->callAPISuccess('report_template', 'getrows', [
 322       'report_id' => $reportID,
 323     ]);
 324     if (strpos($reportID, 'logging') === 0) {
 325       Civi::settings()->set('logging', 0);
 326     }
 327   }
 328
 329   /**
 330    * Test get statistics.
 331    *
 332    * @dataProvider getReportTemplates
 333    *
 334    * @param $reportID
 335    *
 336    * @throws \PHPUnit\Framework\IncompleteTestError
 337    */
 338   public function testReportTemplateGetStatisticsAllReports($reportID) {
 339     if (stripos($reportID, 'has existing issues') !== FALSE) {
 340       $this->markTestIncomplete($reportID);
 341     }
 342     if (in_array($reportID, ['contribute/softcredit', 'contribute/bookkeeping'])) {
 343       $this->markTestIncomplete($reportID . ' has non enotices when calling statistics fn');
 344     }
 345     if (strpos($reportID, 'logging') === 0) {
 346       Civi::settings()->set('logging', 1);
 347     }
 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']);
 351     }
 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);
 357     }
 358   }
 359
 360   /**
 361    * Implements hook_civicrm_alterReportVar().
 362    */
 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);
 372     }
 373   }
 374
 375   /**
 376    * Data provider function for getting all templates.
 377    *
 378    * Note that the function needs to
 379    * be static so cannot use $this->callAPISuccess
 380    *
 381    * @throws \CiviCRM_API3_Exception
 382    */
 383   public static function getReportTemplates() {
 384     $reportTemplates = [];
 385     $reportsToSkip = [
 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)',
 388     ];
 389
 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']];
 394       }
 395       else {
 396         $reportTemplates[] = [$report['value'] . ' has existing issues :  ' . $reportsToSkip[$report['value']]];
 397       }
 398     }
 399
 400     return $reportTemplates;
 401   }
 402
 403   /**
 404    * Get contribution templates that work with basic filter tests.
 405    *
 406    * These templates require minimal data config.
 407    */
 408   public static function getContributionReportTemplates() {
 409     return [['contribute/summary'], ['contribute/detail'], ['contribute/repeat'], ['topDonor' => 'contribute/topDonor']];
 410   }
 411
 412   /**
 413    * Get contribution templates that work with basic filter tests.
 414    *
 415    * These templates require minimal data config.
 416    */
 417   public static function getMembershipReportTemplates() {
 418     return [['member/detail']];
 419   }
 420
 421   /**
 422    * Get the membership and contribution reports to test.
 423    *
 424    * @return array
 425    */
 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]);
 432       }
 433
 434     }
 435     return $templates;
 436   }
 437
 438   /**
 439    * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
 440    *
 441    * @throws \CRM_Core_Exception
 442    */
 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',
 450       'yid_value' => 2015,
 451       'yid_op' => 'calendar',
 452       'options' => ['metadata' => ['sql']],
 453     ]);
 454     $this->assertEquals(1, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE));
 455   }
 456
 457   /**
 458    * Test Lybunt report applies ACLs.
 459    *
 460    * @throws \CRM_Core_Exception
 461    */
 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']);
 469     $params = [
 470       'report_id' => 'contribute/lybunt',
 471       'yid_value' => 2015,
 472       'yid_op' => 'calendar',
 473       'options' => ['metadata' => ['sql']],
 474       'check_permissions' => 1,
 475     ];
 476
 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));
 479
 480     CRM_Utils_Hook::singleton()->reset();
 481   }
 482
 483   /**
 484    * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
 485    *
 486    * @throws \CRM_Core_Exception
 487    */
 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',
 496       'yid_value' => 2015,
 497       'yid_op' => 'fiscal',
 498       'options' => ['metadata' => ['sql']],
 499       'order_bys' => [
 500         [
 501           'column' => 'first_name',
 502           'order' => 'ASC',
 503         ],
 504       ],
 505     ]);
 506
 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);
 523   }
 524
 525   /**
 526    * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
 527    *
 528    * @throws \CRM_Core_Exception
 529    */
 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',
 538       'yid_value' => 2015,
 539       'yid_op' => 'fiscal',
 540       'options' => ['metadata' => ['sql']],
 541       'fields' => ['first_name' => 1],
 542       'order_bys' => [
 543         [
 544           'column' => 'last_year_total_amount',
 545           'order' => 'ASC',
 546         ],
 547       ],
 548     ]);
 549
 550     $this->assertEquals(2, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE));
 551   }
 552
 553   /**
 554    * Test the group filter works on the contribution summary (with a smart
 555    * group).
 556    *
 557    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
 558    *
 559    * @param string $template
 560    *   Name of the template to test.
 561    *
 562    * @throws \CRM_Core_Exception
 563    * @throws \CiviCRM_API3_Exception
 564    */
 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,
 570       'gid_op' => 'in',
 571       'options' => ['metadata' => ['sql']],
 572     ]);
 573     $this->assertNumberOfContactsInResult(3, $rows, $template);
 574     if ($template === 'contribute/summary') {
 575       $this->assertEquals(3, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 576     }
 577   }
 578
 579   /**
 580    * Test the group filter works on the contribution summary.
 581    *
 582    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
 583    *
 584    * @param string $template
 585    *
 586    * @throws \CRM_Core_Exception
 587    * @throws \CiviCRM_API3_Exception
 588    */
 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,
 594       'gid_op' => 'notin',
 595       'options' => ['metadata' => ['sql']],
 596     ]);
 597     $this->assertEquals(2, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 598   }
 599
 600   /**
 601    * Test no fatal on order by per https://lab.civicrm.org/dev/core/issues/739
 602    *
 603    * @throws \CRM_Core_Exception
 604    */
 605   public function testCaseDetailsCaseTypeHeader() {
 606     $this->callAPISuccess('report_template', 'getrows', [
 607       'report_id' => 'case/detail',
 608       'fields' => ['subject' => 1, 'client_sort_name' => 1],
 609       'order_bys' => [
 610         1 => [
 611           'column' => 'case_type_title',
 612           'order' => 'ASC',
 613           'section' => '1',
 614         ],
 615       ],
 616     ]);
 617   }
 618
 619   /**
 620    * Test the group filter works on the contribution summary.
 621    *
 622    * @throws \CRM_Core_Exception
 623    */
 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']],
 634     ]);
 635     $this->assertEquals(
 636       "<a href='/index.php?q=civicrm/contact/view&amp;reset=1&amp;cid=" . $contactID2 . "'>Anderson, Anthony</a> $ 5.00",
 637       $rows['values'][0]['civicrm_contribution_soft_credits']
 638     );
 639   }
 640
 641   /**
 642    * Test the amount column is populated on soft credit details.
 643    *
 644    * @throws \CRM_Core_Exception
 645    */
 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',
 654       'fields' => [
 655         'sort_name' => '1',
 656         'email' => '1',
 657         'financial_type_id' => '1',
 658         'receive_date' => '1',
 659         'total_amount' => '1',
 660       ],
 661       'options' => ['metadata' => ['sql', 'labels']],
 662     ]);
 663     foreach (array_keys($rows['metadata']['labels']) as $header) {
 664       $this->assertNotEmpty($rows['values'][0][$header]);
 665     }
 666   }
 667
 668   /**
 669    * Test the group filter works on the various reports.
 670    *
 671    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
 672    *
 673    * @param string $template
 674    *   Report template unique identifier.
 675    *
 676    * @throws \CRM_Core_Exception
 677    */
 678   public function testReportsWithNonSmartGroupFilter($template) {
 679     $groupID = $this->setUpPopulatedGroup();
 680     $rows = $this->callAPISuccess('report_template', 'getrows', [
 681       'report_id' => $template,
 682       'gid_value' => [$groupID],
 683       'gid_op' => 'in',
 684       'options' => ['metadata' => ['sql']],
 685     ]);
 686     $this->assertNumberOfContactsInResult(1, $rows, $template);
 687   }
 688
 689   /**
 690    * Assert the included results match the expected.
 691    *
 692    * There may or may not be a group by in play so the assertion varies a little.
 693    *
 694    * @param int $numberExpected
 695    * @param array $rows
 696    *   Rows returned from the report.
 697    * @param string $template
 698    */
 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);
 702     }
 703     else {
 704       $this->assertCount($numberExpected, $rows['values'], 'wrong row count in ' . $template);
 705     }
 706   }
 707
 708   /**
 709    * Test the group filter works on the contribution summary when 2 groups are involved.
 710    *
 711    * @throws \CRM_Core_Exception
 712    */
 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],
 719       'gid_op' => 'in',
 720       'options' => ['metadata' => ['sql']],
 721     ]);
 722     $this->assertEquals(4, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 723   }
 724
 725   /**
 726    * Test we don't get a fatal grouping  by only contribution status id.
 727    *
 728    * @throws \CRM_Core_Exception
 729    */
 730   public function testContributionSummaryGroupByContributionStatus() {
 731     $params = [
 732       'report_id' => 'contribute/summary',
 733       'fields' => ['total_amount' => 1, 'country_id' => 1],
 734       'group_bys' => ['contribution_status_id' => 1],
 735       'options' => ['metadata' => ['sql']],
 736     ];
 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]);
 741   }
 742
 743   /**
 744    * Test we don't get a fatal grouping  by only contribution status id.
 745    *
 746    * @throws \CRM_Core_Exception
 747    */
 748   public function testContributionSummaryGroupByYearFrequency() {
 749     $params = [
 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']],
 755     ];
 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]);
 760   }
 761
 762   /**
 763    * Test we don't get a fatal grouping with QUARTER frequency.
 764    *
 765    * @throws \CRM_Core_Exception
 766    */
 767   public function testContributionSummaryGroupByYearQuarterFrequency() {
 768     $params = [
 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']],
 774     ];
 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]);
 779   }
 780
 781   /**
 782    * Test we don't get a fatal grouping with QUARTER frequency.
 783    *
 784    * @throws \CRM_Core_Exception
 785    */
 786   public function testContributionSummaryGroupByDateFrequency() {
 787     $params = [
 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']],
 793     ];
 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]);
 798   }
 799
 800   /**
 801    * Test we don't get a fatal grouping with QUARTER frequency.
 802    *
 803    * @throws \CRM_Core_Exception
 804    */
 805   public function testContributionSummaryGroupByWeekFrequency() {
 806     $params = [
 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']],
 812     ];
 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]);
 817   }
 818
 819   /**
 820    * CRM-20640: Test the group filter works on the contribution summary when a single contact in 2 groups.
 821    *
 822    * @throws \CRM_Core_Exception
 823    */
 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,
 831       'status' => 'Added',
 832     ]);
 833
 834     $rows = $this->callAPISuccess('report_template', 'getrows', [
 835       'report_id' => 'contribute/summary',
 836       'gid_value' => [$groupID1, $groupID2],
 837       'gid_op' => 'in',
 838       'options' => ['metadata' => ['sql']],
 839     ]);
 840     $this->assertEquals(1, $rows['count']);
 841   }
 842
 843   /**
 844    * Test the group filter works on the contribution summary when 2 groups are involved.
 845    *
 846    * @throws \CRM_Core_Exception
 847    */
 848   public function testContributionSummaryWithTwoGroupsWithIntersection(): void {
 849     $groups = $this->setUpIntersectingGroups();
 850
 851     $rows = $this->callAPISuccess('report_template', 'getrows', [
 852       'report_id' => 'contribute/summary',
 853       'gid_value' => $groups,
 854       'gid_op' => 'in',
 855       'options' => ['metadata' => ['sql']],
 856     ]);
 857     $this->assertEquals(7, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 858   }
 859
 860   /**
 861    * Test date field is correctly handled.
 862    *
 863    * @throws \CRM_Core_Exception
 864    */
 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
 882
 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]);
 889   }
 890
 891   /**
 892    * Set up a smart group for testing.
 893    *
 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
 896    * not added.
 897    *
 898    * One household is hard-added as well as being in the filter.
 899    *
 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.
 902    *
 903    * @return int
 904    * @throws \CRM_Core_Exception
 905    * @throws \CiviCRM_API3_Exception
 906    */
 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',
 918     ]);
 919     $this->callAPISuccess('GroupContact', 'create', [
 920       'group_id' => $groupID,
 921       'contact_id' => $individualID,
 922       'status' => 'Added',
 923     ]);
 924     $this->callAPISuccess('GroupContact', 'create', [
 925       'group_id' => $groupID,
 926       'contact_id' => $householdID,
 927       'status' => 'Added',
 928     ]);
 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]);
 932     }
 933
 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);
 936     return $groupID;
 937   }
 938
 939   /**
 940    * Set up a static group for testing.
 941    *
 942    * An individual is created and hard-added and an individual is created that is not added.
 943    *
 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.
 946    *
 947    * @param bool $returnAddedContact
 948    *
 949    * @return int
 950    * @throws \CRM_Core_Exception
 951    */
 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',
 961     ]);
 962     $this->callAPISuccess('GroupContact', 'create', [
 963       'group_id' => $groupID,
 964       'contact_id' => $individualID,
 965       'status' => 'Added',
 966     ]);
 967
 968     foreach ([$individual1ID, $individualID, $individualIDRemoved] as $contactID) {
 969       $this->contributionCreate(['contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => '']);
 970       $this->contactMembershipCreate(['contact_id' => $contactID]);
 971     }
 972
 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);
 975
 976     if ($returnAddedContact) {
 977       return [$groupID, $individualID];
 978     }
 979
 980     return $groupID;
 981   }
 982
 983   /**
 984    * @return array
 985    *
 986    * @throws \CRM_Core_Exception
 987    */
 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,
 998       'status' => 'Added',
 999     ]);
1000     $this->callAPISuccess('GroupContact', 'create', [
1001       'group_id' => $groupID2,
1002       'contact_id' => $addedToBothIndividualID,
1003       'status' => 'Added',
1004     ]);
1005     $this->callAPISuccess('GroupContact', 'create', [
1006       'group_id' => $groupID,
1007       'contact_id' => $removedFromBothIndividualID,
1008       'status' => 'Removed',
1009     ]);
1010     $this->callAPISuccess('GroupContact', 'create', [
1011       'group_id' => $groupID2,
1012       'contact_id' => $removedFromBothIndividualID,
1013       'status' => 'Removed',
1014     ]);
1015     $this->callAPISuccess('GroupContact', 'create', [
1016       'group_id' => $groupID2,
1017       'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
1018       'status' => 'Added',
1019     ]);
1020     $this->callAPISuccess('GroupContact', 'create', [
1021       'group_id' => $groupID,
1022       'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
1023       'status' => 'Removed',
1024     ]);
1025     $this->callAPISuccess('GroupContact', 'create', [
1026       'group_id' => $groupID,
1027       'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
1028       'status' => 'Added',
1029     ]);
1030     $this->callAPISuccess('GroupContact', 'create', [
1031       'group_id' => $groupID2,
1032       'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
1033       'status' => 'Removed',
1034     ]);
1035
1036     foreach ([
1037       $addedToBothIndividualID,
1038       $removedFromBothIndividualID,
1039       $addedToSmartGroupRemovedFromOtherIndividualID,
1040       $removedFromSmartGroupAddedToOtherIndividualID,
1041     ] as $contactID) {
1042       $this->contributionCreate([
1043         'contact_id' => $contactID,
1044         'invoice_id' => '',
1045         'trxn_id' => '',
1046       ]);
1047     }
1048     return [$groupID, $groupID2];
1049   }
1050
1051   /**
1052    * Test Deferred Revenue Report.
1053    *
1054    * @throws \CRM_Core_Exception
1055    * @throws \CiviCRM_API3_Exception
1056    */
1057   public function testDeferredRevenueReport(): void {
1058     $indv1 = $this->individualCreate();
1059     $indv2 = $this->individualCreate();
1060     Civi::settings()->set('deferred_revenue_enabled', TRUE);
1061     $this->contributionCreate(
1062       [
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,
1067       ]
1068     );
1069     $this->contributionCreate(
1070       [
1071         'contact_id' => $indv1,
1072         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+22 month')),
1073         'financial_type_id' => 4,
1074         'trxn_id' => NULL,
1075         'invoice_id' => NULL,
1076       ]
1077     );
1078     $this->contributionCreate(
1079       [
1080         'contact_id' => $indv2,
1081         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+1 month')),
1082         'financial_type_id' => 4,
1083         'trxn_id' => NULL,
1084         'invoice_id' => NULL,
1085       ]
1086     );
1087     $this->contributionCreate(
1088       [
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,
1093         'trxn_id' => NULL,
1094         'invoice_id' => NULL,
1095       ]
1096     );
1097     $rows = $this->callAPISuccess('report_template', 'getrows', [
1098       'report_id' => 'contribute/deferredrevenue',
1099     ]);
1100     $this->assertEquals(2, $rows['count'], 'Report failed to get row count');
1101     $count = [2, 1];
1102     foreach ($rows['values'] as $row) {
1103       $this->assertCount(array_pop($count), $row['rows'], 'Report failed to get row count');
1104     }
1105   }
1106
1107   /**
1108    * Test the custom data order by works when not in select.
1109    *
1110    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
1111    *
1112    * @param string $template
1113    *   Report template unique identifier.
1114    *
1115    * @throws \API_Exception
1116    * @throws \CRM_Core_Exception
1117    * @throws \Civi\API\Exception\UnauthorizedException
1118    */
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']],
1126     ]);
1127   }
1128
1129   /**
1130    * Test the group filter works on the various reports.
1131    *
1132    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
1133    *
1134    * @param string $template
1135    *   Report template unique identifier.
1136    *
1137    * @throws \CRM_Core_Exception
1138    */
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']],
1146     ]);
1147     $this->assertNumberOfContactsInResult(2, $rows, $template);
1148   }
1149
1150   /**
1151    * Test we don't get a fatal grouping with various frequencies.
1152    *
1153    * @throws \CRM_Core_Exception
1154    */
1155   public function testActivitySummaryGroupByFrequency() {
1156     $this->createContactsWithActivities();
1157     foreach (['MONTH', 'YEARWEEK', 'QUARTER', 'YEAR'] as $frequency) {
1158       $params = [
1159         'report_id' => 'activitySummary',
1160         'fields' => [
1161           'activity_type_id' => 1,
1162           'duration' => 1,
1163           // id is "total activities", which is required by default(?)
1164           'id' => 1,
1165         ],
1166         'group_bys' => ['activity_date_time' => 1],
1167         'group_bys_freq' => ['activity_date_time' => $frequency],
1168         'options' => ['metadata' => ['sql']],
1169       ];
1170       $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
1171       $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
1172       switch ($frequency) {
1173         case 'YEAR':
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");
1178           break;
1179
1180         default:
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");
1183           break;
1184       }
1185     }
1186   }
1187
1188   /**
1189    * Test activity details report - requiring all current fields to be output.
1190    *
1191    * @throws \CRM_Core_Exception
1192    */
1193   public function testActivityDetails() {
1194     $this->createContactsWithActivities();
1195     $fields = [
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',
1208       'status_id' => '1',
1209       'duration' => '1',
1210       'location' => '1',
1211       'details' => '1',
1212       'priority_id' => '1',
1213       'result' => '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',
1223       'city' => '1',
1224       'postal_code' => '1',
1225       'postal_code_suffix' => '1',
1226       'country_id' => '1',
1227       'state_province_id' => '1',
1228       'county_id' => '1',
1229     ];
1230     $params = [
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,
1236       'order_bys' => [
1237         1 => ['column' => 'activity_date_time', 'order' => 'ASC'],
1238         2 => ['column' => 'activity_type_id', 'order' => 'ASC'],
1239       ],
1240     ];
1241
1242     $params['report_id'] = 'Activity';
1243
1244     $rows = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1245     $expected = [
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&amp;reset=1&amp;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&amp;reset=1&amp;cid=3\'>Brzęczysław, Anthony</a>; <a title=\'View Contact Summary for this Contact\' href=\'/index.php?q=civicrm/contact/view&amp;reset=1&amp;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&amp;reset=1&amp;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',
1285     ];
1286     $row = $rows[0];
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';
1292     }
1293
1294     $this->assertEquals($expected, $row);
1295   }
1296
1297   /**
1298    * Activity Details report has some whack-a-mole to fix when filtering on null/not null.
1299    *
1300    * @throws \CRM_Core_Exception
1301    */
1302   public function testActivityDetailsNullFilters() {
1303     $this->createContactsWithActivities();
1304     $params = [
1305       'report_id' => 'activity',
1306       'location_op' => 'nll',
1307       'location_value' => '',
1308     ];
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.
1315     $params = [
1316       'report_id' => 'activity',
1317       'contact_target_op' => 'nll',
1318       'contact_target_value' => '',
1319     ];
1320     $rowsWithNullTarget = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1321     $this->assertEmpty($rowsWithNullTarget);
1322   }
1323
1324   /**
1325    * Test the source contact filter works.
1326    *
1327    * @throws \CRM_Core_Exception
1328    */
1329   public function testActivityDetailsContactFilter() {
1330     $this->createContactsWithActivities();
1331     $params = [
1332       'report_id' => 'activity',
1333       'contact_source_op' => 'has',
1334       'contact_source_value' => 'z',
1335       'options' => ['metadata' => ['sql']],
1336     ];
1337     $rows = $this->callAPISuccess('report_template', 'getrows', $params);
1338     $this->assertStringContainsString("civicrm_contact_source.sort_name LIKE '%z%'", $rows['metadata']['sql'][3]);
1339   }
1340
1341   /**
1342    * Set up some activity data..... use some chars that challenge our utf handling.
1343    *
1344    * @throws \CRM_Core_Exception
1345    */
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']);
1350
1351     $this->activityID = $this->callAPISuccess('Activity', 'create', [
1352       'subject' => 'Very secret meeting',
1353       'activity_date_time' => date('Y-m-d 23:59:58'),
1354       'duration' => 120,
1355       'location' => 'Pennsylvania',
1356       'details' => 'a test activity',
1357       'status_id' => 1,
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],
1362     ])['id'];
1363   }
1364
1365   /**
1366    * Test the group filter works on the contribution summary.
1367    *
1368    * @throws \CRM_Core_Exception
1369    */
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',
1378       'fields' => [
1379         'sort_name' => '1',
1380         'age' => '1',
1381         'email' => '1',
1382         'phone' => '1',
1383         'financial_type_id' => '1',
1384         'receive_date' => '1',
1385         'total_amount' => '1',
1386       ],
1387       'order_bys' => [['column' => 'sort_name', 'order' => 'ASC', 'section' => '1']],
1388       'options' => ['metadata' => ['sql']],
1389     ]);
1390   }
1391
1392   /**
1393    * Test contact subtype filter on grant report.
1394    *
1395    * @throws \CRM_Core_Exception
1396    */
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'],
1406     ]);
1407     $this->assertEquals(1, $rows['count']);
1408   }
1409
1410   /**
1411    * Test contact subtype filter on summary report.
1412    *
1413    * @throws \CRM_Core_Exception
1414    */
1415   public function testContactSubtypeNotNull() {
1416     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1417     $this->individualCreate();
1418
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',
1425     ]);
1426     $this->assertEquals(1, $rows['count']);
1427   }
1428
1429   /**
1430    * Test contact subtype filter on summary report.
1431    *
1432    * @throws \CRM_Core_Exception
1433    */
1434   public function testContactSubtypeNull() {
1435     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1436     $this->individualCreate();
1437
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',
1444     ]);
1445     $this->assertEquals(1, $rows['count']);
1446   }
1447
1448   /**
1449    * Test contact subtype filter on summary report.
1450    *
1451    * @throws \CRM_Core_Exception
1452    */
1453   public function testContactSubtypeIn() {
1454     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1455     $this->individualCreate();
1456
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',
1463     ]);
1464     $this->assertEquals(1, $rows['count']);
1465   }
1466
1467   /**
1468    * Test contact subtype filter on summary report.
1469    *
1470    * @throws \CRM_Core_Exception
1471    */
1472   public function testContactSubtypeNotIn() {
1473     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1474     $this->individualCreate();
1475
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',
1482     ]);
1483     $this->assertEquals(1, $rows['count']);
1484   }
1485
1486   /**
1487    * Test PCP report to ensure total donors and total committed is accurate.
1488    *
1489    * @throws \CRM_Core_Exception
1490    */
1491   public function testPcpReportTotals() {
1492     $donor1ContactId = $this->individualCreate();
1493     $donor2ContactId = $this->individualCreate();
1494     $donor3ContactId = $this->individualCreate();
1495
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.
1498     //
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).
1501
1502     // A PCP page requires three components:
1503     // 1. A contribution page
1504     // 2. A PCP Block
1505     // 3. A PCP page
1506
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);
1511
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;
1515
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
1519     // soft credit.
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);
1525
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);
1535
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'];
1539
1540     // Create two contributions assigned to this contribution page and
1541     // assign soft credits appropriately.
1542     // FIRST...
1543     $contribution1params = [
1544       'contact_id' => $donor1ContactId,
1545       'contribution_page_id' => $contribution_page_id,
1546       'total_amount' => '75.00',
1547     ];
1548     $c1 = $this->contributionCreate($contribution1params);
1549     // Now the soft contribution.
1550     $p = [
1551       'contribution_id' => $c1,
1552       'pcp_id' => $pcp1->id,
1553       'contact_id' => $pcpOwnerContact1Id,
1554       'amount' => 75.00,
1555       'currency' => 'USD',
1556       'soft_credit_type_id' => $pcp_soft_credit_type_id,
1557     ];
1558     $this->callAPISuccess('contribution_soft', 'create', $p);
1559     // SECOND...
1560     $contribution2params = [
1561       'contact_id' => $donor2ContactId,
1562       'contribution_page_id' => $contribution_page_id,
1563       'total_amount' => '25.00',
1564     ];
1565     $c2 = $this->contributionCreate($contribution2params);
1566     // Now the soft contribution.
1567     $p = [
1568       'contribution_id' => $c2,
1569       'pcp_id' => $pcp1->id,
1570       'contact_id' => $pcpOwnerContact1Id,
1571       'amount' => 25.00,
1572       'currency' => 'USD',
1573       'soft_credit_type_id' => $pcp_soft_credit_type_id,
1574     ];
1575     $this->callAPISuccess('contribution_soft', 'create', $p);
1576
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',
1582     ];
1583     $c3 = $this->contributionCreate($contribution3params);
1584     // Now the soft contribution.
1585     $p = [
1586       'contribution_id' => $c3,
1587       'pcp_id' => $pcp2->id,
1588       'contact_id' => $pcpOwnerContact2Id,
1589       'amount' => 200.00,
1590       'currency' => 'USD',
1591       'soft_credit_type_id' => $pcp_soft_credit_type_id,
1592     ];
1593     $this->callAPISuccess('contribution_soft', 'create', $p);
1594
1595     $template = 'contribute/pcp';
1596     $rows = $this->callAPISuccess('report_template', 'getrows', [
1597       'report_id' => $template,
1598       'title' => 'My PCP',
1599       'fields' => [
1600         'amount_1' => '1',
1601         'soft_id' => '1',
1602       ],
1603     ]);
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');
1607   }
1608
1609   /**
1610    * Test a report that uses getAddressColumns();
1611    *
1612    * @throws \CRM_Core_Exception
1613    */
1614   public function testGetAddressColumns() {
1615     $template = 'event/participantlisting';
1616     $this->callAPISuccess('report_template', 'getrows', [
1617       'report_id' => $template,
1618       'fields' => [
1619         'sort_name' => '1',
1620         'street_address' => '1',
1621       ],
1622     ]);
1623   }
1624
1625   /**
1626    * Convoluted test of the convoluted logging detail report.
1627    *
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.
1633    */
1634   public function testLoggingDetail() {
1635     \Civi::settings()->set('logging', 1);
1636     $this->createContactsWithActivities();
1637     $this->doQuestionableStuffInASeparateFunctionSoNobodyNotices();
1638
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',
1644     ]);
1645
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
1648     // it was.
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);
1651
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', [
1655       'return' => ['id'],
1656       'report_id' => 'logging/contact/detail',
1657     ])['id'];
1658
1659     $_GET = $_REQUEST = [
1660       'reset' => '1',
1661       'log_conn_id' => $log_conn_id,
1662       'q' => "civicrm/report/instance/$instance_id",
1663     ];
1664     $values = $this->callAPISuccess('report_template', 'getrows', [
1665       'report_id' => 'logging/contact/detail',
1666     ])['values'];
1667
1668     // Note this is a delete+insert which is logically equivalent to update
1669     $expectedValues = [
1670       // here's the delete
1671       0 => [
1672         'field' => [
1673           0 => 'Activity ID (id: 2)',
1674           1 => 'Contact ID (id: 2)',
1675           2 => 'Activity Contact Type (id: 2)',
1676         ],
1677         'from' => [
1678           0 => 'Very secret meeting (id: 1)',
1679           1 => 'Mr. Anthony Łąchowski-Roberts II (id: 4)',
1680           2 => 'Activity Assignees',
1681         ],
1682         'to' => [
1683           0 => '',
1684           1 => '',
1685           2 => '',
1686         ],
1687       ],
1688       // this is the insert
1689       1 => [
1690         'field' => [
1691           0 => 'Activity ID (id: 5)',
1692           1 => 'Contact ID (id: 5)',
1693           2 => 'Activity Contact Type (id: 5)',
1694         ],
1695         'from' => [
1696           0 => '',
1697           1 => '',
1698           2 => '',
1699         ],
1700         'to' => [
1701           0 => 'Very secret meeting (id: 1)',
1702           1 => 'Mr. Anthony Brzęczysław II (id: 3)',
1703           2 => 'Activity Assignees',
1704         ],
1705       ],
1706     ];
1707     $this->assertEquals($expectedValues, $values);
1708
1709     \Civi::settings()->set('logging', 0);
1710   }
1711
1712   /**
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.
1720    *
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.
1723    */
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
1727     // every trigger.
1728     CRM_Core_Config::singleton(TRUE, TRUE);
1729     \Civi::service('sql_triggers')->rebuild(NULL, TRUE);
1730   }
1731
1732 }