/// <summary>
///
/// </summary>
/// <param name="moduleId"></param>
/// <returns></returns>
public ModuleAccessDetail GetModuleDetailData(int moduleId)
{
ModuleAccessDetail moduleDetail = new ModuleAccessDetail();
List <TrainingAccess> trainingList = new List <TrainingAccess>();
List <QuizAccess> quizList = new List <QuizAccess>();
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
//query training table
cmd.CommandText = "SELECT trainingId, trainingName, description, type FROM TrainingModule WHERE moduleId = @moduleId";
cmd.Parameters.AddWithValue("@moduleId", moduleId);
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
TrainingAccess training = new TrainingAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
training.TrainingId = 0;
}
else
{
training.TrainingId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
training.TrainingName = null;
}
else
{
training.TrainingName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
training.Description = null;
}
else
{
training.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
training.Type = null;
}
else
{
training.Type = Convert.ToString(dr[3]);
}
trainingList.Add(training);
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
moduleDetail.Training = trainingList;
//query quiz table
cmd.CommandText = "SELECT quizId, quizName, description, passingGrade FROM Quiz WHERE moduleId = @moduleIdq";
cmd.Parameters.AddWithValue("@moduleIdq", moduleId);
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
QuizAccess quiz = new QuizAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
quiz.QuizId = 0;
}
else
{
quiz.QuizId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
quiz.QuizName = null;
}
else
{
quiz.QuizName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
quiz.Description = null;
}
else
{
quiz.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
quiz.PassingGrade = 0;
}
else
{
quiz.PassingGrade = Convert.ToInt32(dr[3]);
}
quizList.Add(quiz);
}
}
moduleDetail.Quiz = quizList;
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
return(moduleDetail);
}
/// <summary>
/// Get a quiz list for a module that customers want to select
/// </summary>
/// <param name="moduleId"></param>
/// <returns></returns>
public List <QuizTransfer> GetQuizForModule(int moduleId)
{
List <QuizTransfer> quizList = new List <QuizTransfer>();
string currentQuestionType;
int currentQuizId = 0;
int currentQuestionId = 0;
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT DISTINCT * FROM Quiz AS Q LEFT JOIN Question_Answer AS QA ON Q.quizId = QA.quizId"
+ " LEFT JOIN Qustion_Answer_Multiple_Selection AS QAMS ON QA.questionAnswerId = QAMS.questionAnswerId"
+ " WHERE Q.moduleId = @moduleId;";
cmd.Parameters.Add("@moduleId", SqlDbType.Int).Value = moduleId;
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
//add data to quiz table
//quizId, quizName, description, passingGrade
QuizTransfer quizTransfer = new QuizTransfer();
if (currentQuizId != Convert.ToInt32(dr[0]))
{
QuizAccess quiz = new QuizAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
quiz.QuizId = 0;
}
else
{
quiz.QuizId = Convert.ToInt32(dr[0]);
}
currentQuizId = quiz.QuizId;
if (System.DBNull.Value.Equals(dr[1]))
{
quiz.QuizName = null;
}
else
{
quiz.QuizName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
quiz.Description = null;
}
else
{
quiz.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
quiz.PassingGrade = 0;
}
else
{
quiz.PassingGrade = Convert.ToInt32(dr[3]);
}
if (System.DBNull.Value.Equals(dr[4]))
{
quiz.ModuleId = 0;
}
else
{
quiz.ModuleId = Convert.ToInt32(dr[4]);
}
quizTransfer.QuizDescription = quiz;
//add quiz table to quizTransfer
if (currentQuizId != 0)
{
quizList.Add(quizTransfer);
}
}
//add data to Question_Answer table
//questionAnswerId, quizId, questionDescription, type, Answer
if (!System.DBNull.Value.Equals(dr[5]))
{
if (currentQuestionId != Convert.ToInt32(dr[5]))
{
QuestionAnswerAccess question = new QuestionAnswerAccess();
QuestionTransfer mulQuestion = new QuestionTransfer();
if (System.DBNull.Value.Equals(dr[5]))
{
question.QuestionAnswerId = 0;
}
else
{
question.QuestionAnswerId = Convert.ToInt32(dr[5]);
}
currentQuestionId = question.QuestionAnswerId;
if (System.DBNull.Value.Equals(dr[6]))
{
question.QuizId = 0;
}
else
{
question.QuizId = Convert.ToInt32(dr[6]);
}
if (System.DBNull.Value.Equals(dr[7]))
{
question.QuestionDescription = null;
}
else
{
question.QuestionDescription = Convert.ToString(dr[7]);
}
if (System.DBNull.Value.Equals(dr[8]))
{
question.Type = null;
}
else
{
question.Type = Convert.ToString(dr[8]);
}
currentQuestionType = question.Type;
if (System.DBNull.Value.Equals(dr[9]))
{
question.Answer = null;
}
else
{
question.Answer = Convert.ToString(dr[9]);
}
if (currentQuestionType == "bool")
{
quizList.Last().BoolQuestion.Add(question);
quizList.Last().BoolQuestionNumber++;
}
else
{
mulQuestion.QuestionAnswer = question;
quizList.Last().MulSelQuestion.Add(mulQuestion);
quizList.Last().MulSelQuestionNumber++;
}
}
}
//add data to Qustion_Answer_Multiple_Selection table
//mulSelectionId, questionAnswerId, choiceDescription, choiceSequenceNumber
if (!System.DBNull.Value.Equals(dr[10]))
{
QuestionSelectItem selectItem = new QuestionSelectItem();
selectItem.MulSelectionId = Convert.ToInt32(dr[10]);
if (System.DBNull.Value.Equals(dr[11]))
{
selectItem.QuestionAnswerId = 0;
}
else
{
selectItem.QuestionAnswerId = Convert.ToInt32(dr[11]);
}
if (System.DBNull.Value.Equals(dr[12]))
{
selectItem.ChoiceDescription = null;
}
else
{
selectItem.ChoiceDescription = Convert.ToString(dr[12]);
}
if (System.DBNull.Value.Equals(dr[13]))
{
selectItem.ChoiceSequenceNumber = null;
}
else
{
selectItem.ChoiceSequenceNumber = Convert.ToString(dr[13]);
}
quizList.Last().MulSelQuestion.Last().SelectionItems.Add(selectItem);
quizList.Last().MulSelQuestion.Last().ItemsNumber++;
}
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
return(quizList);
}
