Author Topic: Added a contribution 'receive_date' filter to report, the filter does nothing? (Read 1258 times)

CiviTeacher.com · June 03, 2011, 06:59:13 am

I'm trying to make a modification to this default report <site>/civicrm/report/instance/2&reset=1 which is the out-of-the-box Constituent Report (Detail).

I added a few lines around line 124:

Code: [Select]

						 'filters' => 
                          array( 'receive_date'           => 
                                    array( 'operatorType' => CRM_Report_Form::OP_DATE ),
						  ),

This works on other reports to create a date filter for contributions. In this particular report, the date filter appears, and the report will actually run showing "from Xdate to Ydate" in the results. Yet the results are not actually filtered. All the dates for contributions appear. It's like the filter is just being ignored.

Just for testing, I've manually placed a "HAVING" statement around line 488:

Code: [Select]

if ($val == 'contribution_civireport') {
						$sql .= " HAVING {$this->_aliases['civicrm_contribution']}.receive_date > '2002-01-01'";
				}

and this will narrow the results, but obviously this won't work for the end user.

How to a get the filter to work?

mcarson · June 06, 2011, 06:27:51 am

Are you sure you want to edit this report template rather than creating your own?

The report you are editing to include a date filter references CRM_Report_Form_Contact_Detail which includes capabilities to report on almost all contact information as editing this template may have the side-effect of changing other in-built pages and search results and most likely will be overwritten when CiviCRM is upgraded!

You can create your own directory structure for scripts and templates outside of the civicrm directory. You can create (or copy and modify) your own report script and template. You can check the docs for this.

That said, CiviCRM does not know how to apply the filter to the query: ( I have NOT tested this as there are too many dependencies elsewhere in the code) (Make a backup first).

Replace the 'where' function:

Code: [Select]

    function where( ) {
        $clauses = array( );

        foreach ( $this->_columns as $tableName => $table ) {
            if ( array_key_exists('filters', $table) ) {
                foreach ( $table['filters'] as $fieldName => $field ) {
                    $clause = null;
                    $op = CRM_Utils_Array::value( "{$fieldName}_op", $this->_params );
                    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 ) );
                    }
                    if ( ! empty( $clause ) ) {
                        $clauses[ ] = $clause;
                    }
                }
            }
        }
       
        if ( empty( $clauses ) ) {
            $this->_where = "WHERE ( 1 ) ";
        } else {
            $this->_where = "WHERE " . implode( ' AND ', $clauses );
        }

        if ( $this->_aclWhere ) {
            $this->_where .= " AND {$this->_aclWhere} ";
        }            
        
        $this->_where .= " GROUP BY {$this->_aliases['civicrm_contact']}.id ";
    }

with the following that includes date processing:

Code: [Select]

    function where( ) {
        $clauses = array( );

         function where( ) {
        $clauses = array( );

        foreach ( $this->_columns as $tableName => $table ) {
            if ( array_key_exists('filters', $table) ) {
                foreach ( $table['filters'] as $fieldName => $field ) {
                    $clause = null;
					if ( CRM_Utils_Array::value( 'operatorType', $field ) & CRM_Utils_Type::T_DATE ) {
						$relative = CRM_Utils_Array::value( "{$fieldName}_relative", $this->_params );
						$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'] );
					} else {
						$op = CRM_Utils_Array::value( "{$fieldName}_op", $this->_params );
						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 ) );
						}
					}
                    if ( ! empty( $clause ) ) {
                        $clauses[ ] = $clause;
                    }
                }
            }
        }
       
        if ( empty( $clauses ) ) {
            $this->_where = "WHERE ( 1 ) ";
        } else {
            $this->_where = "WHERE " . implode( ' AND ', $clauses );
        }

        if ( $this->_aclWhere ) {
            $this->_where .= " AND {$this->_aclWhere} ";
        }            
        
        $this->_where .= " GROUP BY {$this->_aliases['civicrm_contact']}.id ";
    }

OR replace with the following to limit the date processing to 'receive_date' in 'civicrm_contribution' only:

