XYY.Core/2.Data/XYY.Data.Strandard/Finance/IFinance_CustomerDeliveryBillRepository.cs

using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Caching.Distributed;
using NPOI.SS.Formula.Functions;
using XYY.Common.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard;
using XYY.Model.Standard.Enums;
using XYY.Model.Standard.Finance;
using XYY.Model.Standard.Task;
using XYY.Service.Standard.Finance.Charging.Base;
using XYY.TaskTrack.Standard;

namespace XYY.Data.Standard.Finance
{
    public interface IFinance_CustomerDeliveryBillRepository : IBaseRepository<Finance_CustomerDeliveryBill>
    {
        Task<Finance_CustomerDeliveryBill> GetDeliveryBillById(int id);

        Task AddDeliveryBill(Finance_CustomerDeliveryBill bill, bool ss);

        Task UpdateCustomerDeliveryBillState(int id, BillState billState, bool notran);


        Task<IEnumerable<Finance_CustomerDeliveryBill>> AddDeliveryBillBatch(List<Finance_CustomerDeliveryBill> finance_CustomerDeliveryBills);

        Task<AddDeliveryBillResult> AddDeliveryBillAsFile(Finance_CustomerDeliveryBill bill, List<Finance_CustomerDeliveryBillDetailInput2> list);

        [NonTrans]
        Task InputSupplementOrDeduction(int billId, List<Finance_CustomerDeliveryBillDetailInput> details, int type, bool isClear = true);

        [NonTrans]
        Task InputClaim(int billId, List<Finance_CustomerDeliveryBillClaimInput> details);

        [NonTrans]
        Task UpdateConfirmed(int billId, List<Finance_CustomerDeliveryBillDetailInput> details);
        Task<List<Finance_CustomerDeliveryBillDetail>> GetDetail(int billId, int type, bool? confirmed);

        Task<List<Finance_CustomerDeliveryBill>> GetDataAsStatus(FiannceCustomerDeliveryBillParam param);
        Task<Dictionary<int, int>> GetGroupCounts();
        [NonTrans]
        Task Pass(int billId, bool isRealTime, bool fromDirectImport = false);

        Task Rereject(int billId);

        Task<List<Finance_Logistics_LadingBillLogDto>> GetPaymentLog(int billId);

        Task DeleteDetails(int billId);

        void BatchAddDetails(int billId, List<Finance_CustomerDeliveryBillDetail> list);
        [NonTrans]
        Task UpdatetWeightAndFee(List<Finance_CustomerDeliveryBillDetail> list, int taskQty, int batchQty, string guidId, int oriBillId);
        Task<PageResult<Finance_CustomerDeliveryBill>> GetRemittancePageResult(QueryModel queryModel);
        Task<IEnumerable<WaitCreateBillCustomer>> GetWaitCreateBill(int billType);

        Task<CustomerDeliveryPanel> GetCustomerDeliveryPanelAsync();
        Task<CustomerDeliveryPanelDetail> GetCustomerDeliveryPanelDetail();
        Task<List<CustomerRemittance>> GetRemittance(QueryModel queryModel);

        Tuple<decimal, decimal> GetUSDAndEURToCny(DateTime start, DateTime end);
        Task<IEnumerable<Finance_ChargesDetail>> GetChargesDetails(int customerId, DateTime start, DateTime end);
        Task<IEnumerable<OP_FirstBillInfo>> GetCustomerDeliveryBillByNew(IEnumerable<int> customerIds);
        /// <summary>
        /// 退件费用
        /// </summary>
        /// <param name="customerId">客户Id</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        Task<List<Finance_CustomerDeliveryBillDetail>> Load_Return_Fee(int customerId, DateTime start, DateTime end);

        Task<List<Finance_CustomerDeliveryBillDetail>> Get_Repeat_Fee(int billId);
    }

    public class Finance_CustomerDeliveryBillRepository : BaseRepository<Finance_CustomerDeliveryBill>, IFinance_CustomerDeliveryBillRepository
    {
        private readonly IDistributedCache _cache;
        private readonly IFinance_Customer_AccountRepository _accountRepository;
        public Finance_CustomerDeliveryBillRepository(IUnitOfWork unitOfWork, IDistributedCache cache, IFinance_Customer_AccountRepository accountRepository) : base(unitOfWork)
        {
            _cache = cache;
            _accountRepository = accountRepository;
        }



        public async Task<List<Finance_CustomerDeliveryBillDetail>> Load_Return_Fee(int customerId, DateTime start, DateTime end)
        {
            string sql = $@"select  b.*
	                            from Order_Return(nolock) a join Finance_CustomerDeliveryBillDetail(nolock)b on a.OrderId=b.OrderId
		                            and b.FeeType = 0
		                            join Order_Order(nolock)c on c.id=b.OrderId
		                            join User_Customer(nolock)d on d.id=c.CustomerId
		                            left join Finance_CustomerDeliveryBillDetail(nolock)e on e.OrderId = a.OrderId and e.FeeType = 9
			                            where c.CustomerId= {customerId}  and  a.createtime between '{start}' and '{end.AddDays(1)}' 
			                            and e.Id is null";
            return (await _unitOfWork.QueryBySqlAsync<Finance_CustomerDeliveryBillDetail>(sql)).ToList();
        }

