proxy_health_pds.py 6.52 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 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
import pendulum

from pot_libs.mysql_util.mysql_util import MysqlUtil


async def proxy_health_sumarry(cids, month_str):
    health_stats_list, point_ids = [], []
    if cids:
        async with MysqlUtil() as conn:
            sql = "select * from safe_health_stats_point " \
                  "where cid in %s and cal_month=%s"
            health_stats_list = await conn.fetchall(sql, args=(cids, month_str,))

            month_start_time = pendulum.from_format(month_str, "YYYY-MM",
                                                    tz="Asia/Shanghai")
            month_end_time = month_start_time.subtract(months=-1)
            mettimestamp = month_end_time.int_timestamp
            point_sql = "select p.pid, p.cid, m.demolished " \
                        "from point p LEFT JOIN monitor m on p.mtid=m.mtid " \
                        "where p.cid in %s " \
                        "and p.create_time < %s and m.demolished=0"
            points = await conn.fetchall(point_sql, args=(cids, mettimestamp))
            point_ids = [point["pid"] for point in points]

    unqualified_point_cnt = 0
    health_problem_map = {
        "volt_hr": 0,
        "curr_hr": 0,
        "ubl": 0,
        "ibl": 0,
        "volt_dev": 0,
        "freq_dev": 0,
    }
    health_level_map = {
        "excellent_cnt": 0,
        "good_cnt": 0,
        "fair_cnt": 0,
        "bad_cnt": 0,
    }

    for i in health_stats_list:
        stand_volt_hr = 5
        stand_curr_hr = 15
        stand_ubl = 2
        stand_ibl = 5

        unqualified_point = False
        if abs(i["volt_hr"] * 100) > stand_volt_hr:
            health_problem_map["volt_hr"] += 1
            unqualified_point = True
        if abs(i["curr_hr"] * 100) > stand_curr_hr:
            health_problem_map["curr_hr"] += 1
            unqualified_point = True
        if abs(i["ubl"] * 100) > stand_ubl:
            health_problem_map["ubl"] += 1
            unqualified_point = True
        if abs(i["ibl"] * 100) > stand_ibl:
            health_problem_map["ibl"] += 1
            unqualified_point = True
        if abs(i["volt_dev"] * 100) > 5:
            health_problem_map["volt_dev"] += 1
            unqualified_point = True
        if abs(i["freq_dev"]) > 0.1:
            health_problem_map["freq_dev"] += 1
            unqualified_point = True
        if unqualified_point:
            unqualified_point_cnt += 1

    cid_healths = []
    if cids:
        async with MysqlUtil() as conn:
            sql = (
                "select cid, health_exp from safe_health_stats_cid "
                "where cid in %s and cal_month=%s"
            )
            cid_healths = await conn.fetchall(sql, args=(cids, month_str))
    for cid_health in cid_healths:
        health_index = round(cid_health["health_exp"])
        if 90 <= health_index <= 100:
            health_level_map["excellent_cnt"] += 1
        elif 75 <= health_index < 90:
            health_level_map["good_cnt"] += 1
        elif 60 <= health_index < 75:
            health_level_map["fair_cnt"] += 1
        else:
            health_level_map["bad_cnt"] += 1
    return {
        "total_point_cnt": len(point_ids),
        "unqualified_point_cnt": unqualified_point_cnt,
        "health_level": health_level_map,
        "health_problem": health_problem_map,
    }


async def proxy_page_point_healths(
    cids, month_str, page_size, page_num, sort_field="health_exp",
        sort_direction="asc"
):
    if not cids:
        return {"total": 0, "rows": []}
    async with MysqlUtil() as conn:
        sql = "select * from safe_health_stats_point " \
              "where cid in %s and cal_month=%s order by %s"
        health_stats_list = await conn.fetchall(
            sql, args=(cids, month_str, sort_field))
        if health_stats_list and sort_field in [
            "health_exp",
            "volt_hr",
            "curr_hr",
            "ubl",
            "ibl",
            "volt_dev",
            "freq_dev",
        ]:
            health_stats_list.sort(
                key=lambda i: i[sort_field], reverse=True
                if sort_direction == "desc" else False
            )
        point_ids = [i["pid"] for i in health_stats_list]
        unqualified_stats_list = []
        for i in health_stats_list:
            stand_volt_hr = 5
            stand_curr_hr = 15
            stand_ubl = 2
            stand_ibl = 5

            i["stand_volt_hr"] = stand_volt_hr
            i["stand_curr_hr"] = stand_curr_hr
            i["stand_ubl"] = stand_ubl
            i["stand_ibl"] = stand_ibl

            unqualified_point = False
            if abs(i["volt_hr"] * 100) > stand_volt_hr:
                unqualified_point = True
            if abs(i["curr_hr"] * 100) > stand_curr_hr:
                unqualified_point = True
            if abs(i["ubl"] * 100) > stand_ubl:
                unqualified_point = True
            if abs(i["ibl"] * 100) > stand_ibl:
                unqualified_point = True
            if abs(i["volt_dev"] * 100) > 5:
                unqualified_point = True
            if abs(i["freq_dev"]) > 0.1:
                unqualified_point = True
            if unqualified_point:
                unqualified_stats_list.append(i)

        total = len(unqualified_stats_list)
        points = []
        if point_ids:
            point_sql = "select pid, name from point where pid in %s"
            points = await conn.fetchall(point_sql, args=(point_ids,))
        point_map = {i["pid"]: i for i in points}

        company_sql = "select cid, shortname from company where cid in %s"
        companys = await conn.fetchall(company_sql, args=(cids,))
        company_map = {i["cid"]: i for i in companys}
    return {
        "total": total,
        "rows": [
            {
                "company_name": company_map[i["cid"]]["shortname"],
                "point_name": point_map.get(i["pid"], {}).get("name"),
                "health_index": i["health_exp"],
                "volt_hr": round(100 * i["volt_hr"], 1),
                "curr_hr": round(100 * i["curr_hr"], 1),
                "ubl": round(100 * i["ubl"], 1),
                "ibl": round(100 * i["ibl"], 1),
                "volt_dev": round(100 * i["volt_dev"], 1),
                "freq_dev": round(i["freq_dev"], 2),
                "stand_volt_hr": i["stand_volt_hr"],
                "stand_curr_hr": i["stand_curr_hr"],
                "stand_ubl": i["stand_ubl"],
                "stand_ibl": i["stand_ibl"],
                "stand_volt_dev": 5,
                "stand_freq_dev": 0.1,
            }
            for i in unqualified_stats_list[(page_num - 1) * page_size : page_num * page_size]
        ],
    }