YBEE.EQM/YBEE.EQM.Application/Exam/ExamTeacher/Services/ExamTeacherService.cs

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

namespace YBEE.EQM.Application;

/// <summary>
/// 监测教师管理服务
/// </summary>
public class ExamTeacherService : IExamTeacherService, ITransient
{
    private readonly IRepository<ExamTeacher> _rep;
    private readonly ISysDictDataService _sysDictDataService;

    public ExamTeacherService(IRepository<ExamTeacher> rep, ISysDictDataService sysDictDataService)
    {
        _rep = rep;
        _sysDictDataService = sysDictDataService;
    }

    #region 批量导入
    /// <summary>
    /// 上传监测教师批量导入文件
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<UploadExamDataOutput<UploadExamTeacherOutput>> Upload(string filePath, int examPlanId)
    {
        UploadExamDataOutput<UploadExamTeacherOutput> 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 NAME_INDEX = index++;
            int CERT_TYPE_INDEX = index++;
            int ID_NUM_INDEX = index++;
            int GENDER_INDEX = index++;
            int JOB_INDEX = index++;
            int MOBILE_INDEX = index++;
            int EMAIL_INDEX = index++;
            int REMARK_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { NAME_INDEX, "姓名" },
                { CERT_TYPE_INDEX, "证件类型" },
                { ID_NUM_INDEX, "证件号码" },
                { GENDER_INDEX, "性别" },
                { JOB_INDEX, "校内职务" },
                { MOBILE_INDEX, "手机号码" },
                { EMAIL_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 cts = await _sysDictDataService.GetListByDictTypeId(304);
            var certificateTypes = cts.ToDictionary(x => x.Name, y => y.Value);
            // 获取选科组全
            var sjs = await _sysDictDataService.GetListByDictTypeId(306);
            var jobs = sjs.ToDictionary(x => x.Name, y => y.Value);

            // 读取数据
            List<UploadExamTeacherOutput> 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;
                }

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

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

                // 证件类型
                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}");
                    }
                    else
                    {
                        item.GenderName = idNumberValidate.Gender == Gender.MALE ? "男" : "女";
                        item.Gender = idNumberValidate.Gender;
                    }
                }
                else
                {
                    // 性别
                    item.GenderName = StringUtil.ClearWhite(row.GetCell(GENDER_INDEX)?.ToString() ?? "");
                    if (item.CertificateType != CertificateType.ID_CARD)
                    {
                        item.Gender = item.GenderName == "男" ? Gender.MALE : item.GenderName == "女" ? Gender.FEMALE : Gender.UNKNOWN;
                    }
                }

                // 校内职务
                item.SchoolJobTitleName = StringUtil.ClearWhite(row.GetCell(JOB_INDEX)?.ToString() ?? "");
                if (item.SchoolJobTitleName == "")
                {
                    item.SchoolJobTitle = SchoolJobTitle.TEACHER;
                }
                else if (jobs.ContainsKey(item.SchoolJobTitleName))
                {
                    item.SchoolJobTitle = (SchoolJobTitle)jobs[item.SchoolJobTitleName];
                }
                else
                {
                    item.ErrorMessage.Add(headers[JOB_INDEX]);
                }

                // 手机号码
                item.Mobile = StringUtil.ClearWhite(row.GetCell(MOBILE_INDEX)?.ToString() ?? "");
                // 电子邮箱
                item.Email = StringUtil.ClearWhite(row.GetCell(EMAIL_INDEX)?.ToString() ?? "");

                // 备注
                item.Remark = StringUtil.ClearWhite(row.GetCell(REMARK_INDEX)?.ToString() ?? "");
                // 行是否验证通过
                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(ImportExamTeacherInput input)
    {
        // 删除同一监测计划中监测机构内所有数据
        await _rep.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == input.SysOrgId).ExecuteDeleteAsync();

        foreach (var item in input.Items)
        {
            item.SysOrgId ??= CurrentSysUserInfo.SysOrgId;
        }
        var items = input.Items.Select(t => t.Adapt<ExamTeacher>()).ToList();
        await _rep.InsertAsync(items);
    }
    #endregion

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

        // 检测同一监测计划中同机构内是否有相同证件号码的教师
        var sameItems = await _rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.CertificateType == input.CertificateType && t.IdNumber == input.IdNumber).ProjectToType<ExamTeacherOutput>().ToListAsync();
        if (sameItems.Any())
        {
            throw Oops.Oh(ErrorCode.E2003, "", "证件号码");
        }

        var item = input.Adapt<ExamTeacher>();
        if (!input.SysOrgId.HasValue)
        {
            item.SysOrgId = orgId;
        }
        await item.InsertAsync();
    }
    /// <summary>
    /// 更新监测教师
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Update(UpdateExamTeacherInput input)
    {
        var oitem = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);

        var item = input.Adapt<ExamTeacher>();
        await item.UpdateIncludeAsync(new[] {
            nameof(item.Name),
            nameof(item.CertificateType),
            nameof(item.SchoolJobTitle),
            nameof(item.IdNumber),
            nameof(item.Gender),
            nameof(item.BirthDate),
            nameof(item.Mobile),
            nameof(item.Email),
            nameof(item.Remark),
        });
    }
    /// <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(ClearExamTeacherInput 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<ExamTeacherOutput>> QueryPageList(ExamTeacherPageInput 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<ExamTeacherOutput>().ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
    #endregion

    #region 私有方法
    /// <summary>
    /// 构建查询
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    private IQueryable<ExamTeacher> GetQueryBase(ExamTeacherPageInput input)
    {
        var name = !string.IsNullOrEmpty(input.Name?.Trim());
        var idNumber = !string.IsNullOrEmpty(input.IdNumber?.Trim());
        var mobile = !string.IsNullOrEmpty(input.Mobile?.Trim());
        var email = !string.IsNullOrEmpty(input.Email?.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((mobile, u => EF.Functions.Like(u.Mobile, $"%{input.Mobile.Trim()}%")))
                                         .Where((email, u => EF.Functions.Like(u.Email, $"%{input.Email.Trim()}%")))
                                         .Where(input.CertificateType.HasValue, t => t.CertificateType == input.CertificateType)
                                         .Where(input.SchoolJobTitle.HasValue, t => t.SchoolJobTitle == input.SchoolJobTitle)
                                         .Where(input.Gender.HasValue, t => t.Gender == input.Gender);
        return query;
    }
    #endregion
}