private void DoctorBuilderDeptOneLine()
{
#region Variable
StringBuilder strSQL = new StringBuilder();
DataTable dt = new DataTable();
#endregion
#region SQL Query
strSQL.Append("SELECT ");
strSQL.Append("(");
strSQL.Append("SELECT TOP 1 MedicalCenter.UID ");
strSQL.Append("FROM DoctorDepartment ");
strSQL.Append("INNER JOIN MedicalCenter ON DoctorDepartment.DepartmentUID = MedicalCenter.DepartmentUID AND MedicalCenter.LanguageUID = " + (clsLanguage.LanguageCurrent == "th-TH" ? "1" : "2") + " ");
strSQL.Append("WHERE DoctorDepartment.DoctorUID = D.UID ");
strSQL.Append(")MedicalCenterUID,");
strSQL.Append("(");
strSQL.Append("SELECT MC.Name + ' , ' ");
strSQL.Append("FROM DoctorDepartment DD ");
strSQL.Append("INNER JOIN MedicalCenter MC ON DD.DepartmentUID = MC.DepartmentUID ");
if (clsLanguage.LanguageCurrent == "th-TH")
{
strSQL.Append("AND MC.LanguageUID=1 ");
}
else
{
strSQL.Append("AND MC.LanguageUID=2 ");
}
strSQL.Append("WHERE DD.DoctorUID = D.UID FOR XML PATH('')");
strSQL.Append(")Department,");
strSQL.Append("(");
strSQL.Append("SELECT TOP 1 DoctorDepartment.DepartmentUID ");
strSQL.Append("FROM DoctorDepartment ");
strSQL.Append("INNER JOIN MedicalCenter ON DoctorDepartment.DepartmentUID = MedicalCenter.DepartmentUID AND MedicalCenter.LanguageUID = " + (clsLanguage.LanguageCurrent == "th-TH" ? "1" : "2") + " ");
strSQL.Append("WHERE DoctorDepartment.DoctorUID = D.UID ");
strSQL.Append(")DepartmentUID,");
strSQL.Append("D.UID,D.Photo,");
#region Language Select
if (clsLanguage.LanguageCurrent == "th-TH")
{
strSQL.Append("D.PNameTH+' '+D.FNameTH+' '+D.LNameTH Name1,");
strSQL.Append("D.FNameEN+' '+D.LNameEN+', '+D.PNameEN Name2,");
strSQL.Append("D.SpecialtyTH Specialty,");
strSQL.Append("D.EducationTH Education,");
strSQL.Append("D.ExperianceTH Experiance,");
strSQL.Append("D.TypeTH Type,");
}
else
{
strSQL.Append("D.PNameTH+' '+D.FNameTH+' '+D.LNameTH Name2,");
strSQL.Append("D.FNameEN+' '+D.LNameEN+', '+D.PNameEN Name1,");
strSQL.Append("D.SpecialtyEN Specialty,");
strSQL.Append("D.EducationEN Education,");
strSQL.Append("D.ExperianceEN Experiance,");
strSQL.Append("D.TypeEN Type,");
}
#endregion
strSQL.Append("D.MedID,D.Phone,D.EMail ");
strSQL.Append("FROM Doctor D ");
strSQL.Append("WHERE D.Active = '1' ");
#region SearchBuilder
#region Name
if (txtSearchName.Text.Trim() != "")
{
if (clsLanguage.LanguageCurrent == "th-TH")
{
strSQL.Append("AND (");
strSQL.Append("D.FNameTH LIKE '%" + clsSQL.CodeFilter(txtSearchName.Text) + "%' ");
strSQL.Append("OR D.LNameTH LIKE '%" + clsSQL.CodeFilter(txtSearchName.Text) + "%'");
strSQL.Append(") ");
}
else
{
strSQL.Append("AND (");
strSQL.Append("D.FNameEN LIKE '%" + clsSQL.CodeFilter(txtSearchName.Text) + "%' ");
strSQL.Append("OR D.LNameEN LIKE '%" + clsSQL.CodeFilter(txtSearchName.Text) + "%'");
strSQL.Append(") ");
}
}
#endregion
#region Specialty
if (ddlSearchSpecialty.SelectedItem.Value != "null")
{
strSQL.Append("AND (");
strSQL.Append("SpecialtyTH LIKE '%" + ddlSearchSpecialty.SelectedItem.Text + "%' ");
strSQL.Append("OR SpecialtyEN LIKE '%" + ddlSearchSpecialty.SelectedItem.Text + "%' ");
strSQL.Append(") ");
}
#endregion
#region MedicalCenter
if (ddlSearchMedicalCenter.SelectedItem.Value != "null")
{
strSQL.Append("AND (");
strSQL.Append("SELECT COUNT(MedicalCenter.UID) ");
strSQL.Append("FROM DoctorDepartment ");
strSQL.Append("INNER JOIN MedicalCenter ON DoctorDepartment.DepartmentUID = MedicalCenter.DepartmentUID AND MedicalCenter.LanguageUID = " + (clsLanguage.LanguageCurrent == "th-TH" ? "1" : "2") + " ");
strSQL.Append("WHERE DoctorDepartment.DoctorUID = D.UID ");
strSQL.Append("AND MC.Name LIKE '%" + ddlSearchMedicalCenter.SelectedItem.Text + "%'");
strSQL.Append(")>0,");
}
#endregion
#region Schedule
#region Checked
bool cbChecked = false;
for (int c = 0; c < cbSearchSchedule.Items.Count; c++)
{
if (cbSearchSchedule.Items[c].Selected)
{
cbChecked = true;
break;
}
}
#endregion
if (cbChecked)
{
strSQL.Append("AND (");
strSQL.Append("SELECT ");
strSQL.Append("COUNT(UID) ");
strSQL.Append("FROM ");
strSQL.Append("DoctorSchedule ");
strSQL.Append("WHERE ");
strSQL.Append("DoctorUID=D.UID ");
//strSQL.Append("AND DATEPART(DW,DoctorSchedule.DateActive)=1");
strSQL.Append("AND " + clsSQL.QueryBuilderWhere(cbSearchSchedule, "DATEPART(DW,DoctorSchedule.DateActive)", "OR", false, true));
strSQL.Append(")>0 ");
}
#endregion
#endregion
strSQL.Append("ORDER BY ");
strSQL.Append("D.FNameTH;");
#endregion
#region Data Builder
dt = clsSQL.Bind(strSQL.ToString(), dbType, cs);
if (dt != null && dt.Rows.Count > 0)
{
gvDoctor.Visible = true;
gvDoctor.DataSource = dt;
gvDoctor.DataBind();
DoctorScheduleBuilder();
}
else
{
gvDoctor.Visible = false;
lblDoctor.Text = (clsLanguage.LanguageCurrent == "th-TH"?clsDefault.AlertMessageColor("ไม่พบข้อมูลแพทย์ที่ต้องการ.", clsDefault.AlertType.Info):clsDefault.AlertMessageColor("Not found data.", clsDefault.AlertType.Info));
}
#endregion
}
