python-fastapi-mm-zhcs-yj-api/utils/risk/__init__.py

from common.BigDataCenterAPI import *
from models import *
from sqlalchemy import  text

import json





def get_rain_township(db,rn):
    resutl = []
    sql = text(f"""
        SELECT T1.`name`,IFNULL(T2.num,0)as num FROM (SELECT '特大暴雨（>200）' as `name` union all
SELECT '大暴雨（100-200）' as `name` union all
SELECT '暴雨（50-100）' as `name` union all
SELECT '大雨（25-50）' as `name` union all
SELECT '中雨（10-25）' as `name` union all
SELECT '小雨（<10）' as `name` )T1 left join (
 SELECT IFNULL(CASE 
	WHEN rainfall>0 and rainfall<10 THEN
		'小雨（<10）'
	WHEN rainfall>=10 and rainfall<25 THEN
		'中雨（10-25）'
	WHEN rainfall>=25 and rainfall<50 THEN
		'大雨（25-50）'
	WHEN rainfall>=50 and rainfall<100 THEN
		'暴雨（50-100）'
	WHEN rainfall>=100 and rainfall<200 THEN
		'大暴雨（100-200）'
	WHEN rainfall>=200 THEN
		'特大暴雨（>200）'
	ELSE
		'0'
END,'0') as `name` ,COUNT(1) as `num` FROM (
SELECT 
    `latest_data`.`township` AS `township`,
    SUM(`latest_data`.`rainfall`) AS `rainfall`
FROM (
    SELECT 
        a.`code`,
        MAX(a.`area_name`) AS `area_name`,
        MAX(a.`update_time`) AS `update_time`,
        MAX(a.`address`) AS `address`,
        b.`township`,
        a.`create_time`,
        AVG(a.`rainfall`) AS `rainfall`,
        ROW_NUMBER() OVER (PARTITION BY a.`code` ORDER BY a.`create_time` DESC) AS `rn`
    FROM 
        sharedb.`govdata_rain_data_info` a
    LEFT JOIN 
        sharedb.`govdata_real_time_address` b ON a.`code` = b.`code`
    WHERE 
        a.`address` LIKE '%茂名%'
    GROUP BY 
        a.`code`, b.`township`, a.`create_time`
) `latest_data`
WHERE 
    `latest_data`.`rn` = {rn}
GROUP BY 
    `latest_data`.`township`
ORDER BY 
    `rainfall` DESC
)A GROUP BY IFNULL(CASE 
	WHEN rainfall>0 and rainfall<10 THEN
		'小雨（<10）'
	WHEN rainfall>=10 and rainfall<25 THEN
		'中雨（10-25）'
	WHEN rainfall>=25 and rainfall<50 THEN
		'大雨（25-50）'
	WHEN rainfall>=50 and rainfall<100 THEN
		'暴雨（50-100）'
	WHEN rainfall>=100 and rainfall<200 THEN
		'大暴雨（100-200）'
	WHEN rainfall>=200 THEN
		'特大暴雨（>200）'
	ELSE
		'0'
END,'0'))T2 on T1.`name`=T2.`name`;
""")

    sqlresult = db.execute(sql)
    for i in sqlresult:
        print(i)
        resutl.append({
            'name':i[0],
            'value':i[1]
        })
    return resutl

def get_rain_max_time(db):
    sql = text(f"""
        SELECT MAX(CREATE_TIME) FROM sharedb.govdata_rain_data_info;
""")

    sqlresult = db.execute(sql).first()[0]
    formatted_dt = sqlresult.strftime('%Y-%m-%d %H:%M')

    return formatted_dt

def get_forest_max_time(db):
    sql = text(f"""
        SELECT MAX(CREATE_TIME) FROM forest_fire;
""")

    sqlresult = db.execute(sql).first()[0]
    formatted_dt = sqlresult.strftime('%Y-%m-%d %H:%M')

    return formatted_dt

