XYY.Core/2.Data/XYY.Data.Strandard/Order/IOrderRepository.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.Order;
using System.Linq;
using Dapper;
using Microsoft.Extensions.Logging;
using XYY.Data.Standard.Channel;
using XYY.Common.Standard;
using XYY.Model.Standard.Channel;
using XYY.Model.Standard.Dto.order;
using RestSharp.Extensions;
using static XYY.Data.Standard.Order.OrderRepository;
using Org.BouncyCastle.Bcpg;
using IoC;

namespace XYY.Data.Standard.Order
{
    public interface IOrderRepository : IBaseRepository<Order_Order>
    {
        Task<int> GetNewId();

        /// <summary>
        /// 导入澳鹏订单
        /// </summary>
        /// <param name="orders">导入澳鹏订单</param>
        Task InputOrderAndGoods(List<OrderInputDto> order);

        Task<List<OrderConfimRequest>> GetWaitConfimOrders(int qty);
        [NonTrans]
        Task AddWinitLog(string winitOrderId, string transferNumber);

        [NonTrans]
        Task RemoveWinitLog(string winitOrderId, string transferNumber);

        [NonTrans]
        Task AddChannelOrderInfoRecord(int channelId, string channelOrderId, string transferNumber);

        [NonTrans]
        Task<string> GetChannelOrderInfoRecord(int channelId, string transferNumber);

        [NonTrans]
        Task<string> GetWinitOrderNo(string transferNumber, string SystemNo = "");
        Task<BagDeclareRequest> GetWaitBoxDeclareRequest(int channelId, string ladingBillNumber = null, string boxNumber = null);
        Task<List<BagDeclareRequest>> GetWaitBoxDeclareRequest(string[] serviceChannelCodeList);
        Task<IEnumerable<SysWarehouseFileLog>> GetWaitDownFile(int qty = 200);
        Task UpdateDownFile(List<SysWarehouseFileLog> transfernums);

        Task<PageResult<View_QueryOverCancelOrders>> QueryOverCancelOrders(QueryModel queryModel);

        Task<PageResult<BagDeclareLog>> GetBagDeclareLogs(QueryModel queryModel);

        Task<List<dynamic>> QueryCustomerData(string sql);

        Task<List<dynamic>> GetZZESBUpdateData(string lading);
        int GetPackagePrintQty(string customerOrderNo);
        Task SetUnusualNotTransfer(string trackingNumber, UnusualReasonType UnusualReasonType, bool IsReprocessing);
        Task AddChannelOrderInfoRecord(string FCOrderId, string transferNumber, int channelId);
        Task<OrderQtys> GetCustomerDraftOrderCount(QueryModel qm);
        Task<List<OrderQtys>> GetOrderQty(QueryModel qm);
        Task<PageResult<CustomerOrder>> GetCustomerOrderPager(QueryModel qm);
        Task<CustomerOrder> GetCustomerOrder(int id);
        Task<CustomerOrder> GetCustomerDraftOrder(int id);
        Task<PageResult<CustomerOrder>> GetCustomerDraftOrderList(QueryModel qm);
        Task<List<CustomerOrder>> GetCustomerOrderList(QueryModel queryModel);

        Task<IEnumerable<ServiceChannelCodeIdDto>> GetLadingBillServiceChannelCodes(string ladingBillNumber);
        Task UpdateTrackingNumber(List<UpdateDataDto> updateDatas);

        Task<List<Order_Order>> GetWaitSyncCustomerTracking(int qty = 30);
        Task<List<Order_CustomerAsyncTracking>> GetWaitAsyncZZESBData(int qty);
        Task UpdateEnd(List<int> ids);
        Task SetNextUpdate(List<Order_Order> noUpdateData);
        Task<List<Order_CustomerAsyncTracking>> GetWaitAsyncKTXESBData(int qty);
        Task InsertFirstToDB(List<Order_CustomerAsyncTracking> list);
        Task<List<Sys_Currency>> GetCurrencies();

        Task<IEnumerable<ServiceChannelCodeIdDto>> GetBoxServiceChannelCodes(string boxNumber);
    }

    public class Sys_Currency
    {
        /// <summary>
        /// 货币名称
        /// </summary>
        public string Name { get; set; }
        /// <summary>
        /// 描述
        /// </summary>
        public string Description { get; set; }
        /// <summary>
        /// 货币符号
        /// </summary>
        public string Symbol { get; set; }
        /// <summary>
        /// USD兑换率
        /// </summary>
        public decimal USDToExchangeRate { get; set; }
        /// <summary>
        /// RMB被兑换率
        /// </summary>
        public decimal ExchangeRateToRMB { get; set; }
        /// <summary>
        /// 中国银行汇率时间
        /// </summary>
        public DateTime? ExchangeRateToRMBTime { get; set; }
        /// <summary>
        /// 更新时间
        /// </summary>
        public DateTime? UpdateRateDate { get; set; }
        public bool IsEnable { get; set; }
    }

    public class OrderRepository : BaseRepository<Order_Order>, IOrderRepository
    {
        public class UpdateDataDto
        {
            public string TransferNumber
            {
                get; set;
            }

            public string CustomerOrderNo
            {
                get; set;
            }

            public int Id
            {
                get; set;
            }

            public string SystemNo
            {
                get; set;
            }

            public string TrackingNumber
            {
                get; set;
            }

            public DateTime ReceiveTime
            {
                get; set;
            }

            public int ChannelId
            {
                get; set;
            }
            public string ServiceOrderNumebr { get; set; }
        }

        private ILogger<OrderInputDto> _logger;
        private ILogistics_ChannelRepository _logisticsRepository;

        public OrderRepository(IUnitOfWork unitOfWork, ILogger<OrderInputDto> logger, ILogistics_ChannelRepository logisticsRepository) : base(unitOfWork)
        {
            _logger = logger;
            _logisticsRepository = logisticsRepository;
        }

