public JsonResult CadastroLimiteControle()
{
try
{
var model = CalcularLimiteControle();
var l = new LimiteControle();
l.DataCalculo = DateTime.Now;
l.LSC = model.LimiteControle.LSC;
l.LC = model.LimiteControle.LC;
l.LIC = model.LimiteControle.LIC;
l.Usuario = (Usuario)Session["usuario"];
l.Ativo = true;
l.Lotes = new List <Lote>();
foreach (var item in model.ConsultaLoteAmostra)
{
l.Lotes.Add(new Lote()
{
IdLote = item.IdLote
});
}
new LimiteDAL().CadastrarLimiteControle(l);
return(Json(true));
}
catch (Exception e)
{
return(Json(e));
}
}
public void CadastrarLimiteControle(LimiteControle l)
{
try
{
AbrirConexao();
tr = con.BeginTransaction();
new SqlCommand("update LimiteControle set ativo = 0", con, tr).ExecuteNonQuery();
string query = "insert into LimiteControle (dataCalculo,LSC,LC,LIC,idUsuarioAprovacao,ativo,tipoCarta) values (@dataCalculo,@LSC,@LC,@LIC,@idUsuarioAprovacao,@ativo,@tipoCarta); " +
"SELECT SCOPE_IDENTITY()";
cmd = new SqlCommand(query, con, tr);
cmd.Parameters.AddWithValue("@dataCalculo", l.DataCalculo);
cmd.Parameters.AddWithValue("@LSC", l.LSC);
cmd.Parameters.AddWithValue("@LC", l.LC);
cmd.Parameters.AddWithValue("@LIC", l.LIC);
cmd.Parameters.AddWithValue("@idUsuarioAprovacao", l.Usuario.IdUsuario);
cmd.Parameters.AddWithValue("@ativo", l.Ativo);
cmd.Parameters.AddWithValue("@tipoCarta", l.TipoCarta);
l.IdLimite = Convert.ToInt32(cmd.ExecuteScalar());
foreach (var item in l.Lotes)
{
query = "update Lote set idLimite = @idLimite where idLote = @idLote";
cmd = new SqlCommand(query, con, tr);
cmd.Parameters.AddWithValue("@idLimite", l.IdLimite);
cmd.Parameters.AddWithValue("@idLote", item.IdLote);
cmd.ExecuteNonQuery();
}
new SqlCommand("delete from TempLote", con, tr).ExecuteNonQuery();
tr.Commit();
}
catch (Exception e)
{
tr.Rollback();
throw e;
}
finally
{
FecharConexao();
}
}
public List <LimiteControle> ConsultarLimiteControle(bool?ativo = null, int?idLimite = null)
{
try
{
AbrirConexao();
var lista = new List <LimiteControle>();
string query = "select lc.idLimite, lc.dataCalculo, lc.LSC, lc.LC, lc.LIC, u.nome, lc.ativo from LimiteControle lc " +
"inner join Usuario u on lc.idUsuarioAprovacao = u.idUsuario " +
"where (lc.ativo = @ativo or @ativo is null) and (lc.idLimite = @idLimite or @idLimite is null)";
cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithNullValue("@ativo", ativo);
cmd.Parameters.AddWithNullValue("@idLimite", idLimite);
dr = cmd.ExecuteReader();
while (dr.Read())
{
var l = new LimiteControle();
l.Usuario = new Usuario();
l.IdLimite = (int)dr["idLimite"];
l.DataCalculo = (DateTime)dr["dataCalculo"];
l.LSC = (decimal)dr["LSC"];
l.LC = (decimal)dr["LC"];
l.LIC = (decimal)dr["LIC"];
l.Usuario.Nome = dr["nome"].ToString();
l.Ativo = (bool)dr["ativo"];
lista.Add(l);
}
return(lista);
}
catch (Exception e)
{
throw e;
}
finally
{
FecharConexao();
}
}
