<?php
include('includes/header.php'); // Include your database connection file

// Calculate the previous month
$currentYear = date('Y');
$currentMonth = date('m');

if ($currentMonth == '01') {
    // If the current month is January, set the previous month to December of the previous year
    $previousMonth = '12';
    $previousYear = $currentYear - 1;
} else {
    // For other months, simply subtract 1 from the current month
    $previousMonth = sprintf('%02d', $currentMonth - 1); // Ensure 2-digit format
    $previousYear = $currentYear;
}

// Get the full name of the previous month
$previousMonthName = date('F', mktime(0, 0, 0, $previousMonth, 10));

// Handle form submission for Budget MIS Income, Budget Contribution, and Budget Direct Material
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    if (isset($_POST['budget_mis_income']) && isset($_POST['budget_contribution'])) {
        $budgetMisIncomeInput = mysqli_real_escape_string($conn, $_POST['budget_mis_income']);
        $budgetContributionInput = mysqli_real_escape_string($conn, $_POST['budget_contribution']);
        $month = "$previousYear-$previousMonth-01";

        // Insert budget_mis_income and budget_contribution into mis_income_cum table
        $insertQuery = "INSERT INTO mis_income_cum (month, budget_mis_income, budget_contribution) VALUES ('$month', '$budgetMisIncomeInput', '$budgetContributionInput')
                        ON DUPLICATE KEY UPDATE budget_mis_income='$budgetMisIncomeInput', budget_contribution='$budgetContributionInput'";
        $resultInsert = mysqli_query($conn, $insertQuery);
        if (!$resultInsert) {
            die("Insert Query failed: " . mysqli_error($conn));
        } else {
            echo "<div class='alert alert-success'>Budget MIS Income and Budget Contribution inserted/updated successfully!</div>";
        }
    }

    if (isset($_POST['budget_direct_material'])) {
        $budgetDirectMaterialInput = mysqli_real_escape_string($conn, $_POST['budget_direct_material']);
        $month = "$previousYear-$previousMonth-01";
        // Insert budget_direct_material into mis_DM_cum table
        $insertDMQuery = "INSERT INTO mis_DM_cum (month, budget_DM_value) VALUES ('$month', '$budgetDirectMaterialInput')
                        ON DUPLICATE KEY UPDATE budget_DM_value='$budgetDirectMaterialInput'";
        $resultInsertDM = mysqli_query($conn, $insertDMQuery);
        if (!$resultInsertDM) {
            die("Insert Query for Direct Material failed: " . mysqli_error($conn));
        } else {
            echo "<div class='alert alert-success'>Budget Direct Material inserted/updated successfully!</div>";
        }
    }
}

// Query to retrieve the Total DL Value for the current year from the database
$queryDLTotal = "SELECT total_DL_value FROM mis_DL_cum WHERE month = '$previousYear-$previousMonth-01'";
$resultDLTotal = mysqli_query($conn, $queryDLTotal);
if (!$resultDLTotal) {
    die("Monthly Query failed: " . mysqli_error($conn));
}
// Fetch the Monthly MIS Income value
$rowDLTotal = mysqli_fetch_assoc($resultDLTotal);
$TotalDL = $rowDLTotal['total_DL_value'] / 100000;

// Query to retrieve the Total MIS Income for the Year from the database
$queryTotal = "SELECT total_mis_income,budget_mis_income,budget_contribution FROM mis_income_cum WHERE month = '$previousYear-$previousMonth-01'";
$resultTotal = mysqli_query($conn, $queryTotal);
if (!$resultTotal) {
    die("Total Query failed: " . mysqli_error($conn));
}
// Fetch the Total MIS Income value
$rowTotal = mysqli_fetch_assoc($resultTotal);
$misIncomeTotal = $rowTotal['total_mis_income'] / 100000;
$budgetMisIncome = $rowTotal['budget_mis_income'] / 100000;
$budgetGrossContribution = $rowTotal['budget_contribution'] / 100000;

// Query to retrieve Salaries from the salaries table
$querySalaries = "SELECT SUM(cum_salary) AS total_salaries, SUM(budget_salary) AS budget_salaries FROM salaries WHERE month BETWEEN '2025-04-01' AND '$previousYear-$previousMonth-01'";
$resultSalaries = mysqli_query($conn, $querySalaries);

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

// Fetch Salaries value
$rowSalaries = mysqli_fetch_assoc($resultSalaries);
$salaries = $rowSalaries['total_salaries'] / 100000;
$budgetSalaries = $rowSalaries['budget_salaries'] / 100000;

// Query to retrieve Direct Material from the mis_DM_cum table
$queryDirectMaterial = "SELECT cumulative_DM_value,budget_DM_value FROM mis_DM_cum WHERE month = '$previousYear-$previousMonth-01'";
$resultDirectMaterial = mysqli_query($conn, $queryDirectMaterial);

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

