data_es_dao.py 5.07 KB
Newer Older
lcn's avatar
lcn committed
1
from pot_libs.settings import SETTING
wang.wenrong's avatar
wang.wenrong committed
2 3
from unify_api.modules.anshiu.dao.fine_monitor_dao import get_mtid_by_pid_dao, \
    get_sid_by_mtid_dao, get_mtids_by_pids_dao
lcn's avatar
lcn committed
4 5
from unify_api.utils.common_utils import make_tdengine_data_as_list
from unify_api.utils.taos_new import get_td_engine_data
lcn's avatar
lcn committed
6 7 8
from pot_libs.mysql_util.mysql_util import MysqlUtil


lcn's avatar
lcn committed
9 10
async def query_point_1min_index(date_start, date_end,
                                 mtid, fields=None):
lcn's avatar
lcn committed
11
    """point点某一天1440个点数据"""
lcn's avatar
lcn committed
12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
    if fields:
        fields.insert(0, 'ts')
        fields_str = ",".join(fields)
    else:
        fields_str = "*"
    db = "db_electric"
    url = f"{SETTING.stb_url}{db}"
    table_name = "mt%s_ele" % mtid
    sql = f"select {fields_str} from {table_name} where ts >= '{date_start}'" \
          f" and ts <= '{date_end}' order by ts asc"
    is_succ, tdengine_data = await get_td_engine_data(url, sql)
    if not is_succ:
        return []
    results = make_tdengine_data_as_list(tdengine_data)
    return results


async def query_location_1min_index(date_start, date_end, mtid,
                                    fields=None):
lcn's avatar
lcn committed
31
    """location点某一天1440*n个点数据"""
lcn's avatar
lcn committed
32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
    if fields:
        fields.insert(0, 'ts')
        fields_str = ",".join(fields)
    else:
        fields_str = "*"
    db = "db_adio"
    url = f"{SETTING.stb_url}{db}"
    table_name = "mt%s_adi" % mtid
    sql = f"select {fields_str} from {table_name} where ts >= '{date_start}'" \
          f" and ts <= '{date_end}' order by ts asc"
    is_succ, tdengine_data = await get_td_engine_data(url, sql)
    if not is_succ:
        return []
    results = make_tdengine_data_as_list(tdengine_data)
    return results
lcn's avatar
lcn committed
47 48


lcn's avatar
lcn committed
49 50 51
async def get_search_scope(cid, pid, start, end, limit, offset):
    where = f" event_mode='scope'"
    args = ()
lcn's avatar
lcn committed
52
    if cid:
lcn's avatar
lcn committed
53 54 55 56
        where += f" and cid in %s"
        args = (cid,)
    if pid:
        where += f" and pid = {pid}"
lcn's avatar
lcn committed
57
    if start and end:
lcn's avatar
lcn committed
58 59 60 61 62
        where += f" and event_datetime >= '{start}' and event_datetime <=" \
                 f"'{end}' "
    sql = f"select * from point_1min_event where {where} " \
          f"ORDER BY event_datetime desc limit {limit} offset {offset}"
    total_sql = f"select count(*) count from point_1min_event where {where}"
lcn's avatar
lcn committed
63
    async with MysqlUtil() as conn:
lcn's avatar
lcn committed
64 65 66
        data = await conn.fetchall(sql, args=args) or []
        total = await conn.fetch_value(total_sql, args=args) or 0
    return data, total
lcn's avatar
lcn committed
67

wang.wenrong's avatar
wang.wenrong committed
68

lcn's avatar
lcn committed
69
async def query_search_scope(cid, pid, page_num, page_size,
wang.wenrong's avatar
wang.wenrong committed
70 71 72 73 74
                             start_time, end_time, scope_g):
    """
    查询录波列表
    """
    if len(pid) > 1:
lcn's avatar
lcn committed
75
        
wang.wenrong's avatar
wang.wenrong committed
76
        mtid = await get_mtids_by_pids_dao(pid)
lcn's avatar
lcn committed
77
    
wang.wenrong's avatar
wang.wenrong committed
78
    else:
wang.wenrong's avatar
wang.wenrong committed
79
        mtid = await get_mtid_by_pid_dao(pid)
