# coding=utf-8
import datetime
from decimal import Decimal
import xmltodict
import copy
from binascii import b2a_hex, a2b_hex
from functools import partial
import json
import re

from exceptions import ParseHeaderError
from utils import xml_parse, AESBCBEncrypt, crc_modbus, getLogger, signed_int_hex, signed_int_value

logger = getLogger(__name__, filename="dtsy1352.log")

aes_bcb = AESBCBEncrypt("fromhdsegtoacrel", "fromhdsegtoacrel")
ACREL_COMMON_DATAMETA = {
    "root": {
        "common": {
            "building_id": None,
            "gateway_id": None,
            "type": None,
        },
    }
}


class DTSYOperations(object):
    PAYMENT_COMMAND_TABLE = "ld_billing_pay_command"
    PAYMENT_ORDER_TABLE = "ld_billing_pay"
    CONTROL_COMMAND_TABLE = "ld_billing_device_command"
    CONTROL_RECORD_TABLE = "ld_billing_device_command_record"
    BALANCE_TABLE = "ld_device_prepaid_month"

    def __getattr__(self, item):
        if isinstance(item, str):
            return getattr(self, 'event_' + item)

    def event_request(self):
        validate_data = {"@operation": "sequence", "sequence": "powerKeeper"}
        return validate_data

    def event_md5(self):
        validate_data = {"@operation": "result", "result": "pass"}
        return validate_data

    def __init__(self, adapter):
        self.adapter = adapter

    def operations(self, adapter):
        eval('self.event_{}'.format(adapter.operation))(adapter)

    def heart_beat(self, adapter):
        rst = copy.deepcopy(adapter.response_meta)
        time = ''.join(datetime.datetime.now().strftime('%Y%m%d%H%M%S').split("/"))
        rst["root"]["common"]["type"] = "heart_beat"
        rst["root"]["heart_beat"] = {"@operation": "time", "time": time}
        return rst

    def do_heart_beat(self, adapter):
        heart_beat_body = self.heart_beat(adapter)
        rst_data = heart_beat_body
        rst_action = "02"
        rst_body = adapter.get_response_body(rst_data)
        rst_headers = adapter.get_response_headers(rst_action, rst_body)
        result = a2b_hex(rst_headers) + rst_body
        adapter.protocol.write(result)

    def event_01(self, adapter):
        validate_data = getattr(self, 'event_' + adapter.body["root"]["id_validate"]["@operation"])()
        rst = copy.deepcopy(adapter.response_meta)
        rst["root"]["common"]["type"] = "id_validate"
        rst["root"]["id_validate"] = validate_data
        rsts_with_type = [(rst, "01")]
        if adapter.body["root"]["id_validate"]["@operation"] == "md5":  # 身份验证通过
            response_list = adapter.responses(rsts_with_type)
            adapter.protocol.write(response_list[0])

            # 每1分钟请求一次0x02心跳包
            heart_beat = partial(self.do_heart_beat, adapter)
            adapter.protocol.loop_task(heart_beat)

            # 5秒后，每5分钟请求一次0x03电能数据
            request_data = partial(adapter.protocol.write, b'\x1f\x1f\x03\x00\x00\x00\x00')
            adapter.reactor.callLater(5,
                                      adapter.protocol.loop_task,
                                      func=request_data,
                                      tm=300)

            # 10秒后，每1分钟触发一次充值检测指令
            recharge_func = partial(self.check_and_recharge, adapter)
            adapter.reactor.callLater(10,
                                      adapter.protocol.loop_task,
                                      func=recharge_func,
                                      tm=60)
            # 40秒后，每1分钟触发一次强控检测指令
            adapter.reactor.callLater(40,
                                      adapter.protocol.loop_task,
                                      func=self.check_control_command,
                                      tm=60)
        else:
            response_list = adapter.responses(rsts_with_type)
            adapter.protocol.write(response_list[0])

    def event_02(self, adapter):
        pass

    # 电能数据
    def event_03(self, adapter):
        return_time = None
        if adapter.body is not None:
            if isinstance(
                    json.loads(
                        json.dumps(
                            adapter.body['root']['data']['meters']['meter'])),
                    list):
                for meter_content in adapter.body['root']['data']['meters']['meter']:
                    self.insert_values(meter_content, adapter)
            else:
                meter_content = adapter.body['root']['data']['meters']['meter']
                self.insert_values(meter_content, adapter)

            return_time = adapter.body["root"]["data"]["time"]
        adapter.responses_ack(time=return_time, add_header=False)

    def insert_values(self, meter_content, adapter):
        meter = {}
        meter["building_id"] = adapter.body['root']["common"]["building_id"]
        meter["gateway_id"] = adapter.body['root']["common"]["gateway_id"]
        meter["meter_id"] = meter_content['@id']
        meter["meter_name"] = meter_content['@name']
        up_time = adapter.body['root']["data"]['time']
        tday = '-'.join([up_time[0:4], up_time[4:6], up_time[6:8]])
        meter["day"] = tday
        meter["hour"] = up_time[8:10]
        meter["time"] = meter["day"] + " " + meter["hour"] + ':%s:%s' % (up_time[10:12], up_time[12:14])
        for j in meter_content["function"]:
            meter[j["@id"]] = float(j["#text"])
            if j["@error"] == "":
                meter["status"] = "1"
            else:
                meter["status"] = "0"
        sql = create_insert_sql(meter)
        adapter.protocol.db.sql(sql)
        if "DDSY1352" in meter["meter_name"] or "DTSY1352" in meter["meter_name"]:
            self.report_balance(meter, adapter)

    def report_balance(self, meter, adapter):
        up_time = adapter.body['root']["data"]['time']
        current_hour = up_time[:10]
        building_id = meter["building_id"]
        gateway_id = meter["gateway_id"]
        meter_id = meter["meter_id"]

        last_balance_hour = adapter.factory.last_balance_hour[adapter.protocol.clientID].get(meter_id, None)
        if current_hour == last_balance_hour:
            pass
        else:
            month = up_time[:4] + "-" + up_time[4:6]
            day = 'day' + str(int(up_time[6:8]))
            sql = """
            SELECT {day}
            FROM ld_device_prepaid_month
            WHERE building_id='{building_id}' AND gateway_id='{gateway_id}' AND meter_id='{meter_id}' AND month='{month}'
            LIMIT 1
            """.format(day=day, building_id=building_id, gateway_id=gateway_id, meter_id=meter_id, month=month)
            adapter.protocol.payment_db.fetch(sql).addCallback(self.do_report, meter, adapter)
            adapter.factory.last_balance_hour[adapter.protocol.clientID].update({meter_id: current_hour})

    def do_report(self, data, meter, adapter):
        building_id = meter["building_id"]
        gateway_id = meter["gateway_id"]
        meter_id = meter["meter_id"]
        balance = meter['Balance']
        up_time = adapter.body['root']["data"]['time']
        month = up_time[:4] + "-" + up_time[4:6]
        day = 'day' + str(int(up_time[6:8]))
        hour = up_time[8:10]
        minute_sec = up_time[10:]
        if len(data) == 0:
            t_list = [''] * int(hour) + [minute_sec]
            m_list = [''] * int(hour) + [str(balance)]
            d = "{'t':'%s','m':'%s'}" % (','.join(t_list), ','.join(m_list))
            sql = """
            INSERT INTO {table} 
            (building_id, gateway_id, meter_id, month, {day}, create_date, create_by, update_date, update_by)
            VALUES ('{building_id}', '{gateway_id}', '{meter_id}', '{month}', "{d}", '{create_date}', 'cc', '{update_date}', 'cc')
            """.format(table=self.BALANCE_TABLE, day=day, building_id=building_id, gateway_id=gateway_id, meter_id=meter_id,
                       month=month, d=d, create_date=up_time, update_date=up_time)
        else:
            day_data = data[0][day]
            if day_data is None:
                t_list = [''] * int(hour) + [minute_sec]
                m_list = [''] * int(hour) + [str(balance)]
                d = "{'t':'%s','m':'%s'}" % (','.join(t_list), ','.join(m_list))
            else:
                t_list = eval(day_data)['t'].split(",")
                m_list = eval(day_data)['m'].split(",")
                exist_hour = len(t_list)
                pad_hour = int(hour) - exist_hour
                if pad_hour < 0:
                    # logger.debug("ENOUGH BALANCE RECORD, RETURN...")
                    return
                t_list += [''] * pad_hour + [minute_sec]
                m_list += [''] * pad_hour + [str(balance)]
                d = "{'t':'%s','m':'%s'}" % (','.join(t_list), ','.join(m_list))
            sql = """
            UPDATE {table} SET {day}="{d}", update_date={update_date}, update_by='cc'
            WHERE building_id='{building_id}' AND gateway_id='{gateway_id}' AND meter_id='{meter_id}' AND month='{month}'
            """.format(table=self.BALANCE_TABLE, day=day, d=d, update_date=up_time,
                       building_id=building_id, gateway_id=gateway_id, meter_id=meter_id, month=month)
        adapter.protocol.payment_db.sql(sql)

    # 充值功能
    def check_and_recharge(self, adapter):
        building_id = adapter.factory.clients_content[adapter.protocol.clientID]["building_id"]
        gateway_id = adapter.factory.clients_content[adapter.protocol.clientID]["gateway_id"]

        command_sql = """
        SELECT serial_number AS order_id, meter_id, recharge_amount AS payment 
        FROM {table} 
        WHERE building_id = '{building_id}' AND gateway_id = '{gateway_id}'
        ORDER BY create_date
        LIMIT 1
        """.format(table=self.PAYMENT_COMMAND_TABLE,
                   building_id=building_id,
                   gateway_id=gateway_id)
        adapter.protocol.payment_db.fetch(command_sql).addCallback(self.do_recharge, adapter=adapter)

    def do_recharge(self, data, adapter):
        if len(data) == 0:
            logger.debug("{} {} NO RECHARGE COMMAND...".format(adapter.factory.clients_content[adapter.protocol.clientID]["building_id"],
                                                               adapter.factory.clients_content[adapter.protocol.clientID]["gateway_id"]))
        else:
            kw = data[0]
            adapter.factory.clients_content[adapter.protocol.clientID]["order_id"] = kw["order_id"]
            adapter.factory.clients_content[adapter.protocol.clientID]["meter_id"] = kw["meter_id"]
            adapter.factory.clients_content[adapter.protocol.clientID]["payment"] = float(kw["payment"])
            adapter.factory.clients_content[adapter.protocol.clientID]["recharge_msg"] = self.recharge_message(**kw)
            adapter.factory.clients_content[adapter.protocol.clientID]["recharge_status"] = 0
            adapter.factory.clients_content[adapter.protocol.clientID]["before_collect"] = 1
            adapter.factory.clients_content[adapter.protocol.clientID]["after_collect"] = 1
            self.send_data_collect_msg()

    # 集抄指令
    def send_data_collect_msg(self):
        msg = self.data_collect_message()
        self.adapter.protocol.write(msg)
        self.adapter.reactor.callLater(5, self.check_recharge_status)
        if self.adapter.factory.clients_content[self.adapter.protocol.clientID]["recharge_status"] == 0:
            self.adapter.factory.clients_content[self.adapter.protocol.clientID]["recharge_status"] = 1
        elif self.adapter.factory.clients_content[self.adapter.protocol.clientID]["recharge_status"] == 2:
            self.adapter.factory.clients_content[self.adapter.protocol.clientID]["recharge_status"] = 3

    def data_collect_message(self):
        """集抄指令"""
        meter_id = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["meter_id"]
        addr = hex(int(meter_id[2:]))[2:].zfill(2)  # 仪表地址
        command = "%s0301000007" % addr
        msg = self.create_pass_through_msg(meter_id, command)
        return msg

    def check_recharge_status(self):
        recharge_status = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["recharge_status"]
        before = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["before_collect"]
        after = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["after_collect"]
        if recharge_status == 1:
            if before < 3:
                self.send_data_collect_msg()
                self.adapter.factory.clients_content[self.adapter.protocol.clientID]["before_collect"] += 1
            else:
                order_id = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["order_id"]  # 充值订单号
                # 下发失败，订单状态改为6下发失败
                update_sql = """
                UPDATE {table} SET status='6', update_date='{update_time}', update_by='cc' WHERE serial_number='{order_id}'
                """.format(table=self.PAYMENT_ORDER_TABLE, order_id=order_id,
                           update_time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
                self.adapter.protocol.payment_db.sql(update_sql)
                logger.debug(order_id + " COMMAND SENT FAILED!")
                # 删除command表对应的订单号
                del_sql = "DELETE FROM {table} WHERE serial_number='{order_id}'".format(table=self.PAYMENT_COMMAND_TABLE, order_id=order_id)
                self.adapter.protocol.payment_db.sql(del_sql)
                logger.debug(order_id + " COMMAND CLEANED!")

        elif recharge_status == 3:
            if after < 3:
                self.send_data_collect_msg()
                self.adapter.factory.clients_content[self.adapter.protocol.clientID]["after_collect"] += 1
            else:
                order_id = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["order_id"]  # 充值订单号
                # 未收到充值后集抄回复，订单状态改为8充值异常
                update_sql = """
                UPDATE {table} SET status='8', update_date='{update_time}', update_by='cc' WHERE serial_number='{order_id}'
                """.format(table=self.PAYMENT_ORDER_TABLE, order_id=order_id,
                           update_time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
                self.adapter.protocol.payment_db.sql(update_sql)
                logger.debug(order_id + " BALANCE CHECK FAILED!")

    # 充值指令
    def send_recharge_msg(self):
        order_id = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["order_id"]  # 充值订单号
        msg = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["recharge_msg"]  # 充值透传内容
        # 下发透传指令
        self.adapter.protocol.write(msg)
        # 下发完成，订单状态改为5下发成功
        update_sql = """
        UPDATE {table} SET status='5', update_date='{update_time}', update_by='cc' WHERE serial_number='{order_id}'
        """.format(table=self.PAYMENT_ORDER_TABLE, order_id=order_id, update_time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        self.adapter.protocol.payment_db.sql(update_sql)
        logger.debug(order_id + " RECHARGE COMMAND ASSIGNED!")
        # 删除command表对应的订单号
        del_sql = "DELETE FROM {table} WHERE serial_number='{order_id}'".format(table=self.PAYMENT_COMMAND_TABLE, order_id=order_id)
        self.adapter.protocol.payment_db.sql(del_sql)
        logger.debug(order_id + " RECHARGE COMMAND CLEANED!")

    def recharge_message(self, **kwarg):
        """充值透传指令"""
        meter_id = kwarg["meter_id"]  # 电表id
        payment = float(kwarg["payment"])  # 充值金额
        addr = hex(int(meter_id[2:]))[2:].zfill(2)  # 仪表地址
        command = "{addr}10004C000204{payment}".format(addr=addr,
                                                       payment=signed_int_hex(int(payment * 100)))  # 充值指令
        msg = self.create_pass_through_msg(meter_id, command)
        return msg

    # 强控指令
    def check_control_command(self):
        building_id = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["building_id"]
        gateway_id = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["gateway_id"]

        command_sql = """
        SELECT id AS command_id, meter_id, type, value
        FROM {table} 
        WHERE building_id = '{building_id}' AND gateway_id = '{gateway_id}' and type = 'control'
        ORDER BY create_date
        LIMIT 5
        """.format(table=self.CONTROL_COMMAND_TABLE,
                   building_id=building_id,
                   gateway_id=gateway_id)
        self.adapter.protocol.payment_db.fetch(command_sql).addCallback(self.do_control_command)

    def do_control_command(self, data):
        if len(data) == 0:
            logger.debug("{} {} NO CONTROL COMMAND...".format(
                self.adapter.factory.clients_content[self.adapter.protocol.clientID]["building_id"],
                self.adapter.factory.clients_content[self.adapter.protocol.clientID]["gateway_id"]))
        else:
            sec = 0
            self.adapter.factory.clients_content[self.adapter.protocol.clientID]["command_status"] = {}
            for kw in data:
                self.adapter.reactor.callLater(sec, self.send_control_command_msg, kw=kw)
                sec += 2

    def send_control_command_msg(self, kw):
        self.adapter.factory.clients_content[self.adapter.protocol.clientID]["command_id"] = kw["command_id"]
        self.adapter.factory.clients_content[self.adapter.protocol.clientID]["command_status"].update({kw["command_id"]: 0})
        meter_id = kw["meter_id"]
        command_id = kw["command_id"]
        addr = hex(int(meter_id[2:]))[2:].zfill(2)  # 仪表地址
        if kw["value"] == "0":  # 强制闭合
            command = "{addr}10005700020400010000".format(addr=addr)
        elif kw["value"] == "1":  # 强制断开
            command = "{addr}10005700020400010001".format(addr=addr)
        elif kw["value"] == "2":  # 恢复预付费
            command = "{addr}10005700020400000000".format(addr=addr)
        else:
            return
        msg = self.create_pass_through_msg(meter_id, command)
        self.adapter.protocol.write(msg)
        del_sql = "DELETE FROM {table} WHERE id='{command_id}'".format(table=self.CONTROL_COMMAND_TABLE, command_id=command_id)
        self.adapter.protocol.payment_db.sql(del_sql)
        logger.debug(str(command_id) + " CONTROL COMMAND CLEANED!")
        self.adapter.reactor.callLater(5, self.check_command_status, command_id=command_id)

    def check_command_status(self, command_id):
        command_status = self.adapter.factory.clients_content[self.adapter.protocol.clientID]["command_status"][command_id]
        if command_status == 0:  # 未收到回复
            result = '{"message":"failed","code":"1", "data":"1"}'
            update_sql = """
            UPDATE %s SET result='%s', update_date='%s', update_by='cc' 
            WHERE id='%s'
            """ % (self.CONTROL_RECORD_TABLE,
                   result,
                   datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                   command_id)
            self.adapter.protocol.payment_db.sql(update_sql)
        elif command_status == 1:  # 已收到回复
            pass
        else:  # 其他情况
            pass

    # 透传回复（集抄回复，充值回复，强控回复）
    def event_f2(self, adapter):
        """透传指令回复"""
        order_id = adapter.factory.clients_content[adapter.protocol.clientID].get("order_id")
        recharge_status = adapter.factory.clients_content[adapter.protocol.clientID].get("recharge_status")
        command_id = adapter.factory.clients_content[adapter.protocol.clientID].get("command_id")

        return_msg = adapter.body.split("|")[1][:-4]
        return_crc = adapter.body.split("|")[1][-4:]
        crc = crc_modbus(return_msg, left_high=False)
        logger.debug(str(command_id) + "RESPONSE RECEIVED: " + adapter.body)
        if return_crc == crc:  # crc校验通过
            if return_msg[2:6] == '030E':  # 集抄回复
                p = int(return_msg[6:10], base=16) * 0.001
                ept = int(return_msg[10:18], base=16) * 0.01
                balance = signed_int_value(return_msg[18:26]) * 0.01
                buy_time = int(return_msg[26:30], base=16)
                meter_status = int(return_msg[30:34], base=16)
                if recharge_status == 1:
                    adapter.factory.clients_content[adapter.protocol.clientID]["buy_time"] = buy_time
                    update_sql = """
                    UPDATE {table} 
                    SET before_balance={before_balance}, update_date='{update_time}', update_by='cc' 
                    WHERE serial_number='{order_id}'
                    """.format(table=self.PAYMENT_ORDER_TABLE,
                               before_balance=balance,
                               order_id=order_id,
                               update_time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
                    adapter.protocol.payment_db.sql(update_sql)
                    self.send_recharge_msg()
                    self.adapter.reactor.callLater(5, self.send_data_collect_msg)
                    adapter.factory.clients_content[adapter.protocol.clientID]["recharge_status"] = 2
                elif recharge_status == 3:
                    if buy_time - adapter.factory.clients_content[adapter.protocol.clientID]["buy_time"] == 1:
                        adapter.factory.clients_content[adapter.protocol.clientID]["buy_time"] = buy_time
                        update_sql = """
                        UPDATE {table} 
                        SET status='7', after_balance={after_balance}, update_date='{update_time}', update_by='cc' 
                        WHERE serial_number='{order_id}'
                        """.format(table=self.PAYMENT_ORDER_TABLE,
                                   after_balance=balance,
                                   order_id=order_id,
                                   update_time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
                        adapter.protocol.payment_db.sql(update_sql)
                    else:
                        update_sql = """
                        UPDATE {table} SET status='8', update_date='{update_time}', update_by='cc' WHERE serial_number='{order_id}'
                        """.format(table=self.PAYMENT_ORDER_TABLE,
                                   order_id=order_id,
                                   update_time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
                        adapter.protocol.payment_db.sql(update_sql)
                    adapter.factory.clients_content[adapter.protocol.clientID]["recharge_status"] = 4
            elif return_msg[2:8] == '10004C':  # 充值回复
                update_sql = """
                UPDATE {table} SET status='7', update_date='{update_time}', update_by='cc' WHERE serial_number='{order_id}'
                """.format(table=self.PAYMENT_ORDER_TABLE, order_id=order_id, update_time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
                adapter.protocol.payment_db.sql(update_sql)
                logger.debug(order_id + " RECHARGE COMPLETE!")
            elif return_msg[2:8] == '100057':  # 强控回复
                result = '{"message":"success","code":"0", "data":"0"}'
                update_sql = """
                UPDATE %s SET result='%s', update_date='%s', update_by='cc' 
                WHERE id='%s'
                """ % (self.CONTROL_RECORD_TABLE,
                       result,
                       datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                       command_id)
                adapter.protocol.payment_db.sql(update_sql)
                self.adapter.factory.clients_content[self.adapter.protocol.clientID]["command_status"].update({command_id: 1})
            else:
                pass

    @staticmethod
    def create_pass_through_msg(meter_id, modbus_command):
        """创建透传命令报文"""
        crc = crc_modbus(modbus_command, left_high=False)  # crc校验码
        data = meter_id + "|" + modbus_command + crc  # 需要下发的透传内容
        body = a2b_hex(aes_bcb.encrypt(data))  # 透传内容加密，并转化成16进制
        length = hex(len(body))[2:].zfill(8)  # 计算body的长度，并写成16进制，补全至4字节
        head = "1f1ff1" + length  # 消息报头
        msg = a2b_hex(head) + body  # 完整的下发透传报文
        return msg


class AcrelDTSY1352(object):
    """
    AcrelDTSY1352系列为安科瑞ANet系列网关的预付费转发数据格式

    目前可供解析的表型号有：（其中1、2为预付费操作）
    1. DTSY1352
    2. DDSY1352
    3. DTSD1352
    4. PZ72L-E4/A
    5. AEM72/CFK
    """
    operations = ["01", "02", "03", "f2"]
    header_format = '1f1f'
    headers = None
    operation = None
    length = None
    message_length = 0
    message_format = 'xml'
    response_meta = copy.deepcopy(ACREL_COMMON_DATAMETA)

    def initial(self, data, protocol, reactor):
        self.protocol = protocol
        self.factory = protocol.factory
        self.reactor = reactor
        self.operations_event = DTSYOperations(self)
        self.parse(data)

    def parse(self, data):
        self.parse_headers(data)
        self.parse_body()

    def parse_headers(self, data):
        headers = b2a_hex(data[0:7]).decode()
        if headers.startswith(self.header_format):
            self.headers = headers
            self.factory.headers[self.protocol.clientID] = self.headers
            self.data = data
        else:
            if self.factory.not_enough[self.protocol.clientID]:
                self.headers = self.factory.headers[self.protocol.clientID]
                self.factory.clients_message[self.protocol.clientID] += data
                self.data = self.factory.clients_message[self.protocol.clientID]
            else:
                raise ParseHeaderError("error message information")

        if len(self.headers) == 0:
            raise ParseHeaderError("no found headers information")
        self.operation = self.headers[4:6]
        if self.operation not in self.operations:
            raise ParseHeaderError("unknown operation: %s" % self.operation)
        self.length = int(self.headers[6:], 16)
        self.message_length = self.length + 7

        recv_message_length = len(self.data)
        if recv_message_length < self.message_length:
            self.factory.clients_message[self.protocol.clientID] = self.data
            self.factory.not_enough[self.protocol.clientID] = True
        elif recv_message_length == self.message_length:
            self.factory.clients_message[self.protocol.clientID] = b''
            self.factory.not_enough[self.protocol.clientID] = False
        elif recv_message_length > self.message_length:
            self.factory.clients_message[self.protocol.clientID] = b''
            self.factory.not_enough[self.protocol.clientID] = False
            raise ParseHeaderError("recv message length too lang, reset cache")

    def parse_body(self):
        self.body = None
        if not self.factory.not_enough[self.protocol.clientID]:
            body = self.data[7:]
            eval("self.body_{0}".format(self.operation))(body)
            if self.original_body is not None:
                if self.operation == "f2":
                    self.body = self.original_body
                else:
                    xml_header, xml_body = xml_parse(self.original_body)
                    if xml_body is not None:
                        self.body = xmltodict.parse(xml_body)
                        self.factory.clients_content[self.protocol.clientID]["building_id"] = self.body["root"]["common"]["building_id"]
                        self.factory.clients_content[self.protocol.clientID]["gateway_id"] = self.body["root"]["common"]["gateway_id"]
        self.response_meta["root"]["common"]["building_id"] = self.factory.clients_content[self.protocol.clientID]["building_id"]
        self.response_meta["root"]["common"]["gateway_id"] = self.factory.clients_content[self.protocol.clientID]["gateway_id"]

    def run_operations(self):
        if self.factory.not_enough[self.protocol.clientID]:
            self.responses_ack(add_header=False)
            return
        if self.body is None:
            if self.operation == "02":
                return
            self.responses_ack(add_header=False)
            return
        self.operations_event.operations(self)

    def responses(self, data_list, add_header=True):
        result_list = []
        for data in data_list:
            rst_data = data[0]
            rst_action = data[1]
            rst_body = self.get_response_body(rst_data)
            if add_header:
                rst_headers = self.get_response_headers(rst_action, rst_body)
                result = a2b_hex(rst_headers) + rst_body
            else:
                result = rst_body
            result_list.append(result)
        return result_list

    def responses_ack(self, status=True, time=None, add_header=True):
        rsts_with_type = self.ack_ok(status=status, time=time)
        response_list = self.responses(rsts_with_type, add_header=add_header)
        self.protocol.write(response_list[0])

    def get_response_body(self, rst_data):
        if rst_data is not None:
            if self.message_format == "xml":
                result = ''.join(xmltodict.unparse(rst_data).split('\n'))
                result = result.encode()
                return result
            return ''.encode()
        else:
            return ''.encode()

    def get_response_headers(self, rst_action, rst_data):
        rst_action = rst_action or self.operation
        rst_data = rst_data or ''
        headers = ((self.headers[:4] + rst_action) +
                   hex(len(rst_data))[2:].zfill(8))
        return headers

    def body_01(self, body):
        self.original_body = body.decode()

    def body_02(self, body):
        self.original_body = body.decode()

    def body_03(self, body):
        aes_body = b2a_hex(body)
        self.original_body = aes_bcb.decrypt(aes_body)

    def body_f2(self, body):
        aes_body = b2a_hex(body)
        decrypt_body = aes_bcb.decrypt(aes_body)
        decrypt_body = re.search('^(\d\d\d\d\d\|[0-9A-Za-z]+).*', decrypt_body)
        if decrypt_body is not None:
            self.original_body = decrypt_body.groups()[0]

    def ack_ok(self, status=True, time=None):
        rst = copy.deepcopy(self.response_meta)
        if time is None:
            time = ''.join(
                datetime.datetime.now().strftime('%Y%m%d%H%M%S').split("/"))
        if self.operation == '03':
            rst["root"]["common"]["type"] = "energy_data"
            rst["root"]["data"] = {"@operation": "report"}
            rst["root"]["time"] = time
            if status:
                rst["root"]["ack"] = "OK"
            else:
                rst["root"]["ack"] = "fail"
        else:
            rst["root"]["ack"] = "fail"
            self.operation = "03"
        return [(rst, self.operation)]


def create_insert_sql(data):
    monitor_model = {}
    column = []
    content = []
    monitor_model["device_imei"] = '--'.join([data["building_id"], data["gateway_id"], data["meter_id"]])
    monitor_model["building_code"] = data["building_id"]
    monitor_model["gateway_code"] = data["gateway_id"]
    monitor_model["meter_code"] = data["meter_id"]
    monitor_model["day"] = data["day"]
    monitor_model["hour"] = data["hour"]
    monitor_model["device_content"] = ''

    if 'DDSY1352' in data["meter_name"]:  # DDSY1352
        if int(data["control"]) == 0:
            if float(data["Balance"]) > 0:
                data["switch"] = 0
            else:
                data["switch"] = 1
        monitor_model["epi"] = data["EPI"]
        monitor_model["epi_f"] = data["EPIF"]
        monitor_model["epi_g"] = data["EPIG"]
        monitor_model["epi_j"] = data["EPIJ"]
        monitor_model["epi_p"] = data["EPIP"]
        monitor_model["a_dy"] = data["U"]
        monitor_model["a_dl"] = data["I"]
        monitor_model["total_yggl"] = data["P"]
        monitor_model["total_wggl"] = data["Q"]
        monitor_model["total_szgl"] = data["S"]
        monitor_model["total_glys"] = data["PF"]
        monitor_model["pl"] = data["F"]
        monitor_model["extend_s1"] = ",".join([str(int(data["control"])), str(int(data["switch"])), str(data["status"]), str(data["Balance"])])
    elif 'DTSY1352' in data["meter_name"]:  # DTSY1352
        if int(data["control"]) == 0:
            if float(data["Balance"]) > 0:
                data["switch"] = 0
            else:
                data["switch"] = 1
        pt = int(data["Pt"])  # 电压变比
        ct = int(data["Ct"])  # 电流变比
        monitor_model["epi"] = data["EPI"] * ct * pt
        monitor_model["epi_f"] = data["EPIF"] * ct * pt
        monitor_model["epi_g"] = data["EPIG"] * ct * pt
        monitor_model["epi_j"] = data["EPIJ"] * ct * pt
        monitor_model["epi_p"] = data["EPIP"] * ct * pt
        monitor_model["a_dy"] = data["Ua"] * pt
        monitor_model["b_dy"] = data["Ub"] * pt
        monitor_model["c_dy"] = data["Uc"] * pt
        monitor_model["a_dl"] = data["Ia"] * ct
        monitor_model["b_dl"] = data["Ib"] * ct
        monitor_model["c_dl"] = data["Ic"] * ct
        monitor_model["ab_dy"] = data["Uab"] * pt
        monitor_model["bc_dy"] = data["Ubc"] * pt
        monitor_model["ac_dy"] = data["Uca"] * pt
        monitor_model["a_yggl"] = data["Pa"] * ct * pt
        monitor_model["b_yggl"] = data["Pb"] * ct * pt
        monitor_model["c_yggl"] = data["Pc"] * ct * pt
        monitor_model["total_yggl"] = data["P"] * ct * pt
        monitor_model["a_wggl"] = data["Qa"] * ct * pt
        monitor_model["b_wggl"] = data["Qb"] * ct * pt
        monitor_model["c_wggl"] = data["Qc"] * ct * pt
        monitor_model["total_wggl"] = data["Q"] * ct * pt
        monitor_model["a_szgl"] = data["Sa"] * ct * pt
        monitor_model["b_szgl"] = data["Sb"] * ct * pt
        monitor_model["c_szgl"] = data["Sc"] * ct * pt
        monitor_model["total_szgl"] = data["S"] * ct * pt
        monitor_model["a_glys"] = data["Pfa"]
        monitor_model["b_glys"] = data["Pfb"]
        monitor_model["c_glys"] = data["Pfc"]
        monitor_model["total_glys"] = data["Pf"]
        monitor_model["pl"] = data["Fr"]
        monitor_model["md"] = data["MD"] * ct * pt
        monitor_model["extend_s1"] = ",".join([str(int(data["control"])), str(int(data["switch"])), str(data["status"]), str(data["Balance"])])
    elif 'DTSD1352' in data["meter_name"]:  # DTSD1352
        pt = int(data["PT"])  # 电压变比
        ct = int(data["CT"])  # 电流变比
        monitor_model["epi"] = data["EPI"] * ct * pt
        monitor_model["epi_f"] = data["EPIF"] * ct * pt
        monitor_model["epi_g"] = data["EPIG"] * ct * pt
        monitor_model["epi_j"] = data["EPIJ"] * ct * pt
        monitor_model["epi_p"] = data["EPIP"] * ct * pt
        monitor_model["epe"] = data["EPE"] * ct * pt
        monitor_model["a_dy"] = data["Ua"] * pt
        monitor_model["b_dy"] = data["Ub"] * pt
        monitor_model["c_dy"] = data["Uc"] * pt
        monitor_model["a_dl"] = data["Ia"] * ct
        monitor_model["b_dl"] = data["Ib"] * ct
        monitor_model["c_dl"] = data["Ic"] * ct
        monitor_model["ab_dy"] = data["Uab"] * pt
        monitor_model["bc_dy"] = data["Ubc"] * pt
        monitor_model["ac_dy"] = data["Uca"] * pt
        monitor_model["a_yggl"] = data["Pa"] * ct * pt
        monitor_model["b_yggl"] = data["Pb"] * ct * pt
        monitor_model["c_yggl"] = data["Pc"] * ct * pt
        monitor_model["total_yggl"] = data["P"] * ct * pt
        monitor_model["a_wggl"] = data["Qa"] * ct * pt
        monitor_model["b_wggl"] = data["Qb"] * ct * pt
        monitor_model["c_wggl"] = data["Qc"] * ct * pt
        monitor_model["total_wggl"] = data["Q"] * ct * pt
        monitor_model["a_glys"] = data["Pfa"]
        monitor_model["b_glys"] = data["Pfb"]
        monitor_model["c_glys"] = data["Pfc"]
        monitor_model["total_glys"] = data["Pf"]
        monitor_model["pl"] = data["Fr"]
        monitor_model["md"] = data["MD"] * ct * pt
        monitor_model["extend_s1"] = ",".join(["", "", str(data["status"]), ""])
    elif 'PZ72L-E4-A' in data["meter_name"]:  # PZ72L-E4/A
        pt = int(data["pt"])  # 电压变比
        ct = int(data["ct"])  # 电流变比
        monitor_model["epi"] = data["epi"] * ct * pt
        monitor_model["epe"] = data["epe"] * ct * pt
        monitor_model["a_dy"] = data["ua"] * pt
        monitor_model["b_dy"] = data["ub"] * pt
        monitor_model["c_dy"] = data["uc"] * pt
        monitor_model["ab_dy"] = data["uab"] * pt
        monitor_model["bc_dy"] = data["ubc"] * pt
        monitor_model["ac_dy"] = data["uca"] * pt
        monitor_model["a_dl"] = data["ia"] * ct
        monitor_model["b_dl"] = data["ib"] * ct
        monitor_model["c_dl"] = data["ic"] * ct
        monitor_model["a_yggl"] = data["pa"] * ct * pt
        monitor_model["b_yggl"] = data["pb"] * ct * pt
        monitor_model["c_yggl"] = data["pc"] * ct * pt
        monitor_model["total_yggl"] = data["p"] * ct * pt
        monitor_model["a_wggl"] = data["qa"] * ct * pt
        monitor_model["b_wggl"] = data["qb"] * ct * pt
        monitor_model["c_wggl"] = data["qc"] * ct * pt
        monitor_model["total_wggl"] = data["q"] * ct * pt
        monitor_model["a_glys"] = data["pfa"]
        monitor_model["b_glys"] = data["pfb"]
        monitor_model["c_glys"] = data["pfc"]
        monitor_model["total_glys"] = data["pf"]
        monitor_model["pl"] = data["f"]
        monitor_model["md"] = data["md"] * ct * pt
        monitor_model["extend_s1"] = ",".join(["", "", str(data["status"]), ""])
    elif "PZ72L-E4-KC" in data["meter_name"]:  # PZ72L-E4/KC
        pt = int(data["pt"])  # 电压变比
        ct = int(data["ct"])  # 电流变比
        dpct = bin(int(data["dpct"]))[2:].zfill(16)
        dpt = int(dpct[0:8], base=2)
        dct = int(dpct[8:16], base=2)
        pq = bin(int(data["pq"]))[2:].zfill(16)
        dpq = int(pq[0:8], base=2)
        sign = list(pq[8:16])

        monitor_model["a_dy"] = data["ua"] * 10 ** (dpt - 4)
        monitor_model["b_dy"] = data["ub"] * 10 ** (dpt - 4)
        monitor_model["c_dy"] = data["uc"] * 10 ** (dpt - 4)
        monitor_model["ab_dy"] = data["uab"] * 10 ** (dpt - 4)
        monitor_model["bc_dy"] = data["ubc"] * 10 ** (dpt - 4)
        monitor_model["ac_dy"] = data["uca"] * 10 ** (dpt - 4)
        monitor_model["a_dl"] = data["ia"] * 10 ** (dct - 4)
        monitor_model["b_dl"] = data["ib"] * 10 ** (dct - 4)
        monitor_model["c_dl"] = data["ic"] * 10 ** (dct - 4)
        monitor_model["a_yggl"] = data["pa"] * 10 ** (dpq - 7) * -1 ** int(sign[0])
        monitor_model["b_yggl"] = data["pb"] * 10 ** (dpq - 7) * -1 ** int(sign[1])
        monitor_model["c_yggl"] = data["pc"] * 10 ** (dpq - 7) * -1 ** int(sign[2])
        monitor_model["total_yggl"] = data["p"] * 10 ** (dpq - 7) * -1 ** int(sign[3])
        monitor_model["a_wggl"] = data["qa"] * 10 ** (dpq - 7) * -1 ** int(sign[4])
        monitor_model["b_wggl"] = data["qb"] * 10 ** (dpq - 7) * -1 ** int(sign[5])
        monitor_model["c_wggl"] = data["qc"] * 10 ** (dpq - 7) * -1 ** int(sign[6])
        monitor_model["total_wggl"] = data["q"] * 10 ** (dpq - 7) * -1 ** int(sign[7])
        monitor_model["a_szgl"] = data["sa"] * 10 ** (dpq - 7)
        monitor_model["b_szgl"] = data["sb"] * 10 ** (dpq - 7)
        monitor_model["c_szgl"] = data["sc"] * 10 ** (dpq - 7)
        monitor_model["total_szgl"] = data["s"] * 10 ** (dpq - 7)
        monitor_model["a_glys"] = data["pfa"]
        monitor_model["b_glys"] = data["pfb"]
        monitor_model["c_glys"] = data["pfc"]
        monitor_model["total_glys"] = data["pf"]
        monitor_model["pl"] = data["f"]
        monitor_model["epi"] = data["epi"] * 0.001 * pt * ct
        monitor_model["epe"] = data["epe"] * 0.001 * pt * ct
        monitor_model["wqp"] = data["eqi"] * 0.001 * pt * ct
        monitor_model["wpp"] = data["eqe"] * 0.001 * pt * ct
        monitor_model["extend_s1"] = ",".join(["", "", str(data["status"]), ""])
    elif 'AEM72-CFK' in data["meter_name"]:  # AEM72/CFK
        pt = int(data["pt"])  # 电压变比
        ct = int(data["ct"])  # 电流变比
        monitor_model["epi"] = data["epi"] * ct * pt
        monitor_model["epe"] = data["epe"] * ct * pt
        monitor_model["a_dy"] = data["ua"] * pt
        monitor_model["b_dy"] = data["ub"] * pt
        monitor_model["c_dy"] = data["uc"] * pt
        monitor_model["ab_dy"] = data["uab"] * pt
        monitor_model["bc_dy"] = data["ubc"] * pt
        monitor_model["ac_dy"] = data["uca"] * pt
        monitor_model["a_dl"] = data["ia"] * ct
        monitor_model["b_dl"] = data["ib"] * ct
        monitor_model["c_dl"] = data["ic"] * ct
        monitor_model["a_yggl"] = data["pa"] * ct * pt
        monitor_model["b_yggl"] = data["pb"] * ct * pt
        monitor_model["c_yggl"] = data["pc"] * ct * pt
        monitor_model["total_yggl"] = data["p"] * ct * pt
        monitor_model["a_wggl"] = data["qa"] * ct * pt
        monitor_model["b_wggl"] = data["qb"] * ct * pt
        monitor_model["c_wggl"] = data["qc"] * ct * pt
        monitor_model["total_wggl"] = data["q"] * ct * pt
        monitor_model["a_glys"] = data["pfa"]
        monitor_model["b_glys"] = data["pfb"]
        monitor_model["c_glys"] = data["pfc"]
        monitor_model["total_glys"] = data["pf"]
        monitor_model["pl"] = data["f"]
        monitor_model["md"] = data["md"] * ct * pt
        monitor_model["extend_s1"] = ",".join(["", "", str(data["status"]), ""])
    else:
        pass
    monitor_model["create_date"] = data["time"]
    monitor_model["update_date"] = data["time"]
    monitor_model["create_by"] = 'cc'
    monitor_model["update_by"] = 'cc'

    for k, v in monitor_model.items():
        column.append('`%s`' % k)
        if isinstance(v, str):
            content.append("'%s'" % v)
        else:
            content.append(str(v))
    table = 'ld_device_data_%s' % (data["time"][0:4] + data["time"][5:7])
    cm = "(" + ','.join(column) + ")"
    vl = "(" + ','.join(content) + ")"
    sql = "INSERT INTO {} {} VALUES {} ".format(table, cm, vl)
    return sql