// Fetch Direct Material value
$rowDirectMaterial = mysqli_fetch_assoc($resultDirectMaterial);
$directMaterial = $rowDirectMaterial['cumulative_DM_value'] / 100000;
$budgetDM = $rowDirectMaterial['budget_DM_value'] / 100000;

// Query to retrieve Fixed Cost from the fixed_cost table
$queryFixedCost = "SELECT SUM(cum_fixed_cost) AS total_fixed_cost, SUM(budget_fixed_cost) AS budgeted_fixed_cost FROM fixed_cost WHERE month BETWEEN '2025-04-01' AND '$previousYear-$previousMonth-01'";
$resultFixedCost = mysqli_query($conn, $queryFixedCost);

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

// Fetch Fixed Cost value
$rowFixedCost = mysqli_fetch_assoc($resultFixedCost);
$fixedCost = $rowFixedCost['total_fixed_cost'] / 100000;
$budgetFixedCost = $rowFixedCost['budgeted_fixed_cost'] / 100000;

$grossContribution = $misIncomeTotal - ($directMaterial + $TotalDL);
$netProfit = $misIncomeTotal - ($salaries + $directMaterial + $fixedCost);
$budgetNetProfit = $budgetMisIncome - ($budgetSalaries + $budgetDM + $budgetFixedCost);

$queryDLTotalPrevYear = "SELECT total_DL_value FROM mis_DL_cum WHERE month = '2025-03-01'";
$resultDLTotalPrevYear = mysqli_query($conn, $queryDLTotalPrevYear);
if (!$resultDLTotalPrevYear) {
    die("Monthly Query failed: " . mysqli_error($conn));
}
// Fetch the Monthly MIS Income value
$rowDLTotalPrevYear = mysqli_fetch_assoc($resultDLTotalPrevYear);
$TotalDLPrevYear = $rowDLTotalPrevYear['total_DL_value'] / 100000;

// Query to retrieve the Total MIS Income for the Previous Year and Previous Month from the database
$queryTotalPrevYear = "SELECT total_mis_income FROM mis_income_cum WHERE month = '2025-03-01'";
$resultTotalPrevYear = mysqli_query($conn, $queryTotalPrevYear);
if (!$resultTotalPrevYear) {
    die("Total Previous Year Query failed: " . mysqli_error($conn));
}

// Fetch the Total MIS Income value for the Previous Year and Previous Month
$rowTotalPrevYear = mysqli_fetch_assoc($resultTotalPrevYear);
$misIncomeTotalPrevYear = $rowTotalPrevYear['total_mis_income'] / 100000;


// Query to retrieve Salaries for the Previous Year and Previous Month from the database
$querySalariesPrevYear = "SELECT SUM(cum_salary) AS total_salaries FROM salaries WHERE month BETWEEN '2024-04-01' AND '2025-$previousMonth-01'";
$resultSalariesPrevYear = mysqli_query($conn, $querySalariesPrevYear);
if (!$resultSalariesPrevYear) {
    die("Salaries Previous Year Query failed: " . mysqli_error($conn));
}
// Fetch Salaries value for the Previous Year and Previous Month
$rowSalariesPrevYear = mysqli_fetch_assoc($resultSalariesPrevYear);
$salariesPrevYear = $rowSalariesPrevYear['total_salaries'] / 100000;

// Query to retrieve Direct Material for the Previous Year and Previous Month from the database
$queryDirectMaterialPrevYear = "SELECT cumulative_DM_value FROM mis_DM_cum WHERE month = '2025-$previousMonth-01'";
$resultDirectMaterialPrevYear = mysqli_query($conn, $queryDirectMaterialPrevYear);
if (!$resultDirectMaterialPrevYear) {
    die("Direct Material Previous Year Query failed: " . mysqli_error($conn));
}
// Fetch Direct Material value for the Previous Year and Previous Month
$rowDirectMaterialPrevYear = mysqli_fetch_assoc($resultDirectMaterialPrevYear);
$directMaterialPrevYear = $rowDirectMaterialPrevYear['cumulative_DM_value'] / 100000;

// Query to retrieve Fixed Cost for the Previous Year and Previous Month from the database
$queryFixedCostPrevYear = "SELECT SUM(cum_fixed_cost) AS total_fixed_cost FROM fixed_cost WHERE month BETWEEN '2024-04-01' AND '2025-$previousMonth-01'";
$resultFixedCostPrevYear = mysqli_query($conn, $queryFixedCostPrevYear);
if (!$resultFixedCostPrevYear) {
    die("Fixed Cost Previous Year Query failed: " . mysqli_error($conn));
}
// Fetch Fixed Cost value for the Previous Year and Previous Month
$rowFixedCostPrevYear = mysqli_fetch_assoc($resultFixedCostPrevYear);
$fixedCostPrevYear = $rowFixedCostPrevYear['total_fixed_cost'] / 100000;

