CRM/Contact/Form/Search/Custom/RandomSegment.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.3                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_Form_Search_Custom_RandomSegment extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_debug = 0; function __construct(&$formValues) {
  38     parent::__construct($formValues);
  39
  40     $this->_columns = array(
  41       ts('Contact Id') => 'contact_id',
  42       ts('Contact Type') => 'contact_type',
  43       ts('Name') => 'sort_name',
  44       ts('Email') => 'email',
  45     );
  46
  47     $this->initialize();
  48   }
  49
  50   function initialize() {
  51     $this->_segmentSize = CRM_Utils_Array::value('segmentSize', $this->_formValues);
  52
  53     $this->_includeGroups = CRM_Utils_Array::value('includeGroups', $this->_formValues);
  54
  55     $this->_excludeGroups = CRM_Utils_Array::value('excludeGroups', $this->_formValues);
  56
  57     $this->_allSearch = FALSE;
  58     $this->_groups = FALSE;
  59
  60     if (empty($this->_includeGroups) && empty($this->_excludeGroups)) {
  61       //empty search
  62       $this->_allSearch = TRUE;
  63     }
  64
  65     if (!empty($this->_includeGroups) || !empty($this->_excludeGroups)) {
  66       //group(s) selected
  67       $this->_groups = TRUE;
  68     }
  69   }
  70
  71   function buildForm(&$form) {
  72     $form->add('text',
  73       'segmentSize',
  74       ts('Segment Size'),
  75       TRUE
  76     );
  77
  78     $groups = CRM_Core_PseudoConstant::group();
  79     $inG = &$form->addElement('advmultiselect', 'includeGroups',
  80       ts('Include Group(s)') . ' ', $groups,
  81       array(
  82         'size' => 5,
  83         'style' => 'width:240px',
  84         'class' => 'advmultiselect',
  85       )
  86     );
  87
  88     $outG = &$form->addElement('advmultiselect', 'excludeGroups',
  89       ts('Exclude Group(s)') . ' ', $groups,
  90       array(
  91         'size' => 5,
  92         'style' => 'width:240px',
  93         'class' => 'advmultiselect',
  94       )
  95     );
  96
  97     $inG->setButtonAttributes('add', array('value' => ts('Add >>')));
  98     $outG->setButtonAttributes('add', array('value' => ts('Add >>')));
  99     $inG->setButtonAttributes('remove', array('value' => ts('<< Remove')));
 100     $outG->setButtonAttributes('remove', array('value' => ts('<< Remove')));
 101
 102     $this->setTitle('Create a random segment of contacts');
 103
 104     /**
 105      * if you are using the standard template, this array tells the template what elements
 106      * are part of the search criteria
 107      */
 108     $form->assign('elements', array('segmentSize', 'includeGroups', 'excludeGroups'));
 109   }
 110
 111   function summary() {
 112     return NULL;
 113   }
 114
 115   function all($offset = 0, $rowcount = 0, $sort = NULL,
 116     $includeContactIDs = FALSE, $justIDs = FALSE
 117   ) {
 118     if ($justIDs) {
 119       $selectClause = "contact_a.id as contact_id";
 120     }
 121     else {
 122       $selectClause = "contact_a.id   as contact_id,
 123                          contact_a.contact_type as contact_type,
 124                          contact_a.sort_name    as sort_name,
 125                          civicrm_email.email    as email";
 126     }
 127
 128     return $this->sql($selectClause,
 129       $offset, $rowcount, $sort,
 130       $includeContactIDs, NULL
 131     );
 132   }
 133
 134   function from() {
 135     //define table name
 136     $randomNum = md5(uniqid());
 137     $this->_tableName = "civicrm_temp_custom_{$randomNum}";
 138
 139     //block for Group search
 140     $smartGroup       = array();
 141     $group            = new CRM_Contact_DAO_Group();
 142     $group->is_active = 1;
 143     $group->find();
 144     while ($group->fetch()) {
 145       $allGroups[] = $group->id;
 146       if ($group->saved_search_id) {
 147         $smartGroup[$group->saved_search_id] = $group->id;
 148       }
 149     }
 150     $includedGroups = implode(',', $allGroups);
 151
 152     if (!empty($this->_includeGroups)) {
 153       $iGroups = implode(',', $this->_includeGroups);
 154     }
 155     else {
 156       //if no group selected search for all groups
 157       $iGroups = $includedGroups;
 158     }
 159     if (is_array($this->_excludeGroups)) {
 160       $xGroups = implode(',', $this->_excludeGroups);
 161     }
 162     else {
 163       $xGroups = 0;
 164     }
 165
 166     $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
 167     CRM_Core_DAO::executeQuery($sql);
 168     $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
 169     CRM_Core_DAO::executeQuery($sql);
 170
 171     //used only when exclude group is selected
 172     if ($xGroups != 0) {
 173       $excludeGroup = "INSERT INTO  Xg_{$this->_tableName} ( contact_id )
 174               SELECT  DISTINCT civicrm_group_contact.contact_id
 175               FROM civicrm_group_contact
 176               WHERE
 177                  civicrm_group_contact.status = 'Added' AND
 178                  civicrm_group_contact.group_id IN ( {$xGroups} )";
 179
 180       CRM_Core_DAO::executeQuery($excludeGroup);
 181
 182       //search for smart group contacts
 183       foreach ($this->_excludeGroups as $keys => $values) {
 184         if (in_array($values, $smartGroup)) {
 185           $ssId = CRM_Utils_Array::key($values, $smartGroup);
 186
 187           $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
 188
 189           $smartSql = $smartSql . " AND contact_a.id NOT IN (
 190                           SELECT contact_id FROM civicrm_group_contact
 191                           WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
 192
 193           $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
 194
 195           CRM_Core_DAO::executeQuery($smartGroupQuery);
 196         }
 197       }
 198     }
 199
 200     $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
 201     CRM_Core_DAO::executeQuery($sql);
 202     $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}
 203             ( id int PRIMARY KEY AUTO_INCREMENT,
 204               contact_id int,
 205               group_names varchar(64)) ENGINE=HEAP";
 206
 207     if ($this->_debug > 0) {
 208       print "-- Include groups query: <pre>";
 209       print "$sql;";
 210       print "</pre>";
 211     }
 212
 213     CRM_Core_DAO::executeQuery($sql);
 214
 215     $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
 216              SELECT      civicrm_group_contact.contact_id, civicrm_group.name as group_name
 217              FROM        civicrm_group_contact
 218              LEFT JOIN   civicrm_group
 219              ON          civicrm_group_contact.group_id = civicrm_group.id";
 220
 221     //used only when exclude group is selected
 222     if ($xGroups != 0) {
 223       $includeGroup .= " LEFT JOIN        Xg_{$this->_tableName}
 224                                       ON        civicrm_group_contact.contact_id = Xg_{$this->_tableName}.contact_id";
 225     }
 226     $includeGroup .= " WHERE
 227                                  civicrm_group_contact.status = 'Added'  AND
 228                                  civicrm_group_contact.group_id IN($iGroups)";
 229
 230     //used only when exclude group is selected
 231     if ($xGroups != 0) {
 232       $includeGroup .= " AND  Xg_{$this->_tableName}.contact_id IS null";
 233     }
 234
 235     if ($this->_debug > 0) {
 236       print "-- Include groups query: <pre>";
 237       print "$includeGroup;";
 238       print "</pre>";
 239     }
 240
 241     CRM_Core_DAO::executeQuery($includeGroup);
 242
 243     //search for smart group contacts
 244     foreach ($this->_includeGroups as $keys => $values) {
 245       if (in_array($values, $smartGroup)) {
 246
 247         $ssId = CRM_Utils_Array::key($values, $smartGroup);
 248
 249         $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
 250
 251         $smartSql .= " AND contact_a.id NOT IN (
 252                                SELECT contact_id FROM civicrm_group_contact
 253                                WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
 254
 255         //used only when exclude group is selected
 256         if ($xGroups != 0) {
 257           $smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM  Xg_{$this->_tableName})";
 258         }
 259
 260         $smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id)
 261                     $smartSql";
 262
 263         CRM_Core_DAO::executeQuery($smartGroupQuery);
 264         $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
 265                     SET group_names = (SELECT title FROM civicrm_group
 266                         WHERE civicrm_group.id = $values)
 267                     WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
 268                         AND Ig_{$this->_tableName}.group_names IS NULL";
 269         CRM_Core_DAO::executeQuery($insertGroupNameQuery);
 270       }
 271     }
 272
 273     $from = "FROM civicrm_contact contact_a";
 274
 275     $fromTail = "LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1 )";
 276
 277     $fromTail .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
 278
 279     // now create a temp table to store the randomized contacts
 280     $sql = "DROP TEMPORARY TABLE IF EXISTS random_{$this->_tableName}";
 281     CRM_Core_DAO::executeQuery($sql);
 282     $sql = "CREATE TEMPORARY TABLE random_{$this->_tableName} ( id int primary key ) ENGINE=HEAP";
 283     CRM_Core_DAO::executeQuery($sql);
 284
 285     if (substr($this->_segmentSize, -1) == '%') {
 286       $countSql = "SELECT DISTINCT contact_a.id $from $fromTail
 287                          WHERE " . $this->where();
 288       $dao        = CRM_Core_DAO::executeQuery($countSql);
 289       $totalSize  = $dao->N;
 290       $multiplier = substr($this->_segmentSize, 0, strlen($this->_segmentSize) - 1);
 291       $multiplier /= 100;
 292       //CRM_Core_Error::debug( "Total size: $totalSize<br/>Multiplier: $multiplier<br/>");
 293       $this->_segmentSize = round($totalSize * $multiplier);
 294     }
 295
 296     $sql = "INSERT INTO random_{$this->_tableName} ( id )
 297                 SELECT DISTINCT contact_a.id $from $fromTail
 298                 WHERE " . $this->where() . "
 299                 ORDER BY RAND()
 300                 LIMIT {$this->_segmentSize}";
 301     CRM_Core_DAO::executeQuery($sql);
 302
 303     $from = "FROM random_{$this->_tableName} random";
 304
 305     $from .= " INNER JOIN civicrm_contact contact_a ON random.id = contact_a.id";
 306
 307     $from .= " $fromTail";
 308
 309     return $from;
 310   }
 311
 312   function where($includeContactIDs = FALSE) {
 313     return '(1)';
 314   }
 315
 316   function templateFile() {
 317     return 'CRM/Contact/Form/Search/Custom.tpl';
 318   }
 319
 320   function setTitle($title) {
 321     if ($title) {
 322       CRM_Utils_System::setTitle($title);
 323     }
 324     else {
 325       CRM_Utils_System::setTitle(ts('Search'));
 326     }
 327   }
 328
 329   function count() {
 330     $sql = $this->all();
 331
 332     $dao = CRM_Core_DAO::executeQuery($sql);
 333     return $dao->N;
 334   }
 335
 336   function __destruct() {
 337     // the temporary tables are dropped automatically
 338     // so we dont do it here
 339     // but let mysql clean up
 340     return;
 341   }
 342 }
 343