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

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

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


        public DelegationVisaViewList GetDelegationList(IOS_VisaDto _dto)
        {
            string sqlWhere = string.Format(" Where IsDel=0 ");

            int currPIndex = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) * _dto.PageSize) + 1;
            int currPSize = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) + 1) * _dto.PageSize;

            ChangeDataBase(DBEnum.OA2014DB);

            string sql = string.Format(@" Select * From(Select ROW_NUMBER() Over(order By Id desc) as RowNumber, Id as DiId,
TeamName,ClientUnit,ClientName,TeamLev,VisitDate,VisitDays,VisitPNumber
From DelegationInfo With(Nolock) {2}
) as tb Where tb.RowNumber Between {0} And {1} ", currPIndex, currPSize, sqlWhere);
            DelegationVisaViewList rst = new DelegationVisaViewList();
            rst.CurrPageIndex = currPIndex;
            rst.CurrPageSize = currPSize;
            List<Grp_DelegationVisaView> dataSource = _sqlSugar.SqlQueryable<Grp_DelegationVisaView>(sql).ToList();

            foreach (var item in dataSource)
            {
                string sql2 = string.Format(@" Select * From Grp_VisaProgressCustomer With(Nolock) Where DiId={0} And IsDel=0 ", item.DiId);
                List<Grp_VisaProgressCustomer> listComplete = _sqlSugar.SqlQueryable<Grp_VisaProgressCustomer>(sql2).ToList();

                item.CompletePNumber = string.Format(@"已完成{0}人", listComplete.Count);
            }
            rst.DataList = new List<Grp_DelegationVisaView>(dataSource);

            if (rst.DataList.Count > 0)
            {
                string sqlCount = string.Format(@" Select Id as DiId From DelegationInfo With(Nolock) {0} ", sqlWhere);
                int dataCount = _sqlSugar.SqlQueryable<Grp_DelegationVisaView>(sqlCount).Count();
                rst.DataCount = dataCount;
            }

            return rst;
        }
    }
}