-- coding: utf-8 --

import os
import datetime
import pymysql
import utils
import matplotlib
import matplotlib.pyplot as plt
from pylab import mpl

1、设置可视化绘图参数

ship_ids = []
from_date = utils.parse_datetime('2018-08-26')
to_date=utils.parse_datetime('2018-08-28')
columns = ['ACQ_TIME', 'DATA_TYPE', 'SIGNAL_STRENGTH', 'LIBV', 'PVBV', 'CPU_TEMPRETURE']
def datetime_toString(dt):
return dt.strftime("%Y-%m-%d %H:%M:%S")

def execute_sql(sql_cmd):
db = pymysql.connect()
cursor = db.cursor()
cursor.execute(sql_cmd)
return cursor.fetchall()

tmp_path = 'D:/tmp'

if not os.path.exists(tmp_path):

os.mkdir(tmp_path)

if dest_path is None:

dest_path = os.path.join(path, '%s_%s' % (from_date.date(), to_date.date()))

通过日期区间和渔船设备号查找数据

def get_signal_strength_by_device_id_and_signal_type(from_date, to_date,device_no):

print ("Partition ship data by device_no")
process_day = from_date
rs =[]
signal_change_points= [0]
data_time_temp1 = 0
data_time_temp2 = 0
signal_change_point_temp1 = 0
signal_change_point_temp2 = 0
temp_dts =[]
GPRS_TIME = 0
LBS_TIME = 0
GPRS_loss_num = 0
GPRS_loss_point = []
GPRS_fact_num = 0
LBS_fact_num = 0
LBS_loss_num =0 
LBS_loss_point = []
flag = 0  #用于日期递增
flag1 = 0  #判断信号频率转换的标志位
flag2 = 1  #判断GPRS转为北斗信号后的频率为2分钟一次还是5分钟一次的标志位
while process_day <= to_date:
    table_name = process_day.strftime("t_acq_data_%Y%m%d")   
    get_ship_data_by_date_cmd = ("select ALARM_STATUS,ACQ_TIME,DATA_TYPE  from %s where  DEVICE_NO = %s"
                                 % (table_name,device_no))    
    temp_dts = execute_sql(get_ship_data_by_date_cmd)
    if temp_dts == ():
        print('--------------device_no为'+device_no+'在'+str(process_day.date())+'这天没有数据')
    else:
        if (int(temp_dts[0][0],2) & int(8192)) :
            data_type_begin = 2
            flag1 = 4
        elif (int(temp_dts[0][0],2) & int(32)):
            flag1 = 1#5分钟一次
            data_type_begin = 1
        elif(int(temp_dts[0][0],2) & int(2048)):
            flag1 = 2#10分钟一次
            data_type_begin = 1
        else:
            flag1 = 3#2分钟一次
            data_type_begin = 1  
        data_time_temp1 = temp_dts[0][1].second + temp_dts[0][1].minute*60+temp_dts[0][1].hour*3600+flag*3600*24
        signal_change_point_temp1 = data_time_temp1
        for temp_dt in temp_dts:
            data_time_temp2=temp_dt[1].second + temp_dt[1].minute*60+temp_dt[1].hour*3600 +flag*3600*24 #下一个点的时间以秒数计算
            if (2 if int(temp_dt[0],2) & int(8192) else 1) == 1:#判断是否GPRS信号
                GPRS_fact_num += 1
                if flag1 == 4:
                    flag2 = 1
                    signal_change_points.append(data_time_temp2)
                    signal_change_point_temp2 = data_time_temp2
                    LBS_TIME += signal_change_point_temp2 - signal_change_point_temp1
                    signal_change_point_temp1 = signal_change_point_temp2
                elif (int(temp_dt[0],2) & int(32)):#5分钟一次
                    if (flag1 == 1) and (data_time_temp2-data_time_temp1) > (5*60+60):
                        GPRS_loss_num += (round((data_time_temp2-data_time_temp1)/60/5)-1)
                        GPRS_loss_point.append(datetime_toString(temp_dt[1]))
                    flag1 =1
                elif (int(temp_dt[0],2) & int(2048)):#10分钟一次
                    if (flag1 == 2) and (data_time_temp2-data_time_temp1) > (10*60+120):
                        GPRS_loss_num += (round((data_time_temp2-data_time_temp1)/60/10)-1)
                        GPRS_loss_point.append(datetime_toString(temp_dt[1]))
                    flag1 =2  
                else:#2分钟一次
                    if (flag1 == 3) and (data_time_temp2-data_time_temp1) > (2*60+40):
                        GPRS_loss_num += (round((data_time_temp2-data_time_temp1)/60/2)-1)
                        GPRS_loss_point.append(datetime_toString(temp_dt[1]))
                    flag1 =3           
            elif (2 if int(temp_dt[0],2) & int(8192) else 1) == 2:#判断是否北斗信号    
                LBS_fact_num += 1 
                if not (flag1 == 4):
                    signal_change_points.append(data_time_temp2)
                    signal_change_point_temp2 = data_time_temp2
                    GPRS_TIME += signal_change_point_temp2 - signal_change_point_temp1
                    signal_change_point_temp1 = signal_change_point_temp2
                else:
                    if  flag2 == 1 and (data_time_temp2-data_time_temp1) > (2*60+40):
                        LBS_loss_num += (round((data_time_temp2-data_time_temp1)/60/2)-1) 
                        LBS_loss_point.append(datetime_toString(temp_dt[1]))
                    elif flag2 == 1 and (data_time_temp2-data_time_temp1) > (5*60-40) and (data_time_temp2-data_time_temp1) < (5*60+40):
                        flag2 = 2
                    elif flag2 == 2 and (data_time_temp2-data_time_temp1) > (5*60+60):
                        LBS_loss_num += (round((data_time_temp2-data_time_temp1)/60/5)-1)
                        LBS_loss_point.append(datetime_toString(temp_dt[1]))
                flag1 = 4 
            elif int(temp_dt[0],2) & int(49152):
                print('-------------------存在报警信息-------------------------------') 
            data_time_temp1=data_time_temp2
        signal_change_point_temp2 = (flag + 1)*3600*24
        if not flag1 == 4:
            GPRS_TIME += signal_change_point_temp2-signal_change_point_temp1
        else:
            LBS_TIME += signal_change_point_temp2-signal_change_point_temp1
        signal_change_point_temp1 = signal_change_point_temp2           
    flag +=1
    process_day += datetime.timedelta(1)
    signal_change_points.append(flag*3600*24) 
