public List <Questions> GenerateFinalQuestionsSchool(int testId)
{
IPTSE_EXAMEntities objEntities = new IPTSE_EXAMEntities();
Questions objQuestions = new Questions();
List <Questions> lstQuestions = new List <Questions>();
using (var ctx = new IPTSE_EXAMEntities())
{
var lstQuestionsNew = ctx.Database
.SqlQuery <Questions>("select * from (select Row_number() over (Partition by QuestionCategoryId , QuestionDifficultyId order by NEWID()) Sno ,Q.Id,Q.QuestionText,Q.QuestionCategoryId ,Q.QuestionDifficultyId,QM.TestId from tbl_Question Q left outer join tbl_Test_Question_Map QM on QM.QuestionId=Q.Id Where QM.TestId = "+ testId + " and Q.IsActive = 1 and Q.QuestionDifficultyId<>4) as a where a. Sno < = 2")
.ToList();
lstQuestions = lstQuestionsNew;
}
int index = 0;
foreach (var quest in lstQuestions)
{
quest.QuestNo = ++index;
quest.questionsChoice = objEntities.tbl_Question_Choice.Where(t => t.Question_Id == quest.Id && t.IsActive == true)
.Select(t1 =>
new QuestionsChoice
{
Id = t1.Id,
Question_Id = t1.Question_Id,
ChoiceText = t1.ChoiceText,
IsAnswer = t1.IsAnswer,
}).ToList();
}
return(lstQuestions);
}
public List <Questions> GenerateFinalQuestionsCollege(int testId)
{
IPTSE_EXAMEntities objEntities = new IPTSE_EXAMEntities();
Questions objQuestions = new Questions();
List <Questions> lstQuestions = new List <Questions>();
using (var ctx = new IPTSE_EXAMEntities())
{
//var lstQuestionsNew = ctx.Database
// .SqlQuery<Questions>("SELECT A.*,QM.TestId FROM tbl_Question_Category AC(NoLock) INNER JOIN(SELECT ROW_NUMBER() OVER(PARTITION BY A.QuestionCategoryId ORDER BY CHECKSUM(NEWID())) AS RowNumber, A.* FROM tbl_Question A(NoLock)) A ON A.QuestionCategoryId = AC.Id left outer join tbl_Test_Question_Map QM on QM.QuestionId=A.Id WHERE A.RowNumber < 3 and QM.TestId=" + testId + "")
// .ToList();
var lstQuestionsNew = ctx.Database
.SqlQuery <Questions>("select * from (select Row_number() over (Partition by QuestionCategoryId , QuestionDifficultyId order by NEWID()) Sno ,Q.Id,Q.QuestionText,Q.QuestionCategoryId ,Q.QuestionDifficultyId,QM.TestId from tbl_Question Q left outer join tbl_Test_Question_Map QM on QM.QuestionId=Q.Id Where QM.TestId = "+ testId + " and Q.IsActive = 1) as a where a. Sno < = 2")
.ToList();
lstQuestions = lstQuestionsNew;
}
int index = 0;
foreach (var quest in lstQuestions)
{
quest.QuestNo = ++index;
quest.questionsChoice = objEntities.tbl_Question_Choice.Where(t => t.Question_Id == quest.Id && t.IsActive == true)
.Select(t1 =>
new QuestionsChoice
{
Id = t1.Id,
Question_Id = t1.Question_Id,
ChoiceText = t1.ChoiceText,
IsAnswer = t1.IsAnswer,
}).ToList();
}
return(lstQuestions);
}
private IQueryable <Questions> GetQuestions(IPTSE_EXAMEntities objEntities, int categoryId, int testId)
{
int qNo = 0;
return(from quest in objEntities.tbl_Question
join questMap in objEntities.tbl_Test_Question_Map
on quest.Id equals questMap.QuestionId
where questMap.TestId == testId
select new Questions
{
Id = quest.Id,
QuestionText = quest.QuestionText,
QuestionCategoryId = quest.QuestionCategoryId,
questionsChoice = quest.tbl_Question_Choice.Where(t => t.Question_Id == quest.Id && t.IsActive == true)
.Select(t1 =>
new QuestionsChoice
{
Id = t1.Id,
Question_Id = t1.Question_Id,
ChoiceText = t1.ChoiceText,
IsAnswer = t1.IsAnswer,
}).ToList(),
Points = quest.Points,
TestId = questMap.TestId,
skipQuestions = false
});
}
public void IsExamCompletion(UserExamCompletion objExamCompletion)
{
tbl_txn_Test_Completion objTestCompletion = new tbl_txn_Test_Completion();
using (var objContext = new IPTSE_EXAMEntities())
{
using (var dbcxtransaction = objContext.Database.BeginTransaction())
{
try
{
objTestCompletion.CandidateId = Convert.ToInt32(objExamCompletion.CandidateId);
objTestCompletion.IsExamCompleted = objExamCompletion.IsExamCompleted;
objTestCompletion.CreatedBy = objExamCompletion.CreatedBy;
objTestCompletion.CreatedDateTime = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("India Standard Time"));
objContext.tbl_txn_Test_Completion.Add(objTestCompletion);
objContext.SaveChanges();
dbcxtransaction.Commit();
}
catch (Exception ex)
{
throw ex;
}
}
}
}
public List <Test> GetExam()
{
IPTSE_EXAMEntities objEntities = new IPTSE_EXAMEntities();
return(objEntities.tbl_Test.Where(n => (bool)n.IsActive).
Select(m => new Test {
TestId = m.TestId, Name = m.Name, Description = m.Description, DurationInMinutes = m.DurationInMinutes
}).ToList());
}
public List <QuestionCategory> GetQuestionCategory()
{
IPTSE_EXAMEntities objEntities = new IPTSE_EXAMEntities();
return(objEntities.tbl_Question_Category.Select(m => new QuestionCategory
{
Category = m.Category,
Id = m.Id
}).ToList());
}
public string selectedAnswer(int?choiceId)
{
string choiceText = "";
using (var objContext = new IPTSE_EXAMEntities())
{
choiceText = objContext.tbl_Question_Choice.Where(t => t.Id == choiceId).Select(t1 => t1.ChoiceText).FirstOrDefault();
}
return(choiceText);
}
public bool isAnswer(int?choiceId)
{
Boolean isAns;
using (var objContext = new IPTSE_EXAMEntities())
{
isAns = objContext.tbl_Question_Choice.Where(t => t.Id == choiceId).Select(t1 => t1.IsAnswer).FirstOrDefault();
}
return(isAns);
}
public List <QuestionDifficultyLevel> GetDifficultyLevel()
{
IPTSE_EXAMEntities objEntities = new IPTSE_EXAMEntities();
return(objEntities.tbl_Question_Difficulty_Level.Select(m => new QuestionDifficultyLevel
{
Description = m.Description,
Difficulty_Level = m.Difficulty_Level,
Id = m.Id
}).ToList());
}
public List <Questions> GenerateQuestions()
{
IPTSE_EXAMEntities objEntities = new IPTSE_EXAMEntities();
Questions objQuestions = new Questions();
List <Questions> lstQuestions = new List <Questions>();
IQueryable <Questions> questionList = GetQuestions(objEntities, 1, 1);
lstQuestions.AddRange(QuestionHelper.GetRandom(questionList, 10));
int index = 0;
foreach (var quest in lstQuestions)
{
quest.QuestNo = ++index;
}
return(lstQuestions);
}
public void SaveAnswer(Questions questions, login_table objUserProfile)
{
tbl_Txn_Test_Result objQuest = new tbl_Txn_Test_Result();
tbl_Question_Choice objQuestChoice = new tbl_Question_Choice();
tbl_Txn_Question_Duration_Map objMap = new tbl_Txn_Question_Duration_Map();
using (var objContext = new IPTSE_EXAMEntities())
{
using (var dbcxtransaction = objContext.Database.BeginTransaction())
{
try
{
objQuest.CandidateId = Convert.ToInt32(objUserProfile.Id);
objQuest.CandidateEmailId = objUserProfile.email;
objQuest.TestXQId = questions.Id;
objQuest.ChoiceId = questions.selectedvalue;
objQuest.MarkScored = questions.markScored;
var isRight = objContext.tbl_Question_Choice.Where(t => t.Id == questions.selectedvalue).Select(t1 => t1.IsAnswer).ToString();
if (isRight == "true")
{
objQuest.MarkScored = objContext.tbl_Question.Where(t1 => t1.Id == questions.Id).Select(t2 => t2.Points).FirstOrDefault();
}
objQuest.CreatedBy = objUserProfile.email;
objQuest.CreatedDate = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("India Standard Time"));
objContext.tbl_Txn_Test_Result.Add(objQuest);
objMap.TestXQId = questions.Id;
objMap.AnswerTime_Sec = Convert.ToInt32(60 - Convert.ToInt32(questions.skippedTime.Substring(questions.skippedTime.Length - 2)));
//objMap.AnswerTime_Sec = Convert.ToInt32(timeSpend);
objMap.CreatedBy = objUserProfile.email;
objMap.CreatedDate = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("India Standard Time"));
objContext.tbl_Txn_Question_Duration_Map.Add(objMap);
objContext.SaveChanges();
dbcxtransaction.Commit();
}
catch (Exception ex)
{
throw ex;
}
}
}
}
public List <Questions> GetQuestions()
{
IPTSE_EXAMEntities db = new IPTSE_EXAMEntities();
return(db.tbl_Question.Select(m => new Questions
{
Id = m.Id,
IsActive = m.IsActive,
QuestionText = m.QuestionText,
QuestionCategoryId = m.QuestionCategoryId,
TestId = m.tbl_Test_Question_Map.FirstOrDefault().TestId,
QuestionDifficultyId = m.QuestionDifficultyId,
questionsChoice = m.tbl_Question_Choice.Select(k => new QuestionsChoice
{
Id = k.Id,
ChoiceText = k.ChoiceText,
IsActive = k.IsActive,
IsAnswer = k.IsAnswer,
Question_Id = k.Question_Id
}).ToList(),
}).ToList());//.Include(m => m.questionsChoice).ToList();//.Include(q => q.Exam_tbl);
}
public void SaveRemaining(Questions quest, UserProfile objUserProfile)
{
tbl_Txn_Test_Result objQuest = new tbl_Txn_Test_Result();
tbl_Question_Choice objQuestChoice = new tbl_Question_Choice();
tbl_Txn_Question_Duration_Map objMap = new tbl_Txn_Question_Duration_Map();
using (var objContext = new IPTSE_EXAMEntities())
{
using (var dbcxtransaction = objContext.Database.BeginTransaction())
{
try
{
objQuest.CandidateId = objUserProfile.Id;
objQuest.CandidateEmailId = objUserProfile.email;
objQuest.TestXQId = quest.Id;
objQuest.ChoiceId = quest.selectedvalue;
objQuest.MarkScored = quest.markScored;
var isRight = objContext.tbl_Question_Choice.Where(t => t.Id == quest.selectedvalue).Select(t1 => t1.IsAnswer).ToString();
if (isRight == "true")
{
objQuest.MarkScored = objContext.tbl_Question.Where(t1 => t1.Id == quest.Id).Select(t2 => t2.Points).FirstOrDefault();
}
objContext.tbl_Txn_Test_Result.Add(objQuest);
objMap.TestXQId = quest.Id;
objMap.AnswerTime_Sec = objMap.AnswerTime_Sec = Convert.ToInt32(60 - Convert.ToInt32(quest.skippedTime.Substring(quest.skippedTime.Length - 2)));
objContext.tbl_Txn_Question_Duration_Map.Add(objMap);
objContext.SaveChanges();
dbcxtransaction.Commit();
}
catch (Exception ex)
{
throw ex;
}
}
}
}
public bool IsExamGiven(login_table objUProfile)
{
tbl_txn_Test_Completion objTestCompletion = new tbl_txn_Test_Completion();
using (var objContext = new IPTSE_EXAMEntities())
{
using (var dbcxtransaction = objContext.Database.BeginTransaction())
{
try
{
var isCompletion = objContext.tbl_txn_Test_Completion.Where(t => t.CreatedBy == objUProfile.email && t.CandidateId == objUProfile.Id).Select(t1 => t1).FirstOrDefault();
if (isCompletion != null)
{
return(true);
}
else
{
return(false);
}
}
catch { return(false); }
}
}
}
public Questions GetQuestionDetails(int?id)
{
using (IPTSE_EXAMEntities db = new IPTSE_EXAMEntities())
{
tbl_Question ques = db.tbl_Question.Find(id);
if (ques == null)
{
return(null);
}
else
{
return(new Questions
{
Id = ques.Id,
IsActive = ques.IsActive,
QuestionText = ques.QuestionText,
QuestionCategoryId = ques.QuestionCategoryId,
TestId = ques.tbl_Test_Question_Map.FirstOrDefault().TestId,
QuestionDifficultyId = ques.QuestionDifficultyId,
questionsChoice = ques.tbl_Question_Choice.Select(m => new QuestionsChoice
{
Id = m.Id,
ChoiceText = m.ChoiceText,
IsActive = m.IsActive,
IsAnswer = m.IsAnswer,
Question_Id = m.Question_Id
}).ToList()
});
}
}
//if (questions_tbl == null)
//{
// return HttpNotFound();
//}
}
public void SaveChanges(Questions ques)
{
using (IPTSE_EXAMEntities db = new IPTSE_EXAMEntities())
{
using (var dbcxtransaction = db.Database.BeginTransaction())
{
try
{
tbl_Question tbl_Ques = new tbl_Question();
tbl_Ques.Id = ques.Id;
tbl_Ques.QuestionText = ques.QuestionText;
tbl_Ques.QuestionCategoryId = (int)ques.QuestionCategoryId;
tbl_Ques.QuestionDifficultyId = ques.QuestionDifficultyId;
tbl_Ques.IsActive = ques.IsActive;
tbl_Ques.Points = 1;
tbl_Ques.CreatedBy = "Admin";
tbl_Ques.CreatedDate = DateTime.Now;
ques.questionsChoice.ForEach(m => tbl_Ques.tbl_Question_Choice.Add(new tbl_Question_Choice
{
Id = m.Id,
Question_Id = m.Question_Id,
ChoiceText = m.ChoiceText,
IsAnswer = m.IsAnswer,
IsActive = m.IsActive,
CreatedBy = "Admin",
CreatedDate = tbl_Ques.CreatedDate
}));
db.Entry(tbl_Ques).State = EntityState.Modified;
tbl_Test_Question_Map map = db.tbl_Test_Question_Map.FirstOrDefault(m => m.QuestionId == ques.Id);
//tbl_Ques.tbl_Test_Question_Map.Add(new tbl_Test_Question_Map
//{
// ID = map.ID,
// IsActive = true,
// QuestionId = ques.Id,
// TestId = ques.TestId,
// CreatedBy = "Admin",
// CreatedDate = tbl_Ques.CreatedDate
//});
foreach (var item in tbl_Ques.tbl_Test_Question_Map)
{
item.TestId = ques.TestId;
db.Entry(item).State = EntityState.Modified;
}
foreach (var item in tbl_Ques.tbl_Question_Choice)
{
db.Entry(item).State = EntityState.Modified;
}
db.SaveChanges();
dbcxtransaction.Commit();
}
catch (DbEntityValidationException ex)
{
dbcxtransaction.Rollback();
throw ex;
}
catch (Exception ex)
{
dbcxtransaction.Rollback();
throw ex;
}
}
}
}
public void AddQuestion(Questions in_question)
{
using (IPTSE_EXAMEntities db = new IPTSE_EXAMEntities())
{
using (var dbcxtransaction = db.Database.BeginTransaction())
{
try
{
tbl_Question question = new tbl_Question();
tbl_Question_Category question_Category = new tbl_Question_Category();
tbl_Question_Choice question_Choice = new tbl_Question_Choice();
//tbl_Test test = new tbl_Test();
tbl_Test_Question_Map test_question_Map = new tbl_Test_Question_Map();
question.QuestionText = in_question.QuestionText;
question.IsActive = in_question.IsActive;
question.Points = in_question.Points;
question.QuestionCategoryId = (int)in_question.QuestionCategoryId;
question.QuestionDifficultyId = in_question.QuestionDifficultyId;
question.CreatedBy = "Admin";
question.CreatedDate = DateTime.Now;
question.Points = 1;
db.tbl_Question.Add(question);
db.SaveChanges();
//question.Id
db.tbl_Test_Question_Map.Add(new tbl_Test_Question_Map
{
TestId = in_question.TestId,
QuestionId = question.Id,
IsActive = in_question.IsActive,
CreatedBy = "Admin",
CreatedDate = DateTime.Now
});
foreach (QuestionsChoice item in in_question.questionsChoice)
{
db.tbl_Question_Choice.Add(new tbl_Question_Choice
{
ChoiceText = item.ChoiceText,
IsActive = item.IsActive,
IsAnswer = item.IsAnswer,
Question_Id = question.Id,
CreatedBy = "Admin",
CreatedDate = DateTime.Now
//,tbl_Question = question
});
}
db.SaveChanges();
dbcxtransaction.Commit();
}
catch (DbEntityValidationException ex)
{
dbcxtransaction.Rollback();
throw ex;
}
catch (Exception ex)
{
dbcxtransaction.Rollback();
throw ex;
}
}
}
}