tests/phpunit/api/v3/ReportTemplateTest.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2017                                |
   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   protected $_apiversion = 3;
  37
  38   /**
  39    * Our group reports use an alter so transaction cleanup won't work.
  40    *
  41    * @throws \Exception
  42    */
  43   public function tearDown() {
  44     $this->quickCleanUpFinancialEntities();
  45     $this->quickCleanup(array('civicrm_group', 'civicrm_saved_search', 'civicrm_group_contact'));
  46     parent::tearDown();
  47   }
  48
  49   public function testReportTemplate() {
  50     $result = $this->callAPISuccess('ReportTemplate', 'create', array(
  51       'label' => 'Example Form',
  52       'description' => 'Longish description of the example form',
  53       'class_name' => 'CRM_Report_Form_Examplez',
  54       'report_url' => 'example/path',
  55       'component' => 'CiviCase',
  56     ));
  57     $this->assertAPISuccess($result);
  58     $this->assertEquals(1, $result['count']);
  59     $entityId = $result['id'];
  60     $this->assertTrue(is_numeric($entityId));
  61     $this->assertEquals(7, $result['values'][$entityId]['component_id']);
  62     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  63       WHERE name = "CRM_Report_Form_Examplez"
  64       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  65     $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value
  66       WHERE name = "CRM_Report_Form_Examplez"');
  67
  68     // change component to null
  69     $result = $this->callAPISuccess('ReportTemplate', 'create', array(
  70       'id' => $entityId,
  71       'component' => '',
  72     ));
  73     $this->assertAPISuccess($result);
  74     $this->assertEquals(1, $result['count']);
  75     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  76       WHERE name = "CRM_Report_Form_Examplez"
  77       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  78     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  79       WHERE name = "CRM_Report_Form_Examplez"
  80       AND component_id IS NULL');
  81
  82     // deactivate
  83     $result = $this->callAPISuccess('ReportTemplate', 'create', array(
  84       'id' => $entityId,
  85       'is_active' => 0,
  86     ));
  87     $this->assertAPISuccess($result);
  88     $this->assertEquals(1, $result['count']);
  89     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
  90       WHERE name = "CRM_Report_Form_Examplez"
  91       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
  92     $this->assertDBQuery(0, 'SELECT is_active FROM civicrm_option_value
  93       WHERE name = "CRM_Report_Form_Examplez"');
  94
  95     // activate
  96     $result = $this->callAPISuccess('ReportTemplate', 'create', array(
  97       'id' => $entityId,
  98       'is_active' => 1,
  99     ));
 100     $this->assertAPISuccess($result);
 101     $this->assertEquals(1, $result['count']);
 102     $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value
 103       WHERE name = "CRM_Report_Form_Examplez"
 104       AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") ');
 105     $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value
 106       WHERE name = "CRM_Report_Form_Examplez"');
 107
 108     $result = $this->callAPISuccess('ReportTemplate', 'delete', array(
 109       'id' => $entityId,
 110     ));
 111     $this->assertAPISuccess($result);
 112     $this->assertEquals(1, $result['count']);
 113     $this->assertDBQuery(0, 'SELECT count(*) FROM civicrm_option_value
 114       WHERE name = "CRM_Report_Form_Examplez"
 115       ');
 116   }
 117
 118   /**
 119    * Test getrows on contact summary report.
 120    */
 121   public function testReportTemplateGetRowsContactSummary() {
 122     $description = "Retrieve rows from a report template (optionally providing the instance_id).";
 123     $result = $this->callAPIAndDocument('report_template', 'getrows', array(
 124       'report_id' => 'contact/summary',
 125       'options' => array('metadata' => array('labels', 'title')),
 126     ), __FUNCTION__, __FILE__, $description, 'Getrows', 'getrows');
 127     $this->assertEquals('Contact Name', $result['metadata']['labels']['civicrm_contact_sort_name']);
 128
 129     //the second part of this test has been commented out because it relied on the db being reset to
 130     // it's base state
 131     //wasn't able to get that to work consistently
 132     // however, when the db is in the base state the tests do pass
 133     // and because the test covers 'all' contacts we can't create our own & assume the others don't exist
 134     /*
 135     $this->assertEquals(2, $result['count']);
 136     $this->assertEquals('Default Organization', $result[0]['civicrm_contact_sort_name']);
 137     $this->assertEquals('Second Domain', $result[1]['civicrm_contact_sort_name']);
 138     $this->assertEquals('15 Main St', $result[1]['civicrm_address_street_address']);
 139      */
 140   }
 141
 142   /**
 143    * Tet api to get rows from reports.
 144    *
 145    * @dataProvider getReportTemplates
 146    *
 147    * @param $reportID
 148    *
 149    * @throws \PHPUnit_Framework_IncompleteTestError
 150    */
 151   public function testReportTemplateGetRowsAllReports($reportID) {
 152     if (stristr($reportID, 'has existing issues')) {
 153       $this->markTestIncomplete($reportID);
 154     }
 155     $this->callAPISuccess('report_template', 'getrows', array(
 156       'report_id' => $reportID,
 157     ));
 158   }
 159
 160   /**
 161    * Test get statistics.
 162    *
 163    * @dataProvider getReportTemplates
 164    *
 165    * @param $reportID
 166    *
 167    * @throws \PHPUnit_Framework_IncompleteTestError
 168    */
 169   public function testReportTemplateGetStatisticsAllReports($reportID) {
 170     if (stristr($reportID, 'has existing issues')) {
 171       $this->markTestIncomplete($reportID);
 172     }
 173     if (in_array($reportID, array('contribute/softcredit', 'contribute/bookkeeping'))) {
 174       $this->markTestIncomplete($reportID . " has non enotices when calling statistics fn");
 175     }
 176     $description = "Get Statistics from a report (note there isn't much data to get in the test DB).";
 177     $result = $this->callAPIAndDocument('report_template', 'getstatistics', array(
 178       'report_id' => $reportID,
 179     ), __FUNCTION__, __FILE__, $description, 'Getstatistics', 'getstatistics');
 180   }
 181
 182   /**
 183    * Data provider function for getting all templates.
 184    *
 185    * Note that the function needs to
 186    * be static so cannot use $this->callAPISuccess
 187    */
 188   public static function getReportTemplates() {
 189     $reportsToSkip = array(
 190       'activity' => 'does not respect function signature on from clause',
 191       'contribute/topDonor' => 'construction of query in postProcess makes inaccessible ',
 192       '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',
 193       'logging/contact/summary' => '(likely to be test related) probably logging off Undefined index: Form/Contact/LoggingSummary.php(231): PHP',
 194       'logging/contribute/summary' => '(likely to be test related) probably logging off DB Error: no such table',
 195       'contribute/history' => 'Declaration of CRM_Report_Form_Contribute_History::buildRows() should be compatible with CRM_Report_Form::buildRows($sql, &$rows)',
 196       'activitySummary' => 'We use temp tables for the main query generation and name are dynamic. These names are not available in stats() when called directly.',
 197     );
 198
 199     $reports = civicrm_api3('report_template', 'get', array('return' => 'value', 'options' => array('limit' => 500)));
 200     foreach ($reports['values'] as $report) {
 201       if (empty($reportsToSkip[$report['value']])) {
 202         $reportTemplates[] = array($report['value']);
 203       }
 204       else {
 205         $reportTemplates[] = array($report['value'] . " has existing issues :  " . $reportsToSkip[$report['value']]);
 206       }
 207     }
 208
 209     return $reportTemplates;
 210   }
 211
 212   /**
 213    * Get contribution templates that work with basic filter tests.
 214    *
 215    * These templates require minimal data config.
 216    */
 217   public static function getContributionReportTemplates() {
 218     return array(array('contribute/summary'), array('contribute/detail'), array('contribute/repeat'), array('contribute/topDonor'));
 219   }
 220
 221   /**
 222    * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
 223    */
 224   public function testLybuntReportWithData() {
 225     $inInd = $this->individualCreate();
 226     $outInd = $this->individualCreate();
 227     $this->contributionCreate(array('contact_id' => $inInd, 'receive_date' => '2014-03-01'));
 228     $this->contributionCreate(array('contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL));
 229     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 230       'report_id' => 'contribute/lybunt',
 231       'yid_value' => 2015,
 232       'yid_op' => 'calendar',
 233       'options' => array('metadata' => array('sql')),
 234     ));
 235     $this->assertEquals(1, $rows['count'], "Report failed - the sql used to generate the results was " . print_r($rows['metadata']['sql'], TRUE));
 236   }
 237
 238   /**
 239    * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
 240    */
 241   public function testLybuntReportWithFYData() {
 242     $inInd = $this->individualCreate();
 243     $outInd = $this->individualCreate();
 244     $this->contributionCreate(array('contact_id' => $inInd, 'receive_date' => '2014-10-01'));
 245     $this->contributionCreate(array('contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL));
 246     $this->callAPISuccess('Setting', 'create', array('fiscalYearStart' => array('M' => 7, 'd' => 1)));
 247     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 248       'report_id' => 'contribute/lybunt',
 249       'yid_value' => 2015,
 250       'yid_op' => 'fiscal',
 251       'options' => array('metadata' => array('sql')),
 252       'order_bys' => array(
 253         array(
 254           'column' => 'first_name',
 255           'order' => 'ASC',
 256         ),
 257       ),
 258     ));
 259
 260     $this->assertEquals(2, $rows['count'], "Report failed - the sql used to generate the results was " . print_r($rows['metadata']['sql'], TRUE));
 261   }
 262
 263   /**
 264    * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year.
 265    */
 266   public function testLybuntReportWithFYDataOrderByLastYearAmount() {
 267     $inInd = $this->individualCreate();
 268     $outInd = $this->individualCreate();
 269     $this->contributionCreate(array('contact_id' => $inInd, 'receive_date' => '2014-10-01'));
 270     $this->contributionCreate(array('contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL));
 271     $this->callAPISuccess('Setting', 'create', array('fiscalYearStart' => array('M' => 7, 'd' => 1)));
 272     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 273       'report_id' => 'contribute/lybunt',
 274       'yid_value' => 2015,
 275       'yid_op' => 'fiscal',
 276       'options' => array('metadata' => array('sql')),
 277       'fields' => array('first_name'),
 278       'order_bys' => array(
 279         array(
 280           'column' => 'last_year_total_amount',
 281           'order' => 'ASC',
 282         ),
 283       ),
 284     ));
 285
 286     $this->assertEquals(2, $rows['count'], "Report failed - the sql used to generate the results was " . print_r($rows['metadata']['sql'], TRUE));
 287   }
 288
 289   /**
 290    * Test the group filter works on the contribution summary (with a smart group).
 291    */
 292   public function testContributionSummaryWithSmartGroupFilter() {
 293     $groupID = $this->setUpPopulatedSmartGroup();
 294     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 295       'report_id' => 'contribute/summary',
 296       'gid_value' => $groupID,
 297       'gid_op' => 'in',
 298       'options' => array('metadata' => array('sql')),
 299     ));
 300     $this->assertEquals(3, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 301
 302   }
 303
 304   /**
 305    * Test the group filter works on the contribution summary (with a smart group).
 306    */
 307   public function testContributionSummaryWithNotINSmartGroupFilter() {
 308     $groupID = $this->setUpPopulatedSmartGroup();
 309     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 310       'report_id' => 'contribute/summary',
 311       'gid_value' => $groupID,
 312       'gid_op' => 'notin',
 313       'options' => array('metadata' => array('sql')),
 314     ));
 315     $this->assertEquals(2, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 316
 317   }
 318
 319   /**
 320    * Test the group filter works on the contribution summary (with a smart group).
 321    *
 322    * @dataProvider getContributionReportTemplates
 323    *
 324    * @param string $template
 325    *   Report template unique identifier.
 326    */
 327   public function testContributionSummaryWithNonSmartGroupFilter($template) {
 328     $groupID = $this->setUpPopulatedGroup();
 329     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 330       'report_id' => $template,
 331       'gid_value' => array($groupID),
 332       'gid_op' => 'in',
 333       'options' => array('metadata' => array('sql')),
 334     ));
 335     $this->assertNumberOfContactsInResult(1, $rows, $template);
 336   }
 337
 338   /**
 339    * Assert the included results match the expected.
 340    *
 341    * There may or may not be a group by in play so the assertion varies a little.
 342    *
 343    * @param int $numberExpected
 344    * @param array $rows
 345    *   Rows returned from the report.
 346    * @param string $template
 347    */
 348   protected function assertNumberOfContactsInResult($numberExpected, $rows, $template) {
 349     if (isset($rows['values'][0]['civicrm_contribution_total_amount_count'])) {
 350       $this->assertEquals($numberExpected, $rows['values'][0]['civicrm_contribution_total_amount_count'], 'wrong row count in ' . $template);
 351     }
 352     else {
 353       $this->assertEquals($numberExpected, count($rows['values']), 'wrong row count in ' . $template);
 354     }
 355   }
 356
 357   /**
 358    * Test the group filter works on the contribution summary when 2 groups are involved.
 359    */
 360   public function testContributionSummaryWithTwoGroups() {
 361     $groupID = $this->setUpPopulatedGroup();
 362     $groupID2 = $this->setUpPopulatedSmartGroup();
 363     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 364       'report_id' => 'contribute/summary',
 365       'gid_value' => array($groupID, $groupID2),
 366       'gid_op' => 'in',
 367       'options' => array('metadata' => array('sql')),
 368     ));
 369     $this->assertEquals(4, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 370   }
 371
 372   /**
 373    * Test the group filter works on the contribution summary when 2 groups are involved.
 374    */
 375   public function testContributionSummaryWithTwoGroupsWithIntersection() {
 376     $groups = $this->setUpIntersectingGroups();
 377
 378     $rows = $this->callAPISuccess('report_template', 'getrows', array(
 379       'report_id' => 'contribute/summary',
 380       'gid_value' => $groups,
 381       'gid_op' => 'in',
 382       'options' => array('metadata' => array('sql')),
 383     ));
 384     $this->assertEquals(7, $rows['values'][0]['civicrm_contribution_total_amount_count']);
 385   }
 386
 387   /**
 388    * Set up a smart group for testing.
 389    *
 390    * The smart group includes all Households by filter. In addition an individual
 391    * is created and hard-added and an individual is created that is not added.
 392    *
 393    * One household is hard-added as well as being in the filter.
 394    *
 395    * This gives us a range of scenarios for testing contacts are included only once
 396    * whenever they are hard-added or in the criteria.
 397    *
 398    * @return int
 399    */
 400   public function setUpPopulatedSmartGroup() {
 401     $household1ID = $this->householdCreate();
 402     $individual1ID = $this->individualCreate();
 403     $householdID = $this->householdCreate();
 404     $individualID = $this->individualCreate();
 405     $individualIDRemoved = $this->individualCreate();
 406     $groupID = $this->smartGroupCreate(array(), array('name' => uniqid(), 'title' => uniqid()));
 407     $this->callAPISuccess('GroupContact', 'create', array(
 408       'group_id' => $groupID,
 409       'contact_id' => $individualIDRemoved,
 410       'status' => 'Removed',
 411     ));
 412     $this->callAPISuccess('GroupContact', 'create', array(
 413       'group_id' => $groupID,
 414       'contact_id' => $individualID,
 415       'status' => 'Added',
 416     ));
 417     $this->callAPISuccess('GroupContact', 'create', array(
 418       'group_id' => $groupID,
 419       'contact_id' => $householdID,
 420       'status' => 'Added',
 421     ));
 422     foreach (array($household1ID, $individual1ID, $householdID, $individualID, $individualIDRemoved) as $contactID) {
 423       $this->contributionCreate(array('contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => ''));
 424     }
 425
 426     // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
 427     CRM_Contact_BAO_GroupContactCache::remove($groupID, FALSE);
 428     return $groupID;
 429   }
 430
 431   /**
 432    * Set up a smart group for testing.
 433    *
 434    * The smart group includes all Households by filter. In addition an individual
 435    * is created and hard-added and an individual is created that is not added.
 436    *
 437    * One household is hard-added as well as being in the filter.
 438    *
 439    * This gives us a range of scenarios for testing contacts are included only once
 440    * whenever they are hard-added or in the criteria.
 441    *
 442    * @return int
 443    */
 444   public function setUpPopulatedGroup() {
 445     $individual1ID = $this->individualCreate();
 446     $individualID = $this->individualCreate();
 447     $individualIDRemoved = $this->individualCreate();
 448     $groupID = $this->groupCreate(array('name' => uniqid(), 'title' => uniqid()));
 449     $this->callAPISuccess('GroupContact', 'create', array(
 450       'group_id' => $groupID,
 451       'contact_id' => $individualIDRemoved,
 452       'status' => 'Removed',
 453     ));
 454     $this->callAPISuccess('GroupContact', 'create', array(
 455       'group_id' => $groupID,
 456       'contact_id' => $individualID,
 457       'status' => 'Added',
 458     ));
 459
 460     foreach (array($individual1ID, $individualID, $individualIDRemoved) as $contactID) {
 461       $this->contributionCreate(array('contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => ''));
 462     }
 463
 464     // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
 465     CRM_Contact_BAO_GroupContactCache::remove($groupID, FALSE);
 466     return $groupID;
 467   }
 468
 469   /**
 470    * @return array
 471    */
 472   public function setUpIntersectingGroups() {
 473     $groupID = $this->setUpPopulatedGroup();
 474     $groupID2 = $this->setUpPopulatedSmartGroup();
 475     $addedToBothIndividualID = $this->individualCreate();
 476     $removedFromBothIndividualID = $this->individualCreate();
 477     $addedToSmartGroupRemovedFromOtherIndividualID = $this->individualCreate();
 478     $removedFromSmartGroupAddedToOtherIndividualID = $this->individualCreate();
 479     $this->callAPISuccess('GroupContact', 'create', array(
 480       'group_id' => $groupID,
 481       'contact_id' => $addedToBothIndividualID,
 482       'status' => 'Added',
 483     ));
 484     $this->callAPISuccess('GroupContact', 'create', array(
 485       'group_id' => $groupID2,
 486       'contact_id' => $addedToBothIndividualID,
 487       'status' => 'Added',
 488     ));
 489     $this->callAPISuccess('GroupContact', 'create', array(
 490       'group_id' => $groupID,
 491       'contact_id' => $removedFromBothIndividualID,
 492       'status' => 'Removed',
 493     ));
 494     $this->callAPISuccess('GroupContact', 'create', array(
 495       'group_id' => $groupID2,
 496       'contact_id' => $removedFromBothIndividualID,
 497       'status' => 'Removed',
 498     ));
 499     $this->callAPISuccess('GroupContact', 'create', array(
 500       'group_id' => $groupID2,
 501       'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
 502       'status' => 'Added',
 503     ));
 504     $this->callAPISuccess('GroupContact', 'create', array(
 505       'group_id' => $groupID,
 506       'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID,
 507       'status' => 'Removed',
 508     ));
 509     $this->callAPISuccess('GroupContact', 'create', array(
 510       'group_id' => $groupID,
 511       'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
 512       'status' => 'Added',
 513     ));
 514     $this->callAPISuccess('GroupContact', 'create', array(
 515       'group_id' => $groupID2,
 516       'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID,
 517       'status' => 'Removed',
 518     ));
 519
 520     foreach (array(
 521                $addedToBothIndividualID,
 522                $removedFromBothIndividualID,
 523                $addedToSmartGroupRemovedFromOtherIndividualID,
 524                $removedFromSmartGroupAddedToOtherIndividualID,
 525              ) as $contactID) {
 526       $this->contributionCreate(array(
 527         'contact_id' => $contactID,
 528         'invoice_id' => '',
 529         'trxn_id' => '',
 530       ));
 531     }
 532     return array($groupID, $groupID2);
 533   }
 534
 535 }