YBEE.EQM/YBEE.EQM.Application/Exam/ExamTeacherCourse/Services/ExamTeacherCourseService.cs

using Furion.DatabaseAccessor.Extensions;
using NPOI.SS.UserModel;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 监测教师任教科目管理服务
/// </summary>
public class ExamTeacherCourseService : IExamTeacherCourseService, ITransient
{
    private readonly IRepository<ExamTeacherCourse> _rep;
    private readonly ISysDictDataService _sysDictDataService;
    private readonly IExamGradeService _examGradeService;
    private readonly ISchoolClassService _schoolClassService;
    private readonly ICourseService _courseService;

    public ExamTeacherCourseService(IRepository<ExamTeacherCourse> rep, ISysDictDataService sysDictDataService, IExamGradeService examGradeService, ISchoolClassService schoolClassService, ICourseService courseService)
    {
        _rep = rep;
        _sysDictDataService = sysDictDataService;
        _examGradeService = examGradeService;
        _schoolClassService = schoolClassService;
        _courseService = courseService;
    }

    #region 批量导入
    /// <summary>
    /// 上传监测教师批量导入文件
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<UploadExamDataOutput<UploadExamTeacherCourseOutput>> Upload(string filePath, int examPlanId)
    {
        UploadExamDataOutput<UploadExamTeacherCourseOutput> result = new();
        try
        {
            using FileStream fs = new(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath, fs);
            var sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();

            #region 验证表结构
            // 少于2行验证
            if (sheet.LastRowNum < 2)
            {
                result.ErrorMessage.Add("第一行应为填写说明，第二行应为标题行，请勿修改模板结构。");
                return result;
            }

            // 跳过第一行
            rows.MoveNext();

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;
            int GRADE_INDEX = index++;
            int CLASS_INDEX = index++;
            int COURSE_INDEX = index++;
            int NAME_INDEX = index++;
            int CERT_TYPE_INDEX = index++;
            int ID_NUM_INDEX = index++;
            int REMARK_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { GRADE_INDEX, "年级号" },
                { CLASS_INDEX, "班级号" },
                { COURSE_INDEX, "科目" },
                { NAME_INDEX, "教师姓名" },
                { CERT_TYPE_INDEX, "证件类型" },
                { ID_NUM_INDEX, "证件号码" },
                { REMARK_INDEX, "备注" },
            };
            List<string> headerErrors = new();
            for (int i = 0; i <= index; i++)
            {
                if (headerRow.GetCell(i)?.ToString() != headers[i])
                {
                    char letter = (char)('A' + i);
                    headerErrors.Add(letter.ToString());
                }
            }
            if (headerErrors.Any())
            {
                string columnErrors = string.Join("、", headerErrors);
                result.ErrorMessage.Add($"第2行标题行{columnErrors}列名错误。从A列开始依次应为年级号、班级号、科目、教师姓名、证件类型、证件号码和备注。");
                return result;
            }
            result.StructureCorrect = true;
            #endregion

            #region 处理数据
            // 监测年级
            var examGrades = await _examGradeService.GetListByExamPlanId(examPlanId);

            // 获取证件类型
            var cts = await _sysDictDataService.GetListByDictTypeId(304);
            var certificateTypes = cts.ToDictionary(x => x.Name, y => y.Value);

            // 学科
            var cs = await _courseService.GetAllLiteList();
            var courses = cs.ToDictionary(x => x.Name, y => y.Id);

            // 读取数据
            List<UploadExamTeacherCourseOutput> data = new();
            int rn = 0;
            while (rows.MoveNext())
            {
                IRow row = (IRow)rows.Current;
                string rv = row.GetCell(0)?.ToString().Trim() ?? "";
                rv += row.GetCell(1)?.ToString().Trim() ?? "";
                if (rv == "")
                {
                    break;
                }

                UploadExamTeacherCourseOutput item = new() { RowNumber = ++rn };

                // 年级
                if (short.TryParse(row.GetCell(GRADE_INDEX)?.ToString(), out short gradeNumber))
                {
                    var g = examGrades.FirstOrDefault(t => t.Grade.GradeNumber == gradeNumber);
                    if (g == null)
                    {
                        item.ErrorMessage.Add($"{headers[GRADE_INDEX]}与监测年级不符");
                    }
                    else
                    {
                        item.ExamGradeId = g.Id;
                        item.GradeId = g.GradeId;
                    }
                }
                else
                {
                    item.ErrorMessage.Add(headers[CLASS_INDEX]);
                }

                // 班级
                if (short.TryParse(row.GetCell(CLASS_INDEX)?.ToString(), out short classNumber))
                {
                    item.ClassNumber = classNumber;
                    if (item.ClassNumber < 1 || item.ClassNumber > 35)
                    {
                        item.ErrorMessage.Add($"{headers[CLASS_INDEX]}超限");
                    }
                }
                else
                {
                    item.ErrorMessage.Add(headers[CLASS_INDEX]);
                }

                // 科目
                item.CourseName = StringUtil.ClearWhite(row.GetCell(COURSE_INDEX)?.ToString() ?? "");
                if (item.CourseName == null)
                {
                    item.ErrorMessage.Add($"{headers[COURSE_INDEX]}未填");
                }
                else
                {
                    var cn = item.CourseName;
                    if (cn.Contains("政治") || cn.Contains("道法") || cn.Contains("道德") || cn.Contains("法治"))
                    {
                        cn = "政治";
                    }
                    if (courses.TryGetValue(cn, out short value))
                    {
                        item.CourseId = value;
                    }
                    else
                    {
                        item.ErrorMessage.Add($"{headers[COURSE_INDEX]}错误");
                    }
                }

                // 姓名
                item.Name = StringUtil.ClearWhite(row.GetCell(NAME_INDEX)?.ToString() ?? "");
                if (item.Name == "" || item.Name.Length > 100)
                {
                    item.ErrorMessage.Add(headers[NAME_INDEX]);
                }
                if (item.Name.Length > 100) { item.Name = item.Name[..100]; }

                // 证件类型
                item.CertificateTypeName = StringUtil.ClearWhite(row.GetCell(CERT_TYPE_INDEX)?.ToString() ?? "");
                if (!(item.CertificateTypeName != "" && certificateTypes.ContainsKey(item.CertificateTypeName)))
                {
                    item.ErrorMessage.Add(headers[CERT_TYPE_INDEX]);
                }
                else
                {
                    item.CertificateType = (CertificateType)certificateTypes[item.CertificateTypeName];
                }

                // 证件号码
                item.IdNumber = StringUtil.ClearWhite(row.GetCell(ID_NUM_INDEX)?.ToString() ?? "").ToUpper();
                if (item.CertificateType == CertificateType.ID_CARD)
                {
                    var idNumberValidate = CertificateNumberValidator.ValidateIdCard(item.IdNumber);
                    if (!idNumberValidate.Success)
                    {
                        item.ErrorMessage.Add($"{item.CertificateTypeName}{idNumberValidate.ErrorMessage}");
                    }
                }
                if (item.IdNumber.Length > 100) { item.IdNumber = item.IdNumber[..100]; }

                // 备注
                item.Remark = StringUtil.ClearWhite(row.GetCell(REMARK_INDEX)?.ToString() ?? "");
                if (item.Remark.Length > 200) { item.Remark = item.Remark[..200]; }

                // 行是否验证通过
                item.IsSuccess = item.ErrorMessage.Count == 0;
                data.Add(item);

                result.TotalRowCount++;
                if (!item.IsSuccess)
                {
                    result.ErrorRowCount++;
                }
            }
            result.Rows = data;
            #endregion

            workbook.Close();
            fs.Close();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }

