public void deleteSaleExpense(int seq) throws IOException {
// ATTENTION: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
try {
transaction.begin();
saleExpenseDao.deleteSaleExpense(transaction, seq);
transaction.commit();
} catch (IOException e) {
transaction.rollback();
throw e;
}
}
public int getSeq() throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
try {
transaction.begin();
int seq = saleExpenseDao.getSeq(transaction);
transaction.commit();
return seq;
} catch (IOException e) {
transaction.rollback();
throw e;
}
}
public List<SaleExpenseDto> getBySalepersonId(String id) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
try {
transaction.begin();
List<SaleExpenseDto> all = saleExpenseDao.getBySalepersonId(transaction, id);
transaction.commit();
return all;
} catch (IOException e) {
transaction.rollback();
throw e;
}
}
public SaleExpenseDto getSaleExpenseById(int seq) throws IOException {
// ATTENTION: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
try {
transaction.begin();
SaleExpenseDto result = saleExpenseDao.getSaleExpenseById(transaction, seq);
transaction.commit();
return result;
} catch (IOException e) {
transaction.rollback();
throw e;
}
}
public void updateFaq(Transaction transaction, FaqDto faq) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(UPDATE_FAQ);
prepareStatement.setInt(1, faq.getProduct().getSeq());
prepareStatement.setString(2, faq.getQuestion());
prepareStatement.setString(3, faq.getAnswer());
resultSet = prepareStatement.executeQuery();
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public void deleteCallReport(Transaction transaction, CallReportDto cr) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(DELETE_CALL_REPORT);
prepareStatement.setInt(1, cr.getSeq());
resultSet = prepareStatement.executeQuery();
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public int getSeq(Transaction transaction) throws IOException {
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(GET_SEQ);
resultSet = prepareStatement.executeQuery();
int result = 1;
if (resultSet.next()) {
result = resultSet.getInt(1);
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public int getSeq(Transaction transaction) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(GET_SEQ);
resultSet = prepareStatement.executeQuery();
int seq = 1;
if (resultSet.next()) {
seq = resultSet.getInt(1);
}
return seq;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public Map<Integer, ProductDto> getProductByUserAndStatus(
Transaction transaction, String userId, String status) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(GET_PRODUCT_BY_USER_AND_STATUS);
prepareStatement.setString(1, userId);
prepareStatement.setString(2, status.toLowerCase());
resultSet = prepareStatement.executeQuery();
return makeProductMap(resultSet);
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public List<ProductDto> getProductByDeal(Transaction transaction, int seq) throws IOException {
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(GET_PRODUCT_BY_DEAL);
prepareStatement.setInt(1, seq);
resultSet = prepareStatement.executeQuery();
List<ProductDto> result = new ArrayList<ProductDto>();
while (resultSet.next()) {
result.add(makeProductDto(transaction, resultSet));
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public List<FaqDto> getAll(Transaction transaction) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(GET_ALL);
resultSet = prepareStatement.executeQuery();
List<FaqDto> result = new ArrayList<FaqDto>();
while (resultSet.next()) {
result.add(makeFaqDto(transaction, resultSet));
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public List<ProductDto> searchByCategories(Transaction transaction, List<String> categories)
throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
StringBuilder builder = new StringBuilder();
for (int i = 0; i < categories.size(); i++) {
builder.append("?, ");
}
String stmt =
"SELECT p.product_seq from t_prod_category p WHERE p.category IN ("
+ builder.deleteCharAt(builder.length() - 2).toString()
+ ") GROUP BY p.product_seq HAVING COUNT(DISTINCT p.category) = ?";
System.out.println(stmt);
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(stmt);
int i = 1;
for (String cat : categories) {
prepareStatement.setString(i, cat);
i++;
}
prepareStatement.setInt(i, categories.size());
resultSet = prepareStatement.executeQuery();
System.out.println(prepareStatement);
List<ProductDto> result = new ArrayList<ProductDto>();
while (resultSet.next()) {
System.out.println(resultSet.getInt(1));
result.add(getProductById(transaction, resultSet.getInt(1)));
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public int countReportByUserAndTime(
Transaction transaction, String username, Date startDate, Date endDate) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Calendar day = new GregorianCalendar();
day.setTime(startDate);
day.set(Calendar.HOUR_OF_DAY, 0);
day.set(Calendar.MINUTE, 0);
day.set(Calendar.SECOND, 0);
day.set(Calendar.MILLISECOND, 0);
Calendar nextDay = new GregorianCalendar();
nextDay.setTime(endDate);
nextDay.set(Calendar.HOUR_OF_DAY, 0);
nextDay.set(Calendar.MINUTE, 0);
nextDay.set(Calendar.SECOND, 0);
nextDay.set(Calendar.MILLISECOND, 0);
nextDay.add(Calendar.DAY_OF_MONTH, 1);
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(COUNT_REPORT_BY_USER_AND_TIME);
prepareStatement.setString(1, username);
prepareStatement.setDate(2, new java.sql.Date(day.getTime().getTime()));
prepareStatement.setDate(3, new java.sql.Date(nextDay.getTime().getTime()));
resultSet = prepareStatement.executeQuery();
int result = 0;
if (resultSet.next()) {
result = resultSet.getInt(1);
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public void updateProduct(Transaction transaction, ProductDto product) throws IOException {
// ATTENTION: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(UPDATE_PRODUCT);
prepareStatement.setString(1, product.getName());
prepareStatement.setString(2, product.getSummary());
prepareStatement.setString(3, product.getTarget());
prepareStatement.setDouble(4, product.getPrice());
prepareStatement.setDouble(5, product.getPermanentPrice());
prepareStatement.setInt(6, product.getSeq());
resultSet = prepareStatement.executeQuery();
prodCache.putProduct(product);
prepareStatement = connection.prepareStatement(REMOVE_PRODUCT_CATEGORY);
prepareStatement.setInt(1, product.getSeq());
resultSet = prepareStatement.executeQuery();
for (CategoryDto dto : product.getCategory()) {
prepareStatement = connection.prepareStatement(ADD_PRODUCT_CATEGORY);
prepareStatement.setInt(1, product.getSeq());
prepareStatement.setString(2, dto.getCategory());
resultSet = prepareStatement.executeQuery();
}
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public ProductDto getProductById(Transaction transaction, int seq) throws IOException {
// ATTENTION: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
ProductDto temp = prodCache.getProduct(seq);
if (temp != null) {
return temp;
}
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(GET_PRODUCT_BY_ID);
prepareStatement.setInt(1, seq);
resultSet = prepareStatement.executeQuery();
ProductDto result = null;
while (resultSet.next()) {
result = makeProductDto(transaction, resultSet);
prodCache.putProduct(result);
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public void addReport(Transaction transaction, CallReportDto report) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(ADD_REPORT);
prepareStatement.setInt(1, getSeq(transaction));
prepareStatement.setInt(2, report.getContact().getSeq());
prepareStatement.setTimestamp(3, new java.sql.Timestamp(report.getCallTime().getTime()));
prepareStatement.setString(4, report.getDetail());
prepareStatement.setString(5, report.getRating());
prepareStatement.setString(6, report.getSalesperson().getId());
prepareStatement.setInt(7, report.getCallBack());
prepareStatement.setInt(8, report.getActId());
resultSet = prepareStatement.executeQuery();
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public List<ProductDto> searchByName(Transaction transaction, String name) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(SEARCH_BY_NAME);
prepareStatement.setString(1, "%" + name.toUpperCase() + "%");
resultSet = prepareStatement.executeQuery();
List<ProductDto> result = new ArrayList<ProductDto>();
while (resultSet.next()) {
ProductDto temp = makeProductDto(transaction, resultSet);
result.add(temp);
prodCache.putProduct(temp);
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public List<ProductDto> searchByPrice(Transaction transaction, int budget) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(SEARCH_BY_PRICE);
prepareStatement.setInt(1, budget);
prepareStatement.setInt(2, budget);
resultSet = prepareStatement.executeQuery();
List<ProductDto> result = new ArrayList<ProductDto>();
while (resultSet.next()) {
result.add(makeProductDto(transaction, resultSet));
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public List<CategoryDto> getCategoryByProduct(Transaction transaction, int seq)
throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(GET_CATEGORY_BY_PRODUCT);
prepareStatement.setInt(1, seq);
resultSet = prepareStatement.executeQuery();
List<CategoryDto> result = new ArrayList<CategoryDto>();
while (resultSet.next()) {
result.add(new CategoryDto(resultSet.getString(1)));
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public FaqDto getById(Transaction transaction, int seq) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(GET_BY_ID);
prepareStatement.setInt(1, seq);
resultSet = prepareStatement.executeQuery();
FaqDto faq = new FaqDto();
if (resultSet.next()) {
faq = makeFaqDto(transaction, resultSet);
}
return faq;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public void removeProduct(Transaction transaction, int seq) throws IOException {
// ATTENTION: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(REMOVE_PRODUCT);
prepareStatement.setInt(1, seq);
resultSet = prepareStatement.executeQuery();
prodCache.removeProduct(seq);
prepareStatement = connection.prepareStatement(REMOVE_PRODUCT_CATEGORY);
prepareStatement.setInt(1, seq);
resultSet = prepareStatement.executeQuery();
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
public ProductDto getByName(Transaction transaction, String name) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(GET_BY_NAME);
prepareStatement.setString(1, name);
resultSet = prepareStatement.executeQuery();
ProductDto result = null;
while (resultSet.next()) {
result = makeProductDto(transaction, resultSet);
prodCache.putProduct(result);
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
