public static MateriaisUtils getMatsPed(int _ped)
{
MateriaisUtils mats = new MateriaisUtils();
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConn"].ToString()))
{
SqlCommand cmm = cnn.CreateCommand();
cmm.CommandText = "SELECT Materiais_Pedido.Cod_Material, Materiais.Descricao, Materiais_Pedido.Qtd "
+ "FROM Materiais_Pedido INNER JOIN Materiais ON "
+ "Materiais_Pedido.Cod_Material = Materiais.Cod_Material "
+ "WHERE (Materiais_Pedido.Num_Pedido = @numped) order by Materiais.Descricao ";
cmm.Parameters.Add("@numped", SqlDbType.Int).Value = _ped;
try
{
cnn.Open();
SqlDataReader dr = cmm.ExecuteReader();
MaterialUtil mat;
while (dr.Read())
{
mat = new MaterialUtil();
mat.Cod = dr.GetInt32(0);
mat.Descr = dr.GetString(1);
mat.Qtd = dr.GetInt32(2);
mat.Descr = mat.Descr + " - Qtd: " + mat.Qtd.ToString();
mats.Items.Add(mat.Cod, mat);
}
}
catch
{
}
}
return(mats);
}
public static SQLInsert gravaFechamentoPedido(int _numped, ExamesRealzs _exms, Profs _profs, MateriaisUtils _mats, int _status)
{
SQLInsert sqi = new SQLInsert();
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConn"].ToString()))
{
SqlCommand cmm = cnn.CreateCommand();
cnn.Open();
SqlTransaction st = cnn.BeginTransaction();
cmm.Transaction = st;
try
{
cmm.CommandText = "select cod_status from pedidos where num_pedido = @numped";
cmm.Parameters.Add("@numped", SqlDbType.Int).Value = _numped;
int status = (int)cmm.ExecuteScalar();
int ent_log = 4;
if (status != 3)
{
cmm.CommandText = "update pedidos set cod_status = 3 where num_pedido = @numped";
cmm.ExecuteNonQuery();
ent_log = 3;
}
cmm.CommandText = "DELETE FROM Exames_Pedido WHERE (Num_Pedido = @numped)";
cmm.ExecuteNonQuery();
if (_exms.Items.Count > 0)
{
cmm.CommandText = "INSERT INTO Exames_Pedido (Cod_Exame,Num_Pedido) VALUES (@exm,@numped)";
cmm.Parameters.Add("@exm", SqlDbType.Int);
foreach (ExameRealz exm in _exms.Items.Values)
{
cmm.Parameters["@exm"].Value = exm.Cod_exame;
cmm.ExecuteNonQuery();
}
}
cmm.CommandText = "DELETE FROM Profissionais_Pedido WHERE (Num_Pedido = @numped)";
cmm.ExecuteNonQuery();
if (_profs.Items.Count > 0)
{
cmm.CommandText = "INSERT INTO Profissionais_Pedido (Cod_Profissional, Num_Pedido) VALUES (@prof,@numped)";
cmm.Parameters.Add("@prof", SqlDbType.Int);
foreach (Prof pf in _profs.Items.Values)
{
cmm.Parameters["@prof"].Value = pf.Cod;
cmm.ExecuteNonQuery();
}
}
cmm.CommandText = "DELETE FROM Materiais_Pedido WHERE (Num_Pedido = @numped)";
cmm.ExecuteNonQuery();
if (_mats.Items.Count > 0)
{
cmm.CommandText = "INSERT INTO Materiais_Pedido (Cod_Material, Num_Pedido,Qtd) VALUES (@mat,@numped,@qtd)";
cmm.Parameters.Add("@mat", SqlDbType.Int);
cmm.Parameters.Add("@qtd", SqlDbType.Int);
foreach (MaterialUtil mt in _mats.Items.Values)
{
cmm.Parameters["@mat"].Value = mt.Cod;
cmm.Parameters["@qtd"].Value = mt.Qtd;
cmm.ExecuteNonQuery();
}
}
cmm.CommandText = "INSERT INTO Log_Pedido ([Num_Pedido],[TimeStamp],[Usuario],[Cod_Ent_Log]) "
+ "VALUES (@numped,getdate(),@usu,@entlog)";
cmm.Parameters.Add("@entlog", SqlDbType.Int).Value = ent_log;
cmm.Parameters.Add("@usu", SqlDbType.UniqueIdentifier).Value = (Guid)Membership.GetUser().ProviderUserKey;
cmm.ExecuteNonQuery();
st.Commit();
sqi.Success = true;
}
catch (Exception ex)
{
sqi.Success = false;
sqi.Erro = ex.Message;
try
{
st.Rollback();
}
catch (Exception ex1)
{
sqi.Erro += ex1.Message;
}
}
}
return(sqi);
}
