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.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;
        }

        /// <summary>
        /// 获取团组流程数据单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_dto"></param>
        /// <returns></returns>
        public async Task<Grp_ScheduleView> GetView_GrpSchedule(Grp_ScheduleDto _dto)
        {
            Grp_ScheduleView _view = null;
            string sql = this.SetSql_GrpScheduleDto(_dto);
            _view = await _sqlSugar.SqlQueryable<Grp_ScheduleView>(sql).FirstAsync();
            return _view;
        }

        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 ROW_NUMBER() Over(order By Id desc) as RowNumber,* From ( {2} ) ) 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;
        }



    }
}