Find Contribution by date changed from 3.0.0 to 3.2.2 and possible bug?

[jbertolacci]

I am running a test CiviCRM v3.2.2 server with live data in preparation for an upgrade from 3.0.0. Our accounting team has noticed that their Find Contribution searches in 3.2.2 are not jiving with 3.0.0 searches in terms of numbers of contributions or totals when searching by date.

If I search in 3.0.0 for contributions between July 1, 2010 and July 2, 2010, I get all contributions processed in the 48 hour period of July 1st and 2nd. In 3.2.2 I only get contributions processed on the 24 hours  of July 1st 12:00 AM to July 2nd at 12:00 AM. Any contributions received during the day of the 2nd are not found in the search in 3.2.2.

It looks like 3.0.0 searched by the specified dates only, where 3.2.2 is searching using the specified dates and hour of 12:00AM. Was this an intended change? I couldn't find anything Issue Tracker about this change.

I tried to work around this change by setting Time to "24 hr" in the Advanced Date and Time settings (http://drupal.demo.civicrm.org/civicrm/admin/setting/preferences/date?action=update&id=7&reset=1) so accounting could specify a time period in 3.2.2 searches to mirror 3.0.0, but I get a database error due to a malformed sql query when I do. Is this a bug?


Sorry. A non-recoverable error has occurred.
DB Error: syntax error
Error Details
Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') = '00:00' AND civicrm_contribution.receive_date <= '20100701' AND LOWER() = '2' at line 3, 1064
Additional Details:
Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT count(*)  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id  LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument') LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
                               AND option_group_payment_instrument.id = payment_instrument.option_group_id )  LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status') LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
                               AND option_group_contribution_status.id = contribution_status.option_group_id )  WHERE  ( civicrm_contribution.receive_date >= '20100701' AND LOWER() = '00:00' AND civicrm_contribution.receive_date <= '20100701' AND LOWER() = '23:59' AND civicrm_contribution.is_test = 0 )  AND (contact_a.is_deleted = 0)    [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') = '00:00' AND civicrm_contribution.receive_date <= '20100701' AND LOWER() = '2' at line 3]
    [type] => DB_Error
    [user_info] => SELECT count(*)  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id  LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument') LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
                               AND option_group_payment_instrument.id = payment_instrument.option_group_id )  LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status') LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
                               AND option_group_contribution_status.id = contribution_status.option_group_id )  WHERE  ( civicrm_contribution.receive_date >= '20100701' AND LOWER() = '00:00' AND civicrm_contribution.receive_date <= '20100701' AND LOWER() = '23:59' AND civicrm_contribution.is_test = 0 )  AND (contact_a.is_deleted = 0)    [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') = '00:00' AND civicrm_contribution.receive_date <= '20100701' AND LOWER() = '2' at line 3]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT count(*)  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id  LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument') LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
                               AND option_group_payment_instrument.id = payment_instrument.option_group_id )  LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status') LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
                               AND option_group_contribution_status.id = contribution_status.option_group_id )  WHERE  ( civicrm_contribution.receive_date >= '20100701' AND LOWER() = '00:00' AND civicrm_contribution.receive_date <= '20100701' AND LOWER() = '23:59' AND civicrm_contribution.is_test = 0 )  AND (contact_a.is_deleted = 0)    [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') = '00:00' AND civicrm_contribution.receive_date <= '20100701' AND LOWER() = '2' at line 3]"]
)


[Donald Lobo]

seems like in 3.0.0 the start date was: date + 12:00:00 and end date: date + 23:59:59

seems like this was reset in 3.2.x or so

can u please file an issue and we'll fix for 3.2.3

thanx

lobo

[jbertolacci]

Done...

http://issues.civicrm.org/jira/browse/CRM-6770

[Donald Lobo]

this issue is fixed in 3.2.3 (the bad sql for 24 hrs is not yet fixed)

lobo

[jbertolacci]

lobo,

To follow up on this issue and our IRC conversation, my 3.2 test install shows 1821 total contributions in the Find Contributions results summary for a given peroid, but actually finds 1824 contributions. I looked at the mysql query log as you directed and the "Total Amount, # Contributions, Avg Amount" Find Contributions summary appends a WHERE clause of "AND civicrm_contribution.contribution_status_id = 1" even if you have not specified a contribution status in the search criteria.

Is this the desired behavior or should "AND civicrm_contribution.contribution_status_id = 1" be removed from the query?

Code: [Select]

SELECT COUNT( civicrm_contribution.total_amount ) as total_count,
SUM(   civicrm_contribution.total_amount ) as total_amount,
AVG(   civicrm_contribution.total_amount ) as total_avg,
civicrm_contribution.currency              as currency
FROM civicrm_contact contact_a
LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id
LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument') LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
AND option_group_payment_instrument.id = payment_instrument.option_group_id )
LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')
LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
AND option_group_contribution_status.id = contribution_status.option_group_id )
WHERE  ( civicrm_contribution.receive_date >= '20100601' AND civicrm_contribution.receive_date <= '20100701' AND civicrm_contribution.is_test = 0 ) 
AND civicrm_contribution.contribution_status_id = 1
GROUP BY currency


[Donald Lobo]

i dont think the answer for this is simple:

basically when u r doing the totals, in most cases i suspect u would not want the "cancelled' contributions. in some cases u might not want the pending contributions either.

Not sure what the right thing to do in this case

lobo
 

[jbertolacci]

In 3.0.0 the behavior of the summary was to count exactly what was searched for -- so the summary shows all 1824. Perhaps we could look at why the sql was changed or the simplest path would be to clarify the summary labeling that total "# Completed" or "# Completed Contributions".