public IList <SurveyResultsModel> GetSurveyResults()
{
IList <SurveyResultsModel> result = new List <SurveyResultsModel>();
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand("select count(*) as TotalVotes, parkName, park.parkCode from survey_result " +
"join park on park.parkCode = survey_result.parkCode " +
"group by parkName, park.parkCode order by TotalVotes desc;", connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResultsModel surveyResultsModel = new SurveyResultsModel();
surveyResultsModel.ParkName = Convert.ToString(reader["parkName"]);
surveyResultsModel.ParkCode = Convert.ToString(reader["parkCode"]);
surveyResultsModel.ParkVotes = Convert.ToInt32(reader["TotalVotes"]);
result.Add(surveyResultsModel);
}
}
}
catch (Exception)
{
throw;
}
return(result);
}
public IList <SurveyResultsModel> GetResults()
{
IList <SurveyResultsModel> results = new List <SurveyResultsModel>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT park.parkName, park.parkCode, COUNT(survey_result.parkCode) AS surveys_submitted FROM park
JOIN survey_result ON survey_result.parkCode = park.parkCode
GROUP BY park.parkCode, park.parkName
ORDER BY COUNT(survey_result.parkCode) DESC, park.parkName ASC", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResultsModel result = new SurveyResultsModel();
result.ParkCode = Convert.ToString(reader["parkCode"]);
result.ParkName = Convert.ToString(reader["parkName"]);
result.SurveysSubmitted = Convert.ToInt32(reader["surveys_submitted"]);
results.Add(result);
}
}
return(results);
}
public ActionResult SurveyResult()
{
SurveyResultsModel surveyResult = new SurveyResultsModel();
surveyResult.ParkSurveyList = new List <park>();
surveyResult.Fahrenheit = CheckTemp();
List <string> parkCodes = dal.GetParkCodeByVote();
using (var context = new ParkSystemDatabaseEntities())
{
foreach (string code in parkCodes)
{
surveyResult.ParkSurveyList.Add(context.parks.Where(p => p.parkCode == code).FirstOrDefault());
}
//var query = from survey_result in context.survey_result
// group survey_result by survey_result.parkCode into grouping
// orderby grouping.Count() descending
// select new { parkCode = grouping.Key, Total = grouping.Count() };
//foreach(var parkCodeFound in query)
//{
// surveyResult.ParkSurveyList.Add(context.parks.Where(p => p.parkCode == parkCodeFound.ToString()).FirstOrDefault());
//}
}
return(View(surveyResult));
}
public IList <SurveyResultsModel> GetSurveys()
{
IList <SurveyResultsModel> surveys = new List <SurveyResultsModel>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT COUNT(survey_result.parkCode) as surveyCount, park.parkName, park.state, park.parkDescription, park.annualVisitorCount FROM survey_result JOIN park ON park.parkCode = survey_result.parkCode GROUP BY park.parkCode, park.parkName, park.state, park.parkDescription, park.annualVisitorCount ORDER BY COUNT(survey_result.parkCode) DESC, park.parkName ASC", conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var survey = new SurveyResultsModel()
{
SurveyCount = Convert.ToInt32(reader["surveyCount"]),
ParkName = Convert.ToString(reader["parkName"]),
State = Convert.ToString(reader["state"]),
ParkDescription = Convert.ToString(reader["parkDescription"]),
AnnualVisitorCount = Convert.ToInt32(reader["annualVisitorCount"]),
};
surveys.Add(survey);
}
}
return(surveys);
}
catch (Exception)
{
throw;
}
}
public IList <SurveyResultsModel> GetPopularParks()
{
List <SurveyResultsModel> surveyResults = new List <SurveyResultsModel>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT park.parkCode, park.parkName, COUNT(park.parkCode) AS park_count FROM park
JOIN survey_result ON park.parkCode = survey_result.parkCode
GROUP BY park.parkCode, park.parkName
ORDER BY park_count DESC, park.parkName ASC", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResultsModel surveyResultsModel = new SurveyResultsModel();
surveyResultsModel.ParkCode = Convert.ToString(reader["parkCode"]);
surveyResultsModel.ParkName = Convert.ToString(reader["parkName"]);
surveyResultsModel.ReviewCount = Convert.ToInt32(reader["park_count"]);
surveyResults.Add(surveyResultsModel);
}
}
}
catch (Exception)
{
throw;
}
return(surveyResults);
}
public static void SetSurveyResult(SurveyResultsModel survey)
{
if (survey == null)
{
throw new System.ArgumentNullException("survey");
}
XName arg_C4_0 = "surveyResult";
object[] array = new object[2];
array[0] = ((survey.guest == null) ? null : new XElement("guest", new object[]
{
new XElement("name", survey.guest.name),
new XElement("sex", survey.guest.sex)
}));
object[] arg_C2_0 = array;
int arg_C2_1 = 1;
System.Collections.Generic.IEnumerable <XElement> arg_C2_2;
if (survey.questions != null)
{
arg_C2_2 = survey.questions.Select(delegate(System.Collections.Generic.KeyValuePair <string, SurveyQuestionModel> q)
{
XName arg_105_0 = "question";
object[] array2 = new object[4];
array2[0] = new XAttribute("id", q.Key);
array2[1] = (string.IsNullOrEmpty(q.Value.issue) ? null : new XElement("issue", new XAttribute("id", q.Value.issue)));
object[] arg_D4_0 = array2;
int arg_D4_1 = 2;
System.Collections.Generic.IEnumerable <XElement> arg_D4_2;
if (q.Value.marks != null)
{
arg_D4_2 =
from m in q.Value.marks
where (m.Value ?? "").ToLower() == "on"
select new XElement("issue", new XAttribute("id", m.Key));
}
else
{
arg_D4_2 = null;
}
arg_D4_0[arg_D4_1] = arg_D4_2;
array2[3] = ((q.Value.note == null) ? null : new XElement("note", q.Value.note));
return(new XElement(arg_105_0, array2));
});
}
else
{
arg_C2_2 = null;
}
arg_C2_0[arg_C2_1] = arg_C2_2;
XElement xml = new XElement(arg_C4_0, array);
DataSet result = DatabaseOperationProvider.QueryProcedure("[rating].[up_setSurveyResult]", "result", new
{
accesscode = survey.accesscode,
data = xml
});
}
public ActionResult Index(SurveyResultsModel model)
{
if (model == null)
{
throw new ArgumentNullException("model");
}
if (SurveyProvider.GetInvitationInfo(model.accesscode) == null)
{
((dynamic)base.ViewBag).NotFound = true;
}
else
{
SurveyProvider.SetSurveyResult(model);
((dynamic)base.ViewBag).Thanks = true;
}
return(base.View());
}
public ActionResult SurveyResult()
{
SurveyResultsModel surveyResult = new SurveyResultsModel();
surveyResult.ParkSurveyList = new List<ParkSurveyCount>();
surveyResult.Fahrenheit = CheckTemp();
using (var context = new ParkSystemDatabaseEntities())
{
foreach(var park in context.survey_result)
foreach (var survey in context.survey_result)
{
ParkSurveyCount count = new ParkSurveyCount{
park = survey.park,
numberOfSurveys = survey.park.parkCode.Count()
};
surveyResult.ParkSurveyList.Add(count);
}
}
surveyResult.ParkSurveyList.OrderBy(model => model.numberOfSurveys);
return View(surveyResult);
}
