#!/usr/bin/env python
# -*- coding: UTF-8 -*-

# @Time : 2022/01/05
# @Author : Xianpeng
# @Site :
# @File :
# @Software:
# 根据excel生成模板文件 参考例子bms-ems_cluster1-12021-6-10.xlsx

from os import read
import sys
import getopt
# xlrd库不能打开xlsx,excel空格cell读取为""空字符
import xlrd2
import openpyxl
import re
import json


# 打开excel文件
def open_excel(name, mode='r'):
    data = xlrd2.open_workbook(name)  # 打开xls文件
    return data

# 打开某个sheet表,参数从0开始表示第一个表，返回


def open_sheet(data, index):
    table = data.sheets()[index]  # 打开第一张表
    # 定位到sheet
    sheet1 = data.sheet_by_index(index)
    # 也可以用名字定位
    # sheet2=data.sheet_by_name('222')
    return sheet1

# 得到单元格，入参sheet, row行从0，col列从0


def get_cell(sheet, row, col):
    # print("---get_cell",sheet,row,col)
    return sheet.cell_value(row, col)


def generate_template_service(service_table):
    service_cfg = []
    for key, value in service_table.items():
        if value['read_func'] != "" and value['read_func'] != -1:
            down = {}
            down['direction'] = 0
            down['identifier'] = key
            down['instruction_code'] = value['read_func']
            down['report_period'] = 0
            down['server_period'] = value['period']  # 默认1s采集一次
            down['param'] = []
            one = {}
            one['dynamic'] = 1
            one['raw_data_type'] = "UINT8"
            one['sign_mark'] = 'term_addr'
            one['sign_val'] = 0
            down['param'].append(one)
            one = {}
            one['dynamic'] = 0
            one['raw_data_type'] = "UINT8"
            one['sign_mark'] = 'instruction_code'
            one['sign_val'] = value['read_func']
            down['param'].append(one)
            one = {}
            one['dynamic'] = 0
            one['raw_data_type'] = "UINT16I"
            one['sign_mark'] = 'start_address'
            one['sign_val'] = value['start_addr']
            down['param'].append(one)
            one = {}
            one['dynamic'] = 0
            one['raw_data_type'] = "UINT16I"
            one['sign_mark'] = 'date_length'
            one['sign_val'] = value['reg_cnt']
            down['param'].append(one)
            one = {}
            one['dynamic'] = 1
            one['raw_data_type'] = "UINT8"
            one['sign_mark'] = 'crc'
            one['sign_val'] = 0
            down['param'].append(one)
            service_cfg.append(down)

            up = {}
            up['direction'] = 1
            up['identifier'] = key
            up['instruction_code'] = value['read_func']
            up['report_period'] = 0
            up['server_period'] = value['period']  # 默认1s采集一次
            up['param'] = []
            one = {}
            one['b'] = 0
            one['dynamic'] = 1
            one['k'] = 1
            one['raw_data_type'] = 'UINT8'
            one['sign_mark'] = 'term_addr'
            one['sign_val'] = 0
            up['param'].append(one)
            one = {}
            one['b'] = 0
            one['dynamic'] = 0
            one['k'] = 1
            one['raw_data_type'] = 'UINT8'
            one['sign_mark'] = 'instruction_code'
            one['sign_val'] = 3
            up['param'].append(one)
            one = {}
            one['b'] = 0
            one['dynamic'] = 0
            one['k'] = 1
            one['raw_data_type'] = 'UINT8'
            one['sign_mark'] = 'date_length'
            one['sign_val'] = 0
            up['param'].append(one)

            for k, item in value['addr_tab'].items():
                if k == -1:
                    continue
                if len(item) == 1:
                    # 常规数据
                    one = {}
                    one['b'] = item[0]['b']
                    one['dynamic'] = 1
                    one['k'] = item[0]['k']
                    one['raw_data_type'] = item[0]['data_type']
                    one['sign_mark'] = item[0]['tag_id']
                    one['addr'] = item[0]['addr']
                    up['param'].append(one)
                else:
                    # BIT数据
                    one = {}
                    one['b'] = 0
                    one['dynamic'] = 1
                    one['k'] = 1
                    one['raw_data_type'] = item[0]['data_type']
                    one['sign_mark'] = "addr_" + str(item[0]['addr'])
                    one['bit_fields'] = []
                    for bit_cfg in item:
                        one_bit = {}
                        one_bit['start'] = bit_cfg['bit_start']
                        one_bit['count'] = bit_cfg['bit_count']
                        one_bit['sign_mark'] = bit_cfg['tag_id']
                        one['bit_fields'].append(one_bit)
                    up['param'].append(one)

            one = {}
            one['dynamic'] = 0
            one['b'] = 0
            one['k'] = 1
            one['raw_data_type'] = "UINT16"
            one['sign_mark'] = 'crc'
            one['sign_val'] = 0
            up['param'].append(one)
            service_cfg.append(up)
        elif value['write_func'] != ""  and value['write_func'] != -1:
            down = {}
            down['direction'] = 0
            down['identifier'] = key
            down['instruction_code'] = value['write_func']
            down['report_period'] = 0
            down['server_period'] = 0
            down['param'] = []
            one = {}
            one['dynamic'] = 1
            one['raw_data_type'] = "UINT8"
            one['sign_mark'] = 'term_addr'
            one['sign_val'] = 0
            down['param'].append(one)
            one = {}
            one['dynamic'] = 0
            one['raw_data_type'] = "UINT8"
            one['sign_mark'] = 'instruction_code'
            one['sign_val'] = value['write_func']
            down['param'].append(one)
            one = {}
            one['dynamic'] = 0
            one['raw_data_type'] = "UINT16I"
            one['sign_mark'] = 'start_address'
            one['sign_val'] = value['start_addr']
            down['param'].append(one)
            one = {}
            for k, item in value['addr_tab'].items():
                if k == -1:
                    continue
                one = {}
                one['desc'] = item[0]['tag_id']
                one['dynamic'] = 1
                one['raw_data_type'] = item[0]['data_type']
                one['sign_mark'] = item[0]['tag_id']
                one['addr'] = item[0]['addr']
                down['param'].append(one)

            one = {}
            one['dynamic'] = 1
            one['raw_data_type'] = "UINT16"
            one['sign_mark'] = 'crc'
            one['sign_val'] = 0
            down['param'].append(one)
            service_cfg.append(down)

            up = {}
            up['direction'] = 1
            up['identifier'] = key
            up['instruction_code'] = value['write_func']
            up['report_period'] = 0
            up['server_period'] = 0
            up['param'] = []
            one = {}
            one['b'] = 0
            one['dynamic'] = 1
            one['k'] = 1
            one['raw_data_type'] = 'UINT8'
            one['sign_mark'] = 'term_addr'
            one['sign_val'] = 0
            up['param'].append(one)
            one = {}
            one['b'] = 0
            one['dynamic'] = 0
            one['k'] = 1
            one['raw_data_type'] = 'UINT8'
            one['sign_mark'] = 'instruction_code'
            one['sign_val'] = 0
            up['param'].append(one)
            one = {}
            one['b'] = 0
            one['dynamic'] = 0
            one['k'] = 1
            one['raw_data_type'] = 'UINT8'
            one['sign_mark'] = 'date_length'
            one['sign_val'] = 0
            up['param'].append(one)
            one = {}
            one['dynamic'] = 0
            one['b'] = 0
            one['k'] = 1
            one['raw_data_type'] = "UINT16"
            one['sign_mark'] = 'crc'
            one['sign_val'] = 0
            up['param'].append(one)
            service_cfg.append(up)

    return service_cfg


