import pandas as pd
import numpy as np
import altair as alt
import pymysql
from sqlalchemy import create_engine
import seaborn    


engine = create_engine('mysql+pymysql://xxxx')
sql = ''' select case_name ,result, platform_name, error_msg  from report_reportdetail; '''
df = pd.read_sql_query(sql, engine)


# 根据 error message区分
ErrorMsg=df.loc[df['result']=='fail',:].groupby(['error_msg']).count().sort_values(by = ['result'], ascending = [False])
# select count(result) as i,error_msg  from report_reportdetail where result = 'fail' group by error_msg order by i desc ;

# 选取failed case 和指定的平台
df1 =df.loc[(df['result']=='fail')&(df['platform_name']=='HW1') ,:].copy()
df2 =df.loc[(df['result']=='fail')&(df['platform_name']=='HW2') ,:].copy()

df1.loc[:, "error_msg_short"] = df1["error_msg"].str[0:60]
df2.loc[:, "error_msg_short"] = df2["error_msg"].str[0:60]

df1.set_index(['case_name', 'error_msg_short'], inplace=True)
df2.set_index(['case_name', 'error_msg_short'], inplace=True)


df1 =df1.groupby(level=df1.index.names).count()
df2 =df2.groupby(level=df2.index.names).count()


HW1=df1.loc[(df1["result"]>0) ,:].rename(columns={'result':'出现次数'}).loc[:,['出现次数']]
HW2=df2.loc[(df2["result"]>0) ,:].rename(columns={'result':'出现次数'}).loc[:,['出现次数']]

HW1_ENV=df1.loc[(df1["result"]<=1) ,:].rename(columns={'result':'出现次数'}).loc[:,['出现次数']]
HW2_ENV=df2.loc[(df2["result"]<=3) ,:].rename(columns={'result':'出现次数'}).loc[:,['出现次数']]

HW1_BUG = df1.iloc[df1.index.get_level_values(1).str.contains('BUG\d{6}:')]
HW2_BUG = df2.iloc[df2.index.get_level_values(1).str.contains('BUG\d{6}:')]



# ErrorMsg

HW1
# HW2

# HW2_BUG
# HW1_BUG

# HW1_ENV



data = HW1_ENV.reset_index()
fg = seaborn.factorplot(x='case_name', y='出现次数',col='error_msg_short', data=data, kind='bar')
fg.fig.set_size_inches(160,160)


# fg.set_xlabels('')