/*
 * 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.Ubigeo;
import pe.siso.academia.DAO.UbigeoDAO;

/**
 *
 * @author sistem08user
 */
public class MySqlUbigeoDAO implements UbigeoDAO{

    @Override
    public ArrayList<Ubigeo> listarDepartamentos() throws Exception {
        ArrayList<Ubigeo> lista = new ArrayList<>();
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "select cod_dep_ubi,nom_dep_ubi from ubigeo GROUP BY(cod_dep_ubi)";
        ResultSet rs = st.executeQuery(query);
        while(rs.next()){
            Ubigeo sede = new Ubigeo();
            sede.setNomDep(rs.getString("nom_dep_ubi"));
            sede.setCodDep(rs.getInt("cod_dep_ubi"));
            lista.add(sede);
        }
          cnx.close(); return lista;
    }

    @Override
    public ArrayList<Ubigeo> listarProvincias(int codDep) throws Exception {
        ArrayList<Ubigeo> lista = new ArrayList<>();
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "select cod_pro_ubi,nom_pro_ubi from ubigeo where cod_dep_ubi="+codDep+" GROUP BY(cod_pro_ubi)";
        ResultSet rs = st.executeQuery(query);
        
        while(rs.next()){
            Ubigeo sede = new Ubigeo();
            sede.setNomProv(rs.getString("nom_pro_ubi"));
            sede.setCodProv(rs.getInt("cod_pro_ubi"));
            lista.add(sede);
        }
          cnx.close(); return lista;
    }

    @Override
    public ArrayList<Ubigeo> listarDistritos(int codPro, int codDep) throws Exception {
        ArrayList<Ubigeo> lista = new ArrayList<>();
        String base = "academianew";
        Connection cnx = MySqlDAOFactory.obtenerConexion(base);
        Statement st = cnx.createStatement();
        String query = "select cod_ubi,nom_dis_ubi from ubigeo where cod_pro_ubi="+codPro+" and cod_dep_ubi="+codDep+" GROUP BY(cod_dis_ubi)";
        ResultSet rs = st.executeQuery(query);
        
        while(rs.next()){
            Ubigeo sede = new Ubigeo();
            sede.setNomDis(rs.getString("nom_dis_ubi"));
            sede.setCodUbigeo(rs.getInt("cod_ubi"));
            lista.add(sede);
        }
          cnx.close(); return lista;
    }
    
}