YBEE.EQM/YBEE.EQM.Application/Exam/ExamSpecialStudent/Services/ExamSpecialStudentAuditService.cs

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

namespace YBEE.EQM.Application;

/// <summary>
/// 特殊学生审核服务
/// </summary>
public class ExamSpecialStudentAuditService : IExamSpecialStudentAuditService, ITransient
{
    private readonly IRepository<ExamSpecialStudent> _rep;
    private readonly ISysRoleService _sysRoleService;

    public ExamSpecialStudentAuditService(IRepository<ExamSpecialStudent> rep, ISysRoleService sysRoleService)
    {
        _rep = rep;
        _sysRoleService = sysRoleService;
    }

    /// <summary>
    /// 提交审核
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Submit(BaseId input)
    {
        var item = await _rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        var dtype = await _rep.Change<ExamDataReport>().Where(t => t.ExamPlanId == item.ExamPlanId && t.Type == DataReportType.SP_STUDENT).FirstOrDefaultAsync();
        if (dtype == null || dtype.Status != ExamStatus.ACTIVE || (item.Status != AuditStatus.UNSUBMIT && item.Status != AuditStatus.REJECTED))
        {
            throw Oops.Oh(ErrorCode.E2006);
        }
        item.Status = AuditStatus.AUDIT;
        item.Audits = AuditUtil.InsertInto(item.Audits, AuditUtil.CreateNew(AuditActionType.SUBMIT, item.Status));
        await item.UpdateAsync();
    }

    /// <summary>
    /// 审核
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Audit(ExamSepcialStudentAuditInput input)
    {
        var dt = DateTime.Now;
        var items = await _rep.Where(t => input.Ids.Contains(t.Id)).ToListAsync();
        foreach (var item in items)
        {
            item.Status = input.IsApproved ? AuditStatus.APPROVED : AuditStatus.REJECTED;
            item.Audits = AuditUtil.InsertInto(item.Audits, AuditUtil.CreateNew(input.IsApproved ? AuditActionType.APPROVE : AuditActionType.REJECT, item.Status, dt, input.Remark));
        }
        await _rep.UpdateAsync(items);
    }

    /// <summary>
    /// 反审
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Reaudit(BaseId input)
    {
        var item = await _rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        if (item.Status != AuditStatus.APPROVED)
        {
            throw Oops.Oh(ErrorCode.E2006);
        }
        item.Status = AuditStatus.APPROVE_CANCELED;
        item.Audits = AuditUtil.InsertInto(item.Audits, AuditUtil.CreateNew(AuditActionType.REAUDIT, item.Status));
        await item.UpdateAsync();
    }


    //public async Task<byte[]> ExportAll(int examPlanId)
    //{

    //}


