DataCenterGame/app/game_data_sum/data_sum.py

from model.DateUtils import DateUtils
from model.DataBaseUtils import MysqlUtils, CkUtils
from datetime import datetime, timedelta, timezone
import time
import logging


def h_member_sum(ymd):
    print(f'开始迁移 h_member {ymd}')
    datatime_ymd = datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))).timetuple()
    datatime_ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
        days=1)).timetuple()

    timestamp_ymd = time.mktime(datatime_ymd)
    timestamp_tom = time.mktime(datatime_ymd_tom)

    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    for database_info in database_infos:
        prefix, database_name = database_info

        sql = f'''
        insert into GameDataSum.h_member_sum
        select 
        concat('{prefix}',toString(id)) id ,
        `username` ,
        `nickname` ,
        `mobile_prefix` ,
        `reg_mobile` ,
        `reg_email` ,
        `password` ,
        `pay_pwd` ,
        `email` ,
        `mobile` ,
        `from_device` ,
        `device_id` ,
        concat('{prefix}',toString(app_id)) app_id ,
        `op_id`,
        concat('{prefix}',toString(agent_id)) agent_id ,
        `agent_game` ,
        `status`,
        `reg_ip` ,
        `avatar` ,
        concat('{prefix}',toString(parent_mem_id)) parent_mem_id ,
        create_time ,
        update_time ,
        `real_name` ,
        `identify_type` ,
        `id_card` ,
        `is_switch` ,
         switch_time,
        `switch_money` ,
        `alipay_account` ,
        concat('{prefix}',toString(vb_id)) vb_id 
    from {database_name}.h_member hm 
    where update_time>={timestamp_ymd} and update_time<={timestamp_tom}
    '''
        print(sql)
        ck.execute(sql)


def h_mg_role_sum(ymd):
    print(f'开始迁移 h_mg_role_sum {ymd}')
    datatime_ymd = datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))).timetuple()
    datatime_ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
        days=1)).timetuple()

    timestamp_ymd = time.mktime(datatime_ymd)
    timestamp_tom = time.mktime(datatime_ymd_tom)
    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    for database_info in database_infos:
        prefix, database_name = database_info
        sql = f'''
        insert into GameDataSum.h_mg_role_sum
        select 
            concat('{prefix}',toString(id)) id ,
            concat('{prefix}',toString(mg_mem_id)) mg_mem_id ,
            concat('{prefix}',toString(app_id)) app_id ,
            concat('{prefix}',toString(server_id)) server_id ,
            `server_name` ,
            concat('{prefix}',toString(role_id)) role_id ,
            `role_name` ,
            `role_level` ,
            `role_vip` ,
            `money` ,
            `combat_num` ,
            `ext` ,
            `os` ,
            `create_time` ,
            `update_time` 
        from {database_name}.h_mg_role
        where update_time>={timestamp_ymd} and update_time<={timestamp_tom}

        '''
        print(sql)
        ck.execute(sql)


def h_game_sum():
    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    for database_info in database_infos:
        prefix, database_name = database_info
        sql = f'''
        insert into GameDataSum.h_game_sum
        select 
            concat('{prefix}',toString(id)) id ,
            `name` ,
            `en_name` ,
            `en_abbr` ,
            `app_key` ,
            `tags` ,
            `category` ,
            `classify` ,
            `icon` ,
            `cp_payback_url` ,
            `cp_id` ,
            concat('{prefix}',toString(parent_id)) parent_id ,
            `package_name` ,
            `pay_switch` ,
            `order_switch` ,
            `pay_show` ,
            `float_is_show` ,
            `status` ,
            `is_delete` ,
            `delete_time` ,
            `is_online` ,
            `is_sdk` ,
            `list_order` ,
            `rise_order` ,
            `hot_order` ,
            `like_order` ,
            `publicity` ,
            `language` ,
            `description` ,
            `image` ,
            `run_time` ,
            `create_time` ,
            `update_time` ,
            `fine_order` ,
            `promote_switch` ,
            `apple_id` ,
            `add_cp_time` ,
            `is_bt` ,
            `is_auth` ,
            `single_tag` ,
            `ext_info` ,
            `jump_switch` ,
            `is_push` ,
            `is_exp` 
            from {database_name}.h_game
        '''
        print(sql)
        ck.execute(sql)


