Allow user to change dates in a list of activities

[Robert Townrow]

Hi, I'm fairly new to CiviCRM and I've got a complicated problem to solve for a custom report using CiviReport.
I think CiviReport might not be the solution to the problem, which is why I'm posting in here.

What I want is

• a list of all activities meeting certain criteria (this could be achieved using CiviReport or Advanced Search or some other way e.g. ctools?)
• the ability to change dates directly from this list, without having to change webpage

There are other factors involved (about 20 custom date fields added to activities for instance) which I'll have to deal with later, but let's try modifying for a 'vanilla' CiviCRM system to start with.

Currently I can see 3 possibilities for solving the problem:

• I could modify the exisiting custom CiviReport we have. Background: This custom report produces a nice list of activities, and solves all end-user needs except for the slow way of changing dates. Currently the user must click on the link for the activity, go into it, modify the relevant date, save and wait for the report to load back. Possible solution: I can add JavaScript so that when the user clicks a date, a new popup webpage is loaded with the activity ID and date in the query string. Potentially a webform or similar could be used to allow the user to modify the date and then save it back into CiviCRM before closing the popup then refreshing the report. The problem I need to solve is how to create the webform and how to save the date into CiviCRM?
  
• I could modify an Advanced Search (with a custom profile and "Display Results As"="Activity") so that dates can be modified from there?
• There may be a CiviCRM module like the editablefields Drupal module which might allow me to create a view of the data with editing capabilities? I've looked at editablefields but not sure how to proceed. I'm using CiviCRM 3.4/Drupal 6, so I wonder whether an upgrade to 4.2 might give us the features we need?

I've attached an example of one of the Activity Reports showing some test data (column names are changed). I would like the date fields to either be directly editable, or editable using a popup dialog as described in option 1 above. If my question isn't clear, please ask what information you need. We are running CiviCRM 3.4 and I've installed various modules on my test system to try out possibilities e.g. ctools, webforms, editablefields, ajax etc. Please let me know any suggestions which could help our users modify dates more efficiently. If anybody has asked similar questions, please link me to them.

Many thanks,

Robert

[Robert Townrow]

I've been looking into possible options, and thought of a better way of explaining what I'm doing. I'm trying to replicate the ease-of-use of the original Excel spreadsheet users were using. I found some evidence CiviCRM has already been used for this kind of work - the ability to edit a list in place. Below I'm giving a list of 6 options, so one help would be please guide me as to which approach would be most fruitful:

(1) In particular, there is a discussion here: http://forum.civicrm.org/index.php/topic,24049.msg101219.html#msg101219 which suggests that I can get what I want simply by adding the crm-editable tag. I'm not sure how to apply this to my template - and not sure this will work until I've upgraded to CiviCRM 4.2? Is there somewhere I can learn how to do this? I've also found this documentation here (wiki.civicrm.org/confluence/display/CRMDOC42/Structure+convention+for+automagic+edit+in+place) but I'm still unsure how to apply this to my template - e.g. where do I get the {entity} and {id} fields in the code below?

Code: [Select]

{foreach from=$rows item=row key=rowid}
           {eval var=$sectionHeaderTemplate}
            <tr  class="{cycle values="odd-row,even-row"} {$row.class} crm-report" id="crm-report_{$rowid}">
                {foreach from=$columnHeaders item=header key=field}
                    {assign var=fieldLink value=$field|cat:"_link"}
                    {assign var=fieldHover value=$field|cat:"_hover"}
                    <td class="crm-report-{$field}{if $header.type eq 1024 OR $header.type eq 1} report-contents-right{elseif $row.$field eq 'Subtotal'} report-label{/if}">
                        {if $row.$fieldLink}
                            <a title="{$row.$fieldHover}" href="{$row.$fieldLink}">
                        {/if}
                       
                        {if $row.$field eq 'Subtotal'}
                            {$row.$field}
                        {elseif $header.type & 4 OR $header.type & 256}   
                            {if $header.group_by eq 'MONTH' or $header.group_by eq 'QUARTER'}
                                {$row.$field|crmDate:$config->dateformatPartial}
                            {elseif $header.group_by eq 'YEAR'}
                                {$row.$field|crmDate:$config->dateformatYear}
                            {else}
                                {if $header.type & 4}
                                   {$row.$field|truncate:10:''|crmDate}
                                {else}
                                   {$row.$field|crmDate}
                                {/if}
                            {/if}
                        {elseif $header.type eq 1024}
                            <span class="nowrap">{$row.$field|crmMoney}</span>
                        {else}
                            {$row.$field}
                        {/if}
                       
                        {if $row.$fieldLink}</a>{/if}                       
                    </td>
                {/foreach}
            </tr>
        {/foreach}
