/// <summary>
/// บันทึก Log การส่งอีเมล์จากระบบ Template
/// </summary>
/// <param name="UID">EmailTemplateUID</param>
/// <param name="From">จากเมล์</param>
/// <param name="To">ถึงเมล์</param>
/// <param name="Cc">แนบเมล์</param>
/// <param name="Bcc">ไม่เปิดเผยเมล์</param>
/// <param name="Result">ผลการส่ง</param>
/// <returns>true=บันทึกสำเร็จ , false=บันทึกไม่สำเร็จ</returns>
private bool SendTemplateLog(string UID, string From, string To, string Cc, string Bcc, string Result)
{
#region Variable
bool rtnValue = false;
clsSQL clsSQL = new clsSQL();
clsSecurity clsSecurity=new clsSecurity();
clsNet clsNet = new clsNet();
string outSQL;
#endregion
#region SQL Query
if(clsSQL.Insert(
"EmailTemplateLog",
new string[,]{
{"UID",clsSQL.GetNewID("UID","EmailTemplateLog","",dbType,cs).ToString()},
{"EmailTemplateUID",UID},
{"EmailFrom","'"+clsSQL.CodeFilter(From)+"'"},
{"EmailTo","'"+clsSQL.CodeFilter(To)+"'"},
{"EmailCc","'"+clsSQL.CodeFilter(Cc)+"'"},
{"EmailBcc","'"+clsSQL.CodeFilter(Bcc)+"'"},
{"Result","'"+clsSQL.CodeFilter(Result)+"'"},
{"CWhen","GETDATE()"},
{"CUser","0"},
{"CIP","'"+clsNet.IPGet()+"'"},
{"CHostname","'"+clsNet.ComNameGet()+"'"}},
new string[,]{{}},
dbType,
cs,
out outSQL))
{
rtnValue = true;
}
#endregion
return rtnValue;
}
public bool setPatientToMobile(DataTable dt, out int countSuccess, out int countFail, out int countExist, out int countChecklistSuccess, out int countChecklistFail, out int countChecklistExist,out int countChecklistUpdate, out string outMessage)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
#region Variable
var result = true;
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var strSQL = "";
countSuccess = 0; countFail = 0; countExist = 0;
countChecklistSuccess = 0; countChecklistFail = 0; countChecklistExist = 0; countChecklistUpdate = 0;
outMessage = "";
#endregion
#region Procedure
try
{
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
setProgressBarSyncToMobile(i + 1, dt.Rows.Count);
#region Patient
strSQL = "SELECT COUNT(PatientGUID) FROM patient WHERE PatientGUID='" + dt.Rows[i]["PatientGUID"].ToString() + "';";
var count = clsSQL.Return(strSQL);
if (count == "0")
{
if (!clsSQL.Insert(
"patient",
new string[,]
{
{"PatientGUID","'"+dt.Rows[i]["PatientGUID"].ToString()+"'" },
{"HN","'"+dt.Rows[i]["HN"].ToString()+"'" },
{"Episode","'"+dt.Rows[i]["Episode"].ToString()+"'" },
{"LabEpisode","'"+dt.Rows[i]["LabEpisode"].ToString()+"'" },
{"DOB","'"+DateTime.Parse(dt.Rows[i]["DOB"].ToString()).ToString("yyyy-MM-dd")+"'" },
{"No","'"+dt.Rows[i]["No"].ToString()+"'" },
{"EmployeeID","'"+dt.Rows[i]["EmployeeID"].ToString()+"'" },
{"DOE","'"+DateTime.Parse(dt.Rows[i]["DOE"].ToString()).ToString("yyyy-MM-dd HH:mm")+"'" },
{"Company","'"+dt.Rows[i]["Company"].ToString().SQLQueryFilter()+"'" },
{"ChildCompany","'"+dt.Rows[i]["ChildCompany"].ToString().SQLQueryFilter()+"'" },
{"ProChkList","'"+dt.Rows[i]["ProChkList"].ToString()+"'" },
{"ProChkListDetail","'"+dt.Rows[i]["ProChkListDetail"].ToString().SQLQueryFilter()+"'" },
{"Prename","'"+dt.Rows[i]["Prename"].ToString().SQLQueryFilter()+"'" },
{"Forename","'"+dt.Rows[i]["Forename"].ToString().SQLQueryFilter()+"'" },
{"Surname","'"+dt.Rows[i]["Surname"].ToString().SQLQueryFilter()+"'" },
{"Age","'"+dt.Rows[i]["Age"].ToString().SQLQueryFilter()+"'" },
{"Sex","'"+dt.Rows[i]["Sex"].ToString().Trim().SQLQueryFilter()+"'" },
{"Address","'"+dt.Rows[i]["Address"].ToString().SQLQueryFilter()+"'" },
{"Tel","'"+dt.Rows[i]["Tel"].ToString().SQLQueryFilter()+"'" },
{"Email","'"+dt.Rows[i]["Email"].ToString().SQLQueryFilter()+"'" },
{"Physician","'"+dt.Rows[i]["Physician"].ToString().SQLQueryFilter()+"'" },
{"RegType","'"+dt.Rows[i]["RegType"].ToString().SQLQueryFilter()+"'" },
{"Programid",""+(dt.Rows[i]["Programid"].ToString()==""?"NULL":dt.Rows[i]["Programid"].ToString())+"" },
{"DIVI","'"+dt.Rows[i]["DIVI"].ToString().SQLQueryFilter()+"'" },
{"DEP","'"+dt.Rows[i]["DEP"].ToString().SQLQueryFilter()+"'" },
{"SEC","'"+dt.Rows[i]["SEC"].ToString().SQLQueryFilter()+"'" },
{"POS","'"+dt.Rows[i]["POS"].ToString().SQLQueryFilter()+"'" },
{"LAN","'"+dt.Rows[i]["LAN"].ToString().SQLQueryFilter()+"'" },
{"NAT","'"+dt.Rows[i]["NAT"].ToString().SQLQueryFilter()+"'" },
{"CNT_TRY","'"+dt.Rows[i]["CNT_TRY"].ToString().SQLQueryFilter()+"'" },
{"LOC","'"+dt.Rows[i]["LOC"].ToString().SQLQueryFilter()+"'" },
{"Payor","'"+dt.Rows[i]["Payor"].ToString().SQLQueryFilter()+"'" },
{"Epi_Rowid",""+(dt.Rows[i]["Epi_Rowid"].ToString().Trim()==""?"NULL":dt.Rows[i]["Epi_Rowid"].ToString())+"" },
{"ORD_STS","'"+dt.Rows[i]["ORD_STS"].ToString().SQLQueryFilter()+"'" },
{"STS","'"+dt.Rows[i]["STS"].ToString().SQLQueryFilter()+"'" },
{"DR_CDE","'"+dt.Rows[i]["DR_CDE"].ToString().SQLQueryFilter()+"'" },
{"NTE","'"+dt.Rows[i]["NTE"].ToString().SQLQueryFilter()+"'" },
{"Job","'"+dt.Rows[i]["Job"].ToString().SQLQueryFilter()+"'" },
{"BusUnit","'"+dt.Rows[i]["BusUnit"].ToString().SQLQueryFilter()+"'" },
{"BusDiv","'"+dt.Rows[i]["BusDiv"].ToString().SQLQueryFilter()+"'" },
{"Line","'"+dt.Rows[i]["Line"].ToString().SQLQueryFilter()+"'" },
{"Shift","'"+dt.Rows[i]["Shift"].ToString().SQLQueryFilter()+"'" },
{"Location","'"+dt.Rows[i]["Location"].ToString().SQLQueryFilter()+"'" },
{"GrpBook","'"+dt.Rows[i]["GrpBook"].ToString().SQLQueryFilter()+"'" },
{"BookCreate","'"+dt.Rows[i]["BookCreate"].ToString().SQLQueryFilter()+"'" },
{"HISExist","'"+dt.Rows[i]["HISExist"].ToString().SQLQueryFilter()+"'" },
{"SyncStatus",(dt.Rows[i]["SyncStatus"]!=DBNull.Value?"'"+dt.Rows[i]["SyncStatus"].ToString()+"'":"'0'") },
{"SyncWhen",(dt.Rows[i]["SyncWhen"]!=DBNull.Value?"'"+DateTime.Parse(dt.Rows[i]["SyncWhen"].ToString()).ToString("yyyy-MM-dd HH:mm")+"'":"NULL") },
{"CUser","'"+clsGlobal.WindowsLogon()+"'" },
{"StatusFlag","'A'" }
},
new string[,] { { } },
out strSQL,
true))
{
countFail += 1;
result = false;
}
else
{
countSuccess += 1;
}
}
else
{
var tempHN = dt.Rows[i]["HN"].ToString();
countExist += 1;
}
#endregion
#region PatientChecklist
var countChecklistSuccessTemp = 0;
var countChecklistExistTemp = 0;
var countChecklistFailTemp = 0;
var countChecklistUpdateTemp = 0;
var setPatientChecklistToMobileStatus = setPatientChecklistToMobile(
dt.Rows[i]["PatientGUID"].ToString(),
dt.Rows[i]["Episode"].ToString(),
dt.Rows[i]["HN"].ToString(),
out countChecklistSuccessTemp, out countChecklistExistTemp, out countChecklistFailTemp,out countChecklistUpdateTemp);
countChecklistSuccess += countChecklistSuccessTemp;
countChecklistExist += countChecklistExistTemp;
countChecklistFail += countChecklistFailTemp;
countChecklistUpdate += countChecklistUpdateTemp;
#endregion
}
}
else
{
result = false;
}
}
catch (Exception ex)
{
result = false;
outMessage = ex.Message;
}
#endregion
return result;
}
public bool setChecklistToMobile(DataTable dt)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
#region Variable
var result = true;
var clsSQL = new clsSQL(clsGlobal.dbTypeMain, clsGlobal.csMain);
var clsSQLMobile = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var dv = dt.DefaultView;
var dtProChkList = new DataTable();
var strSQL = "";
#endregion
#region Procedure
dtProChkList = dv.ToTable(true, "ProChkList");
if(dtProChkList!=null && dtProChkList.Rows.Count > 0)
{
for(int i = 0; i < dtProChkList.Rows.Count; i++)
{
var checklistID = "";
strSQL = "DELETE FROM Checklist WHERE Code='"+ dtProChkList.Rows[i][0].ToString().Trim() + "';";
clsSQLMobile.Execute(strSQL);
#region Checklist
strSQL = "SELECT TOP 1 id ChecklistID,Checklist Code,CheckListDesc Detail FROM Checklist WHERE Checklist='" + dtProChkList.Rows[i][0].ToString().Trim() + "';";
var dtChecklist = new DataTable();
dtChecklist = clsSQL.Bind(strSQL);
if (dtChecklist != null && dtChecklist.Rows.Count > 0)
{
checklistID = dtChecklist.Rows[0]["ChecklistID"].ToString();
#region Insert
if (!clsSQLMobile.Insert(
"checklist",
new string[,]
{
{"ChecklistID", dtChecklist.Rows[0]["ChecklistID"].ToString() },
{"Code", "'"+dtChecklist.Rows[0]["Code"].ToString().Trim().SQLQueryFilter()+"'" },
{"Detail", "'"+dtChecklist.Rows[0]["Detail"].ToString().SQLQueryFilter()+"'" }
},
new string[,] { { } },
out strSQL))
{
//If error, what you want to do?
result = false;
MessageBox.Show("Checklist Insert : " + strSQL, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
#endregion
}
#endregion
#region ChecklistDetail
if (checklistID != "")
{
strSQL = "SELECT TOP 100 rowid ProID,WFID,WFSequen,WorkFlow FROM Process WHERE CheckListid="+ checklistID + ";";
var dtChecklistDetail = new DataTable();
dtChecklistDetail = clsSQL.Bind(strSQL);
if (dtChecklistDetail != null && dtChecklistDetail.Rows.Count > 0)
{
for(int j = 0; j < dtChecklistDetail.Rows.Count; j++)
{
#region Insert
if (!clsSQLMobile.Insert(
"checklistdetail",
new string[,]
{
{"ChecklistID", checklistID },
{"ProID", dtChecklistDetail.Rows[j]["ProID"].ToString().Trim() },
{"WFID", dtChecklistDetail.Rows[j]["WFID"].ToString() },
{"WFSequen", dtChecklistDetail.Rows[j]["WFSequen"].ToString() },
{"WorkFlow", "'"+dtChecklistDetail.Rows[j]["WorkFlow"].ToString().SQLQueryFilter()+"'" }
},
new string[,] { { } },
out strSQL))
{
result = false;
//If error, what you want to do?
MessageBox.Show("ChecklistDetail Insert : " + strSQL, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
#endregion
}
}
}
#endregion
}
}
#endregion
return result;
}
public string setPatientChecklistToMobile(string PatientGUID, string Episode, string HN, out int countChecklistSuccess, out int countChecklistExist, out int countChecklistFail, out int countChecklistUpdate)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
#region Variable
var result = "F";//F=False , S=Success , E=Exist , U=Update
var dt = new DataTable();
var outSQL = "";
var clsSQLMain = new clsSQL(clsGlobal.dbTypeMain, clsGlobal.csMain);
var clsSQLMobile = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var clsTempData = new clsTempData();
var strSQL = "";
countChecklistSuccess = 0; countChecklistFail = 0; countChecklistExist = 0; countChecklistUpdate = 0;
#endregion
#region Procedure
dt = clsTempData.getPatientChecklist(PatientGUID, Episode, HN);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
var proStatusMobile = "";
strSQL = "SELECT IFNULL(ProStatus,0)ProStatus FROM patientchecklist WHERE RowID=" + dt.Rows[i]["RowID"].ToString() + ";";
proStatusMobile = clsSQLMobile.Return(strSQL);
if (proStatusMobile == "")
{
#region Insert
if (!clsSQLMobile.Insert(
"patientchecklist",
new string[,]
{
{"RowID",dt.Rows[i]["RowID"].ToString() },
{"PatientGUID","'"+PatientGUID+"'" },
{"HN","'"+HN+"'" },
{"Episode","'"+Episode+"'" },
{"CheckListID",dt.Rows[i]["CheckListID"].ToString() },
{"ProChkList","'"+dt.Rows[i]["ProChkList"].ToString()+"'" },
{"ProID",dt.Rows[i]["ProID"].ToString() },
{"WorkFlow","'"+dt.Rows[i]["WorkFlow"].ToString()+"'" },
{"WFID",dt.Rows[i]["WFID"].ToString() },
{"WFSequen",dt.Rows[i]["WFSequen"].ToString() },
{"ProStatus",dt.Rows[i]["ProStatus"].ToString() },
{"RegDate","NULL" },
{"ModifyDate","NULL" },
{"SyncStatus",(dt.Rows[i]["SyncWhen"]!=DBNull.Value?"'1'":"'0'") },
{"SyncWhen",(dt.Rows[i]["SyncWhen"]!=DBNull.Value?"'"+DateTime.Parse(dt.Rows[i]["SyncWhen"].ToString()).ToString("yyyy-MM-dd HH:mm")+"'":"NULL") },
{"CUser","'"+clsGlobal.WindowsLogon()+"'" },
{"MWhen","NOW()" },
{"MUser","'"+clsGlobal.WindowsLogon()+"'" },
},
new string[,] { { } },
out outSQL, true
))
{
result = "F";
countChecklistFail += 1;
}
else
{
result = "S";
countChecklistSuccess += 1;
}
#endregion
}
else if (int.Parse(dt.Rows[i]["ProStatus"].ToString()) > int.Parse(proStatusMobile))
{
#region Update
if (!clsSQLMobile.Update(
"patientchecklist",
new string[,]
{
{"ProStatus",dt.Rows[i]["ProStatus"].ToString() },
{"RegDate",(dt.Rows[i]["RegDate"]!=DBNull.Value?"'"+DateTime.Parse(dt.Rows[i]["RegDate"].ToString()).ToString("yyyy-MM-dd HH:mm")+"'":"NULL") },
{"ModifyDate",(dt.Rows[i]["ModifyDate"]!=DBNull.Value?"'"+DateTime.Parse(dt.Rows[i]["ModifyDate"].ToString()).ToString("yyyy-MM-dd HH:mm")+"'":"NULL") },
{"SyncStatus","'0'" },
{"SyncWhen","NOW()" },
{"CUser","'"+clsGlobal.WindowsLogon()+"'" },
{"MWhen","NOW()" },
{"MUser","'"+clsGlobal.WindowsLogon()+"'" },
},
new string[,] { { } },
"RowID="+ dt.Rows[i]["RowID"].ToString(),
out outSQL, true
))
{
result = "F";
countChecklistFail += 1;
}
else
{
result = "U";
countChecklistUpdate += 1;
}
#endregion
}
else
{
result = "E";
countChecklistExist += 1;
}
}
}
#endregion
return result;
}