tests/phpunit/api/v3/ReportTemplateTest.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *  Test APIv3 civicrm_report_instance_* functions
  30  *
  31  * @package CiviCRM_APIv3
  32  * @subpackage API_Report
  33  * @group headless
  34  */
  35 class api_v3_ReportTemplateTest extends CiviUnitTestCase {
  36
  37   use CRMTraits_ACL_PermissionTrait;
  38
  39   protected $_apiversion = 3;
  40
  41   protected $contactIDs = [];
  42
  43   /**
  44    * Our group reports use an alter so transaction cleanup won't work.
  45    *
  46    * @throws \Exception
  47    */
  48   public function tearDown() {
  49     $this->quickCleanUpFinancialEntities();
  50     $this->quickCleanup(array('civicrm_group', 'civicrm_saved_search', 'civicrm_group_contact', 'civicrm_group_contact_cache', 'civicrm_group'));
  51     parent::tearDown();
  52   }
  53
  54   public function testReportTemplate() {
  55     $result = $this->callAPISuccess('ReportTemplate', 'create', array(
  56       'label' => 'Example Form',
  57       'description' => 'Longish description of the example form',
  58       'class_name' => 'CRM_Report_Form_Examplez',
  59       'report_url' => 'example/path',
  60       'component' => 'CiviCase',
  61     ));
  62     $this->assertAPISuccess($result);
  63     $this->assertEquals(1, $result['count']);
  64     $entityId = $result['id'];
  65     $this->assertTrue(is_numeric($entityId));
  66     $this->assertEquals(7, $result['values'][$entityId]['component_id']);
  67     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  68       WHERE name = "CRM_Report_Form_Examplez"
  69       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  70     $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value
  71       WHERE name = "CRM_Report_Form_Examplez"');
  72
  73     // change component to null
  74     $result = $this->callAPISuccess('ReportTemplate', 'create', array(
  75       'id' => $entityId,
  76       'component' => '',
  77     ));
  78     $this->assertAPISuccess($result);
  79     $this->assertEquals(1, $result['count']);
  80     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  81       WHERE name = "CRM_Report_Form_Examplez"
  82       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  83     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  84       WHERE name = "CRM_Report_Form_Examplez"
  85       AND component_id IS NULL');
  86
  87     // deactivate
  88     $result = $this->callAPISuccess('ReportTemplate', 'create', array(
  89       'id' => $entityId,
  90       'is_active' => 0,
  91     ));
  92     $this->assertAPISuccess($result);
  93     $this->assertEquals(1, $result['count']);
  94     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  95       WHERE name = "CRM_Report_Form_Examplez"
  96       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  97     $this->assertDBQuery(0, 'SELECT is_active FROM civicrm_option_value
  98       WHERE name = "CRM_Report_Form_Examplez"');
  99
 100     // activate
 101     $result = $this->callAPISuccess('ReportTemplate', 'create', array(
 102       'id' => $entityId,
 103       'is_active' => 1,
 104     ));
 105     $this->assertAPISuccess($result);
 106     $this->assertEquals(1, $result['count']);
 107     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
 108       WHERE name = "CRM_Report_Form_Examplez"
 109       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
 110     $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value
 111       WHERE name = "CRM_Report_Form_Examplez"');
 112
 113     $result = $this->callAPISuccess('ReportTemplate', 'delete', array(
 114       'id' => $entityId,
 115     ));
 116     $this->assertAPISuccess($result);
 117     $this->assertEquals(1, $result['count']);
 118     $this->assertDBQuery(0, 'SELECT count(*) FROM civicrm_option_value
 119       WHERE name = "CRM_Report_Form_Examplez"
 120       ');
 121   }
 122
 123   /**
 124    * Test api to get rows from reports.
 125    *
 126    * @dataProvider getReportTemplatesSupportingSelectWhere
 127    *
 128    * @param $reportID
 129    *
 130    * @throws \PHPUnit_Framework_IncompleteTestError
 131    */
 132   public function testReportTemplateSelectWhere($reportID) {
 133     $this->hookClass->setHook('civicrm_selectWhereClause', array($this, 'hookSelectWhere'));
 134     $result = $this->callAPISuccess('report_template', 'getrows', [
 135       'report_id' => $reportID,
 136       'options' => ['metadata' => ['sql']],
 137     ]);
 138     $found = FALSE;
 139     foreach ($result['metadata']['sql'] as $sql) {
 140       if (strstr($sql, " =  'Organization' ")) {
 141         $found = TRUE;
 142       }
 143     }
 144     $this->assertTrue($found, $reportID);
 145   }
 146
 147   /**
 148    * Get templates suitable for SelectWhere test.
 149    *
 150    * @return array
 151    */
 152   public function getReportTemplatesSupportingSelectWhere() {
 153     $allTemplates = $this->getReportTemplates();
 154     // Exclude all that do not work as of test being written. I have not dug into why not.
 155     $currentlyExcluded = [
 156       'contribute/repeat',
 157       'member/summary',
 158       'event/summary',
 159       'case/summary',
 160       'case/timespent',
 161       'case/demographics',
 162       'contact/log',
 163       'contribute/bookkeeping',
 164       'grant/detail',
 165       'event/incomesummary',
 166       'case/detail',
 167       'Mailing/bounce',
 168       'Mailing/summary',
 169       'grant/statistics',
 170       'logging/contact/detail',
 171       'logging/contact/summary',
 172     ];
 173     foreach ($allTemplates as $index => $template) {
 174       $reportID = $template[0];
 175       if (in_array($reportID, $currentlyExcluded) || stristr($reportID, 'has existing issues')) {
 176         unset($allTemplates[$index]);
 177       }
 178     }
 179     return $allTemplates;
 180   }
 181
 182   /**
 183    * @param \CRM_Core_DAO $entity
 184    * @param array $clauses
 185    */
 186   public function hookSelectWhere($entity, &$clauses) {
 187     // Restrict access to cases by type
 188     if ($entity == 'Contact') {
 189       $clauses['contact_type'][] = " =  'Organization' ";
 190     }
 191   }
 192
 193   /**
 194    * Test getrows on contact summary report.
 195    */
 196   public function testReportTemplateGetRowsContactSummary() {
 197     $description = "Retrieve rows from a report template (optionally providing the instance_id).";
 198     $result = $this->callApiSuccess('report_template', 'getrows', array(
 199       'report_id' => 'contact/summary',
 200       'options' => array('metadata' => array('labels', 'title')),
 201     ), __FUNCTION__, __FILE__, $description, 'Getrows');
 202     $this->assertEquals('Contact Name', $result['metadata']['labels']['civicrm_contact_sort_name']);
 203
 204     //the second part of this test has been commented out because it relied on the db being reset to
 205     // it's base state
 206     //wasn't able to get that to work consistently
 207     // however, when the db is in the base state the tests do pass
 208     // and because the test covers 'all' contacts we can't create our own & assume the others don't exist
 209     /*
 210     $this->assertEquals(2, $result['count']);
 211     $this->assertEquals('Default Organization', $result[0]['civicrm_contact_sort_name']);
 212     $this->assertEquals('Second Domain', $result[1]['civicrm_contact_sort_name']);
 213     $this->assertEquals('15 Main St', $result[1]['civicrm_address_street_address']);
 214      */
 215   }
 216
 217   /**
 218    * Test getrows on Mailing Opened report.
 219    */
 220   public function testReportTemplateGetRowsMailingUniqueOpened() {
 221     $description = "Retrieve rows from a mailing opened report template.";
 222     $op = new PHPUnit_Extensions_Database_Operation_Insert();
 223     $op->execute($this->_dbconn,
 224       $this->createFlatXMLDataSet(
 225         dirname(__FILE__) . '/../../CRM/Mailing/BAO/queryDataset.xml'
 226       )
 227     );
 228
 229     // Check total rows without distinct
 230     global $_REQUEST;
 231     $_REQUEST['distinct'] = 0;
 232     $result = $this->callAPIAndDocument('report_template', 'getrows', array(
 233       'report_id' => 'Mailing/opened',
 234       'options' => array('metadata' => array('labels', 'title')),
 235     ), __FUNCTION__, __FILE__, $description, 'Getrows');
 236     $this->assertEquals(14, $result['count']);
 237
 238     // Check total rows with distinct
 239     $_REQUEST['distinct'] = 1;
 240     $result = $this->callAPIAndDocument('report_template', 'getrows', array(
 241       'report_id' => 'Mailing/opened',
 242       'options' => array('metadata' => array('labels', 'title')),
 243     ), __FUNCTION__, __FILE__, $description, 'Getrows');
 244     $this->assertEquals(5, $result['count']);
 245
 246     // Check total rows with distinct by passing NULL value to distinct parameter
 247     $_REQUEST['distinct'] = NULL;
 248     $result = $this->callAPIAndDocument('report_template', 'getrows', array(
 249       'report_id' => 'Mailing/opened',
 250       'options' => array('metadata' => array('labels', 'title')),
 251     ), __FUNCTION__, __FILE__, $description, 'Getrows');
 252     $this->assertEquals(5, $result['count']);
 253   }
 254
 255   /**
 256    * Test api to get rows from reports.
 257    *
 258    * @dataProvider getReportTemplates
 259    *
 260    * @param $reportID
 261    *
 262    * @throws \PHPUnit_Framework_IncompleteTestError
 263    */
 264   public function testReportTemplateGetRowsAllReports($reportID) {
 265     //$reportID = 'logging/contact/summary';
 266     if (stristr($reportID, 'has existing issues')) {
 267       $this->markTestIncomplete($reportID);
 268     }
 269     if (substr($reportID, 0, '7') === 'logging') {
 270       Civi::settings()->set('logging', 1);
 271     }
 272
 273     $this->callAPISuccess('report_template', 'getrows', array(
 274       'report_id' => $reportID,
 275     ));
 276     if (substr($reportID, 0, '7') === 'logging') {
 277       Civi::settings()->set('logging', 0);
 278     }
 279   }
 280
 281   /**
 282    * Test logging report when a custom data table has a table removed by hook.
 283    *
 284    * Here we are checking that no fatal is triggered.
 285    */
 286   public function testLoggingReportWithHookRemovalOfCustomDataTable() {
 287     Civi::settings()->set('logging', 1);
 288     $group1 = $this->customGroupCreate();
 289     $group2 = $this->customGroupCreate(['name' => 'second_one', 'title' => 'second one', 'table_name' => 'civicrm_value_second_one']);
 290     $this->customFieldCreate(array('custom_group_id' => $group1['id'], 'label' => 'field one'));
 291     $this->customFieldCreate(array('custom_group_id' => $group2['id'], 'label' => 'field two'));
 292     $this->hookClass->setHook('civicrm_alterLogTables', array($this, 'alterLogTablesRemoveCustom'));
 293
 294     $this->callAPISuccess('report_template', 'getrows', array(
 295       'report_id' => 'logging/contact/summary',
 296     ));
 297     Civi::settings()->set('logging', 0);
 298     $this->customGroupDelete($group1['id']);
 299     $this->customGroupDelete($group2['id']);
 300   }
 301
 302   /**
 303    * Remove one log table from the logging spec.
 304    *
 305    * @param array $logTableSpec
 306    */
 307   public function alterLogTablesRemoveCustom(&$logTableSpec) {
 308     unset($logTableSpec['civicrm_value_second_one']);
 309   }
 310
 311   /**
 312    * Test api to get rows from reports with ACLs enabled.
 313    *
 314    * Checking for lack of fatal error at the moment.
 315    *
 316    * @dataProvider getReportTemplates
 317    *
 318    * @param $reportID
 319    *
 320    * @throws \PHPUnit_Framework_IncompleteTestError
 321    */
 322   public function testReportTemplateGetRowsAllReportsACL($reportID) {
 323     if (stristr($reportID, 'has existing issues')) {
 324       $this->markTestIncomplete($reportID);
 325     }
 326     $this->hookClass->setHook('civicrm_aclWhereClause', array($this, 'aclWhereHookNoResults'));
 327     $this->callAPISuccess('report_template', 'getrows', array(
 328       'report_id' => $reportID,
 329     ));
 330   }
 331
 332   /**
 333    * Test get statistics.
 334    *
 335    * @dataProvider getReportTemplates
 336    *
 337    * @param $reportID
 338    *
 339    * @throws \PHPUnit_Framework_IncompleteTestError
 340    */
 341   public function testReportTemplateGetStatisticsAllReports($reportID) {
 342     if (stristr($reportID, 'has existing issues')) {
 343       $this->markTestIncomplete($reportID);
 344     }
 345     if (in_array($reportID, array('contribute/softcredit', 'contribute/bookkeeping'))) {
 346       $this->markTestIncomplete($reportID . " has non enotices when calling statistics fn");
 347     }
 348     $description = "Get Statistics from a report (note there isn't much data to get in the test DB).";
 349     $result = $this->callAPIAndDocument('report_template', 'getstatistics', array(
 350       'report_id' => $reportID,
 351     ), __FUNCTION__, __FILE__, $description, 'Getstatistics', 'getstatistics');
 352   }
 353
 354   /**
 355    * Data provider function for getting all templates.
 356    *
 357    * Note that the function needs to
 358    * be static so cannot use $this->callAPISuccess
 359    */
 360   public static function getReportTemplates() {
 361     $reportsToSkip = array(
 362       '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',
 363       'contribute/history' => 'Declaration of CRM_Report_Form_Contribute_History::buildRows() should be compatible with CRM_Report_Form::buildRows($sql, &$rows)',
 364       'activitySummary' => 'We use temp tables for the main query generation and name are dynamic. These names are not available in stats() when called directly.',
 365     );
 366
 367     $reports = civicrm_api3('report_template', 'get', array('return' => 'value', 'options' => array('limit' => 500)));
 368     foreach ($reports['values'] as $report) {
 369       if (empty($reportsToSkip[$report['value']])) {
 370         $reportTemplates[] = array($report['value']);
 371       }
 372       else {
 373         $reportTemplates[] = array($report['value'] . " has existing issues :  " . $reportsToSkip[$report['value']]);
 374       }
 375     }
 376
 377     return $reportTemplates;
 378   }
 379
 380   /**
 381    * Get contribution templates that work with basic filter tests.
 382    *
 383    * These templates require minimal data config.
 384    */
 385   public static function getContributionReportTemplates() {
 386     return array(array('contribute/summary'), array('contribute/detail'), array('contribute/repeat'), array('topDonor' => 'contribute/topDonor'));
 387   }
 388
 389   /**
 390    * Get contribution templates that work with basic filter tests.
 391    *
 392    * These templates require minimal data config.
 393    */
 394   public static function getMembershipReportTemplates() {
 395     return array(array('member/detail'));
 396   }
 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(array('contact_id' => $inInd, 'receive_date' => '2014-03-01'));
 417     $this->contributionCreate(array('contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL));
 418     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 419       'report_id' => 'contribute/lybunt',
 420       'yid_value' => 2015,
 421       'yid_op' => 'calendar',
 422       'options' => array('metadata' => array('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 = array('administer CiviCRM');
 432     $inInd = $this->individualCreate();
 433     $outInd = $this->individualCreate();
 434     $this->contributionCreate(array('contact_id' => $inInd, 'receive_date' => '2014-03-01'));
 435     $this->contributionCreate(array('contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL));
 436     $this->hookClass->setHook('civicrm_aclWhereClause', array($this, 'aclWhereHookNoResults'));
 437     $params = array(
 438       'report_id' => 'contribute/lybunt',
 439       'yid_value' => 2015,
 440       'yid_op' => 'calendar',
 441       'options' => array('metadata' => array('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(array('contact_id' => $inInd, 'receive_date' => '2014-10-01'));
 458     $this->contributionCreate(array('contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL));
 459     $this->callAPISuccess('Setting', 'create', array('fiscalYearStart' => array('M' => 7, 'd' => 1)));
 460     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 461       'report_id' => 'contribute/lybunt',
 462       'yid_value' => 2015,
 463       'yid_op' => 'fiscal',
 464       'options' => array('metadata' => array('sql')),
 465       'order_bys' => array(
 466         array(
 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
 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(array('contact_id' => $inInd, 'receive_date' => '2014-10-01'));
 497     $this->contributionCreate(array('contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL));
 498     $this->callAPISuccess('Setting', 'create', array('fiscalYearStart' => array('M' => 7, 'd' => 1)));
 499     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 500       'report_id' => 'contribute/lybunt',
 501       'yid_value' => 2015,
 502       'yid_op' => 'fiscal',
 503       'options' => array('metadata' => array('sql')),
 504       'fields' => array('first_name'),
 505       'order_bys' => array(
 506         array(
 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   public function testContributionSummaryWithSmartGroupFilter($template) {
 525     $groupID = $this->setUpPopulatedSmartGroup();
 526     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 527       'report_id' => $template,
 528       'gid_value' => $groupID,
 529       'gid_op' => 'in',
 530       'options' => array('metadata' => array('sql')),
 531     ));
 532     $this->assertNumberOfContactsInResult(3, $rows, $template);
 533     if ($template === 'contribute/summary') {
 534       $this->assertEquals(3, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 535     }
 536   }
 537
 538   /**
 539    * Test the group filter works on the contribution summary.
 540    *
 541    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
 542    */
 543   public function testContributionSummaryWithNotINSmartGroupFilter($template) {
 544     $groupID = $this->setUpPopulatedSmartGroup();
 545     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 546       'report_id' => 'contribute/summary',
 547       'gid_value' => $groupID,
 548       'gid_op' => 'notin',
 549       'options' => array('metadata' => array('sql')),
 550     ));
 551     $this->assertEquals(2, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 552   }
 553
 554   /**
 555    * Test the group filter works on the contribution summary.
 556    */
 557   public function testContributionDetailSoftCredits() {
 558     $contactID = $this->individualCreate();
 559     $contactID2 = $this->individualCreate();
 560     $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
 561     $template = 'contribute/detail';
 562     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 563       'report_id' => $template,
 564       'contribution_or_soft_value' => 'contributions_only',
 565       'fields' => ['soft_credits' => 1, 'contribution_or_soft' => 1, 'sort_name' => 1],
 566       'options' => array('metadata' => array('sql')),
 567     ));
 568     $this->assertEquals(
 569       "<a href='/index.php?q=civicrm/contact/view&amp;reset=1&amp;cid=" . $contactID2 . "'>Anderson, Anthony</a> $ 5.00",
 570       $rows['values'][0]['civicrm_contribution_soft_credits']
 571     );
 572   }
 573
 574   /**
 575    * Test the amount column is populated on soft credit details.
 576    */
 577   public function testContributionDetailSoftCreditsOnly() {
 578     $contactID = $this->individualCreate();
 579     $contactID2 = $this->individualCreate();
 580     $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
 581     $template = 'contribute/detail';
 582     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 583       'report_id' => $template,
 584       'contribution_or_soft_value' => 'soft_credits_only',
 585       'fields' => [
 586         'sort_name' => '1',
 587         'email' => '1',
 588         'financial_type_id' => '1',
 589         'receive_date' => '1',
 590         'total_amount' => '1',
 591       ],
 592       'options' => array('metadata' => ['sql', 'labels']),
 593     ));
 594     foreach (array_keys($rows['metadata']['labels']) as $header) {
 595       $this->assertTrue(!empty($rows['values'][0][$header]));
 596     }
 597   }
 598
 599   /**
 600    * Test the group filter works on the various reports.
 601    *
 602    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
 603    *
 604    * @param string $template
 605    *   Report template unique identifier.
 606    */
 607   public function testReportsWithNonSmartGroupFilter($template) {
 608     $groupID = $this->setUpPopulatedGroup();
 609     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 610       'report_id' => $template,
 611       'gid_value' => array($groupID),
 612       'gid_op' => 'in',
 613       'options' => array('metadata' => array('sql')),
 614     ));
 615     $this->assertNumberOfContactsInResult(1, $rows, $template);
 616   }
 617
 618   /**
 619    * Assert the included results match the expected.
 620    *
 621    * There may or may not be a group by in play so the assertion varies a little.
 622    *
 623    * @param int $numberExpected
 624    * @param array $rows
 625    *   Rows returned from the report.
 626    * @param string $template
 627    */
 628   protected function assertNumberOfContactsInResult($numberExpected, $rows, $template) {
 629     if (isset($rows['values'][0]['civicrm_contribution_total_amount_count'])) {
 630       $this->assertEquals($numberExpected, $rows['values'][0]['civicrm_contribution_total_amount_count'], 'wrong row count in ' . $template);
 631     }
 632     else {
 633       $this->assertEquals($numberExpected, count($rows['values']), 'wrong row count in ' . $template);
 634     }
 635   }
 636
 637   /**
 638    * Test the group filter works on the contribution summary when 2 groups are involved.
 639    */
 640   public function testContributionSummaryWithTwoGroups() {
 641     $groupID = $this->setUpPopulatedGroup();
 642     $groupID2 = $this->setUpPopulatedSmartGroup();
 643     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 644       'report_id' => 'contribute/summary',
 645       'gid_value' => array($groupID, $groupID2),
 646       'gid_op' => 'in',
 647       'options' => array('metadata' => array('sql')),
 648     ));
 649     $this->assertEquals(4, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 650   }
 651
 652   /**
 653    * CRM-20640: Test the group filter works on the contribution summary when a single contact in 2 groups.
 654    */
 655   public function testContributionSummaryWithSingleContactsInTwoGroups() {
 656     list($groupID1, $individualID) = $this->setUpPopulatedGroup(TRUE);
 657     // create second group and add the individual to it.
 658     $groupID2 = $this->groupCreate(array('name' => uniqid(), 'title' => uniqid()));
 659     $this->callAPISuccess('GroupContact', 'create', array(
 660       'group_id' => $groupID2,
 661       'contact_id' => $individualID,
 662       'status' => 'Added',
 663     ));
 664
 665     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 666       'report_id' => 'contribute/summary',
 667       'gid_value' => array($groupID1, $groupID2),
 668       'gid_op' => 'in',
 669       'options' => array('metadata' => array('sql')),
 670     ));
 671     $this->assertEquals(1, $rows['count']);
 672   }
 673
 674   /**
 675    * Test the group filter works on the contribution summary when 2 groups are involved.
 676    */
 677   public function testContributionSummaryWithTwoGroupsWithIntersection() {
 678     $groups = $this->setUpIntersectingGroups();
 679
 680     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 681       'report_id' => 'contribute/summary',
 682       'gid_value' => $groups,
 683       'gid_op' => 'in',
 684       'options' => array('metadata' => array('sql')),
 685     ));
 686     $this->assertEquals(7, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 687   }
 688
 689   /**
 690    * Set up a smart group for testing.
 691    *
 692    * The smart group includes all Households by filter. In addition an individual
 693    * is created and hard-added and an individual is created that is not added.
 694    *
 695    * One household is hard-added as well as being in the filter.
 696    *
 697    * This gives us a range of scenarios for testing contacts are included only once
 698    * whenever they are hard-added or in the criteria.
 699    *
 700    * @return int
 701    */
 702   public function setUpPopulatedSmartGroup() {
 703     $household1ID = $this->householdCreate();
 704     $individual1ID = $this->individualCreate();
 705     $householdID = $this->householdCreate();
 706     $individualID = $this->individualCreate();
 707     $individualIDRemoved = $this->individualCreate();
 708     $groupID = $this->smartGroupCreate(array(), array('name' => uniqid(), 'title' => uniqid()));
 709     $this->callAPISuccess('GroupContact', 'create', array(
 710       'group_id' => $groupID,
 711       'contact_id' => $individualIDRemoved,
 712       'status' => 'Removed',
 713     ));
 714     $this->callAPISuccess('GroupContact', 'create', array(
 715       'group_id' => $groupID,
 716       'contact_id' => $individualID,
 717       'status' => 'Added',
 718     ));
 719     $this->callAPISuccess('GroupContact', 'create', array(
 720       'group_id' => $groupID,
 721       'contact_id' => $householdID,
 722       'status' => 'Added',
 723     ));
 724     foreach (array($household1ID, $individual1ID, $householdID, $individualID, $individualIDRemoved) as $contactID) {
 725       $this->contributionCreate(array('contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => ''));
 726       $this->contactMembershipCreate(array('contact_id' => $contactID));
 727     }
 728
 729     // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
 730     CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID);
 731     return $groupID;
 732   }
 733
 734   /**
 735    * Set up a static group for testing.
 736    *
 737    * An individual is created and hard-added and an individual is created that is not added.
 738    *
 739    * This gives us a range of scenarios for testing contacts are included only once
 740    * whenever they are hard-added or in the criteria.
 741    *
 742    * @param bool $returnAddedContact
 743    *
 744    * @return int
 745    */
 746   public function setUpPopulatedGroup($returnAddedContact = FALSE) {
 747     $individual1ID = $this->individualCreate();
 748     $individualID = $this->individualCreate();
 749     $individualIDRemoved = $this->individualCreate();
 750     $groupID = $this->groupCreate(array('name' => uniqid(), 'title' => uniqid()));
 751     $this->callAPISuccess('GroupContact', 'create', array(
 752       'group_id' => $groupID,
 753       'contact_id' => $individualIDRemoved,
 754       'status' => 'Removed',
 755     ));
 756     $this->callAPISuccess('GroupContact', 'create', array(
 757       'group_id' => $groupID,
 758       'contact_id' => $individualID,
 759       'status' => 'Added',
 760     ));
 761
 762     foreach (array($individual1ID, $individualID, $individualIDRemoved) as $contactID) {
 763       $this->contributionCreate(array('contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => ''));
 764       $this->contactMembershipCreate(array('contact_id' => $contactID));
 765     }
 766
 767     // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
 768     CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID);
 769
 770     if ($returnAddedContact) {
 771       return array($groupID, $individualID);
 772     }
 773
 774     return $groupID;
 775   }
 776
 777   /**
 778    * @return array
 779    */
 780   public function setUpIntersectingGroups() {
 781     $groupID = $this->setUpPopulatedGroup();
 782     $groupID2 = $this->setUpPopulatedSmartGroup();
 783     $addedToBothIndividualID = $this->individualCreate();
 784     $removedFromBothIndividualID = $this->individualCreate();
 785     $addedToSmartGroupRemovedFromOtherIndividualID = $this->individualCreate();
 786     $removedFromSmartGroupAddedToOtherIndividualID = $this->individualCreate();
 787     $this->callAPISuccess('GroupContact', 'create', array(
 788       'group_id' => $groupID,
 789       'contact_id' => $addedToBothIndividualID,
 790       'status' => 'Added',
 791     ));
 792     $this->callAPISuccess('GroupContact', 'create', array(
 793       'group_id' => $groupID2,
 794       'contact_id' => $addedToBothIndividualID,
 795       'status' => 'Added',
 796     ));
 797     $this->callAPISuccess('GroupContact', 'create', array(
 798       'group_id' => $groupID,
 799       'contact_id' => $removedFromBothIndividualID,
 800       'status' => 'Removed',
 801     ));
 802     $this->callAPISuccess('GroupContact', 'create', array(
 803       'group_id' => $groupID2,
 804       'contact_id' => $removedFromBothIndividualID,
 805       'status' => 'Removed',
 806     ));
 807     $this->callAPISuccess('GroupContact', 'create', array(
 808       'group_id' => $groupID2,
 809       'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
 810       'status' => 'Added',
 811     ));
 812     $this->callAPISuccess('GroupContact', 'create', array(
 813       'group_id' => $groupID,
 814       'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
 815       'status' => 'Removed',
 816     ));
 817     $this->callAPISuccess('GroupContact', 'create', array(
 818       'group_id' => $groupID,
 819       'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
 820       'status' => 'Added',
 821     ));
 822     $this->callAPISuccess('GroupContact', 'create', array(
 823       'group_id' => $groupID2,
 824       'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
 825       'status' => 'Removed',
 826     ));
 827
 828     foreach (array(
 829                $addedToBothIndividualID,
 830                $removedFromBothIndividualID,
 831                $addedToSmartGroupRemovedFromOtherIndividualID,
 832                $removedFromSmartGroupAddedToOtherIndividualID,
 833              ) as $contactID) {
 834       $this->contributionCreate(array(
 835         'contact_id' => $contactID,
 836         'invoice_id' => '',
 837         'trxn_id' => '',
 838       ));
 839     }
 840     return array($groupID, $groupID2);
 841   }
 842
 843   /**
 844    * Test Deferred Revenue Report.
 845    */
 846   public function testDeferredRevenueReport() {
 847     $indv1 = $this->individualCreate();
 848     $indv2 = $this->individualCreate();
 849     $params = array(
 850       'contribution_invoice_settings' => array(
 851         'deferred_revenue_enabled' => '1',
 852       ),
 853     );
 854     $this->callAPISuccess('Setting', 'create', $params);
 855     $this->contributionCreate(
 856       array(
 857         'contact_id' => $indv1,
 858         'receive_date' => '2016-10-01',
 859         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+3 month')),
 860         'financial_type_id' => 2,
 861       )
 862     );
 863     $this->contributionCreate(
 864       array(
 865         'contact_id' => $indv1,
 866         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+22 month')),
 867         'financial_type_id' => 4,
 868         'trxn_id' => NULL,
 869         'invoice_id' => NULL,
 870       )
 871     );
 872     $this->contributionCreate(
 873       array(
 874         'contact_id' => $indv2,
 875         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+1 month')),
 876         'financial_type_id' => 4,
 877         'trxn_id' => NULL,
 878         'invoice_id' => NULL,
 879       )
 880     );
 881     $this->contributionCreate(
 882       array(
 883         'contact_id' => $indv2,
 884         'receive_date' => '2016-03-01',
 885         'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+4 month')),
 886         'financial_type_id' => 2,
 887         'trxn_id' => NULL,
 888         'invoice_id' => NULL,
 889       )
 890     );
 891     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 892       'report_id' => 'contribute/deferredrevenue',
 893     ));
 894     $this->assertEquals(2, $rows['count'], "Report failed to get row count");
 895     $count = array(2, 1);
 896     foreach ($rows['values'] as $row) {
 897       $this->assertEquals(array_pop($count), count($row['rows']), "Report failed to get row count");
 898     }
 899   }
 900
 901   /**
 902    * Test the group filter works on the various reports.
 903    *
 904    * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests
 905    *
 906    * @param string $template
 907    *   Report template unique identifier.
 908    */
 909   public function testReportsWithNoTInSmartGroupFilter($template) {
 910     $groupID = $this->setUpPopulatedGroup();
 911     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 912       'report_id' => $template,
 913       'gid_value' => array($groupID),
 914       'gid_op' => 'notin',
 915       'options' => array('metadata' => array('sql')),
 916     ));
 917     $this->assertNumberOfContactsInResult(2, $rows, $template);
 918   }
 919
 920   /**
 921    * Test activity summary report - requiring all current fields to be output.
 922    */
 923   public function testActivitySummary() {
 924     $this->createContactsWithActivities();
 925     $fields = [
 926       'contact_source' => '1',
 927       'contact_assignee' => '1',
 928       'contact_target' => '1',
 929       'contact_source_email' => '1',
 930       'contact_assignee_email' => '1',
 931       'contact_target_email' => '1',
 932       'contact_source_phone' => '1',
 933       'contact_assignee_phone' => '1',
 934       'contact_target_phone' => '1',
 935       'activity_type_id' => '1',
 936       'activity_subject' => '1',
 937       'activity_date_time' => '1',
 938       'status_id' => '1',
 939       'duration' => '1',
 940       'location' => '1',
 941       'details' => '1',
 942       'priority_id' => '1',
 943       'result' => '1',
 944       'engagement_level' => '1',
 945       'address_name' => '1',
 946       'street_address' => '1',
 947       'supplemental_address_1' => '1',
 948       'supplemental_address_2' => '1',
 949       'supplemental_address_3' => '1',
 950       'street_number' => '1',
 951       'street_name' => '1',
 952       'street_unit' => '1',
 953       'city' => '1',
 954       'postal_code' => '1',
 955       'postal_code_suffix' => '1',
 956       'country_id' => '1',
 957       'state_province_id' => '1',
 958       'county_id' => '1',
 959     ];
 960     $params = [
 961       'fields' => $fields,
 962       'current_user_op' => 'eq',
 963       'current_user_value' => '0',
 964       'include_case_activities_op' => 'eq',
 965       'include_case_activities_value' => 0,
 966       'order_bys' => [
 967         1 => ['column' => 'activity_date_time', 'order' => 'ASC'],
 968         2 => ['column' => 'activity_type_id', 'order' => 'ASC'],
 969       ],
 970     ];
 971
 972     $params['report_id'] = 'Activity';
 973
 974     $rows = $this->callAPISuccess('report_template', 'getrows', $params)['values'];
 975     $expected = [
 976       'civicrm_contact_contact_source' => 'Łąchowski-Roberts, Anthony',
 977       '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>',
 978       '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>',
 979       'civicrm_contact_contact_source_id' => $this->contactIDs[2],
 980       'civicrm_contact_contact_assignee_id' => $this->contactIDs[1],
 981       'civicrm_contact_contact_target_id' => $this->contactIDs[0] . ';' . $this->contactIDs[1],
 982       'civicrm_email_contact_source_email' => 'anthony_anderson@civicrm.org',
 983       'civicrm_email_contact_assignee_email' => 'anthony_anderson@civicrm.org',
 984       'civicrm_email_contact_target_email' => 'techo@spying.com;anthony_anderson@civicrm.org',
 985       'civicrm_phone_contact_source_phone' => NULL,
 986       'civicrm_phone_contact_assignee_phone' => NULL,
 987       'civicrm_phone_contact_target_phone' => NULL,
 988       'civicrm_activity_id' => '1',
 989       'civicrm_activity_source_record_id' => NULL,
 990       'civicrm_activity_activity_type_id' => 'Meeting',
 991       'civicrm_activity_activity_subject' => 'Very secret meeting',
 992       'civicrm_activity_activity_date_time' => date('Y-m-d 23:59:58', strtotime('now')),
 993       'civicrm_activity_status_id' => 'Scheduled',
 994       'civicrm_activity_duration' => '120',
 995       'civicrm_activity_location' => 'Pennsylvania',
 996       'civicrm_activity_details' => 'a test activity',
 997       'civicrm_activity_priority_id' => 'Normal',
 998       'civicrm_address_address_name' => NULL,
 999       'civicrm_address_street_address' => NULL,
1000       'civicrm_address_supplemental_address_1' => NULL,
1001       'civicrm_address_supplemental_address_2' => NULL,
1002       'civicrm_address_supplemental_address_3' => NULL,
1003       'civicrm_address_street_number' => NULL,
1004       'civicrm_address_street_name' => NULL,
1005       'civicrm_address_street_unit' => NULL,
1006       'civicrm_address_city' => NULL,
1007       'civicrm_address_postal_code' => NULL,
1008       'civicrm_address_postal_code_suffix' => NULL,
1009       'civicrm_address_country_id' => NULL,
1010       'civicrm_address_state_province_id' => NULL,
1011       'civicrm_address_county_id' => NULL,
1012       'civicrm_contact_contact_source_link' => '/index.php?q=civicrm/contact/view&amp;reset=1&amp;cid=' . $this->contactIDs[2],
1013       'civicrm_contact_contact_source_hover' => 'View Contact Summary for this Contact',
1014       'civicrm_activity_activity_type_id_hover' => 'View Activity Record',
1015     ];
1016     $row = $rows[0];
1017     // This link is not relative - skip for now
1018     unset($row['civicrm_activity_activity_type_id_link']);
1019     if ($row['civicrm_email_contact_target_email'] === 'anthony_anderson@civicrm.org;techo@spying.com') {
1020       // order is unpredictable
1021       $expected['civicrm_email_contact_target_email'] = 'anthony_anderson@civicrm.org;techo@spying.com';
1022     }
1023
1024     $this->assertEquals($expected, $row);
1025   }
1026
1027   /**
1028    * Set up some activity data..... use some chars that challenge our utf handling.
1029    */
1030   public function createContactsWithActivities() {
1031     $this->contactIDs[] = $this->individualCreate(['last_name' => 'Brzęczysław', 'email' => 'techo@spying.com']);
1032     $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']);
1033     $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']);
1034
1035     $this->callAPISuccess('Activity', 'create', [
1036       'subject' => 'Very secret meeting',
1037       'activity_date_time' => date('Y-m-d 23:59:58', strtotime('now')),
1038       'duration' => 120,
1039       'location' => 'Pennsylvania',
1040       'details' => 'a test activity',
1041       'status_id' => 1,
1042       'activity_type_id' => 'Meeting',
1043       'source_contact_id' => $this->contactIDs[2],
1044       'target_contact_id' => array($this->contactIDs[0], $this->contactIDs[1]),
1045       'assignee_contact_id' => $this->contactIDs[1],
1046     ]);
1047   }
1048
1049   /**
1050    * Test the group filter works on the contribution summary.
1051    */
1052   public function testContributionDetailTotalHeader() {
1053     $contactID = $this->individualCreate();
1054     $contactID2 = $this->individualCreate();
1055     $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]);
1056     $template = 'contribute/detail';
1057     $rows = $this->callAPISuccess('report_template', 'getrows', array(
1058       'report_id' => $template,
1059       'contribution_or_soft_value' => 'contributions_only',
1060       'fields' => [
1061         'sort_name' => '1',
1062         'age' => '1',
1063         'email' => '1',
1064         'phone' => '1',
1065         'financial_type_id' => '1',
1066         'receive_date' => '1',
1067         'total_amount' => '1',
1068        ],
1069       'order_bys' => [['column' => 'sort_name', 'order' => 'ASC', 'section' => '1']],
1070       'options' => array('metadata' => array('sql')),
1071     ));
1072   }
1073
1074 }