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/history',
 149       'contribute/repeat',
 150       'member/summary',
 151       'event/summary',
 152       'case/summary',
 153       'case/timespent',
 154       'case/demographics',
 155       'contact/log',
 156       'contribute/bookkeeping',
 157       'grant/detail',
 158       'event/incomesummary',
 159       'case/detail',
 160       'Mailing/bounce',
 161       'Mailing/summary',
 162       'grant/statistics',
 163       'logging/contact/detail',
 164       'logging/contact/summary',
 165     ];
 166     foreach ($allTemplates as $index => $template) {
 167       $reportID = $template[0];
 168       if (in_array($reportID, $currentlyExcluded, TRUE) || stripos($reportID, 'has existing issues') !== FALSE) {
 169         unset($allTemplates[$index]);
 170       }
 171     }
 172     return $allTemplates;
 173   }
 174
 175   /**
 176    * @param \CRM_Core_DAO $entity
 177    * @param array $clauses
 178    */
 179   public function hookSelectWhere($entity, &$clauses) {
 180     // Restrict access to cases by type
 181     if ($entity === 'Contact') {
 182       $clauses['contact_type'][] = " =  'Organization' ";
 183     }
 184   }
 185
 186   /**
 187    * Test getrows on contact summary report.
 188    *
 189    * @throws \CRM_Core_Exception
 190    */
 191   public function testReportTemplateGetRowsContactSummary() {
 192     $result = $this->callAPISuccess('report_template', 'getrows', [
 193       'report_id' => 'contact/summary',
 194       'options' => ['metadata' => ['labels', 'title']],
 195     ]);
 196     $this->assertEquals('Contact Name', $result['metadata']['labels']['civicrm_contact_sort_name']);
 197
 198     //the second part of this test has been commented out because it relied on the db being reset to
 199     // it's base state
 200     //wasn't able to get that to work consistently
 201     // however, when the db is in the base state the tests do pass
 202     // and because the test covers 'all' contacts we can't create our own & assume the others don't exist
 203     /*
 204     $this->assertEquals(2, $result['count']);
 205     $this->assertEquals('Default Organization', $result[0]['civicrm_contact_sort_name']);
 206     $this->assertEquals('Second Domain', $result[1]['civicrm_contact_sort_name']);
 207     $this->assertEquals('15 Main St', $result[1]['civicrm_address_street_address']);
 208      */
 209   }
 210
 211   /**
 212    * Test getrows on Mailing Opened report.
 213    */
 214   public function testReportTemplateGetRowsMailingUniqueOpened() {
 215     $description = 'Retrieve rows from a mailing opened report template.';
 216     $this->loadXMLDataSet(__DIR__ . '/../../CRM/Mailing/BAO/queryDataset.xml');
 217
 218     // Check total rows without distinct
 219     global $_REQUEST;
 220     $_REQUEST['distinct'] = 0;
 221     $result = $this->callAPIAndDocument('report_template', 'getrows', [
 222       'report_id' => 'Mailing/opened',
 223       'options' => ['metadata' => ['labels', 'title']],
 224     ], __FUNCTION__, __FILE__, $description, 'Getrows');
 225     $this->assertEquals(14, $result['count']);
 226
 227     // Check total rows with distinct
 228     $_REQUEST['distinct'] = 1;
 229     $result = $this->callAPIAndDocument('report_template', 'getrows', [
 230       'report_id' => 'Mailing/opened',
 231       'options' => ['metadata' => ['labels', 'title']],
 232     ], __FUNCTION__, __FILE__, $description, 'Getrows');
 233     $this->assertEquals(5, $result['count']);
 234
 235     // Check total rows with distinct by passing NULL value to distinct parameter
 236     $_REQUEST['distinct'] = NULL;
 237     $result = $this->callAPIAndDocument('report_template', 'getrows', [
 238       'report_id' => 'Mailing/opened',
 239       'options' => ['metadata' => ['labels', 'title']],
 240     ], __FUNCTION__, __FILE__, $description, 'Getrows');
 241     $this->assertEquals(5, $result['count']);
 242   }
 243
 244   /**
 245    * Test api to get rows from reports.
 246    *
 247    * @dataProvider getReportTemplates
 248    *
 249    * @param $reportID
 250    *
 251    * @throws \CRM_Core_Exception
 252    */
 253   public function testReportTemplateGetRowsAllReports($reportID) {
 254     //$reportID = 'logging/contact/summary';
 255     if (stripos($reportID, 'has existing issues') !== FALSE) {
 256       $this->markTestIncomplete($reportID);
 257     }
 258     if (strpos($reportID, 'logging') === 0) {
 259       Civi::settings()->set('logging', 1);
 260     }
 261
 262     $this->callAPISuccess('report_template', 'getrows', [
 263       'report_id' => $reportID,
 264     ]);
 265     if (strpos($reportID, 'logging') === 0) {
 266       Civi::settings()->set('logging', 0);
 267     }
 268   }
 269
 270   /**
 271    * Test logging report when a custom data table has a table removed by hook.
 272    *
 273    * Here we are checking that no fatal is triggered.
 274    *
 275    * @throws \CRM_Core_Exception
 276    */
 277   public function testLoggingReportWithHookRemovalOfCustomDataTable() {
 278     Civi::settings()->set('logging', 1);
 279     $group1 = $this->customGroupCreate();
 280     $group2 = $this->customGroupCreate(['name' => 'second_one', 'title' => 'second one', 'table_name' => 'civicrm_value_second_one']);
 281     $this->customFieldCreate(['custom_group_id' => $group1['id'], 'label' => 'field one']);
 282     $this->customFieldCreate(['custom_group_id' => $group2['id'], 'label' => 'field two']);
 283     $this->hookClass->setHook('civicrm_alterLogTables', [$this, 'alterLogTablesRemoveCustom']);
 284
 285     $this->callAPISuccess('report_template', 'getrows', [
 286       'report_id' => 'logging/contact/summary',
 287     ]);
 288     Civi::settings()->set('logging', 0);
 289     $this->customGroupDelete($group1['id']);
 290     $this->customGroupDelete($group2['id']);
 291   }
 292
 293   /**
 294    * Remove one log table from the logging spec.
 295    *
 296    * @param array $logTableSpec
 297    */
 298   public function alterLogTablesRemoveCustom(&$logTableSpec) {
 299     unset($logTableSpec['civicrm_value_second_one']);
 300   }
 301
 302   /**
 303    * Test api to get rows from reports with ACLs enabled.
 304    *
 305    * Checking for lack of fatal error at the moment.
 306    *
 307    * @dataProvider getReportTemplates
 308    *
 309    * @param $reportID
 310    *
 311    * @throws \PHPUnit\Framework\IncompleteTestError
 312    * @throws \CRM_Core_Exception
 313    */
 314   public function testReportTemplateGetRowsAllReportsACL($reportID) {
 315     if (stripos($reportID, 'has existing issues') !== FALSE) {
 316       $this->markTestIncomplete($reportID);
 317     }
 318     if (strpos($reportID, 'logging') === 0) {
 319       Civi::settings()->set('logging', 1);
 320     }
 321     $this->hookClass->setHook('civicrm_aclWhereClause', [$this, 'aclWhereHookNoResults']);
 322     $this->callAPISuccess('report_template', 'getrows', [
 323       'report_id' => $reportID,
 324     ]);
 325     if (strpos($reportID, 'logging') === 0) {
 326       Civi::settings()->set('logging', 0);
 327     }
 328   }
 329
 330   /**
 331    * Test get statistics.
 332    *
 333    * @dataProvider getReportTemplates
 334    *
 335    * @param $reportID
 336    *
 337    * @throws \PHPUnit\Framework\IncompleteTestError
 338    */
 339   public function testReportTemplateGetStatisticsAllReports($reportID) {
 340     if (stripos($reportID, 'has existing issues') !== FALSE) {
 341       $this->markTestIncomplete($reportID);
 342     }
 343     if (in_array($reportID, ['contribute/softcredit', 'contribute/bookkeeping'])) {
 344       $this->markTestIncomplete($reportID . ' has non enotices when calling statistics fn');
 345     }
 346     if (strpos($reportID, 'logging') === 0) {
 347       Civi::settings()->set('logging', 1);
 348     }
 349     $description = "Get Statistics from a report (note there isn't much data to get in the test DB).";
 350     if ($reportID === 'contribute/summary') {
 351       $this->hookClass->setHook('civicrm_alterReportVar', [$this, 'alterReportVarHook']);
 352     }
 353     $this->callAPIAndDocument('report_template', 'getstatistics', [
 354       'report_id' => $reportID,
 355     ], __FUNCTION__, __FILE__, $description, 'Getstatistics');
 356     if (strpos($reportID, 'logging') === 0) {
 357       Civi::settings()->set('logging', 0);
 358     }
 359   }
 360
 361   /**
 362    * Implements hook_civicrm_alterReportVar().
 363    */
 364   public function alterReportVarHook($varType, &$var, &$object) {
 365     if ($varType === 'sql' && $object instanceof CRM_Report_Form_Contribute_Summary) {
 366       /* @var CRM_Report_Form $var */
 367       $from = $var->getVar('_from');
 368       $from .= ' LEFT JOIN civicrm_financial_type as temp ON temp.id = contribution_civireport.financial_type_id';
 369       $var->setVar('_from', $from);
 370       $where = $var->getVar('_where');
 371       $where .= ' AND ( temp.id IS NOT NULL )';
 372       $var->setVar('_where', $where);
 373     }
 374   }
 375
 376   /**
 377    * Data provider function for getting all templates.
 378    *
 379    * Note that the function needs to
 380    * be static so cannot use $this->callAPISuccess
 381    *
 382    * @throws \CiviCRM_API3_Exception
 383    */
 384   public static function getReportTemplates() {
 385     $reportTemplates = [];
 386     $reportsToSkip = [
 387       'event/income' => "This report overrides buildQuery() so doesn't seem compatible with this test and you get a syntax error `WHERE civicrm_event.id IN( ) GROUP BY civicrm_event.id`",
 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       'class' => NULL,
1286     ];
1287     $row = $rows[0];
1288     // This link is not relative - skip for now
1289     unset($row['civicrm_activity_activity_type_id_link']);
1290     if ($row['civicrm_email_contact_target_email'] === 'anthony_anderson@civicrm.org;techo@spying.com') {
1291       // order is unpredictable
1292       $expected['civicrm_email_contact_target_email'] = 'anthony_anderson@civicrm.org;techo@spying.com';
1293     }
1294
1295     $this->assertEquals($expected, $row);
1296   }
1297
1298   /**
1299    * Activity Details report has some whack-a-mole to fix when filtering on null/not null.
1300    *
1301    * @throws \CRM_Core_Exception
1302    */
1303   public function testActivityDetailsNullFilters() {
1304     $this->createContactsWithActivities();
1305     $params = [
1306       'report_id' => 'activity',
1307       'location_op' => 'nll',
1308       'location_value' => '',
1309     ];
1310     $rowsWithoutLocation = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1311     $this->assertEmpty($rowsWithoutLocation);
1312     $params['location_op'] = 'nnll';
1313     $rowsWithLocation = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1314     $this->assertCount(1, $rowsWithLocation);
1315     // Test for CRM-18356 - activity shouldn't appear if target contact filter is null.
1316     $params = [
1317       'report_id' => 'activity',
1318       'contact_target_op' => 'nll',
1319       'contact_target_value' => '',
1320     ];
1321     $rowsWithNullTarget = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1322     $this->assertEmpty($rowsWithNullTarget);
1323   }
1324
1325   /**
1326    * Test the source contact filter works.
1327    *
1328    * @throws \CRM_Core_Exception
1329    */
1330   public function testActivityDetailsContactFilter() {
1331     $this->createContactsWithActivities();
1332     $params = [
1333       'report_id' => 'activity',
1334       'contact_source_op' => 'has',
1335       'contact_source_value' => 'z',
1336       'options' => ['metadata' => ['sql']],
1337     ];
1338     $rows = $this->callAPISuccess('report_template', 'getrows', $params);
1339     $this->assertStringContainsString("civicrm_contact_source.sort_name LIKE '%z%'", $rows['metadata']['sql'][3]);
1340   }
1341
1342   /**
1343    * Set up some activity data..... use some chars that challenge our utf handling.
1344    *
1345    * @throws \CRM_Core_Exception
1346    */
1347   public function createContactsWithActivities() {
1348     $this->contactIDs[] = $this->individualCreate(['last_name' => 'Brzęczysław', 'email' => 'techo@spying.com']);
1349     $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']);
1350     $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']);
1351
1352     $this->activityID = $this->callAPISuccess('Activity', 'create', [
1353       'subject' => 'Very secret meeting',
1354       'activity_date_time' => date('Y-m-d 23:59:58'),
1355       'duration' => 120,
1356       'location' => 'Pennsylvania',
1357       'details' => 'a test activity',
1358       'status_id' => 1,
1359       'activity_type_id' => 'Meeting',
1360       'source_contact_id' => $this->contactIDs[2],
1361       'target_contact_id' => [$this->contactIDs[0], $this->contactIDs[1]],
1362       'assignee_contact_id' => $this->contactIDs[1],
1363     ])['id'];
1364   }
1365
1366   /**
1367    * Test the group filter works on the contribution summary.
1368    *
1369    * @throws \CRM_Core_Exception
1370    */
1371   public function testContributionDetailTotalHeader() {
1372     $contactID = $this->individualCreate();
1373     $contactID2 = $this->individualCreate();
1374     $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
1375     $template = 'contribute/detail';
1376     $this->callAPISuccess('report_template', 'getrows', [
1377       'report_id' => $template,
1378       'contribution_or_soft_value' => 'contributions_only',
1379       'fields' => [
1380         'sort_name' => '1',
1381         'age' => '1',
1382         'email' => '1',
1383         'phone' => '1',
1384         'financial_type_id' => '1',
1385         'receive_date' => '1',
1386         'total_amount' => '1',
1387       ],
1388       'order_bys' => [['column' => 'sort_name', 'order' => 'ASC', 'section' => '1']],
1389       'options' => ['metadata' => ['sql']],
1390     ]);
1391   }
1392
1393   /**
1394    * Test contact subtype filter on grant report.
1395    *
1396    * @throws \CRM_Core_Exception
1397    */
1398   public function testGrantReportSeparatedFilter() {
1399     $contactID = $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1400     $contactID2 = $this->individualCreate();
1401     $this->callAPISuccess('Grant', 'create', ['contact_id' => $contactID, 'status_id' => 1, 'grant_type_id' => 1, 'amount_total' => 1]);
1402     $this->callAPISuccess('Grant', 'create', ['contact_id' => $contactID2, 'status_id' => 1, 'grant_type_id' => 1, 'amount_total' => 1]);
1403     $rows = $this->callAPISuccess('report_template', 'getrows', [
1404       'report_id' => 'grant/detail',
1405       'contact_sub_type_op' => 'in',
1406       'contact_sub_type_value' => ['Student'],
1407     ]);
1408     $this->assertEquals(1, $rows['count']);
1409   }
1410
1411   /**
1412    * Test contact subtype filter on summary report.
1413    *
1414    * @throws \CRM_Core_Exception
1415    */
1416   public function testContactSubtypeNotNull() {
1417     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1418     $this->individualCreate();
1419
1420     $rows = $this->callAPISuccess('report_template', 'getrows', [
1421       'report_id' => 'contact/summary',
1422       'contact_sub_type_op' => 'nnll',
1423       'contact_sub_type_value' => [],
1424       'contact_type_op' => 'eq',
1425       'contact_type_value' => 'Individual',
1426     ]);
1427     $this->assertEquals(1, $rows['count']);
1428   }
1429
1430   /**
1431    * Test contact subtype filter on summary report.
1432    *
1433    * @throws \CRM_Core_Exception
1434    */
1435   public function testContactSubtypeNull() {
1436     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1437     $this->individualCreate();
1438
1439     $rows = $this->callAPISuccess('report_template', 'getrows', [
1440       'report_id' => 'contact/summary',
1441       'contact_sub_type_op' => 'nll',
1442       'contact_sub_type_value' => [],
1443       'contact_type_op' => 'eq',
1444       'contact_type_value' => 'Individual',
1445     ]);
1446     $this->assertEquals(1, $rows['count']);
1447   }
1448
1449   /**
1450    * Test contact subtype filter on summary report.
1451    *
1452    * @throws \CRM_Core_Exception
1453    */
1454   public function testContactSubtypeIn() {
1455     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1456     $this->individualCreate();
1457
1458     $rows = $this->callAPISuccess('report_template', 'getrows', [
1459       'report_id' => 'contact/summary',
1460       'contact_sub_type_op' => 'in',
1461       'contact_sub_type_value' => ['Student'],
1462       'contact_type_op' => 'in',
1463       'contact_type_value' => 'Individual',
1464     ]);
1465     $this->assertEquals(1, $rows['count']);
1466   }
1467
1468   /**
1469    * Test contact subtype filter on summary report.
1470    *
1471    * @throws \CRM_Core_Exception
1472    */
1473   public function testContactSubtypeNotIn() {
1474     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1475     $this->individualCreate();
1476
1477     $rows = $this->callAPISuccess('report_template', 'getrows', [
1478       'report_id' => 'contact/summary',
1479       'contact_sub_type_op' => 'notin',
1480       'contact_sub_type_value' => ['Student'],
1481       'contact_type_op' => 'in',
1482       'contact_type_value' => 'Individual',
1483     ]);
1484     $this->assertEquals(1, $rows['count']);
1485   }
1486
1487   /**
1488    * Test PCP report to ensure total donors and total committed is accurate.
1489    *
1490    * @throws \CRM_Core_Exception
1491    */
1492   public function testPcpReportTotals() {
1493     $donor1ContactId = $this->individualCreate();
1494     $donor2ContactId = $this->individualCreate();
1495     $donor3ContactId = $this->individualCreate();
1496
1497     // We are going to create two PCP pages. We will create two contributions
1498     // on the first PCP page and one contribution on the second PCP page.
1499     //
1500     // Then, we will ensure that the first PCP page reports a total of both
1501     // contributions (but not the contribution made on the second PCP page).
1502
1503     // A PCP page requires three components:
1504     // 1. A contribution page
1505     // 2. A PCP Block
1506     // 3. A PCP page
1507
1508     // pcpBLockParams creates a contribution page and returns the parameters
1509     // necessary to create a PBP Block.
1510     $blockParams = $this->pcpBlockParams();
1511     $pcpBlock = CRM_PCP_BAO_PCPBlock::create($blockParams);
1512
1513     // Keep track of the contribution page id created. We will use this
1514     // contribution page id for all the PCP pages.
1515     $contribution_page_id = $pcpBlock->entity_id;
1516
1517     // pcpParams returns the parameters needed to create a PCP page.
1518     $pcpParams = $this->pcpParams();
1519     // Keep track of the owner of the page so we can properly apply the
1520     // soft credit.
1521     $pcpOwnerContact1Id = $pcpParams['contact_id'];
1522     $pcpParams['pcp_block_id'] = $pcpBlock->id;
1523     $pcpParams['page_id'] = $contribution_page_id;
1524     $pcpParams['page_type'] = 'contribute';
1525     $pcp1 = CRM_PCP_BAO_PCP::create($pcpParams);
1526
1527     // Nice work. Now, let's create a second PCP page.
1528     $pcpParams = $this->pcpParams();
1529     // Keep track of the owner of the page.
1530     $pcpOwnerContact2Id = $pcpParams['contact_id'];
1531     // We're using the same pcpBlock id and contribution page that we created above.
1532     $pcpParams['pcp_block_id'] = $pcpBlock->id;
1533     $pcpParams['page_id'] = $contribution_page_id;
1534     $pcpParams['page_type'] = 'contribute';
1535     $pcp2 = CRM_PCP_BAO_PCP::create($pcpParams);
1536
1537     // Get soft credit types, with the name column as the key.
1538     $soft_credit_types = CRM_Core_PseudoConstant::get('CRM_Contribute_BAO_ContributionSoft', 'soft_credit_type_id', ['flip' => TRUE, 'labelColumn' => 'name']);
1539     $pcp_soft_credit_type_id = $soft_credit_types['pcp'];
1540
1541     // Create two contributions assigned to this contribution page and
1542     // assign soft credits appropriately.
1543     // FIRST...
1544     $contribution1params = [
1545       'contact_id' => $donor1ContactId,
1546       'contribution_page_id' => $contribution_page_id,
1547       'total_amount' => '75.00',
1548     ];
1549     $c1 = $this->contributionCreate($contribution1params);
1550     // Now the soft contribution.
1551     $p = [
1552       'contribution_id' => $c1,
1553       'pcp_id' => $pcp1->id,
1554       'contact_id' => $pcpOwnerContact1Id,
1555       'amount' => 75.00,
1556       'currency' => 'USD',
1557       'soft_credit_type_id' => $pcp_soft_credit_type_id,
1558     ];
1559     $this->callAPISuccess('contribution_soft', 'create', $p);
1560     // SECOND...
1561     $contribution2params = [
1562       'contact_id' => $donor2ContactId,
1563       'contribution_page_id' => $contribution_page_id,
1564       'total_amount' => '25.00',
1565     ];
1566     $c2 = $this->contributionCreate($contribution2params);
1567     // Now the soft contribution.
1568     $p = [
1569       'contribution_id' => $c2,
1570       'pcp_id' => $pcp1->id,
1571       'contact_id' => $pcpOwnerContact1Id,
1572       'amount' => 25.00,
1573       'currency' => 'USD',
1574       'soft_credit_type_id' => $pcp_soft_credit_type_id,
1575     ];
1576     $this->callAPISuccess('contribution_soft', 'create', $p);
1577
1578     // Create one contributions assigned to the second PCP page
1579     $contribution3params = [
1580       'contact_id' => $donor3ContactId,
1581       'contribution_page_id' => $contribution_page_id,
1582       'total_amount' => '200.00',
1583     ];
1584     $c3 = $this->contributionCreate($contribution3params);
1585     // Now the soft contribution.
1586     $p = [
1587       'contribution_id' => $c3,
1588       'pcp_id' => $pcp2->id,
1589       'contact_id' => $pcpOwnerContact2Id,
1590       'amount' => 200.00,
1591       'currency' => 'USD',
1592       'soft_credit_type_id' => $pcp_soft_credit_type_id,
1593     ];
1594     $this->callAPISuccess('contribution_soft', 'create', $p);
1595
1596     $template = 'contribute/pcp';
1597     $rows = $this->callAPISuccess('report_template', 'getrows', [
1598       'report_id' => $template,
1599       'title' => 'My PCP',
1600       'fields' => [
1601         'amount_1' => '1',
1602         'soft_id' => '1',
1603       ],
1604     ]);
1605     $values = $rows['values'][0];
1606     $this->assertEquals(100.00, $values['civicrm_contribution_soft_amount_1_sum'], 'Total committed should be $100');
1607     $this->assertEquals(2, $values['civicrm_contribution_soft_soft_id_count'], 'Total donors should be 2');
1608   }
1609
1610   /**
1611    * Test a report that uses getAddressColumns();
1612    *
1613    * @throws \CRM_Core_Exception
1614    */
1615   public function testGetAddressColumns() {
1616     $template = 'event/participantlisting';
1617     $this->callAPISuccess('report_template', 'getrows', [
1618       'report_id' => $template,
1619       'fields' => [
1620         'sort_name' => '1',
1621         'street_address' => '1',
1622       ],
1623     ]);
1624   }
1625
1626   /**
1627    * Test that the contribution aggregate by relationship report filters
1628    * by financial type.
1629    */
1630   public function testContributionAggregateByRelationship() {
1631     $contact = $this->individualCreate();
1632     // Two contributions with different financial types.
1633     // We don't really care which types, just different.
1634     $this->contributionCreate(['contact_id' => $contact, 'receive_date' => (date('Y') - 1) . '-07-01', 'financial_type_id' => 1, 'total_amount' => '10']);
1635     $this->contributionCreate(['contact_id' => $contact, 'receive_date' => (date('Y') - 1) . '-08-01', 'financial_type_id' => 2, 'total_amount' => '20']);
1636     $rows = $this->callAPISuccess('report_template', 'getrows', [
1637       'report_id' => 'contribute/history',
1638       'financial_type_id_op' => 'in',
1639       'financial_type_id_value' => [1],
1640       'options' => ['metadata' => ['sql']],
1641       'fields' => [
1642         'relationship_type_id' => 1,
1643         'total_amount' => 1,
1644       ],
1645     ]);
1646
1647     // Hmm it has styling in it before being sent to the template. If that gets fixed then will need to update this.
1648     $this->assertEquals('<strong>10.00</strong>', $rows['values'][$contact]['civicrm_contribution_total_amount'], 'should only include the $10 contribution');
1649
1650     $this->callAPISuccess('Contact', 'delete', ['id' => $contact]);
1651   }
1652
1653   /**
1654    * Convoluted test of the convoluted logging detail report.
1655    *
1656    * In principle it's just make an update and get the report and see if it
1657    * matches the update.
1658    * In practice, besides some setup and trigger-wrangling, the report isn't
1659    * useful for activities, so we're checking activity_contact records, and
1660    * because of how an activity update works that's actually a delete+insert.
1661    */
1662   public function testLoggingDetail() {
1663     \Civi::settings()->set('logging', 1);
1664     $this->createContactsWithActivities();
1665     $this->doQuestionableStuffInASeparateFunctionSoNobodyNotices();
1666
1667     // Do something that creates an update record.
1668     $this->callAPISuccess('Activity', 'create', [
1669       'id' => $this->activityID,
1670       'assignee_contact_id' => $this->contactIDs[0],
1671       'details' => 'Edited details',
1672     ]);
1673
1674     // In normal UI flow you would go to the summary report and drill down,
1675     // but here we need to go directly to the connection id, so find out what
1676     // it was.
1677     $queryParams = [1 => [$this->activityID, 'Integer']];
1678     $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);
1679
1680     // There should be only one instance of this after enabling so we can
1681     // just specify the template id as the lookup criteria.
1682     $instance_id = $this->callAPISuccess('report_instance', 'getsingle', [
1683       'return' => ['id'],
1684       'report_id' => 'logging/contact/detail',
1685     ])['id'];
1686
1687     $_GET = $_REQUEST = [
1688       'reset' => '1',
1689       'log_conn_id' => $log_conn_id,
1690       'q' => "civicrm/report/instance/$instance_id",
1691     ];
1692     $values = $this->callAPISuccess('report_template', 'getrows', [
1693       'report_id' => 'logging/contact/detail',
1694     ])['values'];
1695
1696     // Note this is a delete+insert which is logically equivalent to update
1697     $expectedValues = [
1698       // here's the delete
1699       0 => [
1700         'field' => [
1701           0 => 'Activity ID (id: 2)',
1702           1 => 'Contact ID (id: 2)',
1703           2 => 'Activity Contact Type (id: 2)',
1704         ],
1705         'from' => [
1706           0 => 'Very secret meeting (id: 1)',
1707           1 => 'Mr. Anthony Łąchowski-Roberts II (id: 4)',
1708           2 => 'Activity Assignees',
1709         ],
1710         'to' => [
1711           0 => '',
1712           1 => '',
1713           2 => '',
1714         ],
1715       ],
1716       // this is the insert
1717       1 => [
1718         'field' => [
1719           0 => 'Activity ID (id: 5)',
1720           1 => 'Contact ID (id: 5)',
1721           2 => 'Activity Contact Type (id: 5)',
1722         ],
1723         'from' => [
1724           0 => '',
1725           1 => '',
1726           2 => '',
1727         ],
1728         'to' => [
1729           0 => 'Very secret meeting (id: 1)',
1730           1 => 'Mr. Anthony Brzęczysław II (id: 3)',
1731           2 => 'Activity Assignees',
1732         ],
1733       ],
1734     ];
1735     $this->assertEquals($expectedValues, $values);
1736
1737     \Civi::settings()->set('logging', 0);
1738   }
1739
1740   /**
1741    * The issue is that in a unit test the log_conn_id is going to
1742    * be the same throughout the entire test, which is not how it works normally
1743    * when you have separate page requests. So use the fact that the conn_id
1744    * format is controlled by a hidden variable, so we can force different
1745    * conn_id's during initialization and after.
1746    * If we don't do this, then the report thinks EVERY log record is part
1747    * of the one change detail.
1748    *
1749    * On the plus side, this doesn't affect other tests since if they enable
1750    * logging then that'll just recreate the variable and triggers.
1751    */
1752   private function doQuestionableStuffInASeparateFunctionSoNobodyNotices(): void {
1753     CRM_Core_DAO::executeQuery("DELETE FROM civicrm_setting WHERE name='logging_uniqueid_date'");
1754     // Now we have to rebuild triggers because the format formula is stored in
1755     // every trigger.
1756     CRM_Core_Config::singleton(TRUE, TRUE);
1757     \Civi::service('sql_triggers')->rebuild(NULL, TRUE);
1758   }
1759
1760 }