| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215 | import pandas as pdfrom utils.UrlConfig import *import jsonimport loggingimport tracebackimport randomimport tornado.webfrom concurrent_log import ConcurrentTimedRotatingFileHandlerfrom utils.ConstantConfig import *from utils.DataBaseConfig import *from utils.CommonFunction import get_history_video_info, get_new_video_info, get_missing_video_info, \    get_top_video_info, get_campaign_and_group_name_rule, get_app_detail, get_request_data, \    refresh_video, get_app_listfrom ai_strategy_request_func import ai_strategy_request_parselog_formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s', '%m/%d/%Y %I:%M:%S %p')log_handler = ConcurrentTimedRotatingFileHandler("logs/ai_auto_create.log", when="midnight", backupCount=100)log_handler.setFormatter(log_formatter)logger = logging.getLogger('ai_time_task_creative_logger')logger.addHandler(log_handler)logger.setLevel(logging.DEBUG)print('id of ai_time_task_creative_logger %s' % id(logger))logger.info("ai_time_task_creative_server started!")class AiHistoricalMissingMaterial(tornado.web.RequestHandler):    """    补充历史遗漏素材(关联创意个数为0个素材)    """    def post(self):        data = self.request.body        data = str(data, 'utf8')        data = json.loads(data, encoding='utf8')        logger.info("***************************************  NEW REQUEST ***************************************")        account_id = data.get('account_id')        campaign_id = data.get('campaign_id')        logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s,  raw data from request is %s" %                    (account_id, os.getpid(), os.getppid(), data))        try:            sql = """            select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy            where account_id=%s  order by create_time desc limit 1            """ % account_id            df = pd.read_sql(sql, engine)            if df['general_track'].values[0] == 0:                app_id_array = eval(df['app_id_array'].values[0])                app_list = get_app_list(account_id=account_id, url=get_app_list_url)                # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息                app_dict = {}                for app_info in app_list['data']:                    if app_info['appId'] in app_id_array:                        app_dict[app_info['appVersion']] = app_info                video_list = get_missing_video_info(account_id=account_id,                                                    cnt=missing_video_cnt,                                                    url=get_missing_video_url,                                                    start_time=missing_video_start_time,                                                    end_time=missing_video_end_time)                # 返回视频中有通用视频和特定视频,需要分开处理                # "photo_name": "all",                # "photo_name": "ksjytdrca-糖豆人冲啊",                if video_list['code'] == 0:                    video_df = pd.DataFrame(video_list['data'])                    # 1-非通用视频的创建                    special_video_df = video_df[video_df.photo_name != 'all']                    for photo_name in special_video_df['photo_name'].unique():                        if photo_name in app_dict.keys():                            campaign_id = None                            app_id = app_dict[photo_name].get('appId', 'error')                            app_version = app_dict[photo_name].get('appVersion', 'error')                            track_url = app_dict[photo_name].get('trackUrl', 'error')                            sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                            campaign_df = pd.read_sql(sql, engine)                            for row in campaign_df.itertuples():                                if (app_version + '--优选广告位-' + '遗漏素材') == getattr(row, 'campaign_name'):                                    campaign_id = getattr(row, 'campaign_id')                                    break                            app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]                            app_id_video_list = []                            for k, v in app_id_video_df.T.to_dict().items():                                app_id_video_list.append(v)                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=app_id_video_list,                                                            campaign_name=app_version + '--优选广告位-' + '遗漏素材',                                                            group_name=app_version + '-优选广告位-不限-自定义-' + '遗漏素材',                                                            ai_strategy_remark="补充遗漏素材",                                                            unit_type=4,                                                            app_id=app_id,                                                            click_track_url=track_url)                            request = ai_strategy_request_parse(request_data)                            logger.info("account_id=%s, app_id=%s, 补充遗漏素材-特定视频,ai策略的返回信息:%s" % (account_id, app_id, request))                    # 2-通用视频的创建,random.shuffle 选择 app_id                    general_video_df = video_df[video_df.photo_name == 'all']                    if not general_video_df.empty:                        general_video_cnt = len(general_video_df)                        app_version_list = list(app_dict)                        random.shuffle(app_version_list)                        video_cnt = 0                        for app_version in app_version_list[:general_video_cnt]:                            campaign_id = None                            app_id = app_dict[app_version].get('appId', 'error')                            track_url = app_dict[app_version].get('trackUrl', 'error')                            sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                            campaign_df = pd.read_sql(sql, engine)                            for row in campaign_df.itertuples():                                if (app_version + '--优选广告位-' + '遗漏素材') == getattr(row, 'campaign_name'):                                    campaign_id = getattr(row, 'campaign_id')                                    break                            single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=single_video,                                                            campaign_name=app_version + '--优选广告位-' + '遗漏素材',                                                            group_name=app_version + '-优选广告位-不限-自定义-' + '遗漏素材',                                                            ai_strategy_remark="补充遗漏素材",                                                            unit_type=4,                                                            app_id=app_id,                                                            click_track_url=track_url)                            request = ai_strategy_request_parse(request_data)                            video_cnt += 1                            logger.info("account_id=%s, app_id=%s, 补充遗漏素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))                    else:                        logger.info("account_id=%s, 补充遗漏素材: 返回的视频中没有通用视频" % account_id)                self.write(json.dumps({"message": "account_id=%s, 多应用的补充遗漏素材请求已走完!" % account_id}))                self.flush()            else:                video_info = get_missing_video_info(account_id=account_id,                                                    cnt=missing_video_cnt,                                                    url=get_missing_video_url,                                                    start_time=missing_video_start_time,                                                    end_time=missing_video_end_time)                if video_info['code'] != 0:                    logger.info("account_id = %s,补充历史遗漏素材,没有获取到视频,不发送ai策略请求!" % account_id)                    self.write(json.dumps({"account_id": account_id,                                           "message": "补充历史遗漏素材,没有获取到视频,不发送ai策略请求!"}))                    self.flush()                else:                    campaign_name, group_name = get_campaign_and_group_name_rule(account_id)                    if (campaign_name is None) or (group_name is None):                        logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")                        self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))                        self.flush()                    else:                        request_data = get_request_data(account_id=account_id,                                                        campaign_id=campaign_id,                                                        video_info=video_info['data'],                                                        campaign_name=campaign_name,                                                        group_name=group_name,                                                        ai_strategy_remark="补充遗漏素材",                                                        name_replace="补充遗漏素材")                        request = ai_strategy_request_parse(request_data)                        logger.info("account_id = %s, 补充遗漏素材,ai策略的返回信息:%s" % (account_id, request))                        self.write(json.dumps(request))                        self.flush()        except Exception:            logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))            self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))            self.flush()class AiAutoCreative(tornado.web.RequestHandler):    """    每5分钟,检查一次是否有上新素材,有的话,就自动创建    """    def post(self):        data = self.request.body        data = str(data, 'utf8')        data = json.loads(data, encoding='utf8')        logger.info("***************************************  NEW REQUEST ***************************************")        account_id = data.get('account_id')        campaign_id = data.get('campaign_id')        logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s,  raw data from request is %s" %                    (account_id, os.getpid(), os.getppid(), data))        try:            # 刷新快手视频信息            refresh_video(account_id=account_id, url=refresh_video_url)            sql = """                        select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy                        where account_id=%s order by create_time desc limit 1                        """ % account_id            df = pd.read_sql(sql, engine)            if df['general_track'].values[0] == 0:                app_id_array = eval(df['app_id_array'].values[0])                app_list = get_app_list(account_id=account_id, url=get_app_list_url)                # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息                app_dict = {}                for app_info in app_list['data']:                    if app_info['appId'] in app_id_array:                        app_dict[app_info['appVersion']] = app_info                video_list = get_new_video_info(account_id=account_id,                                                url=get_new_video_url)                # 返回的视频中有通用视频和特定视频,需要分开处理                # "photo_name": "all",                # "photo_name": "ksjytdrca-糖豆人冲啊",                if video_list['code'] == 0:                    video_df = pd.DataFrame(video_list['data'])                    # 1-特定视频的创建                    special_video_df = video_df[video_df.photo_name != 'all']                    for photo_name in special_video_df['photo_name'].unique():                        if photo_name in app_dict.keys():                            campaign_id = None                            app_id = app_dict[photo_name].get('appId', 'error')                            app_version = app_dict[photo_name].get('appVersion', 'error')                            track_url = app_dict[photo_name].get('trackUrl', 'error')                            sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                            campaign_df = pd.read_sql(sql, engine)                            for row in campaign_df.itertuples():                                if (app_version + '--优选广告位-' + '素材自动上新') == getattr(row, 'campaign_name'):                                    campaign_id = getattr(row, 'campaign_id')                                    break                            app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]                            app_id_video_list = []                            for k, v in app_id_video_df.T.to_dict().items():                                app_id_video_list.append(v)                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=app_id_video_list,                                                            campaign_name=app_version + '--优选广告位-' + '素材自动上新',                                                            group_name=app_version + '-优选广告位-不限-自定义-' + '素材自动上新',                                                            ai_strategy_remark="素材自动上新",                                                            unit_type=4,                                                            app_id=app_id,                                                            click_track_url=track_url)                            request = ai_strategy_request_parse(request_data)                            logger.info("account_id=%s, app_id=%s, 素材自动上新-特定视频,ai策略的返回信息:%s" % (account_id, app_id, request))                    # 2-通用视频的创建,random.shuffle 选择 app_id                    general_video_df = video_df[video_df.photo_name == 'all']                    if not general_video_df.empty:                        general_video_cnt = len(general_video_df)                        app_version_list = list(app_dict)                        random.shuffle(app_version_list)                        video_cnt = 0                        for app_version in app_version_list[:general_video_cnt]:                            campaign_id = None                            app_id = app_dict[app_version].get('appId', 'error')                            track_url = app_dict[app_version].get('trackUrl', 'error')                            sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                            campaign_df = pd.read_sql(sql, engine)                            for row in campaign_df.itertuples():                                if (app_version + '--优选广告位-' + '素材自动上新') == getattr(row, 'campaign_name'):                                    campaign_id = getattr(row, 'campaign_id')                                    break                            single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=single_video,                                                            campaign_name=app_version + '--优选广告位-' + '素材自动上新',                                                            group_name=app_version + '-优选广告位-不限-自定义-' + '素材自动上新',                                                            ai_strategy_remark="素材自动上新",                                                            unit_type=4,                                                            app_id=app_id,                                                            click_track_url=track_url)                            request = ai_strategy_request_parse(request_data)                            video_cnt += 1                            logger.info("account_id=%s, app_id=%s, 素材自动上新-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))                    else:                        logger.info("account_id=%s, 素材自动上新: 返回的视频中没有通用视频" % account_id)                else:                    logger.info("account_id=%s, 素材自动上新: 没有获取到视频" % account_id)                self.write(json.dumps({"message": "account_id=%s, 多应用的素材自动上新已走完!" % account_id}))                self.flush()            else:                start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')                end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')                # 1-1 获取当天计划中包含“自动上新”计划的,计划id                sql = """                                    select campaign_id, campaign_name from ctop_kuaishou_campaign                                     where account_id = %s                                    and put_create_time >= '%s'                                    and put_create_time <= '%s'                                    """ % (account_id, start_time, end_time)                df = pd.read_sql(sql, engine)                for row in df.itertuples():                    if '素材自动上新' in getattr(row, 'campaign_name'):                        campaign_id = getattr(row, 'campaign_id')                        break                video_info = get_new_video_info(account_id=account_id,                                                url=get_new_video_url)                if video_info['code'] != 0:                    self.write(json.dumps({"account_id": account_id, "message": "素材自动上新没有获取到视频,不发送ai策略请求!"}))                    self.flush()                else:                    campaign_name, group_name = get_campaign_and_group_name_rule(account_id)                    if (campaign_name is None) or (group_name is None):                        logger.info("account_id = %s, 素材自动上新 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"                                    % account_id)                        self.write(json.dumps({"account_id": account_id,                                               "message": "素材自动上新 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))                        self.flush()                    else:                        request_data = get_request_data(account_id=account_id,                                                        campaign_id=campaign_id,                                                        video_info=video_info['data'],                                                        campaign_name=campaign_name,                                                        group_name=group_name,                                                        ai_strategy_remark="自动上新",                                                        name_replace="素材自动上新")                        request = ai_strategy_request_parse(request_data)                        logger.info("account_id = %s, 素材自动上新 ai策略的返回信息:%s" % (account_id, request))                        self.write(json.dumps(request))                        self.flush()        except Exception:            logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))            self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))            self.flush()class AiHighQualityMaterial(tornado.web.RequestHandler):    """    补充历史高质量素材  -- 测试通过 在AD后台创建成功,检查AD信息和数据库表 没有问题    """    def post(self):        data = self.request.body        data = str(data, 'utf8')        data = json.loads(data, encoding='utf8')        logger.info("***************************************  NEW REQUEST ***************************************")        account_id = data.get('account_id')        campaign_id = data.get('campaign_id')        logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s,  raw data from request is %s" %                    (account_id, os.getpid(), os.getppid(), data))        try:            sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy                     where account_id=%s  order by create_time desc limit 1                     """ % account_id            df = pd.read_sql(sql, engine)            if df['general_track'].values[0] == 0:                app_id_array = eval(df['app_id_array'].values[0])                app_list = get_app_list(account_id=account_id, url=get_app_list_url)                # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息                app_dict = {}                for app_info in app_list['data']:                    if app_info['appId'] in app_id_array:                        app_dict[app_info['appVersion']] = app_info                video_list = get_top_video_info(account_id=account_id,                                                cnt=high_quality_video_cnt,                                                start_time=(datetime.datetime.now() +                                                            datetime.timedelta(days=-high_quality_video_days)).                                                strftime("%Y-%m-%d %H:%M:%S"),                                                end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).                                                strftime("%Y-%m-%d %H:%M:%S"),                                                url=get_high_quality_video_url)                # 单独处理返回的时候中,有通用视频,需要单独处理                # "photo_name": "all",                # "photo_name": "ksjytdrca-糖豆人冲啊",                if video_list['code'] == 0:                    video_df = pd.DataFrame(video_list['data'])                    # 1-特定视频的创建                    special_video_df = video_df[video_df.photo_name != 'all']                    for photo_name in special_video_df['photo_name'].unique():                        if photo_name in app_dict.keys():                            campaign_id = None                            app_id = app_dict[photo_name].get('appId', 'error')                            app_version = app_dict[photo_name].get('appVersion', 'error')                            track_url = app_dict[photo_name].get('trackUrl', 'error')                            sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                            campaign_df = pd.read_sql(sql, engine)                            for row in campaign_df.itertuples():                                if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):                                    campaign_id = getattr(row, 'campaign_id')                                    break                            app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]                            app_id_video_list = []                            for k, v in app_id_video_df.T.to_dict().items():                                app_id_video_list.append(v)                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=app_id_video_list,                                                            campaign_name=app_version + '--优选广告位-' + '高质量素材',                                                            group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',                                                            ai_strategy_remark="高质量素材复建",                                                            unit_type=4,                                                            app_id=app_id,                                                            click_track_url=track_url)                            request = ai_strategy_request_parse(request_data)                            logger.info("account_id=%s, app_id=%s, 高质量素材复建-特定视频,ai策略的返回信息:%s" % (account_id, app_id, request))                    # 2-通用视频的创建,random.shuffle 选择 app_id                    general_video_df = video_df[video_df.photo_name == 'all']                    if not general_video_df.empty:                        general_video_cnt = len(general_video_df)                        app_version_list = list(app_dict)                        random.shuffle(app_version_list)                        video_cnt = 0                        for app_version in app_version_list[:general_video_cnt]:                            campaign_id = None                            app_id = app_dict[app_version].get('appId', 'error')                            track_url = app_dict[app_version].get('trackUrl', 'error')                            sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                            campaign_df = pd.read_sql(sql, engine)                            for row in campaign_df.itertuples():                                if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):                                    campaign_id = getattr(row, 'campaign_id')                                    break                            single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=single_video,                                                            campaign_name=app_version + '--优选广告位-' + '高质量素材',                                                            group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',                                                            ai_strategy_remark="高质量素材复建",                                                            unit_type=4,                                                            app_id=app_id,                                                            click_track_url=track_url)                            request = ai_strategy_request_parse(request_data)                            video_cnt += 1                            logger.info("account_id=%s, app_id=%s, 高质量素材复建-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))                    else:                        logger.info("account_id=%s, 高质量素材复建: 返回的视频中没有通用视频" % account_id)                else:                    logger.info("account_id=%s, 高质量素材复建: 没有获取到视频" % account_id)                self.write(json.dumps({"message": "account_id=%s, 多应用的高质量素材复建请求已走完!" % account_id}))                self.flush()            else:                video_info = get_top_video_info(account_id=account_id,                                                cnt=high_quality_video_cnt,                                                start_time=(datetime.datetime.now() +                                                            datetime.timedelta(days=-high_quality_video_days)).                                                strftime("%Y-%m-%d %H:%M:%S"),                                                end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).                                                strftime("%Y-%m-%d %H:%M:%S"),                                                url=get_high_quality_video_url)                if video_info['code'] != 0:                    logger.info("account_id = %s, 高质量素材复建,没有获取到视频,不发送ai策略请求!" % account_id)                    self.write(json.dumps({"account_id": account_id,                                           "message": "高质量素材复建,没有获取到视频,不发送ai策略请求!"}))                    self.flush()                else:                    campaign_name, group_name = get_campaign_and_group_name_rule(account_id)                    if (campaign_name is None) or (group_name is None):                        logger.error("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)                        self.write(json.dumps({"account_id": account_id,                                               "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))                        self.flush()                    else:                        request_data = get_request_data(account_id=account_id,                                                        campaign_id=campaign_id,                                                        video_info=video_info['data'],                                                        campaign_name=campaign_name,                                                        group_name=group_name,                                                        ai_strategy_remark="高质量素材复建",                                                        name_replace="高质量素材")                        request = ai_strategy_request_parse(request_data)                        logger.info("account_id = %s, 高质量素材复建,ai策略的返回信息:%s" % (account_id, request))                        self.write(json.dumps(request))                        self.flush()        except Exception:            logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))            self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))            self.flush()class AiCheckAndUpTOFullCreative(tornado.web.RequestHandler):    """    每天22:00 调用该任务,检查当天创意是否创建达到上限,否则使用历史高质量素材填满    """    def post(self):        data = self.request.body        data = str(data, 'utf8')        data = json.loads(data, encoding='utf8')        logger.info("***************************************  NEW REQUEST ***************************************")        account_id = data.get('account_id')        campaign_id = data.get('campaign_id')        logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s,  raw data from request is %s" %                    (account_id, os.getpid(), os.getppid(), data))        try:            # 1-1 从客户策略表中获取该账户下 每个素材搭配的封面个数, 来计算需要补充的素材个数            sql = """select image_cnt from ctop_ai_kuaishou_advertiser_strategy                         where account_id = %s                        limit 1""" % account_id            df = pd.read_sql(sql, engine)            image_cnt = int(df['image_cnt'].values[0])            # 1-2、获取从0点到now()为止,该账户下已有的创意个数 M            sql = """select count(1) creative_cnt from ctop_kuaishou_creative                      where account_id = %s                     and creative_id is not NULL                     and creative_create_time <= '%s'                     and creative_create_time >= '%s'""" % (account_id,                                                            (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S'),                                                            (datetime.datetime.now()).strftime('%Y-%m-%d'))            df = pd.read_sql(sql, engine)            exist_creative_cnt = int(df['creative_cnt'].values[0])            now_hour = datetime.datetime.now().hour            now = datetime.datetime.now()            should_creative_cnt = now_hour * 100            if exist_creative_cnt < should_creative_cnt:                need_video_cnt = (should_creative_cnt - exist_creative_cnt) // image_cnt                logger.info("account_id = %s, 按小时需要补充的视频数量 = %s" % (account_id, need_video_cnt))                if now_hour % 5 == 0:                    video_list = get_top_video_info(account_id=account_id,                                                    url=get_high_quality_video_url,                                                    start_time=(now + datetime.timedelta(days=-2 * high_quality_video_days)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    end_time=(now + datetime.timedelta(days=-high_quality_video_days)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    cnt=need_video_cnt,                                                    app_version=None)                else:                    video_list = get_history_video_info(account_id=account_id,                                                        url=get_history_video_url,                                                        start_time=history_video_start_time,                                                        end_time=history_video_end_time,                                                        video_cnt=need_video_cnt,                                                        related_creative_max_cnt=related_creative_max_cnt)                # 根据获取到的视频进行创建                sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy                         where account_id=%s order by create_time desc limit 1""" % account_id                df = pd.read_sql(sql, engine)                if df['general_track'].values[0] == 0:                    app_id_array = eval(df['app_id_array'].values[0])                    app_list = get_app_list(account_id=account_id, url=get_app_list_url)                    # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息                    app_dict = {}                    for app_info in app_list['data']:                        if app_info['appId'] in app_id_array:                            app_dict[app_info['appVersion']] = app_info                    # 单独处理返回的时候中,有通用视频,需要单独处理                    # "photo_name": "all",                    # "photo_name": "ksjytdrca-糖豆人冲啊",                    if video_list['code'] == 0:                        video_df = pd.DataFrame(video_list['data'])                        # 1-特定视频的创建                        special_video_df = video_df[video_df.photo_name != 'all']                        for photo_name in special_video_df['photo_name'].unique():                            if photo_name in app_dict.keys():                                campaign_id = None                                app_id = app_dict[photo_name].get('appId', 'error')                                app_version = app_dict[photo_name].get('appVersion', 'error')                                track_url = app_dict[photo_name].get('trackUrl', 'error')                                sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                                campaign_df = pd.read_sql(sql, engine)                                for row in campaign_df.itertuples():                                    if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):                                        campaign_id = getattr(row, 'campaign_id')                                        break                                app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]                                app_id_video_list = []                                for k, v in app_id_video_df.T.to_dict().items():                                    app_id_video_list.append(v)                                request_data = get_request_data(account_id=account_id,                                                                campaign_id=campaign_id,                                                                video_info=app_id_video_list,                                                                campaign_name=app_version + '--优选广告位-' + '高质量素材',                                                                group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',                                                                ai_strategy_remark="按小时补满创意",                                                                unit_type=4,                                                                app_id=app_id,                                                                click_track_url=track_url)                                request = ai_strategy_request_parse(request_data)                                logger.info("account_id=%s, app_id=%s, 用高质量素材补满创意-特定视频,ai策略的返回信息:%s" %                                            (account_id, app_id, request))                        # 2-通用视频的创建,random.shuffle 选择 app_id                        general_video_df = video_df[video_df.photo_name == 'all']                        if not general_video_df.empty:                            general_video_cnt = len(general_video_df)                            app_version_list = list(app_dict)                            random.shuffle(app_version_list)                            video_cnt = 0                            for app_version in app_version_list[:general_video_cnt]:                                campaign_id = None                                app_id = app_dict[app_version].get('appId', 'error')                                track_url = app_dict[app_version].get('trackUrl', 'error')                                sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                                campaign_df = pd.read_sql(sql, engine)                                for row in campaign_df.itertuples():                                    if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):                                        campaign_id = getattr(row, 'campaign_id')                                        break                                single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]                                request_data = get_request_data(account_id=account_id,                                                                campaign_id=campaign_id,                                                                video_info=single_video,                                                                campaign_name=app_version + '--优选广告位-' + '高质量素材',                                                                group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',                                                                ai_strategy_remark="用高质量素材补满创意",                                                                unit_type=4,                                                                app_id=app_id,                                                                click_track_url=track_url)                                request = ai_strategy_request_parse(request_data)                                video_cnt += 1                                logger.info("account_id=%s, app_id=%s, 用高质量素材补满创意-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))                        else:                            logger.info("account_id=%s, 用高质量素材补满创意: 返回的视频中没有通用视频" % account_id)                        self.write(json.dumps({"message": "account_id=%s, 用高质量素材补满创意请求已走完!" % account_id}))                        self.flush()                    else:                        logger.info("account_id=%s, 按小时补满创意: 没有成功获取到视频!" % account_id)                        self.write(json.dumps({"message": "account_id=%s, 按小时补满创意: 没有成功获取到视频!" % account_id}))                        self.flush()                else:                    # 1-1 获取当天计划中包含“高质量素材”计划的,计划id                    sql = """                                                       select campaign_id, campaign_name from ctop_kuaishou_campaign                                                        where account_id = %s                                                       and put_create_time >= '%s'                                                       and put_create_time <= '%s'                                                       """ % (account_id, start_time, end_time)                    df = pd.read_sql(sql, engine)                    for row in df.itertuples():                        if '高质量素材' in getattr(row, 'campaign_name'):                            campaign_id = getattr(row, 'campaign_id')                            break                    video_info = get_top_video_info(account_id=account_id,                                                    cnt=video_cnt,                                                    start_time=(now + datetime.timedelta(days=-2 * high_quality_video_days)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    end_time=(now + datetime.timedelta(days=-high_quality_video_days)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    url=get_high_quality_video_url)                    if video_info['code'] != 0:                        logger.info("account_id = %s, 历史高质量素材填满, 没有获取到视频,不发送ai策略请求!" % account_id)                        self.write(json.dumps({"account_id": account_id,                                               "message": "历史高质量素材填满, 没有获取到视频,不发送ai策略请求!"}))                        self.flush()                    else:                        campaign_name, group_name = get_campaign_and_group_name_rule(account_id)                        if (campaign_name is None) or (group_name is None):                            logger.info("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)                            self.write(json.dumps({"account_id": account_id,                                                   "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))                            self.flush()                        else:                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=video_info['data'],                                                            campaign_name=campaign_name,                                                            group_name=group_name,                                                            ai_strategy_remark="用高质量素材补满创意",                                                            name_replace="高质量素材")                            request = ai_strategy_request_parse(request_data)                            logger.info("account_id = %s, 用高质量素材补满创意,ai策略的返回信息:%s" % (account_id, request))                            self.write(json.dumps(request))                            self.flush()            else:                logger.info("account_id = %s, 创意已经建满,不需要补充素材!" % account_id)                self.write(json.dumps({"account_id": account_id, "message": "创意已经建满,不需要补充素材!"}))                self.flush()            # 2、如果创意个数M 小于 hour()*100, 则使用 (hour()*100 - M)// image_cnt 个视频            # 2、datetime.datetime.now().hour % 5, 如果不等于0, 则使用创意关联数少的视频,否则使用历史跑量视频            start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')            end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')            now = datetime.datetime.now()            # 1-1 从客户策略表中获取该账户下 每个素材搭配的封面个数, 来计算需要补充的素材个数            sql = """            select image_cnt from ctop_ai_kuaishou_advertiser_strategy             where account_id = %s            limit 1            """ % account_id            df = pd.read_sql(sql, engine)            image_cnt = int(df['image_cnt'].values[0])            # 2-1 从 ctop_kuaishou_creative 获取当天该账户到此刻 已经创建的创意个数            video_cnt = 0            sql = """                   select count(1) creative_cnt from ctop_kuaishou_creative                    where account_id = %s                   and creative_id is not NULL                   and creative_create_time <= '%s'                   and creative_create_time >= '%s'                   """ % (account_id, end_time, start_time)            df = pd.read_sql(sql, engine)            creative_cnt = int(df['creative_cnt'].values[0])            if creative_cnt >= 2000:                video_cnt = 0            else:                video_cnt = (2000 - creative_cnt) // image_cnt            # 3-1 获取视频信息            if video_cnt == 0:                logger.info("account_id = %s, 创意已经建满,不需要补充素材!" % account_id)                self.write(json.dumps({"account_id": account_id, "message": "创意已经建满,不需要补充素材!"}))                self.flush()            else:                logger.info("account_id = %s, 需要补充的视频数量 = %s" % (account_id, video_cnt))                sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy                                 where account_id=%s order by create_time desc limit 1                              """ % account_id                df = pd.read_sql(sql, engine)                if df['general_track'].values[0] == 0:                    app_id_array = eval(df['app_id_array'].values[0])                    app_list = get_app_list(account_id=account_id, url=get_app_list_url)                    # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息                    app_dict = {}                    for app_info in app_list['data']:                        if app_info['appId'] in app_id_array:                            app_dict[app_info['appVersion']] = app_info                    video_list = get_top_video_info(account_id=account_id,                                                    url=get_high_quality_video_url,                                                    start_time=(now + datetime.timedelta(days=-2 * high_quality_video_days)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    end_time=(now + datetime.timedelta(days=-high_quality_video_days)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    cnt=video_cnt,                                                    app_version=None)                    # 单独处理返回的时候中,有通用视频,需要单独处理                    # "photo_name": "all",                    # "photo_name": "ksjytdrca-糖豆人冲啊",                    if video_list['code'] == 0:                        video_df = pd.DataFrame(video_list['data'])                        # 1-特定视频的创建                        special_video_df = video_df[video_df.photo_name != 'all']                        for photo_name in special_video_df['photo_name'].unique():                            if photo_name in app_dict.keys():                                campaign_id = None                                app_id = app_dict[photo_name].get('appId', 'error')                                app_version = app_dict[photo_name].get('appVersion', 'error')                                track_url = app_dict[photo_name].get('trackUrl', 'error')                                sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                                campaign_df = pd.read_sql(sql, engine)                                for row in campaign_df.itertuples():                                    if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):                                        campaign_id = getattr(row, 'campaign_id')                                        break                                app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]                                app_id_video_list = []                                for k, v in app_id_video_df.T.to_dict().items():                                    app_id_video_list.append(v)                                request_data = get_request_data(account_id=account_id,                                                                campaign_id=campaign_id,                                                                video_info=app_id_video_list,                                                                campaign_name=app_version + '--优选广告位-' + '高质量素材',                                                                group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',                                                                ai_strategy_remark="用高质量素材补满创意",                                                                unit_type=4,                                                                app_id=app_id,                                                                click_track_url=track_url)                                request = ai_strategy_request_parse(request_data)                                logger.info("account_id=%s, app_id=%s, 用高质量素材补满创意-特定视频,ai策略的返回信息:%s" %                                            (account_id, app_id, request))                        # 2-通用视频的创建,random.shuffle 选择 app_id                        general_video_df = video_df[video_df.photo_name == 'all']                        if not general_video_df.empty:                            general_video_cnt = len(general_video_df)                            app_version_list = list(app_dict)                            random.shuffle(app_version_list)                            video_cnt = 0                            for app_version in app_version_list[:general_video_cnt]:                                campaign_id = None                                app_id = app_dict[app_version].get('appId', 'error')                                track_url = app_dict[app_version].get('trackUrl', 'error')                                sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                                campaign_df = pd.read_sql(sql, engine)                                for row in campaign_df.itertuples():                                    if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):                                        campaign_id = getattr(row, 'campaign_id')                                        break                                single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]                                request_data = get_request_data(account_id=account_id,                                                                campaign_id=campaign_id,                                                                video_info=single_video,                                                                campaign_name=app_version + '--优选广告位-' + '高质量素材',                                                                group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',                                                                ai_strategy_remark="用高质量素材补满创意",                                                                unit_type=4,                                                                app_id=app_id,                                                                click_track_url=track_url)                                request = ai_strategy_request_parse(request_data)                                video_cnt += 1                                logger.info("account_id=%s, app_id=%s, 用高质量素材补满创意-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))                        else:                            logger.info("account_id=%s, 用高质量素材补满创意: 返回的视频中没有通用视频" % account_id)                        self.write(json.dumps({"message": "account_id=%s, 用高质量素材补满创意请求已走完!" % account_id}))                        self.flush()                else:                    # 1-1 获取当天计划中包含“高质量素材”计划的,计划id                    sql = """                                       select campaign_id, campaign_name from ctop_kuaishou_campaign                                        where account_id = %s                                       and put_create_time >= '%s'                                       and put_create_time <= '%s'                                       """ % (account_id, start_time, end_time)                    df = pd.read_sql(sql, engine)                    for row in df.itertuples():                        if '高质量素材' in getattr(row, 'campaign_name'):                            campaign_id = getattr(row, 'campaign_id')                            break                    video_info = get_top_video_info(account_id=account_id,                                                    cnt=video_cnt,                                                    start_time=(now + datetime.timedelta(days=-2 * high_quality_video_days)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    end_time=(now + datetime.timedelta(days=-high_quality_video_days)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    url=get_high_quality_video_url)                    if video_info['code'] != 0:                        logger.info("account_id = %s, 历史高质量素材填满, 没有获取到视频,不发送ai策略请求!" % account_id)                        self.write(json.dumps({"account_id": account_id,                                               "message": "历史高质量素材填满, 没有获取到视频,不发送ai策略请求!"}))                        self.flush()                    else:                        campaign_name, group_name = get_campaign_and_group_name_rule(account_id)                        if (campaign_name is None) or (group_name is None):                            logger.info("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)                            self.write(json.dumps({"account_id": account_id,                                                   "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))                            self.flush()                        else:                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=video_info['data'],                                                            campaign_name=campaign_name,                                                            group_name=group_name,                                                            ai_strategy_remark="用高质量素材补满创意",                                                            name_replace="高质量素材")                            request = ai_strategy_request_parse(request_data)                            logger.info("account_id = %s, 用高质量素材补满创意,ai策略的返回信息:%s" % (account_id, request))                            self.write(json.dumps(request))                            self.flush()        except Exception:            logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))            self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))            self.flush()class AiProgramCreativeHighQualityMaterial(tornado.web.RequestHandler):    """    使用历史跑量素材创建程序化创意    跑量素材150个(近14天的素材)    计划名称--跑量素材程序化    timeTask: 每天上午10点执行 --(0 0 10 * * ?)    ----------------------------------------    拼装参数和写入数据库时,需要修改名称(程序化与非程序化不一致):    creative_name  --> package_name(程序化创意名称)    action_bar_text --> action_bar(行动号召按钮)    description --> captions(作品广告语)    click_track_url --> click_url    写入数据库时:    image_md5s --> cover_image_tokens    程序化与非程序化的逻辑不一样:    需要5个视频4张封面一组,拼装程序化创意参数需要单独抽取一个方法出来    所有操作记录表中,添加status信息(已提交,成功,失败),和回调时的 message信息    """    def post(self):        data = self.request.body        data = str(data, 'utf8')        data = json.loads(data, encoding='utf8')        logger.info("***************************************  NEW REQUEST ***************************************")        account_id = data.get('account_id')        campaign_id = data.get('campaign_id')        logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s,  raw data from request is %s" %                    (account_id, os.getpid(), os.getppid(), data))        try:            sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy                     where account_id=%s  order by create_time desc limit 1                  """ % account_id            df = pd.read_sql(sql, engine)            if df['general_track'].values[0] == 0:                app_id_array = eval(df['app_id_array'].values[0])                app_list = get_app_list(account_id=account_id, url=get_app_list_url)                # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息                app_dict = {}                for app_info in app_list['data']:                    if app_info['appId'] in app_id_array:                        app_dict[app_info['appVersion']] = app_info                video_list = get_top_video_info(account_id=account_id,                                                cnt=multi_app_id_programme_high_quality_video_cnt,                                                start_time=(datetime.datetime.now() + datetime.timedelta(                                                    days=-high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),                                                end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).                                                strftime("%Y-%m-%d %H:%M:%S"),                                                url=get_high_quality_video_url)                # 单独处理返回的时候中,有通用视频,需要单独处理                # "photo_name": "all",                # "photo_name": "ksjytdrca-糖豆人冲啊",                if video_list['code'] == 0:                    video_df = pd.DataFrame(video_list['data'])                    # 1-特定视频的创建                    special_video_df = video_df[video_df.photo_name != 'all']                    for photo_name in special_video_df['photo_name'].unique():                        if photo_name in app_dict.keys():                            campaign_id = None                            app_id = app_dict[photo_name].get('appId', 'error')                            app_version = app_dict[photo_name].get('appVersion', 'error')                            track_url = app_dict[photo_name].get('trackUrl', 'error')                            sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                            campaign_df = pd.read_sql(sql, engine)                            for row in campaign_df.itertuples():                                if (app_version + '--优选广告位-' + '程序化高质量素材') == getattr(row, 'campaign_name'):                                    campaign_id = getattr(row, 'campaign_id')                                    break                            app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]                            app_id_video_list = []                            for k, v in app_id_video_df.T.to_dict().items():                                app_id_video_list.append(v)                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=app_id_video_list,                                                            campaign_name=app_version + '--优选广告位-' + '程序化高质量素材',                                                            group_name=app_version + '-优选广告位-不限-程序化-' + '高质量素材',                                                            ai_strategy_remark="程序化高质量素材",                                                            unit_type=7,                                                            app_id=app_id,                                                            click_track_url=track_url)                            request = ai_strategy_request_parse(request_data)                            logger.info("account_id=%s, app_id=%s, 程序化高质量素材-特定视频,ai策略的返回信息:%s" %                                        (account_id, app_id, request))                    # 2-通用视频的创建,random.shuffle 选择 app_id                    general_video_df = video_df[video_df.photo_name == 'all']                    general_video_list = []                    for k, v in general_video_df.T.to_dict().items():                        general_video_list.append(v)                    if general_video_list:                        app_version_list = list(app_dict)                        random.shuffle(app_version_list)                        # 每5个通用视频创建一个程序化广告组                        batch_num = 5                        app_id_cnt = 0                        for i in range(0, len(general_video_list), 5):                            if i + 5 < len(general_video_list):                                batch_general_video = general_video_list[i: i + batch_num]                                app_version = app_version_list[app_id_cnt]                                app_id = app_dict[app_version].get('appId', 'error')                                track_url = app_dict[app_version].get('trackUrl', 'error')                                sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                                campaign_df = pd.read_sql(sql, engine)                                for row in campaign_df.itertuples():                                    if (app_version + '--优选广告位-' + '程序化高质量素材') == getattr(row, 'campaign_name'):                                        campaign_id = getattr(row, 'campaign_id')                                        break                                request_data = get_request_data(account_id=account_id,                                                                campaign_id=campaign_id,                                                                video_info=batch_general_video,                                                                campaign_name=app_version + '--优选广告位-' + '程序化高质量素材',                                                                group_name=app_version + '-优选广告位-不限-程序化-' + '高质量素材',                                                                ai_strategy_remark="程序化高质量素材",                                                                unit_type=7,                                                                app_id=app_id,                                                                click_track_url=track_url)                                request = ai_strategy_request_parse(request_data)                                app_id_cnt += 1                                logger.info("account_id=%s, app_id=%s, 程序化高质量素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))                            else:                                batch_general_video = general_video_list[i: len(general_video_list)]                                app_version = app_version_list[app_id_cnt]                                app_id = app_dict[app_version].get('appId', 'error')                                track_url = app_dict[app_version].get('trackUrl', 'error')                                sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                                campaign_df = pd.read_sql(sql, engine)                                for row in campaign_df.itertuples():                                    if (app_version + '--优选广告位-' + '程序化高质量素材') == getattr(row, 'campaign_name'):                                        campaign_id = getattr(row, 'campaign_id')                                        break                                request_data = get_request_data(account_id=account_id,                                                                campaign_id=campaign_id,                                                                video_info=batch_general_video,                                                                campaign_name=app_version + '--优选广告位-' + '程序化高质量素材',                                                                group_name=app_version + '-优选广告位-不限-程序化-' + '高质量素材',                                                                ai_strategy_remark="程序化高质量素材",                                                                unit_type=7,                                                                app_id=app_id,                                                                click_track_url=track_url)                                request = ai_strategy_request_parse(request_data)                                logger.info("account_id=%s, app_id=%s, 程序化高质量素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))                    else:                        logger.info("account_id=%s, 程序化高质量素材: 返回的视频中没有通用视频" % account_id)                    self.write(json.dumps({"message": "account_id=%s, 程序化高质量素材请求已走完!" % account_id}))                    self.flush()            else:                video_info = get_top_video_info(account_id=account_id,                                                cnt=programme_high_quality_video_cnt,                                                start_time=(datetime.datetime.now() + datetime.timedelta(                                                    days=-high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),                                                end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).                                                strftime("%Y-%m-%d %H:%M:%S"),                                                url=get_high_quality_video_url)                if video_info['code'] != 0:                    logger.info("account_id = %s, 程序化高质量素材,没有获取到视频,不发送ai策略请求!" % account_id)                    self.write(json.dumps({"account_id": account_id,                                           "message": "没有获取到视频,不发送ai策略请求!"}))                    self.flush()                else:                    campaign_name, group_name = get_campaign_and_group_name_rule(account_id)                    if (campaign_name is None) or (group_name is None):                        logger.info("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)                        self.write(json.dumps({"account_id": account_id,                                               "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))                        self.flush()                    else:                        request_data = get_request_data(account_id=account_id,                                                        campaign_id=campaign_id,                                                        video_info=video_info['data'],                                                        campaign_name=campaign_name,                                                        group_name=group_name,                                                        ai_strategy_remark="程序化高质量素材",                                                        name_replace="程序化高质量素材",                                                        unit_type=7)                        request = ai_strategy_request_parse(request_data)                        logger.info("account_id = %s, 程序化高质量素材, ai策略的返回信息:%s" % (account_id, request))                        self.write(json.dumps(request))                        self.flush()        except Exception:            logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))            self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))            self.flush()class AiProgramCreativeNewMaterial(tornado.web.RequestHandler):    """    使用N天内 创意数关联小于M个(如当天创意关联数小于10的,150个素材)的素材创建程序化创意    计划名称--新素材程序化    timeTask: 每天上午10点执行 --(0 0 10 * * ?)    """    def post(self):        data = self.request.body        data = str(data, 'utf8')        data = json.loads(data, encoding='utf8')        logger.info("***************************************  NEW REQUEST ***************************************")        account_id = data.get('account_id')        campaign_id = data.get('campaign_id')        logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s,  raw data from request is %s" %                    (account_id, os.getpid(), os.getppid(), data))        try:            sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy                     where account_id=%s  order by create_time desc limit 1                  """ % account_id            df = pd.read_sql(sql, engine)            if df['general_track'].values[0] == 0:                app_id_array = eval(df['app_id_array'].values[0])                app_list = get_app_list(account_id=account_id, url=get_app_list_url)                # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息                app_dict = {}                for app_info in app_list['data']:                    if app_info['appId'] in app_id_array:                        app_dict[app_info['appVersion']] = app_info                video_list = get_history_video_info(account_id=account_id,                                                    url=get_history_video_url,                                                    start_time=(datetime.datetime.now() + datetime.timedelta(days=-7)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    video_cnt=multi_app_id_programme_new_video_cnt,                                                    related_creative_max_cnt=related_creative_max_cnt)                # 单独处理返回的时候中,有通用视频,需要单独处理                # "photo_name": "all",                # "photo_name": "ksjytdrca-糖豆人冲啊",                if video_list['code'] == 0:                    video_df = pd.DataFrame(video_list['data'])                    # 1-特定视频的创建                    special_video_df = video_df[video_df.photo_name != 'all']                    for photo_name in special_video_df['photo_name'].unique():                        if photo_name in app_dict.keys():                            campaign_id = None                            app_id = app_dict[photo_name].get('appId', 'error')                            app_version = app_dict[photo_name].get('appVersion', 'error')                            track_url = app_dict[photo_name].get('trackUrl', 'error')                            sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                            campaign_df = pd.read_sql(sql, engine)                            for row in campaign_df.itertuples():                                if (app_version + '--优选广告位-' + '程序化上新素材') == getattr(row, 'campaign_name'):                                    campaign_id = getattr(row, 'campaign_id')                                    break                            app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]                            app_id_video_list = []                            for k, v in app_id_video_df.T.to_dict().items():                                app_id_video_list.append(v)                            request_data = get_request_data(account_id=account_id,                                                            campaign_id=campaign_id,                                                            video_info=app_id_video_list,                                                            campaign_name=app_version + '--优选广告位-' + '程序化上新素材',                                                            group_name=app_version + '-优选广告位-不限-程序化-' + '上新素材',                                                            ai_strategy_remark="程序化上新素材",                                                            unit_type=7,                                                            app_id=app_id,                                                            click_track_url=track_url)                            request = ai_strategy_request_parse(request_data)                            logger.info("account_id=%s, app_id=%s, 程序化上新素材-特定视频,ai策略的返回信息:%s" %                                        (account_id, app_id, request))                    # 2-通用视频的创建,random.shuffle 选择 app_id                    general_video_df = video_df[video_df.photo_name == 'all']                    general_video_list = []                    for k, v in general_video_df.T.to_dict().items():                        general_video_list.append(v)                    if general_video_list:                        app_version_list = list(app_dict)                        random.shuffle(app_version_list)                        # 每5个通用视频创建一个程序化广告组                        batch_num = 5                        app_id_cnt = 0                        for i in range(0, len(general_video_list), 5):                            if i + 5 < len(general_video_list):                                batch_general_video = general_video_list[i: i + batch_num]                                app_version = app_version_list[app_id_cnt]                                app_id = app_dict[app_version].get('appId', 'error')                                track_url = app_dict[app_version].get('trackUrl', 'error')                                sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                                campaign_df = pd.read_sql(sql, engine)                                for row in campaign_df.itertuples():                                    if (app_version + '--优选广告位-' + '程序化上新素材') == getattr(row, 'campaign_name'):                                        campaign_id = getattr(row, 'campaign_id')                                        break                                request_data = get_request_data(account_id=account_id,                                                                campaign_id=campaign_id,                                                                video_info=batch_general_video,                                                                campaign_name=app_version + '--优选广告位-' + '程序化上新素材',                                                                group_name=app_version + '-优选广告位-不限-程序化-' + '上新素材',                                                                ai_strategy_remark="程序化上新素材",                                                                unit_type=7,                                                                app_id=app_id,                                                                click_track_url=track_url)                                request = ai_strategy_request_parse(request_data)                                app_id_cnt += 1                                logger.info("account_id=%s, app_id=%s, 程序化上新素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))                            else:                                batch_general_video = general_video_list[i: len(general_video_list)]                                app_version = app_version_list[app_id_cnt]                                app_id = app_dict[app_version].get('appId', 'error')                                track_url = app_dict[app_version].get('trackUrl', 'error')                                sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id                                campaign_df = pd.read_sql(sql, engine)                                for row in campaign_df.itertuples():                                    if (app_version + '--优选广告位-' + '程序化上新素材') == getattr(row, 'campaign_name'):                                        campaign_id = getattr(row, 'campaign_id')                                        break                                request_data = get_request_data(account_id=account_id,                                                                campaign_id=campaign_id,                                                                video_info=batch_general_video,                                                                campaign_name=app_version + '--优选广告位-' + '程序化上新素材',                                                                group_name=app_version + '-优选广告位-不限-程序化-' + '上新素材',                                                                ai_strategy_remark="程序化上新素材",                                                                unit_type=7,                                                                app_id=app_id,                                                                click_track_url=track_url)                                request = ai_strategy_request_parse(request_data)                                logger.info("account_id=%s, app_id=%s, 程序化上新素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))                    else:                        logger.info("account_id=%s, 程序化上新素材: 返回的视频中没有通用视频" % account_id)                    self.write(json.dumps({"message": "account_id=%s, 程序化上新素材请求已走完!" % account_id}))                    self.flush()            else:                video_info = get_history_video_info(account_id=account_id,                                                    url=get_history_video_url,                                                    start_time=(datetime.datetime.now() + datetime.timedelta(days=-7)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).                                                    strftime("%Y-%m-%d %H:%M:%S"),                                                    video_cnt=programme_new_video_cnt,                                                    related_creative_max_cnt=related_creative_max_cnt)                if video_info['code'] != 0:                    self.write(json.dumps({"account_id": account_id,                                           "message": "程序化上新素材,没有获取到视频,不发送ai策略请求!"}))                    self.flush()                else:                    campaign_name, group_name = get_campaign_and_group_name_rule(account_id)                    if (campaign_name is None) or (group_name is None):                        logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")                        self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))                        self.flush()                    else:                        request_data = get_request_data(account_id=account_id,                                                        campaign_id=campaign_id,                                                        video_info=video_info['data'],                                                        campaign_name=campaign_name,                                                        group_name=group_name,                                                        ai_strategy_remark="程序化上新素材",                                                        name_replace="程序化上新素材",                                                        unit_type=7)                        request = ai_strategy_request_parse(request_data)                        logger.info("account_id = %s, 程序化上新素材,ai策略的返回信息:%s" % (account_id, request))                        self.write(json.dumps(request))                        self.flush()        except Exception:            logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))            self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))            self.flush()
 |