<?php
namespace SiqnetConsultingGmbH\Dslcontex\Controller;
include 'ContexAdditionalHelpers.php';
include_once('phpexcel/PHPExcel.php');
include_once('phpexcel/PHPExcel/IOFactory.php');


/***************************************************************
 *
 *  Copyright notice
 *
 *  (c) 2015 DSL <dsl@siqnet.de>, siQnet Consulting GmbH
 *
 * Änderungen
 * jsc - 5.3.2020 - index wird aus 1100 6m, 1700 6m, 2500 12m, 2700 12m, 3500 12m, 4250 12m gebildet
 *
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
 ***************************************************************/

/**
 * ContexController
 */
class ContexController extends \TYPO3\CMS\Extbase\Mvc\Controller\ActionController {

	/**
	 * contexRepository
	 *
	 * @var \SiqnetConsultingGmbH\Dslcontex\Domain\Repository\ContexRepository
	 * @inject
	 */
	protected $contexRepository = NULL;
	
	/**
	 * contexFirmenmitarbeiterRepository
	 *
	 * @var \SiqnetConsultingGmbH\Dslcontex\Domain\Repository\ContexFirmenmitarbeiterRepository
	 * @inject
	 */
	protected $contexFirmenmitarbeiterRepository = NULL;
	
	/**
	 * contexFirmenRepository
	 *
	 * @var \SiqnetConsultingGmbH\Dslcontex\Domain\Repository\ContexFirmenRepository
	 * @inject
	 */
	protected $contexFirmenRepository = NULL;	
	
	/**
	 * protected $mySessionArray
	 * @var type array
	 */
	protected $mySessionArray;
	
	/**
	 * contexContainerdatenRepository
	 *
	 * @var \SiqnetConsultingGmbH\Dslcontex\Domain\Repository\ContexContainerdatenRepository
	 * @inject
	 */
	protected $contexContainerdatenRepository = NULL;	
	
        
	/**
	 * CONSTRUKTOR
	 *
	 * @return
	 */
	public function __construct() {
		//OWN VARIABLES
		$this->locale = setlocale(LC_ALL, 'de_DE@euro', 'de_DE', 'de', 'ge');
		
	}  
	
	
	/**
	 * 
	 */
	public function csvAction() {
		
		
		$cds = $this->contexContainerdatenRepository->getAllContainerdata();
		$now = new \DateTime();
		
		$line[] = '"uid";"datum";"c1100teu";"c1700teu";"c2500teu";"c2700teu";"c3500teu";"c4250teu";"c1100teu6";"c1700teu6";"c1800teu6";"c2500teu12";"c2700teu12";"c3500teu12";"c4250teu12";"c5700teu12";"c6500teu12";"overall"';
		foreach ($cds as $key => $cd) {
			$line[] = '"'.$cd->getUid().'";' . 
					'"'.$cd->getDatum()->format("Y-m-d").'";' . 
					'"'.$cd->getC1100teu().'";' . 
					'"'.$cd->getC1700teu().'";' . 
					'"'.$cd->getC2500teu().'";' . 
					'"'.$cd->getC2700teu().'";' . 
					'"'.$cd->getC3500teu().'";' .
					'"'.$cd->getC4250teu().'";' .
					'"'.$cd->getC1100teu6().'";' .
					'"'.$cd->getC1700teu6().'";' .
					'"'.$cd->getC1800teu6().'";' .
					'"'.$cd->getC2500teu12().'";' .
					'"'.$cd->getC2700teu12().'";' .
					'"'.$cd->getC3500teu12().'";' .
					'"'.$cd->getC4250teu12().'";' .
					'"'.$cd->getC5700teu12().'";' .
					'"'.$cd->getC6500teu12().'";' .					
					'"'.$cd->getOverall().'"';
		}

		$file = 'typo3conf/ext/dslcontex/Resources/Public/csv/ContainerDaten_'.$now->format('Ymd_His').'.csv';
		file_put_contents ( $file , implode("\n",$line));
		
		
		if (file_exists($file)) {
			header('Content-Description: File Transfer');
			header('Content-Type: application/octet-stream');
			header('Content-Disposition: attachment; filename="'.basename($file).'"');
			header('Expires: 0');
			header('Cache-Control: must-revalidate');
			header('Pragma: public');
			header('Content-Length: ' . filesize($file));
			readfile($file);
			exit;
		}		
		
		$this->redirect('list');
		
	}	
        
	
/**
	 * checks the permission
	 */
	public function checkPermission() {
		//Constant Admin String to Array
		$this->settings['adminFirma'] = explode(',', $this->settings['adminFirma']);
		$this->mySessionArray = $GLOBALS['TSFE']->fe_user->getKey('ses', 'Contex');	
		$this->view->assign('session', $this->mySessionArray);
		$this->settings['inAdminList'] = False;
		foreach ($this->settings['adminFirma'] as $firmenID) {
			if($firmenID==$this->mySessionArray['firmenID']) {
				$this->settings['inAdminList'] = True;
			}
		}
		$this->view->assign('settings', $this->settings);		
	}
	
