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
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?