JW/学校扩展文件/EMIS.ExtensionLogic.HBGD/ServiceLogic/ScoreManage/LevelScoreServices.cs

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.ScoreManage.LevelScoreManage;

namespace EMIS.ExtensionLogic.ServiceLogic.ScoreManage
{
    public class LevelScoreServices : EMIS.CommonLogic.ScoreManage.LevelScoreManage.LevelScoreServices
    {
        /// <summary>
        /// 编辑
        /// 注：处理工作流程状态(河北工业大学)
        /// </summary>
        /// <param name="levelScoreView"></param>
        public override void LevelScoreEdit(LevelScoreView levelScoreView)
        {
            try
            {
                var approveStatusList = this.GetStatusViewList();
                if (approveStatusList == null || approveStatusList.Count() <= 0)
                {
                    throw new Exception("工作流平台中，科目成绩录入流程未配置，请核查。");
                }
                var startStatusID = this.GetStartStatus();
                if (startStatusID == null)
                {
                    throw new Exception("工作流平台中，科目成绩录入流程开始环节未配置，请核查。");
                }
                var correctEndStatusID = this.GetCorrectEndStatus();
                if (correctEndStatusID == null)
                {
                    throw new Exception("工作流平台中，科目成绩录入流程结束环节未配置，请核查。");
                }
                ER_LevelScore levelScoreVerify = null;
                var levelScoreList = LevelScoreDAL.Value.LevelScoreRepository.GetList(x => x.LevelScoreID != levelScoreView.LevelScoreID && x.UserID == levelScoreView.UserID && x.ExaminationSubjectID == levelScoreView.ExaminationSubjectID).ToList();
                if (levelScoreList != null && levelScoreList.Count() > 0)
                {
                    levelScoreVerify = levelScoreList.Where(x => x.ExaminationDate.ToStringEx("yyyyMM") == levelScoreView.ExaminationDate.ToStringEx("yyyyMM")).SingleOrDefault();
                }
                if (levelScoreVerify == null)
                {
                    if (levelScoreView.LevelScoreID != Guid.Empty)
                    {
                        var levelScore = LevelScoreDAL.Value.LevelScoreRepository.GetList(x => x.LevelScoreID == levelScoreView.LevelScoreID).SingleOrDefault();
                        if (levelScore == null)
                        {
                            throw new Exception("数据有误，请核查。");
                        }
                        else
                        {
                            //表示修改
                            levelScore.UserID = levelScoreView.UserID;
                            levelScore.ExaminationSubjectID = levelScoreView.ExaminationSubjectID;
                            levelScore.SchoolyearID = levelScoreView.SchoolyearID;
                            levelScore.ExaminationDate = levelScoreView.ExaminationDate;
                            levelScore.ScoreNo = levelScoreView.ScoreNo;
                            levelScore.TotalScore = levelScoreView.TotalScore;
                            levelScore.Remark = levelScoreView.Remark;
                            SetModifyStatus(levelScore);
                        }
                    }
                    else
                    {
                        //表示新增
                        var newLevelScore = new ER_LevelScore();
                        newLevelScore.LevelScoreID = Guid.NewGuid();
                        newLevelScore.UserID = levelScoreView.UserID;
                        newLevelScore.ExaminationSubjectID = levelScoreView.ExaminationSubjectID;
                        newLevelScore.SchoolyearID = levelScoreView.SchoolyearID;
                        newLevelScore.ExaminationDate = levelScoreView.ExaminationDate;
                        newLevelScore.ScoreNo = levelScoreView.ScoreNo;
                        newLevelScore.TotalScore = levelScoreView.TotalScore;
                        newLevelScore.Remark = levelScoreView.Remark;
                        newLevelScore.ApprovalStatus = startStatusID;
                        SetNewStatus(newLevelScore);
                        UnitOfWork.Add(newLevelScore);
                    }
                }
                else
                {
                    throw new Exception("已存在相同的科目成绩信息(学生信息、考试科目、考试日期唯一)，请核查。");
                }
                UnitOfWork.Commit();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <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 override void LevelScoreImport(Dictionary<string, string> cellheader, out int? inCount, out int? upCount, out List<LevelScoreView> errdataList, out int? errCount, string sourcePhysicalPath)
        {
            try
            {
                var approveStatusList = this.GetStatusViewList();
                if (approveStatusList == null || approveStatusList.Count() <= 0)
                {
                    throw new Exception("工作流平台中，科目成绩录入流程未配置，请核查。");
                }
                var startStatusID = this.GetStartStatus();
                if (startStatusID == null)
                {
                    throw new Exception("工作流平台中，科目成绩录入流程开始环节未配置，请核查。");
                }
                var correctEndStatusID = this.GetCorrectEndStatus();
                if (correctEndStatusID == null)
                {
                    throw new Exception("工作流平台中，科目成绩录入流程结束环节未配置，请核查。");
                }
                StringBuilder errorMsg = new StringBuilder();
                var errList = new List<LevelScoreView>();
                cellheader.Remove("ErrorMessage");
                var enlist = NpoiExcelHelper.ExcelToEntityList<LevelScoreView>(cellheader, sourcePhysicalPath, out errorMsg, out errList);
                cellheader.Add("ErrorMessage", "未导入原因");
                //对List集合进行有效性校验
                if (enlist.Count() <= 0)
                {
                    throw new Exception("Excel文件数据为空，请检查。");
                }
                Regex reg = null;
                DateTime result;
                decimal isDecimal;
                inCount = 0;
                upCount = 0;
                errCount = 0;
                string errorMsgStr = "";
                List<ER_LevelScore> levelScoreInList = new List<ER_LevelScore>();
                List<ER_LevelScore> levelScoreUpList = new List<ER_LevelScore>();
                //将循环中相关数据库查询统一查询出来进行匹配(尽量避免在循环中进行数据库查询)
                //学号
                var studentNoList = enlist.Where(x => !string.IsNullOrEmpty(x.StudentNo)).Select(x => x.StudentNo).ToList();
                var studentList = LevelScoreDAL.Value.StudentRepository.GetList(x => studentNoList.Contains(x.Sys_User.LoginID), (x => x.Sys_User)).ToList();
                var userIDList = studentList.Select(x => x.UserID).ToList();
                var examinationSubjectNameList = enlist.Where(x => !string.IsNullOrEmpty(x.ExaminationSubjectName)).Select(x => x.ExaminationSubjectName).ToList();
                var examinationSubjectList = LevelScoreDAL.Value.ExaminationSubjectRepository.GetList(x => examinationSubjectNameList.Contains(x.Name)).ToList();
                var examinationSubjectIDList = examinationSubjectList.Select(x => x.ExaminationSubjectID).ToList();
                var schoolyearCodeList = enlist.Where(x => !string.IsNullOrEmpty(x.SchoolyearCode)).Select(x => x.SchoolyearCode).ToList();
                var schoolyearList = LevelScoreDAL.Value.SchoolyearRepository.GetList(x => schoolyearCodeList.Contains(x.Code)).ToList();
                var schoolyearIDList = schoolyearList.Select(x => x.SchoolyearID).ToList();
                var levelScoreList = LevelScoreDAL.Value.LevelScoreRepository.GetList(x => schoolyearIDList.Contains(x.SchoolyearID.Value)).ToList();
                levelScoreList = levelScoreList.Where(x => userIDList.Contains(x.UserID.Value) && examinationSubjectIDList.Contains(x.ExaminationSubjectID.Value)).ToList();
                //循环检测数据列,对各数据列进行验证（必填、字典项验证、数据格式等）
                for (int i = 0; i < enlist.Count; i++)
                {
                    LevelScoreView en = enlist[i];
                    ER_LevelScore newLevelScore = new ER_LevelScore();
                    //学号
                    if (string.IsNullOrEmpty(en.StudentNo))
                    {
                        errCount++;
                        errorMsgStr = "学号不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var student = studentList.Where(x => x.Sys_User.LoginID == en.StudentNo.Trim()).SingleOrDefault();
                        if (student == null)
                        {
                            errCount++;
                            errorMsgStr = "学号不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            newLevelScore.UserID = student.UserID;
                        }
                    }
                    //考试科目
                    if (string.IsNullOrEmpty(en.ExaminationSubjectName))
                    {
                        errCount++;
                        errorMsgStr = "考试科目不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var examinationSubject = examinationSubjectList.Where(x => x.Name == en.ExaminationSubjectName.Trim()).SingleOrDefault();
                        if (examinationSubject == null)
                        {
                            errCount++;
                            errorMsgStr = "考试科目不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            newLevelScore.ExaminationSubjectID = examinationSubject.ExaminationSubjectID;
                        }
                    }
                    //学年学期
                    if (string.IsNullOrEmpty(en.SchoolyearCode))
                    {
                        errCount++;
                        errorMsgStr = "学年学期不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var schoolyear = schoolyearList.Where(x => x.Code == en.SchoolyearCode.Trim()).SingleOrDefault();
                        if (schoolyear == null)
                        {
                            errCount++;
                            errorMsgStr = "学年学期不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            newLevelScore.SchoolyearID = schoolyear.SchoolyearID;
                        }
                    }
                    //考试日期
                    if (string.IsNullOrWhiteSpace(en.ExaminationDateStr))
                    {
                        errCount++;
                        errorMsgStr = "考试日期不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        //reg = new Regex(@"(\d{4})-(\d{1,2})-(\d{1,2})"); //日期正则表达式,2017-12-28
                        if (!DateTime.TryParse(en.ExaminationDateStr, out result))
                        {
                            errCount++;
                            errorMsgStr = "考试日期格式不正确，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            newLevelScore.ExaminationDate = Convert.ToDateTime(en.ExaminationDateStr);
                        }
                    }
                    //成绩单编号
                    if (string.IsNullOrWhiteSpace(en.ScoreNo))
                    {
                        //不考虑
                    }
                    else
                    {
                        reg = new Regex(@"^[0-9a-zA-Z\s?]+$"); //正则表达式(请输入数字或英文字母)
                        if (!reg.IsMatch(en.ScoreNo))
                        {
                            errCount++;
                            errorMsgStr = "成绩单编号格式不正确，请检查(数字或英文字母)";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            newLevelScore.ScoreNo = en.ScoreNo.Trim();
                        }
                    }
                    //总成绩
                    if (string.IsNullOrEmpty(en.TotalScoreStr))
                    {
                        errCount++;
                        errorMsgStr = "总成绩不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        if (!Decimal.TryParse(en.TotalScoreStr.Trim(), out isDecimal))
                        {
                            errCount++;
                            errorMsgStr = "总成绩格式不正确，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            newLevelScore.TotalScore = Convert.ToDecimal(en.TotalScoreStr.Trim());
                        }
                    }
                    //备注
                    if (string.IsNullOrWhiteSpace(en.Remark))
                    {
                        //不考虑
                    }
                    else
                    {
                        newLevelScore.Remark = en.Remark;
                    }
                    //数据表重复性验证(用户ID、考试科目ID、考试日期唯一)
                    var levelScoreVerify = levelScoreList.Where(x => x.UserID == newLevelScore.UserID && x.ExaminationSubjectID == newLevelScore.ExaminationSubjectID && x.ExaminationDate.ToStringEx("yyyyMM") == newLevelScore.ExaminationDate.ToStringEx("yyyyMM")).SingleOrDefault();
                    if (levelScoreVerify == null)
                    {
                        //新增
                        if (!levelScoreInList.Any(x => x.UserID == newLevelScore.UserID && x.ExaminationSubjectID == newLevelScore.ExaminationSubjectID && x.ExaminationDate.ToStringEx("yyyyMM") == newLevelScore.ExaminationDate.ToStringEx("yyyyMM")))
                        {
                            newLevelScore.LevelScoreID = Guid.NewGuid();
                            newLevelScore.ApprovalStatus = startStatusID;
                            SetNewStatus(newLevelScore);
                            levelScoreInList.Add(newLevelScore);
                            inCount++;
                        }
                        else
                        {
                            //Excel表重复性验证
                            //（注：当数据表中没有此记录，但是Excel中有重复数据,可在此处进行抛出到失败数据文件中,目前暂不考虑）

                            inCount++;
                        }
                    }
                    else
                    {
                        //更新(Excel有重复时，以最后一条记录的更新为准)
                        if (levelScoreVerify.ApprovalStatus != correctEndStatusID)
                        {
                            levelScoreVerify.SchoolyearID = newLevelScore.SchoolyearID;
                            levelScoreVerify.ScoreNo = newLevelScore.ScoreNo;
                            levelScoreVerify.TotalScore = newLevelScore.TotalScore;
                            levelScoreVerify.Remark = newLevelScore.Remark;
                            SetModifyStatus(levelScoreVerify);
                            levelScoreUpList.Add(levelScoreVerify);
                            upCount++;
                        }
                        else
                        {
                            errCount++;
                            errorMsgStr = "存在已审核通过的相同的考试科目(学生信息、考试科目、考试日期唯一)，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                    }
                }
                TransactionOptions transactionOption = new TransactionOptions();
                transactionOption.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
                transactionOption.Timeout = new TimeSpan(0, 2, 0);
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, transactionOption))
                {
                    UnitOfWork.BulkInsert(levelScoreInList);
                    if (levelScoreUpList != null && levelScoreUpList.Count() > 0)
                    {
                        UnitOfWork.BatchUpdate(levelScoreUpList);
                    }
                    ts.Complete();
                }
                errdataList = errList.Distinct().ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}