Tiobon.Web.Core/Tiobon.Core.Services/Ghre/Ghre_CourseSceneServices.cs

using static Tiobon.Core.Model.Consts;

namespace Tiobon.Core.Services;

/// <summary>
/// 课程场景 (服务)
/// </summary>
public class Ghre_CourseSceneServices : BaseServices<Ghre_CourseScene, Ghre_CourseSceneDto, InsertGhre_CourseSceneInput, EditGhre_CourseSceneInput>, IGhre_CourseSceneServices
{
    private readonly IBaseRepository<Ghre_CourseScene> _dal;
    public Ghre_CourseSceneServices(ICaching caching, IBaseRepository<Ghre_CourseScene> dal)
    {
        this._dal = dal;
        base.BaseDal = dal;
        base._caching = caching;
    }
    public override async Task<ServicePageResult<Ghre_CourseSceneDto>> QueryFilterPage(QueryBody filter, string condition, bool? IsEnable = true)
    {
        var data = await base.QueryFilterPage(filter, condition, IsEnable);
        data.result.DT_TableDataT1.ForEach(x =>
        {
            x.BuiltInLabel = x.BuiltIn == 1 ? "是" : "否";
        });
        return data;
    }

    public override async Task<ServiceFormResult<Ghre_CourseSceneDto>> QueryForm(QueryForm body)
    {
        var result = await base.QueryForm(body);

        var DT_TableDataT1 = result.result.DT_TableDataT1;
        DT_TableDataT1.ForEach(t =>
        {
            if (!string.IsNullOrWhiteSpace(t.CourseId))
                t.CourseIds = JsonHelper.JsonToObj<List<string>>(t.CourseId);
            else
                t.CourseIds = new List<string>();
        });
        if (body.id.IsNotEmptyOrNull())
        {
            var coures = await Db.Queryable<Ghre_Course>().Where(x => x.CourseSceneIds != null && (x.CourseSceneIds.Contains(body.id.ObjToString()) || x.CourseSceneId == body.id) && x.Status == Consts.DIC_COURSE_STATUS.RELEASED).ToListAsync();
            if (DT_TableDataT1.Any())
                DT_TableDataT1[0].Courses = coures;
        }
        result.result.DT_TableDataT1 = DT_TableDataT1;
        return result;
    }


    public override async Task<Ghre_CourseSceneDto> QueryById(object objId)
    {
        var data = await base.QueryById(objId);
        var DT_TableDataT1 = Mapper.Map(data).ToANew<Ghre_CourseSceneDto>();
        var coures = await Db.Queryable<Ghre_Course>().Where(x => x.CourseSceneIds != null && x.CourseSceneIds.Contains(objId.ObjToString())).ToListAsync();
        if (DT_TableDataT1.CreditRuleId != null)
            DT_TableDataT1.CreditRuleName = (await Db.Queryable<Ghre_CreditRule>().Where(x => x.Id == DT_TableDataT1.CreditRuleId).FirstAsync())?.RuleName;

        DT_TableDataT1.Courses = coures;
        return DT_TableDataT1;
    }

    public override async Task<long> Add(InsertGhre_CourseSceneInput entity)
    {
        var courseIds = new List<long>();
        if (entity.Courses != null && entity.Courses.Any())
        {
            courseIds = entity.Courses.Select(x => x.Id).ToList();
            entity.CourseId = JsonHelper.ObjToJson(courseIds);

            var list = Db.Queryable<Ghre_Course>().Where(x => courseIds.Contains(x.Id)).Select(x => x.CourseName).ToList();

            entity.CourseName = string.Join("、", list.ToArray());
        }
        var id = await base.Add(entity);
        var courses = Db.Queryable<Ghre_Course>().Where(x => courseIds.Contains(x.Id)).ToList();
        for (int i = 0; i < courses.Count; i++)
        {
            var courseSceneIds = new List<long>();
            if (courses[i].CourseSceneIds.IsNotEmptyOrNull())
                courseSceneIds = JsonHelper.JsonToObj<List<long>>(courses[i].CourseSceneIds);
            courseSceneIds.Add(id);
            courses[i].CourseSceneIds = JsonHelper.ObjToJson(courseSceneIds);
            await Db.Updateable(courses).UpdateColumns(it => new { it.CourseSceneIds }, true)//true表示追加AOP赋值列
                .ExecuteCommandAsync();
        }
        return id;
    }

