public static bool MergeIntoProductStatistics(List <ProductStatisticsModel> models, IEnumerable <ProductDefaultFavourableStatisticsModel> defaultFavourableStatisticsList) { if (models == null || !models.Any()) { return(true); } string DBName = GetSwitchValue() ? DBName_ProductStatistics + "_temp" : DBName_ProductStatistics; #region sql var sql = @"MERGE [Tuhu_productcatalog].[dbo].[" + DBName + @"] WITH ( ROWLOCK ) AS A USING ( {models} ) AS B ON A.ProductID = B.ProductID AND A.VariantID = B.VariantID WHEN NOT MATCHED THEN INSERT ( ProductID ,VariantID ,OrderQuantity , SalesQuantity ,CommentTimes ,CommentR1 , CommentR2 , CommentR3 ,CommentR4 ,CommentR5 , CommentRate , FavourableCount ,DefaultFavourableCount ,Score,CommentTimesB,CommentR1B,CommentR2B,CommentR3B,CommentR4B,CommentR5B,CreateTime ) VALUES ( B.ProductID , B.VariantID , B.OrderQuantity , B.SalesQuantity , B.CommentTimes , B.CommentR1 , B.CommentR2 , B.CommentR3 , B.CommentR4 , B.CommentR5 , B.CommentRate , B.FavourableCount ,B.DefaultFavourableCount ,B.Score,B.CommentTimesB,B.CommentR1B,B.CommentR2B,B.CommentR3B,B.CommentR4B,B.CommentR5B,getdate() ) WHEN MATCHED THEN UPDATE SET OrderQuantity = B.OrderQuantity , SalesQuantity = B.SalesQuantity , CommentTimes = B.CommentTimes , CommentR1 = B.CommentR1 , CommentR2 = B.CommentR2 , CommentR3 = B.CommentR3 , CommentR4 = B.CommentR4 , CommentR5 = B.CommentR5 , CommentRate = B.CommentRate , FavourableCount = B.FavourableCount , DefaultFavourableCount = B.DefaultFavourableCount , Score = B.Score, CommentTimesB=B.CommentTimesB, CommentR1B=B.CommentR1B, CommentR2B=B.CommentR2B, CommentR3B=B.CommentR3B, CommentR4B=B.CommentR4B, CommentR5B=B.CommentR5B, LastUpdateTime=getdate() ;"; #endregion using (var cmd = new SqlCommand()) { var modelSqlList = new List <string>(); models.ForEach(p => { //获取产品 默认好评个数 ProductDefaultFavourableStatisticsModel _ProductDefaultFavourableStatisticsModel = defaultFavourableStatisticsList.Where(l => l.ProductFamilyId == p.ProductID).FirstOrDefault() ?? new ProductDefaultFavourableStatisticsModel(); p.DefaultFavourableCount = _ProductDefaultFavourableStatisticsModel.DefaultFavourableCount; //字段 备份 p.CommentTimesB = p.CommentTimes; p.CommentR1B = p.CommentR1; p.CommentR2B = p.CommentR2; p.CommentR3B = p.CommentR3; p.CommentR4B = p.CommentR4; p.CommentR5B = p.CommentR5; p.Score = p.CommentRate; //重新赋值 p.CommentTimes = p.CommentTimes + p.DefaultFavourableCount; p.CommentR1 = p.CommentR1 + p.DefaultFavourableCount * 5; p.CommentR2 = p.CommentR2 + p.DefaultFavourableCount * 5; p.CommentR3 = p.CommentR3 + p.DefaultFavourableCount * 5; p.CommentR4 = p.CommentR4 + p.DefaultFavourableCount * 5; p.CommentR5 = p.CommentR5 + p.DefaultFavourableCount * 5; //产品的平均分(带上默认好评的) score 的分数需要重新 计算 if (p.CommentTimes > 0) { if (p.ProductID.ToUpper().IndexOf("TR") == 0)//轮胎算法 { p.CommentRate = float.Parse(((p.CommentR1 + p.CommentR2 + p.CommentR3 + p.CommentR4 + p.CommentR5) / (p.CommentTimes * 5.0)).ToString("#0.00000")); } else { p.CommentRate = float.Parse((p.CommentR1 / (p.CommentTimes * 1.0)).ToString("#0.00000")); } } modelSqlList.Add($@" SELECT N'{p.ProductID}' ProductID , N'{p.VariantID}' VariantID , N'{p.OrderQuantity}' OrderQuantity , N'{p.SalesQuantity}' SalesQuantity , N'{p.CommentTimes}' CommentTimes , N'{p.CommentR1}' CommentR1 , N'{p.CommentR2}' CommentR2 , N'{p.CommentR3}' CommentR3 , N'{p.CommentR4}' CommentR4 , N'{p.CommentR5}' CommentR5 , N'{p.CommentRate}' CommentRate, N'{p.FavourableCount}' FavourableCount , N'{p.DefaultFavourableCount}' DefaultFavourableCount , N'{p.Score}' Score , N'{p.CommentTimesB}' CommentTimesB , N'{p.CommentR1B}' CommentR1B , N'{p.CommentR2B}' CommentR2B , N'{p.CommentR3B}' CommentR3B , N'{p.CommentR4B}' CommentR4B , N'{p.CommentR5B}' CommentR5B "); }); cmd.CommandText = sql.Replace("{models}", string.Join(" UNION ALL ", modelSqlList)); return(DbHelper.ExecuteNonQuery(cmd) > 0); } }
public void Execute(IJobExecutionContext context) { Stopwatch watch = Stopwatch.StartNew(); UpdateProductStatisticsLogger.Info($"产品评分统计更新开始"); int index = 1; try { var allProductComments = DalProductStatistics1.GetProductStatisticsByPage(); UpdateProductStatisticsLogger.Info($"GetProductStatisticsByPage:执行时间={watch.ElapsedMilliseconds};allProductComments.count={allProductComments.Count()}"); watch.Restart(); //除去 temp的产品 allProductComments = allProductComments.Where(p => p.ProductID.ToUpper().IndexOf("TEMP-") != 0).ToList(); UpdateProductStatisticsLogger.Info($"除去 temp的产品:执行时间={watch.ElapsedMilliseconds};allProductComments.count={allProductComments.Count()}"); watch.Restart(); //获取所有 产品的 默认好评数 List <ProductCommentStatusModel> ProductCommentStatusList = DalProductStatistics1.GetAllProductDefaultFavourableStatistics().ToList(); UpdateProductStatisticsLogger.Info($"GetAllProductDefaultFavourableStatistics:执行时间={watch.ElapsedMilliseconds};ProductCommentStatusList={ProductCommentStatusList.Count()}"); watch.Restart(); List <ProductDefaultFavourableStatisticsModel> defaultFavourableStatisticsList = new List <ProductDefaultFavourableStatisticsModel>(); foreach (var item in ProductCommentStatusList) { ProductDefaultFavourableStatisticsModel model = new ProductDefaultFavourableStatisticsModel(); if (item.ProductId.Split('|').Count() < 2) { UpdateProductStatisticsLogger.Info($"ProductCommentStatus ProductId 是familyid:ProductId={item.ProductId};"); model.ProductFamilyId = item.ProductId.Split('|')[0]; model.VariantID = "0"; model.DefaultFavourableCount = item.DefaultFavourableCount; } else { model.ProductFamilyId = item.ProductId.Split('|')[0]; model.VariantID = item.ProductId.Split('|')[1]; model.DefaultFavourableCount = item.DefaultFavourableCount; } defaultFavourableStatisticsList.Add(model); } List <ProductDefaultFavourableStatisticsModel> defaultFavourableStatisticsList_Comment = new List <ProductDefaultFavourableStatisticsModel>(); //所有的产品 的 默认好评数相同 foreach (var item in defaultFavourableStatisticsList) { ProductDefaultFavourableStatisticsModel model = new ProductDefaultFavourableStatisticsModel(); model.ProductFamilyId = item.ProductFamilyId; model.VariantID = item.VariantID; model.DefaultFavourableCount = defaultFavourableStatisticsList.Where(p => p.ProductFamilyId == item.ProductFamilyId).Sum(p => p.DefaultFavourableCount); defaultFavourableStatisticsList_Comment.Add(model); } UpdateProductStatisticsLogger.Info($"GetAllProductDefaultFavourableStatistics:执行时间={watch.ElapsedMilliseconds};defaultFavourableStatisticsList={defaultFavourableStatisticsList.Count()}"); watch.Restart(); if (allProductComments != null && allProductComments.Any()) { Parallel.ForEach(allProductComments.GroupBy(g => g.ProductID), new ParallelOptions() { MaxDegreeOfParallelism = 5 }, f => { Stopwatch tempWatch = Stopwatch.StartNew(); try { var mergeResult = DalProductStatistics1.MergeIntoProductStatistics(f.ToList(), defaultFavourableStatisticsList_Comment); if (!mergeResult) { UpdateProductStatisticsLogger.Error($"MergeIntoProductStatistics-->产品评分统计更新失败:ProductId={f.Key}:执行时间={tempWatch.ElapsedMilliseconds}"); } } catch (Exception ex) { UpdateProductStatisticsLogger.Error($"产品评分统计更新异常:ProductId={f.Key};{ex}:执行时间={tempWatch.ElapsedMilliseconds}"); } index++; UpdateProductStatisticsLogger.Info($"产品评分统计更新:第{index}个商品ProductId={f.Key}数据更新完成:执行时间={tempWatch.ElapsedMilliseconds}"); }); } ; //allProductComments.GroupBy(g => g.ProductID).ForEach(f => //{ // Stopwatch tempWatch = Stopwatch.StartNew(); // try // { // var mergeResult = DalProductStatistics1.MergeIntoProductStatistics(f.ToList(), defaultFavourableStatisticsList); // if (!mergeResult) // { // UpdateProductStatisticsLogger.Error($"MergeIntoProductStatistics-->产品评分统计更新失败:ProductId={f.Key}:执行时间={tempWatch.ElapsedMilliseconds}"); // } // //var dbProductStatistics = DalProductStatistics.GetProductStatisticsByProductId(f.Key); // //dbProductStatistics.Split(100).ForEach(oneList => { // // var mergeResult = DalProductStatistics.MergeIntoProductStatistics(oneList?.ToList()); // // if (!mergeResult) // // { // // UpdateProductStatisticsLogger.Error($"MergeIntoProductStatistics-->产品评分统计更新失败:ProductId={f.Key}"); // // } // //}); // } // catch (Exception ex) // { // UpdateProductStatisticsLogger.Error($"产品评分统计更新异常:ProductId={f.Key};{ex}:执行时间={tempWatch.ElapsedMilliseconds}"); // } // index++; // UpdateProductStatisticsLogger.Info($"产品评分统计更新:第{index}个商品ProductId={f.Key}数据更新完成:执行时间={tempWatch.ElapsedMilliseconds}"); //}); if (!DalProductStatistics1.UpdatetCarPAR_CatalogProducts())//更新CarPAR_CatalogProducts中的OrderQuantity,SalesQuantity数据 { UpdateProductStatisticsLogger.Error($"产品评分统计更新:OrderQuantity,SalesQuantity更新失败"); } else { UpdateProductStatisticsLogger.Info($"UpdatetCarPAR_CatalogProducts 更新:OrderQuantity,SalesQuantity更新成功:执行时间={watch.ElapsedMilliseconds}"); watch.Restart(); } } catch (Exception ex) { UpdateProductStatisticsLogger.Error($"产品评分统计更新异常:{ex}"); } UpdateProductStatisticsLogger.Info($"产品评分统计更新结束 time = {watch.ElapsedMilliseconds}"); watch.Stop(); }