from flask import Flask, request, jsonify
import subprocess
import csv
import mysql.connector
from datetime import datetime
import math


mdb_file = 'test.mdb'
table_name = 'AcsData'
output_file = 'output.csv'

# 构建命令，确保提供可执行文件的完整路径
cmd = ['mdb-export', mdb_file, table_name]

# 执行命令并捕获输出
try:
    result = subprocess.run(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, universal_newlines=True, check=True)
    # 将结果写入输出文件
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(result.stdout)
except subprocess.CalledProcessError as e:
    print(f"An error occurred: {e}")
    # 可以写入错误信息到文件或进行其他错误处理
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(e.stderr.decode('utf-8'))  # 将stderr从字节解码为字符串

# 建立MySQL连接
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="HelloWorld123",
    database="water"
)

# 设置MySQL连接的字符集
conn.set_charset_collation('utf8')

# 定义CSV文件和表格名称
csv_file = 'output.csv'
table_name = 'sensor_data'

# 定义数据模型类
class SensorData:
    def __init__(self, sample_time, recv_time, node_name, node_value, node_type, node_unit, device_id, channel_id, project_id):
        self.sample_time = sample_time
        self.recv_time = recv_time
        self.node_name = node_name
        self.node_value = node_value
        self.node_type = node_type
        self.node_unit = node_unit
        self.device_id = device_id
        self.channel_id = channel_id
        self.project_id = project_id

    def __repr__(self):  # 为了方便打印对象列表，定义一个简单的字符串表示方法
        return f"{self.sample_time} - {self.node_name}: {self.node_value}"

try:
    # 打开CSV文件进行读取
    with open(csv_file, mode='r', encoding='utf-8') as file:
        # 创建CSV阅读器
        reader = csv.reader(file)
        # 跳过第一行，因为它是标题行
        next(reader) 
        
        sensor_data_list = []

        # 创建游标对象
        cursor = conn.cursor()
        
        index = 0
        # 逐行插入数据
        for row in reader:
            print(index)
            index += 1
            # 将日期时间字符串转换为MySQL可接受的格式
            SampleTime = datetime.strptime(row[0], '%m/%d/%y %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S')
            RecvTime = datetime.strptime(row[1], '%m/%d/%y %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S')
            
            NodeName = row[2]
            NodeValue = float(row[3])
            if math.isnan(NodeValue):
                NodeValue = 0.0
            
            NodeType = row[4]
            NodeUnit = row[5]
            DeviceID = row[6]
            ChannelID = row[7]
            ProjectID = 0
            sensor_data_list.append(SensorData(SampleTime,RecvTime,NodeName,NodeValue,NodeType,NodeUnit,DeviceID,ChannelID,ProjectID))
        
        # 对列表进行排序，根据sample_time倒序排序
        sensor_data_list.sort(key=lambda x: x.recv_time)


        for sd in sensor_data_list:
            
            # 准备SQL语句，使用参数化查询
            sql = "INSERT INTO {0} (SampleTime, RecvTime, NodeName, NodeValue, NodeType, NodeUnit, DeviceID, ChannelID, ProjectID) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)".format(table_name)
            
            # 执行插入操作，使用元组传递参数确保字符编码正确
            cursor.execute(sql, (sd.sample_time, sd.recv_time, sd.node_name, sd.node_value, sd.node_type, sd.node_unit, sd.device_id, sd.channel_id, sd.project_id))
            print(sd.recv_time)
        # 提交事务
        conn.commit()
            
except mysql.connector.Error as e:
    pass
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()