public ResultSet find(Query q) throws SQLException {
List<String> criteria = new ArrayList<>();
for (Integer id : q.idPlanning) {
criteria.add("planning = " + id);
}
for (Integer id : q.idProf) {
criteria.add("prof = " + id);
}
for (DateFr dateFr : q.start) {
criteria.add("date >= '" + dateFr + "'");
}
for (DateFr dateFr : q.end) {
DateFr endDate = new DateFr(dateFr);
endDate.incDay(1);
criteria.add("date < '" + endDate + "'");
}
for (PlanningFact.Type type : q.type) {
criteria.add("type = '" + type.toDBType() + "'");
}
String whereClause = criteria.size() > 0 ? StringUtils.join(criteria, " AND ") : "1 = 1";
String query = "SELECT * FROM " + TABLE + " WHERE " + whereClause + " ORDER BY date ASC";
return dataConnection.executeQuery(query);
}
public static int update(RehearsalPass card, DataConnection dc) throws SQLException {
PreparedStatement ps = dc.prepareStatement(UPDATE_STATEMENT);
ps.setString(1, escape(card.getLabel()));
ps.setFloat(2, card.getAmount());
ps.setInt(3, card.getMin());
ps.setInt(4, card.getTotalTime());
ps.setInt(5, card.getId());
return ps.executeUpdate();
}
public static boolean isActive(int id, DataConnection dc) throws SQLException {
String query =
"SELECT count(DISTINCT id) FROM "
+ PersonSubscriptionCardIO.TABLE
+ " WHERE idpass = "
+ id;
int n = 0;
ResultSet rs = dc.executeQuery(query);
while (rs.next()) {
n = rs.getInt(1);
}
return n > 0;
}
public static Vector<RehearsalPass> findAll(String where, DataConnection dc) throws SQLException {
Vector<RehearsalPass> v = new Vector<RehearsalPass>();
String query = "SELECT " + COLUMNS + " FROM " + TABLE + " " + where;
ResultSet rs = dc.executeQuery(query);
while (rs.next()) {
RehearsalPass c = new RehearsalPass();
c.setId(rs.getInt(1));
c.setLabel(unEscape(rs.getString(2)));
c.setAmount(rs.getFloat(3));
c.setMin(rs.getInt(4));
c.setTotalTime(rs.getInt(5));
v.addElement(c);
}
return v;
}
public void insert(PlanningFact fact) throws Exception {
String query =
String.format(
"INSERT INTO planning_fact VALUES (DEFAULT, '%s', '%s', %d, %d, '%s', '%s', %d, %d, '%s')",
toTimeStamp(fact.getDate()),
fact.getType().toDBType(),
fact.getPlanning(),
fact.getProf(),
escape(fact.getCommentaire()),
minutesToPGInterval(fact.getDureeMinutes()),
fact.getStatut(),
fact.getNiveau(),
escape(fact.getPlanningDescription()));
dataConnection.executeUpdate(query);
}
public static RehearsalPass find(int id, DataConnection dc) throws SQLException {
String query = "SELECT " + COLUMNS + " FROM " + TABLE + " WHERE id = " + id;
ResultSet rs = dc.executeQuery(query);
if (!rs.next()) {
return null;
}
RehearsalPass c = new RehearsalPass();
c.setId(rs.getInt(1));
c.setLabel(unEscape(rs.getString(2)));
c.setAmount(rs.getFloat(3));
c.setMin(rs.getInt(4));
c.setTotalTime(rs.getInt(5));
return c;
}
public void update(PlanningFact fact) throws Exception {
if (fact.getId() == -1)
throw new IllegalArgumentException("Cannot save transient fact " + fact);
String query =
String.format(
"UPDATE planning_fact "
+ "SET date = '%s', type = '%s', planning = %d, prof = %d, commentaire = '%s', duree = '%s', statut = %d, niveau = %d, planning_desc = '%s' "
+ "WHERE id = "
+ fact.getId(),
toTimeStamp(fact.getDate()),
fact.getType().toDBType(),
fact.getPlanning(),
fact.getProf(),
escape(fact.getCommentaire()),
minutesToPGInterval(fact.getDureeMinutes()),
fact.getStatut(),
fact.getNiveau(),
escape(fact.getPlanningDescription()));
dataConnection.executeUpdate(query);
}
public static boolean delete(int id, DataConnection dc) throws SQLException {
int deleted = dc.executeUpdate(getDeleteQuery(id));
return deleted > 0;
}
public static void insert(RehearsalPass card, DataConnection dc) throws SQLException {
card.setId(nextId(SEQUENCE, dc));
dc.executeUpdate(getInsertQuery(card));
}
public void delete(long id) throws Exception {
dataConnection.executeUpdate("DELETE FROM planning_fact WHERE id = " + id);
}