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
}