using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using EMIS.Entities;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.CultureplanManage.PlanManagement;
using EMIS.ViewModel.GraduationManage.GraduationSetting;
namespace EMIS.ExtensionLogic.CommonDataLogic.GraduationManage
{
public class GraduationStandardDAL : EMIS.DataLogic.GraduationManage.GraduationSetting.GraduationStandardDAL
{
public SpecialtyRepository SpecialtyRepository { get; set; }
///
/// 查询对应的毕业标准信息View（门数、各类型学分统计：专业计划、执行计划）
/// 注：由于学分制实行的程度不同，目前只对专业计划进行统计
/// 此为成教版本
///
///
///
///
///
public override IQueryable GetGraduationStandardViewQueryable(Expression> expGraduationStandard,
Expression> expExecutablePlan, Expression> expStudent)
{
var query = from gs in GraduationStandardRepository.GetList(expGraduationStandard)
join gr in GrademajorRepository.Entities
on gs.GrademajorID equals gr.GrademajorID
join fa in FacultymajorRepository.Entities
on gr.FacultymajorID equals fa.FacultymajorID
join col in CollegeRepository.Entities
on fa.CollegeID equals col.CollegeID
join cam in CampusRepository.Entities
on col.CampusID equals cam.CampusID
join spe in SpecialtyRepository.Entities
on new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem }
equals new { spe.StandardID, spe.EducationID, spe.LearningformID, spe.LearnSystem }
join sye in SchoolyearRepository.Entities
on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
equals new { SchoolyearID = sye.Years, SchoolcodeID = sye.SchoolcodeID } into tempsyenter
from syenter in tempsyenter.DefaultIfEmpty()
join stugr in
(
//年级专业对应的学生人数（区分在校、非在校）
from stu in StudentRepository.GetList(expStudent)
join cl in ClassmajorRepository.Entities
on stu.ClassmajorID equals cl.ClassmajorID
group cl by cl.GrademajorID into g
select new
{
GrademajorID = g.Key,
StudentCount = g.Count()
}
)
on gr.GrademajorID equals stugr.GrademajorID into tempstugr
from gsstugr in tempstugr.DefaultIfEmpty()
join spgr in
(
//专业计划(计划门数、计划学分、必修、限选、任选学分统计)
from spct in
(
from sp in SpecialtyPlanRepository.Entities
join ct in CourseTypeSelectCourseTypeRepository.Entities
on sp.CourseTypeID equals ct.CourseTypeID into tempct
from spct in tempct.DefaultIfEmpty()
select new
{
StartSchoolyearID = sp.StartSchoolyearID,
SpecialtyID = sp.SpecialtyID,
CourseTypeID = sp.CourseTypeID,
Credit = sp.EM_SpecialtyPlanTeachingSetting.Credit ?? 0,
IsRequired = spct.IsRequired ?? true,
IsOptionalCourse = spct.IsOptionalCourse ?? false,
IsFreeSelectionCourse = spct.IsFreeSelectionCourse ?? false,
}
)
group spct by new { spct.StartSchoolyearID, spct.SpecialtyID } into g
select new
{
StartSchoolyearID = g.Key.StartSchoolyearID,
SpecialtyID = g.Key.SpecialtyID,
PlanCourseCount = g.Count(),
SpecialtyRequireCreditTotal = g.Sum(x => x.IsRequired ? x.Credit : 0),
OptionalCreditTotal = g.Sum(x => x.IsOptionalCourse ? x.Credit : 0),
FreeSelectionCreditTotal = g.Sum(x => x.IsFreeSelectionCourse ? x.Credit : 0),
SpecialtyCreditTotal = g.Sum(x => x.Credit)
}
)
on new { StartSchoolyearID = syenter.SchoolyearID, SpecialtyID = spe.SpecialtyID }
equals new { StartSchoolyearID = spgr.StartSchoolyearID.Value, SpecialtyID = spgr.SpecialtyID.Value } into tempspgr
from gsspgr in tempspgr.DefaultIfEmpty()
join epgr in
(
//执行计划(执行门数、执行学分)
from ep in ExecutablePlanRepository.GetList(expExecutablePlan)
join ct in CourseTypeSelectCourseTypeRepository.Entities
on ep.CourseTypeID equals ct.CourseTypeID into tempct
from epct in tempct.DefaultIfEmpty()
group ep by ep.GrademajorID into g
select new
{
GrademajorID = g.Key,
ExecCourseCount = g.Count(),
ExecutableCreditTotal = g.Sum(x => x.EM_ExecutablePlanTeachingSetting.Credit ?? 0)
}
)
on gr.GrademajorID equals epgr.GrademajorID into tempepgr
from gsepgr in tempepgr.DefaultIfEmpty()
select new GraduationStandardView
{
GraduationStandardID = gs.GraduationStandardID,
GrademajorID = gs.GrademajorID,
GrademajorCode = gr.Code,
GrademajorName = gr.Name,
FacultymajorID = fa.FacultymajorID,
FacultymajorNo = fa.Code,
FacultymajorName = fa.Name,
CollegeID = fa.CollegeID,
CollegeNo = col.No,
CollegeName = col.Name,
CampusID = col.CampusID,
CampusNo = cam.No,
CampusName = cam.Name,
SchoolyearID = gr.GradeID,
SchoolcodeID = gr.SemesterID,
EnteringSchoolYearID = syenter.SchoolyearID,
EnteringSchoolYearCode = syenter.Code,
GraduatingSemesterID = gr.CF_Schoolyear.SchoolyearID,
GraduatingSemesterCode = gr.CF_Schoolyear.Code,
StandardID = fa.StandardID,
EducationID = fa.EducationID,
LearningformID = fa.LearningformID,
LearnSystem = fa.LearnSystem,
StudentCount = gsstugr.StudentCount == null ? 0 : gsstugr.StudentCount,
PlanCourseCount = gsspgr.PlanCourseCount == null ? 0 : gsspgr.PlanCourseCount,
SpecialtyRequireCreditTotal = gsspgr.SpecialtyRequireCreditTotal == null ? 0 : gsspgr.SpecialtyRequireCreditTotal,
OptionalCreditTotal = gsspgr.OptionalCreditTotal == null ? 0 : gsspgr.OptionalCreditTotal,
FreeSelectionCreditTotal = gsspgr.FreeSelectionCreditTotal == null ? 0 : gsspgr.FreeSelectionCreditTotal,
SpecialtyCreditTotal = gsspgr.SpecialtyCreditTotal == null ? 0 : gsspgr.SpecialtyCreditTotal,
ExecCourseCount = gsepgr.ExecCourseCount == null ? 0 : gsepgr.ExecCourseCount,
ExecutableCreditTotal = gsepgr.ExecutableCreditTotal == null ? 0 : gsepgr.ExecutableCreditTotal,
RequireCourseCount = gs.RequireCourseCount,
GraduationCredit = gs.GraduationCredit,
RecordStatus = gs.RecordStatus,
CreateUserID = gs.CreateUserID,
CreateTime = gs.CreateTime,
ModifyUserID = gs.ModifyUserID,
ModifyTime = gs.ModifyTime
};
return query;
}
///
/// 查询毕业标准对应的专业计划信息
/// 此为成教版本
///
///
///
public override IQueryable GetSpecialtyPlanViewQueryable(Expression> exp)
{
var query = from gs in GraduationStandardRepository.GetList(exp)
join gr in GrademajorRepository.Entities
on gs.GrademajorID equals gr.GrademajorID
join fa in FacultymajorRepository.Entities
on gr.FacultymajorID equals fa.FacultymajorID
join col in CollegeRepository.Entities
on fa.CollegeID equals col.CollegeID
join spe in SpecialtyRepository.Entities
on new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem }
equals new { spe.StandardID, spe.EducationID, spe.LearningformID, spe.LearnSystem }
join sye in SchoolyearRepository.Entities
on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
equals new { SchoolyearID = sye.Years, SchoolcodeID = sye.SchoolcodeID } into tempsyenter
from syenter in tempsyenter.DefaultIfEmpty()
join sp in SpecialtyPlanRepository.Entities
on new { StartSchoolyearID = syenter.SchoolyearID, SpecialtyID = spe.SpecialtyID }
equals new { StartSchoolyearID = sp.StartSchoolyearID.Value, SpecialtyID = sp.SpecialtyID.Value }
join sy in SchoolyearRepository.Entities
on sp.SchoolyearID equals sy.SchoolyearID
join cou in CoursematerialRepository.Entities
on sp.CoursematerialID equals cou.CoursematerialID
join depcou in DepartmentRepository.Entities
on sp.DepartmentID equals depcou.DepartmentID into tempdepcou
from spdepcou in tempdepcou.DefaultIfEmpty()
select new SpecialtyPlanView
{
SpecialtyPlanID = sp.SpecialtyPlanID,
SchoolyearID = sp.SchoolyearID,
SchoolyearCode = sy.Code,
GrademajorID = gr.GrademajorID,
GrademajorCode = gr.Code,
GrademajorName = gr.Name,
CollegeID = fa.CollegeID,
CollegeCode = col.No,
CollegeName = col.Name,
CoursematerialID = sp.CoursematerialID,
CourseCode = cou.CourseCode,
CourseName = cou.CourseName,
CourseStructureID = sp.CourseStructureID,
CourseCategoryID = sp.CourseCategoryID,
CourseTypeID = sp.CourseTypeID,
CourseQualityID = sp.CourseQualityID,
ResultTypeID = sp.ResultTypeID,
PracticeTypeID = sp.PracticeTypeID,
ExaminationModeID = sp.ExaminationModeID,
TeachinglanguageID = sp.TeachinglanguageID,
CourseFineID = sp.CourseFineID,
IsSpecialtycore = sp.IsSpecialtycore ?? false,
IsCooperation = sp.IsCooperation ?? false,
IsRequired = sp.IsRequired ?? false,
IsElective = sp.IsElective ?? false,
IsNetworkCourse = sp.IsNetworkCourse ?? false,
IsMainCourse = sp.IsMainCourse ?? false,
Credit = sp.EM_SpecialtyPlanTeachingSetting.Credit ?? 0,
TheoryCourse = sp.EM_SpecialtyPlanTeachingSetting.TheoryCourse ?? 0,
Practicehours = sp.EM_SpecialtyPlanTeachingSetting.Practicehours ?? 0,
Trialhours = sp.EM_SpecialtyPlanTeachingSetting.Trialhours ?? 0,
Totalhours = (sp.EM_SpecialtyPlanTeachingSetting.TheoryCourse ?? 0) + (sp.EM_SpecialtyPlanTeachingSetting.Practicehours ?? 0),
SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
SchoolcodeID = sy.SchoolcodeID,
StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
DepartmentID = sp.DepartmentID,
DepartmentCode = spdepcou.No,
DepartmentName = spdepcou.Name,
HandleModeID = sp.HandleModeID,
Remark = sp.Remark,
RecordStatus = sp.RecordStatus,
CreateUserID = sp.CreateUserID,
CreateTime = sp.CreateTime,
ModifyUserID = sp.ModifyUserID,
ModifyTime = sp.ModifyTime
};
return query;
}
}
}