using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Text.RegularExpressions;
using System.Transactions;
using Bowin.Common.Linq;
using Bowin.Common.Linq.Entity;
using Bowin.Common.Utility;
using EMIS.Entities;
using EMIS.Utility;
using EMIS.ViewModel;
using EMIS.ViewModel.CacheManage;
using EMIS.ViewModel.UniversityManage.AdministrativeOrgan;
using EMIS.ViewModel.UniversityManage.SpecialtyClassManage;
using EMIS.ViewModel.UniversityManage.TeacherManage;
using EMIS.DataLogic.UniversityManage.AdministrativeOrgan;
using EMIS.CommonLogic.UniversityManage.TeacherManage;
using EMIS.CommonLogic.StudentManage.StudentStatistics;

namespace EMIS.CommonLogic.UniversityManage.AdministrativeOrgan
{
    public class CollegeServices : BaseServices, ICollegeServices
    {
        public CollegeDAL CollegeDAL { get; set; }
        public Lazy<IStaffServices> StaffServices { get; set; }
        public Lazy<IInSchoolSettingServices> InSchoolSettingServices { get; set; }

        /// <summary>
        /// 查询院系所信息View
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="campusID"></param>
        /// <param name="unitCategoryID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns> 
        public IGridResultSet<CollegeView> GetCollegeViewGrid(ConfiguretView configuretView, Guid? campusID, int? unitCategoryID, int pageIndex, int pageSize)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = CollegeDAL.GetCollegeViewQueryable(expCollege);

            if (campusID.HasValue)
            {
                query = query.Where(x => x.CampusID == campusID);
            }
            if (unitCategoryID.HasValue)
            {
                query = query.Where(x => x.UnitCategoryID == unitCategoryID);
            }

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

            return this.GetQueryByDataRangeByCollege(query).OrderBy(x => x.No.Length).ThenBy(x => x.No).ToGridResultSet<CollegeView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询院系所信息List
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="campusID"></param>
        /// <param name="unitCategoryID"></param>
        /// <returns></returns>
        public IList<CollegeView> GetCollegeViewList(ConfiguretView configuretView, Guid? campusID, int? unitCategoryID)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = CollegeDAL.GetCollegeViewQueryable(expCollege);

            if (campusID.HasValue)
            {
                query = query.Where(x => x.CampusID == campusID);
            }
            if (unitCategoryID.HasValue)
            {
                query = query.Where(x => x.UnitCategoryID == unitCategoryID);
            }

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

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

        /// <summary>
        /// 查询院系所信息View(只显示院、系、部类别的院系所)
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="campusID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<CollegeView> GetOnlyCollegeViewList(ConfiguretView configuretView, Guid? campusID, int pageIndex, int pageSize)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = CollegeDAL.GetOnlyCollegeViewQueryable(expCollege);

            if (campusID.HasValue)
            {
                query = query.Where(x => x.CampusID == campusID);
            }

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

            return this.GetQueryByDataRangeByCollege(query).OrderBy(x => x.No.Length).ThenBy(x => x.No).ToGridResultSet<CollegeView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询院系所信息View(无数据范围)
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="campusID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<CollegeView> GetCollegeViewWithoutRange(ConfiguretView configuretView, Guid? campusID, int pageIndex, int pageSize)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = CollegeDAL.GetCollegeViewQueryable(expCollege);

            if (campusID.HasValue)
            {
                query = query.Where(x => x.CampusID == campusID);
            }

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

            return query.OrderBy(x => x.No.Length).ThenBy(x => x.No).ToGridResultSet<CollegeView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 根据校区信息ID查询对应的院系所信息CF_College(带数据范围)
        /// </summary>
        /// <param name="campusID"></param>
        /// <returns></returns>
        public List<CF_College> GetCollegeList(Guid? campusID)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = CollegeDAL.CollegeRepository.GetList(expCollege);

            if (campusID.HasValue)
            {
                query = query.Where(x => x.CampusID == campusID);
            }

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

