Export activity target

[davej]

Hi,

A user wants to do something that seems entirely reasonable: export a set of activities, with the export including target contacts. In fact an activity export that doesn't include the target contacts wouldn't be much use. They also want to export custom fields on the target contacts.

I can't see a way to do this without custom code. Looking at the obvious things you'd try:

Find Activities: can't specify target contact as export field.
Advanced Search / as Activity: ditto.
Custom Searches / Find Latest Activities: ditto.

- Presumably due to activity -> target being 1:n, however:

Activity Report: this allows exporting target contact but not contact custom fields.

So the best bet seems to be customising Activity Report to allow contact custom fields.

Worth flagging this up though, I think, as a major deficiency in the activity searches (yes, I know, "if this is important to your use case, please consider..." ;-) ) - or have I missed something?

While looking at this, I noticed what looks like a bug in Custom Searches / Find Latest Activities (CRM_Contact_Form_Search_Custom_ActivitySearch):

Code: [Select]

    function from( ) {
        return "
        civicrm_contact contact_a
            JOIN civicrm_activity activity
                 ON contact_a.id = activity.source_contact_id
            JOIN civicrm_option_value ov1
                 ON activity.activity_type_id = ov1.value AND ov1.option_group_id = 2
            JOIN civicrm_option_value ov2
                 ON activity.status_id = ov2.value AND ov2.option_group_id = {$this->_groupId}
            JOIN civicrm_contact contact_b
                 ON activity.source_contact_id = contact_b.id
            LEFT JOIN civicrm_case_activity cca
                 ON activity.id = cca.activity_id
            LEFT JOIN civicrm_activity_assignment assignment
                 ON activity.id = assignment.activity_id
            LEFT JOIN civicrm_contact contact_c
                 ON assignment.assignee_contact_id = contact_c.id ";
    }

Note:
- no civicrm_activity_target;
- both contact_a and contact_b are formed by joining on activity.source_contact_id, so they are in fact the same thing.

The Contact Name field in Custom Searches / Find Latest Activities used to search by target contact but now searches by source contact. The join for contact_a used to be on civicrm_activity_target.target_contact_id (e.g. in 2.1.6). This search seems outmoded now anyway, as it lists results as contacts not activities and doesn't allow selecting fields for export.

Dave J

[Donald Lobo]

we've tweaked / modified the activity query in the last release to avoid making it consume a lot of mysql resources. The custom search seems like a bug, any chance you can investigate and commit a patch.

In general for lots of activities, our current query will not scale (the LEFT JOINs basically blow up the query). We've optimized it for a few cases in BAO_Activity but its not generalized to be used in the general search

Yeah, going down the activity report part would be a good start. Reports can break their final query into a bunch of smaller queries and hence can be made more scalable a lot easier

lobo

[davej]

Hi Lobo,

Target contact appears in activity search results but is not available for export. Did the tweaking affect what could be exported?

The user is adamant that they used to be able to export activities with targets + target custom data but they can't remember how they were doing it!

Thanks,

Dave J

[jimurl]

Hi Dave,

We are looking at almost exactly the same thing - outputting activity targets, one per row, including some custom fields.  I was looking for a way to do this as well, and was surprised that there wasn't an existing Search for this yet.

I see exactly what you mean by "- both contact_a and contact_b are formed by joining on activity.source_contact_id, so they are in fact the same thing." I'm not sure what contact_b was supposed to be in the original version, but got rid of it and replaced it with what we are looking for: target contact.