def generate_template_cfg(service_table, template_id):
    one = {}
    one['communication_timeout'] = 0
    one['event_cfg'] = []  # TODO event generate
    one['logout_times'] = 0
    one['mac_info'] = []
    one['offline_times'] = 7200
    one['protocol'] = "GW_PROTC_MODBUS"
    one['report_period'] = 0
    one['service_cfg'] = generate_template_service(service_table)
    one['tag_cfg'] = []
    one['template_id'] = template_id
    one['template_name'] = "test"
    one['use_parser_url'] = 0

    return one


def gen_service_obj(service_table):
    service_cfg = []
    for key, value in service_table.items():
        if value['read_func'] != "" and value['read_func'] != -1:
            obj = {}
            obj['identifier'] = key
            obj['outputData'] = []
            obj['required'] = False

            for k, items in value['addr_tab'].items():
                for item in items:
                    if item['no_obj'] == 1:
                        continue
                    # 常规数据
                    one = {}
                    one['dataType'] = {}
                    one['dataType']['specs'] = {}
                    one['dataType']['specs']['max'] = "0"
                    one['dataType']['specs']['min'] = "0"
                    one['dataType']['specs']['unit'] = ""
                    one['dataType']['specs']['min'] = "0"
                    one['dataType']['type'] = "int"
                    one['desc'] = item['exp']
                    one['identifier'] = item['tag_id']
                    one['required'] = False
                    one['calc'] = item['calc']
                    one['name'] = item['name']
                    obj['outputData'].append(one)
            service_cfg.append(obj)
        elif value['write_func'] != "" and value['write_func'] != -1:
            obj = {}
            obj['identifier'] = key
            obj['inputData'] = []
            obj['required'] = False

            for k, items in value['addr_tab'].items():
                for item in items:
                    # 常规数据
                    one = {}
                    one['dataType'] = {}
                    one['dataType']['specs'] = {}
                    one['dataType']['type'] = "int"
                    one['desc'] = item['tag_id']
                    one['identifier'] = item['tag_id']
                    one['required'] = False
                    one['name'] = item['name']
                    one['calc'] = item['calc']
                    obj['inputData'].append(one)
            service_cfg.append(obj)
    return service_cfg


