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
from pot_libs.mysql_util.mysql_util import MysqlUtil
from unify_api.constants import SDU_ALARM_LIST
import pendulum
from unify_api.utils.time_format import CST
async def get_user_product_auth(user_id):
sql = "SELECT * from user_product_auth where user_id=%s and product=4"
async with MysqlUtil() as conn:
return await conn.fetchone(sql, args=(user_id,))
async def get_basic_info_by_mtid(mtid, cid):
sql = "select s.room_name,s.storey_name,m.meter_no,m.sid,m.latitude," \
"m.longitude from storey_room_map s left join point p " \
"on p.pid=s.point_id LEFT JOIN monitor m on m.mtid=p.mtid " \
"where m.mtid=%s and m.demolished=0 and s.cid =%s"
async with MysqlUtil() as conn:
return await conn.fetchone(sql, args=(mtid, cid))
async def monitor_point_company(cids):
sql = "SELECT c.cid,c.address,c.fullname,p.pid,m.mtid,s.storey_name," \
"s.room_name,m.sid FROM `company` c INNER JOIN monitor m " \
"on c.cid=m.cid INNER JOIN point p on m.mtid=p.mtid " \
"INNER JOIN storey_room_map s on s.point_id=p.pid where c.cid in %s"
async with MysqlUtil() as conn:
return await conn.fetchall(sql, args=(cids,))
async def load_lg_sdu_events(cid, pg_num, pg_size, importance):
cond_lst = [f"cid={cid}", f"event_type in {tuple(SDU_ALARM_LIST)}"]
if len(importance) > 0:
importance = str(tuple(importance)).replace(',)', ')')
cond_lst.append(f"importance in {importance}")
time_format = "%Y-%m-%d %H:%M:%S"
end_date = str(pendulum.now(tz=CST).strftime(time_format))
cond_lst.append(f"event_datetime < '{end_date}'")
cond_str = " AND ".join(cond_lst)
async with MysqlUtil() as conn:
sql = f"select count(*) cnt from point_1min_event WHERE {cond_str};"
total = await conn.fetchone(sql)
total_count = total.get("cnt", 0)
if total_count <= 0:
return 0, []
sql = f"select * from point_1min_event WHERE {cond_str} " \
f"order by event_datetime desc limit %s,%s;"
data = await conn.fetchall(sql, args=((pg_num - 1) * pg_size, pg_size))
return total_count, data