def h_mem_game_sum(ymd):
    print(f'开始迁移 h_mem_game_sum {ymd}')
    datatime_ymd = datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))).timetuple()
    datatime_ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
        days=1)).timetuple()

    timestamp_ymd = time.mktime(datatime_ymd)
    timestamp_tom = time.mktime(datatime_ymd_tom)

    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    for database_info in database_infos:
        prefix, database_name = database_info
        sql = f'''
        insert into GameDataSum.h_mem_game_sum
        select 
        concat('{prefix}',toString(id)) id ,
        concat('{prefix}',toString(mem_id)) mem_id ,
        concat('{prefix}',toString(guided_agent_id)) guided_agent_id ,
        concat('{prefix}',toString(app_id)) app_id ,
        `nickname` ,
        `create_time` ,
        `update_time` ,
        `is_default` ,
        `like` ,
        `star_cnt` ,
        `status` ,
        `sum_money` 
          from {database_name}.h_mem_game
        where update_time>={timestamp_ymd} and update_time<={timestamp_tom}

        '''
        print(sql)
        ck.execute(sql)


def h_log_mem_login_sum(ymd):
    def get_tables(database_name):
        sql = f'''
        select name from `system`.tables t 
            where database ='{database_name}'
            and name like 'h_log_mem_login_%'
        '''
        pd = ck.getData_pdv2(sql)
        res_list = []
        for _ in pd:
            for name in pd[_]:
                res_list.append(name)
        return res_list

    print(f'开始迁移 h_log_mem_login_sum {ymd}')
    datatime_ymd = datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))).timetuple()
    datatime_ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
        days=1)).timetuple()

    timestamp_ymd = time.mktime(datatime_ymd)
    timestamp_tom = time.mktime(datatime_ymd_tom)
    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    # mp_mp_conf mp_conf_agent
    for database_info in database_infos:
        prefix, database_name = database_info
        table_names = get_tables(database_name)
        for table_name in table_names:
            sql = f'''
            insert into GameDataSum.h_log_mem_login_sum
            select 
            concat('{prefix}','{table_name}',toString(id)) id ,
            `date` ,
            concat('{prefix}',toString(mem_id)) mem_id ,
            concat('{prefix}',toString(mg_mem_id)) mg_mem_id ,
            concat('{prefix}',toString(agent_id)) agent_id ,
            `agent_game` ,
            concat('{prefix}',toString(app_id)) app_id ,
            `device_id` ,
            `idfv` ,
            `mac` ,
            `ip` ,
            `brand` ,
            `model` ,
            `os` ,
            `os_version` ,
            `screen` ,
            `net` ,
            `imsi` ,
            `longitude` ,
            `latitude` ,
            `userua` ,
            `from` ,
            `reg_time` ,
            `create_time` ,
            `open_cnt` ,
            concat('{prefix}',toString(reg_app_id)) reg_app_id 
             from {database_name}.{table_name}
                    where create_time>={timestamp_ymd} and create_time<={timestamp_tom}
            '''
            print(sql)
            ck.execute(sql)


def mp_mp_conf_sum():
    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    for database_info in database_infos:
        prefix, database_name = database_info
        sql = f'''
        insert into GameDataSum.mp_mp_conf_sum
        select 
            concat('{prefix}',toString(id)) id ,
            concat('{prefix}',toString(app_id)) app_id ,
        `mp_id` ,
        `app_secret` ,
        `sand_key` ,
        `type` ,
        `email` ,
        `password` ,
        `master` ,
        `company` ,
        `mobile` ,
        `wx_name` ,
        `wx_intro` ,
        `wx_logo` ,
        `source_id` ,
        `wx_account`,
        `qr_code` ,
        `ext_info` ,
        `parent_id` ,
        `is_delete` ,
        `delete_time` ,
        `create_time` ,
        `update_time` 
        from {database_name}.mp_mp_conf
        '''
        ck.execute(sql)


