Hi,
I’ve created a Python-script that creates Worklist-files based on two reports from our Electronic Medical Records softwaresystem (Dutch EMR: “Medicore EPD”): The appointments-report of patients for a period, and all patient-information.
The output WL-files are placed here: /var/lib/orthanc/worklists/wl, where they can be served to modalities with the standard Orthanc worklists-plugin.
In our clinic, almost all modalities are ultrasound-devices.
My Setup: The Latest Osimis Orthanc-server image in Docker on a Synology NAS. With a configured volume. The Excel-sheets (xlsx) are saved from a Windows Workstation to the directory /var/lib/orthanc/python/rapportages
The script runs every hour.
Before running this script you must install the following Python-packages in the Orthanc-image:
pip install pandas
pip install pydicom
pip install openpyxl
In the (standard) appointments-report from our EMR only the date and doctor is listed (so, not the consultation-room, used modality etc). I use a trick to determine the room (based on the schedule of our doctors) and the modalities available in that room. Therefor I have a helper-excel sheet with the planning en modaliteits: Karmenta_DICOM_Instellingen.xlsx, with three sheets: the planning (which doctor works when and where), the modalities, and the appointment-types that must be exported as WL-files. For every modality a WL-file is created.
See, below. This is actually my first Python-script, so I will be glad to receive any improvements from more experienced Python-programmers 
Thanks, Gerard Verheij
=====
import orthanc
import threading
import os
import os.path
import pandas as pd
import numpy as np
import datetime
from pydicom.dataset import Dataset, FileMetaDataset
from pydicom.uid import ExplicitVRLittleEndian
import glob
def RemoveWLFiles():
# Search files with .wl extension in current directory
pattern = "/var/lib/orthanc/worklists/wl/*.wl"
files = glob.glob(pattern)
# deleting the files with wl extension
for file in files:
os.remove(file)
def ProcessKarmentaWorklists():
# Open Excel-sheet for reading with PANDA, OpenPyXL and Numpy
# Attention: You probably MUST save the Excel-spreadsheet from Excel, otherwise the item named 'xl/sharedStrings.xml' may be missing in the archive"
df_a = pd.read_excel('/var/lib/orthanc/python/rapportages/afspraken.xlsx')
df_p = pd.read_excel('/var/lib/orthanc/python/rapportages/patienten.xlsx')
df_s = pd.read_excel('/var/lib/orthanc/python/rapportages/Karmenta_DICOM_Instellingen.xlsx','Planning')
df_m = pd.read_excel('/var/lib/orthanc/python/rapportages/Karmenta_DICOM_Instellingen.xlsx','Modaliteiten')
df_t = pd.read_excel('/var/lib/orthanc/python/rapportages/Karmenta_DICOM_Instellingen.xlsx','Afspraaksoorten')
# Create subset-dataframe from the appointments-table with only the relevant columns:
appointments = df_a[["afspraak_id", "datum", "starttijd", "afspraaksoort", "uitvoerder", "clienten_aanwezig"]]
# remove rows where no patient_id (clienten_aanwezig) is available
appointments["clienten_aanwezig"].replace('', np.nan, inplace=True)
appointments.dropna(subset=['clienten_aanwezig'], inplace=True)
patients = df_p[["patient_id", "patient_code", "patient_naam", "roepnaam", "voornamen", "tussenvoegsel_achternaam", "achternaam", "geslacht", "geboortedatum", "inschrijfdatum", "adres", "postcode", "plaats", "bsn", "verzekeringsmaatschappij", "polisnummer", "huisarts", "huisarts_agb"]]
# List with appointment-types ("Afspraaksoort") that must be exported as WL-files
WL_App_types = df_t['Afspraaksoort'].values.tolist()
Counter = 0
for index, row in appointments[0:].iterrows():
if (appointments["afspraaksoort"][index] in WL_App_types):
print("\nAFSPRAAK-GEGEVENS: ")
print("Afspraak-ID is : ", appointments["afspraak_id"][index])
print("Behandelaar is : ", appointments["uitvoerder"][index])
app_pt_id = appointments["clienten_aanwezig"][index]
# select the correct patient as a dataframe from the patient-table based on the patient-ID in appointment-table-column "clienten_aanwezig" (is unique)
pt_df = patients.loc[patients["patient_code"] == app_pt_id]
print("PATIENT-GEGEVENS:")
# In the excel-table with patient-data, the patient_code is unique, so there is always only one match: index=[0]
print("Patient ID : ", pt_df.iloc[0]["patient_id"])
print("Patient geboortedatum is : ", pt_df.iloc[0]["geboortedatum"])
app_date = datetime.datetime(int(appointments["datum"][index][6:]), int(appointments["datum"][index][3:5]), int(appointments["datum"][index][:2]))
app_wday = app_date.strftime("%a")
# Find the room(s) where the study is planned, based on doctorsname and weekday (from the Excel-sheet-tab "Planning")
df_app_room = df_s.loc[(df_s["Arts"] == appointments["uitvoerder"][index]) & (df_s["Weekday"] == app_wday)]
for index_room, row_room in df_app_room[0:].iterrows():
df_modality = df_m.loc[df_m["Kamer"] == df_app_room["Spreekkamer"][index_room]]
app_room = df_app_room["Spreekkamer"][index_room]
print("Datum Study : {0}, Week-dag : {1}, Kamer: {2}".format(app_date, app_wday, app_room))
for index_mod, row_mod in df_modality[0:].iterrows():
app_modality = df_modality["Modaliteit"][index_mod]
# Create DICOM WL-files with PYDICOM
wl_file_name = "/var/lib/orthanc/worklists/wl/{0}-{1}-{2}.wl".format(appointments["afspraak_id"][index], index_room, index_mod)
# Make changes in the variables to meet DICOM WL-dataset requirements
if pt_df.iloc[0]["geslacht"].upper() == "VROUW":
pt_geslacht="V"
else:
pt_geslacht="M"
# Create data set
ds = Dataset()
# Add file meta information elements
ds.file_meta = FileMetaDataset()
ds.file_meta.TransferSyntaxUID = ExplicitVRLittleEndian
ds.file_meta.MediaStorageSOPClassUID = "0"
ds.file_meta.MediaStorageSOPInstanceUID = "0"
# Fill out the worklist query elements
ds.SpecificCharacterSet = "ISO_IR 6" # (0008,0005) CS
ds.InstanceCreationDate = "{0}{1}{2}".format(pt_df.iloc[0]["inschrijfdatum"][6:], pt_df.iloc[0]["inschrijfdatum"][3:5], pt_df.iloc[0]["inschrijfdatum"][:2]) # (0008,0012) DA = Inschrijfdatum van patient in EPD?
ds.AccessionNumber = "100{0}".format(appointments["afspraak_id"][index]) # (0008,0050) SH
ds.PatientName = "{0}^{1}^{2}".format(pt_df.iloc[0]["achternaam"], pt_df.iloc[0]["voornamen"], pt_df.iloc[0]["tussenvoegsel_achternaam"]) # (0010,0010) PN
ds.PatientID = "{0}".format(pt_df.iloc[0]["patient_code"]) # (0010,0020) LO
ds.PatientBirthDate = "{0}{1}{2}".format(pt_df.iloc[0]["geboortedatum"][6:], pt_df.iloc[0]["geboortedatum"][3:5], pt_df.iloc[0]["geboortedatum"][:2]) # (0010,0030) DA
ds.PatientSex = pt_geslacht # (0010,0040) CS
ds.StudyInstanceUID = "{0}".format(appointments["afspraak_id"][index]) # (0020,000d) UI
ds.RequestedProcedureDescription = "Karmenta {0}, kmr: {1}".format(appointments["afspraaksoort"][index], app_room)
ds.ScheduledProcedureStepID = "0001"
ds.ScheduledProcedureStepSequence = [Dataset()]
ds.ScheduledProcedureStepSequence[0].Modality = df_modality["Type"][index_mod] # (0008,0060) CS
ds.ScheduledProcedureStepSequence[0].ScheduledStationAETitle = app_modality # (0040,0001) AE
ds.ScheduledProcedureStepSequence[0].ScheduledProcedureStepStartDate = "{0}{1}{2}".format(appointments["datum"][index][6:], appointments["datum"][index][3:5], appointments["datum"][index][:2]) # (0040,0002) DA
ds.ScheduledProcedureStepSequence[0].ScheduledProcedureStepStartTime = "{0}{1}00".format(appointments["starttijd"][index][:2], appointments["starttijd"][index][3:]) # (0040,0003) TM
ds.ScheduledProcedureStepSequence[0].ScheduledPerformingPhysicianName = "{0}".format(appointments["uitvoerder"][index]) # (0040,0006) PN
ds.ScheduledProcedureStepSequence[0].ScheduledProcedureStepDescription = "{0}-onderzoek Toestel {1}".format(app_modality[:2], df_modality["Naam"][index_mod]) # (0040,0007) LO
# Do NOT print de dataset as it may contain unicode-characters and Python will blow up when it must print unicode-characters in the log...
# print("Filenaam : {0}, DICOM-Dataset is : \n{1}".format(wl_file_name, ds))
# Save directly as a .wl file.
# Set write_like_original=False to be certain you’re writing the dataset in the DICOM File Format
ds.save_as(wl_file_name, write_like_original=False)
Counter = Counter + 1
print("*** Ready *** Amount of exported worklist-files : ", Counter)
TIMER = None
def RunTimer():
global TIMER
TIMER = None
orthanc.LogWarning("In RunTimer of the Karmenta Hourly Worklist scheduler()")
if os.path.exists('/var/lib/orthanc/python/rapportages/afspraken.xlsx'):
if os.path.exists('/var/lib/orthanc/python/rapportages/patienten.xlsx'):
if os.path.exists('/var/lib/orthanc/python/rapportages/Karmenta_DICOM_Instellingen.xlsx'):
RemoveWLFiles()
ProcessKarmentaWorklists()
else:
print("ProcessKarmentaWorklists CANNOT run: Karmenta_DICOM_Instellingen.xlsx. is not available")
else:
print("ProcessKarmentaWorklists CANNOT run: patienten.xlsx is not available")
else:
print("ProcessKarmentaWorklists CANNOT run: afspraken.xlsx is not available")
TIMER = threading.Timer(3600, RunTimer) # Re-schedule every 1 hour (60x60=3600) seconds
TIMER.start()
def OnChange(changeType, level, resource):
if changeType == orthanc.ChangeType.ORTHANC_STARTED:
orthanc.LogWarning("Starting the Karmenta Hourly Worklist scheduler")
RunTimer()
elif changeType == orthanc.ChangeType.ORTHANC_STOPPED:
if TIMER != None:
orthanc.LogWarning("Stopping the Karmenta Hourly Worklist scheduler")
TIMER.cancel()
orthanc.RegisterOnChangeCallback(OnChange)