<?php
/**
 * Export module - Excel
 * 
 * Main Word report generation page
 * 
 * @package linea21\utils\export
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 * Define, compose and generate a SDI (Sustainable Development Indicators) Word report
 */


# bench start tag
$start_time=getmicrotime();


// Code for spreadsheet 0.20 version
// https://github.com/PHPOffice/PhpSpreadsheet/releases/tag/1.20.0


/**
 * setDocProperties()
 * Set formatting document options
 * @param string $item
 * @return array
 */
function setDocProperties() {
    
    global $jsonSchema;
    
    // default options available
    $docOptions = [];
    $docOptions['zoomLevel'] = 100;
    $docOptions['freezePane'] = true;
    $docOptions['rowHeight'] = -1; // -1 for auto height - works only in Microsoft Office. Does not in LibreOffice - https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-a-rows-height
    $docOptions['cellAutoWidth'] = true;
    $docOptions['headerAutoFilter'] = true;
    $docOptions['wrapText'] = true;
    $docOptions['mainBackgroundColor'] = "FFFFFFFF";
    $docOptions['mainFontColor'] = "FF222222";
    $docOptions['mainFontSize'] = 12;
    $docOptions['mainFontBold'] = false;
    $docOptions['headerFontColor'] = "FF222222";
    $docOptions['headerFontSize'] = 12;
    $docOptions['headerFontBold'] = false;
    $docOptions['headerFontColor2'] = "FF222222";
    $docOptions['headerFontSize2'] = 12;
    $docOptions['headerFontBold2'] = false;
    $docOptions['headerBackgroundColor'] = "fffca841";
    $docOptions['headerBackgroundColor2'] = "ffd7a6ed";
    $docOptions['levelFontColor'] = "FF222222";
    $docOptions['levelFontSize'] = 12;
    $docOptions['levelFontBold'] = false;
    $docOptions['levelBackgroundColor'] = "fff0f0f0";
    $docOptions['borderColor'] = "FF59a0b7";
    
    foreach($jsonSchema->options as $entry) {

		$oprops = get_object_vars($entry);
		$key = array_key_first($oprops); // return element name such as 'mainBackgroundColor'

		// we overwrite value if exists		
		if (array_key_exists($key,$docOptions)) {
			$docOptions[$key] = $oprops[$key];
		}
    }
    
    return $docOptions;
    
}


/**
 * getProperties()
 * Access given properties of a given column
 * @param string $item
 * @return object|boolean
 */
function getColProperties(string $item ) {
    
    global $jsonSchema;
    
    $columns = $jsonSchema->columns;
    
    foreach($columns as $entry) {
        if($entry->item == $item && isset($entry->properties)) return $entry->properties;
    }
    
    return false;
    
}

/** Create a new Spreadsheet Object **/
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();


// https://phpspreadsheet.readthedocs.io/en/latest/topics/architecture/
// https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/
// https://phpoffice.github.io/PhpSpreadsheet/
// https://stackoverflow.com/questions/48681839/write-data-from-array-to-sheet-using-phpspreadsheet-library
// https://phpspreadsheet.readthedocs.io/en/latest/topics/accessing-cells/#setting-a-range-of-cells-from-an-array

// Adding document properties
$spreadsheet->getProperties()
->setCompany(SITE_NAME)
->setCreator('Linea21 '. LINEA_VERSION)
->setLastModifiedBy($report_settings['author'] . ' - ' .$report_settings['mail_author'])
->setTitle($report_settings['title'])
->setCreated(time())
->setModified(time())
->setSubject($report_settings['title']);

// Renaming default worksheet
$spreadsheet->getActiveSheet()->setTitle(mb_ucfirst(_t('report', 'projects')));

$mainsheet = $spreadsheet->getActiveSheet();


// This header works in association with /export/excel/project-schema.json file
$headerMapper = array(
    'level' => _t('project', 'level'),
    'id' => 'identifiant', 'name' => _t('project', 'name'), 'priority' => _t('project', 'priority'),
    'target' => _t('project', 'target'), 'goal' => _t('project', 'goal'), 'opgoal' => _t('project', 'opgoal'),
    'description' => _t('project', 'description'), 'body' => _t('project', 'body'), 'budget' => _t('project', 'budget'), 'budget_comment' => _t('project', 'budget_comment'),
    'elected' => _t('project', 'elected'), 'manager' => _t('project', 'manager'), 'coordinator' => _t('project', 'coordinator'), 'partners' => _t('project', 'partners'),
    'team' => _t('project', 'team'), 'achievement' => _t('project', 'achievement'), 'cal_begin_date' => _t('project', 'begin_date'), 'cal_estimated_date' => _t('project', 'estimated_date'),
    'cal_end_date' => _t('project', 'end_date'), 'cal_completion' => _t('project', 'completion'). ' (%)', 'cal_comment' => _t('project', 'cal_comment'),
    'parent' => _t('project', 'parent'), 'comment' => _t('project', 'comment'),
    'tasks'  => _t('project', 'tasks_title'), 'scale'  => _t('project', 'scale'), 
    'status' => _t('project', 'statut'), 'author' => _t('project', 'author'), 'finalities' => _t('project', 'finalities'),
    'indicators' => _t('project', 'sdi'), 'indicators-values' => _t('project', 'sdi_s')
);

