JW/ServiceLogic/EMIS.CommonLogic/ScoreManage/ImportScoreServices.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Text.RegularExpressions;
using System.Data;
using System.IO;
using System.Web;
using System.Web.Mvc;
using System.Transactions;
using Bowin.Common.Data;
using Bowin.Common.Linq;
using Bowin.Common.Utility;
using Bowin.Common.Linq.Entity;
using EMIS.Utility;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.ViewModel.ScoreManage;
using EMIS.ViewModel.CalendarManage;
using EMIS.DataLogic.ScoreManage;
using EMIS.CommonLogic.Students;
using EMIS.CommonLogic.StudentManage.StudentStatistics;

namespace EMIS.CommonLogic.ScoreManage
{
    public class ImportScoreServices : BaseServices, IImportScoreServices
    {
        public ImportScoreDAL ImportScoreDAL { get; set; }
        public IInSchoolSettingServices InSchoolSettingServices { get; set; }

        /// <summary>
        /// 查询对应的导入成绩(平时成绩)信息View
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="schoolyearID"></param>
        /// <param name="collegeID"></param>
        /// <param name="yearID"></param>
        /// <param name="standardID"></param>
        /// <param name="educationID"></param>
        /// <param name="learningformID"></param>
        /// <param name="learnSystem"></param>
        /// <param name="inSchoolStatus"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<ImportScoreView> GetImportScoreViewGrid(ConfiguretView configuretView, Guid? schoolyearID,
                Guid? collegeID, int? yearID, int? standardID, int? educationID, int? learningformID, string learnSystem,
                int? inSchoolStatus, int pageIndex, int pageSize)
        { 
            //导入成绩
            Expression<Func<ER_ImportScore, bool>> expImportScore = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (schoolyearID.HasValue)
            {
                expImportScore = expImportScore.And(x => x.SchoolyearID == schoolyearID);
            }

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            if (inSchoolStatus != null && inSchoolStatus > -1)
            {
                var inschoolStatusList = InSchoolSettingServices.GetInschoolStatusList(true);
                if (inSchoolStatus == 1)
                {
                    //表示在校
                    expStudent = expStudent.And(x => inschoolStatusList.Contains(x.InSchoolStatusID));
                }
                if (inSchoolStatus == 0)
                {
                    //不在校
                    expStudent = expStudent.And(x => !inschoolStatusList.Contains(x.InSchoolStatusID));
                }
            }

            var query = ImportScoreDAL.GetImportScoreViewQueryable(expImportScore, expStudent);

            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }
            if (yearID.HasValue)
            {
                query = query.Where(x => x.GradeID == yearID);
            }
            if (standardID.HasValue)
            {
                query = query.Where(x => x.StandardID == standardID);
            }
            if (educationID.HasValue)
            {
                query = query.Where(x => x.EducationID == educationID);
            }
            if (learningformID.HasValue)
            {
                query = query.Where(x => x.LearningformID == learningformID);
            }
            if (!string.IsNullOrEmpty(learnSystem) && learnSystem != "-1")
            {
                var LearnSystems = Convert.ToDecimal(learnSystem);
                query = query.Where(x => x.LearnSystem == LearnSystems);
            }

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

