Developers Getting Started With i2b2
Space shortcuts
Space Tools
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 23 Next »


Overview

Data Exporter functionality enables an i2b2 user to request patient data for a desired patient set query. The request is then processed by Manager role use. The patient data is exported as a file and stored in a specified location for retrieval.

The patient dataset, request options and file specifications are configured in the database using specific request type entries and configuration parameters in the database.

Design and Architecture

The existing i2b2 breakdown architecture is modified to display breakdowns for the request/Export Options display in the run query dialog corresponding to the entries in the QT_RESULT_TYPE table

Configuring Request/Export parameters

User request and Manager Export parameters are configured in QT_REULT_TYPE table. VISUAL_ATTRIBUTE_TYPE_ID is set to LR for Data request and LX for Data export request

USER_ROLE_CD should be minimum DATA_LDS for Data Request and USER_ROLE_CD should be MANAGER for Data Export Options display.


NAMEDESCRIPTIONDISPLAYTYPE_IDVISUAL_ATTRIBUTE_TYPE_IDUSER_ROLE_CDCLASSNAME
PATIENT_DEMOGRAPHIC_REQUESTRequest Demographics DataCATNUMLRDATA_LDSedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest
PATIENT_MEDICATION_REQUESTRequest Medication DataCATNUMLRDATA_LDSedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest
PATIENT_PROCEDURE_REQUESTRequest Procedure DataCATNUMLRDATA_LDSedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest
PATIENT_DIAGNOSIS_REQUESTRequest Diagnosis DataCATNUMLRDATA_LDSedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest
PATIENT_LAB_REQUESTRequest Lab DataCATNUMLRDATA_LDSedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest
PATIENT_MAPPING_REQUESTRequest Patient MappingCATNUMLRDATA_LDSedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest
PATIENT_DEMOGRAPHIC_CSVExport Demographics DataCATNUMLXMANAGERedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientDownload
PATIENT_MEDICATION_CSVExport Medication DataCATNUMLXMANAGERedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientDownload
PATIENT_PROCEDURE_CSVExport Procedure DataCATNUMLXMANAGERedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientDownload
PATIENT_DIAGNOSIS_CSVExport Diagnosis DataCATNUMLXMANAGERedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientDownload
PATIENT_LAB_CSVExport Lab DataCATNUMLXMANAGERedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientDownload
PATIENT_MAPPING_CSVExport Patient MappingCATNUMLXMANAGERedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientDownload



Configuring Export file generation

The QT_BREAKDOWN_PATH table has XML file entries with embedded SQL statements for each Request type and details for file content generation 


NAMEVALUE
PATIENT_MAPPING_CSV<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}.
  </RequestLetter>
  <LetterFilename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Readme.txt</LetterFilename>
  <Letter>
Results of the i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, are available.

Important notes about your data:
- Total number of patients returned in your data request: {{{PATIENT_COUNT}}}
- i2b2 reviewer:

Only persons specifically authorized and selected (as listed at the top of this letter) can download these files. If additional user access is needed, please ensure the person is listed on your project IRB protocol and contact the i2b2 team.

Specifically:
- Remove all PHI from computer, laptop, or mobile device after analysis is completed.
- Do NOT share PHI or PII with anyone who is not listed on the IRB protocol.

Your guideline for the storage of Protected Health Information can be found at: https://www.site.com/guidelines_for_protecting_and_storing_phi.pdf

*To download these files*
- You must be logged onto your site

These results are the data that was requested under the authority of the Institutional Review Board.  The query resulting in this identified patient data is included at the end of this letter.  A copy of this letter is kept on file and is available to the IRB in the event of an audit.

Thank you,

The i2b2 Team </Letter>
  <Table>
<Filename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/PatientMapping.csv</Filename>
<Query>SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"
      ,a.PATIENT_IDE_SOURCE as "PATIENT_SOURCE"
      ,a.PATIENT_IDE as "PATIENT_IDE"
  FROM patient_mapping a, {{{DX}}} c
  where a.patient_num = c.patient_num</Query>
      <SeparatorCharacter>\t</SeparatorCharacter>
  </Table>

</ValueExporter>
PATIENT_MEDICATION_CSV<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}.
  </RequestLetter>
  <LetterFilename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Readme.txt</LetterFilename>
  <Letter>
Results of the i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, are available.

Important notes about your data:
- Total number of patients returned in your data request: {{{PATIENT_COUNT}}}
- i2b2 reviewer:

Only persons specifically authorized and selected (as listed at the top of this letter) can download these files. If additional user access is needed, please ensure the person is listed on your project IRB protocol and contact the i2b2 team.

