-- coding: utf-8 --

import os import datetime import pymysql import matplotlib.pyplot as plt from pylab import mpl import time import xlwt import xlrd import threading import pandas as pd import glob from matplotlib.font_manager import FontProperties import sys import platform from shutil import copy from concurrent.futures import ThreadPoolExecutor from concurrent.futures import as_completed from DBUtils.PooledDB import PooledDB from future.types import no
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))
POOL = PooledDB(creator=pymysql,#リンクデータベースのモジュールmaxconnections=11,#接続プールで許可されている最大接続数,0とNoneは接続数mincached=3を制限しないことを示し,#初期化時にリンクプールで少なくとも作成された空きリンク,0はmaxcached=6を作成しないことを示し,#リンクプールで最大アイドルリンク,0とNoneはmaxshared=4を制限せず,#リンクプールで最大共有リンク数,0とNoneはすべて共有を示す.PS:無用,pymysqlやMySQLdbなどのモジュールのthreadsafetyはすべて1なので,すべての値がいくらに設定されても,maxcachedは常に0なので、すべてのリンクが共有されます.blocking=True、#接続プールで使用可能な接続がない場合、待機をブロックするかどうか.True、待つ;Falseは、待機せずにmaxusage=Noneとエラーを報告し、1つのリンクが最も繰り返し使用される回数を示します.Noneはsetsession=[]、#セッションを開始する前に実行されるコマンドのリストを示します.[set datestyle to...]のように、"set time zone ..."] ping=0,#ping MySQLサービス側、サービスが利用可能かどうかを確認します.#例えば、0=None=never、1=default=whenever it is requested、2=when a cursor is created、4=when a query is executed、7=always host='、port=、user='、password='、database='、charset='utf 8')
def execute_sql(sql_cmd): conn = POOL.connection() cursor = conn.cursor() cursor.execute(sql_cmd) results = cursor.fetchall() conn.close()

db = pymysql.connect(host='61.164.208.174',user='root',passwd='root',db='db_rcld_zj_statistics',port=8116,charset='utf8')

cursor = db.cursor()

cursor.execute(sql_cmd)

results = cursor.fetchall()

db.close()

return results

日付区間と漁船設備番号でデータを検索

def get_signal_info_by_device_no(from_date, to_date,device_no): print ("Partition ship data by device_no") process_day = from_date 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 = [] singnal_strength_sum = 0 cpu_tempreture_sum = 0 libv_count = 0 libv_sum = 0.0 pvbv_sum = 0.0 pvbv_count=0 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,SIGNAL_STRENGTH, CPU_TEMPRETURE,LIBV,PVBV 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 == () or pre_temp_dts==None: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],'SIGNAL_STRENGTH': pre_temp_dt[3],'CPU_TEMPRETURE': pre_temp_dt[4], 'LIBV': pre_temp_dt[5],'PVBV': pre_temp_dt[6]}) 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:
        
        libv_count += 1
        libv_sum += temp_dt.get('LIBV')
        
        times = str(temp_dt.get('ACQ_TIME'))
        seconds = (int(times[11])*10+int(times[12]))*3600+(int(times[14])*10+\
        int(times[15]))*60+(int(times[17])*10+int(times[18]))
        if  seconds >= (6*3600) and seconds <= (18*3600):
            pvbv_count += 1
            pvbv_sum += temp_dt.get('PVBV')

singnal_strength_sum += temp_dt.get('SIGNAL_STRENGTH')

cpu_tempreture_sum += temp_dt.get('CPU_TEMPRETURE')

        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) > (5*60+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) < (10*60+60) and (data_time_temp2-data_time_temp1) > (10*60-60):
                    print('-------------------- ： '+device_no+' 12 1 10 GPRS ----------------------------')
                    flag3 = 2
                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.get('ACQ_TIME')))
                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.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:
                    if (data_time_temp2-data_time_temp1) > (5*60-40) and (data_time_temp2-data_time_temp1) < (5*60+40):
                        flag2 = 2# 2 5 
                    else:
                        if (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.get('ACQ_TIME')))
                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)
