// insert the data that has been validated
private void updateTracktingTicketData(List<UserTicketTrackingData> userdatas)
throws SQLException {
for (UserTicketTrackingData userdata : userdatas) {
String user_sid = trackingTicketScriptDao.finduser_id(userdata.getUserId());
String user_uid_timesheetid = userdata.getTimesheetId();
List<TaskData> user_taskdata = userdata.getTaskdatas();
for (TaskData taskdata : user_taskdata) {
String user_task_assignmentId = taskdata.assignmentid;
List<TicketInfo> tickets = taskdata.ticketinfos;
List<TicketLog> ticketLogs = taskdata.ticketLogs;
updateTicketsForOneUser(
user_sid, user_task_assignmentId, user_uid_timesheetid, tickets, ticketLogs);
}
}
}
// read the data from the excel
public List readFromXML(InputStream input) throws Exception {
List<TeamRecord> teamRecord = new ArrayList<TeamRecord>();
List<String> wrong_data = new ArrayList<String>();
List rt_list = new ArrayList<List>();
HashSet<String> wrong_users = new HashSet<String>();
// String log="Start read Excel";
// System.out.println(log);
// all_log.add(log);
try {
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
boolean vali_date = true;
boolean firstLine = true;
setRmLoginNames(trackingTicketScriptDao.findrmLoginNames());
for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext(); ) {
Row row = rit.next();
if (firstLine) {
firstLine = false;
} else {
vali_date = validation.validate_exceldata(row, wrong_users, wrong_data);
if (vali_date == true) {
teamRecord.add(readexcelline(row));
}
}
rt_list.add(teamRecord);
rt_list.add(wrong_data);
rt_list.add(wrong_users);
}
} catch (IOException ex) {
wrong_data.add("Excel is NULL,Please check Excel!");
rt_list.add(teamRecord);
rt_list.add(wrong_data);
rt_list.add(wrong_users);
return rt_list;
}
return rt_list;
}
// According to userid and assignmentid, pasrse the teamrecord data group by user
public List<UserTicketTrackingData> parseUserData(List<TaskData> taskdatas) throws SQLException {
Map<String, UserTicketTrackingData> userid_map = new HashMap<String, UserTicketTrackingData>();
for (TaskData user_assignmetid_taskData : taskdatas) {
String userid = user_assignmetid_taskData.ticketinfos.get(0).assignee;
String timesheetid = trackingTicketScriptDao.findtimeSheetId(userid, startDate, endDate);
String rmlogin = user_assignmetid_taskData.getRmlogin();
if (!userid_map.containsKey(userid)) {
UserTicketTrackingData userdata = new UserTicketTrackingData();
userdata.setUserId(userid);
userdata.setTimesheetId(timesheetid);
userdata.setRmlogin(rmlogin);
// userdata.taskdatas.add(user_assignmetid_taskData);
userid_map.put(userid, userdata);
}
UserTicketTrackingData userdata = userid_map.get(userid);
userdata.getTaskdatas().add(user_assignmetid_taskData);
}
List<UserTicketTrackingData> list = new ArrayList<UserTicketTrackingData>(userid_map.values());
return list;
}
// According to userid, pasrse the teamrecord data group by user and assignmentid
public List<TaskData> parseTaskData(List<TeamRecord> records) throws SQLException {
Map<String, TaskData> ticket_map = new HashMap<String, TaskData>();
for (TeamRecord rcd : records) {
// If this line's rmlogin is not in the database, we need to delete this line
if (!rmLoginNames.contains(rcd.getRmlogin())) {
// need to add a log
continue;
}
// to find userid according rmlogin, rmlogin is the remedy's name
// useridtd is String array, useridtd[0] is userId, useridtd[1] is
// timesheetid, useridtd[2] is staffid
String[] strs = trackingTicketScriptDao.findUserId(rcd.getRmlogin(), startDate, endDate);
String uid = strs[0];
String tid = strs[1];
String sid = strs[2];
String assignmentid = trackingTicketScriptDao.findassignmentId(rcd.getTask());
// distinguish the data by assignmentid and id, to confirm each task's ticket_id
if (!ticket_map.containsKey(assignmentid + ":" + uid)) {
TaskData taskdata = new TaskData();
taskdata.setAssignmentid(assignmentid);
ticket_map.put(assignmentid + ":" + uid, taskdata);
}
TaskData taskdata = ticket_map.get(assignmentid + ":" + uid);
taskdata.setRmlogin(rcd.getRmlogin());
TicketInfo ticketinfo = new TicketInfo();
ticketinfo.setDescription(rcd.getTask());
ticketinfo.setAssignee(uid);
ticketinfo.setTicket_id(rcd.getTicketNumber());
String ticket_id =
new Integer(trackingTicketScriptDao.getticket_id(rcd.getTicketNumber())).toString();
// gain the TicketInfo data information from excel
ticketinfo.setId(ticket_id);
ticketinfo.setFunctionPoint(rcd.getFp());
ticketinfo.setEstimation(rcd.getEst());
ticketinfo.setProgress(rcd.getProgress());
ticketinfo.setInternalCommentsCount(rcd.getInternal_Comments());
ticketinfo.setWmCommentsCount(rcd.getWm_Comments());
ticketinfo.setInternalReviewEffort(rcd.getInternal_Review_Effort());
ticketinfo.setInternalReworkEffort(rcd.getInternal_Rework_Effort());
ticketinfo.setExternalReworkEffort(rcd.getExternal_Rework_Effort());
ticketinfo.setTransactionCount(rcd.getTransaction());
ticketinfo.setUpdateBy(sid);
// gain the TicketLog data information from excel
TicketLog ticketLog = new TicketLog();
ticketLog.setAssignmentId(trackingTicketScriptDao.findassignmentId(rcd.getTask()));
ticketLog.setHourSpends(rcd.getDays());
ticketLog.setTimesheetId(tid);
ticketLog.setAssigneeId(sid);
ticketLog.setTicketId(ticket_id);
ticketLog.setComment(rcd.getComment());
taskdata.ticketinfos.add(ticketinfo);
taskdata.ticketLogs.add(ticketLog);
taskdata.setWrong_log(rcd.getWrong_log());
}
List<TaskData> list = new ArrayList<TaskData>(ticket_map.values());
return list;
}