electric_dao.py 3.19 KB
Newer Older
lcn's avatar
lcn committed
1
from pot_libs.mysql_util.mysql_util import MysqlUtil
ZZH's avatar
ZZH committed
2
from pot_libs.settings import SETTING
lcn's avatar
lcn committed
3 4 5 6


async def monitor_point_join_by_points(points):
    """monitor和point关联"""
lcn's avatar
lcn committed
7
    sql = "SELECT m.mtid,p.ctnum,m.name, m.m_type, p.pid,p.cid " \
lcn's avatar
lcn committed
8
          "FROM monitor m inner join point p on m.mtid = p.mtid " \
lcn's avatar
lcn committed
9
          "WHERE p.pid in %s and m.demolished = 0 order by field(p.pid,{})". \
lcn's avatar
lcn committed
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
        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:
lcn's avatar
lcn committed
30
        electric_datas = await conn.fetchall(sql, args=(pid,))
lcn's avatar
lcn committed
31 32 33 34 35 36 37 38
    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:
lcn's avatar
lcn committed
39
        datas = await conn.fetchall(sql, args=(pid,))
lcn's avatar
lcn committed
40 41 42 43 44 45 46 47 48 49 50 51
    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
wang.wenrong's avatar
wang.wenrong committed
52 53 54


async def get_elec_mtid_sid_by_cid(cid):
lcn's avatar
lcn committed
55 56 57 58 59 60
    if isinstance(cid, tuple):
        cid_tuple = cid
    elif isinstance(cid, list):
        cid_tuple = tuple(cid)
    else:
        cid_tuple = (cid,)
wang.wenrong's avatar
wang.wenrong committed
61 62 63 64 65 66 67 68
    sql = (
        f"""
        SELECT
            mtid,
            sid
        FROM
            monitor
        WHERE
ZZH's avatar
ZZH committed
69
            cid in %s AND demolished=0;
wang.wenrong's avatar
wang.wenrong committed
70 71 72
        """
    )
    async with MysqlUtil() as conn:
lcn's avatar
lcn committed
73
        datas = await conn.fetchall(sql, args=(cid_tuple,))
wang.wenrong's avatar
wang.wenrong committed
74
    return datas if datas else []
ZZH's avatar
ZZH committed
75 76 77


async def load_add_to_compy_ids(cid):
lcn's avatar
lcn committed
78
    symbol = "in" if isinstance(cid, (list, tuple)) else "="
ZZH's avatar
ZZH committed
79 80 81
    db = SETTING.mysql_db
    sql = f"SELECT monitor.mtid, monitor.sid FROM {db}.monitor " \
          f"INNER JOIN {db}.point ON point.mtid=monitor.mtid " \
lcn's avatar
lcn committed
82
          f"WHERE monitor.cid {symbol} %s AND point.add_to_company=1 " \
ZZH's avatar
ZZH committed
83 84 85 86
          f"AND monitor.demolished=0;"
    async with MysqlUtil() as conn:
        ids = await conn.fetchall(sql, (cid,))
        return ids if ids else []