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 }