else if (sortmem.equals("EndTime"))
sortmem = "End";
boolean allRecords = true;
CVDal cvdl = new CVDal(dataSource);
CVUtility.getAllAccessibleRecords("Time Slips", "timeslipaccess", "timeslip", "TimeSlipID",
"CreatedBy", null, userID, cvdl);
Collection col = null;
if (searchString != null && searchString.startsWith("ADVANCE:")) {
searchString = searchString.substring(8);
String str = "create TEMPORARY TABLE timeslipsearch " + searchString;
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery(str);
cvdl.executeUpdate();
cvdl.clearParameters();
str = "select ts.TimeSlipID, ts.ProjectID, project.ProjectTitle, ts.Description, "
+ "ts.CreatedBy, individual.FirstName, individual.LastName, "
+ "ts.activityid, ts.BreakTime, ts.Date, ts.Start, ts.End, activity.title "
+ "from timeslip ts, timeslipsearch, timeslipaccess tla "
+ "left outer join project on ts.ProjectID = project.ProjectID "
+ "left outer join individual on ts.CreatedBy = individual.IndividualID "
+ "left outer join task on ts.activityID = task.activityID "
+ "left outer join activity on task.activityid = activity.activityid "
+ "WHERE timeslipsearch.timeslipid = ts.timeslipid and tla.TimeSlipID=ts.TimeSlipID";
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery(str);
col = cvdl.executeQuery();
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE timeslipsearch");
cvdl.executeUpdate();
allRecords = false;
} else {
String appendStr = "";
if (searchString.startsWith("SIMPLE :")) {
searchString = searchString.substring(8);
appendStr = " and (ts.TimeSlipID like '%" + searchString + "%' "
+ "OR ts.ProjectID like '%" + searchString + "%' "
+ "OR project.ProjectTitle like '%" + searchString + "%' "
+ "OR ts.Description like '%" + searchString + "%' " + "OR ts.CreatedBy like '%"
+ searchString + "%' " + "OR individual.FirstName like '%" + searchString + "%' "
+ "OR individual.LastName like '%" + searchString + "%' "
+ "OR ts.activityid like '%" + searchString + "%' " + "OR ts.BreakTime like '%"
+ searchString + "%' " + "OR ts.Date like '%" + searchString + "%' "
+ "OR ts.End like '%" + searchString + "%' " + "OR activity.title like '%"
+ searchString + "%' " + "OR ts.Start like '%" + searchString + "%' ) ";
allRecords = false;
}
if (sorttype == 'A') {
String str = "select ts.TimeSlipID, ts.ProjectID, project.ProjectTitle, ts.Description, ts.CreatedBy, individual.FirstName, individual.LastName, ts.activityid, ts.BreakTime, ts.Date, ts.Start, ts.End, activity.title from timeslipaccess tla, timeslip ts left outer join project on ts.ProjectID = project.ProjectID left outer join individual on ts.CreatedBy = individual.IndividualID left outer join task on ts.activityID = task.activityID left outer join activity on task.activityid = activity.activityid where tla.TimeSlipID=ts.TimeSlipID "
+ appendStr
+ " order by "
+ sortmem
+ " limit "
+ (beginindex - 1)
+ " , "
+ (endindex + 1) + ";";
cvdl.setSqlQuery(str);
} else {
String str = "select ts.TimeSlipID, ts.ProjectID, project.ProjectTitle, ts.Description, ts.CreatedBy, individual.FirstName, individual.LastName, ts.activityid, ts.BreakTime, ts.Date, ts.Start, ts.End, activity.title from timeslipaccess tla, timeslip ts left outer join project on ts.ProjectID = project.ProjectID left outer join individual on ts.CreatedBy = individual.IndividualID left outer join task on ts.activityID = task.activityID left outer join activity on task.activityid = activity.activityid where tla.TimeSlipID=ts.TimeSlipID "
+ appendStr
+ " order by "
+ sortmem
+ " desc limit "
+ (beginindex - 1)
+ " , "
+ (endindex + 1) + ";";
cvdl.setSqlQuery(str);
}
col = cvdl.executeQuery();
}
cvdl.setSqlQueryToNull();
cvdl.clearParameters();
cvdl.setSqlQuery("drop table timeslipaccess");
cvdl.executeUpdate();
ModuleFieldRightMatrix mfrmx = CVUtility.getUserModuleRight("Time Slip", userID, false,
this.dataSource);
Iterator it = col.iterator();
int i = 0;
while (it.hasNext()) {
i++;
HashMap hm = (HashMap)it.next();
int timeSlipID = ((Long)hm.get("TimeSlipID")).intValue();
int projectID = 0;
if (hm.get("ProjectID") != null)
projectID = ((Long)hm.get("ProjectID")).intValue();
StringMember description, projectName = null, taskName = null, createdBy = null;
PureDateMember date = null;
PureTimeMember startTime = null, endTime = null;
IntMember creator, taskId;
IntMember intmem = new IntMember("ID", timeSlipID, 10, "URL", 'T', true, 10);
IntMember projectId = new IntMember("ProjectID", projectID, 10, "URL", 'T', true, 10);
Time tStartTime = (Time)hm.get("Start");
Time tEndTime = (Time)hm.get("End");
java.util.Date dt = (java.util.Date)hm.get("Date");
Calendar calendarStart = Calendar.getInstance();
calendarStart.set(dt.getYear(), dt.getMonth(), dt.getDay(), tStartTime.getHours(),
tStartTime.getMinutes());
Calendar calendarEnd = Calendar.getInstance();
calendarEnd.set(dt.getYear(), dt.getMonth(), dt.getDay(), tEndTime.getHours(), tEndTime
.getMinutes());
long startMili = calendarStart.getTimeInMillis();
long endMili = calendarEnd.getTimeInMillis();
long diff = endMili - startMili;
Time difTime = new Time(diff);
int startHrs = calendarStart.get(Calendar.HOUR_OF_DAY);// startHrsmm[0];
int startMins = calendarStart.get(Calendar.MINUTE);// startHrsmm[1];
int endHrs = calendarEnd.get(Calendar.HOUR_OF_DAY);// endHrsmm[0];
int endMins = calendarEnd.get(Calendar.MINUTE);// endHrsmm[1];
float Duration = 0;
Number dBreakTime = (Number)hm.get("BreakTime");
float BreakTime = dBreakTime.floatValue();
float remMin = 0;
if (startMins != 0 && endMins != 0 && (startMins - endMins != 0)) {
if (endMins > startMins)
remMin = 60 / (endMins - startMins);
else
remMin = 60 / (startMins - endMins);
}
Duration = (endHrs - startHrs) + remMin - BreakTime;
if (hm.get("Description") != null)
description = new StringMember("Description", (String)hm.get("Description"), 10, "", 'T',
false);
else
description = new StringMember("Description", "", 10, "", 'T', false);
if (hm.get("ProjectTitle") != null)
projectName = new StringMember("Project", (String)hm.get("ProjectTitle"), mfrmx
.getFieldRight("Time Slip", "project"), "URL", 'T', true);
else
projectName = new StringMember("Project", "",
mfrmx.getFieldRight("Time Slip", "project"), "URL", 'T', true);
if (hm.get("title") != null)
taskName = new StringMember("Task", (String)hm.get("title"), mfrmx.getFieldRight(
"Time Slip", "task"), "URL", 'T', true);
else
taskName = new StringMember("Task", "", mfrmx.getFieldRight("Time Slip", "task"), "URL",
'T', true);
if (hm.get("activityid") != null)
taskId = new IntMember("TaskID", ((Integer)hm.get("activityid")).intValue(), 10, "URL",
'T', true, 10);
else
taskId = new IntMember("TaskID", 0, 10, "URL", 'T', true, 10);
String firstName = (String)hm.get("FirstName");
String lastName = (String)hm.get("LastName");
String created = (firstName != null ? firstName + " " : "")
+ (lastName != null ? lastName : "");
if (!created.equals(""))
createdBy = new StringMember("CreatedBy", created, 10, "", 'T', true);
else
createdBy = new StringMember("CreatedBy", "", 10, "", 'T', true);
if (hm.get("CreatedBy") != null)
creator = new IntMember("Creator", ((Integer)hm.get("CreatedBy")).intValue(), 10, "URL",
'T', false, 10);
else
creator = new IntMember("Creator", 0, 10, "URL", 'T', false, 10);
if (hm.get("Date") != null)
date = new PureDateMember("Date", (java.util.Date)hm.get("Date"), 10, "URL", 'T', false,
100, "EST");
else
date = new PureDateMember("Date", null, 10, "URL", 'T', false, 100, "EST");
if (hm.get("Start") != null)
startTime = new PureTimeMember("StartTime", (java.util.Date)hm.get("Start"), 10, "URL",
'T', false, 100, "EST");
else
startTime = new PureTimeMember("StartTime", null, 10, "URL", 'T', false, 100, "EST");
if (hm.get("End") != null)
endTime = new PureTimeMember("EndTime", (java.util.Date)hm.get("End"), 10, "URL", 'T',
false, 100, "EST");
else
endTime = new PureTimeMember("EndTime", null, 10, "URL", 'T', false, 100, "EST");
TimeSlipListElement ele = new TimeSlipListElement(timeSlipID);
ele.put("ID", intmem);
ele.put("TaskID", taskId);
ele.put("Description", description);
ele.put("Project", projectName);
ele.put("ProjectID", projectId);
ele.put("Task", taskName);
if (Duration > 1.0) {
StringMember duration = new StringMember("Duration", Float.toString(Duration) + " hours",
10, "URL", 'T', false);
ele.put("Duration", duration);
} else if (Duration == 1.0) {
StringMember duration = new StringMember("Duration", Float.toString(Duration) + " hour",
10, "URL", 'T', false);
ele.put("Duration", duration);
} else if (Duration > 0.0 && Duration < 1.0) {
StringMember duration = new StringMember("Duration", Float.toString(Duration * 60)
+ " minutes", 10, "URL", 'T', false);
ele.put("Duration", duration);
} else {
StringMember duration = new StringMember("Duration", "", 10, "URL", 'T', false);
ele.put("Duration", duration);
}
ele.put("CreatedBy", createdBy);
ele.put("Creator", creator);
ele.put("Date", date);
ele.put("StartTime", startTime);
ele.put("EndTime", endTime);
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();
if (!allRecords) {
timeSlipList.setTotalNoOfRecords(timeSlipList.size());
} else {
int count = 0;
cvdl.setSql("project.selecttimeslipcount");
Collection col2 = cvdl.executeQuery();
Iterator ite2 = col2.iterator();
if (ite2.hasNext()) {
HashMap hm2 = (HashMap)ite2.next();
count = ((Integer)hm2.get("count(timeslipid)")).intValue();
}
timeSlipList.setTotalNoOfRecords(count);
}
timeSlipList.put(newOrd, ele);
timeSlipList.setListType("Timeslip");
timeSlipList.setBeginIndex(beginindex);
timeSlipList.setEndIndex(endindex);
}
cvdl.clearParameters();
cvdl.destroy();
} catch (Exception e) {
e.printStackTrace();
}
return timeSlipList;