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

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


"""
LUA脚本使用方法，替换global_point_table内容，并编辑global_period_service_tab
require "bit"

local global_point_table = {
	tags_cfg = 
	{
		{
			identifier = "collect_data",
			regs = 
			{
				{tag = "basStatus", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,0),1)", k = 1, b = 0},
				{tag = "jdanxStatus", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,1),1)", k = 1, b = 0},
				{tag = "fjRunStatus", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,2),1)", k = 1, b = 0},
				{tag = "fjLRunStatus", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,3),1)", k = 1, b = 0},
				{tag = "fjStopStatus", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,4),1)", k = 1, b = 0},
				{tag = "zdPreAlarm", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,5),1)", k = 1, b = 0},
				{tag = "rqzhStatus", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,6),1)", k = 1, b = 0},
				{tag = "qzwAlarm", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,7),1)", k = 1, b = 0},
				{tag = "hzwAlarm", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,8),1)", k = 1, b = 0},
				{tag = "rzAAlarm", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,9),1)", k = 1, b = 0},
				{tag = "rzBAlarm", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,10),1)", k = 1, b = 0},
				{tag = "rzCAlarm", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,11),1)", k = 1, b = 0},
				{tag = "dzgzAlarm", reg = 100, type = ">S", desc = "bit.band(bit.rshift(val,12),1)", k = 1, b = 0},
				{tag = "hzqcPro", reg = 101, type = ">S", desc = "bit.band(bit.rshift(val,0),1)", k = 1, b = 0},
				{tag = "AI", reg = 102, type = ">S", k = 0, b = 0},
				{tag = "BI", reg = 103, type = ">S", k = 0, b = 0},
				{tag = "CI", reg = 104, type = ">S", k = 0, b = 0},
				{tag = "AU", reg = 105, type = ">S", k = 0, b = 0},
				{tag = "BU", reg = 106, type = ">S", k = 0, b = 0},
				{tag = "CU", reg = 107, type = ">S", k = 0, b = 0},
				{tag = "qzw", reg = 108, type = ">S", k = 0, b = 0},
				{tag = "hzw", reg = 109, type = ">S", k = 0, b = 0},
				{tag = "rzwA", reg = 110, type = ">S", k = 0, b = 0},
				{tag = "rzwB", reg = 111, type = ">S", k = 0, b = 0},
				{tag = "rzwC", reg = 112, type = ">S", k = 0, b = 0},
				{tag = "zdX", reg = 113, type = ">S", k = 0, b = 0},
				{tag = "zdY", reg = 114, type = ">S", k = 0, b = 0},
				{tag = "communStatus", reg = 115, type = ">S", k = 1, b = 0},
				{tag = "PF", reg = 116, type = ">S", k = 0, b = 0},
				{tag = "activePower", reg = 117, type = ">S", k = 0, b = 0},
				{tag = "totalKwh", reg = 118, type = ">S", k = 0, b = 0}
			}
		}
	}
}

----------------------[MODBUS read data]--------------------------
local function read_multi_register(obj, start, reg_cnt, cmd)
    return cmd or 0x03, start, reg_cnt
end

----------------------[控制服务]--------------------------
local global_period_service_tab = {
    {
        interval = 60,
        identifier = "collect_data",
        param = {},
        create_bin_func = read_multi_register,
        create_bin_args = {100, 0x13, 0x03}
    }

}

local function getServiceInfo(id)
    for i, v in pairs(global_period_service_tab) do
        if v.identifier == id then return v end
    end
    return nil
end

function protocol_encode(json_str, len)
    local err_code = 0
    -- 取出入参--
    local obj = cjson.decode(json_str)
    local id = obj.identifier
    local server_period = obj.server_period
    local dev_sn = obj.sn
    local addr = tonumber(obj.term_addr, 16)
    -- 编码--

    local raw_data = nil
    for i, v in pairs(global_period_service_tab) do
        if v.identifier == id then
            local call_func = v.create_bin_func
            local func, start, len, data_len, data = call_func(obj,
                                                               v.create_bin_args[1],
                                                               v.create_bin_args[2],
                                                               v.create_bin_args[3])
            if func == nil then return -1, nil, 0 end
            raw_data = bpack("C", tonumber(obj.term_addr, 16))
            if not data then
                raw_data = raw_data .. bpack("C>S>S", func, start, len)
            else
                raw_data = raw_data ..
                               bpack("C>S>SC", func, start, len, data_len)
                for i = 1, #data do
                    raw_data = raw_data .. bpack("c", data:byte(i))
                end
            end
            raw_data = raw_data ..
                           bpack(">S", dyutils.CRC16(raw_data, #raw_data))

            local t_str = ''
            for i = 1, #raw_data do
                t_str = t_str ..
                            (string.format("%02X", string.byte(
                                               string.sub(raw_data, i, i))))
            end

            print(t_str, #t_str)
            log.info("DTSD1352", 'encode result ', err_code, t_str, #t_str)
            return err_code, t_str, #t_str
        end
    end
    return -1, nil, 0
end

function protocol_decode(input_str, len)
    local err_code = 0
    -- 取出入参--
    local obj = {}
    local input_obj = cjson.decode(input_str)
    local bin_str = input_obj.raw_data
    local id = input_obj.identifier
    -- 入参中的raw_data的二进制转换--
    local t_str = ''
    for i = 1, #bin_str, 2 do
        t_str = t_str .. string.char(tonumber(string.sub(bin_str, i, i + 1), 16))
    end

    raw_data = t_str
    local crc = dyutils.CRC16(raw_data, #raw_data - 2)
    _, crc_pack = bunpack(string.sub(raw_data, #raw_data - 1), ">S")

    if (crc ~= crc_pack) then
        print(string.format("Data crc check failed cal:%04X packet:%04X", crc, crc_pack))
        return nil
    end

    local _, _, func = bunpack(raw_data, "CC")
    local data, data_len = nil, 0
    if func == 0x05 then
        data_len = #raw_data - 5 - 1
        data = string.sub(raw_data, 5, -3)
    elseif func == 0x01 or func == 0x02 or func == 0x03 or func == 0x04 then
        data_len = #raw_data - 4 - 1
        data = string.sub(raw_data, 4, -3)
    elseif func == 0x10 then
        data_len = #raw_data - 4 - 1
        data = string.sub(raw_data, 4, -3)
    else
        log.info(string.format("unknow function code 02X", func))
        return -1
    end

    local v = getServiceInfo(id)
    if v == nil then
        log.info(string.format("unknow service identifier %s", id))
        return -1
    end
    local json_obj = {}
    for i = 1, #global_point_table["tags_cfg"] do
        -- 判断起始地址
        if global_point_table["tags_cfg"][i].identifier == id then
            local start_addr = v.create_bin_args[1]
            local end_addr = start_addr + data_len / 2 - 1
            for j = 1, #global_point_table["tags_cfg"][i].regs do
                if global_point_table["tags_cfg"][i].regs[j]["reg"] >= start_addr and global_point_table["tags_cfg"][i].regs[j]["reg"] <= end_addr then
                    local offset = (global_point_table["tags_cfg"][i].regs[j]["reg"] - start_addr) * 2 + 1
                    local tmp_data = string.sub(data, offset, -1)
                    _, val = bunpack(tmp_data, global_point_table["tags_cfg"][i].regs[j]["type"])
                    if global_point_table["tags_cfg"][i].regs[j]["desc"] and #global_point_table["tags_cfg"][i].regs[j]["desc"] > 0 then
                        local des = loadstring("return "..global_point_table["tags_cfg"][i].regs[j]["desc"])
                        local ret, v = pcall(des)
                        if ret then
                            val = v
                        else
                            print("pcall error ".. v)
                        end
                    end
                    json_obj[global_point_table["tags_cfg"][i].regs[j]["tag"]] = val * global_point_table["tags_cfg"][i].regs[j]["k"] + global_point_table["tags_cfg"][i].regs[j]["b"]
                end
            end
            json_obj.identifier = id -- 返回调用标识
            local json_str = cjson.encode(json_obj)
            return err_code, json_str, #json_str
        end
    end
    return nil

end
 """

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

