Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
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 | * $Id$ |
17 | * | |
18 | */ | |
19 | class CRM_Report_Form_Contact_Relationship extends CRM_Report_Form { | |
20 | ||
21 | protected $_summary = NULL; | |
22 | protected $_emailField_a = FALSE; | |
23 | protected $_emailField_b = FALSE; | |
7393ba64 M |
24 | protected $_phoneField_a = FALSE; |
25 | protected $_phoneField_b = FALSE; | |
be2fb01f | 26 | protected $_customGroupExtends = [ |
21dfd5f5 | 27 | 'Relationship', |
be2fb01f CW |
28 | ]; |
29 | public $_drilldownReport = ['contact/detail' => 'Link to Detail Report']; | |
2f4c2f5d | 30 | |
1728e9a0 | 31 | /** |
32 | * This report has not been optimised for group filtering. | |
33 | * | |
34 | * The functionality for group filtering has been improved but not | |
35 | * all reports have been adjusted to take care of it. This report has not | |
36 | * and will run an inefficient query until fixed. | |
37 | * | |
38 | * CRM-19170 | |
39 | * | |
40 | * @var bool | |
41 | */ | |
42 | protected $groupFilterNotOptimised = TRUE; | |
43 | ||
55f71fa7 | 44 | /** |
45 | * This will be a_b or b_a. | |
46 | * | |
47 | * @var string | |
48 | */ | |
49 | protected $relationType; | |
50 | ||
70599df6 | 51 | /** |
52 | * Class constructor. | |
53 | */ | |
00be9182 | 54 | public function __construct() { |
6a488035 TO |
55 | |
56 | $contact_type = CRM_Contact_BAO_ContactType::getSelectElements(FALSE, TRUE, '_'); | |
57 | ||
be2fb01f CW |
58 | $this->_columns = [ |
59 | 'civicrm_contact' => [ | |
6a488035 | 60 | 'dao' => 'CRM_Contact_DAO_Contact', |
be2fb01f CW |
61 | 'fields' => [ |
62 | 'sort_name_a' => [ | |
9d72cede | 63 | 'title' => ts('Contact A'), |
6a488035 TO |
64 | 'name' => 'sort_name', |
65 | 'required' => TRUE, | |
be2fb01f CW |
66 | ], |
67 | 'display_name_a' => [ | |
16e98ad0 SL |
68 | 'title' => ts('Contact A Full Name'), |
69 | 'name' => 'display_name', | |
be2fb01f CW |
70 | ], |
71 | 'id' => [ | |
6a488035 TO |
72 | 'no_display' => TRUE, |
73 | 'required' => TRUE, | |
be2fb01f CW |
74 | ], |
75 | 'contact_type_a' => [ | |
30f85891 RN |
76 | 'title' => ts('Contact Type (Contact A)'), |
77 | 'name' => 'contact_type', | |
be2fb01f CW |
78 | ], |
79 | 'contact_sub_type_a' => [ | |
b8f96eb8 | 80 | 'title' => ts('Contact Subtype (Contact A)'), |
30f85891 | 81 | 'name' => 'contact_sub_type', |
be2fb01f CW |
82 | ], |
83 | ], | |
84 | 'filters' => [ | |
85 | 'sort_name_a' => [ | |
9d72cede | 86 | 'title' => ts('Contact A'), |
6a488035 TO |
87 | 'name' => 'sort_name', |
88 | 'operator' => 'like', | |
89 | 'type' => CRM_Report_Form::OP_STRING, | |
be2fb01f CW |
90 | ], |
91 | 'contact_type_a' => [ | |
9d72cede | 92 | 'title' => ts('Contact Type A'), |
bc1f381d BS |
93 | 'name' => 'contact_type', |
94 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
95 | 'options' => $contact_type, | |
96 | 'type' => CRM_Utils_Type::T_STRING, | |
be2fb01f CW |
97 | ], |
98 | ], | |
99 | 'order_bys' => [ | |
100 | 'sort_name_a' => [ | |
6f5155f0 BS |
101 | 'title' => ts('Contact A'), |
102 | 'name' => 'sort_name', | |
103 | 'default_weight' => '1', | |
be2fb01f CW |
104 | ], |
105 | ], | |
70bea8e2 | 106 | 'grouping' => 'contact_a_fields', |
be2fb01f CW |
107 | ], |
108 | 'civicrm_contact_b' => [ | |
6a488035 TO |
109 | 'dao' => 'CRM_Contact_DAO_Contact', |
110 | 'alias' => 'contact_b', | |
be2fb01f CW |
111 | 'fields' => [ |
112 | 'sort_name_b' => [ | |
9d72cede | 113 | 'title' => ts('Contact B'), |
6a488035 TO |
114 | 'name' => 'sort_name', |
115 | 'required' => TRUE, | |
be2fb01f CW |
116 | ], |
117 | 'display_name_b' => [ | |
16e98ad0 SL |
118 | 'title' => ts('Contact B Full Name'), |
119 | 'name' => 'display_name', | |
be2fb01f CW |
120 | ], |
121 | 'id' => [ | |
6a488035 TO |
122 | 'no_display' => TRUE, |
123 | 'required' => TRUE, | |
be2fb01f CW |
124 | ], |
125 | 'contact_type_b' => [ | |
30f85891 RN |
126 | 'title' => ts('Contact Type (Contact B)'), |
127 | 'name' => 'contact_type', | |
be2fb01f CW |
128 | ], |
129 | 'contact_sub_type_b' => [ | |
b8f96eb8 | 130 | 'title' => ts('Contact Subtype (Contact B)'), |
30f85891 | 131 | 'name' => 'contact_sub_type', |
be2fb01f CW |
132 | ], |
133 | ], | |
134 | 'filters' => [ | |
135 | 'sort_name_b' => [ | |
9d72cede | 136 | 'title' => ts('Contact B'), |
6a488035 TO |
137 | 'name' => 'sort_name', |
138 | 'operator' => 'like', | |
139 | 'type' => CRM_Report_Form::OP_STRING, | |
be2fb01f CW |
140 | ], |
141 | 'contact_type_b' => [ | |
9d72cede | 142 | 'title' => ts('Contact Type B'), |
bc1f381d BS |
143 | 'name' => 'contact_type', |
144 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
145 | 'options' => $contact_type, | |
146 | 'type' => CRM_Utils_Type::T_STRING, | |
be2fb01f CW |
147 | ], |
148 | ], | |
149 | 'order_bys' => [ | |
150 | 'sort_name_b' => [ | |
6f5155f0 BS |
151 | 'title' => ts('Contact B'), |
152 | 'name' => 'sort_name', | |
153 | 'default_weight' => '2', | |
be2fb01f CW |
154 | ], |
155 | ], | |
70bea8e2 | 156 | 'grouping' => 'contact_b_fields', |
be2fb01f CW |
157 | ], |
158 | 'civicrm_email' => [ | |
6a488035 | 159 | 'dao' => 'CRM_Core_DAO_Email', |
be2fb01f CW |
160 | 'fields' => [ |
161 | 'email_a' => [ | |
9d72cede | 162 | 'title' => ts('Email (Contact A)'), |
6a488035 | 163 | 'name' => 'email', |
be2fb01f CW |
164 | ], |
165 | ], | |
70bea8e2 | 166 | 'grouping' => 'contact_a_fields', |
be2fb01f CW |
167 | ], |
168 | 'civicrm_email_b' => [ | |
6a488035 TO |
169 | 'dao' => 'CRM_Core_DAO_Email', |
170 | 'alias' => 'email_b', | |
be2fb01f CW |
171 | 'fields' => [ |
172 | 'email_b' => [ | |
9d72cede | 173 | 'title' => ts('Email (Contact B)'), |
6a488035 | 174 | 'name' => 'email', |
be2fb01f CW |
175 | ], |
176 | ], | |
70bea8e2 | 177 | 'grouping' => 'contact_b_fields', |
be2fb01f CW |
178 | ], |
179 | 'civicrm_phone' => [ | |
7393ba64 M |
180 | 'dao' => 'CRM_Core_DAO_Phone', |
181 | 'alias' => 'phone_a', | |
be2fb01f CW |
182 | 'fields' => [ |
183 | 'phone_a' => [ | |
a9315b5e | 184 | 'title' => ts('Phone (Contact A)'), |
7393ba64 | 185 | 'name' => 'phone', |
be2fb01f CW |
186 | ], |
187 | 'phone_ext_a' => [ | |
a9315b5e | 188 | 'title' => ts('Phone Ext (Contact A)'), |
7393ba64 | 189 | 'name' => 'phone_ext', |
be2fb01f CW |
190 | ], |
191 | ], | |
70bea8e2 | 192 | 'grouping' => 'contact_a_fields', |
be2fb01f CW |
193 | ], |
194 | 'civicrm_phone_b' => [ | |
7393ba64 M |
195 | 'dao' => 'CRM_Core_DAO_Phone', |
196 | 'alias' => 'phone_b', | |
be2fb01f CW |
197 | 'fields' => [ |
198 | 'phone_b' => [ | |
a9315b5e | 199 | 'title' => ts('Phone (Contact B)'), |
21dfd5f5 | 200 | 'name' => 'phone', |
be2fb01f CW |
201 | ], |
202 | 'phone_ext_b' => [ | |
a9315b5e | 203 | 'title' => ts('Phone Ext (Contact B)'), |
21dfd5f5 | 204 | 'name' => 'phone_ext', |
be2fb01f CW |
205 | ], |
206 | ], | |
70bea8e2 | 207 | 'grouping' => 'contact_b_fields', |
be2fb01f CW |
208 | ], |
209 | 'civicrm_relationship_type' => [ | |
6a488035 | 210 | 'dao' => 'CRM_Contact_DAO_RelationshipType', |
be2fb01f CW |
211 | 'fields' => [ |
212 | 'label_a_b' => [ | |
9d72cede | 213 | 'title' => ts('Relationship A-B '), |
6a488035 | 214 | 'default' => TRUE, |
be2fb01f CW |
215 | ], |
216 | 'label_b_a' => [ | |
9d72cede | 217 | 'title' => ts('Relationship B-A '), |
6a488035 | 218 | 'default' => TRUE, |
be2fb01f CW |
219 | ], |
220 | ], | |
221 | 'order_bys' => [ | |
222 | 'label_a_b' => [ | |
6f5155f0 BS |
223 | 'title' => ts('Relationship A-B'), |
224 | 'name' => 'label_a_b', | |
be2fb01f CW |
225 | ], |
226 | 'label_b_A' => [ | |
6f5155f0 BS |
227 | 'title' => ts('Relationship B-A'), |
228 | 'name' => 'label_b_a', | |
be2fb01f CW |
229 | ], |
230 | ], | |
6a488035 | 231 | 'grouping' => 'relation-fields', |
be2fb01f CW |
232 | ], |
233 | 'civicrm_relationship' => [ | |
6a488035 | 234 | 'dao' => 'CRM_Contact_DAO_Relationship', |
be2fb01f CW |
235 | 'fields' => [ |
236 | 'start_date' => [ | |
9d72cede | 237 | 'title' => ts('Relationship Start Date'), |
be2fb01f CW |
238 | ], |
239 | 'end_date' => [ | |
9d72cede | 240 | 'title' => ts('Relationship End Date'), |
be2fb01f CW |
241 | ], |
242 | 'is_permission_a_b' => [ | |
f871c3a9 | 243 | 'title' => ts('Permission A has to access B'), |
be2fb01f CW |
244 | ], |
245 | 'is_permission_b_a' => [ | |
f871c3a9 | 246 | 'title' => ts('Permission B has to access A'), |
be2fb01f CW |
247 | ], |
248 | 'description' => [ | |
9d72cede | 249 | 'title' => ts('Description'), |
be2fb01f | 250 | ], |
dbab7f84 | 251 | 'is_active' => [ |
252 | 'title' => ts('Is active?'), | |
363a1cb3 | 253 | 'type' => CRM_Utils_Type::T_BOOLEAN, |
dbab7f84 | 254 | ], |
be2fb01f | 255 | 'relationship_id' => [ |
a9315b5e DG |
256 | 'title' => ts('Rel ID'), |
257 | 'name' => 'id', | |
be2fb01f CW |
258 | ], |
259 | ], | |
260 | 'filters' => [ | |
261 | 'is_active' => [ | |
9d72cede | 262 | 'title' => ts('Relationship Status'), |
6a488035 | 263 | 'operatorType' => CRM_Report_Form::OP_SELECT, |
be2fb01f | 264 | 'options' => [ |
ccc29f8e | 265 | '' => ts('- Any -'), |
266 | 1 => ts('Active'), | |
267 | 0 => ts('Inactive'), | |
be2fb01f | 268 | ], |
6a488035 | 269 | 'type' => CRM_Utils_Type::T_INT, |
be2fb01f CW |
270 | ], |
271 | 'is_valid' => [ | |
7c41e675 | 272 | 'title' => ts('Relationship Dates Validity'), |
273 | 'operatorType' => CRM_Report_Form::OP_SELECT, | |
be2fb01f | 274 | 'options' => [ |
7c41e675 | 275 | NULL => ts('- Any -'), |
276 | 1 => ts('Not expired'), | |
277 | 0 => ts('Expired'), | |
be2fb01f | 278 | ], |
7c41e675 | 279 | 'type' => CRM_Utils_Type::T_INT, |
be2fb01f CW |
280 | ], |
281 | 'relationship_type_id' => [ | |
9d72cede | 282 | 'title' => ts('Relationship'), |
02cbed89 | 283 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
0ddfa7be | 284 | 'options' => CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, NULL, NULL, NULL, TRUE), |
6a488035 | 285 | 'type' => CRM_Utils_Type::T_INT, |
be2fb01f CW |
286 | ], |
287 | 'start_date' => [ | |
7c41e675 | 288 | 'title' => ts('Start Date'), |
289 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
290 | ], |
291 | 'end_date' => [ | |
7c41e675 | 292 | 'title' => ts('End Date'), |
293 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
294 | ], |
295 | 'active_period_date' => [ | |
09ed300e | 296 | 'title' => ts('Active Period'), |
297 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
298 | ], |
299 | 'is_permission_a_b' => [ | |
7d56e11e | 300 | 'title' => ts('Does contact A have permission over contact B?'), |
f871c3a9 AS |
301 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
302 | 'options' => CRM_Contact_BAO_Relationship::buildOptions('is_permission_a_b'), | |
7d56e11e | 303 | 'type' => CRM_Utils_Type::T_INT, |
be2fb01f CW |
304 | ], |
305 | 'is_permission_b_a' => [ | |
7d56e11e | 306 | 'title' => ts('Does contact B have permission over contact A?'), |
f871c3a9 AS |
307 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
308 | 'options' => CRM_Contact_BAO_Relationship::buildOptions('is_permission_b_a'), | |
7d56e11e | 309 | 'type' => CRM_Utils_Type::T_INT, |
be2fb01f CW |
310 | ], |
311 | ], | |
7d56e11e | 312 | |
be2fb01f CW |
313 | 'order_bys' => [ |
314 | 'start_date' => [ | |
7d56e11e | 315 | 'title' => ts('Start Date'), |
316 | 'name' => 'start_date', | |
be2fb01f | 317 | ], |
9b78e56b | 318 | 'end_date' => [ |
319 | 'title' => ts('End Date'), | |
320 | 'name' => 'end_date', | |
321 | ], | |
be2fb01f | 322 | ], |
6a488035 | 323 | 'grouping' => 'relation-fields', |
be2fb01f CW |
324 | ], |
325 | 'civicrm_address' => [ | |
6a488035 | 326 | 'dao' => 'CRM_Core_DAO_Address', |
be2fb01f CW |
327 | 'filters' => [ |
328 | 'country_id' => [ | |
9d72cede | 329 | 'title' => ts('Country'), |
8ee006e7 | 330 | 'type' => CRM_Utils_Type::T_INT, |
6a488035 TO |
331 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
332 | 'options' => CRM_Core_PseudoConstant::country(), | |
be2fb01f CW |
333 | ], |
334 | 'state_province_id' => [ | |
9d72cede | 335 | 'title' => ts('State/Province'), |
8ee006e7 | 336 | 'type' => CRM_Utils_Type::T_INT, |
6a488035 TO |
337 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
338 | 'options' => CRM_Core_PseudoConstant::stateProvince(), | |
be2fb01f CW |
339 | ], |
340 | ], | |
6a488035 | 341 | 'grouping' => 'contact-fields', |
be2fb01f CW |
342 | ], |
343 | ]; | |
6a488035 | 344 | |
16e2e80c | 345 | $this->_groupFilter = TRUE; |
6a488035 TO |
346 | $this->_tagFilter = TRUE; |
347 | parent::__construct(); | |
348 | } | |
349 | ||
00be9182 | 350 | public function preProcess() { |
6a488035 TO |
351 | parent::preProcess(); |
352 | } | |
353 | ||
00be9182 | 354 | public function select() { |
be2fb01f | 355 | $select = $this->_columnHeaders = []; |
6a488035 TO |
356 | foreach ($this->_columns as $tableName => $table) { |
357 | if (array_key_exists('fields', $table)) { | |
358 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
359 | if (!empty($field['required']) || |
360 | !empty($this->_params['fields'][$fieldName]) | |
361 | ) { | |
6a488035 TO |
362 | |
363 | if ($fieldName == 'email_a') { | |
364 | $this->_emailField_a = TRUE; | |
365 | } | |
366 | if ($fieldName == 'email_b') { | |
367 | $this->_emailField_b = TRUE; | |
368 | } | |
9d72cede | 369 | if ($fieldName == 'phone_a') { |
7393ba64 M |
370 | $this->_phoneField_a = TRUE; |
371 | } | |
9d72cede | 372 | if ($fieldName == 'phone_b') { |
7393ba64 M |
373 | $this->_phoneField_b = TRUE; |
374 | } | |
6a488035 | 375 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; |
9c1bc317 CW |
376 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL; |
377 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL; | |
6a488035 TO |
378 | } |
379 | } | |
380 | } | |
381 | } | |
d1641c51 | 382 | $this->_selectClauses = $select; |
6a488035 TO |
383 | |
384 | $this->_select = "SELECT " . implode(', ', $select) . " "; | |
385 | } | |
386 | ||
00be9182 | 387 | public function from() { |
6a488035 TO |
388 | $this->_from = " |
389 | FROM civicrm_relationship {$this->_aliases['civicrm_relationship']} | |
2f4c2f5d | 390 | |
6a488035 | 391 | INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} |
2f4c2f5d | 392 | ON ( {$this->_aliases['civicrm_relationship']}.contact_id_a = |
6a488035 TO |
393 | {$this->_aliases['civicrm_contact']}.id ) |
394 | ||
395 | INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact_b']} | |
2f4c2f5d | 396 | ON ( {$this->_aliases['civicrm_relationship']}.contact_id_b = |
6a488035 TO |
397 | {$this->_aliases['civicrm_contact_b']}.id ) |
398 | ||
399 | {$this->_aclFrom} "; | |
400 | ||
401 | if (!empty($this->_params['country_id_value']) || | |
402 | !empty($this->_params['state_province_id_value']) | |
403 | ) { | |
404 | $this->_from .= " | |
2f4c2f5d | 405 | INNER JOIN civicrm_address {$this->_aliases['civicrm_address']} |
6a488035 TO |
406 | ON (( {$this->_aliases['civicrm_address']}.contact_id = |
407 | {$this->_aliases['civicrm_contact']}.id OR | |
408 | {$this->_aliases['civicrm_address']}.contact_id = | |
2f4c2f5d | 409 | {$this->_aliases['civicrm_contact_b']}.id ) AND |
6a488035 TO |
410 | {$this->_aliases['civicrm_address']}.is_primary = 1 ) "; |
411 | } | |
412 | ||
413 | $this->_from .= " | |
414 | INNER JOIN civicrm_relationship_type {$this->_aliases['civicrm_relationship_type']} | |
2f4c2f5d | 415 | ON ( {$this->_aliases['civicrm_relationship']}.relationship_type_id = |
6a488035 TO |
416 | {$this->_aliases['civicrm_relationship_type']}.id ) "; |
417 | ||
eaafc14c | 418 | // Include Email Field. |
6a488035 | 419 | if ($this->_emailField_a) { |
2f4c2f5d | 420 | $this->_from .= " |
6a488035 TO |
421 | LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} |
422 | ON ( {$this->_aliases['civicrm_contact']}.id = | |
2f4c2f5d | 423 | {$this->_aliases['civicrm_email']}.contact_id AND |
6a488035 TO |
424 | {$this->_aliases['civicrm_email']}.is_primary = 1 )"; |
425 | } | |
426 | if ($this->_emailField_b) { | |
2f4c2f5d | 427 | $this->_from .= " |
428 | LEFT JOIN civicrm_email {$this->_aliases['civicrm_email_b']} | |
6a488035 | 429 | ON ( {$this->_aliases['civicrm_contact_b']}.id = |
2f4c2f5d | 430 | {$this->_aliases['civicrm_email_b']}.contact_id AND |
6a488035 TO |
431 | {$this->_aliases['civicrm_email_b']}.is_primary = 1 )"; |
432 | } | |
eaafc14c | 433 | // Include Phone Field. |
7393ba64 M |
434 | if ($this->_phoneField_a) { |
435 | $this->_from .= " | |
436 | LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} | |
437 | ON ( {$this->_aliases['civicrm_contact']}.id = | |
438 | {$this->_aliases['civicrm_phone']}.contact_id AND | |
439 | {$this->_aliases['civicrm_phone']}.is_primary = 1 )"; | |
440 | } | |
441 | if ($this->_phoneField_b) { | |
442 | $this->_from .= " | |
443 | LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone_b']} | |
444 | ON ( {$this->_aliases['civicrm_contact_b']}.id = | |
445 | {$this->_aliases['civicrm_phone_b']}.contact_id AND | |
446 | {$this->_aliases['civicrm_phone_b']}.is_primary = 1 )"; | |
447 | } | |
6a488035 TO |
448 | } |
449 | ||
00be9182 | 450 | public function where() { |
be2fb01f | 451 | $whereClauses = $havingClauses = []; |
6a488035 TO |
452 | foreach ($this->_columns as $tableName => $table) { |
453 | if (array_key_exists('filters', $table)) { | |
454 | foreach ($table['filters'] as $fieldName => $field) { | |
455 | ||
456 | $clause = NULL; | |
457 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
9c1bc317 CW |
458 | $relative = $this->_params["{$fieldName}_relative"] ?? NULL; |
459 | $from = $this->_params["{$fieldName}_from"] ?? NULL; | |
460 | $to = $this->_params["{$fieldName}_to"] ?? NULL; | |
6a488035 | 461 | |
eea5db81 | 462 | if ($fieldName == 'active_period_date') { |
463 | $clause = $this->activeClause($field['name'], $relative, $from, $to, $field['type']); | |
464 | } | |
465 | else { | |
466 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
467 | } | |
6a488035 TO |
468 | } |
469 | else { | |
9c1bc317 | 470 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 | 471 | if ($op) { |
9d72cede EM |
472 | if (($tableName == 'civicrm_contact' || |
473 | $tableName == 'civicrm_contact_b') && | |
474 | ($fieldName == 'contact_type_a' || | |
475 | $fieldName == 'contact_type_b') | |
6a488035 | 476 | ) { |
9c1bc317 | 477 | $cTypes = $this->_params["{$fieldName}_value"] ?? NULL; |
be2fb01f | 478 | $contactTypes = $contactSubTypes = []; |
6a488035 TO |
479 | if (!empty($cTypes)) { |
480 | foreach ($cTypes as $ctype) { | |
481 | $getTypes = CRM_Utils_System::explode('_', $ctype, 2); | |
9d72cede EM |
482 | if ($getTypes[1] && |
483 | !in_array($getTypes[1], $contactSubTypes) | |
484 | ) { | |
6a488035 TO |
485 | $contactSubTypes[] = $getTypes[1]; |
486 | } | |
9d72cede EM |
487 | elseif ($getTypes[0] && |
488 | !in_array($getTypes[0], $contactTypes) | |
489 | ) { | |
6a488035 TO |
490 | $contactTypes[] = $getTypes[0]; |
491 | } | |
492 | } | |
493 | } | |
494 | ||
495 | if (!empty($contactTypes)) { | |
496 | $clause = $this->whereClause($field, | |
497 | $op, | |
498 | $contactTypes, | |
499 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
500 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
501 | ); | |
502 | } | |
503 | ||
504 | if (!empty($contactSubTypes)) { | |
bc1f381d | 505 | $field['name'] = 'contact_sub_type'; |
6a488035 TO |
506 | $field['dbAlias'] = $field['alias'] . '.' . $field['name']; |
507 | $subTypeClause = $this->whereClause($field, | |
508 | $op, | |
509 | $contactSubTypes, | |
510 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
511 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
512 | ); | |
513 | if ($clause) { | |
514 | $clause = '(' . $clause . ' OR ' . $subTypeClause . ')'; | |
515 | } | |
516 | else { | |
517 | $clause = $subTypeClause; | |
518 | } | |
519 | } | |
520 | } | |
521 | else { | |
e93563f1 | 522 | if ($fieldName == 'is_valid') { |
7c41e675 | 523 | $clause = $this->buildValidityQuery(CRM_Utils_Array::value("{$fieldName}_value", $this->_params)); |
524 | } | |
525 | else { | |
526 | $clause = $this->whereClause($field, | |
527 | $op, | |
528 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
529 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
530 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
531 | ); | |
532 | } | |
6a488035 TO |
533 | } |
534 | } | |
535 | } | |
536 | ||
537 | if (!empty($clause)) { | |
a7488080 | 538 | if (!empty($field['having'])) { |
6a488035 TO |
539 | $havingClauses[] = $clause; |
540 | } | |
541 | else { | |
542 | $whereClauses[] = $clause; | |
543 | } | |
544 | } | |
545 | } | |
546 | } | |
547 | } | |
7d56e11e | 548 | $this->_where = "WHERE ( {$this->_aliases['civicrm_contact']}.is_deleted = 0 AND {$this->_aliases['civicrm_contact_b']}.is_deleted = 0 ) "; |
549 | if ($whereClauses) { | |
550 | $this->_where .= ' AND ' . implode(' AND ', $whereClauses); | |
6a488035 TO |
551 | } |
552 | else { | |
7d56e11e | 553 | $this->_having = ''; |
6a488035 TO |
554 | } |
555 | ||
556 | if ($this->_aclWhere) { | |
557 | $this->_where .= " AND {$this->_aclWhere} "; | |
558 | } | |
559 | ||
560 | if (!empty($havingClauses)) { | |
561 | // use this clause to construct group by clause. | |
562 | $this->_having = 'HAVING ' . implode(' AND ', $havingClauses); | |
563 | } | |
564 | } | |
565 | ||
74cf4551 EM |
566 | /** |
567 | * @param $rows | |
568 | * | |
569 | * @return array | |
570 | */ | |
00be9182 | 571 | public function statistics(&$rows) { |
6a488035 TO |
572 | $statistics = parent::statistics($rows); |
573 | ||
574 | $isStatusFilter = FALSE; | |
575 | $relStatus = NULL; | |
576 | if (CRM_Utils_Array::value('is_active_value', $this->_params) == '1') { | |
e60e0c01 | 577 | $relStatus = ts('Is equal to Active'); |
6a488035 TO |
578 | } |
579 | elseif (CRM_Utils_Array::value('is_active_value', $this->_params) == '0') { | |
e60e0c01 | 580 | $relStatus = ts('Is equal to Inactive'); |
6a488035 | 581 | } |
a7488080 | 582 | if (!empty($statistics['filters'])) { |
6a488035 | 583 | foreach ($statistics['filters'] as $id => $value) { |
eaafc14c | 584 | // For displaying relationship type filter. |
6a488035 TO |
585 | if ($value['title'] == 'Relationship') { |
586 | $relTypes = CRM_Core_PseudoConstant::relationshipType(); | |
ccc29f8e | 587 | $op = CRM_Utils_Array::value('relationship_type_id_op', $this->_params) == 'in' ? ts('Is one of') . ' ' : ts('Is not one of') . ' '; |
be2fb01f | 588 | $relationshipTypes = []; |
02cbed89 SL |
589 | foreach ($this->_params['relationship_type_id_value'] as $relationship) { |
590 | $relationshipTypes[] = $relTypes[$relationship]['label_' . $this->relationType]; | |
591 | } | |
592 | $statistics['filters'][$id]['value'] = $op . | |
593 | implode(', ', $relationshipTypes); | |
6a488035 TO |
594 | } |
595 | ||
eaafc14c | 596 | // For displaying relationship status. |
6a488035 TO |
597 | if ($value['title'] == 'Relationship Status') { |
598 | $isStatusFilter = TRUE; | |
599 | $statistics['filters'][$id]['value'] = $relStatus; | |
600 | } | |
601 | } | |
602 | } | |
eaafc14c | 603 | // For displaying relationship status. |
6a488035 | 604 | if (!$isStatusFilter && $relStatus) { |
be2fb01f | 605 | $statistics['filters'][] = [ |
e300cf31 | 606 | 'title' => ts('Relationship Status'), |
6a488035 | 607 | 'value' => $relStatus, |
be2fb01f | 608 | ]; |
6a488035 TO |
609 | } |
610 | return $statistics; | |
611 | } | |
612 | ||
00be9182 | 613 | public function groupBy() { |
6a488035 | 614 | $this->_groupBy = " "; |
be2fb01f | 615 | $groupBy = []; |
6a488035 TO |
616 | if ($this->relationType == 'a_b') { |
617 | $groupBy[] = " {$this->_aliases['civicrm_contact']}.id"; | |
618 | } | |
619 | elseif ($this->relationType == 'b_a') { | |
620 | $groupBy[] = " {$this->_aliases['civicrm_contact_b']}.id"; | |
621 | } | |
622 | ||
623 | if (!empty($groupBy)) { | |
d1641c51 | 624 | $groupBy[] = "{$this->_aliases['civicrm_relationship']}.id"; |
6a488035 TO |
625 | } |
626 | else { | |
be2fb01f | 627 | $groupBy = ["{$this->_aliases['civicrm_relationship']}.id"]; |
6a488035 | 628 | } |
d1641c51 | 629 | |
b708c08d | 630 | $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); |
6a488035 TO |
631 | } |
632 | ||
99a555a5 | 633 | public function beginPostProcessCommon() { |
9c1bc317 | 634 | $originalRelationshipTypeIdValue = $this->_params['relationship_type_id_value'] ?? NULL; |
13415144 | 635 | if ($originalRelationshipTypeIdValue) { |
be2fb01f CW |
636 | $relationshipTypes = []; |
637 | $direction = []; | |
13415144 | 638 | foreach ((array) $originalRelationshipTypeIdValue as $relationship_type) { |
02cbed89 SL |
639 | $relType = explode('_', $relationship_type); |
640 | $direction[] = $relType[1] . '_' . $relType[2]; | |
641 | $relationshipTypes[] = intval($relType[0]); | |
642 | } | |
eaafc14c | 643 | // Lets take the first relationship type to guide us in the relationship |
644 | // direction we should use. | |
02cbed89 SL |
645 | $this->relationType = $direction[0]; |
646 | $this->_params['relationship_type_id_value'] = $relationshipTypes; | |
6a488035 | 647 | } |
99a555a5 ML |
648 | } |
649 | ||
650 | public function postProcess() { | |
651 | $this->beginPostProcess(); | |
6a488035 | 652 | |
be2fb01f | 653 | $this->buildACLClause([ |
353ffa53 TO |
654 | $this->_aliases['civicrm_contact'], |
655 | $this->_aliases['civicrm_contact_b'], | |
be2fb01f | 656 | ]); |
99a555a5 | 657 | |
6a488035 TO |
658 | $sql = $this->buildQuery(); |
659 | $this->buildRows($sql, $rows); | |
660 | ||
661 | $this->formatDisplay($rows); | |
662 | $this->doTemplateAssignment($rows); | |
663 | ||
674b20c9 | 664 | if (!empty($originalRelationshipTypeIdValue)) { |
eaafc14c | 665 | // Store its old value, CRM-5837. |
674b20c9 | 666 | $this->_params['relationship_type_id_value'] = $originalRelationshipTypeIdValue; |
6a488035 TO |
667 | } |
668 | $this->endPostProcess($rows); | |
669 | } | |
670 | ||
74cf4551 | 671 | /** |
70bea8e2 | 672 | * @param $rows |
74cf4551 | 673 | */ |
00be9182 | 674 | public function alterDisplay(&$rows) { |
eaafc14c | 675 | // Custom code to alter rows. |
6a488035 TO |
676 | $entryFound = FALSE; |
677 | ||
678 | foreach ($rows as $rowNum => $row) { | |
679 | ||
37935ea7 WM |
680 | // Handle ID to label conversion for contact fields |
681 | $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contact/relationship', 'View Relationships') ? TRUE : $entryFound; | |
682 | ||
683 | // Handle contact subtype A | |
7ed79ffa | 684 | // @todo refactor into separate function |
37935ea7 WM |
685 | if (array_key_exists('civicrm_contact_contact_sub_type_a', $row)) { |
686 | if ($value = $row['civicrm_contact_contact_sub_type_a']) { | |
687 | $rowValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value); | |
be2fb01f | 688 | $rowLabels = []; |
37935ea7 WM |
689 | foreach ($rowValues as $rowValue) { |
690 | if ($rowValue) { | |
4a413eb6 | 691 | $rowLabels[] = CRM_Core_PseudoConstant::getLabel('CRM_Contact_BAO_Contact', 'contact_sub_type', $rowValue); |
37935ea7 WM |
692 | } |
693 | } | |
694 | $rows[$rowNum]['civicrm_contact_contact_sub_type_a'] = implode(', ', $rowLabels); | |
695 | } | |
696 | $entryFound = TRUE; | |
697 | } | |
698 | ||
699 | // Handle contact subtype B | |
7ed79ffa | 700 | // @todo refactor into separate function |
37935ea7 WM |
701 | if (array_key_exists('civicrm_contact_b_contact_sub_type_b', $row)) { |
702 | if ($value = $row['civicrm_contact_b_contact_sub_type_b']) { | |
703 | $rowValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value); | |
be2fb01f | 704 | $rowLabels = []; |
37935ea7 WM |
705 | foreach ($rowValues as $rowValue) { |
706 | if ($rowValue) { | |
4a413eb6 | 707 | $rowLabels[] = CRM_Core_PseudoConstant::getLabel('CRM_Contact_BAO_Contact', 'contact_sub_type', $rowValue); |
37935ea7 WM |
708 | } |
709 | } | |
710 | $rows[$rowNum]['civicrm_contact_b_contact_sub_type_b'] = implode(', ', $rowLabels); | |
711 | } | |
712 | $entryFound = TRUE; | |
713 | } | |
714 | ||
94d31384 | 715 | $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound; |
6a488035 | 716 | |
7ed79ffa WM |
717 | // Handle contact name A |
718 | // @todo refactor into separate function | |
6a488035 TO |
719 | if (array_key_exists('civicrm_contact_sort_name_a', $row) && |
720 | array_key_exists('civicrm_contact_id', $row) | |
721 | ) { | |
722 | $url = CRM_Report_Utils_Report::getNextUrl('contact/detail', | |
723 | 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'], | |
724 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
725 | ); | |
7c550ca0 WA |
726 | $rows[$rowNum]['civicrm_contact_sort_name_a'] |
727 | = $rows[$rowNum]['civicrm_contact_sort_name_a'] . ' (' . | |
9d72cede | 728 | $rows[$rowNum]['civicrm_contact_id'] . ')'; |
6a488035 | 729 | $rows[$rowNum]['civicrm_contact_sort_name_a_link'] = $url; |
37935ea7 | 730 | $rows[$rowNum]['civicrm_contact_sort_name_a_hover'] = ts('View Contact Detail Report for this contact'); |
6a488035 TO |
731 | $entryFound = TRUE; |
732 | } | |
733 | ||
7ed79ffa WM |
734 | // Handle contact name B |
735 | // @todo refactor into separate function | |
6a488035 TO |
736 | if (array_key_exists('civicrm_contact_b_sort_name_b', $row) && |
737 | array_key_exists('civicrm_contact_b_id', $row) | |
738 | ) { | |
739 | $url = CRM_Report_Utils_Report::getNextUrl('contact/detail', | |
740 | 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_b_id'], | |
741 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
742 | ); | |
7c550ca0 WA |
743 | $rows[$rowNum]['civicrm_contact_b_sort_name_b'] |
744 | = $rows[$rowNum]['civicrm_contact_b_sort_name_b'] . ' (' . | |
9d72cede | 745 | $rows[$rowNum]['civicrm_contact_b_id'] . ')'; |
6a488035 | 746 | $rows[$rowNum]['civicrm_contact_b_sort_name_b_link'] = $url; |
37935ea7 | 747 | $rows[$rowNum]['civicrm_contact_b_sort_name_b_hover'] = ts('View Contact Detail Report for this contact'); |
a9315b5e DG |
748 | $entryFound = TRUE; |
749 | } | |
750 | ||
7ed79ffa | 751 | // Handle relationship |
bb0a814d WM |
752 | if (array_key_exists('civicrm_relationship_relationship_id', $row) && |
753 | array_key_exists('civicrm_contact_id', $row) | |
754 | ) { | |
755 | $url = "/civicrm/contact/view/rel?reset=1&action=update&rtype=a_b&cid=" . | |
756 | $row['civicrm_contact_id'] . "&id=" . | |
757 | $row['civicrm_relationship_relationship_id']; | |
758 | $rows[$rowNum]['civicrm_relationship_relationship_id_link'] = $url; | |
759 | $rows[$rowNum]['civicrm_relationship_relationship_id_hover'] = ts("Edit this relationship."); | |
760 | $entryFound = TRUE; | |
761 | } | |
762 | ||
f871c3a9 AS |
763 | // Handle permissioned relationships |
764 | if (array_key_exists('civicrm_relationship_is_permission_a_b', $row)) { | |
765 | $rows[$rowNum]['civicrm_relationship_is_permission_a_b'] | |
766 | = ts(self::permissionedRelationship($row['civicrm_relationship_is_permission_a_b'])); | |
767 | $entryFound = TRUE; | |
768 | } | |
769 | ||
770 | if (array_key_exists('civicrm_relationship_is_permission_b_a', $row)) { | |
771 | $rows[$rowNum]['civicrm_relationship_is_permission_b_a'] | |
772 | = ts(self::permissionedRelationship($row['civicrm_relationship_is_permission_b_a'])); | |
773 | $entryFound = TRUE; | |
774 | } | |
775 | ||
6a488035 TO |
776 | // skip looking further in rows, if first row itself doesn't |
777 | // have the column we need | |
778 | if (!$entryFound) { | |
779 | break; | |
780 | } | |
781 | } | |
782 | } | |
96025800 | 783 | |
f871c3a9 AS |
784 | /** |
785 | * Convert values to permissioned relationship descriptions | |
786 | * @param [int] $key | |
787 | * @return [string] | |
788 | */ | |
789 | public static function permissionedRelationship($key) { | |
790 | static $lookup; | |
791 | if (!$lookup) { | |
792 | $lookup = CRM_Contact_BAO_Relationship::buildOptions("is_permission_a_b"); | |
793 | }; | |
914d3734 | 794 | return $lookup[$key] ?? NULL; |
f871c3a9 AS |
795 | } |
796 | ||
7c41e675 | 797 | /** |
81ec12f6 | 798 | * @param $valid bool - set to 1 if we are looking for a valid relationship, 0 if not |
7c41e675 | 799 | * |
800 | * @return array | |
801 | */ | |
81ec12f6 | 802 | public function buildValidityQuery($valid) { |
7c41e675 | 803 | $clause = NULL; |
804 | if ($valid == '1') { | |
eaafc14c | 805 | // Relationships dates are not expired. |
7c41e675 | 806 | $clause = "((start_date <= CURDATE() OR start_date is null) AND (end_date >= CURDATE() OR end_date is null))"; |
807 | } | |
808 | elseif ($valid == '0') { | |
eaafc14c | 809 | // Relationships dates are expired or has not started yet. |
7c41e675 | 810 | $clause = "(start_date >= CURDATE() OR end_date < CURDATE())"; |
811 | } | |
812 | return $clause; | |
813 | } | |
81ec12f6 | 814 | |
eea5db81 | 815 | /** |
816 | * Get SQL where clause for a active period field. | |
817 | * | |
818 | * @param string $fieldName | |
819 | * @param string $relative | |
820 | * @param string $from | |
821 | * @param string $to | |
822 | * @param string $type | |
eea5db81 | 823 | * |
824 | * @return null|string | |
825 | */ | |
826 | public function activeClause( | |
827 | $fieldName, | |
6d6630cf | 828 | $relative, $from, $to, $type = NULL) { |
be2fb01f | 829 | $clauses = []; |
eea5db81 | 830 | if (in_array($relative, array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE)))) { |
831 | return NULL; | |
832 | } | |
833 | ||
6d6630cf | 834 | list($from, $to) = $this->getFromTo($relative, $from, $to); |
eea5db81 | 835 | |
836 | if ($from) { | |
837 | $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from; | |
838 | } | |
839 | ||
840 | if ($to) { | |
841 | $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to; | |
842 | } | |
843 | ||
02633eb0 | 844 | if ($from || $to) { |
eea5db81 | 845 | return CRM_Contact_BAO_Query::getRelationshipActivePeriodClauses($from, $to, FALSE); |
846 | } | |
eea5db81 | 847 | return NULL; |
848 | } | |
849 | ||
6a488035 | 850 | } |