<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportController extends Controller
{
    public function index()
    {
        $output = [
            "page_controller" => $this->page_controller,
            "page_route" => $this->page_route,
            "page_button" => "",
            "page_title" => "Dashboard",
            "sales_list" => $sales_list,
            "total" => $dashboard_model->getAchievement()
        ];

        echo view('export_form', $output); // Chargez un formulaire HTML similaire à celui fourni
    }

    public function export()
    {
        $startDate = $this->request->getPost('start_date');
        $endDate = $this->request->getPost('end_date');

        if (!$startDate || !$endDate) {
            return redirect()->back()->with('error', 'Veuillez fournir une date de début et une date de fin.');
        }

        $db = \Config\Database::connect();
        $sql = "SELECT DISTINCT `N° Commande` 
        FROM articlesExpeditions 
        WHERE `Statut expédition` = :status: 
        AND `Date expédition` BETWEEN :start_date: AND :end_date:";

        $query = $db->query($sql, [
            'status'     => 'Expédié',
            'start_date' => $startDate,
            'end_date'   => $endDate,
        ]);

        $expeditions = $query->getResultArray();

        if (empty($expeditions)) {
            return redirect()->back()->with('error', 'Aucune commande expédiée trouvée pour la période sélectionnée.');
        }

        $commandNumbers = array_column($expeditions, 'N° Commande');

        $db = \Config\Database::connect();

// Créez la requête SQL brute
        $sql = "
    SELECT 
        `N° Client` AS Client_Id,
        `Réf Article` AS Ref_Article,
        `Désignation`,
        `ML`,
        `MG`,
        SUM(`Qté Commandées`) AS Quantité
    FROM articlesCommandesClient
    WHERE `N° Commande` IN (" . implode(',', array_map(fn($cmd) => $db->escape($cmd), $commandNumbers)) . ")
    GROUP BY `N° Client`, `Réf Article`, `Désignation`, `ML`, `MG`
    ORDER BY `Réf Article`
";

// Exécutez la requête brute
        $query = $db->query($sql);
        $articles = $query->getResultArray();


        if (empty($articles)) {
            return redirect()->back()->with('error', 'Aucun article trouvé pour les commandes expédiées.');
        }

        $spreadsheet = new Spreadsheet();

        // Feuille A
        $sheetA = $spreadsheet->getActiveSheet();
        $sheetA->setTitle('Feuille A');
        $sheetA->fromArray(['Client Id', 'Réf Article', 'Désignation', 'ML', 'MG', 'Quantité'], NULL, 'A1');
        $sheetA->fromArray($articles, NULL, 'A2');

        // Feuille B
        $sheetB = $spreadsheet->createSheet();
        $sheetB->setTitle('Feuille B');
        $sheetB->fromArray(['Client Id', 'Réf Article', 'Désignation', 'ML', 'MG', 'Quantité Totale'], NULL, 'A1');
        $sheetB->fromArray($articles, NULL, 'A2');

        $fileName = "Export_Commandes_" . date('Ymd_His') . ".xlsx";

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

        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }
}
