YBEE.EQM/YBEE.EQM.Application/Exam/ExamSampleReplace/Services/ExamSampleReplaceService.cs

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

namespace YBEE.EQM.Application;

/// <summary>
/// 缺测替补抽样服务
/// </summary>
public class ExamSampleReplaceService(IRepository<ExamSampleReplace> replaceRep, IRepository<ExamSampleStudent> stuRep, IExportExcelService exportExcelService) : IExamSampleReplaceService, ITransient
{
    /// <summary>
    /// 抽取
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Sample(SampleExamSampleReplaceInput input)
    {
        var orgId = CurrentSysUserInfo.SysOrgId;

        // 缺测学生
        var absentStu = await stuRep.DetachedEntities
                                    .Include(t => t.ExamStudent).Include(t => t.ExamSample)
                                    .FirstOrDefaultAsync(t => t.Id == input.AbsentExamSampleStudentId && t.ExamStudent.SysOrgId == orgId)
                                    ?? throw Oops.Oh(ErrorCode.E2001);
        // 替补列表
        var rps = await replaceRep.DetachedEntities
                                  .Where(t => t.ExamSampleId == absentStu.ExamSampleId && t.SysOrgId == orgId && t.IsDeleted == false)
                                  .Select(t => new { t.AbsentExamSampleStudentId, t.ReplaceExamSampleStudentId, t.IsReplaceAbsent })
                                  .ToListAsync();

        // 如果已添加缺测，并且替补未被标记为缺测的情况则报错
        if (rps.Any(t => t.AbsentExamSampleStudentId == absentStu.Id && t.IsReplaceAbsent == false))
        {
            throw Oops.Oh(ErrorCode.E3010);
        }

        // 所有学生
        var stus = await stuRep.DetachedEntities.Where(t => t.ExamSampleId == absentStu.ExamSampleId &&
                                                            t.ExamStudent.SysOrgId == orgId &&
                                                            t.ExamStudent.GradeId == absentStu.ExamStudent.GradeId &&
                                                            t.ExamStudent.SchoolClassId == absentStu.ExamStudent.SchoolClassId &&
                                                            t.IsSpecialStudent == false)
                                                .OrderByDescending(t => t.PreTotalScore).ThenBy(t => t.ExamStudentId)
                                                .ToListAsync();
        if (stus.Count == 0)
        {
            throw Oops.Oh(ErrorCode.E3009);
        }

        // 去掉已抽为替补的
        var ers = rps.Select(t => t.ReplaceExamSampleStudentId).ToList();
        stus = stus.Where(t => !ers.Contains(t.Id)).ToList();

        ExamSampleStudent rstu = null;
        // 有前置成绩按顺序抽取，无随机抽取
        if (stus.Any(t => t.PreTotalScore > 0))
        {
            // 向上找紧挨的1位
            rstu = stus.Where(t => t.PreTotalScore >= absentStu.PreTotalScore && t.Id != absentStu.Id && t.ExamSampleType == ExamSampleType.SCHOOL_EXAM).LastOrDefault();
            // 向上未找到则向下找紧挨的1位
            rstu ??= stus.Where(t => t.PreTotalScore <= absentStu.PreTotalScore && t.Id != absentStu.Id && t.ExamSampleType == ExamSampleType.SCHOOL_EXAM).FirstOrDefault();
        }
        else
        {
            var nstus = stus.Where(t => t.Id != absentStu.Id && t.ExamSampleType == ExamSampleType.SCHOOL_EXAM).ToList();
            if (nstus.Count > 0)
            {
                var rand = new Random();
                var si = rand.Next(0, nstus.Count);
                rstu = nstus[si];
            }
        }
        if (rstu is null)
        {
            throw Oops.Oh(ErrorCode.E3009);
        }

        // 同一缺测生抽的其他替补缺测锁定
        var rps2 = await replaceRep.Where(t => t.ExamSampleId == absentStu.ExamSampleId &&
                                               t.SysOrgId == CurrentSysUserInfo.SysOrgId &&
                                               t.AbsentExamSampleStudentId == absentStu.Id &&
                                               t.IsDeleted == false &&
                                               t.IsReplaceAbsent == true
                                   ).ToListAsync();
        foreach (var rp in rps2)
        {
            rp.IsReplaceAbsentLocked = true;
            await rp.UpdateIncludeAsync([nameof(rp.IsReplaceAbsentLocked)]);
        }

        ExamSampleReplace item = new()
        {
            ExamSampleId = absentStu.ExamSampleId,
            ExamPlanId = absentStu.ExamSample.ExamPlanId,
            SysOrgId = absentStu.ExamStudent.SysOrgId,
            SysOrgBranchId = absentStu.ExamStudent.SysOrgBranchId,
            SchoolClassId = absentStu.ExamStudent.SchoolClassId,
            ExamGradeId = absentStu.ExamStudent.ExamGradeId,
            GradeId = absentStu.ExamStudent.GradeId,
            ClassNumber = absentStu.ExamStudent.ClassNumber,
            AbsentExamSampleStudentId = absentStu.Id,
            ReplaceExamSampleStudentId = rstu.Id,
            Remark = input.Remark,
            IsReplaceAbsent = false,
            IsReplaceAbsentLocked = false,
        };
        await replaceRep.InsertAsync(item);
    }
    /// <summary>
    /// 标记替补为缺测
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task MarkedReplaceAbsent(int id)
    {
        var item = await replaceRep.FirstOrDefaultAsync(t => t.Id == id && t.IsReplaceAbsentLocked == false) ?? throw Oops.Oh(ErrorCode.E2001);
        item.IsReplaceAbsent = !item.IsReplaceAbsent;
        await item.UpdateIncludeAsync([nameof(item.IsReplaceAbsent)]);

        // 同一缺测生抽的其他替补缺测解除锁定
        var rps2 = await replaceRep.Where(t => t.ExamSampleId == item.ExamSampleId &&
                                               t.SysOrgId == CurrentSysUserInfo.SysOrgId &&
                                               t.AbsentExamSampleStudentId == item.AbsentExamSampleStudentId &&
                                               t.Id != item.Id &&
                                               t.IsDeleted == false &&
                                               t.IsReplaceAbsent == true
                                   ).ToListAsync();
        foreach (var rp in rps2)
        {
            rp.IsReplaceAbsentLocked = !item.IsReplaceAbsent;
            await rp.UpdateIncludeAsync([nameof(rp.IsReplaceAbsentLocked)]);
        }
    }
    /// <summary>
    /// 软删除
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task FakeDelete(BaseId input)
    {
        var item = await replaceRep.FirstOrDefaultAsync(t => t.Id == input.Id && t.IsDeleted == false) ?? throw Oops.Oh(ErrorCode.E2001);
        item.IsDeleted = true;
        await item.UpdateIncludeNowAsync([nameof(item.IsDeleted)]);
    }
    /// <summary>
    /// 导出缺测替补名单
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<(string fileName, byte[] fileBytes)> ExportToOrg(int examPlanId)
    {
        var res = await QueryOrgPageList(new()
        {
            ExamPlanId = examPlanId,
            IsReplaceAbsent = false,
            PageIndex = 1,
            PageSize = 9999
        });

        var hasBranch = res.Items.Any(t => t.SysOrgBranchId.HasValue && t.SysOrgBranchId > 0);

        XSSFWorkbook wb = new();
        ISheet sheet = wb.CreateSheet();
        sheet.DisplayGridlines = false;

        // 获取样式
        var cellStyle = exportExcelService.GetCellStyle(wb);

        #region 表头
        int rowNum = 0;

        IRow headerRow1 = sheet.CreateRow(rowNum);
        headerRow1.Height = ExportExcelCellStyle.DefaultRowHeight;
        int ci = 0;
        exportExcelService.AddCell("序", headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 6);
        if (hasBranch)
        {
            exportExcelService.AddCell("校区", headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        }
        exportExcelService.AddCell("年级", headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 10);
        exportExcelService.AddCell("班级", headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 8);

        exportExcelService.AddCell("缺测学生", headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        //exportExcelService.AddCell(null, headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 20);
        exportExcelService.AddCell(null, headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 14);
        sheet.AddMergedRegion(new CellRangeAddress(rowNum, rowNum, ci - 2, ci - 1));

        exportExcelService.AddCell("替补学生", headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        //exportExcelService.AddCell(null, headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 20);
        exportExcelService.AddCell(null, headerRow1, ci++, cellStyle.ColumnHeaderStyle, sheet, 14);
        sheet.AddMergedRegion(new CellRangeAddress(rowNum, rowNum, ci - 2, ci - 1));

        exportExcelService.AddCell("抽取时间", headerRow1, ci, cellStyle.ColumnHeaderStyle, sheet, 20);

        IRow headerRow2 = sheet.CreateRow(++rowNum);
        headerRow2.Height = ExportExcelCellStyle.DefaultRowHeight;
        int lci = hasBranch ? 4 : 3;
        for (ci = 0; ci < lci; ci++)
        {
            exportExcelService.AddCell(null, headerRow2, ci, cellStyle.ColumnHeaderStyle);
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, ci, ci));
        }
        exportExcelService.AddCell("姓名", headerRow2, ci++, cellStyle.ColumnHeaderStyle);
        //exportExcelService.AddCell("证件号码", headerRow2, ci++, cellStyle.ColumnHeaderStyle);
        exportExcelService.AddCell("监测号", headerRow2, ci++, cellStyle.ColumnHeaderStyle);

        exportExcelService.AddCell("姓名", headerRow2, ci++, cellStyle.ColumnHeaderStyle);
        //exportExcelService.AddCell("证件号码", headerRow2, ci++, cellStyle.ColumnHeaderStyle);
        exportExcelService.AddCell("监测号", headerRow2, ci++, cellStyle.ColumnHeaderStyle);

        exportExcelService.AddCell(null, headerRow2, ci, cellStyle.ColumnHeaderStyle);
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, ci, ci));

