public int updateUsuario(BeanAluno al)
{
String sql = "UPDATE SchoolManager.dbo.cadaluno SET nome_aluno = @nome_aluno, dt_nasc = @dt_nasc, turma_fk = @turma_fk, cadescola_fk = @cadescola_fk "
+ " WHERE idaluno= @id ;";
int i = 0;
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@nome_aluno", al.Nome_aluno);
command.Parameters.AddWithValue("@dt_nasc", al.Dt_nasc);
command.Parameters.AddWithValue("@turma_fk", al.Turma.Nome_turma);
command.Parameters.AddWithValue("@cadescola_fk", al.Escola.Cnpj);
command.Parameters.AddWithValue("@id", al.Idaluno);
try
{
i = command.ExecuteNonQuery();
return(i);
}
catch (Exception e)
{
Debug.WriteLine("Falha ao atualizar dados: " + e);
return(i);
}
}
}
}
public int insertUsuario(BeanAluno al)
{
int i = 0;
String sql = "INSERT INTO SchoolManager.dbo.cadaluno(nome_aluno,dt_nasc,turma_fk,responsavel_fk) VALUES(@nome_aluno,@dt_nasc,@turma_fk,@responsavel_fk);";
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@nome_aluno", al.Nome_aluno);
command.Parameters.AddWithValue("@dt_nasc", al.Dt_nasc);
command.Parameters.AddWithValue("@turma_fk", al.Turma.Nome_turma);
//command.Parameters.AddWithValue("@cadescola_fk", al.Escola.Cnpj);
command.Parameters.AddWithValue("@responsavel_fk", al.Responsavel.Idresponsavel);
try
{
i = command.ExecuteNonQuery();
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Falha ao inserir dados: " + e);
}
}
}
return(i);
}
public List <BeanAluno> selectAll()
{
List <BeanAluno> list = new List <BeanAluno>();
String sql = "SELECT DISTINCT * FROM SchoolManager.dbo.cadaluno ca,SchoolManager.dbo.responsavel res, "
+ " SchoolManager.dbo.turmas tu WHERE ca.responsavel_fk = res.idresponsavel AND ca.turma_fk = tu.nome_turma "
+ " ORDER BY ca.idaluno DESC";
SqlConnection conn;
// BeanUsuario usu = new BeanUsuario();
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
try
{
SqlDataReader objDataReader = command.ExecuteReader();
if (objDataReader.HasRows)
{
while (objDataReader.Read())
{
BeanAluno al = new BeanAluno();
BeanEscola esc = new BeanEscola();
BeanResponsavel res = new BeanResponsavel();
BeanTurma tu = new BeanTurma();
al.Idaluno = int.Parse(objDataReader["idaluno"].ToString());
al.Nome_aluno = objDataReader["nome_aluno"].ToString();
al.Dt_nasc = Convert.ToDateTime(objDataReader["dt_nasc"].ToString());
tu.Nome_turma = objDataReader["turma_fk"].ToString();
al.Turma = tu;
/*esc.Cnpj = objDataReader["cadescola_fk"].ToString();
* esc.Nome_escola = objDataReader["nome_escola"].ToString();
* esc.Email = objDataReader["email"].ToString();
* al.Escola = esc;*/
res.Idresponsavel = int.Parse(objDataReader["responsavel_fk"].ToString());
res.Nome_responsavel = objDataReader["nome_responsavel"].ToString();
al.Responsavel = res;
list.Add(al);
}
objDataReader.Close();
}
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Erros ao selecionar todos usuarios: " + e);
}
}
}
return(list);
}
public String updateAluno(String json)
{
JavaScriptSerializer js = new JavaScriptSerializer();
BeanAluno obje = js.Deserialize <BeanAluno>(json);
AlunoDAO al = new AlunoDAO();
int i = 0;
i = al.updateUsuario(obje);
return(js.Serialize(i));
}
public String alunoID(String json)
{
JavaScriptSerializer js = new JavaScriptSerializer();
BeanAluno obje = js.Deserialize <BeanAluno>(json);
AlunoDAO al = new AlunoDAO();
List <BeanAluno> list = new List <BeanAluno>();
list = al.selectID(obje);
return(js.Serialize(list));
}
public List <BeanAluno> selectID(BeanAluno al)
{
List <BeanAluno> list = new List <BeanAluno>();
String sql = "SELECT DISTINCT * FROM SchoolManager.dbo.cadaluno WHERE idaluno = @id";
SqlConnection conn;
// BeanUsuario usu = new BeanUsuario();
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@id", al.Idaluno);
try
{
SqlDataReader objDataReader = command.ExecuteReader();
if (objDataReader.HasRows)
{
while (objDataReader.Read())
{
BeanAluno a = new BeanAluno();
BeanEscola esc = new BeanEscola();
BeanResponsavel res = new BeanResponsavel();
BeanTurma tu = new BeanTurma();
a.Idaluno = int.Parse(objDataReader["idaluno"].ToString());
a.Nome_aluno = objDataReader["nome_aluno"].ToString();
a.Dt_nasc = Convert.ToDateTime(objDataReader["dt_nasc"].ToString());
tu.Nome_turma = objDataReader["turma_fk"].ToString();
a.Turma = tu;
esc.Cnpj = objDataReader["cadescola_fk"].ToString();
a.Escola = esc;
res.Idresponsavel = int.Parse(objDataReader["idresponsavel_fk"].ToString());
a.Responsavel = res;
list.Add(a);
}
objDataReader.Close();
}
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Erros ao selecionar todos usuarios: " + e);
}
}
}
return(list);
}
protected void enviar_Click(object sender, EventArgs e)
{
/*lb_msg.Text = "Relatório enviado com sucesso!";
* tb_nome.Text = "";
* msgtb.Text = "";*/
//Response.Redirect("relatorio.aspx");
Utils utils = new Utils();
String rel = "";
String json = "";
BeanAluno ba = new BeanAluno();
BeanHistorico bh = new BeanHistorico();
if (id.Text == "")
{
ba.Idaluno = int.Parse(tb_nome.Text);
bh.Aluno = ba;
bh.Msg = msgtb.Text;
json = new JavaScriptSerializer().Serialize(bh);
rel = ws.historicoIn(json);
if (rel.Equals("\"0\""))
{
Session["msgBD"] = "Problema ao enviar relatório";
Response.Redirect("relatorio.aspx");
}
else if (rel.Equals("\"1\""))
{
Session["msgBD"] = "Relatório enviado com sucesso!";
Response.Redirect("relatorio.aspx");
}
else if (rel.Equals("\"2\""))
{
Session["msgBD"] = "Erro";
Response.Redirect("c.aspx");
}
else
{
Session["msgBD"] = "Eduardo falo que Isac mangou de mim!" + rel;
Response.Redirect("relatorio.aspx");
}
}
}
public String alunoDel(String json)
{
int i = 0;
try
{
// Deserializando a string para Object
JavaScriptSerializer serializer = new JavaScriptSerializer();
BeanAluno obje = serializer.Deserialize <BeanAluno>(json);
AlunoDAO al = new AlunoDAO();
i = al.deleteUsuario(obje);
}
catch (WebException ex)
{
Debug.WriteLine("Erro: " + ex);
}
return(i.ToString());
}
public List <BeanHistorico> selectAll()
{
List <BeanHistorico> list = new List <BeanHistorico>();
String sql = "SELECT DISTINCT * FROM SchoolManager.dbo.historico ORDER BY idhistorico DESC";
SqlConnection conn;
// BeanUsuario usu = new BeanUsuario();
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
try
{
SqlDataReader objDataReader = command.ExecuteReader();
if (objDataReader.HasRows)
{
while (objDataReader.Read())
{
BeanHistorico his = new BeanHistorico();
BeanAluno al = new BeanAluno();
his.Idhistorico = int.Parse(objDataReader["idhistorico"].ToString());
al.Idaluno = int.Parse(objDataReader["idaluno_fk"].ToString());
his.Aluno = al;
his.Msg = objDataReader["msg"].ToString();
list.Add(his);
}
objDataReader.Close();
}
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Erros ao selecionar todos usuarios: " + e);
}
}
}
return(list);
}
protected void listaUsuarios_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int i = e.RowIndex;
String idaluno = ((Label)listaUsuarios.Rows[i].FindControl("Lid")).Text;
Debug.WriteLine("RowDeleting chamado Indice da Linha: " + i + "ID:" + idaluno);
BeanAluno u = new BeanAluno();
u.Idaluno = int.Parse(idaluno);
JavaScriptSerializer serializer = new JavaScriptSerializer();
String result = ws.alunoDel(serializer.Serialize(u));
if (result == "0")
{
Session["msg"] = "Aluno não foi excluido!";
Response.Redirect("cadastroaluno.aspx");
}
else
{
Session["msg"] = "Aluno excluído com sucesso!";
Response.Redirect("cadastroaluno.aspx");
}
}
public int deleteUsuario(BeanAluno al)
{
String sql = "DELETE FROM SchoolManager.dbo.cadaluno WHERE idaluno = @pid";
SqlConnection conn;
int i = 0;
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@pid", al.Idaluno);
try
{
i = command.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("Falha ao deletar dados: " + e);
}
conn.Close();
}
}
return(i);
}
protected void enviar_Click(object sender, EventArgs e)
{
Utils utils = new Utils();
String rel = "";
String json = "";
BeanAluno ba = new BeanAluno();
BeanTurma bt = new BeanTurma();
BeanResponsavel br = new BeanResponsavel();
if (id.Text == "")
{
ba.Nome_aluno = tb_nome.Text;
ba.Dt_nasc = DateTime.Parse(tb_datanasc.Text);
bt.Nome_turma = tb_turma.Text;
ba.Turma = bt;
br.Idresponsavel = int.Parse(tb_responsavel.Text);
ba.Responsavel = br;
/*nome_aluno = tb_nome.Text,
* dt_nasc = tb_datanasc.Text,
* turma_fk = tb_turma.Text,
* responsavel_fk = tb_responsavel.Text*/
json = new JavaScriptSerializer().Serialize(ba);
rel = ws.alunoIn(json);
if (rel.Equals("\"0\""))
{
Session["msg"] = "Problema ao inserir aluno";
Response.Redirect("cadastroaluno.aspx");
}
else if (rel.Equals("\"1\""))
{
Session["msg"] = "Aluno inserido com sucesso!";
Response.Redirect("cadastroaluno.aspx");
}
else if (rel.Equals("\"2\""))
{
Session["msg"] = "Aluno já cadastrado no sistema!";
Response.Redirect("cadastroaluno.aspx");
}
}
else
{
json = new JavaScriptSerializer().Serialize(new
{
idaluno = id.Text,
nome_aluno = tb_nome.Text,
dt_nasc = tb_datanasc.Text,
turma_fk = tb_turma.Text,
responsavel_fk = tb_responsavel.Text
});
rel = ws.alunoDel(json);
Debug.WriteLine("Valor do retorno: " + rel);
if (rel == "0")
{
Session["msg"] = "Problemas ao atualizar aluno";
Response.Redirect("painelescola.aspx");
}
else if (rel == "1")
{
Session["msg"] = "Aluno atualizado com sucesso!";
Response.Redirect("painelescola.aspx");
}
else if (rel == "2")
{
Session["msg"] = "Aluno já cadastrado no sistema!";
Response.Redirect("painelescola.aspx");
}
}
}
