*/
@SuppressWarnings("unchecked")
public Report getTopSellersForDates(java.util.Date startdate, java.util.Date enddate, int quantity,
ReportFormat reportFormat)
{
Report report = null;
Connection conn = null;
ResultSet results = null;
PreparedStatement sqlStatement = null;
try
{
// Establish connection to datasource.
String orderItemsTableName="ORDERITEM";
DataSource ds = (DataSource) Util.getInitialContext().lookup("jdbc/PlantsByWebSphereDataSource");
conn = ds.getConnection();
// Set sort order of ascending or descending.
String sortOrder;
if (reportFormat.isAscending())
sortOrder = "ASC";
else
sortOrder = "DESC";
// Set up where by clause.
String startDateString = Long.toString(startdate.getTime());
if (startDateString.length() < 14)
{
StringBuffer sb = new StringBuffer(Util.ZERO_14);
sb.replace((14 - startDateString.length()), 14, startDateString);
startDateString = sb.toString();
}
String endDateString = Long.toString(enddate.getTime());
if (endDateString.length() < 14)
{
StringBuffer sb = new StringBuffer(Util.ZERO_14);
sb.replace((14 - endDateString.length()), 14, endDateString);
endDateString = sb.toString();
}
String whereString = " WHERE sellDate BETWEEN '" + startDateString +
"' AND '" + endDateString + "' ";
// Create SQL statement.
String sqlString = "SELECT inventoryID, name, category," +
" SUM(quantity * (price - cost)) as PROFIT FROM " + orderItemsTableName +
whereString +
" GROUP BY inventoryID, name, category ORDER BY PROFIT " + sortOrder + ", name";
Util.debug("sqlstring=" + sqlString );
sqlStatement = conn.prepareStatement(sqlString);
results = sqlStatement.executeQuery();
int i;
// Initialize vectors to store data in.
Vector[] vecs = new Vector[4];
for (i = 0; i < vecs.length; i++)
{
vecs[i] = new Vector();
}
// Sift thru results.
int count = 0;
while ((results.next()) && (count < quantity))
{
count++;
i = 1;
vecs[0].addElement(results.getString(i++));
vecs[1].addElement(results.getString(i++));
vecs[2].addElement(new Integer(results.getInt(i++)));
vecs[3].addElement(new Float(results.getFloat(i++)));
}
// Create report.
report = new Report();
report.setReportFieldByRow(Report.ORDER_INVENTORY_ID, vecs[0]);
report.setReportFieldByRow(Report.ORDER_INVENTORY_NAME, vecs[1]);
report.setReportFieldByRow(Report.ORDER_INVENTORY_CATEGORY, vecs[2]);
report.setReportFieldByRow(Report.PROFITS, vecs[3]);
}
catch (Exception e)
{
Util.debug("exception in ReportGeneratorBean:getTopSellersForDates. "+e);