Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 TO |
16 | */ |
17 | class CRM_Contact_Form_Search_Custom_RandomSegment extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface { | |
18 | ||
430ae6dd | 19 | protected $_debug = 0; |
d14ccbdc SL |
20 | protected $_aclFrom = NULL; |
21 | protected $_aclWhere = NULL; | |
430ae6dd | 22 | |
86538308 | 23 | /** |
5a409b50 | 24 | * Class constructor. |
25 | * | |
26 | * @param array $formValues | |
86538308 | 27 | */ |
00be9182 | 28 | public function __construct(&$formValues) { |
6a488035 TO |
29 | parent::__construct($formValues); |
30 | ||
be2fb01f | 31 | $this->_columns = [ |
7b99ead3 | 32 | ts('Contact ID') => 'contact_id', |
6a488035 TO |
33 | ts('Contact Type') => 'contact_type', |
34 | ts('Name') => 'sort_name', | |
35 | ts('Email') => 'email', | |
be2fb01f | 36 | ]; |
6a488035 TO |
37 | |
38 | $this->initialize(); | |
39 | } | |
40 | ||
00be9182 | 41 | public function initialize() { |
9c1bc317 | 42 | $this->_segmentSize = $this->_formValues['segmentSize'] ?? NULL; |
6a488035 | 43 | |
9c1bc317 | 44 | $this->_includeGroups = $this->_formValues['includeGroups'] ?? NULL; |
6a488035 | 45 | |
9c1bc317 | 46 | $this->_excludeGroups = $this->_formValues['excludeGroups'] ?? NULL; |
6a488035 TO |
47 | |
48 | $this->_allSearch = FALSE; | |
49 | $this->_groups = FALSE; | |
50 | ||
51 | if (empty($this->_includeGroups) && empty($this->_excludeGroups)) { | |
52 | //empty search | |
53 | $this->_allSearch = TRUE; | |
54 | } | |
55 | ||
56 | if (!empty($this->_includeGroups) || !empty($this->_excludeGroups)) { | |
57 | //group(s) selected | |
58 | $this->_groups = TRUE; | |
59 | } | |
60 | } | |
61 | ||
86538308 | 62 | /** |
11cac306 | 63 | * @param CRM_Core_Form $form |
86538308 | 64 | */ |
00be9182 | 65 | public function buildForm(&$form) { |
6a488035 TO |
66 | $form->add('text', |
67 | 'segmentSize', | |
68 | ts('Segment Size'), | |
69 | TRUE | |
70 | ); | |
71 | ||
24431f7b | 72 | $groups = CRM_Core_PseudoConstant::nestedGroup(); |
11cac306 | 73 | |
be2fb01f | 74 | $select2style = [ |
11cac306 CW |
75 | 'multiple' => TRUE, |
76 | 'style' => 'width: 100%; max-width: 60em;', | |
77 | 'class' => 'crm-select2', | |
78 | 'placeholder' => ts('- select -'), | |
be2fb01f | 79 | ]; |
6a488035 | 80 | |
11cac306 CW |
81 | $form->add('select', 'includeGroups', |
82 | ts('Include Group(s)'), | |
83 | $groups, | |
84 | FALSE, | |
85 | $select2style | |
6a488035 TO |
86 | ); |
87 | ||
11cac306 CW |
88 | $form->add('select', 'excludeGroups', |
89 | ts('Exclude Group(s)'), | |
90 | $groups, | |
91 | FALSE, | |
92 | $select2style | |
93 | ); | |
6a488035 TO |
94 | |
95 | $this->setTitle('Create a random segment of contacts'); | |
96 | ||
97 | /** | |
98 | * if you are using the standard template, this array tells the template what elements | |
99 | * are part of the search criteria | |
100 | */ | |
be2fb01f | 101 | $form->assign('elements', ['segmentSize', 'includeGroups', 'excludeGroups']); |
6a488035 TO |
102 | } |
103 | ||
86538308 EM |
104 | /** |
105 | * @return null | |
106 | */ | |
00be9182 | 107 | public function summary() { |
6a488035 TO |
108 | return NULL; |
109 | } | |
110 | ||
86538308 EM |
111 | /** |
112 | * @param int $offset | |
113 | * @param int $rowcount | |
114 | * @param null $sort | |
115 | * @param bool $includeContactIDs | |
116 | * @param bool $justIDs | |
117 | * | |
118 | * @return string | |
119 | */ | |
389bcebf | 120 | public function all( |
51ccfbbe | 121 | $offset = 0, $rowcount = 0, $sort = NULL, |
6a488035 TO |
122 | $includeContactIDs = FALSE, $justIDs = FALSE |
123 | ) { | |
124 | if ($justIDs) { | |
125 | $selectClause = "contact_a.id as contact_id"; | |
126 | } | |
127 | else { | |
128 | $selectClause = "contact_a.id as contact_id, | |
129 | contact_a.contact_type as contact_type, | |
130 | contact_a.sort_name as sort_name, | |
131 | civicrm_email.email as email"; | |
132 | } | |
133 | ||
134 | return $this->sql($selectClause, | |
135 | $offset, $rowcount, $sort, | |
136 | $includeContactIDs, NULL | |
137 | ); | |
138 | } | |
139 | ||
86538308 EM |
140 | /** |
141 | * @return string | |
142 | */ | |
00be9182 | 143 | public function from() { |
6a488035 | 144 | //define table name |
1cde5376 SL |
145 | $this->_Xg_table = CRM_Utils_SQL_TempTable::build()->setMemory()->setCategory('xgSegment'); |
146 | $this->_Xg_tableName = $this->_Xg_table->getName(); | |
147 | $this->_Ig_table = CRM_Utils_SQL_TempTable::build()->setMemory()->setCategory('IgSegment'); | |
148 | $this->_Ig_tableName = $this->_Ig_table->getName(); | |
6a488035 TO |
149 | |
150 | //block for Group search | |
be2fb01f | 151 | $smartGroup = []; |
353ffa53 | 152 | $group = new CRM_Contact_DAO_Group(); |
6a488035 TO |
153 | $group->is_active = 1; |
154 | $group->find(); | |
155 | while ($group->fetch()) { | |
156 | $allGroups[] = $group->id; | |
157 | if ($group->saved_search_id) { | |
158 | $smartGroup[$group->saved_search_id] = $group->id; | |
159 | } | |
160 | } | |
161 | $includedGroups = implode(',', $allGroups); | |
162 | ||
163 | if (!empty($this->_includeGroups)) { | |
164 | $iGroups = implode(',', $this->_includeGroups); | |
165 | } | |
166 | else { | |
167 | //if no group selected search for all groups | |
168 | $iGroups = $includedGroups; | |
169 | } | |
170 | if (is_array($this->_excludeGroups)) { | |
171 | $xGroups = implode(',', $this->_excludeGroups); | |
172 | } | |
173 | else { | |
174 | $xGroups = 0; | |
175 | } | |
176 | ||
1cde5376 | 177 | $sql = "DROP TEMPORARY TABLE IF EXISTS {$this->_Xg_tableName}"; |
6a488035 | 178 | CRM_Core_DAO::executeQuery($sql); |
1cde5376 | 179 | $this->_Xg_table->createWithColumns('contact_id int PRIMARY KEY'); |
6a488035 TO |
180 | |
181 | //used only when exclude group is selected | |
182 | if ($xGroups != 0) { | |
1cde5376 | 183 | $excludeGroup = "INSERT INTO {$this->_Xg_tableName} ( contact_id ) |
6a488035 TO |
184 | SELECT DISTINCT civicrm_group_contact.contact_id |
185 | FROM civicrm_group_contact | |
186 | WHERE | |
187 | civicrm_group_contact.status = 'Added' AND | |
188 | civicrm_group_contact.group_id IN ( {$xGroups} )"; | |
189 | ||
190 | CRM_Core_DAO::executeQuery($excludeGroup); | |
191 | ||
192 | //search for smart group contacts | |
193 | foreach ($this->_excludeGroups as $keys => $values) { | |
194 | if (in_array($values, $smartGroup)) { | |
195 | $ssId = CRM_Utils_Array::key($values, $smartGroup); | |
196 | ||
197 | $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); | |
198 | ||
199 | $smartSql = $smartSql . " AND contact_a.id NOT IN ( | |
200 | SELECT contact_id FROM civicrm_group_contact | |
201 | WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')"; | |
202 | ||
1cde5376 | 203 | $smartGroupQuery = " INSERT IGNORE INTO {$this->_Xg_tableName}(contact_id) $smartSql"; |
6a488035 TO |
204 | |
205 | CRM_Core_DAO::executeQuery($smartGroupQuery); | |
206 | } | |
207 | } | |
208 | } | |
209 | ||
1cde5376 | 210 | $sql = "DROP TEMPORARY TABLE IF EXISTS {$this->_Ig_tableName}"; |
6a488035 | 211 | CRM_Core_DAO::executeQuery($sql); |
1cde5376 | 212 | $this->_Ig_table->createWithColumns("id int PRIMARY KEY AUTO_INCREMENT, contact_id int, group_names varchar(64)"); |
6a488035 | 213 | |
1cde5376 | 214 | $includeGroup = "INSERT INTO {$this->_Ig_tableName} (contact_id, group_names) |
6a488035 TO |
215 | SELECT civicrm_group_contact.contact_id, civicrm_group.name as group_name |
216 | FROM civicrm_group_contact | |
217 | LEFT JOIN civicrm_group | |
218 | ON civicrm_group_contact.group_id = civicrm_group.id"; | |
219 | ||
220 | //used only when exclude group is selected | |
221 | if ($xGroups != 0) { | |
1cde5376 SL |
222 | $includeGroup .= " LEFT JOIN {$this->_Xg_tableName} |
223 | ON civicrm_group_contact.contact_id = {$this->_Xg_tableName}.contact_id"; | |
6a488035 TO |
224 | } |
225 | $includeGroup .= " WHERE | |
226 | civicrm_group_contact.status = 'Added' AND | |
227 | civicrm_group_contact.group_id IN($iGroups)"; | |
228 | ||
229 | //used only when exclude group is selected | |
230 | if ($xGroups != 0) { | |
1cde5376 | 231 | $includeGroup .= " AND {$this->_Xg_tableName}.contact_id IS null"; |
6a488035 TO |
232 | } |
233 | ||
234 | if ($this->_debug > 0) { | |
235 | print "-- Include groups query: <pre>"; | |
236 | print "$includeGroup;"; | |
237 | print "</pre>"; | |
238 | } | |
239 | ||
240 | CRM_Core_DAO::executeQuery($includeGroup); | |
241 | ||
242 | //search for smart group contacts | |
243 | foreach ($this->_includeGroups as $keys => $values) { | |
244 | if (in_array($values, $smartGroup)) { | |
245 | ||
246 | $ssId = CRM_Utils_Array::key($values, $smartGroup); | |
247 | ||
248 | $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); | |
249 | ||
250 | $smartSql .= " AND contact_a.id NOT IN ( | |
251 | SELECT contact_id FROM civicrm_group_contact | |
252 | WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')"; | |
253 | ||
254 | //used only when exclude group is selected | |
255 | if ($xGroups != 0) { | |
1cde5376 | 256 | $smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM {$this->_Xg_tableName})"; |
6a488035 TO |
257 | } |
258 | ||
1cde5376 | 259 | $smartGroupQuery = " INSERT IGNORE INTO {$this->_Ig_tableName} (contact_id) |
6a488035 TO |
260 | $smartSql"; |
261 | ||
262 | CRM_Core_DAO::executeQuery($smartGroupQuery); | |
1cde5376 | 263 | $insertGroupNameQuery = "UPDATE IGNORE {$this->_Ig_tableName} |
6a488035 TO |
264 | SET group_names = (SELECT title FROM civicrm_group |
265 | WHERE civicrm_group.id = $values) | |
1cde5376 SL |
266 | WHERE {$this->_Ig_tableName}.contact_id IS NOT NULL |
267 | AND {$this->_Ig_tableName}.group_names IS NULL"; | |
6a488035 TO |
268 | CRM_Core_DAO::executeQuery($insertGroupNameQuery); |
269 | } | |
270 | } | |
d14ccbdc | 271 | $this->buildACLClause('contact_a'); |
6a488035 TO |
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 | ||
1cde5376 | 277 | $fromTail .= " INNER JOIN {$this->_Ig_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; |
6a488035 TO |
278 | |
279 | // now create a temp table to store the randomized contacts | |
1cde5376 SL |
280 | $this->_rand_table = CRM_Utils_SQL_TempTable::build()->setMemory()->setCategory('randSegment'); |
281 | $this->_rand_tableName = $this->_rand_table->getName(); | |
282 | $sql = "DROP TEMPORARY TABLE IF EXISTS {$this->_rand_tableName}"; | |
6a488035 | 283 | CRM_Core_DAO::executeQuery($sql); |
1cde5376 | 284 | $this->_rand_table->createWithColumns("id int PRIMARY KEY"); |
6a488035 TO |
285 | |
286 | if (substr($this->_segmentSize, -1) == '%') { | |
287 | $countSql = "SELECT DISTINCT contact_a.id $from $fromTail | |
288 | WHERE " . $this->where(); | |
353ffa53 TO |
289 | $dao = CRM_Core_DAO::executeQuery($countSql); |
290 | $totalSize = $dao->N; | |
6a488035 TO |
291 | $multiplier = substr($this->_segmentSize, 0, strlen($this->_segmentSize) - 1); |
292 | $multiplier /= 100; | |
6a488035 TO |
293 | $this->_segmentSize = round($totalSize * $multiplier); |
294 | } | |
295 | ||
1cde5376 | 296 | $sql = "INSERT INTO {$this->_rand_tableName} ( id ) |
6a488035 TO |
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 | ||
1cde5376 | 303 | $from = "FROM {$this->_rand_tableName} random"; |
6a488035 | 304 | |
d14ccbdc | 305 | $from .= " INNER JOIN civicrm_contact contact_a ON random.id = contact_a.id {$this->_aclFrom}"; |
6a488035 TO |
306 | |
307 | $from .= " $fromTail"; | |
308 | ||
309 | return $from; | |
d14ccbdc | 310 | |
6a488035 TO |
311 | } |
312 | ||
86538308 EM |
313 | /** |
314 | * @param bool $includeContactIDs | |
315 | * | |
316 | * @return string | |
317 | */ | |
00be9182 | 318 | public function where($includeContactIDs = FALSE) { |
47b8444f | 319 | $where = '(1)'; |
7bbd0c9d | 320 | |
47b8444f | 321 | if ($this->_aclWhere) { |
b3798d48 | 322 | $where .= " AND {$this->_aclWhere} "; |
47b8444f | 323 | } |
7bbd0c9d | 324 | |
6a488035 TO |
325 | return '(1)'; |
326 | } | |
327 | ||
86538308 EM |
328 | /** |
329 | * @return string | |
330 | */ | |
00be9182 | 331 | public function templateFile() { |
6a488035 TO |
332 | return 'CRM/Contact/Form/Search/Custom.tpl'; |
333 | } | |
334 | ||
86538308 EM |
335 | /** |
336 | * @param $title | |
337 | */ | |
00be9182 | 338 | public function setTitle($title) { |
6a488035 TO |
339 | if ($title) { |
340 | CRM_Utils_System::setTitle($title); | |
341 | } | |
342 | else { | |
343 | CRM_Utils_System::setTitle(ts('Search')); | |
344 | } | |
345 | } | |
346 | ||
dcc94695 EM |
347 | /** |
348 | * @return mixed | |
349 | */ | |
69078420 | 350 | |
bb271167 EM |
351 | /** |
352 | * @return mixed | |
353 | */ | |
00be9182 | 354 | public function count() { |
6a488035 TO |
355 | $sql = $this->all(); |
356 | ||
357 | $dao = CRM_Core_DAO::executeQuery($sql); | |
358 | return $dao->N; | |
359 | } | |
360 | ||
2e2605fe EM |
361 | /** |
362 | * Destructor function. | |
363 | */ | |
00be9182 | 364 | public function __destruct() { |
6a488035 | 365 | // the temporary tables are dropped automatically |
bb271167 | 366 | // so we don't do it here |
6a488035 | 367 | // but let mysql clean up |
389bcebf | 368 | return NULL; |
6a488035 | 369 | } |
96025800 | 370 | |
d14ccbdc SL |
371 | /** |
372 | * @param string $tableAlias | |
373 | */ | |
374 | public function buildACLClause($tableAlias = 'contact') { | |
375 | list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias); | |
376 | } | |
377 | ||
6a488035 | 378 | } |