/// <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);
}
