JW/EMISOnline/EMISOnline.DataLogic/ExamSetting/TestPaperDAL.cs

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

namespace EMISOnline.DataLogic.ExamSetting
{
    public class TestPaperDAL:SqlHelper
    { 
        public VTestQestionRepository VquestionRepository { get; set; }
        public paper_question_setRepository question_setRepository { get; set; }
        public questionRepository questionRepository { get; set; }
        public base_question_typeRepository typeRepository { get; set; }
        public question_libaryRepository libaryRepository { get; set; }
        public onlinetestRepository onlinetestRepository { get; set; }
        public ExamResultRepository ExamResultRepository { get; set; }
        public onlinetest_manRepository manRepository { get; set; }
        public exchangeRepository exchangeRepository { get; set; }
        public UserRepository UserRepository { get; set; }

        public void SetQuestion(decimal paperid, decimal diff_min, decimal diff_max, int usedcount_min, int usedcount_max, decimal wrong_rate)
        {
            var paper = PaperRepository.Entities.Single(q => q.test_paper_id == paperid);
            //题库范围 
            var libary_set_idstr = paper.question_libary_set_id;
            var libary_set_ids = libary_set_idstr.Split(',').Select(q => decimal.Parse(q)).ToArray();

            //得到按题型设置的题目数和分数  
            XElement xml = XElement.Parse(paper.question_setting_xml);

            var question_set = from c in xml.Descendants("QType")
                               select new
                               {
                                   QType = c.Attribute("QTypeID").Value,
                                   PSum = c.Attribute("PSum").Value,
                                   TSum = c.Attribute("TSum").Value,
                                   QScore = c.Attribute("QScore").Value,
                               };

            var questions = (from tq in vQuestion()
                             where tq.difficulty_degree >= diff_min
                && tq.difficulty_degree <= diff_max
                && tq.used_count >= usedcount_min
                && tq.used_count <= usedcount_max
                && tq.wrong_percent >= wrong_rate
                && tq.filled == false
                && (libary_set_ids.Contains(tq.test_question_libary_id.Value))
                             select tq).ToList();

            List<XElement> listxml = new List<XElement>();
            foreach (var set in question_set.ToList())
            {
                var tquestion = questions.Where(q => q.base_question_type_id.ToString() == set.QType).Select(q => new
                {
                    q.test_question_Id,
                    set.PSum,
                    set.QScore
                }).ToList();
                foreach (var testquest in tquestion)
                {
                    XElement x = new XElement("QuestionXml");
                    x.SetElementValue("Question_id", testquest.test_question_Id);
                    x.SetElementValue("Score", testquest.PSum);
                    listxml.Add(x);
                }
            }
            XElement qtxml = new XElement("ArrayOfQuestionXml", listxml);
            test_paper_question_set paper_question_set = new test_paper_question_set()
            {
                test_paper_id = paperid,
                questionXml = qtxml.ToString(),
                order = 0
            };
            question_setRepository.UnitOfWork.Add(paper_question_set);
            question_setRepository.UnitOfWork.Commit();
            //           --题库范围  
            //declare @libary_set_id varchar(200)  
            //select @libary_set_id=question_libary_set_id from test_paper where test_paper_id =@test_paper_id  
            //select * into #libary_set_id from dbo.SplitString(@libary_set_id,',',1)  

            //--得到按题型设置的题目数和分数  
            //select d.c.value('(@QTypeID)[1]','varchar(20)') QType,    
            //d.c.value('(@PSum)[1]','varchar(20)') PSum,  
            //d.c.value('(@TSum)[1]','varchar(20)') TSum,  
            //d.c.value('(@QScore)[1]','varchar(20)') QScore    
            //into #question_set    
            //from test_paper       
            //cross apply      
            //test_paper.question_setting_xml.nodes('/paper_set/QType') as d(c) where test_paper_id =@test_paper_id  

            //--按条件筛选问题  
            //select *   
            //into #questions  
            //from iexam_v_test_question where difficulty_degree >= @diff_min   
            //and difficulty_degree <= @diff_max  
            //and used_count >= @usedcount_min   
            //and used_count <= @usedcount_max  
            //and wrong_percent >= @wrong_rate  
            //and filled = 0  
            //and test_question_libary_id in (select Value from #libary_set_id)  

            //--问题存储临时表  
            //create table #q_selected(  
            //test_question_Id decimal null,  
            //QScore decimal null)  

            //declare @QType decimal,@PSum int,@TSum decimal,@QScore decimal  
            //declare type_Cursor cursor for  
            //select * from #question_set  
            //open type_Cursor  
            //fetch next from type_Cursor into @QType,@PSum,@TSum,@QScore  
            //while @@FETCH_STATUS=0  
            //begin  
            //insert into #q_selected  
            //select top(@PSum) test_question_Id,@QScore from #questions where base_question_type_id= @QType order by rid  
            //fetch next from type_Cursor into @QType,@PSum,@TSum,@QScore  
            //end  
            //close type_Cursor  
            //DEALLOCATE type_Cursor  

            //insert into test_paper_question_set(test_paper_id,questionXml,[order]) select @test_paper_id,  
            //(select test_question_Id as Question_id,QScore as Score from #q_selected  
            //for xml path('QuestionXml'),root('ArrayOfQuestionXml')) x,0  

            //drop table #libary_set_id  
            //drop table #question_set  
            //drop table #questions  
            //drop table #q_selected  
            //end
            //GO
        }

