public int updateUsuario(BeanEscola u)
{
String sql = "UPDATE SchoolManager.dbo.cadescola SET email = @email, senha = @senha"
+ " WHERE cnpj= @id ;";
int i = 0;
using (conn = new Connection().abrirConexao())
{
if (this.searchEmailId(u.Email).ToString() != u.Cnpj.ToString())
{
return(2);
}
else
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@senha", u.Senha);
command.Parameters.AddWithValue("@email", u.Email);
command.Parameters.AddWithValue("@id", u.Cnpj);
try
{
i = command.ExecuteNonQuery();
return(i);
}
catch (Exception e)
{
Debug.WriteLine("Falha ao atualizar usuarios dados: " + e);
return(i);
}
}
}
}
}
public int insertUsuario(BeanEscola u)
{
int i = 0;
String sql = "INSERT INTO SchoolManager.dbo.cadescola(email,senha) VALUES(@email,@senha);";
using (conn = new Connection().abrirConexao())
{
if (this.searchEmail(u.Email))
{
return(2);
}
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@email", u.Email);
command.Parameters.AddWithValue("@senha", u.Senha);
try
{
i = command.ExecuteNonQuery();
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Falha ao inserir usuario dados: " + e);
}
}
}
return(i);
}
public int updateEscola(BeanEscola esc)
{
String sql = "UPDATE SchoolManager.dbo.cadescola SET nome_escola = @nome_escola, email = @email, senha = @senha WHERE cnpj= @id ";
int i = 0;
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@nome", esc.Nome_escola);
command.Parameters.AddWithValue("@email", esc.Email);
command.Parameters.AddWithValue("@senha", esc.Senha);
command.Parameters.AddWithValue("@id", esc.Cnpj);
try
{
i = command.ExecuteNonQuery();
return(i);
}
catch (Exception e)
{
Debug.WriteLine("Falha ao atualizar dados: " + e);
return(i);
}
}
}
}
public int insertEscola(BeanEscola esc)
{
int i = 0;
String sql = "INSERT INTO SchoolManager.dbo.cadescola(nome_escola,email,cnpj,senha) VALUES(@nome_escola,@email,@cnpj,@senha);";
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@nome_escola", esc.Nome_escola);
command.Parameters.AddWithValue("@email", esc.Email);
command.Parameters.AddWithValue("@cnpj", esc.Cnpj);
command.Parameters.AddWithValue("@senha", esc.Senha);
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 updateEscola(String json)
{
JavaScriptSerializer js = new JavaScriptSerializer();
BeanEscola obje = js.Deserialize <BeanEscola>(json);
EscolaDAO esc = new EscolaDAO();
int i = 0;
i = esc.updateEscola(obje);
return(js.Serialize(i));
}
public String escolaID(String json)
{
JavaScriptSerializer js = new JavaScriptSerializer();
BeanEscola obje = js.Deserialize <BeanEscola>(json);
EscolaDAO esc = new EscolaDAO();
List <BeanEscola> list = new List <BeanEscola>();
list = esc.selectCNPJ(obje);
return(js.Serialize(list));
}
public String loginEscola(String json)
{
JavaScriptSerializer js = new JavaScriptSerializer();
BeanEscola obje = js.Deserialize <BeanEscola>(json);
Boolean r = false;
EscolaDAO esc = new EscolaDAO();
r = esc.loginEscola(obje.Email, obje.Senha);
return(js.Serialize(r));
}
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 bt_logar_Click(object sender, EventArgs e)
{
String email = tb_email.Text;
String pass = tb_pass.Text;
Utils utils = new Utils();
if (email == "")
{
lb_msg.Text = "Campo email é requerido!";
tb_email.Focus();
}
else if (!utils.eValido(email))
{
lb_msg.Text = "Email inválido!!";
tb_email.Focus();
}
else if (pass.Length < 5)
{
lb_msg.Text = "Campo pass deve conte seis dígitos!";
tb_pass.Focus();
}
else
{
BeanEscola u = new BeanEscola();
u.Email = tb_email.Text;
u.Senha = tb_pass.Text;
JavaScriptSerializer serializer = new JavaScriptSerializer();
String json;
json = serializer.Serialize(u);
Debug.WriteLine("Resp:" + json);
webService.WSControlSoapClient ws = new webService.WSControlSoapClient();
String rel = ws.loginEscola(json);
if (rel == "true")
{
Session["email"] = email;
Session["logado"] = "OK";
Response.Redirect("cadastroresponsavel.aspx");
}
else
{
lb_msg.Text = "Email ou senha inválidos!";
tb_email.Focus();
}
}
}
public String escolaDel(String json)
{
int i = 0;
try
{
// Deserializando a string para Object
JavaScriptSerializer serializer = new JavaScriptSerializer();
BeanEscola obje = serializer.Deserialize <BeanEscola>(json);
EscolaDAO esc = new EscolaDAO();
i = esc.deleteEscola(obje);
}
catch (WebException ex)
{
Debug.WriteLine("Erro: " + ex);
}
return(i.ToString());
}
public List <BeanEscola> selectAll()
{
List <BeanEscola> list = new List <BeanEscola>();
String sql = "SELECT DISTINCT * FROM SchoolManager.dbo.cadescola ORDER BY cnpj 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())
{
BeanEscola usu = new BeanEscola();
usu.Cnpj = objDataReader["cnpj"].ToString();
usu.Email = objDataReader["email"].ToString();
usu.Senha = objDataReader["senha"].ToString();
list.Add(usu);
}
objDataReader.Close();
}
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Erros ao selecionar todos usuarios: " + e);
}
}
}
return(list);
}
public List <BeanEscola> selectCNPJ(BeanEscola a)
{
List <BeanEscola> list = new List <BeanEscola>();
String sql = "SELECT DISTINCT * FROM SchoolManager.dbo.cadescola WHERE cnpj = @cnpj";
SqlConnection conn;
// BeanUsuario usu = new BeanUsuario();
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@cnpj", a.Cnpj);
try
{
SqlDataReader objDataReader = command.ExecuteReader();
if (objDataReader.HasRows)
{
while (objDataReader.Read())
{
BeanEscola esc = new BeanEscola();
esc.Cnpj = objDataReader["cnpj"].ToString();
esc.Nome_escola = objDataReader["nome_escola"].ToString();
esc.Email = objDataReader["email"].ToString();
esc.Senha = objDataReader["senha"].ToString();
list.Add(esc);
}
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 idc = ((Label)listaUsuarios.Rows[i].FindControl("Lid")).Text;
Debug.WriteLine("RowDeleting chamado Indice da Linha: " + i + "CNPJ:" + idc);
BeanEscola u = new BeanEscola();
u.Cnpj = idc;
JavaScriptSerializer serializer = new JavaScriptSerializer();
String result = ws.escolaDel(serializer.Serialize(u));
if (result == "0")
{
Session["msg"] = "Escola não foi excluida!";
Response.Redirect("CadastroEscola.aspx");
}
else
{
Session["msg"] = "Escola excluída com sucesso!";
Response.Redirect("CadastroEscola.aspx");
}
}
public int deleteUsuario(BeanEscola u)
{
String sql = "DELETE FROM SchoolManager.dbo.cadescola WHERE cnpj = @pid";
SqlConnection conn;
int i = 0;
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@pid", u.Cnpj);
try
{
i = command.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("Falha ao deletar usuario: " + e);
}
conn.Close();
}
}
return(i);
}
