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
			'特大暴雨（>250）' AS `name`
		UNION ALL
			SELECT
				'大暴雨（100-250）' 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
								'小雨（0.1-10）' AS `name`
	) T1
LEFT JOIN (
	SELECT
		IFNULL(
			CASE
			WHEN rainfall > 0
			AND rainfall < 10 THEN
				'小雨（0.1-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 < 250 THEN
				'大暴雨（100-250）'
			WHEN rainfall >= 250 THEN
				'特大暴雨（>250）'
			ELSE
				'无雨'
			END,
			'无雨'
		) AS `name`,
		COUNT(1) AS `num`
	FROM
		(
			SELECT
				`latest_data`.`code` AS `code`,
				sum(`latest_data`.`rainfall`) AS `rainfall`
			FROM
				(
					SELECT
						`govdata_rain_data_info`.`code` AS `code`,
						`govdata_rain_data_info`.`area_name` AS `area_name`,
						`govdata_rain_data_info`.`address` AS `address`,
						`govdata_rain_data_info`.`create_time` AS `create_time`,
						`govdata_rain_data_info`.`rainfall` AS `rainfall`,
						`govdata_rain_data_info`.`update_time` AS `update_time`,
						row_number () OVER (
							PARTITION BY `govdata_rain_data_info`.`code`
							ORDER BY
								`govdata_rain_data_info`.`create_time` DESC
						) AS `rn`
					FROM
						sharedb.`govdata_rain_data_info`
				) `latest_data`
			WHERE
				(
					(
						`latest_data`.`rn` <= {rn}
					)
					AND `latest_data`.`code` IN (
						SELECT
							`govdata_real_time_address`.`code`
						FROM
							sharedb.`govdata_real_time_address`
					)
				)
			GROUP BY
				`latest_data`.`code`
			ORDER BY
				`rainfall` DESC
		) A
	GROUP BY
		IFNULL(
			CASE
			WHEN rainfall > 0
			AND rainfall < 10 THEN
				'小雨（0.1-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 < 250 THEN
				'大暴雨（100-250）'
			WHEN rainfall >= 250 THEN
				'特大暴雨（>250）'
			ELSE
				'无雨'
			END,
			'无雨'
		)
) 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 name1,area_name,township,rainfall
FROM (
    SELECT 
				b.`area_name` as `name1`,
        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 
        b.`area_name`,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({
            'name':i[0],
            'area_name':i[1],
            'township':i[2],
            'rainfall':i[3]
        })
    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