def mp_conf_agent_sum():
    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    for database_info in database_infos:
        prefix, database_name = database_info
        sql = f'''
            insert into GameDataSum.mp_conf_agent_sum
             select 
            concat('{prefix}',toString(id)) id ,
            `conf_id`,
            concat('{prefix}',toString(app_id)) app_id ,
            concat('{prefix}',toString(agent_id)) agent_id ,    
            concat('{prefix}',toString(advertiser_conf_id)) advertiser_conf_id ,    
            `create_time` ,
            `update_time` 
            from  {database_name}.mp_conf_agent 
            '''
        ck.execute(sql)


def h_pay_sum(ymd):
    print(f'开始迁移 h_pay_sum {ymd}')
    datatime_ymd = datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))).timetuple()
    datatime_ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
        days=1)).timetuple()

    timestamp_ymd = time.mktime(datatime_ymd)
    timestamp_tom = time.mktime(datatime_ymd_tom)

    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    for database_info in database_infos:
        prefix, database_name = database_info
        sql = f'''
        insert into GameDataSum.h_pay_sum
        select 
        concat('{prefix}',toString(id)) id ,
        concat('{prefix}',toString(order_id)) order_id ,
        concat('{prefix}',toString(cp_order_id)) cp_order_id ,
        concat('{prefix}',toString(mem_id)) mem_id ,        
        concat('{prefix}',toString(mg_mem_id)) mg_mem_id ,
        concat('{prefix}',toString(agent_id)) agent_id ,
        concat('{prefix}',toString(app_id)) app_id ,
        `currency` ,
        `amount` ,
        `real_amount` ,
        concat('{prefix}',toString(product_id)) product_id ,
        `product_cnt` ,
        `product_name` ,
        `coupon_amount` ,
        `ptb_amount` ,
        `gm_amount` ,
        `integral` ,
        `integral_money` ,
        `rebate_amount` ,
        `rate` ,
        `status` ,
        `cp_status` ,
        `payway`,
        `mobile_prefix` ,
        `is_handle` ,
        `pay_time` ,
        `create_time` ,
        `update_time` ,
        `is_distribute` ,
        `notify_cnt` ,
        `last_notify_time` ,
        `ext` ,
        `mem_note` ,
        `admin_note` ,
        `remark` ,
        `is_switch` ,
        `vb_id` 
        from  {database_name}.h_pay 
        where update_time>={timestamp_ymd} and update_time<={timestamp_tom}

            '''
        print(sql)
        ck.execute(sql)


def h_pay_ext_sum():
    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    for database_info in database_infos:
        prefix, database_name = database_info

        sql = f'''
        insert into GameDataSum.h_pay_ext_sum
       select 
	concat('{prefix}',toString(pay_id)) pay_id,	
    `product_id` ,
    `product_name` ,
    `product_desc` ,
    `product_cnt` ,
    `device_id` ,
    `mac` ,
    `ip` ,
    `brand` ,
    `model` ,
    `os` ,
    `os_version` ,
    `screen` ,
    `net` ,
    `imsi` ,
    `longitude` ,
    `latitude` ,
    `userua` ,
    concat('{prefix}',toString(server_id)) server_id,
    `server_name` ,
    concat('{prefix}',toString(role_id)) role_id ,
    `role_name` ,
    `role_level` ,
    `money` ,
    `role_vip` ,
    `online_time` ,
    `scene` ,
    `axis` ,
    `last_operation` ,
    `party_name` ,
    `role_balance` ,
    `experience` ,
    `attach` ,
    `type` ,
    `rolelevel_ctime` ,
    `rolelevel_mtime` 
    from {database_name}.h_pay_ext hpe 
    '''
        print(sql)
        ck.execute(sql)


