@RequestMapping(value = "/shinfo", method = RequestMethod.POST)
@ResponseBody
public UserModel shortInfo(@RequestParam(value = "email", required = true) String email) {
UserModel userModel = new UserModel();
AccountModel accountModel = new AccountModel();
Connection connection = null;
try {
connection = dataSource.getConnection();
String sqlI =
"select id from user where email='"
+ email
+ "' AND sysdate() between validfrom and validto";
PreparedStatement psI = connection.prepareStatement(sqlI);
ResultSet resultSetI = psI.executeQuery();
if (resultSetI.next()) {
userModel.setId(resultSetI.getInt(1));
}
resultSetI.close();
psI.close();
String sqlA =
"select amount from budget where user_id=(SELECT id FROM user where email='"
+ email
+ "' AND sysdate() between validfrom and validto )";
PreparedStatement psA = connection.prepareStatement(sqlA);
ResultSet resultSetA = psA.executeQuery();
if (resultSetA.next()) {
accountModel.setAmount(resultSetA.getDouble(1));
}
resultSetA.close();
psA.close();
userModel.setEmail(email);
userModel.setAccountModel(accountModel);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
return userModel;
}
@RequestMapping(value = "/shinfoid", method = RequestMethod.POST)
@ResponseBody
public UserModel shortInfoId(@RequestParam(value = "id", required = true) Integer id) {
UserModel userModel = new UserModel();
AccountModel accountModel = new AccountModel();
Connection connection = null;
try {
connection = dataSource.getConnection();
String sqlE = "select email from user where id=" + id;
PreparedStatement psE = connection.prepareStatement(sqlE);
ResultSet resultSetE = psE.executeQuery();
if (resultSetE.next()) {
userModel.setEmail(resultSetE.getString(1));
}
resultSetE.close();
psE.close();
String sqlA = "select amount from budget where user_id=" + id;
PreparedStatement psA = connection.prepareStatement(sqlA);
ResultSet resultSetA = psA.executeQuery();
if (resultSetA.next()) {
accountModel.setAmount(resultSetA.getDouble(1));
}
resultSetA.close();
psA.close();
userModel.setId(id);
userModel.setAccountModel(accountModel);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
return userModel;
}
@RequestMapping(value = "/info", method = RequestMethod.POST)
@ResponseBody
public UserModel info(@RequestParam(value = "email", required = true) String email) {
UserModel userModel = new UserModel();
AccountModel accountModel;
List<TicketModel> ticketModelList = new ArrayList<TicketModel>();
String sql =
"select * from ticket where user_id=(select id from user where email='" + email + "')";
Connection connection = null;
try {
connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()) {
TicketModel ticketModel =
new TicketModel(
resultSet.getInt(1),
resultSet.getDouble(2),
resultSet.getString(4),
resultSet.getInt(5),
resultSet.getInt(6));
String sqlDurationSeconds =
"select time_to_sec(duration) from ticket where id =" + ticketModel.getId();
PreparedStatement psDurationSeconds = connection.prepareStatement(sqlDurationSeconds);
ResultSet resultSetDurationSeconds = psDurationSeconds.executeQuery();
if (resultSetDurationSeconds.next()) {
ticketModel.setDurationSeconds(resultSetDurationSeconds.getLong(1));
}
resultSetDurationSeconds.close();
psDurationSeconds.close();
ticketModelList.add(ticketModel);
}
resultSet.close();
ps.close();
String sqlI =
"select id from user where email='"
+ email
+ "' AND sysdate() between validfrom and validto";
PreparedStatement psI = connection.prepareStatement(sqlI);
ResultSet resultSetI = psI.executeQuery();
if (resultSetI.next()) {
userModel.setId(resultSetI.getInt(1));
}
resultSetI.close();
psI.close();
DatabaseController controller = new DatabaseController();
controller.setDataSource(dataSource);
accountModel = controller.accountInformation(email);
userModel.setEmail(email);
userModel.setTicketModelList(ticketModelList);
userModel.setAccountModel(accountModel);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
return userModel;
}