public BranchTO[] getAllBranchByCountry(String countryId) {
PreparedStatement pstmt = null;
Connection con = null;
ResultSet rs = null;
BranchTO branchTO = null;
ArrayList<BranchTO> branchList = new ArrayList<BranchTO>();
try {
con = DAOFactory.getInstance().getConnection(Connection.TRANSACTION_READ_UNCOMMITTED);
pstmt = con.prepareStatement(SQLConstants.LIST_BRANCH_BY_COUNTRY_SQL);
pstmt.setInt(1, Integer.parseInt(countryId));
rs = pstmt.executeQuery();
while (rs.next()) {
branchTO = new BranchTO();
branchTO.setId(rs.getString("id"));
branchTO.setCode(Utility.trim(rs.getString("code")));
branchTO.setName(Utility.trim(rs.getString("name")));
branchTO.setStatus(Utility.trim(rs.getString("status")));
branchList.add(branchTO);
}
return (BranchTO[]) branchList.toArray(new BranchTO[0]);
} catch (SQLException e) {
throw new DataException(e.getMessage());
} catch (NamingException e) {
throw new DataException(e.getMessage());
} finally {
Utility.closeAll(null, pstmt, con);
}
}
public boolean updateBranch(BranchTO branchTO) {
PreparedStatement pstmt = null;
Connection con = null;
int result;
try {
con = DAOFactory.getInstance().getConnection(Connection.TRANSACTION_READ_UNCOMMITTED);
pstmt = con.prepareStatement(SQLConstants.UPDATE_BRANCH_SQL);
pstmt.setString(1, Utility.trim(branchTO.getName()));
pstmt.setString(2, Utility.trim(branchTO.getCountry().getId()));
pstmt.setString(3, Utility.trim(branchTO.getStatus()));
pstmt.setInt(4, 1);
pstmt.setString(5, Utility.trim(branchTO.getId()));
result = pstmt.executeUpdate();
if (result > 0) {
return true;
} else {
return false;
}
} catch (SQLException e) {
throw new DataException(e.getMessage());
} catch (NamingException e) {
throw new DataException(e.getMessage());
} finally {
Utility.closeAll(null, pstmt, con);
}
}
public void updateAdmBranch(AdmBranchTO admBranchTO) {
PreparedStatement pstmt = null;
Connection con = null;
try {
con = DAOFactory.getInstance().getConnection(Connection.TRANSACTION_READ_UNCOMMITTED);
pstmt = con.prepareStatement(SQLConstants.UPDATE_ADM_BRANCH_SQL);
pstmt.setString(1, Utility.trim(admBranchTO.getStatus()));
pstmt.setInt(2, 1);
pstmt.setString(3, Utility.trim(admBranchTO.getBranch().getId()));
pstmt.executeUpdate();
} catch (SQLException e) {
throw new DataException(e.getMessage());
} catch (NamingException e) {
throw new DataException(e.getMessage());
} finally {
Utility.closeAll(null, pstmt, con);
}
}
public AdmBranchTO[] listAdmBranch(AdmTO admTO) {
PreparedStatement pstmt = null;
Connection con = null;
ResultSet rs = null;
AdmBranchTO admBranchTO = null;
ArrayList<AdmBranchTO> admBranchList = new ArrayList<AdmBranchTO>();
try {
con = DAOFactory.getInstance().getConnection(Connection.TRANSACTION_READ_UNCOMMITTED);
pstmt = con.prepareStatement(SQLConstants.LIST_ADMIN_BRANCH_SQL);
pstmt.setString(1, Utility.trim(admTO.getId()));
rs = pstmt.executeQuery();
while (rs.next()) {
BranchTO branchTO = new BranchTO();
admBranchTO = new AdmBranchTO();
admBranchTO.setAdm(admTO);
admBranchTO.setId(rs.getString("id"));
admBranchTO.setStatus(Utility.trim(rs.getString("status")));
admBranchTO.setBranch(branchTO);
admBranchTO.getBranch().setId(Utility.trim(rs.getString("branch_id")));
admBranchTO.getBranch().setCode(Utility.trim(rs.getString("code")));
admBranchTO.getBranch().setName(Utility.trim(rs.getString("name")));
admBranchList.add(admBranchTO);
}
return (AdmBranchTO[]) admBranchList.toArray(new AdmBranchTO[0]);
} catch (SQLException e) {
throw new DataException(e.getMessage());
} catch (NamingException e) {
throw new DataException(e.getMessage());
} finally {
Utility.closeAll(null, pstmt, con);
}
}
public void insertBranch(BranchTO branchTO) {
PreparedStatement pstmt = null;
Connection con = null;
try {
con = DAOFactory.getInstance().getConnection(Connection.TRANSACTION_READ_UNCOMMITTED);
pstmt = con.prepareStatement(SQLConstants.INSERT_BRANCH_SQL);
pstmt.setString(1, Utility.trim(branchTO.getCode()));
pstmt.setString(2, Utility.trim(branchTO.getName()));
pstmt.setString(3, Utility.trim(branchTO.getCountry().getId()));
pstmt.setString(4, Utility.trim(branchTO.getStatus()));
pstmt.setInt(5, 1); // createdby
pstmt.executeUpdate();
} catch (SQLException e) {
throw new DataException(e.getMessage());
} catch (NamingException e) {
throw new DataException(e.getMessage());
} finally {
Utility.closeAll(null, pstmt, con);
}
}
public BranchTO loadBranch(String searchQuery, String key) {
PreparedStatement pstmt = null;
Connection con = null;
ResultSet rs = null;
BranchTO branchTO = null;
String completeQueryStmt = SQLConstants.GET_BRANCH_SQL + searchQuery;
try {
con = DAOFactory.getInstance().getConnection(Connection.TRANSACTION_READ_UNCOMMITTED);
pstmt = con.prepareStatement(completeQueryStmt);
pstmt.setString(1, Utility.trim(key));
rs = pstmt.executeQuery();
if (rs.next()) {
branchTO = new BranchTO();
branchTO.setId(rs.getString("id"));
branchTO.setCode(Utility.trim(rs.getString("code")));
branchTO.setName(Utility.trim(rs.getString("name")));
branchTO.setStatus(Utility.trim(rs.getString("status")));
CountryTO countryTO = new CountryTO();
branchTO.setCountry(countryTO);
branchTO.getCountry().setId(rs.getString("country_id"));
branchTO.getCountry().setCode(rs.getString("ctry_code"));
branchTO.getCountry().setName(rs.getString("ctry_name"));
}
return branchTO;
} catch (SQLException e) {
throw new DataException(e.getMessage());
} catch (NamingException e) {
throw new DataException(e.getMessage());
} finally {
Utility.closeAll(null, pstmt, con);
}
}
public BranchTO[] searchBranch(String searchQuery, String[] parameterList) {
PreparedStatement pstmt = null;
Connection con = null;
ResultSet rs = null;
BranchTO branchTO = null;
ArrayList<BranchTO> branchList = new ArrayList<BranchTO>();
String completeQueryStmt = SQLConstants.GET_BRANCH_SQL + searchQuery;
try {
con = DAOFactory.getInstance().getConnection(Connection.TRANSACTION_READ_UNCOMMITTED);
pstmt = con.prepareStatement(completeQueryStmt);
for (int i = 0; i < parameterList.length; i++) {
pstmt.setString(i + 1, Utility.trim("%" + parameterList[i] + "%"));
}
rs = pstmt.executeQuery();
while (rs.next()) {
branchTO = new BranchTO();
branchTO.setId(rs.getString("id"));
branchTO.setCode(Utility.trim(rs.getString("code")));
branchTO.setName(Utility.trim(rs.getString("name")));
branchTO.setStatus(Utility.trim(rs.getString("status")));
CountryTO countryTO = new CountryTO();
branchTO.setCountry(countryTO);
branchTO.getCountry().setId(rs.getString("country_id"));
branchTO.getCountry().setCode(Utility.trim(rs.getString("ctry_code")));
branchTO.getCountry().setName(Utility.trim(rs.getString("ctry_name")));
branchList.add(branchTO);
}
return (BranchTO[]) branchList.toArray(new BranchTO[0]);
} catch (SQLException e) {
throw new DataException(e.getMessage());
} catch (NamingException e) {
throw new DataException(e.getMessage());
} finally {
Utility.closeAll(null, pstmt, con);
}
}