1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
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