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;
    private readonly IExportExcelService _exportExcelService;

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

    #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];
                item.Name = StringUtil.ClearWhite(item.Name);
                item.IdNumber = StringUtil.ClearIdNumber(item.IdNumber);
                items.Add(item);
            }
        }

        await _rep.InsertAsync(items);
    }
    #endregion

    #region 批量导出
    /// <summary>
    /// 导出TQES导入文件格式文件包
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task<(string, byte[])> ExportTqesFile(int examPlanId)
    {
        var examPlan = await _rep.Change<ExamPlan>().DetachedEntities.ProjectToType<ExamPlanOutput>().FirstOrDefaultAsync(t => t.Id == examPlanId) ?? throw Oops.Oh(ErrorCode.E2001);

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        string filePath = Path.Combine(fileRoot, $"{examPlan.Name}-教师任教科目");
        Directory.CreateDirectory(filePath);
        try
        {
            // 定义EXCEL列
            List<ExportExcelColDto<ExamTeacherCourseOutput>> noCols = new()
            {
                new() { Name = "年级号", Width = 8, GetCellValue = (r) => r.ExamGrade.Grade.GradeNumber2 },
                new() { Name = "班级号", Width = 8, GetCellValue = (r) => r.ClassNumber },
                new() { Name = "科目", Width = 8, GetCellValue = (r) => r.Course.Name },
                new() { Name = "身份证号码", Width = 20, GetCellValue = (r) => r.IdNumber },
                new() { Name = "姓名", Width = 20, GetCellValue = (r) => r.Name },
            };

            var items = await _rep.DetachedEntities.Where(t => t.ExamPlanId == examPlanId).ProjectToType<ExamTeacherCourseOutput>().ToListAsync();
            var orgs = items.Select(t => new { t.SysOrg.Id, t.SysOrg.Name, t.SysOrg.TqesCode }).Distinct().ToList();
            foreach (var org in orgs)
            {
                var orgData = items.Where(t => t.SysOrgId == org.Id).ToList();
                var bs = _exportExcelService.ExportExcel(new ExportExcelDto<ExamTeacherCourseOutput>()
                {
                    IsXlsx = false,
                    Title = null,
                    Columns = noCols,
                    Items = orgData,
                    IncludeExportTime = false,
                });
                await File.WriteAllBytesAsync(Path.Combine(filePath, $"T-{org.TqesCode}-{org.Name}-教师任教科目.xls"), bs);
            }

            string outFileName = $"{examPlan.Name}-教师任教科目.zip";
            string outFilePath = Path.Combine(fileRoot, outFileName);
            ICSharpCode.SharpZipLib.Zip.FastZip zip = new();
            zip.CreateZip(outFilePath, filePath, true, string.Empty);

            var retBytes = await File.ReadAllBytesAsync(outFilePath);
            return (outFileName, retBytes);
        }
        catch (Exception ex)
        {
            throw new Exception("导出错误", ex);
        }
        finally
        {
            Directory.Delete(fileRoot, true);
        }
    }
    #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;
        item.Name = StringUtil.ClearWhite(item.Name);
        item.IdNumber = StringUtil.ClearIdNumber(item.IdNumber);

        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;
        item.Name = StringUtil.ClearWhite(item.Name);
        item.IdNumber = StringUtil.ClearIdNumber(item.IdNumber);

        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
}