using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Data.Entity;
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.UniversityManage.AdministrativeOrgan;
using EMIS.ViewModel.UniversityManage.TeacherManage;
using EMIS.DataLogic.Repositories;
using EMIS.DataLogic.UniversityManage.AdministrativeOrgan;
using EMIS.CommonLogic.UniversityManage.TeacherManage;

namespace EMIS.CommonLogic.UniversityManage.AdministrativeOrgan
{
    public class DepartmentServices : BaseServices, IDepartmentServices
    {
        public DepartmentDAL DepartmentDAL { get; set; }
        public Lazy<IStaffServices> StaffServices { get; set; }
        public EMIS.DataLogic.Repositories.HRService.OrgInfoRepository OrgInfoRepository { get; set; }

        /// <summary>
        /// 查询部门信息View
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="campusID"></param>
        /// <param name="collegeID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<DepartmentView> GetDepartmentViewGrid(ConfiguretView configuretView, Guid? campusID, Guid? collegeID, int pageIndex, int pageSize)
        {
            Expression<Func<CF_Department, bool>> expDepartment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (collegeID.HasValue)
            {
                expDepartment = expDepartment.And(x => x.CollegeID == collegeID);
            }
            var query = DepartmentDAL.GetDepartmentViewQueryable(expDepartment);

            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.CollegeCode.Length).ThenBy(x => x.CollegeCode).ThenBy(x => x.No.Length).ThenBy(x => x.No).ToGridResultSet<DepartmentView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询部门信息List
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="campusID"></param>
        /// <param name="collegeID"></param>
        /// <returns></returns>
        public IList<DepartmentView> GetDepartmentViewList(ConfiguretView configuretView, Guid? campusID, Guid? collegeID)
        {
            Expression<Func<CF_Department, bool>> expDepartment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (collegeID.HasValue)
            {
                expDepartment = expDepartment.And(x => x.CollegeID == collegeID);
            }
            var query = DepartmentDAL.GetDepartmentViewQueryable(expDepartment);

            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.CollegeCode.Length).ThenBy(x => x.CollegeCode).ThenBy(x => x.No.Length).ThenBy(x => x.No).ToList();
        }

