using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Transactions; using Bowin.Common.Utility; using EMIS.Entities; using EMIS.ViewModel; using EMIS.ViewModel.CacheManage; using EMIS.ViewModel.EnrollManage.NewStudentManage; namespace EMIS.ExtensionLogic.ServiceLogic.EnrollManage { public class NewStudentCollegeServices : EMIS.CommonLogic.EnrollManage.NewStudentManage.NewStudentCollegeServices { /// /// 招生信息Excel导入(预分配,需根据省招专业相关信息进行匹配--个性化) /// 此为东莞理工版本 /// /// /// /// /// /// /// public override void NewStudentCollegeImport(Dictionary cellheader, out int? inCount, out int? upCount, out List errdataList, out int? errCount, string sourcePhysicalPath) { /// 招生信息Excel导入(预分配,需根据省招专业相关信息进行匹配--东莞理工版本) /// 匹配规则:根据Excel文件中录取专业(取后四位)与省招专业信息中省招代码进行匹配 try { StringBuilder errorMsg = new StringBuilder(); // 错误信息 List errList = new List(); // 1.1解析文件,存放到一个List集合里 cellheader.Remove("ErrorMessage");//移除“未导入原因”列(ErrorMessage) List enlist = NpoiExcelHelper.ExcelToEntityList(cellheader, sourcePhysicalPath, out errorMsg, out errList); cellheader.Add("ErrorMessage", "未导入原因"); //对List集合进行有效性校验 if (enlist.Count() <= 0) { throw new Exception("Excel文件数据为空,请检查。"); } Regex reg = null; //正则表达式 DateTime result; //用于返回判断日期字段格式 int isInt; //用于返回判断int列格式 inCount = 0; //导入个数 upCount = 0; //更新个数 errCount = 0; //失败个数 string errorMsgStr = ""; //错误信息 List newStudentInList = new List(); //新生信息insert实体List List newStudentUpList = new List(); //新生信息update实体List //将循环中相关数据库查询统一查询出来进行匹配(尽量避免在循环中进行数据库查询) //性别 var sexList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_Sex).ToList(); //民族 var nationList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_Nation).ToList(); //政治面貌 var politicsList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_Politics).ToList(); //考生号 var examineeNumList = enlist.Where(x => !string.IsNullOrEmpty(x.ExamineeNum)).Select(x => x.ExamineeNum.Trim()).ToList(); ////准考证号 //var admissionTicketNoList = enlist.Where(x => !string.IsNullOrEmpty(x.AdmissionTicketNo)).Select(x => x.AdmissionTicketNo.Trim()).ToList(); ////身份证号 //var iDNumberList = enlist.Where(x => !string.IsNullOrEmpty(x.IDNumber)).Select(x => x.IDNumber.Trim()).ToList(); //录取专业(省招代码),取后4位 var customStandardCodeList = enlist.Where(x => !string.IsNullOrEmpty(x.Code)).Select(x => x.Code.Trim().Remove(0, x.Code.Length > 4 ? x.Code.Length - 4 : 0)).ToList(); //新生名单 var newStudentAllList = NewStudentDAL.NewStudentRepository.GetList(x => true).ToList(); //对比后的newStudentList var newStudentList = newStudentAllList.Where(x => examineeNumList.Contains(x.ExamineeNum)).ToList(); //省招专业(注:省招代码唯一或学年、学期、院系所ID、专业信息唯一) var customStandardList = NewStudentDAL.CustomStandardSettingRepository.GetList(x => customStandardCodeList.Contains(x.Code)).ToList(); //专业信息 var specialtyList = NewStudentDAL.SpecialtyRepository.GetList(x => true).ToList(); //循环检测数据列,对各数据列进行验证(必填、字典项验证、数据格式等) for (int i = 0; i < enlist.Count; i++) { NewStudentView en = enlist[i]; //Excel表数据视图 CF_NewStudent newStudent = new CF_NewStudent(); //新生信息实体 //考生号 if (string.IsNullOrEmpty(en.ExamineeNum)) { errCount++; errorMsgStr = "ksh(考生号)不能为空"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { reg = new Regex(@"^[A-Za-z0-9]+$"); //考生号正则表达式 if (!reg.IsMatch(en.ExamineeNum.Trim())) { errCount++; errorMsgStr = "ksh(考生号)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //考生号 newStudent.ExamineeNum = en.ExamineeNum.Trim(); } } //准考证号 if (string.IsNullOrEmpty(en.AdmissionTicketNo)) { errCount++; errorMsgStr = "zkzh(准考证号)不能为空"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { reg = new Regex(@"^[A-Za-z0-9]+$"); //准考证号正则表达式 if (!reg.IsMatch(en.AdmissionTicketNo.Trim())) { errCount++; errorMsgStr = "zkzh(准考证号)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //准考证号 newStudent.AdmissionTicketNo = en.AdmissionTicketNo.Trim(); } } //姓名 if (string.IsNullOrEmpty(en.Name)) { errCount++; errorMsgStr = "xm(姓名)不能为空"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //姓名 newStudent.Name = en.Name.Trim(); } //性别 if (string.IsNullOrEmpty(en.SexStr)) { errCount++; errorMsgStr = "性别不能为空"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { if (!int.TryParse(en.SexStr.Trim(), out isInt)) { errCount++; errorMsgStr = "xbdm(性别代码)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //性别代码 var sex = sexList.Where(x => x.Value == Convert.ToInt32(en.SexStr.Trim())).SingleOrDefault(); if (sex == null) { errCount++; errorMsgStr = "xbdm(性别代码)不存在,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //性别 newStudent.SexID = sex.Value; } } } //民族 if (!string.IsNullOrEmpty(en.NationStr)) { if (!int.TryParse(en.NationStr.Trim(), out isInt)) { errCount++; errorMsgStr = "mzdm(民族代码)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { var nation = nationList.Where(x => x.Value == Convert.ToInt32(en.NationStr.Trim())).SingleOrDefault(); if (nation == null) { errCount++; errorMsgStr = "mzdm(民族代码)不存在,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //民族 newStudent.NationID = nation.Value; } } } else { //为空 } //政治面貌 if (!string.IsNullOrEmpty(en.PoliticsStr)) { if (!int.TryParse(en.PoliticsStr.Trim(), out isInt)) { errCount++; errorMsgStr = "zzmmdm(政治面貌代码)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { var politics = politicsList.Where(x => x.Value == Convert.ToInt32(en.PoliticsStr.Trim())).SingleOrDefault(); if (politics == null) { errCount++; errorMsgStr = "zzmmdm(政治面貌代码)不存在,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //政治面貌 newStudent.PoliticsID = politics.Value; } } } else { //为空 } //出生日期 if (!string.IsNullOrEmpty(en.BirthDateStr)) { //reg = new Regex(@"(\d{4})-(\d{1,2})-(\d{1,2})"); //日期正则表达式,2017-12-28 if (!DateTime.TryParse(en.BirthDateStr.Trim(), out result)) { errCount++; errorMsgStr = "csrq(出生日期)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //出生日期 newStudent.BirthDate = Convert.ToDateTime(en.BirthDateStr.Trim()); } } else { //为空 } //考生特征 if (!string.IsNullOrEmpty(en.FeaturesStr)) { if (!int.TryParse(en.FeaturesStr.Trim(), out isInt)) { errCount++; errorMsgStr = "kstzbz(考生特征标记)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //考生特征标记 newStudent.FeaturesID = Convert.ToInt32(en.FeaturesStr.Trim()); } } else { //为空 } //身份证号 if (string.IsNullOrEmpty(en.IDNumber)) { errCount++; errorMsgStr = "sfzh(身份证号)不能为空"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //reg = new Regex(@"^[A-Za-z0-9]+$"); //身份证号正则表达式 //if (!reg.IsMatch(en.IDNumber.Trim())) //{ // errCount++; // errorMsgStr = "sfzh(身份证号)格式不正确,请检查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; //} //else //{ // //身份证号 // newStudent.IDNumber = en.IDNumber.Trim(); //} newStudent.IDNumber = en.IDNumber.Trim(); } //邮政编码 newStudent.ZIPCode = en.ZIPCode; //联系电话 newStudent.Telephone = en.Telephone; //通讯地址 newStudent.Address = en.Address; //行政区划代码 if (!string.IsNullOrEmpty(en.TerritorialStr)) { if (!int.TryParse(en.TerritorialStr.Trim(), out isInt)) { errCount++; errorMsgStr = "xzqhdm(行政区划代码)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //行政区划代码 newStudent.TerritorialID = Convert.ToInt32(en.TerritorialStr.Trim()); } } else { //为空 } //总分 if (!string.IsNullOrEmpty(en.ScoreStr)) { reg = new Regex(@"^[0-9]+([.]{1}[0-9]+){0,1}$"); //数字正则表达式 if (!reg.IsMatch(en.ScoreStr.Trim())) { errCount++; errorMsgStr = "zf(总分)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //总分 newStudent.Score = Convert.ToDecimal(en.ScoreStr.Trim()); } } else { //为空 } //录取专业(省招代码,取后4位) if (string.IsNullOrEmpty(en.Code)) { errCount++; errorMsgStr = "lqzy(录取专业)不能为空"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { reg = new Regex(@"^[0-9]*$"); //数字正则表达式 if (!reg.IsMatch(en.Code.Trim())) { errCount++; errorMsgStr = "lqzy(录取专业)格式不正确,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //录取专业(省招代码,取后4位) var customStandardCode = en.Code.Trim().Remove(0, en.Code.Length > 4 ? en.Code.Length - 4 : 0); var customStandard = customStandardList.Where(x => x.Code == customStandardCode).SingleOrDefault(); if (customStandard == null) { errCount++; errorMsgStr = "对应的省招代码不存在(取lqzy录取专业后四位进行匹配),请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { var specialty = specialtyList.Where(x => x.SpecialtyID == customStandard.SpecialtyID).SingleOrDefault(); if (specialty == null) { errCount++; errorMsgStr = "省招专业对应的专业信息不存在,请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { newStudent.SpecialtyID = specialty.SpecialtyID; newStudent.GradeID = customStandard.YearID; newStudent.SemesterID = customStandard.SemesterID; newStudent.CollegeID = customStandard.CollegeID; } } } } //备注 newStudent.Remark = en.Remark; ////Excel表重复性验证(注:当数据表中没有此记录,但是Excel中有重复数据时的去掉) //for (int j = i + 1; j < enlist.Count; j++) //{ // NewStudentView enA = enlist[j]; // //根据Excel表中的业务主键进行去重(考生号唯一) // if (en.ExamineeNum == enA.ExamineeNum) // { // //用于标识Excel表中的重复记录(由于是批量进行插入数据表) // } //} //由于新生管理模块中存在新生分配的业务,在进行新生信息新增时, //需要对新生名单、录取名单中的信息进行提示性验证(业务主键:考生号唯一) //处理方法:进行Excel导入时,对新生名单更新、录取名单中的信息提示性验证(当错误信息抛出) //注:(未分配-1,预分配-2,已分配-3) //数据表重复性验证(考生号唯一) var examineeNumVerify = newStudentList.Where(x => x.ExamineeNum == newStudent.ExamineeNum).SingleOrDefault(); if (examineeNumVerify == null) { //if (!string.IsNullOrEmpty(newStudent.AdmissionTicketNo)) //{ // if (!newStudentUpList.Any(x => x.AdmissionTicketNo == newStudent.AdmissionTicketNo)) // { // if (!newStudentInList.Any(x => x.AdmissionTicketNo == newStudent.AdmissionTicketNo)) // { // var admissionTicketNoVerify = newStudentList.Where(x => x.AdmissionTicketNo == newStudent.AdmissionTicketNo).SingleOrDefault(); // if (admissionTicketNoVerify != null) // { // if (admissionTicketNoVerify.AssignStatus == (int)CF_AssignStatus.PreAssigned) // { // errCount++; // errorMsgStr = "已存在相同的准考证号(准考证号重复),请核查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } // else if (admissionTicketNoVerify.AssignStatus == (int)CF_AssignStatus.Assigned) // { // errCount++; // errorMsgStr = "录取名单信息中已存在此准考证号(准考证号重复),请核查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } // else // { // errCount++; // errorMsgStr = "新生名单信息中已存在此准考证号(准考证号重复),请核查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } // } // } // else // { // errCount++; // errorMsgStr = "准考证号重复(Excel中),请检查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } // } // else // { // errCount++; // errorMsgStr = "准考证号重复(Excel中),请检查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } //} //新增 if (!newStudentInList.Any(x => x.ExamineeNum == newStudent.ExamineeNum)) { newStudent.NewStudentID = Guid.NewGuid(); newStudent.CertificatesType = (int)CF_CertificatesType.IdCrad; newStudent.IsDoubt = false; newStudent.AssignStatus = (int)CF_AssignStatus.PreAssigned; SetNewStatus(newStudent, (int)CF_NewStudentEnterStatus.NotSubmitted); newStudentInList.Add(newStudent); inCount++; } else { //Excel表重复性验证 //(注:当数据表中没有此记录,但是Excel中有重复数据,可在此处进行抛出到失败数据文件中) errCount++; errorMsgStr = "考生号重复(Excel中),请检查"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } } else { //由于新生管理模块中存在新生分配的业务,只更新分配状态为:未分配-1、预分配-2 if (examineeNumVerify.AssignStatus == (int)CF_AssignStatus.Assigned) { errCount++; errorMsgStr = "导入失败,录取名单信息中已存在此考生号(考生号重复)"; en.ErrorMessage = errorMsgStr; errList.Add(en); errorMsg.AppendLine(errorMsgStr); continue; } else { //更新(Excel有重复时,以最后一条记录的更新为准) //if (!string.IsNullOrEmpty(newStudent.AdmissionTicketNo)) //{ // if (!newStudentInList.Any(x => x.AdmissionTicketNo == newStudent.AdmissionTicketNo)) // { // if (!newStudentUpList.Any(x => x.AdmissionTicketNo == newStudent.AdmissionTicketNo)) // { // var admissionTicketNoVerify = newStudentList.Where(x => x.AdmissionTicketNo == newStudent.AdmissionTicketNo).SingleOrDefault(); // if (admissionTicketNoVerify != null) // { // if (admissionTicketNoVerify.AssignStatus == (int)CF_AssignStatus.PreAssigned) // { // errCount++; // errorMsgStr = "已存在相同的准考证号(准考证号重复),请核查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } // else if (admissionTicketNoVerify.AssignStatus == (int)CF_AssignStatus.Assigned) // { // errCount++; // errorMsgStr = "录取名单信息中已存在此准考证号(准考证号重复),请核查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } // else // { // errCount++; // errorMsgStr = "新生名单信息中已存在此准考证号(准考证号重复),请核查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } // } // } // else // { // errCount++; // errorMsgStr = "准考证号重复(Excel中),请检查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } // } // else // { // errCount++; // errorMsgStr = "准考证号重复(Excel中),请检查"; // en.ErrorMessage = errorMsgStr; // errList.Add(en); // errorMsg.AppendLine(errorMsgStr); // continue; // } //} examineeNumVerify.ExamineeNum = newStudent.ExamineeNum; examineeNumVerify.AdmissionTicketNo = newStudent.AdmissionTicketNo; examineeNumVerify.Name = newStudent.Name; examineeNumVerify.SexID = newStudent.SexID; examineeNumVerify.NationID = newStudent.NationID; examineeNumVerify.PoliticsID = newStudent.PoliticsID; examineeNumVerify.BirthDate = newStudent.BirthDate; //examineeNumVerify.CertificatesType = newStudent.CertificatesType; examineeNumVerify.IDNumber = newStudent.IDNumber; examineeNumVerify.GradeID = newStudent.GradeID; examineeNumVerify.SemesterID = newStudent.SemesterID; examineeNumVerify.SpecialtyID = newStudent.SpecialtyID; //examineeNumVerify.EntranceDate = newStudent.EntranceDate; examineeNumVerify.Score = newStudent.Score; examineeNumVerify.Telephone = newStudent.Telephone; examineeNumVerify.ZIPCode = newStudent.ZIPCode; //examineeNumVerify.EntranceWayID = newStudent.EntranceWayID; examineeNumVerify.FeaturesID = newStudent.FeaturesID; examineeNumVerify.TerritorialID = newStudent.TerritorialID; examineeNumVerify.Address = newStudent.Address; //examineeNumVerify.Dormitory = newStudent.Dormitory; examineeNumVerify.AssignStatus = (int)CF_AssignStatus.PreAssigned; examineeNumVerify.CollegeID = newStudent.CollegeID; examineeNumVerify.Remark = newStudent.Remark; SetModifyStatus(examineeNumVerify); newStudentUpList.Add(examineeNumVerify); upCount++; } } } using (TransactionScope ts = new TransactionScope()) { UnitOfWork.BulkInsert(newStudentInList); if (newStudentUpList != null && newStudentUpList.Count() > 0) { UnitOfWork.BatchUpdate(newStudentUpList); } ts.Complete(); } errdataList = errList.Distinct().ToList(); } catch (Exception) { throw; } } } }