public PartialViewResult PriceProductList(BaoYangPriceSelectModel param, string pageType, int pageIndex, int pageSize) { PagerModel pager = new PagerModel(pageIndex, pageSize); var manager = new BaoYangPriceGuideManager(); var result = new List <BaoYangPriceGuideList>(); var data = manager.SelectBaoYangPriceGuide(param, pager); if (data.Item1) { result = data.Item2; } ViewBag.SelectModel = param; ViewBag.PageType = pageType; return(PartialView(new ListModel <BaoYangPriceGuideList>() { Pager = pager, Source = result })); }
public ActionResult ExportExcel(BaoYangPriceSelectModel selectModel, string pageType) { PagerModel pager = new PagerModel(1, 100000); var manager = new BaoYangPriceGuideManager(); var list = manager.SelectBaoYangPriceGuide(selectModel, pager); //创建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 fileName = $"保养价格指导{ThreadIdentity.Operator.Name.Split('@')[0]}.xls"; var rowNum = 0; row.CreateCell(rowNum).SetCellValue("品牌"); row.CreateCell(rowNum++).SetCellValue("PID"); row.CreateCell(rowNum++).SetCellValue("二级类目"); row.CreateCell(rowNum++).SetCellValue("三级类目"); row.CreateCell(rowNum++).SetCellValue("产品名称"); row.CreateCell(rowNum++).SetCellValue("总库存"); row.CreateCell(rowNum++).SetCellValue("仓库库存"); row.CreateCell(rowNum++).SetCellValue("工场店库存"); row.CreateCell(rowNum++).SetCellValue("近7天销量"); row.CreateCell(rowNum++).SetCellValue("近30天销量"); row.CreateCell(rowNum++).SetCellValue("周转天数"); if (!pageType.Equals("see")) { row.CreateCell(rowNum++).SetCellValue("进货价"); row.CreateCell(rowNum++).SetCellValue("最近一次采购价"); } if (string.IsNullOrWhiteSpace(pageType)) { row.CreateCell(rowNum++).SetCellValue("理论指导价"); row.CreateCell(rowNum++).SetCellValue("实际指导价"); } row.CreateCell(rowNum++).SetCellValue("官网价格"); if (string.IsNullOrWhiteSpace(pageType)) { row.CreateCell(rowNum++).SetCellValue("活动价"); } if (!pageType.Equals("see")) { row.CreateCell(rowNum++).SetCellValue("途虎毛利率"); row.CreateCell(rowNum++).SetCellValue("途虎毛利额"); } row.CreateCell(rowNum++).SetCellValue("汽配龙"); if (!pageType.Equals("see")) { row.CreateCell(rowNum++).SetCellValue("汽配龙毛利率"); row.CreateCell(rowNum++).SetCellValue("汽配龙毛利额"); row.CreateCell(rowNum++).SetCellValue("工场店毛利率"); row.CreateCell(rowNum++).SetCellValue("工场店毛利额"); } row.CreateCell(rowNum++).SetCellValue("京东自营"); row.CreateCell(rowNum++).SetCellValue("特维轮天猫"); row.CreateCell(rowNum++).SetCellValue("养车无忧官网"); row.CreateCell(rowNum++).SetCellValue("汽车超人零售"); row.CreateCell(rowNum++).SetCellValue("康众官网"); row.CreateCell(rowNum++).SetCellValue("汽车超人批发"); row.CreateCell(rowNum++).SetCellValue("途虎淘宝"); row.CreateCell(rowNum++).SetCellValue("途虎淘宝2"); row.CreateCell(rowNum++).SetCellValue("途虎天猫1"); row.CreateCell(rowNum++).SetCellValue("途虎天猫2"); row.CreateCell(rowNum++).SetCellValue("途虎天猫3"); row.CreateCell(rowNum++).SetCellValue("途虎天猫4"); row.CreateCell(rowNum++).SetCellValue("途虎京东"); row.CreateCell(rowNum).SetCellValue("途虎京东旗舰"); if (list.Item1 && list.Item2.Any()) { var i = 0; foreach (var item in list.Item2) { var rowtempNum = 0; NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(++i); rowtemp.CreateCell(rowtempNum).SetCellValue(item.Brand); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.PID); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.SecondType); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ThirdType); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ProductName); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.totalstock == null ? "" : item.totalstock.Value.ToString()); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.totalstock == null ? "" : (item.totalstock.Value - item.ShopStock).ToString()); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ShopStock); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_week == null ? "" : item.num_week.Value.ToString()); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_month == null ? "" : item.num_month.Value.ToString()); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_month == null || item.totalstock == null || item.num_month <= 0 || item.totalstock <= 0 ? "" : Math.Ceiling((decimal)item.totalstock / item.num_month.Value * 30).ToString(CultureInfo.InvariantCulture)); if (!pageType.Equals("see")) { rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.cost == null ? "" : item.cost.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.PurchasePrice == null ? "" : item.PurchasePrice.Value.ToString("0.00")); } if (string.IsNullOrWhiteSpace(pageType)) { rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.TheoryGuidePrice == null ? "" : item.TheoryGuidePrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.ActualGuidePrice == null ? "" : item.ActualGuidePrice.Value.ToString("0.00")); } rowtemp.CreateCell(rowtempNum++).SetCellValue(item.Price.ToString("0.00")); if (string.IsNullOrWhiteSpace(pageType)) { rowtemp.CreateCell(rowtempNum++).SetCellValue(item.FlashSalePrice != null ? item.FlashSalePrice.Value.ToString("0.00") : "-"); } if (!pageType.Equals("see")) { rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0 ? ((item.Price - item.cost.Value) / item.Price).ToString("0.00%") : ""); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0 ? (item.Price - item.cost.Value).ToString("0.00") : ""); } rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QPLPrice == null ? "" : item.QPLPrice.Value.ToString("0.00")); if (!pageType.Equals("see")) { rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.QPLPrice > 0 && item.cost > 0 ? (((decimal)item.QPLPrice - item.cost.Value) / (decimal)item.QPLPrice).ToString("0.00%") : ""); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.QPLPrice > 0 && item.cost > 0 ? ((decimal)item.QPLPrice - item.cost.Value).ToString("0.00") : ""); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.QPLPrice > 0 && item.Price > 0 ? ((item.Price - (decimal)item.QPLPrice) / item.Price).ToString("0.00%") : ""); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.QPLPrice > 0 ? (item.Price - (decimal)item.QPLPrice).ToString("0.00") : ""); } rowtemp.CreateCell(rowtempNum++).SetCellValue(item.JDSelfPrice == null ? "" : item.JDSelfPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TWLTMPrice == null ? "" : item.TWLTMPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QccrlPrice == null ? "" : item.QccrlPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.YcwyPrice == null ? "" : item.YcwyPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.KzPrice == null ? "" : item.KzPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QccrpPrice == null ? "" : item.QccrpPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TBPrice == null ? "" : item.TBPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TB2Price == null ? "" : item.TB2Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM1Price == null ? "" : item.TM1Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM2Price == null ? "" : item.TM2Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM3Price == null ? "" : item.TM3Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM4Price == null ? "" : item.TM4Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.JDPrice == null ? "" : item.JDPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum).SetCellValue(item.JDFlagShipPrice == null ? "" : item.JDFlagShipPrice.Value.ToString("0.00")); } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", fileName)); }
public Tuple <bool, List <BaoYangPriceGuideList> > SelectBaoYangPriceGuide(BaoYangPriceSelectModel param, PagerModel pager) { List <BaoYangPriceGuideList> result = new List <BaoYangPriceGuideList>(); bool flag; try { flag = true; var baseData = DalBaoYangPriceGuide.SelectBaoYangProductInfo(param); baseData = IntegrateProductInfo(baseData, param); Func <BaoYangPriceGuideList, bool> func = (list) => { bool filter = true; //勾选库存 if (param.TotalStock != null) { filter = list.totalstock > 0; if (param.MatchTotalStock.Equals(1)) { filter = filter && list.totalstock >= param.TotalStock; } if (param.MatchTotalStock.Equals(-1)) { filter = filter && list.totalstock <= param.TotalStock; } } //勾选周转时间 if (param.TurnoverDays != null) { filter = filter && list.totalstock > 0 && list.num_month > 0; if (list.totalstock != null && list.num_month != null && list.totalstock > 0 && list.num_month > 0) { var turnoverDays = Math.Ceiling((decimal)list.totalstock / list.num_month.Value * 30); if (param.MatchTurnoverDays.Equals(1)) { filter = filter && turnoverDays >= param.TurnoverDays; } if (param.MatchTurnoverDays.Equals(-1)) { filter = filter && turnoverDays <= param.TurnoverDays; } } } //勾选毛利额 if (param.MaoLiE != null) { filter = filter && list.Price > 0 && list.cost > 0; if (param.MatchMaoLiE.Equals(1)) { filter = filter && (list.Price - list.cost ?? 0) >= param.MaoLiE; } if (param.MatchMaoLiE.Equals(-1)) { filter = filter && (list.Price - list.cost ?? 0) <= param.MaoLiE; } } //勾选毛利率 if (param.MaoLiLv != null) { filter = filter && list.Price > 0; if (list.Price > 0) { if (param.MatchMaoLiLv.Equals(1)) { filter = filter && (list.Price - list.cost ?? 0) / list.Price >= param.MaoLiLv / 100; } if (param.MatchMaoLiLv.Equals(-1)) { filter = filter && (list.Price - list.cost ?? 0) / list.Price <= param.MaoLiLv / 100; } } } //勾选汽配龙毛利额 if (param.QplMaoLiE != null) { filter = filter && list.QPLPrice > 0 && list.cost > 0; if (param.MatchQplMaoLiE.Equals(1)) { filter = filter && (list.QPLPrice - list.cost ?? 0) >= param.QplMaoLiE; } if (param.MatchQplMaoLiE.Equals(-1)) { filter = filter && (list.QPLPrice - list.cost ?? 0) <= param.QplMaoLiE; } } //勾选汽配龙毛利率 if (param.QplMaoLiLv != null) { filter = filter && list.QPLPrice > 0; if (list.QPLPrice > 0) { if (param.MatchQplMaoLiLv.Equals(1)) { filter = filter && (list.QPLPrice - list.cost ?? 0) / list.QPLPrice >= param.QplMaoLiLv / 100; } if (param.MatchQplMaoLiLv.Equals(-1)) { filter = filter && (list.QPLPrice - list.cost ?? 0) / list.QPLPrice <= param.QplMaoLiLv / 100; } } } //勾选工场店毛利额 if (param.ShopMaoLiE != null) { filter = filter && list.Price > 0 && list.QPLPrice > 0; if (param.MatchShopMaoLiE.Equals(1)) { filter = filter && (list.Price - list.QPLPrice ?? 0) >= param.ShopMaoLiE; } if (param.MatchShopMaoLiE.Equals(-1)) { filter = filter && (list.Price - list.QPLPrice ?? 0) <= param.ShopMaoLiE; } } //勾选工场店毛利率 if (param.ShopMaoLiLv != null) { filter = filter && list.QPLPrice > 0 && list.Price > 0; if (list.QPLPrice > 0 && list.Price > 0) { if (param.MatchShopMaoLiLv.Equals(1)) { filter = filter && (list.Price - list.QPLPrice ?? 0) / list.Price >= param.ShopMaoLiLv / 100; } if (param.MatchShopMaoLiLv.Equals(-1)) { filter = filter && (list.Price - list.QPLPrice ?? 0) / list.Price <= param.ShopMaoLiLv / 100; } } } //勾选实际指导价 if (param.PCPricePer != null) { filter = filter && list.Price > 0; if (param.MatchPCPricePer.Equals(1)) { filter = filter && (list.Price - list.ActualGuidePrice) / list.ActualGuidePrice >= param.PCPricePer / 100; } if (param.MatchPCPricePer.Equals(-1)) { filter = filter && (list.Price - list.ActualGuidePrice) / list.ActualGuidePrice <= param.PCPricePer / 100; } } //勾选价格对比 if (!string.IsNullOrWhiteSpace(param.SitePrices) && param.SitePrices.Split(',').Any()) { var propertys = list.GetType().GetProperties(); var contrast = param.Contrast.Equals(1) ? 1 : 0; decimal price = 0; var sitePrice = propertys.FirstOrDefault(p => p.Name.Equals(param.SitePrice)); if (sitePrice?.GetValue(list) != null) { price = (decimal)sitePrice.GetValue(list); } filter = filter && price > 0; if (price > 0) { var siteResult = true; var count = 0; foreach (var site in param.SitePrices.Split(',')) { foreach (PropertyInfo t in propertys) { if (site.Equals(t.Name)) { if (count == 0) { siteResult = PriceJudge(price, (decimal?)t.GetValue(list), param.Proportion, contrast); count++; } else { siteResult = siteResult || PriceJudge(price, (decimal?)t.GetValue(list), param.Proportion, contrast); } } } } filter = filter && siteResult; } } return(filter); }; var filterData = baseData.Where(func); if (filterData.Any()) { filterData = filterData.OrderByDescending(t => t.num_month); pager.TotalItem = filterData.Count(); result = filterData.Skip(pager.PageSize * (pager.CurrentPage - 1)).Take(pager.PageSize).Select(i => i).ToList(); AppendShopStock(result); GetDisPlayName(result); var pageNum = result.Count / 100 + (result.Count % 100 > 0 ? 1 : 0); for (var i = 0; i < pageNum; i++) { var currentData = result.Skip(100 * (i - 1)).Take(100).Select(t => t).ToList(); AppendQplPrice(currentData); GetFlashSalePrice(currentData); } } } catch (Exception ex) { Logger.Log(Level.Error, ex, "SelectBaoYangPriceGuide"); flag = false; } return(Tuple.Create(flag, result)); }
public static List <BaoYangPriceGuideList> SelectBaoYangProductInfo(BaoYangPriceSelectModel model) { var result = new List <BaoYangPriceGuideList>(); using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI"))) { #region sql var sql = @"SELECT VP.CP_Brand AS Brand , VP.PID , VP.DisplayName AS ProductName , VP.stockout AS StockStatus, VP.OnSale AS OnSaleStatus, VP.Category, DPSD.totalstock , DPSD.SelfStock , DPSD.num_week , DPSD.num_month , DPSD.num_threemonth , DPSD.cost , DPSD.PurchasePrice , VP.cy_list_price AS Price , VP.cy_marketing_price AS MarketingPrice , DPSD.taobao_tuhuid AS TBPID , DPSD.taobao_tuhuprice AS TBPrice , DPSD.taobao2_tuhuid AS TB2PID , DPSD.taobao2_tuhuprice AS TB2Price , DPSD.tianmao1_tuhuprice AS TM1Price , DPSD.tianmao1_tuhuid AS TM1PID , DPSD.tianmao2_tuhuprice AS TM2Price , DPSD.tianmao2_tuhuid AS TM2PID , DPSD.tianmao3_tuhuprice AS TM3Price , DPSD.tianmao3_tuhuid AS TM3PID , DPSD.tianmao4_tuhuid AS TM4PID , DPSD.tianmao4_tuhuprice AS TM4Price , DPSD.jingdong_tuhuprice AS JDPrice , DPSD.jingdong_tuhuid AS JDPID , DPSD.jingdongflagship_tuhuprice AS JDFlagShipPrice , DPSD.jingdongflagship_tuhuid AS JDFlagShipPID , DPSD.jingdongself_price AS JDSelfPrice , DPSD.jingdongself_id AS JDSelfPID , DPSD.teweilun_tianmaoprice AS TWLTMPrice , DPSD.teweilun_tianmaoid AS TWLTMPID, DPSD.qccr_retailprice AS QccrlPrice , DPSD.qccr_retailid AS QccrlId , DPSD.qccr_wholesaleprice AS QccrpPrice , DPSD.qccr_wholesaleid AS QccrpId, DPSD.yangche51_id AS YcwyId, DPSD.yangche51_price AS YcwyPrice, DPSD.carzone_id AS KzId, DPSD.carzone_price AS KzPrice FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) LEFT JOIN Tuhu_bi.dbo.dm_Product_SalespredictData AS DPSD WITH ( NOLOCK ) ON VP.PID = DPSD.pid WHERE ( ( @PID IS NULL OR @PID = '' ) OR VP.PID = @PID ) AND VP.Category IN ( SELECT CategoryName FROM Tuhu_productcatalog..vw_ProductCategories (NOLOCK) WHERE NodeNo LIKE N'%' + @FirstType + N'%' AND ( ( @SecondType IS NULL OR @SecondType = '' ) OR NodeNo LIKE N'%' + @SecondType + N'%' ) AND ( ( @ThirdType IS NULL OR @ThirdType = '' ) OR NodeNo LIKE N'%' + @ThirdType + N'%' )) AND ( VP.DisplayName LIKE '%' + @ProductName + '%' OR @ProductName IS NULL ) AND ( VP.CP_Brand = @Brand OR @Brand IS NULL ) AND ( @StockStatus = 0 OR @StockStatus = 1 AND VP.stockout = 0 OR @StockStatus = 2 AND VP.stockout = 1 ) AND ( @OnSaleStatus = 0 OR @OnSaleStatus = 1 AND VP.OnSale = 1 OR @OnSaleStatus = 2 AND VP.OnSale = 0 )"; #endregion SqlParameter[] parameters = { new SqlParameter("@PID", string.IsNullOrWhiteSpace(model.PID)?null:model.PID), new SqlParameter("@ProductName", string.IsNullOrWhiteSpace(model.ProductName)?null:model.ProductName), new SqlParameter("@Brand", string.IsNullOrWhiteSpace(model.Brand)?null:model.Brand), new SqlParameter("@StockStatus", model.StockStatus), new SqlParameter("@OnSaleStatus", model.OnSaleStatus), new SqlParameter("@FirstType", model.FirstType), new SqlParameter("@SecondType", model.SecondType), new SqlParameter("@ThirdType", model.ThirdType) }; var dt = dbHelper.ExecuteDataTable(sql, CommandType.Text, parameters); if (dt != null && dt.Rows.Count > 0) { result = dt.ConvertTo <BaoYangPriceGuideList>().ToList(); } return(result); } }
public List <BaoYangPriceGuideList> IntegrateProductInfo(List <BaoYangPriceGuideList> baseData, BaoYangPriceSelectModel param) { try { var warnLine = DalBaoYangPriceGuide.SelectWarningLine(); var priceWeight = DalBaoYangPriceGuide.SelectAllWeight(); var pageNum = baseData.Count / 100 + (baseData.Count % 100 > 0 ? 1 : 0); if (param.QplMaoLiE != null || param.QplMaoLiLv != null || param.ShopMaoLiE != null || param.ShopMaoLiLv != null || (!string.IsNullOrWhiteSpace(param.SitePrices) && param.SitePrices.Contains("QPLPrice")) || (!string.IsNullOrWhiteSpace(param.SitePrice) && param.SitePrice.Equals("QPLPrice"))) { for (var i = 0; i < pageNum; i++) { var currentData = baseData.Skip(100 * (i - 1)).Take(100).Select(t => t).ToList(); AppendQplPrice(currentData); } } foreach (var list in baseData) { //获取加权值 list.JiaQuan = (int)((priceWeight.FirstOrDefault(p => p.WeightType.Equals("Base"))? .WeightValue ?? 0) + (priceWeight.FirstOrDefault( p => p.WeightType.Equals("Brand") && p.WeightName.Equals(list.Brand) && p.CategoryName.Equals(list.Category))? .WeightValue ?? 0) + (priceWeight.FirstOrDefault( p => p.WeightType.Equals("Category") && p.WeightName.Equals(list.Category)) ? .WeightValue ?? 0)); var theoryGuidePrice = list.cost == null ? (decimal?)null : list.cost.Value * (100 + list.JiaQuan) / 100; decimal?actualGuidePrice = null; if (list.cost == null || list.cost == 0) { if (list.JDSelfPrice > 0) { actualGuidePrice = list.JDSelfPrice; } } else { if (list.JDSelfPrice == null) { actualGuidePrice = theoryGuidePrice; } else { actualGuidePrice = Math.Min(theoryGuidePrice.Value, list.JDSelfPrice.Value); } } list.TheoryGuidePrice = theoryGuidePrice; list.ActualGuidePrice = actualGuidePrice; //获取预警线 var currentWarnLine = warnLine.FirstOrDefault( p => p.MinGuidePrice <= (actualGuidePrice ?? 0) && p.MaxGuidePrice > (actualGuidePrice ?? 0)); if (currentWarnLine != null) { list.UpperLimit = currentWarnLine.UpperLimit; list.LowerLimit = currentWarnLine.LowerLimit; } } } catch (Exception ex) { Logger.Log(Level.Error, ex, "IntegrateProductInfo"); } return(baseData); }