TransWar File Browser

Path: C:\

<?php
  ini_set('max_execution_time',259200); 
  ini_set ('memory_limit','-1');
  setlocale(LC_TIME, 'pt_BR', 'pt_BR.utf-8', 'pt_BR.utf-8', 'portuguese');
  date_default_timezone_set('America/Sao_Paulo');
  $dateadded = date("d/m/Y  --  G:i"); 
	require_once('../vendor/autoload.php');
	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
  $link=0;
  $_SESSION['Link']  = $link;
  $excel = $_SESSION['Excel'];
  if($excel=='N'){		
	  echo "<script type=\"text/javascript\">alert(\"Não foram carregados os Loggers.\");location.href='main.php';</script>";
	}
	$nr_pqd 	= $_SESSION['Nr_PQD'];     
  $conjunto = $_SESSION['Conjunto'];
	$json0 		= file_get_contents('json/conjuntos.json');
  $conj0 		= json_decode($json0, true);
  $conj  		= $conj0[$conjunto];
	define('URL_JotFORM', 'https://api.jotform.com/form/');
  define('KEY_JotFORM', '9659ead4a973f60dc8b567df2e4f259d');  
  define('FORM_ID', '242474572155661');		// FO.06.03.PQD.T07
	$url = URL_JotFORM.'/'.FORM_ID.'/submissions?apikey='.KEY_JotFORM.'&filter={"status":"ACTIVE","q101":"'.$nr_pqd.'"}&limit=1000';
	$curl = curl_init($url);
	curl_setopt($curl, CURLOPT_URL, $url);
	curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
	curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
	$resp = curl_exec($curl);
	curl_close($curl);
	$data=json_decode($resp, true);
  // pega valores do jotform
	$placa=trim(strtok($data['content'][0]['answers'][101]['answer'], " "));
	$ini['dd'] 	 = $data['content'][0]['answers'][118]['answer']['day'];
	$ini['mm'] 	 = $data['content'][0]['answers'][118]['answer']['month'];
	$ini['aaaa'] = $data['content'][0]['answers'][118]['answer']['year'];
	$ini['hhmm'] = $data['content'][0]['answers'][111]['answer']['timeInput'];
	$T07_dt_ini	 = $ini['aaaa'].'-'.$ini['mm'].'-'.$ini['dd'].' '.$ini['hhmm'];
	$fim['dd'] 	 = $data['content'][0]['answers'][119]['answer']['day'];
	$fim['mm'] 	 = $data['content'][0]['answers'][119]['answer']['month'];
	$fim['aaaa'] = $data['content'][0]['answers'][119]['answer']['year'];
	$fim['hhmm'] = $data['content'][0]['answers'][117]['answer']['timeInput'];
	$T07_dt_fim	 = $fim['aaaa'].'-'.$fim['mm'].'-'.$fim['dd'].' '.$fim['hhmm'];
	$dt_ini=strtotime($T07_dt_ini);
	$dt_fim=strtotime($T07_dt_fim);
	$temp_max=8;$temp_min=2;
	$cont_tt=$temp_tt=0;
	$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
	$reader->setReadDataOnly(true);
  $path = 'dados/temp/';
  $nc=0;
  $diretorio = dir($path);
	$data = array();
	while($arquivo = $diretorio -> read()){
		if(substr($arquivo, strlen($arquivo)-4,4)=='xlsx'){
			$ct=explode("_", $arquivo);
			$arq[$nc]=$arquivo;
			$nome[$nc]=$ct[0];
			$spreadsheet[$nc] = $reader->load($path.$arq[$nc]);
			$sheet[$nc] = $spreadsheet[$nc]->getSheet(1);
			$cont=$med_t=$temp=$med_u=$umid=$max_tt=$min_tt=$max_uu=$min_uu=0;
			$max_t=$max_u=0;$min_t=$min_u=99;
			foreach ($sheet[$nc]->getRowIterator() as $row) {
				if ($row->getRowIndex() < 2) {continue;}
				$cells=iterator_to_array($row->getCellIterator("B", "D"));
				$dia=strtotime($cells["B"]->getValue());
        if ($dia < $dt_ini or $dia > $dt_fim) {continue;}					
        $cont++;
				$val_t=$cells["C"]->getValue();
				$val_u=$cells["D"]->getValue();
				$temp=$temp+$val_t;
				$umid=$umid+$val_u;
				if($val_t<=$min_t){$min_t=$val_t;}
				if($val_t>=$max_t){$max_t=$val_t;}
				if($val_u<=$min_u){$min_u=$val_u;}
				if($val_u>=$max_u){$max_u=$val_u;}
				$med_t=floatval(number_format($temp/$cont,1));
				$med_u=floatval(number_format($umid/$cont,1));
			}
			$data[$nome[$nc]] = [
				"minima_t"	=> $min_t,
				"media_t"		=> $med_t,
				"maxima_t"	=> $max_t,
				"minima_u"	=> $min_u,
				"media_u"		=> $med_u,
				"maxima_u"	=> $max_u
			];
			unset($spreadsheet[$nc]);
			$nc++;
		}
	}
	$diretorio -> close();
	unset($spreadsheet);
	$l1=1;
	$lin[1]=PHP_EOL;
	$lin[2]=';QUALIFICAÇÃO POR 12 HORAS COM SIMULAÇÃO DE CARGA (TESTE 07 – FO.06.03.PQD)'.PHP_EOL;
	$lin[3]=';;DATALOGGERS'.PHP_EOL;
	$lin[4]=';';
	$lin[5]=';;ºC;%RH;ºC;%RH;ºC;%RH;ºC;%RH;ºC;%RH;ºC;%RH';
	$lin[6]=';MÍNIMA';
	$lin[7]=';MÉDIA';
	$lin[8]=';MÁXIMA';
	$lin[9]=';;DATALOGGERS';
	$lin[10]=';';
	$lin[11]=';;ºC;%RH;ºC;%RH;ºC;%RH;ºC;%RH;ºC;%RH;ºC;%RH';
	$lin[12]=';MÍNIMA';
	$lin[13]=';MÉDIA';
	$lin[14]=';MÁXIMA';
	$lin[15]=PHP_EOL;
	$lin[16]=';;MÁXIMOS E MÍNIMOS LOCAIS'.PHP_EOL;
	$lin[17]=';;DADO;;;;DATALOGGER(S)'.PHP_EOL;
	$lin[18]=';;PONTO(S) COM TEMP.(ºC) MÍNIMA;;;;';
	$lin[19]=';;PONTO(S) COM TEMP.(ºC) MÁXIMA;;;;';
	$lin[20]=';;PONTO(S) COM UMID.(%RH) MÍNIMA;;;;';
	$lin[21]=';;PONTO(S) COM UMID.(%RH) MÁXIMA;;;;';
	$mais_baixo_t=99;$mais_alto_t=0;
	$logger_mais_baixo_t=$logger_mais_alto_t='';
	$mais_baixo_u=99;$mais_alto_u=0;
	$logger_mais_baixo_u=$logger_mais_alto_u='';
	foreach ($data as $logger => $col) {
		if($l1<7){
			$lin[4].=';'.$conj[$logger].';';
			$lin[6].=';'.$col['minima_t'].';'.$col['minima_u'];
			$lin[7].=';'.$col['media_t'].';'.$col['media_u'];
			$lin[8].=';'.$col['maxima_t'].';'.$col['maxima_u'];$l1++;
		}else{
			$lin[10].=';'.$conj[$logger].';';
			$lin[12].=';'.$col['minima_t'].';'.$col['minima_u'];
			$lin[13].=';'.$col['media_t'].';'.$col['media_u'];
			$lin[14].=';'.$col['maxima_t'].';'.$col['maxima_u'];$l1++;
		}
		if ($conj[$logger]=='E12' or $conj[$logger]=='E24' or $conj[$logger]=='E36') {continue;}
		if($l1>12){$l1=1;}
		if($col['minima_t']==$mais_baixo_t){
			$logger_mais_baixo_t.=' ('.$conj[$logger].")";
		}else{
			if($col['minima_t']<$mais_baixo_t){
				$logger_mais_baixo_t='('.$conj[$logger].")";$mais_baixo_t=$col['minima_t'];
			}
		}
		if($col['maxima_t']==$mais_alto_t){
			$logger_mais_alto_t.=' ('.$conj[$logger].")";
		}else{
			if($col['maxima_t']>$mais_alto_t){
				$logger_mais_alto_t='('.$conj[$logger].")";$mais_alto_t=$col['maxima_t'];
			}
		}
		if($col['minima_u']==$mais_baixo_u){
			$logger_mais_baixo_u.=' ('.$conj[$logger].")";
		}else{
			if($col['minima_u']<$mais_baixo_u){
				$logger_mais_baixo_u='('.$conj[$logger].")";$mais_baixo_u=$col['minima_u'];
			}
		}
		if($col['maxima_u']==$mais_alto_u){
			$logger_mais_alto_u.=' ('.$conj[$logger].")";
		}else{
			if($col['maxima_u']>$mais_alto_u){
				$logger_mais_alto_u='('.$conj[$logger].")";$mais_alto_u=$col['maxima_u'];
			}
		}
	}
	$lin[18].=$logger_mais_baixo_t.PHP_EOL;
	$lin[19].=$logger_mais_alto_t.PHP_EOL;
	$lin[20].=$logger_mais_baixo_u.PHP_EOL;
	$lin[21].=$logger_mais_alto_u.PHP_EOL;
	for($i=4;$i<15;$i++){$lin[$i].=PHP_EOL;}
	$arqcsv='temp.csv';
	$fptemp = fopen($arqcsv, 'w+');
	for($i=1;$i<22;$i++){fwrite($fptemp,$lin[$i]);}
	fclose($fptemp); 
	$data['criticos'] = [
		"mais baixo_t"	=> $logger_mais_baixo_t,
		"mais alto_t"		=> $logger_mais_alto_t,
		"mais baixo_u"	=> $logger_mais_baixo_u,
		"mais alto_u"		=> $logger_mais_alto_u
	];
	$arqxlsx='dados/excel/PQD_'.$nr_pqd.'_T07.xlsx';
	$spreadsheet = new Spreadsheet();
	$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
	$reader->setDelimiter(';');
	$reader->setEnclosure('"');
	$reader->setSheetIndex(0);
	$spreadsheet = $reader->load($arqcsv);
	$writer = new Xlsx($spreadsheet);
	$spreadsheet->getActiveSheet()->setTitle('PQD-T07');
	$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(35);
	$spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
	$spreadsheet->getActiveSheet()->getRowDimension('15')->setRowHeight(35);
	$spreadsheet->getActiveSheet()->getRowDimension('16')->setRowHeight(25);
	$styleArray0 = [
			'borders' => [
					'allBorders' => [
							'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
							'color' => ['argb' => 'ffffff'],
					],
			],
	];
	$styleArray = [
			'borders' => [
					'allBorders' => [
							'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
							'color' => ['argb' => 'ffffff'],
					],
			],
	];
	$styleArray1 = [
			'borders' => [
					'inside' => [
							'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
							'color' => ['argb' => '000000'],
					],
			],
	];
	$styleArray2 = [
			'borders' => [
					'outline' => [
							'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
							'color' => ['argb' => '000000'],
					],
			],
	];
	$spreadsheet->getActiveSheet()->getStyle('A1:N21')->getAlignment()->setWrapText(true);
	$spreadsheet->getActiveSheet()->getStyle('A1:N21')->getFont()->setSize(11);
	$spreadsheet->getActiveSheet()->getStyle('A2:N5')->getFont()->setSize(13);
	$spreadsheet->getActiveSheet()->getStyle('A9:N11')->getFont()->setSize(13);
	$spreadsheet->getActiveSheet()->getStyle('B16:G17')->getFont()->setSize(13);
	$spreadsheet->getActiveSheet()->getStyle('B2:N5')->getFont()->setBold(true);
	$spreadsheet->getActiveSheet()->getStyle('B2:B14')->getFont()->setBold(true);
	$spreadsheet->getActiveSheet()->getStyle('B9:N11')->getFont()->setBold(true);
	$spreadsheet->getActiveSheet()->getStyle('B16:G17')->getFont()->setBold(true);
	$spreadsheet->getActiveSheet()->getStyle('A1:N21')
		->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
	$spreadsheet->getActiveSheet()->getStyle('A1:N21')
		->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
	$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
	$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(10);
	$spreadsheet->getActiveSheet()->getStyle('A1:Z500')->applyFromArray($styleArray0);
	$spreadsheet->getActiveSheet()->getStyle('B3:N14')->applyFromArray($styleArray);
	$spreadsheet->getActiveSheet()->getStyle('C16:L21')->applyFromArray($styleArray);
	$spreadsheet->getActiveSheet()->getStyle('C6:N8')->applyFromArray($styleArray1);
	$spreadsheet->getActiveSheet()->getStyle('C12:N14')->applyFromArray($styleArray1);
	$spreadsheet->getActiveSheet()->getStyle('C18:L21')->applyFromArray($styleArray1);
	$spreadsheet->getActiveSheet()->getStyle('B2:N14')->applyFromArray($styleArray2);
	$spreadsheet->getActiveSheet()->getStyle('C16:L21')->applyFromArray($styleArray2);
	$spreadsheet->getActiveSheet()->getStyle('C3:N5')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('5882FA');
	$spreadsheet->getActiveSheet()->getStyle('C3:N5')
		->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$spreadsheet->getActiveSheet()->getStyle('C9:N11')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('5882FA');
	$spreadsheet->getActiveSheet()->getStyle('C9:N11')
		->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$spreadsheet->getActiveSheet()->getStyle('B3:B14')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('5882FA');
	$spreadsheet->getActiveSheet()->getStyle('B3:B14')
		->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$spreadsheet->getActiveSheet()->getStyle('C17:L17')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('5882FA');
	$spreadsheet->getActiveSheet()->getStyle('C17:L17')
		->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$spreadsheet->getActiveSheet()->mergeCells('B2:N2');
	$spreadsheet->getActiveSheet()->mergeCells('C4:D4');
	$spreadsheet->getActiveSheet()->mergeCells('E4:F4');
	$spreadsheet->getActiveSheet()->mergeCells('G4:H4');
	$spreadsheet->getActiveSheet()->mergeCells('I4:J4');
	$spreadsheet->getActiveSheet()->mergeCells('K4:L4');
	$spreadsheet->getActiveSheet()->mergeCells('M4:N4');
	$spreadsheet->getActiveSheet()->mergeCells('C10:D10');
	$spreadsheet->getActiveSheet()->mergeCells('E10:F10');
	$spreadsheet->getActiveSheet()->mergeCells('G10:H10');
	$spreadsheet->getActiveSheet()->mergeCells('I10:J10');
	$spreadsheet->getActiveSheet()->mergeCells('K10:L10');
	$spreadsheet->getActiveSheet()->mergeCells('M10:N10');
	$spreadsheet->getActiveSheet()->mergeCells('C16:L16');
	$spreadsheet->getActiveSheet()->mergeCells('C17:F17');
	$spreadsheet->getActiveSheet()->mergeCells('G17:L17');
	$spreadsheet->getActiveSheet()->mergeCells('C18:F18');
	$spreadsheet->getActiveSheet()->mergeCells('G18:L18');
	$spreadsheet->getActiveSheet()->mergeCells('C19:F19');
	$spreadsheet->getActiveSheet()->mergeCells('G19:L19');
	$spreadsheet->getActiveSheet()->mergeCells('C20:F20');
	$spreadsheet->getActiveSheet()->mergeCells('G20:L20');
	$spreadsheet->getActiveSheet()->mergeCells('C21:F21');
	$spreadsheet->getActiveSheet()->mergeCells('G21:L21');
	$spreadsheet->getActiveSheet()->mergeCells('C3:N3');
	$spreadsheet->getActiveSheet()->mergeCells('C9:N9');
	$spreadsheet->getActiveSheet()->mergeCells('B3:B5');
	$spreadsheet->getActiveSheet()->mergeCells('B9:B11');
	$spreadsheet->getActiveSheet()->getStyle('E18:G21')->getAlignment()->setWrapText(true);
	$spreadsheet->getActiveSheet()->getStyle('B3:B5')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('ffffff');
	$spreadsheet->getActiveSheet()->getStyle('B9:B11')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('ffffff');
	$spreadsheet->getActiveSheet()->getStyle('B2:N2')->applyFromArray($styleArray2);

	$writer->save($arqxlsx);
	$spreadsheet->disconnectWorksheets();
	unset($spreadsheet);
	unlink($arqcsv);
	echo"<script language='JavaScript'>decisao = confirm('ARQUIVO EXCEL GERADO COM SUCESSO\\n\\nEFETUAR O DOWNLOAD?');if (decisao){location.href='main.php?arq=".$arqxlsx."';}else{location.href='main.php';}</script>";