public void SearchResults2(object state, object city)
{
// Console.WriteLine(state);
// Console.WriteLine("\n");
// Console.WriteLine(city);
Search_Results.Rows.Clear();
Search_Results.Refresh();
using (var conn = new NpgsqlConnection(buildConnString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT name,full_addr,num_checkins,review_count FROM business where state='" + state + "' AND city='" + city + "'ORDER BY business";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var add = reader.GetString(1);
var bname = reader.GetString(0);
object numTips, numCheck;
if (reader.IsDBNull(2))
{
numCheck = 0;
}
else
{
numCheck = reader.GetValue(2);
}
if (reader.IsDBNull(3))
{
numTips = 0;
}
else
{
numTips = reader.GetValue(3);
}
Search_Results.Rows.Add(new object[] { bname, add, numTips, numCheck });
}
}
}
conn.Close();
}
}
public void refineSearchHour()
{
// Console.WriteLine(state);
// Console.WriteLine("\n");
// Console.WriteLine(city);
Search_Results.Rows.Clear();
Search_Results.Refresh();
string day;
if (Day_Box.SelectedItem.ToString() == "Sunday")
{
day = "sun";
}
else if (Day_Box.SelectedItem.ToString() == "Monday")
{
day = "mon";
}
else if (Day_Box.SelectedItem.ToString() == "Tuesday")
{
day = "tue";
}
else if (Day_Box.SelectedItem.ToString() == "Wednesday")
{
day = "wed";
}
else if (Day_Box.SelectedItem.ToString() == "Thursday")
{
day = "thur";
}
else if (Day_Box.SelectedItem.ToString() == "Friday")
{
day = "fri";
}
else
{
day = "sat";
}
string opHour = From_Box.SelectedItem.ToString();
string clHour = To_Box.SelectedItem.ToString();
using (var conn = new NpgsqlConnection(buildConnString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "Select name,full_addr,review_count,num_checkins from business JOIN hours on business.bid=hours.bid where " + day + "open='\"" + opHour + "\"' and " + day + "close ='\"" + clHour + "\"'";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var add = reader.GetString(1);
var bname = reader.GetString(0);
object numTips, numCheck;
if (reader.IsDBNull(2))
{
numCheck = 0;
}
else
{
numCheck = reader.GetValue(2);
}
if (reader.IsDBNull(3))
{
numTips = 0;
}
else
{
numTips = reader.GetValue(3);
}
Search_Results.Rows.Add(new object[] { bname, add, numTips, numCheck });
}
}
}
conn.Close();
}
}
public void fillSearchResultsCategory(object state, object city, object zip)
{
Search_Results.Rows.Clear();
Search_Results.Refresh();
using (var conn = new NpgsqlConnection(buildConnString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
/*cmd.CommandText = "Select name,full_addr,num_checkins,review_count From business Join Categories On Business.bid=Categories.bid where state='" + state + "' AND city='" + city + "' AND zip='" + zip + "'";
* if (Categories_Checklist.CheckedItems.Count > 0)
* {
* cmd.CommandText = cmd.CommandText + "and ( ";
* for (int i = 0; i < Categories_Checklist.CheckedItems.Count; i++)
* {
* cmd.CommandText = cmd.CommandText + " category='" + Categories_Checklist.CheckedItems[i].ToString() + "'";
* if (i + 1 < Categories_Checklist.CheckedItems.Count)
* cmd.CommandText = cmd.CommandText + " and ";
* }
* cmd.CommandText = cmd.CommandText + ") ";
* }
* cmd.CommandText = cmd.CommandText + " ORDER BY business";*/
cmd.CommandText = "Select b1.name,b1.full_addr,b1.num_checkins,b1.review_count From (select bid from business where business.state='" +
state +
"' and business.city='" +
city +
"' and business.zip='" +
zip +
"') bidPick, business b1";
for (int i = 0; i < Categories_Checklist.CheckedItems.Count; i++)
{
cmd.CommandText = cmd.CommandText + ",categories c" + i.ToString();
}
cmd.CommandText = cmd.CommandText + " where";
for (int i = 0; i < Categories_Checklist.CheckedItems.Count; i++)
{
cmd.CommandText = cmd.CommandText + " bidPick.bid=c" + i.ToString() + ".bid and c" + i.ToString() + ".category='" + Categories_Checklist.CheckedItems[i].ToString() + "' and";
}
cmd.CommandText = cmd.CommandText + " b1.bid=bidPick.bid";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var add = reader.GetString(1);
var bname = reader.GetString(0);
object numTips, numCheck;
if (reader.IsDBNull(2))
{
numCheck = 0;
}
else
{
numCheck = reader.GetValue(2);
}
if (reader.IsDBNull(3))
{
numTips = 0;
}
else
{
numTips = reader.GetValue(3);
}
Search_Results.Rows.Add(new object[] { bname, add, numTips, numCheck });
}
}
}
conn.Close();
}
}