common_dao.py 12.1 KB
Newer Older
lcn's avatar
lcn committed
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
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


36
async def query_points_by_storey(cid, storeys):
lcn's avatar
lcn committed
37 38
    """根据storey_id查询point_id和room_name"""
    sql = "SELECT s.storey_id,s.storey_name,s.point_id,s.room_name,m.mtid," \
lcn's avatar
lcn committed
39
          "p.ctnum,p.cid from storey_room_map s LEFT JOIN point p " \
lcn's avatar
lcn committed
40
          "on p.pid=s.point_id LEFT JOIN monitor m on m.mtid=p.mtid " \
41
          "where s.storey_id in %s and s.cid=%s and m.demolished=0 " \
lcn's avatar
lcn committed
42 43
          "order by s.storey_id, s.room_name"
    async with MysqlUtil() as conn:
44
        points = await conn.fetchall(sql, args=(tuple(storeys), cid))
lcn's avatar
lcn committed
45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
    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):
lcn's avatar
lcn committed
65
    sql = "SELECT m.meter_no,m.mtid,m.sid," \
lcn's avatar
lcn committed
66
          "p.ctr,p.ptr,p.ctnum,p.vc,p.tc,p.imax " \
lcn's avatar
lcn committed
67
          "FROM `point` p INNER JOIN monitor m on m.mtid=p.mtid " \
lcn's avatar
lcn committed
68
          "where p.pid=%s and m.demolished = 0"
lcn's avatar
lcn committed
69 70 71 72 73 74 75 76 77 78 79 80 81
    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


lcn's avatar
lcn committed
82 83 84 85 86 87 88 89 90
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


lcn's avatar
lcn committed
91 92 93 94 95 96 97 98 99 100 101
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:
wang.wenrong's avatar
wang.wenrong committed
102
        datas = await conn.fetchall(sql, args=(mtids,))
lcn's avatar
lcn committed
103 104 105
    return datas


ZZH's avatar
ZZH committed
106
async def load_compy_storey_points(cid, pg_num, pg_size):
lcn's avatar
lcn committed
107
    """monitor和point和storey联合查询, 分页"""
ZZH's avatar
ZZH committed
108 109
    sql = "SELECT monitor.cid, c.address, point.name, point.create_time, " \
          "monitor.sid, monitor.meter_no, srm.room_name, srm.storey_name, " \
ZZH's avatar
ZZH committed
110
          "point.mtid, point.pid " \
ZZH's avatar
ZZH committed
111 112 113 114 115 116 117 118
          "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))
lcn's avatar
lcn committed
119 120


121
async def meter_param_by_mid(mtid):
lcn's avatar
lcn committed
122
    """根据mid查询meter, 参数固定不要再增加"""
123 124
    sql = "select ctr,ptr,ctnum,vc,tc,imax from point " \
          "where mtid=%s order by create_time desc limit 1"
lcn's avatar
lcn committed
125
    async with MysqlUtil() as conn:
126
        meter_param_dic = await conn.fetchone(sql, args=(mtid,))
lcn's avatar
lcn committed
127 128 129 130 131
    return meter_param_dic


async def tsp_by_cid(cid):
    """根据cid查询points"""
wang.wenrong's avatar
wang.wenrong committed
132
    sql = "SELECT tsp_id, name FROM tsp WHERE cid = %s " \
lcn's avatar
lcn committed
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
          "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):
wang.wenrong's avatar
wang.wenrong committed
198
    sql = "SELECT * FROM water WHERE cid = %s"
lcn's avatar
lcn committed
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
    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查询项目名称/总数以及分页"""
lcn's avatar
lcn committed
248 249
    sql = "SELECT a.cid, b.create_time,b.shortname name, count(a.cid) " \
          "count_num FROM monitor a " \
lcn's avatar
lcn committed
250
          "left join company b on a.cid=b.cid " \
lcn's avatar
lcn committed
251 252
          "where a.cid in %s and a.demolished = 0 " \
          "GROUP BY a.cid order by  b.create_time desc limit %s, %s;"
lcn's avatar
lcn committed
253 254
    async with MysqlUtil() as conn:
        info_list = await conn.fetchall(sql=sql, args=(
wang.wenrong's avatar
wang.wenrong committed
255
            tuple(cids), (page_num - 1) * page_size, page_size))
lcn's avatar
lcn committed
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
    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:
wang.wenrong's avatar
wang.wenrong committed
314
        monitor_dic = await conn.fetchone(sql, args=(mtid,))
lcn's avatar
lcn committed
315 316 317 318 319 320
    return monitor_dic


async def search_iccid(sid):
    sql = "select iccid, sid from sid_iccid where sid = %s "
    async with MysqlUtil() as conn:
wang.wenrong's avatar
wang.wenrong committed
321
        iccid = await conn.fetchone(sql, args=(sid,))
lcn's avatar
lcn committed
322 323 324 325 326 327 328
    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))
wang.wenrong's avatar
wang.wenrong committed
329 330 331 332 333 334 335 336 337 338 339 340 341 342 343
        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
wang.wenrong's avatar
wang.wenrong committed
344 345


ZZH's avatar
ZZH committed
346
async def load_point_pttl_mean(start, end, pid):
wang.wenrong's avatar
wang.wenrong committed
347 348 349 350 351
    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