YBEE.EQM/YBEE.EQM.Application/Exam/ExamPaper/Services/ExamPaperService.cs

using Furion.DatabaseAccessor.Extensions;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 试卷管理服务
/// </summary>
public class ExamPaperService : IExamPaperService, ITransient
{
    private readonly IRepository<ExamPaper> _repExamPaper;
    private readonly ISysRoleService _sysRoleService;
    private readonly IExportExcelService _exportExcelService;

    public ExamPaperService(IRepository<ExamPaper> repExamPaper, ISysRoleService sysRoleService, IExportExcelService exportExcelService)
    {
        _repExamPaper = repExamPaper;
        _sysRoleService = sysRoleService;
        _exportExcelService = exportExcelService;
    }

    /// <summary>
    /// 按监测计划初始化试卷
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task BatchInit(ExamPaperBatchInitInput input)
    {
        var examCourses = await _repExamPaper.Change<ExamCourse>().DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId).ProjectToType<ExamCourseOutput>().ToListAsync();
        if (!examCourses.Any())
        {
        }

        var eitems = await _repExamPaper.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId).ToListAsync();
        var ecs = from t in examCourses
                  where !(from p in eitems select new { p.GradeId, p.CourseId }).Contains(new { t.GradeId, t.CourseId })
                  select t;

        if (ecs.Any())
        {
            foreach (var ec in ecs)
            {
                var p = new ExamPaper
                {
                    ExamPlanId = input.ExamPlanId,
                    ExamGradeId = ec.ExamGradeId,
                    GradeId = ec.GradeId,
                    ExamCourseId = ec.Id,
                    CourseId = ec.CourseId,
                    Score = ec.TotalScore,
                    Name = $"{ec.ExamGrade.Grade.FullName}{ec.Course.Name}",
                };
                await _repExamPaper.InsertNowAsync(p);

                if (ec.ScoreReportConfig.HeaderColumnNames.Count > ec.ScoreReportConfig.MinorQuestionColumnIndex + 1)
                {
                    List<ExamPaperQuestionMinor> mitems = new();
                    for (int i = ec.ScoreReportConfig.MinorQuestionColumnIndex; i < ec.ScoreReportConfig.HeaderColumnNames.Count; i++)
                    {
                        var columnName = ec.ScoreReportConfig.HeaderColumnNames[i];
                        var pmi = new ExamPaperQuestionMinor()
                        {
                            ExamPaperId = p.Id,
                            Sequence = i - ec.ScoreReportConfig.MinorQuestionColumnIndex + 1,
                            ColumnName = columnName,
                            Name = columnName.Replace("第", "").Replace("题", "").Trim(),
                        };
                        mitems.Add(pmi);
                    }
                    await _repExamPaper.Change<ExamPaperQuestionMinor>().InsertNowAsync(mitems);
                }
            }
        }
    }
    /// <summary>
    /// 分配双向细目表编制人
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task AssignTwclWriter(AssignExamPaperWriterInput input)
    {
        var items = await _repExamPaper.Where(t => input.Ids.Contains(t.Id)).ToListAsync();
        foreach (var item in items)
        {
            item.TwclSysUserId = input.WriterSysUserId;
            await item.UpdateIncludeNowAsync(new[] { nameof(item.TwclSysUserId) });
        }
    }
    /// <summary>
    /// 分配问题建议撰写人
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task AssignSuggestionWriter(AssignExamPaperWriterInput input)
    {
        var items = await _repExamPaper.Where(t => input.Ids.Contains(t.Id)).ToListAsync();
        foreach (var item in items)
        {
            item.SuggestionSysUserId = input.WriterSysUserId;
            await item.UpdateIncludeNowAsync(new[] { nameof(item.SuggestionSysUserId) });
        }
    }
    /// <summary>
    /// 根据ID获取试卷详情
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<ExamPaperOutput> GetById(int id)
    {
        var item = await _repExamPaper.DetachedEntities.ProjectToType<ExamPaperOutput>().FirstOrDefaultAsync(t => t.Id == id);
        return item;
    }
    /// <summary>
    /// 根据监测计划ID获取试卷列表（管理端）
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<List<ExamPaperLiteOutput>> GetListByExamPlanId(int examPlanId)
    {
        var items = await _repExamPaper.DetachedEntities
                                       .Where(t => t.ExamPlanId == examPlanId)
                                       .OrderBy(t => t.GradeId).ThenBy(t => t.CourseId)
                                       .ProjectToType<ExamPaperLiteOutput>().ToListAsync();
        SetSubmitStatus(items);
        return items;
    }
    /// <summary>
    /// 获取双向细目表监测计划列表（管理端）
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamPaperTodoPlanOutput>> QueryExamPlanPageList(ExamPlanPageInput input)
    {
        List<string> whereCause = new();
        if (!string.IsNullOrEmpty(input.Name?.Trim()))
        {
            whereCause.Add("T3.full_name LIKE '%@name%'");
        }
        if (input.SemesterId.HasValue)
        {
            whereCause.Add("T3.semester_id = @semesterId");
        }
        if (input.Status.HasValue)
        {
            whereCause.Add("T3.status = @status");
        }

        string whereSql = $"WHERE (T3.`status` = {(short)ExamStatus.ACTIVE} OR T3.`status` = {(short)ExamStatus.STOPPED}) AND T3.is_deleted = 0";
        if (whereCause.Count > 0)
        {
            whereSql = $"{whereSql} AND {string.Join(" AND ", whereCause)}";
        }

        var p = new
        {
            input.PageSize,
            PageOffset = (input.PageIndex - 1) * input.PageSize,
            input.SemesterId,
            input.Status,
            input.Name,
        };

        var totalCount = await _repExamPaper.SqlScalarAsync<int>($@"
SELECT COUNT(1) AS total_count
FROM
(
    SELECT T1.exam_plan_id, COUNT(T1.id) AS total_count
    FROM exam_paper AS T1
    JOIN exam_plan AS T3 ON T1.exam_plan_id = T3.id
    {whereSql}
    GROUP BY T1.exam_plan_id
) AS T
", p);


        string select = $@"
SELECT 
    T1.exam_plan_id,
    T3.full_name AS exam_plan_full_name,
    T3.`name` AS exam_plan_name,
    T3.semester_id,
    T3.`status` AS exam_plan_status,
    COUNT(T1.id) AS total_count,
    COUNT(CASE WHEN T1.`twcl_status` = {(short)AuditStatus.UNSUBMIT} OR T1.`twcl_status` = {(short)AuditStatus.REJECTED} THEN T1.id ELSE NULL END) AS twcl_unsubmit_count,
    COUNT(CASE WHEN T1.`twcl_status` = {(short)AuditStatus.AUDIT} OR T1.`twcl_status` = {(short)AuditStatus.APPROVED} OR T1.`twcl_status` = {(short)AuditStatus.APPROVE_CANCELED} THEN T1.id ELSE NULL END) AS twcl_submitted_count,
    COUNT(CASE WHEN T1.`suggestion_status` = {(short)AuditStatus.UNSUBMIT} OR T1.`suggestion_status` = {(short)AuditStatus.REJECTED} THEN T1.id ELSE NULL END) AS suggestion_unsubmit_count,
    COUNT(CASE WHEN T1.`suggestion_status` = {(short)AuditStatus.AUDIT} OR T1.`suggestion_status` = {(short)AuditStatus.APPROVED} OR T1.`suggestion_status` = {(short)AuditStatus.APPROVE_CANCELED} THEN T1.id ELSE NULL END) AS suggestion_submitted_count
FROM exam_paper AS T1
JOIN exam_plan AS T3 ON T1.exam_plan_id = T3.id
{whereSql}
GROUP BY T1.exam_plan_id, T3.full_name, T3.`name`, T3.semester_id, T3.`status`
ORDER BY T3.semester_id DESC, T1.exam_plan_id DESC
LIMIT @pageSize OFFSET @pageOffset
";
        var items = await _repExamPaper.SqlQueriesAsync<ExamPaperTodoPlanOutput>(select, p);

        PageResult<ExamPaperTodoPlanOutput> ret = new()
        {
            PageIndex = input.PageIndex,
            PageSize = input.PageSize,
            TotalCount = totalCount,
            Items = items
        };
        return ret;
    }

    /// <summary>
    /// 导出TQES导入文件格式文件包
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task<(string, byte[])> ExportTqesFile(int examPlanId)
    {
        var examPlan = await _repExamPaper.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<ExamPaperQuestionMinorOutput>> cols = new()
            {
                new() { Name = "大题号", Width = 10, GetCellValue = (r) => r.ExamPaperQuestionMajor?.Number ?? "" },
                new() { Name = "小题号", Width = 18, GetCellValue = (r) => r.ColumnName, Align = ExportExcelCellAlign.LEFT },
                new() { Name = "分值", Width = 8, GetCellValue = (r) => r.Score },
                new() { Name = "题型", Width = 10, GetCellValue = (r) => r.QuestionCatalog?.GetDescription() ?? "" },
                new() { Name = "知识模块", Width = 30, GetCellValue = (r) => r.KnowledgeModule, Align = ExportExcelCellAlign.LEFT },
                new() { Name = "知识点", Width = 60, GetCellValue = (r) => r.KnowledgePoint, Align = ExportExcelCellAlign.LEFT },
                new() { Name = "认知能力", Width = 10, GetCellValue = (r) => r.CognitiveAbility?.GetDescription() ?? "" },
                new() { Name = "预估难度", Width = 10, GetCellValue = (r) => r.EstimatedDifficulty },
                new() { Name = "是否必答", Width = 10, GetCellValue = (r) => r.IsChoose ? "否" : "是" },
                new() { Name = "备注", Width = 20, GetCellValue = (r) => null, Align = ExportExcelCellAlign.LEFT },
            };

            var ps = await _repExamPaper.DetachedEntities.Where(t => t.ExamPlanId == examPlanId).ProjectToType<ExamPaperLiteOutput>().ToListAsync();
            foreach (var p in ps)
            {
                var items = await _repExamPaper.Change<ExamPaperQuestionMinor>().DetachedEntities.Where(t => t.ExamPaperId == p.Id && t.IsLeaf == true).ProjectToType<ExamPaperQuestionMinorOutput>().ToListAsync();
                var bs = _exportExcelService.ExportExcel(new ExportExcelDto<ExamPaperQuestionMinorOutput>()
                {
                    IsXlsx = false,
                    Title = null,
                    Columns = cols,
                    Items = items,
                    IncludeExportTime = false,
                });
                await File.WriteAllBytesAsync(Path.Combine(filePath, $"{examPlan.EducationStage.GetDescription()}-{p.Grade.Name}-{p.Course.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);
        }
    }

    #region 编撰者
    /// <summary>
    /// 分页查询编撰人监测计划列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamPaperTodoPlanOutput>> QueryWriterExamPlanPageList(ExamPaperExamPlanPageInput input)
    {
        List<string> whereCause = new();
        if (!string.IsNullOrEmpty(input.Name?.Trim()))
        {
            whereCause.Add("T3.full_name LIKE '%@name%'");
        }
        if (input.SemesterId.HasValue)
        {
            whereCause.Add("T3.semester_id = @semesterId");
        }
        if (input.Status.HasValue)
        {
            whereCause.Add("T3.status = @status");
        }

        string prefix = input.WriterType == ExamPaperWriterType.TWCL ? "twcl" : "suggestion";

        string whereSql = $"WHERE T1.{prefix}_sys_user_id = {CurrentSysUserInfo.SysUserId} AND (T3.`status` = {(short)ExamStatus.ACTIVE} OR T3.`status` = {(short)ExamStatus.STOPPED}) AND T3.is_deleted = 0";
        if (whereCause.Count > 0)
        {
            whereSql = $"{whereSql} AND {string.Join(" AND ", whereCause)}";
        }

        var p = new
        {
            input.PageSize,
            PageOffset = (input.PageIndex - 1) * input.PageSize,
            input.SemesterId,
            input.Status,
            input.Name,
        };

        var totalCount = await _repExamPaper.SqlScalarAsync<int>($@"
SELECT COUNT(1) AS total_count
FROM
(
    SELECT T1.exam_plan_id, COUNT(T1.id) AS total_count
    FROM exam_paper AS T1
    JOIN exam_plan AS T3 ON T1.exam_plan_id = T3.id
    {whereSql}
    GROUP BY T1.exam_plan_id
) AS T
", p);


        string select = $@"
SELECT 
    T1.exam_plan_id,
    T3.full_name AS exam_plan_full_name,
    T3.`name` AS exam_plan_name,
    T3.semester_id,
    T3.`status` AS exam_plan_status,
    COUNT(T1.id) AS total_count,
    COUNT(CASE WHEN T1.`{prefix}_status` = {(short)AuditStatus.UNSUBMIT} OR T1.`{prefix}_status` = {(short)AuditStatus.REJECTED} THEN T1.id ELSE NULL END) AS {prefix}_unsubmit_count,
    COUNT(CASE WHEN T1.`{prefix}_status` = {(short)AuditStatus.AUDIT} OR T1.`{prefix}_status` = {(short)AuditStatus.APPROVED} OR T1.`{prefix}_status` = {(short)AuditStatus.APPROVE_CANCELED} THEN T1.id ELSE NULL END) AS {prefix}_submitted_count
FROM exam_paper AS T1
JOIN exam_plan AS T3 ON T1.exam_plan_id = T3.id
{whereSql}
GROUP BY T1.exam_plan_id, T3.full_name, T3.`name`, T3.semester_id, T3.`status`
ORDER BY T3.semester_id DESC, T1.exam_plan_id DESC
LIMIT @pageSize OFFSET @pageOffset
";
        var items = await _repExamPaper.SqlQueriesAsync<ExamPaperTodoPlanOutput>(select, p);

        PageResult<ExamPaperTodoPlanOutput> ret = new()
        {
            PageIndex = input.PageIndex,
            PageSize = input.PageSize,
            TotalCount = totalCount,
            Items = items
        };
        return ret;
    }
    /// <summary>
    /// 根据监测计划ID获取待处理试卷列表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <param name="writerType"></param>
    /// <returns></returns>
    public async Task<List<ExamPaperLiteOutput>> GetWriterListByExamPlanId(int examPlanId, ExamPaperWriterType writerType)
    {
        var query = _repExamPaper.DetachedEntities.Where(t => t.ExamPlanId == examPlanId);
        if (writerType == ExamPaperWriterType.TWCL)
        {
            query = query.Where(t => t.TwclSysUserId == CurrentSysUserInfo.SysUserId);
        }
        else
        {
            query = query.Where(t => t.SuggestionSysUserId == CurrentSysUserInfo.SysUserId);
        }
        var items = await query.ProjectToType<ExamPaperLiteOutput>().ToListAsync();
        SetSubmitStatus(items);
        return items;
    }
    /// <summary>
    /// 保存问题建议
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task SaveSuggestion(SaveExamPaperSuggestion input)
    {
        var item = await _repExamPaper.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001, "监测科目");
        item.Questions = input.Questions;
        item.Suggestions = input.Suggestions;
        await item.UpdateIncludeAsync(new[] { nameof(item.Questions), nameof(item.Suggestions) });
    }
    /// <summary>
    /// 提交双向细目表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task SubmitTwcl(BaseId input)
    {
        var item = await _repExamPaper.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001, "试卷");
        var nc = await _repExamPaper.Change<ExamPaperQuestionMinor>().CountAsync(t => t.ExamPaperId == input.Id && (!t.QuestionCatalog.HasValue || !t.CognitiveAbility.HasValue || t.EstimatedDifficulty <= 0 || t.KnowledgeModule.Trim() == "" || t.KnowledgePoint.Trim() == ""));
        if (nc > 0)
        {
            throw Oops.Oh(ErrorCode.E3002, nc);
        }
        item.TwclStatus = AuditStatus.APPROVED;
        item.TwclAudits = AuditUtil.InsertInto(item.TwclAudits, AuditUtil.CreateNew(AuditActionType.SUBMIT, item.TwclStatus));
        await item.UpdateAsync();
    }
    /// <summary>
    /// 提交问题建议
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task SubmitSuggestion(BaseId input)
    {
        var item = await _repExamPaper.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001, "试卷");
        if (item.Questions.Trim() == "" || item.Suggestions.Trim() == "")
        {
            throw Oops.Oh(ErrorCode.E3003);
        }
        item.SuggestionStatus = AuditStatus.APPROVED;
        item.SuggestionAudits = AuditUtil.InsertInto(item.SuggestionAudits, AuditUtil.CreateNew(AuditActionType.SUBMIT, item.SuggestionStatus));
        await item.UpdateAsync();
    }
    #endregion


    private static void SetSubmitStatus(List<ExamPaperLiteOutput> items)
    {
        foreach (var item in items)
        {
            item.TwclSubmitted = item.TwclStatus == AuditStatus.AUDIT || item.TwclStatus == AuditStatus.APPROVED || item.TwclStatus == AuditStatus.APPROVE_CANCELED;
            item.SuggestionSubmitted = item.SuggestionStatus == AuditStatus.AUDIT || item.SuggestionStatus == AuditStatus.APPROVED || item.SuggestionStatus == AuditStatus.APPROVE_CANCELED;
        }
    }
}