//Advanced user search
protected void SearchUserBtn_Click(object sender, EventArgs e)
{
String empID = SearchEmployeeIDTextBox.Text.Trim();
String firstname = SearchFirstNameTextBox.Text.Trim();
String lastname = SearchLastNameTextBox.Text.Trim();
String email = SearchEmailTextBox.Text.Trim();
String contactNo = SearchContactTextBox.Text.Trim();
//String username = SearchUsernameTextBox.Text.Trim();
String resultMessage = "";
String query = "SELECT * FROM Employee WHERE";
if (empID != "")
{
query += " empID='" + empID + "'";
resultMessage += empID + ", ";
}
if (firstname != "")
{
query += " AND firstname like '" + firstname + "%'";
resultMessage += firstname + ", ";
}
if (lastname != "")
{
query += " AND lastname like '" + lastname + "%'";
resultMessage += lastname + ", ";
}
if (email != "")
{
query += " AND email like '" + email + "%'";
resultMessage += email + ", ";
}
if (contactNo != "")
{
query += " AND contactNo like '" + contactNo + "%'";
resultMessage += contactNo + ", ";
}
// Clearing the grid view
UserSearchGridView.DataSource = null;
UserSearchGridView.DataBind();
query = query.Replace("WHERE AND", "WHERE ");
//Response.Write(query + "<br>");
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SystemUserConnectionString"].ConnectionString); //database connectivity
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null && reader.HasRows) //if search results found
{
DataTable dt = new DataTable();
dt.Load(reader);
UserSearchGridView.DataSource = dt; //display found data in grid view
UserSearchGridView.DataBind();
responseBoxGreen.Style.Add("display", "block");
responseMsgGreen.InnerHtml = "Search Results Found for <strong>" + resultMessage + "</strong>";
}
else
{
responseBoxRed.Style.Add("display", "block");
responseMsgRed.InnerHtml = "No Results Found for <strong>" + resultMessage + "</strong>";
}
conn.Close();
//expanding block
AdvancedUserSearchContent.Style.Add("display", "block");
//updating expandingItems dictionary in javascript
ClientScript.RegisterStartupScript(this.GetType(), "setExpandingItem", "setExpandingItem('AdvancedUserSearchContent');", true);
}
catch (Exception ex)
{
responseBoxRed.Style.Add("display", "block");
responseMsgRed.InnerHtml = "There were some issues with the database. Please try again later.";
Response.Write(ex.ToString());
}
}
//Advanced user search
protected void SearchUserBtn_Click(object sender, EventArgs e)
{
String costID = SearchUserCostNameDropDown.SelectedValue;
String firstname = SearchFirstNameTextBox.Text.Trim();
String lastname = SearchLastNameTextBox.Text.Trim();
String email = SearchEmailTextBox.Text.Trim();
String contactNo = SearchContactTextBox.Text.Trim();
String resultMessage = "";
String query = "SELECT E.empID AS Employee_ID, C.name AS Cost_Center, (E.firstName+' '+E.lastName) AS Name, E.contactNo AS Contact, E.email AS Email " +
"FROM Employee E " +
"INNER JOIN CostCenter C ON E.costID=C.costID " +
"WHERE E.status=1";
if (costID != "")
{
query += " AND E.costID='" + costID + "'";
resultMessage += "Cost Center <strong>" + SearchUserCostNameDropDown.SelectedItem + "</strong>, ";
}
if (firstname != "")
{
query += " AND firstname like '" + firstname + "%'";
resultMessage += "First Name <strong>" + firstname + "</strong>, ";
}
if (lastname != "")
{
query += " AND lastname like '" + lastname + "%'";
resultMessage += "Last Name <strong>" + lastname + "</strong>, ";
}
if (email != "")
{
query += " AND email like '" + email + "%'";
resultMessage += "Email <strong>" + email + "</strong>, ";
}
if (contactNo != "")
{
query += " AND contactNo like '" + contactNo + "%'";
resultMessage += "Contact <strong>" + contactNo + "</strong>, ";
}
// Clearing the grid view
UserSearchGridView.DataSource = null;
UserSearchGridView.DataBind();
//Remove unnessary 'and'
query = query.Replace("WHERE AND", "WHERE ");
//Remove result message last comma
resultMessage = resultMessage.Substring(0, resultMessage.Length - 2);
//Response.Write(query + "<br>");
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SystemUserConnectionString"].ConnectionString); //database connectivity
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null && reader.HasRows) //if search results found
{
DataTable dt = new DataTable();
dt.Load(reader);
UserSearchGridView.DataSource = dt; //display found data in grid view
UserSearchGridView.DataBind();
responseBoxGreen.Style.Add("display", "block");
responseMsgGreen.InnerHtml = "Search results found for " + resultMessage;
}
else
{
responseBoxRed.Style.Add("display", "block");
responseMsgRed.InnerHtml = "No results found for " + resultMessage;
}
conn.Close();
//expanding block
AdvancedUserSearchContent.Style.Add("display", "block");
//updating expandingItems dictionary in javascript
ClientScript.RegisterStartupScript(this.GetType(), "setExpandingItem", "setExpandingItem('AdvancedUserSearchContent');", true);
}
catch (Exception ex)
{
responseBoxRed.Style.Add("display", "block");
responseMsgRed.InnerHtml = "There were some issues with the database. Please try again later.";
Response.Write(ex.ToString());
}
}
