package com.tcs.hrr.service.impl;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.tcs.hrr.dao.RequirementDAO;
import com.tcs.hrr.dao.RequirementStatusDAO;
import com.tcs.hrr.domain.Requirement;
import com.tcs.hrr.domain.RequirementStatus;
import com.tcs.hrr.service.RequirementManager;
import com.tcs.hrr.util.Constant;
public class RequirementManagerImpl implements RequirementManager {
private RequirementDAO requirementDAO;
private RequirementStatusDAO requirementStatusDAO;
public RequirementStatusDAO getRequirementStatusDAO() {
return requirementStatusDAO;
}
public void setRequirementStatusDAO(
RequirementStatusDAO requirementStatusDAO) {
this.requirementStatusDAO = requirementStatusDAO;
}
public RequirementDAO getRequirementDAO() {
return requirementDAO;
}
public void setRequirementDAO(RequirementDAO requirementDAO) {
this.requirementDAO = requirementDAO;
}
@Override
public void saveRequirement(Requirement requirement) {
// TODO Auto-generated method stub
this.requirementDAO.save(requirement);
}
@Override
public void updateRequirement(Requirement requirement) {
// TODO Auto-generated method stub
this.requirementDAO.merge(requirement);
}
@Override
public List downloadRequirement() {
File file = new File("D:/request" + new Date() + ".xls");
if (!file.exists()) {
try {
file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
try {
FileInputStream in;
in = new FileInputStream(file);
HSSFWorkbook wk = new HSSFWorkbook(in);
HSSFSheet sheet = wk.createSheet();
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell0 = row0.createCell(0);
cell0.setCellValue("S.No");
HSSFCell cell1 = row0.createCell(0);
cell1.setCellValue("Request Id");
HSSFCell cell2 = row0.createCell(0);
cell2.setCellValue("Requirement Id");
HSSFCell cell3 = row0.createCell(0);
cell3.setCellValue("IOU");
HSSFCell cell4 = row0.createCell(0);
cell4.setCellValue("Customer Name");
HSSFCell cell5 = row0.createCell(0);
cell5.setCellValue("Key Resource");
HSSFCell cell6 = row0.createCell(0);
cell6.setCellValue("Technology");
HSSFCell cell7 = row0.createCell(0);
cell7.setCellValue("Resume Status");
HSSFCell cell8 = row0.createCell(0);
cell8.setCellValue("Realization");
HSSFCell cell9 = row0.createCell(0);
cell9.setCellValue("Bucket");
HSSFCell cell10 = row0.createCell(0);
cell10.setCellValue("Start Date");
HSSFCell cell11 = row0.createCell(0);
cell11.setCellValue("End Date");
HSSFCell cell12 = row0.createCell(0);
cell12.setCellValue("Remarks");
HSSFCell cell13 = row0.createCell(0);
cell13.setCellValue("Location Details");
HSSFCell cell14 = row0.createCell(0);
cell14.setCellValue("Country");
HSSFCell cell15 = row0.createCell(0);
cell15.setCellValue("Won No/Oppurtunity Id");
HSSFCell cell16 = row0.createCell(0);
cell16.setCellValue("Project Type");
HSSFCell cell17 = row0.createCell(0);
cell17.setCellValue("Onsite Work Sector");
HSSFCell cell18 = row0.createCell(0);
cell18.setCellValue("Offshore Work Sector");
HSSFCell cell19 = row0.createCell(0);
cell19.setCellValue("Resume To Be Sent To Emp Id");
HSSFCell cell20 = row0.createCell(0);
cell20.setCellValue("Resume To Be Sent To Emp Name");
HSSFCell cell21 = row0.createCell(0);
cell21.setCellValue("No Of Rejected Resume");
HSSFCell cell22 = row0.createCell(0);
cell22.setCellValue("Requirement Type");
HSSFCell cell23 = row0.createCell(0);
cell23.setCellValue("RMG Emp Nod");
HSSFCell cell24 = row0.createCell(0);
cell24.setCellValue("RMG Emp Name");
HSSFCell cell25 = row0.createCell(0);
cell25.setCellValue("Request Created On");
HSSFCell cell26 = row0.createCell(0);
cell26.setCellValue("Reassign History");
HSSFCell cell27 = row0.createCell(0);
cell27.setCellValue("BA Company Name");
HSSFCell cell28 = row0.createCell(0);
cell28.setCellValue("GRS Emp Id");
HSSFCell cell29 = row0.createCell(0);
cell29.setCellValue("GRS Emp Name");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
@Override
public List<String> getAllRequirementIds() {
return this.requirementDAO.findAllRequirementIds();
}
public void mergeRequirement(File file) {
try {
FileInputStream in = new FileInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(in);
HSSFSheet sheet = workbook.getSheetAt(0);
ArrayList<String> idsExcel = new ArrayList<String>();
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
idsExcel.add(sheet.getRow(i).getCell(2).getStringCellValue());
}
ArrayList<String> idsDB = (ArrayList<String>) this
.getAllRequirementIds();
for (int i = 0; i < idsDB.size(); i++) {
if (idsExcel.contains(idsDB.get(i))) {
continue;
} else {
Requirement re = (Requirement) this.requirementDAO
.findByRequirementId(idsDB).get(i);
re.setStatus(Constant.REQUIREMENT_STATUS_CLOSED);
this.updateRequirement(re);
}
}
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Requirement re = this.findRequirementByRequirementid(sheet
.getRow(i).getCell(2).getStringCellValue());
// New requirement
if (re == null) {
System.out.println("row i: " + sheet.getRow(i));
Requirement newRe = createRequiement(sheet.getRow(i));
newRe.setCreateDate(new Date());
// Set requirement status to open
newRe.setRequirementStatus((RequirementStatus) (this.requirementStatusDAO
.findByValue(Constant.REQUIREMENT_STATUS_OPEN)
.get(0)));
newRe.setStatus(Constant.REQUIREMENT_STATUS_OPEN);
this.saveRequirement(newRe);
} else {
Requirement updateRe = createRequiement(sheet.getRow(i));
updateRe.setCreateBy(re.getCreateBy());
updateRe.setCreateDate(re.getCreateDate());
updateRe.setUpdateDate(new Date());
updateRe.setRequirementHrrId(re.getRequirementHrrId());
updateRe.setRequirementStatus((RequirementStatus) (this.requirementStatusDAO
.findByValue(Constant.REQUIREMENT_STATUS_PROPOSED)
.get(0)));
updateRe.setStatus(Constant.REQUIREMENT_STATUS_PROPOSED);
this.updateRequirement(updateRe);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public Requirement findRequirementByRequirementid(String requirementId) {
this.requirementDAO.findByRequirementId(requirementId);
return null;
}
private Requirement createRequiement(HSSFRow row) {
Requirement requirement = new Requirement();
int cellIndex = 1;
HSSFCell cell = row.getCell(cellIndex++);
requirement.setRequestId(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setRequirementId(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setIou(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCustomerGroup(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCustomer(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setLocation(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setKeyResource(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCompetencyArea(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setFocusArea(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setSubFocusArea(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCompetencySubArea(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCompetencySubArea1(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCompetencySubArea2(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCompetencySubArea3(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setProficiency(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setExperience(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setResumeStatus(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setRole(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setTotalExp(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setRealization(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCurrency(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setStartDate(cell.getDateCellValue());
cell = row.getCell(cellIndex++);
requirement.setEndDate(cell.getDateCellValue());
cell = row.getCell(cellIndex++);
requirement.setRemarks(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setSutiabelCandidate(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setPrefferedStaffSource(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setPrefferedDesignation(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setLocationDetail(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCity(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setCountry(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setStaffReason(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setWonno(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setProjectType(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setWorkSectorOnsite(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setWorkSectorOffsite(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setRequestEmpId(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setRequestEmpName(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setResumeRejectNo(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setRequirementType(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setRmgEmpNo(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setRmgEmpName(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setRequestCreatedOn(cell.getDateCellValue());
cell = row.getCell(cellIndex++);
requirement.setGrsStatus(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setReassignHistory(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setBaCompname(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setGrsEmpId(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setGrsEmpName(cell.getStringCellValue());
cell = row.getCell(cellIndex++);
requirement.setHorizontal(cell.getStringCellValue());
return requirement;
}
}