(2) I've also found this: "Multiple-Record Custom Data Sets in Profiles" http://wiki.civicrm.org/confluence/display/CRM/Multiple-record+Custom+Data+Sets+in+Profiles but I don't know if this could be applied to a list of Activities.

(3) Looking further back in time, I've found this: http://forum.civicrm.org/index.php/topic,19501.msg81172.html#msg81172, which seems to describe what I want, particularly what Greenberg says:

2. If you need a grid with ability to add multiple events at once you'll need to build a separate module which renders your fields as a grid (perhaps using Drupal's web form builder) and then use CiviCRM api functions to populate the events in the database from your grid. This will require more advanced developer skills. The Extending CiviCRM chapters above should get you started. If you wind up developing a solution for this - please consider sharing your approach and code with the community.

. However, I'm not sure how I'd get started with this?

(4) Here's another guy asking for the same feature, but obviously it will take more than a few hours of PHP coding by an amateur (like me!) http://forum.civicrm.org/index.php/topic,18334.msg76133.html#msg76133

(5) Finally, for completeness, here's something for batch entry, although it seems limited to billing rather than extensible to modifying a batch/list of activities: http://civicrm.org/blogs/dave-greenberg/batch-entry-contributions-and-membership-payments-draft-specification
(6) And here's an approach one custom used to enable AJAX updating from a list - potentially this approach could be used for updating dates, although it might be more work than necessary if one of the other approaches works? http://civicrm.org/node/669

Thanks for your time/suggestions.

[petednz]

I have read this a couple of times - and not sure I am seeing the Batch Update via Profile in there.
Have your tried this? Is there some catch that is stopping you using
- Find Activities
- Batch update by Profile: choose a Profile with the Activity fields you require (which you will have to make first)

[Coleman Watts]

The civicrm api actually has a magical in-place edit feature that should do exactly what you're looking for... you just need to override the template of the page in question to add a little special markup, and the magic takes over from there
http://wiki.civicrm.org/confluence/display/CRMDOC42/Structure+convention+for+automagic+edit+in+place

[Robert Townrow]

Thanks for your suggestions. I will investigate and post again to let you know how I get on.

[xavier]

Hi,

By default, the magical inline mode deals with "simple" input types, there isn't yet a "calendar" mode. Shouldn't be too complicated to add (eileen did it for a select) but isn't done for calendar. That seems to be easy to add custom input type in jeditable, ping me if you want to go that road and get blocked.

X+

[Robert Townrow]

Thanks Coleman Watts. I have already looked at the webpage you mention, but I'm not sure how to proceed. Could you link me to a worked example?
Also, I've noticed that trying to use the Civi API from jQuery on a Civi Report doesn't seem to work. For example, I added the following to my custom version of table.tpl:

Code: [Select]

{crmAPI var="ActivityS" entity="Activity" action="get" q="civicrm/ajax/rest" version="3" }
{foreach from=$ActivityS.values item=Activity}
<li>{$Activity.details}</li>
{/foreach}
The above Smarty code worked fine, and displayed a list of Activities with details, as expected.

However, the API generator gives invalid jQuery code, which I'm not sure how to correct (or use).

Code: [Select]

$().crmAPI ('Activity','get',{'q' :'civicrm/ajax/rest', 'version' :'3'}}
  ,{ success:function (data){   
      $.each(data, function(key, value) {// do something });
    }
});
Notice in the first line, there is a round bracket after crmAPI then curly opening bracket { , then followed by 2 curly closing brackets at the end }}.

Once this is corrected, I believe I have to add it to the template between {literal} tags, like this:

Code: [Select]

