Thursday, October 25, 2012

Patches to the Modality Worklist Database

It turns out that the Modality Worklist Server draws a lot of attention and that there were couple of glitches in the last release. The main issue comes from date and time columns in the database (i.e. datetime sql type). Until this is fixed in the DICOM Database Plugin, here's a little patch that maps the database date and time fields to valid DICOM date and time formats.

The DICOM date format is YYYYMMDD and the DICOM time format is HHMMSS.TTT or HHMMSS

Another issue that is going to be solved in the next release is the case sensitive column names. This means that in order to add more fields to your MWL C-FIND Response, you should add the columns to the view exactly as they are named in the toolkit dictionary with the correct case. This problem was in the last build modality field that should have been Modality with a capital M.


I'm working on preparing the database plugin ready for source code release but until then, the following two scripts are the proper mapping of the MWL view and sub-view to these formats. The modifications are highlighted.


USE [RZ_DICOM]
GO
/****** Object:  View [dbo].[CFIND_MWL_VIEW]    Script Date: 10/25/2012 22:52:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[CFIND_MWL_VIEW]
AS
SELECT     dbo.REQUESTED_PROCEDURE.study_instance_uid AS StudyInstanceUID,
                      dbo.REQUESTED_PROCEDURE.req_proc_desc AS RequestedProcedureDescription,
                      dbo.REQUESTED_PROCEDURE.id AS ScheduledProcedureStepSequence, dbo.REQUESTED_PROCEDURE.req_proc_id AS RequestedProcedureID,
                      dbo.SERVICE_REQUEST.accession_number AS AccessionNumber, dbo.SERVICE_REQUEST.referring_physicians_names AS ReferringPhysicianName,
                      dbo.SERVICE_REQUEST.requesting_physician AS RequestingPhysician, dbo.N_PATIENT.patient_id AS PatientID,
                      dbo.N_PATIENT.patient_name AS PatientName, CONVERT(char(8), dbo.N_PATIENT.birth_date, 112) AS PatientBirthDate,
                      dbo.N_PATIENT.sex AS PatientSex
FROM         dbo.REQUESTED_PROCEDURE INNER JOIN
                      dbo.SERVICE_REQUEST ON dbo.REQUESTED_PROCEDURE.service_request_id = dbo.SERVICE_REQUEST.id INNER JOIN
                      dbo.N_PATIENT ON dbo.REQUESTED_PROCEDURE.patient_internal_id = dbo.N_PATIENT.id


USE [RZ_DICOM]
GO
/****** Object:  View [dbo].[CFIND_ScheduledProcedureStepSequence_VIEW]    Script Date: 10/25/2012 22:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[CFIND_ScheduledProcedureStepSequence_VIEW]
AS
SELECT     id, modality, scheduled_station_ae_title AS ScheduledStationAETitle, CONVERT(char(8), start_date_time, 112) AS ScheduledProcedureStepStartDate
                      perf_phys_name AS ScheduledPerformingPhysicianName, req_proc_id AS ScheduledProcedureStepSequence, REPLACE(CONVERT(char(8), 
                      start_date_time, 108), ':', '') AS ScheduledProcedureStepStartTime, sched_proc_step_desc AS ScheduledProcedureStepDescription, 
                      'SPSID' + CAST(id AS nvarchar) AS ScheduledProcedureStepID
FROM         dbo.SCHED_PROC_STEP

As always, comments and questions are welcome

2 comments:

  1. Please help I'm trying out the MWL server but don't really understand how to define my work list structure. I wanted to create a third level for ScheduledProtocolCodeSequence so I tried to copy the logic above. Add Id field to CFIND_ScheduledProcedureStepSequence_VIEW called ScheduledProtocolCodeSequence create a new view CFIND_ScheduledProtocolCodeSequence_VIEW with a fkey field called ScheduledProtocolCodeSequence and additional fields that map to the DICOM tags. Then just hard coded some values to see if it would pick them up. When I manually join from the CFIND_ScheduledProcedureStepSequence_VIEW to CFIND_ScheduledProtocolCodeSequence_VIEW everything is fine and each row has data but the MWL never picks up any data for the CFIND_ScheduledProtocolCodeSequence_VIEW.

    My new view looks like this

    Alter VIEW [dbo].[CFIND_ScheduledProtocolCodeSequence_VIEW]
    AS
    SELECT
    id AS ScheduledProtocolCodeSequence,
    'CV' AS CodeValue,
    '1' AS CodingSchemeVersion,
    '99CSD' AS CodingSchemeDesignator,
    'DCM' AS CodeMeaning
    FROM dbo.SCHED_PROC_STEP

    When I run the server from the command prompt in verbose mode when I send a query I see it recognizes the new sequence level but does not show any fields for it(see below..Any idea how I get it to recognize the additional fields?

    ReplyDelete
  2. Hi
    If you download HL7Kit 2018 latest version from here there’s example script exactly for this in the sql scripts folders.
    http://downloads.roniza.com/downloads/hl7kit/HL7Kit2018/HL7Kit-2018_setup_01_05_2018_490.zip

    ReplyDelete