using Dapper;
using Microsoft.AspNetCore.Http;
using Nest;
using NPOI.SS.Formula.Functions;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Common.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.DingTalk;
using XYY.Model.Standard.Enums;
using XYY.Model.Standard.Finance;
using XYY.Model.Standard.MpsOrder;
using XYY.Model.Standard.MpsOrder.dto;
using XYY.Model.Standard.MpsOrder.Report;
using XYY.Model.Standard.MpsOrder.views;
using XYY.Model.Standard.Order.FBA;
using XYY.Service.Standard.Finance.Charging.Dto;

namespace XYY.Data.Standard.Order
{
    public interface IFBAOrderRepository : IBaseRepository<Order_MpsOrder>
    {
        Task<PageResult<View_Order_ReceivingInfo>> GetReceivingInfo(QueryModel queryModel);
        Task<IEnumerable<Order_MpsOrderBox>> GetReceivingBoxs(IEnumerable<int> orderIds);
        Task<bool> UpdateReceivingBoxs(List<Order_MpsOrderBox> boxs);
        Task<bool> UpdateReceivingMpsOrders(List<MpsOrderWeight> mps);
        Task<IEnumerable<string>> CheackBoxIntegrity(IEnumerable<int> OrderIds);
        Task<bool> UpdateMpsOrdersStatus(IEnumerable<int> OrderIds, int status);
        Task<View_Order_ReceivingInfo> GetOrderReceivingInfo(int orderId);
        Task<bool> UpdateOrderReceiveTime(IEnumerable<int> MpsOrderIds);

        Task SaveOrderRceiveingInfo(View_Order_ReceivingInfo order);

        Task<PageResult<View_Order_Print>> GetPrintList(QueryModel queryModel);
        Task<ReceivingAddress> GetReceivingAddress(int orderId);
        Task<IEnumerable<Order_MpsOrderGoods>> GetReceivingGoods(int orderId);
        Task<View_Order_Print> GetPrint(int orderId);
        Task SavePrintInfos(View_Order_Print order);
        Task<bool> UpdatePrintBoxs(List<Order_MpsOrderBox> boxs);
        Task<bool> UpdatePrintReviceAddress(ReceivingAddress receivingAddress, int OrderId);
        Task<bool> UpdatePrintGoods(List<Order_MpsOrderGoods> orderGoods, int OrderId);
        Task<List<FBA_Incidental>> GetFBA_Incidentals(int OrderId, FBAIncidentalCalculateTarget target);
        Task<bool> SaveFBA_Incidentals(List<FBA_Incidental> fBA_Incidentals, int OrderId, bool isSettlement = false);
        Task<bool> DelFBA_Incidentals(int OrderId, bool isSettlement = false);
        Task<decimal> GetUnitPrice(KPUnitPriceRequest dto);
        Task<bool> UpdateCustomerOrderConfirm(IEnumerable<CustomerConfirmOrderDto> customerConfirmOrderDtos, int OrderId);
        Task<bool> UpdateSettlementOrderConfirm(IEnumerable<SettlementConfirmOrderDto> settlementConfirmOrderDtos, int OrderId);
        Task<IEnumerable<ConfirmWeight>> GetBatchConfirmWeight(IEnumerable<int> Ids);
        Task<IEnumerable<ConfirmWeight>> GetBatchSettlementWeight(IEnumerable<int> Ids);
        Task<bool> UpdateBatchSettlementWeight(List<ConfirmWeight> confirmWeights);
        Task<bool> UpdateBatchConfirmWeight(List<ConfirmWeight> confirmWeights);
        Task<bool> UpdateBatchConfirmIncidenta(List<ConfirmIncidenta> confirmIncidentas);
        Task<bool> UpdateBatchSettlementConfirmIncidenta(List<ConfirmIncidenta> confirmIncidentas);
        Task<IEnumerable<FBA_Incidental>> GetBatchIncidenta(IEnumerable<int> Ids, FBAIncidentalCalculateTarget target);

        Task<bool> UpdateCustomerConfirmBoxs(IEnumerable<Order_MpsOrderBox> mpsOrderBoxes);
        Task<bool> UpdateSettlementConfirmBoxs(IEnumerable<Order_MpsOrderBox> mpsOrderBoxes);
        Task<Order_MpsOrder> GetMpsOrder(int OrderId);
        Task<IEnumerable<View_Order_CustomerConfirm>> GetCustomerConfirmList(IEnumerable<int> Ids);
        Task<IEnumerable<View_Order_SettlementConfirm>> GetSettlementConfirmList(IEnumerable<int> Ids);
        Task<bool> UpdateCharge(KPCharges kPCharges);

