public frmLabReferFee()
{
InitializeComponent();
sqldb = new clsSQL();
grd.DataSource = sqldb.GetDataSet("select * from tbllabreferfee as rf inner join tbllabtest as lt on lt.labTestPK= rf.testPK").Tables[0];
lueLabTest.DataSource = sqldb.GetDataSet("Select * from tbllabtest").Tables[0];
}
public frmLabReferFee()
{
InitializeComponent();
sqldb = new clsSQL();
grd.DataSource = sqldb.GetDataSet("select * from tbllabreferfee as rf inner join tbllabtest as lt on lt.labTestPK= rf.testPK").Tables[0];
lueLabTest.DataSource = sqldb.GetDataSet("Select * from tbllabtest").Tables[0];
}
protected void btLogin_Click(object sender, EventArgs e)
{
#region Variable
var clsSQL = new clsSQL();
var clsDefault = new clsDefault();
var clsColorBox = new clsColorBox();
#endregion
#region Procedure
if (clsSecurity.LoginChecker(
clsSQL.CodeFilter(txtUsername.Text),
clsSQL.CodeFilter(txtPassword.Text),
cbEnableCookie.Checked))
{
//ucColorBox.Redirect(Request.RawUrl, "เข้าสู่ระบบแล้ว");
Response.Redirect(Request.RawUrl);
}
else
{
var script = "document.getElementById('dvUCLogon').scrollIntoView(true);";
Page.ClientScript.RegisterStartupScript(Page.GetType(),
"ucColorBoxScroller",
script,
true);
ucColorBox.Alert("Login Alert", "ไม่พบข้อมูลที่คุณกรอก", AlertImage: global::ucColorBox.Alerts.Fail);
lblLogin.Text = clsDefault.AlertMessageColor("ไม่พบข้อมูลที่คุณกรอก", clsDefault.AlertType.Warn);
}
#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
}
private void Delete(string id)
{
pnDetail.Visible = false;
#region Authorize
if (!clsSecurity.LoginChecker("admin"))
{
ucColorBox1.Redirect("/", "กรุณาล็อคอินด้วยสิทธิ์ Admin");
return;
}
#endregion
#region Variable
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var clsIO = new clsIO();
var strSQL = new StringBuilder();
#endregion
#region Procedure
#region Delete Photo
#region SQL Query
strSQL.Append("SELECT ");
strSQL.Append("Photo ");
strSQL.Append("FROM ");
strSQL.Append(tableDefault + " ");
strSQL.Append("WHERE ");
strSQL.Append("UID=" + id);
#endregion
string photoDelete = clsSQL.Return(strSQL.ToString());
if (!string.IsNullOrEmpty(photoDelete))
{
clsIO.FileExist(photoDelete, true);
}
strSQL.Length = 0; strSQL.Capacity = 0;
#endregion
#region Delete Database
#region SQL Query
strSQL.Append("DELETE FROM ");
strSQL.Append(tableDefault + " ");
strSQL.Append("WHERE ");
strSQL.Append("UID=" + id);
#endregion
if (clsSQL.Execute(strSQL.ToString()))
{
ucColorBox1.Redirect(webDefault);
}
else
{
ucColorBox1.Redirect(webDefault, "เกิดข้อผิดพลาดขณะลบข้อมูล");
return;
}
strSQL.Length = 0; strSQL.Capacity = 0;
#endregion
#endregion
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
clsSQL clsSQL = new clsSQL();
clsSQL.Execute(
"INSERT INTO Content(UID,LanguageUID,Name,Detail,Content,CWhen,CUser,MWhen,MUser,Sort,Active)" +
"VALUES(17,1,'HospitalNetwork','กลุ่มโรงพยาบาลเครือข่าย','กลุ่มโรงพยาบาลเครือข่าย',GETDATE(),0,GETDATE(),0,0,'1');" +
"INSERT INTO Content(UID,LanguageUID,Name,Detail,Content,CWhen,CUser,MWhen,MUser,Sort,Active)" +
"VALUES(18,2,'HospitalNetwork','กลุ่มโรงพยาบาลเครือข่าย','กลุ่มโรงพยาบาลเครือข่าย',GETDATE(),0,GETDATE(),0,0,'1');",
clsSQL.DBType.SQLServer, "cs");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
clsSQL clsSQL = new clsSQL();
if (!clsSQL.IsConnected(dbType, cs))
{
Response.Write("ไม่สามารถเชื่อมต่อฐานข้อมูลได้");
return;
}
MenuBuilder();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
clsSQL clsSQL = new clsSQL();
if (!clsSQL.IsConnected(dbType, cs))
{
Response.Write("ไม่สามารถเชื่อมต่อฐานข้อมูลได้");
return;
}
MenuBuilder();
}
}
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
}
public bool DoctorScheduleSyncer()
{
#region Variable
clsSQL clsSQL = new clsSQL();
FileInfo fi = new FileInfo(System.Web.HttpContext.Current.Server.MapPath("/Upload/Doctor/SQLQuery.txt"));
string sqlQuery = "";
bool result = false;
#endregion
if (fi.Exists)
{
StreamReader StrWer;
try
{
StrWer = File.OpenText(System.Web.HttpContext.Current.Server.MapPath("/Upload/Doctor/SQLQuery.txt"));
while (!(StrWer.EndOfStream))
{
sqlQuery = sqlQuery + StrWer.ReadLine();
}
StrWer.Close();
}
catch (Exception) { }
if (sqlQuery != "")
{
clsMail clsMail = new clsMail();
string outMail = ""; string outSQLError = "";
if (!clsSQL.Execute(sqlQuery, dbType, cs,out outSQLError))
{
clsMail.Send("*****@*****.**", "*****@*****.**",
"DoctorScheduleSyncer : Error Insert (OnWeb)",
outSQLError + "<hr/>"+sqlQuery, out outMail);
//lblDoctorScheduleSyncer.Text = "DoctorScheduleSyncer : เกิดข้อผิดพลาดขณะรันคำสั่ง<br/>"+sqlQuery;
fi.Delete();
}
else
{
fi.Delete();
result = true;
}
}
}
else
{
result = true;
}
return result;
}
public bool DoctorScheduleSyncer()
{
#region Variable
clsSQL clsSQL = new clsSQL();
FileInfo fi = new FileInfo(System.Web.HttpContext.Current.Server.MapPath("/Upload/Doctor/SQLQuery.txt"));
string sqlQuery = "";
bool result = false;
#endregion
if (fi.Exists)
{
StreamReader StrWer;
try
{
StrWer = File.OpenText(System.Web.HttpContext.Current.Server.MapPath("/Upload/Doctor/SQLQuery.txt"));
while (!(StrWer.EndOfStream))
{
sqlQuery = sqlQuery + StrWer.ReadLine();
}
StrWer.Close();
}
catch (Exception) { }
if (sqlQuery != "")
{
clsMail clsMail = new clsMail();
string outMail = ""; string outSQLError = "";
if (!clsSQL.Execute(sqlQuery, dbType, cs, out outSQLError))
{
clsMail.Send("*****@*****.**", "*****@*****.**",
"DoctorScheduleSyncer : Error Insert (OnWeb)",
outSQLError + "<hr/>" + sqlQuery, out outMail);
//lblDoctorScheduleSyncer.Text = "DoctorScheduleSyncer : เกิดข้อผิดพลาดขณะรันคำสั่ง<br/>"+sqlQuery;
fi.Delete();
}
else
{
fi.Delete();
result = true;
}
}
}
else
{
result = true;
}
return(result);
}
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
}
protected void btDGSubmit_Click(object sender, EventArgs e)
{
#region Authorize
if (!clsSecurity.LoginChecker("admin"))
{
ucColorBox1.Redirect("/", "เกิดข้อผิดพลาด", "คุณไม่ได้รับสิทธิ์ในการบันทึกข้อมูล กรุณาล็อคอินด้วยสิทธิ์ Admin");
return;
}
#endregion
#region Variable
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
#endregion
#region Procedure
#region SQL Builder
for (int i = 0; i < dlDefault.Items.Count; i++)
{
Label lblDGID = (Label)dlDefault.Items[i].FindControl("lblDGID");
CheckBox cbDGActive = (CheckBox)dlDefault.Items[i].FindControl("cbDGActive");
TextBox txtDGSort = (TextBox)dlDefault.Items[i].FindControl("txtDGSort");
if (lblDGID != null && cbDGActive != null)
{
#region SQL Query
strSQL.Append("UPDATE ");
strSQL.Append(tableDefault + " ");
strSQL.Append("SET ");
strSQL.Append("Sort=" + clsSQL.CodeFilter(txtDGSort.Text) + ",");
strSQL.Append("StatusFlag='" + (cbDGActive.Checked ? "A" : "I") + "' ");
strSQL.Append("WHERE ");
strSQL.Append("UID=" + lblDGID.Text);
strSQL.Append(";");
#endregion
}
}
#endregion
if (clsSQL.Execute(strSQL.ToString()))
{
ucColorBox1.Redirect("/Management/" + webDefault + clsDefault.QueryStringMerge(), "ดำเนินการเสร็จสิ้น", "แก้ไขข้อมูลเสร็จเรียบร้อย");
}
else
{
ucColorBox1.Alert("เกิดข้อผิดพลาด", "เกิดข้อผิดพลาดขณะบันทึกข้อมูลลงฐานข้อมูล<br/>" + strSQL.ToString(), AlertImage: ucColorBox.Alerts.Fail);
}
#endregion
}
protected void btLogin_Click(object sender, EventArgs e)
{
clsSQL clsSQL = new clsSQL();
clsDefault clsDefault = new clsDefault();
if (clsSecurity.LoginChecker(
clsSQL.CodeFilter(txtUsername.Text),
clsSQL.CodeFilter(txtPassword.Text),
cbEnableCookie.Checked))
{
//LoginChecker();
Response.Redirect(Request.RawUrl);
}
else
{
lblLogin.Text = clsDefault.AlertMessageColor("ไม่พบข้อมูลที่คุณกรอก", clsDefault.AlertType.Warn);
}
}
protected void btLogin_Click(object sender, EventArgs e)
{
clsSQL clsSQL = new clsSQL();
clsDefault clsDefault = new clsDefault();
if(clsSecurity.LoginChecker(
clsSQL.CodeFilter(txtUsername.Text),
clsSQL.CodeFilter(txtPassword.Text),
cbEnableCookie.Checked))
{
//LoginChecker();
Response.Redirect(Request.RawUrl);
}
else
{
lblLogin.Text = clsDefault.AlertMessageColor("ไม่พบข้อมูลที่คุณกรอก", clsDefault.AlertType.Warn);
}
}
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
}
public static void Save(string labTestPricePK, string labTestPK, string branchPK, double originalPrice, double profitMargin,
int exchangePK, double discount, double sellingPrice)
{
clsSQL sql = new clsSQL();
bool isExisted = false;
isExisted = sql.ExecuteScalar<Int32>("Select Count(*) from tblLabTestPrice where labTestPricePK=@PK", new MySqlParameter("@PK", labTestPricePK)) > 0 ? true : false;
if (!isExisted)
{
//Insert
labTestPricePK = Guid.NewGuid().ToString();
sql.ExecuteNonQuery("Insert into tblLabTestPrice(labTestPricePK,labTestPK,branchPK,originalPrice,ProfitMargin,exchangePK,discount,sellingPrice,createPK,createDate,updatePK,updateDate) values(" +
"@labTestPricePK,@labTestPK,@branchPK,@originalPrice,@ProfitMargin,@exchangePK,@discount,@sellingPrice,@createPK,@createDate,@updatePK,@updateDate)",
new MySqlParameter("labTestPricePK", labTestPK),
new MySqlParameter("@labTestPK", labTestPK),
new MySqlParameter("@branchPK", branchPK),
new MySqlParameter("@originalPrice", originalPrice),
new MySqlParameter("@ProfitMargin", profitMargin),
new MySqlParameter("@exchangePK", exchangePK),
new MySqlParameter("@discount", discount),
new MySqlParameter("@sellingPrice", sellingPrice),
new MySqlParameter("@createPK", "1"),
new MySqlParameter("@createDate", DateTime.Now),
new MySqlParameter("@updatePK", "1"),
new MySqlParameter("@updateDate", DateTime.Now));
}
else
{
//Update
sql.ExecuteNonQuery("Update tblLabTestPrice set labTestPricePK=@labTestPricePK,labTestPK=@labTestPK,branchPK=@branchPK,originalPrice=@originalPrice,ProfitMargin=@ProfitMargin,exchangePK=@exchangePK,discount=@discount,sellingPrice=@sellingPrice," +
"updatePK=@updatePK,updateDate=@updateDate where labTestPricePK=@labTestPricePK",
new MySqlParameter("labTestPricePK", labTestPricePK),
new MySqlParameter("@labTestPK", labTestPK),
new MySqlParameter("@branchPK", branchPK),
new MySqlParameter("@originalPrice", originalPrice),
new MySqlParameter("@ProfitMargin", profitMargin),
new MySqlParameter("@exchangePK", exchangePK),
new MySqlParameter("@discount", discount),
new MySqlParameter("@sellingPrice", sellingPrice),
new MySqlParameter("@updatePK", "1"),
new MySqlParameter("@updateDate", DateTime.Now));
}
}
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;
}
/// <summary>
/// บันทึก Log การส่งอีเมล์จากระบบ Template
/// </summary>
/// <param name="UID">EmailTemplateUID</param>
/// <param name="From">จากเมล์</param>
/// <param name="To">ถึงเมล์</param>
/// <param name="Cc">แนบเมล์</param>
/// <param name="Bcc">ไม่เปิดเผยเมล์</param>
/// <param name="Result">ผลการส่ง</param>
/// <returns>true=บันทึกสำเร็จ , false=บันทึกไม่สำเร็จ</returns>
private bool SendTemplateLog(string UID, string From, string To, string Cc, string Bcc, string Result)
{
#region Variable
bool rtnValue = false;
clsSQL clsSQL = new clsSQL();
clsSecurity clsSecurity = new clsSecurity();
clsNet clsNet = new clsNet();
string outSQL;
#endregion
#region SQL Query
if (clsSQL.Insert(
"EmailTemplateLog",
new string[, ] {
{ "UID", clsSQL.GetNewID("UID", "EmailTemplateLog", "", dbType, cs).ToString() },
{ "EmailTemplateUID", UID },
{ "EmailFrom", "'" + clsSQL.CodeFilter(From) + "'" },
{ "EmailTo", "'" + clsSQL.CodeFilter(To) + "'" },
{ "EmailCc", "'" + clsSQL.CodeFilter(Cc) + "'" },
{ "EmailBcc", "'" + clsSQL.CodeFilter(Bcc) + "'" },
{ "Result", "'" + clsSQL.CodeFilter(Result) + "'" },
{ "CWhen", "GETDATE()" },
{ "CUser", "0" },
{ "CIP", "'" + clsNet.IPGet() + "'" },
{ "CHostname", "'" + clsNet.ComNameGet() + "'" }
},
new string[, ] {
{}
},
dbType,
cs,
out outSQL))
{
rtnValue = true;
}
#endregion
return(rtnValue);
}
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 getCompanyMobile(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("DISTINCT P.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.Company;");
#endregion
dt = clsSQL.Bind(strSQL.ToString());
#endregion
return dt;
}
/// <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);
}
static void Main(string[] args)
{
clsSQL objSQL = new clsSQL();
String strTest;
strTest = "";
strTest = objSQL.Get_SQL_Connection_String("", "websource");
Console.WriteLine("a");
Console.ReadKey();
Console.WriteLine("Git is good");
Console.ReadKey();
Console.WriteLine("Git is real good");
// test
// another comment
// test2
}
/// <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 string getPatientCountAll(DateTime dtDOEFrom,DateTime dtDOETo,string Company)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
#region Variable
var result = "";
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
#endregion
#region Procedure
strSQL.Append("SELECT COUNT(PatientGUID) FROM patient WHERE (DOE BETWEEN '" + dtDOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + dtDOETo.ToString("yyyy-MM-dd HH:mm") + "') AND Company='"+Company+"'");
result = clsSQL.Return(strSQL.ToString());
#endregion
return result;
}
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("A.Photo GroupPhoto, A.Name GroupName,A.NameEN GroupNameEN, A.Detail GroupDetail,A.DetailEN GroupDetailEN, B.PhotoPreview,B.Photo,B.Name,B.Detail ");
strSQL.Append("FROM ");
strSQL.Append("P5_PhotoGalleryGroup A ");
strSQL.Append("LEFT JOIN P5_PhotoGallery B ON A.UID = B.PhotoGalleryGroupUID AND B.StatusFlag = 'A' ");
strSQL.Append("WHERE ");
strSQL.Append("A.StatusFlag = 'A' ");
strSQL.Append("AND A.UID=@UID ");
strSQL.Append("ORDER BY B.Sort;");
#endregion
dt = clsSQL.Bind(strSQL.ToString(), new string[, ] {
{ "@UID", uid }
});
if (dt != null && dt.Rows.Count > 0)
{
portfolioIcon = dt.Rows[0]["GroupPhoto"].ToString();
if (clsLanguage.LanguageCurrent == "th-TH")
{
portfolioName = dt.Rows[0]["GroupName"].ToString();
portfolioDetail = dt.Rows[0]["GroupDetail"].ToString();
}
else
{
portfolioName = dt.Rows[0]["GroupNameEN"].ToString();
portfolioDetail = dt.Rows[0]["GroupDetailEN"].ToString();
}
this.Title = portfolioName;
lblIcon.Text = "<img src='" + portfolioIcon + "' alt='" + portfolioName + "' title='" + portfolioName + "' style='width:100px;'/>";
lblName.Text = portfolioName;
lblDetail.Text = portfolioDetail;
lblContent.Text = portfolioContent;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i % 4 == 0)
{
lblContent.Text += "<br/>";
}
lblContent.Text += "<div style='display:inline-block;text-align:center;margin:10px 15px;'>";
lblContent.Text += "<a href='" + dt.Rows[i]["Photo"].ToString() + "' class='cbPhoto' title='" + dt.Rows[i]["Detail"].ToString() + "'>";
//lblContent.Text += "<img src='" + dt.Rows[i]["PhotoPreview"].ToString() + "' style='border-radius:20px;' title='" + dt.Rows[i]["Detail"].ToString() + "'/>";
lblContent.Text += "<img src='" + dt.Rows[i]["PhotoPreview"].ToString() + "' style='' title='" + dt.Rows[i]["Detail"].ToString() + "'/>";
//lblContent.Text += "<p style='color:#4D4D4D;font-family:thaisans_neueregular;'>" + dt.Rows[i]["Name"].ToString() + "</p>";
lblContent.Text += "</a>";
lblContent.Text += "</div>";
}
}
else
{
portfolioName = "- ไม่พบข้อมูล -";
}
#endregion
}
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;
}
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
}
/// <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);
}
/// <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;
}
public DataTable getPatientMobileByBookCreate(DateTime DOEFrom, DateTime DOETo, string BookCreate, bool withHeavyMetal = true)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
#region Variable
var dt = new DataTable();
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
#endregion
#region Procedure
#region SQLQuery
strSQL.Append("SELECT ");
strSQL.Append("P.PatientGUID,");
strSQL.Append("P.No OrderNo,");
strSQL.Append("P.HN,");
strSQL.Append("P.EmployeeID,");
strSQL.Append("CONCAT(P.Forename,' ',P.Surname) Name,");
strSQL.Append("P.POS Position,");
strSQL.Append("P.DEP Department,");
strSQL.Append("P.DIVI Division,");
strSQL.Append("P.SEC Section,");
strSQL.Append("P.Line,");
strSQL.Append("P.Shift,");
strSQL.Append("P.Location Site,");
strSQL.Append("P.Payor,");
strSQL.Append("IFNULL(P.BookCreate,'') BookCreate,");
//strSQL.Append("(SELECT MWhen FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND WFID=1 AND ProStatus=3 LIMIT 0,1) DateRegis,");
strSQL.Append("(SELECT RegDate FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND WFID=1 LIMIT 0,1) DateRegis,");
strSQL.Append("(SUM(PC.WFID=1 AND PC.ProStatus=3)) RegisStatus,");
strSQL.Append("(SUM(PC.WFID=1 AND PC.ProStatus=2)) RegisProStatus2,");
strSQL.Append("ProChkListDetail ProgramDetail,");
strSQL.Append("COUNT(PC.RowID) CountChecklistAll,");
strSQL.Append("SUM(ProStatus>=3) CountChecklistComplete,");
strSQL.Append("SUM(ProStatus=4) CountChecklistCancel,");
//strSQL.Append("(SELECT CONVERT(GROUP_CONCAT(WorkFlow SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND ProStatus<>3) ProgramPending,");
//strSQL.Append("(SELECT CONVERT(GROUP_CONCAT(WorkFlow SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID=P.PatientGUID AND ProStatus=4) ProgramCancel ");
strSQL.Append("'' ProgramPending,");
strSQL.Append("'' ProgramCancel,");
strSQL.Append("P.SyncStatus,P.SyncWhen ");
strSQL.Append("FROM ");
strSQL.Append("Patient P ");
strSQL.Append("INNER JOIN patientchecklist PC ON P.PatientGUID=PC.PatientGUID ");
var heavyMetalSQL = new StringBuilder();
if (!withHeavyMetal)
{
try
{
string[] heavyMetalNames = System.Configuration.ConfigurationManager.AppSettings["heavyMetalName"].Split(',');
for (int k = 0; k < heavyMetalNames.Length; k++)
{
heavyMetalSQL.Append("AND WorkFlow NOT LIKE '%" + heavyMetalNames[k] + "%' ");
}
}
catch (Exception)
{
heavyMetalSQL.Append("AND WorkFlow NOT LIKE '%ปัสสาวะสารเคมี%' ");
}
}
strSQL.Append(heavyMetalSQL.ToString());
strSQL.Append("WHERE ");
strSQL.Append("(P.DOE BETWEEN '" + DOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + DOETo.ToString("yyyy-MM-dd HH:mm") + "') ");
if (BookCreate != "")
{
strSQL.Append("AND BookCreate = '" + BookCreate + "' ");
}
strSQL.Append("GROUP BY P.PatientGUID ");
strSQL.Append("ORDER BY P.BookCreate,P.No;");
#endregion
dt = clsSQL.Bind(strSQL.ToString());
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["CountChecklistAll"].ToString().Trim() != dt.Rows[i]["CountChecklistComplete"].ToString())
{
dt.Rows[i]["ProgramPending"] = clsSQL.Return("SELECT CONVERT(GROUP_CONCAT(WorkFlow SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID='" + dt.Rows[i]["PatientGUID"].ToString() + "' AND ProStatus<>3 AND ProStatus<>4 " + heavyMetalSQL.ToString());
}
if (dt.Rows[i]["CountChecklistCancel"].ToString().Trim() != "0")
{
//dt.Rows[i]["ProgramCancel"] = clsSQL.Return("SELECT CONVERT(GROUP_CONCAT(WorkFlow SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID='" + dt.Rows[i]["PatientGUID"].ToString() + "' AND ProStatus=4");
dt.Rows[i]["ProgramCancel"] = clsSQL.Return("SELECT CONVERT(GROUP_CONCAT(ProStatusRemark SEPARATOR ',') USING 'UTF8') FROM patientchecklist WHERE PatientGUID='" + dt.Rows[i]["PatientGUID"].ToString() + "' AND ProStatus=4 "+ heavyMetalSQL.ToString());
}
}
dt.AcceptChanges();
}
#endregion
return dt;
}
public 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;
}
private void Report_Load(object sender, EventArgs e)
{
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
if (!clsSQL.IsConnected())
{
MessageBox.Show("ไม่สามารถเชื่อมต่อฐานข้อมูล Mobile ได้", "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
dtDOEFrom.Enabled = false;dtDOETo.Enabled = false;btSearch.Enabled = false;btExport.Enabled = false;ddlCompany.Enabled = false;
return;
}
setType();
}
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();
}
protected void btSendMail_Click(object sender, EventArgs e)
{
#region Variable
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var clsDefault = new clsDefault();
#endregion
#region Procedure
if (hidFileName.Value != "")
{
#region SQLQuery
strSQL.Append("INSERT INTO ");
strSQL.Append("P5_Job");
strSQL.Append("(FileName,Name,Detail,ContactName,ContactPhone,ContactEmail,Location,CWhen,MWhen)");
strSQL.Append("VALUES(");
strSQL.Append("'" + hidFileName.Value.Trim() + "',");
strSQL.Append("'" + txtName.Text.SQLQueryFilter() + "',");
strSQL.Append("'" + txtDetail.Text.SQLQueryFilter() + "',");
strSQL.Append("'" + txtContactName.Text.SQLQueryFilter() + "',");
strSQL.Append("'" + txtContactPhone.Text.SQLQueryFilter() + "',");
strSQL.Append("'" + txtContactEmail.Text.SQLQueryFilter() + "',");
strSQL.Append("'" + txtLocation.Text.SQLQueryFilter() + "',");
strSQL.Append("GETDATE(),");
strSQL.Append("GETDATE()");
strSQL.Append(");");
#endregion
if (clsSQL.Execute(strSQL.ToString()))
{
#region MailSender
var clsMail = new clsMail();
var outMessage = "";
//Send to Admin
try
{
if (!clsMail.SendByGmail(
"*****@*****.**",
"G00des1gn",
System.Configuration.ConfigurationManager.AppSettings["mailTo"],
"P5GraphicDesign : มีใบงานใหม่ '" + txtName.Text.SQLQueryFilter() + "'",
string.Format("<h1>มีใบงานใหม่ : {0}</h1><div><b>จาก</b> : {1}</div><div><b>เบอร์โทร</b> : {2}</div><div><b>รายละเอียด</b> : {3}</div><hr/><a href='http://www.p5graphicdesign.com/Management/Job.aspx'>คลิกที่นี่เพื่อดูข้อมูล</a>",
txtName.Text.SQLQueryFilter(),
txtContactName.Text.SQLQueryFilter(),
txtContactPhone.Text.SQLQueryFilter(),
txtDetail.Text.SQLQueryFilter()),
out outMessage,
"P5GraphicDesign : มีใบงานใหม่ '" + txtName.Text.SQLQueryFilter() + "'",
"*****@*****.**", "", "", System.Net.Mail.MailPriority.High))
{
Response.Write("Send to Admin : " + outMessage);
//ucColorBox1.Alert("พบข้อผิดพลาดขณะส่งเมล์", outMessage, AlertImage: ucColorBox.Alerts.Fail);
return;
}
}
catch (Exception exMailToAdmin) { Response.Write(exMailToAdmin.Message); }
if (txtContactEmail.Text.Trim() != "" && txtContactEmail.Text.Contains("@") && txtContactEmail.Text.Contains("."))
{
//Send to Customer
try
{
if (!clsMail.SendByGmail(
"*****@*****.**",
"G00des1gn",
txtContactEmail.Text.Trim(),
"P5GraphicDesign : ได้รับใบงาน '" + txtName.Text.SQLQueryFilter() + "' ของคุณแล้ว",
string.Format("<h1>ได้รับใบงานใหม่เรียบร้อยแล้ว : {0}</h1><div><b>จาก</b> : {1}</div><div><b>เบอร์โทร</b> : {2}</div><div><b>รายละเอียด</b> : {3}</div>",
txtName.Text.SQLQueryFilter(),
txtContactName.Text.SQLQueryFilter(),
txtContactPhone.Text.SQLQueryFilter(),
txtDetail.Text.SQLQueryFilter()),
out outMessage,
"P5GraphicDesign : ได้รับใบงาน '" + txtName.Text.SQLQueryFilter() + "' ของคุณแล้ว",
"", "", "", System.Net.Mail.MailPriority.High))
{
Response.Write(outMessage);
//ucColorBox1.Alert("พบข้อผิดพลาดขณะส่งเมล์", outMessage, AlertImage: ucColorBox.Alerts.Fail);
return;
}
}
catch (Exception exMailToCustomer) { Response.Write("Send to Customer : " + exMailToCustomer.Message); }
}
#endregion
txtName.Text = ""; txtDetail.Text = ""; txtContactName.Text = ""; txtContactPhone.Text = ""; txtLocation.Text = ""; hidFileName.Value = "";
lblSendMailAlert.Text = clsDefault.AlertMessageFlat("บันทึกข้อมูลเสร็จสมบูรณ์", clsDefault.AlertType.Success);
lblSendMailAlert.Focus();
}
else
{
lblSendMailAlert.Text = clsDefault.AlertMessageFlat("เกิดข้อผิดพลาดขณะบันทึกข้อมูล<br/>" + strSQL.ToString(), clsDefault.AlertType.Fail);
lblSendMailAlert.Focus();
}
}
else
{
lblSendMailAlert.Text = clsDefault.AlertMessageFlat("โปรดเลือกอัพโหลดไฟล์ก่อนทำการส่งข้อมูล", clsDefault.AlertType.Fail);
lblSendMailAlert.Focus();
}
#endregion
}
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 string getPatientCountPending(DateTime dtDOEFrom, DateTime dtDOETo, string Company)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
#region Variable
var result = "";
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
#endregion
#region Procedure
strSQL.Append("SELECT ");
strSQL.Append("COUNT(P.PatientGUID)CountNotRegister ");
strSQL.Append("FROM ");
strSQL.Append("patient P ");
strSQL.Append("WHERE ");
strSQL.Append("(P.DOE BETWEEN '" + dtDOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + dtDOETo.ToString("yyyy-MM-dd HH:mm") + "') ");
strSQL.Append("AND P.Company = '" + Company + "' ");
strSQL.Append("AND(SELECT COUNT(RowID) FROM patientchecklist WHERE PatientGUID = P.PatientGUID AND patientchecklist.RegDate IS NULL) > 0;");
result = clsSQL.Return(strSQL.ToString());
#endregion
return result;
}
/// <summary>
/// หา UID ใหม่ในฐานข้อมูล จากเงื่อนไขที่เรากำหนด
/// </summary>
/// <param name="id_column_name">ชื่อฟิลด์ที่ต้องการ (UID)</param>
/// <param name="fromTable">ชื่อ Table</param>
/// <param name="whereStr">เงื่อนไขพิเศษ อาจเว้นว่างไว้ก็ได้ (member_active='1')</param>
/// <param name="strDBType">ชนิดของฐานข้อมูล เช่น sql,odbc,mysql</param>
/// <param name="appsetting_name">ชื่อตัวแปรที่เก็บ ConnectionString ในไฟล์ AppSetting</param>
/// <returns>คืนค่า UID ใหม่</returns>
/// <example>
/// clsSQL.GetNewID("member_id","MEMBER","member_active='1'",clsSQL.DBType.MySQL,"cs");
/// </example>
public int GetNewID(string id_column_name, string fromTable, string whereStr, DBType dbType, string cs)
{
StringBuilder strSQL = new StringBuilder();
int id = 0;
string functionName;
if (dbType == DBType.SQLServer)
{
functionName = "ISNULL";
}
else if (dbType == DBType.MySQL)
{
functionName = "IFNULL";
}
else
{
functionName = "IFNULL";
}
strSQL.Append("SELECT ");
strSQL.Append(functionName + "(MAX(" + id_column_name + "),0)+1 ");
strSQL.Append("FROM ");
strSQL.Append(fromTable + " ");
if (!string.IsNullOrEmpty(whereStr))
{
strSQL.Append("WHERE ");
strSQL.Append(whereStr);
}
clsSQL clsSQL = new clsSQL();
id = int.Parse(clsSQL.Return(strSQL.ToString()));
return id;
}
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());
}
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();
}
}
private void ContentBuilder()
{
#region Variable
var strSQL = new StringBuilder();
var strScript = new StringBuilder();
var dt = new DataTable();
var dtIndex = 0;
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var clsSecurity = new clsSecurity();
#endregion
#region Procedure
#region SQL Query
strSQL.Append("SELECT ");
strSQL.Append("Content.UID,");
strSQL.Append("Content.Name,");
strSQL.Append("Content.Detail,");
strSQL.Append("Content.Content,Content.ContentEN ");
strSQL.Append("FROM ");
strSQL.Append("P5_Content Content ");
strSQL.Append("WHERE ");
strSQL.Append("Content.StatusFlag='A' ");
strSQL.Append("AND Content.Name='" + _contentName.Trim() + "';");
#endregion
dt = clsSQL.Bind(strSQL.ToString());
if (dt != null && dt.Rows.Count > 0)
{
strScript.Append("<div class='" + (clsSecurity.LoginChecker("Admin") ? "dvContent" : "dvContentNormal") + "'>");
#region Default Builder
if (dt.Rows[0]["Content"] != DBNull.Value)
{
var clsLanguage = new clsLanguage();
if (clsLanguage.LanguageCurrent == "th-TH")
{
strScript.Append(dt.Rows[0]["Content"].ToString());
}
else
{
strScript.Append((dt.Rows[0]["ContentEN"].ToString() != ""? dt.Rows[0]["ContentEN"].ToString(): 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();
}
#endregion
}
protected void btSubmit_Click(object sender, EventArgs e)
{
#region Authorize
if (!clsSecurity.LoginChecker("admin"))
{
ucColorBox1.Redirect("/", "กรุณาล็อคอินด้วยสิทธิ์ Admin");
return;
}
#endregion
#region Variable
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var strSQL = new StringBuilder();
var id = 0;
var outSQL = "";
var photoName = "";
#endregion
#region Procedure
#region Update
if (Request.QueryString["id"] != null && clsDefault.QueryStringChecker("command") == "edit")
{
id = int.Parse(Request.QueryString["id"].ToString());
#region Photo Upload
if (fuPhoto.HasFile)
{
var clsIO = new clsIO();
string outErrorMessage; string outFilename;
if (clsIO.UploadPhoto(
fuPhoto, pathUpload,
tableDefault + id.ToString(),
out outErrorMessage,
out outFilename,
maxWidth: photoWidth,
maxHeight: photoHeight))
{
photoName = outFilename;
}
else
{
ucColorBox1.Alert("เกิดข้อผิดพลาด", "เกิดข้อผิดพลาดขณะอัพโหลดไฟล์รูปภาพ<br/>" + outErrorMessage, AlertImage: ucColorBox.Alerts.Fail);
return;
}
}
#endregion
if (clsSQL.Update(tableDefault,
new string[, ] {
{ "Photo", (!string.IsNullOrEmpty(photoName)?"'" + pathUpload + photoName + "'":"Photo") },
{ "Name", "'" + clsSQL.CodeFilter(txtName.Text) + "'" },
{ "Detail", "'" + clsSQL.CodeFilter(txtDetail.Text) + "'" },
{ "NameEN", "'" + clsSQL.CodeFilter(txtNameEN.Text) + "'" },
{ "DetailEN", "'" + clsSQL.CodeFilter(txtDetailEN.Text) + "'" },
{ "MetaKeywords", "'" + clsSQL.CodeFilter(txtMetaKeyword.Text) + "'" },
{ "MetaDescription", "'" + clsSQL.CodeFilter(txtMetaDescription.Text) + "'" },
{ "MUser", "'" + clsSecurity.LoginUID + "'" },
{ "MWhen", functionGetDate },
{ "Sort", clsSQL.CodeFilter(txtSort.Text) },
{ "StatusFlag", "'" + (cbActive.Checked ? "A" : "I") + "'" }
}, new string[, ] {
{ parameterChar + "UID", id.ToString() }
},
"UID=" + parameterChar + "UID",
out outSQL))
{
ucColorBox1.ReloadParent();
}
else
{
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล<br/>", outSQL, AlertImage: ucColorBox.Alerts.Fail);
}
}
#endregion
#region Insert
else
{
#region Find New ID
id = clsSQL.GetNewIDAutoIncrement(tableDefault);
if (id == 0)
{
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล", "ไม่สามารถหา UID ใหม่ได้", AlertImage: ucColorBox.Alerts.Fail);
return;
}
#endregion
#region Photo Upload
if (fuPhoto.HasFile)
{
clsIO clsIO = new clsIO();
string outErrorMessage; string outFilename;
if (clsIO.UploadPhoto(
fuPhoto, pathUpload,
tableDefault + id.ToString(),
out outErrorMessage,
out outFilename,
maxWidth: photoWidth,
maxHeight: photoHeight))
{
photoName = outFilename;
}
else
{
ucColorBox1.Alert("เกิดข้อผิดพลาด", "เกิดข้อผิดพลาดขณะอัพโหลดไฟล์รูปภาพ<br/>" + outErrorMessage, AlertImage: ucColorBox.Alerts.Fail);
return;
}
}
#endregion
if (clsSQL.Insert(tableDefault,
new string[, ] {
{ "Photo", (!string.IsNullOrEmpty(photoName)?"'" + pathUpload + photoName + "'":"null") },
{ "Name", "'" + clsSQL.CodeFilter(txtName.Text) + "'" },
{ "Detail", "'" + clsSQL.CodeFilter(txtDetail.Text) + "'" },
{ "NameEN", "'" + clsSQL.CodeFilter(txtNameEN.Text) + "'" },
{ "DetailEN", "'" + clsSQL.CodeFilter(txtDetailEN.Text) + "'" },
{ "Type", "'Experiences'" },
{ "MetaKeywords", "'" + clsSQL.CodeFilter(txtMetaKeyword.Text) + "'" },
{ "MetaDescription", "'" + clsSQL.CodeFilter(txtMetaDescription.Text) + "'" },
{ "CUser", "'" + clsSecurity.LoginUID + "'" },
{ "CWhen", functionGetDate },
{ "MUser", "'" + clsSecurity.LoginUID + "'" },
{ "MWhen", functionGetDate },
{ "Sort", clsSQL.CodeFilter(txtSort.Text) },
{ "StatusFlag", "'" + (cbActive.Checked ? "A" : "I") + "'" }
}, new string[, ] {
{ }
},
out outSQL))
{
ucColorBox1.ReloadParent();
}
else
{
ucColorBox1.Alert("เกิดข้อผิดพลาดขณะบันทึกข้อมูล<br/>", outSQL, AlertImage: ucColorBox.Alerts.Fail);
}
}
#endregion
#endregion
}
/// <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;
}
/// <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>
/// ดึงค่า 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;
}
public int getMaxDateRegis(DateTime DOEFrom,DateTime DOETo)
{
#region Variable
var strSQL = new StringBuilder();
var clsSQL = new clsSQL(clsGlobal.dbType, clsGlobal.cs);
var result = 0;
#endregion
#region Procedure
strSQL.Append("SELECT COUNT(A.Result) ");
strSQL.Append("FROM(");
strSQL.Append("SELECT patient.Payor, DATE(patientchecklist.MWhen) Result ");
strSQL.Append("FROM patient ");
strSQL.Append("INNER JOIN patientchecklist ON patient.PatientGUID = patientchecklist.PatientGUID AND patientchecklist.WFID = 1 AND patientchecklist.ProStatus = 3 ");
strSQL.Append("WHERE ");
strSQL.Append("patient.DOE BETWEEN '" + DOEFrom.ToString("yyyy-MM-dd HH:mm") + "' AND '" + DOETo.ToString("yyyy-MM-dd HH:mm") + "' ");
strSQL.Append("GROUP BY patient.Payor, DATE(patientchecklist.MWhen) ");
strSQL.Append(") A ");
strSQL.Append("GROUP BY A.Payor ");
strSQL.Append("ORDER BY COUNT(A.Result) DESC ");
strSQL.Append("LIMIT 0, 1;");
try
{
var resultTemp = clsSQL.Return(strSQL.ToString());
if (resultTemp != "")
{
result = int.Parse(resultTemp);
}
else { result = 0; }
}
catch (Exception) { }
#endregion
return result;
}
/// <summary>
/// หารหัส UID ใหม่จากฟิลด์ประเภท AutoIncrement
/// </summary>
/// <param name="tbName"></param>
/// <param name="dbType"></param>
/// <param name="appSettingName"></param>
/// <returns></returns>
public int GetNewIDAutoIncrement(string tbName)
{
#region Variable
var clsSQL = new clsSQL(dbType, cs);
var strSQL = "";
var id = 0;
#endregion
#region Procedure
if (dbType == DBType.SQLServer)
{
strSQL = "SELECT ISNULL(IDENT_CURRENT('" + tbName + "'),0)+1;";
}
else if (dbType == DBType.MySQL)
{
strSQL = @"SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name='" + tbName + @"'
AND table_schema=DATABASE()";
}
else
{
return 0;
}
try
{
id = int.Parse(clsSQL.Return(strSQL.ToString()));
}
catch (Exception) { }
#endregion
return id;
}
public DataTable 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;
}
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();
}
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;
}
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 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;
}
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;
}
/// <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)
{
#region Variable
var result = false;
var clsDefault = new clsDefault();
var clsSQL = new clsSQL(_dbType, _cs);
var strSQL = new StringBuilder();
var strCookie = "";
var dt = new DataTable();
#endregion
#region Procedure
#region Session
if (HttpContext.Current.Session[_sessionName] != null)
{
if (!string.IsNullOrEmpty(GroupName))
{
if (GetLoginSession(_sessionName, _sessionGroup).ToLower() == GroupName.ToLower())
{
result = true;
}
else
{
result = false;
}
}
else
{
result = true;
}
}
#endregion
#region No Session Check Cookie
else
{
if (clsDefault.CookieChecker(_sessionName, out strCookie))
{
strCookie = Decrypt(strCookie);
#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.UID=" + _parameterChar + "UID ");
strSQL.Append("AND A.StatusFlag='A'");
#endregion
dt = clsSQL.Bind(
strSQL.ToString(),
new string[, ] {
{ "" + _parameterChar + "UID", strCookie }
}
);
if (dt != null && dt.Rows.Count > 0)
{
#region FoundData
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()
}
);
}
result = 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()
}
);
}
result = true;
}
#endregion
}
else
{
LoginDelete();
}
}
}
#endregion
#endregion
return(result);
}
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 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;
}