Specifically:
- Remove all PHI from computer, laptop, or mobile device after analysis is completed.
- Do NOT share PHI or PII with anyone who is not listed on the IRB protocol.

Your guideline for the storage of Protected Health Information can be found at: https://www.site.com/guidelines_for_protecting_and_storing_phi.pdf

*To download these files*
- You must be logged onto your site

These results are the data that was requested under the authority of the Institutional Review Board.  The query resulting in this identified patient data is included at the end of this letter.  A copy of this letter is kept on file and is available to the IRB in the event of an audit.

Thank you,

The i2b2 Team
</Letter>
  <Table>
<Filename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Medication.csv</Filename>
<Query>SELECT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"
        ,a.start_date as "START_DATE"
        ,a.start_date as "END_DATE"
        ,b.name_char as "MEDICATION_NAME"
        ,b.concept_cd as "NDC_CODE"
        ,a.units_cd as "UNIT"
        ,a.quantity_num as "DOSE_QUANTITY"       
        ,a.modifier_cd as "MODIFIER"
        ,m.name_char as "MODIFIER_NAME"
        ,a.instance_num as "INSTANCE_NUM"
        ,a.location_cd as "FACILITY"
        ,case v.inout_cd  when 'O' then 'Outpatient' when 'I' then 'Inpatient' else 'Unknown' end as "ENCOUNTER_TYPE"
        ,p.name_char as "PROVIDER"
        ,a.encounter_num as "ENCOUNTER_NUMBER"
    FROM observation_fact  a
    INNER  JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like '\i2b2\Medications\%'
    JOIN {{{DX}}} c on a.patient_num = c.patient_num
    LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id
    JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num
    LEFT OUTER JOIN modifier_dimension m on a.modifier_cd = m.modifier_cd</Query>
      <SeparatorCharacter>\t</SeparatorCharacter>
  </Table>

</ValueExporter>
PATIENT_PROCEDURE_CSV<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}.
  </RequestLetter>
  <LetterFilename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Readme.txt</LetterFilename>
  <Letter>
Results of the i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, are available.

Important notes about your data:
- Total number of patients returned in your data request: {{{PATIENT_COUNT}}}
- i2b2 reviewer:

Only persons specifically authorized and selected (as listed at the top of this letter) can download these files. If additional user access is needed, please ensure the person is listed on your project IRB protocol and contact the i2b2 team.

Specifically:
- Remove all PHI from computer, laptop, or mobile device after analysis is completed.
- Do NOT share PHI or PII with anyone who is not listed on the IRB protocol.

Your guideline for the storage of Protected Health Information can be found at: https://www.site.com/guidelines_for_protecting_and_storing_phi.pdf

*To download these files*
- You must be logged onto your site

These results are the data that was requested under the authority of the Institutional Review Board.  The query resulting in this identified patient data is included at the end of this letter.  A copy of this letter is kept on file and is available to the IRB in the event of an audit.

Thank you,

The i2b2 Team
</Letter>
  <Table>
<Filename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Procedure.csv</Filename>
<Query>SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"
        ,m.patient_ide as ”PATIENT_ID"
        ,a.start_date as "START_DATE"
        ,a.start_date as "END_DATE"
        ,b.name_char as "PROCEDURE_NAME"
        ,b.concept_cd as "PROCEDURE_CODE"
        ,a.quantity_num as "QUANTITY"
        ,a.modifier_cd as "MODIFIER"
        ,a.location_cd as "FACILITY"
        ,case v.inout_cd  when 'O' then 'Outpatient' when 'I' then 'Inpatient' else 'Unknown' end as "ENCOUNTER_TYPE"
        ,p.name_char as "PROVIDER"
        ,a.encounter_num as "ENCOUNTER_NUMBER"
    FROM observation_fact  a
    INNER  JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like '\i2b2\Procedures\%'
    JOIN {{{DX}}} c on a.patient_num = c.patient_num
    LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id
    JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num
    JOIN patient_mapping m on a.patient_num = m.patient_num   
     </Query>
      <SeparatorCharacter>\t</SeparatorCharacter>
  </Table>

</ValueExporter>
PATIENT_LAB_CSV<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}.
  </RequestLetter>
  <LetterFilename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Readme.txt</LetterFilename>
  <Letter>
Results of the i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, are available.

Important notes about your data:
- Total number of patients returned in your data request: {{{PATIENT_COUNT}}}
- i2b2 reviewer:

Only persons specifically authorized and selected (as listed at the top of this letter) can download these files. If additional user access is needed, please ensure the person is listed on your project IRB protocol and contact the i2b2 team.

