# -*- coding: utf_8 -*-
# 表名: hosts
# 作者: 陈磊
# 时间: 2019-11-15


from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, VARCHAR, SMALLINT, DATETIME, DECIMAL, BigInteger, FLOAT, NUMERIC, TEXT
from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound
from sqlalchemy import or_

from WorkUtils.UtilsLog import UtilsLog
from WorkUtils.UtilsDataBase import UtilsDataBase

import time

Base = declarative_base()


class Hosts(Base):
    """
    主机信息表
    """
    __tablename__ = "hosts"

    hostid = Column(Integer, primary_key=True)
    host_name = Column(VARCHAR(128), comment="主机名")
    asset_code = Column(VARCHAR(128), comment="资产编号")
    manage_ip = Column(VARCHAR(32), comment="管理IP")
    departmentid = Column(Integer, comment="资产部门")
    manage_level = Column(Integer, comment="资产管理等级")
    host_type = Column(Integer, comment="主机类型")
    parent_host = Column(VARCHAR(128), comment="所属主机")
    available = Column(Integer, comment="可用性")
    monitor_status = Column(Integer, comment="监控状态")
    iplist = Column(VARCHAR(255), comment="IP列表 分号隔开")
    dns = Column(VARCHAR(255), comment="域名")
    monitor_interface = Column(VARCHAR(255), comment="监控接口 分号隔开")
    monitor_type = Column(Integer, comment="监控方式（1：ip 2：dns）")
    business_ids = Column(VARCHAR(255), comment="业务id（对应于zabbix系统的分组）")
    host_type_tree = Column(VARCHAR(255), comment="类型树")
    business_tree = Column(VARCHAR(255), comment="业务树")
    port = Column(VARCHAR(30), comment="服务端口号")
    ops_person = Column(Integer, comment="运维人员")
    factory_id = Column(VARCHAR(255), comment="厂商")
    model = Column(VARCHAR(255), comment="型号")
    version = Column(VARCHAR(50), comment="版本号")
    serialnumber = Column(VARCHAR(255), comment="序列号")
    description = Column(VARCHAR(255), comment="描述")
    snmp_community = Column(VARCHAR(255), comment="snmp社区名（对应于zabbix系统里的snmp宏里SNMP_COMMUNITY模板的值）")
    ipmi_authtype = Column(Integer, comment="ipmi认证算法（0：无 1：MD2 2：MD5 3：Straight 4：OEM 5：RMCP+）")
    ipmi_privilege = Column(Integer, comment="ipmi优先权层级（1：回调 2：用户 3：操作者 4：管理者 5：OEM）")
    ipmi_username = Column(VARCHAR(50), comment="ipmi用户名")
    ipmi_password = Column(VARCHAR(50), comment="ipmi密码")
    create_time = Column(BigInteger, comment="创建时间")
    del_flag = Column(Integer, comment="0:存在；1：删除")
    discovered = Column(Integer, comment="0-非自动发现  1- 自动发现主机")
    update_time = Column(BigInteger, comment="修改时间")


class DataHosts(object):
    def __init__(self):
        self.log = UtilsLog()
        self.log.info(self.__class__)

        self.table = Hosts

    def select_all_from_allKeys(self, session, hostid=None, host_name=None, available=None, monitor_status=None):
        """
        :param session: 指针
        :param hostid:
        :param host_name:
        :param available:
        :param monitor_status:
        :return: 查询结果
        """
        self.log.debug("查询数据库:")
        try:
            base = UtilsDataBase()
            base.add_param(_key="hostid", value=hostid)
            base.add_param(_key="host_name", value=host_name)
            base.add_param(_key="available", value=available)
            base.add_param(_key="monitor_status", value=monitor_status)
            sql_rep = session.query(self.table).filter_by(**base.param).all()
            self.log.debug(sql_rep)
            session.close()
            return sql_rep
        except UtilsDataBase().errors as error:
            self.log.error("异常:")
            self.log.error(error)
            session.close()
            return error

    def delete_like_host_name(self, session, host_name):
        """
        :param session: 指针
        :param host_name:
        :return:
        """
        self.log.debug("查询数据库:")
        try:
            self.log.debug("删除相关测试数据")
            session.query(self.table).filter(self.table.host_name.like(str(host_name) + "%")).delete(synchronize_session=False)
            # session.delete(sql)
            session.commit()
            session.close()
            self.log.debug("删除成功")
            return True
        except UtilsDataBase().errors as error:
            self.log.error("异常:")
            self.log.error(error)
            session.close()
            return error

    def insert_one(self, session, host_name=None, asset_code=None, manage_ip=None, departmentid=None, manage_level=None,
                   host_type=None, parent_host=None, available=None, monitor_status=None, iplist=None, monitor_interface=None,
                   del_flag=None):
        """
        :param session: 指针
        :param host_name:
        :param asset_code:
        :param manage_ip:
        :param departmentid:
        :param manage_level:
        :param host_type:
        :param parent_host:
        :param available:
        :param monitor_status:
        :param iplist:
        :param monitor_interface:
        :param del_flag:
        :return:
        """
        self.log.debug("新增表数据:")
        try:
            base = UtilsDataBase()
            base.add_param(_key="host_name", value=host_name)
            base.add_param(_key="asset_code", value=asset_code)
            base.add_param(_key="manage_ip", value=manage_ip)
            base.add_param(_key="departmentid", value=departmentid)
            base.add_param(_key="manage_level", value=manage_level)
            base.add_param(_key="host_type", value=host_type)
            base.add_param(_key="parent_host", value=parent_host)
            base.add_param(_key="available", value=available)
            base.add_param(_key="monitor_status", value=monitor_status)
            base.add_param(_key="iplist", value=iplist)
            base.add_param(_key="monitor_interface", value=monitor_interface)
            base.add_param(_key="del_flag", value=del_flag)
            sql_rep = self.table(**base.param)

            session.add(sql_rep)
            session.commit()
            hostid = sql_rep.hostid
            self.log.debug("新增的hostid:%s" % hostid)
            session.close()
            return hostid
        except UtilsDataBase().errors as error:
            self.log.error("异常:")
            self.log.error(error)
            session.close()
            return error

    def select_all_like_host_name(self, session, host_name):
        """
        :param session: 指针
        :param host_name:
        :return: 查询结果
        """
        self.log.debug("查询数据库:")
        try:
            sql_rep = session.query(self.table).filter(self.table.host_name.like(str(host_name) + "%")).all()
            self.log.debug(sql_rep)
            session.close()
            return sql_rep
        except UtilsDataBase().errors as error:
            self.log.error("异常:")
            self.log.error(error)
            session.close()
            return error