equip_management_pds.py 6.51 KB
Newer Older
wang.wenrong's avatar
wang.wenrong 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 36 37 38 39 40 41 42 43 44 45 46 47 48 49
from pot_libs.mysql_util.mysql_util import MysqlUtil
from unify_api.modules.product_info.procedures.hardware_pds import (
    get_user_hardware_info, hardware_statistics)


async def check_company_exist(company_id):
    '''
        判断工厂是否存在
    '''
    raw_sql = "select count(*) as company_count from company where cid = %s"
    async with MysqlUtil() as conn:
        company_count = await conn.fetchone(sql=raw_sql, args=(company_id))
    return company_count.get('company_count') > 0


async def equip_management_list(company_id, page_num, page_size):
    '''
        获取设备管理的监测点列表,先保留老的写法,后面1.0改版的时候统一改
    '''
    datas = await get_user_hardware_info(company_id, page_num, page_size)
    return_fields = (
        "installed_location", "device_number", "wiring_type", "ct_change",
        "pt_change", "rated_voltage", "start_time")
    return_datas = []
    for data in datas.get('rows'):
        return_one = {}
        for return_field in return_fields:
            return_one[return_field] = data.get(return_field)
        
        return_datas.append(return_one)
    datas['rows'] = return_datas
    return datas


async def equip_management_total(company_id):
    '''
        获取设备管理的汇总信息
    '''
    datas = await hardware_statistics(company_id)
    return datas


async def equip_run_list(company_id, point_ids, start_time, end_time,
                         page_num, page_size, sort_field, sort_type):
    '''
    获取设备运行记录
    '''
    async with MysqlUtil() as conn:
        raw_sql = "SELECT {} from scope_equip_run_record s " \
lcn's avatar
lcn committed
50
                  "left join (select pid,max(start_time) start_time " \
wang.wenrong's avatar
wang.wenrong committed
51 52 53 54
                  "from scope_equip_run_record group by pid) sp " \
                  "on s.pid = sp.pid " \
                  "left join point p on s.pid=p.pid " \
                  "left join monitor_reuse r on p.mtid = r.mtid " \
lcn's avatar
lcn committed
55 56 57
                  "where (p.cid=%s or r.cid = %s) and s.start_time " \
                  "BETWEEN %s and %s and (s.end_time > 0 or " \
                  "(s.end_time = 0 and s.start_time = sp.start_time)) "
wang.wenrong's avatar
wang.wenrong committed
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
        if point_ids:
            raw_sql += " and s.pid in %s"
            args = (
                company_id,
                company_id,
                start_time,
                end_time,
                tuple(point_ids)
            )
        else:
            args = (
                company_id,
                company_id,
                start_time,
                end_time,
            )
        # 先总数
        count_sql = raw_sql.format("count(*) as run_count", "")
        count_result = await conn.fetchone(sql=count_sql, args=args)
        
        list_result = []
        if count_result.get("run_count", 0) > 0:
            # 排序字段处理
            if sort_field == 'point_name':
                sort_field = 'p.name'
            elif sort_field == 'run_time':
                sort_field = '(s.end_time-s.start_time)'
            # 再分页列表
            raw_sql = raw_sql.format(
                "s.pid,p.name point_name,s.start_time,s.end_time",
            )
            raw_sql += " order by {} {}  LIMIT %s OFFSET %s".format(sort_field,
                                                                    sort_type)
            if point_ids:
                args = (company_id,
                        company_id,
                        start_time,
                        end_time,
                        tuple(point_ids),
                        page_size,
                        (page_num - 1) * page_size
                        )
            else:
                args = (company_id,
                        company_id,
                        start_time,
                        end_time,
                        page_size,
                        (page_num - 1) * page_size
                        )
            list_result = await conn.fetchall(sql=raw_sql,
                                              args=args)
    return list_result, count_result.get("run_count", 0)


async def equip_run_statistics(company_id, point_ids, start_time, end_time):
    '''
    获取运行统计数据
    '''
lcn's avatar
lcn committed
117
    dura_time = "case when end_time > 0 then end_time-s.start_time else 0 end"
wang.wenrong's avatar
wang.wenrong committed
118 119 120 121 122 123
    async with MysqlUtil() as conn:
        count_sql = f"SELECT count(*) as total_count," \
                    f"avg({dura_time}) as avg_time," \
                    f"sum({dura_time}) as all_time," \
                    f"max({dura_time}) as max_time " \
                    "from scope_equip_run_record s " \
lcn's avatar
lcn committed
124
                    "left join (select pid,max(start_time) start_time from " \
wang.wenrong's avatar
wang.wenrong committed
125 126 127 128 129 130
                    "scope_equip_run_record group by pid) sp " \
                    "on s.pid = sp.pid " \
                    "left join point p on s.pid=p.pid " \
                    "left join monitor_reuse r on p.mtid = r.mtid " \
                    "where (p.cid=%s or r.cid = %s) " \
                    "and s.start_time BETWEEN %s and %s and (s.end_time > 0 " \
lcn's avatar
lcn committed
131
                    "or (s.end_time = 0 and s.start_time = sp.start_time)) "
wang.wenrong's avatar
wang.wenrong committed
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
        if point_ids:
            count_sql += " and s.pid in %s"
            args = (
                company_id,
                company_id,
                start_time,
                end_time,
                tuple(point_ids)
            )
        else:
            args = (
                company_id,
                company_id,
                start_time,
                end_time,
            )
        count_result = await conn.fetchone(sql=count_sql, args=args)
    return count_result


async def get_equip_run_status(point_id):
    '''
    获取当前设备是否正在运行
    '''
    async with MysqlUtil() as conn:
        # 是否非动力设备
        power_equip_sql = "select is_power_equipment from monitor m " \
lcn's avatar
lcn committed
159 160
                          "left join point p on m.mtid = p.mtid " \
                          "where p.pid = %s"
wang.wenrong's avatar
wang.wenrong committed
161 162 163 164
        power_equip_result = await conn.fetchone(sql=power_equip_sql,
                                                 args=(point_id,))
        if power_equip_result.get("is_power_equipment", 0) == 0:
            return 2
lcn's avatar
lcn committed
165 166 167 168 169
        raw_sql = "select (case when end_time > unix_timestamp(NOW()) then 1 " \
                  "when end_time=0 then 1 else 0 end) run_count " \
                  "from scope_equip_run_record " \
                  "WHERE pid = %s and start_time < unix_timestamp(NOW()) " \
                  "order by start_time desc limit 1"
wang.wenrong's avatar
wang.wenrong committed
170
        result = await conn.fetchone(sql=raw_sql, args=(point_id,))
lcn's avatar
lcn committed
171
    return 1 if result and result.get("run_count") else 0