Php – Allotment Request Form DateTime Group Problem

I am preparing a project, I am preparing an allocation form, but I am having a problem,

While creating the table, I want to group the incoming date data according to the quota data. So let me explain it like this:

Current uutput:

The output I want:

The code block where I make Excel operations:

/////////////////////// EXCEL ISLEMLERI
    $hcode = $veri[0]->hcode;
    $rtype = $veri[0]->rtype;
    $mailsubject = $veri[0]->mailsubject;
    $sendMailQuery = "SELECT * FROM vakanzsendmail WHERE hcode = ? AND rtype = ? AND mailsubject = ?";
    $sendMailParams = array($hcode, $rtype, $mailsubject);
    $sendMailResult = HALIL::sorgula($sendMailQuery, $sendMailParams);
    $veriExcel = HALIL::sorgula("SELECT * FROM vakanzfillrooms WHERE hcode = ?", array($hcode));
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setCellValue('A1', 'BCH');
    $sheet->setCellValue('B1', 'Allotment Request Form');
    $sheet->setCellValue('F1', "Sold" . PHP_EOL . "Rooms");
    $sheet->getStyle('F1')->getAlignment()->setWrapText(true);
    $sheet->setCellValue('G1', "Total" . PHP_EOL . "Allotment");
    $sheet->getStyle('G1')->getAlignment()->setWrapText(true);
    $sheet->setCellValue('H1', "Please fill" . PHP_EOL . "up" . PHP_EOL . "Extra Allot.");
    $sheet->getStyle('H1')->getAlignment()->setWrapText(true);
    $columnHeaders2 = ['Hcode', 'Hotel Name', 'Room Type', 'Date Ranges'];
    $col2 = 'A';
    foreach ($columnHeaders2 as $header2) {
        $sheet->setCellValue($col2 . '2', $header2);
        $col2++;
    }
    function customSort($a, $b) {
        $odaTipiCompare = strcmp($a->rtype, $b->rtype);
        if ($odaTipiCompare == 0) {
            $dateA = strtotime(str_replace('.', '-', $a->mailsubject));
            $dateB = strtotime(str_replace('.', '-', $b->mailsubject));

            return $dateA - $dateB;
        }

        return $odaTipiCompare;
    }
    usort($veriExcel, 'customSort');
    $row = 3;
    $rowHeader = 1;
    $alternateColors = ['#FFFFFF', '#D9D9D9'];
    $currentColorIndex = 0;
    foreach ($veriExcel as $veriex) {
        $otelKodu = $veriex->hcode;
        $otelAdi = $veriex->hname;
        $odaTipi = $veriex->rtype;
        $dolulukTarihAraliklari = $veriex->mailsubject;
        $totalKontenjan = $veriex->total_kontenjan;
        $doluKontenjan = $veriex->dolu_kontenjan;
        $sheet->setCellValue('A' . $row, $otelKodu);
        $sheet->setCellValue('B' . $row, $otelAdi);
        $sheet->setCellValue('C' . $row, $odaTipi);
        $sheet->setCellValue('D' . $row, $dolulukTarihAraliklari);
        $sheet->setCellValue('E' . $row, $dolulukTarihAraliklari);
        $sheet->setCellValue('F' . $row, $totalKontenjan);
        $sheet->setCellValue('G' . $row, $doluKontenjan);
        foreach (range('A', 'H') as $columnName) {
            $sheet->getStyle($columnName . $row)->getAlignment()->setHorizontal(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . $row)->getAlignment()->setVertical(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . $rowHeader)->getAlignment()->setHorizontal(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . $rowHeader)->getAlignment()->setVertical(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . $row)->getBorders()->getAllBorders()->setBorderStyle(PhpOfficePhpSpreadsheetStyleBorder::BORDER_THIN);
            $sheet->getStyle($columnName . $rowHeader)->getBorders()->getAllBorders()->setBorderStyle(PhpOfficePhpSpreadsheetStyleBorder::BORDER_THIN);
            $sheet->getStyle($columnName . '2')->getBorders()->getAllBorders()->setBorderStyle(PhpOfficePhpSpreadsheetStyleBorder::BORDER_THIN);
            $sheet->getStyle($columnName . '2')->getAlignment()->setHorizontal(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . '2')->getAlignment()->setVertical(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
            
        }
        $sheet->getStyle('A' . $rowHeader . ':H' . $rowHeader)->getFont()->setBold(true);
        $sheet->getStyle('A' . $rowHeader . ':H' . $rowHeader)->getFont()->setSize(13);
        $sheet->getStyle('A1:H2')->getFont()->setBold(true);
        $sheet->getStyle('A1:H2')->getFont()->setSize(13);
        $colors = [
            0 => 'FFFFFF', // Durum 0 için beyaz
            1 => '54ff9f', // Durum 1 için yeşil
            2 => 'FFFF00', // Durum 2 için sarı
            3 => 'ff6a6a', // Durum 3 için kırmızı
        ];
        $queryDurum = "SELECT durum FROM vakanzhcode WHERE hcode = ? AND rtype = ? AND mailsubject = ?";
        $paramsDurum = array($otelKodu, $odaTipi, $dolulukTarihAraliklari);
        $resultDurum = HALIL::sorgula($queryDurum, $paramsDurum);
        if ($resultDurum && count($resultDurum) > 0) {
            $durum = $resultDurum[0]->durum;
        } else {
            $durum = 0;
        }
        $sheet->getStyle('A' . $row . ':H' . $row)->applyFromArray([
            'fill' => [
                'fillType' => PhpOfficePhpSpreadsheetStyleFill::FILL_SOLID,
                'startColor' => [
                    'rgb' => $colors[$durum], 
                ],
            ],
        ]);
        $sheet->mergeCells('B1:E1'); 
        $sheet->mergeCells('D2:E2'); 
        $sheet->mergeCells('F1:F2'); 
        $sheet->mergeCells('G1:G2'); 
        $sheet->mergeCells('H1:H2'); 
        
        $sheet->getRowDimension(1)->setRowHeight(40);
        $sheet->getColumnDimension('F')->setAutoSize(true);
        $sheet->getColumnDimension('G')->setAutoSize(true);
        $sheet->getColumnDimension('H')->setAutoSize(true);
        $cellWidths = [
            strlen($otelKodu) * 2.5,
            strlen($otelAdi) * 2,
            strlen($odaTipi) * 1.4,
            strlen($dolulukTarihAraliklari) * 2.5,
            strlen($dolulukTarihAraliklari) * 2.5,
        ];
        foreach (range('A', 'E') as $columnIndex => $columnName) {
            $sheet->getColumnDimension($columnName)->setWidth($cellWidths[$columnIndex]);
        }
        
        $row++;
    }
    $imagePath = 'imaj/logocsv.png';
    $drawing = new Drawing();
    $drawing->setName('Logo');
    $drawing->setDescription('Company Logo');
    $drawing->setPath($imagePath);
    $drawing->setCoordinates('A1');
    $drawing->setResizeProportional(true);
    $drawing->setWidthAndHeight(88, 54);
    $drawing->setWorksheet($sheet);
    $writer = new Xlsx($spreadsheet);
    $tarihFormati = date("d_m_y");
    $fileNameHotel = $veriExcel[0]->hname;
    $uniqueNumber = rand(100000, 999999);
    $fileName = "{$fileNameHotel}-{$tarihFormati}-{$uniqueNumber}-Occupancy-List.xlsx";
    $excelDosyaYolu = "vakanzfillroomsspreadsheets/{$fileName}";
    $writer->save($excelDosyaYolu);

/////////////////////// EXCEL ISLEMLERI

I tried a few methods but I could not reach the result, I give the example below from the database I pulled from sql.

INSERT INTO `vakanzfillrooms` (`id`, `hcode`, `hname`, `rtype`, `mailsubject`, `mail`, `total_kontenjan`, `dolu_kontenjan`) VALUES
(426, 'ANDEI', 'Hotel Name', 'Hotel Code', '01.10.2023', '[email protected]', 3, 3),
(427, 'ANDEI', 'Hotel Name', 'Hotel Code', '02.10.2023', '[email protected]', 3, 3),
(428, 'ANDEI', 'Hotel Name', 'Hotel Code', '03.10.2023', '[email protected]', 3, 3),
(429, 'ANDEI', 'Hotel Name', 'Hotel Code', '04.10.2023', '[email protected]', 3, 3),
(430, 'ANDEI', 'Hotel Name', 'Hotel Code', '06.10.2023', '[email protected]', 4, 4),
(431, 'ANDEI', 'Hotel Name', 'Hotel Code', '07.10.2023', '[email protected]', 4, 4),
(436, 'ANDEI', 'Hotel Name', 'Hotel Code', '03.11.2023', '[email protected]', 4, 4),

Let me share one of my experiments with you

    // Verileri tarihe göre sıralayın
    usort($veriExcel, 'customSort');

    // Gruplandırılmış verileri tutmak için bir dizi oluşturun
    $groupedData = [];
    $currentGroup = [];

    // Verileri gruplandırın
    foreach ($veriExcel as $veriex) {
        $otelKodu = $veriex->hcode;
        $otelAdi = $veriex->hname;
        $odaTipi = $veriex->rtype;
        $dolulukTarihAraliklari = $veriex->mailsubject;
        $totalKontenjan = $veriex->total_kontenjan;
        $doluKontenjan = $veriex->dolu_kontenjan;

        // Tarihi bir sonraki tarihe eklemek için strtotime kullanın
        if (empty($currentGroup) || strtotime($dolulukTarihAraliklari) == strtotime('+1 day', strtotime(end($currentGroup)['dolulukTarihAraliklari']))) {
            $currentGroup[] = [
                'otelKodu' => $otelKodu,
                'otelAdi' => $otelAdi,
                'odaTipi' => $odaTipi,
                'dolulukTarihAraliklari' => $dolulukTarihAraliklari,
                'totalKontenjan' => $totalKontenjan,
                'doluKontenjan' => $doluKontenjan,
            ];
        } else {
            // Eğer önceki tarih ile bir sonraki tarih bir gün aralıklı değilse, mevcut grup tamamlandı
            $groupedData[] = $currentGroup;
            $currentGroup = [
                [
                    'otelKodu' => $otelKodu,
                    'otelAdi' => $otelAdi,
                    'odaTipi' => $odaTipi,
                    'dolulukTarihAraliklari' => $dolulukTarihAraliklari,
                    'totalKontenjan' => $totalKontenjan,
                    'doluKontenjan' => $doluKontenjan,
                ]
            ];
        }
    }

    // Son gruplandırmayı ekleyin
    $groupedData[] = $currentGroup;

    // Geri kalan kod aynı kalabilir
    $row = 3;
    $rowHeader = 1;
    $alternateColors = ['#FFFFFF', '#D9D9D9'];
    $currentColorIndex = 0;
    // Verileri gruplandırılmış şekilde döngüye alın
    foreach ($groupedData as $group) {
        // Grubun her bir öğesini işleyin
        foreach ($group as $veriey) {
            $otelKodu = $veriey['otelKodu'];
            $otelAdi = $veriey['otelAdi'];
            $odaTipi = $veriey['odaTipi'];
            $dolulukTarihAraliklari = $veriey['dolulukTarihAraliklari'];
            $totalKontenjan = $veriey['totalKontenjan'];
            $doluKontenjan = $veriey['doluKontenjan'];

I tried to get it into the group in this way, but there was no change in the output.