from pot_libs.settings import SETTING 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 from unify_api.utils.common_utils import make_tdengine_data_as_list from unify_api.utils.taos_new import get_td_engine_data from pot_libs.mysql_util.mysql_util import MysqlUtil async def query_point_1min_index(date_start, date_end, mtid, fields=None): """point点某一天1440个点数据""" 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): """location点某一天1440*n个点数据""" 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 async def get_search_scope(cid, pid, start, end, limit, offset): where = f" event_mode='scope'" args = () if cid: where += f" and cid in %s" args = (cid,) if pid: where += f" and pid = {pid}" if start and end: 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}" async with MysqlUtil() as conn: data = await conn.fetchall(sql, args=args) or [] total = await conn.fetch_value(total_sql, args=args) or 0 return data, total async def query_search_scope(cid, pid, page_num, page_size, start_time, end_time, scope_g): """ 查询录波列表 """ if len(pid) > 1: mtid = await get_mtids_by_pids_dao(pid) else: mtid = await get_mtid_by_pid_dao(pid) where = "" if cid: where += f" and pe.cid={cid} " if start_time: where += f" and pt.event_datetime >= '{start_time}' " if end_time: where += f" and pe.create_time <= '{end_time}' " if mtid: 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)} " sql = f""" SELECT pt.event_id doc_id, DATE_FORMAT(pt.event_datetime, '%Y-%m-%d %H:%i:%s') check_dt, 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 pt.event_mode = 'scope' {where} ORDER BY pe.create_time DESC LIMIT {page_num} , {page_size} """ 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} """ async with MysqlUtil() as conn: data = await conn.fetchall(sql, ) total = await conn.fetchone(total_sql) return data, total async def get_scope_pids(pids, start, end, limit=20, offset=0): where_str = f"event_mode='scope' and pid in {pids}" if start and end: 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}" async with MysqlUtil() as conn: data = await conn.fetchall(sql) or [] total = await conn.fetch_value(total_sql) or 0 return data, total