public List <Model.Disciplina> Get(Model.Professor p)
{
try
{
var list = new List <Model.Disciplina>();
var sb = new StringBuilder();
sb.Append($"SELECT DIS.* ");
sb.Append($"FROM Disciplinas AS DIS ");
sb.Append($"INNER JOIN ProfDisciplinas AS PRD ");
sb.Append($"ON DIS.Id = PRD.IdDisciplina ");
sb.Append($"INNER JOIN Professores AS PRF ");
sb.Append($"ON PRD.IdProf = PRF.Id ");
sb.Append($"WHERE PRD.IdProf = {p.Id}");
using (var cmd = new SQLiteCommand(sb.ToString(), conn))
{
using (var da = new SQLiteDataAdapter(cmd))
{ Fill(list, da); }
}
return(list);
}
catch (Exception ex)
{ throw ex; }
finally { conn.Close(); }
}
public void Delete(Model.Professor oProf)
{
if (oProf.Codigo > 0)
{
dalProf.Delete(oProf);
}
}
private void Fill(List <Model.Professor> list, DataTable dt)
{
foreach (DataRow dr in dt.Rows)
{
var p = new Model.Professor(this, new Faixa(), new Nivel(), new Situacao(), new Categoria(), new Disciplina());
p.Id = Convert.ToInt32(dr["id"]);
p.Nome = Convert.ToString(dr["Nome"]);
p.RG = Convert.ToString(dr["RG"]);
p.Faixa.Id = Convert.ToInt32(dr["idFaixa"]);
p.Faixa.NFaixa = Convert.ToString(dr["NFaixa"]);
p.Faixa.Descricao = dr["DescricaoFaixa"] == DBNull.Value ? string.Empty : Convert.ToString(dr["DescricaoFaixa"]);
p.Nivel.Id = Convert.ToInt32(dr["IdNivel"]);
p.Nivel.Nome = Convert.ToString(dr["NomeNivel"]);
p.Nivel.Descricao = dr["DescricaoNivel"] == DBNull.Value ? string.Empty : Convert.ToString(dr["DescricaoNivel"]);
p.Situacao.Id = Convert.ToInt32(dr["IdSituacao"]);
p.Situacao.Nome = Convert.ToString(dr["NomeSituacao"]);
p.Situacao.Descricao = dr["DescricaoSituacao"] == DBNull.Value ? string.Empty : Convert.ToString(dr["DescricaoSituacao"]);
p.Categoria.Id = Convert.ToInt32(dr["IdCategoria"]);
p.Categoria.Nome = Convert.ToString(dr["NomeCategoria"]);
p.Categoria.Descricao = dr["DescricaoCategoria"] == DBNull.Value ? string.Empty : Convert.ToString(dr["DescricaoCategoria"]);
list.Add(p);
}
}
public void Update(Model.Professor oProf)
{
if (oProf.Nome != "")
{
dalProf.Update(oProf);
}
}
public void Insert(Model.Professor oProf)
{
if (oProf.Nome == "")
{
throw new Exception("Campo Obrigatório!");
}
dalProf.Insert(oProf);
}
private object GetTSql(Model.Professor p, Model.Disciplina d, DayOfWeek dw)
{
var sb = new StringBuilder();
sb.Append($"SELECT TUR.Id, TUR.Nome,");
sb.Append($" TUR.Descricao ");
sb.Append($"FROM Turmas as TUR ");
sb.Append($"INNER JOIN Calendário as CAL ");
sb.Append($"ON TUR.id = CAL.idTurma ");
sb.Append($"WHERE CAL.idprofessor = {p.Id} ");
sb.Append($"AND CAL.iddisciplina = {d.Id} ");
sb.Append($"AND CAL.diadasemana = {(int)dw} ");
return(sb);
}
public void Delete(Model.Professor oProfessor)
{
SqlConnection con = new SqlConnection(Banco.stringDeConexao);
string sql = "DELETE Professor WHERE Codigo = @Codigo";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@Codigo", oProfessor.Codigo);
con.Open();
try
{
cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
}
public List <Model.Turma> Get(Model.Professor p, Model.Disciplina d, DayOfWeek dw)
{
try
{
var list = new List <Model.Turma>();
using (var cmd = new SQLiteCommand(GetTSql(p, d, dw).ToString(), conn))
{
using (var da = new SQLiteDataAdapter(cmd))
{ Fill(list, da); }
}
return(list);
}
catch (Exception ex)
{ throw ex; }
finally { conn.Close(); }
}
public void Update(Model.Professor oProfessor)
{
SqlConnection con = new SqlConnection(Banco.stringDeConexao);
string sql = "UPDATE Professor SET Cpf= @Cpf ,Telefone=@Telefone,DataCadastro=@DataCadastro WHERE Nome = @Nome";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@Codigo", oProfessor.Codigo);
cmd.Parameters.AddWithValue("@Nome", oProfessor.Nome);
cmd.Parameters.AddWithValue("@Cpf", oProfessor.Cpf);
cmd.Parameters.AddWithValue("@Telefone", oProfessor.Telefone);
cmd.Parameters.AddWithValue("@DataCadastro", oProfessor.DataCadastro);
con.Open();
try
{
cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
}
public void Insert(Model.Professor oProfessor)
{
SqlConnection con = new SqlConnection(Banco.stringDeConexao);
string sql = "INSERT INTO Professor (DataCadastro,Nome, Cpf,Telefone) VALUES (@DataCadastro,@Nome, @Cpf,@Telefone)";
SqlCommand cmd = new SqlCommand(sql, con);
//cmd.Parameters.AddWithValue("@Codigo",oProfessor.Codigo);
cmd.Parameters.AddWithValue("@Nome", oProfessor.Nome);
cmd.Parameters.AddWithValue("@Cpf", oProfessor.Cpf);
cmd.Parameters.AddWithValue("@Telefone", oProfessor.Telefone);
cmd.Parameters.AddWithValue("@DataCadastro", oProfessor.DataCadastro);
con.Open();
try
{
cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
}