    /// <summary>
    /// 获取待审核监测计划列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamPlanAuditOutput>> QueryExamPlanPageList(ExamPlanPageInput input)
    {
        var roleDataScope = await _sysRoleService.GetCurrentUserDataScope();
        if (roleDataScope == null || roleDataScope.EducationStages.Count == 0)
        {
            return new();
        }

        List<string> whereCause = new();
        if (roleDataScope.EducationStages.Count == 0)
        {
            whereCause.Add($"T1.education_stage = {(short)roleDataScope.EducationStages[0]}");
        }
        else
        {
            var ess = roleDataScope.EducationStages.Select(t => $"T1.education_stage = {(short)t}").ToList();
            string wes = string.Join(" OR ", ess);
            whereCause.Add($"({wes})");
        }
        if (!string.IsNullOrEmpty(input.Name?.Trim()))
        {
            whereCause.Add("T1.full_name LIKE '%@name%'");
        }
        if (input.SemesterId.HasValue)
        {
            whereCause.Add("T1.semester_id = @semesterId");
        }
        if (input.Status.HasValue)
        {
            whereCause.Add("T1.status = @status");
        }

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

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

        var totalCount = await _rep.SqlScalarAsync<int>($@"
SELECT COUNT(1) AS total_count
FROM
(
	SELECT T1.id, COUNT(T1.sys_org_id) AS org_count
	FROM
	(
		SELECT T1.id, T2.sys_org_id
		FROM exam_plan AS T1
        JOIN exam_data_report AS EDR ON T1.id = EDR.exam_plan_id
		LEFT JOIN exam_org AS T2 ON T1.id = T2.exam_plan_id
		{whereSql}
		GROUP BY T1.id, t2.sys_org_id
	) AS T1
	GROUP BY T1.id
) AS T
", p);


        var items = await _rep.SqlQueriesAsync<ExamPlanAuditOutput>($@"
SELECT
    T1.id, 
    T1.full_name, 
    T1.short_name, 
    T1.`name`,
    T1.semester_id, 
    T1.education_stage, 
    T1.`status`,
    COUNT(DISTINCT T1.sys_org_id) AS org_count,
    SUM(T1.total_count) AS total_count,
    SUM(T1.audit_count) AS audit_count,
    SUM(T1.approved_count) AS approved_count,
    SUM(T1.rejected_count) AS rejected_count,
    SUM(T1.pre_identified_count) AS pre_identified_count
FROM
(
    SELECT 
        T1.id, 
        T1.full_name, 
        T1.short_name,
        T1.`name`,
        T1.semester_id, 
        T1.education_stage, 
        T1.`status`,
        T2.sys_org_id,
        COUNT(CASE WHEN T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED} THEN T4.id ELSE NULL END) AS total_count,
        COUNT(CASE WHEN (T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED}) AND (T4.`status` ={(short)AuditStatus.AUDIT} OR T4.`status` = {(short)AuditStatus.APPROVE_CANCELED}) THEN T4.id ELSE NULL END) AS audit_count,
        COUNT(CASE WHEN (T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED}) AND T4.`status` = {(short)AuditStatus.APPROVED} THEN T4.id ELSE NULL END) AS approved_count,
        COUNT(CASE WHEN (T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED}) AND T4.`status` = {(short)AuditStatus.REJECTED} THEN T4.id ELSE NULL END) AS rejected_count,
        COUNT(CASE WHEN (T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED}) AND T4.is_pre_identified = 1 THEN T4.id ELSE NULL END) AS pre_identified_count
    FROM exam_plan AS T1
    JOIN exam_data_report AS EDR ON T1.id = EDR.exam_plan_id
    LEFT JOIN exam_org AS T2 ON T1.id = T2.exam_plan_id
    LEFT JOIN (SELECT * FROM exam_org_data_report WHERE type = {(short)DataReportType.SP_STUDENT}) AS T3 ON T2.id = T3.exam_org_id AND EDR.type = T3.type
    LEFT JOIN exam_special_student AS T4 ON T1.id = T4.exam_plan_id AND T2.sys_org_id = T4.sys_org_id
    {whereSql}
