LIMS Integration for ZEPRS

In March 2008 the team began work on integrating output from the the lab system at one of the clinics, called LIMS, into the ZEPRS lab results. The goal was to automate reporting of lab results, rather than having to key in the results, which can result in transcription errors.

Fortunately, some of the heavy pushing was done, since the CIDRZ team already had an application (Iguana Integration Engine for HL7 processing) in-place that processes the HL7 messages generated by LIMS and inserts them into a SQL Server instance. ZEPRS has a separate process that queries the SQL Server and inserts any new records into its MySQL database.

SQL Server database documentation:

PatientInformation table

NAME: ID TYPE: varchar - GUID generated by SQL Server
NAME: NatID TYPE: varchar - ZEPRS id
NAME: FirstName TYPE: varchar
NAME: Surname TYPE: varchar
NAME: Clinic TYPE: varchar
NAME: Study TYPE: varchar
NAME: DOB TYPE: datetime
NAME: Sex TYPE: varchar
NAME: Collection TYPE: datetime - date/time that the specimen is collected. That is provided to the lab by the nursing staff at the clinics.
NAME: Reception TYPE: datetime - date/time that the lab “receives” the sample, or rather places the order in the LIMS.
NAME: ObservationRequest TYPE: varchar

PatientObservations table

NAME: ID TYPE: varchar - GUID generated by SQL Server - FK to pi.ID
NAME: NatID TYPE: varchar - ZEPRS id
NAME: ObservationIdentifier TYPE: varchar
NAME: ObservationText TYPE: varchar
NAME: AbnormalFlags TYPE: varchar
NAME: ObservationValue TYPE: varchar
NAME: Units TYPE: varchar
NAME: ReferencesRange TYPE: varchar
NAME: ObservationResultStatus TYPE: varchar
NAME: ObservationDate TYPE: datetime - this is the timestanp given by SQL server to indicate when the record was imported.

Each PatientInformation (pi) record documents a lab request. It has one of more PatientObservations (po). po.id is a foreign key to pi.id.

Since ZEPRS is an open source project, it's worth noting that similar HL7 integration could be achieved using an open source HL7 processor such as Mirth.

LIMS Import Process:

• Fetch id, observationDate timestamp of LastRecordImported LIMS record from lims.xml file
• Query database for list of Observations, query by observationDate timestamp
• Loop through this list. For each record, check if lab import is disabled for the patient and do a few more checks.
  • See if there are any outstanding records for this patient.
  • Loop through the current lab tests
    • Check if date of lab request is within 4 days of the LIMS observation
    • Fill out the correct observations - CD4A, HGB
    • If there are matches, update the lab request (labtest table in zeprs database) w/ the observation data, and update the encounter's timestamp.
    • Otherwise, create a new lab request and fill w/ the observation data.
• After finishing the loop of all lims records, set the id, observationDate timestamp of LastRecordImported LIMS record in lims.xml file

For debugging purposes:

Create table statement for labtest and encounter:

CREATE TABLE `labtest` (
  `id` bigint(20) NOT NULL default '0',
  `dateLabRequest` date default NULL,
  `labType` int(11) default NULL,
  `dateLabResults` date default NULL,
  `results` int(11) default NULL,
  `treatment` text,
  `comments` text,
  `resultsNumeric` float default NULL,
  `reason_not_treating` int(11) default NULL,
  `cd4count` int(11) default NULL,
  `extended_test_id` bigint(20) default NULL,
  `lims_import_id` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FKFC42579FD1B` (`id`),
  CONSTRAINT `FKFC42579FD1B` FOREIGN KEY (`id`) REFERENCES `encounter` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `encounter` (
  `id` bigint(20) NOT NULL auto_increment,
  `patient_id` bigint(20) default NULL,
  `form_id` bigint(20) NOT NULL default '0',
  `last_modified` datetime default NULL,
  `created` datetime default NULL,
  `last_modified_by` varchar(255) default NULL,
  `created_by` varchar(255) default NULL,
  `site_id` bigint(20) default NULL,
  `flow_id` bigint(20) NOT NULL default '0',
  `date_visit` date NOT NULL default '0000-00-00',
  `pregnancy_id` bigint(20) default NULL,
  `referral_id` bigint(20) default NULL,
  `created_site_id` bigint(20) default NULL,
  `import_encounter_id` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK5AD86933D25AB9CC` (`flow_id`),
  KEY `FK5AD86933D79EFE76` (`form_id`),
  KEY `FK5AD869337D2B7913` (`site_id`),
  KEY `FK5AD8693351A3A90E` (`created_by`),
  KEY `FK5AD869333E8F4D64` (`last_modified_by`),
  KEY `FK5AD869332370976D` (`pregnancy_id`),
  KEY `FK5AD869338523EC95` (`patient_id`),
  CONSTRAINT `encounter_ibfk_1` FOREIGN KEY (`pregnancy_id`) REFERENCES `pregnancy` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `encounter_ibfk_2` FOREIGN KEY (`last_modified_by`) REFERENCES `user_group_membership` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `encounter_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `user_group_membership` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `encounter_ibfk_4` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `encounter_ibfk_5` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To find the timestamp, make a join between encounter.id and LabTest.id, selecting encounter.last_modified.

In Labtest, notice lims_import_id. It sets the value informationId, which is the id from the imported LIMS record. This may be useful in tracking down a particular record.
The comments field usually stores the name that was passed from the LIMS system. It's one of our checks to help see if the correct patient was imported.
The dateLabResults field stores the observationDate passed from the LIMS system.