for i in range(2, nrows, 1):
rows = sheet.row_values(i)
car = rows[1]
part = rows[2]
key_mail_addrs = car + '_' + part
excel_mail_addrs = rows[4:]
excel_mail_addrs = list(set(excel_mail_addrs))
excel_mail_addrs.remove('')
for mail_addr in excel_mail_addrs:
if '@' not in mail_addr:
new_mail_addr = mail_addr
mail_addr_name = new_mail_addr.split('.')[1]
mail_addr_familyname = new_mail_addr.split('.')[0]
mail_addr = mail_addr_name + '.' + mail_addr_familyname + '@faw-vw.com'
print(mail_addr)
print(standard_cars, standard_parts)
st_car = standard_cars[car]
st_part = standard_parts[part]
table = 'mail_address'
canshu = (st_car, table, st_part, mail_addr)
# SQL 插入语句
sql = """INSERT INTO %s.%s (Part, Address)
VALUES ('%s', '%s')""" % canshu
print(sql)
try:
into_data(host, user, pw, sql)
except:
print('可能是重复了')
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 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)
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)