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 
        `govdata_rain_data_info` a
    LEFT JOIN 
        `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_max_time(db):
    resutl = []
    sql = text(f"""
        SELECT MAX(CREATE_TIME) FROM govdata_rain_data_info;
""")

    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 
        `govdata_rain_data_info` a
    LEFT JOIN 
        `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