def gen_peroperty_obj(service_table):
    perp_cfg = []
    for key, value in service_table.items():
        if value['read_func'] != "":
            for k, items in value['addr_tab'].items():
                for item in items:
                    if item['no_obj'] == 1:
                        continue
                    # 常规数据
                    one = {}
                    one['dataType'] = {}
                    one['dataType']['specs'] = {}
                    one['dataType']['specs']['max'] = "0"
                    one['dataType']['specs']['min'] = "0"
                    one['dataType']['specs']['unit'] = ""
                    one['dataType']['specs']['min'] = "0"
                    one['dataType']['type'] = "float"
                    one['desc'] = item['exp']
                    one['identifier'] = item['tag_id']
                    one['required'] = False
                    one['calc'] = item['calc']
                    perp_cfg.append(one)

    return perp_cfg


def generate_object(service_table, template_id):
    one = {}
    one['profile'] = {}
    one['profile']['productKey'] = template_id
    one['services'] = gen_service_obj(service_table)
    # one['properties'] = gen_peroperty_obj(service_table)
    return one


def gen_template(file, template_id):
    data = open_excel(file)
    sheet1 = open_sheet(data, 0)
    rows = sheet1.nrows
    cols = sheet1.ncols
    service_table = {}
    last_service = ""
    last_read_func = -1
    last_write_func = -1

    for rowindex in range(1, rows):
        service = get_cell(sheet1, rowindex, 0)
        if service == "":
            service = last_service
        if service == "":
            print("Error: service should not empty")
        else:
            last_service = service

        if get_cell(sheet1, rowindex, 1) != "":
            read_func = int(get_cell(sheet1, rowindex, 1))
        else:
            read_func = last_read_func
        last_read_func = read_func

        if get_cell(sheet1, rowindex, 2) != "":
            write_func = int(get_cell(sheet1, rowindex, 2))
        else:
            write_func = last_write_func
        last_write_func = write_func

        if get_cell(sheet1, rowindex, 3) != "":
            print(get_cell(sheet1, rowindex, 3), type(get_cell(sheet1, rowindex, 3)))
            period = float(get_cell(sheet1, rowindex, 3))
            print(period)
        else:
            period = 0

        name = get_cell(sheet1, rowindex, 4)

        tag_id = get_cell(sheet1, rowindex, 5)
        if tag_id == "":
            tag_id = "FREE" + str(rowindex)
            no_obj = 1
        else:
            no_obj = 0

        addr = get_cell(sheet1, rowindex, 6)
        if addr == "":
            addr = -1
        else:
            if isinstance(addr, str) and ("0x" in addr or "0X" in addr):
                addr = int(addr, 16)
            else:
                print(addr)
                addr = int(addr)

        data_type = get_cell(sheet1, rowindex, 7)

        if get_cell(sheet1, rowindex, 8) != "":
            bit_start = int(get_cell(sheet1, rowindex, 8))
        else:
            bit_start = -1

        if get_cell(sheet1, rowindex, 9) != "":
            bit_count = int(get_cell(sheet1, rowindex, 9))
        else:
            bit_count = -1

        if get_cell(sheet1, rowindex, 10) != "":
            k = get_cell(sheet1, rowindex, 10)
            if k == 1:
                k = 1
        else:
            k = 1

        if get_cell(sheet1, rowindex, 11) != "":
            b = get_cell(sheet1, rowindex, 11)
            if b == 0:
                b = 0
        else:
            b = 0

        exp = get_cell(sheet1, rowindex, 12)
        if (exp == ""):
            calc = 0
            exp = tag_id
        else:
            calc = 1
        if cols >= 15:
            length = get_cell(sheet1, rowindex, 14)
            if (length == ""):
                length = -1
            else:
                length = int(length)
        else:
            length = -1

        if service not in service_table:
            service_table[service] = {}
            service_table[service]['addr_tab'] = {}

        one = {}
        one['name'] = name
        one['tag_id'] = tag_id
        one['read_func'] = read_func
        one['addr'] = addr
        one['data_type'] = data_type
        one['bit_start'] = bit_start
        one['bit_count'] = bit_count
        one['k'] = k
        one['b'] = b
        one['exp'] = exp
        one['write_func'] = write_func
        one['calc'] = calc
        one['period'] = period
        one['no_obj'] = no_obj
        one['length'] = length

        if addr not in service_table[service]['addr_tab']:
            service_table[service]['addr_tab'][addr] = []

        service_table[service]['addr_tab'][addr].append(one)

    for key, value in service_table.items():
        min_addr = 65536
        max_addr = -1
        read_func = 0
        write_func = 0
        period = 0
        length = -1
        data_type = "UINT16I"
        for k, item in value['addr_tab'].items():
            if read_func == 0:
                read_func = item[0]['read_func']
            if write_func == 0:
                write_func = item[0]['write_func']
            if period == 0:
                period = item[0]['period']
            if item[0]['length'] != -1:
                length = item[0]['length']
            if item[0]['addr'] == -1:continue
            if item[0]['addr'] < min_addr:
                min_addr = item[0]['addr']
            if item[0]['addr'] > max_addr:
                max_addr = item[0]['addr']
                data_type = item[0]['data_type']

        if "INT32" in data_type:
            max_addr = max_addr + 1
        if "INT64" in data_type:
            max_addr = max_addr + 3
        print(key, min_addr, max_addr, period)
        value['start_addr'] = min_addr
        if length == -1:
            value['reg_cnt'] = max_addr - min_addr + 1
        else:
            value['reg_cnt'] = length
        value['read_func'] = read_func
        value['write_func'] = write_func
        value['period'] = period

    temp = generate_template_cfg(service_table, template_id)
    obj = generate_object(service_table, template_id)

    return temp, obj

    return 0