Code: [Select]

    function where( ) {
        $clauses = array( );

        foreach ( $this->_columns as $tableName => $table ) {
            if ( array_key_exists('filters', $table) ) {
                foreach ( $table['filters'] as $fieldName => $field ) {
                    $clause = null;

					if ( $tableName == 'civicrm_contribution' && $fieldName == 'receive_date' ) {
						$relative = CRM_Utils_Array::value( "{$fieldName}_relative", $this->_params );
						$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'] );

					} else {
						$op = CRM_Utils_Array::value( "{$fieldName}_op", $this->_params );
						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 ) );
					}

                    if ( ! empty( $clause ) ) {
                        $clauses[ ] = $clause;
                    }

                }
            }
        }
       
        if ( empty( $clauses ) ) {
            $this->_where = "WHERE ( 1 ) ";
        } else {
            $this->_where = "WHERE " . implode( ' AND ', $clauses );
        }

        if ( $this->_aclWhere ) {
            $this->_where .= " AND {$this->_aclWhere} ";
        }            
        
        $this->_where .= " GROUP BY {$this->_aliases['civicrm_contact']}.id ";
    }

I will stress again, however, NOT to change the civicrm code if you are not sure of the impact (particularly with this script)

Let me know what worked for you.

CiviTeacher.com · June 06, 2011, 10:29:16 am

mcarson, thanks but wow, I don't really understand why all the warnings about overwriting core files. But thanks for the concern anyhow, I am already aware that I should not edit the civicrm core code without creating a custom directory for my PHP or a new report template.

But anyway, besides that...I will try your code and see if it works thanks.

UPDATE
Unknown column 'contribution_civireport.receive_date' in 'where clause', 1054
Well, editing the where() function applies the filter on receive_date to every JOIN statement. Even when I used your last example where the action is restricted to only civicrm_contribution table and receive_date field.

Something ends up happening like this:

Code: [Select]

[debug_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.display_name as civicrm_contact_display_name, contact_civireport.id as civicrm_contact_id, address_civireport.country_id as civicrm_address_country_id  
        FROM civicrm_contact contact_civireport 
            LEFT JOIN civicrm_address address_civireport 
                   ON (contact_civireport.id = address_civireport.contact_id AND 
                      address_civireport.is_primary = 1 )   WHERE ( contact_civireport.display_name LIKE '%johnston%' ) AND ( contribution_civireport.receive_date >= 20050607000000 ) AND contact_civireport.is_deleted = 0  GROUP BY contact_civireport.id      LIMIT 0, 10 [nativecode=1054 ** Unknown column 'contribution_civireport.receive_date' in 'where clause']

I tried a few different permutations of how to keep this from happening, and none works. I don't have much interest in continuing this, so I am going to have to contact web access. thanks.

mcarson · June 06, 2011, 12:22:09 pm

my bad

! What I meant to suggest is that as you, in your initial post, stated that you were modifying the 'out-of-the-box' report; I only meant this as a reminder not to change CiviCRM code as I have done before (proper backup helped, though).

mcarson · June 07, 2011, 01:42:54 am

One last thing to attempt:

maybe change:
'receive_date' => array( 'default' => true ),
to:
'receive_date' => array('title' => ts('Receive Date'), 'default' => true ),
in the column definition...

However, not sure how this will work with the INNER JOIN on the contribution id - contact id.

CiviCRM Community Forums (archive)

News:

Author Topic: Added a contribution 'receive_date' filter to report, the filter does nothing? (Read 1258 times)

CiviTeacher.com

Added a contribution 'receive_date' filter to report, the filter does nothing?

mcarson

Re: Added a contribution 'receive_date' filter to report, the filter does nothing?

CiviTeacher.com

Re: Added a contribution 'receive_date' filter to report, the filter does nothing?

mcarson

Re: Added a contribution 'receive_date' filter to report, the filter does nothing?

mcarson

Re: Added a contribution 'receive_date' filter to report, the filter does nothing?