/// <summary>
/// Returns all survey objects stored in database
/// </summary>
/// <returns></returns>
public List <SurveyResultModel> GetAllSurveys()
{
List <SurveyResultModel> surveyResults = new List <SurveyResultModel>();
string query = @"SELECT park.parkDescription, park.parkName, park.parkCode, COUNT(surveyId) as 'surveycount' " +
"FROM survey_result " +
"join park on park.parkCode = survey_result.parkCode " +
"GROUP BY park.parkCode, park.parkName, park.parkDescription " +
"Order BY surveycount Desc, park.parkName ASC";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResultModel favcount = new SurveyResultModel();
favcount.ParkName = Convert.ToString(reader["parkName"]);
favcount.ParkCode = Convert.ToString(reader["parkCode"]);
favcount.SurveyCount = Convert.ToInt32(reader["surveycount"]);
favcount.ParkDesc = Convert.ToString(reader["parkDescription"]);
surveyResults.Add(favcount);
}
}
return(surveyResults);
}
public ActionResult Results()
{
List <ParkModel> parks = parkDAL.GetAllParks();
Dictionary <string, ParkModel> parksDict = new Dictionary <string, ParkModel>();
List <SurveyResultModel> parksSurveyResults = new List <SurveyResultModel>();
foreach (ParkModel park in parks)
{
// Add key-value pair to parksDict
parksDict[park.ParkCode] = park;
// Add to parksSurveyResults
SurveyResultModel results = surveyDAL.GetParkSurveyResults(park.ParkCode);
if (results.NumSurveys >= 1)
{
parksSurveyResults.Add(results);
}
}
ParkSurveyResultViewModel surveySummaries = new ParkSurveyResultViewModel();
surveySummaries.ParksDict = parksDict;
surveySummaries.ParksSurveyResults = parksSurveyResults.OrderBy(p => p.NumSurveys).Reverse().ToList();
return(View("Results", surveySummaries));
}
public IActionResult NewSurvey(SurveyResultModel model)
{
SurveyDAL myDal = new SurveyDAL();
myDal.SaveSurvey(model);
return(RedirectToAction("favorites"));
}
public int SaveSurvey(SurveyResultModel newSurvey)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
int affectedRows = connection.Execute(SaveSurveyString, newSurvey);
return(affectedRows);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="SurveyResultDTOValidator"/> class.
/// </summary>
/// <param name="surveyResultModel">
/// The survey Result Model.
/// </param>
public SurveyResultDTOValidator(SurveyResultModel surveyResultModel)
{
this.CascadeMode = CascadeMode.StopOnFirstFailure;
this.RuleFor(model => model.participantName).NotEmpty().WithError(Errors.CODE_ERRORTYPE_INVALID_OBJECT, "Participant name is empty");
this.RuleFor(model => model.email)
.Must((model, x) => (!model.isArchive && string.IsNullOrWhiteSpace(x)) || (model.isArchive && !string.IsNullOrWhiteSpace(x)))
.WithError(Errors.CODE_ERRORTYPE_INVALID_OBJECT, "Email should be provided only in isArchive mode")
.Must((model, x) => (!model.isArchive && string.IsNullOrWhiteSpace(x)) || surveyResultModel.GetOneByACSessionIdAndEmail(model.acSessionId, x).Value == null)
.WithError(Errors.CODE_ERRORTYPE_INVALID_SESSION, "You have already passed this survey");
}
public async Task SaveAll(SurveySummaryResultDTO sResult)
{
if (sResult == null)
{
throw new ArgumentNullException(nameof(sResult));
}
if (sResult.surveyResults == null)
{
sResult.surveyResults = new SurveyResultDTO[0];
}
try
{
if (!IsValid(sResult, out ValidationResult valResult))
{
return;
}
Survey survey = SurveyModel.GetOneById(sResult.surveyId).Value;
ACSession acSession = ACSessionModel.GetOneById(sResult.acSessionId).Value;
if (acSession == null)
{
throw new ArgumentException($"There are not session with acSessionId : {sResult.acSessionId}");
}
int acSessionId = acSession.Id;
foreach (var surveyResultDTO in sResult.surveyResults)
{
surveyResultDTO.acSessionId = sResult.acSessionId;
if (IsValid(surveyResultDTO, out ValidationResult validationResult))
{
var surveyResult = ConvertDto(surveyResultDTO, survey, acSessionId);
SurveyResultModel.RegisterSave(surveyResult);
if (surveyResultDTO.results != null)
{
await SaveAllAsync(surveyResult, surveyResultDTO.results);
}
}
}
}
catch (ArgumentException ex)
{
Logger.Error("SurveyResultService.SaveAll: ", ex);
throw;
}
catch (Exception ex)
{
Logger.Error($"SurveyResultService.SaveAll json={JsonConvert.SerializeObject(sResult)}", ex);
throw;
}
}
public SurveyResultModel GetParkSurveyResults(string parkCode)
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// Get total number of surveys completed for park
SqlCommand cmd = new SqlCommand(SQL_GetSurveyResultsCount, conn);
cmd.Parameters.AddWithValue("parkCode", parkCode);
int surveyCount = Convert.ToInt32(cmd.ExecuteScalar());
// Get count of activity levels listed on this park's surveys
Dictionary <string, int> activityTypeCount = new Dictionary <string, int>();
List <string> activityType = new List <string>()
{
"inactive", "sedentary", "active", "extremely active"
};
foreach (string s in activityType)
{
string finalQuery = SQL_GetParkActivityCount + "'" + s + "';";
cmd = new SqlCommand(finalQuery, conn);
cmd.Parameters.AddWithValue("parkCode", parkCode);
activityTypeCount[s] = Convert.ToInt32(cmd.ExecuteScalar());
}
// Populate and return SurveyResultModel
SurveyResultModel completedSurveyResult = new SurveyResultModel();
completedSurveyResult.ParkCode = parkCode;
completedSurveyResult.NumSurveys = surveyCount;
completedSurveyResult.NumInactive = activityTypeCount["inactive"];
completedSurveyResult.NumSedentary = activityTypeCount["sedentary"];
completedSurveyResult.NumActive = activityTypeCount["active"];
completedSurveyResult.NumExtremelyActive = activityTypeCount["extremely active"];
return(completedSurveyResult);
}
}
catch (SqlException)
{
throw;
}
}
public int PostSurvey(SurveyResultModel surveyModel)
{
int result = 0;
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO survey_result (parkCode, emailAddress, state, activityLevel) " +
"VALUES ( @parkCode, @emailAddress, @state, @activityLevel) SELECT CAST(SCOPE_IDENTITY() as int)", conn);
cmd.Parameters.AddWithValue("@parkCode", surveyModel.ParkCode);
cmd.Parameters.AddWithValue("@emailAddress", surveyModel.Email);
cmd.Parameters.AddWithValue("@state", surveyModel.State);
cmd.Parameters.AddWithValue("@activityLevel", surveyModel.ActivityLevel);
result = (int)cmd.ExecuteScalar();
}
return(result);
}
public JsonResult getSurveyResult(int surveyId, int userId)
{
SurveyForm sur = new SurveyForm();
SurveyResultModel survey = new SurveyResultModel();
ResultModel result = new ResultModel();
result.SurveyId = surveyId;
result.UserId = userId;
result.results = sur.getSurveyResult(surveyId, userId);
// return result;
return(Json(result, JsonRequestBehavior.AllowGet));
//catch(Exception ex)
//{
// return Json(ex.Message, JsonRequestBehavior.AllowGet);
//}
}
public IList <SurveyResultModel> GetAllSurveys()
{
List <SurveyResultModel> surveys = new List <SurveyResultModel>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT parkCode, COUNT(*) AS numVotes FROM survey_result GROUP BY parkCode ORDER BY numVotes DESC", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResultModel rm = new SurveyResultModel();
rm.ParkCode = Convert.ToString(reader["parkCode"]);
rm.NumVotes = Convert.ToInt32(reader["numVotes"]);
surveys.Add(rm);
}
}
return(surveys);
}
public IActionResult Survey(SurveyResultModel RM)
{
_surveyDAO.PostSurvey(RM);
return(RedirectToAction("Favorites"));
}
public async Task <IActionResult> Index()
{
var userId = GetUserId();
var isAdmin = User.IsInRole(Roles.ADMIN);
var meetings = await _database.AnnualMeetings
.Include(m => m.Attendees.Where(a => isAdmin || a.UserId == userId))
.ThenInclude(a => a.User)
.Include(m => m.Survey)
.Expressionify()
.OrderBy(m => m.MeetingStartsAt)
.ToListAsync();
var meeting = meetings
.FirstOrDefault(m => m.MeetingEndsAt > TimeProvider.UtcNow);
var member = await _database.Users
.Expressionify()
.Where(u => u.Id == userId)
.FirstOrDefaultAsync(u => u.HasPayedMembershipThisYear());
if (meeting is null)
{
meeting = meetings
.OrderByDescending(m => m.MeetingEndsAt)
.FirstOrDefault();
if (meeting is null)
{
return(View("NoMeeting"));
}
}
else if (meeting.IsLive() && member != null)
{
var attendee = meeting.Attendees.GetOrAdd(a => a.UserId == member.Id,
() => new AnnualMeetingAttendee
{
User = member,
CreatedAt = TimeProvider.UtcNow
});
attendee.Visits++;
attendee.LastVisited = TimeProvider.UtcNow;
await _database.SaveChangesAsync();
}
var results = await _database.AnnualMeetings
.Expressionify()
.Select(s => SurveyResultModel.Create(s))
.FirstOrDefaultAsync(s => s.MeetingId == meeting.Id);
return(View(new Model
{
Id = meeting.Id,
IsMember = member != null,
UserId = member.Id,
Title = meeting.Title,
MeetingInvitation = meeting.MeetingInvitation,
MeetingInfo = meeting.MeetingInfo,
MeetingSummary = meeting.MeetingSummary,
MeetingStartsAt = meeting.MeetingStartsAt,
HasStarted = meeting.MeetingStartsAt < TimeProvider.UtcNow,
HasEnded = meeting.MeetingEndsAt < TimeProvider.UtcNow,
Attendees = meeting.Attendees.Where(a => a.User is not null).Select(a => new Model.Attendee
{
UserId = a.UserId,
Name = a.User.FullName,
Visits = a.Visits,
FirstVisit = a.CreatedAt,
LastVisit = a.LastVisited
}).ToList(),
VotingResults = results
}));
public SurveyResultDataDTO GetSurveyResultByACSessionIdAcEmail(int acSessionId, int subModuleItemId, string adobeConnectEmail)
{
return(SurveyResultModel.GetSurveyResultByACSessionIdAcEmail(acSessionId, subModuleItemId, adobeConnectEmail));
}
public SurveyResultDataDTO GetSurveyResultByACSessionId(int acSessionId, int subModuleItemId)
{
return(SurveyResultModel.GetSurveyResultByACSessionId(acSessionId, subModuleItemId));
}