def main(argv):
    inputfile = ''
    template_id = ''
    mode = ""
    try:
        opts, args = getopt.getopt(
            argv, "hi:t:m:", ["ifile=", "ttemp=", "mmode="])
    except getopt.GetoptError:
        print("{} -i <inputfile> -t <template id>".format(sys.argv[0]))
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print("{} -i <inputfile> -t <template id>".format(sys.argv[0]))
            sys.exit()
        elif opt in ("-i", "--ifile"):
            inputfile = arg
        elif opt in ("-t", "--ttemmplate_id"):
            template_id = arg
        elif opt in ("-m", "--mode"):
            mode = arg

    temps = {}
    temps['template_cfg'] = []

    objs = {}
    objs['object_model_cfg'] = []

    if mode == "batch":
        with open(inputfile) as f:
            result = json.load(f)
            for item in result['templates']:
                print("====", item['file'], item['template_id'])
                temp, obj = gen_template(item['file'], item['template_id'])
                objs['object_model_cfg'].append(obj)
                temps['template_cfg'].append(temp)
    else:
        temp, obj = gen_template(inputfile, template_id)
        objs['object_model_cfg'].append(obj)
        temps['template_cfg'].append(temp)

    with open("templates_cfg.json", 'w') as f:
        f.write(json.dumps(temps))
    with open("object_model_cfg.json", 'w') as f:
        f.write(json.dumps(objs))


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