public List<Area> getAllArea() {
List<Area> areaList = new ArrayList<Area>();
con = new DBConnection();
try {
cstmt = con.connection.prepareCall("{call getAllArea()}");
ResultSet rs = cstmt.executeQuery();
while (rs.next()) {
Area objArea = new Area();
objArea.setAreaID(rs.getInt("AreaID"));
objArea.setAreaName(rs.getString("AreaName"));
City c = new City();
c.setCityName(rs.getString("CityName"));
objArea.setCity(c);
areaList.add(objArea);
}
rs.close();
} catch (Exception ex) {
ex.getMessage();
} finally {
con.closeConnection();
}
return areaList;
}
// Reports
public ArrayList<Customer> getAllCustomers() {
con = new DBConnection();
ArrayList<Customer> customers = new ArrayList<Customer>();
try {
cstmt = con.connection.prepareCall("{call getAllCustomer()}");
ResultSet rs = cstmt.executeQuery();
while (rs.next()) {
Customer objCustomer = new Customer();
Area objArea = new Area();
objCustomer.setFirstName(rs.getString("FirstName"));
objCustomer.setLastName(rs.getString("LastName"));
objArea.setAreaName(rs.getString("AreaName"));
objCustomer.setArea(objArea);
objCustomer.setUserName(rs.getString("UserName"));
objCustomer.setLane(rs.getString("Lane"));
objCustomer.setMobileNo(rs.getString("MobileNo"));
objCustomer.setEmailID(rs.getString("EmailID"));
objCustomer.setStatus(rs.getBoolean("Status"));
customers.add(objCustomer);
}
rs.close();
} catch (Exception ex) {
ex.getMessage();
} finally {
con.closeConnection();
}
return customers;
}
public ArrayList<Area> getAllCityArea(int cityid) {
con = new DBConnection();
try {
cstmt = con.connection.prepareCall("{call getAreaCityID(?)}");
cstmt.setInt(1, cityid);
ArrayList<Area> areas = new ArrayList<Area>();
ResultSet rs = cstmt.executeQuery();
while (rs.next()) {
Area objArea = new Area();
objArea.setAreaID(rs.getInt("AreaID"));
objArea.setAreaName(rs.getString("AreaName"));
areas.add(objArea);
}
return areas;
} catch (SQLException ex) {
return null;
}
}
public ArrayList<Vendor> getAllVendors() {
con = new DBConnection();
ArrayList<Vendor> vendors = new ArrayList<Vendor>();
try {
cstmt = con.connection.prepareCall("{call getAllVendors ()}");
ResultSet rs = cstmt.executeQuery();
while (rs.next()) {
Vendor objVendor = new Vendor();
objVendor.setUserName(rs.getString("UserName"));
objVendor.setVendorName(rs.getString("VendorName"));
Area objArea = new Area(); // Creating area object
objArea.setAreaName(rs.getString("AreaName")); // setting areaname of vendor
City objCity = new City();
objCity.setCityName(rs.getString("CityName")); // setting cityname
objArea.setCity(objCity);
objVendor.setArea(objArea);
// setting all other fields
objVendor.setMobileNo(rs.getString("MobileNo"));
objVendor.setEmailID(rs.getString("EmailID"));
objVendor.setLane(rs.getString("Lane"));
objVendor.setOwnerName(rs.getString("OwnerName"));
objVendor.setLandlineNumber(rs.getString("LandlineNo"));
objVendor.setStatus(rs.getBoolean("Status"));
vendors.add(objVendor);
}
rs.close();
} catch (Exception ex) {
ex.getMessage();
} finally {
con.closeConnection();
}
return vendors;
}
public List<Customer> viewCustomerProfile(String user_name) {
// Author: Vivek Shukla
// Date: 14-October-2013
// Description:
List<Customer> customerList = new ArrayList<Customer>();
try {
cstmt = con.connection.prepareCall("{call viewCustomerProfile(?)}");
cstmt.setString(1, user_name);
ResultSet rs = cstmt.executeQuery();
if (rs.next()) {
Customer objCustomer = new Customer();
objCustomer.setUserName(rs.getString("UserName"));
objCustomer.setPassword(rs.getString("Password"));
objCustomer.setFirstName(rs.getString("FirstName"));
objCustomer.setLane(rs.getString("Lane"));
objCustomer.setLastName(rs.getString("LastName"));
Area objArea = new Area();
objArea.setAreaName(rs.getString("AreaName"));
objCustomer.setArea(objArea);
objCustomer.setMobileNo(rs.getString("MobileNo"));
objCustomer.setEmailID(rs.getString("EmailID"));
customerList.add(objCustomer);
} else {
return null;
}
} catch (Exception ex) {
ex.getMessage();
} finally {
con.closeConnection();
}
return customerList;
}
public Area getArea(int area_id) {
Area objArea = null;
con = new DBConnection();
try {
cstmt = con.connection.prepareCall("{call getArea(?)}");
cstmt.setInt(1, area_id);
ResultSet rs = cstmt.executeQuery();
if (rs.next()) {
objArea = new Area();
objArea.setAreaName(rs.getString("AreaName"));
objArea.setAreaID(rs.getInt("AreaID"));
City ct = new City();
// ct.setCityID(rs.getInt("CityID"));
ct.setCityName(rs.getString("CityName"));
objArea.setCity(ct);
}
rs.close();
} catch (Exception ex) {
ex.getMessage();
} finally {
con.closeConnection();
}
return objArea;
}