function from()
{
//define table name
$randomNum = md5(uniqid());
$this->_tableName = "civicrm_temp_custom_{$randomNum}";
//grab the contacts added in the date range first
$sql = "CREATE TEMPORARY TABLE dates_{$this->_tableName} ( id int primary key, date_added date ) ENGINE=HEAP";
if ($this->_debug > 0) {
print "-- Date range query: <pre>";
print "{$sql};";
print "</pre>";
}
CRM_Core_DAO::executeQuery($sql);
$startDate = CRM_Utils_Date::mysqlToIso(CRM_Utils_Date::processDate($this->_formValues['start_date']));
$endDateFix = NULL;
if (!empty($this->_formValues['end_date'])) {
$endDate = CRM_Utils_Date::mysqlToIso(CRM_Utils_Date::processDate($this->_formValues['end_date']));
# tack 11:59pm on to make search inclusive of the end date
$endDateFix = "AND date_added <= '" . substr($endDate, 0, 10) . " 23:59:00'";
}
$dateRange = "INSERT INTO dates_{$this->_tableName} ( id, date_added )\n SELECT\n civicrm_contact.id,\n min(civicrm_log.modified_date) AS date_added\n FROM\n civicrm_contact LEFT JOIN civicrm_log\n ON (civicrm_contact.id = civicrm_log.entity_id AND\n civicrm_log.entity_table = 'civicrm_contact')\n GROUP BY\n civicrm_contact.id\n HAVING\n date_added >= '{$startDate}' \n {$endDateFix}";
if ($this->_debug > 0) {
print "-- Date range query: <pre>";
print "{$dateRange};";
print "</pre>";
}
CRM_Core_DAO::executeQuery($dateRange, CRM_Core_DAO::$_nullArray);
// Only include groups in the search query of one or more Include OR Exclude groups has been selected.
// CRM-6356
if ($this->_groups) {
//block for Group search
$smartGroup = array();
require_once 'CRM/Contact/DAO/Group.php';
$group = new CRM_Contact_DAO_Group();
$group->is_active = 1;
$group->find();
while ($group->fetch()) {
$allGroups[] = $group->id;
if ($group->saved_search_id) {
$smartGroup[$group->saved_search_id] = $group->id;
}
}
$includedGroups = implode(',', $allGroups);
if (!empty($this->_includeGroups)) {
$iGroups = implode(',', $this->_includeGroups);
} else {
//if no group selected search for all groups
$iGroups = $includedGroups;
}
if (is_array($this->_excludeGroups)) {
$xGroups = implode(',', $this->_excludeGroups);
} else {
$xGroups = 0;
}
$sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
$sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
//used only when exclude group is selected
if ($xGroups != 0) {
$excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_group_contact.contact_id\n FROM civicrm_group_contact, dates_{$this->_tableName} AS d\n WHERE \n d.id = civicrm_group_contact.contact_id AND \n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN( {$xGroups})";
CRM_Core_DAO::executeQuery($excludeGroup, CRM_Core_DAO::$_nullArray);
//search for smart group contacts
foreach ($this->_excludeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssId = CRM_Utils_Array::key($values, $smartGroup);
$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
$smartSql = $smartSql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact \n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
$smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery, CRM_Core_DAO::$_nullArray);
}
}
}
$sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
$sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}\n ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n group_names varchar(64)) ENGINE=HEAP";
if ($this->_debug > 0) {
print "-- Include groups query: <pre>";
print "{$sql};";
print "</pre>";
}
CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT d.id as contact_id, civicrm_group.name as group_name\n FROM dates_{$this->_tableName} AS d\n INNER JOIN civicrm_group_contact\n ON civicrm_group_contact.contact_id = d.id\n LEFT JOIN civicrm_group\n ON civicrm_group_contact.group_id = civicrm_group.id";
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " LEFT JOIN Xg_{$this->_tableName}\n ON d.id = Xg_{$this->_tableName}.contact_id";
}
$includeGroup .= " WHERE \n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN({$iGroups})";
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
}
if ($this->_debug > 0) {
print "-- Include groups query: <pre>";
print "{$includeGroup};";
print "</pre>";
}
CRM_Core_DAO::executeQuery($includeGroup, CRM_Core_DAO::$_nullArray);
//search for smart group contacts
foreach ($this->_includeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssId = CRM_Utils_Array::key($values, $smartGroup);
$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
$smartSql .= " AND contact_a.id IN (\n SELECT id AS contact_id\n FROM dates_{$this->_tableName} )";
$smartSql .= " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact\n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
//used only when exclude group is selected
if ($xGroups != 0) {
$smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
}
$smartGroupQuery = " INSERT IGNORE INTO\n Ig_{$this->_tableName}(contact_id) \n {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery, CRM_Core_DAO::$_nullArray);
if ($this->_debug > 0) {
print "-- Smart group query: <pre>";
print "{$smartGroupQuery};";
print "</pre>";
}
$insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}\n SET group_names = (SELECT title FROM civicrm_group\n WHERE civicrm_group.id = {$values})\n WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL \n AND Ig_{$this->_tableName}.group_names IS NULL";
CRM_Core_DAO::executeQuery($insertGroupNameQuery, CRM_Core_DAO::$_nullArray);
if ($this->_debug > 0) {
print "-- Smart group query: <pre>";
print "{$insertGroupNameQuery};";
print "</pre>";
}
}
}
}
// end if( $this->_groups ) condition
$from = "FROM civicrm_contact contact_a";
/* We need to join to this again to get the date_added value */
$from .= " INNER JOIN dates_{$this->_tableName} d ON (contact_a.id = d.id)";
// Only include groups in the search query of one or more Include OR Exclude groups has been selected.
// CRM-6356
if ($this->_groups) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
}
//this makes smart groups using this search compatible w/ CiviMail
$from .= " LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id)";
return $from;
}
function from()
{
//define table name
$randomNum = md5(uniqid());
$this->_tableName = "civicrm_temp_custom_{$randomNum}";
//block for Group search
$smartGroup = array();
$group = new CRM_Contact_DAO_Group();
$group->is_active = 1;
$group->find();
while ($group->fetch()) {
$allGroups[] = $group->id;
if ($group->saved_search_id) {
$smartGroup[$group->saved_search_id] = $group->id;
}
}
$includedGroups = implode(',', $allGroups);
if (!empty($this->_includeGroups)) {
$iGroups = implode(',', $this->_includeGroups);
} else {
//if no group selected search for all groups
$iGroups = $includedGroups;
}
if (is_array($this->_excludeGroups)) {
$xGroups = implode(',', $this->_excludeGroups);
} else {
$xGroups = 0;
}
$sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
CRM_Core_DAO::executeQuery($sql);
$sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
//used only when exclude group is selected
if ($xGroups != 0) {
$excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_group_contact.contact_id\n FROM civicrm_group_contact\n WHERE \n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN ( {$xGroups} )";
CRM_Core_DAO::executeQuery($excludeGroup);
//search for smart group contacts
foreach ($this->_excludeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssId = CRM_Utils_Array::key($values, $smartGroup);
$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
$smartSql = $smartSql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact \n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
$smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery);
}
}
}
$sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
CRM_Core_DAO::executeQuery($sql);
$sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}\n ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n group_names varchar(64)) ENGINE=HEAP";
if ($this->_debug > 0) {
print "-- Include groups query: <pre>";
print "{$sql};";
print "</pre>";
}
CRM_Core_DAO::executeQuery($sql);
$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_group_contact.contact_id, civicrm_group.name as group_name\n FROM civicrm_group_contact\n LEFT JOIN civicrm_group\n ON civicrm_group_contact.group_id = civicrm_group.id";
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " LEFT JOIN Xg_{$this->_tableName}\n ON civicrm_group_contact.contact_id = Xg_{$this->_tableName}.contact_id";
}
$includeGroup .= " WHERE \n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN({$iGroups})";
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
}
if ($this->_debug > 0) {
print "-- Include groups query: <pre>";
print "{$includeGroup};";
print "</pre>";
}
CRM_Core_DAO::executeQuery($includeGroup);
//search for smart group contacts
foreach ($this->_includeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssId = CRM_Utils_Array::key($values, $smartGroup);
$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
$smartSql .= " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact\n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
//used only when exclude group is selected
if ($xGroups != 0) {
$smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
}
$smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id) \n {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery);
$insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}\n SET group_names = (SELECT title FROM civicrm_group\n WHERE civicrm_group.id = {$values})\n WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL \n AND Ig_{$this->_tableName}.group_names IS NULL";
CRM_Core_DAO::executeQuery($insertGroupNameQuery);
}
}
$from = "FROM civicrm_contact contact_a";
$fromTail = "LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1 )";
$fromTail .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
// now create a temp table to store the randomized contacts
$sql = "DROP TEMPORARY TABLE IF EXISTS random_{$this->_tableName}";
CRM_Core_DAO::executeQuery($sql);
$sql = "CREATE TEMPORARY TABLE random_{$this->_tableName} ( id int primary key ) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
if (substr($this->_segmentSize, -1) == '%') {
$countSql = "SELECT DISTINCT contact_a.id {$from} {$fromTail}\n WHERE " . $this->where();
$dao = CRM_Core_DAO::executeQuery($countSql);
$totalSize = $dao->N;
$multiplier = substr($this->_segmentSize, 0, strlen($this->_segmentSize) - 1);
$multiplier /= 100;
//CRM_Core_Error::debug( "Total size: $totalSize<br/>Multiplier: $multiplier<br/>");
$this->_segmentSize = round($totalSize * $multiplier);
}
$sql = "INSERT INTO random_{$this->_tableName} ( id )\n SELECT DISTINCT contact_a.id {$from} {$fromTail}\n WHERE " . $this->where() . "\n ORDER BY RAND()\n LIMIT {$this->_segmentSize}";
CRM_Core_DAO::executeQuery($sql);
$from = "FROM random_{$this->_tableName} random";
$from .= " INNER JOIN civicrm_contact contact_a ON random.id = contact_a.id";
$from .= " {$fromTail}";
return $from;
}
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;
}
function from()
{
//define table name
$randomNum = md5(uniqid());
$this->_tableName = "civicrm_temp_custom_{$randomNum}";
//block for Group search
$smartGroup = array();
if ($this->_groups || $this->_allSearch) {
require_once 'CRM/Contact/DAO/Group.php';
$group = new CRM_Contact_DAO_Group();
$group->is_active = 1;
$group->find();
while ($group->fetch()) {
$allGroups[] = $group->id;
if ($group->saved_search_id) {
$smartGroup[$group->saved_search_id] = $group->id;
}
}
$includedGroups = implode(',', $allGroups);
if (!empty($this->_includeGroups)) {
$iGroups = implode(',', $this->_includeGroups);
} else {
//if no group selected search for all groups
$iGroups = null;
}
if (is_array($this->_excludeGroups)) {
$xGroups = implode(',', $this->_excludeGroups);
} else {
$xGroups = 0;
}
$sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
//used only when exclude group is selected
if ($xGroups != 0) {
$excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_group_contact.contact_id\n FROM civicrm_group_contact, civicrm_contact \n WHERE \n civicrm_contact.id = civicrm_group_contact.contact_id AND \n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN( {$xGroups})";
CRM_Core_DAO::executeQuery($excludeGroup);
//search for smart group contacts
foreach ($this->_excludeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssId = CRM_Utils_Array::key($values, $smartGroup);
$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
$smartSql = $smartSql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact \n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
$smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery);
}
}
}
$sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n group_names varchar(64)) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
if ($iGroups) {
$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, civicrm_group.title as group_name\n FROM civicrm_contact\n INNER JOIN civicrm_group_contact\n ON civicrm_group_contact.contact_id = civicrm_contact.id\n LEFT JOIN civicrm_group\n ON civicrm_group_contact.group_id = civicrm_group.id";
} else {
$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact";
}
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " LEFT JOIN Xg_{$this->_tableName}\n ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id";
}
if ($iGroups) {
$includeGroup .= " WHERE \n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN({$iGroups})";
} else {
$includeGroup .= " WHERE ( 1 ) ";
}
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
}
CRM_Core_DAO::executeQuery($includeGroup);
//search for smart group contacts
foreach ($this->_includeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssId = CRM_Utils_Array::key($values, $smartGroup);
$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
$smartSql .= " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact\n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
//used only when exclude group is selected
if ($xGroups != 0) {
$smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
}
$smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id) \n {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery);
$insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}\n SET group_names = (SELECT title FROM civicrm_group\n WHERE civicrm_group.id = {$values})\n WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL \n AND Ig_{$this->_tableName}.group_names IS NULL";
CRM_Core_DAO::executeQuery($insertGroupNameQuery);
}
}
}
//group contact search end here;
//block for Tags search
if ($this->_tags || $this->_allSearch) {
//find all tags
require_once 'CRM/Core/DAO/Tag.php';
$tag = new CRM_Core_DAO_Tag();
$tag->is_active = 1;
$tag->find();
while ($tag->fetch()) {
$allTags[] = $tag->id;
}
$includedTags = implode(',', $allTags);
if (!empty($this->_includeTags)) {
$iTags = implode(',', $this->_includeTags);
} else {
//if no group selected search for all groups
$iTags = null;
}
if (is_array($this->_excludeTags)) {
$xTags = implode(',', $this->_excludeTags);
} else {
$xTags = 0;
}
$sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
//used only when exclude tag is selected
if ($xTags != 0) {
$excludeTag = "INSERT INTO Xt_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_entity_tag.entity_id\n FROM civicrm_entity_tag, civicrm_contact \n WHERE \n civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_contact.id = civicrm_entity_tag.entity_id AND \n civicrm_entity_tag.tag_id IN( {$xTags})";
CRM_Core_DAO::executeQuery($excludeTag);
}
$sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n tag_names varchar(64)) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
if ($iTags) {
$includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, civicrm_tag.name as tag_name\n FROM civicrm_contact\n INNER JOIN civicrm_entity_tag\n ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_entity_tag.entity_id = civicrm_contact.id )\n LEFT JOIN civicrm_tag\n ON civicrm_entity_tag.tag_id = civicrm_tag.id";
} else {
$includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact";
}
//used only when exclude tag is selected
if ($xTags != 0) {
$includeTag .= " LEFT JOIN Xt_{$this->_tableName}\n ON civicrm_contact.id = Xt_{$this->_tableName}.contact_id";
}
if ($iTags) {
$includeTag .= " WHERE civicrm_entity_tag.tag_id IN({$iTags})";
} else {
$includeTag .= " WHERE ( 1 ) ";
}
//used only when exclude tag is selected
if ($xTags != 0) {
$includeTag .= " AND Xt_{$this->_tableName}.contact_id IS null";
}
CRM_Core_DAO::executeQuery($includeTag);
}
$from = " FROM civicrm_contact contact_a";
/*
* check the situation and set booleans
*/
if ($iGroups != 0) {
$iG = true;
} else {
$iG = false;
}
if ($iTags != 0) {
$iT = true;
} else {
$iT = false;
}
if ($xGroups != 0) {
$xG = true;
} else {
$xG = false;
}
if ($xTags != 0) {
$xT = true;
} else {
$xT = false;
}
if (!$this->_groups || !$this->_tags) {
$this->_andOr = 1;
}
/*
* Set from statement depending on array sel
*/
if ($iG && $iT && $xG && $xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )\n AND contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})\n AND contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})";
} else {
$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$from .= " LEFT JOIN Xg_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)";
$from .= " LEFT JOIN Xt_{$this->_tableName} temptable4 ON (contact_a.id = temptable4.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL OR temptable4.contact_id IS NOT NULL)";
}
}
if ($iG && $iT && $xG && !$xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )\n AND contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})";
} else {
$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$from .= " LEFT JOIN Xg_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)";
}
}
if ($iG && $iT && !$xG && $xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )\n AND contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})";
} else {
$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$from .= " LEFT JOIN Xt_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)";
}
}
if ($iG && $iT && !$xG && !$xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )";
} else {
$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)";
}
}
if ($iG && !$iT && $xG && $xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName}) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})";
} else {
$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$from .= " LEFT JOIN Xt_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)";
}
}
if ($iG && !$iT && $xG && !$xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName})";
} else {
$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)";
}
}
if ($iG && !$iT && !$xG && $xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})";
} else {
$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN Xt_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)";
}
}
if ($iG && !$iT && !$xG && !$xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL)";
} else {
$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL)";
}
}
if (!$iG && $iT && $xG && $xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName}) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})";
} else {
$from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$from .= " LEFT JOIN Xt_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)";
}
}
if (!$iG && $iT && $xG && !$xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName})";
} else {
$from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)";
}
}
if (!$iG && $iT && !$xG && $xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})";
} else {
$from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN Xt_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)";
}
}
if (!$iG && $iT && !$xG && !$xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL)";
} else {
$from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL)";
}
}
if (!$iG && !$iT && $xG && $xT) {
if ($this->_andOr == 1) {
$this->_where = "contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})\n AND contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})";
} else {
$from .= " LEFT JOIN Xg_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN Xt_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)";
}
}
if (!$iG && !$iT && !$xG && $xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})";
} else {
$from .= " LEFT JOIN Xt_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)";
}
}
if (!$iG && !$iT && $xG && !$xT) {
if ($this->_andOr == 1) {
$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})";
} else {
$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN Xg_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$this->_where = "( temptable1.contact_id IS NOT NULL)";
}
}
$from .= " LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) )";
return $from;
}
function from()
{
$iGroups = $xGroups = $iTags = $xTags = 0;
//define table name
$randomNum = md5(uniqid());
$this->_tableName = "civicrm_temp_custom_{$randomNum}";
//block for Group search
$smartGroup = array();
if ($this->_groups || $this->_allSearch) {
$group = new CRM_Contact_DAO_Group();
$group->is_active = 1;
$group->find();
while ($group->fetch()) {
$allGroups[] = $group->id;
if ($group->saved_search_id) {
$smartGroup[$group->saved_search_id] = $group->id;
}
}
$includedGroups = implode(',', $allGroups);
if (!empty($this->_includeGroups)) {
$iGroups = implode(',', $this->_includeGroups);
} else {
//if no group selected search for all groups
$iGroups = NULL;
}
if (is_array($this->_excludeGroups)) {
$xGroups = implode(',', $this->_excludeGroups);
} else {
$xGroups = 0;
}
$sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
//used only when exclude group is selected
if ($xGroups != 0) {
$excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_group_contact.contact_id\n FROM civicrm_group_contact, civicrm_contact\n WHERE\n civicrm_contact.id = civicrm_group_contact.contact_id AND\n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN( {$xGroups})";
CRM_Core_DAO::executeQuery($excludeGroup);
//search for smart group contacts
foreach ($this->_excludeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssId = CRM_Utils_Array::key($values, $smartGroup);
$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
$smartSql = $smartSql . " AND contact_a.id NOT IN (\n SELECT contact_id FROM civicrm_group_contact\n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
$smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery);
}
}
}
$sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n group_names varchar(64)) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
if ($iGroups) {
$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, civicrm_group.title as group_name\n FROM civicrm_contact\n INNER JOIN civicrm_group_contact\n ON civicrm_group_contact.contact_id = civicrm_contact.id\n LEFT JOIN civicrm_group\n ON civicrm_group_contact.group_id = civicrm_group.id";
} else {
$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact";
}
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " LEFT JOIN Xg_{$this->_tableName}\n ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id";
}
if ($iGroups) {
$includeGroup .= " WHERE\n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN({$iGroups})";
} else {
$includeGroup .= " WHERE ( 1 ) ";
}
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
}
CRM_Core_DAO::executeQuery($includeGroup);
//search for smart group contacts
foreach ($this->_includeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssId = CRM_Utils_Array::key($values, $smartGroup);
$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
$smartSql .= " AND contact_a.id NOT IN (\n SELECT contact_id FROM civicrm_group_contact\n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
//used only when exclude group is selected
if ($xGroups != 0) {
$smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
}
$smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id)\n {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery);
$insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}\n SET group_names = (SELECT title FROM civicrm_group\n WHERE civicrm_group.id = {$values})\n WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL\n AND Ig_{$this->_tableName}.group_names IS NULL";
CRM_Core_DAO::executeQuery($insertGroupNameQuery);
}
}
}
//group contact search end here;
//block for Tags search
if ($this->_tags || $this->_allSearch) {
//find all tags
$tag = new CRM_Core_DAO_Tag();
$tag->is_active = 1;
$tag->find();
while ($tag->fetch()) {
$allTags[] = $tag->id;
}
$includedTags = implode(',', $allTags);
if (!empty($this->_includeTags)) {
$iTags = implode(',', $this->_includeTags);
} else {
//if no group selected search for all groups
$iTags = NULL;
}
if (is_array($this->_excludeTags)) {
$xTags = implode(',', $this->_excludeTags);
} else {
$xTags = 0;
}
$sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
//used only when exclude tag is selected
if ($xTags != 0) {
$excludeTag = "INSERT INTO Xt_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_entity_tag.entity_id\n FROM civicrm_entity_tag, civicrm_contact\n WHERE\n civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_contact.id = civicrm_entity_tag.entity_id AND\n civicrm_entity_tag.tag_id IN( {$xTags})";
CRM_Core_DAO::executeQuery($excludeTag);
}
$sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n tag_names varchar(64)) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
if ($iTags) {
$includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, civicrm_tag.name as tag_name\n FROM civicrm_contact\n INNER JOIN civicrm_entity_tag\n ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_entity_tag.entity_id = civicrm_contact.id )\n LEFT JOIN civicrm_tag\n ON civicrm_entity_tag.tag_id = civicrm_tag.id";
} else {
$includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact";
}
//used only when exclude tag is selected
if ($xTags != 0) {
$includeTag .= " LEFT JOIN Xt_{$this->_tableName}\n ON civicrm_contact.id = Xt_{$this->_tableName}.contact_id";
}
if ($iTags) {
$includeTag .= " WHERE civicrm_entity_tag.tag_id IN({$iTags})";
} else {
$includeTag .= " WHERE ( 1 ) ";
}
//used only when exclude tag is selected
if ($xTags != 0) {
$includeTag .= " AND Xt_{$this->_tableName}.contact_id IS null";
}
CRM_Core_DAO::executeQuery($includeTag);
}
$from = " FROM civicrm_contact contact_a";
/*
* CRM-10850 / CRM-10848
* If we use include / exclude groups as smart groups for ACL's having the below causes
* a cycle which messes things up. Hence commenting out for now
* $this->buildACLClause('contact_a');
*/
/*
* check the situation and set booleans
*/
$Ig = $iGroups != 0;
$It = $iTags != 0;
$Xg = $xGroups != 0;
$Xt = $xTags != 0;
//PICK UP FROM HERE
if (!$this->_groups && !$this->_tags) {
$this->_andOr = 1;
}
/*
* Set from statement depending on array sel
*/
$whereitems = array();
foreach (array('Ig', 'It') as $inc) {
if ($this->_andOr == 1) {
if (${$inc}) {
$from .= " INNER JOIN {$inc}_{$this->_tableName} temptable{$inc} ON (contact_a.id = temptable{$inc}.contact_id)";
}
} else {
if (${$inc}) {
$from .= " LEFT JOIN {$inc}_{$this->_tableName} temptable{$inc} ON (contact_a.id = temptable{$inc}.contact_id)";
}
}
if (${$inc}) {
$whereitems[] = "temptable{$inc}.contact_id IS NOT NULL";
}
}
$this->_where = $whereitems ? "(" . implode(' OR ', $whereitems) . ')' : '(1)';
foreach (array('Xg', 'Xt') as $exc) {
if (${$exc}) {
$from .= " LEFT JOIN {$exc}_{$this->_tableName} temptable{$exc} ON (contact_a.id = temptable{$exc}.contact_id)";
$this->_where .= " AND temptable{$exc}.contact_id IS NULL";
}
}
$from .= " LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) ) {$this->_aclFrom}";
if ($this->_aclWhere) {
$this->_where .= " AND {$this->_aclWhere} ";
}
// also exclude all contacts that are deleted
// CRM-11627
$this->_where .= " AND (contact_a.is_deleted != 1) ";
return $from;
}
