@Override
public int countTotalSearch() {
Connection connection = null;
PreparedStatement stm = null;
ResultSet rs = null;
try {
connection = BaseDAO.getConnect();
stm = connection.prepareStatement("SELECT COUNT(resultID) as \"Total\" FROM result");
rs = stm.executeQuery();
if (rs.next()) {
return rs.getInt("Total");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stm != null) {
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return 0;
}
@Override
public Boolean delete(int id) {
Connection connection = null;
PreparedStatement stm = null;
try {
connection = BaseDAO.getConnect();
stm =
connection.prepareStatement(
"UPDATE trafficdb.result SET isActive = false WHERE resultID=?");
stm.setInt(1, id);
return stm.executeUpdate() > 0;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stm != null) {
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return false;
}
@Override
public ArrayList<ResultDTO> getAllResult(Boolean getInActive) {
Connection connection = null;
PreparedStatement stm = null;
ArrayList<ResultDTO> resultData = new ArrayList<ResultDTO>();
try {
connection = BaseDAO.getConnect();
if (getInActive == false) {
stm =
connection.prepareStatement(
"SELECT resultID, uploadedImage, listTraffic, creator, createDate, isActive FROM result WHERE isActive = ? ORDER BY resultID DESC");
stm.setBoolean(1, true);
} else {
stm =
connection.prepareStatement(
"SELECT resultID, uploadedImage, listTraffic, creator, createDate, isActive FROM result ORDER BY resultID DESC");
}
ResultSet rs = stm.executeQuery();
while (rs.next()) {
ResultDTO resultObject = new ResultDTO();
resultObject.setResultID(rs.getInt("resultID"));
resultObject.setUploadedImage(rs.getString("uploadedImage"));
resultObject.setListTraffic(rs.getString("listTraffic"));
resultObject.setCreator(rs.getString("creator"));
Timestamp tempTimeStamp = rs.getTimestamp("createDate");
Date tempDate = new Date(tempTimeStamp.getTime());
resultObject.setCreateDate(tempDate);
resultObject.setIsActive(rs.getBoolean("isActive"));
resultData.add(resultObject);
}
return resultData;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stm != null) {
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return resultData;
}
public int add(ResultDTO result) {
Connection connection = null;
PreparedStatement stm = null;
try {
String uploadedImage = result.getUploadedImage();
String creator = result.getCreator();
connection = BaseDAO.getConnect();
stm =
connection.prepareStatement(
"INSERT INTO result(uploadedImage, creator, createDate,isActive) VALUES(?,?,NOW(),?)");
stm.setString(1, uploadedImage);
stm.setString(2, creator);
stm.setBoolean(3, false);
stm.executeUpdate();
stm.close();
stm =
connection.prepareStatement(
"SELECT resultID FROM trafficdb.result WHERE creator = ? ORDER BY resultID DESC LIMIT 1");
stm.setString(1, creator);
ResultSet rs = stm.executeQuery();
if (rs.next()) {
return rs.getInt("resultID");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stm != null) {
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return -1;
}
public ResultDTO getResultByID(int id) {
Connection connection = null;
PreparedStatement stm = null;
ResultDTO resultData = new ResultDTO();
try {
connection = BaseDAO.getConnect();
stm =
connection.prepareStatement(
"SELECT uploadedImage,listTraffic,creator,createDate,isActive FROM trafficdb.result WHERE resultID=? ORDER BY resultID DESC");
stm.setInt(1, id);
ResultSet rs = stm.executeQuery();
if (rs.next()) {
resultData.setResultID(id);
resultData.setUploadedImage(rs.getString("uploadedImage"));
resultData.setListTraffic(rs.getString("listTraffic"));
resultData.setCreator(rs.getString("creator"));
Timestamp tempTimeStamp = rs.getTimestamp("createDate");
Date tempDate = new Date(tempTimeStamp.getTime());
resultData.setCreateDate(tempDate);
resultData.setIsActive(rs.getBoolean("isActive"));
return resultData;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stm != null) {
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return null;
}
@Override
public ArrayList<StatisticDTO> statisticResult(Date from, Date to) {
ArrayList<StatisticDTO> listResult = new ArrayList<StatisticDTO>();
Connection connection = null;
PreparedStatement stm = null;
ResultSet rs = null;
try {
connection = BaseDAO.getConnect();
stm =
connection.prepareStatement(
"SELECT COUNT(resultID) as \"Num\", Date(createDate) as \"Date\" FROM result WHERE ? <= Date(createDate) AND Date(createDate) <= ? GROUP BY Date(createDate)");
stm.setDate(1, from);
stm.setDate(2, to);
rs = stm.executeQuery();
while (rs.next()) {
StatisticDTO statisticDTO = new StatisticDTO();
statisticDTO.setNum(rs.getInt("Num"));
Timestamp tempTimeStamp = rs.getTimestamp("Date");
Date tempDate = new Date(tempTimeStamp.getTime());
statisticDTO.setDate(tempDate);
listResult.add(statisticDTO);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stm != null) {
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return listResult;
}
@Override
public ArrayList<ResultDTO> getResultInLastXDay(int days) {
Connection connection = null;
PreparedStatement stm = null;
ArrayList<ResultDTO> resultData = new ArrayList<ResultDTO>();
try {
connection = BaseDAO.getConnect();
stm =
connection.prepareStatement(
"SELECT resultID, createDate FROM trafficdb.result WHERE createDate >= DATE_SUB(NOW(),INTERVAL ? DAY) ORDER BY createDate DESC");
stm.setInt(1, days);
ResultSet rs = stm.executeQuery();
while (rs.next()) {
ResultDTO resultObject = new ResultDTO();
resultObject.setResultID(rs.getInt("resultID"));
resultObject.setCreateDate(rs.getDate("createDate"));
resultData.add(resultObject);
}
return resultData;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stm != null) {
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return resultData;
}
public Boolean edit(ResultDTO result) {
Connection connection = null;
PreparedStatement stm = null;
try {
String listTraffic = result.getListTraffic();
Boolean isActive = result.getIsActive();
int resultID = result.getResultID();
String imagePath = result.getUploadedImage();
connection = BaseDAO.getConnect();
stm =
connection.prepareStatement(
"UPDATE trafficdb.result SET listTraffic = ?,uploadedImage = ?, isActive = ? WHERE resultID=?");
stm.setString(1, listTraffic);
stm.setString(2, imagePath);
stm.setBoolean(3, isActive);
stm.setInt(4, resultID);
return stm.executeUpdate() > 0;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stm != null) {
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return false;
}