TransWar File Browser

Path: C:\

<?php
require_once __DIR__ . '/includes/functions.php';

// Carregar autoloader do Composer se disponível
if (file_exists(__DIR__ . '/vendor/autoload.php')) {
    require_once __DIR__ . '/vendor/autoload.php';
}

$clienteDocFilter = sanitize_doc($_GET['cliente_documento'] ?? '');
$mesAno = trim($_GET['mes_ano'] ?? date('Y-m'));

// Normalizar mês/ano
if (preg_match('/^(\d{4})-(\d{2})$/', $mesAno, $m)) {
    $year = (int)$m[1];
    $month = (int)$m[2];
} elseif (preg_match('/^(\d{2})\/(\d{4})$/', $mesAno, $m)) {
    $month = (int)$m[1];
    $year = (int)$m[2];
} else {
    $year = (int)date('Y');
    $month = (int)date('m');
}

$startDate = sprintf('%04d-%02d-01', $year, $month);
$totalDays = cal_days_in_month(CAL_GREGORIAN, $month, $year);

$movimentacoes = load_json('movimentacoes.json');
$produtosMap = get_produtos_map();

// Agrupar dados por dia do mês selecionado
$dailyData = [];
for ($d = 1; $d <= $totalDays; $d++) {
    $dateKey = sprintf('%04d-%02d-%02d', $year, $month, $d);
    $dailyData[$dateKey] = [
        'day' => $d,
        'date' => $dateKey,
        'paletes_entrada' => 0,
        'paletes_saida' => 0,
        'valor_entrada' => 0.0,
        'valor_saida' => 0.0
    ];
}

foreach ($movimentacoes as $mov) {
    $dEmi = $mov['data_emissao'] ?? '';
    if (empty($dEmi) || !isset($dailyData[$dEmi])) continue;

    $doc = sanitize_doc($mov['cliente_documento'] ?? ($mov['destinatario_documento'] ?? ''));
    if (!empty($clienteDocFilter) && $doc !== $clienteDocFilter) continue;

    $tipo = strtoupper($mov['tipo'] ?? 'SAIDA');
    $valorNfe = (float)($mov['valor_nfe'] ?? 0);

    $paletesNf = 0;
    if (!empty($mov['itens']) && is_array($mov['itens'])) {
        foreach ($mov['itens'] as $item) {
            $codigo = $item['codigo'] ?? '';
            $qtd = (float)($item['quantidade'] ?? 0);

            $compositeKey = $doc . '|' . $codigo;
            $fator = null;
            if (isset($produtosMap[$compositeKey])) {
                $fator = $produtosMap[$compositeKey]['fator_palete'];
            } elseif (isset($produtosMap[$codigo])) {
                $fator = $produtosMap[$codigo]['fator_palete'];
            }

            $calc = calculate_item_pallets($qtd, $fator);
            $paletesNf += $calc['ceil'];
        }
    }

    if ($tipo === 'ENTRADA') {
        $dailyData[$dEmi]['paletes_entrada'] += $paletesNf;
        $dailyData[$dEmi]['valor_entrada'] += $valorNfe;
    } else {
        $dailyData[$dEmi]['paletes_saida'] += $paletesNf;
        $dailyData[$dEmi]['valor_saida'] += $valorNfe;
    }
}

$templatePath = __DIR__ . '/modelo.xlsx';
if (!file_exists($templatePath)) {
    die("Erro: Arquivo modelo.xlsx não encontrado no diretório do projeto.");
}

$filename = sprintf('fechamento_%s_%02d_%04d.xlsx', !empty($clienteDocFilter) ? $clienteDocFilter : 'todos', $month, $year);

// Converter string de data YYYY-MM-DD para serial do Excel
function helper_date_to_excel_serial($dateStr) {
    $timestamp = strtotime($dateStr . ' 00:00:00 UTC');
    return 25569 + ($timestamp / 86400);
}

