<?php
//ECHO "AQUI";

include './conexao.php';
include '../PHPExcel-1.8/PHPExcel-1.8/Classes/PHPExcel.php';
$arquivo = 'inscritos.xls';

$seqBolsao = $_GET['b'];
$unidade = $_GET['u'];
$serie = $_GET['s'];
$turno = $_GET['t'];
$filtro='';
if ($serie <> '') {
    $filtro = $filtro . " and i.serie_ano='$serie'";
}
if ($turno <> '') {
    $filtro = $filtro . " and i.horario='$turno'";
}


$sql="select i.inscricao,initcap(trim(i.nome)) nome,i.data_nas,i.cep,i.tel,i.aluno_interno,i.origem,sm.serie_ano,u.nome as nome_unidade,i.endereco,"
        . "i.horario, i.data_inscricao,i.email,i.qtde_port,i.qtde_mat,i.compareceu, i.ano "
        . " from inscricoes i "
        . " inner join unidade u on u.id = i.unidade"
        . " inner join serie_ano_mensalidade sm on sm.id = i.id_serie_ano"
        . " where i.id_bolsao=$seqBolsao  and  i.unidade="
        . $unidade . $filtro . " order by i.serie_ano,i.nome";
//echo $sql;
$res=  mysqli_query($conexao, $sql);
$rowCount=2;
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('K1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('L1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('M1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('N1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('O1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('P1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('Q1')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Inscrição' );
$objPHPExcel->getActiveSheet()->setCellValue('B1', "Nome " );
$objPHPExcel->getActiveSheet()->setCellValue("C1", "Dt. Nascimento" );
$objPHPExcel->getActiveSheet()->setCellValue("D1", "Cep" );
$objPHPExcel->getActiveSheet()->setCellValue("E1", "Telefone" );
$objPHPExcel->getActiveSheet()->setCellValue("F1", "Aluno Interno" );
$objPHPExcel->getActiveSheet()->setCellValue("G1", "Origem" );
$objPHPExcel->getActiveSheet()->setCellValue("H1", "Série Pretendida" );
$objPHPExcel->getActiveSheet()->setCellValue("I1", "Unidade" );
$objPHPExcel->getActiveSheet()->setCellValue("J1", "Endereço" );
$objPHPExcel->getActiveSheet()->setCellValue("K1", "Turno" );
$objPHPExcel->getActiveSheet()->setCellValue("L1", "Dt. Inscrição" );
$objPHPExcel->getActiveSheet()->setCellValue("M1", "Email" );
$objPHPExcel->getActiveSheet()->setCellValue("N1", "Port" );
$objPHPExcel->getActiveSheet()->setCellValue("O1", "Mat" );
$objPHPExcel->getActiveSheet()->setCellValue("P1", "Compareceu" );
$objPHPExcel->getActiveSheet()->setCellValue("Q1", "Ano" );

while($row = mysqli_fetch_array($res)){
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['inscricao']);
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['nome']);
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, date('d/m/Y', strtotime($row['data_nas'])));
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $row['cep']);
$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['tel']);
$objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, $row['aluno_interno']);
$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, $row['origem']);
$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount, $row['serie_ano']);
$objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount, $row['nome_unidade']);
$objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount, $row['endereco']);
$objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowCount, $row['horario']);
$objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowCount,date('d/m/Y', strtotime($row['data_inscricao']))); 
$objPHPExcel->getActiveSheet()->SetCellValue('M'.$rowCount, $row['email']);
$objPHPExcel->getActiveSheet()->SetCellValue('N'.$rowCount, $row['qtde_port']);
$objPHPExcel->getActiveSheet()->SetCellValue('O'.$rowCount, $row['qtde_mat']);
$objPHPExcel->getActiveSheet()->SetCellValue('P'.$rowCount, $row['compareceu']);
$objPHPExcel->getActiveSheet()->SetCellValue('Q'.$rowCount, $row['ano']);
  $rowCount++;
  //pr($objPHPExcel);
  next($row);
}
header('Content-Type: application/vnd.openxmlformats-   officedocument.spreadsheetml.sheet');
 header('Content-Disposition: attachment;filename="Inscritos.xls"');
 header('Cache-Control: max-age=0');

 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 $objWriter->save('php://output');

?>

