Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
7e9e8871 | 4 | | CiviCRM version 4.7 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
fa938177 | 6 | | Copyright CiviCRM LLC (c) 2004-2016 | |
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 | |
fa938177 | 31 | * @copyright CiviCRM LLC (c) 2004-2016 |
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; | |
6a488035 | 42 | protected $_customGroupExtends = array( |
21dfd5f5 | 43 | 'Relationship', |
9d72cede | 44 | ); |
6a488035 | 45 | public $_drilldownReport = array('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 | ||
74 | $this->_columns = array( | |
9d72cede | 75 | 'civicrm_contact' => array( |
6a488035 | 76 | 'dao' => 'CRM_Contact_DAO_Contact', |
9d72cede EM |
77 | 'fields' => array( |
78 | 'sort_name_a' => array( | |
79 | 'title' => ts('Contact A'), | |
6a488035 TO |
80 | 'name' => 'sort_name', |
81 | 'required' => TRUE, | |
82 | ), | |
16e98ad0 SL |
83 | 'display_name_a' => array( |
84 | 'title' => ts('Contact A Full Name'), | |
85 | 'name' => 'display_name', | |
86 | ), | |
9d72cede | 87 | 'id' => array( |
6a488035 TO |
88 | 'no_display' => TRUE, |
89 | 'required' => TRUE, | |
90 | ), | |
9d72cede | 91 | 'contact_type_a' => array( |
30f85891 RN |
92 | 'title' => ts('Contact Type (Contact A)'), |
93 | 'name' => 'contact_type', | |
94 | ), | |
9d72cede | 95 | 'contact_sub_type_a' => array( |
b8f96eb8 | 96 | 'title' => ts('Contact Subtype (Contact A)'), |
30f85891 RN |
97 | 'name' => 'contact_sub_type', |
98 | ), | |
6a488035 | 99 | ), |
9d72cede EM |
100 | 'filters' => array( |
101 | 'sort_name_a' => array( | |
102 | 'title' => ts('Contact A'), | |
6a488035 TO |
103 | 'name' => 'sort_name', |
104 | 'operator' => 'like', | |
105 | 'type' => CRM_Report_Form::OP_STRING, | |
106 | ), | |
9d72cede EM |
107 | 'contact_type_a' => array( |
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, | |
113 | ), | |
6a488035 | 114 | ), |
70bea8e2 | 115 | 'grouping' => 'contact_a_fields', |
6a488035 | 116 | ), |
9d72cede | 117 | 'civicrm_contact_b' => array( |
6a488035 TO |
118 | 'dao' => 'CRM_Contact_DAO_Contact', |
119 | 'alias' => 'contact_b', | |
9d72cede EM |
120 | 'fields' => array( |
121 | 'sort_name_b' => array( | |
122 | 'title' => ts('Contact B'), | |
6a488035 TO |
123 | 'name' => 'sort_name', |
124 | 'required' => TRUE, | |
125 | ), | |
16e98ad0 SL |
126 | 'display_name_b' => array( |
127 | 'title' => ts('Contact B Full Name'), | |
128 | 'name' => 'display_name', | |
129 | ), | |
9d72cede | 130 | 'id' => array( |
6a488035 TO |
131 | 'no_display' => TRUE, |
132 | 'required' => TRUE, | |
133 | ), | |
9d72cede | 134 | 'contact_type_b' => array( |
30f85891 RN |
135 | 'title' => ts('Contact Type (Contact B)'), |
136 | 'name' => 'contact_type', | |
137 | ), | |
9d72cede | 138 | 'contact_sub_type_b' => array( |
b8f96eb8 | 139 | 'title' => ts('Contact Subtype (Contact B)'), |
30f85891 RN |
140 | 'name' => 'contact_sub_type', |
141 | ), | |
6a488035 | 142 | ), |
9d72cede EM |
143 | 'filters' => array( |
144 | 'sort_name_b' => array( | |
145 | 'title' => ts('Contact B'), | |
6a488035 TO |
146 | 'name' => 'sort_name', |
147 | 'operator' => 'like', | |
148 | 'type' => CRM_Report_Form::OP_STRING, | |
149 | ), | |
9d72cede EM |
150 | 'contact_type_b' => array( |
151 | 'title' => ts('Contact Type B'), | |
bc1f381d BS |
152 | 'name' => 'contact_type', |
153 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
154 | 'options' => $contact_type, | |
155 | 'type' => CRM_Utils_Type::T_STRING, | |
156 | ), | |
6a488035 | 157 | ), |
70bea8e2 | 158 | 'grouping' => 'contact_b_fields', |
6a488035 | 159 | ), |
9d72cede | 160 | 'civicrm_email' => array( |
6a488035 | 161 | 'dao' => 'CRM_Core_DAO_Email', |
9d72cede EM |
162 | 'fields' => array( |
163 | 'email_a' => array( | |
164 | 'title' => ts('Email (Contact A)'), | |
6a488035 TO |
165 | 'name' => 'email', |
166 | ), | |
167 | ), | |
70bea8e2 | 168 | 'grouping' => 'contact_a_fields', |
6a488035 | 169 | ), |
9d72cede | 170 | 'civicrm_email_b' => array( |
6a488035 TO |
171 | 'dao' => 'CRM_Core_DAO_Email', |
172 | 'alias' => 'email_b', | |
9d72cede EM |
173 | 'fields' => array( |
174 | 'email_b' => array( | |
175 | 'title' => ts('Email (Contact B)'), | |
6a488035 TO |
176 | 'name' => 'email', |
177 | ), | |
178 | ), | |
70bea8e2 | 179 | 'grouping' => 'contact_b_fields', |
6a488035 | 180 | ), |
9d72cede | 181 | 'civicrm_phone' => array( |
7393ba64 M |
182 | 'dao' => 'CRM_Core_DAO_Phone', |
183 | 'alias' => 'phone_a', | |
9d72cede EM |
184 | 'fields' => array( |
185 | 'phone_a' => array( | |
a9315b5e | 186 | 'title' => ts('Phone (Contact A)'), |
7393ba64 M |
187 | 'name' => 'phone', |
188 | ), | |
9d72cede | 189 | 'phone_ext_a' => array( |
a9315b5e | 190 | 'title' => ts('Phone Ext (Contact A)'), |
7393ba64 M |
191 | 'name' => 'phone_ext', |
192 | ), | |
193 | ), | |
70bea8e2 | 194 | 'grouping' => 'contact_a_fields', |
7393ba64 | 195 | ), |
9d72cede | 196 | 'civicrm_phone_b' => array( |
7393ba64 M |
197 | 'dao' => 'CRM_Core_DAO_Phone', |
198 | 'alias' => 'phone_b', | |
9d72cede EM |
199 | 'fields' => array( |
200 | 'phone_b' => array( | |
a9315b5e | 201 | 'title' => ts('Phone (Contact B)'), |
21dfd5f5 | 202 | 'name' => 'phone', |
7393ba64 | 203 | ), |
9d72cede | 204 | 'phone_ext_b' => array( |
a9315b5e | 205 | 'title' => ts('Phone Ext (Contact B)'), |
21dfd5f5 | 206 | 'name' => 'phone_ext', |
7393ba64 M |
207 | ), |
208 | ), | |
70bea8e2 | 209 | 'grouping' => 'contact_b_fields', |
7393ba64 | 210 | ), |
9d72cede | 211 | 'civicrm_relationship_type' => array( |
6a488035 | 212 | 'dao' => 'CRM_Contact_DAO_RelationshipType', |
9d72cede EM |
213 | 'fields' => array( |
214 | 'label_a_b' => array( | |
215 | 'title' => ts('Relationship A-B '), | |
6a488035 TO |
216 | 'default' => TRUE, |
217 | ), | |
9d72cede EM |
218 | 'label_b_a' => array( |
219 | 'title' => ts('Relationship B-A '), | |
6a488035 TO |
220 | 'default' => TRUE, |
221 | ), | |
222 | ), | |
6a488035 TO |
223 | 'grouping' => 'relation-fields', |
224 | ), | |
9d72cede | 225 | 'civicrm_relationship' => array( |
6a488035 | 226 | 'dao' => 'CRM_Contact_DAO_Relationship', |
9d72cede EM |
227 | 'fields' => array( |
228 | 'start_date' => array( | |
229 | 'title' => ts('Relationship Start Date'), | |
6a488035 | 230 | ), |
9d72cede EM |
231 | 'end_date' => array( |
232 | 'title' => ts('Relationship End Date'), | |
6a488035 | 233 | ), |
9d72cede EM |
234 | 'description' => array( |
235 | 'title' => ts('Description'), | |
6a488035 | 236 | ), |
9d72cede | 237 | 'relationship_id' => array( |
a9315b5e DG |
238 | 'title' => ts('Rel ID'), |
239 | 'name' => 'id', | |
240 | ), | |
6a488035 | 241 | ), |
9d72cede EM |
242 | 'filters' => array( |
243 | 'is_active' => array( | |
244 | 'title' => ts('Relationship Status'), | |
6a488035 | 245 | 'operatorType' => CRM_Report_Form::OP_SELECT, |
9d72cede | 246 | 'options' => array( |
ccc29f8e | 247 | '' => ts('- Any -'), |
248 | 1 => ts('Active'), | |
249 | 0 => ts('Inactive'), | |
6a488035 TO |
250 | ), |
251 | 'type' => CRM_Utils_Type::T_INT, | |
252 | ), | |
7c41e675 | 253 | 'is_valid' => array( |
254 | 'title' => ts('Relationship Dates Validity'), | |
255 | 'operatorType' => CRM_Report_Form::OP_SELECT, | |
256 | 'options' => array( | |
257 | NULL => ts('- Any -'), | |
258 | 1 => ts('Not expired'), | |
259 | 0 => ts('Expired'), | |
260 | ), | |
261 | 'type' => CRM_Utils_Type::T_INT, | |
262 | ), | |
9d72cede EM |
263 | 'relationship_type_id' => array( |
264 | 'title' => ts('Relationship'), | |
02cbed89 SL |
265 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
266 | 'options' => CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, NULL, TRUE), | |
6a488035 TO |
267 | 'type' => CRM_Utils_Type::T_INT, |
268 | ), | |
7c41e675 | 269 | 'start_date' => array( |
270 | 'title' => ts('Start Date'), | |
271 | 'type' => CRM_Utils_Type::T_DATE, | |
272 | ), | |
273 | 'end_date' => array( | |
274 | 'title' => ts('End Date'), | |
275 | 'type' => CRM_Utils_Type::T_DATE, | |
276 | ), | |
6a488035 TO |
277 | ), |
278 | 'grouping' => 'relation-fields', | |
279 | ), | |
9d72cede | 280 | 'civicrm_address' => array( |
6a488035 | 281 | 'dao' => 'CRM_Core_DAO_Address', |
9d72cede EM |
282 | 'filters' => array( |
283 | 'country_id' => array( | |
284 | 'title' => ts('Country'), | |
8ee006e7 | 285 | 'type' => CRM_Utils_Type::T_INT, |
6a488035 TO |
286 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
287 | 'options' => CRM_Core_PseudoConstant::country(), | |
288 | ), | |
9d72cede EM |
289 | 'state_province_id' => array( |
290 | 'title' => ts('State/Province'), | |
8ee006e7 | 291 | 'type' => CRM_Utils_Type::T_INT, |
6a488035 TO |
292 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
293 | 'options' => CRM_Core_PseudoConstant::stateProvince(), | |
294 | ), | |
295 | ), | |
296 | 'grouping' => 'contact-fields', | |
297 | ), | |
6a488035 TO |
298 | ); |
299 | ||
16e2e80c | 300 | $this->_groupFilter = TRUE; |
6a488035 TO |
301 | $this->_tagFilter = TRUE; |
302 | parent::__construct(); | |
303 | } | |
304 | ||
00be9182 | 305 | public function preProcess() { |
6a488035 TO |
306 | parent::preProcess(); |
307 | } | |
308 | ||
00be9182 | 309 | public function select() { |
6a488035 TO |
310 | $select = $this->_columnHeaders = array(); |
311 | foreach ($this->_columns as $tableName => $table) { | |
312 | if (array_key_exists('fields', $table)) { | |
313 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
314 | if (!empty($field['required']) || |
315 | !empty($this->_params['fields'][$fieldName]) | |
316 | ) { | |
6a488035 TO |
317 | |
318 | if ($fieldName == 'email_a') { | |
319 | $this->_emailField_a = TRUE; | |
320 | } | |
321 | if ($fieldName == 'email_b') { | |
322 | $this->_emailField_b = TRUE; | |
323 | } | |
9d72cede | 324 | if ($fieldName == 'phone_a') { |
7393ba64 M |
325 | $this->_phoneField_a = TRUE; |
326 | } | |
9d72cede | 327 | if ($fieldName == 'phone_b') { |
7393ba64 M |
328 | $this->_phoneField_b = TRUE; |
329 | } | |
6a488035 TO |
330 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; |
331 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field); | |
332 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field); | |
333 | } | |
334 | } | |
335 | } | |
336 | } | |
d1641c51 | 337 | $this->_selectClauses = $select; |
6a488035 TO |
338 | |
339 | $this->_select = "SELECT " . implode(', ', $select) . " "; | |
340 | } | |
341 | ||
00be9182 | 342 | public function from() { |
6a488035 TO |
343 | $this->_from = " |
344 | FROM civicrm_relationship {$this->_aliases['civicrm_relationship']} | |
2f4c2f5d | 345 | |
6a488035 | 346 | INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} |
2f4c2f5d | 347 | ON ( {$this->_aliases['civicrm_relationship']}.contact_id_a = |
6a488035 TO |
348 | {$this->_aliases['civicrm_contact']}.id ) |
349 | ||
350 | INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact_b']} | |
2f4c2f5d | 351 | ON ( {$this->_aliases['civicrm_relationship']}.contact_id_b = |
6a488035 TO |
352 | {$this->_aliases['civicrm_contact_b']}.id ) |
353 | ||
354 | {$this->_aclFrom} "; | |
355 | ||
356 | if (!empty($this->_params['country_id_value']) || | |
357 | !empty($this->_params['state_province_id_value']) | |
358 | ) { | |
359 | $this->_from .= " | |
2f4c2f5d | 360 | INNER JOIN civicrm_address {$this->_aliases['civicrm_address']} |
6a488035 TO |
361 | ON (( {$this->_aliases['civicrm_address']}.contact_id = |
362 | {$this->_aliases['civicrm_contact']}.id OR | |
363 | {$this->_aliases['civicrm_address']}.contact_id = | |
2f4c2f5d | 364 | {$this->_aliases['civicrm_contact_b']}.id ) AND |
6a488035 TO |
365 | {$this->_aliases['civicrm_address']}.is_primary = 1 ) "; |
366 | } | |
367 | ||
368 | $this->_from .= " | |
369 | INNER JOIN civicrm_relationship_type {$this->_aliases['civicrm_relationship_type']} | |
2f4c2f5d | 370 | ON ( {$this->_aliases['civicrm_relationship']}.relationship_type_id = |
6a488035 TO |
371 | {$this->_aliases['civicrm_relationship_type']}.id ) "; |
372 | ||
373 | // include Email Field | |
374 | if ($this->_emailField_a) { | |
2f4c2f5d | 375 | $this->_from .= " |
6a488035 TO |
376 | LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} |
377 | ON ( {$this->_aliases['civicrm_contact']}.id = | |
2f4c2f5d | 378 | {$this->_aliases['civicrm_email']}.contact_id AND |
6a488035 TO |
379 | {$this->_aliases['civicrm_email']}.is_primary = 1 )"; |
380 | } | |
381 | if ($this->_emailField_b) { | |
2f4c2f5d | 382 | $this->_from .= " |
383 | LEFT JOIN civicrm_email {$this->_aliases['civicrm_email_b']} | |
6a488035 | 384 | ON ( {$this->_aliases['civicrm_contact_b']}.id = |
2f4c2f5d | 385 | {$this->_aliases['civicrm_email_b']}.contact_id AND |
6a488035 TO |
386 | {$this->_aliases['civicrm_email_b']}.is_primary = 1 )"; |
387 | } | |
7393ba64 M |
388 | // include Phone Field |
389 | if ($this->_phoneField_a) { | |
390 | $this->_from .= " | |
391 | LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} | |
392 | ON ( {$this->_aliases['civicrm_contact']}.id = | |
393 | {$this->_aliases['civicrm_phone']}.contact_id AND | |
394 | {$this->_aliases['civicrm_phone']}.is_primary = 1 )"; | |
395 | } | |
396 | if ($this->_phoneField_b) { | |
397 | $this->_from .= " | |
398 | LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone_b']} | |
399 | ON ( {$this->_aliases['civicrm_contact_b']}.id = | |
400 | {$this->_aliases['civicrm_phone_b']}.contact_id AND | |
401 | {$this->_aliases['civicrm_phone_b']}.is_primary = 1 )"; | |
402 | } | |
6a488035 TO |
403 | } |
404 | ||
00be9182 | 405 | public function where() { |
6a488035 TO |
406 | $whereClauses = $havingClauses = array(); |
407 | foreach ($this->_columns as $tableName => $table) { | |
408 | if (array_key_exists('filters', $table)) { | |
409 | foreach ($table['filters'] as $fieldName => $field) { | |
410 | ||
411 | $clause = NULL; | |
412 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
413 | $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params); | |
9d72cede EM |
414 | $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params); |
415 | $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params); | |
6a488035 TO |
416 | |
417 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
418 | } | |
419 | else { | |
420 | $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); | |
421 | if ($op) { | |
9d72cede EM |
422 | if (($tableName == 'civicrm_contact' || |
423 | $tableName == 'civicrm_contact_b') && | |
424 | ($fieldName == 'contact_type_a' || | |
425 | $fieldName == 'contact_type_b') | |
6a488035 TO |
426 | ) { |
427 | $cTypes = CRM_Utils_Array::value("{$fieldName}_value", $this->_params); | |
428 | $contactTypes = $contactSubTypes = array(); | |
429 | if (!empty($cTypes)) { | |
430 | foreach ($cTypes as $ctype) { | |
431 | $getTypes = CRM_Utils_System::explode('_', $ctype, 2); | |
9d72cede EM |
432 | if ($getTypes[1] && |
433 | !in_array($getTypes[1], $contactSubTypes) | |
434 | ) { | |
6a488035 TO |
435 | $contactSubTypes[] = $getTypes[1]; |
436 | } | |
9d72cede EM |
437 | elseif ($getTypes[0] && |
438 | !in_array($getTypes[0], $contactTypes) | |
439 | ) { | |
6a488035 TO |
440 | $contactTypes[] = $getTypes[0]; |
441 | } | |
442 | } | |
443 | } | |
444 | ||
445 | if (!empty($contactTypes)) { | |
446 | $clause = $this->whereClause($field, | |
447 | $op, | |
448 | $contactTypes, | |
449 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
450 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
451 | ); | |
452 | } | |
453 | ||
454 | if (!empty($contactSubTypes)) { | |
bc1f381d | 455 | $field['name'] = 'contact_sub_type'; |
6a488035 TO |
456 | $field['dbAlias'] = $field['alias'] . '.' . $field['name']; |
457 | $subTypeClause = $this->whereClause($field, | |
458 | $op, | |
459 | $contactSubTypes, | |
460 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
461 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
462 | ); | |
463 | if ($clause) { | |
464 | $clause = '(' . $clause . ' OR ' . $subTypeClause . ')'; | |
465 | } | |
466 | else { | |
467 | $clause = $subTypeClause; | |
468 | } | |
469 | } | |
470 | } | |
471 | else { | |
e93563f1 | 472 | if ($fieldName == 'is_valid') { |
7c41e675 | 473 | $clause = $this->buildValidityQuery(CRM_Utils_Array::value("{$fieldName}_value", $this->_params)); |
474 | } | |
475 | else { | |
476 | $clause = $this->whereClause($field, | |
477 | $op, | |
478 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
479 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
480 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
481 | ); | |
482 | } | |
6a488035 TO |
483 | } |
484 | } | |
485 | } | |
486 | ||
487 | if (!empty($clause)) { | |
a7488080 | 488 | if (!empty($field['having'])) { |
6a488035 TO |
489 | $havingClauses[] = $clause; |
490 | } | |
491 | else { | |
492 | $whereClauses[] = $clause; | |
493 | } | |
494 | } | |
495 | } | |
496 | } | |
497 | } | |
498 | ||
499 | if (empty($whereClauses)) { | |
500 | $this->_where = 'WHERE ( 1 ) '; | |
501 | $this->_having = ''; | |
502 | } | |
503 | else { | |
504 | $this->_where = 'WHERE ' . implode(' AND ', $whereClauses); | |
505 | } | |
506 | ||
507 | if ($this->_aclWhere) { | |
508 | $this->_where .= " AND {$this->_aclWhere} "; | |
509 | } | |
510 | ||
511 | if (!empty($havingClauses)) { | |
512 | // use this clause to construct group by clause. | |
513 | $this->_having = 'HAVING ' . implode(' AND ', $havingClauses); | |
514 | } | |
515 | } | |
516 | ||
74cf4551 EM |
517 | /** |
518 | * @param $rows | |
519 | * | |
520 | * @return array | |
521 | */ | |
00be9182 | 522 | public function statistics(&$rows) { |
6a488035 TO |
523 | $statistics = parent::statistics($rows); |
524 | ||
525 | $isStatusFilter = FALSE; | |
526 | $relStatus = NULL; | |
527 | if (CRM_Utils_Array::value('is_active_value', $this->_params) == '1') { | |
528 | $relStatus = 'Is equal to Active'; | |
529 | } | |
530 | elseif (CRM_Utils_Array::value('is_active_value', $this->_params) == '0') { | |
531 | $relStatus = 'Is equal to Inactive'; | |
532 | } | |
a7488080 | 533 | if (!empty($statistics['filters'])) { |
6a488035 TO |
534 | foreach ($statistics['filters'] as $id => $value) { |
535 | //for displaying relationship type filter | |
536 | if ($value['title'] == 'Relationship') { | |
537 | $relTypes = CRM_Core_PseudoConstant::relationshipType(); | |
ccc29f8e | 538 | $op = CRM_Utils_Array::value('relationship_type_id_op', $this->_params) == 'in' ? ts('Is one of') . ' ' : ts('Is not one of') . ' '; |
02cbed89 SL |
539 | $relationshipTypes = array(); |
540 | foreach ($this->_params['relationship_type_id_value'] as $relationship) { | |
541 | $relationshipTypes[] = $relTypes[$relationship]['label_' . $this->relationType]; | |
542 | } | |
543 | $statistics['filters'][$id]['value'] = $op . | |
544 | implode(', ', $relationshipTypes); | |
6a488035 TO |
545 | } |
546 | ||
547 | //for displaying relationship status | |
548 | if ($value['title'] == 'Relationship Status') { | |
549 | $isStatusFilter = TRUE; | |
550 | $statistics['filters'][$id]['value'] = $relStatus; | |
551 | } | |
552 | } | |
553 | } | |
554 | //for displaying relationship status | |
555 | if (!$isStatusFilter && $relStatus) { | |
556 | $statistics['filters'][] = array( | |
557 | 'title' => 'Relationship Status', | |
558 | 'value' => $relStatus, | |
559 | ); | |
560 | } | |
561 | return $statistics; | |
562 | } | |
563 | ||
00be9182 | 564 | public function groupBy() { |
6a488035 TO |
565 | $this->_groupBy = " "; |
566 | $groupBy = array(); | |
567 | if ($this->relationType == 'a_b') { | |
568 | $groupBy[] = " {$this->_aliases['civicrm_contact']}.id"; | |
569 | } | |
570 | elseif ($this->relationType == 'b_a') { | |
571 | $groupBy[] = " {$this->_aliases['civicrm_contact_b']}.id"; | |
572 | } | |
573 | ||
574 | if (!empty($groupBy)) { | |
d1641c51 | 575 | $groupBy[] = "{$this->_aliases['civicrm_relationship']}.id"; |
6a488035 TO |
576 | } |
577 | else { | |
d1641c51 | 578 | $groupBy = array("{$this->_aliases['civicrm_relationship']}.id"); |
6a488035 | 579 | } |
d1641c51 | 580 | |
b708c08d | 581 | $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); |
6a488035 TO |
582 | } |
583 | ||
00be9182 | 584 | public function orderBy() { |
6a488035 TO |
585 | $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact_b']}.sort_name "; |
586 | } | |
587 | ||
00be9182 | 588 | public function postProcess() { |
6a488035 TO |
589 | $this->beginPostProcess(); |
590 | ||
674b20c9 | 591 | $originalRelationshipTypeIdValue = $this->_params['relationship_type_id_value']; |
a7488080 | 592 | if (!empty($this->_params['relationship_type_id_value'])) { |
02cbed89 SL |
593 | $relationshipTypes = array(); |
594 | $direction = array(); | |
674b20c9 | 595 | $relType = array(); |
02cbed89 SL |
596 | foreach ($this->_params['relationship_type_id_value'] as $relationship_type) { |
597 | $relType = explode('_', $relationship_type); | |
598 | $direction[] = $relType[1] . '_' . $relType[2]; | |
599 | $relationshipTypes[] = intval($relType[0]); | |
600 | } | |
601 | // Lets take the first relationship type to guide us in the relationship direction | |
602 | // we should use. | |
603 | $this->relationType = $direction[0]; | |
604 | $this->_params['relationship_type_id_value'] = $relationshipTypes; | |
6a488035 TO |
605 | } |
606 | ||
9d72cede | 607 | $this->buildACLClause(array( |
353ffa53 TO |
608 | $this->_aliases['civicrm_contact'], |
609 | $this->_aliases['civicrm_contact_b'], | |
610 | )); | |
6a488035 TO |
611 | $sql = $this->buildQuery(); |
612 | $this->buildRows($sql, $rows); | |
613 | ||
614 | $this->formatDisplay($rows); | |
615 | $this->doTemplateAssignment($rows); | |
616 | ||
674b20c9 | 617 | if (!empty($originalRelationshipTypeIdValue)) { |
6a488035 | 618 | // store its old value, CRM-5837 |
674b20c9 | 619 | $this->_params['relationship_type_id_value'] = $originalRelationshipTypeIdValue; |
6a488035 TO |
620 | } |
621 | $this->endPostProcess($rows); | |
622 | } | |
623 | ||
74cf4551 | 624 | /** |
70bea8e2 | 625 | * @param $rows |
74cf4551 | 626 | */ |
00be9182 | 627 | public function alterDisplay(&$rows) { |
70bea8e2 | 628 | // custom code to alter rows |
6a488035 TO |
629 | $entryFound = FALSE; |
630 | ||
631 | foreach ($rows as $rowNum => $row) { | |
632 | ||
633 | // handle country | |
634 | if (array_key_exists('civicrm_address_country_id', $row)) { | |
635 | if ($value = $row['civicrm_address_country_id']) { | |
636 | $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE); | |
637 | } | |
638 | $entryFound = TRUE; | |
639 | } | |
640 | ||
641 | if (array_key_exists('civicrm_address_state_province_id', $row)) { | |
642 | if ($value = $row['civicrm_address_state_province_id']) { | |
643 | $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE); | |
644 | } | |
645 | $entryFound = TRUE; | |
646 | } | |
647 | ||
648 | if (array_key_exists('civicrm_contact_sort_name_a', $row) && | |
649 | array_key_exists('civicrm_contact_id', $row) | |
650 | ) { | |
651 | $url = CRM_Report_Utils_Report::getNextUrl('contact/detail', | |
652 | 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'], | |
653 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
654 | ); | |
7c550ca0 WA |
655 | $rows[$rowNum]['civicrm_contact_sort_name_a'] |
656 | = $rows[$rowNum]['civicrm_contact_sort_name_a'] . ' (' . | |
9d72cede | 657 | $rows[$rowNum]['civicrm_contact_id'] . ')'; |
6a488035 TO |
658 | $rows[$rowNum]['civicrm_contact_sort_name_a_link'] = $url; |
659 | $rows[$rowNum]['civicrm_contact_sort_name_a_hover'] = ts("View Contact details for this contact."); | |
660 | $entryFound = TRUE; | |
661 | } | |
662 | ||
663 | if (array_key_exists('civicrm_contact_b_sort_name_b', $row) && | |
664 | array_key_exists('civicrm_contact_b_id', $row) | |
665 | ) { | |
666 | $url = CRM_Report_Utils_Report::getNextUrl('contact/detail', | |
667 | 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_b_id'], | |
668 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
669 | ); | |
7c550ca0 WA |
670 | $rows[$rowNum]['civicrm_contact_b_sort_name_b'] |
671 | = $rows[$rowNum]['civicrm_contact_b_sort_name_b'] . ' (' . | |
9d72cede | 672 | $rows[$rowNum]['civicrm_contact_b_id'] . ')'; |
6a488035 TO |
673 | $rows[$rowNum]['civicrm_contact_b_sort_name_b_link'] = $url; |
674 | $rows[$rowNum]['civicrm_contact_b_sort_name_b_hover'] = ts("View Contact details for this contact."); | |
675 | $entryFound = TRUE; | |
676 | } | |
677 | ||
9d72cede EM |
678 | if (array_key_exists('civicrm_relationship_relationship_id', $row) && |
679 | array_key_exists('civicrm_contact_id', $row) | |
680 | ) { | |
7c550ca0 | 681 | $url = "/civicrm/contact/view/rel?reset=1&action=update&rtype=a_b&cid=" . |
9d72cede EM |
682 | $row['civicrm_contact_id'] . "&id=" . |
683 | $row['civicrm_relationship_relationship_id']; | |
a9315b5e DG |
684 | $rows[$rowNum]['civicrm_relationship_relationship_id_link'] = $url; |
685 | $rows[$rowNum]['civicrm_relationship_relationship_id_hover'] = ts("Edit this relationship."); | |
686 | $entryFound = TRUE; | |
687 | } | |
688 | ||
6a488035 TO |
689 | // skip looking further in rows, if first row itself doesn't |
690 | // have the column we need | |
691 | if (!$entryFound) { | |
692 | break; | |
693 | } | |
694 | } | |
695 | } | |
96025800 | 696 | |
7c41e675 | 697 | /** |
81ec12f6 | 698 | * @param $valid bool - set to 1 if we are looking for a valid relationship, 0 if not |
7c41e675 | 699 | * |
700 | * @return array | |
701 | */ | |
81ec12f6 | 702 | public function buildValidityQuery($valid) { |
7c41e675 | 703 | $clause = NULL; |
704 | if ($valid == '1') { | |
e93563f1 | 705 | // relationships dates are not expired |
7c41e675 | 706 | $clause = "((start_date <= CURDATE() OR start_date is null) AND (end_date >= CURDATE() OR end_date is null))"; |
707 | } | |
708 | elseif ($valid == '0') { | |
e93563f1 | 709 | // relationships dates are expired or has not started yet |
7c41e675 | 710 | $clause = "(start_date >= CURDATE() OR end_date < CURDATE())"; |
711 | } | |
712 | return $clause; | |
713 | } | |
81ec12f6 | 714 | |
6a488035 | 715 | } |