/// <summary>
/// Gets an IDataReader with all rows from the mp_Polls table matching the siteGuid.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public static IDataReader GetPollsByUserGuid(Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT p.*, ");
sqlCommand.Append("po.OptionGuid, ");
sqlCommand.Append("po.Answer ");
sqlCommand.Append("FROM mp_Polls p ");
sqlCommand.Append("JOIN mp_PollUsers pu ");
sqlCommand.Append("ON p.PollGuid = pu.PollGuid ");
sqlCommand.Append("JOIN mp_PollOptions po ");
sqlCommand.Append("ON pu.OptionGuid = po.OptionGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pu.UserGuid = @UserGuid ");
sqlCommand.Append("ORDER BY ActiveFrom DESC, Question ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@UserGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_SurveyQuestionAnswers table.
/// </summary>
/// <param name="answerGuid"> answerGuid </param>
public static IDataReader GetOne(Guid responseGuid, Guid questionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SurveyQuestionAnswers ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QuestionGuid = @QuestionGuid ");
sqlCommand.Append("AND ResponseGuid = @ResponseGuid; ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@ResponseGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
arParams[1] = new FbParameter("@QuestionGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = questionGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows from the mp_Polls table matching the siteGuid.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public static IDataReader GetActivePolls(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Polls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
//sqlCommand.Append("AND Active = 1 ");
sqlCommand.Append("AND ActiveFrom <= @CurrentTime ");
sqlCommand.Append("AND ActiveTo >= @CurrentTime ");
sqlCommand.Append("ORDER BY Question ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@CurrentTime", FbDbType.TimeStamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = DateTime.UtcNow;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Polls table.
/// </summary>
/// <param name="pollGuid"> pollGuid </param>
public static IDataReader GetPollByModuleID(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT p.*, ");
sqlCommand.Append("(SELECT SUM(po.Votes) FROM mp_PollOptions po WHERE po.PollGuid = p.PollGuid) As TotalVotes ");
sqlCommand.Append("FROM mp_Polls p ");
sqlCommand.Append("JOIN mp_PollModules pm ");
sqlCommand.Append("ON p.PollGuid = pm.PollGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pm.ModuleID = @ModuleID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with the next response to a survey
/// </summary>
public static IDataReader GetPrevious(Guid responseGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SurveyResponses ");
sqlCommand.Append("WHERE Complete = 1 AND SubmissionDate < (");
sqlCommand.Append("SELECT SubmissionDate ");
sqlCommand.Append("FROM mp_SurveyResponses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ResponseGuid = @ResponseGuid) ");
sqlCommand.Append("AND ");
sqlCommand.Append("SurveyGuid = (");
sqlCommand.Append("SELECT SurveyGuid ");
sqlCommand.Append("FROM mp_SurveyResponses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ResponseGuid = @ResponseGuid) ");
sqlCommand.Append("Order By SubmissionDate DESC, ResponseGuid; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ResponseGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader from the mp_SurveyQuestionAnswers table.
/// </summary>
/// <param name="answerGuid"> answerGuid </param>
public static IDataReader GetResults(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("qa.AnswerGuid, ");
sqlCommand.Append("qa.QuestionGuid, ");
sqlCommand.Append("qa.ResponseGuid, ");
sqlCommand.Append("qa.Answer, ");
sqlCommand.Append("qa.AnsweredDate, ");
sqlCommand.Append("u.Name, ");
sqlCommand.Append("u.Email ");
//sqlCommand.Append(" ");
//sqlCommand.Append(" ");
//sqlCommand.Append(" ");
sqlCommand.Append("FROM mp_SurveyQuestionAnswers qa ");
sqlCommand.Append("JOIN mp_SurveyResponses sr ");
sqlCommand.Append("ON qa.ResponseGuid = sr.ResponseGuid ");
sqlCommand.Append("LEFT OUTER JOIN mp_Users u ");
sqlCommand.Append("ON u.UserGuid = sr.UserGuid ");
sqlCommand.Append("WHERE sr.SurveyGuid = @SurveyGuid ");
//sqlCommand.Append("AND sr.Complete = 1 ");
//sqlCommand.Append("AND sp.PageEnabled = 1 ");
//sqlCommand.Append("ORDER BY sp.PageOrder, sq.QuestionOrder ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyPages table.
/// </summary>
public static IDataReader GetAll(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT sp.*, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyQuestions sq WHERE sp.PageGuid = sq.PageGuid) AS QuestionCount ");
sqlCommand.Append("FROM mp_SurveyPages sp ");
sqlCommand.Append("WHERE sp.SurveyGuid = @SurveyGuid ");
sqlCommand.Append("ORDER BY sp.PageOrder; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Selects a particular Track from the doan_MediaTracks table.
/// </summary>
/// <param name="trackID">The ID of the track.</param>
/// <returns>An IDataReader containing the MediaTrack data.</returns>
public static IDataReader Select(int trackId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_MediaTrack ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("TrackID = @TrackID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@TrackID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
return(FBSqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_PollOptions table.
/// </summary>
/// <param name="optionGuid"> optionGuid </param>
public static IDataReader GetPollOption(Guid optionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_PollOptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("OptionGuid = @OptionGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@OptionGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = optionGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows from the mp_Polls table matching the siteGuid.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public static IDataReader GetPolls(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Polls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append("ORDER BY ActiveFrom DESC, Question ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyQuestionOptions table.
/// </summary>
public static IDataReader GetAll(Guid questionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SurveyQuestionOptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QuestionGuid = @QuestionGuid ");
sqlCommand.Append("ORDER BY \"Order\" ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@QuestionGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with the first response to a survey
/// </summary>
public static IDataReader GetFirst(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 * ");
sqlCommand.Append("FROM mp_SurveyResponses ");
sqlCommand.Append("WHERE SurveyGuid = @SurveyGuid ");
sqlCommand.Append("AND Complete = 1 ");
sqlCommand.Append("ORDER BY SubmissionDate, ResponseGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Polls table.
/// </summary>
/// <param name="pollGuid"> pollGuid </param>
public static IDataReader GetPoll(Guid pollGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT p.*, ");
sqlCommand.Append("(SELECT SUM(Votes) FROM mp_PollOptions WHERE mp_PollOptions.PollGuid = @PollGuid) As TotalVotes ");
sqlCommand.Append("FROM mp_Polls p ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("p.PollGuid = @PollGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@PollGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pollGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Surveys table.
/// </summary>
/// <param name="surveyGuid"> surveyGuid </param>
public static IDataReader GetOne(
Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT s.*, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyPages sp WHERE sp.SurveyGuid = s.SurveyGuid) AS PageCount, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyResponses sr WHERE sr.SurveyGuid = s.SurveyGuid) AS ResponseCount ");
sqlCommand.Append("FROM mp_Surveys s ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("s.SurveyGuid = @SurveyGuid; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyQuestionAnswers table.
/// </summary>
public static IDataReader GetOneResult(Guid responseGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Surveys s ");
sqlCommand.Append("JOIN mp_SurveyResponses sr ");
sqlCommand.Append("ON s.SurveyGuid = sr.SurveyGuid ");
sqlCommand.Append("JOIN mp_SurveyPages sp ");
sqlCommand.Append("ON sr.SurveyGuid = sp.SurveyGuid ");
sqlCommand.Append("JOIN mp_SurveyQuestions sq ");
sqlCommand.Append("ON sp.PageGuid = sq.PageGuid ");
sqlCommand.Append("LEFT JOIN mp_SurveyQuestionAnswers qa ");
sqlCommand.Append("ON sq.QuestionGuid = qa.QuestionGuid ");
sqlCommand.Append("AND sr.REsponseGuid = qa.ResponseGuid ");
sqlCommand.Append("WHERE sr.ResponseGuid = @ResponseGuid ");
sqlCommand.Append("AND sr.Complete = 1 ");
sqlCommand.Append("AND sp.PageEnabled = 1 ");
sqlCommand.Append("ORDER BY sp.PageOrder, sq.QuestionOrder; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ResponseGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_Surveys table.
/// </summary>
public static IDataReader GetAll(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.SurveyGuid, ");
sqlCommand.Append("s.SiteGuid, ");
sqlCommand.Append("s.SurveyName, ");
sqlCommand.Append("s.CreationDate, ");
sqlCommand.Append("s.StartPageText, ");
sqlCommand.Append("s.EndPageText, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyPages sp WHERE sp.SurveyGuid = s.SurveyGuid) AS PageCount, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyResponses sr WHERE sr.SurveyGuid = s.SurveyGuid) AS ResponseCount ");
sqlCommand.Append(" ");
sqlCommand.Append(" ");
sqlCommand.Append(" ");
sqlCommand.Append("FROM mp_Surveys s ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("s.SiteGuid = @SiteGuid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("s.SurveyName ");
//sqlCommand.Append(" ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}