Scratchpad: Questions & Notes

• Dave: How to search using custom fields extending event?

Budget

Timeline

• Development work will finish on June 29th - expected result is to have all the functionality in place, with bugs and problems to be removed during next two-three weeks.
• Data migration will start on July 2nd.

Assumptions

1. The scope of the project is to provide following elements:
   1. Support in installation of CiviCRM v.1.7 with it's features
   2. Case management functionality as described below
   3. Activity management functionality as described below
   4. Grant management functionality as described below
   5. Various minor customisations as listed below
2. Project does not include:
   1. system and network administration, CiviCRM deployment maintenance and upgrades
   2. end user documentation preparation
   3. data migration
   4. UI customisations, themeing and graphic design other than explicitly listed in this specification
   5. other items not explicitly listed in this specification

General concepts

Contact database basic segmentation

We'll use following categories (tags) mechanism for base contact segmentation.

1. Active Human Rights Defenders
2. Board and Leadership Council (BLC)
3. Front Line Donors
4. Front Line Staff
5. General
6. Government Contact
7. Inactive Human Rights Defenders
8. Intergovernmental Organization Contact
9. Media Contact
10. Non Governmental Organization
11. Unconfirmed Human Rights Defenders

Mailing lists

FL database uses mailing lists for online newsletter mailings. We'll use CiviMail and custom field/smart group combination to implement this.

Case management (Candidate for core)

Case is an entity grouping single contact's activities. It's described by a set of attributes (below).

Case object attributes

• Field: Case Type (enum values)
  • Civil & Political;
  • Economic, Social and Cultural;
  • Gender Issues
• Field: Case Sub-Type (enum values)
  • Labour Rights;
  • Land Rights;
  • LGTB Rights;
  • State Corruption;
  • Working with IDPs
• Field: Violation (enum values)
  • Arrested / Detained;
  • Disappeared;
  • Harassment;
  • Killed;
  • Legal Action;
  • Threats;
  • Tortured / Ill-treatment;
  • Violence Against Women
• Field: Case start date (YYYY-MM-DD, defaulted to today)
• Field: Case end date (YYYY-MM-DD, defaulted to none)
• Field: Subject (input - 128 characters)
• Field: Notes (textarea)
• Field: Status
  • Resolved;
  • Ongoing;

Special attributes:

• Field: created_date - handled through civicrm_log, first entry
• Field: created_user - handled through civicrm_log, first entry
• Field: modified_date - handled through civicrm_log, last entry
• Field: modified_user - handled through civicrm_log, last entry
• Foreign key: contact_id, points at the contact given case belongs to, required

Actions

Following actions are required:

• action adding new case (happens only in context of selected contact record)
• action removing the case (does not physically remove associated contact or activities)
• action attaching activity record to the case (belongs to activity management functionality!)
• action disattaching activity record from the case (belongs to activity management functionality!)
• action search by all fields

Remarks:

• Attaching activity record to the case happens through activity adding screen

User interface

New case can be added only from the context of selected contact record.

Following UI elements need to be implemented:

• Screen: "List cases" The list of cases associated with given contact
  • access through dojo tab in contact record screen
  • presents the link to new case adding screen
  • presents the list of cases attached to given contact record
    • presents attributes: case status, case type, subject, start date, from, to, regarding
    • sortable by all attributes
    • cases's subject links to "Show case" screen
    • "edit" link next to each case
    • "delete" link next to each case (see actions!), warning before removing
    • throws fatal error when contact_id not provided

• Screen: "Show case" Presents details of given case
  • can come back to "List cases" screen
  • presents the list of activities contained in given case
    • for each activity, presents attributes: direction, type, subject, date added
    • sortable by all attributes
    • "edit" link next to each activity
    • "detach" link next to each activity
  • presents the link to new activity adding screen (redirects to activities module, with case_id parameter)

• Screen: Add new case/Edit case Case adding/editing screen
  • presents fields for adding/editing all the case's attributes
  • can come back to "Show case" screen
  • when editing, all the fields contain proper values

• Search screen addition (dojo): Case search section available in advanced search
  • search by:
    • case subject (input)
    • case start date ranges (date selector for start range, date selector for end range)
    • case type (select)
    • case subtype (select)
    • violation (select)
    • status (select)

• Screen: Confirm deleting case Case removal confirmation screen
  • presents request for confirmation of deleting activity record

Data structure

Option values are handled through civicrm_option_group and civicrm_option_value. Please note that ids assigned to respective rows below are just examples.

Custom options groups

 
Core case module tables

Core inclusion

Generalised version of this component that can be included in trunk, will have following differences:

