def get_trade_id(cls, adx_id, campaign):
"""
获取行业Id
:return:
"""
if adx_id not in cls.trade_mapping:
conn = bunny_engine.connect()
sql = text(
"SELECT m.adproductId, t.adxAdproductId "
"FROM b_base_adx_adproduct t "
"LEFT JOIN b_base_adproduct_mapping m on t.id = m.adxAdproductId "
"WHERE t.adxId = :adx_id").bindparams(adx_id=adx_id)
result = conn.execute(sql).fetchall()
if not result:
current_app.logger.debug('[ DB ] category is empty')
return {}
cls.trade_mapping[adx_id] = {}
for row in result:
cls.trade_mapping[adx_id][
row['adproductId']] = row['adxAdproductId']
category_id = campaign.categoryId
if category_id in cls.trade_mapping[adx_id]:
return cls.trade_mapping[adx_id][category_id]
else:
current_app.logger.error(
'[ DB ] Creative(%s) has no tradeId(%s), Campaign categoryId(%s) not in mapping',
adx_id, creative.id, category_id)
return False
def get_mail_list():
"""
获取邮件发送列表
:return:
"""
conn = bunny_engine.connect()
sql = text("SELECT `id`, `to`, `cc`, `bcc`, `subject`, `body` FROM b_mail WHERE `status` = 1")
return conn.execute(sql).fetchall()
def get_domain(cls, company_id):
if company_id not in cls.domain_mapping:
conn = bunny_engine.connect()
sql = text("SELECT id, domain FROM b_company_domain WHERE companyId = :company_id") \
.columns(id=Integer, domain=String).bindparams(company_id=company_id)
company_domain = conn.execute(sql).fetchone()
cls.domain_mapping[company_id] = company_domain[
'domain'] if company_domain else None
return cls.domain_mapping[company_id]
def sended_mail(mail_id, status=0):
"""
将邮件设定为已发送
:param mail_id:
:return:
"""
conn = bunny_engine.connect()
sql = text("UPDATE b_mail SET `status` = :status WHERE `id` = :id ").bindparams(status=status, id=mail_id)
return conn.execute(sql)
def get_mail_list():
"""
获取邮件发送列表
:return:
"""
conn = bunny_engine.connect()
sql = text(
"SELECT `id`, `to`, `cc`, `bcc`, `subject`, `body` FROM b_mail WHERE `status` = 1"
)
return conn.execute(sql).fetchall()
def creative_type(cls, adx_id):
if adx_id not in cls.creative_type_mapping:
conn = bunny_engine.connect()
sql = text("SELECT creativeTypeId FROM b_relation_adxCreativeTemplate WHERE adxId = :adx_id")\
.bindparams(adx_id=adx_id)
id_list = conn.execute(sql).fetchall()
cls.creative_type_mapping[adx_id] = [
row['creativeTypeId'] for row in id_list
] if id_list else []
return cls.creative_type_mapping[adx_id]
def sended_mail(mail_id, status=0):
"""
将邮件设定为已发送
:param mail_id:
:return:
"""
conn = bunny_engine.connect()
sql = text(
"UPDATE b_mail SET `status` = :status WHERE `id` = :id ").bindparams(
status=status, id=mail_id)
return conn.execute(sql)