<script>
{literal}
$().crmAPI ('Activity','get',{'q' :'civicrm/ajax/rest', 'version' :'3'}}
  ,{ success:function (data){   
      $.each(data, function(key, value) {// do something });
    }
});
{/literal}
</script>
However, I'm not sure what to do in the { // do something } block. Some examples of how to manipulate the key and value stuff would be useful. Of course, ultimately I want to be using the update function, not just the get function.

Thanks,

[xavier]

Thanks Coleman Watts. I have already looked at the webpage you mention, but I'm not sure how to proceed. Could you link me to a worked example?

You can look for instance at how it's done on the admin list of tags templates/CRM/Admin/Page/Tag.tpl

Code: [Select]

<script>
{literal}
$().crmAPI ('Activity','get',{'q' :'civicrm/ajax/rest', 'version' :'3'}}
  ,{ success:function (data){   
      $.each(data, function(key, value) {// do something });
    }
});
{/literal}
</script>However, I'm not sure what to do in the { // do something } block. Some examples of how to manipulate the key and value stuff would be useful. Of course, ultimately I want to be using the update function, not just the get function.

Well, you can do whatever you need there, for instance adding a html list in the page to display the activities.

you can as well use the update action (well, actually create+id)

There is an example on how we use it in templates/CRM/Tag/Form/Tag.tpl (to add /remove a tag on the contact)

X+

[Robert Townrow]

Thanks Xavier,

I had a look in civicrm/templates/CRM/Tag/Form/Tag.tpl and found this:

Code: [Select]

<script type="text/javascript">

options = {ldelim} ajaxURL:"{crmURL p='civicrm/ajax/rest' h=0}", closetxt:'<div class="ui-icon ui-icon-close" style="float:left"></div>' {rdelim}
entityID={$entityID};
entityTable='{$entityTable}';
{literal}
function hideStatus( ) {
    cj( '#restmsg' ).hide( );
}
cj(document).ready(function(){initTagTree()});

function initTagTree() {
    //unobstructive elements are there to provide the function to those not having javascript, no need for the others
    cj(".unobstructive").hide();

    //load js tree.
    cj("#tagtree").jstree({"plugins" : ["themes", "html_data"]});

    cj("#tagtree ul input:checked").each (function(){
        cj(this).parents("li").children(".jstree-icon").addClass('highlighted');
    });

    cj("#tagtree input").change(function(){
        tagid = this.id.replace("check_", "");

        //get current tags from Summary and convert to array
        var tagLabels = cj.trim( cj("#tags").text( ) );
        if ( tagLabels ) {
            var tagsArray = tagLabels.split(',');
        } else{
            var tagsArray = new Array();
        }

        //get current tag label
        var currentTagLabel = cj("#tagLabel_" + tagid ).text( );
        if (this.checked) {
            //civiREST ('entity_tag','create',{entity_table:entityTable,entity_id:entityID,tag_id:tagid},image);
            cj().crmAPI ('entity_tag','create',{entity_table:entityTable,entity_id:entityID,tag_id:tagid},options);
            // add check to tab label array
            tagsArray.push( currentTagLabel );
        } else {
            cj().crmAPI ('entity_tag','delete',{entity_table:entityTable,entity_id:entityID,tag_id:tagid},options);
            // build array of tag labels
            tagsArray = cj.map(tagsArray, function (a) {
                 if ( cj.trim( a ) != currentTagLabel ) {
                     return cj.trim( a );
                 }
             });
        }

        //showing count of tags in summary tab
        var existingTagsInTagset = cj('.token-input-delete-token-facebook').length;
        var tagCount = cj("#tagtree input:checkbox:checked").length + existingTagsInTagset; 
        cj( '.ui-tabs-nav #tab_tag a' ).html( 'Tags <em>' + tagCount + '</em>');

        //update summary tab
        tagLabels = tagsArray.join(', ');
        cj("#tags").html( tagLabels );
        ( tagLabels ) ? cj("#tagLink,#tags").show( ) : cj("#tagLink,#tags").hide( );
    });
   
    {/literal}
    {if $permission neq 'edit'}
    {literal}
        cj("#tagtree input").attr('disabled', true);
    {/literal}
    {/if}
    {literal}
   
};
{/literal}
</script>
I think perhaps I have to use different syntax, like the following - but this doesn't work either:

Code: [Select]

cj().crmAPI ('Activity','get',{'q':'civicrm/ajax/rest', 'version':'3'}
  ,{ success:function (data){   
      $.each(data, function(key, value) {alert('Working!');});
    }
});
Maybe I need to upgrade first?

[xavier]

didn't see your version. Indeed, update they have been a lot of fixes

X+

[Robert Townrow]

Sorry this has been inactive for a long time. I'm posting to mention the attempt at a solution I have implemented.
Unfortunately we are still stuck on CiviCRM 3.4.8 so the advice below might need to be changed for more recent versions.
I discovered that the {crmURL syntax did not seem to work properly for CiviCRM 3.4.8 for the update command, so in the end I used an AJAX call of this form to perform updates:
'/index.php?q=civicrm/ajax/rest&json=1&debug=1&version=3&entity=Activity&action=update&id=16710&activity_date_time=20121025120400'

By using an AJAX call of this kind, I was able to use jQuery and JavaScript string manipulation to update any given column with any input.

So to do my changes I took the following steps:
1) Changed the HTML structure for the table.tpl Smarty template, so that input fields can be used in place of simple text for the items which require modification:

{if (!strpos($field, "custom_69") && !strpos($field, "custom_70") && !strpos($field, "custom_73")
 && (strpos($field, 'activity_date_time') || strpos($field,'custom_')))}
    <input type="text" value="{$row.$field}" title="{$header.title} - Type new date and press Enter to save" maxlength="11"></input>
{else}
    {$row.$field}
{/if}
As you can see, I changed the simple {$row.$field} text to an input element with value="{$row.$field}". I have complicated this a little due to the following factors:
a) Only specific fields were desired to be editable, so a Smarty condition statement examines the $field name to determine if it the right type. In my case, I wanted to edit only fields of type 'activity_date_time' and all fields with name starting 'custom_' except for "custom_69", "custom_70" or "custom_73". These editable fields are all date fields, so I restricted maxlength to 11
b) I have added title text which includes the column name and instructions for editing the item (press Enter). Adding the column name makes it easier for a user to determine which date they are editing for a big table

