MySqlSedeDAO.java 7.99 KB
Newer Older
Felipe Escala Torres committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189
/*
 * 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 pe.siso.academia.MySqlDAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import pe.siso.academia.Beans.Sede;
import pe.siso.academia.DAO.SedeDAO;

/**
 *
 * @author sistem08user
 */
public class MySqlSedeDAO implements SedeDAO{
    @Override
    public ArrayList<Sede> listarSedes() throws Exception {
        ArrayList<Sede> lista = new ArrayList<>();
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "SELECT idlocal,deslocal,activo,abrev,asoc,codubige,nom_pro_ubi,nom_dep_ubi,nom_dis_ubi "
                + "FROM sede AS s INNER JOIN ubigeo AS u ON s.codubige=u.cod_ubi WHERE s.activo=1";
        ResultSet rs = st.executeQuery(query);
      //System.out.println("listar sede \n"+query);
        while(rs.next()){
            Sede sede = new Sede();
            sede.setIdLocal(rs.getInt("idlocal"));
            sede.setDesLocal(rs.getString("deslocal"));
            sede.setActivo(rs.getInt("activo"));
            sede.setAbrev(rs.getString("abrev"));
            sede.setAsoc(rs.getString("asoc"));
            sede.setCodUbigeo(rs.getInt("codubige"));
            sede.setProvincia(rs.getString("nom_pro_ubi"));
            sede.setDepartamento(rs.getString("nom_dep_ubi"));
            sede.setDistrito(rs.getString("nom_dis_ubi"));
            lista.add(sede);
        }
          cnx.close(); return lista;
    }
    
    @Override
    public ArrayList<Sede> listarSedesActivas() throws Exception {
        ArrayList<Sede> lista = new ArrayList<>();
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "SELECT idlocal,deslocal,activo,abrev,asoc,codubige,nom_pro_ubi,nom_dep_ubi,nom_dis_ubi "
                + "FROM sede AS s INNER JOIN ubigeo AS u ON s.codubige=u.cod_ubi WHERE activo = 1";
        ResultSet rs = st.executeQuery(query);
        
        while(rs.next()){
            Sede sede = new Sede();
            sede.setIdLocal(rs.getInt("idlocal"));
            sede.setDesLocal(rs.getString("deslocal"));
            sede.setActivo(rs.getInt("activo"));
            sede.setAbrev(rs.getString("abrev"));
            sede.setAsoc(rs.getString("asoc"));
            sede.setCodUbigeo(rs.getInt("codubige"));
            sede.setProvincia(rs.getString("nom_pro_ubi"));
            sede.setDepartamento(rs.getString("nom_dep_ubi"));
            sede.setDistrito(rs.getString("nom_dis_ubi"));
            lista.add(sede);
        }
          cnx.close(); return lista;
    }

    @Override
    public ArrayList<Sede> listarSedesPorNombre(String nombre, int inicio, int cantidad) throws Exception {
        ArrayList<Sede> lista = new ArrayList<>();
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "SELECT idlocal,deslocal,activo,abrev,asoc,codubige,nom_pro_ubi,nom_dep_ubi,nom_dis_ubi "
                + "FROM sede AS s INNER JOIN ubigeo AS u ON s.codubige=u.cod_ubi "
                + "WHERE deslocal like '%" + nombre + "%' ORDER BY idlocal LIMIT "+inicio+","+cantidad;
        //System.out.println(" ddddddddddddddddd \n "+query);
        ResultSet rs = st.executeQuery(query);
        
        
        
        while(rs.next()){
            Sede sede = new Sede();
            sede.setIdLocal(rs.getInt("idlocal"));
            sede.setDesLocal(rs.getString("deslocal"));
            sede.setActivo(rs.getInt("activo"));
            sede.setAbrev(rs.getString("abrev"));
            sede.setAsoc(rs.getString("asoc"));
            sede.setCodUbigeo(rs.getInt("codubige"));
            sede.setProvincia(rs.getString("nom_pro_ubi"));
            sede.setDepartamento(rs.getString("nom_dep_ubi"));
            sede.setDistrito(rs.getString("nom_dis_ubi"));
            lista.add(sede);
        }
          cnx.close(); return lista;
    }
    
    @Override
    public int cantidadSedesPorNombre(String nombre) throws Exception {
        int cantidad = 0;
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "SELECT COUNT(*) as cant"
                + " FROM sede AS s INNER JOIN ubigeo AS u ON s.codubige=u.cod_ubi "
                + " WHERE deslocal like '%" + nombre + "%'";
        ResultSet rs = st.executeQuery(query);
        
        while(rs.next()){
            cantidad = rs.getInt("cant");
        }
          cnx.close(); return cantidad;
    }

    @Override
    public int agregarSede(String nombre, String abrev, String asoc, int codUbigeo, String direccion, String telefono, String autorizacion, String ticketera, int codsede) throws Exception {
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "INSERT INTO sede (deslocal,abrev,asoc,codubige,direccion,telefono,autorizacion,maquina_regis,codigo_tk) "
                + "VALUES ('"+nombre+"','"+abrev+"','"+asoc+"','"+codUbigeo+"','"+direccion+"','"+telefono+"','"+autorizacion+"','"+ticketera+"','"+codsede+"')";
        int r = st.executeUpdate(query);
          cnx.close(); return r;
    }

    @Override
    public int editarSede(int id, String nombre, String abrev, String asoc, int codUbigeo) throws Exception {
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "UPDATE sede SET deslocal='"+nombre+"', abrev='"+abrev+"', "
                + "asoc='"+asoc+"', codubige='"+codUbigeo+"' WHERE idlocal="+id;
        int r = st.executeUpdate(query);
          cnx.close(); return r;
    }

    @Override
    public int cambiarEstado(int id, int estado) throws Exception {
        int cantidad = 0;
        int r = 0;
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "SELECT COUNT(*) AS cant FROM matricula AS m \n" +
            "INNER JOIN aula AS a ON m.codAula = a.codAula \n" +
            "INNER JOIN sede AS s ON a.codSede = s.idlocal\n" +
            "WHERE estado_matri = 1 AND idlocal = " + id;
        ResultSet rs = st.executeQuery(query);
        while(rs.next()){
            cantidad = rs.getInt("cant");
        }
        if(cantidad == 0){
            query = "UPDATE sede SET activo='"+estado+"' WHERE idlocal='"+id+"'";
            r = st.executeUpdate(query);
        }
        else{
            r = 2;
        }
          cnx.close(); return r;
    }

    @Override
    public Sede obtenerSede(int id) throws Exception {
        Sede objSede = new Sede();
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "SELECT idlocal,deslocal,activo,abrev,asoc,codubige,nom_pro_ubi,nom_dep_ubi,nom_dis_ubi "
                + "FROM sede AS s INNER JOIN ubigeo AS u ON s.codubige=u.cod_ubi WHERE idlocal="+id;
        ResultSet rs = st.executeQuery(query);
        
        while(rs.next()){
            objSede.setIdLocal(rs.getInt("idlocal"));
            objSede.setDesLocal(rs.getString("deslocal"));
            objSede.setActivo(rs.getInt("activo"));
            objSede.setAbrev(rs.getString("abrev"));
            objSede.setAsoc(rs.getString("asoc"));
            objSede.setCodUbigeo(rs.getInt("codubige"));
            objSede.setProvincia(rs.getString("nom_pro_ubi"));
            objSede.setDepartamento(rs.getString("nom_dep_ubi"));
            objSede.setDistrito(rs.getString("nom_dis_ubi"));
        }
          cnx.close(); return objSede;
    }
}