from pot_libs.mysql_util.mysql_util import MysqlUtil from pot_libs.settings import SETTING async def monitor_point_join_by_points(points): """monitor和point关联""" sql = "SELECT m.mtid,p.ctnum,m.name, m.m_type, p.pid,p.cid " \ "FROM monitor m inner join point p on m.mtid = p.mtid " \ "WHERE p.pid in %s and m.demolished = 0 order by field(p.pid,{})". \ format(str(points).replace("[", "").replace("]", "")) async with MysqlUtil() as conn: monitor_point_list = await conn.fetchall(sql, args=(tuple(points),)) return monitor_point_list async def monitor_point_join_points(points): """monitor和point关联""" sql = "SELECT m.mtid, m.name, m.m_type, m.sid, p.pid " \ "FROM monitor m inner join point p on m.mtid = p.mtid " \ "WHERE p.pid in %s and m.demolished = 0" async with MysqlUtil() as conn: monitor_point_list = await conn.fetchall(sql, args=(tuple(points),)) return monitor_point_list async def get_electric_datas_dao(table_name, pid, start, end): sql = f"SELECT * FROM {table_name} where pid=%s and create_time " \ f"BETWEEN '{start}' and '{end}' ORDER BY create_time desc" async with MysqlUtil() as conn: electric_datas = await conn.fetchall(sql, args=(pid,)) return electric_datas async def get_qual_history_dao(table_name, pid, start, end, date_format): sql = f"SELECT DATE_FORMAT(p.create_time, '{date_format}') date_time, " \ f"p.* FROM {table_name} p where p.pid=%s and p.create_time " \ f"BETWEEN '{start}' and '{end}' order by p.create_time" async with MysqlUtil() as conn: datas = await conn.fetchall(sql, args=(pid,)) return datas async def get_elec_history_dao(table_name, pid, start, end, date_format): sql = f"SELECT DATE_FORMAT(create_time, '{date_format}') date_time, " \ f"lf_mean, pttl_mean, qttl_mean, costtl_mean, ua_mean, ub_mean, " \ f"uc_mean, ia_mean, ib_mean, ic_mean, freq_mean, uab_mean, " \ f"ucb_mean FROM {table_name} where pid=%s and create_time " \ f"BETWEEN '{start}' and '{end}' order by create_time" async with MysqlUtil() as conn: datas = await conn.fetchall(sql, args=(pid,)) return datas async def get_elec_mtid_sid_by_cid(cid): if isinstance(cid, tuple): cid_tuple = cid elif isinstance(cid, list): cid_tuple = tuple(cid) else: cid_tuple = (cid,) sql = ( f""" SELECT mtid, sid FROM monitor WHERE cid in %s AND demolished=0; """ ) async with MysqlUtil() as conn: datas = await conn.fetchall(sql, args=(cid_tuple,)) return datas if datas else [] async def load_add_to_compy_ids(cid): symbol = "in" if isinstance(cid, (list, tuple)) else "=" db = SETTING.mysql_db sql = f"SELECT monitor.mtid, monitor.sid FROM {db}.monitor " \ f"INNER JOIN {db}.point ON point.mtid=monitor.mtid " \ f"WHERE monitor.cid {symbol} %s AND point.add_to_company=1 " \ f"AND monitor.demolished=0;" async with MysqlUtil() as conn: ids = await conn.fetchall(sql, (cid,)) return ids if ids else []