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