/* Imports de la classe */
import java.sql.*;

/* Capa de Control de Dades */
class CtrlDadesPublic extends CtrlDadesPrivat {

    public ConjuntTuples consulta(Connection c, Tuple params) throws BDException {
        ConjuntTuples ct = new ConjuntTuples();

        String modul = params.consulta(1);
        String superficie = params.consulta(2);

        try {
            Statement query = c.createStatement();
            int numBorrad = query.executeUpdate("delete from despatxos "+
                                                "where modul = '" + modul +
                                                "' and superficie < '" + superficie + "';");
            if(numBorrad == 0) throw new BDException(12);

            ResultSet rs = query.executeQuery("select sum(superficie) as sumSuperficies from despatxos");
            rs.next();
            int sum_sup = rs.getInt("sumSuperficies");
            if(rs.wasNull()) {
                Tuple fila = new Tuple();
                fila.afegir("NO");
                ct.afegir(fila);
            } else {
                Tuple fila = new Tuple();
                fila.afegir(Integer.toString(sum_sup));
                ct.afegir(fila);
            }

            rs = query.executeQuery("select p.nomProf as nomProf, count(*) as assigFin " +
                                    "from assignacions as a, professors as p " +
                                    "where a.dni = p.dni and a.instantfi is not null " +
                                    "group by p.dni;");

            while(rs.next()) {
                Tuple fila = new Tuple();
                fila.afegir(rs.getString("nomProf"));
                fila.afegir(Integer.toString(rs.getInt("assigFin")));
                ct.afegir(fila);
            }


        } catch (SQLException se) {
            //System.out.println(se.getSQLState() +" "+ se.getMessage());
            if (se.getSQLState().equals("23503")) throw new BDException(13);
            else throw new BDException(14);
        }

        return ct;
    }
}




//System.out.println(se.getSQLState() +" "+ se.getMessage());