    public override async Task<bool> Update(long Id, EditGhre_CourseSceneInput editModel)
    {
        var courseIds = new List<long>();
        if (editModel.Courses != null && editModel.Courses.Any())
        {
            courseIds = editModel.Courses.Select(x => x.Id).ToList();
            editModel.CourseId = JsonHelper.ObjToJson(courseIds);

            var list = Db.Queryable<Ghre_Course>().Where(x => courseIds.Contains(x.Id)).Select(x => x.CourseName).ToList();

            editModel.CourseName = string.Join("、", list.Select(x => x).ToArray());
        }
        var result = await base.Update(Id, editModel);

        var courses = await Db.Queryable<Ghre_Course>().Where(x => x.CourseSceneIds != null && x.CourseSceneIds.Contains(Id.ObjToString())).ToListAsync();

        for (int j = 0; j < courses.Count; j++)
        {
            var courseSceneIds = new List<long>();
            if (courses[j].CourseSceneIds.IsNotEmptyOrNull())
                courseSceneIds = JsonHelper.JsonToObj<List<long>>(courses[j].CourseSceneIds);
            courseSceneIds = courseSceneIds.Where(x => x != Id).ToList();
            courses[j].CourseSceneIds = JsonHelper.ObjToJson(courseSceneIds);
            await Db.Updateable(courses).UpdateColumns(it => new { it.CourseSceneIds }, true)//true表示追加AOP赋值列
                .ExecuteCommandAsync();
        }
        courses = Db.Queryable<Ghre_Course>().Where(x => courseIds.Contains(x.Id)).ToList();
        for (int i = 0; i < courses.Count; i++)
        {
            var courseSceneIds = new List<long>();
            if (courses[i].CourseSceneIds.IsNotEmptyOrNull())
                courseSceneIds = JsonHelper.JsonToObj<List<long>>(courses[i].CourseSceneIds);
            courseSceneIds.Add(Id);
            courses[i].CourseSceneIds = JsonHelper.ObjToJson(courseSceneIds);
            await Db.Updateable(courses).UpdateColumns(it => new { it.CourseSceneIds }, true)//true表示追加AOP赋值列
                .ExecuteCommandAsync();
        }

        return result;
    }

    #region Excel导入


