using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.DataLogic.Common.CalendarManage;
using EMIS.ViewModel.Students.HighBaseTable;
using EMIS.ViewModel.EnrollManage.SpecialtyManage;
using EMIS.ViewModel.Students;
namespace EMIS.DataLogic.Common.Students.HighBaseTable
{
public class JuniorSpecialtyStudentCountDAL
{
public DictionaryItemRepository DictionaryItemRepository { get; set; }
public StudentRepository StudentRepository { get; set; }
public StudentProfileRepository StudentProfileRepository { get; set; }
public ClassmajorRepository ClassmajorRepository { get; set; }
public GrademajorRepository GrademajorRepository { get; set; }
public FacultymajorRepository FacultymajorRepository { get; set; }
public SpecialtyRepository SpecialtyRepository { get; set; }
public SchoolyearRepository SchoolyearRepository { get; set; }
public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
public EducationTypeSettingRepository EducationTypeSettingRepository { get; set; }
public SchoolYearDAL SchoolYearDAL { get; set; }
public GraduationApplyRepository GraduationApplyRepository { get; set; }
public StudentReportRepository StudentReportRepository { get; set; }
public IQueryable<JuniorSpecialtyStudentCountView> GetStudent()
{
var query = from student in StudentRepository.GetList(x => true)
join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
//join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
//equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.Special) on famajor.EducationID equals edutype.EducationID
group student by new
{
//student.Sex,
//edutype.EducationTypeID,
//special.SpecialtyID,
//famajor.TeacherIdentification,
famajor.StandardID,
famajor.EducationID,
famajor.LearnSystem,
famajor.LearningformID
} into g
select new JuniorSpecialtyStudentCountView
{
//SpecialtyID = special.SpecialtyID,
StandardID = g.Key.StandardID,
EducationID = g.Key.EducationID,
LearningformID = g.Key.LearningformID,
LearnSystem = g.Key.LearnSystem,
};
return query.Distinct();
}
public IQueryable<JuniorSpecialtyStudentCountView> GetInschoolCount(int? schoolYear)
{
var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault();
var query = from student in StudentRepository.Entities
join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true)
on student.InSchoolStatusID equals inschool.InSchoolStatusID
join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
//join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
//equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.Special) on famajor.EducationID equals edutype.EducationID
join grayear in SchoolyearRepository.Entities on new { SchoolcodeID = grmajor.SemesterID.Value, Years = grmajor.GradeID.Value } equals new { grayear.SchoolcodeID, grayear.Years }
//join report in
// (
// from report in StudentReportRepository.GetList(x => x.RecordStatus > (int)EMIS.ViewModel.SYS_STATUS.UNUSABLE
// && x.ReportStatusID == (int)EMIS.ViewModel.CF_ReportStatus.Havetoreportforduty)
// join schoolyear in SchoolyearRepository.Entities on report.SchoolyearID equals schoolyear.SchoolyearID
// where schoolyear.Years == schoolYear
// select report.UserID
// ).Distinct() on student.UserID equals report
group student by new
{
student.SexID,
edutype.EducationTypeID,
//special.SpecialtyID,
famajor.TeacherIdentification,
famajor.StandardID,
famajor.EducationID,
famajor.LearnSystem,
famajor.LearningformID,
SchoolyearNumID = ((schYear.Value - (schYear.SchoolcodeID == grmajor.SemesterID ? 0 : 1)) - grayear.Value) / 2 + 1
} into g
select new JuniorSpecialtyStudentCountView
{
Sex = g.Key.SexID,
//SpecialtyID = g.Key.SpecialtyID,
SchoolyearNumID = g.Key.SchoolyearNumID,
StandardID = g.Key.StandardID,
EducationID = g.Key.EducationID,
LearnSystem = g.Key.LearnSystem,
LearningformID = g.Key.LearningformID,
EducationTypeID = g.Key.EducationTypeID,
InschoolCount = g.Count()
};
return query;
}
//招生数
public IQueryable<JuniorSpecialtyStudentCountView> GetRecruitCount(int? schoolYear)
{
//var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().Years;
var query = from student in StudentRepository.Entities
join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true)
on student.InSchoolStatusID equals inschool.InSchoolStatusID
join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
//join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
//equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.Special) on famajor.EducationID equals edutype.EducationID
join entyear in SchoolyearRepository.GetList(x => x.Years == schoolYear) on recruit.EnteringSchoolYearID equals entyear.SchoolyearID
group student by new
{
student.SexID,
edutype.EducationTypeID,
//special.SpecialtyID,
famajor.TeacherIdentification,
famajor.StandardID,
famajor.EducationID,
famajor.LearnSystem,
famajor.LearningformID
} into g
select new JuniorSpecialtyStudentCountView
{
Sex = g.Key.SexID,
//SpecialtyID = g.Key.SpecialtyID,
StandardID = g.Key.StandardID,
EducationID = g.Key.EducationID,
LearnSystem = g.Key.LearnSystem,
LearningformID = g.Key.LearningformID,
EducationTypeID = g.Key.EducationTypeID,
RecruitCount = g.Count()
};
return query;
}
//春季招生数，普教需要
public IQueryable<JuniorSpecialtyStudentCountView> SpringRecruitCount(int? schoolYear)
{
//var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().Years;
var query = from student in StudentRepository.Entities
join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true)
on student.InSchoolStatusID equals inschool.InSchoolStatusID
join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
//join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
//equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.Special) on famajor.EducationID equals edutype.EducationID
join entyear in SchoolyearRepository.GetList(x => x.Years == schoolYear && x.SchoolcodeID == (int)EMIS.ViewModel.CF_Semester.Spring) on recruit.EnteringSchoolYearID equals entyear.SchoolyearID
group student by new
{
student.SexID,
edutype.EducationTypeID,
//special.SpecialtyID,
famajor.TeacherIdentification,
famajor.StandardID,
famajor.EducationID,
famajor.LearnSystem,
famajor.LearningformID
} into g
select new JuniorSpecialtyStudentCountView
{
Sex = g.Key.SexID,
//SpecialtyID = g.Key.SpecialtyID,
StandardID = g.Key.StandardID,
EducationID = g.Key.EducationID,
LearnSystem = g.Key.LearnSystem,
LearningformID = g.Key.LearningformID,
EducationTypeID = g.Key.EducationTypeID,
SpringRecruitCount = g.Count()
};
return query;
}
//预计毕业数
public IQueryable<JuniorSpecialtyStudentCountView> PlanGraduationCount(int? schoolYear)
{
//var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().Years;
var query = from student in StudentRepository.Entities
join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true)
on student.InSchoolStatusID equals inschool.InSchoolStatusID
join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.Special) on famajor.EducationID equals edutype.EducationID
//年级专业毕业学年等于当前学年
join grayear in SchoolyearRepository.GetList(x => x.Years == schoolYear) on grmajor.GraduateSchoolyearID equals grayear.SchoolyearID
group student by new
{
student.SexID,
edutype.EducationTypeID,
//special.SpecialtyID,
famajor.TeacherIdentification,
famajor.StandardID,
famajor.EducationID,
famajor.LearnSystem,
famajor.LearningformID
} into g
select new JuniorSpecialtyStudentCountView
{
Sex = g.Key.SexID,
//SpecialtyID = g.Key.SpecialtyID,
StandardID = g.Key.StandardID,
EducationID = g.Key.EducationID,
LearnSystem = g.Key.LearnSystem,
LearningformID = g.Key.LearningformID,
EducationTypeID = g.Key.EducationTypeID,
PlanGraduationCount = g.Count()
};
return query;
}
//应届毕业生数,上一学年毕业的学生，并且为正常的预计毕业学期（如3年制的学生，从入学学期到毕业刚好6个学期）
public IQueryable<JuniorSpecialtyStudentCountView> GetGraduatingCount(int? schoolYear)
{
var query = from student in StudentRepository.GetList(x => x.StudentStatus == (int)EMIS.ViewModel.CF_StudentStatus.Graduation)
join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
join enteringSchoolYear in SchoolyearRepository.Entities on recruit.EnteringSchoolYearID equals enteringSchoolYear.SchoolyearID
join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.Special) on famajor.EducationID equals edutype.EducationID
join gradApply in
(
from graduationApply in GraduationApplyRepository.Entities
join schoolyear in SchoolyearRepository.Entities on graduationApply.ApplySchoolyearID equals schoolyear.SchoolyearID
group schoolyear by new { graduationApply.UserID, schoolyear.Years } into g
select new { UserID = g.Key.UserID.Value, graduationYear = g.Max(x => x.Years), schoolYearValue = g.Max(x => x.Value) }
) on new
{
student.UserID,
schoolYear = schoolYear.Value,
schoolYearValue = enteringSchoolYear.Value + (int)(famajor.LearnSystem * 2) - 1
} equals new
{
gradApply.UserID,
schoolYear = gradApply.graduationYear + 1,
gradApply.schoolYearValue
}
group student by new
{
student.SexID,
edutype.EducationTypeID,
//special.SpecialtyID,
famajor.TeacherIdentification,
famajor.StandardID,
famajor.EducationID,
famajor.LearnSystem,
famajor.LearningformID
} into g
select new JuniorSpecialtyStudentCountView
{
Sex = g.Key.SexID,
//SpecialtyID = g.Key.SpecialtyID,
StandardID = g.Key.StandardID,
EducationID = g.Key.EducationID,
LearnSystem = g.Key.LearnSystem,
LearningformID = g.Key.LearningformID,
EducationTypeID = g.Key.EducationTypeID,
GraduatingCount = g.Count()
};
return query;
}
//毕业数，上一学年毕业的学生数，学籍状态为毕业，并且最后一次毕业申请是上一年
public IQueryable<JuniorSpecialtyStudentCountView> GetGraduationCount(int? schoolYear)
{
var query = from student in StudentRepository.GetList(x => x.StudentStatus == (int)EMIS.ViewModel.CF_StudentStatus.Graduation)
//join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
//join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
//join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
//equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.Special) on famajor.EducationID equals edutype.EducationID
//最后一次毕业申请是上一年
//join gradApply in GraduationApplyRepository.Entities on student.UserID equals gradApply.UserID
//join grayear in SchoolyearRepository.GetList(x => x.Years == schoolYear - 1) on gradApply.SchoolyearID equals grayear.SchoolyearID
join gradApply in
(
from graduationApply in GraduationApplyRepository.Entities
join schoolyear in SchoolyearRepository.Entities on graduationApply.ApplySchoolyearID equals schoolyear.SchoolyearID
group schoolyear by new { graduationApply.UserID, schoolyear.Years } into g
select new { UserID = g.Key.UserID.Value, graduationYear = g.Max(x => x.Years) }
) on new { student.UserID, schoolYear = schoolYear.Value } equals new { gradApply.UserID, schoolYear = gradApply.graduationYear + 1 }
group student by new
{
student.SexID,
edutype.EducationTypeID,
//special.SpecialtyID,
famajor.TeacherIdentification,
famajor.StandardID,
famajor.EducationID,
famajor.LearnSystem,
famajor.LearningformID
} into g
select new JuniorSpecialtyStudentCountView
{
Sex = g.Key.SexID,
//SpecialtyID = g.Key.SpecialtyID,
StandardID = g.Key.StandardID,
EducationID = g.Key.EducationID,
LearnSystem = g.Key.LearnSystem,
LearningformID = g.Key.LearningformID,
EducationTypeID = g.Key.EducationTypeID,
GraduationCount = g.Count()
};
return query;
}
}
}