        public IQueryable<ExamQuestionView> vQuestion()
        {
            var query = from question in questionRepository.Entities
                        join type in typeRepository.Entities on question.base_question_type_id equals type.base_question_type_id
                        join libary in libaryRepository.Entities on question.test_question_libary_id equals libary.test_question_libary_id
                        select new ExamQuestionView
                        {
                            rid = Guid.NewGuid(),
                            test_question_Id = question.test_question_Id,
                            base_question_type_id = question.base_question_type_id,
                            content = question.content,
                            question_file_id = question.question_file_id,
                            difficulty_degree = question.difficulty_degree,
                            answers = question.answers,
                            is_order = question.is_order,
                            answers_note = question.answers_note,
                            note = question.note,
                            test_question_libary_id = question.test_question_libary_id,
                            used_count = question.used_count,
                            right_percent = question.right_percent,
                            is_vaild = question.is_vaild,
                            created_date = question.created_date,
                            wrong_count = question.wrong_count,
                            score = question.score,
                            filled = question.filled,
                            time_of_filling = question.time_of_filling,
                            created_by = question.created_by,
                            wrong_percent = question.wrong_percent,
                            question_type_name = type.Name,
                            questione_lib_name = libary.name
                        };
            return query;
        }

        /// <summary>
        /// 修改分配的试卷ID
        /// </summary>
        /// <param name="test_id"></param>
        /// <param name="userid"></param>
        /// <param name="paperID"></param>
        /// <returns></returns>
        public bool UpdatePaperID(decimal test_id, string userid, decimal paperID)
        {
            //删除原试卷数据
            var paper = from p in PaperRepository.Entities
                        join t in onlinetestRepository.Entities on p.test_paper_parent_id equals t.test_paper_id
                        join r in ExamResultRepository.Entities on p.test_paper_id equals r.test_paper_id
                        where t.onlinetest_id == test_id
                        where r.user_id == userid
                        select p;

            if (paper != null && paper.Count() > 0)
            {
                var paperid = paper.ToArray()[0].test_paper_id;
                Expression<Func<test_paper_question_set, bool>> whereExpression = (q => q.test_paper_id == paperid);
                question_setRepository.UnitOfWork.Delete(whereExpression);
                //context.test_paper_question_set_fortest.Delete(q => q.test_paper_id == paperid);
                Expression<Func<test_paper, bool>> paperExpression = (q => q.test_paper_id == paperid);
                PaperRepository.UnitOfWork.Delete(paperExpression);
                question_setRepository.UnitOfWork.Commit();
            }

            var result = ExamResultRepository.Entities.Where(q => q.onlinetest_id == test_id && q.user_id == userid).FirstOrDefault();
            if (result != null)
            {
                result.test_paper_id = paperID;
                question_setRepository.UnitOfWork.Commit();
                return true;
            }
            return false;
        }

        public void AddExchange(decimal test_id, int type, int receiveType, string receiver, int extendIntData)
        {
            test_exchange exchange = new test_exchange()
            {
                onlinetest_id = (int)test_id,
                type = type,
                receiveType = receiveType,
                receiver = receiver,
                content = null,
                extendIntData = extendIntData,
                handled = false,
                sendTime = DateTime.Now
            };
            exchangeRepository.UnitOfWork.Add(exchange);

            ////            insert into dbo.test_exchange
            ////(onlinetest_id,[type],receiveType,receiver,content,extendIntData,extendStringData,handled,sendTime )
            ////values(@test_id,@type,@receiveType,@recevier,@content,@extendIntData,@extendStringData,0,GETDATE())
            //Execute("dbo.sp_insert_exchange",
            //    new KeyValuePair<string, object>("@test_id", test_id),
            //    new KeyValuePair<string, object>("@type", type),
            //    new KeyValuePair<string, object>("@receiveType", receiveType),
            //    new KeyValuePair<string, object>("@recevier", receiver),
            //    new KeyValuePair<string, object>("@content", null),
            //    new KeyValuePair<string, object>("@extendIntData", extendIntData),
            //    new KeyValuePair<string, object>("@extendStringData", null));
        }
        public DataSet PaperBuilder(decimal test_id, decimal paper_id)
        {
            var ds = Query("dbo.sp_fetch_paper",
                new KeyValuePair<string, object>("@onlinetest_id", test_id),
                new KeyValuePair<string, object>("@test_paper_id", paper_id));

            return (ds);
        }
        public object ListExchange(decimal test_id, int receiveType, string receiver, DateTime? sendTime)
        {
            var ds = Query("dbo.sp_get_exchange",
                new KeyValuePair<string, object>("@test_id", test_id),
                new KeyValuePair<string, object>("@receiveType", receiveType),
                new KeyValuePair<string, object>("@recevier", receiver),
                new KeyValuePair<string, object>("@sendTime", sendTime));
            var tb = ds.Tables[0];
            var query = tb.AsEnumerable().Select(t => new { type = t.Field<int>("type"), extendInt = t.Field<int>("extendIntData"), id = t.Field<decimal>("id"), userid = t.Field<string>("receiver") }).ToArray();
            return query;
        }

        public bool RootReadPaper(decimal testid, string userid)
        {
            var result = true;
            try
            {
                var q = onlinetestRepository.GetSingle(to => to.onlinetest_id == testid);
                //&& q.test_method_set_id.Value == 0
                if (q.isReadPaper.Value == true)
                {
                    var readuser = Guid.Parse(q.created_by);
                    var query = UserRepository.GetSingle(e => e.UserID == readuser);

                    Execute("dbo.sp_autoReadPaper",
                new KeyValuePair<string, object>("@onlinetest_id", testid),
                new KeyValuePair<string, object>("@readerUser", query.Name),
                new KeyValuePair<string, object>("@userids", userid));
                }
            }
            catch (Exception esx)
            {
                result = false;
                string message = esx.Message;
            }
            return result;
        }

       
    }
}