Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Major
-
Resolution: Won't Fix
-
Affects Version/s: 2.2.7
-
Fix Version/s: 3.0
-
Component/s: CiviReport
-
Labels:None
Description
v2.2.7
The LYBUNT (CRM/Report/Form/Contribute/Lybunt.php) report returns inflated values under certain circumstances. This affects the list view as well as the detail view. Running this report we were getting contribution summaries that were in some cases 10 or 14 times the correct values. I identified two problems, both of them relating to the SQL. The generated SQL for this report includes an email address, and in some cases is supposed to filter on group ids.
Problem 1:
The SQL joins the civicrm_email table on contact_id and is_primary=1. The problem is appears that there can multiple records with the is_primary bit set. When a certain contact has multiple primary email addresses, the report will return a contribution row for each primary email address thereby inflating the sum for a given year by the number of primary email address.
Problem 2:
The SQL joins the civicrm_group[_contact] tables. If the contact is a member of one or more groups AND there was no criteria given for group membership, then a contribution row for each group the contact is a member of is returned. This inflates the contribution numbers by the number of active groups the contact was a member of.
Solution:
For joining with the civicrm_email table adding an extra condition to the ON clause of "AND email.location_type_id = 5" will limit the list to only the billing email address. For joining with the civicrm_group[_contact] tables, a conditional should be put around those joins such that they are not invoked unless the user actually specified some group criteria.
The attached patch seems to fix the issue for me.
Attachments
Activity
If CiviCRM doesn't support multiple is_primary email addresses for a given contact then there may be a bug in the code and the interface seems confusing, because we do in fact have a contact with multiple email addresses flagged as is_primary and we certainly didn't manually flag it through the database. It must have happened via the CiviCRM code.
For the particular contact with two is_primary email addresses, the interface would seem to indicate that this is correct. In the "Primary Location" section there is an address specified for the "Email (preferred)" field, and that address is flagged as is_primary in the database. However, there is another section labelled "Additional Location," in which there is also an "Email (preferred)" field, which also has a value and which is also flagged as is_primary in the database. I took this to mean that "Email (preferred)" was equivalent to is_primary, as both of the addresses in these fields are flagged as is_primary in the database.
Is that incorrect? What does "Email (preferred)" mean in the "Additional Location" section? If it's not supposed to be flagged as is_primary in the database, is it possible that the CiviCRM code somehow incorrectly flagged that field? Perhaps this is another bug?
Email (preferred) label are present in all location block in add/edit mode
because we don't know the which is your primary location type in at least in Add Contact mode
( Email (preferred) label is not dynamic on add/edit form ) same for phone, IM and Open ID
For a contact there is only one (required) Primary location Type AND Billing Location Type(optional)
email, phone, IM, openId and Address mark as is_primary based on location type selected as Primary
If primary location type contain more than one email, phone, im, openid then First one of each entity will consider as is_primary
Your DB contain the more than one email id as is_primary
Are you using the Contact/Location API? if yes, it might be possible is_primary flagged for more than one record (api not checking strictly is_primary flag already present or not )
I am pretty sure through contact add/Edit screen, we only add one record as is_primary/is_billing
closing the issue, since it's not related to report SQL
Might be need to file another issue (if you able to recreate/replicate ) for is_primary flagged for more than one email address (phone, IM, Open ID ) for a contact
it's might be related to Upgrade / API.
We already have same issue for duplicate row in export contribution CRM-4488 (becauase of is_primary is flagged for more than one record)
But don't know why this is happening.
Okay. We've got 35 contacts with multiple email addresses flagged as is_primary. We don't use the API. I have no idea how the database got into this inconsistent state. I don't know how to possibly replicate the problem because I only became aware of it accidentally when I noticed wildly incorrect report numbers. It would appear to have been happening for quite some time because many of the contacts have had no activity for over a year.
Problem 1:
More than one primary email address not supported.
is_primary flag true for one email address, not matter with Location Type
for a contact there is single primary email
Problem 2: Already fixed and will be part of v2.2.8 / v3.0.alpha1