using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Core.Standard.AliYun;
using XYY.Core.Standard.ExcelHelper.MSExcelHelper;
using XYY.Model.Standard.MpsOrder;
using XYY.Core.Standard.Data.Infrastructure;
using Dapper;
using XYY.Model.Standard.Order;
using XYY.Service.Standard.Channel;
using XYY.Model.Standard.Dto.MpsOrder;
using XYY.Core.Standard.Mvc;
using XYY.Data.Standard.Order;
using XYY.Model.Standard.Finance;
using XYY.Service.Standard.Finance.Charging.CustomerOrderFee;
using XYY.Service.Standard.Finance.Charging.Base;
using System.IO.Compression;
using System.Xml;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;
using ICSharpCode.SharpZipLib.Checksum;
using ICSharpCode.SharpZipLib.Zip;
using XYY.Model.Standard;
using XYY.Data.Standard.UserExtend;
using Newtonsoft.Json;
using XYY.Common.Standard;
using XYY.Model.Standard.User;
using XYY.Model.Standard.Channel;
using System.Threading.Channels;
using System.Globalization;

namespace XYY.Service.Standard.Order
{
    public interface IMoreThanOneTicketService
    {
        Task AddDraftOrderData(List<Order_MpsOrder> waitAddJob);
        Task<List<Order_MpsOrder>> LoadData(string fileUrl, int customerId);

        Task SaveMpsData(List<Order_MpsOrder> orders, string FileUrl);

        Task<Order_MpsOrder> Get(int orderId, int draftOrderId);
        Task<Order_MpsOrderBox> ConfirmCustomerOrderReceipt(ConfirmCustomerOrderReceiptInput input);
        Task<ApiJsonModel> CustomerOrderOut(string InputNumber, int opStatus);
        Task BoxStatusOp(int id, BoxStatus boxStatus);
        Task<IEnumerable<Order_MpsOrder>> GetMpsOrders(IEnumerable<int> Ids);
        Task UpdateDraftOrderErrorMessage(int DraftOrderId, string errorMessage);
        Task UpdateBoxNumbser(Dictionary<string, string> boxInfo);
        Task<byte[]> OutBoxInfo(int[] OrderIds, bool FbaChannelView = false);
        Task UpdateMpsOrder(UpdateMpsOrderInfo updateMpsOrderInfo);
        Task<byte[]> OutMpsOrderData(int[] Ids);
        Task<bool> ImportMpsBoxInfo(System.IO.Stream formFile, string UserName);
        Task<IEnumerable<MpsBoxCount>> GetMpsBoxCount(IEnumerable<int> mpsOrderIds);
        Task<bool> ModifyCreateOrderChannelId(MpsModifyCreateOrderChannelId mpsModify, UserContent user);
        Task<bool> ImportFBAChannelOrder(System.IO.Stream formFile);
        Task RecalculateBilling();
        Task RecalculateCustomerFee();
    }
    public class InputMoreThanOneTicketService : IMoreThanOneTicketService
    {
        private IUnitOfWork _unitOfWork;
        private IFBA_IncidentalRepository _incidentalRepository;
        private XYY.Core.Standard.AliYun.IAliYunPostFileSerivce aliYunPostFileSerivce;
        public InputMoreThanOneTicketService(IUnitOfWork unitOfWork, IFBA_IncidentalRepository incidentalRepository, IAliYunPostFileSerivce aliYunPostFileSerivce)
        {
            _unitOfWork = unitOfWork;
            _incidentalRepository = incidentalRepository;
            this.aliYunPostFileSerivce = aliYunPostFileSerivce;
        }

        public InputMoreThanOneTicketService()
        {
        }

        public async Task<List<Order_MpsOrder>> LoadData(string fileUrl, int customerId)
        {
            var publics = await ChannelClientService.CacheGetAllPublic();
            var channels = await ChannelClientService.CacheGetAllChannel();
            System.Net.WebClient client = new System.Net.WebClient();
            var data = client.DownloadData(fileUrl);
            MSExcelHelper excelHelper = new MSExcelHelper();
            //根据渠道名称和收件人国家判断是否空行，并过滤空行
            var list = excelHelper.LoadDataByByte<Order_MpsOrderMpping, Order_MpsOrderExcelDto>(data);
                //?.Where(i => !string.IsNullOrWhiteSpace(i.ChannelName) && !string.IsNullOrWhiteSpace(i.ReceiverCountryCode)).ToList();
            StringBuilder stringBuilder = new StringBuilder();
            int i = 0;

            foreach (var item in list)
            {
                #region 数据判断赋值
                if (item.ChannelName == "美国空派专线")
                {
                    if (string.IsNullOrEmpty(item.ReceiverCompany)) { item.ReceiverCompany = "Amazon.com Services, Inc"; }
                    if (string.IsNullOrEmpty(item.ReceiverPhone)) { item.ReceiverPhone = "8888888888"; }
                }
                else if (item.ChannelName == "欧洲空派包税")
                {
                    if (string.IsNullOrEmpty(item.ReceiverCompany)) { item.ReceiverCompany = "Amazon Fulfillment Center"; }
                    if (string.IsNullOrEmpty(item.ReceiverPhone)) { item.ReceiverPhone = "8888888888"; }
                }
                else if (item.ChannelName == "加拿大空派专线")
                {
                    if (string.IsNullOrEmpty(item.ReceiverCompany)) { item.ReceiverCompany = "Amazon.com Services, Inc"; }
                    if (string.IsNullOrEmpty(item.ReceiverPhone)) { item.ReceiverPhone = "8888888888"; }
                }
                #endregion
                i++;
                string defaultMessage = $"第{i}个明细错误:";
                string error = "";
                if (string.IsNullOrEmpty(item.CustomerOrderNumber))
                {
                    error += "订单号/客户单号-未填;";
                }
                if (string.IsNullOrEmpty(item.BoxNumber))
                {
                    error += "箱号-未填;";
                }
                if (string.IsNullOrEmpty(item.ChannelName))
                {
                    error += "线路产品-未填";
                }
                if (string.IsNullOrEmpty(item.ReceiverCountryCode))
                {
                    error += "收件人国家-未填";
                }
                if (string.IsNullOrEmpty(item.ReceiverCity))
                {
                    error += "城市-未填";
                }
                if (string.IsNullOrEmpty(item.ReceiverState))
                {
                    error += "州/省-未填";
                }
                if (string.IsNullOrEmpty(item.ReceiverZipCode))
                {
                    error += "邮编-未填";
                }
                if (string.IsNullOrEmpty(item.ReceiverStreet1))
                {
                    error += "地址-未填";
                }
                if (!string.IsNullOrEmpty(item.ReceiverStreet1) && item.ReceiverStreet1.Length > 105)
                {
                    error += "地址-长度超过105个字符";
                }

                if (string.IsNullOrEmpty(item.ReceiverName))
                {
                    error += "收件人名称-未填";
                }
                if (string.IsNullOrEmpty(item.CnName))
                {
                    error += "品名 中文描述-未填";
                }
                if (string.IsNullOrEmpty(item.EnName))
                {
                    error += "品名 英文描述-未填";
                }

                if (item.GoodsWeight == 0)
                {
                    error += "单个产品净重（KG）-未填";
                }
                if (item.Quantity == 0)
                {
                    error += "产品件数-未填";
                }

                if (item.BoxWeight == 0)
                {
                    error += "单箱实重(KG)-未填";
                }
                if (item.Length == 0)
                {
                    error += "长-未填";
                }
                if (item.Width == 0)
                {
                    error += "宽-未填";
                }
                if (item.Height == 0)
                {
                    error += "高-未填";
                }
                if (item.GoodsValue == 0)
                {
                    error += "单个产品申报价值-未填";
                }
                if (item.GoodsValueSum == 0)
                {
                    error += "申报总价值-未填";
                }
                if (string.IsNullOrEmpty(item.Currency))
                {
                    error += "申报币种USD/EUR-未填";
                }
                //if (string.IsNullOrEmpty(item.Magnetic))
                //{
                //    error += "带磁-未填";
                //}
                //if (string.IsNullOrEmpty(item.Electric))
                //{
                //    error += "带电-未填";
                //}
                //if (string.IsNullOrEmpty(item.Liquid))
                //{
                //    error += "危险品-未填";
                //}
                if (string.IsNullOrEmpty(item.Meterial))
                {
                    error += "材质-未填";
                }
                if (string.IsNullOrEmpty(item.Usage))
                {
                    error += "用途-未填";
                }
                if (string.IsNullOrEmpty(item.Brand))
                {
                    error += "品牌-未填";
                }
                if (string.IsNullOrEmpty(item.ModelNumber))
                {
                    error += "型号-未填";
                }
                if ( item.ChannelName?.Trim() == "欧洲空派包税" || item.ChannelName?.Trim() == "欧洲空派VAT")
                {
                    if (string.IsNullOrEmpty(item.SaleLink))
                    {
                        error += "销售链接-未填";
                    }
                    else if (item.SaleLink.Length < 6)
                    {
                        error += "销售链接-链接地址填写不正确";
                    }
                    //if (item.SalePrice == 0)
                    //{
                    //    error += "销售价格-未填";
                    //}
                }
                if (string.IsNullOrEmpty(item.HsCode))
                {
                    error += "海关编码-未填";
                }
                if (string.IsNullOrEmpty(item.DeclarationMethod))
                {
                    error += "报关方式-未填";
                }
                if (Math.Round(item.GoodsValueSum, 4) != Math.Round(item.GoodsValue * item.Quantity, 4))
                {
                    error += "单个产品申报价值*产品件数不等于申报总价值";
                }
                var pc = publics.FirstOrDefault(x => x.Name == item.ChannelName?.Trim());
                if (pc == null)
                    error += "找不到公开渠道" + item.ChannelName;
                if (pc!=null&&!pc.Enable)
                    error += "渠道已暂停使用";
                var channel = channels.FirstOrDefault(x => x.PublicCode == pc?.Code);
                if (channel == null)
                    error += "找不到渠道" + pc?.Code;

                if (pc!=null&&pc.ChannelType != Model.Standard.Enums.ChannelType.FBA)
                {
                    error += $"公开渠道为非FBA渠道{pc.Name};";
                }
                //if (!string.IsNullOrEmpty(item.CustomerOrderNumber)&& !string.IsNullOrEmpty(item.BoxNumber) && item.CustomerOrderNumber.StartsWith("FBA"))
                //{
                //    //如果是FBA单号，箱号必须也为FBA箱号
                //    var regex = new System.Text.RegularExpressions.Regex(@$"^{item.CustomerOrderNumber}U\d{{6,}}$");
                //    if (!regex.IsMatch(item.BoxNumber))
                //    {
                //        //error += ($"该订单为FBA订单，请填写正确的FBA箱号。如{item.CustomerOrderNumber}U000001");
                //    }
                //}

                if (!string.IsNullOrEmpty(error))
                {
                    item.ErrorMessage = defaultMessage + error;
                }
                if (channel != null)
                    item.ChannelId = channel.Id;
                if (pc != null)
                {
                    item.ChannelCode = pc.Code;
                    item.ChannelName = pc.Name;
                }

            }


            return ConvertOrder(list, customerId, data);
        }

        public async Task AddDraftOrderData(List<Order_MpsOrder> waitAddJob)
        {
            string batchId = Guid.NewGuid().ToString();
            List<Order_DraftOrder> draftOrders = new List<Order_DraftOrder>();
            waitAddJob.ForEach(x =>
            {
                var od = new Order_DraftOrder
                {
                    ReferenceNo = x.CustomerOrderNumber,
                    CustomerId = x.CustomerId,
                    Company = x.ReceiverCompanyName,
                    //ChannelId = x.ChannelName,
                    City = x.ReceiverCity,
                    State = x.ReceiverState,
                    Street = x.ReceiverStreet1,
                    BatchId = batchId,
                    CountryCode = x.ReceiverCountry,
                    CreateChannelId = x.ChannelId,
                    ChannelId = x.ChannelId,
                    ChannelCode = x.ChannelCode,
                    FullName = x.ReceiverName,
                    ZipCode = x.ReceiverZipCode,
                    IsCod = false,
                    Weight = x.Boxs.Sum(x => Math.Max((int)((int)(x.Length * x.Width * x.Height) / 6000.0m * 1000), (int)(x.Weight * 1000))),
                    MoreThanOneTicket = true,
                    Phone = x.ReceiverPhone,
                    Status = DraftOrderStatus.UnFinished,
                    CreateTime = DateTime.Now
                };
                x.Weight = od.Weight;
                draftOrders.Add(od);
            });
            //保存草稿
            await _unitOfWork.BulkToDBAsync(draftOrders);
            //取数据
            var dbDatas = await _unitOfWork.QueryAsync<Order_DraftOrder>(x => x.BatchId == batchId);
            waitAddJob.ForEach(x =>
            {
                x.DraftOrderId = dbDatas.First(y => y.ReferenceNo == x.CustomerOrderNumber).Id;
                _unitOfWork.UpdateByPrimaryKeyAsync<Order_MpsOrder>("DraftOrderId", x.DraftOrderId, x.Id).Wait();
                //draftOrders.Add(x);
            });
        }

