Hello. I have this interface
Dropbox - PUBLIC PHOTOS FOR FORUMS[
^]
I created this in order for our HR Staff to filter employees and generate report according to their requirement (e.g All Faculty in Middle School, all american teachers, etc). I used to call this Flexible Employee Filtering
However, Do you have any suggestion on how to make this work without any convenience especially when using OR in WHERE clause? This is one of my dilemma
This is my code so far
public void downloadOutput()
{
List<OrderFields> listOrderBy = OrderBy();
List<string> listFields = constructFieldNames();
List<string> listCondition = constructCondition();
string strFieldsToShow = string.Join(",", listFields);
string strCondition = string.Join(" ", listCondition);
string strOrderByFields = "";
var newOrderByFields = new List<OrderFields>();
newOrderByFields = listOrderBy;
for (int i = 0; i <= listOrderBy.Count - 1; i++)
{
strOrderByFields += newOrderByFields[i].OrderField.ToString()+",";
}
strOrderByFields = strOrderByFields.Trim(',');
string finalquery = "Select "+strFieldsToShow+" FROM "+
"EmploymentInfo LEFT JOIN EmpPersonalInfo "+
"ON EmploymentInfo.EmployeeNo=EmpPersonalInfo.EmployeeNo "+
"LEFT JOIN tblEmployeeMainInfo ON EmpPersonalInfo.EmployeeNo = tblEmployeeMainInfo.EmployeeCode "+
"LEFT JOIN EmploymentHistory ON EmpPersonalInfo.EmployeeNo = EmploymentHistory.EmployeeNo "+
"LEFT JOIN viewEmployeeApprover ON EmpPersonalInfo.EmployeeNo = viewEmployeeApprover.EmployeeNo "+
"LEFT JOIN EmployeePayType ON EmpPersonalInfo.EmployeeNo = EmployeePayType.EmployeeCode";
if (strCondition.Trim() != "")
{
finalquery += " where " + strCondition;
}
if (strOrderByFields.Trim() != "")
{
finalquery += " order by " + strOrderByFields;
}
using (SqlConnection con = new SqlConnection(DAO.ConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(finalquery, con))
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
exportToExcel(dt);
}
}
}
And these are the output of the strings.
strFieldsToShow = "EmpPersonalInfo.EmployeeNo, EmpPersonalInfo.LastName, EmpPersonalInfo.FirstName, tblEmployeeMainInfo.BranchCode"
strCondition = "EmploymentHistory.Status='TRUE' AND tblEmployeeMainInfo.LevelCode like 'FACULTY%'"
strOrderByFields="EmpPersonalInfo.Lastname,EmpPersonalInfo.FirstName,EmpPersonalInfo.BranchCode,EmpPersonalInfo.EmployeeNo"
Please note that TABLE NAMES are values of dropdownlist which I created in other class.
What I have tried:
I have tried the codes above, it is working except for when user selects OR for the where clause.