def get_content_from(bracket, range_time, competitors, category, brand_name, platform='net'):
# 获取内容分布雷达图 竞品为全品类的时候值显示当前品牌， 为主要竞品时是本品+竞品
str_brand = join_sql_bracket([brand_name, ])
bracket_platform = join_sql_bracket([platform,])
if competitors:
if platform == 'net': # 全网的数据和bbv全部的数据时一样的
sql_radar = sqls.content_radar%(bracket, range_time)
sql_radar_classify = sqls.content_radar_classify%(bracket, range_time)
elif platform == '全部':
sql_radar = sqls.content_radar_bbv_all % (bracket, range_time)
sql_radar_classify = sqls.content_radar_classify_bbv_all % (bracket, range_time)
else:
sql_radar = sqls.content_radar_other_platform % (bracket,bracket_platform, range_time)
sql_radar_classify = sqls.content_radar_classify_other_platform % (bracket,bracket_platform, range_time)
else:
if platform == 'net': # 全网的数据和bbv全部的数据时一样的
sql_radar = sqls.content_radar%(str_brand, range_time)
sql_radar_classify = sqls.content_radar_classify%(str_brand, range_time)
elif platform == '全部':
sql_radar = sqls.content_radar_bbv_all % (str_brand, range_time)
sql_radar_classify = sqls.content_radar_classify_bbv_all % (str_brand, range_time)
else:
sql_radar = sqls.content_radar_other_platform % (str_brand, bracket_platform,range_time)
sql_radar_classify = sqls.content_radar_classify_other_platform % (str_brand, bracket_platform, range_time)
data_radar = DB.search(sql_radar, {"category_name": category.name})
data_radar_classify = DB.search(sql_radar_classify, {"category_name": category.name})
data = dispose_content_radar(data_radar, data_radar_classify)
return data
def get_bracket_datarange(vcBrand, category, date_range, platform='net'):
brand_name = vcBrand.get("brand")
range_time = " and a.date between '{}' and '{}' ".format(date_range[0], date_range[1])
competitors = vcBrand.get("competitor")
list_compete = copy.deepcopy(competitors)
platform_bracket = join_sql_bracket([platform, ])
if competitors: # 有竞品
if brand_name not in competitors:
list_compete.append(brand_name)
bracket = join_sql_bracket(list_compete)
else:
# 获取top5的声量
if platform == 'net':
sql = sqls.net_all_top5 % (range_time)
elif platform == '全部':
sql = sqls.bbv_all_top5%(range_time)
elif platform in ["微博", "微信"]:
sql = sqls.ww_net_all_top5%(platform_bracket, range_time)
else:
sql = sqls.bbv_classify_all_top5%(platform_bracket, range_time)
sql_brand = DB.search(sql, {"brand_name": brand_name, "category_name": category.name})
for brand in sql_brand:
list_compete.append(brand.get("brand"))
if brand_name not in list_compete:
list_compete.append(brand_name)
bracket = join_sql_bracket(list_compete)
return bracket, competitors, range_time
def randar_patter_map(vcBrand, platform, date_range, category):
"""
雷达组合图 获取条形加玉珏图 页面展示咖啡的微博加小红书
品牌 购买行为 感官 取出top5 没有玉珏图
使用场景 产品属性 取出top3 加上玉珏图
top5和top3分别计算返回
:return:
"""
list_data = list()
list_top3 = []
list_level1 = ['使用场景', '产品属性']
if platform in ["微博", '小红书']:
range_time = " and date between '{}' and '{}' ".format(date_range[0], date_range[1])
brand_name = vcBrand.get("brand")
sql_second_top5 = sqls.randar_patter_map%(range_time)
data_second_top5 = DB.search(sql_second_top5, {"brand_name": brand_name, "category_name": category.name, "platform": platform})
level2 = []
data_second_top5.sort(key=itemgetter('level1', "count"), reverse=True)
for level1, items in groupby(data_second_top5, key=itemgetter('level1')):
if level1 in list_level1:
data = list(items)[:3]
for i in data:
i.update(name=i.get('level2'))
level2.append(i.get('level2'))
list_top3.append({'name': level1, 'children': data})
else:
list_data.append({"name": level1, "children": [{"name": i.get('level2'), "value": i.get('count')} for i in items]})
if level2: # # 获取使用场景 产品属性的3级认知的全部的言论数计算玉珏图
level2_bracket = join_sql_bracket(level2)
level1_bracket = join_sql_bracket(list_level1)
sql = sqls.region_three_for_randar%(level1_bracket, level2_bracket, range_time)
data_three_region = DB.search(sql, {"brand_name": brand_name, "category_name": category.name, "platform": platform})
for i in data_three_region:
i.update(name=i.get('level3'))
data_three_region.sort(key=itemgetter('level1', "level2"))
for level2, items in groupby(data_three_region, key=itemgetter('level2')):
data_level3 = list(items)
for val in list_top3:
for list_level2 in val.get('children'):
if level2 == list_level2.get('level2'):
if data_level3 and data_level3[0].get('level1') == val.get('name'):
list_level2.update(children=data_level3)
return list_data, list_top3
def dispose_standard_area(areas):
# 更新标准的地域名称
standard_areas = DB.search(sqls.get_standard_area)
for area in areas:
area_old = area.get('area')
for standard_area in standard_areas:
if standard_area.get('name') in area_old:
area.update(area=standard_area.get('name'))
break
def get_area_voice_from(range_time, category, brand_name, platform='net'):
# 获取平台的地域声量
bracket_platform = join_sql_bracket([platform, ])
if platform == 'net':
sql_area_classify = sqls.area_voice_classify % (range_time) # 各个地域的分类声量
elif platform == '全部':
sql_area_classify = sqls.bbv_area_voice_classify%(range_time) # 各个地域的分类声量
else:
sql_area_classify = sqls.bbv_platform_area_voice_classify%(bracket_platform, range_time)
data_voice_area_classify = DB.search(sql_area_classify, {"category_name": category.name, "brand_name": brand_name})
dispose_standard_area(data_voice_area_classify)
return data_voice_area_classify
def get_user_menus_apis(request):
logger.debug("auth_apis.get_user_menu_apis()")
sql = """
SELECT m.code menu_code, m.name menu_name, a.url api_url
FROM sm_user u, sm_role r, sm_role_menus rm, sm_menu m, sm_menu_apis ma, sm_api a
WHERE 1=1
AND r.id=u.role_id AND rm.smrole_id=r.id AND m.id=rm.smmenu_id AND ma.smmenu_id=m.id AND a.id=ma.smapi_id
AND u.id={user_id}
"""
dargs = dict(user_id=request.user.id)
permission_menu_api_list = DB.search(sql, dargs, use_catched=False)
return permission_menu_api_list
def get_platform_voice_from(bracket, range_time, category, platform='net'):
# 获取平台声量来源 只有bbv全部和全网数据有平台来源
if platform == 'net':
sql_platform_sum = sqls.platform_voice_sum % (bracket, bracket, range_time) # 平台的声量sum
if category.name == "奶粉": # 奶粉和咖啡需要展示的平台名称不一样
sql_platform_classify = sqls.platfom_classify_count_milk % (bracket, bracket, range_time) # 各个平台的分类声量
else:
sql_platform_classify = sqls.platfom_classify_count_coffee % (bracket, bracket, range_time)
elif platform == '全部': # 子类下面没有平台的声量数据
sql_platform_sum = sqls.bbv_platform_voice_sum_all%(bracket, bracket, range_time) # 平台的声量sum
sql_platform_classify = sqls.bbv_platform_voice_all_classify%(bracket, bracket, range_time) # 各个平台的分类声量
else:
return list()
data_voice_platform_sum = DB.search(sql_platform_sum, {"category_name": category.name})
data_voice_platform_classify = DB.search(sql_platform_classify, {"category_name": category.name})
vioce_platform = dispose_platform_voice(data_voice_platform_sum, data_voice_platform_classify)
list_data = list()
vioce_platform.sort(key=itemgetter("platform", "brand"))
for brand, items in groupby(vioce_platform, key=itemgetter('platform')):
list_data.append(list(items))
return list_data
def get_keywords_from(range_time, category, brand_name, platform='net'):
# 获取关键词
bracket_platform = join_sql_bracket([platform, ])
special_character = "keywords not in ('{0}')".format("', '".join(sqls.keywords_cloud_exclude))
sxclude_condition = special_character + "and ((keywords regexp '[^\x00-\xff]') or (keywords regexp '[A-Za-z0-9_]+' and length(keywords) > 2))"
if platform == 'net':
net_keywords = sqls.net_keywords % (range_time, sxclude_condition) # 获取全网关键词
elif platform == '全部':
net_keywords = sqls.bbv_all_keywords%(range_time, sxclude_condition) # 获取全网关键词
else:
net_keywords = sqls.bbv_platform_keywords_classify % (bracket_platform, range_time, sxclude_condition) # 获取全网关键词
keywords = DB.search(net_keywords, {"category_name": category.name, "brand_name": brand_name})
return keywords
def competitor_list(param, user):
'''
设置, 竞品列表
:param param:
:param user:
:return:
'''
params = dict(queue_filter=param.get("queue_filter", ''),
corporation=user.corporation)
data = DB.search(sqls.competitor_list, params)
map(
lambda x: x.update(
competitors=json.loads(x["competitors"]),
brand=json.loads(x["brand"]),
), data)
return data
def get_top_20_user_posts(vcBrand, platform, date_range, category):
"""
获取用户发帖top20 只有是深度社媒的微博和小红书平台
:return:
"""
range_time = " a.date between '{}' and '{}' ".format(date_range[0], date_range[1])
brand_name = vcBrand.get("brand")
if platform in ["微博", "小红书"]:
if platform == "微博":
sql = sqls.dsm_weibo_official_top20 %(range_time, range_time)
# elif platform == "微信": 用户发帖没有微信
# sql = sqls.dsm_weixin_official_top20 % range_time
else:
sql = sqls.dsm_redbook_official_top20 % (range_time, range_time)
users_posts = DB.search(sql, {'type_from': 2, "platform": platform, "brand_name": brand_name, "category_name": category.name})
else:
users_posts = []
show_comment(users_posts)
return users_posts
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_top_20_offical_posts(vcBrand, platform, date_range, category):
"""
获取官方发帖top20 只有是深度社媒的微博和小红书和微信平台
按照互动量排序 微博：原创微博的转发+评论+点赞
微信： 点赞数
小红书： 原创帖子的转发+品论+点赞+收藏
知乎：评论+点赞
:return:
"""
range_time = " a.date between '{}' and '{}' ".format(date_range[0], date_range[1])
brand_name = vcBrand.get("brand")
if platform in ["微博", "微信", "小红书"]:
if platform == "微博":
sql = sqls.dsm_weibo_official_top20 %(range_time, range_time)
elif platform == "微信":
sql = sqls.dsm_weixin_official_top20%(range_time, range_time)
else:
sql = sqls.dsm_redbook_official_top20%(range_time, range_time)
offcial_posts = DB.search(sql, {'type_from': 1, "platform": platform, "brand_name": brand_name, "category_name": category.name})
else:
offcial_posts = []
show_comment(offcial_posts)
return offcial_posts
def get_all_monitor_card_data(request, brand_name, category_id):
# 按照品类查询所有的品牌id, 计算声量信息
vcBrands = search_monitor_brand(request.user, brand_name, category_id)
category = DimCategory.objects.get(id=category_id)
industry = DimIndustry.objects.get(id=category.industry_id)
# 计算sov 获取所有竞品或者全品
for vcBrand in vcBrands:
time_slot = vcBrand.get("time_slot")
# 时间周期计算
date1 = datetime.now().strftime("%Y-%m-%d")
date2 = (datetime.now() - relativedelta(days=time_slot)).strftime("%Y-%m-%d")
date_range = [date2, date1]
vcBrand.update(date_range=date_range)
range_time = " and date between '{}' and '{}' ".format(date_range[0], date_range[1])
self_voice, competitors, compete_voice, self_voice_previous, compete_voice_previous = get_card_voice_sov(vcBrand, category, date_range)
brand_name = vcBrand.get("brand")
data_sql = sqls.all_data_card_voice_assert %(range_time, range_time)
data_assert = DB.search(data_sql, {"brand_name": brand_name, "category_name": category.name}) # 所有当前品牌的日期数据
sov = get_all_sov(self_voice, compete_voice)
append_vc_brand(vcBrand, data_assert, self_voice, category, industry, compete_voice, sov)
return vcBrands
def get_data(bracket, competitors, range_time, category, platform='net'):
"""
# 全品类的时候声量top5+本品 有竞品的时候本品+竞品
assert_data = {
"name1": {"day": [], "month": [], "week": []},
"name2": {"day": [], "month": [], "week": []},
"name3": {"day": [], "month": [], "week": []},
}
:param brand_id:
:return:
"""
dict_data = defaultdict(list)
bracket_platform = join_sql_bracket([platform, ])
range_time_new = range_time.replace('and', '', 1)
if platform == 'net':
sql_day = sqls.compete_day_month_week_voice % (range_time_new, bracket, bracket, range_time_new, "base2.date", "base2.date", "base2.date")
sql_month = sqls.compete_day_month_week_voice % (range_time_new, bracket, bracket, range_time_new, "base2.month", "base2.month", "base2.month")
sql_week = sqls.compete_day_month_week_voice % (range_time_new, bracket, bracket, range_time_new, "base2.week", "base2.week", "base2.week")
elif platform == '全部': # bbv全部
sql_day = sqls.bbv_compete_day_month_week_voice % (range_time_new, bracket, bracket, range_time_new, "base2.date", "base2.date", "base2.date")
sql_month = sqls.bbv_compete_day_month_week_voice % (range_time_new, bracket, bracket, range_time_new, "base2.month", "base2.month", "base2.month")
sql_week = sqls.bbv_compete_day_month_week_voice % (range_time_new, bracket, bracket, range_time_new, "base2.week", "base2.week", "base2.week")
elif platform in ["微博", "微信"]:
sql_day = sqls.ww_compete_day_month_week_voice % (range_time_new, bracket, bracket_platform, bracket, range_time_new, "base2.date", "base2.date", "base2.date")
sql_month = sqls.ww_compete_day_month_week_voice % (range_time_new, bracket,bracket_platform, bracket, range_time_new, "base2.month", "base2.month", "base2.month")
sql_week = sqls.ww_compete_day_month_week_voice % (range_time_new, bracket,bracket_platform, bracket, range_time_new, "base2.week", "base2.week", "base2.week")
else: # 其他的各个平台
sql_day = sqls.bbv_platform_compete_day_month_week_voice % (range_time_new, bracket, bracket_platform, bracket, range_time_new, "base2.date", "base2.date", "base2.date")
sql_month = sqls.bbv_platform_compete_day_month_week_voice % (range_time_new, bracket, bracket_platform, bracket, range_time_new, "base2.month", "base2.month", "base2.month")
sql_week = sqls.bbv_platform_compete_day_month_week_voice % (range_time_new, bracket, bracket_platform, bracket, range_time_new, "base2.week", "base2.week", "base2.week")
if competitors:
if platform == 'net':
sov_month = sqls.area_of_tend_sov % (range_time_new, bracket, "a.month",range_time_new, "a.month", "a.month")
sov_day = sqls.area_of_tend_sov % (range_time_new, bracket, 'a.date', range_time_new, "a.date", "a.date")
sov_week = sqls.area_of_tend_sov%(range_time_new, bracket, "a.week", range_time_new, "a.week", "a.week")
elif platform == '全部':
sov_month = sqls.bbv_area_of_tend_sov % (range_time_new, bracket, "a.month", range_time_new, "a.month", "a.month")
sov_day = sqls.bbv_area_of_tend_sov % (range_time_new, bracket, "a.date", range_time_new, "a.date", "a.date")
sov_week = sqls.bbv_area_of_tend_sov % (range_time_new, bracket, "a.week", range_time_new, "a.week", "week")
elif platform in ["微博", "微信"]:
sov_month = sqls.ww_area_of_tend_sov % (range_time_new, bracket, bracket_platform, "a.month", range_time_new, "a.month", "a.month")
sov_day = sqls.ww_area_of_tend_sov % (range_time_new, bracket, bracket_platform, "a.date",range_time_new, "a.date", "a.date")
sov_week = sqls.ww_area_of_tend_sov % (range_time_new, bracket, bracket_platform, "a.week", range_time_new, "a.week", "a.week")
else:
sov_month = sqls.bbv_platform_area_of_tend_sov % (range_time_new, bracket, bracket_platform, "a.month", range_time_new, "a.month", "a.month")
sov_day = sqls.bbv_platform_area_of_tend_sov % (range_time_new, bracket, bracket_platform, "a.date", range_time_new, "a.date", "a.date")
sov_week = sqls.bbv_platform_area_of_tend_sov % (range_time_new, bracket, bracket_platform, "a.week", range_time_new, "a.week", "week")
else:
if platform == 'net':
sov_month = sqls.area_of_all_brand_tend_sov % (range_time_new, "a.month", range_time_new, "a.month", "a.month")
sov_day = sqls.area_of_all_brand_tend_sov % (range_time_new, "a.date", range_time_new, "a.date", "a.date")
sov_week = sqls.area_of_all_brand_tend_sov % (range_time_new, "a.week", range_time_new, "a.week", "a.week")
elif platform == '全部':
sov_month = sqls.bbv_area_all_brand_of_tend_sov % (range_time_new, "a.month", range_time_new, "a.month", "a.month")
sov_day = sqls.bbv_area_all_brand_of_tend_sov % (range_time_new, "a.date", range_time_new, "a.date", "a.date")
sov_week = sqls.bbv_area_all_brand_of_tend_sov % (range_time_new, "a.week", range_time_new, "a.week", "a.week")
elif platform in ["微博", "微信"]:
sov_month = sqls.ww_area_all_brand_of_tend_sov % (range_time_new, bracket_platform, "a.month", range_time_new, "a.month", "a.month")
sov_day = sqls.ww_area_all_brand_of_tend_sov % (range_time_new, bracket_platform, "a.date", range_time_new, "a.date", "a.date")
sov_week = sqls.ww_area_all_brand_of_tend_sov % (range_time_new, bracket_platform, "a.week", range_time_new, "a.week", "a.week")
else:
sov_month = sqls.bbv_platform_area_all_brand_of_tend_sov % (range_time_new, bracket_platform, "a.month", range_time_new, "a.month", "a.month")
sov_day = sqls.bbv_platform_area_all_brand_of_tend_sov % (range_time_new, bracket_platform, "a.date", range_time_new, "a.date", "a.date")
sov_week = sqls.bbv_platform_area_all_brand_of_tend_sov % (range_time_new, bracket_platform, "a.week", range_time_new, "a.week", "a.week")
day_assert = DB.search(sql_day, {"category_name": category.name})
month_assert = DB.search(sql_month, {"category_name": category.name})
week_assert = DB.search(sql_week, {"category_name": category.name})
# 日月周的总数 用于计算sov
day_sov_assert = DB.search(sov_day, {"category_name": category.name})
month_sov_assert = DB.search(sov_month, {"category_name": category.name})
week_sov_assert = DB.search(sov_week, {"category_name": category.name})
assemble_data_new(day_assert, dict_data, 'day')
assemble_data_new(month_assert, dict_data, "month")
assemble_data_new(week_assert, dict_data, "week")
get_classify_sov_new(dict_data, day_sov_assert, 'day', competitors)
get_classify_sov_new(dict_data, month_sov_assert, 'month', competitors)
get_classify_sov_new(dict_data, week_sov_assert, 'week', competitors)
return dict_data
def get_report_list(user, report_status, monitor_end_time, monitor_cycle,
key_word):
# 刷选报告
sql_format = [
"status>=-1",
]
db = DB()
sql = "SELECT report.* FROM report join sm_user on report.user_id=sm_user.id WHERE `delete`=false and {} ORDER BY create_time DESC"
# sql = "SELECT report.* FROM report where `delete`=false and status>=0 ORDER BY status DESC, create_time DESC"
# if report_status != "100" or monitor_end_time != "36500" or monitor_cycle != "36500" or key_word or(not(user.is_admin and user.user_type == 1)):
# sql = "SELECT report.* FROM report WHERE `delete`=false and {} ORDER BY status DESC, create_time DESC"
if report_status != "100":
if int(report_status) >= 2:
# sql_format.append("{}>={}".format("status", report_status))
sql_format.append("status in (-1, 2, 3, 4, 5, 6)")
else:
sql_format.append("{}={}".format("status", report_status))
if monitor_end_time != "36500":
if monitor_end_time == "-180":
sql_format.append("datediff(\'{}\', {})>{}".format(
datetime.datetime.now(), "monitor_end_date", 180))
else:
sql_format.append("datediff(\'{}\', {})<={}".format(
datetime.datetime.now(), "monitor_end_date", monitor_end_time))
if monitor_cycle != "36500":
if monitor_cycle == "-90":
sql_format.append("datediff({}, {})>{}".format(
"monitor_end_date", "monitor_start_date", 90))
else:
sql_format.append("datediff({}, {})<={}".format(
"monitor_end_date", "monitor_start_date", monitor_cycle))
if key_word:
sql_title = "LOWER(report.name) like '%{}%'".format(key_word.lower())
sql_name = "LOWER(sm_user.username) like '%{}%'".format(
key_word.lower())
report_title = "LOWER(report.title) like '%{}%'".format(
key_word.lower())
report_brand = "LOWER(report.brand_id) like '%{}%'".format(
key_word.lower())
sql_format.append("(" + sql_title + " OR " + sql_name + " OR " +
report_title + " OR " + report_brand + ")")
# 判断是管理员内部用户
# if user.is_admin and user.user_type == 1:
# pass
# elif user.is_admin:
# # sql = sql_join
# corporation = user.corporation
# sql_user = "******".format("sm_user.corporation", corporation)
# sql_format.append(sql_user)
if not user.is_admin:
# user_id = user.id
# sql_user = "******".format("user_id", user_id)
corporation = user.corporation
sql_user = "******".format("sm_user.corporation", corporation)
sql_format.append(sql_user)
if sql_format:
sql_format = " and ".join(sql_format)
sql = sql.format(sql_format)
res = db.search(sql)
report_create_list = list() # 状态 >2 或者 等于 -1
success_list = list() # 状态0
commit_list = list() # 状态1
for report in res:
if report.get("status") >= 2:
report_create_list.append(report)
elif report.get('status') == -1:
report_create_list.append(report)
elif report.get("status") == 1:
commit_list.append(report)
else:
success_list.append(report)
report_create_list.extend(commit_list)
report_create_list.extend(success_list)
return formatted_report(report_create_list) if res else []