def get_cdfs(num):
cdfs = CalDailyForm().getList({}, CalDailyForm.date.desc(),
('dgp', 'davgs', 'date', 'mgp', 'agp'), 0,
int(num))['list']
newest_cdf = cdfs[0]
newest_date = datetime.datetime.strptime(newest_cdf['date'], "%Y-%m-%d")
year_rate = Utils.realRound(
newest_cdf['agp'] / GpPlan.getGpPlan(newest_date.year) / 10000, 4)
month_rate = Utils.realRound(
newest_cdf['mgp'] /
GpPlan.getGpPlan(newest_date.year, newest_date.month) / 10000, 4)
plan_rate = Utils.realRound(
GpPlan.getGpPlanUntil(newest_date.year, newest_date.month,
newest_date.day) /
GpPlan.getGpPlan(newest_date.year), 4)
res = {
'cdfs': cdfs,
'year_rate': float(year_rate),
'month_rate': float(month_rate),
'plan_rate': float(plan_rate)
}
return BaseController().successData(result=res, msg='获取成功')
def import_dlfs(dlfs_file='', year=2021, types='overwrite'):
"""
自动化对每日电量风速统计表进行读取同步
dlfs_file: 风机电量风速文件
year: 数据年份
types: insert 插入模式 / overwrite 覆盖模式
"""
if str(year) in config.keys():
cfg_dlfs = config[str(year)]['dlfs']
else:
cfg_dlfs = config['default']['dlfs']
dlfs = pd.read_excel(
dlfs_file
if dlfs_file else os.path.join(cfg_dlfs['dir'], cfg_dlfs['name']),
sheet_name=cfg_dlfs['sheet1']['name'],
usecols=range(column_index_from_string(cfg_dlfs['sheet1']['end_col'])),
skiprows=range(cfg_dlfs['sheet1']['start_row'] - 1),
header=None)
response = []
for x in range(cfg_dlfs['sheet1']['end_row'] -
cfg_dlfs['sheet1']['start_row']):
date = dlfs.loc[x].values[
column_index_from_string(cfg_dlfs['sheet1']['date_col']) -
1].replace(year=year)
if types == 'insert' and CalDailyForm.query.filter_by(
date=date).first():
continue # 如果数据库存在本日数据，那么跳过
if pd.isnull(dlfs.loc[x].values[1]):
# if cdf.loc[x].values[0] >= datetime.now():
break # 如果读到的数据不是浮点数类型，那么停止，用于判断是否到达表内空行
data = {
'date':
date,
'davgs1':
float(dlfs.loc[x].values[column_index_from_string(
cfg_dlfs['sheet1']['wind_speed_1_col']) - 1]),
'davgs2':
float(dlfs.loc[x].values[column_index_from_string(
cfg_dlfs['sheet1']['wind_speed_2_col']) - 1])
}
data['davgs'] = float(
Utils.realRound((data['davgs1'] + data['davgs2']) / 2, 2))
print(data)
if CalDailyForm().getOne(CalDailyForm, {CalDailyForm.date == date}):
CalDailyForm().edit(CalDailyForm, data,
{CalDailyForm.date == date})
else:
CalDailyForm().add(data)
return BaseController().successData(result=response, msg='风速数据更新成功！')
def get_weather_now():
res = requests.get(
'https://free-api.heweather.com/s6/weather/now?location=119.238801,36.09725&key=c4a0db75e5234137a88df8eccbcc7fe4'
).json()
return BaseController().successData(
result={
'tmp':
res['HeWeather6'][0]['now']['tmp'],
'cond_txt':
res['HeWeather6'][0]['now']['cond_txt'],
'wind_spd':
float(
Utils.realRound(
int(res['HeWeather6'][0]['now']['wind_spd']) / 3.6, 1))
})
def wtm_syn():
res = gzp_syn() # 现将工作票同步
whtj = pd.read_excel(EXCEL_PATH,
sheet_name='风机维护统计',
usecols=range(20),
header=None).fillna('')
gztj = pd.read_excel(EXCEL_PATH,
sheet_name='风机故障统计',
usecols=range(20),
header=None).fillna('')
# 维护
for x in range(len(whtj)):
if re.findall(r'^(A)(\d+)(\s*)(\d{5})$', whtj.loc[x].values[0]):
stop_time = whtj.loc[x].values[3]
zero_time = stop_time - datetime.timedelta(
hours=stop_time.hour,
minutes=stop_time.minute,
seconds=stop_time.second)
wt_id = int(
re.match(r'^(A)(\d+)(\s*)(\d{5})$',
whtj.loc[x].values[0]).group(2))
try:
gzp = Gzp.query.filter(
and_(Gzp.pstart_time < stop_time,
Gzp.pstart_time > zero_time,
Gzp.wts.any(id=wt_id))).first() # 这里可能会生成bug
wtm = WTMaintain()
is_in = False
for item in gzp.wtms:
if item == WTMaintain.query.filter(
WTMaintain.stop_time == whtj.loc[x].values[3],
WTMaintain.start_time ==
whtj.loc[x].values[4]).first():
wtm = item
is_in = True # 标定数据库中已存在
wtm.wt_id = wt_id
wtm.type = whtj.loc[x].values[1]
wtm.task = whtj.loc[x].values[2]
wtm.stop_time = whtj.loc[x].values[3]
wtm.start_time = whtj.loc[x].values[4]
wtm.time = Utils.realRound(
(wtm.start_time - wtm.stop_time).seconds / 3600, 2)
wtm.lost_power = Utils.realRound(float(whtj.loc[x].values[6]),
4)
if not is_in:
gzp.wtms.append(wtm)
if len(gzp.wtms.all()) == len(gzp.wts.all()):
gzp.is_end = 1
Gzp().add(gzp)
except (AttributeError):
print('工作票不存在，风机号：A' + str(wt_id) + '，停机时间' + str(stop_time))
res.append('风机号：A' + str(wt_id) + '，停机时间' + str(stop_time))
if re.findall(r'^(A)(\d+)(\s*)(\d{5})$', whtj.loc[x].values[8]):
stop_time = whtj.loc[x].values[11]
zero_time = stop_time - datetime.timedelta(
hours=stop_time.hour,
minutes=stop_time.minute,
seconds=stop_time.second)
wt_id = int(
re.match(r'^(A)(\d+)(\s*)(\d{5})$',
whtj.loc[x].values[8]).group(2))
try:
gzp = Gzp.query.filter(
and_(Gzp.pstart_time < stop_time,
Gzp.pstart_time > zero_time,
Gzp.wts.any(id=wt_id))).first() # 这里可能会生成bug
wtm = WTMaintain()
is_in = False
for item in gzp.wtms:
if item == WTMaintain.query.filter(
WTMaintain.stop_time == whtj.loc[x].values[11],
WTMaintain.start_time ==
whtj.loc[x].values[12]).first():
wtm = item
is_in = True # 标定数据库中已存在
wtm.wt_id = wt_id
wtm.type = whtj.loc[x].values[9]
wtm.task = whtj.loc[x].values[10]
wtm.stop_time = whtj.loc[x].values[11]
wtm.start_time = whtj.loc[x].values[12]
wtm.time = Utils.realRound(
(wtm.start_time - wtm.stop_time).seconds / 3600, 2)
wtm.lost_power = Utils.realRound(float(whtj.loc[x].values[13]),
4)
if not is_in:
gzp.wtms.append(wtm)
if len(gzp.wtms.all()) == len(gzp.wts.all()):
gzp.is_end = 1
Gzp().add(gzp)
except (AttributeError):
print('工作票不存在，风机号：A' + str(wt_id) + '，停机时间' + str(stop_time))
res.append('风机号：A' + str(wt_id) + '，停机时间' + str(stop_time))
# 故障
for x in range(len(gztj)):
if re.findall(r'^(A)(\d+)(\s*)(\d{5})$', gztj.loc[x].values[0]):
stop_time = gztj.loc[x].values[4]
start_time = gztj.loc[x].values[5]
if stop_time.hour < 18:
zero_time = stop_time - datetime.timedelta(
hours=stop_time.hour,
minutes=stop_time.minute,
seconds=stop_time.second) + datetime.timedelta(days=1)
else:
zero_time = stop_time - datetime.timedelta(
hours=stop_time.hour,
minutes=stop_time.minute,
seconds=stop_time.second) + datetime.timedelta(days=2)
wt_id = int(
re.match(r'^(A)(\d+)(\s*)(\d{5})$',
gztj.loc[x].values[0]).group(2))
try:
gzp = Gzp.query.filter(
and_(Gzp.pstart_time > stop_time,
Gzp.pstart_time < zero_time,
Gzp.wts.any(id=wt_id))).first() # 这里可能会生成bug
wtm = WTMaintain()
is_in = False
for item in gzp.wtms:
if item == WTMaintain.query.filter(
WTMaintain.stop_time == stop_time,
WTMaintain.start_time == start_time).first():
wtm = item
is_in = True # 标定数据库中已存在
wtm.wt_id = wt_id
wtm.error_code = gztj.loc[x].values[1]
wtm.error_content = gztj.loc[x].values[2]
wtm.type = gztj.loc[x].values[3]
wtm.stop_time = gztj.loc[x].values[4]
wtm.start_time = gztj.loc[x].values[5]
wtm.time = Utils.realRound(
(wtm.start_time - wtm.stop_time).seconds / 3600, 2)
wtm.lost_power = Utils.realRound(float(gztj.loc[x].values[7]),
4)
wtm.error_approach = gztj.loc[x].values[8]
wtm.task = gzp.task
if not is_in:
gzp.wtms.append(wtm)
if len(gzp.wtms.all()) == len(gzp.wts.all()):
gzp.is_end = 1
Gzp().add(gzp)
except (AttributeError):
print('工作票不存在，风机号：A' + str(wt_id) + '，停机时间' + str(stop_time))
res.append('风机号：A' + str(wt_id) + '，停机时间' + str(stop_time))
if re.findall(r'^(A)(\d+)(\s*)(\d{5})$', gztj.loc[x].values[10]):
stop_time = gztj.loc[x].values[14]
start_time = gztj.loc[x].values[15]
if stop_time.hour < 18:
zero_time = stop_time - datetime.timedelta(
hours=stop_time.hour,
minutes=stop_time.minute,
seconds=stop_time.second) + datetime.timedelta(days=1)
else:
zero_time = stop_time - datetime.timedelta(
hours=stop_time.hour,
minutes=stop_time.minute,
seconds=stop_time.second) + datetime.timedelta(days=2)
wt_id = int(
re.match(r'^(A)(\d+)(\s*)(\d{5})$',
gztj.loc[x].values[10]).group(2))
try:
gzp = Gzp.query.filter(
and_(Gzp.pstart_time > stop_time,
Gzp.pstart_time < zero_time,
Gzp.wts.any(id=wt_id))).first() # 这里可能会生成bug
wtm = WTMaintain()
is_in = False
for item in gzp.wtms:
if item == WTMaintain.query.filter(
WTMaintain.stop_time == stop_time,
WTMaintain.start_time == start_time).first():
wtm = item
is_in = True # 标定数据库中已存在
wtm.wt_id = wt_id
wtm.error_code = gztj.loc[x].values[11]
wtm.error_content = gztj.loc[x].values[12]
wtm.type = gztj.loc[x].values[13]
wtm.stop_time = gztj.loc[x].values[14]
wtm.start_time = gztj.loc[x].values[15]
wtm.time = Utils.realRound(
(wtm.start_time - wtm.stop_time).seconds / 3600, 2)
wtm.lost_power = Utils.realRound(float(gztj.loc[x].values[17]),
4)
wtm.error_approach = gztj.loc[x].values[18]
wtm.task = gzp.task
if not is_in:
gzp.wtms.append(wtm)
if len(gzp.wtms.all()) == len(gzp.wts.all()):
gzp.is_end = 1
Gzp().add(gzp)
except (AttributeError):
print('工作票不存在，风机号：A' + str(wt_id) + '，停机时间' + str(stop_time))
res.append('风机号：A' + str(wt_id) + '，停机时间' + str(stop_time))
return BaseController().successData(msg='res')
def trans_rbb_file(file, file_date, exp_path, temp_path):
"""
转换日报表文件
:param file:
:param file_date:
:param exp_path:
:param temp_path:
:return:
"""
# 写入上网电量
cdf_dl = pd.read_excel(file,
sheet_name='日报计算表',
usecols=range(76),
skiprows=range(4),
header=None)
temp_dl_file = os.path.join(temp_path, '石桥子风电场_上网电量&功率&测风塔数据.xls')
exp_dl_path = os.path.join(exp_path, '上网电量&功率&测风塔数据')
exp_dl_file = os.path.join(exp_dl_path, '石桥子风电场_上网电量&功率&测风塔数据.xls')
if os.path.exists(exp_dl_file):
wb = xlrd.open_workbook(exp_dl_file, formatting_info=True)
else:
if not os.path.exists(exp_dl_path):
os.mkdir(exp_dl_path)
wb = xlrd.open_workbook(temp_dl_file, formatting_info=True)
wb_copy_sw = copy(wb)
ws_copy = wb_copy_sw.get_sheet(0)
row_num = 1
for row in cdf_dl.values:
if file_date <= row[0] < file_date + relativedelta(months=+1):
ws_copy.write(row_num, 0, row[0].strftime('%Y/%m/%d'))
ws_copy.write(row_num, 1, Utils.realRound(row[32] / 1000, 3))
row_num = row_num + 1
elif row[0] >= file_date + relativedelta(months=+1):
break
wb_copy_sw.save(exp_dl_file)
# 停机数据
temp_tj_file = os.path.join(temp_path, '石桥子风电场_停机数据.xls')
exp_tj_path = os.path.join(exp_path, '停机数据')
exp_tj_file = os.path.join(exp_tj_path, '石桥子风电场_停机数据.xls')
if os.path.exists(exp_tj_file):
wb = xlrd.open_workbook(exp_tj_file, formatting_info=True)
else:
if not os.path.exists(exp_tj_path):
os.mkdir(exp_tj_path)
wb = xlrd.open_workbook(temp_tj_file, formatting_info=True)
wb_copy_wh = copy(wb)
ws_copy_wh = wb_copy_wh.get_sheet(2)
row_num = 2
# 写入维护数据
cdf_wh = pd.read_excel(file, sheet_name='风机维护统计', header=None)
for row in cdf_wh.values:
if str(row[0]).strip().startswith('A'):
try:
# row_date = datetime.datetime.strptime(row[3], '%Y/%m/%d %H:%M')
if file_date <= row[3] < file_date + relativedelta(months=+1):
ws_copy_wh.write(row_num, 0,
trans_double_id(row[0].strip()[0:3]))
ws_copy_wh.write(row_num, 1,
row[3].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 2,
row[4].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 3, row[2])
row_num = row_num + 1
elif row[3] >= file_date + relativedelta(months=+1):
break
except Exception as e:
print(e)
print(row[0])
print(row[3])
print(row[8])
print(row[11])
continue
elif str(row[0]).strip().startswith('3'):
try:
# row_date = datetime.datetime.strptime(row[3], '%Y/%m/%d %H:%M')
if file_date <= row[3] < file_date + relativedelta(months=+1):
ws_copy_wh.write(row_num, 0,
trans_double_id(row[0].strip()[6:-1]))
ws_copy_wh.write(row_num, 1,
row[3].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 2,
row[4].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 3, row[2])
row_num = row_num + 1
elif row[3] >= file_date + relativedelta(months=+1):
break
except Exception as e:
print(e)
print(row[0])
print(row[3])
print(row[8])
print(row[11])
continue
if str(row[8]).strip().startswith('A'):
try:
# row_date = datetime.datetime.strptime(row[11], '%Y/%m/%d %H:%M')
if file_date <= row[11] < file_date + relativedelta(months=+1):
ws_copy_wh.write(row_num, 0,
trans_double_id(row[8].strip()[0:3]))
ws_copy_wh.write(row_num, 1,
row[11].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 2,
row[12].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 3, row[10])
row_num = row_num + 1
elif row[11] >= file_date + relativedelta(months=+1):
break
except Exception as e:
print(e)
print(row[0])
print(row[3])
print(row[8])
print(row[11])
continue
elif str(row[8]).strip().startswith('3'):
try:
# row_date = datetime.datetime.strptime(row[11], '%Y/%m/%d %H:%M')
if file_date <= row[11] < file_date + relativedelta(months=+1):
ws_copy_wh.write(row_num, 0,
trans_double_id(row[8].strip()[6:-1]))
ws_copy_wh.write(row_num, 1,
row[11].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 2,
row[12].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 3, row[10])
row_num = row_num + 1
elif row[11] >= file_date + relativedelta(months=+1):
break
except Exception as e:
print(e)
print(row[0])
print(row[3])
print(row[8])
print(row[11])
continue
# 写入输变电维护记录
cdf_sbd_wh = pd.read_excel(file, sheet_name='输变电故障、维护统计', header=None)
for row in cdf_sbd_wh.values:
# 先统计一期
if str(row[0]).strip() in [
'全场停电', '全厂停电', '#1集电线路、#2集电线路', '#1集电线路', '#2集电线路'
]:
try:
# 全场停电情况
if str(row[0]).strip() in ['全场停电', '全厂停电', '#1集电线路、#2集电线路']:
wt_range = range(1, 21)
# #1线
elif str(row[0]).strip() == '#1集电线路':
wt_range = range(1, 11)
# #2线
elif str(row[0]).strip() == '#2集电线路':
wt_range = range(11, 21)
if file_date <= row[3] < file_date + relativedelta(months=+1):
for wid in wt_range:
ws_copy_wh.write(row_num, 0,
trans_double_id('A' + str(wid)))
ws_copy_wh.write(row_num, 1,
row[3].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 2,
row[4].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 3, row[0] + row[1])
row_num = row_num + 1
elif row[3] >= file_date + relativedelta(months=+1):
break
except Exception as e:
print(e)
print(row[0])
print(row[3])
print(row[4])
continue
# 统计二期
if str(row[8]).strip() in [
'全场停电', '全厂停电', '#3集电线路、#4集电线路', '#3集电线路', '#4集电线路'
]:
# 全场停电情况
try:
# 全场停电情况
if str(row[8]).strip() in ['全场停电', '全厂停电', '#3集电线路、#4集电线路']:
wt_range = range(21, 41)
# #1线
elif str(row[8]).strip() == '#3集电线路':
wt_range = range(21, 31)
# #2线
elif str(row[8]).strip() == '#4集电线路':
wt_range = range(31, 41)
if file_date <= row[11] < file_date + relativedelta(months=+1):
for wid in wt_range:
ws_copy_wh.write(row_num, 0,
trans_double_id('A' + str(wid)))
ws_copy_wh.write(row_num, 1,
row[11].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 2,
row[12].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_wh.write(row_num, 3, row[8] + row[9])
row_num = row_num + 1
elif row[11] >= file_date + relativedelta(months=+1):
break
except Exception as e:
print(e)
print(row[8])
print(row[11])
print(row[12])
continue
wb_copy_wh.save(exp_tj_file)
# 写入限电数据
wb = xlrd.open_workbook(exp_tj_file, formatting_info=True)
wb_copy_xd = copy(wb)
ws_copy_xd = wb_copy_xd.get_sheet(3)
row_num = 2
cdf_xd = pd.read_excel(file, sheet_name='电网故障、检修、限电统计', header=None)
for row in cdf_xd.values:
if str(row[2]).startswith('A'):
try:
# row_date = datetime.datetime.strptime(row[4], '%Y/%m/%d %H:%M')
if file_date <= row[4] < file_date + relativedelta(months=+1):
for id in range(40):
if id == 6:
continue
elif id == 11:
continue
elif id == 28:
continue
elif id == 38:
continue
wt_id = 'A' + str(id + 1).zfill(2)
ws_copy_xd.write(row_num, 0, trans_double_id(wt_id))
ws_copy_xd.write(row_num, 1,
row[4].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_xd.write(row_num, 2,
row[5].strftime('%Y-%m-%d %H:%M:%S'))
ws_copy_xd.write(row_num, 3, '电网限电')
row_num = row_num + 1
elif row[4] >= file_date + relativedelta(months=+1):
break
except Exception as e:
print("异常对象的类型是:%s" % type(e))
print("异常对象的内容是:%s" % e)
continue
wb_copy_xd.save(exp_tj_file)