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=$temp=$max_tt=$min_tt=0;
			$max=0;$min=99;
			foreach ($sheet[$nc]->getRowIterator() as $row) {
				if ($row->getRowIndex() < 2) {continue;}
				$cells=iterator_to_array($row->getCellIterator("B", "C"));
				$dia=strtotime($cells["B"]->getValue());
        if ($dia < $dt_ini or $dia > $dt_fim) {continue;}					
        $cont++;
				$val=$cells["C"]->getValue();
				$temp=$temp+$val;
				if($val<=$min){$min=$val;}
				if($val>=$max){$max=$val;}
				$med=floatval(number_format($temp/$cont,1));
			}
			$data[$nome[$nc]] = [
				"minima"	=> $min,
				"media"		=> $med,
				"maxima"	=> $max
			];
			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]=';Temperatura (°C);DATALOGGERS'.PHP_EOL;
	$lin[4]=';';
	$lin[5]=';MÍNIMA';
	$lin[6]=';MÉDIA';
	$lin[7]=';MÁXIMA';
	$lin[8]=';Temperatura (°C);DATALOGGERS';
	$lin[9]=';';
	$lin[10]=';MÍNIMA';
	$lin[11]=';MÉDIA';
	$lin[12]=';MÁXIMA';
	$lin[13]=PHP_EOL;
	$lin[14]=';;MÁXIMOS E MÍNIMOS LOCAIS'.PHP_EOL;
	$lin[15]=';;DADO;;DATALOGGER(S)'.PHP_EOL;
	$lin[16]=';;PONTO(S) COM O VALOR MÍNIMO;;';
	$lin[17]=';;PONTO(S) COM O VALOR MÁXIMO;;';
	$mais_baixo=99;$mais_alto=0;
	$logger_mais_baixo=$logger_mais_alto='';
	foreach ($data as $logger => $col) {
		if($l1<7){
			$lin[4].=';'.$conj[$logger];
			$lin[5].=';'.$col['minima'];
			$lin[6].=';'.$col['media'];
			$lin[7].=';'.$col['maxima'];$l1++;
		}else{
			$lin[9].=';'.$conj[$logger];
			$lin[10].=';'.$col['minima'];
			$lin[11].=';'.$col['media'];
			$lin[12].=';'.$col['maxima'];$l1++;
		}
		if ($conj[$logger]=='E12' or $conj[$logger]=='E24' or $conj[$logger]=='E36') {continue;}
		if($l1>12){$l1=1;}
		if($col['minima']==$mais_baixo){
			$logger_mais_baixo.='('.$conj[$logger].") ";
		}else{
			if($col['minima']<$mais_baixo){
				$logger_mais_baixo='('.$conj[$logger].") ";$mais_baixo=$col['minima'];
			}
		}
		if($col['maxima']==$mais_alto){
			$logger_mais_alto.='('.$conj[$logger].") ";
		}else{
			if($col['maxima']>$mais_alto){
				$logger_mais_alto='('.$conj[$logger].") ";$mais_alto=$col['maxima'];
			}
		}
	}
	$lin[16].=$logger_mais_baixo.PHP_EOL;
	$lin[17].=$logger_mais_alto.PHP_EOL;
	for($i=4;$i<13;$i++){$lin[$i].=PHP_EOL;}
	$arqcsv='temp.csv';
	$fptemp = fopen($arqcsv, 'w+');
	for($i=1;$i<18;$i++){fwrite($fptemp,$lin[$i]);}
	fclose($fptemp); 
	$data['criticos'] = [
		"mais baixo"	=> $logger_mais_baixo,
		"mais alto"		=> $logger_mais_alto
	];
	
	$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('13')->setRowHeight(35);
	$spreadsheet->getActiveSheet()->getRowDimension('14')->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:H17')->getAlignment()->setWrapText(true);
	$spreadsheet->getActiveSheet()->getStyle('A1:H17')->getFont()->setSize(11);
	$spreadsheet->getActiveSheet()->getStyle('A2:H4')->getFont()->setSize(13);
	$spreadsheet->getActiveSheet()->getStyle('A8:H9')->getFont()->setSize(13);
	$spreadsheet->getActiveSheet()->getStyle('B14:G15')->getFont()->setSize(13);
	$spreadsheet->getActiveSheet()->getStyle('B2:H4')->getFont()->setBold(true);
	$spreadsheet->getActiveSheet()->getStyle('B2:B12')->getFont()->setBold(true);
	$spreadsheet->getActiveSheet()->getStyle('B8:H9')->getFont()->setBold(true);
	$spreadsheet->getActiveSheet()->getStyle('B14:G15')->getFont()->setBold(true);
	$spreadsheet->getActiveSheet()->getStyle('C16:D17')->getFont()->setBold(true);
	$spreadsheet->getActiveSheet()->getStyle('A1:H17')
		->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
	$spreadsheet->getActiveSheet()->getStyle('A1:H17')
		->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
	$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
	$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
	$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(17);
	$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(17);
	$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(17);
	$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(17);
	$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(17);
	$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(17);
	$spreadsheet->getActiveSheet()->getStyle('A1:Z500')->applyFromArray($styleArray0);
	$spreadsheet->getActiveSheet()->getStyle('B2:H12')->applyFromArray($styleArray);
	$spreadsheet->getActiveSheet()->getStyle('C14:G17')->applyFromArray($styleArray);
	$spreadsheet->getActiveSheet()->getStyle('C5:H7')->applyFromArray($styleArray1);
	$spreadsheet->getActiveSheet()->getStyle('C10:H12')->applyFromArray($styleArray1);
	$spreadsheet->getActiveSheet()->getStyle('E16:G17')->applyFromArray($styleArray1);
	$spreadsheet->getActiveSheet()->getStyle('B2:H12')->applyFromArray($styleArray2);
	$spreadsheet->getActiveSheet()->getStyle('C14:G17')->applyFromArray($styleArray2);
	$spreadsheet->getActiveSheet()->getStyle('B2:H4')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('5882FA');
	$spreadsheet->getActiveSheet()->getStyle('B2:H4')
		->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$spreadsheet->getActiveSheet()->getStyle('B8:H9')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('5882FA');
	$spreadsheet->getActiveSheet()->getStyle('B8:H9')
		->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$spreadsheet->getActiveSheet()->getStyle('B2:B12')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('5882FA');
	$spreadsheet->getActiveSheet()->getStyle('B2:B12')
		->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$spreadsheet->getActiveSheet()->getStyle('C14:G15')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('5882FA');
	$spreadsheet->getActiveSheet()->getStyle('C14:G15')
		->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$spreadsheet->getActiveSheet()->getStyle('C16:D17')->getFill()
		->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
		->getStartColor()->setARGB('5882FA');
	$spreadsheet->getActiveSheet()->getStyle('C16:D17')
		->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$spreadsheet->getActiveSheet()->mergeCells('B2:H2');
	$spreadsheet->getActiveSheet()->mergeCells('C14:G14');
	$spreadsheet->getActiveSheet()->mergeCells('C15:D15');
	$spreadsheet->getActiveSheet()->mergeCells('E15:G15');
	$spreadsheet->getActiveSheet()->mergeCells('C16:D16');
	$spreadsheet->getActiveSheet()->mergeCells('E16:G16');
	$spreadsheet->getActiveSheet()->mergeCells('C17:D17');
	$spreadsheet->getActiveSheet()->mergeCells('E17:G17');
	$spreadsheet->getActiveSheet()->mergeCells('C17:D17');
	$spreadsheet->getActiveSheet()->mergeCells('E17:G17');
	$spreadsheet->getActiveSheet()->mergeCells('C3:H3');
	$spreadsheet->getActiveSheet()->mergeCells('C8:H8');
	$spreadsheet->getActiveSheet()->mergeCells('B3:B4');
	$spreadsheet->getActiveSheet()->mergeCells('B8:B9');
	$spreadsheet->getActiveSheet()->getStyle('E16:G17')->getAlignment()->setWrapText(true);

	$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>";