    public override async Task<ServiceResult<string>> DownloadExcel(string menuName)
    {
        var physicsPath = $"{Environment.CurrentDirectory}{Path.DirectorySeparatorChar}wwwroot";
        var path = $"{$"{Path.DirectorySeparatorChar}files{Path.DirectorySeparatorChar}ExcelTemplate{Path.DirectorySeparatorChar}"}";
        if (!Directory.Exists(physicsPath + path))
            Directory.CreateDirectory(physicsPath + path);

        Type entityType = typeof(Ghre_CourseScene);
        var fileName = entityType.GetEntityTableName() + ".xlsx";


        var physicsPath1 = physicsPath + path + fileName;
        //if (dataSourceLists.Any())
        //    physicsPath1 = physicsPath + path + newFileName;
        var result = ServiceResult<string>.OprateSuccess("课程场景_" + DateTimeHelper.ConvertToSecondString1(DateTime.Now) + ".xlsx", physicsPath1);
        return result;
    }
    public override async Task<ServiceResult<ExcelData>> ImportExcel(IFormFile file, string menuName = null, long? MasterId = null)
    {
        var data = new ExcelData();
        long id = SnowFlakeSingle.instance.getID();
        var physicsPath = $"{Environment.CurrentDirectory}{Path.DirectorySeparatorChar}wwwroot";
        var path = $"{$"{Path.DirectorySeparatorChar}files{Path.DirectorySeparatorChar}import{Path.DirectorySeparatorChar}{id}{Path.DirectorySeparatorChar}"}";
        if (!Directory.Exists(physicsPath + path))
            Directory.CreateDirectory(physicsPath + path);

        var filepath = physicsPath + path + file.FileName;
        using (var stream = File.Create(filepath))
        {
            await file.CopyToAsync(stream);
        }
        string extension = Path.GetExtension(filepath);

        bool isExistError = false;
        var id1 = SnowFlakeSingle.instance.getID();
        string errorFileName = path + SnowFlakeSingle.instance.getID() + extension;

        var dt = NPOIHelper.ImportExcel(filepath, "课程场景");
        if (dt.Columns["Comments"] == null)
            dt.Columns.Add("Comments", typeof(string));

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var comments = new List<string>();

            if (!dt.Columns.Contains("场景编号"))
            {
                comments.Add("未查询到【场景编号】列！");
                data.ErrorCount++;
                dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                isExistError = true;
                continue;
            }
            if (!dt.Columns.Contains("场景名称"))
            {
                comments.Add("未查询到【场景名称】列！");
                data.ErrorCount++;
                dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                isExistError = true;
                continue;
            }
            if (!dt.Columns.Contains("备注"))
            {
                comments.Add("未查询到【备注】列！");
                data.ErrorCount++;
                dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                isExistError = true;
                continue;
            }


            var SceneNo = dt.Rows[i]["场景编号"].ToString();
            var SceneName = dt.Rows[i]["场景名称"].ToString();
            if (SceneNo.IsNullOrEmpty() && SceneName.IsNullOrEmpty())
                continue;

            var remarkSz = dt.Rows[i]["备注"].ToString();

            if (await base.AnyAsync(x => x.SceneName == SceneName && x.SceneNo == SceneNo))
            {
                comments.Add($"系统已存在相同编号名称课程场景数据！");
                data.ErrorCount++;
                dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                isExistError = true;
                continue;
            }


            var dict = new Dictionary<string, object>
                {
                    { "Id", SnowFlakeSingle.Instance.NextId() },
                    { "CreateBy", App.User.ID },
                    { "CreateTime", DateTime.Now },
                    { "SceneNo", SceneNo },
                    { "SceneName", SceneName },
                    { "RemarkSz", remarkSz }
                };
            try
            {
                await Db.Insertable(dict).AS("Ghre_CourseScene").ExecuteCommandAsync();
                data.SuccessCount++;
                //data.SuccessCount += list.Count;
            }
            catch (Exception E)
            {
                comments.Add(E.Message);
                data.ErrorCount++;
                dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                isExistError = true;
                continue;
            }

        }

