Is an "OR" query condition possible?

emmet · Oct 20th 2021

We have modalities that are configured to query conquest by today's date by default, and some modalities allow users to change this when querying but some don't. For those that don't it creates a situation around midnight each night where things get a little unpredictable and very time dependant, for example if a scan is scheduled for 00:10 the following day but it's only 23:50 at the moment then the operator has to wait until midnight to see the entry. Things also become very sensitive to how correct the clocks are on each modality and on conquest, and a difference of a few minutes or more will cause similar mismatching effects. To further complicate things, some modalities implement a feature where they can search for studies "within X hours of now" but in practice it's not always implemented correctly and if this window of time crosses over into a different day it results in a query to conquest like "date>=today AND time>=21:00 AND date<=tomorrow AND time<= 03:00". When this happens this query returns no results, because of the way the times are ANDed (eg >=21:00 AND <= 03:00).

To avoid these issues I'm exploring the possibility of using a WorkListQueryConverter0 to override all date and time queries that a modality may have set, and instead set it to a rolling window that I can apply to all incoming queries, for example filter to all studies from 24 hours in the past to 24 hours in the future no matter what time/date was queried for. However to do this without falling into the wierdness around midnight mentioned above I need an OR clause, for example "(date >=today AND time>=21:00) OR (date<=tomorrow AND time<= 03:00)".

I've tried searching in the code to see if it's possible to do an OR instead of an AND when setting fields in the query but I don't think I found the right place. I've also tried searching to see if it's even possible to define an OR in these queries but also couldn't find any guidance from the standard.

Is this possible? Any suggestions or pointers would be greatly appreciated, thanks in advance!

marcelvanherk · Oct 20th 2021

Hi,

the code to create the queries is hardcoded in vrtosql.cpp. Time is indeed processed as you mentioned. Even though SQL provides OR, the C code does not provide options to insert an OR. Queries are produced by concatenating the results of BuildSearchString for each search tag, so there is no easy way to make it combine date and time. I also noted it does not process the DT_TIME in any way.

WorkListQueryConverter0 can only change the passed DICOM tags, but what you are looking for may not achievable that way. One potential option could be suppress time in the input query (but store it in a local variable) and then add a script in WorkListQueryResultConverter0 do the correct filtering (you can destroy() individual responses). Since worklist queries are not huge this may be workable.

Marcel

emmet · Oct 22nd 2021

Hi Marcel,

Thanks a mill for that. I ended up with exactly as you said, I'll put the scripts below for anyone else in a similar situation.

Here is WorkListQueryConverter0:

Code

-- Always filter by the calling AETitle
Data["0040,0001"] = Association.Calling;

-- Remove some filters that some modalities ask for, which cause problems
Data.RequestedProcedureID = nil;
if Data.ScheduledProcedureStepSequence then
  Data.ScheduledProcedureStepSequence[0].ScheduledProcedureStepLocation = nil;
  Data.ScheduledProcedureStepSequence[0].ScheduledStationName = nil
end

-- Next we overwrite the date/time of the results, we filter further in WorkListQueryResultConverter0
-- Note that we can't filter by time here, only date, since we can't use OR in the query we
-- can only filter out by dates outside the desired window in WorkListQueryResultConverter0
-- https://forum.image-systems.biz/forum/index.php?thread/50405-is-an-or-query-condition-possible/

startTimestamp = os.time(os.date('*t')) - (60 * 60 * 12)
endTimestamp = os.time(os.date('*t')) + (60 * 60 * 12)

startDate = os.date("%Y%m%d", startTimestamp)
endDate = os.date("%Y%m%d", endTimestamp)

startTime = os.date("%H%M%S", startTimestamp)

Data.ScheduledProcedureStepStartDate = startDate .. "-" .. endDate

-- This is here just to ensure the time is returned in the response, it doesnt exclude anything
Data.ScheduledProcedureStepStartTime = '000000-235959'

Display More

And here is ModalityWorklistQueryResultConverter0:

Code

startTimestamp = os.time(os.date('*t')) - (60 * 60 * 12)
endTimestamp = os.time(os.date('*t')) + (60 * 60 * 12)

startDate = os.date("%Y%m%d", startTimestamp)
endDate = os.date("%Y%m%d", endTimestamp)

startTime = os.date("%H%M%S", startTimestamp)
endTime = os.date("%H%M%S", endTimestamp)

-- Remove any result that is outside our window
if (Data.ScheduledProcedureStepStartDate and Data.ScheduledProcedureStepStartTime and Data.ScheduledProcedureStepStartDate <= startDate and Data.ScheduledProcedureStepStartTime < startTime) then
    print("Skipping worklist result " .. Data.ScheduledProcedureStepStartDate .. " at " .. Data.ScheduledProcedureStepStartTime .. " because it's outside window of ".. startDate .. " " .. startTime .. " to " .. endDate .. " " .. endTime)
    destroy()
end

if (Data.ScheduledProcedureStepStartDate and Data.ScheduledProcedureStepStartTime and Data.ScheduledProcedureStepStartDate >= endDate and Data.ScheduledProcedureStepStartTime > endTime) then
    print("Skipping worklist result " .. Data.ScheduledProcedureStepStartDate .. " at " .. Data.ScheduledProcedureStepStartTime .. " because it's outside window of ".. startDate .. " " .. startTime .. " to " .. endDate .. " " .. endTime)
    destroy()
end

print("Keeping worklist result " .. Data.ScheduledProcedureStepStartDate .. " at " .. Data.ScheduledProcedureStepStartTime .. " because it's within window of ".. startDate .. " " .. startTime .. " to " .. endDate .. " " .. endTime)

Display More

I'm more than happy to hear any improvements to these, so far it looks like it will do the trick quite nicely.

Thanks again marcelvanherk !

marcelvanherk · Oct 22nd 2021

Nice!

Marcel

Is an "OR" query condition possible?

Participate now!

Share

Similar Threads

Questions on PACS Operations

Tags