/**
* Given a list of conditions in params and a list of desired
* return Properties generate the required select and from
* clauses. Note that since the where clause introduces new
* tables, the initial attempt also retrieves all variables used
* in the params list
*
* @return void
* @access public
*/
function selectClause()
{
$properties = array();
$this->addSpecialFields();
// CRM_Core_Error::debug( 'f', $this->_fields );
// CRM_Core_Error::debug( 'p', $this->_params );
// CRM_Core_Error::debug( 'p', $this->_paramLookup );
foreach ($this->_fields as $name => $field) {
// skip component fields
// there are done by the alter query below
// and need not be done on every field
if (substr($name, 0, 12) == 'participant_' || substr($name, 0, 7) == 'pledge_' || substr($name, 0, 5) == 'case_') {
continue;
}
// redirect to activity select clause
if (substr($name, 0, 9) == 'activity_') {
CRM_Activity_BAO_Query::select($this);
continue;
}
// if this is a hierarchical name, we ignore it
$names = explode('-', $name);
if (count($names > 1) && isset($names[1]) && is_numeric($names[1])) {
continue;
}
$cfID = CRM_Core_BAO_CustomField::getKeyID($name);
if (CRM_Utils_Array::value($name, $this->_paramLookup) || CRM_Utils_Array::value($name, $this->_returnProperties)) {
if ($cfID) {
// add to cfIDs array if not present
if (!array_key_exists($cfID, $this->_cfIDs)) {
$this->_cfIDs[$cfID] = array();
}
} elseif (isset($field['where'])) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
if (substr($tableName, 0, 6) == 'quest_') {
$this->_select['ethnicity_id_1'] = 'ethnicity_id_1';
$this->_select['gpa_weighted_calc'] = 'gpa_weighted_calc';
$this->_select['SAT_composite'] = 'SAT_composite';
$this->_select['household_income_total'] = 'household_income_total';
}
if (CRM_Utils_Array::value($tableName, self::$_dependencies)) {
$this->_tables['civicrm_address'] = 1;
$this->_select['address_id'] = 'civicrm_address.id as address_id';
$this->_element['address_id'] = 1;
}
if ($tableName == 'gender' || $tableName == 'individual_prefix' || $tableName == 'individual_suffix' || $tableName == 'im_provider' || $tableName == 'email_greeting' || $tableName == 'postal_greeting' || $tableName == 'addressee') {
CRM_Core_OptionValue::select($this);
if (in_array($tableName, array('email_greeting', 'postal_greeting', 'addressee'))) {
//get display
$greetField = "{$name}_display";
$this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
$this->_element[$greetField] = 1;
//get custom
$greetField = "{$name}_custom";
$this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
$this->_element[$greetField] = 1;
}
} else {
$this->_tables[$tableName] = 1;
// also get the id of the tableName
$tName = substr($tableName, 8);
if ($tName != 'contact') {
$this->_select["{$tName}_id"] = "{$tableName}.id as {$tName}_id";
$this->_element["{$tName}_id"] = 1;
}
//special case for phone
if ($name == 'phone') {
$this->_select['phone_type_id'] = "civicrm_phone.phone_type_id as phone_type_id";
$this->_element['phone_type_id'] = 1;
}
// if IM then select provider_id also
// to get "IM Service Provider" in a file to be exported, CRM-3140
if ($name == 'im') {
$this->_select['provider_id'] = "civicrm_im.provider_id as provider_id";
$this->_element['provider_id'] = 1;
}
if ($name == 'state_province') {
$this->_select[$name] = "civicrm_state_province.abbreviation as `{$name}`, civicrm_state_province.name as state_province_name";
$this->_element['state_province_name'] = 1;
} elseif ($tName == 'contact') {
// special case, when current employer is set for Individual contact
if ($fieldName == 'organization_name') {
$this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name";
} elseif ($fieldName != 'id') {
$this->_select[$name] = "contact_a.{$fieldName} as `{$name}`";
}
} else {
$this->_select[$name] = "{$field['where']} as `{$name}`";
}
$this->_element[$name] = 1;
}
}
} elseif ($name === 'tags') {
$this->_useGroupBy = TRUE;
$this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags";
$this->_element[$name] = 1;
$this->_tables['civicrm_tag'] = 1;
$this->_tables['civicrm_entity_tag'] = 1;
} elseif ($name === 'groups') {
$this->_useGroupBy = TRUE;
$this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_group.title)) as groups";
$this->_element[$name] = 1;
$this->_tables['civicrm_group'] = 1;
} elseif ($name === 'notes') {
$this->_useGroupBy = TRUE;
$this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_note.note)) as notes";
$this->_element[$name] = 1;
$this->_tables['civicrm_note'] = 1;
} elseif ($name === 'current_employer') {
$this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer";
$this->_element[$name] = 1;
}
}
if ($cfID && CRM_Utils_Array::value('is_search_range', $field)) {
// this is a custom field with range search enabled, so we better check for two/from values
if (CRM_Utils_Array::value($name . '_from', $this->_paramLookup)) {
if (!array_key_exists($cfID, $this->_cfIDs)) {
$this->_cfIDs[$cfID] = array();
}
foreach ($this->_paramLookup[$name . '_from'] as $pID => $p) {
// search in the cdID array for the same grouping
$fnd = FALSE;
foreach ($this->_cfIDs[$cfID] as $cID => $c) {
if ($c[3] == $p[3]) {
$this->_cfIDs[$cfID][$cID][2]['from'] = $p[2];
$fnd = TRUE;
}
}
if (!$fnd) {
$p[2] = array('from' => $p[2]);
$this->_cfIDs[$cfID][] = $p;
}
}
}
if (CRM_Utils_Array::value($name . '_to', $this->_paramLookup)) {
if (!array_key_exists($cfID, $this->_cfIDs)) {
$this->_cfIDs[$cfID] = array();
}
foreach ($this->_paramLookup[$name . '_to'] as $pID => $p) {
// search in the cdID array for the same grouping
$fnd = FALSE;
foreach ($this->_cfIDs[$cfID] as $cID => $c) {
if ($c[4] == $p[4]) {
$this->_cfIDs[$cfID][$cID][2]['to'] = $p[2];
$fnd = TRUE;
}
}
if (!$fnd) {
$p[2] = array('to' => $p[2]);
$this->_cfIDs[$cfID][] = $p;
}
}
}
}
}
// add location as hierarchical elements
$this->addHierarchicalElements();
// add multiple field like website
$this->addMultipleElements();
//fix for CRM-951
CRM_Core_Component::alterQuery($this, 'select');
if (!empty($this->_cfIDs)) {
$this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE);
$this->_customQuery->query();
$this->_select = array_merge($this->_select, $this->_customQuery->_select);
$this->_element = array_merge($this->_element, $this->_customQuery->_element);
$this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
$this->_whereTables = array_merge($this->_whereTables, $this->_customQuery->_whereTables);
$this->_options = $this->_customQuery->_options;
}
}
/**
* Given a list of conditions in params and a list of desired
* return Properties generate the required select and from
* clauses. Note that since the where clause introduces new
* tables, the initial attempt also retrieves all variables used
* in the params list
*/
public function selectClause()
{
$this->addSpecialFields();
foreach ($this->_fields as $name => $field) {
// skip component fields
// there are done by the alter query below
// and need not be done on every field
if (substr($name, 0, 12) == 'participant_' || substr($name, 0, 7) == 'pledge_' || substr($name, 0, 5) == 'case_' || substr($name, 0, 13) == 'contribution_' && (strpos($name, 'source') !== FALSE && strpos($name, 'recur') !== FALSE) || substr($name, 0, 8) == 'payment_') {
continue;
}
// redirect to activity select clause
if (substr($name, 0, 9) == 'activity_' || $name == 'parent_id') {
CRM_Activity_BAO_Query::select($this);
continue;
}
// if this is a hierarchical name, we ignore it
$names = explode('-', $name);
if (count($names) > 1 && isset($names[1]) && is_numeric($names[1])) {
continue;
}
// make an exception for special cases, to add the field in select clause
$makeException = FALSE;
//special handling for groups/tags
if (in_array($name, array('groups', 'tags', 'notes')) && isset($this->_returnProperties[substr($name, 0, -1)])) {
$makeException = TRUE;
}
// since note has 3 different options we need special handling
// note / note_subject / note_body
if ($name == 'notes') {
foreach (array('note', 'note_subject', 'note_body') as $noteField) {
if (isset($this->_returnProperties[$noteField])) {
$makeException = TRUE;
break;
}
}
}
if (in_array($name, array('prefix_id', 'suffix_id', 'gender_id', 'communication_style_id'))) {
if (CRM_Utils_Array::value($field['pseudoconstant']['optionGroupName'], $this->_returnProperties)) {
$makeException = TRUE;
}
}
$cfID = CRM_Core_BAO_CustomField::getKeyID($name);
if (!empty($this->_paramLookup[$name]) || !empty($this->_returnProperties[$name]) || $makeException) {
if ($cfID) {
// add to cfIDs array if not present
if (!array_key_exists($cfID, $this->_cfIDs)) {
$this->_cfIDs[$cfID] = array();
}
} elseif (isset($field['where'])) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
if (CRM_Utils_Array::value($tableName, self::$_dependencies)) {
$this->_tables['civicrm_address'] = 1;
$this->_select['address_id'] = 'civicrm_address.id as address_id';
$this->_element['address_id'] = 1;
}
if ($tableName == 'im_provider' || $tableName == 'email_greeting' || $tableName == 'postal_greeting' || $tableName == 'addressee') {
if ($tableName == 'im_provider') {
CRM_Core_OptionValue::select($this);
}
if (in_array($tableName, array('email_greeting', 'postal_greeting', 'addressee'))) {
$this->_element["{$name}_id"] = 1;
$this->_select["{$name}_id"] = "contact_a.{$name}_id as {$name}_id";
$this->_pseudoConstantsSelect[$name] = array('pseudoField' => $tableName, 'idCol' => "{$name}_id");
$this->_pseudoConstantsSelect[$name]['select'] = "{$name}.{$fieldName} as {$name}";
$this->_pseudoConstantsSelect[$name]['element'] = $name;
if ($tableName == 'email_greeting') {
$this->_pseudoConstantsSelect[$name]['join'] = " LEFT JOIN civicrm_option_group option_group_email_greeting ON (option_group_email_greeting.name = 'email_greeting')";
$this->_pseudoConstantsSelect[$name]['join'] .= " LEFT JOIN civicrm_option_value email_greeting ON (contact_a.email_greeting_id = email_greeting.value AND option_group_email_greeting.id = email_greeting.option_group_id ) ";
} elseif ($tableName == 'postal_greeting') {
$this->_pseudoConstantsSelect[$name]['join'] = " LEFT JOIN civicrm_option_group option_group_postal_greeting ON (option_group_postal_greeting.name = 'postal_greeting')";
$this->_pseudoConstantsSelect[$name]['join'] .= " LEFT JOIN civicrm_option_value postal_greeting ON (contact_a.postal_greeting_id = postal_greeting.value AND option_group_postal_greeting.id = postal_greeting.option_group_id ) ";
} elseif ($tableName == 'addressee') {
$this->_pseudoConstantsSelect[$name]['join'] = " LEFT JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee')";
$this->_pseudoConstantsSelect[$name]['join'] .= " LEFT JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id ) ";
}
$this->_pseudoConstantsSelect[$name]['table'] = $tableName;
//get display
$greetField = "{$name}_display";
$this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
$this->_element[$greetField] = 1;
//get custom
$greetField = "{$name}_custom";
$this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
$this->_element[$greetField] = 1;
}
} else {
if (!in_array($tableName, array('civicrm_state_province', 'civicrm_country', 'civicrm_county'))) {
$this->_tables[$tableName] = 1;
}
// also get the id of the tableName
$tName = substr($tableName, 8);
if (in_array($tName, array('country', 'state_province', 'county'))) {
if ($tName == 'state_province') {
$this->_pseudoConstantsSelect['state_province_name'] = array('pseudoField' => "{$tName}", 'idCol' => "{$tName}_id", 'bao' => 'CRM_Core_BAO_Address', 'table' => "civicrm_{$tName}", 'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ");
$this->_pseudoConstantsSelect[$tName] = array('pseudoField' => 'state_province_abbreviation', 'idCol' => "{$tName}_id", 'table' => "civicrm_{$tName}", 'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ");
} else {
$this->_pseudoConstantsSelect[$name] = array('pseudoField' => "{$tName}_id", 'idCol' => "{$tName}_id", 'bao' => 'CRM_Core_BAO_Address', 'table' => "civicrm_{$tName}", 'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ");
}
$this->_select["{$tName}_id"] = "civicrm_address.{$tName}_id as {$tName}_id";
$this->_element["{$tName}_id"] = 1;
} elseif ($tName != 'contact') {
$this->_select["{$tName}_id"] = "{$tableName}.id as {$tName}_id";
$this->_element["{$tName}_id"] = 1;
}
//special case for phone
if ($name == 'phone') {
$this->_select['phone_type_id'] = "civicrm_phone.phone_type_id as phone_type_id";
$this->_element['phone_type_id'] = 1;
}
// if IM then select provider_id also
// to get "IM Service Provider" in a file to be exported, CRM-3140
if ($name == 'im') {
$this->_select['provider_id'] = "civicrm_im.provider_id as provider_id";
$this->_element['provider_id'] = 1;
}
if ($tName == 'contact') {
// special case, when current employer is set for Individual contact
if ($fieldName == 'organization_name') {
$this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name";
} elseif ($fieldName != 'id') {
if ($fieldName == 'prefix_id') {
$this->_pseudoConstantsSelect['individual_prefix'] = array('pseudoField' => 'prefix_id', 'idCol' => "prefix_id", 'bao' => 'CRM_Contact_BAO_Contact');
}
if ($fieldName == 'suffix_id') {
$this->_pseudoConstantsSelect['individual_suffix'] = array('pseudoField' => 'suffix_id', 'idCol' => "suffix_id", 'bao' => 'CRM_Contact_BAO_Contact');
}
if ($fieldName == 'gender_id') {
$this->_pseudoConstantsSelect['gender'] = array('pseudoField' => 'gender_id', 'idCol' => "gender_id", 'bao' => 'CRM_Contact_BAO_Contact');
}
if ($name == 'communication_style_id') {
$this->_pseudoConstantsSelect['communication_style'] = array('pseudoField' => 'communication_style_id', 'idCol' => "communication_style_id", 'bao' => 'CRM_Contact_BAO_Contact');
}
$this->_select[$name] = "contact_a.{$fieldName} as `{$name}`";
}
} elseif (in_array($tName, array('country', 'county'))) {
$this->_pseudoConstantsSelect[$name]['select'] = "{$field['where']} as `{$name}`";
$this->_pseudoConstantsSelect[$name]['element'] = $name;
} elseif ($tName == 'state_province') {
$this->_pseudoConstantsSelect[$tName]['select'] = "{$field['where']} as `{$name}`";
$this->_pseudoConstantsSelect[$tName]['element'] = $name;
} else {
$this->_select[$name] = "{$field['where']} as `{$name}`";
}
if (!in_array($tName, array('state_province', 'country', 'county'))) {
$this->_element[$name] = 1;
}
}
}
} elseif ($name === 'tags') {
$this->_useGroupBy = TRUE;
$this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags";
$this->_element[$name] = 1;
$this->_tables['civicrm_tag'] = 1;
$this->_tables['civicrm_entity_tag'] = 1;
} elseif ($name === 'groups') {
$this->_useGroupBy = TRUE;
$this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_group.title)) as groups";
$this->_element[$name] = 1;
$this->_tables['civicrm_group'] = 1;
} elseif ($name === 'notes') {
// if note field is subject then return subject else body of the note
$noteColumn = 'note';
if (isset($noteField) && $noteField == 'note_subject') {
$noteColumn = 'subject';
}
$this->_useGroupBy = TRUE;
$this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_note.{$noteColumn})) as notes";
$this->_element[$name] = 1;
$this->_tables['civicrm_note'] = 1;
} elseif ($name === 'current_employer') {
$this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer";
$this->_element[$name] = 1;
}
}
if ($cfID && !empty($field['is_search_range'])) {
// this is a custom field with range search enabled, so we better check for two/from values
if (!empty($this->_paramLookup[$name . '_from'])) {
if (!array_key_exists($cfID, $this->_cfIDs)) {
$this->_cfIDs[$cfID] = array();
}
foreach ($this->_paramLookup[$name . '_from'] as $pID => $p) {
// search in the cdID array for the same grouping
$fnd = FALSE;
foreach ($this->_cfIDs[$cfID] as $cID => $c) {
if ($c[3] == $p[3]) {
$this->_cfIDs[$cfID][$cID][2]['from'] = $p[2];
$fnd = TRUE;
}
}
if (!$fnd) {
$p[2] = array('from' => $p[2]);
$this->_cfIDs[$cfID][] = $p;
}
}
}
if (!empty($this->_paramLookup[$name . '_to'])) {
if (!array_key_exists($cfID, $this->_cfIDs)) {
$this->_cfIDs[$cfID] = array();
}
foreach ($this->_paramLookup[$name . '_to'] as $pID => $p) {
// search in the cdID array for the same grouping
$fnd = FALSE;
foreach ($this->_cfIDs[$cfID] as $cID => $c) {
if ($c[4] == $p[4]) {
$this->_cfIDs[$cfID][$cID][2]['to'] = $p[2];
$fnd = TRUE;
}
}
if (!$fnd) {
$p[2] = array('to' => $p[2]);
$this->_cfIDs[$cfID][] = $p;
}
}
}
}
}
// add location as hierarchical elements
$this->addHierarchicalElements();
// add multiple field like website
$this->addMultipleElements();
//fix for CRM-951
CRM_Core_Component::alterQuery($this, 'select');
CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'select');
if (!empty($this->_cfIDs)) {
// @todo This function is the select function but instead of running 'select' it
// is running the whole query.
$this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE, $this->_locationSpecificCustomFields);
$this->_customQuery->query();
$this->_select = array_merge($this->_select, $this->_customQuery->_select);
$this->_element = array_merge($this->_element, $this->_customQuery->_element);
$this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
$this->_whereTables = array_merge($this->_whereTables, $this->_customQuery->_whereTables);
$this->_options = $this->_customQuery->_options;
}
}