public com.centraview.common.TimeSheetList getTimeSheetList(int individualId, HashMap info) throws AuthorizationFailedException
{
if(!CVUtility.isModuleVisible("Time Sheets",individualId, this.dataSource))
throw new AuthorizationFailedException("Time Sheets - getTimeSheetList");
TimeSheetList timeSheetList = new TimeSheetList();
try
{
Integer startATparam = (Integer) info.get( "startATparam" ) ;
Integer EndAtparam = (Integer) info.get( "EndAtparam" ) ;
String searchString = (String) info.get( "searchString" ) ;
String sortmem = (String) info.get( "sortmem" ) ;
Character chr = (Character) info.get( "sortType" ) ;
char sorttype = chr.charValue();
int startat = startATparam.intValue();
int endat = EndAtparam.intValue();
int beginindex = Math.max( startat - 100, 1 ) ;
int endindex = endat + 100;
timeSheetList.setSortMember( sortmem );
boolean allRecords = true;
CVDal cvdl = new CVDal(dataSource);
if(sortmem.equals("Employee"))
sortmem="Name";
//else if(sortmem.equals("Entity"))
//sortmem="EntityName";
//else if(sortmem.equals("DueDate"))
//sortmem="End";
String appendStr = "";
Collection col = null;
cvdl.setSql("hr.createtimesheetlist");
//please check the query individualid shoud be of user logged in cvdl.set
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSql("hr.inserttimesheetlist");
cvdl.setInt(1,individualId);
cvdl.setInt(2,individualId);
cvdl.setInt(3,individualId);
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSql("hr.updatetimesheetlist1");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSql("hr.updatetimesheetlist2");
cvdl.executeUpdate();
cvdl.clearParameters();
String strquery = "create temporary table tempduration select timesheetID ID,sum(Hours) Hours from timeslip group by timesheetid";
cvdl.setSqlQuery(strquery);
cvdl.executeUpdate();
String strquery2 = "update timesheetlist,tempduration set Duration= Hours where timesheetlist.ID = tempduration.ID";
cvdl.setSqlQuery(strquery2);
cvdl.executeUpdate();
String strquery3 = "DROP TABLE tempduration";
cvdl.setSqlQuery(strquery3);
cvdl.executeUpdate();
if (searchString != null && searchString.startsWith("ADVANCE:"))
{
searchString = searchString.substring(8);
String str = "create TEMPORARY TABLE timesheetsearch "+searchString;
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery(str);
cvdl.executeUpdate();
cvdl.clearParameters();
str = "select timesheetlist.ID, EmpIndvidualID, Name, StartDate, EndDate, Duration, CreatedBy, Creator FROM timesheetlist, timesheetsearch WHERE timesheetlist.ID = timesheetsearch.TimeSheetID";
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery(str);
col = cvdl.executeQuery();
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE timesheetsearch");
cvdl.executeUpdate();
allRecords = false;
}
else
{
if (searchString.startsWith("SIMPLE :"))
{
searchString = searchString.substring(8);
appendStr = " WHERE (ID like '%"+searchString+"%' "
+ "OR Name like '%"+searchString+"%' "
+ "OR StartDate like '%"+searchString+"%' "
+ "OR EndDate like '%"+searchString+"%' "
+ "OR Duration like '%"+searchString+"%' "
+ "OR CreatedBy like '%"+searchString+"%') ";
allRecords = false;
}
String str = "SELECT * FROM timesheetlist";
if ( sorttype == 'A' )
{
str = str + appendStr + " order by "+ sortmem + " asc limit "+(beginindex-1) +" , "+ (endindex+1) +";" ;
cvdl.setSqlQuery( str );
}else
{
str = str + appendStr +" order by "+ sortmem + " desc limit "+(beginindex-1) +" , "+ (endindex+1) +";";
cvdl.setSqlQuery( str );
}
col = cvdl.executeQuery();
}
Iterator it = col.iterator();
int i=0 ;
while( it.hasNext() )
{
i++;
HashMap hm = ( HashMap )it.next();
int TimeSheetId = ((Long)hm.get("ID")).intValue();
String EmployeeName = (String)hm.get("Name");
java.util.Date StartDate = (java.util.Date)hm.get("StratDate");
java.util.Date EndDate = (java.util.Date)hm.get("EndDate");
float Duration = Float.parseFloat( hm.get("Duration").toString() );
String CreatedBy = (String)hm.get("CreatedBy");
IntMember intmem = new IntMember( "ID" , TimeSheetId , 10 , "", 'T' , true , 10 );
StringMember sname = new StringMember( "Employee", EmployeeName ,10 , "" , 'T' , true );
PureDateMember startdate= new PureDateMember( "StartDate" ,(java.util.Date)hm.get("StartDate"),10 , "URL" , 'T' , false ,100 ,"EST");
PureDateMember enddate = new PureDateMember( "EndDate" ,(java.util.Date)hm.get("EndDate"),10 , "URL" , 'T' , false ,100 ,"EST");
StringMember cname = new StringMember( "CreatedBy", CreatedBy ,10 , "" , 'T' , true );
TimeSheetListElement ele = new TimeSheetListElement(TimeSheetId );
ele.put( "ID", intmem );
ele.put( "EmployeeID", new IntMember( "EmployeeID" , ((Long)hm.get("EmpIndvidualID")).intValue() , 10 , "", 'T' , false , 10 ));
ele.put( "Employee", sname );
ele.put( "StartDate", startdate );
ele.put( "EndDate", enddate );
//FloatMember duration= new FloatMember( "Duration", new Float(Duration) ,10 , "" , 'T' , false , 10 );
StringMember duration = null;
if ( Duration > 1.0 )
{
duration = new StringMember( "Duration" , Float.toString(Duration)+ " hours" , 10 , "URL", 'T' , false );
}
else if ( Duration == 1.0 )
{
duration = new StringMember( "Duration" , Float.toString(Duration)+ " hour" , 10 , "URL", 'T' , false );
}
else if ( Duration > 0.0 && Duration < 1.0 )
{
duration = new StringMember( "Duration" , Float.toString(Duration*60)+ " minutes" , 10 , "URL", 'T' , false );
}
else
{
duration = new StringMember( "Duration" , "" , 10 , "URL", 'T' , false );
}
ele.put( "Duration", duration );
ele.put( "Creator", new IntMember( "Creator" , ((Long)hm.get("Creator")).intValue() , 10 , "", 'T' , false , 10 ));
ele.put( "CreatedBy", cname );
StringBuffer sb = new StringBuffer("00000000000");
sb.setLength(11);
String str = (new Integer(i)).toString();
sb.replace((sb.length()-str.length()),(sb.length()),str);
String newOrd = sb.toString();
cvdl.clearParameters();
timeSheetList.put(newOrd , ele );
}
if (!allRecords)
{
timeSheetList.setTotalNoOfRecords( timeSheetList.size() );
}
else
{
int count = 0;
cvdl.setSql("hr.selecttimesheetcount");
Collection col2 = cvdl.executeQuery();
Iterator ite2 = col2.iterator();
if (ite2.hasNext())
{
HashMap hm2 = (HashMap) ite2.next();
count = ((Integer)hm2.get("count(TimeSheetID)")).intValue();
}
timeSheetList.setTotalNoOfRecords( count );
}
timeSheetList.setListType( "TimeSheet" );
timeSheetList.setBeginIndex( beginindex );
timeSheetList.setEndIndex( endindex ) ;
cvdl.clearParameters();
//cvdl.setSql("hr.deletetimesheetlist");
cvdl.setSqlQuery("DROP TABLE timesheetlist");