common_cps.py 5.47 KB
Newer Older
lcn's avatar
lcn committed
1 2 3 4
from datetime import datetime
import pendulum
from pot_libs.mysql_util.mysql_util import MysqlUtil
from unify_api import constants
lcn's avatar
lcn committed
5
from unify_api.constants import CST
lcn's avatar
lcn committed
6 7 8 9 10 11


def point_day2month(dt):
    if isinstance(dt, int) or isinstance(dt, float):
        dt = pendulum.from_timestamp(dt, tz="Asia/Shanghai")
        es_index = f"{constants.POINT_1MIN_INDEX}_{dt.year}_{dt.month}"
ZZH's avatar
ZZH committed
12

lcn's avatar
lcn committed
13 14
    elif isinstance(dt, datetime):
        es_index = f"{constants.POINT_1MIN_INDEX}_{dt.year}_{dt.month}"
ZZH's avatar
ZZH committed
15

lcn's avatar
lcn committed
16 17
    else:
        es_index = constants.POINT_1MIN_INDEX
ZZH's avatar
ZZH committed
18

lcn's avatar
lcn committed
19 20 21 22 23
    return es_index


async def today_alarm_cnt(cids):
    start_time = pendulum.today(tz="Asia/Shanghai")
lcn's avatar
lcn committed
24 25
    es_end_time = start_time.subtract(days=-1).format("YYYY-MM-DD HH:mm:ss")
    es_start_time = start_time.format("YYYY-MM-DD HH:mm:ss")
ZZH's avatar
ZZH committed
26

lcn's avatar
lcn committed
27 28 29 30 31 32 33 34 35 36 37
    sql = f"""
        select cid,count(*) count
        from point_1min_event pe
        left join event_type et on pe.event_type = et.e_type
        where cid in %s and et.mode = 'alarm' and event_datetime >= %s
        and event_datetime < %s
        group by cid
    """
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql=sql,
                                    args=(cids, es_start_time, es_end_time))
ZZH's avatar
ZZH committed
38

lcn's avatar
lcn committed
39
    cid_bucket_map = {i["cid"]: i["count"] for i in datas}
ZZH's avatar
ZZH committed
40

lcn's avatar
lcn committed
41 42
    cid_alarm_map = {cid: {"today_alarm_count": 0} for cid in cids}
    for cid in cids:
lcn's avatar
lcn committed
43 44
        alarm_count = cid_bucket_map.get("cid") or 0
        cid_alarm_map[cid]["today_alarm_count"] += alarm_count
lcn's avatar
lcn committed
45 46 47
    return cid_alarm_map


lcn's avatar
lcn committed
48
async def proxy_safe_run_info(cids, start_time_str=None,
ZZH's avatar
ZZH committed
49
                              end_time_str=None):
lcn's avatar
lcn committed
50 51 52 53 54 55 56 57 58 59
    """
    批量获取 各个工厂的安全运行天数以及今日报警数, 如果是获取月份的,那么计算这个月的安全运行天数
    :param cids:
    :return:
    """
    filters = [
        {"terms": {"cid": cids}},
        # {"term": {"mode": "alarm"}},
        {"term": {"importance": 1}},
    ]
lcn's avatar
lcn committed
60 61 62
    where = ""
    start_dt, end_dt, start_ts, end_ts = None, None, 0, 0
    now_dt = pendulum.now(tz=CST)
lcn's avatar
lcn committed
63
    if start_time_str and end_time_str:
lcn's avatar
lcn committed
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
        start_dt = pendulum.parse(start_time_str)
        end_dt = pendulum.parse(end_time_str)
        start_ts = start_dt.int_timestamp
        end_ts = end_dt.int_timestamp
        now_ts = now_dt.int_timestamp
        if end_ts > now_ts:
            end_time_str = now_dt.format("YYYY-MM-DD HH:mm:ss")
        where += f" and event_datetime>= '{start_time_str}' and " \
                 f"event_datetime < '{end_time_str}' "
    sql = f"""
        select cid,date_format(event_datetime,"%%Y-%%m-%%d") fmt_day,
        count(*) count
        from point_1min_event
        where cid in %s {where}
        group by cid,date_format(event_datetime,"%%Y-%%m-%%d")
    """
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql=sql, args=(cids,))
ZZH's avatar
ZZH committed
82

