public void Refresh_Data()
{
CSDataContext dc = new CSDataContext();
// 20200522 add Companion Log in and out
Admin = (from p in dc.log_Adm
where p.operation_name != "Log in" && p.operation_name != "Log out" && p.operation_name != "update opd" &&
p.operation_name != "update opd order" && p.operation_name != "OPD file format" &&
p.operation_name != "Change order data" && p.operation_name != "Add a new order" &&
p.operation_name != "Lab file format" && p.operation_name != "Change patient data" &&
p.operation_name != "Add a new patient" && p.operation_name != "Companion Log out" &&
p.operation_name != "Companion Log in"
orderby p.regdate descending
select new { p.regdate, p.operation_name, p.description }).Take(100);
Err = (from p in dc.log_Err
orderby p.error_date descending
select new { p.error_date, p.error_message }).Take(100);
OPD = (from p in dc.log_Adm
where p.operation_name == "update opd" || p.operation_name == "update opd order"
orderby p.regdate descending
select new { p.regdate, p.description }).Take(100);
Order = (from p in dc.log_Adm
where p.operation_name == "Change order data" || p.operation_name == "Add a new order"
orderby p.regdate descending
select new { p.regdate, p.description }).Take(100);
PT = (from p in dc.log_Adm
where p.operation_name == "Change patient data" || p.operation_name == "Add a new patient"
orderby p.regdate descending
select new { p.regdate, p.description }).Take(100);
tb.ShowBalloonTip("完成", "訊息頁資料已更新", BalloonIcon.Info);
}
public void Refresh_Data()
{
CSDataContext dc = new CSDataContext();
DataNoOrder = from p in dc.v_labdata_not_match_with_opd_order
orderby p.uid, p.l05
select p;
OrderNoData = from p in dc.v_opdorder_not_match_with_lab_record
orderby p.uid, p.SDATE
select p;
}
public void Execute(object parameter)
{
#region 進行配對
CSDataContext dc = new CSDataContext();
// 20190615 tbl_lab_record連結tbl_opd_order
var q = from cs in dc.sp_match_lab(int.Parse(_lmVM.StrFrom), int.Parse(_lmVM.StrTO)).AsEnumerable()
select cs;
int n = q.First().rows_affected;
Logging.Record_admin("檢驗檔配對", $"{n}筆配對成功");
#endregion 進行配對
_lmVM.Refresh_Data();
}
public void Refresh_Data()
{
CSDataContext dc = new CSDataContext();
#region Function Page
LogInOut = (from p in dc.log_Adm
where p.operation_name == "Log in" || p.operation_name == "Log out"
orderby p.regdate descending
select new { p.regdate, p.operation_name }).Take(100);
// 20200522 add opd_import, for correct display
OPD = (from p in dc.log_Adm
where p.operation_name == "add opd" || p.operation_name == "opd_import"
orderby p.regdate descending
select new { p.regdate }).Take(100);
PT = (from p in dc.log_Adm
where p.operation_name == "病患檔案格式"
orderby p.regdate descending
select new { p.regdate }).Take(100);
Order = (from p in dc.log_Adm
where p.operation_name == "計價檔格式"
orderby p.regdate descending
select new { p.regdate }).Take(100);
Upload = (from p in dc.log_Adm
where p.operation_name == "健保上傳XML檔配對"
orderby p.regdate descending
select new { p.regdate }).Take(100);
// 20200522 add PIJIA add/change, for correct display
Pijia = (from p in dc.log_Adm
where p.operation_name == "新增批價檔: " || p.operation_name == "PIJIA add/change"
orderby p.regdate descending
select new { p.regdate }).Take(100);
ChangeDepartment = (from p in dc.log_Adm
where p.operation_name == "change department"
orderby p.regdate descending
select new { p.regdate }).Take(100);
Lab = (from p in dc.log_Adm
where p.operation_name == "Lab file format"
orderby p.regdate descending
select new { p.regdate }).Take(100);
#endregion Function Page
tb.ShowBalloonTip("完成", "主頁資料已更新", BalloonIcon.Info);
}
private void Matching()
{
#region 進行配對
// 現再來配對, 使用Stored Procedure
// 第一步Pijia配上CASENO
// 第二步檢查CASENO是否1to1配上Pijia, 若是進行配對,並顯示正確,若否回傳錯誤幾筆,並且紀錄下來
// 20200519 transcribed
// 20190614 created
// 目的是將tbl_pijia和tbl_opd配對起來
// 分為兩步
// 第一步將tbl_pijia配上CASENO
CSDataContext dc = new CSDataContext();
var q1 = from cs in dc.sp_CASENO_for_pijia().AsEnumerable()
select cs;
string strOutput = $"{_begindate:d}_{_enddate:d}: {q1.First().rows_affected}筆配對";
Logging.Record_admin("批價檔配對STEP1 Pijia", strOutput);
log.Info($"批價檔配對STEP1 Pijia: {strOutput}");
tb.ShowBalloonTip("批價檔配對STEP1 Pijia", strOutput, BalloonIcon.Info);
var q2 = from pj in dc.sp_PIJIA_for_opd().AsEnumerable()
select pj;
strOutput = string.Empty;
foreach (var q in q2)
{
strOutput += $"{q.CASENO} {q.SDATE} {q.VIST} {q.RMNO} {q.bid} {q.cname};";
}
if (strOutput == string.Empty)
{
Logging.Record_admin("批價檔配對STEP2 OPD", "沒有重複");
log.Info($"批價檔配對STEP2 OPD: 沒有重複");
tb.ShowBalloonTip("批價檔配對STEP2 OPD", "沒有重複", BalloonIcon.Info);
}
else
{
strOutput += ";請修正後再上傳";
Logging.Record_admin("批價檔配對STEP2 OPD", strOutput);
log.Info($"批價檔配對STEP2 OPD: {strOutput}");
tb.ShowBalloonTip("批價檔配對STEP2 OPD, CASE有重複值:", strOutput, BalloonIcon.Info);
}
#endregion 進行配對
}
public static void Record_admin(string op, string des)
{
///寫入作業訊息
using (CSDataContext dc = new CSDataContext())
{
log_Adm newLog = new log_Adm()
{
regdate = DateTime.Now,
application_name = System.Reflection.Assembly.GetExecutingAssembly().FullName.Substring(0, 49),
machine_name = Dns.GetHostName(),
ip_address = Dns.GetHostEntry(Dns.GetHostName()).AddressList[0].ToString(),
userid = "Ethan",
operation_name = op,
description = des
};
dc.log_Adm.InsertOnSubmit(newLog);
dc.SubmitChanges();
}
}
public static void Record_error(string er)
{
///created on 2020/03/28, transcribed from vb.net
///寫入錯誤訊息
using (CSDataContext dc = new CSDataContext())
{
log_Err newErr = new log_Err()
{
error_date = DateTime.Now,
application_name = System.Reflection.Assembly.GetExecutingAssembly().FullName.Substring(0, 49),
machine_name = Dns.GetHostName(),
ip_address = Dns.GetHostEntry(Dns.GetHostName()).AddressList[0].ToString(),
userid = "Ethan",
error_message = er
};
dc.log_Err.InsertOnSubmit(newErr);
dc.SubmitChanges();
}
}
public void Build()
{
#region Declaration
XmlDocument xdoc; // TOTFA.xml
XmlElement xElement; // patient
XmlElement xChildElement;
XmlElement xElement2;
XmlElement xChildElement2;
string savepath;
#endregion Declaration
#region 寫入檔案路徑
// 讀取要輸入的位置
// 從杏翔病患資料輸入, 只有一種xml格式
// Xml格式的index=2
SaveFileDialog sFDialog = new SaveFileDialog
{
Filter = "xml|*.xml",
FileName = "TOTFA.xml"
};
if (sFDialog.ShowDialog() == true)
{
savepath = sFDialog.FileName;
}
else
{
// 取消, 什麼也沒有做
return;
}
#endregion 寫入檔案路徑
try
{
CSDataContext dc = new CSDataContext();
var q = from pt in dc.sp_get_hdata(_strYM).AsEnumerable()
select pt;
// 建立一個 XmlDocument 物件並加入 Declaration
xdoc = new XmlDocument();
xdoc.AppendChild(xdoc.CreateXmlDeclaration("1.0", "big5", ""));
// 建立根節點物件並加入 XmlDocument 中 (第0層)
xElement = xdoc.CreateElement("patient");
xChildElement = xElement; // 這個舉動毫無意義,但可以避免錯誤訊息
xdoc.AppendChild(xElement);
// 在sections下寫入一個節點名稱為section(第1層)
foreach (var p in q)
{
if (p.r1 == 1)
{
xChildElement = xdoc.CreateElement("hdata");
xElement.AppendChild(xChildElement); // patient下加個hdata
// 第2層節點
xElement2 = xdoc.CreateElement("h1");
xElement2.InnerText = p.h1; // h1 報告類別, 1:檢體檢驗報告
xChildElement.AppendChild(xElement2); // hdata下加個h1
xElement2 = xdoc.CreateElement("h2");
xElement2.InnerText = p.h2; // h2 醫事機構代碼
xChildElement.AppendChild(xElement2); // hdata下加個h2
xElement2 = xdoc.CreateElement("h3");
xElement2.InnerText = p.h3; // h3 醫事類別, 11:門診西醫診所
xChildElement.AppendChild(xElement2); // hdata下加個h3
xElement2 = xdoc.CreateElement("h4");
xElement2.InnerText = p.h4; // h4 費用年月
xChildElement.AppendChild(xElement2); // hdata下加個h4
xElement2 = xdoc.CreateElement("h5");
xElement2.InnerText = p.h5; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h6");
xElement2.InnerText = p.h6; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h7");
xElement2.InnerText = p.h7; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h8");
xElement2.InnerText = p.h8.ToString(); // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h9");
xElement2.InnerText = p.h9; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h10");
xElement2.InnerText = p.h10; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h11");
xElement2.InnerText = p.h11; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h17");
xElement2.InnerText = p.h17.ToString(); // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h18");
xElement2.InnerText = p.h18; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h19");
xElement2.InnerText = p.h19; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h20");
xElement2.InnerText = p.h20; //h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h22");
xElement2.InnerText = p.h22; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h23");
xElement2.InnerText = p.h23; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h25");
xElement2.InnerText = p.h25; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
xElement2 = xdoc.CreateElement("h26");
xElement2.InnerText = p.h26; // h5 申報類別, 1:送核
xChildElement.AppendChild(xElement2); // hdata下加個h5
// 第3層節點
xChildElement2 = xdoc.CreateElement("rdata"); // rdata
xChildElement.AppendChild(xChildElement2); // under hdata add rdata
xElement2 = xdoc.CreateElement("r1");
xElement2.InnerText = p.r1.ToString();
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r2");
xElement2.InnerText = p.r2;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r3");
xElement2.InnerText = p.r3;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r4");
xElement2.InnerText = p.r4;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r5");
xElement2.InnerText = p.r5;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r6-1");
xElement2.InnerText = p.r6a;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r6-2");
xElement2.InnerText = p.r6b;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r9");
xElement2.InnerText = p.r9;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r10");
xElement2.InnerText = p.r10;
xChildElement2.AppendChild(xElement2);
}
else
{
// 第3層節點
xChildElement2 = xdoc.CreateElement("rdata"); //rdata
xChildElement.AppendChild(xChildElement2); //under hdata add rdata
xElement2 = xdoc.CreateElement("r1");
xElement2.InnerText = p.r1.ToString();
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r2");
xElement2.InnerText = p.r2;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r3");
xElement2.InnerText = p.r3;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r4");
xElement2.InnerText = p.r4;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r5");
xElement2.InnerText = p.r5;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r6-1");
xElement2.InnerText = p.r6a;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r6-2");
xElement2.InnerText = p.r6b;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r9");
xElement2.InnerText = p.r9;
xChildElement2.AppendChild(xElement2);
xElement2 = xdoc.CreateElement("r10");
xElement2.InnerText = p.r10;
xChildElement2.AppendChild(xElement2);
}
}
xdoc.Save(savepath);
using (FileStream fs = new FileStream(savepath.Replace(".xml", ".zip"), FileMode.Create))
using (ZipArchive arch = new ZipArchive(fs, ZipArchiveMode.Create))
{
arch.CreateEntryFromFile(savepath, "TOTFA.xml");
}
string output = $"{_strYM} 檢驗上傳XML製作完成.";
log.Info(output);
tb.ShowBalloonTip("完成!", $"{output}\r\n請上傳檔案.", BalloonIcon.Info);
Logging.Record_admin("製作檢驗上傳檔案", output);
}
catch (System.Exception ex)
{
string output = $"{ex.Message} \r\n{ex.StackTrace}";
log.Error(output);
tb.ShowBalloonTip("錯誤!", output, BalloonIcon.Error);
}
}
private async Task <PIJIAresult> WriteIntoSQL_async(object[,] data, string strYM)
{
// 20200519 獨立成一個副程式
CSDataContext dc = new CSDataContext();
int totalN = data.GetUpperBound(0);
int add_N = 0;
int change_N = 0;
int all_N = 0;
await Task.Run(() =>
{
for (int i = 2; i <= totalN; i++)
{
// 找到KEY值, YM, bid: YM=strYM, bid=Item(1), 第二個值就是bid
// 查詢,看看是否有重複
// 沒有重複就是新增, 有重複就是修改
var q = from o in dc.tbl_pijia
where (o.YM == strYM) && (o.bid == (string)data[i, 2]) && (o.uid == (string)data[i, 14])
select o;
if (q.Count() == 0) // 資料庫裡面沒有 INSERT
{
// MedFee 有小數點, 所以要先變成double, 再變成int
tbl_pijia newPijia = new tbl_pijia()
{
YM = strYM,
STATUS = (string)data[i, header_order[1]],
bid = (string)data[i, header_order[2]],
op = (string)data[i, header_order[3]],
VDATE = (string)data[i, header_order[4]] ?? string.Empty,
SDATE = (string)data[i, header_order[5]],
VIST = (string)data[i, header_order[6]],
RMNO = (string)data[i, header_order[7]],
DEPTNAME = (string)data[i, header_order[8]],
DOCTNAME = (string)data[i, header_order[9]],
POSINAME = (string)data[i, header_order[10]],
HEATH_CARD = (string)data[i, header_order[11]] ?? string.Empty,
Youmian = (string)data[i, header_order[12]] ?? string.Empty,
PAYNO = (string)data[i, header_order[13]] ?? string.Empty,
uid = (string)data[i, header_order[14]],
cname = (string)data[i, header_order[15]],
MedFee = (int)double.Parse((string)data[i, header_order[16]]),
RegFee = int.Parse((string)data[i, header_order[17]]),
Copay = int.Parse((string)data[i, header_order[18]]),
Deposit = int.Parse((string)data[i, header_order[19]]),
SelfPay = int.Parse((string)data[i, header_order[20]]),
PharmW = int.Parse((string)data[i, header_order[21]]),
Arrears = int.Parse((string)data[i, header_order[22]]),
Discount = int.Parse((string)data[i, header_order[23]]),
AMTreceivable = int.Parse((string)data[i, header_order[24]]),
AMTreceived = int.Parse((string)data[i, header_order[25]]),
bremark = (string)data[i, header_order[26]] ?? string.Empty,
remark = (string)data[i, header_order[27]] ?? string.Empty,
QDATE = _qdate
};
// 20200526 加入QDATE
dc.tbl_pijia.InsertOnSubmit(newPijia);
dc.SubmitChanges();
add_N++;
}
else
{
// 資料庫裡已經有了, 檢查是否有異,有異UPDATE
tbl_pijia oldPijia = q.ToList()[0]; // this is a record
string strChange = string.Empty;
bool bChange = false;
if (oldPijia.STATUS != (string)data[i, header_order[1]])
{
strChange += $";改狀態: {oldPijia.STATUS}=>{(string)data[i, header_order[1]]}";
bChange = true;
oldPijia.STATUS = (string)data[i, header_order[1]];
}
if (oldPijia.op != (string)data[i, header_order[3]])
{
strChange += $";改批價人員: {oldPijia.op}=>{(string)data[i, header_order[3]]}";
bChange = true;
oldPijia.op = (string)data[i, header_order[3]];
}
if (oldPijia.VDATE != ((string)data[i, header_order[4]] ?? string.Empty))
{
strChange += $";改作廢日期: {oldPijia.VDATE}=>{((string)data[i, header_order[4]] ?? string.Empty)}";
bChange = true;
oldPijia.VDATE = ((string)data[i, header_order[4]] ?? string.Empty);
}
if (oldPijia.SDATE != (string)data[i, header_order[5]])
{
strChange += $";改看診日期: {oldPijia.SDATE}=>{(string)data[i, header_order[5]]}";
bChange = true;
oldPijia.SDATE = (string)data[i, header_order[5]];
}
if (oldPijia.VIST != (string)data[i, header_order[6]])
{
strChange += $";改午別: {oldPijia.VIST}=>{(string)data[i, header_order[6]]}";
bChange = true;
oldPijia.VIST = (string)data[i, header_order[6]];
}
if (oldPijia.RMNO != (string)data[i, header_order[7]])
{
strChange += $";改診別: {oldPijia.RMNO}=>{(string)data[i, header_order[7]]}";
bChange = true;
oldPijia.RMNO = (string)data[i, header_order[7]];
}
if (oldPijia.DEPTNAME != (string)data[i, header_order[8]])
{
strChange += $";改科別: {oldPijia.DEPTNAME}=>{(string)data[i, header_order[8]]}";
bChange = true;
oldPijia.DEPTNAME = (string)data[i, header_order[8]];
}
if (oldPijia.DOCTNAME != (string)data[i, header_order[9]])
{
strChange += $";改醫師: {oldPijia.DOCTNAME}=>{(string)data[i, header_order[9]]}";
bChange = true;
oldPijia.DOCTNAME = (string)data[i, header_order[9]];
}
if (oldPijia.POSINAME != (string)data[i, header_order[10]])
{
strChange += $";改身分: {oldPijia.POSINAME}=>{(string)data[i, header_order[10]]}";
bChange = true;
oldPijia.POSINAME = (string)data[i, header_order[10]];
}
if (oldPijia.HEATH_CARD != ((string)data[i, header_order[11]] ?? string.Empty))
{
strChange += $";改就醫序號: {oldPijia.HEATH_CARD}=>{((string)data[i, header_order[11]] ?? string.Empty)}";
bChange = true;
oldPijia.HEATH_CARD = ((string)data[i, header_order[11]] ?? string.Empty);
}
if (oldPijia.Youmian != ((string)data[i, header_order[12]] ?? string.Empty))
{
strChange += $";改優免: {oldPijia.Youmian}=>{((string)data[i, header_order[12]] ?? string.Empty)}";
bChange = true;
oldPijia.Youmian = ((string)data[i, header_order[12]] ?? string.Empty);
}
if (oldPijia.PAYNO != ((string)data[i, header_order[13]] ?? string.Empty))
{
strChange += $";改部分負擔: {oldPijia.PAYNO}=>{((string)data[i, header_order[13]] ?? string.Empty)}";
bChange = true;
oldPijia.PAYNO = ((string)data[i, header_order[13]] ?? string.Empty);
}
if (oldPijia.cname != (string)data[i, header_order[15]])
{
strChange += $";改患者姓名: {oldPijia.cname}=>{(string)data[i, header_order[15]]}";
bChange = true;
oldPijia.cname = (string)data[i, header_order[15]];
}
if (oldPijia.MedFee != (int)double.Parse((string)data[i, header_order[16]]))
{
strChange += $";改醫療費用: {oldPijia.MedFee}=>{(string)data[i, header_order[16]]}";
bChange = true;
oldPijia.MedFee = (int)double.Parse((string)data[i, header_order[16]]);
}
if (oldPijia.RegFee != int.Parse((string)data[i, header_order[17]]))
{
strChange += $";改掛號費用: {oldPijia.RegFee}=>{(string)data[i, header_order[17]]}";
bChange = true;
oldPijia.RegFee = int.Parse((string)data[i, header_order[17]]);
}
if (oldPijia.Copay != int.Parse((string)data[i, header_order[18]]))
{
strChange += $";改部分負擔: {oldPijia.Copay}=>{(string)data[i, header_order[18]]}";
bChange = true;
oldPijia.Copay = int.Parse((string)data[i, header_order[18]]);
}
if (oldPijia.Deposit != int.Parse((string)data[i, header_order[19]]))
{
strChange += $";改押金: {oldPijia.Deposit}=>{(string)data[i, header_order[19]]}";
bChange = true;
oldPijia.Deposit = int.Parse((string)data[i, header_order[19]]);
}
if (oldPijia.SelfPay != int.Parse((string)data[i, header_order[20]]))
{
strChange += $";改自付金額: {oldPijia.SelfPay}=>{(string)data[i, header_order[20]]}";
bChange = true;
oldPijia.SelfPay = int.Parse((string)data[i, header_order[20]]);
}
if (oldPijia.PharmW != int.Parse((string)data[i, header_order[21]]))
{
strChange += $";改藥費加重: {oldPijia.PharmW}=>{(string)data[i, header_order[21]]}";
bChange = true;
oldPijia.PharmW = int.Parse((string)data[i, header_order[21]]);
}
if (oldPijia.Arrears != int.Parse((string)data[i, header_order[22]]))
{
strChange += $";改欠收: {oldPijia.Arrears}=>{(string)data[i, header_order[22]]}";
bChange = true;
oldPijia.Arrears = int.Parse((string)data[i, header_order[22]]);
}
if (oldPijia.Discount != int.Parse((string)data[i, header_order[23]]))
{
strChange += $";改折扣: {oldPijia.Discount}=>{(string)data[i, header_order[23]]}";
bChange = true;
oldPijia.Discount = int.Parse((string)data[i, header_order[23]]);
}
if (oldPijia.AMTreceivable != int.Parse((string)data[i, header_order[24]]))
{
strChange += $";改應收金額: {oldPijia.AMTreceivable}=>{(string)data[i, header_order[24]]}";
bChange = true;
oldPijia.AMTreceivable = int.Parse((string)data[i, header_order[24]]);
}
if (oldPijia.AMTreceived != int.Parse((string)data[i, header_order[25]]))
{
strChange += $";改實收金額: {oldPijia.AMTreceived}=>{(string)data[i, header_order[25]]}";
bChange = true;
oldPijia.AMTreceived = int.Parse((string)data[i, header_order[25]]);
}
if (oldPijia.bremark != ((string)data[i, header_order[26]] ?? string.Empty))
{
strChange += $";改收據說明: {oldPijia.bremark}=>{((string)data[i, header_order[26]] ?? string.Empty)}";
bChange = true;
oldPijia.bremark = ((string)data[i, header_order[26]] ?? string.Empty);
}
if (oldPijia.remark != ((string)data[i, header_order[27]] ?? string.Empty))
{
strChange += $";改說明: {oldPijia.remark}=>{((string)data[i, header_order[27]] ?? string.Empty)}";
bChange = true;
oldPijia.remark = ((string)data[i, header_order[27]] ?? string.Empty);
}
if (bChange)
{
// tbl_opd的Pijia欄位也要歸零
var r = from opd in dc.tbl_opd
where opd.CASENO == oldPijia.CASENO
select opd;
tbl_opd opdOPD = r.ToList()[0];
opdOPD.Pijia = null;
// CASENO, G要歸零
oldPijia.CASENO = null;
oldPijia.G = null;
// 做實改變
// 20200526 加入QDATE
oldPijia.QDATE = _qdate;
dc.SubmitChanges();
change_N++;
// 做記錄
Logging.Record_admin("修改批價資料", $"{strYM}-{(string)data[i, 13]}: {strChange}");
}
}
all_N++;
}
});
return(new PIJIAresult()
{
NewPIJIA = add_N,
ChangePIJIA = change_N,
AllPIJIA = all_N
});
}
public void Transform()
{
// 檢查檔案格式
// 檢查第一行的標題,看看是否符合
string[] strT = new string[] { "", "身份證字號", "病患姓名", "出生日期", "性別", "原病歷號碼", "原就醫日期",
"檢驗單工號", "開單日(收件日)", "開單時間", "檢驗日期", "報告日期", "報告時間",
"就醫序號" };
for (int i = 1; i <= strT.Length; i++)
{
if (strT[i - 1] != ((string)_data[1, i] ?? string.Empty))
{
// 寫入Error Log
Logging.Record_error("輸入的常誠檢驗資料檔案格式不對");
log.Error("輸入的常誠檢驗資料檔案格式不對");
tb.ShowBalloonTip("錯誤", "輸入的常誠檢驗資料檔案格式不對", BalloonIcon.Error);
return;
}
}
// 通過測試
Logging.Record_admin("Lab file format", "correct");
log.Info("輸入的常誠檢驗資料檔案格式正確");
tb.ShowBalloonTip("正確", "常誠檢驗檔案格式正確", BalloonIcon.Info);
// 要有迴路, 來讀一行一行的xls, 能夠判斷
// 檔案結構複雜, 不好用for next, 應該用while
// 一次性讀檔, 不用update
// totalN+1 是excel檔的總rows數
int totalN = _data.GetUpperBound(0) - 1;
int ind = 1; // index, 從第二行開始
string strUid = string.Empty;
string strLid = string.Empty;
DateTime dL05 = DateTime.Parse("1901/01/01");
CSDataContext dc = new CSDataContext();
while (ind <= totalN)
{
ind++; //next line
if ((string)_data[ind, 1] == "***")
{
try
{
// 檢驗單工號, 第8欄, 檢查是否空白, 空白不行
if (_data[ind, 8].ToString().Length == 0 || _data[ind, 2].ToString().Length == 0 ||
!DateTime.TryParse((string)_data[ind, 12], out DateTime d1))
{
strUid = string.Empty;
dL05 = DateTime.Parse("1901/01/01");
strLid = string.Empty;
// 寫入Error Log
string output = "輸入檢驗資料時,缺少檢驗單工號,或身分證字號, 或沒有報告日期";
Logging.Record_error(output);
log.Error(output);
continue; // continue while就可以跳下一行
}
else
{
strLid = _data[ind, 8].ToString().Trim();
var La = from l in dc.tbl_lab
where l.lid == strLid
select l; // a query for searching duplicates
if (La.Count() != 0) //如果有重複,不但這行不要讀了, 連帶後面也都不要讀(strLid=""), 直到下次"***"
{
strUid = string.Empty;
dL05 = DateTime.Parse("1901/01/01");
strLid = string.Empty;
continue; //跳下一行
}
// 身分證字號, 第2欄, 檢查是否空白, 空白不行
strUid = _data[ind, 2].ToString().Trim();
// 報告日期, 第12欄, 檢查是否空白, 空白不行
dL05 = d1;
// 檢查檢驗單工號是否存在,如果有就不要存了
}
// 寫入資料庫tbl_Lab, uid, lid, cname, bd, mf, cid, l01, l02, l03, l04, l05, l06
// 有些變數共用uid, lid, l05
//l01, 原就醫日期,刻意留白, 第7欄
tbl_lab newLb = new tbl_lab()
{
uid = strUid, //身分證字號,第2欄
cname = _data[ind, 3].ToString().Trim(), //病患姓名, 第3欄
mf = _data[ind, 5].ToString().Trim(), //性別,第5欄
cid = _data[ind, 6].ToString().Trim(), //原病歷號碼,第6欄
lid = strLid, //檢驗單工號, 第8欄
l03 = _data[ind, 10].ToString().Trim(), //開單時間, 第10欄
l05 = dL05, //報告日期, 第12欄
l06 = _data[ind, 13].ToString().Trim() //報告時間,第13欄
};
if (DateTime.TryParse((string)_data[ind, 4], out DateTime d2)) //出生日期, 第4欄
{
newLb.bd = d2;
}
if (DateTime.TryParse((string)_data[ind, 9], out DateTime d3)) //開單日(收件日), 第9欄
{
newLb.l02 = d3;
}
if (DateTime.TryParse((string)_data[ind, 11], out DateTime d4)) //檢驗日期, 第11欄
{
newLb.l04 = d4;
}
dc.tbl_lab.InsertOnSubmit(newLb);
dc.SubmitChanges();
}
catch (Exception ex)
{
// 寫入錯誤訊息
Logging.Record_error(ex.Message);
log.Error(ex.Message);
}
}
else
{
try
{
//如果沒讀過"***"就略過,以防檔案有錯
if (strLid.Length == 0 || strUid.Length == 0 || dL05 == DateTime.Parse("1901/01/01"))
{
continue;
}
// 寫入資料庫tbl_Lab_record: uid, lid, l05, iid, l07
tbl_lab_record newLbrd = new tbl_lab_record()
{
uid = strUid, //身分證字號
lid = strLid, //檢驗單工號
l05 = dL05, //報告日期
iid = _data[ind, 1].ToString().Trim(), //檢驗代碼, 第1欄
l07 = _data[ind, 4].ToString().Trim(), //檢驗值, 第4欄
l09 = _data[ind, 5].ToString().Trim() //異常, 第5欄
};
dc.tbl_lab_record.InsertOnSubmit(newLbrd);
// 寫入資料庫p_lab_temp: l05, iid, l08, l09, l10, l11
p_lab_temp newTemp = new p_lab_temp()
{
l05 = dL05, //報告日期
iid = _data[ind, 1].ToString().Trim(), //檢驗代碼, 第1欄
l08 = _data[ind, 2].ToString().Trim(), //檢驗名稱, 第2欄
l10 = _data[ind, 6].ToString().Trim(), //單位, 第6欄
l11 = _data[ind, 7].ToString().Trim() //參考值, 第7欄
};
dc.p_lab_temp.InsertOnSubmit(newTemp);
dc.SubmitChanges();
}
catch (Exception ex)
{
// 寫入錯誤訊息
Logging.Record_error(ex.Message);
log.Error(ex.Message);
}
}
}
this.Dispose();
}
private async Task <PTresult> ImportPT_async(object[,] data, IProgress <ProgressReportModel> progress, ProgressReportModel report)
{
int totalN = data.GetUpperBound(0);
int add_N = 0;
int change_N = 0;
int all_N = 0;
await Task.Run(() =>
{
log.Info($" enter ImportPT_async.");
// 要有迴路, 來讀一行一行的xls, 能夠判斷
for (int i = 0; i <= totalN; i++)
{
// 先判斷是否已經在資料表中, 如果不是就insert否則判斷要不要update
// 如何判斷是否已經在資料表中?
CSDataContext dc = new CSDataContext();
string strUID = string.Empty;
// 先判斷身分證字號是否空白, 原本第8, 現在第7
if (string.IsNullOrEmpty((string)data[i, 7]))
{
// 寫入Error Log
// 沒有身分證字號是不行的
Logging.Record_error("身分證字號是空的");
log.Error("身分證字號是空的");
return;
}
// 再判斷是否已在資料表中
strUID = (string)data[i, 7]; //身分證號,第7欄
var pt = from p in dc.tbl_patients
where p.uid == strUID
select p; // this is a querry
if (pt.Count() == 0)
{
// insert
// 沒這個人可以新增這個人
// 填入資料
try
{
tbl_patients newPt = new tbl_patients();
if (string.IsNullOrEmpty((string)data[i, 0]))
{
// 寫入Error Log
Logging.Record_error($"{strUID} 沒有病歷號碼");
log.Error($"{strUID} 沒有病歷號碼");
}
else
{
newPt.cid = long.Parse((string)data[i, 0]); // 病歷號, 第1欄
}
newPt.uid = strUID; // 身分證號,第8欄
if (string.IsNullOrEmpty((string)data[i, 1]))
{
// 寫入Error Log
Logging.Record_error($"{strUID} 沒有姓名");
log.Error($"{strUID} 沒有姓名");
}
else
{
newPt.cname = (string)data[i, 1]; //姓名,第2欄
}
newPt.mf = (string)data[i, 2]; // 性別, 第3欄
if (string.IsNullOrEmpty((string)data[i, 8]))
{
// 寫入Error Log
Logging.Record_error($"{strUID} 沒有生日資料");
log.Error($"{strUID} 沒有生日資料");
}
else
{
string strD = (string)data[i, 8]; // 生日, 第9欄
newPt.bd = DateTime.Parse($"{strD.Substring(0, 4)}/{strD.Substring(4, 2)}/{strD.Substring(6, 2)}");
}
newPt.p01 = (string)data[i, 3]; // 市內電話, 第4欄
newPt.p02 = (string)data[i, 4]; // 手機電話, 第5欄
newPt.p03 = (string)data[i, 9]; // 地址,第10欄
newPt.p04 = (string)data[i, 10]; // 提醒,第11欄
newPt.QDATE = _qdate;
// 2020026新增QDATE
dc.tbl_patients.InsertOnSubmit(newPt);
dc.SubmitChanges();
// 20190929 加姓名, 病歷號
Logging.Record_admin("Add a new patient", $"{data[i, 0]} {strUID} {data[i, 1]}");
log.Info($"Add a new patient: {data[i, 0]} {strUID} {data[i, 1]}");
add_N++;
}
catch (Exception ex)
{
Logging.Record_error(ex.Message);
log.Error(ex.Message);
}
}
else
{
// update
// 有此人喔, 走update方向
// 拿pt比較ws.cells(i),如果不同就修改,並且記錄
tbl_patients oldPt = (from p in dc.tbl_patients
where p.uid == strUID
select p).ToList()[0]; // this is a record
string strChange = string.Empty;
bool bChange = false;
try
{
// 病歷號, 20200512加上修改病歷號
if (string.IsNullOrEmpty((string)data[i, 0]))
{
// 寫入Error Log
Logging.Record_error($"{strUID} 沒有病歷號碼");
log.Error($"{strUID} 沒有病歷號碼");
}
else if (oldPt.cid != long.Parse((string)data[i, 0]))
{
strChange += $"改病歷號: {oldPt.cid}=>{data[i, 0]}; ";
bChange = true;
oldPt.cid = long.Parse((string)data[i, 0]); // 病歷號, 第1欄
}
// 姓名
if (string.IsNullOrEmpty((string)data[i, 1]))
{
// 寫入Error Log
Logging.Record_error(strUID + " 沒有姓名");
log.Error($"{strUID} 沒有姓名");
}
else if (oldPt.cname != (string)data[i, 1])
{
strChange += $"改名: {oldPt.cname}=>{data[i, 1]}; ";
bChange = true;
oldPt.cname = (string)data[i, 1]; // 姓名,第2欄
}
// 性別
if (oldPt.mf != (string)data[i, 2])
{
strChange += $"改性別: {oldPt.mf}=>{data[i, 2]}; ";
bChange = true;
oldPt.mf = (string)data[i, 2]; // 性別, 第3欄
}
// 生日
if (string.IsNullOrEmpty((string)data[i, 8]))
{
// 寫入Error Log
Logging.Record_error($"{strUID} 沒有生日資料");
log.Error($"{strUID} 沒有生日資料");
}
else
{
string strBD = (string)data[i, 8]; // 生日, 第9欄
DateTime dBD = DateTime.Parse($"{strBD.Substring(0, 4)}/{strBD.Substring(4, 2)}/{strBD.Substring(6, 2)}");
if (oldPt.bd != dBD)
{
strChange += $"改生日: {oldPt.bd}=>{dBD}; ";
bChange = true;
oldPt.bd = dBD; // 生日,第9欄
}
}
// 市內電話
if ((oldPt.p01 ?? string.Empty) != ((string)data[i, 3] ?? string.Empty))
{
strChange += $"改市內電話: {oldPt.p01}=>{data[i, 3]}; ";
bChange = true;
oldPt.p01 = (string)data[i, 3]; // 市內電話,第4欄
}
// 手機電話
if ((oldPt.p02 ?? string.Empty) != ((string)data[i, 4] ?? string.Empty))
{
strChange += $"改手機電話: {oldPt.p02}=>{data[i, 4]}; ";
bChange = true;
oldPt.p02 = (string)data[i, 4]; // 手機電話,第5欄
}
// 地址
if ((oldPt.p03 ?? string.Empty) != ((string)data[i, 9] ?? string.Empty))
{
strChange += $"改地址: {oldPt.p03}=>{data[i, 9]}; ";
bChange = true;
oldPt.p03 = (string)data[i, 9]; // 地址,第10欄
}
// 提醒
if ((oldPt.p04 ?? string.Empty) != ((string)data[i, 10] ?? string.Empty))
{
strChange += $"改提醒: {oldPt.p04}=>{data[i, 10]}; ";
bChange = true;
oldPt.p04 = (string)data[i, 10]; // 提醒,第11欄
}
if (bChange)
{
// 做實改變
// 2020026新增QDATE
oldPt.QDATE = _qdate;
dc.SubmitChanges();
// 做記錄
// 20190929 加姓名, 病歷號
Logging.Record_admin("Change patient data", $"{data[i, 0]} {strUID} {data[i, 1]}: {strChange}");
log.Info($"Change patient data: {data[i, 0]} {strUID} {data[i, 1]}: {strChange}");
change_N++;
}
}
catch (Exception ex)
{
Logging.Record_error(ex.Message);
log.Error(ex.Message);
}
}
all_N++;
report.PercentageComeplete = all_N * 100 / totalN;
progress.Report(report);
}
log.Info($" exit ImportPT_async.");
});
return(new PTresult()
{
NewPT = add_N,
ChangePT = change_N,
AllPT = all_N
});
}
public void Transform()
{
#region 宣告
DataSet ds = new DataSet();
System.Data.DataTable dtO = new System.Data.DataTable();
System.Data.DataTable dtP = new System.Data.DataTable();
int new_opd_N = 0;
int change_opd_N = 0;
int change_order_N = 0;
int total_rows = 0;
#endregion 宣告
#region 整理datatable
//整理datatable, 分拆成兩個, 一旦可以通過,那這個檔案應該沒有問題,如果有問題,就不是正確的檔案
try
{
ds.ReadXml(_loadpath, XmlReadMode.ReadSchema);
dtP = ds.Tables[0]; // dtP for tbl_opd_order, P stands for prescription
dtP.Columns.Remove("STATUS");
dtP.Columns.Remove("REGNO");
dtP.Columns.Remove("PNAME");
dtP.Columns.Remove("SEX");
dtP.Columns.Remove("BIRTH");
dtP.Columns.Remove("ORI_TOTAL");
dtP.Columns.Remove("TOTAL");
dtP.Columns.Remove("AMT8");
dtP.Columns.Remove("RECT_NO");
dtO = dtP.Copy();
// 移除dtO不必要欄位, 先轉移給暫存檔, 因為要distinct, O stands for OPD
dtO.Columns.Remove("CODE");
dtO.Columns.Remove("ENAME");
dtO.Columns.Remove("TIMES_DAY");
dtO.Columns.Remove("METHODE");
dtO.Columns.Remove("TIME_QTY1");
dtO.Columns.Remove("DAYS");
dtO.Columns.Remove("BILL_QTY");
dtO.Columns.Remove("CHRONIC");
dtO.Columns.Remove("PUT_TYPE");
dtO.Columns.Remove("HC");
dtO.Columns.Remove("PRICE");
dtO.Columns.Remove("AMT");
dtO.Columns.Remove("ORI_AMT");
dtO.Columns.Remove("CLASS");
dtO.Columns.Remove("PRN_CODE");
dtO.Columns.Remove("RESULT");
// 移除dtP不需要的欄位(for tbl_opd_order)
dtP.Columns.Remove("VIST");
dtP.Columns.Remove("RMNO");
dtP.Columns.Remove("DEPTNAME");
dtP.Columns.Remove("DOCTNAME");
dtP.Columns.Remove("POSINAME");
dtP.Columns.Remove("PAYNO");
dtP.Columns.Remove("HEATH_CARD");
dtP.Columns.Remove("STEXT");
dtP.Columns.Remove("OTEXT");
dtP.Columns.Remove("ICDCODE1");
dtP.Columns.Remove("ICDCODE2");
dtP.Columns.Remove("ICDCODE3");
dtO = dtO.DefaultView.ToTable(true, new string[] { "CASENO", "SDATE", "VIST", "RMNO", "DEPTNAME", "DOCTNAME",
"IDNO", "POSINAME", "PAYNO", "HEATH_CARD", "STEXT", "OTEXT", "ICDCODE1", "ICDCODE2",
"ICDCODE3" }); // true stands for distinct
}
catch (Exception ex)
{
Logging.Record_error(ex.Message);
log.Error(ex.Message);
tb.ShowBalloonTip("錯誤!", ex.Message, BalloonIcon.Error);
return;
}
// 通過測試
total_rows = dtO.Rows.Count;
Logging.Record_admin("OPD file format", $"correct, {total_rows} records.");
log.Info($"OPD XML 檔案格式正確, 共{total_rows}筆.");
tb.ShowBalloonTip("正確", $"OPD XML 檔案格式正確, 共{total_rows}筆.", BalloonIcon.Info);
#endregion 整理datatable
#region 進行讀取資料
//Main.ProgressBar1.Minimum = 1
//Main.ProgressBar1.Maximum = totalN
CSDataContext dc = new CSDataContext();
// 開始回圈
foreach (DataRow dtO_Row in dtO.Rows)
{
//Main.ProgressBar1.Value = i + 1 // 顯示一下進度
// 檢查案號是否已經在資料庫中, dtO.CASENO, tbl_opd.CASENO
string strCASENO = (string)dtO_Row["CASENO"];
if (string.IsNullOrEmpty(strCASENO))
{
Logging.Record_error("在輸入門診資料時, 缺少案號CASENO");
log.Error("在輸入門診資料時, 缺少案號CASENO");
tb.ShowBalloonTip("錯誤!", "在輸入門診資料時, 缺少案號CASENO", BalloonIcon.Error);
// 下一個
continue;
}
var q = from o in dc.tbl_opd
where o.CASENO == strCASENO
select o;
if (q.Count() == 0) // 資料庫裡面沒有 INSERT
{
try
{
tbl_opd newOPD = new tbl_opd()
{
CASENO = strCASENO, // CASENO
VIST = (string)dtO_Row["VIST"], // VIST
RMNO = byte.Parse((string)dtO_Row["RMNO"]), // RMNO
uid = (string)dtO_Row["IDNO"], // uid
DEPTNAME = (string)dtO_Row["DEPTNAME"], // DEPTNAME
DOCTNAME = (string)dtO_Row["DOCTNAME"], // DOCTNAME
POSINAME = (string)dtO_Row["POSINAME"], // POSINAME
PAYNO = (string)dtO_Row["PAYNO"], // PAYNO
HEATH_CARD = (string)dtO_Row["HEATH_CARD"], // HEATH_CARD
ICDCODE1 = (string)dtO_Row["ICDCODE1"], // ICDCODE1
ICDCODE2 = (string)dtO_Row["ICDCODE2"], // ICDCODE2
ICDCODE3 = (string)dtO_Row["ICDCODE3"], // ICDCODE3
INS_CODE = "A", // INS_CODE, default value "A"
STEXT = (string)dtO_Row["STEXT"], // STEXT
OTEXT = (string)dtO_Row["OTEXT"], // OTEXT
QDATE = _qdate // 20200526 加入
};
string tempstr;
tempstr = dtO_Row["SDATE"].ToString();
if (DateTime.TryParse($"{tempstr.Substring(0, 4)}/{tempstr.Substring(4, 2)}/{tempstr.Substring(6, 2)}", out DateTime temp_date))
{
newOPD.SDATE = temp_date;
}
dc.tbl_opd.InsertOnSubmit(newOPD);
dc.SubmitChanges();
new_opd_N++;
// tbl_opd沒有資料, tbl_opd_order就一定沒有資料, 所以要加入, 這裡的挑戰是要加上醫令序
// datatable 此時不能使用LINQ查詢
List <DataRow> q2 = dtP.Select("CASENO='" + strCASENO + "'").ToList();
// 處理tbl_opd_order部分
int j = 1;
foreach (DataRow dtP_Row in q2)
{
tbl_opd_order newPr = new tbl_opd_order()
{
CASENO = strCASENO,
uid = (string)dtO_Row["IDNO"],
SDATE = temp_date,
OD_idx = (byte)(j + 1),
rid = (string)dtP_Row["CODE"], //CODE
TIMES_DAY = (string)dtP_Row["TIMES_DAY"], //TIMES_DAY
METHOD = (string)dtP_Row["METHODE"], //METHOD
TIME_QTY1 = (string)dtP_Row["TIME_QTY1"], //TIME_QTY1
DAYS = (string)dtP_Row["DAYS"], //DAYS
BILL_QTY = (string)dtP_Row["BILL_QTY"], //BILL_QTY
HC = (string)dtP_Row["HC"], //HC
PRICE = (string)dtP_Row["PRICE"], //PRICE
AMT = (string)dtP_Row["AMT"], //AMT
CLASS = (string)dtP_Row["CLASS"], //CLASS
CHRONIC = (string)dtP_Row["CHRONIC"], //CHRONIC
QDATE = _qdate // 20200526 加入
};
dc.tbl_opd_order.InsertOnSubmit(newPr);
dc.SubmitChanges();
j++;
}
}
catch (Exception ex)
{
log.Error(ex.Message);
tb.ShowBalloonTip("錯誤!", ex.Message, BalloonIcon.Error);
Logging.Record_error(ex.Message);
}
}
else // 資料庫裡已經有了, 檢查是否有異,有異UPDATE
{
// 先處理tbl_opd部分
tbl_opd oldOPD = (from p in dc.tbl_opd
where p.CASENO == strCASENO
select p).ToList()[0]; // this is a record
string strChange = string.Empty;
bool bChange = false;
try
{
string tempstr = string.Empty;
if (oldOPD.DEPTNAME != (string)dtO_Row["DEPTNAME"])
{
strChange += $"改科別: {oldOPD.DEPTNAME} => {dtO_Row["DEPTNAME"]}";
bChange = true;
oldOPD.DEPTNAME = (string)dtO_Row["DEPTNAME"]; // DEPTNAME
}
if (oldOPD.DOCTNAME != (string)dtO_Row["DOCTNAME"])
{
strChange += $"改醫師: {oldOPD.DOCTNAME} => {dtO_Row["DOCTNAME"]}";
bChange = true;
oldOPD.DOCTNAME = (string)dtO_Row["DOCTNAME"]; //DOCTNAME
}
if (oldOPD.POSINAME != (string)dtO_Row["POSINAME"])
{
strChange += $"改身分: {oldOPD.POSINAME} => {dtO_Row["POSINAME"]}";
bChange = true;
oldOPD.POSINAME = (string)dtO_Row["POSINAME"]; //POSINAME
}
if (oldOPD.PAYNO != (string)dtO_Row["PAYNO"])
{
strChange += $"改負擔: {oldOPD.PAYNO} => {dtO_Row["PAYNO"]}";
bChange = true;
oldOPD.PAYNO = (string)dtO_Row["PAYNO"]; //PAYNO
}
if (oldOPD.HEATH_CARD != (string)dtO_Row["HEATH_CARD"])
{
strChange += $"改卡號: {oldOPD.HEATH_CARD} => {dtO_Row["HEATH_CARD"]}";
bChange = true;
oldOPD.HEATH_CARD = (string)dtO_Row["HEATH_CARD"]; //HEATH_CARD
}
if (oldOPD.ICDCODE1 != (string)dtO_Row["ICDCODE1"])
{
strChange += $"改診斷1: {oldOPD.ICDCODE1} => {dtO_Row["ICDCODE1"]}";
bChange = true;
oldOPD.ICDCODE1 = (string)dtO_Row["ICDCODE1"]; //ICDCODE1
}
if (oldOPD.ICDCODE2 != (string)dtO_Row["ICDCODE2"])
{
strChange += $"改診斷2: {oldOPD.ICDCODE2} => {dtO_Row["ICDCODE2"]}";
bChange = true;
oldOPD.ICDCODE2 = (string)dtO_Row["ICDCODE2"]; //ICDCODE2
}
if (oldOPD.ICDCODE3 != (string)dtO_Row["ICDCODE3"])
{
strChange += $"改診斷3: {oldOPD.ICDCODE3} => {dtO_Row["ICDCODE3"]}";
bChange = true;
oldOPD.ICDCODE3 = (string)dtO_Row["ICDCODE3"]; //ICDCODE3
}
if (bChange == true)
{
// 做實改變
oldOPD.QDATE = _qdate;
dc.SubmitChanges();
change_opd_N++;
// 做記錄
Logging.Record_admin("update opd", $"{strCASENO}: {strChange}");
}
}
catch (Exception ex)
{
log.Error(strCASENO + ex.Message);
tb.ShowBalloonTip("錯誤!", strCASENO + ex.Message, BalloonIcon.Error);
Logging.Record_error(strCASENO + ex.Message);
}
// 再處理tbl_opd_order部分
// 先製造兩個list of tbl_opd_order
List <Prescription> oldPre = (from d in dc.tbl_opd_order
where d.CASENO == strCASENO
orderby d.rid, d.TIMES_DAY
select new Prescription()
{
CASENO = d.CASENO,
Rid = d.rid,
TIMES_DAY = d.TIMES_DAY,
METHOD = d.METHOD,
TIME_QTY1 = d.TIME_QTY1,
DAYS = d.DAYS,
BILL_QTY = d.BILL_QTY,
HC = d.HC,
PRICE = d.PRICE,
AMT = d.AMT,
CLAS = d.CLASS,
CHRONIC = d.CHRONIC
}).ToList();
List <Prescription> newPre = new List <Prescription>();
List <DataRow> q2 = dtP.Select($"CASENO='{strCASENO}'", "CODE, TIMES_DAY").ToList();
// 這個r.count一定大於等於1
// 處理tbl_opd_order部分
int totalP = q2.Count();
for (int j = 0; j < totalP; j++)
{
Prescription newP = new Prescription()
{
CASENO = strCASENO,
Rid = (string)q2[j]["CODE"], //CODE
TIMES_DAY = (string)q2[j]["TIMES_DAY"], //TIMES_DAY
METHOD = (string)q2[j]["METHODE"], //METHOD
TIME_QTY1 = (string)q2[j]["TIME_QTY1"], //TIME_QTY1
DAYS = (string)q2[j]["DAYS"], //DAYS
BILL_QTY = (string)q2[j]["BILL_QTY"], //BILL_QTY
HC = (string)q2[j]["HC"], //HC
PRICE = (string)q2[j]["PRICE"], //PRICE
AMT = (string)q2[j]["AMT"], //AMT
CLAS = (string)q2[j]["CLASS"], //CLASS
CHRONIC = (string)q2[j]["CHRONIC"] //CHRONIC
};
newPre.Add(newP);
}
// Now we have 2 lists now, but lists are only references
// 先比較兩者是否相同, 相同則跳下一筆
string strT = Exact(oldPre, newPre);
if (strT.Length != 0) // "" stands for identical
{
// 若不同則找出哪裡不同, 記錄下來
Logging.Record_admin("update opd order", $"{strCASENO}: {strT}");
// 最後把舊的刪掉, 插入新的
// 刪掉舊的
var q3 = from p in dc.tbl_opd_order
where p.CASENO == strCASENO
select p;
foreach (tbl_opd_order pr in q3)
{
dc.tbl_opd_order.DeleteOnSubmit(pr);
}
dc.SubmitChanges();
// 插入新的
// datatable 此時不能使用LINQ查詢
List <DataRow> q4 = dtP.Select($"CASENO='{strCASENO}'").ToList();
// 處理tbl_opd_order部分
int totalPr = q4.Count;
for (int j = 0; j < totalPr; j++)
{
tbl_opd_order newPr = new tbl_opd_order()
{
CASENO = strCASENO,
uid = oldOPD.uid,
SDATE = oldOPD.SDATE,
OD_idx = (byte)(j + 1),
rid = (string)q4[j]["CODE"], //CODE
TIMES_DAY = (string)q4[j]["TIMES_DAY"], //TIMES_DAY
METHOD = (string)q4[j]["METHODE"], //METHOD
TIME_QTY1 = (string)q4[j]["TIME_QTY1"], //TIME_QTY1
DAYS = (string)q4[j]["DAYS"], //DAYS
BILL_QTY = (string)q4[j]["BILL_QTY"], //BILL_QTY
HC = (string)q4[j]["HC"], //HC
PRICE = (string)q4[j]["PRICE"], //PRICE
AMT = (string)q4[j]["AMT"], //AMT
CLASS = (string)q4[j]["CLASS"], //CLASS
CHRONIC = (string)q4[j]["CHRONIC"], //CHRONIC
QDATE = _qdate // 20200526加入
};
dc.tbl_opd_order.InsertOnSubmit(newPr);
dc.SubmitChanges();
}
change_order_N++;
}
}
}
// 這樣的add opd沒什麼用
// Record_adm("add opd", dtO.TableName)
string summary = $"一共讀取{new_opd_N}筆新門診紀錄, 更改{change_opd_N}筆門診紀錄, 更改{change_order_N}筆醫令.";
tb.ShowBalloonTip("讀取完成", summary, BalloonIcon.Info);
log.Info(summary);
Logging.Record_admin("opd_import", summary);
dtO.Dispose();
dtP.Dispose();
ds.Dispose();
#endregion 進行讀取資料
this.Dispose();
}
public void Change()
{
// Dim output As DEP_return = Change_DEP(strYM)
// MessageBox.Show("修改了" + output.m.ToString + "筆, 請匯入門診資料")
string savepath = @"C:\vpn\change_dep";
int change_N;
DateTime minD;
DateTime maxD;
List <sp_change_depResult> ListChange;
// 存放目錄,不存在就要建立一個
if (!(System.IO.Directory.Exists(savepath)))
{
System.IO.Directory.CreateDirectory(savepath);
}
#region Making CSV
try
{
// 呼叫SQL stored procedure
using (CSDataContext dc = new CSDataContext())
{
ListChange = dc.sp_change_dep(_strYM).ToList();
}
// 自動產生名字
string savefile = $"\\change_dep_{_strYM}_{DateTime.Now.Year}{(DateTime.Now.Month + 100).ToString().Substring(1)}";
savefile += $"{(DateTime.Now.Day + 100).ToString().Substring(1)}_{DateTime.Now.TimeOfDay}";
savefile = savefile.Replace(":", "").Replace(".", "");
savepath += $"{savefile}.csv";
// 製作csv檔 writing to csv
System.IO.StreamWriter sw = new System.IO.StreamWriter(savepath);
int i = 1;
change_N = ListChange.Count;
if (change_N == 0)
{
tb.ShowBalloonTip("完成", "沒有什麼需要修改的", BalloonIcon.Info);
log.Info("change department: 沒有什麼需要修改的");
Logging.Record_admin("change department", "沒有什麼需要修改的");
}
else
{
minD = DateTime.Parse("9999/12/31");
maxD = DateTime.Parse("0001/01/01");
foreach (var c in ListChange)
{
sw.Write(c.o); // 欄位名叫o
if (i < change_N)
{
sw.Write(sw.NewLine);
}
DateTime tempD = DateTime.Parse($"{c.o.Substring(0, 4)}/{c.o.Substring(4, 2)}/{c.o.Substring(6, 2)}");
// 找尋最大的值
if (tempD.CompareTo(maxD) > 0)
{
maxD = tempD;
}
// 找尋最小的值
if (tempD.CompareTo(minD) < 0)
{
minD = tempD;
}
i++;
}
// 20200518 放在foreach的loop迴圈裡是錯誤的, 我把它放出來了
string output = $"{minD:d}~{maxD:d}, 共{change_N}筆需要修改";
tb.ShowBalloonTip("需修改:", output, BalloonIcon.Info);
log.Info($"change department: {output}");
Logging.Record_admin("change department", output);
sw.Close();
}
}
catch (System.Exception ex)
{
Logging.Record_error(ex.Message);
log.Error(ex.Message);
return;
}
#endregion Making CSV
#region Environment
try
{
// 營造環境
if (AutoItX.WinExists("看診清單") == 1) //如果直接存在就直接叫用
{
AutoItX.WinActivate("看診清單");
}
else
{
Thesis.LogIN();
// 打開"看診清單"
AutoItX.Run(@"C:\Program Files (x86)\THESE\杏雲醫療資訊系統\THCClinic.exe", @"C:\Program Files (x86)\THESE\杏雲醫療資訊系統\");
AutoItX.WinWaitActive("看診清單");
AutoItX.WinActivate("看診清單");
}
}
catch (Exception ex)
{
Logging.Record_error(ex.Message);
log.Error(ex.Message);
return;
}
#endregion Environment
#region Execute change department
try
{
string strD_o = string.Empty;
string strV_o = string.Empty;
string strR_o = string.Empty;
foreach (var c in ListChange)
{
string strD_n = c.o.Substring(0, 8);
string strV_n = c.o.Substring(8, 1);
string strR_n = c.o.Substring(9, 2);
bool changed = false;
string strNr = c.o.Substring(11, 3);
string strDEP = c.o.Substring(14, 2);
// 先檢查是否換日, 如果有就換到新日期;
if (strD_n != strD_o)
{
// 製造3個AutoIT VB程式, 1. changeDP_DATE, 針對"看診清單", [NAME:dtpSDate]
// 一個參數, 格式YYYYMMDD
AutoItX.Run($"C:\\vpn\\exe\\changeDP_DATE.exe {strD_n}", @"C:\vpn\exe\");
log.Info($"Change Date from {strD_o} to {strD_n}. Then sleep 1000ms.");
strD_o = strD_n;
changed = true;
AutoItX.Sleep(1000);
}
// 再檢查是否換午別, 如果有就換到新的午別
if (strV_n != strV_o)
{
// 製造3個AutoIT VB程式, 2. changeDP_VIST, 針對"看診清單", [NAME:cmbVist]
// 一個參數, 格式V
AutoItX.Run($"C:\\vpn\\exe\\changeDP_VIST.exe {strV_n}", @"C:\vpn\exe\");
log.Info($"Change Vist from {strV_o} to {strV_n}. Then sleep 1000ms.");
strV_o = strV_n;
changed = true;
AutoItX.Sleep(1000);
}
// 最後檢查是否換診間, 如果有就換到新的診間
if (strR_n != strR_o)
{
// 製造3個AutoIT VB程式, 3. changeDP_ROOM, 針對"看診清單", [NAME:cmbRmno]
// 一個參數, 格式RR
AutoItX.Run($"C:\\vpn\\exe\\changeDP_ROOM.exe {strR_n}", @"C:\vpn\exe\");
log.Info($"Change Date from {strR_o} to {strR_n}. Then sleep 1000ms.");
strR_o = strR_n;
changed = true;
AutoItX.Sleep(1000);
}
if (changed)
{
AutoItX.ControlClick("看診清單", "", "[NAME:btnRefresh]");
log.Info($"Press Refresh button. [NAME:btnRefresh].");
AutoItX.WinWaitActive("看診清單");
changed = false;
}
// 按下新的號碼;
// Seq NO
// 輸入診號
AutoItX.ControlSend("看診清單", "", "[NAME:txbSqno]", strNr);
log.Info($"Enter {strNr}.");
// 按鈕
AutoItX.ControlClick("看診清單", "", "[NAME:btnGo]");
log.Info($"Press GO button. [NAME:btnGo].");
// 進入問診畫面
AutoItX.WinWaitActive("問診畫面");
AutoItX.Sleep(500);
// 製造一個AutoIT VB程式, changeDP_DEP, 針對"問診畫面"[NAME:cmbDept]
// 一個參數, 格式DD
AutoItX.Run($"C:\\vpn\\exe\\changeDP_DEP.exe {strDEP}", @"C:\vpn\exe\");
AutoItX.Sleep(500);
int idx = 0;
// 先問「此病患已批價, 是否繼續?」, THCClinic, 還可能問重大傷病, 超過8種藥物, 可能有重複用藥畫面
do
{
AutoItX.Send("{F9}");
AutoItX.Sleep(100);
idx++; // time out for 10 sec at most
} while (AutoItX.WinExists("THCClinic") == 0 && idx < 100);
// 下一個畫面「確定要重複開立收據」,
// 一定會有「已經批價」, 可能會有「重大傷病身分」, 可能會有「八種以上藥物]
// 可能會有「跨院重複開立醫囑提示」
idx = 0;
do
{
if (AutoItX.WinExists("THCClinic") == 1)
{
AutoItX.ControlClick("THCClinic", "", "[CLASSNN:Button1]");
}
if (AutoItX.WinExists("跨院重複開立醫囑提示") == 1)
{
AutoItX.ControlClick("跨院重複開立醫囑提示", "", "[NAME:OK_Button]");
}
AutoItX.Sleep(100);
idx++; // time out for 10 sec at most
} while (AutoItX.WinExists("These.CludCln.Accounting") == 0 && idx < 100);
// 是否重印收據
AutoItX.WinWaitActive("These.CludCln.Accounting");
AutoItX.ControlClick("These.CludCln.Accounting", "", "[CLASSNN:Button2]");
AutoItX.Sleep(1000);
}
Logging.Record_admin("change department", $"修改了{change_N}筆");
tb.ShowBalloonTip("完成", $"修改了{change_N}筆, 請匯入門診資料.", BalloonIcon.Info);
}
catch (Exception ex)
{
Logging.Record_error(ex.Message);
log.Error(ex.Message);
return;
}
#endregion Execute change department
}
private async Task <ODRresult> ImportODR_async(object[,] data, IProgress <ProgressReportModel> progress, ProgressReportModel report)
{
int totalN = data.GetUpperBound(0);
int add_N = 0;
int change_N = 0;
int all_N = 0;
await Task.Run(() =>
{
log.Info($" enter ImporODR_async.");
// 要有迴路, 來讀一行一行的xls, 能夠判斷
for (int i = 0; i <= totalN; i++)
{
// 先判斷是否已經在資料表中, 如果不是就insert否則判斷要不要update
CSDataContext dc = new CSDataContext();
string strRID = string.Empty;
// 先判斷醫令代碼是否空白, 原本第1, 現在第0
if (string.IsNullOrEmpty((string)data[i, 0]))
{
// 寫入Error Log
// 沒有醫令代碼是不行的
//Logging.Record_error("醫令代碼是空的");
log.Error("醫令代碼是空的");
// 20200528 發現這裡用return是不對的, continue才對
//return;
continue;
}
// 再判斷是否已在資料表中
strRID = (string)data[i, 0]; //醫令代碼,第0欄
var od = from d in dc.p_order
where d.rid == strRID
select d; // this is a querry
if (od.Count() == 0)
{
// insert
// 沒這個醫令可以新增這個醫令
// 填入資料
try
{
p_order newOd = new p_order()
{
rid = strRID,
r01 = (string)data[i, 1], //英文規格, 第2欄, 20200513 改成1欄
r02 = (string)data[i, 2], //生效日期,第3欄, 20200513 改成2欄
r03 = (string)data[i, 3], //截止日期,第4欄, 20200513 改成3欄
r04 = (string)data[i, 4], // 健保碼, 第5欄, 20200513 改成4欄
r06 = (string)data[i, 6], //中文規格, 第7欄, 20200513 改成6欄
r07 = (string)data[i, 7], //學名, 第8欄, 20200513 改成7欄
r08 = (string)data[i, 8], //類別,第9欄, 20200513 改成8欄
r09 = (string)data[i, 9], //健保價,第10欄, 20200513 改成9欄
r10 = (string)data[i, 10], //自費價, 第11欄, 20200513 改成10欄
r13 = (string)data[i, 13], //使用單位, 第14欄, 20190611 改成第16欄, 20200319 改成14欄, 20200513 改成13欄
r14 = (string)data[i, 11], //批價單位, 第15欄, 20190611 改成第14欄, 20200319 改成12欄, 20200513 改成11欄
r15 = (string)data[i, 14], //頻率, 第16欄, 20190611 改成第17欄, 20200319 改成15欄, 20200513 改成14欄
r16 = (string)data[i, 15], //途徑, 第17欄, 20190611 改成第18欄, 20200319 改成16欄, 20200513 改成15欄
r18 = (string)data[i, 17], //調劑方式, 第19欄, 20190611 改成第20欄, 20200319 改成18欄, 20200513 改成17欄
r19 = (string)data[i, 12], //批價比率, 第20欄, 20190611 改成第15欄, 20200319 改成13欄, 20200513 改成12欄
r25 = (string)data[i, 23], //劑型, 第26欄, 20200319 改成24欄, 20200513 改成23欄
r26 = (string)data[i, 24], //副作用, 第27欄, 20200319 改成25欄, 20200513 改成24欄
r27 = (string)data[i, 25], //用途, 第28欄, 20200319 改成26欄, 20200513 改成25欄
r28 = (string)data[i, 26], //用藥指示, 第29欄, 20200319 改成27欄, 20200513 改成26欄
r29 = (string)data[i, 27], //外觀, 第30欄, 20200319 改成28欄, 20200513 改成27欄
r30 = (string)data[i, 28], //程分含量, 第31欄, 20200319 改成29欄, 20200513 改成28欄
r31 = (string)data[i, 29], //廠牌, 第32欄, 20200319 改成30欄, 20200513 改成29欄
r32 = (string)data[i, 30], //用藥/排程說明, 第33欄, 20200319 改成31欄, 20200513 改成30欄
r33 = (string)data[i, 31], //藥品備註, 第34欄, 20200319 改成32欄, 20200513 改成31欄
r34 = (string)data[i, 32], //許可證字號, 第35欄, 20200319 改成33欄, 20200513 改成32欄
r40 = (string)data[i, 38], //檢驗代碼, 第41欄, 20200319 改成39欄, 20200513 改成38欄
r48 = (string)data[i, 46], //管制藥品, 第49欄, 20190611 改成第48欄, 20200319 改成47欄, 20200513 改成46欄
r52 = (string)data[i, 52], //門診缺藥, 第53欄, 20190929 改成第54欄, 20200319 改成53欄, 20200513 改成52欄
r60 = (string)data[i, 60], //異動人員, 第61欄, 20190929 改成第62欄, 20200319 改成61欄, 20200513 改成60欄
r61 = (string)data[i, 61], //異動日期, 第62欄, 20190929 改成第63欄, 20200319 改成62欄, 20200513 改成61欄
QDATE = _qdate
};
dc.p_order.InsertOnSubmit(newOd);
dc.SubmitChanges();
Logging.Record_admin("Add a new order", strRID);
log.Info($"Add a new order: {strRID} {data[i, 1]}");
add_N++;
}
catch (Exception ex)
{
Logging.Record_error(ex.Message);
log.Error(ex.Message);
}
}
else
{
// update
// 有此醫令喔, 走update方向
// 拿oldOd比較data[i),如果不同就修改,並且記錄
p_order oldOd = (from d in dc.p_order
where d.rid == strRID
select d).First(); // this is a record
string strChange = string.Empty;
bool bChange = false;
try
{
// 英文規格, 第2欄, 20200513 改成1欄
if (oldOd.r01 != (string)data[i, 1])
{
strChange += $"改英文規格: {oldOd.r01} => {data[i, 1]}; ";
bChange = true;
oldOd.r01 = (string)data[i, 1];
}
// 生效日期,第3欄, 20200513 改成2欄
if (oldOd.r02 != (string)data[i, 2])
{
strChange += $"改生效日期: {oldOd.r02} => {data[i, 2]}; ";
bChange = true;
oldOd.r02 = (string)data[i, 2];
}
// 截止日期,第4欄, 20200513 改成3欄
if (oldOd.r03 != (string)data[i, 3])
{
strChange += $"改截止日期: {oldOd.r03} => {data[i, 3]}; ";
bChange = true;
oldOd.r03 = (string)data[i, 3];
}
// 健保碼, 第5欄, 20200513 改成4欄
if (oldOd.r04 != (string)data[i, 4])
{
strChange += $"改健保碼: {oldOd.r04} => {data[i, 4]}; ";
bChange = true;
oldOd.r04 = (string)data[i, 4];
}
// 中文規格, 第7欄, 20200513 改成6欄
if (oldOd.r06 != (string)data[i, 6])
{
strChange += $"改中文規格: {oldOd.r06} => {data[i, 6]}; ";
bChange = true;
oldOd.r06 = (string)data[i, 6];
}
// 學名, 第8欄, 20200513 改成7欄
if (oldOd.r07 != (string)data[i, 7])
{
strChange += $"改學名: {oldOd.r07} => {data[i, 7]}; ";
bChange = true;
oldOd.r07 = (string)data[i, 7];
}
// 類別,第9欄, 20200513 改成8欄
if (oldOd.r08 != (string)data[i, 8])
{
strChange += $"改類別: {oldOd.r08} => {data[i, 8]}; ";
bChange = true;
oldOd.r08 = (string)data[i, 8];
}
// 健保價,第10欄, 20200513 改成9欄
if (oldOd.r09 != (string)data[i, 9])
{
strChange += $"改健保價: {oldOd.r09} => {data[i, 9]}; ";
bChange = true;
oldOd.r09 = (string)data[i, 9];
}
// 自費價, 第11欄, 20200513 改成10欄
if (oldOd.r10 != (string)data[i, 10])
{
strChange += $"改自費價: {oldOd.r10} => {data[i, 10]}; ";
bChange = true;
oldOd.r10 = (string)data[i, 10];
}
// 使用單位, 第14欄, 20190611 改成第16欄, 20200319 改成14欄, 20200513 改成13欄
if (oldOd.r13 != (string)data[i, 13])
{
strChange += $"改使用單位: {oldOd.r13} => {data[i, 13]}; ";
bChange = true;
oldOd.r13 = (string)data[i, 13];
}
// 批價單位, 第15欄, 20190611 改成第14欄, 20200319 改成12欄, 20200513 改成11欄
if (oldOd.r14 != (string)data[i, 11])
{
strChange += $"改批價單位: {oldOd.r14} => {data[i, 11]}; ";
bChange = true;
oldOd.r14 = (string)data[i, 11];
}
// 頻率, 第16欄, 20190611 改成第17欄, 20200319 改成15欄, 20200513 改成14欄
if (oldOd.r15 != (string)data[i, 14])
{
strChange += $"改頻率: {oldOd.r15} => {data[i, 14]}; ";
bChange = true;
oldOd.r15 = (string)data[i, 14];
}
// 途徑, 第17欄, 20190611 改成第18欄, 20200319 改成16欄, 20200513 改成15欄
if (oldOd.r16 != (string)data[i, 15])
{
strChange += $"改途徑: {oldOd.r16} => {data[i, 15]}; ";
bChange = true;
oldOd.r16 = (string)data[i, 15];
}
// 調劑方式, 第19欄, 20190611 改成第20欄, 20200319 改成18欄, 20200513 改成17欄
if (oldOd.r18 != (string)data[i, 17])
{
strChange += $"改調劑方式: {oldOd.r18} => {data[i, 17]}; ";
bChange = true;
oldOd.r18 = (string)data[i, 17];
}
// 批價比率, 第20欄, 20190611 改成第15欄, 20200319 改成13欄, 20200513 改成12欄
if (oldOd.r19 != (string)data[i, 12])
{
strChange += $"改批價比率: {oldOd.r19} => {data[i, 12]}; ";
bChange = true;
oldOd.r19 = (string)data[i, 12];
}
// 劑型, 第26欄, 20200319 改成24欄, 20200513 改成23欄
if (oldOd.r25 != (string)data[i, 23])
{
strChange += $"改劑型: {oldOd.r25} => {data[i, 23]}; ";
bChange = true;
oldOd.r25 = (string)data[i, 23];
}
// 副作用, 第27欄, 20200319 改成25欄, 20200513 改成24欄
if (oldOd.r26 != (string)data[i, 24])
{
strChange += $"改副作用: {oldOd.r26} => {data[i, 24]}; ";
bChange = true;
oldOd.r26 = (string)data[i, 24];
}
// 用途, 第28欄, 20200319 改成26欄, 20200513 改成25欄
if (oldOd.r27 != (string)data[i, 25])
{
strChange += $"改用途: {oldOd.r27} => {data[i, 25]}; ";
bChange = true;
oldOd.r27 = (string)data[i, 25];
}
// 用藥指示, 第29欄, 20200319 改成27欄, 20200513 改成26欄
if (oldOd.r28 != (string)data[i, 26])
{
strChange += $"改用藥指示: {oldOd.r28} => {data[i, 26]}; ";
bChange = true;
oldOd.r28 = (string)data[i, 26];
}
// 外觀, 第30欄, 20200319 改成28欄, 20200513 改成27欄
if (oldOd.r29 != (string)data[i, 27])
{
strChange += $"改外觀: {oldOd.r29} => {data[i, 27]}; ";
bChange = true;
oldOd.r29 = (string)data[i, 27];
}
// 成分含量, 第31欄, 20200319 改成29欄, 20200513 改成28欄
if (oldOd.r30 != (string)data[i, 28])
{
strChange += $"改成分含量: {oldOd.r30} => {data[i, 28]}; ";
bChange = true;
oldOd.r30 = (string)data[i, 28];
}
// 廠牌, 第32欄, 20200319 改成30欄, 20200513 改成29欄
if (oldOd.r31 != (string)data[i, 29])
{
strChange += $"改廠牌: {oldOd.r31} => {data[i, 29]}; ";
bChange = true;
oldOd.r31 = (string)data[i, 29];
}
// 用藥/排程說明, 第33欄, 20200319 改成31欄, 20200513 改成30欄
if (oldOd.r32 != (string)data[i, 30])
{
strChange += $"改用藥排程說明: {oldOd.r32} => {data[i, 30]}; ";
bChange = true;
oldOd.r32 = (string)data[i, 30];
}
// 藥品備註, 第34欄, 20200319 改成32欄, 20200513 改成31欄
if (oldOd.r33 != (string)data[i, 31])
{
strChange += $"改藥品備註: {oldOd.r33} => {data[i, 31]}; ";
bChange = true;
oldOd.r33 = (string)data[i, 31];
}
// 許可證字號, 第35欄, 20200319 改成33欄, 20200513 改成32欄
if (oldOd.r34 != (string)data[i, 32])
{
strChange += $"改許可證字號: {oldOd.r34} => {data[i, 32]}; ";
bChange = true;
oldOd.r34 = (string)data[i, 32];
}
// 檢驗代碼, 第41欄, 20200319 改成39欄, 20200513 改成38欄
if (oldOd.r40 != (string)data[i, 38])
{
strChange += $"改檢驗代碼: {oldOd.r40} => {data[i, 38]}; ";
bChange = true;
oldOd.r40 = (string)data[i, 38];
}
// 管制藥品, 第49欄, 20190611 改成第48欄, 20200319 改成47欄, 20200513 改成46欄
if (oldOd.r48 != (string)data[i, 46])
{
strChange += $"改管制藥品: {oldOd.r48} => {data[i, 46]}; ";
bChange = true;
oldOd.r48 = (string)data[i, 46];
}
// 門診缺藥, 第53欄, 20190929 改成第54欄, 20200319 改成53欄, 20200513 改成52欄
if (oldOd.r52 != (string)data[i, 52])
{
strChange += $"改門診缺藥: {oldOd.r52} => {data[i, 52]}; ";
bChange = true;
oldOd.r52 = (string)data[i, 52];
}
// 異動人員, 第61欄, 20190929 改成第62欄, 20200319 改成61欄, 20200513 改成60欄
if (oldOd.r60 != (string)data[i, 60])
{
strChange += $"改異動人員: {oldOd.r60} => {data[i, 60]}; ";
bChange = true;
oldOd.r60 = (string)data[i, 60];
}
// 異動日期, 第62欄, 20190929 改成第63欄, 20200319 改成62欄, 20200513 改成61欄
if (oldOd.r61 != (string)data[i, 61])
{
strChange += $"改異動日期: {oldOd.r61} => {data[i, 61]}; ";
bChange = true;
oldOd.r61 = (string)data[i, 61];
}
if (bChange == true)
{
// 做實改變
oldOd.QDATE = _qdate;
dc.SubmitChanges();
// 做記錄
Logging.Record_admin("Change order data", $"{strRID}: {strChange}");
log.Info($"Change order data: [{strRID}] {data[i, 1]}: {strChange}");
change_N++;
}
}
catch (Exception ex)
{
Logging.Record_error(ex.Message);
log.Error(ex.Message);
}
}
all_N++;
report.PercentageComeplete = all_N * 100 / totalN;
progress.Report(report);
}
log.Info($" exit ImporODR_async.");
});
return(new ODRresult()
{
NewODR = add_N,
ChangeODR = change_N,
AllODR = all_N
});
}
public void Transform()
{
#region 宣告
XmlDocument xdoc = new XmlDocument();
XmlNode xOutpatient; // root of the xml
XmlNodeList xTDATA; // 用來放tDATA
XmlNodeList xDDATA; // 用來放dDATA
XmlNode xNodeTemp; // 臨時的xml node操作
string keyT3; // 當key值, 費用年月
string keyD1; // 當key值, 案件分類
string keyD2; // 當key值,流水編號
int dN = 0; // dData筆數, 即當月看診人次數
int pN = 0; // pData筆數, 即當月處方筆數
//讀取XML
// 20190615 revisited: 原本想說建立防呆機制, 結果一看才發現有天然的防呆機制,就是primary key的設置, 三個表都有,有重複值自然就不會讀了
xdoc.Load(_loadpath);
// root node就是outpatient, outpatient下面就是兩個node: tdata,
xOutpatient = (XmlNode)xdoc.DocumentElement;
//選擇section
xTDATA = xOutpatient.SelectNodes("tdata"); //這應該只有一個
xDDATA = xOutpatient.SelectNodes("ddata"); //這應該有很多個
#endregion 宣告
#region 讀取tdata
try
{
//TDATA只有一個item
xNodeTemp = xTDATA.Item(0);
//這個唯一的item下面有42個child node/item, 就是總表了
//20190527 我已經搞懂總表了,可以寫入SQL了
//以下寫入SQL
// 總表重複12次, xml_tdata
// 宣告新的一行
keyT3 = xNodeTemp.SelectSingleNode("t3").InnerText;
// 如果已經曾經匯入, 就不要重複匯入, t3是key值, 不可重複
using (CSDataContext dc = new CSDataContext())
{
var q = from p in dc.xml_tdata
where p.t3 == keyT3
select p;
if (q.Count() != 0)
{
throw new Exception($"{keyT3} 之前已經匯入, 不可重複匯入!");
}
}
xml_tdata newT = new xml_tdata()
{
t1 = xNodeTemp.SelectSingleNode("t1").InnerText,
t2 = xNodeTemp.SelectSingleNode("t2").InnerText,
t3 = xNodeTemp.SelectSingleNode("t3").InnerText,
t4 = char.Parse(xNodeTemp.SelectSingleNode("t4").InnerText),
t5 = char.Parse(xNodeTemp.SelectSingleNode("t5").InnerText),
t6 = xNodeTemp.SelectSingleNode("t6").InnerText,
t37 = int.Parse(xNodeTemp.SelectSingleNode("t37").InnerText),
t38 = int.Parse(xNodeTemp.SelectSingleNode("t38").InnerText),
t39 = int.Parse(xNodeTemp.SelectSingleNode("t39").InnerText),
t40 = int.Parse(xNodeTemp.SelectSingleNode("t40").InnerText),
t41 = xNodeTemp.SelectSingleNode("t41").InnerText,
t42 = xNodeTemp.SelectSingleNode("t42").InnerText
};
//20190527 完成
using (CSDataContext dc = new CSDataContext())
{
dc.xml_tdata.InsertOnSubmit(newT);
dc.SubmitChanges();
string output = $"匯入健保申報檔年月: {keyT3}";
Logging.Record_admin("匯入健保申報檔", output);
log.Info(output);
}
}
catch (Exception ex)
{
string output = $"讀取tdata錯誤: {ex.Message}";
Logging.Record_error(output);
log.Error(output);
tb.ShowBalloonTip("錯誤!", output, BalloonIcon.Error);
return;
}
#endregion 讀取tdata
#region 讀取ddata, pdata
//=====================================================================================
//2019/5/27 完成的
//DDATA有很多個item
foreach (XmlNode dNode in xDDATA)
{
//取得節點[dhead]
xNodeTemp = dNode.SelectSingleNode("dhead");
keyD1 = xNodeTemp.SelectSingleNode("d1").InnerText;
keyD2 = xNodeTemp.SelectSingleNode("d2").InnerText;
//取得節點[dbody]
xNodeTemp = dNode.SelectSingleNode("dbody");
//取得ddata, 下面應有dhead, dbody兩個node, dhead下有d1, d2, dbody下有30欄位
//------------> as ddata
try
{
// 宣告新的一行
xml_ddata newD = new xml_ddata()
{
t3 = keyT3,
d1 = keyD1,
d2 = int.Parse(keyD2),
d3 = xNodeTemp.SelectSingleNode("d3")?.InnerText,
d4 = xNodeTemp.SelectSingleNode("d4")?.InnerText,
d8 = xNodeTemp.SelectSingleNode("d8")?.InnerText,
d9 = xNodeTemp.SelectSingleNode("d9")?.InnerText,
d11 = xNodeTemp.SelectSingleNode("d11")?.InnerText,
d15 = xNodeTemp.SelectSingleNode("d15")?.InnerText,
d16 = xNodeTemp.SelectSingleNode("d16")?.InnerText,
d17 = xNodeTemp.SelectSingleNode("d17")?.InnerText,
d18 = char.Parse(xNodeTemp.SelectSingleNode("d18")?.InnerText),
d19 = xNodeTemp.SelectSingleNode("d19")?.InnerText,
d20 = xNodeTemp.SelectSingleNode("d20")?.InnerText,
d21 = xNodeTemp.SelectSingleNode("d21")?.InnerText,
d22 = xNodeTemp.SelectSingleNode("d22")?.InnerText,
d23 = xNodeTemp.SelectSingleNode("d23")?.InnerText,
d28 = char.Parse(xNodeTemp.SelectSingleNode("d28")?.InnerText),
d29 = xNodeTemp.SelectSingleNode("d29")?.InnerText,
d30 = xNodeTemp.SelectSingleNode("d30")?.InnerText,
d32 = int.Parse(xNodeTemp.SelectSingleNode("d32")?.InnerText),
d33 = int.Parse(xNodeTemp.SelectSingleNode("d33")?.InnerText),
d34 = int.Parse(xNodeTemp.SelectSingleNode("d34")?.InnerText),
d35 = xNodeTemp.SelectSingleNode("d35")?.InnerText,
d36 = int.Parse(xNodeTemp.SelectSingleNode("d36")?.InnerText),
d39 = int.Parse(xNodeTemp.SelectSingleNode("d39")?.InnerText),
d40 = int.Parse(xNodeTemp.SelectSingleNode("d40")?.InnerText),
d41 = int.Parse(xNodeTemp.SelectSingleNode("d41")?.InnerText),
d49 = xNodeTemp.SelectSingleNode("d49")?.InnerText
};
if (int.TryParse(xNodeTemp.SelectSingleNode("d27")?.InnerText, out int i27))
{
newD.d27 = i27;
}
if (!string.IsNullOrEmpty(xNodeTemp.SelectSingleNode("d14")?.InnerText))
{
newD.d14 = char.Parse(xNodeTemp.SelectSingleNode("d14").InnerText);
}
using (CSDataContext dc = new CSDataContext())
{
dc.xml_ddata.InsertOnSubmit(newD);
dc.SubmitChanges();
dN++;
}
}
catch (Exception ex)
{
string output = $"讀取ddata錯誤: {ex.Message}";
Logging.Record_error(output);
log.Error(output);
tb.ShowBalloonTip("錯誤!", output, BalloonIcon.Error);
continue;
}
//取得[dbody]下的節點[pdata],這可能有很多個,也可能沒有半個, 要有個if, 要有個迴圈for next
if (xNodeTemp.SelectNodes("pdata").Count != 0)
{
XmlNodeList xPDATA = xNodeTemp.SelectNodes("pdata");
foreach (XmlNode pNode in xPDATA)
{
try
{
xml_pdata newP = new xml_pdata()
{
t3 = keyT3,
d1 = keyD1,
d2 = int.Parse(keyD2),
p4 = pNode.SelectSingleNode("p4")?.InnerText,
p6 = pNode.SelectSingleNode("p6").InnerText,
p7 = pNode.SelectSingleNode("p7").InnerText,
p8 = double.Parse(pNode.SelectSingleNode("p8").InnerText),
p9 = pNode.SelectSingleNode("p9").InnerText,
p10 = (int)float.Parse(pNode.SelectSingleNode("p10")?.InnerText),
p11 = (int)float.Parse(pNode.SelectSingleNode("p11")?.InnerText),
p12 = int.Parse(pNode.SelectSingleNode("p12")?.InnerText),
p13 = int.Parse(pNode.SelectSingleNode("p13")?.InnerText),
p14 = pNode.SelectSingleNode("p14")?.InnerText,
p15 = pNode.SelectSingleNode("p15")?.InnerText,
p16 = pNode.SelectSingleNode("p16")?.InnerText,
p20 = pNode.SelectSingleNode("p20")?.InnerText
};
if (int.TryParse(pNode.SelectSingleNode("p1")?.InnerText, out int i1))
{
newP.p1 = i1;
}
if (!string.IsNullOrEmpty(pNode.SelectSingleNode("p2")?.InnerText))
{
newP.p2 = char.Parse(pNode.SelectSingleNode("p2").InnerText);
}
if (!string.IsNullOrEmpty(pNode.SelectSingleNode("p3")?.InnerText))
{
newP.p3 = char.Parse(pNode.SelectSingleNode("p3")?.InnerText);
}
if (double.TryParse(pNode.SelectSingleNode("p5")?.InnerText, out double i5))
{
newP.p5 = i5;
}
if (!string.IsNullOrEmpty(pNode.SelectSingleNode("p17")?.InnerText))
{
newP.p17 = char.Parse(pNode.SelectSingleNode("p17")?.InnerText);
}
using (CSDataContext dc = new CSDataContext())
{
dc.xml_pdata.InsertOnSubmit(newP);
dc.SubmitChanges();
pN++;
}
}
catch (Exception ex)
{
string output = $"讀取pdata錯誤: {ex.Message}";
Logging.Record_error(output);
log.Error(output);
tb.ShowBalloonTip("錯誤!", output, BalloonIcon.Error);
continue;
}
}
}
}
#endregion 讀取ddata, pdata
#region 進行配對
//20190615 連結tbl_opd
using (CSDataContext dc = new CSDataContext())
{
var q = (from cs in dc.sp_match_xml().AsEnumerable()
select cs).First();
int n = q.rows_affected;
string output = $"健保上傳XML檔配對{n}筆配對成功, 共{dN}看診人次, {pN}處方";
Logging.Record_admin("健保上傳XML檔配對", output);
log.Info(output);
tb.ShowBalloonTip("匯入成功!", output, BalloonIcon.Info);
}
#endregion 進行配對
this.Dispose();
}
