/// <summary> /// 查询产品列表 /// </summary> /// <param name="conn"></param> /// <param name="pids"></param> /// <returns></returns> public IEnumerable <CompetingProductsMonitorModel> GetProductList(PriceSelectModel selectModel, PagerModel pager) { //获取商品列表 var list = PriceManager.SelectPriceProductList(selectModel, pager); //AutoMapper初始化配置文件 var config = new MapperConfiguration(cfg => cfg.CreateMap <TireListModel, CompetingProductsMonitorModel>()); var mapper = config.CreateMapper(); //集合类型转换 var productMonitorlist = mapper.Map <IEnumerable <CompetingProductsMonitorModel> >(list); var pids = list.Select(g => g.PID).ToList(); using (var conn = ProcessConnection.OpenConfigurationReadOnly) { //根据Pids查询竞品中最低价商品 var monitorList = dal.GetProductsMonitorbyPids(conn, pids); foreach (var pid in pids) { var rel = productMonitorlist.Where(g => g.PID == pid).First(); if (monitorList.Count() > 0) { var monitorModel = monitorList.Where(g => g.Pid == pid).FirstOrDefault(); if (monitorModel != null) { rel.MonitorCount = monitorModel.MonitorCount; rel.ItemID = monitorModel.ItemID; rel.SkuID = monitorModel.SkuID; rel.ItemCode = monitorModel.ItemCode; rel.Title = monitorModel.Title; rel.ShopCode = monitorModel.ShopCode; rel.MinPrice = monitorModel.MinPrice; rel.LastUpdateDateTime = monitorModel.LastUpdateDateTime; } } } return(productMonitorlist); } }
public async Task <HttpResponseMessage> ExportExcel() { JObject o = JObject.Parse(ContextRequest["searchCondition"]); var selectModel = o.ToObject <PriceSelectModel>(); selectModel.MatchWarnLine = -99; var pagesize = int.Parse(System.Configuration.ConfigurationManager.AppSettings["ExportSize"]); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row = sheet1.CreateRow(0); var user = HttpContext.Current.User.Identity.Name; var fileName = $"轮胎价格管理{user}.xls"; row.CreateCell(0).SetCellValue("品牌"); row.CreateCell(1).SetCellValue("PID"); row.CreateCell(2).SetCellValue("产品名称"); row.CreateCell(3).SetCellValue("原配车型"); row.CreateCell(4).SetCellValue("上架状态"); row.CreateCell(5).SetCellValue("展示状态"); row.CreateCell(6).SetCellValue("库存"); row.CreateCell(7).SetCellValue("近7天销量"); row.CreateCell(8).SetCellValue("近30天销量"); row.CreateCell(9).SetCellValue("进货价"); row.CreateCell(10).SetCellValue("最近一次采购价"); row.CreateCell(11).SetCellValue("理论指导价"); row.CreateCell(12).SetCellValue("实际指导价"); row.CreateCell(13).SetCellValue("官网价格"); row.CreateCell(14).SetCellValue("毛利率"); row.CreateCell(15).SetCellValue("毛利额"); row.CreateCell(16).SetCellValue("途虎淘宝"); row.CreateCell(17).SetCellValue("途虎淘宝2"); row.CreateCell(18).SetCellValue("途虎天猫1"); row.CreateCell(19).SetCellValue("途虎天猫2"); row.CreateCell(20).SetCellValue("途虎天猫3"); row.CreateCell(21).SetCellValue("途虎天猫4"); row.CreateCell(22).SetCellValue("途虎京东"); row.CreateCell(23).SetCellValue("途虎京东旗舰"); row.CreateCell(24).SetCellValue("途虎京东机油"); row.CreateCell(25).SetCellValue("途虎京东服务"); row.CreateCell(26).SetCellValue("汽配龙"); row.CreateCell(27).SetCellValue("京东自营"); row.CreateCell(28).SetCellValue("特维轮天猫"); row.CreateCell(29).SetCellValue("汽车超人零售"); row.CreateCell(30).SetCellValue("汽车超人批发"); row.CreateCell(31).SetCellValue("劵后价"); row.CreateCell(32).SetCellValue("汽配龙毛利额"); row.CreateCell(33).SetCellValue("汽配龙毛利率"); row.CreateCell(34).SetCellValue("工厂店毛利额"); row.CreateCell(35).SetCellValue("工厂店毛利率"); row.CreateCell(36).SetCellValue("采购在途"); row.CreateCell(37).SetCellValue("是否防爆"); row.CreateCell(38).SetCellValue("天猫养车零配件官方直营"); PagerModel pager = new PagerModel(1, pagesize); var list = PriceManager.SelectPriceProductList(selectModel, pager, true); var i = 0; while (list.Any()) { var pids = list.Select(g => g.PID).ToList(); //var activePrices = PriceManager.SelectActivePriceByPids(pids); var WhiteList = StockoutStatusManager.GetShowStatusByPids(pids); //var CaigouZaitu = PriceManager.SelectCaigouZaituByPids(pids); foreach (var item in list) { //var val = activePrices.Where(g => g.PID == item.PID).FirstOrDefault(); var Status = WhiteList.Where(g => g.PID == item.PID).FirstOrDefault(); var guidePriceStr = item.cost == null ? "" : (item.cost.Value + item.cost.Value * item.JiaQuan / 100).ToString("0.00"); //item.ActivePrice = val == null ? null : val.ActivePrice; item.ShowStatus = Status == null ? 0 : Status.ShowStatus; //item.CaigouZaitu = CaigouZaitu?.Where(g => g.PID == item.PID).FirstOrDefault()?.CaigouZaitu; var guidePriceStr_1 = ""; if (guidePriceStr == "") { if (item.JDSelfPrice != null) { guidePriceStr_1 = item.JDSelfPrice.Value.ToString("0.00"); } } else { if (item.JDSelfPrice == null) { guidePriceStr_1 = guidePriceStr; } else { if (Convert.ToDecimal(guidePriceStr) >= Convert.ToDecimal(item.JDSelfPrice)) { guidePriceStr_1 = item.JDSelfPrice.Value.ToString("0.00"); } else { guidePriceStr_1 = guidePriceStr; } } } var onSale = ""; onSale = item.OnSale == 0 ? "下架" : "上架"; var ShowStatus = "N/A"; if (item.ShowStatus == 1) { ShowStatus = "有货"; } else if (item.ShowStatus == 2) { ShowStatus = "缺货"; } else if (item.ShowStatus == 3) { ShowStatus = "不展示"; } var QPLProfi = item.QPLPrice != null && item.cost != null ? (item.QPLPrice.Value - item.cost.Value).ToString("0.00") : "-"; var QPLProfitRate = item.cost != null && item.QPLPrice != null && item.QPLPrice.Value != 0 ? ((item.QPLPrice.Value - item.cost.Value) / item.QPLPrice.Value).ToString("0.00%") : "-"; var ShopProfit = item.Price > 0 && item.QPLPrice != null ? (item.Price - item.QPLPrice.Value).ToString("0.00") : "-"; var ShopProfitRate = item.Price > 0 && item.QPLPrice != null ? ((item.Price - item.QPLPrice.Value) / item.Price).ToString("0.00%") : "-"; NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(++i); rowtemp.CreateCell(0).SetCellValue(item.Brand); rowtemp.CreateCell(1).SetCellValue(item.PID); rowtemp.CreateCell(2).SetCellValue(item.ProductName); rowtemp.CreateCell(3).SetCellValue(item.VehicleCount); rowtemp.CreateCell(4).SetCellValue(onSale); rowtemp.CreateCell(5).SetCellValue(ShowStatus); rowtemp.CreateCell(6).SetCellValue(item.totalstock == null ? "" : item.totalstock.Value.ToString()); rowtemp.CreateCell(7).SetCellValue(item.num_week == null ? "" : item.num_week.Value.ToString()); rowtemp.CreateCell(8).SetCellValue(item.num_month == null ? "" : item.num_month.Value.ToString()); rowtemp.CreateCell(9).SetCellValue(item.cost == null ? "" : item.cost.Value.ToString("0.00")); rowtemp.CreateCell(10).SetCellValue(item.PurchasePrice == null ? "" : item.PurchasePrice.Value.ToString("0.00")); rowtemp.CreateCell(11).SetCellValue(guidePriceStr); rowtemp.CreateCell(12).SetCellValue(guidePriceStr_1); rowtemp.CreateCell(13).SetCellValue(item.Price.ToString("0.00")); rowtemp.CreateCell(14).SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0 ? ((item.Price - item.cost.Value) / item.Price).ToString("0.00%") : ""); rowtemp.CreateCell(15).SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0 ? (item.Price - item.cost.Value).ToString("0.00") : ""); rowtemp.CreateCell(16).SetCellValue(item.TBPrice == null ? "" : item.TBPrice.Value.ToString("0.00")); rowtemp.CreateCell(17).SetCellValue(item.TB2Price == null ? "" : item.TB2Price.Value.ToString("0.00")); rowtemp.CreateCell(18).SetCellValue(item.TM1Price == null ? "" : item.TM1Price.Value.ToString("0.00")); rowtemp.CreateCell(19).SetCellValue(item.TM2Price == null ? "" : item.TM2Price.Value.ToString("0.00")); rowtemp.CreateCell(20).SetCellValue(item.TM3Price == null ? "" : item.TM3Price.Value.ToString("0.00")); rowtemp.CreateCell(21).SetCellValue(item.TM4Price == null ? "" : item.TM4Price.Value.ToString("0.00")); rowtemp.CreateCell(22).SetCellValue(item.JDPrice == null ? "" : item.JDPrice.Value.ToString("0.00")); rowtemp.CreateCell(23).SetCellValue(item.JDFlagShipPrice == null ? "" : item.JDFlagShipPrice.Value.ToString("0.00")); rowtemp.CreateCell(24).SetCellValue(item.JDJYPrice == null ? "" : item.JDJYPrice.Value.ToString("0.00")); rowtemp.CreateCell(25).SetCellValue(item.JDFWPrice == null ? "" : item.JDFWPrice.Value.ToString("0.00")); rowtemp.CreateCell(26).SetCellValue(item.QPLPrice == null ? "" : item.QPLPrice.Value.ToString("0.00")); rowtemp.CreateCell(27).SetCellValue(item.JDSelfPrice == null ? "" : item.JDSelfPrice.Value.ToString("0.00")); rowtemp.CreateCell(28).SetCellValue(item.TWLTMPrice == null ? "" : item.TWLTMPrice.Value.ToString("0.00")); rowtemp.CreateCell(29).SetCellValue(item.MLTTMPrice == null ? "" : item.MLTTMPrice.Value.ToString("0.00")); rowtemp.CreateCell(30).SetCellValue(item.MLTPrice == null ? "" : item.MLTPrice.Value.ToString("0.00")); rowtemp.CreateCell(31).SetCellValue(item.CouponPrice == null ? "" : item.CouponPrice.Value.ToString("0.00")); rowtemp.CreateCell(32).SetCellValue(QPLProfi); rowtemp.CreateCell(33).SetCellValue(QPLProfitRate); rowtemp.CreateCell(34).SetCellValue(ShopProfit); rowtemp.CreateCell(35).SetCellValue(ShopProfitRate); rowtemp.CreateCell(36).SetCellValue(item.CaigouZaitu == null ? "" : item.CaigouZaitu.Value.ToString()); rowtemp.CreateCell(37).SetCellValue(item.Rof ?? ""); rowtemp.CreateCell(38).SetCellValue(item.TMLPJPrice == null ? "" : item.TMLPJPrice.Value.ToString("0.00")); } pager.CurrentPage += 1; list = PriceManager.SelectPriceProductList(selectModel, pager, true); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); var response = new HttpResponseMessage(HttpStatusCode.OK) { Content = new StreamContent(ms) }; response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/ms-excel"); response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = HttpUtility.UrlEncode(fileName) }; return(response); }