blog_api/routers/disbursemanage.py

from fastapi import Depends, APIRouter, status, Query, Path, HTTPException,Request
from internal.models import *
from internal.database import fetch_one, fetch_all, execute_query, response_success, raise_if_exists, raise_if_not_found
from dependencies import get_current_active_user
from datetime import datetime
from limiter_config import limiter
router = APIRouter(
    prefix="/disburses",
    tags=['支出管理']
)


@router.get("/line/nighweek")
@limiter.limit("10/minute")
async def disburse_list(request: Request,):
    select_query = """
        -- Generate dates for the current week (Monday to Sunday)
        SELECT
            dates.selected_date AS create_date,
            COALESCE(SUM(d.disburseprice), 0) AS total_disburseprice
        FROM (
            SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE()) - x.a) DAY AS selected_date
            FROM (
                SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 
                UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
                UNION ALL SELECT 6
            ) x
        ) AS dates
        LEFT JOIN disburses d ON DATE(d.create_at) = dates.selected_date
        WHERE dates.selected_date BETWEEN CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY 
                                    AND CURDATE() + INTERVAL (6 - WEEKDAY(CURDATE())) DAY
        GROUP BY dates.selected_date
        ORDER BY dates.selected_date ASC;
    """
    disburse_list = fetch_all(select_query)
    return response_success(disburse_list, "disburse get list success")


@router.get("/line/nighmonth")
@limiter.limit("10/minute")
async def disburse_list(request: Request,):
    select_query = """
        -- Generate dates for the current month (1st to last day)
        SELECT
            dates.selected_date AS create_date,
            COALESCE(SUM(d.disburseprice), 0) AS total_disburseprice
        FROM (
            SELECT DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY + INTERVAL x.a DAY, '%Y-%m-%d') AS selected_date
            FROM (
                SELECT @rownum := @rownum + 1 AS a
                FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                      UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 
                      UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 
                      UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 
                      UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 
                      UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) t1,
                (SELECT @rownum := -1) t2
            ) x
            WHERE DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY + INTERVAL x.a DAY, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')
        ) AS dates
        LEFT JOIN disburses d ON DATE(d.create_at) = dates.selected_date
        GROUP BY dates.selected_date
        ORDER BY dates.selected_date ASC;
    """
    disburse_list = fetch_all(select_query)
    return response_success(disburse_list, "disburse get list success")


@router.get("/line/nighyear")
@limiter.limit("10/minute")
async def disburse_list(request: Request,):
    select_query = """
        -- Generate months for the current year (January to December)
        SELECT
            DATE_FORMAT(months.selected_month, '%Y-%m') AS create_date, -- Only keep the year and month
            COALESCE(SUM(d.disburseprice), 0) AS total_disburseprice
        FROM (
            -- Generate months from January to December of the current year
            SELECT DATE_FORMAT(CONCAT(YEAR(CURDATE()), '-', LPAD(x.a, 2, '0'), '-01'), '%Y-%m-01') AS selected_month
            FROM (
                SELECT 1 AS a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 
                UNION ALL SELECT 12
            ) x
        ) AS months
        LEFT JOIN disburses d ON DATE_FORMAT(d.create_at, '%Y-%m') = DATE_FORMAT(months.selected_month, '%Y-%m') -- Match by year and month only
        AND d.create_at >= DATE_FORMAT(CURDATE(), '%Y-01-01') 
        AND d.create_at < DATE_FORMAT(CURDATE() + INTERVAL 1 YEAR, '%Y-01-01')
        GROUP BY DATE_FORMAT(months.selected_month, '%Y-%m') -- Group by year and month only
        ORDER BY create_date ASC;
    """
    disburse_list = fetch_all(select_query)
    return response_success(disburse_list, "disburse get list success")


@router.get("/pie/nighweek")
@limiter.limit("10/minute")
async def classified_list(request: Request,):
    select_query = """
        -- Generate classified data for the current week (Monday to Sunday)
        SELECT
            t.typename AS name,
            COALESCE(SUM(daily_totals.value), 0) AS value
        FROM (
            -- Generate dates for the current week
            SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE()) - x.a) DAY AS selected_date
            FROM (
                SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 
                UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
                UNION ALL SELECT 6
            ) x
        ) AS dates
        CROSS JOIN disbursetypes t  -- Cross join to combine all dates with all typenames
        LEFT JOIN (
            -- Subquery to aggregate daily totals per type
            SELECT
                DATE(d.create_at) AS create_date,
                t.typename AS name,
                SUM(d.disburseprice) AS value
            FROM disburses d
            JOIN disbursetypes t ON d.typeid = t.id
            WHERE DATE(d.create_at) BETWEEN CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY 
                                        AND CURDATE() + INTERVAL (6 - WEEKDAY(CURDATE())) DAY
            GROUP BY DATE(d.create_at), t.typename
        ) AS daily_totals ON daily_totals.create_date = dates.selected_date AND daily_totals.name = t.typename
        GROUP BY t.typename
        ORDER BY t.typename ASC;
    """
    classified_list = fetch_all(select_query)
    return response_success(classified_list, "disburse get list success")


