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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
from pot_libs.mysql_util.mysql_util import MysqlUtil
from pot_libs.logger import log
async def company_by_cids(cids):
"""根据cids查询company信息"""
sql = "SELECT * from company where cid in %s"
async with MysqlUtil() as conn:
company_list = await conn.fetchall(sql, args=(tuple(cids),))
return company_list
async def point_by_points(point_list):
sql = "SELECT * from point where pid in %s"
async with MysqlUtil() as conn:
point_info_list = await conn.fetchall(sql, args=(tuple(point_list),))
return point_info_list
async def point_by_pid_mtid(mtid, pid):
sql = "SELECT * from point where pid = %s and mtid = %s"
async with MysqlUtil() as conn:
point_dic = await conn.fetchone(sql, args=(pid, mtid))
return point_dic
async def storey_by_cid(cid):
"""根据cids查询楼层信息"""
sql = "SELECT storey_id, storey_name from storey_room_map " \
"where cid = %s order by storey_id "
async with MysqlUtil() as conn:
storey_list = await conn.fetchall(sql, args=(cid,))
return storey_list
async def query_points_by_storey(cid, storeys):
"""根据storey_id查询point_id和room_name"""
sql = "SELECT s.storey_id,s.storey_name,s.point_id,s.room_name,m.mtid," \
"p.ctnum,p.cid 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 s.storey_id in %s and s.cid=%s and m.demolished=0 " \
"order by s.storey_id, s.room_name"
async with MysqlUtil() as conn:
points = await conn.fetchall(sql, args=(tuple(storeys), cid))
return points
async def points_by_cid(cids):
"""根据cid查询points"""
sql = "SELECT * FROM point WHERE cid in %s"
async with MysqlUtil() as conn:
points = await conn.fetchall(sql, args=(tuple(cids),))
return points
async def points_monitor_by_cid(cids):
sql = "SELECT p.pid, m.* FROM `monitor` m LEFT JOIN point p " \
"on m.mtid=p.mtid where m.cid in %s and m.demolished=0"
async with MysqlUtil() as conn:
points = await conn.fetchall(sql, args=(tuple(cids),))
return points
async def get_point_monitor_dao(pid):
sql = "SELECT m.meter_no,m.mtid,m.sid," \
"p.ctr,p.ptr,p.ctnum,p.vc,p.tc,p.imax " \
"FROM `point` p INNER JOIN monitor m on m.mtid=p.mtid " \
"where p.pid=%s and m.demolished = 0"
async with MysqlUtil() as conn:
datas = await conn.fetchone(sql, args=(pid,))
return datas
async def get_location_monitor_dao(lid):
sql = "SELECT l.lid,l.ad_field,m.sid FROM `location` l " \
"INNER JOIN monitor m on l.mtid=m.mtid where l.lid=%s limit 1"
async with MysqlUtil() as conn:
datas = await conn.fetchone(sql, args=(lid,))
return datas
async def sid_by_pid(pid):
"""根据pid查询sid"""
sql = "select sid,meter_no from monitor m left join point p on m.mtid = " \
"p.mtid where p.pid=%s "
async with MysqlUtil() as conn:
result = await conn.fetchone(sql, args=(pid,))
return result
async def meter_by_mids(mids):
"""根据mids查询meter"""
sql = "select * from meter where mid in %s"
async with MysqlUtil() as conn:
meter_list = await conn.fetchall(sql, args=(tuple(mids),))
return meter_list
async def item_by_mitd_dao(mtids):
sql = "select mtid, meter_no from monitor where mtid in %s"
async with MysqlUtil() as conn:
datas = await conn.fetchall(sql, args=(mtids,))
return datas
async def load_compy_storey_points(cid, pg_num, pg_size):
"""monitor和point和storey联合查询, 分页"""
sql = "SELECT monitor.cid, c.address, point.name, point.create_time, " \
"monitor.sid, monitor.meter_no, srm.room_name, srm.storey_name, " \
"point.mtid, point.pid " \
"FROM monitor " \
"inner join point on monitor.mtid = point.mtid " \
"inner join storey_room_map srm on point.pid = srm.point_id " \
"left join company c on c.cid = monitor.cid " \
"WHERE monitor.cid in %s and monitor.demolished = 0 " \
"order by point.pid limit %s, %s"
async with MysqlUtil() as conn:
return await conn.fetchall(sql, (cid, (pg_num - 1) * pg_size, pg_size))
async def meter_param_by_mid(mtid):
"""根据mid查询meter, 参数固定不要再增加"""
sql = "select ctr,ptr,ctnum,vc,tc,imax from point " \
"where mtid=%s order by create_time desc limit 1"
async with MysqlUtil() as conn:
meter_param_dic = await conn.fetchone(sql, args=(mtid,))
return meter_param_dic
async def tsp_by_cid(cid):
"""根据cid查询points"""
sql = "SELECT tsp_id, name FROM tsp WHERE cid = %s " \
"order by name"
async with MysqlUtil() as conn:
tsp_list = await conn.fetchall(sql, args=(cid,))
return tsp_list
async def monitor_by_cid(cid):
"""根据cid查询monitor"""
sql = "SELECT * FROM monitor WHERE cid = %s and demolished = 0"
async with MysqlUtil() as conn:
monitor_list = await conn.fetchall(sql, args=(cid,))
return monitor_list
async def monitor_point_join(cid):
"""monitor和point关联"""
sql = "SELECT m.mtid, p.pid, p.name, p.add_to_company FROM monitor m " \
"inner join point p on m.mtid = p.mtid " \
"WHERE m.cid = %s and m.demolished = 0"
async with MysqlUtil() as conn:
monitor_point_list = await conn.fetchall(sql, args=(cid,))
return monitor_point_list
async def monitor_location_join(cid):
"""monitor和location关联"""
sql = "SELECT m.mtid, l.lid, l.item FROM monitor m " \
"inner join location l on m.mtid = l.mtid " \
"WHERE m.cid = %s and m.demolished = 0"
async with MysqlUtil() as conn:
monitor_location_list = await conn.fetchall(sql, args=(cid,))
return monitor_location_list
async def monitor_point_storey_join(cid, page_num, page_size):
"""monitor和point和storey联合查询, 分页"""
sql = "SELECT srm.mtid,point.pid,point.name,point.create_time, " \
"monitor.sid, srm.room_name, srm.storey_name FROM monitor " \
"inner join point on monitor.mtid=point.mtid inner join " \
"storey_room_map srm " \
"on point.pid = srm.point_id WHERE monitor.cid = %s " \
"and monitor.demolished = 0 order by point.pid limit %s, %s"
async with MysqlUtil() as conn:
monitor_point_storey_list = await conn.fetchall(sql, args=(
cid, (page_num - 1) * page_size, page_size))
return monitor_point_storey_list
async def company_model_by_cid(cid):
"""根据cid查询company_model信息"""
sql = "SELECT * from company_model where cid = %s"
async with MysqlUtil() as conn:
company_model_dic = await conn.fetchone(sql, args=(cid,))
return company_model_dic
async def inline_zdu_all_by_cid(cid):
"""根据cid查询inline_zdu信息"""
sql = "SELECT * from inline where cid = %s"
async with MysqlUtil() as conn:
inline_zdu_dic = await conn.fetchall(sql, args=(cid,))
return inline_zdu_dic
async def water_by_cid(cid):
sql = "SELECT * FROM water WHERE cid = %s"
async with MysqlUtil() as conn:
water_list = await conn.fetchall(sql, args=(cid,))
return water_list
async def storey_wp_by_cid(cid):
"""根据cids查询楼层, 雾炮信息"""
sql = "SELECT * from storey_room_map where cid = %s and storey_name = '雾炮'"
async with MysqlUtil() as conn:
storey_list = await conn.fetchall(sql, args=(cid,))
return storey_list
async def storey_pl_by_cid(cid):
"""根据cids查询楼层, 喷淋信息"""
sql = "SELECT * from storey_room_map where cid = %s and storey_name = '喷淋'"
async with MysqlUtil() as conn:
storey_list = await conn.fetchall(sql, args=(cid,))
return storey_list
async def company_extend_by_cid(cid):
"""根据cids查询company信息"""
sql = "SELECT * from company_extend where cid = %s"
async with MysqlUtil() as conn:
company_list = await conn.fetchall(sql, args=(cid,))
return company_list
async def company_by_cid(cids):
"""根据cids查询company"""
sql = "SELECT create_time FROM `company` " \
"where cid in %s ORDER BY create_time;"
async with MysqlUtil() as conn:
monitor_list = await conn.fetchall(sql, args=(cids,))
return monitor_list
async def detection_point_by_cid(cids):
"""根据cids查询检测点总数"""
sql = "SELECT count(mtid) as point_num FROM `monitor` " \
"where cid in %s and demolished=0;"
async with MysqlUtil() as conn:
detection_point_count = await conn.fetchall(sql, args=(tuple(cids),))
return detection_point_count
async def monitor_page_by_cid(cids, page_num, page_size):
"""根据cids查询项目名称/总数以及分页"""
sql = "SELECT a.cid, b.create_time,b.shortname name, count(a.cid) " \
"count_num FROM monitor a " \
"left join company b on a.cid=b.cid " \
"where a.cid in %s and a.demolished = 0 " \
"GROUP BY a.cid order by b.create_time desc limit %s, %s;"
async with MysqlUtil() as conn:
info_list = await conn.fetchall(sql=sql, args=(
tuple(cids), (page_num - 1) * page_size, page_size))
return info_list
async def start_time_by_cids(cids):
"""根据cid查询启用时间"""
sql = "SELECT cid,create_time FROM `company` where cid in %s and is_show=1"
async with MysqlUtil() as conn:
start_times = await conn.fetchall(sql=sql, args=(tuple(cids),))
return start_times
async def tcs_runtime_by_cids(cids):
sql = """SELECT cid, sum(tc_runtime) tcs FROM `inline`
where cid in %s GROUP BY cid"""
async with MysqlUtil() as conn:
tcs_counts = await conn.fetchall(sql=sql, args=(tuple(cids),))
return tcs_counts
async def user_by_user_id(user_id):
sql = "SELECT * FROM user where user_id = %s"
async with MysqlUtil() as conn:
user_dic = await conn.fetchone(sql=sql, args=(user_id,))
return user_dic
async def user_by_phone_number(phone):
sql = "SELECT * FROM user where phone_number = %s and is_delete=0"
async with MysqlUtil() as conn:
user_dic = await conn.fetchone(sql=sql, args=(phone,))
return user_dic
async def meter_by_sid(sid):
sql = "SELECT mid from meter WHERE sid=%s ORDER BY create_time " \
"DESC limit 1;"
async with MysqlUtil() as conn:
meter_dic = await conn.fetchone(sql=sql, args=(sid,))
return meter_dic
async def get_all_username():
sql = "SELECT user_id, real_name as user_name, zhiweiu_auth as role " \
"FROM `user`"
async with MysqlUtil() as conn:
datas = await conn.fetchall(sql=sql)
username_info = {}
for data in datas:
username_info[data["user_id"]] = {
"user_name": data["user_name"],
"role": data["role"]
}
return username_info
async def monitor_by_mtid(mtid):
sql = "select * from monitor where mtid = %s "
async with MysqlUtil() as conn:
monitor_dic = await conn.fetchone(sql, args=(mtid,))
return monitor_dic
async def search_iccid(sid):
sql = "select iccid, sid from sid_iccid where sid = %s "
async with MysqlUtil() as conn:
iccid = await conn.fetchone(sql, args=(sid,))
return iccid
async def save_iccid(sid, iccid):
sql = "INSERT INTO `sid_iccid` (`sid`, `iccid`) VALUES ( %s, %s) "
async with MysqlUtil() as conn:
await conn.execute(sql, args=(sid, iccid))
log.info(sql % (sid, iccid))
async def get_fields_by_mtid(mtid, table_name="monitor", fields="m_type"):
"""
通过mtid获取设备表id
:param mtid:
:param table_name:
:param fields:
:return:
"""
sql = f"select {fields} from {table_name} where mtid = %s"
async with MysqlUtil() as conn:
result = await conn.fetchone(sql, (mtid,))
return result
async def load_point_pttl_mean(start, end, pid):
sql = f"SELECT pttl_mean, create_time FROM `point_15min_electric` " \
f"where pid=%s and create_time BETWEEN '{start}' and '{end}'"
async with MysqlUtil() as conn:
datas = await conn.fetchall(sql, args=(pid,))
return datas