fo-dicom/Desktop/Worklist/Model/WorklistSQLServerHandler.cs

// Copyright (c) 2012-2020 fo-dicom contributors.
// Licensed under the Microsoft Public License (MS-PL).

using Dicom;
using System;
using System.Data;

using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

using System.Net;
using System.IO;

using System.Security.Cryptography;
using Newtonsoft.Json;

using System.Configuration;
using System.Globalization;
using System.Text;

namespace Worklist_SCP.Model
{

    public class WorklistSQLServerHandler
    {
        private static string HospitalName = ConfigurationManager.AppSettings["HospitalName"];
        private static string HospitalId = ConfigurationManager.AppSettings["HospitalId"];
        private static string ScheduledAET = ConfigurationManager.AppSettings["ScheduledAET"];
        private static string ModalityStringInt = ConfigurationManager.AppSettings["ModalityStringInt"];
        private static bool isChinese = "TRUE".Equals(ConfigurationManager.AppSettings["Chinese"]);
        private static void cache(List<View_PACS> hisBeans)
        {
            try
            {

                var Cache_Url = ConfigurationManager.AppSettings["Cache_Url"];
                List<Tmp> datas = new List<Tmp>();
                foreach (View_PACS hisBean in hisBeans)
                {
                    datas.Add(new Tmp(hisBean.INPATIENTNO, hisBean.NAME, hisBean.zyh, hisBean.cwh, hisBean.PARTOFCHECK));
                }

                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Cache_Url);
                string postDataStr = "data=" + JsonConvert.SerializeObject(datas);
                request.Method = "POST";
                request.ContentType = "application/x-www-form-urlencoded";
                //request.ContentLength = DicomEncoding.GetEncoding("utf-8").GetByteCount(postDataStr);
                //request.ContentLength = DicomEncoding.Default.GetByteCount(postDataStr);
                Stream myRequestStream = request.GetRequestStream();
                //StreamWriter myStreamWriter = new StreamWriter(myRequestStream, DicomEncoding.Default);
                StreamWriter myStreamWriter = new StreamWriter(myRequestStream);
                myStreamWriter.Write(postDataStr);
                myStreamWriter.Close();

                HttpWebResponse response = (HttpWebResponse)request.GetResponse();

                Stream myResponseStream = response.GetResponseStream();
                StreamReader myStreamReader = new StreamReader(myResponseStream, DicomEncoding.GetEncoding("utf-8"));
                string retString = myStreamReader.ReadToEnd();
                myStreamReader.Close();
                myResponseStream.Close();

                Console.WriteLine($"服务器缓存 请求结果:{retString} 请求参数: {postDataStr}");
            } catch(Exception e)
            {
                Console.WriteLine($"请求失败 :{e.Message}");
            }
        }

        private static List<View_PACS> getHisBeans()
        {
            var SQL_IP = ConfigurationManager.AppSettings["SQL_IP"];
            var SQL_DB = ConfigurationManager.AppSettings["SQL_DB"];
            var SQL_USER = ConfigurationManager.AppSettings["SQL_USER"];
            var SQL_Password = ConfigurationManager.AppSettings["SQL_Password"];
            var ModalityConfig = ConfigurationManager.AppSettings["Modality"];
            //var dataSource = "Data Source=192.168.1.52;Initial Catalog=QiHIS;Persist Security Info=True;MultipleActiveResultSets=true;User ID=Lis999;Password=054805;Connect Timeout=30;min pool size=1;connection lifetime=15";
            var dataSource = "Data Source=" + SQL_IP + ";Initial Catalog=" + SQL_DB + "; Persist Security Info=True;MultipleActiveResultSets=true;User ID=" + SQL_USER + ";Password=" + SQL_Password + ";Connect Timeout=30;min pool size=1;connection lifetime=15";
            IFreeSql fsql = new FreeSql.FreeSqlBuilder()
            .UseConnectionString(FreeSql.DataType.SqlServer, dataSource)
            .UseAutoSyncStructure(false) //自动同步实体结构【开发环境必备】
            //.UseMonitorCommand(cmd => Console.Write(cmd.CommandText))
            .Build();

            var hisBeans = fsql.Select<View_PACS>()
                //.Where(b => b.CHECK_TYPE == ModalityConfig)
                //.Where(b => ModalityConfig.Split(',').Contains(b.CHECK_TYPE))

                .Where(b => b.LODGEDATE > DateTime.Now.AddDays(-7))
                .OrderByDescending(b => b.LODGEDATE)
            //.Skip(100)
            //.Limit(10) //第100行-110行的记录
            .ToList();
            return hisBeans;
        }

