python-fastapi-mm-zhcs-yj-api/routers/api/rainfall/windspeed.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from fastapi import APIRouter, Request, Depends, Query, HTTPException, status
from fastapi.responses import JSONResponse,StreamingResponse
from common.db import db_czrz
from common.security import valid_access_token
from sqlalchemy.orm import Session
from sqlalchemy.sql import func
from common.auth_user import *
from sqlalchemy import  text
from pydantic import BaseModel
from common.BigDataCenterAPI import *
from database import get_db
from typing import List
from models import *
from utils import *
from utils.spatial import *
import json
import traceback
from datetime import datetime,timedelta

router = APIRouter()
@router.get('/get_windspeed_range/export')
async def get_inspection_task_list(
        request: Request,
        sort: str = Query('desc'),
        timeOption: str = Query('24'),
        area: str = Query(''),
        township: str = Query(''),
        db: Session = Depends(get_db),
        auth_user: AuthUser = Depends(find_auth_user),
    user_id = Depends(valid_access_token)
):
    try:
        sql=f"""SELECT row_number() OVER ( ORDER BY `T1`.`windspeed` {sort} ) as `序号`,T2.area as `区县`,T2.township as `镇街`,T2.address as `站点地址`,T1.windspeed as `风速`,CASE 	WHEN T1.windspeed<0.3 THEN 0
	WHEN 0.3<=T1.windspeed and T1.windspeed<=1.5 THEN		1
	WHEN 1.6<=T1.windspeed and T1.windspeed<=3.3 THEN		2
	WHEN 3.4<=T1.windspeed and T1.windspeed<=5.4 THEN		3
	WHEN 5.5<=T1.windspeed and T1.windspeed<=7.9 THEN		4
	WHEN 8.0<=T1.windspeed and T1.windspeed<=10.7 THEN		5
	WHEN 10.8<=T1.windspeed and T1.windspeed<=13.8 THEN		6
	WHEN 13.9<=T1.windspeed and T1.windspeed<=17.1 THEN		7
	WHEN 17.2<=T1.windspeed and T1.windspeed<=20.7 THEN		8
	WHEN 20.8<=T1.windspeed and T1.windspeed<=24.4 THEN		9
	WHEN 24.5<=T1.windspeed and T1.windspeed<=28.4 THEN		10
	WHEN 28.5<=T1.windspeed and T1.windspeed<=32.6 THEN		11
	ELSE		12 END as `风速等级`,T2.longitude as `站点经度`,T2.latitude as `站点纬度`,T1.`code` as `站点编号` FROM (SELECT `code`,ROUND(SUM(windspeed)/COUNT(1),1)as windspeed FROM sharedb.`govdata_wind_speed_data_info`
where create_time>= NOW() - INTERVAL {timeOption} HOUR and {'1=1' if area == '' else f"area_name='{area}'"} and del_flag<>'2' GROUP BY `code`)T1 LEFT JOIN sharedb.govdata_wind_stand_address T2 on T1.`code`=T2.`code` ORDER BY `T1`.`windspeed` {sort}"""
        # print(sql)
        da = db.execute(sql).fetchall()
        outlist = [dict(row) for row in da]
        # 返回结果
        import pandas as pd
        from io import BytesIO
        # 将查询结果转换为 DataFrame
        df = pd.DataFrame(outlist)

        # 将 DataFrame 导出为 Excel 文件
        output = BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False)

        # 设置响应头
        output.seek(0)
        from urllib.parse import quote
        encoded_filename = f'风速监测导出{datetime.now().strftime("%Y%m%d%H%mi%s")}.xlsx'
        encoded_filename = quote(encoded_filename, encoding='utf-8')
        headers = {
            'Content-Disposition': f'attachment; filename*=UTF-8\'\'{encoded_filename}',
            'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        }

        db_czrz.log(db, auth_user, "大风监测", f"风速导出数据成功", request.client.host)

        # 返回文件流
        return StreamingResponse(output, headers=headers)
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        return JSONResponse(status_code=500, content={"code": 500, "msg": f"Internal server error: {str(e)}"})

def get_windspeed_data(db,timeOption):
    sql = f"""WITH ranked AS (
    SELECT
        A.windlevel,
        A.windspeed,
        B.`address`,
        ROW_NUMBER() OVER (PARTITION BY A.windlevel ORDER BY A.windspeed DESC) AS rn
    FROM sharedb.govdata_wind_speed_data_info A JOIN sharedb.govdata_wind_stand_address B on A.`code`=B.`code` where A.create_time >= NOW() - INTERVAL {timeOption} HOUR and A.del_flag<>'2'
),
max_per_level AS (
    SELECT windlevel, windspeed AS maxspeed, address AS maxspeed_standaddress
    FROM ranked
    WHERE rn = 1
),
summary AS (
    SELECT
        windlevel,
        COUNT(*) AS cnt,
        ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) AS ratio
    FROM sharedb.govdata_wind_speed_data_info where del_flag<>'2'
    GROUP BY windlevel
)
SELECT
    s.windlevel,
    s.cnt,
    s.ratio,
    CASE
        WHEN s.ratio < 10 THEN '个别'
        WHEN s.ratio < 40 THEN '部分'
        WHEN s.ratio < 90 THEN '大范围'
        ELSE '全市'
    END AS fugaifanwei,
    m.maxspeed,
    m.maxspeed_standaddress
FROM summary s
JOIN max_per_level m ON s.windlevel = m.windlevel
ORDER BY s.windlevel DESC;"""
    result = []
    sqlresult = db.execute(sql)
    for i in sqlresult:
        print(i)
        result.append({
            'windlevel': i[0],
            'cnt': i[1],
            'ratio': i[2],
            'fugaifanwei': i[3],
            'maxspeed': i[4],
            'maxspeed_standaddress': i[5]
        })
    return result

@router.get('/windspeed_warrning')
async def mine(request: Request,
        timeOption: int = Query(24),body = Depends(remove_xss_json),db: Session = Depends(get_db)):
    try:
        # timeOption = 24
        # start_time = "8月19日20时"
        # end_time = "8月20日20时"
        end_dt = datetime.now()  # 当前时间
        start_dt = end_dt - timedelta(hours=timeOption)

        fmt = "%-m月%-d日%-H时"  # Windows 用 %#m %#d %#H
        start_time = start_dt.strftime(fmt)
        end_time = end_dt.strftime(fmt)
        windspeed_data = get_windspeed_data(db, timeOption)
        if windspeed_data:
            result = f"局气象监测，{start_time}至{end_time}，"
            fugaifanwei = windspeed_data[0]['fugaifanwei']
            max_speed_level = windspeed_data[0]['windlevel']
            address=windspeed_data[0]['maxspeed_standaddress']
            max_speed =windspeed_data[0]['maxspeed']
            result += f'{fugaifanwei}站点出现{max_speed_level}级短时大风。'
            if len(windspeed_data)>1:
                fugaifanwei2 =windspeed_data[1]['fugaifanwei']
                max_speed_level2 = windspeed_data[1]['windlevel']
                if fugaifanwei!=fugaifanwei2:
                    result = result[:-1] + f"，{fugaifanwei2}站点出现{max_speed_level2}级短时大风。"
            result += f'其中，{address}录得最大阵风{max_speed}米/秒（{max_speed_level}级）。'
            for info in windspeed_data:
                stand_count = info['cnt']
                stand_pres = info['ratio']
                speed_level = info['windlevel']
                result += f'有{stand_count}个监测站（占总数的{stand_pres}%）录得{speed_level}级以上阵风；'
            result = result[:-1] + "。"

        else:
            result = f"{start_time}至{end_time}，暂无风速数据。"


        return {
            "code": 200,
            "msg": "成功",
            "data":
                {"result":result}
        }
    except Exception as e:
        db.rollback()
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")