def week_farm_index(query_condition): (conn,cur)= connectDB.sqlite() sqlstr='SELECT * FROM farm_index' res=pd.read_sql(sqlstr,con=conn) conn.close() return res
def export(self,power,stime,etime): (conn, cur) = connectDB.sqlite() sql_que="SELECT * FROM power WHERE wtgs_id=\'"+str(self.wtgs_path['wtgs_id'].iloc[0])+"\' AND time>=\'"+stime+"\' AND time<=\'"+etime+"\'" res=pd.read_sql(sql_que,con=conn) power.index=power['time'].tolist() if len(res)>0: for row in range(len(res)): power['power_ib'].loc[res['time'].iloc[row]]=str(res['power_ib'].iloc[row]) power['power_bd'].loc[res['time'].iloc[row]] = str(res['power_bd'].iloc[row]) power=np.array(power) sqlvul='(\'' for item in power: if not item[4]: item[4]='' if not item[5]: item[5] = '' sqlvul+="\',\'".join(item) sqlvul +="\'),(\'" # print(sqlvul[:-3]) sqlstr = "REPLACE INTO power VALUES " sqlstr += sqlvul[:-3] # print(sqlstr) try: cur.execute(sqlstr) conn.commit() except: pass conn.close()
def sqlserver_wtgs_path_by_farm_code(farm_code): #function: 通过风场代号查询actionlist库中风场所有风机具体路径 (conn, cur) = connectDB.sqlite() sqlstr = "SELECT * FROM farm_path_sqlserver WHERE farm_code=\'"+str(int(farm_code))+"\'" wtgs_paths = pd.read_sql(sqlstr, con=conn) conn.close # 关闭连接 return wtgs_paths
def sqlserver_wtgs_path_by_wtgs_id(wtgs_id): # function: 通过机组代号查询ib库中风场所有风机具体路径 (conn, cur) = connectDB.sqlite() sqlstr = "SELECT * FROM farm_path_sqlserver WHERE wtgs_id=\'" + str(int(wtgs_id)) + "\'" wtgs_path = pd.read_sql(sqlstr, con=conn) conn.close # 关闭连接 return wtgs_path
def farm_path_sqlserver(query_condition): sqlstr = 'SELECT farm_code,farm_name,wtgs_id,db,wtgs_name from farm_path_sqlserver' if 'wtgs_id' in query_condition.keys(): sqlstr += " WHERE wtgs_id=\'" + "\' or wtgs_id=\'".join(query_condition['wtgs_id']) + "\' ORDER BY wtgs_id" elif 'farm_code' in query_condition.keys(): sqlstr += " WHERE farm_code=\'" + "\' or farm_code=\'".join(query_condition['farm_code']) + "\' ORDER BY wtgs_id" else: sqlstr += " ORDER BY wtgs_id" # print(sqlstr) (conn, cur) = connectDB.sqlite() res = pd.read_sql(sqlstr, con=conn) conn.close() return res
def farm_tag(query_condition): sqlstr = 'SELECT farm_code,farm_name,' if 'farm_code' in query_condition.keys() and 'wtgs_id' in query_condition.keys(): sqlstr += "wtgs_id,power from golden_tag WHERE wtgs_id=\'" + "\' or wtgs_id=\'".join(query_condition['wtgs_id']) + "\' ORDER BY wtgs_id" elif 'farm_code' in query_condition.keys(): sqlstr += "wtgs_id,power from golden_tag WHERE farm_code=\'" + "\' or farm_code=\'".join(query_condition['farm_code']) + "\' ORDER BY wtgs_id" else: sqlstr += "wtgs_id,power from golden_tag ORDER BY wtgs_id" # print(sqlstr) (conn, cur) = connectDB.sqlite() res = pd.read_sql(sqlstr, con=conn) conn.close() return res
def match(type, status_code): conn, cur = connectDB.sqlite() if type == 1: sqlstr = "SELECT fault_name,fault_group FROM status_code_cfg_1p5 WHERE status_code==\'" + status_code + "\'" else: sqlstr = "SELECT fault_name,fault_group FROM status_code_cfg_2p0 WHERE status_code==\'" + status_code + "\'" res = pd.read_sql(sqlstr, con=conn) if len(res) > 0: fault_name = res['fault_name'].iloc[0] fault_group = res['fault_group'].iloc[0] else: fault_name = [] fault_group = [] return fault_name, fault_group
def power2(query_condition): sqlstr0 = "SELECT * from power WHERE time BETWEEN \'" + query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" if 'wtgs_id' in query_condition.keys(): #多个机组 sqlstr1 = "SELECT * FROM (" + sqlstr0 + ") WHERE wtgs_id=\'" + "\' or farm_code=\'".join(query_condition['wtgs_id']) + "\'" elif 'farm_code' in query_condition.keys(): # 多个风场 sqlstr1 = "SELECT * FROM ("+sqlstr0+") WHERE farm_code=\'" + "\' or farm_code=\'".join(query_condition['farm_code']) + "\'" else: # 所有风场 pass # print(sqlstr1) (conn,cur)= connectDB.sqlite() res=pd.read_sql(sqlstr1,con=conn) conn.close() print(res) return res
def power(query_condition): sqlstr = "SELECT farm_code,farm_name,wtgs_id,time,power_ib,power_bd,power_golden from power WHERE" if 'farm_code' in query_condition.keys() and 'wtgs_id' in query_condition.keys(): # 单个机组 sqlstr+=" farm_code=\'"+str(query_condition['farm_code'])+"\' AND wtgs_id=\'"+str(query_condition['wtgs_id'])+"\' AND time BETWEEN \'"+query_condition['start_time'] +"\' AND \'"+query_condition['end_time']+"\'" elif 'farm_code' in query_condition.keys(): # 单个风场 sqlstr += " farm_code=\'" + str(query_condition['farm_code']) + "\' AND time BETWEEN \'" + query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" else: # 所有风场 sqlstr += " time BETWEEN \'" + query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" # print(sqlstr) (conn,cur)= connectDB.sqlite() res=pd.read_sql(sqlstr,con=conn) conn.close() # #print(res) return res
def farm_path_ib(query_condition): sqlstr = 'SELECT farm_code,farm_name,' if 'farm_code' in query_condition.keys() and 'wtgs_id' in query_condition.keys(): sqlstr += "wtgs_id,host,port,db,table_name,wtgs_name from farm_path_ib WHERE farm_code=\'" + \ str(query_condition['farm_code']) + "\' AND wtgs_id=\'" + str(query_condition['wtgs_id']) + "\' ORDER BY wtgs_id" elif 'farm_code' in query_condition.keys(): sqlstr += "wtgs_id,host,port,db,table_name,wtgs_name from farm_path_ib WHERE farm_code=\'" + \ str(query_condition['farm_code']) + "\' ORDER BY wtgs_id" else: sqlstr += "wtgs_id,host,port,db,table_name,wtgs_name from farm_path_ib ORDER BY wtgs_id" # #print(sqlstr) (conn, cur) = connectDB.sqlite() res = pd.read_sql(sqlstr, con=conn) conn.close() return res
def farmcodeDirectory(): # 查询风场及机组 (conn,cur)= connectDB.sqlite() sqlstr="SELECT farm_name,farm_code,wtgs_id FROM farm_path_sqlserver ORDER BY farm_code" farm_name_code=pd.read_sql(sqlstr,con=conn) conn.close # 关闭连接 farmDir={} wtgsDir={} for ifarm_code in sorted(list(set(farm_name_code['farm_code'].tolist()))): farmDir[farm_name_code[farm_name_code['farm_code']==ifarm_code]['farm_name'].iloc[0]]=int(ifarm_code) for ifarm_code in sorted(list(set(farm_name_code['farm_code'].tolist()))): iwtgsDir={} for iwtgs_id in sorted(list(set(farm_name_code[farm_name_code['farm_code']==ifarm_code]['wtgs_id'].tolist()))): iwtgsDir[str(int(iwtgs_id))]=int(iwtgs_id) wtgsDir[ifarm_code]=iwtgsDir return farmDir, wtgsDir
def fault_info(query_condition): sqlstr = 'SELECT farm_code,farm_name,' if 'farm_code' in query_condition.keys() and 'wtgs_id' in query_condition.keys(): sqlstr += "wtgs_id,start_time,end_time,duration,maintain_time,status_code,fault_name,fault_group from fault_info WHERE farm_code=\'" + query_condition[ 'farm_code'] + "\' AND wtgs_id=\'" + query_condition['wtgs_id'] + "\' AND start_time BETWEEN \'" + \ query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" elif 'farm_code' in query_condition.keys(): sqlstr += "wtgs_id,start_time,end_time,duration,maintain_time,status_code,fault_name,fault_group from fault_info WHERE farm_code=\'" + query_condition[ 'farm_code'] + "\' AND start_time BETWEEN \'" + \ query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" else: sqlstr += "wtgs_id,start_time,end_time,duration,maintain_time,status_code,fault_name,fault_group from fault_info WHERE start_time BETWEEN \'" + \ query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" # #print(sqlstr) (conn, cur) = connectDB.sqlite() res = pd.read_sql(sqlstr, con=conn) conn.close() return res
def com_quality(query_condition): sqlstr = 'SELECT farm_code,farm_name,' if 'farm_code' in query_condition.keys() and 'wtgs_id' in query_condition.keys(): sqlstr += "wtgs_id,wtgs_bd,start_time,end_time,duration,reason from com_quality WHERE farm_code=\'" + \ query_condition['farm_code'] + "\' AND wtgs_id=\'" + query_condition['wtgs_id'] + "\' AND start_time BETWEEN \'" + \ query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" elif 'farm_code' in query_condition.keys(): sqlstr += "wtgs_id,wtgs_bd,start_time,end_time,duration,reason from com_quality WHERE farm_code=\'" + \ query_condition['farm_code'] + "\' AND start_time BETWEEN \'" + \ query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" else: sqlstr += "wtgs_id,wtgs_bd,start_time,end_time,duration,reason from com_quality WHERE start_time BETWEEN \'" + \ query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" # #print(sqlstr) (conn, cur) = connectDB.sqlite() res = pd.read_sql(sqlstr, con=conn) conn.close() return res
def utilize(query_condition): sqlstr = 'SELECT farm_code,farm_name,' if 'farm_code' in query_condition.keys() and 'wtgs_id' in query_condition.keys(): sqlstr += "wtgs_id,wtgs_bd,time,fault_maintain_time,maintain_time_normal,stop_time_normal,utilize_time,utilize,info from utilize WHERE farm_code=\'" + \ query_condition['farm_code'] + "\' AND wtgs_id=\'" + query_condition['wtgs_id'] + "\' AND time BETWEEN \'" + \ query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" elif 'farm_code' in query_condition.keys(): sqlstr += "wtgs_id,wtgs_bd,time,fault_maintain_time,maintain_time_normal,stop_time_normal,utilize_time,utilize,info from utilize WHERE farm_code=\'" + \ query_condition[ 'farm_code'] + "\' AND time BETWEEN \'" + \ query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" else: sqlstr += "wtgs_id,wtgs_bd,time,fault_maintain_time,maintain_time_normal,stop_time_normal,utilize_time,utilize,info from utilize WHERE time BETWEEN \'" + \ query_condition['start_time'] + "\' AND \'" + query_condition['end_time'] + "\'" # #print(sqlstr) (conn, cur) = connectDB.sqlite() res = pd.read_sql(sqlstr, con=conn) conn.close() return res
def record(): # workbook= win32com.client.Dispatch('Excel.Application').Workbooks.Open('D:/work/周报表/9.3-9.9/9.10工程运维停机机组处理进度跟进表.xlsx') # sht1 = workbook.Worksheets('9.3') # sht2 = workbook.Worksheets('9.4') # sht3 = workbook.Worksheets('9.5') # sht4 = workbook.Worksheets('9.6') # sht5 = workbook.Worksheets('9.7') # sht6 = workbook.Worksheets('9.8') # sht7 = workbook.Worksheets('9.9') # sht1.Rows(1).Delete() # sht2.Rows(1).Delete() # sht3.Rows(1).Delete() # sht4.Rows(1).Delete() # sht5.Rows(1).Delete() # sht6.Rows(1).Delete() # sht7.Rows(1).Delete() # workbook.Close() (conn, cur) = connectDB.sqlite() sqlstr = "SELECT DISTINCT farm_name FROM farm_path_ib" try: farm=pd.read_sql(sqlstr,con=conn) except: pass farm=farm['farm_name'].tolist() print(len(farm),farm) selectedRecord=[] for date in ['9.3','9.4','9.5','9.6','9.7','9.8','9.9']: recorddata=pd.read_excel('D:/work/周报表/9.3-9.9/9.10工程运维停机机组处理进度跟进表.xlsx',sheetname=date) for row in range(len(recorddata)): irecord=recorddata.ix[row:row+1] for ifarm in farm: print(irecord.tolist()) if irecord['项目名称'].iloc[0] in ifarm: print('yes') selectedRecord.append(irecord) da=pd.DataFrame(selectedRecord,columns=['运维中心','项目名称','项目状态 停机因素','异常机位','机组状态','故障类型', '故障描述','故障日期','停机天数','备件','处理进度 ','项目主管','预计恢复时间','备注']) da.to_excel('D:/work/周报表/9.3-9.9/运维筛选.xlsx')
def export(self): if len(self.updated_table_data)>0: for table_data in self.updated_table_data: (conn, cur) = connectDB.sqlite() sqlstr = "REPLACE INTO com_quality (farm_name,farm_code,wtgs_id,wtgs_bd,start_time,end_time,duration,reason) VALUES " value = '(\'' value+='\',\''.join(table_data) value += '\')' sqlstr += value try: # print(sqlstr) cur.execute(sqlstr) conn.commit() QMessageBox.question(self, "消息", "保存成功!", QMessageBox.Yes | QMessageBox.No) pass except: # print('insert error') pass conn.close() else: pass
def export(self): if len(self.updated_table_data)>0: for table_data in self.updated_table_data: (conn, cur) = connectDB.sqlite() sqlstr = "REPLACE INTO utilize (farm_name,farm_code,wtgs_id,wtgs_bd,time,fault_maintain_time,maintain_time_normal,stop_time_normal,utilize_time,utilize,info) VALUES " value = '(\'' value+='\',\''.join(table_data) value += '\')' sqlstr += value try: # print(sqlstr) cur.execute(sqlstr) conn.commit() QMessageBox.question(self, "消息", "保存成功!", QMessageBox.Yes | QMessageBox.No) pass except: # print('insert error') pass conn.close() else: pass
def export(self,power,time): (conn, cur) = connectDB.sqlite() sql_que="SELECT * FROM power WHERE wtgs_id=\'"+str(self.wtgs_path['wtgs_id'].iloc[0])+"\' AND time=\'"+time+"\'" res=pd.read_sql(sql_que,con=conn) if len(res)>0: sqlstr = "UPDATE power SET power_bd=\'"+str(power)+"\' WHERE wtgs_id=\'"+str(self.wtgs_path['wtgs_id'].iloc[0])+"\' AND time=\'"+time+"\'" try: cur.execute(sqlstr) conn.commit() except: #print(self.wtgs_path['wtgs_id'],time,power,'UPDATE error') pass else: sqlstr = "REPLACE INTO power (farm_code,farm_name,wtgs_id,time,power_bd) VALUES " value = "(\'" + str(self.wtgs_path['farm_code'].iloc[0]) + "\',\'" + str(self.wtgs_path['farm_name'].iloc[0]) + "\',\'" + self.wtgs_path['wtgs_id'].iloc[0] + "\',\'" + time + "\',\'" + str(power) + "\')" sqlstr += value try: cur.execute(sqlstr) conn.commit() except: #print(self.wtgs_path['wtgs_id'], time, power, 'insert error') pass conn.close()
def actionlist_fault(self, wtgs_path): fault_info = [] res = dataIO.actionlist_record(wtgs_path, self.start_time, self.end_time) ##print('原始数据',res) row = 0 while row < len(res): #print(len(res),row) if int(res['RUN_CODE'].iloc[row]) == 4 and str( res['IS_ERROR_STOP'].iloc[row]) == '是': # 机组故障维护时间 #print('故障:') farm_name = str(res['WINDFARM_BIAS_NAME'].iloc[row]) farm_code = str(int(res['TURBINE_NAME'].iloc[row][0:5])) wtgs_id = str(int(res['TURBINE_NAME'].iloc[row][6:14])) start_time = str(res['ACTION_TIME'].iloc[row]) # end_time=str(res['END_TIME'].iloc[row]) duration = str(res['DELTA_HOURS'].iloc[row]) status_code = str( res['FIRST_CODE_NOTE'].iloc[row] [res['FIRST_CODE_NOTE'].iloc[row].index(':') + 1:]) match_fault_name, match_fault_group = match( int(res['TURBINE_NAME'].iloc[row][0:1]), res['FIRST_CODE_NOTE'].iloc[row] [res['FIRST_CODE_NOTE'].iloc[row].index(':') + 1:]) # self.textinfo.emit(farm_name,wtgs_id,start_time,end_time,duration,status_code,match_fault_name,match_fault_group) if len(match_fault_name) > 0: fault_name = match_fault_name fault_group = match_fault_group else: fault_name = res['ERROR_STOP_GROUP'].iloc[row][:-4] fault_group = res['ERROR_STOP_GROUP'].iloc[row][:-4] rowj = row + 1 if rowj == len(res): row = len(res) break else: while rowj < len(res): # print(rowj,len(res),str(res['ACTION_STR'].iloc[rowj]),res['END_TIME'].iloc[rowj]) if str(res['ACTION_STR'].iloc[rowj]) in [ '环境停机', '环境待机', '电网故障停机' ]: delta = (datetime.datetime.strptime( str(res['ACTION_TIME'].iloc[rowj]), "%Y-%m-%d %H:%M:%S") - datetime.datetime.strptime( str(res['END_TIME'].iloc[row]), "%Y-%m-%d %H:%M:%S")) maintain_time = delta.seconds / 3600 + delta.days * 24 maintain_time = str(round(maintain_time, 4)) end_time = str(res['ACTION_TIME'].iloc[rowj]) row = rowj + 1 break elif str(res['ACTION_STR'].iloc[rowj]) == '机组启动' and ( rowj == len(res) - 1): # 最后一条是机组启动状态 delta = (datetime.datetime.strptime( str(res['ACTION_TIME'].iloc[rowj]), "%Y-%m-%d %H:%M:%S") - datetime.datetime.strptime( str(res['END_TIME'].iloc[row]), "%Y-%m-%d %H:%M:%S")) maintain_time = delta.seconds / 3600 + delta.days * 24 maintain_time = str(round(maintain_time, 4)) end_time = str(res['ACTION_TIME'].iloc[rowj]) row = rowj + 1 break elif str(res['ACTION_STR'].iloc[rowj]) == '机组启动' and ( rowj < (len(res) - 1)): if str(res['ACTION_STR'].iloc[rowj + 1]) == '并网运行': delta = (datetime.datetime.strptime( str(res['ACTION_TIME'].iloc[rowj]), "%Y-%m-%d %H:%M:%S") - datetime.datetime.strptime( str(res['END_TIME'].iloc[row]), "%Y-%m-%d %H:%M:%S")) maintain_time = delta.seconds / 3600 + delta.days * 24 maintain_time = str(round(maintain_time, 4)) end_time = str(res['ACTION_TIME'].iloc[rowj]) row = rowj + 1 break else: rowj += 1 continue elif str(res['ACTION_STR'].iloc[rowj]) == '并网运行': delta = (datetime.datetime.strptime( str(res['ACTION_TIME'].iloc[rowj]), "%Y-%m-%d %H:%M:%S") - datetime.datetime.strptime( str(res['END_TIME'].iloc[row]), "%Y-%m-%d %H:%M:%S")) maintain_time = delta.seconds / 3600 + delta.days * 24 maintain_time = str(round(maintain_time, 4)) end_time = str(res['ACTION_TIME'].iloc[rowj]) row = rowj + 1 break elif rowj == len(res) - 1 and str( res['ACTION_STR'].iloc[rowj] ) == '机组维护': # 截止到统计周期末端仍故障维护中 delta = (datetime.datetime.strptime( str(res['END_TIME'].iloc[rowj]), "%Y-%m-%d %H:%M:%S") - datetime.datetime.strptime( str(res['END_TIME'].iloc[row]), "%Y-%m-%d %H:%M:%S")) maintain_time = delta.seconds / 3600 + delta.days * 24 maintain_time = str(round(maintain_time, 4)) end_time = str(res['END_TIME'].iloc[rowj]) row = len(res) break else: rowj += 1 continue row = rowj + 1 fault_info.append([ farm_name, farm_code, wtgs_id, start_time, end_time, duration, maintain_time, status_code, fault_name, fault_group ]) elif str(res['ACTION_STR'].iloc[row]) in [ '机组维护', '机组故障停机', '就地停机', '远程停机', '电网故障停机', '环境停机' ]: # 机组不可用时间 # ##print('不可用',res[row:row+1]) farm_name = str(res['WINDFARM_BIAS_NAME'].iloc[row]) farm_code = str(int(res['TURBINE_NAME'].iloc[row][0:5])) wtgs_id = str(int(res['TURBINE_NAME'].iloc[row][6:14])) start_time = str(res['ACTION_TIME'].iloc[row]) end_time = str(res['END_TIME'].iloc[row]) duration = str(res['DELTA_HOURS'].iloc[row]) status_code = 'unavailable' fault_name = str(res['ACTION_STR'].iloc[row]) fault_group = str(res['ACTION_STR'].iloc[row]) maintain_time = '10000' fault_info.append([ farm_name, farm_code, wtgs_id, start_time, end_time, duration, maintain_time, status_code, fault_name, fault_group ]) row += 1 continue else: # ##print('其它', res[row:row+1]) row += 1 continue if len(fault_info) > 0: for i in range(len(fault_info)): sqlstr = "REPLACE INTO fault_info (farm_name,farm_code,wtgs_id,start_time,end_time,duration,maintain_time,status_code,fault_name,fault_group) VALUES " sqlstr += "(\'" + "\',\'".join(fault_info[i]) + "\')" self.textinfo.emit("\',\'".join(fault_info[i])) try: (conn, cur) = connectDB.sqlite() cur.execute(sqlstr) conn.commit() conn.close() except: conn.close() pass
def fault_info(self, wtgs_path): (conn, cur) = connectDB.sqlite() sqlstr = "SELECT * from fault_info WHERE wtgs_id=\'" + str( wtgs_path['wtgs_id'].iloc[0] ) + "\' AND end_time >= \'" + self.start_time + "\' AND start_time <=\'" + self.end_time + "\'" res = pd.read_sql(sqlstr, con=conn) conn.close() left_flag = 0 # 是否有跨越统计周期开始时间的故障标志位 span_flag = 0 # 是否有跨越整个统计周期的故障标志位 right_flag = 0 # 是否有跨越统计周期结束时间的故障标志位 middle_flag = 0 # 统计周期内的故障次数标志位 # if len(res)>0: fault_reocrd = res[res['status_code'] != 'unavailable'] fault_times = 0 fault_time = 0 unavailabel_time = 0 fault_plus_unavailabel_time = 0 for row in range(len(res)): if res['status_code'].iloc[row] != 'unavailable': fault_times += 1 if res['end_time'].iloc[row] > self.end_time and res[ 'start_time'].iloc[row] > self.start_time: #右侧 etime = datetime.datetime.strptime(str(self.end_time), "%Y-%m-%d %H:%M:%S") stime = datetime.datetime.strptime( str(res['start_time'].iloc[row]), "%Y-%m-%d %H:%M:%S") this_fault_time = round((etime - stime).seconds / 3600, 4) + (etime - stime).days * 24 elif res['end_time'].iloc[row] > self.end_time and res[ 'start_time'].iloc[row] < self.start_time: #横跨 span_flag = 1 this_fault_time = 168 elif res['end_time'].iloc[row] < self.end_time and res[ 'start_time'].iloc[row] < self.start_time: #左侧 etime = datetime.datetime.strptime( str(res['end_time'].iloc[row]), "%Y-%m-%d %H:%M:%S") stime = datetime.datetime.strptime(str(self.start_time), "%Y-%m-%d") this_fault_time = float( res['maintain_time'].iloc[row]) + round( (etime - stime).seconds / 3600, 4) + (etime - stime).days * 24 else: #中间 middle_flag += 1 this_fault_time = float(res['duration'].iloc[row]) + float( res['maintain_time'].iloc[row]) fault_time += this_fault_time fault_plus_unavailabel_time += this_fault_time elif res['fault_name'].iloc[row] not in ['环境停机', '远程停机', '电网故障停机']: if res['end_time'].iloc[row] > self.end_time and res[ 'start_time'].iloc[row] > self.start_time: #右侧 etime = datetime.datetime.strptime(str(self.end_time), "%Y-%m-%d %H:%M:%S") stime = datetime.datetime.strptime( str(res['start_time'].iloc[row]), "%Y-%m-%d %H:%M:%S") this_fault_time = +round((etime - stime).seconds / 3600, 4) + (etime - stime).days * 24 elif res['end_time'].iloc[row] > self.end_time and res[ 'start_time'].iloc[row] < self.start_time: #横跨 span_flag = 1 this_fault_time = 168 elif res['end_time'].iloc[row] < self.end_time and res[ 'start_time'].iloc[row] < self.start_time: #左侧 etime = datetime.datetime.strptime( str(res['end_time'].iloc[row]), "%Y-%m-%d %H:%M:%S") stime = datetime.datetime.strptime(str(self.start_time), "%Y-%m-%d") this_fault_time = round((etime - stime).seconds / 3600, 4) + (etime - stime).days * 24 else: #中间 middle_flag += 1 this_fault_time = float(res['duration'].iloc[row]) fault_time += this_fault_time fault_plus_unavailabel_time += this_fault_time # if res['end_time'].iloc[row]>self.end_time: # Stime = datetime.datetime.strptime(res['start_time'].iloc[row], "%Y-%m-%d %H:%M:%S") # Etime = datetime.datetime.strptime(self.end_time, "%Y-%m-%d %H:%M:%S") # this_fault_time=round((Etime-Stime).seconds/3600,4)+(Etime-Stime).days*24 # # fault_times += 1 # fault_time += this_fault_time # fault_plus_unavailabel_time += this_fault_time # else: # # fault_times += 1 # fault_time += float(res['duration'].iloc[row]) # fault_plus_unavailabel_time+= float(res['duration'].iloc[row]) # if fault_times>0: # mtbf=(168-fault_plus_unavailabel_time)/fault_times # else: # mtbf=168-fault_plus_unavailabel_time # availabel_p=(168-fault_plus_unavailabel_time)/168 delta_time = datetime.datetime.strptime( self.end_time, "%Y-%m-%d %H:%M:%S") - datetime.datetime.strptime( self.start_time, "%Y-%m-%d") if span_flag == 1 and fault_time > (delta_time.days * 24): fault_time -= delta_time.days * 24 fault_plus_unavailabel_time -= delta_time.days * 24 mtbf = (168 - fault_plus_unavailabel_time) / (middle_flag + 1) availabel_p = (168 - fault_plus_unavailabel_time) / 168 elif span_flag == 1 and fault_time == (delta_time.days * 24): mtbf = 0 availabel_p = 0 elif middle_flag == 0: mtbf = (168 - fault_plus_unavailabel_time) availabel_p = (168 - fault_plus_unavailabel_time) / 168 else: mtbf = (168 - fault_plus_unavailabel_time) / (middle_flag + 1) availabel_p = (168 - fault_plus_unavailabel_time) / 168 # else: # # 查询机组是否在本周内一直停机 # (conn, cur) = connectDB.sqlite() # sqlstr = "SELECT * from fault_info WHERE start_time<\'"+self.start_time+"\' and end_time LIKE \'2049%\' AND fault_name!=\'环境停机\' AND wtgs_id=\'"+wtgs_path['wtgs_id'].iloc[0]+"\'" # # print(sqlstr) # res = pd.read_sql(sqlstr, con=conn) # conn.close() # if len(res)>0: # fault_reocrd = [] # fault_times = 1 # fault_time = 168 # unavailabel_time = 0 # fault_plus_unavailabel_time = 168 # mtbf = 0 # availabel_p = 0 # else: # fault_reocrd = [] # fault_times = 0 # fault_time = 0 # unavailabel_time = 0 # fault_plus_unavailabel_time = 0 # mtbf = 168 # availabel_p = 1 return fault_reocrd, [ wtgs_path['farm_name'].iloc[0], wtgs_path['wtgs_id'].iloc[0], fault_times, fault_time, unavailabel_time, fault_plus_unavailabel_time, mtbf, availabel_p ]