private string WebboardConfig(string name)
{
string value = "";
value = clsSQL.Return("SELECT Value FROM WebboardConfig WHERE Name='" + name + "' AND Active='1'", dbType, cs);
return(value);
}
protected void BindDefault()
{
gvDefault.Visible = true; pnDGHeader.Visible = true;
#region Variable
StringBuilder strSQL = new StringBuilder();
DataTable dt = new DataTable();
#endregion
#region Header Builder
lblHeader.Text = clsSQL.Return("SELECT Name FROM MedicalCenterGroup WHERE UID=" + clsDefault.QueryStringChecker("group"), dbType, cs);
#endregion
#region SQL Query
strSQL.Append("SELECT ");
strSQL.Append(tableDefault + ".UID,");
strSQL.Append(tableDefault + ".Name,");
strSQL.Append(tableDefault + ".Detail,");
strSQL.Append(tableDefault + ".MWhen,");
strSQL.Append(tableDefault + ".Sort,");
strSQL.Append(tableDefault + ".Active,");
strSQL.Append("Language.Name LanguageName,");
strSQL.Append("Language.Icon LanguageIcon ");
strSQL.Append("FROM ");
strSQL.Append(tableDefault + " ");
strSQL.Append("INNER JOIN Language ON " + tableDefault + ".LanguageUID=Language.UID ");
#region Where
strSQL.Append("WHERE ");
strSQL.Append("MedicalCenterGroupUID=" + clsDefault.QueryStringChecker("group") + " ");
#endregion
strSQL.Append("ORDER BY ");
strSQL.Append(tableDefault + ".Sort,");
strSQL.Append("Language.Sort");
#endregion
#region Data Builder
dt = clsSQL.Bind(strSQL.ToString(), dbType, cs);
strSQL.Length = 0; strSQL.Capacity = 0;
if (dt != null && dt.Rows.Count > 0)
{
lblDG.Text = "";
gvDefault.DataSource = dt;
if (Request.QueryString["page"] != null)
{
try
{
gvDefault.PageIndex = int.Parse(Request.QueryString["page"].ToString());
}
catch (Exception ex)
{
gvDefault.PageIndex = int.Parse(Request.QueryString["page"].ToString()) - 1;
}
}
gvDefault.DataBind();
dt = null;
}
else
{
pnDGHeader.Visible = false;
lblDG.Text = clsDefault.AlertMessageColor("ไม่พบข้อมูลที่ต้องการ", clsDefault.AlertType.Info);
}
#endregion
}
private void Delete(string id)
{
pnDetail.Visible = false;
#region Authorize
if (!clsSecurity.LoginChecker("admin"))
{
ucColorBox1.Redirect("/", "กรุณาล็อคอินด้วยสิทธิ์ Admin");
return;
}
#endregion
#region Variable
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var clsIO = new clsIO();
var strSQL = new StringBuilder();
#endregion
#region Procedure
#region Delete Photo
#region SQL Query
strSQL.Append("SELECT ");
strSQL.Append("Photo ");
strSQL.Append("FROM ");
strSQL.Append(tableDefault + " ");
strSQL.Append("WHERE ");
strSQL.Append("UID=" + id);
#endregion
string photoDelete = clsSQL.Return(strSQL.ToString());
if (!string.IsNullOrEmpty(photoDelete))
{
clsIO.FileExist(photoDelete, true);
}
strSQL.Length = 0; strSQL.Capacity = 0;
#endregion
#region Delete Database
#region SQL Query
strSQL.Append("DELETE FROM ");
strSQL.Append(tableDefault + " ");
strSQL.Append("WHERE ");
strSQL.Append("UID=" + id);
#endregion
if (clsSQL.Execute(strSQL.ToString()))
{
ucColorBox1.Redirect(webDefault);
}
else
{
ucColorBox1.Redirect(webDefault, "เกิดข้อผิดพลาดขณะลบข้อมูล");
return;
}
strSQL.Length = 0; strSQL.Capacity = 0;
#endregion
#endregion
}
protected void BindDefault()
{
dlDefault.Visible = true; pnDGHeader.Visible = true;
#region Variable
var strSQL = new StringBuilder();
var dt = new DataTable();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
#endregion
#region Procedure
#region Header Builder
lblHeader.Text = clsSQL.Return("SELECT Name FROM " + tableParrent + " WHERE UID=" + clsDefault.QueryStringChecker("group"));
#endregion
#region SQL Query
strSQL.Append("SELECT ");
strSQL.Append("A.UID,");
strSQL.Append("A.PhotoPreview,");
strSQL.Append("A.Photo,");
strSQL.Append("A.Name,");
strSQL.Append("A.Detail,");
strSQL.Append("A.[View],");
strSQL.Append("A.MWhen,");
strSQL.Append("A.Sort,");
strSQL.Append("A.StatusFlag ");
strSQL.Append("FROM ");
strSQL.Append(tableDefault + " A ");
#region Where
strSQL.Append("WHERE ");
strSQL.Append("PhotoGalleryGroupUID=" + clsDefault.QueryStringChecker("group") + " ");
#endregion
strSQL.Append("ORDER BY ");
strSQL.Append("A.Sort");
#endregion
#region Data Builder
dt = clsSQL.Bind(strSQL.ToString());
strSQL.Length = 0; strSQL.Capacity = 0;
if (dt != null && dt.Rows.Count > 0)
{
lblDG.Text = "";
dlDefault.DataSource = dt;
dlDefault.DataBind();
dt = null;
}
else
{
pnDGHeader.Visible = false;
lblDG.Text = clsDefault.AlertMessageColor("ไม่พบข้อมูลที่ต้องการ", clsDefault.AlertType.Info);
}
#endregion
#endregion
}
protected void BindDefault()
{
dlDefault.Visible = true; pnDGHeader.Visible = true;
#region Variable
StringBuilder strSQL = new StringBuilder();
DataTable dt = new DataTable();
#endregion
#region Header Builder
lblHeader.Text = clsSQL.Return("SELECT Name FROM " + clsDefault.QueryStringChecker("globalname") + " WHERE UID=" + clsDefault.QueryStringChecker("globalid"), dbType, cs);
#endregion
#region SQL Query
strSQL.Append("SELECT ");
strSQL.Append(tableDefault + ".UID,");
strSQL.Append(tableDefault + ".PhotoPreview,");
strSQL.Append(tableDefault + ".Photo,");
strSQL.Append(tableDefault + ".Name,");
strSQL.Append(tableDefault + ".Detail,");
strSQL.Append(tableDefault + ".[View],");
strSQL.Append(tableDefault + ".MWhen,");
strSQL.Append(tableDefault + ".Sort,");
strSQL.Append(tableDefault + ".Active ");
strSQL.Append("FROM ");
strSQL.Append(tableDefault + " ");
#region Where
strSQL.Append("WHERE ");
strSQL.Append("GlobalName='" + clsDefault.QueryStringChecker("globalname") + "' ");
strSQL.Append("AND GlobalUID=" + clsDefault.QueryStringChecker("globalid") + " ");
#endregion
strSQL.Append("ORDER BY ");
strSQL.Append(tableDefault + ".Sort");
#endregion
#region Data Builder
dt = clsSQL.Bind(strSQL.ToString(), dbType, cs);
strSQL.Length = 0; strSQL.Capacity = 0;
if (dt != null && dt.Rows.Count > 0)
{
lblDG.Text = "";
dlDefault.DataSource = dt;
dlDefault.DataBind();
dt = null;
}
else
{
pnDGHeader.Visible = false;
lblDG.Text = clsDefault.AlertMessageColor("ไม่พบข้อมูลที่ต้องการ", clsDefault.AlertType.Info);
}
#endregion
}
private void Delete(string id)
{
pnDetail.Visible = false;
#region Authorize
if (!clsSecurity.LoginChecker("admin"))
{
ucColorBox1.Redirect("/", "กรุณาล็อคอินด้วยสิทธิ์ Admin");
return;
}
#endregion
#region Variable
StringBuilder strSQL = new StringBuilder();
DataTable dt = new DataTable();
clsIO clsIO = new clsIO();
string photoName;
#endregion
#region Delete Photo
photoName = clsSQL.Return(
"SELECT Icon FROM " + tableDefault + " WHERE UID=" + parameterChar + "UID",
new string[, ] {
{ parameterChar + "UID", id }
},
dbType,
cs);
clsIO.FileExist(photoName, true);
#endregion
#region Delete Database
#region SQL Query
strSQL.Append("DELETE FROM ");
strSQL.Append(tableDefault + " ");
strSQL.Append("WHERE ");
strSQL.Append("UID=" + parameterChar + "UID");
#endregion
if (clsSQL.Execute(strSQL.ToString(), new string[, ] {
{ parameterChar + "UID", id }
}, dbType, cs))
{
ucColorBox1.Redirect(webDefault);
}
else
{
ucColorBox1.Redirect(webDefault, "เกิดข้อผิดพลาดขณะลบข้อมูล");
}
strSQL.Length = 0; strSQL.Capacity = 0;
#endregion
}
protected void btDGSubmit_Click(object sender, EventArgs e)
{
#region Authorize
if (!clsSecurity.LoginChecker("admin"))
{
ucColorBox1.Redirect("/", "เกิดข้อผิดพลาด", "คุณไม่ได้รับสิทธิ์ในการบันทึกข้อมูล กรุณาล็อคอินด้วยสิทธิ์ Admin");
return;
}
#endregion
#region Variable
var strSQL = new StringBuilder();
var outError = "";
var statusFlag = "";
#endregion
#region SQL Builder
for (int i = 0; i < dlDefault.Items.Count; i++)
{
Label lblDGID = (Label)dlDefault.Items[i].FindControl("lblDGID");
Label lblDGName = (Label)dlDefault.Items[i].FindControl("lblDGName");
CheckBox cbDGActive = (CheckBox)dlDefault.Items[i].FindControl("cbDGActive");
if (lblDGID != null && lblDGName != null && cbDGActive != null)
{
statusFlag = clsSQL.Return("SELECT StatusFlag FROM Highlight WHERE GlobalUID=" + lblDGID.Text + " AND GlobalName='" + lblDGName.Text + "';", dbType, cs);
if (statusFlag != "" && statusFlag != (cbDGActive.Checked?"A":"D"))
{
#region UPDATE SQL Query
strSQL.Append("UPDATE ");
strSQL.Append("Highlight ");
strSQL.Append("SET ");
strSQL.Append("MWhen=GETDATE(),");
strSQL.Append("MUser="******",");
strSQL.Append("StatusFlag='" + (cbDGActive.Checked?"A":"D") + "' ");
strSQL.Append("WHERE ");
strSQL.Append("GlobalUID=" + lblDGID.Text + " ");
strSQL.Append("AND GlobalName='" + lblDGName.Text + "'");
strSQL.Append(";");
#endregion
}
else if (statusFlag == "")
{
if (cbDGActive.Checked)
{
#region INSERT SQL Query
strSQL.Append("INSERT INTO ");
strSQL.Append("Highlight ");
strSQL.Append("(GlobalUID,GlobalName,CWhen,CUser,MWhen,MUser,Sort,StatusFlag)");
strSQL.Append("VALUES(");
strSQL.Append(lblDGID.Text + ",");
strSQL.Append("'" + lblDGName.Text + "',");
strSQL.Append("GETDATE(),");
strSQL.Append(clsSecurity.LoginUID + ",");
strSQL.Append("GETDATE(),");
strSQL.Append(clsSecurity.LoginUID + ",");
strSQL.Append("0,");
strSQL.Append("'A'");
strSQL.Append(")");
strSQL.Append(";");
#endregion
}
}
if (strSQL.Length > 0)
{
if (!clsSQL.Execute(strSQL.ToString(), dbType, cs, out outError))
{
ucColorBox1.Alert("SQL Error", outError, AlertImage: ucColorBox.Alerts.Fail);
return;
}
strSQL.Length = 0; strSQL.Capacity = 0;
}
}
}
#endregion
ucColorBox1.Redirect("/Management/Highlight.aspx" + clsDefault.QueryStringMerge(), "ดำเนินการเสร็จสิ้น", "แก้ไขข้อมูลเสร็จเรียบร้อย");
}
private bool getAdminChecker()
{
#region Variable
var result = false;
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var resultSQL = "";
#endregion
#region Procedure
resultSQL = clsSQL.Return("SELECT COUNT(UID) FROM adminlist WHERE LogonName = '"+clsGlobal.WindowsLogonBuilder()+"' AND StatusFlag='A';");
if (resultSQL != "" && resultSQL != "0")
{
result = true;
}
#endregion
return result;
}
protected void btSubmit_Click(object sender, EventArgs e)
{
#region Variable
StringBuilder strSQL = new StringBuilder();
string outSQL;
string outError;
string outPhotoName = "null";
#endregion
#region Check Data
#region Find Username
if (int.Parse(clsSQL.Return("SELECT COUNT(UID) FROM [User] WHERE Username='******'", dbType, cs)) > 0)
{
//lblUsername.Text = clsDefault.AlertMessageColor("Username นี้มีผู้ใช้งานแล้ว", clsDefault.AlertType.Warn);
//lblUsername.Focus();
txtUsername.Focus();
ucColorBox1.Alert("ข้อมูลไม่ถูกต้อง", "Username นี้มีผู้ใช้งานแล้ว", AlertImage: ucColorBox.Alerts.Fail);
return;
}
else
{
lblUsername.Text = "";
}
#endregion
#region Find Email
if (int.Parse(clsSQL.Return("SELECT COUNT(UID) FROM [User] WHERE Email='" + clsDefault.CodeFilter(txtEMail.Text) + "'", dbType, cs)) > 0)
{
//lblEmail.Text = clsDefault.AlertMessageColor("Email นี้มีผู้ใช้งานแล้ว", clsDefault.AlertType.Warn);
//lblEmail.Focus();
txtEMail.Focus();
ucColorBox1.Alert("ข้อมูลไม่ถูกต้อง", "E-Mail นี้มีผู้ใช้งานแล้ว", AlertImage: ucColorBox.Alerts.Fail);
return;
}
else
{
lblEmail.Text = "";
}
#endregion
#region Find UID
int UID = clsSQL.GetNewID("UID", "[User]", "", dbType, cs);
if (UID == 0)
{
//lblSQL.Text = clsDefault.AlertMessageColor("เกิดข้อผิดพลาดขณะหา UID", clsDefault.AlertType.Fail);
//lblSQL.Focus();
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", "ไม่สามารถหา UID ได้", AlertImage: ucColorBox.Alerts.Fail);
return;
}
else
{
lblSQL.Text = "";
}
#endregion
#endregion
#region Insert
#region Photo Upload
if (fuPhoto.HasFile)
{
if (!clsIO.UploadPhoto(fuPhoto, pathPhoto, clsSecurity.LoginUID, 500, photoWidth, photoHeight, "", 0, out outError, out outPhotoName))
{
//lblSQL.Text = clsDefault.AlertMessageColor("เกิดข้อผิดพลาดขณะอัพโหลดภาพ : " + outError, clsDefault.AlertType.Fail);
//lblSQL.Focus();
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", "เกิดข้อผิดพลาดขณะอัพโหลดภาพ", AlertImage: ucColorBox.Alerts.Fail);
return;
}
else
{
outPhotoName = "'" + pathPhoto + outPhotoName + "'";
}
}
#endregion
#region SQL Insert
if (clsSQL.Insert(
"[USER]",
new string[, ] {
{ "UID", UID.ToString() },
{ "UserGroupUID", "2" },
{ "Username", "'" + clsDefault.CodeFilter(txtUsername.Text) + "'" },
{ "Password", "'" + clsSecurity.Encrypt(clsDefault.CodeFilter(txtPassword.Text)) + "'" },
{ "Photo", outPhotoName },
{ "PName", ddlPName.SelectedItem.Value != "null"?"'" + ddlPName.SelectedItem.Value + "'":"null" },
{ "FName", "'" + clsDefault.CodeFilter(txtFName.Text) + "'" },
{ "LName", "'" + clsDefault.CodeFilter(txtLName.Text) + "'" },
{ "HN", "'" + clsDefault.CodeFilter(HNConvert(txtHN.Text)) + "'" },
{ "BirthDate", ucDateTimeFlat1.DateTime != DateTime.MinValue?"'" + ucDateTimeFlat1.DateTime.ToString("yyyy-MM-dd HH:mm:ss") + "'":"null" },
{ "Gender", rbGender.SelectedItem.Value != "null"?"'" + rbGender.SelectedItem.Value + "'":"null" },
{ "Phone", "'" + clsDefault.CodeFilter(txtPhone.Text) + "'" },
{ "Mobile", "'" + clsDefault.CodeFilter(txtMobile.Text) + "'" },
{ "Email", "'" + clsDefault.CodeFilter(txtEMail.Text) + "'" },
{ "Address", "'" + clsDefault.CodeFilter(txtAddress.Text) + "'" },
{ "AddressDistrict", "'" + clsDefault.CodeFilter(txtAddressDistrict.Text) + "'" },
{ "AddressPrefecture", "'" + clsDefault.CodeFilter(txtAddressPrefecture.Text) + "'" },
{ "AddressProvince", "'" + clsDefault.CodeFilter(txtAddressProvince.Text) + "'" },
{ "AddressPostal", "'" + clsDefault.CodeFilter(txtAddressPostal.Text) + "'" },
{ "Profile", "'" + ucProfile.Text + "'" },
{ "Signature", "'" + ucSignature.Text + "'" },
{ "CUser", UID.ToString() },
{ "CWhen", "GETDATE()" },
{ "MUser", UID.ToString() },
{ "MWhen", "GETDATE()" },
{ "Sort", clsDefault.CodeFilter(txtSort.Text) },
{ "Active", "'0'" /*cbActive.Checked?"'1'":"'0'"+"'"*/ }
},
new string[, ] {
{ }
},
dbType,
cs,
out outSQL
))
{
clsMail clsMail = new clsMail();
string outMessage;
string idEncode = Server.UrlEncode(clsSecurity.Encrypt(UID.ToString()));
#region Mail to User
if (!clsMail.SendTemplate(
"UserRegisterConfirm",
clsMail.GetEmailList("GlobalFrom"),
txtEMail.Text,
new string[, ] {
{ "[Username]", txtUsername.Text },
{ "[UIDEncrypt]", idEncode }
},
out outMessage))
{
//lblSQL.Text = clsDefault.AlertMessageColor("เกิดข้อผิดพลาดขณะส่งเมล์ยืนยัน<br/>"+outMessage, clsDefault.AlertType.Fail);
//lblSQL.Focus();
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", "เกิดข้อผิดพลาดขณะส่งเมล์ยืนยัน<br/>" + outMessage, AlertImage: ucColorBox.Alerts.Fail);
return;
}
#endregion
#region Mail to Admin
if (!clsMail.SendTemplate(
"UserRegisterAdmin",
clsMail.GetEmailList("AutoSystemFrom"),
clsMail.GetEmailList("AdminTo"),
new string[, ] {
{ "[Username]", txtUsername.Text }
},
out outMessage))
{
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", "เกิดข้อผิดพลาดขณะส่งเมล์ยืนยัน<br/>" + outMessage, AlertImage: ucColorBox.Alerts.Fail);
return;
}
#endregion
}
else
{
//lblSQL.Text = clsDefault.AlertMessageColor("เกิดข้อผิดพลาดขณะบันทึกลงฐานข้อมูล : " + outSQL, clsDefault.AlertType.Fail);
//lblSQL.Focus();
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", "เกิดข้อผิดพลาดขณะบันทึกข้อมูลลงฐานข้อมูล : " + outSQL, AlertImage: ucColorBox.Alerts.Fail);
return;
}
#endregion
//clsDefault.Redirect("/", "บันทึกข้อมูลเรียบร้อยแล้ว");
ucColorBox1.Redirect("/");
#endregion
}
public string getPatientCountPending(DateTime dtDOEFrom, DateTime dtDOETo, string Company)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
#region Variable
var result = "";
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
#endregion
#region Procedure
strSQL.Append("SELECT ");
strSQL.Append("COUNT(P.PatientGUID)CountNotRegister ");
strSQL.Append("FROM ");
strSQL.Append("patient P ");
strSQL.Append("WHERE ");
strSQL.Append("(P.DOE BETWEEN '" + dtDOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + dtDOETo.ToString("yyyy-MM-dd HH:mm") + "') ");
strSQL.Append("AND P.Company = '" + Company + "' ");
strSQL.Append("AND(SELECT COUNT(RowID) FROM patientchecklist WHERE PatientGUID = P.PatientGUID AND patientchecklist.RegDate IS NULL) > 0;");
result = clsSQL.Return(strSQL.ToString());
#endregion
return result;
}
public DataTable getPatientMobileByBookCreate(DateTime DOEFrom, DateTime DOETo, string BookCreate, bool withHeavyMetal = true)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
#region Variable
var dt = new DataTable();
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
#endregion
#region Procedure
#region SQLQuery
strSQL.Append("SELECT ");
strSQL.Append("P.PatientGUID,");
strSQL.Append("P.No OrderNo,");
strSQL.Append("P.HN,");
strSQL.Append("P.EmployeeID,");
strSQL.Append("CONCAT(P.Forename,' ',P.Surname) Name,");
strSQL.Append("P.POS Position,");
strSQL.Append("P.DEP Department,");
strSQL.Append("P.DIVI Division,");
strSQL.Append("P.SEC Section,");
strSQL.Append("P.Line,");
strSQL.Append("P.Shift,");
strSQL.Append("P.Location Site,");
strSQL.Append("P.Payor,");
strSQL.Append("IFNULL(P.BookCreate,'') BookCreate,");
//strSQL.Append("(SELECT MWhen FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND WFID=1 AND ProStatus=3 LIMIT 0,1) DateRegis,");
strSQL.Append("(SELECT RegDate FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND WFID=1 LIMIT 0,1) DateRegis,");
strSQL.Append("(SUM(PC.WFID=1 AND PC.ProStatus=3)) RegisStatus,");
strSQL.Append("(SUM(PC.WFID=1 AND PC.ProStatus=2)) RegisProStatus2,");
strSQL.Append("ProChkListDetail ProgramDetail,");
strSQL.Append("COUNT(PC.RowID) CountChecklistAll,");
strSQL.Append("SUM(ProStatus>=3) CountChecklistComplete,");
strSQL.Append("SUM(ProStatus=4) CountChecklistCancel,");
//strSQL.Append("(SELECT CONVERT(GROUP_CONCAT(WorkFlow SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND ProStatus<>3) ProgramPending,");
//strSQL.Append("(SELECT CONVERT(GROUP_CONCAT(WorkFlow SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND ProStatus=4) ProgramCancel ");
strSQL.Append("'' ProgramPending,");
strSQL.Append("'' ProgramCancel,");
strSQL.Append("P.SyncStatus,P.SyncWhen ");
strSQL.Append("FROM ");
strSQL.Append("Patient P ");
strSQL.Append("INNER JOIN patientchecklist PC ON P.PatientGUID=PC.PatientGUID ");
var heavyMetalSQL = new StringBuilder();
if (!withHeavyMetal)
{
try
{
string[] heavyMetalNames = System.Configuration.ConfigurationManager.AppSettings["heavyMetalName"].Split(',');
for (int k = 0; k < heavyMetalNames.Length; k++)
{
heavyMetalSQL.Append("AND WorkFlow NOT LIKE '%" + heavyMetalNames[k] + "%' ");
}
}
catch (Exception)
{
heavyMetalSQL.Append("AND WorkFlow NOT LIKE '%ปัสสาวะสารเคมี%' ");
}
}
strSQL.Append(heavyMetalSQL.ToString());
strSQL.Append("WHERE ");
strSQL.Append("(P.DOE BETWEEN '" + DOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + DOETo.ToString("yyyy-MM-dd HH:mm") + "') ");
if (BookCreate != "")
{
strSQL.Append("AND BookCreate = '" + BookCreate + "' ");
}
strSQL.Append("GROUP BY P.PatientGUID ");
strSQL.Append("ORDER BY P.BookCreate,P.No;");
#endregion
dt = clsSQL.Bind(strSQL.ToString());
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["CountChecklistAll"].ToString().Trim() != dt.Rows[i]["CountChecklistComplete"].ToString())
{
dt.Rows[i]["ProgramPending"] = clsSQL.Return("SELECT CONVERT(GROUP_CONCAT(WorkFlow SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID='" + dt.Rows[i]["PatientGUID"].ToString() + "' AND ProStatus<>3 AND ProStatus<>4 " + heavyMetalSQL.ToString());
}
if (dt.Rows[i]["CountChecklistCancel"].ToString().Trim() != "0")
{
//dt.Rows[i]["ProgramCancel"] = clsSQL.Return("SELECT CONVERT(GROUP_CONCAT(WorkFlow SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID='" + dt.Rows[i]["PatientGUID"].ToString() + "' AND ProStatus=4");
dt.Rows[i]["ProgramCancel"] = clsSQL.Return("SELECT CONVERT(GROUP_CONCAT(ProStatusRemark SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID='" + dt.Rows[i]["PatientGUID"].ToString() + "' AND ProStatus=4 "+ heavyMetalSQL.ToString());
}
}
dt.AcceptChanges();
}
#endregion
return dt;
}
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;
}
protected void btSubmit_Click(object sender, EventArgs e)
{
#region Authorize
if (!clsSecurity.LoginChecker("admin"))
{
ucColorBox1.Redirect("/", "กรุณาล็อคอินด้วยสิทธิ์ Admin");
return;
}
#endregion
#region Variable
var strSQL = new StringBuilder();
var id = 0;
var outSQL = "";
var photoName = "";
#endregion
#region Procedure
#region Update
if (Request.QueryString["id"] != null && clsDefault.QueryStringChecker("command") == "edit")
{
id = int.Parse(Request.QueryString["id"].ToString());
#region Photo Upload
if (fuPhoto.HasFile)
{
var clsIO = new clsIO();
string outErrorMessage; string outFilename;
if (clsIO.UploadPhoto(
fuPhoto, pathUpload,
tableDefault + id.ToString(),
out outErrorMessage,
out outFilename,
maxWidth: photoWidth,
maxHeight: photoHeight))
{
photoName = outFilename;
}
else
{
ucColorBox1.Alert("เกิดข้อผิดพลาด", "เกิดข้อผิดพลาดขณะอัพโหลดไฟล์รูปภาพ<br/>" + outErrorMessage, AlertImage: ucColorBox.Alerts.Fail);
return;
}
}
#endregion
if (clsSQL.Update(tableDefault,
new string[, ] {
{ "Icon", (!string.IsNullOrEmpty(photoName)?"'" + pathUpload + photoName + "'":"Icon") },
{ "Name", "'" + txtName.Text.SQLQueryFilter() + "'" },
{ "Detail", "'" + txtDetail.Text.SQLQueryFilter() + "'" },
{ "Content", "'" + ucContent.Text.SQLQueryFilter() + "'" },
{ "NameEN", "'" + txtNameEN.Text.SQLQueryFilter() + "'" },
{ "DetailEN", "'" + txtDetailEN.Text.SQLQueryFilter() + "'" },
{ "ContentEN", "'" + ucContentEN.Text.SQLQueryFilter() + "'" },
{ "MUser", clsSecurity.LoginUID },
{ "MWhen", functionGetDate },
{ "Sort", txtSort.Text.SQLQueryFilter() },
{ "StatusFlag", "'" + (cbActive.Checked ? "A" : "I") + "'" }
}, new string[, ] {
{ }
},
"UID=" + id.ToString(),
out outSQL))
{
ucColorBox1.ReloadParent();
}
else
{
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", outSQL, AlertImage: ucColorBox.Alerts.Fail);
}
}
#endregion
#region Insert
else
{
id = int.Parse(clsSQL.Return("SELECT ISNULL(MAX(UID),0)+1 FROM " + tableDefault));
#region Photo Upload
if (fuPhoto.HasFile)
{
clsIO clsIO = new clsIO();
string outErrorMessage; string outFilename;
if (clsIO.UploadPhoto(
fuPhoto, pathUpload,
tableDefault + id.ToString(),
out outErrorMessage,
out outFilename,
maxWidth: photoWidth,
maxHeight: photoHeight))
{
photoName = outFilename;
}
else
{
ucColorBox1.Alert("เกิดข้อผิดพลาด", "เกิดข้อผิดพลาดขณะอัพโหลดไฟล์รูปภาพ<br/>" + outErrorMessage, AlertImage: ucColorBox.Alerts.Fail);
return;
}
}
#endregion
if (clsSQL.Insert(tableDefault,
new string[, ] {
{ "Icon", (!string.IsNullOrEmpty(photoName)?"'" + pathUpload + photoName + "'":"null") },
{ "Name", "'" + txtName.Text.SQLQueryFilter() + "'" },
{ "Detail", "'" + txtDetail.Text.SQLQueryFilter() + "'" },
{ "Content", "'" + ucContent.Text.SQLQueryFilter() + "'" },
{ "NameEN", "'" + txtNameEN.Text.SQLQueryFilter() + "'" },
{ "DetailEN", "'" + txtDetailEN.Text.SQLQueryFilter() + "'" },
{ "ContentEN", "'" + ucContentEN.Text.SQLQueryFilter() + "'" },
{ "CUser", clsSecurity.LoginUID },
{ "CWhen", functionGetDate },
{ "MUser", clsSecurity.LoginUID },
{ "MWhen", functionGetDate },
{ "Sort", txtSort.Text.SQLQueryFilter() },
{ "StatusFlag", "'" + (cbActive.Checked ? "A" : "I") + "'" }
}, new string[, ] {
{ }
},
out outSQL))
{
ucColorBox1.ReloadParent();
}
else
{
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", outSQL, AlertImage: ucColorBox.Alerts.Fail);
}
}
#endregion
#endregion
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string id = clsDefault.URLRouting("id");
if (!string.IsNullOrEmpty(id))
{
string idDecrypt = clsSecurity.Decrypt(id);
string active = clsSQL.Return(
"SELECT Active FROM [User] WHERE UID=" + parameterChar + "UID",
new string[, ] {
{ parameterChar + "UID", idDecrypt }
},
dbType,
cs);
if (!string.IsNullOrEmpty(active))
{
if (active == "0")
{
string outSQL;
if (clsSQL.Update(
"[User]",
new string[, ] {
{ "Active", "'1'" }, { "MWhen", "GETDATE()" }
},
new string[, ] {
{ "@UID", idDecrypt }
},
"UID=@UID",
dbType,
cs,
out outSQL))
{
#region Mail to Admin
string outMessage;
string Name = clsSQL.Return(
"SELECT Username FROM [User] WHERE UID=" + parameterChar + "UID",
new string[, ] {
{ parameterChar + "UID", idDecrypt }
},
dbType,
cs);
clsMail clsMail = new clsMail();
if (!clsMail.SendTemplate(
"UserRegisterConfirmAdmin",
clsMail.GetEmailList("AutoSystemFrom"),
clsMail.GetEmailList("AdminTo"),
new string[, ] {
{ "[Username]", Name }
},
out outMessage))
{
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", "เกิดข้อผิดพลาดขณะส่งเมล์ยืนยัน<br/>" + outMessage, AlertImage: ucColorBox.Alerts.Fail);
return;
}
#endregion
ucColorBox1.Redirect(
"/",
"ดำเนินการเสร็จสิ้น",
"ระบบยืนยันสถานะสมาชิกของคุณเรียบร้อยแล้ว");
}
else
{
ucColorBox1.Redirect(
"/",
"เกิดข้อผิดพลาด",
"ไม่พบรหัสยืนยันของคุณ");
}
}
else
{
ucColorBox1.Redirect(
"/",
"ดำเนินการเสร็จสิ้น",
"คุณเคยทำการยืนยันอีเมล์ไว้แล้ว");
}
}
else
{
ucColorBox1.Redirect(
"/",
"เกิดข้อผิดพลาด",
"ไม่พบรหัสยืนยันของคุณ");
}
}
else
{
ucColorBox1.Redirect(
"/",
"เกิดข้อผิดพลาด",
"ไม่พบรหัสยืนยันของคุณ");
}
}
}
/// <summary>
/// หา UID ใหม่ในฐานข้อมูล จากเงื่อนไขที่เรากำหนด
/// </summary>
/// <param name="id_column_name">ชื่อฟิลด์ที่ต้องการ (UID)</param>
/// <param name="fromTable">ชื่อ Table</param>
/// <param name="whereStr">เงื่อนไขพิเศษ อาจเว้นว่างไว้ก็ได้ (member_active='1')</param>
/// <param name="strDBType">ชนิดของฐานข้อมูล เช่น sql,odbc,mysql</param>
/// <param name="appsetting_name">ชื่อตัวแปรที่เก็บ ConnectionString ในไฟล์ AppSetting</param>
/// <returns>คืนค่า UID ใหม่</returns>
/// <example>
/// clsSQL.GetNewID("member_id","MEMBER","member_active='1'",clsSQL.DBType.MySQL,"cs");
/// </example>
public int GetNewID(string id_column_name, string fromTable, string whereStr, DBType dbType, string cs)
{
StringBuilder strSQL = new StringBuilder();
int id = 0;
string functionName;
if (dbType == DBType.SQLServer)
{
functionName = "ISNULL";
}
else if (dbType == DBType.MySQL)
{
functionName = "IFNULL";
}
else
{
functionName = "IFNULL";
}
strSQL.Append("SELECT ");
strSQL.Append(functionName + "(MAX(" + id_column_name + "),0)+1 ");
strSQL.Append("FROM ");
strSQL.Append(fromTable + " ");
if (!string.IsNullOrEmpty(whereStr))
{
strSQL.Append("WHERE ");
strSQL.Append(whereStr);
}
clsSQL clsSQL = new clsSQL();
id = int.Parse(clsSQL.Return(strSQL.ToString()));
return id;
}
protected void btSubmit_Click(object sender, EventArgs e)
{
#region Variable
var outSQL = "";
var clsIO = new clsIO();
var outError = "";
var outPhoto = "";
var outMail = "";
#endregion
#region Procedure
if (fuPhoto.HasFile)
{
if (!clsIO.UploadPhoto(
fuPhoto,
"/Upload/Jobs/",
clsSQL.GetNewID("UID", "JobsHistory", "", dbType, cs).ToString(),
out outError,
out outPhoto,
512, 200, 200))
{
ucColorBox1.Alert("เกิดข้อผิดพลาด", "เกิดข้อผิดพลาดขณะอัพโหลดภาพ : " + outError);
return;
}
}
#region InsertData
if (!clsSQL.Insert(
"JobsHistory",
new string[, ] {
{ "JobsUID", ddlJobs.SelectedItem.Value },
{ "JobsName", "'" + txtJobsName.Text.SQLQueryFilter() + "'" },
{ "Salary", "'" + txtSalary.Text.SQLQueryFilter() + "'" },
{ "Start", "'" + txtStart.Text.SQLQueryFilter() + "'" },
{ "ProvinceChange", "'" + rbProvinceChange.SelectedItem.Value + "'" },
{ "PrenameTH", "'" + ddlPrenameTH.SelectedItem.Value + "'" },
{ "ForenameTH", "'" + txtForenameTH.Text.SQLQueryFilter() + "'" },
{ "SurnameTH", "'" + txtSurnameTH.Text.SQLQueryFilter() + "'" },
{ "PrenameEN", "'" + ddlPrenameEN.SelectedItem.Value + "'" },
{ "ForenameEN", "'" + txtForenameEN.Text.SQLQueryFilter() + "'" },
{ "SurnameEN", "'" + txtSurnameEN.Text.SQLQueryFilter() + "'" },
{ "MarriageStatus", "'" + rbMarriageStatus.SelectedItem.Value + "'" },
{ "Gender", "'" + rbGender.SelectedItem.Value + "'" },
{ "Birthdate", "'" + (ucBirthdate.Text.Trim() != ""?ucBirthdate.DateTime.ToString("yyyy-MM-dd"):"1900-01-01 00:00") + "'" },
{ "Birthplace", "'" + txtBirthplace.Text.SQLQueryFilter() + "'" },
{ "Nationality", "'" + txtNationality.Text.SQLQueryFilter() + "'" },
{ "Race", "'" + txtRace.Text.SQLQueryFilter() + "'" },
{ "Religion", "'" + txtReligion.Text.SQLQueryFilter() + "'" },
{ "Weight", "'" + txtWeight.Text.SQLQueryFilter() + "'" },
{ "Height", "'" + txtHeight.Text.SQLQueryFilter() + "'" },
{ "NID", "'" + txtNID.Text.SQLQueryFilter() + "'" },
{ "NIDCreateBy", "'" + txtNIDCreateBy.Text.SQLQueryFilter() + "'" },
{ "NIDExpire", "'" + (ucNIDExpire.Text != ""?ucNIDExpire.DateTime.ToString("yyyy-MM-dd"):"") + "'" },
{ "Phone", "'" + txtPhone.Text.SQLQueryFilter() + "'" },
{ "Email", "'" + txtEmail.Text.SQLQueryFilter() + "'" },
{ "Address", "'" + txtAddress.Text.SQLQueryFilter() + "'" },
{ "EmergencyName", "'" + txtEmergencyName.Text.SQLQueryFilter() + "'" },
{ "EmergencyPhone", "'" + txtEmergencyPhone.Text.SQLQueryFilter() + "'" },
{ "EmergencyEmail", "'" + txtEmergencyEmail.Text.SQLQueryFilter() + "'" },
{ "EmergencyAddress", "'" + txtEmergencyAddress.Text.SQLQueryFilter() + "'" },
{ "EmergencyRelationship", "'" + txtEmergencyRelationship.Text.SQLQueryFilter() + "'" },
{ "Photo", (outPhoto != ""?"'" + outPhoto + "'":"null") },
{ "Education", "'" + ucEducation.Text.SQLQueryFilter() + "'" },
{ "Experience", "'" + ucExperience.Text.SQLQueryFilter() + "'" },
{ "Resume", "null" },
{ "CWhen", "GETDATE()" },
{ "CUser", "0" },
{ "MWhen", "GETDATE()" },
{ "MUser", "0" },
{ "Sort", "0" },
{ "StatusFlag", "'A'" }
},
new string[, ] {
{ }
},
dbType,
cs,
out outSQL))
{
ucColorBox1.Alert("เกิดข้อผิดพลาด", "เกิดข้อผิดพลาดขณะรันคำสั่ง : " + Server.HtmlEncode(outSQL));
return;
}
else
{
#region MailSender
string JobsUID = clsSQL.Return("SELECT MAX(UID) FROM JobsHistory", dbType, cs);
if (!clsMail.SendTemplate(
"JobsApply",
clsMail.GetEmailList("AutoSystemFrom"),
clsMail.GetEmailList("JobsTo"),
new string[, ]
{
{ "[Photo]", "<img src='" + System.Configuration.ConfigurationManager.AppSettings["website"] + "/Upload/Jobs/" + outPhoto + "'/>" },
{ "[UID]", JobsUID },
{ "[JobsName]", ddlJobs.SelectedItem.Text + (txtJobsName.Text.Trim() != ""?" (" + txtJobsName.Text.SQLQueryFilter() + ")":"") },
{ "[PrenameTH]", ddlPrenameTH.SelectedItem.Text },
{ "[ForenameTH]", txtForenameTH.Text },
{ "[SurnameTH]", txtSurnameTH.Text },
{ "[Birthdate]", ucBirthdate.DateTime.ToString("dd/MM/yyyy") },
{ "[Birthplace]", txtBirthplace.Text },
{ "[Gender]", rbGender.SelectedItem.Text },
{ "[Phone]", txtPhone.Text },
{ "[Email]", txtEmail.Text },
{ "[Start]", txtStart.Text.SQLQueryFilter() },
{ "[Education]", ucEducation.Text.SQLQueryFilter() },
{ "[Experience]", ucExperience.Text.SQLQueryFilter() },
{ "[CWhen]", DateTime.Now.ToString("dd/MM/yyyy HH:mm") }
},
out outMail))
{
lblMessage.Text = clsDefault.AlertMessageColor("เกิดข้อผิดพลาดขณะพยายามส่งอีเมล์<br/>" + outMail, clsDefault.AlertType.Fail);
lblMessage.Focus();
return;
}
#endregion
ucColorBox1.Redirect("/Jobs/");
}
#endregion
#endregion
}
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;
}
private static void Sync(string fileFullName)
{
#region Variable
var fi = new FileInfo(fileFullName);
var dt = new DataTable();
var dtMain = new DataTable();
var tblPatientStatusOnMobile = "";
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var countSuccess = 0;
var countFail = 0;
var countSuccessMobileStatus = 0;
var mailMessage = new StringBuilder();
var outSQL = "";
var outMessage = "";
#endregion
#region Procedure
if (fi.Exists)
{
if (!IsFileLocked(fi))
{
#region FileExist
Console.WriteLine(string.Format("Find file : {0} ({1})", "Found", fi.FullName));
Console.WriteLine(string.Format("Read file : {0}", "Processing..."));
dt = XMLSelecter(fi.FullName);
Console.WriteLine(string.Format("Read file : {0}", "Completed"));
if (dt != null && dt.Rows.Count > 0)
{
Console.WriteLine(string.Format("Read DataTable : {0} ({1} Rows)", "Found", dt.Rows.Count.ToString()));
#region UpdateToDatabase
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["PatientGUID"].ToString().Trim().ToLower().Contains("new-regis"))
{
continue;
}
#region Update tblPatientListSTS & tblPatientStatusOnMobile
if (dt.Rows[i]["WFID"].ToString().Trim() == "1" && float.Parse(dt.Rows[i]["ProStatus"].ToString().Trim()) >= 2)
{
tblPatientStatusOnMobile = clsSQL.Return("SELECT StatusOnMobile FROM Patient WHERE rowguid='" + dt.Rows[i]["PatientGUID"].ToString().Trim() + "';");
if (tblPatientStatusOnMobile != "R")
{
if (clsSQL.Execute(
"UPDATE tblPatientList SET STS='R',SyncWhen=GETDATE() WHERE PatientUID='" + dt.Rows[i]["PatientGUID"].ToString().Trim() + "';" +
"UPDATE Patient SET SyncStatus='1',SyncWhen=GETDATE(),StatusOnMobile='R' WHERE rowguid='" + dt.Rows[i]["PatientGUID"].ToString().Trim() + "';", out outMessage))
{
countSuccessMobileStatus += 1;
Console.WriteLine(string.Format("{0} : Update MobileStatus : {1} ({2})", dt.Rows[i]["HN"].ToString(), "Success", dt.Rows[i]["HN"].ToString().Trim()));
mailMessage.Append(string.Format("{0} : Update MobileStatus : {1} ({2})<br/>", dt.Rows[i]["HN"].ToString(), "<span style='color:green;'>Success</span>", dt.Rows[i]["HN"].ToString().Trim()));
}
else
{
countFail += 1;
Console.WriteLine(string.Format("{0} : Update MobileStatus : {1} ({2}) : {3}", dt.Rows[i]["HN"].ToString(), "Fail", dt.Rows[i]["HN"].ToString().Trim(), outMessage));
mailMessage.Append(string.Format("{0} : Update MobileStatus : {1} ({2} : {3})<br/>", dt.Rows[i]["HN"].ToString(), "<span style='color:red;'>Fail</span>", dt.Rows[i]["HN"].ToString().Trim(), outMessage));
}
}
}
#endregion
#region ChecklistUpdate
dtMain = getPatientChecklistMain(dt.Rows[i]["RowID"].ToString());
if (dtMain != null && dtMain.Rows.Count > 0)
{
if (dt.Rows[i]["ProStatus"].ToString().Trim() != dtMain.Rows[0]["ProStatus"].ToString().Trim() ||
dt.Rows[i]["ProStatusRemark"].ToString().Trim() != dtMain.Rows[0]["ProStatusRemark"].ToString().Trim()/*||
dtMobile.Rows[i]["RegDate"].ToString().Trim() != dtMain.Rows[0]["RegDate"].ToString().Trim() ||
dtMobile.Rows[i]["ModifyDate"].ToString().Trim() != dtMain.Rows[0]["ModifyDate"].ToString().Trim()*/)
{
#region UpdateChecklist
if (!clsSQL.Update(
"tblCheckList",
new string[,]
{
{"ProStatus",dt.Rows[i]["ProStatus"].ToString().Trim() },
{"ProStatusRemark","'"+dt.Rows[i]["ProStatusRemark"].ToString().SQLQueryFilter()+"'" },
{"RegDate",(dt.Rows[i]["RegDate"].ToString()!=""?"'"+DateTime.Parse(dt.Rows[i]["RegDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss")+"'":"NULL") },
{"SyncWhen","GETDATE()"}
},
new string[,] { { } },
"RowID=" + dt.Rows[i]["RowID"].ToString(), out outSQL, out outMessage, true))
{
countFail += 1;
Console.WriteLine(string.Format("{0} : Update tblChecklist : {1} ({2})", dt.Rows[i]["HN"].ToString(), "Fail", outMessage));
mailMessage.Append(string.Format("{0} : Update tblChecklist : {1} ({2})<br/>", dt.Rows[i]["HN"].ToString(), "<span style='color:red;'>Fail</span>", outMessage));
}
else
{
#region LogUpdate
countSuccess += 1;
Console.WriteLine(string.Format("{0} : Update tblChecklist : {1} ({2})", dt.Rows[i]["HN"].ToString(), "Success", dtMain.Rows[0]["ProStatus"].ToString().Trim() + "->" + dt.Rows[i]["ProStatus"].ToString().Trim()));
mailMessage.Append(string.Format("{0} : Update tblChecklist : {1} ({2})<br/>", dt.Rows[i]["HN"].ToString(), "<span style='color:green;'>Success</span>", dtMain.Rows[0]["ProStatus"].ToString().Trim() + "->" + dt.Rows[i]["ProStatus"].ToString().Trim()));
#endregion
}
#endregion
}
}
else
{
mailMessage.Append("ไม่พบข้อมูลในไฟล์");
}
#endregion
}
#endregion
Console.WriteLine(string.Format("Summary : Success {0} Fail {1}", countSuccess.ToString(), countFail.ToString()));
Console.WriteLine(string.Format("MailSend : {0}", "Processing..."));
try
{
if (countSuccessMobileStatus > 0 || countSuccess > 0 || countFail > 0)
{
wsDefault.ServiceSoapClient wsDefault = new wsDefault.ServiceSoapClient();
if (wsDefault.MailSend(
System.Configuration.ConfigurationManager.AppSettings["mailTo"],
System.Configuration.ConfigurationManager.AppSettings["site"] + " : " + clsGlobal.ApplicationName + " Console Sync",
"<h1>" + System.Configuration.ConfigurationManager.AppSettings["site"] + " : " + clsGlobal.ApplicationName + " Console Sync" + "</h1><h3><span style='color:#238DBB;'>StatusUpdateSuccess : " + countSuccessMobileStatus.ToString() + "</span> , <span style='color:green;'>Success : " + countSuccess.ToString() + "</span> , <span style='color:red;'>Fail : " + countFail.ToString() + "</span></h3><h4>FileName : " + fileFullName + "</h4><hr/>" + mailMessage.ToString(),
"*****@*****.**",
System.Configuration.ConfigurationManager.AppSettings["site"] + " : " + clsGlobal.ApplicationName,
"", "", "<b>ServerIP</b> : " + clsGlobal.IPAddress() + "<br/><b>ExecutePath</b> : " + clsGlobal.ExecutePathBuilder() + "<br/><b>Application Version</b> : " + clsGlobal.ApplicationVersion(), false))
{
Console.WriteLine(string.Format("MailSend : {0}", "Success"));
}
else
{
Console.WriteLine(string.Format("MailSend : {0}", "Fail"));
}
}
}
catch (Exception exMail)
{
Console.WriteLine(string.Format("MailSend : {0}", "Fail : " + exMail.Message));
}
}
else
{
Console.WriteLine(string.Format("Read DataTable : {0}", "No Data"));
}
try
{
fi.Delete();
}
catch (Exception exDelete)
{
Console.WriteLine(string.Format("Delete file : {0} ({1})", "Fail", exDelete.Message));
}
#endregion
}
else
{
Console.WriteLine(string.Format("Read File : {0} ({1})", "Fail", "FileIsLocked"));
}
}
else
{
//ไม่เจอไฟล์ก็จบไป
}
#endregion
}
/// <summary>
/// หารหัส UID ใหม่จากฟิลด์ประเภท AutoIncrement
/// </summary>
/// <param name="tbName"></param>
/// <param name="dbType"></param>
/// <param name="appSettingName"></param>
/// <returns></returns>
public int GetNewIDAutoIncrement(string tbName)
{
#region Variable
var clsSQL = new clsSQL(dbType, cs);
var strSQL = "";
var id = 0;
#endregion
#region Procedure
if (dbType == DBType.SQLServer)
{
strSQL = "SELECT ISNULL(IDENT_CURRENT('" + tbName + "'),0)+1;";
}
else if (dbType == DBType.MySQL)
{
strSQL = @"SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name='" + tbName + @"'
AND table_schema=DATABASE()";
}
else
{
return 0;
}
try
{
id = int.Parse(clsSQL.Return(strSQL.ToString()));
}
catch (Exception) { }
#endregion
return id;
}
public DataTable getLabSummary()
{
#region Variable
var dt = new DataTable();
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
#endregion
#region Procedure
#region SQLQuery
strSQL.Append("SELECT ");
strSQL.Append("P.Company,");
strSQL.Append("DATE(PL.CWhen) DateAccept,");
//strSQL.Append("(");
//strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
//strSQL.Append("FROM patientlab ");
//strSQL.Append("INNER JOIN patient ON patientlab.LabEpisode = patient.LabEpisode ");
//strSQL.Append("WHERE DATE(PL.CWhen) = DATE(patientlab.CWhen) AND Company = P.Company AND WFID = 6");
//strSQL.Append(") CountBloodComplete,");
strSQL.Append("SUM(PL.WFID = 6)CountBloodComplete,");
//strSQL.Append("(");
//strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
//strSQL.Append("FROM patientchecklist ");
//strSQL.Append("INNER JOIN patient ON patientchecklist.PatientGUID = patient.PatientGUID ");
//strSQL.Append("WHERE Company = P.Company AND WFID = 6");
//strSQL.Append(") CountBloodAll,");
strSQL.Append("0 CountBloodAll,");
//strSQL.Append("(");
//strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
//strSQL.Append("FROM patientlab ");
//strSQL.Append("INNER JOIN patient ON patientlab.LabEpisode = patient.LabEpisode ");
//strSQL.Append("WHERE DATE(PL.CWhen) = DATE(patientlab.CWhen) AND Company = P.Company AND WFID = 7");
//strSQL.Append(") CountUrineComplete,");
strSQL.Append("SUM(PL.WFID=7) CountUrineComplete,");
//strSQL.Append("(");
//strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
//strSQL.Append("FROM patientchecklist ");
//strSQL.Append("INNER JOIN patient ON patientchecklist.PatientGUID = patient.PatientGUID ");
//strSQL.Append("WHERE Company = P.Company AND WFID = 7");
//strSQL.Append(") CountUrineAll,");
strSQL.Append("0 CountUrineAll,");
//strSQL.Append("(");
//strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
//strSQL.Append("FROM patientlab ");
//strSQL.Append("INNER JOIN patient ON patientlab.LabEpisode = patient.LabEpisode ");
//strSQL.Append("WHERE DATE(PL.CWhen) = DATE(patientlab.CWhen) AND Company = P.Company AND WFID = 8");
//strSQL.Append(") CountStoolComplete,");
strSQL.Append("SUM(PL.WFID=8) CountStoolComplete,");
//strSQL.Append("(");
//strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
//strSQL.Append("FROM patientchecklist ");
//strSQL.Append("INNER JOIN patient ON patientchecklist.PatientGUID = patient.PatientGUID ");
//strSQL.Append("WHERE Company = P.Company AND WFID = 8");
//strSQL.Append(") CountStoolAll,");
strSQL.Append("0 CountStoolAll,");
//strSQL.Append("(");
//strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
//strSQL.Append("FROM patientlab ");
//strSQL.Append("INNER JOIN patient ON patientlab.LabEpisode = patient.LabEpisode ");
//strSQL.Append("WHERE DATE(PL.CWhen) = DATE(patientlab.CWhen) AND Company = P.Company AND WFID = 9");
//strSQL.Append(") CountHeavyMetalComplete,");
strSQL.Append("SUM(PL.WFID=9) CountHeavyMetalComplete,");
//strSQL.Append("(");
//strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
//strSQL.Append("FROM patientchecklist ");
//strSQL.Append("INNER JOIN patient ON patientchecklist.PatientGUID = patient.PatientGUID ");
//strSQL.Append("WHERE Company = P.Company AND WFID = 9");
//strSQL.Append(") CountHeavyMetalAll ");
strSQL.Append("0 CountHeavyMetalAll ");
strSQL.Append("FROM ");
strSQL.Append("patient P ");
strSQL.Append("INNER JOIN patientlab PL ON P.LabEpisode = PL.LabEpisode AND PL.StatusFlag = 'A' ");
strSQL.Append("GROUP BY P.Company,DATE(PL.CWhen);");
#endregion
dt = clsSQL.Bind(strSQL.ToString());
if(dt!=null && dt.Rows.Count > 0)
{
for(int i = 0; i < dt.Rows.Count; i++)
{
strSQL.Length = 0;strSQL.Capacity = 0;
strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
strSQL.Append("FROM patientchecklist ");
strSQL.Append("INNER JOIN patient ON patientchecklist.PatientGUID = patient.PatientGUID ");
strSQL.Append("WHERE Company = '"+dt.Rows[i]["Company"].ToString()+"' AND WFID = 6");
dt.Rows[i]["CountBloodAll"] = clsSQL.Return(strSQL.ToString());
strSQL.Length = 0; strSQL.Capacity = 0;
strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
strSQL.Append("FROM patientchecklist ");
strSQL.Append("INNER JOIN patient ON patientchecklist.PatientGUID = patient.PatientGUID ");
strSQL.Append("WHERE Company = '" + dt.Rows[i]["Company"].ToString() + "' AND WFID = 7");
dt.Rows[i]["CountUrineAll"] = clsSQL.Return(strSQL.ToString());
strSQL.Length = 0; strSQL.Capacity = 0;
strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
strSQL.Append("FROM patientchecklist ");
strSQL.Append("INNER JOIN patient ON patientchecklist.PatientGUID = patient.PatientGUID ");
strSQL.Append("WHERE Company = '" + dt.Rows[i]["Company"].ToString() + "' AND WFID = 8");
dt.Rows[i]["CountStoolAll"] = clsSQL.Return(strSQL.ToString());
strSQL.Length = 0; strSQL.Capacity = 0;
strSQL.Append("SELECT COUNT(patient.LabEpisode) ");
strSQL.Append("FROM patientchecklist ");
strSQL.Append("INNER JOIN patient ON patientchecklist.PatientGUID = patient.PatientGUID ");
strSQL.Append("WHERE Company = '" + dt.Rows[i]["Company"].ToString() + "' AND WFID = 9");
dt.Rows[i]["CountHeavyMetalAll"] = clsSQL.Return(strSQL.ToString());
}
dt.AcceptChanges();
}
#endregion
return dt;
}
protected void btSubmit_Click(object sender, EventArgs e)
{
#region Security
if (!clsSecurity.LoginChecker())
{
//clsDefault.Redirect("/Register", "กรุณาสมัครสมาชิก หรือ ล็อคอิน ก่อนเข้าใช้งาน");
ucColorBox1.Redirect("/Register", "กรุณาสมัครสมาชิก หรือ ล็อคอินก่อนเข้าใช้งาน");
}
#endregion
#region Variable
StringBuilder strSQL = new StringBuilder();
string outSQL;
string outError;
string outPhotoName = "Photo";
#endregion
#region Update
#region Photo Upload
if (fuPhoto.HasFile)
{
if (!clsIO.UploadPhoto(fuPhoto, pathPhoto, clsSecurity.LoginUID, 500, photoWidth, photoHeight, "", 0, out outError, out outPhotoName))
{
//lblSQL.Text = clsDefault.AlertMessageColor("เกิดข้อผิดพลาดขณะอัพโหลดภาพ : " + outError, clsDefault.AlertType.Fail);
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", "เกิดข้อผิดพลาดขณะอัพโหลดภาพ : " + outError, AlertImage: ucColorBox.Alerts.Fail);
return;
}
else
{
outPhotoName = "'" + pathPhoto + outPhotoName + "'";
}
}
#endregion
#region Check Data
if (int.Parse(clsSQL.Return("SELECT COUNT(UID) FROM [USER] WHERE UID='" + clsSecurity.LoginUID + "' AND Password='******'", dbType, cs)) == 0)
{
//lblPassword.Text = clsDefault.AlertMessageColor("Password ที่คุณกรอกไม่ถูกต้อง", clsDefault.AlertType.Warn);
ucColorBox1.Alert("ข้อมูลไม่ถูกต้อง", "Password ที่คุณกรอกไม่ถูกต้อง", AlertImage: ucColorBox.Alerts.Fail);
lblPassword.Focus();
return;
}
#endregion
#region SQL Update
if (!clsSQL.Update(
"[User]",
new string[, ] {
{ "Password", txtPasswordChange.Text.Trim() != ""?"'" + clsSecurity.Encrypt(clsDefault.CodeFilter(txtPasswordChange.Text)) + "'":"Password" },
{ "Photo", outPhotoName },
{ "UserGroupUID", "'" + ddlUserGroup.SelectedItem.Value + "'" },
{ "HN", "'" + clsDefault.CodeFilter(txtHN.Text) + "'" },
{ "PName", ddlPName.SelectedItem.Value != "null"?"'" + ddlPName.SelectedItem.Value + "'":"null" },
{ "FName", "'" + clsDefault.CodeFilter(txtFName.Text) + "'" },
{ "LName", "'" + clsDefault.CodeFilter(txtLName.Text) + "'" },
{ "BirthDate", ucDateTimeFlat1.DateTime != DateTime.MinValue?"'" + ucDateTimeFlat1.DateTime.ToString("yyyy-MM-dd HH:mm:ss") + "'":"null" },
{ "Gender", rbGender.SelectedItem.Value != "null"?"'" + rbGender.SelectedItem.Value + "'":"null" },
{ "Phone", "'" + clsDefault.CodeFilter(txtPhone.Text) + "'" },
{ "Mobile", "'" + clsDefault.CodeFilter(txtMobile.Text) + "'" },
{ "Email", "'" + clsDefault.CodeFilter(txtEMail.Text) + "'" },
{ "Address", "'" + clsDefault.CodeFilter(txtAddress.Text) + "'" },
{ "AddressDistrict", "'" + clsDefault.CodeFilter(txtAddressDistrict.Text) + "'" },
{ "AddressPrefecture", "'" + clsDefault.CodeFilter(txtAddressPrefecture.Text) + "'" },
{ "AddressProvince", "'" + clsDefault.CodeFilter(txtAddressProvince.Text) + "'" },
{ "AddressPostal", "'" + clsDefault.CodeFilter(txtAddressPostal.Text) + "'" },
{ "Profile", "'" + ucProfile.Text + "'" },
{ "Signature", "'" + ucSignature.Text + "'" },
{ "MUser", clsSecurity.LoginUID },
{ "MWhen", "GETDATE()" },
{ "Sort", clsDefault.CodeFilter(txtSort.Text) },
{ "Active", cbActive.Checked?"'1'":"'0'" + "'" }
},
new string[, ] {
{ "" + parameterChar + "UID", clsSecurity.LoginUID }
},
"UID=" + parameterChar + "UID",
dbType,
cs,
out outSQL
))
{
//lblSQL.Text = clsDefault.AlertMessageColor("เกิดข้อผิดพลาดขณะบันทึกลงฐานข้อมูล : " + outSQL, clsDefault.AlertType.Fail);
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", "เกิดข้อผิดพลาดขณะบันทึกลงฐานข้อมูล : " + outSQL, AlertImage: ucColorBox.Alerts.Fail);
return;
}
#endregion
//clsDefault.Redirect("/", "บันทึกข้อมูลเรียบร้อยแล้ว");
ucColorBox1.Redirect("/", "บันทึกข้อมูลเรียบร้อยแล้ว");
#endregion
}
public int getMaxDateRegis(DateTime DOEFrom,DateTime DOETo)
{
#region Variable
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var result = 0;
#endregion
#region Procedure
strSQL.Append("SELECT COUNT(A.Result) ");
strSQL.Append("FROM(");
strSQL.Append("SELECT patient.Payor, DATE(patientchecklist.MWhen) Result ");
strSQL.Append("FROM patient ");
strSQL.Append("INNER JOIN patientchecklist ON patient.PatientGUID = patientchecklist.PatientGUID AND patientchecklist.WFID = 1 AND patientchecklist.ProStatus = 3 ");
strSQL.Append("WHERE ");
strSQL.Append("patient.DOE BETWEEN '" + DOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + DOETo.ToString("yyyy-MM-dd HH:mm") + "' ");
strSQL.Append("GROUP BY patient.Payor, DATE(patientchecklist.MWhen) ");
strSQL.Append(") A ");
strSQL.Append("GROUP BY A.Payor ");
strSQL.Append("ORDER BY COUNT(A.Result) DESC ");
strSQL.Append("LIMIT 0, 1;");
try
{
var resultTemp = clsSQL.Return(strSQL.ToString());
if (resultTemp != "")
{
result = int.Parse(resultTemp);
}
else { result = 0; }
}
catch (Exception) { }
#endregion
return result;
}
protected void Page_Load(object sender, EventArgs e)
{
#region Variable Builder
DateTime dttm = DateTime.Now;
DayOfWeek = (int)dttm.DayOfWeek;
for (int d = 0; d < 7; d++)
{
int dow = (int)dttm.AddDays(d).DayOfWeek;
int day = dttm.AddDays(d).Day;
Day[dow] = day.ToString();
}
if (clsLanguage.LanguageCurrent == "th-TH")
{
SpecialtyText = "เชี่ยวชาญ";
DepartmentText = "ประจำ";
EducationText = "ประวัติการศึกษา";
DayText[0] = "อาทิตย์";
DayText[1] = "จันทร์";
DayText[2] = "อังคาร";
DayText[3] = "พุธ";
DayText[4] = "พฤหัส";
DayText[5] = "ศุกร์";
DayText[6] = "เสาร์";
}
else
{
SpecialtyText = "Specialty";
DepartmentText = "Center";
EducationText = "Education";
DayText[0] = "Sunday";
DayText[1] = "Monday";
DayText[2] = "Tuesday";
DayText[3] = "Wednesday";
DayText[4] = "Thursday";
DayText[5] = "Friday";
DayText[6] = "Saturday";
}
#endregion
if (!IsPostBack)
{
//clsSyncer clsSyncer = new clsSyncer();
//clsSyncer.DoctorScheduleSyncer();
var lastUpdate = clsSQL.Return("SELECT TOP 1 CWhen FROM DoctorSchedule", dbType, cs);
lblDetail.Text += "<div style='font-size:8pt;color:#E89D2D;'>LastUpdate : " + (!string.IsNullOrEmpty(lastUpdate) ? DateTime.Parse(lastUpdate).ToString("dd/MM/yyyy HH:mm") : "-") + "</div>";
BindSearch();
#region SearchByURLRouting
//"DoctorSchedule/{name}/{special}/{dept}/{sun}/{mon}/{tue}/{wed}/{thu}/{fri}/{sat}/"
if (clsDefault.URLRouting("sat") != "")
{
if (clsDefault.URLRouting("name") != "null")
{
txtSearchName.Text = clsDefault.URLRouting("name");
}
if (clsDefault.URLRouting("special") != "null")
{
ddlSearchSpecialty.SelectedValue = clsDefault.URLRouting("special");
}
if (clsDefault.URLRouting("dept") != "null")
{
ddlSearchMedicalCenter.SelectedValue = clsDefault.URLRouting("dept");
}
if (clsDefault.URLRouting("sun") != "0")
{
cbSearchSchedule.Items[0].Selected = true;
}
if (clsDefault.URLRouting("mon") != "0")
{
cbSearchSchedule.Items[1].Selected = true;
}
if (clsDefault.URLRouting("tue") != "0")
{
cbSearchSchedule.Items[2].Selected = true;
}
if (clsDefault.URLRouting("wed") != "0")
{
cbSearchSchedule.Items[3].Selected = true;
}
if (clsDefault.URLRouting("thu") != "0")
{
cbSearchSchedule.Items[4].Selected = true;
}
if (clsDefault.URLRouting("fri") != "0")
{
cbSearchSchedule.Items[5].Selected = true;
}
if (clsDefault.URLRouting("sat") != "0")
{
cbSearchSchedule.Items[6].Selected = true;
}
}
#endregion
DoctorBuilder();
}
}
public string getPatientCountAll(DateTime dtDOEFrom,DateTime dtDOETo,string Company)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
#region Variable
var result = "";
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
#endregion
#region Procedure
strSQL.Append("SELECT COUNT(PatientGUID) FROM patient WHERE (DOE BETWEEN '" + dtDOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + dtDOETo.ToString("yyyy-MM-dd HH:mm") + "') AND Company='"+Company+"'");
result = clsSQL.Return(strSQL.ToString());
#endregion
return result;
}
