crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/CRM/Contact/Form/Search/Custom/RandomSegment.php

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