@router.get("/pie/nighmonth")
@limiter.limit("10/minute")
async def classified_list(request: Request,):
    select_query = """
        -- Generate classified data for the current month (1st to last day of the month)
        SELECT
            t.typename AS name,
            COALESCE(SUM(daily_totals.value), 0) AS value
        FROM (
            -- Generate dates for the current month
            SELECT DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY + INTERVAL x.a DAY, '%Y-%m-%d') AS selected_date
            FROM (
                SELECT @rownum := @rownum + 1 AS a
                FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                      UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 
                      UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 
                      UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 
                      UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 
                      UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) t1,
                (SELECT @rownum := -1) t2
            ) x
            WHERE DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY + INTERVAL x.a DAY, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')
        ) AS dates
        CROSS JOIN disbursetypes t  -- Cross join to combine all dates with all typenames
        LEFT JOIN (
            -- Subquery to aggregate daily totals per type
            SELECT
                DATE(d.create_at) AS create_date,
                t.typename AS name,
                SUM(d.disburseprice) AS value
            FROM disburses d
            JOIN disbursetypes t ON d.typeid = t.id
            WHERE DATE(d.create_at) BETWEEN DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-%d') 
                                        AND LAST_DAY(CURDATE())
            GROUP BY DATE(d.create_at), t.typename
        ) AS daily_totals ON daily_totals.create_date = dates.selected_date AND daily_totals.name = t.typename
        GROUP BY t.typename
        ORDER BY t.typename ASC;
    """
    classified_list = fetch_all(select_query)
    return response_success(classified_list, "disburse get list success")


@router.get("/pie/nighyear")
@limiter.limit("10/minute")
async def classified_list(request: Request,):
    select_query = """
        -- Generate classified data for the current year (January to December)
        SELECT
            t.typename AS name,
            COALESCE(SUM(monthly_totals.value), 0) AS value
        FROM (
            -- Generate months for the current year
            SELECT DATE_FORMAT(CONCAT(YEAR(CURDATE()), '-', LPAD(x.a, 2, '0'), '-01'), '%Y-%m-01') AS selected_month
            FROM (
                SELECT 1 AS a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 
                UNION ALL SELECT 12
            ) x
        ) AS months
        CROSS JOIN disbursetypes t  -- Cross join to combine all months with all typenames
        LEFT JOIN (
            -- Subquery to aggregate monthly totals per type
            SELECT
                DATE_FORMAT(d.create_at, '%Y-%m-01') AS create_month,
                t.typename AS name,
                SUM(d.disburseprice) AS value
            FROM disburses d
            JOIN disbursetypes t ON d.typeid = t.id
            WHERE DATE_FORMAT(d.create_at, '%Y-%m') BETWEEN DATE_FORMAT(CURDATE() - INTERVAL (YEAR(CURDATE()) - YEAR(d.create_at)) YEAR, '%Y-01') 
                                                     AND DATE_FORMAT(CURDATE(), '%Y-12')
            GROUP BY DATE_FORMAT(d.create_at, '%Y-%m-01'), t.typename
        ) AS monthly_totals ON monthly_totals.create_month = months.selected_month AND monthly_totals.name = t.typename
        GROUP BY t.typename
        ORDER BY t.typename ASC;
    """
    classified_list = fetch_all(select_query)
    return response_success(classified_list, "disburse get list success")


@router.get("/list/consume")
@limiter.limit("10/minute")
async def consume_list(request: Request,days: int):
    current_year = datetime.now().year
    target_year = current_year - days

    select_query = f"""
    SELECT SUM(d.disburseprice) AS data, t.typename AS name, DATE_FORMAT(d.create_at, '%Y-%m') AS date
    FROM disburses d JOIN disbursetypes t ON d.typeid = t.id
    WHERE YEAR(d.create_at) = {target_year}
    GROUP BY t.typename, DATE_FORMAT(d.create_at, '%Y-%m')
    ORDER BY DATE_FORMAT(d.create_at, '%Y-%m');
    """
    consume_list = fetch_all(select_query)
    result = {}
    for entry in consume_list:
        name = entry['name']
        date = entry['date']
        data = entry['data']

        if name not in result:
            result[name] = [0] * 12

        month_index = int(date.split('-')[1]) - 1
        result[name][month_index] = data

    final_result = [{"name": name, "data": data}
                    for name, data in result.items()]
    return response_success(final_result, "disburse get list success")


@router.get("/list/calendar")
@limiter.limit("10/minute")
async def calendar_list(request: Request,):
    select_query = """
        SELECT t.typename AS NAME, YEAR(d.create_at) AS YEAR, SUM(d.disburseprice) AS total_amount
        FROM disburses d 
        JOIN disbursetypes t ON d.typeid = t.id
        GROUP BY t.typename, YEAR(d.create_at)
        ORDER BY t.typename, YEAR(d.create_at);
    """
    calendar_list = fetch_all(select_query)

    # Initialize dictionaries to store the transformed data
    transformed_data = {}
    years = set()

    # Process each row from the query result
    for row in calendar_list:
        name = row['NAME']
        year = row['YEAR']
        total_amount = row['total_amount']
        
        if name not in transformed_data:
            transformed_data[name] = {}
        
        transformed_data[name][year] = total_amount
        years.add(year)

    # Prepare the final result in the desired format
    sorted_years = sorted(years)  # Sort years in ascending order
    headers = ['name'] + [str(year) for year in sorted_years]  # Create headers

    final_result = [headers]  # Initialize with headers

    for name, year_data in transformed_data.items():
        row = [name]
        for year in sorted_years:
            row.append(year_data.get(year, 0))  # Append data or 0 if not available
        final_result.append(row)
    
    return response_success(final_result, "disburse get list success")