public string LISVPNCreate(string sHospID, string sDate)
{
string sResult = String.Empty;
if (!String.IsNullOrEmpty(sDate))
{
// 切割日期資料取得日期
DateTime dtDate = Convert.ToDateTime(sDate);
string sdt = (dtDate.Year - 1911).ToString("000") + (dtDate.Month).ToString("00") + (dtDate.Day).ToString("00") + "000000";
string edt = (dtDate.Year - 1911).ToString("000") + (dtDate.Month).ToString("00") + (dtDate.Day).ToString("00") + "235959";
if (!String.IsNullOrEmpty(sHospID))
{
GetLink();
LIS_HISEntities db_his = new LIS_HISEntities(myClass.GetSQLConnectionString(dbs2, "his" + sHospID, userid, password, LIS_HISEntities_String));
ZMLISEntities db_zmlis = new ZMLISEntities();
// 設定 ADO 連結
string sbconn = String.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True; User ID={2}; Password={3}", dbs2, "his" + sHospID, userid, password);
try
{
List <string> headerKey = new List <string>();
headerKey.Clear();
// 從 IC 卡上面取得的資料以 A17(就診日期時間，格式:yyyy-MM-dd HH:mm:ss) 做為 Key 來做清除資料依據
// A17 (就診日期時間): 基本上是以病人插入健保卡時的讀卡時間,所以,理論上不會有重覆時間的問題,精細計算單位至秒
var dt_icheader = (from ich in db_his.hisICHeader
where ich.A17.CompareTo(sdt) >= 1 && ich.A17.CompareTo(edt) <= 1
select ich);
List <string> key_delete = new List <string>();
if (dt_icheader != null)
{
foreach (var icHeader in dt_icheader)
{
headerKey.Add(icHeader.A17);
}
// 取得與匯入資料相同的檢驗單號
var r_header = from e in db_his.hisICHeader where e.A17.CompareTo(sdt) >= 1 && e.A17.CompareTo(edt) <= 1 select e;
foreach (var a in r_header)
{
key_delete.Add(a.ICHRowid);
}
}
if (key_delete.Count > 0)
{
db_his.lisVPN2PLM.Where(x => key_delete.Contains(x.ICHRowid)).Delete();
db_his.lisVPNHData.Where(x => key_delete.Contains(x.ICHRowid)).Delete();
db_his.lisVPNRData.Where(x => key_delete.Contains(x.ICHRowid)).Delete();
}
#region 處理IC卡上傳資料與檢驗單據做媒合
// 把IC上傳VPN資料Header段與檢驗單據做媒合
// Key:看診日期 + 身份證字號
// 把取到媒合的檢驗單據放到 lisVPN2PLM table 內
var db_v2p = (from ic_hdata in db_his.hisICHeader
where ic_hdata.ICHClinicDate >= dtDate && ic_hdata.ICHClinicDate <= dtDate
join lpm in db_his.lisPatientLaboratoryMaster
on new { q1 = ic_hdata.ICHClinicDate, q2 = ic_hdata.A12 } equals new { q1 = (DateTime)lpm.PLMClinicDate, q2 = lpm.PLMPTIdno }
select new
{
V2LSRowid = Guid.NewGuid().ToString(),
ICHRowid = ic_hdata.ICHRowid,
PLMRowid = lpm.PLMRowid,
PLMSNo = lpm.PLMSNo,
LLSLabID = lpm.LLSLabID
});
string sLLSLabID = String.Empty;
if (db_v2p != null)
{
sLLSLabID = db_v2p.First().LLSLabID;
}
// 取得資料單媒合的 Key
List <string> v2pKey = new List <string>();
foreach (var i in db_v2p)
{
v2pKey.Add(i.PLMSNo);
}
// 先把資料清除,清除 Key:PLMSNo(檢驗單號)
db_his.lisVPN2PLM.Where(x => v2pKey.Contains(x.PLMSNo)).Delete();
// 把 db_v2p 寫到資料庫內
DataTable dt_v2p = myClass.LinqQueryToDataTable(db_v2p);
if (dt_v2p.Rows.Count > 0)
{
using (SqlBulkCopy bcp_v2p = new SqlBulkCopy(sbconn))
{
bcp_v2p.DestinationTableName = "lisVPN2PLM";
bcp_v2p.ColumnMappings.Add("V2LSRowid", "V2LSRowid");
bcp_v2p.ColumnMappings.Add("ICHRowid", "ICHRowid");
bcp_v2p.ColumnMappings.Add("PLMRowid", "PLMRowid");
bcp_v2p.ColumnMappings.Add("PLMSNo", "PLMSNo");
bcp_v2p.WriteToServer(dt_v2p);
}
}
#endregion
#region 媒合好的資料匯入到 MSSQL 內
// 處理檢驗上傳基本資料段-依每個醫令逐一儲存
var db_ich = (from ic_hdata in db_his.hisICHeader
where ic_hdata.ICHClinicDate >= dtDate && ic_hdata.ICHClinicDate <= dtDate
join ic_bdata in db_his.hisICBody on ic_hdata.ICHRowid equals ic_bdata.ICHRowid
join vpn in db_his.lisLaboratoryVPNItem on ic_bdata.A73 equals vpn.LIVMNHICode
select new
{
VHDRowid = "", //ic_hdata.ICHRowid,
ICHRowid = ic_hdata.ICHRowid,
h1 = ic_hdata.A00,
h2 = ic_hdata.A14,
h3 = "11",
h4 = ic_hdata.A17.Substring(0, 5),
h5 = ic_hdata.A17,
h6 = ic_hdata.A23,
h7 = ic_hdata.A18,
h8 = ic_hdata.A19,
h9 = ic_hdata.A12,
h10 = ic_hdata.A13,
h11 = ic_hdata.A17.Substring(0, 7),
h12 = "",
h13 = "",
h14 = "",
h15 = ic_bdata.A73,
h16 = "",
h17 = ic_hdata.A15,
h18 = "",
h19 = ic_hdata.A17.Substring(0, 7) + "0000",
h20 = "", // From lisSys
h21 = "", // From lisSys
h22 = "", // From lisSys
h23 = ic_hdata.A20,
h24 = ic_hdata.A21,
h25 = ""
}).AsEnumerable();
// 取出符合VPN檢驗上傳名單的健保碼檢驗單資料，LisVPN2PLM 是已經媒合好的病人所屬的檢驗單
// 取出該診所的檢驗項目:需在診所所屬的結構表內取出檢驗項目清單
var db_lisItemData = (from sh in db_zmlis.sysHospital
where sh.HospID == sHospID
join lls in db_zmlis.lisLaboratorySchedule on sh.HospRowid equals lls.HospRowid
where lls.LLSLabID == sLLSLabID
join llm in db_zmlis.lisLaboratoryMaster on lls.LLMRowid equals llm.LLMRowid
join lhli in db_zmlis.lisHospitalLaboratoryItem on llm.LLMRowid equals lhli.LLMRowid
join lli in db_zmlis.lisLaboratoryItem on lhli.LLIRowid equals lli.LLIRowid
select new
{
lhli.HLICode,
lli.LLINhiCode,
lli.LLIUnit,
lli.LLIUp_Male,
lli.LLILo_Male,
lli.LLIUp_Female,
lli.LLILo_Female
}).AsEnumerable();
// 取得檢驗資料媒合
var db_lisData = (from a1 in db_his.lisVPN2PLM
join lplm in db_his.lisPatientLaboratoryMaster on a1.PLMRowid equals lplm.PLMRowid
join lpld in db_his.lisPatientLaboratoryDetail on lplm.PLMRowid equals lpld.PLMRowid
select new
{
a1.V2LSRowid,
a1.ICHRowid,
a1.PLMRowid,
lplm.PLMPTIdno,
lplm.PLMClinicDate,
lplm.PLMApplyDate,
lplm.PLMReportDate,
lplm.PLMSNo,
lpld.PLDCode,
lpld.PLDName,
PLDValue = (lpld.PLDValue != 0) ? lpld.PLDValue.ToString() : lpld.PLDStrValue,
lpld.PLDUnit
}).AsEnumerable();
var db = (from a1 in db_lisData
join lid in db_lisItemData on a1.PLDCode equals lid.HLICode
select new
{
a1.V2LSRowid,
a1.ICHRowid,
a1.PLMRowid,
a1.PLMPTIdno,
a1.PLMClinicDate,
a1.PLMApplyDate,
a1.PLMReportDate,
a1.PLMSNo,
a1.PLDCode,
lid.LLINhiCode,
a1.PLDName,
a1.PLDValue,
PLDUnit = (String.IsNullOrEmpty(a1.PLDUnit)) ? lid.LLIUnit : a1.PLDUnit,
lid.LLIUp_Male,
lid.LLILo_Male,
lid.LLIUp_Female,
lid.LLILo_Female
}).ToList();
var result = (from ich in db_ich
join a in db on
new { o1 = ich.ICHRowid, o2 = ich.h15.Trim() }
equals new { o1 = a.ICHRowid, o2 = a.LLINhiCode.Trim() }
orderby ich.ICHRowid
select new
{
VHDRowid = ich.VHDRowid,
ICHRowid = ich.ICHRowid,
h1 = ich.h1,
h2 = ich.h2,
h3 = ich.h3,
h4 = ich.h4,
h5 = ich.h5,
h6 = ich.h6,
h7 = ich.h7,
h8 = ich.h8,
h9 = ich.h9,
h10 = ich.h10,
h11 = ich.h11,
h12 = ich.h12,
h13 = ich.h13,
h14 = ich.h14,
h15 = ich.h15,
h16 = ich.h16,
h17 = ich.h17,
h18 = ich.h18,
h19 = ich.h19,
h20 = (a.PLMApplyDate.Value.Year - 1911).ToString("000") + (a.PLMApplyDate.Value.Month).ToString("00") + (a.PLMApplyDate.Value.Day).ToString("00") + a.PLMApplyDate.Value.ToString("HHmm"),
h21 = (a.PLMReportDate == null) ? (a.PLMApplyDate.Value.Year - 1911).ToString("000") + (a.PLMApplyDate.Value.Month).ToString("00") + (a.PLMApplyDate.Value.Day).ToString("00") + a.PLMApplyDate.Value.ToString("HHmm") : (a.PLMReportDate.Value.Year - 1911).ToString("000") + (a.PLMReportDate.Value.Month).ToString("00") + (a.PLMReportDate.Value.Day).ToString("00") + a.PLMReportDate.Value.ToString("HHmm"),
h22 = "血液",
h23 = ich.h23,
h24 = ich.h24,
h25 = ich.h25,
a.PLDName,
a.PLDValue,
a.PLDUnit,
a.LLIUp_Male,
a.LLILo_Male,
a.LLIUp_Female,
a.LLILo_Female,
PLMReportDate = (a.PLMReportDate == null) ? (a.PLMApplyDate.Value.Year - 1911).ToString("000") + (a.PLMApplyDate.Value.Month).ToString("00") + (a.PLMApplyDate.Value.Day).ToString("00") + a.PLMApplyDate.Value.ToString("HHmm") : (a.PLMReportDate.Value.Year - 1911).ToString("000") + (a.PLMReportDate.Value.Month).ToString("00") + (a.PLMReportDate.Value.Day).ToString("00") + a.PLMReportDate.Value.ToString("HHmm")
}).ToList();
// 取得資料 Key
List <string> icKey = new List <string>();
foreach (var i in result)
{
icKey.Add(i.ICHRowid);
}
List <lisVPNHDataClass> vh = new List <lisVPNHDataClass>();
List <lisVPNRDataClass> vr = new List <lisVPNRDataClass>();
// 開始取得產生基本資料段及報告資料段
foreach (var vpn_data in result)
{
// 產生基本資料段
string sGuid_VHDRowid = Guid.NewGuid().ToString();
DateTime dt = DateTime.Now;
//string h16_date = (dt.Year - 1911).ToString("000") + dt.ToString("MMddHHmmss");
string h16_date = vpn_data.h11 + dt.ToString("HHmmss");
lisVPNHDataClass vpn_hdata = new lisVPNHDataClass();
vpn_hdata.VHDRowid = sGuid_VHDRowid;
vpn_hdata.ICHRowid = vpn_data.ICHRowid;
vpn_hdata.h1 = vpn_data.h1;
vpn_hdata.h2 = vpn_data.h2;
vpn_hdata.h3 = vpn_data.h3;
vpn_hdata.h4 = vpn_data.h4;
vpn_hdata.h5 = vpn_data.h5;
vpn_hdata.h6 = vpn_data.h6;
vpn_hdata.h7 = vpn_data.h7;
vpn_hdata.h8 = vpn_data.h8;
vpn_hdata.h9 = vpn_data.h9;
vpn_hdata.h10 = vpn_data.h10;
vpn_hdata.h11 = vpn_data.h11;
vpn_hdata.h12 = vpn_data.h12;
vpn_hdata.h13 = vpn_data.h13;
vpn_hdata.h14 = vpn_data.h14;
vpn_hdata.h15 = vpn_data.h15;
vpn_hdata.h16 = h16_date;
vpn_hdata.h17 = vpn_data.h17;
vpn_hdata.h18 = vpn_data.h18;
vpn_hdata.h19 = vpn_data.h19;
vpn_hdata.h20 = vpn_data.h20;
vpn_hdata.h21 = vpn_data.h21;
vpn_hdata.h22 = vpn_data.h22;
vpn_hdata.h23 = vpn_data.h23;
vpn_hdata.h24 = vpn_data.h24;
vpn_hdata.h25 = vpn_data.h25;
vh.Add(vpn_hdata);
// 產生報告資料段
int seq = 0;
lisVPNRDataClass vpn_rdata = new lisVPNRDataClass();
vpn_rdata.VRDRowid = Guid.NewGuid().ToString();
vpn_rdata.VHDRowid = sGuid_VHDRowid;
vpn_rdata.ICHRowid = vpn_data.ICHRowid;
vpn_rdata.r1 = seq + 1;
vpn_rdata.r2 = vpn_data.PLDName;
vpn_rdata.r3 = "未定義";
vpn_rdata.r4 = vpn_data.PLDValue;
vpn_rdata.r5 = vpn_data.PLDUnit;
vpn_rdata.r61 = vpn_data.LLILo_Male.ToString();
vpn_rdata.r62 = vpn_data.LLIUp_Male.ToString();
vpn_rdata.r7 = "";
vpn_rdata.r81 = "";
vpn_rdata.r82 = "";
vpn_rdata.r83 = "";
vpn_rdata.r84 = "";
vpn_rdata.r85 = "";
vpn_rdata.r86 = "";
vpn_rdata.r87 = "";
vpn_rdata.r88 = "";
vpn_rdata.r89 = "";
vpn_rdata.r9 = sHospID;
vpn_rdata.r10 = vpn_data.PLMReportDate;
vpn_rdata.r11 = "";
vpn_rdata.r12 = 0;
vpn_rdata.r13 = "";
vpn_rdata.r14 = "";
vr.Add(vpn_rdata);
}
// 刪除原有的資料
if (icKey.Count > 0)
{
db_his.lisVPNHData.Where(x => icKey.Contains(x.ICHRowid)).Delete();
db_his.lisVPNRData.Where(x => icKey.Contains(x.ICHRowid)).Delete();
}
#region 開始把整理好的檢驗上傳資料儲存到 lisVPNHData及 lisVPNRData
if (vh != null) // && db_icb != null
{
DataTable dt_header = myClass.LinqQueryToDataTable(vh);
DataTable dt_body = myClass.LinqQueryToDataTable(vr);
//string sbconn = String.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True; User ID={2}; Password={3}", dbs, "his" + sHospID, userid, password);
if (dt_header.Rows.Count > 0)
{
using (SqlBulkCopy bcp_ich = new SqlBulkCopy(sbconn))
{
bcp_ich.DestinationTableName = "lisVPNHData";
bcp_ich.ColumnMappings.Add("VHDRowid", "VHDRowid");
bcp_ich.ColumnMappings.Add("ICHRowid", "ICHRowid");
bcp_ich.ColumnMappings.Add("h1", "h1");
bcp_ich.ColumnMappings.Add("h2", "h2");
bcp_ich.ColumnMappings.Add("h3", "h3");
bcp_ich.ColumnMappings.Add("h4", "h4");
bcp_ich.ColumnMappings.Add("h5", "h5");
bcp_ich.ColumnMappings.Add("h6", "h6");
bcp_ich.ColumnMappings.Add("h7", "h7");
bcp_ich.ColumnMappings.Add("h8", "h8");
bcp_ich.ColumnMappings.Add("h9", "h9");
bcp_ich.ColumnMappings.Add("h10", "h10");
bcp_ich.ColumnMappings.Add("h11", "h11");
bcp_ich.ColumnMappings.Add("h12", "h12");
bcp_ich.ColumnMappings.Add("h13", "h13");
bcp_ich.ColumnMappings.Add("h14", "h14");
bcp_ich.ColumnMappings.Add("h15", "h15");
bcp_ich.ColumnMappings.Add("h16", "h16");
bcp_ich.ColumnMappings.Add("h17", "h17");
bcp_ich.ColumnMappings.Add("h18", "h18");
bcp_ich.ColumnMappings.Add("h19", "h19");
bcp_ich.ColumnMappings.Add("h20", "h20");
bcp_ich.ColumnMappings.Add("h21", "h21");
bcp_ich.ColumnMappings.Add("h22", "h22");
bcp_ich.ColumnMappings.Add("h23", "h23");
bcp_ich.ColumnMappings.Add("h24", "h24");
bcp_ich.ColumnMappings.Add("h25", "h25");
bcp_ich.WriteToServer(dt_header);
}
}
if (dt_body.Rows.Count > 0)
{
using (SqlBulkCopy bcp_icb = new SqlBulkCopy(sbconn))
{
bcp_icb.DestinationTableName = "lisVPNRData";
bcp_icb.ColumnMappings.Add("VRDRowid", "VRDRowid");
bcp_icb.ColumnMappings.Add("VHDRowid", "VHDRowid");
bcp_icb.ColumnMappings.Add("ICHRowid", "ICHRowid");
bcp_icb.ColumnMappings.Add("r1", "r1");
bcp_icb.ColumnMappings.Add("r2", "r2");
bcp_icb.ColumnMappings.Add("r3", "r3");
bcp_icb.ColumnMappings.Add("r4", "r4");
bcp_icb.ColumnMappings.Add("r5", "r5");
bcp_icb.ColumnMappings.Add("r61", "r61");
bcp_icb.ColumnMappings.Add("r62", "r62");
bcp_icb.ColumnMappings.Add("r7", "r7");
bcp_icb.ColumnMappings.Add("r81", "r81");
bcp_icb.ColumnMappings.Add("r82", "r82");
bcp_icb.ColumnMappings.Add("r83", "r83");
bcp_icb.ColumnMappings.Add("r84", "r84");
bcp_icb.ColumnMappings.Add("r85", "r85");
bcp_icb.ColumnMappings.Add("r86", "r86");
bcp_icb.ColumnMappings.Add("r87", "r87");
bcp_icb.ColumnMappings.Add("r88", "r88");
bcp_icb.ColumnMappings.Add("r89", "r89");
bcp_icb.ColumnMappings.Add("r9", "r9");
bcp_icb.ColumnMappings.Add("r10", "r10");
bcp_icb.ColumnMappings.Add("r11", "r11");
bcp_icb.ColumnMappings.Add("r12", "r12");
bcp_icb.ColumnMappings.Add("r13", "r13");
bcp_icb.ColumnMappings.Add("r14", "r14");
bcp_icb.WriteToServer(dt_body);
}
}
vh.Clear();
vr.Clear();
sResult = "Code(200): 檢驗資料媒合成功 !!!";
}
else
{
sResult = "Code(992): 沒有任何檢驗資料可供媒合 !!!";
}
#endregion
#endregion
}
catch (Exception ex)
{
sResult = "Code(999):" + ex.ToString();
}
}
else
{
sResult = "Code(990):醫事機構代碼沒傳入 ! (sHospID is Null !)";
}
}
else
{
sResult = "Code(991):日期沒傳入 ! (sDate is Null !)";
}
return(sResult);
}