lcn's avatar
lcn committed
80
    
wang.wenrong's avatar
wang.wenrong committed
81
    where = ""
wang.wenrong's avatar
wang.wenrong committed
82 83
    if cid:
        where += f" and pe.cid={cid} "
wang.wenrong's avatar
wang.wenrong committed
84
    if start_time:
wang.wenrong's avatar
wang.wenrong committed
85
        where += f" and pt.event_datetime >= '{start_time}' "
wang.wenrong's avatar
wang.wenrong committed
86
    if end_time:
wang.wenrong's avatar
wang.wenrong committed
87
        where += f" and pe.create_time <= '{end_time}' "
wang.wenrong's avatar
wang.wenrong committed
88
    if mtid:
wang.wenrong's avatar
wang.wenrong committed
89 90 91 92 93 94 95 96 97
        if len(mtid) == 1:
            where += f" and pe.mtid = {mtid['mtid']} "
        else:
            where += f" and pe.mtid in {tuple(mtid)} "
    if scope_g:
        if len(scope_g) == 1:
            where += f" AND pe.scope_g =  {scope_g[0]}  "
        else:
            where += f" AND pe.scope_g in  {tuple(scope_g)}  "
lcn's avatar
lcn committed
98
    
wang.wenrong's avatar
wang.wenrong committed
99 100 101
    sql = f"""
                SELECT
                    pt.event_id doc_id,
wang.wenrong's avatar
wang.wenrong committed
102
                    DATE_FORMAT(pt.event_datetime, '%Y-%m-%d %H:%i:%s') check_dt,
wang.wenrong's avatar
wang.wenrong committed
103 104 105 106 107 108 109 110
                    pt.`name` point,
                    pt.message message,
                    pe.scope_g scope_type
                FROM
                    point_1min_event pt
                    LEFT JOIN point_1min_scope pe ON pt.mtid = pe.mtid 
                    AND pe.create_time = pt.event_datetime 
                WHERE
wang.wenrong's avatar
wang.wenrong committed
111
                    pt.event_mode = 'scope'
wang.wenrong's avatar
wang.wenrong committed
112 113 114
                    {where}
                ORDER BY
                    pe.create_time DESC 
wang.wenrong's avatar
wang.wenrong committed
115
                    LIMIT  {page_num} , {page_size} """
lcn's avatar
lcn committed
116
    
wang.wenrong's avatar
wang.wenrong committed
117 118 119 120 121 122 123 124 125 126 127
    total_sql = f"""
                SELECT
                    count(*) total
                FROM
                    point_1min_event pt
                    LEFT JOIN point_1min_scope pe ON pt.mtid = pe.mtid 
                    AND pe.create_time = pt.event_datetime 
                WHERE
                    pt.event_mode = 'scope'
                    {where}
            """
wang.wenrong's avatar
wang.wenrong committed
128
    async with MysqlUtil() as conn:
lcn's avatar
lcn committed
129
        
wang.wenrong's avatar
wang.wenrong committed
130
        data = await conn.fetchall(sql, )
wang.wenrong's avatar
wang.wenrong committed
131
        total = await conn.fetchone(total_sql)
lcn's avatar
lcn committed
132
    
wang.wenrong's avatar
wang.wenrong committed
133
    return data, total
lcn's avatar
lcn committed
134 135


lcn's avatar
lcn committed
136 137
async def get_scope_pids(pids, start, end, limit=20, offset=0):
    where_str = f"event_mode='scope' and pid in {pids}"
lcn's avatar
lcn committed
138
    if start and end:
lcn's avatar
lcn committed
139 140 141 142 143
        where_str += f" and event_datetime>={start} and event_datetime<={end}"
    sql = f"select * from point_1min_event where {where_str}  " \
          f"ORDER BY event_datetime desc limit {limit} offset {offset}"
    total_sql = f"select count(*) count from point_1min_event where" \
                f" {where_str}"
lcn's avatar
lcn committed
144
    async with MysqlUtil() as conn:
lcn's avatar
lcn committed
145 146 147
        data = await conn.fetchall(sql) or []
        total = await conn.fetch_value(total_sql) or 0
    return data, total