Next I added JavaScript to bind a function on keypresses for the input elements. For normal keypresses, we simply mark the item as "dirty" by removing the "status-saved" class, but if carriage return (ASCII 13) is pressed, then it must attempt to save the new value of the input box.

<script>
{literal}

    $(document).ready(function () {
        $("td input").bind("keypress", function (e) {
            // need to avoid the colour staying the "saved" colour after the user edits this input box:
            $(this).removeClass('status-saved');

            if (e.which === 13) {  // only fire for Enter events
                var col, activityDate, customPosition, customNumber, urlEdit, dateClass, fullUrl, queryStringPosition, queryString, newDate, ajaxCommand, that;
                // First, get the parent "td" of the input box ($(this).parent()).                           
                // Column index may change, so we actually want the column class name:
                col = $(this).parent().attr("class");
                activityDate = "activity_date_time";
                if (col.indexOf(activityDate) !== -1) {
                    // activity date time is the "Target Date" and needs to be editable:
                    col = activityDate;
                    dateClass = true;  // specify we *do* want the edit behaviour
                } else {
                    // look for part of the class name being "custom_66" or "custom_68", etc.
                    customPosition = col.indexOf("custom_");
                    if (customPosition > 0) {
                        customNumber = parseInt(col.slice(customPosition + 7), 10);  // extract the relevant part and convert to base-10 integer
                        if (customNumber === 66 || customNumber === 68 || customNumber === 71 || customNumber === 72 ||
                                (customNumber >= 75 && customNumber <= 107)) {
                            col = "custom_" + customNumber;
                            dateClass = true;
                        }
                    }
                }   // end else       
                if (dateClass === true) {
                    recordRef = $(this).parent().children(".crm-report-civicrm_activity_activity_subject");
          urlEdit = $(recordRef).children("a");
          var fullUrl = $(urlEdit).attr("href");
             var queryStringPosition = fullUrl.indexOf("&cid=");
             if (queryStringPosition > 3) {
                 var queryString = fullUrl.slice(queryStringPosition);
                        queryString = fullUrl.slice(queryStringPosition + ;
                        // remove any unwanted spaces:
                        queryString = queryString.replace(/\s+/g, '');

                        // get what was entered into the text box and process it as a date:
                        newDate = $(this).val();
                        // user has inputted either an empty string (i.e. cleared the field),
                        // or else entered something, which would have to be verified
                        if (newDate !== "") {
                            // user has entered something, so this must be validated and formatted using
                            // the getDateForCivi function:
                            newDate = getDateForCivi(newDate);
                            // an empty string from getDateForCivi indicates an error, so abort:
                            if (newDate === "") {
                                alert("'" + $(this).val() + "' is not a valid date. \n\n" +
                                      "The date must be in this format: \n\n" +
                                      "The day of the month, between 1 and 31, followed by 1 space, \n" +
                                      "followed by the 3-character abbreviation for the month e.g. Sep, followed by 1 space, \n" +
                                      "then optionally followed by 2 or 4 digits for the year e.g. 11 or 2011  . ");
                                return false;
                            }
                            // we can assume now that newDate is set to a valid date (in YYYYMMDD format)
                        }
                        // Just to clarify: newDate is now either (1) an empty string, so we will set the date to empty, or
                        //                                        (2) a valid date, in YYYYMMDD format

                        // now starts with the id. We want the id and the column (custom field) name to determine what data to obtain.

                        // something like this should work, but it doesn't. Fortunately the update command works, even though the get command does not:
                        // /index.php?q=civicrm/ajax/rest&json=1&debug=1&version=3&entity=Activity&action=get&id=16710&return=activity_date_time,custom_68
                        // $.getJSON('/index.php?q=civicrm/ajax/rest&json=1&debug=1&version=3&entity=Activity&action=get&id=' + queryString, function(data) {
                        //      queryValues = data["values"];
                        //      queryValues = queryValues[queryString];
                        //      var dateString = queryValues[col];
                        //      alert(dateString);
                        // });

                        // update does work - below is an example
                        // $.getJSON('/index.php?q=civicrm/ajax/rest&json=1&debug=1&version=3&entity=Activity&action=update&id=16710&activity_date_time=20121025120400', function(data) {
                        //  alert(data["is_error"]);
                        //});                       

                        ajaxCommand = '/index.php?q=civicrm/ajax/rest&json=1&version=3&entity=Activity&action=update&id=' +
                                queryString + '&' + col + '=' + newDate;

                        document.body.style.cursor = 'wait';  // show hourglass during AJAX call and page refresh                                       

                        that = this;

                        $.getJSON(ajaxCommand, function (data) {
                            if (data.is_error !== 0) {
                                alert('The command failed with error: ' + data.is_error);
                                document.body.style.cursor = 'auto';  // return mouse to default
                            } else {
                                // Now instead of refreshing, standardize the display and change the colour of the row:
                                $(that).val(getDateForDisplay(newDate));
                                $(that).addClass('status-saved');
                                document.body.style.cursor = 'auto';  // return mouse to default
                            }
                        });
                    }       // end if (queryStringPosition > 3)
                } else {
                    return true;    // allow default action (e.g. bringing up new webpage) still to occur
                }           // end if (dateClass == true)             
            }               // end if (e.which == 13)
        });                 // end .bind
    });

{/literal}
</script>

To explain the save function, when the user presses enter, the JavaScript does the following:
a) Looks at the parent <td> table item for the input box to obtain the class name
b) Examines it to see if it is the right type of class. This unfortunately is a duplication of the logic used in the Smarty section. However, the class name is needed, and the variable dateClass is set to true if this is a valid class.
c) If the input element is in a <td> with a valid class, then the JavaScript looks for the edit activity link in the current row. This is found in the <td> of class ".crm-report-civicrm_activity_activity_subject" in the current <tr>, and we obtain the href attribute from the <a> link in this cell. From this we extract the id of the activity (the bit after cid=). The actual process in my JavaScript is more complicated than this for various reasons, but this method is easier to explain!
d) Next we validate user input (which is in $(this).val()).
e) Now we have the 3 pieces of information needed: the class (field) name, the activity ID, and the new value to set. Note unfortunately we're not doing any concurrency checks here!
f) With this information we create an AJAX update command: ajaxCommand = '/index.php?q=civicrm/ajax/rest&json=1&version=3&entity=Activity&action=update&id=' + queryString + '&' + col + '=' + newDate;
i.e. the important part is we end up with an AJAX command with the following attributes:
json=1
version=3  // version 3 of the Civi API
entity=Activity
action=update
id=(the cid of the activity to be updated)
(field name)=(new field value) // e.g. activity_date_time=20121025 or custom_75=20130706

