package br.com.catequese.dao;
import br.com.caelum.vraptor.interceptor.download.InputStreamDownload;
import br.com.caelum.vraptor.ioc.Component;
import br.com.catequese.to.Aluno;
import br.com.catequese.to.ChamadaItem;
import com.mysql.jdbc.Connection;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigInteger;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletContext;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperRunManager;
import org.apache.catalina.connector.Response;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
@Component
public class AlunoDao {
private final Session session;
public AlunoDao(Session session) {
this.session = session;
}
public void salva(Aluno aluno){
Transaction tx = session.beginTransaction();
session.save(aluno);
tx.commit();
}
public void excluir(Aluno aluno) throws Exception{
try{
Transaction tx = session.beginTransaction();
session.delete(aluno);
tx.commit();
}catch(Exception ex){
throw new Exception("Erro ao excluir Aluno. <br />O mesmo tem referência a outros dados.");
}
}
public List<Aluno> listaTudo(){
return this.session.createCriteria(Aluno.class).addOrder(Order.asc("nome")).list();
}
public Aluno carrega(Integer id) {
return (Aluno) this.session.load(Aluno.class, id);
}
public void atualiza(Aluno aluno) {
Transaction tx = session.beginTransaction();
this.session.update(aluno);
tx.commit();
}
public List<Aluno> busca(String nome) {
return session.createCriteria(Aluno.class)
.add(Restrictions.ilike("nome", nome, MatchMode.ANYWHERE))
.list();
}
public Aluno carregaCodigo(long d) throws Exception {
Transaction tx = session.beginTransaction();
String sql = "FROM Aluno WHERE codigo = " + d;
Aluno aluno = null;
Object object;
try{
object = (Aluno) session.createQuery(sql).uniqueResult();
}catch(Exception e){
throw new Exception("erro");
}
if(object == null)
throw new Exception("erro");
return (Aluno) object;
}
public long ultimaPosicao() throws Exception {
Criteria c = this.session.createCriteria(Aluno.class);
c.setProjection(Projections.max("idAluno"));
int num = (Integer) c.uniqueResult();
long codigo = (12301 * num);
return codigo;
}
private Connection getConexao() throws SQLException, ClassNotFoundException{
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/catequese";
String usuario = "root";
String senha = "";
con = (Connection) DriverManager.getConnection(url,usuario,senha);
}catch(SQLException sql){
System.out.println("erro ao conectar");
sql.printStackTrace();
}
return con;
}
public InputStreamDownload relAlunos(String turma) throws JRException, SQLException, ClassNotFoundException {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/relAlunos.jasper");
Map parametros = new HashMap();
parametros.put("turma", turma);
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioAlunos.pdf", true, os.toByteArray().length);
}
public InputStreamDownload relAlunosCarteirinhas(String turma) throws JRException, SQLException, ClassNotFoundException {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/carteirinha.jasper");
Map parametros = new HashMap();
parametros.put("turma", turma);
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioCarterinhas.pdf", true, os.toByteArray().length);
}
/*public InputStreamDownload relChamada(String turma, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/relChamada.jasper");
Map parametros = new HashMap();
parametros.put("turma", turma);
parametros.put("dtInicio", formataData(dtInicio));
parametros.put("dtFim", formataData(dtFim));
String sql = "SELECT paroquia.`nome` AS paroquia_nome, aluno.`nome` AS aluno_nome, chamada.`dtChamada` AS chamada_dtChamada, " +
"decanato.`nome` AS decanato_nome, chamadaitem.`ispresenca` AS chamadaitem_ispresenca, chamadaitem.`chamada_num` AS chamadaitem_chamada_num," +
"disciplina.`nome` AS disciplina_nome FROM `aluno` aluno INNER JOIN `chamadaitem` chamadaitem ON aluno.`idAluno` = chamadaitem.`aluno` " +
"INNER JOIN `chamada` chamada ON chamadaitem.`chamada` = chamada.`id`" +
"INNER JOIN `disciplina` disciplina ON chamada.`disciplina` = disciplina.`idDisciplina`" +
"INNER JOIN `turma` turma ON disciplina.`idTurma` = turma.`idturma` AND aluno.`idTurma` = turma.`idturma`" +
"INNER JOIN `paroquia` paroquia ON aluno.`paroquia` = paroquia.`id`" +
"INNER JOIN `decanato` decanato ON aluno.`decanato` = decanato.`idDecanato`" +
"WHERE turma.`descricao` = $P{turma} and chamada.`dtChamada` between $P{dtInicio} and $P{dtFim}" +
"ORDER BY chamada.`dtChamada` ASC, aluno.`nome` ASC";
List lista = session.createQuery(sql).list();
lista.get(1);
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioChamadaGeral.pdf", true, os.toByteArray().length);
}*/
public InputStreamDownload relChamada(String turma, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/relChamada.jasper");
Map parametros = new HashMap();
parametros.put("turma", turma);
parametros.put("dtInicio", formataData(dtInicio));
parametros.put("dtFim", formataData(dtFim));
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioChamadaGeral.pdf", true, os.toByteArray().length);
}
public InputStreamDownload relChamadaDecanato(String nome, String turma, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/relChamadaDecanato.jasper");
Map parametros = new HashMap();
parametros.put("decanato", nome);
parametros.put("turma", turma);
parametros.put("dtInicio", formataData(dtInicio));
parametros.put("dtFim", formataData(dtFim));
//parametros.put("codigo", "6503000");
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioChamadaDecanato.pdf", true, os.toByteArray().length);
}
public InputStreamDownload relChamadaDecanatoS(String nome, String turma, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/relChamadaDecanatoS.jasper");
Map parametros = new HashMap();
parametros.put("decanato", nome);
parametros.put("turma", turma);
parametros.put("dtInicio", formataData(dtInicio));
parametros.put("dtFim", formataData(dtFim));
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioPresencaDecanato.pdf", true, os.toByteArray().length);
}
public InputStreamDownload relChamadaDecanatoN(String nome, String turma, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/relChamadaDecanatoN.jasper");
Map parametros = new HashMap();
parametros.put("decanato", nome);
parametros.put("turma", turma);
parametros.put("dtInicio", formataData(dtInicio));
parametros.put("dtFim", formataData(dtFim));
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioFaltaDecanato.pdf", true, os.toByteArray().length);
}
public InputStreamDownload relChamadaParoquia(String nome, String turma, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/relChamadaParoquia.jasper");
Map parametros = new HashMap();
parametros.put("paroquia", nome);
parametros.put("turma", turma);
parametros.put("dtInicio", formataData(dtInicio));
parametros.put("dtFim", formataData(dtFim));
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioChamadaParoquia.pdf", true, os.toByteArray().length);
}
public InputStreamDownload relChamadaParoquiaS(String nome, String turma, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/relChamadaParoquiaS.jasper");
Map parametros = new HashMap();
parametros.put("paroquia", nome);
parametros.put("turma", turma);
parametros.put("dtInicio", formataData(dtInicio));
parametros.put("dtFim", formataData(dtFim));
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioPresencaParoquia.pdf", true, os.toByteArray().length);
}
public InputStreamDownload relChamadaParoquiaN(String nome, String turma, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStream file = getClass().getClassLoader().getResourceAsStream("/br/com/catequese/relatorio/relChamadaParoquiaN.jasper");
Map parametros = new HashMap();
parametros.put("paroquia", nome);
parametros.put("turma", turma);
parametros.put("dtInicio", formataData(dtInicio));
parametros.put("dtFim", formataData(dtFim));
ByteArrayOutputStream os = new ByteArrayOutputStream();
JasperRunManager.runReportToPdfStream(file, os, parametros, getConexao());
InputStream document = new ByteArrayInputStream(os.toByteArray());
return new InputStreamDownload(document, "application/pdf", "relatorioFaltaParoquia.pdf", true, os.toByteArray().length);
}
public String formataData(String data)throws Exception{
String retorno = "";
try{
retorno = data.substring(6,10)+"-" + data.substring(3,5) + "-" +data.substring(0,2);
}
catch(Exception e) {
throw new Exception("Erro na conversão da data" +
"\nMensagem original: " + e.getMessage());
}
return retorno;
}
}
package br.com.catequese.controller;
import br.com.caelum.vraptor.Delete;
import br.com.caelum.vraptor.Get;
import br.com.caelum.vraptor.Path;
import br.com.caelum.vraptor.Post;
import br.com.caelum.vraptor.Put;
import br.com.caelum.vraptor.Resource;
import br.com.caelum.vraptor.Result;
import br.com.caelum.vraptor.Validator;
import br.com.caelum.vraptor.interceptor.download.Download;
import br.com.caelum.vraptor.interceptor.download.FileDownload;
import br.com.caelum.vraptor.interceptor.download.InputStreamDownload;
import br.com.caelum.vraptor.validator.Hibernate;
import br.com.catequese.dao.AlunoDao;
import br.com.catequese.dao.DecanatoDao;
import br.com.catequese.dao.EstadoCivilDao;
import br.com.catequese.dao.GrauInstrucaoDao;
import br.com.catequese.dao.ParoquiaDao;
import br.com.catequese.dao.TurmaDao;
import br.com.catequese.to.Aluno;
import java.io.File;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import static br.com.caelum.vraptor.view.Results.*;
import net.sf.jasperreports.engine.JRException;
@Resource
public class AlunoController {
private final AlunoDao dao;
private final TurmaDao turmaDao;
private final DecanatoDao decanatoDao;
private final ParoquiaDao paroquiaDao;
private final GrauInstrucaoDao grauInstrucaoDao;
private final EstadoCivilDao estadoCivilDao;
private final Result result;
private final Validator validator;
public AlunoController(AlunoDao dao, TurmaDao turmaDao, DecanatoDao decanatoDao, ParoquiaDao paroquiaDao, GrauInstrucaoDao grauInstrucaoDao, EstadoCivilDao estadoCivilDao, Result result, Validator validator) {
this.dao = dao;
this.turmaDao = turmaDao;
this.decanatoDao = decanatoDao;
this.paroquiaDao = paroquiaDao;
this.grauInstrucaoDao = grauInstrucaoDao;
this.estadoCivilDao = estadoCivilDao;
this.result = result;
this.validator = validator;
}
@Post @Path("/aluno/adiciona")
public void adiciona(Aluno aluno) throws Exception{
validator.addAll(Hibernate.validate(aluno));
validator.onErrorUse(page()).of(AlunoController.class).formulario();
dao.salva(aluno);
result.redirectTo(AlunoController.class).lista();
}
@Delete @Path("/aluno/{idAluno}")
public void remove(Integer idAluno) throws Exception {
Aluno d = dao.carrega(idAluno);
dao.excluir(d);
result.redirectTo(AlunoController.class).lista();
}
@Get @Path("/aluno/{idAluno}")
public Aluno edita(Integer idAluno){
this.result.include("estadoCivils", estadoCivilDao.listaTudo());
this.result.include("grauInstrucaos", grauInstrucaoDao.listaTudo());
this.result.include("paroquias", paroquiaDao.listaTudo());
this.result.include("decanatos", decanatoDao.listaTudo());
this.result.include("turmas", turmaDao.listaTudo());
return dao.carrega(idAluno);
}
@Put @Path("/aluno/{aluno.idAluno}")
public void altera(Aluno aluno) {
validator.addAll(Hibernate.validate(aluno));
validator.onErrorUse(page()).of(AlunoController.class).edita(aluno.getIdAluno());
dao.atualiza(aluno);
result.redirectTo(AlunoController.class).lista();
}
@Get @Path("/aluno/novo")
public void formulario() throws Exception{
this.result.include("estadoCivils", estadoCivilDao.listaTudo());
this.result.include("grauInstrucaos", grauInstrucaoDao.listaTudo());
this.result.include("paroquias", paroquiaDao.listaTudo());
this.result.include("decanatos", decanatoDao.listaTudo());
this.result.include("codigo", dao.ultimaPosicao());
this.result.include("turmas", turmaDao.listaTudo());
}
@Get @Path("/aluno/todos")
public List<Aluno> lista(){
return dao.listaTudo();
}
public List<Aluno> busca(String nome) {
result.include("nome", nome);
return dao.busca(nome);
}
@Get @Path("/aluno/busca.json")
public void buscaJson(String q) {
result.use(json()).from(dao.busca(q))
.serialize();
}
@Get @Path("/aluno/relatorioAlunos")
public Download relatorioAlunos(Aluno aluno) throws JRException, SQLException, ClassNotFoundException {
InputStreamDownload relatorio = dao.relAlunos(aluno.getTurma().getDescricao());
return relatorio;
}
@Get @Path("/aluno/relAlunos")
public void relAlunos() {
this.result.include("turmas", turmaDao.listaTudo());
}
@Get @Path("/aluno/relatorioCarterinhas")
public Download relatorioCarterinhas(Aluno aluno) throws JRException, SQLException, ClassNotFoundException {
InputStreamDownload relatorio = dao.relAlunosCarteirinhas(aluno.getTurma().getDescricao());
return relatorio;
}
@Get @Path("/aluno/relCarterinha")
public void relAlunoCarterinha() {
this.result.include("turmas", turmaDao.listaTudo());
}
@Get @Path("/aluno/relChamadaDecanato")
public void relAlunoChamadaDecanato() {
this.result.include("turmas", turmaDao.listaTudo());
this.result.include("decanatos", decanatoDao.listaTudo());
}
@Get @Path("/aluno/relChamadaDecanatoS")
public void relAlunoChamadaDecanatoS() {
this.result.include("turmas", turmaDao.listaTudo());
this.result.include("decanatos", decanatoDao.listaTudo());
}
@Get @Path("/aluno/relChamadaDecanatoN")
public void relAlunoChamadaDecanatoN() {
this.result.include("turmas", turmaDao.listaTudo());
this.result.include("decanatos", decanatoDao.listaTudo());
}
@Get @Path("/aluno/relChamadaParoquia")
public void relAlunoChamadaParoquia() {
this.result.include("turmas", turmaDao.listaTudo());
this.result.include("paroquias", paroquiaDao.listaTudo());
}
@Get @Path("/aluno/relChamadaParoquiaS")
public void relAlunoChamadaParoquiaS() {
this.result.include("turmas", turmaDao.listaTudo());
this.result.include("paroquias", paroquiaDao.listaTudo());
}
@Get @Path("/aluno/relChamadaParoquiaN")
public void relAlunoChamadaParoquiaN() {
this.result.include("turmas", turmaDao.listaTudo());
this.result.include("paroquias", paroquiaDao.listaTudo());
}
@Get @Path("/aluno/relChamada")
public void relChamada() {
this.result.include("turmas", turmaDao.listaTudo());
}
@Get @Path("/aluno/relChamadaGeral")
public Download relatorioChamada(Aluno aluno, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStreamDownload relatorio = dao.relChamada(aluno.getTurma().getDescricao(), dtInicio, dtFim);
return relatorio;
}
@Post @Path("/aluno/relAlunosChamadaDecanato")
public Download relatorioChamadaDecanato(Aluno aluno, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStreamDownload relatorio = dao.relChamadaDecanato(aluno.getDecanato().getNome(), aluno.getTurma().getDescricao(), dtInicio, dtFim);
return relatorio;
}
@Post @Path("/aluno/relAlunosChamadaDecanatoS")
public Download relatorioChamadaDecanatoS(Aluno aluno, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStreamDownload relatorio = dao.relChamadaDecanatoS(aluno.getDecanato().getNome(), aluno.getTurma().getDescricao(), dtInicio, dtFim);
return relatorio;
}
@Post @Path("/aluno/relAlunosChamadaDecanatoN")
public Download relatorioChamadaDecanatoN(Aluno aluno, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStreamDownload relatorio = dao.relChamadaDecanatoN(aluno.getDecanato().getNome(), aluno.getTurma().getDescricao(), dtInicio, dtFim);
return relatorio;
}
@Post @Path("/aluno/relAlunosChamadaParoquia")
public Download relatorioChamadaParoquia(Aluno aluno, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
System.out.println(dtInicio);
System.out.println(dtFim);
System.out.println(aluno.getParoquia().getNome());
System.out.println(aluno.getTurma().getDescricao());
InputStreamDownload relatorio = dao.relChamadaParoquia(aluno.getParoquia().getNome(), aluno.getTurma().getDescricao(), dtInicio, dtFim);
return relatorio;
}
@Post @Path("/aluno/relAlunosChamadaParoquiaS")
public Download relatorioChamadaParoquiaS(Aluno aluno, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStreamDownload relatorio = dao.relChamadaParoquiaS(aluno.getParoquia().getNome(), aluno.getTurma().getDescricao(), dtInicio, dtFim);
return relatorio;
}
@Post @Path("/aluno/relAlunosChamadaParoquiaN")
public Download relatorioChamadaParoquiaN(Aluno aluno, String dtInicio, String dtFim) throws JRException, SQLException, ClassNotFoundException, Exception {
InputStreamDownload relatorio = dao.relChamadaParoquiaN(aluno.getParoquia().getNome(), aluno.getTurma().getDescricao(), dtInicio, dtFim);
return relatorio;
}
}