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