        public async Task<AddDeliveryBillResult> AddDeliveryBillAsFile(Finance_CustomerDeliveryBill bill, List<Finance_CustomerDeliveryBillDetailInput2> list)
        {
            await _unitOfWork.ExecuteScalarAsync<Finance_CustomerDeliveryBillDetailInput>("delete Finance_CustomerDeliveryBillDetailInput2");
            await _unitOfWork.BulkToDBAsync(list);
            await _unitOfWork.ExecuteStoredProcedureAsync("createCustomerBillAsFile", new
            {
                BillName = bill.Name,
                CustomerId = bill.CustomerId,
                StartTime = bill.StartTime,
                EndTime = bill.EndTime,
                ServiceBillUrl = bill.ServiceBillUrl
            }, 180);
            return null;
        }

        [NonTrans]
        public async Task UpdateConfirmed(int billId, List<Finance_CustomerDeliveryBillDetailInput> details)
        {

            await _unitOfWork.ExecuteScalarAsync<Finance_CustomerDeliveryBillDetailInput>("delete Finance_CustomerDeliveryBillDetailInput where BillId=" + billId);
            details.ForEach(x => { x.BillId = billId; x.CreateTime = DateTime.Now; });
            await _unitOfWork.BulkToDBAsync(details);

            await _unitOfWork.ExecuteStoredProcedureAsync("UpdateCustomerConfirmed", new
            {
                billId = billId
            }, 180);

            await _unitOfWork.ExecuteStoredProcedureAsync("UpdateCustomerBill", new { billId = billId }, 180);
        }

        public async Task<List<Finance_CustomerDeliveryBillDetail>> GetDetail(int billId, int type, bool? confirmed)
        {
            string sql = @" select * from Finance_CustomerDeliveryBillDetail(nolock) where [FeeType]=" + type + " and  billId= " + billId;
            if (confirmed.HasValue)
                sql += " and confirmed = " + (confirmed.Value ? 1 : 0);
            var t = await _unitOfWork.QueryBySqlAsync<Finance_CustomerDeliveryBillDetail>(sql);
            return t.ToList();
        }


        public async Task<List<Finance_CustomerDeliveryBill>> GetDataAsStatus(FiannceCustomerDeliveryBillParam param)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append($@"select b.BillingCycle,a.* from Finance_CustomerDeliveryBill(nolock)a join User_Customer(nolock)b on a.CustomerId=b.Id where b.Type = 0 and Status=" + param.status + " and StartTime>='2021-10-01'");
            if (0 != (int)param.BillingCycle)
                sqlBuilder.Append($@" and b.BillingCycle = " + (int)param.BillingCycle + "");
            if (0 != param.customerId)
                sqlBuilder.Append($@" and b.Id =  " + param.customerId + "");
            if (param.IsInterceptCustomers.HasValue)
                sqlBuilder.Append($@" and b.IsInterceptCustomers = " + (param.IsInterceptCustomers.Value ? 1 : 0));
            sqlBuilder.Append(" order by a.CreateTime desc");
            var t = await _unitOfWork.QueryBySqlAsync<Finance_CustomerDeliveryBill>(sqlBuilder.ToString());
            return t.ToList();
        }

        public async Task<Dictionary<int, int>> GetGroupCounts()
        {
            string sql = @"select Status as [Key],count(0) as [Value] from Finance_CustomerDeliveryBill(nolock)a  join User_Customer(nolock)b on a.CustomerId=b.Id where b.Type = 0 and StartTime>='2021-10-01'  group by Status";
            var t = await _unitOfWork.QueryBySqlAsync<dynamic>(sql);
            return t.ToDictionary(x => (int)x.Key, x => (int)x.Value);
        }



        [NonTrans]
        public async Task InputSupplementOrDeduction(int billId, List<Finance_CustomerDeliveryBillDetailInput> details, int type, bool isClear = true)
        {
            if (isClear)
                await _unitOfWork.ExecuteScalarAsync<Finance_CustomerDeliveryBillDetailInput>("delete Finance_CustomerDeliveryBillDetailInput where BillId=" + billId);
            details.ForEach(x => { x.BillId = billId; x.CreateTime = DateTime.Now; });
            if (details.Count > 0)
                await _unitOfWork.BulkToDBAsync(details);

            await _unitOfWork.ExecuteStoredProcedureAsync("InputCustomerSupplementOrDeduction", new
            {
                billId = billId,
                type = type
            });

            await _unitOfWork.ExecuteStoredProcedureAsync("updateCustomerBill", new { billId = billId });
        }

