/* 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 {
        try {
        ConjuntTuples ct = new ConjuntTuples();
        String modul = params.consulta(1);
        String superficie = params.consulta(2);
        Statement stat = c.createStatement();
        int numModificats = stat.executeUpdate("delete *  from despatxos d where d.modul = '"+ modul + "'"
                                                        + "and d.superficie < '" + superficie +"'");
        if (numModificats == 0) throw new BDException(12);

        PreparedStatement ps = c.prepareStatement("select sum(d.superficie) as Sup "+
                "from despatxos");
        ResultSet res = null;
        res = ps.executeQuery();
        Tuple fila = new Tuple();
        fila.afegir(res.getString(1));
        ct.afegir(fila);
        ResultSet res1 = null;
        Statement s = c.createStatement();
        res1 = s.executeQuery("select count(*), a.dni "
                + "from assignacions a"
                + "where a.instantFi NOT NULL"
                + "group by a.dni");
        while (res1.next()) {
            Tuple fila1 = new Tuple();
            fila1.afegir(res1.getString(1));
            fila1.afegir(res1.getString(2));
            ct.afegir(fila1);
        }
        return ct;
        }
        catch(SQLException se) {
            if (se.getSQLState().equals("23503")) {
                throw new BDException(13);
            }else{
                throw new BDException(14);
            }
        }
    }
}