Specifically:
- Remove all PHI from computer, laptop, or mobile device after analysis is completed.
- Do NOT share PHI or PII with anyone who is not listed on the IRB protocol.

Your guideline for the storage of Protected Health Information can be found at: https://www.site.com/guidelines_for_protecting_and_storing_phi.pdf

*To download these files*
- You must be logged onto your site

These results are the data that was requested under the authority of the Institutional Review Board.  The query resulting in this identified patient data is included at the end of this letter.  A copy of this letter is kept on file and is available to the IRB in the event of an audit.

Thank you,

The i2b2 Team
</Letter>
  <Table>
<Filename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Lab.csv</Filename>
<Query>SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"
       ,m.patient_ide as ”PATIENT_ID"
       ,a.start_date as "START_DATE"
       ,a.start_date as "END_DATE"
       ,b.name_char as "LAB_NAME"
       ,b.concept_cd as "LAB_CODE"
       ,case when a.valtype_cd = 'T' then tval_char else to_char(nval_num) end as "LAB_RESULTS"
       ,a.modifier_cd as "MODIFIER"
       ,a.location_cd as "FACILITY"
       ,case v.inout_cd  when 'O' then 'Outpatient' when 'I' then 'Inpatient' else 'Unknown' end as "ENCOUNTER_TYPE"
       ,p.name_char as "PROVIDER"
       ,a.encounter_num as "ENCOUNTER_NUMBER"
   FROM observation_fact  a
   INNER  JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like '\i2b2\Labtests\%'
   JOIN {{{DX}}} c on a.patient_num = c.patient_num
   LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id
   JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num
   JOIN patient_mapping m on a.patient_num = m.patient_num
</Query>
      <SeparatorCharacter>\t</SeparatorCharacter>
  </Table>

</ValueExporter>
PATIENT_DEMOGRAPHIC_CSV<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}.
  </RequestLetter>
  <LetterFilename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Readme.txt</LetterFilename>
  <Letter>
Results of the i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, are available.

Important notes about your data:
- Total number of patients returned in your data request: {{{PATIENT_COUNT}}}
- i2b2 reviewer:

Only persons specifically authorized and selected (as listed at the top of this letter) can download these files. If additional user access is needed, please ensure the person is listed on your project IRB protocol and contact the i2b2 team.

Specifically:
- Remove all PHI from computer, laptop, or mobile device after analysis is completed.
- Do NOT share PHI or PII with anyone who is not listed on the IRB protocol.

Your guideline for the storage of Protected Health Information can be found at: https://www.site.com/guidelines_for_protecting_and_storing_phi.pdf

*To download these files*
- You must be logged onto your site

These results are the data that was requested under the authority of the Institutional Review Board.  The query resulting in this identified patient data is included at the end of this letter.  A copy of this letter is kept on file and is available to the IRB in the event of an audit.

Thank you,

The i2b2 Team </Letter>
  <Table>
<Filename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Demographics.csv</Filename>
<Query>SELECT   to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"
      ,a.BIRTH_DATE as "BIRTH_DATE"
      , a.DEATH_DATE as "DEATH_DATE"
      ,a.SEX_CD as "GENDER"
      , a.AGE_IN_YEARS_NUM as "AGE_IN_YEARS"
      ,a.LANGUAGE_CD as "PRIMARY_SPOKEN_LANGUAGE"
      ,a.RACE_CD as "RACE"
      ,a.MARITAL_STATUS_CD as "MARTIAL_STATUS"
      ,a.RELIGION_CD as "RELIGION"
      ,a.ZIP_CD as "ZIP_CODE"
      ,a.STATECITYZIP_PATH as "STATE_CITY_ZIP"
      ,a.INCOME_CD as "INCOME"
      ,a.VITAL_STATUS_CD as "VITAL_STATUS"
  FROM patient_dimension a, {{{DX}}} c
  where a.patient_num = c.patient_num</Query>
      <SeparatorCharacter>\t</SeparatorCharacter>
  </Table>

</ValueExporter>
PATIENT_DIAGNOSIS_CSV<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}.
  </RequestLetter>
  <LetterFilename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Readme.txt</LetterFilename>
  <Letter>
Results of the i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, are available.

Important notes about your data:
- Total number of patients returned in your data request: {{{PATIENT_COUNT}}}
- i2b2 reviewer:

Only persons specifically authorized and selected (as listed at the top of this letter) can download these files. If additional user access is needed, please ensure the person is listed on your project IRB protocol and contact the i2b2 team.

Specifically:
- Remove all PHI from computer, laptop, or mobile device after analysis is completed.
- Do NOT share PHI or PII with anyone who is not listed on the IRB protocol.