        [NonTrans]
        public async Task InputClaim(int billId, List<Finance_CustomerDeliveryBillClaimInput> details)
        {

            await _unitOfWork.ExecuteScalarAsync<Finance_CustomerDeliveryBillClaimInput>("delete Finance_CustomerDeliveryBillClaimInput where BillId=" + billId);
            details.ForEach(x => { x.BillId = billId; x.CreateTime = DateTime.Now; });
            await _unitOfWork.BulkToDBAsync(details);

            await _unitOfWork.ExecuteStoredProcedureAsync("InputCustomerClaim", new
            {
                billId = billId
            });

            await _unitOfWork.ExecuteStoredProcedureAsync("updateCustomerBill", new { billId = billId });
        }

        public async Task<CustomerDeliveryPanel> GetCustomerDeliveryPanelAsync()
        {
            string sql =
                @"
                    --当天未核对帐单
                    select * from (
                    select count(0) as TodayNotPassQty from Finance_CustomerDeliveryBill(nolock)
                     where Status=0 and CreateTime 
	                    between convert(nvarchar(10), GETDATE(),120) and convert(nvarchar(10),DATEADD( DAY,1,getdate()),120))t1
                    left join
                    --总计未核对帐单
                    (select count(0) as NotPassQty from Finance_CustomerDeliveryBill(nolock)
                     where Status=0 and StartTime>='2022-01-01')t2 on 1=1
                     left join
                     --当前欠款帐单总额
                     (select count(0)ArrearsQty,SUM(Finance_CustomerDeliveryBill.Receivables-Payment) ArrearsTotal
                     from Finance_CustomerDeliveryBill(nolock)
                     where Status=3 and StartTime>='2022-01-01')t3 on 1=1
                     left join
                     --当前逾期欠款帐单 
                     (select count(0)OverdueQty,SUM(Finance_CustomerDeliveryBill.Receivables-Payment) OverdueTotal,AVG(CurrentOverdueDays) CurrentOverdueDay
                     from Finance_CustomerDeliveryBill(nolock)
                     where Status=3 and StartTime>='2022-01-01' and CurrentOverdueDays>0)t5 on 1=1
                     left join(
                     --当前收款完成帐单总额
                     select count(0)PaymentQty,SUM(Payment) PaymentTotalc
                     from Finance_CustomerDeliveryBill(nolock)
                     where (Status=5 or Status=4) and StartTime>='2022-01-01')t6 on 1=1
                ";
            return await _unitOfWork.Connection.QuerySingleAsync<CustomerDeliveryPanel>(sql, null, _unitOfWork.Transaction);
        }

