<?php
// Include header
include('includes/header.php');

$today = new DateTime(); // or set a test date here
$year = (int)$today->format('Y');
$month = (int)$today->format('n'); // 1-12

if ($month > 4) {
    // April to December: FY is current year to next year
    $fy_start = $year;
    $fy_end = $year + 1;
} else {
    // January to March: FY is previous year to current year
    $fy_start = $year - 1;
    $fy_end = $year;
}

// Current FY: $fy_start-04-01 to $fy_end-03-31
$currentFYStart = "$fy_start-04-01";
$currentFYEnd = "$fy_end-03-31";

// Previous FY: ($fy_start-1)-04-01 to ($fy_end-1)-03-31
$prevFYStart = ($fy_start - 1) . "-04-01";
$prevFYEnd = ($fy_end - 1) . "-03-31";

// Fetch cumulative salary data from salaries table for the current financial year (April to March)
$salariesQuery = "SELECT MONTHNAME(month) AS month, cum_salary, budget_salary FROM salaries WHERE month BETWEEN '$currentFYStart' AND '$currentFYEnd'";
$salariesResult = mysqli_query($conn, $salariesQuery);

if (!$salariesResult) {
    die("Query failed: " . mysqli_error($conn));
}

$salariesData = [];
while ($row = mysqli_fetch_assoc($salariesResult)) {
    $salariesData[] = $row;
}

// Fetch cumulative fixed cost data from fixed_cost table for the current financial year (April to March)
$fixedCostQuery = "SELECT MONTHNAME(month) AS month, cum_fixed_cost, budget_fixed_cost FROM fixed_cost WHERE month BETWEEN '$currentFYStart' AND '$currentFYEnd'";
$fixedCostResult = mysqli_query($conn, $fixedCostQuery);

if (!$fixedCostResult) {
    die("Query failed: " . mysqli_error($conn));
}

$fixedCostData = [];
while ($row = mysqli_fetch_assoc($fixedCostResult)) {
    $fixedCostData[] = $row;
}

// Fetch previous year actuals for salaries
$previousYearSalariesQuery = "SELECT MONTHNAME(month) AS month, cum_salary AS previous_year_actuals FROM salaries WHERE month BETWEEN '$prevFYStart' AND '$prevFYEnd'";
$previousYearSalariesResult = mysqli_query($conn, $previousYearSalariesQuery);

if (!$previousYearSalariesResult) {
    die("Query failed: " . mysqli_error($conn));
}

$previousYearSalariesData = [];
while ($row = mysqli_fetch_assoc($previousYearSalariesResult)) {
    $previousYearSalariesData[$row['month']] = $row['previous_year_actuals'] / 100000; // Display in lakhs
}

// Fetch previous year actuals for fixed cost
$previousYearFixedCostQuery = "SELECT MONTHNAME(month) AS month, cum_fixed_cost AS previous_year_actuals FROM fixed_cost WHERE month BETWEEN '$prevFYStart' AND '$prevFYEnd'";
$previousYearFixedCostResult = mysqli_query($conn, $previousYearFixedCostQuery);

if (!$previousYearFixedCostResult) {
    die("Query failed: " . mysqli_error($conn));
}

$previousYearFixedCostData = [];
while ($row = mysqli_fetch_assoc($previousYearFixedCostResult)) {
    $previousYearFixedCostData[$row['month']] = $row['previous_year_actuals'] / 100000; // Display in lakhs
}

// Merge data based on the month
$mergedData = [];
foreach ($salariesData as $salary) {
    $month = $salary['month'];
    $previousYearActuals = isset($previousYearSalariesData[$month]) ? $previousYearSalariesData[$month] : 0;
    $mergedData[$month] = [
        'month' => $month,
        'cum_salary' => $salary['cum_salary'] / 100000, // Display in lakhs
        'budget_salary' => $salary['budget_salary'] / 100000, // Display in lakhs
        'previous_year_actuals_salary' => $previousYearActuals,
        'cum_fixed_cost' => 0, // Default value for fixed cost, assuming it's not available for all months
        'budget_fixed_cost' => 0, // Default value for budget fixed cost
        'previous_year_actuals_fixed_cost' => 0 // Default value for previous year actuals fixed cost
    ];
}

foreach ($fixedCostData as $fixedCost) {
    $month = $fixedCost['month'];
    $previousYearActuals = isset($previousYearFixedCostData[$month]) ? $previousYearFixedCostData[$month] : 0;
    if (isset($mergedData[$month])) {
        $mergedData[$month]['cum_fixed_cost'] = $fixedCost['cum_fixed_cost'] / 100000; // Display in lakhs
        $mergedData[$month]['budget_fixed_cost'] = $fixedCost['budget_fixed_cost'] / 100000; // Display in lakhs
        $mergedData[$month]['previous_year_actuals_fixed_cost'] = $previousYearActuals; // Display in lakhs
    } else {
        $mergedData[$month] = [
            'month' => $month,
            'cum_salary' => 0, // Default value for salary, assuming it's not available for all months
            'budget_salary' => 0, // Default value for budget salary
            'previous_year_actuals_salary' => 0, // Default value for previous year actuals salary
            'cum_fixed_cost' => $fixedCost['cum_fixed_cost'] / 100000, // Display in lakhs
            'budget_fixed_cost' => $fixedCost['budget_fixed_cost'] / 100000, // Display in lakhs
            'previous_year_actuals_fixed_cost' => $previousYearActuals // Display in lakhs
        ];
    }
}

