/**
* 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();
}