if libv_count == 0:
    libv_average = None
else:
    libv_average = libv_sum/libv_count
if pvbv_count == 0:
    pvbv_average = None
else:
    pvbv_average = pvbv_sum/pvbv_count

return   gprs_success,lbs_success,gprs_time_percent,lbs_time_percent,libv_average, pvbv_average

def get_device_nos(dev_type): get_device_nos_by_date_cmd = ("select distinct(DEVICE_NO) from t_device where DEVICE_TYPE = %s"% (dev_type)) 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 chunks(device_nos, n): for i in range(0, len(device_nos), n): yield device_nos[i:i + n]
def get_excel(device_nos,num):

path = '.'
os.chdir(path)
tmp_path = './tmp'
if not os.path.exists(tmp_path):  
    os.mkdir(tmp_path)
outexcel_path = os.path.join(tmp_path, ' %s.xls' % (num))

flag_excel = 1 
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(9):
    booksheet.write(0, i, c[i])
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,libv_average,pvbv_average = get_signal_info_by_device_no(from_date, to_date,device_no)
    data_toexcel.append({"DEVICE_NO": device_no, "COUNT_TIME_INTERVAL": "%s-%s" % (from_date,to_date), "GPRS_SUCCESS": gprs_success, "GPRS_TIME_PERCENT": gprs_time_percent,\
              "LBS_SUCCESS": lbs_success,"LBS_TIME_PERCENT": lbs_time_percent, "DEVICE_TYPE": ' ', "LIBV_AVERAGE": libv_average,"PVBV_AVERAGE": pvbv_average}) 
    booksheet.write(flag_excel, 0, data_toexcel[flag_excel-1].get('DEVICE_NO'))
    booksheet.write(flag_excel, 1, data_toexcel[flag_excel-1].get('COUNT_TIME_INTERVAL'))
    booksheet.write(flag_excel, 2, data_toexcel[flag_excel-1].get('GPRS_SUCCESS'))
    booksheet.write(flag_excel, 3, data_toexcel[flag_excel-1].get('GPRS_TIME_PERCENT'))
    booksheet.write(flag_excel, 4, data_toexcel[flag_excel-1].get('LBS_SUCCESS'))
    booksheet.write(flag_excel, 5, data_toexcel[flag_excel-1].get('LBS_TIME_PERCENT'))
    booksheet.write(flag_excel, 6, data_toexcel[flag_excel-1].get('LIBV_AVERAGE'))
    booksheet.write(flag_excel, 7, data_toexcel[flag_excel-1].get('PVBV_AVERAGE'))
    booksheet.write(flag_excel, 8, data_toexcel[flag_excel-1].get('DEVICE_TYPE'))
    flag_excel += 1
    print('-----------------------flag_excel:'+str(flag_excel))

    workbook.save(outexcel_path)    

def excels_toOne_excel(dest_path=None, path= '.'): filearray=[] filelocation="./tmp/"for filename in glob.glob(filelocation+"*.xls"): filearray.append(filename) res=pd.read_excel(filearray[0]) for i in range(1,len(filearray)): A=pd.read_excel(filearray[i]) res=pd.concat([res,A],ignore_index=True) print(res.index) os.chdir(path) if dest_path is None: dest_path = os.path.join(path, '%s_%s' % (from_date.date(), to_date.date())) if not os.path.exists(dest_path): os.mkdir(dest_path) outfn = os.path.join(dest_path,u「漁船設備報告書.xlsx」)writer=pd.ExcelWriter(outfn) res.to_excel(writer,'sheet1') writer.save()""@summary:プラットフォームタイプに応じてフォントライブラリlinuxを処理:フォントライブラリをシステムフォントディレクトリにコピーし、windows:matplotを使用したフォントパラメータを手動でロードすればよい""def prepare_png_font(): font = None if platform.system() == 'Windows': mpl.rcParams['font.sans-serif']=['FangSong']#デフォルトフォントelseを指定:fontFile='/usr/share/fonts/SimHei.ttf' if not os.path.exists(fontFile) and os.path.exists('./SimHei.ttf'): copy('./SimHei.ttf', fontFile) font = FontProperties(fname=fontFile,size=17)

