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] ], }