2018-09-29渔船数据的信号传输成功率报表（全部统计）
-- coding: utf-8 --
import os
import datetime
import pymysql
import matplotlib
import matplotlib.pyplot as plt
from pylab import mpl
import time
import xlwt
1、设置可视化绘图参数
def parse_datetime(s):
try:
if s.find(' ') != -1:
return datetime.datetime.strptime(s, "%Y-%m-%d %H:%M:%S")
else:
return datetime.datetime.strptime(s, "%Y-%m-%d")
except:
print ("string '%s' is not a valid date or datetime")
def datetime_toString(dt):
return dt.strftime("%Y-%m-%d %H:%M:%S")
def datetime_toTimestamp(dt):
return time.mktime(dt.timetuple())
def timestamp_toString(sp):
return time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(sp))
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_info_by_device_no(from_date, to_date,device_no):
print ("Partition ship data by device_no")
process_day = from_date
rs =[]
signal_change_points= []
data_time_temp1 = 0
data_time_temp2 = 0
signal_change_point_temp1 = 0
signal_change_point_temp2 = 0
pre_temp_dts =[]
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分钟一次的标志位
flag3 = 1 #判断第12标志位为1时出现10分钟一次的GPRS信号
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))
pre_temp_dts = execute_sql(get_ship_data_by_date_cmd)
if pre_temp_dts == ():
print('--------------device_no为'+device_no+'在'+str(process_day.date())+'这天没有数据')
else:
for pre_temp_dt in pre_temp_dts:
temp_dts.append({'ALARM_STATUS': str(pre_temp_dt[0]), 'ACQ_TIME': pre_temp_dt[1], 'DATA_TYPE': pre_temp_dt[2]})
flag +=1
process_day += datetime.timedelta(1)
if (temp_dts) == []:
print('--------------device_no为'+device_no+'在这几天都没有数据')
else:
if (int(temp_dts[0].get('ALARM_STATUS'),2) & int(8192)):
data_type_begin = 2
flag1 = 4
else:
data_type_begin = 1
data_time_temp1 = datetime_toTimestamp(temp_dts[0].get('ACQ_TIME'))
signal_change_point_temp1 = data_time_temp1
signal_change_points.append(timestamp_toString(data_time_temp1))
for temp_dt in temp_dts:
data_time_temp2 = datetime_toTimestamp(temp_dt.get('ACQ_TIME'))#下一个点的时间以秒数计算
if (2 if int(temp_dt.get('ALARM_STATUS'),2) & int(8192) else 1) == 1:#判断是否GPRS信号
GPRS_fact_num += 1
if flag1 == 4:
flag2 = 1
signal_change_points.append(timestamp_toString(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
if (int(temp_dt.get('ALARM_STATUS'),2) & int(32)):#5分钟一次
flag1 =1
elif (int(temp_dt.get('ALARM_STATUS'),2) & int(2048)):#10分钟一次
flag1 =2
else:#2分钟一次
flag1 =3
elif (int(temp_dt.get('ALARM_STATUS'),2) & int(32)):#5分钟一次
if (flag1 == 1) and (data_time_temp2-data_time_temp1) > (560+60):
GPRS_loss_num += (round((data_time_temp2-data_time_temp1)/60/5)-1)
GPRS_loss_point.append(datetime_toString(temp_dt.get('ACQ_TIME')))
flag1 =1
elif (int(temp_dt.get('ALARM_STATUS'),2) & int(2048)):#10分钟一次
if (flag1 == 2) and (flag3 == 1) and (data_time_temp2-data_time_temp1) < (1060+60) and (data_time_temp2-data_time_temp1) > (1060-60):
print('--------------------警告：设备号为'+device_no+'第12标志位为1时出现10分钟一次的GPRS信号----------------------------')
flag3 = 2
if (flag1 == 2) and (data_time_temp2-data_time_temp1) > (1060+120):
GPRS_loss_num += (round((data_time_temp2-data_time_temp1)/60/10)-1)
GPRS_loss_point.append(datetime_toString(temp_dt.get('ACQ_TIME')))
flag1 =2
else:#2分钟一次
if (flag1 == 3) and (data_time_temp2-data_time_temp1) > (260+40):
GPRS_loss_num += (round((data_time_temp2-data_time_temp1)/60/2)-1)
GPRS_loss_point.append(datetime_toString(temp_dt.get('ACQ_TIME')))
flag1 =3
elif (2 if int(temp_dt.get('ALARM_STATUS'),2) & int(8192) else 1) == 2:#判断是否北斗信号
LBS_fact_num += 1
if not (flag1 == 4):
signal_change_points.append(timestamp_toString(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) > (260+40):
LBS_loss_num += (round((data_time_temp2-data_time_temp1)/60/2)-1)
LBS_loss_point.append(datetime_toString(temp_dt.get('ACQ_TIME')))
elif flag2 == 1 and (data_time_temp2-data_time_temp1) > (560-40) and (data_time_temp2-data_time_temp1) < (560+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.get('ACQ_TIME')))
flag1 = 4
elif int(temp_dt.get('ALARM_STATUS'),2) & int(49152):
print('-------------------存在报警信息-------------------------------')
data_time_temp1=data_time_temp2
signal_change_point_temp2 = datetime_toTimestamp(temp_dts[-1].get('ACQ_TIME'))
signal_change_points.append(timestamp_toString(signal_change_point_temp2))
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
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
lbs_time_percent = None
else:
gprs_time_percent = GPRS_TIME/(GPRS_TIME + LBS_TIME)
lbs_time_percent = LBS_TIME/(GPRS_TIME + LBS_TIME)
return gprs_success,lbs_success,gprs_time_percent,lbs_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_device_no in temp_device_nos:
device_nos.append(temp_device_no[0])
return device_nos
def data_toExcel():
workbook = xlwt.Workbook(encoding='utf-8')
booksheet = workbook.add_sheet('Sheet 1', cell_overwrite_ok=True)
c = ['设备NO', '统计数据时间区间','GPRS成功率', 'GPRS占比', '北斗成功率','北斗占比','设备代数']
#用于初步处理后的数据存储
data_toexcel = []
for i in range(7):
booksheet.write(0, i, c[i])
device_nos = get_device_nos()
flag = 1
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,lbs_time_percent = get_signal_info_by_device_no(from_date, to_date,device_no)
data_toexcel.append({"DEVICE_NO": device_no, "COUNT_TIME_INTERVAL": '2018-08-26~2018-08-28', "GPRS_SUCCESS": gprs_success, "GPRS_TIME_PERCENT": gprs_time_percent,\
"LBS_SUCCESS": lbs_success,"LBS_TIME_PERCENT": lbs_time_percent, "DEVICE_TYPE": '二代'})
booksheet.write(flag, 0, data_toexcel[flag-1].get('DEVICE_NO'))
booksheet.write(flag, 1, data_toexcel[flag-1].get('COUNT_TIME_INTERVAL'))
booksheet.write(flag, 2, data_toexcel[flag-1].get('GPRS_SUCCESS'))
booksheet.write(flag, 3, data_toexcel[flag-1].get('GPRS_TIME_PERCENT'))
booksheet.write(flag, 4, data_toexcel[flag-1].get('LBS_SUCCESS'))
booksheet.write(flag, 5, data_toexcel[flag-1].get('LBS_TIME_PERCENT'))
booksheet.write(flag, 6, data_toexcel[flag-1].get('DEVICE_TYPE'))
flag += 1
dataname = r'D:/设备数据报表.xls'
workbook.save(dataname)
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()
gprs_success,lbs_success,gprs_time_percent,lbs_time_percent = get_signal_info_by_device_no(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()
