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