static function getContactList(&$config)
{
require_once 'CRM/Core/BAO/Preferences.php';
$name = CRM_Utils_Type::escape($_GET['s'], 'String');
$limit = '10';
$list = array_keys(CRM_Core_BAO_Preferences::valueOptions('contact_autocomplete_options'), '1');
$select = array('sort_name');
$where = '';
$from = array();
foreach ($list as $value) {
$suffix = substr($value, 0, 2) . substr($value, -1);
switch ($value) {
case 'street_address':
case 'city':
$selectText = $value;
$value = "address";
$suffix = 'sts';
case 'phone':
case 'email':
$select[] = $value == 'address' ? $selectText : $value;
$from[$value] = "LEFT JOIN civicrm_{$value} {$suffix} ON ( cc.id = {$suffix}.contact_id AND {$suffix}.is_primary = 1 ) ";
break;
case 'country':
case 'state_province':
$select[] = "{$suffix}.name";
if (!in_array('address', $from)) {
$from['address'] = 'LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1) ';
}
$from[$value] = " LEFT JOIN civicrm_{$value} {$suffix} ON ( sts.{$value}_id = {$suffix}.id ) ";
break;
}
}
$select = implode(', ', $select);
$from = implode(' ', $from);
if (CRM_Utils_Array::value('limit', $_GET)) {
$limit = CRM_Utils_Type::escape($_GET['limit'], 'Positive');
}
// add acl clause here
require_once 'CRM/Contact/BAO/Contact/Permission.php';
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc');
if ($aclWhere) {
$where .= " AND {$aclWhere} ";
}
$query = "\nSELECT DISTINCT(cc.id) as id, CONCAT_WS( ' :: ', {$select} ) as data\nFROM civicrm_contact cc {$from}\n{$aclFrom}\nWHERE sort_name LIKE '%{$name}%' {$where} \nORDER BY sort_name\nLIMIT 0, {$limit}\n";
// send query to hook to be modified if needed
require_once 'CRM/Utils/Hook.php';
CRM_Utils_Hook::contactListQuery($query, $name, CRM_Utils_Array::value('context', $_GET), CRM_Utils_Array::value('id', $_GET));
$dao = CRM_Core_DAO::executeQuery($query);
$contactList = null;
while ($dao->fetch()) {
echo $contactList = "{$dao->data}|{$dao->id}\n";
}
exit;
}
function buildACLClause($tableAlias = 'contact')
{
list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
}
public static function getContactPhone()
{
$queryString = NULL;
//check for mobile type
$phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name');
$mobileType = CRM_Utils_Array::value('Mobile', $phoneTypes);
$name = CRM_Utils_Array::value('name', $_GET);
if ($name) {
$name = CRM_Utils_Type::escape($name, 'String');
$queryString = " ( cc.sort_name LIKE '%{$name}%' OR cp.phone LIKE '%{$name}%' ) ";
} else {
$cid = CRM_Utils_Array::value('cid', $_GET);
if ($cid) {
//check cid for integer
$contIDS = explode(',', $cid);
foreach ($contIDS as $contID) {
CRM_Utils_Type::escape($contID, 'Integer');
}
$queryString = " cc.id IN ( {$cid} )";
}
}
if ($queryString) {
$offset = CRM_Utils_Array::value('offset', $_GET, 0);
$rowCount = CRM_Utils_Array::value('rowcount', $_GET, 20);
$offset = CRM_Utils_Type::escape($offset, 'Int');
$rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
// add acl clause here
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc');
if ($aclWhere) {
$aclWhere = " AND {$aclWhere}";
}
$query = "\nSELECT sort_name name, cp.phone, cc.id\nFROM civicrm_phone cp INNER JOIN civicrm_contact cc ON cc.id = cp.contact_id\n {$aclFrom}\nWHERE cc.is_deceased = 0 AND cc.do_not_sms = 0 AND cp.phone_type_id = {$mobileType} AND {$queryString}\n {$aclWhere}\nLIMIT {$offset}, {$rowCount}\n";
// send query to hook to be modified if needed
CRM_Utils_Hook::contactListQuery($query, $name, CRM_Utils_Request::retrieve('context', 'String', CRM_Core_DAO::$_nullObject), CRM_Utils_Request::retrieve('cid', 'Positive', CRM_Core_DAO::$_nullObject));
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$result[] = array('text' => '"' . $dao->name . '" (' . $dao->phone . ')', 'id' => CRM_Utils_Array::value('id', $_GET) ? "{$dao->id}::{$dao->phone}" : '"' . $dao->name . '" <' . $dao->phone . '>');
}
}
if ($result) {
CRM_Utils_JSON::output($result);
}
CRM_Utils_System::civiExit();
}
/**
* note that $job_id is used only as a variable in the temp table construction
* and does not play a role in the queries generated
* @param int $job_id
* (misnomer) a nonce value used to name temporary tables.
* @param int $mailing_id
* @param bool $storeRecipients
* @param bool $dedupeEmail
* @param null $mode
*
* @return CRM_Mailing_Event_BAO_Queue|string
*/
public static function getRecipients($job_id, $mailing_id = NULL, $storeRecipients = FALSE, $dedupeEmail = FALSE, $mode = NULL)
{
$mailingGroup = new CRM_Mailing_DAO_MailingGroup();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$job = CRM_Mailing_BAO_MailingJob::getTableName();
$mg = CRM_Mailing_DAO_MailingGroup::getTableName();
$eq = CRM_Mailing_Event_DAO_Queue::getTableName();
$email = CRM_Core_DAO_Email::getTableName();
if ($mode == 'sms') {
$phone = CRM_Core_DAO_Phone::getTableName();
}
$contact = CRM_Contact_DAO_Contact::getTableName();
$group = CRM_Contact_DAO_Group::getTableName();
$g2contact = CRM_Contact_DAO_GroupContact::getTableName();
$m = new CRM_Mailing_DAO_Mailing();
$m->id = $mailing_id;
$m->find(TRUE);
$email_selection_method = $m->email_selection_method;
$location_type_id = $m->location_type_id;
// Note: When determining the ORDER that results are returned, it's
// the record that comes last that counts. That's because we are
// INSERT'ing INTO a table with a primary id so that last record
// over writes any previous record.
switch ($email_selection_method) {
case 'location-exclude':
$location_filter = "({$email}.location_type_id != {$location_type_id})";
// If there is more than one email that doesn't match the location,
// prefer the one marked is_bulkmail, followed by is_primary.
$order_by = "ORDER BY {$email}.is_bulkmail, {$email}.is_primary";
break;
case 'location-only':
$location_filter = "({$email}.location_type_id = {$location_type_id})";
// If there is more than one email of the desired location, prefer
// the one marked is_bulkmail, followed by is_primary.
$order_by = "ORDER BY {$email}.is_bulkmail, {$email}.is_primary";
break;
case 'location-prefer':
$location_filter = "({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1 OR {$email}.location_type_id = {$location_type_id})";
// ORDER BY is more complicated because we have to set an arbitrary
// order that prefers the location that we want. We do that using
// the FIELD function. For more info, see:
// https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field
// We assign the location type we want the value "1" by putting it
// in the first position after we name the field. All other location
// types are left out, so they will be assigned the value 0. That
// means, they will all be equally tied for first place, with our
// location being last.
$order_by = "ORDER BY FIELD({$email}.location_type_id, {$location_type_id}), {$email}.is_bulkmail, {$email}.is_primary";
break;
case 'automatic':
// fall through to default
// fall through to default
default:
$location_filter = "({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)";
$order_by = "ORDER BY {$email}.is_bulkmail";
}
/* Create a temp table for contact exclusion */
$mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id}\n (contact_id int primary key)\n ENGINE=HEAP");
/* Add all the members of groups excluded from this mailing to the temp
* table */
$excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubGroup);
/* Add all unsubscribe members of base group from this mailing to the temp
* table */
$unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'";
$mailingGroup->query($unSubscribeBaseGroup);
/* Add all the (intended) recipients of an excluded prior mailing to
* the temp table */
$excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubMailing);
// get all the saved searches AND hierarchical groups
// and load them in the cache
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$groupDAO = CRM_Core_DAO::executeQuery($sql);
while ($groupDAO->fetch()) {
if ($groupDAO->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($groupDAO);
}
$smartGroupExclude = "\nINSERT IGNORE INTO X_{$job_id} (contact_id)\nSELECT c.contact_id\nFROM civicrm_group_contact_cache c\nWHERE c.group_id = {$groupDAO->id}\n";
$mailingGroup->query($smartGroupExclude);
}
$tempColumn = 'email_id';
if ($mode == 'sms') {
$tempColumn = 'phone_id';
}
/* Get all the group contacts we want to include */
$mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id}\n ({$tempColumn} int, contact_id int primary key)\n ENGINE=HEAP");
/* Get the group contacts, but only those which are not in the
* exclusion temp table */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.email IS NOT NULL\n AND {$email}.email != ''\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}";
if ($mode == 'sms') {
$phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name');
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$phone}.phone IS NOT NULL\n AND {$phone}.phone != ''\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
/* Query prior mailings */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}";
if ($mode == 'sms') {
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$groupDAO = CRM_Core_DAO::executeQuery($sql);
while ($groupDAO->fetch()) {
if ($groupDAO->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($groupDAO);
}
$smartGroupInclude = "\nREPLACE INTO I_{$job_id} (email_id, contact_id)\nSELECT civicrm_email.id as email_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_email ON civicrm_email.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_email = 0\n AND c.is_opt_out = 0\n AND c.is_deceased <> 1\n AND {$location_filter}\n AND civicrm_email.on_hold = 0\n AND X_{$job_id}.contact_id IS null\n{$order_by}\n";
if ($mode == 'sms') {
$smartGroupInclude = "\nREPLACE INTO I_{$job_id} (phone_id, contact_id)\nSELECT p.id as phone_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_phone p ON p.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_sms = 0\n AND c.is_opt_out = 0\n AND c.is_deceased <> 1\n AND p.phone_type_id = {$phoneTypes['Mobile']}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($smartGroupInclude);
}
/**
* Construct the filtered search queries
*/
$query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id);
$query = "REPLACE INTO I_{$job_id} ({$tempColumn}, contact_id)\n {$customSQL}";
$mailingGroup->query($query);
}
/* Get the emails with only location override */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}";
if ($mode == "sms") {
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
$eq = new CRM_Mailing_Event_BAO_Queue();
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause();
$aclWhere = $aclWhere ? "WHERE {$aclWhere}" : '';
if ($storeRecipients && $mailing_id) {
$sql = "\nDELETE\nFROM civicrm_mailing_recipients\nWHERE mailing_id = %1\n";
$params = array(1 => array($mailing_id, 'Integer'));
CRM_Core_DAO::executeQuery($sql, $params);
// CRM-3975
$groupBy = $groupJoin = '';
if ($dedupeEmail) {
$groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id";
$groupBy = " GROUP BY e.email ";
}
$sql = "\nINSERT INTO civicrm_mailing_recipients ( mailing_id, contact_id, {$tempColumn} )\nSELECT %1, i.contact_id, i.{$tempColumn}\nFROM civicrm_contact contact_a\nINNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$groupJoin}\n {$aclFrom}\n {$aclWhere}\n {$groupBy}\nORDER BY i.contact_id, i.{$tempColumn}\n";
CRM_Core_DAO::executeQuery($sql, $params);
// if we need to add all emails marked bulk, do it as a post filter
// on the mailing recipients table
if (CRM_Core_BAO_Email::isMultipleBulkMail()) {
self::addMultipleEmails($mailing_id);
}
}
/* Delete the temp table */
$mailingGroup->reset();
$mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}");
$mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}");
return $eq;
}
/**
* Old Contact quick search api.
*
* @deprecated
*
* @param array $params
*
* @return array
* @throws \API_Exception
*/
function civicrm_api3_contact_getquick($params)
{
$name = CRM_Utils_Type::escape(CRM_Utils_Array::value('name', $params), 'String');
$table_name = CRM_Utils_String::munge($params['table_name']);
// get the autocomplete options from settings
$acpref = explode(CRM_Core_DAO::VALUE_SEPARATOR, CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, 'contact_autocomplete_options'));
// get the option values for contact autocomplete
$acOptions = CRM_Core_OptionGroup::values('contact_autocomplete_options', FALSE, FALSE, FALSE, NULL, 'name');
$list = array();
foreach ($acpref as $value) {
if ($value && !empty($acOptions[$value])) {
$list[$value] = $acOptions[$value];
}
}
// If we are doing quicksearch by a field other than name, make sure that field is added to results
if (!empty($params['field_name'])) {
$field_name = CRM_Utils_String::munge($params['field_name']);
// Unique name contact_id = id
if ($field_name == 'contact_id') {
$field_name = 'id';
}
// phone_numeric should be phone
$searchField = str_replace('_numeric', '', $field_name);
if (!in_array($searchField, $list)) {
$list[] = $searchField;
}
} else {
// Set field name to first name for exact match checking.
$field_name = 'sort_name';
}
$select = $actualSelectElements = array('sort_name');
$where = '';
$from = array();
foreach ($list as $value) {
$suffix = substr($value, 0, 2) . substr($value, -1);
switch ($value) {
case 'street_address':
case 'city':
case 'postal_code':
$selectText = $value;
$value = "address";
$suffix = 'sts';
case 'phone':
case 'email':
$actualSelectElements[] = $select[] = $value == 'address' ? $selectText : $value;
if ($value == 'phone') {
$actualSelectElements[] = $select[] = 'phone_ext';
}
$from[$value] = "LEFT JOIN civicrm_{$value} {$suffix} ON ( cc.id = {$suffix}.contact_id AND {$suffix}.is_primary = 1 ) ";
break;
case 'country':
case 'state_province':
$select[] = "{$suffix}.name as {$value}";
$actualSelectElements[] = "{$suffix}.name";
if (!in_array('address', $from)) {
$from['address'] = 'LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1) ';
}
$from[$value] = " LEFT JOIN civicrm_{$value} {$suffix} ON ( sts.{$value}_id = {$suffix}.id ) ";
break;
default:
if ($value != 'id') {
$suffix = 'cc';
if (!empty($params['field_name']) && $params['field_name'] == 'value') {
$suffix = CRM_Utils_String::munge(CRM_Utils_Array::value('table_name', $params, 'cc'));
}
$actualSelectElements[] = $select[] = $suffix . '.' . $value;
}
break;
}
}
$config = CRM_Core_Config::singleton();
$as = $select;
$select = implode(', ', $select);
if (!empty($select)) {
$select = ", {$select}";
}
$actualSelectElements = implode(', ', $actualSelectElements);
$selectAliases = $from;
unset($selectAliases['address']);
$selectAliases = implode(', ', array_keys($selectAliases));
if (!empty($selectAliases)) {
$selectAliases = ", {$selectAliases}";
}
$from = implode(' ', $from);
$limit = (int) CRM_Utils_Array::value('limit', $params);
$limit = $limit > 0 ? $limit : Civi::settings()->get('search_autocomplete_count');
// add acl clause here
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc');
if ($aclWhere) {
$where .= " AND {$aclWhere} ";
}
if (!empty($params['org'])) {
$where .= " AND contact_type = \"Organization\"";
// CRM-7157, hack: get current employer details when
// employee_id is present.
$currEmpDetails = array();
if (!empty($params['employee_id'])) {
if ($currentEmployer = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', (int) $params['employee_id'], 'employer_id')) {
if ($config->includeWildCardInName) {
$strSearch = "%{$name}%";
} else {
$strSearch = "{$name}%";
}
// get current employer details
$dao = CRM_Core_DAO::executeQuery("SELECT cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data, sort_name\n FROM civicrm_contact cc {$from} WHERE cc.contact_type = \"Organization\" AND cc.id = {$currentEmployer} AND cc.sort_name LIKE '{$strSearch}'");
if ($dao->fetch()) {
$currEmpDetails = array('id' => $dao->id, 'data' => $dao->data);
}
}
}
}
if (!empty($params['contact_sub_type'])) {
$contactSubType = CRM_Utils_Type::escape($params['contact_sub_type'], 'String');
$where .= " AND cc.contact_sub_type = '{$contactSubType}'";
}
if (!empty($params['contact_type'])) {
$contactType = CRM_Utils_Type::escape($params['contact_type'], 'String');
$where .= " AND cc.contact_type LIKE '{$contactType}'";
}
// Set default for current_employer or return contact with particular id
if (!empty($params['id'])) {
$where .= " AND cc.id = " . (int) $params['id'];
}
if (!empty($params['cid'])) {
$where .= " AND cc.id <> " . (int) $params['cid'];
}
// Contact's based of relationhip type
$relType = NULL;
if (!empty($params['rel'])) {
$relation = explode('_', CRM_Utils_Array::value('rel', $params));
$relType = CRM_Utils_Type::escape($relation[0], 'Integer');
$rel = CRM_Utils_Type::escape($relation[2], 'String');
}
if ($config->includeWildCardInName) {
$strSearch = "%{$name}%";
} else {
$strSearch = "{$name}%";
}
$includeEmailFrom = $includeNickName = $exactIncludeNickName = '';
if ($config->includeNickNameInName) {
$includeNickName = " OR nick_name LIKE '{$strSearch}'";
$exactIncludeNickName = " OR nick_name LIKE '{$name}'";
}
//CRM-10687
if (!empty($params['field_name']) && !empty($params['table_name'])) {
$whereClause = " WHERE ( {$table_name}.{$field_name} LIKE '{$strSearch}') {$where}";
$exactWhereClause = " WHERE ( {$table_name}.{$field_name} = '{$name}') {$where}";
// Search by id should be exact
if ($field_name == 'id' || $field_name == 'external_identifier') {
$whereClause = $exactWhereClause;
}
} else {
$whereClause = " WHERE ( sort_name LIKE '{$strSearch}' {$includeNickName} ) {$where} ";
$exactWhereClause = " WHERE ( sort_name LIKE '{$name}' {$exactIncludeNickName} ) {$where} ";
if ($config->includeEmailInName) {
if (!in_array('email', $list)) {
$includeEmailFrom = "LEFT JOIN civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 )";
}
$emailWhere = " WHERE email LIKE '{$strSearch}'";
}
}
$additionalFrom = '';
if ($relType) {
$additionalFrom = "\n INNER JOIN civicrm_relationship_type r ON (\n r.id = {$relType}\n AND ( cc.contact_type = r.contact_type_{$rel} OR r.contact_type_{$rel} IS NULL )\n AND ( cc.contact_sub_type = r.contact_sub_type_{$rel} OR r.contact_sub_type_{$rel} IS NULL )\n )";
}
// check if only CMS users are requested
if (!empty($params['cmsuser'])) {
$additionalFrom = "\n INNER JOIN civicrm_uf_match um ON (um.contact_id=cc.id)\n ";
}
$orderByInner = $orderByOuter = "ORDER BY exactFirst";
if ($config->includeOrderByClause) {
$orderByInner = "ORDER BY exactFirst, sort_name";
$orderByOuter .= ", sort_name";
}
//CRM-5954
$query = "\n SELECT DISTINCT(id), data, sort_name {$selectAliases}, exactFirst\n FROM (\n ( SELECT IF({$table_name}.{$field_name} = '{$name}', 0, 1) as exactFirst, cc.id as id, CONCAT_WS( ' :: ',\n {$actualSelectElements} )\n as data\n {$select}\n FROM civicrm_contact cc {$from}\n {$aclFrom}\n {$additionalFrom}\n {$whereClause}\n {$orderByInner}\n LIMIT 0, {$limit} )\n ";
if (!empty($emailWhere)) {
$query .= "\n UNION (\n SELECT IF({$table_name}.{$field_name} = '{$name}', 0, 1) as exactFirst, cc.id as id, CONCAT_WS( ' :: ',\n {$actualSelectElements} )\n as data\n {$select}\n FROM civicrm_contact cc {$from}\n {$aclFrom}\n {$additionalFrom} {$includeEmailFrom}\n {$emailWhere} AND cc.is_deleted = 0 " . ($aclWhere ? " AND {$aclWhere} " : '') . "\n {$orderByInner}\n LIMIT 0, {$limit}\n )\n ";
}
$query .= ") t\n {$orderByOuter}\n LIMIT 0, {$limit}\n ";
// send query to hook to be modified if needed
CRM_Utils_Hook::contactListQuery($query, $name, empty($params['context']) ? NULL : CRM_Utils_Type::escape($params['context'], 'String'), empty($params['id']) ? NULL : $params['id']);
$dao = CRM_Core_DAO::executeQuery($query);
$contactList = array();
$listCurrentEmployer = TRUE;
while ($dao->fetch()) {
$t = array('id' => $dao->id);
foreach ($as as $k) {
$t[$k] = isset($dao->{$k}) ? $dao->{$k} : '';
}
$t['data'] = $dao->data;
$contactList[] = $t;
if (!empty($params['org']) && !empty($currEmpDetails) && $dao->id == $currEmpDetails['id']) {
$listCurrentEmployer = FALSE;
}
}
//return organization name if doesn't exist in db
if (empty($contactList)) {
if (!empty($params['org'])) {
if ($listCurrentEmployer && !empty($currEmpDetails)) {
$contactList = array(array('data' => $currEmpDetails['data'], 'id' => $currEmpDetails['id']));
} else {
$contactList = array(array('data' => $name, 'id' => $name));
}
}
}
return civicrm_api3_create_success($contactList, $params, 'Contact', 'getquick');
}
/**
* Return the SQL query for getting only the interesting results out of the dedupe table.
*
* @$checkPermission boolean $params a flag to indicate if permission should be considered.
* default is to always check permissioning but public pages for example might not want
* permission to be checked for anonymous users. Refer CRM-6211. We might be beaking
* Multi-Site dedupe for public pages.
*
* @param bool $checkPermission
*
* @return string
*/
public function thresholdQuery($checkPermission = TRUE)
{
$this->_aclFrom = '';
// CRM-6603: anonymous dupechecks side-step ACLs
$this->_aclWhere = ' AND is_deleted = 0 ';
if ($this->params && !$this->noRules) {
if ($checkPermission) {
list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('civicrm_contact');
$this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : '';
}
$query = "SELECT dedupe.id1 as id\n FROM dedupe JOIN civicrm_contact ON dedupe.id1 = civicrm_contact.id {$this->_aclFrom}\n WHERE contact_type = '{$this->contact_type}' {$this->_aclWhere}\n AND weight >= {$this->threshold}";
} else {
$this->_aclWhere = ' AND c1.is_deleted = 0 AND c2.is_deleted = 0';
if ($checkPermission) {
list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(array('c1', 'c2'));
$this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : '';
}
$query = "SELECT dedupe.id1, dedupe.id2, dedupe.weight\n FROM dedupe JOIN civicrm_contact c1 ON dedupe.id1 = c1.id\n JOIN civicrm_contact c2 ON dedupe.id2 = c2.id {$this->_aclFrom}\n LEFT JOIN civicrm_dedupe_exception exc ON dedupe.id1 = exc.contact_id1 AND dedupe.id2 = exc.contact_id2\n WHERE c1.contact_type = '{$this->contact_type}' AND\n c2.contact_type = '{$this->contact_type}' {$this->_aclWhere}\n AND weight >= {$this->threshold} AND exc.contact_id1 IS NULL";
}
CRM_Utils_Hook::dupeQuery($this, 'threshold', $query);
return $query;
}
/**
* We are over-riding this because the current choice is NO acls or automatically adding contact.is_deleted
* which is a pain when left joining form another table
* @see CRM_Report_Form::buildACLClause($tableAlias)
*
* @param string $tableAlias
*
*/
function buildACLClause($tableAlias = 'contact_a') {
list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
if ($this->_skipACLContactDeletedClause && CRM_Core_Permission::check('access deleted contacts')) {
if (trim($this->_aclWhere) == "{$tableAlias}.is_deleted = 0") {
$this->_aclWhere = NULL;
}
else {
$this->_aclWhere = str_replace("AND {$tableAlias}.is_deleted = 0", '', $this->_aclWhere);
}
}
}
static function &getRecipients($job_id, $mailing_id = NULL, $offset = NULL, $limit = NULL, $storeRecipients = FALSE, $dedupeEmail = FALSE, $mode = NULL)
{
$mailingGroup = new CRM_Mailing_DAO_MailingGroup();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$job = CRM_Mailing_BAO_MailingJob::getTableName();
$mg = CRM_Mailing_DAO_MailingGroup::getTableName();
$eq = CRM_Mailing_Event_DAO_Queue::getTableName();
$ed = CRM_Mailing_Event_DAO_Delivered::getTableName();
$eb = CRM_Mailing_Event_DAO_Bounce::getTableName();
$email = CRM_Core_DAO_Email::getTableName();
if ($mode == 'sms') {
$phone = CRM_Core_DAO_Phone::getTableName();
}
$contact = CRM_Contact_DAO_Contact::getTableName();
$group = CRM_Contact_DAO_Group::getTableName();
$g2contact = CRM_Contact_DAO_GroupContact::getTableName();
/* Create a temp table for contact exclusion */
$mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id}\n (contact_id int primary key)\n ENGINE=HEAP");
/* Add all the members of groups excluded from this mailing to the temp
* table */
$excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubGroup);
/* Add all unsubscribe members of base group from this mailing to the temp
* table */
$unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'";
$mailingGroup->query($unSubscribeBaseGroup);
/* Add all the (intended) recipients of an excluded prior mailing to
* the temp table */
$excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubMailing);
// get all the saved searches AND hierarchical groups
// and load them in the cache
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$groupDAO = CRM_Core_DAO::executeQuery($sql);
while ($groupDAO->fetch()) {
if ($groupDAO->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($groupDAO);
}
$smartGroupExclude = "\nINSERT IGNORE INTO X_{$job_id} (contact_id)\nSELECT c.contact_id\nFROM civicrm_group_contact_cache c\nWHERE c.group_id = {$groupDAO->id}\n";
$mailingGroup->query($smartGroupExclude);
}
$tempColumn = 'email_id';
if ($mode == 'sms') {
$tempColumn = 'phone_id';
}
/* Get all the group contacts we want to include */
$mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id}\n ({$tempColumn} int, contact_id int primary key)\n ENGINE=HEAP");
/* Get the group contacts, but only those which are not in the
* exclusion temp table */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.email IS NOT NULL\n AND {$email}.email != ''\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail";
if ($mode == 'sms') {
$phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name');
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$phone}.phone IS NOT NULL\n AND {$phone}.phone != ''\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
/* Query prior mailings */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail";
if ($mode == 'sms') {
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$groupDAO = CRM_Core_DAO::executeQuery($sql);
while ($groupDAO->fetch()) {
if ($groupDAO->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($groupDAO);
}
$smartGroupInclude = "\nINSERT IGNORE INTO I_{$job_id} (email_id, contact_id)\nSELECT e.id as email_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_email e ON e.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_email = 0\n AND c.is_opt_out = 0\n AND c.is_deceased = 0\n AND (e.is_bulkmail = 1 OR e.is_primary = 1)\n AND e.on_hold = 0\n AND X_{$job_id}.contact_id IS null\nORDER BY e.is_bulkmail\n";
if ($mode == 'sms') {
$smartGroupInclude = "\nINSERT IGNORE INTO I_{$job_id} (phone_id, contact_id)\nSELECT p.id as phone_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_phone p ON p.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_sms = 0\n AND c.is_opt_out = 0\n AND c.is_deceased = 0\n AND p.phone_type_id = {$phoneTypes['Mobile']}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($smartGroupInclude);
}
/**
* Construct the filtered search queries
*/
$query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id);
$query = "REPLACE INTO I_{$job_id} ({$tempColumn}, contact_id)\n {$customSQL}";
$mailingGroup->query($query);
}
/* Get the emails with only location override */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail";
if ($mode == "sms") {
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
$results = array();
$eq = new CRM_Mailing_Event_BAO_Queue();
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause();
$aclWhere = $aclWhere ? "WHERE {$aclWhere}" : '';
$limitString = NULL;
if ($limit && $offset !== NULL) {
$offset = CRM_Utils_Type::escape($offset, 'Int');
$limit = CRM_Utils_Type::escape($limit, 'Int');
$limitString = "LIMIT {$offset}, {$limit}";
}
if ($storeRecipients && $mailing_id) {
$sql = "\nDELETE\nFROM civicrm_mailing_recipients\nWHERE mailing_id = %1\n";
$params = array(1 => array($mailing_id, 'Integer'));
CRM_Core_DAO::executeQuery($sql, $params);
// CRM-3975
$groupBy = $groupJoin = '';
if ($dedupeEmail) {
$groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id";
$groupBy = " GROUP BY e.email ";
}
$sql = "\nINSERT INTO civicrm_mailing_recipients ( mailing_id, contact_id, {$tempColumn} )\nSELECT %1, i.contact_id, i.{$tempColumn}\nFROM civicrm_contact contact_a\nINNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$groupJoin}\n {$aclFrom}\n {$aclWhere}\n {$groupBy}\nORDER BY i.contact_id, i.{$tempColumn}\n";
CRM_Core_DAO::executeQuery($sql, $params);
// if we need to add all emails marked bulk, do it as a post filter
// on the mailing recipients table
if (CRM_Core_BAO_Email::isMultipleBulkMail()) {
self::addMultipleEmails($mailing_id);
}
}
/* Delete the temp table */
$mailingGroup->reset();
$mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}");
$mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}");
return $eq;
}
/**
* Function to get email address of a contact
*/
static function getContactEmail()
{
if (CRM_Utils_Array::value('contact_id', $_POST)) {
$contactID = CRM_Utils_Type::escape($_POST['contact_id'], 'Positive');
require_once 'CRM/Contact/BAO/Contact/Location.php';
list($displayName, $userEmail) = CRM_Contact_BAO_Contact_Location::getEmailDetails($contactID);
if ($userEmail) {
echo $userEmail;
}
} else {
$noemail = CRM_Utils_Array::value('noemail', $_GET);
if ($name = CRM_Utils_Array::value('name', $_GET)) {
$name = CRM_Utils_Type::escape($name, 'String');
if ($noemail) {
$queryString = " cc.sort_name LIKE '%{$name}%'";
} else {
$queryString = " ( cc.sort_name LIKE '%{$name}%' OR ce.email LIKE '%{$name}%' ) ";
}
} else {
$cid = CRM_Utils_Array::value('cid', $_GET);
$queryString = " cc.id IN ( {$cid} )";
}
// add acl clause here
require_once 'CRM/Contact/BAO/Contact/Permission.php';
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc');
if ($aclWhere) {
$aclWhere = " AND {$aclWhere}";
}
if ($noemail) {
$query = "\nSELECT sort_name name, cc.id\nFROM civicrm_contact cc \n {$aclFrom}\nWHERE {$queryString}\n {$aclWhere}\n";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$result[] = array('name' => $dao->name, 'id' => $dao->id);
}
} else {
$query = "\nSELECT sort_name name, ce.email, cc.id\nFROM civicrm_email ce INNER JOIN civicrm_contact cc ON cc.id = ce.contact_id\n {$aclFrom}\nWHERE ce.on_hold = 0 AND cc.is_deceased = 0 AND cc.do_not_email = 0 AND {$queryString}\n {$aclWhere}\n";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$result[] = array('name' => '"' . $dao->name . '" <' . $dao->email . '>', 'id' => CRM_Utils_Array::value('id', $_GET) ? "{$dao->id}::{$dao->email}" : '"' . $dao->name . '" <' . $dao->email . '>');
}
}
if ($result) {
echo json_encode($result);
}
}
exit;
}
function buildACLClause($tableAlias = 'contact_a')
{
require_once 'CRM/Contact/BAO/Contact/Permission.php';
list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
}
function &getRecipients($job_id, $includeDelivered = false, $mailing_id = null, $offset = NULL, $limit = NULL)
{
$mailingGroup = new CRM_Mailing_DAO_Group();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$job = CRM_Mailing_BAO_Job::getTableName();
$mg = CRM_Mailing_DAO_Group::getTableName();
$eq = CRM_Mailing_Event_DAO_Queue::getTableName();
$ed = CRM_Mailing_Event_DAO_Delivered::getTableName();
$eb = CRM_Mailing_Event_DAO_Bounce::getTableName();
$email = CRM_Core_DAO_Email::getTableName();
$contact = CRM_Contact_DAO_Contact::getTableName();
require_once 'CRM/Contact/DAO/Group.php';
$group = CRM_Contact_DAO_Group::getTableName();
$g2contact = CRM_Contact_DAO_GroupContact::getTableName();
/* Create a temp table for contact exclusion */
$mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id} \n (contact_id int primary key) \n ENGINE=HEAP");
/* Add all the members of groups excluded from this mailing to the temp
* table */
$excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubGroup);
/* Add all unsubscribe members of base group from this mailing to the temp
* table */
$unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'";
$mailingGroup->query($unSubscribeBaseGroup);
/* Add all the (intended) recipients of an excluded prior mailing to
* the temp table */
$excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubMailing);
$ss = new CRM_Core_DAO();
$ss->query("SELECT {$group}.saved_search_id as saved_search_id,\n {$group}.id as id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n WHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND {$group}.saved_search_id IS NOT null");
$whereTables = array();
while ($ss->fetch()) {
/* run the saved search query and dump result contacts into the temp
* table */
$tables = array($contact => 1);
$sql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ss->saved_search_id);
$sql = $sql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM {$g2contact} \n WHERE {$g2contact}.group_id = {$ss->id} AND {$g2contact}.status = 'Removed')";
$mailingGroup->query("INSERT IGNORE INTO X_{$job_id} (contact_id) {$sql}");
}
/* Get all the group contacts we want to include */
$mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id} \n (email_id int, contact_id int primary key)\n ENGINE=HEAP");
/* Get the group contacts, but only those which are not in the
* exclusion temp table */
/* Get the emails with no override */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n ({$mg}.group_type = 'Include' OR {$mg}.group_type = 'Base')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.email_id IS null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail";
$mailingGroup->query($query);
/* Query prior mailings */
$mailingGroup->query("REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include' OR {$mg}.group_type = 'Base')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail");
/* Construct the saved-search queries */
$ss->query("SELECT {$group}.saved_search_id as saved_search_id,\n {$group}.id as id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n AND {$mg}.entity_table = '{$group}'\n WHERE \n {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND {$group}.saved_search_id IS NOT null");
$whereTables = array();
while ($ss->fetch()) {
$tables = array($contact => 1, $location => 1, $email => 1);
list($from, $where) = CRM_Contact_BAO_SavedSearch::fromWhereEmail($ss->saved_search_id);
$where = trim($where);
if ($where) {
$where = " AND {$where} ";
}
$ssq = "INSERT IGNORE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n contact_a.id as contact_id \n {$from}\n LEFT JOIN X_{$job_id}\n ON contact_a.id = X_{$job_id}.contact_id\n WHERE \n contact_a.do_not_email = 0\n AND contact_a.is_opt_out = 0\n AND contact_a.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n {$where}\n AND contact_a.id NOT IN ( \n SELECT contact_id FROM {$g2contact} \n WHERE {$g2contact}.group_id = {$ss->id} AND {$g2contact}.status = 'Removed') \n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail";
$mailingGroup->query($ssq);
}
/**
* Construct the filtered search queries
*/
$query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n";
$dao = CRM_Core_DAO::executeQuery($query);
require_once 'CRM/Contact/BAO/SearchCustom.php';
while ($dao->fetch()) {
$customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id);
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n {$customSQL}";
$mailingGroup->query($query);
}
/* Get the emails with only location override */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.email_id is null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail";
$mailingGroup->query($query);
/* Get the emails with full override */
$mailingGroup->query("REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$g2contact}\n ON {$email}.id = {$g2contact}.email_id\n INNER JOIN {$contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.email_id IS NOT null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail");
$results = array();
$eq = new CRM_Mailing_Event_BAO_Queue();
require_once 'CRM/Contact/BAO/Contact/Permission.php';
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause();
$aclWhere = $aclWhere ? "WHERE {$aclWhere}" : '';
$limitString = null;
if ($limit && $offset !== null) {
$limitString = "LIMIT {$offset}, {$limit}";
}
$eq->query("SELECT i.contact_id, i.email_id \n FROM civicrm_contact contact_a\n INNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$aclFrom}\n {$aclWhere}\n ORDER BY i.contact_id, i.email_id\n {$limitString}");
/* Delete the temp table */
$mailingGroup->reset();
$mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}");
$mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}");
return $eq;
}
/**
* Return the SQL query for getting only the interesting results out of the dedupe table.
*/
function thresholdQuery()
{
require_once 'CRM/Contact/BAO/Contact/Permission.php';
if ($this->params && !$this->noRules) {
list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('civicrm_contact');
$this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : '';
$query = "SELECT dedupe.id\n FROM dedupe JOIN civicrm_contact USING (id) {$this->_aclFrom}\n WHERE contact_type = '{$this->contact_type}' {$this->_aclWhere}\n GROUP BY dedupe.id HAVING SUM(weight) >= {$this->threshold}\n ORDER BY SUM(weight) desc";
} else {
list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(array('c1', 'c2'));
$this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : '';
$query = "SELECT dedupe.id1, dedupe.id2, SUM(weight) as weight\n FROM dedupe JOIN civicrm_contact c1 ON dedupe.id1 = c1.id \n JOIN civicrm_contact c2 ON dedupe.id2 = c2.id {$this->_aclFrom}\n WHERE c1.contact_type = '{$this->contact_type}' AND \n c2.contact_type = '{$this->contact_type}' {$this->_aclWhere}\n GROUP BY dedupe.id1, dedupe.id2 HAVING SUM(weight) >= {$this->threshold}\n ORDER BY SUM(weight) desc";
}
return $query;
}
function civicrm_api3_contact_getquick($params)
{
civicrm_api3_verify_mandatory($params, NULL, array('name'));
$name = CRM_Utils_Array::value('name', $params);
// get the autocomplete options from settings
require_once 'CRM/Core/BAO/Setting.php';
$acpref = explode(CRM_Core_DAO::VALUE_SEPARATOR, CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, 'contact_autocomplete_options'));
// get the option values for contact autocomplete
$acOptions = CRM_Core_OptionGroup::values('contact_autocomplete_options', FALSE, FALSE, FALSE, NULL, 'name');
$list = array();
foreach ($acpref as $value) {
if ($value && CRM_Utils_Array::value($value, $acOptions)) {
$list[$value] = $acOptions[$value];
}
}
$select = $actualSelectElements = array('sort_name');
$where = '';
$from = array();
foreach ($list as $value) {
$suffix = substr($value, 0, 2) . substr($value, -1);
switch ($value) {
case 'street_address':
case 'city':
$selectText = $value;
$value = "address";
$suffix = 'sts';
case 'phone':
case 'email':
$actualSelectElements[] = $select[] = $value == 'address' ? $selectText : $value;
$from[$value] = "LEFT JOIN civicrm_{$value} {$suffix} ON ( cc.id = {$suffix}.contact_id AND {$suffix}.is_primary = 1 ) ";
break;
case 'country':
case 'state_province':
$select[] = "{$suffix}.name as {$value}";
$actualSelectElements[] = "{$suffix}.name";
if (!in_array('address', $from)) {
$from['address'] = 'LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1) ';
}
$from[$value] = " LEFT JOIN civicrm_{$value} {$suffix} ON ( sts.{$value}_id = {$suffix}.id ) ";
break;
}
}
$config = CRM_Core_Config::singleton();
$as = $select;
$select = implode(', ', $select);
if (!empty($select)) {
$select = ", {$select}";
}
$actualSelectElements = implode(', ', $actualSelectElements);
$selectAliases = $from;
unset($selectAliases['address']);
$selectAliases = implode(', ', array_keys($selectAliases));
if (!empty($selectAliases)) {
$selectAliases = ", {$selectAliases}";
}
$from = implode(' ', $from);
$limit = CRM_Utils_Array::value('limit', $params, 10);
// add acl clause here
require_once 'CRM/Contact/BAO/Contact/Permission.php';
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc');
if ($aclWhere) {
$where .= " AND {$aclWhere} ";
}
if (CRM_Utils_Array::value('org', $params)) {
$where .= " AND contact_type = \"Organization\"";
//set default for current_employer
if ($orgId = CRM_Utils_Array::value('id', $params)) {
$where .= " AND cc.id = {$orgId}";
}
// CRM-7157, hack: get current employer details when
// employee_id is present.
$currEmpDetails = array();
if (CRM_Utils_Array::value('employee_id', $params)) {
if ($currentEmployer = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', CRM_Utils_Array::value('employee_id', $params), 'employer_id')) {
if ($config->includeWildCardInName) {
$strSearch = "%{$name}%";
} else {
$strSearch = "{$name}%";
}
// get current employer details
$dao = CRM_Core_DAO::executeQuery("SELECT cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data, sort_name\n FROM civicrm_contact cc {$from} WHERE cc.contact_type = \"Organization\" AND cc.id = {$currentEmployer} AND cc.sort_name LIKE '{$strSearch}'");
if ($dao->fetch()) {
$currEmpDetails = array('id' => $dao->id, 'data' => $dao->data);
}
}
}
}
if (CRM_Utils_Array::value('cid', $params)) {
$where .= " AND cc.id <> {$params['cid']}";
}
//contact's based of relationhip type
$relType = NULL;
if (CRM_Utils_Array::value('rel', $params)) {
$relation = explode('_', CRM_Utils_Array::value('rel', $params));
$relType = CRM_Utils_Type::escape($relation[0], 'Integer');
$rel = CRM_Utils_Type::escape($relation[2], 'String');
}
if ($config->includeWildCardInName) {
$strSearch = "%{$name}%";
} else {
$strSearch = "{$name}%";
}
$includeEmailFrom = $includeNickName = $exactIncludeNickName = '';
if ($config->includeNickNameInName) {
$includeNickName = " OR nick_name LIKE '{$strSearch}'";
$exactIncludeNickName = " OR nick_name LIKE '{$name}'";
}
if ($config->includeEmailInName) {
if (!in_array('email', $list)) {
$includeEmailFrom = "LEFT JOIN civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 )";
}
$whereClause = " WHERE ( email LIKE '{$strSearch}' OR sort_name LIKE '{$strSearch}' {$includeNickName} ) {$where} ";
$exactWhereClause = " WHERE ( email LIKE '{$name}' OR sort_name LIKE '{$name}' {$exactIncludeNickName} ) {$where} ";
} else {
$whereClause = " WHERE ( sort_name LIKE '{$strSearch}' {$includeNickName} ) {$where} ";
$exactWhereClause = " WHERE ( sort_name LIKE '{$name}' {$exactIncludeNickName} ) {$where} ";
}
$additionalFrom = '';
if ($relType) {
$additionalFrom = "\n INNER JOIN civicrm_relationship_type r ON (\n r.id = {$relType}\n AND ( cc.contact_type = r.contact_type_{$rel} OR r.contact_type_{$rel} IS NULL )\n AND ( cc.contact_sub_type = r.contact_sub_type_{$rel} OR r.contact_sub_type_{$rel} IS NULL )\n )";
}
//CRM-5954
$query = "\n SELECT DISTINCT(id), data, sort_name {$selectAliases}\n FROM (\n ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data {$select}\n FROM civicrm_contact cc {$from}\n {$aclFrom}\n {$additionalFrom} {$includeEmailFrom}\n {$exactWhereClause}\n LIMIT 0, {$limit} )\n UNION\n ( SELECT 1 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data {$select}\n FROM civicrm_contact cc {$from}\n {$aclFrom}\n {$additionalFrom} {$includeEmailFrom}\n {$whereClause}\n ORDER BY sort_name\n LIMIT 0, {$limit} )\n) t\nORDER BY exactFirst, sort_name\nLIMIT 0, {$limit}\n ";
// send query to hook to be modified if needed
require_once 'CRM/Utils/Hook.php';
CRM_Utils_Hook::contactListQuery($query, $name, CRM_Utils_Array::value('context', $params), CRM_Utils_Array::value('id', $params));
$dao = CRM_Core_DAO::executeQuery($query);
$contactList = array();
$listCurrentEmployer = TRUE;
while ($dao->fetch()) {
$t = array('id' => $dao->id);
foreach ($as as $k) {
$t[$k] = $dao->{$k};
}
$t['data'] = $dao->data;
$contactList[] = $t;
if (CRM_Utils_Array::value('org', $params) && !empty($currEmpDetails) && $dao->id == $currEmpDetails['id']) {
$listCurrentEmployer = FALSE;
}
}
//return organization name if doesn't exist in db
if (empty($contactList)) {
if (CRM_Utils_Array::value('org', $params)) {
if ($listCurrentEmployer && !empty($currEmpDetails)) {
$contactList = array(array('data' => $currEmpDetails['data'], 'id' => $currEmpDetails['id']));
} else {
$contactList = array(array('data' => $name, 'id' => $name));
}
}
}
return civicrm_api3_create_success($contactList, $params);
}
