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

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  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;
  38
  39   /**
  40    * @param $formValues
  41    */
  42   function __construct(&$formValues) {
  43     parent::__construct($formValues);
  44
  45     $this->_columns = array(
  46       ts('Contact Id') => 'contact_id',
  47       ts('Contact Type') => 'contact_type',
  48       ts('Name') => 'sort_name',
  49       ts('Email') => 'email',
  50     );
  51
  52     $this->initialize();
  53   }
  54
  55   function initialize() {
  56     $this->_segmentSize = CRM_Utils_Array::value('segmentSize', $this->_formValues);
  57
  58     $this->_includeGroups = CRM_Utils_Array::value('includeGroups', $this->_formValues);
  59
  60     $this->_excludeGroups = CRM_Utils_Array::value('excludeGroups', $this->_formValues);
  61
  62     $this->_allSearch = FALSE;
  63     $this->_groups = FALSE;
  64
  65     if (empty($this->_includeGroups) && empty($this->_excludeGroups)) {
  66       //empty search
  67       $this->_allSearch = TRUE;
  68     }
  69
  70     if (!empty($this->_includeGroups) || !empty($this->_excludeGroups)) {
  71       //group(s) selected
  72       $this->_groups = TRUE;
  73     }
  74   }
  75
  76   /**
  77    * @param CRM_Core_Form $form
  78    */
  79   function buildForm(&$form) {
  80     $form->add('text',
  81       'segmentSize',
  82       ts('Segment Size'),
  83       TRUE
  84     );
  85
  86     $groups = CRM_Core_PseudoConstant::group();
  87
  88     $select2style = array(
  89       'multiple' => TRUE,
  90       'style' => 'width: 100%; max-width: 60em;',
  91       'class' => 'crm-select2',
  92       'placeholder' => ts('- select -'),
  93     );
  94
  95     $form->add('select', 'includeGroups',
  96       ts('Include Group(s)'),
  97       $groups,
  98       FALSE,
  99       $select2style
 100     );
 101
 102     $form->add('select', 'excludeGroups',
 103       ts('Exclude Group(s)'),
 104       $groups,
 105       FALSE,
 106       $select2style
 107     );
 108
 109     $this->setTitle('Create a random segment of contacts');
 110
 111     /**
 112      * if you are using the standard template, this array tells the template what elements
 113      * are part of the search criteria
 114      */
 115     $form->assign('elements', array('segmentSize', 'includeGroups', 'excludeGroups'));
 116   }
 117
 118   /**
 119    * @return null
 120    */
 121   function summary() {
 122     return NULL;
 123   }
 124
 125   /**
 126    * @param int $offset
 127    * @param int $rowcount
 128    * @param null $sort
 129    * @param bool $includeContactIDs
 130    * @param bool $justIDs
 131    *
 132    * @return string
 133    */
 134   function all($offset = 0, $rowcount = 0, $sort = NULL,
 135     $includeContactIDs = FALSE, $justIDs = FALSE
 136   ) {
 137     if ($justIDs) {
 138       $selectClause = "contact_a.id as contact_id";
 139     }
 140     else {
 141       $selectClause = "contact_a.id   as contact_id,
 142                          contact_a.contact_type as contact_type,
 143                          contact_a.sort_name    as sort_name,
 144                          civicrm_email.email    as email";
 145     }
 146
 147     return $this->sql($selectClause,
 148       $offset, $rowcount, $sort,
 149       $includeContactIDs, NULL
 150     );
 151   }
 152
 153   /**
 154    * @return string
 155    */
 156   function from() {
 157     //define table name
 158     $randomNum = md5(uniqid());
 159     $this->_tableName = "civicrm_temp_custom_{$randomNum}";
 160
 161     //block for Group search
 162     $smartGroup       = array();
 163     $group            = new CRM_Contact_DAO_Group();
 164     $group->is_active = 1;
 165     $group->find();
 166     while ($group->fetch()) {
 167       $allGroups[] = $group->id;
 168       if ($group->saved_search_id) {
 169         $smartGroup[$group->saved_search_id] = $group->id;
 170       }
 171     }
 172     $includedGroups = implode(',', $allGroups);
 173
 174     if (!empty($this->_includeGroups)) {
 175       $iGroups = implode(',', $this->_includeGroups);
 176     }
 177     else {
 178       //if no group selected search for all groups
 179       $iGroups = $includedGroups;
 180     }
 181     if (is_array($this->_excludeGroups)) {
 182       $xGroups = implode(',', $this->_excludeGroups);
 183     }
 184     else {
 185       $xGroups = 0;
 186     }
 187
 188     $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
 189     CRM_Core_DAO::executeQuery($sql);
 190     $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
 191     CRM_Core_DAO::executeQuery($sql);
 192
 193     //used only when exclude group is selected
 194     if ($xGroups != 0) {
 195       $excludeGroup = "INSERT INTO  Xg_{$this->_tableName} ( contact_id )
 196               SELECT  DISTINCT civicrm_group_contact.contact_id
 197               FROM civicrm_group_contact
 198               WHERE
 199                  civicrm_group_contact.status = 'Added' AND
 200                  civicrm_group_contact.group_id IN ( {$xGroups} )";
 201
 202       CRM_Core_DAO::executeQuery($excludeGroup);
 203
 204       //search for smart group contacts
 205       foreach ($this->_excludeGroups as $keys => $values) {
 206         if (in_array($values, $smartGroup)) {
 207           $ssId = CRM_Utils_Array::key($values, $smartGroup);
 208
 209           $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
 210
 211           $smartSql = $smartSql . " AND contact_a.id NOT IN (
 212                           SELECT contact_id FROM civicrm_group_contact
 213                           WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
 214
 215           $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
 216
 217           CRM_Core_DAO::executeQuery($smartGroupQuery);
 218         }
 219       }
 220     }
 221
 222     $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
 223     CRM_Core_DAO::executeQuery($sql);
 224     $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}
 225             ( id int PRIMARY KEY AUTO_INCREMENT,
 226               contact_id int,
 227               group_names varchar(64)) ENGINE=HEAP";
 228
 229     if ($this->_debug > 0) {
 230       print "-- Include groups query: <pre>";
 231       print "$sql;";
 232       print "</pre>";
 233     }
 234
 235     CRM_Core_DAO::executeQuery($sql);
 236
 237     $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
 238              SELECT      civicrm_group_contact.contact_id, civicrm_group.name as group_name
 239              FROM        civicrm_group_contact
 240              LEFT JOIN   civicrm_group
 241              ON          civicrm_group_contact.group_id = civicrm_group.id";
 242
 243     //used only when exclude group is selected
 244     if ($xGroups != 0) {
 245       $includeGroup .= " LEFT JOIN        Xg_{$this->_tableName}
 246                                       ON        civicrm_group_contact.contact_id = Xg_{$this->_tableName}.contact_id";
 247     }
 248     $includeGroup .= " WHERE
 249                                  civicrm_group_contact.status = 'Added'  AND
 250                                  civicrm_group_contact.group_id IN($iGroups)";
 251
 252     //used only when exclude group is selected
 253     if ($xGroups != 0) {
 254       $includeGroup .= " AND  Xg_{$this->_tableName}.contact_id IS null";
 255     }
 256
 257     if ($this->_debug > 0) {
 258       print "-- Include groups query: <pre>";
 259       print "$includeGroup;";
 260       print "</pre>";
 261     }
 262
 263     CRM_Core_DAO::executeQuery($includeGroup);
 264
 265     //search for smart group contacts
 266     foreach ($this->_includeGroups as $keys => $values) {
 267       if (in_array($values, $smartGroup)) {
 268
 269         $ssId = CRM_Utils_Array::key($values, $smartGroup);
 270
 271         $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
 272
 273         $smartSql .= " AND contact_a.id NOT IN (
 274                                SELECT contact_id FROM civicrm_group_contact
 275                                WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
 276
 277         //used only when exclude group is selected
 278         if ($xGroups != 0) {
 279           $smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM  Xg_{$this->_tableName})";
 280         }
 281
 282         $smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id)
 283                     $smartSql";
 284
 285         CRM_Core_DAO::executeQuery($smartGroupQuery);
 286         $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
 287                     SET group_names = (SELECT title FROM civicrm_group
 288                         WHERE civicrm_group.id = $values)
 289                     WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
 290                         AND Ig_{$this->_tableName}.group_names IS NULL";
 291         CRM_Core_DAO::executeQuery($insertGroupNameQuery);
 292       }
 293     }
 294
 295     $from = "FROM civicrm_contact contact_a";
 296
 297     $fromTail = "LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1 )";
 298
 299     $fromTail .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
 300
 301     // now create a temp table to store the randomized contacts
 302     $sql = "DROP TEMPORARY TABLE IF EXISTS random_{$this->_tableName}";
 303     CRM_Core_DAO::executeQuery($sql);
 304     $sql = "CREATE TEMPORARY TABLE random_{$this->_tableName} ( id int primary key ) ENGINE=HEAP";
 305     CRM_Core_DAO::executeQuery($sql);
 306
 307     if (substr($this->_segmentSize, -1) == '%') {
 308       $countSql = "SELECT DISTINCT contact_a.id $from $fromTail
 309                          WHERE " . $this->where();
 310       $dao        = CRM_Core_DAO::executeQuery($countSql);
 311       $totalSize  = $dao->N;
 312       $multiplier = substr($this->_segmentSize, 0, strlen($this->_segmentSize) - 1);
 313       $multiplier /= 100;
 314       //CRM_Core_Error::debug( "Total size: $totalSize<br/>Multiplier: $multiplier<br/>");
 315       $this->_segmentSize = round($totalSize * $multiplier);
 316     }
 317
 318     $sql = "INSERT INTO random_{$this->_tableName} ( id )
 319                 SELECT DISTINCT contact_a.id $from $fromTail
 320                 WHERE " . $this->where() . "
 321                 ORDER BY RAND()
 322                 LIMIT {$this->_segmentSize}";
 323     CRM_Core_DAO::executeQuery($sql);
 324
 325     $from = "FROM random_{$this->_tableName} random";
 326
 327     $from .= " INNER JOIN civicrm_contact contact_a ON random.id = contact_a.id";
 328
 329     $from .= " $fromTail";
 330
 331     return $from;
 332   }
 333
 334   /**
 335    * @param bool $includeContactIDs
 336    *
 337    * @return string
 338    */
 339   function where($includeContactIDs = FALSE) {
 340     return '(1)';
 341   }
 342
 343   /**
 344    * @return string
 345    */
 346   function templateFile() {
 347     return 'CRM/Contact/Form/Search/Custom.tpl';
 348   }
 349
 350   /**
 351    * @param $title
 352    */
 353   function setTitle($title) {
 354     if ($title) {
 355       CRM_Utils_System::setTitle($title);
 356     }
 357     else {
 358       CRM_Utils_System::setTitle(ts('Search'));
 359     }
 360   }
 361
 362   /**
 363    * @return mixed
 364    */
 365   /**
 366    * @return mixed
 367    */
 368   function count() {
 369     $sql = $this->all();
 370
 371     $dao = CRM_Core_DAO::executeQuery($sql);
 372     return $dao->N;
 373   }
 374
 375   function __destruct() {
 376     // the temporary tables are dropped automatically
 377     // so we don't do it here
 378     // but let mysql clean up
 379     return;
 380   }
 381 }
 382