import pandas as pd
from datetime import timedelta
con ='postgresql://postgres:1@10.101.0.178/rpt_repair'
sql ='select a.id, a.procedure,a.content, a.failure_begin, a.repair_start, a.repair_end, a.debug_end,a.workers,b.name from simple_enroll_simplerollinrecord as a left join simple_enroll_failuretype as b on a.failure_type_id=b.id'
sql +=' where failure_begin >= \'2020-07-01\' and failure_begin < \'2020-07-31\''
defban(x):
ban =''
day_range=[8,9,10,11,12,13,14,15,16,17,18,19]
night_range =[0,1,2,3,4,5,6,7,20,21,22,23]
dt = x.loc['failure_begin']
dt2 = x.loc['debug_end']if dt.timetuple().tm_hour in day_range and dt2.timetuple().tm_hour in day_range:
ban ='白'if dt.timetuple().tm_hour in night_range and dt2.timetuple().tm_hour in night_range:
ban ='夜'#if ban == '':# #print(x)return ban
# 维修人参与次数排名defranking(q):from collections import Counter
itr=q.iterrows()
workers =[]
w =''for index, row in q.iterrows():
t = row.loc['workers'].split(',')
workers += t
result = Counter(workers)
ss =sorted(result.items(),key=lambda p: p[1], reverse=True)for s in ss:print(s[0],'\t',s[1])return workers
def 技能(procedure,workers, sql):
result =[]for w in workers:
sql_per = sql +' and workers like \'%%'+ w +'%%\''# print(sql_per)
q_zhangjias = prepare(sql_per, con)
q_zhangjias_jg = q_zhangjias.query('procedure=="%s"'% procedure)
t = q_zhangjias_jg.agg({'次数':['sum'],'维修':['sum','mean']})
result.append({'姓名':w,'次数':'%d'% t['次数']['sum'],'平均维修':'%.1f'% t['维修']['mean']})print('姓名','\t','次数','\t','平均维修时间(分)')for r in result:print(r['姓名'],'\t',r['次数'],'\t',r['平均维修'])