LIOT/EMIS.CommonLogic/UniversityInformationManage/Specialtyclass/FacultymajorServices.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.Entities;
using Bowin.Common.Linq.Entity;
using EMIS.DataLogic.Common.Specialtyclass;
using EMIS.ViewModel.Specialtyclass;
using EMIS.ViewModel;
using System.Linq.Expressions;
using EMIS.CommonLogic.Students;
using Bowin.Common.Linq;
using EMIS.Utility;
using EMIS.ViewModel.SystemView;
using Bowin.Common.Utility;
using System.Text.RegularExpressions;
using EMIS.ViewModel.Cache;
using EMIS.ViewModel.Students;

namespace EMIS.CommonLogic.Specialtyclass
{
    public class FacultymajorServices : BaseServices, IFacultymajorServices
    {
        public FacultymajorDAL FacultymajorDAL { get; set; }

        /// <summary>
        /// 查询院系专业信息View
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="campusID"></param>
        /// <param name="collegeID"></param>
        /// <param name="standardID"></param>
        /// <param name="educationID"></param>
        /// <param name="learningformID"></param>
        /// <param name="learnSystem"></param>
        /// <param name="scienceclassID"></param>
        /// <param name="inSchoolStatus"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<FacultymajorView> GetFacultymajorViewGrid(ConfiguretView configuretView, Guid? campusID, Guid? collegeID, 
                int? standardID, int? educationID, int? learningformID, string learnSystem, int? scienceclassID, 
                int? inSchoolStatus, int pageIndex, int pageSize)
        {
            //院系专业信息
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (collegeID.HasValue)
            {
                //院系所
                expFacultymajor = expFacultymajor.And(x => x.CollegeID == collegeID);
            }
            if (standardID.HasValue)
            {
                //专业ID(Value)
                expFacultymajor = expFacultymajor.And(x => x.StandardID == standardID);
            }
            if (educationID.HasValue)
            {
                //培养层次
                expFacultymajor = expFacultymajor.And(x => x.EducationID == educationID);
            }
            if (learningformID.HasValue)
            {
                //学习形式
                expFacultymajor = expFacultymajor.And(x => x.LearningformID == learningformID);
            }
            if (!string.IsNullOrEmpty(learnSystem) && learnSystem != "-1")
            {
                //学制
                var LearnSystems = Convert.ToDecimal(learnSystem);
                expFacultymajor = expFacultymajor.And(x => x.LearnSystem == LearnSystems);
            }
            if (scienceclassID.HasValue)
            {
                //专业科类
                expFacultymajor = expFacultymajor.And(x => x.ScienceclassID == scienceclassID);
            }

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.GetFacultymajorViewQueryable(expFacultymajor, expStudent);

            if (campusID.HasValue)
            {
                //校区
                query = query.Where(x => x.CampusID == campusID);
            }
            //if (inSchoolStatus != null && inSchoolStatus > -1)
            //{
            //    //排除人数为0的信息
            //    query = query.Where(x => x.StudentCount > 0);
            //}

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            return this.GetQueryByDataRangeByCollege(query)
                    .OrderBy(x => x.CollegeCode.Length).ThenBy(x => x.CollegeCode)
                    .ThenBy(x => x.StandardID).ThenBy(x => x.Code.Length).ThenBy(x => x.Code)
                    .ToGridResultSet<FacultymajorView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询院系专业信息List
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="campusID"></param>
        /// <param name="collegeID"></param>
        /// <param name="standardID"></param>
        /// <param name="educationID"></param>
        /// <param name="learningformID"></param>
        /// <param name="learnSystem"></param>
        /// <param name="scienceclassID"></param>
        /// <param name="inSchoolStatus"></param>
        /// <returns></returns>
        public IList<FacultymajorView> GetFacultymajorViewList(ConfiguretView configuretView, Guid? campusID, Guid? collegeID, 
                int? standardID, int? educationID, int? learningformID, string learnSystem, 
                int? scienceclassID, int? inSchoolStatus)
        {
            //院系专业信息
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (collegeID.HasValue)
            {
                //院系所
                expFacultymajor = expFacultymajor.And(x => x.CollegeID == collegeID);
            }
            if (standardID.HasValue)
            {
                //专业ID(Value)
                expFacultymajor = expFacultymajor.And(x => x.StandardID == standardID);
            }
            if (educationID.HasValue)
            {
                //培养层次
                expFacultymajor = expFacultymajor.And(x => x.EducationID == educationID);
            }
            if (learningformID.HasValue)
            {
                //学习形式
                expFacultymajor = expFacultymajor.And(x => x.LearningformID == learningformID);
            }
            if (!string.IsNullOrEmpty(learnSystem) && learnSystem != "-1")
            {
                //学制
                var LearnSystems = Convert.ToDecimal(learnSystem);
                expFacultymajor = expFacultymajor.And(x => x.LearnSystem == LearnSystems);
            }
            if (scienceclassID.HasValue)
            {
                //专业科类
                expFacultymajor = expFacultymajor.And(x => x.ScienceclassID == scienceclassID);
            }

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.GetFacultymajorViewQueryable(expFacultymajor, expStudent);

            if (campusID.HasValue)
            {
                //校区
                query = query.Where(x => x.CampusID == campusID);
            }
            //if (inSchoolStatus != null && inSchoolStatus > -1)
            //{
            //    //排除人数为0的信息
            //    query = query.Where(x => x.StudentCount > 0);
            //}

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            return this.GetQueryByDataRangeByCollege(query)
                    .OrderBy(x => x.CollegeCode.Length).ThenBy(x => x.CollegeCode)
                    .ThenBy(x => x.StandardID).ThenBy(x => x.Code.Length).ThenBy(x => x.Code)
                    .ToList();
        }

        /// <summary>
        /// 根据院系所ID、专业ID(Value)查询对应的院系专业信息
        /// </summary>
        /// <param name="collegeID"></param>
        /// <param name="standardID"></param>
        /// <returns></returns>
        public List<FacultymajorView> GetFacultymajorNameList(Guid? collegeID, int? standardID)
        {
            List<FacultymajorView> FacultymajorList = new List<FacultymajorView>();

            var query = FacultymajorDAL.GetFacultymajorViewQueryable(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }
            if (standardID.HasValue)
            {
                query = query.Where(x => x.StandardID == standardID);
            }

            var list = this.GetQueryByDataRangeByCollege(query)
                    .OrderBy(x => x.CollegeCode.Length).ThenBy(x => x.CollegeCode)
                    .ThenBy(x => x.StandardID).ThenBy(x => x.Code.Length).ThenBy(x => x.Code)
                    .ToList();

            //foreach (var f in list)
            //{
            //    FacultymajorView Facultymajor = new FacultymajorView();
            //    Facultymajor.Name = f.Name;
            //    Facultymajor.FacultymajorID = f.FacultymajorID;
            //    Facultymajor.CollegeName = f.CollegeName;
            //    var faculty = FacultymajorList.Where(x => x.FacultymajorID == Facultymajor.FacultymajorID).FirstOrDefault();
            //    if (faculty == null)
            //    {
            //        FacultymajorList.Add(Facultymajor);
            //    }
            //}

            return list;
        }

        /// <summary>
        /// 查询全部院系专业信息FacultymajorView(无数据范围)
        /// </summary>
        /// <returns></returns>
        public List<CF_Facultymajor> GetFacultymajorList()
        {
            //院系专业信息
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.FacultymajorRepository.GetList(expFacultymajor).ToList();

            return query.OrderBy(x => x.Code.Length).ThenBy(x => x.Code).ToList();
        }

        /// <summary>
        /// 查询院系专业对应的专业信息StandardView（去重）
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="collegeID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<StandardView> GetStandardViewList(ConfiguretView configuretView, Guid? collegeID, int? pageIndex, int? pageSize)
        {
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.GetFacultymajorViewQueryable(expFacultymajor);
            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            query = this.GetQueryByDataRangeByCollege(query);

            var facultymajorIDList = query.Select(x => x.FacultymajorID).ToList();

            var standardQuery = FacultymajorDAL
                    .GetStandardQueryable(x => facultymajorIDList.Contains(x.FacultymajorID));

            return standardQuery.OrderBy(x => x.StandardID)
                    .ToGridResultSet(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询院系专业对应的学制信息（去重）
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="learnSystem"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<FacultymajorView> GetLearnSystemViewList(ConfiguretView configuretView, string learnSystem, int pageIndex, int pageSize)
        {
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.GetLearnSystemQueryable(expFacultymajor);
            if (!string.IsNullOrEmpty(learnSystem))
            {
                var LearnSystems = Convert.ToDecimal(learnSystem);
                query = query.Where(x => x.LearnSystem == LearnSystems);
            }
            if (configuretView.ConditionValue != null && configuretView.ConditionValue != "")
            {
                var learn = Convert.ToDecimal(configuretView.ConditionValue);
                query = query.Where(x => x.LearnSystem == learn);
            }

            return query.OrderBy(x => x.LearnSystem)
                    .ToGridResultSet<FacultymajorView>(pageIndex, pageSize);
        
        }

        /// <summary>
        /// 根据院系所ID查询对应的院系专业信息(带数据范围)
        /// </summary>
        /// <param name="collegeID"></param>
        /// <returns></returns>
        public IList<FacultymajorView> GetAllFacultymajorViewList(Guid? collegeID)
        {
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.GetFacultymajorViewQueryable(expFacultymajor);
            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }

            return this.GetQueryByDataRangeByCollege(query)
                .OrderBy(x => x.CollegeCode.Length).ThenBy(x => x.CollegeCode)
                .ThenBy(x => x.StandardID).ThenBy(x => x.Code.Length).ThenBy(x => x.Code)
                .ToList();
        }

        /// <summary>
        /// 查询对应的院系专业信息CF_Facultymajor
        /// </summary>
        /// <param name="facultymajorID"></param>
        /// <returns></returns>
        public CF_Facultymajor GetFacultymajorInfo(Guid? facultymajorID)
        {
            try
            {
                var query = FacultymajorDAL.FacultymajorRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
                if (facultymajorID.HasValue)
                {
                    query = query.Where(x => x.FacultymajorID == facultymajorID);
                }
                return query.SingleOrDefault();
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 查询对应的院系专业信息CF_Facultymajor
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public CF_Facultymajor GetFacultymajorInfoByCode(string code)
        {
            try
            {
                var query = FacultymajorDAL.FacultymajorRepository.GetSingle(x => x.Code == code);

                return query;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 查询对应的院系专业信息FacultymajorView
        /// </summary>
        /// <param name="facultymajorID"></param>
        /// <returns></returns>
        public FacultymajorView GetFacultymajorView(Guid? facultymajorID)
        {
            try
            {
                var query = FacultymajorDAL.GetFacultymajorViewQueryable(x => x.FacultymajorID == facultymajorID);

                return query.SingleOrDefault();
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            
        }

        /// <summary>
        /// 编辑（新增、修改，业务主键：院系所ID、院系专业编号、专业ID(Value)、培养层次、学习形式、学制唯一）
        /// </summary>
        /// <param name="facultymajorView"></param>
        public void FacultymajorEdit(FacultymajorView facultymajorView)
        {
            try
            {
                CF_Facultymajor facultymajor = new CF_Facultymajor();
                var existsFacultymajor = FacultymajorDAL.FacultymajorRepository
                    .GetSingle(x => x.FacultymajorID != facultymajorView.FacultymajorID
                    && x.Code == facultymajorView.Code
                    && x.CollegeID == facultymajorView.CollegeID
                    && x.StandardID == facultymajorView.StandardID && x.LearnSystem == facultymajorView.LearnSystem
                    && x.EducationID == facultymajorView.EducationID && x.LearningformID == facultymajorView.LearningformID
                    );
                if (existsFacultymajor != null)
                {
                    throw new Exception("院系专业编号、" + RSL.Get("College") + "、专业名称、学制、" + RSL.Get("EducationID") + "、学习形式已存在。");
                }
                if (facultymajorView.FacultymajorID == null || facultymajorView.FacultymajorID == Guid.Empty)
                {
                    //新增
                    facultymajor.FacultymajorID = Guid.NewGuid();
                    facultymajor.CollegeID = facultymajorView.CollegeID;
                    facultymajor.Code = facultymajorView.Code.Trim();
                    facultymajor.Name = facultymajorView.Name;
                    facultymajor.Abbreviation = facultymajorView.Abbreviation;
                    facultymajor.EnglishName = facultymajorView.EnglishName;
                    facultymajor.StandardID = facultymajorView.StandardID;
                    facultymajor.EducationID = facultymajorView.EducationID;
                    facultymajor.LearningformID = facultymajorView.LearningformID;
                    facultymajor.LearnSystem = facultymajorView.LearnSystem;
                    facultymajor.ScienceclassID = facultymajorView.ScienceclassID;
                    facultymajor.LearningstyleID = facultymajorView.LearningstyleID;
                    facultymajor.LearnPositionID = facultymajorView.LearnPositionID;
                    facultymajor.TeacherIdentification = facultymajorView.TeacherIdentification;
                    facultymajor.SetTime = facultymajorView.SetTime;
                    facultymajor.Remark = facultymajorView.Remark;
                    SetNewStatus(facultymajor);
                    UnitOfWork.Add(facultymajor);
                }
                else
                {
                    //修改
                    facultymajor = this.GetFacultymajorInfo(facultymajorView.FacultymajorID);
                    facultymajor.CollegeID = facultymajorView.CollegeID;
                    facultymajor.Code = facultymajorView.Code.Trim();
                    facultymajor.Name = facultymajorView.Name;
                    facultymajor.Abbreviation = facultymajorView.Abbreviation;
                    facultymajor.EnglishName = facultymajorView.EnglishName;
                    facultymajor.StandardID = facultymajorView.StandardID;
                    facultymajor.EducationID = facultymajorView.EducationID;
                    facultymajor.LearningformID = facultymajorView.LearningformID;
                    facultymajor.LearnSystem = facultymajorView.LearnSystem;
                    facultymajor.ScienceclassID = facultymajorView.ScienceclassID;
                    facultymajor.LearningstyleID = facultymajorView.LearningstyleID;
                    facultymajor.LearnPositionID = facultymajorView.LearnPositionID;
                    facultymajor.TeacherIdentification = facultymajorView.TeacherIdentification;
                    facultymajor.SetTime = facultymajorView.SetTime;
                    facultymajor.Remark = facultymajorView.Remark;
                    SetModifyStatus(facultymajor);
                }

                UnitOfWork.Commit();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="facultymajorIDList"></param>
        /// <returns></returns>
        public bool FacultymajorDelete(List<Guid?> facultymajorIDList)
        {
            try
            {
                UnitOfWork.Delete<CF_Facultymajor>(x => facultymajorIDList.Contains(x.FacultymajorID));
                return true;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 查询院系专业对应的年级专业信息GrademajorView(带对应的在校状态学生人数)
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="facultymajorID"></param>
        /// <param name="inSchoolStatus"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<GrademajorView> GetGrademajorViewGrid(ConfiguretView configuretView, Guid? facultymajorID, 
                int? inSchoolStatus, int pageIndex, int pageSize)
        {
            //院系专业信息
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expFacultymajor = expFacultymajor.And(x => x.FacultymajorID == facultymajorID);

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.GetGrademajorViwQueryable(expFacultymajor, expStudent);

            //if (inSchoolStatus != null && inSchoolStatus > -1)
            //{
            //    //排除人数为0的信息
            //    query = query.Where(x => x.StudentCount > 0);
            //}

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            return query.OrderBy(x => x.CollegeCode.Length).ThenBy(x => x.CollegeCode)
                    .ThenByDescending(x => x.SchoolyearID).ThenBy(x => x.StandardID)
                    .ThenBy(x => x.Code.Length).ThenBy(x => x.Code)
                    .ToGridResultSet<GrademajorView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询院系专业对应的年级专业信息List(带对应的在校状态学生人数)
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="facultymajorID"></param>
        /// <param name="inSchoolStatus"></param>
        /// <returns></returns>
        public IList<GrademajorView> GetGrademajorViewList(ConfiguretView configuretView, Guid? facultymajorID, int? inSchoolStatus)
        {
            //院系专业信息
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expFacultymajor = expFacultymajor.And(x => x.FacultymajorID == facultymajorID);

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.GetGrademajorViwQueryable(expFacultymajor, expStudent);

            //if (inSchoolStatus != null && inSchoolStatus > -1)
            //{
            //    //排除人数为0的信息
            //    query = query.Where(x => x.StudentCount > 0);
            //}

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            return query.OrderBy(x => x.CollegeCode.Length).ThenBy(x => x.CollegeCode)
                    .ThenByDescending(x => x.SchoolyearID).ThenBy(x => x.StandardID)
                    .ThenBy(x => x.Code.Length).ThenBy(x => x.Code)
                    .ToList();
        }

        /// <summary>
        /// 查询院系专业对应的学生信息BaseStudentView
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="facultymajorID"></param>
        /// <param name="inSchoolStatus"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<BaseStudentView> GetBaseStudentViewGrid(ConfiguretView configuretView, Guid? facultymajorID, 
                int? inSchoolStatus, int pageIndex, int pageSize)
        {
            //院系专业信息
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expFacultymajor = expFacultymajor.And(x => x.FacultymajorID == facultymajorID);

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.GetBaseStudentViewQueryable(expFacultymajor, expStudent);

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            return query.OrderBy(x => x.LoginID)
                    .ThenBy(x => x.ClassmajorCode.Length).ThenBy(x => x.ClassmajorCode)
                    .ToGridResultSet<BaseStudentView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询院系专业对应的学生信息List
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="facultymajorID"></param>
        /// <param name="inSchoolStatus"></param>
        /// <returns></returns>
        public IList<BaseStudentView> GetBaseStudentViewList(ConfiguretView configuretView, Guid? facultymajorID, int? inSchoolStatus)
        {
            //院系专业信息
            Expression<Func<CF_Facultymajor, bool>> expFacultymajor = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expFacultymajor = expFacultymajor.And(x => x.FacultymajorID == facultymajorID);

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            var query = FacultymajorDAL.GetBaseStudentViewQueryable(expFacultymajor, expStudent);

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            return query.OrderBy(x => x.LoginID)
                    .ThenBy(x => x.ClassmajorCode.Length).ThenBy(x => x.ClassmajorCode)
                    .ToList();
        }

        /// <summary>
        /// 院系专业信息Excel导入
        /// </summary>
        /// <param name="cellheader"></param>
        /// <param name="inCount"></param>
        /// <param name="upCount"></param>
        /// <param name="errdataList"></param>
        /// <param name="errCount"></param>
        /// <param name="sourcePhysicalPath"></param>
        public void FacultymajorImport(Dictionary<string, string> cellheader, out int? inCount, out int? upCount, out List<FacultymajorView> errdataList, out int? errCount, string sourcePhysicalPath)
        {
            try
            {
                StringBuilder errorMsg = new StringBuilder(); // 错误信息
                List<FacultymajorView> errList = new List<FacultymajorView>();

                // 1.1解析文件，存放到一个List集合里
                cellheader.Remove("ErrorMessage");//移除“未导入原因”列(ErrorMessage)

                List<FacultymajorView> enlist = NpoiExcelHelper.ExcelToEntityList<FacultymajorView>(cellheader, sourcePhysicalPath, out errorMsg, out errList);

                cellheader.Add("ErrorMessage", "未导入原因");

                //对List集合进行有效性校验
                if (enlist.Count() <= 0)
                {
                    throw new Exception("Excel文件数据为空，请检查。");
                }

                Regex reg = null; //正则表达式

                DateTime result; //用于返回判断日期字段格式

                inCount = 0; //导入个数
                upCount = 0; //更新个数
                errCount = 0; //失败个数

                string errorMsgStr = ""; //错误信息

                List<CF_Facultymajor> newFacultymajorInList = new List<CF_Facultymajor>();

                List<CF_Facultymajor> newFacultymajorUpList = new List<CF_Facultymajor>();

                //将循环中相关数据库查询统一查询出来进行匹配(尽量避免在循环中进行数据库查询)

                //院系所信息
                var collegeList = FacultymajorDAL.CollegeRepository.GetList(x => true).ToList();
                //院系所代码
                var collegeNoList = enlist.Where(x => !string.IsNullOrEmpty(x.CollegeCode)).Select(x => x.CollegeCode).ToList();
                //对比后的newCollegeList
                var newCollegeList = collegeList.Where(x => collegeNoList.Contains(x.No)).ToList();

                //专业代码、专业名称、专业ID(Value)
                var standardList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_Standard).ToList();
                //培养层次(所修学历)
                var educationList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_Education).ToList();
                //学习形式
                var learningFormList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_Learningform).ToList();
                //专业科类
                var scienceclassList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_Scienceclass).ToList();
                //学习方式
                var learningstyleList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_Learningstyle).ToList();
                //授予学位
                var learnPositionList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_LearnPosition).ToList();

                //院系专业
                var facultymajorList = FacultymajorDAL.FacultymajorRepository.GetList(x => true).ToList();
                //院系所代码
                var facultymajorCodeList = enlist.Where(x => !string.IsNullOrEmpty(x.Code)).Select(x => x.Code).ToList();
                //对比后的newFacultymajorList(暂时只对比院系专业编号)
                var newFacultymajorList = facultymajorList.Where(x => facultymajorCodeList.Contains(x.Code)).ToList();

                //循环检测数据列,对各数据列进行验证（必填、字典项验证、数据格式等）
                for (int i = 0; i < enlist.Count; i++)
                {
                    FacultymajorView en = enlist[i]; //Excel表数据视图

                    CF_Facultymajor newFacultymajor = new CF_Facultymajor();

                    //院系专业编号
                    if (string.IsNullOrEmpty(en.Code))
                    {
                        errCount++;
                        errorMsgStr = "院系专业编号不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        reg = new Regex(@"^[0-9a-zA-Z\s?]+$"); //正则表达式(请输入数字或英文字母)
                        if (!reg.IsMatch(en.Code))
                        {
                            errCount++;
                            errorMsgStr = "院系专业编号格式不正确，请检查(数字或英文字母)";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //院系专业编号
                            newFacultymajor.Code = en.Code.Trim();
                        }
                    }

                    //院系专业名称
                    if (string.IsNullOrEmpty(en.Name))
                    {
                        errCount++;
                        errorMsgStr = "院系专业名称不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        //院系专业名称
                        newFacultymajor.Name = en.Name.Trim();
                    }

                    //简称
                    if (string.IsNullOrEmpty(en.Abbreviation))
                    {
                        //不考虑
                    }
                    else
                    {
                        newFacultymajor.Abbreviation = en.Abbreviation;
                    }

                    //英文名称
                    if (string.IsNullOrEmpty(en.EnglishName))
                    {
                        //不考虑
                    }
                    else
                    {
                        newFacultymajor.EnglishName = en.EnglishName;
                    }

                    //院系所代码
                    if (string.IsNullOrEmpty(en.CollegeCode))
                    {
                        errCount++;
                        errorMsgStr = RSL.Get("CollegeCode") + "不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        reg = new Regex(@"^[0-9a-zA-Z\s?]+$"); //正则表达式(请输入数字或英文字母)
                        if (!reg.IsMatch(en.CollegeCode))
                        {
                            errCount++;
                            errorMsgStr = RSL.Get("CollegeCode") + "格式不正确，请检查(数字或英文字母)";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            var college = newCollegeList.Where(x => x.No == en.CollegeCode.Trim())
                            .SingleOrDefault();
                            if (college == null)
                            {
                                errCount++;
                                errorMsgStr = RSL.Get("CollegeCode") + "不存在，请检查";
                                en.ErrorMessage = errorMsgStr;
                                errList.Add(en);
                                errorMsg.AppendLine(errorMsgStr);
                                continue;
                            }
                            else
                            {
                                //院系所信息ID
                                newFacultymajor.CollegeID = college.CollegeID;
                            }
                        }
                    }

                    //专业代码
                    if (string.IsNullOrEmpty(en.StandardCodeStr))
                    {
                        errCount++;
                        errorMsgStr = "专业代码不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var standardCode = standardList.Where(x => x.Code == en.StandardCodeStr.Trim())
                                .FirstOrDefault();
                        if (standardCode == null)
                        {
                            errCount++;
                            errorMsgStr = "专业代码不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //专业代码
                        }
                    }

                    //专业名称
                    if (string.IsNullOrEmpty(en.StandardNameStr))
                    {
                        errCount++;
                        errorMsgStr = "专业名称不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var standardName = standardList.Where(x => x.Name == en.StandardNameStr.Trim())
                                .FirstOrDefault();
                        if (standardName == null)
                        {
                            errCount++;
                            errorMsgStr = "专业名称不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //专业名称
                        }
                    }

                    //专业代码与专业名称查询专业ID(Value)
                    if (string.IsNullOrEmpty(en.StandardCodeStr) || string.IsNullOrEmpty(en.StandardNameStr))
                    {
                        errCount++;
                        errorMsgStr = "专业代码或专业名称不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var standardID = standardList.Where(x => x.Code == en.StandardCodeStr.Trim() && x.Name == en.StandardNameStr.Trim())
                            .SingleOrDefault();
                        if (standardID == null)
                        {
                            errCount++;
                            errorMsgStr = "专业代码与专业名称对应的元素值不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //专业ID(Value)
                            newFacultymajor.StandardID = standardID.Value;
                        }
                    }

                    //培养层次
                    if (string.IsNullOrEmpty(en.EducationStr))
                    {
                        errCount++;
                        errorMsgStr = RSL.Get("EducationID") + "不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var education = educationList.Where(x => x.Name == en.EducationStr.Trim()).SingleOrDefault();
                        if (education == null)
                        {
                            errCount++;
                            errorMsgStr = RSL.Get("EducationID") + "不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //培养层次
                            newFacultymajor.EducationID = education.Value;
                        }
                    }

                    //学习形式
                    if (string.IsNullOrEmpty(en.LearningformStr))
                    {
                        errCount++;
                        errorMsgStr = "学习形式不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var learningForm = learningFormList.Where(x => x.Name == en.LearningformStr.Trim()).SingleOrDefault();
                        if (learningForm == null)
                        {
                            errCount++;
                            errorMsgStr = "学习形式不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //学习形式
                            newFacultymajor.LearningformID = learningForm.Value;
                        }
                    }

                    //学制
                    if (string.IsNullOrEmpty(en.LearnSystemStr))
                    {
                        errCount++;
                        errorMsgStr = "学制不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        reg = new Regex(@"^[0-9]+([.]{1}[0-9]+){0,1}$"); //学制字段正则表达式()
                        if (!reg.IsMatch(en.LearnSystemStr))
                        {
                            errCount++;
                            errorMsgStr = "学制格式不正确，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //学制
                            newFacultymajor.LearnSystem = Convert.ToDecimal(en.LearnSystemStr);
                        }
                    }

                    //专业科类
                    if (string.IsNullOrEmpty(en.ScienceclassStr))
                    {
                        //不考虑
                    }
                    else
                    {
                        var scienceclass = scienceclassList.Where(x => x.Name == en.ScienceclassStr.Trim())
                                .SingleOrDefault();
                        if (scienceclass == null)
                        {
                            errCount++;
                            errorMsgStr = "专业科类不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //专业科类
                            newFacultymajor.ScienceclassID = scienceclass.Value;
                        }
                    }

                    //学习方式
                    if (string.IsNullOrEmpty(en.LearningstyleStr))
                    {
                        //不考虑
                    }
                    else
                    {
                        var learningstyle = learningstyleList.Where(x => x.Name == en.LearningstyleStr.Trim())
                                .SingleOrDefault();
                        if (learningstyle == null)
                        {
                            errCount++;
                            errorMsgStr = "学习方式不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //学习方式
                            newFacultymajor.LearningstyleID = learningstyle.Value;
                        }
                    }

                    //授予学位
                    if (string.IsNullOrEmpty(en.LearnPositionStr))
                    {
                        //不考虑
                    }
                    else
                    {
                        var learnPosition = learnPositionList.Where(x => x.Name == en.LearnPositionStr.Trim())
                                .SingleOrDefault();
                        if (learnPosition == null)
                        {
                            errCount++;
                            errorMsgStr = "授予学位不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //授予学位
                            newFacultymajor.LearnPositionID = learnPosition.Value;
                        }
                    }

                    //师范标识
                    if (string.IsNullOrEmpty(en.TeacherIdentification))
                    {
                        //不考虑
                    }
                    else
                    {
                        newFacultymajor.TeacherIdentification = en.TeacherIdentification;
                    }

                    //设置时间
                    if (string.IsNullOrEmpty(en.SetTimeStr))
                    {
                        //不考虑
                    }
                    else
                    {
                        //reg = new Regex(@"(\d{4})-(\d{1,2})-(\d{1,2})"); //日期正则表达式,2017-12-28
                        if (!DateTime.TryParse(en.SetTimeStr, out result))
                        {
                            errCount++;
                            errorMsgStr = "创建年月格式不正确，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //设置时间
                            newFacultymajor.SetTime = Convert.ToDateTime(en.SetTimeStr);
                        }
                    }

                    //备注
                    if (string.IsNullOrEmpty(en.Remark))
                    {
                        //不考虑
                    }
                    else
                    {
                        newFacultymajor.Remark = en.Remark;
                    }

                    ////Excel表重复性验证（注：当数据表中没有此记录，但是Excel中有重复数据时的去掉）
                    //for (int j = i + 1; j < enlist.Count; j++)
                    //{
                    //    NewFacultymajorView enA = enlist[j];

                    //    //根据Excel表中的业务主键进行去重（院系所ID、院系专业编号、专业ID(Value)、培养层次、学习形式、学制唯一）
                    //    if (en.No == enA.No && en.Name == enA.Name)
                    //    {
                    //        //用于标识Excel表中的重复记录（由于是批量进行插入数据表）

                    //    }
                    //}


                    //数据表重复性验证（院系所ID、院系专业编号、专业ID(Value)、培养层次、学习形式、学制唯一）
                    var facultymajorVerification = newFacultymajorList
                            .Where(x => x.CollegeID == newFacultymajor.CollegeID 
                                && x.Code == newFacultymajor.Code
                                && x.StandardID == newFacultymajor.StandardID
                                && x.EducationID == newFacultymajor.EducationID
                                && x.LearningformID == newFacultymajor.LearningformID
                                && x.LearnSystem == newFacultymajor.LearnSystem)
                            .SingleOrDefault();


                    if (facultymajorVerification == null)
                    {
                        //新增
                        if (!newFacultymajorInList.Any(x => x.CollegeID == newFacultymajor.CollegeID
                                && x.Code == newFacultymajor.Code
                                && x.StandardID == newFacultymajor.StandardID
                                && x.EducationID == newFacultymajor.EducationID
                                && x.LearningformID == newFacultymajor.LearningformID
                                && x.LearnSystem == newFacultymajor.LearnSystem))
                        {
                            newFacultymajor.FacultymajorID = Guid.NewGuid();
                            SetNewStatus(newFacultymajor);
                            newFacultymajorInList.Add(newFacultymajor);

                            inCount++;
                        }
                        else
                        {
                            //Excel表重复性验证
                            //（注：当数据表中没有此记录，但是Excel中有重复数据,可在此处进行抛出到失败数据文件中,目前暂不考虑）

                            inCount++;
                        }
                    }
                    else
                    {
                        //更新(Excel有重复时，以最后一条记录的更新为准)
                        facultymajorVerification.Abbreviation = newFacultymajor.Abbreviation;
                        facultymajorVerification.EnglishName = newFacultymajor.EnglishName;
                        facultymajorVerification.ScienceclassID = newFacultymajor.ScienceclassID;
                        facultymajorVerification.LearningstyleID = newFacultymajor.LearningstyleID;
                        facultymajorVerification.LearnPositionID = newFacultymajor.LearnPositionID;
                        facultymajorVerification.TeacherIdentification = newFacultymajor.TeacherIdentification;
                        facultymajorVerification.SetTime = newFacultymajor.SetTime;
                        facultymajorVerification.Remark = newFacultymajor.Remark;
                        SetModifyStatus(facultymajorVerification);
                        newFacultymajorUpList.Add(facultymajorVerification);

                        upCount++;
                    }
                }

                UnitOfWork.BulkInsert(newFacultymajorInList); //批量插入
                
                //批量统一提交更新
                if (newFacultymajorUpList != null && newFacultymajorUpList.Count() > 0)
                {
                    UnitOfWork.BatchUpdate(newFacultymajorUpList);
                }

                errdataList = errList.Distinct().ToList(); //错误列表List

            }
            catch (Exception ex)
            {
                //目前会出现，由于错误信息字符太长，无法抛出弹出框的问题
                throw new Exception(ex.Message);
            }
        }

    }
}