public static void main(String[] args) {
try {
Connection con = DBopsMySql.setuptradesConnection();
con.setAutoCommit(false);
String ul = "AUD";
PreparedStatement pstmt = DBopsMySql.getExpirysForUpdate(con, ul, 20090300, 20100700);
PreparedStatement upDateStmt = DBopsMySql.updateBeginEndDatesForExpiry(con);
ResultSet res = pstmt.executeQuery();
res.next(); // To get a lastexpiry for loop, so should be one extra early expiry
int lastexp = res.getInt("expiry");
while (res.next()) {
int exp = res.getInt("expiry");
String bdate = DateOps.dbShortFormatString(lastexp - 5);
String edate = DateOps.dbShortFormatString(exp - 6);
upDateStmt.setString(1, bdate);
if (!res.isLast()) {
upDateStmt.setString(2, edate);
}
upDateStmt.setString(3, ul);
upDateStmt.setInt(4, exp);
upDateStmt.addBatch();
lastexp = exp;
}
int[] updateCounts = upDateStmt.executeBatch();
upDateStmt.close();
con.close();
} catch (SQLException ex) {
MsgBox.err2(ex);
}
}
public String getCustomers(String lastName) {
StringBuffer json = new StringBuffer();
try {
entries =
stmt.executeQuery(
"SELECT Person_ID,Vorname, Name, Strasse, Ort, Privattelefon FROM person WHERE '"
+ lastName
+ "' LIKE Vorname OR '"
+ lastName
+ "' LIKE Name ");
json.append("[");
while (entries.next()) {
json.append("{");
json.append("\"id\":" + entries.getString("Person_ID") + ",");
json.append("\"firstname\":\"" + entries.getString("Vorname") + "\",");
json.append("\"lastname\":\"" + entries.getString("Name") + "\",");
json.append("\"street\":\"" + entries.getString("Strasse") + "\",");
json.append("\"city\":\"" + entries.getString("Ort") + "\",");
json.append("\"tel\":\"" + entries.getString("Privattelefon") + "\"");
if (entries.isLast()) {
json.append("}");
} else {
json.append("},");
}
}
json.append("]");
} catch (SQLException e) {
e.printStackTrace();
}
return json.toString();
}
public String getCustomers(Date date) {
StringBuffer json = new StringBuffer();
try {
entries =
stmt.executeQuery(
"SELECT Person_ID, Vorname,Name,Ankunft,Abreise FROM buchung, person WHERE '"
+ date
+ "' >= Ankunft And '"
+ date
+ "' <= Abreise And Person_ID = Person_IDFS");
json.append("[");
while (entries.next()) {
json.append("{");
json.append("\"id\":" + entries.getString("Person_ID") + ",");
json.append("\"firstname\":\"" + entries.getString("Vorname") + "\",");
json.append("\"lastname\":\"" + entries.getString("Name") + "\",");
json.append("\"arrival\":\"" + entries.getString("Ankunft") + "\",");
json.append("\"departure\":\"" + entries.getString("Abreise") + "\"");
if (entries.isLast()) {
json.append("}");
} else {
json.append("},");
}
}
json.append("]");
} catch (SQLException e) {
e.printStackTrace();
}
return json.toString();
}
public String getCutomerDetails(int id) {
StringBuilder json = new StringBuilder();
ResultSet additionalSet;
ResultSet additionalSet2;
try {
entries =
stmt.executeQuery(
"SELECT Person_ID,Vorname, Name, Strasse, Ort, Privattelefon FROM person WHERE Person_ID = "
+ id);
additionalSet =
stmt2.executeQuery(
"SELECT Buchung_ID, Ankunft,Abreise FROM buchung WHERE Person_IDFS = " + id);
json.append("[");
while (entries.next()) {
json.append("{");
json.append("\"id\":\"" + entries.getString("Person_ID") + "\",");
json.append("\"firstname\":\"" + entries.getString("Vorname") + "\",");
json.append("\"lastname\":\"" + entries.getString("Name") + "\",");
json.append("\"street\":\"" + entries.getString("Strasse") + "\",");
json.append("\"city\":\"" + entries.getString("Ort") + "\",");
json.append("\"tel\":\"" + entries.getString("Privattelefon") + "\",");
json.append("\"bookings\": [");
while (additionalSet.next()) {
json.append("{");
json.append("\"arrival\":\"" + additionalSet.getString("Ankunft") + "\",");
json.append("\"departure\":\"" + additionalSet.getString("Abreise") + "\",");
json.append("\"consumptions\": [");
additionalSet2 =
stmt3.executeQuery(
"SELECT Beschreibung, Anzahl, Preis, Rabatt, Datum FROM position, leistung WHERE Leistung_IDFS = Leistung_ID AND Buchung_IDFS = "
+ additionalSet.getString("Buchung_ID")
+ " ORDER BY Datum");
while (additionalSet2.next()) {
json.append("{");
json.append(
"\"description\":\""
+ additionalSet2.getString("Beschreibung").replace("\r", "")
+ "\",");
json.append("\"amount\":\"" + additionalSet2.getString("Anzahl") + "\",");
json.append("\"price\":\"" + additionalSet2.getString("Preis") + "\",");
json.append("\"discount\":\"" + additionalSet2.getString("Rabatt") + "\",");
json.append("\"date\":\"" + additionalSet2.getString("Datum") + "\"");
if (additionalSet2.isLast()) {
json.append("}");
} else {
json.append("},");
}
}
json.append("]");
if (additionalSet.isLast()) {
json.append("}");
} else {
json.append("},");
}
}
json.append("]");
json.append("}");
}
json.append("]");
} catch (SQLException e) {
e.printStackTrace();
}
System.out.print(isJSONValid(json.toString()));
return json.toString();
}
