SMP.Service/2.Data/SMP.Data/Finance/IFinance_ReconcileDataRepository.cs

using SMP.Model.Finance;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Core.Standard.Data.Infrastructure;
using Dapper;
using SMP.Model.Dto.Reconcile;
using XYY.Common.Standard;
using SMP.Model.Enums;

namespace SMP.Data.Finance
{
    public interface IFinance_ReconcileDataRepository : IBaseRepository<Finance_ReconcileData>
    {
        Task<int> InsertConcileData(string billName, int mappingSolutionId);
        Task<int> RemoveReconcileData(string billName, int mappingSolutionId);
        Task<int> RefreshConcileData(string billName, int mappingSolutionId);

        Task<IEnumerable<ReconcileSumAmount>> GetSumAmount(string billName, int reconcileSolutionId);
        Task<PageResult<Finance_ReconcileData>> QueryServiceBillExpensePage(string billName, int mappingSolutionId, ReconcileDiffType diffType, decimal diffAccurate, List<string> numbers, int pageIndex, int pageLength);
    }
    public class Finance_ReconcileDataRepository : BaseRepository<Finance_ReconcileData>, IFinance_ReconcileDataRepository
    {
        public Finance_ReconcileDataRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        {

        }

        public Task<IEnumerable<ReconcileSumAmount>> GetSumAmount(string billName, int mappingSolutionId)
        {
            string sql = "select BillDataName,sum(isnull(BillData,0)) as BillSumAmount,SystemDataName as ExpenseName,sum(SystemData) as ExpenseSumAmount from Finance_ReconcileData where BillName=@BillName and MappingSolutionId=@MappingSolutionId group by BillDataName,SystemDataName";
            return _unitOfWork.Connection.QueryAsync<ReconcileSumAmount>(sql, new { BillName = billName, MappingSolutionId = mappingSolutionId }, transaction: _unitOfWork.Transaction);


        }
        public Task<int> RemoveReconcileData(string billName, int mappingSolutionId)
        {
            string sql = "delete from Finance_ReconcileData where BillName=@BillName and MappingSolutionId=@MappingSolutionId";
            return _unitOfWork.Connection.ExecuteAsync(sql, new { BillName = billName, MappingSolutionId = mappingSolutionId }, transaction: _unitOfWork.Transaction);

        }

        public Task<int> InsertConcileData(string billName, int mappingSolutionId)
        {
            string sql = @"insert into Finance_ReconcileData(BillName,MappingSolutionId,Number,BillDataName,BillData,ExpenseId,SystemDataName ,SystemData,Sort,Diff,CreateTime,Version)
select BillName,MappingSolutionId,BillNumber as Number,BillDataName,BillData,ExpenseId, ExpenseName as SystemDataName,AmountinOriginalCurrency as  SystemData,Sort,(isnull(BillData,0) - isnull(AmountinOriginalCurrency,0)) as Diff,getdate() as CreateTime,'2022-05-17' as Version
from View_ServiceBillOrderExpense
where BillName=@BillName and MappingSolutionId=@MappingSolutionId
union
select BillName,MappingSolutionId,Number,BillDataName,BillData,ExpenseId,ExpenseName as SystemDataName,SumOriginAmount as  SystemData,Sort,(isnull(BillData,0) - isnull(SumOriginAmount,0)) as Diff,getdate() as CreateTime,'2022-05-17' as Version 
from View_ServiceBillLadingBillExpense
where BillName=@BillName and MappingSolutionId=@MappingSolutionId and ExpenseName is not null";

            return _unitOfWork.Connection.ExecuteAsync(sql, new { BillName = billName, MappingSolutionId = mappingSolutionId }, transaction: _unitOfWork.Transaction);
        }