        /// <summary>
        /// 根据校区信息ID查询对应的院系所信息CF_College(无数据范围)
        /// </summary>
        /// <param name="campusID"></param>
        /// <returns></returns>
        public List<CF_College> GetAllCollegeList(Guid? campusID)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = CollegeDAL.CollegeRepository.GetList(expCollege);

            if (campusID.HasValue)
            {
                query = query.Where(x => x.CampusID == campusID);
            }

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

        /// <summary>
        /// 查询全部院系所信息CollegeView(带数据范围)
        /// </summary>
        /// <returns></returns>
        public List<CollegeView> GetCollegeList()
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = CollegeDAL.GetCollegeViewQueryable(expCollege);

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

        /// <summary>
        /// 查询全部院系所信息CollegeView(只显示院、系、部类别的院系所,带数据范围)
        /// </summary>
        /// <returns></returns>
        public List<CollegeView> GetOnlyCollegeList()
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = CollegeDAL.GetOnlyCollegeViewQueryable(expCollege);

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

        /// <summary>
        /// 查询全部院系所信息CollegeView(无数据范围)
        /// </summary>
        /// <returns></returns>
        public List<CollegeView> GetCollegeViewListWithoutDataRange()
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = CollegeDAL.GetCollegeViewQueryable(expCollege);

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

