Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.save.serviceprovider; import com.save.connection.DBConnection; import com.save.global.ErrorLoggedNotification; import com.save.model.PromoDeals; import com.save.service.PromoDealService; import com.save.utilities.CommonUtilities; import com.vaadin.ui.Notification; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; /** * * @author jetdario */ public class PromoDealServiceImpl implements PromoDealService { @Override public boolean create(PromoDeals p) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; boolean result = false; try { pstmt = conn.prepareStatement( "INSERT INTO promo_deals " + "SET ClientID = ?, " + "PromoItem = ?, " + "PromoAmount = ?, " + "Quantity = ?, " + "ProductID = ?, " + "EntryDate = ?, " + "StartDate = ?, " + "EndDate = ?, " + "Remarks = ?, " + "SalesRepID = ?, " + "AreaSalesID = ?"); pstmt.setInt(1, p.getClientId()); pstmt.setString(2, p.getPromoItem()); pstmt.setDouble(3, p.getPromoAmount()); pstmt.setDouble(4, p.getQuantity()); pstmt.setInt(5, p.getProductId()); pstmt.setString(6, CommonUtilities.convertDateFormat(p.getEntryDate().toString())); pstmt.setString(7, CommonUtilities.convertDateFormat(p.getStartDate().toString())); pstmt.setString(8, CommonUtilities.convertDateFormat(p.getEndDate().toString())); pstmt.setString(9, p.getRemarks()); pstmt.setInt(10, p.getSalesRepId()); pstmt.setInt(11, p.getAreaSalesId()); pstmt.executeUpdate(); result = true; Notification.show("Added New Promo", Notification.Type.TRAY_NOTIFICATION); } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return result; } @Override public boolean delete(int promoId, String remarks) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; boolean result = false; try { pstmt = conn.prepareStatement("UPDATE promo_deals " + "SET Status = ? " + "WHERE PromoDealID = ? "); pstmt.setInt(1, 1); pstmt.setInt(2, promoId); pstmt.executeUpdate(); result = true; Notification.show("Promo Deleted!", Notification.Type.TRAY_NOTIFICATION); } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return result; } @Override public boolean update(PromoDeals p) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; boolean result = false; try { pstmt = conn.prepareStatement("UPDATE promo_deals " + "SET PromoItem = ?, " + "PromoAmount = ?, " + "Quantity = ?, " + "ProductID = ?, " + "EntryDate = ?, " + "StartDate = ?, " + "EndDate = ?, " + "Remarks = ?, " + "SalesRepID = ?, " + "AreaSalesID = ? " + "WHERE PromoDealID = ? "); pstmt.setString(1, p.getPromoItem()); pstmt.setDouble(2, p.getPromoAmount()); pstmt.setDouble(3, p.getQuantity()); pstmt.setInt(4, p.getProductId()); pstmt.setString(5, CommonUtilities.convertDateFormat(p.getEntryDate().toString())); pstmt.setString(6, CommonUtilities.convertDateFormat(p.getStartDate().toString())); pstmt.setString(7, CommonUtilities.convertDateFormat(p.getEndDate().toString())); pstmt.setString(8, p.getRemarks()); pstmt.setInt(9, p.getSalesRepId()); pstmt.setInt(10, p.getAreaSalesId()); pstmt.setInt(11, p.getPromoId()); pstmt.executeUpdate(); result = true; Notification.show("Promo Updated!", Notification.Type.TRAY_NOTIFICATION); } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return result; } @Override public List<PromoDeals> getPromoDealsByClientId(int clientId) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<PromoDeals>(); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND ClientID = ? " + "ORDER BY PromoDealID DESC"); pstmt.setInt(1, clientId); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public PromoDeals getPromoDealById(int promoId) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; PromoDeals pd = new PromoDeals(); try { pstmt = conn .prepareStatement("SELECT * FROM promo_deals " + "WHERE Status = 0 " + "AND PromoDealID = ?"); pstmt.setInt(1, promoId); rs = pstmt.executeQuery(); while (rs.next()) { pd.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); pd.setPromoItem(rs.getString("PromoItem")); pd.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); pd.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); pd.setProductId(CommonUtilities.convertStringToInt(rs.getString("ProductID"))); pd.setEntryDate(CommonUtilities.parsingDate(rs.getString("EntryDate"))); pd.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); pd.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); pd.setAreaSalesId(CommonUtilities.convertStringToInt(rs.getString("AreaSalesID"))); pd.setSalesRepId(CommonUtilities.convertStringToInt(rs.getString("SalesRepID"))); pd.setRemarks(rs.getString("Remarks")); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return pd; } @Override public List<PromoDeals> getAllPromoDeals() { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<PromoDeals>(); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "ORDER BY ClientName ASC"); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(int clientId) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND ClientID = ? "); pstmt.setInt(1, clientId); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(double amount) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND PromoAmount <= ? "); pstmt.setDouble(1, amount); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(Date from, Date to) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); String fromDate = CommonUtilities.convertDateFormat(from.toString()); String toDate = CommonUtilities.convertDateFormat(to.toString()); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND (EntryDate BETWEEN ? AND ?) "); pstmt.setString(1, fromDate); pstmt.setString(2, toDate); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(int clientId, double amount) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND ClientId = ? " + "AND PromoAmount <= ? "); pstmt.setInt(1, clientId); pstmt.setDouble(2, amount); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(int clientId, Date from, Date to) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); String fromDate = CommonUtilities.convertDateFormat(from.toString()); String toDate = CommonUtilities.convertDateFormat(to.toString()); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND ClientID = ? " + "AND (EntryDate BETWEEN ? AND ?) "); pstmt.setInt(1, clientId); pstmt.setString(2, fromDate); pstmt.setString(3, toDate); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(double amount, Date from, Date to) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); String fromDate = CommonUtilities.convertDateFormat(from.toString()); String toDate = CommonUtilities.convertDateFormat(to.toString()); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND PromoAmount = ? " + "AND (EntryDate BETWEEN ? AND ?) "); pstmt.setDouble(1, amount); pstmt.setString(2, fromDate); pstmt.setString(3, toDate); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(int clientId, double amount, Date from, Date to) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); String fromDate = CommonUtilities.convertDateFormat(from.toString()); String toDate = CommonUtilities.convertDateFormat(to.toString()); try { pstmt = conn.prepareStatement( "SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus - 0 " + "AND ClientStatus = 0 " + "AND ClientId = ? " + "AND PromoAmount = ? " + "AND (EntryDate BETWEEN ? AND ?) "); pstmt.setInt(1, clientId); pstmt.setDouble(2, amount); pstmt.setString(3, fromDate); pstmt.setString(4, toDate); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(String areaCode) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND AreaCode = ? "); pstmt.setString(1, areaCode); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(String areaCode, double amount) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND AreaCode = ? " + "AND PromoAmount <= ? "); pstmt.setString(1, areaCode); pstmt.setDouble(2, amount); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(String areaCode, Date from, Date to) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); String fromDate = CommonUtilities.convertDateFormat(from.toString()); String toDate = CommonUtilities.convertDateFormat(to.toString()); try { pstmt = conn.prepareStatement("SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus = 0 " + "AND ClientStatus = 0 " + "AND AreaCode = ? " + "AND (EntryDate BETWEEN ? AND ?) "); pstmt.setString(1, areaCode); pstmt.setString(2, fromDate); pstmt.setString(3, toDate); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } @Override public List<PromoDeals> getAllPromoDeals(String areaCode, double amount, Date from, Date to) { Connection conn = DBConnection.connect(); PreparedStatement pstmt = null; ResultSet rs = null; List<PromoDeals> promoList = new ArrayList<>(); String fromDate = CommonUtilities.convertDateFormat(from.toString()); String toDate = CommonUtilities.convertDateFormat(to.toString()); try { pstmt = conn.prepareStatement( "SELECT * FROM promo_deals_view " + "WHERE PromoDealStatus - 0 " + "AND ClientStatus = 0 " + "AND AreaCode = ? " + "AND PromoAmount = ? " + "AND (EntryDate BETWEEN ? AND ?) "); pstmt.setString(1, areaCode); pstmt.setDouble(2, amount); pstmt.setString(3, fromDate); pstmt.setString(4, toDate); rs = pstmt.executeQuery(); while (rs.next()) { PromoDeals p = new PromoDeals(); p.setPromoId(CommonUtilities.convertStringToInt(rs.getString("PromoDealID"))); p.setClientName(rs.getString("ClientName")); p.setPromoItem(rs.getString("PromoItem")); p.setPromoAmount(CommonUtilities.convertStringToDouble(rs.getString("PromoAmount"))); p.setQuantity(CommonUtilities.convertStringToDouble(rs.getString("Quantity"))); p.setProductItem(rs.getString("ProductItem")); p.setStartDate(CommonUtilities.parsingDate(rs.getString("StartDate"))); p.setEndDate(CommonUtilities.parsingDate(rs.getString("EndDate"))); p.setRemarks(rs.getString("Remarks")); promoList.add(p); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } finally { try { if (conn != null || !conn.isClosed()) { pstmt.close(); rs.close(); conn.close(); } } catch (SQLException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } } return promoList; } }