        Task<PageResult<View_Order_CustomerConfirm>> GetCustomerConfirmList(QueryModel queryModel);
        Task SaveCustomerConfirm(View_Order_CustomerConfirm order);
        Task<View_Order_CustomerConfirm> GetCustomerConfirm(int orderId);
        Task<PageResult<View_Order_SettlementConfirm>> GetSettlementConfirmList(QueryModel queryModel);
        Task SaveSettlementConfirm(View_Order_SettlementConfirm order);
        Task<View_Order_SettlementConfirm> GetSettlementConfirm(int orderId);
        Task<bool> SettlementConfirmOperation(int orderId, string NiceName);
        Task<bool> UpdateSettlementConfirmForTable(MpsSettlementConfirmTableData dto);

        Task<PageResult<GrossProfitByCustomer>> GetGrossProfitByCustomer(QueryModel qm);
        Task<PageResult<GrossProfitByOrder>> GetGrossProfitByOrder(QueryModel qm);

    }

    public class FBAOrderRepository : BaseRepository<Order_MpsOrder>, IFBAOrderRepository
    {
        public FBAOrderRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        {
        }

        public Task<View_Order_CustomerConfirm> GetCustomerConfirm(int orderId)
        {
            return _unitOfWork.GetAsync<View_Order_CustomerConfirm>(orderId);
        }
        public Task<IEnumerable<Order_MpsOrderBox>> GetReceivingBoxs(IEnumerable<int> orderIds)
        {
            return _unitOfWork.QueryBySqlAsync<Order_MpsOrderBox>(@$"select b.* from Order_MpsOrder(nolock) a
left join Order_MpsOrderBox(nolock) b on b.MpsOrderId=a.Id
where a.OrderId in @orderIds ", null, new { orderIds = orderIds });
        }
        public async Task<bool> UpdateReceivingBoxs(List<Order_MpsOrderBox> boxs)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrderBox set OpInputWeight = @OpInputWeight,OpLength=@OpLength,OpWidth=@OpWidth,OpHeight=@OpHeight,VolumeWeight=@VolumeWeight,VolumeFactor=@VolumeFactor,GoodsNames=@GoodsNames,Remark=@Remark,Identification=@Identification  where Id = @Id  ";
                await _unitOfWork.ExecuteAsync(sql, boxs);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        public async Task<bool> UpdateReceivingMpsOrders(List<MpsOrderWeight> mps)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrder set OPInputWeight=@InputWeight,OPVolumeWeight=@VolumeWeight,OpBillWeight=@OpBillWeight,GoodsNames=@GoodsNames,Remark=@Remark,Identification=@Identification where Id=@MpsOrderId";
                await _unitOfWork.ExecuteAsync(sql, mps);
            }
            catch (Exception ex)
            {
                throw ex;
                result = false;
            }
            return result;
        }
        public async Task<IEnumerable<string>> CheackBoxIntegrity(IEnumerable<int> OrderIds)
        {
            string sql = $@"select a.CustomerOrderNumber from Order_MpsOrder(nolock) a
left join Order_MpsOrderBox(nolock) b on b.MpsOrderId=a.Id
where a.OrderId in @ids and ( 0=ISNULL(b.OpInputWeight,0) or 0=ISNULL(b.VolumeWeight,0) )";
            return await _unitOfWork.QueryBySqlAsync<string>(sql, null, new { ids = OrderIds });
        }
        public async Task<bool> UpdateMpsOrdersStatus(IEnumerable<int> OrderIds, int status)
        {
            string sql = $"update Order_MpsOrder set Status={status} where OrderId in @OrderIds";
            await _unitOfWork.ExecuteAsync(sql, new { OrderIds = OrderIds });
            return true;
        }
        public async Task<bool> UpdateOrderReceiveTime(IEnumerable<int> MpsOrderIds)
        {
            string sql = $"update Order_Order set ReceiveTime=GETDATE() where Id in (select OrderId from Order_MpsOrder(nolock) where Id in @orderIds) ";
            await _unitOfWork.ExecuteAsync(sql, new { orderIds = MpsOrderIds });
            return true;
        }
        public async Task<ReceivingAddress> GetReceivingAddress(int orderId)
        {
            string sql = @$"select a.ReceiverName,a.ReceiverPhone,a.ReceiverCountryCode,b.WarehouseCode,a.ReceiverEmail,b.DeclarationMethod
,a.ReceiverState,a.ReceiverCity,a.ReceiverZipCode,a.House,a.ReceiverStreet
from Order_Order(nolock) a
left join Order_MpsOrder(nolock) b on b.Id=a.MpsOrderId
where a.Id={orderId}";
            return (await _unitOfWork.QueryBySqlAsync<ReceivingAddress>(sql)).FirstOrDefault();
        }
        public Task<IEnumerable<Order_MpsOrderGoods>> GetReceivingGoods(int orderId)
        {
            string sql = @$"select b.* from Order_Order(nolock) a 
left join Order_MpsOrderGoods(nolock) b on b.MpsOrderId=a.MpsOrderId
where a.Id={orderId}";
            return _unitOfWork.QueryBySqlAsync<Order_MpsOrderGoods>(sql);
        }
        public async Task<bool> UpdatePrintBoxs(List<Order_MpsOrderBox> boxs)
        {
            bool result = true;
            if (boxs == null || boxs.Count == 0) { return result; }
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrderBox set ChannelWeight = @ChannelWeight,ChannelLength=@ChannelLength,ChannelWidth=@ChannelWidth,ChannelHeight=@ChannelHeight  where Id = @Id  ";
                await _unitOfWork.ExecuteAsync(sql, boxs);
                await _unitOfWork.ExecuteAsync($" update Order_MpsOrder set ChannelInputWeight={boxs.Sum(x => (x.ChannelWeight ?? 0))} where Id=" + boxs.FirstOrDefault().MpsOrderId);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        public async Task<bool> UpdatePrintReviceAddress(ReceivingAddress receivingAddress, int OrderId)
        {
            bool result = true;
            if (receivingAddress == null) { return result; }
            try
            {
                string sql = @$" update Order_Order set ReceiverName=@ReceiverName,ReceiverPhone=@ReceiverPhone,ReceiverCountryCode=@ReceiverCountryCode,ReceiverEmail=@ReceiverEmail,ReceiverState=@ReceiverState,ReceiverCity=@ReceiverCity,ReceiverZipCode=@ReceiverZipCode,House=@House,ReceiverStreet=@ReceiverStreet where Id={OrderId};
update Order_MpsOrder set WarehouseCode=@WarehouseCode,DeclarationMethod=@DeclarationMethod where OrderId={OrderId}  ";
                await _unitOfWork.ExecuteAsync(sql, receivingAddress);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        public async Task<bool> UpdatePrintGoods(List<Order_MpsOrderGoods> orderGoods, int OrderId)
        {
            bool result = true;
            if (orderGoods == null || orderGoods.Count == 0) { return result; }
            try
            {
                string sql = @$" update Order_MpsOrderGoods set CnName=@CnName,EnName=@EnName,Quantity=@Quantity,GoodsValue=@GoodsValue,Brand=@Brand,Meterial=@Meterial,SaleLink=@SaleLink,HsCode=@HsCode,Magnetic=@Magnetic,Electric=@Electric,Liquid=@Liquid where Id=@Id ";
                await _unitOfWork.ExecuteAsync(sql, orderGoods);
                string orderSqlSet = string.Empty;
                if (orderGoods.Any(x => x.Magnetic))
                {
                    orderSqlSet += "Magnetic=1,";
                }
                if (orderGoods.Any(x => x.Electric))
                {
                    orderSqlSet += "Electric=1,";
                }
                if (orderGoods.Any(x => x.Liquid))
                {
                    orderSqlSet += "Liquid=1,";
                }
                if (!string.IsNullOrEmpty(orderSqlSet))
                {
                    await _unitOfWork.ExecuteAsync("update Order_MpsOrder set " + orderSqlSet.TrimEnd(',') + $" where OrderId={OrderId}");
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        public async Task<PageResult<View_Order_CustomerConfirm>> GetCustomerConfirmList(QueryModel qm)
        {
            #region 基础sql
            string AdditionalSql = string.Empty;
            var qmSystemNo = qm?.QueryParamer?.Where(x => x.Filed == "SystemNo")?.FirstOrDefault();
            string where = string.Empty;
            if (qmSystemNo != null && !string.IsNullOrEmpty(qmSystemNo.Value))
            {
                AdditionalSql = $" and (SystemNo='{qmSystemNo.Value}' or CustomerOrderNo='{qmSystemNo.Value}')";
                qm.QueryParamer.Remove(qmSystemNo);
            }
            if (qm?.QueryParamer != null && qm?.QueryParamer.Count > 0) { where = _unitOfWork.GetWhere(qm.QueryParamer); }

            StringBuilder sb = new StringBuilder();
            sb.Append($" select * from View_Order_CustomerConfirm(nolock) where 1=1 {AdditionalSql}");

            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<View_Order_CustomerConfirm>()
            {
                Result = dr.Read<View_Order_CustomerConfirm>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
                TotalCount = dr.Read<int>().FirstOrDefault()
            };
            #endregion
            return pageResult;
        }
        public Task<IEnumerable<View_Order_CustomerConfirm>> GetCustomerConfirmList(IEnumerable<int> Ids)
        {
            return _unitOfWork.QueryAsync<View_Order_CustomerConfirm>(x => x.Id.In(Ids));
        }
        public Task<IEnumerable<View_Order_SettlementConfirm>> GetSettlementConfirmList(IEnumerable<int> Ids)
        {
            return _unitOfWork.QueryAsync<View_Order_SettlementConfirm>(x => x.Id.In(Ids));
        }
        public async Task<List<FBA_Incidental>> GetFBA_Incidentals(int OrderId, FBAIncidentalCalculateTarget target)
        {
            var OrderInc = (await _unitOfWork.QueryAsync<FBA_Incidental>(x => x.OrderId == OrderId)).ToList();
            var OrderIncGroup = OrderInc.Where(x => x.Target == target).ToList();
            return OrderIncGroup;
        }
        public async Task<bool> SaveFBA_Incidentals(List<FBA_Incidental> fBA_Incidentals, int OrderId, bool isSettlement = false)
        {
            //每次只能先删除后新增
            string sql = $"delete FBA_Incidental where OrderId={OrderId}";
            if (isSettlement) { sql += " and Target=1 "; }//结算只删除结算的部分，收入将删除所有
            await _unitOfWork.ExecuteAsync(sql);
            await _unitOfWork.BulkToDBAsync(fBA_Incidentals);
            return true;
        }
        public async Task<bool> DelFBA_Incidentals(int OrderId, bool isSettlement = false)
        {
            string sql = $"delete FBA_Incidental where OrderId={OrderId}";
            if (isSettlement) { sql += " and Target=1 "; }//结算只删除结算的部分，收入将删除所有
            await _unitOfWork.ExecuteAsync(sql);
            return true;
        }
        public async Task<bool> UpdateCustomerOrderConfirm(IEnumerable<CustomerConfirmOrderDto> customerConfirmOrderDtos, int OrderId)
        {
            bool result = true;
            try
            {
                var temp = customerConfirmOrderDtos.FirstOrDefault();
                string sql = @$" update Order_MpsOrder set CustomerConfirmBillWeight={temp.CustomerConfirmBillWeight},CustomerConfirmUnitPrice={temp.CustomerConfirmUnitPrice},CustomerConfirmTotalSurcharges={temp.CustomerConfirmTotalSurcharges},CustomerConfirmTotal={temp.CustomerConfirmTotal},ApprovalNumber='{temp.ApprovalNumber}' where OrderId={OrderId}  ";
                await _unitOfWork.ExecuteAsync(sql);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        public async Task<bool> UpdateSettlementOrderConfirm(IEnumerable<SettlementConfirmOrderDto> settlementConfirmOrderDtos, int OrderId)
        {
            bool result = true;
            try
            {
                var temp = settlementConfirmOrderDtos.FirstOrDefault();
                string sql = @$" update Order_MpsOrder set SettlementConfirmBillWeight={temp.SettlementConfirmBillWeight},SettlementConfirmUnitPrice={temp.SettlementConfirmUnitPrice},SettlementConfirmTotalSurcharges={temp.SettlementConfirmTotalSurcharges},SettlementConfirmTotal={temp.SettlementConfirmTotal} where OrderId={OrderId}  ";
                await _unitOfWork.ExecuteAsync(sql);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        public async Task<bool> UpdateCustomerConfirmBoxs(IEnumerable<Order_MpsOrderBox> mpsOrderBoxes)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrderBox set BillWeight = @BillWeight,BillLenght=@BillLenght,BillWidth=@BillWidth,BillHeight=@BillHeight,VolumeWeight=@VolumeWeight,VolumeFactor=@VolumeFactor  where Id = @Id  ";
                await _unitOfWork.ExecuteAsync(sql, mpsOrderBoxes);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        public async Task<bool> UpdateSettlementConfirmBoxs(IEnumerable<Order_MpsOrderBox> mpsOrderBoxes)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrderBox set SettlementWeight = @SettlementWeight,SettlementLenght=@SettlementLenght,SettlementWidth=@SettlementWidth,SettlementHeight=@SettlementHeight,SettlementVolumeWeight=@SettlementVolumeWeight,SettlementVolumeFactor=@SettlementVolumeFactor  where Id = @Id  ";
                await _unitOfWork.ExecuteAsync(sql, mpsOrderBoxes);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }

        public async Task<Order_MpsOrder> GetMpsOrder(int OrderId)
        {
            return (await _unitOfWork.QueryBySqlAsync<Order_MpsOrder>($"select * from Order_MpsOrder(nolock) where OrderId={OrderId}")).FirstOrDefault();
        }
        public async Task<IEnumerable<ConfirmWeight>> GetBatchConfirmWeight(IEnumerable<int> Ids)
        {
            string sql = @"select OrderId,TransferNumber OrderNo,CustomerConfirmBillWeight Weight,CustomerConfirmUnitPrice UnitPrice from Order_MpsOrder
where OrderId in @OrderId";
            return await _unitOfWork.QueryBySqlAsync<ConfirmWeight>(sql, null, new { OrderId = Ids });
        }
        public async Task<IEnumerable<ConfirmWeight>> GetBatchSettlementWeight(IEnumerable<int> Ids)
        {
            string sql = @"select OrderId,TransferNumber OrderNo,SettlementConfirmBillWeight Weight,SettlementConfirmUnitPrice UnitPrice from Order_MpsOrder
where OrderId in @OrderId";
            return await _unitOfWork.QueryBySqlAsync<ConfirmWeight>(sql, null, new { OrderId = Ids });
        }
        public async Task<bool> UpdateBatchSettlementWeight(List<ConfirmWeight> confirmWeights)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrder set SettlementConfirmBillWeight=@Weight,SettlementConfirmUnitPrice=@UnitPrice,SettlementConfirmTotal=@Weight*@UnitPrice+isnull(SettlementConfirmTotalSurcharges,0) where OrderId=@OrderId  ";
                await _unitOfWork.ExecuteAsync(sql, confirmWeights);
            }
            catch (Exception ex)
            {
                throw;
            }
            return result;
        }
        public async Task<bool> UpdateBatchConfirmWeight(List<ConfirmWeight> confirmWeights)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrder set CustomerConfirmBillWeight=@Weight,CustomerConfirmUnitPrice=@UnitPrice,CustomerConfirmTotal=@Weight*@UnitPrice+isnull(CustomerConfirmTotalSurcharges,0) where OrderId=@OrderId ";
                await _unitOfWork.ExecuteAsync(sql, confirmWeights);
            }
            catch (Exception ex)
            {
                throw;
            }
            return result;
        }
        public async Task<bool> UpdateBatchConfirmIncidenta(List<ConfirmIncidenta> confirmIncidentas)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrder set CustomerConfirmTotalSurcharges=@TotalSurcharges,CustomerConfirmTotal=CustomerConfirmBillWeight*CustomerConfirmUnitPrice+@TotalSurcharges where OrderId=@OrderId ";
                await _unitOfWork.ExecuteAsync(sql, confirmIncidentas);
            }
            catch (Exception ex)
            {
                throw;
            }
            return result;
        }
        public async Task<bool> UpdateBatchSettlementConfirmIncidenta(List<ConfirmIncidenta> confirmIncidentas)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrder set SettlementConfirmTotalSurcharges=@TotalSurcharges,SettlementConfirmTotal=SettlementConfirmBillWeight*SettlementConfirmUnitPrice+@TotalSurcharges where OrderId=@OrderId ";
                await _unitOfWork.ExecuteAsync(sql, confirmIncidentas);
            }
            catch (Exception ex)
            {
                throw;
            }
            return result;
        }
        public async Task<IEnumerable<FBA_Incidental>> GetBatchIncidenta(IEnumerable<int> Ids, FBAIncidentalCalculateTarget target)
        {
            string sql = $@"select a.[Id]
      ,a.[Name]
      ,a.[CalculateType]
      ,a.[Target]
      ,a.[Fee]
      ,a.[FBAOtherFeeId]
      ,a.[CustomerRemark]
      ,a.[JSRemark]
      ,a.[SysncJS]
      ,a.[CreateUserName]
      ,a.[CreateTime]
      ,a.[UpdateUserName]
      ,a.[UpdateTime]
      ,a.[Remark],b.TransferNumber OrderNo,b.OrderId from FBA_Incidental(nolock)a
right join Order_MpsOrder(nolock)b on a.OrderId=b.OrderId
where ISNULL(a.Target,{(int)target})={(int)target} and b.OrderId in @Id ";
            return await _unitOfWork.QueryBySqlAsync<FBA_Incidental>(sql, null, new { Id = Ids });
        }
        public async Task<decimal> GetUnitPrice(KPUnitPriceRequest dto)
        {
            var fee = (await
                   _unitOfWork.QueryBySqlAsync<dynamic>(@"select top 1 * from Logistics_ExpressFeeBase(nolock) 
                            Where ExpressId = @ExpressId and CustomerId=@CustomerId and EnableTime<@EnableTime order by EnableTime desc",
                           null, new
                           {
                               ExpressId = dto.PublicExpressId,
                               CustomerId = dto.CustomerId,
                               EnableTime = dto.TransactionTime
                           })).FirstOrDefault();
            if (fee == null)
                return 0m;

            var details = (await _unitOfWork.QueryBySqlAsync<dynamic>($@" 
                                    select  LEFT(b.ZipCode,c.StartQty) as ZipSrart ,c.StartQty,a.FristWeight,a.FristFee,a.ContinuedWeight,a.ContinuedFee from Logistics_ExpressFeeArea(nolock)c join
	                                     Logistics_ExpressFeeGrads  (nolock)a on c.ExpressId = a.Expressid and a.BaseId = {fee.Id}
	                                    join Logistics_ExpressFeeAreaDetail(nolock)b on a.Countory=b.AreaCode and a.Expressid=b.ExpressId
	                                    where a.MaxWeight>{dto.gWeight}  
	                                    order by a.MaxWeight	 
	                                         ")).ToList();

            var d = details.Where(x => dto.ZipCode.Substring(0, x.StartQty) == x.ZipSrart).FirstOrDefault();
            if (d == null)
                return 0;

            return d.ContinuedFee;
        }

        #region 计费数据处理
        public async Task<bool> UpdateCharge(KPCharges kPCharges)
        {
            try
            {
                //找出待更新处理的数据
                var dic = kPCharges.charges.Select(x => new { x.SystemNo, x.CustomerId, x.CreateUserName }).Distinct().ToList();
                //更新旧数据
                string updateSql = @$"update Finance_Order_Charges set Offset=1,UpdateTime=GETDATE(),UpdateUserName='{dic.FirstOrDefault().CreateUserName}' where SystemNo=@SystemNo and CustomerId=@CustomerId;
update Finance_ChargesDetail set Offset=1,UpdateTime=GETDATE(),UpdateUserName='{dic.FirstOrDefault().CreateUserName}' where BillNo=@SystemNo and CustomerId=@CustomerId ";
                await _unitOfWork.ExecuteAsync(updateSql, dic);
                foreach (var item in kPCharges.charges)
                {
                    if (item.ChargeTarget == ChargeTarget.客户)
                    {
                        string sql = $@" update order_fee set OPCFreight = {item.ShippingFee},OPCServiceCharge={item.Remark} , AttachFee={item.AttachFee},UpdateTime=GetDate() ,Remark = '{item.Remark}' where OrderId=(select Id from Order_Order(nolock) where systemNo='{item.SystemNo}')  ";
                        await _unitOfWork.ExecuteAsync(sql);
                    }
                    await _unitOfWork.InsertAsync(item);
                }

                await _unitOfWork.BulkToDBAsync(kPCharges.chargesDetails);
                return true;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion




        public Task<View_Order_ReceivingInfo> GetOrderReceivingInfo(int orderId)
        {
            return _unitOfWork.GetAsync<View_Order_ReceivingInfo>(orderId);
        }

        public Task<View_Order_Print> GetPrint(int orderId)
        {
            return _unitOfWork.GetAsync<View_Order_Print>(orderId);
        }

        public async Task<PageResult<View_Order_Print>> GetPrintList(QueryModel qm)
        {
            #region 基础sql
            string AdditionalSql = string.Empty;
            var qmSystemNo = qm?.QueryParamer?.Where(x => x.Filed == "SystemNo")?.FirstOrDefault();
            string where = string.Empty;
            if (qmSystemNo != null && !string.IsNullOrEmpty(qmSystemNo.Value))
            {
                AdditionalSql = $" and (SystemNo='{qmSystemNo.Value}' or CustomerOrderNo='{qmSystemNo.Value}')";
                qm.QueryParamer.Remove(qmSystemNo);
            }
            if (qm?.QueryParamer != null && qm?.QueryParamer.Count > 0) { where = _unitOfWork.GetWhere(qm.QueryParamer); }

            StringBuilder sb = new StringBuilder();
            sb.Append($" select * from View_Order_Print(nolock) where 1=1 {AdditionalSql}");
            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<View_Order_Print>()
            {
                Result = dr.Read<View_Order_Print>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
                TotalCount = dr.Read<int>().FirstOrDefault()
            };
            #endregion
            return pageResult;
        }

        public async Task<PageResult<View_Order_ReceivingInfo>> GetReceivingInfo(QueryModel qm)
        {
            #region 基础sql
            string AdditionalSql = string.Empty;
            var qmSystemNo = qm?.QueryParamer?.Where(x => x.Filed == "SystemNo")?.FirstOrDefault();
            string where = string.Empty;
            if (qmSystemNo != null && !string.IsNullOrEmpty(qmSystemNo.Value))
            {
                AdditionalSql = $" and (SystemNo='{qmSystemNo.Value}' or CustomerOrderNo='{qmSystemNo.Value}')";
                qm.QueryParamer.Remove(qmSystemNo);
            }
            if (qm?.QueryParamer != null && qm?.QueryParamer.Count > 0) { where = _unitOfWork.GetWhere(qm.QueryParamer); }

            StringBuilder sb = new StringBuilder();
            sb.Append($" select * from view_order_receivingInfo(nolock) where 1=1 {AdditionalSql}");
            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<View_Order_ReceivingInfo>()
            {
                Result = dr.Read<View_Order_ReceivingInfo>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
                TotalCount = dr.Read<int>().FirstOrDefault()
            };
            #endregion
            return pageResult;
        }

        public Task<View_Order_SettlementConfirm> GetSettlementConfirm(int orderId)
        {
            return _unitOfWork.GetAsync<View_Order_SettlementConfirm>(orderId);
        }

        public async Task<PageResult<View_Order_SettlementConfirm>> GetSettlementConfirmList(QueryModel qm)
        {
            #region 基础sql
            string AdditionalSql = string.Empty;
            var qmSystemNo = qm?.QueryParamer?.Where(x => x.Filed == "SystemNo")?.FirstOrDefault();
            string where = string.Empty;
            if (qmSystemNo != null && !string.IsNullOrEmpty(qmSystemNo.Value))
            {
                AdditionalSql = $" and (SystemNo='{qmSystemNo.Value}' or CustomerOrderNo='{qmSystemNo.Value}')";
                qm.QueryParamer.Remove(qmSystemNo);
            }
            if (qm?.QueryParamer != null && qm?.QueryParamer.Count > 0) { where = _unitOfWork.GetWhere(qm.QueryParamer); }

            StringBuilder sb = new StringBuilder();
            sb.Append($" select * from View_Order_SettlementConfirm(nolock) where 1=1 {AdditionalSql}");
            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<View_Order_SettlementConfirm>()
            {
                Result = dr.Read<View_Order_SettlementConfirm>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
                TotalCount = dr.Read<int>().FirstOrDefault()
            };
            #endregion
            return pageResult;
        }
        public async Task<bool> SettlementConfirmOperation(int orderId, string NiceName)
        {
            await _unitOfWork.ExecuteAsync($"update Order_MpsOrder set UpdateTime=GETDATE(),IsSettlementConfirm=1,SettlementConfirmUserName='{NiceName}' where OrderId={orderId} ");
            return true;
        }
        public async Task<bool> UpdateSettlementConfirmForTable(MpsSettlementConfirmTableData dto)
        {
            decimal incidentalsFee = 0;
            if (dto.incidentals != null && dto.incidentals.Count() > 0) { incidentalsFee = dto.incidentals.Sum(x => x.Fee); }
            string updateSql = $" Update Order_MpsOrder set SettlementSplitWeightRate={dto.SettlementSplitWeightRate},SettlementConfirmBillWeight={dto.SettlementConfirmBillWeight},SettlementConfirmUnitPrice={dto.SettlementConfirmUnitPrice},SettlementConfirmTotalSurcharges={incidentalsFee},SettlementConfirmTotal={dto.SettlementConfirmBillWeight * dto.SettlementConfirmUnitPrice + incidentalsFee}  where OrderId={dto.OrderId} ";
            await _unitOfWork.ExecuteAsync(updateSql);
            return true;
        }

        public Task SaveCustomerConfirm(View_Order_CustomerConfirm order)
        {
            throw new NotImplementedException();
        }

        public Task SaveOrderRceiveingInfo(View_Order_ReceivingInfo order)
        {
            throw new NotImplementedException();
        }

        public Task SavePrintInfos(View_Order_Print order)
        {
            throw new NotImplementedException();
        }

        public Task SaveSettlementConfirm(View_Order_SettlementConfirm order)
        {
            throw new NotImplementedException();
        }
        public async Task<PageResult<GrossProfitByCustomer>> GetGrossProfitByCustomer(QueryModel qm)
        {
            #region 基础sql
            string where = string.Empty;
            if (qm.QueryParamer != null && qm.QueryParamer.Count > 0)
            {
                foreach (var item in qm.QueryParamer)
                {
                    if (item.Filed == "SalesmanUserId") { item.Filed = "c." + item.Filed; }
                    else
                    {
                        item.Filed = "b." + item.Filed;
                    }
                }
                where = _unitOfWork.GetWhere(qm.QueryParamer);
            }
            StringBuilder sb = new StringBuilder();
            sb.Append($@"   select b.Id,c.CompanyName,d.NickName,COUNT(b.Id) OrderCount,sum(isnull(a.CustomerConfirmBillWeight,0))CustomerConfirmBillWeight,SUM(ISNULL(a.CustomerConfirmTotal,0))  CustomerConfirmTotal,SUM(ISNULL(a.SettlementConfirmBillWeight,0))SettlementConfirmBillWeight,SUM(ISNULL(a.SettlementConfirmTotal,0))SettlementConfirmTotal
 ,SUM(ISNULL(a.SettlementConfirmBillWeight,0))-SUM(ISNULL(a.SettlementConfirmTotal,0)) GrossProfit
 from Order_MpsOrder(nolock) a
 left join Order_Order(nolock) b on b.Id=a.OrderId
 left join User_Customer(nolock) c on c.Id=a.CustomerId
 left join User_Info(nolock) d on d.Id=c.SalesmanUserId
 where b.OrderStatus>0 ");
            sb.Append(where);
            sb.Append(" group by c.CompanyName,d.NickName ");
            #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<GrossProfitByCustomer>()
            {
                Result = dr.Read<GrossProfitByCustomer>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
                TotalCount = dr.Read<int>().FirstOrDefault()
            };
            #endregion
            return pageResult;
        }
        public async Task<PageResult<GrossProfitByOrder>> GetGrossProfitByOrder(QueryModel qm)
        {
            #region 基础sql
            string where = string.Empty;
            if (qm.QueryParamer != null && qm.QueryParamer.Count > 0)
            {
                foreach (var item in qm.QueryParamer)
                {
                    if (item.Filed == "SalesmanUserId") { item.Filed = "c." + item.Filed; }
                    else
                    {
                        item.Filed = "b." + item.Filed;
                    }
                   
                }
                where = _unitOfWork.GetWhere(qm.QueryParamer);
            }
            StringBuilder sb = new StringBuilder();
            sb.Append($@"   select b.Id,b.ReceiveTime,c.CompanyName,d.NickName,b.CustomerOrderNo,b.SystemNo,b.TransferNumber,e.PublicName,f.BoxCount,a.CustomerConfirmBillWeight,a.CustomerConfirmUnitPrice
 ,a.CustomerConfirmTotalSurcharges,a.CustomerConfirmTotal,a.SettlementConfirmBillWeight,a.SettlementConfirmUnitPrice,a.SettlementConfirmTotalSurcharges,
 ISNULL(a.CustomerConfirmTotal,0)-ISNULL(a.SettlementConfirmTotal,0) GrossProfit
  from Order_MpsOrder(nolock) a
 left join Order_Order(nolock) b on b.Id=a.OrderId
 left join User_Customer(nolock) c on c.Id=a.CustomerId
 left join User_Info(nolock) d on d.Id=c.SalesmanUserId
 left join Logistics_Channel(nolock) e on e.Id=b.CreateOrderChannelId
outer apply(select COUNT(Id) BoxCount from  Order_MpsOrderBox(nolock) where MpsOrderId=a.Id) f
where b.OrderStatus>0 ");
            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<GrossProfitByOrder>()
            {
                Result = dr.Read<GrossProfitByOrder>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
                TotalCount = dr.Read<int>().FirstOrDefault()
            };
            #endregion
            return pageResult;
        }
        public async Task<PageResult<GrossProfitByOrder>> GetGrossProfitByMonth(QueryModel qm)
        {
            #region 基础sql
            string where = string.Empty;

            StringBuilder sb = new StringBuilder();
            sb.Append($@"   select b.ReceiveTime,c.CompanyName,d.NickName,b.CustomerOrderNo,b.SystemNo,b.TransferNumber,e.PublicName,f.BoxCount,a.CustomerConfirmBillWeight,a.CustomerConfirmUnitPrice
 ,a.CustomerConfirmTotalSurcharges,a.CustomerConfirmTotal,a.SettlementConfirmBillWeight,a.SettlementConfirmUnitPrice,a.SettlementConfirmTotalSurcharges,
 ISNULL(a.CustomerConfirmTotal,0)-ISNULL(a.SettlementConfirmTotal,0) GrossProfit
  from Order_MpsOrder(nolock) a
 left join Order_Order(nolock) b on b.Id=a.OrderId
 left join User_Customer(nolock) c on c.Id=a.CustomerId
 left join User_Info(nolock) d on d.Id=c.SalesmanUserId
 left join Logistics_Channel(nolock) e on e.Id=b.ChannelId
outer apply(select COUNT(Id) BoxCount from  Order_MpsOrderBox(nolock) where MpsOrderId=a.Id) f
where b.OrderStatus>0 ");
            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<GrossProfitByOrder>()
            {
                Result = dr.Read<GrossProfitByOrder>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
                TotalCount = dr.Read<int>().FirstOrDefault()
            };
            #endregion
            return pageResult;
        }

    }
}