        public async Task AddDeliveryBill(Finance_CustomerDeliveryBill bill, bool ss)
        {
            int billId = bill.Id;
            if (billId <= 0)
            {
                billId = (int)(await _unitOfWork.InsertAsync(bill));
                bill.Id = billId;
            }

            if (ss)
                await _unitOfWork.ExecuteStoredProcedureAsync("createCustomerBillAsCharges", new { billId = billId }, 120);
            else
                await _unitOfWork.ExecuteStoredProcedureAsync("createCustomerBill", new { billId = billId }, 120);

            await _unitOfWork.ExecuteStoredProcedureAsync("updateCustomerBill", new { billId = billId }, 120);
        }
        [NonTrans]
        public async Task Pass(int billId, bool isRealTime = false, bool fromDirectImport = false)
        {
            var model = await GetAsync(billId);
            if (model.Status == FiannceServiceBillStatus.结算中)
                throw new Exception("帐单已完结,无须再审核");
            model.Status = (FiannceServiceBillStatus)(model.Status.GetValue() + 1);
            if (model.Status == FiannceServiceBillStatus.财务确认)
            {
                if (fromDirectImport)
                {
                    model.Status = FiannceServiceBillStatus.结算中;

                }
                else
                {
                    model.Status = FiannceServiceBillStatus.最终确认;
                }

            }
            if (model.Status == FiannceServiceBillStatus.结算中)
            {
                //最终确认通过时，更新应付款金额
                await _unitOfWork.ExecuteStoredProcedureAsync("passCustomerConfirmed", new { billId = billId }, 120);
                var response = await _unitOfWork.Connection.ExecuteAsync("  exec UpdateCustomerBill @billid=" + billId, null, _unitOfWork.Transaction, 180);

                model = await GetAsync(billId);
                model.Status = FiannceServiceBillStatus.结算中;
                model.Receivables =
                    model.ConfirmedFreigh + model.Supplement - Math.Abs(model.Deduction) - Math.Abs(model.ClaimAmount.HasValue ? model.ClaimAmount.Value : 0);
                model.CustomerRevenue = Math.Abs(model.Deduction) + Math.Abs(model.ClaimAmount.HasValue ? model.ClaimAmount.Value : 0);
                model.CustomerExpenditure = model.ConfirmedFreigh + model.Supplement;

                //收入还款
                if (model.CustomerRevenue > 0)
                {
                    //查找当前客户有欠款的帐单
                    var bills = await _unitOfWork.QueryBySqlAsync<Finance_CustomerDeliveryBill>(
                        "select * from Finance_CustomerDeliveryBill(nolock) where CustomerId=@CustomerId and Status = @Status", null, new
                        {
                            CustomerId = model.CustomerId,
                            Status = FiannceServiceBillStatus.结算中
                        });
                    foreach (var item in bills)
                    {
                        if (item.AmountToBeRepaid < model.CustomerRevenue)
                        {
                            item.Status = FiannceServiceBillStatus.已还清;
                            item.AmountToBeRepaid = 0;
                            item.Repayment = item.Arrears;
                        }
                        else
                        {
                            item.AmountToBeRepaid -= model.CustomerRevenue;
                            item.Repayment += model.CustomerRevenue;
                        }
                        await _unitOfWork.UpdateAsync(item);
                    }
                }

                var account = (await _unitOfWork.QueryAsync<Finance_Customer_Account>(x => x.CustomerId == model.CustomerId)).FirstOrDefault();
                if (account == null)
                {
                    //为客户创建帐户
                    account = new Finance_Customer_Account() { CustomerId = model.CustomerId };
                    account.Id = (int)await _unitOfWork.InsertAsync<Finance_Customer_Account>(account);
                }
                //查找上期帐单
                var lastBill = (await QueryAsync(x => x.EndTime < model.StartTime && x.CustomerId == model.CustomerId)).OrderByDescending(x => x.EndTime)
                    .FirstOrDefault();
                if (lastBill == null)
                    lastBill = new Finance_CustomerDeliveryBill();
                //上期余额
                model.BalanceOfPreviousPeriod = lastBill.ClosingBalance;     //帐期内付款金额
                decimal? CurrentPeriod =
                    _unitOfWork.Connection.QuerySingle<decimal?>("select sum(PaymentCNY) from Finance_Customer_Remittance(nolock) " +
                    "where CustomerId=@CustomerId and  convert(nvarchar(10),PassDate,120) between @StartTime and @EndTime",
                    new { CustomerId = model.CustomerId, StartTime = model.StartTime, EndTime = model.EndTime }, _unitOfWork.Transaction);
                model.CurrentPeriod = CurrentPeriod ?? 0;
                //期末余额等于帐户余额减去本期应收
                if (!isRealTime)
                    model.ClosingBalance = account.Balance - model.Receivables;
                else
                    model.ClosingBalance
                        = model.BalanceOfPreviousPeriod + model.CurrentPeriod + model.CustomerRevenue - model.CustomerExpenditure;

                //期末余额大于0的设置待还款金额
                if (model.ClosingBalance < 0)
                {
                    model.AmountToBeRepaid = Math.Abs(model.ClosingBalance);
                    model.Arrears = Math.Abs(model.ClosingBalance);
                }

                if (account.RepaymentTermDay == 0)
                    throw new Exception("未找到当前客户的付款限制天数设置");
                else
                    model.LastRepaymentDate = model.EndTime.AddDays(account.RepaymentTermDay);
                if (!isRealTime)
                {
                    //更新帐户余额
                    account.Balance = account.Balance - model.Receivables;
                    if (account.Balance > 0)
                    {
                        //帐户还有余额的话更新为无欠款
                        model.Status = FiannceServiceBillStatus.无欠款;
                    }
                    else
                    {
                        model.Status = FiannceServiceBillStatus.结算中;
                    }
                    //int[] testCustomerIds = new int[] { 146, 221, 250, 362, 227 };
                    int[] testCustomerIds = new int[] { };
                    if (testCustomerIds.Contains(account.CustomerId))
                    {

                        //非实时计算帐单，在核帐后，判断是否有超额
                        if (account.Quota + account.BaseQuota + account.Balance < 0)
                        {
                            account.Arrears = true;
                            //超额时发送消息给李素
                            var customer = (await _unitOfWork.QueryAsync<User_Customer>(x => x.Id == account.CustomerId)).FirstOrDefault();
                            if (customer != null)
                            {
                                RestSharp.RestClient client = new RestSharp.RestClient("http://120.24.149.148:9505");
                                RestSharp.RestRequest request = new RestSharp.RestRequest("/WarningNotice/NoticeCard", RestSharp.Method.POST);
                                string noticeText = $"# 欠费通知，客户**{(string.IsNullOrEmpty(customer.Abbreviation) ? customer.CompanyName : customer.Abbreviation)}** 客户编码**{customer.CustomerNumber}**" +
                                $"  \n  当前余额：**{account.Balance.ToString("0.00")}**  \n  基础授信额度：**{account.BaseQuota.ToString("0.00")}**  \n  临时授信额度：**{account.Quota.ToString("0.00")}**  \n  可用余额：**{(account.Quota + account.BaseQuota + account.Balance).ToString("0.00")}**  \n  已产生**欠费**，仓库**停止收件**。  \n  请及时回款，或在 **钉钉OA审批** **财务** **额度调整** 中申请临时额度 避免**发货延误**。";
                                var body = new
                                {
                                    ServiceType = "钉钉",
                                    ButtonOrient = "0",
                                    Text = noticeText,
                                    Title = $"客户{(string.IsNullOrEmpty(customer.Abbreviation) ? customer.CompanyName : customer.Abbreviation)}欠费",
                                    ToAllUser = false,
                                    UserIds = new List<int>
                                    {
                                        200,
                                        1
                                    },
                                    Buttons = new List<dynamic>
                                    {
                                        new
                                        {
                                            Text = "已知悉",
                                            Link = "http://wms.xingyunyi.cn"
                                        }
                                    }
                                };
                                request.AddJsonBody(body);

                                var restResponse = client.Execute(request);
                                if (restResponse.StatusCode == System.Net.HttpStatusCode.OK)
                                {
                                    NotailResult notailResult = null;
                                    try
                                    {
                                        notailResult = Newtonsoft.Json.JsonConvert.DeserializeObject<NotailResult>(restResponse.Content);
                                    }
                                    catch (Exception ex)
                                    {
                                        throw new Exception("钉钉推送信息反解析失败，请联系立航" + ex.Message);
                                    }
                                    if (!notailResult.IsSuccess)
                                    {
                                        throw new Exception("钉钉推送信息api失败，请联系立航" + notailResult.ErrMsg);
                                    }
                                }
                                else
                                {
                                    throw new Exception("钉钉推送信息http失败，请联系立航" + restResponse.Content);
                                }
                            }

                        }
                    }
                    await _unitOfWork.UpdateAsync(account);


                }
                else
                {
                    if (model.ClosingBalance > 0)
                        model.Status = FiannceServiceBillStatus.无欠款;
                }
            }
            else
            {
                var response = await _unitOfWork.Connection.ExecuteAsync("  exec UpdateCustomerBill @billid=" + billId, null, _unitOfWork.Transaction, 180);
            }
            await _unitOfWork.UpdateAsync(model);

        }

