Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package bean; import com.liferay.faces.portal.context.LiferayFacesContext; import com.liferay.portal.theme.ThemeDisplay; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.Serializable; import java.util.Date; import javax.faces.application.FacesMessage; import javax.faces.bean.ManagedBean; import javax.faces.bean.ManagedProperty; import javax.faces.bean.RequestScoped; import javax.faces.context.FacesContext; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Font; /** * * @author misteam */ @ManagedBean @RequestScoped public class DamayanListingEnrolleeController implements Serializable { /** * Creates a new instance of DamayanListingEnrolleeController */ public DamayanListingEnrolleeController() { } /* * properties */ @ManagedProperty(value = "#{damayanListingEnrolleeData}") private DamayanListingEnrolleeData damayanListingEnrolleeData; @ManagedProperty(value = "#{myMessages}") private MyMessages myMessages; @ManagedProperty(value = "#{exportData}") private ExportData exportData; /* * getter setter */ public DamayanListingEnrolleeData getDamayanListingEnrolleeData() { return damayanListingEnrolleeData == null ? damayanListingEnrolleeData = new DamayanListingEnrolleeData() : damayanListingEnrolleeData; } public void setDamayanListingEnrolleeData(DamayanListingEnrolleeData damayanListingEnrolleeData) { this.damayanListingEnrolleeData = damayanListingEnrolleeData; } public MyMessages getMyMessages() { return myMessages == null ? myMessages = new MyMessages() : myMessages; } public void setMyMessages(MyMessages myMessages) { this.myMessages = myMessages; } public ExportData getExportData() { return exportData == null ? exportData = new ExportData() : exportData; } public void setExportData(ExportData exportData) { this.exportData = exportData; } /* * methods */ public void init() { if (FacesContext.getCurrentInstance().isPostback() == false) { beanclear(); } } public void beanclear() { getDamayanListingEnrolleeData().beanclear(); } public String stringFormat() { String query; query = "SELECT cdm.dm_acctno, " + "cdm.sc_acctno, " + "cdm.acct_name, " + "cdm.birthdate, " + "cdm.acct_create_date, " + "cdm.payment_type, " + "cdm.acct_status, " + "cdm.pdform, " + "cdm.account_code " + "FROM damayan_listing_enrollee_view cdm " + "WHERE cdm.acct_create_date BETWEEN '" + getDamayanListingEnrolleeData() .getCustomDate().formatDate(getDamayanListingEnrolleeData().getDateFrom(), "yyyy-MM-dd") + "' AND '" + getDamayanListingEnrolleeData().getCustomDate() .formatDate(getDamayanListingEnrolleeData().getDateTo(), "yyyy-MM-dd") + "' " + "AND cdm.account_code IN ("; int ctr = 1; for (String x : getDamayanListingEnrolleeData().getAccountCodes()) { if (getDamayanListingEnrolleeData().getAccountCodes().size() == 1) { query += "'" + x + "'"; } else { if (ctr == getDamayanListingEnrolleeData().getAccountCodes().size()) { query += "'" + x + "'"; } else { query += "'" + x + "',"; } } ctr++; } query += ") "; // query += "ORDER BY cdm.acct_create_date DESC"; query += "ORDER BY cdm.dm_acctno"; return query; } public void runEnrollee() { getDamayanListingEnrolleeData().getPrintController().setSelectedBatch(null); getDamayanListingEnrolleeData().setDamayanListSummaryPerAccountCode(null); getDamayanListingEnrolleeData().setDamayanListSummaryPerBatch(null); getDamayanListingEnrolleeData().setSelectedAccountCode(null); getDamayanListingEnrolleeData().setDamayanList(null); getDamayanListingEnrolleeData().setRunComplete(false); getDamayanListingEnrolleeData().setDamayanList( getDamayanListingEnrolleeData().getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory() .createEntityManager().createNativeQuery(stringFormat()).getResultList()); getDamayanListingEnrolleeData().setAccountCodesFiltered(getDamayanListingEnrolleeData().getAccountCodes()); getDamayanListingEnrolleeData().setRunComplete(true); FacesContext context = FacesContext.getCurrentInstance(); if (getDamayanListingEnrolleeData().getDamayanList().isEmpty()) { context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_WARN, "No Data Found", "")); } else { context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Run Complete", "")); } } public String queryAccountCodeMe(String accountCodeParam) { String q = ""; q = "SELECT cdm.dm_acctno, " + "cdm.sc_acctno, " + "cdm.acct_name, " + "cdm.birthdate, " + "cdm.acct_create_date, " + "cdm.payment_type, " + "cdm.acct_status, " + "cdm.pdform, " + "cdm.account_code " + "FROM damayan_listing_enrollee_view cdm " + "WHERE cdm.acct_create_date BETWEEN '" + getDamayanListingEnrolleeData() .getCustomDate().formatDate(getDamayanListingEnrolleeData().getDateFrom(), "yyyy-MM-dd") + "' AND '" + getDamayanListingEnrolleeData().getCustomDate() .formatDate(getDamayanListingEnrolleeData().getDateTo(), "yyyy-MM-dd") + "' " + "AND cdm.account_code = '" + accountCodeParam + "' "; return q; } public String queryBatchMe(String accountCodeParam, String selectedBatchParam) { String q = ""; q = "SELECT * FROM (" + "SELECT row_number() OVER(ORDER BY cdm.dm_acctno) AS row_number, " + "cdm.dm_acctno, " + "cdm.sc_acctno, " + "cdm.acct_name, " + "cdm.birthdate, " + "cdm.acct_create_date, " + "cdm.payment_type, " + "cdm.acct_status, " + "cdm.pdform, " + "cdm.account_code " + "FROM damayan_listing_enrollee_view cdm " + "WHERE cdm.acct_create_date BETWEEN '" + getDamayanListingEnrolleeData() .getCustomDate().formatDate(getDamayanListingEnrolleeData().getDateFrom(), "yyyy-MM-dd") + "' AND '" + getDamayanListingEnrolleeData().getCustomDate() .formatDate(getDamayanListingEnrolleeData().getDateTo(), "yyyy-MM-dd") + "' " + "AND cdm.account_code = '" + accountCodeParam + "' " + "ORDER BY row_number) x " + "WHERE x.row_number " + selectedBatchParam + " "; return q; } public void accountCodeMe() { if (!getDamayanListingEnrolleeData().getSelectedAccountCode().isEmpty()) { getDamayanListingEnrolleeData().setDamayanListSummaryPerAccountCode(getDamayanListingEnrolleeData() .getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory().createEntityManager() .createNativeQuery(queryAccountCodeMe(getDamayanListingEnrolleeData().getSelectedAccountCode())) .getResultList()); } getDamayanListingEnrolleeData().getPrintController() .method0(getDamayanListingEnrolleeData().getDamayanListSummaryPerAccountCode().size()); } public void batchMe() { if (!getDamayanListingEnrolleeData().getPrintController().getBatchIndicators().isEmpty()) { getDamayanListingEnrolleeData().setDamayanListSummaryPerBatch(getDamayanListingEnrolleeData() .getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory().createEntityManager() .createNativeQuery(queryBatchMe(getDamayanListingEnrolleeData().getSelectedAccountCode(), getDamayanListingEnrolleeData().getPrintController().getSelectedBatch())) .getResultList()); } System.out.println("Batch Me Query" + queryBatchMe(getDamayanListingEnrolleeData().getSelectedAccountCode(), getDamayanListingEnrolleeData().getPrintController().getSelectedBatch())); } public void export0() throws FileNotFoundException, IOException { Integer columnNo; HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow dataRow; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); //create folder getExportData().createFolder(null, themeDisplay, "Damayan Listing Enrollee", "DESCRIPTION"); //set filename if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default (" + new Date() + ") "); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); sheet = workbook.createSheet("Damayan Listing Enrollee " + getDamayanListingEnrolleeData().getCustomDate() .formatDate(getDamayanListingEnrolleeData().getDateFrom(), "MMMM dd yyyy") + " to " + getDamayanListingEnrolleeData().getCustomDate() .formatDate(getDamayanListingEnrolleeData().getDateTo(), "MMMM dd yyyy")); dataRow = sheet.createRow((short) 0); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("DAMAYAN LISTING ENROLLEE"); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getDamayanListingEnrolleeData().getCustomDate() .formatDate(getDamayanListingEnrolleeData().getDateFrom(), "MMMM dd yyyy") + " to " + getDamayanListingEnrolleeData().getCustomDate() .formatDate(getDamayanListingEnrolleeData().getDateTo(), "MMMM dd yyyy")); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("DM Account No."); cell.setCellStyle(boldStyle); columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellValue("SC Account No."); cell.setCellStyle(boldStyle); columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellValue("Account Name"); cell.setCellStyle(boldStyle); columnNo = 3; cell = dataRow.createCell(columnNo++); cell.setCellValue("Birthdate"); cell.setCellStyle(boldStyle); columnNo = 4; cell = dataRow.createCell(columnNo++); cell.setCellValue("Damayan Enrolled Date"); cell.setCellStyle(boldStyle); columnNo = 5; cell = dataRow.createCell(columnNo++); cell.setCellValue("Payment Type"); cell.setCellStyle(boldStyle); // dataRow.createCell(columnNo++).setCellValue(""); columnNo = 6; cell = dataRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); columnNo = 7; cell = dataRow.createCell(columnNo++); cell.setCellValue("PD Form"); cell.setCellStyle(boldStyle); for (int i = 0; i < getDamayanListingEnrolleeData().getDamayanList().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); //DM Account columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingEnrolleeData().getDamayanList().get(i)[0]); cell.setCellStyle(cellStyle); //SC Account columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingEnrolleeData().getDataConvert().convertSdToScAcctno( getDamayanListingEnrolleeData().getDamayanList().get(i)[1].toString())); cell.setCellStyle(cellStyle); //Account Name columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingEnrolleeData().getDamayanList().get(i)[2]); cell.setCellStyle(cellStyle); //Birthdate columnNo = 3; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getDamayanListingEnrolleeData().getCustomDate().formatDate( (Date) getDamayanListingEnrolleeData().getDamayanList().get(i)[3], "yyyy-MM-dd")); cell.setCellStyle(cellStyle); //Damayan Enrolled Date columnNo = 4; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingEnrolleeData().getCustomDate().formatDate( (Date) getDamayanListingEnrolleeData().getDamayanList().get(i)[4], "yyyy-MM-dd")); cell.setCellStyle(cellStyle); //Payment Type columnNo = 5; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingEnrolleeData().getDamayanList().get(i)[5].toString() .replace("true", "Auto Deduction").replace("false", "Manual Deduction")); cell.setCellStyle(cellStyle); //Account Status columnNo = 6; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingEnrolleeData().getDataConvert().acctStatusConvert( getDamayanListingEnrolleeData().getDamayanList().get(i)[6].toString().charAt(0))); cell.setCellStyle(cellStyle); //PD Form try { columnNo = 7; cell = dataRow.createCell(columnNo++); cell.setCellValue( (String) getDamayanListingEnrolleeData().getDamayanList().get(i)[7].toString()); cell.setCellStyle(cellStyle); } catch (Exception e) { System.out.println("PD Form null " + e); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Damayan Listing Enrollee", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.println("damayanlistingenrolleecontroller.export0 " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } } }