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