YBEE.EQM/YBEE.EQM.Application/Exam/ExamQuestionnaire/Services/ExamPatriarchQuestionnaireProgressService.cs

using Org.BouncyCastle.Tls;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 家长问卷填答进度管理服务
/// </summary>
public class ExamPatriarchQuestionnaireProgressService : IExamPatriarchQuestionnaireProgressService, ITransient
{
    private readonly IRepository<ExamPatriarchQuestionnaireProgress> _rep;
    private readonly IExportExcelService _exportExcelService;
    private readonly ISysDictDataService _sysDictDataService;

    public ExamPatriarchQuestionnaireProgressService(IRepository<ExamPatriarchQuestionnaireProgress> rep, IExportExcelService exportExcelService, ISysDictDataService sysDictDataService)
    {
        _rep = rep;
        _exportExcelService = exportExcelService;
        _sysDictDataService = sysDictDataService;
    }

    /// <summary>
    /// 查询学生家长问卷明细
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamStudentPatriarchQuestionnaireProgressOutput>> QueryPageList(ExamStudentPatriarchQuestionnaireProgressPageInput input)
    {
        string where = "T1.exam_plan_id = @examPlanId AND T1.sys_org_id = @sysOrgId";
        if (!string.IsNullOrEmpty(input.Name?.Trim()))
        {
            where = $"{where} AND T1.name LIKE '%@name%'";
        }
        if (!string.IsNullOrEmpty(input.IdNumber?.Trim()))
        {
            where = $"{where} AND T1.id_number LIKE '%@idNumber%'";
        }
        if (!string.IsNullOrEmpty(input.Mobile?.Trim()))
        {
            where = $"{where} AND T1.name LIKE '%@mobile%'";
        }
        if (input.ClassNumber.HasValue)
        {
            where = $"{where} AND T1.class_number = {input.ClassNumber.Value}";
        }
        if (input.GradeId.HasValue)
        {
            where = $"{where} AND T1.grade_id = {input.GradeId.Value}";
        }

        var p = new
        {
            CurrentSysUserInfo.SysOrgId,
            input.PageSize,
            PageOffset = (input.PageIndex - 1) * input.PageSize,
            input.ExamPlanId,
            input.Name,
            input.ClassNumber,
            input.IdNumber,
            input.Mobile,
        };

        var totalCount = await _rep.SqlScalarAsync<int>($@"
SELECT COUNT(1) AS total_count
FROM exam_student AS T1
WHERE {where}", p);


        var items = await _rep.SqlQueriesAsync<ExamStudentPatriarchQuestionnaireProgressOutput>($@"
SELECT ROW_NUMBER() OVER (ORDER BY T1.grade_id, T1.class_number, T2.id) AS `row_number`, 
    T1.sys_org_branch_id, T5.`name` AS sys_org_branch_name,
    T1.id, T1.grade_id, T6.`name` AS grade_name, T1.ncee_course_comb_id,
    T1.school_class_id, T7.`name` AS school_class_name, T7.grade_begin_year, T1.class_number, 
    T1.`name`, T1.certificate_type, T1.id_number, 
    text_mask(T2.mobile, 3, 4) AS mobile, T2.submit_time, T2.is_completed
FROM exam_student AS T1
LEFT JOIN exam_patriarch_questionnaire_progress AS T2 ON T1.id = T2.exam_student_id
LEFT JOIN sys_org AS T5 ON T1.sys_org_branch_id = T5.id
LEFT JOIN base_grade AS T6 ON T1.grade_id = T6.id
LEFT JOIN school_class AS T7 ON T1.school_class_id = T7.id
WHERE {where}
LIMIT @pageSize OFFSET @pageOffset;", p);

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

    /// <summary>
    /// 导出未填答学生名单
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<byte[]> ExportUncompletedExcel(ExportExamPatriarchQuestionnaireProgressInput input)
    {
        var items = await _rep.SqlQueriesAsync<ExamStudentPatriarchQuestionnaireProgressOutput>($@"
SELECT ROW_NUMBER() OVER (ORDER BY T1.grade_id, T1.class_number, T2.id) AS `row_number`, 
    T1.sys_org_branch_id, T5.`name` AS sys_org_branch_name,
    T1.id, T1.grade_id, T6.`name` AS grade_name, T1.ncee_course_comb_id,
    T1.school_class_id, T7.`name` AS school_class_name, T7.grade_begin_year, T1.class_number, 
    T1.`name`, T1.certificate_type, T1.id_number, 
    text_mask(T2.mobile, 3, 4) AS mobile, T2.submit_time, T2.is_completed
FROM exam_student AS T1
LEFT JOIN exam_patriarch_questionnaire_progress AS T2 ON T1.id = T2.exam_student_id
LEFT JOIN sys_org AS T5 ON T1.sys_org_branch_id = T5.id
LEFT JOIN base_grade AS T6 ON T1.grade_id = T6.id
LEFT JOIN school_class AS T7 ON T1.school_class_id = T7.id
WHERE T1.exam_plan_id = @examPlanId AND T1.sys_org_id = @sysOrgId AND T2.is_completed IS NULL
", new { input.ExamPlanId, CurrentSysUserInfo.SysOrgId });

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

        List<ExportExcelColDto<ExamStudentPatriarchQuestionnaireProgressOutput>> cols = new()
        {
            new(){ Name = "年级", Width = 18, GetCellValue = (r) => $"{r.GradeName}（{r.GradeBeginYear}级）" },
            new(){ Name = "班级", Width = 10, GetCellValue = (r) => $"{r.ClassNumber}班" },
            new(){ Name = "校区", Width = 14, GetCellValue = (r) => r.SysOrgBranchName },
            new(){ Name = "学生姓名", Width = 20, GetCellValue = (r) => r.Name },
            new(){ Name = "证件类型", Width = 16, GetCellValue = (r) => certificateTypes[r.CertificateType] },
            new(){ Name = "证件号码", Width = 20, GetCellValue = (r) => r.IdNumber },
        };

        var ret = _exportExcelService.ExportExcel(new ExportExcelDto<ExamStudentPatriarchQuestionnaireProgressOutput>()
        {
            Title = $"{CurrentSysUserInfo.SysOrgName}学生家长问卷未填答名单",
            Columns = cols,
            Items = items,
            IncludeExportTime = false,
        });
        return ret;
    }

    /// <summary>
    /// 获取各班级问卷填答进度
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<List<ExamStudentPatriarchQuestionnaireProgressStatOutput>> GetProgressList(int examPlanId)
    {
        var items = await _rep.SqlQueriesAsync<ExamStudentPatriarchQuestionnaireProgressStatOutput>($@"
SELECT T1.grade_id * 100 + T1.class_number AS id,
    T1.*, T2.`name` AS grade_name, 
    ROUND(CASE WHEN T1.total_count = 0 THEN 0 ELSE T1.completed_count * 1.0 / T1.total_count * 100 END, 2) as completed_progress
FROM
(
    SELECT T1.grade_id, T1.grade_begin_year, T1.class_number, 
        SUM(CASE WHEN T1.is_completed = 1 THEN T1.sub_total_count ELSE 0 END) AS completed_count,
        SUM(CASE WHEN T1.is_completed = 0 THEN T1.sub_total_count ELSE 0 END) AS uncompleted_count,
        MAX(T1.total_count) AS total_count
    FROM
    (
        SELECT T1.*, IFNULL(T2.is_completed, 0) AS is_completed, T2.sub_total_count
        FROM
        (
            SELECT T1.grade_id, T3.grade_begin_year, T1.class_number, COUNT(1) AS total_count
            FROM exam_student AS T1
            LEFT JOIN exam_patriarch_questionnaire_progress AS T2 ON T1.id = T2.exam_student_id
            LEFT JOIN school_class AS T3 ON T1.school_class_id = T3.id
            WHERE T1.exam_plan_id = @examPlanId AND T1.sys_org_id = @sysOrgId
            GROUP BY T1.grade_id, T3.grade_begin_year, T1.class_number
        ) AS T1
        JOIN 
        (
            SELECT T1.grade_id, T1.class_number, T2.is_completed, COUNT(1) AS sub_total_count
            FROM exam_student AS T1
            LEFT JOIN exam_patriarch_questionnaire_progress AS T2 ON T1.id = T2.exam_student_id
            WHERE T1.exam_plan_id = @examPlanId AND T1.sys_org_id = @sysOrgId
            GROUP BY T1.grade_id, T1.class_number, T2.is_completed
        ) AS T2 ON T1.grade_id = T2.grade_id AND T1.class_number = T2.class_number
    ) AS T1
    GROUP BY T1.grade_id, T1.grade_begin_year, T1.class_number
) AS T1
LEFT JOIN base_grade AS T2 ON T1.grade_id = T2.id
ORDER BY T1.grade_id, T1.class_number", new { ExamPlanId = examPlanId, CurrentSysUserInfo.SysOrgId });

        return items;
    }
}