        private static List<WorklistItem> conversionWorklistItems(List<View_PACS> hisBeans) {
            List<WorklistItem> exams = new List<WorklistItem>();
            foreach (View_PACS hisBean in hisBeans) 
            { 
                exams.Add(conversionWorklistItem(hisBean)); 
            } 
            return exams;
        }

        private static WorklistItem conversionWorklistItem(View_PACS hisBean) {
            WorklistItem exam = new WorklistItem();
            exam.AccessionNumber = hisBean.LODGENO;
            exam.PatientID = hisBean.INPATIENTNO;
            exam.Surname = hisBean.NAME;
            //exam.Name = TinyPinyin.Core.PinyinHelper.GetPinyin(hisBean.NAME);
            //exam.Name = TinyPinyin.Core.PinyinHelper.GetPinyin(hisBean.NAME);
            exam.Name = isChinese ? hisBean.NAME : TinyPinyin.Core.PinyinHelper.GetPinyin(hisBean.NAME);
            //exam.Name = TransferEncoding(Encoding.Default, Encoding.UTF32, hisBean.NAME);


            exam.Forename = "";
            exam.Title = "";
            
            exam.Sex = fixSex(hisBean.SEX);
            exam.DateOfBirth = hisBean.BIRTHDATE.Date;
            //exam.DateOfBirth = DateTime.ParseExact(hisBean.BIRTHDATE, "M/d/Y H:m:s", CultureInfo.InvariantCulture);
            //exam.ReferringPhysician = TinyPinyin.Core.PinyinHelper.GetPinyin(hisBean.LODGEDOCTOR);
            exam.ReferringPhysician = isChinese ? hisBean.LODGEDOCTOR: TinyPinyin.Core.PinyinHelper.GetPinyin(hisBean.LODGEDOCTOR);
            //exam.PerformingPhysician = TinyPinyin.Core.PinyinHelper.GetPinyin(hisBean.LODGESECTION);
            exam.PerformingPhysician = isChinese ? hisBean.LODGESECTION: TinyPinyin.Core.PinyinHelper.GetPinyin(hisBean.LODGESECTION);
            exam.Modality = hisBean.CHECK_TYPE;
            exam.ExamDateAndTime = hisBean.LODGEDATE;
            //exam.ExamDateAndTime = DateTime.ParseExact(hisBean.LODGEDATE, "M/d/Y H:m:s", CultureInfo.InvariantCulture);
            exam.ExamRoom = hisBean.BEDNO;
            exam.ExamDescription = "";
            // exam.StudyUID = "1.2.840.01053932350.3.152.235.2.12.187636473";
            // exam.StudyUID = "1.2.156.14702.3.3506.80065049053.20201024.10350685";
            exam.StudyUID = generatStudyUid(exam.PatientID, exam.AccessionNumber, exam.ExamDateAndTime);
            exam.ProcedureID = "";
            exam.ProcedureStepID = "";
            //exam.HospitalName = TinyPinyin.Core.PinyinHelper.GetPinyin(HospitalName);
            exam.HospitalName = isChinese ? HospitalName : TinyPinyin.Core.PinyinHelper.GetPinyin(HospitalName);
            exam.HospitalId = HospitalId;
            exam.ScheduledAET = ScheduledAET;
            return exam;
        }

        private static String fixSex(String sex) {
            if ("男".Equals(sex))
            {
                return "M";
            }
            if("女".Equals(sex))
            {
                return "F";
            }
            return "O";
        }

        // "1.2.840.xxxxx.3.152.235.2.12.187636473"
        // 1 Identifies ISO
        // 2 Identifies ANSI Member Body
        // 840 Country code of a specific Member Body (U.S. for ANSI)
        // xxxxx Identifies a specific Organization.(assigned by ANSI)
        // 3 Manufacturer defined device type
        // 152 Manufacturer defined serial number
        // 235 Study number
        // 2 Series number
        // 12 Image number
        // 187636473 Encoded date and time stamp of image acquisition

        

        private static string generatStudyUid(string PatientID, string AccessionNumber, DateTime ExamDateAndTime) {
            return "1.2.156.14702.3." + ModalityStringInt + "." + PatientID + "." + AccessionNumber + "." +  GetTimeStamp(ExamDateAndTime);
        }


        private static string GetTimeStamp()
        {
            TimeSpan ts = DateTime.Now - new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return Convert.ToInt64(ts.TotalSeconds).ToString();
        } 

        private static string GetTimeStamp(DateTime dateTime)
        {
            TimeSpan ts = dateTime - new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return Convert.ToInt64(ts.TotalSeconds).ToString();
        } 

