import pandas as pd
import numpy as np
import re
import os
import matplotlib.pyplot as plt
import sql_op
#解决中文显示问题
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
"""
获取巡检任务信息
"""
class de_sys(object):
    def __init__(self):
        pass
    #各变电站预设点位总数比较分析
    def station_count_ansys(self,pdsqlio):
        sql = 'select Station,count(Station) from default_point group by Station order by count(Station) desc;'
        df = pd.DataFrame()
        df = pdsqlio.get_sql_data(sql)
        print('各变电站',df)
        plt.figure(figsize=(15, 15))
        plt.subplots_adjust(hspace=0.3, wspace=0.3)
        plt.bar(x=df['Station'], height=df['count(Station)'], width=0.2)
        plt.title('各站总巡检点位比较')
        plt.show()
    #各变电站预设点位类型占比分析    
    def PatrolType_ansys(self,pdsqlio):
        sql = 'select Station,PatrolType,count(Station) from default_point group by Station,PatrolType ORDER by Station,count(Station) desc'
        df = pd.DataFrame()
        df = pdsqlio.get_sql_data(sql)
        # print('各变电站', df.groupby(['Station','PatrolType']).groups)
        # g = df.groupby(['Station', 'PatrolType'])
        # for i,j in g:
        #     print(i)
        #     print(j)
        print(pd.pivot_table(df,index=[u'Station',u'PatrolType']))
        i = 0
        plt.figure(figsize=(15, 15))
        plt.subplots_adjust(hspace=0.3, wspace=0.3)
        for tempstr in df['Station'].unique():
            plt.subplot(2, 3, i+1)
            a = df[df['Station'] == tempstr]['count(Station)'].tolist()
            b = df[df['Station'] == tempstr]['PatrolType'].tolist()
            # plt.bar(x=df['Station'], height=df['count(Station)'], width=0.2)
            plt.pie(x=a, labels=b, autopct='%.3f%%')
            temp = tempstr+'各类巡检点位占比'
            plt.title(temp)
            i = i+1
        plt.show()
    #各变电站设备区域点位数量占比分析
    def Region_ansys(self,pdsqlio):
        sql = 'select Station,DeviceRegion,count(Station) from default_point group by Station,DeviceRegion ORDER by Station,count(Station) desc'
        df = pd.DataFrame()
        df = pdsqlio.get_sql_data(sql)
        print(pd.pivot_table(df, index=[u'Station', u'DeviceRegion']))
        i = 0
        plt.figure(figsize=(15, 15))
        plt.subplots_adjust(hspace=0.3, wspace=0.3)
        for tempstr in df['Station'].unique():
            plt.subplot(2, 3, i+1)
            a = df[df['Station'] == tempstr]['count(Station)'].tolist()
            b = df[df['Station'] == tempstr]['DeviceRegion'].tolist()
            # plt.bar(x=df['Station'], height=df['count(Station)'], width=0.2)
            plt.pie(x=a, labels=b, autopct='%.3f%%')
            temp = tempstr+'各类巡检点位占比'
            plt.title(temp)
            i = i+1
        plt.show()
        
    #各变电站间隔区域点位数量占比分析

    def LineName_ansys(self, pdsqlio):
        sql = 'select Station,LineName,count(Station) from default_point group by Station,LineName ORDER by Station,count(Station) desc'
        df = pd.DataFrame()
        df = pdsqlio.get_sql_data(sql)
        print(pd.pivot_table(df, index=[u'Station', u'LineName']))
        i = 0
        plt.figure(figsize=(15, 15))
        plt.subplots_adjust(hspace=0.3, wspace=0.3)
        for tempstr in df['Station'].unique():
            plt.subplot(2, 3, i+1)
            a = df[df['Station'] == tempstr]['count(Station)'].tolist()
            b = df[df['Station'] == tempstr]['LineName'].tolist()
            # plt.bar(x=df['Station'], height=df['count(Station)'], width=0.2)
            plt.pie(x=a, labels=b, autopct='%.3f%%')
            temp = tempstr+'各类巡检点位占比'
            plt.title(temp)
            i = i+1
        plt.show()