//-------ESCRITURA---------------
public void saveItems(ItemCarrito item, string codigoFactura)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "insert into itemsxfactura values(@ArtCode, @CodigoFactura, @price, @cant) ";
command.Parameters.AddWithValue("@ArtCode", item.code);
command.Parameters.AddWithValue("@CodigoFactura", codigoFactura);
command.Parameters.AddWithValue("@price", (decimal)item.unitPrice);
command.Parameters.AddWithValue("@cant", item.ammount);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
public void addFav(string prodcode, string usercode)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "insert into favoritosxusuario values (@user, @art)";
command.Parameters.AddWithValue("@user", usercode);
command.Parameters.AddWithValue("@art", prodcode);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
public void creditTransaction(float monto, string userCode)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "update usuarios set Credito = Credito + @value where Codigo = @userCode";
command.Parameters.AddWithValue("@value", monto);
command.Parameters.AddWithValue("@userCode", userCode);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
//--------------LECTURA--------------
public int checkMail(string mail)
{
int x = 0;
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select Count(*) from usuarios where Correo = '" + mail + "'";
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
while (lector.Read())
{
x = lector.GetInt32(0);
}
connection.Close();
return(x);
}
catch (Exception)
{
throw;
}
}
//---------ESCRITURA-------------
public void nuevoUsuario(Usuario usuario)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "exec SP_AltaUsuario @Codigo, @Nombre, @Apellido, @DNI, @Correo, @Password, @Dir, @Zona, 0 ";
command.Parameters.AddWithValue("@Codigo", usuario.code);
command.Parameters.AddWithValue("@Nombre", usuario.name);
command.Parameters.AddWithValue("@Apellido", usuario.apellido);
command.Parameters.AddWithValue("@DNI", usuario.dni);
command.Parameters.AddWithValue("@Correo", usuario.mail);
command.Parameters.AddWithValue("@Password", usuario.pass);
command.Parameters.AddWithValue("@Dir", usuario.direccion);
command.Parameters.AddWithValue("@Zona", usuario.zona.id);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
public void delete(string code)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "update articulos set Active = 0 where Codigo = @code ";
command.Parameters.AddWithValue("@code", code);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
removeFromFavorites(code);
}
catch (Exception)
{
throw;
}
finally
{
}
}
//------------GENERACION DE CODIGO-----------------
public bool checkcode(string code)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
bool found = false;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select * from vw_articulos where Codigo ='" + code + "'";
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
while (lector.Read())
{
found = true;
}
connection.Close();
return(found);
}
catch (Exception)
{
throw;
}
}
//-----------LECTURA-------------
public List <Zona> listar()
{
GestorConexion gestor = new GestorConexion();
List <Zona> aux = new List <Zona>();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select * from zonas";
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
while (lector.Read())
{
Zona x = new Zona();
x.id = lector.GetInt32(0);
x.name = lector.GetString(1);
aux.Add(x);
}
connection.Close();
return(aux);
}
catch (Exception)
{
throw;
}
}
public void updatestock(string productCode, int ammount, decimal price)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "UPDATE stock set Cantidad = @cant, PrecioDistribuidor = @price where CodigoArticulo = @code";
command.Parameters.AddWithValue("@cant", ammount);
command.Parameters.AddWithValue("@price", price);
command.Parameters.AddWithValue("@code", productCode);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
// ---------------VALIDACION--------------------
private bool checkFactura(Factura factura)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
int x;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select Count(*) from facturas where Codigo = @code and CodigoUsuario = @user";
command.Parameters.AddWithValue("@code", factura.codigo);
command.Parameters.AddWithValue("@user", factura.codigoUsuario);
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
lector.Read();
x = lector.GetInt32(0);
connection.Close();
if (x != 0)
{
return(true);
}
return(false);
}
catch (Exception)
{
throw;
}
}
public Marca buscarnombre(string name)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select * from vw_marcas where Nombre = '" + name + "'";
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
Marca x = new Marca();
while (lector.Read())
{
x.id = lector.GetInt32(0);
x.name = lector.GetString(1);
x.productAmmount = lector.GetInt32(2);
}
connection.Close();
return(x);
}
catch (Exception)
{
throw;
}
}
private void deleteItem(string itemCode, string facturaCode)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "delete itemsxfactura where CodigoFactura = @fac and CodigoArticulo = @art";
command.Parameters.AddWithValue("@fac", facturaCode);
command.Parameters.AddWithValue("@art", itemCode);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
public void changePrice(Factura factura)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "update facturas set Monto = @monto where Codigo = @code";
command.Parameters.AddWithValue("@code", factura.codigo);
command.Parameters.AddWithValue("@monto", (decimal)factura.totalPrice());
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
public void SaveFactura(Factura factura)
{
if (factura.codigo == null)
{
factura.codigo = generateCode();
}
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "exec SP_CargaFactura @code, @userCode, @date , @status, @payment, @value, @dir, @paid, @deliveryst";
command.Parameters.AddWithValue("@code", factura.codigo);
command.Parameters.AddWithValue("@userCode", factura.codigoUsuario);
command.Parameters.AddWithValue("@status", factura.estado);
command.Parameters.AddWithValue("@payment", factura.modoDePago);
command.Parameters.AddWithValue("@value", (decimal)factura.monto);
command.Parameters.AddWithValue("@date", factura.fecha);
command.Parameters.AddWithValue("@dir", factura.dir);
command.Parameters.AddWithValue("@paid", factura.pago);
command.Parameters.AddWithValue("@deliveryst", factura.estadoEntrega);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception)
{
connection.Close();
throw;
}
}
public Categoria buscarid(int id)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select * from vw_categorias where Id = '" + id + "'";
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
Categoria x = new Categoria();
while (lector.Read())
{
x.id = lector.GetInt32(0);
x.name = lector.GetString(1);
x.productAmmount = lector.GetInt32(2);
}
connection.Close();
return(x);
}
catch (Exception)
{
throw;
}
}
public int checkStock(string code)
{
GestorConexion gestor = new GestorConexion();
int x;
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select Cantidad from stock where CodigoArticulo = @code";
command.Parameters.AddWithValue("@code", code);
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
lector.Read();
x = lector.GetInt32(0);
connection.Close();
return(x);
}
catch (Exception)
{
throw;
}
}
public void create(Producto producto)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "insert into ARTICULOS values(@cod, @nombre, @desc, @marca, @imagen, @margen, 1)";
command.Parameters.AddWithValue("@cod", producto.code);
command.Parameters.AddWithValue("@nombre", producto.name);
command.Parameters.AddWithValue("@desc", producto.desc);
command.Parameters.AddWithValue("@marca", producto.marca.id);
command.Parameters.AddWithValue("@imagen", producto.urlimagen);
command.Parameters.AddWithValue("@margen", producto.margin);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
public Categoria getFromName(string name)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select * from vw_categorias where Nombre = @name";
command.Parameters.AddWithValue("@name", name);
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
Categoria x = new Categoria();
while (lector.Read())
{
x.id = lector.GetInt32(0);
x.name = lector.GetString(1);
x.productAmmount = lector.GetInt32(2);
}
connection.Close();
return(x);
}
catch (Exception)
{
throw;
}
}
public void mod(Producto producto)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "update ARTICULOS set Nombre = @nombre, Descripcion = @desc, IdMarca = @marca, ImagenUrl = @imagen, MargenGanancia = @margen, Active = 1 where Codigo = @cod ";
command.Parameters.AddWithValue("@cod", producto.code);
command.Parameters.AddWithValue("@nombre", producto.name);
command.Parameters.AddWithValue("@desc", producto.desc);
command.Parameters.AddWithValue("@marca", producto.marca.id);
command.Parameters.AddWithValue("@imagen", producto.urlimagen);
command.Parameters.AddWithValue("@margen", producto.margin);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
public bool checkItemInCategory(int catid, string prodid)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
bool result = true;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select count(*) from categoriaxarticulo where CodigoArticulo = @prod and Idcategoria = @cat";
command.Parameters.AddWithValue("@prod", prodid);
command.Parameters.AddWithValue("@cat", catid);
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
Categoria x = new Categoria();
lector.Read();
if (lector.GetInt32(0) == 0)
{
result = false;
}
connection.Close();
return(result);
}
catch (Exception)
{
throw;
}
}
public List <string> favoritos(string usercode)
{
List <string> aux = new List <string>();
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select * from vw_favoritos where Usuario = @user ";
command.Parameters.AddWithValue("@user", usercode);
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
while (lector.Read())
{
string x = lector.GetString(1);
aux.Add(x);
}
connection.Close();
return(aux);
}
catch (Exception)
{
throw;
}
}
//------------ESCRITURA------------------
public void rename(int id, string newname)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "UPDATE CATEGORIAS set Nombre = @name where Id = @code";
command.Parameters.AddWithValue("@name", newname);
command.Parameters.AddWithValue("@code", id);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
//-------------CODIGOS------------------
private bool checkCode(string code)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
bool result = false;
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select Count(*) from usuarios where Codigo = @code";
command.Parameters.AddWithValue("@code", code);
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
lector.Read();
int x = lector.GetInt32(0);
if (x != 0)
{
result = true;
}
connection.Close();
return(result);
}
catch (Exception)
{
throw;
}
}
public void assignCategories(int catid, string prodid)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "insert into categoriaxarticulo values (@art, @cat)";
command.Parameters.AddWithValue("@art", prodid);
command.Parameters.AddWithValue("@cat", catid);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception)
{
throw;
}
finally
{
}
}
public void removeFav(string prodcode, string usercode)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "delete from favoritosxusuario where Usuario = @user and Articulo = @art";
command.Parameters.AddWithValue("@user", usercode);
command.Parameters.AddWithValue("@art", prodcode);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
public void delete(int id)
{
cleanProducts(id);
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "delete from categorias where Id = @code";
command.Parameters.AddWithValue("@code", id);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception)
{
throw;
}
finally
{
}
}
public void cambiarPass(string mail, string pass, string newpass)
{
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "update usuarios set Password = @newpass where Correo = @mail and Password = @oldpass";
command.Parameters.AddWithValue("@mail", mail);
command.Parameters.AddWithValue("@oldpass", pass);
command.Parameters.AddWithValue("@newpass", newpass);
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
public List <Categoria> buscar(string name)
{
GestorConexion gestor = new GestorConexion();
List <Categoria> aux = new List <Categoria>();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select * from vw_categorias where Nombre like '%" + name + "%'";
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
while (lector.Read())
{
Categoria x = new Categoria();
x.id = lector.GetInt32(0);
x.name = lector.GetString(1);
x.productAmmount = lector.GetInt32(2);
aux.Add(x);
}
connection.Close();
return(aux);
}
catch (Exception)
{
throw;
}
}
public int CheckAlta(string mail, string pass)
{
int x = 0;
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select Count(*) from usuarios where Correo = @correo and Password = @pass";
command.Parameters.AddWithValue("@correo", mail);
command.Parameters.AddWithValue("@pass", pass);
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
while (lector.Read())
{
x = lector.GetInt32(0);
}
connection.Close();
return(x);
}
catch (Exception)
{
throw;
}
}
public int contarPendientes()
{
int x;
GestorConexion gestor = new GestorConexion();
SqlConnection connection = gestor.connection();
SqlCommand command = new SqlCommand();
SqlDataReader lector;
try
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select * from vw_facturasPendientes";
command.Connection = connection;
connection.Open();
lector = command.ExecuteReader();
lector.Read();
x = lector.GetInt32(0);
return(x);
}
catch (Exception)
{
throw;
}
}