// -------------------------------------------------------------
// ABORDAGEM 1: Usando PhpSpreadsheet se disponível
// -------------------------------------------------------------
if (class_exists('PhpOffice\PhpSpreadsheet\IOFactory')) {
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($templatePath);
    $sheet = $spreadsheet->getActiveSheet();

    // C2: Data inicial do mês
    $sheet->setCellValue('C2', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(new DateTime($startDate)));

    for ($d = 1; $d <= $totalDays; $d++) {
        $row = 5 + $d;
        $dateKey = sprintf('%04d-%02d-%02d', $year, $month, $d);
        $dt = new DateTime($dateKey);

        $sheet->setCellValue('B' . $row, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($dt));
        $sheet->setCellValue('D' . $row, (int)$dailyData[$dateKey]['paletes_entrada']);
        $sheet->setCellValue('E' . $row, (int)$dailyData[$dateKey]['paletes_saida']);
        $sheet->setCellValue('J' . $row, (float)$dailyData[$dateKey]['valor_entrada']);
        $sheet->setCellValue('K' . $row, (float)$dailyData[$dateKey]['valor_saida']);
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
    exit;
}

// -------------------------------------------------------------
// ABORDAGEM 2: Edição Nativa via ZipArchive OpenXML (Fallback sem dependência)
// -------------------------------------------------------------
$tempCopy = __DIR__ . '/data/temp_export_' . uniqid() . '.xlsx';
copy($templatePath, $tempCopy);

$zip = new ZipArchive();
if ($zip->open($tempCopy) === true) {
    $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');

    if ($sheetXml !== false) {
        $dom = new DOMDocument();
        $dom->preserveWhiteSpace = false;
        $dom->formatOutput = false;
        $dom->loadXML($sheetXml);

        $xpath = new DOMXPath($dom);
        $xpath->registerNamespace('s', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');

        // Atualizar C2 (Data inicial do mês)
        $startSerial = helper_date_to_excel_serial($startDate);
        $c2Nodes = $xpath->query('//s:c[@r="C2"]');
        if ($c2Nodes->length > 0) {
            $c2Node = $c2Nodes->item(0);
            $c2Node->removeAttribute('t');
            $vNode = $c2Node->getElementsByTagName('v')->item(0);
            if (!$vNode) {
                $vNode = $dom->createElement('v');
                $c2Node->appendChild($vNode);
            }
            $vNode->nodeValue = (string)$startSerial;
        }

        // Atualizar linhas de 6 até (5 + totalDays)
        for ($d = 1; $d <= $totalDays; $d++) {
            $rowNum = 5 + $d;
            $dateKey = sprintf('%04d-%02d-%02d', $year, $month, $d);
            $daySerial = helper_date_to_excel_serial($dateKey);
            $pEnt = (int)$dailyData[$dateKey]['paletes_entrada'];
            $pSai = (int)$dailyData[$dateKey]['paletes_saida'];
            $vEnt = (float)$dailyData[$dateKey]['valor_entrada'];
            $vSai = (float)$dailyData[$dateKey]['valor_saida'];

            $valuesToSet = [
                'B' => ['val' => $daySerial, 'type' => 'number'],
                'D' => ['val' => $pEnt, 'type' => 'number'],
                'E' => ['val' => $pSai, 'type' => 'number'],
                'J' => ['val' => $vEnt, 'type' => 'number'],
                'K' => ['val' => $vSai, 'type' => 'number'],
            ];

            foreach ($valuesToSet as $col => $info) {
                $cellRef = $col . $rowNum;
                $cellNodes = $xpath->query('//s:c[@r="' . $cellRef . '"]');
                if ($cellNodes->length > 0) {
                    $cellNode = $cellNodes->item(0);
                    $cellNode->removeAttribute('t');
                    $vNode = $cellNode->getElementsByTagName('v')->item(0);
                    if (!$vNode) {
                        $vNode = $dom->createElement('v');
                        $cellNode->appendChild($vNode);
                    }
                    $vNode->nodeValue = (string)$info['val'];
                }
            }
        }

        $zip->addFromString('xl/worksheets/sheet1.xml', $dom->saveXML());
    }
    $zip->close();
}

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Content-Length: ' . filesize($tempCopy));
header('Cache-Control: max-age=0');

readfile($tempCopy);
@unlink($tempCopy);
exit;