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,

    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

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
WHERE ap.enable = 1;
"""

# 查询单个告警计划
# todo


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