public void ObtenerMXU(DtoMoldura objmoldura, DtoMolduraXUsuario objmxu, DtoTipoMoldura tm)
{
SqlCommand command = new SqlCommand("SP_Detalle_MXU_C", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id", objmxu.PK_IMU_Cod);
DataSet ds = new DataSet();
conexion.Open();
SqlDataAdapter moldura = new SqlDataAdapter(command);
moldura.Fill(ds);
moldura.Dispose();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
objmxu.PK_IMU_Cod = Convert.ToInt32(reader[0].ToString());
objmoldura.PK_IM_Cod = Convert.ToInt32(reader[1].ToString());
tm.PK_ITM_Tipo = Convert.ToInt32(reader[2].ToString());
objmoldura.VM_Descripcion = reader[3].ToString();
tm.VTM_Nombre = reader[4].ToString();
objmoldura.DM_Largo = Convert.ToDouble(reader[5].ToString());
objmoldura.DM_Ancho = Convert.ToDouble(reader[6].ToString());
tm.VTM_UnidadMetrica = reader[7].ToString();
objmxu.IMU_Cantidad = Convert.ToInt32(reader[8].ToString());
objmxu.DMU_Precio = Convert.ToDouble(reader[9].ToString());
objmoldura.DM_Precio = Convert.ToDouble(reader[10].ToString());
}
conexion.Close();
conexion.Dispose();
}
public DataTable CalcularSubtotal(DtoMoldura objmoldura, DtoTipoMoldura objtipo, double cant)
{
DataTable dt = null;
conexion.Open();
SqlCommand command = new SqlCommand("SP_CalcularSubtotal", conexion);
command.Parameters.AddWithValue("@codMol", objmoldura.PK_IM_Cod);
command.Parameters.AddWithValue("@cantidad", cant);
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
command.CommandType = CommandType.StoredProcedure;
dt = new DataTable();
daAdaptador.Fill(dt);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
objmoldura.PK_IM_Cod = int.Parse(reader["PK_IM_Cod"].ToString());
objmoldura.VBM_Imagen = Encoding.ASCII.GetBytes(reader["VBM_Imagen"].ToString());
objtipo.VTM_Nombre = reader["VTM_Nombre"].ToString();
objmoldura.DM_Largo = Double.Parse(reader["DM_Largo"].ToString());
objmoldura.DM_Ancho = Double.Parse(reader["DM_Ancho"].ToString());
objmoldura.IM_Stock = int.Parse(reader["IM_Stock"].ToString());
objmoldura.DM_Precio = Convert.ToDouble(reader["DM_Precio"].ToString());
//objmoldura.DM_subtotal = Double.Parse(reader["Subtotal"].ToString());
}
conexion.Close();
return(dt);
}
public double Aprox(DtoMoldura objMoldura)
{
//SqlConnection con = new SqlConnection(@"data source=DESKTOP-4LVLNRM; initial catalog=BD_SCPEDR; integrated security=SSPI;");
//double aprox = 0;
SqlCommand cmd = new SqlCommand("select sum(DM_Precio)/ COUNT(*) as promedio from T_MOLDURA where FK_ITM_Tipo = " + objMoldura.FK_ITM_Tipo, conexion);
Console.WriteLine(cmd);
conexion.Open();
string aprox = cmd.ExecuteScalar().ToString();
//SqlDataReader reader = cmd.ExecuteReader();
//if (reader.Read())
//{
// aprox = double.Parse(reader[0].ToString());
//}
conexion.Close();
if (aprox == "")
{
return(0);
}
//return aprox;
return(Convert.ToDouble(aprox));
/**
* SqlCommand unComando = new SqlCommand("select DS_ImporteTotal from T_Solicitud where PK_IS_Cod =" + objsolicitud.FK_IS_Cod, conexion);
* conexion.Open();
* var ultimo = unComando.ExecuteScalar().ToString();
* conexion.Close();
* return Convert.ToDouble(ultimo);**/
}
public void ObtenerMoldura(DtoMoldura objmoldura, DtoTipoMoldura objtipo)
{
SqlCommand command = new SqlCommand("SP_Obtener_Moldura", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@codMol", objmoldura.PK_IM_Cod);
DataSet ds = new DataSet();
conexion.Open();
SqlDataAdapter moldura = new SqlDataAdapter(command);
moldura.Fill(ds);
moldura.Dispose();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
objmoldura.PK_IM_Cod = int.Parse(reader[0].ToString());
objmoldura.VM_Descripcion = reader[1].ToString();
objtipo.PK_ITM_Tipo = int.Parse(reader[2].ToString());
objtipo.VTM_Nombre = reader[3].ToString();
objmoldura.DM_Medida = Convert.ToDouble(reader[4].ToString());
objtipo.VTM_UnidadMetrica = reader[5].ToString();
objmoldura.IM_Estado = int.Parse(reader[6].ToString());
objmoldura.IM_Stock = int.Parse(reader[7].ToString());
objmoldura.DM_Precio = Convert.ToDouble(reader[8].ToString());
objmoldura.VBM_Imagen = Encoding.ASCII.GetBytes(reader[9].ToString());
}
conexion.Close();
conexion.Dispose();
}
public void InspeccionarMolduraByTipoMoldura(DtoTipoMoldura objtipo, DtoMoldura objmoldura)
{
SqlCommand command = new SqlCommand("SP_Inspeccionar_Moldura_by_TipoMoldura", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@idTipoMold", objtipo.PK_ITM_Tipo);
DataSet ds = new DataSet();
conexion.Open();
SqlDataAdapter tipomoldura = new SqlDataAdapter(command);
tipomoldura.Fill(ds);
tipomoldura.Dispose();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
objmoldura.VBM_Imagen = Encoding.ASCII.GetBytes(reader[0].ToString());
objmoldura.DM_Medida = Convert.ToDouble(reader[1].ToString());
objtipo.VTM_UnidadMetrica = reader[2].ToString();
objmoldura.DM_Precio = Convert.ToDouble(reader[3].ToString());
}
conexion.Close();
conexion.Dispose();
}
public void obtenerMoldura(DtoMoldura objdatousr)
{
SqlCommand command = new SqlCommand("SP_Obtener_Moldura", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@codMol", objdatousr.PK_IM_Cod);
DataSet ds = new DataSet();
conexion.Open();
SqlDataAdapter moldura = new SqlDataAdapter(command);
moldura.Fill(ds);
moldura.Dispose();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
objdatousr.DM_Medida = Convert.ToDouble(reader[0].ToString());
objdatousr.IM_Stock = int.Parse(reader[1].ToString());
objdatousr.DM_Precio = Convert.ToDouble(reader[2].ToString());
}
conexion.Close();
conexion.Dispose();
}
public void RegistrarMoldura(DtoMoldura objmoldura)
{
SqlCommand command = new SqlCommand("SP_Registrar_Moldura", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@descripcion", objmoldura.VM_Descripcion);
//command.Parameters.AddWithValue("@imagen", DBNull.Value);
var binary1 = command.Parameters.Add("@imagen", SqlDbType.VarBinary, -1);
binary1.Value = DBNull.Value;
command.Parameters.AddWithValue("@medida", objmoldura.DM_Medida);
command.Parameters.AddWithValue("@stock", objmoldura.IM_Stock);
command.Parameters.AddWithValue("@precio", objmoldura.DM_Precio);
command.Parameters.AddWithValue("@estado", objmoldura.IM_Estado);
command.Parameters.AddWithValue("@idtipom", objmoldura.FK_ITM_Tipo);
command.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;
conexion.Open();
//command.ExecuteNonQuery();
//SqlParameter retValue = new SqlParameter("@NewId", SqlDbType.Int).Direction=Par;
//retValue.Direction = ParameterDirection.ReturnValue;
//command.Parameters.Add(retValue);
using (SqlDataReader dr = command.ExecuteReader())
{
objmoldura.PK_IM_Cod = Convert.ToInt32(command.Parameters["@NewId"].Value);
}
conexion.Close();
}
public void ObtenerImgMoldura(DtoMoldura objmoldura)
{
SqlCommand command = new SqlCommand("SP_GetImageById", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id", objmoldura.PK_IM_Cod);
DataSet ds = new DataSet();
conexion.Open();
SqlDataAdapter moldura = new SqlDataAdapter(command);
moldura.Fill(ds);
moldura.Dispose();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
objmoldura.VBM_Imagen = Encoding.ASCII.GetBytes(reader[1].ToString());
objmoldura.VM_Descripcion = reader[1].ToString();
objmoldura.IM_Stock = int.Parse(reader[2].ToString());
}
conexion.Close();
conexion.Dispose();
}
public int StockMoldura(DtoMoldura objMoldura)
{
try
{
SqlConnection con = new SqlConnection("data source=DESKTOP-IAELG6V\\SQLEXPRESS; initial catalog=BD_SCPEDR; integrated security=SSPI;");
int valor_retornado = 0;
SqlCommand cmd = new SqlCommand("SELECT IM_Stock FROM T_Moldura WHERE PK_IM_Cod=" + objMoldura.PK_IM_Cod, con);
Console.WriteLine(cmd);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
valor_retornado = int.Parse(reader[0].ToString());
}
con.Close();
return(valor_retornado);
}
catch (Exception)
{
throw;
}
}
public DataTable ObtenerMoldura2(DtoMoldura objmoldura, DtoTipoMoldura objtipo)
{
DataTable dt = null;
conexion.Open();
SqlCommand command = new SqlCommand("SP_Obtener_Moldura2", conexion);
command.Parameters.AddWithValue("@codMol", objmoldura.PK_IM_Cod);
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
command.CommandType = CommandType.StoredProcedure;
dt = new DataTable();
daAdaptador.Fill(dt);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
objmoldura.PK_IM_Cod = int.Parse(reader["PK_IM_Cod"].ToString());
objmoldura.VBM_Imagen = Encoding.ASCII.GetBytes(reader["VBM_Imagen"].ToString());
objtipo.VTM_Nombre = reader["VTM_Nombre"].ToString();
objtipo.VTM_UnidadMetrica = reader["MedidaUM"].ToString();
objmoldura.IM_Stock = int.Parse(reader["IM_Stock"].ToString());
objmoldura.DM_Precio = Convert.ToDouble(reader["DM_Precio"].ToString());
}
conexion.Close();
return(dt);
}
public void PrecioAprox(DtoMoldura objMoldura)
{
SqlCommand command = new SqlCommand("SP_PrecioAprox", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@idtipomol", objMoldura.FK_ITM_Tipo);
conexion.Open();
command.ExecuteNonQuery();
conexion.Close();
}
public void ActualizarStockxMoldura(DtoMoldura objmoldura)
{
SqlCommand command = new SqlCommand("SP_Actualizar_Stock_Moldura", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@idMol", objmoldura.PK_IM_Cod);
command.Parameters.AddWithValue("@stock", objmoldura.IM_Stock);
conexion.Open();
command.ExecuteNonQuery();
conexion.Close();
}
public DataTable SelectMoldurasVista(DtoMoldura objMoldura)
{
DataTable dtmolduras = null;
conexion.Open();
SqlCommand command = new SqlCommand("Select * from Vista_Molduras_consulta where Codigo=" + objMoldura.PK_IM_Cod, conexion);
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
dtmolduras = new DataTable();
daAdaptador.Fill(dtmolduras);
conexion.Close();
return(dtmolduras);
}
public DataTable ListarTodoMolduras(DtoMoldura objmoldura)
{
DataTable dtmolduras = null;
conexion.Open();
SqlCommand command = new SqlCommand("SP_Listar_Todo_Moldura", conexion);
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
command.CommandType = CommandType.StoredProcedure;
dtmolduras = new DataTable();
daAdaptador.Fill(dtmolduras);
conexion.Close();
return(dtmolduras);
}
public double PrecioAprox(DtoMoldura objMoldura)
{
SqlCommand cmd = new SqlCommand("select sum(DM_Precio)/ COUNT(*) as promedio from T_Moldura where FK_ITM_Tipo= " + objMoldura.FK_ITM_Tipo, conexion);
Console.WriteLine(cmd);
conexion.Open();
string aprox = cmd.ExecuteScalar().ToString();
conexion.Close();
if (aprox == "")
{
return(0);
}
return(Convert.ToDouble(aprox));
}
public void ActualizarMoldura(DtoMoldura objmoldura)
{
SqlCommand command = new SqlCommand("SP_Actualizar_Moldura", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@idMol", objmoldura.PK_IM_Cod);
command.Parameters.AddWithValue("@descripcion", objmoldura.VM_Descripcion);
command.Parameters.AddWithValue("@medida", objmoldura.DM_Medida);
command.Parameters.AddWithValue("@stock", objmoldura.IM_Stock);
command.Parameters.AddWithValue("@precio", objmoldura.DM_Precio);
command.Parameters.AddWithValue("@estado", objmoldura.IM_Estado);
command.Parameters.AddWithValue("@idtipom", objmoldura.FK_ITM_Tipo);
conexion.Open();
command.ExecuteNonQuery();
conexion.Close();
}
public void InsertMoldura(DtoMoldura objmoldura)
{
SqlCommand command = new SqlCommand("SP_Registrar_Moldura", conexion);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@descripcion", objmoldura.VM_Descripcion);
command.Parameters.AddWithValue("@stock", objmoldura.IM_Stock);
command.Parameters.AddWithValue("@largo", objmoldura.DM_Largo);
command.Parameters.AddWithValue("@ancho", objmoldura.DM_Ancho);
command.Parameters.AddWithValue("@precio", objmoldura.DM_Precio);
command.Parameters.AddWithValue("@estado", objmoldura.IM_Estado);
command.Parameters.AddWithValue("@idtipom", objmoldura.FK_ITM_Tipo);
command.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;
conexion.Open();
using (SqlDataReader dr = command.ExecuteReader())
{
objmoldura.PK_IM_Cod = Convert.ToInt32(command.Parameters["@NewId"].Value);
}
conexion.Close();
}
public int StockMoldura(DtoMoldura objMoldura)
{
try
{
string select = "SELECT IM_Stock FROM T_MOLDURA WHERE PK_IM_Cod=" + objMoldura.PK_IM_Cod;
int valor_retornado = 0;
SqlCommand command = new SqlCommand(select, conexion);
conexion.Open();
command.ExecuteNonQuery();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
valor_retornado = int.Parse(reader[0].ToString());
}
conexion.Close();
return(valor_retornado);
}
catch (Exception)
{
throw;
}
}
public bool SelectMolduraID(DtoMoldura objmoldura)
{
string Select = "SELECT * from T_Moldura where PK_IM_Cod =" + objmoldura.PK_IM_Cod;
SqlCommand unComando = new SqlCommand(Select, conexion);
conexion.Open();
SqlDataReader reader = unComando.ExecuteReader();
bool hayRegistros = reader.Read();
if (hayRegistros)
{
objmoldura.PK_IM_Cod = int.Parse(reader[0].ToString());
objmoldura.VM_Descripcion = reader[1].ToString();
objmoldura.VBM_Imagen = Encoding.ASCII.GetBytes(reader[2].ToString());
objmoldura.IM_Stock = int.Parse(reader[3].ToString());
objmoldura.DM_Medida = Convert.ToDouble(reader[4].ToString());
objmoldura.DM_Precio = Convert.ToDouble(reader[5].ToString());
}
conexion.Close();
return(hayRegistros);
}
public double Aprox(DtoMoldura objMoldura)
{
return(objDaoMoldura.Aprox(objMoldura));
}
public void PrecioAprox(DtoMoldura objMoldura)
{
objDaoMoldura.PrecioAprox(objMoldura);
}
public int StockMoldura_(DtoMoldura objDtoMoldura)
{
return(objDaoMoldura.StockMoldura(objDtoMoldura));
}
public DataTable ListarTodoMoldura(DtoMoldura objDtoMoldura)
{
return(objDaoMoldura.ListarTodoMolduras(objDtoMoldura));
}
public void ActualizarImgMoldura(DtoMoldura objmoldura)
{
objDaoMoldura.ActualizarImgMoldura(objmoldura);
}
public void ActualizarRegistroMoldura(DtoMoldura objmoldura)
{
objDaoMoldura.ActualizarMoldura(objmoldura);
}
public DataTable ObtenerMoldura2(DtoMoldura objmoldura, DtoTipoMoldura objtipo)
{
return(objDaoMoldura.ObtenerMoldura2(objmoldura, objtipo));
}
public DataTable CalcularSubtotal(DtoMoldura objmoldura, DtoTipoMoldura objtipo, double cant)
{
return(objDaoMoldura.CalcularSubtotal(objmoldura, objtipo, cant));
}
public void ObtenerMoldura(DtoMoldura objmoldura, DtoTipoMoldura objtipo)
{
objDaoMoldura.ObtenerMoldura(objmoldura, objtipo);
}
public void ObtenerImagen_Desc_Moldura(DtoMoldura objDtoMoldura)
{
objDaoMoldura.ObtenerImgMoldura(objDtoMoldura);
}
public void registrarNuevaMoldura(DtoMoldura objDtoMoldura)
{
objDaoMoldura.RegistrarMoldura(objDtoMoldura);
}