JW/EMISOnline/EMISOnline.DataLogic/ExamSetting/ExamPaperDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMISOnline.DataLogic.Repositories;
using System.Linq.Expressions;
using EMISOnline.Entities;
using EMISOnline.ViewModel.ExamView;
using System.Xml.Linq;
using Bowin.Common.Linq;
using System.Data;

namespace EMISOnline.DataLogic.ExamSetting
{
    public class ExamPaperDAL : SqlHelper
    {
        public onlinetestRepository OnlinetestRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public questionRepository questionRepository { get; set; }
        public paper_question_setRepository question_setRepository { get; set; }
        public base_question_typeRepository question_typeRepository { get; set; }
        /// <summary>
        /// 根据条件查找试卷信息
        /// </summary>
        /// <param name="maximumRows"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="sortClause"></param>
        /// <param name="totalRow"></param>
        /// <param name="expWhere"></param>
        /// <returns></returns>
        public IQueryable<ListPaperView> ListPaper(int maximumRows, int startRowIndex, Expression<Func<test_paper, bool>> expWhere)
        {
            var q = PaperRepository.Entities;
            q = q.Where(expWhere);
            var query = from tq in q
                        join user in UserRepository.Entities on tq.created_by equals user.LoginID into ruser
                        from us in ruser.DefaultIfEmpty()
                        orderby tq.test_paper_id descending
                        select new ListPaperView
                        {
                            test_paper_id = tq.test_paper_id,
                            PaperName = tq.PaperName,
                            paper_score = tq.paper_score,
                            used_count = tq.used_count,
                            created_date = tq.created_date,
                            created_by = us != null ? us.Name : "",
                            is_dynamic = tq.is_dynamic ? "动态" : "静态",
                            isEnable = tq.is_vaild.Value ? "是" : "否"
                        };
            return query;
        }
        /// <summary>
        /// 根据一序列的条件查找不同题型的题数是多少 
        /// </summary>
        /// <param name="lib_ids"></param>
        /// <param name="diff_min"></param>
        /// <param name="diff_max"></param>
        /// <param name="err_rate"></param>
        /// <param name="usecount_min"></param>
        /// <param name="usecount_max"></param>
        /// <param name="question_type"></param>
        /// <returns></returns>
        public object CountQuesSumByCondition(List<decimal> lib_ids, decimal diff_min, decimal diff_max, decimal err_rate,
            int usecount_min, int usecount_max)
        {
            var query = from ques in questionRepository.Entities
                        where lib_ids.Contains(ques.test_question_libary_id.Value)
                        && ((ques.difficulty_degree.HasValue ? ques.difficulty_degree.Value : 0) >= diff_min && (ques.difficulty_degree.HasValue ? ques.difficulty_degree.Value : 0) <= diff_max)
                        && (ques.wrong_percent.HasValue ? ques.wrong_percent.Value : 0) >= err_rate
                        && ((ques.used_count.HasValue ? ques.used_count.Value : 0) >= usecount_min && (ques.used_count.HasValue ? ques.used_count.Value : 0) <= usecount_max)
                            // && ques.base_question_type_id == question_type
                        && ques.filled == false
                        group ques by ques.base_question_type_id into res
                        select new
                        {
                            Number = res.Count(),
                            TypeID = res.Key
                        };

            return query;//.ToDictionary(q => q.TypeID.Value, q => q.Number);

        }
        /// <summary>
        /// 根据试卷
        /// </summary>
        /// <param name="paperid"></param>
        /// <returns></returns>
        public DataTable GetQuestionByPaperID(decimal paperid)
        {
            var paperset = question_setRepository.GetSingle(q => q.test_paper_id == paperid);
            XElement xml = XElement.Parse(paperset.questionXml);
            var question_set = from c in xml.Descendants("QuestionXml")
                               select new
                               {
                                   Question_id = decimal.Parse(c.Element("Question_id").Value),
                                   Score = c.Element("Score").Value,
                               };

            var query = from set in question_set
                        join q_set in questionRepository.Entities on set.Question_id equals q_set.test_question_Id

                        join type in question_typeRepository.Entities on q_set.base_question_type_id equals type.base_question_type_id
                        orderby q_set.created_date descending
                        select new
                        {
                            q_set.test_question_Id,
                            q_set.content,
                            q_set.base_question_type_id,
                            question_type = type.Name,
                            q_set.created_date,
                            score = set.Score,
                            q_set.difficulty_degree,
                            q_set.used_count,
                            q_set.created_by

                        };

            return query.ToTable();

        }
        /// <summary>
        /// 按照设定条件抽取题库
        /// </summary>
        /// <param name="test_question_libary_id">题库id,id,id</param>
        /// <param name="difficulty_degree_min">难度最小</param>
        /// <param name="difficulty_degree_max">难度最大</param>
        /// <param name="wrong_percent">错误率</param>
        /// <param name="used_count_min">使用次数最小</param>
        /// <param name="used_count_max">使用次数最大</param>
        /// <returns></returns>
        public System.Data.DataTable SelectQuestions(string test_question_libary_id, string difficulty_degree_min,
            string difficulty_degree_max, string wrong_percent, string used_count_min, string used_count_max)
        {
            var ds = Query("dbo.SP_test_questions_filter", new KeyValuePair<string, object>("@test_question_libary_id", test_question_libary_id),
            new KeyValuePair<string, object>("@difficulty_degree_min", difficulty_degree_min),
            new KeyValuePair<string, object>("@difficulty_degree_max", difficulty_degree_max),
            new KeyValuePair<string, object>("@wrong_percent", wrong_percent),
            new KeyValuePair<string, object>("@used_count_min", used_count_min), new KeyValuePair<string, object>("@used_count_max", used_count_max));
            return ds.Tables[0];
        }

