/// <summary> /// 検査テーブルに取り込んだ検査情報を新規登録もしくは更新 /// </summary> /// <param name="patientId"></param> /// <param name="studyitems"></param> public static void ImportStudyInfo(string patientId, ValidatedStudyItems studyitems) { using (IDbConnection connection = CreateConnection()) { connection.Open(); using (IDbCommand cmd = connection.CreateCommand()) { using (var trn = connection.BeginTransaction()) { cmd.Transaction = trn; cmd.CommandText = @" SELECT COUNT(*) FROM dbo.StudyOrders WHERE OrderNumber = @OrderNumber;"; AddQueryParam(cmd, "OrderNumber", studyitems.OrderNumber); if ((int)cmd.ExecuteScalar() == 0) { //新規登録 InsertStudy(cmd, trn, patientId, studyitems); } else { //上書き機能 UpdateStudy(cmd, trn, studyitems); } } } } }
/// <summary> /// 検査テーブルに検査情報を登録 /// </summary> /// <param name="item"></param> /// <returns></returns> private ValidatedStudyItems RegisterStudy(CsvItem item) { ValidatedStudyItems studyItems = new ValidatedStudyItems(); studyItems.OrderNumber = int.Parse(item.OrderNumber); studyItems.ScheduledOn = DateTime.Parse(item.ScheduledOn); studyItems.ProcessingDivision = int.Parse(item.ProcessingDivision); studyItems.StudyTypeCode = item.StudyTypeCode; studyItems.StudyTypeName = item.StudyTypeName; studyItems.ShotItemSet = item.ShotItemSet; return(studyItems); }
/// <summary> /// 撮影項目登録 /// </summary> /// <param name="cmd"></param> /// <param name="studyitems"></param> private static void SaveShotItems(IDbCommand cmd, ValidatedStudyItems studyitems) { int n = 0; foreach (var shotitem in studyitems.ShotItemSet) { cmd.CommandText = $@" INSERT INTO dbo.ShotItems (OrderNumber, ShotItemCode, ShotItemName) VALUES (@OrderNumber{n}, @ShotItemCode{n}, @ShotItemName{n})"; AddQueryParam(cmd, $"OrderNumber{n}", studyitems.OrderNumber); AddQueryParam(cmd, $"ShotItemCode{n}", shotitem.ShotItemCode); AddQueryParam(cmd, $"ShotItemName{n}", shotitem.ShotItemName); cmd.ExecuteNonQuery(); n++; } }
/// <summary> /// 検査情報を更新 /// </summary> /// <param name="cmd"></param> /// <param name="trn"></param> /// <param name="studyitems"></param> private static void UpdateStudy(IDbCommand cmd, IDbTransaction trn, ValidatedStudyItems studyitems) { cmd.CommandText = @" UPDATE dbo.StudyOrders SET ProcessingDivision = @ProcessingDivision, ScheduledOn = @ScheduledOn, StudyTypeCode = @StudyTypeCode, StudyTypeName = @StudyTypeName, UpdatedAt = GETDATE() WHERE OrderNumber = @OrderNumber;"; AddQueryParam(cmd, "ProcessingDivision", studyitems.ProcessingDivision); AddQueryParam(cmd, "ScheduledOn", studyitems.ScheduledOn); AddQueryParam(cmd, "StudyTypeCode", studyitems.StudyTypeCode); AddQueryParam(cmd, "StudyTypeName", studyitems.StudyTypeName); cmd.ExecuteNonQuery(); cmd.CommandText = @" DELETE FROM dbo.ShotItems WHERE OrderNumber = @OrderNumber;"; cmd.ExecuteNonQuery(); //撮影項目の登録 SaveShotItems(cmd, studyitems); }
/// <summary> /// 検査情報を新規登録 /// </summary> /// <param name="cmd"></param> /// <param name="trn"></param> /// <param name="studyitems"></param> private static void InsertStudy(IDbCommand cmd, IDbTransaction trn, string patientId, ValidatedStudyItems studyitems) { cmd.CommandText = @" INSERT INTO dbo.StudyOrders (OrderNumber, PatientId, ProcessingDivision, ScheduledOn, StudyTypeCode, StudyTypeName, CreatedAt, UpdatedAt) VALUES (@OrderNumber, @PatientId, @ProcessingDivision, @ScheduledOn, @StudyTypeCode, @StudyTypeName, GETDATE(), GETDATE());"; AddQueryParam(cmd, "PatientId", patientId); AddQueryParam(cmd, "ProcessingDivision", studyitems.ProcessingDivision); AddQueryParam(cmd, "ScheduledOn", studyitems.ScheduledOn); AddQueryParam(cmd, "StudyTypeCode", studyitems.StudyTypeCode); AddQueryParam(cmd, "StudyTypeName", studyitems.StudyTypeName); cmd.ExecuteNonQuery(); SaveShotItems(cmd, studyitems); }