/** * Build qill for field. * * Qill refers to the query detail visible on the UI. * * @param string $daoName * @param string $fieldName * @param mixed $fieldValue * @param string $op * @param array $pseudoExtraParam * @param int $type * Type of the field per CRM_Utils_Type * * @return array */ public static function buildQillForFieldValue($daoName, $fieldName, $fieldValue, $op, $pseudoExtraParam = array(), $type = CRM_Utils_Type::T_STRING) { $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators(); // if Operator chosen is NULL/EMPTY then if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) { return array(CRM_Utils_Array::value($op, $qillOperators, $op), ''); } if ($fieldName == 'activity_type_id') { $pseudoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE); } elseif ($fieldName == 'country_id') { $pseduoOptions = CRM_Core_PseudoConstant::country(); } elseif ($fieldName == 'county_id') { $pseduoOptions = CRM_Core_PseudoConstant::county(); } elseif ($fieldName == 'world_region') { $pseduoOptions = CRM_Core_PseudoConstant::worldRegion(); } elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') { $pseudoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE); } elseif ($fieldName == 'contribution_product_id') { $pseudoOptions = CRM_Contribute_PseudoConstant::products(); } elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') { $pseudoOptions = CRM_Core_PseudoConstant::group(); } elseif ($fieldName == 'country_id') { $pseudoOptions = CRM_Core_PseudoConstant::country(); } elseif ($daoName) { $pseudoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseudoExtraParam); } //API usually have fieldValue format as array(operator => array(values)), //so we need to separate operator out of fieldValue param if (is_array($fieldValue) && in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) { $op = key($fieldValue); $fieldValue = $fieldValue[$op]; } if (is_array($fieldValue)) { $qillString = array(); if (!empty($pseudoOptions)) { foreach ((array) $fieldValue as $val) { $qillString[] = CRM_Utils_Array::value($val, $pseudoOptions, $val); } $fieldValue = implode(', ', $qillString); } else { if ($type == CRM_Utils_Type::T_DATE) { foreach ($fieldValue as $index => $value) { $fieldValue[$index] = CRM_Utils_Date::customFormat($value); } } $separator = ', '; // @todo - this is a bit specific (one operator). // However it is covered by a unit test so can be altered later with // some confidence. if ($op == 'BETWEEN') { $separator = ' AND '; } $fieldValue = implode($separator, $fieldValue); } } elseif (!empty($pseudoOptions) && array_key_exists($fieldValue, $pseudoOptions)) { $fieldValue = $pseudoOptions[$fieldValue]; } elseif ($type === CRM_Utils_Type::T_DATE) { $fieldValue = CRM_Utils_Date::customFormat($fieldValue); } return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue); }
/** * Generate the where clause and also the english language. * equivalent * * @return void */ public function where() { foreach ($this->_ids as $id => $values) { // Fixed for Isuue CRM 607 if (CRM_Utils_Array::value($id, $this->_fields) === NULL || !$values) { continue; } $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; foreach ($values as $tuple) { list($name, $op, $value, $grouping, $wildcard) = $tuple; $field = $this->_fields[$id]; $fieldName = "{$field['table_name']}.{$field['column_name']}"; // Autocomplete comes back as a string not an array if ($field['data_type'] == 'String' && $field['html_type'] == 'Autocomplete-Select' && $op == '=') { $value = explode(',', $value); } $isSerialized = CRM_Core_BAO_CustomField::isSerialized($field); // fix $value here to escape sql injection attacks $qillValue = NULL; if (!is_array($value)) { $value = CRM_Core_DAO::escapeString(trim($value)); $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options); } elseif (count($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) { $op = key($value); $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value[$op], $id, $this->_options); } else { $op = 'IN'; $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options); } $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op); switch ($field['data_type']) { case 'String': case 'StateProvince': case 'Country': if ($field['is_search_range'] && is_array($value)) { $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); } else { // fix $value here to escape sql injection attacks if (!is_array($value)) { if ($field['data_type'] == 'String') { $value = CRM_Utils_Type::escape($strtolower($value), 'String'); } else { $value = CRM_Utils_Type::escape($value, 'Integer'); } } elseif ($isSerialized) { if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) { $op = key($value); $value = $value[$op]; } $value = implode(',', $value); } // CRM-14563,CRM-16575 : Special handling of multi-select custom fields if ($isSerialized && !empty($value)) { if (strstr($op, 'IN')) { $value = str_replace(",", "[[:cntrl:]]*|[[:cntrl:]]*", $value); $value = str_replace('(', '[[.left-parenthesis.]]', $value); $value = str_replace(')', '[[.right-parenthesis.]]', $value); } $op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE'; $value = "[[:cntrl:]]*" . $value . "[[:cntrl:]]*"; if (!$wildcard) { $value = str_replace("[[:cntrl:]]*|", '', $value); } } //FIX for custom data query fired against no value(NULL/NOT NULL) $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); $this->_qill[$grouping][] = "{$field['label']} {$qillOp} {$qillValue}"; } break; case 'ContactReference': $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : ''; $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$label}"; break; case 'Int': if ($field['is_search_range'] && is_array($value)) { $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); } else { $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); } break; case 'Boolean': if (!is_array($value)) { if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) { $value = 1; } else { $value = (int) $value; } $value = $value == 1 ? 1 : 0; $qillValue = $value ? 'Yes' : 'No'; } $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); break; case 'Link': case 'Memo': $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); break; case 'Money': if (is_array($value)) { $value = CRM_Utils_Array::value($op, $value, $value); foreach ($value as $key => $val) { $moneyFormat = CRM_Utils_Rule::cleanMoney($value[$key]); $value[$key] = $moneyFormat; } } else { $value = CRM_Utils_Rule::cleanMoney($value); } case 'Float': if ($field['is_search_range']) { $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); } else { $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float'); $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); } break; case 'Date': $fromValue = CRM_Utils_Array::value('from', $value); $toValue = CRM_Utils_Array::value('to', $value); if (!$fromValue && !$toValue) { if (!CRM_Utils_Date::processDate($value) && !in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) { continue; } // hack to handle yy format during search if (is_numeric($value) && strlen($value) == 4) { $value = "01-01-{$value}"; } $date = CRM_Utils_Date::processDate($value); $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} " . CRM_Utils_Date::customFormat($date); } else { if (is_numeric($fromValue) && strlen($fromValue) == 4) { $fromValue = "01-01-{$fromValue}"; } if (is_numeric($toValue) && strlen($toValue) == 4) { $toValue = "01-01-{$toValue}"; } // TO DO: add / remove time based on date parts $fromDate = CRM_Utils_Date::processDate($fromValue); $toDate = CRM_Utils_Date::processDate($toValue); if (!$fromDate && !$toDate) { continue; } if ($fromDate) { $this->_where[$grouping][] = "{$fieldName} >= {$fromDate}"; $this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate); } if ($toDate) { $this->_where[$grouping][] = "{$fieldName} <= {$toDate}"; $this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate); } } break; case 'File': if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') { switch ($op) { case 'IS EMPTY': $op = 'IS NULL'; break; case 'IS NOT EMPTY': $op = 'IS NOT NULL'; break; } $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} "; } break; } } } }
/** * Build qill for field. * * Qill refers to the query detail visible on the UI. * * @param $daoName * @param $fieldName * @param $fieldValue * @param $op * @param array $pseduoExtraParam * * @return array */ public static function buildQillForFieldValue($daoName = NULL, $fieldName, $fieldValue, $op, $pseduoExtraParam = array()) { $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators(); if ($fieldName == 'activity_type_id') { $pseduoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE); } elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') { $pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE); } elseif ($fieldName == 'contribution_product_id') { $pseduoOptions = CRM_Contribute_PseudoConstant::products(); } elseif ($daoName) { $pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array()); } //API usually have fieldValue format as array(operator => array(values)), //so we need to separate operator out of fieldValue param if (is_array($fieldValue) && in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) { $op = key($fieldValue); $fieldValue = $fieldValue[$op]; } if (is_array($fieldValue)) { $qillString = array(); if (!empty($pseduoOptions)) { foreach ((array) $fieldValue as $val) { $qillString[] = $pseduoOptions[$val]; } $fieldValue = implode(', ', $qillString); } else { $fieldValue = implode(', ', $fieldValue); } } elseif (!empty($pseduoOptions) && array_key_exists($fieldValue, $pseduoOptions)) { $fieldValue = $pseduoOptions[$fieldValue]; } return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue); }
/** * Build the mapping form. * * @param CRM_Core_Form $form * @param string $mappingType * (Export/Import/Search Builder). * @param int $mappingId * @param int $columnNo * @param int $blockCount * (no of blocks shown). * @param NULL $exportMode * * @return void */ public static function buildMappingForm(&$form, $mappingType = 'Export', $mappingId = NULL, $columnNo, $blockCount = 3, $exportMode = NULL) { if ($mappingType == 'Export') { $name = "Map"; $columnCount = array('1' => $columnNo); } elseif ($mappingType == 'Search Builder') { $name = "Builder"; $columnCount = $columnNo; } //get the saved mapping details if ($mappingType == 'Export') { $form->applyFilter('saveMappingName', 'trim'); //to save the current mappings if (!isset($mappingId)) { $saveDetailsName = ts('Save this field mapping'); $form->add('text', 'saveMappingName', ts('Name')); $form->add('text', 'saveMappingDesc', ts('Description')); } else { $form->assign('loadedMapping', $mappingId); $params = array('id' => $mappingId); $temp = array(); $mappingDetails = CRM_Core_BAO_Mapping::retrieve($params, $temp); $form->assign('savedName', $mappingDetails->name); $form->add('hidden', 'mappingId', $mappingId); $form->addElement('checkbox', 'updateMapping', ts('Update this field mapping'), NULL); $saveDetailsName = ts('Save as a new field mapping'); $form->add('text', 'saveMappingName', ts('Name')); $form->add('text', 'saveMappingDesc', ts('Description')); } $form->addElement('checkbox', 'saveMapping', $saveDetailsName, NULL, array('onclick' => "showSaveDetails(this)")); $form->addFormRule(array('CRM_Export_Form_Map', 'formRule'), $form->get('mappingTypeId')); } elseif ($mappingType == 'Search Builder') { $form->addElement('submit', 'addBlock', ts('Also include contacts where'), array('class' => 'submit-link')); } $defaults = array(); $hasLocationTypes = array(); $hasRelationTypes = array(); $fields = array(); if ($mappingType == 'Export') { $required = TRUE; } elseif ($mappingType == 'Search Builder') { $required = FALSE; } $contactType = array('Individual', 'Household', 'Organization'); foreach ($contactType as $value) { $contactFields = CRM_Contact_BAO_Contact::exportableFields($value, FALSE, $required); $contactFields = array_merge($contactFields, CRM_Contact_BAO_Query_Hook::singleton()->getFields()); // exclude the address options disabled in the Address Settings $fields[$value] = CRM_Core_BAO_Address::validateAddressOptions($contactFields); ksort($fields[$value]); if ($mappingType == 'Export') { $relationships = array(); $relationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, NULL, NULL, $value); asort($relationshipTypes); foreach ($relationshipTypes as $key => $var) { list($type) = explode('_', $key); $relationships[$key]['title'] = $var; $relationships[$key]['headerPattern'] = '/' . preg_quote($var, '/') . '/'; $relationships[$key]['export'] = TRUE; $relationships[$key]['relationship_type_id'] = $type; $relationships[$key]['related'] = TRUE; $relationships[$key]['hasRelationType'] = 1; } if (!empty($relationships)) { $fields[$value] = array_merge($fields[$value], array('related' => array('title' => ts('- related contact info -'))), $relationships); } } } //get the current employer for mapping. if ($required) { $fields['Individual']['current_employer']['title'] = ts('Current Employer'); } // add component fields $compArray = array(); //we need to unset groups, tags, notes for component export if ($exportMode != CRM_Export_Form_Select::CONTACT_EXPORT) { foreach (array('groups', 'tags', 'notes') as $value) { unset($fields['Individual'][$value]); unset($fields['Household'][$value]); unset($fields['Organization'][$value]); } } if ($mappingType == 'Search Builder') { //build the common contact fields array. $fields['Contact'] = array(); foreach ($fields['Individual'] as $key => $value) { if (!empty($fields['Household'][$key]) && !empty($fields['Organization'][$key])) { $fields['Contact'][$key] = $value; unset($fields['Organization'][$key], $fields['Household'][$key], $fields['Individual'][$key]); } } if (array_key_exists('note', $fields['Contact'])) { $noteTitle = $fields['Contact']['note']['title']; $fields['Contact']['note']['title'] = $noteTitle . ': ' . ts('Body and Subject'); $fields['Contact']['note_body'] = array('title' => $noteTitle . ': ' . ts('Body Only'), 'name' => 'note_body'); $fields['Contact']['note_subject'] = array('title' => $noteTitle . ': ' . ts('Subject Only'), 'name' => 'note_subject'); } } if ($mappingType == 'Search Builder' || $exportMode == CRM_Export_Form_Select::CONTRIBUTE_EXPORT) { if (CRM_Core_Permission::access('CiviContribute')) { $fields['Contribution'] = CRM_Contribute_BAO_Contribution::exportableFields(); foreach (array('contribution_contact_id', 'contribution_soft_credit_name', 'contribution_soft_credit_amount', 'contribution_soft_credit_type', 'contribution_soft_credit_contribution_id') as $element) { unset($fields['Contribution'][$element]); } $compArray['Contribution'] = ts('Contribution'); } } if ($mappingType == 'Search Builder' || $exportMode == CRM_Export_Form_Select::EVENT_EXPORT) { if (CRM_Core_Permission::access('CiviEvent')) { $fields['Participant'] = CRM_Event_BAO_Participant::exportableFields(); //get the component payment fields if ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT) { $componentPaymentFields = array(); foreach (CRM_Export_BAO_Export::componentPaymentFields() as $payField => $payTitle) { $componentPaymentFields[$payField] = array('title' => $payTitle); } $fields['Participant'] = array_merge($fields['Participant'], $componentPaymentFields); } $compArray['Participant'] = ts('Participant'); } } if ($mappingType == 'Search Builder' || $exportMode == CRM_Export_Form_Select::MEMBER_EXPORT) { if (CRM_Core_Permission::access('CiviMember')) { $fields['Membership'] = CRM_Member_BAO_Membership::getMembershipFields($exportMode); unset($fields['Membership']['membership_contact_id']); $compArray['Membership'] = ts('Membership'); } } if ($mappingType == 'Search Builder' || $exportMode == CRM_Export_Form_Select::PLEDGE_EXPORT) { if (CRM_Core_Permission::access('CiviPledge')) { $fields['Pledge'] = CRM_Pledge_BAO_Pledge::exportableFields(); unset($fields['Pledge']['pledge_contact_id']); $compArray['Pledge'] = ts('Pledge'); } } if ($mappingType == 'Search Builder' || $exportMode == CRM_Export_Form_Select::CASE_EXPORT) { if (CRM_Core_Permission::access('CiviCase')) { $fields['Case'] = CRM_Case_BAO_Case::exportableFields(); $compArray['Case'] = ts('Case'); $fields['Activity'] = CRM_Activity_BAO_Activity::exportableFields('Case'); $compArray['Activity'] = ts('Case Activity'); unset($fields['Case']['case_contact_id']); } } if ($mappingType == 'Search Builder' || $exportMode == CRM_Export_Form_Select::GRANT_EXPORT) { if (CRM_Core_Permission::access('CiviGrant')) { $fields['Grant'] = CRM_Grant_BAO_Grant::exportableFields(); unset($fields['Grant']['grant_contact_id']); if ($mappingType == 'Search Builder') { unset($fields['Grant']['grant_type_id']); } $compArray['Grant'] = ts('Grant'); } } if ($mappingType == 'Search Builder' || $exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { $fields['Activity'] = CRM_Activity_BAO_Activity::exportableFields('Activity'); $compArray['Activity'] = ts('Activity'); } //Contact Sub Type For export $contactSubTypes = array(); $subTypes = CRM_Contact_BAO_ContactType::subTypeInfo(); foreach ($subTypes as $subType => $val) { //adding subtype specific relationships CRM-5256 $csRelationships = array(); if ($mappingType == 'Export') { $subTypeRelationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, NULL, NULL, $val['parent'], FALSE, 'label', TRUE, $subType); foreach ($subTypeRelationshipTypes as $key => $var) { if (!array_key_exists($key, $fields[$val['parent']])) { list($type) = explode('_', $key); $csRelationships[$key]['title'] = $var; $csRelationships[$key]['headerPattern'] = '/' . preg_quote($var, '/') . '/'; $csRelationships[$key]['export'] = TRUE; $csRelationships[$key]['relationship_type_id'] = $type; $csRelationships[$key]['related'] = TRUE; $csRelationships[$key]['hasRelationType'] = 1; } } } $fields[$subType] = $fields[$val['parent']] + $csRelationships; //custom fields for sub type $subTypeFields = CRM_Core_BAO_CustomField::getFieldsForImport($subType); $fields[$subType] += $subTypeFields; if (!empty($subTypeFields) || !empty($csRelationships)) { $contactSubTypes[$subType] = $val['label']; } } unset($subTypes); foreach ($fields as $key => $value) { foreach ($value as $key1 => $value1) { //CRM-2676, replacing the conflict for same custom field name from different custom group. $customGroupName = self::getCustomGroupName($key1); if ($customGroupName) { $relatedMapperFields[$key][$key1] = $mapperFields[$key][$key1] = $customGroupName . ': ' . $value1['title']; } else { $relatedMapperFields[$key][$key1] = $mapperFields[$key][$key1] = $value1['title']; } if (isset($value1['hasLocationType'])) { $hasLocationTypes[$key][$key1] = $value1['hasLocationType']; } if (isset($value1['hasRelationType'])) { $hasRelationTypes[$key][$key1] = $value1['hasRelationType']; unset($relatedMapperFields[$key][$key1]); } } if (array_key_exists('related', $relatedMapperFields[$key])) { unset($relatedMapperFields[$key]['related']); } } $mapperKeys = array_keys($mapperFields); $locationTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id'); $defaultLocationType = CRM_Core_BAO_LocationType::getDefault(); // FIXME: dirty hack to make the default option show up first. This // avoids a mozilla browser bug with defaults on dynamically constructed // selector widgets. if ($defaultLocationType) { $defaultLocation = $locationTypes[$defaultLocationType->id]; unset($locationTypes[$defaultLocationType->id]); $locationTypes = array($defaultLocationType->id => $defaultLocation) + $locationTypes; } $locationTypes = array(' ' => ts('Primary')) + $locationTypes; // since we need a hierarchical list to display contact types & subtypes, // this is what we going to display in first selector $contactTypes = CRM_Contact_BAO_ContactType::getSelectElements(FALSE, FALSE); if ($mappingType == 'Search Builder') { $contactTypes = array('Contact' => ts('Contacts')) + $contactTypes; } $sel1 = array('' => ts('- select record type -')) + $contactTypes + $compArray; foreach ($sel1 as $key => $sel) { if ($key) { // sort everything BUT the contactType which is sorted seperately by // an initial commit of CRM-13278 (check ksort above) if (!in_array($key, $contactType)) { asort($mapperFields[$key]); } $sel2[$key] = array('' => ts('- select field -')) + $mapperFields[$key]; } } $sel3[''] = NULL; $sel5[''] = NULL; $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id'); $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id'); asort($phoneTypes); foreach ($sel1 as $k => $sel) { if ($k) { foreach ($locationTypes as $key => $value) { if (trim($key) != '') { $sel4[$k]['phone'][$key] =& $phoneTypes; $sel4[$k]['im'][$key] =& $imProviders; } } } } foreach ($sel1 as $k => $sel) { if ($k) { foreach ($mapperFields[$k] as $key => $value) { if (isset($hasLocationTypes[$k][$key])) { $sel3[$k][$key] = $locationTypes; } else { $sel3[$key] = NULL; } } } } // Array for core fields and relationship custom data $relationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, NULL, NULL, NULL, TRUE); if ($mappingType == 'Export') { foreach ($sel1 as $k => $sel) { if ($k) { foreach ($mapperFields[$k] as $field => $dontCare) { if (isset($hasRelationTypes[$k][$field])) { list($id, $first, $second) = explode('_', $field); // FIX ME: For now let's not expose custom data related to relationship $relationshipCustomFields = array(); //$relationshipCustomFields = self::getRelationTypeCustomGroupData( $id ); //asort($relationshipCustomFields); $relationshipType = new CRM_Contact_BAO_RelationshipType(); $relationshipType->id = $id; if ($relationshipType->find(TRUE)) { $direction = "contact_sub_type_{$second}"; if (isset($relationshipType->{$direction})) { $relatedFields = array_merge((array) $relatedMapperFields[$relationshipType->{$direction}], (array) $relationshipCustomFields); } else { $target_type = 'contact_type_' . $second; $relatedFields = array_merge((array) $relatedMapperFields[$relationshipType->{$target_type}], (array) $relationshipCustomFields); } } $relationshipType->free(); asort($relatedFields); $sel5[$k][$field] = $relatedFields; } } } } //Location Type for relationship fields foreach ($sel5 as $k => $v) { if ($v) { foreach ($v as $rel => $fields) { foreach ($fields as $field => $fieldLabel) { if (isset($hasLocationTypes[$k][$field])) { $sel6[$k][$rel][$field] = $locationTypes; } } } } } //PhoneTypes for relationship fields $sel7[''] = NULL; foreach ($sel6 as $k => $rel) { if ($k) { foreach ($rel as $phonekey => $phonevalue) { foreach ($locationTypes as $locType => $loc) { if (trim($locType) != '') { $sel7[$k][$phonekey]['phone'][$locType] =& $phoneTypes; $sel7[$k][$phonekey]['im'][$locType] =& $imProviders; } } } } } } //special fields that have location, hack for primary location $specialFields = array('street_address', 'supplemental_address_1', 'supplemental_address_2', 'city', 'postal_code', 'postal_code_suffix', 'geo_code_1', 'geo_code_2', 'state_province', 'country', 'phone', 'email', 'im'); if (isset($mappingId)) { $colCnt = 0; list($mappingName, $mappingContactType, $mappingLocation, $mappingPhoneType, $mappingImProvider, $mappingRelation, $mappingOperator, $mappingValue) = CRM_Core_BAO_Mapping::getMappingFields($mappingId); $blkCnt = count($mappingName); if ($blkCnt >= $blockCount) { $blockCount = $blkCnt + 1; } for ($x = 1; $x < $blockCount; $x++) { if (isset($mappingName[$x])) { $colCnt = count($mappingName[$x]); if ($colCnt >= $columnCount[$x]) { $columnCount[$x] = $colCnt; } } } } $form->_blockCount = $blockCount; $form->_columnCount = $columnCount; $form->set('blockCount', $form->_blockCount); $form->set('columnCount', $form->_columnCount); $defaults = $noneArray = $nullArray = array(); //used to warn for mismatch column count or mismatch mapping $warning = 0; for ($x = 1; $x < $blockCount; $x++) { for ($i = 0; $i < $columnCount[$x]; $i++) { $sel =& $form->addElement('hierselect', "mapper[{$x}][{$i}]", ts('Mapper for Field %1', array(1 => $i)), NULL); $jsSet = FALSE; if (isset($mappingId)) { $locationId = isset($mappingLocation[$x][$i]) ? $mappingLocation[$x][$i] : 0; if (isset($mappingName[$x][$i])) { if (is_array($mapperFields[$mappingContactType[$x][$i]])) { if (isset($mappingRelation[$x][$i])) { $contactDetails = strtolower(str_replace(" ", "_", $mappingName[$x][$i])); $relLocationId = isset($mappingLocation[$x][$i]) ? $mappingLocation[$x][$i] : 0; if (!$relLocationId && in_array($mappingName[$x][$i], $specialFields)) { $relLocationId = " "; } $relPhoneType = isset($mappingPhoneType[$x][$i]) ? $mappingPhoneType[$x][$i] : NULL; $defaults["mapper[{$x}][{$i}]"] = array($mappingContactType[$x][$i], $mappingRelation[$x][$i], $locationId, $phoneType, $mappingName[$x][$i], $relLocationId, $relPhoneType); if (!$locationId) { $noneArray[] = array($x, $i, 2); } if (!$phoneType && !$imProvider) { $noneArray[] = array($x, $i, 3); } if (!$mappingName[$x][$i]) { $noneArray[] = array($x, $i, 4); } if (!$relLocationId) { $noneArray[] = array($x, $i, 5); } if (!$relPhoneType) { $noneArray[] = array($x, $i, 6); } $noneArray[] = array($x, $i, 2); } else { $phoneType = isset($mappingPhoneType[$x][$i]) ? $mappingPhoneType[$x][$i] : NULL; $imProvider = isset($mappingImProvider[$x][$i]) ? $mappingImProvider[$x][$i] : NULL; if (!$locationId && in_array($mappingName[$x][$i], $specialFields)) { $locationId = " "; } $defaults["mapper[{$x}][{$i}]"] = array($mappingContactType[$x][$i], $mappingName[$x][$i], $locationId, $phoneType); if (!$mappingName[$x][$i]) { $noneArray[] = array($x, $i, 1); } if (!$locationId) { $noneArray[] = array($x, $i, 2); } if (!$phoneType && !$imProvider) { $noneArray[] = array($x, $i, 3); } $noneArray[] = array($x, $i, 4); $noneArray[] = array($x, $i, 5); $noneArray[] = array($x, $i, 6); } $jsSet = TRUE; if (CRM_Utils_Array::value($i, CRM_Utils_Array::value($x, $mappingOperator))) { $defaults["operator[{$x}][{$i}]"] = CRM_Utils_Array::value($i, $mappingOperator[$x]); } if (CRM_Utils_Array::value($i, CRM_Utils_Array::value($x, $mappingValue))) { $defaults["value[{$x}][{$i}]"] = CRM_Utils_Array::value($i, $mappingValue[$x]); } } } } //Fix for Search Builder if ($mappingType == 'Export') { $j = 7; } else { $j = 4; } $formValues = $form->exportValues(); if (!$jsSet) { if (empty($formValues)) { // Incremented length for third select box(relationship type) for ($k = 1; $k < $j; $k++) { $noneArray[] = array($x, $i, $k); } } else { if (!empty($formValues['mapper'][$x])) { foreach ($formValues['mapper'][$x] as $value) { for ($k = 1; $k < $j; $k++) { if (!isset($formValues['mapper'][$x][$i][$k]) || !$formValues['mapper'][$x][$i][$k]) { $noneArray[] = array($x, $i, $k); } else { $nullArray[] = array($x, $i, $k); } } } } else { for ($k = 1; $k < $j; $k++) { $noneArray[] = array($x, $i, $k); } } } } //Fix for Search Builder if ($mappingType == 'Export') { if (!isset($mappingId) || $i >= count(reset($mappingName))) { if (isset($formValues['mapper']) && isset($formValues['mapper'][$x][$i][1]) && array_key_exists($formValues['mapper'][$x][$i][1], $relationshipTypes)) { $sel->setOptions(array($sel1, $sel2, $sel5, $sel6, $sel7, $sel3, $sel4)); } else { $sel->setOptions(array($sel1, $sel2, $sel3, $sel4, $sel5, $sel6, $sel7)); } } else { $sel->setOptions(array($sel1, $sel2, $sel3, $sel4, $sel5, $sel6, $sel7)); } } else { $sel->setOptions(array($sel1, $sel2, $sel3, $sel4)); } if ($mappingType == 'Search Builder') { //CRM -2292, restricted array set $operatorArray = array('' => ts('-operator-')) + CRM_Core_SelectValues::getSearchBuilderOperators(); $form->add('select', "operator[{$x}][{$i}]", '', $operatorArray); $form->add('text', "value[{$x}][{$i}]", ''); } } //end of columnCnt for if ($mappingType == 'Search Builder') { $title = ts('Another search field'); } else { $title = ts('Select more fields'); } $form->addElement('submit', "addMore[{$x}]", $title, array('class' => 'submit-link')); } //end of block for $js = "<script type='text/javascript'>\n"; $formName = "document.{$name}"; if (!empty($nullArray)) { $js .= "var nullArray = ["; $elements = array(); $seen = array(); foreach ($nullArray as $element) { $key = "{$element[0]}, {$element[1]}, {$element[2]}"; if (!isset($seen[$key])) { $elements[] = "[{$key}]"; $seen[$key] = 1; } } $js .= implode(', ', $elements); $js .= "]"; $js .= "\n for (var i=0;i<nullArray.length;i++) {\n if ( {$formName}['mapper['+nullArray[i][0]+']['+nullArray[i][1]+']['+nullArray[i][2]+']'] ) {\n {$formName}['mapper['+nullArray[i][0]+']['+nullArray[i][1]+']['+nullArray[i][2]+']'].style.display = '';\n }\n }\n"; } if (!empty($noneArray)) { $js .= "var noneArray = ["; $elements = array(); $seen = array(); foreach ($noneArray as $element) { $key = "{$element[0]}, {$element[1]}, {$element[2]}"; if (!isset($seen[$key])) { $elements[] = "[{$key}]"; $seen[$key] = 1; } } $js .= implode(', ', $elements); $js .= "]"; $js .= "\n for (var i=0;i<noneArray.length;i++) {\n if ( {$formName}['mapper['+noneArray[i][0]+']['+noneArray[i][1]+']['+noneArray[i][2]+']'] ) {\n {$formName}['mapper['+noneArray[i][0]+']['+noneArray[i][1]+']['+noneArray[i][2]+']'].style.display = 'none';\n }\n }\n"; } $js .= "</script>\n"; $form->assign('initHideBoxes', $js); $form->assign('columnCount', $columnCount); $form->assign('blockCount', $blockCount); $form->setDefaults($defaults); $form->setDefaultAction('refresh'); }
public function _testCustomValue($customField, $sqlOps, $type) { $isSerialized = CRM_Core_BAO_CustomField::isSerialized($customField); $customId = $customField['id']; $params = array('contact_type' => 'Individual', 'email' => substr(sha1(rand()), 0, 7) . '*****@*****.**'); $result = $this->callAPISuccess('Contact', 'create', $params); $contactId = $result['id']; $count = rand(1, 2); if ($isSerialized) { $selectedValue = $this->optionGroup[$type]['values']; $notselectedValue = $selectedValue[$count]; unset($selectedValue[$count]); } elseif ($customField['html_type'] == 'Link') { $selectedValue = "http://" . substr(sha1(rand()), 0, 7) . ".com"; $notselectedValue = "http://" . substr(sha1(rand()), 0, 7) . ".com"; } elseif ($type == 'date') { $selectedValue = date('Ymd'); $notselectedValue = $lesserSelectedValue = date('Ymd', strtotime('yesterday')); $greaterSelectedValue = date('Ymd', strtotime('+ 1 day')); } elseif ($type == 'contact') { $selectedValue = $this->optionGroup[$type]['values'][1]; $notselectedValue = $this->optionGroup[$type]['values'][0]; } elseif ($type == 'boolean') { $selectedValue = 1; $notselectedValue = 0; } else { $selectedValue = $this->optionGroup[$type]['values'][0]; $notselectedValue = $this->optionGroup[$type]['values'][$count]; if (in_array(">", $sqlOps)) { $greaterSelectedValue = $selectedValue + 1; $lesserSelectedValue = $selectedValue - 1; } } $params = array('entity_id' => $contactId, 'custom_' . $customId => $selectedValue); $this->callAPISuccess('CustomValue', 'create', $params); foreach ($sqlOps as $op) { $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op); switch ($op) { case '=': $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => is_array($selectedValue) ? implode(CRM_Core_DAO::VALUE_SEPARATOR, $selectedValue) : $selectedValue)); $this->assertEquals($contactId, $result['id']); break; case '!=': $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $notselectedValue))); $this->assertEquals(TRUE, array_key_exists($contactId, $result['values'])); break; case '>': case '<': case '>=': case '<=': if ($isSerialized) { continue; } // To be precise in for these operator we can't just rely on one contact, // hence creating multiple contact with custom value less/more then $selectedValue respectively $result = $this->callAPISuccess('Contact', 'create', array('contact_type' => 'Individual', 'email' => substr(sha1(rand()), 0, 7) . '*****@*****.**')); $contactId2 = $result['id']; $this->callAPISuccess('CustomValue', 'create', array('entity_id' => $contactId2, 'custom_' . $customId => $lesserSelectedValue)); if ($op == '>') { $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $lesserSelectedValue))); $this->assertEquals($contactId, $result['id']); } elseif ($op == '<') { $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $selectedValue))); $this->assertEquals($contactId2, $result['id']); } else { $result = $this->callAPISuccess('Contact', 'create', array('contact_type' => 'Individual', 'email' => substr(sha1(rand()), 0, 7) . '*****@*****.**')); $contactId3 = $result['id']; $this->callAPISuccess('CustomValue', 'create', array('entity_id' => $contactId3, 'custom_' . $customId => $greaterSelectedValue)); $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $selectedValue))); $this->assertEquals($contactId, $result['values'][$contactId]['id']); if ($op == '>=') { $this->assertEquals($contactId3, $result['values'][$contactId3]['id']); } else { $this->assertEquals($contactId2, $result['values'][$contactId2]['id']); } $this->callAPISuccess('contact', 'delete', array('id' => $contactId3)); } $this->callAPISuccess('contact', 'delete', array('id' => $contactId2)); break; case 'IN': $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => (array) $selectedValue))); $this->assertEquals($contactId, $result['id']); break; case 'NOT IN': $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => (array) $notselectedValue))); $this->assertEquals($contactId, $result['id']); break; case 'LIKE': $selectedValue = is_array($selectedValue) ? $selectedValue[0] : $selectedValue; $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => "%{$selectedValue}%"))); $this->assertEquals($contactId, $result['id']); break; case 'NOT LIKE': $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $notselectedValue))); $this->assertEquals($contactId, $result['id']); break; case 'IS NULL': $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => 1))); $this->assertEquals(FALSE, array_key_exists($contactId, $result['values'])); break; case 'IS NOT NULL': $result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => 1))); $this->assertEquals($contactId, $result['id']); break; } } $this->callAPISuccess('Contact', 'delete', array('id' => $contactId)); }
/** * @param $value * @param $grouping * @param string $op */ public function includeContactSubTypes($value, $grouping, $op = 'LIKE', $contact) { $clause = array(); $alias = "{$contact}.contact_sub_type"; $qillOperators = array('NOT LIKE' => ts('Not Like')) + CRM_Core_SelectValues::getSearchBuilderOperators(); $op = str_replace('IN', 'LIKE', $op); $op = str_replace('=', 'LIKE', $op); $op = str_replace('!', 'NOT ', $op); if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) { $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($alias, $op, $value, 'String'); } elseif (is_array($value)) { foreach ($value as $k => $v) { if (!empty($k)) { $clause[$k] = "({$alias} {$op} '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($k, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')"; } } } else { $clause[$value] = "({$alias} {$op} '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($value, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')"; } if (!empty($clause)) { $this->_where[$grouping][] = "( " . implode(' OR ', $clause) . " )"; } $this->_qill[$grouping][] = ts('Contact Subtype %1 ', array(1 => $qillOperators[$op])) . implode(' ' . ts('or') . ' ', array_keys($clause)); }
/** * Generate the where clause and also the english language. * equivalent * * @return void */ public function where() { foreach ($this->_ids as $id => $values) { // Fixed for Isuue CRM 607 if (CRM_Utils_Array::value($id, $this->_fields) === NULL || !$values) { continue; } $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; foreach ($values as $tuple) { list($name, $op, $value, $grouping, $wildcard) = $tuple; $field = $this->_fields[$id]; $fieldName = "{$field['table_name']}.{$field['column_name']}"; // Autocomplete comes back as a string not an array if ($field['data_type'] == 'String' && $field['html_type'] == 'Autocomplete-Select' && $op == '=') { $value = explode(',', $value); } $isSerialized = CRM_Core_BAO_CustomField::isSerialized($field); // Handle multi-select search for any data type if (is_array($value) && !$field['is_search_range'] && $field['data_type'] != 'String') { $wildcard = $isSerialized ? $wildcard : TRUE; $options = CRM_Utils_Array::value('values', civicrm_api3('contact', 'getoptions', array('field' => $name, 'context' => 'search'), array())); $qillValue = ''; $sqlOP = $wildcard ? ' OR ' : ' AND '; $sqlValue = array(); foreach ($value as $num => &$v) { $sep = count($value) > 1 + $num ? ', ' : ' ' . ($wildcard ? ts('OR') : ts('AND')) . ' '; $qillValue .= ($num ? $sep : '') . $options[$v]; $v = CRM_Core_DAO::escapeString($v); if ($isSerialized) { $sqlValue[] = "( {$fieldName} like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $v . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) "; } else { $v = "'{$v}'"; } } if (!$isSerialized) { $sqlValue = array("{$fieldName} IN (" . implode(',', $value) . ")"); } $this->_where[$grouping][] = ' ( ' . implode($sqlOP, $sqlValue) . ' ) '; $this->_qill[$grouping][] = "{$field['label']} {$qillOp} {$qillValue}"; continue; } // fix $value here to escape sql injection attacks if (!is_array($value)) { $value = CRM_Core_DAO::escapeString(trim($value)); } $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options); $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op); switch ($field['data_type']) { case 'String': $sql = "{$fieldName}"; if ($field['is_search_range'] && is_array($value)) { $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); } else { // fix $value here to escape sql injection attacks if (!is_array($value)) { $value = CRM_Utils_Type::escape($strtolower($value), 'String'); } elseif ($isSerialized && strstr($op, 'IN')) { $value = implode(',', $value); } // CRM-14563,CRM-16575 : Special handling of multi-select custom fields if (!empty($value)) { if ($isSerialized) { if (strstr($op, 'IN')) { $value = str_replace(array('(', ')'), '', str_replace(",", "[[:cntrl:]]|[[:cntrl:]]", $value)); } $op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE'; $value = "[[:cntrl:]]" . $value . "[[:cntrl:]]"; } elseif ($wildcard) { $value = "[[:cntrl:]]%{$value}%[[:cntrl:]]"; $op = 'LIKE'; } } //FIX for custom data query fired against no value(NULL/NOT NULL) $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($sql, $op, $value, $field['data_type']); $this->_qill[$grouping][] = "{$field['label']} {$qillOp} {$qillValue}"; } break; case 'ContactReference': $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : ''; $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$label}"; break; case 'Int': if ($field['is_search_range'] && is_array($value)) { $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); } else { $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$value}"; } break; case 'Boolean': if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) { $value = 1; } else { $value = (int) $value; } $value = $value == 1 ? 1 : 0; $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); $value = $value ? ts('Yes') : ts('No'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$value}"; break; case 'Link': $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$value}"; break; case 'Float': if ($field['is_search_range'] && is_array($value)) { $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); } else { $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$value}"; } break; case 'Money': if ($field['is_search_range'] && is_array($value)) { foreach ($value as $key => $val) { $moneyFormat = CRM_Utils_Rule::cleanMoney($value[$key]); $value[$key] = $moneyFormat; } $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); } else { $moneyFormat = CRM_Utils_Rule::cleanMoney($value); $value = $moneyFormat; $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$value}"; } break; case 'Memo': $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); $this->_qill[$grouping][] = "{$field['label']} {$qillOp} {$value}"; break; case 'Date': $fromValue = CRM_Utils_Array::value('from', $value); $toValue = CRM_Utils_Array::value('to', $value); if (!$fromValue && !$toValue) { if (!CRM_Utils_Date::processDate($value) && !in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) { continue; } // hack to handle yy format during search if (is_numeric($value) && strlen($value) == 4) { $value = "01-01-{$value}"; } $date = CRM_Utils_Date::processDate($value); $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} " . CRM_Utils_Date::customFormat($date); } else { if (is_numeric($fromValue) && strlen($fromValue) == 4) { $fromValue = "01-01-{$fromValue}"; } if (is_numeric($toValue) && strlen($toValue) == 4) { $toValue = "01-01-{$toValue}"; } // TO DO: add / remove time based on date parts $fromDate = CRM_Utils_Date::processDate($fromValue); $toDate = CRM_Utils_Date::processDate($toValue); if (!$fromDate && !$toDate) { continue; } if ($fromDate) { $this->_where[$grouping][] = "{$fieldName} >= {$fromDate}"; $this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate); } if ($toDate) { $this->_where[$grouping][] = "{$fieldName} <= {$toDate}"; $this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate); } } break; case 'StateProvince': case 'Country': $this->_where[$grouping][] = "{$fieldName} {$op} " . CRM_Utils_Type::escape($value, 'Int'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$qillValue}"; break; case 'File': if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') { switch ($op) { case 'IS EMPTY': $op = 'IS NULL'; break; case 'IS NOT EMPTY': $op = 'IS NOT NULL'; break; } $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} "; } break; } } } }
/** * Generate the where clause and also the english language equivalent. */ public function where() { foreach ($this->_ids as $id => $values) { // Fixed for Issue CRM 607 if (CRM_Utils_Array::value($id, $this->_fields) === NULL || !$values) { continue; } $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; foreach ($values as $tuple) { list($name, $op, $value, $grouping, $wildcard) = $tuple; $field = $this->_fields[$id]; $fieldName = "{$field['table_name']}.{$field['column_name']}"; $isSerialized = CRM_Core_BAO_CustomField::isSerialized($field); // fix $value here to escape sql injection attacks $qillValue = NULL; if (!is_array($value)) { $value = CRM_Core_DAO::escapeString(trim($value)); $qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id); } elseif (count($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) { $op = key($value); $qillValue = strstr($op, 'NULL') ? NULL : CRM_Core_BAO_CustomField::displayValue($value[$op], $id); } else { $op = strstr($op, 'IN') ? $op : 'IN'; $qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id); } $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op); switch ($field['data_type']) { case 'String': case 'StateProvince': case 'Country': if ($field['is_search_range'] && is_array($value)) { //didn't found any field under any of these three data-types as searchable by range } else { // fix $value here to escape sql injection attacks if (!is_array($value)) { if ($field['data_type'] == 'String') { $value = CRM_Utils_Type::escape($strtolower($value), 'String'); } else { $value = CRM_Utils_Type::escape($value, 'Integer'); } } elseif ($isSerialized) { if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) { $op = key($value); $value = $value[$op]; } $value = implode(',', (array) $value); } // CRM-14563,CRM-16575 : Special handling of multi-select custom fields if ($isSerialized && !empty($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) { $sp = CRM_Core_DAO::VALUE_SEPARATOR; if (strstr($op, 'IN')) { $value = str_replace(",", "{$sp}|{$sp}", $value); $value = str_replace('(', '[[.left-parenthesis.]]', $value); $value = str_replace(')', '[[.right-parenthesis.]]', $value); } $op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE'; $value = $sp . $value . $sp; if (!$wildcard) { foreach (explode("|", $value) as $val) { $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $val, 'String'); } } else { $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); } } else { //FIX for custom data query fired against no value(NULL/NOT NULL) $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); } $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$qillValue}"; } break; case 'ContactReference': $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : ''; $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$label}"; break; case 'Int': $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); break; case 'Boolean': if (!is_array($value)) { if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) { $value = 1; } else { $value = (int) $value; } $value = $value == 1 ? 1 : 0; $qillValue = $value ? 'Yes' : 'No'; } $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); break; case 'Link': case 'Memo': $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); break; case 'Money': $value = CRM_Utils_Array::value($op, (array) $value, $value); if (is_array($value)) { foreach ($value as $key => $val) { $value[$key] = CRM_Utils_Rule::cleanMoney($value[$key]); } } else { $value = CRM_Utils_Rule::cleanMoney($value); } case 'Float': $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float'); $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); break; case 'Date': $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); list($qillOp, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $field['label'], $value, $op, array(), CRM_Utils_Type::T_DATE); $this->_qill[$grouping][] = "{$field['label']} {$qillOp} '{$qillVal}'"; break; case 'File': if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') { switch ($op) { case 'IS EMPTY': $op = 'IS NULL'; break; case 'IS NOT EMPTY': $op = 'IS NOT NULL'; break; } $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op); $this->_qill[$grouping][] = $field['label'] . " {$qillOp} "; } break; } } } }
/** * Build qill for field. * * Qill refers to the query detail visible on the UI. * * @param $daoName * @param $fieldName * @param $fieldValue * @param $op * @param array $pseduoExtraParam * * @return array */ public static function buildQillForFieldValue($daoName, $fieldName, $fieldValue, $op, $pseduoExtraParam = array()) { $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators(); if ($fieldName == 'activity_type_id') { $pseduoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE); } elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') { $pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE); } else { $pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array()); } //For those $fieldName which don't have any associated pseudoconstant defined if (empty($pseduoOptions)) { if (is_array($fieldValue)) { $op = key($fieldValue); $fieldValue = $fieldValue[$op]; if (is_array($fieldValue)) { $fieldValue = implode(', ', $fieldValue); } } } elseif (is_array($fieldValue)) { $qillString = array(); if (in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) { $op = key($fieldValue); $fieldValue = $fieldValue[$op]; } foreach ((array) $fieldValue as $val) { $qillString[] = $pseduoOptions[$val]; } $fieldValue = implode(', ', $qillString); } else { if (array_key_exists($fieldValue, $pseduoOptions)) { $fieldValue = $pseduoOptions[$fieldValue]; } } return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue); }
/** * Build quick form. */ public function buildQuickForm() { $fields = self::fields(); // Get fields of type date // FIXME: This is a hack until our fields contain this meta-data $dateFields = array(); $stringFields = array(); $searchByLabelFields = array(); foreach ($fields as $name => $field) { if (strpos($name, '_date') || CRM_Utils_Array::value('data_type', $field) == 'Date') { $dateFields[] = $name; } // it's necessary to know which of the fields are from string data type if (isset($field['type']) && $field['type'] === CRM_Utils_Type::T_STRING) { $stringFields[] = $name; } // it's necessary to know which of the fields are searchable by label if (isset($field['searchByLabel']) && $field['searchByLabel']) { $searchByLabelFields[] = $name; } } // Add javascript CRM_Core_Resources::singleton()->addScriptFile('civicrm', 'templates/CRM/Contact/Form/Search/Builder.js', 1, 'html-header')->addSetting(array('searchBuilder' => array('newBlock' => $this->get('newBlock'), 'dateFields' => $dateFields, 'fieldOptions' => self::fieldOptions(), 'stringFields' => $stringFields, 'searchByLabelFields' => $searchByLabelFields, 'generalOperators' => array('' => ts('-operator-')) + CRM_Core_SelectValues::getSearchBuilderOperators(), 'stringOperators' => array('' => ts('-operator-')) + CRM_Core_SelectValues::getSearchBuilderOperators(CRM_Utils_Type::T_STRING)))); //get the saved search mapping id $mappingId = NULL; if ($this->_ssID) { $mappingId = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $this->_ssID, 'mapping_id'); } CRM_Core_BAO_Mapping::buildMappingForm($this, 'Search Builder', $mappingId, $this->_columnCount, $this->_blockCount); parent::buildQuickForm(); }