return font

def draw_signal(dev_type, from_date, to_date,dest_path=None,path='.'): fname = os.path.join(path,'./%s_%s/漁船設備報告書.xlsx'%(from_date.date()、to_date.date())) book = xlrd.open_workbook(fname) try: sheet=book.sheet_by_name(「sheet 1」)except:print(「ファイル%sにsheet 1が見つかりません.ファイルデータの読み取りに失敗しました.テーブルの名前を変更しますか?」%fname) nrows = sheet.nrows

gprs_success_count_100=0
gprs_success_count_90=0
gprs_success_count_80=0
gprs_success_count_70=0
gprs_success_count_60=0
gprs_success_count_50=0
gprs_success_count_40=0
gprs_success_count_30=0
gprs_success_count_20=0
gprs_success_count_10=0 
gprs_success_count_0=0 
gprs_success = 0.0
gprs_time_count_100=0
gprs_time_count_90=0
gprs_time_count_80=0
gprs_time_count_70=0
gprs_time_count_60=0
gprs_time_count_50=0
gprs_time_count_40=0
gprs_time_count_30=0
gprs_time_count_20=0
gprs_time_count_10=0 
gprs_time_count_0=0 
gprs_time_percent = 0.0

for j in range(1,nrows-1):
    if not sheet.cell(j,3).value == '':
        gprs_success = sheet.cell(j,3).value
    else:
        gprs_success = ''
    if not gprs_success == '':
        if(gprs_success == 1.0):
            gprs_success_count_100+=1
        if(gprs_success < 1.0 and gprs_success >= 0.9):
            gprs_success_count_90+=1
        if(gprs_success < 0.9 and gprs_success>=0.8):
            gprs_success_count_80+=1
        if(gprs_success< 0.8 and gprs_success>=0.7):
            gprs_success_count_70+=1
        if(gprs_success<0.7 and gprs_success>=0.6):
            gprs_success_count_60+=1
        if(gprs_success< 0.6 and gprs_success>=0.5):
            gprs_success_count_50+=1
        if(gprs_success<0.5 and gprs_success>= 0.4):
            gprs_success_count_40+=1
        if(gprs_success<0.4 and gprs_success>=0.3):
            gprs_success_count_30+=1
        if(gprs_success<0.3 and gprs_success>=0.2):
            gprs_success_count_20+=1
        if(gprs_success<0.2 and gprs_success>=0.1):
            gprs_success_count_10+=1
        if(gprs_success<0.1):
            gprs_success_count_0+=1
    if not sheet.cell(j,4).value == '':
        gprs_time_percent = sheet.cell(j,4).value
    else:
        gprs_time_percent = ''
    if not gprs_time_percent == '':
        if(gprs_time_percent == 1.0):
            gprs_time_count_100+=1
        if(gprs_time_percent < 1.0 and gprs_time_percent >= 0.9):
            gprs_time_count_90+=1
        if(gprs_time_percent < 0.9 and gprs_time_percent>=0.8):
            gprs_time_count_80+=1
        if(gprs_time_percent< 0.8 and gprs_time_percent>=0.7):
            gprs_time_count_70+=1
        if(gprs_time_percent<0.7 and gprs_time_percent>=0.6):
            gprs_time_count_60+=1
        if(gprs_time_percent< 0.6 and gprs_time_percent>=0.5):
            gprs_time_count_50+=1
        if(gprs_time_percent<0.5 and gprs_time_percent>= 0.4):
            gprs_time_count_40+=1
        if(gprs_time_percent<0.4 and gprs_time_percent>=0.3):
            gprs_time_count_30+=1
        if(gprs_time_percent<0.3 and gprs_time_percent>=0.2):
            gprs_time_count_20+=1
        if(gprs_time_percent<0.2 and gprs_time_percent>=0.1):
            gprs_time_count_10+=1
        if(gprs_time_percent<0.1):
            gprs_time_count_0+=1
