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.