def last_1500_email(args):
date = args['date']
days = args['days'] if 'days' in args and args['days'] else 1
count = args['count'] if 'count' in args and args['count'] else 50
timenow = int(time.time())
stauts = 0
index_id = 0
job_id = args['job_id']
try:
start_day = get_display_day(start_day=date, calc=1 - days)
sql_find_user = """select tvcbook.distinct_id,max(tvcbook.created_at) as max_c from tvcbook left join tvcbook_user on tvcbook.distinct_id=tvcbook_user.distinct_id where tvcbook.date>=DATE_SUB('{start_day}',INTERVAL 1 DAY) and date<=DATE_SUB('{end_day}',INTERVAL 1 DAY) and event = '$pageview' and tvcbook_user.original_id is not null and tvcbook_user.original_id != '' GROUP BY tvcbook.distinct_id order by max_c desc limit {count}""".format(
start_day=start_day, end_day=date, count=count)
users_result = do_tidb_select(sql=sql_find_user)
# print(users_count)
total_insert = 0
all_data = {'data_list': []}
for user in users_result[0]:
sql_email = """select email,nickname from yii2_user left join yii2_user_profile on yii2_user.id = yii2_user_profile.user_id where uid = '{uid}';""".format(
uid=user[0])
sql_email_result, sql_email_count = do_tidb_select(sql=sql_email)
if sql_email_count > 0 and sql_email_result[0][
0] is not None and sql_email_result[0][
0] != '' and total_insert < count:
all_data['data_list'].append({
'key': user[0],
'enable': 9,
'json': {
'email': sql_email_result[0][0],
'nickname': sql_email_result[0][1],
'last_active_time': get_time_str(inttime=user[1])
}
})
total_insert = total_insert + 1
# print(total_insert,user[0],sql_email_result[0][0],sql_email_result[0][1],get_time_str(inttime=user[1]))
# print(all_data)
status, index_id = insert_usergroup(project='tvcbook',
group_id=4301,
data=all_data,
init_time=timenow,
list_desc=date,
jobs_id=job_id)
return status, index_id
except Exception:
error = traceback.format_exc()
write_to_log(filename='tvcbook',
defname='last_1500_email',
result=error)
return 20, index_id
def show_project_usergroup_plan(project):
sql = f"""SELECT
`{project}_usergroup_plan`.id as id,
`{project}_usergroup_plan`.group_title as group_title,
`{project}_usergroup_plan`.group_desc as group_desc,
`{project}_usergroup_plan`.latest_data_list_index as latest_data_list_index,
`{project}_usergroup_plan`.`repeatable` as `repeatable`,
`{project}_usergroup_plan`.priority as priority_id,
sc1.`desc` as priority,
`{project}_usergroup_plan`.enable_policy as enable_policy_id,
sc2.`desc` as enable_policy,
`{project}_usergroup_plan`.repeat_times as repeat_times,
from_unixtime(`{project}_usergroup_plan`.latest_data_time,"%Y-%m-%d %H:%i:%s") as latest_data_time,
`{project}_usergroup_plan`.latest_apply_temple_id as latest_apply_temple_id,
{project}_noti_temple.`name` as latest_apply_temple_name,
from_unixtime(`{project}_usergroup_plan`.latest_apply_temple_time,"%Y-%m-%d %H:%i:%s") as latest_apply_temple_time,
from_unixtime(`{project}_usergroup_plan`.created_at ,"%Y-%m-%d %H:%i:%s") as created_at,
from_unixtime(`{project}_usergroup_plan`.updated_at,"%Y-%m-%d %H:%i:%s") as updated_at
FROM
`{project}_usergroup_plan`
LEFT JOIN status_code AS sc1 ON {project}_usergroup_plan.priority = sc1.id
LEFT JOIN status_code AS sc2 ON {project}_usergroup_plan.enable_policy = sc2.id
LEFT JOIN {project}_noti_temple ON latest_apply_temple_id = {project}_noti_temple.id
ORDER BY
`{project}_usergroup_plan`.created_at desc"""
# timenow = int(time.time())
result = do_tidb_select(sql=sql)
return result
def show_project():
sql = """select project_name,FROM_UNIXTIME(created_at),FROM_UNIXTIME(expired_at),enable_scheduler from project_list order by project_name"""
result = do_tidb_select(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='show_project',result=str(result)+sql)
return '',0
return result[0],result[1]
def read_mobile_ad_list(page,
length,
filters='',
sort='created_at',
way='desc'):
sort = 'mobile_ad_list.' + sort
sql = """select mobile_ad_list.project,
mobile_ad_list.url,
mobile_ad_list.src,
mobile_ad_src.src_name,
mobile_ad_list.src_url,
mobile_ad_list.submitter,
mobile_ad_list.utm_source,
mobile_ad_list.utm_medium,
mobile_ad_list.utm_campaign,
mobile_ad_list.utm_content,
mobile_ad_list.utm_term,
from_unixtime(mobile_ad_list.created_at,'%Y-%m-%d'),
from_unixtime(mobile_ad_list.expired_at,'%Y-%m-%d')
from mobile_ad_list left join mobile_ad_src on mobile_ad_list.src=mobile_ad_src.src {filters} ORDER BY {sort} {way} Limit {start_pageline},{length}""".format(
start_pageline=(page - 1) * length,
length=length,
filters=filters,
sort=sort,
way=way)
print(sql)
result, count = do_tidb_select(sql)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',
defname='read_mobile_ad_list',
result=str(result) + sql)
return '', 0
return result, count
def count_mobile_ad_list(filters=''):
sql = """SELECT count(*) FROM `mobile_ad_list` {filters} """.format(filters=filters)
result = do_tidb_select(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='count_mobile_ad_list',result=str(result)+sql)
return '',0
return result[0]
def show_check(project,date,hour,order,start,limit,add_on_where):
sql = """select distinct_id,event,type,all_json,host,user_agent,ip,url,remark,from_unixtime(created_at) from {project} where date = '{date}' and hour = {hour} {add_on_where} order by created_at {order} limit {start},{limit}""".format(project=project,date=date,hour=hour,order=order,start=start,limit=limit,add_on_where=add_on_where)
result = do_tidb_select(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='show_check',result=str(result)+sql)
return '',0
return result[0],result[1]
def show_shortcut(page,length,filters='',sort='`shortcut`.created_at',way='desc'):
sql = """SELECT `shortcut`.project,`shortcut`.short_url,`shortcut`.long_url,from_unixtime(`shortcut`.expired_at),from_unixtime(`shortcut`.created_at),`shortcut`.src,`shortcut`.src_short_url,`shortcut`.submitter,`shortcut`.utm_source,`shortcut`.utm_medium,`shortcut`.utm_campaign,`shortcut`.utm_content,`shortcut`.utm_term,shortcut.created_at,shortcut.expired_at,count(shortcut_history.created_at) as visit_times,count(shortcut_read.created_at) as read_times FROM `shortcut` left join `shortcut_history` on `shortcut`.short_url = `shortcut_history`.`short_url` left join `shortcut_read` on `shortcut`.short_url = `shortcut_read`.`short_url` {filters} GROUP BY `shortcut`.project,`shortcut`.short_url,`shortcut`.long_url,from_unixtime(`shortcut`.expired_at),from_unixtime(`shortcut`.created_at),`shortcut`.src,`shortcut`.src_short_url,`shortcut`.submitter,`shortcut`.utm_source,`shortcut`.utm_medium,`shortcut`.utm_campaign,`shortcut`.utm_content,`shortcut`.utm_term,shortcut.created_at,shortcut.expired_at ORDER BY {sort} {way} Limit {start_pageline},{length}""".format(start_pageline=(page-1)*length if page>1 else 0,length=length,filters=filters,sort=sort,way=way)
result = do_tidb_select(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='show_shortcut',result=str(result)+sql)
return '',0
return result[0],result[1]
def check_mobile_ad_url(url):
timenow = int(time.time())
sql = """select project,url,from_unixtime(expired_at),from_unixtime(created_at),src,src_url,submitter,utm_source,utm_medium,utm_campaign,utm_content,utm_term from mobile_ad_list where url = '{url}' and expired_at > {timenow}""".format(
url=url, timenow=timenow)
result, count = do_tidb_select(sql)
if count > 0:
result_dict = []
for item in result:
result_dict.append({
'project': item[0],
'url': item[1],
'expired_at': item[2],
'created_at': item[3],
'src': item[4],
'src_url': item[5],
'submitter': item[6],
'utm_source': item[7],
'utm_medium': item[8],
'utm_campaign': item[9],
'utm_content': item[10],
'utm_term': item[11]
})
return result_dict, 'exist'
else:
return '', 'empty'
def show_project_usergroup_list(project,plan_id):
sql = f"""SELECT
{project}_usergroup_list.id as list_id,
{project}_usergroup_list.group_id as group_id,
{project}_usergroup_plan.group_title as group_title,
{project}_usergroup_list.group_list_index as group_list_index,
from_unixtime(`{project}_usergroup_list`.list_init_date,"%Y-%m-%d %H:%i:%s") as list_init_date,
{project}_usergroup_list.list_desc as list_desc,
{project}_usergroup_list.jobs_id as jobs_id,
{project}_usergroup_list.item_count as item_count,
{project}_usergroup_list.`status` as status_id,
sc1.`desc` as status_name,
from_unixtime(`{project}_usergroup_list`.complete_at,"%Y-%m-%d %H:%i:%s") as complete_at,
{project}_usergroup_list.apply_temple_times as apply_temple_times,
from_unixtime(`{project}_usergroup_list`.created_at ,"%Y-%m-%d %H:%i:%s") as created_at,
from_unixtime(`{project}_usergroup_list`.updated_at,"%Y-%m-%d %H:%i:%s") as updated_at
FROM
{project}_usergroup_list
LEFT JOIN {project}_usergroup_plan on {project}_usergroup_list.group_id={project}_usergroup_plan.id
left join status_code as sc1 on {project}_usergroup_list.`status`=sc1.id
WHERE
group_id = {plan_id}
ORDER BY group_list_index desc"""
# timenow = int(time.time())
result = do_tidb_select(sql=sql)
return result
def count_shortcut(filters=''):
sql = """SELECT count(*) FROM `shortcut` {filters} """.format(filters=filters)
result,count = do_tidb_select(sql)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',defname='show_shortcut',result=str(result)+sql)
return '',0
return result
def read_mobile_ad_src_list(add_on_where=''):
timenow = int(time.time())
sql = """select src,src_name,src_args,from_unixtime(created_at),from_unixtime(updated_at),if({timenow}-created_at<=604800,1,0),utm_source,utm_medium,utm_campaign,utm_content,utm_term from mobile_ad_src {add_on_where} order by src_name""".format(add_on_where=add_on_where,timenow=timenow)
result = do_tidb_select(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='read_mobile_ad_src_list',result=str(result)+sql)
return '',0
return result[0],result[1]
def update_properties():
sql = """select project_name from project_list"""
result = do_tidb_select(sql=sql)
for project in result[0]:
sql2="""ALTER TABLE `events`.`{project}_properties`
ADD COLUMN `access_control_threshold` int(10) NULL AFTER `total_count`;""".format(project=project[0])
do_tidb_exe(sql=sql2)
print('properities加字段完成')
def find_recall_history(project, device_id, created_at):
date = time.strftime("%Y-%m-%d", time.localtime(created_at))
sql = """select count(1) from {project} where date >= DATE_SUB('{date}',INTERVAL {day_diff} day) and date <= '{date}' and `event` = '$is_channel_callback_event' and distinct_id in ('{device_id}',md5('{device_id}'),replace('{device_id}','"',''),md5(replace('{device_id}','"','')))""".format(
project=project,
device_id=device_id,
date=date,
day_diff=admin.aso_dsp_callback_interval_days)
result, count = do_tidb_select(sql)
return result[0][0]
def update_ram(self):
sql = f'''select `desc` from status_code where id ={self.tag_id}'''
result = do_tidb_select(sql=sql)
if result[1] > 0:
self.tags[self.tag_id] = result[0][0][0]
print('增加', self.tag_id)
else:
self.tags[self.tag_id] = None
print('未找到', self.tag_id)
def select_usergroup_datacount_for_api(project,list_id,length,page,everywhere):
#整组选择要套模板的分群
sql = f"""SELECT count(*)
FROM
{project}_usergroup_data
WHERE
group_list_id = {list_id} {everywhere}"""
result = do_tidb_select(sql=sql)
return result
def find_recall_url(project, device_id, created_at):
date = time.strftime("%Y-%m-%d", time.localtime(created_at))
sql = """select JSON_EXTRACT(all_json,'$."properties"."callback_url"'),all_json,distinct_id from {project} where date >= DATE_SUB('{date}',INTERVAL {day_diff} day) and date <= '{date}' and `event` = '$AppChannelMatching' and distinct_id in ('{device_id}',md5('{device_id}'),replace('{device_id}','"',''),md5(replace('{device_id}','"',''))) order by created_at desc limit 1""".format(
project=project,
device_id=device_id,
date=date,
day_diff=admin.aso_dsp_callback_interval_days)
result, count = do_tidb_select(sql)
return result, count
def update_ram(self):
sql = f'''select all_user_profile from {self.project}_user where distinct_id='{self.tag_id}' and all_user_profile is not null and all_user_profile != ' ' and all_user_profile != '' ORDER BY created_at desc limit 1'''
result = do_tidb_select(sql=sql)
if result[1] > 0:
self.tags[self.tag_id] = json.loads(result[0][0][0])
print('增加', self.tag_id)
else:
self.tags[self.tag_id] = None
print('未找到', self.tag_id)
def show_noti_count_db(project,length,page,everywhere):
#查询推送分组列表
page = (page-1)*length if page and length and page > 1 else 0
sql=f"""SELECT count(*)
FROM
`{project}_noti`
where 1=1 {everywhere}
ORDER BY `{project}_noti`.created_at desc limit {page},{length}"""
result = do_tidb_select(sql=sql)
return result
def show_project():
sql = """select project_name,FROM_UNIXTIME(created_at),FROM_UNIXTIME(expired_at) from project_list order by project_name"""
result, count = do_tidb_select(sql)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',
defname='show_project',
result=str(result) + sql)
return '', 0
return result, count
def count_mobile_ad_list(filters=''):
sql = """SELECT count(*) FROM `mobile_ad_list` left join mobile_ad_src on mobile_ad_list.src=mobile_ad_src.src {filters} """.format(
filters=filters)
result, count = do_tidb_select(sql)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',
defname='count_mobile_ad_list',
result=str(result) + sql)
return '', 0
return result
def get_long_url_from_short(short_url):
timenow = int(time.time())
sql = """select HIGH_PRIORITY long_url,expired_at from shortcut where short_url ='{shorturl}' order by expired_at desc""".format(shorturl=short_url)
result,count = do_tidb_select(sql)
if count>0:
long_url = result[0][0]
expired_at = result[0][1]
if expired_at is None or expired_at > timenow :
return long_url,'success'
else:
return '','expired'
else:
return '','fail'
def select_usergroupdata_data(project,data_id):
#选择单个要套模板的人
sql = f"""SELECT
{project}_usergroup_list.group_id,
{project}_usergroup_list.id,
{project}_usergroup_data.id,
{project}_usergroup_data.data_index,
{project}_usergroup_data.data_key,
{project}_usergroup_data.data_json,
{project}_usergroup_data.`enable`
FROM
{project}_usergroup_data left join {project}_usergroup_list on {project}_usergroup_data.group_list_id={project}_usergroup_list.id
WHERE
{project}_usergroup_data.id = {data_id} and {project}_usergroup_data.enable != 10"""
result = do_tidb_select(sql=sql)
return result[0],result[1]
def check_utm(project,distinct_id):
sql = """select distinct_id,
lib,
utm_content,
utm_campaign,
utm_medium,
utm_term,
utm_source,
latest_utm_content,
latest_utm_campaign,
latest_utm_medium,
latest_utm_term,
latest_utm_source,
first_traffic_source_type,
latest_traffic_source_type
from {project}_device where distinct_id = '{distinct_id}'""".format(project=project,distinct_id=distinct_id)
result = do_tidb_select(sql)
return result[0],result[1]
def show_noti_db(project,length,page,everywhere):
#查询推送分组列表
page = (page-1)*length if page and length and page > 1 else 0
sql=f"""SELECT
{project}_noti.id AS noti_id,
{project}_noti.plan_id AS plan_id,
{project}_usergroup_plan.group_title AS plan_name,
{project}_noti.list_id AS list_id,
from_unixtime( `{project}_usergroup_list`.list_init_date, "%Y-%m-%d %H:%i:%s" ) AS list_init_date,
{project}_usergroup_list.list_desc AS list_desc,
{project}_usergroup_list.jobs_id AS jobs_id,
lsid.`desc` as list_status,
{project}_noti.data_id as data_id,
{project}_noti.temple_id as temple_id,
{project}_noti_temple.`name` as temple_name,
{project}_noti.noti_group_id,
{project}_noti.distinct_id,
{project}_noti.type as type_id,
tid.`desc` as type_name,
{project}_noti.content,
{project}_noti.priority as priority_id,
pid.`desc` as priority_name,
{project}_noti.`status` as status_id,
sid.`desc` as status_name,
{project}_noti.owner as `owner`,
{project}_noti.recall_result,
from_unixtime( `{project}_noti`.send_at, "%Y-%m-%d %H:%i:%s" ) AS send_at,
from_unixtime( `{project}_noti`.created_at, "%Y-%m-%d %H:%i:%s" ) AS created_at,
from_unixtime( `{project}_noti`.updated_at, "%Y-%m-%d %H:%i:%s" ) AS updated_at
FROM
`{project}_noti`
LEFT JOIN {project}_usergroup_plan ON {project}_noti.plan_id = {project}_usergroup_plan.id
LEFT JOIN {project}_usergroup_list ON {project}_noti.list_id = {project}_usergroup_list.id
LEFT JOIN {project}_noti_temple ON {project}_noti.temple_id = {project}_noti_temple.id
LEFT JOIN status_code AS pid ON {project}_noti.priority = pid.`id`
LEFT JOIN status_code AS sid ON {project}_noti.`status` = sid.`id`
left JOIN status_code AS lsid ON {project}_usergroup_list.`status` = lsid.id
left join status_code as tid on {project}_noti.type = tid.id
where 1=1 {everywhere}
ORDER BY {project}_noti.created_at asc
limit {page},{length}"""
# print(sql)
result = do_tidb_select(sql=sql)
return result
def select_auto_temple_apply_plan(project):
sql = """SELECT
{project}_usergroup_list.group_id,
{project}_usergroup_list.id,
{project}_usergroup_plan.func,
{project}_usergroup_list.created_at
FROM
{project}_usergroup_plan
LEFT JOIN {project}_usergroup_list ON {project}_usergroup_plan.id = {project}_usergroup_list.group_id
WHERE
{project}_usergroup_list.apply_temple_times = 0
AND {project}_usergroup_list.status = 5
AND {project}_usergroup_list.item_count > 0
AND {project}_usergroup_plan.enable_policy in (8,28)
AND FROM_UNIXTIME({project}_usergroup_list.list_init_date,"%Y-%m-%d") = CURRENT_DATE
ORDER BY
{project}_usergroup_list.created_at ASC""".format(project=project)
result = do_tidb_select(sql=sql)
return result
def select_usergroup_data_for_api(project,list_id,length,page,everywhere):
#整组选择要套模板的分群
page = (page-1)*length if page and length and page > 1 else 0
# print(page,length)
sql = f"""SELECT
{project}_usergroup_list.group_id,
{project}_usergroup_list.id,
{project}_usergroup_data.id,
{project}_usergroup_data.data_index,
{project}_usergroup_data.data_key,
{project}_usergroup_data.data_json,
{project}_usergroup_data.`enable`,
status_code.desc,
{project}_usergroup_list.item_count
FROM
{project}_usergroup_data left join {project}_usergroup_list on {project}_usergroup_data.group_list_id={project}_usergroup_list.id left join status_code on {project}_usergroup_data.`enable`=status_code.id
WHERE
group_list_id = {list_id} {everywhere} order by {project}_usergroup_data.data_index asc limit {page},{length}"""
result = do_tidb_select(sql=sql)
return result
def show_scheduler_jobs_db(page,length):
page = (page-1)*length if page and length and page > 1 else 0
sql = f"""select
scheduler_jobs.id,
scheduler_jobs.project,
scheduler_jobs.group_id,
scheduler_jobs.list_index,
from_unixtime( scheduler_jobs.datetime, "%Y-%m-%d %H:%i:%s" ) AS start_at,
scheduler_jobs.data,
scheduler_jobs.priority as priority_id,
pid.desc as priority_name,
scheduler_jobs.status as status_id,
sid.desc as status_name,
from_unixtime( scheduler_jobs.created_at, "%Y-%m-%d %H:%i:%s" ) AS created_at,
from_unixtime( scheduler_jobs.updated_at, "%Y-%m-%d %H:%i:%s" ) AS updated_at
from scheduler_jobs
left join status_code as pid on scheduler_jobs.priority = pid.id
left join status_code as sid on scheduler_jobs.`status` = sid.id
ORDER BY scheduler_jobs.created_at desc limit {page},{length}"""
result = do_tidb_select(sql=sql)
return result
def show_noti_group_db(project,length,page,everywhere):
#查询推送分组列表
page = (page-1)*length if page and length and page > 1 else 0
sql=f"""SELECT
{project}_noti_group.id AS noti_group_id,
{project}_noti_group.plan_id AS plan_id,
{project}_usergroup_plan.group_title AS plan_title,
{project}_noti_group.list_id AS list_id,
from_unixtime( `{project}_usergroup_list`.list_init_date, "%Y-%m-%d %H:%i:%s" ) AS list_init_date,
{project}_usergroup_list.list_desc AS list_desc,
{project}_usergroup_list.jobs_id AS jobs_id,
{project}_usergroup_list.item_count AS item_count,
{project}_usergroup_list.`status` AS list_status_id,
lsid.`desc` as list_status,
{project}_noti_group.data_id as data_id,
{project}_noti_group.temple_id as temple_id,
{project}_noti_temple.`name` as temple_name,
{project}_noti_group.priority as priority_id,
pid.`desc` as priority_name,
{project}_noti_group.`status` as status_id,
sid.`desc` as status_name,
{project}_noti_group.owner as `owner`,
from_unixtime( `{project}_noti_group`.send_at, "%Y-%m-%d %H:%i:%s" ) AS send_at,
from_unixtime( `{project}_noti_group`.created_at, "%Y-%m-%d %H:%i:%s" ) AS created_at,
from_unixtime( `{project}_noti_group`.updated_at, "%Y-%m-%d %H:%i:%s" ) AS updated_at,
`{project}_noti_group`.sent as sent,
`{project}_noti_group`.total as total
FROM
`{project}_noti_group`
LEFT JOIN {project}_usergroup_plan ON {project}_noti_group.plan_id = {project}_usergroup_plan.id
LEFT JOIN {project}_usergroup_list ON {project}_noti_group.list_id = {project}_usergroup_list.id
LEFT JOIN {project}_noti_temple ON {project}_noti_group.temple_id = {project}_noti_temple.id
LEFT JOIN status_code AS pid ON {project}_noti_group.priority = pid.`id`
LEFT JOIN status_code AS sid ON {project}_noti_group.`status` = sid.`id`
left JOIN status_code AS lsid ON {project}_usergroup_list.`status` = lsid.id
where 1=1 {everywhere}
ORDER BY `{project}_noti_group`.created_at desc limit {page},{length}"""
result = do_tidb_select(sql=sql)
return result
def check_long_url(long_url):
timenow = int(time.time())
sql = """select project,short_url,from_unixtime(expired_at),from_unixtime(created_at),src,src_short_url,submitter,utm_source,utm_medium,utm_campaign,utm_content,utm_term,long_url from shortcut where long_url = '{long_url}' and expired_at > {timenow}""".format(
long_url=long_url, timenow=timenow)
result, count = do_tidb_select(sql)
if count > 0:
result_dict = []
for item in result:
project = result[0][0]
short_url = result[0][1]
expired_at = result[0][2]
created_at = result[0][3]
src = result[0][4]
src_short_url = result[0][5]
submitter = result[0][6]
utm_source = result[0][7]
utm_medium = result[0][8]
utm_campaign = result[0][9]
utm_content = result[0][10]
utm_term = result[0][11]
long_url = result[0][12]
result_dict.append({
'project': project,
'short_url': short_url,
'expired_at': expired_at,
'created_at': created_at,
'src': src,
'src_short_url': src_short_url,
'submitter': submitter,
'utm_source': utm_source,
'utm_medium': utm_medium,
'utm_campaign': utm_campaign,
'utm_content': utm_content,
'utm_term': utm_term,
'long_url': long_url
})
return result_dict, 'exist'
else:
return '', 'empty'
def show_temples_db(project):
sql = f"""SELECT
{project}_noti_temple.id,
{project}_noti_temple.`name`,
{project}_noti_temple.temple_desc,
{project}_noti_temple.args,
{project}_noti_temple.content,
{project}_noti_temple.apply_times,
FROM_UNIXTIME( {project}_noti_temple.lastest_apply_time, "%Y-%m-%d %H:%i:%s" ) AS lastest_apply_time,
{project}_noti_temple.lastest_apply_list,
{project}_usergroup_list.list_desc,
{project}_usergroup_plan.group_title,
FROM_UNIXTIME( {project}_noti_temple.created_at, "%Y-%m-%d %H:%i:%s" ) AS created_at,
FROM_UNIXTIME( {project}_noti_temple.updated_at, "%Y-%m-%d %H:%i:%s" ) AS updated_at
FROM
`{project}_noti_temple`
LEFT JOIN
{project}_usergroup_list
on {project}_noti_temple.lastest_apply_list = {project}_usergroup_list.id
left join
{project}_usergroup_plan
on {project}_usergroup_list.group_id = {project}_usergroup_plan.id order by `{project}_noti_temple`.id asc"""
result = do_tidb_select(sql=sql)
return result