// Calculate totals
$totalCumSalary = $totalBudgetSalary = $totalPreviousYearActualsSalary = $totalCumFixedCost = $totalBudgetFixedCost = $totalPreviousYearActualsFixedCost = 0;

foreach ($mergedData as $monthData) {
    $totalCumSalary += $monthData['cum_salary'];
    $totalBudgetSalary += $monthData['budget_salary'];
    $totalPreviousYearActualsSalary += $monthData['previous_year_actuals_salary'];
    $totalCumFixedCost += $monthData['cum_fixed_cost'];
    $totalBudgetFixedCost += $monthData['budget_fixed_cost'];
    $totalPreviousYearActualsFixedCost += $monthData['previous_year_actuals_fixed_cost'];
}
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Salaries and Fixed Costs</h4>
                <button id='download-excel' class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download Excel</button>
            </div>
            <div class="card-body">
                <table class="table table-bordered table-striped table-hover text-center table-responsive">
                    <thead class="align-middle text-center" style="background-color: #ccc;">
                        <tr class="align-middle text-center">
                            <th rowspan="2">Month</th>
                            <th colspan="3">Salaries (in Lakhs)</th>
                            <th colspan="3">Fixed Cost (in Lakhs)</th>
                        </tr>
                        <tr class="align-middle text-center">
                            <th>Actuals</th>
                            <th>Budgeted</th>
                            <th>Previous Year Actuals</th>
                            <th>Actuals</th>
                            <th>Budgeted</th>
                            <th>Previous Year Actuals</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($mergedData as $monthData): ?>
                            <tr class="align-middle text-center">
                                <td><?= $monthData['month']; ?></td>
                                <td><?= number_format($monthData['cum_salary'], 2); ?></td>
                                <td><?= number_format($monthData['budget_salary'], 2); ?></td>
                                <td><?= number_format($monthData['previous_year_actuals_salary'], 2); ?></td>
                                <td><?= number_format($monthData['cum_fixed_cost'], 2); ?></td>
                                <td><?= number_format($monthData['budget_fixed_cost'], 2); ?></td>
                                <td><?= number_format($monthData['previous_year_actuals_fixed_cost'], 2); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    
                        <!-- Totals Row -->
                        <tr class="align-middle text-center font-weight-bold">
                            <td>Total</td>
                            <td><?= number_format($totalCumSalary, 2); ?></td>
                            <td><?= number_format($totalBudgetSalary, 2); ?></td>
                            <td><?= number_format($totalPreviousYearActualsSalary, 2); ?></td>
                            <td><?= number_format($totalCumFixedCost, 2); ?></td>
                            <td><?= number_format($totalBudgetFixedCost, 2); ?></td>
                            <td><?= number_format($totalPreviousYearActualsFixedCost, 2); ?></td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
<!-- ... Your existing code ... -->

<div class="row mt-4">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Enter Salaries & Fixed Costs</h4>
            </div>
            <div class="card-body">
                <form method="post" action="process_costs.php"> <!-- Assuming you have a separate script to process the entered costs -->
                    <div class="row">
                        <div class="col-md-6">
                            <div class="form-group">
                                <label for="salaryActuals">Enter Salary Actuals:</label>
                                <input type="text" class="form-control" id="salaryActuals" name="salaryActuals" placeholder="Enter actual salary amount">
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="form-group">
                                <label for="salaryBudgeted">Enter Salary Budgeted:</label>
                                <input type="text" class="form-control" id="salaryBudgeted" name="salaryBudgeted" placeholder="Enter budgeted salary amount">
                            </div>
                        </div>
                    </div>
                    <div class="row">
                        <div class="col-md-6">
                            <div class="form-group">
                                <label for="fixedCostActuals">Enter Fixed Cost Actuals:</label>
                                <input type="text" class="form-control" id="fixedCostActuals" name="fixedCostActuals" placeholder="Enter actual fixed cost amount">
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="form-group">
                                <label for="fixedCostBudgeted">Enter Fixed Cost Budgeted:</label>
                                <input type="text" class="form-control" id="fixedCostBudgeted" name="fixedCostBudgeted" placeholder="Enter budgeted fixed cost amount">
                            </div>
                        </div>
                    </div>
                    <button type="submit" class="btn btn-primary">Submit</button>
                </form>
            </div>
        </div>
    </div>
</div>
<script>
    function downloadExcel() {
        const table = document.querySelector('.table'); // Select the table
        const wb = XLSX.utils.table_to_book(table, { sheet: "Salaries & Fixed Cost Report" }); // Convert table to Excel workbook
        
        // Generate the Excel file and trigger the download
        XLSX.writeFile(wb, 'salaries_fixed_cost_report.xlsx');
    }
    
    // Add event listener to the "Download Excel" button
    document.getElementById('download-excel').addEventListener('click', downloadExcel);
</script>
<?php include('includes/footer.php'); ?>