Displaying ICE messages

The ice_messaging drupal module is used to access the MS SQL database containing the received ICE messages.

Some of the drupal 7 documentation still refers to the old syntax for database queries, even while demonstrating the drupal database abstraction layer. The best and most consistent documentation for building database queries is

ice_messaging uses the datatables module to provide fancy presentation. The datatables modules requires manual installation of the DataTables jQuery plugin, and this is very fussy about directory names. Just be careful.

The datatables module requires the views module, which in turn requires the Chaos tools module.

Within drupal, switching to the alternative database is done using:


  // execute queries here

  db_set_active(); // without the paramater means set back to the default for the site

This assumes the YourDatabaseKey connection has been defined in /sites/default/settings.php

The sqlsrv project provides a SQL server driver, but it only works on Windows servers. There's a fork of sqlsrv which uses dblib to work on linux:

To run that, we need to 'sudo apt-get install php5-sybase git' then 'git clone dblib_driver_for_sql_server'

Apache needs restarting in order for the php5-sybase / dblib library to be recognised.

Then 'sudo drush dl datatables'. Then do the manual installation of DataTables jQuery plugin, rename the intermediate directory to 'dataTables' and then 'sudo drush en datatables'. That will enable the necessary additional prerequisites.

Then enable the dblib module and set up a link from /includes/database/dblib to the module's dblib directory.

Finally, enable the ice_messaging module. After that, everything should work.

  • Note regarding css in datatables - there's a discrepency in the style sheet interactions between the datatables code and the drupal theme code. I have resolved this for our deployment by hard-coding font color and background color into the dataTables/media/css/demo_table.css as follows:
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * DataTables sorting

.sorting_asc {
        background: url('../images/sort_asc.png') no-repeat center right;
        color: rgb(128,128,128);

.sorting_desc {
        background: url('../images/sort_desc.png') no-repeat center right;
        color: rgb(128,128,128);

.sorting {
        background: url('../images/sort_both.png') no-repeat center right rgb(255,255,255);
        color: rgb(128,128,128);

.sorting_disabled {
        background: rgb(255,255,255);
        color: rgb(128,128,128);

.sorting_asc_disabled {
        background: url('../images/sort_asc_disabled.png') no-repeat center right;
        color: rgb(128,128,128);

.sorting_desc_disabled {
        background: url('../images/sort_desc_disabled.png') no-repeat center right;
        color: rgb(128,128,128);

th:active {
        outline: none;

Processing recruitment

As of version 1.1.0, the ice_messaging module also provides one-click loading of patients and GENVASC Study enrolment into the CiviCRM database, using the CiviCRM API v3.

Duplicate Enrollments

As of version 1.1.1 the Ice Messaging module:

  • Checks to see if a patient already exists within CiviCRM.
  • If they do the new GENVASC enrollment will be associated with the existing patient.
  • If the patient already has an existing GENVASC enrollment, the new enrollment will have the same GPT number as the existing enrollment and will have a status of excluded.

Address uploading

As of version 1.2 the Ice Messaging module:

  • Uploads the address details from the PMI into CiviCRM.
  • If there is an existing address in CiviCRM, the user gets to choose between the existing address and the PMI address.
  • The post code of the chosen address will be used to populate the post code custom field on the GENVASC study recruitment record.

Work Instructions

  • If the patient does not exist in the PMI or their address is different from the one on the GENVASC consent form, the details from the consent form should be used to update Patient Centre. The PMI details on the ICE message can be updated by clicking the refresh button.
  • The PMI look up only shops the patients main address. It is possible that the address on the consent form is in Patient Centre as a postal address - for example, if the patient lives abroad but is staying long term in the UK. In these situations:
    1. Process the ICE message using the PMI address.
    2. Add the address on the consent form to the patient's record in Civi and mark this address as the main address.
    3. Update the post code custom field in their GENVASC enrollment to be the post code from the consent form.
Last modified 8 years ago Last modified on 05/19/15 21:19:48

Attachments (1)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.