import json 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 load_user_lang(user_id): async with MysqlUtil() as conn: sql = "SELECT lang FROM user WHERE user_id=%s;" try: return await conn.fetch_value(sql, (user_id,)) except Exception as e: log.info(f"error user_id:{user_id}, {e}") return None 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 async def load_monitor_names(mtids, lang): sql = f"SELECT mtid, {lang} FROM power_iot.monitor_name WHERE mtid IN %s;" async with MysqlUtil() as conn: return {r["mtid"]: r[lang] for r in await conn.fetchall(sql, (tuple(mtids),))} async def load_compy_name(cid, lang): shortname, fullname = "", "" sql = f"SELECT * FROM power_iot.company_name WHERE cid=%s;" async with MysqlUtil() as conn: rlt = await conn.fetchone(sql, (cid,)) if rlt: shortname = rlt.get(f"short_{lang}") fullname = rlt.get(f"full_{lang}") return shortname, fullname