def creat_basic_db(host, user, pw, database, map_database):
base_table_byte = '(Point_Group INT, ' \
'PointName CHAR(50) NOT NULL primary key,' \
'X_Position FLOAT, ' \
'Y_Position FLOAT, ' \
'Z_Position FLOAT, ' \
'i_Richtung FLOAT, ' \
'j_Richtung FLOAT, ' \
'k_Richtung FLOAT, ' \
'UntererTol FLOAT, ' \
'ObererTol FLOAT, ' \
'UntererTol_I FLOAT, ' \
'ObererTol_I FLOAT, ' \
'UntererTol_II FLOAT, ' \
'ObererTol_II FLOAT, ' \
'UntererTol_III FLOAT, ' \
'ObererTol_III FLOAT, ' \
'Characteristic CHAR(50),' \
'Description CHAR(50),' \
'Point_Grade CHAR(50),' \
'Consistency CHAR(50),' \
'FM_POINT CHAR(50),' \
'CS_Statistics CHAR(50)' \
')'
# 创建基础数据库
cre_db_table(host, user, pw, database, map_database, base_table_byte)
def creat_db(car):
database = car
table = 'mail_address'
# table_byte = '(ID INT NOT NULL auto_increment primary key,' \
# 'Part CHAR(50) NOT NULL ,' \
# 'Address CHAR(50) NOT NULL' \
# ')'
table_byte = '(Part CHAR(50) NOT NULL ,' \
'Address CHAR(50) NOT NULL,' \
'primary key(Part,Address)'\
')'
cre_db_table(host, user, pw, database, table, table_byte)
#以标准的车型缩写创建对应的数据路
cre_db_database(host, user, pw, car)
dve_table = 'inline' + '_' + 'dev' + '_' + part
print(dve_table)
dve_table_byte = '(ID INT NOT NULL auto_increment primary key,' \
'PointName CHAR(30) NOT NULL,' \
'Messwert FLOAT, ' \
'Identnummer CHAR(30),' \
'StartDateTime DATETIME ,' \
'EndDateTime DATETIME ' \
')'
# 以标准的零件缩写创建对应的零件数据列表
cre_db_table(host, user, pw, car, dve_table, dve_table_byte)
print('2121212121')
#将dmo的数据写入到数据库
point_value_to_db(host, user, pw, car, dve_table,
point_value_information, basic_information)
dmo_point_names = []
for point_name in point_value_information.keys():
dmo_point_names.append(point_name)
# #预警部分++++++++++++++++++++++++++++++++++++++++++++++++
#提取基础数据库的点的名
database = car #数据库名称
base_table = 'inline_base_' + part #数据库的列表名称
host = 'localhost' # mysql的ip或者本地的地址
user = '******' # mydql的用户
pw = 'mysql-qd1' # mysql的密码
map_database = 'MappingTable' #映射表数据据库名称
car_table = 'car'
part_table = 'part'
program_addr = os.path.split(os.path.realpath(__file__))[0]
mappingtable_excel_addr = os.path.split(
program_addr)[0] + '\\' + '11_映射表' + '\\' + '映射表.xlsx'
car_sheet = 'car'
part_sheet = 'part'
table_byte = '(standard CHAR(30) NOT NULL , notstandard CHAR(30) NOT NULL, primary key(standard,notstandard))'
cre_db_database(host, user, pw, map_database)
cre_db_table(host, user, pw, map_database, car_table, table_byte)
cre_db_table(host, user, pw, map_database, part_table, table_byte)
print(mappingtable_excel_addr)
workbook = xlrd.open_workbook(mappingtable_excel_addr)
car_sheet = workbook.sheet_by_name(car_sheet)
car_nrows = car_sheet.nrows
print(car_nrows)
for i in range(car_nrows): # 循环逐行打印
if i == 0: # 跳过第一行
continue
row_value = car_sheet.row_values(i)[:car_nrows]
print(row_value)
standard_car = row_value[1]
notstandard_car = row_value[2]
canshu = (map_database, car_table, standard_car, notstandard_car)
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 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)