inventory/backend/api/tuloslaskelma.php

<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization');

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../models/AccountingEntry.php';

// Handle preflight requests
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    exit(0);
}

try {
    $database = new Database();
    $conn = $database->getConnection();
    
    if (!$conn) {
        throw new Exception('Database connection failed');
    }

    // Get query parameters
    $period = $_GET['period'] ?? 'current';
    $month = $_GET['month'] ?? null;
    $year = $_GET['year'] ?? null;
    
    // Determine date range based on period
    $dateConditions = getDateConditions($period, $month, $year);
    
    // Use AccountingEntry model to get data with category information
    $sql = "SELECT 
                ae.*,
                ac.category_name,
                ac.category_type,
                ac.vat_percentage as category_vat,
                acgn.category_group_name
            FROM accounting_entries ae
            LEFT JOIN accounting_categories ac ON ae.category = ac.category_code
            LEFT JOIN accounting_category_group_names acgn ON SUBSTRING(ae.category, 1, 3) = acgn.category_group_code
            WHERE ae.entry_date BETWEEN :start_date AND :end_date
            ORDER BY ae.entry_type, ac.category_code";
    
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':start_date', $dateConditions['start_date']);
    $stmt->bindParam(':end_date', $dateConditions['end_date']);
    $stmt->execute();
    
    $entries = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Group entries by category for profit and loss statement
    $tuloslaskelmaData = groupEntriesForTuloslaskelma($entries);
    
    // Calculate totals
    $totals = calculateTotals($tuloslaskelmaData);
    
    // Get previous period data for comparison
    $previousDateConditions = getPreviousDateConditions($period, $month, $year);
    $previousData = getPreviousPeriodData($conn, $previousDateConditions);
    
    // Combine current and previous data
    $result = [
        'period' => $period,
        'date_range' => $dateConditions,
        'current_period' => array_merge($tuloslaskelmaData, $totals),
        'previous_period' => $previousData,
        'comparison' => calculateComparison($tuloslaskelmaData, $previousData)
    ];
    
    echo json_encode([
        'success' => true,
        'data' => $result
    ]);
    
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}

function getDateConditions($period, $month, $year) {
    $currentYear = $year ?: date('Y');
    $currentMonth = $month ? date('m', strtotime($month)) : date('m');
    
    switch ($period) {
        case 'month':
            return [
                'start_date' => "$currentYear-$currentMonth-01",
                'end_date' => date('Y-m-t', strtotime("$currentYear-$currentMonth-01"))
            ];
        case 'quarter':
            $quarter = ceil($currentMonth / 3);
            $startMonth = ($quarter - 1) * 3 + 1;
            $endMonth = $quarter * 3;
            return [
                'start_date' => "$currentYear-" . str_pad($startMonth, 2, '0', STR_PAD_LEFT) . "-01",
                'end_date' => "$currentYear-" . str_pad($endMonth, 2, '0', STR_PAD_LEFT) . "-" . date('t', strtotime("$currentYear-$endMonth-01"))
            ];
        case 'year':
            return [
                'start_date' => "$currentYear-01-01",
                'end_date' => "$currentYear-12-31"
            ];
        case 'current':
        default:
            return [
                'start_date' => date('Y-01-01'),
                'end_date' => date('Y-m-d')
            ];
    }
}

function getPreviousDateConditions($period, $month, $year) {
    $currentYear = $year ?: date('Y');
    $currentMonth = $month ? date('m', strtotime($month)) : date('m');
    
    switch ($period) {
        case 'month':
            $previousDate = date('Y-m', strtotime("$currentYear-$currentMonth-01 -1 month"));
            $prevYear = date('Y', strtotime($previousDate));
            $prevMonth = date('m', strtotime($previousDate));
            return [
                'start_date' => "$prevYear-$prevMonth-01",
                'end_date' => date('Y-m-t', strtotime("$prevYear-$prevMonth-01"))
            ];
        case 'quarter':
            $quarter = ceil($currentMonth / 3);
            if ($quarter == 1) {
                $prevYear = $currentYear - 1;
                $prevQuarter = 4;
            } else {
                $prevYear = $currentYear;
                $prevQuarter = $quarter - 1;
            }
            $startMonth = ($prevQuarter - 1) * 3 + 1;
            $endMonth = $prevQuarter * 3;
            return [
                'start_date' => "$prevYear-" . str_pad($startMonth, 2, '0', STR_PAD_LEFT) . "-01",
                'end_date' => "$prevYear-" . str_pad($endMonth, 2, '0', STR_PAD_LEFT) . "-" . date('t', strtotime("$prevYear-$endMonth-01"))
            ];
        case 'year':
            return [
                'start_date' => ($currentYear - 1) . "-01-01",
                'end_date' => ($currentYear - 1) . "-12-31"
            ];
        case 'current':
        default:
            return [
                'start_date' => (date('Y') - 1) . "-01-01",
                'end_date' => (date('Y') - 1) . "-12-31"
            ];
    }
}

