<?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;
    }
}