public SqlDataReader showUserComments(UserMasterPage masterPage, int hoodId)
{
newConnection.Open();
string query = string.Format(
@"SELECT
n.name, comment, n.neighbourhoodID, Account.firstName, Account.lastName,
CrimeRate, TransportationRate, HousingRate,
EntertainmentRate, SchoolRate, ShoppingRate,
((crimeRate*2+transportationRate+housingRate*2+entertainmentRate+schoolRate*2+shoppingRate)/9) as overalRate " +
"from Review " +
"Inner join Account on Account.accountID = Review.accountID " +
"Inner join Neighbourhood n on n.neighbourhoodID = Review.neighbourhoodID " +
"WHERE n.neighbourhoodID = " + hoodId +
"and CrimeRate >= '" + masterPage.SliderCrime + "'" +
"and TransportationRate >= '" + masterPage.SliderTransportation + "'" +
"and HousingRate >= '" + masterPage.SliderHousing + "'" +
"and EntertainmentRate >= '" + masterPage.SliderEntertainment + "'" +
"and ShoppingRate >= '" + masterPage.SliderShopping + "'" +
"and SchoolRate >= '" + masterPage.SliderSchools + "' ");
command = new SqlCommand(query, newConnection);
return(command.ExecuteReader());
}
protected void Page_Load(object sender, EventArgs e)
{
List <string> result = new List <string>();
if (Session["USER"] != null)
{
lbl_welcome.Text = $"Welcome, {Session["USER"].ToString()} !!";
}
nc = new Connection();
try
{
UserMasterPage masterPage = Master as UserMasterPage;
result = nc.showTop3Ranking(masterPage);
nc.CloseConnection();
}
finally
{
nc.CloseConnection();
}
//rank1
Latitude1 = Convert.ToDouble(result[9]);
Lngitude1 = Convert.ToDouble(result[10]);
lbl_Title1.Text = result[1];
all_rank1.Text = result[8];
crime1.Text = result[2];
trans1.Text = result[3];
house1.Text = result[4];
entertainment1.Text = result[5];
school1.Text = result[6];
shopping1.Text = result[7];
disc_rank1.Text = result[30];
//rank2
Latitude2 = Convert.ToDouble(result[20]);
Lngitude2 = Convert.ToDouble(result[21]);
lbl_Title2.Text = result[12];
all_rank2.Text = result[19];
crime2.Text = result[13];
trans2.Text = result[14];
house2.Text = result[15];
entertainment2.Text = result[16];
school2.Text = result[17];
shopping2.Text = result[18];
disc_rank2.Text = result[30];
//rank3
Latitude3 = Convert.ToDouble(result[31]);
Lngitude3 = Convert.ToDouble(result[32]);
lbl_Title3.Text = result[23];
all_rank3.Text = result[30];
crime3.Text = result[24];
trans3.Text = result[25];
house3.Text = result[26];
entertainment3.Text = result[27];
school3.Text = result[28];
shopping3.Text = result[29];
disc_rank3.Text = result[30];
}
protected void Page_Load(object sender, EventArgs e)
{
Latitude = Convert.ToDouble(Session["Lat"]);
Lngitude = Convert.ToDouble(Session["Lng"]);
if (Session["hoodId"] != null)
{
Latitude = Convert.ToDouble(Session["Lat"]);
Lngitude = Convert.ToDouble(Session["Lng"]);
newConnection = new Connection();
int hoodIdInt = Convert.ToInt16(Session["hoodId"]);
hood = newConnection.getNeighbourhood2(hoodIdInt);
//DetailsView1.DataSource = newConnection.getNeighbourhood(hoodIdInt);
//DetailsView1.DataBind();
//newConnection.CloseConnection();
//hood = new Neighbourhood(); try later move entity objects using Session
//lbl_Title.Text = Session["hoodIdReview"].ToString();
lbl_Title.Text = hood.Name.ToString();
lblCrime.Text = hood.OveralCrimeRate.ToString();
lbl_rate.Text = hood.OveralRate.ToString();
lblHouse.Text = hood.OveralHousingRate.ToString();
lblTrans.Text = hood.OveralTransportationRate.ToString();
lblEnter.Text = hood.OveralEntertainmentRate.ToString();
lblShopping.Text = hood.OveralShoppingRate.ToString();
lblSchool.Text = hood.OveralSchoolRate.ToString();
lbl_description.Text = hood.Description.ToString();
Session["hoodName"] = lbl_Title.Text;
//adding user comments
try
{
UserMasterPage masterPage = Master as UserMasterPage;
rptRate.DataSource = newConnection.showUserComments(masterPage, hoodIdInt);
rptRate.DataBind();
newConnection.CloseConnection();
}
finally
{
newConnection.CloseConnection();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
newConnection = new Connection();
try
{
UserMasterPage masterPage = Master as UserMasterPage;
userListRepeaterAdminReviewPage.DataSource = newConnection.showReviewList();
userListRepeaterAdminReviewPage.DataBind();
}
catch (Exception sqlException)
{
throw new Exception(sqlException.ToString());
}
finally
{
newConnection.CloseConnection();
}
}
//public SqlDataReader ShowRanking()
//{
// newConnection.Open();
// string query = string.Format(@"SELECT name, convert(decimal(5,2),overallCrimeRate) as CrimeRate, convert(decimal(5,2),overallTransportationRate) as TransportationRate," +
// "convert(decimal(5,2),overallHousingRate) as HousingRate,convert(decimal(5,2),overallEntertainmentRate) as EntertainmentRate,convert(decimal(5,2),overallSchoolRate) as SchoolRate," +
// "convert(decimal(5,2),overallShoppingRate) AS ShoppingRate," +
// "convert(decimal(5,2),overallRate) as overallRate FROM NEIGHBOURHOOD ORDER BY OVERALLRATE DESC");
// command = new SqlCommand(query, newConnection);
// return command.ExecuteReader();
//}
public SqlDataReader showRanking2(UserMasterPage masterPage)
{
newConnection.Open();
string query = string.Format(@"SELECT neighbourhoodId, name, convert(decimal(5,2),overallCrimeRate) as CrimeRate, convert(decimal(5,2),overallTransportationRate) as TransportationRate," +
"convert(decimal(5,2),overallHousingRate) as HousingRate,convert(decimal(5,2),overallEntertainmentRate) as EntertainmentRate,convert(decimal(5,2),overallSchoolRate) as SchoolRate," +
"convert(decimal(5,2),overallShoppingRate) as ShoppingRate," +
"convert(decimal(5,2),overallRate) as overallRate, latitude, longitude FROM HOODCOORD " +
"WHERE overallCrimeRate >= '" + masterPage.SliderCrime + "'" +
"and overallTransportationRate >= '" + masterPage.SliderTransportation + "'" +
"and overallHousingRate >= '" + masterPage.SliderHousing + "'" +
"and overallEntertainmentRate >= '" + masterPage.SliderEntertainment + "'" +
"and overallShoppingRate >= '" + masterPage.SliderShopping + "'" +
"and overallSchoolRate >= '" + masterPage.SliderSchools + "' " +
"ORDER BY OVERALLRATE DESC");
command = new SqlCommand(query, newConnection);
return(command.ExecuteReader());
}
protected void Page_Load(object sender, EventArgs e)
{
Lat = 43.65F;
Lng = -79.38F;
Session["hoodId"] = null;
nc = new Connection();
try
{
UserMasterPage masterPage = Master as UserMasterPage;
neighborhoodRepeater.DataSource = nc.showRanking2(masterPage);
neighborhoodRepeater.DataBind();
nc.CloseConnection();
}
finally
{
nc.CloseConnection();
}
}
//SqlConnection connection = new SqlConnection("Data Source=LAOGUNAGXPS\\LAOGUANGSQL;Initial Catalog=HoodTo;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
{
Session["PAGETOGO"] = null;
Session["PAGE"] = "UserProfile";
//Session["USER"] = "******";
if (Session["USER"] == null)
{
ClientScript.RegisterStartupScript(this.GetType(), "Check if user login",
"alert('Please LOG IN');window.location ='LoginPage.aspx';", true);
}
else
{
lblWelcome.Text = $"Welcome, {Session["USER"].ToString()} ";
}
try
{
UserMasterPage masterPage = Master as UserMasterPage;
query = string.Format(
@"SELECT
n.name, comment, n.neighbourhoodID,
CrimeRate, TransportationRate, HousingRate,
EntertainmentRate, SchoolRate, ShoppingRate " +
"from Review " +
"Inner join Account on Account.accountID = Review.accountID " +
"Inner join Neighbourhood n on n.neighbourhoodID = Review.neighbourhoodID " +
"WHERE CrimeRate > '" + masterPage.SliderCrime + "'" +
"and TransportationRate > '" + masterPage.SliderTransportation + "'" +
"and HousingRate > '" + masterPage.SliderHousing + "'" +
"and EntertainmentRate > '" + masterPage.SliderEntertainment + "'" +
"and ShoppingRate > '" + masterPage.SliderShopping + "'" +
"and SchoolRate > '" + masterPage.SliderSchools + "'" +
"and Account.email = '" + Session["USER"].ToString() + "'");
connection.Open();
command = new SqlCommand(query, connection);
rptRate.DataSource = command.ExecuteReader();
rptRate.DataBind();
connection.Close();
query = string.Format(@"SELECT admin from account where email = '" + Session["USER"].ToString() + "'");
command = new SqlCommand(query, connection);
connection.Open();
string isAdmin = (string)command.ExecuteScalar();
command.ExecuteReader().Close();
connection.Close();
try
{
if (isAdmin.ToLower() == "y")
{
tbl1.Enabled = true;
tbl1.Visible = true;
}
}
catch (Exception)
{
var message = new JavaScriptSerializer().Serialize("Cannot find the user");
var script = string.Format("alert({0});", message);
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", script, true);
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
connection.Close();
}
finally
{
connection.Close();
}
}
public List <string> showTop3Ranking(UserMasterPage masterPage)
{
List <string> rankingInfo = new List <string>();
newConnection.Open();
string query = string.Format(@"SELECT TOP 3 neighbourhoodId, name, convert(decimal(5,2),overallCrimeRate) as CrimeRate, convert(decimal(5,2),overallTransportationRate) as TransportationRate," +
"convert(decimal(5,2),overallHousingRate) as HousingRate,convert(decimal(5,2),overallEntertainmentRate) as EntertainmentRate,convert(decimal(5,2),overallSchoolRate) as SchoolRate," +
"convert(decimal(5,2),overallShoppingRate) as ShoppingRate," +
"convert(decimal(5,2),overallRate) as overallRate, latitude, longitude FROM HOODCOORD " +
"WHERE overallCrimeRate > '" + masterPage.SliderCrime + "'" +
"and overallTransportationRate > '" + masterPage.SliderTransportation + "'" +
"and overallHousingRate > '" + masterPage.SliderHousing + "'" +
"and overallEntertainmentRate > '" + masterPage.SliderEntertainment + "'" +
"and overallShoppingRate > '" + masterPage.SliderShopping + "'" +
"and overallSchoolRate > '" + masterPage.SliderSchools + "' " +
"ORDER BY OVERALLRATE DESC");
command = new SqlCommand(query, newConnection);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
rankingInfo.Add(reader.GetValue(0).ToString());
rankingInfo.Add(reader.GetValue(1).ToString());
rankingInfo.Add(reader.GetValue(2).ToString());
rankingInfo.Add(reader.GetValue(3).ToString());
rankingInfo.Add(reader.GetValue(4).ToString());
rankingInfo.Add(reader.GetValue(5).ToString());
rankingInfo.Add(reader.GetValue(6).ToString());
rankingInfo.Add(reader.GetValue(7).ToString());
rankingInfo.Add(reader.GetValue(8).ToString());
rankingInfo.Add(reader.GetValue(9).ToString());
rankingInfo.Add(reader.GetValue(10).ToString());
}
reader.Close();
newConnection.Close();
}
else
{
throw new Exception();
}
//newConnection.Open();
//query = $"Select description from Neighbourhood where neighbourhoodID = '{rankingInfo[0]}' or neighbourhoodID = '{rankingInfo[11]}' or neighbourhoodID = '{rankingInfo[22]}'";
//command = new SqlCommand(query, newConnection);
//reader = command.ExecuteReader();
//if (reader.HasRows)
//{
// while (reader.Read())
// {
// rankingInfo.Add(reader.GetValue(0).ToString());
// }
//}
//else
//{
// throw new Exception();
//}
return(rankingInfo);
}