// we remove html tags in headers
$headerMapper = array_map("strip_tags",$headerMapper);

// we dynamically get impacts items from $GLOBALS['lang']['project']['impactsItems'] and create key => value
$impacts = [];
if(is_array($GLOBALS['lang']['project']['impactsItems'])) {
	foreach($GLOBALS['lang']['project']['impactsItems'] as $key => $value) {
		$impacts['impact-'.$key] = _t('project', 'impacts') . ' - ' . $value . ' (x/'. PROJECT_IMPACTS .')';
	}
}
// And we do merge arrays !
$headerMapper = array_merge($headerMapper, $impacts);


// we include default files or user files
//ability to overwrite $headerMapper or merge values
$jsonSchema = json_decode(file_get_contents(override('../export/excel/project-schema.json', THEME_PUBLIC_PATH)));
include_once(override('../export/excel/excel.report.inc.php', THEME_PUBLIC_PATH));


// we set document properties based on given content
$docOptions = setDocProperties();

// background color
$mainsheet->getStyle('A:CZ')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB($docOptions['mainBackgroundColor']);


$col = 'A'; // we init first column
$itemsCols = array(); // we generate an associative array to place content in col such 'name' =>  'B'

foreach($jsonSchema->columns as &$entry) {
    
    // indicators-values are handle later
    if($entry->item != 'indicators-values') {
        $mainsheet->setCellValue($col.'1', mb_ucfirst($headerMapper[$entry->item]));
        
        $itemsCols[$entry->item] = $col;
        $entry->col = $col; // we add given column as property of current entry
        
        $lastCol = $col; // we determine the lastCol !
        $col++; // increment $col = 'B' if $col = 'A', and handle AA after Z
        
    }
    
}

$lastColBeforeIndicators = $lastCol; // we init $lastColBeforeIndicators


// we get the limit of indicators values and prepare column 
// we need to do it before runing the script on project because if not it will not merge correctly cells
$props = getColProperties('indicators-values');
if(isset($props->limit)) $maxIndicatorsLimit = $props->limit;

$indicatorsBounds = []; // we will use it later to style border to distinct better indicators columns
$indicatorsCols = []; // we will use it later to style width indicators columns

for($i=1; $i <= $maxIndicatorsLimit; $i++) {
    
    if($i != 1) $col++; // increment $col but not one first pass
    array_push($indicatorsBounds, $col);
    array_push($indicatorsCols, $col);
    
    $mainsheet->setCellValue($col.'1', mb_ucfirst($headerMapper['indicators-values']. ' '. $i .' - '. mb_ucfirst(_t('report', 'name'))));
    $col++; // increment $col = 'B' if $col = 'A', and handle AA after Z
    array_push($indicatorsCols, $col);
    $mainsheet->setCellValue($col.'1', mb_ucfirst($headerMapper['indicators-values']. ' '. $i .' - '. mb_ucfirst(_t('report', 'value'))));
    $col++; // increment $col = 'B' if $col = 'A', and handle AA after Z
    array_push($indicatorsCols, $col);
    $mainsheet->setCellValue($col.'1', mb_ucfirst($headerMapper['indicators-values']. ' '. $i .' - '. mb_ucfirst(_t('report', 'date'))));
    
    $lastCol = $col; // we determine the lastCol !
}


// we prepare var to get sorted objects (projects or indicators)
$ordered_items = array();


// if(!$report_settings['quick_export']) {
//      pour l'instant, export du programme d'actions en intégralité
//      pourquoi pas un jour, créer un modèle et permettre la création d'une fiche individuelle 
// }


// if($report_settings['summary']) {
// 	no summary on Excel file
// }

$currentLine=2; // to iterate on rows !
// SDI / dashboard report - not implemented yet !
if($report_settings['export_module'] == 'dashboard') {
    indicator_recursive(null);
    display_orphan_indicators ();
// project report
} else {
    $lastLine = project_recursive(null, $currentLine);
    $lastLine = display_orphan_projects ($lastLine);
}


//////// FORMATTING stylesheet
//////////////////////////////////////////////////////////

// if 'cellAutoWidth' == true, we apply it
if($docOptions['cellAutoWidth']) {
    foreach(range('A',$lastCol) as $columnID) {
        $mainsheet->getColumnDimension($columnID)->setAutoSize(true);
    }
}