        public bool DeletePaper(decimal PaperID)
        {
            question_setRepository.UnitOfWork.Delete<test_paper_question_set>(q => q.test_paper_id == PaperID);
            //PaperRepository.UnitOfWork.Delete<test_paper>(q => q.test_paper_id == PaperID); 
            PaperRepository.UnitOfWork.Commit();
            return true;
        }

        /// <summary>
        /// 根据试卷id获取试卷信息以json格式返回
        /// </summary>
        /// <param name="paperid"></param>
        /// <returns></returns>
        public object GetPaperQuestions(decimal paperid)
        {
            var question_set = PaperQuestion_set(paperid);
            var query = from ques_set in question_set
                        join ques in questionRepository.Entities on ques_set.Question_id equals ques.test_question_Id into questions
                        from q in questions.DefaultIfEmpty()
                        where q.filled == false
                        select new
                        {
                            question_id = q.test_question_Id,
                            typeName = q.test_base_question_type.Name,
                            q.content,
                            q.base_question_type_id,
                            hasImg = q.question_file_id.HasValue,
                            url = q.question_file_id.HasValue ? q.test_question_file.url : "",

                            score = ques_set.Score,
                            rightAnswer = q.answers,
                            answers = q.test_question_provid_answer.Select(a => new
                            {
                                content = a.answer_name,
                                answer_id = a.provid_answer_id,
                                hasImg = a.file_id.HasValue,
                                url = a.file_id.HasValue ? a.test_question_file.url : "",
                            })
                        };

            var count = query.Count();

            var type_quetions = from basetype in question_typeRepository.Entities
                                orderby basetype.diplay_order
                                select new
                                {
                                    id = basetype.base_question_type_id,
                                    name = basetype.Name,
                                    tscore = (from q in query
                                              where q.base_question_type_id.Value == basetype.base_question_type_id
                                              select q.score).Sum(),
                                    questions = (from q in query
                                                 where q.base_question_type_id.Value == basetype.base_question_type_id
                                                 select q).ToArray()
                                };

            return type_quetions.Where(q => q.questions.Count() > 0).ToArray();
        }

        public List<QuestionXml> PaperQuestion_set(decimal paperid)
        {
            var paperset = question_setRepository.GetSingle(q => q.test_paper_id == paperid);
            XElement xml = XElement.Parse(paperset.questionXml);
            var question_set = from c in xml.Descendants("QuestionXml")
                               select new QuestionXml
                               {
                                   Question_id = decimal.Parse(c.Element("Question_id").Value),
                                   Score = decimal.Parse(c.Element("Score").Value),
                               };
            return question_set.ToList();
        }
    }
}