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_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_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_export_data(r_scope,
r_type,
r_format,
r_ids,
yaml_path,
config_path,
export_dir=None):
'''
export data to excel or csv
r_scope for top level of yaml data
r_type for specfic data to get
r_format for export file type
r_ids for user string format ids
like (id1, id2, id3 ...)
'''
AVAILABLE_FILE_FORMAT = ('csv', 'xls')
if r_format not in AVAILABLE_FILE_FORMAT:
raise RuntimeError('您请求了不支持的导出格式')
try:
with open(yaml_path, encoding='utf-8') as f:
yaml_data = yaml.load(f)
# getting correspoding yaml node
for job in yaml_data:
if r_scope in job.keys():
for sub_job in job[r_scope]:
if r_type == sub_job['id']:
sql_str = sub_job['mysql']
db_config = kits.get_mysql_config(
config_path, sub_job['db_info'])
if db_config is None:
raise RuntimeError('读取数据库配置失败')
cnx = mysql.connector.connect(**db_config)
cursor = cnx.cursor()
sql_str = sql_str.replace('{ids}', r_ids)
cursor.execute(sql_str)
res_data = [cursor.column_names]
res_data += cursor.fetchall()
return kits.generate_file(
r_format, res_data, export_dir)
raise RuntimeError('没有找到需要导出的数据')
except IndexError:
err_message = '{0}: {1}'.format(str(sys.exc_info()[0]),
str(sys.exc_info()[1]))
return {'message': err_message}
def get_export_data(r_scope, r_type, r_format, r_ids,
yaml_path, config_path,
export_dir=None):
'''
export data to excel or csv
r_scope for top level of yaml data
r_type for specfic data to get
r_format for export file type
r_ids for user string format ids
like (id1, id2, id3 ...)
'''
AVAILABLE_FILE_FORMAT = ('csv', 'xls')
if r_format not in AVAILABLE_FILE_FORMAT:
raise RuntimeError('您请求了不支持的导出格式')
try:
with open(yaml_path, encoding='utf-8') as f:
yaml_data = yaml.load(f)
# getting correspoding yaml node
for job in yaml_data:
if r_scope in job.keys():
for sub_job in job[r_scope]:
if r_type == sub_job['id']:
sql_str = sub_job['mysql']
db_config = kits.get_mysql_config(
config_path,
sub_job['db_info'])
if db_config is None:
raise RuntimeError('读取数据库配置失败')
cnx = mysql.connector.connect(**db_config)
cursor = cnx.cursor()
sql_str = sql_str.replace('{ids}', r_ids)
cursor.execute(sql_str)
res_data = [cursor.column_names]
res_data += cursor.fetchall()
return kits.generate_file(r_format,
res_data,
export_dir)
raise RuntimeError('没有找到需要导出的数据')
except IndexError:
err_message = '{0}: {1}'.format(str(sys.exc_info()[0]),
str(sys.exc_info()[1]))
return {'message': err_message}
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_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_data(self, stats_type):
if not os.path.exists(self.CONFIG_YAML_PATH):
self.result['err_message'] = '未找到YAML配置文件'
return self.result
# the node info to be dealed
sections = None
title = None
with open(self.CONFIG_YAML_PATH, encoding='utf-8') as f:
yaml_data = yaml.load(f)
# getting correspoding yaml node
sections = None
for job in yaml_data:
if 'routine jobs' in job.keys():
for r_job in job['routine jobs']:
if stats_type == r_job["stats"]:
title = r_job['title']
sections = r_job['sections']
config_section = r_job['CONFIG']
if sections is None:
self.result['err_message'] = '未找到处理类型 {0}'\
''.format(stats_type)
return self.result
today = arrow.now('Asia/Shanghai')
stats_end_date = arrow.now('Asia/Shanghai').replace(days=-1)
stats_start_date_month = stats_end_date.replace(days=-30)
stats_dates_month = []
for dt in arrow.Arrow.range('day',
stats_start_date_month,
stats_end_date):
stats_dates_month.append(dt.format('YY-MM-DD'))
x_val_month = np.arange(0, len(stats_dates_month))
db_config = kits.get_mysql_config(self.CONFIG_PATH, config_section)
if db_config is None:
self.result['err_message'] = '读取数据库配置失败'
return self.result
# getting data
cnx = mysql.connector.connect(**db_config)
cursor = cnx.cursor()
show_legend = True
show_annotate = True
for section in sections:
for figure in section['figures']:
# Draw every figure
figure['url'] = self.IMG_PATH\
+ figure['url']\
+ '_'\
+ arrow.now().format('YYDDDD')\
+ '.png'
y_data = []
for line in figure['lines']:
# get data
stats_data = []
day = stats_start_date_month
while (day <= stats_end_date):
start_date_str = day.format('YYYY-MM-DD')
day = day.replace(days=1)
end_date_str = day.format('YYYY-MM-DD')
sql_str = line['mysql'].replace('{start_date}',
start_date_str)
sql_str = sql_str.replace('{end_date}',
end_date_str)
cursor.execute(sql_str)
raw_data = cursor.fetchall()
if len(raw_data) == 0:
stats_data.append(0)
else:
stats_data.append(raw_data[0][1])
y_data.append({'data': np.array(stats_data),
'label': line['label']})
if y_data:
drawfigure.draw_line_chart('month',
figure['url'],
x_val_month,
y_data,
stats_dates_month,
title=figure['name'],
ylabel=figure['ylabel'],
show_annotate=show_annotate,
show_legend=show_legend)
cnx.close()
# build response
self.result['success'] = True
self.result['err_message'] = ''
self.result['title'] = title
self.result['sections'] = sections
return self.result
