def bt_rom_ver():
print('bt_rom_ver')
if (mysql_inserted('bt_rom_ver') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid +
'out9.txt',
names=tv_telecontrol_columns)
df = pd.DataFrame(data)
df.fillna(0, inplace=True)
df['rc_type'] = df['rc_type'].astype('int')
df['ctype'] = df['ctype'].astype('int')
df = df[(df['rc_type'] == 1)]
df.drop_duplicates(subset=['mac', 'h_mode', 'ctype'],
keep='last',
inplace=True)
groups = df.groupby(['rom_ver', 'h_mode', 'ctype'])
df_from_group = groups.size().reset_index(name='count')
for row in df_from_group.itertuples():
print(row)
bt_rom_ver_sql = 'INSERT INTO bt_rom_ver (rom_ver, ctype, h_mode, count, date) VALUES (\'' + row.rom_ver + '\',\'' + str(
row.ctype) + '\',\'' + row.h_mode + '\',\'' + str(
row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(bt_rom_ver_sql)
def txt_helper_cast_screen():
print('txt_helper_cast_screen update_date:' + yesterday_str())
if (mysql_inserted('cast_screen') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid +
'out7.txt',
names=helper_cast_screen_columns)
df = pd.DataFrame(data)
df.fillna(0, inplace=True)
df['app_type'] = df['app_type'].astype('int')
df = df[df['app_type'] == 5]
df.drop_duplicates(subset=['mac', 'protocal_id'],
keep='last',
inplace=True)
groups = df.groupby(['h_mode', 'app_ver', 'protocal_id'])
df_from_group = groups.size().reset_index(name='count')
#print(df_from_group)
for row in df_from_group.itertuples():
print(row)
if (row.protocal_id not in ('AIRPLAY', 'DLNA', 'MIRACAST')):
continue
helper_cast_screen_sql = 'INSERT INTO cast_screen (h_mode, app_ver, protocal_id, count, date) VALUES (\'' + row.h_mode + '\',\'' + row.app_ver + '\',\'' + row.protocal_id + '\',\'' + str(
row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(helper_cast_screen_sql)
def txt_install_sucess():
print('txt_install_sucess')
data = pd.read_table(filename_pre + yesterday_str() + filename_mid +
'out5.txt', names=tv_download_apk_installed_columns)
df = pd.DataFrame(data)
df.fillna(0, inplace=True)
df.drop_duplicates(subset=['mac', 'tver', 'ok'], keep='last', inplace=True)
df['ok'] = df['ok'].astype('int')
sucess_state = [1, -4, -25]
df_valid = df[(df['ok'].isin(sucess_state)) & (df['mac'] != 0)]
df_distinct_mac = del_old_mac(df_valid, 'installSucess')
if (len(df_distinct_mac) == 0):
return
groups = df_distinct_mac.groupby(['h_mode', 'app_ver', 'tver'])
df_from_group = groups.size().reset_index(name='count')
# print(df_from_group)
for row in df_from_group.itertuples():
print(row)
txt_install_sucess_select_sql = 'SELECT * FROM debug_sdk_download_install WHERE h_mode=\'' + row.h_mode + '\' and sdk_ver=\'' + \
str(row.app_ver) + '\' and app=\'' + row.tver + \
'\' and date=\'' + yesterday_str() + '\';'
txt_install_sucess_sql = ''
if (mysql_query(txt_install_sucess_select_sql)):
txt_install_sucess_sql = 'UPDATE debug_sdk_download_install set install_sucess=\'' + \
str(row.count) + '\' where h_mode=\'' + row.h_mode + '\' and sdk_ver=\'' + \
str(row.app_ver) + '\' and app=\'' + row.tver + \
'\' and date=\'' + yesterday_str() + '\';'
else:
txt_install_sucess_sql = 'INSERT INTO debug_sdk_download_install (h_mode, sdk_ver, app, install_sucess, date) VALUES (\'' + row.h_mode + '\',\'' + str(row.app_ver) + '\',\'' + row.tver + '\',\'' + \
str(row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(txt_install_sucess_sql)
install_sucess_save = 0
save_macs = {}
for row in df_distinct_mac.itertuples():
try:
install_sucess_save += 1
current_line = (row.mac + '\t' + yesterday_str() + '\n')
if (row.tver in save_macs):
save_macs[row.tver] = (save_macs[row.tver] + current_line)
else:
save_macs[row.tver] = current_line
if (install_sucess_save % 10000 == 0):
save_batch(save_macs, 'installSucess')
print('save installSucess index:' + str(install_sucess_save))
save_macs.clear()
except Exception as err:
print('installSucess tver:' + row.tver)
print('installSucess mac:' + row.mac)
print(err)
continue
save_batch(save_macs, 'installSucess')
save_macs.clear()
df_distinct_mac.drop_duplicates(subset=['tver'], keep='last', inplace=True)
for row in df_distinct_mac.itertuples():
# print(row)
duplicates_mac(debug_task_top_dir + row.tver + '/' + 'installSucess')
print('debug sdkmanager end...')
def sdk_upgrade_info():
print('sdk_upgrade_info')
if (mysql_inserted('sdk_upgrade_info') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid + 'out2.txt', names = tv_upgrade_info_columns)
df = pd.DataFrame(data)
df.fillna(0, inplace = True)
df.drop_duplicates(subset = ['app_ver', 'mac'], keep = 'last', inplace = True)
#20181129 tv_upgrade_info新增两列，'android_id','router_ssid'导致下面问题
#ValueError: invalid literal for int() with base 10: 'FD5551A-SU'
#df['app_ver'] = df['app_ver'].astype('int')
try:
df['app_ver'] = df['app_ver'].astype('int')
except Exception as err:
print('sdk_upgrade_info error')
print(err)
return
groups = df.groupby(['app_ver'])#####板卡rom版本混乱，后续不跟据rom版本分组，日活也不支持根据rom版本查询
df_from_group = groups.size().reset_index(name = 'count')
#print(df_from_group)
for row in df_from_group.itertuples():
print(row)
sdk_upgrade_info_sql = 'INSERT INTO sdk_upgrade_info (app_ver, app_count, date) VALUES (\'' + str(row.app_ver) + '\',\'' + str(row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(sdk_upgrade_info_sql)
######不跟据rom版本分组
"""
def txt_download_start():
print('txt_download_start update_date:' + yesterday_str())
if (mysql_inserted('sdk_download_install') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid + 'out3.txt', names = tv_download_apk_start_columns)
df = pd.DataFrame(data)
df.fillna(0, inplace = True)
df.drop_duplicates(subset = ['mac', 'tver'], keep = 'last', inplace = True)
df_valid = df[df['mac'] != 0]
df_distinct_mac = del_old_mac(df_valid, 'downloadStart')###
if (len(df_distinct_mac) == 0):
return
groups = df_distinct_mac.groupby(['tver'])
df_from_group = groups.size().reset_index(name = 'count')
#print(df_from_group)
for row in df_from_group.itertuples():
#print(row)
txt_download_start_sql = 'INSERT INTO sdk_download_install (app, download_start, date) VALUES (\'' + row.tver + '\',\'' + str(row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(txt_download_start_sql)
for row in df_distinct_mac.itertuples():
try:
save(row.tver, 'downloadStart', row.mac)###
except Exception as err:
print('downloadStart tver:' + row.tver)
print('downloadStart mac:' + row.mac)
print(err)
continue
for row in df_from_group.itertuples():
#print(row)
duplicates_mac(task_top_dir + row.tver + '/' + 'downloadStart')###
def bt_pair_status():
print('bt_pair_status')
if (mysql_inserted('bt_pair_status') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid +
'out9.txt',
names=tv_telecontrol_columns)
df = pd.DataFrame(data)
df.fillna(-1, inplace=True)
df['rc_type'] = df['rc_type'].astype('int')
df['ctype'] = df['ctype'].astype('int')
df['bt_pair_ui'] = df['bt_pair_ui'].astype('int')
df['bt_pair_status'] = df['bt_pair_status'].astype('int')
df['bt_start_pk'] = df['bt_start_pk'].astype('int')
df['bt_callback'] = df['bt_callback'].astype('int')
df = df[(df['rc_type'] == 1)]
groups_macs = df.groupby([
'rom_ver', 'h_mode', 'ctype', 'bt_pair_ui', 'bt_pair_status',
'bt_start_pk', 'bt_callback'
])
df_from_group_macs = groups_macs.size().reset_index(name='count')
for row in df_from_group_macs.itertuples():
print(row)
bt_rom_ver_sql = 'INSERT INTO bt_pair_status (rom_ver, ctype, h_mode, bt_pair_ui, bt_pair_status, bt_start_pk, bt_callback, count, date, distinct_mac) VALUES (\'' + row.rom_ver + '\',\'' + str(
row.ctype) + '\',\'' + row.h_mode + '\',\'' + str(
row.bt_pair_ui) + '\',\'' + str(
row.bt_pair_status) + '\',\'' + str(
row.bt_start_pk) + '\',\'' + str(
row.bt_callback) + '\',\'' + str(
row.count) + '\',\'' + yesterday_str(
) + '\',\'' + str(0) + '\');'
mysql_execute(bt_rom_ver_sql)
df.drop_duplicates(subset=[
'mac', 'h_mode', 'ctype', 'bt_pair_ui', 'bt_pair_status',
'bt_start_pk', 'bt_callback'
],
keep='last',
inplace=True)
groups_mac = df.groupby([
'rom_ver', 'h_mode', 'ctype', 'bt_pair_ui', 'bt_pair_status',
'bt_start_pk', 'bt_callback'
])
df_from_group_mac = groups_mac.size().reset_index(name='count')
for row in df_from_group_mac.itertuples():
print(row)
bt_rom_ver_sql = 'INSERT INTO bt_pair_status (rom_ver, ctype, h_mode, bt_pair_ui, bt_pair_status, bt_start_pk, bt_callback, count, date, distinct_mac) VALUES (\'' + row.rom_ver + '\',\'' + str(
row.ctype) + '\',\'' + row.h_mode + '\',\'' + str(
row.bt_pair_ui) + '\',\'' + str(
row.bt_pair_status) + '\',\'' + str(
row.bt_start_pk) + '\',\'' + str(
row.bt_callback) + '\',\'' + str(
row.count) + '\',\'' + yesterday_str(
) + '\',\'' + str(1) + '\');'
mysql_execute(bt_rom_ver_sql)
def tv_upgrade_info():
print('tv_upgrade_info')
if (mysql_inserted('tv_upgrade_info') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid + 'out1.txt', names=['brand', 'rom_ver', 'count'])
df = pd.DataFrame(data)
for row in df.itertuples():
print(row)
tv_upgrade_info_sql = 'INSERT INTO tv_upgrade_info (brand, rom_ver, count, date) VALUES (\'' + row.brand + '\',\'' + row.rom_ver + '\',\'' + str(row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(tv_upgrade_info_sql)
def txt_download_start():
print('txt_download_start update_date:' + yesterday_str())
if (mysql_inserted('debug_sdk_download_install') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() +
filename_mid + 'out3.txt', names=tv_download_apk_start_columns)
df = pd.DataFrame(data)
df.fillna(0, inplace=True)
df.drop_duplicates(subset=['mac', 'tver'], keep='last', inplace=True)
df_valid = df[df['mac'] != 0]
df_distinct_mac = del_old_mac(df_valid, 'downloadStart')
if (len(df_distinct_mac) == 0):
return
groups = df_distinct_mac.groupby(['h_mode', 'app_ver', 'tver'])
df_from_group = groups.size().reset_index(name='count')
# print(df_from_group)
for row in df_from_group.itertuples():
# print(row)
txt_download_start_sql = 'INSERT INTO debug_sdk_download_install (h_mode, sdk_ver, app, download_start, date) VALUES (\'' + row.h_mode + '\', \'' + str(row.app_ver) + '\', \'' + row.tver + '\', \'' + \
str(row.count) + '\', \'' + yesterday_str() + '\');'
mysql_execute(txt_download_start_sql)
download_start_save = 0
save_macs = {}
for row in df_distinct_mac.itertuples():
try:
download_start_save += 1
current_line = (row.mac + '\t' + yesterday_str() + '\n')
if (row.tver in save_macs):
save_macs[row.tver] = (save_macs[row.tver] + current_line)
else:
save_macs[row.tver] = current_line
if (download_start_save % 10000 == 0):
save_batch(save_macs, 'downloadStart')
print('save downloadStart index:' + str(download_start_save))
save_macs.clear()
except Exception as err:
print('downloadStart tver:' + row.tver)
print('downloadStart mac:' + row.mac)
print(err)
continue
save_batch(save_macs, 'downloadStart')
save_macs.clear()
df_distinct_mac.drop_duplicates(subset=['tver'], keep='last', inplace=True)
for row in df_distinct_mac.itertuples():
# print(row)
duplicates_mac(debug_task_top_dir + row.tver + '/' + 'downloadStart')
def txt_install_fail():
print('txt_install_fail')
if (mysql_inserted('sdk_install_fail') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid + 'out5.txt', names = tv_download_apk_installed_columns)
df = pd.DataFrame(data)
df.fillna(0, inplace = True)
df.drop_duplicates(subset = ['mac', 'tver', 'ok'], keep = 'last', inplace = True)
df['app_ver'] = df['app_ver'].astype('int')
df['ok'] = df['ok'].astype('int')
df_valid = df[df['rom_ver'].str.contains('_s')]
groups = df_valid.groupby(['rom_ver', 'app_ver', 'tver', 'ok'])
df_from_group = groups.size().reset_index(name = 'count')
#print(df_from_group)
for row in df_from_group.itertuples():
print(row)
sdk_install_fail_sql = 'INSERT INTO sdk_install_fail (rom_ver, sdk_ver, app, error_code, count, date) VALUES (\'' + row.rom_ver + '\',\'' + str(row.app_ver) + '\',\'' + row.tver + '\',\'' + str(row.ok) + '\',\'' + str(row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(sdk_install_fail_sql)
def txt_helper_cast_screen_status():
print('txt_helper_cast_screen_status update_date:' + yesterday_str())
if (mysql_inserted('cast_screen_status') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid +
'out7.txt',
names=helper_cast_screen_columns)
df = pd.DataFrame(data)
df.fillna('0', inplace=True)
df['app_type'] = df['app_type'].astype('int')
df = df[df['app_type'] == 5]
df_valid = df[df['protocal_id'].isin(['DLNA', 'AIRPLAY', 'MIRACAST'])]
groups = df_valid.groupby(
['h_mode', 'rom_ver', 'app_ver', 'protocal_id', 'status'])
df_from_group = groups.size().reset_index(name='count')
for row in df_from_group.itertuples():
print(row)
cast_screen_status_sql = 'INSERT INTO cast_screen_status (h_mode, rom_ver, app_ver, protocal_id, status ,count , date) VALUES (\'' + row.h_mode + '\',\'' + row.rom_ver + '\',\'' + row.app_ver + '\',\'' + row.protocal_id + '\',\'' + str(
row.status) + '\',\'' + str(
row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(cast_screen_status_sql)
pass
def rom_recovery():
try:
print('rom_recovery_mac')
if (mysql_inserted('rom_recovery') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid +
'out8.txt',
names=rom_recovery_columns)
df = pd.DataFrame(data)
df.fillna(0, inplace=True)
######不去重mac
groups_macs = df.groupby(['rom_ver', 'h_mode', 'recovery_info'])
df_from_group_macs = groups_macs.size().reset_index(name='count')
for row in df_from_group_macs.itertuples():
print(row)
rom_recovery_macs = 'INSERT INTO rom_recovery (rom_ver, h_mode, recovery_info, count, date, distinct_mac) VALUES (\'' + row.rom_ver + '\',\'' + row.h_mode + '\',\'' + str(
row.recovery_info) + '\',\'' + str(
row.count) + '\',\'' + yesterday_str() + '\',\'' + str(
0) + '\');'
mysql_execute(rom_recovery_macs)
######去重mac
df.drop_duplicates(subset=['mac', 'recovery_info'],
keep='last',
inplace=True)
groups_mac = df.groupby(['rom_ver', 'h_mode', 'recovery_info'])
df_from_group_mac = groups_mac.size().reset_index(name='count')
for row in df_from_group_mac.itertuples():
print(row)
rom_recovery_mac = 'INSERT INTO rom_recovery (rom_ver, h_mode, recovery_info, count, date, distinct_mac) VALUES (\'' + row.rom_ver + '\',\'' + row.h_mode + '\',\'' + str(
row.recovery_info) + '\',\'' + str(
row.count) + '\',\'' + yesterday_str() + '\',\'' + str(
1) + '\');'
mysql_execute(rom_recovery_mac)
except Exception as err:
print('rom_recovery error', err)
pass
def txt_helper_cast_screen_err():
print('txt_helper_cast_screen_err update_date:' + yesterday_str())
if (mysql_inserted('cast_screen_err') > 0):
return
data = pd.read_table(filename_pre + yesterday_str() + filename_mid +
'out7.txt',
names=helper_cast_screen_columns)
df = pd.DataFrame(data)
df.fillna('0', inplace=True)
df_valid = df[(df['protocal_id'].isin(['DLNA', 'AIRPLAY', 'MIRACAST']))
& (df['status'] == 2)]
groups = df_valid.groupby(
['h_mode', 'rom_ver', 'app_ver', 'protocal_id', 'err'])
df_from_group = groups.size().reset_index(name='count')
for row in df_from_group.itertuples():
print(row)
if ((row.protocal_id == 'DLNA') & (row.err.find(':') > 0)):
IndexErr = row.err.find(':')
subErr = row.err[0:IndexErr]
cast_screen_status_select_sql = 'SELECT count FROM cast_screen_err WHERE h_mode=\'' + row.h_mode + '\' and rom_ver=\'' + row.rom_ver + '\' and app_ver=\'' + row.app_ver + '\' and protocal_id=\'' + row.protocal_id + '\' and err=\'' + subErr + '\' and date=\'' + yesterday_str(
) + '\';'
cast_screen_status_sub_sql = ''
rs = mysql_group_count(cast_screen_status_select_sql)
if (rs[0] > 0):
cast_screen_status_sub_sql = 'UPDATE cast_screen_err set count=\'' + str(
row.count + rs[1]
) + '\' where h_mode=\'' + row.h_mode + '\' and rom_ver=\'' + row.rom_ver + '\' and app_ver=\'' + row.app_ver + '\' and protocal_id=\'' + row.protocal_id + '\' and err=\'' + subErr + '\' and date=\'' + yesterday_str(
) + '\';'
pass
else:
cast_screen_status_sub_sql = 'INSERT INTO cast_screen_err (h_mode, rom_ver, app_ver, protocal_id, err ,count , date) VALUES (\'' + row.h_mode + '\',\'' + row.rom_ver + '\',\'' + row.app_ver + '\',\'' + row.protocal_id + '\',\'' + subErr + '\',\'' + str(
row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(cast_screen_status_sub_sql)
continue
cast_screen_status_sql = 'INSERT INTO cast_screen_err (h_mode, rom_ver, app_ver, protocal_id, err ,count , date) VALUES (\'' + row.h_mode + '\',\'' + row.rom_ver + '\',\'' + row.app_ver + '\',\'' + row.protocal_id + '\',\'' + row.err + '\',\'' + str(
row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(cast_screen_status_sql)
def txt_install_sucess():
print('txt_install_sucess')
data = pd.read_table(filename_pre + yesterday_str() + filename_mid + 'out5.txt', names = tv_download_apk_installed_columns)
df = pd.DataFrame(data)
df.fillna(0, inplace = True)
df.drop_duplicates(subset = ['mac', 'tver', 'ok'], keep = 'last', inplace = True)
df['ok'] = df['ok'].astype('int')
sucess_state = [1, -4, -25]
df_valid = df[(df['ok'].isin(sucess_state)) & (df['mac'] != 0)]
df_distinct_mac = del_old_mac(df_valid, 'installSucess')###
if (len(df_distinct_mac) == 0):
return
groups = df_distinct_mac.groupby(['tver'])
df_from_group = groups.size().reset_index(name = 'count')
#print(df_from_group)
for row in df_from_group.itertuples():
print(row)
txt_install_sucess_select_sql = 'SELECT * FROM sdk_download_install WHERE app=\'' + row.tver + '\' and date=\'' + yesterday_str() + '\';'
txt_install_sucess_sql = ''
if (mysql_query(txt_install_sucess_select_sql)):
txt_install_sucess_sql = 'UPDATE sdk_download_install set install_sucess=\'' + str(row.count) + '\' where app=\'' + row.tver + '\' and date=\'' + yesterday_str() + '\';'
else:
txt_install_sucess_sql = 'INSERT INTO sdk_download_install (app, install_sucess, date) VALUES (\'' + row.tver + '\',\'' + str(row.count) + '\',\'' + yesterday_str() + '\');'
mysql_execute(txt_install_sucess_sql)
for row in df_distinct_mac.itertuples():
try:
save(row.tver, 'installSucess', row.mac)###
except Exception as err:
print('installSucess tver:' + row.tver)
print('installSucess mac:' + row.mac)
print(err)
continue
for row in df_from_group.itertuples():
#print(row)
duplicates_mac(task_top_dir + row.tver + '/' + 'installSucess')###