        public async Task SaveMpsData(List<Order_MpsOrder> orders, string FileUrl)
        {
            try
            {
                string guid = Guid.NewGuid().ToString();
                orders.ForEach(x =>
                {
                    x.UploadFileUrl = FileUrl;
                    x.BatchId = guid; x.Boxs.ForEach(y => { y.BatchId = guid; y.Goods.ForEach(z => z.BatchId = guid); });
                });
                await _unitOfWork.BulkToDBAsync(orders);
                var addOrders = await _unitOfWork.QueryBySqlAsync<Order_MpsOrder>(
                    "select * from Order_MpsOrder(nolock) where BatchId =@BatchId", param: new
                    {
                        BatchId = guid
                    });
                List<Order_MpsOrderBox> boxs = new List<Order_MpsOrderBox>();
                orders.ForEach(order =>
                {
                    order.Boxs.ForEach(box =>
                    {
                        box.MpsOrderId = addOrders.First(x => x.CustomerOrderNumber == order.CustomerOrderNumber).Id;
                        box.CustomerOrderNumber = order.CustomerOrderNumber;
                        order.Id = box.MpsOrderId;
                    });

                    boxs.AddRange(order.Boxs);
                });

                await _unitOfWork.BulkToDBAsync(boxs);
                var addBox = await _unitOfWork
                    .QueryBySqlAsync<Order_MpsOrderBox>("select * from Order_MpsOrderBox(nolock) where BatchId =@BatchId", param: new
                    {
                        BatchId = guid
                    });

                List<Order_MpsOrderGoods> bulkToDbGoods = new List<Order_MpsOrderGoods>();
                orders.ForEach(order =>
                {
                    order.Boxs.ForEach(box =>
                    {
                        var b = addBox.First(x => x.BoxNumber == box.BoxNumber && x.CustomerOrderNumber == box.CustomerOrderNumber);
                        box.Goods.ForEach(good =>
                        {
                            good.MpsBoxId = b.Id;
                            good.MpsOrderId = b.MpsOrderId;
                        });
                        box.Id = b.Id;
                        bulkToDbGoods.AddRange(box.Goods);
                    });
                });

                await _unitOfWork.BulkToDBAsync(bulkToDbGoods);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw ex;
            }
        }

        private List<Order_MpsOrder> ConvertOrder(List<Order_MpsOrderExcelDto> dtos, int customerId, byte[] data)
        {

            DateTime createtime = DateTime.Now;
            var orders = dtos.GroupBy(x => x.CustomerOrderNumber).Select(x => x.First());
            List<Order_MpsOrder> maps = new List<Order_MpsOrder>();
            //获取图片链接
            if (dtos.All(x => string.IsNullOrEmpty(x.ErrorMessage)))
            {
                var urls = ConvertImageUrl(data);
                if (urls != null && urls.Count > 0)
                {
                    for (int i = 0; i < dtos.Count; i++)
                    {
                        var url = urls.Where(x => (x.Row - 1) == i)?.FirstOrDefault();
                        if (url != null)
                        {
                            string aliYunFileUrl = $"sku/{customerId}/{dtos[i].ProductNo ?? "无产品编码"}/{Guid.NewGuid().ToString()}{System.IO.Path.GetExtension(url.FileUrl)}";
                            string aliUrl = aliYunPostFileSerivce.PostFile(aliYunFileUrl, url.FileUrl);
                            string pubUrl = aliYunPostFileSerivce.GetPublicCDN(aliUrl);
                            dtos[i].Imageurl = pubUrl;
                        }
                    }
                }
            }
            foreach (var dto in orders)
            {
                Order_MpsOrder order = new Order_MpsOrder();
                order.ReceiverCity = dto.ReceiverCity?.Trim();
                order.ReceiverCompanyName = dto.ReceiverCompany?.Trim();
                order.ReceiverCountry = dto.ReceiverCountryCode?.Trim();
                order.ReceiverEmail = dto.ReceiverEmail?.Trim();
                order.ReceiverName = dto.ReceiverName?.Trim();
                order.ReceiverPhone = dto.ReceiverPhone?.Trim();
                order.ReceiverState = dto.ReceiverState?.Trim();
                order.ReceiverStreet1 = dto.ReceiverStreet1?.Trim();
                order.ReceiverStreet2 = dto.ReceiverStreet2?.Trim();
                order.ReceiverZipCode = dto.ReceiverZipCode?.Trim();
                order.Remark = string.Join(";", dtos.Where(x => x.CustomerOrderNumber == dto.CustomerOrderNumber && !string.IsNullOrEmpty(x.ErrorMessage)).Select(x => x.ErrorMessage));
                order.ChannelId = dto.ChannelId;
                order.ChannelName = dto.ChannelName?.Trim();
                order.ChannelCode = dto.ChannelCode?.Trim();
                order.FbaOrderNumber = dto.FbaOrderNumber?.Trim();
                order.CustomerOrderNumber = dto.CustomerOrderNumber?.Trim();
                order.WarehouseCode = dto.FBAWareHouseCode?.Trim();
                order.CreateTime = createtime;
                order.CustomerId = customerId;
                order.DeclarationMethod = dto.DeclarationMethod?.Trim();
                order.Boxs = dtos.Where(x => x.CustomerOrderNumber == dto.CustomerOrderNumber).GroupBy(x => x.BoxNumber).Select(y =>
                    new Order_MpsOrderBox
                    {
                        Weight = y.First().BoxWeight,
                        Length = y.First().Length,
                        Width = y.First().Width,
                        Height = y.First().Height,
                        CustomerOrderNumber = order.CustomerOrderNumber?.Trim(),
                        BoxNumber = y.First().BoxNumber?.Trim(),
                        CreateTime = createtime,
                        Goods = dtos.Where(x => x.CustomerOrderNumber == dto.CustomerOrderNumber && x.BoxNumber == y.First().BoxNumber)
                        .Select(z => new Order_MpsOrderGoods
                        {
                            BoxNumber = y.First().BoxNumber?.Trim(),
                            CnName = z.CnName?.Trim(),
                            EnName = z.EnName?.Trim(),
                            Currency = z.Currency?.Trim(),
                            GoodsValue = z.GoodsValue,
                            HsCode = z.HsCode?.Trim(),
                            SaleLink = z.SaleLink?.Trim(),
                            Meterial = z.Meterial?.Trim(),
                            Quantity = z.Quantity,
                            ProductNo = z.ProductNo?.Trim(),
                            CreateTime = createtime,
                            Brand = z.Brand?.Trim(),
                            ModelNumber = z.ModelNumber?.Trim(),
                            Usage = z.Usage?.Trim(),
                            Weight = z.GoodsWeight,
                            Magnetic = z.Magnetic?.Trim() == "是" ? true : false,
                            Electric = z.Electric?.Trim() == "是" ? true : false,
                            Liquid = z.Liquid?.Trim() == "是" ? true : false,
                            SalePrice = z.SalePrice,
                            ProductImageUrl = z.Imageurl?.Trim(),
                        }).ToList()
                    }
                ).ToList();

                order.BoxQty = order.Boxs.Count;
                order.Magnetic = order.Boxs.Any(x => x.Goods.Any(x => x.Magnetic == true));
                order.Electric = order.Boxs.Any(x => x.Goods.Any(x => x.Electric == true));
                order.Liquid = order.Boxs.Any(x => x.Goods.Any(x => x.Liquid == true));
                maps.Add(order);
            }
            return maps;
        }
        #region 图片转换获取
        public List<ExcelSkuImage> ConvertImageUrl(byte[] data)
        {
            var tempPath = System.IO.Path.Combine(AppContext.BaseDirectory, "temp", "FBAOrderImgs");
            if (!System.IO.Directory.Exists(tempPath))
            {
                System.IO.Directory.CreateDirectory(tempPath);
            }
            string fileName = Guid.NewGuid().ToString();
            var zipPath = System.IO.Path.Combine(tempPath, fileName + ".zip");
            System.IO.File.WriteAllBytes(zipPath, data);
            List<ExcelSkuImage> skuItems = new List<ExcelSkuImage>();
            string dict = "";
            try
            {
                skuItems = GetSkuImages(zipPath, out dict);
            }
            catch (Exception ex)
            {
                //文件获取失败时，清理一次文件;
                if (System.IO.Directory.Exists(dict))
                    System.IO.Directory.Delete(dict, true);
                if (System.IO.File.Exists(zipPath))
                    System.IO.File.Delete(zipPath);

                //不抛出异常，没有就不做解析
            }

            if (skuItems.Count > 0)
            {
                skuItems = skuItems.OrderBy(x => x.Row).ToList();
                var first = skuItems.First();
            }
            return skuItems;
        }
        public class ExcelSkuImage
        {
            public int Row
            {
                get; set;
            }

