XYY.Core/2.Data/XYY.Data.Strandard/DingTalk/IDingTalk_ReimbursementRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.DingTalk;
using Dapper;
using XYY.Common.Standard;

namespace XYY.Data.Standard.DingTalk
{
    public interface IDingTalk_ReimbursementRepository : IBaseRepository<DingTalk_Reimbursement>
    {
        Task insertDetail(int ReimbursementId, List<DingTalk_ReimbursementDetail> details);
        Task<List<DingTalk_ReimbursementOptions>> GetAllReimbursementOptions();
        Task<bool> BatchUpdateOptionsEntiy(List<DingTalk_ReimbursementOptions> models);
        Task insertOptions(List<DingTalk_ReimbursementOptions> models);
        Task<PageResult<DingTalk_Reimbursement>> QueryPage(QueryModel qm);
        Task<List<DingTalk_ReimbursementOptions>> GetOptionsByType(int OptionType);
        Task<List<DingTalk_ReimbursementDetail>> GetReimbursementDetails(int ReimbursementId);
    }
    public class DingTalk_ReimbursementRepository : BaseRepository<DingTalk_Reimbursement>, IDingTalk_ReimbursementRepository
    {
        public DingTalk_ReimbursementRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        {
        }
        public async Task insertDetail(int ReimbursementId, List<DingTalk_ReimbursementDetail> details)
        {
            if (details.Count != 0)
            {
                string deleteSql = $" delete DingTalk_ReimbursementDetail where ReimbursementId = {ReimbursementId} ";
                await _unitOfWork.ExecuteAsync(deleteSql);
                details.ForEach(x => { x.ReimbursementId = ReimbursementId; });
                await _unitOfWork.BulkToDBAsync(details);
            }
        }


        public async Task<List<DingTalk_ReimbursementOptions>> GetAllReimbursementOptions()
        {
            var result = await _unitOfWork.QueryAsync<DingTalk_ReimbursementOptions>();
            return result.ToList();
        }
        public async Task<bool> BatchUpdateOptionsEntiy(List<DingTalk_ReimbursementOptions> models)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = " update DingTalk_ReimbursementOptions set DName = @DName,LevelNum=@LevelNum,DParentId=@DParentId  where DId = @DId ";
                await _unitOfWork.ExecuteAsync(sql, models);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;


        }
        public async Task insertOptions(List<DingTalk_ReimbursementOptions> models)
        {
            await _unitOfWork.BulkToDBAsync(models);
        }
        public async Task<PageResult<DingTalk_Reimbursement>> QueryPage(QueryModel qm)
        {
            #region 基础sql
            int Option = 0;

            var qmCategories= qm.QueryParamer.Where(x => x.Filed == "Option").FirstOrDefault();
            if (qmCategories != null && !string.IsNullOrEmpty(qmCategories.Value)) { Option = Convert.ToInt32(qmCategories.Value.Split(',')[1]); qm.QueryParamer.Remove(qmCategories); }
            

            StringBuilder sb = new StringBuilder();
            sb.Append(" select * from DingTalk_Reimbursement(nolock) where 1=1 ");
            string where = _unitOfWork.GetWhere(qm.QueryParamer);
            if (Option > 0 )
            {
                where += $" and Id in (select distinct ReimbursementId from DingTalk_ReimbursementDetail where  Categories2_Id={Option}) ";
            }
            sb.Append(where);
            #endregion
            #region 分页
            StringBuilder pager = new StringBuilder();
            string sortString = " Id desc";
            pager.Append($@"select * from (select Row_Number()over(order by  {sortString})rowId,*
            from ({sb.ToString()})a where 1=1 ) T where rowId between {(qm.PageIndex - 1) * qm.PageSize + 1} and {qm.PageSize * qm.PageIndex};");
            pager.Append(@$"select count(0)TotalCount from (select Row_Number()over(order by  {sortString})rowId,*
            from({sb.ToString()})a where 1 = 1 ) T where 1=1");
            #endregion
            #region 取值
            string sql = pager.ToString();

            var dr = await _unitOfWork.Connection.QueryMultipleAsync(sql, null, _unitOfWork.Transaction);
            //需要注意读取顺序，依据语句执行顺序进行读取
            var pageResult = new PageResult<DingTalk_Reimbursement>()
            {
                Result = dr.Read<DingTalk_Reimbursement>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
                TotalCount = dr.Read<int>().FirstOrDefault()
            };
            #endregion
            return pageResult;
        }
        public async Task<List<DingTalk_ReimbursementOptions>> GetOptionsByType(int OptionType)
        {
            var result = await _unitOfWork.QueryAsync<DingTalk_ReimbursementOptions>(x => x.OptionType == (OptionTypeEnum)OptionType);
            return result.ToList();
        }
        public async Task<List<DingTalk_ReimbursementDetail>> GetReimbursementDetails(int ReimbursementId)
        {
            var result = await _unitOfWork.QueryAsync<DingTalk_ReimbursementDetail>(x => x.ReimbursementId == ReimbursementId);
            return result.ToList();
        }
    }

}