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