            public string FileUrl
            {
                get; set;
            }

        }
        public List<ExcelSkuImage> GetSkuImages(string path, out string d)
        {
            d = path.Replace(".zip", "");
            try
            {
                System.IO.Compression.ZipFile.ExtractToDirectory(path, d);
                string drawings = System.IO.Path.Combine(d, "xl", "drawings", "drawing1.xml");
                if (!System.IO.File.Exists(drawings))
                    throw new Exception("未找到相关的图片");
                XmlDocument xdoc = new XmlDocument();
                xdoc.Load(drawings);

                //获取根节点
                XmlNode root = xdoc.DocumentElement;
                //获取根节点下的所有子节点
                XmlNodeList nodeList = root.ChildNodes;
                Dictionary<string, string> picRowDictionary = new Dictionary<string, string>();
                foreach (XmlNode nl in nodeList)
                {
                    XmlElement sondNode = (XmlElement)nl;
                    XmlNodeList descendDodeList = sondNode.ChildNodes;
                    XmlNodeList fromNodeList = descendDodeList[0].ChildNodes;
                    //取得行号
                    string row = fromNodeList.Item(2).InnerText.Trim();
                    XmlNodeList picNodeList = descendDodeList[2].ChildNodes;
                    XmlNodeList blipFillNodeList = picNodeList[1].ChildNodes;
                    XmlElement picElement = (XmlElement)blipFillNodeList.Item(0);
                    string picturePath = picElement.GetAttribute("r:embed").ToString();
                    if (picRowDictionary.ContainsKey(row))
                    {
                        if (picRowDictionary[row] != picturePath)
                            throw new Exception($"图片超出了单元行{row}，请修改后再次上传");
                        //int r = int.Parse(row) + 1;
                        //Console.WriteLine(r);
                        //picRowDictionary.Add(r.ToString(), picturePath);
                    }
                    else
                    {
                        picRowDictionary.Add(row, picturePath);
                    }

                }

                List<ExcelSkuImage> images = new List<ExcelSkuImage>();

                string drawingsRels = System.IO.Path.Combine(d, "xl", "drawings", "_rels", "drawing1.xml.rels");
                XmlDocument doc = new XmlDocument();

                doc.Load(drawingsRels);

                XmlNode relsRoot = doc.DocumentElement;

                XmlNodeList relsNlist = relsRoot.ChildNodes;
                string pathOfPicture = string.Empty;
                foreach (var picRow in picRowDictionary)
                {
                    foreach (XmlNode xn in relsNlist)
                    {
                        XmlElement xe = (XmlElement)xn;
                        if (xe.GetAttribute("Id").ToString() == picRow.Value)
                        {
                            pathOfPicture = xe.GetAttribute("Target").ToString().Replace("..", "").Replace("/", @"\");
                            pathOfPicture = d + "\\" + "xl" + pathOfPicture;
                            images.Add(new ExcelSkuImage
                            {
                                Row = int.Parse(picRow.Key),
                                FileUrl = pathOfPicture
                            });
                        }
                    }
                }

                return images;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ////无论如何清理解压文件
                //if (System.IO.Directory.Exists(d))
                //    System.IO.Directory.Delete(d, true);
            }
        }
        #endregion

        public async Task<byte[]> OutBoxInfo(int[] OrderIds, bool FbaChannelView = false)
        {
            var mpsOrderBoxsSql = string.Format(@"select a.* from Order_MpsOrderBox(nolock)a join Order_MpsOrder(nolock)b on a.MpsOrderId=b.Id
 join Order_Order(nolock) c on c.id=b.OrderId and c.OrderStatus>0 where b.OrderId in @OrderIds");
            var dbBoxs = (await _unitOfWork.QueryBySqlAsync<Order_MpsOrderBox>(mpsOrderBoxsSql, null, new { OrderIds = OrderIds })).ToList();
            if (!FbaChannelView)
            {
                //权限判断是否展示这些信息
                foreach (var item in dbBoxs)
                {
                    item.ChannelLength = item.ChannelWidth = item.ChannelHeight = item.ChannelWeight = 0;
                }
            }
            MSExcelHelper excelHelper = new MSExcelHelper();
            return excelHelper.OutDataAsByte<OutBoxInfoMapping, Order_MpsOrderBox>(dbBoxs);
        }

        public class OutBoxInfoMapping : XYY.Core.Standard.ExcelHelper.MSExcelHelper.MSExcelDefaultMapping<Order_MpsOrderBox>
        {
            public OutBoxInfoMapping()
            {
                Map(x => x.BoxNumber).Name("箱号");
                Map(x => x.BoxServiceNumber).Name("系统箱号");
                Map(x => x.BoxTrackingNumber).Name("派送单号");
                Map(x => x.Length).Name("下单长");
                Map(x => x.Width).Name("下单宽");
                Map(x => x.Height).Name("下单高");
                Map(x => x.Weight).Name("下单重量");
                Map(x => x.OpLength).Name("收件长");
                Map(x => x.OpWidth).Name("收件宽");
                Map(x => x.OpHeight).Name("收件高");
                Map(x => x.OpInputWeight).Name("收件重(Kg)");
                Map(x => x.ChannelLength).Name("下给末端长");
                Map(x => x.ChannelWidth).Name("下给末端宽");
                Map(x => x.ChannelHeight).Name("下给末端高");
                Map(x => x.ChannelWeight).Name("下给末端重量(Kg)");
                Map(x => x.BillLenght).Name("计费长");
                Map(x => x.BillWidth).Name("计费宽");
                Map(x => x.BillHeight).Name("计费高");
                Map(x => x.OpWeight).Name("计费重");

            }
        }

        public async Task<Order_MpsOrder> Get(int orderId, int draftOrderId)
        {
            Order_MpsOrder order = null;
            if (orderId > 0)
                order = (await _unitOfWork.QueryAsync<Order_MpsOrder>(x => x.OrderId == orderId)).FirstOrDefault();
            else
                order = (await _unitOfWork.QueryAsync<Order_MpsOrder>(x => x.DraftOrderId == orderId)).FirstOrDefault();
            if (order == null)
                throw new Exception("找不到相关的mps订单信息");
            var boxs = await _unitOfWork.QueryAsync<Order_MpsOrderBox>(x => x.MpsOrderId == order.Id);
            if (boxs.Count() == 0)
                throw new Exception("找不到相关的boxs信息");
            var goods = await _unitOfWork.QueryAsync<Order_MpsOrderGoods>(x => x.MpsOrderId == order.Id);
            if (goods.Count() == 0)
                throw new Exception("找不到相关的物品信息");
            order.Boxs = boxs.ToList();
            order.Boxs.ForEach(x =>
            x.Goods = goods.Where(y => y.MpsBoxId == x.Id).ToList()
            );
            return order;
        }
        public async Task<bool> ModifyCreateOrderChannelId(MpsModifyCreateOrderChannelId mpsModify, UserContent user)
        {
            string mpsboxsql = @$"select a.*,c.CustomerId,c.CreateOrderChannelId,c.SystemNo from Order_MpsOrderBox(nolock)a join Order_MpsOrder(nolock)b on a.MpsOrderId=b.Id
 join Order_Order(nolock) c on c.id=b.OrderId and c.OrderStatus>0
 where c.Id={mpsModify.OrderId}";
            var dbBoxInfo = await _unitOfWork.QueryBySqlAsync<Order_MpsOrderBox>(mpsboxsql);
            //权限判断
            if (dbBoxInfo == null || dbBoxInfo.Count() == 0) { throw new Exception("当前订单非FBA订单，或订单无箱子数据！不可使用此功能"); }
            else if (dbBoxInfo.Any(x => x.BoxOutStatus == BoxOutStatus.Receipt || x.OpInputWeight > 0) && !user.Roles.Contains("FBA修改创建渠道"))
            {
                throw new Exception("当前已有箱子收货，您无权限进行此操作");
            }
            else if (!(user.Roles.Contains("客服") || user.Roles.Contains("渠道运营") || user.Roles.Contains("财务") || user.Roles.Contains("FBA修改创建渠道")))
            {
                throw new Exception("您无权限进行此操作");
            }

            if (dbBoxInfo.All(x => x.BoxOutStatus != BoxOutStatus.Receipt && (x.OpInputWeight == 0)))
            {
                //更改渠道数据
                await _unitOfWork.ExecuteAsync($"  update Order_Order set CreateOrderChannelId={mpsModify.CreateOrderChannelId} where Id={mpsModify.OrderId} ");
                await _unitOfWork.ExecuteAsync(@$"insert into User_OperationLog(OperationTime,OperationName,CreateTime,CreateUserName,Remark,OperationUserName,Traget)
values(GETDATE(),'FBA修改创建渠道',GETDATE(),'{user.NiceName}','{dbBoxInfo.FirstOrDefault().CreateOrderChannelId}-->{mpsModify.CreateOrderChannelId}','{user.NiceName}','{dbBoxInfo.FirstOrDefault().SystemNo}')");
                return true;
            }
            //全收货数据需要修改其中一箱为未收货，避免循环执行一直计费
            else if (dbBoxInfo.All(x => x.BoxOutStatus == BoxOutStatus.Receipt))
            {
                int boxId = dbBoxInfo.LastOrDefault().Id;
                dbBoxInfo.LastOrDefault().BoxOutStatus = BoxOutStatus.Default;
                await _unitOfWork.ExecuteAsync($"update Order_Order set OrderStatus=1 where SystemNo='{dbBoxInfo.LastOrDefault().SystemNo}';update Order_MpsOrderBox set BoxOutStatus=0 where Id={boxId} ");
            }
            var tempData = dbBoxInfo.Where(x => x.OpInputWeight > 0).OrderByDescending(x => x.BoxOutStatus);
            //更改渠道数据
            await _unitOfWork.ExecuteAsync($"  update Order_Order set CreateOrderChannelId={mpsModify.CreateOrderChannelId} where Id={mpsModify.OrderId} ");
            foreach (var item in tempData)
            {
                try
                {
                    ConfirmCustomerOrderReceiptInput input = new ConfirmCustomerOrderReceiptInput()
                    {
                        BoxNumber = item.BoxNumber,
                        OpLength = (int)item.OpLength,
                        OpWidth = (int)item.OpWidth,
                        OpHeight = (int)item.OpHeight,
                        OpWeight = item.OpWeight,
                        Mode = InputMode.手动输入,
                        BillHeight = (int)item.BillHeight,
                        BillLenght = (int)item.BillLenght,
                        BillWidth = (int)item.BillWidth,
                        BillWeight = item.BillWeight,
                        OpInputWeight = item.OpInputWeight,
                    };
                    await ConfirmCustomerOrderReceipt(input);
                }
                catch (Exception ex)
                {
                    await _unitOfWork.ExecuteAsync($"  update Order_Order set CreateOrderChannelId={dbBoxInfo.FirstOrDefault().CreateOrderChannelId} where Id={mpsModify.OrderId} ");
                    throw new Exception(ex.Message);
                }
            }
            //重算杂费数据
            var IncidentalInfos = (await _unitOfWork.QueryAsync<FBA_Incidental>(x => x.OrderId == mpsModify.OrderId)).ToList();
            if (IncidentalInfos != null && IncidentalInfos.Count > 0)
            {
                var mpsOrderInfo = (await _unitOfWork.QueryAsync<Order_MpsOrder>(x => x.OrderId == mpsModify.OrderId)).FirstOrDefault();
                try
                {
                    if (IncidentalInfos.Any(x => x.Target == FBAIncidentalCalculateTarget.结算价))
                    {
                        var ins = IncidentalInfos.Where(x => x.Target == FBAIncidentalCalculateTarget.结算价).ToList();
                        FBAIncidentalDto dto = new FBAIncidentalDto()
                        {
                            OrderId = mpsModify.OrderId,
                            Target = FBAIncidentalCalculateTarget.结算价,
                            CostPrice = mpsOrderInfo.CostPrice,
                            ChargedWeight = mpsOrderInfo.ChargedWeight,
                            Incidentals = ins,
                        };

                        string msg = requst(dto);
                        if (msg != "成功") { throw new Exception("结算价重算执行失败！"); }
                    }

                    if (IncidentalInfos.Any(x => x.Target == FBAIncidentalCalculateTarget.客户))
                    {
                        var ins = IncidentalInfos.Where(x => x.Target == FBAIncidentalCalculateTarget.客户).ToList();
                        FBAIncidentalDto dto = new FBAIncidentalDto()
                        {
                            OrderId = mpsModify.OrderId,
                            Target = FBAIncidentalCalculateTarget.客户,
                            CostPrice = 0,
                            ChargedWeight = 0,
                            Incidentals = ins,
                        };
                        string msg = requst(dto);
                        if (msg != "成功") { throw new Exception("客户杂费重算执行失败！"); }
                    }
                }
                catch (Exception ex)
                {
                    await _unitOfWork.ExecuteAsync($"  update Order_Order set CreateOrderChannelId={dbBoxInfo.FirstOrDefault().CreateOrderChannelId} where Id={mpsModify.OrderId} ");
                    throw new Exception(ex.Message);
                }
            }
            //记录日志
            await _unitOfWork.ExecuteAsync(@$"insert into User_OperationLog(OperationTime,OperationName,CreateTime,CreateUserName,Remark,OperationUserName,Traget)
values(GETDATE(),'FBA修改创建渠道',GETDATE(),'{user.NiceName}','{dbBoxInfo.FirstOrDefault().CreateOrderChannelId}-->{mpsModify.CreateOrderChannelId}','{user.NiceName}','{dbBoxInfo.FirstOrDefault().SystemNo}')");
            return true;
        }
        public async Task<Order_MpsOrderBox> ConfirmCustomerOrderReceipt(ConfirmCustomerOrderReceiptInput input)
        {
            string boxSql = string.Format(@" select a.*,c.CustomerId,c.CreateOrderChannelId from Order_MpsOrderBox(nolock)a join Order_MpsOrder(nolock)b on a.MpsOrderId=b.Id
 join Order_Order(nolock) c on c.id=b.OrderId and c.OrderStatus>0 
	where BoxNumber='{0}' or BoxServiceNumber='{0}' ", input.BoxNumber);
            var dbBoxInfo = await _unitOfWork.QueryBySqlAsync<Order_MpsOrderBox>(boxSql);
            if (dbBoxInfo == null || dbBoxInfo.Count() == 0) { throw new Exception("找不到相关的boxs信息"); }
            else if (dbBoxInfo.Count() > 1) { throw new Exception("相关的boxs信息错误，存在多条记录"); }

            var toDBBoxInfo = dbBoxInfo.FirstOrDefault();


            toDBBoxInfo.BoxOutStatus = BoxOutStatus.Receipt;
            toDBBoxInfo.OpLength = input.OpLength;
            toDBBoxInfo.OpWidth = input.OpWidth;
            toDBBoxInfo.OpHeight = input.OpHeight;
            toDBBoxInfo.OpInputWeight = input.OpInputWeight;

            if (input.Mode == InputMode.取收件)
            {
                throw new Exception("模块停用");
            }
            else if (input.Mode == InputMode.手动输入)
            {
                toDBBoxInfo.BillHeight = input.BillHeight;
                toDBBoxInfo.BillLenght = input.BillLenght;
                toDBBoxInfo.BillWidth = input.BillWidth;
                toDBBoxInfo.BillWeight = input.BillWeight;
                if (toDBBoxInfo.BillHeight == 0 || toDBBoxInfo.BillLenght == 0 || toDBBoxInfo.BillWidth == 0 || toDBBoxInfo.BillWeight == 0)
                {
                    throw new Exception("必须录入用户确认信息");
                }
                else if (toDBBoxInfo.OpInputWeight == 0 || toDBBoxInfo.OpWidth == 0 || toDBBoxInfo.OpHeight == 0 || toDBBoxInfo.OpLength == 0)
                {
                    throw new Exception("必须录入实收信息");
                }
                //5000计泡系数的渠道
                List<int> channel5K = new List<int> { 489, 518, 517, 516, 515, 514, 513, 533,567 };
                //我们以最终数据判断计费标准   渠道计泡系数不同
                decimal billVWeight = 0;
                decimal opVWeight = 0;
                if (channel5K.Contains(toDBBoxInfo.CreateOrderChannelId))
                {
                    billVWeight = (toDBBoxInfo.BillLenght * toDBBoxInfo.BillWidth * toDBBoxInfo.BillHeight) / 5000.0m;
                    opVWeight = (input.OpLength * input.OpWidth * input.OpHeight) / 5000.0m;
                }
                else
                {
                    billVWeight = (toDBBoxInfo.BillLenght * toDBBoxInfo.BillWidth * toDBBoxInfo.BillHeight) / 6000.0m;
                    opVWeight = (input.OpLength * input.OpWidth * input.OpHeight) / 6000.0m;
                }

                if (billVWeight > toDBBoxInfo.BillWeight)
                {
                    toDBBoxInfo.OpWeight = billVWeight;
                }
                else
                {
                    toDBBoxInfo.OpWeight = toDBBoxInfo.BillWeight;
                }
                if (toDBBoxInfo.CustomerId == 663)
                {
                    //英驰的，我们向上取整，其他客户不用处理
                    toDBBoxInfo.OpWeight = toDBBoxInfo.OpWeight;
                }
                //计费重
                //toDBBoxInfo.OpWeight = tempOpWeight > input.OpWeight ? tempOpWeight : input.OpWeight;
                //称重
                //录入体积重
                toDBBoxInfo.VolumeWeight = opVWeight;
            }
            //箱数据进位
            //先取大数据 ，然后再进位数据
            if (new List<int> { 513, 516 }.Contains(toDBBoxInfo.CreateOrderChannelId))
            {
                //特殊取整逻辑 不满0.5 按0.5算，满0.5 按1算 如：
                toDBBoxInfo.OpWeight = RoundToNearestHalf(toDBBoxInfo.OpWeight);
            }
            await _unitOfWork.UpdateAsync(toDBBoxInfo);
            //整票取整的逻辑
            string updateWeight = "update a set OPWeight=@tempWeight*1000 from  Order_Order(nolock) a where a.Id=@orderId";
            if (new List<int> { 513, 516, 518, 517, 516, 515, 514, 513,567 }.Contains(toDBBoxInfo.CreateOrderChannelId))
            {
                if (new List<int> { 517, 514 }.Contains(toDBBoxInfo.CreateOrderChannelId)) { updateWeight = " if @tempWeight>30.5  "; } else { updateWeight = " if @tempWeight>21.5  "; }
                //写死小包重量的判断 21.5 小包范围内的数据不做进位处理，大货整票进1kg
                updateWeight += @" 
	begin
		update a set OPWeight=CEILING(@tempWeight)*1000 from  Order_Order(nolock) a where a.Id=@orderId
	end
else
	begin
		update a set OPWeight=@tempWeight*1000 from  Order_Order(nolock) a where a.Id=@orderId
	end ";
            }

            await _unitOfWork.ExecuteAsync(@$"
declare @tempWeight decimal(18,3);
declare @orderId int;
declare @IsReceiverEnd int;
select @tempWeight = SUM(ISNULL(OpWeight,0)),@orderId=b.OrderId from Order_MpsOrderBox(nolock)a join Order_MpsOrder(nolock)b on a.MpsOrderId=b.Id 
where MpsOrderId={toDBBoxInfo.MpsOrderId} 
group by OrderId

select @IsReceiverEnd= case when 
	count(0) - sum(case when BoxOutStatus >0 or BoxStatus>0 then 1 else 0 end) =0
	then 1
	else 0 
	end  
	from Order_MpsOrderBox where MpsOrderId= {toDBBoxInfo.MpsOrderId}  
if(@IsReceiverEnd=1)
begin
	update Order_Order set OrderStatus=2,ReceiveTime=getdate() where id = @orderId
end 
{updateWeight} ");
            var order = (await _unitOfWork.QueryAsync<Order_Order>(x => x.CustomerOrderNo == toDBBoxInfo.CustomerOrderNumber && x.OrderStatus > 0)).FirstOrDefault();
            var mpsOrder = (await _unitOfWork.QueryAsync<Order_MpsOrder>(x => x.OrderId == order.Id)).FirstOrDefault();
            if (order != null)
            {
                var customer = (await _unitOfWork.QueryBySqlAsync<User_CustomerExtend>($"select * from User_Customer where Id={order.CustomerId}")).FirstOrDefault();
                if (order.OrderStatus == 2)
                {
                    var client = new System.Net.WebClient();

                    var branchCopanyId = await GetBranchCopanyId(order.CustomerId);
                    if (mpsOrder == null || (mpsOrder.ChargedWeight ?? 0) == 0) { branchCopanyId = 0; }
                    //最终计算重量
                    double mpsWeight = (order.OPWeight / 1000.0);
                    ///优先计费运费
                    string message = Charges(new ChargesRequest(order.ChannelId,
                         order.CreateOrderChannelId,
                         order.ReceiverCountryCode, 0,
                         order.CustomerId,
                         order.Id, order.ReceiveTime.Value,
                         mpsWeight, order.SystemNo, false, order.TrackingNumber, branchCopanyId, true, order.ReceiverZipCode, "FBA费用", order.ReceiverStreet));
                    if (string.IsNullOrEmpty(message) || !message.Contains("成功")) { await _unitOfWork.ExecuteAsync($"update Order_MpsOrderBox set BoxOutStatus=0 where BoxNumber='{input.BoxNumber}' "); throw new Exception(message ?? "计费异常，请重试！"); }//至少一箱为未收货，下单才会有提示
                    //物流轨迹 创建订单
                    await _unitOfWork.ExecuteStoredProcedureAsync("sp_FBATrackingLogAdd", new { @Status = 2, @OrderId = order.Id, @TriggerTime = DateTime.Now });
                    //await _incidentalRepository.SaveOtherFee(
                    //    new Model.Standard.Finance.FBAIncidentalDto
                    //    {
                    //        OrderId = order.Id,
                    //        Target = Model.Standard.Finance.FBAIncidentalCalculateTarget.客户
                    //    });


                    //await _incidentalRepository.SaveOtherFee(
                    //    new Model.Standard.Finance.FBAIncidentalDto
                    //    {
                    //        OrderId = order.Id,
                    //        Target = Model.Standard.Finance.FBAIncidentalCalculateTarget.结算价
                    //    });
                }
            }
            return toDBBoxInfo;
        }
        private static decimal RoundToNearestHalf(decimal d)
        {
            var integerPart = Math.Floor(d);
            var decimalPart = d - integerPart;
            if (decimalPart == 0)
            {
                return integerPart;
            }
            else if (decimalPart < 0.5m)
            {
                return integerPart + 0.5m;
            }
            else
            {
                return integerPart + 1;
            }
        }
        #region 人工重跑数据接口 临时使用

        /// <summary>
        /// 重算663计费数据
        /// </summary>
        /// <returns></returns>
        public async Task RecalculateBilling()
        {
            //452(已跑)  663(已跑)   472(已跑)   164  
            int customerId = 164;
            string errorId = string.Empty;
            //164 需要增加sql条件  and CustomerOrderNo like 'FBA%'
            string sql = $"select * from Order_Order(nolock) where id in (220112389) ";
            var orders = (await _unitOfWork.QueryBySqlAsync<Order_Order>(sql)).OrderBy(x => x.Id).ToList();
            var customers = (await _unitOfWork.QueryBySqlAsync<dynamic>("select Id,BranchCompanyId from User_Customer(nolock) ")).ToList();

            //var branchCopanyId = await GetBranchCopanyId(customerId);
            foreach (var order in orders)
            {
                var branchCopanyId = customers.Where(x => x.Id == customerId)?.FirstOrDefault()?.BranchCompanyId;
                double mpsWeight = order.CustomerId == 663 ? (order.CustomerWeight / 1000.0) : (order.OPWeight / 1000.0);
                if (!order.ReceiveTime.HasValue || order.TrackingNumber.StartsWith("XYY")) { continue; }
                //循环调计费接口
                string msg = Charges(new ChargesRequest(order.ChannelId,
                       order.CreateOrderChannelId,
                       order.ReceiverCountryCode, 0,
                       order.CustomerId,
                       order.Id, order.ReceiveTime.Value,
                       mpsWeight, order.SystemNo, false, order.TrackingNumber, branchCopanyId, true, order.ReceiverZipCode, "FBA费用", order.ReceiverStreet));
                if (msg != "成功")
                {
                    //断点查看异常
                    errorId += $"{order.Id},";
                }
            }
            Console.WriteLine(errorId);
            throw new Exception(errorId);
        }
        /// <summary>
        /// 重算杂费
        /// </summary>
        /// <returns></returns>
        public async Task RecalculateCustomerFee()
        {
            string sql = "select *  from Order_Order(nolock) a where ReceiveTime>='2022-09-01' and ReceiveTime<'2022-11-01'and OrderStatus>0 and MpsOrderId>0 ";
            string errorOrder = "select *  from Order_Order(nolock) a where Id in (218648266)";
            var orders = (await _unitOfWork.QueryBySqlAsync<Order_Order>(errorOrder)).ToList();
            List<int> orderids = orders.Select(x => x.Id).ToList();
            string incidentalSql = @" select * from FBA_Incidental where OrderId in @orderids";
            var IncidentalInfos = (await _unitOfWork.QueryBySqlAsync<FBA_Incidental>(incidentalSql, null, new { orderids = orderids })).ToList();//SaveOtherFee 方法会更新Incidental 信息的类型
            List<string> errorMsg = new List<string>();
            foreach (var item in orders)
            {
                int count = orders.FindIndex(x => x == item);
                var tempInfo = IncidentalInfos.Where(x => x.OrderId == item.Id).ToList();
                //以订单表为主 取mpsOrder 数据
                string mpsSql = $"select * from Order_MpsOrder(nolock) where Id ={item.MpsOrderId}";
                var mpsOrderInfo = (await _unitOfWork.QueryBySqlAsync<Order_MpsOrder>(mpsSql)).FirstOrDefault();
                try
                {
                    if (tempInfo.Any(x => x.Target == FBAIncidentalCalculateTarget.结算价))
                    {
                        var ins = tempInfo.Where(x => x.Target == FBAIncidentalCalculateTarget.结算价).ToList();
                        FBAIncidentalDto dto = new FBAIncidentalDto()
                        {
                            OrderId = item.Id,
                            Target = FBAIncidentalCalculateTarget.结算价,
                            CostPrice = mpsOrderInfo.CostPrice,
                            ChargedWeight = mpsOrderInfo.ChargedWeight,
                            Incidentals = ins,
                        };
                        Console.WriteLine($"订单Id:{item},成本杂费：" + JsonConvert.SerializeObject(dto));
                        string msg = requst(dto);
                        if (msg != "成功") { errorMsg.Add(msg); }
                    }

                    if (tempInfo.Any(x => x.Target == FBAIncidentalCalculateTarget.客户))
                    {
                        var ins = tempInfo.Where(x => x.Target == FBAIncidentalCalculateTarget.客户).ToList();
                        FBAIncidentalDto dto = new FBAIncidentalDto()
                        {
                            OrderId = item.Id,
                            Target = FBAIncidentalCalculateTarget.客户,
                            CostPrice = 0,
                            ChargedWeight = 0,
                            Incidentals = ins,
                        };
                        Console.WriteLine($"订单Id:{item},客户杂费：" + JsonConvert.SerializeObject(dto));
                        string msg = requst(dto);
                        if (msg != "成功") { errorMsg.Add(msg); }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"订单Id：{item},执行异常，请重试");
                }

            }

        }
        //组装数据
        private string requst(FBAIncidentalDto item)
        {
            string errorId = "成功";
            //改请求的写法
            string host = "http://localhost:9521/";
            RestSharp.IRestRequest request = new RestSharp.RestRequest(method: RestSharp.Method.POST);
            request.AddHeader("Authorization", "token 132A7468DE079C6CEB59F383A661E612");
            request.AddJsonBody(JsonConvert.SerializeObject(item));
            RestSharp.IRestClient client = new RestSharp.RestClient($"{host}api/FBAIncidental/SaveFee");
            var reponse = client.Execute(request);
            string msg = string.Empty;
            if (reponse.StatusCode == System.Net.HttpStatusCode.OK)
            {
                msg = Newtonsoft.Json.JsonConvert.DeserializeObject<ApiJsonModel>(reponse.Content).message;
            }
            else
            {
                try
                {
                    msg = Newtonsoft.Json.JsonConvert.DeserializeObject<ApiJsonModel>(reponse.ErrorMessage ?? reponse.Content).message;
                }
                catch
                {
                    errorId += $"{item.OrderId} 失败类型:{item.Target},";
                    msg = reponse.ErrorMessage ?? reponse.Content;
                }
            }
            if (msg != "成功")
            {
                errorId += $"{item.OrderId} 失败类型:{item.Target},原因：{msg}";
            }
            return errorId;
        }
        #endregion
        public async Task<int?> GetBranchCopanyId(int customerId)
        {
            var customers = (await _unitOfWork.QueryBySqlAsync<dynamic>("select Id,BranchCompanyId from User_Customer(nolock) ")).ToList();

            var customer = customers.Where(x => x.Id == customerId).FirstOrDefault();
            return customer?.BranchCompanyId;
        }


        /// <summary>
        /// 计费
        /// </summary>
        /// <param name="ExpressId">渠道Id</param>
        /// <param name="PublicExpressId">公开渠道Id</param>
        /// <param name="CountryCode">国家</param>
        /// <param name="GoodsDeclared">申报价值</param>
        /// <param name="CustomerId">客户Id</param>
        /// <param name="OrderId">订单Id</param>
        /// <param name="TransactionTime">交易时间</param>
        /// <param name="BillingWeight">帐单重量</param>
        /// <param name="systemNO">系统单号</param>
        /// <param name="Bookkeeping">是否即时扣费</param>
        /// <returns></returns>
        public static string Charges(
            ChargesRequest chargesRequest
           )
        {
            string host = "http://120.24.149.148:9503/";

            if (!string.IsNullOrEmpty(chargesRequest.CountryCode))
            {
                chargesRequest.CountryCode = chargesRequest.CountryCode.ToUpper();
                if (chargesRequest.CountryCode == "UK")
                {
                    chargesRequest.CountryCode = "GB";
                }
            }
            RestSharp.IRestRequest request = new RestSharp.RestRequest(method: RestSharp.Method.POST);
            request.AddHeader("Authorization", "token 132A7468DE079C6CEB59F383A661E612");
            request.AddJsonBody(new
            {
                chargesRequest.ExpressId,
                chargesRequest.PublicExpressId,
                chargesRequest.CountryCode,
                chargesRequest.GoodsDeclared,
                chargesRequest.CustomerId,
                chargesRequest.OrderId,
                chargesRequest.TransactionTime,
                AccountingDate = chargesRequest.TransactionTime,
                chargesRequest.BillingWeight,
                BillType = "全程派送运费",
                BillNo = chargesRequest.systemNO,
                IncomeOrESxpense = "支出",
                Bookkeeping = chargesRequest.Bookkeeping,
                chargesRequest.TrackingNumber,
                chargesRequest.BranchCompanyCustomerId,
                chargesRequest.HasIOSS,
                chargesRequest.ZipCode,
                chargesRequest.FeeDescription,
                chargesRequest.Stree,
            });
            RestSharp.IRestClient client = new RestSharp.RestClient($"{host}api/ExpressFee/Recalculation");
            var reponse = client.Execute(request);
            if (reponse.StatusCode == System.Net.HttpStatusCode.OK)
            {
                return Newtonsoft.Json.JsonConvert.DeserializeObject<ChargesItem>(reponse.Content).data;
            }
            else
            {
                try
                {
                    return Newtonsoft.Json.JsonConvert.DeserializeObject<ChargesItem>(reponse.ErrorMessage ?? reponse.Content).data;
                }
                catch
                {
                    return reponse.ErrorMessage ?? reponse.Content;
                }
            }
        }
        public async Task BoxStatusOp(int id, BoxStatus boxStatus)
        {
            string upSql = @$"update Order_MpsOrderBox set BoxStatus={(int)boxStatus} where id={id} ";
            await _unitOfWork.ExecuteAsync(upSql);
            //多件订单tag处理
            var order = await _unitOfWork.QueryBySqlAsync<Order_MpsOrder>($" select *from Order_MpsOrder where id=(select MpsOrderId from Order_MpsOrderBox where Id={id})");
            if (order != null && order.Count() > 0)
            {
                var tempOrder = order.FirstOrDefault();
                var boxs = await _unitOfWork.QueryBySqlAsync<Order_MpsOrderBox>($" select * from Order_MpsOrderBox where MpsOrderId={tempOrder.Id} ");
                //数据判断
                string Tag = string.Empty;
                var statusInfo = boxs.Select(x => x.BoxStatus).Distinct();
                if (statusInfo.Count() > 1)
                {
                    foreach (var item in statusInfo)
                    {
                        if (item >= BoxStatus.BeforeReceiptToSend)
                        {
                            Tag += "部分" + item.GetDescription() + ";";
                        }
                    }
                }
                else
                {
                    Tag = "全部" + statusInfo.FirstOrDefault().GetDescription();
                }
                Tag = Tag.TrimEnd(';');
                string upOrderTags = $"update Order_MpsOrder set Tags='{Tag}' where Id={tempOrder.Id}";
                await _unitOfWork.ExecuteAsync(upOrderTags);
            }
        }
        public async Task<IEnumerable<Order_MpsOrder>> GetMpsOrders(IEnumerable<int> Ids)
        {
            if (Ids == null || Ids.Count() == 0)
            {
                return null;
            }
            else
            {
                IEnumerable<Order_MpsOrder> MpsOrders = await _unitOfWork.QueryBySqlAsync<Order_MpsOrder>($"select * from Order_MpsOrder where Id in ({string.Join(",", Ids)})");
                return MpsOrders;
            }
        }
        public async Task<IEnumerable<MpsBoxCount>> GetMpsBoxCount(IEnumerable<int> orderIds)
        {
            if (orderIds == null || orderIds.Count() == 0)
            {
                return null;
            }
            else
            {
                string sql = @"select d.Id OrderId,case when ISNULL(d.MpsOrderId,0)>0 then  COUNT(b.Id) else SUM(c.Quantity) end Quantity from Order_Order(nolock) d
left join Order_MpsOrder(nolock) a on a.OrderId=d.Id
left join Order_MpsOrderBox(nolock) b on b.MpsOrderId=a.Id
left join Order_OrderGoods(nolock) c on c.OrderId=d.Id
where d.Id in @orderIds
group by d.Id,d.MpsOrderId ";
                IEnumerable<MpsBoxCount> MpsBoxCounts = await _unitOfWork.QueryBySqlAsync<MpsBoxCount>(sql, null, new { orderIds = orderIds });
                return MpsBoxCounts;
            }
        }
        public async Task<ApiJsonModel> CustomerOrderOut(string InputNumber, int opStatus)
        {
            string boxSql = string.Format(@"select * from Order_MpsOrderBox(nolock)a join Order_MpsOrder(nolock)b on a.MpsOrderId=b.Id
 join Order_Order(nolock) c on c.id=b.OrderId and c.OrderStatus>0 
	where BoxNumber='{0}' or BoxServiceNumber='{0}'", InputNumber);
            var dbBoxInfo = await _unitOfWork.QueryBySqlAsync<Order_MpsOrderBox>(boxSql);
            if (dbBoxInfo == null || dbBoxInfo.Count() == 0) { return ApiJsonModel.Error("找不到相关的boxs信息"); }
            else if (dbBoxInfo.Count() > 1) { return ApiJsonModel.Error("相关的boxs信息错误，存在多条记录"); }
            else if (dbBoxInfo.FirstOrDefault().BoxOutStatus > BoxOutStatus.Receipt) { return ApiJsonModel.Error($"当前箱子已{dbBoxInfo.FirstOrDefault().BoxOutStatus.GetDescription()}"); }
            var toDBBoxInfo = dbBoxInfo.FirstOrDefault();
            toDBBoxInfo.BoxOutStatus = (BoxOutStatus)opStatus;
            await _unitOfWork.UpdateAsync(toDBBoxInfo);
            //标签处理
            var boxs = await _unitOfWork.QueryBySqlAsync<Order_MpsOrderBox>($" select * from Order_MpsOrderBox(nolock) where MpsOrderId={toDBBoxInfo.MpsOrderId}");
            if (boxs.All(x => x.BoxOutStatus == BoxOutStatus.Transfer))
            {
                //如果箱子状态大于称重，我们判断该订单已结束(即已转运)
                var order = await _unitOfWork.QueryBySqlAsync<Order_MpsOrder>($" select *from Order_MpsOrder(nolock) where id=(select MpsOrderId from Order_MpsOrderBox where Id={toDBBoxInfo.MpsOrderId})");
                string tag = order.FirstOrDefault().Tags;
                if (!string.IsNullOrEmpty(tag)) { order.FirstOrDefault().Tags += ";已转运"; } else { order.FirstOrDefault().Tags = "已转运"; }
                await _unitOfWork.UpdateAsync(order.FirstOrDefault());
                var o = (await _unitOfWork.QueryAsync<Order_Order>(x => x.Id == order.First().OrderId)).First();
                o.OrderStatus = 3;
                await _unitOfWork.UpdateByPrimaryKeyAsync<Order_Order>("OrderStatus", 3, o.Id);
            }

            return ApiJsonModel.OK("出库成功");
        }
        public async Task UpdateDraftOrderErrorMessage(int DraftOrderId, string errorMessage)
        {
            await _unitOfWork.UpdateByConditionAsync<Order_DraftOrder, bool>(x => x.Id == DraftOrderId, new { ErrorMessage = errorMessage });
        }

        public async Task UpdateBoxNumbser(Dictionary<string, string> boxInfo)
        {
            string sql = @"";
            foreach (var item in boxInfo)
            {
                sql += $@"update Order_MpsOrderBox set BoxTrackingNumber = '{item.Value}' where BoxServiceNumber='{item.Key}' ";
            }
            await _unitOfWork.ExecuteAsync(sql);
        }

        public async Task UpdateMpsOrder(UpdateMpsOrderInfo updateMpsOrderInfo)
        {
            try
            {
                var dbOrder = (await _unitOfWork.QueryAsync<Order_Order>(x => x.Id == updateMpsOrderInfo.mpsOrder.Id)).FirstOrDefault();
                var dbMpsOrder = (await _unitOfWork.QueryAsync<Order_MpsOrder>(x => x.Id == updateMpsOrderInfo.mpsOrder.MpsOrderId)).FirstOrDefault();
                dbMpsOrder.ReceiverName = dbOrder.ReceiverName = updateMpsOrderInfo.mpsOrder.ReceiverName;
                dbMpsOrder.ReceiverCountry = dbOrder.ReceiverCountryCode = updateMpsOrderInfo.mpsOrder.ReceiverCountryCode;
                dbMpsOrder.ReceiverState = dbOrder.ReceiverState = updateMpsOrderInfo.mpsOrder.ReceiverState;
                dbMpsOrder.ReceiverCity = dbOrder.ReceiverCity = updateMpsOrderInfo.mpsOrder.ReceiverCity;
                dbMpsOrder.ReceiverStreet1 = dbOrder.ReceiverStreet = updateMpsOrderInfo.mpsOrder.ReceiverStreet;
                dbMpsOrder.ReceiverPhone = dbOrder.ReceiverPhone = updateMpsOrderInfo.mpsOrder.ReceiverPhone;
                dbMpsOrder.ReceiverZipCode = dbOrder.ReceiverZipCode = updateMpsOrderInfo.mpsOrder.ReceiverZipCode;
                dbMpsOrder.ReceiverCompanyName = dbOrder.ReceiverCompany = updateMpsOrderInfo.mpsOrder.ReceiverCompany;
                dbOrder.House = updateMpsOrderInfo.mpsOrder.House;
                dbMpsOrder.ReceiverEmail = dbOrder.ReceiverEmail = updateMpsOrderInfo.mpsOrder.ReceiverEmail;
                dbMpsOrder.DeclarationMethod = updateMpsOrderInfo.mpsOrder.DeclarationMethod;
                await _unitOfWork.UpdateAsync(dbOrder);
                dbMpsOrder.Magnetic = updateMpsOrderInfo.goods.Any(x => x.Magnetic == true);
                dbMpsOrder.Electric = updateMpsOrderInfo.goods.Any(x => x.Electric == true);
                dbMpsOrder.Liquid = updateMpsOrderInfo.goods.Any(x => x.Liquid == true);
                await _unitOfWork.UpdateAsync(dbMpsOrder);
                //比逐条执行稍快
                string sql = " update Order_MpsOrderGoods set Magnetic=@Magnetic,Electric=@Electric,Liquid=@Liquid where Id=@Id";
                await _unitOfWork.ExecuteAsync(sql, updateMpsOrderInfo.goods);
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }
        #region 导出一票多件订单信息，合并为zip包
        public async Task<byte[]> OutMpsOrderData(int[] Ids)
        {
            var data = await GetExcelDtos(Ids);
            if (data == null || data.Count == 0) { throw new Exception("未获取到任何一票多件订单信息！"); }
            var numbers = data.Select(x => x.CustomerOrderNumber).Distinct().ToList();
            List<string> files = new List<string>();
            foreach (var number in numbers)
            {
                var dtos = data.Where(x => x.CustomerOrderNumber == number).ToList();
                string file = getTempFilePath(Name: number + "_");
                await OutExcel(file, dtos);
                files.Add(file);
            }
            //进行文件合并
            if (files.Count > 0)
            {
                string zipFile = getTempFilePath(".zip");
                SaveZipFile(files, zipFile);
                //清理文件
                byte[] bytes = System.IO.File.ReadAllBytes(zipFile);
                System.IO.File.Delete(zipFile);
                return bytes;
            }
            return null;
        }
        /// <summary>
        /// 获取待导出数据
        /// </summary>
        /// <returns></returns>
        private async Task<List<Order_MpsOrderExcelDto>> GetExcelDtos(int[] Ids)
        {
            string sql = @"select a.CustomerOrderNumber,a.ReceiverCity,a.ReceiverCompanyName,a.ReceiverCountry ReceiverCountryCode,a.ReceiverEmail,a.ReceiverName,a.ReceiverPhone,a.ReceiverState
,a.ReceiverStreet1,a.ReceiverStreet2,a.ReceiverZipCode,e.PublicName ChannelName,a.FbaOrderNumber,a.WarehouseCode,a.CreateTime,a.CustomerId
,a.DeclarationMethod,b.Weight BoxWeight,b.Length,b.Width,b.Height,b.BoxNumber
,c.CnName,c.EnName,c.Currency,c.GoodsValue,c.HsCode,c.SaleLink,c.Meterial,c.Quantity,c.ProductNo,c.Brand,c.ModelNumber,c.Usage,c.Weight GoodsWeight,c.Weight GoodsWeight
,case  c.Magnetic when 0 then '否' else '是' end Magnetic
,case  c.Electric when 0 then '否' else '是' end Electric
,case  c.Liquid when 0 then '否' else '是' end Liquid
,c.SalePrice,c.ProductImageUrl Imageurl
from Order_MpsOrder(nolock) a
left join Order_MpsOrderBox(nolock) b on b.MpsOrderId=a.Id
left join Order_MpsOrderGoods(nolock) c on c.MpsBoxId=b.Id
left join Order_Order(nolock) d on d.Id=a.OrderId
left join Logistics_Channel(nolock) e on e.Id=d.CreateOrderChannelId
where a.OrderId in @Ids ";
            var result = await _unitOfWork.QueryBySqlAsync<Order_MpsOrderExcelDto>(sql, null, new { Ids = Ids });
            return result.ToList();

        }
        /// <summary>
        /// 创建excel文件路径
        /// </summary>
        /// <returns></returns>
        private string getTempFilePath(string Name = "", string suffix = ".xlsx")
        {
            if (Name.Contains("/"))
                Name = Name.Replace("/", "_");
            string basePath = System.IO.Path.Combine(System.AppContext.BaseDirectory, "temp", "OutMpsOrderData");
            if (!System.IO.Directory.Exists(basePath))
                System.IO.Directory.CreateDirectory(basePath);
            return System.IO.Path.Combine(basePath, Name + Guid.NewGuid().ToString() + suffix);
            // return System.IO.Path.Combine(basePath,(!string.IsNullOrEmpty(Name)?Name:Guid.NewGuid().ToString() )+ suffix);
        }
        /// <summary>
        /// 写excel
        /// </summary>
        /// <returns></returns>
        private async Task OutExcel(string fileName, List<Order_MpsOrderExcelDto> dtos)
        {
            XSSFWorkbook excelBook = new XSSFWorkbook();
            #region 样式设置
            //表头字体设置
            NPOI.SS.UserModel.IFont headFont = excelBook.CreateFont();
            headFont.FontHeightInPoints = 14;
            headFont.IsBold = true;

            NPOI.SS.UserModel.IFont sheetTable1Font = excelBook.CreateFont();
            sheetTable1Font.FontHeightInPoints = 11;
            sheetTable1Font.IsBold = true;

            //sheet 1表头
            ICellStyle headStyle = excelBook.CreateCellStyle();
            headStyle.WrapText = true;//设置换行这个要先设置
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderTop = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.SetFont(headFont);
            //sheet 2表头 table1
            ICellStyle sheet2headStyle1 = excelBook.CreateCellStyle();
            sheet2headStyle1.WrapText = true;//设置换行这个要先设置
            sheet2headStyle1.Alignment = HorizontalAlignment.Center;
            sheet2headStyle1.BorderBottom = BorderStyle.Thin;
            sheet2headStyle1.BorderTop = BorderStyle.Thin;
            sheet2headStyle1.BorderLeft = BorderStyle.Thin;
            sheet2headStyle1.BorderRight = BorderStyle.Thin;
            sheet2headStyle1.SetFont(sheetTable1Font);
            //sheet 2表头 table2 
            ICellStyle sheet2headStyle = excelBook.CreateCellStyle();
            sheet2headStyle.WrapText = true;//设置换行这个要先设置
            sheet2headStyle.Alignment = HorizontalAlignment.Center;
            sheet2headStyle.BorderBottom = BorderStyle.Thin;
            sheet2headStyle.BorderTop = BorderStyle.Thin;
            sheet2headStyle.BorderLeft = BorderStyle.Thin;
            sheet2headStyle.BorderRight = BorderStyle.Thin;
            sheet2headStyle.SetFont(headFont);
            //设置颜色
            sheet2headStyle.FillForegroundColor = 0;
            sheet2headStyle.FillPattern = FillPattern.SolidForeground;
            ((XSSFColor)sheet2headStyle.FillForegroundColorColor).SetRgb(new byte[] { 0, 238, 0 });//浅绿色
            //单元格通用样式
            ICellStyle styleCommonCenter = excelBook.CreateCellStyle();
            styleCommonCenter.Alignment = HorizontalAlignment.Center;
            styleCommonCenter.WrapText = true;
            styleCommonCenter.VerticalAlignment = VerticalAlignment.Center;
            styleCommonCenter.BorderBottom = BorderStyle.Thin;
            styleCommonCenter.BorderTop = BorderStyle.Thin;
            styleCommonCenter.BorderLeft = BorderStyle.Thin;
            styleCommonCenter.BorderRight = BorderStyle.Thin;
            //cell样式 赋值到 RowStyle 解决合并无边框问题
            ICellStyle RowStyle = excelBook.CreateCellStyle();
            RowStyle.Alignment = HorizontalAlignment.Center;
            RowStyle.WrapText = true;
            RowStyle.VerticalAlignment = VerticalAlignment.Center;
            RowStyle.BorderBottom = BorderStyle.Thin;
            RowStyle.BorderTop = BorderStyle.Thin;
            RowStyle.BorderLeft = BorderStyle.Thin;
            RowStyle.BorderRight = BorderStyle.Thin;
            #endregion
            #region sheet 1数据写入
            ISheet sheet = excelBook.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);
            row.Height = 200 * 3;
            List<string> detailHeaderName = new List<string>() { "FBA单号", "订单号/客户单号", "线路产品", "箱号", "服务商单号2", "收件人国家", "城市", "州/省", "邮编", "地址一", "地址二", "收件人名称", "联系电话", "品名 中文描述", "品名 英文描述", "单个产品重量（KG）", "产品件数", "单箱实重(KG)", "箱数", "体积", "长", "宽", "高", "单个产品总申报", "申报总价值", "申报币种（USD/EUR）", "带磁", "带电", "危险品", "材质", "用途", "品牌", "型号", "销售链接", "销售单价", "海关编码", "图片", "报关方式", "SKU（可选）", "ASIN" };
            for (int i = 0; i < detailHeaderName.Count; i++)
            {
                sheet.SetColumnWidth(i, 20 * 256);
                ICell cell = row.CreateCell(i);
                cell.CellStyle = headStyle;
                cell.SetCellValue(detailHeaderName[i]);
            }
            for (int i = 0; i < dtos.Count; i++)
            {
                IRow dataRow = sheet.CreateRow(i + 1);
                for (int j = 0; j < detailHeaderName.Count; j++)
                {
                    ICell cell = dataRow.CreateCell(j);
                    cell.CellStyle = styleCommonCenter;
                    switch (j)
                    {
                        case 0:
                        case 1:
                            cell.SetCellValue(dtos[i].CustomerOrderNumber);
                            break;
                        case 2:
                            cell.SetCellValue(dtos[i].ChannelName);
                            break;
                        case 3:
                            cell.SetCellValue(dtos[i].BoxNumber);
                            break;
                        case 4://服务商单号2
                        case 38://SKU(可选)
                            cell.SetCellValue("");
                            break;
                        case 5:
                            cell.SetCellValue(dtos[i].ReceiverCountryCode);
                            break;
                        case 6:
                            cell.SetCellValue(dtos[i].ReceiverCity);
                            break;
                        case 7:
                            cell.SetCellValue(dtos[i].ReceiverState);
                            break;
                        case 8:
                            cell.SetCellValue(dtos[i].ReceiverZipCode);
                            break;
                        case 9:
                            cell.SetCellValue(dtos[i].ReceiverStreet1);
                            break;
                        case 10:
                            cell.SetCellValue(dtos[i].ReceiverStreet2);
                            break;
                        case 11:
                            cell.SetCellValue(dtos[i].ReceiverName);
                            break;
                        case 12:
                            cell.SetCellValue(dtos[i].ReceiverPhone);
                            break;
                        case 13:
                            cell.SetCellValue(dtos[i].CnName);
                            break;
                        case 14:
                            cell.SetCellValue(dtos[i].EnName);
                            break;
                        case 15:
                            cell.SetCellValue(dtos[i].GoodsWeight.ToString());
                            break;
                        case 16:
                            cell.SetCellValue(dtos[i].Quantity);
                            break;
                        case 17:
                            cell.SetCellValue((double)dtos[i].BoxWeight);
                            break;
                        case 18:
                            cell.SetCellValue("1");
                            break;
                        case 19:
                            decimal value19 = Math.Round((dtos[i].Length * dtos[i].Width * dtos[i].Height) / 100000.0M, 3);
                            cell.SetCellValue(value19.ToString());
                            break;
                        case 20:
                            cell.SetCellValue((double)dtos[i].Length);
                            break;
                        case 21:
                            cell.SetCellValue((double)dtos[i].Width);
                            break;
                        case 22:
                            cell.SetCellValue((double)dtos[i].Height);
                            break;
                        case 23:
                            cell.SetCellValue(dtos[i].GoodsValue.ToString());
                            break;
                        case 24:
                            decimal value24 = dtos[i].GoodsValue * dtos[i].Quantity;
                            cell.SetCellValue(value24.ToString());
                            break;
                        case 25:
                            cell.SetCellValue(dtos[i].Currency);
                            break;
                        case 26:
                            cell.SetCellValue(dtos[i].Magnetic);
                            break;
                        case 27:
                            cell.SetCellValue(dtos[i].Electric);
                            break;
                        case 28:
                            cell.SetCellValue(dtos[i].Liquid);
                            break;
                        case 29:
                            cell.SetCellValue(dtos[i].Meterial);
                            break;
                        case 30:
                            cell.SetCellValue(dtos[i].Usage);
                            break;
                        case 31:
                            cell.SetCellValue(dtos[i].Brand);
                            break;
                        case 32:
                            cell.SetCellValue(dtos[i].ModelNumber);
                            break;
                        case 33:
                            cell.SetCellValue(dtos[i].SaleLink);
                            break;
                        case 34:
                            cell.SetCellValue(dtos[i].SalePrice.ToString());
                            break;
                        case 35:
                            cell.SetCellValue(dtos[i].HsCode);
                            break;
                        case 36://图片
                            if (!string.IsNullOrEmpty(dtos[i].Imageurl))
                            {
                                // 添加图片
                                // 1.以字节数组的形式读取图片
                                byte[] buffer = GetProductImageByte(dtos[i].Imageurl);
                                // 2.向Excel添加图片，获取到图片索引
                                int picIdx = excelBook.AddPicture(buffer, PictureType.JPEG);
                                // 3.构建Excel图像
                                XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                                // 4.确定图像的位置
                                // new XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)
                                // 前四个参数是单元格内的偏移量，这里需要填满整个单元格所以不设置。
                                // col1,row1表示图片的左上角在哪个单元格的左上角；col2,row2表示图片的右下角在哪个单元格的左上角
                                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
                                // 5.将图片设置到上面的位置中
                                XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, picIdx);
                            }
                            break;
                        case 37:
                            cell.SetCellValue(dtos[i].DeclarationMethod);
                            break;
                        case 39:
                            cell.SetCellValue(dtos[i].ProductNo);
                            break;


                    }
                }
            }
            #endregion
            #region sheet 2数据写入
            ISheet sheet2 = excelBook.CreateSheet("发票");
            #region 第一个表格写入
            float cellHeight = 30;
            //第一行
            IRow sheet2Row1 = sheet2.CreateRow(0);
            sheet2Row1.RowStyle = RowStyle;
            sheet2Row1.HeightInPoints = cellHeight;
            CellRangeAddress sheet2Row1Region = new CellRangeAddress(0, 0, 0, 9);
            sheet2.AddMergedRegion(sheet2Row1Region);
            ICell sheet2Row1Cell1 = sheet2Row1.CreateCell(0);
            sheet2Row1Cell1.CellStyle = headStyle;
            sheet2Row1Cell1.SetCellValue("Commercial Invoice");

            //第二行
            IRow sheet2Row2 = sheet2.CreateRow(1);
            sheet2Row2.RowStyle = RowStyle;
            sheet2Row2.HeightInPoints = cellHeight;
            CellRangeAddress sheet2Row2Region = new CellRangeAddress(1, 1, 0, 1);
            sheet2.AddMergedRegion(sheet2Row2Region);
            ICell sheet2Row2Cell1 = sheet2Row2.CreateCell(0);
            sheet2Row2Cell1.CellStyle = sheet2headStyle1;
            sheet2Row2Cell1.SetCellValue("Invoice No. \n（订单号 / 客户单号）");


            CellRangeAddress sheet2Row2Region2 = new CellRangeAddress(1, 1, 2, 7);
            sheet2.AddMergedRegion(sheet2Row2Region2);
            ICell sheet2Row2Cell2 = sheet2Row2.CreateCell(2);
            sheet2Row2Cell2.CellStyle = styleCommonCenter;
            sheet2Row2Cell2.SetCellValue(dtos[0].CustomerOrderNumber);


            ICell sheet2Row2Cell3 = sheet2Row2.CreateCell(8);
            sheet2Row2Cell3.CellStyle = sheet2headStyle1;
            sheet2Row2Cell3.SetCellValue("Date");

            ICell sheet2Row2Cell4 = sheet2Row2.CreateCell(9);
            sheet2Row2Cell4.CellStyle = styleCommonCenter;
            sheet2Row2Cell4.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd"));
            //第三行
            IRow sheet2Row3 = sheet2.CreateRow(2);
            sheet2Row3.HeightInPoints = cellHeight;
            sheet2Row3.RowStyle = RowStyle;
            CellRangeAddress sheet2Row3Region = new CellRangeAddress(2, 2, 0, 1);
            ICell sheet2Row3Cell1 = sheet2Row3.CreateCell(0);
            sheet2Row3Cell1.CellStyle = sheet2headStyle1;
            sheet2Row3Cell1.SetCellValue("Warehouse/Company \n（亚马逊仓库 / 公司）");
            sheet2.AddMergedRegion(sheet2Row3Region);

            CellRangeAddress sheet2Row3Region2 = new CellRangeAddress(2, 2, 2, 9);
            ICell sheet2Row3Cell2 = sheet2Row3.CreateCell(2);
            sheet2Row3Cell2.CellStyle = styleCommonCenter;
            sheet2Row3Cell2.SetCellValue(dtos[0].ReceiverName);
            sheet2.AddMergedRegion(sheet2Row3Region2);
            //第四行
            IRow sheet2Row4 = sheet2.CreateRow(3);
            sheet2Row4.RowStyle = RowStyle;
            sheet2Row4.HeightInPoints = cellHeight;
            CellRangeAddress sheet2Row4Region = new CellRangeAddress(3, 3, 0, 1);
            ICell sheet2Row4Cell1 = sheet2Row4.CreateCell(0);
            sheet2Row4Cell1.CellStyle = sheet2headStyle1;
            sheet2Row4Cell1.SetCellValue("Name \n（收件人信息）");
            sheet2.AddMergedRegion(sheet2Row4Region);

            CellRangeAddress sheet2Row4Region2 = new CellRangeAddress(3, 3, 2, 9);
            ICell sheet2Row4Cell2 = sheet2Row4.CreateCell(2);
            sheet2Row4Cell2.CellStyle = styleCommonCenter;
            sheet2Row4Cell2.SetCellValue(dtos[0].ReceiverName);
            sheet2.AddMergedRegion(sheet2Row4Region2);
            //第五行
            IRow sheet2Row5 = sheet2.CreateRow(4);
            sheet2Row5.RowStyle = RowStyle;
            sheet2Row5.HeightInPoints = cellHeight;
            CellRangeAddress sheet2Row5Region = new CellRangeAddress(4, 4, 0, 1);
            ICell sheet2Row5Cell1 = sheet2Row5.CreateCell(0);
            sheet2Row5Cell1.CellStyle = sheet2headStyle1;
            sheet2Row5Cell1.SetCellValue("Country \n（国家）");
            sheet2.AddMergedRegion(sheet2Row5Region);

            CellRangeAddress sheet2Row5Region2 = new CellRangeAddress(4, 4, 2, 4);
            ICell sheet2Row5Cell2 = sheet2Row5.CreateCell(2);
            sheet2Row5Cell2.CellStyle = styleCommonCenter;
            sheet2Row5Cell2.SetCellValue(dtos[0].ReceiverCountryCode);
            sheet2.AddMergedRegion(sheet2Row5Region2);

            CellRangeAddress sheet2Row5Region3 = new CellRangeAddress(4, 4, 5, 6);
            ICell sheet2Row5Cell3 = sheet2Row5.CreateCell(5);
            sheet2Row5Cell3.CellStyle = sheet2headStyle1;
            sheet2Row5Cell3.SetCellValue("City \n（城市）");
            sheet2.AddMergedRegion(sheet2Row5Region3);

            CellRangeAddress sheet2Row5Region4 = new CellRangeAddress(4, 4, 7, 9);
            ICell sheet2Row5Cell4 = sheet2Row5.CreateCell(7);
            sheet2Row5Cell4.CellStyle = styleCommonCenter;
            sheet2Row5Cell4.SetCellValue(dtos[0].ReceiverCity);
            sheet2.AddMergedRegion(sheet2Row5Region4);
            //第六行
            IRow sheet2Row6 = sheet2.CreateRow(5);
            sheet2Row6.RowStyle = RowStyle;
            sheet2Row6.HeightInPoints = cellHeight;
            CellRangeAddress sheet2Row6Region = new CellRangeAddress(5, 5, 0, 1);
            ICell sheet2Row6Cell1 = sheet2Row6.CreateCell(0);
            sheet2Row6Cell1.CellStyle = sheet2headStyle1;
            sheet2Row6Cell1.SetCellValue("Address \n（收件人地址）");
            sheet2.AddMergedRegion(sheet2Row6Region);

            CellRangeAddress sheet2Row6Region2 = new CellRangeAddress(5, 5, 2, 9);
            ICell sheet2Row6Cell2 = sheet2Row6.CreateCell(2);
            sheet2Row6Cell2.CellStyle = styleCommonCenter;
            sheet2Row6Cell2.SetCellValue(dtos[0].ReceiverStreet1);
            sheet2.AddMergedRegion(sheet2Row6Region2);
            //第七行
            IRow sheet2Row7 = sheet2.CreateRow(6);
            sheet2Row7.RowStyle = RowStyle;
            sheet2Row7.HeightInPoints = cellHeight;
            CellRangeAddress sheet2Row7Region = new CellRangeAddress(6, 6, 0, 1);
            ICell sheet2Row7Cell1 = sheet2Row7.CreateCell(0);
            sheet2Row7Cell1.CellStyle = sheet2headStyle1;
            sheet2Row7Cell1.SetCellValue("Contact/Tel \n（收件人联系方式）");
            sheet2.AddMergedRegion(sheet2Row7Region);

            CellRangeAddress sheet2Row7Region2 = new CellRangeAddress(6, 6, 2, 4);
            ICell sheet2Row7Cell2 = sheet2Row7.CreateCell(2);
            sheet2Row7Cell2.CellStyle = styleCommonCenter;
            sheet2Row7Cell2.SetCellValue(dtos[0].ReceiverPhone);
            sheet2.AddMergedRegion(sheet2Row7Region2);

            CellRangeAddress sheet2Row7Region3 = new CellRangeAddress(6, 6, 5, 6);
            ICell sheet2Row7Cell3 = sheet2Row7.CreateCell(5);
            sheet2Row7Cell3.CellStyle = sheet2headStyle1;
            sheet2Row7Cell3.SetCellValue("PostCode \n（邮编）");
            sheet2.AddMergedRegion(sheet2Row7Region3);

            CellRangeAddress sheet2Row7Region4 = new CellRangeAddress(6, 6, 7, 9);
            ICell sheet2Row7Cell4 = sheet2Row7.CreateCell(7);
            sheet2Row7Cell4.CellStyle = styleCommonCenter;
            sheet2Row7Cell4.SetCellValue(dtos[0].ReceiverZipCode);
            sheet2.AddMergedRegion(sheet2Row7Region4);
            #endregion
            #region 第二个表格写入
            List<string> sheetTableTitle1 = new List<string>() { "FBA", "Marks No.", "Description", "Description CN", "Quantity (pcs)", "Total Carton", "Total Gross Weight (Kgs)", "Unit Price \n (USD/EUR)", "Total Value \n (USD/EUR)", "Material", "USE", "HS code", "Link", "Picture", "尺寸" };
            List<string> sheetTableTitle2 = new List<string>() { "FBA单号", "唛头/箱号", "英文品名", "中文品名", "件数", "箱数", "毛重", "单价", "总价值", "材质", "用途", "HS编码", "销售链接", "图片", " " };
            IRow sheet2Row8 = sheet2.CreateRow(7);
            IRow sheet2Row9 = sheet2.CreateRow(8);
            for (int i = 0; i < sheetTableTitle1.Count; i++)
            {
                sheet2.SetColumnWidth(i, 20 * 256);
                ICell cell = sheet2Row8.CreateCell(i);
                cell.CellStyle = sheet2headStyle;
                cell.SetCellValue(sheetTableTitle1[i]);

                ICell cell2 = sheet2Row9.CreateCell(i);
                cell2.CellStyle = sheet2headStyle;
                cell2.SetCellValue(sheetTableTitle2[i]);
            }
            for (int i = 0; i < dtos.Count; i++)
            {
                IRow dataRow = sheet2.CreateRow(i + 9);
                for (int j = 0; j < sheetTableTitle1.Count; j++)
                {
                    ICell cell = dataRow.CreateCell(j);
                    cell.CellStyle = styleCommonCenter;
                    switch (j)
                    {
                        case 0:
                            cell.SetCellValue(dtos[i].CustomerOrderNumber);
                            break;
                        case 1:
                            cell.SetCellValue(dtos[i].BoxNumber);
                            break;
                        case 2:
                            cell.SetCellValue(dtos[i].EnName);
                            break;
                        case 3:
                            cell.SetCellValue(dtos[i].CnName);
                            break;
                        case 4:
                            cell.SetCellValue(dtos[i].Quantity);
                            break;
                        case 5:
                            cell.SetCellValue("1");
                            break;
                        case 6:
                            cell.SetCellValue(dtos[i].GoodsWeight.ToString());
                            break;
                        case 7:
                            cell.SetCellValue(dtos[i].GoodsValue.ToString());
                            break;
                        case 8:
                            decimal value9 = Math.Round(dtos[i].GoodsValue * dtos[i].Quantity, 2);
                            cell.SetCellValue(value9.ToString());
                            break;
                        case 9:
                            cell.SetCellValue(dtos[i].Meterial);
                            break;
                        case 10:
                            cell.SetCellValue(dtos[i].Usage);
                            break;
                        case 11:
                            cell.SetCellValue(dtos[i].HsCode);
                            break;
                        case 12:
                            cell.SetCellValue(dtos[i].SaleLink);
                            break;
                        case 13:
                            if (!string.IsNullOrEmpty(dtos[i].Imageurl))
                            {
                                // 添加图片
                                // 1.以字节数组的形式读取图片
                                byte[] buffer = GetProductImageByte(dtos[i].Imageurl);
                                // 2.向Excel添加图片，获取到图片索引
                                int picIdx = excelBook.AddPicture(buffer, PictureType.JPEG);
                                // 3.构建Excel图像
                                XSSFDrawing patriarch = (XSSFDrawing)sheet2.CreateDrawingPatriarch();
                                // 4.确定图像的位置
                                // new XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)
                                // 前四个参数是单元格内的偏移量，这里需要填满整个单元格所以不设置。
                                // col1,row1表示图片的左上角在哪个单元格的左上角；col2,row2表示图片的右下角在哪个单元格的左上角
                                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
                                // 5.将图片设置到上面的位置中
                                XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, picIdx);
                            }
                            break;
                        case 14:
                            cell.SetCellValue($"{dtos[i].Length}*{dtos[i].Width}*{dtos[i].Height}（cm）");
                            break;
                    }
                }
            }
            #endregion

            #endregion
            #region 文件保存
            MemoryStream stream = new MemoryStream();
            excelBook.Write(stream);
            var buf = stream.ToArray();
            //保存为Excel文件  
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
            #endregion
        }
        /// <summary>
        /// 下载图片
        /// </summary>
        /// <returns></returns>
        private byte[] GetProductImageByte(string fileUrl)
        {
            System.Net.WebClient client = new System.Net.WebClient();
            var data = client.DownloadData(fileUrl);
            return data;
        }
        /// <summary>
        /// 打包文件
        /// </summary>
        public void SaveZipFile(List<string> fileList, string savepath)
        {
            Crc32 crc = new Crc32();
            using (ZipOutputStream zipStream = new ZipOutputStream(File.Create(savepath)))
            {
                zipStream.SetLevel(9);   //压缩级别0-9  
                foreach (var url in fileList)
                {
                    //读取本地路径文件
                    byte[] bytes = System.IO.File.ReadAllBytes(url);
                    string tempFileName = url.Substring(url.LastIndexOf("\\") + 1);  //获得当前文件路径的文件名
                    tempFileName = tempFileName.Substring(0, tempFileName.IndexOf('_')) + ".xlsx";
                    ZipEntry entry = new ZipEntry(tempFileName);
                    entry.DateTime = DateTime.Now;
                    entry.Size = bytes.Length;
                    crc.Reset();
                    crc.Update(bytes);
                    zipStream.PutNextEntry(entry);
                    zipStream.Write(bytes, 0, bytes.Length);
                    //写入zip后删除文件
                    System.IO.File.Delete(url);
                }
            }
        }
        #endregion

        #region 导入末端渠道箱子信息
        public async Task<bool> ImportMpsBoxInfo(System.IO.Stream formFile, string UserName)
        {
            MSExcelHelper excelHelper = new MSExcelHelper();
            var list = excelHelper.LoadDataAsStream<Order_MpsOrderBoxExcelMpping, Order_MpsOrderBoxExcelDto>(formFile)?.Where(i => !string.IsNullOrWhiteSpace(i.BoxNumber)).ToList();
            var data = await ImportMpsBoxDataVerify(list);
            foreach (var item in data)
            {
                var temps = list.Where(x => x.BoxNumber == item.BoxNumber || x.BoxNumber == item.BoxServiceNumber);
                if (temps.Count() > 1)
                    throw new Exception("通过客户箱号查询箱子重复:" + item.BoxNumber + ",请联系技术人员");
                var temp = temps.FirstOrDefault();
                if (temp == null) { continue; }
                item.ChannelLength = temp.ChannelLength;
                item.ChannelWidth = temp.ChannelWidth;
                item.ChannelHeight = temp.ChannelHeight;
                item.ChannelWeight = temp.ChannelWeight;
            }
            //比逐条更新稍微快点
            string sql = @$" update Order_MpsOrderBox set ChannelImportName='{UserName}',ChannelLength = @ChannelLength,ChannelWidth=@ChannelWidth,ChannelHeight=@ChannelHeight,ChannelWeight=@ChannelWeight  where Id = @boxId ";
            await _unitOfWork.ExecuteAsync(sql, data);
            return true;
        }
        private async Task<List<Order_MpsOrderBoxExcelData>> ImportMpsBoxDataVerify(List<Order_MpsOrderBoxExcelDto> dtos)
        {
            string sql = @"select a.Id,c.Id boxId,c.BoxNumber,a.TrackingNumber,c.BoxServiceNumber 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
where a.OrderStatus>=1 and b.OrderId>0 and (c.BoxNumber in @BoxNumbers or c.BoxServiceNumber in @BoxNumbers)";
            var data = (await _unitOfWork.QueryBySqlAsync<Order_MpsOrderBoxExcelData>(sql, null, new { BoxNumbers = dtos.Select(x => x.BoxNumber).Distinct() })).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")).Distinct()
                    ));
            }
            return data;
        }
        #endregion

        #region 导入末端订单信息
        public async Task<bool> ImportFBAChannelOrder(System.IO.Stream formFile)
        {
            MSExcelHelper excelHelper = new MSExcelHelper();
            var list = excelHelper.LoadDataAsStream<MpsChannelOrderImpExcelMpping, MpsChannelOrderImpExcelDto>(formFile);
            //数据校验，并返回所有渠道信息
            var channelData = await ImportFBAChannelOrderDataVerify(list);
            foreach (var item in list)
            {
                item.ChannelId = channelData.Where(x => x.PublicName == item.ChannelName).FirstOrDefault().Id;
            }
            var orderGroup = list.GroupBy(x => x.SystemNo);
            foreach (var item in orderGroup)
            {
                string sql = @" update Order_Order set ChannelId=@ChannelId,TrackingNumber=@TrackingNumber where SystemNo=@SystemNo ";
                await _unitOfWork.ExecuteAsync(sql, new { ChannelId = item.FirstOrDefault().ChannelId, TrackingNumber = item.FirstOrDefault().TrackingNumber, SystemNo = item.FirstOrDefault().SystemNo });
                //比逐条更新稍微快点
                string boxSql = @"update Order_MpsOrderBox set BoxTrackingNumber=@TrackingNumber 
                                    where (BoxNumber=@BoxNumber or BoxServiceNumber=@BoxNumber) and MpsOrderId=(select MpsOrderId from Order_Order(nolock) where SystemNo=@SystemNo)";
                await _unitOfWork.ExecuteAsync(boxSql, item);
            }
            return true;
        }
        public async Task<List<Logistics_Channel>> ImportFBAChannelOrderDataVerify(List<MpsChannelOrderImpExcelDto> list)
        {
            if (list.Any(x => string.IsNullOrEmpty(x.SystemNo) || string.IsNullOrEmpty(x.ChannelName) || string.IsNullOrEmpty(x.BoxNumber) || string.IsNullOrEmpty(x.TrackingNumber))) { throw new Exception("当前表格存在空数据，请补充后重试！"); }
            List<string> systemNos = list.Select(x => x.SystemNo.Trim()).Distinct().ToList();
            List<string> channelNames = list.Select(x => x.ChannelName.Trim()).Distinct().ToList();

            string GetChannelSql = "select * from Logistics_Channel(nolock) where PublicName in @PublicName";
            var channelData = await _unitOfWork.QueryBySqlAsync<Logistics_Channel>(GetChannelSql, null, new { PublicName = channelNames });
            if (channelData == null || channelData.Count() == 0)
            {
                throw new Exception("未获取到服务产品信息，请确认名称是否传入正确");
            }
            else if (channelData.Count() != channelNames.Count)
            {
                List<string> temp = channelData.Select(x => x.PublicName).Distinct().ToList();
                var exceptData = channelNames.Except(temp);
                throw new Exception("以下渠道不存在系统，请检查后重试！" + string.Join("|", channelNames.ToList()));
            }

            string GetOrderSql = @"select SystemNo,isnull(a.MpsOrderId,0)MpsOrderId,c.BoxNumber,c.BoxServiceNumber 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
where SystemNo in @SystemNo ";
            var orderData = await _unitOfWork.QueryBySqlAsync<MpsChannelOrderVerifyDto>(GetOrderSql, null, new { SystemNo = systemNos });
            if (orderData.Any(x => x.MpsOrderId == 0))
            {
                throw new Exception("以下订单非FBA订单，不可使用此功能！"
                    + string.Join("|", orderData.Where(x => x.MpsOrderId == 0).Distinct().ToList())
                    );
            }
            var orderGroup = orderData.GroupBy(x => x.SystemNo);
            var impOrderGroup = list.GroupBy(x => x.SystemNo);
            foreach (var item in impOrderGroup)
            {
                var tempOrderGroup = orderGroup.Where(x => x.Key == item.Key)?.FirstOrDefault();
                if (tempOrderGroup == null || tempOrderGroup.Count() == 0) { throw new Exception($"订单：{item.Key},不存在系统中，请确认订单号是否正确！"); }
                else if (tempOrderGroup.Count() != item.Count()) { throw new Exception($"订单：{item.Key},箱号数量与系统不一样，请确认数据！"); }
                foreach (var box in item)
                {
                    var tempOrderBoxGroup = tempOrderGroup.Where(x => x.BoxServiceNumber == box.BoxNumber || x.BoxNumber == box.BoxNumber)?.FirstOrDefault();
                    if (tempOrderBoxGroup == null) { throw new Exception($"订单：{item.Key}中,不存在箱号:{box.BoxNumber}！"); }
                }
            }
            return channelData.ToList();
        }
        #endregion
    }


    public class Order_MpsOrderExcelDto
    {
        //物品
        public string EnName { get; set; }
        public string CnName { get; set; }
        public int Quantity { get; set; }
        public decimal GoodsWeight { get; set; }

        public decimal GoodsValueSum { get; set; }
        public decimal GoodsValue { get; set; }
        public string Currency { get; set; }
        public string HsCode { get; set; }
        public string SaleLink { get; set; }
        public string Usage { get; set; }
        public string Meterial { get; set; }
        public string Brand { get; set; }
        public string ModelNumber { get; set; }
        public string ProductNo { get; set; }

        //箱子
        public string BoxNumber { get; set; }
        public decimal Length { get; set; }
        public decimal Width { get; set; }
        public decimal Height { get; set; }
        public decimal BoxWeight { get; set; }

        /// <summary>
        /// 报关方式
        /// </summary>
        public string DeclarationMethod { get; set; }

        public string CustomerOrderNumber { get; set; }
        public string ChannelName { get; set; }

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

        public string FbaOrderNumber { get; set; }
        //FBA仓库代码
        public string FBAWareHouseCode { get; set; }
        public string ReceiverName { get; set; }
        public string ReceiverPhone { get; set; }
        public string ReceiverCountryCode { get; set; }
        public string ReceiverState { get; set; }
        public string ReceiverCity { get; set; }
        public string ReceiverStreet1 { get; set; }
        public string ReceiverStreet2 { get; set; }
        public string ReceiverZipCode { get; set; }
        public string ReceiverCompany { get; set; }
        public string ReceiverEmail { get; set; }
        /// <summary>
        /// 带磁
        /// </summary>
        public string Magnetic { get; set; }
        /// <summary>
        /// 带电
        /// </summary>
        public string Electric { get; set; }
        /// <summary>
        /// 危险品
        /// 原：液体
        /// </summary>
        public string Liquid { get; set; }
        /// <summary>
        /// 销售单价
        /// </summary>
        public decimal? SalePrice { get; set; }

        //错误信息
        public string ErrorMessage
        {
            get; set;
        }
        public string Imageurl { get; set; }
    }

    public class Order_MpsOrderMpping : MSExcelClassMapping<Order_MpsOrderExcelDto>
    {
        public Order_MpsOrderMpping()
        {
            Map(x => x.CustomerOrderNumber).Name("订单号/客户单号");
            Map(x => x.ChannelName).Name("线路产品");
            Map(x => x.BoxNumber).Name("箱号");
            Map(x => x.ReceiverCountryCode).Name("收件人国家");
            Map(x => x.ReceiverCity).Name("城市");
            Map(x => x.ReceiverState).Name("州/省");
            Map(x => x.ReceiverZipCode).Name("邮编");
            Map(x => x.ReceiverCompany).Name("公司名称");
            Map(x => x.ReceiverStreet1).Name("地址一");
            Map(x => x.ReceiverStreet2).Name("地址二");
            Map(x => x.ReceiverName).Name("收件人名称");
            Map(x => x.ReceiverPhone).Name("联系电话");
            Map(x => x.CnName).Name("品名 中文描述");
            Map(x => x.EnName).Name("品名 英文描述");
            Map(x => x.GoodsWeight).Name("单个产品重量（KG）");
            Map(x => x.Quantity).Name("产品件数");
            Map(x => x.BoxWeight).Name("单箱实重(KG)");
            Map(x => x.Length).Name("长");
            Map(x => x.Width).Name("宽");
            Map(x => x.Height).Name("高");
            Map(x => x.GoodsValue).Name("单个产品总申报");
            Map(x => x.GoodsValueSum).Name("申报总价值");
            Map(x => x.Currency).Name(@"申报币种（USD/EUR）");
            Map(x => x.Magnetic).Name("带磁");
            Map(x => x.Electric).Name("带电");
            Map(x => x.Liquid).Name("危险品");
            Map(x => x.Meterial).Name("材质");
            Map(x => x.Usage).Name("用途");
            Map(x => x.Brand).Name("品牌");
            Map(x => x.ModelNumber).Name("型号");
            Map(x => x.SaleLink).Name("销售链接");
            Map(x => x.SalePrice).Name("销售单价");
            Map(x => x.HsCode).Name("海关编码");
            Map(x => x.DeclarationMethod).Name("报关方式");
            Map(x => x.ProductNo).Name("ASIN");
        }
    }
}