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

using SqlSugar.Extensions;
using System.Collections.Generic;

namespace Tiobon.Core.Services;

/// <summary>
/// 岗位技能_宝连通 (服务)
/// </summary>
public class Ghre_TitleSkill_BoltoneServices : BaseServices<Ghre_TitleSkill_Boltone, Ghre_TitleSkill_BoltoneDto, InsertGhre_TitleSkill_BoltoneInput, EditGhre_TitleSkill_BoltoneInput>, IGhre_TitleSkill_BoltoneServices
{
    private readonly IBaseRepository<Ghre_TitleSkill_Boltone> _dal;
    private readonly ICommonServices _commonServices;
    private readonly IGhra_StaffServices _staffServices;
    public Ghre_TitleSkill_BoltoneServices(ICaching caching,
        IBaseRepository<Ghre_TitleSkill_Boltone> dal,
        IGhra_StaffServices staffServices,
        ICommonServices commonServices)
    {
        this._dal = dal;
        base.BaseDal = dal;
        base._caching = caching;
        _staffServices = staffServices;
        _commonServices = commonServices;
    }
    public override async Task<ServicePageResult<Ghre_TitleSkill_BoltoneDto>> QueryFilterPage(QueryBody filter, string condition, bool? IsEnable = true)
    {
        if (filter.orderBy == "EffectiveDate1")
            filter.orderBy = "EffectiveDate";
        if (filter.orderBy == "ExpiryDate1")
            filter.orderBy = "ExpiryDate";

        var result = await base.QueryFilterPage(filter, condition, IsEnable);
        var DT_TableDataT1 = result.result.DT_TableDataT1;

        var deptIds = DT_TableDataT1.Select(x => x.DeptId).Distinct().ToList();
        var courseIds = DT_TableDataT1.Select(x => x.CourseId).Distinct().ToList();
        var titleIds = DT_TableDataT1.Select(x => x.TitleId).Distinct().ToList();

        var depts = await Db.Queryable<Ghro_Dept>().Where(x => deptIds.Contains(x.DeptID)).ToListAsync();

        var deptIds1 = depts.Select(x => x.ParentDeptID).Distinct().ToList();
        depts = await Db.Queryable<Ghro_Dept>().Where(x => deptIds.Contains(x.DeptID) || deptIds1.Contains(x.DeptID)).ToListAsync();
        var courses = await Db.Queryable<Ghre_Course>().Where(x => courseIds.Contains(x.Id)).ToListAsync();
        var courseScenes = await Db.Queryable<Ghre_CourseScene>().Where(x => courseIds.Contains(x.Id)).ToListAsync();
        var titles = await Db.Queryable<Ghra_Title>().Where(x => titleIds.Contains(x.TitleID)).ToListAsync();

        for (int i = 0; i < DT_TableDataT1.Count; i++)
        {
            if (DT_TableDataT1[i].CourseId.IsNotEmptyOrNull())
                DT_TableDataT1[i].CourseName = courses.Where(x => x.Id == DT_TableDataT1[i].CourseId).Select(x => x.CourseName).FirstOrDefault();
            if (DT_TableDataT1[i].CourseName.IsNullOrEmpty())
                DT_TableDataT1[i].CourseName = courseScenes.Where(x => x.Id == DT_TableDataT1[i].CourseId).Select(x => x.SceneName).FirstOrDefault();


            if (DT_TableDataT1[i].DeptId.IsNotEmptyOrNull())
            {
                var dept = depts.Where(x => x.DeptID == DT_TableDataT1[i].DeptId).FirstOrDefault();
                DT_TableDataT1[i].DeptName = dept?.DeptName;

                if (dept != null && dept.ParentDeptID.IsNotEmptyOrNull())
                    DT_TableDataT1[i].ParentDeptName = await Db.Queryable<Ghro_Dept>().Where(x => x.DeptID == dept.ParentDeptID).Select(X => X.DeptName).FirstAsync();

            }
            if (DT_TableDataT1[i].TitleId.IsNotEmptyOrNull())
                DT_TableDataT1[i].TitleName = titles.Where(x => x.TitleID == DT_TableDataT1[i].TitleId).Select(x => x.TitleName).FirstOrDefault();
            DT_TableDataT1[i].EffectiveDate1 = DateTimeHelper.ConvertToDayString(DT_TableDataT1[i].EffectiveDate);
            DT_TableDataT1[i].ExpiryDate1 = DateTimeHelper.ConvertToDayString(DT_TableDataT1[i].ExpiryDate);
        }
        return result;
    }

