private void BindControl() { #region Variable StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #endregion #region SQL Query strSQL.Append("SELECT "); strSQL.Append("UID,Name "); strSQL.Append("FROM "); strSQL.Append("Language "); strSQL.Append("WHERE "); strSQL.Append("Active='1' "); strSQL.Append("ORDER BY "); strSQL.Append("Sort,Name"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { ddlLanguage.DataSource = dt; ddlLanguage.DataValueField = "UID"; ddlLanguage.DataTextField = "Name"; ddlLanguage.DataBind(); } else { ddlLanguage.Enabled = false; } }
private void BindControl(string type) { #region Variable StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #endregion #region Data Builder #region SQL Query strSQL.Append("SELECT "); strSQL.Append("UID,"); strSQL.Append("Name "); strSQL.Append("FROM "); strSQL.Append("WebboardType "); strSQL.Append("ORDER BY "); strSQL.Append("Sort,Name"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { ddlWebboardType.DataSource = dt; ddlWebboardType.DataTextField = "Name"; ddlWebboardType.DataValueField = "UID"; ddlWebboardType.DataBind(); if (type != "") { ddlWebboardType.SelectedValue = type; } } #endregion }
private void DefaultBuilder() { gvDefault.Visible = true; pnDetail.Visible = false; #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("B.Name LanguageName,"); strSQL.Append("A.UID,"); strSQL.Append("A.PackageName Subject,"); strSQL.Append("A.DetailSub,"); strSQL.Append("A.PicThumbnail,"); strSQL.Append("A.UnitPrice,"); strSQL.Append("A.ActiveDateFrom,"); strSQL.Append("A.ActiveDateTo,"); strSQL.Append("A.DepartmentUID,"); strSQL.Append("A.StatusFlag "); strSQL.Append("FROM "); strSQL.Append("Package A INNER JOIN Language B ON A.LanguageUID=B.UID AND B.Active='1' "); if (clsSecurity.LoginChecker("admin")) { strSQL.Append("ORDER BY "); strSQL.Append("A.MWhen DESC;"); } else { strSQL.Append("WHERE "); strSQL.Append("StatusFlag='A' "); strSQL.Append("AND B.Name='" + clsLanguage.LanguageCurrent + "' "); strSQL.Append("AND (ActiveDateFrom IS NULL OR ActiveDateFrom <= GETDATE()) "); strSQL.Append("AND (ActiveDateTo IS NULL OR ActiveDateTo >= GETDATE()) "); strSQL.Append("ORDER BY "); strSQL.Append("A.MWhen DESC;"); } #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { gvDefault.DataSource = dt; gvDefault.DataBind(); } else { lblDefault.Text = clsDefault.AlertMessageColor("ไม่พบข้อมูล"); } #endregion }
private void BindControl() { #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Language #region SQL Query strSQL.Append("SELECT "); strSQL.Append("UID,"); strSQL.Append("Detail,"); strSQL.Append("Icon "); strSQL.Append("FROM "); strSQL.Append("Language "); strSQL.Append("WHERE "); strSQL.Append("Active='1' "); strSQL.Append("ORDER BY "); strSQL.Append("Sort ASC"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); strSQL.Length = 0; strSQL.Capacity = 0; if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { rbLanguage.Items.Add( new ListItem( "<img src='" + dt.Rows[i]["Icon"].ToString() + "'/> " + dt.Rows[i]["Detail"].ToString(), dt.Rows[i]["UID"].ToString() ) ); if (i == 0) { rbLanguage.Items[i].Selected = true; } } try { rbLanguage.SelectedValue = clsDefault.QueryStringChecker("language"); rbLanguage.Enabled = false; } catch (Exception ex) { } dt = null; } #endregion }
private void MedicalCenterBuilder() { #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("UID,Name "); strSQL.Append("FROM "); strSQL.Append("MedicalCenter "); strSQL.Append("WHERE "); strSQL.Append("LanguageUID=" + clsLanguage.LanguageUIDCurrent + " "); strSQL.Append("AND Active='1' "); strSQL.Append("ORDER BY "); strSQL.Append("Sort,Name"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { ddlMedicalCenter.DataSource = dt; ddlMedicalCenter.DataTextField = "Name"; ddlMedicalCenter.DataValueField = "UID"; ddlMedicalCenter.DataBind(); } ddlMedicalCenter.Items.Insert(0, new ListItem("- ไม่ระบุ -", "null")); #endregion }
public DataTable IntroPageBuilder() { #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("Photo,Name "); strSQL.Append("FROM "); strSQL.Append("IntroPage "); strSQL.Append("WHERE "); strSQL.Append("StatusFlag='A' "); strSQL.Append("AND (("); strSQL.Append("ActiveIgnoreYear='0' "); strSQL.Append("AND (ActiveFrom IS NULL OR ActiveFrom <= GETDATE()) "); strSQL.Append("AND (ActiveTo IS NULL OR ActiveTo >= GETDATE())"); strSQL.Append(") "); strSQL.Append("OR ("); strSQL.Append("ActiveIgnoreYear='1' "); strSQL.Append("AND (ActiveFrom IS NULL OR CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(YEAR,GETDATE()))+'-'+CONVERT(VARCHAR,DATEPART(MONTH,ActiveFrom))+'-'+CONVERT(VARCHAR,DATEPART(DAY,ActiveFrom))) <= GETDATE()) "); strSQL.Append("AND (ActiveTo IS NULL OR CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(YEAR,GETDATE()))+'-'+CONVERT(VARCHAR,DATEPART(MONTH,ActiveTo))+'-'+CONVERT(VARCHAR,DATEPART(DAY,ActiveTo))) >= GETDATE())"); strSQL.Append(")) "); strSQL.Append("ORDER BY "); strSQL.Append("Sort ASC;"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); #endregion return(dt); }
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 }
protected void BindDefault() { gvDefault.Visible = true; pnDGHeader.Visible = true; #region Variable StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #endregion #region SQL Query strSQL.Append("SELECT "); strSQL.Append(tableDefault + ".UID,"); strSQL.Append(tableDefault + ".HN,"); strSQL.Append(tableDefault + ".PName,"); strSQL.Append(tableDefault + ".FName,"); strSQL.Append(tableDefault + ".LName,"); strSQL.Append(tableDefault + ".Email,"); strSQL.Append(tableDefault + ".Phone,"); strSQL.Append(tableDefault + ".DoctorName,"); strSQL.Append(tableDefault + ".DepartmentName,"); strSQL.Append(tableDefault + ".Comment,"); strSQL.Append(tableDefault + ".BirthDate,"); strSQL.Append(tableDefault + ".NID,"); strSQL.Append(tableDefault + ".AppointmentDate,"); strSQL.Append(tableDefault + ".MWhen,"); strSQL.Append(tableDefault + ".Sort,"); strSQL.Append(tableDefault + ".Active "); strSQL.Append("FROM "); strSQL.Append(tableDefault + " "); strSQL.Append("ORDER BY "); strSQL.Append(tableDefault + ".Sort"); strSQL.Append("," + tableDefault + ".MWhen DESC"); #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 DefaultBuilder(string id) { #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("Name,Detail "); strSQL.Append("FROM "); strSQL.Append("Jobs "); strSQL.Append("WHERE "); strSQL.Append("UID=" + id + ";"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { lblName.Text = dt.Rows[0]["Name"].ToString(); lblDetail.Text = dt.Rows[0]["Detail"].ToString(); } else { clsColorBox clsColorBox = new clsColorBox(); clsColorBox.Refresh(); } #endregion }
private void DefaultBuilder() { #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("UID,Name,Detail "); strSQL.Append("FROM "); strSQL.Append("Jobs "); strSQL.Append("WHERE "); strSQL.Append("StatusFlag='A' "); strSQL.Append("ORDER BY "); strSQL.Append("Sort ASC;"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { gvDefault.DataSource = dt; gvDefault.DataBind(); } else { } #endregion }
protected void BindDetail(string id) { StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #region SQL Query strSQL.Append("SELECT "); strSQL.Append(tableDefault + ".Word,"); strSQL.Append(tableDefault + ".WordReplace,"); strSQL.Append(tableDefault + ".Sort,"); strSQL.Append(tableDefault + ".Active "); strSQL.Append("FROM "); strSQL.Append(tableDefault + " "); strSQL.Append("WHERE "); strSQL.Append(tableDefault + ".UID=" + parameterChar + "ID"); #endregion dt = clsSQL.Bind(strSQL.ToString(), new string[, ] { { parameterChar + "ID", id } }, dbType, cs); if (dt != null && dt.Rows.Count > 0) { txtWord.Text = dt.Rows[0]["Word"].ToString(); txtWordReplace.Text = dt.Rows[0]["WordReplace"].ToString(); txtSort.Text = dt.Rows[0]["Sort"].ToString(); cbActive.Checked = (dt.Rows[0]["Active"].ToString() == "1" ? true : false); } else { ucColorBox1.Redirect(webDefault, "ไม่พบหน้าเว็บที่คุณต้องการ"); } }
protected void DefaultBuilder() { gvDefault.Visible = true; pnDGHeader.Visible = true; #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQL Query strSQL.Append("SELECT "); strSQL.Append("A.UID,"); strSQL.Append("A.Photo,"); strSQL.Append("A.Name,"); strSQL.Append("A.Detail,"); strSQL.Append("A.Type,"); 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("Type='Progress' "); strSQL.Append("Type=Type "); #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 = ""; gvDefault.DataSource = dt; #region PageBuilder if (Request.QueryString["page"] != null) { try { gvDefault.PageIndex = int.Parse(Request.QueryString["page"].ToString()); } catch (Exception) { gvDefault.PageIndex = int.Parse(Request.QueryString["page"].ToString()) - 1; } } #endregion gvDefault.DataBind(); dt = null; } else { pnDGHeader.Visible = false; lblDG.Text = clsDefault.AlertMessageColor("ไม่พบข้อมูลที่ต้องการ", clsDefault.AlertType.Info); } #endregion #endregion }
/// <summary> /// ใช้ตรวจสอบ Username Password และสร้าง Session Cookie /// </summary> /// <param name="Username">Login Username</param> /// <param name="Password">Login Password</param> /// <param name="CreateCookie">สร้าง Cookie ด้วยไหม</param> /// <returns>ผลการล็อคอิน</returns> /// <example> /// clsSecurity.LoginChecker("offduiclub","off1234",false); /// clsSecurity.LoginChecker("offduiclub","off1234"); /// </example> public bool LoginChecker(string Username, string Password, bool CreateCookie = false) { bool rtnValue = false; DataTable dt = new DataTable(); clsDefault clsDefault = new clsDefault(); clsSQL clsSQL = new clsSQL(); StringBuilder strSQL = new StringBuilder(); #region SQL Query strSQL.Append("SELECT "); strSQL.Append("[User].UID,"); strSQL.Append("[User].Username,"); strSQL.Append("UserGroup.Name AS UserGroupName,"); strSQL.Append("ISNULL(UserGroup.Authority,'') AS GroupAuthority,"); strSQL.Append("ISNULL([User].Authority,'') AS UserAuthority "); strSQL.Append("FROM "); strSQL.Append("[User] "); strSQL.Append("INNER JOIN UserGroup "); strSQL.Append("ON [User].UserGroupUID=UserGroup.UID AND UserGroup.Active='1' "); strSQL.Append("WHERE "); strSQL.Append("[User].Username="******"Username "); strSQL.Append("AND [User].Password="******"Password "); strSQL.Append("AND [User].Active='1'"); #endregion dt = clsSQL.Bind( strSQL.ToString(), new string[, ] { { "" + _parameterChar + "Username", Username }, { "" + _parameterChar + "Password", Encrypt(Password) } }, _dbType, _cs ); if (dt != null && dt.Rows.Count > 0) { rtnValue = true; SetLoginSession( _sessionName, new string[] { dt.Rows[0]["UID"].ToString(), dt.Rows[0]["Username"].ToString(), dt.Rows[0]["UserGroupName"].ToString(), dt.Rows[0]["GroupAuthority"].ToString(), dt.Rows[0]["UserAuthority"].ToString() } ); if (CreateCookie) { clsDefault.CookieCreate(_sessionName, Encrypt(dt.Rows[0]["UID"].ToString())); } } return(rtnValue); }
/// <summary> /// ใช้ตรวจสอบ Username Password และสร้าง Session Cookie /// </summary> /// <param name="Username">Login Username</param> /// <param name="Password">Login Password</param> /// <param name="CreateCookie">สร้าง Cookie ด้วยไหม</param> /// <returns>ผลการล็อคอิน</returns> /// <example> /// clsSecurity.LoginChecker("offduiclub","off1234",false); /// clsSecurity.LoginChecker("offduiclub","off1234"); /// </example> public bool LoginChecker(string Username, string Password, bool CreateCookie = false) { #region Variable var result = false; var dt = new DataTable(); var clsDefault = new clsDefault(); var clsSQL = new clsSQL(_dbType, _cs); var strSQL = new StringBuilder(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("A.UID,"); strSQL.Append("A.Username,"); strSQL.Append("B.Name AS UserGroupName,"); strSQL.Append((_dbType == clsSQL.DBType.MySQL?"IFNULL":"ISNULL") + "(B.Authority,'') AS GroupAuthority,"); strSQL.Append((_dbType == clsSQL.DBType.MySQL?"IFNULL":"ISNULL") + "(A.Authority,'') AS UserAuthority "); strSQL.Append("FROM "); strSQL.Append("[User] A "); strSQL.Append("INNER JOIN UserGroup B "); strSQL.Append("ON A.UserGroupUID=B.UID AND B.StatusFlag='A' "); strSQL.Append("WHERE "); strSQL.Append("A.Username="******"Username "); strSQL.Append("AND A.Password="******"Password "); strSQL.Append("AND A.StatusFlag='A'"); #endregion dt = clsSQL.Bind( strSQL.ToString(), new string[, ] { { "" + _parameterChar + "Username", Username }, { "" + _parameterChar + "Password", Encrypt(Password) } } ); if (dt != null && dt.Rows.Count > 0) { result = true; SetLoginSession( _sessionName, new string[] { dt.Rows[0]["UID"].ToString(), dt.Rows[0]["Username"].ToString(), dt.Rows[0]["UserGroupName"].ToString(), dt.Rows[0]["GroupAuthority"].ToString(), dt.Rows[0]["UserAuthority"].ToString() } ); if (CreateCookie) { clsDefault.CookieCreate(_sessionName, Encrypt(dt.Rows[0]["UID"].ToString())); } } #endregion return(result); }
private void setDefault() { var uid = clsDefault.URLRouting("id"); if (string.IsNullOrEmpty(uid)) { return; } #region Variable var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); var dt = new DataTable(); var strSQL = new StringBuilder(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("Icon,Name,Detail,Content,NameEN,DetailEN,ContentEN "); strSQL.Append("FROM "); strSQL.Append("P5_ProductGroup "); strSQL.Append("WHERE "); strSQL.Append("StatusFlag='A' "); strSQL.Append("AND UID=@UID;"); #endregion dt = clsSQL.Bind(strSQL.ToString(), new string[, ] { { "@UID", uid } }); if (dt != null && dt.Rows.Count > 0) { productIcon = dt.Rows[0]["Icon"].ToString(); if (clsLanguage.LanguageCurrent == "th-TH") { productName = dt.Rows[0]["Name"].ToString(); productDetail = dt.Rows[0]["Detail"].ToString(); productContent = dt.Rows[0]["Content"].ToString().Replace("../Upload/", "/Upload/"); } else { productName = dt.Rows[0]["NameEN"].ToString(); productDetail = dt.Rows[0]["DetailEN"].ToString(); productContent = dt.Rows[0]["ContentEN"].ToString().Replace("../Upload/", "/Upload/"); } this.Title = productName; lblIcon.Text = "<img src='" + productIcon + "' alt='" + productName + "' title='" + productName + "' style='width:100px;'/>"; lblName.Text = productName; lblDetail.Text = productDetail; lblContent.Text = productContent; } else { productName = "- ไม่พบข้อมูล -"; } #endregion }
protected void BindDetail(string id) { #region Variable StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #endregion #region SQL Query strSQL.Append("SELECT "); strSQL.Append(tableDefault + ".Photo,"); strSQL.Append(tableDefault + ".Name,"); strSQL.Append(tableDefault + ".Detail,"); strSQL.Append(tableDefault + ".ActiveFrom,"); strSQL.Append(tableDefault + ".ActiveTo,"); strSQL.Append(tableDefault + ".ActiveIgnoreYear,"); strSQL.Append(tableDefault + ".Sort,"); strSQL.Append(tableDefault + ".StatusFlag "); strSQL.Append("FROM "); strSQL.Append(tableDefault + " "); strSQL.Append("WHERE "); strSQL.Append(tableDefault + ".UID=" + parameterChar + "ID"); #endregion dt = clsSQL.Bind(strSQL.ToString(), new string[, ] { { parameterChar + "ID", id } }, dbType, cs); if (dt != null && dt.Rows.Count > 0) { #region Data Builder if (dt.Rows[0]["Photo"] != DBNull.Value) { lblPhoto.Text = "<img src='" + dt.Rows[0]["Photo"].ToString() + "' style='width:200px;'/><br/>"; } txtName.Text = dt.Rows[0]["Name"].ToString(); txtDetail.Text = dt.Rows[0]["Detail"].ToString(); if (dt.Rows[0]["ActiveFrom"].ToString() != "") { ucActiveFrom.DateTime = DateTime.Parse(dt.Rows[0]["ActiveFrom"].ToString()); } if (dt.Rows[0]["ActiveTo"].ToString() != "") { ucActiveTo.DateTime = DateTime.Parse(dt.Rows[0]["ActiveTo"].ToString()); } cbActiveIgnoreYear.Checked = (dt.Rows[0]["ActiveIgnoreYear"].ToString() == "1"?true:false); txtSort.Text = dt.Rows[0]["Sort"].ToString(); cbActive.Checked = (dt.Rows[0]["StatusFlag"].ToString() == "A" ? true : false); #endregion } else { ucColorBox1.Redirect("/Management/" + webDefault + "?group=" + clsDefault.QueryStringChecker("group"), "ไม่พบหน้าเว็บที่คุณต้องการ"); } }
protected void BindDefault() { gvDefault.Visible = true; pnDGHeader.Visible = true; StringBuilder strSQL = new StringBuilder(); #region SQL Query strSQL.Append("SELECT "); strSQL.Append("Content.UID,"); strSQL.Append("Content.Name,"); strSQL.Append("Content.Detail,"); strSQL.Append("Content.MWhen,"); strSQL.Append("Content.Active,"); strSQL.Append("Language.Name LanguageName,"); strSQL.Append("Language.Icon LanguageIcon "); strSQL.Append("FROM "); strSQL.Append("Content "); strSQL.Append("INNER JOIN Language ON Content.LanguageUID=Language.UID "); strSQL.Append("ORDER BY "); strSQL.Append("Content.Name,"); strSQL.Append("Language.Sort"); #endregion DataTable dt = new DataTable(); 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); } strSQL.Length = 0; strSQL.Capacity = 0; }
private void WebboardGroupBuilder() { #region Variable StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #endregion #region Data Builder #region SQL Query strSQL.Append("SELECT "); strSQL.Append("UID,"); strSQL.Append("Icon,"); strSQL.Append("Name,"); strSQL.Append("Detail,"); strSQL.Append("(SELECT COUNT(UID) FROM WebboardQuestion WHERE WebboardGroupUID=T.UID AND Active='1') QuestionCount,"); strSQL.Append("(SELECT COUNT(WebboardAnswer.UID) FROM WebboardAnswer INNER JOIN WebboardQuestion "); strSQL.Append("ON WebboardAnswer.WebboardQuestionUID=WebboardQuestion.UID AND WebboardQuestion.Active='1' AND WebboardQuestion.WebboardGroupUID=T.UID "); strSQL.Append("WHERE WebboardAnswer.Active='1')AnswerCount,"); #region Last Update strSQL.Append("(SELECT TOP 1 UID FROM WebboardQuestion WHERE WebboardGroupUID=T.UID AND Active='1' ORDER BY CWhen DESC) QuestionLastUID,"); strSQL.Append("(SELECT TOP 1 Name FROM WebboardQuestion WHERE WebboardGroupUID=T.UID AND Active='1' ORDER BY CWhen DESC) QuestionLastName,"); strSQL.Append("(SELECT TOP 1 CWhen FROM WebboardQuestion WHERE WebboardGroupUID=T.UID AND Active='1' ORDER BY CWhen DESC) QuestionLastCWhen,"); strSQL.Append("(SELECT TOP 1 CName FROM WebboardQuestion WHERE WebboardGroupUID=T.UID AND Active='1' ORDER BY CWhen DESC) QuestionLastCName,"); strSQL.Append("(SELECT TOP 1 [Username] FROM WebboardQuestion LEFT JOIN [User] ON WebboardQuestion.CUser=[User].UID WHERE WebboardGroupUID=T.UID AND WebboardQuestion.Active='1' ORDER BY WebboardQuestion.CWhen DESC) QuestionLastCUsername,"); #endregion strSQL.Append("Active "); strSQL.Append("FROM "); strSQL.Append(tableDefault + " T "); strSQL.Append("WHERE "); strSQL.Append("WebboardTypeUID=" + _webboardTypeUID + " "); if (clsSecurity.LoginGroup != "Admin") { strSQL.Append("AND Active='1' "); } strSQL.Append("ORDER BY "); strSQL.Append("Sort,Name"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { gvDefault.Visible = true; gvDefault.DataSource = dt; gvDefault.DataBind(); lblDefault.Text = ""; } else { gvDefault.Visible = false; lblDefault.Text = "<div style='padding:10px;text-align:center;border-top:1px solid #E8E8E8;'>-</div>"; } #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 }
private string ReservedWords(string content) { #region Variable string contentOut = content; StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #endregion #region ReservedWords #region SQL Query strSQL.Append("SELECT "); strSQL.Append("Word,WordReplace "); strSQL.Append("FROM "); strSQL.Append("WebboardReservedWords "); strSQL.Append("WHERE "); strSQL.Append("Active='1' "); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); strSQL.Length = 0; strSQL.Capacity = 0; if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { contentOut = contentOut.Replace( dt.Rows[i]["Word"].ToString(), ( dt.Rows[i]["WordReplace"] != DBNull.Value? dt.Rows[i]["WordReplace"].ToString(): "*" ) ); } dt = null; } #endregion return(contentOut); }
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 }
protected void BindDetail(string id) { #region Variable var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQL Query strSQL.Append("SELECT "); strSQL.Append("A.Photo,"); strSQL.Append("A.Name,A.NameEN,"); strSQL.Append("A.Detail,A.DetailEN,"); strSQL.Append("A.MetaKeywords,"); strSQL.Append("A.MetaDescription,"); strSQL.Append("A.Sort,"); strSQL.Append("A.StatusFlag "); strSQL.Append("FROM "); strSQL.Append(tableDefault + " A "); strSQL.Append("WHERE "); strSQL.Append("UID=" + parameterChar + "ID"); #endregion dt = clsSQL.Bind(strSQL.ToString(), new string[, ] { { parameterChar + "ID", id } }); if (dt != null && dt.Rows.Count > 0) { vdPhoto.Enabled = false; #region Data Builder if (dt.Rows[0]["Photo"] != DBNull.Value) { lblPhoto.Text = "<img src='" + dt.Rows[0]["Photo"].ToString() + "'/><br/>"; } txtName.Text = dt.Rows[0]["Name"].ToString(); txtDetail.Text = dt.Rows[0]["Detail"].ToString(); txtNameEN.Text = dt.Rows[0]["NameEN"].ToString(); txtDetailEN.Text = dt.Rows[0]["DetailEN"].ToString(); txtMetaKeyword.Text = dt.Rows[0]["MetaKeywords"].ToString(); txtMetaDescription.Text = dt.Rows[0]["MetaDescription"].ToString(); txtSort.Text = dt.Rows[0]["Sort"].ToString(); cbActive.Checked = (dt.Rows[0]["StatusFlag"].ToString() == "A" ? true : false); #endregion } else { ucColorBox1.Redirect("/Management/" + webDefault, "ไม่พบหน้าเว็บที่คุณต้องการ"); } #endregion }
private void setPortfolio() { #region Variable var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); var dt = new DataTable(); var strSQL = new StringBuilder(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("UID,Photo,Name,Detail,NameEN,DetailEN "); strSQL.Append("FROM "); strSQL.Append("P5_PhotoGalleryGroup "); strSQL.Append("WHERE "); strSQL.Append("StatusFlag='A' "); strSQL.Append("ORDER BY "); strSQL.Append("Sort ASC;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { var name = ""; var detail = ""; if (clsLanguage.LanguageCurrent == "th-TH") { name = dt.Rows[i]["Name"].ToString(); detail = dt.Rows[i]["Detail"].ToString(); } else { name = dt.Rows[i]["NameEN"].ToString(); detail = dt.Rows[i]["DetailEN"].ToString(); } if (i % 4 == 0) { portfolioOut += "<br/>"; } portfolioOut += "<div style='display:inline-block;text-align:center;margin:0 15px;'>"; portfolioOut += "<a href='/Portfolio/" + dt.Rows[i]["UID"].ToString() + "/" + name.Trim().Replace(" ", "-").Replace("/", "-").Replace(@"\", "-") + "/' class='cbPortfolio' title='" + detail + "'>"; portfolioOut += "<img src='" + dt.Rows[i]["Photo"].ToString() + "' style='b' title='" + detail + "'/>"; portfolioOut += "<p style='color:#4D4D4D;font-family:thaisans_neueregular;'>" + name + "</p>"; portfolioOut += "</a>"; portfolioOut += "</div>"; } } #endregion }
private void setProductGroup() { #region Variable var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); var dt = new DataTable(); var strSQL = new StringBuilder(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("UID,Name,Detail,NameEN,DetailEN "); strSQL.Append("FROM "); strSQL.Append("P5_ProductGroup "); strSQL.Append("WHERE "); strSQL.Append("StatusFlag='A' "); strSQL.Append("ORDER BY "); strSQL.Append("Sort,Name;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); if (dt != null && dt.Rows.Count > 0) { menuProductGroup.Text += "<div class='submenu'><ul>"; for (int i = 0; i < dt.Rows.Count; i++) { menuProductGroup.Text += "<li style='padding-left:20px;'>"; //menuProductGroup.Text += "<a href='/Product/"+ dt.Rows[i]["UID"].ToString() + "/"+ // dt.Rows[i]["Name"].ToString().Replace(" ","-").Replace("/","-").Replace(@"\", "-").Replace("<", "-").Replace(">", "-") + // "/' alt='"+ dt.Rows[i]["Detail"].ToString() + "'>"+ // " - " + dt.Rows[i]["Name"].ToString() + // "</a>"; if (clsLanguage.LanguageCurrent == "th-TH") { menuProductGroup.Text += "<a href='#product' class='more scrolly'>" + " - " + dt.Rows[i]["Name"].ToString() + "</a>"; } else { menuProductGroup.Text += "<a href='#product' class='more scrolly'>" + " - " + (dt.Rows[i]["NameEN"].ToString() != ""? dt.Rows[i]["NameEN"].ToString(): dt.Rows[i]["Name"].ToString()) + "</a>"; } menuProductGroup.Text += "</li>"; } menuProductGroup.Text += "</ul></div>"; } #endregion }
protected void BindDetail(string id) { #region Variable StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #endregion #region SQL Query strSQL.Append("SELECT "); strSQL.Append(tableDefault + ".PhotoPreview,"); strSQL.Append(tableDefault + ".Photo,"); strSQL.Append(tableDefault + ".Name,"); strSQL.Append(tableDefault + ".Detail,"); strSQL.Append(tableDefault + ".Sort,"); strSQL.Append(tableDefault + ".Active "); strSQL.Append("FROM "); strSQL.Append(tableDefault + " "); strSQL.Append("WHERE "); strSQL.Append(tableDefault + ".UID=" + parameterChar + "ID"); #endregion dt = clsSQL.Bind(strSQL.ToString(), new string[, ] { { parameterChar + "ID", id } }, dbType, cs); if (dt != null && dt.Rows.Count > 0) { #region Data Builder if (dt.Rows[0]["PhotoPreview"] != DBNull.Value) { lblPhoto.Text = "<img src='" + dt.Rows[0]["PhotoPreview"].ToString() + "'/><br/>"; } txtName.Text = dt.Rows[0]["Name"].ToString(); txtDetail.Text = dt.Rows[0]["Detail"].ToString(); txtSort.Text = dt.Rows[0]["Sort"].ToString(); cbActive.Checked = (dt.Rows[0]["Active"].ToString() == "1" ? true : false); #endregion } else { ucColorBox1.Redirect("/", "ไม่พบหน้าเว็บที่คุณต้องการ"); } }
private void ControlBuilder(string id) { #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("UID,Name "); strSQL.Append("FROM "); strSQL.Append("Jobs "); strSQL.Append("WHERE "); strSQL.Append("StatusFlag='A' "); strSQL.Append("ORDER BY "); strSQL.Append("Sort,Name;"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { ddlJobs.DataSource = dt; ddlJobs.DataTextField = "Name"; ddlJobs.DataValueField = "UID"; ddlJobs.DataBind(); ddlJobs.SelectedValue = id; if (ddlJobs.SelectedItem.Text.ToLower().Contains("other")) { pnJobsName.Visible = true; txtJobsName.Text = ""; } else { pnJobsName.Visible = false; } } else { ddlJobs.Items.Add(new ListItem("- ไม่พบตำแหน่งว่าง -", "null")); } #endregion }
public static DataTable getPatientChecklistMain(string RowID) { #region Variable var result = new DataTable(); var strSQL = new StringBuilder(); var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("RowID,PatientUID,WFID,ProStatus,ProStatusRemark,RegDate,ModifyDate "); strSQL.Append("FROM "); strSQL.Append("tblCheckList "); strSQL.Append("WHERE "); strSQL.Append("RowID="+RowID.ToString()+";"); #endregion result = clsSQL.Bind(strSQL.ToString()); #endregion return result; }
protected void BindDetail(string id) { #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQL Query strSQL.Append("SELECT "); strSQL.Append("A.Name,A.NameEN,"); strSQL.Append("A.Detail,A.DetailEN,"); strSQL.Append("A.Content,A.ContentEN,"); strSQL.Append("A.Sort,"); strSQL.Append("A.StatusFlag "); strSQL.Append("FROM "); strSQL.Append(tableDefault + " A "); strSQL.Append("WHERE "); strSQL.Append("A.UID=" + parameterChar + "ID"); #endregion dt = clsSQL.Bind(strSQL.ToString(), new string[, ] { { parameterChar + "ID", id } }); if (dt != null && dt.Rows.Count > 0) { vdPhoto.Enabled = false; var clsLanguage = new clsLanguage(); txtName.Text = dt.Rows[0]["Name"].ToString(); txtDetail.Text = dt.Rows[0]["Detail"].ToString(); ucContent.Text = dt.Rows[0]["Content"].ToString(); txtNameEN.Text = dt.Rows[0]["NameEN"].ToString(); txtDetailEN.Text = dt.Rows[0]["DetailEN"].ToString(); ucContentEN.Text = dt.Rows[0]["ContentEN"].ToString(); txtSort.Text = dt.Rows[0]["Sort"].ToString(); cbActive.Checked = (dt.Rows[0]["StatusFlag"].ToString() == "A" ? true : false); } else { ucColorBox1.Redirect("/Management/" + webDefault, "ไม่พบหน้าเว็บที่คุณต้องการ"); } #endregion }
protected void BindDetail(string id) { StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #region SQL Query strSQL.Append("SELECT "); strSQL.Append(tableDefault + ".MedicalCenterUID,MedicalCenter.Name MedicalCenterName,"); strSQL.Append(tableDefault + ".Message,"); strSQL.Append(tableDefault + ".Name,"); strSQL.Append(tableDefault + ".Phone,"); strSQL.Append(tableDefault + ".EMail,"); strSQL.Append(tableDefault + ".Status,"); strSQL.Append(tableDefault + ".Sort,"); strSQL.Append(tableDefault + ".Active "); strSQL.Append("FROM "); strSQL.Append(tableDefault + " "); strSQL.Append("LEFT JOIN MedicalCenter ON MedicalCenter.UID=MedicalCenterUID "); strSQL.Append("WHERE "); strSQL.Append(tableDefault + ".UID=" + parameterChar + "ID"); #endregion dt = clsSQL.Bind(strSQL.ToString(), new string[, ] { { parameterChar + "ID", id } }, dbType, cs); if (dt != null && dt.Rows.Count > 0) { lblMedicalCenter.Text = dt.Rows[0]["MedicalCenterName"].ToString(); lblName.Text = dt.Rows[0]["Name"].ToString(); lblMessage.Text = dt.Rows[0]["Message"].ToString(); lblPhone.Text = dt.Rows[0]["Phone"].ToString(); lblEmail.Text = dt.Rows[0]["Email"].ToString(); rbStatus.SelectedValue = dt.Rows[0]["Status"].ToString(); } else { ucColorBox1.Redirect("/Management/" + webDefault, "ไม่พบหน้าเว็บที่คุณต้องการ"); } }
private void WebboardTypeBuilder() { #region Variable StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); #endregion #region Data Builder #region SQL Query strSQL.Append("SELECT "); strSQL.Append("UID,"); strSQL.Append("Name,"); strSQL.Append("Detail,"); strSQL.Append("Active "); strSQL.Append("FROM "); strSQL.Append(tableDefault + " "); if (clsSecurity.LoginGroup != "Admin") { strSQL.Append("WHERE "); strSQL.Append("Active='1' "); } strSQL.Append("ORDER BY "); strSQL.Append("Sort,Name"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { gvDefault.Visible = true; gvDefault.DataSource = dt; gvDefault.DataBind(); lblDefault.Text = ""; } else { gvDefault.Visible = false; lblDefault.Text = clsDefault.AlertMessageColor( "ไม่พบข้อมูลที่ต้องการ", clsDefault.AlertType.Info); } #endregion }
private void setProduct() { #region Variable var strSQL = new StringBuilder(); var dt = new DataTable(); var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("UID,Icon,Name,Detail,NameEN,DetailEN "); strSQL.Append("FROM "); strSQL.Append("P5_ProductGroup "); strSQL.Append("WHERE "); strSQL.Append("StatusFlag='A' "); strSQL.Append("ORDER BY "); strSQL.Append("Sort;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { lblProduct.Text += "<li style='font-family:thaisans_neuebold;color:#2E3842;'>"; lblProduct.Text += "<a href='/Product/" + dt.Rows[i]["UID"].ToString() + "/" + dt.Rows[i]["Name"].ToString().Trim().Replace(" ", "-").Replace("/", "-").Replace(@"\", "-") + "/' class='cbDefault'>"; lblProduct.Text += "<img src='" + dt.Rows[i]["Icon"].ToString() + "' style='width:150px;' alt='" + dt.Rows[i]["Name"].ToString() + "'/>"; if (clsLanguage.LanguageCurrent == "th-TH") { lblProduct.Text += "<p>" + dt.Rows[i]["Name"].ToString() + "</p></a>"; } else { lblProduct.Text += "<p>" + dt.Rows[i]["NameEN"].ToString() + "</p></a>"; } lblProduct.Text += "</li>"; } } #endregion }
public DataTable getCompany(DateTime DOEFrom,DateTime DOETo) { 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.dbTypeMain, clsGlobal.csMain); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("DISTINCT P.Payor Company "); strSQL.Append("FROM "); strSQL.Append("Patient P "); strSQL.Append("WHERE "); strSQL.Append("(P.DOE BETWEEN '" + DOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + DOETo.ToString("yyyy-MM-dd HH:mm") + "') "); strSQL.Append("ORDER BY P.Payor;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); #endregion return dt; }
private void SliderBuilder() { #region Variable clsDefault clsDefault = new clsDefault(); clsSQL clsSQL = new clsSQL(); clsLanguage clsLanguage = new clsLanguage(); StringBuilder strSQL = new StringBuilder(); StringBuilder strSliderItem = new StringBuilder(); StringBuilder strOutput = new StringBuilder(); StringBuilder strScript = new StringBuilder(); DataTable dt = new DataTable(); bool foundChecker = false; string languageDefault = ""; #endregion #region SQL Query strSQL.Append("SELECT "); strSQL.Append("Language.Name LanguageName,"); strSQL.Append("Slider.Photo,"); strSQL.Append("Slider.Name "); strSQL.Append("FROM "); strSQL.Append("Slider "); strSQL.Append("INNER JOIN Language ON "); strSQL.Append("Slider.LanguageUID=Language.UID "); strSQL.Append("AND Language.Active='1' "); strSQL.Append("WHERE "); strSQL.Append("Slider.Active='1' "); strSQL.Append("ORDER BY "); strSQL.Append("Language.Sort ASC,Slider.Sort ASC"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { #region Find Language for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0) languageDefault = dt.Rows[i]["LanguageName"].ToString(); if (dt.Rows[i]["LanguageName"].ToString() == clsLanguage.LanguageCurrent) { foundChecker = true; strSliderItem.Append("<li>"); strSliderItem.Append("<img src='" + dt.Rows[i]["Photo"].ToString() + "' "); strSliderItem.Append("alt='" + (dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : "") + "' "); strSliderItem.Append("title='" + (dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : "") + "'/>"); strSliderItem.Append("</li>"); } } #endregion #region Default Language if (!foundChecker) { for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["LanguageName"].ToString() == languageDefault) { strSliderItem.Append("<li>"); strSliderItem.Append("<img src='" + dt.Rows[i]["Photo"].ToString() + "' "); strSliderItem.Append("alt='" + (dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : "") + "' "); strSliderItem.Append("title='" + (dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : "") + "'/>"); strSliderItem.Append("</li>"); } } } #endregion #region div Builder strOutput.Append("<div id='Slider' style='display:block;width:" + _width + ";height:" + _height + ";overflow:hidden;'>"); strOutput.Append("<ul class='bjqs'>"); strOutput.Append(strSliderItem.ToString()); strOutput.Append("</ul>"); strOutput.Append("</div>"); #endregion } else { strOutput.Append(""); } lblSlider.Text = strOutput.ToString(); }
private bool setSessionLog(List<clsRDPModel> clsRDPModels) { #region Variable var result = false; var strSQL = ""; var dt = new DataTable(); var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); #endregion #region Procedure try { dt = clsSQL.Bind("SELECT UID,Domain,Username,SessionID,ConnectWhen,DisconnectWhen,Status FROM sessionlog WHERE ServerName='" + clsGlobal.IPAddress() + "' AND DisconnectWhen IS NULL AND StatusFlag='A';"); if (dt != null && dt.Rows.Count > 0) { #region Logout for (int i = 0; i < dt.Rows.Count; i++) { var exist = false; try { for (int j = 0; j < clsRDPModels.Count; j++) { try { if (dt.Rows[i]["Domain"].ToString() == clsRDPModels[j].Domain && dt.Rows[i]["Username"].ToString() == clsRDPModels[j].Username && dt.Rows[i]["SessionID"].ToString() == clsRDPModels[j].SessionID.ToString()) { exist = true; break; } } catch (Exception exSelect) { MessageBox.Show("Fail on condition." + Environment.NewLine + exSelect.Message, "setSessionLog", MessageBoxButtons.OK, MessageBoxIcon.Error); tmDefault.Stop(); tmDefault.Enabled = false; return false; } } if (!exist) { var sql = "UPDATE sessionlog SET DisconnectWhen=NOW(),Status='Logoff' WHERE UID=" + dt.Rows[i]["UID"].ToString(); if (!clsSQL.Execute(sql)) { MessageBox.Show("Can't update sessionlog." + Environment.NewLine, "setSessionLog", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } catch (Exception exSelect) { MessageBox.Show("Fail on update(139)." + Environment.NewLine + exSelect.Message, "setSessionLog", MessageBoxButtons.OK, MessageBoxIcon.Error); tmDefault.Stop(); tmDefault.Enabled = false; return false; } } #endregion } #region Connected for (int i = 0; i < clsRDPModels.Count; i++) { try { strSQL = "SELECT UID,IFNULL(DisconnectWhen,'')DisconnectWhen,Status " + "FROM sessionlog " + "WHERE ServerName='" + clsGlobal.IPAddress() + "' " + "AND Domain='" + clsRDPModels[i].Domain + "' " + "AND Username='******' " + "AND SessionID='" + clsRDPModels[i].SessionID + "' " + "AND ConnectWhen='" + clsRDPModels[i].ConnectTime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' " + "AND StatusFlag='A';"; } catch(Exception exSelect) { MessageBox.Show("Fail on select." + Environment.NewLine + exSelect.Message, "setSessionLog", MessageBoxButtons.OK, MessageBoxIcon.Error); tmDefault.Stop(); tmDefault.Enabled = false; return false; } var dtUpdate = new DataTable(); var sql = ""; var addSeparate = false; dtUpdate = clsSQL.Bind(strSQL); if (dtUpdate != null && dtUpdate.Rows.Count > 0) { try { sql = "UPDATE sessionlog SET "; if (dtUpdate.Rows[0]["Status"].ToString() != clsRDPModels[i].Status) { sql += "Status='" + clsRDPModels[i].Status + "' "; addSeparate = true; } if (dtUpdate.Rows[0]["DisconnectWhen"].ToString() != (clsRDPModels[i].DisconnectTime != null ? clsRDPModels[i].DisconnectTime.Value.ToString("yyyy-MM-dd HH:mm:ss") : "")) { if (addSeparate) sql += ","; sql += "DisconnectWhen=" + (clsRDPModels[i].DisconnectTime != null ? "'"+clsRDPModels[i].DisconnectTime.Value.ToString("yyyy-MM-dd HH:mm:ss")+"'" : "NULL") + " "; addSeparate = true; } sql += "WHERE UID=" + dtUpdate.Rows[0]["UID"].ToString(); if (!addSeparate) sql = ""; } catch (Exception exSelect) { MessageBox.Show("Fail on update(188)." + Environment.NewLine + exSelect.Message, "setSessionLog", MessageBoxButtons.OK, MessageBoxIcon.Error); tmDefault.Stop(); tmDefault.Enabled = false; return false; } } else { try { sql = "INSERT INTO sessionlog(ServerName,Domain,Username,IPAddress,SessionID,Status,ConnectWhen,DisconnectWhen,StatusFlag) " + "VALUES('" + clsGlobal.IPAddress() + "'," + "'" + clsRDPModels[i].Domain + "'," + "'" + clsRDPModels[i].Username + "'," + "'" + clsRDPModels[i].IPAddress + "'," + "" + clsRDPModels[i].SessionID.ToString() + "," + "'" + clsRDPModels[i].Status + "'," + "'" + clsRDPModels[i].ConnectTime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'," + "" + (clsRDPModels[i].DisconnectTime != null ? "'" + clsRDPModels[i].DisconnectTime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'" : "NULL") + "," + "'A');"; } catch (Exception exSelect) { MessageBox.Show("Fail on insert." + Environment.NewLine + exSelect.Message, "setSessionLog", MessageBoxButtons.OK, MessageBoxIcon.Error); tmDefault.Stop(); tmDefault.Enabled = false; return false; } } if (sql != "") { if (!clsSQL.Execute(sql)) { MessageBox.Show("Can't update sessionlog." + Environment.NewLine + sql, "setSessionLog", MessageBoxButtons.OK, MessageBoxIcon.Error); tmDefault.Stop(); tmDefault.Enabled = false; return false; } } //if (clsRDPModels[i].DisconnectTime== null) //{ // var getExistCheckerResult = ""; // getExistCheckerResult = getExistChecker(dt, clsRDPModels[i]); // if (getExistCheckerResult != "") // { // if (!clsSQL.Execute(getExistCheckerResult)) // { // MessageBox.Show("Can't update sessionlog." + Environment.NewLine + getExistCheckerResult, "setSessionLog", MessageBoxButtons.OK, MessageBoxIcon.Error); // } // } //} } #endregion } catch(Exception ex) { MessageBox.Show("Fail something on setSessionlog." + Environment.NewLine + ex.Message, "setSessionLog", MessageBoxButtons.OK, MessageBoxIcon.Error); tmDefault.Stop(); tmDefault.Enabled = false; return false; } #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; }
private void getServerList() { #region Variable var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); var dt = new DataTable(); var strSQL = new StringBuilder(); List<string> imageDetails = new List<string>(); #endregion #region Procedure lvServerList.Items.Clear(); #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("A.Name,A.IPAddress,(SELECT COUNT(UID) FROM sessionlog WHERE (ServerName = A.IPAddress OR ServerName = A.Name) AND StatusFlag = 'A' AND DisconnectWhen IS NULL) ActiveCount "); strSQL.Append("FROM "); strSQL.Append("serverlist A "); strSQL.Append("WHERE "); strSQL.Append("A.StatusFlag = 'A' "); strSQL.Append("ORDER BY "); strSQL.Append("A.Sort;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); if(dt!=null && dt.Rows.Count > 0) { var imlDefault = new ImageList(); var imageName = ""; var imagePath = AppDomain.CurrentDomain.BaseDirectory + @"Resource\"; imlDefault.ImageSize = new Size(64, 64); for(int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["ActiveCount"].ToString() == "0") { imageName = "icServerFree.png"; imageDetails.Add(dt.Rows[i]["Name"].ToString()+Environment.NewLine+"ว่าง"); } else { if(int.Parse(dt.Rows[i]["ActiveCount"].ToString()) >= maxActiveNumber) { imageName = "icServerFull.png"; imageDetails.Add(dt.Rows[i]["Name"].ToString() + Environment.NewLine + "เต็ม"); } else { imageName = "icServerBusy.png"; imageDetails.Add(dt.Rows[i]["Name"].ToString() + Environment.NewLine + "ว่างบางส่วน"); } } imlDefault.Images.Add( Image.FromFile(imagePath + imageName) ); } lvServerList.LargeImageList = imlDefault; for(int i = 0; i < imageDetails.Count; i++) { lvServerList.Items.Add(imageDetails[i], i); } } #endregion }
/// <summary> /// ดึงค่า Session จาก Index ที่กำหนด /// </summary> /// <param name="SessionName">ชื่อ Session ที่ใช้เก็บการล็อคอิน</param> /// <param name="ArrayIndex">Index ของตัวแปรที่ต้องการ</param> /// <returns></returns> private string GetLoginSession(string SessionName, int ArrayIndex) { clsDefault clsDefault = new clsDefault(); clsSQL clsSQL = new clsSQL(); StringBuilder strSQL = new StringBuilder(); string rtnValue = ""; if (System.Web.HttpContext.Current.Session[SessionName] != null) { #region Find Session Login Value string[] arrLogin = System.Web.HttpContext.Current.Session[_sessionName].ToString().Split(_sessionSeparate, StringSplitOptions.None); if (ArrayIndex < arrLogin.Count()) { rtnValue = arrLogin[ArrayIndex]; } #endregion } else { #region No Session string strCookie; DataTable dt = new DataTable(); if (clsDefault.CookieChecker(_sessionName, out strCookie)) { strCookie = Decrypt(strCookie); #region SQL Query strSQL.Append("SELECT "); strSQL.Append("[User].UID,"); strSQL.Append("[User].Username,"); strSQL.Append("UserGroup.Name AS UserGroupName,"); strSQL.Append("ISNULL(UserGroup.Authority,'') AS GroupAuthority,"); strSQL.Append("ISNULL([User].Authority,'') AS UserAuthority "); strSQL.Append("FROM "); strSQL.Append("[User] "); strSQL.Append("INNER JOIN UserGroup "); strSQL.Append("ON [User].UserGroupUID=UserGroup.UID AND UserGroup.Active='1' "); strSQL.Append("WHERE "); strSQL.Append("[User].UID=" + _parameterChar + "UID "); strSQL.Append("AND [User].Active='1'"); #endregion dt = clsSQL.Bind( strSQL.ToString(), new string[,] { { "" + _parameterChar + "UID", strCookie } }, _dbType, _cs ); if (dt != null && dt.Rows.Count > 0) { SetLoginSession( _sessionName, new string[] { strCookie, dt.Rows[0]["Username"].ToString(), dt.Rows[0]["UserGroupName"].ToString(), dt.Rows[0]["GroupAuthority"].ToString(), dt.Rows[0]["UserAuthority"].ToString() } ); if (System.Web.HttpContext.Current.Session[_sessionName] != null) { #region Find Session Login Value string[] arrLogin = System.Web.HttpContext.Current.Session[_sessionName].ToString().Split(_sessionSeparate, StringSplitOptions.None); if (ArrayIndex < arrLogin.Count()) { rtnValue = arrLogin[ArrayIndex]; } #endregion } } else { LoginDelete(); } } #endregion } return rtnValue; }
/// <summary> /// ส่งเมล์จาก EmailTemplate โดยระบุค่าตัวแปรได้ /// </summary> /// <param name="TemplateName">ชื่อ Template</param> /// <param name="From">เมล์ต้นทาง</param> /// <param name="To">เมล์ปลายทาง</param> /// <param name="Parameter">ค่าที่ใช้แทนในเมล์ เช่น [Username],nithi.re</param> /// <param name="outMessage">ข้อความแจ้งเตือนเมื่อเกิดข้อผิดพลาด</param> /// <param name="FromAliasName">ชื่อเมล์ต้นทาง</param> /// <param name="Cc">เมล์แนบ</param> /// <param name="Bcc">เมล์ไม่เปิดเผย</param> /// <param name="Signature">ข้อความท้ายเมล์</param> /// <returns>true=ส่งผ่าน , false=ส่งไม่ผ่าน</returns> /// <example> /// clsMail clsMail=new clsMail(); /// string outMessage; /// clsMail.SendTemplate( /// "UserRegisterConfirm", /// "*****@*****.**", /// "*****@*****.**", /// new string[,] { { "[Username]", "ยูสเซอร์เนม" }, { "[UIDEncrypt]", "ยูไอดีเอ็นคริบ" } }, /// out outMessage); /// </example> public bool SendTemplate(string TemplateName, string From, string To, string[,] Parameter, out string outMessage, string FromAliasName = "", string Cc = "", string Bcc = "", string Signature = "") { #region Variable bool rtnValue = false; outMessage = ""; StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); clsSQL clsSQL = new clsSQL(); clsLanguage clsLanguage = new clsLanguage(); clsData clsData=new clsData(); clsDefault clsDefault = new clsDefault(); #endregion #region Found Language #region SQL Query strSQL.Append("SELECT "); strSQL.Append("EmailTemplate.UID,Language.Name LanguageName,EmailTemplate.Subject,EmailTemplate.Message "); strSQL.Append("FROM "); strSQL.Append("EmailTemplate "); strSQL.Append("INNER JOIN Language ON EmailTemplate.LanguageUID=Language.UID "); strSQL.Append("AND Language.Active='1' "); //strSQL.Append("AND Language.Name='" + clsLanguage.LanguageCurrent + "' "); strSQL.Append("WHERE "); strSQL.Append("EmailTemplate.Active='1' "); strSQL.Append("AND EmailTemplate.Name='" + TemplateName + "' "); strSQL.Append("ORDER BY "); strSQL.Append("Language.Sort ASC"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); strSQL.Length = 0; strSQL.Capacity = 0; #endregion if (dt != null && dt.Rows.Count > 0) { #region Find LanguageRow bool languageMatch = false; int i; for (i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["LanguageName"].ToString() == clsLanguage.LanguageCurrent) { languageMatch = true; break; } } if (!languageMatch) i = 0; #endregion #region Parameter Replace string subject = dt.Rows[i]["Subject"].ToString(); string message = dt.Rows[i]["Message"].ToString(); subject = clsData.Replacer(subject, Parameter); message = clsData.Replacer(message, Parameter); if (Send(From, To, subject, message, out outMessage, FromAliasName, Cc, Bcc, Signature)) { SendTemplateLog(dt.Rows[i]["UID"].ToString(), From, To, Cc, Bcc, "Complete"); rtnValue = true; } else { SendTemplateLog(dt.Rows[i]["UID"].ToString(), From, To, Cc, Bcc, clsDefault.Left(outMessage,90)); rtnValue = false; } #endregion } else { outMessage = "ไม่พบข้อมูล EmailTemplate"; return false; } return rtnValue; }
public DataTable getPatient(DateTime DOEFrom,DateTime DOETo,string CompanyName) { 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.dbTypeMain, clsGlobal.csMain); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("P.No,"); strSQL.Append("P.rowguid PatientGUID,"); strSQL.Append("P.HN,"); strSQL.Append("P.Episode,"); strSQL.Append("P.Prename,"); strSQL.Append("P.Name Forename,"); strSQL.Append("P.LastName Surname,"); strSQL.Append("P.LabEpisode,"); strSQL.Append("P.DOB,"); strSQL.Append("P.EMPID EmployeeID,"); strSQL.Append("P.DOE,"); strSQL.Append("P.Payor Company,"); strSQL.Append("PL.ChildCompany,"); strSQL.Append("P.ProChkList,"); strSQL.Append("(SELECT TOP 1 CheckListDesc FROM Checklist WHERE CheckList = P.ProChkList ORDER BY ID DESC)ProChkListDetail,"); strSQL.Append("P.Age,"); strSQL.Append("P.Sex,"); strSQL.Append("P.Address,"); strSQL.Append("P.Tel,"); strSQL.Append("P.Email,"); strSQL.Append("P.Physician,P.RegType,P.Programid,P.DIV DIVI, P.DEP,P.SEC,P.POS,P.LAN,P.NAT,P.CNT_TRY,P.LOC,"); strSQL.Append("P.Payor,P.Epi_Rowid,P.ORD_STS,P.STS,P.DR_CDE,P.NTE,P.Job,P.BusUnit,P.BusDiv,P.Line,P.Shift,P.Location,"); strSQL.Append("P.GrpBook,P.HISExist,"); strSQL.Append("P.SyncStatus,P.SyncWhen,PL.BookCreate,"); strSQL.Append("(SELECT COUNT(RowID) FROM tblCheckList WITH(NOLOCK) WHERE PatientUID = P.rowguid) ChecklistCount "); strSQL.Append("FROM "); strSQL.Append("Patient P "); strSQL.Append("LEFT JOIN tblPatientList PL ON P.rowguid = PL.PatientUID "); strSQL.Append("WHERE "); strSQL.Append("(P.DOE BETWEEN '"+DOEFrom.ToString("yyyy-MM-dd HH:mm")+"' AND '"+DOETo.ToString("yyyy-MM-dd HH:mm")+"') "); strSQL.Append("AND Payor = '"+CompanyName+"' "); strSQL.Append("ORDER BY P.NO;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); #endregion return dt; }
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; }
public DataTable getLabDetailByBook(DateTime DOEFrom, DateTime DOETo, string BookCreate) { 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,P.No OrderNo, P.HN,P.EmployeeID,P.LabEpisode,CONCAT(P.Forename, ' ', P.Surname) Name,P.POS Position, P.DEP Department, P.DIVI Division, P.SEC Section, P.Line,P.Shift,P.Location Site,P.Payor,IFNULL(P.BookCreate,'') BookCreate,"); strSQL.Append("(SELECT CWhen FROM patientlab WHERE LabEpisode = P.LabEpisode AND WFID = 6 LIMIT 0, 1) AcceptDateBlood,"); strSQL.Append("(SELECT CWhen FROM patientlab WHERE LabEpisode = P.LabEpisode AND WFID = 7 LIMIT 0,1) AcceptDateUrine,"); strSQL.Append("(SELECT CWhen FROM patientlab WHERE LabEpisode = P.LabEpisode AND WFID = 8 LIMIT 0,1) AcceptDateStool,"); strSQL.Append("(SELECT CWhen FROM patientlab WHERE LabEpisode = P.LabEpisode AND WFID = 9 LIMIT 0,1) AcceptDateHeavyMetal,"); strSQL.Append("(SUM(WFID = 6 OR WFID = 7 OR WFID = 8 OR WFID = 9) - (SELECT COUNT(LabEpisode) FROM patientlab WHERE LabEpisode = P.LabEpisode AND WFID IN(6, 7, 8, 9))) CountLabPending,"); strSQL.Append("(SELECT MWhen FROM patientchecklist WHERE PatientGUID = P.PatientGUID AND WFID = 1 LIMIT 0,1) RegisterDate,"); strSQL.Append("SUM(WFID = 6) CountChecklistBlood,"); strSQL.Append("SUM(WFID = 7) CountChecklistUrine,"); strSQL.Append("SUM(WFID = 8) CountChecklistStool,"); strSQL.Append("SUM(WFID = 9) CountChecklistHeavyMetal "); strSQL.Append("FROM "); strSQL.Append("Patient P "); strSQL.Append("INNER JOIN patientchecklist PL ON P.PatientGUID = PL.PatientGUID "); 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 != "" && BookCreate != "- ทั้งหมด -") { strSQL.Append("AND BookCreate = '" + BookCreate + "' "); } strSQL.Append("GROUP BY P.PatientGUID ORDER BY P.BookCreate,P.No;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); if (dt != null && dt.Rows.Count > 0) { dt.Columns.Remove("PatientGUID"); dt.AcceptChanges(); } #endregion return dt; }
public bool setChecklistToMobile(DataTable dt) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); #region Variable var result = true; var clsSQL = new clsSQL(clsGlobal.dbTypeMain, clsGlobal.csMain); var clsSQLMobile = new clsSQL(clsGlobal.dbType, clsGlobal.cs); var dv = dt.DefaultView; var dtProChkList = new DataTable(); var strSQL = ""; #endregion #region Procedure dtProChkList = dv.ToTable(true, "ProChkList"); if(dtProChkList!=null && dtProChkList.Rows.Count > 0) { for(int i = 0; i < dtProChkList.Rows.Count; i++) { var checklistID = ""; strSQL = "DELETE FROM Checklist WHERE Code='"+ dtProChkList.Rows[i][0].ToString().Trim() + "';"; clsSQLMobile.Execute(strSQL); #region Checklist strSQL = "SELECT TOP 1 id ChecklistID,Checklist Code,CheckListDesc Detail FROM Checklist WHERE Checklist='" + dtProChkList.Rows[i][0].ToString().Trim() + "';"; var dtChecklist = new DataTable(); dtChecklist = clsSQL.Bind(strSQL); if (dtChecklist != null && dtChecklist.Rows.Count > 0) { checklistID = dtChecklist.Rows[0]["ChecklistID"].ToString(); #region Insert if (!clsSQLMobile.Insert( "checklist", new string[,] { {"ChecklistID", dtChecklist.Rows[0]["ChecklistID"].ToString() }, {"Code", "'"+dtChecklist.Rows[0]["Code"].ToString().Trim().SQLQueryFilter()+"'" }, {"Detail", "'"+dtChecklist.Rows[0]["Detail"].ToString().SQLQueryFilter()+"'" } }, new string[,] { { } }, out strSQL)) { //If error, what you want to do? result = false; MessageBox.Show("Checklist Insert : " + strSQL, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } #endregion } #endregion #region ChecklistDetail if (checklistID != "") { strSQL = "SELECT TOP 100 rowid ProID,WFID,WFSequen,WorkFlow FROM Process WHERE CheckListid="+ checklistID + ";"; var dtChecklistDetail = new DataTable(); dtChecklistDetail = clsSQL.Bind(strSQL); if (dtChecklistDetail != null && dtChecklistDetail.Rows.Count > 0) { for(int j = 0; j < dtChecklistDetail.Rows.Count; j++) { #region Insert if (!clsSQLMobile.Insert( "checklistdetail", new string[,] { {"ChecklistID", checklistID }, {"ProID", dtChecklistDetail.Rows[j]["ProID"].ToString().Trim() }, {"WFID", dtChecklistDetail.Rows[j]["WFID"].ToString() }, {"WFSequen", dtChecklistDetail.Rows[j]["WFSequen"].ToString() }, {"WorkFlow", "'"+dtChecklistDetail.Rows[j]["WorkFlow"].ToString().SQLQueryFilter()+"'" } }, new string[,] { { } }, out strSQL)) { result = false; //If error, what you want to do? MessageBox.Show("ChecklistDetail Insert : " + strSQL, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } #endregion } } } #endregion } } #endregion return result; }
/// <summary> /// ใช้ตรวจสอบสถานะสมาชิก จาก Session และ Cookie /// </summary> /// <param name="GroupName">ชื่อสถานะที่ต้องการตรวจสอบ</param> /// <param name="CreateSession">กรณีพบ Cookie ให้สร้าง Session ด้วยเลยไหม</param> /// <returns>true = พบข้อมูลการล็อคอิน , false = ไม่พบข้อมูลการล็อคอิน</returns> /// <example> /// clsSecurity.LoginChecker("admin"); /// clsSecurity.LoginChecker(); /// </example> public bool LoginChecker(string GroupName = "", bool CreateSession = true) { bool rtnValue = false; clsDefault clsDefault = new clsDefault(); clsSQL clsSQL = new clsSQL(); StringBuilder strSQL = new StringBuilder(); #region Session if (HttpContext.Current.Session[_sessionName] != null) { if (!string.IsNullOrEmpty(GroupName)) { if (GetLoginSession(_sessionName,_sessionGroup).ToLower() == GroupName.ToLower()) { rtnValue = true; } else { rtnValue = false; } } else { rtnValue = true; } } #endregion #region No Session Check Cookie else { string strCookie; DataTable dt = new DataTable(); if (clsDefault.CookieChecker(_sessionName, out strCookie)) { strCookie = Decrypt(strCookie); #region SQL Query strSQL.Append("SELECT "); strSQL.Append("[User].UID,"); strSQL.Append("[User].Username,"); strSQL.Append("UserGroup.Name AS UserGroupName,"); strSQL.Append("ISNULL(UserGroup.Authority,'') AS GroupAuthority,"); strSQL.Append("ISNULL([User].Authority,'') AS UserAuthority "); strSQL.Append("FROM "); strSQL.Append("[User] "); strSQL.Append("INNER JOIN UserGroup "); strSQL.Append("ON [User].UserGroupUID=UserGroup.UID AND UserGroup.Active='1' "); strSQL.Append("WHERE "); strSQL.Append("[User].UID=" + _parameterChar + "UID "); strSQL.Append("AND [User].Active='1'"); #endregion dt = clsSQL.Bind( strSQL.ToString(), new string[,] { { "" + _parameterChar + "UID", strCookie } }, _dbType, _cs ); if (dt != null && dt.Rows.Count > 0) { if (!string.IsNullOrEmpty(GroupName)) { if (dt.Rows[0]["UserGroupName"].ToString().ToLower() == GroupName.ToLower()) { if (CreateSession) { SetLoginSession( _sessionName, new string[] { strCookie, dt.Rows[0]["Username"].ToString(), dt.Rows[0]["UserGroupName"].ToString(), dt.Rows[0]["GroupAuthority"].ToString(), dt.Rows[0]["UserAuthority"].ToString() } ); } rtnValue = true; } } else { if (CreateSession) { SetLoginSession( _sessionName, new string[] { strCookie, dt.Rows[0]["Username"].ToString(), dt.Rows[0]["UserGroupName"].ToString(), dt.Rows[0]["GroupAuthority"].ToString(), dt.Rows[0]["UserAuthority"].ToString() } ); } rtnValue = true; } } else { LoginDelete(); } } } #endregion return rtnValue; }
public DataTable getPatientChecklistGroupByRegisterDate(DateTime dtDOEFrom, DateTime dtDOETo, string Company) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); #region Variable var result = new DataTable(); var strSQL = new StringBuilder(); var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("DATE(RegDate)RegisterDate "); strSQL.Append("FROM patientchecklist PL "); strSQL.Append("INNER JOIN patient P ON PL.PatientGUID = P.PatientGUID "); strSQL.Append("WHERE PL.WFID = 1 AND NOT PL.RegDate IS NULL "); strSQL.Append("AND(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("GROUP BY DATE(RegDate) "); strSQL.Append("ORDER BY DATE(RegDate);"); #endregion result = clsSQL.Bind(strSQL.ToString()); #endregion return result; }
/// <summary> /// ใช้ตรวจสอบ Username Password และสร้าง Session Cookie /// </summary> /// <param name="Username">Login Username</param> /// <param name="Password">Login Password</param> /// <param name="CreateCookie">สร้าง Cookie ด้วยไหม</param> /// <returns>ผลการล็อคอิน</returns> /// <example> /// clsSecurity.LoginChecker("offduiclub","off1234",false); /// clsSecurity.LoginChecker("offduiclub","off1234"); /// </example> public bool LoginChecker(string Username, string Password, bool CreateCookie = false) { bool rtnValue = false; DataTable dt = new DataTable(); clsDefault clsDefault = new clsDefault(); clsSQL clsSQL = new clsSQL(); StringBuilder strSQL=new StringBuilder(); #region SQL Query strSQL.Append("SELECT "); strSQL.Append("[User].UID,"); strSQL.Append("[User].Username,"); strSQL.Append("UserGroup.Name AS UserGroupName,"); strSQL.Append("ISNULL(UserGroup.Authority,'') AS GroupAuthority,"); strSQL.Append("ISNULL([User].Authority,'') AS UserAuthority "); strSQL.Append("FROM "); strSQL.Append("[User] "); strSQL.Append("INNER JOIN UserGroup "); strSQL.Append("ON [User].UserGroupUID=UserGroup.UID AND UserGroup.Active='1' "); strSQL.Append("WHERE "); strSQL.Append("[User].Username="******"Username "); strSQL.Append("AND [User].Password="******"Password "); strSQL.Append("AND [User].Active='1'"); #endregion dt = clsSQL.Bind( strSQL.ToString(), new string[,] { { "" + _parameterChar + "Username", Username }, { "" + _parameterChar + "Password", Encrypt(Password) } }, _dbType, _cs ); if (dt != null && dt.Rows.Count > 0) { rtnValue = true; SetLoginSession( _sessionName, new string[] { dt.Rows[0]["UID"].ToString(), dt.Rows[0]["Username"].ToString(), dt.Rows[0]["UserGroupName"].ToString(), dt.Rows[0]["GroupAuthority"].ToString(), dt.Rows[0]["UserAuthority"].ToString() } ); if (CreateCookie) { clsDefault.CookieCreate(_sessionName, Encrypt(dt.Rows[0]["UID"].ToString())); } } return rtnValue; }
/// <summary> /// ดึงข้อมูล PatientChecklist ทั้งหมดในระบบ Mobile ที่มี ProStatus>1 เพื่อนำไปอัพเดทข้อมูลที่ Main Server /// </summary> /// <returns></returns> public DataTable getPatientChecklistMobile() { #region Variable //var hourAddSync = System.Configuration.ConfigurationManager.AppSettings["hourAddSync"]; var hourAddSync = clsGlobal.AddHours; var result = new DataTable(); var strSQL = new StringBuilder(); var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("RowID,PatientGUID,HN,WorkFlow,WFID,ProStatus,IFNULL(ProStatusRemark,'')ProStatusRemark,RegDate,ModifyDate,MWhen,MUser "); strSQL.Append("FROM "); strSQL.Append("patientchecklist "); strSQL.Append("WHERE "); strSQL.Append("ProStatus>1 "); strSQL.Append("AND MWhen>=DATE_ADD(NOW(),INTERVAL "+ hourAddSync + " HOUR);"); #endregion result = clsSQL.Bind(strSQL.ToString()); #endregion return result; }
public DataTable getPatientChecklist(string PatientGUID,string Episode,string HN) { #region Variable var dt = new DataTable(); var strSQL = new StringBuilder(); var clsSQL = new clsSQL(clsGlobal.dbTypeMain, clsGlobal.csMain); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("RowID,"); strSQL.Append("PatientUID PatientGUID,"); strSQL.Append("'"+HN+"' HN,"); strSQL.Append("EN Episode,"); strSQL.Append("CheckListID,"); strSQL.Append("CheckList ProChkList,"); strSQL.Append("ProID,"); //strSQL.Append("WorkFlow,"); strSQL.Append("(SELECT TOP 1 WorkFlow FROM Process WHERE CheckListId=tblCheckList.CheckListId AND WFID=tblCheckList.WFID)WorkFlow,"); strSQL.Append("WFID,"); strSQL.Append("WFSequen,"); //strSQL.Append("ProStatus,"); strSQL.Append("(CASE WHEN SyncWhen IS NULL THEN '1' ELSE ProStatus END) ProStatus,"); strSQL.Append("RegDate,"); strSQL.Append("ModifyDate,"); strSQL.Append("SyncWhen "); strSQL.Append("FROM tblCheckList "); strSQL.Append("WHERE "); strSQL.Append("(EN<>'' AND EN='"+Episode+"') OR (PatientUID<>'' AND PatientUID='"+PatientGUID+"') "); strSQL.Append("ORDER BY ModifyDate DESC, WFSequen ASC;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); #region DuplicateClear if(dt!=null && dt.Rows.Count > 0) { dt = RemoveDuplicateRows(dt, "WFID"); } #endregion #endregion //if(dt!=null && dt.Rows.Count > 0) //{ //} //else //{ // MessageBox.Show("Test"); //} return dt; }
private void MenuBuilder() { #region Variable clsSQL clsSQL = new clsSQL(); clsDefault clsDefault = new clsDefault(); ucMenuMega.Item item = new ucMenuMega.Item(); StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); DataTable dtService = new DataTable(); #endregion item = new ucMenuMega.Item(); item.UID = 1; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "About Hospital"; break; case "km-KH": item.Name = "គេហទំព័រដើម"; break; default: item.Name = "รู้จักเรา"; break; } item.Detail = ""; item.URL = ""; ucMenuMega1.Items.Add(item); #region รู้จักเรา item = new ucMenuMega.Item(); item.UID = 11; item.ParentUID = 1; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Overview"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ទស្សនីយភាពរួមមន្ទីពេទ្យបាងកកចាន់បុរី"+"</span>"; break; default: item.Name = "โรงพยาบาลกรุงเทพจันทบุรี"; break; } item.Detail = ""; item.URL = "/AboutHospital"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 12; item.ParentUID = 1; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Hospital Facilities"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "គ្រឿងបរិក្ខាផេ្សងៗ"+"</span>"; break; default: item.Name = "สิ่งอำนวยความสะดวกในโรงพยาบาล"; break; } item.Detail = ""; item.URL = "/Facilities"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 13; item.ParentUID = 1; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Award & Accreditations"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "រង្វាន់ធានាគុណភាពសេវាកម្ម"+"</span>"; break; default: item.Name = "รางวัลและการประกันคุณภาพบริการ"; break; } item.Detail = ""; item.URL = "/Awards"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 14; item.ParentUID = 1; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Advanced Technologies"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "បច្ទេកវិទ្យាទំនើប"+"</span>"; break; default: item.Name = "เทคโนโลยีเพื่อการรักษาผู้ป่วย"; break; } item.Detail = ""; item.URL = "/AdvancedTechnologies"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 15; item.ParentUID = 1; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Hospital Network"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "បណ្តាញរបស់មន្ទីពេទ្យ"+"</span>"; break; default: item.Name = "กลุ่มโรงพยาบาลเครือข่าย"; break; } item.Detail = ""; item.URL = "/HospitalNetwork"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 16; item.ParentUID = 1; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Vision & Mission"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ទស្សនៈវិស័យរបស់" + "</span>"; break; default: item.Name = "วิสัยทัศน์ และ พันธกิจ"; break; } item.Detail = ""; item.URL = "/VisionMission"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 17; item.ParentUID = 1; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "About Chanthaburi"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "About Chanthaburi" + "</span>"; break; default: item.Name = "ข้อมูลทั่วไปของจังหวัดจันทบุรี"; break; } item.Detail = ""; item.URL = "/AboutChanthaburi"; ucMenuMega1.Items.Add(item); #endregion item = new ucMenuMega.Item(); item.UID = 2; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Healthcare Services"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "គ្លីនិក& មជ្ឈមណ្ឌលព្យាបាលជំងឺផ្សេងៗ"+"</span>"; break; default: item.Name = "บริการทางการแพทย์"; break; } item.Detail = ""; item.MegaData = CenterBuilder(); ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 3; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Patient Services"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "សេវាកម្មអ្នកជម្ងឺ"+"</span>"; break; default: item.Name = "บริการสำหรับผู้ป่วย"; break; } item.Detail = ""; ucMenuMega1.Items.Add(item); #region บริการสำหรับผู้ป่วย item = new ucMenuMega.Item(); item.UID = 31; item.ParentUID = 3; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Room & Facilities"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "បរិក្ខាប្រើប្រាស់ក្នុងបន្ទប់"+"</span>"; break; default: item.Name = "ห้องพักผู้ป่วยและสิ่งอำนวยความสะดวก"; break; } item.Detail = ""; item.URL = ""; ucMenuMega1.Items.Add(item); #region Service #region ServiceGroup #region SQL Query strSQL.Append("SELECT "); strSQL.Append("ServiceGroup.UID,ServiceGroup.Name "); strSQL.Append("FROM "); strSQL.Append("ServiceGroup "); strSQL.Append("INNER JOIN Language "); strSQL.Append("ON ServiceGroup.LanguageUID=Language.UID AND Language.Active='1' "); strSQL.Append("WHERE "); strSQL.Append("ServiceGroup.Active='1' "); strSQL.Append("AND Language.Name='" + ucLanguageDB1.LanguageCurrent + "' "); strSQL.Append("ORDER BY "); strSQL.Append("ServiceGroup.Sort ASC"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); strSQL.Length = 0; strSQL.Capacity = 0; if (dt != null && dt.Rows.Count > 0) { for (int g = 0; g < dt.Rows.Count; g++) { item = new ucMenuMega.Item(); item.UID = int.Parse("31"+(g+1).ToString()); item.ParentUID = 31; item.Name = dt.Rows[g]["Name"].ToString(); item.Detail = ""; item.URL = ""; ucMenuMega1.Items.Add(item); #region Service #region SQL Query strSQL.Append("SELECT "); //strSQL.Append("UID,"); strSQL.Append("Service.DepartmentUID UID,"); strSQL.Append("Service.Name "); strSQL.Append("FROM "); strSQL.Append("Service "); strSQL.Append("INNER JOIN Language "); strSQL.Append("ON Service.LanguageUID=Language.UID AND Language.Active='1' "); strSQL.Append("WHERE "); strSQL.Append("Service.Active='1' "); strSQL.Append("AND Service.ServiceGroupUID='" + dt.Rows[g]["UID"].ToString() + "' "); strSQL.Append("AND Language.Name='" + ucLanguageDB1.LanguageCurrent + "' "); strSQL.Append("ORDER BY "); strSQL.Append("Service.Sort ASC"); #endregion dtService = clsSQL.Bind(strSQL.ToString(), dbType, cs); strSQL.Length = 0; strSQL.Capacity = 0; if (dtService != null && dtService.Rows.Count > 0) { for (int s = 0; s < dtService.Rows.Count; s++) { item = new ucMenuMega.Item(); item.UID = int.Parse("31" + (g + 1).ToString()+(s+1).ToString()); item.ParentUID = int.Parse("31" + (g + 1).ToString()); item.Name = dtService.Rows[s]["Name"].ToString(); item.Detail = ""; item.URL = "/Service/"+dtService.Rows[s]["UID"].ToString()+"/"+clsDefault.URLRoutingFilter(dtService.Rows[s]["Name"].ToString())+"/"; ucMenuMega1.Items.Add(item); } dtService = null; } #endregion } dt = null; } #endregion #endregion item = new ucMenuMega.Item(); item.UID = 33; item.ParentUID = 3; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Chivawattana Membership Card"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ចូលជាសមាជិកកម្មវិធីជីវះវឌ្ឍនះ"+"</span>"; break; default: item.Name = "สมาชิกบัตรชีววัฒนะ"; break; } item.Detail = ""; item.URL = "/Chivawattana/"; ucMenuMega1.Items.Add(item); /* item = new ucMenuMega.Item(); item.UID = 34; item.ParentUID = 3; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Checkup Result"; break; default: item.Name = "ผลตรวจสุขภาพ"; break; } item.Detail = ""; item.URL = "/CheckupCondition/"; ucMenuMega1.Items.Add(item); */ item = new ucMenuMega.Item(); item.UID = 35; item.ParentUID = 3; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Health Packages"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "កញ្ចប់ សុខភាព"+"</span>"; break; default: item.Name = "แพคเกจตรวจสุขภาพ"; break; } item.Detail = ""; item.URL = "/HealthPackage"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 36; item.ParentUID = 3; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Packages"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "កញ្ចប់ សុខភាព"+"</span>"; break; default: item.Name = "แพคเกจโรคทั่วไป"; break; } item.Detail = ""; item.URL = "/Package"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 37; item.ParentUID = 3; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Promotions"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ការផ្ដល់ជូនពិសេស"+"</span>"; break; default: item.Name = "โปรโมชั่น"; break; } item.Detail = ""; item.URL = "/Promotion"; ucMenuMega1.Items.Add(item); #endregion item = new ucMenuMega.Item(); item.UID = 4; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Find a Doctor"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ស្វែងរកគ្រូពេទ្យ & ធ្វើការណាត់"+"</span>"; break; default: item.Name = "ค้นหาและนัดหมายแพทย์"; break; } item.Detail = ""; item.URL = "/DoctorSchedule"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 5; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Hospital News"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ព័ត៍មាន"+"</span>"; break; default: item.Name = "ข่าวสารโรงพยาบาล"; break; } item.Detail = ""; ucMenuMega1.Items.Add(item); #region ข่าวสารโรงพยาบาล item = new ucMenuMega.Item(); item.UID = 51; item.ParentUID = 5; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Events"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ព្រឹត្តការណ៍"+"</span>"; break; default: item.Name = "กิจกรรม"; break; } item.Detail = ""; item.URL = "/Event"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 52; item.ParentUID = 5; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "News"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ព័ត៍មាន"+"</span>"; break; default: item.Name = "ข่าวประชาสัมพันธ์"; break; } item.Detail = ""; item.URL = "/News"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 53; item.ParentUID = 5; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Health Articles"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "សុខភាព មាត្រា"+"</span>"; break; default: item.Name = "บทความสุขภาพ"; break; } item.Detail = ""; item.URL = "/Article"; ucMenuMega1.Items.Add(item); #endregion item = new ucMenuMega.Item(); item.UID = 6; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Contact Us"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ទំនាក់ទំនងមន្ទីពេទ្យបាងកកចាន់បុរី"+"</span>"; break; default: item.Name = "ติดต่อเรา"; break; } item.Detail = ""; ucMenuMega1.Items.Add(item); #region ติดต่อเรา item = new ucMenuMega.Item(); item.UID = 61; item.ParentUID = 6; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Inquiry"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "សាកសួរពត៌មានលំអិត"+"</span>"; break; default: item.Name = "ฝากคำถามถึงโรงพยาบาล"; break; } item.Detail = ""; item.URL = "/Inquiry/"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 62; item.ParentUID = 6; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Feedback"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ផ្តល់មតិរិះគន់ដើម្បីកែប្រែ"+"</span>"; break; default: item.Name = "แนะนำ/ติชม ถึงผู้บริหาร"; break; } item.Detail = ""; item.URL = "/Feedback/"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 63; item.ParentUID = 6; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Webboard"; break; case "km-KH": item.Name = "Webboard"; break; default: item.Name = "เว็บบอร์ดตอบปัญหาสุขภาพ"; break; } item.Detail = ""; item.URL = "/Webboard/"; //ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 64; item.ParentUID = 6; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Maps & Directions"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "ផែនទី"+"</span>"; break; default: item.Name = "แผนที่และการเดินทาง"; break; } item.Detail = ""; item.URL = "/Maps/"; ucMenuMega1.Items.Add(item); item = new ucMenuMega.Item(); item.UID = 65; item.ParentUID = 6; switch (ucLanguageDB1.LanguageCurrent) { case "en-US": item.Name = "Jobs"; break; case "km-KH": item.Name = "<span style='font-size:7.5pt;'>" + "រួមការងារជាមួយយើង"+"</span>"; break; default: item.Name = "ร่วมงานกับเรา"; break; } item.Detail = ""; item.URL = "/Jobs/"; ucMenuMega1.Items.Add(item); #endregion }
public DataTable getPatientChecklistCountByProStatus(DateTime dtDOEFrom, DateTime dtDOETo, string Company,string RegisterDate) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); #region Variable var result = 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.HN,"); strSQL.Append("P.Forename,"); strSQL.Append("P.Surname,"); strSQL.Append("P.PatientGUID,"); strSQL.Append("(SELECT COUNT(RowID) FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND DATE(patientchecklist.RegDate)='"+ RegisterDate + "')CountChecklistAll,"); strSQL.Append("(SELECT COUNT(RowID) FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND DATE(patientchecklist.RegDate)='" + RegisterDate + "' AND ProStatus=3)CountChecklistComplete,"); strSQL.Append("(SELECT COUNT(RowID) FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND DATE(patientchecklist.RegDate)='" + RegisterDate + "' AND ProStatus=2)CountChecklistDocumentPending,"); strSQL.Append("(SELECT COUNT(RowID) FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND DATE(patientchecklist.RegDate)='" + RegisterDate + "' AND ProStatus=4)CountChecklistCancel "); strSQL.Append("FROM 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+"';"); #endregion result = clsSQL.Bind(strSQL.ToString()); #endregion return result; }
/// <summary> /// ดึงรายชื่ออีเมล์ในฐานข้อมูล EmailList โดยคืนค่าเป็น List<EmailList> /// </summary> /// <param name="EmailListName">ชื่ออ้างอิงข้อมูลเมล์ในฐานข้อมูล</param> /// <returns>List ของคลาส EmailList</returns> /// <example> /// clsMail clsMail = new clsMail(); /// List<clsMail.EmailList> emailLists=new List<clsMail.EmailList>(); /// emailLists = clsMail.GetEmailList("DoctorScheduleTo"); /// </example> public List<EmailList> GetEmailList(string EmailListName) { #region Variable clsSQL clsSQL = new clsSQL(); StringBuilder strSQL = new StringBuilder(); DataTable dt = new DataTable(); List<EmailList> emailLists = new List<EmailList>(); #endregion #region DataBuilder #region SQL Query strSQL.Append("SELECT "); strSQL.Append("EMail,"); strSQL.Append("EMailAliasName "); strSQL.Append("FROM "); strSQL.Append("EmailList "); strSQL.Append("WHERE "); strSQL.Append("Active='1' "); strSQL.Append("AND Name="+parameterChar+"Name"); #endregion dt = clsSQL.Bind(strSQL.ToString(),new string[,]{{parameterChar+"Name",EmailListName}}, dbType, cs); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { EmailList emailList = new EmailList(); emailList.EmailAddress = dt.Rows[i]["EMail"].ToString(); emailList.EmailAliasName = (dt.Rows[i]["EMailAliasName"] != DBNull.Value ? dt.Rows[i]["EMailAliasName"].ToString() : ""); emailLists.Add(emailList); } } #endregion return emailLists; }
private void ContentBuilder() { #region Variable StringBuilder strSQL = new StringBuilder(); StringBuilder strScript = new StringBuilder(); DataTable dt = new DataTable(); bool foundChecker = false; int dtIndex=0; clsSQL clsSQL = new clsSQL(); clsLanguage clsLanguage = new clsLanguage(); clsSecurity clsSecurity = new clsSecurity(); #endregion #region SQL Query strSQL.Append("SELECT "); strSQL.Append("Language.UID LanguageUID,"); strSQL.Append("Language.Name LanguageName,"); strSQL.Append("Content.UID,"); strSQL.Append("Content.Name,"); strSQL.Append("Content.Detail,"); strSQL.Append("Content.Content "); strSQL.Append("FROM "); strSQL.Append("Content "); strSQL.Append("INNER JOIN Language ON Content.LanguageUID=Language.UID "); strSQL.Append("AND Language.Active='1' "); strSQL.Append("WHERE "); strSQL.Append("Content.Active='1' "); strSQL.Append("AND Content.Name='"+_contentName.Trim()+"' "); strSQL.Append("ORDER BY "); strSQL.Append("Language.Sort ASC"); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); if (dt != null && dt.Rows.Count > 0) { strScript.Append("<div class='" + (clsSecurity.LoginChecker("Admin") ? "dvContent" : "dvContentNormal") + "'>"); #region Find Language for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["LanguageName"].ToString() == clsLanguage.LanguageCurrent) { foundChecker = true; dtIndex=i; #region Content Builder if (dt.Rows[i]["Content"] != DBNull.Value) { strScript.Append(dt.Rows[i]["Content"].ToString()); } #endregion break; } } #endregion #region Default Builder if (!foundChecker) { if (dt.Rows[0]["Content"] != DBNull.Value) { strScript.Append(dt.Rows[0]["Content"].ToString()); } } #endregion #region Admin Builder if (clsSecurity.LoginChecker("Admin")) { strScript.Append("<div class='dvContentMenu'>"); strScript.Append("<a href='/Management/ContentManage.aspx?id="+dt.Rows[dtIndex]["UID"].ToString()+"&command=edit' "); strScript.Append("title='แก้ไขข้อมูล' "); if (_modalRefreshOnClose) { strScript.Append("class='cbIFrameRefreshOnClose'"); } else { strScript.Append("class='cbIFrame'"); } strScript.Append(">"); strScript.Append("<span class='Icon16 Edit' />"); strScript.Append("</a>"); strScript.Append("</div>"); } #endregion strScript.Append("</div>"); lblContent.Text = strScript.ToString(); } }
public string getPatientNotHadChecklist() { #region Variable var result = new StringBuilder(); var strSQL = new StringBuilder(); var dt = new DataTable(); var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); #endregion #region Procedure #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("P.HN,P.Episode,P.Forename,P.Surname,P.ProChkList,P.Company "); strSQL.Append("FROM patient P "); //strSQL.Append("WHERE(SELECT COUNT(RowID) FROM patientchecklist WHERE PatientGUID = P.PatientGUID) = 0;"); strSQL.Append("WHERE NOT P.PatientGUID IN (SELECT DISTINCT PatientGUID FROM patientchecklist);"); #endregion dt = clsSQL.Bind(strSQL.ToString()); if(dt!=null && dt.Rows.Count > 0) { result.Append(Environment.NewLine + Environment.NewLine); result.Append("## รายชื่อที่ไม่มี Checklist ##"); for (int i = 0; i < dt.Rows.Count; i++) { result.Append(Environment.NewLine); result.Append("HN : "+dt.Rows[i]["HN"].ToString()+" "); result.Append("EN : "+dt.Rows[i]["Episode"].ToString()+" "); result.Append("Name : "+dt.Rows[i]["Forename"].ToString()+" "+ dt.Rows[i]["Surname"].ToString()+" "); result.Append("Company : " + dt.Rows[i]["Company"].ToString() + " "); } } else { result.Append(Environment.NewLine + Environment.NewLine); result.Append("## รายชื่อที่ไม่มี Checklist ##"); result.Append(Environment.NewLine); result.Append("- ไม่พบข้อมูล -"); } #endregion return result.ToString(); }
private string CenterBuilder() { #region Variable clsDefault clsDefault = new clsDefault(); clsSQL clsSQL = new clsSQL(); clsLanguage clsLanguage = new clsLanguage(); StringBuilder strSQL = new StringBuilder(); StringBuilder strCenter = new StringBuilder(); DataTable dtGroup = new DataTable(); DataTable dt = new DataTable(); #endregion #region MedicalCenterGroup : SQL Query strSQL.Append("SELECT "); strSQL.Append("MedicalCenterGroup.UID,"); strSQL.Append("MedicalCenterGroup.Name "); strSQL.Append("FROM "); strSQL.Append("MedicalCenterGroup "); strSQL.Append("INNER JOIN Language ON MedicalCenterGroup.LanguageUID=Language.UID AND Language.Active='1' "); strSQL.Append("WHERE "); strSQL.Append("MedicalCenterGroup.Active='1' "); strSQL.Append("AND Language.Name='" + clsLanguage.LanguageCurrent + "' "); strSQL.Append("ORDER BY "); strSQL.Append("MedicalCenterGroup.Sort"); #endregion dtGroup = clsSQL.Bind(strSQL.ToString(), dbType, cs); strSQL.Length = 0; strSQL.Capacity = 0; strCenter.Append("<div style='padding:0 10px 0 10px;width:400px;'>"); if (dtGroup != null && dtGroup.Rows.Count > 0) { for (int i = 0; i < dtGroup.Rows.Count; i++) { strCenter.Append("<h4>" + dtGroup.Rows[i]["Name"].ToString() + "</h4>"); #region MedicalCenter #region MedicalCenter : SQL Query strSQL.Append("SELECT "); //strSQL.Append("UID,"); strSQL.Append("DepartmentUID UID,"); strSQL.Append("Name "); strSQL.Append("FROM "); strSQL.Append("MedicalCenter "); strSQL.Append("WHERE "); strSQL.Append("MedicalCenterGroupUID=" + dtGroup.Rows[i]["UID"].ToString() + " "); strSQL.Append("AND Active='1' "); #endregion dt = clsSQL.Bind(strSQL.ToString(), dbType, cs); strSQL.Length = 0; strSQL.Capacity = 0; if (dt != null && dt.Rows.Count > 0) { strCenter.Append("<table cellpadding='0' cellspacing='0'>"); for (int j = 0; j < dt.Rows.Count; j++) { if ((j+1) % 2 != 0) { strCenter.Append("<tr>"); strCenter.Append("<td style='width:200px;'>"); strCenter.Append("<a href='/MedicalCenter/" + dt.Rows[j]["UID"].ToString() + "/" + clsDefault.URLRoutingFilter(dt.Rows[j]["Name"]) + "/'>"); strCenter.Append(dt.Rows[j]["Name"].ToString()); strCenter.Append("</a>"); strCenter.Append("</td>"); } else { strCenter.Append("<td style='width:200px;'>"); strCenter.Append("<a href='/MedicalCenter/" + dt.Rows[j]["UID"].ToString() + "/" + clsDefault.URLRoutingFilter(dt.Rows[j]["Name"]) + "/'>"); strCenter.Append(dt.Rows[j]["Name"].ToString()); strCenter.Append("</a>"); strCenter.Append("</td>"); strCenter.Append("</tr>"); } } if (dt.Rows.Count % 2 != 0) { strCenter.Append("<td style='width:200px;'>"); strCenter.Append("</td>"); strCenter.Append("</tr>"); } strCenter.Append("</table>"); dt = null; } else { strCenter.Append("<div style='text-align:center;'>-</div>"); } #endregion } } else { strCenter.Append("-"); } strCenter.Append("</div>"); /* strCenter.Append("<tr>"); strCenter.Append("<td style='width:200px;'>"); strCenter.Append("<a href='/Center/" + dt.Rows[i]["MedicalCenterUID"].ToString() + "/" + dt.Rows[i]["MedicalCenterName"].ToString() + "/'>"); strCenter.Append(dt.Rows[i]["MedicalCenterName"].ToString()); strCenter.Append("</a>"); strCenter.Append("</td>"); strCenter.Append("<td style='width:200px;'>"); strCenter.Append("<a href='/Center/" + dt.Rows[i]["MedicalCenterUID"].ToString() + "/" + dt.Rows[i]["MedicalCenterName"].ToString() + "/'>"); strCenter.Append(dt.Rows[i]["MedicalCenterName"].ToString()); strCenter.Append("</a>"); strCenter.Append("</td>"); strCenter.Append("</tr>"); */ return strCenter.ToString(); }
public DataTable getLabDetail(DateTime DOEFrom, DateTime DOETo) { 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.No OrderNo,"); strSQL.Append("P.HN,"); strSQL.Append("P.EmployeeID,"); strSQL.Append("P.LabEpisode,"); 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("P.BookCreate,"); strSQL.Append("(SELECT CWhen FROM patientlab WHERE LabEpisode=P.LabEpisode AND WFID=6 LIMIT 0,1) Blood,"); strSQL.Append("(SELECT CWhen FROM patientlab WHERE LabEpisode=P.LabEpisode AND WFID=7 LIMIT 0,1) Urine,"); strSQL.Append("(SELECT CWhen FROM patientlab WHERE LabEpisode=P.LabEpisode AND WFID=8 LIMIT 0,1) Stool,"); strSQL.Append("(SELECT CWhen FROM patientlab WHERE LabEpisode=P.LabEpisode AND WFID=9 LIMIT 0,1) HeavyMetal,"); strSQL.Append("(SELECT MWhen FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND WFID=1 AND ProStatus>=2) RegisterDate "); strSQL.Append("FROM "); strSQL.Append("Patient P "); strSQL.Append("WHERE "); strSQL.Append("(P.DOE BETWEEN '" + DOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + DOETo.ToString("yyyy-MM-dd HH:mm") + "');"); */ strSQL.Append("SELECT "); strSQL.Append("P.No OrderNo,"); strSQL.Append("P.HN,"); strSQL.Append("P.EmployeeID,"); strSQL.Append("P.LabEpisode,"); 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("P.BookCreate,"); strSQL.Append("PLBlood.CWhen Blood,"); strSQL.Append("PLUrine.CWhen Urine,"); strSQL.Append("PLStool.CWhen Stool,"); strSQL.Append("PLHeavyMetal.CWhen HeavyMetal,"); strSQL.Append("PC.MWhen RegisterDate "); strSQL.Append("FROM "); strSQL.Append("Patient P "); strSQL.Append("LEFT JOIN PatientChecklist PC ON P.PatientGUID = PC.PatientGUID AND PC.WFID = 1 AND PC.ProStatus >= 2 "); strSQL.Append("LEFT JOIN PatientLab PLBlood ON P.LabEpisode = PLBlood.LabEpisode AND PLBlood.StatusFlag = 'A' AND PLBlood.WFID = 6 "); strSQL.Append("LEFT JOIN PatientLab PLUrine ON P.LabEpisode = PLUrine.LabEpisode AND PLUrine.StatusFlag = 'A' AND PLUrine.WFID = 7 "); strSQL.Append("LEFT JOIN PatientLab PLStool ON P.LabEpisode = PLStool.LabEpisode AND PLStool.StatusFlag = 'A' AND PLStool.WFID = 8 "); strSQL.Append("LEFT JOIN PatientLab PLHeavyMetal ON P.LabEpisode = PLHeavyMetal.LabEpisode AND PLHeavyMetal.StatusFlag = 'A' AND PLHeavyMetal.WFID = 9 "); strSQL.Append("WHERE "); strSQL.Append("(P.DOE >= '" + DOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND P.DOE <= '" + DOETo.ToString("yyyy-MM-dd HH:mm") + "') "); strSQL.Append("ORDER BY P.Payor,P.NO;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); #endregion return dt; }
public DataTable getPatientMobile_OLD(DateTime DOEFrom, DateTime DOETo, string CompanyName) { 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.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("P.BookCreate,"); strSQL.Append("(SELECT MWhen FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND WFID=1 AND ProStatus=3 LIMIT 0,1) DateRegis,"); strSQL.Append("ProChkListDetail ProgramDetail,"); strSQL.Append("(SELECT Count(RowID) FROM patientchecklist WHERE PatientGUID=P.PatientGUID) CountChecklistAll,"); strSQL.Append("(SELECT Count(RowID) FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND ProStatus=3) CountChecklistComplete,"); strSQL.Append("(SELECT Count(RowID) FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND 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("FROM "); strSQL.Append("Patient P "); strSQL.Append("WHERE "); strSQL.Append("(P.DOE BETWEEN '" + DOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + DOETo.ToString("yyyy-MM-dd HH:mm") + "') "); strSQL.Append("AND Company = '" + CompanyName + "';"); #endregion dt = clsSQL.Bind(strSQL.ToString()); #endregion return dt; }
private void btSearch_Click(object sender, EventArgs e) { #region Variable var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs); var strSQL = new StringBuilder(); var dt = new DataTable(); #endregion #region Procedure #region SQLQuery strSQL.Append(""); strSQL.Append("SELECT "); strSQL.Append("ServerName, Domain, Username, DATE_FORMAT(ConnectWhen,'%d/%m/%Y %T') ConnectWhen,TIMESTAMPDIFF(SECOND, ConnectWhen, DisconnectWhen) UsageSecond, TIMESTAMPDIFF(MINUTE, ConnectWhen, DisconnectWhen) UsageMinute "); strSQL.Append("FROM "); strSQL.Append("sessionlog "); strSQL.Append("WHERE "); if (dtFrom.Checked) { strSQL.Append("ConnectWhen>='"+dtFrom.Value.ToString("yyyy-MM-dd HH:mm")+ "' AND "); } if (dtTo.Checked) { strSQL.Append("ConnectWhen<='" + dtTo.Value.ToString("yyyy-MM-dd HH:mm") + "' AND "); } strSQL.Append("StatusFlag = 'A' AND NOT DisconnectWhen IS NULL;"); #endregion dt = clsSQL.Bind(strSQL.ToString()); if(dt!=null && dt.Rows.Count > 0) { gvDefault.Visible = true; gvDefault.DataSource = dt; dtStatic = dt; btExport.Enabled = true; } else { dtStatic = null; btExport.Enabled = false; gvDefault.Visible = false; MessageBox.Show("ไม่พบข้อมูลที่ต้องการ", "Not found.", MessageBoxButtons.OK, MessageBoxIcon.Information); } #endregion }