/// <summary>
/// Searches the database by City.
/// </summary>
private void SearchByCity()
{
var query = from p in restaurantDB.RESTAURANTS
where p.city == MultiSearchTextBox.Text
select p;
SearchResultsGridView.DataSource = query;
SearchResultsGridView.DataBind();
}
/// <summary>
/// Searches the database by Name and also returns partial matches.
/// </summary>
private void SearchByName()
{
var query = from p in restaurantDB.RESTAURANTS
where p.name.Contains(MultiSearchTextBox.Text)
select p;
SearchResultsGridView.DataSource = query;
SearchResultsGridView.DataBind();
}
private void PopulateGridView()
{
InventoryPurchasingController controller = new InventoryPurchasingController();
int searchId = int.Parse(CategoryDropDown.SelectedValue);
List <Product> data = controller.GetProductsByCategory(searchId);
SearchResultsGridView.DataSource = data;
SearchResultsGridView.DataBind();
}
protected void AddToCart(object sender, EventArgs e)
{
SearchResultsGridView.DataBind();
int counter = 0;
DataTable dt = new DataTable();
for (int i = 1; i < SearchResultsGridView.Columns.Count; i++)
{
if (i == 1)
{
dt.Columns.Add("Author");
}
if (i == 2)
{
dt.Columns.Add("Title");
}
if (i == 3)
{
dt.Columns.Add("Price");
}
}
foreach (GridViewRow row in SearchResultsGridView.Rows)
{
DataRow dr = dt.NewRow();
for (int j = 1; j < SearchResultsGridView.Columns.Count; j++)
{
if (j == 1)
{
dr["Author"] = row.Cells[j].Text;
}
if (j == 2)
{
dr["Title"] = row.Cells[j].Text;
}
if (j == 3)
{
dr["Price"] = row.Cells[j].Text;
}
}
dt.Rows.Add(dr);
}
SearchResultsGridView.DataSource = dt;
SearchResultsGridView.DataBind();
}
/// <summary>
/// Searches the database by Zip Code
/// </summary>
private void SearchByZipCode()
{
try
{
int zip = Convert.ToInt32(MultiSearchTextBox.Text);
var query = from p in restaurantDB.RESTAURANTS
where p.zip == zip
select p;
SearchResultsGridView.DataSource = query;
SearchResultsGridView.DataBind();
}
catch (System.FormatException fe)
{
fe.ToString();
}
}
/// <summary>
/// Searches the database by Store ID.
/// </summary>
private void SearchByStoreID()
{
try
{
int id = Convert.ToInt32(MultiSearchTextBox.Text);
cwilli46Entities restaurantDB = new cwilli46Entities();
var query = from p in restaurantDB.RESTAURANTS
where p.storeid == id
select p;
SearchResultsGridView.DataSource = query;
SearchResultsGridView.DataBind();
}
catch (System.FormatException fe)
{
fe.ToString();
}
}
protected void DeleteActivity_Click(object sender, EventArgs e)
{
int activityid;
if (int.TryParse(CurrentActivityID.Text, out activityid))
{
try
{
var controller = new ClubActivityController();
controller.DeleteClubActivity(activityid);
ShowMessage("Club Activity has been deleted", STYLE_SUCCESS);
ResetForm_Click(sender, e);
SearchResultsGridView.DataBind();
}
catch (Exception ex)
{
ShowMessage(ex.Message, STYLE_INFO);
}
}
}
protected void SearchButton_Click(object sender, EventArgs e)
{
decimal maxSalary = 0;
decimal.TryParse(SalaryTextBox.Text, out maxSalary);
using (TpsDbContext tps = new TpsDbContext()) {
var staff = tps.Users.Where(u => u.SecurityRole == "Staff");
if (JobCategoryDropDownList.SelectedValue != "Any")
{
staff = staff.Where(u => u.DesiredJobCategory == JobCategoryDropDownList.SelectedValue);
}
if (maxSalary > 0)
{
staff = staff.Where(u => u.DesiredSalary <= maxSalary);
}
if (!string.IsNullOrWhiteSpace(CityTextBox.Text))
{
staff = staff.Where(u => u.City.ToUpper() == CityTextBox.Text.ToUpper());
}
if (!string.IsNullOrWhiteSpace(StateTextBox.Text))
{
staff = staff.Where(u => u.State.ToUpper() == StateTextBox.Text.ToUpper());
}
if (RelocateCheckBox.Checked)
{
staff = staff.Where(u => u.CanRelocate == true);
}
var staffList = staff.ToList();
staffList.RemoveAll(s => {
// This is so horrible, but just go with it
if (EducationLevelDropDownList.SelectedValue == "High School")
{
return(false);
}
if (EducationLevelDropDownList.SelectedValue == "Some College" && s.EducationLevel != "High School")
{
return(false);
}
if (EducationLevelDropDownList.SelectedValue == "Undergraduate" &&
(s.EducationLevel != "Some College" && s.EducationLevel != "High School"))
{
return(false);
}
if (EducationLevelDropDownList.SelectedValue == "Graduate" && s.EducationLevel == "Graduate")
{
return(false);
}
return(true);
});
SearchResultsGridView.DataSource = staffList;
SearchResultsGridView.DataBind();
}
}
protected void proceedToCartButton_Click(object sender, EventArgs e)
{
SearchResultsGridView.DataBind();
DataTable dt = new DataTable();
for (int i = 1; i < SearchResultsGridView.Columns.Count; i++)
{
if (i == 1)
{
dt.Columns.Add("Author");
}
if (i == 2)
{
dt.Columns.Add("Title");
}
if (i == 3)
{
dt.Columns.Add("Price");
}
}
foreach (GridViewRow row in SearchResultsGridView.Rows)
{
DataRow dr = dt.NewRow();
for (int j = 1; j < SearchResultsGridView.Columns.Count; j++)
{
if (j == 1)
{
dr["Author"] = row.Cells[j].Text;
}
if (j == 2)
{
dr["Title"] = row.Cells[j].Text;
}
if (j == 3)
{
dr["Price"] = row.Cells[j].Text;
}
}
dt.Rows.Add(dr);
Session["EmptySetForCart"] = dt;
}
Response.Redirect("ShoppingCart.aspx");
//Get the GridView Row.
//GridViewRow row = SearchResultsGridView.Rows[0];
////Get the HobbyId from the DataKey property.
//int bookID = 1;
////Get the checked value of the CheckBoxField column.
//bool isSelected = (row.Cells[0].Controls[1] as CheckBox).Checked;
//string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
//using (SqlConnection con = new SqlConnection(constr))
//{
// using (SqlCommand cmd = new SqlCommand())
// {
// cmd.CommandText = "UPDATE hobbies SET [IsSelected] = @IsSelected WHERE HobbyId=@HobbyId";
// cmd.Connection = con;
// con.Open();
// cmd.Parameters.AddWithValue("@HobbyId", bookID);
// cmd.Parameters.AddWithValue("@IsSelected", isSelected);
// cmd.ExecuteNonQuery();
// con.Close();
// }
//}
//SearchResultsGridView.EditIndex = -1;
//SearchResultsGridView.DataBind();
//LabeltEST.Text = counter.ToString();
}
protected void Page_Load(object sender, EventArgs e)
{
//var list = SearchResultsGridView.SelectedRow.Controls;
// Gets the search string from the home search text box
string searchString = (string)Session["SearchString"];
// components for a connection string to an Access 2016 named 'SampleDB.accdb'
string provider = "Microsoft.ACE.OLEDB.12.0";
// the data source for Access database if it's placed on the local device
string dataSource = "C:\\Users\\Joshua P\\source\\repos\\InterDimensionalCable\\InterDimensionalCable\\DataAccessLayer\\A2ZBooks.accdb";
// the data source for Access database if it's placed in the 'App_Data' folder of the
// current project when run within Citrix
//string dataSource = "\\\\itfs1\\wpcarey\\StudentHomeFolders\\kroumina\\Documents\\Visual Studio 2017\\Projects\\PracticeDataConnectivity_Sln\\PracticeDataConnectivity\\App_Data\\SampleDB.accdb";
string dbConnectionString = string.Format("Provider={0};Data Source={1};", provider, dataSource);
OleDbConnection myConn = new OleDbConnection(dbConnectionString);
myConn.Open();
//string query = "SELECT * FROM book, course, bookcoursebridge WHERE bookID = bookcoursebridge.BookID" +
// " AND bookcoursebridge.CourseID = course.ID";
// the SampleDB Access database has a table named 'practice'
string query;
if (searchString == "CIS 235")
{
query = $"SELECT DISTINCT Author, Title, Price FROM book, course, bookcoursebridge WHERE book.ID = bookcoursebridge.BookID" +
" AND bookcoursebridge.CourseID = course.ID AND " +
$" CODE = '{searchString}';";
}
else
{
query = $"SELECT DISTINCT Author, Title, Price FROM book, course, bookcoursebridge WHERE book.ID = bookcoursebridge.BookID" +
" AND bookcoursebridge.CourseID = course.ID AND " +
$" Author = '{searchString}' OR" +
$" Title = '{searchString}' OR" +
$" ISBN = '{searchString}' OR" +
$" CODE = '{searchString}';";
}
//string query = $"SELECT * FROM book, course, bookcoursebridge WHERE bookID = bookcoursebridge.BookID AND bookcoursebridge.CourseID = course.ID";
//OleDbCommand cmd = new OleDbCommand(query, myConn);
//var reader = cmd.ExecuteReader();
//lblMyData.Text = String.Empty;
//while (reader.Read())
//{
// var author = reader["Author"];
// var title = reader["Title"];
// var price = reader["Price"];
// Label1.Text = $"{author} {title} {isbn} {code}";
// Do something with the retrieved values
// lblMyData.Text += "Semester selected is " + semesterName.ToString()
// + " in the year " + yearOffered.ToString()
// + ".<br>";
//}
string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
DataTable dt = new DataTable();
using (OleDbConnection con = new OleDbConnection(strConnString))
{
OleDbCommand cmd = new OleDbCommand(query);
using (OleDbDataAdapter sda = new OleDbDataAdapter())
{
cmd.Connection = con;
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
SearchResultsGridView.DataSource = dt;
SearchResultsGridView.DataBind();
Session["dataSource"] = dt;
}
}
//reader.Close();
//cmd.CommandText = "Insert into practice (semester, yearoffered) values (@semester, @year);";
//cmd.Parameters.AddWithValue("@semester", "Summer");
//cmd.Parameters.AddWithValue("@year", "2020");
//cmd.ExecuteNonQuery();
myConn.Close();
}
private void PopulateGridView(List <ClubActivity> data)
{
SearchResultsGridView.DataSource = data;
SearchResultsGridView.DataBind();
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string start = "SELECT * FROM [Users] JOIN Answers ON [Users].userId = Answers.userId WHERE [Users].userId IN((SELECT userId FROM Answers WHERE";
string end = "))";
optIDs = "";
//For each loops to check whether items has been selected line 185 to 311
foreach (ListItem checkbox in CheckBoxListGender.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListInputState.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListBank.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListBankServicesCommbank.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListBankServicesNAB.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListBankServicesANZ.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListNewspaper.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListSports.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListTravel.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
//Where admin selects, it will do these queries
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
if (optIDs != "")
{
StringBuilder sbCommand = new
StringBuilder(start + optIDs);
if (inputFirstname.Value.Trim() != "")
{
sbCommand.Append(" AND firstName=@firstName");
SqlParameter param = new SqlParameter("@firstName", inputFirstname.Value);
command.Parameters.Add(param);
}
if (inputLastname.Value.Trim() != "")
{
sbCommand.Append(" AND lastName=@lastName");
SqlParameter param = new SqlParameter("@lastName", inputLastname.Value);
command.Parameters.Add(param);
}
if (inputPostcode.Value.Trim() != "")
{
sbCommand.Append(" AND answerText=@Postcode");
SqlParameter param = new SqlParameter("@Postcode", inputPostcode.Value);
command.Parameters.Add(param);
}
if (inputSuburb.Value.Trim() != "")
{
sbCommand.Append(" AND answerText=@Suburb");
SqlParameter param = new SqlParameter("@Suburb", inputSuburb.Value);
command.Parameters.Add(param);
}
sbCommand.Append(end);
command.CommandText = sbCommand.ToString();
command.CommandType = CommandType.Text;
SqlDataReader rdr = command.ExecuteReader();
SearchResultsGridView.DataSource = rdr;
SearchResultsGridView.DataBind();
}
else
{
StringBuilder sbCommand = new
StringBuilder("SELECT * FROM [Users] JOIN Answers ON [Users].userId = Answers.userId");
if (inputFirstname.Value.Trim() != "")
{
sbCommand.Append(" AND firstName=@firstName");
SqlParameter param = new SqlParameter("@firstName", inputFirstname.Value);
command.Parameters.Add(param);
}
if (inputLastname.Value.Trim() != "")
{
sbCommand.Append(" AND lastName=@lastName");
SqlParameter param = new SqlParameter("@lastName", inputLastname.Value);
command.Parameters.Add(param);
}
if (inputPostcode.Value.Trim() != "")
{
sbCommand.Append(" AND answerText=@Postcode");
SqlParameter param = new SqlParameter("@Postcode", inputPostcode.Value);
command.Parameters.Add(param);
}
if (inputSuburb.Value.Trim() != "")
{
sbCommand.Append(" AND answerText=@Suburb");
SqlParameter param = new SqlParameter("@Suburb", inputSuburb.Value);
command.Parameters.Add(param);
}
command.CommandText = sbCommand.ToString();
command.CommandType = CommandType.Text;
SqlDataReader rdr = command.ExecuteReader();
SearchResultsGridView.DataSource = rdr;
SearchResultsGridView.DataBind();
}
}
}