            return this.GetQueryByDataRangeByCollege(query)
                    .OrderBy(x => x.StudentNo.Length).ThenBy(x => x.StudentNo)
                    .ThenBy(x => x.SchoolyearCode).ThenBy(x => x.CourseCode.Length)
                    .ThenBy(x => x.CourseCode)
                    .ToGridResultSet<ImportScoreView>(pageIndex, pageSize);

        }

        /// <summary>
        /// 查询对应的导入成绩(平时成绩)信息List
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="schoolyearID"></param>
        /// <param name="collegeID"></param>
        /// <param name="yearID"></param>
        /// <param name="standardID"></param>
        /// <param name="educationID"></param>
        /// <param name="learningformID"></param>
        /// <param name="learnSystem"></param>
        /// <param name="inSchoolStatus"></param>
        /// <returns></returns>
        public List<ImportScoreView> GetImportScoreViewList(ConfiguretView configuretView, Guid? schoolyearID,
                Guid? collegeID, int? yearID, int? standardID, int? educationID, int? learningformID, string learnSystem,
                int? inSchoolStatus)
        {
            //导入成绩
            Expression<Func<ER_ImportScore, bool>> expImportScore = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (schoolyearID.HasValue)
            {
                expImportScore = expImportScore.And(x => x.SchoolyearID == schoolyearID);
            }

            //学生信息
            Expression<Func<CF_Student, bool>> expStudent = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);

            if (inSchoolStatus != null && inSchoolStatus > -1)
            {
                var inschoolStatusList = InSchoolSettingServices.GetInschoolStatusList(true);
                if (inSchoolStatus == 1)
                {
                    //表示在校
                    expStudent = expStudent.And(x => inschoolStatusList.Contains(x.InSchoolStatusID));
                }
                if (inSchoolStatus == 0)
                {
                    //不在校
                    expStudent = expStudent.And(x => !inschoolStatusList.Contains(x.InSchoolStatusID));
                }
            }

            var query = ImportScoreDAL.GetImportScoreViewQueryable(expImportScore, expStudent);

            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }
            if (yearID.HasValue)
            {
                query = query.Where(x => x.GradeID == yearID);
            }
            if (standardID.HasValue)
            {
                query = query.Where(x => x.StandardID == standardID);
            }
            if (educationID.HasValue)
            {
                query = query.Where(x => x.EducationID == educationID);
            }
            if (learningformID.HasValue)
            {
                query = query.Where(x => x.LearningformID == learningformID);
            }
            if (!string.IsNullOrEmpty(learnSystem) && learnSystem != "-1")
            {
                var LearnSystems = Convert.ToDecimal(learnSystem);
                query = query.Where(x => x.LearnSystem == LearnSystems);
            }

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

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

        /// <summary>
        /// 查询对应的导入成绩信息ImportScoreView（根据主键ID）
        /// </summary>
        /// <param name="importScoreID"></param>
        /// <returns></returns>
        public ImportScoreView GetImportScoreViewByID(Guid importScoreID)
        {
            try
            {
                Expression<Func<ER_ImportScore, bool>> expImportScore = (x => x.ImportScoreID == importScoreID);
                Expression<Func<CF_Student, bool>> expStudent = (x => true);

                return this.ImportScoreDAL.GetImportScoreViewQueryable(expImportScore, expStudent)
                        .SingleOrDefault();
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 编辑（新增、修改，业务主键：学年学期ID、用户ID、课程信息ID）
        /// </summary>
        /// <param name="importScoreView"></param>
        public void ImportScoreEdit(ImportScoreView importScoreView)
        {
            try
            {
                //查询数据库进行验证
                var importScoreVerification = ImportScoreDAL.ImportScoreRepository
                        .GetList(x => x.ImportScoreID != importScoreView.ImportScoreID
                                && x.SchoolyearID == importScoreView.SchoolyearID
                                && x.UserID == importScoreView.UserID
                                && x.CoursematerialID == importScoreView.CoursematerialID)
                                .SingleOrDefault();

                if (importScoreVerification == null)
                {
                    //数据有误验证
                    if (importScoreView.ImportScoreID != Guid.Empty)
                    {
                        var importScore = ImportScoreDAL.ImportScoreRepository
                                .GetList(x => x.ImportScoreID == importScoreView.ImportScoreID)
                                .SingleOrDefault();
                        if (importScore == null)
                        {
                            throw new Exception("数据有误，请核查");
                        }
                        else
                        {
                            //表示修改
                            importScore.SchoolyearID = importScoreView.SchoolyearID;
                            importScore.UserID = importScoreView.UserID;
                            importScore.CoursematerialID = importScoreView.CoursematerialID;
                            importScore.Score = importScoreView.Score;
                            importScore.Remark = importScoreView.Remark;
                            SetModifyStatus(importScore);
                        }
                    }
                    else
                    {
                        //表示新增
                        ER_ImportScore importScore = new ER_ImportScore();
                        importScore.ImportScoreID = Guid.NewGuid();
                        importScore.SchoolyearID = importScoreView.SchoolyearID;
                        importScore.UserID = importScoreView.UserID;
                        importScore.CoursematerialID = importScoreView.CoursematerialID;
                        importScore.Score = importScoreView.Score;
                        importScore.Remark = importScoreView.Remark;
                        SetNewStatus(importScore);
                        UnitOfWork.Add(importScore);
                    }
                }
                else
                {
                    throw new Exception("已存在相同的平时成绩，请核查");
                }

                //事务提交
                UnitOfWork.Commit();
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="importScoreIDList"></param>
        /// <returns></returns>
        public bool ImportScoreDelete(IList<Guid> importScoreIDList)
        {
            try
            {
                UnitOfWork.Remove<ER_ImportScore>(x => importScoreIDList.Contains(x.ImportScoreID));
                UnitOfWork.Commit();
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <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 ImportScoreImport(Dictionary<string, string> cellheader, out int? inCount, out int? upCount, out List<ImportScoreView> errdataList, out int? errCount, string sourcePhysicalPath)
        {
            try
            {
                StringBuilder errorMsg = new StringBuilder();
                List<ImportScoreView> errList = new List<ImportScoreView>();
                cellheader.Remove("ErrorMessage");
                List<ImportScoreView> enlist = NpoiExcelHelper.ExcelToEntityList<ImportScoreView>(cellheader, sourcePhysicalPath, out errorMsg, out errList);
                cellheader.Add("ErrorMessage", "未导入原因");
                //对List集合进行有效性校验
                if (enlist.Count() <= 0)
                {
                    throw new Exception("Excel文件数据为空，请检查。");
                }
                //Regex reg = null; //正则表达式
                decimal isDecimal; //用于返回判断decimal列格式
                inCount = 0; //导入个数
                upCount = 0; //更新个数
                errCount = 0; //失败个数
                string errorMsgStr = ""; //错误信息
                List<ER_ImportScore> importScoreInList = new List<ER_ImportScore>();
                List<ER_ImportScore> importScoreUpList = new List<ER_ImportScore>();
                //将循环中相关数据库查询统一查询出来进行匹配(尽量避免在循环中进行数据库查询)
                //学年学期
                var schoolyearCodeList = enlist.Where(x => !string.IsNullOrEmpty(x.SchoolyearCode)).Select(x => x.SchoolyearCode).ToList();
                //查询对应的学年学期List
                var schoolyearList = ImportScoreDAL.SchoolyearRepository.GetList(x => schoolyearCodeList.Contains(x.Code)).ToList();
                //对应的学年学期信息schoolyearIDList
                var schoolyearIDList = schoolyearList.Select(x => x.SchoolyearID).ToList();
                //学号
                var studentNoList = enlist.Where(x => !string.IsNullOrEmpty(x.StudentNo)).Select(x => x.StudentNo).ToList();
                //查询对应的学生信息List
                var studentList = ImportScoreDAL.StudentRepository.GetList(x => studentNoList.Contains(x.Sys_User.LoginID), (x => x.Sys_User)).ToList();
                //对应的学生信息userIDList
                var userIDList = studentList.Select(x => x.UserID).ToList();
                //课程代码
                var CourseCodeList = enlist.Where(x => !string.IsNullOrEmpty(x.CourseCode)).Select(x => x.CourseCode).ToList();
                //查询对应的课程信息List
                var courseList = ImportScoreDAL.CoursematerialRepository.GetList(x => CourseCodeList.Contains(x.CourseCode)).ToList();
                //对应的课程信息coursematerialIDList
                var coursematerialIDList = courseList.Select(x => x.CoursematerialID).ToList();
                //平时成绩信息List(暂时只根据schoolyearIDList查询)
                var importScoreList = ImportScoreDAL.ImportScoreRepository.GetList(x => schoolyearIDList.Contains(x.SchoolyearID.Value)).ToList();
                //对比后的成绩信息List（再比对userIDList、coursematerialIDList）
                importScoreList = importScoreList.Where(x => userIDList.Contains(x.UserID.Value) && coursematerialIDList.Contains(x.CoursematerialID.Value)).ToList();
                //循环检测数据列,对各数据列进行验证（必填、字典项验证、数据格式等）
                for (int i = 0; i < enlist.Count; i++)
                {
                    ImportScoreView en = enlist[i]; //Excel表数据视图

                    ER_ImportScore importScore = new ER_ImportScore();

                    //学年学期
                    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
                        {
                            //学年学期ID
                            importScore.SchoolyearID = schoolyear.SchoolyearID;
                        }
                    }
                    //学号
                    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
                        {
                            //用户ID
                            importScore.UserID = student.UserID;
                        }
                    }
                    //课程代码
                    if (string.IsNullOrEmpty(en.CourseCode))
                    {
                        errCount++;
                        errorMsgStr = "课程代码不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var course = courseList.Where(x => x.CourseCode == en.CourseCode.Trim()).SingleOrDefault();
                        if (course == null)
                        {
                            errCount++;
                            errorMsgStr = "课程代码不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //课程信息ID
                            importScore.CoursematerialID = course.CoursematerialID;
                        }
                    }
                    //平时成绩
                    if (string.IsNullOrEmpty(en.ScoreStr))
                    {
                        errCount++;
                        errorMsgStr = "平时成绩不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        if (!Decimal.TryParse(en.ScoreStr.Trim(), out isDecimal))
                        {
                            errCount++;
                            errorMsgStr = "平时成绩格式不正确，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //平时成绩
                            if (isDecimal < 0)
                            {
                                errCount++;
                                errorMsgStr = "平时成绩不能小于0，请检查";
                                en.ErrorMessage = errorMsgStr;
                                errList.Add(en);
                                errorMsg.AppendLine(errorMsgStr);
                                continue;
                            }
                            else
                            {
                                importScore.Score = Convert.ToDecimal(en.ScoreStr.Trim());
                            }
                        }
                    }

                    //备注
                    importScore.Remark = en.Remark;

                    //数据表重复性验证(学年学期ID、用户ID、课程信息ID唯一)
                    var importScoreVerify = importScoreList
                            .Where(x => x.SchoolyearID == importScore.SchoolyearID 
                                    && x.UserID == importScore.UserID 
                                    && x.CoursematerialID == importScore.CoursematerialID)
                                    .SingleOrDefault();
                    if (importScoreVerify == null)
                    {
                        //新增
                        if (!importScoreInList.Any(x => x.SchoolyearID == importScore.SchoolyearID
                            && x.UserID == importScore.UserID
                            && x.CoursematerialID == importScore.CoursematerialID))
                        {
                            importScore.ImportScoreID = Guid.NewGuid();
                            SetNewStatus(importScore);
                            importScoreInList.Add(importScore);

                            inCount++;
                        }
                        else
                        {
                            //Excel表重复性验证
                            //（注：当数据表中没有此记录，但是Excel中有重复数据,可在此处进行抛出到失败数据文件中,目前暂不考虑）

                            inCount++;
                        }
                    }
                    else
                    {
                        //更新(Excel有重复时，以最后一条记录的更新为准)
                        importScoreVerify.Score = importScore.Score;
                        importScoreVerify.Remark = importScore.Remark;
                        SetModifyStatus(importScoreVerify);
                        importScoreUpList.Add(importScoreVerify);
                        upCount++;
                    }
                }
                using (TransactionScope ts = new TransactionScope())
                {
                    UnitOfWork.BulkInsert(importScoreInList);
                    if (importScoreUpList != null && importScoreUpList.Count() > 0)
                    {
                        UnitOfWork.BatchUpdate(importScoreUpList);
                    }
                    ts.Complete();
                }
                errdataList = errList.Distinct().ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}