        if (isExistError)
        {
            NPOIHelper.ExportExcel(dt, null, "课程场景", physicsPath + errorFileName);
            data.filePath = "/Advanced" + errorFileName;
        }
        return ServiceResult<ExcelData>.OprateSuccess("导入成功！", data);
    }
    #endregion



    #region 课程统计
    /// <summary>
    /// 课程统计
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<dynamic> QueryStatistic(long id)
    {

        dynamic obj = new ExpandoObject();
        dynamic data = new ExpandoObject();
        var entity = await QueryById(id);

        //data.CourseClassName = entity.CourseClassName;
        //data.StandardHour = entity.StandardHour;
        //data.CreditPoints = entity.CreditPoints;
        //data.ManagerStaffName = entity.ManagerStaffName;
        //data.InOrOut = entity.InOrOutLabel;
        //data.IsOpen = entity.IsOPenLabel;
        //data.ValidityPeriod = entity.ValidityPeriod;
        //data.CourseSceneName = entity.CourseSceneName;

        //必修人数
        var RequiredCount = await Db.Queryable<Ghre_StudyRecord>().Where(x => x.CourseId == id && (x.CourseType == "ManualRequired" || x.CourseType == "Required")).CountAsync();
        data.RequiredCount = RequiredCount;

        //选修人次
        var ElectiveCount = await Db.Queryable<Ghre_StudyRecord>().Where(x => x.CourseId == id && (x.CourseType == "ManualElective" || x.CourseType == "Elective")).CountAsync();
        data.ElectiveCount = ElectiveCount;

        var CompleteCount = await Db.Queryable<Ghre_StudyRecord>().Where(x => x.CourseId == id && x.CompleteStatus == DIC_STUDY_RECORD_STUDY_COMPLETE_STATUS.FINISHED && (x.CourseType == "ManualElective" || x.CourseType == "Elective" || x.CourseType == "ManualElective" || x.CourseType == "Elective")).CountAsync();
        //完成人数
        data.CompleteCount = CompleteCount;
        //开班人数
        var OpenClassCount = await Db.Queryable<Ghre_StudyRecord>().Where(x => x.CourseId == id && x.OpenClassId != null).CountAsync();
        data.OpenClassCount = OpenClassCount;

        var studyRecordIds = await Db.Queryable<Ghre_StudyRecord>().Where(x => x.CourseId == id).Select(x => x.Id).ToListAsync();
        //总学习时长
        data.TotalStudyDuration = await Db.Queryable<Ghre_StudyRecordDetail>().Where(x => x.StudyRecordId != null && studyRecordIds.Contains(x.StudyRecordId.Value)).SumAsync(x => x.StudyDuration);

        var AvgStudyDuration = await Db.Queryable<Ghre_StudyRecordDetail>().Where(x => x.StudyRecordId != null && studyRecordIds.Contains(x.StudyRecordId.Value)).GroupBy(x => x.StaffId)
                    .Select(m => new { m.StaffId, StudyDuration = SqlFunc.AggregateSum(m.StudyDuration) }).ToListAsync();
        //平均学习时长
        data.AvgStudyDuration = AvgStudyDuration.Average(x => x.StudyDuration);
        //平均分
        var AvgScore = await Db.Queryable<Ghre_ExamRecord>().Where(x => x.StudyRecordId != null && studyRecordIds.Contains(x.StudyRecordId.Value)).AvgAsync(x => x.FinallyScore ?? (x.Score + x.AdjustScore));
        data.AvgScore = AvgScore ?? 0;

        //通过率
        var passPercent = 0;

        if (CompleteCount > 0 && (RequiredCount + ElectiveCount + OpenClassCount) > 0)
            passPercent = CompleteCount / (RequiredCount + ElectiveCount + OpenClassCount);
        data.PassPercent = passPercent;

        //考试安排次数
        data.ExamScheduleCount = await Db.Queryable<Ghre_Exam>().Where(x => x.CourseId == id).CountAsync();

        //考试人数
        data.ExamCount = await Db.Queryable<Ghre_ExamRecord>().Where(x => x.CourseId == id).CountAsync();

        //考试人次
        data.ExamGroupCount = await Db.Queryable<Ghre_ExamRecordGroup>()
            .Where(x => x.StudyRecordId != null && studyRecordIds.Contains(x.StudyRecordId.Value))
            .CountAsync();

        //反馈人数
        data.FeedbackCount = 0;

        #region 课件学习时长
        var courseWareStudyDuration = await Db.Queryable<Ghre_StudyRecordDetail>()
            .Where(a => a.StudyRecordId != null && studyRecordIds.Contains(a.StudyRecordId.Value))

            .GroupBy(a => new { a.CourseWareId, a.CourseWareAttachmentId })
                    .Select(a => new
                    {
                        a.CourseWareId,
                        a.CourseWareAttachmentId,
                        StudyDuration = SqlFunc.AggregateSum(a.StudyDuration)
                    })
                    .ToListAsync();

        var courseWareStudyDurations = new JArray();

        for (int i = 0; i < courseWareStudyDuration.Count; i++)
        {
            var courseWare = await Db.Queryable<Ghre_CourseWare>().Where(x => x.Id == courseWareStudyDuration[i].CourseWareId).FirstAsync();
            var courseWareAttachment = await Db.Queryable<Ghre_CourseWareAttachment>().Where(x => x.Id == courseWareStudyDuration[i].CourseWareAttachmentId).FirstAsync();
            var item = new JObject
            {
                new JProperty("CourseWareId", courseWareStudyDuration[i].CourseWareAttachmentId),
                new JProperty("CourseWareAttachmentId", courseWareStudyDuration[i].CourseWareAttachmentId),
                new JProperty("StudyDuration", courseWareStudyDuration[i].StudyDuration),
                new JProperty("CourseWareName", courseWare?.CourseWareNo+courseWare?.CourseWareName+courseWare?.VersionNo+courseWareAttachment.AttachmentName),
            };
            courseWareStudyDurations.Add(item);
        }
        data.CourseWareStudyDurations = courseWareStudyDurations;
        #endregion

        #region 课件学习人数占比
        var courseWareStudyCount1 = await Db.Queryable<Ghre_StudyRecordDetail>()
            .Where(a => a.StudyRecordId != null && studyRecordIds.Contains(a.StudyRecordId.Value))
            .Select(a => new { a.CourseWareAttachmentId, a.StaffId })
            .Distinct().ToListAsync();
        var courseWareStudyCount = courseWareStudyCount1
            .GroupBy(a => a.CourseWareAttachmentId)
            .Select(a => new
            {
                CourseWareAttachmentId = a.Key,
                Count = a.Count()
            })
            .ToList();

        var courseWareStudyCounts = new JArray();

        for (int i = 0; i < courseWareStudyCount.Count; i++)
        {
            var courseWareAttachment = await Db.Queryable<Ghre_CourseWareAttachment>().Where(x => x.Id == courseWareStudyCount[i].CourseWareAttachmentId).FirstAsync();
            var item = new JObject
            {
                new JProperty("CourseWareAttachmentId", courseWareStudyCount[i].CourseWareAttachmentId),
                new JProperty("CourseWareName",  courseWareAttachment.AttachmentName),
                new JProperty("Count",  courseWareStudyCount[i].Count),
            };
            courseWareStudyCounts.Add(item);
        }
        data.CourseWareStudyCounts = courseWareStudyCounts;
        #endregion

        #region 关联的考试排行
        var exams = await Db.Queryable<Ghre_Exam>()
            .LeftJoin<Ghre_ExamPaper>((a, b) => a.ExamPaperId == b.Id)//多个条件用&&
            .Where(a => a.CourseId == id && a.Status != "Draft")
            .Select((a, b) => new
            {
                a.Id,
                a.ExamNo,
                a.ExamName,
                b.PaperName
            })
            .ToListAsync();
        var examRanking = new JArray();
        var examRankings = new JArray();

        for (int i = 0; i < exams.Count; i++)
        {

            var groups = await Db.Queryable<Ghre_ExamRecordGroup>().Where(x => x.ExamId == exams[i].Id).CountAsync();
            var examRecordCount = await Db.Queryable<Ghre_ExamRecord>().Where(x => x.ExamId == exams[i].Id).CountAsync();
            var examRecordPassCount = await Db.Queryable<Ghre_ExamRecord>()
                .Where(x => x.ExamId == exams[i].Id && x.IsPass == true && x.IsPass != null)
                .CountAsync();
            var examRecordRetakeCount = await Db.Queryable<Ghre_ExamRecord>()
                .Where(x => x.ExamId == exams[i].Id && x.RetakeTimes > 0 && x.RetakeTimes != null)
                .CountAsync();

            passPercent = 0;
            var retakePercent = 0;

            if (examRecordCount > 0)
            {
                if (examRecordPassCount > 0) passPercent = (examRecordPassCount / examRecordCount) * 100;
                if (examRecordRetakeCount > 0) retakePercent = (examRecordRetakeCount / examRecordCount) * 100;
            }

            var item = new JObject
            {
                new JProperty("ExamName", $"{exams[i].ExamName}（{exams[i].ExamNo}）" ),
                new JProperty("PaperName",  exams[i].PaperName),
                new JProperty("Attempts", groups),
                new JProperty("PassPercent", passPercent),
                new JProperty("RetakePercent", retakePercent), 
                //new JProperty("CourseWareName",  courseWareAttachment.AttachmentName),
            };
            examRankings.Add(item);
        }
        data.ExamRankings = examRankings;
        #endregion

        obj.Data = data;
        obj.Success = true;
        obj.Status = 200;
        obj.Message = "查询成功！";
        return obj;
    }
    #endregion
}