	/**
	 * initialize showFirmen action
	 *
	 * @param void
	 */
	public function initializeShowFirmenAction() {
	  $this->arguments->getArgument('contex')
		->getPropertyMappingConfiguration()->forProperty('datum')
		->setTypeConverterOption(
		  'TYPO3\\CMS\\Extbase\\Property\\TypeConverter\\DateTimeConverter',
		  \TYPO3\CMS\Extbase\Property\TypeConverter\DateTimeConverter::CONFIGURATION_DATE_FORMAT,
		  'Y-m-d'
		);
	}		
	
	
	/**
	 * action show Firmen and thier context values
	 * @param \SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $contex
	 * @return void
	 */
	public function showFirmenAction(\SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $contex) {
		$this->checkPermission();
		if($this->settings['inAdminList']==True)
		{	
			$this->view->assign('firma', $contex->getContexfirmenId());
			$this->view->assign('datum', $contex->getDatum()->format("Y-m-d"));		

			$attr = array(
				'contexfirmen_id' => $contex->getContexfirmenId()->getUid(),
				'datum' => $contex->getDatum()->format("Y-m-d"),
				);

			$contexObj = $this->contexRepository->get_contex_betraege($attr);
			$this->view->assign('contex', $contexObj->toArray());
		}
		else
		{
			$this->addFlashMessage('Sie haben keine Berechtigungen für diesen Bereich.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::ERROR);
			$this->redirect('list');			
		}
	}
	
        
	/**
	 * action edit
	 *
	 * 
	 * @ignorevalidation $contex
	 * @return void
	 */
	public function editAction() {
		$this->checkPermission();
	
		if($this->settings['inAdminList']==True)
		{		
			$datum = date("Y-m-d",time() - (60*60*24*7*4*3));
			$contex = $this->contexRepository->getContexDatum(74,$datum,' ORDER BY datum DESC');
			$firmen = $this->contexFirmenRepository->getAllFirmen(72);
			$this->view->assign('firmen',$firmen);			
			$this->view->assign('contex', $contex);
		}
		else
		{
			$this->addFlashMessage('Sie haben keine Berechtigungen für diesen Bereich.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::ERROR);
			$this->redirect('list');			
		}				
	}        
	
	/**
	 * action changePassword
	 *
	 * @return void
	 */
	public function changePasswordAction() {
		$this->checkPermission();
	}
	
	/**
	 * action updatePassword
	 *
	 * @return void
	 */
	public function updatePasswordAction() {
		
		$this->mySessionArray = $GLOBALS['TSFE']->fe_user->getKey('ses', 'Contex');
		$mitarbeiter = $this->contexFirmenmitarbeiterRepository->findByUid($this->mySessionArray['userID']);
		
		if(isset($_POST['tx_dslcontex_contex']['pwOld']) && $_POST['tx_dslcontex_contex']['pwOld']!="" && isset($_POST['tx_dslcontex_contex']['pw1']) && $_POST['tx_dslcontex_contex']['pw1']!="" && isset($_POST['tx_dslcontex_contex']['pw2']) && $_POST['tx_dslcontex_contex']['pw2']!="" && $_POST['tx_dslcontex_contex']['pw1'] == $_POST['tx_dslcontex_contex']['pw2'] && $mitarbeiter->getPasswort() == md5($_POST['tx_dslcontex_contex']['pwOld'])  ) 
		{
			$mitarbeiter->setPasswort(md5($_POST['tx_dslcontex_contex']['pw1']));
			$this->contexFirmenmitarbeiterRepository->update($mitarbeiter);
			$this->addFlashMessage('Sie haben Ihr Passwort erfolgreich geändert.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::ERROR);
			$this->redirect('list');
		}
		else
		{
			$this->addFlashMessage('Sie haben falsche Parameter verwendet. Bitte versuche Sie es erneut.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::ERROR);
			$this->redirect('changePassword');
		}
	}
	
		
	/**
	 * Sends Notification Email to Agents
	 */
	public function sendNotificationMailAction() {
		$cah = new \ContexAdditionalHelpers;
		$firmens = $this->contexFirmenRepository->getAllFirmen(0, 'ASC',' AND aktiv=1');
		
			$mail = array(
				'address' => 'info@schiffsmakler.de',
				'addressName' => 'VHBS e. V.',
				'from' => 'info@schiffsmakler.de',
				'fromName' => 'VHBS e. V.',
				'subject' => 'New ConTex - Results: ' . $_POST['tx_dslcontex_contex']['datum'],
				'body' => stripslashes($_POST['tx_dslcontex_contex']['tinyeditor']),
				'altBody' => stripslashes($_POST['tx_dslcontex_contex']['tinyeditor']),
				'attachment' => $_POST['tx_dslcontex_contex']['excelAttachment'],
				'flashmessage' => $cah->getLL('contex.sendingNotificationMail.ok', 'dslcontex')
			);	
			
			$cah->mailSystem($mail['address'], $mail['addressName'], $mail['from'], $mail['fromName'], $mail['subject'], $mail['body'], $mail['altBody'], $mail['attachment'], $mail['flashmessage']);		
		
		$this->addFlashMessage($mail['flashmessage'], '', \TYPO3\CMS\Core\Messaging\AbstractMessage::OK);
		$this->redirect('notification');
	}
	
	// Craetes an Excel File 
	public function excelAttachment() {
		$objPHPExcel = new \PHPExcel();
		$objPHPExcel->getProperties()
			->setCreator('siQnet Consulting GmbH')
			->setLastModifiedBy('DSL')
			->setTitle('NewContex Results')
			->setSubject('NewContex Results')
			->setDescription('NewContex Results')
			->setKeywords("NewContex Results")
			->setCategory("NewContex Results");

		$excel_spalten = array(
			'A' => 'Datum',
			'B' => 'New ConTex',
			'D' => '1100',
			'F' => '1700',
			'H' => '2500',
			'J' => '2700',
			'L' => '3500',
			'N' => '4250',
			'P' => '6 months 1100',
			'R' => '6 months 1700',
			'T' => '12 months 2500',
			'V' => '12 months 2700',
			'X' => '12 months 3500',
			'Z' => '12 months 4250',
			'AB' => '12 months 5700',
			'AD' => '12 months 6500'
		);

		// Create a new worksheet, after the default sheet
		$worksheets = array(
			 0 => array(
					'title'          => 'New ConTex Results',
					'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
				),
		);

		reset($worksheets);
		// Worksheets in Excel anlegen
		while (list($worksheet_id, $worksheet_attr) = each($worksheets)) {
			// Erste ID überspringen, da dieses Worksheet automatisch angelegt wird
		//				if ($worksheet_id > 0) {
		//					$objPHPExcel->createSheet();
		//				}
			// Titel der Tabs setzen
			$objPHPExcel->setActiveSheetIndex($worksheet_id)->setTitle($worksheet_attr['title']);
			// Feststellen von Zeilen und Spalten
			if (isset($worksheet_attr['freezepane'])) {
				$objPHPExcel->getActiveSheet()->freezePane($worksheet_attr['freezepane']);
			}
		}

		// Übersicht
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Datum');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', 'New ConTex');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '1100');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', '1700');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H1', '2500');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J1', '2700');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L1', '3500');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N1', '4250');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P1', '6 months 1100');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R1', '6 months 1700');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T1', '12 months 2500');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V1', '12 months 2700');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X1', '12 months 3500');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z1', '12 months 4250');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB1', '12 months 5700');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD1', '12 months 6500');

		$cds = $this->contexContainerdatenRepository->getAllContainerdata(75, 0, 'DESC', 9);
	
		$cd = $cds->toArray();
		
		$cdr = array_reverse($cd);
		
		$row = 2;
		for($i=1;$i<count($cdr);$i++) 
		{
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $cdr[$i]->getDatum()->format("d.m.Y"));
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$row, $cdr[$i]->getOverall());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$row, $cdr[$i]->getOverall() -  $cdr[$i-1]->getOverall());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$row, $cdr[$i]->getC1100teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$row, $cdr[$i]->getC1100teu() - $cdr[$i-1]->getC1100teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$row, $cdr[$i]->getC1700teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$row, $cdr[$i]->getC1700teu() - $cdr[$i-1]->getC1700teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$row, $cdr[$i]->getC2500teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$row, $cdr[$i]->getC2500teu() - $cdr[$i-1]->getC2500teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$row, $cdr[$i]->getC2700teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$row, $cdr[$i]->getC2700teu() - $cdr[$i-1]->getC2700teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L'.$row, $cdr[$i]->getC3500teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M'.$row, $cdr[$i]->getC3500teu() - $cdr[$i-1]->getC3500teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N'.$row, $cdr[$i]->getC4250teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('O'.$row, $cdr[$i]->getC4250teu() - $cdr[$i-1]->getC4250teu());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P'.$row, $cdr[$i]->getC1100teu6());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q'.$row, $cdr[$i]->getC1100teu6() - $cdr[$i-1]->getC1100teu6());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R'.$row, $cdr[$i]->getC1700teu6());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('S'.$row, $cdr[$i]->getC1700teu6() - $cdr[$i-1]->getC1700teu6());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T'.$row, $cdr[$i]->getC2500teu12());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U'.$row, $cdr[$i]->getC2500teu12() - $cdr[$i-1]->getC2500teu12());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V'.$row, $cdr[$i]->getC2700teu12());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('W'.$row, $cdr[$i]->getC2700teu12() - $cdr[$i-1]->getC2700teu12());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X'.$row, $cdr[$i]->getC3500teu12());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y'.$row, $cdr[$i]->getC3500teu12() - $cdr[$i-1]->getC3500teu12());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z'.$row, $cdr[$i]->getC4250teu12());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA'.$row, $cdr[$i]->getC4250teu12() - $cdr[$i-1]->getC4250teu12());
			
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB'.$row, $cdr[$i]->getC5700teu12());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC'.$row, $cdr[$i]->getC5700teu12() - $cdr[$i-1]->getC5700teu12());	

			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD'.$row, $cdr[$i]->getC6500teu12());
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE'.$row, $cdr[$i]->getC6500teu12() - $cdr[$i-1]->getC6500teu12());
			
			$row++;
		}

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

		$objPHPExcel->getSecurity()->setLockWindows(true);
		$objPHPExcel->getSecurity()->setLockStructure(true);
		$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");

		// Excel Datei rausschreiben
		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

		$date = date('d.m.Y-His');
		$excel['path'] = 'typo3conf/ext/dslcontex/Resources/Public/excel/';
		$excel['file'] = 'NewContexResults_'.$date.'.xlsx';


		$objWriter->save($excel['path'].$excel['file']);
		
		return $excel['path'].$excel['file'];
	}
	
	/**
	 * action Notification
	 * 
	 */
	public function notificationAction() {
		$this->checkPermission();
		$cah = new \ContexAdditionalHelpers;
		$notificationMail = $cah->mailBuilderNotificationMail();		
		$firmens = $this->contexFirmenRepository->getAllFirmen(0, 'ASC',' AND aktiv=1 AND in_contex=1');
		$containerdatens = $this->contexContainerdatenRepository->getAllContainerdata(75, 0, 'DESC', 2);
		
		$feiertage = $cah->getFreierTag($tag, $monat, $jahr);
		
		
		$cd = $containerdatens->toArray();
		
		$containerdaten[0] = $cd[0];
		$containerdaten[1] = $cd[1];
		
		$notificationMail = str_replace('###countFirmen###', count($firmens->toArray()), $notificationMail);
		
		$notificationMail = str_replace('###12type1100###', number_format($containerdaten[0]->getC1100teu(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###12type1100Diff###', $containerdaten[0]->getC1100teu() - $containerdaten[1]->getC1100teu() , $notificationMail);
		
		$notificationMail = str_replace('###12type1700###', number_format($containerdaten[0]->getC1700teu(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###12type1700Diff###', $containerdaten[0]->getC1700teu() - $containerdaten[1]->getC1700teu() , $notificationMail);		
		
		$notificationMail = str_replace('###24type2500###', number_format($containerdaten[0]->getC2500teu(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###24type2500Diff###', $containerdaten[0]->getC2500teu() - $containerdaten[1]->getC2500teu() , $notificationMail);
		
		$notificationMail = str_replace('###24type2700###', number_format($containerdaten[0]->getC2700teu(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###24type2700Diff###', $containerdaten[0]->getC2700teu() - $containerdaten[1]->getC2700teu() , $notificationMail);						
		
		$notificationMail = str_replace('###24type3500###', number_format($containerdaten[0]->getC3500teu(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###24type3500Diff###', $containerdaten[0]->getC3500teu() - $containerdaten[1]->getC3500teu() , $notificationMail);				
		
		$notificationMail = str_replace('###24type4250###', number_format($containerdaten[0]->getC4250teu(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###24type4250Diff###', $containerdaten[0]->getC4250teu() - $containerdaten[1]->getC4250teu() , $notificationMail);						
		
		$notificationMail = str_replace('###overall###', number_format($containerdaten[0]->getOverall(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###overallDiff###', $containerdaten[0]->getOverall() - $containerdaten[1]->getOverall() , $notificationMail);						
		
		$notificationMail = str_replace('###6type1100###', number_format($containerdaten[0]->getC1100teu6(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###6type1100Diff###', $containerdaten[0]->getC1100teu6() - $containerdaten[1]->getC1100teu6() , $notificationMail);
		
		$notificationMail = str_replace('###6type1700###', number_format($containerdaten[0]->getC1700teu6(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###6type1700Diff###', $containerdaten[0]->getC1700teu6() - $containerdaten[1]->getC1700teu6() , $notificationMail);
		
		$notificationMail = str_replace('###6type1800###', number_format($containerdaten[0]->getC1800teu6(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###6type1800Diff###', $containerdaten[0]->getC1800teu6() - $containerdaten[1]->getC1800teu6() , $notificationMail);
		
		$notificationMail = str_replace('###12type2500###', number_format($containerdaten[0]->getC2500teu12(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###12type2500Diff###', $containerdaten[0]->getC2500teu12() - $containerdaten[1]->getC2500teu12() , $notificationMail);
				
		$notificationMail = str_replace('###12type2700###', number_format($containerdaten[0]->getC2700teu12(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###12type2700Diff###', $containerdaten[0]->getC2700teu12() - $containerdaten[1]->getC2700teu12() , $notificationMail);
		
		$notificationMail = str_replace('###12type3500###', number_format($containerdaten[0]->getC3500teu12(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###12type3500Diff###', $containerdaten[0]->getC3500teu12() - $containerdaten[1]->getC3500teu12() , $notificationMail);
		
		$notificationMail = str_replace('###12type4250###', number_format($containerdaten[0]->getC4250teu12(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###12type4250Diff###', $containerdaten[0]->getC4250teu12() - $containerdaten[1]->getC4250teu12() , $notificationMail);

		$notificationMail = str_replace('###12type5700###', number_format($containerdaten[0]->getC5700teu12(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###12type5700Diff###', $containerdaten[0]->getC5700teu12() - $containerdaten[1]->getC5700teu12() , $notificationMail);

		$notificationMail = str_replace('###12type6500###', number_format($containerdaten[0]->getC6500teu12(),0,".",",") , $notificationMail);
		$notificationMail = str_replace('###12type6500Diff###', $containerdaten[0]->getC6500teu12() - $containerdaten[1]->getC6500teu12() , $notificationMail);
		
		$this->view->assign('datum',$containerdaten[0]->getDatum()->format("d.m.Y"));
		$this->view->assign('excelAttachment',$this->excelAttachment());
		
		
		//WENN FEIERTAG DANN NEXT DATUM
		$d = clone $containerdaten[0]->getDatum();
		$nextD = $this->nextDatum($d);
		
		foreach ($feiertage as $fname => $datum) {
			
			while($datum == $nextD) {
				$dt = new \DateTime($nextD);
				$nextD = $this->nextDatum($dt);
			}
		}

		$notificationMail = str_replace('###nextDatum###', $nextD , $notificationMail);
		$this->view->assign('notificationMail',$notificationMail);
		
	}	
	
	/**
	 * Returns the next ConTex input Date
	 */
	public function nextDatum($datum) {
		switch($datum->format("N")) {
			case 2:
				//Tuesday + 2 days = Thursday
				$newDatum = $datum->modify("+2 day");
			break;
			case 4:
				//Thursday + 5 days = Tuesday
				$newDatum = $datum->modify("+5 day");
			break;
		}
		return $newDatum->format("d.m.Y");
	}
	
	/**
	 * SENDS REMAINDSMAIL
	 */
	public function sendRemindMailAction() {

		$cah = new \ContexAdditionalHelpers;
		//RemindMail an alle Firmen
		foreach ($_POST['tx_dslcontex_contex']['mitarbeiter'] as $firmenID => $data) {
			$mail = array(
				'address' => $data['email'],
				'addressName' => $data['name'],
				'from' => 'info@schiffsmakler.de',
				'fromName' => 'VHBS e. V.',
				'subject' => 'ConTex '.$cah->vbg_convert_datetime(date('Y-m-d'), 'Y-m-d', 'd.m.Y').' - Reminder',
				'body' => stripslashes($_POST['tx_dslcontex_contex']['tinyeditor']),
				'altBody' => stripslashes($_POST['tx_dslcontex_contex']['tinyeditor']),
				'attachment' => '',
				'flashmessage' => $cah->getLL('contex.sendingRemindMail.ok', 'dslcontex')
			);	
	
			$cah->mailSystem($mail['address'], $mail['addressName'], $mail['from'], $mail['fromName'], $mail['subject'], $mail['body'], $mail['altBody'], $mail['attachment'], $mail['flashmessage'],$mail['bcc']);
			
		}

		//RemindMail an VHBS
		$mail = array(
				'address' => 'info@schiffsmakler.de',
				'addressName' => 'VHBS e. V.',
				'from' => 'info@schiffsmakler.de',
				'fromName' => 'VHBS e. V.',
				'subject' => 'ConTex '.$cah->vbg_convert_datetime(date('Y-m-d'), 'Y-m-d', 'd.m.Y').' - Reminder',
				'body' => stripslashes($_POST['tx_dslcontex_contex']['tinyeditor']),
				'altBody' => stripslashes($_POST['tx_dslcontex_contex']['tinyeditor']),
				'attachment' => '',
				'flashmessage' => $cah->getLL('contex.sendingRemindMail.ok', 'dslcontex')
			);	
		$cah->mailSystem($mail['address'], $mail['addressName'], $mail['from'], $mail['fromName'], $mail['subject'], $mail['body'], $mail['altBody'], $mail['attachment'], $mail['flashmessage'],$mail['bcc']);		
		
		$this->addFlashMessage($mail['flashmessage'], '', \TYPO3\CMS\Core\Messaging\AbstractMessage::OK);
		$this->redirect('reminder');
	}	
	
	/**
	 * action Reminder
	 * sends remindEmail to agents
	 */
	public function reminderAction() {
		$cah = new \ContexAdditionalHelpers;
		
		$this->checkPermission();
		$firmens = $this->contexFirmenRepository->getAllFirmen(0, 'ASC',' AND aktiv=1 AND in_contex=1');
		foreach ($firmens as $key => $firma) {
			$mitarbeiters = $this->contexFirmenmitarbeiterRepository->getAllUsers(0, ' AND contexfirmen_id = '.$firma->getUid());
			foreach ($mitarbeiters as $key2 => $mitarbeiter) {
				if($mitarbeiter->getLetzterlogin() !== null) {
					if ($mitarbeiter->getLetzterlogin()->format('Y-m-d') == date('Y-m-d')) {
						$bemerkung = 'Eingeloggt';
					}
				}
				$email = $mitarbeiter->getEmail();				
			}

			$attr = array(
				'contexfirmen_id' => $firma->getUid(),
				'datum'     => date('Y-m-d')
			);	
			
			if ($this->contexRepository->get_contex($attr) !== FALSE) {
				$view[$firma->getUid()]['bemerkung'] = '1';
			} else {
				$view[$firma->getUid()]['bemerkung'] = '0';
			}
			$view[$firma->getUid()]['firma'] = $firma->getFirmenname();
			$view[$firma->getUid()]['firmenID'] = $firma->getUID();
			$view[$firma->getUid()]['mitarbeiterName'] = $mitarbeiter->getVorname().' '.$mitarbeiter->getnachname();
			if($email=='')
			{
				$email = $firma->getEmail();
			}
			$view[$firma->getUid()]['email'] = $email;
			$email = '';
		}
		
		$this->view->assign('remindMail',$cah->mailBuilderRemindMail());	
		$this->view->assign('views',$view);
	}
	
	/**
	 * action list
	 *
	 * @return void
	 */
	public function listAction() {
		$this->checkPermission();
		$this->view->assign('today', Time());
		$firmen = $this->contexFirmenRepository->getAllFirmen(0,'ASC');
		$this->view->assign('firmen', $firmen);
		$cah = new \ContexAdditionalHelpers();
		
		$tag = date("d", time());
		$monat = date("m", time());
		$jahr = date("Y", time());
		$feiertage = $cah->getFreierTag($tag, $monat, $jahr);	
		

		switch($_POST['tx_dslcontex_contex']['dauer'])
		{
			case 0:
				$days = 10;
				break;
			case 1:
				$days = 20;
				break;
			case 2:
				$days = 30;
				break;
			case 3:
				$days = 60;
				break;
			case 4:
				$days = 120;
				break;			
		}	

		$this->view->assign('post', $_POST['tx_dslcontex_contex']);
		if(!isset($_GET['feiertage']))
		{
			$feiertage = '';
		}

		$this->view->assign('feiertage',$feiertage);
		
		if(isset($_POST['tx_dslcontex_contex']['firmen']) && $_POST['tx_dslcontex_contex']['firmen']>0)
		{
			$firmenID = $_POST['tx_dslcontex_contex']['firmen'];
			$contexx = $this->contexRepository->getContex($this->settings['pid'],$this->settings,$this->mySessionArray,$days,$firmenID);
		}
		else 
		{
			$contexx = $this->contexRepository->getContex($this->settings['pid'],$this->settings,$this->mySessionArray,$days);
		}
		
		if(isset($_GET['firmen']) && $_GET['firmen']>0)
		{
			$firmenID = $_GET['firmen'];
			$this->view->assign('post', $_GET);
			$contexx = $this->contexRepository->getContex($this->settings['pid'],$this->settings,$this->mySessionArray,$days,$firmenID);			
		}

		foreach ($contexx as $key => $item) {
		    if(null === $item->getContexfirmenId()){
		        continue;
            }
			$conArr[$item->getContexfirmenId()->getUid()]['firmenname'] = $item->getContexfirmenId()->getFirmenname();
			$conArr[$item->getContexfirmenId()->getUid()]['kurzname'] = $item->getContexfirmenId()->getKurzname();
			$conArr[$item->getContexfirmenId()->getUid()]['ansprechpartner'] = $item->getContexfirmenId()->getAnsprechpartner();
			$conArr[$item->getContexfirmenId()->getUid()]['telefon'] = $item->getContexfirmenId()->getTelefon();
			$conArr[$item->getContexfirmenId()->getUid()]['email'] = $item->getContexfirmenId()->getEmail();
			$conArr[$item->getContexfirmenId()->getUid()]['aktiv'] = $item->getContexfirmenId()->getAktiv();
			$conArr[$item->getContexfirmenId()->getUid()]['excel_spalte'] = $item->getContexfirmenId()->getExcelSpalte();
			$conArr[$item->getContexfirmenId()->getUid()]['in_contex'] = $item->getContexfirmenId()->getInContex();
			$conArr[$item->getContexfirmenId()->getUid()]['datum'][$item->getDatum()->format("Y-m-d")][$item->getCharterPeriode()][$item->getType()] = $item->getBetrag();
			$conArr[$item->getContexfirmenId()->getUid()]['colsize'] = count($conArr[$item->getContexfirmenId()->getUid()]['datum']) + 2;
		}

		$this->view->assign('contexx', $conArr);			
	}

	/**
	 * action show
	 *
	 * @param \SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $contex
	 * @return void
	 */
	public function showAction(\SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $contex) {
		$this->view->assign('contex', $contex);
	}

	/**
	 * action new
	 *
	 * @param \SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $newContex
	 * @ignorevalidation $newContex
	 * @return void
	 */
	public function newAction(\SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $newContex = NULL) {
		$this->checkPermission();
		$cah = new \ContexAdditionalHelpers();
		$this->view->assign('today', Time());
		$firmen = $this->contexFirmenRepository->getAllFirmen(0,'ASC');
		$this->view->assign('firmen', $firmen);	
	
		$tag = date("d", time());
		$monat = date("m", time());
		$jahr = date("Y", time());
		$feiertag = $cah->freierTag($tag, $monat, $jahr);
	
		if(isset($_POST['tx_dslcontex_contex']['firmen']) && $_POST['tx_dslcontex_contex']['firmen'] > 0) 
		{
			$this->view->assign('firmenID', $_POST['tx_dslcontex_contex']['firmen']);
			$contexes = $this->contexRepository->getContex($this->settings['pid'], $this->settings, $this->mySessionArray, 7, $_POST['tx_dslcontex_contex']['firmen']);
		}
		else
		{
			$this->view->assign('firmenID', $this->mySessionArray['firmenID']);
			$contexes = $this->contexRepository->getContex($this->settings['pid'], $this->settings, $this->mySessionArray, 7, $this->mySessionArray['firmenID']);
		}
		$this->view->assign('contex', $contex);
		
		$cfg['contex']['prepredatum'] = '2014-06-05';
		$cfg['contex']['predatum']    = '2014-06-10';
		$cfg['contex']['meldedatum']  = '2014-06-12';
		$cfg['contex']['sperrdatum']  = '2014-06-12 18:00:00';

		if (date('N') == 2) { // Dienstag
			$cfg['contex']['prepredatum'] = date('Y-m-d', time()-(86400*7));
			$cfg['contex']['predatum']    = date('Y-m-d', time()-(86400*5));
			$cfg['contex']['meldedatum']  = date('Y-m-d');
			$cfg['contex']['sperrdatum']  = date('Y-m-d').' 18:00:00';
		}
		if (date('N') == 4) { // Donnerstag
			$cfg['contex']['prepredatum'] = date('Y-m-d', time()-(86400*7));
			$cfg['contex']['predatum']    = date('Y-m-d', time()-(86400*2));
			$cfg['contex']['meldedatum']  = date('Y-m-d');
			$cfg['contex']['sperrdatum']  = date('Y-m-d').' 18:00:00';
		}
		
		$contex_values[1100][6]  = 0;
		$contex_values[1700][6]  = 0;
		$contex_values[1800][6]  = 0;
		$contex_values[1100][12] = 0;
		$contex_values[1700][12] = 0;
		$contex_values[2500][12] = 0;
		$contex_values[2700][12] = 0;
		$contex_values[3500][12] = 0;
		$contex_values[4250][12] = 0;
		$contex_values[2500][24] = 0;
		$contex_values[2700][24] = 0;
		$contex_values[3500][24] = 0;
		$contex_values[4250][24] = 0;
		
		$contex_values[5700][12] = 0;
		$contex_values[6500][12] = 0;

		foreach ($firmen as $key => $data) {
			$contex_firmen[$data->getUid()] = array(
				'firmenname'      => $data->getFirmenname(),
				'kurzname'        => $data->getKurzname(),
				'ansprechpartner' => $data->getAnsprechpartner(),
				'telefon'         => $data->getTelefon(),
				'email'           => $data->getEmail(),
				'aktiv'           => $data->getAktiv(),
				'excel_spalte'    => $data->getExcelSpalte()
			);
		}	

		$datumswertes = $this->contexRepository->getContexDatum(0,'');
		foreach ($datumswertes as $key => $data) {
			if(!$data->getDatum()){
				continue;
			}
			$datumswerte[$data->getDatum()->format('Y-m-d')] = $data->getDatum()->format('Y-m-d');
		}	
		
		
		$firmen_id = $this->mySessionArray['firmenID'];
		if ($this->settings['inAdminList'] == 1 && isset($_POST['tx_dslcontex_contex']['firmen']) && $_POST['tx_dslcontex_contex']['firmen'] > 0) {
			$firmen_id = $_POST['tx_dslcontex_contex']['firmen'];
		}
		$attr = array(
			'contexfirmen_id' => $firmen_id,
			'datum'     => $cfg['contex']['meldedatum']
		);
		
		$contexs = $this->contexRepository->get_contex_betraege($attr);
		foreach ($contexs as $key => $data) {
			if (isset($attr['contexfirmen_id']) && isset($attr['datum'])) {
				$contex[$data->getType()][$data->getCharterPeriode()] = $data->getBetrag();
			}
			if (!isset($attr['contexfirmen_id']) && isset($attr['datum'])) {
			    if(null !== $data->getContexfirmenId()){
                    $contex[$data->getType()][$data->getCharterPeriode()][$data->getContexfirmenId()->getUid()] = $data->getBetrag();
                }
			}
		}			

		if ($contex !== FALSE) {
			$contex_values = $contex;
		}

		$attr = array(
			'contexfirmen_id' => $firmen_id,
			'datum'     => $cfg['contex']['predatum']
		);
		$contex_pres = $this->contexRepository->get_contex_betraege($attr);
		foreach ($contex_pres as $key => $data) {
			if (isset($attr['contexfirmen_id']) && isset($attr['datum'])) {
				$contex_pre[$data->getType()][$data->getCharterPeriode()] = $data->getBetrag();
			}
			if (!isset($attr['contexfirmen_id']) && isset($attr['datum'])) {
			    if(null !== $data->getContexfirmenId()){
                    $contex_pre[$data->getType()][$data->getCharterPeriode()][$data->getContexfirmenId()->getUid()] = $data->getBetrag();
                }
			}
		}	
		

		$values = array(
			'datum' => date('d.m.Y')
		);
		
		$html_output = '
		<table class="contexInputTable">
		<tr>
			<td rowspan="2" class="imageTD">
				<img src="typo3conf/ext/dslcontex/Resources/Public/Icons/vhss-logo-contex.png" alt="VHBS" title="VHBS" width="48px" height="auto" >
			</td>
			<th colspan="2">6 Months</th>
			<th colspan="2">12 Months</th>
			<th colspan="2">24 Months</th>
		</tr>
		<tr>
			<th>'.$cah->vbg_convert_datetime($cfg['contex']['meldedatum'], 'Y-m-d', 'd.m.Y').'</th>
			<th>'.$cah->vbg_convert_datetime($cfg['contex']['predatum'], 'Y-m-d', 'd.m.Y').'</th>
			<th>'.$cah->vbg_convert_datetime($cfg['contex']['meldedatum'], 'Y-m-d', 'd.m.Y').'</th>
			<th>'.$cah->vbg_convert_datetime($cfg['contex']['predatum'], 'Y-m-d', 'd.m.Y').'</th>
			<th>'.$cah->vbg_convert_datetime($cfg['contex']['meldedatum'], 'Y-m-d', 'd.m.Y').'</th>
			<th>'.$cah->vbg_convert_datetime($cfg['contex']['predatum'], 'Y-m-d', 'd.m.Y').'</th>
		</tr>
		<tr>
			<th><a href="index.php?id=32">Type 1100</a></th>
			<td><input type="text" name="contex[charterPeriode][6][1100]" value="'.$contex_values[1100][6].'" size="5" maxlength="6" /><br>6 Months</td>
			<td>'.number_format($contex_pre[1100][6], 0, '', '.').'</td>
			<td><input type="text" name="contex[charterPeriode][12][1100]" value="'.$contex_values[1100][12].'" size="5" maxlength="6" /><br>12 Months</td>
			<td>'.number_format($contex_pre[1100][12], 0, '', '.').'</td>
			<td colspan="2"></td>
		</tr>
		<tr>
			<th><a href="index.php?id=33">Type 1700</a></th>
			<td><input type="text" name="contex[charterPeriode][6][1700]" value="'.$contex_values[1700][6].'" size="5" maxlength="6" /><br>6 Months</td>
			<td>'.number_format($contex_pre[1700][6], 0, '', '.').'</td>
			<td><input type="text" name="contex[charterPeriode][12][1700]" value="'.$contex_values[1700][12].'" size="5" maxlength="6" /><br>12 Months</td>
			<td>'.number_format($contex_pre[1700][12], 0, '', '.').'</td>
			<td colspan="2"></td>
		</tr>
		<tr>
			<th><a href="index.php?id=107">Type 1800</a></th>
			<td><input type="text" name="contex[charterPeriode][6][1800]" value="'.$contex_values[1800][6].'" size="5" maxlength="6" /><br>6 Months</td>
			<td>'.number_format($contex_pre[1800][6], 0, '', '.').'</td>
			<td></td>
			<td></td>	
			<td></td>
			<td></td>
		</tr>
		<tr>
			<th><a href="index.php?id=34">Type 2500</a></th>
			<td></td>
			<td></td>
			<td><input type="text" name="contex[charterPeriode][12][2500]" value="'.$contex_values[2500][12].'" size="5" maxlength="6" /><br>12 Months</td>
			<td>'.number_format($contex_pre[2500][12], 0, '', '.').'</td>
			<td><input type="text" name="contex[charterPeriode][24][2500]" value="'.$contex_values[2500][24].'" size="5" maxlength="6" /><br>24 Months</td>
			<td>'.number_format($contex_pre[2500][24], 0, '', '.').'</td>
		</tr>
		<tr>
			<th><a href="index.php?id=35">Type 2700</a></th>
			<td></td>
			<td></td>
			<td><input type="text" name="contex[charterPeriode][12][2700]" value="'.$contex_values[2700][12].'" size="5" maxlength="6" /><br>12 Months</td>
			<td>'.number_format($contex_pre[2700][12], 0, '', '.').'</td>
			<td><input type="text" name="contex[charterPeriode][24][2700]" value="'.$contex_values[2700][24].'" size="5" maxlength="6" /><br>24 Months</td>
			<td>'.number_format($contex_pre[2700][24], 0, '', '.').'</td>
		</tr>
		<tr>
			<th><a href="index.php?id=36">Type 3500</a></th>
			<td></td>
			<td></td>
			<td><input type="text" name="contex[charterPeriode][12][3500]" value="'.$contex_values[3500][12].'" size="5" maxlength="6" /><br>12 Months</td>
			<td>'.number_format($contex_pre[3500][12], 0, '', '.').'</td>
			<td ><input type="text" name="contex[charterPeriode][24][3500]" value="'.$contex_values[3500][24].'" size="5" maxlength="6" /><br>24 Months</td>
			<td>'.number_format($contex_pre[3500][24], 0, '', '.').'</td>
		</tr>
		<tr>
			<th><a href="index.php?id=37">Type 4250</a></th>
			<td></td>
			<td></td>
			<td><input type="text" name="contex[charterPeriode][12][4250]" value="'.$contex_values[4250][12].'" size="5" maxlength="6" /><br>12 Months</td>
			<td>'.number_format($contex_pre[4250][12], 0, '', '.').'</td>
			<td><input type="text" name="contex[charterPeriode][24][4250]" value="'.$contex_values[4250][24].'" size="5" maxlength="6" /><br>24 Months</td>
			<td>'.number_format($contex_pre[4250][24], 0, '', '.').'</td>
		</tr>
		
		<tr>
			<th><a href="index.php?id=101">Type 5700</a></th>
			<td></td>
			<td></td>
			<td><input type="text" name="contex[charterPeriode][12][5700]" value="'.$contex_values[5700][12].'" size="5" maxlength="6" /><br>12 Months</td>
			<td>'.number_format($contex_pre[5700][12], 0, '', '.').'</td>
			<td></td>
			<td></td>
		</tr>
		
		<tr>
			<th><a href="index.php?id=102">Type 6500</a></th>
			<td></td>
			<td></td>
			<td><input type="text" name="contex[charterPeriode][12][6500]" value="'.$contex_values[6500][12].'" size="5" maxlength="6" /><br>12 Months</td>
			<td>'.number_format($contex_pre[6500][12], 0, '', '.').'</td>
			<td></td>
			<td></td>
		</tr>

		</table>
		<br>';

		if (date('Y-m-d H:i:s') > $cfg['contex']['sperrdatum'] || date('Y-m-d') <> $cfg['contex']['meldedatum']) 
		{
		    $html_output .= '<p align="center"><h1 style="color:red;">Keine Eingabe mehr möglich</h1></p>';
		} 
		else
		{
		    $html_output .= '<p align="center"><input class="submitButtonNew" type="submit" value="Save New ConTex"></p>';
		}
		

		$html_output .= '
		<br>
		<div align="center">
			<p style="font-weight:bold;background-color:#CCCCCC; max-width:300px;padding:10px;text-align:center;">New ConTex<br />Rates per day in US $</p>
			
		</div>';
		
		
		if(isset($_POST['tx_dslcontex_contex']['firmen']) && $_POST['tx_dslcontex_contex']['firmen'] > 0)
		{
			$html_output .= '<input type="hidden" name="contex[firma]" value="'.$_POST['tx_dslcontex_contex']['firmen'].'" />';
		}
		else
		{
			$html_output .= '<input type="hidden" name="contex[firma]" value="'.$this->mySessionArray['firmenID'].'" />';
		}
		
		$html_output .= '<input type="hidden" name="contex[datum][prepredatum]" value="'.$cfg['contex']['prepredatum'].'" />';
		$html_output .= '<input type="hidden" name="contex[datum][predatum]" value="'.$cfg['contex']['predatum'].'" />';
		$html_output .= '<input type="hidden" name="contex[datum][meldedatum]" value="'.$cfg['contex']['meldedatum'].'" />';
		$html_output .= '<input type="hidden" name="contex[datum][sperrdatum]" value="'.$cfg['contex']['sperrdatum'].'" />';

		
		if($feiertag==1)
		{
		    $html_output = '<h1 style="color:red;" class="feiertag">Heute ist ein Feiertag<br />Due to the holiday no report today</h1>';
		}
		$this->view->assign('form', $html_output);
		
	}

	/**
	 * action create
	 *
	 * 
	 * @return void
	 */
	public function createAction() {
		$this->checkPermission();
		if(isset($_POST['contex']['firma']) && $_POST['contex']['firma']>0)
		{
			$fid = $_POST['contex']['firma'];
		}
		else
		{
			$fid = $this->mySessionArray['firmenID'];
		}
		
		$obj = $this->contexFirmenRepository->findByUid($fid);	

		foreach ($_POST['contex']['charterPeriode'] as $charterPeriode => $charterPeriodeArr) {
			foreach ($charterPeriodeArr as $type => $value) {

				$contexObj = $this->contexRepository->findByPropertys($this->settings['pid'],$_POST['contex']['datum']['meldedatum'],$charterPeriode,$type,$fid);
				$contex = $contexObj->current();
				
				if($contexObj->count()>0)
				{
					$contex->setPid($this->settings['pid']);
					$contex->setDatum(new \DateTime());
					$contex->setCharterPeriode($charterPeriode);
					$contex->setType($type);
					$contex->setBetrag($value); 
					$contex->setContexfirmenId($obj);
					$this->contexRepository->update($contex);		
				}
				else
				{
					$newContex = new \SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex;
					$newContex->setPid($this->settings['pid']);
					$newContex->setDatum(new \DateTime());
					$newContex->setCharterPeriode($charterPeriode);
					$newContex->setType($type);
					$newContex->setBetrag($value);
					$newContex->setContexfirmenId($obj);
					$this->contexRepository->add($newContex);		
				}
			}
		}
		$this->addFlashMessage('Der Contex wurde erstellt. Vielen Dank.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::OK);
		$this->redirect('new');
	}

	/**
	 * 
	 */
	public function createContexAction() {
		$this->checkPermission();
		$datum = $_POST['tx_dslcontex_contex']['c']['datum'];
		$d = new \DateTime($datum);
		$fid = $_POST['tx_dslcontex_contex']['c']['contexfirmen_id'];
		$firma = $this->contexFirmenRepository->findByUid($fid);	
		
		foreach ($_POST['contex'] as $charterPeriode => $types) {
			foreach ($types as $type => $betrag) {
				$newContex = new \SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex;
				$newContex->setPid($this->settings['pid']);
				$newContex->setDatum($d);
				$newContex->setCharterPeriode($charterPeriode);
				$newContex->setType($type);
				$newContex->setBetrag($betrag['betrag']);
				$newContex->setContexfirmenId($firma);
				$this->contexRepository->add($newContex);	
			}
		}		
		$this->addFlashMessage('Der Contex für '.$firma->getFirmenname().'/'.$d->format("d.m.Y").'  wurde erstellt. Vielen Dank.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::OK);
		$this->redirect('list');		
	}

	/**
	 * action update
	 *
	 * @param \SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $contex
	 * @return void
	 */
	public function updateAction(\SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $contex) {
		$this->contexRepository->update($contex);
		$this->addFlashMessage('Sie haben den Contex erfolgreich erneuert.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::OK);
		$this->redirect('edit');
	}
	
	/**
	 * action updateContex array
	 *
	 * 
	 * 
	 * @return void
	 */
	public function updateContexAction() {
		$this->checkPermission();
		if($this->settings['inAdminList']==True)
		{	
			foreach ($_POST['tx_dslcontex_contex']['contex'] as $uid => $value) {
				$contex = $this->contexRepository->findByUid($uid);
				if(null === $contex){
				    continue;
                }
				$contex->setBetrag($value['betrag']);
				$this->contexRepository->update($contex);
			}
			$this->addFlashMessage('Sie haben den Contex erfolgreich erneuert.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::OK);
			$this->redirect('edit');
		}
		else
		{
			$this->addFlashMessage('Sie haben keine Berechtigungen für diesen Bereich.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::ERROR);
			$this->redirect('list');			
		}			
	}	

	/**
	 * action delete
	 *
	 * @param \SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $contex
	 * @return void
	 */
	public function deleteAction(\SiqnetConsultingGmbH\Dslcontex\Domain\Model\Contex $contex) {
		$this->checkPermission();
		if($this->settings['inAdminList']==True)
		{	
			$this->addFlashMessage('Sie haben diesen ConTex erfolgreich gelöscht.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::ERROR);
			$this->contexRepository->remove($contex);
			$this->redirect('list');
		}
		else
		{
			$this->addFlashMessage('Sie haben keine Berechtigungen für diesen Bereich.', '', \TYPO3\CMS\Core\Messaging\AbstractMessage::ERROR);
			$this->redirect('list');			
		}		
	}
	
	
	/**
	 * action excelpreview
	 * creates an excel sheet
	 */
	public function excelAction() {

		$this->checkPermission();
		$cah = new \ContexAdditionalHelpers();
		
		$objPHPExcel = new \PHPExcel();
		$objPHPExcel->getProperties()
			->setCreator('siQnet Consulting GmbH')
			->setLastModifiedBy('siQnet Consulting GmbH')
			->setTitle('Contex')
			->setSubject('Contex')
			->setDescription('Contex')
			->setKeywords("New Contex")
			->setCategory("Contex");

			$excel_spalten = array(
				'A' => 'Datum'
			);

			// Create a new worksheet, after the default sheet
			$worksheets = array(
				 0 => array(
						'title'          => 'Übersicht',
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				 1 => array(
						'title'          => utf8_encode('1100'),
						'type'           => 1100,
						'charter_period' => 12,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				 2 => array(
						'title'          => utf8_encode('1700'),
						'type'           => 1700,
						'charter_period' => 12,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				 3 => array(
						'title'          => utf8_encode('2500'),
						'type'           => 2500,
						'charter_period' => 24,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				 4 => array(
						'title'          => utf8_encode('2700'),
						'type'           => 2700,
						'charter_period' => 24,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				 5 => array(
						'title'          => utf8_encode('3500'),
						'type'           => 3500,
						'charter_period' => 24,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				 6 => array(
						'title'          => utf8_encode('4250'),
						'type'           => 4250,
						'charter_period' => 24,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				 7 => array(
						'title'          => utf8_encode('6M 1100'),
						'type'           => 1100,
						'charter_period' => 6,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				 8 => array(
						'title'          => utf8_encode('6M 1700'),
						'type'           => 1700,
						'charter_period' => 6,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				 9 => array(
						'title'          => utf8_encode('12M 2500'),
						'type'           => 2500,
						'charter_period' => 12,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				10 => array(
						'title'          => utf8_encode('12M 2700'),
						'type'           => 2700,
						'charter_period' => 12,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				11 => array(
						'title'          => utf8_encode('12M 3500'),
						'type'           => 3500,
						'charter_period' => 12,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				12 => array(
						'title'          => utf8_encode('12M 4250'),
						'type'           => 4250,
						'charter_period' => 12,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				13 => array ( //DSL NEUER 5700 TYPE 
						'title'          => utf8_encode('12M 5700'),
						'type'           => 5700,
						'charter_period' => 12,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen					
				),
				14 => array ( //DSL NEUER 6500 TYPE 
						'title'          => utf8_encode('12M 6500'),
						'type'           => 6500,
						'charter_period' => 12,
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen										
				),
				15 => array(
						'title'          => utf8_encode('Index'),
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					),
				16 => array(
						'title'          => utf8_encode('Index gerundet'),
						'freezepane'     => 'B2' // Zeile 1 und Spalte A einfrieren (Scrollt nicht mit sondern bleibt an der Startstellung stehen
					)
			);


			// Worksheets in Excel anlegen
			foreach($worksheets as $worksheet_id => $worksheet_attr) {
				// Erste ID überspringen, da dieses Worksheet automatisch angelegt wird
				if ($worksheet_id > 0) {
					$objPHPExcel->createSheet();
				}
				// Titel der Tabs setzen
				$objPHPExcel->setActiveSheetIndex($worksheet_id)->setTitle($worksheet_attr['title']);
				// Feststellen von Zeilen und Spalten
				if (isset($worksheet_attr['freezepane'])) {
					$objPHPExcel->getActiveSheet()->freezePane($worksheet_attr['freezepane']);
				}
			}

			// Übersicht
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Datum');

			$contex_firmens = $this->contexFirmenRepository->getAllFirmen(0, 'ASC');

			foreach ($contex_firmens as $key => $data) {
				
				if(isset($_GET['anonym']) && ($_GET['anonym']==1 || $_GET['anonym']==2))
				{
					$firmenname = 'Anonym';
					$kurzname = 'Anonym';
				}
				else
				{
					$firmenname = $data->getFirmenname();
					$kurzname = $data->getKurzname();
				}

					$contex_firmen[$data->getUid()] = array(
						'firmenname'      => $firmenname,
						'kurzname'        => $kurzname,
						'ansprechpartner' => $data->getAnsprechpartner(),
						'telefon'         => $data->getTelefon(),
						'email'           => $data->getEmail(),
						'aktiv'           => $data->getAktiv(),
						'in_contex'		  => $data->getInContex(),
						'excel_spalte'    => $data->getExcelSpalte()
					);
			}


			// VHBS
			unset($contex_firmen[1]);
			unset($contex_firmen[22]);
			// Daten Holen
			
			$datum = new \DateTime();
			
			if($_GET['anonym']==2)
			{
				$datum->modify("-18 month");
			}
			else
			{
				$datum->modify("-3 month");
			}
			
			if(isset($_GET['datum'])) {
				$datum = new \DateTime($_GET['datum']);
			}
			
			
			$contex_datumswertes = $this->contexRepository->getContexDatum(0,$datum->format("Y-m-d"));
			
			foreach ($contex_datumswertes as $key => $data) {
				$contex_datumswerte[$data->getDatum()->format('Y-m-d')] = $data->getDatum()->format('Y-m-d');
			}
			

			foreach($worksheets as $worksheet_id => $worksheet_attr) {
				if (!isset($worksheet_attr['type'])) {
					// Kann man keinem Worksheet zuordnen also besser überspringen
					continue;
				}

				ksort($contex_firmen);
				reset($contex_firmen);
				$objPHPExcel
					->setActiveSheetIndex($worksheet_id)
					->setCellValue('A1', 'Datum');

				$spalte = 1;
				foreach($contex_firmen as $firmen_id => $firma) {
					$spalte = $firma['excel_spalte'];
					$objPHPExcel
						->setActiveSheetIndex($worksheet_id)
						->setCellValue($spalte.'1', $firma['kurzname']);
					$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
					$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->getStartColor()->setRGB('C0C0C0');

					reset($contex_datumswerte);
					$zeile_temp = 2;
					$col_values = array();

					$attr = array(
						'contexfirmen_id' => $firmen_id,
						'charter_periode' => $worksheet_attr['charter_period'],
						'type'            => $worksheet_attr['type'],
						'abdatum'		  => $datum->format("Y-m-d"),
					);
					$contex_betraeges = $this->contexRepository->get_contex_betraege($attr);

					foreach ($contex_betraeges as $key => $data) {
						$contex_betraege[$data->getDatum()->format("Y-m-d")] = $data->getBetrag();
					}
					

					foreach($contex_datumswerte as $contex_datumswert) {
						$betrag = 0;
						if (isset($contex_betraege[$contex_datumswert])) {
							$betrag = $contex_betraege[$contex_datumswert];
						}
						if (($firmen_id == 20 && $contex_datumswert < '2014-11-13') || $firma['in_contex']==0) {
							$betrag = 0;
						}

						
						if ($betrag == 0) {
							$betrag = 'keine';
							$betrag_merker = 0;
						} else {
							$col_values[$spalte] = $betrag;
							if (!isset($betrag_merker)) {
								$betrag_merker = 0;
							}
							$objPHPExcel->setActiveSheetIndex($worksheet_id)->getStyle($spalte.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
							if ($betrag_merker > 0 && $betrag > 0 && abs((1/$betrag_merker*$betrag)-1) > 0.05) {
								$objPHPExcel->getActiveSheet()->getStyle($spalte.$zeile_temp)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
								$objPHPExcel->getActiveSheet()->getStyle($spalte.$zeile_temp)->getFill()->getStartColor()->setRGB('FF0000');
							}
							// Bei mehr als 500 abweichung
							$diff = $betrag-$betrag_merker;
							if ((!empty($betrag_merker) && !empty($betrag)) && ($diff >= 500 || $diff <= -500)) {
								$objPHPExcel->getActiveSheet()->getStyle($spalte.$zeile_temp)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
								$objPHPExcel->getActiveSheet()->getStyle($spalte.$zeile_temp)->getFill()->getStartColor()->setRGB('FF0000');
							}
							$betrag_merker = $betrag;
						}
						//DSL NAVIS SPALTE AB DATUM WECHSELN
						if ($firmen_id == 21 && $contex_datumswert < '2015-09-15') 
						{
							$spalte = 'V';
							
							$objPHPExcel
								->setActiveSheetIndex($worksheet_id)
								->setCellValue($spalte.'1', $firma['kurzname'].' - ALT');	
							$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
							$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->getStartColor()->setRGB('C0C0C0');							
							
						}
						elseif ($firmen_id == 21 && $contex_datumswert >= '2015-09-15') 
						{
							$spalte = 'U';
						}
						

						$objPHPExcel
							->setActiveSheetIndex($worksheet_id)
							->setCellValue($spalte.$zeile_temp, $betrag);

						$zeile_temp++;
					}
					unset($contex_betraege);
					// Um kleinsten Wert rauszubekommen
					asort($col_values);
					reset($col_values);
					list(, $value) = each($col_values);

					$spalte++;
				}
				

				//$spalte = chr(ord($spalte));
				$spalte++;
				$spalte++;
				$spalte++;
				
				$objPHPExcel->setActiveSheetIndex($worksheet_id)->setCellValue($spalte.'1', 'Average');
				$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
				$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->getStartColor()->setRGB('C0C0C0');$spalte++;
				$objPHPExcel->setActiveSheetIndex($worksheet_id)->setCellValue($spalte.'1', 'Min');
				$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
				$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->getStartColor()->setRGB('C0C0C0');$spalte++;
				$objPHPExcel->setActiveSheetIndex($worksheet_id)->setCellValue($spalte.'1', 'Max');
				$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
				$objPHPExcel->getActiveSheet()->getStyle($spalte.'1')->getFill()->getStartColor()->setRGB('C0C0C0');$spalte++;
			}
			// Spalte A mit Datumswerte vorbelegen
			$anzahl_datumswerte = count($contex_datumswerte);
			
			$anz = 16;
			$anz2 = 15;
			
			for ($i = 0; $i <= $anz; $i++) {
				$zeile_temp = 2;
				foreach($contex_datumswerte as $contex_datumswert) {
					$objPHPExcel
						->setActiveSheetIndex($i)
						->setCellValue('A'.$zeile_temp, $cah->vbg_convert_datetime($contex_datumswert, 'Y-m-d', 'd.m.Y'));
					$objPHPExcel->getActiveSheet()->getStyle('A'.$zeile_temp)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
					$objPHPExcel->getActiveSheet()->getStyle('A'.$zeile_temp)->getFill()->getStartColor()->setRGB('C0C0C0');

					if ($i > 0 && $i < $anz2) {
						$objPHPExcel->getActiveSheet()->setCellValue('Y'.($zeile_temp), '=ROUND(AVERAGE(B'.$zeile_temp.':U'.$zeile_temp.'), 0)');
						$objPHPExcel->getActiveSheet()->getStyle('Y'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
						$objPHPExcel->getActiveSheet()->setCellValue('Z'.($zeile_temp), '=MIN(B'.$zeile_temp.':U'.$zeile_temp.')');
						$objPHPExcel->getActiveSheet()->getStyle('Z'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
						$objPHPExcel->getActiveSheet()->setCellValue('AA'.($zeile_temp), '=MAX(B'.$zeile_temp.':U'.$zeile_temp.')');
						$objPHPExcel->getActiveSheet()->getStyle('AA'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
					}
					if ($zeile_temp < ($anzahl_datumswerte-15)) {
						if($_GET['anonym']==2){
							$objPHPExcel->getActiveSheet()->getRowDimension(2)->setVisible(FALSE);
						}
						elseif(isset($_GET['datum']))
						{
							$objPHPExcel->getActiveSheet()->getRowDimension($zeile_temp)->setVisible(TRUE);
						}
						else
						{
							$objPHPExcel->getActiveSheet()->getRowDimension($zeile_temp)->setVisible(FALSE);
						}
						
					}

					$zeile_temp++;
				}
			}


			// Tabellenblatt Index
			$objPHPExcel
				->setActiveSheetIndex(15)
				->setCellValue('A1', 'Datum')
				->setCellValue('B1', '1100')
				->setCellValue('C1', '1700')
				->setCellValue('D1', '2500')
				->setCellValue('E1', 'CONTEX OLD')
				->setCellValue('F1', '2700')
				->setCellValue('G1', '3500')
				->setCellValue('H1', '4250')
				->setCellValue('I1', 'NEW CONTEX')
				->setCellValue('J1', 'Hilfsrechnung')
				->setCellValue('K1', '1100')
				->setCellValue('L1', '1700')
				->setCellValue('M1', '2500')
				->setCellValue('N1', '2700')
				->setCellValue('O1', '3250')
				->setCellValue('P1', '4250');
//DSL ->setCellValue('P1', '5700');

			$zeile_temp = 2;
			$objPHPExcel
				->setActiveSheetIndex(15)
				->setCellValue('T1', 'Multiplier')
				->setCellValue('U1', '14.67763043')
				->setCellValue('V1', '19.25540449')
				->setCellValue('W1', '22.60611008')
				->setCellValue('X1', '23.895298')
				->setCellValue('Y1', '24.49493078')
				->setCellValue('Z1', '31.89385572');
				/*
				->setCellValue('T1', 'Multiplier')
				->setCellValue('U1', '15.82974345')
				->setCellValue('V1', '17.10195032')
				->setCellValue('W1', '22.27022914')
				->setCellValue('X1', '21.92742456')
				->setCellValue('Y1', '25.74386728')
				->setCellValue('Z1', '46.40518145');*/

			foreach($contex_datumswerte as $contex_datumswert) {
				$objPHPExcel
					->setActiveSheetIndex(15)
					/* jsc 5.3.2020 auskommentiert
					->setCellValue('B'.$zeile_temp, '=\'1100\'!Y'.$zeile_temp)
					->setCellValue('C'.$zeile_temp, '=\'1700\'!Y'.$zeile_temp)
					->setCellValue('D'.$zeile_temp, '=\'2500\'!Y'.$zeile_temp)
					->setCellValue('E'.$zeile_temp, '=AVERAGE(M'.$zeile_temp.':O'.$zeile_temp.')')
					->setCellValue('F'.$zeile_temp, '=\'2700\'!Y'.$zeile_temp)
					->setCellValue('G'.$zeile_temp, '=\'3500\'!Y'.$zeile_temp)
					->setCellValue('H'.$zeile_temp, '=\'4250\'!Y'.$zeile_temp)
					*/
					
					->setCellValue('B'.$zeile_temp, '=\'6M 1100\'!Y'.$zeile_temp)
					->setCellValue('C'.$zeile_temp, '=\'6M 1700\'!Y'.$zeile_temp)
					->setCellValue('D'.$zeile_temp, '=\'12M 2500\'!Y'.$zeile_temp)
					->setCellValue('E'.$zeile_temp, '=AVERAGE(M'.$zeile_temp.':O'.$zeile_temp.')')
					->setCellValue('F'.$zeile_temp, '=\'12M 2700\'!Y'.$zeile_temp)
					->setCellValue('G'.$zeile_temp, '=\'12M 3500\'!Y'.$zeile_temp)
					->setCellValue('H'.$zeile_temp, '=\'12M 4250\'!Y'.$zeile_temp)
					
					#->setCellValue('I'.$zeile_temp, '=\'12M 5700\'!Y'.$zeile_temp)
					#->setCellValue('J'.$zeile_temp, '=\'12M 6500\'!Y'.$zeile_temp)
					
					->setCellValue('I'.$zeile_temp, '=(B'.$zeile_temp.'/U1+C'.$zeile_temp.'/V1+D'.$zeile_temp.'/W1+F'.$zeile_temp.'/X1+G'.$zeile_temp.'/Y1+H'.$zeile_temp.'/Z1)/6');
					//FRAGE: MUESSEN WEITERE MULTIPLIER FÜR DIE 2 NEUEN TYPEN ERSTELLT WERDEN UND IN DIESE RECHNUNG EINFLIESSEN?
					#->setCellValue('K'.$zeile_temp, '=(B'.$zeile_temp.'/W1+C'.$zeile_temp.'/X1+D'.$zeile_temp.'/Y1+F'.$zeile_temp.'/Z1+G'.$zeile_temp.'/AA1+H'.$zeile_temp.'/AB1)/6');
				
					$objPHPExcel->getActiveSheet()->getStyle('E'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
					$objPHPExcel->getActiveSheet()->getStyle('I'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
					$objPHPExcel->getActiveSheet()->getStyle('E'.$zeile_temp)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
					$objPHPExcel->getActiveSheet()->getStyle('E'.$zeile_temp)->getFill()->getStartColor()->setRGB('00FF00');
					$objPHPExcel->getActiveSheet()->getStyle('I'.$zeile_temp)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
					$objPHPExcel->getActiveSheet()->getStyle('I'.$zeile_temp)->getFill()->getStartColor()->setRGB('00FF00');

				if ($zeile_temp > 2) {
					$objPHPExcel
						->setActiveSheetIndex(15)
						->setCellValue('K'.$zeile_temp, '=K'.($zeile_temp-1).'/B'.($zeile_temp-1).'*B'.$zeile_temp)
						->setCellValue('L'.$zeile_temp, '=L'.($zeile_temp-1).'/C'.($zeile_temp-1).'*C'.$zeile_temp)
						->setCellValue('M'.$zeile_temp, '=M'.($zeile_temp-1).'/D'.($zeile_temp-1).'*D'.$zeile_temp)
						->setCellValue('N'.$zeile_temp, '=N'.($zeile_temp-1).'/F'.($zeile_temp-1).'*F'.$zeile_temp)
						->setCellValue('O'.$zeile_temp, '=O'.($zeile_temp-1).'/G'.($zeile_temp-1).'*G'.$zeile_temp)
						->setCellValue('P'.$zeile_temp, '=P'.($zeile_temp-1).'/H'.($zeile_temp-1).'*H'.$zeile_temp)

						;
						$objPHPExcel->getActiveSheet()->getStyle('K'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
						$objPHPExcel->getActiveSheet()->getStyle('L'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
						$objPHPExcel->getActiveSheet()->getStyle('M'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
						$objPHPExcel->getActiveSheet()->getStyle('N'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
						$objPHPExcel->getActiveSheet()->getStyle('O'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
						$objPHPExcel->getActiveSheet()->getStyle('P'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');						
						

						}

				if ($contex_datumswert == '2011-08-04') {
					$objPHPExcel
					->setActiveSheetIndex(15)
					->setCellValue('K'.$zeile_temp, '627')
					->setCellValue('L'.$zeile_temp, '598')							
					->setCellValue('M'.$zeile_temp, '537')
					->setCellValue('N'.$zeile_temp, '559')
					->setCellValue('O'.$zeile_temp, '557')
					->setCellValue('P'.$zeile_temp, '651');
				}
				$zeile_temp++;
			}

			// Tabellenblatt Index gerundet
			$objPHPExcel
				->setActiveSheetIndex(16)
				->setCellValue('A1', 'Datum')
				->setCellValue('B1', '1100')
				->setCellValue('C1', '1700')
				->setCellValue('D1', '2500')
				->setCellValue('E1', '2700')
				->setCellValue('F1', '3500')
				->setCellValue('G1', '4250')
				->setCellValue('H1', 'CONTEX NEW')
				->setCellValue('I1', '12M 2500')
				->setCellValue('J1', '12M 2700')
				->setCellValue('K1', '12M 3500')
				->setCellValue('L1', '12M 4250')
				->setCellValue('M1', '12M 5700') //DSL 6M 1100 Nach unten verschoben
				->setCellValue('N1', '12M 6500') //DSL 6M 1700 Nach unten verschoben				
				->setCellValue('O1', '6M 1100')
				->setCellValue('P1', '6M 1700')
				//->setCellValue('Q1', '6M 1800') // jsc 27.9.23
				;
			$zeile_temp = 2;

			foreach($contex_datumswerte as $contex_datumswert) {
				$objPHPExcel
					->setActiveSheetIndex(16)
					->setCellValue('B'.$zeile_temp, '=ROUND(\'Index\'!B'.$zeile_temp.', 0)')
					->setCellValue('C'.$zeile_temp, '=ROUND(\'Index\'!C'.$zeile_temp.', 0)')
					->setCellValue('D'.$zeile_temp, '=ROUND(\'Index\'!D'.$zeile_temp.', 0)')
					->setCellValue('E'.$zeile_temp, '=ROUND(\'Index\'!F'.$zeile_temp.', 0)')
					->setCellValue('F'.$zeile_temp, '=ROUND(\'Index\'!G'.$zeile_temp.', 0)')
					->setCellValue('G'.$zeile_temp, '=ROUND(\'Index\'!H'.$zeile_temp.', 0)')
					->setCellValue('H'.$zeile_temp, '=ROUND(\'Index\'!I'.$zeile_temp.', 0)')
					->setCellValue('I'.$zeile_temp, '=ROUND(\'12M 2500\'!Y'.$zeile_temp.', 0)')
					->setCellValue('J'.$zeile_temp, '=ROUND(\'12M 2700\'!Y'.$zeile_temp.', 0)')
					->setCellValue('K'.$zeile_temp, '=ROUND(\'12M 3500\'!Y'.$zeile_temp.', 0)')
					->setCellValue('L'.$zeile_temp, '=ROUND(\'12M 4250\'!Y'.$zeile_temp.', 0)')

					->setCellValue('M'.$zeile_temp, '=ROUND(\'12M 5700\'!Y'.$zeile_temp.', 0)') //DSL 6M 1100 Nach unten verschoben
					->setCellValue('N'.$zeile_temp, '=ROUND(\'12M 6500\'!Y'.$zeile_temp.', 0)') //DSL 6M 1700 Nach unten verschoben	
					
					->setCellValue('O'.$zeile_temp, '=ROUND(\'6M 1100\'!Y'.$zeile_temp.', 0)')
					->setCellValue('P'.$zeile_temp, '=ROUND(\'6M 1700\'!Y'.$zeile_temp.', 0)')
					//->setCellValue('Q'.$zeile_temp, '=ROUND(\'6M 1800\'!Y'.$zeile_temp.', 0)') // jsc 27.9.23
					;
					$objPHPExcel->getActiveSheet()->getStyle('H'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');

				$zeile_temp++;
			}

			// Tabellenblatt Übersicht
			$objPHPExcel
				->setActiveSheetIndex(0)
				->setCellValue('A1', 'Datum')
				->setCellValue('B1', 'CONTEX NEW')
				->setCellValue('D1', '1100')
				->setCellValue('F1', '1700')
				->setCellValue('H1', '2500')
				->setCellValue('J1', '2700')
				->setCellValue('L1', '3500')
				->setCellValue('N1', '4250')
				->setCellValue('P1', '12M 2500')
				->setCellValue('R1', '12M 2700')
				->setCellValue('T1', '12M 3500')
				->setCellValue('V1', '12M 4250')
				->setCellValue('X1', '12M 5700')
				->setCellValue('Z1', '12M 6500')
				->setCellValue('AB1', '6M 1100')
				->setCellValue('AD1', '6M 1700')
				//->setCellValue('AF1', '6M 1800') // jsc 27.9.23
				;

			$zeile_temp = 2;

			foreach($contex_datumswerte as $contex_datumswert) {
				$objPHPExcel
					->setActiveSheetIndex(0)
					->setCellValue('B'.$zeile_temp, '=\'Index gerundet\'!H'.$zeile_temp)
					->setCellValue('C'.$zeile_temp, '=B'.$zeile_temp.'-B'.($zeile_temp-1))
					->setCellValue('D'.$zeile_temp, '=\'1100\'!Y'.$zeile_temp)				// jsc 10.8.23
					//->setCellValue('D'.$zeile_temp, '=\'Index gerundet\'!B'.$zeile_temp)	// jsc 10.8.23
					->setCellValue('E'.$zeile_temp, '=D'.$zeile_temp.'-D'.($zeile_temp-1))
					->setCellValue('F'.$zeile_temp, '=\'1700\'!Y'.$zeile_temp)				// jsc 10.8.23
					//->setCellValue('F'.$zeile_temp, '=\'Index gerundet\'!C'.$zeile_temp)	// jsc 10.8.23
					->setCellValue('G'.$zeile_temp, '=F'.$zeile_temp.'-F'.($zeile_temp-1))
					//->setCellValue('H'.$zeile_temp, '=\'Index gerundet\'!D'.$zeile_temp)
					->setCellValue('H'.$zeile_temp, '=\'2500\'!Y'.$zeile_temp)
					->setCellValue('I'.$zeile_temp, '=H'.$zeile_temp.'-H'.($zeile_temp-1))
					//->setCellValue('J'.$zeile_temp, '=\'Index gerundet\'!E'.$zeile_temp)
					->setCellValue('J'.$zeile_temp, '=\'2700\'!Y'.$zeile_temp)
					->setCellValue('K'.$zeile_temp, '=J'.$zeile_temp.'-J'.($zeile_temp-1))
					//->setCellValue('L'.$zeile_temp, '=\'Index gerundet\'!F'.$zeile_temp)
					->setCellValue('L'.$zeile_temp, '=\'3500\'!Y'.$zeile_temp)
					->setCellValue('M'.$zeile_temp, '=L'.$zeile_temp.'-L'.($zeile_temp-1))
					//->setCellValue('N'.$zeile_temp, '=\'Index gerundet\'!G'.$zeile_temp)
					->setCellValue('N'.$zeile_temp, '=\'4250\'!Y'.$zeile_temp)
					->setCellValue('O'.$zeile_temp, '=N'.$zeile_temp.'-N'.($zeile_temp-1))
					->setCellValue('P'.$zeile_temp, '=\'Index gerundet\'!I'.$zeile_temp)
					->setCellValue('Q'.$zeile_temp, '=P'.$zeile_temp.'-P'.($zeile_temp-1))
					->setCellValue('R'.$zeile_temp, '=\'Index gerundet\'!J'.$zeile_temp)
					->setCellValue('S'.$zeile_temp, '=R'.$zeile_temp.'-R'.($zeile_temp-1))
					->setCellValue('T'.$zeile_temp, '=\'Index gerundet\'!K'.$zeile_temp)
					->setCellValue('U'.$zeile_temp, '=T'.$zeile_temp.'-T'.($zeile_temp-1))
					->setCellValue('V'.$zeile_temp, '=\'Index gerundet\'!L'.$zeile_temp)
					->setCellValue('W'.$zeile_temp, '=V'.$zeile_temp.'-V'.($zeile_temp-1))
					->setCellValue('X'.$zeile_temp, '=\'Index gerundet\'!M'.$zeile_temp)
					->setCellValue('Y'.$zeile_temp, '=X'.$zeile_temp.'-X'.($zeile_temp-1))
					->setCellValue('Z'.$zeile_temp, '=\'Index gerundet\'!N'.$zeile_temp)
					->setCellValue('AA'.$zeile_temp, '=Z'.$zeile_temp.'-Z'.($zeile_temp-1))
					->setCellValue('AB'.$zeile_temp, '=\'Index gerundet\'!O'.$zeile_temp)
					->setCellValue('AC'.$zeile_temp, '=AB'.$zeile_temp.'-AB'.($zeile_temp-1))
					->setCellValue('AD'.$zeile_temp, '=\'Index gerundet\'!P'.$zeile_temp)
					->setCellValue('AE'.$zeile_temp, '=AD'.$zeile_temp.'-AD'.($zeile_temp-1))						
						
					;
				$objPHPExcel->getActiveSheet()->getStyle('B'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
				$objPHPExcel->getActiveSheet()->getStyle('C'.$zeile_temp)->getNumberFormat()->setFormatCode('#,##0');
				$zeile_temp++;
			}

			$zeile = 0;
			$objPHPExcel->setActiveSheetIndex(0);


			foreach($excel_spalten as $key => $value) {
				$objPHPExcel->getActiveSheet()->setCellValue($key.$zeile, $value);
				$objPHPExcel->getActiveSheet()->getStyle($key.$zeile)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
				$objPHPExcel->getActiveSheet()->getStyle($key.$zeile)->getFill()->getStartColor()->setRGB('C0C0C0');
				$objPHPExcel->getActiveSheet()->getColumnDimension($key)->setAutoSize(true);
			}

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

		   $objPHPExcel->getSecurity()->setLockWindows(true);
		   $objPHPExcel->getSecurity()->setLockStructure(true);
		   $objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");

		   // Excel Datei rausschreiben
		   $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
			
		   $date = date('d.m.Y');
		   $excel['path'] = 'typo3conf/ext/dslcontex/Resources/Public/excel/';
		  
		   if(isset($_GET['anonym']) && ($_GET['anonym']==1 || $_GET['anonym']==2))
			{
				$excel['file'] = 'ContexAnonym_'.$date.'.xlsx';
			}
			elseif(isset($_GET['datum']))
			{
				$excel['file'] = 'Contex_AB[2015-09-01]_'.$date.'.xlsx';
			}
			else
			{
				$excel['file'] = 'Contex_'.$date.'.xlsx';
			}
		   
		   
		   $objWriter->save($excel['path'].$excel['file']);
		   $this->view->assign('excel',$excel);
	}

}