$grossContributionPrevYear = $misIncomeTotalPrevYear-$directMaterialPrevYear-$TotalDLPrevYear;
$netProfitPrevYear = $misIncomeTotalPrevYear - ($salariesPrevYear + $directMaterialPrevYear + $fixedCostPrevYear);


// Close the database connection
mysqli_close($conn);
?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h3>Net Profit Report</h3>
                <button id="download-excel" class="btn btn-primary float-end" style="margin-bottom: 10px;">Download Excel</button>
                <h5 class="font-weight-bold mt-4">Period: April to <?php echo date('F Y', strtotime("-1 month")); ?></h5>
            </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>(Value in Lakhs)</th>
                            <th>MIS Income</th>
                            <th>Gross Contribution</th>
                            <th>Salaries</th>
                            <th>Direct Material</th>
                            <th>Fixed Cost</th>
                            <th>Net Profit</th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr class="align-middle text-center">
                            <td class="font-weight-bold">Actuals This Year (FY 25-26)</td>
                            <td><?php echo number_format($misIncomeTotal, 2); ?></td>
                            <td><?php echo number_format($grossContribution, 2); ?></td>
                            <td><?php echo number_format($salaries, 2); ?></td>
                            <td><?php echo number_format($directMaterial, 2); ?></td>
                            <td><?php echo number_format($fixedCost, 2); ?></td>
                            <td><?php echo number_format($netProfit, 2); ?></td>
                        </tr>
                        <tr class="align-middle text-center">
                            <td class="font-weight-bold">Budgeted This Year (FY 25-26)</td>
                            <td><?php echo number_format($budgetMisIncome, 2); ?></td>
                            <td><?php echo number_format($budgetGrossContribution, 2); ?></td>
                            <td><?php echo number_format($budgetSalaries, 2); ?></td>
                            <td><?php echo number_format($budgetDM, 2); ?></td>
                            <td><?php echo number_format($budgetFixedCost, 2); ?></td>
                            <td><?php echo number_format($budgetNetProfit, 2); ?></td>
                        </tr>
                        <tr class="align-middle text-center">
                            <td class="font-weight-bold">Actuals Previous Year (FY 24-25)</td>
                            <td><?php echo number_format($misIncomeTotalPrevYear, 2); ?></td>
                            <td><?php echo number_format($grossContributionPrevYear, 2); ?></td>
                            <td><?php echo number_format($salariesPrevYear, 2); ?></td>
                            <td><?php echo number_format($directMaterialPrevYear, 2); ?></td>
                            <td><?php echo number_format($fixedCostPrevYear, 2); ?></td>
                            <td><?php echo number_format($netProfitPrevYear, 2); ?></td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
<div class="row mt-4">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h3>Insert Budget MIS Income and Contribution</h3>
            </div>
            <div class="card-body">
                <form method="POST" action="">
                    <div class="row">
                        <div class="col-md-6">
                            <div class="form-group">
                                <label for="budget_mis_income">Enter Budget MIS Income for <?php echo $previousMonthName; ?>:</label>
                                <input type="number" class="form-control" id="budget_mis_income" name="budget_mis_income" required>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="form-group">
                                <label for="budget_contribution">Enter Budget Contribution for <?php echo $previousMonthName; ?>:</label>
                                <input type="number" class="form-control" id="budget_contribution" name="budget_contribution" required>
                            </div>
                        </div>
                    </div>
                    <button type="submit" class="btn btn-primary">Insert</button>
                </form>
            </div>
        </div>
    </div>
</div>
<div class="row mt-4">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h3>Insert Budget Direct Material</h3>
            </div>
            <div class="card-body">
                <form method="POST" action="">
                    <div class="row">
                        <div class="col-md-12">
                            <div class="form-group">
                                <label for="budget_direct_material">Enter Budget Direct Material for <?php echo $previousMonthName; ?>:</label>
                                <input type="number" class="form-control" id="budget_direct_material" name="budget_direct_material" required>
                            </div>
                        </div>
                    </div>
                    <button type="submit" class="btn btn-primary">Insert</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: "Net Profit Report" }); // Convert table to Excel workbook
        
        // Generate the Excel file and trigger the download
        XLSX.writeFile(wb, 'net_profit_report.xlsx');
    }
    
    // Add event listener to the "Download Excel" button
    document.getElementById('download-excel').addEventListener('click', downloadExcel);
</script>
<?php include('includes/footer.php'); ?>