        public class NotailResult
        {
            public bool IsSuccess
            {
                get; set;
            }
            public string ErrMsg
            {
                get; set;
            }
        }

        public async Task Rereject(int billId)
        {
            var model = await GetAsync(billId);
            if (model.Status == FiannceServiceBillStatus.待对帐)
                throw new Exception("帐单刚开始对帐，无法驳回");
            if (model.Status == FiannceServiceBillStatus.最终确认)
                model.Status = FiannceServiceBillStatus.财务确认;
            model.Status = (FiannceServiceBillStatus)(model.Status.GetValue() - 1);
            await _unitOfWork.UpdateAsync(model);

        }

        public async Task<List<Finance_Logistics_LadingBillLogDto>> GetPaymentLog(int billId)
        {
            var list = (await _unitOfWork.QueryAsync<Finance_CustomerDeliveryBillLog>(x => x.Id == billId)).ToList();

            List<Finance_Logistics_LadingBillLogDto> dtos = new List<Finance_Logistics_LadingBillLogDto>();
            foreach (var item in list)
            {
                dtos.Add(new Finance_Logistics_LadingBillLogDto
                {
                    OldPayment = item.OldValue,
                    NewPayment = item.NewValue,
                    Payment = (decimal.Parse(item.NewValue) - decimal.Parse(item.OldValue)).ToString(),
                    CreateTime = item.CreateTime.Value
                });
            }
            return dtos;
        }

        public async Task DeleteDetails(int billId)
        {
            await _unitOfWork.ExecuteScalarAsync<object>($"delete Finance_CustomerDeliveryBillDetail  where billId={billId}");
        }




