//検査メモ履歴の取得
public static void GetStudyMemoHistory(StudyKey key, out List <StudyMemoItem> items)
{
items = new List <StudyMemoItem>();
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT MemoDate,Memo,UserName FROM T_StudyMemoHistory WHERE StudyInstanceUID=@0 ORDER BY MemoDate DESC";
cmd.Add(key.StudyInstanceUID);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var item = new StudyMemoItem();
item.Memo = (string)dr["Memo"];
item.UserName = (string)dr["UserName"];
item.MemoDate = (DateTime)dr["MemoDate"];
items.Add(item);
}
}
}
}
}
//レポート連携情報取得
public static void GetReportInfo(StudyKey key, out StudyTag tag)
{
tag = new StudyTag();
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT AccessionNumber,PatientID,StudyDate,StudyTime,Modality FROM T_Study WHERE StudyInstanceUID=@0";
cmd.Add(key.StudyInstanceUID);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
tag.AccessionNumber = (string)dr["AccessionNumber"];
tag.PatientID = (string)dr["PatientID"];
tag.StudyDate = (string)dr["StudyDate"];
tag.StudyTime = (string)dr["StudyTime"];
tag.Modality = (string)dr["Modality"];
}
}
}
}
}
//StorageIDの取得 (DCM用)
public static void GetImageStorageID(ref SeriesKey key)
{
if (key.StorageID != null && key.StorageID != "")
{
return;
}
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT StorageID FROM T_Image WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1 AND SOPInstanceUID=@2";
cmd.Add(key.StudyInstanceUID);
cmd.Add(key.SeriesInstanceUID);
cmd.Add(key.SOPInstanceUID);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
key.StorageID = (string)dr["StorageID"];
}
}
}
}
}
//キーワードの設定
public static bool SetKeyword(StudyKey key, string item)
{
for (int i = 0; i < AppUtil.RetryCount; i++)
{
string err = "";
using (var db = new TryDbConnection(settings))
{
db.BeginTransaction();
try
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "UPDATE T_Study SET Keyword=@1 WHERE StudyInstanceUID=@0";
cmd.Add(key.StudyInstanceUID);
cmd.Add(item);
cmd.ExecuteNonQuery();
}
db.Commit();
return(true);
}
catch (Exception ex)
{
db.Rollback();
err = ex.ToString();
}
}
//リトライ
if (i < AppUtil.RetryCount - 1)
{
System.Threading.Thread.Sleep(AppUtil.SleepTime);
}
else
{
LogUtil.Error(err);
}
}
return(false);
}
//GSPSの取得
public static void GetGsps(GSPSKey key, out List <ImageKey> items)
{
items = new List <ImageKey>();
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
if (key.ReferencedSOPInstanceUID == "")
{
cmd.CommandText = "SELECT DISTINCT SeriesInstanceUID,SOPInstanceUID,StorageID FROM T_GSPS_R WHERE StudyInstanceUID=@0 AND ReferencedSeriesInstanceUID=@1 AND ContentLabel=@2";
cmd.Add(key.StudyInstanceUID);
cmd.Add(key.ReferencedSeriesInstanceUID);
cmd.Add(key.ContentLabel);
}
else
{
cmd.CommandText = "SELECT DISTINCT SeriesInstanceUID,SOPInstanceUID,StorageID FROM T_GSPS_R WHERE StudyInstanceUID=@0 AND ReferencedSeriesInstanceUID=@1 AND ReferencedSOPInstanceUID=@2 AND ContentLabel=@3";
cmd.Add(key.StudyInstanceUID);
cmd.Add(key.ReferencedSeriesInstanceUID);
cmd.Add(key.ReferencedSOPInstanceUID);
cmd.Add(key.ContentLabel);
}
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var imkey = new ImageKey()
{
StudyInstanceUID = key.StudyInstanceUID,
SeriesInstanceUID = (string)dr["SeriesInstanceUID"],
SOPInstanceUID = (string)dr["SOPInstanceUID"],
StorageID = (string)dr["StorageID"]
};
items.Add(imkey);
}
}
}
}
}
//検査メモの取得
public static void GetStudyMemo(StudyKey key, out StudyMemoItem item, out int histCount)
{
item = null;
histCount = 0;
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT MemoDate,Memo,UserName FROM T_StudyMemo WHERE StudyInstanceUID=@0";
cmd.Add(key.StudyInstanceUID);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
item = new StudyMemoItem();
item.Memo = (string)dr["Memo"];
item.UserName = (string)dr["UserName"];
item.MemoDate = (DateTime)dr["MemoDate"];
}
}
}
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) cnt FROM T_StudyMemoHistory WHERE StudyInstanceUID=@0";
cmd.Add(key.StudyInstanceUID);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
histCount = Convert.ToInt32(dr["cnt"]);
}
}
}
}
}
//スタディの削除
public static bool DelStudy(StudyKey key, out List <string> items)
{
items = new List <string>();
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT DISTINCT StorageID FROM T_Image WHERE StudyInstanceUID=@0";
cmd.Add(key.StudyInstanceUID);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
items.Add((string)dr["StorageID"]);
}
}
}
}
for (int i = 0; i < AppUtil.RetryCount; i++)
{
using (var db = new TryDbConnection(settings))
{
db.BeginTransaction();
try
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "DELETE FROM T_Study WHERE StudyInstanceUID=@0";
cmd.Add(key.StudyInstanceUID);
cmd.ExecuteNonQuery();
}
db.Commit();
return(true);
}
catch (Exception ex)
{
db.Rollback();
LogUtil.Warn(ex);
}
}
//リトライ
if (i < AppUtil.RetryCount - 1)
{
System.Threading.Thread.Sleep(AppUtil.SleepTime);
LogUtil.Info1("RETRY:{0}", i + 1);
}
else
{
LogUtil.Error1("STUDY削除:失敗 [{0}]", key.StudyInstanceUID);
return(false);
}
}
return(true);
}
//画像一覧の取得
public static void GetImageList(SeriesKey key, out List <ImageTag> tags)
{
tags = new List <ImageTag>();
using (var db = new TryDbConnection(settings))
{
if (!key.IsImage)
{
using (var cmd = db.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("SELECT SOPInstanceUID,InstanceNumber,SliceThickness,ImagePositionPatient,ImageOrientationPatient,SliceLocation,Rows,Columns,PixelSpacing,WindowCenter,WindowWidth,StorageID");
sb.Append(" FROM T_Image");
sb.Append(" WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1");
cmd.CommandText = sb.ToString();
cmd.Add(key.StudyInstanceUID);
cmd.Add(key.SeriesInstanceUID);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var imkey = new ImageKey()
{
StudyInstanceUID = key.StudyInstanceUID,
SeriesInstanceUID = key.SeriesInstanceUID,
SOPInstanceUID = (string)dr["SOPInstanceUID"],
StorageID = (string)dr["StorageID"]
};
var tag = new ImageTag();
tag.ImageKey = ConvertUtil.Serialize(imkey);
tag.InstanceNumber = (long)dr["InstanceNumber"];
tag.SliceThickness = (string)dr["SliceThickness"];
tag.ImagePositionPatient = (string)dr["ImagePositionPatient"];
tag.ImageOrientationPatient = (string)dr["ImageOrientationPatient"];
tag.SliceLocation = (string)dr["SliceLocation"];
tag.Rows = (int)dr["Rows"];
tag.Columns = (int)dr["Columns"];
tag.PixelSpacing = (string)dr["PixelSpacing"];
tag.WindowCenter = (string)dr["WindowCenter"];
tag.WindowWidth = (string)dr["WindowWidth"];
//ソート用
tag.SOPInstanceUID = (string)dr["SOPInstanceUID"];
tags.Add(tag);
}
}
}
//ソート
tags.Sort(new ImageTagComparer());
}
else
{
using (var cmd = db.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("SELECT NumberOfFrames,SliceThickness,ImagePositionPatient,ImageOrientationPatient,SliceLocation,Rows,Columns,PixelSpacing,WindowCenter,WindowWidth,StorageID");
sb.Append(" FROM T_Image");
sb.Append(" WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1 AND SOPInstanceUID=@2");
cmd.CommandText = sb.ToString();
cmd.Add(key.StudyInstanceUID);
cmd.Add(key.SeriesInstanceUID);
cmd.Add(key.SOPInstanceUID);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
int frames = (int)dr["NumberOfFrames"];
for (int i = 0; i < frames; i++)
{
var imkey = new ImageKey()
{
StudyInstanceUID = key.StudyInstanceUID,
SeriesInstanceUID = key.SeriesInstanceUID,
SOPInstanceUID = key.SOPInstanceUID,
FrameNumber = i,
StorageID = (string)dr["StorageID"]
};
var tag = new ImageTag();
tag.ImageKey = ConvertUtil.Serialize(imkey);
tag.InstanceNumber = i;
tag.SliceThickness = (string)dr["SliceThickness"];
tag.ImagePositionPatient = (string)dr["ImagePositionPatient"];
tag.ImageOrientationPatient = (string)dr["ImageOrientationPatient"];
tag.SliceLocation = (string)dr["SliceLocation"];
tag.Rows = (int)dr["Rows"];
tag.Columns = (int)dr["Columns"];
tag.PixelSpacing = (string)dr["PixelSpacing"];
tag.WindowCenter = (string)dr["WindowCenter"];
tag.WindowWidth = (string)dr["WindowWidth"];
tag.NumberOfFrames = frames;
tags.Add(tag);
}
}
}
}
}
}
}
//スタディの取得 (URLコール用)
public static bool GetStudyKey(FindParam prm, out string patientid, out List <string> studykey)
{
patientid = "";
studykey = new List <string>();
using (var db = new TryDbConnection(LCL.settings))
{
var studyQuery = new RBStudyQuery();
studyQuery.is_with_both_exist = AppUtil.rsNas;
studyQuery.max_no_of_replay = AppUtil.rsMax;
if (string.IsNullOrEmpty(prm.PatientID) == false)
{
studyQuery.is_filter_on = 1;
studyQuery.is_pat_id_on = 1;
studyQuery.comp_mode_of_pat_id = AppUtil.cmPatientID;
studyQuery.PatientID = prm.PatientID;
}
if (string.IsNullOrEmpty(prm.PatientName) == false)
{
studyQuery.is_filter_on = 1;
studyQuery.is_pat_name_on = 1;
studyQuery.comp_mode_of_pat_name = AppUtil.cmPatientName;
studyQuery.PatientName = prm.PatientName;
}
if (string.IsNullOrEmpty(prm.AccessionNumber) == false)
{
studyQuery.is_filter_on = 1;
studyQuery.is_acc_no_on = 1;
studyQuery.comp_mode_of_acc_no = AppUtil.cmAccessionNumber;
studyQuery.AccNo = prm.AccessionNumber;
}
if (string.IsNullOrEmpty(prm.Modality) == false)
{
studyQuery.is_filter_on = 1;
studyQuery.is_modality_on = 1;
studyQuery.comp_mode_of_modality = AppUtil.cmModality;
studyQuery.Modality = prm.Modality.Replace(',', '|');
}
if (string.IsNullOrEmpty(prm.StudyDateFrom) == false && string.IsNullOrEmpty(prm.StudyDateTo) == false)
{
studyQuery.is_filter_on = 1;
studyQuery.is_st_date_on = 1;
studyQuery.StudyDate = string.Format("{0}-{1}", prm.StudyDateFrom, prm.StudyDateTo);
}
else if (string.IsNullOrEmpty(prm.StudyDateFrom) == false)
{
studyQuery.is_filter_on = 1;
studyQuery.is_st_date_on = 1;
studyQuery.StudyDate = string.Format("{0}-{1}", prm.StudyDateFrom, DateTime.Now.ToString("yyyyMMdd"));
}
else if (string.IsNullOrEmpty(prm.StudyDateTo) == false)
{
studyQuery.is_filter_on = 1;
studyQuery.is_st_date_on = 1;
studyQuery.StudyDate = string.Format("{0}-{1}", "19700101", prm.StudyDateTo);
}
List <RBStudy> studyList;
if (!GetStudyList(studyQuery, out studyList))
{
return(false);
}
//ソート
studyList.Sort((x, y) =>
{
int c = (y.StudyDate + y.StudyTime).CompareTo(x.StudyDate + x.StudyTime);
if (c == 0)
{
return(x.StudyUID.CompareTo(y.StudyUID));
}
else
{
return(c);
}
});
foreach (var study in studyList)
{
int nasno = -1;
if (!int.TryParse(study.NASHostName, out nasno))
{
LogUtil.Error1("NASHostName={0}", study.NASHostName);
continue;
}
var key = new StudyKey()
{
StudyInstanceUID = study.StudyUID,
StorageID = nasno.ToString()
};
studykey.Add(ConvertUtil.Serialize(key));
if (studykey.Count == 1)
{
patientid = study.PatID;
}
else
{
if (patientid != study.PatID)
{
patientid = null;
studykey = null;
return(false);
}
}
}
}
return(true);
}
public static void LoginUrl(string loginID, out string SID)
{
SID = null;
var now = DateTime.Now;
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT PASSWORD FROM UserTbl WHERE USER_ID=@0";
cmd.Add(loginID);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
}
else
{
return;
}
}
}
}
using (var db = new TryDbConnection(LCL.settings))
{
db.BeginTransaction();
try
{
string sid = null;
lock (typeof(LCL))
{
var len = new Random(Environment.TickCount).Next(8, 16);
sid = System.Web.Security.Membership.GeneratePassword(len, 0);
}
//有効期限の作成
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "INSERT INTO M_Login(SID,GroupID,UserID,StorageID,TimeLimit) VALUES(@0,@1,@2,@3,@4)";
cmd.Add(sid);
cmd.Add("");
cmd.Add(loginID);
cmd.Add("");
cmd.Add(now.AddHours(AppUtil.TimeLimit));
cmd.ExecuteNonQuery();
}
db.Commit();
SID = sid;
}
catch (Exception ex)
{
db.Rollback();
LogUtil.Error(ex.ToString());
}
}
}
//スタディ一覧の取得 (過去検査)
public static void GetStudyList_Kako(string patientid, StudyKey key, out List <StudyTag> tags)
{
tags = new List <StudyTag>();
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("SELECT StudyInstanceUID,StudyDate,StudyTime,AccessionNumber,Modality,StudyDescription,T_Study.PatientName,T_Study.PatientID,PatientBirthDate,PatientSex,PatientAge,BodyPartExamined,Comment,Keyword,NumberOfImages,T_Patient.PatientName PatientName2");
sb.Append(" FROM T_Study LEFT JOIN T_Patient ON T_Study.PatientID=T_Patient.PatientID");
if (patientid.Trim() == "")
{
sb.Append(" WHERE StudyInstanceUID=" + cmd.Add(key.StudyInstanceUID).ParameterName);
}
else
{
sb.Append(" WHERE T_Study.PatientID=" + cmd.Add(patientid).ParameterName);
}
sb.Append(" AND NumberOfImages>0");
sb.Append(" ORDER BY StudyDate DESC,StudyTime DESC,StudyInstanceUID");
cmd.CommandText = sb.ToString();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var stkey = new StudyKey()
{
StudyInstanceUID = (string)dr["StudyInstanceUID"]
};
var tag = new StudyTag();
tag.StudyKey = ConvertUtil.Serialize(stkey);
if (dr["StudyDate"] != DBNull.Value)
{
tag.StudyDate = (string)dr["StudyDate"];
}
if (dr["StudyTime"] != DBNull.Value)
{
tag.StudyTime = (string)dr["StudyTime"];
}
if (dr["AccessionNumber"] != DBNull.Value)
{
tag.AccessionNumber = (string)dr["AccessionNumber"];
}
if (dr["Modality"] != DBNull.Value)
{
tag.Modality = (string)dr["Modality"];
}
if (dr["StudyDescription"] != DBNull.Value)
{
tag.StudyDescription = (string)dr["StudyDescription"];
}
if (dr["PatientName"] != DBNull.Value)
{
tag.PatientName = (string)dr["PatientName"];
}
if (dr["PatientName2"] != DBNull.Value)
{
tag.PatientName = (string)dr["PatientName2"];
}
if (AppUtil.HideData == "1")
{
tag.PatientName = "";
}
if (dr["PatientID"] != DBNull.Value)
{
tag.PatientID = (string)dr["PatientID"];
}
if (dr["PatientBirthDate"] != DBNull.Value)
{
tag.PatientBirthDate = (string)dr["PatientBirthDate"];
}
if (dr["PatientSex"] != DBNull.Value)
{
tag.PatientSex = (string)dr["PatientSex"];
}
if (dr["PatientAge"] != DBNull.Value)
{
tag.PatientAge = (string)dr["PatientAge"];
}
if (dr["BodyPartExamined"] != DBNull.Value)
{
tag.BodyPartExamined = (string)dr["BodyPartExamined"];
}
if (dr["Comment"] != DBNull.Value)
{
tag.Comment = (string)dr["Comment"];
}
if (dr["Keyword"] != DBNull.Value)
{
tag.Keyword = (string)dr["Keyword"];
}
if (dr["NumberOfImages"] != DBNull.Value)
{
tag.NumberOfImages = (int)dr["NumberOfImages"];
}
//メモ有無
using (var cmd2 = db.CreateCommand())
{
cmd2.CommandText = "SELECT COUNT(*) cnt FROM T_StudyMemo WHERE StudyInstanceUID=@0";
cmd2.Add((string)dr["StudyInstanceUID"]);
using (var dr2 = cmd2.ExecuteReader())
{
if (dr2.Read())
{
tag.StudyMemoUmu = Convert.ToInt32(dr2["cnt"]);
}
}
}
tags.Add(tag);
}
}
}
}
//ソート不要
//tags.Sort(new StudyTagComparer());
}
//スタディ一覧の取得
public static void GetStudyList(FindParam prm, out List <StudyTag> tags, out int count)
{
tags = new List <StudyTag>();
count = 0;
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
var sbWhere = new StringBuilder();
sbWhere.Append(" WHERE 0=0");
if (prm.PatientID != null && prm.PatientID.Length > 0)
{
sbWhere.Append(" AND T_Study.PatientID ILIKE " + cmd.Add(prm.PatientID.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
if (prm.PatientName != null && prm.PatientName.Length > 0)
{
sbWhere.Append(" AND T_Study.PatientName ILIKE " + cmd.Add(prm.PatientName.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
if (prm.AccessionNumber != null && prm.AccessionNumber.Length > 0)
{
sbWhere.Append(" AND AccessionNumber ILIKE " + cmd.Add(prm.AccessionNumber.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
if (prm.Modality != null && prm.Modality.Length > 0)
{
int idx = 0;
foreach (var mod in prm.Modality.Split(' '))
{
if (idx == 0)
{
sbWhere.Append(" AND (Modality ILIKE " + cmd.Add("%" + mod + "%").ParameterName);
}
else
{
sbWhere.Append(" OR Modality ILIKE " + cmd.Add("%" + mod + "%").ParameterName);
}
idx++;
}
sbWhere.Append(")");
}
if (prm.StudyDateFrom != null && prm.StudyDateFrom.Length > 0)
{
sbWhere.Append(" AND StudyDate>=" + cmd.Add(prm.StudyDateFrom).ParameterName);
}
if (prm.StudyDateTo != null && prm.StudyDateTo.Length > 0)
{
sbWhere.Append(" AND StudyDate<=" + cmd.Add(prm.StudyDateTo).ParameterName);
}
if (prm.Comment != null && prm.Comment.Length > 0)
{
sbWhere.Append(" AND Comment ILIKE " + cmd.Add(prm.Comment.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
if (prm.Keyword != null && prm.Keyword.Length > 0)
{
sbWhere.Append(" AND Keyword ILIKE " + cmd.Add(prm.Keyword.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
sbWhere.Append(" AND NumberOfImages>0");
string top = "";
if (AppUtil.MaxStudyList > 0)
{
top = string.Format(" LIMIT {0}", AppUtil.MaxStudyList);
}
var sb = new StringBuilder();
sb.Append("SELECT COUNT(*) cnt FROM T_Study");
sb.Append(sbWhere.ToString() + ";");
sb.Append("SELECT StudyInstanceUID,StudyDate,StudyTime,AccessionNumber,Modality,StudyDescription,T_Study.PatientName,T_Study.PatientID,PatientBirthDate,PatientSex,PatientAge,BodyPartExamined,Comment,Keyword,NumberOfImages,T_Patient.PatientName PatientName2");
sb.Append(" FROM T_Study LEFT JOIN T_Patient ON T_Study.PatientID=T_Patient.PatientID");
sb.Append(sbWhere.ToString());
sb.Append(" ORDER BY StudyDate DESC,StudyTime DESC,StudyInstanceUID");
sb.Append(top);
cmd.CommandText = sb.ToString();
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
count = Convert.ToInt32(dr["cnt"]);
if (count > 0)
{
dr.NextResult();
while (dr.Read())
{
var stkey = new StudyKey()
{
StudyInstanceUID = (string)dr["StudyInstanceUID"]
};
var tag = new StudyTag();
tag.StudyKey = ConvertUtil.Serialize(stkey);
if (dr["StudyDate"] != DBNull.Value)
{
tag.StudyDate = (string)dr["StudyDate"];
}
if (dr["StudyTime"] != DBNull.Value)
{
tag.StudyTime = (string)dr["StudyTime"];
}
if (dr["AccessionNumber"] != DBNull.Value)
{
tag.AccessionNumber = (string)dr["AccessionNumber"];
}
if (dr["Modality"] != DBNull.Value)
{
tag.Modality = (string)dr["Modality"];
}
if (dr["StudyDescription"] != DBNull.Value)
{
tag.StudyDescription = (string)dr["StudyDescription"];
}
if (dr["PatientName"] != DBNull.Value)
{
tag.PatientName = (string)dr["PatientName"];
}
if (dr["PatientName2"] != DBNull.Value)
{
tag.PatientName = (string)dr["PatientName2"];
}
if (AppUtil.HideData == "1")
{
tag.PatientName = "";
}
if (dr["PatientID"] != DBNull.Value)
{
tag.PatientID = (string)dr["PatientID"];
}
if (dr["PatientBirthDate"] != DBNull.Value)
{
tag.PatientBirthDate = (string)dr["PatientBirthDate"];
}
if (dr["PatientSex"] != DBNull.Value)
{
tag.PatientSex = (string)dr["PatientSex"];
}
if (dr["PatientAge"] != DBNull.Value)
{
tag.PatientAge = (string)dr["PatientAge"];
}
if (dr["BodyPartExamined"] != DBNull.Value)
{
tag.BodyPartExamined = (string)dr["BodyPartExamined"];
}
if (dr["Comment"] != DBNull.Value)
{
tag.Comment = (string)dr["Comment"];
}
if (dr["Keyword"] != DBNull.Value)
{
tag.Keyword = (string)dr["Keyword"];
}
if (dr["NumberOfImages"] != DBNull.Value)
{
tag.NumberOfImages = (int)dr["NumberOfImages"];
}
//メモ有無
using (var cmd2 = db.CreateCommand())
{
cmd2.CommandText = "SELECT COUNT(*) cnt FROM T_StudyMemo WHERE StudyInstanceUID=@0";
cmd2.Add((string)dr["StudyInstanceUID"]);
using (var dr2 = cmd2.ExecuteReader())
{
if (dr2.Read())
{
tag.StudyMemoUmu = Convert.ToInt32(dr2["cnt"]);
}
}
}
tags.Add(tag);
}
}
}
}
}
}
//ソートしない;
}
//検査メモの設定
public static bool SetStudyMemo(StudyKey key, StudyMemoItem item)
{
for (int i = 0; i < AppUtil.RetryCount; i++)
{
string err = "";
var now = DateTime.Now;
using (var db = new TryDbConnection(settings))
{
db.BeginTransaction();
try
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "DELETE FROM T_StudyMemo WHERE StudyInstanceUID=@0";
cmd.Add(key.StudyInstanceUID);
cmd.ExecuteNonQuery();
}
if (item.Memo.Trim() != "")
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "INSERT INTO T_StudyMemo(StudyInstanceUID,MemoDate,Memo,UserName) VALUES(@0,@1,@2,@3)";
cmd.Add(key.StudyInstanceUID);
cmd.Add(now);
cmd.Add(item.Memo);
cmd.Add(item.UserName);
cmd.ExecuteNonQuery();
}
}
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "INSERT INTO T_StudyMemoHistory(StudyInstanceUID,MemoDate,Memo,UserName) VALUES(@0,@1,@2,@3)";
cmd.Add(key.StudyInstanceUID);
cmd.Add(now);
cmd.Add(item.Memo);
cmd.Add(item.UserName);
cmd.ExecuteNonQuery();
}
db.Commit();
return(true);
}
catch (Exception ex)
{
db.Rollback();
err = ex.ToString();
}
}
//リトライ
if (i < AppUtil.RetryCount - 1)
{
System.Threading.Thread.Sleep(AppUtil.SleepTime);
}
else
{
LogUtil.Error(err);
}
}
return(false);
}
//GSPS一覧の取得
public static void GetGspsList(SeriesKey key, out List <GSPSItem> items)
{
items = new List <GSPSItem>();
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
if (!key.IsImage)
{
cmd.CommandText = "SELECT DISTINCT ContentLabel,ContentDescription,PresentationCreationDate,PresentationCreationTime,ContentCreatorName FROM T_GSPS_R WHERE StudyInstanceUID=@0 AND ReferencedSeriesInstanceUID=@1 ORDER BY PresentationCreationDate DESC,PresentationCreationTime DESC";
cmd.Add(key.StudyInstanceUID);
cmd.Add(key.SeriesInstanceUID);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var gspskey = new GSPSKey()
{
StudyInstanceUID = key.StudyInstanceUID,
ReferencedSeriesInstanceUID = key.SeriesInstanceUID,
ReferencedSOPInstanceUID = "",
ContentLabel = (string)dr["ContentLabel"],
};
var item = new GSPSItem();
item.GSPSKey = ConvertUtil.Serialize(gspskey);
item.ContentLabel = (string)dr["ContentLabel"];
item.ContentDescription = (string)dr["ContentDescription"];
item.PresentationCreationDate = (string)dr["PresentationCreationDate"];
item.PresentationCreationTime = (string)dr["PresentationCreationTime"];
item.ContentCreatorName = (string)dr["ContentCreatorName"];
items.Add(item);
}
}
}
else
{
cmd.CommandText = "SELECT DISTINCT ContentLabel,ContentDescription,PresentationCreationDate,PresentationCreationTime,ContentCreatorName FROM T_GSPS_R WHERE StudyInstanceUID=@0 AND ReferencedSeriesInstanceUID=@1 AND ReferencedSOPInstanceUID=@2 ORDER BY PresentationCreationDate DESC,PresentationCreationTime DESC";
cmd.Add(key.StudyInstanceUID);
cmd.Add(key.SeriesInstanceUID);
cmd.Add(key.SOPInstanceUID);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var gspskey = new GSPSKey()
{
StudyInstanceUID = key.StudyInstanceUID,
ReferencedSeriesInstanceUID = key.SeriesInstanceUID,
ReferencedSOPInstanceUID = key.SOPInstanceUID,
ContentLabel = (string)dr["ContentLabel"],
};
var item = new GSPSItem();
item.GSPSKey = ConvertUtil.Serialize(gspskey);
item.ContentLabel = (string)dr["ContentLabel"];
item.ContentDescription = (string)dr["ContentDescription"];
item.PresentationCreationDate = (string)dr["PresentationCreationDate"];
item.PresentationCreationTime = (string)dr["PresentationCreationTime"];
item.ContentCreatorName = (string)dr["ContentCreatorName"];
items.Add(item);
}
}
}
}
}
}
public static bool DicomToDB(List <Dictionary <string, string> > data)
{
if (data == null || data.Count == 0)
{
return(false);
}
var now = DateTime.Now;
bool studyInsert = true;
foreach (var sop in data)
{
string studyUid = sop[DcmTag.StudyInstanceUID.Name];
string seriesUid = sop[DcmTag.SeriesInstanceUID.Name];
string sopUid = sop[DcmTag.SOPInstanceUID.Name];
for (int i = 0; i < AppUtil.RetryCount; i++)
{
using (var db = new TryDbConnection(settings))
{
db.BeginTransaction();
try
{
int cnt;
if (studyInsert)
{
//STUDY
do
{
LogUtil.Info3("DB [{0}][{1}][{2}]", studyUid, seriesUid, sopUid);
cnt = 0;
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) FROM T_Study WHERE StudyInstanceUID=@0";
cmd.Add(studyUid);
cnt = Convert.ToInt32(cmd.ExecuteScalar());
}
if (cnt > 0 && Array.IndexOf(AppUtil.SkipModality, sop[DcmTag.Modality.Name]) >= 0)
{
break;
}
using (var cmd = db.CreateCommand())
{
var sb = new StringBuilder();
if (cnt == 0)
{
sb.Append("INSERT INTO T_Study(StudyInstanceUID,SpecificCharacterSet,StudyDate,StudyTime,AccessionNumber,Modality,InstitutionName,ReferringPhysicianName,StudyDescription,OperatorsName,PatientName,PatientID,PatientBirthDate,PatientSex,PatientAge,BodyPartExamined,StudyID,RequestingPhysician,RequestingService,AETitle,Comment,UploadUser)");
sb.Append(" VALUES(@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21)");
}
else
{
sb.Append("UPDATE T_Study SET SpecificCharacterSet=@1,StudyDate=@2,StudyTime=@3,AccessionNumber=@4,Modality=@5,InstitutionName=@6,ReferringPhysicianName=@7,StudyDescription=@8,OperatorsName=@9,PatientName=@10,PatientID=@11,PatientBirthDate=@12,PatientSex=@13,PatientAge=@14,BodyPartExamined=@15,StudyID=@16,RequestingPhysician=@17,RequestingService=@18,AETitle=@19,Comment=@20,UploadUser=@21,UploadDate=@22");
sb.Append(" WHERE StudyInstanceUID=@0");
}
cmd.CommandText = sb.ToString();
cmd.Add(studyUid);
cmd.Add(sop[DcmTag.SpecificCharacterSet.Name]);
cmd.Add(sop[DcmTag.StudyDate.Name].Replace(".", ""));
cmd.Add(sop[DcmTag.StudyTime.Name]);
cmd.Add(sop[DcmTag.AccessionNumber.Name]);
cmd.Add(sop[DcmTag.Modality.Name]);
cmd.Add(sop[DcmTag.InstitutionName.Name]);
cmd.Add(sop[DcmTag.ReferringPhysicianName.Name]);
cmd.Add(sop[DcmTag.StudyDescription.Name].Substring2(LEN_Study_StudyDescription));
cmd.Add(sop[DcmTag.OperatorsName.Name]);
cmd.Add(sop[DcmTag.PatientName.Name]);
cmd.Add(sop[DcmTag.PatientID.Name]);
cmd.Add(sop[DcmTag.PatientBirthDate.Name].Replace(".", "").Substring2(LEN_Study_PatientBirthDate));
cmd.Add(sop[DcmTag.PatientSex.Name]);
cmd.Add(sop[DcmTag.PatientAge.Name].Substring2(LEN_Study_PatientAge));
cmd.Add(sop[DcmTag.BodyPartExamined.Name]);
cmd.Add(sop[DcmTag.StudyID.Name]);
cmd.Add(sop[DcmTag.RequestingPhysician.Name]);
cmd.Add(sop[DcmTag.RequestingService.Name]);
string aetitle = "";
if (sop.ContainsKey("AETitle"))
{
aetitle = sop["AETitle"].Substring2(LEN_Study_AETitle);
}
cmd.Add(aetitle);
string comment = "";
if (sop.ContainsKey("Comment"))
{
comment = sop["Comment"].Substring2(LEN_Study_Comment);
}
cmd.Add(comment);
cmd.Add(sop["UserName"]);
cmd.Add(now);
cmd.ExecuteNonQuery();
}
LogUtil.Debug("DB_STUDY end");
} while (false);
//SERIES
cnt = 0;
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) FROM T_Series WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1";
cmd.Add(studyUid);
cmd.Add(seriesUid);
cnt = Convert.ToInt32(cmd.ExecuteScalar());
}
using (var cmd = db.CreateCommand())
{
var sb = new StringBuilder();
if (cnt == 0)
{
sb.Append("INSERT INTO T_Series(StudyInstanceUID,SeriesInstanceUID,SOPInstanceUID,Modality,SeriesDescription,BodyPartExamined,SeriesNumber,StorageID)");
sb.Append(" VALUES(@0,@1,@2,@3,@4,@5,@6,@7)");
}
else
{
sb.Append("UPDATE T_Series SET SOPInstanceUID=@2,Modality=@3,SeriesDescription=@4,BodyPartExamined=@5,SeriesNumber=@6,StorageID=@7");
sb.Append(" WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1");
}
cmd.CommandText = sb.ToString();
cmd.Add(studyUid);
cmd.Add(seriesUid);
cmd.Add(sop[DcmTag.SOPInstanceUID.Name]);
cmd.Add(sop[DcmTag.Modality.Name]);
cmd.Add(sop[DcmTag.SeriesDescription.Name].Substring2(LEN_Series_SeriesDescription));
cmd.Add(sop[DcmTag.BodyPartExamined.Name].Substring2(LEN_Series_BodyPartExamined));
cmd.Add(sop[DcmTag.SeriesNumber.Name] == "" ? "0" : sop[DcmTag.SeriesNumber.Name]);
cmd.Add(sop["StorageID"]);
cmd.ExecuteNonQuery();
}
LogUtil.Debug("DB_SERIES end");
}
//IMAGE
cnt = 0;
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "DELETE FROM T_Image WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1 AND SOPInstanceUID=@2";
cmd.Add(studyUid);
cmd.Add(seriesUid);
cmd.Add(sopUid);
cnt = cmd.ExecuteNonQuery();
}
if (cnt > 0)
{
LogUtil.Debug1("DB_SOP 削除[{0}]", sopUid);
}
using (var cmd = db.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("INSERT INTO T_Image(StudyInstanceUID,SeriesInstanceUID,SOPInstanceUID,InstanceNumber,SliceThickness,ImagePositionPatient,ImageOrientationPatient,SliceLocation,PhotometricInterpretation,NumberOfFrames,Rows,Columns,PixelSpacing,WindowCenter,WindowWidth,RescaleIntercept,RescaleSlope,StorageID,FileSize,CompFileSize)");
sb.Append(" VALUES(@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19)");
cmd.CommandText = sb.ToString();
cmd.Add(studyUid);
cmd.Add(seriesUid);
cmd.Add(sopUid);
cmd.Add(sop[DcmTag.InstanceNumber.Name] == "" ? "0" : sop[DcmTag.InstanceNumber.Name]);
cmd.Add(sop[DcmTag.SliceThickness.Name]);
cmd.Add(sop[DcmTag.ImagePositionPatient.Name]);
cmd.Add(sop[DcmTag.ImageOrientationPatient.Name]);
cmd.Add(sop[DcmTag.SliceLocation.Name]);
cmd.Add(sop[DcmTag.PhotometricInterpretation.Name]);
cmd.Add(sop[DcmTag.NumberOfFrames.Name] == "" ? "1" : sop[DcmTag.NumberOfFrames.Name]);
cmd.Add(sop[DcmTag.Rows.Name]);
cmd.Add(sop[DcmTag.Columns.Name]);
cmd.Add(sop[DcmTag.PixelSpacing.Name]);
cmd.Add(sop[DcmTag.WindowCenter.Name].Substring2(LEN_Image_WindowCenter));
cmd.Add(sop[DcmTag.WindowWidth.Name].Substring2(LEN_Image_WindowWidth));
cmd.Add(sop[DcmTag.RescaleIntercept.Name]);
cmd.Add(sop[DcmTag.RescaleSlope.Name]);
cmd.Add(sop["StorageID"]);
int fileSize = 0;
if (!Int32.TryParse(sop["FileSize"], out fileSize))
{
fileSize = 0;
}
cmd.Add(fileSize);
int compFileSize = 0;
if (sop.ContainsKey("CompFileSize") && !Int32.TryParse(sop["CompFileSize"], out compFileSize))
{
compFileSize = 0;
}
cmd.Add(compFileSize);
cmd.ExecuteNonQuery();
}
db.Commit();
if (studyInsert && sop[DcmTag.Rows.Name] != "" && Array.IndexOf(AppUtil.SkipModality, sop[DcmTag.Modality.Name]) < 0)
{
studyInsert = false;
}
break;
}
catch (Exception ex)
{
db.Rollback();
LogUtil.Warn(ex);
}
}
//リトライ
if (i < AppUtil.RetryCount - 1)
{
System.Threading.Thread.Sleep(AppUtil.SleepTime);
LogUtil.Info1("RETRY:{0}", i + 1);
}
else
{
LogUtil.Error3("DB登録:失敗 [{0}][{1}][{2}]", studyUid, seriesUid, sopUid);
return(false);
}
}
}
LogUtil.Debug("DB_SOP end");
//T_SERIESの更新
{
var sop = data[0];
string studyUid = sop[DcmTag.StudyInstanceUID.Name];
string seriesUid = sop[DcmTag.SeriesInstanceUID.Name];
for (int i = 0; i < AppUtil.RetryCount; i++)
{
using (var db = new TryDbConnection(settings))
{
var sopUid = new List <string>();
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT SOPInstanceUID FROM T_Image WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1 AND Rows>0 ORDER BY InstanceNumber";
cmd.Add(studyUid);
cmd.Add(seriesUid);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
sopUid.Add((string)dr["SOPInstanceUID"]);
}
}
}
if (sopUid.Count == 0)
{
break;
}
//サムネイル位置
string mod = sop[DcmTag.Modality.Name];
string thumbPos;
if (!AppUtil.ModalityThumbPos.TryGetValue(mod, out thumbPos))
{
thumbPos = AppUtil.ThumbPos;
}
int pos = 0;
switch (thumbPos)
{
case "0":
pos = 0;
break;
case "1":
pos = sopUid.Count / 2;
break;
case "2":
pos = sopUid.Count - 1;
break;
default:
pos = 0;
break;
}
//イメージ枚数、フレーム枚数
int imageCnt = 0;
int frameCnt = 0;
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) icnt,SUM(NumberOfFrames) fcnt FROM T_Image WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1 AND Rows>0";
cmd.Add(studyUid);
cmd.Add(seriesUid);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
imageCnt = Convert.ToInt32(dr["icnt"]);
frameCnt = Convert.ToInt32(dr["fcnt"]);
}
}
}
//更新
db.BeginTransaction();
try
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "UPDATE T_Series SET SOPInstanceUID=@2,NumberOfImages=@3,NumberOfFrames=@4,StorageID=@5 WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1";
cmd.Add(studyUid);
cmd.Add(seriesUid);
cmd.Add(sopUid[pos]);
cmd.Add(imageCnt);
cmd.Add(frameCnt);
cmd.Add(sop["StorageID"]);
cmd.ExecuteNonQuery();
}
db.Commit();
LogUtil.Debug("DB_SERIES_2 end");
break;
}
catch (Exception ex)
{
db.Rollback();
LogUtil.Warn1("DB_SERIES_2:{0}", ex.ToString());
}
}
//リトライ
if (i < AppUtil.RetryCount - 1)
{
System.Threading.Thread.Sleep(AppUtil.SleepTime);
LogUtil.Info1("RETRY:{0}", i + 1);
}
else
{
LogUtil.Error3("SERIES登録:失敗 STUDY[{0}] SERIES[{1}] SOP[{2}]", studyUid, seriesUid, sop[DcmTag.SOPInstanceUID.Name]);
return(false);
}
}
}
return(true);
}
//スタディの取得 (URLコール用)
public static bool GetStudyKey(FindParam prm, out string patientid, out List <string> studykey)
{
patientid = "";
studykey = new List <string>();
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
var sbWhere = new StringBuilder();
sbWhere.Append(" WHERE 0=0");
if (prm.PatientID != null && prm.PatientID.Length > 0)
{
sbWhere.Append(" AND T_Study.PatientID ILIKE " + cmd.Add(prm.PatientID.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
if (prm.PatientName != null && prm.PatientName.Length > 0)
{
sbWhere.Append(" AND T_Study.PatientName ILIKE " + cmd.Add(prm.PatientName.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
if (prm.AccessionNumber != null && prm.AccessionNumber.Length > 0)
{
sbWhere.Append(" AND AccessionNumber ILIKE " + cmd.Add(prm.AccessionNumber.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
if (prm.Modality != null && prm.Modality.Length > 0)
{
var mods = new StringBuilder();
foreach (var mod in prm.Modality.Split(' '))
{
if (mod == "")
{
continue;
}
mods.Append(cmd.Add(mod.ToUpper()).ParameterName);
mods.Append(',');
}
sbWhere.Append(" AND Modality IN (" + mods.ToString().TrimEnd(',') + ")");
}
if (prm.StudyDateFrom != null && prm.StudyDateFrom.Length > 0)
{
sbWhere.Append(" AND StudyDate>=" + cmd.Add(prm.StudyDateFrom).ParameterName);
}
if (prm.StudyDateTo != null && prm.StudyDateTo.Length > 0)
{
sbWhere.Append(" AND StudyDate<=" + cmd.Add(prm.StudyDateTo).ParameterName);
}
if (prm.Comment != null && prm.Comment.Length > 0)
{
sbWhere.Append(" AND Comment ILIKE " + cmd.Add(prm.Comment.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
if (prm.Keyword != null && prm.Keyword.Length > 0)
{
sbWhere.Append(" AND Keyword ILIKE " + cmd.Add(prm.Keyword.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace('*', '%').Replace('?', '_')).ParameterName);
}
sbWhere.Append(" AND NumberOfImages>0");
var sb = new StringBuilder();
sb.Append("SELECT StudyInstanceUID,PatientID");
sb.Append(" FROM T_Study");
sb.Append(sbWhere.ToString());
sb.Append(" ORDER BY StudyDate DESC,StudyTime DESC,StudyInstanceUID");
cmd.CommandText = sb.ToString();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var key = new StudyKey()
{
StudyInstanceUID = (string)dr["StudyInstanceUID"]
};
studykey.Add(ConvertUtil.Serialize(key));
string tmp = "";
if (dr["PatientID"] != DBNull.Value)
{
tmp = (string)dr["PatientID"];
}
if (studykey.Count == 1)
{
patientid = tmp;
}
else
{
if (patientid != tmp)
{
patientid = null;
studykey = null;
return(false);
}
}
}
}
}
}
return(true);
}
//STUDYの更新
public static bool UpdateStudy(string StudyUid)
{
for (int i = 0; i < AppUtil.RetryCount; i++)
{
using (var db = new TryDbConnection(settings))
{
var modList = new List <string>();
var bodList = new List <string>();
int images = 0;
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT Modality,BodyPartExamined,NumberOfImages FROM T_Series WHERE StudyInstanceUID=@0 ORDER BY SeriesNumber";
cmd.Add(StudyUid);
using (var dr = cmd.ExecuteReader())
{
var modUQ = new List <string>();
var bodUQ = new List <string>();
while (dr.Read())
{
var mod = ((string)dr["Modality"]).Trim();
if (mod != "" && !modUQ.Contains(mod.ToUpper()))
{
modList.Add(mod);
modUQ.Add(mod.ToUpper());
}
var bod = ((string)dr["BodyPartExamined"]).Trim();
if (bod != "" && !bodUQ.Contains(bod.ToUpper()))
{
bodList.Add(bod);
bodUQ.Add(bod.ToUpper());
}
images += (int)dr["NumberOfImages"];
}
}
}
db.BeginTransaction();
try
{
using (var cmd = db.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("UPDATE T_Study SET NumberOfImages=" + cmd.Add(images).ParameterName);
if (modList.Count > 0)
{
sb.Append(",Modality=" + cmd.Add(modList.ToString('\\', LEN_Study_Modality)).ParameterName);
}
if (bodList.Count > 0)
{
sb.Append(",BodyPartExamined=" + cmd.Add(bodList.ToString('\\', LEN_Study_BodyPartExamined)).ParameterName);
}
sb.Append(" WHERE StudyInstanceUID=" + cmd.Add(StudyUid).ParameterName);
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
db.Commit();
return(true);
}
catch (Exception ex)
{
db.Rollback();
LogUtil.Warn(ex.ToString());
}
}
//リトライ
if (i < AppUtil.RetryCount - 1)
{
System.Threading.Thread.Sleep(AppUtil.SleepTime);
LogUtil.Info1("RETRY:{0}", i + 1);
}
else
{
LogUtil.Error("T_Study更新:失敗");
return(false);
}
}
return(false);
}
//シリーズ一覧の取得
public static void GetSeriesList(StudyKey key, out List <SeriesTag> tags)
{
tags = new List <SeriesTag>();
using (var db = new TryDbConnection(settings))
{
using (var cmd = db.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("SELECT SeriesInstanceUID,SOPInstanceUID,Modality,SeriesDescription,SeriesNumber,NumberOfImages,NumberOfFrames,StorageID");
sb.Append(" FROM T_Series");
sb.Append(" WHERE StudyInstanceUID=@0");
cmd.CommandText = sb.ToString();
cmd.Add(key.StudyInstanceUID);
//シリーズ
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
//無視するモダリティ
if (Array.IndexOf(AppUtil.SkipModality, (string)dr["Modality"]) >= 0)
{
continue;
}
string seriesUid = (string)dr["SeriesInstanceUID"];
int noi = (int)dr["NumberOfImages"];
int nof = (int)dr["NumberOfFrames"];
int cnt = 0;
if (nof > noi)
{
//ウォーターラインのチェック
using (var cmd2 = db.CreateCommand())
{
cmd2.CommandText = "SELECT COUNT(*) FROM T_Image WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1";
cmd2.Add(key.StudyInstanceUID);
cmd2.Add(seriesUid);
cnt = Convert.ToInt32(cmd2.ExecuteScalar());
}
}
//マルチフレームでない or 画像がない
if (cnt == 0)
{
var sekey = new SeriesKey()
{
StudyInstanceUID = key.StudyInstanceUID,
SeriesInstanceUID = seriesUid,
SOPInstanceUID = (string)dr["SOPInstanceUID"],
StorageID = (string)dr["StorageID"],
IsImage = false
};
var tag = new SeriesTag();
tag.SeriesKey = ConvertUtil.Serialize(sekey);
tag.Modality = (string)dr["Modality"];
tag.SeriesDescription = (string)dr["SeriesDescription"];
tag.SeriesNumber = (long)dr["SeriesNumber"];
tag.NumberOfImages = noi;
tag.NumberOfFrames = noi;
//ソート用
tag.SeriesInstanceUID = seriesUid;
//GSPS
using (var cmd3 = db.CreateCommand())
{
cmd3.CommandText = "SELECT COUNT(*) FROM T_GSPS_R WHERE StudyInstanceUID=@0 AND ReferencedSeriesInstanceUID=@1";
cmd3.Add(key.StudyInstanceUID);
cmd3.Add(seriesUid);
var gsps = Convert.ToInt32(cmd3.ExecuteScalar());
tag.IsGSPS = gsps > 0 ? true : false;
}
tags.Add(tag);
}
else
{
//マルチフレーム
using (var cmd2 = db.CreateCommand())
{
cmd2.CommandText = "SELECT SOPInstanceUID,InstanceNumber,NumberOfFrames,StorageID FROM T_Image WHERE StudyInstanceUID=@0 AND SeriesInstanceUID=@1";
cmd2.Add(key.StudyInstanceUID);
cmd2.Add(seriesUid);
using (var dr2 = cmd2.ExecuteReader())
{
while (dr2.Read())
{
var sekey = new SeriesKey()
{
StudyInstanceUID = key.StudyInstanceUID,
SeriesInstanceUID = seriesUid,
SOPInstanceUID = (string)dr2["SOPInstanceUID"],
StorageID = (string)dr2["StorageID"],
IsImage = true
};
var tag = new SeriesTag();
tag.SeriesKey = ConvertUtil.Serialize(sekey);
tag.Modality = (string)dr["Modality"];
tag.SeriesDescription = (string)dr["SeriesDescription"];
tag.SeriesNumber = (long)dr["SeriesNumber"];
tag.NumberOfImages = 1;
tag.NumberOfFrames = (int)dr2["NumberOfFrames"];
//ソート用
tag.SeriesInstanceUID = seriesUid;
tag.SOPInstanceUID = (string)dr2["SOPInstanceUID"];
tag.InstanceNumber = (long)dr2["InstanceNumber"];
//GSPS
using (var cmd3 = db.CreateCommand())
{
cmd3.CommandText = "SELECT COUNT(*) FROM T_GSPS_R WHERE StudyInstanceUID=@0 AND ReferencedSeriesInstanceUID=@1 AND ReferencedSOPInstanceUID=@2";
cmd3.Add(key.StudyInstanceUID);
cmd3.Add(seriesUid);
cmd3.Add((string)dr2["SOPInstanceUID"]);
var gsps = Convert.ToInt32(cmd3.ExecuteScalar());
tag.IsGSPS = gsps > 0 ? true : false;
}
tags.Add(tag);
}
}
}
}
}
}
}
}
//ソート
tags.Sort(new SeriesTagComparer());
}
//スタディ一覧の取得 (過去検査)
public static void GetStudyList_Kako(string patientid, StudyKey key, out List <StudyTag> tags)
{
tags = new List <StudyTag>();
using (var pc = new PacsComm())
{
var prm2 = new PacsComm.FindParam();
if (patientid.Trim() == "")
{
prm2.StudyInstanceUID = key.StudyInstanceUID;
}
else
{
prm2.PatientID = patientid;
}
DicomTagsResult ret = pc.CFindStudy(prm2);
if (!ret.IsSuccess)
{
LogUtil.Error("CFINDに失敗しました。");
return;
}
foreach (var dcmTag in ret.Tags)
{
var stkey = new StudyKey()
{
StudyInstanceUID = dcmTag.GetTagValue(DicomDic.Find("StudyInstanceUID").Tag),
IsPacsSearch = true
};
var tag = new StudyTag();
tag.StudyKey = ConvertUtil.Serialize(stkey);
tag.StudyDate = dcmTag.GetTagValue(DicomDic.Find("StudyDate").Tag);
tag.StudyTime = dcmTag.GetTagValue(DicomDic.Find("StudyTime").Tag);
tag.AccessionNumber = dcmTag.GetTagValue(DicomDic.Find("AccessionNumber").Tag);
tag.PatientName = dcmTag.GetTagValue(DicomDic.Find("PatientsName").Tag);
tag.PatientID = dcmTag.GetTagValue(DicomDic.Find("PatientID").Tag);
tag.Modality = dcmTag.GetTagValue(DicomDic.Find("ModalitiesInStudy").Tag);
tag.StudyDescription = dcmTag.GetTagValue(DicomDic.Find("StudyDescription").Tag);
tag.PatientBirthDate = dcmTag.GetTagValue(DicomDic.Find("PatientsBirthDate").Tag);
tag.PatientSex = dcmTag.GetTagValue(DicomDic.Find("PatientsSex").Tag);
tag.PatientAge = dcmTag.GetTagValue(DicomDic.Find("PatientsAge").Tag);
Int32.TryParse(dcmTag.GetTagValue(DicomDic.Find("NumberOfStudyRelatedInstances").Tag), out tag.NumberOfImages);
//メモ有無
using (var db = new TryDbConnection(LCL.settings))
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) cnt FROM T_StudyMemo WHERE StudyInstanceUID=@0";
cmd.Add(stkey.StudyInstanceUID);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
tag.StudyMemoUmu = Convert.ToInt32(dr["cnt"]);
}
}
}
}
//ソート用
tag.StudyInstanceUID = dcmTag.GetTagValue(DicomDic.Find("StudyInstanceUID").Tag);
tags.Add(tag);
}
}
//ソート
tags.Sort(new StudyTagComparer());
}
//スタディ一覧の取得 (過去検査)
public static void GetStudyList_Kako(string patientid, StudyKey key, out List <StudyTag> tags)
{
tags = new List <StudyTag>();
using (var db = new TryDbConnection(LCL.settings))
{
var studyQuery = new RBStudyQuery();
studyQuery.is_with_both_exist = AppUtil.rsNas;
studyQuery.max_no_of_replay = AppUtil.rsMax;
if (string.IsNullOrEmpty(patientid))
{
studyQuery.is_filter_on = 1;
studyQuery.is_study_id_on = 1;
studyQuery.comp_mode_of_study_id = 1;
studyQuery.StudyUID = key.StudyInstanceUID;
}
else
{
studyQuery.is_filter_on = 1;
studyQuery.is_pat_id_on = 1;
studyQuery.comp_mode_of_pat_id = 1;
studyQuery.PatientID = patientid;
}
List <RBStudy> studyList;
if (!GetStudyList(studyQuery, out studyList))
{
return;
}
var PatName = new Dictionary <string, string>();
foreach (var study in studyList)
{
int nasno = -1;
if (!int.TryParse(study.NASHostName, out nasno))
{
LogUtil.Error1("NASHostName={0}", study.NASHostName);
continue;
}
var stkey = new StudyKey()
{
StudyInstanceUID = study.StudyUID,
StorageID = nasno.ToString()
};
var tag = new StudyTag();
tag.StudyKey = ConvertUtil.Serialize(stkey);
tag.StudyDate = study.StudyDate;
tag.StudyTime = study.StudyTime;
tag.AccessionNumber = study.AccNo;
tag.Modality = study.Modality;
tag.StudyDescription = study.StudyDesc;
tag.PatientName = study.PatNameSJ != "" ? study.PatNameSJ : study.PatName;
tag.PatientID = study.PatID;
tag.PatientBirthDate = study.BirthDate;
tag.PatientSex = study.Sex;
tag.BodyPartExamined = study.BodyPart;
tag.NumberOfImages = (int)study.NoOfImg;
//患者名
if (PatName.ContainsKey(tag.PatientID))
{
if (PatName[tag.PatientID] != null)
{
tag.PatientName = PatName[tag.PatientID];
}
}
else
{
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT PatientName FROM T_Patient WHERE PatientID=@0";
cmd.Add(tag.PatientID);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
tag.PatientName = (string)dr["PatientName"];
PatName.Add(tag.PatientID, (string)dr["PatientName"]);
}
else
{
PatName.Add(tag.PatientID, null);
}
}
}
}
if (AppUtil.HideData == "1")
{
tag.PatientName = "";
}
//メモ有無
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) cnt FROM T_StudyMemo WHERE StudyInstanceUID=@0";
cmd.Add(stkey.StudyInstanceUID);
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
tag.StudyMemoUmu = Convert.ToInt32(dr["cnt"]);
}
}
}
//ソート用
tag.StudyInstanceUID = study.StudyUID;
tags.Add(tag);
}
}
//ソート
tags.Sort(new StudyTagComparer());
}