        /// <summary>
        /// 查询部门信息View(无数据范围)
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="campusID"></param>
        /// <param name="collegeID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<DepartmentView> GetDepartmentViewWithoutRange(ConfiguretView configuretView, Guid? campusID, Guid? collegeID, int pageIndex, int pageSize)
        {
            Expression<Func<CF_Department, bool>> expDepartment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (collegeID.HasValue)
            {
                expDepartment = expDepartment.And(x => x.CollegeID == collegeID);
            }
            var query = DepartmentDAL.GetDepartmentViewQueryable(expDepartment);

            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.CollegeCode.Length).ThenBy(x => x.CollegeCode).ThenBy(x => x.No.Length).ThenBy(x => x.No).ToGridResultSet<DepartmentView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 根据院系所信息ID查询对应的部门信息CF_Department(带数据范围)
        /// </summary>
        /// <param name="collegeID"></param>
        /// <returns></returns>
        public List<CF_Department> GetDepartmentList(Guid? collegeID)
        {
            Expression<Func<CF_Department, bool>> expDepartment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = DepartmentDAL.DepartmentRepository.GetList(expDepartment);

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

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

        /// <summary>
        /// 根据院系所信息ID查询对应的部门信息CF_Department(无数据范围)
        /// </summary>
        /// <param name="collegeID"></param>
        /// <returns></returns>
        public List<CF_Department> GetAllDepartmentList(Guid? collegeID)
        {
            Expression<Func<CF_Department, bool>> expDepartment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = DepartmentDAL.DepartmentRepository.GetList(expDepartment);

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

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

        /// <summary>
        /// 查询全部部门信息DepartmentView(带数据范围)
        /// </summary>
        /// <returns></returns>
        public List<DepartmentView> GetDepartmentList()
        {
            Expression<Func<CF_Department, bool>> expDepartment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = DepartmentDAL.GetDepartmentViewQueryable(expDepartment);

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

        /// <summary>
        /// 查询全部部门信息DepartmentView(无数据范围)
        /// </summary>
        /// <returns></returns>
        public List<DepartmentView> GetDepartmentViewListWithoutDataRange()
        {
            Expression<Func<CF_Department, bool>> expDepartment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            var query = DepartmentDAL.GetDepartmentViewQueryable(expDepartment);

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

        /// <summary>
        /// 查询对应的部门信息CF_Department
        /// </summary>
        /// <param name="departmentID"></param>
        /// <returns></returns>
        public CF_Department GetDepartmentInfo(Guid? departmentID)
        {
            try
            {
                var query = DepartmentDAL.DepartmentRepository.GetList(x => x.DepartmentID == departmentID).SingleOrDefault();
                return query;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 查询对应的部门信息DepartmentView
        /// </summary>
        /// <param name="departmentID"></param>
        /// <returns></returns>
        public DepartmentView GetDepartmentView(Guid? departmentID)
        {
            try
            {
                var query = DepartmentDAL.GetDepartmentViewQueryable(x => x.DepartmentID == departmentID).SingleOrDefault();
                return query;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 编辑（新增、修改，业务主键：部门代码或部门名称）
        /// </summary>
        /// <param name="departmentView"></param>
        public void DepartmentEdit(DepartmentView departmentView)
        {
            try
            {
                //查询数据库进行验证
                var departmentVerify = DepartmentDAL.DepartmentRepository.GetList(x => x.DepartmentID != departmentView.DepartmentID && (x.No == departmentView.No || x.Name == departmentView.Name)).FirstOrDefault();
                if (departmentVerify == null)
                {
                    //数据有误验证
                    if (departmentView.DepartmentID != Guid.Empty)
                    {
                        var department = DepartmentDAL.DepartmentRepository.GetList(x => x.DepartmentID == departmentView.DepartmentID, x => x.CF_DepartmentProfile).SingleOrDefault();
                        if (department == null)
                        {
                            throw new Exception("数据有误，请核查。");
                        }
                        else
                        {
                            //表示修改
                            department.CollegeID = departmentView.CollegeID;
                            department.No = departmentView.No;
                            department.Name = departmentView.Name;
                            department.SimpleName = departmentView.SimpleName;
                            department.EnglishName = departmentView.EnglishName;
                            department.Remark = departmentView.Remark;
                            SetModifyStatus(department);
                            //部门信息扩展表
                            if (department.CF_DepartmentProfile == null)
                            {
                                //新增
                                var newDepartmentProfile = new CF_DepartmentProfile();
                                newDepartmentProfile.DepartmentID = department.DepartmentID;
                                newDepartmentProfile.DirectorID = departmentView.DirectorID;
                                newDepartmentProfile.DeputyDirectorID = departmentView.DeputyDirectorID;
                                newDepartmentProfile.FoundDate = departmentView.FoundDate;
                                SetNewStatus(newDepartmentProfile);
                                UnitOfWork.Add(newDepartmentProfile);
                            }
                            else
                            {
                                //修改
                                department.CF_DepartmentProfile.DirectorID = departmentView.DirectorID;
                                department.CF_DepartmentProfile.DeputyDirectorID = departmentView.DeputyDirectorID;
                                department.CF_DepartmentProfile.FoundDate = departmentView.FoundDate;
                                SetModifyStatus(department.CF_DepartmentProfile);
                            }
                        }
                    }
                    else
                    {
                        //表示新增(CF_Department主表)
                        CF_Department department = new CF_Department();
                        department.DepartmentID = Guid.NewGuid();
                        department.CollegeID = departmentView.CollegeID;
                        department.No = departmentView.No;
                        department.Name = departmentView.Name;
                        department.SimpleName = departmentView.SimpleName;
                        department.EnglishName = departmentView.EnglishName;
                        department.Remark = departmentView.Remark;
                        SetNewStatus(department);
                        UnitOfWork.Add(department);
                        //表示新增(CF_DepartmentProfile扩展表)
                        CF_DepartmentProfile departmentProfile = new CF_DepartmentProfile();
                        departmentProfile.DepartmentID = department.DepartmentID;
                        departmentProfile.DirectorID = departmentView.DirectorID;
                        departmentProfile.DeputyDirectorID = departmentView.DeputyDirectorID;
                        departmentProfile.FoundDate = departmentView.FoundDate;
                        SetNewStatus(departmentProfile);
                        UnitOfWork.Add(departmentProfile);
                    }
                }
                else
                {
                    throw new Exception("已存在相同的教研室代码或教研室名称，请核查。");
                }
                //事务提交
                UnitOfWork.Commit();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="departmentIDList"></param>
        /// <returns></returns>
        public bool DepartmentDelete(List<Guid?> departmentIDList)
        {
            try
            {
                UnitOfWork.Remove<CF_DepartmentProfile>(x => departmentIDList.Contains(x.DepartmentID));
                UnitOfWork.Remove<CF_Department>(x => departmentIDList.Contains(x.DepartmentID));
                UnitOfWork.Commit();
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 期末、补考设定教研室查询
        /// </summary>
        /// <param name="ClassmajorID"></param>
        /// <param name="CoursematerialID"></param>
        /// <returns></returns>
        public string GetDepartmentListForExamination(Guid? ClassmajorID, Guid? CoursematerialID)
        {
            //var ID = "";
            var query = DepartmentDAL.GetDepartmentViewQueryable(x => true);
            //if (ClassmajorID != null && ClassmajorID != Guid.Empty && CoursematerialID != null && CoursematerialID != Guid.Empty)
            //{
            //    var StandardID = ClassmajorDAL.GetClassmajorViewQueryable(x => x.ClassmajorID == ClassmajorID, x => true).Select(w => w.StandardID).FirstOrDefault();
            //    var departmentID = SpecialtyCourseDAL.GetSpecialtyCourseViewQueryable(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE 
            //            && x.CoursematerialID == CoursematerialID && x.CF_Specialty.StandardID == StandardID).Select(w => w.DepartmentID).FirstOrDefault();
            //    if (departmentID.HasValue)
            //    {
            //        ID = departmentID.ToString();
            //    }
            //}

            return query.FirstOrDefault().DepartmentID.ToString();
        }

        /// <summary>
        /// HRService工作流平台
        /// </summary>
        public void SynHRServices()
        {
            var hrOrgInfos = OrgInfoRepository.Entities.ToList();
            var universityList = DepartmentDAL.UniversityRepository.Entities.Include(x => x.CF_UniversityProfile).ToList();
            var campusList = DepartmentDAL.CampusRepository.Entities.Include(x => x.CF_CampusProfile).ToList();
            var collegeList = DepartmentDAL.CollegeRepository.Entities.Include(x => x.CF_CollegeProfile).ToList();
            var departmentList = DepartmentDAL.DepartmentRepository.Entities.Include(x => x.CF_DepartmentProfile).ToList();

            hrOrgInfos.ForEach(x => x.status = "D");
            foreach (var university in universityList)
            {
                var hrOrgInfo = hrOrgInfos.FirstOrDefault(x => x.dpid == university.UniversityID.ToString());
                if (hrOrgInfo == null)
                {
                    hrOrgInfo = new Entities.HRServices.Comm_OrgInfo();
                    hrOrgInfo.dpid = university.UniversityID.ToString();
                    hrOrgInfo.createdt = DateTime.Now;
                    this.HRUnitOfWork.Add(hrOrgInfo);
                }
                hrOrgInfo.parentdpid = null;
                hrOrgInfo.dpcode = university.UniversityID.ToString();
                hrOrgInfo.dplv = 1;
                hrOrgInfo.dpname = university.Name;
                hrOrgInfo.dpfullname = university.Name;
                hrOrgInfo.status = "A";
                hrOrgInfo.orderNo = university.Code;
                hrOrgInfo.isTmpDP = "N";
                hrOrgInfo.updatedt = DateTime.Now;
                hrOrgInfo.OrderNum = "9999";
            }
            foreach (var campus in campusList)
            {
                var hrOrgInfo = hrOrgInfos.FirstOrDefault(x => x.dpid == campus.CampusID.ToString());
                if (hrOrgInfo == null)
                {
                    hrOrgInfo = new Entities.HRServices.Comm_OrgInfo();
                    hrOrgInfo.dpid = campus.CampusID.ToString();
                    hrOrgInfo.createdt = DateTime.Now;
                    this.HRUnitOfWork.Add(hrOrgInfo);
                }
                hrOrgInfo.parentdpid = campus.UniversityID.ToString();
                hrOrgInfo.dpcode = campus.CampusID.ToString();
                hrOrgInfo.dplv = 1;
                hrOrgInfo.dpname = campus.Name;
                hrOrgInfo.dpfullname = campus.Name;
                hrOrgInfo.status = "A";
                hrOrgInfo.orderNo = campus.No;
                hrOrgInfo.isTmpDP = "N";
                hrOrgInfo.updatedt = DateTime.Now;
                hrOrgInfo.OrderNum = "9999";
            }
            foreach (var college in collegeList)
            {
                var hrOrgInfo = hrOrgInfos.FirstOrDefault(x => x.dpid == college.CollegeID.ToString());
                if (hrOrgInfo == null)
                {
                    hrOrgInfo = new Entities.HRServices.Comm_OrgInfo();
                    hrOrgInfo.dpid = college.CollegeID.ToString();
                    hrOrgInfo.createdt = DateTime.Now;
                    this.HRUnitOfWork.Add(hrOrgInfo);
                }
                hrOrgInfo.parentdpid = college.CampusID.ToString();
                hrOrgInfo.dpcode = college.CollegeID.ToString();
                hrOrgInfo.dplv = 1;
                hrOrgInfo.dpname = college.Name;
                hrOrgInfo.dpfullname = college.Name;
                hrOrgInfo.status = "A";
                hrOrgInfo.orderNo = college.No;
                hrOrgInfo.isTmpDP = "N";
                hrOrgInfo.updatedt = DateTime.Now;
                hrOrgInfo.OrderNum = "9999";
            }
            foreach (var department in departmentList)
            {
                var hrOrgInfo = hrOrgInfos.FirstOrDefault(x => x.dpid == department.DepartmentID.ToString());
                if (hrOrgInfo == null)
                {
                    hrOrgInfo = new Entities.HRServices.Comm_OrgInfo();
                    hrOrgInfo.dpid = department.DepartmentID.ToString();
                    hrOrgInfo.createdt = DateTime.Now;
                    this.HRUnitOfWork.Add(hrOrgInfo);
                }
                hrOrgInfo.parentdpid = department.CollegeID.ToString();
                hrOrgInfo.dpcode = department.DepartmentID.ToString();
                hrOrgInfo.dplv = 1;
                hrOrgInfo.dpname = department.Name;
                hrOrgInfo.dpfullname = department.Name;
                hrOrgInfo.status = "A";
                hrOrgInfo.orderNo = department.No;
                hrOrgInfo.isTmpDP = "N";
                hrOrgInfo.updatedt = DateTime.Now;
                hrOrgInfo.OrderNum = "9999";
            }

            this.HRUnitOfWork.Commit();
        }

        /// <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_Department, bool>> expDepartment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expDepartment = expDepartment.And(x => x.DepartmentID == departmentID);
            var query = DepartmentDAL.GetStaffViewQueryable(expDepartment);
            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }
            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_Department, bool>> expDepartment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expDepartment = expDepartment.And(x => x.DepartmentID == departmentID);
            var query = DepartmentDAL.GetStaffViewQueryable(expDepartment);
            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }
            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>
        /// 部门信息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 DepartmentImport(Dictionary<string, string> cellheader, out int? inCount, out int? upCount, out List<DepartmentView> errdataList, out int? errCount, string sourcePhysicalPath)
        {
            try
            {
                StringBuilder errorMsg = new StringBuilder();
                List<DepartmentView> errList = new List<DepartmentView>();
                cellheader.Remove("ErrorMessage");
                List<DepartmentView> enlist = NpoiExcelHelper.ExcelToEntityList<DepartmentView>(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_Department> newDepartmentInList = new List<CF_Department>();
                List<CF_DepartmentProfile> newDepartmentProfileInList = new List<CF_DepartmentProfile>();
                List<CF_Department> newDepartmentUpList = new List<CF_Department>();
                List<CF_DepartmentProfile> newDepartmentProfileUpList = new List<CF_DepartmentProfile>();
                //将循环中相关数据库查询统一查询出来进行匹配(尽量避免在循环中进行数据库查询)
                //部门信息
                var departmentList = DepartmentDAL.DepartmentRepository.GetList(x => true, x => x.CF_DepartmentProfile).ToList();
                //部门代码
                var departmentNoList = enlist.Where(x => !string.IsNullOrEmpty(x.No)).Select(x => x.No).ToList();
                //部门名称
                var departmentNameList = enlist.Where(x => !string.IsNullOrEmpty(x.Name)).Select(x => x.Name).ToList();
                //对比后的newDepartmentList
                var newDepartmentList = departmentList.Where(x => departmentNoList.Contains(x.No) || departmentNameList.Contains(x.Name)).ToList();
                //院系所信息
                var collegeList = DepartmentDAL.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();
                //教职工信息
                var staffViewList = StaffServices.Value.GetListStaffView();
                //教职工姓名(暂时不考虑，教职工号不同，姓名相同的情况)
                var staffNamePList = enlist.Where(x => !string.IsNullOrEmpty(x.DirectorName)).Select(x => x.DirectorName).ToList();
                var staffNameAList = enlist.Where(x => !string.IsNullOrEmpty(x.DeputyDirectorName)).Select(x => x.DeputyDirectorName).ToList();
                //对比后的newStaffViewList
                var newStaffViewList = staffViewList.Where(x => staffNamePList.Contains(x.Name) || staffNameAList.Contains(x.Name)).ToList();

                //循环检测数据列,对各数据列进行验证（必填、字典项验证、数据格式等）
                for (int i = 0; i < enlist.Count; i++)
                {
                    DepartmentView en = enlist[i]; //Excel表数据视图
                    CF_Department newDepartment = new CF_Department();
                    CF_DepartmentProfile newDepartmentProfile = new CF_DepartmentProfile();
                    //部门代码
                    if (string.IsNullOrEmpty(en.No))
                    {
                        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.No))
                        {
                            errCount++;
                            errorMsgStr = "教研室代码格式不正确，请检查(数字或英文字母)";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //教研室代码
                            newDepartment.No = en.No.Trim();
                        }
                    }
                    //教研室名称
                    if (string.IsNullOrEmpty(en.Name))
                    {
                        errCount++;
                        errorMsgStr = "教研室名称不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        //教研室名称
                        newDepartment.Name = en.Name.Trim();
                    }
                    //简称
                    if (string.IsNullOrEmpty(en.SimpleName))
                    {
                        //不考虑
                    }
                    else
                    {
                        newDepartment.SimpleName = en.SimpleName;
                    }
                    //英文名称
                    if (string.IsNullOrEmpty(en.EnglishName))
                    {
                        //不考虑
                    }
                    else
                    {
                        newDepartment.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
                                newDepartment.CollegeID = college.CollegeID;
                            }
                        }
                    }
                    //主任姓名
                    if (string.IsNullOrEmpty(en.DirectorName))
                    {
                        //不考虑
                    }
                    else
                    {
                        var staffViewByP = newStaffViewList.Where(x => x.Name == en.DirectorName.Trim()).FirstOrDefault();
                        if (staffViewByP == null)
                        {
                            ////暂不考虑
                            //errCount++;
                            //errorMsgStr = "主任姓名不存在，请检查";
                            //en.ErrorMessage = errorMsgStr;
                            //errList.Add(en);
                            //errorMsg.AppendLine(errorMsgStr);
                            //continue;
                        }
                        else
                        {
                            //主任姓名
                            newDepartmentProfile.DirectorID = staffViewByP.UserID;
                        }
                    }
                    //副主任姓名
                    if (string.IsNullOrEmpty(en.DeputyDirectorName))
                    {
                        //不考虑
                    }
                    else
                    {
                        var staffViewByA = newStaffViewList.Where(x => x.Name == en.DeputyDirectorName.Trim()).FirstOrDefault();
                        if (staffViewByA == null)
                        {
                            ////暂不考虑
                            //errCount++;
                            //errorMsgStr = "副主任姓名不存在，请检查";
                            //en.ErrorMessage = errorMsgStr;
                            //errList.Add(en);
                            //errorMsg.AppendLine(errorMsgStr);
                            //continue;
                        }
                        else
                        {
                            //副主任姓名
                            newDepartmentProfile.DeputyDirectorID = staffViewByA.UserID;
                        }
                    }
                    //创建年月
                    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
                        {
                            //创建年月
                            newDepartmentProfile.FoundDate = Convert.ToDateTime(en.FoundDateStr);
                        }
                    }
                    //备注
                    if (string.IsNullOrEmpty(en.Remark))
                    {
                        //不考虑
                    }
                    else
                    {
                        newDepartment.Remark = en.Remark;
                    }

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

                    //    }
                    //}

                    //数据表重复性验证（部门代码或部门名称唯一）
                    var departmentVerify = newDepartmentList.Where(x => x.No == newDepartment.No || x.Name == newDepartment.Name).FirstOrDefault();
                    if (departmentVerify == null)
                    {
                        //新增
                        if (!newDepartmentInList.Any(x => x.No == newDepartment.No || x.Name == newDepartment.Name))
                        {
                            //CF_Department主表
                            newDepartment.DepartmentID = Guid.NewGuid();
                            SetNewStatus(newDepartment);
                            newDepartmentInList.Add(newDepartment);
                            //CF_DepartmentProfile扩展表
                            newDepartmentProfile.DepartmentID = newDepartment.DepartmentID;
                            SetNewStatus(newDepartmentProfile);
                            newDepartmentProfileInList.Add(newDepartmentProfile);
                            inCount++;
                        }
                        else
                        {
                            //Excel表重复性验证
                            //（注：当数据表中没有此记录，但是Excel中有重复数据,可在此处进行抛出到失败数据文件中,目前暂不考虑）

                            inCount++;
                        }
                    }
                    else
                    {
                        //更新(Excel有重复时，以最后一条记录的更新为准)
                        //更新CF_Department主表
                        departmentVerify.SimpleName = newDepartment.SimpleName;
                        departmentVerify.EnglishName = newDepartment.EnglishName;
                        departmentVerify.Remark = newDepartment.Remark;
                        SetModifyStatus(departmentVerify);
                        newDepartmentUpList.Add(departmentVerify);
                        //CF_DepartmentProfile扩展表
                        if (departmentVerify.CF_DepartmentProfile == null)
                        {
                            newDepartmentProfile.DepartmentID = departmentVerify.DepartmentID;
                            SetNewStatus(newDepartmentProfile);
                            newDepartmentProfileInList.Add(newDepartmentProfile);
                        }
                        else
                        {
                            //更新CF_DepartmentProfile扩展表
                            departmentVerify.CF_DepartmentProfile.DirectorID = newDepartmentProfile.DirectorID;
                            departmentVerify.CF_DepartmentProfile.DeputyDirectorID = newDepartmentProfile.DeputyDirectorID;
                            departmentVerify.CF_DepartmentProfile.FoundDate = newDepartmentProfile.FoundDate;
                            SetModifyStatus(departmentVerify.CF_DepartmentProfile);
                            newDepartmentProfileUpList.Add(departmentVerify.CF_DepartmentProfile);
                        }
                        upCount++;
                    }
                }
                using (TransactionScope ts = new TransactionScope())
                {
                    UnitOfWork.BulkInsert(newDepartmentInList);
                    UnitOfWork.BulkInsert(newDepartmentProfileInList);
                    //批量统一提交更新
                    if (newDepartmentUpList != null && newDepartmentUpList.Count() > 0)
                    {
                        UnitOfWork.BatchUpdate(newDepartmentUpList);
                    }
                    //批量统一提交更新
                    if (newDepartmentProfileUpList != null && newDepartmentProfileUpList.Count() > 0)
                    {
                        UnitOfWork.BatchUpdate(newDepartmentProfileUpList);
                    }
                    ts.Complete();
                }
                errdataList = errList.Distinct().ToList(); //错误列表List
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}