X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=tests%2Fphpunit%2FCRM%2FCore%2FBAO%2FCustomQueryTest.php;h=32e62037e4c119793a907b3de3226ce752acd90d;hb=08ff628c6d369dc380b56d63a917f5e9f47adc7e;hp=649969971af823e0d3ea35855a80b2b321f8d143;hpb=8fb8e7a0be0f94af94d4dc310dc609e11ad0549f;p=civicrm-core.git diff --git a/tests/phpunit/CRM/Core/BAO/CustomQueryTest.php b/tests/phpunit/CRM/Core/BAO/CustomQueryTest.php index 649969971a..32e62037e4 100644 --- a/tests/phpunit/CRM/Core/BAO/CustomQueryTest.php +++ b/tests/phpunit/CRM/Core/BAO/CustomQueryTest.php @@ -5,6 +5,7 @@ * @group headless */ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { + use CRMTraits_Custom_CustomDataTrait; /** * Restore database to empty state. @@ -23,6 +24,8 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { /** * Test filtering by relative custom data dates. + * + * @throws \CRM_Core_Exception */ public function testSearchCustomDataDateRelative() { $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); @@ -49,8 +52,8 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { "civicrm_value_testsearchcus_1.date_field_2 BETWEEN '" . date('Y') . "0101000000' AND '" . date('Y') . "1231235959'", $queryObj->_where[0][0] ); - $this->assertEquals("date field is This calendar year (between January 1st, " . date('Y') . " 12:00 AM and December 31st, " . date('Y') . " 11:59 PM)", $queryObj->_qill[0][0]); - $queryObj = new CRM_Core_BAO_CustomQuery($params); + $this->assertEquals('date field is This calendar year (between January 1st, ' . date('Y') . " 12:00 AM and December 31st, " . date('Y') . " 11:59 PM)", $queryObj->_qill[0][0]); + $queryObj = new CRM_Contact_BAO_Query($params); $this->assertEquals([ 'id' => $dateCustomField['id'], 'label' => 'date field', @@ -81,12 +84,84 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { 'name' => 'custom_' . $dateCustomField['id'], 'type' => 4, 'where' => 'civicrm_value_testsearchcus_' . $ids['custom_group_id'] . '.date_field_' . $dateCustomField['id'], - ], $queryObj->getFields()[$dateCustomField['id']]); + 'import' => 1, + ], $queryObj->getFieldSpec('custom_' . $dateCustomField['id'])); + + } + + /** + * Test filtering by the renamed custom date fields. + * + * The conversion to date picker will result int these fields + * being renamed _high & _low and needing to return correctly. + * + * @throws \CRM_Core_Exception + */ + public function testSearchCustomDataDateHighLow() { + $this->createCustomGroupWithFieldOfType([], 'date'); + $dateCustomFieldName = $this->getCustomFieldName('date'); + // Assigning the relevant form value to be within a custom key is normally done in + // build field params. It would be better if it were all done in convertFormValues + // but for now we just imitate it. + $formValues = [ + $dateCustomFieldName . '_low' => '2014-06-06', + $dateCustomFieldName . '_high' => '2015-06-06', + ]; + + $params = CRM_Contact_BAO_Query::convertFormValues($formValues); + $queryObject = new CRM_Contact_BAO_Query($params); + $queryObject->query(); + $this->assertEquals( + '( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\' ) AND +( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' <= \'20150606235959\' )', + trim($queryObject->_where[0][0]) + ); + $this->assertEquals('Test Date - greater than or equal to "June 6th, 2014 12:00 AM" AND less than or equal to "June 6th, 2015 11:59 PM"', $queryObject->_qill[0][0]); + $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']); + $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables[$this->getCustomGroupTable()])); + } + + /** + * Test filtering by the renamed custom date fields. + * + * The conversion to date picker will result int these fields + * being renamed _high & _low and needing to return correctly. + * + * @throws \CRM_Core_Exception + */ + public function testSearchCustomDataDateLowWithPermsInPlay() { + $this->createLoggedInUser(); + CRM_Core_Config::singleton()->userPermissionClass->permissions = ['view all contacts', 'access all custom data']; + $this->createCustomGroupWithFieldOfType([], 'date'); + $dateCustomFieldName = $this->getCustomFieldName('date'); + // Assigning the relevant form value to be within a custom key is normally done in + // build field params. It would be better if it were all done in convertFormValues + // but for now we just imitate it. + $formValues = [ + $dateCustomFieldName . '_low' => '2014-06-06', + ]; + $params = CRM_Contact_BAO_Query::convertFormValues($formValues); + $queryObject = new CRM_Contact_BAO_Query($params); + $queryObject->query(); + $this->assertEquals( + 'civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\'', + trim($queryObject->_where[0][0]) + ); + $this->assertEquals( + 'FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id ) LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id +LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', + trim($queryObject->_fromClause) + ); + $this->assertEquals('Test Date - greater than or equal to "June 6th, 2014 12:00 AM"', $queryObject->_qill[0][0]); + $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']); + $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables[$this->getCustomGroupTable()])); } /** * Test filtering by relative custom data dates. + * + * @throws \CRM_Core_Exception */ public function testSearchCustomDataDateFromTo() { $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); @@ -106,9 +181,9 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { $dateCustomFieldName . '_to' => '2015-06-06', ]; - $params[$dateCustomField['id']] = CRM_Contact_BAO_Query::convertFormValues($formValues); - $queryObj = new CRM_Core_BAO_CustomQuery($params); - $queryObj->Query(); + $params = CRM_Contact_BAO_Query::convertFormValues($formValues); + $queryObj = new CRM_Contact_BAO_Query($params); + $queryObj->query(); $this->assertEquals( 'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"', $queryObj->_where[0][0] @@ -122,6 +197,8 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { /** * Test filtering by relative custom data. + * + * @throws \CRM_Core_Exception */ public function testSearchCustomDataFromTo() { $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); @@ -151,11 +228,11 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { $customFieldName . '_to' => $to, ]; - $params = [$customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)]; - $queryObj = new CRM_Core_BAO_CustomQuery($params); - $queryObj->Query(); + $params = CRM_Contact_BAO_Query::convertFormValues($formValues); + $queryObj = new CRM_Contact_BAO_Query($params); + $queryObj->query(); $this->assertEquals( - "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"", + 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"", $queryObj->_where[0][0] ); $this->assertEquals($queryObj->_qill[0][0], "$type field BETWEEN $from, $to"); @@ -164,16 +241,20 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { /** * Test filtering by relative custom data. + * + * @throws \CRM_Core_Exception */ public function testSearchCustomDataFromAndTo() { $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); - $datas = [ - 'Date' => '2015-06-06', - 'Int' => 2, - 'Float' => 12.123, - 'Money' => 91.21, + $dataSet = [ + 'Date' => ['value' => '2015-06-06', 'sql_string' => '"20150606235959"', 'qill_string' => "'June 6th, 2015 11:59 PM'", 'qill_string_greater' => "'June 6th, 2015 12:00 AM'"], + // @todo - investigate the impact of using quotes on what should be an integer field. + 'Int' => ['value' => 2, 'sql_string' => '"2"'], + 'Float' => ['value' => 12.123, 'sql_string' => '"12.123"'], + 'Money' => ['value' => 91.21], ]; - foreach ($datas as $type => $data) { + foreach ($dataSet as $type => $values) { + $data = $values['value']; $isDate = ($type === 'Date'); $customField = $this->customFieldCreate( [ @@ -186,55 +267,57 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { ); $customFieldName = 'custom_' . $customField['id']; - $expectedValue = ($isDate) ? '"20150606235959"' : (($type == 'Money') ? $data : "\"$data\""); - $expectedQillValue = ($isDate) ? "'June 6th, 2015 11:59 PM'" : $data; + $expectedValue = $values['sql_string'] ?? $data; + $expectedQillValue = $values['qill_string'] ?? $data; + $toQillValue = chr(226) . chr(137) . chr(164) . ' ' . $expectedQillValue; + $fromQillValue = chr(226) . chr(137) . chr(165) . ' ' . ($values['qill_string_greater'] ?? $expectedQillValue); // Assigning the relevant form value to be within a custom key is normally done in // build field params. It would be better if it were all done in convertFormValues // but for now we just imitate it. - //Scenrio 2 : TO date filter + //Scenario 2 : TO date filter $formValues = [ $customFieldName . '_to' => $data, ]; - $params = [$customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)]; - $queryObj = new CRM_Core_BAO_CustomQuery($params); - $queryObj->Query(); - $wierdStringThatMeansGreaterEquals = chr(226) . chr(137) . chr(164); + $params = CRM_Contact_BAO_Query::convertFormValues($formValues); + $queryObj = new CRM_Contact_BAO_Query($params); + $queryObj->query(); $this->assertEquals( - "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} <= $expectedValue", + 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} <= $expectedValue", $queryObj->_where[0][0] ); $this->assertEquals($queryObj->_qill[0][0], - "$type field " . $wierdStringThatMeansGreaterEquals . " $expectedQillValue" + "$type field $toQillValue" ); - //Scenrio 2 : FROM date filter + //Scenario 2 : FROM date filter $formValues = [ - $customFieldName . '_from' => $data, + $customFieldName . '_from' => $values['value'], ]; - $params = [$customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)]; - $queryObj = new CRM_Core_BAO_CustomQuery($params); - $queryObj->Query(); - $wierdStringThatMeansLessThanEquals = chr(226) . chr(137) . chr(165); + $params = CRM_Contact_BAO_Query::convertFormValues($formValues); + $queryObj = new CRM_Contact_BAO_Query($params); + $queryObj->query(); $expectedValue = ($isDate) ? '"20150606000000"' : $expectedValue; - $expectedQillValue = ($isDate) ? "'June 6th, 2015 12:00 AM'" : $expectedQillValue; $this->assertEquals( - "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} >= $expectedValue", + 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} >= $expectedValue", $queryObj->_where[0][0] ); - $this->assertEquals($queryObj->_qill[0][0], - "$type field " . $wierdStringThatMeansLessThanEquals . " $expectedQillValue" + $this->assertEquals( + "$type field $fromQillValue", + $queryObj->_qill[0][0] ); } } /** * Test filtering by relative custom data dates. + * + * @throws \CRM_Core_Exception */ public function testSearchCustomDataDateEquals() { $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); @@ -251,9 +334,9 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { // build field params. It would be better if it were all done in convertFormValues // but for now we just imitate it. $formValues = [$dateCustomFieldName => '2015-06-06']; - $params[$dateCustomField['id']] = CRM_Contact_BAO_Query::convertFormValues($formValues); - $queryObj = new CRM_Core_BAO_CustomQuery($params); - $queryObj->Query(); + $params = CRM_Contact_BAO_Query::convertFormValues($formValues); + $queryObj = new CRM_Contact_BAO_Query($params); + $queryObj->query(); $this->assertEquals( "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'", @@ -262,4 +345,36 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { $this->assertEquals($queryObj->_qill[0][0], "date field = 'June 6th, 2015'"); } + /** + * Test search builder style query including custom address fields. + * + * @throws \CRM_Core_Exception + */ + public function testAddressCustomFields() { + $this->createCustomGroupWithFieldOfType(['extends' => 'Address'], 'int'); + $individualID = $this->individualCreate(); + $this->callAPISuccess('Address', 'create', [ + 'contact_id' => $individualID, + 'street_address' => '10 Downing Street', + 'location_type_id' => 'Home', + $this->getCustomFieldName('int') => 5, + ]); + + $queryObject = new CRM_Contact_BAO_Query( + [[$this->getCustomFieldName('int') . '-1', '=', 5, 1, 0]], + ['contact_type' => 1, 'location' => ['Home' => ['location_type' => 1, $this->getCustomFieldName('int') => 1]]] + ); + $queryObject->query(); + $tableName = $this->getCustomGroupTable(); + $fieldName = $this->getCustomFieldColumnName('int'); + + $this->assertEquals([], $queryObject->_where[0]); + $this->assertEquals($tableName . '.' . $fieldName . ' = 5', implode(', ', $queryObject->_where[1])); + $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']); + $this->assertEquals('LEFT JOIN civicrm_address `Home-address` ON (`Home-address`.contact_id = contact_a.id AND `Home-address`.location_type_id = 1)', trim($queryObject->_whereTables['Home-address'])); + $this->assertEquals("LEFT JOIN {$tableName} ON {$tableName}.entity_id = `Home-address`.id", trim($queryObject->_whereTables[$tableName])); + $this->assertEquals([], $queryObject->_qill[0]); + $this->assertEquals(['Enter integer here = 5'], $queryObject->_qill[1]); + } + }