python-fastapi-mm-zhcs-yj-api/routers/api/rainfall/__init__.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 jobs.rainfall_conditions_job import get_stcd_data
from datetime import datetime,timedelta
from .rain_pits import router as rain_pits_router
from .dzzh import router as dzzh_router
from .chemical_company import router as chemical_company_router

router = APIRouter()
router.include_router(rain_pits_router, prefix="/rain_pits")
router.include_router(dzzh_router, prefix="/dzzh")
router.include_router(chemical_company_router, prefix="/chemical_company")

@router.get("/info/{code}")
async def get_pattern_info(
    code: str,
    db: Session = Depends(get_db)
):
    rainfulldata = get_stcd_data(code,12)
    update_time_list = []
    rainfall_history = []
    rainfall_future = []
    cumulative_rainfall = []
    for i in rainfulldata[::-1]:
        area_name = i['F3070220000034_000018002']
        update_time_list.append( datetime.strptime(i['F3070220000034_000018006'], "%Y-%m-%d %H:%M:%S"))
        create_time = datetime.strptime(i['F3070220000034_000018004'], "%Y-%m-%d %H:%M:%S")
        hour = create_time.strftime("%H")
        value = i['F3070220000034_000018005']
        rainfall_history.append({"hour":hour,"value":value})
    update_time_max = max(update_time_list).strftime("%Y-%m-%d %H:%M:%S")
    for t in range(1,13):
        future_time = create_time+ timedelta(hours=t)
        hour = future_time.strftime("%H")
        value = 0
        rainfall_future.append({"hour":hour,"value":value})

    rainfall = 0
    for cumulative in rainfall_history+rainfall_future:
        rainfall += cumulative['value']
        cumulative_rainfall.append({"hour":cumulative['hour'],"value":rainfall})

    return {
        "code": 200, "msg": "获取成功", "data":{"areaName":area_name,"updateTime":update_time_max,"rainfallHistory":rainfall_history,"rainfallFuture":rainfall_future,"cumulativeRainfall":cumulative_rainfall}
    }

@router.get("/gdyl/info/{code}")
async def get_pattern_info(
    code: str,
    db: Session = Depends(get_db)
):
    rainfulldata = get_stcd_data(code,25)
    update_time_list = []
    rainfall_history = []
    rainfall_future = []
    # cumulative_rainfall = []
    num = 24
    for i in rainfulldata[::-1]:
        area_name = i['F3070220000034_000018002']
        update_time_list.append( datetime.strptime(i['F3070220000034_000018006'], "%Y-%m-%d %H:%M:%S"))
        create_time = datetime.strptime(i['F3070220000034_000018004'], "%Y-%m-%d %H:%M:%S")
        # hour = create_time.strftime("%H")
        value = i['F3070220000034_000018005']
        if num in [24,12,6,3,1,0]:
            rainfall_history.append({"hour":-num,"value":value})
        num-=1
    update_time_max = max(update_time_list).strftime("%Y-%m-%d %H:%M:%S")
    # for t in range(72):
    #     future_time = create_time+ timedelta(hours=t)
    #     hour = future_time.strftime("%H")
    #     value = 0
    rainfall_future.append({"hour":1,"value":0})
    rainfall_future.append({"hour":3,"value":0})
    rainfall_future.append({"hour":24,"value":1})
    rainfall_future.append({"hour":48,"value":4})
    rainfall_future.append({"hour":72,"value":2})

    rainfall = 0
    # for cumulative in rainfall_history+rainfall_future:
    #     rainfall += cumulative['value']
    #     cumulative_rainfall.append({"hour":cumulative['hour'],"value":rainfall})

    return {
        "code": 200, "msg": "获取成功", "data":{"areaName":area_name,"updateTime":update_time_max,"rainfallHistory":rainfall_history,"rainfallFuture":rainfall_future}#,"cumulativeRainfall":cumulative_rainfall}
    }

@router.get('/get_rainfall_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 T2.rainfall {sort}) AS `序号`,T1.area as `区县`,T1.township as `镇街`,T1.address as `站点地质`,T1.area_name as `站点名称`,T2.rainfall as `雨量`,T1.`code` as `站点编号`  FROM sharedb.govdata_real_time_address T1 LEFT JOIN (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` <= '{timeOption}') 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) T2 on T1.code=T2.code where CASE 
	WHEN '{area}'<>'' THEN
		T1.area='{area}'
	ELSE
		1=1
END  and CASE 
	WHEN '{township}'<>'' THEN
		T1.township='{township}'
	ELSE
		1=1
END and T2.rainfall>0 ORDER BY T2.rainfall {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)}"})