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
]
