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