python-fastapi-mm-zhcs-yj-api/routers/api/resourceProvison/MaterialReserveManagement/m_count.py

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

from fastapi import APIRouter, Request, Depends, Query, HTTPException, status,WebSocket,WebSocketDisconnect
from common.security import valid_access_token,valid_websocket_token
from fastapi.responses import JSONResponse
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 *
from utils.ry_system_util import *
from common.websocketManager import *
import json
import traceback

router = APIRouter()

def count_warehouse(db):
    query = db.query(ResourceProvisionWarehouseInfo)
    query = query.filter_by(del_flag = '0')
    return query.count()

def count_material(db):
    query = db.query(func.sum(ResourceProvisionMaterialInfo.inventory).label('warehouse_num'))
    query = query.filter_by(del_flag = '0')
    result = query.first()
    warehouse_num = result.warehouse_num if result else 0
    return warehouse_num

def count_area_material(db):
    query = db.query(
        ResourceProvisionWarehouseInfo.area_name.label('name'),
        func.sum(ResourceProvisionMaterialInfo.inventory).label('value')
    ).select_from(
        ResourceProvisionMaterialInfo
    ).join(
        ResourceProvisionWarehouseInfo,
        ResourceProvisionMaterialInfo.warehouse_id == ResourceProvisionWarehouseInfo.warehouse_id
    ).filter(
        ResourceProvisionMaterialInfo.del_flag == '0',
        ResourceProvisionWarehouseInfo.del_flag == '0'
    ).group_by(
        ResourceProvisionWarehouseInfo.area_name
    )

    # 执行查询并获取结果
    results = query.all()
    data = {"茂南区":0,"电白区":0,"高州市":0,"化州市":0,"信宜市":0,"高新区":0,"滨海新区":0}
    # 打印结果
    for result in results:
        data[result.name]=result.value
    return [{"name": key, "value": value} for key, value in data.items()]
def count_warehouse_material(db):
    query = db.query(
        ResourceProvisionWarehouseInfo.warehouse_name.label('name'),
        func.sum(ResourceProvisionMaterialInfo.inventory).label('value')
    ).select_from(
        ResourceProvisionMaterialInfo
    ).join(
        ResourceProvisionWarehouseInfo,
        ResourceProvisionMaterialInfo.warehouse_id == ResourceProvisionWarehouseInfo.warehouse_id
    ).filter(
        ResourceProvisionMaterialInfo.del_flag == '0',
        ResourceProvisionWarehouseInfo.del_flag == '0'
    ).group_by(
        ResourceProvisionWarehouseInfo.warehouse_name
    )
    # 执行查询并获取结果
    results = query.all()
    data = []
    # 打印结果
    for result in results:
        data.append({"name":result.name,"value":result.value})
    return data
def count_type_material(db):
    query = db.query(
        ResourceProvisionMaterialType.material_category_name.label('name'),
        func.sum(ResourceProvisionMaterialInfo.inventory).label('value')
    ).select_from(
        ResourceProvisionMaterialInfo
    ).join(
        ResourceProvisionMaterialType,
        ResourceProvisionMaterialInfo.material_type == ResourceProvisionMaterialType.id
    ).filter(
        ResourceProvisionMaterialInfo.del_flag == '0',
        ResourceProvisionMaterialType.del_flag == '0'
    ).group_by(
        ResourceProvisionMaterialType.material_category_name
    )
    # 执行查询并获取结果
    results = query.all()
    data = []
    # 打印结果
    for result in results:
        data.append({"name":result.name,"value":result.value})
    return data
def count_room_material(db,warehouseId):
    room_list = db.query(ResourceProvisionWarehouseRoomInfo)\
        .filter(ResourceProvisionWarehouseRoomInfo.del_flag == '0',
                ResourceProvisionWarehouseRoomInfo.warehouse == warehouseId).all()
    data = []
    for room in room_list:
        inventory = db.query(func.sum(ResourceProvisionMaterialInfo.inventory).label('value'))\
            .filter( ResourceProvisionMaterialInfo.del_flag == '0',
                     ResourceProvisionMaterialInfo.room_id ==room.id).first()
        value = 0
        if inventory.value:
            value = inventory.value
        data.append({"name":room.room_name,"value":value})
    # query = db.query(
    #     ResourceProvisionWarehouseRoomInfo.room_name.label('name'),
    #     func.coalesce(func.sum(ResourceProvisionMaterialInfo.inventory), 0).label('value')
    # ).select_from(
    #     ResourceProvisionWarehouseRoomInfo
    # ).outerjoin(
    #     ResourceProvisionMaterialInfo,
    #     ResourceProvisionMaterialInfo.room_id == ResourceProvisionWarehouseRoomInfo.id
    # ).filter(
    #     ResourceProvisionMaterialInfo.del_flag == '0',
    #     ResourceProvisionWarehouseRoomInfo.del_flag == '0',
    # ResourceProvisionWarehouseRoomInfo.warehouse == warehouseId
    # ).group_by(
    #     ResourceProvisionWarehouseRoomInfo.room_name
    # )

    # 执行查询并获取结果
    # print(str(query))
    # results = query.all()



    # 打印结果
    # for result in results:
    #     data.append({"name":result.name,"value":result.value})
    return data

@router.get("/data")
async def get_count_info(
    db: Session = Depends(get_db)
):
    try:

        data = {"warehouse_num": count_warehouse(db),
                "material_num": count_material(db),
                "area_material_count": count_area_material(db),
                "warehouse_material_count": count_warehouse_material(db),
                "type_material_count": count_type_material(db)}
        return {"code": 200, "msg": "获取成功", "data": data}
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")
@router.get("/count_room_material")
async def get_count_info(
    warehouseId:str = Query(None),
    db: Session = Depends(get_db)
):
    try:

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