I am writing an excel report using an action,controller, servlet struts framework. The report is very congested and has already around 10 separate queries. I need to add about 10 more queries due to a change in the report. Is there a way to do this without having to keep nesting the Result Sets? Below is the existing code.
public TotalSalesKPIContainer totalSalesKPIQuery(String franchiseId, java.util.Date startDate, java.util.Date stopDate) {
String getFranIds = null;
ArrayList storeResultList = new ArrayList();
ArrayList salesPersonResultList = new ArrayList();
TotalSalesKPIContainer result = null;
Connection conn = null;
PreparedStatement pstmt1, pstmtLY, pstmtCountLY, pstmtTCount, pstmtTCountLY, pstmtCount = null;
PreparedStatement pstmt, ps = null;
ResultSet rs1, rsLY, rsCountLY, rsTCount, rsTCountLY, rsCount = null;
ResultSet rs = null, rsSP = null, rsBySP, rsTCount2, rsTCountLY2 = null, rsBySPLY, rsCountLY2, rsCount2;
java.util.Date prevStartDate;
java.util.Date prevStopDate;
try {
conn = getConnection();
if (franchiseId.equals("0")) {
getFranIds = "Select id, name from vymac.organization";
pstmt = conn.prepareStatement(getFranIds);
} else {
getFranIds = "Select id, name from vymac.organization where id = ? ";
pstmt = conn.prepareStatement(getFranIds);
pstmt.setString(1, franchiseId);
}
int franID = Integer.parseInt(franchiseId);
rs = pstmt.executeQuery();
while (rs.next()) {
result = new TotalSalesKPIContainer(rs.getString(2), franID,startDate, stopDate, storeResultList,salesPersonResultList);
/*
* This section is for Salesperson total sales
*/
pstmt1 = conn.prepareStatement(getTotalSalesBySP);
pstmt1.setInt(1, rs.getInt(1));
pstmt1.setDate(2, new java.sql.Date(startDate.getTime()));
pstmt1.setDate(3, new java.sql.Date(stopDate.getTime()));
rsBySP = pstmt1.executeQuery();
while (rsBySP.next()) {
ps = conn.prepareStatement(getSalesPersons);
ps.setInt(1, rsBySP.getInt(1));
rsSP = ps.executeQuery();
if (rsSP.next()) {
ps = conn.prepareStatement(getPrevTotalSalesBySP);
// Get previous year date
Calendar cal = Calendar.getInstance();
cal.setTime(startDate);
cal.add(Calendar.YEAR, -1);
prevStartDate = cal.getTime();
cal.setTime(stopDate);
cal.add(Calendar.YEAR, -1);
prevStopDate = cal.getTime();
ps.setString(1, rsBySP.getString(1));
ps.setDate(2,new java.sql.Date(prevStartDate.getTime()));
ps.setDate(3, new java.sql.Date(prevStopDate.getTime()));
rsBySPLY = ps.executeQuery();
if (rsBySPLY.next()) {
// Count Invoices for Last Year
ps = conn.prepareStatement(findCountBySP);
ps.setDate(1, new java.sql.Date(prevStartDate.getTime()));
ps.setDate(2, new java.sql.Date(prevStopDate.getTime()));
ps.setString(3, rsBySP.getString(1));
rsCountLY2 = ps.executeQuery();
if (rsCountLY2.next()) {
ps = conn.prepareStatement(findCountBySP);
ps.setDate(1, new java.sql.Date(startDate.getTime()));
ps.setDate(2, new java.sql.Date(stopDate.getTime()));
ps.setString(3, rsBySP.getString(1));
rsCount2 = ps.executeQuery();
if (rsCount2.next()) {
ps = conn.prepareStatement(trafficCountBySP);
ps.setDate(1, new java.sql.Date(startDate.getTime()));
ps.setDate(2, new java.sql.Date(stopDate.getTime()));
ps.setString(3, rsBySP.getString(1));
rsTCount2 = ps.executeQuery();
if (rsTCount2.next()) {
ps = conn.prepareStatement(trafficCountBySP);
ps.setDate(1, new java.sql.Date(prevStartDate.getTime()));
ps.setDate(2, new java.sql.Date(prevStopDate.getTime()));
ps.setString(3, rsBySP.getString(1));
rsTCountLY2 = ps.executeQuery();
if (rsTCountLY2.next()) {
TotalSalesInformationBySalesPerson tbsp = new TotalSalesInformationBySalesPerson(rsSP.getString(1), rs.getString(2), rs.getInt(1),rsCount2.getInt(1), rsBySP.getDouble(2),rsBySPLY.getDouble(2),rsCountLY2.getInt(1),
rsTCount2.getInt(1),rsTCountLY2.getInt(1));
salesPersonResultList.add(tbsp);
} else {
TotalSalesInformationBySalesPerson tbsp = new TotalSalesInformationBySalesPerson(
rsSP.getString(1), rs
.getString(2), rs
.getInt(1),
rsCount2.getInt(1), rsBySP
.getDouble(2),
rsBySPLY.getDouble(2),
rsCountLY2.getInt(1),
rsTCount2.getInt(1), 0);
salesPersonResultList.add(tbsp);
}
}
}
}
} else {
ps = conn.prepareStatement(findCountBySP);
ps.setDate(1,
new java.sql.Date(startDate.getTime()));
ps
.setDate(2, new java.sql.Date(stopDate
.getTime()));
ps.setString(3, rsBySP.getString(1));
rsCount2 = ps.executeQuery();
if (rsCount2.next()) {
ps = conn.prepareStatement(trafficCountBySP);
ps.setDate(1, new java.sql.Date(startDate
.getTime()));
ps.setDate(2, new java.sql.Date(stopDate
.getTime()));
ps.setString(3, rsBySP.getString(1));
rsTCount2 = ps.executeQuery();
if (rsTCount2.next()) {
TotalSalesInformationBySalesPerson tbsp = new TotalSalesInformationBySalesPerson(
rsSP.getString(1), rs.getString(2),
rs.getInt(1), rsCount2.getInt(1),
rsBySP.getDouble(2), 0.00, 0,
rsTCount2.getInt(1), 0);
salesPersonResultList.add(tbsp);
} else {
TotalSalesInformationBySalesPerson tbsp = new TotalSalesInformationBySalesPerson(
rsSP.getString(1), rs.getString(2),
rs.getInt(1), rsCount2.getInt(1),
rsBySP.getDouble(2), 0.00, 0,
rsTCount2.getInt(1), 0);
salesPersonResultList.add(tbsp);
}
}
}
}
}