public IList <SurveyResult> GetSurveyResults()
{
IList <SurveyResult> surveys = new List <SurveyResult>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sqlGetSurveyResults);
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResult survey = new SurveyResult();
survey.SurveyID = Convert.ToInt32(reader["surveyId"]);
survey.ParkCode = Convert.ToString(reader["parkCode"]);
survey.EmailAddress = Convert.ToString(reader["emailAddress"]);
survey.State = Convert.ToString(reader["state"]);
survey.ActivityLevel = Convert.ToString(reader["activityLevel"]);
surveys.Add(survey);
}
return(surveys);
}
}
catch (SqlException ex)
{
throw;
}
}
/// <summary>
/// 提交问卷
/// </summary>
/// <param name="resultVO"></param>
/// <returns></returns>
public ResultVO SubmitSurvey(SurveyResultVO resultVO)
{
ResultVO result = new ResultVO()
{
Result = 0
};
try
{
using (TransactionScope trans = new TransactionScope())
{
RespondentInfo respondent = new RespondentInfo()
{
RespondentName = resultVO.Username,
MobilePhone = resultVO.Mobile,
Age = resultVO.Age,
Gender = resultVO.Gender == 0 ? null : (resultVO.Gender == 1 ? "男" : "女"),
EduBackground = string.IsNullOrEmpty(GetEduString(resultVO.Edu)) ? null : GetEduString(resultVO.Edu),
Location = resultVO.Location,
MaritalStatus = string.IsNullOrEmpty(GetMaritalString(resultVO.Marital)) ? null : GetMaritalString(resultVO.Marital),
Referrer = resultVO.Refer,
Suggestion = resultVO.Suggestion,
CreateTime = CurrentServerTime,
Deleted = false,
Occupation = string.IsNullOrEmpty(GetPosString(resultVO.Position)) ? null : GetPosString(resultVO.Position)
};
var insertedResp = _respondentInfoRepository.Insert(respondent);
_unitOfWork.Commit();
var tmpOpt = resultVO.Options.Split(',').ToList().FindAll(o => o.Contains("#"));
tmpOpt.ForEach(o =>
{
var questionId = Convert.ToInt32(o.Split('#')[0]);
var questionAns = o.Split('#')[1];
SurveyResult surveyResult = new SurveyResult()
{
SurveyId = resultVO.SurveyId,
RespondentId = insertedResp.RespondentId,
QuestionId = questionId,
SelectedOptions = questionAns,
CreateTime = CurrentServerTime
};
_surveyResultRepository.Insert(surveyResult);
_unitOfWork.Commit();
});
trans.Complete();
result.Result = 1;
}
}
catch (Exception ex)
{
result.ErrorMsg = ex.Message;
}
return(result);
}
// TODO: review
private async Task ConvertAndSendSurveyResultAsync(SurveyResult surveyResult, IEnumerable <SurveyQuestionResultDTO> results)
{
if (surveyResult == null)
{
return;
}
var lmsUserParameters = surveyResult.LmsUserParametersId.HasValue
? this.LmsUserParametersModel.GetOneById(surveyResult.LmsUserParametersId.Value).Value
: null;
if (lmsUserParameters == null)
{
return;
}
var lmsSurveyId = surveyResult.Survey.LmsSurveyId;
if (lmsSurveyId == null)
{
return;
}
var converter =
ConverterFactory.GetResultConverter((LmsProviderEnum)lmsUserParameters.CompanyLms.LmsProviderId);
await converter.ConvertAndSendSurveyResultToLmsAsync(results, surveyResult, lmsUserParameters);
}
public List <SurveyResult> GetSurveyResults()
{
try
{
List <SurveyResult> rankings = new List <SurveyResult>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(SQL_GetSurveyResults, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResult park = new SurveyResult();
park.Count = Convert.ToInt16(reader["votes"]);
park.ParkName = Convert.ToString(reader["parkName"]);
rankings.Add(park);
}
}
return(rankings);
}
catch (SqlException ex)
{
throw;
}
}
// POST: api/Survey
public void Post(SurveyResult surveyResult)
{
if (!Moderators.ModSurvey.UpdateTables(surveyResult))
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
}
public static bool update(SurveyResult survey)
{
try
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO Survey(UserName, Q1, Q2,Q3, Q4, Q5)" +
" VALUES('@UserName', '@Q1', '@Q2', '@Q3', '@Q4', '@Q5') ";
command.CommandText = command.CommandText.Replace("@UserName", survey.UserName);
command.CommandText = command.CommandText.Replace("@Q1", survey.Q1.ToString());
command.CommandText = command.CommandText.Replace("@Q2", survey.Q2.ToString());
command.CommandText = command.CommandText.Replace("@Q3", survey.Q3.ToString());
command.CommandText = command.CommandText.Replace("@Q4", survey.Q4.ToString());
command.CommandText = command.CommandText.Replace("@Q5", survey.Q5.ToString());
var rowsAffected = command.ExecuteNonQuery();
return(rowsAffected == 1);
}
}
catch (Exception)
{
return(false);
}
}
public List <SurveyResult> GetSurveyCountOfParks()
{
List <SurveyResult> results = new List <SurveyResult>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL_SelectSurveyCountByParkCode, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResult result = new SurveyResult
{
SurveyCount = Convert.ToInt32(reader["surveyCount"]),
ParkCode = Convert.ToString(reader["parkCode"]),
ParkName = Convert.ToString(reader["parkName"])
};
results.Add(result);
}
return(results);
}
}
catch (SqlException)
{
throw;
}
}
public List <SurveyResult> SurveyResults()
{
List <SurveyResult> output = new List <SurveyResult>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select COUNT(*) as count, parkCode from survey_result Group BY parkCode ORDER BY count desc;", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
{
SurveyResult s = new SurveyResult();
s.Count = Convert.ToInt32(reader["count"]);
s.ParkCode = Convert.ToString(reader["parkCode"]);
output.Add(s);
};
}
}
}
catch (SqlException ex)
{
ex.Message.ToString();
}
return(output);
}
/// <summary>
/// Provides a list of voted favorite parks based on survey results
/// </summary>
/// <returns></returns>
public IList <SurveyResult> Results()
{
IList <SurveyResult> results = new List <SurveyResult>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT survey_result.parkCode, park.parkName, COUNT(survey_result.parkCode) AS favorite FROM survey_result
JOIN park ON(park.parkCode = survey_result.parkCode)
GROUP BY survey_result.parkCode, park.parkName ORDER By favorite DESC, park.parkName ASC", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResult survey = ConvertReaderToSurveyResults(reader);
results.Add(survey);
}
}
}
catch (SqlException ex)
{
throw;
}
return(results);
}
public void SaveSurvey(Survey survey)
{
survey.SurveyId = MaxID + 1;
SurveyResult newSurveyResult = new SurveyResult("Yosemite", survey.ParkCode, 1);
surveyResults.Add(newSurveyResult);
}
/// <summary>
/// Gets all the survey information from the survey_result in the database.
/// </summary>
/// <returns>Returns a list of all submitted surveys in database</returns>
public List <SurveyResult> GetAllSurveys()
{
List <SurveyResult> result = new List <SurveyResult>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = _sqlGetAllReviews;
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResult survey = new SurveyResult();
survey.SurveyId = Convert.ToInt32(reader["surveyId"]);
survey.ParkCode = Convert.ToString(reader["parkCode"]);
survey.EmailAddress = Convert.ToString(reader["emailAddress"]);
survey.State = Convert.ToString(reader["state"]);
survey.ActivityLevel = Convert.ToString(reader["activityLevel"]);
result.Add(survey);
}
}
return(result);
}
/// <summary>
/// Inserting the user information they entered on the site into our database.
/// </summary>
/// <param name="newSurvey">survey to be added to database</param>
/// <returns>Inserts survey information into the database</returns>
public bool SaveSurvey(SurveyResult newSurvey)
{
bool wasSuccessful = true;
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
const string sqlSavePost = "INSERT INTO survey_result (parkCode, emailAddress, state, activityLevel )" +
"VALUES ( @parkcode, @emailAddress, @state, @activityLevel);";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sqlSavePost;
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@parkcode", newSurvey.ParkCode);
cmd.Parameters.AddWithValue("@emailAddress", newSurvey.EmailAddress);
cmd.Parameters.AddWithValue("@state", newSurvey.State);
cmd.Parameters.AddWithValue("@activityLevel", newSurvey.ActivityLevel);
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
wasSuccessful = false;
throw new Exception("ERROR! your survey was not sent, Please try again.");
}
}
return(wasSuccessful);
}
public async Task <SurveyResult> SurveyResults(int surveyId)
{
var response = await surveyResponseBoundedContext.GetSurveyResponse(surveyId);
var survey = await surveyRootBoundedContext.ReadSurvey(surveyId);
SurveyResult surveyResult = new SurveyResult();
surveyResult.title = survey.title;
IList <SurveyResults> results = new List <SurveyResults>();
foreach (var data in response)
{
SurveyResults result = new SurveyResults();
result.questionDesc = survey.questions.Where(q => q.questionId == data.questionId).Select(q => q.questionDesc).FirstOrDefault();
result.questionResponse = data.response;
result.givenBy = data.givenBy;
results.Add(result);
}
surveyResult.surveyResults = results;
return(surveyResult);
}
//get a list of all the surveys in the database
public List <SurveyResult> GetSurveysResults()
{
List <SurveyResult> results = new List <SurveyResult>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL_GetSurveyResults, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResult s = new SurveyResult();
s.NumOfSurveys = Convert.ToInt32(reader["num_of_surveys"]);
s.ParkCode = Convert.ToString(reader["parkCode"]);
s.ParkName = Convert.ToString(reader["parkName"]);
results.Add(s);
}
}
}
catch (SqlException)
{
throw;
}
return(results);
}
public RestResponse SyncEmail(SurveyResult newUser)
{
var subscribeRequest = new
{
email_address = newUser.Email,
email_type = "html",
status = "subscribed",
language = "en",
merge_fields =
new
{
FNAME = newUser.FirstName,
LNAME = newUser.LastName,
PHONE = newUser.Phone
},
ip_opt = "true",
//tags = ""
};
RestClient client = new RestClient();
client.BaseUrl = new Uri($"{BaseUrl}//lists//{ListId}//members");
client.Authenticator =
new HttpBasicAuthenticator("apikey", ApiKey);
RestRequest request = new RestRequest();
request.AddParameter(@"application/json; charset=utf-8", JsonConvert.SerializeObject(subscribeRequest), ParameterType.RequestBody);
request.Method = Method.POST;
return((RestResponse)client.Execute(request));
}
public List <SurveyResult> GetAllSurveys()
{
//List<Survey> output = new List<Survey>();
List <SurveyResult> output = new List <SurveyResult>();
try
{
using (SqlConnection conn = new SqlConnection(aName))
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL_GetAllSurveys, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResult s = new SurveyResult();
s.ParkName = Convert.ToString(reader["parkName"]);
s.ParkCode = Convert.ToString(reader["parkCode"]);
s.NumberOfVotes = Convert.ToInt32(reader["num"]);
output.Add(s);
}
}
}
catch
{
throw;
}
return(output);
}
public IList <SurveyResult> GetSurveyResults()
{
List <SurveyResult> surveyResults = new List <SurveyResult>();
try
{
// Create a new connection object
using (SqlConnection conn = new SqlConnection(connectionString))
{
// Open the connection
conn.Open();
string sql =
@"SELECT *
FROM survey_result";
SqlCommand cmd = new SqlCommand(sql, conn);
// Execute the command
SqlDataReader rdr = cmd.ExecuteReader();
// Loop through each row
while (rdr.Read())
{
SurveyResult sResult = RowToObject(rdr);
surveyResults.Add(sResult);
}
}
}
catch (SqlException)
{
throw;
}
return(surveyResults);
}
public void InsertSurveyTest()
{
//Arrange
SurveyResultDAL surveyDAL = new SurveyResultDAL(connectionString);
bool isAdded;
//Act
isAdded = surveyDAL.InsertSurvey(survey);
//Assert
Assert.IsTrue(isAdded);
//TEST: Expected to return False when null values are passed in
//Arrange
SurveyResult surveyNull = new SurveyResult();
surveyNull.parkCode = "ABC";
surveyNull.emailAddress = "*****@*****.**";
surveyNull.state = "VA";
surveyNull.activityLevel = null;
bool isAdded2;
//Act
isAdded2 = surveyDAL.InsertSurvey(surveyNull);
//Assert
Assert.IsFalse(isAdded2);
}
public IList <SurveyResult> GetAllSurveys()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(@"
SELECT p.parkName, sr.parkCode, COUNT(sr.parkcode) as votes
FROM survey_result sr
JOIN park p ON p.parkCode = sr.parkCode
GROUP BY p.parkName, sr.parkCode
Order by votes desc", connection);
SqlDataReader reader = cmd.ExecuteReader();
List <SurveyResult> surveyResults = new List <SurveyResult>();
while (reader.Read())
{
SurveyResult survey = new SurveyResult();
survey.ParkName = Convert.ToString(reader["parkName"]);
survey.ParkCode = Convert.ToString(reader["parkCode"]);
survey.Votes = Convert.ToInt32(reader["votes"]);
surveyResults.Add(survey);
}
return(surveyResults);
}
}
public void Given()
{
const string username = "******";
const string password = "******";
const string recipient = "44123456789";
var surveyId = Guid.NewGuid();
_errorCode = "survey_problem";
_errorDescription = "There was a problem";
_errorValue = "THIS VALUE";
_errorValue2 = "OTHER VALUE";
var data = new
{
errors = new[]
{
new { code = _errorCode, description = _errorDescription, values = new[] { _errorValue, _errorValue2 } }
}
};
MockApi.SetEndpoint(new MockEndpoint(400,
JsonConvert.SerializeObject(data),
"application/json; charset=utf-8"));
var surveysClient = new SurveysService(MockApi.Url, new EsendexCredentials(username, password));
_result = surveysClient.Send(surveyId, recipient);
}
public IList <SurveyResult> GetSurveyResults()
{
IList <SurveyResult> surveys = new List <SurveyResult>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(Sql_results, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResult surveyResult = new SurveyResult();
surveyResult.ParkCode = Convert.ToString(reader["parkCode"]);
surveyResult.Count = Convert.ToInt32(reader["count"]);
surveyResult.ParkName = Convert.ToString(reader["parkName"]);
surveys.Add(surveyResult);
}
}
return(surveys);
}
/// <summary>
/// A method to retrieve survey results
/// </summary>
/// <returns>a list of survey results</returns>
public IList <SurveyResult> GetSurveyResults()
{
IList <SurveyResult> results = new List <SurveyResult>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"select survey_result.parkCode as parkCode, park.parkName as parkNAME, count(*) as 'count'
from survey_result
join park on survey_result.parkCode = park.parkCode
group by survey_result.parkCode, park.parkName
order by count DESC, park.parkName;", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SurveyResult survey = ConvertReaderToSurvey(reader);
results.Add(survey);
}
}
}
catch (SqlException ex)
{
//ToDo: actually do something with this catch
throw;
}
return(results);
}
public async Task <IActionResult> SurveyForm([FromForm] SurveyResult survey)
{
var probeResult = HttpContext.Session.Get <ProbeResult>(SessionKeyProbeResult);
probeResult.Q1Answer = survey.Q1Answer;
probeResult.Q2Answer = survey.Q2Answer;
probeResult.Q3Answer = survey.Q3Answer;
probeResult.Q4Answer = survey.Q4Answer;
probeResult.Q5Answer = survey.Q5Answer;
probeResult.Q6Answer = survey.Q6Answer;
probeResult.Q7Answer = survey.Q7Answer;
probeResult.Q8Answer = survey.Q8Answer;
probeResult.Q9Answer = survey.Q9Answer;
probeResult.Email = survey.Email;
probeResult.Done = true;
Console.WriteLine("Pyt. 1: " + survey.Q1Answer);
Console.WriteLine("Pyt. 1: " + survey.Q2Answer);
Console.WriteLine("Pyt. 1: " + survey.Q3Answer);
Console.WriteLine("Pyt. 1: " + survey.Q4Answer);
Console.WriteLine("Pyt. 1: " + survey.Q5Answer);
Console.WriteLine("Pyt. 1: " + survey.Q6Answer);
Console.WriteLine("Pyt. 1: " + survey.Q7Answer);
Console.WriteLine("Pyt. 1: " + survey.Q8Answer);
Console.WriteLine("Pyt. 1: " + survey.Q9Answer);
HttpContext.Session.Set(SessionKeyProbeResult, probeResult);
await Database.PostProbe(probeResult);
ProbeDone();
return(RedirectToAction("Summary"));
}
public IActionResult SubmitSurvey(string name, string comment, int mark)
{
SurveyResult.Save(name, comment, mark);
ViewBag.ShowMessage = true;
return(View("Survey"));
}
public static bool UpdateTables(SurveyResult results)
{
if (!AddSurveyResult.update(results))
{
return(false);
}
return(true);
}
public void WhenIEnterValidInformationAndSubmitTheSurvey()
{
SurveyInput page = ScenarioContext.Current.Get <SurveyInput>("CurrentPage");
SurveyResult resultPage = page.SubmitWithValidData_ReturnResultsPage();
ScenarioContext.Current.Set(resultPage, "CurrentPage");
}
public async Task <SurveyResult> AllResult()
{
RequestHandler <SurveyResult> request =
new RequestHandler <SurveyResult>(BaseUrl + "/AllResult", RestSharp.Method.GET);
SurveyResult re = await request.GetData();
return(re);
}
private void GetSlcUser()
{
string qItemId = RequestData.Get("ItemId") + "";
string SurveyId = RequestData.Get("SurveyId") + "";
var Ents = SurveyResult.FindAll(
Expression.Sql(" SurveyId='" + SurveyId + "' and QuestionItemId like '%" + qItemId + "%' "));
this.PageState.Add("Ents", Ents);
}
public static SurveyReviewModel FromDomain(Survey q, SurveyResult r)
{
return(new SurveyReviewModel
{
Survey = q,
Result = r,
Points = r.PointsEarned ?? 0,
});
}
public IActionResult Index(SurveyResult surveyResult)
{
if (!ModelState.IsValid)
{
return(BadRequest("An error has occured while processing your request."));
}
_dal.SaveNewSurveyResult(surveyResult);
return(RedirectToAction("surveyresults"));
}