def h_user_sum(ymd):
    print(f'开始迁移 h_pay_sum {ymd}')
    datatime_ymd = datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))).timetuple()
    datatime_ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
        days=1)).timetuple()

    timestamp_ymd = time.mktime(datatime_ymd)
    timestamp_tom = time.mktime(datatime_ymd_tom)
    database_infos = {('sdk_two_', 'GameDataTwoDbMpPart'), ('sdk_one_', 'GameDataOneDbMpPart')}
    for database_info in database_infos:
        prefix, database_name = database_info

        sql = f'''
        insert into GameDataSum.h_user_sum
       select 
	concat('{prefix}',toString(id)) id ,
    `user_login` ,
    `user_pass` ,
    `pay_pwd` ,
    `user_nicename` ,
    `user_email` ,
    `site_name` ,
    `user_url` ,
    `logo` ,
    `avatar` ,
    `sex` ,
    `birthday` ,
    `signature` ,
    `last_login_ip` ,
    `last_login_time` ,
    `create_time` ,
    `user_activation_key` ,
    `user_status` ,
    `score` ,
	concat('{prefix}',toString(role_id)) role_id ,
    `coin` ,
    `mobile` ,
    `qq` ,
    `linkman` ,
	concat('{prefix}',toString(parent_id)) parent_id ,
    concat('{prefix}',toString(mem_id)) mem_id ,
    `cp_id` ,
    `wx_qr` ,
    `float_icon` ,
    `is_switch` ,
    `switch_rate` ,
    `ext_info` ,
    `account_id` ,
    `parent_account_id`
    from {database_name}.h_user hu 
    where last_login_time>={timestamp_ymd} and last_login_time<={timestamp_tom}

    '''
        print(sql)
        ck.execute(sql)


def optimize_sql():
    final_sql_h_pay_sum = ''' 
        optimize table  GameDataSum.h_pay_sum final
        '''
    ck.execute(final_sql_h_pay_sum)

    final_sql_mp_conf_agent_sum = ''' 
        optimize table  GameDataSum.mp_conf_agent_sum final
        '''
    ck.execute(final_sql_mp_conf_agent_sum)

    final_sql_mp_mp_conf_sum = ''' 
        optimize table  GameDataSum.mp_mp_conf_sum final
        '''
    ck.execute(final_sql_mp_mp_conf_sum)

    final_sql_h_log_mem_login_sum = ''' 
        optimize table  GameDataSum.h_log_mem_login_sum final
        '''
    ck.execute(final_sql_h_log_mem_login_sum)

    final_sql_h_mem_game_sum = ''' 
        optimize table  GameDataSum.h_mem_game_sum final
        '''
    ck.execute(final_sql_h_mem_game_sum)

    final_sql_h_game_sum = ''' 
        optimize table  GameDataSum.h_game_sum final
        '''
    ck.execute(final_sql_h_game_sum)

    final_sql_h_mg_role_sum = ''' 
        optimize table  GameDataSum.h_mg_role_sum final
        '''
    ck.execute(final_sql_h_mg_role_sum)

    final_sql_h_member_sum = ''' 
        optimize table  GameDataSum.h_member_sum final
        '''
    ck.execute(final_sql_h_member_sum)

    final_sql_h_pay_ext_sum = ''' 
        optimize table  GameDataSum.h_pay_ext_sum final
        '''
    ck.execute(final_sql_h_pay_ext_sum)
    final_sql_h_user_sum = ''' 
        optimize table  GameDataSum.h_user_sum final
        '''
    ck.execute(final_sql_h_user_sum)


def change_data_style(ymd):
    h_member_sum(ymd)
    h_mg_role_sum(ymd)
    h_mem_game_sum(ymd)
    h_log_mem_login_sum(ymd)
    h_pay_sum(ymd)

    mp_mp_conf_sum()
    mp_conf_agent_sum()
    h_game_sum()
    h_user_sum(ymd)
    h_pay_ext_sum()

def change_data_style_hourly():
    ymd = du.getNow()
    change_data_style(ymd)
    optimize_sql()


def change_data_style_daily():
    for i in du.getDateLists(du.get_n_days(-10), du.get_n_days(0)):
        print(i)
        change_data_style(i)
    optimize_sql()


def change_data_style_choice(st, et):
    for i in du.getDateLists(st, et):
        print(i)
        change_data_style(i)
    optimize_sql()


ck = CkUtils()
du = DateUtils()
if __name__ == '__main__':
    dt = DateUtils()
    st = du.get_n_days(-30)
    et = du.getNow()
    # change_data_style_choice(st, et)
    # h_user_sum()
    start_time=time.time()
    print()
    h_pay_ext_sum()
    print(time.time()-start_time)