        /// <summary>
        /// 批量更新数据
        /// </summary>
        /// <param name="list">导入重量列表</param>
        /// <param name="taskQty">并发数</param>
        /// <param name="batchQty">并发每次执行记录数</param>
        /// <param name="guidId">任务的GUID，方便对象跟踪</param>
        /// <returns></returns>
        [NonTrans]
        public async Task UpdatetWeightAndFee(List<Finance_CustomerDeliveryBillDetail> list, int taskQty, int batchQty, string guidId, int oriBillId)
        {
            if (this._unitOfWork.Connection.State == System.Data.ConnectionState.Closed)
                this._unitOfWork.Connection.Open();
            try
            {
                await _unitOfWork.ExecuteAsync($"delete Finance_CustomerDeliveryBillDetailInput where BillId={oriBillId}");
                list = list.Where(x => x.Weight != x.SysWeight).ToList();
                var inputs = list.Select(x => new Finance_CustomerDeliveryBillDetailInput
                {
                    BillId = oriBillId,
                    Weight = x.Weight,
                    CreateTime = DateTime.Now,
                    TrackingNumber = x.TrackingNumber,
                    Remark = x.Remark
                }
                          ).ToList();
                await _unitOfWork.BulkToDBAsync(inputs);
                TaskTrackModel taskTrackModel = new TaskTrackModel();
                taskTrackModel.Summary = list.Count;
                taskTrackModel.WaitQty = list.Count;
                taskTrackModel.Name = guidId;
                taskTrackModel.StartTime = DateTime.Now;
                taskTrackModel.EndTime = DateTime.Now;
                int taskId = (int)(await _unitOfWork.InsertAsync(taskTrackModel));
                ConcurrentBag<List<Finance_CustomerDeliveryBillDetail>> waitList = new ConcurrentBag<List<Finance_CustomerDeliveryBillDetail>>();                ///取大概有多少个任务
                int batchCounts = (int)Math.Ceiling(list.Count / (decimal)batchQty);
                for (int i = 0; i < batchCounts; i++)
                {
                    waitList.Add(list.Skip(i * batchQty).Take(batchQty).ToList());
                }
                List<Task> taskList = new List<Task>();
                int q = 0;
                //生成队列任务
                for (var i = 0; i < taskQty; i++)
                {
                    taskList.Add(Task.Run(async () =>
                  {
                      while (waitList.TryTake(out var data))
                      {

                          await _unitOfWork.ExecuteStoredProcedureAsync("UpdateCustomerConfirmedAsWeight", new
                          {
                              billId = oriBillId,
                              index = q,
                              size = batchQty
                          }, 3600);
                          q++;
                          //排他锁Update 数据库
                          await _unitOfWork.ExecuteAsync($"update taskTrackModel set SuccessQty += {batchQty} ,WaitQty -= {batchQty} where id = {taskId}");
                      }
                  }));
                }
                Task.WaitAll(taskList.ToArray());

                await _unitOfWork.ExecuteAsync($"update Finance_CustomerDeliveryBillDetail set Confirmed =1 ,Amount= SysAmount,Weight=SysWeight where BillId={oriBillId} and Weight =0 ");
                await _unitOfWork.ExecuteStoredProcedureAsync("UpdateCustomerBill", new { billId = oriBillId }, 3600);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this._unitOfWork.Connection.State == System.Data.ConnectionState.Open)
                    this._unitOfWork.Connection.Close();
            }
        }

        public void BatchAddDetails(int billId, List<Finance_CustomerDeliveryBillDetail> list)
        {

            _unitOfWork.InsertBatch<Finance_CustomerDeliveryBillDetail>(list);
        }

        public async Task<Finance_CustomerDeliveryBill> GetDeliveryBillById(int id)
        {
            var result = await _unitOfWork.QueryAsync<Finance_CustomerDeliveryBill>(i => i.Id == id);
            return result.FirstOrDefault();
        }

        public async Task<PageResult<Finance_CustomerDeliveryBill>> GetRemittancePageResult(QueryModel queryModel)
        {
            return await _unitOfWork.GetPagingListAsync<Finance_CustomerDeliveryBill>(queryModel);
        }

        public async Task<IEnumerable<WaitCreateBillCustomer>> GetWaitCreateBill(int billType)
        {
            DateTime endTime = DateTime.Now;
            DateTime endDay = new DateTime(endTime.Year, endTime.Month, endTime.Day);
            int year = endDay.Year;
            int month = endDay.Month;
            int day = endDay.Day;
            DateTime startTime = endTime;
            if (billType == 2)
            {
                //日结
                startTime = endDay.AddDays(-1);
            }
            else if (billType == 3)
            {
                //周结
                startTime = endDay.AddDays(-7);
            }
            else if (billType == 4)
            {
                //半月结
                //如果是1号的话，出16号至月尾
                if (endDay.Day == 1)
                {
                    if (endDay.Month == 1)
                    {
                        //1月份出12月份的并且年份减1
                        year--;
                        month = 12;
                    }
                    else
                    {

                        month--;
                    }
                    day = 16;
                    startTime = new DateTime(year, month, day);
                }
                else if (endDay.Day == 16)
                {
                    //16号的话，我们出1-15号的帐单
                    startTime = new DateTime(year, month, 1);
                }
                //startTime = endDay.AddDays(-15);
            }
            else if (billType == 5)
            {
                //月结算上个月帐单
                startTime = endDay.AddMonths(-1);
            }
            //endDay = endDay.AddDays(-1);

            /*有帐单数据的*/
            string sql = $@"select isnull( a.Abbreviation,a.CompanyName) as CustomerName, a.Id as CustomerId,'{startTime.ToString_yyyyMMdd()}' [start],'{endDay.AddDays(-1).ToString_yyyyMMdd()}' [end] from User_Customer(nolock)a
                            join
                             Order_Order(nolock)b on a.id=b.CustomerId
                            where b.ReceiveTime between @start and @end
	                            and BillingCycle = @BillingCycle
	                            group by a.Id,a.CompanyName,a.Abbreviation 
                            union
	                        select isnull( a.Abbreviation,a.CompanyName) as CustomerName, a.Id as CustomerId,'{startTime.ToString_yyyyMMdd()}' [start],'{endDay.AddDays(-1).ToString_yyyyMMdd()}' [end] from User_Customer(nolock)a 
                                    join Finance_Customer_Supplement(nolock)b on a.Id = b.CustomerId
                                where b.createtime between @start and @end 
                                    and BillingCycle = @BillingCycle
                                    group by a.Id,a.CompanyName,a.Abbreviation 
                            union
							   select  isnull( d.Abbreviation,d.CompanyName) as CustomerName, d.Id as CustomerId,'{startTime.ToString_yyyyMMdd()}' [start],'{endDay.AddDays(-1).ToString_yyyyMMdd()}' [end]
	                            from Order_Return(nolock) a join Finance_CustomerDeliveryBillDetail(nolock)b on a.OrderId=b.OrderId
		                            and b.FeeType = 0
		                            join Order_Order(nolock)c on c.id=b.OrderId
		                            join User_Customer(nolock)d on d.id=c.CustomerId
		                            left join Finance_CustomerDeliveryBillDetail(nolock)e on e.OrderId = a.OrderId and e.FeeType = 9
			                            where  a.createtime  between @start and @end   and BillingCycle = @BillingCycle
			                            and e.Id is null
								group by d.Id,d.CompanyName,d.Abbreviation 
                            union
                              select isnull( a.Abbreviation,a.CompanyName) as CustomerName, a.Id as CustomerId,'{startTime.ToString_yyyyMMdd()}' [start],'{endDay.AddDays(-1).ToString_yyyyMMdd()}' [end] from User_Customer(nolock)a
                            join
                                Logistics_ClaimRecord(nolock)l on a.id=l.CustomerId
                            where l.CreateTime between  @start and  @end
	                            and isnull(l.Solution,0) !=0 and l.ClaimStatus=0 and BillingCycle = @BillingCycle
	                            group by a.Id,a.CompanyName,a.Abbreviation  
	                           ";
            return await _unitOfWork.QueryBySqlAsync<WaitCreateBillCustomer>(sql, null, new { BillingCycle = billType, start = startTime, end = endDay });
        }

        public async Task<CustomerDeliveryPanelDetail> GetCustomerDeliveryPanelDetail()
        {
            string sql = @"
                            select b.Abbreviation as CustomerName, convert(nvarchar(10), a.CreateTime,120) as AccountingPeriod,
                            case when convert(nvarchar(10), a.CreateTime,120)  = convert(nvarchar(10),getdate(),120)  then 1 else 0 end as Hot,
                            a.SysFreigh as Total
	                            from Finance_CustomerDeliveryBill(nolock)a join User_Customer(nolock)b on a.CustomerId=b.Id
                                                 where Status=0 and   
	                                                     StartTime>='2022-01-01'
							                             order by a.CreateTime desc


	                            select a.CustomerId,b.Abbreviation as CustomerName, convert(nvarchar(10), min( a.EndTime),120) as AccountingPeriod,
	                            sum(a.Receivables - a.Payment ) as Total
                                                 from Finance_CustomerDeliveryBill(nolock)a join User_Customer(nolock)b on a.CustomerId=b.Id 
                                                 where Status=3 and StartTime>='2022-01-01'
                                group by b.Abbreviation,a.CustomerId
	                            order by sum(a.Receivables - a.Payment )  desc

	
	                            select a.CustomerId,b.Abbreviation as CustomerName, convert(nvarchar(10), min( a.EndTime),120) as AccountingPeriod,
	                            sum(a.Receivables - a.Payment ) as Total
                                                 from Finance_CustomerDeliveryBill(nolock)a join User_Customer(nolock)b on a.CustomerId=b.Id 
                                                 where Status=3 and StartTime>='2022-01-01' 
					                             and CurrentOverdueDays>0
                                group by b.Abbreviation,a.CustomerId
	                            order by sum(a.Receivables - a.Payment )  desc


	
	
	                            select a.CustomerId,b.Abbreviation as CustomerName, convert(nvarchar(10), max( a.EndTime),120) as AccountingPeriod,
	                            sum(a.Payment ) as Total
                                                 from Finance_CustomerDeliveryBill(nolock)a join User_Customer(nolock)b on a.CustomerId=b.Id 
                                                 where Status=5 and StartTime>='2022-01-01' 
					                             and CurrentOverdueDays>0
                                group by b.Abbreviation,a.CustomerId
	                            order by sum(a.Receivables - a.Payment )  desc
                            ";
            var dr = await _unitOfWork.Connection.QueryMultipleAsync(sql);


            CustomerDeliveryPanelDetail detail = new CustomerDeliveryPanelDetail
            {
                NotPass = await dr.ReadAsync<CustomerDeliveryPanelDetailItem>(),
                Arrears = await dr.ReadAsync<CustomerDeliveryPanelDetailItem>(),
                Overdue = await dr.ReadAsync<CustomerDeliveryPanelDetailItem>(),
                Payment = await dr.ReadAsync<CustomerDeliveryPanelDetailItem>()
            };
            return detail;
        }

        public async Task<List<CustomerRemittance>> GetRemittance(QueryModel queryModel)
        {
            return (await _unitOfWork.QueryBySqlAsync<CustomerRemittance>(
    "select a.*,b.Abbreviation from Finance_CustomerDeliveryBill(nolock)a join user_customer(nolock)b on a.CustomerId=b.Id where 1=1 " + GetWhere(queryModel.QueryParamer))).ToList();
        }

        public Tuple<decimal, decimal> GetUSDAndEURToCny(DateTime start, DateTime end)
        {
            //USD
            string sql = "select ToCNY from Finance_CustomerRate(nolock) where @start between startDate and endDate and @end between startDate and endDate and Currency='USD'";
            var usd = _unitOfWork.Connection.QuerySingle<decimal>(sql, new { start = start, end = start });
            sql = "select ToCNY from Finance_CustomerRate(nolock) where @start between startDate and endDate and @end between startDate and endDate  and Currency='EUR'";
            var eur = _unitOfWork.Connection.QuerySingle<decimal>(sql, new { start = start, end = start });
            return new Tuple<decimal, decimal>(usd, eur);
        }

        public async Task<IEnumerable<Finance_ChargesDetail>> GetChargesDetails(int customerId, DateTime start, DateTime end)
        {
            string sql = "select * from Finance_ChargesDetail(nolock) where CustomerId=@customerId and AccountingDate>=@start and AccountingDate<@end and Offset=0 ";//时间全用大于等于、小于
            return await _unitOfWork.QueryBySqlAsync<Finance_ChargesDetail>(sql, null, new { customerId = customerId, start = start, end = end });
        }
        public async Task<IEnumerable<OP_FirstBillInfo>> GetCustomerDeliveryBillByNew(IEnumerable<int> customerIds)
        {
            string sql = @"select a.CompanyName,b.*,c.Balance,c.RepaymentTermDay,c.CustomerId CustomerInfoId from User_Customer a
left join Finance_Customer_Account c on c.CustomerId=a.Id
outer apply (select top 1 * from Finance_CustomerDeliveryBill where CustomerId=a.Id order by id desc) b
where a.Id in @Ids";
            return await _unitOfWork.QueryBySqlAsync<OP_FirstBillInfo>(sql, null, new { Ids = customerIds });
        }

        public async Task<List<Finance_CustomerDeliveryBillDetail>> Get_Repeat_Fee(int billId)
        {
            string sql = @"select OrderId from Finance_CustomerDeliveryBillDetail where OrderId in (
                select OrderId from Finance_CustomerDeliveryBillDetail(nolock) 
	                where BillId = @billId and FeeType=0)
                and BillId <>@billId and FeeType=0";

            return (await _unitOfWork.QueryBySqlAsync<Finance_CustomerDeliveryBillDetail>(sql, null, new { billId = billId })).ToList();
        }

        public async Task<IEnumerable<Finance_CustomerDeliveryBill>> AddDeliveryBillBatch(List<Finance_CustomerDeliveryBill> finance_CustomerDeliveryBills)
        {
            _unitOfWork.InsertBatch(finance_CustomerDeliveryBills);
            var sql = @$"select * from Finance_CustomerDeliveryBill where BillTmpId = @BillTmpId";
            var dbFinance_CustomerDeliveryBills = await _unitOfWork.QueryBySqlAsync<Finance_CustomerDeliveryBill>(sql, null, new { BillTmpId = finance_CustomerDeliveryBills.FirstOrDefault()?.BillTmpId });
            return dbFinance_CustomerDeliveryBills;
        }


        /// <summary>
        /// 更新账单状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="billState"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task UpdateCustomerDeliveryBillState(int id, BillState billState, bool notran)
        {
            if (notran)
                await _unitOfWork.UpdateByConditionNoTransferAsync<Finance_CustomerDeliveryBill, bool>(x => x.Id == id, new { BillState = (int)billState });
            else
                await _unitOfWork.UpdateByConditionAsync<Finance_CustomerDeliveryBill, bool>(x => x.Id == id, new { BillState = (int)billState });

        }
    }

}