gprs_name_list = ['100%','90%','80%','70%','60%','50%','40%','30%','20%','10%','<10%']
gprs_num_list = [gprs_success_count_100,gprs_success_count_90,gprs_success_count_80,gprs_success_count_70,gprs_success_count_60,gprs_success_count_50,gprs_success_count_40,gprs_success_count_30,gprs_success_count_20,gprs_success_count_10,gprs_success_count_0]
gprs_num_list1 = [gprs_time_count_100,gprs_time_count_90,gprs_time_count_80,gprs_time_count_70,gprs_time_count_60,gprs_time_count_50,gprs_time_count_40,gprs_time_count_30,gprs_time_count_20,gprs_time_count_10,gprs_time_count_0]
x =list(range(len(gprs_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']#デフォルトフォントを指定

kwargs = {}
font = prepare_png_font()
if font:
    kwargs['fontproperties'] = font
# 
plt.figure(figsize=(14, 7))
#  
plt.title(u" GPRS      ：%s  ：%s ~ %s" % (dev_type,from_date.date(), to_date.date()), **kwargs)
b1 = plt.bar(x, gprs_num_list, width=width,label=' ',tick_label = gprs_name_list,fc = 'lightskyblue')
for i in range(len(x)):    
    x[i] = x[i] + width
b2 = plt.bar(x, gprs_num_list1, width=width, label=u' ',fc = 'yellowgreen')
plt.legend()
plt.xlabel(' ')
plt.ylabel(' ')
for rect in b1+b2:
    h = rect.get_height()
    plt.text(rect.get_x()+rect.get_width()/2,h,'%d'%int (h),ha ='center',va='bottom')
# change to work path
os.chdir(path)
if dest_path is None:
    dest_path = os.path.join(path, '%s_%s' % (from_date.date(), to_date.date()))
if not os.path.exists(path):
    os.mkdir(path)
if not os.path.exists(dest_path):
    os.mkdir(dest_path)
outfn1 = os.path.join(dest_path, u"%s_%s GPRS .png" % (from_date.date(), to_date.date()))
plt.savefig(outfn1,dpi=300)

plt.show()

plt.close(0)

lbs_success_count_100=0
lbs_success_count_90=0
lbs_success_count_80=0
lbs_success_count_70=0
lbs_success_count_60=0
lbs_success_count_50=0
lbs_success_count_40=0
lbs_success_count_30=0
lbs_success_count_20=0
lbs_success_count_10=0 
lbs_success_count_0=0 
lbs_success_100_lists_by_device_no=[]
lbs_success_90_lists_by_device_no=[]
lbs_success_80_lists_by_device_no=[]
lbs_success_70_lists_by_device_no=[]
lbs_success_60_lists_by_device_no=[]
lbs_success_50_lists_by_device_no=[]
lbs_success_40_lists_by_device_no=[]
lbs_success_30_lists_by_device_no= []
lbs_success_20_lists_by_device_no= []
lbs_success_10_lists_by_device_no=[]
lbs_success_0_lists_by_device_no= []
lbs_success = 0.0
lbs_time_count_100=0
lbs_time_count_90=0
lbs_time_count_80=0
lbs_time_count_70=0
lbs_time_count_60=0
lbs_time_count_50=0
lbs_time_count_40=0
lbs_time_count_30=0
lbs_time_count_20=0
lbs_time_count_10=0 
lbs_time_count_0=0 
lbs_time_percent = 0.0

for j in range(1,nrows-1):
    if not sheet.cell(j,5).value == '':
        lbs_success = sheet.cell(j,5).value
    else:
        lbs_success = ''
    if not lbs_success == '':
        if(lbs_success == 1.0):
            lbs_success_count_100+=1
            lbs_success_100_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success < 1.0 and lbs_success >= 0.9):
            lbs_success_count_90+=1
            lbs_success_90_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success < 0.9 and lbs_success>=0.8):
            lbs_success_count_80+=1
            lbs_success_80_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success< 0.8 and lbs_success>=0.7):
            lbs_success_count_70+=1
            lbs_success_70_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success<0.7 and lbs_success>=0.6):
            lbs_success_count_60+=1
            lbs_success_60_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success< 0.6 and lbs_success>=0.5):
            lbs_success_count_50+=1
            lbs_success_50_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success<0.5 and lbs_success>= 0.4):
            lbs_success_count_40+=1
            lbs_success_40_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success<0.4 and lbs_success>=0.3):
            lbs_success_count_30+=1
            lbs_success_30_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success<0.3 and lbs_success>=0.2):
            lbs_success_count_20+=1
            lbs_success_20_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success<0.2 and lbs_success>=0.1):
            lbs_success_count_10+=1
            lbs_success_10_lists_by_device_no.append(sheet.cell(j,0).value)
        if(lbs_success<0.1):
            lbs_success_count_0+=1
            lbs_success_0_lists_by_device_no.append(sheet.cell(j,0).value)
    if not sheet.cell(j,6).value == '':
        lbs_time_percent = sheet.cell(j,6).value
    else:
        lbs_time_percent = ''
    if not lbs_time_percent == '':
        if(lbs_time_percent == 1.0):
            lbs_time_count_100+=1
        if(lbs_time_percent < 1.0 and lbs_time_percent >= 0.9):
            lbs_time_count_90+=1
        if(lbs_time_percent < 0.9 and lbs_time_percent>=0.8):
            lbs_time_count_80+=1
        if(lbs_time_percent< 0.8 and lbs_time_percent>=0.7):
            lbs_time_count_70+=1
        if(lbs_time_percent<0.7 and lbs_time_percent>=0.6):
            lbs_time_count_60+=1
        if(lbs_time_percent< 0.6 and lbs_time_percent>=0.5):
            lbs_time_count_50+=1
        if(lbs_time_percent<0.5 and lbs_time_percent>= 0.4):
            lbs_time_count_40+=1
        if(lbs_time_percent<0.4 and lbs_time_percent>=0.3):
            lbs_time_count_30+=1
        if(lbs_time_percent<0.3 and lbs_time_percent>=0.2):
            lbs_time_count_20+=1
        if(lbs_time_percent<0.2 and lbs_time_percent>=0.1):
            lbs_time_count_10+=1
        if(lbs_time_percent<0.1):
            lbs_time_count_0+=1