        sheet.CreateFreezePane(0, 2);
        #endregion

        int rn = 0;
        foreach (var item in res.Items)
        {
            IRow row = sheet.CreateRow(++rowNum);
            row.Height = ExportExcelCellStyle.DefaultRowHeight;

            int rci = 0;
            ICellStyle cstyle = cellStyle.CenterCellStyle;

            exportExcelService.AddCell(++rn, row, rci++, cstyle);
            if (hasBranch)
            {
                exportExcelService.AddCell(item.SysOrgBranch?.Name ?? "", row, rci++, cstyle);
            }
            exportExcelService.AddCell(item.ExamGrade.Grade.Name, row, rci++, cstyle);
            exportExcelService.AddCell(item.ClassNumber, row, rci++, cstyle);

            exportExcelService.AddCell(item.AbsentExamSampleStudent.ExamStudent.Name, row, rci++, cstyle);
            //exportExcelService.AddCell(item.AbsentExamSampleStudent.ExamStudent.IdNumber, row, rci++, cstyle);
            exportExcelService.AddCell(item.AbsentExamSampleStudent.ExamNumber, row, rci++, cstyle);

            exportExcelService.AddCell(item.ReplaceExamSampleStudent.ExamStudent.Name, row, rci++, cstyle);
            //exportExcelService.AddCell(item.ReplaceExamSampleStudent.ExamStudent.IdNumber, row, rci++, cstyle);
            exportExcelService.AddCell(item.ReplaceExamSampleStudent.ExamNumber, row, rci++, cstyle);

            exportExcelService.AddCell(item.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"), row, rci++, cstyle);
        }

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ($"监测替补抽取表{DateTime.Now:yyyyMMddHHmmss}.xlsx", ms.ToArray());
    }
    /// <summary>
    /// 分页查询缺测替补抽样列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamSampleReplaceOutput>> QueryOrgPageList(ExamSampleReplacePageInput input)
    {
        var name = !string.IsNullOrEmpty(input.Name?.Trim());
        var idNumber = !string.IsNullOrEmpty(input.IdNumber?.Trim());
        var examNumber = !string.IsNullOrEmpty(input.ExamNumber?.Trim());

        var ret = await replaceRep.DetachedEntities
                                  .Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == CurrentSysUserInfo.SysOrgId && t.IsDeleted == false)
                                  .Where((name, u => EF.Functions.Like(u.AbsentExamSampleStudent.ExamStudent.Name, $"%{input.Name.Trim()}%") || EF.Functions.Like(u.ReplaceExamSampleStudent.ExamStudent.Name, $"%{input.Name.Trim()}%")))
                                  .Where((idNumber, u => EF.Functions.Like(u.AbsentExamSampleStudent.ExamStudent.IdNumber, $"%{input.IdNumber.Trim()}%") || EF.Functions.Like(u.ReplaceExamSampleStudent.ExamStudent.IdNumber, $"%{input.IdNumber.Trim()}%")))
                                  .Where((examNumber, u => EF.Functions.Like(u.AbsentExamSampleStudent.ExamNumber, $"%{input.ExamNumber.Trim()}%") || EF.Functions.Like(u.ReplaceExamSampleStudent.ExamNumber, $"%{input.ExamNumber.Trim()}%")))
                                  .Where(input.GradeId.HasValue, t => t.GradeId == input.GradeId)
                                  .Where(input.ClassNumber.HasValue, t => t.ClassNumber == input.ClassNumber)
                                  .Where(input.SysOrgBranchId.HasValue, t => t.SysOrgBranchId == input.SysOrgBranchId)
                                  .Where(input.IsReplaceAbsent.HasValue, t => t.IsReplaceAbsent == input.IsReplaceAbsent)
                                  .Where(input.IsReplaceAbsentLocked.HasValue, t => t.IsReplaceAbsentLocked == input.IsReplaceAbsentLocked)
                                  .ProjectToType<ExamSampleReplaceOutput>()
                                  .OrderBy(t => t.SysOrgBranchId).ThenBy(t => t.GradeId).ThenBy(t => t.ClassNumber).ThenByDescending(t => t.CreateTime)
                                  .ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
}