    //public override async Task<long> Add(InsertGhre_TitleSkill_BoltoneInput entity)
    //{
    //    if (entity.StaffId != null)
    //        entity.StaffIds = JsonHelper.ObjToJson(entity.StaffId.OrderBy(n => n).ToList());
    //    if (entity.StaffId.IsNotEmptyOrNull() && entity.StaffId.Count > 0)
    //        entity.TrainNum = entity.StaffId.Count;
    //    if (entity.Months != null)
    //        entity.Month = JsonHelper.ObjToJson(entity.Months.OrderBy(n => n).ToList());
    //    var result = await base.Add(entity);

    //    return result;
    //}

    //public override async Task<bool> Update(long Id, EditGhre_TitleSkill_BoltoneInput editModel)
    //{
    //    if (editModel.StaffId != null)
    //        editModel.StaffIds = JsonHelper.ObjToJson(editModel.StaffId);

    //    if (editModel.StaffId.IsNotEmptyOrNull() && editModel.StaffId.Count > 0)
    //        editModel.TrainNum = editModel.StaffId.Count;
    //    if (editModel.Months != null)
    //        editModel.Month = JsonHelper.ObjToJson(editModel.Months.OrderBy(n => n).ToList());

    //    var result = await base.Update(Id, editModel);
    //    return result;
    //}

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

    //    string StaffIds = result.result.DT_TableDataT1[0].StaffIds;
    //    string Month = result.result.DT_TableDataT1[0].Month;
    //    if (!string.IsNullOrWhiteSpace(StaffIds))
    //        result.result.DT_TableDataT1[0].StaffId = JsonConvert.DeserializeObject<List<int>>(StaffIds);

    //    if (!string.IsNullOrWhiteSpace(Month))
    //        result.result.DT_TableDataT1[0].Months = JsonConvert.DeserializeObject<List<string>>(Month);
    //    if (result.result.DT_TableDataT1[0].TrainNum != null && result.result.DT_TableDataT1[0].TrainDays != null)
    //        result.result.DT_TableDataT1[0].TotalDays = result.result.DT_TableDataT1[0].TrainNum * result.result.DT_TableDataT1[0].TrainDays;
    //    return result;
    //}

