Development issueshttps://lab.civicrm.org/groups/dev/-/issues2022-08-25T21:35:03Zhttps://lab.civicrm.org/dev/core/-/issues/3154Custom tokens not working in Scheduled Reminders2022-08-25T21:35:03ZmartyCustom tokens not working in Scheduled RemindersOverview
----------------------------------------
Custom tokens are not evaluated for Scheduled Reminders when initiated by Cron Job. The tokens are evaluated properly when the Scheduled Reminders job is run manually using the Execute No...Overview
----------------------------------------
Custom tokens are not evaluated for Scheduled Reminders when initiated by Cron Job. The tokens are evaluated properly when the Scheduled Reminders job is run manually using the Execute Now option.
Reproduction steps
----------------------------------------
1. Create a custom token using hook_civicrm_container() and implement the civi.token.list and civi.token.eval event listeners.
1. Add a new Scheduled Reminder (I'm using membership end date) and include the custom token in the email message.
1. Create a Cron Job to run civicrm/bin/cron.php periodically (I run every 15 minutes).
1. Enable the Send Scheduled Reminders job and set to run Always.
1. Trigger the reminder appropriately (I create a new membership and set the end date to trigger).
1. Note the custom token is __not__ included in the resulting email after the cron run.
1. Now trigger a new reminder and click Execute Now on the Scheduled Reminders job (before the next cron run).
1. Note the custom token __is evaluated properly__ and included in the resulting email message.
Current behaviour
----------------------------------------
Custom token not included in Scheduled Reminder email when initiated by Cron Job
Expected behaviour
----------------------------------------
Custom token should be included in Scheduled Reminder email when initiated by Cron Job.
Environment information
----------------------------------------
* __CiviCRM:__ _5.47.2_
* __PHP:__ _7.4.28_
* __CMS:__ _WordPress 5.9.2_
* __Database:__ _MySQL_
* __Web Server:__ _Apache_https://lab.civicrm.org/dev/core/-/issues/3152Data stored in universal time does not handle DST consistently2023-11-30T05:00:20ZtottenData stored in universal time does not handle DST consistently[[_TOC_]]
Overview
----------------------------------------
CiviCRM has a number of `TIMESTAMP` columns -- these are stored in universal time (UTC) and displayed in the user's timezone. However, there is a subtle error in handling Dayl...[[_TOC_]]
Overview
----------------------------------------
CiviCRM has a number of `TIMESTAMP` columns -- these are stored in universal time (UTC) and displayed in the user's timezone. However, there is a subtle error in handling Daylight Savings Time (DST): if the current-date and the target-date sit on different sides of the DST-switch, then the time may present as +/- 1 hour.
This bug was one of the major subissues identified in https://lab.civicrm.org/dev/core/-/issues/2122. Although that particular feature was rolled-back/deferred from 5.47, the DST bug still exists -- it's just less obvious.
Example use-case
----------------------------------------
1. Create a contact record.
2. View the contact record. Note the creation time (`civicrm_contact.created_date`).
3. Change the system clock - set to a date where DST differs (eg if today is March 30, then go to December 5).
4. View the contact record. Note the creation time (`civicrm_contact.created_date`).
Current behavior
----------------------------------------
The displayed value of `civicrm_contact.created_date` _appears_ to change by +/- 1 hour, depending on when you view it.
> (Viewed on Mar 31, 2022)
>
> ![Screen_Shot_2022-03-31_at_12.40.53_AM](/uploads/8a2f5a0e4fb6ec884aede78e30d31b1a/Screen_Shot_2022-03-31_at_12.40.53_AM.png)
> (Viewed on Dec 5, 2022)
>
> ![Screen_Shot_2022-12-05_at_12.44.57_AM](/uploads/2df387488ee029a949010da163bf2ea8/Screen_Shot_2022-12-05_at_12.44.57_AM.png)
Why? CiviCRM sends a note to MySQL about the current user's timezone (`SET time_zone = '...'`). However, it doesn't identify the timezone effectively. It gives [the current numeric offset (at the moment of viewing)](https://github.com/civicrm/civicrm-core/blob/5.47.3/CRM/Utils/System/Base.php#L758-L762) - but (in locales with DST) the offsets fluctuate over time.
(_Ex: On Mar 31, the offset in California is `-0700`. Under current/long-standing law, the offset will be `-0800` on Dec 5. Of course, the US Congress is reconsidering this law... so we don't really know what the offset will be!_)
Proposed behavior 1: Fix MySQL timezones
----------------------------------------
CiviCRM should send the timezone as a symbolic name, such as `Europe/Helsinki`, `America/Los_Angeles`, or `Australia/Sydney`. These symbolic-names have an underlying database which allows them adjust automatically based on DST-rules/target-dates/current-law. On the surface, the fix is extremely simple:
```diff
diff --git a/CRM/Utils/System/Base.php b/CRM/Utils/System/Base.php
index a4660834c5..8e40f6da35 100644
--- a/CRM/Utils/System/Base.php
+++ b/CRM/Utils/System/Base.php
@@ -755,10 +755,9 @@ abstract class CRM_Utils_System_Base {
* Set timezone in mysql so that timestamp fields show the correct time.
*/
public function setMySQLTimeZone() {
- $timeZoneOffset = $this->getTimeZoneOffset();
- if ($timeZoneOffset) {
- $sql = "SET time_zone = '$timeZoneOffset'";
- CRM_Core_DAO::executequery($sql);
+ $timeZone = $this->getTimeZoneString();
+ if ($timeZone) {
+ CRM_Core_DAO::executequery('SET time_zone = %1', [1 => [$timeZone, 'String']]);
}
}
```
There are a couple of catches.
* __Timezone rules change__ (occasionally). Any software that supports timezones ultimately needs a _data feed_ with current rules. The good news: IANA publishes a free/open feed (https://www.iana.org/time-zones; aka `tzdata`; aka `zoneinfo`), most Linux/Unix distros have this feed, and MySQL can read it (`mysql_tzinfo_to_sql`). It usually requires one command (which could run during system-config, system-startup, and/or cron):
```bash
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql
```
The problem: we have no measures for (a) how many CiviCRM deployments actually subscribe to this feed and (b) how many could subscribe, if they chose to.
* __Timezone names may be inconsistent__ (occasionally). For example, in different contexts, it's been fashionable to refer to California's timezone as `America/Los_Angeles`, `US/Pacific`, and `PST8PDT`. (The current+official fashion is `America/Los_Angeles` - the others are deprecated.) However, since Civi integrates with various layers (different CMSs; PHP APIs; MySQL APIs), there are edge-case where the layers may choose different names. (*I'm not super-concerned, but we should raise sensible warnings when names are invalid or mismatched.*)
The central issue is - how to cope when data isn't available? This comes to mind:
* (Status check) If the active TZ (`getTimeZoneString()`) has a deprecated name (eg `PST8PDT`) or an offset (eg `-0700`), show a warning.
* (Status check) If the active TZ (`getTimeZoneString()`) isn't supported by MySQL, show a warning.
* (Runtime) If the active TZ (`getTimeZoneString()`) isn't supported by MySQL, fallback to sending offset.
Proposed behavior 2: Change format. Use only PHP TZs.
----------------------------------------
(_This expands on one of @haystack's suggestions in dev/core#2122._)
If you assume that MySQL time services aren't available - what else would you do? You could use PHP time services.
The astute observer will note the status-quo (using both PHP and MySQL time-services) creates two points-of-failure. If either PHP _or_ MySQL has bad/incomplete/old timezone data, then you'll get mis-calculations _somewhere_. Consolidating on PHP time-services would reduce the #dependencies.
Both Drupal and WordPress take this approach. (I suspect this is extremely useful for maximizing compatibility with heterogeneous web-hosts.) They each do it a bit differently, but some central concepts are the same:
* In Drupal, PHP processes read+write temporal data in universal time -- as an `INT` (Unix-style, seconds-since-epoch).
* In WordPress, PHP processes read+write temporal data in universal time -- as a `DATETIME` with a `_gmt` suffix (eg `post_modified_gmt`).
* Hypothetically, you could hardcode MySQL to `SET time_zone='+0:00'`. PHP processes would read+write temporal data in universal time -- as a `TIMESTAMP`.
In all those cases, the onus is on the PHP devs to convert to/from universal-time when implementing functionality (eg "find records from March 1 - March 15" or "find records from this afternoon" or "extract the hour:minute component").
But there is a catch here: Civi already relies on several MySQL time-services. The schema works a certain way; the reports/searches/UIs/APIs expect the schema to work a certain way; etc.
The central issue is - how do you manage/QA all the changes (in schema+logic) required to change time-service?
Comments
----------------------------------------
* I haven't tested, but I'm fairly certain there will be another manifestation in CiviMail scheduling. Ex:
* You live in a timezone where DST changes on March 16.
* On March 10, you schedule a mail-blast for 2:00pm on March 20. It stores the schedule with the wrong offset.
* When March 20 comes, the mailing actually goes out at 3:00pm (or maybe 1:00pm).https://lab.civicrm.org/dev/core/-/issues/3148Dedupe with multi-select custom fields can trigger IDS2023-03-18T04:20:40ZJonGoldDedupe with multi-select custom fields can trigger IDSWhen deduping contacts that have multi-select custom fields, and selecting to move the custom fields to the new contact, the IDS is triggered.
### Steps to replicate
* Create a custom field that allows saving multiple values (e.g. a che...When deduping contacts that have multi-select custom fields, and selecting to move the custom fields to the new contact, the IDS is triggered.
### Steps to replicate
* Create a custom field that allows saving multiple values (e.g. a checkbox). Note that you need several of these to trigger the "kick" on the IDS (3, I think).
* Create two contacts that are duplicates.
* Find and merge the records.
### Expected result
Contacts merged successfully.
### Actual result
"Your activity is a bit suspicious, hence aborting"
The issue is the POST request, which is passing arguments like `move_custom_12` with the `VALUE_SEPARATOR` control character. This triggers the IDS filter labeled "Detects nullbytes and other dangerous characters".
I'm really not certain what the correct answer is here - I can exempt users from the IDS, and maybe that's the solution to pursue, but it seems like there should be another solution available. Is it possible to exempt certain paths from the IDS, or use an alternate set of rules for a certain path?
Keyword: Intrusion Detection Systemhttps://lab.civicrm.org/dev/core/-/issues/3145CiviCase: if contact is an Organization or Household, cannot change Case Coor...2023-11-23T13:15:30ZAllenShawCiviCase: if contact is an Organization or Household, cannot change Case Coordinator (or, must edit Case Coordinator relationship type)# To reproduced on https://dmaster.demo.civicrm.org/ as of today ("Powered by CiviCRM 5.49.alpha1"):
1. Observe that the Homeless Services Coordinator role is defined as in a default installation (contact a type and contact b type are b...# To reproduced on https://dmaster.demo.civicrm.org/ as of today ("Powered by CiviCRM 5.49.alpha1"):
1. Observe that the Homeless Services Coordinator role is defined as in a default installation (contact a type and contact b type are both 'individual')
2. Create a new "Housing Support" case with an Organization contact for the Client.
3. Open Manage Case page for this case
4. Under Roles accordion, click the pencil icon to edit the "Homeless Services Coordinator is (Case Manager)" role
5. Observe pop-up overlay "Reassign Homeless Services Coordinator" where you would select an individual for this role
5. At this point:
* Expected behavior: This pop-up contains a contact reference field allowing me to type a contact name or email address
* Actual behavior: This pop-up may be displayed partially outside of the viewport, with no way to scroll to see all of it; in any case, you can see -- if you're able to use Developer Tools to make the thing display within the viewport -- that the expected contact reference field is just a plain text field; also the Save and Cancel buttons are non-functional, and the only thing I can do is to click the X control to close the pop-up:
![popup](/uploads/92a2aa68c67897f1da5b8ee4ee3e2a7d/popup.png)
# Workaround
1. Edit the Homeless Services Coordinator role to allow Organizations in the Contact A position.
2. Repease the repro steps above and observe expected behavior.
# Other thoughts:
* Testing on client sites indicates his is not limited to the "Homeless Services Coordinator" relationship type; instead, it's happening for any relationship type which is confiugured as the type for the Case Coordinator role.
* Not sure about how best to design a fix from the UX perspective. Prevent case creation? Warn in System Status?https://lab.civicrm.org/dev/core/-/issues/3139Badgelayouts cannot be edited with PHP warning2022-09-01T13:49:35ZBradley TaylorBadgelayouts cannot be edited with PHP warning_Reproduced on dmaster and locally on WordPress_
**Steps to reproduce**
1. Navigate to "Administer CiviCRM", "Event Name Badge Layouts".
2. Create a new name badge
3. Edit the newly created name badge.
**Expected outcode**
The edit scr..._Reproduced on dmaster and locally on WordPress_
**Steps to reproduce**
1. Navigate to "Administer CiviCRM", "Event Name Badge Layouts".
2. Create a new name badge
3. Edit the newly created name badge.
**Expected outcode**
The edit screen should be pre-filled with the values entered initially.
**Actual outcome**
Each field is blank, a PHP warning is shown:
![Screenshot_2022-03-27_at_10.18.33](/uploads/105dc6046cbb9b569207500da40cf77f/Screenshot_2022-03-27_at_10.18.33.png)
**Technical explanation**
The bug was introduced in https://github.com/civicrm/civicrm-core/commit/873bfeb503caa413f17460dbe450b74fac3d6dbf.
The commit above added a new tokens:
```
'{event.start_date|crmDate:"%B %E%f"}' => ts('Event Start Date'),
'{event.end_date|crmDate:"%B %E%f"}' => ts('Event End Date'),`
```
The data for badge layouts is stored as encoded JSON. This means that the quote marks in these two tokens are being wrapped in double-quotes for the string, causing something like `"{event.start_date|crmDate:"%B %E%f"}"`. As such the JSON is not valid and cannot be `json_decode`ed.
The actual fix could be straightforward enough: Switch the tokens to use single instead of double quotes. However, I'm not sure what the correct solution is for any broken JSON which is now stored in CiviCRM databases. Some sort of upgrade script might be required to find/replace the known broken JSON.
Pinging @eileen who did a lot of work on tokens last year.https://lab.civicrm.org/dev/core/-/issues/3137FormBuilder - setting a field as 'required' does not implement any validation...2023-02-28T19:52:15ZUpperholmeFormBuilder - setting a field as 'required' does not implement any validation checkCreating a submission form using Form Builder, it is possible to set any given field to be 'Required'.
I'm assuming that by doing this the form could not then be submitted if the required field is empty. A validation error should be res...Creating a submission form using Form Builder, it is possible to set any given field to be 'Required'.
I'm assuming that by doing this the form could not then be submitted if the required field is empty. A validation error should be resented assisting the user to complete the required field/s.
In practice the form can be submitted with no alert or apparent validation check taking place.
Using CiviCRM 5.47.2 with Wordpress.Kurund JalmiKurund Jalmihttps://lab.civicrm.org/dev/wordpress/-/issues/120Recurring contribution page on frontend tries to access wp-admin.2023-11-23T07:47:01ZdandrzejewskiRecurring contribution page on frontend tries to access wp-admin.I've got a user dashboard that includes recurring contributions. A recurring contribution has two links, cancel and view. When clicking the "view" link, the user is directed to the following URL:
https://example.org/civi/contact/view/...I've got a user dashboard that includes recurring contributions. A recurring contribution has two links, cancel and view. When clicking the "view" link, the user is directed to the following URL:
https://example.org/civi/contact/view/contributionrecur/?reset=1&id=2&cid=66&context=dashboard
This page contains two pieces, the details on the recurring contribution and the "related contributions" - i.e. the previous iterations of the recurring contribution.
However, the second part of the page only works when users have the "access the civicrm backend and API" permission, and I believe that's because the second half is retrieved using this URL:
https://example.org/wp-admin/admin.php?page=CiviCRM&q=civicrm%2Fcontribute%2Fcontributionrecur-payments&reset=1&id=2&cid=66&snippet=json
If I remove that permission, the user gets an error.
Additionally, and I can open a separate bug for this if it is in fact a bug, there's a "done" button on the contribution detail page. This attempts to take the user back to the civicrm "main" dashboard (on the front-end) rather than the user dashboard where we came from.
I can provide any other details, logs, etc as needed.https://lab.civicrm.org/dev/core/-/issues/3130Lack of hooks to detect when an Attachment is deleted2023-12-07T08:27:58ZhaystackLack of hooks to detect when an Attachment is deletedOverview
----------------------------------------
At present, when an Attachment is deleted via the CiviCRM UI (e.g. on "Edit Activity" dialogs/screens) only two CiviCRM hooks fire: the `civi.dao.preDelete` and `civi.dao.postDelete` Symf...Overview
----------------------------------------
At present, when an Attachment is deleted via the CiviCRM UI (e.g. on "Edit Activity" dialogs/screens) only two CiviCRM hooks fire: the `civi.dao.preDelete` and `civi.dao.postDelete` Symfony events triggered by `CRM_Core_BAO_EntityTag::del()`. All other deletions are done via direct SQL queries.
It would be great to:
* Have `hook_civicrm_pre` and `hook_civicrm_post` fire
* Have some way of retrieving the Attachment data prior to deletion
Reproduction steps
----------------------------------------
1. Set up CiviCRM to log all hooks
1. Click the "bin/trash" icon next to an Attachment (as per the screenshot below)
1. Inspect logged callbacks
![attachments-accordion](/uploads/9abded9e0e7f9cc602213b8b20de345d/attachments-accordion.png)
Current behaviour
----------------------------------------
The following callbacks are the only ones that will be seen (data trimmed for concision)
#### `civi.dao.preDelete`
```
[event] => Civi\Core\DAO\Event\PreDelete Object
(
[object] => CRM_Core_BAO_EntityTag Object
(
[id] =>
[entity_table] => civicrm_file
[entity_id] => 6
[tag_id] =>
)
)
```
#### `civi.dao.postDelete`
```
[event] => Civi\Core\DAO\Event\PostDelete Object
(
[object] => CRM_Core_BAO_EntityTag Object
(
[id] =>
[entity_table] => civicrm_file
[entity_id] => 6
[tag_id] =>
)
[result] => 0
)
```
It is possible to use the `entity_id` to retrieve the "File" data via the CiviCRM API. However, because of the order of deletions in [the `deleteEntityFile()` method](https://github.com/civicrm/civicrm-core/blob/6769e8bf8556701b81f914d30fc1bb913f8ed2ce/CRM/Core/BAO/File.php#L250), it is _not_ possible to retrieve the compound "Attachment" data via the API (because the "Entity File" data has already been deleted) and it is therefore not possible to find out which Entity/Entities the File was attached to.
It should also be noted that when the "Delete All Attachment(s)" checkbox is used, the `civi.dao.preDelete` and `civi.dao.postDelete` Symfony events fire *before* `hook_civicrm_pre` is fired for the Activity. To me, it would make more sense for `hook_civicrm_pre` to fire *before* the File deletion process is initiated so that `Activity.pre` and `Activity.post` "wrap" the entire process.
Expected behaviour
----------------------------------------
I would expect to be able to receive callbacks from `hook_civicrm_pre` and `hook_civicrm_post`, where `$op = "delete"` and `$objectName = "File"` along with some more contextual data about the "File" and the "Attachment" being deleted.
Comments
----------------------------------------
I'm going to open a PR that addresses the issue of retrieving the "Attachment" data via the API when using the Symfony hook, since that's pretty straightforward.
I'd appreciate any guidance from those more familiar with this class on the most sensible place(s) to add hooks during the deletion process - or whether deletion should more properly be done via `BAO` objects instead of direct queries.https://lab.civicrm.org/dev/core/-/issues/3128Cases dashlet sometimes gives error when sort by subject2023-12-04T13:49:19ZDaveDCases dashlet sometimes gives error when sort by subjectPulling this out from https://lab.civicrm.org/dev/core/-/issues/1624 as a separate issue.
_Sometimes_, sorting by the subject on a cases dashlet will give a datatables error. The actual error if you look at the network response is `Colu...Pulling this out from https://lab.civicrm.org/dev/core/-/issues/1624 as a separate issue.
_Sometimes_, sorting by the subject on a cases dashlet will give a datatables error. The actual error if you look at the network response is `Column 'subject' in order clause is ambiguous`.
```
Error Field Error Value
Type DB_Error
Code -1
Message DB Error: unknown error
Mode 16
UserInfo SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id as activity_type_id, civicrm_case.case_type_id as case_type_id, civicrm_case.status_id as case_status_id, t_act.status_id as status_id, civicrm_case.start_date as case_start_date, GROUP_CONCAT(DISTINCT IF(case_relationship.contact_id_b = 2, case_relation_type.label_a_b, case_relation_type.label_b_a) SEPARATOR ', ') as case_role, t_act.activity_date_time as activity_date_time, t_act.id as activity_id, case_status.label AS case_status, civicrm_case_type.title AS case_type FROM civicrm_case INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id LEFT JOIN civicrm_option_group option_group_case_status ON ( option_group_case_status.name = 'case_status' ) LEFT JOIN civicrm_option_value case_status ON ( civicrm_case.status_id = case_status.value AND option_group_case_status.id = case_status.option_group_id ) LEFT JOIN civicrm_case_activity ca4 ON civicrm_case.id = ca4.case_id LEFT JOIN civicrm_activity t_act ON t_act.id = ca4.activity_id AND t_act.is_current_revision = 1 LEFT JOIN civicrm_phone ON civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary = 1 LEFT JOIN civicrm_relationship case_relationship ON ((case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 2) OR (case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 2)) AND case_relationship.is_active AND case_relationship.case_id = civicrm_case.id LEFT JOIN civicrm_relationship_type case_relation_type ON case_relation_type.id = case_relationship.relationship_type_id AND case_relation_type.id = case_relationship.relationship_type_id WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1 AND (case_relationship.contact_id_b = 2 OR case_relationship.contact_id_a = 2) AND case_relationship.is_active AND civicrm_case.status_id IN (1,3) GROUP BY case_id ORDER BY subject asc LIMIT 0, 10 [nativecode=1052 ** Column 'subject' in order clause is ambiguous]
DebugInfo SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id as activity_type_id, civicrm_case.case_type_id as case_type_id, civicrm_case.status_id as case_status_id, t_act.status_id as status_id, civicrm_case.start_date as case_start_date, GROUP_CONCAT(DISTINCT IF(case_relationship.contact_id_b = 2, case_relation_type.label_a_b, case_relation_type.label_b_a) SEPARATOR ', ') as case_role, t_act.activity_date_time as activity_date_time, t_act.id as activity_id, case_status.label AS case_status, civicrm_case_type.title AS case_type FROM civicrm_case INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id LEFT JOIN civicrm_option_group option_group_case_status ON ( option_group_case_status.name = 'case_status' ) LEFT JOIN civicrm_option_value case_status ON ( civicrm_case.status_id = case_status.value AND option_group_case_status.id = case_status.option_group_id ) LEFT JOIN civicrm_case_activity ca4 ON civicrm_case.id = ca4.case_id LEFT JOIN civicrm_activity t_act ON t_act.id = ca4.activity_id AND t_act.is_current_revision = 1 LEFT JOIN civicrm_phone ON civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary = 1 LEFT JOIN civicrm_relationship case_relationship ON ((case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 2) OR (case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 2)) AND case_relationship.is_active AND case_relationship.case_id = civicrm_case.id LEFT JOIN civicrm_relationship_type case_relation_type ON case_relation_type.id = case_relationship.relationship_type_id AND case_relation_type.id = case_relationship.relationship_type_id WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1 AND (case_relationship.contact_id_b = 2 OR case_relationship.contact_id_a = 2) AND case_relationship.is_active AND civicrm_case.status_id IN (1,3) GROUP BY case_id ORDER BY subject asc LIMIT 0, 10 [nativecode=1052 ** Column 'subject' in order clause is ambiguous]
PEAR_Exception: DB Error: unknown error in ...\sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php on line 922
- DB_Error: DB Error: unknown error in unknown on line unknown
Exception trace
# Function Location
0 CRM_Core_Error::exceptionHandler(Object(DB_Error)) ...\sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:922
1 PEAR_Error->__construct('DB Error: unknow…', -1, 16, Array, 'SELECT civicrm_c…') ...\sites\all\modules\civicrm\vendor\pear\db\DB.php:997
2 DB_Error->__construct(-1, 16, Array, 'SELECT civicrm_c…') ...\sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:575
3 PEAR::_raiseError(Object(DB_mysqli), null, -1, 16, Array, 'SELECT civicrm_c…', 'DB_Error', true) ...\sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:223
4 PEAR->__call('raiseError', Array) ...\sites\all\modules\civicrm\vendor\pear\db\DB\common.php:1928
5 DB_common->raiseError(-1, null, null, 'SELECT civicrm_c…', '1052 ** Column '…') ...\sites\all\modules\civicrm\vendor\pear\db\DB\mysqli.php:936
6 DB_mysqli->mysqliRaiseError() ...\sites\all\modules\civicrm\vendor\pear\db\DB\mysqli.php:406
7 DB_mysqli->simpleQuery('SELECT civicrm_c…') ...\sites\all\modules\civicrm\vendor\pear\db\DB\common.php:1234
8 DB_common->query('SELECT civicrm_c…') ...\sites\all\modules\civicrm\packages\DB\DataObject.php:2696
9 DB_DataObject->_query('SELECT civicrm_c…') ...\sites\all\modules\civicrm\packages\DB\DataObject.php:1829
10 DB_DataObject->query('SELECT civicrm_c…') ...\sites\all\modules\civicrm\CRM\Core\DAO.php:472
11 CRM_Core_DAO->query('SELECT civicrm_c…', true) ...\sites\all\modules\civicrm\CRM\Core\DAO.php:1637
12 CRM_Core_DAO::executeQuery('SELECT civicrm_c…') ...\sites\all\modules\civicrm\CRM\Case\BAO\Case.php:583
13 CRM_Case_BAO_Case::getCases(false, Array) ...\sites\all\modules\civicrm\CRM\Case\Page\AJAX.php:186
14 CRM_Case_Page_AJAX::getCases() ...\sites\all\modules\civicrm\CRM\Core\Invoke.php:285
15 CRM_Core_Invoke::runItem(Array) ...\sites\all\modules\civicrm\CRM\Core\Invoke.php:69
16 CRM_Core_Invoke::_invoke(Array) ...\sites\all\modules\civicrm\CRM\Core\Invoke.php:36
17 CRM_Core_Invoke::invoke(Array) ...\sites\all\modules\civicrm\drupal\civicrm.module:471
18 civicrm_invoke('ajax', 'get-cases') ...\includes\menu.inc:527
19 menu_execute_active_handler() ...\index.php:21
20 {main}
Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
DB Error: unknown error
```https://lab.civicrm.org/dev/core/-/issues/3121The mysql session timezone that civi sets does not observe daylight saving (a...2023-12-09T05:04:30ZDaveDThe mysql session timezone that civi sets does not observe daylight saving (aka The Contact Logging civireport can show the wrong time)Pulling this out as a separate issue from #2122. It's also different from that issue in that here the value stored in the database is correct, just the display is wrong.
Mysql/mariadb often do not ship with named timezones populated (i....Pulling this out as a separate issue from #2122. It's also different from that issue in that here the value stored in the database is correct, just the display is wrong.
Mysql/mariadb often do not ship with named timezones populated (i.e. the mysql.time_zone table and related tables are empty). So when civi sets the mysql timezone for the session it calculates an offset instead (e.g. '+01:00'), based on "now". Offsets don't know about DST rules, so when it's converted for display based on that "now" offset, it will be wrong if "now" is on the other side of DST from the date that was stored in the database. Example:
Suppose log_civicrm_contact has a log_date with a value of March 8, 2022 12:34, created in timezone America/New_York. When viewed on March 9, 10, 11, etc it displays correctly.
When viewed on, say, March 16, civi will set the mysql timezone as '-04:00', which is different from what it was on March 8 ('-05:00'). Therefore when it displays, it shows 13:34 instead of 12:34.
This only affects timestamp columns. Datetime columns don't do any conversion based on session timezone.
The code where this happens is at https://github.com/civicrm/civicrm-core/blob/1754e1752eb8e516ec846a438005f6fcb55be247/CRM/Utils/System/Base.php#L760
Note also that getTimeZoneString() does different things for different CMSs, so you may experience the issue slightly differently depending on the install.https://lab.civicrm.org/dev/core/-/issues/3103Document contract for alterMailParams2023-11-23T17:28:30ZDaveDDocument contract for alterMailParamsThe data in $params for alterMailParams is inconsistent, and with recent token changes has changed in some cases.
Copied from https://github.com/civicrm/civicrm-core/pull/22878, which is specific to message template emails (there are al...The data in $params for alterMailParams is inconsistent, and with recent token changes has changed in some cases.
Copied from https://github.com/civicrm/civicrm-core/pull/22878, which is specific to message template emails (there are also other types of emails that trigger alterMailParams):
> Quote
* Add a class CRM_Event_WorkflowMessage_OfflineReceipt. This would look a lot like CRM_Contribute_WorkflowMessage_ContributionOfflineReceipt which describes contribution_offline_receipt. (Also, there's some draft docs in https://lab.civicrm.org/documentation/docs/dev/-/merge_requests/987.)
* Add a variant of hook_alterMailParams which gives the message as an object. Here's a sketch for firing+consuming hook_alterWorkflowMessage [gist](https://gist.github.com/totten/14178dabea79ea2a5a639a5b928262e2).https://lab.civicrm.org/dev/core/-/issues/3096Marking historical contact data - add on hold, hold date, reset date columns ...2023-01-06T22:02:03ZandyburnsMarking historical contact data - add on hold, hold date, reset date columns to civicrm_address and civicrm_phone to match civicrm_emailCurrently only civicrm_email has a mechanism to mark a bad email. This data model should be mirrored over to civicrm_address and civicrm_phone. We currently use the lightweight "location type" approach where we have an Invalid location t...Currently only civicrm_email has a mechanism to mark a bad email. This data model should be mirrored over to civicrm_address and civicrm_phone. We currently use the lightweight "location type" approach where we have an Invalid location type to designate a bad email/phone/address. However, it is limiting as you can only have 1 address per location type for a contact.
This was previously brought up here but was not pursued: https://issues.civicrm.org/jira/browse/CRM-13777.
The rationale is storing older data on these 3 entities helps prevent the creation of duplicates when bringing external sources of data (that may be older as well) upon import and therefore need to remain in the respective db table and not only sent to a log table.
The limitation of 1 address per location type would not apply to on hold addresses, therefore creating a history of addresses.
Setting to on hold would automatically set the hold date.
On hold entities would be shown in a "Former Communications Data" tab much like this extension (https://civicrm.org/extensions/former-communication-data) to prevent cluttering the Contact Summary Screen by only querying the entities that are on hold (and conversely, the contact's summary screen would only show those not on hold) and showing them in descending On Hold date order. Allow filtering by email/phone/address.
On hold entities would not be able to used throughout CiviCRM. E.g. a on hold mobile phone would not show Outbound SMS action, an on hold address would be added as a criteria to this checkbox upon export:
![image](/uploads/d481ef9470febc1620b895af2fb1baff/image.png)
There is obviously overlap with what detailed logging does and this so I'd greatly appreciate anyone's thoughts on how to approach / refine this feature request. We are willing to fund a solution.
Ref:
- https://civicrm.stackexchange.com/questions/681/what-are-best-practices-for-bad-postal-addresses
- https://civicrm.stackexchange.com/questions/20584/need-historical-address-informationhttps://lab.civicrm.org/dev/drupal/-/issues/175Call to deprecated function CRM_Contribute_PseudoConstant::pcpStatus in drupa...2022-03-08T14:11:00ZDaveDCall to deprecated function CRM_Contribute_PseudoConstant::pcpStatus in drupal 7 views modulehttps://civicrm.stackexchange.com/questions/41350/why-am-i-getting-deprecated-function-warnings
Coming from https://github.com/civicrm/civicrm-drupal/blob/e22d89a50108a2b4da99e971d0fdcdd656edd56c/modules/views/civicrm/civicrm_handler_fi...https://civicrm.stackexchange.com/questions/41350/why-am-i-getting-deprecated-function-warnings
Coming from https://github.com/civicrm/civicrm-drupal/blob/e22d89a50108a2b4da99e971d0fdcdd656edd56c/modules/views/civicrm/civicrm_handler_filter_pseudo_constant.inc#L45
`$this->_pseudo_constant = call_user_func_array($this->definition['pseudo class'] . "::" . $this->definition['pseudo method'], $pseudo_args);`
```
1 .../sites/all/modules/civicrm/CRM/Contribute/PseudoConstant.php(352): CRM_Core_Error::deprecatedFunctionWarning("Function pcpStatus will be removed")
2 .../sites/all/modules/civicrm/drupal/modules/views/civicrm/civicrm_handler_filter_pseudo_constant.inc(45): CRM_Contribute_PseudoConstant::pcpStatus()
3 .../sites/all/modules/views/includes/handlers.inc(65): civicrm_handler_filter_pseudo_constant->construct()
4 .../sites/all/modules/views/includes/handlers.inc(87): _views_create_handler((Array:8), "handler", "filter")
5 .../sites/all/modules/views/views.module(1399): _views_prepare_handler((Array:8), (Array:18), "status", "filter")
6 .../sites/all/modules/date/date_views/includes/date_views_fields.inc(59): views_get_handler("civicrm_pcp", "status", "filter")
7 .../sites/all/modules/date/date_views/date_views.module(156): _date_views_fields("civicrm_contact")
8 .../sites/all/modules/calendar/includes/calendar.views_template.inc(37): date_views_fields("civicrm_contact")
9 .../sites/all/modules/views/views.module(1520): calendar_views_templates()
10 .../sites/all/modules/views/views_ui.module(56): views_get_all_templates()
11 .../includes/menu.inc(2831): views_ui_menu()
12 .../includes/menu.inc(2794): menu_router_build()
13 .../includes/menu.inc(468): menu_rebuild()
14 .../includes/menu.inc(1779): menu_get_item()
15 .../includes/menu.inc(1794): menu_get_custom_theme(TRUE)
16 .../includes/common.inc(5402): menu_set_custom_theme()
17 .../includes/bootstrap.inc(2602): _drupal_bootstrap_full()
18 .../index.php(20): drupal_bootstrap(7)
19 {main}
```https://lab.civicrm.org/dev/core/-/issues/3089Meta: Create list of items for moving a core component to an extension2024-03-08T05:07:35ZDaveDMeta: Create list of items for moving a core component to an extensionIt's never really been fully done before. Some of the Grant things are likely to come up again if it's not documented. Partial list just quickly compiled from tickets. Doesn't include the things that did work (which would still need doin...It's never really been fully done before. Some of the Grant things are likely to come up again if it's not documented. Partial list just quickly compiled from tickets. Doesn't include the things that did work (which would still need doing), and might be missing some PRs that had no tickets.
* https://lab.civicrm.org/dev/core/-/issues/3069
* https://lab.civicrm.org/dev/core/-/issues/3076
* https://lab.civicrm.org/dev/core/-/issues/3056
* https://lab.civicrm.org/dev/core/-/issues/3057
* https://lab.civicrm.org/dev/core/-/issues/3087
* https://lab.civicrm.org/dev/core/-/issues/3093
* https://lab.civicrm.org/dev/core/-/issues/3100
* https://github.com/civicrm/civicrm-buildkit/pull/677
* https://lab.civicrm.org/dev/core/-/issues/3101
* https://github.com/colemanw/webform_civicrm/pull/719
* extendedreports
* https://github.com/eileenmcnaughton/nz.co.fuzion.extendedreport/commit/6184de98c5b9f40475ab4164c993b19d68561240
* https://github.com/eileenmcnaughton/nz.co.fuzion.extendedreport/pull/503
* https://github.com/eileenmcnaughton/nz.co.fuzion.extendedreport/pull/506
* civicrm_entity
* https://github.com/eileenmcnaughton/civicrm_entity/pull/364
* an unresolved cache(?) issue
* https://github.com/twomice/com.joineryhq.jsumfields/pull/23
* https://github.com/civicrm/civicrm-drupal/pull/653
* https://github.com/civicrm/civicrm-drupal/pull/656
* https://github.com/civicrm/civicrm-core/pull/22905
* https://lab.civicrm.org/dev/core/-/issues/3118
* https://lab.civicrm.org/dev/core/-/issues/3119
* https://lab.civicrm.org/dev/core/-/issues/3159
* https://github.com/civicrm/civicrm-core/pull/23116
* https://github.com/civicrm/civicrm-core/pull/23118
* https://github.com/civicrm/civicrm-core/pull/23115
* https://lab.civicrm.org/dev/core/-/issues/3161
* https://github.com/civicrm/civicrm-core/pull/23336
* https://lab.civicrm.org/dev/core/-/issues/3485
* https://lab.civicrm.org/dev/core/-/issues/3492
* https://lab.civicrm.org/dev/core/-/issues/3503
* https://github.com/civicrm/civicrm-core/pull/24191
* https://github.com/civicrm/civicrm-core/pull/26118
Round 2:
* https://github.com/civicrm/civicrm-core/pull/26497
* https://github.com/civicrm/civicrm-core/pull/26499
Late to the party:
* https://lab.civicrm.org/dev/core/-/issues/5075https://lab.civicrm.org/dev/user-interface/-/issues/46Modernise ship-with-civi theme2023-09-20T15:55:57ZeileenModernise ship-with-civi themeI've opened this issue to see if we can agree on a way to improve the theme that ships with core (or to ship an additional theme with core) without the discussion spinning out into the technical sprawl that always seems to paralyse us on...I've opened this issue to see if we can agree on a way to improve the theme that ships with core (or to ship an additional theme with core) without the discussion spinning out into the technical sprawl that always seems to paralyse us on this issue...
**Problem statement**
CiviCRM ships with a theme looks dated and is off-putting to new adopters- some specific criticisms
- Colours are …. Beige. Current fashion would seem to be more white space (also drab in bootstrap)
- Button styling seems dated
- Some people seem to prefer side tabs - not sure if this is consensus
Goal of issue/discussion
- Find some achievable improvements
- Don’t get bogged down on solving everything
Potential solutions
- Improve the Greenwich theme - possibly as a paralell theme - addressing the most egregious issues (e.g just swapping colours / using more white makes it look more modern)
- Add an existing theme to core (Shoreditch, Aah, Finsbury park, Christian’s theme). Note that if we do this
1) it will mean that the theme becomes part of core codebase & would be maintained as such, with a priority place on maintainability and ensuring not too much css is downloaded (which might not always be in line with the designer’s vision).
2) bringing an existing theme into core would require the mainintainer agreeing to their theme being forked into a core extension & the core extension being maintained according to core maintenance priorities & principles. This may not be something current theme maintainers want as some design elements are likely to be sacrificed in the pursuit of maintainability / compatibility.
2) Anything that ships in core must work on all CMS and have acceptable page load speed for anonymouse users.
- Build up a minimal theme - ie what is the min theming we need to do to make it ‘load’ & move the rest of the css to greenwich (this is assuming a minimal theme would be better….)
Note that this ticket https://lab.civicrm.org/dev/user-interface/-/issues/33 covers previously discussion. Those discussions focussed on making it easier for themers to theme CiviCRM whereas the focus on this is what can we do to make the CiviCRM that ships with core look better.https://lab.civicrm.org/dev/core/-/issues/3083getNonDeductibleAmountFromPriceSet should take into account the financial typ...2023-12-05T21:36:19ZherbdoolgetNonDeductibleAmountFromPriceSet should take into account the financial type of the price field optionOverview
----------------------------------------
`getNonDeductibleAmountFromPriceSet()` gets called in a couple places to calculate the non-deductible amount of contributions. While the calling method will take into account the financi...Overview
----------------------------------------
`getNonDeductibleAmountFromPriceSet()` gets called in a couple places to calculate the non-deductible amount of contributions. While the calling method will take into account the financial type of the contribution when assigning the non-deductible amount, `getNonDeductibleAmountFromPriceSet()` does *not* take into account the financial type of the price set option. I believe it should be.
Reproduction steps
----------------------------------------
1. Create price set and add two fields: one with financial type Donation (deductible) = `$10` and other Event Fee (non-deductible) = `$20`. Make the Event Fee have a random non-deductible amount of say `$2.50`.
2. Add a contribution in the backend to a contact and choose this new price set.
3. Look at the resulting contribution.
Current behaviour
----------------------------------------
The contribution has non-deductible = `$2.50` for a `$30` contribution.
Expected behaviour
----------------------------------------
I expect the non-deducible to be `$20`, the full price of the Event Fee. The contribution should be setting the non-deductible to be equal that of the merchandise price field, regardless of the actual non-deductible amount for that field. If a price field is non-deductible then it is 100% non-deductible based on the financial type.https://lab.civicrm.org/dev/backdrop/-/issues/5Cron for multisite does not work2024-02-02T18:04:20ZcatorghansCron for multisite does not workWhen doing http cron (with .htaccess changes, see https://github.com/backdrop/backdrop-issues/issues/3903 ) or cli cron, on a multsite install, it uses the civicrm.settings.php from the root and not from the sites/[site]/ dir.
It looks ...When doing http cron (with .htaccess changes, see https://github.com/backdrop/backdrop-issues/issues/3903 ) or cli cron, on a multsite install, it uses the civicrm.settings.php from the root and not from the sites/[site]/ dir.
It looks like it does not expect a civicrm.settings.php in CMSroot if you have a multiinstall.
But to my understanding that should be allowed (and even normal)
In my case I have
[cmsroot]/civicrm.settings.php for the default site and also
[cmsroot]/sites/[site]/civicrm.settings.php for the 2nd domain.
When I look at civicrm.config.php it can not work.
When it finds a civicrm.settings.php in cmsroot it will not look further.https://lab.civicrm.org/dev/core/-/issues/3070Placeholders in forms2023-12-06T15:27:43ZshaneonabikePlaceholders in formsOverview
----------------------------------------
_Please describe your improvement in detail._
Example use-case
----------------------------------------
1. View a Contribution form
1. Fields such as _First name_ do not have a placehold...Overview
----------------------------------------
_Please describe your improvement in detail._
Example use-case
----------------------------------------
1. View a Contribution form
1. Fields such as _First name_ do not have a placeholder and only a label
Current behaviour
----------------------------------------
I think that in general providing placeholders is a good practice for all fields for the following reasons:
+ More accessible
+ Simplifies design (hidden labels) reduces the size of forms
Proposed behaviour
----------------------------------------
I was thinking there could be two ways to make this happen to start.
+ Provide placeholders that match the labels (Phase 1)
+ Provide ability on all fields with text entry to have a customizable placeholder (Phase 2)
Comments
----------------------------------------
Would there be any problem with me writing a patch that would allow the creation of placeholders for text holders that presently uses the label and copies that into a placeholder field?https://lab.civicrm.org/dev/core/-/issues/3068PDF filename on Pay later contributions is receipt.pdf2023-11-23T07:47:01Zluke.stewartPDF filename on Pay later contributions is receipt.pdfOverview
----------------------------------------
Currently if attach PDF copies to receipts is enabled and a contact completes a "pay later" contribution an email is sent with a pdf named 'receipt.pdf'
As a quick fix it is trivial to...Overview
----------------------------------------
Currently if attach PDF copies to receipts is enabled and a contact completes a "pay later" contribution an email is sent with a pdf named 'receipt.pdf'
As a quick fix it is trivial to detect in CRM/Contribute/BAO/ContributionPage::sendMail whether the payment is pay later or not and use this to change the PDF name.
We also need to check other workflows that trigger pdfs receipts.
These look to be CRM/Contribute/BAO/ContributionSoft.php which should be a similar fix to above - and also consider workflows for resending "receipts" where this is hardcoded as well.
Long term this value wouldn't be hard coded in this file but exposed through the UI as either a global setting - or potentially having a per contribution page override.
Additionally as it is currently this is not a translateable string either.
Reproduction steps
----------------------------------------
1. Enable "Attach PDF copy to receipts" via Admin -> System Settings -> Misc
1. Create a contribution page with a pay later and payment processor (dummy processor is fine) and add a $1 payment.
1. Under the Receipt options -> "Email Receipt to Contributor?" set true.
Current behaviour
----------------------------------------
When submitting the contribution form with "Pay Later" the email has "Invoice" in the subject - but the attachment filename is "receipt.pdf"
Expected behaviour
----------------------------------------
When submitting the contribution form with "Pay Later" the email has "Invoice" in the subject - but the attachment filename is "receipt.pdf"
Environment information
----------------------------------------
* __CiviCRM:__ _Master ( and Current Stable 5.46.2.)https://lab.civicrm.org/dev/core/-/issues/3067Improving performance of retreiving display names for multivalued custom data2022-11-25T15:39:20ZMichael McAndrewImproving performance of retreiving display names for multivalued custom data.Have been taking a look at https://github.com/civicrm/civicrm-core/pull/20764/files#diff-36ab608da0b5718996afd18c10a8c12653c2a1675e81fd3c4dc3e40c2ebb25d2R1040.
My hunch is that for a query that returns 50,000 rows, the 'group_concat fi....Have been taking a look at https://github.com/civicrm/civicrm-core/pull/20764/files#diff-36ab608da0b5718996afd18c10a8c12653c2a1675e81fd3c4dc3e40c2ebb25d2R1040.
My hunch is that for a query that returns 50,000 rows, the 'group_concat find_in_set' subquery is executed 50,000 times even if pagination is turned on since the limit is applied after it does all the subqueries
If you turn that subquery into a 'superquery', i.e. you do the 'group_concat find_in_set' hydration after the limit, it imrpoves the performance a lot.
I did a proof of concept below. For a query with 500 results it went from 37 seconds to 1.3 seconds.
And for 34,000 it went from never completing to 1.0 seconds.
Not sure how easy it would be to acheive this within the constraints of searchkit @colemanw but it would be great to get your initial thoughts on this.
Proof of concept changes to the query below...
Changing this query:
```sql
SELECT `a`.`id`, (
SELECT GROUP_CONCAT(
`display_name`
ORDER BY FIND_IN_SET(`civicrm_contact`.`id`, REPLACE(`Case_of_abuse_details_1`.`perpetrator_143`, '�', ','))
SEPARATOR '�'
)
FROM `civicrm_contact`
WHERE FIND_IN_SET(`civicrm_contact`.`id`, REPLACE(`Case_of_abuse_details_1`.`perpetrator_143`, '�', ','))
) AS `Case_of_abuse_details.Perpetrator.display_name`
FROM civicrm_activity a
LEFT JOIN `civicrm_value_case_of_abuse_68` `Case_of_abuse_details_1` ON a.id = Case_of_abuse_details_1.entity_id
WHERE ((`a`.`activity_type_id` IS NULL OR (`a`.`activity_type_id` IN (1, 2, 3, 4, 5, 9, 12, 13, 14, 15, 16, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 40, 44, 45, 48, 49, 50, 51, 52, 53, 55, 57, 59, 61, 63, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74)))) AND (`a`.`activity_type_id` = "68") AND (`a`.`is_test` = "0") AND (`a`.`is_deleted` = "0")
GROUP BY `a`.`id`
LIMIT 50
OFFSET 0
```
into this query
```sql
SELECT id,(SELECT GROUP_CONCAT(
`display_name`
ORDER BY FIND_IN_SET(`civicrm_contact`.`id`, REPLACE(`perpetrator_143`, '�', ','))
SEPARATOR '�'
)
FROM `civicrm_contact`
WHERE FIND_IN_SET(`civicrm_contact`.`id`, REPLACE(`perpetrator_143`, '�', ','))
) AS `Case_of_abuse_details.Perpetrator.display_name` FROM (SELECT `a`.`id`, `Case_of_abuse_details_1`.`perpetrator_143`
FROM civicrm_activity a
LEFT JOIN `civicrm_value_case_of_abuse_68` `Case_of_abuse_details_1` ON a.id = Case_of_abuse_details_1.entity_id
WHERE ((`a`.`activity_type_id` IS NULL OR (`a`.`activity_type_id` IN (1, 2, 3, 4, 5, 9, 12, 13, 14, 15, 16, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 40, 44, 45, 48, 49, 50, 51, 52, 53, 55, 57, 59, 61, 63, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74)))) AND (`a`.`activity_type_id` = "68") AND (`a`.`is_test` = "0") AND (`a`.`is_deleted` = "0")
GROUP BY `a`.`id`
LIMIT 50
OFFSET 0) AS alias
```