        return result;
    }
    /// <summary>
    /// 批量导入监测教师
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Import(ImportExamTeacherCourseInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;
        // 删除同一监测计划中监测机构内所有数据
        await _rep.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.SysOrgBranchId == input.SysOrgBranchId).ExecuteDeleteAsync();
        List<ExamTeacherCourse> items = new();

        var gs = input.Items.Select(t => t.ExamGradeId).Distinct().ToList();
        var examGrades = await _rep.Change<ExamGrade>().DetachedEntities.Where(t => gs.Contains(t.Id)).Select(t => t.Adapt<ExamGradeOutput>()).ToListAsync();

        foreach (var eg in examGrades)
        {
            var classNumbers = input.Items.Where(t => t.ExamGradeId == eg.Id).Select(t => t.ClassNumber).Distinct().ToList();
            var classDict = await _schoolClassService.GetImportSchoolClassList(new()
            {
                SysOrgId = orgId,
                SysOrgBranchId = input.SysOrgBranchId,
                ExamGrade = eg,
                ClassNumberList = classNumbers,
            });
            var citems = input.Items.Where(t => t.ExamGradeId == eg.Id).ToList();
            foreach (var ni in citems)
            {
                var item = ni.Adapt<ExamTeacherCourse>();
                item.ExamPlanId = input.ExamPlanId;
                item.SysOrgId = orgId;
                item.SysOrgBranchId = input.SysOrgBranchId;
                item.SchoolClassId = classDict[ni.ClassNumber];
                items.Add(item);
            }
        }

        await _rep.InsertAsync(items);
    }
    #endregion

    #region 创建编辑
    /// <summary>
    /// 添加监测教师
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Add(AddExamTeacherCourseInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        // 检测同年级同班同科目是否已经有教师
        var sameItems = await _rep.DetachedEntities
                                  .Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.GradeId == input.GradeId && t.ClassNumber == input.ClassNumber && t.CourseId == input.CourseId)
                                  .ProjectToType<ExamTeacherCourseOutput>().ToListAsync();
        if (sameItems.Any())
        {
            throw Oops.Oh(ErrorCode.E2003);
        }

        var examGrade = await _examGradeService.GetById(input.ExamGradeId);
        var schoolClass = await _schoolClassService.GetSchoolClass(orgId, input.SysOrgBranchId, examGrade, input.ClassNumber);

        var item = input.Adapt<ExamTeacherCourse>();
        item.SysOrgId = orgId;
        item.SchoolClassId = schoolClass.Id;

        await item.InsertAsync();
    }
    /// <summary>
    /// 更新监测教师
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Update(UpdateExamTeacherCourseInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        var oitem = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);

        // 检测同年级同班同科目是否已经有教师
        var sameItems = await _rep.DetachedEntities
                                  .Where(t => t.Id != input.Id && t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.GradeId == input.GradeId && t.ClassNumber == input.ClassNumber && t.CourseId == input.CourseId)
                                  .ProjectToType<ExamTeacherCourseOutput>().ToListAsync();
        if (sameItems.Any())
        {
            throw Oops.Oh(ErrorCode.E2003);
        }

        var examGrade = await _examGradeService.GetById(oitem.ExamGradeId);
        var schoolClass = await _schoolClassService.GetSchoolClass(oitem.SysOrgId, input.SysOrgBranchId, examGrade, input.ClassNumber);

        var item = input.Adapt<ExamTeacherCourse>();
        item.SchoolClassId = schoolClass.Id;

        await item.UpdateIncludeNowAsync(new[] {
            nameof(item.Name),
            nameof(item.CertificateType),
            nameof(item.IdNumber),
            nameof(item.ExamGradeId),
            nameof(item.GradeId),
            nameof(item.SchoolClassId),
            nameof(item.SysOrgBranchId),
            nameof(item.ClassNumber),
            nameof(item.CourseId),
            nameof(item.Remark),
        });

        var items = await _rep.Where(t => t.Id != input.Id && t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.Name == oitem.Name && t.CertificateType == input.CertificateType && t.IdNumber == input.IdNumber).ToListAsync();
        if (items.Any())
        {
            foreach (var uitem in items)
            {
                uitem.Name = input.Name;
                uitem.CertificateType = input.CertificateType;
                uitem.IdNumber = input.IdNumber;

                await uitem.UpdateIncludeNowAsync(new[] { nameof(uitem.Name), nameof(uitem.CertificateType), nameof(uitem.IdNumber) });
            }
        }
    }
    /// <summary>
    /// 删除监测教师
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Del(BaseId input)
    {
        var item = await _rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        await item.DeleteAsync();
    }
    /// <summary>
    /// 清空监测教师
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Clear(ClearExamTeacherCourseInput input)
    {
        var orgId = CurrentSysUserInfo.SysOrgId;
        await _rep.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId).ExecuteDeleteAsync();
    }
    #endregion

    #region 查询统计
    /// <summary>
    /// 分页查询监测教师列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamTeacherCourseOutput>> QueryPageList(ExamTeacherCoursePageInput input)
    {
        var query = GetQueryBase(input);
        query = query.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == CurrentSysUserInfo.SysOrgId);
        var ret = await query.OrderBy(t => t.Id).ProjectToType<ExamTeacherCourseOutput>().ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
    #endregion

    #region 私有方法
    /// <summary>
    /// 构建查询
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    private IQueryable<ExamTeacherCourse> GetQueryBase(ExamTeacherCoursePageInput input)
    {
        var name = !string.IsNullOrEmpty(input.Name?.Trim());
        var idNumber = !string.IsNullOrEmpty(input.IdNumber?.Trim());

        var query = _rep.DetachedEntities.Where((name, u => EF.Functions.Like(u.Name, $"%{input.Name.Trim()}%")))
                                         .Where((idNumber, u => EF.Functions.Like(u.IdNumber, $"%{input.IdNumber.Trim()}%")))
                                         .Where(input.CertificateType.HasValue, t => t.CertificateType == input.CertificateType)
                                         .Where(input.ClassNumber.HasValue, t => t.ClassNumber == input.ClassNumber)
                                         .Where(input.SysOrgBranchId.HasValue, t => t.SysOrgBranchId == input.SysOrgBranchId)
                                         .Where(input.GradeId.HasValue, t => t.GradeId == input.GradeId)
                                         .Where(input.CourseId.HasValue, t => t.CourseId == input.CourseId);
        return query;
    }
    #endregion
}