count_info_dao.py 5.59 KB
Newer Older
ZZH's avatar
ZZH committed
1 2 3
import pendulum
from datetime import timedelta
import math
lcn's avatar
lcn committed
4 5 6
from pot_libs.es_util.es_utils import EsUtil
from pot_libs.logger import log
from unify_api.constants import POINT_1MIN_EVENT
ZZH's avatar
ZZH committed
7
from unify_api.utils.time_format import convert_es_str, CST
lcn's avatar
lcn committed
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
from pot_libs.mysql_util.mysql_util import MysqlUtil

index = POINT_1MIN_EVENT


async def alarm_aggs_point_location(date_start, date_end, cid):
    """根据point_id和location_id聚合"""
    start_es = convert_es_str(date_start)
    end_es = convert_es_str(date_end)
    query_body = {
        "size": 0,
        "query": {
            "bool": {
                "must": [
                    {
                        "term": {
                            "cid": cid
                        }
                    },
                    {
                        "range": {
                            "datetime": {
                                "gte": start_es,
                                "lte": end_es
                            }
                        }
                    }
                ]
            }
        },
        "aggs": {
            "point": {
                "terms": {
                    "field": "point_id",
                    "size": 500
                }
            },
            "location": {
                "terms": {
                    "field": "location_id",
                    "size": 500
                }
            }
        }
    }
    log.info(f"index:{index} ---- query_body:{query_body}")
    async with EsUtil() as es:
        es_re = await es.search_origin(body=query_body, index=index)
    location_list = es_re["aggregations"]["location"]["buckets"]
    point_list = es_re["aggregations"]["point"]["buckets"]
    return location_list, point_list


async def get_inline_by_cid(cid):
62
    sql = "SELECT inlid, `name` FROM inline WHERE cid=%s"
lcn's avatar
lcn committed
63 64 65 66 67 68 69 70 71
    async with MysqlUtil() as conn:
        inlines = await conn.fetchall(sql, args=(cid,))
    return inlines


# 功率因素
async def get_power_factor_kpi(inline_ids, month_str):
    sql = "SELECT b.name, a.inlid, a.save_charge pf_cost, a.kpi_x, " \
          "a.save_charge FROM algo_power_factor_result a LEFT JOIN inline b " \
72
          "on a.inlid = b.inlid WHERE a.inlid in %s and a.`month`=%s"
lcn's avatar
lcn committed
73 74 75 76 77 78 79 80 81
    async with MysqlUtil() as conn:
        power_factor_results = await conn.fetchall(sql, args=(
            inline_ids, month_str))
    return power_factor_results


# 移峰填谷
async def get_pcvf_kpi(inline_ids, last_month_str):
    sql = "select b.name, a.score, a.cost_save " \
82
          "from algo_plsi_result a left join inline b " \
lcn's avatar
lcn committed
83 84 85 86 87 88 89 90 91
          "on a.inlid = b.inlid where a.inlid in %s and a.month = %s"
    async with MysqlUtil() as conn:
        pcvfs = await conn.fetchall(sql, args=(inline_ids, last_month_str))
    return pcvfs


# 经济运行
async def get_economic_kpi(inline_ids, last_month_str):
    sql = "select b.name, a.kpi_x, a.save_charge, a.mean_load_factor " \
92
          "from algo_economic_operation_result a " \
lcn's avatar
lcn committed
93 94 95 96 97 98 99 100 101 102 103
          "left join inline b on a.inlid = b.inlid " \
          "where a.inlid in %s and a.month = %s"
    async with MysqlUtil() as conn:
        econ = await conn.fetchall(sql, args=(inline_ids, last_month_str))
    return econ


# 最大需量
async def get_md_space(inline_ids, last_month_str):
    sql = (
        "select a.inline_md_charge, a.kpi_x, a.save_charge, "
104 105 106
        "a.inline_md_predict, b.inlid,b.tc_runtime "
        "from algo_md_space_analysis_result a "
        "inner join algo_md_space_analysis_unit b "
lcn's avatar
lcn committed
107
        "on a.space_analysis_id=b.id "
108
        "where b.inlid in %s and a.month = %s and valid=1;"
lcn's avatar
lcn committed
109 110 111 112 113 114 115 116 117
    )
    async with MysqlUtil() as conn:
        md_spaces = await conn.fetchall(sql, args=(inline_ids, last_month_str))
    return md_spaces


async def get_tc_runtime(inline_ids):
    sql = "SELECT inlid, name, tc_runtime FROM `inline` where inlid in %s;"
    async with MysqlUtil() as conn:
ZZH's avatar
ZZH committed
118
        tc_runtimes = await conn.fetchall(sql, args=(inline_ids,))
lcn's avatar
lcn committed
119 120 121
    return tc_runtimes


ZZH's avatar
ZZH committed
122 123 124 125 126 127
async def compy_real_pf(cid):
    sql = "select pid, inlid from point where cid=%s and add_to_company=1"
    async with MysqlUtil() as conn:
        pids = [r["pid"] for r in await conn.fetchall(sql, (cid,))]
        if not pids:
            return ""
lcn's avatar
lcn committed
128
        
ZZH's avatar
ZZH committed
129 130 131 132 133 134 135 136
        dt = pendulum.now(tz=CST)
        tstamp = dt.int_timestamp // (15 * 60) * (15 * 60)
        dt = pendulum.from_timestamp(tstamp, tz=CST)
        end_dt = (dt - timedelta(minutes=15)).strftime("%Y-%m-%d %H:%M:%S")
        str_dt = dt.strftime("%Y-%m-%d %H:%M:%S")
        sql = f"SELECT pid, create_time, pttl_mean, qttl_mean FROM " \
              f"`point_15min_electric` where create_time in " \
              f"('{str_dt}', '{end_dt}') and pid in %s;"
lcn's avatar
lcn committed
137
        
ZZH's avatar
ZZH committed
138 139 140 141
        total_pttl, total_qttl = 0, 0
        for r in await conn.fetchall(sql, (tuple(pids),)):
            total_pttl += r["pttl_mean"]
            total_qttl += r["qttl_mean"]
lcn's avatar
lcn committed
142
        
ZZH's avatar
ZZH committed
143 144 145 146 147 148 149 150 151 152
        std_cos = math.sqrt(total_pttl * total_pttl + total_qttl * total_qttl)
        return round(total_pttl / std_cos, 2) if std_cos else ""


async def compy_lst_month_pf(cid):
    sql = "SELECT inlid FROM inline WHERE cid=%s;"
    async with MysqlUtil() as conn:
        inlids = [r["inlid"] for r in await conn.fetchall(sql, (cid,))]
        if not inlids:
            return ""
lcn's avatar
lcn committed
153
        
ZZH's avatar
ZZH committed
154
        now_dt = pendulum.now(tz=CST)
lcn's avatar
lcn committed
155 156
        cal_month = now_dt.subtract(months=1).start_of(unit="month").format(
            "YYYY-MM-DD")
ZZH's avatar
ZZH committed
157 158 159 160
        sql = "SELECT cos FROM algo_power_factor_result " \
              "WHERE inlid in %s and month=%s"
        cos_lst = [r["cos"] for r in
                   await conn.fetchall(sql, (inlids, cal_month))]
lcn's avatar
lcn committed
161
        
ZZH's avatar
ZZH committed
162
        return min(cos_lst) if cos_lst else ""