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

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