CiviCRM Community Forums (archive)

We've built a custom report template based on the Case Detail template, and in trying to resolve an issue with search filters / parameters not being saved correctly, I think we've identified a problem in the core Case Detail template itself.

The core problem is that once a report has been previewed, any filter set for case type is lost from the report criteria. The preview runs the search correctly, but thereafter, the search returns data as though the case type filter did not exist. Further clues to the problem are that in the summary of the report settings at the foot of the page, where the Case Status filter might be shown as "Is one of Ongoing, Urgent" the Case Type filter would be shown as "Is one of '4','5'" rather than "Is one of Housing, Education".

What seems to be the problem is that the where() method manipulates $this->_params['case_type_id_value'] array, to add the value separator character so that the database query will work correctly. But once this has happened, the array holds a five character string, instead of a single character string, so when the form is re-drawn, the option select field cannot work out which options to pre-select.

            $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
            if ($fieldName == 'case_type_id' && CRM_Utils_Array::value('case_type_id_value', $this->_params)) {
              foreach ($this->_params['case_type_id_value'] as $key => $value) {
                if (strpos($value, CRM_Core_DAO::VALUE_SEPARATOR) === FALSE) {
                  $value = CRM_Core_DAO::VALUE_SEPARATOR . $value . CRM_Core_DAO::VALUE_SEPARATOR;
                  $this->_params['case_type_id_value'][$key] = "'{$value}'";
                }
                else {
                  $this->_params['case_type_id_value'][$key] = $value;
                }
              }
            }

            if ($op) {
              $clause = $this->whereClause($field,
                $op,
                CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
                CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
                CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
              );
            }

The value separators are stripped out again later in alterDisplay() with a str_replace within each individual $row, which helps makes the results look pretty, but it doesn't resolve the problem with the $_params array.

We resolved this in our report by abandoning the use of $this->_params for the third argument to whereClause(), instead creating a new array which has the value separator added if required, and then passing the new array to whereClause():

            $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
           
            if ($fieldName == 'case_type_id' && CRM_Utils_Array::value('case_type_id_value', $this->_params)) {
              $value_parameter = array();
              foreach ($this->_params['case_type_id_value'] as $key => $value) {
                  $value_parameter[$key] = "'" . CRM_Core_DAO::VALUE_SEPARATOR . $value . CRM_Core_DAO::VALUE_SEPARATOR . "'";
              }
            } else {
                $value_parameter = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
            }
            if ($op) {
              $clause = $this->whereClause($field,
                $op,
                $value_parameter,
                CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
                CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
              );
             
            }

What confuses me is that this must be a problem with any searches derived from the Case Detail report but I couldn't find any reference to it in the forum. Is no-one else generating reports filtered by case type? Or has everyone else just found a better way of doing so? Shouldn't the same issue (of needing to add the value separator into the SQL query, without causing problems with the saved search parameters) be present in contact-based reports which filter by contact sub-type? How is it resolved there? Or aren't there any such searches?

Have we misunderstood something with the Case Detail report? Is there a better way of addressing this issue?

We are using 4.4.1 - we've tested this in the demo site (4.4.5), and seen very similar (not identical, but still buggy) behaviour. In the demo site, using the native Case Detail report template, and creating a new report from it searching for cases in one of two case types, with one of two possible case statuses, the case type filter in the summary only shows one case type ("Is Adult Day Care Referral") even if multiple case types have been selected, although it does show the case type label, not just the '4','5' thing we're getting on our test system.