def get_vcbrand_for_name(brand_id):
result = DB.get(sqls.search_one_brand, {"brand_id": brand_id})
if not result:
raise Exception("监测id不存在")
result.update(brand=json.loads(result.get("brand")))
result.update(competitor=json.loads(result.get("competitor")))
apps_apis.brand_to_brand(result)
return result
def get_data_voice_histogram(bracket, range_time, category, competitors, platform='net'):
"""
获取声量柱形图和sov环形图的总数和每个平台的声量
"""
bracket_platform = join_sql_bracket([platform, ])
if platform == 'net':
sql_his = sqls.brand_voice_histogram % (bracket, bracket, range_time,)
elif platform == '全部':
sql_his = sqls.bbv_brand_voice_histogram % (bracket, bracket, range_time,)
elif platform in ["微博", "微信"]:
sql_his = sqls.brand_ww_voice_histogram % (bracket, bracket, bracket_platform, range_time,)
else:
sql_his = sqls.bbv_platform_brand_voice_histogram % (bracket, bracket, bracket_platform, range_time,)
# 有竞争产品的时候(环形图的总数是竞争产品的全部声量 全品类的时候环形图的总数是全部的声量其余的剩下的用其他来表示)
if competitors:
if platform == 'net':
sql_round = sqls.round_sum_sov % (bracket, range_time) # 声量总和计算sov环形图
elif platform == '全部':
sql_round = sqls.bbv_round_sum_sov % (bracket, range_time) # 柱形图数据和声量总和计算sov环形图合并在一起
elif platform in ["微博", "微信"]:
sql_round = sqls.round_ww_sum_sov % (bracket, bracket_platform, range_time)
else:
sql_round = sqls.bbv_platform_round_sum_sov % (bracket, bracket_platform, range_time)
else:
if platform == 'net':
sql_round = sqls.round_all_brand_sum_sov % (range_time) # 声量总和计算sov环形图
elif platform == '全部':
sql_round = sqls.bbv_all_brand_round_sum_sov % (range_time)
elif platform in ["微博", "微信"]:
sql_round = sqls.ww_round_sum_sov % (bracket_platform, range_time)
else:
sql_round = sqls.bbv_platform_all_brand_round_sum_sov % (bracket_platform, range_time)
data_voice_histogram = DB.search(sql_his, {"category_name": category.name})
data_voice_round = DB.get(sql_round, {"category_name": category.name})
get_round_sov(data_voice_histogram, data_voice_round, competitors)
return data_voice_histogram, data_voice_round
def get_card_voice_sov(vcBrand, category, date_range, type="net"):
"""
仅按照时间日期获取相应的本品声量，竞品声量或者全品声量
:param vcBrand:
:param category:
:param date_range:
:return:
"""
brand_name = vcBrand.get("brand")
date1 = datetime.strptime(date_range[0], "%Y-%m-%d")
date2 = datetime.strptime(date_range[1], "%Y-%m-%d")
range_time = " and date between '{}' and '{}' ".format(date_range[0], date_range[1])
time_slot = abs((date2-date1).days)
date_previous1 = (date1 - relativedelta(days=1)).strftime("%Y-%m-%d")
date_previous2 = (date1 - relativedelta(days=time_slot+1)).strftime("%Y-%m-%d")
range_time_previous = " and date between '{}' and '{}' ".format(date_previous2, date_previous1)
competitors = vcBrand.get("competitor")
bracket_platform = join_sql_bracket([type, ])
list_compete = copy.deepcopy(competitors)
if competitors: # 有竞品
list_compete.append(brand_name) # 所有的竞品加上本品的总数
bracket = join_sql_bracket(list_compete)
if type == "net":
sql = sqls.monitor_data_compete_voice % (bracket, range_time) # 获取当前
sql_previous = sqls.monitor_data_compete_voice% (bracket, range_time_previous) # 获取上个阶段
elif type == "全部":
# 只是针对深度bbv的全部
sql = sqls.monitor_data_bbv_all_compete_voice % (bracket, range_time) # 获取当前
sql_previous = sqls.monitor_data_bbv_all_compete_voice % (bracket, range_time_previous) # 获取上个阶段
elif type in ["微博", "微信"]: # 微博微信的平台声量是从sass取出来
sql = sqls.milk_dw_all_compete_voice % (bracket, bracket_platform, range_time) # 获取当前
sql_previous = sqls.milk_dw_all_compete_voice % (bracket, bracket_platform, range_time_previous) # 获取上个阶段
else:
# 各个平台的, 包括深度社煤和深度bbv的所有子集
sql = sqls.monitor_data_classify_compete_voice % (bracket, bracket_platform, range_time) # 获取当前
sql_previous = sqls.monitor_data_classify_compete_voice % (bracket, bracket_platform, range_time_previous) # 获取上个阶段
# voice = DB.get(sql, {"category_name": category.name}) # 获取竞品声量
# voice_previous = DB.get(sql_previous, {"category_name": category.name}) # 获取竞品声量
# compete_voice = int(voice.get("voice_all")) if voice.get("voice_all") else 0
# compete_voice_previous = int(voice_previous.get("voice_all")) if voice_previous.get("voice_all") else 0
else: # 全品
if type == 'net':
sql = sqls.all_monitor_voice % (range_time)
sql_previous = sqls.all_monitor_voice % (range_time_previous)
elif type == '全部':
sql = sqls.bbv_all_sum_voice % (range_time)
sql_previous = sqls.bbv_all_sum_voice % (range_time_previous)
elif type in ["微博", "微信"]:
sql = sqls.milk_platform_classify_voice % (bracket_platform, range_time) # 获取当前
sql_previous = sqls.milk_platform_classify_voice % (bracket_platform, range_time_previous) # 获取上个阶段
else:
sql = sqls.bbv_platform_classify_voice % (bracket_platform, range_time)
sql_previous = sqls.bbv_platform_classify_voice % (bracket_platform, range_time_previous)
voice = DB.get(sql, {"category_name": category.name}) # 获取全品类声量
voice_previous = DB.get(sql_previous, {"category_name": category.name}) # 获取全品类声量
compete_voice = int(voice.get("voice_all")) if voice.get("voice_all") else 0
compete_voice_previous = int(voice_previous.get("voice_all")) if voice_previous.get("voice_all") else 0
if type == 'net':
new_sql = sqls.monitor_data_analysis_voice % (range_time)
new_sql_previous = sqls.monitor_data_analysis_voice % (range_time_previous)
elif type == "全部":
new_sql = sqls.self_brand_bbv_all % (range_time)
new_sql_previous = sqls.self_brand_bbv_all % (range_time_previous)
elif type in ["微博", "微信"]:
new_sql = sqls.self_brand_milk_classify % (bracket_platform, range_time)
new_sql_previous = sqls.self_brand_milk_classify % (bracket_platform, range_time_previous)
else:
new_sql = sqls.self_brand_bbv_classify % (bracket_platform, range_time)
new_sql_previous = sqls.self_brand_bbv_classify % (bracket_platform, range_time_previous)
voice = DB.get(new_sql, {"brand_name": brand_name, "category_name": category.name}) # 本品的的声量
voice_previous = DB.get(new_sql_previous, {"brand_name": brand_name, "category_name": category.name}) # 本品的的声量
self_voice = int(voice.get("voice")) if voice.get("voice") else 0
self_voice_previous = int(voice_previous.get("voice")) if voice_previous.get("voice") else 0
return self_voice, competitors, compete_voice, self_voice_previous, compete_voice_previous