LAS/db/db_sqls.py

tmp_sql = """
INSERT INTO dev_info (
    dev_id, online, software, hardware, ssid, password, ip, mount_plain, 
    start_x, start_y, start_z, stop_x, stop_y, stop_z, height
) VALUES (
    %(dev_id)s, %(online)s, %(software)s, %(hardware)s, %(ssid)s, %(password)s, %(ip)s, %(mount_plain)s,
    %(start_x)s, %(start_y)s, %(start_z)s, %(stop_x)s, %(stop_y)s, %(stop_z)s, %(height)s
)
"""

# 查询所有 dev_info 的设备信息
sql_query_all_dev_info = "SELECT * FROM dev_info"
sql_query_one_dev_info = "SELECT * FROM dev_info WHERE dev_id = %s"


# 查询所有设备的信息
sql_query_all_dev_detail_info = """
SELECT 
    dev_info.*,
    user_info.phone AS ui_phone,
    dev_room.room_params AS dr_roomparams,
    dev_room.furnitures AS dr_furnitures,
    dev_room.create_time AS dr_create_time,
    dev_room.delete_tag AS dr_delete_tag
FROM 
    dev_info
LEFT JOIN 
    user_info ON dev_info.user_openid = user_info.openid
LEFT JOIN 
    dev_room ON dev_info.dev_id = dev_room.dev_id
"""

# 查询单个设备的信息
sql_query_one_dev_detail_info = """
SELECT 
    dev_info.*,
    user_info.phone AS ui_phone,
    dev_room.room_params AS dr_roomparams,
    dev_room.furnitures AS dr_furnitures,
    dev_room.create_time AS dr_create_time,
    dev_room.delete_tag AS dr_delete_tag
FROM 
    dev_info
LEFT JOIN 
    user_info ON dev_info.user_openid = user_info.openid
LEFT JOIN 
    dev_room ON dev_info.dev_id = dev_room.dev_id
WHERE 
    dev_info.dev_id = %(dev_id)s
"""

# 查询所有告警计划
sql_query_all_alarm_plan = """
SELECT
    ap.id               AS plan_id,
    ap.uuid             AS plan_uuid,
    ap.name             AS plan_name,
    ap.dev_id           AS dev_id,
    ap.enable           AS enable,
    ap.region           AS region,
    ap.threshold_time   AS threshold_time,
    ap.merge_time       AS merge_time,
    ap.param            AS param,
    ap.create_time      AS create_time,
    ap.update_time      AS update_time,
    ap.linkage_push_wechat_service  AS linkage_push_wechat_service,

    et.event_val        AS event_val,
    et.event_str        AS event_str,
    et.event_desc       AS event_desc,

    atp.id              AS time_plan_id,
    atp.start_date      AS start_date,
    atp.stop_date       AS stop_date,
    atp.time_range      AS time_range,
    atp.month_days      AS month_days,
    atp.weekdays        AS weekdays,

    di.dev_name         AS dev_name,
    di.tenant_id        AS tenant_id

FROM alarm_plan ap
LEFT JOIN event_type et 
       ON ap.event_val = et.event_val
LEFT JOIN alarm_time_plan atp 
       ON ap.alarm_time_plan_id = atp.id
LEFT JOIN dev_info di
       ON ap.dev_id = di.client_id
WHERE
    ap.enable = 1;
"""

# 查询单个告警计划
sql_query_one_alarm_plan = """
SELECT
    ap.id               AS plan_id,
    ap.uuid             AS plan_uuid,
    ap.name             AS plan_name,
    ap.dev_id           AS dev_id,
    ap.enable           AS enable,
    ap.region           AS region,
    ap.threshold_time   AS threshold_time,
    ap.merge_time       AS merge_time,
    ap.param            AS param,
    ap.create_time      AS create_time,
    ap.update_time      AS update_time,
    ap.linkage_push_wechat_service  AS linkage_push_wechat_service,

    et.event_val        AS event_val,
    et.event_str        AS event_str,
    et.event_desc       AS event_desc,

    atp.id              AS time_plan_id,
    atp.start_date      AS start_date,
    atp.stop_date       AS stop_date,
    atp.time_range      AS time_range,
    atp.month_days      AS month_days,
    atp.weekdays        AS weekdays,

    di.dev_name         AS dev_name,
    di.tenant_id        AS tenant_id
FROM alarm_plan ap
LEFT JOIN event_type et 
       ON ap.event_val = et.event_val
LEFT JOIN alarm_time_plan atp 
       ON ap.alarm_time_plan_id = atp.id
LEFT JOIN dev_info di
       ON ap.dev_id = di.client_id
WHERE
    ap.uuid = %(plan_uuid)s;
"""


# 插入events
sql_insert_events = """
INSERT INTO events (
    dev_id,
    uuid,
    plan_uuid,
    event_type,
    info,
    linkage_push_wechat_service,
    is_handle,
    create_time,
    is_deleted,
    tenant_id,
    remark)
VALUES (
    %(dev_id)s,
    %(uuid)s,
    %(plan_uuid)s,
    %(event_type)s,
    %(info)s,
    %(linkage_push_wechat_service)s,
    %(is_handle)s,
    %(create_time)s,
    %(is_deleted)s,
    %(tenant_id)s,
    %(remark)s
);
"""


# 查询events
sql_query_events_by_datetime = """
SELECT *
    FROM lnxx_dev.events
WHERE
    dev_id = %(dev_id)s AND
    event_type = %(event_type)s AND
    create_time BETWEEN %(start_dt)s AND %(end_dt)s AND
    is_deleted = 0;
"""


# 查询告警记录保存周期
sql_event_save_range = """
SELECT * FROM lnxx_dev.tbl_parameter
WHERE
	parameter_id = 1963779012011212801
"""


# 删除 events 表中过期数据，只保留最近 {save_days} 天
sql_delete_expire_events = """
DELETE FROM lnxx_dev.events
WHERE
    create_time < DATE_SUB(NOW(), INTERVAL %(save_days)s DAY)
    AND is_deleted = 0;
"""