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