90 lines
3.4 KiB
TypeScript
90 lines
3.4 KiB
TypeScript
import { NextResponse } from 'next/server';
|
|
import { getDbPool } from '@/lib/db';
|
|
import { RowDataPacket } from 'mysql2';
|
|
|
|
// GET /api/ausgaben/stats - Get monthly statistics
|
|
export async function GET(request: Request) {
|
|
try {
|
|
const { searchParams } = new URL(request.url);
|
|
const month = searchParams.get('month');
|
|
const year = searchParams.get('year');
|
|
const typ = searchParams.get('typ');
|
|
|
|
if (!month || !year || typ === null) {
|
|
return NextResponse.json(
|
|
{ success: false, error: 'Month, year and typ are required' },
|
|
{ status: 400 }
|
|
);
|
|
}
|
|
|
|
const pool = getDbPool();
|
|
|
|
// Get total ausgaben and breakdown by payment type based on TYP
|
|
let query: string;
|
|
|
|
if (parseInt(typ) === 0) {
|
|
// Haushalt - unterstützt beide Varianten: mit/ohne Bindestrich und Ein/Einnahme, Uber/Ueber
|
|
query = `
|
|
SELECT
|
|
SUM(CASE WHEN Wie IN ('EC-R', 'ECR', 'EC-B', 'ECB', 'bar-R', 'barR', 'bar-B', 'barB', 'Ueber', 'Uber') THEN Wieviel ELSE 0 END) as totalAusgaben,
|
|
SUM(CASE WHEN Wie IN ('EC-R', 'ECR') THEN Wieviel ELSE 0 END) as ECR,
|
|
SUM(CASE WHEN Wie IN ('EC-B', 'ECB') THEN Wieviel ELSE 0 END) as ECB,
|
|
SUM(CASE WHEN Wie IN ('bar-R', 'barR') THEN Wieviel ELSE 0 END) as barR,
|
|
SUM(CASE WHEN Wie IN ('bar-B', 'barB') THEN Wieviel ELSE 0 END) as barB,
|
|
SUM(CASE WHEN Wie IN ('Einnahme', 'Ein') THEN Wieviel ELSE 0 END) as Einnahmen,
|
|
SUM(CASE WHEN Wie IN ('Ueber', 'Uber') THEN Wieviel ELSE 0 END) as Ueberweisungen
|
|
FROM Ausgaben
|
|
WHERE YEAR(Datum) = ? AND MONTH(Datum) = ? AND TYP = 0
|
|
`;
|
|
} else {
|
|
// Privat - unterstützt Uber/Ueber für Überweisung
|
|
query = `
|
|
SELECT
|
|
SUM(CASE WHEN Wie IN ('bar', 'EC', 'VISA', 'MASTER', 'Uber', 'Ueber') THEN Wieviel ELSE 0 END) as totalAusgaben,
|
|
SUM(CASE WHEN Wie = 'bar' THEN Wieviel ELSE 0 END) as bar,
|
|
SUM(CASE WHEN Wie = 'EC' THEN Wieviel ELSE 0 END) as EC,
|
|
SUM(CASE WHEN Wie = 'VISA' THEN Wieviel ELSE 0 END) as VISA,
|
|
SUM(CASE WHEN Wie = 'MASTER' THEN Wieviel ELSE 0 END) as MASTER,
|
|
SUM(CASE WHEN Wie = 'Einnahme' THEN Wieviel ELSE 0 END) as Einnahmen,
|
|
SUM(CASE WHEN Wie IN ('Uber', 'Ueber') THEN Wieviel ELSE 0 END) as Ueberweisungen
|
|
FROM Ausgaben
|
|
WHERE YEAR(Datum) = ? AND MONTH(Datum) = ? AND TYP = 1
|
|
`;
|
|
}
|
|
|
|
const [rows] = await pool.query<RowDataPacket[]>(query, [year, month]);
|
|
|
|
const data = rows[0] || {};
|
|
|
|
// Convert string values from MySQL to numbers
|
|
const parsedData: any = {
|
|
totalAusgaben: parseFloat(data.totalAusgaben) || 0,
|
|
Einnahmen: parseFloat(data.Einnahmen) || 0,
|
|
Ueberweisungen: parseFloat(data.Ueberweisungen) || 0,
|
|
};
|
|
|
|
if (parseInt(typ) === 0) {
|
|
parsedData.ECR = parseFloat(data.ECR) || 0;
|
|
parsedData.ECB = parseFloat(data.ECB) || 0;
|
|
parsedData.barR = parseFloat(data.barR) || 0;
|
|
parsedData.barB = parseFloat(data.barB) || 0;
|
|
} else {
|
|
parsedData.bar = parseFloat(data.bar) || 0;
|
|
parsedData.EC = parseFloat(data.EC) || 0;
|
|
parsedData.VISA = parseFloat(data.VISA) || 0;
|
|
parsedData.MASTER = parseFloat(data.MASTER) || 0;
|
|
}
|
|
|
|
return NextResponse.json({
|
|
success: true,
|
|
data: parsedData,
|
|
});
|
|
} catch (error) {
|
|
console.error('Database error:', error);
|
|
return NextResponse.json(
|
|
{ success: false, error: 'Database error' },
|
|
{ status: 500 }
|
|
);
|
|
}
|
|
}
|