trans_conf_service.py 5.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146
  1. # -*- coding: utf-8 -*-
  2. # @Time : 2025/1/9
  3. # @Author : 魏志亮
  4. from datetime import datetime
  5. from service.common_connect import trans
  6. def update_timeout_trans_data():
  7. sql = """
  8. UPDATE data_transfer
  9. SET trans_sys_status = 2,err_info='运行超时失败',transfer_status=2
  10. WHERE
  11. TIMESTAMPDIFF(HOUR, transfer_start_time, NOW()) > 24
  12. AND trans_sys_status = 0
  13. """
  14. trans.execute(sql)
  15. def update_trans_status_running(id, save_db=True):
  16. if save_db:
  17. exec_sql = """
  18. update data_transfer set transfer_status = 0,trans_sys_status = 0 ,transfer_start_time = now(),err_info='',
  19. engine_count =0,time_granularity=0,transfer_finish_time=null,transfer_progress=0,
  20. data_min_time= null,data_max_time= null,transfer_data_count=null
  21. where id = %s
  22. """
  23. trans.execute(exec_sql, id)
  24. def update_archive_success(id, archive_path, save_db=True):
  25. if save_db:
  26. exec_sql = """
  27. update data_transfer set transfer_progress=70,archive_path = %s
  28. where id = %s
  29. """
  30. trans.execute(exec_sql, (archive_path, id))
  31. def update_trans_status_error(id, message="", save_db=True):
  32. if save_db:
  33. exec_sql = """
  34. update data_transfer set transfer_status = 2,trans_sys_status=2 ,err_info= %s,transfer_finish_time=now()
  35. where id = %s
  36. """
  37. message = message if len(message) <= 200 else message[0:200]
  38. trans.execute(exec_sql, (message, id))
  39. def update_trans_status_success(id, wind_count=0, time_granularity=0,
  40. min_date=datetime.now(),
  41. max_date=datetime.now(),
  42. total_count=0, save_db=True):
  43. if save_db:
  44. if min_date is not None:
  45. exec_sql = """
  46. update data_transfer set transfer_status = 1,trans_sys_status = 1,transfer_progress=100,err_info = '',engine_count =%s,time_granularity=%s,transfer_finish_time=now(),
  47. data_min_time= %s,data_max_time= %s,transfer_data_count=%s
  48. where id = %s
  49. """
  50. trans.execute(exec_sql, (wind_count, time_granularity, min_date, max_date, total_count, id))
  51. else:
  52. exec_sql = """
  53. update data_transfer set transfer_status = 1,trans_sys_status = 1,transfer_progress = 100,err_info = '',engine_count =%s,time_granularity=%s,transfer_finish_time=now()
  54. where id = %s
  55. """
  56. trans.execute(exec_sql, (wind_count, time_granularity, id))
  57. def update_trans_transfer_progress(id, transfer_progress=0, save_db=True):
  58. print(id, transfer_progress)
  59. if save_db:
  60. exec_sql = """
  61. update data_transfer set transfer_progress =%s where id = %s
  62. """
  63. trans.execute(exec_sql, (int(transfer_progress), id))
  64. def get_now_running_count():
  65. query_running_sql = """
  66. select count(1) as count from data_transfer where trans_sys_status = 0
  67. """
  68. data = trans.execute(query_running_sql)
  69. now_count = int(data[0]['count'])
  70. return now_count
  71. # 获取执行的数据
  72. def get_batch_exec_data() -> dict:
  73. query_next_exec_sql = """
  74. SELECT
  75. *
  76. FROM
  77. data_transfer t
  78. WHERE
  79. t.trans_sys_status in (-1,1,2) and t.transfer_status = -1
  80. AND t.read_dir != ''
  81. ORDER BY
  82. t.update_time
  83. LIMIT 1
  84. """
  85. data = trans.execute(query_next_exec_sql)
  86. if type(data) == tuple:
  87. return None
  88. return data[0]
  89. def get_data_by_id(id):
  90. query_exec_sql = f"""
  91. SELECT
  92. t.*,a.field_name,b.batch_name
  93. FROM
  94. data_transfer t INNER JOIN wind_field a on t.field_code = a.field_code
  95. inner join wind_field_batch b on t.id = b.id
  96. WHERE
  97. t.trans_sys_status in (-1,1,2) and t.transfer_status = 2 and t.id = '{id}'
  98. AND t.read_dir != ''
  99. """
  100. data = trans.execute(query_exec_sql)
  101. if type(data) == tuple:
  102. return None
  103. return data[0]
  104. def create_wave_table(table_name, save_db=True):
  105. if save_db:
  106. exec_sql = f"""
  107. CREATE TABLE `{table_name}` (
  108. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  109. `wind_turbine_number` varchar(20) DEFAULT NULL COMMENT '风机编号',
  110. `wind_turbine_name` varchar(20) DEFAULT NULL COMMENT '原始风机编号',
  111. `time_stamp` datetime DEFAULT NULL COMMENT '时间',
  112. `rotational_speed` float DEFAULT NULL COMMENT '转速',
  113. `sampling_frequency` varchar(50) DEFAULT NULL COMMENT '采样频率',
  114. `mesure_point_name` varchar(100) DEFAULT NULL COMMENT '测点名称',
  115. `type` int(11) DEFAULT '-1' COMMENT '-1:不存在 0:角度 1:速度 2:加速度 3:位移,默认 -1',
  116. `mesure_data` longtext COMMENT '测点数据',
  117. PRIMARY KEY (`id`),
  118. KEY `wind_turbine_number` (`wind_turbine_number`),
  119. KEY `time_stamp` (`time_stamp`),
  120. KEY `mesure_point_name` (`mesure_point_name`)
  121. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
  122. """
  123. trans.execute(exec_sql)