jxgzcd/query_device_filter.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
查询td_d_log_result表，筛选符合条件的device_id和channel_no组合
筛选条件：
- task_id = 2
- 对于每个device_id和channel_no的组合，取最新的10条记录（按channel_no的generate_time）
- 如果这10个value值中有超过3个值大于20，则记录该(device_id, channel_no)组合
"""

import pymysql
import sys
import os
import zlib
import json
from collections import defaultdict
from datetime import datetime

# 设置控制台输出编码（Windows需要，Linux默认UTF-8）
if sys.platform == 'win32':
    try:
        os.system('chcp 65001 >nul 2>&1')
    except:
        pass
else:
    # Linux环境下设置UTF-8编码
    import locale
    try:
        locale.setlocale(locale.LC_ALL, 'en_US.UTF-8')
    except:
        try:
            locale.setlocale(locale.LC_ALL, 'C.UTF-8')
        except:
            pass

def parse_file_data(file_name, use_fdfs=False, fdfs_client=None):
    """
    解析振动数据文件
    
    参数:
        file_name: 文件路径（FDFS路径或本地路径）
        use_fdfs: 是否使用FDFS客户端（如果为True，需要提供fdfs_client）
        fdfs_client: FDFS客户端对象（可选）
    
    返回:
        解析后的数据（字典或列表）
    """
    try:
        # 1.1 从 FDFS 下载文件数据，先把文件名转成 UTF-8 编码的二进制串
        if use_fdfs and fdfs_client:
            # 使用FDFS客户端下载
            data = fdfs_client.download_bytes(bytes(file_name, encoding="utf8"))
        else:
            # 如果没有FDFS客户端，尝试从本地文件系统读取
            # 将FDFS路径转换为本地路径
            if file_name.startswith('group1/M00/'):
                local_path = file_name.replace('group1/M00/', '/home/soft/data/fdfs/storage/data/', 1)
            elif file_name.startswith('group1/M00'):
                local_path = file_name.replace('group1/M00', '/home/soft/data/fdfs/storage/data', 1)
            else:
                local_path = file_name
            
            # 从本地文件系统读取
            with open(local_path, 'rb') as f:
                data = f.read()
        
        # 1.2 检查下载的二进制数据开头是否是 {（JSON 格式的标志）
        if data[0:1] == b'{':
            # 如果是 JSON 二进制串，直接解码成字符串
            data = data.decode()
        else:
            # 如果不是 JSON 开头，说明数据是压缩的，先解压
            ss = zlib.decompress(data).decode()
            # 修正格式：单引号改双引号（JSON 要求双引号）、nan 改成 0（避免 JSON 解析报错）
            ss = ss.replace('\'', '"').replace("nan", "0")
            # 把修正后的字符串解析成 JSON 格式的 Python 数据（字典/列表）
            data = json.loads(ss)
        
        return data
    except FileNotFoundError as e:
        # 文件未找到，静默返回None
        return None
    except zlib.error as e:
        print(f"      警告: 解压缩失败 {file_name}: {e}")
        return None
    except json.JSONDecodeError as e:
        print(f"      警告: JSON解析失败 {file_name}: {e}")
        return None
    except Exception as e:
        print(f"      警告: 解析文件失败 {file_name}: {e}")
        return None

def calculate_asymmetry(data):
    """
    计算不对称性
    从data中提取raw_y数组，然后计算不对称性
    返回 (abs_正, abs_负)
    """
    try:
        # 如果data是字典，提取raw_y字段
        if isinstance(data, dict):
            if 'raw_y' not in data:
                return None, None
            values_list = data['raw_y']
        elif isinstance(data, list):
            # 如果是列表，直接使用（兼容旧代码）
            values_list = data
        else:
            return None, None
        
        # 确保values_list是列表
        if not isinstance(values_list, list):
            return None, None
        
        # 转换为数值列表
        values = []
        for v in values_list:
            try:
                val = float(v)
                values.append(val)
            except (ValueError, TypeError):
                continue
        
        if len(values) == 0:
            return None, None
        
        # 分离正半轴和负半轴的值
        positive_values = [v for v in values if v > 0]
        negative_values = [abs(v) for v in values if v < 0]  # 负半轴取绝对值
        
        # 计算和
        sum_positive = sum(positive_values)
        sum_negative = sum(negative_values)
        
        # 计算不对称性
        if sum_positive > 0:
            abs_positive = abs((sum_positive - sum_negative) / sum_positive)
        else:
            abs_positive = None
        
        if sum_negative > 0:
            abs_negative = abs((sum_positive - sum_negative) / sum_negative)
        else:
            abs_negative = None
        
        return abs_positive, abs_negative
    except Exception as e:
        return None, None

def query_filtered_devices(fdfs_client=None):
    """
    查询符合条件的device_id和channel_no组合
    
    参数:
        fdfs_client: FDFS客户端对象（可选，用于下载文件）
    """
    # 数据库连接配置
    db_config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'prod',
        'password': 'hmdmxjIvfIjIoflL',
        'database': 'iot',
        'charset': 'utf8mb4',
        'connect_timeout': 10
    }
    
    connection = None
    try:
        print("正在连接到数据库...")
        connection = pymysql.connect(**db_config)
        print("[成功] 数据库连接成功！\n")
        
        with connection.cursor() as cursor:
            # 查询所有唯一的device_id和channel_no组合，且task_id=2
            print("查询所有device_id和channel_no的组合...")
            query_unique = """
                SELECT DISTINCT device_id, channel_no 
                FROM td_d_log_result 
                WHERE task_id = '2'
            """
            cursor.execute(query_unique)
            device_channel_pairs = cursor.fetchall()
            print(f"找到 {len(device_channel_pairs)} 个device_id和channel_no的组合\n")
            
            # 存储符合条件的(device_id, channel_no)组合
            filtered_channel_list = []
            
            # 对每个device_id和channel_no组合进行处理
            for idx, (device_id, channel_no) in enumerate(device_channel_pairs, 1):
                # 查询该组合的最新10条记录（按generate_time降序，按channel_no）
                query_latest = """
                    SELECT value, generate_time
                    FROM td_d_log_result
                    WHERE device_id = %s 
                      AND channel_no = %s 
                      AND task_id = '2'
                    ORDER BY generate_time DESC
                    LIMIT 10
                """
                cursor.execute(query_latest, (device_id, channel_no))
                records = cursor.fetchall()
                
                if len(records) < 10:
                    # 如果记录数少于10条，跳过
                    continue
                
                # 统计value值大于20的个数
                count_over_20 = 0
                values = []
                
                for value_str, generate_time in records:
                    try:
                        # value字段是varchar类型，需要转换为float
                        value = float(value_str) if value_str else 0
                        values.append(value)
                        if value > 20:
                            count_over_20 += 1
                    except (ValueError, TypeError):
                        # 如果转换失败，跳过该值
                        continue
                
                # 如果超过3个值大于20，记录该(device_id, channel_no)组合
                if count_over_20 > 3:
                    filtered_channel_list.append((device_id, channel_no))
                    print(f"[{idx}] device_id: {device_id}, channel_no: {channel_no}, "
                          f"超过20的值数量: {count_over_20}/10, 最新10个值: {values}")
            
            # 查询device_name
            print("\n查询设备名称...")
            device_info_map = {}
            if filtered_channel_list:
                # 获取所有唯一的device_id
                unique_device_ids = list(set([device_id for device_id, channel_no in filtered_channel_list]))
                
                # 批量查询device_name
                placeholders = ','.join(['%s'] * len(unique_device_ids))
                query_device_name = f"""
                    SELECT device_id, device_name 
                    FROM td_d_device_info 
                    WHERE device_id IN ({placeholders})
                """
                cursor.execute(query_device_name, tuple(unique_device_ids))
                device_info_results = cursor.fetchall()
                
                # 构建device_id到device_name的映射
                for device_id, device_name in device_info_results:
                    device_info_map[device_id] = device_name if device_name else '(未设置设备名称)'
            
            # 查询channel_name
            print("查询通道名称...")
            channel_info_map = {}
            if filtered_channel_list:
                # 批量查询channel_name（使用device_id和channel_no组合）
                query_channel_name = """
                    SELECT device_id, channel_no, channel_name 
                    FROM td_d_device_channel 
                    WHERE (device_id, channel_no) IN (
                """
                # 构建IN子句的占位符
                placeholders = ','.join(['(%s, %s)'] * len(filtered_channel_list))
                query_channel_name += placeholders + ")"
                
                # 准备参数：将(device_id, channel_no)元组展开
                params = []
                for device_id, channel_no in filtered_channel_list:
                    params.extend([device_id, int(channel_no)])  # channel_no需要转换为int
                
                cursor.execute(query_channel_name, tuple(params))
                channel_info_results = cursor.fetchall()
                
                # 构建(device_id, channel_no)到channel_name的映射
                for device_id, channel_no, channel_name in channel_info_results:
                    channel_info_map[(device_id, str(channel_no))] = channel_name if channel_name else '(未设置通道名称)'
            
            # 查询每个(device_id, channel_no)组合最近10条的file_name
            print("查询每个channel_no最近10条文件记录...")
            channel_file_map = {}
            if filtered_channel_list:
                for device_id, channel_no in filtered_channel_list:
                    # 查询该(device_id, channel_no)组合最近10条记录的file_name（按generate_time降序，task_id=2）
                    # 同时关联查询td_d_log_result表的generate_time
                    query_file_name = """
                        SELECT lf.file_name, lf.generate_time as file_generate_time, lr.generate_time as result_generate_time
                        FROM td_d_log_file lf
                        LEFT JOIN td_d_log_result lr ON (
                            lf.device_id = lr.device_id 
                            AND lf.channel_no = lr.channel_no 
                            AND lf.task_id = lr.task_id 
                            AND lf.generate_time = lr.generate_time
                        )
                        WHERE lf.device_id = %s 
                          AND lf.channel_no = %s
                          AND lf.task_id = '2'
                        ORDER BY lf.generate_time DESC
                        LIMIT 10
                    """
                    cursor.execute(query_file_name, (device_id, channel_no))
                    file_results = cursor.fetchall()
                    
                    # 提取file_name列表，同时解析文件计算不对称性（使用FDFS路径）
                    file_info_list = []
                    for file_name, file_generate_time, result_generate_time in file_results:
                        if file_name:
                            # 解析文件并计算不对称性（使用原始FDFS路径，如果提供了fdfs_client则使用FDFS，否则从本地读取）
                            data = parse_file_data(file_name, use_fdfs=(fdfs_client is not None), fdfs_client=fdfs_client)
                            abs_positive, abs_negative = calculate_asymmetry(data) if data is not None else (None, None)
                            
                            file_info_list.append({
                                'file_name': file_name,  # 保持原始FDFS路径
                                'file_generate_time': file_generate_time,
                                'result_generate_time': result_generate_time,
                                'abs_positive': abs_positive,
                                'abs_negative': abs_negative
                            })
                    channel_file_map[(device_id, channel_no)] = file_info_list
            
            # 生成输出文件（包含时间戳）
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            output_filename = f"query_result_{timestamp}.txt"
            output_filepath = os.path.join(os.getcwd(), output_filename)
            
            # 打印所有符合条件的device_id、device_name、channel_no、channel_name和最近10条file_name
            print("\n" + "=" * 60)
            print("符合条件的device_id和channel_no组合列表：")
            print("=" * 60)
            
            # 准备输出内容（同时用于打印和写入文件）
            output_lines = []
            output_lines.append("=" * 60)
            output_lines.append("符合条件的device_id和channel_no组合列表：")
            output_lines.append("=" * 60)
            output_lines.append("")
            
            if filtered_channel_list:
                # 按device_id和channel_no排序后打印
                sorted_channels = sorted(filtered_channel_list, key=lambda x: (x[0], int(x[1]) if x[1].isdigit() else x[1]))
                displayed_count = 0  # 记录实际显示的记录数
                for device_id, channel_no in sorted_channels:
                    device_name = device_info_map.get(device_id, '(未找到设备名称)')
                    channel_name = channel_info_map.get((device_id, channel_no), '(未找到通道名称)')
                    
                    # 过滤掉"未找到通道名称"的记录
                    if channel_name == '(未找到通道名称)':
                        continue
                    
                    displayed_count += 1  # 增加显示计数
                    file_names = channel_file_map.get((device_id, channel_no), [])
                    
                    device_info_str = f"\n设备ID: {device_id}"
                    device_name_str = f"设备名称: {device_name}"
                    channel_no_str = f"通道号: {channel_no}"
                    channel_name_str = f"通道名称: {channel_name}"
                    file_count_str = f"最近10条文件记录({len(file_names)}条):"
                    
                    print(device_info_str)
                    print(device_name_str)
                    print(channel_no_str)
                    print(channel_name_str)
                    print(file_count_str)
                    
                    output_lines.append(device_info_str)
                    output_lines.append(device_name_str)
                    output_lines.append(channel_no_str)
                    output_lines.append(channel_name_str)
                    output_lines.append(file_count_str)
                    
                    if file_names:
                        for idx, file_info in enumerate(file_names, 1):
                            file_name = file_info['file_name']
                            file_generate_time = file_info.get('file_generate_time')
                            result_generate_time = file_info.get('result_generate_time')
                            abs_positive = file_info.get('abs_positive')
                            abs_negative = file_info.get('abs_negative')
                            
                            # 格式化输出
                            file_time_str = str(file_generate_time) if file_generate_time else "N/A"
                            result_time_str = str(result_generate_time) if result_generate_time else "N/A"
                            abs_positive_str = f"{abs_positive:.6f}" if abs_positive is not None else "N/A"
                            abs_negative_str = f"{abs_negative:.6f}" if abs_negative is not None else "N/A"
                            
                            file_line = f"  {idx}. {file_name}"
                            time_line = f"      generate_time(td_d_log_result): {result_time_str}"
                            abs_line = f"      abs正: {abs_positive_str}, abs负: {abs_negative_str}"
                            
                            print(file_line)
                            print(time_line)
                            print(abs_line)
                            
                            output_lines.append(file_line)
                            output_lines.append(time_line)
                            output_lines.append(abs_line)
                    else:
                        no_file_str = "  (无文件记录)"
                        print(no_file_str)
                        output_lines.append(no_file_str)
                    
                    separator = "-" * 60
                    print(separator)
                    output_lines.append(separator)
                
                total_str = f"\n总计: {displayed_count} 个(device_id, channel_no)组合（已过滤掉未找到通道名称的记录）"
                print(total_str)
                output_lines.append(total_str)
            else:
                no_result_str = "未找到符合条件的(device_id, channel_no)组合"
                print(no_result_str)
                output_lines.append(no_result_str)
            
            # 将结果写入文件
            try:
                with open(output_filepath, 'w', encoding='utf-8') as f:
                    f.write('\n'.join(output_lines))
                    f.write('\n')
                print(f"\n[成功] 查询结果已保存到文件: {output_filepath}")
            except Exception as e:
                print(f"\n[警告] 保存结果到文件失败: {e}")
            
            return filtered_channel_list
        
    except pymysql.Error as e:
        error_code, error_msg = e.args
        print("[失败] 数据库操作失败！")
        print(f"[失败] 错误代码: {error_code}")
        print(f"[失败] 错误信息: {error_msg}")
        return None
        
    except Exception as e:
        print("[失败] 发生未知错误！")
        print(f"[失败] 错误信息: {e}")
        import traceback
        traceback.print_exc()
        return None
        
    finally:
        # 关闭数据库连接
        if connection:
            connection.close()
            print("\n[成功] 数据库连接已关闭")

if __name__ == "__main__":
    print("=" * 60)
    print("设备和通道筛选工具")
    print("=" * 60)
    print("筛选条件：")
    print("- task_id = 2")
    print("- 每个device_id下channel_no的最新10条记录（按channel_no的generate_time）")
    print("- 这10个value值中有超过3个值大于20")
    print("=" * 60 + "\n")
    
    result = query_filtered_devices()
    
    if result is not None:
        sys.exit(0)
    else:
        sys.exit(1)