public ArrayList<Tarea> listadoPendienteEquipo(int idEquipo) throws Exception {
ArrayList<Tarea> resultado = null;
resultado = new ArrayList<Tarea>();
String consulta = "SELECT * FROM tareas t WHERE t.activa=1 AND NOT EXISTS ";
consulta =
consulta
+ "(SELECT * FROM tareas_equipo te WHERE t.idTarea=te.idTarea AND te.idEquipo=? AND te.resultado is NOT null)";
consulta =
consulta
+ " AND EXISTS (SELECT * FROM Colecciones_Tarea ct INNER JOIN Equipos_Coleccion ec on ec.idColeccion=ct.idColeccion WHERE ec.idEquipo=? AND ct.idTarea=t.idTarea)";
PreparedStatement ps = conn.getConnection().prepareStatement(consulta);
ps.setInt(1, idEquipo);
ResultSet rs = ps.executeQuery(consulta);
Tarea t;
while (rs.next()) {
t = new Tarea();
t.setActiva(rs.getBoolean("activa"));
t.setIdPaquete(rs.getInt("idPaquete"));
t.setIdTarea(rs.getInt("idTarea"));
t.setNombre(rs.getString("nombre"));
t.setUsuario(rs.getString("usuario"));
t.setPassword(rs.getString("password"));
resultado.add(t);
}
log.Escribir("DAOTareas - Listado - OK: numero de tareas: " + resultado.size());
conn.desconectar();
return resultado;
}
private void ReadDataToArray(Workbook dataWb) {
Connection con = DbConnection.getConnection();
String DataSheetInsertQuery = "insert into DataSheet_tbl values(?,?,?);";
for (int i = 1; i < dataWb.getSheet("Data").getPhysicalNumberOfRows(); i++) {
if (!dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue().equals(null)) {
try {
preparedStatement = con.prepareStatement(DataSheetInsertQuery);
preparedStatement.setString(
1,
String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(1).getNumericCellValue()));
preparedStatement.setDate(
2,
new Date(
(dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue()).getTime()));
preparedStatement.setString(
3,
String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(2).getNumericCellValue()));
preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
DbConnection.closePreparedStatement(preparedStatement);
DbConnection.closeConnection(con);
System.out.println("All the data in the sheet is analysed and updated to memory");
}
public ArrayList<Tarea> listadoTareas() throws Exception {
System.out.println("DAOTareas - listadoTareas - INICIO");
ArrayList<Tarea> resultado = null;
resultado = new ArrayList<Tarea>();
String consulta = "SELECT * FROM Tareas";
Statement st = conn.getConnection().createStatement();
System.out.println("Ejecutamos la consulta");
ResultSet rs = st.executeQuery(consulta);
System.out.println("Recorremos el recordset");
while (rs.next()) {
Tarea t = new Tarea();
t.setActiva(rs.getBoolean("activa"));
t.setIdPaquete(rs.getInt("idPaquete"));
t.setIdTarea(rs.getInt("idTarea"));
t.setNombre(rs.getString("nombre"));
t.setUsuario(rs.getString("usuario"));
t.setPassword(rs.getString("password"));
resultado.add(t);
}
System.out.println("DAOTareas - ListadoTareas - OK - número de Tareas: " + resultado.size());
log.Escribir("DAOTareas - Listado - OK - número de Tareas: " + resultado.size());
conn.desconectar();
return resultado;
}
public void analyseData(String createdFileName) {
Connection con = DbConnection.getConnection();
String DataSheetSelectQuery =
"select item_quantity from DataSheet_tbl where item_id = ? and item_date = ?;";
XSSFWorkbook createdWorkbook = null;
File dataFileOriginal = new File(createdFileName);
FileInputStream fIPCreatedFile;
try {
fIPCreatedFile = new FileInputStream(dataFileOriginal);
createdWorkbook = new XSSFWorkbook(fIPCreatedFile);
} catch (Exception e) {
e.printStackTrace();
}
for (int k = 0; k < 12; k++) {
for (int i = 1; i < createdWorkbook.getSheetAt(k).getPhysicalNumberOfRows(); i++) {
double itemNumber =
createdWorkbook.getSheetAt(k).getRow(i).getCell(0).getNumericCellValue();
Calendar cal = Calendar.getInstance();
cal.set(Calendar.YEAR, 2014);
cal.set(Calendar.MONTH, k);
int maxDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
for (int j = 1; j <= maxDay; j++) {
java.util.Date itemDate =
Date.valueOf(
createdWorkbook.getSheetAt(k).getRow(0).getCell(j + 1).getStringCellValue());
java.sql.Date itemDateSql = new java.sql.Date(itemDate.getTime());
try {
preparedStatement = con.prepareStatement(DataSheetSelectQuery);
preparedStatement.setDouble(1, itemNumber);
preparedStatement.setDate(2, itemDateSql);
rs = preparedStatement.executeQuery();
while (rs.next()) {
double quant = rs.getDouble("item_quantity");
createdWorkbook.getSheetAt(k).getRow(i).createCell(j + 1).setCellValue(quant);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
DbConnection.closePreparedStatement(preparedStatement);
DbConnection.closeConnection(con);
FileOutputStream out;
try {
out = new FileOutputStream(createdFileName);
createdWorkbook.write(out);
out.close();
System.out.println("Data analysed and created the excel sheet.");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public boolean activarTarea(int idTarea) throws Exception {
PreparedStatement ps;
String consulta = "UPDATE Tareas SET activa=1 WHERE idTarea=?";
ps = conn.getConnection().prepareStatement(consulta);
ps.setInt(1, idTarea);
boolean result = (ps.executeUpdate() == 1);
ps.close();
conn.desconectar();
return result;
}
public boolean cambiarNombre(String nombre, int idTarea) throws Exception {
PreparedStatement ps;
String consulta = "UPDATE Tareas SET nombre=? WHERE idTarea=?";
ps = conn.getConnection().prepareStatement(consulta);
ps.setString(1, nombre);
ps.setInt(2, idTarea);
boolean result = (ps.executeUpdate() == 1);
ps.close();
conn.desconectar();
return result;
}
public static void dispose() {
try {
w.lock();
DbConnection.force();
DbConnection.closeFiles();
} catch (Throwable e) {
throw DbConnection.handleError(e);
} finally {
ourIsDisposed = true;
w.unlock();
}
}
public boolean cambiarCredenciales(String usuario, String password, int idTarea)
throws Exception {
PreparedStatement ps;
String consulta = "UPDATE Tareas SET usuario=?, password=? WHERE idTarea=?";
ps = conn.getConnection().prepareStatement(consulta);
ps.setString(1, usuario);
ps.setString(2, password);
ps.setInt(2, idTarea);
boolean result = (ps.executeUpdate() == 1);
ps.close();
conn.desconectar();
return result;
}
@Override
public boolean delete(int id) throws SQLException {
boolean resul = false;
DbConnection conn = new DbConnection();
String sql = "DELETE FROM `estilo` WHERE `id`= ? ;";
PreparedStatement consulta = conn.getConnection().prepareStatement(sql);
consulta.setInt(1, id);
if (consulta.executeUpdate() == 1) {
resul = true;
}
cerrarPeticion(conn, consulta);
return resul;
}
private static void deleteRecord(final int id) {
try {
w.lock();
DbConnection.markDirty();
deleteContentAndAttributes(id);
DbConnection.cleanRecord(id);
addToFreeRecordsList(id);
} catch (Throwable e) {
throw DbConnection.handleError(e);
} finally {
w.unlock();
}
}
@Override
public Estilo getById(int id) throws SQLException {
Estilo est = new Estilo();
DbConnection conn = new DbConnection();
String sql = "select id, nombre, descripcion, codigo from `estilo` where id = ? ;";
PreparedStatement consulta = conn.getConnection().prepareStatement(sql);
consulta.setInt(1, id);
ResultSet rs = consulta.executeQuery();
if (rs.next()) {
est = mapeo(rs);
}
cerrarConsulta(rs, conn, consulta);
return est;
}
@Override
public List<Estilo> getAll() throws SQLException {
ArrayList<Estilo> lista = new ArrayList<Estilo>();
DbConnection conn = new DbConnection();
String sql =
"select id, nombre, descripcion, codigo from `estilo` order by `id` desc limit 500;";
PreparedStatement consulta = conn.getConnection().prepareStatement(sql);
ResultSet rs = consulta.executeQuery();
while (rs.next()) {
lista.add(mapeo(rs));
}
cerrarConsulta(rs, conn, consulta);
return lista;
}
public ArrayList<ResultadoTarea> resultadosEquipo(int idEquipo) {
ArrayList<ResultadoTarea> resultados = new ArrayList<ResultadoTarea>();
String consulta =
"SELECT t.nombre, e.ip, te.fechaEjecucion,te.resultado,te.idTarea,te.idEquipo FROM Tareas_Equipo te inner join Tareas t on t.idTarea=te.idTarea inner join Equipos e on e.idEquipo=te.idEquipo WHERE te.idEquipo=?";
PreparedStatement ps;
try {
ps = conn.getConnection().prepareStatement(consulta);
ps.setInt(1, idEquipo);
ResultSet rs = ps.executeQuery();
ResultadoTarea r;
while (rs.next()) {
r =
new ResultadoTarea(
rs.getInt("idTarea"),
rs.getInt("idEquipo"),
rs.getString("nombre"),
rs.getString("ip"),
rs.getString("resultado"),
rs.getDate("fechaEjecucion"));
resultados.add(r);
}
} catch (Exception e) {
System.out.println("Excepcion en DAOTareas_resultadosEquipo: " + e.getMessage());
}
return resultados;
}
public Tarea getTarea(int idTarea) {
ResultSet rs = null;
PreparedStatement pStmt = null;
Tarea tarea = null;
try {
String consulta = "SELECT * FROM Tareas WHERE idTarea=?";
pStmt = conn.getConnection().prepareStatement(consulta);
pStmt.setInt(1, idTarea);
rs = pStmt.executeQuery();
if (rs.next()) {
tarea =
new Tarea(
rs.getInt("idTarea"),
rs.getString("nombre"),
rs.getInt("idPaquete"),
rs.getString("usuario"),
rs.getString("password"),
rs.getBoolean("activa"));
}
return tarea;
} catch (Exception e) {
log.Escribir("Excepción DAOTareas_getTarea: " + e.getMessage());
System.out.println("Excepción DAOTareas_getTarea: " + e.getMessage());
return null;
}
}
public boolean nuevaEjecucion(int idTarea, int idEquipo, String resultado, Date fechaEjecucion) {
boolean actualizacion = true;
String consulta =
"INSERT INTO Tareas_Equipo (resultado,fechaEjecucion,idTarea,idEquipo) VALUES (?,?,?,?)";
log.Escribir(
"DAOTareas - NuevaEjecucion - Parametros: "
+ idTarea
+ ","
+ idEquipo
+ ","
+ resultado
+ ","
+ fechaEjecucion);
try {
log.Escribir("Antes de crear la conexion");
PreparedStatement ps = conn.getConnection().prepareStatement(consulta);
log.Escribir("Despues de crear la conexion");
ps.setString(1, resultado);
ps.setDate(2, fechaEjecucion);
ps.setInt(3, idTarea);
ps.setInt(4, idEquipo);
log.Escribir("Antes del update");
int numFilas = ps.executeUpdate();
log.Escribir("Despues del update");
if (numFilas != 1) {
log.Escribir("Error en DAOTareas - NuevaEjecucion: Número de filas afectadas: " + numFilas);
}
} catch (Exception e) {
log.Escribir("DAOTareas - NuevaEjecución - Excepción: " + e.getMessage());
actualizacion = false;
}
return actualizacion;
}
public static void select(
DbConnection conn,
String tblNames,
String colNames,
String qual,
boolean distinct,
String hint,
DbOutputRecordSet rs)
throws Exception {
DbSelectStatement stmt = null;
DbOutputRecord rec;
try {
if (rs.getMaxNumItems() > 0) {
stmt = new DbSelectStatement();
if (conn.isAllowHint()) {
/* String sql = */ stmt.create(conn, null, tblNames, colNames, qual, distinct, hint);
} else {
/* String sql = */ stmt.create(conn, null, tblNames, colNames, qual, distinct);
}
stmt.execute();
int count = 0;
while (stmt.next() && count++ < rs.getMaxNumItems()) {
rec = rs.newRecord();
rec.getStatementValues(stmt);
}
stmt.release();
}
} catch (Exception e) {
DbSelectStatement.ensureRelease(stmt, e);
}
}
public static void select(
DbConnection conn,
String tblNames,
String colNames,
String qual,
String hint,
DbOutputRecord rec)
throws Exception {
DbSelectStatement stmt = null;
try {
stmt = new DbSelectStatement();
if (conn.isAllowHint()) {
/* String sql = */ stmt.create(conn, null, tblNames, colNames, qual, false, hint);
} else {
/* String sql = */ stmt.create(conn, null, tblNames, colNames, qual, false);
}
stmt.execute();
if (stmt.next()) rec.getStatementValues(stmt);
else throw new IeciTdException(DbError.EC_NOT_FOUND, DbError.EM_NOT_FOUND);
stmt.release();
} catch (Exception e) {
DbSelectStatement.ensureRelease(stmt, e);
}
}
public static void releaseContent(int contentId) {
try {
getContentStorage().releaseRecord(contentId);
} catch (Throwable e) {
throw DbConnection.handleError(e);
}
}
public static Pair<String[], int[]> listAll(int parentId) {
try {
r.lock();
try {
final DataInputStream input = readAttribute(parentId, CHILDREN_ATT);
if (input == null)
return Pair.create(ArrayUtil.EMPTY_STRING_ARRAY, ArrayUtil.EMPTY_INT_ARRAY);
final int count = DataInputOutputUtil.readINT(input);
final int[] ids = ArrayUtil.newIntArray(count);
final String[] names = ArrayUtil.newStringArray(count);
for (int i = 0; i < count; i++) {
int id = DataInputOutputUtil.readINT(input);
id = id >= 0 ? id + parentId : -id;
ids[i] = id;
names[i] = getName(id);
}
input.close();
return Pair.create(names, ids);
} finally {
r.unlock();
}
} catch (Throwable e) {
throw DbConnection.handleError(e);
}
}
public static long getCreationTimestamp() {
try {
r.lock();
return DbConnection.getTimestamp();
} finally {
r.unlock();
}
}
public boolean crearTarea(
int idPaquete, String nombre, String usuario, String password, boolean activa)
throws Exception {
PreparedStatement ps;
String consulta =
"INSERT INTO Tareas (idPaquete,nombre,usuario,password,activa) VALUES (?,?,?,?,?)";
ps = conn.getConnection().prepareStatement(consulta);
ps.setInt(1, idPaquete);
ps.setString(2, nombre);
ps.setString(3, usuario);
ps.setString(4, password);
ps.setBoolean(5, activa);
boolean result = (ps.executeUpdate() == 1);
ps.close();
conn.desconectar();
return result;
}
@Nullable
public static DataInputStream readContentById(int contentId) {
try {
return getContentStorage().readStream(contentId);
} catch (Throwable e) {
throw DbConnection.handleError(e);
}
}
@Override
public boolean update(Estilo est) throws SQLException {
boolean resul = false;
if (est != null && !est.equals(new Estilo())) {
DbConnection conn = new DbConnection();
String sql =
"UPDATE `estilo` SET `nombre`= ? , `descripcion`= ?, `codigo`= ? WHERE `id`= ? ;";
PreparedStatement consulta = conn.getConnection().prepareStatement(sql);
consulta.setString(1, est.getNombre());
consulta.setString(2, est.getDescripcion());
consulta.setString(3, est.getCodigo());
consulta.setInt(4, est.getId());
if (consulta.executeUpdate() == 1) {
resul = true;
}
cerrarPeticion(conn, consulta);
}
return resul;
}
@Override
public void close() throws IOException {
super.close();
try {
doFlush();
} catch (Throwable e) {
throw DbConnection.handleError(e);
}
}
/**
* Get base data from database and create a dataset for analysis
*
* @param lat
* @param lon
* @throws SQLException
*/
public void buildDataSet(int size) throws SQLException {
DbConnection dbConn = new DbConnection();
int latLonStep = (int) (360 / (MOON_CIRC / size));
Double currentLat = LAT_MIN;
Double currentLon = LON_MIN;
String db = DataSets.getDb(size);
ArrayList<ArrayList<DataTile>> dataTiles;
Connection conn = dbConn.getConnection();
updateSetConfig(conn, db, latLonStep, latLonStep);
currentLat = LAT_MIN;
currentLon = LON_MIN;
while (currentLat < LAT_MAX) {
Double startLat;
Double endLat;
startLat = currentLat;
endLat = currentLat + latLonStep;
while (currentLon < LON_MAX) {
dataTiles = new ArrayList<ArrayList<DataTile>>();
Double startLon;
Double endLon;
startLon = currentLon;
endLon = currentLon + latLonStep;
// Get the data
dataTiles = getData(startLat, endLat, startLon, endLon, conn);
DataTile tile = processTile(dataTiles, 1896, currentLat, currentLon);
if (tile != null) {
addTileSet(conn, db, tile);
}
dataTiles = null;
currentLon += latLonStep;
}
currentLat += latLonStep;
}
dbConn.closeConnection(conn);
}
public static void setTimestamp(int id, long value) {
try {
w.lock();
incModCount(id);
getRecords().putLong(getOffset(id, TIMESTAMP_OFFSET), value);
} catch (Throwable e) {
throw DbConnection.handleError(e);
} finally {
w.unlock();
}
}
public static void setLength(int id, long len) {
try {
w.lock();
incModCount(id);
getRecords().putLong(getOffset(id, LENGTH_OFFSET), len);
} catch (Throwable e) {
throw DbConnection.handleError(e);
} finally {
w.unlock();
}
}
public static void setName(int id, String name) {
try {
w.lock();
incModCount(id);
putRecordInt(id, NAME_OFFSET, getNames().enumerate(name));
} catch (Throwable e) {
throw DbConnection.handleError(e);
} finally {
w.unlock();
}
}
public static void deleteRecordRecursively(int id) {
try {
w.lock();
incModCount(id);
doDeleteRecursively(id);
} catch (Throwable e) {
throw DbConnection.handleError(e);
} finally {
w.unlock();
}
}
public static int storeUnlinkedContent(byte[] bytes) {
try {
int recordId = getContentStorage().acquireNewRecord();
AbstractStorage.StorageDataOutput output = getContentStorage().writeStream(recordId, true);
output.write(bytes);
output.close();
return recordId;
} catch (IOException e) {
throw DbConnection.handleError(e);
}
}