lbs_name_list = ['100%','90%','80%','70%','60%','50%','40%','30%','20%','10%','<10%']
lbs_num_list = [lbs_success_count_100,lbs_success_count_90,lbs_success_count_80,lbs_success_count_70,lbs_success_count_60,lbs_success_count_50,lbs_success_count_40,lbs_success_count_30,lbs_success_count_20,lbs_success_count_10,lbs_success_count_0]
lbs_num_list1 = [lbs_time_count_100,lbs_time_count_90,lbs_time_count_80,lbs_time_count_70,lbs_time_count_60,lbs_time_count_50,lbs_time_count_40,lbs_time_count_30,lbs_time_count_20,lbs_time_count_10,lbs_time_count_0]
x =list(range(len(lbs_num_list)))
total_width, n = 0.8, 2
width = total_width / n 
# 
plt.figure(figsize=(14, 7))
#  
plt.title(u"     ：%s  ：%s ~ %s" % (dev_type,from_date.date(), to_date.date()), **kwargs)
b1 = plt.bar(x, lbs_num_list, width=width,label=' ',tick_label = lbs_name_list,fc = 'lightskyblue')
for i in range(len(x)):    
    x[i] = x[i] + width
b2 = plt.bar(x, lbs_num_list1, width=width, label=u' ',fc = 'yellowgreen')
plt.legend()
plt.xlabel(' ')
plt.ylabel(' ')
for rect in b1+b2:
    h = rect.get_height()
    plt.text(rect.get_x()+rect.get_width()/2,h,'%d'%int (h),ha ='center',va='bottom')
# change to work path
outfn2 = os.path.join(dest_path, u"%s_%s .png" % (from_date.date(), to_date.date()))
plt.savefig(outfn2,dpi=300)