        public static IEnumerable<DicomDataset> FilterWorklistItems(DicomDataset request)
        {
            var patientName = request.GetSingleValueOrDefault(DicomTag.PatientName, string.Empty);
            var PatientID = request.GetSingleValueOrDefault(DicomTag.PatientID, string.Empty);
            var api = ConfigurationManager.AppSettings["API"];
            var AETitleConfig = ConfigurationManager.AppSettings["AETitle"];
            var CallingAEConfig = ConfigurationManager.AppSettings["CallingAE"];
            var ModalityConfig = ConfigurationManager.AppSettings["Modality"];
            var paramStr = $"patientName={patientName}&PatientID={PatientID}";
            DicomDataset procedureStep = null;

            if (request.Contains(DicomTag.ScheduledProcedureStepSequence))
            {
                procedureStep = request.GetSequence(DicomTag.ScheduledProcedureStepSequence).First();
                var scheduledStationAET = procedureStep.GetSingleValueOrDefault(DicomTag.ScheduledStationAETitle, string.Empty);
                var performingPhysician = procedureStep.GetSingleValueOrDefault(DicomTag.PerformingPhysicianName, string.Empty);
                var modality = procedureStep.GetSingleValueOrDefault(DicomTag.Modality, string.Empty);
                var scheduledProcedureStepStartDateTime = procedureStep.GetSingleValueOrDefault(DicomTag.ScheduledProcedureStepStartDateTime, string.Empty);
                var procedureStepLocation = procedureStep.GetSingleValueOrDefault(DicomTag.ScheduledProcedureStepLocation, string.Empty);
                var procedureDescription = procedureStep.GetSingleValueOrDefault(DicomTag.ScheduledProcedureStepDescription, string.Empty);
                paramStr = $"AETitleConfig={AETitleConfig}&CallingAEConfig={CallingAEConfig}&ModalityConfig={ModalityConfig}&patientName={patientName}&PatientID={PatientID}&scheduledStationAET={scheduledStationAET}&performingPhysician={performingPhysician}&modality={modality}&scheduledProcedureStepStartDateTime={scheduledProcedureStepStartDateTime}&procedureStepLocation={procedureStepLocation}&procedureDescription={procedureDescription}";
            }
            Console.WriteLine($"Http request url: {api} paramStr: {paramStr}");
            List<View_PACS> hisBeans = getHisBeans();
            cache(hisBeans);
            Console.WriteLine($"hisbeans: {JsonConvert.SerializeObject(hisBeans)}");
            List<WorklistItem> exams = conversionWorklistItems(hisBeans);
            Console.WriteLine($"exams: {JsonConvert.SerializeObject(exams)}");
            var results = exams.ToList();
            //  Parsing result 
            foreach (var result in results)
            {
                var resultingSPS = new DicomDataset();
                var resultDataset = new DicomDataset();
                var resultingSPSSequence = new DicomSequence(DicomTag.ScheduledProcedureStepSequence, resultingSPS);

                if (procedureStep != null)
                {
                    resultDataset.Add(resultingSPSSequence);
                }

                // add results to "main" dataset

                AddIfExistsInRequest(resultDataset, request, DicomTag.AccessionNumber, result.AccessionNumber);    // T2
                AddIfExistsInRequest(resultDataset, request, DicomTag.InstitutionName, result.HospitalName);
                //AddIfExistsInRequest(resultDataset, request, DicomTag.InstitutionCodeSequence, result.HospitalId);
                AddIfExistsInRequest(resultDataset, request, DicomTag.ReferringPhysicianName, result.ReferringPhysician); // T2
                
                //AddIfExistsInRequest(resultDataset, request, DicomTag.PatientName, TransferEncoding(Encoding.Default, Encoding.GetEncoding("GB18030") , result.Name)); //T1

                //AddIfExistsInRequest(resultDataset, request, DicomTag.PatientName, result.Surname + "^" + result.Forename + "^^" + result.Title); //T1
                AddIfExistsInRequest(resultDataset, request, DicomTag.PatientName, result.Name); //T1
                AddIfExistsInRequest(resultDataset, request, DicomTag.ModalitiesInStudy, result.Modality); // T1

                AddIfExistsInRequest(resultDataset, request, DicomTag.PatientID, result.PatientID); // T1
                AddIfExistsInRequest(resultDataset, request, DicomTag.PatientBirthDate, result.DateOfBirth); // T2
                AddIfExistsInRequest(resultDataset, request, DicomTag.PatientSex, result.Sex); //T2

                AddIfExistsInRequest(resultDataset, request, DicomTag.StudyInstanceUID, result.StudyUID); // T1

                AddIfExistsInRequest(resultDataset, request, DicomTag.RequestingPhysician, result.ReferringPhysician); //T2
                AddIfExistsInRequest(resultDataset, request, DicomTag.RequestedProcedureDescription, result.ExamDescription); //T1C

                AddIfExistsInRequest(resultDataset, request, DicomTag.RequestedProcedureID, result.ProcedureID); // T1


                //AddIfExistsInRequest(resultDataset, request, DicomTag.SpecificCharacterSet, "ISO_IR 192");
                //ISO_IR 192 utf8
                //ISO_IR 100 GB18030
                // Scheduled Procedure Step sequence T1
                // add results to procedure step dataset
                // Return if requested
                if (procedureStep != null)
                {

                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.ModalitiesInStudy, result.Modality); // T1
                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.ScheduledStationAETitle, result.ScheduledAET); // T1
                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.ScheduledProcedureStepStartDate, result.ExamDateAndTime); //T1
                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.ScheduledProcedureStepStartTime, result.ExamDateAndTime); //T1
                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.Modality, result.Modality); // T1

                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.ScheduledPerformingPhysicianName, result.PerformingPhysician); //T2
                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.ScheduledProcedureStepDescription, result.ExamDescription); // T1C
                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.ScheduledProcedureStepID, result.ProcedureStepID); // T1
                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.ScheduledStationName, result.ExamRoom); //T2
                    AddIfExistsInRequest(resultingSPS, procedureStep, DicomTag.ScheduledProcedureStepLocation, result.ExamRoom); //T2
                }

                // Put blanks in for unsupported fields which are type 2 (i.e. must have a value even if NULL)
                // In a real server, you may wish to support some or all of these, but they are not commonly supported
                AddIfExistsInRequest(resultDataset, request, DicomTag.SpecificCharacterSet, "GB18030");         // Ref//d Study Sequence
                AddIfExistsInRequest(resultDataset, request, DicomTag.ReferencedStudySequence, new DicomDataset());         // Ref//d Study Sequence
                AddIfExistsInRequest(resultDataset, request, DicomTag.Priority, "");                                  // Priority
                AddIfExistsInRequest(resultDataset, request, DicomTag.PatientTransportArrangements, "");              // Transport Arrangements
                AddIfExistsInRequest(resultDataset, request, DicomTag.AdmissionID, "");                               // Admission ID
                AddIfExistsInRequest(resultDataset, request, DicomTag.CurrentPatientLocation, "");                    // Patient Location
                AddIfExistsInRequest(resultDataset, request, DicomTag.ReferencedPatientSequence, new DicomDataset());       // Ref//d Patient Sequence
                AddIfExistsInRequest(resultDataset, request, DicomTag.PatientWeight, "");                             // Weight
                AddIfExistsInRequest(resultDataset, request, DicomTag.ConfidentialityConstraintOnPatientDataDescription, ""); // Confidentiality Constraint

                // Send Reponse Back
                yield return resultDataset;
            }
        }

        private static string TransferEncoding(Encoding srcEncoding, Encoding dstEncoding, string srcStr)
        {
            byte[] srcBytes = srcEncoding.GetBytes(srcStr);
            byte[] bytes = Encoding.Convert(srcEncoding, dstEncoding, srcBytes);
            return dstEncoding.GetString(bytes);
        }


        //Splits patient name into 2 separte strings surname and forename and send then to the addstringcondition subroutine.
        internal static IQueryable<WorklistItem> AddNameCondition(IQueryable<WorklistItem> exams, string dicomName)
        {
            if (string.IsNullOrEmpty(dicomName) || dicomName == "*")
            {
                return exams;
            }

            var personName = new DicomPersonName(DicomTag.PatientName, dicomName);
            if (dicomName.Contains("*"))
            {
                var firstNameRegex = new Regex("^" + Regex.Escape(personName.First).Replace("\\*", ".*") + "$");
                var lastNameRegex = new Regex("^" + Regex.Escape(personName.Last).Replace("\\*", ".*") + "$");
                exams = exams.Where(x => firstNameRegex.IsMatch(x.Forename) || lastNameRegex.IsMatch(x.Surname));
            }
            else
            {
                exams = exams.Where(x => (x.Forename.Equals(personName.First) && x.Surname.Equals(personName.Last)));
            }

            return exams;
        }


        internal static IQueryable<WorklistItem> AddDateCondition(IQueryable<WorklistItem> exams, string dateCondition)
        {
            if (!string.IsNullOrEmpty(dateCondition) && dateCondition != "*")
            {
                var range = new DicomDateTime(DicomTag.ScheduledProcedureStepStartDate, dateCondition).Get<DicomDateRange>();
                exams = exams.Where(x => range.Contains(x.ExamDateAndTime));
            }
            return exams;
        }


        internal static void AddIfExistsInRequest<T>(DicomDataset result, DicomDataset request, DicomTag tag, T value)
        {
            // Only send items which have been requested
            if (request.Contains(tag))
            {
                if (value == null)
                {
                    value = default;
                }

                result.AddOrUpdate(tag, value);
            }
        }


    }
}