Allotment Request Form DateTime Group Problem

Question

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(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . $rowHeader)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . $rowHeader)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . $row)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
            $sheet->getStyle($columnName . $rowHeader)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
            $sheet->getStyle($columnName . '2')->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
            $sheet->getStyle($columnName . '2')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($columnName . '2')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::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' => \PhpOffice\PhpSpreadsheet\Style\Fill::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.

Answer 1

It was tested, let me know if not work

$resultSet = array(
    array('id'=>426, 'hcode'=>'ANDEI', 'hname'=>'Hotel Name', 'rtype'=>'Hotel Code', 'mailsubject'=>'01.10.2023', 'mail'=>'[email protected]', 'total_kontenjan'=>3, 'dolu_kontenjan'=>3),
    array('id'=>427, 'hcode'=>'ANDEI', 'hname'=>'Hotel Name', 'rtype'=>'Hotel Code', 'mailsubject'=>'02.10.2023', 'mail'=>'[email protected]', 'total_kontenjan'=>3, 'dolu_kontenjan'=>3),
    array('id'=>428, 'hcode'=>'ANDEI', 'hname'=>'Hotel Name', 'rtype'=>'Hotel Code', 'mailsubject'=>'03.10.2023', 'mail'=>'[email protected]', 'total_kontenjan'=>3, 'dolu_kontenjan'=>3),
    array('id'=>429, 'hcode'=>'ANDEI', 'hname'=>'Hotel Name', 'rtype'=>'Hotel Code', 'mailsubject'=>'04.10.2023', 'mail'=>'[email protected]', 'total_kontenjan'=>3, 'dolu_kontenjan'=>3),
    array('id'=>430, 'hcode'=>'ANDEI', 'hname'=>'Hotel Name', 'rtype'=>'Hotel Code', 'mailsubject'=>'06.10.2023', 'mail'=>'[email protected]', 'total_kontenjan'=>4, 'dolu_kontenjan'=>4),
    array('id'=>431, 'hcode'=>'ANDEI', 'hname'=>'Hotel Name', 'rtype'=>'Hotel Code', 'mailsubject'=>'07.10.2023', 'mail'=>'[email protected]', 'total_kontenjan'=>4, 'dolu_kontenjan'=>4),
    array('id'=>436, 'hcode'=>'ANDEI', 'hname'=>'Hotel Name', 'rtype'=>'Hotel Code', 'mailsubject'=>'03.11.2023', 'mail'=>'[email protected]', 'total_kontenjan'=>4, 'dolu_kontenjan'=>4)
);

print_r($resultSet);


$splitter = '|';
$previousHead = null;
$groupedResult = array();
$tempRow = array();
foreach ($resultSet as $row) {
    $thisHead = $row['hname'] . $splitter . $row['rtype']; // append all the columns to identify the same room or not
    if ($previousHead == null) {
        $row['toDate'] = $row['mailsubject']; // set ToDate equals to FromDate (mailsubject)
        $tempRow = $row;
    } elseif ($previousHead != null && $previousHead == $thisHead) {
        $nextDay = date("d.m.Y", strtotime("+1day", strtotime($tempRow['toDate'])));
        if ($nextDay == $row['mailsubject']) {
            // current row continues with the previous, therefore, update the toDate
            $tempRow['toDate'] = $nextDay;
        } else {
            array_push($groupedResult, $tempRow);
            $row['toDate'] = $row['mailsubject']; // set ToDate equals to FromDate (mailsubject)
            $tempRow = $row;
        }
    }
    $previousHead = $thisHead;
}
if( !empty($tempRow) ){
    array_push($groupedResult, $tempRow); // the last row
}

print_r($groupedResult);

And here is the grouped Result