print('GPRS_fact_num:'+ str(GPRS_fact_num))
print('GPRS_loss_num:'+ str(GPRS_loss_num))
print('LBS_fact_num:'+ str(LBS_fact_num))
print('LBS_loss_num:'+ str(LBS_loss_num))
print('GPRS_TIME:'+ str(GPRS_TIME))
print('LBS_TIME:'+ str(LBS_TIME))
print('signal_change_points:'+ str(signal_change_points))
print('GPRS_loss_point:'+ str(GPRS_loss_point))
print('LBS_loss_point:'+ str(LBS_loss_point))
if GPRS_fact_num == 0:
    gprs_success = None
else:
    gprs_success =  GPRS_fact_num/(GPRS_fact_num + GPRS_loss_num)
if LBS_fact_num == 0:
    lbs_success = None
else:
    lbs_success =  LBS_fact_num/(LBS_fact_num + LBS_loss_num)
if GPRS_TIME + LBS_TIME == 0:
    gprs_time_percent = None
else:
    gprs_time_percent = GPRS_TIME/(GPRS_TIME + LBS_TIME)

return   gprs_success,lbs_success,gprs_time_percent   

def get_device_nos():
get_device_nos_by_date_cmd = ("select distinct(DEVICE_NO) from t_device where DEVICE_TYPE = 2")
temp_device_nos = execute_sql(get_device_nos_by_date_cmd)
device_nos=[]
for temp_dt in temp_device_nos:
device_nos.append(temp_dt[0])
return device_nos

count_100=0
count_95=0
count_90=0
count_85=0
count_80=0
count_75=0
count_70=0
count_65=0
count_60=0
count_55=0
count_50=0
count_45=0
count_40=0
count_35=0
count_30=0
count_25=0
count_20=0
count_15=0
device_nos = get_device_nos()
for device_no in device_nos:
print('---------------------------------------------------------------------------------------------------------')
print('ship which is now handled is device_no:'+ str(device_no))
gprs_success,lbs_success,gprs_time_percent = get_signal_strength_by_device_id_and_signal_type(from_date, to_date,device_no)
if not gprs_success ==None:
if(gprs_success<=1.0 and gprs_success>0.95):
count_100+=1
if(gprs_success<=0.95 and gprs_success>0.9):
count_95+=1
if(gprs_success<=0.9 and gprs_success>0.85):
count_90+=1
if(gprs_success<=0.85 and gprs_success>0.80):
count_85+=1
if(gprs_success<=0.8 and gprs_success>0.75):
count_80+=1
if(gprs_success<=0.75 and gprs_success>0.70):
count_75+=1
if(gprs_success<=0.7 and gprs_success>0.65):
count_70+=1
if(gprs_success<=0.65 and gprs_success>0.60):
count_65+=1
if(gprs_success<=0.6 and gprs_success>0.55):
count_60+=1
if(gprs_success<=0.55 and gprs_success>0.50):
count_55+=1
if(gprs_success<=0.50 and gprs_success>0.45):
count_50+=1
if(gprs_success<=0.45 and gprs_success>0.40):
count_45+=1
if(gprs_success<=0.40 and gprs_success>0.35):
count_40+=1
if(gprs_success<=0.35 and gprs_success>0.30):
count_35+=1
if(gprs_success<=0.30 and gprs_success>0.25):
count_30+=1
if(gprs_success<=0.25 and gprs_success>0.20):
count_25+=1
if(gprs_success<=0.20 and gprs_success>0.15):
count_20+=1
if(gprs_success<=0.15):
count_15+=1

name_list = ['95%-100%','90%-95%','85%-90%','80%-85%','75%-80%','70%-75%','65%-70%','60%-65%','55%-60%','50%-55%','45%-50%','40%-45%','35%-40%','30%-35%','25%-30%','20%-25%','15%-20%','0%-15%']
num_list = [count_100,count_95,count_90,count_85,count_80,count_75,count_70,count_65,count_60,count_55,count_50,count_45,count_40,count_35,count_30,count_25,count_20,count_15]
num_list1 = [0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2]
x =list(range(len(num_list)))
total_width, n = 0.8, 2
width = total_width / n
myfont = matplotlib.font_manager.FontProperties(fname=r'C:/Windows/Fonts/simsun.ttc',size=14) # 这一行
mpl.rcParams['font.sans-serif'] = ['FangSong'] # 指定默认字体
plt.title('GPRS统计', fontproperties=myfont)
rect1 = plt.bar(x, num_list, width=width,label='成功率',fc = 'lightskyblue')
plt.legend((rect1,),("123",))
for i in range(len(x)):
x[i] = x[i] + width
plt.bar(x, num_list1, width=width, label=u'占比',tick_label = name_list,fc = 'yellowgreen')
plt.legend()
plt.show()