def get_campaigns(yaml_path, config_path, conf_section, month_span=None):
sql = 'select id, name '\
'from prepaid_card_campaign '
db_conf = kits.get_mysql_config(config_path, conf_section)
data = []
if month_span and isinstance(month_span, int):
start_date = arrow.now('Asia/Shanghai').replace(months=-month_span)
sql += " where created_at>'{0}'"\
"".format(start_date.format('YYYY-MM-DD'))
sql += " order by created_at desc"
campaign_info = kits.get_mysql_data(db_conf, sql)
if len(campaign_info) > 0:
for (campaign_id, campaign_name) in campaign_info:
campaign_data = {
'id': campaign_id,
'name': campaign_name,
'batches': [],
}
sql_batch = 'select id, name, quantity '\
'from prepaid_card_batch '\
'where prepaid_card_campaign_id={0}'\
''.format(campaign_id)
batch_info = kits.get_mysql_data(db_conf, sql_batch)
if len(batch_info) > 0:
for (batch_id, batch_name, quantity) in batch_info:
batch_data = {
'id': batch_id,
'name': batch_name,
'quantity': quantity,
}
campaign_data['batches'].append(batch_data)
data.append(campaign_data)
return data
def get_ids(self, str_source):
source = str_source.split('=')
with open(self.yaml_path, encoding='utf-8') as f:
yaml_data = yaml.load(f)
# getting correspoding yaml node
for job in yaml_data:
if 'get register info' in job.keys():
for sub_job in job['get register info']:
if sub_job["id"] == self.yaml_id:
db_conf = kits.get_mysql_config(self.config_path,
sub_job['db_info'])
str_bids = ''
if source[0] == 'bid':
str_bids = '({0})'.format(source[1])
if source[0] == 'cid':
str_bids = self.get_batch_ids(db_conf,
source[1])
if str_bids == '':
raise RuntimeError('查询类型异常')
sql_str = sub_job['mysql'].replace('{source_data}',
str_bids)
self.get_campaign_info(db_conf,
source[0],
source[1])
users = kits.get_mysql_data(db_conf, sql_str)
self.appand_general_info(users,
self.name,
self.user_amount)
def get_campaign_info(self, db_conf, data_type, data_id):
if 'bid' == data_type:
sql_str_total = 'select name, quantity '\
'from prepaid_card_batch '\
'where id='\
'{0};'.format(data_id)
if 'cid' == data_type:
sql_str_total = 'select pcc.name, count(0) '\
'from prepaid_card_campaign pcc '\
'left join prepaid_card_batch pcb '\
'on pcb.prepaid_card_campaign_id=pcc.id '\
'left join prepaid_card pc '\
'on pc.prepaid_card_batch_id=pcb.id '\
'where pcc.id = {0} '\
'group by pcc.id;'.format(data_id)
if sql_str_total:
campaign_info = kits.get_mysql_data(db_conf, sql_str_total)
if len(campaign_info) == 1:
self.name = campaign_info[0][0]
self.user_amount = campaign_info[0][1]
return
else:
raise RuntimeError('获取卡信息失败')
raise RuntimeError('异常的获取活动信息请求')
def get_ids(self, str_source):
source = str_source.split('=')
with open(self.yaml_path, encoding='utf-8') as f:
yaml_data = yaml.load(f)
# getting correspoding yaml node
for job in yaml_data:
if 'get register info' in job.keys():
for sub_job in job['get register info']:
if sub_job["id"] == self.yaml_id:
db_conf = kits.get_mysql_config(
self.config_path, sub_job['db_info'])
str_bids = ''
if source[0] == 'bid':
str_bids = '({0})'.format(source[1])
if source[0] == 'cid':
str_bids = self.get_batch_ids(
db_conf, source[1])
if str_bids == '':
raise RuntimeError('查询类型异常')
sql_str = sub_job['mysql'].replace(
'{source_data}', str_bids)
self.get_campaign_info(db_conf, source[0],
source[1])
users = kits.get_mysql_data(db_conf, sql_str)
self.appand_general_info(users, self.name,
self.user_amount)
def get_campaign_info(self, db_conf, data_type, data_id):
if 'bid' == data_type:
sql_str_total = 'select name, quantity '\
'from prepaid_card_batch '\
'where id='\
'{0};'.format(data_id)
if 'cid' == data_type:
sql_str_total = 'select pcc.name, count(0) '\
'from prepaid_card_campaign pcc '\
'left join prepaid_card_batch pcb '\
'on pcb.prepaid_card_campaign_id=pcc.id '\
'left join prepaid_card pc '\
'on pc.prepaid_card_batch_id=pcb.id '\
'where pcc.id = {0} '\
'group by pcc.id;'.format(data_id)
if sql_str_total:
campaign_info = kits.get_mysql_data(db_conf, sql_str_total)
if len(campaign_info) == 1:
self.name = campaign_info[0][0]
self.user_amount = campaign_info[0][1]
return
else:
raise RuntimeError('获取卡信息失败')
raise RuntimeError('异常的获取活动信息请求')
def get_batch_ids(self, db_conf, campaign_id):
sql_str = 'select id from prepaid_card_batch where '\
'prepaid_card_campaign_id=' + campaign_id
batches = kits.get_mysql_data(db_conf, sql_str)
if len(batches) > 0:
str_bids = ', '.join([str(bid) for (bid,) in batches])
return '({0})'.format(str_bids)
raise RuntimeError('活动下没有任何充值卡')
def get_batch_ids(self, db_conf, campaign_id):
sql_str = 'select id from prepaid_card_batch where '\
'prepaid_card_campaign_id=' + campaign_id
batches = kits.get_mysql_data(db_conf, sql_str)
if len(batches) > 0:
str_bids = ', '.join([str(bid) for (bid, ) in batches])
return '({0})'.format(str_bids)
raise RuntimeError('活动下没有任何充值卡')
def get_campaigns(yaml_path, config_path, conf_section, month_span=None):
sql = 'select id, name '\
'from prepaid_card_campaign '
db_conf = kits.get_mysql_config(config_path,
conf_section)
data = []
if month_span and isinstance(month_span, int):
start_date = arrow.now('Asia/Shanghai').replace(months=-month_span)
sql += " where created_at>'{0}'"\
"".format(start_date.format('YYYY-MM-DD'))
sql += " order by created_at desc"
campaign_info = kits.get_mysql_data(db_conf, sql)
if len(campaign_info) > 0:
for (campaign_id, campaign_name) in campaign_info:
campaign_data = {
'id': campaign_id,
'name': campaign_name,
'batches': [],
}
sql_batch = 'select id, name, quantity '\
'from prepaid_card_batch '\
'where prepaid_card_campaign_id={0}'\
''.format(campaign_id)
batch_info = kits.get_mysql_data(db_conf, sql_batch)
if len(batch_info) > 0:
for (batch_id, batch_name, quantity) in batch_info:
batch_data = {
'id': batch_id,
'name': batch_name,
'quantity': quantity,
}
campaign_data['batches'].append(batch_data)
data.append(campaign_data)
return data
def get_ids(self, str_source):
str_mobiles = '({0})'.format(self.get_mobiles(str_source))
with open(self.yaml_path, encoding='utf-8') as f:
yaml_data = yaml.load(f)
# getting correspoding yaml node
for job in yaml_data:
if 'get register info' in job.keys():
for sub_job in job['get register info']:
if sub_job["id"] == self.yaml_id:
sql_str = sub_job['mysql'].replace(
'{source_data}', str_mobiles)
db_conf = kits.get_mysql_config(
self.config_path, sub_job['db_info'])
users = kits.get_mysql_data(db_conf, sql_str)
self.appand_general_info(users, self.name,
self.user_amount)
def get_ids(self, str_source):
str_mobiles = '({0})'.format(self.get_mobiles(str_source))
with open(self.yaml_path, encoding='utf-8') as f:
yaml_data = yaml.load(f)
# getting correspoding yaml node
for job in yaml_data:
if 'get register info' in job.keys():
for sub_job in job['get register info']:
if sub_job["id"] == self.yaml_id:
sql_str = sub_job['mysql'].replace('{source_data}',
str_mobiles)
db_conf = kits.get_mysql_config(self.config_path,
sub_job['db_info'])
users = kits.get_mysql_data(db_conf, sql_str)
self.appand_general_info(users,
self.name,
self.user_amount)
def get_marketing_info(self):
try:
if not self.ids or len(self.ids) == 0:
raise RuntimeError('未获取到用户id')
str_ids = '({0})'.format(', '.join(str(x) for x in self.ids))
self.result['ids'] = str_ids
with open(self.yaml_path, encoding='utf-8') as f:
yaml_data = yaml.load(f)
# getting correspoding yaml node
for job in yaml_data:
if 'user track' in job.keys():
for sub_job in job['user track']:
if sub_job['type'] not in self.AVAILABLE_DATA_TYPE:
raise RuntimeError('{}: 未定义的数据处理类型'
''.format(sub_job['id']))
sql = sub_job['mysql'].replace('{ids}', str_ids)
db_conf = kits.get_mysql_config(self.config_path,
sub_job['db_info'])
print(sql)
user_stats = kits.get_mysql_data(db_conf, sql)
value = self.return_stats_value(sub_job['type'],
user_stats)
stats_info = {
'name': sub_job['name'],
'id': sub_job['id'],
'value': value,
'type': sub_job['type'],
}
self.result['res_data'].append(stats_info)
self.result['success'] = True
self.result['err_message'] = ''
return self.result
except IndexError:
err_message = '{0}: {1}'.format(str(sys.exc_info()[0]),
str(sys.exc_info()[1]))
self.result['err_message'] = err_message
return self.result
def get_marketing_info(self):
try:
if not self.ids or len(self.ids) == 0:
raise RuntimeError('未获取到用户id')
str_ids = '({0})'.format(', '.join(str(x) for x in self.ids))
self.result['ids'] = str_ids
with open(self.yaml_path, encoding='utf-8') as f:
yaml_data = yaml.load(f)
# getting correspoding yaml node
for job in yaml_data:
if 'user track' in job.keys():
for sub_job in job['user track']:
if sub_job['type'] not in self.AVAILABLE_DATA_TYPE:
raise RuntimeError('{}: 未定义的数据处理类型'
''.format(sub_job['id']))
sql = sub_job['mysql'].replace('{ids}', str_ids)
db_conf = kits.get_mysql_config(
self.config_path, sub_job['db_info'])
print(sql)
user_stats = kits.get_mysql_data(db_conf, sql)
value = self.return_stats_value(
sub_job['type'], user_stats)
stats_info = {
'name': sub_job['name'],
'id': sub_job['id'],
'value': value,
'type': sub_job['type'],
}
self.result['res_data'].append(stats_info)
self.result['success'] = True
self.result['err_message'] = ''
return self.result
except IndexError:
err_message = '{0}: {1}'.format(str(sys.exc_info()[0]),
str(sys.exc_info()[1]))
self.result['err_message'] = err_message
return self.result
