Another sql query for dcm4chee

Find study instance uid for all the files stored before a date.

SELECT distinct(study.study_iuid)
FROM series
JOIN study ON series.study_fk = study.pk
JOIN patient ON study.patient_fk = patient.pk
JOIN instance ON instance.series_fk = series.pk
JOIN files on files.instance_fk = instance.pk
where files.created_time < '2012-12-15';

MDM^T02 with embedded PDF HL7 message sample

MSH|^~\&|PROSOLV|XYZHOSPITAL|SYSTEM|XYZHOSPITAL|200801311600||MDM^T02|PS1-20080131160038|P|2.5
EVN|T10|200801311600
PID|1|987654321|987654321||PROSOLV^SAMPLE||19721201|M||||||||||10000001|111-22-3333
PV1|1|I|CCU^2000^1|||CCU^2003^1|1234^HIPPOCRATES^KOS|9099^KEVORKIAN^JACK|8888^HOUSE^GREGORY||||||||||10000001
ORC|SC|00012345|1-1
OBR|1|00012345|1-1|02585^TransthoracicEcho^PCV4|||20080131155500|||||||||||||||200801311600|||F||||||796.4^^I9M~786.09^^I9M~414.8^^I9M||||54321^Doctorovich^Ivan^
TXA|1|DI|TX|200801311555||200801311600|||IvanDoctorovich|||1.2.840.317.5947431.51.20080131160038|1.2.840.317.5947431.51.20080131155715|00012345|1-1||AU|||||54321^Doctorovich^Ivan^^^^^^^^^^^^200801311600
OBX|1|HD|113014^DICOM Study^DCM||1.2.840.317.5947431.51||||||O
OBX|2|ED|02585^TransthoracicEcho^PCV4||^Application^PDF^Base64^Single_Line_base64_encoded_PDF||||||F

The Single_Line_base64_encoded_PDF, must be a base64 encoded PDF string, in a single line, i.e. the result of this linux command

base64 -w 0 document.pdf

Dcm4chee: move study to trash using command line

Using twiddle.sh on command line:

/export/dcm4chee-2.17.2-mysql/bin/twiddle.sh -s 127.0.0.1 -u admin -p password invoke dcm4chee.archive:service=ContentEditService moveStudyToTrash 1.2.826.0.1.3680043.8.291.20091126113000.28044

Where 1.2.826.0.... is the Study Instance UID

This can be useful for mass trashing or if you have timeout problem on large studies.

dcmcompare

Java program useful to query two pacs server and compare results. i.e. same number of studies, same patients etc. 

Please visit https://github.com/alcir/dcmcompare

dcmold

Java program useful to query pacs server to get scriptable list of studies in a specified time range.

Please visit https://github.com/alcir/dcmold

Send study with a manually generated Patient ID

Send a study with a manually generated Patient ID using dcm4che tools

/usr/java/jdk1.6.0_31-32bit/jre/bin/java -jar /opt/Mayam/dcm4che-1.4.31/bin/dcmsnd.jar --set=PatientID:552220 dicom://DESTAE@192.168.1.94:11112 /path/to/the/study

SQL queries for dcm4chee

How many US patients have not birth date

select count(distinct s.patient_fk)
from study as s
join patient as p
where s.patient_fk = p.pk
and s.mods_in_study = 'US'
and ( p.pat_birthdate not like '1%'
and p.pat_birthdate not like '2%'
or p.pat_birthdate is NULL )

How many US patients have not M or F sex?

select count(distinct s.patient_fk)
from study as s
join patient as p
where s.patient_fk = p.pk
and s.mods_in_study = 'US'
and (p.pat_sex is NULL
or p.pat_sex = 'O' )

How many studies for each modality?

select distinct mods_in_study , count(mods_in_study) as conto from study group by  mods_in_study

How many patients without studies?

SELECT patient.pat_name, patient.pat_id, patient.pk as papk, study.pk as stpk, study.num_series, study.num_instances
FROM patient LEFT JOIN study ON (patient.pk = study.patient_fk)
WHERE (study.pk IS NULL)
ORDER BY patient.pat_name ASC, patient.pk ASC, study.study_datetime ASC

Studies with a number of instances > 10000 Grouped by Year

select count(cnt) as num, year
from(
  SELECT COUNT(*) as cnt, 
  YEAR(study_datetime) as year
  FROM instance as i
  join series as s 
  on i.series_fk = s.pk
  join study as stu
  on stu.pk = s.study_fk
  GROUP BY YEAR(study_datetime),  i.series_fk
  HAVING COUNT(*) > 10000) XX 
GROUP BY year

Files patient and study iuid year and month

SELECT files.filepath, patient.pat_name, study.study_iuid 
FROM series 
join study on series.study_fk = study.pk
join patient on study.patient_fk = patient.pk
join instance on instance.series_fk = series.pk
join files on instance.pk = files.instance_fk
WHERE series.src_aet="DCM4STORICOMS" and YEAR(study.study_datetime) = '2011' 
and MONTH(study.study_datetime) = 7;