/src/modelo/MdlArticulo.java

/*

 * 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;

    }

}