Exemplo n.º 1
0
 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();
     }
 }
Exemplo n.º 2
0
        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();
            }
        }
Exemplo n.º 3
0
        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();
            }
        }