plt.show()

plt.close(0)

libv_average_count_6=0
libv_average_count_5=0
libv_average_count_4=0
libv_average_count_3=0
libv_average_count_2=0
libv_average_count_1=0
libv_average_count_0=0

libv_average_6_lists_by_device_no=[]
libv_average_5_lists_by_device_no=[]
libv_average_4_lists_by_device_no=[]
libv_average_3_lists_by_device_no=[]
libv_average_2_lists_by_device_no=[]
libv_average_1_lists_by_device_no=[]
libv_average_0_lists_by_device_no=[]
libv_average = 0.0

pvbv_average_count_3=0
pvbv_average_count_2_5=0
pvbv_average_count_2=0
pvbv_average_count_1_5=0
pvbv_average_count_1=0
pvbv_average_count_0_5=0
pvbv_average_count_0=0
pvbv_average_3_lists_by_device_no=[]
pvbv_average_2_5_lists_by_device_no=[]
pvbv_average_2_lists_by_device_no=[]
pvbv_average_1_5_lists_by_device_no=[]
pvbv_average_1_lists_by_device_no=[]
pvbv_average_0_5_lists_by_device_no=[]
pvbv_average_0_lists_by_device_no=[]
pvbv_average = 0.0

for j in range(1,nrows-1):
    if not sheet.cell(j,7).value == '':
        libv_average = sheet.cell(j,7).value
    else:
        libv_average = ''
    if not libv_average == '':
        if(libv_average>= 4.1):
            libv_average_count_6+= 1
            libv_average_6_lists_by_device_no.append(sheet.cell(j,0).value)
        if(libv_average < 4.1 and libv_average >=4.0):
            libv_average_count_5+= 1
            libv_average_5_lists_by_device_no.append(sheet.cell(j,0).value)
        if(libv_average < 4.0 and libv_average >=3.9):
            libv_average_count_4+= 1
            libv_average_4_lists_by_device_no.append(sheet.cell(j,0).value)
        if(libv_average <3.9 and libv_average >=3.8):
            libv_average_count_3+= 1
            libv_average_3_lists_by_device_no.append(sheet.cell(j,0).value)
        if(libv_average < 3.8 and libv_average >=3.7):
            libv_average_count_2 += 1
            libv_average_2_lists_by_device_no.append(sheet.cell(j,0).value)
        if(libv_average < 3.7 and libv_average >= 3.6):
            libv_average_count_1 += 1
            libv_average_1_lists_by_device_no.append(sheet.cell(j,0).value)
        if(libv_average < 3.6):
            libv_average_count_0 += 1
            libv_average_0_lists_by_device_no.append(sheet.cell(j,0).value)

    if not sheet.cell(j,8).value == '':
        pvbv_average = sheet.cell(j,8).value
    else:
        pvbv_average = ''
    if not pvbv_average == '':
        if(pvbv_average < 2.75 and pvbv_average >= 2.25):
            pvbv_average_count_3+= 1
            pvbv_average_3_lists_by_device_no.append(sheet.cell(j,1).value)
        if(pvbv_average < 2.25 and pvbv_average >= 1.75):
            pvbv_average_count_2_5+= 1
            pvbv_average_2_5_lists_by_device_no.append(sheet.cell(j,1).value)
        if(pvbv_average < 1.75 and pvbv_average >= 1.25):
            pvbv_average_count_2+= 1
            pvbv_average_2_lists_by_device_no.append(sheet.cell(j,1).value)
        if(pvbv_average < 1.25 and pvbv_average >= 0.75):
            pvbv_average_count_1_5+= 1
            pvbv_average_1_5_lists_by_device_no.append(sheet.cell(j,1).value)
        if(pvbv_average < 0.75 and pvbv_average >= 0.65):
            pvbv_average_count_1 += 1
            pvbv_average_1_lists_by_device_no.append(sheet.cell(j,1).value)
        if(pvbv_average < 0.65 and pvbv_average >= 0.45):
            pvbv_average_count_0_5 += 1
            pvbv_average_0_5_lists_by_device_no.append(sheet.cell(j,0).value)
        if(pvbv_average < 0.45 and pvbv_average >= 0.0):
            pvbv_average_count_0 += 1
            pvbv_average_0_lists_by_device_no.append(sheet.cell(j,1).value)