Array
(
    [0] => Array
        (
            [id] => 426
            [hcode] => ANDEI
            [hname] => Hotel Name
            [rtype] => Hotel Code
            [mailsubject] => 01.10.2023
            [mail] => [email protected]
            [total_kontenjan] => 3
            [dolu_kontenjan] => 3
            [toDate] => 04.10.2023
        )

    [1] => Array
        (
            [id] => 430
            [hcode] => ANDEI
            [hname] => Hotel Name
            [rtype] => Hotel Code
            [mailsubject] => 06.10.2023
            [mail] => [email protected]
            [total_kontenjan] => 4
            [dolu_kontenjan] => 4
            [toDate] => 07.10.2023
        )

    [2] => Array
        (
            [id] => 436
            [hcode] => ANDEI
            [hname] => Hotel Name
            [rtype] => Hotel Code
            [mailsubject] => 03.11.2023
            [mail] => [email protected]
            [total_kontenjan] => 4
            [dolu_kontenjan] => 4
            [toDate] => 03.11.2023
        )

)

The From Date using mailsubject and the To Date using toDate

Answer 2

Solved.

    usort($veriExcel, function($a, $b) {
    $odaTipSonuc = strcmp($a->rtype, $b->rtype);
    
    if ($odaTipSonuc == 0) {
        $tarihA = strtotime(str_replace('.', '-', $a->mailsubject));
        $tarihB = strtotime(str_replace('.', '-', $b->mailsubject));
        
        if ($tarihA == $tarihB) {
            return 0;
        }
        return ($tarihA < $tarihB) ? -1 : 1;
    }
    
    return $odaTipSonuc;
});

$birlestirilmisVeri = [];
$birlestirilmisVeri[] = $veriExcel[0]; // İlk satırı başlangıç olarak ekleyin

for ($i = 1; $i < count($veriExcel); $i++) {
    $onceki = strtotime(str_replace('.', '-', $veriExcel[$i - 1]->mailsubject));
    $suanki = strtotime(str_replace('.', '-', $veriExcel[$i]->mailsubject));
    $birGun = 24 * 60 * 60; // 1 günün saniye cinsinden değeri

    if ($suanki - $onceki === $birGun && $veriExcel[$i - 1]->hcode === $veriExcel[$i]->hcode) {
        if ($veriExcel[$i - 1]->total_kontenjan === $veriExcel[$i]->total_kontenjan &&
            $veriExcel[$i - 1]->dolu_kontenjan === $veriExcel[$i]->dolu_kontenjan) {
            $birlestirilmisVeri[count($birlestirilmisVeri) - 1]->mailsubject .= ' - ' . $veriExcel[$i]->mailsubject;
        } else {
            $birlestirilmisVeri[] = $veriExcel[$i];
        }
    } else {

        $birlestirilmisVeri[] = $veriExcel[$i];
    }
}
$row = 3;
$rowHeader = 1;
$alternateColors = ['#FFFFFF', '#D9D9D9'];
$currentColorIndex = 0;
foreach ($birlestirilmisVeri as $veriex) {
    $otelKodu = $veriex->hcode;
    $otelAdi = $veriex->hname;
    $odaTipi = $veriex->rtype;
    $dolulukTarihAraliklari = $veriex->mailsubject;
    $totalKontenjan = $veriex->total_kontenjan;
    $doluKontenjan = $veriex->dolu_kontenjan;
    $tarihAraligi = explode(' - ', $veriex->mailsubject);
    $baslangicTarihi = $tarihAraligi[0];
    $bitisTarihi = end($tarihAraligi);
    $sheet->setCellValue('A' . $row, $otelKodu);
    $sheet->setCellValue('B' . $row, $otelAdi);
    $sheet->setCellValue('C' . $row, $odaTipi);
    $sheet->setCellValue('D' . $row, $baslangicTarihi); // Başlangıç tarihini D sütununa yazın
    $sheet->setCellValue('E' . $row, $bitisTarihi); // Bitiş tarihini E sütununa yazın
    $sheet->setCellValue('F' . $row, $totalKontenjan);
    $sheet->setCellValue('G' . $row, $doluKontenjan);

Thank you very much for the suggestions for help.