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_PriceSet extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface { | |
18 | ||
19 | protected $_eventID = NULL; | |
d14ccbdc SL |
20 | protected $_aclFrom = NULL; |
21 | protected $_aclWhere = NULL; | |
4e54c348 PJ |
22 | protected $_tableName = NULL; |
23 | public $_permissionedComponent; | |
24 | ||
86538308 | 25 | /** |
5a409b50 | 26 | * Class constructor. |
27 | * | |
28 | * @param array $formValues | |
86538308 | 29 | */ |
00be9182 | 30 | public function __construct(&$formValues) { |
6a488035 TO |
31 | parent::__construct($formValues); |
32 | ||
33 | $this->_eventID = CRM_Utils_Array::value('event_id', | |
34 | $this->_formValues | |
35 | ); | |
36 | ||
37 | $this->setColumns(); | |
38 | ||
39 | if ($this->_eventID) { | |
40 | $this->buildTempTable(); | |
6a488035 TO |
41 | $this->fillTable(); |
42 | } | |
4e54c348 PJ |
43 | |
44 | // define component access permission needed | |
45 | $this->_permissionedComponent = 'CiviEvent'; | |
6a488035 TO |
46 | } |
47 | ||
00be9182 | 48 | public function __destruct() { |
6a488035 | 49 | /* |
e70a7fc0 TO |
50 | if ( $this->_eventID ) { |
51 | $sql = "DROP TEMPORARY TABLE {$this->_tableName}"; | |
52 | CRM_Core_DAO::executeQuery( $sql ); | |
53 | } | |
54 | */ | |
6a488035 TO |
55 | } |
56 | ||
00be9182 | 57 | public function buildTempTable() { |
bf45c45c | 58 | $sql = "id int unsigned NOT NULL AUTO_INCREMENT, |
6a488035 TO |
59 | contact_id int unsigned NOT NULL, |
60 | participant_id int unsigned NOT NULL, | |
61 | "; | |
62 | ||
63 | foreach ($this->_columns as $dontCare => $fieldName) { | |
be2fb01f | 64 | if (in_array($fieldName, [ |
6a488035 | 65 | 'contact_id', |
353ffa53 TO |
66 | 'participant_id', |
67 | 'display_name', | |
be2fb01f | 68 | ])) { |
6a488035 TO |
69 | continue; |
70 | } | |
71 | $sql .= "{$fieldName} int default 0,\n"; | |
72 | } | |
73 | ||
74 | $sql .= " | |
bf45c45c SL |
75 | PRIMARY KEY ( id ), |
76 | UNIQUE INDEX unique_participant_id ( participant_id )"; | |
6a488035 | 77 | |
bf45c45c | 78 | $this->_tableName = CRM_Utils_SQL_TempTable::build()->setCategory('priceset')->setMemory()->createWithColumns($sql)->getName(); |
6a488035 TO |
79 | } |
80 | ||
00be9182 | 81 | public function fillTable() { |
6a488035 TO |
82 | $sql = " |
83 | REPLACE INTO {$this->_tableName} | |
84 | ( contact_id, participant_id ) | |
85 | SELECT c.id, p.id | |
86 | FROM civicrm_contact c, | |
87 | civicrm_participant p | |
88 | WHERE p.contact_id = c.id | |
89 | AND p.is_test = 0 | |
c8275544 | 90 | AND p.event_id = %1 |
6a488035 TO |
91 | AND p.status_id NOT IN (4,11,12) |
92 | AND ( c.is_deleted = 0 OR c.is_deleted IS NULL ) | |
93 | "; | |
c8275544 | 94 | CRM_Core_DAO::executeQuery($sql, [1 => [$this->_eventID, 'Positive']]); |
6a488035 TO |
95 | |
96 | $sql = " | |
c8275544 PN |
97 | SELECT c.id as contact_id, |
98 | p.id as participant_id, | |
99 | l.price_field_value_id AS price_field_value_id, | |
100 | l.qty | |
101 | FROM civicrm_contact c | |
102 | INNER JOIN civicrm_participant p | |
103 | ON p.contact_id = c.id AND c.is_deleted = 0 | |
104 | INNER JOIN civicrm_line_item l | |
105 | ON p.id = l.entity_id AND l.entity_table ='civicrm_participant' | |
106 | INNER JOIN civicrm_price_field_value cpfv | |
107 | ON cpfv.id = l.price_field_value_id AND cpfv.is_active = 1 | |
108 | INNER JOIN civicrm_price_field cpf | |
109 | ON cpf.id = l.price_field_id AND cpf.is_active = 1 | |
110 | INNER JOIN civicrm_price_set cps | |
111 | ON cps.id = cpf.price_set_id AND cps.is_active = 1 | |
112 | WHERE p.event_id = %1 | |
113 | ORDER BY c.id, l.price_field_value_id; | |
114 | "; | |
115 | ||
116 | $dao = CRM_Core_DAO::executeQuery($sql, [1 => [$this->_eventID, 'Positive']]); | |
6a488035 TO |
117 | |
118 | // first store all the information by option value id | |
be2fb01f | 119 | $rows = []; |
6a488035 TO |
120 | while ($dao->fetch()) { |
121 | $contactID = $dao->contact_id; | |
122 | $participantID = $dao->participant_id; | |
123 | if (!isset($rows[$participantID])) { | |
be2fb01f | 124 | $rows[$participantID] = []; |
6a488035 TO |
125 | } |
126 | ||
127 | $rows[$participantID][] = "price_field_{$dao->price_field_value_id} = {$dao->qty}"; | |
128 | } | |
129 | ||
130 | foreach (array_keys($rows) as $participantID) { | |
131 | $values = implode(',', $rows[$participantID]); | |
63794520 | 132 | if ($values) { |
133 | $sql = " | |
6a488035 TO |
134 | UPDATE {$this->_tableName} |
135 | SET $values | |
136 | WHERE participant_id = $participantID; | |
137 | "; | |
63794520 | 138 | CRM_Core_DAO::executeQuery($sql); |
139 | } | |
6a488035 TO |
140 | } |
141 | } | |
142 | ||
86538308 | 143 | /** |
100fef9d | 144 | * @param int $eventID |
86538308 EM |
145 | * |
146 | * @return Object | |
147 | */ | |
00be9182 | 148 | public function priceSetDAO($eventID = NULL) { |
6a488035 TO |
149 | |
150 | // get all the events that have a price set associated with it | |
151 | $sql = " | |
152 | SELECT e.id as id, | |
153 | e.title as title, | |
154 | p.price_set_id as price_set_id | |
155 | FROM civicrm_event e, | |
156 | civicrm_price_set_entity p | |
157 | ||
158 | WHERE p.entity_table = 'civicrm_event' | |
159 | AND p.entity_id = e.id | |
160 | "; | |
161 | ||
be2fb01f | 162 | $params = []; |
6a488035 | 163 | if ($eventID) { |
be2fb01f | 164 | $params[1] = [$eventID, 'Integer']; |
6a488035 TO |
165 | $sql .= " AND e.id = $eventID"; |
166 | } | |
167 | ||
168 | $dao = CRM_Core_DAO::executeQuery($sql, | |
169 | $params | |
170 | ); | |
171 | return $dao; | |
172 | } | |
173 | ||
86538308 | 174 | /** |
c490a46a | 175 | * @param CRM_Core_Form $form |
86538308 EM |
176 | * |
177 | * @throws Exception | |
178 | */ | |
00be9182 | 179 | public function buildForm(&$form) { |
6a488035 TO |
180 | $dao = $this->priceSetDAO(); |
181 | ||
be2fb01f | 182 | $event = []; |
6a488035 TO |
183 | while ($dao->fetch()) { |
184 | $event[$dao->id] = $dao->title; | |
185 | } | |
186 | ||
187 | if (empty($event)) { | |
2d296f18 | 188 | CRM_Core_Error::statusBounce(ts('There are no events with Price Sets')); |
6a488035 TO |
189 | } |
190 | ||
191 | $form->add('select', | |
192 | 'event_id', | |
193 | ts('Event'), | |
194 | $event, | |
195 | TRUE | |
196 | ); | |
197 | ||
198 | /** | |
199 | * You can define a custom title for the search form | |
200 | */ | |
201 | $this->setTitle('Price Set Export'); | |
202 | ||
203 | /** | |
204 | * if you are using the standard template, this array tells the template what elements | |
205 | * are part of the search criteria | |
206 | */ | |
be2fb01f | 207 | $form->assign('elements', ['event_id']); |
6a488035 TO |
208 | } |
209 | ||
00be9182 | 210 | public function setColumns() { |
be2fb01f | 211 | $this->_columns = [ |
7b99ead3 | 212 | ts('Contact ID') => 'contact_id', |
aa6228f8 | 213 | ts('Participant ID') => 'participant_id', |
6a488035 | 214 | ts('Name') => 'display_name', |
be2fb01f | 215 | ]; |
6a488035 TO |
216 | |
217 | if (!$this->_eventID) { | |
218 | return; | |
219 | } | |
220 | ||
221 | // for the selected event, find the price set and all the columns associated with it. | |
222 | // create a column for each field and option group within it | |
223 | $dao = $this->priceSetDAO($this->_formValues['event_id']); | |
224 | ||
225 | if ($dao->fetch() && | |
226 | !$dao->price_set_id | |
227 | ) { | |
228 | CRM_Core_Error::fatal(ts('There are no events with Price Sets')); | |
229 | } | |
230 | ||
231 | // get all the fields and all the option values associated with it | |
9da8dc8c | 232 | $priceSet = CRM_Price_BAO_PriceSet::getSetDetail($dao->price_set_id); |
6a488035 TO |
233 | if (is_array($priceSet[$dao->price_set_id])) { |
234 | foreach ($priceSet[$dao->price_set_id]['fields'] as $key => $value) { | |
235 | if (is_array($value['options'])) { | |
236 | foreach ($value['options'] as $oKey => $oValue) { | |
9c1bc317 | 237 | $columnHeader = $value['label'] ?? NULL; |
6a488035 TO |
238 | if (CRM_Utils_Array::value('html_type', $value) != 'Text') { |
239 | $columnHeader .= ' - ' . $oValue['label']; | |
240 | } | |
241 | ||
242 | $this->_columns[$columnHeader] = "price_field_{$oValue['id']}"; | |
243 | } | |
244 | } | |
245 | } | |
246 | } | |
247 | } | |
248 | ||
86538308 EM |
249 | /** |
250 | * @return null | |
251 | */ | |
00be9182 | 252 | public function summary() { |
6a488035 TO |
253 | return NULL; |
254 | } | |
255 | ||
86538308 EM |
256 | /** |
257 | * @param int $offset | |
258 | * @param int $rowcount | |
259 | * @param null $sort | |
260 | * @param bool $includeContactIDs | |
261 | * @param bool $justIDs | |
262 | * | |
263 | * @return string | |
264 | */ | |
87a890cc | 265 | public function all( |
51ccfbbe | 266 | $offset = 0, $rowcount = 0, $sort = NULL, |
6a488035 TO |
267 | $includeContactIDs = FALSE, $justIDs = FALSE |
268 | ) { | |
269 | if ($justIDs) { | |
270 | $selectClause = "contact_a.id as contact_id"; | |
271 | } | |
272 | else { | |
273 | $selectClause = " | |
274 | contact_a.id as contact_id , | |
275 | contact_a.display_name as display_name"; | |
276 | ||
277 | foreach ($this->_columns as $dontCare => $fieldName) { | |
be2fb01f | 278 | if (in_array($fieldName, [ |
353ffa53 TO |
279 | 'contact_id', |
280 | 'display_name', | |
be2fb01f | 281 | ])) { |
6a488035 TO |
282 | continue; |
283 | } | |
284 | $selectClause .= ",\ntempTable.{$fieldName} as {$fieldName}"; | |
285 | } | |
286 | } | |
287 | ||
288 | return $this->sql($selectClause, | |
289 | $offset, $rowcount, $sort, | |
290 | $includeContactIDs, NULL | |
291 | ); | |
292 | } | |
293 | ||
86538308 EM |
294 | /** |
295 | * @return string | |
296 | */ | |
00be9182 | 297 | public function from() { |
d14ccbdc SL |
298 | $this->buildACLClause('contact_a'); |
299 | $from = " | |
6a488035 | 300 | FROM civicrm_contact contact_a |
d14ccbdc | 301 | INNER JOIN {$this->_tableName} tempTable ON ( tempTable.contact_id = contact_a.id ) {$this->_aclFrom} |
6a488035 | 302 | "; |
d14ccbdc | 303 | return $from; |
6a488035 TO |
304 | } |
305 | ||
86538308 EM |
306 | /** |
307 | * @param bool $includeContactIDs | |
308 | * | |
309 | * @return string | |
310 | */ | |
00be9182 | 311 | public function where($includeContactIDs = FALSE) { |
47b8444f SL |
312 | $where = ' ( 1 ) '; |
313 | if ($this->_aclWhere) { | |
b3798d48 | 314 | $where .= " AND {$this->_aclWhere} "; |
47b8444f SL |
315 | } |
316 | return $where; | |
6a488035 TO |
317 | } |
318 | ||
86538308 EM |
319 | /** |
320 | * @return string | |
321 | */ | |
00be9182 | 322 | public function templateFile() { |
6a488035 TO |
323 | return 'CRM/Contact/Form/Search/Custom.tpl'; |
324 | } | |
325 | ||
86538308 EM |
326 | /** |
327 | * @param $row | |
328 | */ | |
51ccfbbe TO |
329 | public function alterRow(&$row) { |
330 | } | |
6a488035 | 331 | |
86538308 EM |
332 | /** |
333 | * @param $title | |
334 | */ | |
00be9182 | 335 | public function setTitle($title) { |
6a488035 TO |
336 | if ($title) { |
337 | CRM_Utils_System::setTitle($title); | |
338 | } | |
339 | else { | |
340 | CRM_Utils_System::setTitle(ts('Export Price Set Info for an Event')); | |
341 | } | |
342 | } | |
96025800 | 343 | |
d14ccbdc SL |
344 | /** |
345 | * @param string $tableAlias | |
346 | */ | |
347 | public function buildACLClause($tableAlias = 'contact') { | |
348 | list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias); | |
349 | } | |
350 | ||
6a488035 | 351 | } |