# 得到sheet表数目
def get_sheets(data):
    sheetnumbers = data.nsheets
    # 得到sheet名字
    sheetnames = data.sheet_names()
    print(sheetnames)
    return sheetnumbers

# 打开某个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)

# 以lua table的语法格式输出
def print_data(data, output):
    str = ""
    str = str + "{\n\ttags_cfg =\n\t{"
    first_ser = True
    for val in data["tags_cfg"]:
        if not first_ser:
            str = str + ",\n"
        else:
            str = str + "\n"
        first_ser = False
        str = str + "\t\t{\n\t\t\tidentifier = \"%s\",\n\t\t\tregs =\n\t\t\t{" % (val['identifier'])
        first_reg = True
        for reg in val['regs']:
            if not first_reg:
                str = str + ",\n"
            else:
                str = str + "\n"
            first_reg = False
            str = str + "\t\t\t\t{"
            str = str + "tag = \"%s\", " % (reg['tag'])
            str = str + "reg = %d, " % (reg['reg'])
            str = str + "type = \"%s\", " % (reg['type'])
            if reg['desc'] is not None:
                str = str + "desc = \"%s\", " % (reg['desc'])
            str = str + "k = %g, " % (reg['k'])
            str = str + "b = %d" % (reg['b'])
            str = str + "}"
        str = str + "\n"
        str = str + "\t\t\t}\n\t\t}"
    str = str + "\n"
    str = str + "\t}\n}\n"

    print(str)

    if output is not None:
        with open(output, 'w') as f:
            f.write(str)


