Integer month = (Integer)pars[1];
String companyCode = (String)pars[2];
CallOutTypeVO callOutType = (CallOutTypeVO)pars[3];
conn = ConnectionManager.getConnection(null);
CallOutRequestReportVO vo = null;
CallOutRequestReportVO[] openedRows = null;
CallOutRequestReportVO[] closedRows = null;
Calendar cal = Calendar.getInstance();
java.sql.Date startDate = null;
java.sql.Date endDate = null;
if (month==null) {
// analysys of a whole year, month by month...
String sql =
"select count(*) "+
"from SCH03_CALL_OUT_REQUESTS,SCH10_CALL_OUTS where "+
"SCH03_CALL_OUT_REQUESTS.COMPANY_CODE_SYS01=SCH10_CALL_OUTS.COMPANY_CODE_SYS01 and "+
"SCH03_CALL_OUT_REQUESTS.CALL_OUT_CODE_SCH10=SCH10_CALL_OUTS.CALL_OUT_CODE and "+
"SCH03_CALL_OUT_REQUESTS.COMPANY_CODE_SYS01=? and "+
"SCH10_CALL_OUTS.PROGRESSIVE_HIE02=? and "+
"SCH03_CALL_OUT_REQUESTS.CALL_OUT_STATE=? and "+
"SCH03_CALL_OUT_REQUESTS.REQUEST_DATE>=? and SCH03_CALL_OUT_REQUESTS.REQUEST_DATE<? ";
pstmt = conn.prepareStatement(sql);
openedRows = new CallOutRequestReportVO[12];
for(int i=0;i<=11;i++) {
cal.set(cal.YEAR,year.intValue());
cal.set(cal.MONTH,i);
cal.set(cal.DAY_OF_MONTH,1);
cal.set(cal.HOUR_OF_DAY,0);
cal.set(cal.MINUTE,0);
cal.set(cal.SECOND,0);
cal.set(cal.MILLISECOND,0);
startDate = new java.sql.Date(cal.getTimeInMillis());
if (i!=11)
cal.set(cal.MONTH,i+1);
else {
cal.set(cal.YEAR,year.intValue()+1);
cal.set(cal.MONTH,0);
}
endDate = new java.sql.Date(cal.getTimeInMillis());
pstmt.setString(1,companyCode);
pstmt.setBigDecimal(2,callOutType.getProgressiveHie02SCH11());
pstmt.setString(3,ApplicationConsts.OPENED);
pstmt.setDate(4,startDate);
pstmt.setDate(5,endDate);
rset = pstmt.executeQuery();
vo = new CallOutRequestReportVO();
vo.setName(String.valueOf(i+1));
if (rset.next()) {
vo.setValue(rset.getBigDecimal(1));
}
openedRows[i] = vo;
rset.close();
}
closedRows = new CallOutRequestReportVO[12];
for(int i=0;i<=11;i++) {
cal.set(cal.YEAR,year.intValue());
cal.set(cal.MONTH,i);
cal.set(cal.DAY_OF_MONTH,1);
cal.set(cal.HOUR_OF_DAY,0);
cal.set(cal.MINUTE,0);
cal.set(cal.SECOND,0);
cal.set(cal.MILLISECOND,0);
startDate = new java.sql.Date(cal.getTimeInMillis());
if (i!=11)
cal.set(cal.MONTH,i+1);
else {
cal.set(cal.YEAR,year.intValue()+1);
cal.set(cal.MONTH,0);
}
endDate = new java.sql.Date(cal.getTimeInMillis());
pstmt.setString(1,companyCode);
pstmt.setBigDecimal(2,callOutType.getProgressiveHie02SCH11());
pstmt.setString(3,ApplicationConsts.CLOSED);
pstmt.setDate(4,startDate);
pstmt.setDate(5,endDate);
rset = pstmt.executeQuery();
vo = new CallOutRequestReportVO();
vo.setName(String.valueOf(i+1));
if (rset.next()) {
vo.setValue(rset.getBigDecimal(1));
}
closedRows[i] = vo;
rset.close();
}
pstmt.close();
}
else {
// analysys of a specific month...
String sql =
"select COUNT(*),SCH03_CALL_OUT_REQUESTS.REQUEST_DATE "+
"from SCH03_CALL_OUT_REQUESTS,SCH10_CALL_OUTS where "+
"SCH03_CALL_OUT_REQUESTS.COMPANY_CODE_SYS01=SCH10_CALL_OUTS.COMPANY_CODE_SYS01 and "+
"SCH03_CALL_OUT_REQUESTS.CALL_OUT_CODE_SCH10=SCH10_CALL_OUTS.CALL_OUT_CODE and "+
"SCH03_CALL_OUT_REQUESTS.COMPANY_CODE_SYS01=? and "+
"SCH10_CALL_OUTS.PROGRESSIVE_HIE02=? and "+
"SCH03_CALL_OUT_REQUESTS.CALL_OUT_STATE=? and "+
"SCH03_CALL_OUT_REQUESTS.REQUEST_DATE>=? and SCH03_CALL_OUT_REQUESTS.REQUEST_DATE<? "+
"GROUP BY SCH03_CALL_OUT_REQUESTS.REQUEST_DATE "+
"ORDER BY SCH03_CALL_OUT_REQUESTS.REQUEST_DATE ";
pstmt = conn.prepareStatement(sql);
cal.set(cal.YEAR,year.intValue());
cal.set(cal.MONTH,month.intValue());
cal.set(cal.DAY_OF_MONTH,1);
cal.set(cal.HOUR_OF_DAY,0);
cal.set(cal.MINUTE,0);
cal.set(cal.SECOND,0);
cal.set(cal.MILLISECOND,0);
startDate = new java.sql.Date(cal.getTimeInMillis());
if (month.intValue()!=11)
cal.set(cal.MONTH,month.intValue()+1);
else {
cal.set(cal.YEAR,year.intValue()+1);
cal.set(cal.MONTH,0);
}
endDate = new java.sql.Date(cal.getTimeInMillis());
int nrOfDays = 31;
if (month.intValue()==1)
nrOfDays = year.intValue()%4==0?29:28;
else if (month.intValue()==10 || month.intValue()==3 || month.intValue()==5 || month.intValue()==8)
nrOfDays = 30;
pstmt.setString(1,companyCode);
pstmt.setBigDecimal(2,callOutType.getProgressiveHie02SCH11());
pstmt.setString(3,ApplicationConsts.OPENED);
pstmt.setDate(4,startDate);
pstmt.setDate(5,endDate);
rset = pstmt.executeQuery();
openedRows = new CallOutRequestReportVO[nrOfDays];
for(int i=1;i<=nrOfDays;i++) {
vo = new CallOutRequestReportVO();
vo.setName(String.valueOf(i));
openedRows[i-1] = vo;
}
int day = -1;
while(rset.next()) {
cal.setTime(rset.getDate(2));
day = cal.get(cal.DAY_OF_MONTH);
openedRows[day-1].setValue(rset.getBigDecimal(1));
}
rset.close();
pstmt.setString(1,companyCode);
pstmt.setBigDecimal(2,callOutType.getProgressiveHie02SCH11());
pstmt.setString(3,ApplicationConsts.CLOSED);
pstmt.setDate(4,startDate);
pstmt.setDate(5,endDate);
rset = pstmt.executeQuery();
closedRows = new CallOutRequestReportVO[nrOfDays];
for(int i=1;i<=nrOfDays;i++) {
vo = new CallOutRequestReportVO();
vo.setName(String.valueOf(i));
closedRows[i-1] = vo;
}
day = -1;
while(rset.next()) {
cal.setTime(rset.getDate(2));