• case_type_id ,case_sub_type_id, violation_type_id and relevant UI elements will be removed
• civicrm_option_group and civicrm_option_value will not be included
• can be (but don't have to) an optional component - I would leave it as part of core functionality

Activity management (Experimental/Custom)

Due to specific FL needs (intensive use of activity records), our built-in Activities mechanism might not be efficient enough to handle the load that will be put on activity records usage. However, we'll use our standard Activities, with some tweaks and customisations.

Activity object

Each activity has three contact associations:

1. from relationship: points at the contact, which took action to perform given activity
2. to relationship: points at the contact, who was the recipient of given activity
3. regarding relationship: point at the contact, who was the reason to perform given activity

Real life use-cases:

Human rights defender (John Doe) under Frontline's care has been arrested. Frontline decides to send out a call for action to 100 journalists in Europe and make them write about this case. 100 activity records are created, with following association values: from: Frontline (or user name) to: Journalist Name regarding: John Doe In single Journalist's activity history record, there is a note saying s/he was a recipient of the call regarding John Doe. In John Doe's record, there is a single activity history record saying there was an action call sent out regarding his arrest. Through this record, we can alse see the list of contacts this call has been sent to as well.

Above use case shows basic mechanism behind

Human rights defender (John Doe) under Frontline's care met with Frontline's worker. Frontline registers this activity in the database, creating a record, with following association values: from: Frontline worker's name to: John Doe regarding: John Doe

Activity object attributes

• Field: Type:
  • Source (incoming);
  • Incoming correspondence;
  • Incoming Press Release;
  • Complaint (incoming);
  • Urgent Action (incoming)
  • Outgoing correspondence;
  • Outgoing Press Release;
  • FL Action (outgoing);
  • FL Urgent Action (outgoing);
  • Lobbying (outgoing)
• Field: Communication-Medium:
  • N/A;
  • E-mail;
  • Fax;
  • Letter;
  • Meeting;
  • SMS Message;
  • Telephone;
  • Video Conference
• Field: Violation-Type
  • N/A;
  • Arrested / Detained;
  • Disappeared;
  • Harassment;
  • Killed;
  • Legal Action;
  • Threats;
  • Tortured / Ill-treatment;
  • Violence Against Women
• Field: Creation date (timestamp)
• Field: Subject: (string(256))
• Field: Content (blob)

Special attributes:

• Field: created_date - handled through civicrm_log, first entry
• Field: created_user - handled through civicrm_log, first entry
• Field: modified_date - handled through civicrm_log, last entry
• Field: modified_user - handled through civicrm_log, last entry

Aggregate attributes:

• Field: to_contact_id - from joining table civicrm_activity_contact
• Field: from_contact_id - from joining table civicrm_activity_contact
• Field: regarding_contact_id - from joining table civicrm_activity_contact

Actions

• action adding new activity (happens only in context of selected contact record)
• action removing the activity (does not physically remove associated contacts!)
• action attaching activity record to the case (only if case's contact_id matches activitie's regarding_contact_id!)
• action disattaching activity record from the case
• action search by all fields

User interface

New activity record can be added only from the context of selected contact record, whose id automatically becomes regarding_contact_id.

Following UI elements need to be implemented:

• Screen: "List activity records" The list of activity records associated with given contact (doesn't matter through which association)
  • access through dojo tab in contact record screen
  • presents the link to new activity record adding screen
  • presents the list of activity records attached to given contact
    • presents attributes: direction, type, subject, start date
    • sortable by all attributes
    • cases's subject links to "Activity record details" screen
    • "edit" link next to each case
    • "delete" link next to each activity record, warning before removing
    • throws fatal error when contact_id not provided

• Screen: "Activity record details" Details of single activity record
  • can come back to "List activity records" screen
  • presents all activity record attributes

• Screen: "Add/Edit activity record" Activity record adding/editing screen
  • can come back to "List activity records" screen
  • when editing, all the fields contain proper values
  • this screen has conditional selector fields, see "Conditional selector fields" section

• Screen: "Activity record removal confirmation"
  • Presents request for confirmation of activity record removal

• Search screen addition (dojo): Activity record search section available in advanced search
  • search by:
    • activity type (select)
    • activity medium (select)
    • activity violation type (select)
    • creation date ranges (date selector for start range, date selector for end range)
    • subject (input)
    • content (input)

• Screen: Confirm deleting case Case removal confirmation screen
  • presents request for confirmation of deleting activity record

Implementation concept

We'll use existing civicrm_activities table, and extend it with following columns:

These fields will be used to store: Type, Communication-Medium and Violation-Type, respectively. Select values for these fields will be stored in civicrm_option_group and civicrm_option_value tables (see cases example).

Final shape of civicrm_activity table is the following:

Above columns shouldn't be implemented with custom fields, since activities will be the most heavy used element of FL database and searching these fields needs to be really efficient (correct me if I'm wrong here).

source_contact_id and target_entity_id/target_entity_table fields handling will stay unchanged and will serve, respectively, from and regarding activity associations.

In order to implement to association, we will need to implement joining table (table name to be changed):

When given contact's activity list is going to be displayed, we will have to check all three association directions: two traditional ones and yet another one stored in civicrm_activity_assignment table.

target_entity_table/target_entity_id will be used to store "TO" direction contact record association, activity_entity_table/activity_entity_id will be used to store information about which record in which table (civicrm_activity, civicrm_meeting, civicrm_phonecall) contains specific

Activity status modification needs to be disabled and therefore triggering "activity" into "activity history" transition will not be available (which is what we want).

We need to be able to register past activities, therefore we cannot have a limitation of setting past dates in activity adding/editing.

Core inclusion - experimental/custom

We can check how multidirectional "task assignment" works for activities.

Grant management (Candidate for core)

Grant management allows tracking grant donations made by Frontline passed to respective people or organisations (contact records). In CiviCRM core, CiviGrant will be available as an optional module.

Grant object attributes

• Field: Grant status
  • Pending (default)
  • Granted
  • Rejected
• Field: Grant application received date (YYYY-MM-DD, defaulted to today)
• Field: Grant type (select)
  • Emergency;
  • Family Support;
  • General Protection;
  • Impunity
• Field: Rationale of Project (textarea)
• Field: Total Amount of Project (input validated for amount)
• Field: Amount Applied For (input validated for amount)
• Field: Amount Granted (textarea)
• Field: Decision Date (YYYY-MM-DD, defaulted to today)
• Field: Report due date (YYYY-MM-DD, defaulted to today)
• Field: Money sent date (YYYY-MM-DD, defaulted to today)
• Field: Grant Report Received (boolean field)
• Field: Notes (textarea)

Actions

• action adding new grant
• action editing existing grant
• action removing existing grant
• action search by all fields

User interface

This component will be working in similar way as CiviEvent or CiviContribute, having it's own dashboard screen.

• Screen: "CiviGrants Dashboard" CiviGrant's main screen, accessible through main menu
  • presents general description of CiviGrant's functionality
  • presents the list of pending grant applications
  • presents the list of upcoming grant report deadlines
  • each position in either of above lists have
    • "View" link allowing to go to Grant details screen
    • "Edit" link allowing to go to Add/Edit grant screen

• Screen: "Grants in contact record"
  • access through dojo tab in contact record screen
  • presents the link to new grant adding screen
  • presents the list of grants attached to given contact record
  • presents attributes: grant status, grant application received date, grant type, amount applied for
  • sortable by all attributes
  • each grant's subject links to "Show grant details" screen
  • "edit" link next to each grant
  • "delete" link next to each grant, warning before removing
  • throws fatal error when contact_id not provided

• Screen: "Grant details" presents details of single grant
  •  
• Screen: "Add/edit grant" allows to add or edit a grant

Various customisations

Regions for country list (Candidate for core)

FL requires country list segmentation to be able to assign countries to specific regions. From our side, this will require:

• adding new table:
• adding new column to civicrm_country table: region_id
• populating civicrm_region and civicrm_country with proper region<->country assignments
• providing UI for displaying the region in contact's location block
• providing the way to search for contacts/activities/cases in specific region
• we'll not implement UI for editing regions list at this stage (potential changes will be done directly in the database)

Timezones for locations (Candidate for core)

FL workers perform phone calls with numerous people from different countries quite often, therefore they require functionality which allows them to quickly figure out local time at phonecall destination. From our side, implementing this functionality will require:

• adding new table:
• populating civicrm_timezone with necessary data
• providing UI in Location block editing that allows choosing default timezone
• providing UI in Location block displaying that presents default timezone
• we'll not implement UI for editing timezones at this stage (potential changes will be done directly in the database)

EML2Activity converter

A lot of Frontline researchers' work is sending/receiving emails and registering them in database as activities. We were going back and forth on various ways to integrate CiviCRM with the database and finally came up with following concept:

• we will have a script, which will take either EML file (saved email) or source email message forwarded from MTA's mailbox and will convert it to CiviCRM activity (FL custom style)

more to come

Functionality available out of the box

Event management

Vanilla CiviEvent is going to satisfy Frontline's needs.

Contribution management

Vanilla CiviContribute is going to satisfy Frontline's needs.