* The ICDE utility program is not automatically provided as part of Crimestar RMS but can be license separately for those who require such an extract utility. The most common use for this utility is to provide periodic in-custody related data to support the sharing of inmate information with external jail telephone or commissary systems.
The In-Custody Data Extract (ICDE) Utility is a small stand-alone program that has been designed by Crimestar Corporation to open the Crimestar RMS database, select Jail/Arrest/Booking records for subjects shown as “In-Custody” (i.e. Not Released), assemble certain specific data fields into an output file using either a CSV or XML format and place that output file in a pre-defined configurable directory/path specification or FTP that file to a pre-defined FTP host server.
The program makes use of a simple text based configuration file named “icde.ini” to defined certain information and parameters needed by the ICDE program for proper operation. This configuration file allows the utility program to be configured with a variety of options detailed below. The ICDE program can be installed on any machine that has network access to the Crimestar RMS database. It in not necessary for any other Crimestar software to be installed on the same machine.
The resulting output .CSV
file will contain one or more records where each data field is quoted
with double quotes and data fields are separated from each other with
commas. This
is the standard recognized specification for a .CSV file. Example:
“0123456789”,”SMITH”,”JAMES”,”WILLIAM”,”19601201”,"M",”JID12345”,”OKITBBEHA”
If an XML file format is selected the resulting output .XML file will contain one or more records where the entire data block is wrapped in <ICDE> tags, data records are wrapped in <INMATE> tags and each data field is wrapped in an xml tag specific to the data field represented. This is a standard format for XML. Example:
<?xml version="1.0" encoding="WINDOWS-1252" standalone="yes"?>
<ICDE>
<INMATE>
<SSN>123456789</SSN>
<LAST_NAME>RAMIREZ</LAST_NAME>
<FIRST_NAME>JOSEPH</FIRST_NAME>
<MIDDLE_NAME>BRIAN</MIDDLE_NAME>
<DOB>19901219</DOB>
<SEX>M</SEX>
<BOOKING_NO>05-000666</BOOKING_NO>
<BOOKING_DATE>20050208</BOOKING_DATE>
<INCIDENT_NO>05-000666</INCIDENT_NO>
<JAIL_ID>123456789</JAIL_ID>
<MASTER_INDEX>66634.0000</MASTER_INDEX>
<LOCAL_ID>12345</LOCAL_NO>
<STATE_ID>54321</STATE_ID>
<POD>P1</POD>
<BLOCK>02</BLOCK>
<CELL>03</CELL>
<FACILITY_ID>CRIMESTARTEST</FACILITY_ID>
</INMATE>
</ICDE>
The output files created are, by default, named using both a prefix name (default: “icde”) followed by a date and time reference in _YYYYMMDDHHMMSS format which represents the date and time that the data extract was performed. Example: icde_20131004132133.csv The output files can be created on demand or at regular time intervals using the "Autorun" configuration option where the interval time is specified in minutes. By using this file naming strategy, each subsequent file that is created is saved without overwriting any previously created files. Each file created will contain a complete set of records for subjects who are In-Custody at the time that the output file is created. The ICDE utility can be configured to create output files with a static name when required. When output files use a static name the icde utility program will first try to delete any existing file, then create a new file with the same name.
The Crimestar RMS Arrest/Booking Module does not have a specific Yes/No data field to indicate if a subject is In-Custody or not. The implication is that any subject who is booked, is in-custody until the systems expressly indicates that he/she has been released. This is indicated in the Arrest & Booking Module of the Records Management System (RMS) by a date-time data field labeled “Released Date:” located on the “Release” page of the Booking Module. When the Released Date data field is empty (or NULL) or contains a future date, the subject is presumed to have not yet been released, thus he/she would still be, by default, in-custody. Only those arrest/booking records that are presumed as “In-Custody” by this definition are extracted from the Crimestar RMS database.
.
The following is a list of the specific Arrest & Booking data fields extracted and converted by this utility program and represented in the resulting output file. Because the .CSV file format is position specific the data fields represented in the resulting output file are listed in the same order in which they are listed in the table below:
OUTPUT DATA ELEMENTS |
|
Social Security # |
Maximum 9 Digits. Leading zeros as needed and no hyphens or dashes. * This field is not always mandatory, but can be configured as a mandatory field at the customer site within the Crimestar RMS system configuration. |
Last Name |
Maximum 20 Alphanumeric Characters. * This is always a mandatory field within the Crimestar RMS system. |
First Name |
Maximum 14 Alphanumeric Characters. * This is NOT a mandatory field within the Crimestar RMS system. |
Middle Name |
Maximum 14 Alphanumeric Characters. * This is NOT a mandatory field within the Crimestar RMS system. |
Date of Birth |
Maximum of 8 Digits. The date is represented in a YYYYMMDD format * This is a mandatory field within the Crimestar RMS system |
|
Additional Data Elements The data elements listed above will, by default, always appear in the resulting ICDE output file. (They can be expressly turned off if desired). The following data elements shown below are also available for output but, by default, do not automatically appear in the output.
|
Sex / Gender |
Maximum 1 Alphabetic Character. This field represent the sex / gender of the subject. * This is a mandatory field within the Crimestar RMS system |
Booking Number |
Maximum 12 Alphanumeric Characters. This field is an identification number that represents the specific booking transaction and does not necessarily represent an identification # for any given person. This field is typically unique to each specific booking, but uniqueness is not guaranteed. * This is a mandatory field within the Crimestar RMS system |
Booking Date |
Maximum 8 Digits. Data is represented in a YYYYMMDD format. * This field is not always mandatory, but can be configured as a mandatory field at the customer site within the Crimestar RMS system configuration. |
Incident Number |
Maximum 12 Alphanumeric Characters. This field represent the identification # for an incident or crime report which is linked or associated with this respective Booking #. * This is a mandatory field within the Crimestar RMS system |
Inmate Jail ID |
Maximum 10 Alphanumeric Characters. This field is intended to serve as a unique permanent ID value assigned to a person. Data in this field is entered by the end user as needed during the booking process and uniqueness cannot be guaranteed. * This field is not always mandatory, but can be configured as a mandatory field at the customer site within the Crimestar RMS system configuration. |
Master Index # |
Converted
numeric decimal value. This is a Crimestar internal system
programmatically generated number assigned to a person within
the database of the Crimestar system. To the degree that
the positive identification of a subject can be confirmed and
to the degree that a permanent unique consistent subject identification
number is possible this number is a consistent unique ID for the
subject. Internally Crimestar RMS uses this value as a primary
key for our Master Name Index table. Master Index #'s are
represented as a decimal value with 4 digits of precision (Decimal
24,4). Example: 1110987654321.1234 |
Local ID |
Maximum 15 Alphanumeric Characters. This field represents the Local ID value assigned to a person within the agency. * This field is not always mandatory, but can be configured as a mandatory field at the customer site within the Crimestar RMS system configuration. |
State ID |
Maximum 15 Alphanumeric Characters. This field represents the State ID value assigned to a person. * This is NOT a mandatory or optionally mandatory field within the Crimestar RMS system. |
POD |
Maximum 6 Alphanumeric Characters. This field represents the cell pod ID associated with the current custody housing assignment. * This field is not always mandatory, but can be configured as a mandatory field at the customer site within the Crimestar RMS system configuration. |
Block |
Maximum 6 Alphanumeric Characters. This field represents the cell block ID associated with the current custody housing assignment. * This field is not always mandatory, but can be configured as a mandatory field at the customer site within the Crimestar RMS system configuration. |
Cell |
Maximum 6 Alphanumeric Characters. This field represents the cell # ID associated with the current custody housing assignment. * This field is not always mandatory, but can be configured as a mandatory field at the customer site within the Crimestar RMS system configuration. |
Facility ID |
Maximum 20 Alphanumeric Characters. This is a static text string configured in the icde.ini configuration file. |
“icde.ini” Configuration File: Before the icde.exe program file can perform any actions it needs to know how and where to access the Crimestar RMS database as well as how and where to copy or FTP the resulting output file. It accomplished this by reading the entries in the icde.ini configuration file. The icde.ini configuration file should always be located within the same windows directory or folder as the icde.exe program file. The following table is a list of the configuration file [sections] and entries that are recognized within the icde.ini configuration file as well as an explanation for the purpose and use of each respective entry.
CONFIGURATION
FILE ENTRIES / PARAMETERS
|
|
[PATH] | INI File Section Name |
DATAFILES= | Used with the only with the Professional Edition of Crimestar RMS to define the absolute path to the RMS professional edition database “dbf” files. |
[CONFIGURATION] | INI File Section Name |
DATABASEMODE= | This
entry is used to determine if the data access to the RMS database
is a Professional Edition “DBF” type database or an Enterprise
Edition “SQL” Server type database. Acceptable values are:
DATABASEMODE=DBF DATABASEMODE=SQL
|
SQL_NAME_IP= | This
entry is used with the Enterprise Edition of Crimestar RMS to
define the IP Address or network machine name where SQL server
is located. Examples:
SQL_NAME_IP=\\ServerMachine\InstanceName SQL_NAME_IP=192.168.0.200 SQL_NAME_IP=192.168.0.200,1766
When a specific port other than the default is needed in order to access SQL Server the port specification can be defined by adding a comma and a port # to the end of the SQL_NAME_IP entry.
Note: Crimestar Corporation does not control, setup or otherwise maintain the computer networks used by our customers. As such there are no "default" IP addresses or machine names to be used for this configuration setting. That information must be obtained directly from the customer or DBA for the installation site.
|
SQL_DSN= | Used
to reference a DSN for the SQL Server Connection (if applicable)
|
SQL_DATABASENAME= | Used
to define the name of the SQL Server crimestar database
(Default: crimestar ) This field is usually left blank.
|
SQL_USER= | Used
to define the SQL Server Login User Name when something other
than the crimestar database default user name is required. This field is usually
left blank.
|
SQL_PWD= | Used
to define the SQL Server Login User password when something other
than the crimestar database default user password is required. Crimestar encrypted
passwords are recognized by this utility. This
field is usually left blank.
|
SQL_ODBC_DRIVER= | Used
to define the SQL Server ODBC Driver to use for database connections
(Default: {SQL Server} ) This field is usually left blank.
|
[ICDE] | INI File Section Name |
FACILITY_ID= | When
specified, this static string value is appended to the end of
a data output record to help the recipient of the output file
identify the jail facility for which the output file data is being
generated. This is a
100% static value defined in the icde.ini file and appended to
all data records.
Example: FACILITY_ID=XYZ COUNTY JAIL
|
FILE_TYPE= | This
entry is used to specify the file type to be created. The
icde.exe program file creates output files in either the "CSV"
or "XML" format. FILE_TYPE=CSV / XML The default format is CSV
This setting does not apply when COREMR_MODE=ON |
FILE_PATH= | Used
to specify the directory or location where the output file(s)
is/are created before being FTP’d. The default
location is the Windows Temporary File folder.
|
FILE_PREFIX= | Used
to define a specific prefix value for output
file name.
|
FILE_STATIC_NAME= | This entry is used to define a static root file name that the ICDE utility will use to create an output file. The created file will have a .csv or .xml file name extension based on the FILE_TYPE= setting as described above. When the FILE_STATIC_NAME= entry has a value, any FILE_PREFIX= that has been defined will be ignored. When using static file names the icde utility, upon each run, will first check to see if a file by the same name already exists. If the file exists, the icde utility will first attempt to delete the existing file. If the delete of the existing file is successful, the utility will then create a new file of the same name with the most current data from the output process. If the icde utility is unable to first delete the existing file (Due to file locking conflicts with other programs or the Windows Operating system), the creation of a new output file will be skipped. This setting does not apply when COREMR_MODE=ON |
FTP_ADDRESS= | Defines
the IP Address or URL where the destination FTP Server is located.
|
FTP_USERNAME= | Defines
the User Name to use for the FTP Login.
|
FTP_PASSWORD= | Defines
the User Name Password to use for the FTP Login.
|
FTP_SSL_MODE= | This
setting is used to define the type of Transport Layer Security
(TLS) used for a secure FTP-SSL connection with the FTP Host.
This protocol
is commonly referred to as "FTPS"
This
setting can have 4 possible values as follows:
0
= Automatic
1
= Implicit:
(Implicit security is a mechanism by which security is automatically
turned on as soon as the FTP client makes a connection to an FTP
server. In this case, the FTP server defines a specific port for
the client to be used for secure connections.) This setting is less common.
2
= Explicit:
Explicit security requires that the FTP client issues a
specific command to the FTP server after establishing a connection
to establish the SSL link. In explicit TLS the FTP client
needs to send an explicit command (i.e. "AUTH TLS")
to the FTP server to initiate a secure control connection. The
default FTP server port is used. This setting is more common.
3
= None: When
no TLS/SSL connection will be used for the FTP connection.
If
the icde.exe software cannot or does not receive the proper messaging
from the FTP host it may not be able to automatically resolve
the TLS/SSL Mode. In those circumstances the Automatic setting
of "0" may not work properly and expressly defining
the TLS/SSL setting will be required.
*** PLEASE NOTE *** The ICDE utility program uses the "FTPS" protocol which is FTP over Transport Layer Security "TLS" (formerly known as "SSL") as defined in RFC 959 and RFC 1579 with the added option of SSL security as defined in RFC 2228. The utility does not use the SSH File Transfer Protocol commonly referred to as "SFTP" ( which actually has nothing to do with FTP ). They are two entirely different protocols! The "FTPS" protocol which typically runs over ports 21 or 990 and requires that the FTP host server be configured for secure socket layer (SSL) communication as used with "FTPS" and have the appropriate valid SSL security certificate installed. If you are not familiar with the differences between FTPS and SFTP, please GOOGLE it as it is too detailed to completely cover in this help topic! |
FTP_REMOTE_PATH= | Defines
the remote path on the FTP server where the transferred file is
to be placed.
|
FTP_DELETE_LOCAL= | Defines
if the output file which is created locally (as specified by the
FILE_PATH= entry) should be deleted after the FTP transfer. Acceptable
values are: ON or OFF If the FTP file transfer fails
for any reason the file will not be deleted. Only files
that successfully transferred via FTP are deleted.
FTP_DELETE_LOCAL=ON
/ OFF This option is by default OFF.
|
FIELD_SSN= | This
option switch determines if the inmate's Social Security Number
(SSN) is included in the output file.
FIELD_SSN=ON
/ OFF This option is by default ON. |
FIELD_LASTNAME= | This option switch determines if the inmate's Last Name is included in the output file. FIELD_LASTNAME=ON / OFF This option is by default ON. |
FIELD_FIRSTNAME= | This option switch determines if the inmate's First Name is included in the output file. FIELD_FIRSTNAME=ON / OFF This option is by default ON. |
FIELD_MIDDLENAME= | This option switch determines if the inmate's Middle Name is included in the output file. FIELD_MIDDLENAME=ON / OFF This option is by default ON. |
FIELD_DOB= | This option switch determines if the inmate's Date of Birth (DOB) is included in the output file. FIELD_DOB=ON / OFF This option is by default ON. |
FIELD_SEX= | This option switch determines if the inmate SEX (Gender) is added to the output string. FIELD_SEX=ON / OFF This option is by default OFF. |
FIELD_BOOKINGNO= | This option switch determines if the inmate Booking Number is added to the output string. FIELD_BOOKINGNO=ON / OFF This option is by default OFF. |
FIELD_BOOKING_DATE= | This
option switch determines if the inmate Booking date is added to
the output string. FIELD_BOOKING_DATE=ON / OFF This
option is by default OFF. |
FIELD_INCIDENTNO= | This
option switch determines if a related Incident Number is added
to the output string. FIELD_INCIDENTNO=ON / OFF This
option is by default OFF.
|
FIELD_JAIL_ID= | This
option switch determines if the inmate JAIL ID is added to the
output string. FIELD_JAIL_ID=ON
/ OFF This
option is by default OFF.
|
FIELD_MNI= | This option
switch determines if the MASTER NAME INDEX (MNI) # is added to
the output string. FIELD_MNI=ON /OFF. This option
is by default OFF.
|
FIELD_LOCALID= | This
option switch determines if the LOCAL ID field is added to the
output string. FIELD_LOCALID=ON /OFF. This option
is by default OFF. |
FIELD_STATEID= | This
option switch determines if the STATE ID field is added to the
output string. FIELD_STATEID=ON /OFF. This option
is by default OFF. |
FIELD_POD= | This option switch determines if the inmate custody housing POD ID is added to the output string. FIELD_POD=ON / OFF This option is by default OFF. |
FIELD_BLOCK= |
This option switch determines if the inmate custody housing cell Block ID is added to the output string. FIELD_BLOCK=ON / OFF This option is by default OFF. |
FIELD_CELL= | This option switch determines if the inmate custody housing Cell ID is added to the output string. FIELD_CELL=ON / OFF This option is by default OFF. |
QUOTE_ELEMENTS= | The
default .CSV file format specification call for all character
data to be quoted with double quotes. This
allows for character data fields which contain only numbers to
preserve leading zeros and for character data fields to include
imbedded commas in the text. When
QUOTE_ELEMENTS=ON (the default) all character data elements are
quoted with double quotes per the .CSV file specification. When QUOTE_ELEMENTS=OFF,
the character data fields are not quoted.
* Note: To avoid file formatting errors when QUOTE_ELEMENTS=OFF, the output data fields are also scrubbed to remove any imbedded commas that the source data may contain. This is necessary so that those non-quoted imbedded commas are not mistakenly interpreted as a field delimiter. The removing of imbedded commas in the data does change the value of the original data but must be done in order for the file to be processed correctly.
This setting does not apply when COREMR_MODE=ON |
RUN_INTERVAL= | Defines
the Auto-start or Auto-run time interval expressed in minutes
for the program to wait before running. Provided that
the program is running within Windows, the database extract and
file copy or FTP file transfer will pause for RUN_INTERVAL
minutes before execution (again). Example: RUN_INTERVAL=15
(pauses for
15 minutes before running.)
A Run_INTERVAL of zero (0) disables the auto-run feature and the extract process may only be performed by clicking the “Export Data Records Now” button on the application program user interface form.
A RUN_INTERVAL of -1 will cause the utility to immediately perform an extract and transfer then automatically shut down/close. This option could be used if the program were to be activated on regular intervals by the Microsoft Windows scheduler or another external scheduling program.
|
COREMR_MODE= | Defines if the ICDE program should run
using the CoreEMR process and data format. The setting is
COREMR_MODE=ON/OFF. The CorEMR data format extracts both
in-custody and released subjects from the RMS database and creates
a single XML data file for each subject record, then FTPs that
XML file to the CorEMR host as defined by the various FTP setting
in this icde.ini configuration file. To avoid file name
duplication conflicts, each time a data file is created it is
given a unique file name. Because a unique file is created
for each booking record, each time the program is run, it is very
highly recommended that you use the FTP_DELETE_LOCAL=ON setting
to prevent a rapid and excessive accumulation of files on your
local system.
When COREMR_MODE=ON the following settings are
always used:
FIELD_BOOKINGNO=ON
FIELD_BOOKING_DATE=ON
FIELD_INCIDENTNO=OFF FIELD_JAIL_ID=ON FIELD_MNI=ON (special data format is used) FIELD_STATE=ON FIELD_POD=ON FIELD_BLOCK=ON FIELD_CELL=ON The CorEMR mode creates a log file ( icde_coremr_log.dbf ) that is used to track the database records which have already been processed and transferred. Whenever a data record is updated within the Crimestar database and the internal datetime stamp for the record is newer than what has been previously recorded in the icde_coremr_log file, the record will be processed and the log file will be updated. Note that Deleting or removing this log file will force all database records in the databse to be extracted and transferred again. Sample of CorEMR mode xml data output: <?xml version="1.0" encoding="WINDOWS-1252" standalone="true"?> <INMATE> <DataType>PatientDemographics</DataType> <Operation>Add</Operation> <SequenceID>icde_coremr20170918141943_00001.xml</SequenceID> <PostDate>2017-09-18T14:17:00</PostDate> <LastName>ABBOTT</LastName> <FirstName>RICHARD</FirstName> <MI>A</MI> <BirthDate>1946-03-14</BirthDate> <SSN>123-45-6789</SSN> <PatientID>I0000000000000016055.3340</PatientID> <JailID>123456</JailID> <StateNo>CO12345678</StateNo> <FBINo>FBI12345</FBINo> <Sex>M</Sex> <Race>W</Race> <Height>75</Height> <Weight>250</Weight> <Hair>BRO</Hair> <Eyes>BLU</Eyes> <CountyCode>LAKE</CountyCode> <CountyORI>CA0411000</CountyORI> <Facility>MAIN JAIL</Facility> <Pod>P1</Pod> <Block>02</Block> <Cell>03</Cell> <Booking>9876789</Booking> <BookingDate>2012-07-01T00:00:00</BookingDate> <Agency>ABCPD</Agency> <Status>INACTIVE</Status> <UserID>ADMIN</UserID> <PhotoBase64>[Encoded Binary String]</PhotoBase64> </INMATE> |
COREMR_LOG_PATH= | This setting determines the path location where the icde_coremr_log.dbf file is created. By leaving this setting blank or empty the log file will be created in the same folder or location where the icde.exe program is being run. When running the icde.exe program in CoreEMR mode this log file is important because it helps to regulate the records that are processed and transferred to the CorEMR host server. * It is important to ensure that the needed windows permissions exist for the file path you provide so that the log file can be properly created and updated. |