GROUP BY T1.id, T1.full_name, T1.short_name, T1.`name`, T1.semester_id, T1.education_stage, T1.`status`, T2.sys_org_id
) AS T1
GROUP BY T1.id, T1.full_name, T1.short_name, T1.`name`, T1.semester_id, T1.education_stage, T1.`status`
ORDER BY T1.id DESC
LIMIT @pageSize OFFSET @pageOffset;
", p);

        PageResult<ExamPlanAuditOutput> 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<PageResult<ExamPlanOrgAuditOutput>> QueryOrgAuditPageList(ExamOrgDataReportAuditPageInput input)
    {
        var roleDataScope = await _sysRoleService.GetCurrentUserDataScope();
        if (roleDataScope == null || roleDataScope.EducationStages.Count == 0)
        {
            return new();
        }

        var p = new
        {
            input.PageSize,
            PageOffset = (input.PageIndex - 1) * input.PageSize,
            input.ExamPlanId,
            input.SearchValue,
            input.SysOrgName,
            input.ExamPlanName,
            Type = (short)DataReportType.SP_STUDENT,
        };

        List<string> whereCause = new();
        if (input.ExamPlanId.HasValue)
        {
            whereCause.Add("T1.id = @examPlanId");
        }
        if (roleDataScope.EducationStages.Count == 0)
        {
            whereCause.Add($"T1.education_stage = {(short)roleDataScope.EducationStages[0]}");
        }
        else
        {
            var ess = roleDataScope.EducationStages.Select(t => $"T1.education_stage = {(short)t}").ToList();
            string wes = string.Join(" OR ", ess);
            whereCause.Add($"({wes})");
        }
        if (input.SemesterId.HasValue)
        {
            whereCause.Add("T1.semester_id = @semesterId");
        }
        if (!string.IsNullOrEmpty(input.ExamPlanName?.Trim()))
        {
            whereCause.Add("T1.full_name LIKE '%@examPlanName%'");
        }
        if (!string.IsNullOrEmpty(input.SysOrgName?.Trim()))
        {
            whereCause.Add("ORG.full_name LIKE '%@sysOrgName%'");
        }

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

        string fromSql = $@"
FROM exam_plan AS T1
JOIN exam_org AS T2 ON T1.id = T2.exam_plan_id
JOIN sys_org AS ORG ON T2.sys_org_id = ORG.id
LEFT JOIN (SELECT * FROM exam_org_data_report WHERE type = {(short)DataReportType.SP_STUDENT}) AS T3 ON T2.id = T3.exam_org_id
LEFT JOIN exam_special_student AS T4 ON T1.id = T4.exam_plan_id AND T2.sys_org_id = T4.sys_org_id
";

        string groupSql = @"GROUP BY T2.exam_plan_id, T1.full_name, T1.`name`, T1.semester_id, T1.education_stage, T1.`status`, T2.sys_org_id, ORG.full_name, ORG.`name`, ORG.`code`, T3.`status`, T3.report_time";

        var totalCount = await _rep.SqlScalarAsync<int>($@"
SELECT COUNT(1) AS total_count
FROM
(
    SELECT COUNT(1) AS total_count
    {fromSql}
    {whereSql}
    {groupSql}
) AS T", p);


        var items = await _rep.SqlQueriesAsync<ExamPlanOrgAuditOutput>($@"
SELECT ROW_NUMBER() OVER (ORDER BY T.exam_plan_id DESC, T.audit_count DESC, T.report_time) AS `row_number`, T.*
FROM
(
    SELECT
	    T2.exam_plan_id, 
	    T1.full_name AS exam_plan_full_name, 
	    T1.`name` AS exam_plan_name, 
	    T1.semester_id, 
	    T1.education_stage, 
	    T1.`status` AS exam_status,
	    T2.sys_org_id,
	    ORG.full_name AS sys_org_full_name,
	    ORG.`name` AS sys_org_name,
	    ORG.`code` AS sys_org_code,
	    IFNULL(T3.`status`, 1) AS data_report_status,
	    T3.report_time,	
	    COUNT(CASE WHEN (T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED}) THEN T4.id ELSE NULL END) AS total_count,
	    COUNT(CASE WHEN (T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED}) AND (T4.`status` = {(short)AuditStatus.AUDIT} OR T4.`status` = {(short)AuditStatus.APPROVE_CANCELED}) THEN T4.id ELSE NULL END) AS audit_count,
	    COUNT(CASE WHEN (T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED}) AND T4.`status` = {(short)AuditStatus.APPROVED} THEN T4.id ELSE NULL END) AS approved_count,
	    COUNT(CASE WHEN (T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED}) AND T4.`status` = {(short)AuditStatus.REJECTED} THEN T4.id ELSE NULL END) AS rejected_count,
        COUNT(CASE WHEN (T3.`status` = {(short)DataReportStatus.REPORTED} OR T3.`status` = {(short)DataReportStatus.REJECTED}) AND T4.is_pre_identified = 1 THEN T4.id ELSE NULL END) AS pre_identified_count
    {fromSql}
    {whereSql}
    {groupSql}
) AS T
ORDER BY T.exam_plan_id DESC, T.audit_count DESC, T.data_report_status DESC, T.report_time DESC
LIMIT @pageSize OFFSET @pageOffset;
", p);

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