public IList <SurveyResultVM> GetAllParkNamesWithSurvey()
{
List <SurveyResultVM> parks = new List <SurveyResultVM>();
try
{
// Create a new connection object
using (SqlConnection conn = new SqlConnection(connectionString))
{
// Open the connection
conn.Open();
string sql = @"select survey_result.parkcode, parkname, count(survey_result.parkcode) as Count from survey_result
join park on park.parkcode = survey_result.parkcode group by survey_result.parkCode, parkname order by count desc, parkName";
SqlCommand cmd = new SqlCommand(sql, conn);
// Execute the command
SqlDataReader reader = cmd.ExecuteReader();
// Loop through each row
while (reader.Read())
{
SurveyResultVM vm = RowToObject1(reader);
parks.Add(vm);
}
}
}
catch (SqlException)
{
throw;
}
return(parks);
}
public ActionResult SurveyResult()
{
SurveyResultVM model = new SurveyResultVM();
model.ListAllSurveys = dal.GetAllSurveys();
model.TotalSurveys = dal.SurveyCount();
return(View("SurveyResult", model));
}
private SurveyResultVM MapRowToSurvey(SqlDataReader reader)
{
SurveyResultVM surveyResult = new SurveyResultVM();
surveyResult.SurveyCount = Convert.ToInt32(reader["tally"]);
surveyResult.ParkCode = Convert.ToString(reader["parkCode"]);
surveyResult.ParkName = Convert.ToString(reader["ParkName"]);
return(surveyResult);
}
private SurveyResultVM RowToObject1(SqlDataReader reader)
{
SurveyResultVM vm = new SurveyResultVM()
{
ParkCode = Convert.ToString(reader["parkcode"]),
ParkName = Convert.ToString(reader["parkname"]),
CountOfSurveys = Convert.ToInt32(reader["Count"]),
};
return(vm);
}
public IActionResult Index()
{
//Get a list of parks for the select list in the view.
IList <Park> parks = parkDAO.GetParks();
SurveyResultVM vm = new SurveyResultVM();
vm.Parks = new SelectList(parks, "ParkCode", "ParkName");
//Return the view.
return(View(vm));
}
public IList <SurveyResultVM> GetSurveyResultsOrdered()
{
List <SurveyResultVM> surveyResults = new List <SurveyResultVM>();
try
{
// Create a new connection object
using (SqlConnection conn = new SqlConnection(connectionString))
{
// Open the connection
conn.Open();
string sql =
@"SELECT p.parkName as ParkName, s.parkCode as Code, COUNT(s.parkCode) as NumOfFaves
from survey_result s
JOIN park p ON p.parkCode = s.parkCode
Group BY p.parkName, s.parkCode
ORDER BY NumOfFaves DESC";
SqlCommand cmd = new SqlCommand(sql, conn);
// Execute the command
SqlDataReader rdr = cmd.ExecuteReader();
// Loop through each row
while (rdr.Read())
{
SurveyResultVM sResult = new SurveyResultVM();
sResult.ParkName = Convert.ToString(rdr["ParkName"]);
sResult.ParkCode = Convert.ToString(rdr["Code"]);
sResult.NumOfFaves = Convert.ToInt32(rdr["NumOfFaves"]);
surveyResults.Add(sResult);
}
}
}
catch (SqlException)
{
throw;
}
return(surveyResults);
}
public IActionResult Index(SurveyResultVM vm)
{
//If the form is not completely filled out.
if (!ModelState.IsValid)
{
//Get a list of the parks for the selectlist in the view.
IList <Park> parks = parkDAO.GetParks();
vm.Parks = new SelectList(parks, "ParkCode", "ParkName");
//return the view with the errors.
return(View(vm));
}
//If the form is completely filled out, save the survey to the database.
SurveyResult survey = vm.Survey;
surveyResultDAO.SaveSurvey(survey);
//Return the result view.
return(RedirectToAction("Results"));
}
/// <summary>
/// Get survey results from database
/// Returns count of votes by park name
/// If the votes are tied, it sorts the parks alphabetically
/// </summary>
public IList <SurveyResultVM> GetSurveyResults()
{
IList <SurveyResultVM> results = new List <SurveyResultVM>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
//Open connection
conn.Open();
string sql =
@"select survey_result.parkCode, parkName, count(survey_result.parkCode) AS votes
from survey_result
join park on park.parkCode = survey_result.parkCode
group by survey_result.parkCode, parkName
order by votes desc, parkName asc";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader reader = cmd.ExecuteReader();
//Loop through each row
//All we want are the parkCode (for the image), the parkName and the number of votes per park
//Add them to the list of survey results
while (reader.Read())
{
SurveyResultVM resultVM = new SurveyResultVM();
resultVM.ParkCode = Convert.ToString(reader["parkCode"]);
resultVM.ParkName = Convert.ToString(reader["parkName"]);
resultVM.Votes = Convert.ToInt32(reader["votes"]);
results.Add(resultVM);
}
}
}
catch (SqlException ex)
{
throw ex;
}
return(results);
}
