From eea5db8195ea093b86d2c4c077308550044866de Mon Sep 17 00:00:00 2001 From: francescbassas Date: Thu, 11 May 2017 13:29:31 +0200 Subject: [PATCH] CRM-18081 Allow search of active relationships during a custom range of dates https://issues.civicrm.org/jira/browse/CRM-18081 --- CRM/Contact/BAO/Query.php | 59 +++++++++++++++++++ CRM/Contact/Form/Search/Criteria.php | 2 + CRM/Report/Form/Contact/Relationship.php | 55 ++++++++++++++++- .../Form/Search/Criteria/Relationship.tpl | 6 ++ 4 files changed, 121 insertions(+), 1 deletion(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index b72e793f39..9aa3310298 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -1972,6 +1972,8 @@ class CRM_Contact_BAO_Query { case 'relation_start_date_low': case 'relation_end_date_high': case 'relation_end_date_low': + case 'relation_active_period_date_high': + case 'relation_active_period_date_low': case 'relation_target_name': case 'relation_status': case 'relation_date_low': @@ -4130,6 +4132,7 @@ civicrm_relationship.is_permission_a_b = 0 } $this->addRelationshipDateClauses($grouping, $where); + $this->addRelationshipActivePeriodClauses($grouping, $where); if (!empty($relationType) && !empty($rType) && isset($rType->id)) { $where[$grouping][] = 'civicrm_relationship.relationship_type_id = ' . $rType->id; } @@ -4194,6 +4197,62 @@ civicrm_relationship.is_permission_a_b = 0 } } + /** + * Add start & end active period criteria in + * @param string $grouping + * @param array $where + * = array to add where clauses to, in case you are generating a temp table. + * not the main query. + */ + public function addRelationshipActivePeriodClauses($grouping, &$where) { + $dateValues = array(); + $dateField = 'active_period_date'; + + $dateValueLow = $this->getWhereValues('relation_active_period_date_low', $grouping); + $dateValueHigh = $this->getWhereValues('relation_active_period_date_high', $grouping); + if (!empty($dateValueLow) && !empty($dateValueHigh)) { + $dateValueLowFormated = date('Ymd', strtotime($dateValueLow[2])); + $dateValueHighFormated = date('Ymd', strtotime($dateValueHigh[2])); + $where[$grouping][] = self::getRelationshipActivePeriodClauses($dateValueLowFormated, $dateValueHighFormated, TRUE); + $this->_qill[$grouping][] = (ts('Relationship was active between')) . " " . CRM_Utils_Date::customFormat($dateValueLowFormated) . " and " . CRM_Utils_Date::customFormat($dateValueHighFormated); + } + elseif (!empty($dateValueLow)) { + $dateValueLowFormated = date('Ymd', strtotime($dateValueLow[2])); + $where[$grouping][] = self::getRelationshipActivePeriodClauses($dateValueLowFormated, NULL, TRUE); + $this->_qill[$grouping][] = (ts('Relationship was active after')) . " " . CRM_Utils_Date::customFormat($dateValueLowFormated); + } + elseif (!empty($dateValueHigh)) { + $dateValueHighFormated = date('Ymd', strtotime($dateValueHigh[2])); + $where[$grouping][] = self::getRelationshipActivePeriodClauses(NULL, $dateValueHighFormated, TRUE); + $this->_qill[$grouping][] = (ts('Relationship was active before')) . " " . CRM_Utils_Date::customFormat($dateValueHighFormated); + } + } + + /** + * Get start & end active period criteria + */ + public static function getRelationshipActivePeriodClauses($from, $to, $forceTableName) { + $tableName = $forceTableName ? 'civicrm_relationship.' : ''; + if (!is_null($from) && !is_null($to)) { + return '(((' . $tableName . 'start_date >= ' . $from . ' AND ' . $tableName . 'start_date <= ' . $to . ') OR + (' . $tableName . 'end_date >= ' . $from . ' AND ' . $tableName . 'end_date <= ' . $to . ') OR + (' . $tableName . 'start_date <= ' . $from . ' AND ' . $tableName . 'end_date >= ' . $to . ' )) OR + (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date IS NULL) OR + (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date >= ' . $from . ') OR + (' . $tableName . 'end_date IS NULL AND ' . $tableName . 'start_date <= ' . $to . '))'; + } + elseif (!is_null($from)) { + return '((' . $tableName . 'start_date >= ' . $from . ') OR + (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date IS NULL) OR + (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date >= ' . $from . '))'; + } + elseif (!is_null($to)) { + return '((' . $tableName . 'start_date <= ' . $to . ') OR + (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date IS NULL) OR + (' . $tableName . 'end_date IS NULL AND ' . $tableName . 'start_date <= ' . $to . '))'; + } + } + /** * Default set of return properties. * diff --git a/CRM/Contact/Form/Search/Criteria.php b/CRM/Contact/Form/Search/Criteria.php index b6280d2702..f876e40e81 100644 --- a/CRM/Contact/Form/Search/Criteria.php +++ b/CRM/Contact/Form/Search/Criteria.php @@ -436,6 +436,8 @@ class CRM_Contact_Form_Search_Criteria { CRM_Core_Form_Date::buildDateRange($form, 'relation_start_date', 1, '_low', '_high', ts('From:'), FALSE, FALSE); CRM_Core_Form_Date::buildDateRange($form, 'relation_end_date', 1, '_low', '_high', ts('From:'), FALSE, FALSE); + CRM_Core_Form_Date::buildDateRange($form, 'relation_active_period_date', 1, '_low', '_high', ts('From:'), FALSE, FALSE); + // Add reltionship dates CRM_Core_Form_Date::buildDateRange($form, 'relation_date', 1, '_low', '_high', ts('From:'), FALSE, FALSE); diff --git a/CRM/Report/Form/Contact/Relationship.php b/CRM/Report/Form/Contact/Relationship.php index eb7579c755..46aa82cd12 100644 --- a/CRM/Report/Form/Contact/Relationship.php +++ b/CRM/Report/Form/Contact/Relationship.php @@ -298,6 +298,10 @@ class CRM_Report_Form_Contact_Relationship extends CRM_Report_Form { 'title' => ts('End Date'), 'type' => CRM_Utils_Type::T_DATE, ), + 'active_period_date' => array( + 'title' => ts('Active Period'), + 'type' => CRM_Utils_Type::T_DATE, + ), ), 'grouping' => 'relation-fields', ), @@ -438,7 +442,12 @@ class CRM_Report_Form_Contact_Relationship extends CRM_Report_Form { $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params); $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params); - $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); + if ($fieldName == 'active_period_date') { + $clause = $this->activeClause($field['name'], $relative, $from, $to, $field['type']); + } + else { + $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); + } } else { $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); @@ -774,4 +783,48 @@ class CRM_Report_Form_Contact_Relationship extends CRM_Report_Form { return $clause; } + /** + * Get SQL where clause for a active period field. + * + * @param string $fieldName + * @param string $relative + * @param string $from + * @param string $to + * @param string $type + * @param string $fromTime + * @param string $toTime + * + * @return null|string + */ + public function activeClause( + $fieldName, + $relative, $from, $to, $type = NULL, $fromTime = NULL, $toTime = NULL + ) { + $clauses = array(); + if (in_array($relative, array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE)))) { + return NULL; + } + + list($from, $to) = $this->getFromTo($relative, $from, $to, $fromTime, $toTime); + + if ($from) { + $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from; + } + + if ($to) { + $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to; + } + + if ($from && $to) { + return CRM_Contact_BAO_Query::getRelationshipActivePeriodClauses($from, $to, FALSE); + } + elseif ($from) { + return CRM_Contact_BAO_Query::getRelationshipActivePeriodClauses($from, NULL, FALSE); + } + elseif ($to) { + return CRM_Contact_BAO_Query::getRelationshipActivePeriodClauses(NULL, $to, FALSE); + } + return NULL; + } + } diff --git a/templates/CRM/Contact/Form/Search/Criteria/Relationship.tpl b/templates/CRM/Contact/Form/Search/Criteria/Relationship.tpl index a7de91157b..ae47f810a8 100644 --- a/templates/CRM/Contact/Form/Search/Criteria/Relationship.tpl +++ b/templates/CRM/Contact/Form/Search/Criteria/Relationship.tpl @@ -65,6 +65,12 @@ {include file="CRM/Core/DateRange.tpl" fieldName="relation_end_date" from='_low' to='_high'} + + + + + {include file="CRM/Core/DateRange.tpl" fieldName="relation_active_period_date" from='_low' to='_high'} + {if $relationshipGroupTree} -- 2.25.1