OA2023/OASystem/OASystem.Infrastructure/Repositories/Groups/GrpScheduleRepository.cs

using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Groups;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class GrpScheduleRepository : BaseRepository<Grp_ScheduleInfo, Grp_ScheduleInfo>
    {
        public GrpScheduleRepository(SqlSugarClient sqlSugar) : base(sqlSugar)
        {
        }

        public string SetSql_GrpScheduleDto(Grp_ScheduleDto _dto)
        {
            string sqlWhere = string.Empty;

            if (_dto.SysUserId > 0)
            {
                sqlWhere += string.Format(@" And s.Id in ( Select Id From Grp_SchedulePerson With(Nolock) Where SysUserId = '{0}' ) ", _dto.SysUserId);
            }

            if (_dto.DiId > 0)
            {
                sqlWhere = string.Format(@" And s.DiId = '{0}' ", _dto.DiId);
            }

            //请放在最后
            if (_dto.ScheduleId > 0)
            {
                sqlWhere = string.Format(@" And s.Id = '{0}' ", _dto.ScheduleId);
            }

            if (!string.IsNullOrEmpty(sqlWhere.Trim()))
            {
                Regex r = new Regex("And");
                sqlWhere = r.Replace(sqlWhere, "Where", 1);
            }

            string sql = string.Format(@" Select s.Id,s.DiId,di.TeamName as DeleName,s.PrimaryStep,s.ExpectBeginDt,s.ExpectEndDt,s.Leader,s.Exception,u.CnName as LeaderName
            From Grp_Schedule as s With(Nolock) Inner Join Grp_DelegationInfo as di With(Nolock) On s.DiId=di.Id
            Inner Join Sys_Users as u With(Nolock) On s.Leader=u.Id
            {0} ", sqlWhere);

            return sql;
        }

        #region 获取添加后的新数据
        public async Task<Grp_ScheduleDetailView> GetInsertBackData(int detailId)
        {
            Grp_ScheduleDetailInfo item = await _sqlSugar.Queryable<Grp_ScheduleDetailInfo>().FirstAsync(s => s.Id == detailId);
            if (item != null)
            {
                Grp_ScheduleDetailView temp = new Grp_ScheduleDetailView();
                temp.Duty = item.Duty;
                temp.Exception = item.Exception;
                temp.ExpectBeginDt = item.ExpectBeginDt;
                temp.ExpectEndDt = item.ExpectEndDt;
                temp.DetailId = item.Id;
                temp.JobContent = item.JobContent;
                temp.Level = item.SLevel;
                temp.RealEndDt = item.RealEndDt;
                temp.Remark = item.Remark;
                temp.Root = item.ParentStep;
                temp.Step = item.Step;
                temp.StepStatus = item.StepStatus;

                return temp;
            }

            return null;
        }
        #endregion

        /// <summary>
        /// 获取团组流程数据单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_dto"></param>
        /// <returns></returns>
        public async Task<Grp_ScheduleCombinView> GetView_GrpSchedule(Grp_ScheduleDto _dto)
        {
            Grp_ScheduleCombinView _view = new Grp_ScheduleCombinView();

            #region 主流程
            string sql = this.SetSql_GrpScheduleDto(_dto);
            Grp_ScheduleView primary = await _sqlSugar.SqlQueryable<Grp_ScheduleView>(sql).FirstAsync();
            #endregion
            _view.Primary = primary;

            #region 主流程节点

            List<Grp_ScheduleRootView> rootList = new List<Grp_ScheduleRootView>()
            {
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Wait },
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Confirm},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Budget},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Feedback},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Puote},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Visa},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Business},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.CostAudit},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Pay},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Training},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.DropOff},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.PickUp},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Collect},
                  new Grp_ScheduleRootView() { Root = (int)GrpSchedulePrimaryStepEnum.Finish}
            };


            #endregion

            #region 子节点

            //子节点
            List<Grp_ScheduleDetailInfo> detailEntityList = _sqlSugar.Queryable<Grp_ScheduleDetailInfo>().Where(s => s.IsDel != 1 && s.ScheduleId == _dto.ScheduleId).ToList();
            List<Grp_ScheduleDetailView> childList = new List<Grp_ScheduleDetailView>();

            //区分2级节点 3级节点
            List<Grp_ScheduleDetailInfo> LV2List = detailEntityList.Where(s => s.SLevel == 2).ToList();
            List<Grp_ScheduleDetailInfo> LV3List = detailEntityList.Where(s => s.SLevel == 3).ToList();
            foreach (var item in LV2List)
            {
                Grp_ScheduleDetailView temp = new Grp_ScheduleDetailView();
                temp.Duty = item.Duty;
                temp.Exception = item.Exception;
                temp.ExpectBeginDt = item.ExpectBeginDt;
                temp.ExpectEndDt = item.ExpectEndDt;
                temp.DetailId = item.Id;
                temp.JobContent = item.JobContent;
                temp.Level = item.SLevel;
                temp.RealEndDt = item.RealEndDt;
                temp.Remark = item.Remark;
                temp.Root = item.ParentStep;
                temp.Step = item.Step;
                temp.StepStatus = item.StepStatus;

                List<Grp_ScheduleDetailInfo> tempLv3List = LV3List.Where(s => s.ParentStep == item.Step).ToList();
                List<Grp_ScheduleDetailView> tempChildList = new List<Grp_ScheduleDetailView>();
                if (tempLv3List.Count > 0)
                {
                    foreach (var item2 in tempLv3List)
                    {
                        Grp_ScheduleDetailView temp2 = new Grp_ScheduleDetailView();
                        temp2.Duty = item2.Duty;
                        temp2.Exception = item2.Exception;
                        temp2.ExpectBeginDt = item2.ExpectBeginDt;
                        temp2.ExpectEndDt = item2.ExpectEndDt;
                        temp2.DetailId = item2.Id;
                        temp2.JobContent = item2.JobContent;
                        temp2.Level = item2.SLevel;
                        temp2.RealEndDt = item2.RealEndDt;
                        temp2.Remark = item2.Remark;
                        temp2.Root = item2.ParentStep;
                        temp2.Step = item2.Step;
                        temp2.StepStatus = item2.StepStatus;
                        tempChildList.Add(temp2);
                    }
                }
                temp.ChildList = new List<Grp_ScheduleDetailView>(tempChildList);


                childList.Add(temp);
            }
            #endregion
            foreach (var item in rootList)
            {
                item.ChildList = new List<Grp_ScheduleDetailView>(childList.Where(s => s.Root == item.Root).ToList());
            }

            _view.RootList = new List<Grp_ScheduleRootView>(rootList);

            #region 流程人员
            List<Grp_SchedulePersonInfo> personEntityList = _sqlSugar.Queryable<Grp_SchedulePersonInfo>().Where(s => s.IsDel != 1 && s.ScheduleId == _dto.ScheduleId).ToList();
            List<Grp_SchedulePersonView> personList = new List<Grp_SchedulePersonView>();
            foreach (var item in personEntityList)
            {
                Grp_SchedulePersonView temp = new Grp_SchedulePersonView();
                temp.JobStr = item.JobStr;
                temp.PersonId = item.Id;
                temp.PersonRemark = item.Remark;
                temp.SysUserId = item.SysUserId;
                temp.SysUserName = item.SysUserName;
                personList.Add(temp);
            }
            #endregion
            _view.PersonList = new List<Grp_SchedulePersonView>(personList);

            return _view;
        }

        /// <summary>
        /// 获取团组流程（列表分页）
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        public async Task<List<Grp_ScheduleView>> GetViewList_GrpSchedule(Grp_ScheduleDto _dto)
        {
            List<Grp_ScheduleView> _viewList = new List<Grp_ScheduleView>();
            string sqlInner = this.SetSql_GrpScheduleDto(_dto);
            string sql = string.Format(@" Select * From(
Select * From ( Select ROW_NUMBER() Over(order By Id desc) as RowNumber,* From ( {2} )
as a ) as tb Where tb.RowNumber Between {0} And {1} ",
(((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) * _dto.PageSize) + 1,
(((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) + 1) * _dto.PageSize,
sqlInner);
            _viewList = await _sqlSugar.SqlQueryable<Grp_ScheduleView>(sql).ToListAsync();
            return _viewList;
        }

        public void bulkInsert<T>(List<T> list) where T : class, new()
        {
            _sqlSugar.Insertable(list).UseParameter().ExecuteCommand();
        }

        public async Task<List<Grp_SchedulePersonInfo>> GetEntityList_SchedulePersonBySql(string sql)
        {
            List<Grp_SchedulePersonInfo> _list = new List<Grp_SchedulePersonInfo>();
            _list = await _sqlSugar.SqlQueryable<Grp_SchedulePersonInfo>(sql).ToListAsync();
            return _list;
        }

    }
}