def servicecontent(data, identifier, model, startaddr, length, duan='big', k=1, b=0):
    duandx = 0
    if (duan is None):
        duandx = 1  # 默认1大端，0小端
    elif (duan == "little"):
        duandx = 0
    elif (duan == "big"):
        duandx = 1
    elif (duan == ""):
        duandx = 1

    lengthformat = None
    if (length == "int8"):  # int8或unit8的不需要<>，就一个字节
        lengthformat = "c"
    elif (length == "int16") and (duandx == 1):
        lengthformat = ">s"
    elif (length == "int16") and (duandx == 0):
        lengthformat = "<s"
    elif (length == "int32") and (duandx == 1):
        lengthformat = ">i"
    elif (length == "int32") and (duandx == 0):
        lengthformat = "<i"
    elif (length == "uint8"):
        lengthformat = "C"
    elif (length == "uint16") and (duandx == 1):
        lengthformat = ">S"
    elif (length == "uint16") and (duandx == 0):
        lengthformat = "<S"
    elif (length == "uint32") and (duandx == 1):
        lengthformat = ">I"
    elif (length == "uint32") and (duandx == 0):
        lengthformat = "<I"
    elif (length == "FLOAT") and (duandx == 0):
        lengthformat = "CCCC"
    elif (length == "FLOAT") and (duandx == 1):
        lengthformat = "CCCC"
    else:  # 不填默认大端S
        lengthformat = ">S"

    desc = None
    if '.' in length:
        bit = re.findall(r"\w+\.(\d+)$", length)
        desc = "bit.band(bit.rshift(val,{}),1)".format(bit[0])

    one = {}
    one['tag'] = model
    one['reg'] = int(startaddr)
    one['type'] = lengthformat
    one['desc'] = desc
    one['k'] = k
    one['b'] = b

    find = False
    for val in data["tags_cfg"]:
        if val['identifier'] == identifier:
            if "regs" not in val:
                val['regs'] = []
            val['regs'].append(one)
            find = True

    if not find:
        service = {}
        service['identifier'] = identifier
        service['regs'] = []
        service['regs'].append(one)
        data['tags_cfg'].append(service)


def generate_lua_code(input, output):
    data = open_excel(input)
    sheet1 = open_sheet(data, 0)
    rows = sheet1.nrows

    jsondata = {}
    jsondata['tags_cfg'] = []

    lastserviceid = None
    servicecount = 0  # 服务数量第一个是0
    regcount = 0  # 服务内有几行寄存器，第一个是0

    for rowindex in range(1, rows):
        identifier = get_cell(sheet1, rowindex, 0)
        model = get_cell(sheet1, rowindex, 2)
        startaddr = get_cell(sheet1, rowindex, 3)
        length = get_cell(sheet1, rowindex, 4)
        duan = get_cell(sheet1, rowindex, 5)
        k = get_cell(sheet1, rowindex, 6)
        b = get_cell(sheet1, rowindex, 7)
        # print(identifier, model, startaddr, length, duan, k)

        if lastserviceid is None:
            lastserviceid = identifier
        elif identifier is None or identifier == '':
            identifier = lastserviceid

        lastserviceid = identifier

        servicecontent(jsondata, identifier, model, startaddr, length, duan, k, b)

    # print(json.dumps(jsondata))
    print_data(jsondata, output)


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

    generate_lua_code(inputfile, outputfile)


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