<?php
/**
 * Enterprise HRM ERP System - MIS Report Excel Export
 * Complete Excel format matching MIS.xlsx structure
 */

 require_once __DIR__ . '/../../includes/init.php';
 require_once __DIR__ . '/../../includes/mis_permissions_seed.php';
requireLogin();

$misId = isset($_GET['id']) ? (int)$_GET['id'] : 0;
$format = $_GET['format'] ?? 'excel';
$siteId = getCurrentSiteId();

if ($misId <= 0) {
    $_SESSION['error'] = 'Invalid MIS report ID.';
    header('Location: ' . BASE_URL . '/modules/mis/index.php');
    exit;
}

// Ensure permissions
ensureMisPermissions();
if (!hasPermission('MIS_EXPORT') && !in_array(strtolower($_SESSION['role_code'] ?? ''), ['super_admin', 'hr_admin'])) {
    $_SESSION['error'] = 'You do not have permission to export MIS reports.';
    header('Location: ' . BASE_URL . '/modules/mis/index.php');
    exit;
}

try {
    $db = getDB();
    
    // Ensure tables exist
    try {
        $db->exec("CREATE TABLE IF NOT EXISTS `mis_reports` (
          `mis_id` int(11) NOT NULL AUTO_INCREMENT,
          `site_id` int(11) NOT NULL,
          `report_month` int(11) NOT NULL,
          `report_year` int(11) NOT NULL,
          `entity` varchar(255) DEFAULT 'Divine Food Cravers',
          `segment` varchar(100) DEFAULT 'Industry',
          `location` varchar(100),
          `hm_manager_name` varchar(255),
          `site_accountant_name` varchar(255),
          `operation_head` varchar(255),
          `report_status` enum('draft','final','locked') DEFAULT 'draft',
          `is_locked` tinyint(1) DEFAULT 0,
          `locked_at` datetime,
          `locked_by` int(11),
          `created_by` int(11),
          `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
          `updated_by` int(11),
          `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`mis_id`),
          UNIQUE KEY `unique_site_month_year` (`site_id`, `report_month`, `report_year`),
          KEY `fk_mis_site` (`site_id`),
          KEY `fk_mis_created_by` (`created_by`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    } catch (Exception $e) {
        // Table might already exist
    }
    
    // Get MIS report
    $stmt = $db->prepare("SELECT m.*, s.site_name FROM mis_reports m LEFT JOIN sites s ON m.site_id = s.site_id WHERE m.mis_id = ? AND m.site_id = ?");
    $stmt->execute([$misId, $siteId]);
    $misData = $stmt->fetch();
    
    if (!$misData) {
        $_SESSION['error'] = 'MIS report not found.';
        header('Location: ' . BASE_URL . '/modules/mis/index.php');
        exit;
    }
    
    // Get all related data
    $stmt = $db->prepare("SELECT * FROM mis_sales WHERE mis_id = ?");
    $stmt->execute([$misId]);
    $salesData = $stmt->fetch();
    
    $stmt = $db->prepare("SELECT * FROM mis_segment_purchases WHERE mis_id = ? ORDER BY segment_id");
    $stmt->execute([$misId]);
    $segmentPurchases = $stmt->fetchAll();
    
    $stmt = $db->prepare("SELECT * FROM mis_purchases WHERE mis_id = ?");
    $stmt->execute([$misId]);
    $purchaseData = $stmt->fetch();
    
    $stmt = $db->prepare("SELECT * FROM mis_odc_breakup WHERE mis_id = ? ORDER BY odc_id");
    $stmt->execute([$misId]);
    $odcData = $stmt->fetchAll();
    
    $stmt = $db->prepare("SELECT * FROM mis_labour WHERE mis_id = ?");
    $stmt->execute([$misId]);
    $labourData = $stmt->fetch();
    
    $stmt = $db->prepare("SELECT * FROM mis_budget_vs_actual WHERE mis_id = ? ORDER BY budget_id");
    $stmt->execute([$misId]);
    $budgetData = $stmt->fetchAll();
    
    $stmt = $db->prepare("SELECT * FROM mis_fuel_consumption WHERE mis_id = ?");
    $stmt->execute([$misId]);
    $fuelData = $stmt->fetch();
    
    $stmt = $db->prepare("SELECT * FROM mis_summary WHERE mis_id = ?");
    $stmt->execute([$misId]);
    $summaryData = $stmt->fetch();
    
    // Calculate totals
    $totalSalesSubtotal = ($salesData['variable_sales'] ?? 0) + 
                          ($salesData['catering_sales_bill_to_company'] ?? 0) +
                          ($salesData['laundry_sales_bill_to_company'] ?? 0) +
                          ($salesData['catering_sales_cash'] ?? 0) +
                          ($salesData['laundry_sales_cash'] ?? 0) +
                          ($salesData['saloon_sale_cash'] ?? 0);
    $grandTotalSales = $totalSalesSubtotal + 
                      ($salesData['lumsump'] ?? 0) +
                      ($salesData['fixed_revenue_onetime'] ?? 0) +
                      ($salesData['reimbursement'] ?? 0) +
                      ($salesData['other_income'] ?? 0);
    
    $totalCreditProcurement = 0;
    foreach ($segmentPurchases as $seg) {
        $totalCreditProcurement += (float)($seg['amount'] ?? 0);
    }
    
    $totalODC = 0;
    $totalODCDFC = 0;
    $totalODCPC = 0;
    foreach ($odcData as $odc) {
        $totalODCDFC += (float)($odc['dfc_credit'] ?? 0);
        $totalODCPC += (float)($odc['petty_cash'] ?? 0);
    }
    $totalODC = $totalODCDFC + $totalODCPC;
    
    $consumptionCatering = ($purchaseData['opening_stock_catering'] ?? 0) + 
                          ($purchaseData['purchase_credit_catering'] ?? 0) +
                          ($purchaseData['purchase_dairy_catering'] ?? 0) +
                          ($purchaseData['purchase_provision_catering'] ?? 0) +
                          ($purchaseData['material_in_attach_format_catering'] ?? 0) -
                          ($purchaseData['material_out_attach_format_catering'] ?? 0) -
                          ($purchaseData['closing_stock_catering'] ?? 0);
    
    $costPercentCatering = $grandTotalSales > 0 ? ($consumptionCatering / $grandTotalSales * 100) : 0;
    
    $totalLabourCost = ($labourData['gross_salary'] ?? 0) +
                      ($labourData['laundry_allowance'] ?? 0) +
                      ($labourData['provision_for_bonus'] ?? 0) +
                      ($labourData['provision_for_leave_salary'] ?? 0) +
                      ($labourData['employee_statutory_contribution'] ?? 0) +
                      ($labourData['staff_welfare_expenses'] ?? 0) +
                      ($labourData['dist_head_cost'] ?? 0) +
                      ($labourData['overtime'] ?? 0);
    
    $gop = $grandTotalSales - $consumptionCatering - $totalLabourCost - $totalODC;
    $gopPercent = $grandTotalSales > 0 ? ($gop / $grandTotalSales * 100) : 0;
    
    // Set filename
    $monthName = date('M', mktime(0, 0, 0, $misData['report_month'], 1));
    $filename = 'MIS_Report_' . $monthName . '_' . $misData['report_year'] . '.xls';
    
    if ($format === 'csv') {
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . str_replace('.xls', '.csv', $filename) . '"');
        
        $output = fopen('php://output', 'w');
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF)); // UTF-8 BOM
        
        // Header
        fputcsv($output, ['MIS REPORT']);
        fputcsv($output, []);
        fputcsv($output, ['Year', date('M/y', mktime(0, 0, 0, $misData['report_month'], 1, $misData['report_year']))]);
        fputcsv($output, ['Site Name', $misData['site_name'] ?? 'N/A']);
        fputcsv($output, ['Entity', $misData['entity'] ?? 'Divine Food Cravers']);
        fputcsv($output, ['Segment', $misData['segment'] ?? 'Industry']);
        fputcsv($output, ['Site Code', $misData['entity'] ?? 'N/A']);
        fputcsv($output, ['Location', $misData['location'] ?? 'N/A']);
        fputcsv($output, []);
        fputcsv($output, ['HM Manager Name', $misData['hm_manager_name'] ?? 'N/A']);
        fputcsv($output, ['Site Accountant Name', $misData['site_accountant_name'] ?? 'N/A']);
        fputcsv($output, ['Operation Head', $misData['operation_head'] ?? 'N/A']);
        fputcsv($output, []);
        
        // Sales
        fputcsv($output, ['SALES', 'Amount in Net']);
        fputcsv($output, ['Variable Sales', number_format($salesData['variable_sales'] ?? 0, 2)]);
        fputcsv($output, ['Catering Sales - Bill to Company', number_format($salesData['catering_sales_bill_to_company'] ?? 0, 2)]);
        fputcsv($output, ['Laundry Sales - Bill to Company', number_format($salesData['laundry_sales_bill_to_company'] ?? 0, 2)]);
        fputcsv($output, ['Catering Sales - Cash', number_format($salesData['catering_sales_cash'] ?? 0, 2)]);
        fputcsv($output, ['Laundry Sales - Cash', number_format($salesData['laundry_sales_cash'] ?? 0, 2)]);
        fputcsv($output, ['Saloon Sale Cash', number_format($salesData['saloon_sale_cash'] ?? 0, 2)]);
        fputcsv($output, ['Total', number_format($totalSalesSubtotal, 2)]);
        fputcsv($output, ['Lumsump', number_format($salesData['lumsump'] ?? 0, 2)]);
        fputcsv($output, ['Fixed Revenue Onetime', number_format($salesData['fixed_revenue_onetime'] ?? 0, 2)]);
        fputcsv($output, ['Reimbursement', number_format($salesData['reimbursement'] ?? 0, 2)]);
        fputcsv($output, ['Other Income', number_format($salesData['other_income'] ?? 0, 2)]);
        fputcsv($output, ['Total Sales', number_format($grandTotalSales, 2)]);
        
        fclose($output);
        exit;
    }
    
    // Excel format (HTML table that Excel can open)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Cache-Control: max-age=0');
    
    echo '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">';
    echo '<head>';
    echo '<meta http-equiv="Content-Type" content="text/html; charset=utf-8">';
    echo '<style>
        table { border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; font-size: 11pt; }
        td, th { border: 1px solid #000; padding: 4px 8px; }
        .total-row { background-color: #dbeafe; font-weight: bold; }
        .section-header { background-color: #f3f4f6; font-weight: bold; }
        .highlight-blue { background-color: #dbeafe; }
        .text-right { text-align: right; }
        .text-center { text-align: center; }
    </style>';
    echo '</head><body>';
    
    echo '<table>';
    
    // Title
    echo '<tr><td colspan="10" style="text-align: center; font-size: 16pt; font-weight: bold; border: none;">MIS REPORT</td></tr>';
    echo '<tr><td colspan="10" style="border: none;"></td></tr>';
    
    // Header Section
    echo '<tr>';
    echo '<td style="border: none;"><strong>Year:</strong> ' . date('M/y', mktime(0, 0, 0, $misData['report_month'], 1, $misData['report_year'])) . '</td>';
    echo '<td colspan="2" style="border: none;"></td>';
    echo '<td style="border: none;"><strong>HM Manager Name:</strong> ' . htmlspecialchars($misData['hm_manager_name'] ?? 'N/A') . '</td>';
    echo '<td colspan="6" style="border: none;"></td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td style="border: none;"><strong>Site Name:</strong> ' . htmlspecialchars($misData['site_name'] ?? 'N/A') . '</td>';
    echo '<td colspan="2" style="border: none;"></td>';
    echo '<td style="border: none;"><strong>Site Accountant Name:</strong> ' . htmlspecialchars($misData['site_accountant_name'] ?? 'N/A') . '</td>';
    echo '<td colspan="6" style="border: none;"></td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td style="border: none;"><strong>Entity:</strong> ' . htmlspecialchars($misData['entity'] ?? 'Divine Food Cravers') . '</td>';
    echo '<td colspan="2" style="border: none;"></td>';
    echo '<td style="border: none;"><strong>Operation Head:</strong> ' . htmlspecialchars($misData['operation_head'] ?? 'N/A') . '</td>';
    echo '<td colspan="6" style="border: none;"></td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td style="border: none;"><strong>Segment:</strong> ' . htmlspecialchars($misData['segment'] ?? 'Industry') . '</td>';
    echo '<td colspan="9" style="border: none;"></td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td style="border: none;"><strong>Site Code:</strong> ' . htmlspecialchars($misData['entity'] ?? 'N/A') . '</td>';
    echo '<td colspan="9" style="border: none;"></td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td style="border: none;"><strong>Location:</strong> ' . htmlspecialchars($misData['location'] ?? 'N/A') . '</td>';
    echo '<td colspan="9" style="border: none;"></td>';
    echo '</tr>';
    echo '<tr><td colspan="10" style="border: none;"></td></tr>';
    
    // Two Column Layout
    echo '<tr>';
    echo '<td colspan="5" style="vertical-align: top;">';
    
    // SALES Section
    echo '<table style="width: 100%;">';
    echo '<tr class="section-header"><td colspan="2"><strong>SALES</strong></td><td><strong>Amount in Net</strong></td></tr>';
    echo '<tr><td>Variable Sales</td><td></td><td class="text-right">' . number_format($salesData['variable_sales'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Catering Sales - Bill to Company</td><td></td><td class="text-right">' . number_format($salesData['catering_sales_bill_to_company'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Laundry Sales - Bill to Company</td><td></td><td class="text-right">' . number_format($salesData['laundry_sales_bill_to_company'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Catering Sales - Cash</td><td></td><td class="text-right">' . number_format($salesData['catering_sales_cash'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Laundry Sales - Cash</td><td></td><td class="text-right">' . number_format($salesData['laundry_sales_cash'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Saloon Sale Cash</td><td></td><td class="text-right">' . number_format($salesData['saloon_sale_cash'] ?? 0, 2) . '</td></tr>';
    echo '<tr class="total-row"><td><strong>Total</strong></td><td></td><td class="text-right"><strong>' . number_format($totalSalesSubtotal, 2) . '</strong></td></tr>';
    echo '<tr><td>Lumsump</td><td></td><td class="text-right">' . number_format($salesData['lumsump'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Fixed Revenue Onetime</td><td></td><td class="text-right">' . number_format($salesData['fixed_revenue_onetime'] ?? 0, 2) . '</td></tr>';
    echo '<tr class="highlight-blue"><td>Reimbursement</td><td></td><td class="text-right">' . number_format($salesData['reimbursement'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Other Income</td><td></td><td class="text-right">' . number_format($salesData['other_income'] ?? 0, 2) . '</td></tr>';
    echo '<tr class="total-row"><td><strong>Total Sales</strong></td><td></td><td class="text-right"><strong>' . number_format($grandTotalSales, 2) . '</strong></td></tr>';
    echo '</table>';
    
    // PURCHASE Section
    echo '<table style="width: 100%; margin-top: 20px;">';
    echo '<tr class="section-header"><td><strong>PURCHASE</strong></td><td><strong>CATERING Amount</strong></td><td><strong>HK & LAUNDRY Amount</strong></td><td><strong>Other Amount</strong></td></tr>';
    echo '<tr><td>Opening Stock</td><td class="text-right">' . number_format($purchaseData['opening_stock_catering'] ?? 0, 2) . '</td><td class="text-right">0.00</td><td class="text-right">0.00</td></tr>';
    echo '<tr><td>Purchase Credit</td><td class="text-right">' . number_format($purchaseData['purchase_credit_catering'] ?? 0, 2) . '</td><td class="text-right">0.00</td><td class="text-right">0.00</td></tr>';
    echo '<tr><td>Purchase Dairy</td><td class="text-right">' . number_format($purchaseData['purchase_dairy_catering'] ?? 0, 2) . '</td><td class="text-right">0.00</td><td class="text-right">0.00</td></tr>';
    echo '<tr><td>Purchase Provision</td><td class="text-right">' . number_format($purchaseData['purchase_provision_catering'] ?? 0, 2) . '</td><td class="text-right">0.00</td><td class="text-right">0.00</td></tr>';
    echo '<tr><td>Material In Attach Format</td><td class="text-right">' . number_format($purchaseData['material_in_attach_format_catering'] ?? 0, 2) . '</td><td class="text-right">0.00</td><td class="text-right">0.00</td></tr>';
    echo '<tr><td>Material Out Attach Format</td><td class="text-right">' . number_format($purchaseData['material_out_attach_format_catering'] ?? 0, 2) . '</td><td class="text-right">0.00</td><td class="text-right">0.00</td></tr>';
    echo '<tr class="highlight-blue"><td>Closing Stock</td><td class="text-right">' . number_format($purchaseData['closing_stock_catering'] ?? 0, 2) . '</td><td class="text-right">0.00</td><td class="text-right">0.00</td></tr>';
    echo '<tr class="total-row"><td><strong>Consumption</strong></td><td class="text-right"><strong>' . number_format($consumptionCatering, 2) . '</strong></td><td class="text-right"><strong>0.00</strong></td><td class="text-right"><strong>0.00</strong></td></tr>';
    echo '<tr class="total-row"><td><strong>Cost %</strong></td><td class="text-right"><strong>' . number_format($costPercentCatering, 2) . '%</strong></td><td class="text-right"><strong>%</strong></td><td class="text-right"><strong>%</strong></td></tr>';
    echo '<tr><td>Total Purchase Fuel</td><td class="text-right" colspan="3">' . number_format($purchaseData['total_purchase_fuel'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Total Purchase Food</td><td class="text-right" colspan="3">' . number_format($purchaseData['total_purchase_food'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Total Consumption</td><td class="text-right" colspan="3">' . number_format($consumptionCatering, 2) . '</td></tr>';
    echo '</table>';
    
    // LABOUR Section
    echo '<table style="width: 100%; margin-top: 20px;">';
    echo '<tr class="section-header"><td colspan="2"><strong>LABOUR</strong></td><td><strong>Amount</strong></td></tr>';
    echo '<tr><td>Gross Salary</td><td></td><td class="text-right">' . number_format($labourData['gross_salary'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>No. of Crew</td><td></td><td class="text-right">' . ($labourData['no_of_crew'] ?? 0) . '</td></tr>';
    echo '<tr><td>Laundry Allowance</td><td></td><td class="text-right">' . number_format($labourData['laundry_allowance'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Provision for Bonus</td><td></td><td class="text-right">' . number_format($labourData['provision_for_bonus'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Provision for Leave Salary</td><td></td><td class="text-right">' . number_format($labourData['provision_for_leave_salary'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Employee Sat. Contribution (ESI & EPF)</td><td></td><td class="text-right">' . number_format($labourData['employee_statutory_contribution'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Staff Welfare Expenses</td><td></td><td class="text-right">' . number_format($labourData['staff_welfare_expenses'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>DIST HEAD COST</td><td></td><td class="text-right">' . number_format($labourData['dist_head_cost'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Overtime</td><td></td><td class="text-right">' . number_format($labourData['overtime'] ?? 0, 2) . '</td></tr>';
    echo '<tr class="total-row"><td><strong>Total Labour Cost</strong></td><td></td><td class="text-right"><strong>' . number_format($totalLabourCost, 2) . '</strong></td></tr>';
    echo '</table>';
    
    // Summary
    echo '<table style="width: 100%; margin-top: 20px;">';
    echo '<tr><td><strong>Total Other Direct Expenses</strong></td><td class="text-right"><strong>' . number_format($totalODC, 2) . '</strong></td></tr>';
    echo '<tr><td><strong>GOP (Gross Operating Profit)</strong></td><td class="text-right"><strong>' . number_format($gop, 2) . '</strong></td></tr>';
    echo '<tr><td><strong>GOP%</strong></td><td class="text-right"><strong>' . number_format($gopPercent, 2) . '%</strong></td></tr>';
    echo '<tr class="highlight-blue"><td>Petty Cash Closing Balance Rs</td><td class="text-right">' . number_format($summaryData['petty_cash_closing_balance'] ?? 0, 2) . '</td></tr>';
    echo '<tr class="highlight-blue"><td>Sales Cash Closing Balance Rs</td><td class="text-right">' . number_format($summaryData['sales_cash_closing_balance'] ?? 0, 2) . '</td></tr>';
    echo '</table>';
    
    // Fuel Consumption
    echo '<table style="width: 100%; margin-top: 20px;">';
    echo '<tr class="section-header"><td colspan="2"><strong>Fuel Consumption Report</strong></td></tr>';
    echo '<tr><td>No of Cylinder</td><td class="text-right">' . ($fuelData['no_of_cylinders'] ?? 0) . ' Nos</td></tr>';
    echo '<tr><td>Credit Purchases</td><td class="text-right">INR ' . number_format($fuelData['credit_purchases'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Bio - Gas Purchase</td><td class="text-right">' . number_format($fuelData['bio_gas_purchase'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>Consumption</td><td class="text-right">INR ' . number_format($fuelData['consumption'] ?? 0, 2) . '</td></tr>';
    echo '<tr><td>COST %</td><td class="text-right">' . number_format($fuelData['cost_percent'] ?? 0, 2) . '%</td></tr>';
    echo '</table>';
    
    echo '</td>';
    echo '<td colspan="5" style="vertical-align: top;">';
    
    // Segment Wise Purchase
    echo '<table style="width: 100%;">';
    echo '<tr class="section-header"><td><strong>Segment wise purchase Details</strong></td><td><strong>(Vendor Name/Category)</strong></td><td><strong>Amount</strong></td></tr>';
    
    $defaultSegments = [
        'Provision and Dry', 'Bakes', 'Dairy', 'Sweets', 'Soft Drinks',
        'Ice Cream', 'Non Vegitable', 'Vegitable', 'Angul Vegetables',
        'Dispossible', 'Housekeeping', 'Fuel & Gas', 'Bio Gas'
    ];
    
    $segmentMap = [];
    foreach ($segmentPurchases as $seg) {
        $segmentMap[strtolower($seg['segment_name'])] = $seg;
    }
    
    foreach ($defaultSegments as $segName) {
        $segData = $segmentMap[strtolower($segName)] ?? null;
        echo '<tr>';
        echo '<td>' . htmlspecialchars($segName) . '</td>';
        echo '<td>' . htmlspecialchars($segData['vendor_name'] ?? '') . '</td>';
        echo '<td class="text-right">' . number_format($segData['amount'] ?? 0, 2) . '</td>';
        echo '</tr>';
    }
    
    echo '<tr class="total-row"><td colspan="2"><strong>Total Credit Procurement</strong></td><td class="text-right"><strong>' . number_format($totalCreditProcurement, 2) . '</strong></td></tr>';
    echo '</table>';
    
    // ODC BREAKUP
    echo '<table style="width: 100%; margin-top: 20px;">';
    echo '<tr class="section-header"><td><strong>ODC BREAKUP</strong></td><td><strong>DFC Credit</strong></td><td><strong>Petty Cash</strong></td></tr>';
    
    $defaultODC = [
        'Traveling & Conveyance Exp. To',
        'Commission on Sales @',
        'Purchase catering',
        'Courier Charges',
        'Telphone & Internet Charges',
        'Decoration Charges',
        'Housekeeping',
        'Electricity Client',
        'Water Charges',
        'Fuel & Gas',
        'Repair & Maint.- Gas Line & othe',
        'Printing & Stationary Materials',
        'Staff & Welfare',
        'Staff Accomodation & Electricity',
        'AMC-Charges',
        'Depreciation',
        'Dispossibale Exp.',
        'Books & Periodicals',
        'Crockery & Cuttlery',
        'Other Direct Expenses',
        'Garbage Dispose',
        'Impres Money Commision',
        'Uniform',
        'Festival Expenses'
    ];
    
    $odcMap = [];
    foreach ($odcData as $odc) {
        $odcMap[strtolower($odc['expense_category'])] = $odc;
    }
    
    foreach ($defaultODC as $odcName) {
        $odcItem = $odcMap[strtolower($odcName)] ?? null;
        echo '<tr>';
        echo '<td>' . htmlspecialchars($odcName) . '</td>';
        echo '<td class="text-right">' . number_format($odcItem['dfc_credit'] ?? 0, 2) . '</td>';
        echo '<td class="text-right">' . number_format($odcItem['petty_cash'] ?? 0, 2) . '</td>';
        echo '</tr>';
    }
    
    echo '<tr class="total-row"><td><strong>Total Expenses Amount</strong></td><td class="text-right"><strong>' . number_format($totalODCDFC, 2) . '</strong></td><td class="text-right"><strong>' . number_format($totalODCPC, 2) . '</strong></td></tr>';
    echo '</table>';
    
    // Budget Vs DBR
    echo '<table style="width: 100%; margin-top: 20px;">';
    echo '<tr class="section-header"><td colspan="3"><strong>Budget Vs DBR</strong></td></tr>';
    echo '<tr><td><strong>Category</strong></td><td><strong>Budget</strong></td><td><strong>Actual</strong></td></tr>';
    
    $defaultBudget = ['sales', 'consumption', 'labour_cost', 'other_expenses', 'depreciation', 'ce'];
    $budgetMap = [];
    foreach ($budgetData as $bud) {
        $budgetMap[strtolower($bud['category'])] = $bud;
    }
    
    foreach ($defaultBudget as $cat) {
        $budItem = $budgetMap[strtolower($cat)] ?? null;
        $budgetVal = $budItem ? ($budItem['budget_amount'] == 0 ? 'FALSE' : number_format($budItem['budget_amount'], 2)) : 'FALSE';
        echo '<tr>';
        echo '<td>' . ucfirst(str_replace('_', ' ', $cat)) . '</td>';
        echo '<td class="text-right">' . ($budgetVal === 'FALSE' ? 'FALSE' : 'INR ' . $budgetVal) . '</td>';
        echo '<td class="text-right">INR ' . number_format($budItem['actual_amount'] ?? 0, 2) . '</td>';
        echo '</tr>';
    }
    echo '</table>';
    
    echo '</td>';
    echo '</tr>';
    
    // Footer
    echo '<tr><td colspan="10" style="border: none; padding-top: 20px; text-align: center; font-size: 10pt; color: #666;">* Description to be mentioned on the Back page</td></tr>';
    
    echo '</table>';
    echo '</body></html>';
    exit;
    
} catch (Exception $e) {
    error_log("MIS export error: " . $e->getMessage());
    $_SESSION['error'] = 'Error exporting MIS report: ' . $e->getMessage();
    header('Location: ' . BASE_URL . '/modules/mis/index.php');
    exit;
}
