CiviCRM Core issueshttps://lab.civicrm.org/dev/core/-/issues2019-08-08T01:27:17Zhttps://lab.civicrm.org/dev/core/-/issues/1171unreleased regression2019-08-08T01:27:17Zeileenunreleased regressionnew enotices on importing with an import mapping (not to be confused with the pre-existing ones)
https://github.com/civicrm/civicrm-core/pull/14978new enotices on importing with an import mapping (not to be confused with the pre-existing ones)
https://github.com/civicrm/civicrm-core/pull/149785.17.0https://lab.civicrm.org/dev/core/-/issues/1135Participants having multiple roles affects maximum event registration count2019-08-05T19:47:12ZDon WijesooriyaParticipants having multiple roles affects maximum event registration countIf you add multiple participant roles for a participant entry, it affects the event full count.
### Steps to reproduce
1. On dmaster, create a new event
2. Enable online registration and set "Max Number of Participants" to 1
3. Go to l...If you add multiple participant roles for a participant entry, it affects the event full count.
### Steps to reproduce
1. On dmaster, create a new event
2. Enable online registration and set "Max Number of Participants" to 1
3. Go to live event registration page. You should be able to register
4. Now add a new registration to a different contact from backend.
5. Once again go to live event registration page. Now it should show the event full message.
6. Go back to the other contact's events. Edit the event participant entry and add another role such as "Host".
7. Now go to the event registration page and you will be able to register for the maxed out event
### Issue
After inspecting `CRM_Event_BAO_Participant::eventFull()` the following line assumes there will only be one value in the field
````php
$where[] = " participant.role_id IN ( '" . implode("', '", $escapedRoles) . "' ) ";
````
Therefore when you have multiple values it won't work properly.
### Solution
I used regular expression code that's used in `CRM_Event_BAO_Query::whereClauseSingle()` under `case 'participant_role_id'`. This is a simple workaround I could think of. Haven't tested in all other scenarios.
````php
$regexp = "([[:cntrl:]]|^)" . implode('([[:cntrl:]]|$)|([[:cntrl:]]|^)', $escapedRoles) . "([[:cntrl:]]|$)";
$where[] = " participant.role_id REGEXP '{$regexp}'";
````5.17.0https://lab.civicrm.org/dev/core/-/issues/470Current employer dissapears when disabling expired relationships2024-03-15T18:12:06ZfrancescbassasCurrent employer dissapears when disabling expired relationshipsOriginal issue: https://issues.civicrm.org/jira/browse/CRM-21851
Affected versions: at least from 5.0.0
---
How to reproduce:
1) Create a current relationship on individual A as *employee of* organization B and mark as *current emplo...Original issue: https://issues.civicrm.org/jira/browse/CRM-21851
Affected versions: at least from 5.0.0
---
How to reproduce:
1) Create a current relationship on individual A as *employee of* organization B and mark as *current employer*
2) Create a past relationship on individual A as *employee of* organization B without marking as current employer
At this point on summary tab of individual A appears organization B as his Employer.
3) Run *Disable expired relationships* cron job
At this point on summary tab there is no organization at Employer field although there is one active employee relationship and past relationship was disabled.5.17.0https://lab.civicrm.org/dev/core/-/issues/285Scheduled Reminders for Membership not being sent2019-07-12T03:35:58ZMickCScheduled Reminders for Membership not being sentMembership reminders are not being sent - 2 probable causes found:
1. civicrm_action_log records for the same reminder 1 year ago
2. Status Override is checked
When either of the above conditions exist, the reminder is not sent.
Whe...Membership reminders are not being sent - 2 probable causes found:
1. civicrm_action_log records for the same reminder 1 year ago
2. Status Override is checked
When either of the above conditions exist, the reminder is not sent.
When neither of the above conditions exist, the reminder is sent.
When corrective action is taken to remove past civicrm_action_log records and remove any override, the reminder is sent.
Environment:
* CiviCRM 4.7.29
* Extension 'Transactional Email' is installed5.17.0https://lab.civicrm.org/dev/core/-/issues/829Swaziland has changed its name to Eswatini2019-03-28T20:07:12Zlord_tSwaziland has changed its name to EswatiniSwaziland country has changed name to Eswatini so in CiviCRM it also has to be changed.
> On 19 April 2018, King Mswati III announced that the Kingdom of Swaziland had renamed itself the Kingdom of Eswatini, reflecting the extant Swazi...Swaziland country has changed name to Eswatini so in CiviCRM it also has to be changed.
> On 19 April 2018, King Mswati III announced that the Kingdom of Swaziland had renamed itself the Kingdom of Eswatini, reflecting the extant Swazi name for the state eSwatini, to mark the 50th anniversary of Swazi independence. The new name, **Eswatini**, [...]
Source: https://en.wikipedia.org/wiki/Eswatini#Independence_(1968%E2%80%93present)
Patch: https://github.com/civicrm/civicrm-core/pull/139025.13.0https://lab.civicrm.org/dev/core/-/issues/795PHP Warning "explode() expects parameter 2 to be string, array given" for mul...2019-03-20T14:59:22ZVirenmagePHP Warning "explode() expects parameter 2 to be string, array given" for multi-value country fieldsI am getting above error while trying to save multiple country for agent profile. And when I search about this error I found that unnecessary use of explode() function on line 127. You can see more about on https://lab.civicrm.org/dev/co...I am getting above error while trying to save multiple country for agent profile. And when I search about this error I found that unnecessary use of explode() function on line 127. You can see more about on https://lab.civicrm.org/dev/core/issues/216 and code file
https://lab.civicrm.org/dev/core/blob/master/CRM/Core/BAO/CustomValueTable.php#L127
So according to me you need to ask to civiCRM team to comment line 127
$mulValues = explode(',', $value);
![error](/uploads/c124cf6618247c06ef570df0639f869c/error.png)5.13.0https://lab.civicrm.org/dev/core/-/issues/789Event reg error when skipping additional contacts2019-03-18T00:07:09ZMartinEvent reg error when skipping additional contactsWe recently encountered a bug under some fairly specific conditions:
* Doing an event registration
* Multiple participants enabled
* User select >1 participant
* On one of the subsequent participants, chooses the "skip" option
* Att...We recently encountered a bug under some fairly specific conditions:
* Doing an event registration
* Multiple participants enabled
* User select >1 participant
* On one of the subsequent participants, chooses the "skip" option
* Attempts to pay using a real credit card on a live payment gateway (in our case using the iATS extension)
This was on Civi 5.9.1 with Drupal 7.63.
In this situation, we found the following error:
> Warning: Invalid argument supplied for foreach() in CRM_Event_Form_Rgistration_Confirm->cleanMoneyFields() (line 1357 of [drupal root]/sites/all/modules/civicrm/CRM/Event/Form/Registration/Confirm.php).
Creating a pull request to fix in progress. I'm confused though, is the codebase here or on github??5.13.0https://lab.civicrm.org/dev/core/-/issues/788Warning: A non-numeric value encountered in ...2019-03-18T05:00:01ZwouterhWarning: A non-numeric value encountered in ...I've encountered warnings on contribution pages & event registrations
- Drupal8
- CiviCRM 5.10.4
- PHP 7.2.12
Warning: A non-numeric value encountered in CRM_Contribute_Form_Contribution_Main::formRule() (line 900 of /var/www/html/vendo...I've encountered warnings on contribution pages & event registrations
- Drupal8
- CiviCRM 5.10.4
- PHP 7.2.12
Warning: A non-numeric value encountered in CRM_Contribute_Form_Contribution_Main::formRule() (line 900 of /var/www/html/vendor/civicrm/civicrm-core/CRM/Contribute/Form/Contribution/Main.php).
Warning: A non-numeric value encountered in CRM_Event_Form_Registration_Register->postProcess() (line 1146 of /var/www/html/vendor/civicrm/civicrm-core/CRM/Event/Form/Registration/Register.php).
```
if ($self->_paymentProcessor &&
$self->_paymentProcessor['billing_mode'] & CRM_Core_Payment::BILLING_MODE_BUTTON
) {
```
```
elseif ($this->_paymentProcessor &&
$this->_paymentProcessor['billing_mode'] & CRM_Core_Payment::BILLING_MODE_NOTIFY
) {
```
![788](/uploads/6ce88a0bf5059e65b381841b42b1fdf2/788.png)5.13.0https://lab.civicrm.org/dev/core/-/issues/771Smart group with uf_group_id does not load contacts with same search profile2019-03-18T02:47:02ZjitendraSmart group with uf_group_id does not load contacts with same search profileSteps to reproduce -
- Create a search profile adding some basic fields `first name`, last name and email.
- Open Advanced Search form, select this profile and add some filters.
- create a smart group of the resulted contacts.
- When yo...Steps to reproduce -
- Create a search profile adding some basic fields `first name`, last name and email.
- Open Advanced Search form, select this profile and add some filters.
- create a smart group of the resulted contacts.
- When you come back from the process after clicking the `Done` button on the last screen, the profile is not pre-selected by default.
- `uf_group_id` is not stored as a formvalue in `civicrm_saved_search` table.5.13.0jitendrajitendrahttps://lab.civicrm.org/dev/core/-/issues/735Query performance - suppress 'product' and related fields where products are ...2019-04-26T06:50:00ZeileenQuery performance - suppress 'product' and related fields where products are not in the databaseMost people hate the fact we waste an output field on premium on the contribution tab. They all think it should be replaced - but all have different replacements.
I don't want to solve that - I think the right solution for that is to f...Most people hate the fact we waste an output field on premium on the contribution tab. They all think it should be replaced - but all have different replacements.
I don't want to solve that - I think the right solution for that is to find funding to leap the interface.
What I DO think we can solve is the fact we are doing unnecessary joins / queries / processing to generate & display premiums - regardless of whether the site uses them. I think we could short-circuit that pretty cleanly by just doing a quick
"select id FROM civicrm_product_premium LIMIT 1' (cached) before adding product fields to our default return properties (with a small tpl tweak too)5.13.0https://lab.civicrm.org/dev/core/-/issues/725Address API incorrectly sets state_province_id if multiple countries have sam...2019-04-03T18:25:00ZjackrabbithannaAddress API incorrectly sets state_province_id if multiple countries have same state name / abbreviationReplicable on dmaster.demo
The Address API is not taking into account the country_id when figuring the state_province_id when given the state name or state abbreviation, even if country_id is provided...
Example:
```
$result = civi...Replicable on dmaster.demo
The Address API is not taking into account the country_id when figuring the state_province_id when given the state name or state abbreviation, even if country_id is provided...
Example:
```
$result = civicrm_api3('Address', 'create', [
'contact_id' => 206,
'location_type_id' => "Main",
'city' => "Baltimore",
'state_province_id' => "Maryland",
'country_id' => 1228,
]);
```
Output:
```
{
"is_error": 0,
"version": 3,
"count": 1,
"id": 193,
"values": {
"193": {
"id": "193",
"contact_id": "206",
"location_type_id": "3",
"is_primary": "1",
"is_billing": "0",
"city": "Baltimore",
"state_province_id": "3497",
"country_id": "1228",
"manual_geo_code": "0"
}
}
}
```
Notice the state_province_id is 3497, which is Maryland, Liberia. It should be 1019.
This happens regardless of which countries are enabled in the Localization settings. It also does not respect the "default country" setting, if the country_id is not provided.5.13.0https://lab.civicrm.org/dev/core/-/issues/688Contacts -> New Email give Unknown Error in Smarty when Allow Mail to be sent...2019-03-21T08:26:23ZspalmstromContacts -> New Email give Unknown Error in Smarty when Allow Mail to be sent from logged in contact's email address disabledI raised this issue in [StackExchange](https://civicrm.stackexchange.com/questions/27901/contacts-new-email-give-unknown-error-in-smarty-when-allow-mail-to-be-sent-fr) some time ago, but didn't get a response. I was able to reproduce it...I raised this issue in [StackExchange](https://civicrm.stackexchange.com/questions/27901/contacts-new-email-give-unknown-error-in-smarty-when-allow-mail-to-be-sent-fr) some time ago, but didn't get a response. I was able to reproduce it in one of the demo sites: [https://civicrm.demo.civihosting.com/civicrm/activity/email/add?atype=3&action=add&reset=1&context=standalone](https://civicrm.demo.civihosting.com/civicrm/activity/email/add?atype=3&action=add&reset=1&context=standalone). Basically, if you disable *Allow Mail to be sent from logged in user*, you see the error. It will probably arise from *any* disabled Boolean setting as it is generated when a request for a setting value returns FALSE. The 'offending' line is 54 in …/CRM/Core/Smarty/plugins/function.crmSetting.php. Lines 52-57 read:
```
$result = civicrm_api('setting', 'getvalue', $params);
unset($errorScope);
if ($result === FALSE) {
$smarty->trigger_error("Unknown error");
return NULL;
}
```
So, *any* setting returning FALSE will trigger the error. Replacing line 54 with
if ($result === null)
cures the issue, but what other effects does it have?
I am running CiviCRM 5.9.1 under Joomla.5.13.0https://lab.civicrm.org/dev/core/-/issues/664Add new indexes when updating log table schema regardless of engine change2019-03-18T00:07:09ZPatrick Figelpfigel@greenpeace.orgAdd new indexes when updating log table schema regardless of engine changeWhile working on [at.greenpeace.advancedlogtables](https://github.com/greenpeace-cee/at.greenpeace.advancedlogtables), I noticed that the `System.updatelogtables` API call only adds new indexes set by the `alterLogTables` hook when it's ...While working on [at.greenpeace.advancedlogtables](https://github.com/greenpeace-cee/at.greenpeace.advancedlogtables), I noticed that the `System.updatelogtables` API call only adds new indexes set by the `alterLogTables` hook when it's accompanied by an engine change.
That's generally fine when the indexes don't change, but once new ones are added you'd have to temporarily change the engine or apply the new ones manually.
Is there a particular reason why we'd want to keep that behaviour, or can we change it so that new indexes are added regardless of the engine?
I have [a patch](https://github.com/civicrm/civicrm-core/commit/eababeabc1d0e97a809c7c534ce7e5a73a065c65#diff-e0d65cfb03f2d6f003eb4d598ef7e50b) ready for this, but wanted to check if there's agreement on this first.5.13.0https://lab.civicrm.org/dev/core/-/issues/571Can't self-service cancel a recurring contribution made while you're logged in2020-08-12T12:19:57ZJonGoldCan't self-service cancel a recurring contribution made while you're logged inThe self-service recurring payment page is accessible either a) with a checksum, b) if you have "Edit contributions" permission ([code here](https://github.com/civicrm/civicrm-core/blob/master/CRM/Contribute/Form/CancelSubscription.php#L...The self-service recurring payment page is accessible either a) with a checksum, b) if you have "Edit contributions" permission ([code here](https://github.com/civicrm/civicrm-core/blob/master/CRM/Contribute/Form/CancelSubscription.php#L108-L115)).
However, if you're logged in when you create a recurring contribution, the email you receive doesn't contain a checksum ([code here](https://github.com/civicrm/civicrm-core/blob/master/CRM/Core/Payment.php#L1516)). So users without "Edit contributions" permission can't cancel their subscriptions.
I think the implication of allowing a user to cancel their payment via checksum is that we should also allow a user to cancel if the logged-in contact ID matches the contact ID of the recurring contribution. This would also mean on systems where users are logged in, you would have the added benefit of not worrying about expired checksums.
Is there any scenario in which we shouldn't allow a user to cancel a recurring subscription they "own"? If not I'll submit a PR.5.13.0JonGoldJonGoldhttps://lab.civicrm.org/dev/core/-/issues/536Contribution tab is slow to render for contacts with many contributions (in l...2019-04-26T23:08:59ZeileenContribution tab is slow to render for contacts with many contributions (in large database)We are finding that the contribution tab is extremely slow to render on contacts with a large number of contributions. While we are taking at a different scale db to most instances (many millions of contacts and the very slow to load con...We are finding that the contribution tab is extremely slow to render on contacts with a large number of contributions. While we are taking at a different scale db to most instances (many millions of contacts and the very slow to load contacts have > 10,000 donation) from my digging the issues that cause the slowness are not limited to us and the query improvements that work for us are generally applicable.
We are seeing maybe 10 queries that take about 6 seconds each. In each case it is possible to reduce the query to around 0.05 seconds with an index hint. The issue is exacerbated by the fact that several of the slower queries run twice each.
I've pasted all the queries below
1.
SELECT COUNT(*) as count,
SUM(total_amount) as amount,
AVG(total_amount) as average,
currency
FROM civicrm_contribution b
LEFT JOIN civicrm_line_item i ON i.contribution_id = b.id AND i.entity_table = 'civicrm_contribution' AND i.financial_type_id NOT IN (3,1,4,2)
WHERE b.contact_id IN (76) AND b.contribution_status_id = 1 AND b.is_test = 0 AND b.receive_date >= 20180101 AND b.receive_date < 20190101
GROUP BY currency;
2.
------------------------------
SELECT count( DISTINCT civicrm_contribution.id ) as rowCount
FROM civicrm_contact contact_a
LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' )
AND (contact_a.is_deleted = 0) ;
3.
------------------------------------
SELECT COUNT( conts.total_amount ) as total_count,
SUM( conts.total_amount ) as total_amount,
AVG( conts.total_amount ) as total_avg,
conts.currency as currency FROM (
SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
civicrm_contribution.currency FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 GROUP BY civicrm_contribution.id
) as conts
GROUP BY currency;
4.
------------------------------------
SELECT COUNT( conts.total_amount ) as total_count,
SUM( conts.total_amount ) as total_amount,
AVG( conts.total_amount ) as total_avg,
conts.currency as currency, SUBSTRING_INDEX(GROUP_CONCAT(conts.total_amount
ORDER BY conts.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
MAX(conts.civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count
FROM (SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
civicrm_contribution.currency
FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1
GROUP BY currency, civicrm_contribution.total_amount ORDER BY civicrm_contribution_total_amount_count DESC) as conts
GROUP BY currency;
5.
---------------------------------------
SELECT count(*) as count FROM civicrm_contact contact_a
LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0)
AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'AUD';
6.
------------------------------------
SELECT civicrm_contribution.total_amount as median
FROM civicrm_contact contact_a
LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
WHERE ( contact_a.id = '76' )
AND (contact_a.is_deleted = 0)
AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1
AND civicrm_contribution.currency = 'AUD'
ORDER BY median LIMIT 0,1;
7.
------------------------------------
SELECT count(*) as count FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'NZD';
8.
------------------------------------
SELECT civicrm_contribution.total_amount as median
FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'NZD'
ORDER BY median LIMIT 0,1;
9.
------------------------------------
SELECT count(*) as count FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'USD';
10.
------------------------------------
SELECT civicrm_contribution.total_amount as median
FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'USD'
ORDER BY median LIMIT 0,2;
11.
------------------------------------
SELECT COUNT( conts.total_amount ) as cancel_count,
SUM( conts.total_amount ) as cancel_amount,
AVG( conts.total_amount ) as cancel_avg,
conts.currency as currency FROM (
SELECT civicrm_contribution.total_amount, civicrm_contribution.currency FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.cancel_date IS NOT NULL
GROUP BY civicrm_contribution.id
) as conts
GROUP BY currency;
12.
------------------------------------
SELECT COUNT( conts.total_amount ) as total_count,
SUM( conts.total_amount ) as total_amount,
AVG( conts.total_amount ) as total_avg,
conts.currency as currency FROM (
SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
civicrm_contribution.currency FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 GROUP BY civicrm_contribution.id
) as conts
GROUP BY currency;
13.
------------------------------------
SELECT COUNT( conts.total_amount ) as total_count,
SUM( conts.total_amount ) as total_amount,
AVG( conts.total_amount ) as total_avg,
conts.currency as currency, SUBSTRING_INDEX(GROUP_CONCAT(conts.total_amount
ORDER BY conts.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
MAX(conts.civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count
FROM (SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
civicrm_contribution.currency FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1
GROUP BY currency, civicrm_contribution.total_amount ORDER BY civicrm_contribution_total_amount_count DESC) as conts
GROUP BY currency;
14.
------------------------------------
SELECT count(*) as count FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'AUD';
15.
------------------------------------
SELECT civicrm_contribution.total_amount as median
FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'AUD'
ORDER BY median LIMIT 0,1;
16.
------------------------------------
SELECT count(*) as count FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'NZD';
17.
------------------------------------
SELECT civicrm_contribution.total_amount as median
FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'NZD'
ORDER BY median LIMIT 0,1;
18.
------------------------------------
SELECT count(*) as count FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'USD';
19.
------------------------------------
SELECT civicrm_contribution.total_amount as median
FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'USD'
ORDER BY median LIMIT 0,2;
20.
------------------------------------
SELECT COUNT( conts.total_amount ) as cancel_count,
SUM( conts.total_amount ) as cancel_amount,
AVG( conts.total_amount ) as cancel_avg,
conts.currency as currency FROM (
SELECT civicrm_contribution.total_amount, civicrm_contribution.currency FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( contact_a.id = '76' ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.cancel_date IS NOT NULL
GROUP BY civicrm_contribution.id
) as conts
GROUP BY currency;
21.
------------------------------------
SELECT a.id as `id`, a.contact_id as `contact_id`, a.amount as `amount`, a.currency as `currency`, a.frequency_unit as `frequency_unit`, a.frequency_interval as `frequency_interval`, a.installments as `installments`, a.start_date as `start_date`, a.create_date as `create_date`, a.modified_date as `modified_date`, a.cancel_date as `cancel_date`, a.end_date as `end_date`, a.processor_id as `processor_id`, a.payment_token_id as `payment_token_id`, a.trxn_id as `trxn_id`, a.invoice_id as `invoice_id`, a.contribution_status_id as `contribution_status_id`, a.is_test as `is_test`, a.cycle_day as `cycle_day`, a.next_sched_contribution_date as `next_sched_contribution_date`, a.failure_count as `failure_count`, a.failure_retry_date as `failure_retry_date`, a.auto_renew as `auto_renew`, a.payment_processor_id as `payment_processor_id`, a.financial_type_id as `financial_type_id`, a.payment_instrument_id as `payment_instrument_id`, a.campaign_id as `campaign_id`, a.is_email_receipt as `is_email_receipt`
FROM civicrm_contribution_recur a
WHERE (a.contact_id = "76") AND (a.contribution_status_id IN ("3", "10", "7", "1"))
ORDER BY a.is_test, a.start_date DESC;
22.
------------------------------------
SELECT SQL_CALC_FOUND_ROWS ccs.id, ccs.amount as amount,
ccs.contribution_id,
ccs.pcp_id,
ccs.pcp_display_in_roll,
ccs.pcp_roll_nickname,
ccs.pcp_personal_note,
ccs.soft_credit_type_id,
sov.label as sct_label,
cc.receive_date,
cc.contact_id as contributor_id,
cc.contribution_status_id as contribution_status_id,
cov.label as contribution_status,
cp.title as pcp_title,
cc.currency,
contact.display_name as contributor_name,
cct.name as financial_type
FROM civicrm_contribution_soft ccs
LEFT JOIN civicrm_contribution cc
ON ccs.contribution_id = cc.id
LEFT JOIN civicrm_pcp cp
ON ccs.pcp_id = cp.id
LEFT JOIN civicrm_contact contact ON
ccs.contribution_id = cc.id AND cc.contact_id = contact.id
LEFT JOIN civicrm_financial_type cct ON cc.financial_type_id = cct.id
LEFT JOIN civicrm_option_value sov ON sov.option_group_id = %3 AND ccs.soft_credit_type_id = sov.value
LEFT JOIN civicrm_option_value cov ON cov.option_group_id = %4 AND cc.contribution_status_id = cov.value
WHERE cc.is_test = %2 AND ccs.contact_id = %1 ORDER BY cc.receive_date DESC ;
23.
------------------------------------
SELECT count( x.id ) count FROM (
SELECT contribution.id AS id
FROM civicrm_contribution contribution
LEFT JOIN civicrm_line_item i ON i.contribution_id = contribution.id AND i.entity_table = 'civicrm_contribution' AND i.financial_type_id NOT IN (3,1,4,2)
WHERE contribution.is_test = 0 AND contribution.contact_id = 76
AND contribution.financial_type_id IN (3,1,4,2)
AND i.id IS NULL UNION
SELECT contribution.id
FROM civicrm_contribution contribution INNER JOIN civicrm_contribution_soft softContribution
ON ( contribution.id = softContribution.contribution_id )
WHERE contribution.is_test = 0 AND softContribution.contact_id = 76 ) x;5.13.0https://lab.civicrm.org/dev/core/-/issues/433Queries combining multiple text labels using REGEXP do not escape regular exp...2019-12-15T05:25:58ZAgilewareQueries combining multiple text labels using REGEXP do not escape regular expression metacharactersMost obvious example of this is the "Fee Level" options for events.
We have a site that had set their price set option like:
Price set option name - $120 (GST incl)
This fails during search, either because of the characters after the ...Most obvious example of this is the "Fee Level" options for events.
We have a site that had set their price set option like:
Price set option name - $120 (GST incl)
This fails during search, either because of the characters after the $ end-of-string match, or because (GST incl) is interpreted as a sub-expression rather than matching the parentheses when it's selected as a fee level to search for.
Currently we're working on a solution using preg_quote.
Agileware Ref: CIVICRM-9905.13.0https://lab.civicrm.org/dev/core/-/issues/314Transfer picks up the deleted contact ID basically transferring to the wrong ...2019-03-23T20:47:10ZaniesshsethhTransfer picks up the deleted contact ID basically transferring to the wrong contactTransfer picks up the deleted contact ID basically transferring to the wrong contact. We should be checking if the contact related to that email is deleted or notTransfer picks up the deleted contact ID basically transferring to the wrong contact. We should be checking if the contact related to that email is deleted or not5.13.0https://lab.civicrm.org/dev/core/-/issues/288Use the correct membership date for the notification that appear after comple...2019-04-01T21:27:31Zomar_compucorpUse the correct membership date for the notification that appear after completing the membership payment in case pre hook is usedSuppose you have an extension that implements **hook_civicrm_pre** on membership entity that add 1 year (or whatever amount of time) to the membership new end date whenever someone completed a pending payment for the membership, Based on...Suppose you have an extension that implements **hook_civicrm_pre** on membership entity that add 1 year (or whatever amount of time) to the membership new end date whenever someone completed a pending payment for the membership, Based on this assume the following example :
1- You created a pending membership starts today (27/7/2018) and ends in 1 year (26/7/2019).
2- You changed this membership payment (contribution) from "pending" to "completed".
Now without the hook implementation as explained above, the following message will appear :
```
the contribution record has been saved.
Membership for "CONTACT NAME" has been updated. The membership End Date is July 26th, 2019.
```
and the membership status will be change from Pending to New, And the end date of the membership will be similar to the one shown in the notification message which is 26/7/2019 which is all good.
But when you have the pre hook implemented as suggest above, the membership end date will be 26/7/2020 (since the hook add one year to the end date) but the notification message will still indicate the old end date which is 26/7/2019. This is wrong and the notification message should show the correct end date that reflects the membership actual end date.5.13.0https://lab.civicrm.org/dev/core/-/issues/3268Deprecate `getBasicContactFields` in favor of `getColumns('Contact')`2022-04-22T15:53:15ZJonGoldDeprecate `getBasicContactFields` in favor of `getColumns('Contact')`@eileen has ported some of the cleaner code for defining report specs from Extended Reports to core in the form of `getColumns()` and `getContactColumns()`. Since this appears to be the direction we're headed in, I think it makes sense ...@eileen has ported some of the cleaner code for defining report specs from Extended Reports to core in the form of `getColumns()` and `getContactColumns()`. Since this appears to be the direction we're headed in, I think it makes sense to deprecate `getBasicContactColumns()`, which does a similar job. However, `getBasicContactColumns()` adds a bunch of fields to core reports that `getContactColumns` doesn't.
My PR adds all the missing fields to `getContactColumns` (except for "Organization Name"; this seems unnecessary since it will virtually always match the display name). I also mark `getBasicContactFields()` as deprecated so future cleanup can target reports using it.5.12.0JonGoldJonGoldhttps://lab.civicrm.org/dev/core/-/issues/720Performance change approved - remove mode & median slow queries2019-02-19T04:53:15ZeileenPerformance change approved - remove mode & median slow queriesUpdate - simply removing per decision by @colemanw below....
---------------------------------------------------------------
Original
----------------------------------------------------------------
The calculations done for the summary...Update - simply removing per decision by @colemanw below....
---------------------------------------------------------------
Original
----------------------------------------------------------------
The calculations done for the summary statistics on the contribution search is currently the biggest point of slowness we are facing. The following stats are generated:
'count' (number completed)
'amount' (total amount of completed)
'avg' (average amount of completed)
'mode' (most common value of completed)
'median' (median value of complete)
'cancel_amount' (total of cancelled)
'cancel_count' (number cancelled)
'cancel_avg' (average amount of cancelled)
These are then grouped by currency.
Of these the count and total amount are highly useful whereas the usefulness of mode & median are more niche.
On the other hand it is possible to fix up MOST of these queries to perform well. Mode and median are pretty much impossible to make performant on a large result set, and are actually the main reason our users have to do carefully constrained queries that don't return more than around 50k of results.
Let's assume we do a query that returns 50,000 results and the criteria is the payment_instrument_id then ideally that field will be used as the index on the query and we get the main query returned pretty quickly.
However, in order to do a median query it is necessary to order the results by total_amount. We can't use both indexes, and we can't add combined indexes for every combination of total_amount & possible criteria so we are one way or another going to be either
- using the total_amount index to sort & doing an unindexed filter - on our whole DB....
- using the index to filter & doing an unindexed sort on 50k rows
- using a merged index (these take time to compile)
Some queries are possible to rewrite - I recently got the annual query down from 6 seconds to .02 seconds but the median query just isn't every going to scale well.
Which leaves us with 'how can we help sites that with users are not happy twiddling their thumbs while the median is calculated'.
There are a few options IMHO
1) just remove median & mean - no-one (by which I mean me) cares about them anyway
2) add a setting that allows a site to specify which contribution stats they want calculated
3) only calculate median & mean if the total number of rows is < 1000
4) add a hook to permit the queries that run to be altered
Of these both 1 & 3 are imposing change on people who might not want change.
Adding a setting seems like a hack. In general adding settings to tweak core behaviour for a different use case/ preferences is almost always a hack - although the use case 'I have a big database' is perhaps a bit more generic than the 'I'd like this page/search bar / widget to behave differently & the least hassle on me is to add a setting'
I do think, however, that 4 is probably the least hacky / most sensible and it will 'gracefully retire itself' when we finally get a better search screen that doesn't use the query object.
I think it would look like
```
hookAlterQuerySummary($entity, $context, &$callbacks);
```
(only Contribution is relevant at the moment but passing entity seems to make sense)
We would have to break out the existing queries to their own fns & then we'd get
$callbacks = [
'CRM_Contact_BAO_Query::getBasicStats',
'CRM_Contact_BAO_Query::getMedian',
'CRM_Contact_BAO_Query::getMean',
'CRM_Contact_BAO_Query::getCancelStats'
]
There would then be a call like
CRM_Contact_BAO_Query::getBasicStats($rowStats, $whereClause, $fromClause)
And $rowStats would be altered by the function adding values & labels so the tpl could iterate through them
Longer term - I would argue the slow stats should probably be ADDED rather than REMOVED by extension as I think shipping something that makes hard-to-justify performance trade-offs is a big call5.12.0