// if 'cellWidth' is defined in properties of a given element we set column width
// this loop will not match indicators values
foreach($itemsCols as $key => $val) {
    $props = getColProperties($key);

    // https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-a-columns-width
    if(isset($props->cellWidth)) {
        // echo "$val : $props->cellWidth";
        $mainsheet->getColumnDimension($val)->setWidth((int) $props->cellWidth);
    }
}

// we loop will on 'indicators-values' columns
// if 'cellWidth' is defined in properties of 'indicators-values' element we set column width
$props = getColProperties('indicators-values');

foreach($indicatorsCols as $key => $val) {    
    // https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-a-columns-width
    if(isset($props->cellWidth)) {
        // echo "$val : $props->cellWidth";
        $mainsheet->getColumnDimension($val)->setWidth((int) $props->cellWidth);

    }
}

// Common style to all cells
// doc : https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#valid-array-keys-for-style-applyfromarray

$styleArray = [
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP
    ],
    'borders' => [
        'outline' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => $docOptions['borderColor'] ]
        ],
        'horizontal' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => $docOptions['borderColor'] ]
        ],
        'vertical' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => $docOptions['borderColor'] ]
        ]
    ]

];
// applying styles
$mainsheet->getStyle('A1:'.$lastCol.$lastLine)->applyFromArray($styleArray);
$mainsheet->getStyle('A1:'.$lastCol.$lastLine)->getAlignment()->setWrapText((bool) $docOptions['wrapText']);


// we freeze the first row by default if asked
if($docOptions['freezePane']) $mainsheet->freezePane('A2');

// applying auto-filter on first row if asked
if($docOptions['headerFontBold']) $mainsheet->setAutoFilter('A1:'.$lastCol.$lastLine);

// First line - header / projects
// doc : https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#valid-array-keys-for-style-applyfromarray
$styleArray = [
    'font' => [
        'bold' => (boolean) $docOptions['headerFontBold'],
        'size' => (string) $docOptions['headerFontSize'],
        'color' => ['argb' => $docOptions['headerFontColor'] ]
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'color' => ['argb' => $docOptions['headerBackgroundColor'] ]
    ]
];


$mainsheet->getStyle('A1:'.$lastColBeforeIndicators.'1')->applyFromArray($styleArray);
$mainsheet->getRowDimension('1')->setRowHeight($docOptions['rowHeight']);

//
$styleArray = [

    'borders' => [
        'left' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOUBLE,
            'color' => ['argb' => $docOptions['borderColor'] ]
        ]
    ]
    
];
// we add double border between each indicator to make them more readable
foreach ($indicatorsBounds as $val) {
    $mainsheet->getStyle($val.'1:'.$val.$lastLine)->applyFromArray($styleArray);
    // echo $val.'1:'.$val.$lastLine."<br>";
}


// First line - header / indicators
// doc : https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#valid-array-keys-for-style-applyfromarray
$styleArray = [
    'font' => [
        'bold' => (boolean) $docOptions['headerFontBold2'],
        'size' => (string) $docOptions['headerFontSize2'],
        'color' => ['argb' => $docOptions['headerFontColor2'] ]
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'color' => ['argb' => $docOptions['headerBackgroundColor2'] ]
    ]
];

$lastColBeforeIndicators++; // we increment to to the first indicator column before applying style

$mainsheet->getStyle($lastColBeforeIndicators.'1:'.$lastCol.'1')->applyFromArray($styleArray);
$mainsheet->getRowDimension('1')->setRowHeight($docOptions['rowHeight']);
// https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-worksheet-zoom-level
$mainsheet->getSheetView()->setZoomScale((int) $docOptions['zoomLevel']);
$mainsheet->getTabColor()->setRGB('ead16b');

// we finally make columns invisible if asked in config file
foreach($jsonSchema->columns as $col) {

    if(isset($col->properties->visible) && $col->properties->visible == false) $mainsheet->getColumnDimension($col->col)->setVisible(false);

}

# bench stop tag
$stop_time=getmicrotime();

$total_time=(float) $stop_time-$start_time;


#inclusion page d'infos de génération
if($report_settings['publi_infos']===true) include_once(override('../export/excel/excel.lastpage.php', THEME_PUBLIC_PATH));


if($report_settings['export_module'] == 'dashboard') {
    $file = _t('name', 'sdi_module'). '-'.date("d-m-Y_His").'.xlsx';
} else {
    $file = _t('name', 'project_module'). '-'.date("d-m-Y_His").'.xlsx';
}

/* Set active sheet index to the first sheet, so Excel opens this as the first sheet */
$spreadsheet->setActiveSheetIndex(0);

header("Content-Description: File Transfer");
header('Content-Disposition: attachment; filename="' . $file . '"');
//header('Content-Type: application/vnd.openxmlformats-officedocument.wordprocessingml.document');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Expires: 0');
ob_end_clean(); // uncomment to prevent unwanted content to be printed
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('php://output');
ob_end_flush();
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
@unlink($file);
exit();
?>