cms_class_20241201.py 31 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722
  1. import numpy as np
  2. from scipy.signal import hilbert
  3. from scipy.fft import ifft
  4. import plotly.graph_objs as go
  5. import pandas as pd
  6. from sqlalchemy import create_engine, text
  7. import sqlalchemy
  8. from typing import Dict,Any
  9. import json
  10. import ast
  11. import math
  12. '''
  13. # 输入:
  14. {
  15. "ids":[12345,121212],
  16. "windCode":"xxxx",
  17. "analysisType":"xxxxx",
  18. "fmin":int(xxxx) (None),
  19. "fmax":"int(xxxx) (None),
  20. }
  21. [{id:xxxx,"time":xxx},{}]
  22. id[123456]
  23. # 通过id,读取mysql,获取数据
  24. engine = create_engine('mysql+pymysql://root:admin123456@192.168.50.235:30306/energy_data')
  25. def get_by_id(table_name,id):
  26. lastday_df_sql = f"SELECT * FROM {table_name} where id = {id} "
  27. # print(lastday_df_sql)
  28. df = pd.read_sql(lastday_df_sql, engine)
  29. return df
  30. select distinct id, timeStamp from table_name group by ids
  31. ids time
  32. 1 xxx
  33. 2 xxxx
  34. df_data = []
  35. # for id in ids:
  36. # sql_data = get_by_id('SKF001_wave',id)
  37. # df_data.append(sql_data)
  38. # print(sql_data)
  39. [df1,df2]
  40. '''
  41. '''
  42. 数据库字段:
  43. "samplingFrequency"
  44. "timeStamp"
  45. "mesureData"
  46. '''
  47. # %%
  48. # %%
  49. # 主要的类
  50. class CMSAnalyst:
  51. def __init__(self, fmin, fmax, table_name, ids):
  52. self.table_name =table_name
  53. self.ids = ids
  54. # envelope_spectrum_analysis
  55. # datas是[df1,df2,.....]
  56. #从数据库获取原始数据
  57. self.datas = self._get_by_id(table_name,ids)
  58. self.datas = [
  59. df[['id', 'mesure_data', 'time_stamp', 'sampling_frequency',
  60. 'wind_turbine_number', 'rotational_speed', 'mesure_point_name']]
  61. for df in self.datas
  62. ]
  63. # 只输入一个id,返回一个[df],所以拿到self.data[0]
  64. self.data_filter = self.datas[0]
  65. # print("mesure_data sample:", self.data_filter['mesure_data'].iloc[0]) # 打印第一条数据
  66. self.data = np.array(ast.literal_eval(self.data_filter['mesure_data'].iloc[0]))
  67. # print(self.data_filter)
  68. # 取数据列
  69. self.data = np.array(ast.literal_eval(self.data_filter['mesure_data'][0]))
  70. self.envelope_spectrum_m = self.data.shape[0]
  71. self.envelope_spectrum_n = 1
  72. #设置分析参数
  73. self.fs = int(self.data_filter['sampling_frequency'].iloc[0])
  74. self.envelope_spectrum_t = np.arange(self.envelope_spectrum_m) / self.fs
  75. self.fmin = fmin if fmin is not None else 0
  76. self.fmax = fmax if fmax is not None else float('inf')
  77. self.envelope_spectrum_y = self._bandpass_filter(self.data)
  78. self.f, self.HP = self._calculate_envelope_spectrum(self.envelope_spectrum_y)
  79. #设备信息
  80. self.wind_code = self.data_filter['wind_turbine_number'].iloc[0]
  81. self.rpm_Gen = self.data_filter['rotational_speed'].iloc[0]
  82. self.mesure_point_name = self.data_filter['mesure_point_name'].iloc[0]
  83. self.fn_Gen = round(self.rpm_Gen/60,2)
  84. self.CF = self.Characteristic_Frequency()
  85. print(self.CF)
  86. self.CF = pd.DataFrame(self.CF,index=[0])
  87. print(self.CF)
  88. print(self.rpm_Gen)
  89. #if self.CF['type'].iloc[0] == 'bearing':
  90. n_rolls_m = self.CF['n_rolls'].iloc[0]
  91. d_rolls_m = self.CF['d_rolls'].iloc[0]
  92. D_diameter_m = self.CF['D_diameter'].iloc[0]
  93. theta_deg_m = self.CF['theta_deg'].iloc[0]
  94. print(n_rolls_m)
  95. print(d_rolls_m)
  96. print(D_diameter_m)
  97. print(theta_deg_m)
  98. self.bearing_frequencies = self.calculate_bearing_frequencies(n_rolls_m, d_rolls_m, D_diameter_m, theta_deg_m, self.rpm_Gen)
  99. print(self.bearing_frequencies)
  100. self.bearing_frequencies = pd.DataFrame(self.bearing_frequencies,index=[0])
  101. print(self.bearing_frequencies)
  102. # frequency_domain_analysis
  103. (
  104. self.frequency_domain_analysis_t,
  105. self.frequency_domain_analysis_f,
  106. self.frequency_domain_analysis_m,
  107. self.frequency_domain_analysis_mag,
  108. self.frequency_domain_analysis_Xrms,
  109. ) = self._calculate_spectrum(self.data)
  110. # time_domain_analysis
  111. self.time_domain_analysis_t = np.arange(self.data.shape[0]) / self.fs
  112. # def _get_by_id(self, windcode, ids):
  113. # df_res = []
  114. # #engine = create_engine('mysql+pymysql://root:admin123456@192.168.50.235:30306/energy_data_prod')
  115. # engine = create_engine('mysql+pymysql://root:admin123456@106.120.102.238:10336/energy_data_prod')
  116. # for id in ids:
  117. # table_name=windcode+'_wave'
  118. # lastday_df_sql = f"SELECT * FROM {table_name} where id = {id} "
  119. # # print(lastday_df_sql)
  120. # df = pd.read_sql(lastday_df_sql, engine)
  121. # df_res.append(df)
  122. # return df_res
  123. # def _get_by_id(self, windcode, ids):
  124. # #engine = create_engine('mysql+pymysql://root:admin123456@106.120.102.238:10336/energy_data_prod')
  125. # engine = create_engine('mysql+pymysql://root:admin123456@192.168.50.235:30306/energy_data_prod')
  126. # table_name = windcode + '_wave'
  127. # ids_str = ','.join(map(str, ids))
  128. # sql = f"SELECT * FROM {table_name} WHERE id IN ({ids_str}) ORDER BY time_stamp"
  129. # df = pd.read_sql(sql, engine)
  130. # # 按ID分组返回
  131. # grouped = [group for _, group in df.groupby('id')]
  132. # return grouped
  133. def _get_by_id(self, windcode, ids):
  134. engine = create_engine('mysql+pymysql://root:admin123456@192.168.50.235:30306/energy_data_prod')
  135. table_name = windcode + '_wave'
  136. ids_str = ','.join(map(str, ids))
  137. sql = f"SELECT * FROM {table_name} WHERE id IN ({ids_str}) ORDER BY time_stamp"
  138. print("Executing SQL:", sql) # 打印 SQL
  139. df = pd.read_sql(sql, engine)
  140. print("Returned DataFrame shape:", df.shape) # 检查返回的数据量
  141. grouped = [group for _, group in df.groupby('id')]
  142. return grouped
  143. # envelope_spectrum_analysis 包络谱分析
  144. def _bandpass_filter(self, data):
  145. """带通滤波"""
  146. m= data.shape[0]
  147. ni = round(self.fmin * self.envelope_spectrum_m / self.fs + 1)
  148. # na = round(self.fmax * self.envelope_spectrum_m / self.fs + 1)
  149. if self.fmax == float('inf'):
  150. na = m
  151. else:
  152. na = round(self.fmax * m / self.fs + 1)
  153. col = 1
  154. y = np.zeros((self.envelope_spectrum_m, col))
  155. # for p in range(col):
  156. # print(data.shape,p)
  157. z = np.fft.fft(data)
  158. a = np.zeros(self.envelope_spectrum_m, dtype=complex)
  159. a[ni:na] = z[ni:na]
  160. a[self.envelope_spectrum_m - na + 1 : self.envelope_spectrum_m - ni + 1] = z[
  161. self.envelope_spectrum_m - na + 1 : self.envelope_spectrum_m - ni + 1
  162. ]
  163. z = np.fft.ifft(a)
  164. y[:, 0] = np.real(z)
  165. return y
  166. def _calculate_envelope_spectrum(self, y):
  167. """计算包络谱"""
  168. m, n = y.shape
  169. HP = np.zeros((m, n))
  170. col = 1
  171. for p in range(col):
  172. H = np.abs(hilbert(y[:, p] - np.mean(y[:, p])))
  173. HP[:, p] = np.abs(np.fft.fft(H - np.mean(H))) * 2 / m
  174. f = np.fft.fftfreq(m, d=1 / self.fs)
  175. return f, HP
  176. def envelope_spectrum(self):
  177. """绘制包络谱"""
  178. # 只取正频率部分
  179. positive_frequencies = self.f[: self.envelope_spectrum_m // 2]
  180. positive_HP = self.HP[: self.envelope_spectrum_m // 2, 0]
  181. x = positive_frequencies
  182. y = positive_HP
  183. title = "包络谱"
  184. xaxis = "频率(Hz)"
  185. yaxis = "加速度(m/s^2)"
  186. Xrms = np.sqrt(np.mean(y**2)) # 加速度均方根值(有效值)
  187. rpm_Gen = round(self.rpm_Gen, 2)
  188. BPFI_1X = round(self.bearing_frequencies['BPFI'].iloc[0], 2)
  189. BPFO_1X = round(self.bearing_frequencies['BPFO'].iloc[0], 2)
  190. BSF_1X = round(self.bearing_frequencies['BSF'].iloc[0], 2)
  191. FTF_1X = round(self.bearing_frequencies['FTF'].iloc[0], 2)
  192. fn_Gen = round(self.fn_Gen, 2)
  193. _3P_1X = round(self.fn_Gen, 2) * 3
  194. # if self.CF['type'].iloc[0] == 'bearing':
  195. result = {
  196. "fs":self.fs,
  197. "Xrms":round(Xrms, 2),
  198. "x":list(x),
  199. "y":list(y),
  200. "title":title,
  201. "xaxis":xaxis,
  202. "yaxis":yaxis,
  203. "rpm_Gen": round(rpm_Gen, 2), # 转速r/min
  204. "BPFI": [{"Xaxis": BPFI_1X ,"val": "1BPFI"},{"Xaxis": BPFI_1X*2 ,"val": "2BPFI"},
  205. {"Xaxis": BPFI_1X*3, "val": "3BPFI"}, {"Xaxis": BPFI_1X*4, "val": "4BPFI"},
  206. {"Xaxis": BPFI_1X*5, "val": "5BPFI"}, {"Xaxis": BPFI_1X*6, "val": "6BPFI"}],
  207. "BPFO": [{"Xaxis": BPFO_1X ,"val": "1BPFO"},{"Xaxis": BPFO_1X*2 ,"val": "2BPFO"},
  208. {"Xaxis": BPFO_1X*3, "val": "3BPFO"}, {"Xaxis": BPFO_1X*4, "val": "4BPFO"},
  209. {"Xaxis": BPFO_1X*5, "val": "5BPFO"}, {"Xaxis": BPFO_1X*6, "val": "6BPFO"}],
  210. "BSF": [{"Xaxis": BSF_1X ,"val": "1BSF"},{"Xaxis": BSF_1X*2 ,"val": "2BSF"},
  211. {"Xaxis": BSF_1X*3, "val": "3BSF"}, {"Xaxis": BSF_1X*4, "val": "4BSF"},
  212. {"Xaxis": BSF_1X*5, "val": "5BSF"}, {"Xaxis": BSF_1X*6, "val": "6BSF"}],
  213. "FTF": [{"Xaxis": FTF_1X ,"val": "1FTF"},{"Xaxis": FTF_1X*2 ,"val": "2FTF"},
  214. {"Xaxis": FTF_1X*3, "val": "3FTF"}, {"Xaxis": FTF_1X*4, "val": "4FTF"},
  215. {"Xaxis": FTF_1X*5, "val": "5FTF"}, {"Xaxis": FTF_1X*6, "val": "6FTF"}],
  216. "fn_Gen":[{"Xaxis": fn_Gen ,"val": "1X"},{"Xaxis": fn_Gen*2 ,"val": "2X"},
  217. {"Xaxis": fn_Gen*3, "val": "3X"}, {"Xaxis": fn_Gen*4, "val": "4X"},
  218. {"Xaxis": fn_Gen*5, "val": "5X"}, {"Xaxis": fn_Gen*6, "val": "6X"}],
  219. "B3P":_3P_1X,
  220. }
  221. # result = json.dumps(result, ensure_ascii=False)
  222. result = self.replace_nan(result)
  223. return result
  224. # frequency_domain_analysis 频谱分析
  225. def _calculate_spectrum(self, data):
  226. """计算频谱"""
  227. m = data.shape[0]
  228. n = 1
  229. t = np.arange(m) / self.fs
  230. mag = np.zeros((m, n))
  231. Xrms = np.sqrt(np.mean(data**2)) # 加速度均方根值(有效值)
  232. # col=1
  233. # for p in range(col):
  234. mag = np.abs(np.fft.fft(data - np.mean(data))) * 2 / m
  235. f = np.fft.fftfreq(m, d=1 / self.fs)
  236. return t, f, m, mag, Xrms
  237. def frequency_domain(self):
  238. """绘制频域波形参数"""
  239. # 只取正频率部分
  240. positive_frequencies = self.frequency_domain_analysis_f[
  241. : self.frequency_domain_analysis_m // 2
  242. ]
  243. positive_mag = self.frequency_domain_analysis_mag[
  244. : self.frequency_domain_analysis_m // 2
  245. ]
  246. x = positive_frequencies
  247. y = positive_mag
  248. title = "频域信号"
  249. xaxis = "频率(Hz)"
  250. yaxis = "加速度(m/s^2)"
  251. Xrms = self.frequency_domain_analysis_Xrms
  252. rpm_Gen = round(self.rpm_Gen, 2)
  253. BPFI_1X = round(self.bearing_frequencies['BPFI'].iloc[0], 2)
  254. BPFO_1X = round(self.bearing_frequencies['BPFO'].iloc[0], 2)
  255. BSF_1X = round(self.bearing_frequencies['BSF'].iloc[0], 2)
  256. FTF_1X = round(self.bearing_frequencies['FTF'].iloc[0], 2)
  257. fn_Gen = round(self.fn_Gen, 2)
  258. _3P_1X = round(self.fn_Gen, 2) * 3
  259. # if self.CF['type'].iloc[0] == 'bearing':
  260. result = {
  261. "fs":self.fs,
  262. "Xrms":round(Xrms, 2),
  263. "x":list(x),
  264. "y":list(y),
  265. "title":title,
  266. "xaxis":xaxis,
  267. "yaxis":yaxis,
  268. "rpm_Gen": round(rpm_Gen, 2), # 转速r/min
  269. "BPFI": [{"Xaxis": BPFI_1X ,"val": "1BPFI"},{"Xaxis": BPFI_1X*2 ,"val": "2BPFI"},
  270. {"Xaxis": BPFI_1X*3, "val": "3BPFI"}, {"Xaxis": BPFI_1X*4, "val": "4BPFI"},
  271. {"Xaxis": BPFI_1X*5, "val": "5BPFI"}, {"Xaxis": BPFI_1X*6, "val": "6BPFI"}],
  272. "BPFO": [{"Xaxis": BPFO_1X ,"val": "1BPFO"},{"Xaxis": BPFO_1X*2 ,"val": "2BPFO"},
  273. {"Xaxis": BPFO_1X*3, "val": "3BPFO"}, {"Xaxis": BPFO_1X*4, "val": "4BPFO"},
  274. {"Xaxis": BPFO_1X*5, "val": "5BPFO"}, {"Xaxis": BPFO_1X*6, "val": "6BPFO"}],
  275. "BSF": [{"Xaxis": BSF_1X ,"val": "1BSF"},{"Xaxis": BSF_1X*2 ,"val": "2BSF"},
  276. {"Xaxis": BSF_1X*3, "val": "3BSF"}, {"Xaxis": BSF_1X*4, "val": "4BSF"},
  277. {"Xaxis": BSF_1X*5, "val": "5BSF"}, {"Xaxis": BSF_1X*6, "val": "6BSF"}],
  278. "FTF": [{"Xaxis": FTF_1X ,"val": "1FTF"},{"Xaxis": FTF_1X*2 ,"val": "2FTF"},
  279. {"Xaxis": FTF_1X*3, "val": "3FTF"}, {"Xaxis": FTF_1X*4, "val": "4FTF"},
  280. {"Xaxis": FTF_1X*5, "val": "5FTF"}, {"Xaxis": FTF_1X*6, "val": "6FTF"}],
  281. "fn_Gen":[{"Xaxis": fn_Gen ,"val": "1X"},{"Xaxis": fn_Gen*2 ,"val": "2X"},
  282. {"Xaxis": fn_Gen*3, "val": "3X"}, {"Xaxis": fn_Gen*4, "val": "4X"},
  283. {"Xaxis": fn_Gen*5, "val": "5X"}, {"Xaxis": fn_Gen*6, "val": "6X"}],
  284. "B3P":_3P_1X,
  285. }
  286. result = self.replace_nan(result)
  287. result = json.dumps(result, ensure_ascii=False)
  288. return result
  289. # time_domain_analysis 时域分析
  290. def time_domain(self):
  291. """绘制时域波形参数"""
  292. x = self.time_domain_analysis_t
  293. y = self.data
  294. rpm_Gen =self.rpm_Gen
  295. title = "时间域信号"
  296. xaxis = "时间(s)"
  297. yaxis = "加速度(m/s^2)"
  298. # 图片右侧统计量
  299. mean_value = np.mean(y)#平均值
  300. max_value = np.max(y)#最大值
  301. min_value = np.min(y)#最小值
  302. Xrms = np.sqrt(np.mean(y**2)) # 加速度均方根值(有效值)
  303. Xp = (max_value - min_value) / 2 # 峰值(单峰最大值) # 峰值
  304. Xpp=max_value-min_value#峰峰值
  305. Cf = Xp / Xrms # 峰值指标
  306. Sf = Xrms / mean_value # 波形指标
  307. If = Xp / np.mean(np.abs(y)) # 脉冲指标
  308. Xr = np.mean(np.sqrt(np.abs(y))) ** 2 # 方根幅值
  309. Ce = Xp / Xr # 裕度指标
  310. # 计算每个数据点的绝对值减去均值后的三次方,并求和
  311. sum_abs_diff_cubed_3 = np.mean((np.abs(y) - mean_value) ** 3)
  312. # 计算偏度指标
  313. Cw = sum_abs_diff_cubed_3 / (Xrms**3)
  314. # 计算每个数据点的绝对值减去均值后的四次方,并求和
  315. sum_abs_diff_cubed_4 = np.mean((np.abs(y) - mean_value) ** 4)
  316. # 计算峭度指标
  317. Cq = sum_abs_diff_cubed_4 / (Xrms**4)
  318. result = {
  319. "x":list(x),
  320. "y":list(y),
  321. "title":title,
  322. "xaxis":xaxis,
  323. "yaxis":yaxis,
  324. "fs":self.fs,
  325. "Xrms":round(Xrms, 2),#有效值
  326. "mean_value":round(mean_value, 2),# 均值
  327. "max_value":round(max_value, 2),# 最大值
  328. "min_value":round(min_value, 2), # 最小值
  329. "Xp":round(Xp, 2),# 峰值
  330. "Xpp":round(Xpp, 2),# 峰峰值
  331. "Cf":round(Cf, 2),# 峰值指标
  332. "Sf":round(Sf, 2),# 波形因子
  333. "If":round(If, 2),# 脉冲指标
  334. "Ce":round(Ce, 2),# 裕度指标
  335. "Cw":round(Cw, 2) ,# 偏度指标
  336. "Cq":round(Cq, 2) ,# 峭度指标
  337. "rpm_Gen": round(rpm_Gen, 2), # 转速r/min
  338. }
  339. result = self.replace_nan(result)
  340. result = json.dumps(result, ensure_ascii=False)
  341. return result
  342. def trend_analysis(self) -> str:
  343. """
  344. 优化后的趋势分析方法(向量化计算统计指标)
  345. 返回 JSON 字符串,包含所有时间点的统计结果。
  346. """
  347. for df in self.datas:
  348. df['parsed_data'] = df['mesure_data'].apply(json.loads)
  349. # 1. 合并所有数据并解析 mesure_data
  350. combined_df = pd.concat(self.datas)
  351. combined_df['parsed_data'] = combined_df['mesure_data'].apply(json.loads) # 批量解析 JSON
  352. # 2. 向量化计算统计指标(避免逐行循环)
  353. def calculate_stats(group: pd.DataFrame) -> Dict[str, Any]:
  354. data = np.array(group['parsed_data'].iloc[0]) # 提取振动数据数组
  355. fs = int(group['sampling_frequency'].iloc[0]) # 采样频率
  356. dt = 1 / fs # 时间间隔
  357. # 计算时域指标(向量化操作)
  358. mean = np.mean(data)
  359. max_val = np.max(data)
  360. min_val = np.min(data)
  361. Xrms = np.sqrt(np.mean(data**2))
  362. Xp = (max_val - min_val) / 2
  363. Cf = Xp / Xrms
  364. Sf = Xrms / mean if mean != 0 else 0
  365. If = Xp / np.mean(np.abs(data))
  366. Xr = np.mean(np.sqrt(np.abs(data))) ** 2
  367. Ce = Xp / Xr
  368. # 计算偏度和峭度
  369. # 计算速度有效值
  370. velocity = np.cumsum(data) * dt
  371. velocity_rms = np.sqrt(np.mean(velocity**2))
  372. Cw = np.mean((data - mean) ** 3) / (Xrms ** 3) if Xrms != 0 else 0
  373. Cq = np.mean((data - mean) ** 4) / (Xrms ** 4) if Xrms != 0 else 0
  374. return {
  375. "fs": fs,
  376. "Mean": round(mean, 2),
  377. "Max": round(max_val, 2),
  378. "Min": round(min_val, 2),
  379. "Xrms": round(Xrms, 2),
  380. "Xp": round(Xp, 2),
  381. "If": round(If, 2),
  382. "Cf": round(Cf, 2),
  383. "Sf": round(Sf, 2),
  384. "Ce": round(Ce, 2),
  385. "Cw": round(Cw, 2),
  386. "Cq": round(Cq, 2),
  387. "velocity_rms": round(velocity_rms, 2),
  388. "time_stamp": str(group['time_stamp'].iloc[0])
  389. }
  390. # 3. 按 ID 分组并应用统计计算
  391. stats = combined_df.groupby('id').apply(calculate_stats).tolist()
  392. # 4. 返回 JSON 格式结果
  393. return json.dumps(stats, ensure_ascii=False)
  394. def Characteristic_Frequency(self):
  395. """提取轴承、齿轮等参数"""
  396. str1 = self.mesure_point_name
  397. print(str1)
  398. # 2、连接233的数据库'energy_show',从表'wind_engine_group'查找风机编号'engine_code'对应的机型编号'mill_type_code'
  399. engine_code = self.wind_code
  400. print(engine_code)
  401. Engine = create_engine('mysql+pymysql://admin:admin123456@192.168.50.233:3306/energy_show')
  402. #Engine = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
  403. # df_sql2 = f"SELECT * FROM {'wind_engine_group'} where engine_code = {'engine_code'} "
  404. df_sql2 = f"SELECT * FROM wind_engine_group WHERE engine_code = '{engine_code}'"
  405. df2 = pd.read_sql(df_sql2, Engine)
  406. mill_type_code = df2['mill_type_code'].iloc[0]
  407. print(mill_type_code)
  408. # # 3、从表'unit_bearings'中通过机型编号'mill_type_code'查找部件'brand'、'model'的参数信息
  409. # 3、从相关的表中通过机型编号'mill_type_code'或者齿轮箱编号gearbox_code查找部件'brand'、'model'的参数信息
  410. #unit_bearings主轴承参数表 关键词"main_bearing"
  411. if 'main_bearing' in str1:
  412. print("main_bearing")
  413. df_sql3 = f"SELECT * FROM unit_bearings WHERE mill_type_code = '{mill_type_code}' "
  414. df3 = pd.read_sql(df_sql3, Engine)
  415. if df3.empty:
  416. print("警告: 没有找到有效的机型信息")
  417. if 'front' in str1:
  418. brand = 'front_bearing' + '_brand'
  419. model = 'front_bearing' + '_model'
  420. front_has_value = not pd.isna(df3[brand].iloc[0]) and not pd.isna(df3[model].iloc[0])
  421. if not front_has_value:
  422. print("警告: 没有找到有效的品牌信息")
  423. elif 'rear' in str1:
  424. brand = 'rear_bearing' + '_brand'
  425. model = 'rear_bearing' + '_model'
  426. end_has_value = not pd.isna(df3[brand].iloc[0]) and not pd.isna(df3[model].iloc[0])
  427. if not end_has_value:
  428. print("警告: 没有找到有效的品牌信息")
  429. else:
  430. # 当没有指定 front 或 end 时,自动选择有值的轴承信息
  431. front_brand_col = 'front_bearing_brand'
  432. front_model_col = 'front_bearing_model'
  433. rear_brand_col = 'rear_bearing_brand'
  434. rear_model_col = 'rear_bearing_model'
  435. # 检查 front_bearing 是否有值
  436. front_has_value = not pd.isna(df3[front_brand_col].iloc[0]) and not pd.isna(df3[front_model_col].iloc[0])
  437. # 检查 end_bearing 是否有值
  438. end_has_value = not pd.isna(df3[rear_brand_col].iloc[0]) and not pd.isna(df3[rear_model_col].iloc[0])
  439. # 根据检查结果选择合适的列
  440. if front_has_value and end_has_value:
  441. # 如果两者都有值,默认选择 front
  442. brand = front_brand_col
  443. model = front_model_col
  444. elif front_has_value:
  445. brand = front_brand_col
  446. model = front_model_col
  447. elif end_has_value:
  448. brand = rear_brand_col
  449. model = rear_model_col
  450. else:
  451. # 如果两者都没有有效值,设置默认值或抛出异常
  452. print("警告: 没有找到有效的轴承信息")
  453. brand = front_brand_col # 默认使用 front
  454. model = front_model_col # 默认使用 front
  455. print(brand)
  456. _brand = df3[brand].iloc[0]
  457. _model = df3[model].iloc[0]
  458. print(_brand)
  459. print(_model)
  460. #unit_dynamo 发电机参数表 关键词generator stator
  461. elif 'generator'in str1 or 'stator' in str1:
  462. print("generator or 'stator'")
  463. # df_sql3 = f"SELECT * FROM {'unit_dynamo'} where mill_type_code = {'mill_type_code'} "
  464. df_sql3 = f"SELECT * FROM unit_dynamo WHERE mill_type_code = '{mill_type_code}' "
  465. df3 = pd.read_sql(df_sql3, Engine)
  466. if 'non' in str1:
  467. brand = 'non_drive_end_bearing' + '_brand'
  468. model = 'non_drive_end_bearing' + '_model'
  469. else:
  470. brand = 'drive_end_bearing' + '_brand'
  471. model = 'drive_end_bearing' + '_model'
  472. print(brand)
  473. _brand = df3[brand].iloc[0]
  474. _model = df3[model].iloc[0]
  475. print(_brand)
  476. print(_model)
  477. #齿轮箱区分行星轮/平行轮 和 轴承两个表
  478. elif 'gearbox' in str1:
  479. print("gearbox")
  480. #根据mill_type_code从unit_gearbox表中获得gearbox_code
  481. df_sql3 = f"SELECT * FROM unit_gearbox WHERE mill_type_code = '{mill_type_code}' "
  482. df3 = pd.read_sql(df_sql3, Engine)
  483. gearbox_code =df3['code'].iloc[0]
  484. print(gearbox_code)
  485. #Engine33 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
  486. #如果是行星轮/平行轮 则从unit_gearbox_structure 表中取数据
  487. if 'planet'in str1 or 'sun' in str1:
  488. print("'planet' or 'sun' ")
  489. gearbox_structure =1 if 'planet'in str1 else 2
  490. planetary_gear_grade =1
  491. if 'first' in str1:
  492. planetary_gear_grade =1
  493. elif 'second'in str1:
  494. planetary_gear_grade =2
  495. elif 'third'in str1:
  496. planetary_gear_grade =3
  497. # df_sql33 = f"SELECT * FROM unit_gearbox_structure WHERE gearbox_code = '{gearbox_code}' "
  498. df_sql33 = f"""
  499. SELECT bearing_brand, bearing_model
  500. FROM unit_gearbox_structure
  501. WHERE gearbox_code = '{gearbox_code}'
  502. AND gearbox_structure = '{gearbox_structure}'
  503. AND planetary_gear_grade = '{planetary_gear_grade}'
  504. """
  505. df33 = pd.read_sql(df_sql33, Engine)
  506. if df33.empty:
  507. print("unit_gearbox_structure没有该测点的参数")
  508. else:
  509. brand = 'bearing' + '_brand'
  510. model = 'bearing' + '_model'
  511. print(brand)
  512. _brand = df33[brand].iloc[0]
  513. _model = df33[model].iloc[0]
  514. has_value = not pd.isna(df33[brand].iloc[0]) and not pd.isna(df33[model].iloc[0])
  515. if has_value:
  516. print(_brand)
  517. print(_model)
  518. else:
  519. print("警告: 没有找到有效的轴承信息")
  520. #如果是齿轮箱轴承 则从unit_gearbox_bearings 表中取数据
  521. elif 'shaft' in str1 or'input' in str1:
  522. print("'shaft'or'input'")
  523. # df_sql33 = f"SELECT * FROM unit_gearbox_bearings WHERE gearbox_code = '{gearbox_code}' "
  524. # df33 = pd.read_sql(df_sql33, Engine33)
  525. #高速轴 低速中间轴 取bearing_rs/gs均可
  526. parallel_wheel_grade=1
  527. if 'low_speed' in str1:
  528. parallel_wheel_grade =1
  529. elif 'low_speed_intermediate' in str1:
  530. parallel_wheel_grade =2
  531. elif 'high_speed' in str1:
  532. parallel_wheel_grade =3
  533. # df_sql33 = f"SELECT * FROM unit_gearbox_bearings WHERE gearbox_code = '{gearbox_code}' "
  534. df_sql33 = f"""
  535. SELECT bearing_rs_brand, bearing_rs_model, bearing_gs_brand, bearing_gs_model
  536. FROM unit_gearbox_bearings
  537. WHERE gearbox_code = '{gearbox_code}'
  538. AND parallel_wheel_grade = '{parallel_wheel_grade}'
  539. """
  540. df33 = pd.read_sql(df_sql33, Engine)
  541. if not df33.empty:
  542. if 'high_speed' in str1 or 'low_speed_intermediate' in str1:
  543. rs_brand = 'bearing_rs' + '_brand'
  544. rs_model = 'bearing_rs' + '_model'
  545. gs_brand = 'bearing_gs' + '_brand'
  546. gs_model = 'bearing_gs' + '_model'
  547. rs_has_value = not pd.isna(df33[rs_brand].iloc[0]) and not pd.isna(df33[rs_model].iloc[0])
  548. gs_has_value = not pd.isna(df33[gs_brand].iloc[0]) and not pd.isna(df33[gs_model].iloc[0])
  549. if rs_has_value and gs_has_value:
  550. brand = rs_brand
  551. model = rs_model
  552. elif rs_has_value:
  553. brand = rs_brand
  554. model = rs_model
  555. elif gs_has_value:
  556. brand = gs_brand
  557. model = gs_model
  558. else:
  559. print("警告: 没有找到有效的品牌信息")
  560. brand = rs_brand
  561. model = rs_model
  562. #低速轴 取bearing_model
  563. elif 'low_speed'in str1:
  564. brand = 'bearing' + '_brand'
  565. model = 'bearing' + '_model'
  566. else:
  567. print("警告: 没有找到有效的轴承信息")
  568. print(brand)
  569. _brand = df33[brand].iloc[0]
  570. _model = df33[model].iloc[0]
  571. print(_brand)
  572. print(_model)
  573. # 4、从表'unit_dict_brand_model'中通过'_brand'、'_model'查找部件的参数信息
  574. df_sql4 = f"SELECT * FROM unit_dict_brand_model where manufacture = %s AND model_number = %s"
  575. params = [(_brand, _model)]
  576. df4 = pd.read_sql(df_sql4, Engine, params=params)
  577. n_rolls = df4['rolls_number'].iloc[0]
  578. d_rolls = df4['rolls_diameter'].iloc[0]
  579. D_diameter = df4['circle_diameter'].iloc[0]
  580. theta_deg = df4['theta_deg'].iloc[0]
  581. result = {
  582. "type":'bearing',
  583. "n_rolls":round(n_rolls, 2),
  584. "d_rolls":round(d_rolls, 2),
  585. "D_diameter":round(D_diameter, 2),
  586. "theta_deg":round(theta_deg, 2),
  587. }
  588. # result = json.dumps(result, ensure_ascii=False)
  589. return result
  590. def calculate_bearing_frequencies(self, n, d, D, theta_deg, rpm):
  591. """
  592. 计算轴承各部件特征频率
  593. 参数:
  594. n (int): 滚动体数量
  595. d (float): 滚动体直径(单位:mm)
  596. D (float): 轴承节圆直径(滚动体中心圆直径,单位:mm)
  597. theta_deg (float): 接触角(单位:度)
  598. rpm (float): 转速(转/分钟)
  599. 返回:
  600. dict: 包含各特征频率的字典(单位:Hz)
  601. """
  602. # 转换角度为弧度
  603. theta = math.radians(theta_deg)
  604. # 转换直径单位为米(保持单位一致性,实际计算中比值抵消单位影响)
  605. # 注意:由于公式中使用的是比值,单位可以保持mm不需要转换
  606. ratio = d / D
  607. # 基础频率计算(转/秒)
  608. f_r = rpm / 60.0
  609. # 计算各特征频率
  610. BPFI = n / 2 * (1 + ratio * math.cos(theta)) * f_r # 内圈故障频率
  611. BPFO = n / 2 * (1 - ratio * math.cos(theta)) * f_r # 外圈故障频率
  612. BSF = (D / (2 * d)) * (1 - (ratio ** 2) * (math.cos(theta) ** 2)) * f_r # 滚动体故障频率
  613. FTF = 0.5 * (1 - ratio * math.cos(theta)) * f_r # 保持架故障频率
  614. return {
  615. "BPFI": round(BPFI, 2),
  616. "BPFO": round(BPFO, 2),
  617. "BSF": round(BSF, 2),
  618. "FTF": round(FTF, 2),
  619. }
  620. #检查返回结果是否有nan 若有,则替换成none
  621. def replace_nan(self, obj):
  622. if isinstance(obj, dict):
  623. return {k: self.replace_nan(v) for k, v in obj.items()}
  624. elif isinstance(obj, list):
  625. return [self.replace_nan(item) for item in obj]
  626. elif isinstance(obj, float) and math.isnan(obj):
  627. return None
  628. return obj
  629. if __name__ == "__main__":
  630. # table_name = "SKF001_wave"
  631. # ids = [67803,67804]
  632. # fmin, fmax = None, None
  633. cms = CMSAnalyst(fmin, fmax,table_name,ids)
  634. time_domain = cms.time_domain()
  635. # print(time_domain)
  636. '''
  637. trace = go.Scatter(
  638. x=time_domain['x'],
  639. y=time_domain['y'],
  640. mode="lines",
  641. name=time_domain['title'],
  642. )
  643. layout = go.Layout(
  644. title= time_domain['title'],
  645. xaxis=dict(title=time_domain["xaxis"]),
  646. yaxis=dict(title=time_domain["yaxis"]),
  647. )
  648. fig = go.Figure(data=[trace], layout=layout)
  649. fig.show()
  650. '''
  651. # data_path_lsit = ["test1.csv", "test2.csv"]
  652. # trend_analysis_test = cms.trend_analysis(data_path_lsit, fmin, fmax)
  653. # print(trend_analysis_test)