        async Task<IEnumerable<ServiceChannelCodeIdDto>> IOrderRepository.GetLadingBillServiceChannelCodes(string ladingBillNumber)
        {
            string sql = @"select id as ChannelId,ServiceChannelCode from Logistics_Channel where id in(
select b.ChannelId from Logistics_Box b inner join Logistics_BoxDetail bd
on b.id=bd.BoxId
where LadingBillNumber=@LadingBillNumber 
group by b.ChannelId)";

            var items = await _unitOfWork.QueryBySqlAsync<ServiceChannelCodeIdDto>(sql, commandTimeout: 120, param: new { LadingBillNumber = ladingBillNumber });
            return items;
        }


        async Task<IEnumerable<ServiceChannelCodeIdDto>> IOrderRepository.GetBoxServiceChannelCodes(string boxNumber)
        {
            string sql = @"select id as ChannelId,ServiceChannelCode from Logistics_Channel where id in(
select b.ChannelId from Logistics_Box b inner join Logistics_BoxDetail bd
on b.id=bd.BoxId
where BoxNumber=@boxNumber 
group by b.ChannelId)";

            var items = await _unitOfWork.QueryBySqlAsync<ServiceChannelCodeIdDto>(sql, commandTimeout: 120, param: new { BoxNumber = boxNumber });
            return items;
        }


        [NonTrans]
        public async Task<List<Sys_Currency>> GetCurrencies()
        {
            string sql = "select * from Sys_Currency(nolock)";
            return (await _unitOfWork.QueryBySqlAsync<Sys_Currency>(sql)).ToList();
        }

        [NonTrans]
        async Task<List<BagDeclareRequest>> IOrderRepository.GetWaitBoxDeclareRequest(string[] serviceChannelCodeList)
        {
            List<BagDeclareRequest> resultList = new List<BagDeclareRequest>();

            foreach (var ServiceChannelCode in serviceChannelCodeList)
            {

                //由于渠道ServiceChannelCode 2041 与其他渠道重复，单独写渠道Id进行预报
                IEnumerable<Logistics_Channel> channels = null;
                if (ServiceChannelCode == "2041")
                {
                    channels = await _logisticsRepository
                    .QueryAsync(x => x.Id == 542);
                }
                else
                {
                    channels = (await _logisticsRepository
                    .QueryAsync(x => x.ServiceChannelCode == ServiceChannelCode)).OrderBy(x => x.ServiceChannelCode);
                }
                foreach (var channelId in channels)
                {
                    BagDeclareRequest result = await ((IOrderRepository)this).GetWaitBoxDeclareRequest(channelId.Id);
                    resultList.Add(result);
                }
            }
            return resultList;
        }

        async Task<BagDeclareRequest> IOrderRepository.GetWaitBoxDeclareRequest(int channelId, string ladingBillNumber, string boxNumber = null)
        {
            var channel = await _logisticsRepository.GetAsync(channelId);
            BagDeclareRequest bagDeclareRequest = new BagDeclareRequest();
            bagDeclareRequest.Channel = channel;

            string specifiedAwbCondition = null;
            if (string.IsNullOrWhiteSpace(ladingBillNumber) && string.IsNullOrEmpty(boxNumber))
            {
                //UBI-GB-Line 改为装箱预报
                if (channel.Id == 383)
                    specifiedAwbCondition = " and isnull(b.IsConfim,0) =0 ";
                else
                    specifiedAwbCondition = " and ''!=ISNULL(c.LadingBillNumber,'') and isnull(b.IsConfim,0) =0 ";
            }
            else if (!string.IsNullOrWhiteSpace(ladingBillNumber))
            {
                specifiedAwbCondition = $" and c.LadingBillNumber ='{ladingBillNumber}'";
            }
            else if (!string.IsNullOrEmpty(boxNumber))
            {
                specifiedAwbCondition += $"and c.boxnumber = '{boxNumber}' and isnull(b.IsConfim,0) =0 ";
            }
            string dateTime = DateTime.Now.AddMonths(-2).ToString_yyyyMMdd();
            string sql = $@"select a.Id as OrderId,a.ActualWeight as ActualWeight,a.ChannelId,e.Id,e.OriginPort as OriginPort,e.Destination as  Port,A.ServicesOrderNo as ServiceNo,a.ReceiverCountryCode as CountryCode
, case when a.CustomerId=294 and LEFT(a.TrackingNumber,3)='XYY' then a.TransferNumber else  a.TrackingNumber end TrackingNumber
,c.BoxNumber,c.LadingBillNumber,b.Id as DetailId,a.OPWeight,a.OPWeight - isnull(d.Deduction,0) as Weight,a.CreateOrderChannelId,d.CreateChannelId,c.DestAirPort,c.OriginAirPort,c.UserBoxNumber,a.ServiceOrderNumber3 from Order_Order(nolock)a join Logistics_BoxDetail(nolock) b on a.id=b.OrderId
                                join Logistics_Box(nolock) c on c.Id= b.BoxId
								left join Logistics_LadingBill(nolock) e on e.LadingBillNumber=c.LadingBillNumber  and e.IsDeleted=0
								left join Weight_Adj(nolock) d on d.CreateChannelId=76
								and a.OPWeight between d.Start and d.[End]
                                where 1=1 and a.ChannelId = {channelId} {specifiedAwbCondition}  and c.CreateTime>'{dateTime}' ";

            /*Debug*/


            //写死一个数据做测试
            string testSql = @$"select 666 Id,'LAX' OriginPort,'LAX'  Port,A.ServicesOrderNo as ServiceNo,a.ReceiverCountryCode as CountryCode, a.TrackingNumber,'zkltest0001' BoxNumber,'zkltest-0001' LadingBillNumber,666 as DetailId,500 OPWeight,600 Weight,a.CreateOrderChannelId,a.CreateOrderChannelId CreateChannelId,'lax' DestAirPort,'lax' OriginAirPort,a.ServiceOrderNumber3 from Order_Order(nolock)a 
        where  a.Id=223655811";
            var items = await _unitOfWork.QueryBySqlAsync<BoxDetailItem>(sql, commandTimeout: 180);
            if (channel.Id == 371)
            {
                foreach (var i in items)
                {
                    i.Weight = i.OPWeight;
                    i.ActualWeight = i.ActualWeight;
                }
            }
            if (channel.Id == 731)
            {
                //部分渠道预报取回下单申报信息
                int maxQty = 1000;
                int count = (int)Math.Ceiling(items.Count() / (decimal)maxQty);

                for (var i = 0; i < count; i++)
                {
                    var l = items.Skip(i * maxQty).Take(maxQty);
                    int[] arrays = l.Select(x => x.OrderId).ToArray();
                    string goodsDeclareValSql = @"
                                select a.Id as OrderId, isnull(sum(c.DeclareValue),  sum(DeclareFee)) as DeclareValue,min(DeclareCurrency) as DeclareCurrency from Order_Order(nolock)a
	                                join Order_OrderGoods(nolock)b on a.Id = b.OrderId
	                                left join Order_ApiDeclaration(nolock)c on c.Guid=b.Guid
	                                where a.id in  @ids
                                group by a.id
                                 ";
                    var declareVals = (await _unitOfWork.QueryBySqlAsync<OrderDeclareVal>(goodsDeclareValSql, null, new { ids = arrays })).ToList();
                    foreach (var item in l)
                    {
                        var d = declareVals.First(x => x.OrderId == item.OrderId);
                        item.DeclareCurrency = d.DeclareCurrency;
                        item.DeclareValue = d.DeclareValue;
                    }
                }
            }
            var dicts = items.GroupBy(x => new { x.CountryCode, x.BoxNumber })
                .ToDictionary(x => x.Key.BoxNumber, x => x.ToList());
            bagDeclareRequest.BatchDeclare = dicts;
            return bagDeclareRequest;
        }


        public class OrderDeclareVal
        {
            public int OrderId
            {
                get; set;
            }
            public decimal DeclareValue
            {
                get; set;
            }
            public string DeclareCurrency
            {
                get; set;
            }
        }


        [NonTrans]
        async Task<List<OrderConfimRequest>> IOrderRepository.GetWaitConfimOrders(int qty)
        {
            int[] IdList = new int[]{ 458,540,
539,
538,
537,
536,549,
550,565,579,580,574,700,
573,
572,
571,669,668,742,743,744,745};
            Dictionary<int[], int[]> customerDic = new Dictionary<int[], int[]>() { { new int[] { 1444 }, new int[] { 383 } } };
            int[] testList = new int[] { 742 };//测试用  and c.SystemNo='XYYEX0029375687YQ'
                                               //            string sql = $@" 
                                               //select top {qty} * from (

            //select top {qty} b.Id as BoxDetailId,c.ChannelId,
            //                      c.ServicesOrderNo,case when c.ChannelId=187 then c.TrackingNumber else c.ServicesOrderNo  end as OrderNo,c.TrackingNumber,d.ServiceCode,c.ActualWeight as OPWeight from Logistics_Box(nolock) a 
            //                                join Logistics_BoxDetail(nolock) b on a.Id=b.BoxId 
            //                                join Order_Order(nolock) c on c.id= b.OrderId
            //                                join Logistics_Channel(nolock) d on d.Id = c.ChannelId  
            //                                where   
            //									  isnull( b.IsConfim ,0)=0   and a.IsDelivery=1 and c.ChannelId in @Id  and c.ReceiveTime>'2022-12-06'
            //union 

            //select top {qty} b.Id as BoxDetailId,c.ChannelId,
            //                      c.ServicesOrderNo,case when c.ChannelId=187 then c.TrackingNumber else c.ServicesOrderNo  end as OrderNo,c.TrackingNumber,d.ServiceCode,c.ActualWeight as OPWeight from Logistics_Box(nolock) a 
            //                                join Logistics_BoxDetail(nolock) b on a.Id=b.BoxId 
            //                                join Order_Order(nolock) c on c.id= b.OrderId
            //                                join Logistics_Channel(nolock) d on d.Id = c.ChannelId  
            //                                where   
            //									  isnull( b.IsConfim ,0)=0  and a.IsDelivery=1 and  c.CustomerId in @customerIds and a.ChannelId in @customerChannelId  and c.ReceiveTime>'2022-12-06')t1";
            //4
            string dayAgo = DateTime.Now.AddDays(-4).ToString_yyyyMMdd();
            string sql = $@"select top {qty} b.Id as BoxDetailId,c.ChannelId,
                      c.ServicesOrderNo,c.ServicesOrderNo as OrderNo,c.ServiceOrderNumber3,c.TrackingNumber,d.ServiceCode,c.ActualWeight as OPWeight from Logistics_Box(nolock) a 
                                left join Logistics_BoxDetail(nolock) b on a.Id=b.BoxId 
                                left join Order_Order(nolock) c on c.id= b.OrderId
                                left join Logistics_Channel(nolock) d on d.Id = c.ChannelId  
                                where   
									  a.DeliveryTime>'{dayAgo}' and isnull( b.IsConfim ,0)=0 and b.Id>0  and a.IsDelivery=1 and a.ChannelId in @Id ";
            return (await _unitOfWork.QueryBySqlAsync<OrderConfimRequest>(sql, 300, new { Id = IdList, customerIds = customerDic.FirstOrDefault().Key, customerChannelId = customerDic.FirstOrDefault().Value })).ToList();
        }

        async Task<int> IOrderRepository.GetNewId()
        {
            string connText = _unitOfWork.ConnectionAddress;
            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connText))
            {
                string sql = @"INSERT INTO  SysNewID (TmpInt) OUTPUT  inserted.ID Values(1); ";
                using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        var val = await command.ExecuteScalarAsync();
                        try
                        {
                            int id = (int)val;
                            return id;
                        }
                        catch
                        {
                            throw new Exception("标识生成失败");
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("标识列生成失败:" + ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 导入澳鹏订单
        /// </summary>
        /// <param name="orders">导入澳鹏订单</param>
        /// <returns></returns>
        async Task IOrderRepository.InputOrderAndGoods(List<OrderInputDto> orders)
        {
            try
            {

                List<Order_OrderGoods> bulkGoods = new List<Order_OrderGoods>();
                List<Logistics_BoxDetail> bulkBoxDetails = new List<Logistics_BoxDetail>();
                foreach (var order in orders)
                {
                    try
                    {
                        if (!(await IsExistsAsync(x => x.CustomerOrderNo == order.CustomerOrderNo || x.TrackingNumber == order.TrackingNumber)))
                        {
                            var id = await InsertAsync(order);
                            order.GoodsList.ForEach(x => x.OrderId = (int)id);
                            bulkGoods.AddRange(order.GoodsList);
                            Logistics_BoxDetail boxDetail = new Logistics_BoxDetail
                            {
                                BoxId = order.BoxId,
                                OrderId = (int)id,
                                TrackingNumber = order.TrackingNumber,
                                TryAgainToSubmit = false,
                                CreateTime = DateTime.Now,
                                CreateUserName = "system"
                            };
                        }
                        else
                        {
                            //已存在的不再重复导入
                            _logger.LogWarning(order.TrackingNumber + ";" + order.CustomerOrderNo + "重复");
                        }
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError("单条错误:" + order.TrackingNumber + ";" + order.CustomerOrderNo);
                        throw ex;
                    }
                }
                await _unitOfWork.BulkToDBAsync<Order_OrderGoods>(bulkGoods);
                await _unitOfWork.BulkToDBAsync<Logistics_BoxDetail>(bulkBoxDetails);
                _logger.LogWarning("导入订单成功条数:" + orders.Count);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex.Message, ex);
                throw ex;
            }
        }

        async Task IOrderRepository.AddWinitLog(string winitOrderId, string transferNumber)
        {
            string sql = @"insert WinitOrderInfo values(@winitOrderId,@transferNumber)";
            await _unitOfWork.Connection.ExecuteAsync(sql, new
            {
                winitOrderId = winitOrderId,
                transferNumber = transferNumber
            }, null, null, System.Data.CommandType.Text);
        }

        /// <summary>
        /// 添加一个渠道下单时的渠道单号与XYY转单号关系记录
        /// </summary>
        /// <param name="channelId"></param>
        /// <param name="channelOrderId"></param>
        /// <param name="transferNumber"></param>
        /// <returns></returns>
        async Task IOrderRepository.AddChannelOrderInfoRecord(int channelId, string channelOrderId, string transferNumber)
        {
            string sql = @"insert ChannelOrderInfoRecord values(@ChannelOrderId,@TransferNumber,@ChannelId,getdate())";
            await _unitOfWork.Connection.ExecuteAsync(sql, new
            {
                ChannelOrderId = channelOrderId,
                TransferNumber = transferNumber,
                ChannelId = channelId
            }, null, null, System.Data.CommandType.Text);
        }

        [NonTrans]
        async Task<string> IOrderRepository.GetChannelOrderInfoRecord(int channelId, string transferNumber)
        {
            string sql = "select  top 1 ChannelOrderId from ChannelOrderInfoRecord(nolock) where transferNumber=@transferNumber and ChannelId=@channelId";
            return (await _unitOfWork.QueryBySqlAsync<string>(sql, null, new { transferNumber = transferNumber, channelId = channelId })).FirstOrDefault();
        }

        [NonTrans]
        async Task<string> IOrderRepository.GetWinitOrderNo(string transferNumber, string SystemNo = "")
        {
            string sql = "select  top 1 WinitOrderId from WinitOrderInfo(nolock) where transferNumber=@transferNumber";
            if (!string.IsNullOrEmpty(SystemNo)) { sql += $" or transferNumber='{SystemNo}' "; }
            return (await _unitOfWork.QueryBySqlAsync<string>(sql, null, new { transferNumber = transferNumber })).FirstOrDefault();
        }
        int IOrderRepository.GetPackagePrintQty(string customerOrderNo)
        {

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(_unitOfWork.ConnectionAddress))
            {
                conn.Open();
                using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("GetPackagePrintQty", conn))
                {
                    try
                    {

                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerOcrderNo", customerOrderNo));
                        var val = command.ExecuteScalar();
                        if (val == null || val == DBNull.Value)
                            throw new Exception("数据服务出错");
                        else
                            return (int)val;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        conn.Close();

                    }
                }
            };
        }
        async Task<IEnumerable<SysWarehouseFileLog>> IOrderRepository.GetWaitDownFile(int qty)
        {
            string sql = $@"select top {qty} * from SysWarehouseFileLog(nolock) where IsSysWarehouseFile=0 ";
            return await _unitOfWork.QueryBySqlAsync<SysWarehouseFileLog>(sql);
        }

        async Task IOrderRepository.UpdateDownFile(List<SysWarehouseFileLog> transfernums)
        {
            string sql = @"update SysWarehouseFileLog set IsSysWarehouseFile =1,UpdateTime=getdate() where Id in @Id";
            await _unitOfWork.ExecuteAsync(sql, new { Id = transfernums.Select(x => x.Id).ToList() });
        }

        async Task<PageResult<View_QueryOverCancelOrders>> IOrderRepository.QueryOverCancelOrders(QueryModel queryModel)
        {
            return await _unitOfWork.GetPagingListAsync<View_QueryOverCancelOrders>(queryModel);
        }

        async Task<List<dynamic>> IOrderRepository.QueryCustomerData(string sql)
        {
            return (await _unitOfWork.QueryBySqlAsync<dynamic>(sql)).ToList();
        }

        async Task<List<dynamic>> IOrderRepository.GetZZESBUpdateData(string lading)
        {
            IEnumerable<dynamic> enumerable = (await _unitOfWork.QueryBySqlAsync<dynamic>($@"select d.* from Order_Order(nolock)a 
join Logistics_BoxDetail(nolock)b on a.id = b.OrderId
join Logistics_Box(nolock)c on c.id = b.BoxId
left join ZZESBUpdateData(nolock) d on d.orderid = a.Id
where c.LadingBillNumber = '{lading}'"));
            return new List<dynamic>(enumerable);
        }

        async Task IOrderRepository.SetUnusualNotTransfer(string trackingNumber, UnusualReasonType UnusualReasonType, bool IsReprocessing)
        {
            string sql = "SetOrderUnusualReason";
            await ExecuteSqlNotTransferAsync(sql, new { TrakcingNumber = trackingNumber, UnusualReason = UnusualReasonType.GetValue(), UserName = _unitOfWork.CurrentName, IsReprocessing = IsReprocessing });

        }
        async Task IOrderRepository.AddChannelOrderInfoRecord(string FCOrderId, string transferNumber, int channelId)
        {
            string sql = @"pro_addChannelOrderInfoRecord";
            await ExecuteSqlNotTransferAsync(sql, new
            {
                FCOrderId = FCOrderId,
                transferNumber = transferNumber,
                ChannelId = channelId
            });
        }

        async Task<PageResult<BagDeclareLog>> IOrderRepository.GetBagDeclareLogs(QueryModel queryModel)
        {

            var pageResult = await _unitOfWork.GetPagingListAsync<BagDeclareLog>(queryModel, 60);
            return pageResult;

        }
        #region 订单管理

        async Task<OrderQtys> IOrderRepository.GetCustomerDraftOrderCount(QueryModel qm)
        {
            string where = GetCustomerWhere(qm);
            string sql2 = "select count(0) Qty,10000 OrderStatus from Order_DraftOrder(nolock) where 1=1 " + where + "";
            return (await _unitOfWork.QueryBySqlAsync<OrderQtys>(sql2)).FirstOrDefault();
        }
        public string GetCustomerWhere(QueryModel qm)
        {
            //移除状态
            var status = qm.QueryParamer.FirstOrDefault(x => x.Filed == "OrderStatus");
            if (status != null)
            {
                qm.QueryParamer.Remove(status);
            }
            //修改批量查询方式
            var numbers = qm.QueryParamer.FirstOrDefault(x => x.Filed == "SearchNumberList");
            if (numbers != null)
            {
                numbers.Filed = "ReferenceNo";
                numbers.Method = "In";
            }
            var PostDSStatus = qm.QueryParamer.FirstOrDefault(x => x.Filed == "PostDSStatus");
            if (PostDSStatus != null)
            {
                qm.QueryParamer.Remove(PostDSStatus);
            }
            //修正查询条件
            var country = qm.QueryParamer.FirstOrDefault(x => x.Filed == "ReceiverCountryCode");
            if (country != null)
            {
                country.Filed = "CountryCode";
            }

            var channel = qm.QueryParamer.FirstOrDefault(x => x.Filed == "CreateOrderChannelId");
            if (channel != null)
            {
                channel.Filed = "ChannelId";
            }

            var receiveTime = qm.QueryParamer.Where(x => x.Filed == "ReceiveTime");
            if (receiveTime != null && receiveTime.Count() > 0)
            {
                qm.QueryParamer.RemoveAll(x => x.Filed == "ReceiveTime");
            }

            var IsOnline = qm.QueryParamer.FirstOrDefault(x => x.Filed == "IsOnline");
            if (IsOnline != null)
            {
                qm.QueryParamer.Remove(IsOnline);
            }

            var FullName = qm.QueryParamer.FirstOrDefault(x => x.Filed == "ReceiverName");
            if (FullName != null)
            {
                FullName.Filed = "FullName";
            }
            var MpsOrder = qm.QueryParamer.FirstOrDefault(x => x.Filed == "MpsOrderId");
            if (MpsOrder != null)
            {
                MpsOrder.Filed = "MoreThanOneTicket";
                MpsOrder.Method = "Eq";
            }
            return GetWhere(qm.QueryParamer) + "  and Status<>1 and Status<>-1 ";
        }
        async Task<List<OrderQtys>> IOrderRepository.GetOrderQty(QueryModel qm)
        {
            if (!qm.QueryParamer.Exists(x => x.Filed == "SearchNumberList"))
            {
                string sql = "select count(0)Qty,OrderStatus from Order_Order(nolock) where 1=1" + GetWhere(qm.QueryParamer) + " group by OrderStatus";
                return (await _unitOfWork.QueryBySqlAsync<OrderQtys>(sql)).ToList();
            }
            else
            {
                string sql2 = GetSql2(qm);
                string whereString = GetCWhere(qm);
                sql2 += "\r\n select count(0)Qty,OrderStatus from #tempOrders where 1=1 " + whereString + " group by OrderStatus";
                return (await _unitOfWork.QueryBySqlAsync<OrderQtys>(sql2)).ToList();
            }
        }
        private string GetSql2(QueryModel qm)
        {
            var p = qm.QueryParamer.First(x => x.Filed == "SearchNumberList");
            qm.QueryParamer.Remove(p);
            var xml = string.Join("\r\n", p.Value.Split(',').Select(x => $"<NumberList><Number>{x}</Number></NumberList>"));
            string sql2 = @"
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_searchs') and type='U')
    drop table #temp_searchs
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tempOrders') and type='U')
    drop table #tempOrders
DECLARE @idoc int
                                            DECLARE @doc varchar(max)
                                            SET @doc ='
                                            <ROOT>
                                            {0}
                                            </ROOT>'
                                            --Create an internal representation of the XML document.
                                             EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
                                            -- SELECT stmt using OPENXML rowset provider 
											select * into  #temp_searchs
                                            FROM   OPENXML (@idoc, '/ROOT/NumberList',2)
                                                     WITH (Number nvarchar(64))

                                             select * into #tempOrders from (
                                            select * from  Order_Order(nolock) 
	                                             join #temp_searchs(nolock) on Order_Order.CustomerOrderNo = #temp_searchs.Number
	                                             union 
	                                             select * from  Order_Order(nolock) 
	                                             join #temp_searchs(nolock) on Order_Order.TransferNumber = #temp_searchs.Number
	                                            union 
	                                             select * from  Order_Order(nolock) 
	                                             join #temp_searchs(nolock) on Order_Order.TrackingNumber = #temp_searchs.Number)zz

";
            return string.Format(sql2, xml);
        }
        public string GetCWhere(QueryModel queryModel)
        {
            string where = "";
            if (queryModel.QueryParamer != null && queryModel.QueryParamer.Any(x => x.Filed == "OrderStatus"))
                where += " and OrderStatus = " + queryModel.QueryParamer.First(x => x.Filed == "OrderStatus").Value;
            return where;
        }
        async Task<PageResult<CustomerOrder>> IOrderRepository.GetCustomerOrderPager(QueryModel qm)
        {
            PageResult<CustomerOrder> results = new PageResult<CustomerOrder>();
            if (!qm.QueryParamer.Exists(x => x.Filed == "SearchNumberList"))
            {
                var order = await _unitOfWork.GetPagingList2Async<Order_Order>(qm);
                results.Result = order.Result.CopyList<CustomerOrder>().ToList();
                results.TotalCount = order.TotalCount;
            }
            else
            {
                List<string> appendSearchNumberList = new List<string>();
                string[] SearchNumberList = qm.QueryParamer.First(x => x.Filed == "SearchNumberList").Value.Split(',');
                foreach (string s in SearchNumberList)
                {
                    if (s.Contains("U000") && s.StartsWith("FBA"))
                    {
                        appendSearchNumberList.Add(s.Substring(0, s.Length - 7));
                    }
                    else
                    {
                        appendSearchNumberList.Add(s);
                    }
                }

                qm.QueryParamer.First(x => x.Filed == "SearchNumberList").Value = String.Join(',', appendSearchNumberList.ToArray());

                string sql2 = GetSql2(qm) + @"  
            select * from (
	            select Row_Number()over(order by {0})rowId,Id from #tempOrders 		
		            WHERE 1=1  {1}
		            )T   
	            join Order_Order(nolock) b on t.Id = b.Id where rowId between  {2} and {3};
                   select count(0) from #tempOrders(nolock)  WHERE 1=1  {1}
	               ";
                string sortString = GetSortString(qm);
                string whereString = GetCWhere(qm);
                sql2 = string.Format(sql2, sortString, whereString, (qm.PageIndex - 1) * qm.PageSize + 1, qm.PageSize * qm.PageIndex);
                var grid = await _unitOfWork.Connection.QueryMultipleAsync(sql2, transaction: _unitOfWork.Transaction, commandTimeout: 600);
                results.Result = grid.Read<CustomerOrder>();
                results.TotalCount = grid.Read<int>().SingleOrDefault();
            }
            string sql = "select status as  LatestLogisticsStatus , targettime as LatestLogisticsTime,LastMessage,OrderId as Id from Logistics_Trace(nolock) where OrderId in @OrderId ";
            var r = await _unitOfWork.QueryBySqlAsync<CustomerOrder>(sql, null, new { OrderId = results.Result.Select(x => x.Id).ToArray() });
            foreach (var item in results.Result)
            {
                var t = r.FirstOrDefault(x => x.Id == item.Id);
                if (t != null)
                {
                    item.LastMessage = t.LastMessage;
                    item.LatestLogisticsStatus = t.LatestLogisticsStatus;
                    item.LatestLogisticsTime = t.LatestLogisticsTime;
                }
            }

            string sql4 = "select OrderId as Id,CustomerSettlement,BranchOfficeSettlement from Order_MpsOrder(nolock) where OrderId in @OrderId";
            var r3 = await _unitOfWork.QueryBySqlAsync<CustomerOrder>(sql4, null, new { OrderId = results.Result.Select(x => x.Id).ToArray() });
            foreach (var item in results.Result)
            {
                var t = r3.FirstOrDefault(x => x.Id == item.Id);
                if (t != null)
                {
                    item.CustomerSettlement = t.CustomerSettlement;
                    item.BranchOfficeSettlement = t.BranchOfficeSettlement;
                }
            }

            string sql3 = "select CFright,OrderId as Id from order_fee(nolock) where orderId in @OrderId";
            var r2 = await _unitOfWork.QueryBySqlAsync<CustomerOrder>(sql3, null, new { OrderId = results.Result.Select(x => x.Id).ToArray() });
            foreach (var item in results.Result)
            {
                var t = r2.FirstOrDefault(x => x.Id == item.Id);
                if (t != null)
                {
                    item.CFright = t.CFright;
                }
            }
            results.PageIndex = qm.PageIndex;
            results.PageSize = qm.PageSize;
            return results;
        }
        private static string GetSortString(QueryModel qm)
        {
            string sortString = "Id";
            if (qm.QuerySort == null || qm.QuerySort.Count == 0)
            {
            }
            else
            {
                foreach (var item in qm.QuerySort)
                {
                    sortString += " " + item.Field + " " + (item.IsDesc ? "DESC" : "") + ",";
                }
                sortString = sortString.TrimEnd(',');
            }

            return sortString;
        }
        async Task<CustomerOrder> IOrderRepository.GetCustomerOrder(int id)
        {
            Order_Order order = await GetAsync(id);
            var data = (await _unitOfWork.QueryBySqlAsync<Order_UnusualReason>("select top 1 UnusualReason from   Order_UnusualReason(nolock) where OrderId =" + id)).FirstOrDefault();

            CustomerOrder dto = order.Copy<CustomerOrder>();
            if (data != null)
            {
                dto.UnusualReason = data.UnusualReason.ToString();
            }
            else
            {
                dto.UnusualReason = "无";
            }
            switch (dto.OrderStatus)
            {
                case 1:
                    dto.StrOrderStatus = "待揽件";
                    break;
                case 2:
                    dto.StrOrderStatus = "处理中";
                    break;
                case 3:
                    dto.StrOrderStatus = "已出库";
                    break;
                case 4:
                    dto.StrOrderStatus = "已退件";
                    break;
                case -1:
                    dto.StrOrderStatus = "已取消";
                    break;
                case 0:
                    dto.StrOrderStatus = "已取消";
                    break;
                case -2:
                    dto.StrOrderStatus = "已取消";
                    break;
            }

            //物流简码和服务商 

            await GetTrackingShippingSerivce(dto);

            dto.Goods = (await _unitOfWork.QueryAsync<Order_OrderGoods>(x => x.OrderId == order.Id)).ToList();
            dto.FristTrackingLogs = (await _unitOfWork.QueryAsync<Logistics_TrackingLog>(x => x.OrderId == order.Id)).ToList();
            return dto;


        }
        public async Task GetTrackingShippingSerivce(CustomerOrder dto)
        {
            var allCodesSql = "select * from Logistics_ChannelFirstCode(nolock)";

            var allCodes = (await _unitOfWork.QueryBySqlAsync<dynamic>(allCodesSql)).ToList();

            allCodes.ForEach(x => x.FirstCode = (x.FirstCode as string).Replace("\r", "").Replace("\n", "").Trim());
            dynamic code = null;
            var codes = allCodes.Where(x => x.ChannelId == dto.ChannelId);
            if (codes.Count() == 1)
            {
                code = codes.First();
            }
            else if (codes.Count() > 1)
            {
                code = codes.Where(c => !string.IsNullOrEmpty(c.Regex) && (c.Regex as string).Split(',', ' ').Any(y => dto.TrackingNumber.StartsWith(y))).FirstOrDefault();
            }
            if (code != null)
            {
                dto.ShippingServiceName = code.FirstCode;
                dto.ShippingServiceUrl = code.Url;
            }
        }
        async Task<CustomerOrder> IOrderRepository.GetCustomerDraftOrder(int id)
        {
            string sql = @"select ReferenceNo as CustomerOrderNo,
                                                CountryCode as ReceiverCountryCode,
                                                b.Name as ChannelName,
                                                Weight as CustomerWeight,
                                                CodFee as CodFee,
                                                CodCurrency as CodCurrency,
                                                FullName as ReceiverName,
                                                Phone as ReceiverPhone, 
                                                Email as ReceiverEmail,
                                                City as ReceiverCity,
                                                a.Id,
                                                State as ReceiverState,
                                                ZipCode as ReceiverZipCode,
                                                Street as ReceiverStreet,
                                                House as House,
                                                IOSS as IOSS,
                                                a.ErrorMessage,
                                                Status as DraftStatus,
                                                0 as IsOnline from Order_DraftOrder(nolock)a
												left join Logistics_Public(nolock)b on a.ChannelCode=b.Code 
												where a.id =" + id;
            var order = (await _unitOfWork.QueryBySqlAsync<CustomerOrder>(sql)).FirstOrDefault();
            order.HasIOSS = !string.IsNullOrEmpty(order.ioss);
            if (order.DraftStatus == 0)
            {
                order.StrDraftStatus = "待处理";
            }
            else
            {
                order.StrDraftStatus = "失败";
            }
            if (order == null)
                throw new Exception("未找到相关草稿订单");
            var goods = await _unitOfWork.QueryBySqlAsync<Order_OrderGoods>("select *,ProductNo as GoodsNumber from Order_DraftOrderGoods(nolock) where DraftOrderId=" + id);
            order.Goods = goods.ToList();
            return order;
        }
        async Task<PageResult<CustomerOrder>> IOrderRepository.GetCustomerDraftOrderList(QueryModel qm)
        {
            string where = GetCustomerWhere(qm);
            string sql = @"
            select  
                    b.Id,
                    b.ErrorMessage,
                    b.ReferenceNo as  CustomerOrderNo,
                    b.CountryCode as  ReceiverCountryCode,
                    b.CreateTime,
                    b.FullName as  ReceiverName,
                    b.Weight as CustomerWeight,
                    d.Name as ChannelName,
                    b.Status as DraftStatus
                    ,b.MoreThanOneTicket MpsOrderId
                from(
              select Row_Number() over(order by {0})rowId,Id from Order_DraftOrder 		
		            WHERE 1 = 1  {1}
		            )T
                join Order_DraftOrder(nolock) b on t.Id = b.Id  
	                left join Logistics_Public(nolock)d on d.Code = b.ChannelCode 
                where rowId between  {2}
            and {3};
            select count(0) from Order_DraftOrder(nolock)  WHERE 1=1  {1}
	               ";
            string sort = GetSortString(qm);
            sql = string.Format(sql, sort, where, (qm.PageIndex - 1) * qm.PageSize + 1, qm.PageSize * qm.PageIndex);

            var grid = await _unitOfWork.Connection.QueryMultipleAsync(sql, transaction: _unitOfWork.Transaction, commandTimeout: 600);

            var pr = new PageResult<CustomerOrder>();
            pr.Result = grid.Read<CustomerOrder>();
            pr.TotalCount = grid.Read<int>().SingleOrDefault();
            pr.PageIndex = qm.PageIndex;
            pr.PageSize = qm.PageSize;
            return pr;
        }
        async Task<List<CustomerOrder>> IOrderRepository.GetCustomerOrderList(QueryModel qm)
        {
            if (!qm.QueryParamer.Exists(x => x.Filed == "SearchNumberList"))
            {
                string where = GetWhere(qm.QueryParamer);
                return (await _unitOfWork.QueryBySqlAsync<CustomerOrder>("select * from CustomerOrder(nolock) where 1=1 " + where)).ToList();
            }
            else
            {
                string sql2 = GetSql2(qm);
                string whereString = GetCWhere(qm);
                sql2 += "\r\n select b.* from(select Id from #tempOrders    where 1=1 " + whereString + " ) a join CustomerOrder(nolock)b on a.Id=b.Id";
                return (await _unitOfWork.QueryBySqlAsync<CustomerOrder>(sql2)).ToList();
            }
        }

        async Task IOrderRepository.UpdateTrackingNumber(List<UpdateDataDto> updateDatas)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in updateDatas)
            {
                string serviceOrderNumebr = item.ServiceOrderNumebr;
                sb.AppendLine($"update order_order set TransferNumber='{item.TransferNumber}',trackingnumber = '{item.TrackingNumber}',SendOutTime='{item.ReceiveTime.ToString_yyyyMMddHHmmss()}'," +
                    $"ServiceOrderNumber4='{serviceOrderNumebr}',ReceiveTime='{item.ReceiveTime.ToString_yyyyMMddHHmmss()}',OrderStatus=3 where Id = '{item.Id}'");
                if (!string.IsNullOrEmpty(item.ServiceOrderNumebr))
                {
                    sb.AppendLine(@$"  if (select count(0) from Order_ZZESBJJData(nolock) where SystemNo = '{item.SystemNo}')=0
                              begin
	                            insert Order_ZZESBJJData(CreateTime,SystemNo,Weight,CountryCode,DownloadTime,TransferNumber,TrackingNumber)
		                            select  getdate(),SystemNo,CustomerWeight,ReceiverCountryCode,'{item.ReceiveTime.ToString_yyyyMMddHHmmss()}','{item.TransferNumber}','{item.TrackingNumber}' from Order_Order (nolock) where Id = '{item.Id}'
	                            end");
                }
                //写入两条同步TASK
            }
            await _unitOfWork.ExecuteAsync(sb.ToString());
        }

        async Task<List<Order_Order>> IOrderRepository.GetWaitSyncCustomerTracking(int qty)
        {
            string sql = $@"
select  top 30  Id,SystemNo,CustomerOrderNo,TrackingNumber as TransferNumber,CreateTime,ChannelId from Order_Order(nolock) 
where  CustomerId=1238  and orderstatus=1 and datediff(minute ,updatetime,getdate())>120
and DATEDIFF(HOUR,CreateTime,getdate())>48 and trackingnumber like 'xyy%'
and CreateTime>'2023-11-15'";
            var list = (await _unitOfWork.QueryBySqlAsync<Order_Order>(sql)).ToList();
            foreach (var item in list)
            {
                if (item.ChannelId == 461)
                {
                    item.ServiceOrderNumber4 = "DWESB";
                }
                else
                {
                    item.ServiceOrderNumber4 = string.Empty;
                }
            }
            return list;
        }

        /// <summary>
        /// 取待同步的轨迹（100条）
        /// </summary>
        /// <param name="qty"></param>
        /// <returns></returns>
        public async Task<List<Order_CustomerAsyncTracking>> GetWaitAsyncZZESBData(int qty)
        {
            string sql = $@"select top {qty} * from Order_CustomerAsyncTracking(nolock) where AnsycTime is null  and OperTime<getdate() and ChannelId=227";
            return (await _unitOfWork.QueryBySqlAsync<Order_CustomerAsyncTracking>(sql)).ToList();
        }

        public async Task<List<Order_CustomerAsyncTracking>> GetWaitAsyncKTXESBData(int qty)
        {
            string sql = $@"select top {qty} * from Order_CustomerAsyncTracking(nolock) where AnsycTime is null  and OperTime<getdate() and ChannelId=569";
            return (await _unitOfWork.QueryBySqlAsync<Order_CustomerAsyncTracking>(sql)).ToList();
        }

        public async Task InsertFirstToDB(List<Order_CustomerAsyncTracking> list)
        {
            string batchSql = "";
            foreach (var item in list)
            {
                batchSql +=
                  $@"insert Logistics_TrackingLog(TriggerTime,Location,Status,CreateUserName,CreateTime,UpdateUserName,UpdateTime,Remark,TransferNumber,OrderId,StatusString)
                    values('{item.OperTime}','{item.Loaction}',4,'admin',getdate(),null,null,null,'{item.TransferNumber}',{item.OrderId},'{item.Event}') ";
            }

            if (!string.IsNullOrEmpty(batchSql))
            {
                await _unitOfWork.ExecuteAsync(batchSql);
            }

        }
        public async Task UpdateEnd(List<int> ids)
        {
            await _unitOfWork.UpdateByConditionAsync<Order_CustomerAsyncTracking, bool>(x => x.Id.In(ids), new { AnsycTime = DateTime.Now });
        }

        public async Task SetNextUpdate(List<Order_Order> noUpdateData)
        {
            var ids = noUpdateData.Select(x => x.Id).ToList();
            string sql = @"update order_order set updatetime=getdate() where id in @ids";
            await _unitOfWork.ExecuteAsync(sql, new { ids });
        }

        public async Task RemoveWinitLog(string winitOrderId, string transferNumber)
        {
            string sql = @"delete WinitOrderInfo where WinitOrderId='" + winitOrderId + "'";
            await _unitOfWork.ExecuteAsync(sql);
        }



        #endregion


    }
}