public static bool UpdateShopStatistic(ShopStatisticsModel model)
{
#region sql
const string sql = @"UPDATE [Gungnir].[dbo].[tbl_ShopStatistics] WITH(ROWLOCK)
SET InstallQuantity =@InstallQuantity,
CommentTimes = @CommentTimes,
CommentR1 = @CommentR1,
CommentR2 = @CommentR2,
CommentR3 = @CommentR3,
CommentR4 = @CommentR4,
ApplyCommentTimes=@ApplyCommentTimes,
ApplyCommentRate1=@ApplyCommentRate1,
ApplyCommentRate2=@ApplyCommentRate2,
ApplyCommentRate3=@ApplyCommentRate3,
ApplyCommentRate4=@ApplyCommentRate4
WHERE ShopId=@ShopId and Type=@Type";
#endregion
using (var cmd = new SqlCommand(sql))
{
cmd.Parameters.AddWithValue("@InstallQuantity", model.InstallQuantity);
cmd.Parameters.AddWithValue("@CommentTimes", model.CommentTimes);
cmd.Parameters.AddWithValue("@CommentR1", model.CommentR1);
cmd.Parameters.AddWithValue("@CommentR2", model.CommentR2);
cmd.Parameters.AddWithValue("@CommentR3", model.CommentR3);
cmd.Parameters.AddWithValue("@CommentR4", model.CommentR4);
cmd.Parameters.AddWithValue("@ApplyCommentTimes", model.ApplyCommentTimes);
cmd.Parameters.AddWithValue("@ApplyCommentRate1", model.ApplyCommentRate1);
cmd.Parameters.AddWithValue("@ApplyCommentRate2", model.ApplyCommentRate2);
cmd.Parameters.AddWithValue("@ApplyCommentRate3", model.ApplyCommentRate3);
cmd.Parameters.AddWithValue("@ApplyCommentRate4", model.ApplyCommentRate4);
cmd.Parameters.AddWithValue("@ShopId", model.ShopID);
cmd.Parameters.AddWithValue("@Type", model.Type);
return(DbHelper.ExecuteNonQuery(cmd) > 0);
}
}
public static bool UpdateShopStatistic(ShopStatisticsModel model)
{
string DBName = GetSwitchValue() ? DBName_ShopStatistics + "_temp" : DBName_ShopStatistics;
#region sql
string sql = @"UPDATE [Gungnir].[dbo].[" + DBName + @"] WITH(ROWLOCK)
SET InstallQuantity =@InstallQuantity,
CommentTimes = @CommentTimes,
CommentR1 = @CommentR1,
CommentR2 = @CommentR2,
CommentR3 = @CommentR3,
CommentR4 = @CommentR4,
ApplyCommentTimes=@ApplyCommentTimes,
ApplyCommentRate1=@ApplyCommentRate1,
ApplyCommentRate2=@ApplyCommentRate2,
ApplyCommentRate3=@ApplyCommentRate3,
ApplyCommentRate4=@ApplyCommentRate4,
LastUpdateTime=getdate(),
FavourableCount = @FavourableCount,
DefaultFavourableCount = @DefaultFavourableCount,
Score = @Score,
CommentTimesB = @CommentTimesB,
CommentR1B = @CommentR1B,
CommentR2B = @CommentR2B,
CommentR3B = @CommentR3B,
CommentR4B = @CommentR4B
WHERE ShopId=@ShopId and Type=@Type";
#endregion
using (var cmd = new SqlCommand(sql))
{
//cmd.Parameters.AddWithValue("@DBName", DBName);
cmd.Parameters.AddWithValue("@InstallQuantity", model.InstallQuantity);
cmd.Parameters.AddWithValue("@CommentTimes", model.CommentTimes);
cmd.Parameters.AddWithValue("@CommentR1", model.CommentR1);
cmd.Parameters.AddWithValue("@CommentR2", model.CommentR2);
cmd.Parameters.AddWithValue("@CommentR3", model.CommentR3);
cmd.Parameters.AddWithValue("@CommentR4", model.CommentR4);
cmd.Parameters.AddWithValue("@ApplyCommentTimes", model.ApplyCommentTimes);
cmd.Parameters.AddWithValue("@ApplyCommentRate1", model.ApplyCommentRate1);
cmd.Parameters.AddWithValue("@ApplyCommentRate2", model.ApplyCommentRate2);
cmd.Parameters.AddWithValue("@ApplyCommentRate3", model.ApplyCommentRate3);
cmd.Parameters.AddWithValue("@ApplyCommentRate4", model.ApplyCommentRate4);
cmd.Parameters.AddWithValue("@FavourableCount", model.FavourableCount);
cmd.Parameters.AddWithValue("@DefaultFavourableCount", model.DefaultFavourableCount);
cmd.Parameters.AddWithValue("@Score", model.Score);
cmd.Parameters.AddWithValue("@CommentTimesB", model.CommentTimesB);
cmd.Parameters.AddWithValue("@CommentR1B", model.CommentR1B);
cmd.Parameters.AddWithValue("@CommentR2B", model.CommentR2B);
cmd.Parameters.AddWithValue("@CommentR3B", model.CommentR3B);
cmd.Parameters.AddWithValue("@CommentR4B", model.CommentR4B);
cmd.Parameters.AddWithValue("@ShopId", model.ShopID);
cmd.Parameters.AddWithValue("@Type", model.Type);
return(DbHelper.ExecuteNonQuery(cmd) > 0);
}
}
public static bool InsertShopStatistic(ShopStatisticsModel model)
{
#region sql
const string sql = @"Insert into [Gungnir].[dbo].[tbl_ShopStatistics]
([ShopID]
,[Type]
,[InstallQuantity]
,[CommentTimes]
,[CommentR1]
,[CommentR2]
,[CommentR3]
,[CommentR4]
,[ApplyCommentTimes]
,[ApplyCommentRate1]
,[ApplyCommentRate2]
,[ApplyCommentRate3]
,[ApplyCommentRate4]
,CreateTime
)
values(@ShopId,
@Type,
@InstallQuantity,
@CommentTimes,
@CommentR1,
@CommentR2,
@CommentR3,
@CommentR4,
@ApplyCommentTimes,
@ApplyCommentRate1,
@ApplyCommentRate2,
@ApplyCommentRate3,
@ApplyCommentRate4
,getdate())";
#endregion
using (var cmd = new SqlCommand(sql))
{
cmd.Parameters.AddWithValue("@InstallQuantity", model.InstallQuantity);
cmd.Parameters.AddWithValue("@CommentTimes", model.CommentTimes);
cmd.Parameters.AddWithValue("@CommentR1", model.CommentR1);
cmd.Parameters.AddWithValue("@CommentR2", model.CommentR2);
cmd.Parameters.AddWithValue("@CommentR3", model.CommentR3);
cmd.Parameters.AddWithValue("@CommentR4", model.CommentR4);
cmd.Parameters.AddWithValue("@ApplyCommentTimes", model.ApplyCommentTimes);
cmd.Parameters.AddWithValue("@ApplyCommentRate1", model.ApplyCommentRate1);
cmd.Parameters.AddWithValue("@ApplyCommentRate2", model.ApplyCommentRate2);
cmd.Parameters.AddWithValue("@ApplyCommentRate3", model.ApplyCommentRate3);
cmd.Parameters.AddWithValue("@ApplyCommentRate4", model.ApplyCommentRate4);
cmd.Parameters.AddWithValue("@ShopId", model.ShopID);
cmd.Parameters.AddWithValue("@Type", model.Type);
return(DbHelper.ExecuteNonQuery(cmd) > 0);
}
}
public static bool InsertShopStatistic(ShopStatisticsModel model)
{
string DBName = GetSwitchValue() ? DBName_ShopStatistics + "_temp" : DBName_ShopStatistics;
#region sql
string sql = @"Insert into [Gungnir].[dbo].[" + DBName + @"]
(ShopId,
Type,InstallQuantity,CommentTimes,CommentR1, CommentR2,CommentR3, CommentR4,
ApplyCommentTimes,ApplyCommentRate1,ApplyCommentRate2,ApplyCommentRate3,ApplyCommentRate4,
FavourableCount,DefaultFavourableCount,Score,CommentTimesB,CommentR1B,CommentR2B,CommentR3B,CommentR4B,CreateTime
)
values(
@ShopId,@Type,@InstallQuantity,@CommentTimes,@CommentR1, @CommentR2,@CommentR3, @CommentR4,
@ApplyCommentTimes,@ApplyCommentRate1,@ApplyCommentRate2,@ApplyCommentRate3,@ApplyCommentRate4,
@FavourableCount,@DefaultFavourableCount,@Score,@CommentTimesB,@CommentR1B,@CommentR2B,@CommentR3B,@CommentR4B,getdate()
)";
#endregion
using (var cmd = new SqlCommand(sql))
{
//cmd.Parameters.AddWithValue("@DBName", DBName);
cmd.Parameters.AddWithValue("@InstallQuantity", model.InstallQuantity);
cmd.Parameters.AddWithValue("@CommentTimes", model.CommentTimes);
cmd.Parameters.AddWithValue("@CommentR1", model.CommentR1);
cmd.Parameters.AddWithValue("@CommentR2", model.CommentR2);
cmd.Parameters.AddWithValue("@CommentR3", model.CommentR3);
cmd.Parameters.AddWithValue("@CommentR4", model.CommentR4);
cmd.Parameters.AddWithValue("@ApplyCommentTimes", model.ApplyCommentTimes);
cmd.Parameters.AddWithValue("@ApplyCommentRate1", model.ApplyCommentRate1);
cmd.Parameters.AddWithValue("@ApplyCommentRate2", model.ApplyCommentRate2);
cmd.Parameters.AddWithValue("@ApplyCommentRate3", model.ApplyCommentRate3);
cmd.Parameters.AddWithValue("@ApplyCommentRate4", model.ApplyCommentRate4);
cmd.Parameters.AddWithValue("@ShopId", model.ShopID);
cmd.Parameters.AddWithValue("@Type", model.Type);
cmd.Parameters.AddWithValue("@FavourableCount", model.FavourableCount);
cmd.Parameters.AddWithValue("@DefaultFavourableCount", model.DefaultFavourableCount);
cmd.Parameters.AddWithValue("@Score", model.Score);
cmd.Parameters.AddWithValue("@CommentTimesB", model.CommentTimesB);
cmd.Parameters.AddWithValue("@CommentR1B", model.CommentR1B);
cmd.Parameters.AddWithValue("@CommentR2B", model.CommentR2B);
cmd.Parameters.AddWithValue("@CommentR3B", model.CommentR3B);
cmd.Parameters.AddWithValue("@CommentR4B", model.CommentR4B);
return(DbHelper.ExecuteNonQuery(cmd) > 0);
}
}
public static void DoJob()
{
Stopwatch watch = Stopwatch.StartNew();
var shopsModel_TR = DalShopStatistics1.GetShopStatisticsByTR(); //统计出来的数据
var shopsModel_MR = DalShopStatistics1.GetShopStatisticsByMR(); //统计出来的数据
var shopsModel_FW = DalShopStatistics1.GetShopStatisticsByFW(); //统计出来的数据
var shopsModel_PQ = DalShopStatistics1.GetShopStatisticsByPQ(); //统计出来的数据
var shopsModel_BY = DalShopStatistics1.GetShopStatisticsByBY(); //统计出来的数据
Task.WaitAll(shopsModel_TR, shopsModel_MR, shopsModel_FW, shopsModel_PQ, shopsModel_BY);
var shopsModel = new List <ShopStatisticsModel>();
shopsModel.AddRange(shopsModel_TR.Result);
shopsModel.AddRange(shopsModel_MR.Result);
shopsModel.AddRange(shopsModel_FW.Result);
shopsModel.AddRange(shopsModel_PQ.Result);
shopsModel.AddRange(shopsModel_BY.Result);
UpdateShopStatisticsLogger.Info($"GetShopStatisticsBy **:执行时间={watch.ElapsedMilliseconds};shopsModel.count={shopsModel.Count()}"); watch.Restart();
//shopsModel = shopsModel.Where(p => p.ShopID == 38&&p.Type=="TR").ToList();
//获取 不同 分类下的 默认好评数
List <ShopDefaultFavourableStatisticsModel> defaultFavourableList = DalShopStatistics1.GetAllProductDefaultFavourableStatistics();
foreach (var item in shopsModel)
{
var defaultFavourableStatistics = defaultFavourableList.Where(p => p.ShopID == item.ShopID && p.Type == item.Type).FirstOrDefault() ?? new ShopDefaultFavourableStatisticsModel();
item.DefaultFavourableCount = defaultFavourableStatistics.DefaultFavourableCount;
//重新计算评分
if (item.CommentTimes > 0)
{
item.Score = (item.CommentR1 + 5 * item.DefaultFavourableCount) / (item.CommentTimes + item.DefaultFavourableCount);
}
}
UpdateShopStatisticsLogger.Info($"GetAllProductDefaultFavourableStatistics **:执行时间={watch.ElapsedMilliseconds};defaultFavourableList.count={shopsModel.Count()}"); watch.Restart();
var shopsModel_ALL = new List <ShopStatisticsModel>();
shopsModel.GroupBy(g => g.ShopID).ForEach(f =>
{
ShopStatisticsModel _ShopStatisticsModel = new ShopStatisticsModel()
{
ShopID = f.Key,
Type = "ALL",
InstallQuantity = (f.ToList()?.Sum(e => e.InstallQuantity) ?? 0),
CommentR1 = (f.ToList()?.Sum(e => e.CommentR1) ?? 0),
CommentR2 = (f.ToList()?.Sum(e => e.CommentR2) ?? 0),
CommentR3 = (f.ToList()?.Sum(e => e.CommentR3) ?? 0),
CommentR4 = (f.ToList()?.Sum(e => e.CommentR4) ?? 0),
CommentTimes = (f.ToList()?.Sum(e => e.CommentTimes) ?? 0),
FavourableCount = (f.ToList()?.Sum(e => e.FavourableCount) ?? 0),
DefaultFavourableCount = (f.ToList()?.Sum(e => e.DefaultFavourableCount) ?? 0),
ApplyCommentRate1 = (f.ToList()?.Sum(e => e.ApplyCommentRate1) ?? 0),
ApplyCommentRate2 = (f.ToList()?.Sum(e => e.ApplyCommentRate2) ?? 0),
ApplyCommentRate3 = (f.ToList()?.Sum(e => e.ApplyCommentRate3) ?? 0),
ApplyCommentRate4 = (f.ToList()?.Sum(e => e.ApplyCommentRate4) ?? 0),
ApplyCommentTimes = (f.ToList()?.Sum(e => e.ApplyCommentTimes) ?? 0)
};
//重新计算评分
if (_ShopStatisticsModel.CommentTimes > 0)
{
_ShopStatisticsModel.Score = (_ShopStatisticsModel.CommentR1 + 5 * _ShopStatisticsModel.DefaultFavourableCount) / (_ShopStatisticsModel.CommentTimes + _ShopStatisticsModel.DefaultFavourableCount);
}
shopsModel_ALL.Add(_ShopStatisticsModel);
});
shopsModel.AddRange(shopsModel_ALL);
int index = 0;
if (shopsModel != null && shopsModel.Any())
{
Parallel.ForEach(shopsModel.GroupBy(g => g.ShopID), new ParallelOptions()
{
MaxDegreeOfParallelism = 5
}, e =>
//shopsModel.GroupBy(g => g.ShopID).ForEach(e =>
{
Stopwatch tempWatch = Stopwatch.StartNew();
try
{
var dbShopsModel = DalShopStatistics1.GetShopStatisticByShopIds(e.Key);//数据库ShopStatistic表里的数据
e.ToList()?.ForEach(f =>
{
var temp = dbShopsModel.Where(w => w.ShopID == f.ShopID && w.Type == f.Type).FirstOrDefault();
if (temp != null)//更新
{
//字段 备份
temp.CommentTimesB = f.CommentTimes;
temp.CommentR1B = f.CommentR1;
temp.CommentR2B = f.CommentR2;
temp.CommentR3B = f.CommentR3;
temp.CommentR4B = f.CommentR4;
//重新赋值
temp.CommentTimes = f.CommentTimes + f.DefaultFavourableCount;
temp.CommentR1 = f.CommentR1 + f.DefaultFavourableCount * 5;
temp.CommentR2 = f.CommentR2 + f.DefaultFavourableCount * 5;
temp.CommentR3 = f.CommentR3 + f.DefaultFavourableCount * 5;
temp.CommentR4 = f.CommentR4 + f.DefaultFavourableCount * 5;
temp.FavourableCount = f.FavourableCount;
temp.DefaultFavourableCount = f.DefaultFavourableCount;
temp.Score = f.CommentTimesB == 0 ? 0 : f.CommentR1B / f.CommentTimesB;
//temp.Score = f.CommentTimes > 0 ? (f. * p.CommentTimes + 5 * p.DefaultFavourableCount) / (p.CommentTimes + p.DefaultFavourableCount) : 0;
temp.InstallQuantity = f.InstallQuantity;
decimal times = f.ApplyCommentTimes + Convert.ToDecimal(f.DefaultFavourableCount) * 2 / 5;
times = times == 0 ? 1 : times;
temp.ApplyCommentRate2 = (f.ApplyCommentRate2 + Convert.ToDecimal(f.DefaultFavourableCount) * 5 * 2 / 5) / times;
temp.ApplyCommentRate3 = (f.ApplyCommentRate3 + Convert.ToDecimal(f.DefaultFavourableCount) * 5 * 2 / 5) / times;
temp.ApplyCommentRate4 = (f.ApplyCommentRate4 + Convert.ToDecimal(f.DefaultFavourableCount) * 5 * 2 / 5) / times;
temp.ApplyCommentRate1 = temp.ApplyCommentRate2 * 4 / 10 + temp.ApplyCommentRate3 * 3 / 10 + temp.ApplyCommentRate2 * 3 / 10;
temp.ApplyCommentTimes = f.ApplyCommentTimes + Convert.ToDecimal(f.DefaultFavourableCount) * 2 / 5;
if (!DalShopStatistics1.UpdateShopStatistic(temp))
{
UpdateShopStatisticsLogger.Warn($"门店评分统计更新失败：ShopId={f.ShopID};Type={f.Type}");
}
}
else//新增
{
//字段 备份
f.CommentTimesB = f.CommentTimes;
f.CommentR1B = f.CommentR1;
f.CommentR2B = f.CommentR2;
f.CommentR3B = f.CommentR3;
f.CommentR4B = f.CommentR4;
//重新赋值
f.CommentTimes = f.CommentTimesB + f.DefaultFavourableCount;
f.CommentR1 = f.CommentR1 + f.DefaultFavourableCount * 5;
f.CommentR2 = f.CommentR2 + f.DefaultFavourableCount * 5;
f.CommentR3 = f.CommentR3 + f.DefaultFavourableCount * 5;
f.CommentR4 = f.CommentR4 + f.DefaultFavourableCount * 5;
f.FavourableCount = f.FavourableCount;
f.DefaultFavourableCount = f.DefaultFavourableCount;
f.Score = f.CommentTimesB == 0 ? 0 : f.CommentR1B / f.CommentTimesB;
f.InstallQuantity = f.InstallQuantity;
decimal times = f.ApplyCommentTimes + Convert.ToDecimal(f.DefaultFavourableCount) * 2 / 5;
times = times == 0 ? 1 : times;
f.ApplyCommentRate2 = (f.ApplyCommentRate2 + Convert.ToDecimal(f.DefaultFavourableCount) * 5 * 2 / 5) / times;
f.ApplyCommentRate3 = (f.ApplyCommentRate3 + Convert.ToDecimal(f.DefaultFavourableCount) * 5 * 2 / 5) / times;
f.ApplyCommentRate4 = (f.ApplyCommentRate4 + Convert.ToDecimal(f.DefaultFavourableCount) * 5 * 2 / 5) / times;
f.ApplyCommentRate1 = f.ApplyCommentRate2 * 4 / 10 + f.ApplyCommentRate3 * 3 / 10 + f.ApplyCommentRate2 * 3 / 10;
f.ApplyCommentTimes = f.ApplyCommentTimes + Convert.ToDecimal(f.DefaultFavourableCount) * 2 / 5;
if (!DalShopStatistics1.InsertShopStatistic(f))
{
UpdateShopStatisticsLogger.Warn($"门店评分统计插入失败：Data={f}");
}
}
});
index++;
UpdateShopStatisticsLogger.Info($"门店评分统计更新:第{index}个门店ShopId={e.Key}数据更新完成，时间={tempWatch.ElapsedMilliseconds}");
tempWatch.Stop();
}
catch (Exception ex)
{
UpdateShopStatisticsLogger.Error($"门店评分统计更新异常:ShopId={e.Key};Exception={ex}");
}
});
#region 重新统计评论总分
//try
//{
// var setResult = DalShopStatistics.SetTotalPoints();
// UpdateShopStatisticsLogger.Info($"门店评分统计更新:设置{setResult}个门店的总评分");
//}
//catch (Exception ex)
//{
// UpdateShopStatisticsLogger.Error("门店评分总分统计异常", ex);
//}
#endregion
}
watch.Stop();
}
