290 lines
11 KiB
PHP
290 lines
11 KiB
PHP
<?php
|
|
|
|
namespace HermannTelser\Gutachten\Downloads;
|
|
|
|
|
|
use HermannTelser\Gutachten\DB\DbFunction;
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
// Mpdf installed by typo3 extension
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
use PhpOffice\PhpSpreadsheet\Style\Border;
|
|
use PhpOffice\PhpSpreadsheet\Style\Color;
|
|
use PhpOffice\PhpSpreadsheet\Style\Fill;
|
|
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
|
use TYPO3\CMS\Core\Utility\GeneralUtility;
|
|
use HermannTelser\Gutachten\Utility\Message;
|
|
|
|
class ExcelAufstellung
|
|
{
|
|
/**
|
|
* @return array
|
|
*/
|
|
public static function download()
|
|
{
|
|
// Message::debug(__file__,__line__, "DOWNLOAD SPREAD SHEET AUFSTELLUNG");
|
|
|
|
// DbFunction::$debug = 1;
|
|
|
|
$local_debug = 0;
|
|
|
|
$projektid = GeneralUtility::_GP('projekt_aktive');
|
|
if (0 == $projektid or !is_numeric($projektid)) {
|
|
die('aktive projekt ID fehlt!');
|
|
}
|
|
|
|
$_aufstellung = DbFunction::selectJoin(
|
|
'tx_gutachten_raume_generell',
|
|
['[>]tx_gutachten_aufstellung' => ['tx_gutachten_raume_generell.aufstellung_id' => 'id']],
|
|
[
|
|
'tx_gutachten_raume_generell.raum',
|
|
'tx_gutachten_raume_generell.aufstellung_id',
|
|
'tx_gutachten_aufstellung.titel'
|
|
],
|
|
[
|
|
'AND' =>
|
|
[
|
|
'tx_gutachten_raume_generell.pid' => $projektid,
|
|
'tx_gutachten_raume_generell.aufstellung_id[!]' => 0
|
|
]
|
|
]
|
|
);
|
|
|
|
|
|
$_aufstellung_map = [];
|
|
$_aufstellung_header = [];
|
|
foreach ($_aufstellung as $key => $value) {
|
|
$_aufstellung_map[$value['raum']] = $value['titel'];
|
|
if(!in_array($value['titel'], $_aufstellung_header)){
|
|
$_aufstellung_header[] = $value['titel'];
|
|
}
|
|
}
|
|
|
|
if(!$_aufstellung_map){
|
|
Message::note('Die "Aufstellung Titel" fehlen, bitte bei Methoden hinzufügen (Top Menu).');
|
|
exit;
|
|
}
|
|
|
|
if($local_debug){
|
|
print_r($_aufstellung_header);
|
|
}
|
|
|
|
|
|
$_projekt = DbFunction::select(
|
|
'tx_gutachten_projekte',
|
|
['id', 'auftraggeber_id', 'gutachter_id', 'intname', 'name', 'beschreibung', 'erstellung', 'onoff', 'upload', 'allgemeinupload', 'recalculate', 'zuabTitel', 'nwbTitel', 'zustTitel', 'allgemTitel', 'loggia_title', 'nwbStartSeite', 'm1pP', 'm5pP', 'timestamp', 'status', 'erstelldatum'],
|
|
['id' => $projektid]
|
|
);
|
|
$projektname = str_replace(',', '', $_projekt[0]['name']);
|
|
$projektname = preg_replace("'\W, '", '', $projektname) . ' ' . date('d. m. y. H:i:s');
|
|
|
|
if($local_debug){
|
|
print_r($_projekt);
|
|
}
|
|
|
|
$join = [];
|
|
$join['[>]tx_gutachten_raume_all']['tx_gutachten_raume_basis.id'] = 'tt_raume_basis_id';
|
|
$join['[>]tx_gutachten_orientierung']['tx_gutachten_raume_basis.tt_orientierung_id'] = 'id';
|
|
|
|
$fields = [];
|
|
$fields[] = 'tx_gutachten_raume_basis.id';
|
|
$fields[] = 'tx_gutachten_raume_basis.topNr';
|
|
$fields[] = 'tx_gutachten_raume_basis.typ';
|
|
$fields[] = 'tx_gutachten_orientierung.orientierung';
|
|
$fields[] = 'tx_gutachten_raume_all.geschoss';
|
|
$fields[] = 'tx_gutachten_raume_all.raum';
|
|
$fields[] = 'tx_gutachten_raume_all.qm';
|
|
$fields[] = 'tx_gutachten_raume_all.tt_raumart_id';
|
|
|
|
$where = [];
|
|
$where['AND']['tx_gutachten_raume_basis.pid'] = $projektid;
|
|
// $where['AND']['tx_gutachten_raume_basis.id'] = 43538;
|
|
$where['AND']['tx_gutachten_raume_all.tt_raumart_id'] = [1, 2, 3, 4, 7];
|
|
$where['AND']['tx_gutachten_raume_basis.noDispInOrientierung'] = 0;
|
|
$where['ORDER']['tx_gutachten_raume_basis.topNr'] = 'ASC';
|
|
$where['ORDER']['tx_gutachten_raume_all.raum'] = 'ASC';
|
|
// $where['LIMIT'] = 2;
|
|
$_raeume_alle = DbFunction::selectJoin(
|
|
'tx_gutachten_raume_basis',
|
|
$join,
|
|
$fields,
|
|
$where
|
|
);
|
|
|
|
// print_r($_raeume_alle);
|
|
// exit;
|
|
|
|
// [id] => 79543
|
|
// [topNr] => W01
|
|
// [orientierung] => N/W
|
|
// [geschoss] => EG
|
|
// [raum] => AR
|
|
// [qm] => 0.75
|
|
// [tt_raumart_id] => 1
|
|
|
|
$_sorted = [];
|
|
$_header = [];
|
|
$_header4 = [];
|
|
$_header7 = [];
|
|
foreach ($_raeume_alle as $row) {
|
|
$tt_raume_basis_id = $row['id'];
|
|
$_sorted[$tt_raume_basis_id]['Top'] = $row['topNr'];
|
|
$_sorted[$tt_raume_basis_id]['Geschoß'] = $row['geschoss'];
|
|
$_sorted[$tt_raume_basis_id]['Orientierung'] = $row['orientierung'];
|
|
$_sorted[$tt_raume_basis_id]['Type'] = $row['typ'];
|
|
$raum = $row['raum'];
|
|
$_sorted[$tt_raume_basis_id][$raum] = $row['qm'];
|
|
if($row['tt_raumart_id'] == 1)
|
|
$_sorted[$tt_raume_basis_id]['Summe Nutzflächen'] += $row['qm'];
|
|
if ($row['tt_raumart_id'] == 1){
|
|
$_header[] = $row['raum'];
|
|
}
|
|
else if ($row['tt_raumart_id'] == 4)
|
|
$_header4[$tt_raume_basis_id][] = $row['raum'];
|
|
else if ($row['tt_raumart_id'] == 7)
|
|
$_header7[$tt_raume_basis_id][] = $row['raum'];
|
|
|
|
}
|
|
|
|
$_header = array_unique($_header);
|
|
sort($_header);
|
|
array_unshift($_header, 'Top', 'Geschoß', 'Orientierung', 'Type');
|
|
array_push($_header, 'Summe Nutzflächen');
|
|
$_header = array_merge($_header, $_aufstellung_header);
|
|
// print_r($_header);
|
|
|
|
$_header_char = [];
|
|
$char = 'A';
|
|
$summe_position = '';
|
|
foreach ($_header as $value) {
|
|
if ($value == 'Summe Nutzflächen') {
|
|
$summe_position = $char;
|
|
}
|
|
$_header_char[$char] = $value;
|
|
++$char;
|
|
}
|
|
$tmp = array_flip($_header_char);
|
|
|
|
if($local_debug){
|
|
print_r($_aufstellung_map);
|
|
print_r($tmp);
|
|
}
|
|
|
|
$_final = [];
|
|
foreach ($_sorted as $key => $flat) {
|
|
|
|
// print_r($flat);
|
|
|
|
|
|
foreach ($flat as $name => $value) {
|
|
|
|
if(isset($_aufstellung_map[$name])){
|
|
$name = $_aufstellung_map[$name];
|
|
}
|
|
|
|
// if(!isset($tmp[$name])){
|
|
// echo "\n" . $name . "\n";
|
|
// print_r($_header4);
|
|
// }
|
|
|
|
$char = $tmp[$name];
|
|
if(isset($_final[$key][$char])){
|
|
$_final[$key][$char] += $value;
|
|
}
|
|
else {
|
|
$_final[$key][$char] = $value;
|
|
}
|
|
|
|
}
|
|
ksort($_final[$key]);
|
|
// print_r($_final);
|
|
// exit;
|
|
}
|
|
// print_r($_final);
|
|
// exit;
|
|
|
|
|
|
$spreadsheet = new Spreadsheet();
|
|
$spreadsheet->getProperties()->setCreator('Architekt Vogl-Fernheim')->setTitle('Nutzwerte Aufstellung')->setDescription($projektname . ", erstellt am " . date("d.M.Y"));
|
|
$activeSheet = $spreadsheet->getActiveSheet();
|
|
$activeWorksheet = $spreadsheet->getActiveSheet();
|
|
|
|
foreach ($_header_char as $coll => $value) {
|
|
$cell = $coll . 1;
|
|
$activeWorksheet->getStyle($cell)->getAlignment()->setTextRotation(90);
|
|
$activeWorksheet->getRowDimension('1')->setRowHeight(180);
|
|
$activeWorksheet->setCellValue($cell, $value);
|
|
$cellStyle = $activeWorksheet->getStyle($cell);
|
|
$cellStyle->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
|
|
$cellStyle->getFill()->setFillType(Fill::FILL_SOLID);
|
|
if (in_array($coll, ['A', 'B', 'C', 'D', $summe_position])) {
|
|
$cellStyle->getFill()->getStartColor()->setARGB('00000000');
|
|
} else {
|
|
$cellStyle->getFill()->getStartColor()->setARGB('00595959');
|
|
}
|
|
|
|
$cellStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
|
|
$cellStyle->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
|
|
// $activeWorksheet->getStyle($cell)->getBorders()->getOutline()->setBorderStyle(Border::BORDER_HAIR)->setColor(new Color('FFFFFFFF'));
|
|
}
|
|
|
|
foreach ($_header_char as $coll => $value) {
|
|
$cell = $coll . 2;
|
|
$cellStyle = $activeWorksheet->getStyle($cell);
|
|
$cellStyle->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
|
|
$cellStyle->getFill()->setFillType(Fill::FILL_SOLID);
|
|
if (in_array($coll, ['A', 'B', 'C', 'D', $summe_position])) {
|
|
$cellStyle->getFill()->getStartColor()->setARGB('00000000');
|
|
} else {
|
|
$cellStyle->getFill()->getStartColor()->setARGB('00595959');
|
|
}
|
|
if (!in_array($coll, ['A', 'B', 'C', 'D'])) {
|
|
$activeWorksheet->setCellValue($cell, 'm²');
|
|
}
|
|
$cellStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
|
|
// $activeWorksheet->getStyle($cell)->getBorders()->getOutline()->setBorderStyle(Border::BORDER_HAIR)->setColor(new Color('FFFFFFFF'));
|
|
}
|
|
$row = '3';
|
|
// print_r($_final);
|
|
// exit;
|
|
foreach ($_final as $id => $einheit) {
|
|
foreach ($einheit as $colum => $value) {
|
|
$cell = $colum . $row;
|
|
$activeWorksheet->setCellValue($cell, $value);
|
|
$cellStyle = $activeWorksheet->getStyle($cell);
|
|
|
|
if (in_array($colum, ['A', $summe_position])) {
|
|
$cellStyle->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
|
|
$cellStyle->getFill()->setFillType(Fill::FILL_SOLID);
|
|
$cellStyle->getFill()->getStartColor()->setARGB('00000000');
|
|
// $activeWorksheet->getStyle($cell)->getBorders()->getOutline()->setBorderStyle(Border::BORDER_HAIR)->setColor(new Color('FFFFFFFF'));
|
|
} else if (in_array($colum, ['B', 'C'])) {
|
|
$cellStyle->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
|
|
$cellStyle->getFill()->setFillType(Fill::FILL_SOLID);
|
|
$cellStyle->getFill()->getStartColor()->setARGB('00595959');
|
|
// $activeWorksheet->getStyle($cell)->getBorders()->getOutline()->setBorderStyle(Border::BORDER_HAIR)->setColor(new Color('FFFFFFFF'));
|
|
} else {
|
|
// $activeWorksheet->getStyle($cell)->getBorders()->getOutline()->setBorderStyle(Border::BORDER_HAIR)->setColor(new Color('00000000'));
|
|
}
|
|
}
|
|
++$row;
|
|
}
|
|
|
|
header('Content-Type: application/vnd.ms-excel');
|
|
header('Content-Disposition: attachment;filename="' . $projektname . '.xlsx"');
|
|
header('Cache-Control: max-age=0');
|
|
|
|
// If you're serving to IE over SSL, then the following may be needed
|
|
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
|
|
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
|
|
header('Pragma: public'); // HTTP/1.0
|
|
|
|
// Xls
|
|
// $writer = new Xlsx($spreadsheet);
|
|
// $writer->save('hello world.xlsx');
|
|
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
|
|
$writer->save('php://output');
|
|
exit;
|
|
}
|
|
}
|