/* 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 s = c.createStatement();
			int ret = s.executeUpdate ("delete from despatxos "+
										"where modul = '" +modul+ "' and superficie < " +superficie+ ";");
			if (ret == 0) throw new BDException(12);

			ResultSet rs;
			rs = s.executeQuery ("select count(*), sum(superficie) from despatxos;");

			Tuple fila = new Tuple();
			rs.next();
			if (rs.getInt(1) == 0) fila.afegir("NO");
			else fila.afegir(rs.getString(2));
			ct.afegir(fila);

			rs = s.executeQuery ("select nomprof, count(*) "+
									"from assignacions natural inner join professors "+
									"where instantfi is not null group by dni, nomprof;");
			while (rs.next()) {
				fila = new Tuple();
				fila.afegir(rs.getString(1));
				fila.afegir(rs.getString(2));
				ct.afegir(fila);
			}

			return ct;
		}
		catch (SQLException se) {
			/*System.out.println ("El getSQLState es: " + se.getSQLState());
	           System.out.println ();
		       System.out.println ("El getMessage es: " + se.getMessage());*/

			if (se.getSQLState().equals("23503")) throw new BDException(13);
			else throw new BDException(14);
		}
	}
}