        public async Task<PageResult<Finance_ReconcileData>> QueryServiceBillExpensePage(string billName, int mappingSolutionId, ReconcileDiffType diffType, decimal diffAccurate, List<string> numbers, int pageIndex, int pageLength)
        {

            PageResult<Finance_ReconcileData> pageResult = new PageResult<Finance_ReconcileData>();
            pageResult.PageIndex = pageIndex;
            pageResult.PageSize = pageLength;

            string diffCondition = null;
            if (diffType == ReconcileDiffType.无差异)
            {
                diffCondition = " and abs(Diff) < @DiffAccurate ";
            }
            else if (diffType == ReconcileDiffType.有差异)
            {
                diffCondition = " and abs(Diff) >= @DiffAccurate ";
            }
            else if (diffType == ReconcileDiffType.仅正差异)
            {
                diffCondition = " and Diff >= @DiffAccurate ";
            }
            else if (diffType == ReconcileDiffType.仅负差异)
            {
                diffCondition = " and Diff <= @DiffAccurate ";
            }
            if (numbers?.Any() == true)
            {
                string sql = $"select * from Finance_ReconcileData where BillName=@BillName and MappingSolutionId=@MappingSolutionId {diffCondition} and Number in @Numbers";
                var result = await base._unitOfWork.Connection.QueryAsync<Finance_ReconcileData>(sql, param: new { BillName = billName, MappingSolutionId = mappingSolutionId, Numbers = numbers, DiffAccurate = diffAccurate }, transaction: base._unitOfWork.Transaction);
                if (result?.Any() == true)
                {
                    pageResult.TotalCount = result.GroupBy(i => i.Number).Count();
                    pageResult.Result = result;
                    return pageResult;
                }
                else
                {
                    pageResult.TotalCount = 0;
                    pageResult.Result = new List<Finance_ReconcileData>();
                    return pageResult;
                }

            }
            else
            {
                if (pageIndex <= 0)
                    pageIndex = 1;
                if (pageLength <= 0)
                    pageLength = 20;

                if (pageLength >= 2000)
                    throw new Exception("最多支持2000条");

                int start = (pageIndex - 1) * pageLength;
                int stop = pageIndex * pageLength;
                string numberSql = @"with t as(
select ROW_NUMBER() over( order by number) rid, number from Finance_ServiceBillResolveResult where BillName=@BillName  group by number )
select number from t
where rid>=@Start and rid<=@Stop";

                var sortedNumbers = await base._unitOfWork.Connection.QueryAsync<string>(numberSql, param: new { BillName = billName, Start = start, Stop = stop }, transaction: base._unitOfWork.Transaction);

                if (sortedNumbers?.Any() == true)
                {
                    string sql = $"select * from Finance_ReconcileData where BillName=@BillName and MappingSolutionId=@MappingSolutionId {diffCondition} and Number in @Numbers";
                    var result = await base._unitOfWork.Connection.QueryAsync<Finance_ReconcileData>(sql, param: new { BillName = billName, Numbers = sortedNumbers, MappingSolutionId = mappingSolutionId, DiffAccurate = diffAccurate }, transaction: base._unitOfWork.Transaction);

                    string countSql = @$"with t as(
select Number from Finance_ReconcileData where BillName=@BillName {diffCondition} group by Number
)
select count(1) from t";
                    var totalCount = await base._unitOfWork.Connection.ExecuteScalarAsync<int>(countSql, param: new { BillName = billName, MappingSolutionId = mappingSolutionId, DiffAccurate = diffAccurate }, transaction: base._unitOfWork.Transaction);

                    pageResult.TotalCount = totalCount;
                    pageResult.Result = result;
                    return pageResult;
                }
                else
                {
                    pageResult.TotalCount = 0;
                    pageResult.Result = new List<Finance_ReconcileData>();
                    return pageResult;
                }
            }

        }

        public async Task<int> RefreshConcileData(string billName, int mappingSolutionId)
        {
            await RemoveReconcileData(billName, mappingSolutionId);
            int count = await InsertConcileData(billName, mappingSolutionId);
            return count;
        }
    }



}