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 (PARTITION BY cn ORDER BY `T1`.`create_time` {sort} ) as `序号`,T2.area as `区县`,T2.township as `镇街`,T2.address as `站点地址`,T1.windspeed as `风速`,T1.windlevel as `风速等级`,T2.longitude as `站点经度`,T2.latitude as `站点纬度`,T1.`code` as `站点编号`,T1.create_time as `测速时间` FROM (SELECT *,row_number() OVER (PARTITION BY `govdata_wind_speed_data_info`.`code` ORDER BY `govdata_wind_speed_data_info`.`windspeed` {sort} )as cn FROM sharedb.`govdata_wind_speed_data_info`
where create_time>= NOW() - INTERVAL {timeOption} HOUR and IF('{area}'='',1=1,area_name='{area}') )T1 LEFT JOIN sharedb.govdata_wind_stand_address T2 on T1.`code`=T2.`code` and IF('{township}'='',1=1,T2.township='{township}') ORDER BY `T1`.`create_time` {sort}"""
        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
),
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
    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)}")