public List <Question> GetQuestionsForChapterFlashCard(int number, long userId, long chapterId)
{
using (var db = new StudyQuizEntities())
{
var remainingNumber = number;
var types = db.GET_TYPE_COUNT(chapterId).ToList();
var typeNumber = types.Count;
var questions = new List <Question>();
foreach (var item in types)
{
int targetNumber = remainingNumber / typeNumber;
int typeId = (int)item.TypeId;
var notStudiedQuestions = db.GET_QUESTIONS_NOT_STUDY_YET(targetNumber, userId, chapterId, typeId).ToList();
int remainingQuestions = targetNumber - notStudiedQuestions.Count;
var studiedQuestions = new List <GET_QUESTIONS_ALREADY_STDUY_Result>();
if (remainingQuestions > 0)
{
studiedQuestions = db.GET_QUESTIONS_ALREADY_STDUY(remainingQuestions, userId, chapterId, typeId).ToList();
}
var results = MergeToListQuestion(notStudiedQuestions, studiedQuestions);
questions.AddRange(results);
remainingNumber -= results.Count;
typeNumber--;
}
return(questions);
}
}
public User Register(SignUpRequest user)
{
using (var db = new StudyQuizEntities())
{
var result = db.Users.SingleOrDefault(i => i.Username.ToUpper().Equals(user.Username.ToUpper()));
if (result != null)
{
throw new Exception(Errors.USER_USERNAME_DUPLICATION);
}
var list = db.Users.Where(i => i.Email.ToUpper().Equals(user.Email.ToUpper())).ToList <User>();
if (list != null && list.Count > 0)
{
throw new Exception(Errors.EMAIL_DUPLICATION);
}
var newUser = new User
{
Name = user.Name,
Username = user.Username,
Email = user.Email,
Password = user.Password,
CreatedDate = DateTime.Now
};
result = db.Users.Add(newUser);
if (db.SaveChanges() > 0)
{
return(result);
}
throw new Exception(Errors.COMMON_UNKNOW);
}
}
public User Login(string username, string password)
{
using (var db = new StudyQuizEntities())
{
var result = db.Users.SingleOrDefault(i => i.Username.Equals(username) && i.Password.Equals(password));
return(result);
}
}
public List <Subject> SearchSubject(string name)
{
using (var db = new StudyQuizEntities())
{
var list = db.Subjects.Where(i => i.Name.ToUpper().Contains(name.ToUpper())).Take(100).ToList <Subject>();
return(list);
}
}
public List <Chapter> GetChaptersBySubjectId(long subjectId)
{
using (var db = new StudyQuizEntities())
{
var list = db.Chapters.Where(i => i.SubjectId == subjectId).ToList <Chapter>();
return(list);
}
}
public List <Chapter> SearchChapter(string name)
{
using (var db = new StudyQuizEntities())
{
var list = db.Chapters.Where(i => i.Name.ToUpper().Contains(name.ToUpper())).Take(100).ToList <Chapter>();
return(list);
}
}
public bool InsertFeedback(Feedback item)
{
using (var db = new StudyQuizEntities())
{
var tmp = db.Questions.SingleOrDefault(i => i.QuestionId == item.QuestionId);
if (tmp == null)
{
return(false);
}
var tmp2 = db.Users.SingleOrDefault(i => i.UserId == item.UserId);
if (tmp2 == null)
{
return(false);
}
db.Feedbacks.Add(item);
return(db.SaveChanges() > 0);
}
}
public List <Subject> FetchSubject(long offset, long number)
{
string sql = "SELECT * FROM Subject"
+ " ORDER BY Name"
+ " OFFSET @offset ROWS"
+ " FETCH NEXT @number ROWS ONLY";
var offsetParam = new SqlParameter("@offset", offset);
var numberParam = new SqlParameter("@number", number);
using (var db = new StudyQuizEntities())
{
var list = db.Subjects.SqlQuery(sql, offsetParam, numberParam).ToList();
foreach (var item in list)
{
item.Chapters = db.Chapters.Where(i => i.SubjectId == item.SubjectId).ToList <Chapter>();
}
return(list);
}
}
public static bool SaveStudiedQuestion(StudiedQuestionRequest request)
{
bool success = true;
using (var db = new StudyQuizEntities())
{
foreach (var item in request.Questions)
{
bool isExist = true;
var studiedQuestion = db.StudiedQuestions.SingleOrDefault(i => i.QuestionId == item.Key && i.UserId == request.UserId);
if (studiedQuestion == null)
{
isExist = false;
studiedQuestion = new StudiedQuestion
{
QuestionId = item.Key,
UserId = request.UserId,
WrongAnswer = 0,
CorrectAnswer = 0,
Count = 0
};
}
studiedQuestion.Count++;
if (item.Value)
{
studiedQuestion.CorrectAnswer++;
}
else
{
studiedQuestion.WrongAnswer++;
}
if (!isExist)
{
db.StudiedQuestions.Add(studiedQuestion);
}
}
db.SaveChanges();
}
return(success);
}
public List <Question> GetQuestionForSubjectTest(int number, long userId, long subjectId)
{
using (var db = new StudyQuizEntities())
{
var chapters = db.GET_CHAPTERS_TEST(subjectId).ToList();
if (chapters != null && chapters.Count > 0)
{
List <Question> questions = null;
if (number <= chapters.Count)
{
questions = GetOneQuestionPerChapter(number, userId, chapters);
}
else
{
questions = GetMultipleQuestionsPerChapter(number, userId, chapters);
}
return(questions);
}
}
return(new List <Question>());
}
public List <Chapter> SearchChapter(string name, long offset, long number)
{
string sql = "SELECT * FROM Chapter"
+ " WHERE Name LIKE '%' + @name + '%'"
+ " ORDER BY Name"
+ " OFFSET @offset ROWS"
+ " FETCH NEXT @number ROWS ONLY";
var nameParam = new SqlParameter("@name", name);
var offsetParam = new SqlParameter("@offset", offset);
var numberParam = new SqlParameter("@number", number);
using (var db = new StudyQuizEntities())
{
var list = db.Chapters.SqlQuery(sql, nameParam, offsetParam, numberParam).ToList();
foreach (var item in list)
{
item.Subject = db.Subjects.SingleOrDefault(i => i.SubjectId == item.SubjectId);
}
return(list);
}
}
