2

I am trying to use Drupal Views to create a report of contributions. I would like the user to be able to filter the contributions by financial type and by date.

To allow the user filter by date using a simple drop down I have installed the civicrm_entity module. I have previously used this module to create views for activities and events and was able to filter these by date. However this doesn't work with contributions for some reason.

Using the devel module I have looked at the SQL that is created by views. It appears that for contributions the receive date is converted from unixtime which gives NULL. As a result the query returns no results. If you remove the FROM_UNIXTIME() and ADDTIME() functions from the query, it works as expected.

This is the SQL code for the activity view, which works fine:

SELECT civicrm_activity.activity_type_id AS civicrm_activity_activity_type_id, civicrm_value_publication_details_5.type_of_publication_8 AS civicrm_value_publication_details_5_type_of_publication_8, SUM(civicrm_value_publication_details_5.publication_price_9) AS civicrm_value_publication_details_5_publication_price_9, COUNT(civicrm_activity.id) AS id
FROM civicrm_activity civicrm_activity
LEFT JOIN civicrm_value_publication_details_5 civicrm_value_publication_details_5 ON civicrm_activity.id = civicrm_value_publication_details_5.entity_id
WHERE (( (DATE_FORMAT(civicrm_activity.activity_date_time, '%Y-%m') >= :civicrm_activity_date_filter AND DATE_FORMAT(civicrm_activity.activity_date_time, '%Y-%m') <= :civicrm_activity_date_filter1) )) 
GROUP BY civicrm_activity_activity_type_id, civicrm_value_publication_details_5_type_of_publication_8 LIMIT 10 OFFSET 0

This is the SQL code for the contributions view, which doesn't:

SELECT civicrm_contribution.id AS id, civicrm_contribution.receive_date AS civicrm_contribution_receive_date, civicrm_contribution.total_amount AS civicrm_contribution_total_amount 
FROM civicrm_contribution civicrm_contribution 
LEFT JOIN civicrm_contact civicrm_contact ON civicrm_contribution.contact_id = civicrm_contact.id 
WHERE (( (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_contribution.receipt_date), SEC_TO_TIME(3600)), '%Y-%m') = :civicrm_contact_date_filter OR DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_contribution.receive_date), SEC_TO_TIME(3600)), '%Y-%m') = :civicrm_contact_date_filter1) ))
LIMIT 10 OFFSET 0

I'm not sure if this is an error with the civicrm_entity module or with CiviCRM. Can anyone help?

1

This is working in beta6 of CiviCRM entity...probably before, can't remember exactly when I fixed this.

Relevant D.O issue https://www.drupal.org/node/2570319

  • That's great. Thanks for providing an update on this. – William Mortada Apr 18 '17 at 8:48
  • Interestingly, using 7.x-2.1, the problem appears for activities - i.e. the activity date time is getting mangled with FROM_UNIXTIME(civicrm_activity.activity_date_time). It was okay on 7.x-2.0-beta11 – Alan Dixon Jun 26 '18 at 19:53
  • Can you make an issue at the d.o project page issue queue? That will help me remember – jackrabbithanna Jun 26 '18 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.