def get_max_rain_township(db):
    resutl = []
    sql = text(f"""
        SELECT area_name,township,rn
FROM (
    SELECT 
        a.`code`,
        MAX(a.`area_name`) AS `area_name`,
        MAX(a.`update_time`) AS `update_time`,
        MAX(a.`address`) AS `address`,
        b.`township`,
        a.`create_time`,
        AVG(a.`rainfall`) AS `rainfall`,
        ROW_NUMBER() OVER (PARTITION BY a.`code` ORDER BY a.`create_time` DESC) AS `rn`
    FROM 
        sharedb.`govdata_rain_data_info` a
    LEFT JOIN 
        sharedb.`govdata_real_time_address` b ON a.`code` = b.`code`
    WHERE 
        a.`address` LIKE '%茂名%'
    GROUP BY 
        a.`code`, b.`township`, a.`create_time`
) AS latest_data
WHERE 
    latest_data.`rn` = 1
ORDER BY 
    latest_data.`rainfall` DESC
LIMIT 1;
""")

    sqlresult = db.execute(sql)
    for i in sqlresult:
        print(i)
        resutl.append({
            'area_name':i[0],
            'township':i[1],
            'rn':i[2]
        })
    return resutl


def get_max_forest_level(db):
    sql = text('''
        SELECT MAX(subquery.warning_level) as max_level
        FROM (
            SELECT 
                AREA_NAME,
                create_time,
                  warning_level,
                ROW_NUMBER() OVER (PARTITION BY AREA_CODE ORDER BY create_time DESC) AS rn
            FROM 
                `forest_fire`
        ) AS subquery
        WHERE rn = 1;
    ''')
    sqlresult = db.execute(sql).first()[0]
    return sqlresult


def get_forest_warring(db):
    result = []
    sql=text(''' SELECT *
        FROM (
            SELECT 
                AREA_NAME,
                create_time,
                  warning_level,
                ROW_NUMBER() OVER (PARTITION BY AREA_CODE ORDER BY create_time DESC) AS rn
            FROM 
                `forest_fire`
        ) AS subquery
        WHERE rn = 1;
				    ''')
    sqlresult = db.execute(sql)
    for i in sqlresult:
        print(i)
        result.append({
            'area_name': i[0],
            'warning_level': i[2],
            'rn': i[3]
        })
    return result




def get_max_hazardous_level(db):
    sql = text('''
        SELECT MAX(subquery.warning_level) as max_level
        FROM (
            SELECT 
                AREA_NAME,
                create_time,
                  warning_level,
                ROW_NUMBER() OVER (PARTITION BY AREA_CODE ORDER BY create_time DESC) AS rn
            FROM 
                `hazardous_chemicals`
        ) AS subquery
        WHERE rn = 1;
    ''')
    sqlresult = db.execute(sql).first()[0]
    return sqlresult


def get_hazardous_warring(db):
    result = []
    sql=text(''' SELECT *
        FROM (
            SELECT 
                AREA_NAME,
                create_time,
                  warning_level,
                ROW_NUMBER() OVER (PARTITION BY AREA_CODE ORDER BY create_time DESC) AS rn
            FROM 
                `hazardous_chemicals`
        ) AS subquery
        WHERE rn = 1;
				    ''')
    sqlresult = db.execute(sql)
    for i in sqlresult:
        print(i)
        result.append({
            'area_name': i[0],
            'warning_level': i[2],
            'rn': i[3]
        })
    return result


def get_hazardous_warring_count(db):
    result = []
    sql=''' 
        SELECT '重大风险' AS LEVEL,0 AS VAL
        UNION ALL
        SELECT '较大风险' AS LEVEL,5 AS VAL
        UNION ALL
        SELECT '一般风险' AS LEVEL,6 AS VAL
        UNION ALL
        SELECT '低风险' AS LEVEL,23 AS VAL
				    '''
    sqlresult = db.execute(sql)
    for i in sqlresult:
        print(i)
        result.append({
            'LEVEL': i[0],
            'VALUES': i[1]})
    return result