g) We now call the AJAX command using jQuery:
that = this;

$.getJSON(ajaxCommand, function (data) {
    if (data.is_error !== 0) {
        alert('The command failed with error: ' + data.is_error);       
    } else {
        // Now instead of refreshing, standardize the display and change the colour of the row:
        $(that).val(getDateForDisplay(newDate));
        $(that).addClass('status-saved');
    }
};

So if the .getJSON command works, it updates the display to a standard format (if necessary) and sets the class to 'status-saved' to highlight that the save has been acknowledged. I will add the CSS changes in another post

[Robert Townrow]

In the CSS I added the following:

/* when an item is saved, need to highlight it blue: */
#crm-container input.status-saved
{
    color: rgb(0, 0, 255);
}

#crm-container .report-layout input
{
   width: 75px;
   border: 0;  /* remove the border */
   background-color:transparent;
}

This had to be added in the main section of CiviCRM, since it doesn't seem to apply when added to the custom module, so it is in civicrm/css/extras.css

[Coleman Watts]

Yikes.
For anyone stumbling across this, yes, there is an easier way! Especially as of version 4.3 the ajax api is very easy to use and gives a friendly status message by default so you don't necessarily even need a 'success' handler.
CRM.api('activity', 'create', {id:123, 'subject': 'Updated'});