    public async Task<dynamic> QueryMatrix(QueryBody filter)
    {
        dynamic obj = new ExpandoObject();
        dynamic result = new ExpandoObject();

        string titleSkillsCondition = "1=1";
        int? TitleId = null;
        DateTime? Date = null;
        int? StaffId = null;
        int? DeptId = null;
        int? ParentDeptId = null;
        long? CourseId = null;
        if (filter.jsonParam != null)
            foreach (JProperty jProperty in filter.jsonParam.Properties())
            {
                var name = jProperty.Name;
                var value = jProperty.Value.ToString();
                if (name == "page" || name == "pageSize")
                    continue;
                if (name == "TitleId")
                {
                    var jsonParam = JsonConvert.DeserializeObject<JsonParam>(value);

                    if (jsonParam.columnValue.IsNotEmptyOrNull())
                        TitleId = jsonParam.columnValue.ObjToInt();
                    continue;
                }
                else if (name == "Date")
                {
                    var jsonParam = JsonConvert.DeserializeObject<JsonParam>(value);

                    if (jsonParam.columnValue.IsNotEmptyOrNull())
                        Date = jsonParam.columnValue.ObjToDate();
                    continue;
                }
                else if (name == "StaffId")
                {
                    var jsonParam = JsonConvert.DeserializeObject<JsonParam>(value);

                    if (jsonParam.columnValue.IsNotEmptyOrNull())
                        StaffId = jsonParam.columnValue.ObjToInt();
                    continue;
                }
                else if (name == "ParentDeptName")
                {
                    var jsonParam = JsonConvert.DeserializeObject<JsonParam>(value);

                    if (jsonParam.columnValue.IsNotEmptyOrNull())
                        ParentDeptId = jsonParam.columnValue.ObjToInt();
                    continue;
                }
                else if (name == "DeptName")
                {
                    var jsonParam = JsonConvert.DeserializeObject<JsonParam>(value);

                    if (jsonParam.columnValue.IsNotEmptyOrNull())
                        DeptId = jsonParam.columnValue.ObjToInt();
                    continue;
                }
                else if (name == "CourseId")
                {
                    var jsonParam = JsonConvert.DeserializeObject<JsonParam>(value);

                    if (jsonParam.columnValue.IsNotEmptyOrNull())
                        CourseId = jsonParam.columnValue.ObjToLong();
                    continue;
                }
            }

        if (TitleId != null)
            titleSkillsCondition += $" AND TitleId='{TitleId}'";

        if (CourseId != null)
            titleSkillsCondition += $" AND CourseId='{CourseId}'";

        if (Date != null)
            titleSkillsCondition += $" AND EffectiveDate <='{Date}' AND ExpiryDate >='{Date}'";

        var deptIds1 = new List<int?>();
        if (DeptId != null)
            deptIds1.Add(DeptId);

        if (ParentDeptId != null)
        {
            var deptIds = await Db.Queryable<Ghro_Dept>()
                .Where(x => x.ParentDeptID != null && x.ParentDeptID == ParentDeptId)
                .Select(x => x.DeptID).ToListAsync();
            if (deptIds.Any())
                deptIds1.AddRange(deptIds);
            else deptIds1 = new List<int?>() { 0 };
        }
        if (deptIds1.Any())
        {
            titleSkillsCondition += " AND (";
            var i = 0;
            deptIds1.ForEach(x =>
            {
                if (i == 0)
                    titleSkillsCondition += $"DeptId={x}";
                else
                    titleSkillsCondition += $" OR DeptId={x}";

                i++;
            });
            titleSkillsCondition += ")";
        }

        var titleSkillsfilter = new QueryBody()
        {
            menuName = "F_TitleSkill_Boltone",
            pageNum = 1,
            pageSize = 10000,
            orderBy = ""
        };

        var src = Db.Queryable<Ghre_TitleSkill_Boltone>();



        var titleSkills = await QueryFilterPage(titleSkillsfilter, titleSkillsCondition);

        var group1 = titleSkills.result.DT_TableDataT1
            .Select(x => new
            {
                x.DeptId,
                x.TitleId
            }).Distinct().ToList();

        string condition = "1=1";

        if (group1.Any())
        {
            condition += "AND (";
            var i = 0;
            group1.ForEach(x =>
            {
                if (i == 0)
                    condition += $"(DeptId={x.DeptId} AND TitleId={x.TitleId})";
                else
                    condition += $"OR (DeptId={x.DeptId} AND TitleId={x.TitleId})";

                i++;
            });
            condition += ")";
        }
        else
            condition += $" AND 1!=1";

        if (deptIds1.Any())
        {
            condition += " AND (";
            var i = 0;
            deptIds1.ForEach(x =>
            {
                if (i == 0)
                    condition += $"DeptId={x}";
                else
                    condition += $" OR DeptId={x}";

                i++;
            });
            condition += ")";
        }

        if (StaffId != null)
            condition += $" AND StaffID={StaffId}";
        var result2 = await _staffServices.QueryFilterPage(filter, condition);

        var DT_TableDataT1 = result2.result.DT_TableDataT1;

        var param = new ModuleParam()
        {
            langId = filter.langId,
            menuName = filter.menuName
        };
        var module = await _commonServices.GetModuleInfoAsync(param);

        var columns = new JArray();

        //var src = Db.Queryable<Ghre_StudyRecord>();
        for (int i = 0; i < result2.result.DT_TableDataT1.Count; i++)
        {
            var column = result2.result.DT_TableDataT1[i];

            column.Reverse16 = await GetParaLabel("R09", column.Reverse16);

            var item1 = new JObject
            {
                new JProperty("StaffID", column.StaffID),
                new JProperty("DeptName",  column.DeptName),
                new JProperty("IndateLabel",  column.IndateLabel),
                new JProperty("StaffNo",  column.StaffNo),
                new JProperty("StaffName",  column.StaffName),
                new JProperty("StaffType1Label",  column.Reverse16),
                new JProperty("TitleName",  column.TitleName),
                new JProperty("ParentDeptName",  column.ParentDeptName),

            };
            //×	生手 技能有效期内所对应课程还未分配或课程学习时长为0							
            //◑		受训中 技能有效期内所对应课程学习时长大于0小时							
            //☆	熟练工 技能有效期内所对应的课程学习状态为已完成（含必修、选修课程状态）							
            //★	可带教 员工技能所对应的课程学习状态已完成，且通过讲师课程培训学习和考核

            for (int j = 0; j < titleSkills.result.DT_TableDataT1.Count; j++)
            {
                var item = titleSkills.result.DT_TableDataT1[j];
                if (column.DeptID != item.DeptId || column.TitleID != item.TitleId)
                    continue;
                var key = item.CourseId + "_" + item.DeptId + "_" + item.RequiredElective;

                var value = "×";

                if (await Db.Queryable<Ghre_StudyRecord>().Where(x =>
               (x.CourseId == item.CourseId || x.CourseSceneId == item.CourseId) &&
                x.StaffId == column.StaffID &&
                x.StudyStatus == "NoFinish" &&
                ((x.CourseBeginTime >= item.EffectiveDate && x.CourseBeginTime <= item.ExpiryDate) || (x.CourseEndTime >= item.EffectiveDate && x.CourseEndTime <= item.ExpiryDate))
                ).AnyAsync())
                    value = "◑";
                if (await Db.Queryable<Ghre_StudyRecord>().Where(x =>
          (x.CourseId == item.CourseId || x.CourseSceneId == item.CourseId) &&
          x.StaffId == column.StaffID &&
          x.StudyStatus == "HasFinish" &&
          ((x.CourseBeginTime >= item.EffectiveDate && x.CourseBeginTime <= item.ExpiryDate) || (x.CourseEndTime >= item.EffectiveDate && x.CourseEndTime <= item.ExpiryDate))
          ).AnyAsync())
                    value = "☆";
                string courseName = string.Empty;
                courseName = await Db.Queryable<Ghre_Course>().Where(x => x.Id == item.CourseId).Select(x => x.CourseName).FirstAsync();
                if (courseName.IsNullOrEmpty())
                    courseName = await Db.Queryable<Ghre_CourseScene>().Where(x => x.Id == item.CourseId).Select(x => x.SceneName).FirstAsync();


                long? courseId = null;

                courseId = await Db.Queryable<Ghre_Course>().Where(x => x.CourseName.Contains(courseName) && x.CourseName.Contains("可带教")).Select(x => x.Id).FirstAsync();
                if (courseId == 0)
                    courseId = await Db.Queryable<Ghre_CourseScene>().Where(x => x.SceneName.Contains(courseName) && x.SceneName.Contains("可带教")).Select(x => x.Id).FirstAsync();

                if (courseId != null && courseId > 0)
                    if (await Db.Queryable<Ghre_StudyRecord>().Where(x =>
                              (x.CourseId == courseId || x.CourseSceneId == courseId) &&
                              x.StaffId == column.StaffID &&
                              x.StudyStatus == "HasFinish" &&
                              ((x.CourseBeginTime >= item.EffectiveDate && x.CourseBeginTime <= item.ExpiryDate) || (x.CourseEndTime >= item.EffectiveDate && x.CourseEndTime <= item.ExpiryDate))
                              ).AnyAsync())
                        value = "★";
                item1.Add(new JProperty(key, value));
            }

            columns.Add(item1);
        }


        var list = titleSkills.result.DT_TableDataT1.Select(x => new
        {
            Code = x.DeptId + "_" + x.TitleId + "_" + x.RequiredElective,
            Name = x.DeptName + "（" + x.RequiredElective + ")",
            x.CourseId,
            x.CourseName,
            x.DeptId,
            x.DeptName,
            x.TitleId,
            x.TitleName,
            x.RequiredElective,
        }).ToList();

        var group = list.GroupBy(x => new { x.Name })
            .Select(x =>
            new
            {
                x.Key.Name,
                items = x.ToList()

            }).ToList();
        group = group.OrderBy(x => x.Name).ToList();

        var demoColumn = module.Data.JM_TableColumnT1.TableColumn.Where(x => x.field == "Demo").FirstOrDefault();
        group.ForEach(x =>
        {
            x.items.ForEach(item =>
            {
                var field = item.CourseId + "_" + item.DeptId + "_" + item.RequiredElective;
                if (!module.Data.JM_TableColumnT1.TableColumn.Where(x => x.field == field).Any())
                {

                    var column1 = Mapper.Map(demoColumn).ToANew<TableColumn>();
                    column1.field = field;
                    column1.label = item.CourseName;
                    column1.GroupName = x.Name;
                    module.Data.JM_TableColumnT1.TableColumn.Add(column1);
                }
            });

        });
        module.Data.JM_TableColumnT1.TableColumn = module.Data.JM_TableColumnT1.TableColumn.Where(x => x.field != "Demo").ToList();


        result.JM_TableColumnT1 = module.Data.JM_TableColumnT1;
        result.DT_TablePageInfoT1 = result2.result.DT_TablePageInfoT1;
        result.DT_TableDataT1 = columns;
        obj.result = result;
        obj.Success = true;
        obj.code = "0";
        obj.type = "success";
        obj.message = "查询成功！";
        return obj;
    }
}