Your guideline for the storage of Protected Health Information can be found at: https://www.site.com/guidelines_for_protecting_and_storing_phi.pdf

*To download these files*
- You must be logged onto your site

These results are the data that was requested under the authority of the Institutional Review Board.  The query resulting in this identified patient data is included at the end of this letter.  A copy of this letter is kept on file and is available to the IRB in the event of an audit.

Thank you,

The i2b2 Team
</Letter>
  <Table>
<Filename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Diagnosis.csv</Filename>
<Query>SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"
       ,m.patient_ide as ”PATIENT_ID"
       ,a.start_date as "START_DATE"
       ,a.start_date as "END_DATE"
       ,b.name_char as "DIAGNOSIS_NAME"
       ,b.concept_cd as "DIAGNOSIS_CODE"
       ,a.modifier_cd as "MODIFIER"
       ,a.location_cd as "FACILITY"
       ,case v.inout_cd  when 'O' then 'Outpatient' when 'I' then 'Inpatient' else 'Unknown' end as "ENCOUNTER_TYPE"
       ,p.name_char as "PROVIDER"
       ,a.encounter_num as "ENCOUNTER_NUMBER"
   FROM observation_fact  a
   INNER  JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like '\i2b2\Diagnoses\%'
   JOIN {{{DX}}} c on a.patient_num = c.patient_num
   LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id
   JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num
   JOIN patient_mapping m on a.patient_num = m.patient_num
   </Query>
      <SeparatorCharacter>\t</SeparatorCharacter>
  </Table>

</ValueExporter>
PATIENT_MEDICATION_REQUEST<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_MASTER_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}.
  </RequestLetter>
</ValueExporter>
PATIENT_MAPPING_REQUEST<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}.
  </RequestLetter>
</ValueExporter>
PATIENT_PROCEDURE_REQUEST<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}.
  </RequestLetter>
</ValueExporter>
PATIENT_LAB_REQUEST<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}.
  </RequestLetter>
</ValueExporter>
PATIENT_DEMOGRAPHIC_REQUEST<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}.
  </RequestLetter>
</ValueExporter>
PATIENT_DIAGNOSIS_REQUEST<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}.
  </RequestLetter>
</ValueExporter>



Configuring Export file format

Export File format, location and email server parameters are configurable in the HIVE_CELL_PARAMS


CRCedu.harvard.i2b2.crc.exportcsv.datamanageremailuserid@partners.org
CRCedu.harvard.i2b2.crc.smtp.hostsmtp.partners.org
CRCedu.harvard.i2b2.crc.smtp.port25
CRCedu.harvard.i2b2.crc.smtp.ssl.enableFALSE
CRCedu.harvard.i2b2.crc.smtp.authFALSE
CRCedu.harvard.i2b2.crc.smtp.usernamenone
CRCedu.harvard.i2b2.crc.smtp.passwordnone
CRCedu.harvard.i2b2.crc.exportcsv.defaultescapecharacter"
CRCedu.harvard.i2b2.crc.exportcsv.maxfetchrows-1
CRCedu.harvard.i2b2.crc.exportcsv.defaultlineend\n
CRCedu.harvard.i2b2.crc.exportcsv.defaultseperator\t
CRCedu.harvard.i2b2.crc.exportcsv.resultfetchsize50000
CRCedu.harvard.i2b2.crc.exportcsv.filename/tmp/{{{PROJECT_ID}}}/{{{DATE_yyyyMMdd}}}_{{{FULL_NAME}}}.tsv
CRCedu.harvard.i2b2.crc.exportcsv.defaultquotechar"
CRCedu.harvard.i2b2.crc.exportcsv.workfolder/tmp/i2b2
CRCedu.harvard.i2b2.crc.exportcsv.zipencryptmethodnone




Java Changes

include a new breakdown class for the file export process


Request/Export Process

  • End user logs into webclient and creates a patientset query

  • Selects a Data Request option and

  • Click run Query

  • Email is sent to manager user to run the user request

  • The manager logs into the webclient and finds the run query by matching the query master id

  • Manager user then re-runs the previous query and clicks on export.  The export option is displayed selected in the Data export breakdown.

  • Data export runs in the background and file is generated in the folder specified in the HIVE_CELL_PARAM

  • Manger user manually sends a confirmation email to the user on the file generation and retrieval info.




Display for End User



Display for Manager user



Updating Database Configuration


Entry into HIVE_CELL_PARAMS

File parameter specification entries




Variables for File name entries 





Entry into QT_BREAKDOWN_PATH




Entry into QT_RESULT_TTPE




Example Export files

Demographics


Diagnosis


Labs


Procedures


Medications


Patient_Mapping



Sample Letter generated for Data Manager









  • No labels