# !/usr/bin/python
# -*- coding: utf-8 -*-
import datetime
import time
import pymysql
import os
from datetime import datetime



url = "127.0.0.1"
name = "xxx"
pwd = "xxx"
dataBase = "xxx"
db_connect=pymysql.connect(host=url, port=xxxx, user=name, passwd=pwd, db=dataBase)

sql="SELECT t.template_id,t.city_id ,l.request_url,l.send_body,l.send_header,l.receive_data,t.service_desc,l.gmt_create FROM send_log_16 l LEFT JOIN service_invoke_template t ON l.template_id = t.template_id WHERE t.service_desc IN ('XX上报SN','XX上报ModeChange','XX上报fault','XX上报faultRecovery','XX上报alarm') ORDER BY l.gmt_create DESC LIMIT 10000;"

cursor = db_connect.cursor()

try:
    if isinstance(sql, str):
        cursor.execute(sql)
        resultData = list(cursor.fetchall())
    else:
        resultData = []
        for sq in sql:
            cursor.execute(sq)
            resultData.append(list(cursor.fetchall()))
except Exception as err:
    result = ''.join(('An db query exception happened: ', str(err)))


try:
    db.close()
except Exception as err:
    result = ''.join(('An db closed exception happened: ', str(err)))

# print(resultData)

cur_path = os.path.dirname(os.path.realpath(__file__))
delayPath=cur_path + '/text/delay.txt'
dalayTime=300
min0_1=[]
min1_2=[]
min2_3=[]
min3_4=[]
min3_4=[]
min4_5=[]
min5_20=[]
min20=[]
with open(delayPath, 'a', encoding='utf-8') as f:
    for i in resultData:
        if 'currentTime' and 'fault' in i[3] :
            startTime1 = eval(i[3])['fault'][0]['time']
            endTime1 = eval(i[3])['fault'][0]['currentTime']
            startTime3 = datetime.strptime(startTime1, "%Y%m%d%H%M%S%f")
            endTime3 = datetime.strptime(endTime1, "%Y%m%d%H%M%S%f")
            startTime=int(time.mktime(startTime3.timetuple()) * 1000.0+startTime3.microsecond/1000.0)
            endTime = int(time.mktime(endTime3.timetuple()) * 1000.0 + endTime3.microsecond / 1000.0)
            dif=(endTime - startTime)/1000

            if dif>dalayTime:
                f.write("fault delay:%s,header=%s" % (dif, i[4]))
                f.write("\n")
            if dif <= 1:
                min0_1.append(dif)
            elif 1 < dif <= 2:
                min1_2.append(dif)
            elif 2 < dif <= 3:
                min2_3.append(dif)
            elif 3 < dif <= 4:
                min3_4.append(dif)
            elif 4 < dif <= 5:
                min4_5.append(dif)
            elif 5 < dif <= 20:
                min5_20.append(dif)
            elif dif > 20:
                min20.append(dif)

        elif 'service_mode'  in i[3]:
            startTime1 = eval(i[3])['event'][0]['time']
            endTime1 = eval(i[3])['event'][0]['currentTime']
            startTime3 = datetime.strptime(startTime1, "%Y%m%d%H%M%S%f")
            endTime3 = datetime.strptime(endTime1, "%Y%m%d%H%M%S%f")
            startTime = int(time.mktime(startTime3.timetuple()) * 1000.0+startTime3.microsecond/1000.0)
            endTime = int(time.mktime(endTime3.timetuple()) * 1000.0 + endTime3.microsecond / 1000.0)
            dif =(endTime - startTime)/1000
            if dif > dalayTime:
                f.write("service_mode delay:%s,header=%s" % (dif, i[4]))
                f.write("\n")
            if dif <= 1:
                min0_1.append(dif)
            elif 1 < dif <= 2:
                min1_2.append(dif)
            elif 2 < dif <= 3:
                min2_3.append(dif)
            elif 3 < dif <= 4:
                min3_4.append(dif)
            elif 4 < dif <= 5:
                min4_5.append(dif)
            elif 5 < dif <= 20:
                min5_20.append(dif)
            elif dif > 20:
                min20.append(dif)
        elif 'alarm'  in i[3]:
            startTime1 = eval(i[3])['alarm'][0]['time']
            endTime1 = eval(i[3])['alarm'][0]['currentTime']
            startTime3 = datetime.strptime(startTime1, "%Y%m%d%H%M%S%f")
            endTime3 = datetime.strptime(endTime1, "%Y%m%d%H%M%S%f")
            startTime = int(time.mktime(startTime3.timetuple()) * 1000.0 + startTime3.microsecond / 1000.0)
            endTime = int(time.mktime(endTime3.timetuple()) * 1000.0 + endTime3.microsecond / 1000.0)
            dif = (endTime - startTime) / 1000
            if dif >dalayTime:
                f.write("alarm delay:%s,header=%s" % (dif, i[4]))
                f.write("\n")
            if dif <= 1:
                min0_1.append(dif)
            elif 1 < dif <= 2:
                min1_2.append(dif)
            elif 2 < dif <= 3:
                min2_3.append(dif)
            elif 3 < dif <= 4:
                min3_4.append(dif)
            elif 4 < dif <= 5:
                min4_5.append(dif)
            elif 5 < dif <= 20:
                min5_20.append(dif)
            elif dif > 20:
                min20.append(dif)
sumList=min0_1+min1_2+min2_3+min3_4+min4_5+min5_20+min20
sumValue=sum(sumList)
sumNum=len(sumList)

print("统计数量为%s条"%len(sumList))
print("小于1s的数量为%s条，占比%s" %(len(min0_1),round(len(min0_1)*1.0/sumNum,3)))
print("1s-2s之间的数量为%s条，占比%s" %(len(min1_2),round(len(min1_2)*1.0/sumNum,3)))
print("2s-3s之间的数量为%s条，占比%s" %(len(min2_3),round(len(min2_3)*1.0/sumNum,3)))
print("3s-4s之间的数量为%s条，占比%s" %(len(min3_4),round(len(min3_4)*1.0/sumNum,3)))
print("4s-5s之间的数量为%s条，占比%s" %(len(min4_5),round(len(min4_5)*1.0/sumNum,3)))
print("5s-20s之间的数量为%s条，占比%s" %(len(min5_20),round(len(min5_20)*1.0/sumNum,3)))
print("大于20s的数量为%s条，占比%s" %(len(min20),round(len(min5_20)*1.0/sumNum,3)))


average=round(sumValue/sumNum,3)
print("平均值为%s秒"%average)
maxNum = round(max(sumList),2)
minNum=round(min(sumList),2)
print("最大值为%s,最小值为%s"%(maxNum,minNum))