<?php
include("../../../classes/manager.class.php");
// ini_set('display_errors', 1);
// error_reporting(E_ALL);

$m = new Manager();

if (!isset($_SESSION['sess_userid'])) {
    header("Location: " . PROJECT_ADMIN_URL);
    exit();
}

extract($_REQUEST);

$datefrom = isset($datefrom) ? $datefrom : "";
$dateto   = isset($dateto) ? $dateto : "";
$cat_name = isset($cat_name) ? $cat_name : "All";

// ---------- Dates ----------
if (empty($datefrom)) {
    $datefrom = date('Y-m-d', strtotime("-30 days"));
} else {
    $datefrom = date("Y-m-d", strtotime($datefrom));
}

if (empty($dateto)) {
    $dateto = date('Y-m-d');
} else {
    $dateto = date("Y-m-d", strtotime($dateto));
}

$displayFrom = date("d-m-Y", strtotime($datefrom));
$displayTo   = date("d-m-Y", strtotime($dateto));

// ---------- Build filter ----------
$filterReport  = " WHERE DATE(inv.create_date) >= '" . $datefrom . "'
                   AND DATE(inv.create_date) <= '" . $dateto . "'
                   AND inv.status NOT IN ('Draft', 'Cancel') ";

if (isset($cat_name) && $cat_name != '' && $cat_name != 'All') {
    $filterReport .= " AND cs.category_id = '" . intval($cat_name) . "' ";
}

$filterReport .= " ORDER BY inv.create_date ASC, cc.name ASC, cs.name ASC, inv.id ASC, invPr.id ASC ";

// ---------- Fetch data ----------
$rows = Invoice::GetInvoiceSalesDetailReport($filterReport);

// ---------- Group by invoice ----------
$invoices = array();

if ($rows) {
    foreach ($rows as $r) {

        $invId = $r->invoice_id;

        if (!isset($invoices[$invId])) {
            $invoices[$invId] = array(
                'invoice_id'               => $r->invoice_id,
                'invoice_code'             => $r->invoice_code,
                'create_date'              => $r->create_date,
                'customer_id'              => $r->customer_id,
                'customer_name'            => $r->customer_name,
                'customer_category_id'     => $r->customer_category_id,
                'customer_category_name'   => !empty($r->customer_category_name) ? $r->customer_category_name : 'N/A',
                'customer_adv_tax_enabled' => $r->customer_adv_tax_enabled,
                'customer_adv_tax_perc'    => $r->customer_adv_tax_perc,
                'invoice_discount_amount'  => floatval($r->invoice_discount_amount),
                'invoice_is_tax'           => $r->invoice_is_tax,
                'invoice_tax'              => floatval($r->invoice_tax),
                'invoice_tax_amount'       => floatval($r->invoice_tax_amount),
                'invoice_adv_tax_rate'     => floatval($r->invoice_adv_tax_rate),
                'invoice_adv_tax_amount'   => floatval($r->invoice_adv_tax_amount),
                'invoice_total_amount'     => floatval($r->invoice_total_amount),
                'invoice_payment_due'      => floatval($r->invoice_payment_due),
                'lines'                    => array(),
                'invoice_gross_total'      => 0
            );
        }

        $carton = 0;
        if (!empty($r->carton_piece) && floatval($r->carton_piece) > 0) {
            $carton = floatval($r->product_qty) / floatval($r->carton_piece);
        }

        $qtyPcs = floatval($r->product_qty);

        $weightPerPieceKg = 0;
        $unitLower = strtolower(trim($r->product_unit));

        if ($unitLower == 'gm' || $unitLower == 'g') {
            $weightPerPieceKg = floatval($r->product_weight) / 1000;
        } elseif ($unitLower == 'kg') {
            $weightPerPieceKg = floatval($r->product_weight);
        } elseif ($unitLower == 'mg') {
            $weightPerPieceKg = floatval($r->product_weight) / 1000000;
        } else {
            $weightPerPieceKg = 0;
        }

        $lineWeightKg = $weightPerPieceKg * $qtyPcs;
        $lineGross    = $qtyPcs * floatval($r->unit_sale_price);

        $line = array(
            'invoice_product_id' => $r->invoice_product_id,
            'product_id'         => $r->product_id,
            'product_name'       => $r->product_name,
            'product_weight'     => $r->product_weight,
            'product_unit'       => $r->product_unit,
            'carton'             => $carton,
            'qty_pcs'            => $qtyPcs,
            'line_weight_kg'     => $lineWeightKg,
            'unit_sale_price'    => floatval($r->unit_sale_price),
            'line_gross'         => $lineGross
        );

        $invoices[$invId]['lines'][] = $line;
        $invoices[$invId]['invoice_gross_total'] += $lineGross;
    }
}

// ---------- Category text ----------
$categoryText = 'All';
if ($cat_name != 'All') {
    $catObj = CustomerCategorySetup::GetCustomerCategorySetupById($cat_name);
    if ($catObj) {
        $categoryText = $catObj->name;
    }
}

// ---------- CSV headers ----------
$fileName = 'sales-detail-report-' . date('d-m-Y-H-i-s') . '.csv';

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $fileName . '"');

$output = fopen('php://output', 'w');

// Optional UTF-8 BOM for Excel
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// Report title / filter rows
fputcsv($output, array('Detailed Sales Report (Invoice & Products)'));
fputcsv($output, array('Period', $displayFrom . ' to ' . $displayTo));
fputcsv($output, array('Category', $categoryText));
fputcsv($output, array(''));

