def select_mail_addrs(host,user,pw,car,part):
if 'PAD'in part:
part=part[:3]
print(part)
table = 'mail_address'
canshu = (car, table, part)
sql = "select Address from %s.%s where Part='%s' " % canshu
print(sql)
mail_addrs=select_data(host, user, pw, sql)
mail_addrs=list(list(zip(*mail_addrs))[0])
print(mail_addrs)
str_mail_addrs = ''
for i in mail_addrs:
str_mail_addrs = str_mail_addrs + i + ';'
# print(str_mail_addrs)
return str_mail_addrs
dmo_point_names = []
for point_name in point_value_information.keys():
dmo_point_names.append(point_name)
# #预警部分++++++++++++++++++++++++++++++++++++++++++++++++
#提取基础数据库的点的名
database = car #数据库名称
base_table = 'inline_base_' + part #数据库的列表名称
base_point_name_canshu = (database, base_table)
basa_db_point_name_sql = "select PointName from %s.%s " % base_point_name_canshu
#使用已编的函数读取，同时把点名转换为列表
basa_db_point_names = list(
list(zip(*select_data(host, user, pw, basa_db_point_name_sql)))[0])
Intersection_point_name_list = list(
(set(dmo_point_names).union(set(basa_db_point_names)))
^ (set(dmo_point_names) ^ set(basa_db_point_names)))
print(Intersection_point_name_list)
print(basic_information)
Identnummer = basic_information[1]
EndDateTime = basic_information[5].replace(
"/", "-") + ' ' + basic_information[6]
cal_n = 10
WarningType = '未标记'
inline_warining_condition(host, user, pw, car, part,
Intersection_point_name_list, Identnummer,
cal_n, WarningType, gentxtfolder_addr,
EndDateTime)
def inline_warining_condition(host, user, pw,car,part,point_name_list,Identnummer,cal_n,WarningType,gentxtfolder_addr,EndDateTime):
print('开始进入预警判断')
database=car
table='inline_dev_'+part
canshu=(database,table)
sql = "select PointName,Messwert from %s.%s order by EndDateTime desc limit 0,40000" %canshu
mess_vals = list(select_data(host, user, pw, sql))
mess_vals_df = pd.DataFrame(mess_vals).set_index(0)
for point_name in point_name_list:
# print(point_name)
gentxt_addr = gentxtfolder_addr + '\\' + car + '-' + part + '-' + Identnummer + '-' + point_name + '.txt'
mess_vals = mess_vals_df.ix[[point_name], [1]].head(50)
mess_vals_len = len(mess_vals)
# print(mess_vals_len)
base_table = 'inline_base_' + part
base_canshu = (database, base_table,point_name)
base_info_sql = "select UntererTol,ObererTol from %s.%s where PointName = '%s'" % base_canshu
# print(base_info_sql)
base_info = list(select_data(host, user, pw, base_info_sql))
# print(base_info)
if base_info == []: continue
uptol = float(base_info[0][1])
downtol = float(base_info[0][0])
max_warning_value = 50
min_warning_value=abs(uptol-downtol)*0.75
# print('min_warning_value', min_warning_value)
if min_warning_value < 1 :
min_warning_value=1
elif min_warning_value > 2.5:
min_warning_value = 2.5
if mess_vals_len > 25:
point_std = mess_vals[1].std()
# print('1212121',point_std)
if point_std != 0:
point_cp = (uptol - downtol) / (6 * point_std) # 计算出这个点的cp值
# print(point_cp)
# if point_cp < 0.1:
# print(point_name+'该点CP值小于0.1'+str(point_val))
# with open(gentxt_addr,'w') as f:
# f.write(nyr+'\n')
# f.write('该点CP值小于0.1')
else:
pass
# print(point_name+'计算出的西格玛为0')
else:
pass
# print('测量次数少于3次，不计算cp值')
if mess_vals_len >= 50:
point_mean = mess_vals[1].mean()
alarm_description = '该点出现断崖式变化'
last20_vals = mess_vals[:cal_n].reset_index(drop=True)
# print(last20_vals)
last20_vals_std = last20_vals[1].std()#计算标准差
if last20_vals_std == 0: last20_vals_std = 0.001
last20_vals_mean = last20_vals[1].mean()
last20_index = last20_vals.index.tolist()
# print(last20_index)
index_list = []
for i in last20_index:
dui_bi_num = abs(float((last20_vals[1][i]) - last20_vals_mean) / last20_vals_std)
# print(dui_bi_num)
if dui_bi_num > 2.5:
index_list.append(i)
new_last20_vals = last20_vals[1].drop(index_list)
print(new_last20_vals)
qian_mean = new_last20_vals[:5].mean()
hou_mean = new_last20_vals[5:].mean()
if abs(qian_mean) > 100 and abs(hou_mean) > 100:
continue
cha_zhi = abs(qian_mean - hou_mean)
print('cha_zhi',cha_zhi)
# if 0.12 > cha_zhi and cha_zhi > 0.1:
if max_warning_value > cha_zhi and cha_zhi > min_warning_value:
# print(car,part,point_name)
# print(last20_vals)
# print(last20_index)
# print(index_list)
# print(qian_mean, hou_mean, cha_zhi)
with open(gentxt_addr, 'w') as f:
f.write(EndDateTime + '\n')
f.write(alarm_description)
WarningID=car+'_'+part+'_'+point_name.replace('.','')+'_'+EndDateTime.replace('-','').replace(' ','').replace(':','')
# print(WarningID)
warning_table='inline_warning_'+part
warning_record_table_byte = '(WarningID CHAR(100) NOT NULL,' \
'PointName CHAR(100) NOT NULL,' \
'DifferenceValue FLOAT, ' \
'EndDateTime DATETIME, ' \
'Identnummer CHAR(50),' \
'WarningDescribe CHAR(50), ' \
'Amount INT ,' \
'WarningType CHAR(200), ' \
'WarningReason CHAR(250), ' \
'AdjustReason CHAR(250), ' \
'Principal CHAR(50), ' \
'Departments CHAR(80), ' \
'Solution CHAR(250), ' \
'TimeNode CHAR(250) ' \
')'
cre_db_table(host, user, pw, database, warning_table, warning_record_table_byte)
# print()
canshu = (database,warning_table,WarningID, point_name,cha_zhi, EndDateTime, Identnummer, alarm_description, cal_n,WarningType)
record_sql = "insert into %s.%s(WarningID,PointName,DifferenceValue,EndDateTime,Identnummer,WarningDescribe,Amount,WarningType) " \
"values('%s','%s','%s','%s','%s','%s','%s','%s')" % canshu
print(record_sql)
into_data(host, user, pw, record_sql)
print('预警数据写入数据库')
continue
# ###测试
# host = 'localhost'#mysql的ip或者本地的地址
# user = '******'#mydql的用户
# pw = 'LISON23354!'#mysql的密码
# car='TROC'
# part='RO1PAD'
# point_name_list=['MLFAV1201_ODCA.X','MLFAV1201_ODCA.Y','MLFAV1201_ODCA.Z','MRFAV1201_ODCA.X','MRFAV1201_ODCA.Y']
# Identnummer= '58745545'
# cal_n=20
# WarningType='WEIBIAOZHI'
# #有预警是这个文件夹会生成一个txt文件
# gentxtfolder_addr=r'D:\Python\Python_Project\cp4_2_inline\03_Gen_txt'#出现
# EndDateTime='2018-10-20 10:12:22'
# inline_warining_condition(host, user, pw,car,part,point_name_list,Identnummer,cal_n,WarningType,gentxtfolder_addr,EndDateTime)
def inline_warining_condition(host, user, pw,car,part,point_name_list,Identnummer,cal_n,WarningType,gentxtfolder_addr,EndDateTime):
print('开始进入预警判断')
database=car
table='inline_dev_'+part
canshu=(database,table)
sql = "select PointName,Messwert from %s.%s order by EndDateTime desc limit 0,40000" %canshu
mess_vals = list(select_data(host, user, pw, sql))
mess_vals_df = pd.DataFrame(mess_vals).set_index(0)
print(mess_vals_df)
for point_name in point_name_list:
# print(point_name)
gentxt_addr = gentxtfolder_addr + '\\' + car + '-' + part + '-' + Identnummer + '-' + point_name + '.txt'
mess_vals = mess_vals_df.ix[[point_name], [1]].head(50)
mess_vals_len = len(mess_vals)
print(mess_vals)
base_table = 'inline_base_' + part
base_canshu = (database, base_table,point_name)
base_info_sql = "select UntererTol,ObererTol from %s.%s where PointName = '%s'" % base_canshu
print(base_info_sql)
base_info = list(select_data(host, user, pw, base_info_sql))
print(base_info)
if base_info == []: continue
uptol = float(base_info[0][1])
downtol = float(base_info[0][0])
max_warning_value = 50
min_warning_value=abs(uptol-downtol)*0.75
# print('min_warning_value', min_warning_value)
if min_warning_value < 1 :
min_warning_value=1
elif min_warning_value > 2.5:
min_warning_value = 2.5
if mess_vals_len > 25:
point_std = mess_vals[1].std()
# print('1212121',point_std)
if point_std != 0:
point_cp = (uptol - downtol) / (6 * point_std) # 计算出这个点的cp值
# print(point_cp)
# if point_cp < 0.1:
# print(point_name+'该点CP值小于0.1'+str(point_val))
# with open(gentxt_addr,'w') as f:
# f.write(nyr+'\n')
# f.write('该点CP值小于0.1')
else:
pass
# print(point_name+'计算出的西格玛为0')
else:
pass
# print('测量次数少于3次，不计算cp值')
if mess_vals_len >= 50:
point_mean = mess_vals[1].mean()
alarm_description = '该点出现断崖式变化'
last10_vals = mess_vals[:cal_n].reset_index(drop=True)
print(last10_vals)
last10_vals_std = last10_vals[1].std()#计算标准差
if last10_vals_std == 0: last10_vals_std = 0.001
last10_vals_mean = last10_vals[1].mean()
last10_index = last10_vals.index.tolist()
print(last10_index)
index_list = []
for i in last10_index:
dui_bi_num = abs(float((last10_vals[1][i]) - last10_vals_mean) / last10_vals_std)
print(dui_bi_num)
if dui_bi_num > 2.5:
index_list.append(i)
new_last10_vals = last10_vals[1].drop(index_list)
qian_mean = new_last10_vals[:5].mean()
hou_mean = new_last10_vals[5:].mean()
if abs(qian_mean) > 100 and abs(hou_mean) > 100:
continue
cha_zhi = abs(qian_mean - hou_mean)
print('cha_zhi',cha_zhi)
if max_warning_value > cha_zhi and cha_zhi > min_warning_value:
print(car,part,point_name)
print(last10_vals)
print(last10_index)
print(index_list)
print(qian_mean, hou_mean, cha_zhi)
with open(gentxt_addr, 'w') as f:
f.write(EndDateTime + '\n')
f.write(alarm_description)
warning_table = 'inline_warning_' + part
warning_record_table_byte = '(ID INT NOT NULL auto_increment primary key,' \
'PointName CHAR(30) NOT NULL,' \
'DifferenceValue FLOAT, ' \
'EndDateTime DATETIME, ' \
'Identnummer CHAR(50),' \
'WarningDescribe CHAR(200), ' \
'Amount INT ,' \
'WarningType CHAR(100) ' \
')'
cre_db_table(host, user, pw, database, warning_table,
warning_record_table_byte)
# print()
canshu = (database, warning_table, point_name, cha_zhi, EndDateTime,
Identnummer, alarm_description, cal_n, WarningType)
record_sql = "insert into %s.%s(PointName,DifferenceValue,EndDateTime,Identnummer,WarningDescribe,Amount,WarningType) values('%s','%s','%s','%s','%s',%d,'%s')" % canshu
print(record_sql)
into_data(host, user, pw, record_sql)
print('预警数据写入数据库')
continue
def gen_question_sheet(warning_point_name, bao_jing_datetime, car, part):
"""
:param warning_point_name: 预警的点名
:param bao_jing_datetime: 预警的时间
:param car: 对应车型
:param part: 零件
:return: 问题单的存放和问题单，预警的点的名称
"""
# 把时间转化为字符串格式
occur_datatime = datetime.datetime.strptime(bao_jing_datetime,
"%Y-%m-%d %H:%M:%S")
print('gggggggggggggggg', occur_datatime)
# 建时间转化为周表示
nzx_tuple = occur_datatime.isocalendar()
# 读取周的格式
zhou_ci = str(nzx_tuple[0])[-2:] + 'KW' + (
'0' + str(nzx_tuple[1]))[-2:] + '-' + str(nzx_tuple[2])
basic_table = 'inline_base_' + part # mysqll对应的基础数据库的列表名称
canshu = (car, basic_table, warning_point_name)
sql = "select * from %s.%s where PointName = '%s' " % canshu
# 读取预警的点的基础信息
base_info_df = list(zip(*select_data(host, user, pw, sql)))
print(base_info_df)
car_type = car_map_dict[car]
# 将点的各个信息赋给各个变量
point_description = base_info_df[17][0] # 点的描述
point_feature = base_info_df[16][0] # 点的特征
point_grade = base_info_df[18][0] # 点的等级程度
point_standard_up_tol = base_info_df[9][0] # 标准上公差
point_standard_down_tol = base_info_df[8][0] # 标准下公差
point_3grade_up_tol = base_info_df[15][0] # 3级上公差
point_3grade_down_tol = base_info_df[14][0] # 3级下公差
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
excel.DisplayAlerts = False
con = win32.constants
wb = excel.Workbooks.Open(excel_question_addr)
wb.Worksheets('点名').Name = warning_point_name
sheet = wb.Worksheets(warning_point_name)
# 将点的各个信息写入对应excel的表格
sheet.Cells(4, 3).Value = car_type
sheet.Cells(5, 3).Value = part
sheet.Cells(6, 3).Value = warning_point_name
sheet.Cells(7, 2).Value = point_description
sheet.Cells(10, 2).Value = point_feature
sheet.Cells(10, 4).Value = str(point_grade)
sheet.Cells(12, 2).Value = point_standard_down_tol
sheet.Cells(12, 4).Value = point_standard_up_tol
sheet.Cells(14, 2).Value = point_3grade_down_tol
sheet.Cells(14, 4).Value = point_3grade_up_tol
print('ffffffffffffff', occur_datatime, type(occur_datatime))
sheet.Cells(2, 7).Value = str(occur_datatime)
sheet.Cells(2, 'M').Value = zhou_ci
database = car
dve_table = 'inline_dev_' + part
# 读取之前的80个测量值
canshu = (database, dve_table, warning_point_name, occur_datatime)
sql = "select Messwert from %s.%s where PointName = '%s' and EndDateTime <= '%s' order by EndDateTime desc limit 0,80" % canshu
p_vals_1 = pd.DataFrame(list(select_data(host, user, pw, sql))[::-1])
# print(p_vals_1)
# 读取之后的20个测量值
canshu = (database, dve_table, warning_point_name, occur_datatime)
sql = "select Messwert from %s.%s where PointName = '%s' and EndDateTime > '%s' order by EndDateTime limit 0,20" % canshu
p_vals_2 = pd.DataFrame(list(select_data(host, user, pw, sql)))
# print(p_vals_2)
# 把两个读出来的测量值合并在一起
p_vals_hebing = pd.concat([p_vals_1, p_vals_2], ignore_index=True)
p_vals = p_vals_hebing
print(p_vals)
# 读取之前的80个测量结束时间
paramt_x = (database, dve_table, warning_point_name, occur_datatime)
sql_x = "select EndDateTime from %s.%s where PointName = '%s' and EndDateTime <= '%s' order by EndDateTime desc limit 0,80" % paramt_x
temp = list(select_data(host, user, pw, sql_x))[::-1]
print(temp)
# 读取之后的20个测量结束时间
sql_x1 = "select EndDateTime from %s.%s where PointName = '%s' and EndDateTime > '%s' order by EndDateTime limit 0,20" % paramt_x
temp1 = list(select_data(host, user, pw, sql_x1))
print(temp1)
# 把开始和结束时间赋给变量
start_time = temp[0][0]
end_time = temp[-1][0]
start_time = datetime.datetime.strftime(start_time, "%Y-%m-%d %H:%M:%S")
end_time = datetime.datetime.strftime(end_time, "%Y-%m-%d %H:%M:%S")
sheet.Cells(3, 'J').Value = str(start_time)
sheet.Cells(3, 'N').Value = str(end_time)
p_vals_list = p_vals[0].values.tolist()
p_vals_mean = p_vals[0].mean() # 计算平均值
p_vals_std = p_vals[0].std() # 计算标准差
up_control = 3 * p_vals_std + p_vals_mean # 上控制线
down_control = -3 * p_vals_std + p_vals_mean # 下控制线
list_len = len(temp)
for k in range(0, list_len, 10):
list_10 = p_vals_list[k:k + 10]
max_val = np.max(list_10)
min_val = np.min(list_10)
max_index = list_10.index(max_val)
min_index = list_10.index(min_val)
max_excel_column = max_index + k + 2
min_excel_column = min_index + k + 2
sheet.Cells(71, max_excel_column).Value = max_val
sheet.Cells(71, min_excel_column).Value = min_val
for i in range(list_len):
val = p_vals.at[i, 0]
mess_time = temp[i]
sheet.Cells(64, i + 2).Value = mess_time
sheet.Cells(65, i + 2).Value = val
sheet.Cells(66, i + 2).Value = p_vals_mean
sheet.Cells(67, i + 2).Value = up_control
sheet.Cells(68, i + 2).Value = down_control
sheet.Cells(69, i + 2).Value = point_standard_up_tol
sheet.Cells(70, i + 2).Value = point_standard_down_tol
max_column = i + 2
# 插入图片
picture_addr = os.path.split(
pro_addr
)[0] + '\\10_Inline_photo\\' + car + '\\' + part + '\\' + warning_point_name + '.JPG'
print(picture_addr)
picture_left = sheet.Cells(17, 'A').Left
picture_top = sheet.Cells(17, 'A').Top
picture_width = sheet.Cells(17, 'A').Width * 4
picture_height = sheet.Cells(17, 'A').Height * 11
try:
sheet.Shapes.AddPicture(picture_addr, 1, 1, picture_left, picture_top,
picture_width, picture_height)
except:
print('图片库里没有' + car + ' ' + part + ' ' + warning_point_name + '图片')
# 插入图表
chartObjectLeft = sheet.Cells(5, 'E').Left
chartObjectTop = sheet.Cells(5, 'E').Top
chartObjectWidth = sheet.Cells(5, 'E').Width * 12
chartObjectHeight = sheet.Cells(5, 'E').Height * 11
chartObject = sheet.ChartObjects().Add(chartObjectLeft, chartObjectTop,
chartObjectWidth, chartObjectHeight)
chart = chartObject.Chart
chartObject.Name = '时间序列'
chart.ChartType = con.xlLine
chart.ApplyLayout(1)
chart.ChartTitle.Delete()
chart.Axes(con.xlCategory).Delete()
chart.Legend.Delete()
chart.Axes(con.xlValue, con.xlPrimary).AxisTitle.Text = "mm"
chart.Axes(con.xlValue).MajorGridlines.Format.Line.Visible = 0
series1 = chart.SeriesCollection().NewSeries()
chart.SeriesCollection(1).MarkerStyle = con.xlMarkerStyleCircle
series1.Values = sheet.Range(sheet.Cells(65, 'B'),
sheet.Cells(65, max_column))
series2 = chart.SeriesCollection().NewSeries()
chart.SeriesCollection(2).MarkerStyle = con.xlMarkerStyleNone
# chart.SeriesCollection(2).Format.Line.ForeColor.ObjectThemeColor = 6
chart.SeriesCollection(2).Format.Line.ForeColor.RGB = 50
chart.SeriesCollection(2).Format.Line.Weight = 0.25
series2.Values = sheet.Range(sheet.Cells(66, 'B'),
sheet.Cells(66, max_column))
series3 = chart.SeriesCollection().NewSeries()
chart.SeriesCollection(3).MarkerStyle = con.xlMarkerStyleNone
# chart.SeriesCollection(2).Format.Line.ForeColor.ObjectThemeColor = 6
chart.SeriesCollection(3).Format.Line.ForeColor.RGB = 255
chart.SeriesCollection(3).Format.Line.Weight = 0.25
series3.Values = sheet.Range(sheet.Cells(67, 'B'),
sheet.Cells(67, max_column))
series4 = chart.SeriesCollection().NewSeries()
chart.SeriesCollection(4).MarkerStyle = con.xlMarkerStyleNone
# chart.SeriesCollection(2).Format.Line.ForeColor.ObjectThemeColor = 6
chart.SeriesCollection(4).Format.Line.ForeColor.RGB = 255
chart.SeriesCollection(4).Format.Line.Weight = 0.25
series4.Values = sheet.Range(sheet.Cells(68, 'B'),
sheet.Cells(68, max_column))
series5 = chart.SeriesCollection().NewSeries()
chart.SeriesCollection(5).MarkerStyle = con.xlMarkerStyleNone
# chart.SeriesCollection(2).Format.Line.ForeColor.ObjectThemeColor = 6
chart.SeriesCollection(5).Format.Line.ForeColor.RGB = 180
chart.SeriesCollection(5).Format.Line.Weight = 0.25
chart.SeriesCollection(5).Format.Line.DashStyle = 2
series5.Values = sheet.Range(sheet.Cells(69, 'B'),
sheet.Cells(69, max_column))
series6 = chart.SeriesCollection().NewSeries()
chart.SeriesCollection(6).MarkerStyle = con.xlMarkerStyleNone
# chart.SeriesCollection(2).Format.Line.ForeColor.ObjectThemeColor = 6
chart.SeriesCollection(6).Format.Line.ForeColor.RGB = 180
chart.SeriesCollection(6).Format.Line.Weight = 0.25
chart.SeriesCollection(6).Format.Line.DashStyle = 2
series6.Values = sheet.Range(sheet.Cells(70, 'B'),
sheet.Cells(70, max_column))
series7 = chart.SeriesCollection().NewSeries()
chart.SeriesCollection(7).Format.Line.ForeColor.RGB = 230
chart.SeriesCollection(7).MarkerStyle = con.xlMarkerStyleNone
chart.SeriesCollection(7).ApplyDataLabels()
chart.SeriesCollection(7).DataLabels().Position = 0
chart.SeriesCollection(7).Format.Line.Visible = 0
# chart.SeriesCollection(2).Format.Line.ForeColor.ObjectThemeColor = 6
series7.Values = sheet.Range(sheet.Cells(71, 'B'),
sheet.Cells(71, max_column))
# n = 0
row = 82
sheet.Cells(16, 'N').Value = str(occur_datatime.date())
none_Messwert = 0 # 用于控制之前有很多天没有测测量值而导致不停循环关闭的条件之一
while True:
day_datetime_strat = str(occur_datatime.date()) + ' ' + '08:30:00'
night_datetime_strat = str(occur_datatime.date()) + ' ' + '19:30:00'
night_date_end = occur_datatime.date() + datetime.timedelta(days=1)
night_datetime_end = str(night_date_end) + ' ' + '08:30:00'
canshu = (database, dve_table, warning_point_name, day_datetime_strat,
night_datetime_strat)
day_sql = "select Messwert from %s.%s where PointName = '%s' and EndDateTime between '%s' and '%s'" % canshu
day_data = select_data(host, user, pw, day_sql)
print(day_data)
if day_data != ():
day_df = pd.DataFrame(list(day_data))
kai_pan = day_df[0].quantile(0.25)
pan_gao = day_df[0].quantile(1)
pan_di = day_df[0].quantile(0)
shou_pan = day_df[0].quantile(0.75)
sheet.Cells(row, 5).Value = str(occur_datatime.date()) + '白'
sheet.Cells(row, 6).Value = round(kai_pan, 2)
sheet.Cells(row, 7).Value = round(pan_gao, 2)
sheet.Cells(row, 8).Value = round(pan_di, 2)
sheet.Cells(row, 9).Value = round(shou_pan, 2)
row = row - 1
canshu = (database, dve_table, warning_point_name,
night_datetime_strat, night_datetime_end)
night_sql = "select Messwert from %s.%s where PointName = '%s' and EndDateTime between '%s' and '%s'" % canshu
night_data = select_data(host, user, pw, night_sql)
print(night_data)
if night_data != ():
night_df = pd.DataFrame(list(night_data))
kai_pan = night_df[0].quantile(0.25)
pan_gao = night_df[0].quantile(1)
pan_di = night_df[0].quantile(0)
shou_pan = night_df[0].quantile(0.75)
sheet.Cells(row, 5).Value = str(occur_datatime.date()) + '夜'
sheet.Cells(row, 6).Value = kai_pan
sheet.Cells(row, 7).Value = pan_gao
sheet.Cells(row, 8).Value = pan_di
sheet.Cells(row, 9).Value = shou_pan
row = row - 1
if day_data == () and night_data == ():
none_Messwert = none_Messwert + 1
if row < 73 or none_Messwert > 20:
break # 判断是否跳出循环：行数小于73（因为盒型图的数据在82~73行和连续20天没有测量值）
occur_datatime = occur_datatime - datetime.timedelta(days=1)
sheet.Cells(16, 'J').Value = str(occur_datatime.date())
excel_question_save_addr = os.path.split(
pro_addr
)[0] + r'\02_alert_sheet' + '\\' + car + '_' + part + '_' + warning_point_name + '_' + '在线测量预警问题单.xlsx'
wb.SaveAs(excel_question_save_addr)
excel.DisplayAlerts = True
# wb.Close()
excel.Quit()
return excel_question_save_addr, warning_point_name
print('54545454')
print(time)
reason = file.readline().replace('\n', '')
check = 0
print(rule)
print(reason)
if reason == rule:
check = 1
dve_table = 'inline_dev_' + part
sql1 = "select Messwert from %s.%s where PointName='%s' and EndDateTime<='%s' order by EndDateTime desc limit 0,20" % (
car, dve_table, point_name, time)
sql2 = "select EndDateTime from %s.%s where PointName='%s' and EndDateTime<='%s' order by EndDateTime desc limit 0,20" % (
car, dve_table, point_name, time)
val6_df = pd.DataFrame(list(select_data(host, user, pw,
sql1)))[::-1]
print(val6_df)
val6_series = val6_df[0].abs()
val6_series_smaller1_len = len(val6_series[val6_series < 1])
val6_series_max = val6_series.max()
if val6_series_smaller1_len >= 13 and val6_series_max > 3:
os.remove(fdir + '\\' + fname)
continue
val6_series_bigger3_len = len(val6_series[val6_series > 3])
if val6_series_bigger3_len >= 14:
os.remove(fdir + '\\' + fname)
continue
sub_df_1 = val6_df[[0]][:-1].reset_index(drop=True)
def inline_warining_condition(host, user, pw, car, part, point_name_list,
Identnummer, cal_n, WarningType,
gentxtfolder_addr, EndDateTime):
print('开始进入预警判断')
database = car
table = 'inline_dev_' + part
canshu = (database, table)
sql = "select PointName,Messwert from %s.%s order by EndDateTime desc limit 0,40000" % canshu
mess_vals = list(select_data(host, user, pw, sql))
mess_vals_df = pd.DataFrame(mess_vals).set_index(0)
for point_name in point_name_list:
gentxt_addr = gentxtfolder_addr + '\\' + car + '-' + part + '-' + Identnummer + '-' + point_name + '.txt'
mess_vals = mess_vals_df.ix[[point_name], [1]].head(50)
mess_vals_len = len(mess_vals)
base_table = 'inline_base_' + part
base_canshu = (database, base_table, point_name)
base_info_sql = "select UntererTol,ObererTol from %s.%s where PointName = '%s'" % base_canshu
# print(base_info_sql)
base_info = list(select_data(host, user, pw, base_info_sql))
if base_info == []: continue
uptol = float(base_info[0][1])
downtol = float(base_info[0][0])
print(mess_vals_len)
if mess_vals_len > 25:
# print(mess_vals[1])
point_std = mess_vals[1].std() #标准偏差
# print(point_std)
if point_std != 0:
point_cp = (uptol - downtol) / (6 * point_std) # 计算出这个点的cp值
# if point_cp < 0.1:
# print(point_name+'该点CP值小于0.1'+str(point_val))
# with open(gentxt_addr,'w') as f:
# f.write(nyr+'\n')
# f.write('该点CP值小于0.1')
else:
pass
# print(point_name+'计算出的西格玛为0')
else:
pass
# print('测量次数少于3次，不计算cp值')
if mess_vals_len >= 50:
point_mean = mess_vals[1].mean() #平均数
alarm_description = '该点出现断崖式变化'
# print(mess_vals[:cal_n])
last20_vals = mess_vals[:cal_n].reset_index(
drop=True) #取出前20件的values
# print(last20_vals)
last20_vals_std = last20_vals[1].std()
if last20_vals_std == 0: last20_vals_std = 0.001
last20_vals_mean = last20_vals[1].mean()
last20_index = last20_vals.index.tolist()
index_list = []
for i in last20_index:
# z-score 标准化(zero-mean normalization)也叫标准差标准化，经过处理的数据符合标准正态分布
# '''
# 这种方法基于原始数据的均值（mean）和标准差（standard deviation）进行数据的标准化。将A的原始值x使用z-score标准化到x’。
# z-score标准化方法适用于属性A的最大值和最小值未知的情况，或有超出取值范围的离群数据的情况。
# spss默认的标准化方法就是z-score标准化。
# 用Excel进行z-score标准化的方法：在Excel中没有现成的函数，需要自己分步计算，其实标准化的公式很简单。
# 步骤如下：
# 1.求出各变量（指标）的算术平均值（数学期望）xi和标准差si ；
# 2.进行标准化处理：
# zij=（xij－xi）/si
# 其中：zij为标准化后的变量值；xij为实际变量值。
# 3.将逆指标前的正负号对调。
# 标准化后的变量值围绕0上下波动，大于0说明高于平均水平，小于0说明低于平均水平。
# '''
dui_bi_num = abs(
float((last20_vals[1][i]) - last20_vals_mean) /
last20_vals_std)
# print(dui_bi_num)
if dui_bi_num > 3:
index_list.append(i)
new_last20_vals = last20_vals[1].drop(index_list)
# print(new_last20_vals)
# print(last20_vals[1])
qian_mean = new_last20_vals[:10].mean()
hou_mean = new_last20_vals[10:].mean()
if abs(qian_mean) > 100 and abs(hou_mean) > 100:
continue
cha_zhi = abs(qian_mean - hou_mean)
print(cha_zhi)
if cha_zhi > 1:
# print(car,part,point_name)
# print(last20_vals)
# print(last20_index)
# print(index_list)
# print(qian_mean, hou_mean, cha_zhi)
#抛出预警点文件
with open(gentxt_addr, 'w') as f:
f.write(EndDateTime + '\n')
f.write(alarm_description)
warning_table = 'inline_warning_' + part
warning_record_table_byte = '(ID INT NOT NULL auto_increment primary key,' \
'PointName CHAR(30) NOT NULL,' \
'DifferenceValue FLOAT, ' \
'EndDateTime DATETIME, ' \
'Identnummer CHAR(50),' \
'WarningDescribe CHAR(200), ' \
'Amount INT ,' \
'WarningType CHAR(100) ' \
')'
cre_db_table(host, user, pw, database, warning_table,
warning_record_table_byte)
# print()
canshu = (database, warning_table, point_name, cha_zhi,
EndDateTime, Identnummer, alarm_description, cal_n,
WarningType)
record_sql = "insert into %s.%s(PointName,DifferenceValue,EndDateTime,Identnummer,WarningDescribe,Amount,WarningType) values('%s','%s','%s','%s','%s',%d,'%s')" % canshu
print(record_sql)
into_data(host, user, pw, record_sql)
print('预警数据写入数据库')
continue
# #
# # ###测试
# host = 'localhost'#mysql的ip或者本地的地址
# user = '******'#mydql的用户
# pw = 'mysql-qd1'#mysql的密码
# car='Bora_MQB'
# part='RO1'
# point_name_list=['MLESB1201_HDLE.X','NLSHI0009_U_AA.Z','MRRWH0032_HEBA.X','NLRVO0005_R_DG.Y','NRRVO0001_U_CA.X']
# Identnummer= '58745545'
# cal_n=20
# WarningType='WEIBIAOZHI'
# #有预警是这个文件夹会生成一个txt文件
# gentxtfolder_addr=r'D:\01_MProject\cp5_inline\09_mail_point_txt'#出现
# EndDateTime='2018-10-31 10:12:22'
# while True:
# inline_warining_condition(host, user, pw,car,part,point_name_list,Identnummer,cal_n,WarningType,gentxtfolder_addr,EndDateTime)