function groupEntriesForTuloslaskelma($entries) {
    $grouped = [];
    
    // First, group by account (first three digits)
    $accountGroups = [];
    foreach ($entries as $entry) {
        $accountCode = substr($entry['category'], 0, 3);
        if (!isset($accountGroups[$accountCode])) {
            $accountGroups[$accountCode] = [
                'account_code' => $accountCode,
                'account_group_name' => $entry['category_group_name'] ?: 'Unknown Group',
                'entries' => []
            ];
        }
        $accountGroups[$accountCode]['entries'][] = $entry;
    }
    
    // Now, group account groups by Finnish accounting categories
    foreach ($accountGroups as $accountCode => $accountGroup) {
        $totalAmount = 0;
        foreach ($accountGroup['entries'] as $entry) {
            $totalAmount += floatval($entry['tax_free_amount']);
        }
        
        $category = [
            'code' => $accountCode,
            'name' => $accountGroup['account_group_name'],
            'currentAmount' => $totalAmount
        ];
        
        // Group by Finnish accounting categories
        if ($accountGroup['entries'][0]['entry_type'] === 'Tulo') {
            if (strpos($accountCode, '30') === 0) {
                $grouped['sales_revenue'][] = $category;
            } else {
                $grouped['other_revenue'][] = $category;
            }
        } else { // Kulu (Expense)
            switch ($accountCode) {
                case '333':
                    $grouped['cost_of_goods'][] = $category;
                    break;
                case '335':
                    $grouped['personnel_expenses'][] = $category;
                    break;
                case '336':
                    $grouped['personnel_expenses'][] = $category;
                    break;
                case '337':
                    $grouped['depreciation'][] = $category;
                    break;
                case '365':
                    $grouped['tax_expenses'][] = $category;
                    break;
                default:
                    $grouped['other_expenses'][] = $category;
                    break;
            }
        }
    }
    
    return $grouped;
}

function calculateTotals($data) {
    $totals = [];
    
    foreach ($data as $category => $items) {
        $total = 0;
        foreach ($items as $item) {
            $total += $item['currentAmount'];
        }
        $totals[$category . '_total'] = [
            'currentAmount' => $total
        ];
    }
    
    // Calculate derived totals
    $salesRevenueTotal = $totals['sales_revenue_total']['currentAmount'] ?? 0;
    $otherRevenueTotal = $totals['other_revenue_total']['currentAmount'] ?? 0;
    $totalRevenue = $salesRevenueTotal + $otherRevenueTotal;
    
    $costOfGoodsTotal = $totals['cost_of_goods_total']['currentAmount'] ?? 0;
    $personnelExpensesTotal = $totals['personnel_expenses_total']['currentAmount'] ?? 0;
    $otherExpensesTotal = $totals['other_expenses_total']['currentAmount'] ?? 0;
    $depreciationTotal = $totals['depreciation_total']['currentAmount'] ?? 0;
    $taxExpensesTotal = $totals['tax_expenses_total']['currentAmount'] ?? 0;
    
    $totalExpenses = $costOfGoodsTotal + $personnelExpensesTotal + $otherExpensesTotal + $depreciationTotal;
    $grossProfit = $salesRevenueTotal - $costOfGoodsTotal;
    $profitBeforeTax = $totalRevenue - $totalExpenses;
    $netProfit = $profitBeforeTax - $taxExpensesTotal;
    
    $totals['total_revenue'] = ['currentAmount' => $totalRevenue];
    $totals['total_expenses'] = ['currentAmount' => $totalExpenses];
    $totals['gross_profit'] = ['currentAmount' => $grossProfit];
    $totals['profit_before_tax'] = ['currentAmount' => $profitBeforeTax];
    $totals['net_profit'] = ['currentAmount' => $netProfit];
    
    return $totals;
}

function getPreviousPeriodData($conn, $dateConditions) {
    $sql = "SELECT 
                ae.*,
                ac.category_name,
                ac.category_type,
                ac.vat_percentage as category_vat,
                acgn.category_group_name
            FROM accounting_entries ae
            LEFT JOIN accounting_categories ac ON ae.category = ac.category_code
            LEFT JOIN accounting_category_group_names acgn ON SUBSTRING(ae.category, 1, 3) = acgn.category_group_code
            WHERE ae.entry_date BETWEEN :start_date AND :end_date
            ORDER BY ae.entry_type, ac.category_code";
    
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':start_date', $dateConditions['start_date']);
    $stmt->bindParam(':end_date', $dateConditions['end_date']);
    $stmt->execute();
    
    $entries = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $grouped = groupEntriesForTuloslaskelma($entries);
    
    // Add previous amounts to categories
    foreach ($grouped as $category => $items) {
        foreach ($items as $index => $item) {
            $grouped[$category][$index]['previousAmount'] = $item['currentAmount'];
            // Also add isGroup flag for frontend
            $grouped[$category][$index]['isGroup'] = true;
        }
    }
    
    return $grouped;
}

function calculateComparison($current, $previous) {
    $comparison = [];
    
    foreach ($current as $category => $items) {
        $comparison[$category] = [];
        foreach ($items as $index => $item) {
            $prevAmount = 0;
            if (isset($previous[$category][$index])) {
                $prevAmount = $previous[$category][$index]['currentAmount'] ?? 0;
            }
            
            $change = $item['currentAmount'] - $prevAmount;
            $changePercent = $prevAmount != 0 ? ($change / $prevAmount) * 100 : 0;
            
            $comparison[$category][$index] = [
                'change' => $change,
                'changePercent' => $changePercent
            ];
        }
    }
    
    return $comparison;
}
?>