/src/modelo/MdlArticulo.java
Java | 408 lines | 288 code | 27 blank | 93 comment | 10 complexity | fcdd0ad875073f7edca2cf9e4b7899f5 MD5 | raw file
- /*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
- package modelo;
-
- import clases.Articulo;
- import clases.Categoria;
-
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- /**
- *
- * @author Edder
- */
- public class MdlArticulo{
-
- /**
- *
- * @param articulo
- * @param cnx
- * @throws SQLException
- */
-
- public static void insert(Articulo articulo) throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String sql = "INSERT INTO articulo(nombre, presentacion, descripcion, "
- + "stockMinimo, tamanio, unidad, codigoBarras, idCategoria, "
- + "precioVenta, estado) "
- + "VALUES( '" + articulo.getNombre() + "', "
- + "'" + articulo.getPresentacion() + "', "
- + "'" + articulo.getDescripcion() + "', "
- + articulo.getStockMinimo() + ", "
- + "'" + articulo.getTamanio() + "', "
- + "'" + articulo.getUnidad() + "', "
- + "'" + articulo.getCodigoBarras() + "', "
- + articulo.getCategoria().getIdCategoria() + ", "
- + articulo.getPrecioVenta() + ", "
- + "'" + articulo.getEstado() + "')";
- Conexion.ejecutar(sql);
- }
-
- public static void fastInsert(Articulo articulo) throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String sql = "INSERT INTO articulo(nombre, idCategoria, precioVenta, estado) "
- + "VALUES( '" + articulo.getNombre() + "', "
- + articulo.getCategoria().getIdCategoria() + ", "
- + articulo.getPrecioVenta() + ", "
- + "'" + articulo.getEstado() + "')";
- Conexion.ejecutar(sql);
- }
-
- /**
- *
- * @param cnx
- * @return
- * @throws SQLException
- */
-
- public static int getLastId() throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String sql = "SELECT idArticulo FROM articulo ORDER BY idArticulo DESC LIMIT 1";
- ResultSet rs = Conexion.consultar(sql);
- int id = -1;
- if(rs.next()){
- id = rs.getInt(1);
- }
- return id;
- }
-
- /**
- *
- * @param articulo
- * @param cnx
- * @throws SQLException
- */
-
- public static void update(Articulo articulo) throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String sql = "UPDATE articulo SET "
- + "nombre = '" + articulo.getNombre() + "', "
- + "presentacion = '" + articulo.getPresentacion() + "', "
- + "descripcion = '" + articulo.getDescripcion() + "', "
- + "stockMinimo = " + articulo.getStockMinimo() + ", "
- + "tamanio = '" + articulo.getTamanio() + "', "
- + "unidad = '" + articulo.getUnidad() + "', "
- + "codigoBarras = '" + articulo.getCodigoBarras() + "', "
- + "idCategoria = " + articulo.getCategoria().getIdCategoria() + ", "
- + "precioVenta = " + articulo.getPrecioVenta() + ", "
- + "estado = '" + articulo.getEstado() + "' "
- + "WHERE idArticulo = " + articulo.getIdArticulo();
- System.out.println(sql);
- Conexion.ejecutar(sql);
- }
-
- /**
- * Cambia estado del art铆culo a 'I' : Inactivo
- * @param articulo
- * @param cnx
- * @throws SQLException
- */
- public static void dar_baja(Articulo articulo) throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String sql = "UPDATE articulo SET "
- + "estado = 'I' "
- + "WHERE idArticulo = " + articulo.getIdArticulo();
- Conexion.ejecutar(sql);
- }
-
- /**
- *
- * @param stock
- * @param idArticulo
- * @param idAlmacen
- * @param cnx
- * @throws SQLException
- */
- public static void updateStock(int stock, int idArticulo, int idAlmacen)
- throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String sql = "UPDATE almacenarticulo SET "
- + "stock += " + stock + " "
- + "WHERE idAlmacen = " + idAlmacen + " "
- + "AND idArticulo = " + idArticulo;
- Conexion.ejecutar(sql);
- }
-
- /**
- *
- * @param idArticulo
- * @param idAlmacen
- * @param cnx
- * @throws SQLException
- * @throws ClassNotFoundException
- */
- public static void asignarAlmacen(int idArticulo, int idAlmacen) throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String sql = "INSERT INTO almacenarticulo(idAlmacen, idArticulo, stock, stockMinimo) "
- + "VALUES(" + idAlmacen + ", " + idArticulo + ", 0, 0)";
- Conexion.ejecutar(sql);
- }
-
- /**
- * Devuelve una lista de articulos que coincidan con la busqueda a realizar
- * @param query
- * @param where
- * @param cnx
- * @return
- * @throws SQLException
- */
- public static ArrayList<Articulo> getArticulos(String query, String where)
- throws SQLException, ClassNotFoundException{
- String sql = "SELECT * FROM articulo "
- + "WHERE (UPPER(nombre) LIKE UPPER('%" + query + "%') "
- + "OR UPPER(descripcion) LIKE ('%" + query + "%') "
- + "OR codigobarras LIKE '" + query + "') "
- + "AND estado = 'A' "
- + ((where != null && !where.equals(""))? where : ";");
- return MdlArticulo.get(sql);
- }
-
- /**
- *
- * @param query
- * @param cnx
- * @return
- * @throws SQLException
- */
-
- public static ArrayList<Articulo> getArticulos() throws SQLException, ClassNotFoundException{
- String sql = "SELECT idArticulo, nombre FROM articulo;";
- return MdlArticulo.get(sql);
- }
-
- public static ArrayList<Articulo> getArticulos_almacen(int idAlmacen, String busqueda)
- throws SQLException, ClassNotFoundException{
- String sql = "SELECT a.idArticulo, a.nombre, a.presentacion, a.descripcion, a.stockMinimo, "
- + "a.tamanio, a.unidad, a.codigoBarras, a.idCategoria, a.precioVenta, a.urlImagen, a.estado "
- + "FROM articulo as a "
- + "INNER JOIN almacenarticulo as aa "
- + "ON a.idArticulo = aa.idArticulo "
- + "WHERE aa.idAlmacen = " + idAlmacen + " and "
- + "UPPER(a.nombre) LIKE ('%" + busqueda + "%');";
- return MdlArticulo.get(sql);
- }
-
- /**
- *
- * @param idCategoria
- * @param query
- * @param cnx
- * @return
- * @throws SQLException
- */
- public static ArrayList<Articulo> getArticulos_categoria(int idCategoria, String query)
- throws SQLException, ClassNotFoundException{
- String sql = "SELECT * FROM articulo "
- + "WHERE UPPER(nombre) LIKE UPPER('%" + query + "%') "
- + "OR UPPER(descripcion) LIKE UPPER('%" + query + "%') "
- + "AND idCategoria = " + idCategoria + "; ";
- return MdlArticulo.get(sql);
- }
-
- /**
- *
- * @param idArticulo
- * @param cnx
- * @return
- * @throws SQLException
- */
-
- public static Articulo get(int idArticulo) throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String sql = "SELECT * FROM articulo "
- + "WHERE idArticulo = " + idArticulo + "; ";
- ResultSet rs = Conexion.consultar(sql);
- Articulo articulo = null;
- if(rs.next()){
- articulo = new Articulo(rs.getInt(1),
- rs.getString(2),
- rs.getString(3),
- rs.getString(4),
- rs.getInt(5),
- rs.getString(6),
- rs.getString(7),
- rs.getString(8),
- new clases.Categoria(){{
- setIdCategoria(rs.getInt(9));
- }},
- rs.getFloat(10),
- rs.getString(11).charAt(0));
- }
- return articulo;
- }
-
- /**
- *Devuelve un arreglo de Object de 3 columnas (idArticulo, nombre, stock)
- * @param idAlmacen
- * @param query
- * @param cnx
- * @return ArrayList<Object[]>
- * @throws SQLException
- */
- public static ArrayList<Object[]> getArticulosStock_almacen(int idAlmacen, String query)
- throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String sql = "select a.idArticulo, CONCAT(a.nombre, ' ', "
- + "a.presentacion, ' ', a.tamanio, ' ', a.unidad) as nombre, aa.stock "
- + "from articulo as a "
- + "inner join almacenarticulo as aa "
- + "on a.idArticulo = aa.idArticulo "
- + "where (UPPER(a.nombre) LIKE UPPER('%" + query + "%') "
- + "OR UPPER(a.descripcion) LIKE UPPER('%" + query + "%') "
- + "OR a.codigobarras LIKE '" + query + "') "
- + "AND a.estado = 'A' AND aa.idAlmacen = " + idAlmacen;
- ResultSet rs = Conexion.consultar(sql);
- ArrayList<Object[]> articulosStock = new ArrayList<>();
- while(rs.next()){
- Object[] stock = new Object[]{
- rs.getInt("idArticulo"),
- rs.getString("nombre"),
- rs.getInt("stock")
- };
- articulosStock.add(stock);
- }
-
- return articulosStock;
- }
-
- /*
- * Devuelve informaciè´¸n para mostrar en la tabla de orden de compra.
- */
- public static ArrayList<Object[]> getArticulos_OrdenCompra(int idAlmacen, String query, boolean flag)
- throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- String where = "";
- if (flag) {
- where = "AND aa.stock = 0 ";
- }
- String sql = "select a.idArticulo, a.nombre, aa.stock, "
- + "(select sum(stock) from almacenarticulo where idArticulo = a.idArticulo) as total, "
- + "sum(case cantidad when null then 0 else cantidad end) as cantidad "
- + "from articulo as a "
- + "left join detalleordencompra as doc "
- + "on a.idArticulo = doc.idArticulo "
- + "right join almacenarticulo as aa "
- + "on a.idArticulo = aa.idArticulo "
- + "where (UPPER(a.nombre) LIKE UPPER('%" + query + "%') "
- + "OR UPPER(a.descripcion) LIKE UPPER('%" + query + "%')) "
- + "AND aa.idAlmacen = " + idAlmacen + " "
- + where
- + "group by a.idArticulo, a.nombre, a.stockMinimo, aa.stock "
- + "ORDER BY cantidad DESC, a.nombre ASC";
- ResultSet rs = Conexion.consultar(sql);
- ArrayList<Object[]> articulosStock = new ArrayList<>();
- while(rs.next()){
- Object[] stock = new Object[]{
- rs.getInt("idArticulo"),
- rs.getString("nombre"),
- rs.getInt("stock"),
- rs.getInt("total"),
- rs.getInt("cantidad")
- };
- articulosStock.add(stock);
- }
-
- return articulosStock;
- }
-
- public static ArrayList<Articulo> getCategoria(String query, ArrayList<Categoria> categorias, boolean inactivo) throws ClassNotFoundException, SQLException{
- ArrayList<Articulo> articulos;
- String where = "";
- if(categorias.size() > 0){
- where = " AND (";
- int i = 0;
- for(Categoria cat : categorias){
- if(i > 0){
- where += " OR ";
- }
- i++;
- where += "idCategoria = " + cat.getIdCategoria();
- }
- where += ")";
- }else
- where = "";
- where += inactivo ? "" : " AND estado NOT LIKE 'I'";
- String sql = "SELECT * FROM articulo where UPPER(nombre) like UPPER('%" + query + "%') "
- + where + " ORDER BY nombre asc";
- articulos = MdlArticulo.get(sql);
- return articulos;
- }
-
- public static ArrayList<Articulo> get(String sql) throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- ArrayList<Articulo> articulos = new ArrayList<>();
- ResultSet rs = Conexion.consultar(sql);
- while(rs.next()){
- articulos.add(new Articulo(rs.getInt(1),
- rs.getString(2),
- rs.getString(3),
- rs.getString(4),
- rs.getInt(5),
- rs.getString(6),
- rs.getString(7),
- rs.getString(8),
- new clases.Categoria(){{
- setIdCategoria(rs.getInt(9));
- }},
- rs.getFloat(10),
- rs.getString(11).charAt(0)));
- }
- return articulos;
- }
-
- /*
- * Metodo usado en anular movimiento para obtener el stock de articulos de un almacen
- */
- public static ArrayList<Object[]> articulosCantidad(int almacen) throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- ArrayList<Object[]> rows = new ArrayList<>();
- String sql = "SELECT idArticulo, stock "
- + "FROM almacenarticulo "
- + "WHERE idAlmacen = " + almacen;
- ResultSet rs = Conexion.consultar(sql);
- while(rs.next()){
- rows.add(new Object[]{
- rs.getInt("idArticulo"),
- rs.getInt("stock")
- });
- }
- return rows;
- }
-
- public static ArrayList<Object[]> getArticulos(int idAlmacen, Categoria categoria, String query)
- throws SQLException, ClassNotFoundException{
- Conexion.abrirTransaccion();
- ArrayList<Object[]> rows = new ArrayList<>();
- String sqlcategoria = "";
- if(categoria != null){
- sqlcategoria = "c.idCategoria = " + categoria.getIdCategoria() + " AND ";
- }
- String sql = "SELECT a.idArticulo, CONCAT(a.nombre, ' ', "
- + "a.presentacion, ' ', a.tamanio, ' ', a.unidad) as nombre, aa.stock, a.precioVenta as precio "
- + "FROM articulo as a "
- + "INNER JOIN almacenarticulo as aa "
- + "ON a.idArticulo = aa.idArticulo "
- + "INNER JOIN categoria as c "
- + "ON a.idCategoria = c.idCategoria "
- + "WHERE " + sqlcategoria
- + "aa.idAlmacen = " + idAlmacen + " "
- + "AND (a.codigoBarras LIKE '%" + query + "%' "
- + "OR UPPER(a.nombre) LIKE UPPER('%" + query + "%')) AND a.estado = 'A' "
- + "ORDER BY a.nombre asc, a.tamanio asc;";
- System.out.println(sql);
- ResultSet rs = Conexion.consultar(sql);
- while(rs.next()){
- rows.add(new Object[]{
- rs.getInt("idArticulo"),
- rs.getString("nombre"),
- rs.getInt("stock"),
- rs.getFloat("precio")
- });
- }
- return rows;
- }
- }