County specific developments

Introduction

The first version of this tool was built in Salt Lake County, Utah. In each county there may be some unique integrations that we run. The intent will be to note these here, and to make them open in such a way that individuals deploying elsewhere can get ideas for integrations or data connections that might be useful or otherwise relevant to build in their case as well.

Salt Lake County

This section is dedicated to processes that were built to work with Salt Lake's system.

Extracting latest list of clients

The purpose of this query is to create a csv file for Dave (IS staff) to pull from the cjs-cfa FTP that was set up to let us pass information back and forth from ClientComm to Ctracks (the case management system currently in use in Salt Lake County Criminal Justice Services).

SQL query to retrieve client data shown below.

SELECT  clients.clid, 
        clients.cm, 
        clients.first,
        coalesce(clients.middle, '') AS middle,
        clients.last,
        clients.dob,
        coalesce(clients.otn, '') AS otn, 
        coalesce(clients.so, '') AS so, 
        cms.first AS cm_first, 
        cms.last AS cm_last, 
        cms.email AS cm_email 
FROM clients
LEFT JOIN cms ON (cms.cmid = clients.cm)
WHERE clients.active IS TRUE
AND cms.department IN (2, 3, 5, 6, 7, 9, 11)
ORDER BY clients.clid;

This information is then uploaded to the FTP as file closedClients.csv where Dave (the IS staff member) then pulls it down, runs a SQL query he has designed, and places the resulting file in both CSV (cjs-output.csv) and pipe-delimited (cjs-output.unl) formats in the FTP.

Department id's 2, 3, 5, 6, 7, 9, 11 represent the following department (in same order): Probation, Pretrial, Drug Court Therapy, Drug Court Case Managers, Assessment and Pre-sentence, ISP, and Office of Support Services (OSS).