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