2018-09-29漁船データの信号伝送成功率レポート(全統計)
12442 ワード
-- 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_ポイント=[]flag=0#日付インクリメントflag 1=0#判定信号周波数変換用フラグビットflag 2=1#判定GPRSが北斗信号に移行した後の周波数が2分に1回か5分に1回かのフラグビットflag 3=1判定第12フラグビットが1の場合10分に1回の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_temp 2 if(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回flag 1=1 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(2048):#10分1回flag1=2 else:#2分1回flag 1=3 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回if(flag 1=1=1 elif=1 elif(flag=1=1=1 elif(int(int(temp_dt.dt.get..get('ALAR1)and(data_time_temp 2-data_time_temp 1)>(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分に1回if(flag 1==2)and(flag 3==1)and(data_time_temp 2-data_time_temp 1)<(1060+60)and(data_time_temp 2-data_time_temp 1)>(1060-60):print('-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------data_time_temp 2-data_time_temp 1>(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'))flag 1=2 else:#2分1回if(flag 1==3)and(data_time_temp 2-data_time_temp 1)>(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'))flag 1=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'))flag 1=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)rect 1=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_list 1,width=width,label=u'占有率',tick_label=name_list,fc='yellowgreen')plt.legend() plt.show()
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_ポイント=[]flag=0#日付インクリメントflag 1=0#判定信号周波数変換用フラグビットflag 2=1#判定GPRSが北斗信号に移行した後の周波数が2分に1回か5分に1回かのフラグビットflag 3=1判定第12フラグビットが1の場合10分に1回の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_temp 2 if(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回flag 1=1 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(2048):#10分1回flag1=2 else:#2分1回flag 1=3 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回if(flag 1=1=1 elif=1 elif(flag=1=1=1 elif(int(int(temp_dt.dt.get..get('ALAR1)and(data_time_temp 2-data_time_temp 1)>(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分に1回if(flag 1==2)and(flag 3==1)and(data_time_temp 2-data_time_temp 1)<(1060+60)and(data_time_temp 2-data_time_temp 1)>(1060-60):print('-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------data_time_temp 2-data_time_temp 1>(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'))flag 1=2 else:#2分1回if(flag 1==3)and(data_time_temp 2-data_time_temp 1)>(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'))flag 1=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'))flag 1=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)rect 1=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_list 1,width=width,label=u'占有率',tick_label=name_list,fc='yellowgreen')plt.legend() plt.show()
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_ポイント=[]flag=0#日付インクリメントflag 1=0#判定信号周波数変換用フラグビットflag 2=1#判定GPRSが北斗信号に移行した後の周波数が2分に1回か5分に1回かのフラグビットflag 3=1判定第12フラグビットが1の場合10分に1回の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_temp 2 if(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回flag 1=1 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(2048):#10分1回flag1=2 else:#2分1回flag 1=3 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回if(flag 1=1=1 elif=1 elif(flag=1=1=1 elif(int(int(temp_dt.dt.get..get('ALAR1)and(data_time_temp 2-data_time_temp 1)>(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分に1回if(flag 1==2)and(flag 3==1)and(data_time_temp 2-data_time_temp 1)<(1060+60)and(data_time_temp 2-data_time_temp 1)>(1060-60):print('-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------data_time_temp 2-data_time_temp 1>(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'))flag 1=2 else:#2分1回if(flag 1==3)and(data_time_temp 2-data_time_temp 1)>(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'))flag 1=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'))flag 1=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)rect 1=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_list 1,width=width,label=u'占有率',tick_label=name_list,fc='yellowgreen')plt.legend() plt.show()
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_ポイント=[]flag=0#日付インクリメントflag 1=0#判定信号周波数変換用フラグビットflag 2=1#判定GPRSが北斗信号に移行した後の周波数が2分に1回か5分に1回かのフラグビットflag 3=1判定第12フラグビットが1の場合10分に1回の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_temp 2 if(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回flag 1=1 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(2048):#10分1回flag1=2 else:#2分1回flag 1=3 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回if(flag 1=1=1 elif=1 elif(flag=1=1=1 elif(int(int(temp_dt.dt.get..get('ALAR1)and(data_time_temp 2-data_time_temp 1)>(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分に1回if(flag 1==2)and(flag 3==1)and(data_time_temp 2-data_time_temp 1)<(1060+60)and(data_time_temp 2-data_time_temp 1)>(1060-60):print('-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------data_time_temp 2-data_time_temp 1>(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'))flag 1=2 else:#2分1回if(flag 1==3)and(data_time_temp 2-data_time_temp 1)>(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'))flag 1=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'))flag 1=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)rect 1=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_list 1,width=width,label=u'占有率',tick_label=name_list,fc='yellowgreen')plt.legend() plt.show()
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_ポイント=[]flag=0#日付インクリメントflag 1=0#判定信号周波数変換用フラグビットflag 2=1#判定GPRSが北斗信号に移行した後の周波数が2分に1回か5分に1回かのフラグビットflag 3=1判定第12フラグビットが1の場合10分に1回の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_temp 2 if(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回flag 1=1 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(2048):#10分1回flag1=2 else:#2分1回flag 1=3 elif(int(temp_dt.get('ALARM_STATUSS')、2)&int(32):#5分1回if(flag 1=1=1 elif=1 elif(flag=1=1=1 elif(int(int(temp_dt.dt.get..get('ALAR1)and(data_time_temp 2-data_time_temp 1)>(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分に1回if(flag 1==2)and(flag 3==1)and(data_time_temp 2-data_time_temp 1)<(1060+60)and(data_time_temp 2-data_time_temp 1)>(1060-60):print('-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------data_time_temp 2-data_time_temp 1>(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'))flag 1=2 else:#2分1回if(flag 1==3)and(data_time_temp 2-data_time_temp 1)>(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'))flag 1=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'))flag 1=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)rect 1=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_list 1,width=width,label=u'占有率',tick_label=name_list,fc='yellowgreen')plt.legend() plt.show()