#!/usr/bin/python3
# -*- coding: UTF-8 -*-

import sqlite3
import pandas as pd
import sys
import getopt
from datetime import datetime


def read_db(db_file):
    conn = sqlite3.connect(db_file)
    c = conn.cursor()
    print("Opened database successfully")

    cursor = c.execute("SELECT *  from data")
    rows = cursor.fetchall()  # 获取全部数据
    conn.close()
    return rows


def analysis_data(rows):
    data = {}
    last_time = None
    for row in rows:
        time = row[2] + 8 * 3600
        id = row[1]
        raw_data = row[3]
        if last_time is not None and abs(time - last_time) < 3:
            time = last_time
        last_time = time
        if time not in data:
            data[time] = []
        t = {"id": id, "data": raw_data}
        data[time].append(t)

    all_data = []
    for key, val in data.items():
        item = {}
        item['Time'] = key
        for one in val:
            name = "s%d".format(one['id'])
            bin = one['data'][8:]
            v = 1
            for i in range(0, len(bin), 8):
                fn = bin[i]
                if fn <= 100:
                    vid = (fn - 1) * 3 + 1
                    vid_val = ((bin[i + 1] << 8) | bin[i + 2])
                    if (vid_val & 0x8000) != 0:
                        identifier = "s{}_v{}".format(one['id'], vid)
                        item[identifier] = round((vid_val & 0x7FFF) * 0.001, 3)
                    vid = (fn - 1) * 3 + 2
                    vid_val = ((bin[i + 3] << 8) | bin[i + 4])
                    if (vid_val & 0x8000) != 0:
                        identifier = "s{}_v{}".format(one['id'], vid)
                        item[identifier] = round((vid_val & 0x7FFF) * 0.001, 3)
                    vid = (fn - 1) * 3 + 3
                    vid_val = ((bin[i + 5] << 8) | bin[i + 6])
                    if (vid_val & 0x8000) != 0:
                        identifier = "s{}_v{}".format(one['id'], vid)
                        item[identifier] = round((vid_val & 0x7FFF) * 0.001, 3)
                elif fn >= 102 and fn <= 201:
                    tid = (fn - 102) * 3 + 1
                    tid_val = ((bin[i + 1] << 8) | bin[i + 2])
                    if (tid_val & 0x8000) != 0:
                        identifier = "s{}_t{}".format(one['id'], tid)
                        item[identifier] = round((tid_val & 0x7FFF) * 0.001, 3)
                    tid = (fn - 102) * 3 + 2
                    tid_val = ((bin[i + 3] << 8) | bin[i + 4])
                    if (tid_val & 0x8000) != 0:
                        identifier = "s{}_t{}".format(one['id'], tid)
                        item[identifier] = round((tid_val & 0x7FFF) * 0.001, 3)
                    tid = (fn - 102) * 3 + 3
                    tid_val = ((bin[i + 5] << 8) | bin[i + 6])
                    if (tid_val & 0x8000) != 0:
                        identifier = "s{}_t{}".format(one['id'], tid)
                        item[identifier] = round((tid_val & 0x7FFF) * 0.001, 3)
        all_data.append(item)

    df = pd.DataFrame(all_data)
    df = df.sort_values(by="Time", ascending=False)
    df['Time'] = pd.to_datetime(df['Time'], unit='s', origin=pd.Timestamp('1970-01-01'))
    df['Time'] = df['Time'].apply(lambda x: datetime.strftime(x, '%Y/%m/%d %H:%M:%S'))
    return df


def main(argv):
    inputfile = ''
    outputfile = ''
    try:
        opts, args = getopt.getopt(argv, "hi:o:", ["ifile=", "ofile="])
    except getopt.GetoptError:
        print("{} -i <inputfile> -o <outputfile>".format(sys.argv[0]))
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print("{} -i <inputfile> -o <outputfile>".format(sys.argv[0]))
            sys.exit()
        elif opt in ("-i", "--ifile"):
            inputfile = arg
        elif opt in ("-o", "--ofile"):
            outputfile = arg

    r = read_db(inputfile)
    df = analysis_data(r)
    df.to_csv(outputfile, index=0)
    print("write file done")


if __name__ == "__main__":
    main(sys.argv[1:])