tests/phpunit/CRM/Contact/BAO/QueryTest.php

   1 <?php
   2
   3 /**
   4  *  Include dataProvider for tests
   5  * @group headless
   6  */
   7 class CRM_Contact_BAO_QueryTest extends CiviUnitTestCase {
   8   use CRMTraits_Financial_FinancialACLTrait;
   9   use CRMTraits_Financial_PriceSetTrait;
  10
  11   /**
  12    * @return CRM_Contact_BAO_QueryTestDataProvider
  13    */
  14   public function dataProvider() {
  15     return new CRM_Contact_BAO_QueryTestDataProvider();
  16   }
  17
  18   public function setUp() {
  19     parent::setUp();
  20   }
  21
  22   public function tearDown() {
  23     $this->quickCleanUpFinancialEntities();
  24     $tablesToTruncate = array(
  25       'civicrm_group_contact',
  26       'civicrm_group',
  27       'civicrm_saved_search',
  28       'civicrm_entity_tag',
  29       'civicrm_tag',
  30       'civicrm_contact',
  31       'civicrm_address',
  32     );
  33     $this->quickCleanup($tablesToTruncate);
  34   }
  35
  36   /**
  37    *  Test CRM_Contact_BAO_Query::searchQuery().
  38    *
  39    * @dataProvider dataProvider
  40    *
  41    * @param $fv
  42    * @param $count
  43    * @param $ids
  44    * @param $full
  45    */
  46   public function testSearch($fv, $count, $ids, $full) {
  47     $op = new PHPUnit_Extensions_Database_Operation_Insert();
  48     $op->execute($this->_dbconn,
  49       $this->createFlatXMLDataSet(
  50         dirname(__FILE__) . '/queryDataset.xml'
  51       )
  52     );
  53
  54     $params = CRM_Contact_BAO_Query::convertFormValues($fv);
  55     $obj = new CRM_Contact_BAO_Query($params);
  56
  57     // let's set useGroupBy=true since we are listing contacts here who might belong to
  58     // more than one group / tag / notes etc.
  59     $obj->_useGroupBy = TRUE;
  60
  61     $dao = $obj->searchQuery();
  62
  63     $contacts = array();
  64     while ($dao->fetch()) {
  65       $contacts[] = $dao->contact_id;
  66     }
  67
  68     sort($contacts, SORT_NUMERIC);
  69
  70     $this->assertEquals($ids, $contacts);
  71   }
  72
  73   /**
  74    * Check that we get a successful result querying for home address.
  75    * CRM-14263 search builder failure with search profile & address in criteria
  76    */
  77   public function testSearchProfileHomeCityCRM14263() {
  78     $contactID = $this->individualCreate();
  79     CRM_Core_Config::singleton()->defaultSearchProfileID = 1;
  80     $this->callAPISuccess('address', 'create', array(
  81         'contact_id' => $contactID,
  82         'city' => 'Cool City',
  83         'location_type_id' => 1,
  84       ));
  85     $params = array(
  86       0 => array(
  87         0 => 'city-1',
  88         1 => '=',
  89         2 => 'Cool City',
  90         3 => 1,
  91         4 => 0,
  92       ),
  93     );
  94     $returnProperties = array(
  95       'contact_type' => 1,
  96       'contact_sub_type' => 1,
  97       'sort_name' => 1,
  98     );
  99
 100     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 101     try {
 102       $resultDAO = $queryObj->searchQuery();
 103       $this->assertTrue($resultDAO->fetch());
 104     }
 105     catch (PEAR_Exception $e) {
 106       $err = $e->getCause();
 107       $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo);
 108
 109     }
 110   }
 111
 112   /**
 113    * Check that we get a successful result querying for home address.
 114    * CRM-14263 search builder failure with search profile & address in criteria
 115    */
 116   public function testSearchProfileHomeCityNoResultsCRM14263() {
 117     $contactID = $this->individualCreate();
 118     CRM_Core_Config::singleton()->defaultSearchProfileID = 1;
 119     $this->callAPISuccess('address', 'create', array(
 120         'contact_id' => $contactID,
 121         'city' => 'Cool City',
 122         'location_type_id' => 1,
 123       ));
 124     $params = array(
 125       0 => array(
 126         0 => 'city-1',
 127         1 => '=',
 128         2 => 'Dumb City',
 129         3 => 1,
 130         4 => 0,
 131       ),
 132     );
 133     $returnProperties = array(
 134       'contact_type' => 1,
 135       'contact_sub_type' => 1,
 136       'sort_name' => 1,
 137     );
 138
 139     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 140     try {
 141       $resultDAO = $queryObj->searchQuery();
 142       $this->assertFalse($resultDAO->fetch());
 143     }
 144     catch (PEAR_Exception $e) {
 145       $err = $e->getCause();
 146       $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo);
 147
 148     }
 149   }
 150
 151   /**
 152    * Test searchPrimaryDetailsOnly setting.
 153    */
 154   public function testSearchPrimaryLocTypes() {
 155     $contactID = $this->individualCreate();
 156     $params = array(
 157       'contact_id' => $contactID,
 158       'email' => 'primary@example.com',
 159       'is_primary' => 1,
 160     );
 161     $this->callAPISuccess('email', 'create', $params);
 162
 163     unset($params['is_primary']);
 164     $params['email'] = 'secondary@team.com';
 165     $this->callAPISuccess('email', 'create', $params);
 166
 167     foreach (array(0, 1) as $searchPrimary) {
 168       Civi::settings()->set('searchPrimaryDetailsOnly', $searchPrimary);
 169
 170       $params = array(
 171         0 => array(
 172           0 => 'email',
 173           1 => 'LIKE',
 174           2 => 'sEcondary@example.com',
 175           3 => 0,
 176           4 => 1,
 177         ),
 178       );
 179       $returnProperties = array(
 180         'contact_type' => 1,
 181         'contact_sub_type' => 1,
 182         'sort_name' => 1,
 183       );
 184
 185       $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 186       $resultDAO = $queryObj->searchQuery();
 187
 188       if ($searchPrimary) {
 189         $this->assertEquals($resultDAO->N, 0);
 190       }
 191       else {
 192         //Assert secondary email gets included in search results.
 193         while ($resultDAO->fetch()) {
 194           $this->assertEquals('secondary@example.com', $resultDAO->email);
 195         }
 196       }
 197
 198       // API should always return primary email.
 199       $result = $this->callAPISuccess('Contact', 'get', array('contact_id' => $contactID));
 200       $this->assertEquals('primary@example.com', $result['values'][$contactID]['email']);
 201     }
 202   }
 203
 204   /**
 205    *  Test created to prove failure of search on state when location
 206    *  display name is different form location name (issue 607)
 207    */
 208   public function testSearchOtherLocationUpperLower() {
 209
 210     $params = [
 211       0 => [
 212         0 => 'state_province-4',
 213         1 => 'IS NOT EMPTY',
 214         2 => '',
 215         3 => 1,
 216         4 => 0,
 217       ],
 218     ];
 219     $returnProperties = [
 220       'contact_type' => 1,
 221       'contact_sub_type' => 1,
 222       'sort_name' => 1,
 223       'location' => [
 224         'other' => [
 225           'location_type' => 4,
 226           'state_province' => 1,
 227         ],
 228       ],
 229     ];
 230
 231     // update with the api does not work because it updates both the name and the
 232     // the display_name. Plain SQL however does the job
 233     CRM_Core_DAO::executeQuery('update civicrm_location_type set name=%2 where id=%1',
 234       [
 235         1 => [4, 'Integer'],
 236         2 => ['other', 'String'],
 237       ]);
 238
 239     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 240
 241     $resultDAO = $queryObj->searchQuery();
 242     $resultDAO->fetch();
 243   }
 244
 245
 246   /**
 247    * CRM-14263 search builder failure with search profile & address in criteria.
 248    *
 249    * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has
 250    * so few tests detecting any change seems good here :-)
 251    *
 252    * @dataProvider getSearchProfileData
 253    *
 254    * @param array $params
 255    */
 256   public function testSearchProfilePrimaryCityCRM14263($params, $selectClause, $whereClause) {
 257     $contactID = $this->individualCreate();
 258     CRM_Core_Config::singleton()->defaultSearchProfileID = 1;
 259     $this->callAPISuccess('address', 'create', array(
 260         'contact_id' => $contactID,
 261         'city' => 'Cool CITY',
 262         'street_address' => 'Long STREET',
 263         'location_type_id' => 1,
 264       ));
 265     $returnProperties = array(
 266       'contact_type' => 1,
 267       'contact_sub_type' => 1,
 268       'sort_name' => 1,
 269     );
 270     $expectedSQL = "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, civicrm_address.id as address_id, " . $selectClause . "  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) WHERE  (  ( " . $whereClause . " )  )  AND (contact_a.is_deleted = 0)    ORDER BY `contact_a`.`sort_name` ASC, `contact_a`.`id` ";
 271     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 272     try {
 273       $this->assertEquals($expectedSQL, $queryObj->getSearchSQL());
 274       list($select, $from, $where, $having) = $queryObj->query();
 275       $dao = CRM_Core_DAO::executeQuery("$select $from $where $having");
 276       $dao->fetch();
 277       $this->assertEquals('Anderson, Anthony', $dao->sort_name);
 278     }
 279     catch (PEAR_Exception $e) {
 280       $err = $e->getCause();
 281       $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo);
 282
 283     }
 284   }
 285
 286   /**
 287    * Get data sets to test for search.
 288    */
 289   public function getSearchProfileData() {
 290     return [
 291       [
 292         [['city', '=', 'Cool City', 1, 0]], "civicrm_address.city as `city`", "civicrm_address.city = 'Cool City'",
 293       ],
 294       [
 295         // Note that in the query 'long street' is lower cased. We eventually want to change that & not mess with the vars - it turns out
 296         // it doesn't work on some charsets. However, the the lcasing affects more vars & we are looking to stagger removal of lcasing 'in case'
 297         // (although we have been removing without blowback since 2017)
 298         [['street_address', '=', 'Long Street', 1, 0]], "civicrm_address.street_address as `street_address`", "civicrm_address.street_address LIKE '%Long Street%'",
 299       ],
 300     ];
 301   }
 302
 303   /**
 304    * Test set up to test calling the query object per GroupContactCache BAO usage.
 305    *
 306    * CRM-17254 ensure that if only the contact_id is required other fields should
 307    * not be appended.
 308    */
 309   public function testGroupContactCacheAddSearch() {
 310     $returnProperties = array('contact_id');
 311     $params = array(array('group', 'IN', array(1), 0, 0));
 312
 313     $query = new CRM_Contact_BAO_Query(
 314       $params, $returnProperties,
 315       NULL, TRUE, FALSE, 1,
 316       TRUE,
 317       TRUE, FALSE
 318     );
 319
 320     list($select) = $query->query(FALSE);
 321     $this->assertEquals('SELECT contact_a.id as contact_id', $select);
 322   }
 323
 324   /**
 325    * Test smart groups with non-numeric don't fail on range queries.
 326    *
 327    * CRM-14720
 328    */
 329   public function testNumericPostal() {
 330     // Precaution as hitting some inconsistent set up running in isolation vs in the suite.
 331     CRM_Core_DAO::executeQuery('UPDATE civicrm_address SET postal_code = NULL');
 332
 333     $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
 334     $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
 335     $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
 336     $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
 337     $this->individualCreate(array('api.address.create' => array('street_address' => 'just a street', 'location_type_id' => 'Main')));
 338     $this->individualCreate(array('api.address.create' => array('postal_code' => '12345678444455555555555555555555555555555555551314151617181920', 'location_type_id' => 'Main')));
 339
 340     $params = array(array('postal_code_low', '=', 5, 0, 0));
 341     CRM_Contact_BAO_Query::convertFormValues($params);
 342
 343     $query = new CRM_Contact_BAO_Query(
 344       $params, array('contact_id'),
 345       NULL, TRUE, FALSE, 1,
 346       TRUE,
 347       TRUE, FALSE
 348     );
 349
 350     $sql = $query->query(FALSE);
 351     $result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
 352     $this->assertEquals(2, $result->N);
 353
 354     // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
 355     // results in mysql warnings & hence fatal errors.
 356     /// I was unable to get mysql warnings to activate in the context of the unit tests - but
 357     // felt this code still provided a useful bit of coverage as it runs the various queries to load
 358     // the group & could generate invalid sql if a bug were introduced.
 359     $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1);
 360     $group = CRM_Contact_BAO_Group::createSmartGroup($groupParams);
 361     CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
 362   }
 363
 364   /**
 365    * Test searches are case insensitive.
 366    */
 367   public function testCaseInsensitive() {
 368     $orgID = $this->organizationCreate(array('organization_name' => 'BOb'));
 369     $params = array(
 370       'display_name' => 'Minnie Mouse',
 371       'first_name' => 'Minnie',
 372       'last_name' => 'Mouse',
 373       'employer_id' => $orgID,
 374       'contact_type' => 'Individual',
 375       'nick_name' => 'Mins',
 376     );
 377     $this->callAPISuccess('Contact', 'create', $params);
 378     unset($params['contact_type']);
 379     foreach ($params as $key => $value) {
 380       if ($key == 'employer_id') {
 381         $searchParams = array(array('current_employer', '=', 'bob', 0, 1));
 382       }
 383       else {
 384         $searchParams = array(array($key, '=', strtolower($value), 0, 1));
 385       }
 386       $query = new CRM_Contact_BAO_Query($searchParams);
 387       $result = $query->apiQuery($searchParams);
 388       $this->assertEquals(1, count($result[0]), 'search for ' . $key);
 389       $contact = reset($result[0]);
 390       $this->assertEquals('Minnie Mouse', $contact['display_name']);
 391       $this->assertEquals('BOb', $contact['current_employer']);
 392     }
 393   }
 394
 395   /**
 396    * Test smart groups with non-numeric don't fail on equal queries.
 397    *
 398    * CRM-14720
 399    */
 400   public function testNonNumericEqualsPostal() {
 401     $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
 402     $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
 403     $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
 404     $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
 405
 406     $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0));
 407     CRM_Contact_BAO_Query::convertFormValues($params);
 408
 409     $query = new CRM_Contact_BAO_Query(
 410       $params, array('contact_id'),
 411       NULL, TRUE, FALSE, 1,
 412       TRUE,
 413       TRUE, FALSE
 414     );
 415
 416     $sql = $query->query(FALSE);
 417     $this->assertEquals("WHERE  ( civicrm_address.postal_code = 'EH10 4RB-889' )  AND (contact_a.is_deleted = 0)", $sql[2]);
 418     $result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
 419     $this->assertEquals(1, $result->N);
 420
 421   }
 422
 423   public function testNonReciprocalRelationshipTargetGroupIsCorrectResults() {
 424     $contactID_a = $this->individualCreate();
 425     $contactID_b = $this->individualCreate();
 426     $this->callAPISuccess('Relationship', 'create', array(
 427       'contact_id_a' => $contactID_a,
 428       'contact_id_b' => $contactID_b,
 429       'relationship_type_id' => 1,
 430       'is_active' => 1,
 431     ));
 432     // Create a group and add contact A to it.
 433     $groupID = $this->groupCreate();
 434     $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added'));
 435
 436     // Add another (sans-relationship) contact to the group,
 437     $contactID_c = $this->individualCreate();
 438     $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added'));
 439
 440     $params = array(
 441       array(
 442         0 => 'relation_type_id',
 443         1 => 'IN',
 444         2 =>
 445         array(
 446           0 => '1_b_a',
 447         ),
 448         3 => 0,
 449         4 => 0,
 450       ),
 451       array(
 452         0 => 'relation_target_group',
 453         1 => 'IN',
 454         2 =>
 455         array(
 456           0 => $groupID,
 457         ),
 458         3 => 0,
 459         4 => 0,
 460       ),
 461     );
 462
 463     $query = new CRM_Contact_BAO_Query($params);
 464     $dao = $query->searchQuery();
 465     $this->assertEquals('1', $dao->N, "Search query returns exactly 1 result?");
 466     $this->assertTrue($dao->fetch(), "Search query returns success?");
 467     $this->assertEquals($contactID_b, $dao->contact_id, "Search query returns parent of contact A?");
 468   }
 469
 470   public function testReciprocalRelationshipTargetGroupIsCorrectResults() {
 471     $contactID_a = $this->individualCreate();
 472     $contactID_b = $this->individualCreate();
 473     $this->callAPISuccess('Relationship', 'create', array(
 474       'contact_id_a' => $contactID_a,
 475       'contact_id_b' => $contactID_b,
 476       'relationship_type_id' => 2,
 477       'is_active' => 1,
 478     ));
 479     // Create a group and add contact A to it.
 480     $groupID = $this->groupCreate();
 481     $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added'));
 482
 483     // Add another (sans-relationship) contact to the group,
 484     $contactID_c = $this->individualCreate();
 485     $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added'));
 486
 487     $params = array(
 488       array(
 489         0 => 'relation_type_id',
 490         1 => 'IN',
 491         2 =>
 492         array(
 493           0 => '2_a_b',
 494         ),
 495         3 => 0,
 496         4 => 0,
 497       ),
 498       array(
 499         0 => 'relation_target_group',
 500         1 => 'IN',
 501         2 =>
 502         array(
 503           0 => $groupID,
 504         ),
 505         3 => 0,
 506         4 => 0,
 507       ),
 508     );
 509
 510     $query = new CRM_Contact_BAO_Query($params);
 511     $dao = $query->searchQuery();
 512     $this->assertEquals('1', $dao->N, "Search query returns exactly 1 result?");
 513     $this->assertTrue($dao->fetch(), "Search query returns success?");
 514     $this->assertEquals($contactID_b, $dao->contact_id, "Search query returns spouse of contact A?");
 515   }
 516
 517   public function testReciprocalRelationshipTargetGroupUsesTempTable() {
 518     $groupID = $this->groupCreate();
 519     $params = array(
 520       array(
 521         0 => 'relation_type_id',
 522         1 => 'IN',
 523         2 =>
 524         array(
 525           0 => '2_a_b',
 526         ),
 527         3 => 0,
 528         4 => 0,
 529       ),
 530       array(
 531         0 => 'relation_target_group',
 532         1 => 'IN',
 533         2 =>
 534         array(
 535           0 => $groupID,
 536         ),
 537         3 => 0,
 538         4 => 0,
 539       ),
 540     );
 541     $sql = CRM_Contact_BAO_Query::getQuery($params);
 542     $this->assertContains('INNER JOIN civicrm_rel_temp_', $sql, "Query appears to use temporary table of compiled relationships?", TRUE);
 543   }
 544
 545   public function testRelationshipPermissionClause() {
 546     $params = [['relation_type_id', 'IN', ['1_b_a'], 0, 0], ['relation_permission', 'IN', [2], 0, 0]];
 547     $sql = CRM_Contact_BAO_Query::getQuery($params);
 548     $this->assertContains('(civicrm_relationship.is_permission_a_b IN (2))', $sql);
 549   }
 550
 551   /**
 552    * Test Relationship Clause
 553    */
 554   public function testRelationshipClause() {
 555     $today = date('Ymd');
 556     $from1 = " FROM civicrm_contact contact_a LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id ) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )";
 557     $from2 = " FROM civicrm_contact contact_a LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id ) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )";
 558     $where1 = "WHERE  ( (
 559 civicrm_relationship.is_active = 1 AND
 560 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
 561 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
 562 ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8) )  AND (contact_a.is_deleted = 0)";
 563     $where2 = "WHERE  ( (
 564 civicrm_relationship.is_active = 1 AND
 565 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
 566 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
 567 ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8,10) )  AND (contact_a.is_deleted = 0)";
 568     // Test Traditional single select format
 569     $params1 = array(array('relation_type_id', '=', '8_a_b', 0, 0));
 570     $query1 = new CRM_Contact_BAO_Query(
 571       $params1, array('contact_id'),
 572       NULL, TRUE, FALSE, 1,
 573       TRUE,
 574       TRUE, FALSE
 575     );
 576     $sql1 = $query1->query(FALSE);
 577     $this->assertEquals($from1, $sql1[1]);
 578     $this->assertEquals($where1, $sql1[2]);
 579     // Test single relationship type selected in multiple select.
 580     $params2 = array(array('relation_type_id', 'IN', array('8_a_b'), 0, 0));
 581     $query2 = new CRM_Contact_BAO_Query(
 582       $params2, array('contact_id'),
 583       NULL, TRUE, FALSE, 1,
 584       TRUE,
 585       TRUE, FALSE
 586     );
 587     $sql2 = $query2->query(FALSE);
 588     $this->assertEquals($from1, $sql2[1]);
 589     $this->assertEquals($where1, $sql2[2]);
 590     // Test multiple relationship types selected.
 591     $params3 = array(array('relation_type_id', 'IN', array('8_a_b', '10_a_b'), 0, 0));
 592     $query3 = new CRM_Contact_BAO_Query(
 593       $params3, array('contact_id'),
 594       NULL, TRUE, FALSE, 1,
 595       TRUE,
 596       TRUE, FALSE
 597     );
 598     $sql3 = $query3->query(FALSE);
 599     $this->assertEquals($from1, $sql3[1]);
 600     $this->assertEquals($where2, $sql3[2]);
 601     // Test Multiple Relationship type selected where one doesn't actually exist.
 602     $params4 = array(array('relation_type_id', 'IN', array('8_a_b', '10_a_b', '14_a_b'), 0, 0));
 603     $query4 = new CRM_Contact_BAO_Query(
 604       $params4, array('contact_id'),
 605       NULL, TRUE, FALSE, 1,
 606       TRUE,
 607       TRUE, FALSE
 608     );
 609     $sql4 = $query4->query(FALSE);
 610     $this->assertEquals($from1, $sql4[1]);
 611     $this->assertEquals($where2, $sql4[2]);
 612
 613     // Test Multiple b to a Relationship type  .
 614     $params5 = array(array('relation_type_id', 'IN', array('8_b_a', '10_b_a', '14_b_a'), 0, 0));
 615     $query5 = new CRM_Contact_BAO_Query(
 616       $params5, array('contact_id'),
 617       NULL, TRUE, FALSE, 1,
 618       TRUE,
 619       TRUE, FALSE
 620     );
 621     $sql5 = $query5->query(FALSE);
 622     $this->assertEquals($from2, $sql5[1]);
 623     $this->assertEquals($where2, $sql5[2]);
 624   }
 625
 626   /**
 627    * Test the group contact clause does not contain an OR.
 628    *
 629    * The search should return 3 contacts - 2 households in the smart group of
 630    * Contact Type = Household and one Individual hard-added to it. The
 631    * Household that meets both criteria should be returned once.
 632    */
 633   public function testGroupClause() {
 634     $this->householdCreate();
 635     $householdID = $this->householdCreate();
 636     $individualID = $this->individualCreate();
 637     $groupID = $this->smartGroupCreate();
 638     $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added'));
 639     $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added'));
 640
 641     // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
 642     CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID);
 643
 644     $sql = CRM_Contact_BAO_Query::getQuery(
 645       array(array('group', 'IN', array($groupID), 0, 0)),
 646       array('contact_id')
 647     );
 648
 649     $dao = CRM_Core_DAO::executeQuery($sql);
 650     $this->assertEquals(3, $dao->N);
 651     $this->assertFalse(strstr($sql, ' OR '));
 652
 653     $sql = CRM_Contact_BAO_Query::getQuery(
 654       array(array('group', 'IN', array($groupID), 0, 0)),
 655       array('contact_id' => 1, 'group' => 1)
 656     );
 657
 658     $dao = CRM_Core_DAO::executeQuery($sql);
 659     $this->assertEquals(3, $dao->N);
 660     $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or');
 661     while ($dao->fetch()) {
 662       $this->assertTrue(($dao->groups == $groupID || $dao->groups == ',' . $groupID), $dao->groups . ' includes ' . $groupID);
 663     }
 664   }
 665
 666   /**
 667    * CRM-19562 ensure that only ids are used for contact_id searching.
 668    */
 669   public function testContactIDClause() {
 670     $params = array(
 671       array("mark_x_2", "=", 1, 0, 0),
 672       array("mark_x_foo@example.com", "=", 1, 0, 0),
 673     );
 674     $returnProperties = array(
 675       "sort_name" => 1,
 676       "email" => 1,
 677       "do_not_email" => 1,
 678       "is_deceased" => 1,
 679       "on_hold" => 1,
 680       "display_name" => 1,
 681       "preferred_mail_format" => 1,
 682     );
 683     $numberOfContacts = 2;
 684     $query = new CRM_Contact_BAO_Query($params, $returnProperties);
 685     try {
 686       $query->apiQuery($params, $returnProperties, NULL, NULL, 0, $numberOfContacts);
 687     }
 688     catch (Exception $e) {
 689       $this->assertEquals(
 690         "A fatal error was triggered: One of parameters  (value: foo@example.com) is not of the type Positive",
 691         $e->getMessage()
 692       );
 693       $this->assertTrue(TRUE);
 694       return;
 695     }
 696     $this->fail('Test failed for some reason which is not good');
 697   }
 698
 699   /**
 700    * Test the sorting on the contact ID query works.
 701    *
 702    * Checking for lack of fatal.
 703    *
 704    * @param string $sortOrder
 705    *   Param reflecting how sort is passed in.
 706    *   - 1_d is column 1 descending.
 707    *
 708    * @dataProvider getSortOptions
 709    */
 710   public function testContactIDQuery($sortOrder) {
 711     $selector = new CRM_Contact_Selector(NULL, ['radio_ts' => 'ts_all'], NULL, ['sort_name' => 1]);
 712     $selector->contactIDQuery([], $sortOrder);
 713   }
 714
 715   public function getSortOptions() {
 716     return [
 717       ['1_d'],
 718       ['2_d'],
 719       ['3_d'],
 720       ['4_d'],
 721       ['5_d'],
 722       ['6_d'],
 723     ];
 724   }
 725
 726   /**
 727    * Test the summary query does not add an acl clause when acls not enabled..
 728    */
 729   public function testGetSummaryQueryWithFinancialACLDisabled() {
 730     $this->createContributionsForSummaryQueryTests();
 731
 732     // Test the function directly
 733     $where = $from = NULL;
 734     $queryObject = new CRM_Contact_BAO_Query();
 735     $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where,
 736       $from);
 737     $this->assertEquals(NULL, $where);
 738     $this->assertEquals(NULL, $from);
 739
 740     // Test the function in action
 741     $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]);
 742     $summary = $queryObject->summaryContribution();
 743     $this->assertEquals([
 744       'total' => [
 745         'avg' => '$ 233.33',
 746         'amount' => '$ 1,400.00',
 747         'count' => 6,
 748         ],
 749       'cancel' => [
 750         'count' => 2,
 751         'amount' => '$ 100.00',
 752         'avg' => '$ 50.00',
 753       ],
 754     ], $summary);
 755   }
 756
 757   /**
 758    * Test the summary query accurately adds financial acl filters.
 759    */
 760   public function testGetSummaryQueryWithFinancialACLEnabled() {
 761     $where = $from = NULL;
 762     $this->createContributionsForSummaryQueryTests();
 763     $this->enableFinancialACLs();
 764     $this->createLoggedInUserWithFinancialACL();
 765
 766     // Test the function directly
 767     $queryObject = new CRM_Contact_BAO_Query();
 768     $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where,
 769       $from);
 770     $donationTypeID = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation');
 771     $this->assertEquals(
 772       " LEFT JOIN civicrm_line_item li
 773                   ON civicrm_contribution.id = li.contribution_id AND
 774                      li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN ({$donationTypeID}) ", $from);
 775
 776     // Test the function in action
 777     $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]);
 778     $summary = $queryObject->summaryContribution();
 779     $this->assertEquals([
 780       'total' => [
 781         'avg' => '$ 200.00',
 782         'amount' => '$ 400.00',
 783         'count' => 2,
 784       ],
 785       'cancel' => [
 786         'count' => 1,
 787         'amount' => '$ 50.00',
 788         'avg' => '$ 50.00',
 789       ],
 790     ], $summary);
 791     $this->disableFinancialACLs();
 792   }
 793
 794   /**
 795    * When we have a relative date in search criteria, check that convertFormValues() sets _low & _high date fields and returns other criteria.
 796    * CRM-21816 fix relative dates in search bug
 797    */
 798   public function testConvertFormValuesCRM21816() {
 799     $fv = array(
 800       "member_end_date_relative" => "starting_2.month", // next 60 days
 801       "member_end_date_low" => "20180101000000",
 802       "member_end_date_high" => "20180331235959",
 803       "membership_is_current_member" => "1",
 804       "member_is_primary" => "1",
 805     );
 806     $fv_orig = $fv;  // $fv is modified by convertFormValues()
 807     $params = CRM_Contact_BAO_Query::convertFormValues($fv);
 808
 809     // restructure for easier testing
 810     $modparams = array();
 811     foreach ($params as $p) {
 812       $modparams[$p[0]] = $p;
 813     }
 814
 815     // Check member_end_date_low is in params
 816     $this->assertTrue(is_array($modparams['member_end_date_low']));
 817     // ... fv and params should match
 818     $this->assertEquals($modparams['member_end_date_low'][2], $fv['member_end_date_low']);
 819     // ... fv & fv_orig should be different
 820     $this->assertNotEquals($fv['member_end_date_low'], $fv_orig['member_end_date_low']);
 821
 822     // same for member_end_date_high
 823     $this->assertTrue(is_array($modparams['member_end_date_high']));
 824     $this->assertEquals($modparams['member_end_date_high'][2], $fv['member_end_date_high']);
 825     $this->assertNotEquals($fv['member_end_date_high'], $fv_orig['member_end_date_high']);
 826
 827     // Check other fv values are in params
 828     $this->assertEquals($modparams['membership_is_current_member'][2], $fv_orig['membership_is_current_member']);
 829     $this->assertEquals($modparams['member_is_primary'][2], $fv_orig['member_is_primary']);
 830   }
 831
 832   /**
 833    * Create contributions to test summary calculations.
 834    *
 835    * financial type     | cancel_date        |total_amount| source    | line_item_financial_types  |number_line_items| line_amounts
 836    * Donation           |NULL                | 100.00     |SSF         | Donation                  | 1                | 100.00
 837    * Member Dues        |NULL                | 100.00     |SSF         | Member Dues               | 1                | 100.00
 838    * Donation           |NULL                | 300.00     |SSF         | Event Fee,Event Fee       | 2                | 200.00,100.00
 839    * Donation           |NULL                | 300.00     |SSF         | Event Fee,Donation        | 2                | 200.00,100.00
 840    * Donation           |NULL                | 300.00     |SSF         | Donation,Donation         | 2                | 200.00,100.00
 841    * Donation           |2019-02-13 00:00:00 | 50.00      |SSF         | Donation                  | 1                | 50.00
 842    * Member Dues        |2019-02-13 00:00:00 | 50.00      |SSF         | Member Dues               | 1                | 50.00
 843    */
 844   protected function createContributionsForSummaryQueryTests() {
 845     $contactID = $this->individualCreate();
 846     $this->contributionCreate(['contact_id' => $contactID]);
 847     $this->contributionCreate([
 848       'contact_id' => $contactID,
 849       'total_amount' => 100,
 850       'financial_type_id' => 'Member Dues',
 851     ]);
 852     $eventFeeType = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Event Fee');
 853     $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF']);
 854     $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [
 855       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
 856       $eventFeeType,
 857     ]);
 858     $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [
 859       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
 860       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
 861     ]);
 862     $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF', 'financial_type_id' => $eventFeeType], [
 863       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
 864       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
 865     ]);
 866     $this->contributionCreate([
 867       'contact_id' => $contactID,
 868       'total_amount' => 50,
 869       'contribution_status_id' => 'Cancelled',
 870       'cancel_date' => 'yesterday',
 871     ]);
 872     $this->contributionCreate([
 873       'contact_id' => $contactID,
 874       'total_amount' => 50,
 875       'contribution_status_id' => 'Cancelled',
 876       'cancel_date' => 'yesterday',
 877       'financial_type_id' => 'Member Dues',
 878     ]);
 879   }
 880
 881 }