lcn's avatar
lcn committed
83 84 85 86
    # 获取到工厂安装时间create_time
    async with MysqlUtil() as conn:
        company_sql = "select cid, create_time from company where cid in %s"
        companys = await conn.fetchall(company_sql, (cids,))
ZZH's avatar
ZZH committed
87

lcn's avatar
lcn committed
88
    create_time_timestamp_map = {
lcn's avatar
lcn committed
89 90
        company["cid"]: pendulum.from_timestamp(
            company["create_time"], tz=CST) for company in companys
lcn's avatar
lcn committed
91
    }
lcn's avatar
lcn committed
92 93 94 95 96 97 98 99 100
    cid_alarm_map = {cid: {"today_alarm_count": 0, "safe_run_days": 0} for cid
                     in cids}
    cid_alarm_count_dict = dict()
    for data in datas:
        cid = data.get("cid")
        if cid not in cid_alarm_count_dict:
            cid_alarm_count_dict[cid] = 0
        elif data.get("count") > 0:
            cid_alarm_count_dict[cid] += 1
lcn's avatar
lcn committed
101
    for cid in cids:
lcn's avatar
lcn committed
102 103
        create_dt = create_time_timestamp_map[cid]
        total_days = (now_dt - create_dt).days + 1
lcn's avatar
lcn committed
104 105
        if start_time_str and end_time_str:
            # 计算一段时间内安全运行天数,总天数的逻辑稍微不一样
lcn's avatar
lcn committed
106 107 108 109 110
            total_days = (end_dt - start_dt).days + 1
            create_ts = create_dt.int_timestamp
            if start_ts < create_ts < end_ts:
                total_days = (end_dt - create_dt).days + 1
            elif create_ts > end_ts:
lcn's avatar
lcn committed
111
                total_days = 0
ZZH's avatar
ZZH committed
112

lcn's avatar
lcn committed
113
        has_alarm_days = cid_alarm_count_dict.get("cid") or 0
lcn's avatar
lcn committed
114 115 116 117 118
        safe_run_days = total_days - has_alarm_days
        cid_alarm_map[cid]["safe_run_days"] = safe_run_days
        cid_alarm_map[cid]["total_days"] = total_days
    today_alarm_map = await today_alarm_cnt(cids)
    for cid in cid_alarm_map:
lcn's avatar
lcn committed
119 120
        cid_alarm_map[cid]["today_alarm_count"] = today_alarm_map[cid][
            "today_alarm_count"]
lcn's avatar
lcn committed
121 122 123 124
    return cid_alarm_map


async def alarm_time_distribution(company_ids, start, end):
125 126 127 128 129 130 131 132 133 134 135 136 137 138
    sql = f"""
        SELECT
            HOUR (pevent.event_datetime) event_hour,
            COUNT(*) event_count
        FROM
            point_1min_event pevent
        WHERE
            cid IN %s
        AND pevent.event_datetime >= '{start}'
        AND pevent.event_datetime <= '{end}'
        GROUP BY
            HOUR (pevent.event_datetime)
    """
    async with MysqlUtil() as conn:
lcn's avatar
lcn committed
139
        datas = await conn.fetchall(sql, args=(company_ids,))
ZZH's avatar
ZZH committed
140

lcn's avatar
lcn committed
141 142
    time_distribution_map = {"day_alarm_cnt": 0, "night_alarm_cnt": 0,
                             "morning_alarm_cnt": 0}
143 144
    for data in datas:
        hour = int(data["event_hour"])
ZZH's avatar
ZZH committed
145
        if 6 <= hour < 18:
146
            time_distribution_map["day_alarm_cnt"] += data["event_count"]
ZZH's avatar
ZZH committed
147
        elif 18 <= hour <= 23:
148 149 150 151
            time_distribution_map["night_alarm_cnt"] += data["event_count"]
        else:
            time_distribution_map["morning_alarm_cnt"] += data["event_count"]
    return time_distribution_map