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.Channel;
using XYY.Model.Standard.DingTalk;
using XYY.Model.Standard.Dto.MpsOrder;
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<IEnumerable<Order_MpsOrderBox>> GetReceivingBoxs(IEnumerable<string> systemNos);
        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<bool> UpdateOrderPrintQty(int orderId, bool OrderPrintQty);
        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);
        Task<Order_MpsOrderBoxPicture> GetBoxPictureOrderInfo(string boxNumber);
        Task<bool> AddBoxPicture(Order_MpsOrderBoxPicture model);
        Task<bool> RemoveBoxPicture(int[] PicIds);
        Task<List<Order_MpsOrderBoxPicture>> GetBoxPictures(string boxNumber);
        Task<List<VerifyBoxInfo>> ImportMpsBoxDataVerify(List<string> boxNumbers);
        Task<bool> UpdateImportReceivingBoxs(List<Order_MpsOrderBox> boxs);
        Task<List<WaitConfirmDto>> GetExportWaitConfirm(IEnumerable<int> Ids);
        Task<List<Logistics_Channel>> GetChannels(List<int> channelIds);
        Task<List<FBA_Incidental>> GetIncidentalsByOrderIds(List<int> OrderIds, FBAIncidentalCalculateTarget target);
    }

    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 Task<IEnumerable<Order_MpsOrderBox>> GetReceivingBoxs(IEnumerable<string> systemNos)
        {
            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.TransferNumber in @TransferNumber ", null, new { TransferNumber = systemNos });
        }
        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)
        {
            //转入时统一做一次箱子状态更新 和重量的更新
            StringBuilder sb = new StringBuilder();
            sb.Append($"update Order_MpsOrderBox set BoxOutStatus={(int)BoxOutStatus.Receipt} where MpsOrderId in (select Id from Order_MpsOrder(nolock) where OrderId in @OrderIds);");
            //仅转打单才更新
            if (status == ((int)MpsOrderStatus.打单))
            {
                //更新收件时间，订单状态
                sb.Append($" update Order_Order set OrderStatus=2 where Id in @OrderIds;");
            }
            sb.Append($" update Order_MpsOrder set Status={status} where OrderId in @OrderIds ");
            await _unitOfWork.ExecuteAsync(sb.ToString(), new { OrderIds = OrderIds });
            return true;
        }
        public async Task<bool> UpdateOrderPrintQty(int orderId, bool OrderPrintQty)
        {
            string sql = $"update Order_MpsOrder set OrderPrintQty={(OrderPrintQty ? 1 : 0)} where OrderId=@OrderIds";
            await _unitOfWork.ExecuteAsync(sql, new { OrderIds = orderId });
            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)  and isnull(ReceiveTime,'')='' ";
            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,a.ReceiverCompany,b.ReceiverStreet1,b.ReceiverStreet2
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=@ReceiverStreet1,ReceiverCompany=@ReceiverCompany where Id={OrderId};
update Order_MpsOrder set WarehouseCode=@WarehouseCode,DeclarationMethod=@DeclarationMethod,ReceiverStreet1=@ReceiverStreet1,ReceiverStreet2=@ReceiverStreet2 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;
            List<string> numbers = new List<string>();
            if (qmSystemNo != null && !string.IsNullOrEmpty(qmSystemNo.Value))
            {
                AdditionalSql = $" and (SystemNo in @qmSystemNo or CustomerOrderNo in @qmSystemNo)";
                numbers.AddRange(qmSystemNo.Value.Split(',', StringSplitOptions.RemoveEmptyEntries));
                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, new { qmSystemNo = numbers }, _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)
        {
            string sql = $@"select a.*,b.SMPOtherFeeId from FBA_Incidental(nolock) a
left join FBA_OtherFee(nolock) b on b.Id=a.FBAOtherFeeId
where Target=({(int)target}) and OrderId = {OrderId}";
            var data = await _unitOfWork.QueryBySqlAsync<FBA_Incidental>(sql, 600);
            return data.ToList();
        }
        public async Task<List<FBA_Incidental>> GetIncidentalsByOrderIds(List<int> OrderIds, FBAIncidentalCalculateTarget target)
        {
            string sql = $" select * from FBA_Incidental(nolock) where Target=({(int)target}) and OrderId in @Ids ";
            var data=await _unitOfWork.QueryBySqlAsync<FBA_Incidental>(sql, 600, new { Ids = OrderIds });
            return data.ToList();
        }
        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}',DeclarationMethod='{temp.DeclarationMethod}',SplitWeightRate={temp.SplitWeightRate} 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,BillVolumeWeight=@BillVolumeWeight,CustomerGoodsNames=@CustomerGoodsNames  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;
            List<string> numbers = new List<string>();
            if (qmSystemNo != null && !string.IsNullOrEmpty(qmSystemNo.Value))
            {
                AdditionalSql = $" and (SystemNo in @qmSystemNo or CustomerOrderNo in @qmSystemNo)";
                numbers.AddRange(qmSystemNo.Value.Split(',', StringSplitOptions.RemoveEmptyEntries));
                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, new { qmSystemNo = numbers }, _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;
            List<string> numbers= new List<string>();
            if (qmSystemNo != null && !string.IsNullOrEmpty(qmSystemNo.Value))
            {
                AdditionalSql = $" and (SystemNo in @qmSystemNo or CustomerOrderNo in @qmSystemNo)";
                numbers.AddRange(qmSystemNo.Value.Split(',', StringSplitOptions.RemoveEmptyEntries));
                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, new { qmSystemNo= numbers }, _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;
            List<string> numbers = new List<string>();
            if (qmSystemNo != null && !string.IsNullOrEmpty(qmSystemNo.Value))
            {
                AdditionalSql = $" and (SystemNo in @qmSystemNo or CustomerOrderNo in @qmSystemNo)";
                numbers.AddRange(qmSystemNo.Value.Split(',', StringSplitOptions.RemoveEmptyEntries));
                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, new { qmSystemNo = numbers }, _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 if (item.Filed == "NickName") { item.Filed = "d." + 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;
        }

        public async Task<Order_MpsOrderBoxPicture> GetBoxPictureOrderInfo(string boxNumber)
        {
            string sql = $@"select a.OrderId,b.MpsOrderId,b.Id MpsOrderBoxId,b.BoxNumber from Order_MpsOrder(nolock) a
left join Order_MpsOrderBox(nolock) b on b.MpsOrderId=a.Id
where b.BoxNumber='{boxNumber}' ";
            var query = _unitOfWork.QueryBySqlAsync<Order_MpsOrderBoxPicture>(sql);
            return query.Result.FirstOrDefault();
        }
        public async Task<bool> AddBoxPicture(Order_MpsOrderBoxPicture model)
        {
            await _unitOfWork.UpdateByPrimaryKeyAsync<Order_MpsOrder>("IsBoxPicture", true, model.MpsOrderId);
            await _unitOfWork.UpdateByPrimaryKeyAsync<Order_MpsOrderBox>("IsBoxPicture", true, model.MpsOrderBoxId);
            await _unitOfWork.InsertAsync(model);
            return true;
        }
        public async Task<bool> RemoveBoxPicture(int[] PicIds)
        {
            string boxNumberSql = $" select top 1 * from Order_MpsOrderBoxPicture where Id in @picIds ";
            var boxNumber = (await _unitOfWork.QueryBySqlAsync<Order_MpsOrderBoxPicture>(boxNumberSql, null, new { picIds = PicIds })).FirstOrDefault();
            string sql = $@"update Order_MpsOrderBoxPicture set IsDelete=1 where Id in @picIds";
            await _unitOfWork.ExecuteAsync(sql, new { picIds = PicIds });
            if (boxNumber == null) { return true; }
            var boxPicInfo = await GetBoxPictures(boxNumber.BoxNumber);
            //删完图片，更改状态
            if (boxPicInfo == null || boxPicInfo.Count == 0)
            {
                await _unitOfWork.UpdateByPrimaryKeyAsync<Order_MpsOrder>("IsBoxPicture", false, boxNumber.MpsOrderId);
                await _unitOfWork.ExecuteAsync($" update Order_MpsOrderBox set IsBoxPicture=0 where MpsOrderId={boxNumber.MpsOrderId}");
            }
            else
            {
                List<int> boxIds = boxPicInfo.Select(x => x.MpsOrderBoxId).Distinct().ToList();
                string picSql = $@" update Order_MpsOrderBox set IsBoxPicture=0 where MpsOrderId={boxNumber.MpsOrderId} and Id not in @boxIds ";
                await _unitOfWork.ExecuteAsync(picSql, new { boxIds = boxIds });
            }
            return true;
        }
        public async Task<List<Order_MpsOrderBoxPicture>> GetBoxPictures(string boxNumber)
        {
            string sql = $" select * from Order_MpsOrderBoxPicture where IsDelete=0 and BoxNumber='{boxNumber}'  ";
            return (await _unitOfWork.QueryBySqlAsync<Order_MpsOrderBoxPicture>(sql)).ToList();
        }
        public async Task<List<VerifyBoxInfo>> ImportMpsBoxDataVerify(List<string> boxNumbers)
        {
            string sql = @"select c.*,d.CustomerVolumnWeightFactor,a.TrackingNumber from Order_Order(nolock) a 
left join Order_MpsOrder(nolock) b on b.OrderId=a.Id
left join Order_MpsOrderBox(nolock) c on c.MpsOrderId=b.Id
left join Logistics_Channel(nolock)d on d.Id=a.CreateOrderChannelId
where b.Status=2 and b.OrderId>0 and (c.BoxNumber in @BoxNumbers or c.BoxServiceNumber in @BoxNumbers) ";
            var data = (await _unitOfWork.QueryBySqlAsync<VerifyBoxInfo>(sql, null, new { BoxNumbers = boxNumbers })).ToList();
            var repeatDatas = data.GroupBy(x => new { x.BoxNumber }).Where(x => x.Count() > 1);
            string error = string.Join(",", repeatDatas.Select(x => x.Key));
            if (!string.IsNullOrEmpty(error))
                throw new Exception("通过客户箱号查询箱子重复:" + error + ",请改用系统箱号去操作");

            if (data.Count == 0)
            {
                throw new Exception("通过箱号未获取到任何数据，请检查箱号是否正确！");
            }
            else if (data.Any(x => !x.TrackingNumber.StartsWith("XYY")))
            {
                throw new Exception("以下箱号所在订单已下单末端，不能再次更新！" + string.Join(',', data.Where(x => !x.TrackingNumber.StartsWith("XYY")).Select(x => x.BoxNumber + "|" + x.BoxServiceNumber).Distinct()
                    ));
            }
            return data;
        }
        public async Task<bool> UpdateImportReceivingBoxs(List<Order_MpsOrderBox> boxs)
        {
            bool result = true;
            try
            {
                //比逐条更新稍微快点
                string sql = @" update Order_MpsOrderBox set OpInputWeight = @OpInputWeight,OpLength=@OpLength,OpWidth=@OpWidth,OpHeight=@OpHeight,VolumeWeight=@VolumeWeight,Remark=@Remark  where Id = @Id  ";
                await _unitOfWork.ExecuteAsync(sql, boxs);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        public async Task<List<WaitConfirmDto>> GetExportWaitConfirm(IEnumerable<int> Ids)
        {
            string sql = $@"select a.Id OrderId,d.CompanyName,a.CustomerOrderNo,c.BoxNumber,c.BoxTrackingNumber,c.BillLenght,c.BillWidth,c.BillHeight,c.BillWeight,c.BillVolumeWeight,b.CustomerConfirmBillWeight,c.GoodsNames,a.ReceiverZipCode,b.GoodsNames OrderGoodsNames,a.TrackingNumber,e.PublicName PublicChannelName
from order_order(nolock) a
left join User_Customer(nolock)d on d.id=a.customerId
left join Order_MpsOrder(nolock) b on b.orderId=a.id
left join Order_MpsOrderBox(nolock) c on c.MpsOrderId=b.Id
left join Logistics_Channel(nolock)e on e.Id=a.CreateOrderChannelId
where a.Id in @Ids ";
            var query = await _unitOfWork.QueryBySqlAsync<WaitConfirmDto>(sql, null, new { Ids = Ids });
            return query.ToList();
        }

        public async Task<List<Logistics_Channel>> GetChannels(List<int> channelIds)
        {
            string sql = "select * from Logistics_Channel(nolock) where Id in @channelIds  ";
            var channelInfo = await _unitOfWork.QueryBySqlAsync<Logistics_Channel>(sql, null, new { channelIds =channelIds});
            return channelInfo.ToList();
        }
    }
}