private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
throws Exception {
Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
nCell.setCellStyle(tCell.getCellStyle());
tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
nCell.setCellValue(
tCell
.getStringCellValue()
.replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
.replaceAll(
PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
.replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
nCell.setCellStyle(tCell.getCellStyle());
Row tempRow = tempSheet.getRow(4);
Row newRow = newSheet.createRow(4);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
nCell = newRow.createCell(c, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue());
nCell.setCellStyle(tCell.getCellStyle());
}
for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
newSheet.addMergedRegion(mergedRegion);
}
}
public void prepareDateRange() throws Exception {
DetachedCriteria criteria = DetachedCriteria.forClass(ConfirmationRecord.class);
ProjectionList pjl = Projections.projectionList();
pjl.add(Projections.property("cycleFrom"));
pjl.add(Projections.property("cycleTo"));
criteria.setProjection(Projections.distinct(pjl));
List<?> list = confirmationRecordService.findByCriteria(criteria);
if (list.size() > 0) {
Object[] objs = list.toArray();
selectionCycleFrom = new ArrayList<>();
selectionCycleFrom.add(new SelectItem(null, "Please Select"));
cycleTos = new ArrayList<>();
for (Object obj : objs) {
if (obj instanceof Object[]) {
Object[] innerObjs = (Object[]) obj;
if (innerObjs.length == 2) {
Date dFrom = DateUtil.convStringToDate(MS_SQL_DATE_PATTERN, innerObjs[0].toString());
Date dTo = DateUtil.convStringToDate(MS_SQL_DATE_PATTERN, innerObjs[1].toString());
selectionCycleFrom.add(
new SelectItem(
DateUtil.convDateToString(SIMPLE_DATE_PATTERN, dFrom),
DateUtil.convDateToString(DISPLAY_DATE_PATTERN, dFrom)));
cycleTos.add(DateUtil.convDateToString(SIMPLE_DATE_PATTERN, dTo));
} else {
System.err.println("ERR: obj[] length not eq to 2");
}
}
}
}
}
public void cycleListener() throws ParseException {
selectionCycleTo = new ArrayList<>();
if (!StringUtils.isBlank(selectedDateFrom)) {
selectionCycleTo.add(new SelectItem(null, "Please Select"));
for (String cycleTo : cycleTos) {
if (cycleTo.compareTo(selectedDateFrom) > 0) {
selectionCycleTo.add(
new SelectItem(
cycleTo,
DateUtil.convDateToString(
DISPLAY_DATE_PATTERN,
DateUtil.convStringToDate(SIMPLE_DATE_PATTERN, cycleTo))));
}
}
}
}
private String getSheetName(Date cycleFrom, Date cycleTo) {
String sheetName = "";
Calendar c1 = Calendar.getInstance();
c1.setTime(cycleFrom);
Calendar c2 = Calendar.getInstance();
c2.setTime(cycleTo);
if (c1.get(Calendar.MONTH) == c2.get(Calendar.MONTH)) {
sheetName = DateUtil.convDateToString("dd", cycleFrom);
} else if (c1.get(Calendar.YEAR) == c2.get(Calendar.YEAR)) {
sheetName = DateUtil.convDateToString("dd MMM", cycleFrom);
} else {
sheetName = DateUtil.convDateToString("dd MMM yyyy", cycleFrom);
}
sheetName += "-" + DateUtil.convDateToString("dd MMM yyyy", cycleTo);
return sheetName;
}
public void doDownload() throws Throwable {
if (StringUtils.isBlank(selectedDateFrom) || StringUtils.isBlank(selectedDateTo)) {
MessageUtils.getInstance().addErrorMessage("msgDialogDL", "Please select date");
return;
}
if (selectedDateFrom.compareTo(selectedDateTo) < 0) {
try {
Date cycleFrom = DateUtil.convStringToDate(SIMPLE_DATE_PATTERN, selectedDateFrom);
Date cycleTo = DateUtil.convStringToDate(SIMPLE_DATE_PATTERN, selectedDateTo);
DetachedCriteria criteria = DetachedCriteria.forClass(ConfirmationRecord.class);
criteria.add(Restrictions.between("cycleFrom", cycleFrom, cycleTo));
criteria.add(Restrictions.between("cycleTo", cycleFrom, cycleTo));
criteria.addOrder(Order.asc("cycleFrom"));
criteria.addOrder(Order.asc("id"));
List<ConfirmationRecord> list = confirmationRecordService.findByCriteria(criteria);
Workbook wb = createExcel(list);
if (wb != null) {
wb.removeSheetAt(0);
FileTransfer ft = new FileTransfer();
String fileName =
EXCEL_NAME_PATTERN
+ TEMPLATE_EXCEL_CONF_REPORT_PATH.substring(
TEMPLATE_EXCEL_CONF_REPORT_PATH.lastIndexOf("."),
TEMPLATE_EXCEL_CONF_REPORT_PATH.length());
fileName =
fileName.replaceAll(
"#fdd-MMM-yyyy", DateUtil.convDateToString(DISPLAY_DATE_PATTERN, maxCycleFrom));
fileName =
fileName.replaceAll(
"#tdd-MMM-yyyy", DateUtil.convDateToString(DISPLAY_DATE_PATTERN, maxCycleTo));
ft.fileDownload(fileName, wb);
}
init();
} catch (Exception e) {
e.printStackTrace();
}
} else {
MessageUtils.getInstance().addErrorMessage("msgDialogDL", "Invalid date");
}
}
// Setup column data here
private void setValue(Cell cell, ConfirmationRecord data) {
switch (cell.getColumnIndex()) {
case 0:
cell.setCellValue(cell.getRowIndex() - 4);
break;
case 1:
cell.setCellValue(DateUtil.convDateToString("yyyy", data.getApplicationDate()));
break;
case 2:
cell.setCellValue(DateUtil.convDateToString("M", data.getApplicationDate()));
break;
case 3:
cell.setCellValue(DateUtil.convDateToString("d", data.getApplicationDate()));
break;
case 4:
cell.setCellValue(DateUtil.convDateToString("yyyy", data.getSettleDate()));
break;
case 5:
cell.setCellValue(DateUtil.convDateToString("M", data.getSettleDate()));
break;
case 6:
cell.setCellValue(DateUtil.convDateToString("d", data.getSettleDate()));
break;
case 7:
cell.setCellValue(data.getPolicyNo());
break;
case 8:
cell.setCellValue(data.getPlanCode());
break;
case 9:
cell.setCellValue(data.getInsuredName());
break;
case 10:
cell.setCellValue(data.getModalPremium().doubleValue());
break;
case 11:
cell.setCellValue(data.getApe().doubleValue());
break;
case 12:
cell.setCellValue(data.getSumInsured().doubleValue());
break;
case 13:
cell.setCellValue(data.getMode());
break;
case 14:
cell.setCellValue(data.getStatus());
break;
case 15:
cell.setCellValue(data.getUnitCode());
break;
case 16:
cell.setCellValue(data.getBankCard());
break;
case 17:
cell.setCellValue(data.getCreditCardNo());
break;
case 18:
cell.setCellValue(data.getExpiredDate());
break;
case 19:
cell.setCellValue(
data.getBirthDate() != null
? DateUtil.convDateToString("yyyy", data.getBirthDate())
: null);
break;
case 20:
cell.setCellValue(
data.getBirthDate() != null
? DateUtil.convDateToString("M", data.getBirthDate())
: null);
break;
case 21:
cell.setCellValue(
data.getBirthDate() != null
? DateUtil.convDateToString("d", data.getBirthDate())
: null);
break;
case 22:
cell.setCellValue(data.getCitizenId());
break;
case 23:
cell.setCellValue(data.getAddress1());
break;
case 24:
cell.setCellValue(data.getAddress2());
break;
case 25:
cell.setCellValue(data.getAddress3());
break;
case 26:
cell.setCellValue(data.getProvince());
break;
case 27:
cell.setCellValue(data.getPostCode());
break;
case 28:
cell.setCellValue(data.getTel1());
break;
case 29:
cell.setCellValue(data.getTel2());
break;
case 30:
cell.setCellValue(data.getTel3());
break;
case 31:
cell.setCellValue(data.getMobile1());
break;
case 32:
cell.setCellValue(data.getMobile2());
break;
case 33:
cell.setCellValue(data.getTsrName());
break;
case 34:
cell.setCellValue(data.getAction() != null ? data.getAction().getParamValue() : null);
break;
case 35:
cell.setCellValue(data.getRemark());
break;
default:
break;
}
}
