/**
  * 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
  *
  * @return string the from clause
  * @access public
  * @static
  */
 static function fromClause(&$tables, $inner = NULL, $right = NULL, $primaryLocation = TRUE, $mode = 1)
 {
     $from = ' FROM civicrm_contact contact_a';
     if (empty($tables)) {
         return $from;
     }
     if (CRM_Utils_Array::value('civicrm_worldregion', $tables)) {
         $tables = array_merge(array('civicrm_country' => 1), $tables);
     }
     if ((CRM_Utils_Array::value('civicrm_state_province', $tables) || CRM_Utils_Array::value('civicrm_country', $tables) || CRM_Utils_Array::value('civicrm_county', $tables)) && !CRM_Utils_Array::value('civicrm_address', $tables)) {
         $tables = array_merge(array('civicrm_address' => 1), $tables);
     }
     // add group_contact table if group table is present
     if (CRM_Utils_Array::value('civicrm_group', $tables) && !CRM_Utils_Array::value('civicrm_group_contact', $tables)) {
         $tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added'";
     }
     // add group_contact and group table is subscription history is present
     if (CRM_Utils_Array::value('civicrm_subscription_history', $tables) && !CRM_Utils_Array::value('civicrm_group', $tables)) {
         $tables = array_merge(array('civicrm_group' => 1, 'civicrm_group_contact' => 1), $tables);
     }
     // to handle table dependencies of components
     CRM_Core_Component::tableNames($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 (CRM_Utils_Array::value($name, $inner)) {
             $side = 'INNER';
         } elseif (CRM_Utils_Array::value($name, $right)) {
             $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 {
                     $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_state_province':
                 $from .= " {$side} JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id ";
                 continue;
             case 'civicrm_country':
                 $from .= " {$side} JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id ";
                 continue;
             case 'civicrm_worldregion':
                 $from .= " {$side} JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ";
                 continue;
             case 'civicrm_county':
                 $from .= " {$side} JOIN civicrm_county ON civicrm_address.county_id = civicrm_county.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 ";
                 continue;
             case 'civicrm_group_contact':
                 $from .= " {$side} JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id ";
                 continue;
             case 'civicrm_activity':
             case 'civicrm_activity_tag':
             case 'activity_type':
             case 'activity_status':
             case 'civicrm_activity_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 'individual_prefix':
                 $from .= " {$side} JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix')";
                 $from .= " {$side} JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id ) ";
                 continue;
             case 'individual_suffix':
                 $from .= " {$side} JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix')";
                 $from .= " {$side} JOIN civicrm_option_value individual_suffix ON (contact_a.suffix_id = individual_suffix.value AND option_group_suffix.id = individual_suffix.option_group_id ) ";
                 continue;
             case 'gender':
                 $from .= " {$side} JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender')";
                 $from .= " {$side} JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id) ";
                 continue;
             case 'civicrm_relationship':
                 if (self::$_relType == 'reciprocal') {
                     $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 .= " {$side} JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')";
                 $from .= " {$side} 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_task_status':
                 $from .= " {$side} JOIN civicrm_task_status ON ( civicrm_task_status.responsible_entity_table = 'civicrm_contact'\n                                                          AND contact_a.id = civicrm_task_status.responsible_entity_id )";
                 continue;
             case 'civicrm_grant':
                 $from .= CRM_Grant_BAO_Query::from($name, $mode, $side);
                 continue;
                 //build fromClause for email greeting, postal greeting, addressee CRM-4575
             //build fromClause for email greeting, postal greeting, addressee CRM-4575
             case 'email_greeting':
                 $from .= " {$side} JOIN civicrm_option_group option_group_email_greeting ON (option_group_email_greeting.name = 'email_greeting')";
                 $from .= " {$side} 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 ) ";
                 continue;
             case 'postal_greeting':
                 $from .= " {$side} JOIN civicrm_option_group option_group_postal_greeting ON (option_group_postal_greeting.name = 'postal_greeting')";
                 $from .= " {$side} 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 ) ";
                 continue;
             case 'addressee':
                 $from .= " {$side} JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee')";
                 $from .= " {$side} JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id ) ";
                 continue;
             case 'civicrm_website':
                 $from .= " {$side} JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id ";
                 continue;
             default:
                 $from .= CRM_Core_Component::from($name, $mode, $side);
                 continue;
         }
     }
     return $from;
 }
Esempio n. 2
0
 /**
  * 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;
 }