// Main header row
fputcsv($output, array(
    'Row Type',
    'S.No',
    'Invoice Code',
    'Invoice Date',
    'Customer Name',
    'Customer Category',
    'Tax Rate %',
    'Product Name',
    'Weight',
    'Carton',
    'Quantity',
    'Total Weight (KG)',
    'Gross Value',
    'Disc. Value',
    'Value Excl. Sales Tax',
    'Sales Tax Value',
    'Adv. Tax Value',
    'Value Incl. Sales Tax + Adv. Tax'
));

$grand_carton    = 0;
$grand_qty       = 0;
$grand_weight_kg = 0;
$grand_gross     = 0;
$grand_discount  = 0;
$grand_excl      = 0;
$grand_tax       = 0;
$grand_adv_tax   = 0;
$grand_incl      = 0;

$srno = 1;

if (!empty($invoices)) {
    foreach ($invoices as $inv) {

        $invoiceTaxRate    = ($inv['invoice_is_tax'] == 1) ? floatval($inv['invoice_tax']) : 0;
        $invoiceDiscount   = floatval($inv['invoice_discount_amount']);
        $invoiceGrossTotal = floatval($inv['invoice_gross_total']);

        $invoiceAdvTaxAmount = 0;
        if (!empty($inv['customer_adv_tax_enabled']) && $inv['customer_adv_tax_enabled'] == 1) {
            $invoiceAdvTaxAmount = floatval($inv['invoice_adv_tax_amount']);
        }

        $inv_carton    = 0;
        $inv_qty       = 0;
        $inv_weight_kg = 0;
        $inv_gross     = 0;
        $inv_discount  = 0;
        $inv_excl      = 0;
        $inv_tax       = 0;
        $inv_adv_tax   = 0;
        $inv_incl      = 0;

        foreach ($inv['lines'] as $line) {

            $share         = ($invoiceGrossTotal > 0) ? ($line['line_gross'] / $invoiceGrossTotal) : 0;
            $line_discount = $share * $invoiceDiscount;
            $value_excl    = $line['line_gross'] - $line_discount;
            $line_tax      = $value_excl * ($invoiceTaxRate / 100);
            $line_adv_tax  = $share * $invoiceAdvTaxAmount;
            $value_incl    = $value_excl + $line_tax + $line_adv_tax;

            $inv_carton    += $line['carton'];
            $inv_qty       += $line['qty_pcs'];
            $inv_weight_kg += $line['line_weight_kg'];
            $inv_gross     += $line['line_gross'];
            $inv_discount  += $line_discount;
            $inv_excl      += $value_excl;
            $inv_tax       += $line_tax;
            $inv_adv_tax   += $line_adv_tax;
            $inv_incl      += $value_incl;

            fputcsv($output, array(
                'Detail',
                $srno,
                $inv['invoice_code'],
                date('d-m-Y h:i A', strtotime($inv['create_date'])),
                $inv['customer_name'],
                $inv['customer_category_name'],
                number_format($invoiceTaxRate, 2, '.', ''),
                $line['product_name'],
                $line['product_weight'] . ' ' . $line['product_unit'],
                number_format($line['carton'], 2, '.', ''),
                number_format($line['qty_pcs'], 2, '.', ''),
                number_format($line['line_weight_kg'], 2, '.', ''),
                number_format($line['line_gross'], 2, '.', ''),
                number_format($line_discount, 2, '.', ''),
                number_format($value_excl, 2, '.', ''),
                number_format($line_tax, 2, '.', ''),
                number_format($line_adv_tax, 2, '.', ''),
                number_format($value_incl, 2, '.', '')
            ));

            $srno++;
        }

        // Invoice total row
        fputcsv($output, array(
            'Invoice Total',
            '',
            $inv['invoice_code'],
            date('d-m-Y h:i A', strtotime($inv['create_date'])),
            $inv['customer_name'],
            $inv['customer_category_name'],
            number_format($invoiceTaxRate, 2, '.', ''),
            '',
            '',
            number_format($inv_carton, 2, '.', ''),
            number_format($inv_qty, 2, '.', ''),
            number_format($inv_weight_kg, 2, '.', ''),
            number_format($inv_gross, 2, '.', ''),
            number_format($inv_discount, 2, '.', ''),
            number_format($inv_excl, 2, '.', ''),
            number_format($inv_tax, 2, '.', ''),
            number_format($inv_adv_tax, 2, '.', ''),
            number_format($inv_incl, 2, '.', '')
        ));

        fputcsv($output, array(''));

        $grand_carton    += $inv_carton;
        $grand_qty       += $inv_qty;
        $grand_weight_kg += $inv_weight_kg;
        $grand_gross     += $inv_gross;
        $grand_discount  += $inv_discount;
        $grand_excl      += $inv_excl;
        $grand_tax       += $inv_tax;
        $grand_adv_tax   += $inv_adv_tax;
        $grand_incl      += $inv_incl;
    }

    // Grand total row
    fputcsv($output, array(
        'GRAND TOTAL',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        number_format($grand_carton, 2, '.', ''),
        number_format($grand_qty, 2, '.', ''),
        number_format($grand_weight_kg, 2, '.', ''),
        number_format($grand_gross, 2, '.', ''),
        number_format($grand_discount, 2, '.', ''),
        number_format($grand_excl, 2, '.', ''),
        number_format($grand_tax, 2, '.', ''),
        number_format($grand_adv_tax, 2, '.', ''),
        number_format($grand_incl, 2, '.', '')
    ));

} else {
    fputcsv($output, array('No records found for selected criteria.'));
}

fclose($output);
exit;
?>