Interoperability with the Sync Process

Submitted by ckelley on Mon, 01/18/2010 - 12:26

External non-Zcore data systems may also process data once it is uploaded to the external WebDAV server.

Database Setup

First you will need to have a database that already is loaded with the Zcore application schema. See the Generate Schema tip for the Derby command used to generate the SQL that can be modified to create the schema in your database.Also read the section on Dynasite - Zcore Form Rendering to understand Zcore database principles and its schema.

Processing Data Files

As mentioned in Uploading the archive to the Server, uploaded files are stored in a directory based on the site's name, instance id, and the year, month, and week number (1-5) when the file was uploaded - i.e. SOW/1/archive/2010/Jan/3. The import process outlined in the Locating, Downloading, and Extracting the Files section of "Processing the SyncEvent Zip File" may be a useful guide to designing the external system's import process.

Data files in Detail

Each data file is a zip archive continaing 2-3 directories:

  • log - Contains the syncEvent_uuid log file, which has data about each file in the archive. This can be used to process the files. Background on the SyncEvent file is covered in Creating the Sync Archive.
  • admin - admin-related files, such as lists used in dropdowns. If no admin-related files were created, this directory will not exist.
  • people - majority of the records entered.

The people directory contains subdirectories that are named by the patient's uuid. Each file is named using the following format:

  • Encounters: "enc_" + encounter uuid
  • Events: "event+" + event uuid
  • Patient record: "pat_" + patient uuid

Example Import Process

After extracting the files from the archive, deserialize the SyncEvent_uuid.json file. Process the following SyncLog elements in the syncEvent object:

  • patients
  • events
  • encounters
  • admin records
  • edits
  • deletions

As you loop through each item, check if each object's UUID is already in the system. If it isn't, import it. Here is a sample SyncLog that describes an Intake record:

{
    "className" : "Intake",
    "siteId" : 16,
    "siteInstanceId" : 1,
    "formId" : 232,
    "encryptable" : false,
    "checksum" : 2825205359,
    "uuid" : "6e6b124d-44af-475b-b4d1-5993a6509db7",
    "parentUuid" : "d523cb73-5128-4d47-b50b-f94b784d0547",
    "objectUuid" : "79fb549f-4a4b-40a3-9a43-1f98706f0cc6",
    "syncEventUuid" : "a21ea4d0-0e6b-4364-9133-54e49de57b17",
    "exportTimestamp" : 1263821141689
  }

The corresponding record would be in people/enc_79fb549f-4a4b-40a3-9a43-1f98706f0cc6.json. Since this SyncLog record has a patientUuid, we know that it is a patient record, rather than an admin record; therefore, it must be in the "people" directory. Here is the record:

{
  "date_victim_at_tcc" : "2010-01-18",
  "time_victim_at_tcc" : "15:24:04",
  "offender_known_to_victim" : 1,
  "victim_relationship_to_offender" : 3306,
  "statement_taken" : 1,
  "reported_saps" : 1,
  "date_reported_saps" : "2010-01-18",
  "time_reported_saps" : "15:24:04",
  "saps_transport_to_tcc" : 1,
  "survivor_wait_for_transport" : 3353,
  "officer_transporting_survivor_uuid" : "a606530c-c21a-482f-b76a-662225c85883",
  "direct_walk" : 0,
  "referral_not_police" : 0,
  "not_report_to_saps" : 0,
  "id" : 5037,
  "lastModified" : 1263821095767,
  "siteId" : 16,
  "auditInfo" : {
  },
  "lastModifiedBy" : "zepadmin",
  "created" : 1263821044157,
  "formId" : 232,
  "flowId" : 132,
  "encryptable" : false,
  "uuid" : "79fb549f-4a4b-40a3-9a43-1f98706f0cc6",
  "createdBy" : "zepadmin",
  "eventId" : 1004,
  "patientId" : 1028,
  "clientUuid" : "d523cb73-5128-4d47-b50b-f94b784d0547",
  "dateVisit" : "2010-01-18",
  "siteName" : "Soweto",
  "createdByName" : "User, Admin",
  "lastModifiedByName" : "User, Admin",
  "eventUuid" : "369783f0-7f36-4abd-b299-04e681c85738"
}

In the above code, each field is named using the corresponding table's column name. Here is the create table statement for reference:

CREATE TABLE "APP"."INTAKE" ("ID" BIGINT NOT NULL DEFAULT 0,
 "DATE_VICTIM_AT_TCC" DATE,
 "TIME_VICTIM_AT_TCC" TIME,
 "OFFENDER_KNOWN_TO_VICTIM" SMALLINT,
 "VICTIM_RELATIONSHIP_TO_OFFENDER" INTEGER,
 "STATEMENT_TAKEN" SMALLINT,
 "REPORTED_SAPS" SMALLINT,
 "DATE_REPORTED_SAPS" DATE,
 "TIME_REPORTED_SAPS" TIME,
 "STATION" INTEGER,
 "STATION_OTHER" VARCHAR(255),
 "CAS" VARCHAR(255),
 "DIRECT_WALK" SMALLINT,
 "REFERRAL_NOT_POLICE" SMALLINT,
 "INVESTIGATING_OFFIER_NAME" VARCHAR(255),
 "INVESTIGATING_OFFIER_CELL" VARCHAR(255),
 "INVESTIGATING_OFFIER_TEL" VARCHAR(255),
 "INVESTIGATING_OFFIER_EMAIL" VARCHAR(255),
 "INVESTIGATING_OFFIER_ID" INTEGER,
 "SAPS_TRANSPORT_TO_TCC" SMALLINT,
 "SURVIVOR_WAIT_FOR_TRANSPORT" INTEGER,
 "NOT_REPORT_TO_SAPS" INTEGER,
 "OFFICER_TRANSPORTING_SURVIVOR" INTEGER,
 "ADDITIONALL_INVEST_OFFICERS" VARCHAR(255),
 "DATE_OF_ARREST" DATE,
 "OFFICER_TRANSPORTING_SURVIVOR_UUID" CHAR(36));

Note that the record does not have data for all of the fields in the table - it only provides the fields that have data. Also, the class with which this table is associated - Intake.class - extends EncounterData.class, which stores the metadata for the record. When you import an encounter, you must create records in both the encounter table and the table that corresponds to the form's class - in this case, the intake table. Sample SQL:

INSERT INTO encounter (patient_id, form_id, flow_id, date_visit, event_id, last_modified, created, 
last_modified_by, created_by, site_id, created_site_id,import_encounter_id, uuid, event_uuid, 
client_uuid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
INSERT INTO intake(id, date_victim_at_tcc, time_victim_at_tcc, offender_known_to_victim, 
victim_relationship_to_offender, statement_taken, reported_saps, date_reported_saps, time_reported_saps, 
saps_transport_to_tcc, survivor_wait_for_transport, officer_transporting_survivor_uuid, direct_walk, 
referral_not_police, not_report_to_saps) VALUES (IDENTITY_VAL_LOCAL(),?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Although much of the import is straightforward, one of the fields in this record is unusual:

"officer_transporting_survivor_uuid" : "a606530c-c21a-482f-b76a-662225c85883",

This field is populated by an Inline form, which enables the user to create the element for the dropdown - the Officer dropdown in this case. If you need to provide a report based on this data, you would need to make the following joins between the following fields in order to get the name of the police officer:

  • officer_transporting_survivor_uuid field in the Intake table and the uuid field in the encounter table
  • id field in encounter and id field in Contact.

Contact.name will provide the police officer's name.