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_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