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 AddChannelOrderInfoRecord(int channelId, string channelOrderId, string transferNumber);

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

        [NonTrans]
        Task<string> GetWinitOrderNo(string transferNumber);
        Task<BagDeclareRequest> GetWaitBoxDeclareRequest(int channelId, string ladingBillNumber = 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>> GetWaitAsyncESBDate(int qty);
        Task UpdateEnd(List<int> ids);
        Task SetNextUpdate(List<Order_Order> noUpdateData);
    }

    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;
            }
        }

        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;
        }

        [NonTrans]
        async Task<List<BagDeclareRequest>> IOrderRepository.GetWaitBoxDeclareRequest(string[] serviceChannelCodeList)
        {
            List<BagDeclareRequest> resultList = new List<BagDeclareRequest>();
            foreach (var ServiceChannelCode in serviceChannelCodeList)
            {
                var 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)
        {
            var channel = await _logisticsRepository.GetAsync(channelId);
            BagDeclareRequest bagDeclareRequest = new BagDeclareRequest();
            bagDeclareRequest.Channel = channel;

            string specifiedAwbCondition = null;
            if (string.IsNullOrWhiteSpace(ladingBillNumber))
            {
                specifiedAwbCondition = " and c.LadingBillNumber is not null and isnull(b.IsConfim,0) =0 ";
            }
            else
            {
                specifiedAwbCondition = $" and c.LadingBillNumber ='{ladingBillNumber}'";
            }

            string sql = $@"select a.ChannelId,e.Id,e.OriginPort as OriginPort,e.Destination as  Port,A.ServicesOrderNo as ServiceNo,a.ReceiverCountryCode as CountryCode, a.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>'2022-08-25' ";
            //写死一个数据做测试
            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 from Order_Order(nolock)a 
        where  a.ChannelId =449 and a.Id=218907964";
            var items = await _unitOfWork.QueryBySqlAsync<BoxDetailItem>(sql, commandTimeout: 180);
            if (channel.Id == 371)
            {
                foreach (var i in items)
                {
                    i.Weight = i.OPWeight;
                }
            }
            var dicts = items.GroupBy(x => new { x.CountryCode, x.BoxNumber })
                .ToDictionary(x => x.Key.BoxNumber, x => x.ToList());
            bagDeclareRequest.BatchDeclare = dicts;
            return bagDeclareRequest;
        }


        [NonTrans]
        async Task<List<OrderConfimRequest>> IOrderRepository.GetWaitConfimOrders(int qty)
        {
            int[] IdList = new int[]{ 458,540,
539,
538,
537,
536,549,
550};
            Dictionary<int, int[]> customerDic = new Dictionary<int, int[]>() { { 341,new int[] { 503,
504,
505,
557,
558,
559} } };
            int[] testList = new int[] { 536 };//测试用  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 a.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={customerDic.FirstOrDefault().Key} and a.ChannelId in @customerChannelId  and c.ReceiveTime>'2022-12-06')t1";
            return (await _unitOfWork.QueryBySqlAsync<OrderConfimRequest>(sql, 300, new { Id = IdList, 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 sql = "select  top 1 WinitOrderId from WinitOrderInfo(nolock) where transferNumber=@transferNumber";
            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(System.Configuration.ConfigurationManager.ConnectionStrings["defaultConnection"].ConnectionString))
            {
                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)
            {
                sb.AppendLine($"update order_order set TransferNumber='{item.TransferNumber}',trackingnumber = '{item.TrackingNumber}',SendOutTime='{item.ReceiveTime.ToString_yyyyMMddHHmmss()}'," +
                    $"ServiceOrderNumber4='HNESB',ReceiveTime='{item.ReceiveTime.ToString_yyyyMMddHHmmss()}',OrderStatus=3 where Id = '{item.Id}'");
                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 {qty} Id,SystemNo,CustomerOrderNo,TrackingNumber as TransferNumber from Order_Order(nolock) where  CustomerId=1238  and orderstatus=1 and datediff(minute ,updatetime,getdate())>60
";
            return (await _unitOfWork.QueryBySqlAsync<Order_Order>(sql)).ToList();
        }

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

        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 });

        }

        #endregion


    }
}
