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 CRMTraits_ACL_PermissionTrait;
  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() {
  33     $this->quickCleanUpFinancialEntities();
  34     $this->quickCleanup(['civicrm_group', 'civicrm_saved_search', 'civicrm_group_contact', 'civicrm_group_contact_cache', 'civicrm_group'], TRUE);
  35     parent::tearDown();
  36   }
  37
  38   public function testReportTemplate() {
  39     $result = $this->callAPISuccess('ReportTemplate', 'create', [
  40       'label' => 'Example Form',
  41       'description' => 'Longish description of the example form',
  42       'class_name' => 'CRM_Report_Form_Examplez',
  43       'report_url' => 'example/path',
  44       'component' => 'CiviCase',
  45     ]);
  46     $this->assertAPISuccess($result);
  47     $this->assertEquals(1, $result['count']);
  48     $entityId = $result['id'];
  49     $this->assertTrue(is_numeric($entityId));
  50     $this->assertEquals(7, $result['values'][$entityId]['component_id']);
  51     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  52       WHERE name = "CRM_Report_Form_Examplez"
  53       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  54     $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value
  55       WHERE name = "CRM_Report_Form_Examplez"');
  56
  57     // change component to null
  58     $result = $this->callAPISuccess('ReportTemplate', 'create', [
  59       'id' => $entityId,
  60       'component' => '',
  61     ]);
  62     $this->assertAPISuccess($result);
  63     $this->assertEquals(1, $result['count']);
  64     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  65       WHERE name = "CRM_Report_Form_Examplez"
  66       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  67     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  68       WHERE name = "CRM_Report_Form_Examplez"
  69       AND component_id IS NULL');
  70
  71     // deactivate
  72     $result = $this->callAPISuccess('ReportTemplate', 'create', [
  73       'id' => $entityId,
  74       'is_active' => 0,
  75     ]);
  76     $this->assertAPISuccess($result);
  77     $this->assertEquals(1, $result['count']);
  78     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  79       WHERE name = "CRM_Report_Form_Examplez"
  80       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  81     $this->assertDBQuery(0, 'SELECT is_active FROM civicrm_option_value
  82       WHERE name = "CRM_Report_Form_Examplez"');
  83
  84     // activate
  85     $result = $this->callAPISuccess('ReportTemplate', 'create', [
  86       'id' => $entityId,
  87       'is_active' => 1,
  88     ]);
  89     $this->assertAPISuccess($result);
  90     $this->assertEquals(1, $result['count']);
  91     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  92       WHERE name = "CRM_Report_Form_Examplez"
  93       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  94     $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value
  95       WHERE name = "CRM_Report_Form_Examplez"');
  96
  97     $result = $this->callAPISuccess('ReportTemplate', 'delete', [
  98       'id' => $entityId,
  99     ]);
 100     $this->assertAPISuccess($result);
 101     $this->assertEquals(1, $result['count']);
 102     $this->assertDBQuery(0, 'SELECT count(*) FROM civicrm_option_value
 103       WHERE name = "CRM_Report_Form_Examplez"
 104       ');
 105   }
 106
 107   /**
 108    * Test api to get rows from reports.
 109    *
 110    * @dataProvider getReportTemplatesSupportingSelectWhere
 111    *
 112    * @param $reportID
 113    *
 114    * @throws \PHPUnit\Framework\IncompleteTestError
 115    */
 116   public function testReportTemplateSelectWhere($reportID) {
 117     $this->hookClass->setHook('civicrm_selectWhereClause', [$this, 'hookSelectWhere']);
 118     $result = $this->callAPISuccess('report_template', 'getrows', [
 119       'report_id' => $reportID,
 120       'options' => ['metadata' => ['sql']],
 121     ]);
 122     $found = FALSE;
 123     foreach ($result['metadata']['sql'] as $sql) {
 124       if (strstr($sql, " =  'Organization' ")) {
 125         $found = TRUE;
 126       }
 127     }
 128     $this->assertTrue($found, $reportID);
 129   }
 130
 131   /**
 132    * Get templates suitable for SelectWhere test.
 133    *
 134    * @return array
 135    */
 136   public function getReportTemplatesSupportingSelectWhere() {
 137     $allTemplates = $this->getReportTemplates();
 138     // Exclude all that do not work as of test being written. I have not dug into why not.
 139     $currentlyExcluded = [
 140       'contribute/repeat',
 141       'member/summary',
 142       'event/summary',
 143       'case/summary',
 144       'case/timespent',
 145       'case/demographics',
 146       'contact/log',
 147       'contribute/bookkeeping',
 148       'grant/detail',
 149       'event/incomesummary',
 150       'case/detail',
 151       'Mailing/bounce',
 152       'Mailing/summary',
 153       'grant/statistics',
 154       'logging/contact/detail',
 155       'logging/contact/summary',
 156     ];
 157     foreach ($allTemplates as $index => $template) {
 158       $reportID = $template[0];
 159       if (in_array($reportID, $currentlyExcluded) || stristr($reportID, 'has existing issues')) {
 160         unset($allTemplates[$index]);
 161       }
 162     }
 163     return $allTemplates;
 164   }
 165
 166   /**
 167    * @param \CRM_Core_DAO $entity
 168    * @param array $clauses
 169    */
 170   public function hookSelectWhere($entity, &$clauses) {
 171     // Restrict access to cases by type
 172     if ($entity == 'Contact') {
 173       $clauses['contact_type'][] = " =  'Organization' ";
 174     }
 175   }
 176
 177   /**
 178    * Test getrows on contact summary report.
 179    */
 180   public function testReportTemplateGetRowsContactSummary() {
 181     $description = "Retrieve rows from a report template (optionally providing the instance_id).";
 182     $result = $this->callAPISuccess('report_template', 'getrows', [
 183       'report_id' => 'contact/summary',
 184       'options' => ['metadata' => ['labels', 'title']],
 185     ], __FUNCTION__, __FILE__, $description, 'Getrows');
 186     $this->assertEquals('Contact Name', $result['metadata']['labels']['civicrm_contact_sort_name']);
 187
 188     //the second part of this test has been commented out because it relied on the db being reset to
 189     // it's base state
 190     //wasn't able to get that to work consistently
 191     // however, when the db is in the base state the tests do pass
 192     // and because the test covers 'all' contacts we can't create our own & assume the others don't exist
 193     /*
 194     $this->assertEquals(2, $result['count']);
 195     $this->assertEquals('Default Organization', $result[0]['civicrm_contact_sort_name']);
 196     $this->assertEquals('Second Domain', $result[1]['civicrm_contact_sort_name']);
 197     $this->assertEquals('15 Main St', $result[1]['civicrm_address_street_address']);
 198      */
 199   }
 200
 201   /**
 202    * Test getrows on Mailing Opened report.
 203    */
 204   public function testReportTemplateGetRowsMailingUniqueOpened() {
 205     $description = "Retrieve rows from a mailing opened report template.";
 206     $this->loadXMLDataSet(dirname(__FILE__) . '/../../CRM/Mailing/BAO/queryDataset.xml');
 207
 208     // Check total rows without distinct
 209     global $_REQUEST;
 210     $_REQUEST['distinct'] = 0;
 211     $result = $this->callAPIAndDocument('report_template', 'getrows', [
 212       'report_id' => 'Mailing/opened',
 213       'options' => ['metadata' => ['labels', 'title']],
 214     ], __FUNCTION__, __FILE__, $description, 'Getrows');
 215     $this->assertEquals(14, $result['count']);
 216
 217     // Check total rows with distinct
 218     $_REQUEST['distinct'] = 1;
 219     $result = $this->callAPIAndDocument('report_template', 'getrows', [
 220       'report_id' => 'Mailing/opened',
 221       'options' => ['metadata' => ['labels', 'title']],
 222     ], __FUNCTION__, __FILE__, $description, 'Getrows');
 223     $this->assertEquals(5, $result['count']);
 224
 225     // Check total rows with distinct by passing NULL value to distinct parameter
 226     $_REQUEST['distinct'] = NULL;
 227     $result = $this->callAPIAndDocument('report_template', 'getrows', [
 228       'report_id' => 'Mailing/opened',
 229       'options' => ['metadata' => ['labels', 'title']],
 230     ], __FUNCTION__, __FILE__, $description, 'Getrows');
 231     $this->assertEquals(5, $result['count']);
 232   }
 233
 234   /**
 235    * Test api to get rows from reports.
 236    *
 237    * @dataProvider getReportTemplates
 238    *
 239    * @param $reportID
 240    *
 241    * @throws \PHPUnit\Framework\IncompleteTestError
 242    */
 243   public function testReportTemplateGetRowsAllReports($reportID) {
 244     //$reportID = 'logging/contact/summary';
 245     if (stristr($reportID, 'has existing issues')) {
 246       $this->markTestIncomplete($reportID);
 247     }
 248     if (substr($reportID, 0, '7') === 'logging') {
 249       Civi::settings()->set('logging', 1);
 250     }
 251
 252     $this->callAPISuccess('report_template', 'getrows', [
 253       'report_id' => $reportID,
 254     ]);
 255     if (substr($reportID, 0, '7') === 'logging') {
 256       Civi::settings()->set('logging', 0);
 257     }
 258   }
 259
 260   /**
 261    * Test logging report when a custom data table has a table removed by hook.
 262    *
 263    * Here we are checking that no fatal is triggered.
 264    */
 265   public function testLoggingReportWithHookRemovalOfCustomDataTable() {
 266     Civi::settings()->set('logging', 1);
 267     $group1 = $this->customGroupCreate();
 268     $group2 = $this->customGroupCreate(['name' => 'second_one', 'title' => 'second one', 'table_name' => 'civicrm_value_second_one']);
 269     $this->customFieldCreate(['custom_group_id' => $group1['id'], 'label' => 'field one']);
 270     $this->customFieldCreate(['custom_group_id' => $group2['id'], 'label' => 'field two']);
 271     $this->hookClass->setHook('civicrm_alterLogTables', [$this, 'alterLogTablesRemoveCustom']);
 272
 273     $this->callAPISuccess('report_template', 'getrows', [
 274       'report_id' => 'logging/contact/summary',
 275     ]);
 276     Civi::settings()->set('logging', 0);
 277     $this->customGroupDelete($group1['id']);
 278     $this->customGroupDelete($group2['id']);
 279   }
 280
 281   /**
 282    * Remove one log table from the logging spec.
 283    *
 284    * @param array $logTableSpec
 285    */
 286   public function alterLogTablesRemoveCustom(&$logTableSpec) {
 287     unset($logTableSpec['civicrm_value_second_one']);
 288   }
 289
 290   /**
 291    * Test api to get rows from reports with ACLs enabled.
 292    *
 293    * Checking for lack of fatal error at the moment.
 294    *
 295    * @dataProvider getReportTemplates
 296    *
 297    * @param $reportID
 298    *
 299    * @throws \PHPUnit\Framework\IncompleteTestError
 300    */
 301   public function testReportTemplateGetRowsAllReportsACL($reportID) {
 302     if (stristr($reportID, 'has existing issues')) {
 303       $this->markTestIncomplete($reportID);
 304     }
 305     $this->hookClass->setHook('civicrm_aclWhereClause', [$this, 'aclWhereHookNoResults']);
 306     $this->callAPISuccess('report_template', 'getrows', [
 307       'report_id' => $reportID,
 308     ]);
 309   }
 310
 311   /**
 312    * Test get statistics.
 313    *
 314    * @dataProvider getReportTemplates
 315    *
 316    * @param $reportID
 317    *
 318    * @throws \PHPUnit\Framework\IncompleteTestError
 319    */
 320   public function testReportTemplateGetStatisticsAllReports($reportID) {
 321     if (stristr($reportID, 'has existing issues')) {
 322       $this->markTestIncomplete($reportID);
 323     }
 324     if (in_array($reportID, ['contribute/softcredit', 'contribute/bookkeeping'])) {
 325       $this->markTestIncomplete($reportID . " has non enotices when calling statistics fn");
 326     }
 327     $description = "Get Statistics from a report (note there isn't much data to get in the test DB).";
 328     if ($reportID === 'contribute/summary') {
 329       $this->hookClass->setHook('civicrm_alterReportVar', [$this, 'alterReportVarHook']);
 330     }
 331     $result = $this->callAPIAndDocument('report_template', 'getstatistics', [
 332       'report_id' => $reportID,
 333     ], __FUNCTION__, __FILE__, $description, 'Getstatistics', 'getstatistics');
 334   }
 335
 336   /**
 337    * Implements hook_civicrm_alterReportVar().
 338    */
 339   public function alterReportVarHook($varType, &$var, &$object) {
 340     if ($varType === 'sql' && $object instanceof CRM_Report_Form_Contribute_Summary) {
 341       $from = $var->getVar('_from');
 342       $from .= " LEFT JOIN civicrm_financial_type as temp ON temp.id = contribution_civireport.financial_type_id";
 343       $var->setVar('_from', $from);
 344       $where = $var->getVar('_where');
 345       $where .= " AND ( temp.id IS NOT NULL )";
 346       $var->setVar('_where', $where);
 347     }
 348   }
 349
 350   /**
 351    * Data provider function for getting all templates.
 352    *
 353    * Note that the function needs to
 354    * be static so cannot use $this->callAPISuccess
 355    */
 356   public static function getReportTemplates() {
 357     $reportsToSkip = [
 358       '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',
 359       'contribute/history' => 'Declaration of CRM_Report_Form_Contribute_History::buildRows() should be compatible with CRM_Report_Form::buildRows($sql, &$rows)',
 360     ];
 361
 362     $reports = civicrm_api3('report_template', 'get', ['return' => 'value', 'options' => ['limit' => 500]]);
 363     foreach ($reports['values'] as $report) {
 364       if (empty($reportsToSkip[$report['value']])) {
 365         $reportTemplates[] = [$report['value']];
 366       }
 367       else {
 368         $reportTemplates[] = [$report['value'] . " has existing issues :  " . $reportsToSkip[$report['value']]];
 369       }
 370     }
 371
 372     return $reportTemplates;
 373   }
 374
 375   /**
 376    * Get contribution templates that work with basic filter tests.
 377    *
 378    * These templates require minimal data config.
 379    */
 380   public static function getContributionReportTemplates() {
 381     return [['contribute/summary'], ['contribute/detail'], ['contribute/repeat'], ['topDonor' => 'contribute/topDonor']];
 382   }
 383
 384   /**
 385    * Get contribution templates that work with basic filter tests.
 386    *
 387    * These templates require minimal data config.
 388    */
 389   public static function getMembershipReportTemplates() {
 390     return [['member/detail']];
 391   }
 392
 393   /**
 394    * Get the membership and contribution reports to test.
 395    *
 396    * @return array
 397    */
 398   public static function getMembershipAndContributionReportTemplatesForGroupTests() {
 399     $templates = array_merge(self::getContributionReportTemplates(), self::getMembershipReportTemplates());
 400     foreach ($templates as $key => $value) {
 401       if (array_key_exists('topDonor', $value)) {
 402         // Report is not standard enough to test here.
 403         unset($templates[$key]);
 404       }
 405
 406     }
 407     return $templates;
 408   }
 409
 410   /**
 411    * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
 412    */
 413   public function testLybuntReportWithData() {
 414     $inInd = $this->individualCreate();
 415     $outInd = $this->individualCreate();
 416     $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-03-01']);
 417     $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]);
 418     $rows = $this->callAPISuccess('report_template', 'getrows', [
 419       'report_id' => 'contribute/lybunt',
 420       'yid_value' => 2015,
 421       'yid_op' => 'calendar',
 422       'options' => ['metadata' => ['sql']],
 423     ]);
 424     $this->assertEquals(1, $rows['count'], "Report failed - the sql used to generate the results was " . print_r($rows['metadata']['sql'], TRUE));
 425   }
 426
 427   /**
 428    * Test Lybunt report applies ACLs.
 429    */
 430   public function testLybuntReportWithDataAndACLFilter() {
 431     CRM_Core_Config::singleton()->userPermissionClass->permissions = ['administer CiviCRM'];
 432     $inInd = $this->individualCreate();
 433     $outInd = $this->individualCreate();
 434     $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-03-01']);
 435     $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]);
 436     $this->hookClass->setHook('civicrm_aclWhereClause', [$this, 'aclWhereHookNoResults']);
 437     $params = [
 438       'report_id' => 'contribute/lybunt',
 439       'yid_value' => 2015,
 440       'yid_op' => 'calendar',
 441       'options' => ['metadata' => ['sql']],
 442       'check_permissions' => 1,
 443     ];
 444
 445     $rows = $this->callAPISuccess('report_template', 'getrows', $params);
 446     $this->assertEquals(0, $rows['count'], "Report failed - the sql used to generate the results was " . print_r($rows['metadata']['sql'], TRUE));
 447
 448     CRM_Utils_Hook::singleton()->reset();
 449   }
 450
 451   /**
 452    * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
 453    */
 454   public function testLybuntReportWithFYData() {
 455     $inInd = $this->individualCreate();
 456     $outInd = $this->individualCreate();
 457     $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-10-01']);
 458     $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]);
 459     $this->callAPISuccess('Setting', 'create', ['fiscalYearStart' => ['M' => 7, 'd' => 1]]);
 460     $rows = $this->callAPISuccess('report_template', 'getrows', [
 461       'report_id' => 'contribute/lybunt',
 462       'yid_value' => 2015,
 463       'yid_op' => 'fiscal',
 464       'options' => ['metadata' => ['sql']],
 465       'order_bys' => [
 466         [
 467           'column' => 'first_name',
 468           'order' => 'ASC',
 469         ],
 470       ],
 471     ]);
 472
 473     $this->assertEquals(2, $rows['count'], "Report failed - the sql used to generate the results was " . print_r($rows['metadata']['sql'], TRUE));
 474
 475     $expected = preg_replace('/\s+/', ' ', 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
 476       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
 477          AND contribution_civireport.is_test = 0
 478          AND contribution_civireport.receive_date BETWEEN \'20140701000000\' AND \'20150630235959\'
 479          WHERE contact_civireport.id NOT IN (
 480       SELECT cont_exclude.contact_id
 481           FROM civicrm_contribution cont_exclude
 482           WHERE cont_exclude.receive_date BETWEEN \'2015-7-1\' AND \'20160630235959\')
 483           AND ( contribution_civireport.contribution_status_id IN (1) )
 484       GROUP BY contact_civireport.id');
 485     // Exclude whitespace in comparison as we don't care if it changes & this allows us to make the above readable.
 486     $whitespacelessSql = preg_replace('/\s+/', ' ', $rows['metadata']['sql'][0]);
 487     $this->assertContains($expected, $whitespacelessSql);
 488   }
 489
 490   /**
 491    * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
 492    */
 493   public function testLybuntReportWithFYDataOrderByLastYearAmount() {
 494     $inInd = $this->individualCreate();
 495     $outInd = $this->individualCreate();
 496     $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-10-01']);
 497     $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]);
 498     $this->callAPISuccess('Setting', 'create', ['fiscalYearStart' => ['M' => 7, 'd' => 1]]);
 499     $rows = $this->callAPISuccess('report_template', 'getrows', [
 500       'report_id' => 'contribute/lybunt',
 501       'yid_value' => 2015,
 502       'yid_op' => 'fiscal',
 503       'options' => ['metadata' => ['sql']],
 504       'fields' => ['first_name'],
 505       'order_bys' => [
 506         [
 507           'column' => 'last_year_total_amount',
 508           'order' => 'ASC',
 509         ],
 510       ],
 511     ]);
 512
 513     $this->assertEquals(2, $rows['count'], "Report failed - the sql used to generate the results was " . print_r($rows['metadata']['sql'], TRUE));
 514   }
 515
 516   /**
 517    * Test the group filter works on the contribution summary (with a smart group).
 518    *
 519    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
 520    *
 521    * @param string $template
 522    *   Name of the template to test.
 523    *
 524    * @throws \CRM_Core_Exception
 525    */
 526   public function testContributionSummaryWithSmartGroupFilter($template) {
 527     $groupID = $this->setUpPopulatedSmartGroup();
 528     $rows = $this->callAPISuccess('report_template', 'getrows', [
 529       'report_id' => $template,
 530       'gid_value' => $groupID,
 531       'gid_op' => 'in',
 532       'options' => ['metadata' => ['sql']],
 533     ]);
 534     $this->assertNumberOfContactsInResult(3, $rows, $template);
 535     if ($template === 'contribute/summary') {
 536       $this->assertEquals(3, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 537     }
 538   }
 539
 540   /**
 541    * Test the group filter works on the contribution summary.
 542    *
 543    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
 544    */
 545   public function testContributionSummaryWithNotINSmartGroupFilter($template) {
 546     $groupID = $this->setUpPopulatedSmartGroup();
 547     $rows = $this->callAPISuccess('report_template', 'getrows', [
 548       'report_id' => 'contribute/summary',
 549       'gid_value' => $groupID,
 550       'gid_op' => 'notin',
 551       'options' => ['metadata' => ['sql']],
 552     ]);
 553     $this->assertEquals(2, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 554   }
 555
 556   /**
 557    * Test no fatal on order by per https://lab.civicrm.org/dev/core/issues/739
 558    */
 559   public function testCaseDetailsCaseTypeHeader() {
 560     $this->callAPISuccess('report_template', 'getrows', [
 561       'report_id' => 'case/detail',
 562       'fields' => ['subject' => 1, 'client_sort_name' => 1],
 563       'order_bys' => [
 564         1 => [
 565           'column' => 'case_type_title',
 566           'order' => 'ASC',
 567           'section' => '1',
 568         ],
 569       ],
 570     ]);
 571   }
 572
 573   /**
 574    * Test the group filter works on the contribution summary.
 575    */
 576   public function testContributionDetailSoftCredits() {
 577     $contactID = $this->individualCreate();
 578     $contactID2 = $this->individualCreate();
 579     $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
 580     $template = 'contribute/detail';
 581     $rows = $this->callAPISuccess('report_template', 'getrows', [
 582       'report_id' => $template,
 583       'contribution_or_soft_value' => 'contributions_only',
 584       'fields' => ['soft_credits' => 1, 'contribution_or_soft' => 1, 'sort_name' => 1],
 585       'options' => ['metadata' => ['sql']],
 586     ]);
 587     $this->assertEquals(
 588       "<a href='/index.php?q=civicrm/contact/view&amp;reset=1&amp;cid=" . $contactID2 . "'>Anderson, Anthony</a> $ 5.00",
 589       $rows['values'][0]['civicrm_contribution_soft_credits']
 590     );
 591   }
 592
 593   /**
 594    * Test the amount column is populated on soft credit details.
 595    */
 596   public function testContributionDetailSoftCreditsOnly() {
 597     $contactID = $this->individualCreate();
 598     $contactID2 = $this->individualCreate();
 599     $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
 600     $template = 'contribute/detail';
 601     $rows = $this->callAPISuccess('report_template', 'getrows', [
 602       'report_id' => $template,
 603       'contribution_or_soft_value' => 'soft_credits_only',
 604       'fields' => [
 605         'sort_name' => '1',
 606         'email' => '1',
 607         'financial_type_id' => '1',
 608         'receive_date' => '1',
 609         'total_amount' => '1',
 610       ],
 611       'options' => ['metadata' => ['sql', 'labels']],
 612     ]);
 613     foreach (array_keys($rows['metadata']['labels']) as $header) {
 614       $this->assertTrue(!empty($rows['values'][0][$header]));
 615     }
 616   }
 617
 618   /**
 619    * Test the group filter works on the various reports.
 620    *
 621    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
 622    *
 623    * @param string $template
 624    *   Report template unique identifier.
 625    */
 626   public function testReportsWithNonSmartGroupFilter($template) {
 627     $groupID = $this->setUpPopulatedGroup();
 628     $rows = $this->callAPISuccess('report_template', 'getrows', [
 629       'report_id' => $template,
 630       'gid_value' => [$groupID],
 631       'gid_op' => 'in',
 632       'options' => ['metadata' => ['sql']],
 633     ]);
 634     $this->assertNumberOfContactsInResult(1, $rows, $template);
 635   }
 636
 637   /**
 638    * Assert the included results match the expected.
 639    *
 640    * There may or may not be a group by in play so the assertion varies a little.
 641    *
 642    * @param int $numberExpected
 643    * @param array $rows
 644    *   Rows returned from the report.
 645    * @param string $template
 646    */
 647   protected function assertNumberOfContactsInResult($numberExpected, $rows, $template) {
 648     if (isset($rows['values'][0]['civicrm_contribution_total_amount_count'])) {
 649       $this->assertEquals($numberExpected, $rows['values'][0]['civicrm_contribution_total_amount_count'], 'wrong row count in ' . $template);
 650     }
 651     else {
 652       $this->assertEquals($numberExpected, count($rows['values']), 'wrong row count in ' . $template);
 653     }
 654   }
 655
 656   /**
 657    * Test the group filter works on the contribution summary when 2 groups are involved.
 658    *
 659    * @throws \CRM_Core_Exception
 660    */
 661   public function testContributionSummaryWithTwoGroups() {
 662     $groupID = $this->setUpPopulatedGroup();
 663     $groupID2 = $this->setUpPopulatedSmartGroup();
 664     $rows = $this->callAPISuccess('report_template', 'getrows', [
 665       'report_id' => 'contribute/summary',
 666       'gid_value' => [$groupID, $groupID2],
 667       'gid_op' => 'in',
 668       'options' => ['metadata' => ['sql']],
 669     ]);
 670     $this->assertEquals(4, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 671   }
 672
 673   /**
 674    * Test we don't get a fatal grouping  by only contribution status id.
 675    *
 676    * @throws \CRM_Core_Exception
 677    */
 678   public function testContributionSummaryGroupByContributionStatus() {
 679     $params = [
 680       'report_id' => 'contribute/summary',
 681       'fields' => ['total_amount' => 1, 'country_id' => 1],
 682       'group_bys' => ['contribution_status_id' => 1],
 683       'options' => ['metadata' => ['sql']],
 684     ];
 685     $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
 686     $this->assertContains('GROUP BY contribution_civireport.contribution_status_id WITH ROLLUP', $rowsSql[0]);
 687     $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
 688     $this->assertContains('GROUP BY contribution_civireport.contribution_status_id, currency', $statsSql[2]);
 689   }
 690
 691   /**
 692    * Test we don't get a fatal grouping  by only contribution status id.
 693    *
 694    * @throws \CRM_Core_Exception
 695    */
 696   public function testContributionSummaryGroupByYearFrequency() {
 697     $params = [
 698       'report_id' => 'contribute/summary',
 699       'fields' => ['total_amount' => 1, 'country_id' => 1],
 700       'group_bys' => ['receive_date' => 1],
 701       'group_bys_freq' => ['receive_date' => 'YEAR'],
 702       'options' => ['metadata' => ['sql']],
 703     ];
 704     $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
 705     $this->assertContains('GROUP BY  YEAR(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]);
 706     $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
 707     $this->assertContains('GROUP BY  YEAR(contribution_civireport.receive_date), currency', $statsSql[2]);
 708   }
 709
 710   /**
 711    * Test we don't get a fatal grouping with QUARTER frequency.
 712    *
 713    * @throws \CRM_Core_Exception
 714    */
 715   public function testContributionSummaryGroupByYearQuarterFrequency() {
 716     $params = [
 717       'report_id' => 'contribute/summary',
 718       'fields' => ['total_amount' => 1, 'country_id' => 1],
 719       'group_bys' => ['receive_date' => 1],
 720       'group_bys_freq' => ['receive_date' => 'QUARTER'],
 721       'options' => ['metadata' => ['sql']],
 722     ];
 723     $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
 724     $this->assertContains('GROUP BY YEAR(contribution_civireport.receive_date), QUARTER(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]);
 725     $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
 726     $this->assertContains('GROUP BY YEAR(contribution_civireport.receive_date), QUARTER(contribution_civireport.receive_date), currency', $statsSql[2]);
 727   }
 728
 729   /**
 730    * Test we don't get a fatal grouping with QUARTER frequency.
 731    *
 732    * @throws \CRM_Core_Exception
 733    */
 734   public function testContributionSummaryGroupByDateFrequency() {
 735     $params = [
 736       'report_id' => 'contribute/summary',
 737       'fields' => ['total_amount' => 1, 'country_id' => 1],
 738       'group_bys' => ['receive_date' => 1],
 739       'group_bys_freq' => ['receive_date' => 'DATE'],
 740       'options' => ['metadata' => ['sql']],
 741     ];
 742     $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
 743     $this->assertContains('GROUP BY DATE(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]);
 744     $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
 745     $this->assertContains('GROUP BY DATE(contribution_civireport.receive_date), currency', $statsSql[2]);
 746   }
 747
 748   /**
 749    * Test we don't get a fatal grouping with QUARTER frequency.
 750    *
 751    * @throws \CRM_Core_Exception
 752    */
 753   public function testContributionSummaryGroupByWeekFrequency() {
 754     $params = [
 755       'report_id' => 'contribute/summary',
 756       'fields' => ['total_amount' => 1, 'country_id' => 1],
 757       'group_bys' => ['receive_date' => 1],
 758       'group_bys_freq' => ['receive_date' => 'YEARWEEK'],
 759       'options' => ['metadata' => ['sql']],
 760     ];
 761     $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
 762     $this->assertContains('GROUP BY YEARWEEK(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]);
 763     $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
 764     $this->assertContains('GROUP BY YEARWEEK(contribution_civireport.receive_date), currency', $statsSql[2]);
 765   }
 766
 767   /**
 768    * CRM-20640: Test the group filter works on the contribution summary when a single contact in 2 groups.
 769    *
 770    * @throws \CRM_Core_Exception
 771    */
 772   public function testContributionSummaryWithSingleContactsInTwoGroups() {
 773     list($groupID1, $individualID) = $this->setUpPopulatedGroup(TRUE);
 774     // create second group and add the individual to it.
 775     $groupID2 = $this->groupCreate(['name' => uniqid(), 'title' => uniqid()]);
 776     $this->callAPISuccess('GroupContact', 'create', [
 777       'group_id' => $groupID2,
 778       'contact_id' => $individualID,
 779       'status' => 'Added',
 780     ]);
 781
 782     $rows = $this->callAPISuccess('report_template', 'getrows', [
 783       'report_id' => 'contribute/summary',
 784       'gid_value' => [$groupID1, $groupID2],
 785       'gid_op' => 'in',
 786       'options' => ['metadata' => ['sql']],
 787     ]);
 788     $this->assertEquals(1, $rows['count']);
 789   }
 790
 791   /**
 792    * Test the group filter works on the contribution summary when 2 groups are involved.
 793    *
 794    * @throws \CRM_Core_Exception
 795    */
 796   public function testContributionSummaryWithTwoGroupsWithIntersection() {
 797     $groups = $this->setUpIntersectingGroups();
 798
 799     $rows = $this->callAPISuccess('report_template', 'getrows', [
 800       'report_id' => 'contribute/summary',
 801       'gid_value' => $groups,
 802       'gid_op' => 'in',
 803       'options' => ['metadata' => ['sql']],
 804     ]);
 805     $this->assertEquals(7, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 806   }
 807
 808   /**
 809    * Test date field is correctly handled.
 810    *
 811    * @throws \CRM_Core_Exception
 812    */
 813   public function testContributionSummaryDateFields() {
 814     $sql = $this->callAPISuccess('report_template', 'getrows', [
 815       'report_id' => 'contribute/summary',
 816       'thankyou_date_relative' => '0',
 817       'thankyou_date_from' => '2020-03-01 00:00:00',
 818       'thankyou_date_to' => '2020-03-31 23:59:59',
 819       'options' => ['metadata' => ['sql']],
 820     ])['metadata']['sql'];
 821     $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
 822              INNER JOIN civicrm_contribution   contribution_civireport
 823                      ON contact_civireport.id = contribution_civireport.contact_id AND
 824                         contribution_civireport.is_test = 0
 825              LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
 826                        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)
 827              LEFT  JOIN civicrm_financial_type  financial_type_civireport
 828                      ON contribution_civireport.financial_type_id =financial_type_civireport.id
 829
 830                  LEFT JOIN civicrm_address address_civireport
 831                            ON (contact_civireport.id =
 832                                address_civireport.contact_id)  AND
 833                                address_civireport.is_primary = 1
 834  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';
 835     $this->assertLike($expectedSql, $sql[0]);
 836   }
 837
 838   /**
 839    * Set up a smart group for testing.
 840    *
 841    * The smart group includes all Households by filter. In addition an individual
 842    * is created and hard-added and an individual is created that is not added.
 843    *
 844    * One household is hard-added as well as being in the filter.
 845    *
 846    * This gives us a range of scenarios for testing contacts are included only once
 847    * whenever they are hard-added or in the criteria.
 848    *
 849    * @return int
 850    * @throws \CRM_Core_Exception
 851    */
 852   public function setUpPopulatedSmartGroup() {
 853     $household1ID = $this->householdCreate();
 854     $individual1ID = $this->individualCreate();
 855     $householdID = $this->householdCreate();
 856     $individualID = $this->individualCreate();
 857     $individualIDRemoved = $this->individualCreate();
 858     $groupID = $this->smartGroupCreate([], ['name' => uniqid(), 'title' => uniqid()]);
 859     $this->callAPISuccess('GroupContact', 'create', [
 860       'group_id' => $groupID,
 861       'contact_id' => $individualIDRemoved,
 862       'status' => 'Removed',
 863     ]);
 864     $this->callAPISuccess('GroupContact', 'create', [
 865       'group_id' => $groupID,
 866       'contact_id' => $individualID,
 867       'status' => 'Added',
 868     ]);
 869     $this->callAPISuccess('GroupContact', 'create', [
 870       'group_id' => $groupID,
 871       'contact_id' => $householdID,
 872       'status' => 'Added',
 873     ]);
 874     foreach ([$household1ID, $individual1ID, $householdID, $individualID, $individualIDRemoved] as $contactID) {
 875       $this->contributionCreate(['contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => '']);
 876       $this->contactMembershipCreate(['contact_id' => $contactID]);
 877     }
 878
 879     // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
 880     CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID);
 881     return $groupID;
 882   }
 883
 884   /**
 885    * Set up a static group for testing.
 886    *
 887    * An individual is created and hard-added and an individual is created that is not added.
 888    *
 889    * This gives us a range of scenarios for testing contacts are included only once
 890    * whenever they are hard-added or in the criteria.
 891    *
 892    * @param bool $returnAddedContact
 893    *
 894    * @return int
 895    * @throws \CRM_Core_Exception
 896    */
 897   public function setUpPopulatedGroup($returnAddedContact = FALSE) {
 898     $individual1ID = $this->individualCreate();
 899     $individualID = $this->individualCreate();
 900     $individualIDRemoved = $this->individualCreate();
 901     $groupID = $this->groupCreate(['name' => uniqid(), 'title' => uniqid()]);
 902     $this->callAPISuccess('GroupContact', 'create', [
 903       'group_id' => $groupID,
 904       'contact_id' => $individualIDRemoved,
 905       'status' => 'Removed',
 906     ]);
 907     $this->callAPISuccess('GroupContact', 'create', [
 908       'group_id' => $groupID,
 909       'contact_id' => $individualID,
 910       'status' => 'Added',
 911     ]);
 912
 913     foreach ([$individual1ID, $individualID, $individualIDRemoved] as $contactID) {
 914       $this->contributionCreate(['contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => '']);
 915       $this->contactMembershipCreate(['contact_id' => $contactID]);
 916     }
 917
 918     // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
 919     CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID);
 920
 921     if ($returnAddedContact) {
 922       return [$groupID, $individualID];
 923     }
 924
 925     return $groupID;
 926   }
 927
 928   /**
 929    * @return array
 930    *
 931    * @throws \CRM_Core_Exception
 932    */
 933   public function setUpIntersectingGroups() {
 934     $groupID = $this->setUpPopulatedGroup();
 935     $groupID2 = $this->setUpPopulatedSmartGroup();
 936     $addedToBothIndividualID = $this->individualCreate();
 937     $removedFromBothIndividualID = $this->individualCreate();
 938     $addedToSmartGroupRemovedFromOtherIndividualID = $this->individualCreate();
 939     $removedFromSmartGroupAddedToOtherIndividualID = $this->individualCreate();
 940     $this->callAPISuccess('GroupContact', 'create', [
 941       'group_id' => $groupID,
 942       'contact_id' => $addedToBothIndividualID,
 943       'status' => 'Added',
 944     ]);
 945     $this->callAPISuccess('GroupContact', 'create', [
 946       'group_id' => $groupID2,
 947       'contact_id' => $addedToBothIndividualID,
 948       'status' => 'Added',
 949     ]);
 950     $this->callAPISuccess('GroupContact', 'create', [
 951       'group_id' => $groupID,
 952       'contact_id' => $removedFromBothIndividualID,
 953       'status' => 'Removed',
 954     ]);
 955     $this->callAPISuccess('GroupContact', 'create', [
 956       'group_id' => $groupID2,
 957       'contact_id' => $removedFromBothIndividualID,
 958       'status' => 'Removed',
 959     ]);
 960     $this->callAPISuccess('GroupContact', 'create', [
 961       'group_id' => $groupID2,
 962       'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
 963       'status' => 'Added',
 964     ]);
 965     $this->callAPISuccess('GroupContact', 'create', [
 966       'group_id' => $groupID,
 967       'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
 968       'status' => 'Removed',
 969     ]);
 970     $this->callAPISuccess('GroupContact', 'create', [
 971       'group_id' => $groupID,
 972       'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
 973       'status' => 'Added',
 974     ]);
 975     $this->callAPISuccess('GroupContact', 'create', [
 976       'group_id' => $groupID2,
 977       'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
 978       'status' => 'Removed',
 979     ]);
 980
 981     foreach ([
 982       $addedToBothIndividualID,
 983       $removedFromBothIndividualID,
 984       $addedToSmartGroupRemovedFromOtherIndividualID,
 985       $removedFromSmartGroupAddedToOtherIndividualID,
 986     ] as $contactID) {
 987       $this->contributionCreate([
 988         'contact_id' => $contactID,
 989         'invoice_id' => '',
 990         'trxn_id' => '',
 991       ]);
 992     }
 993     return [$groupID, $groupID2];
 994   }
 995
 996   /**
 997    * Test Deferred Revenue Report.
 998    *
 999    * @throws \CRM_Core_Exception
1000    */
1001   public function testDeferredRevenueReport() {
1002     $indv1 = $this->individualCreate();
1003     $indv2 = $this->individualCreate();
1004     Civi::settings()->set('deferred_revenue_enabled', TRUE);
1005     $this->contributionCreate(
1006       [
1007         'contact_id' => $indv1,
1008         'receive_date' => '2016-10-01',
1009         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+3 month')),
1010         'financial_type_id' => 2,
1011       ]
1012     );
1013     $this->contributionCreate(
1014       [
1015         'contact_id' => $indv1,
1016         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+22 month')),
1017         'financial_type_id' => 4,
1018         'trxn_id' => NULL,
1019         'invoice_id' => NULL,
1020       ]
1021     );
1022     $this->contributionCreate(
1023       [
1024         'contact_id' => $indv2,
1025         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+1 month')),
1026         'financial_type_id' => 4,
1027         'trxn_id' => NULL,
1028         'invoice_id' => NULL,
1029       ]
1030     );
1031     $this->contributionCreate(
1032       [
1033         'contact_id' => $indv2,
1034         'receive_date' => '2016-03-01',
1035         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+4 month')),
1036         'financial_type_id' => 2,
1037         'trxn_id' => NULL,
1038         'invoice_id' => NULL,
1039       ]
1040     );
1041     $rows = $this->callAPISuccess('report_template', 'getrows', [
1042       'report_id' => 'contribute/deferredrevenue',
1043     ]);
1044     $this->assertEquals(2, $rows['count'], "Report failed to get row count");
1045     $count = [2, 1];
1046     foreach ($rows['values'] as $row) {
1047       $this->assertEquals(array_pop($count), count($row['rows']), "Report failed to get row count");
1048     }
1049   }
1050
1051   /**
1052    * Test the custom data order by works when not in select.
1053    *
1054    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
1055    *
1056    * @param string $template
1057    *   Report template unique identifier.
1058    *
1059    * @throws \CRM_Core_Exception
1060    */
1061   public function testReportsCustomDataOrderBy($template) {
1062     $this->entity = 'Contact';
1063     $this->createCustomGroupWithFieldOfType();
1064     $this->callAPISuccess('report_template', 'getrows', [
1065       'report_id' => $template,
1066       'contribution_or_soft_value' => 'contributions_only',
1067       'order_bys' => [['column' => 'custom_' . $this->ids['CustomField']['text'], 'order' => 'ASC']],
1068     ]);
1069   }
1070
1071   /**
1072    * Test the group filter works on the various reports.
1073    *
1074    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
1075    *
1076    * @param string $template
1077    *   Report template unique identifier.
1078    *
1079    * @throws \CRM_Core_Exception
1080    */
1081   public function testReportsWithNoTInSmartGroupFilter($template) {
1082     $groupID = $this->setUpPopulatedGroup();
1083     $rows = $this->callAPISuccess('report_template', 'getrows', [
1084       'report_id' => $template,
1085       'gid_value' => [$groupID],
1086       'gid_op' => 'notin',
1087       'options' => ['metadata' => ['sql']],
1088     ]);
1089     $this->assertNumberOfContactsInResult(2, $rows, $template);
1090   }
1091
1092   /**
1093    * Test we don't get a fatal grouping with various frequencies.
1094    *
1095    * @throws \CRM_Core_Exception
1096    */
1097   public function testActivitySummaryGroupByFrequency() {
1098     $this->createContactsWithActivities();
1099     foreach (['MONTH', 'YEARWEEK', 'QUARTER', 'YEAR'] as $frequency) {
1100       $params = [
1101         'report_id' => 'activitySummary',
1102         'fields' => [
1103           'activity_type_id' => 1,
1104           'duration' => 1,
1105           // id is "total activities", which is required by default(?)
1106           'id' => 1,
1107         ],
1108         'group_bys' => ['activity_date_time' => 1],
1109         'group_bys_freq' => ['activity_date_time' => $frequency],
1110         'options' => ['metadata' => ['sql']],
1111       ];
1112       $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql'];
1113       $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql'];
1114       switch ($frequency) {
1115         case 'YEAR':
1116           // Year only contains one grouping.
1117           // Also note the extra space.
1118           $this->assertContains('GROUP BY  YEAR(activity_civireport.activity_date_time)', $rowsSql[1], "Failed for frequency $frequency");
1119           $this->assertContains('GROUP BY  YEAR(activity_civireport.activity_date_time)', $statsSql[1], "Failed for frequency $frequency");
1120           break;
1121
1122         default:
1123           $this->assertContains("GROUP BY YEAR(activity_civireport.activity_date_time), {$frequency}(activity_civireport.activity_date_time)", $rowsSql[1], "Failed for frequency $frequency");
1124           $this->assertContains("GROUP BY YEAR(activity_civireport.activity_date_time), {$frequency}(activity_civireport.activity_date_time)", $statsSql[1], "Failed for frequency $frequency");
1125           break;
1126       }
1127     }
1128   }
1129
1130   /**
1131    * Test activity details report - requiring all current fields to be output.
1132    */
1133   public function testActivityDetails() {
1134     $this->createContactsWithActivities();
1135     $fields = [
1136       'contact_source' => '1',
1137       'contact_assignee' => '1',
1138       'contact_target' => '1',
1139       'contact_source_email' => '1',
1140       'contact_assignee_email' => '1',
1141       'contact_target_email' => '1',
1142       'contact_source_phone' => '1',
1143       'contact_assignee_phone' => '1',
1144       'contact_target_phone' => '1',
1145       'activity_type_id' => '1',
1146       'activity_subject' => '1',
1147       'activity_date_time' => '1',
1148       'status_id' => '1',
1149       'duration' => '1',
1150       'location' => '1',
1151       'details' => '1',
1152       'priority_id' => '1',
1153       'result' => '1',
1154       'engagement_level' => '1',
1155       'address_name' => '1',
1156       'street_address' => '1',
1157       'supplemental_address_1' => '1',
1158       'supplemental_address_2' => '1',
1159       'supplemental_address_3' => '1',
1160       'street_number' => '1',
1161       'street_name' => '1',
1162       'street_unit' => '1',
1163       'city' => '1',
1164       'postal_code' => '1',
1165       'postal_code_suffix' => '1',
1166       'country_id' => '1',
1167       'state_province_id' => '1',
1168       'county_id' => '1',
1169     ];
1170     $params = [
1171       'fields' => $fields,
1172       'current_user_op' => 'eq',
1173       'current_user_value' => '0',
1174       'include_case_activities_op' => 'eq',
1175       'include_case_activities_value' => 0,
1176       'order_bys' => [
1177         1 => ['column' => 'activity_date_time', 'order' => 'ASC'],
1178         2 => ['column' => 'activity_type_id', 'order' => 'ASC'],
1179       ],
1180     ];
1181
1182     $params['report_id'] = 'Activity';
1183
1184     $rows = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1185     $expected = [
1186       'civicrm_contact_contact_source' => 'Łąchowski-Roberts, Anthony',
1187       '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>',
1188       '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>',
1189       'civicrm_contact_contact_source_id' => $this->contactIDs[2],
1190       'civicrm_contact_contact_assignee_id' => $this->contactIDs[1],
1191       'civicrm_contact_contact_target_id' => $this->contactIDs[0] . ';' . $this->contactIDs[1],
1192       'civicrm_email_contact_source_email' => 'anthony_anderson@civicrm.org',
1193       'civicrm_email_contact_assignee_email' => 'anthony_anderson@civicrm.org',
1194       'civicrm_email_contact_target_email' => 'techo@spying.com;anthony_anderson@civicrm.org',
1195       'civicrm_phone_contact_source_phone' => NULL,
1196       'civicrm_phone_contact_assignee_phone' => NULL,
1197       'civicrm_phone_contact_target_phone' => NULL,
1198       'civicrm_activity_id' => '1',
1199       'civicrm_activity_source_record_id' => NULL,
1200       'civicrm_activity_activity_type_id' => 'Meeting',
1201       'civicrm_activity_activity_subject' => 'Very secret meeting',
1202       'civicrm_activity_activity_date_time' => date('Y-m-d 23:59:58', strtotime('now')),
1203       'civicrm_activity_status_id' => 'Scheduled',
1204       'civicrm_activity_duration' => '120',
1205       'civicrm_activity_location' => 'Pennsylvania',
1206       'civicrm_activity_details' => 'a test activity',
1207       'civicrm_activity_priority_id' => 'Normal',
1208       'civicrm_address_address_name' => NULL,
1209       'civicrm_address_street_address' => NULL,
1210       'civicrm_address_supplemental_address_1' => NULL,
1211       'civicrm_address_supplemental_address_2' => NULL,
1212       'civicrm_address_supplemental_address_3' => NULL,
1213       'civicrm_address_street_number' => NULL,
1214       'civicrm_address_street_name' => NULL,
1215       'civicrm_address_street_unit' => NULL,
1216       'civicrm_address_city' => NULL,
1217       'civicrm_address_postal_code' => NULL,
1218       'civicrm_address_postal_code_suffix' => NULL,
1219       'civicrm_address_country_id' => NULL,
1220       'civicrm_address_state_province_id' => NULL,
1221       'civicrm_address_county_id' => NULL,
1222       'civicrm_contact_contact_source_link' => '/index.php?q=civicrm/contact/view&amp;reset=1&amp;cid=' . $this->contactIDs[2],
1223       'civicrm_contact_contact_source_hover' => 'View Contact Summary for this Contact',
1224       'civicrm_activity_activity_type_id_hover' => 'View Activity Record',
1225     ];
1226     $row = $rows[0];
1227     // This link is not relative - skip for now
1228     unset($row['civicrm_activity_activity_type_id_link']);
1229     if ($row['civicrm_email_contact_target_email'] === 'anthony_anderson@civicrm.org;techo@spying.com') {
1230       // order is unpredictable
1231       $expected['civicrm_email_contact_target_email'] = 'anthony_anderson@civicrm.org;techo@spying.com';
1232     }
1233
1234     $this->assertEquals($expected, $row);
1235   }
1236
1237   /**
1238    * Activity Details report has some whack-a-mole to fix when filtering on null/not null.
1239    */
1240   public function testActivityDetailsNullFilters() {
1241     $this->createContactsWithActivities();
1242     $params = [
1243       'report_id' => 'activity',
1244       'location_op' => 'nll',
1245       'location_value' => '',
1246     ];
1247     $rowsWithoutLocation = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1248     $this->assertEmpty($rowsWithoutLocation);
1249     $params['location_op'] = 'nnll';
1250     $rowsWithLocation = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1251     $this->assertCount(1, $rowsWithLocation);
1252     // Test for CRM-18356 - activity shouldn't appear if target contact filter is null.
1253     $params = [
1254       'report_id' => 'activity',
1255       'contact_target_op' => 'nll',
1256       'contact_target_value' => '',
1257     ];
1258     $rowsWithNullTarget = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
1259     $this->assertEmpty($rowsWithNullTarget);
1260   }
1261
1262   /**
1263    * Set up some activity data..... use some chars that challenge our utf handling.
1264    */
1265   public function createContactsWithActivities() {
1266     $this->contactIDs[] = $this->individualCreate(['last_name' => 'Brzęczysław', 'email' => 'techo@spying.com']);
1267     $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']);
1268     $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']);
1269
1270     $this->callAPISuccess('Activity', 'create', [
1271       'subject' => 'Very secret meeting',
1272       'activity_date_time' => date('Y-m-d 23:59:58', strtotime('now')),
1273       'duration' => 120,
1274       'location' => 'Pennsylvania',
1275       'details' => 'a test activity',
1276       'status_id' => 1,
1277       'activity_type_id' => 'Meeting',
1278       'source_contact_id' => $this->contactIDs[2],
1279       'target_contact_id' => [$this->contactIDs[0], $this->contactIDs[1]],
1280       'assignee_contact_id' => $this->contactIDs[1],
1281     ]);
1282   }
1283
1284   /**
1285    * Test the group filter works on the contribution summary.
1286    */
1287   public function testContributionDetailTotalHeader() {
1288     $contactID = $this->individualCreate();
1289     $contactID2 = $this->individualCreate();
1290     $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
1291     $template = 'contribute/detail';
1292     $rows = $this->callAPISuccess('report_template', 'getrows', [
1293       'report_id' => $template,
1294       'contribution_or_soft_value' => 'contributions_only',
1295       'fields' => [
1296         'sort_name' => '1',
1297         'age' => '1',
1298         'email' => '1',
1299         'phone' => '1',
1300         'financial_type_id' => '1',
1301         'receive_date' => '1',
1302         'total_amount' => '1',
1303       ],
1304       'order_bys' => [['column' => 'sort_name', 'order' => 'ASC', 'section' => '1']],
1305       'options' => ['metadata' => ['sql']],
1306     ]);
1307   }
1308
1309   /**
1310    * Test contact subtype filter on grant report.
1311    */
1312   public function testGrantReportSeparatedFilter() {
1313     $contactID = $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1314     $contactID2 = $this->individualCreate();
1315     $this->callAPISuccess('Grant', 'create', ['contact_id' => $contactID, 'status_id' => 1, 'grant_type_id' => 1, 'amount_total' => 1]);
1316     $this->callAPISuccess('Grant', 'create', ['contact_id' => $contactID2, 'status_id' => 1, 'grant_type_id' => 1, 'amount_total' => 1]);
1317     $rows = $this->callAPISuccess('report_template', 'getrows', [
1318       'report_id' => 'grant/detail',
1319       'contact_sub_type_op' => 'in',
1320       'contact_sub_type_value' => ['Student'],
1321     ]);
1322     $this->assertEquals(1, $rows['count']);
1323   }
1324
1325   /**
1326    * Test contact subtype filter on summary report.
1327    *
1328    * @throws \CRM_Core_Exception
1329    */
1330   public function testContactSubtypeNotNull() {
1331     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1332     $this->individualCreate();
1333
1334     $rows = $this->callAPISuccess('report_template', 'getrows', [
1335       'report_id' => 'contact/summary',
1336       'contact_sub_type_op' => 'nnll',
1337       'contact_sub_type_value' => [],
1338       'contact_type_op' => 'eq',
1339       'contact_type_value' => 'Individual',
1340     ]);
1341     $this->assertEquals(1, $rows['count']);
1342   }
1343
1344   /**
1345    * Test contact subtype filter on summary report.
1346    *
1347    * @throws \CRM_Core_Exception
1348    */
1349   public function testContactSubtypeNull() {
1350     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1351     $this->individualCreate();
1352
1353     $rows = $this->callAPISuccess('report_template', 'getrows', [
1354       'report_id' => 'contact/summary',
1355       'contact_sub_type_op' => 'nll',
1356       'contact_sub_type_value' => [],
1357       'contact_type_op' => 'eq',
1358       'contact_type_value' => 'Individual',
1359     ]);
1360     $this->assertEquals(1, $rows['count']);
1361   }
1362
1363   /**
1364    * Test contact subtype filter on summary report.
1365    *
1366    * @throws \CRM_Core_Exception
1367    */
1368   public function testContactSubtypeIn() {
1369     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1370     $this->individualCreate();
1371
1372     $rows = $this->callAPISuccess('report_template', 'getrows', [
1373       'report_id' => 'contact/summary',
1374       'contact_sub_type_op' => 'in',
1375       'contact_sub_type_value' => ['Student'],
1376       'contact_type_op' => 'in',
1377       'contact_type_value' => 'Individual',
1378     ]);
1379     $this->assertEquals(1, $rows['count']);
1380   }
1381
1382   /**
1383    * Test contact subtype filter on summary report.
1384    *
1385    * @throws \CRM_Core_Exception
1386    */
1387   public function testContactSubtypeNotIn() {
1388     $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]);
1389     $this->individualCreate();
1390
1391     $rows = $this->callAPISuccess('report_template', 'getrows', [
1392       'report_id' => 'contact/summary',
1393       'contact_sub_type_op' => 'notin',
1394       'contact_sub_type_value' => ['Student'],
1395       'contact_type_op' => 'in',
1396       'contact_type_value' => 'Individual',
1397     ]);
1398     $this->assertEquals(1, $rows['count']);
1399   }
1400
1401   /**
1402    * Test PCP report to ensure total donors and total committed is accurate.
1403    */
1404   public function testPcpReportTotals() {
1405     $donor1ContactId = $this->individualCreate();
1406     $donor2ContactId = $this->individualCreate();
1407     $donor3ContactId = $this->individualCreate();
1408
1409     // We are going to create two PCP pages. We will create two contributions
1410     // on the first PCP page and one contribution on the second PCP page.
1411     //
1412     // Then, we will ensure that the first PCP page reports a total of both
1413     // contributions (but not the contribution made on the second PCP page).
1414
1415     // A PCP page requires three components:
1416     // 1. A contribution page
1417     // 2. A PCP Block
1418     // 3. A PCP page
1419
1420     // pcpBLockParams creates a contribution page and returns the parameters
1421     // necessary to create a PBP Block.
1422     $blockParams = $this->pcpBlockParams();
1423     $pcpBlock = CRM_PCP_BAO_PCPBlock::create($blockParams);
1424
1425     // Keep track of the contribution page id created. We will use this
1426     // contribution page id for all the PCP pages.
1427     $contribution_page_id = $pcpBlock->entity_id;
1428
1429     // pcpParams returns the parameters needed to create a PCP page.
1430     $pcpParams = $this->pcpParams();
1431     // Keep track of the owner of the page so we can properly apply the
1432     // soft credit.
1433     $pcpOwnerContact1Id = $pcpParams['contact_id'];
1434     $pcpParams['pcp_block_id'] = $pcpBlock->id;
1435     $pcpParams['page_id'] = $contribution_page_id;
1436     $pcpParams['page_type'] = 'contribute';
1437     $pcp1 = CRM_PCP_BAO_PCP::create($pcpParams);
1438
1439     // Nice work. Now, let's create a second PCP page.
1440     $pcpParams = $this->pcpParams();
1441     // Keep track of the owner of the page.
1442     $pcpOwnerContact2Id = $pcpParams['contact_id'];
1443     // We're using the same pcpBlock id and contribution page that we created above.
1444     $pcpParams['pcp_block_id'] = $pcpBlock->id;
1445     $pcpParams['page_id'] = $contribution_page_id;
1446     $pcpParams['page_type'] = 'contribute';
1447     $pcp2 = CRM_PCP_BAO_PCP::create($pcpParams);
1448
1449     // Get soft credit types, with the name column as the key.
1450     $soft_credit_types = CRM_Contribute_BAO_ContributionSoft::buildOptions("soft_credit_type_id", NULL, ["flip" => TRUE, 'labelColumn' => 'name']);
1451     $pcp_soft_credit_type_id = $soft_credit_types['pcp'];
1452
1453     // Create two contributions assigned to this contribution page and
1454     // assign soft credits appropriately.
1455     // FIRST...
1456     $contribution1params = [
1457       'contact_id' => $donor1ContactId,
1458       'contribution_page_id' => $contribution_page_id,
1459       'total_amount' => '75.00',
1460     ];
1461     $c1 = $this->contributionCreate($contribution1params);
1462     // Now the soft contribution.
1463     $p = [
1464       'contribution_id' => $c1,
1465       'pcp_id' => $pcp1->id,
1466       'contact_id' => $pcpOwnerContact1Id,
1467       'amount' => 75.00,
1468       'currency' => 'USD',
1469       'soft_credit_type_id' => $pcp_soft_credit_type_id,
1470     ];
1471     $this->callAPISuccess('contribution_soft', 'create', $p);
1472     // SECOND...
1473     $contribution2params = [
1474       'contact_id' => $donor2ContactId,
1475       'contribution_page_id' => $contribution_page_id,
1476       'total_amount' => '25.00',
1477     ];
1478     $c2 = $this->contributionCreate($contribution2params);
1479     // Now the soft contribution.
1480     $p = [
1481       'contribution_id' => $c2,
1482       'pcp_id' => $pcp1->id,
1483       'contact_id' => $pcpOwnerContact1Id,
1484       'amount' => 25.00,
1485       'currency' => 'USD',
1486       'soft_credit_type_id' => $pcp_soft_credit_type_id,
1487     ];
1488     $this->callAPISuccess('contribution_soft', 'create', $p);
1489
1490     // Create one contributions assigned to the second PCP page
1491     $contribution3params = [
1492       'contact_id' => $donor3ContactId,
1493       'contribution_page_id' => $contribution_page_id,
1494       'total_amount' => '200.00',
1495     ];
1496     $c3 = $this->contributionCreate($contribution3params);
1497     // Now the soft contribution.
1498     $p = [
1499       'contribution_id' => $c3,
1500       'pcp_id' => $pcp2->id,
1501       'contact_id' => $pcpOwnerContact2Id,
1502       'amount' => 200.00,
1503       'currency' => 'USD',
1504       'soft_credit_type_id' => $pcp_soft_credit_type_id,
1505     ];
1506     $this->callAPISuccess('contribution_soft', 'create', $p);
1507
1508     $template = 'contribute/pcp';
1509     $rows = $this->callAPISuccess('report_template', 'getrows', [
1510       'report_id' => $template,
1511       'title' => 'My PCP',
1512       'fields' => [
1513         'amount_1' => '1',
1514         'soft_id' => '1',
1515       ],
1516     ]);
1517     $values = $rows['values'][0];
1518     $this->assertEquals(100.00, $values['civicrm_contribution_soft_amount_1_sum'], "Total commited should be $100");
1519     $this->assertEquals(2, $values['civicrm_contribution_soft_soft_id_count'], "Total donors should be 2");
1520   }
1521
1522   /**
1523    * Test a report that uses getAddressColumns();
1524    */
1525   public function testGetAddressColumns() {
1526     $template = 'event/participantlisting';
1527     $this->callAPISuccess('report_template', 'getrows', [
1528       'report_id' => $template,
1529       'fields' => [
1530         'sort_name' => '1',
1531         'street_address' => '1',
1532       ],
1533     ]);
1534   }
1535
1536 }