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;
}
}
}
}