/**
* Create the from clause.
*
* @param array $tables
* Tables that need to be included in this from clause.
* if null, return mimimal from clause (i.e. civicrm_contact)
* @param array $inner
* Tables that should be inner-joined.
* @param array $right
* Tables that should be right-joined.
*
* @param bool $primaryLocation
* @param int $mode
*
* @return string
* the from clause
*/
public static function fromClause(&$tables, $inner = NULL, $right = NULL, $primaryLocation = TRUE, $mode = 1)
{
$from = ' FROM civicrm_contact contact_a';
if (empty($tables)) {
return $from;
}
if (!empty($tables['civicrm_worldregion'])) {
$tables = array_merge(array('civicrm_country' => 1), $tables);
}
if ((!empty($tables['civicrm_state_province']) || !empty($tables['civicrm_country']) || CRM_Utils_Array::value('civicrm_county', $tables)) && empty($tables['civicrm_address'])) {
$tables = array_merge(array('civicrm_address' => 1), $tables);
}
// add group_contact and group_contact_cache table if group table is present
if (!empty($tables['civicrm_group'])) {
if (empty($tables['civicrm_group_contact'])) {
$tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added' ";
}
if (empty($tables['civicrm_group_contact_cache'])) {
$tables['civicrm_group_contact_cache'] = " LEFT JOIN civicrm_group_contact_cache ON civicrm_group_contact_cache.contact_id = contact_a.id ";
}
}
// add group_contact and group table is subscription history is present
if (!empty($tables['civicrm_subscription_history']) && empty($tables['civicrm_group'])) {
$tables = array_merge(array('civicrm_group' => 1, 'civicrm_group_contact' => 1), $tables);
}
// to handle table dependencies of components
CRM_Core_Component::tableNames($tables);
// to handle table dependencies of hook injected tables
CRM_Contact_BAO_Query_Hook::singleton()->setTableDependency($tables);
//format the table list according to the weight
$info = CRM_Core_TableHierarchy::info();
foreach ($tables as $key => $value) {
$k = 99;
if (strpos($key, '-') !== FALSE) {
$keyArray = explode('-', $key);
$k = CRM_Utils_Array::value('civicrm_' . $keyArray[1], $info, 99);
} elseif (strpos($key, '_') !== FALSE) {
$keyArray = explode('_', $key);
if (is_numeric(array_pop($keyArray))) {
$k = CRM_Utils_Array::value(implode('_', $keyArray), $info, 99);
} else {
$k = CRM_Utils_Array::value($key, $info, 99);
}
} else {
$k = CRM_Utils_Array::value($key, $info, 99);
}
$tempTable[$k . ".{$key}"] = $key;
}
ksort($tempTable);
$newTables = array();
foreach ($tempTable as $key) {
$newTables[$key] = $tables[$key];
}
$tables = $newTables;
foreach ($tables as $name => $value) {
if (!$value) {
continue;
}
if (!empty($inner[$name])) {
$side = 'INNER';
} elseif (!empty($right[$name])) {
$side = 'RIGHT';
} else {
$side = 'LEFT';
}
if ($value != 1) {
// if there is already a join statement in value, use value itself
if (strpos($value, 'JOIN')) {
$from .= " {$value} ";
} else {
$from .= " {$side} JOIN {$name} ON ( {$value} ) ";
}
continue;
}
switch ($name) {
case 'civicrm_address':
if ($primaryLocation) {
$from .= " {$side} JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )";
} else {
//CRM-14263 further handling of address joins further down...
$from .= " {$side} JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) ";
}
continue;
case 'civicrm_phone':
$from .= " {$side} JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) ";
continue;
case 'civicrm_email':
$from .= " {$side} JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) ";
continue;
case 'civicrm_im':
$from .= " {$side} JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) ";
continue;
case 'im_provider':
$from .= " {$side} JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id) ";
$from .= " {$side} JOIN civicrm_option_group option_group_imProvider ON option_group_imProvider.name = 'instant_messenger_service'";
$from .= " {$side} JOIN civicrm_option_value im_provider ON (civicrm_im.provider_id = im_provider.value AND option_group_imProvider.id = im_provider.option_group_id)";
continue;
case 'civicrm_openid':
$from .= " {$side} JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id AND civicrm_openid.is_primary = 1 )";
continue;
case 'civicrm_worldregion':
$from .= " {$side} JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id ";
$from .= " {$side} JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ";
continue;
case 'civicrm_location_type':
$from .= " {$side} JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id ";
continue;
case 'civicrm_group':
$from .= " {$side} JOIN civicrm_group ON (civicrm_group.id = civicrm_group_contact.group_id OR civicrm_group.id = civicrm_group_contact_cache.group_id) ";
continue;
case 'civicrm_group_contact':
$from .= " {$side} JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id ";
continue;
case 'civicrm_group_contact_cache':
$from .= " {$side} JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id ";
continue;
case 'civicrm_activity':
case 'civicrm_activity_tag':
case 'activity_type':
case 'activity_status':
case 'parent_id':
case 'civicrm_activity_contact':
case 'source_contact':
$from .= CRM_Activity_BAO_Query::from($name, $mode, $side);
continue;
case 'civicrm_entity_tag':
$from .= " {$side} JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_entity_tag.entity_id = contact_a.id ) ";
continue;
case 'civicrm_note':
$from .= " {$side} JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND\n contact_a.id = civicrm_note.entity_id ) ";
continue;
case 'civicrm_subscription_history':
$from .= " {$side} JOIN civicrm_subscription_history\n ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id\n AND civicrm_group_contact.group_id = civicrm_subscription_history.group_id";
continue;
case 'civicrm_relationship':
if (self::$_relType == 'reciprocal') {
if (self::$_relationshipTempTable) {
// we have a temptable to join on
$tbl = self::$_relationshipTempTable;
$from .= " INNER JOIN {$tbl} civicrm_relationship ON civicrm_relationship.contact_id = contact_a.id";
} else {
$from .= " {$side} JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id)";
$from .= " {$side} JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.contact_id_b = contact_b.id)";
}
} elseif (self::$_relType == 'b') {
$from .= " {$side} JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id )";
$from .= " {$side} JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )";
} else {
$from .= " {$side} JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id )";
$from .= " {$side} JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )";
}
continue;
case 'civicrm_log':
$from .= " INNER JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')";
$from .= " INNER JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id)";
continue;
case 'civicrm_tag':
$from .= " {$side} JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id ";
continue;
case 'civicrm_grant':
$from .= CRM_Grant_BAO_Query::from($name, $mode, $side);
continue;
case 'civicrm_website':
$from .= " {$side} JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id ";
continue;
default:
$locationTypeName = '';
if (strpos($name, '-address') != 0) {
$locationTypeName = 'address';
} elseif (strpos($name, '-phone') != 0) {
$locationTypeName = 'phone';
} elseif (strpos($name, '-email') != 0) {
$locationTypeName = 'email';
} elseif (strpos($name, '-im') != 0) {
$locationTypeName = 'im';
} elseif (strpos($name, '-openid') != 0) {
$locationTypeName = 'openid';
}
if ($locationTypeName) {
//we have a join on an location table - possibly in conjunction with search builder - CRM-14263
$parts = explode('-', $name);
$locationID = array_search($parts[0], CRM_Core_BAO_Address::buildOptions('location_type_id', 'get', array('name' => $parts[0])));
$from .= " {$side} JOIN civicrm_{$locationTypeName} `{$name}` ON ( contact_a.id = `{$name}`.contact_id ) and `{$name}`.location_type_id = {$locationID} ";
} else {
$from .= CRM_Core_Component::from($name, $mode, $side);
}
$from .= CRM_Contact_BAO_Query_Hook::singleton()->buildSearchfrom($name, $mode, $side);
continue;
}
}
return $from;
}
/**
* Get options for a given contact field.
*
* @see CRM_Core_DAO::buildOptions
*
* TODO: Should we always assume chainselect? What fn should be responsible for controlling that flow?
* TODO: In context of chainselect, what to return if e.g. a country has no states?
*
* @param string $fieldName
* @param string $context
* @see CRM_Core_DAO::buildOptionsContext
* @param array $props
* whatever is known about this dao object.
*
* @return array|bool
*/
public static function buildOptions($fieldName, $context = NULL, $props = array())
{
$params = array();
// Special logic for fields whose options depend on context or properties
switch ($fieldName) {
case 'contact_sub_type':
if (!empty($props['contact_type'])) {
$params['condition'] = "parent_id = (SELECT id FROM civicrm_contact_type WHERE name='{$props['contact_type']}')";
}
break;
case 'contact_type':
if ($context == 'search') {
// CRM-15495 - EntityRef filters and basic search forms expect this format
// FIXME: Search builder does not
return CRM_Contact_BAO_ContactType::getSelectElements();
}
break;
// The contact api supports some related entities so we'll honor that by fetching their options
// The contact api supports some related entities so we'll honor that by fetching their options
case 'group_id':
case 'group':
return CRM_Contact_BAO_GroupContact::buildOptions('group_id', $context, $props);
case 'tag_id':
case 'tag':
$props['entity_table'] = 'civicrm_contact';
return CRM_Core_BAO_EntityTag::buildOptions('tag_id', $context, $props);
case 'state_province_id':
case 'state_province':
case 'state_province_name':
case 'country_id':
case 'country':
case 'county_id':
case 'worldregion':
case 'worldregion_id':
return CRM_Core_BAO_Address::buildOptions($fieldName, 'get', $props);
}
return CRM_Core_PseudoConstant::get(__CLASS__, $fieldName, $params, $context);
}