print('pvbv_average_count_0_5------------'+str(pvbv_average_count_0_5))
print(pvbv_average_0_5_lists_by_device_no)
lbs_name_list = [' 4.1(2.5)','4.0-4.1(2.0)','3.9-4.0(1.5)','3.8-3.9(1.0)','3.7-3.8(0.7)','3.6-3.7(0.5)',' 3.6( 0.45)']
lbs_num_list = [libv_average_count_6,libv_average_count_5,libv_average_count_4,libv_average_count_3,libv_average_count_2,libv_average_count_1,libv_average_count_0]
lbs_num_list1 = [pvbv_average_count_3,pvbv_average_count_2_5,pvbv_average_count_2,pvbv_average_count_1_5,pvbv_average_count_1,pvbv_average_count_0_5,pvbv_average_count_0]
x =list(range(len(lbs_num_list)))
total_width, n = 0.8, 2
width = total_width / n 
# 
plt.figure(figsize=(14, 7))
#  
plt.title(u"     ：%s  ：%s ~ %s" % (dev_type,from_date.date(), to_date.date()), **kwargs)
b1 = plt.bar(x, lbs_num_list, width=width,label=' ',tick_label = lbs_name_list,fc = 'lightskyblue')
for i in range(len(x)):    
    x[i] = x[i] + width
b2 = plt.bar(x, lbs_num_list1, width=width, label=u' ',fc = 'yellowgreen')
plt.legend()
plt.xlabel(' ')
plt.ylabel(' ')
for rect in b1+b2:
    h = rect.get_height()
    plt.text(rect.get_x()+rect.get_width()/2,h,'%d'%int (h),ha ='center',va='bottom')
# change to work path
outfn2 = os.path.join(dest_path, u"%s_%s .png" % (from_date.date(), to_date.date()))
plt.savefig(outfn2,dpi=300)

plt.show()

plt.close(0)

def get_parser(): from optparse import OptionParser parser = OptionParser("Usage: %prog [options] from_date to_date ") parser.add_option("-l", "--use-local", dest="use_local", default=False, action="store_true", help="whether to use local data for processing") parser.add_option("-p", "--working-path", dest="path", default='.', help="working path to save or load data, default: ./") parser.add_option("-i", "--device-no", dest="device_nos", default=[], action='append', help="ship identities, seperated by comma, string like 211,222,232") parser.add_option("-r", "--replace", dest="replace", default=False, action="store_true", help="to replace any result already done") return parser
if name == 'main':

sys.argv = ['', '2018-10-26', '2018-10-28'] 
parser = get_parser()
(options, argv) = parser.parse_args(sys.argv)

from_date = parse_datetime(argv[1])
to_date = parse_datetime(argv[2])   

dev_type = 3

device_nos = get_device_nos(dev_type)

threads=[]

num = 1

for i in list(chunks(device_nos, int(len(device_nos)/12))):

threads.append(threading.Thread(target=get_excel,args=(i,num) ))

num += 1

for thread in threads:

thread.start()

for thread in threads:

thread.join()

excels_toOne_excel()

draw_signal(dev_type, from_date, to_date)

num = 1

future = []

pool = ThreadPoolExecutor(max_workers=12)

for i in list(chunks(device_nos, int(len(device_nos)/12))):

future.append(pool.submit(get_excel,i,num))

num += 1

for f in future:

if f.running():

print('スレッド%s is running'%str(f))

for f in as_completed(future):

try:

ret = f.done()

if ret:

f_ret = f.result()

print('%s, done, result: %s, %s' % (str(f), f_ret.num, len(f_ret.content)))

except Exception as e:

f.cancel()

print(str(e))

excels_toOne_excel()

draw_signal(dev_type, from_date, to_date)