from pot_libs.logger import log
from pot_libs.mysql_util.mysql_util import MysqlUtil
from pot_libs.settings import SETTING


async def order_num_is_having(order_num):
    """判断工单号是否存在"""
    sql = "select id from data_order_record where order_num = %s"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchone(sql, args=(order_num,))
    return data


async def update_order_userid(id, user_id, state):
    """接单 """
    sql = f"UPDATE data_order_record " \
          f"set handle_user_id={user_id}, state={state} " \
          f"where id={id}"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        await conn.execute(sql)
        log.info(sql)


async def insert_order_detail(id, op_record, user_id, explain=None):
    """工单详情表  插入数据"""
    sql = """INSERT INTO `data_order_details` (`order_record_id`, `op_record`,
       `handle_user_id`, `explain`) VALUES ( %s, %s, %s, %s); """
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        await conn.execute(sql, args=(id, op_record, user_id, explain))
        log.info(sql % (id, op_record, user_id, explain))


async def get_detail_id(id, op_record, user_id):
    sql = "select id from data_order_details where order_record_id=%s and " \
          "op_record=%s and handle_user_id=%s order by id desc"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchone(sql, args=(id, op_record, user_id))
    return data


async def select_order_detail(id, op_record):
    sql = """select id from  `data_order_details` where order_record_id=%s 
    and op_record=%s order by id desc"""
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchone(sql, args=(id, op_record))
    return data


async def flow_order_user_dao(user_id):
    """转单 获取用户"""
    sql = "SELECT user_id, real_name as user_name FROM `user` " \
          "where zhiweiu_auth=2 and user_id != %s"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=(user_id,))
    return data


async def search_all_user_dao():
    """搜索 所有用户"""
    sql = "SELECT user_id, real_name as user_name FROM `user` " \
          "where zhiweiu_auth in (1, 2)"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql)
    return data


async def search_all_handle_user_dao():
    """搜索 所有运维用户"""
    sql = "SELECT user_id, real_name as user_name FROM `user` " \
          "where zhiweiu_auth = 2"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql)
    return data


async def sid_to_order_dao(sid):
    """录入工单  传入硬件编号sid 返回平台 客户cid  pid监测点"""
    sql = "select c.product as prod_id,c.cid,c.shortname,m.sid,p.pid,p.name " \
          "from monitor m LEFT JOIN company c on c.cid =m.cid " \
          "LEFT JOIN point p on m.mtid=p.mtid where m.sid=%s and " \
          "m.demolished=0"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=(sid,))
    return data


async def cid_to_sid_dao(pid):
    """
    录入工单
    根据监测点pid  返回硬件编号sid
    """
    sql = f"SELECT m.sid FROM `point` p LEFT JOIN monitor m " \
          f"on p.mtid=m.mtid WHERE p.pid={pid} and m.demolished=0"
    async with MysqlUtil() as conn:
        data = await conn.fetchone(sql)
    return data


async def sid_is_pid(sid, pid):
    """
    录入工单 同时传入sid和pid  判断是否是同一条数据
    """
    sql = "SELECT m.mtid FROM `monitor` m LEFT JOIN point p " \
          "on p.mtid=m.mtid where m.sid = %s and p.pid = %s and m.demolished=0"
    async with MysqlUtil() as conn:
        data = await conn.fetchone(sql, args=(sid, pid))
    return data


async def update_order_state_dao(id, state):
    """修改工单状态 """
    sql = f"UPDATE data_order_record set state={state} where id={id}"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        await conn.execute(sql)
        log.info(sql)


async def update_order_by_id(conn_sql, id):
    """ 修改工单 """
    sql = f"UPDATE data_order_record set {conn_sql} where id={id}"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        await conn.execute(sql)
        log.info(sql)


async def get_order_state_by_id(id):
    """ 查询工单 """
    sql = f"select * from data_order_record where id={id}"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchone(sql)
    return data


async def get_monitor_by_pid(cid, pid):
    """根据cid和pid 查找mtid 和 monitor_name"""
    sql = "SELECT mtid, name FROM `point` where cid=%s and pid=%s"
    async with MysqlUtil() as conn:
        data = await conn.fetchone(sql, args=(cid, pid))
    return data


async def get_monitor_by_sid(sid):
    """根据sid 查找mtid 和 monitor_name"""
    sql = "SELECT mtid, name FROM `monitor` where sid=%s and demolished=0"
    async with MysqlUtil() as conn:
        data = await conn.fetchone(sql, args=(sid,))
    return data


async def select_cname_by_cid(cid):
    """根据平台选择客户 """
    sql = f"select shortname,product from company where cid = %s"
    async with MysqlUtil() as conn:
        data = await conn.fetchone(sql, args=(cid,))
    return data


async def order_detail_by_id(id):
    """工单详情"""
    sql = f"SELECT id,mtid,order_num,check_dt, prod_id,sid,cid,compy_name," \
          f"w_type,handle_user_id,warn_info,monitor_name,create_user_id " \
          f"FROM `data_order_record` WHERE id=%s"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchone(sql, args=(id,))
    return data


async def order_detail_flow_by_id(id):
    """工单详情 流程"""
    sql = "SELECT id,op_time,op_record,`explain`,handle_user_id " \
          "FROM `data_order_details` where order_record_id=%s order by " \
          "op_time desc"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchall(sql, args=(id,))
    return data


async def get_info_by_order_num(order_num):
    """根据data_order_details id查询信息"""
    sql = "SELECT id,cid,prod_id FROM `data_order_record` where order_num=%s "
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchone(sql, args=(order_num,))
    return data


async def get_info_by_details_id(id):
    sql = "SELECT r.cid, r.prod_id FROM `data_order_details` d " \
          "left join data_order_record r on d.order_record_id=r.id " \
          "where d.id=%s"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchone(sql, args=(id,))
    return data


async def insert_order_files(doc_name, doc_url):
    """附件上传"""
    sql = "INSERT INTO `order_files` (`doc_name`, `doc_url`) VALUES ( %s, %s)"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        await conn.execute(sql, args=(doc_name, doc_url))
        log.info(sql % (doc_name, doc_url))


async def insert_one_order_files(order_id_name, order_id, order_files_id):
    """附件表插入数据"""
    sql = f"update `order_files`  set {order_id_name}=%s where id = %s"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        await conn.execute(sql, args=(order_id, order_files_id))
        log.info(sql % (order_id, order_files_id))


async def get_file_by_id(id):
    sql = "select id, doc_name, doc_url from order_files where id=%s"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchone(sql, args=(id,))
    return data


async def delte_order_file(id):
    sql = "DELETE from order_files where id=%s"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        await conn.execute(sql, args=(id,))
        log.info(sql % id)


async def get_file_by_record_id(name, order_record_id):
    sql = f"select id, doc_name, doc_url from order_files " \
          f"where {name}=%s"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchall(sql, args=(order_record_id,))
    return data


async def get_file_info_by_id(doc_id):
    sql = "select id, doc_url from order_files where id = %s"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchall(sql, args=(doc_id,))
    return data


async def search_fid_by_url(qs_url):
    """通过url找出文件id"""
    sql = "select id from order_files where doc_url in %s"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchall(sql, args=(qs_url,))
    return data


async def w_origin_dao():
    sql = "SELECT DISTINCT create_user_id FROM `data_order_record` " \
          "where w_origin=1 ;"
    async with MysqlUtil(db=SETTING.mysql_zhiwei_u_db) as conn:
        data = await conn.fetchall(sql)
    return data
