| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604 | import datetimeimport jsonimport osimport randomimport sysimport uuidimport numpy as npimport pandas as pdimport pymysqlimport requestsimport yamlfrom loguru import loggercurr_path = os.path.abspath(os.path.dirname(__file__))project_root_path = curr_path[:curr_path.find("ai_target") + len("ai_target")]sys.path.append(project_root_path)from config.url_and_db import create_campaign_url, create_group_and_creative_url, headers, \    update_campaign_status_url, jeecg_db, jeecg_product_db, db_config_infofrom utils.BaseClass import NpEncoderfrom utils.commonFunc import is_contains_gender, is_contains_age, is_contains_regionwith open('/data/pythonProject/ai_target/config/config.yaml', mode='r', encoding='utf-8') as f:    config = yaml.load(f.read(), Loader=yaml.FullLoader)@logger.catch()def ai_target_combine_create(account_id: int, project_id: int):    logger.info(f"account_id = {account_id}, project_id = {project_id} 开始创建优质定向...")    ins = GetTargetAndCreateAds(account_id, project_id)    # 2、判断 target_combine_to_create 是否为空,为空则不执行后续的操作    if not ins.target_combine_to_create:        logger.info(f"account_id = {account_id},  project_id = {project_id}, "                    f"message = 没有定向组合: 定向冲突或者素材关联的创意个数达到上限, 创建优质定向已结束 ")    else:        # 创建或获取计划信息        ins.add_campaign()        # 3、拼装广告组和创意的参数        ins.assembly_group_and_creative_params()        # 4、信息写如到智能策略表中        ins.write_intelligence_strategy_table()        # 5、发送创建组和创意的请求        request = requests.post(create_group_and_creative_url,                                headers=headers,                                data=json.dumps(ins.request_data, cls=NpEncoder))        response_data = json.loads(request.text)        # 6、接口的返回信息更新到数据库表        ins.update_intelligence_strategy_table(message=response_data)        logger.info(f"account_id = {account_id}, 策略uuid = {ins.ai_strategy_uuid} 的请求返回信息:{response_data}")    returnclass GetTargetAndCreateAds(object):    def __init__(self, account_id, project_id):        self.account_id = account_id        self.project_id = project_id        self.advertiser_strategy_id = None        self.advertiser_strategy = {}        self.ai_strategy_uuid = str(uuid.uuid4())        self.request_data = {}        self.campaign_id = None        self.operation_type = 1  # 新增广告组        self.target_combine_from_table = []        self.target_combine_filter_by_subset = []        self.target_combine_to_create = []        self.get_advertiser_strategy_info()  # 获取账户配置信息        self.get_signature_and_target()  # 从素材定向组合表中获取最新的定向组合        self.filter_target_combine_by_whether_is_subset()  # 筛选出为账户配置子集的定向组合        self.filter_target_combine_by_creative_cnt()  # 依据素材在指定账户下关联的创意个数,进行筛选创建个数,防止超限导致的创建失败    def get_advertiser_strategy_info(self):        """        获取指定账户下客户的策略信息(不管是开发环境还是生产环境,该表都读取生产数据库) 为后续的写入库表,校验一致性做准备        """        sql = f"select * from ctop_ai_kuaishou_advertiser_strategy where account_id = {self.account_id} and status = 1 limit 1"        advertiser_strategy_df = pd.read_sql(sql, jeecg_product_db)        self.advertiser_strategy_id = int(advertiser_strategy_df['id'].values[0])        self.advertiser_strategy = advertiser_strategy_df.T.to_dict()[0]    def add_campaign(self):        """        广告计划,如果存在则获取campaign_id, 否则新建广告计划。        response_data = {            "code": 0,            "data": {                "account_id": 23212,                "campaign_id": 30956133,                "campaign_create_time": "2021-01-13 13:50:16"            },            "message": "SUCCESS"}        """        # 判断当天是否已经存在包含【优质定向】的广告计划,如果有则获取该计划id,否则需要新建计划        sql = f"select campaign_id from ctop_kuaishou_campaign where account_id = {self.account_id} " \              f"and  put_create_time >= curdate() and campaign_name like '%%优质定向V0.2%%'"        campaign_df = pd.read_sql(sql, jeecg_db)        if not campaign_df.empty:            self.campaign_id = campaign_df['campaign_id'].values[0]        else:            # 请求创建广告计划的接口            campaign_name = self.advertiser_strategy['campaign_name']            campaign_name = campaign_name.replace('{{自定义}}', '')            campaign_name = campaign_name.replace('{{日期}}', '')            campaign_name += '_优质定向V0.2_'            campaign_name += str(datetime.datetime.now().date())            create_campaign_request_data = {'account_id': self.account_id,                                            'campaign_name': campaign_name,                                            'type': self.advertiser_strategy['campaign_type'],                                            'day_budget': self.advertiser_strategy['campaign_day_budget'],                                            'day_budget_schedule': eval(self.advertiser_strategy['campaign_day_budget_schedule'])                                            if self.advertiser_strategy['campaign_day_budget_schedule'] else None}            request = requests.post(url=create_campaign_url,                                    headers=headers,                                    data=json.dumps(create_campaign_request_data, cls=NpEncoder))            response_data = json.loads(request.text)            # 2、对接口返回的结果处理            campaign_info_to_db = {                'campaign_uuid': str(uuid.uuid4()),                'account_id': self.account_id,                'ai_strategy_uuid': self.ai_strategy_uuid,                'campaign_name': campaign_name,                'campaign_type': self.advertiser_strategy['campaign_type'],                'day_budget': self.advertiser_strategy['campaign_day_budget'],                'day_budget_schedule': self.advertiser_strategy['campaign_day_budget_schedule'],                'create_time': datetime.datetime.now()            }            if response_data['code'] == 0:                self.campaign_id = response_data['data'].get('campaign_id', None)                campaign_info_to_db['campaign_id'] = self.campaign_id                campaign_info_to_db['campaign_create_time'] = response_data['data'].get('campaign_create_time', None)                campaign_info_to_db['status'] = response_data.get('code', None)                campaign_info_to_db['message'] = response_data.get('message', None)                # self.advertiser_strategy['campaign_status'] 如果为暂停,需要创建成功后,调用修改广告计划状态的接口                # `campaign_status` int(11) DEFAULT '1' COMMENT '计划状态: 1:投放 2:暂停 '                if self.advertiser_strategy['campaign_status'] == 2:                    update_campaign_status_request = requests.post(url=update_campaign_status_url,                                                                   headers=headers,                                                                   data=json.dumps({'accountId': self.account_id,                                                                                    'putStatus': 2,                                                                                    'userId': '113dee46c7df464da78c07a985e92cd1',                                                                                    'campaignIds': [self.campaign_id]},                                                                                   cls=NpEncoder))                    update_campaign_status_response_data = json.loads(update_campaign_status_request.text)                    if update_campaign_status_response_data.get('result') and \                            update_campaign_status_response_data.get('result').get('failCount') == 0:                        logger.info(f"账户:{self.account_id} 下的广告计划 {self.campaign_id} 暂停成功!")                    else:                        logger.error(f"账户:{self.account_id} 下的广告计划 {self.campaign_id} 暂停失败,"                                     f"异常信息: {str(update_campaign_status_response_data.get('result'))}")                        raise Exception(f"账户:{self.account_id} 下的广告计划 {self.campaign_id} 暂停失败!")            else:                campaign_info_to_db['message'] = response_data['message']                campaign_info_to_db['status'] = response_data['code']                raise Exception(f"账户:{self.account_id}, 新建广告计划失败,详细:{str(response_data)}!")            # 3、写入计划层级的操作表            df = pd.DataFrame.from_dict(campaign_info_to_db, orient='index').T            df.to_sql(name="ctop_ai_kuaishou_campaign_level_operation_record",                      con=jeecg_db,                      if_exists='append',                      index=False)    def get_signature_and_target(self):        """        从 ctop_ai_kuaishou_signature_recommended_target_combine 表中读取素材和对应的定向        读取配置文件的条件,筛选出符合条件的定向组合        """        sql = f"select * from ctop_ai_kuaishou_signature_recommended_target_combine_v2 where project_id = {self.project_id} and " \              f"stat_date = (select max(stat_date) from ctop_ai_kuaishou_signature_recommended_target_combine_v2)"        target_df = pd.read_sql(sql, jeecg_db)        # 'age', 'gender', 'city'        features = [key for key, value in config['bayesDim'].items() if value['isOn']]        self.target_combine_from_table = target_df[['id', 'signature'] + features].to_dict(orient='records')    def write_intelligence_strategy_table(self):        """        拼接好的组创意的参数写入到 ctop_ai_kuaishou_intelligence_strategy 中的 ai_strategy_request_content        """        intelligence_strategy_dict = \            {                'ai_strategy_uuid': self.ai_strategy_uuid,                'advertiser_strategy_id': self.advertiser_strategy_id,                'account_id': self.account_id,                'ai_strategy_request_content': str(self.request_data),                'ai_strategy_remark': '优质定向组合V0.2',                'create_time': datetime.datetime.now()            }        df = pd.DataFrame.from_dict(intelligence_strategy_dict, orient='index').T        df.to_sql(name="ctop_ai_kuaishou_intelligence_strategy",                  con=jeecg_db,                  if_exists='append',                  index=False)    def filter_target_combine_by_whether_is_subset(self):        """        ctop_ai_kuaishou_signature_recommended_target_combine 表中的 city 和 province 都对应 self.advertiser_strategy 的 region []  varchar        age 对应 ages_range []  varchar        client 对应 platform_os int        business_interest 对应 business_interest varchar        gender 对应 gender int        """        gender_bool = True        age_bool = True        region_bool = True        for item in self.target_combine_from_table:            if 'gender' in item.keys():                gender_bool, gender_dict = is_contains_gender(item['gender'], self.advertiser_strategy['gender'])            if 'age' in item.keys():                age_bool, age_dict = is_contains_age(item['age'],                                                     self.advertiser_strategy['age_min'],                                                     self.advertiser_strategy['age_max'],                                                     self.advertiser_strategy['ages_range'])            if 'city' in item.keys():                region_bool, region_dict = is_contains_region(item['city'], self.advertiser_strategy['region'])            if gender_bool & age_bool & region_bool:                target_combine = {'signature': item['signature'], 'target_combine_id': item['id']}                target_combine.update(gender_dict) if 'gender' in item.keys() else None                target_combine.update(age_dict) if 'age' in item.keys() else None                target_combine.update(region_dict) if 'city' in item.keys() else None                self.target_combine_filter_by_subset.append(target_combine)            else:                if not gender_bool:                    logger.info(f"优质定向中的gender:{item.get('gender')}, "                                f"与账户:{self.account_id} 配置信息里的gender: {self.advertiser_strategy['gender']} 存在冲突")                if not age_bool:                    logger.info(f"优质定向中的age:{item.get('age')}, "                                f"与账户:{self.account_id} 配置信息里的age: "                                f"{self.advertiser_strategy['age_min'], self.advertiser_strategy['age_max'], self.advertiser_strategy['ages_range']}"                                f"存在冲突")                if not region_bool:                    logger.info(f"优质定向中的region:{item.get('city')}, "                                f"与账户:{self.account_id} 配置信息里的region: {self.advertiser_strategy['region']} 存在冲突")    def filter_target_combine_by_creative_cnt(self):        """        依据素材已经关联的创意个数,来定素材可以创建的定向组合个数, 并从中随机随着N个        """        if not self.target_combine_filter_by_subset:            return        # 1-1 获取素材在该账户下关联的创意个数,过滤掉素材关联创意个数超过200的情况        df = pd.DataFrame(self.target_combine_filter_by_subset)        # target_combine_filter_by_subset 转化为 dataframe 时 age_min, age_max 会填充为 np.nan,        # 会导致后续拼装的参数发送给java服务报错(jackson.core.JsonParseException: Non-standard token \'NaN\':)        # 需要把 np.nan 替换为 None        df.replace({np.nan: None}, inplace=True)        # signature in ('03b93728f0d82ea7865c3c7cf632bc1b',), 避免这样的sql报错        sig_lst = tuple(list(df.signature.unique()) * 2) if df.signature.nunique() == 1 else tuple(list(df.signature.unique()))        sql = f"select t1.signature, t2.creative_count " \              f"from (select account_id, signature, photo_id from ctop_kuaishou_video_get where account_id = {self.account_id} " \              f"and signature in {sig_lst} ) t1 left join ctop_kuaishou_video_relate_creatives t2 " \              f"on t1.account_id = t2.account_id and t1.photo_id = t2.photo_id "        creative_cnt_df = pd.read_sql(sql, jeecg_product_db)        creative_cnt_df = creative_cnt_df[creative_cnt_df.creative_count < 200]        # 1-2 计算每个素材还能创建的广告组(定向)个数: (200 - 已关联创意个数) / 15        creative_cnt_df['target_combine_cnt'] = np.floor((200 - creative_cnt_df['creative_count']) / 15)        creative_cnt_df = creative_cnt_df[creative_cnt_df.target_combine_cnt >= 1]        final_target_combine = pd.DataFrame([])        for sig in creative_cnt_df.signature.unique():            n = creative_cnt_df[creative_cnt_df.signature == sig].target_combine_cnt.values[0]            now = datetime.datetime.now()            # Cannot take a larger sample than population when 'replace=False'            n = n if n <= len(df[df.signature == sig]) else len(df[df.signature == sig])            sig_target_df = df[df.signature == sig].sample(int(n), axis=0, random_state=(now.year + now.month + now.day))            final_target_combine = final_target_combine.append(sig_target_df)        self.target_combine_to_create = final_target_combine.to_dict(orient='records')        # 从集合中随机选取N个组合        combine_cnt = config['filterTargetCombine']['combineCnt']        random.shuffle(self.target_combine_to_create)        self.target_combine_to_create = self.target_combine_to_create[:combine_cnt]    def assembly_group_and_creative_params(self):        """        拼接参数,请求创建接口        """        group_params, group_params_to_db = self.get_group_params()        creative_params, creative_params_to_db = self.get_creative_params()        self.request_data['group_list'] = []        group_cnt = 1        batch_group_params_to_db = []        batch_creative_params_to_db = []        for item in self.target_combine_to_create:            # 使用优质定向更新组层级的参数            target_combine = item.copy()            del target_combine['target_combine_id']            del target_combine['signature']            group_params.update(target_combine)            # 写入数据库的字段,list类型需要转化为str            group_params_to_db.update(target_combine)            cols = ['ages_range', 'region']            for col in cols:                if col in target_combine.keys():                    group_params_to_db[col] = str(group_params_to_db[col])            group_uuid = str(uuid.uuid4())            group_name = group_params['unit_name']            # 获取素材名称, 在广告组命名中进行替换 (同时获取photo_id, 用于拼装创意层级的参数)            sql = f"select photo_id, photo_name, material_type from ctop_kuaishou_video_get where account_id = {self.account_id} and " \                  f"signature = '{item['signature']}' limit 1"            signature_df = pd.read_sql(sql, jeecg_product_db)            photo_name = signature_df['photo_name'].values[0] if not signature_df.empty else 'unknown'            photo_id = signature_df['photo_id'].values[0] if not signature_df.empty else None            material_type = signature_df['material_type'].values[0] if not signature_df.empty else None            group_name.replace('{{素材名称}}', photo_name)            group_name = group_name + '_优质定向V0.2' + '_' + str(group_cnt)            # 写入数据表中组信息            single_group_params_to_db = group_params_to_db.copy()            single_group_params_to_db['target_combine_id'] = item['target_combine_id']            single_group_params_to_db['group_uuid'] = group_uuid            single_group_params_to_db['group_name'] = group_name            single_group_params_to_db['create_time'] = datetime.datetime.now()            batch_group_params_to_db.append(single_group_params_to_db)            # 拼装返回json的组信息            single_group_params_to_request = group_params.copy()            single_group_params_to_request['group_uuid'] = group_uuid            single_group_params_to_request['unit_name'] = group_name            single_group_params_to_request['creative_list'] = []            group_cnt += 1            # 拼装广告组下面的创意信息            # 一个素材组合为15个一样的创意,因信息流已经下架,不需要提供封面信息            for creative_cnt in range(15):                creative_uuid = str(uuid.uuid4())                creative_name = creative_params_to_db['creative_name'] + '_' + str(creative_cnt + 1)                single_creative_params_to_db = creative_params_to_db.copy()                single_creative_params_to_db['creative_uuid'] = creative_uuid                single_creative_params_to_db['creative_name'] = creative_name                single_creative_params_to_db['photo_id'] = int(photo_id) if photo_id else None                single_creative_params_to_db['creative_material_type'] = material_type                single_creative_params_to_db['create_time'] = datetime.datetime.now()                batch_creative_params_to_db.append(single_creative_params_to_db)                single_creative_params_to_request = creative_params.copy()                single_creative_params_to_request['creative_uuid'] = creative_uuid                single_creative_params_to_request['creative_name'] = creative_name                single_creative_params_to_request['photo_id'] = int(photo_id) if photo_id else None                single_creative_params_to_request['creative_material_type'] = material_type                # 单个创意信息加入到组里面的 creative_list 中                single_group_params_to_request['creative_list'].append(single_creative_params_to_request)            # 组和创意信息添加到最终返回数据 group_list 中            self.request_data['group_list'].append(single_group_params_to_request)        # 创意层级信息批量写入数据库        creative_df = pd.DataFrame(batch_creative_params_to_db)        creative_df.to_sql(name="ctop_ai_kuaishou_creative_level_operation_record",                           con=jeecg_db,                           if_exists='append',                           index=False)        # 组层级信息批量写入数据库        group_df = pd.DataFrame(batch_group_params_to_db)        group_df.to_sql(name="ctop_ai_kuaishou_unit_level_operation_record",                        con=jeecg_db,                        if_exists='append',                        index=False)        self.request_data['account_id'] = self.account_id        self.request_data['campaign_id'] = self.campaign_id    def get_group_params(self):        # 1、从账户信息表读取基本的配置信息        group_params = {'unit_name': self.advertiser_strategy.get('group_name'),                        'put_status': self.advertiser_strategy.get('group_status'),                        'template_id': self.advertiser_strategy.get('template_id'),                        'bid_type': self.advertiser_strategy.get('bid_type'),                        'use_app_market': self.advertiser_strategy.get('use_app_market'),                        'app_store': self.advertiser_strategy.get('app_store'),                        'bid': self.advertiser_strategy.get('bid'),                        'cpa_bid': self.advertiser_strategy.get('cpa_bid'),                        'smart_bid': self.advertiser_strategy.get('smart_bid'),                        'ocpx_action_type': self.advertiser_strategy.get('ocpx_action_type'),                        'deep_conversion_type': self.advertiser_strategy.get('deep_conversion_type'),                        'roi_ratio': self.advertiser_strategy.get('roi_ratio'),                        'deep_conversion_bid': self.advertiser_strategy.get('deep_conversion_bid'),                        'scene_id': self.advertiser_strategy.get('scene_id'),                        'unit_type': self.advertiser_strategy.get('unit_type'),                        'begin_time': self.advertiser_strategy.get('begin_time') if self.advertiser_strategy.get('begin_time')                        else str(datetime.datetime.now().date()),                        'end_time': self.advertiser_strategy.get('end_time'),                        'schedule_time': self.advertiser_strategy.get('schedule_time'),                        'day_budget': self.advertiser_strategy.get('unit_day_budget'),                        'day_budget_schedule': self.advertiser_strategy.get('unit_day_budget_schedule'),                        'convert_id': self.advertiser_strategy.get('convert_id'),                        'url_type': self.advertiser_strategy.get('url_type'),                        'web_uri_type': self.advertiser_strategy.get('web_uri_type'),                        'url': self.advertiser_strategy.get('url'),                        'schema_uri': self.advertiser_strategy.get('schema_uri'),                        'app_id': self.advertiser_strategy.get('app_id'),                        'show_mode': self.advertiser_strategy.get('show_mode'),                        'speed': self.advertiser_strategy.get('speed'),                        'site_type': self.advertiser_strategy.get('site_type'),                        'gift_data': self.advertiser_strategy.get('gift_data'),                        'video_landing_page': self.advertiser_strategy.get('video_landing_page'),                        'auto_target': self.advertiser_strategy.get('auto_target'),                        'auto_create_photo': self.advertiser_strategy.get('auto_create_photo'),                        # 'item_id': self.advertiser_strategy.get('item_id'),                        # 'merchant_item_put_type': self.advertiser_strategy.get('merchant_item_put_type'),                        # 'fiction_id': self.advertiser_strategy.get('fiction_id'),                        'smart_cover': self.advertiser_strategy.get('smart_cover'),                        'asset_mining': self.advertiser_strategy.get('asset_mining'),                        # 'consult_id': self.advertiser_strategy.get('consult_id'),                        # 'adv_card_option': self.advertiser_strategy.get('adv_card_option'),                        # 'adv_card_list': self.advertiser_strategy.get('adv_card_list'),                        # 'card_type': self.advertiser_strategy.get('card_type'),                        # 'merchandise_id': self.advertiser_strategy.get('merchandise_id'),                        # 'merchandise_type': self.advertiser_strategy.get('merchandise_type'),                        # 'intention_target': self.advertiser_strategy.get('intention_target'),                        # 'playable_id': self.advertiser_strategy.get('playable_id'),                        # 'play_button': self.advertiser_strategy.get('play_button'),                        # 'dpa_unit_param': self.advertiser_strategy.get('dpa_unit_param'),                        'region': self.advertiser_strategy.get('region'),                        'district_ids': self.advertiser_strategy.get('district_ids'),                        'user_type': self.advertiser_strategy.get('user_type'),                        'age_min': self.advertiser_strategy.get('age_min'),                        'age_max': self.advertiser_strategy.get('age_min'),                        'ages_range': self.advertiser_strategy.get('ages_range'),                        'gender': self.advertiser_strategy.get('gender'),                        'platform_os': self.advertiser_strategy.get('platform_os'),                        'android_osv': self.advertiser_strategy.get('android_osv'),                        'ios_osv': self.advertiser_strategy.get('ios_osv'),                        'network': self.advertiser_strategy.get('network'),                        'device_brand': self.advertiser_strategy.get('device_brand'),                        'device_price': self.advertiser_strategy.get('device_price'),                        'business_interest_type': self.advertiser_strategy.get('business_interest_type'),                        'business_interest': self.advertiser_strategy.get('business_interest'),                        'fans_star': self.advertiser_strategy.get('fans_star'),                        'interest_video': self.advertiser_strategy.get('interest_video'),                        'app_interest': self.advertiser_strategy.get('app_interest'),                        # 'app_interest_ids': self.advertiser_strategy.get('app_interest_ids'),                        'app_ids': self.advertiser_strategy.get('app_ids'),                        'filter_converted_level': self.advertiser_strategy.get('filter_converted_level'),                        'population': self.advertiser_strategy.get('population'),                        'exclude_population': self.advertiser_strategy.get('exclude_population'),                        'paid_audience': self.advertiser_strategy.get('paid_audience'),                        'splash_ad_switch': self.advertiser_strategy.get('splash_ad_switch'),                        'is_open': self.advertiser_strategy.get('is_open'),                        'no_age_break': self.advertiser_strategy.get('no_age_break'),                        'no_gender_break': self.advertiser_strategy.get('no_gender_break'),                        'no_area_break': self.advertiser_strategy.get('no_area_break'),                        'behavior_interest': self.advertiser_strategy.get('behavior_interest')                        }        # 2、smart_cover 和 asset_mining 需要将0/1 转化为 False/True        group_params['smart_cover'] = True if group_params.get('smart_cover', 0) == 1 else False        group_params['asset_mining'] = True if group_params.get('asset_mining', 0) == 1 else False        # 3、广告组名称,通配符的替换        # 900299 -{{广告位置}}--{{创意制作方式}}--{{日期}}-  (日期、素材名称、应用名称、应用标记、创意制作方式、广告位置)        # 3-1 获取应用标记和应用名称        sql = """select app_version, app_name from ctop_kuaishou_app_list where app_id = %s limit 1""" % \              group_params.get('app_id', -1)        app_info_df = pd.read_sql(sql, jeecg_product_db)        app_version = app_info_df['app_version'].values[0] if not app_info_df.empty else 'unknown'        app_name = app_info_df['app_name'].values[0] if not app_info_df.empty else 'unknown'        # 3-2 素材名称, 在拼装广告组和创意层级函数中获取        # 3-3 获取创意制作方式        unit_type = '自定义' if group_params.get('unit_type') == 4 \            else ('程序化创意' if group_params.get('unit_type') == 5 else '程序化创意2.0')        # 3-4 获取广告位置        scene_id_dict = {1: '优选广告位',                         3: '视频播放页广告-便利贴广告',                         5: '联盟广告',                         6: '上下滑大屏广告',                         7: '信息流广告',                         10: '联盟场景'}        scene_id = '+'.join([scene_id_dict[item] for item in eval(group_params.get('scene_id'))])        unit_name = group_params['unit_name'].replace('{{应用标记}}', app_version). \            replace('{{应用名称}}', app_name). \            replace('{{创意制作方式}}', unit_type). \            replace('{{广告位置}}', scene_id)        # 3-5 日期, 在拼装广告组和创意层级函数中获取        now_date = datetime.datetime.now().strftime('%Y-%m-%d_%H:%M')        if '日期' in group_params['unit_name']:            unit_name = unit_name.replace('{{日期}}', now_date)        else:            unit_name = unit_name + '_' + now_date        group_params['unit_name'] = unit_name        group_params['unit_name'] = group_params['unit_name'].replace('{{自定义}}', '')        # 4、处理 cpa_bid        # 因为存在 【单出价请输入单数字,阶梯出价请用/隔开】 如:1900/2000/2100 cpa_bid varchar(255) DEFAULT NULL COMMENT '出价'        # 处理方式按'/'进行分割,取最后一个值(也就是最大值),并转化为int型        group_params['cpa_bid'] = int(group_params['cpa_bid'].split('/')[-1]) if '/' in group_params['cpa_bid'] \            else (int(group_params['cpa_bid'].split('-')[-1]) if '-' in group_params['cpa_bid'] else int(group_params['cpa_bid']))        # 5、 写入数据库的组参数        group_params_to_db = group_params.copy()        # 5-1 需要删除的字段        drop_cols = ['put_status', 'splash_ad_switch']        for col in drop_cols:            if col in group_params_to_db.keys():                del group_params_to_db[col]        group_params_to_db['group_name'] = group_params_to_db.pop('unit_name')        group_params_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid        group_params_to_db['account_id'] = self.account_id        group_params_to_db['campaign_id'] = self.campaign_id        group_params_to_db['operation_type'] = self.operation_type        group_params_to_db['status'] = 1        group_params_to_db['message'] = None        # 6、用于组装发送请求的部分字段,需要转化为list类型        cols_to_list = ['app_store', 'scene_id', 'region', 'day_budget_schedule', 'ages_range', 'device_brand', 'business_interest',                        'fans_star', 'interest_video', 'app_interest', 'app_interest_ids', 'app_ids', 'population', 'district_ids',                        'exclude_population', 'paid_audience']        for col in cols_to_list:            if col in group_params.keys():                group_params[col] = eval(group_params[col]) if group_params[col] else None        return group_params, group_params_to_db    def get_creative_params(self):        # 1、从账户信息表读取基本的配置信息        creative_params = {'creative_name': self.advertiser_strategy.get('creative_name'),                           'put_status': self.advertiser_strategy.get('group_status'),                           'action_bar_text': self.advertiser_strategy.get('action_bar_text'),                           'description': self.advertiser_strategy.get('description'),                           'short_slogan': self.advertiser_strategy.get('short_slogan'),                           'sticker_title': self.advertiser_strategy.get('sticker_title'),                           'overlay_type': self.advertiser_strategy.get('overlay_type'),                           'expose_tag': self.advertiser_strategy.get('expose_tag'),                           'new_expose_tag': self.advertiser_strategy.get('new_expose_tag'),                           'site_id': self.advertiser_strategy.get('site_id'),                           'click_track_url': self.advertiser_strategy.get('click_track_url'),                           'impression_url': self.advertiser_strategy.get('impression_url'),                           'ad_photo_played_t3s_url': self.advertiser_strategy.get('ad_photo_played_t3s_url'),                           'actionbar_click_url': self.advertiser_strategy.get('actionbar_click_url'),                           'creative_category': self.advertiser_strategy.get('creative_category'),                           'creative_tag': self.advertiser_strategy.get('creative_tag'),                           'live_creative_type': self.advertiser_strategy.get('live_creative_type')                           }        # 1、description 可能存在多个值,需要从中随机获取一个        if creative_params['description'] is not None:            description_lst = eval(creative_params['description'])            random.shuffle(description_lst)            creative_params['description'] = description_lst[0]        # 2 写入数据库的创意参数(数据库表结构没有下面的字段,因此入库时去掉这些字段)        creative_params_to_db = creative_params.copy()        drop_cols = ['put_status', 'live_creative_type']        for col in drop_cols:            if col in creative_params_to_db:                del creative_params_to_db[col]        creative_params_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid        creative_params_to_db['account_id'] = self.account_id        creative_params_to_db['campaign_id'] = self.campaign_id        creative_params_to_db['operation_type'] = self.operation_type        creative_params_to_db['status'] = 1        creative_params_to_db['message'] = None        # 3、用于组装发送请求的部分字段,需要转化为list类型        cols_to_list = ['creative_tag', 'new_expose_tag']        for col in cols_to_list:            if col in creative_params.keys():                creative_params[col] = eval(creative_params[col]) if creative_params[col] else None        return creative_params, creative_params_to_db    def update_intelligence_strategy_table(self, message=None):        """        更新 intelligence_strategy_table        :return:        """        db_con = pymysql.connect(host=db_config_info['host'],                                 port=db_config_info['port'],                                 user=db_config_info['username'],                                 password=db_config_info['password'],                                 database=db_config_info['database'],                                 charset='utf8')        cursor = db_con.cursor()        if message:            cursor.execute("""UPDATE ctop_ai_kuaishou_intelligence_strategy                                SET message = %s                                WHERE ai_strategy_uuid = %s""", (str(message), self.ai_strategy_uuid))        db_con.commit()        db_con.close()
 |