(I mentioned a related question here: http://forum.civicrm.org/index.php/topic,20809.msg87039.html )

To do this, I copied CRM/Contact/Form/Search/Custom/ActivitySearch.php to  a new file:  CompleteActivitySearch.php

Below is a 'diff' showing the changes I made to get the desired effect: Once target contact per row, displayed according to the parameters of the activity search.
 

Code: [Select]

diff CompleteActivitySearch.php  ActivitySearch.php

39c39
< class CRM_Contact_Form_Search_Custom_CompleteActivitySearch
---
> class CRM_Contact_Form_Search_Custom_ActivitySearch
55c55
<                                  ts('Target Contact')   => 'target_sort_name',
---
>                                  ts('Scheduled By')     => 'source_contact',
94c94
<         $this->setTitle('Activities, Listed by Target Contact');
---
>         $this->setTitle('Find Latest Activities');
145c145
<         return 'CRM/Contact/Form/Search/Custom/CompleteActivitySearch.tpl';
---
>         return 'CRM/Contact/Form/Search/Custom/ActivitySearch.tpl';
162,163d161
<                 contact_b.id                as target_id,
<                 contact_b.sort_name         as target_sort_name,
165a164
>                 contact_b.sort_name         as source_contact,

238,241c236,237
<             LEFT JOIN civicrm_activity_target activity_target
<                  ON activity.id = activity_target.activity_id
<             JOIN civicrm_contact contact_b
<                  ON contact_b.id = activity_target.target_contact_id
---
>             JOIN civicrm_contact contact_b
>                  ON activity.source_contact_id = contact_b.id
262a259
>                            contact_b.sort_name LIKE '%{$contactname}%' OR

There are also a few changes that need to be made to the template file, so I copied this one : templates/CRM/Contact/Form/Search/Custom/ActivitySearch.tpl over to CompleteActivitySearch.php Here is the diff. :

Code: [Select]

diff CompleteActivitySearch.tpl ActivitySearch.tpl
88c88
<                 {elseif ($header.sort eq 'sort_name') or ($header.sort eq 'activity_status') or ($header.sort eq 'activity_type') or ($header.sort eq 'activity_subject') or ($header.sort eq 'target_sort_name') or ($header.SORT eq 'activity_date') or ($header.name eq null) }
---
>                 {elseif ($header.sort eq 'sort_name') or ($header.sort eq 'activity_status') or ($header.sort eq 'activity_type') or ($header.sort eq 'activity_subject') or ($header.sort eq 'source_contact') or ($header.SORT eq 'activity_date') or ($header.name eq null) }

109c109
<                     {if ($header.sort eq 'sort_name') or ($header.sort eq 'activity_status') or ($header.sort eq 'activity_type') or ($header.sort eq 'activity_subject') or ($header.sort eq 'target_sort_name') or ($header.SORT eq 'activity_date') or ($header.name eq null) }
---
>                     {if ($header.sort eq 'sort_name') or ($header.sort eq 'activity_status') or ($header.sort eq 'activity_type') or ($header.sort eq 'activity_subject') or ($header.sort eq 'source_contact') or ($header.SORT eq 'activity_date') or ($header.name eq null) }

I also had to enable this new Custom Search on this page: /civicrm/admin/options/custom_search?group=custom_search&reset=1

That made the proper 'sort column' headers show up on the search results; although I think I mucked something up there, they don't always show up correctly.

Anyway- It basically works, returning rows of target contacts for even one Activity (e.g. Select all the 'Recipients of Bulk Mailings that were sent this month ...' ).

Still TODO- the checkboxes still let you update the contact who created the Activity, not the target. Any ideas on how to do this would be welcome.  That seems to be controlled here in the template file, CompleteActivitySearch.tpl:

around line 106:

Code: [Select]

{assign var=cbName value=$row.checkbox}
                <td>{$form.$cbName.html}</td>
The checkboxes have an id variable that corresponds to which contact is updated- I haven't figured out where that is set, yet. Any pointers would be appreciated!

[Dave Greenberg]

I think the contact id's associated with the search result checkboxes are set in CRM/Contact/Selector/Custom.php (starting around line 322). Looks like its using the value from $dao->contact_id HTH

Would be good to add this custom search to our extensions (or potentially replace the existing one if this encompasses that functionality with needed enhancements) - so ping us when you have it working / solid.

[jimurl]

Hi Dave,

Thanks for that! I found it, but I decided that messing with that file was probably not the best way to handle re-writing the output for the 'task' checkbox as target_contact_id . Instead I left that file alone and did some renaming within my search file to make contact_id the Target's ID, instead of the Activity Creator's ID .

I think this search is wo0rking / solid, but would love some feedback.

Here is what the ActivityTargets.php does now:

Activity Search fields are the same: Activity Type, Status (scheduled, completed, etc.), Activity Dates from and To, and Activity Subject.
Other text search fields are for 'Target Name' and 'Assignee Name.'

The Contact Type field now refers to the contact type of the Target, not the Activity creator.
I wanted to include a contact subtype dropdown seletor; but found that this:

Code: [Select]

$form->add('select', 'contact_type', ts('Find...'), CRM_Core_SelectValues::contactType());
couldn't be rewritten as this:         

Code: [Select]

$form->add('select', 'contact_type', ts('Find...'), CRM_Core_SelectValues::contactSubType());
Any ideas if there is a different CRM_Core_somethingsomething:: subcontacttypes() that I could use to display a subcontacttype dropdown?

Everyone feel free to use this search, which can be placed at  /CRM/Contact/Form/Search/Custom/ActivityTargets.php  and /templates/CRM/Contact/Form/Search/Custom/ActivityTargets.tpl (also perhaps in the extensions folder, but that didn't work for me.) Then visit the "Mange Custom Search" page and add it as a new custom search there: example.com/civicrm/admin/options/custom_search?reset=1&group=custom_search

The main change in this search (I'm calling it Activity Targets) over the original Activity Search is the one-contact-per-row style of output;

I'm open to suggestions for improving these searches, or commentary on cleaning up the style.

[Dave Greenberg]

I found the code / pattern for building contact type + subtype select by looking classes that I know have one. Two different approaches:
1. custom data set form: CRM/Custom/Form/Group.php starting around line 227 builds a hierarchical select which includes contact types (sel1) and subtypes (sel2). You could grab this and chop out all the non-contact stuff.

2. Import contacts step 1: CRM/Import/Form/DataSource.php + DataSource.tpl use jquery to create radio button + dropdown widget (starting around 163 with jscript in the tpl). Probably a simpler approach :-)

[davej]

Re exporting activities from searches, see http://issues.civicrm.org/jira/browse/CRM-8671. I commented there:

This is certainly a step in the right direction. I do think though that users would reasonably expect an activity export to include both the source and target contacts. From the user's perspective: I do a search, I see both source and target contacts in the search results, great. I then choose export and am presented with a choice of fields from Individual, Organization, Household or Activity. The user does not know whether the fields relate to source or target contact and will discover by trial and error that they relate only to target (which is better than only source!).

How about an extra drop-down similar to phone type etc, for selecting Source or Target?

Dave J

[Dave Greenberg]

Hi Dave - Discussed this with Lobo just now. We've come to agree w/ Eileen McN that the underlying data model needs to be fixed to make these activity related outputs easier and more scalable. The concept is to combine the activity_target, activity_assignee tables with activity.source_contact_id into a single join table (activity_contact) which would have activity_role (source, assignee, target) + activity_id and contact_id.

Turns out that Eileen McN prototyped this during the UK sprint - but I think she is quite slammed now. We think making this change for 4.1 is somewhere between 10 - 30 hrs of work (with managing the possibility of timeouts during upgrades of larger DB's being a bit complicated). Would you / Circle be able to take this on with some help from our side? Seems like it would simplify quite a few problems and really help w/ scaling.