JW/学校扩展文件/EMIS.ExtensionLogic.GZZY/DataLogic/ScoreManage/StudentScoreDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.DataLogic.Repositories;

namespace EMIS.ExtensionLogic.DataLogic.ScoreManage
{
    public class StudentScoreDAL : EMIS.DataLogic.ScoreManage.StudentScoreDAL
    {
        public CourseTypeSelectCourseTypeRepository CourseTypeSelectCourseTypeRepository { get; set; }

        public override IQueryable<ER_SubmitedScore> GetLastSubmittedScoreBeforDelete(Expression<Func<ER_SubmitedScore, bool>> exp)
        {
            var maxSubmitedScoreList = (
                                        from ss in submitedScoreRepository.Entities
                                        join nss in submitedScoreRepository.GetList(exp)
                                            on new { ss.UserID, ss.CoursematerialID }
                                            equals new { nss.UserID, nss.CoursematerialID }
                                        join ctsc in CourseTypeSelectCourseTypeRepository.Entities
                                        on ss.CourseTypeID equals ctsc.CourseTypeID into dctsc
                                        from ctsc in dctsc.DefaultIfEmpty()
                                        join mss in
                                            (from ssl in submitedScoreRepository.Entities
                                             join nss in submitedScoreRepository.GetList(exp)
                                                on new { ssl.UserID, ssl.CoursematerialID }
                                                equals new { nss.UserID, nss.CoursematerialID }
                                             join ctsc1 in CourseTypeSelectCourseTypeRepository.Entities
                                                on ssl.CourseTypeID equals ctsc1.CourseTypeID into dctsc1
                                             from ctsc1 in dctsc1.DefaultIfEmpty()
                                             where ssl.SubmitedScoreID != nss.SubmitedScoreID
                                             group new { Score = ssl, SelectCourseType = ctsc1 } by new { ssl.UserID, ssl.CoursematerialID }
                                                 into g
                                                 select new
                                                 {
                                                     g.Key.UserID,
                                                     g.Key.CoursematerialID,
                                                     IsRequired = g.Max(x => (x.SelectCourseType.IsRequired == true) ? 1 : 0) == 1,
                                                     MaxCreateTime = g.Max(x => (x.SelectCourseType.IsRequired == true) ? 1 : 0) == 1 ? g.Max(x => (x.SelectCourseType.IsRequired == true) ? x.Score.CreateTime : null) : g.Max(x => x.Score.CreateTime)
                                                 })
                                            on new { ss.UserID, ss.CoursematerialID, IsRequired = ctsc.IsRequired.Value, ss.CreateTime }
                                            equals new { mss.UserID, mss.CoursematerialID, IsRequired = mss.IsRequired, CreateTime = mss.MaxCreateTime }
                                        where ss.SubmitedScoreID != nss.SubmitedScoreID
                                        group ss by new { ss.UserID, ss.CoursematerialID, ss.CreateTime } into g
                                        select (g.Max(x => x.SubmitedScoreID.ToString()))
                                        );

            //这个算法，需要找条重录完后还有成绩的学生来试试
            var lastSubmittedScoreList = (from mss in maxSubmitedScoreList
                                          join ssl in submitedScoreRepository.Entities
                                              on mss equals ssl.SubmitedScoreID.ToString()
                                          select ssl);

            return lastSubmittedScoreList;
        }

        public override IQueryable<ER_SubmitedScore> GetLastSubmittedScore(Expression<Func<ER_SubmitedScore, bool>> exp)
        {
            var maxSubmitedScoreList = (
                                        from ss in submitedScoreRepository.Entities
                                        join ctsc in CourseTypeSelectCourseTypeRepository.Entities
                                        on ss.CourseTypeID equals ctsc.CourseTypeID into dctsc
                                        from ctsc in dctsc.DefaultIfEmpty()
                                        join mss in
                                            (
                                                from ssl in submitedScoreRepository.GetList(exp)
                                                join ssm in submitedScoreRepository.Entities on new
                                                {
                                                    ssl.UserID,
                                                    ssl.CoursematerialID
                                                } equals new { ssm.UserID, ssm.CoursematerialID }
                                                join ctsc1 in CourseTypeSelectCourseTypeRepository.Entities
                                                on ssm.CourseTypeID equals ctsc1.CourseTypeID into dctsc1
                                                from ctsc1 in dctsc1.DefaultIfEmpty()
                                                group new { Score = ssm, SelectCourseType = ctsc1 }
                                                by new { ssm.UserID, ssm.CoursematerialID }
                                                    into g
                                                    select new
                                                    {
                                                        g.Key.UserID,
                                                        g.Key.CoursematerialID,
                                                        IsRequired = g.Max(x => x.SelectCourseType.IsRequired.Value ? 1 : 0) == 1,
                                                        MaxCreateTime = (g.Max(x => x.SelectCourseType.IsRequired.Value ? 1 : 0) == 1) ? g.Max(x => (x.SelectCourseType.IsRequired.Value) ? x.Score.CreateTime : null) : g.Max(x => x.Score.CreateTime)
                                                    }
                                            )
                                        on
                                        new { ss.UserID, ss.CoursematerialID, IsRequired = ctsc.IsRequired.Value, ss.CreateTime }
                                        equals
                                        new { mss.UserID, mss.CoursematerialID, IsRequired = mss.IsRequired, CreateTime = mss.MaxCreateTime }
                                        group ss by new { ss.UserID, ss.CoursematerialID, ss.CreateTime }
                                            into g
                                            select g.Max(x => x.SubmitedScoreID.ToString())
                                        );

            var lastSubmittedScoreList = (from ssl in submitedScoreRepository.Entities
                                          join mss in maxSubmitedScoreList
                                              on ssl.SubmitedScoreID.ToString() equals mss
                                          select ssl);

            return lastSubmittedScoreList;
        }
    }
}