        /// <summary>
        /// 查询对应的院系所信息CF_College
        /// </summary>
        /// <param name="collegeID"></param>
        /// <returns></returns>
        public CF_College GetCollegeInfo(Guid? collegeID)
        {
            try
            {
                var query = CollegeDAL.CollegeRepository.GetList(x => x.CollegeID == collegeID).SingleOrDefault();
                return query;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 查询对应的院系所信息CollegeView
        /// </summary>
        /// <param name="collegeID"></param>
        /// <returns></returns>
        public CollegeView GetCollegeView(Guid? collegeID)
        {
            try
            {
                var query = CollegeDAL.GetCollegeViewQueryable(x => x.CollegeID == collegeID).SingleOrDefault();
                return query;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 编辑（新增、修改，业务主键：院系所代码或院系所名称）
        /// </summary>
        /// <param name="collegeView"></param>
        public void CollegeEdit(CollegeView collegeView)
        {
            try
            {
                //查询数据库进行验证
                var collegeVerify = CollegeDAL.CollegeRepository.GetList(x => x.CollegeID != collegeView.CollegeID && (x.No == collegeView.No || x.Name == collegeView.Name)).FirstOrDefault();
                if (collegeVerify == null)
                {
                    //数据有误验证
                    if (collegeView.CollegeID != Guid.Empty)
                    {
                        var college = CollegeDAL.CollegeRepository.GetList(x => x.CollegeID == collegeView.CollegeID, x => x.CF_CollegeProfile).SingleOrDefault();
                        if (college == null)
                        {
                            throw new Exception("数据有误，请核查。");
                        }
                        else
                        {
                            //表示修改
                            college.CampusID = collegeView.CampusID;
                            college.No = collegeView.No;
                            college.Name = collegeView.Name;
                            college.SimpleName = collegeView.SimpleName;
                            college.EnglishName = collegeView.EnglishName;
                            college.Remark = collegeView.Remark;
                            SetModifyStatus(college);
                            //院系所信息扩展表
                            if (college.CF_CollegeProfile == null)
                            {
                                //新增
                                var newCollegeProfile = new CF_CollegeProfile();
                                newCollegeProfile.CollegeID = college.CollegeID;
                                newCollegeProfile.PoliticalManager = collegeView.PoliticalManager;
                                newCollegeProfile.AdministrativeManager = collegeView.AdministrativeManager;
                                newCollegeProfile.UnitCategoryID = collegeView.UnitCategoryID;
                                newCollegeProfile.CollegeTypeID = collegeView.CollegeTypeID;
                                newCollegeProfile.CollegeCategoryID = collegeView.CollegeCategoryID;
                                newCollegeProfile.RunByCategoryID = collegeView.RunByCategoryID;
                                newCollegeProfile.FoundDate = collegeView.FoundDate;
                                newCollegeProfile.Officephone = collegeView.Officephone;
                                SetNewStatus(newCollegeProfile);
                                UnitOfWork.Add(newCollegeProfile);

                            }
                            else
                            {
                                //修改
                                college.CF_CollegeProfile.PoliticalManager = collegeView.PoliticalManager;
                                college.CF_CollegeProfile.AdministrativeManager = collegeView.AdministrativeManager;
                                college.CF_CollegeProfile.UnitCategoryID = collegeView.UnitCategoryID;
                                college.CF_CollegeProfile.CollegeTypeID = collegeView.CollegeTypeID;
                                college.CF_CollegeProfile.CollegeCategoryID = collegeView.CollegeCategoryID;
                                college.CF_CollegeProfile.RunByCategoryID = collegeView.RunByCategoryID;
                                college.CF_CollegeProfile.FoundDate = collegeView.FoundDate;
                                college.CF_CollegeProfile.Officephone = collegeView.Officephone;
                                SetModifyStatus(college.CF_CollegeProfile);

                            }
                        }
                    }
                    else
                    {
                        //表示新增(CF_College主表)
                        CF_College college = new CF_College();
                        college.CollegeID = Guid.NewGuid();
                        college.CampusID = collegeView.CampusID;
                        college.No = collegeView.No;
                        college.Name = collegeView.Name;
                        college.SimpleName = collegeView.SimpleName;
                        college.EnglishName = collegeView.EnglishName;
                        college.Remark = collegeView.Remark;
                        SetNewStatus(college);
                        UnitOfWork.Add(college);
                        //表示新增(CF_CollegeProfile扩展表)
                        CF_CollegeProfile collegeProfile = new CF_CollegeProfile();
                        collegeProfile.CollegeID = college.CollegeID;
                        collegeProfile.PoliticalManager = collegeView.PoliticalManager;
                        collegeProfile.AdministrativeManager = collegeView.AdministrativeManager;
                        collegeProfile.UnitCategoryID = collegeView.UnitCategoryID;
                        collegeProfile.CollegeTypeID = collegeView.CollegeTypeID;
                        collegeProfile.CollegeCategoryID = collegeView.CollegeCategoryID;
                        collegeProfile.RunByCategoryID = collegeView.RunByCategoryID;
                        collegeProfile.FoundDate = collegeView.FoundDate;
                        collegeProfile.Officephone = collegeView.Officephone;
                        SetNewStatus(collegeProfile);
                        UnitOfWork.Add(collegeProfile);
                    }
                }
                else
                {
                    throw new Exception("已存在相同的" + RSL.Get("CollegeCode") + "或" + RSL.Get("CollegeName") + "，请核查。");
                }
                //事务提交
                UnitOfWork.Commit();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="collegeIDList"></param>
        /// <returns></returns>
        public bool CollegeDelete(List<Guid?> collegeIDList)
        {
            try
            {
                UnitOfWork.Remove<CF_CollegeProfile>(x => collegeIDList.Contains(x.CollegeID));
                UnitOfWork.Remove<CF_College>(x => collegeIDList.Contains(x.CollegeID));
                UnitOfWork.Commit();
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询院系所对应的教研室信息DepartmentView
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="collegeID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<DepartmentView> GetDepartmentListViewGrid(ConfiguretView configuretView, Guid? collegeID, int pageIndex, int pageSize)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expCollege = expCollege.And(x => x.CollegeID == collegeID);
            var query = CollegeDAL.GetDepartmentViewQueryable(expCollege);

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }
            return query.OrderBy(x => x.No.Length).ThenBy(x => x.No).ToGridResultSet<DepartmentView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询院系所对应的教研室信息List
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="collegeID"></param>
        /// <returns></returns>
        public IList<DepartmentView> GetDepartmentListViewList(ConfiguretView configuretView, Guid? collegeID)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expCollege = expCollege.And(x => x.CollegeID == collegeID);
            var query = CollegeDAL.GetDepartmentViewQueryable(expCollege);

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }
            return query.OrderBy(x => x.No.Length).ThenBy(x => x.No).ToList();
        }

        /// <summary>
        /// 查询院系所对应的教师信息StaffView
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="collegeID"></param>
        /// <param name="departmentID"></param>
        /// <param name="isPhoto"></param>
        /// <param name="teacherTypeID"></param>
        /// <param name="incumbencyState"></param>
        /// <param name="titleID"></param>
        /// <param name="isDualTeacher"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<StaffView> GetStaffListViewGrid(ConfiguretView configuretView, Guid? collegeID, Guid? departmentID, int? isPhoto, int? teacherTypeID, 
                int? incumbencyState, int? titleID, int? isDualTeacher, int pageIndex, int pageSize)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expCollege = expCollege.And(x => x.CollegeID == collegeID);

            var query = CollegeDAL.GetStaffViewQueryable(expCollege);
            if (departmentID.HasValue)
            {
                query = query.Where(x => x.DepartmentID == departmentID);
            }
            if (isPhoto.HasValue)
            {
                if (isPhoto.Value == (int)CF_GeneralExist.Have)
                {
                    query = query.Where(x => x.PhotoUrl != null && x.PhotoUrl != "");
                }
                if (isPhoto.Value == (int)CF_GeneralExist.No)
                {
                    query = query.Where(x => x.PhotoUrl == null || x.PhotoUrl == "");
                }
            }
            if (teacherTypeID.HasValue)
            {
                query = query.Where(x => x.TeacherTypeID == teacherTypeID);
            }
            if (incumbencyState.HasValue)
            {
                query = query.Where(x => x.IncumbencyState == incumbencyState);
            }
            if (titleID.HasValue)
            {
                query = query.Where(x => x.TitleID == titleID);
            }
            if (isDualTeacher.HasValue)
            {
                if (isDualTeacher.Value == (int)CF_GeneralPurpose.IsYes)
                {
                    query = query.Where(x => x.IsDualTeacher == true);
                }
                if (isDualTeacher.Value == (int)CF_GeneralPurpose.IsNo)
                {
                    query = query.Where(x => x.IsDualTeacher != true);
                }
            }

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }
            return query.OrderBy(x => x.StaffCode.Length).ThenBy(x => x.StaffCode).ThenBy(x => x.CollegeNo.Length).ThenBy(x => x.CollegeNo).ToGridResultSet<StaffView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询院系所对应的教师信息List
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="collegeID"></param>
        /// <param name="departmentID"></param>
        /// <param name="isPhoto"></param>
        /// <param name="teacherTypeID"></param>
        /// <param name="incumbencyState"></param>
        /// <param name="titleID"></param>
        /// <param name="isDualTeacher"></param>
        /// <returns></returns>
        public IList<StaffView> GetStaffListViewList(ConfiguretView configuretView, Guid? collegeID, Guid? departmentID, int? isPhoto, int? teacherTypeID, 
                int? incumbencyState, int? titleID, int? isDualTeacher)
        {
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expCollege = expCollege.And(x => x.CollegeID == collegeID);

            var query = CollegeDAL.GetStaffViewQueryable(expCollege);
            if (departmentID.HasValue)
            {
                query = query.Where(x => x.DepartmentID == departmentID);
            }
            if (isPhoto.HasValue)
            {
                if (isPhoto.Value == (int)CF_GeneralExist.Have)
                {
                    query = query.Where(x => x.PhotoUrl != null && x.PhotoUrl != "");
                }
                if (isPhoto.Value == (int)CF_GeneralExist.No)
                {
                    query = query.Where(x => x.PhotoUrl == null || x.PhotoUrl == "");
                }
            }
            if (teacherTypeID.HasValue)
            {
                query = query.Where(x => x.TeacherTypeID == teacherTypeID);
            }
            if (incumbencyState.HasValue)
            {
                query = query.Where(x => x.IncumbencyState == incumbencyState);
            }
            if (titleID.HasValue)
            {
                query = query.Where(x => x.TitleID == titleID);
            }
            if (isDualTeacher.HasValue)
            {
                if (isDualTeacher.Value == (int)CF_GeneralPurpose.IsYes)
                {
                    query = query.Where(x => x.IsDualTeacher == true);
                }
                if (isDualTeacher.Value == (int)CF_GeneralPurpose.IsNo)
                {
                    query = query.Where(x => x.IsDualTeacher != true);
                }
            }

            //查询条件
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }
            return query.OrderBy(x => x.StaffCode.Length).ThenBy(x => x.StaffCode).ThenBy(x => x.CollegeNo.Length).ThenBy(x => x.CollegeNo).ToList();
        }

        /// <summary>
        /// 查询院系所对应的院系专业信息FacultymajorView
        /// </summary>
        /// <param name="configuretView"></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> GetFacultymajorListViewGrid(ConfiguretView configuretView, Guid? collegeID, int? standardID, int? educationID, int? learningformID, 
                string learnSystem, int? scienceclassID, int? inSchoolStatus, int pageIndex, int pageSize)
        {
            //院系所信息
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expCollege = expCollege.And(x => x.CollegeID == collegeID);

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (inSchoolStatus != null && inSchoolStatus > -1)
            {
                var inschoolStatusList = InSchoolSettingServices.Value.GetInschoolStatusList(true);
                if (inSchoolStatus == 1)
                {
                    //表示在校
                    expStudent = expStudent.And(x => inschoolStatusList.Contains(x.InSchoolStatusID));
                }
                if (inSchoolStatus == 0)
                {
                    //不在校
                    expStudent = expStudent.And(x => !inschoolStatusList.Contains(x.InSchoolStatusID));
                }
            }
            var query = CollegeDAL.GetFacultymajorViewQueryable(expCollege, expStudent);

            if (standardID.HasValue)
            {
                //专业ID(Value)
                query = query.Where(x => x.StandardID == standardID);
            }
            if (educationID.HasValue)
            {
                //培养层次
                query = query.Where(x => x.EducationID == educationID);
            }
            if (learningformID.HasValue)
            {
                //学习形式
                query = query.Where(x => x.LearningformID == learningformID);
            }
            if (!string.IsNullOrEmpty(learnSystem) && learnSystem != "-1")
            {
                //学制
                var LearnSystems = Convert.ToDecimal(learnSystem);
                query = query.Where(x => x.LearnSystem == LearnSystems);
            }
            if (scienceclassID.HasValue)
            {
                //专业科类
                query = query.Where(x => x.ScienceclassID == scienceclassID);
            }
            //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).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="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> GetFacultymajorListViewList(ConfiguretView configuretView, Guid? collegeID, int? standardID, int? educationID, int? learningformID, 
                string learnSystem, int? scienceclassID, int? inSchoolStatus)
        {
            //院系所信息
            Expression<Func<CF_College, bool>> expCollege = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expCollege = expCollege.And(x => x.CollegeID == collegeID);

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (inSchoolStatus != null && inSchoolStatus > -1)
            {
                var inschoolStatusList = InSchoolSettingServices.Value.GetInschoolStatusList(true);
                if (inSchoolStatus == 1)
                {
                    //表示在校
                    expStudent = expStudent.And(x => inschoolStatusList.Contains(x.InSchoolStatusID));
                }
                if (inSchoolStatus == 0)
                {
                    //不在校
                    expStudent = expStudent.And(x => !inschoolStatusList.Contains(x.InSchoolStatusID));
                }
            }
            var query = CollegeDAL.GetFacultymajorViewQueryable(expCollege, expStudent);

            if (standardID.HasValue)
            {
                //专业ID(Value)
                query = query.Where(x => x.StandardID == standardID);
            }
            if (educationID.HasValue)
            {
                //培养层次
                query = query.Where(x => x.EducationID == educationID);
            }
            if (learningformID.HasValue)
            {
                //学习形式
                query = query.Where(x => x.LearningformID == learningformID);
            }
            if (!string.IsNullOrEmpty(learnSystem) && learnSystem != "-1")
            {
                //学制
                var LearnSystems = Convert.ToDecimal(learnSystem);
                query = query.Where(x => x.LearnSystem == LearnSystems);
            }
            if (scienceclassID.HasValue)
            {
                //专业科类
                query = query.Where(x => x.ScienceclassID == scienceclassID);
            }
            //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).ThenBy(x => x.StandardID).ThenBy(x => x.Code.Length).ThenBy(x => x.Code).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 CollegeImport(Dictionary<string, string> cellheader, out int? inCount, out int? upCount, out List<CollegeView> errdataList, out int? errCount, string sourcePhysicalPath)
        {
            try
            {
                StringBuilder errorMsg = new StringBuilder(); // 错误信息
                List<CollegeView> errList = new List<CollegeView>();
                cellheader.Remove("ErrorMessage");
                List<CollegeView> enlist = NpoiExcelHelper.ExcelToEntityList<CollegeView>(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_College> newCollegeInList = new List<CF_College>();
                List<CF_CollegeProfile> newCollegeProfileInList = new List<CF_CollegeProfile>();
                List<CF_College> newCollegeUpList = new List<CF_College>();
                List<CF_CollegeProfile> newCollegeProfileUpList = new List<CF_CollegeProfile>();
                //将循环中相关数据库查询统一查询出来进行匹配(尽量避免在循环中进行数据库查询)
                //单位类别
                var unitCategoryList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_UnitCategory).ToList();
                //院系所信息
                var collegeList = CollegeDAL.CollegeRepository.GetList(x => true, x => x.CF_CollegeProfile).ToList();
                //院系所代码
                var collegeNoList = enlist.Where(x => !string.IsNullOrEmpty(x.No)).Select(x => x.No).ToList();
                //院系所名称
                var collegeNameList = enlist.Where(x => !string.IsNullOrEmpty(x.Name)).Select(x => x.Name).ToList();
                //对比后的newCollegeList
                var newCollegeList = collegeList.Where(x => collegeNoList.Contains(x.No) || collegeNameList.Contains(x.Name)).ToList();
                //校区信息
                var campusList = CollegeDAL.CampusRepository.GetList(x => true).ToList();
                //校区代码
                var campusNoList = enlist.Where(x => !string.IsNullOrEmpty(x.CampusNo)).Select(x => x.CampusNo).ToList();
                //对比后的newCampusList
                var newCampusList = campusList.Where(x => campusNoList.Contains(x.No)).ToList();
                //教职工信息
                var staffViewList = StaffServices.Value.GetListStaffView();
                //教职工姓名(暂时不考虑，教职工号不同，姓名相同的情况)
                var staffNamePList = enlist.Where(x => !string.IsNullOrEmpty(x.PoliticalManagerName)).Select(x => x.PoliticalManagerName).ToList();
                var staffNameAList = enlist.Where(x => !string.IsNullOrEmpty(x.AdministrativeManagerName)).Select(x => x.AdministrativeManagerName).ToList();
                //对比后的newStaffViewList
                var newStaffViewList = staffViewList.Where(x => staffNamePList.Contains(x.Name) || staffNameAList.Contains(x.Name)).ToList();

                //循环检测数据列,对各数据列进行验证（必填、字典项验证、数据格式等）
                for (int i = 0; i < enlist.Count; i++)
                {
                    CollegeView en = enlist[i]; //Excel表数据视图
                    CF_College newCollege = new CF_College();
                    CF_CollegeProfile newCollegeProfile = new CF_CollegeProfile();
                    //院系所代码
                    if (string.IsNullOrEmpty(en.No))
                    {
                        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.No))
                        {
                            errCount++;
                            errorMsgStr = RSL.Get("CollegeCode") + "格式不正确，请检查(数字或英文字母)";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //院系所代码
                            newCollege.No = en.No.Trim();
                        }
                    }
                    //院系所名称
                    if (string.IsNullOrEmpty(en.Name))
                    {
                        errCount++;
                        errorMsgStr = RSL.Get("CollegeName") + "不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        //院系所名称
                        newCollege.Name = en.Name.Trim();
                    }
                    //简称
                    if (string.IsNullOrEmpty(en.SimpleName))
                    {
                        //不考虑
                    }
                    else
                    {
                        newCollege.SimpleName = en.SimpleName;
                    }
                    //英文名称
                    if (string.IsNullOrEmpty(en.EnglishName))
                    {
                        //不考虑
                    }
                    else
                    {
                        newCollege.EnglishName = en.EnglishName;
                    }
                    //校区代码
                    if (string.IsNullOrEmpty(en.CampusNo))
                    {
                        errCount++;
                        errorMsgStr = RSL.Get("CampusCode") + "不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        reg = new Regex(@"^[0-9a-zA-Z\s?]+$"); //正则表达式(请输入数字或英文字母)
                        if (!reg.IsMatch(en.CampusNo))
                        {
                            errCount++;
                            errorMsgStr = RSL.Get("CampusCode") + "格式不正确，请检查(数字或英文字母)";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            var campus = newCampusList.Where(x => x.No == en.CampusNo.Trim()).SingleOrDefault();
                            if (campus == null)
                            {
                                errCount++;
                                errorMsgStr = RSL.Get("CampusCode") + "不存在，请检查";
                                en.ErrorMessage = errorMsgStr;
                                errList.Add(en);
                                errorMsg.AppendLine(errorMsgStr);
                                continue;
                            }
                            else
                            {
                                //校区信息ID
                                newCollege.CampusID = campus.CampusID;
                            }
                        }
                    }
                    //党务负责人
                    if (string.IsNullOrEmpty(en.PoliticalManagerName))
                    {
                        //不考虑
                    }
                    else
                    {
                        var staffViewByP = newStaffViewList.Where(x => x.Name == en.PoliticalManagerName.Trim()).FirstOrDefault();
                        if (staffViewByP == null)
                        {
                            ////暂不考虑
                            //errCount++;
                            //errorMsgStr = "党务负责人不存在，请检查";
                            //en.ErrorMessage = errorMsgStr;
                            //errList.Add(en);
                            //errorMsg.AppendLine(errorMsgStr);
                            //continue;
                        }
                        else
                        {
                            //党务负责人
                            newCollegeProfile.PoliticalManager = staffViewByP.UserID;
                        }
                    }
                    //行政负责人
                    if (string.IsNullOrEmpty(en.AdministrativeManagerName))
                    {
                        //不考虑
                    }
                    else
                    {
                        var staffViewByA = newStaffViewList.Where(x => x.Name == en.AdministrativeManagerName.Trim()).FirstOrDefault();
                        if (staffViewByA == null)
                        {
                            ////暂不考虑
                            //errCount++;
                            //errorMsgStr = "行政负责人不存在，请检查";
                            //en.ErrorMessage = errorMsgStr;
                            //errList.Add(en);
                            //errorMsg.AppendLine(errorMsgStr);
                            //continue;
                        }
                        else
                        {
                            //行政负责人
                            newCollegeProfile.AdministrativeManager = staffViewByA.UserID;
                        }
                    }
                    //单位类别
                    if (string.IsNullOrEmpty(en.UnitCategoryStr))
                    {
                        errCount++;
                        errorMsgStr = "单位类别不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var unitCategory = unitCategoryList.Where(x => x.Name == en.UnitCategoryStr.Trim()).SingleOrDefault();
                        if (unitCategory == null)
                        {
                            errCount++;
                            errorMsgStr = "单位类别不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //单位类别
                            newCollegeProfile.UnitCategoryID = unitCategory.Value;
                        }
                    }
                    //建立年月
                    if (string.IsNullOrEmpty(en.FoundDateStr))
                    {
                        //不考虑
                    }
                    else
                    {
                        //reg = new Regex(@"(\d{4})-(\d{1,2})-(\d{1,2})"); //日期正则表达式,2017-12-28
                        if (!DateTime.TryParse(en.FoundDateStr, out result))
                        {
                            errCount++;
                            errorMsgStr = "建立年月格式不正确，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //建立年月
                            newCollegeProfile.FoundDate = Convert.ToDateTime(en.FoundDateStr);
                        }
                    }
                    //办公电话
                    if (string.IsNullOrEmpty(en.Officephone))
                    {
                        //不考虑
                    }
                    else
                    {
                        newCollegeProfile.Officephone = en.Officephone;
                    }
                    //备注
                    if (string.IsNullOrEmpty(en.Remark))
                    {
                        //不考虑
                    }
                    else
                    {
                        newCollege.Remark = en.Remark;
                    }

                    ////Excel表重复性验证（注：当数据表中没有此记录，但是Excel中有重复数据时的去掉）
                    //for (int j = i + 1; j < enlist.Count; j++)
                    //{
                    //    NewCollegeView enA = enlist[j];
                    //    //根据Excel表中的业务主键进行去重（院系所代码或院系所名称唯一）
                    //    if (en.No == enA.No && en.Name == enA.Name)
                    //    {
                    //        //用于标识Excel表中的重复记录（由于是批量进行插入数据表）

                    //    }
                    //}

                    //数据表重复性验证(院系所代码或院系所名称唯一)
                    var collegeVerification = newCollegeList.Where(x => x.No == newCollege.No || x.Name == newCollege.Name).FirstOrDefault();
                    if (collegeVerification == null)
                    {
                        //新增
                        if (!newCollegeInList.Any(x => x.No == newCollege.No || x.Name == newCollege.Name))
                        {
                            //CF_College主表
                            newCollege.CollegeID = Guid.NewGuid();
                            SetNewStatus(newCollege);
                            newCollegeInList.Add(newCollege);
                            //CF_CollegeProfile扩展表
                            newCollegeProfile.CollegeID = newCollege.CollegeID;
                            SetNewStatus(newCollegeProfile);
                            newCollegeProfileInList.Add(newCollegeProfile);
                            inCount++;
                        }
                        else
                        {
                            //Excel表重复性验证
                            //（注：当数据表中没有此记录，但是Excel中有重复数据,可在此处进行抛出到失败数据文件中,目前暂不考虑）

                            inCount++;
                        }
                    }
                    else
                    {
                        //更新(Excel有重复时，以最后一条记录的更新为准)
                        //更新CF_College主表
                        collegeVerification.SimpleName = newCollege.SimpleName;
                        collegeVerification.EnglishName = newCollege.EnglishName;
                        collegeVerification.Remark = newCollege.Remark;
                        SetModifyStatus(collegeVerification);
                        newCollegeUpList.Add(collegeVerification);
                        //CF_CollegeProfile扩展表
                        if (collegeVerification.CF_CollegeProfile == null)
                        {
                            newCollegeProfile.CollegeID = collegeVerification.CollegeID;
                            SetNewStatus(newCollegeProfile);
                            newCollegeProfileInList.Add(newCollegeProfile);
                        }
                        else
                        {
                            //更新CF_CollegeProfile扩展表
                            collegeVerification.CF_CollegeProfile.PoliticalManager = newCollegeProfile.PoliticalManager;
                            collegeVerification.CF_CollegeProfile.AdministrativeManager = newCollegeProfile.AdministrativeManager;
                            collegeVerification.CF_CollegeProfile.UnitCategoryID = newCollegeProfile.UnitCategoryID;
                            collegeVerification.CF_CollegeProfile.FoundDate = newCollegeProfile.FoundDate;
                            collegeVerification.CF_CollegeProfile.Officephone = newCollegeProfile.Officephone;
                            SetModifyStatus(collegeVerification.CF_CollegeProfile);
                            newCollegeProfileUpList.Add(collegeVerification.CF_CollegeProfile);
                        }
                        upCount++;
                    }
                }
                using (TransactionScope ts = new TransactionScope())
                {
                    UnitOfWork.BulkInsert(newCollegeInList);
                    UnitOfWork.BulkInsert(newCollegeProfileInList);
                    //批量统一提交更新
                    if (newCollegeUpList != null && newCollegeUpList.Count() > 0)
                    {
                        UnitOfWork.BatchUpdate(newCollegeUpList);
                    }
                    //批量统一提交更新
                    if (newCollegeProfileUpList != null && newCollegeProfileUpList.Count() > 0)
                    {
                        UnitOfWork.BatchUpdate(newCollegeProfileUpList);
                    }
                    ts.Complete();
                }
                errdataList = errList.Distinct().ToList(); //错误列表List
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}